1 /*****************************************************************************
2 * sql.c
3 *****************************************************************************
4 * conversion of dbf files to sql
5 *
6 * Author: Dr Georg Roesler, groesle@gwdg.de
7 * Mikhail Teterin,
8 * Bj�rn Berg, clergyman@gmx.de
9 *
10 *****************************************************************************
11 * $Id: sql.c,v 1.20 2006/04/14 12:28:12 rollinhand Exp $
12 ****************************************************************************/
13
14 #include "dbf.h"
15 #include "sql.h"
16
17 /* Whether to trim SQL strings from either side: */
18 static int trimright = 0;
19 static int trimleft = 0;
20
21 /* Whether to use copy instead of insert statements */
22 static int usecopy = 0;
23
24 /* Whether to add a 'drop table' statement */
25 static unsigned int sql_drop_table = 1;
26
27 /* Whether to create the sql table */
28 static unsigned int sql_create_table = 1;
29
30 /* Whether to output NULL for empty strings */
31 static int empty_str_is_null = 0;
32
33 /* setSQLEmptyStrIsNULL() {{{
34 * Handler for the '--empty-str-is-null' option.
35 * Output NULL for empty strings.
36 */
37 int
setSQLEmptyStrIsNULL(FILE * output,P_DBF * p_dbf,const char * filename,const char * level)38 setSQLEmptyStrIsNULL(FILE *output, P_DBF *p_dbf,
39 const char *filename, const char *level)
40 {
41 empty_str_is_null = 1;
42 return 0;
43 }
44 /* }}} */
45
46 /* setNoDrop() {{{
47 * Handler for the '--nodrop' option.
48 * disable output of DROP TABLE statement
49 */
50 int
setNoDrop(FILE * output,P_DBF * p_dbf,const char * filename,const char * level)51 setNoDrop(FILE *output, P_DBF *p_dbf,
52 const char *filename, const char *level)
53 {
54 sql_drop_table = 0;
55 return 0;
56 }
57 /* }}} */
58
59 /* setNoCreate() {{{
60 * Handler for the '--nocreate' option.
61 * disable output of CREATE TABLE statement
62 */
63 int
setNoCreate(FILE * output,P_DBF * p_dbf,const char * filename,const char * level)64 setNoCreate(FILE *output, P_DBF *p_dbf,
65 const char *filename, const char *level)
66 {
67 sql_create_table = 0;
68 return 0;
69 }
70 /* }}} */
71
72 /* setSQLTrim() {{{
73 * Handler for the '--tim char' option.
74 */
setSQLTrim(FILE * fp,P_DBF * p_dbf,const char * filename,const char * mode)75 int setSQLTrim(FILE *fp, P_DBF *p_dbf,
76 const char *filename, const char *mode)
77 {
78 if (mode[1] != '\0')
79 goto invalid;
80 switch (mode[0]) {
81 case 'R':
82 case 'r':
83 trimright = 1;
84 return 0;
85 case 'L':
86 case 'l':
87 trimleft = 1;
88 return 0;
89 case 'B':
90 case 'b':
91 trimleft = trimright = 1;
92 return 0;
93 invalid:
94 default:
95 fprintf(stderr, _("Invalid trim mode ``%s''. Expecting ``r'', ``l'', or ``b'' for both."),
96 mode);
97 return 1;
98 }
99 }
100 /* }}} */
101
102 /* setSQLUsecopy() {{{
103 * Handler for the '--usecopy' option.
104 */
setSQLUsecopy(FILE * fp,P_DBF * p_dbf,const char * filename,const char * mode)105 int setSQLUsecopy(FILE *fp, P_DBF *p_dbf,
106 const char *filename, const char *mode)
107 {
108 usecopy = 1;
109 return(0);
110 }
111 /* }}} */
112
113 /* writeSQLHeader() {{{
114 * creates the SQL Header with the information provided by DB_FIELD
115 */
writeSQLHeader(FILE * fp,P_DBF * p_dbf,const char * filename,const char * export_filename)116 int writeSQLHeader (FILE *fp, P_DBF *p_dbf,
117 const char *filename, const char *export_filename)
118 {
119 fprintf(fp, "-- %s -- \n--\n"
120 "-- SQL code with the contents of dbf file %s\n\n", export_filename, filename);
121
122 if ( sql_drop_table ) {
123 fprintf(fp, "\nDROP TABLE %s;\n", tablename);
124 }
125 if ( sql_create_table ) {
126 int unsigned l1,l2;
127 int i, columns;
128
129 fprintf(fp, "\nCREATE TABLE %s (\n", tablename);
130
131 columns = dbf_NumCols(p_dbf);
132 for (i = 0; i < columns; i++) {
133 char field_type;
134 const char *field_name;
135 int field_length, field_decimals;
136 field_type = dbf_ColumnType(p_dbf, i);
137 field_name = dbf_ColumnName(p_dbf, i);
138 field_length = dbf_ColumnSize(p_dbf, i);
139 field_decimals = dbf_ColumnDecimals(p_dbf, i);
140 fprintf(fp, " %-11s ", field_name);
141 switch(field_type) {
142 case 'C':
143 /*
144 * SQL 2 requests "character varying" at this point,
145 * but oracle, informix, db2, MySQL and PGSQL
146 * support also "varchar". To be compatible to most
147 * SQL databases we should use varchar for the moment.
148 * - berg, 2003-09-08
149 */
150 fprintf(fp, "varchar(%d)", field_type == 'M' ? 10 : field_length);
151 break;
152 case 'M':
153 /*
154 * M stands for memo fields which are currently not
155 * supported by dbf.
156 * - berg, 2003-09-08
157 */
158 fprintf(stderr, _("Invalid mode. Cannot convert this dBASE file. Memo fields are not supported."));
159 return 1;
160 break;
161 case 'I':
162 fputs("int", fp);
163 break;
164 case 'N':
165 l1 = field_length;
166 l2 = field_decimals;
167 if((l1 < 10) && (l2 == 0))
168 fputs("int", fp);
169 else
170 fprintf(fp, "numeric(%d,%d)",
171 l1, l2);
172 break;
173 case 'F':
174 l1 = field_length;
175 l2 = field_decimals;
176 fprintf(fp, "numeric(%d, %d)", l1, l2);
177 break;
178 case 'B': {
179 /*
180 * In VisualFoxPro 'B' stands for double so it is an int value
181 */
182 int dbversion = dbf_GetVersion(p_dbf);
183 if ( dbversion == VisualFoxPro ) {
184 l1 = field_length;
185 l2 = field_decimals;
186 fprintf(fp, "numeric(%d, %d)", l1, l2);
187 } else if ( dbversion == dBase3 ) {
188 fprintf(stderr, _("Invalid mode. Cannot convert this dBASE file. Memo fields are not supported."));
189 return 1;
190 }
191
192 break;
193 }
194 case 'D':
195 fputs("date", fp);
196 break;
197 case 'L':
198 /*
199 * Type logical is not supported in SQL, you have to use number
200 * resp. numeric to keep to the standard
201 */
202 fprintf(fp, "boolean");
203 break;
204 default:
205 fprintf(fp, "/* unsupported type ``%c'' */", field_type);
206 }
207 if (i < columns-1)
208 fputc(',', fp);
209 fputs("\n", fp);
210 }
211 fputs(");\n\n", fp);
212 }
213
214 if(usecopy) {
215 int columns, i;
216
217 fprintf(fp, "COPY %s (", tablename);
218 columns = dbf_NumCols(p_dbf);
219 for (i = 0; i < columns; i++) {
220 const char *field_name;
221 field_name = dbf_ColumnName(p_dbf, i);
222 fprintf(fp, "%s", field_name);
223 if (i < columns-1)
224 fputc(',', fp);
225 }
226 fputs(") FROM stdin;\n", fp);
227 }
228
229 return 0;
230 }
231 /* }}} */
232
233 /* writeSQLFooter() {{{
234 * creates the SQL Footer
235 */
writeSQLFooter(FILE * fp,P_DBF * p_dbf,const char * filename,const char * export_filename)236 int writeSQLFooter (FILE *fp, P_DBF *p_dbf,
237 const char *filename, const char *export_filename)
238 {
239 if(usecopy)
240 fputs("\\.\n", fp);
241 }
242 /* }}} */
243
244 /* writeSQLLine() {{{
245 * fills the SQL table
246 */
247 int
writeSQLLine(FILE * fp,P_DBF * p_dbf,const unsigned char * value,int record_length,const char * filename,const char * export_filename)248 writeSQLLine (FILE *fp, P_DBF *p_dbf,
249 const unsigned char *value, int record_length,
250 const char *filename, const char *export_filename)
251 {
252 int i, columns;
253
254 columns = dbf_NumCols(p_dbf);
255
256 if(!usecopy)
257 fprintf(fp, "INSERT INTO %s VALUES (", tablename);
258
259 for (i = 0; i < columns; i++) {
260 const unsigned char *end, *begin;
261 char field_type;
262 int isstring;
263 int isdate;
264 int isbool;
265 field_type = dbf_ColumnType(p_dbf, i);
266 isstring = (field_type == 'M' || field_type == 'C');
267 isdate = (field_type == 'D');
268 isbool = (field_type == 'L');
269
270 /*
271 * A string is only trimmed if trimright and/or trimleft is set
272 * Other datatypes are always "trimmed" to determine, if they
273 * are empty, in which case they are printed out as NULL -- to
274 * keep the SQL correctness. -mi Aug, 2003
275 */
276 begin = value;
277 value += dbf_ColumnSize(p_dbf, i); /* The next field */
278 end = value;
279
280 /* Remove NULL chars at end of field */
281 while(--end != begin && *end == '\0')
282 ;
283
284 if(begin == end && *end == '\0') {
285 if(usecopy)
286 fputs("\\N", fp);
287 else
288 fputs("NULL", fp);
289 continue;
290 }
291
292 end++;
293
294 /*
295 * Non-string data-fields are already right justified
296 * and actually don't need right-trimming, but if we right trim
297 * them as well, we will determine NULL values easily.
298 */
299 if (!isstring || (isstring && trimright)) {
300 while (--end != begin && *end == ' ')
301 ;
302 if (end == begin && *end == ' ') {
303 if(empty_str_is_null || !isstring) {
304 if(usecopy)
305 fputs("\\N", fp);
306 else
307 fputs("NULL", fp);
308 } else {
309 if(!usecopy) {
310 putc('\'', fp);
311 putc('\'', fp);
312 }
313 }
314 /* Is this the last field? */
315 if (i < columns-1) {
316 if(usecopy)
317 putc('\t', fp);
318 else
319 putc(',', fp);
320 }
321 continue;
322 }
323 end++;
324 }
325
326 if (trimleft || !isstring) {
327 while (begin != end && *begin == ' ')
328 begin++;
329 }
330
331 if (!usecopy && (isdate || isstring)) {
332 putc('\'', fp);
333 }
334
335 /* Output the field data */
336 if (isbool) {
337 char sign = *begin++;
338 if ( sign == 't' || sign == 'y' || sign == 'T' || sign == 'Y') {
339 fprintf(fp, "true");
340 } else {
341 fprintf(fp, "false");
342 }
343
344 } else if (field_type == 'B' || field_type == 'F') {
345 char fmt[30];
346
347 sprintf(fmt, "%%%d.%df", dbf_ColumnSize(p_dbf, i), dbf_ColumnDecimals(p_dbf, i));
348 fprintf(fp, fmt, *(double *)begin);
349 begin += dbf_ColumnSize(p_dbf, i);
350
351 } else {
352
353 do { /* Output the non-empty string:*/
354
355 char sign = *begin++; /* cast operations */
356 switch (sign) {
357 case '\'':
358 putc('\\', fp);
359 putc('\'', fp);
360 break;
361 case '\"':
362 putc('\\', fp);
363 putc('\"', fp);
364 break;
365 default:
366 putc(sign, fp);
367 }
368 } while (begin < end);
369
370 }
371
372 if (!usecopy && (isdate || isstring))
373 putc('\'', fp);
374
375 /* Is this the last field? */
376 if (i < columns-1) {
377 if(usecopy)
378 putc('\t', fp);
379 else
380 putc(',', fp);
381 }
382
383 }
384 /* Terminate INSERT INTO or COPY line ; */
385 if(!usecopy)
386 fputs(");", fp);
387 fputs("\n", fp);
388
389 return 0;
390 }
391 /* }}} */
392
393 /*
394 * Local variables:
395 * tab-width: 4
396 * c-basic-offset: 4
397 * End:
398 * vim600: sw=4 ts=4 fdm=marker
399 * vim<600: sw=4 ts=4
400 */
401