?  search:
summary | log | graphiclog1 | graphiclog2 | commit | commitdiff | tree | refs | edit | fork
blame | history | raw | HEAD
fix remapping behavior. Remapping is only necessary if we are rendering on the workbe...
[AROS-Contrib.git] / sqlite3 / shell.c
blobe15f0f5741fdfe6fdfbfc3b6d98c168bedaeff3a
1 /*
2 * 2005 July 5, Markku Sukanen
3 * - modifying the code for AROS.
4 *
5 ** 2001 September 15
6 **
7 ** The author disclaims copyright to this source code. In place of
8 ** a legal notice, here is a blessing:
9 **
10 ** May you do good and not evil.
11 ** May you find forgiveness for yourself and forgive others.
12 ** May you share freely, never taking more than you give.
13 **
14 *************************************************************************
15 */
16 /**
17 * @file shell.c
18 *
19 * This file contains code to implement the "sqlite" command line utility for
20 * accessing SQLite databases.
21 * */
22 /* $Id$ */
23 #include <stdlib.h>
24 #include <string.h>
25 #include <stdio.h>
26 #include <assert.h>
27 #include "sqliteInt.h"
28 #include <ctype.h>
29
30 #if !defined(_WIN32) && !defined(WIN32) && !defined(__MACOS__)
31 # include <signal.h>
32 # include <pwd.h>
33 # include <unistd.h>
34 # include <sys/types.h>
35 #endif
36
37 #ifdef __MACOS__
38 # include <console.h>
39 # include <signal.h>
40 # include <unistd.h>
41 # include <extras.h>
42 # include <Files.h>
43 # include <Folders.h>
44 #endif
45
46 #if defined(HAVE_READLINE) && HAVE_READLINE==1
47 # include <readline/readline.h>
48 # include <readline/history.h>
49 #else
50 # define readline(p) local_getline(p,stdin)
51 # define add_history(X)
52 # define read_history(X)
53 # define write_history(X)
54 # define stifle_history(X)
55 #endif
56
57 #ifdef SQLITE_DEBUG
58 # define TRACE2(X,Y) sqlite3DebugPrintf(X,Y)
59 #else
60 # define TRACE2(X,Y)
61 #endif
62
63 /*
64 * Make sure isatty() has a prototype.
65 * */
66 extern int isatty();
67
68 /*
69 * The following is the open SQLite database. We make a pointer to this
70 * database a static variable so that it can be accessed by the SIGINT handler
71 * to interrupt database processing.
72 * */
73 static sqlite3 *db = 0;
74
75 /*
76 * True if an interrupt (Control-C) has been received.
77 * */
78 static BOOL seenInterrupt = FALSE;
79
80 /*
81 * This is the name of our program. It is set in main(), used in a number of
82 * other places, mostly for error messages.
83 * */
84 static STRPTR Argv0;
85
86 /*
87 * Prompt strings. Initialized in main. Settable with
88 *
89 * .prompt main continue
90 *
91 * */
92 static char mainPrompt[20]; /* First line prompt. default: "sqlite> "*/
93 static char continuePrompt[20]; /* Continuation prompt. default: " ...> " */
94
95
96 /*
97 * Determines if a string is a number of not.
98 * */
99 static BOOL isNumber(const unsigned char *z, int *realnum)
100 {
101 if( *z=='-' || *z=='+' ) z++;
102 if( !isdigit(*z) ) return FALSE;
103 z++;
104 if( realnum ) *realnum = 0;
105 while( isdigit(*z) ){ z++; }
106 if( *z=='.' )
107 {
108 z++;
109 if( !isdigit(*z) ) return FALSE;
110 while( isdigit(*z) ){ z++; }
111 if( realnum ) *realnum = 1;
112 }
113 if( *z=='e' || *z=='E' )
114 {
115 z++;
116 if( *z=='+' || *z=='-' ) z++;
117 if( !isdigit(*z) ) return FALSE;
118 while( isdigit(*z) ){ z++; }
119 if( realnum ) *realnum = 1;
120 }
121 return (*z==0);
122 }
123
124
125 /*
126 * A global char* and an SQL function to access its current value from within
127 * an SQL statement. This program used to use the sqlite_exec_printf() API to
128 * substitue a string into an SQL statement. The correct way to do this with
129 * sqlite3 is to use the bind API, but since the shell is built around the
130 * callback paradigm it would be a lot of work. Instead just use this hack,
131 * which is quite harmless.
132 * */
133 static CONST_STRPTR zShellStatic = NULL;
134 static void shellstaticFunc(
135 sqlite3_context *context,
136 int argc,
137 sqlite3_value **argv
138 )
139 {
140 assert( 0==argc );
141 assert( zShellStatic );
142 sqlite3_result_text(context, zShellStatic, -1, SQLITE_STATIC);
143 }
144
145
146 /*
147 * This routine reads a line of text from FILE in, stores the text in memory
148 * obtained from malloc() and returns a pointer to the text. NULL is returned
149 * at end of file, or if malloc() fails.
150 *
151 * The interface is like "readline" but no command-line editing is done.
152 * */
153 static STRPTR local_getline(CONST_STRPTR zPrompt, FILE *in)
154 {
155 STRPTR zLine;
156 int nLine;
157 int n;
158 BOOL eol;
159
160 if( zPrompt && *zPrompt != '\0' )
161 {
162 printf("%s",zPrompt);
163 fflush(stdout);
164 }
165 nLine = 100;
166 if( ! (zLine = malloc( nLine )))
167 return NULL;
168 n = 0;
169 eol = FALSE;
170 while( !eol )
171 {
172 if( n+100>nLine )
173 {
174 nLine = nLine*2 + 100;
175 zLine = realloc(zLine, nLine);
176 if( !zLine ) return NULL;
177 }
178 if( fgets(&zLine[n], nLine - n, in)==0 )
179 {
180 if( !n )
181 {
182 free( zLine );
183 return NULL;
184 }
185 zLine[n] = '\0';
186 eol = TRUE;
187 break;
188 }
189 while( zLine[n] ){ n++; }
190 if( n>0 && zLine[n-1]=='\n' )
191 {
192 n--;
193 zLine[n] = 0;
194 eol = TRUE;
195 }
196 }
197 zLine = realloc( zLine, n+1 );
198 return zLine;
199 }
200
201
202 /*
203 * Retrieve a single line of input text. "isatty" is true if text is coming
204 * from a terminal. In that case, we issue a prompt and attempt to use
205 * "readline" for command-line editing. If "isatty" is false, use
206 * "local_getline" instead of "readline" and issue no prompt.
207 *
208 * *zPrior is a string of prior text retrieved. If not the empty string, then
209 * issue a continuation prompt.
210 * */
211 static STRPTR one_input_line(CONST_STRPTR zPrior, FILE *in)
212 {
213 STRPTR zPrompt;
214 STRPTR zResult;
215 if( in )
216 return local_getline(0, in);
217 if( zPrior && zPrior[0] )
218 zPrompt = continuePrompt;
219 else
220 zPrompt = mainPrompt;
221 zResult = readline(zPrompt);
222 #if defined(HAVE_READLINE) && HAVE_READLINE==1
223 if( zResult ) add_history(zResult);
224 #endif
225 return zResult;
226 }
227
228
229 struct previous_mode_data {
230 int valid; /* Is there legit data in here? */
231 int mode;
232 int showHeader;
233 int colWidth[100];
234 };
235
236
237 /*
238 * An pointer to an instance of this structure is passed from the main program
239 * to the callback. This is used to communicate state and mode information.
240 * */
241 struct callback_data {
242 sqlite3 *db; /* The database */
243 int echoOn; /* True to echo input commands */
244 int cnt; /* Number of records displayed so far */
245 FILE *out; /* Write results here */
246 int mode; /* An output mode setting */
247 BOOL showHeader; /* True to show column names in List or Column mode */
248 char *zDestTable; /* Name of destination table when MODE_Insert */
249 char separator[20]; /* Separator character for MODE_List */
250 int colWidth[100]; /* Requested width of each column when in column mode*/
251 int actualWidth[100];/* Actual width of each column */
252 char nullvalue[20]; /* The text to print when a NULL comes back from
253 * the database */
254 struct previous_mode_data explainPrev;
255 /* Holds the mode information just before
256 * .explain ON */
257 char outfile[FILENAME_MAX]; /* Filename for *out */
258 CONST_STRPTR zDbFilename; /* name of the database file */
259 char *zKey; /* Encryption key */
260 };
261
262
263 /*
264 * These are the allowed modes.
265 * */
266 #define MODE_Line 0 /* One column per line. Blank line between records */
267 #define MODE_Column 1 /* One record per line in neat columns */
268 #define MODE_List 2 /* One record per line with a separator */
269 #define MODE_Semi 3 /* Same as MODE_List but append ";" to each line */
270 #define MODE_Html 4 /* Generate an XHTML table */
271 #define MODE_Insert 5 /* Generate SQL "insert" statements */
272 #define MODE_Tcl 6 /* Generate ANSI-C or TCL quoted elements */
273 #define MODE_Csv 7 /* Quote strings, numbers are plain */
274 #define MODE_NUM_OF 8 /* The number of modes (not a mode itself) */
275
276 char *modeDescr[MODE_NUM_OF] = {
277 "line",
278 "column",
279 "list",
280 "semi",
281 "html",
282 "insert",
283 "tcl",
284 "csv",
285 };
286
287
288 /*
289 * Number of elements in an array.
290 * */
291 #define ArraySize(X) (sizeof(X)/sizeof(X[0]))
292
293
294 /*
295 * Output the given string as a quoted string using SQL quoting conventions.
296 * */
297 static void output_quoted_string(FILE *out, const char *z)
298 {
299 int i;
300 int nSingle = 0;
301 for(i=0; z[i]; i++){ if( z[i]=='\'' ) nSingle++; }
302 if( nSingle==0 )
303 fprintf(out,"'%s'",z);
304 else
305 {
306 fprintf(out,"'");
307 while( *z )
308 {
309 for(i=0; z[i] && z[i]!='\''; i++){}
310 if( i==0 )
311 {
312 fprintf(out,"''");
313 z++;
314 }
315 else if( z[i]=='\'' )
316 {
317 fprintf(out,"%.*s''",i,z);
318 z += i+1;
319 }
320 else
321 {
322 fprintf(out,"%s",z);
323 break;
324 }
325 }
326 fprintf(out,"'");
327 }
328 }
329
330
331 /*
332 * Output the given string as a quoted according to C or TCL quoting rules.
333 * */
334 static void output_c_string(FILE *out, const char *z)
335 {
336 unsigned int c;
337 fputc('"', out);
338 while( (c = *(z++))!=0 )
339 {
340 if( c=='\\' )
341 {
342 fputc(c, out);
343 fputc(c, out);
344 }
345 else if( c=='\t' )
346 {
347 fputc('\\', out);
348 fputc('t', out);
349 }
350 else if( c=='\n' )
351 {
352 fputc('\\', out);
353 fputc('n', out);
354 }
355 else if( c=='\r' )
356 {
357 fputc('\\', out);
358 fputc('r', out);
359 }
360 else if( !isprint(c) )
361 fprintf(out, "\\%03o", c);
362 else
363 fputc(c, out);
364 }
365 fputc('"', out);
366 }
367
368
369 /*
370 * Output the given string with characters that are special to HTML escaped.
371 * */
372 static void output_html_string(FILE *out, const char *z)
373 {
374 int i;
375 while( *z )
376 {
377 for(i=0; z[i] && z[i]!='<' && z[i]!='&'; i++){}
378 if( i>0 )
379 fprintf(out,"%.*s",i,z);
380 if( z[i]=='<' )
381 fprintf(out,"&lt;");
382 else if( z[i]=='&' )
383 fprintf(out,"&amp;");
384 else
385 break;
386 z += i + 1;
387 }
388 }
389
390
391 /*
392 * Output a single term of CSV. Actually, p->separator is used for the
393 * separator, which may or may not be a comma. p->nullvalue is the null value.
394 * Strings are quoted using ANSI-C rules. Numbers appear outside of quotes.
395 * */
396 static void output_csv(struct callback_data *p, const char *z, int bSep)
397 {
398 if( z==0 )
399 fprintf(p->out,"%s",p->nullvalue);
400 else if( isNumber(z, 0) )
401 fprintf(p->out,"%s",z);
402 else
403 output_c_string(p->out, z);
404 if( bSep )
405 fprintf(p->out, p->separator);
406 }
407
408
409 #ifdef SIGINT
410 /*
411 * This routine runs when the user presses Ctrl-C
412 * */
413 static void interrupt_handler(int NotUsed)
414 {
415 seenInterrupt = 1;
416 if( db ) sqlite3_interrupt(db);
417 }
418 #endif
419
420
421 /*
422 * This is the callback routine that the SQLite library invokes for each row of
423 * a query result.
424 * */
425 static int callback(void *pArg, int nArg, char **azArg, char **azCol)
426 {
427 int i;
428 struct callback_data *p = (struct callback_data*)pArg;
429 switch( p->mode )
430 {
431 case MODE_Line:
432 {
433 int w = 5;
434 if( azArg==0 ) break;
435 for(i=0; i<nArg; i++)
436 {
437 int len = strlen(azCol[i]);
438 if( len>w ) w = len;
439 }
440 if( p->cnt++>0 ) fprintf(p->out,"\n");
441 for(i=0; i<nArg; i++)
442 fprintf(p->out,"%*s = %s\n", w, azCol[i],
443 azArg[i] ? azArg[i] : p->nullvalue);
444 }
445 break;
446
447 case MODE_Column:
448 {
449 if( p->cnt++==0 )
450 {
451 for(i=0; i<nArg; i++)
452 {
453 int w, n;
454 if( i<ArraySize(p->colWidth) )
455 w = p->colWidth[i];
456 else w = 0;
457 if( w <= 0 )
458 {
459 w = strlen(azCol[i] ? azCol[i] : "");
460 if( w<10 ) w = 10;
461 n = strlen(azArg && azArg[i] ? azArg[i]
462 : p->nullvalue);
463 if( w<n ) w = n;
464 }
465 if( i<ArraySize(p->actualWidth) )
466 p->actualWidth[i] = w;
467 if( p->showHeader )
468 fprintf(p->out,"%-*.*s%s",w,w,azCol[i],
469 i==nArg-1 ? "\n": " ");
470 }
471 if( p->showHeader )
472 {
473 for(i=0; i<nArg; i++)
474 {
475 int w;
476 if( i<ArraySize(p->actualWidth) )
477 w = p->actualWidth[i];
478 else w = 10;
479
480 fprintf(p->out,"%-*.*s%s",w,w,
481 "-----------------------------------"
482 "-----------------------------------"
483 "-----------------------",
484 i==nArg-1 ? "\n": " ");
485 }
486 }
487 }
488 if( azArg==0 ) break;
489 for(i=0; i<nArg; i++)
490 {
491 int w;
492 if( i<ArraySize(p->actualWidth) )
493 w = p->actualWidth[i];
494 else w = 10;
495
496 fprintf(p->out,"%-*.*s%s",w,w,
497 azArg[i] ? azArg[i] : p->nullvalue,
498 i==nArg-1 ? "\n": " ");
499 }
500 }
501 break;
502
503 case MODE_Semi:
504 case MODE_List:
505 {
506 if( p->cnt++==0 && p->showHeader )
507 for(i=0; i<nArg; i++)
508 fprintf(p->out,"%s%s",azCol[i], i==nArg-1 ? "\n"
509 : p->separator);
510 if( azArg==0 ) break;
511 for(i=0; i<nArg; i++)
512 {
513 char *z = azArg[i];
514 if( z==0 ) z = p->nullvalue;
515 fprintf(p->out, "%s", z);
516 if( i<nArg-1 )
517 fprintf(p->out, "%s", p->separator);
518 else if( p->mode==MODE_Semi )
519 fprintf(p->out, ";\n");
520 else
521 fprintf(p->out, "\n");
522 }
523 }
524 break;
525
526 case MODE_Html:
527 {
528 if( p->cnt++==0 && p->showHeader )
529 {
530 fprintf(p->out,"<TR>");
531 for(i=0; i<nArg; i++)
532 fprintf(p->out,"<TH>%s</TH>",azCol[i]);
533 fprintf(p->out,"</TR>\n");
534 }
535 if( azArg==0 ) break;
536 fprintf(p->out,"<TR>");
537 for(i=0; i<nArg; i++)
538 {
539 fprintf(p->out,"<TD>");
540 output_html_string(p->out, azArg[i] ? azArg[i]
541 : p->nullvalue);
542 fprintf(p->out,"</TD>\n");
543 }
544 fprintf(p->out,"</TR>\n");
545 }
546 break;
547
548 case MODE_Tcl:
549 {
550 if( p->cnt++==0 && p->showHeader )
551 {
552 for(i=0; i<nArg; i++)
553 {
554 output_c_string(p->out,azCol[i]);
555 fprintf(p->out, "%s", p->separator);
556 }
557 fprintf(p->out,"\n");
558 }
559 if( azArg==0 ) break;
560 for(i=0; i<nArg; i++)
561 {
562 output_c_string(p->out, azArg[i] ? azArg[i] : p->nullvalue);
563 fprintf(p->out, "%s", p->separator);
564 }
565 fprintf(p->out,"\n");
566 }
567 break;
568
569 case MODE_Csv:
570 {
571 if( p->cnt++==0 && p->showHeader )
572 {
573 for(i=0; i<nArg; i++)
574 output_csv(p, azCol[i], i<nArg-1);
575 fprintf(p->out,"\n");
576 }
577 if( azArg==0 ) break;
578 for(i=0; i<nArg; i++)
579 output_csv(p, azArg[i], i<nArg-1);
580 fprintf(p->out,"\n");
581 }
582 break;
583
584 case MODE_Insert:
585 {
586 if( azArg==0 ) break;
587 fprintf(p->out,"INSERT INTO %s VALUES(",p->zDestTable);
588 for(i=0; i<nArg; i++)
589 {
590 char *zSep = i>0 ? ",": "";
591 if( azArg[i]==0 )
592 fprintf(p->out,"%sNULL",zSep);
593 else if( isNumber(azArg[i], 0) )
594 fprintf(p->out,"%s%s",zSep, azArg[i]);
595 else
596 {
597 if( zSep[0] ) fprintf(p->out,"%s",zSep);
598 output_quoted_string(p->out, azArg[i]);
599 }
600 }
601 fprintf(p->out,");\n");
602 }
603 break;
604 }
605 return 0;
606 }
607
608
609 /*
610 * Set the destination table field of the callback_data structure to the name
611 * of the table given. Escape any quote characters in the table name.
612 * */
613 static void set_table_name(struct callback_data *p, CONST_STRPTR zName)
614 {
615 int i, n;
616 BOOL needQuote;
617 STRPTR z;
618
619 if( p->zDestTable )
620 {
621 free(p->zDestTable);
622 p->zDestTable = NULL;
623 }
624 if( !zName ) return;
625 needQuote = !isalpha((unsigned char)*zName) && *zName!='_';
626 for(i=n=0; zName[i]; i++, n++)
627 {
628 if( !isalnum((unsigned char)zName[i]) && zName[i]!='_' )
629 {
630 needQuote = TRUE;
631 if( zName[i]=='\'' ) n++;
632 }
633 }
634 if( needQuote ) n += 2;
635 z = p->zDestTable = malloc( n+1 );
636 if( !z )
637 {
638 fprintf(stderr,"Out of memory!\n");
639 exit(1);
640 }
641 n = 0;
642 if( needQuote ) z[n++] = '\'';
643 for(i=0; zName[i]; i++)
644 {
645 z[n++] = zName[i];
646 if( zName[i]=='\'' ) z[n++] = '\'';
647 }
648 if( needQuote ) z[n++] = '\'';
649 z[n] = 0;
650 }
651
652
653 /*
654 * zIn is either a pointer to a NULL-terminated string in memory obtained from
655 * malloc(), or a NULL pointer. The string pointed to by zAppend is added to
656 * zIn, and the result returned in memory obtained from malloc(). zIn, if it
657 * was not NULL, is freed.
658 *
659 * If the third argument, quote, is not '\0', then it is used as a quote
660 * character for zAppend.
661 * */
662 static STRPTR appendText(STRPTR zIn, CONST_STRPTR zAppend, char quote)
663 {
664 int len;
665 int i;
666 int nAppend = strlen(zAppend);
667 int nIn = (zIn?strlen(zIn):0);
668
669 len = nAppend+nIn+1;
670 if( quote )
671 {
672 len += 2;
673 for(i=0; i<nAppend; i++)
674 if( zAppend[i]==quote ) len++;
675 }
676
677 zIn = (char *)realloc(zIn, len);
678 if( !zIn )
679 return NULL;
680
681 if( quote )
682 {
683 STRPTR zCsr = &zIn[nIn];
684 *zCsr++ = quote;
685 for(i=0; i<nAppend; i++)
686 {
687 *zCsr++ = zAppend[i];
688 if( zAppend[i]==quote ) *zCsr++ = quote;
689 }
690 *zCsr++ = quote;
691 *zCsr++ = '\0';
692 assert( (zCsr-zIn)==len );
693 }
694 else
695 {
696 memcpy(&zIn[nIn], zAppend, nAppend);
697 zIn[len-1] = '\0';
698 }
699
700 return zIn;
701 }
702
703
704 /*
705 * Execute a query statement that has a single result column. Print that result
706 * column on a line by itself with a semicolon terminator.
707 * */
708 static int run_table_dump_query(FILE *out, sqlite3 *db, const char *zSelect)
709 {
710 sqlite3_stmt *pSelect;
711 int rc;
712 rc = sqlite3_prepare(db, zSelect, -1, &pSelect, 0);
713 if( rc!=SQLITE_OK || !pSelect )
714 return rc;
715 rc = sqlite3_step(pSelect);
716 while( rc==SQLITE_ROW )
717 {
718 fprintf(out, "%s;\n", sqlite3_column_text(pSelect, 0));
719 rc = sqlite3_step(pSelect);
720 }
721 return sqlite3_finalize(pSelect);
722 }
723
724
725 /*
726 * This is a different callback routine used for dumping the database. Each row
727 * received by this callback consists of a table name, the table type ("index"
728 * or "table") and SQL to create the table. This routine should print text
729 * sufficient to recreate the table.
730 * */
731 static int dump_callback(void *pArg, int nArg, char **azArg, char **azCol)
732 {
733 int rc;
734 CONST_STRPTR zTable;
735 CONST_STRPTR zType;
736 CONST_STRPTR zSql;
737 struct callback_data *p = (struct callback_data *)pArg;
738
739 if( nArg!=3 ) return 1;
740 zTable = azArg[0];
741 zType = azArg[1];
742 zSql = azArg[2];
743
744 if( strcmp(zTable,"sqlite_sequence")!=0 )
745 fprintf(p->out, "%s;\n", zSql);
746 else
747 fprintf(p->out, "DELETE FROM sqlite_sequence;\n");
748
749 if( strcmp(zType, "table")==0 )
750 {
751 sqlite3_stmt *pTableInfo = NULL;
752 STRPTR zSelect = NULL;
753 STRPTR zTableInfo = NULL;
754 STRPTR zTmp = NULL;
755
756 zTableInfo = appendText(zTableInfo, "PRAGMA table_info(", 0);
757 zTableInfo = appendText(zTableInfo, zTable, '"');
758 zTableInfo = appendText(zTableInfo, ");", 0);
759
760 rc = sqlite3_prepare(p->db, zTableInfo, -1, &pTableInfo, 0);
761 if( zTableInfo ) free(zTableInfo);
762 if( rc!=SQLITE_OK || !pTableInfo )
763 {
764 return 1;
765 }
766
767 zSelect = appendText(zSelect, "SELECT 'INSERT INTO ' || ", 0);
768 zTmp = appendText(zTmp, zTable, '"');
769 if( zTmp )
770 zSelect = appendText(zSelect, zTmp, '\'');
771 zSelect = appendText(zSelect, " || ' VALUES(' || ", 0);
772 rc = sqlite3_step(pTableInfo);
773 while( rc==SQLITE_ROW )
774 {
775 zSelect = appendText(zSelect, "quote(", 0);
776 zSelect = appendText(zSelect, sqlite3_column_text(pTableInfo, 1),
777 '"');
778 rc = sqlite3_step(pTableInfo);
779 if( rc==SQLITE_ROW )
780 zSelect = appendText(zSelect, ") || ', ' || ", 0);
781 else
782 zSelect = appendText(zSelect, ") ", 0);
783 }
784 rc = sqlite3_finalize(pTableInfo);
785 if( rc!=SQLITE_OK )
786 {
787 if( zSelect ) free(zSelect);
788 return 1;
789 }
790 zSelect = appendText(zSelect, "|| ')' FROM ", 0);
791 zSelect = appendText(zSelect, zTable, '"');
792
793 rc = run_table_dump_query(p->out, p->db, zSelect);
794 if( rc==SQLITE_CORRUPT )
795 {
796 zSelect = appendText(zSelect, " ORDER BY rowid DESC", 0);
797 rc = run_table_dump_query(p->out, p->db, zSelect);
798 }
799 if( zSelect ) free(zSelect);
800 if( rc!=SQLITE_OK )
801 return 1;
802 }
803 return 0;
804 }
805
806
807 /*
808 * Run zQuery. Update dump_callback() as the callback routine. If we get a
809 * SQLITE_CORRUPT error, rerun the query after appending "ORDER BY rowid DESC"
810 * to the end.
811 * */
812 static int run_schema_dump_query(
813 struct callback_data *p,
814 CONST_STRPTR zQuery,
815 char **pzErrMsg
816 )
817 {
818 int rc;
819 rc = sqlite3_exec(p->db, zQuery, dump_callback, p, pzErrMsg);
820 if( rc==SQLITE_CORRUPT )
821 {
822 STRPTR zQ2;
823 int len = strlen(zQuery);
824 if( pzErrMsg ) sqlite3_free(*pzErrMsg);
825 zQ2 = malloc( len+100 );
826 if( !zQ2 ) return rc;
827 sprintf(zQ2, "%s ORDER BY rowid DESC", zQuery);
828 rc = sqlite3_exec(p->db, zQ2, dump_callback, p, pzErrMsg);
829 free(zQ2);
830 }
831 return rc;
832 }
833
834
835 /*
836 * Text of a help message.
837 * */
838 static char zHelp[] =
839 ".databases List names and files of attached databases\n"
840 ".dump ?TABLE? ... Dump the database in an SQL text format\n"
841 ".echo ON|OFF Turn command echo on or off\n"
842 ".exit Exit this program\n"
843 ".explain ON|OFF Turn output mode suitable for EXPLAIN on or off.\n"
844 ".header(s) ON|OFF Turn display of headers on or off\n"
845 ".help Show this message\n"
846 ".import FILE TABLE Import data from FILE into TABLE\n"
847 ".indices TABLE Show names of all indices on TABLE\n"
848 ".mode MODE ?TABLE? Set output mode where MODE is one of:\n"
849 " csv Comma-separated values\n"
850 " column Left-aligned columns. (See .width)\n"
851 " html HTML <table> code\n"
852 " insert SQL insert statements for TABLE\n"
853 " line One value per line\n"
854 " list Values delimited by .separator string\n"
855 " tabs Tab-separated values\n"
856 " tcl TCL list elements\n"
857 ".nullvalue STRING Print STRING in place of NULL values\n"
858 ".output FILENAME Send output to FILENAME\n"
859 ".output stdout Send output to the screen\n"
860 ".prompt MAIN CONTINUE Replace the standard prompts\n"
861 ".quit Exit this program\n"
862 ".read FILENAME Execute SQL in FILENAME\n"
863 #ifdef SQLITE_HAS_CODEC
864 ".rekey OLD NEW NEW Change the encryption key\n"
865 #endif
866 ".schema ?TABLE? Show the CREATE statements\n"
867 ".separator STRING Change separator used by output mode and .import\n"
868 ".show Show the current values for various settings\n"
869 ".tables ?PATTERN? List names of tables matching a LIKE pattern\n"
870 ".timeout MS Try opening locked tables for MS milliseconds\n"
871 ".width NUM NUM ... Set column widths for \"column\" mode\n"
872 ;
873
874
875 /* Forward reference */
876 static void process_input(struct callback_data *p, FILE *in);
877
878
879 /*
880 * Make sure the database is open. If it is not, then open it. If the database
881 * fails to open, print an error message and exit.
882 * */
883 static void open_db(struct callback_data *p)
884 {
885 if( !p->db )
886 {
887 sqlite3_open(p->zDbFilename, &p->db);
888 db = p->db;
889 #ifdef SQLITE_HAS_CODEC
890 sqlite3_key(p->db, p->zKey, p->zKey ? strlen(p->zKey) : 0);
891 #endif
892 sqlite3_create_function(db, "shellstatic", 0, SQLITE_UTF8, 0,
893 shellstaticFunc, 0, 0);
894 if( SQLITE_OK != sqlite3_errcode(db) )
895 {
896 fprintf(stderr,"Unable to open database \"%s\": %s\n",
897 p->zDbFilename, sqlite3_errmsg(db));
898 exit(1);
899 }
900 }
901 }
902
903
904 /*
905 * Do C-language style dequoting.
906 *
907 * \t -> tab
908 * \n -> newline
909 * \r -> carriage return
910 * \NNN -> ascii character NNN in octal
911 * \\ -> backslash
912 *
913 * */
914 static void resolve_backslashes(STRPTR z)
915 {
916 int i, j, c;
917 for(i=j=0; (c = z[i])!=0; i++, j++)
918 {
919 if( c=='\\' )
920 {
921 c = z[++i];
922 if( c=='n' ) c = '\n';
923 else if( c=='t' ) c = '\t';
924 else if( c=='r' ) c = '\r';
925 else if( c>='0' && c<='7' )
926 {
927 c =- '0';
928 if( z[i+1]>='0' && z[i+1]<='7' )
929 {
930 i++;
931 c = (c<<3) + z[i] - '0';
932 if( z[i+1]>='0' && z[i+1]<='7' )
933 {
934 i++;
935 c = (c<<3) + z[i] - '0';
936 }
937 }
938 }
939 }
940 z[j] = c;
941 }
942 z[j] = 0;
943 }
944
945
946 /*
947 * If an input line begins with "." then invoke this routine to process that
948 * line.
949 *
950 * Return 1 to exit and 0 to continue.
951 * */
952 static BOOL do_meta_command(STRPTR zLine, struct callback_data *p)
953 {
954 int i = 1;
955 int nArg = 0;
956 int n, c;
957 BOOL rc = FALSE;
958 char *azArg[50];
959
960 /* Parse the input line into tokens. */
961 while( zLine[i] && nArg<ArraySize(azArg) )
962 {
963 while( isspace((unsigned char)zLine[i]) ){ i++; }
964 if( zLine[i]=='\0' ) break;
965 if( zLine[i]=='\'' || zLine[i]=='"' )
966 {
967 int delim = zLine[i++];
968 azArg[nArg++] = &zLine[i];
969 while( zLine[i] && zLine[i]!=delim ){ i++; }
970 if( zLine[i]==delim )
971 zLine[i++] = '\0';
972 if( delim=='"' ) resolve_backslashes(azArg[nArg-1]);
973 }
974 else
975 {
976 azArg[nArg++] = &zLine[i];
977 while( zLine[i] && !isspace((unsigned char)zLine[i]) ){ i++; }
978 if( zLine[i] ) zLine[i++] = '\0';
979 resolve_backslashes(azArg[nArg-1]);
980 }
981 }
982
983 /*
984 * Process the input line.
985 * */
986 if( !nArg ) return rc;
987 n = strlen(azArg[0]);
988 c = azArg[0][0];
989 if( c=='d' && n>1 && strncmp(azArg[0], "databases", n)==0 )
990 {
991 struct callback_data data;
992 char *zErrMsg = NULL;
993 open_db(p);
994 memcpy(&data, p, sizeof(data));
995 data.showHeader = TRUE;
996 data.mode = MODE_Column;
997 data.colWidth[0] = 3;
998 data.colWidth[1] = 15;
999 data.colWidth[2] = 58;
1000 data.cnt = 0;
1001 sqlite3_exec(p->db, "PRAGMA database_list; ", callback,
1002 &data, &zErrMsg);
1003 if( zErrMsg )
1004 {
1005 fprintf(stderr,"Error: %s\n", zErrMsg);
1006 sqlite3_free(zErrMsg);
1007 }
1008 }
1009 else if( c=='d' && strncmp(azArg[0], "dump", n)==0 )
1010 {
1011 STRPTR zErrMsg = NULL;
1012 open_db(p);
1013 fprintf(p->out, "BEGIN TRANSACTION;\n");
1014 if( nArg==1 )
1015 {
1016 run_schema_dump_query(p,
1017 "SELECT name, type, sql FROM sqlite_master "
1018 "WHERE sql NOT NULL AND type=='table'", 0);
1019 run_schema_dump_query(p,
1020 "SELECT name, type, sql FROM sqlite_master "
1021 "WHERE sql NOT NULL AND type!='table' AND type!='meta'",
1022 0);
1023 }
1024 else
1025 {
1026 int i;
1027 for(i=1; i<nArg; i++)
1028 {
1029 zShellStatic = azArg[i];
1030 run_schema_dump_query(p,
1031 "SELECT name, type, sql FROM sqlite_master "
1032 "WHERE tbl_name LIKE shellstatic() AND type=='table'"
1033 " AND sql NOT NULL", 0);
1034 run_schema_dump_query(p,
1035 "SELECT name, type, sql FROM sqlite_master "
1036 "WHERE tbl_name LIKE shellstatic() AND type!='table'"
1037 " AND type!='meta' AND sql NOT NULL", 0);
1038 zShellStatic = 0;
1039 }
1040 }
1041 if( zErrMsg )
1042 {
1043 fprintf(stderr,"Error: %s\n", zErrMsg);
1044 sqlite3_free(zErrMsg);
1045 }else
1046 fprintf(p->out, "COMMIT;\n");
1047 }
1048 else if( c=='e' && strncmp(azArg[0], "echo", n)==0 && nArg>1 )
1049 {
1050 int j;
1051 char *z = azArg[1];
1052 int val = atoi(azArg[1]);
1053 for(j=0; z[j]; j++)
1054 z[j] = tolower((unsigned char)z[j]);
1055 if( strcmp(z,"on")==0 )
1056 val = 1;
1057 else if( strcmp(z,"yes")==0 )
1058 val = 1;
1059 p->echoOn = val;
1060 }
1061 else if( c=='e' && strncmp(azArg[0], "exit", n)==0 )
1062 rc = TRUE;
1063 else if( c=='e' && strncmp(azArg[0], "explain", n)==0 )
1064 {
1065 int j;
1066 static char zOne[] = "1";
1067 char *z = nArg>=2 ? azArg[1] : zOne;
1068 int val = atoi(z);
1069 for(j=0; z[j]; j++)
1070 z[j] = tolower((unsigned char)z[j]);
1071 if( strcmp(z,"on")==0 )
1072 val = 1;
1073 else if( strcmp(z,"yes")==0 )
1074 val = 1;
1075 if(val == 1)
1076 {
1077 if(!p->explainPrev.valid)
1078 {
1079 p->explainPrev.valid = TRUE;
1080 p->explainPrev.mode = p->mode;
1081 p->explainPrev.showHeader = p->showHeader;
1082 memcpy(p->explainPrev.colWidth,p->colWidth,sizeof(p->colWidth));
1083 }
1084 /*
1085 * We could put this code under the !p->explainValid condition so
1086 * that it does not execute if we are already in explain mode.
1087 * However, always executing it allows us an easy way to reset to
1088 * explain mode in case the user previously did an .explain
1089 * followed by a .width, .mode or .header command.
1090 * */
1091 p->mode = MODE_Column;
1092 p->showHeader = TRUE;
1093 memset(p->colWidth,0,ArraySize(p->colWidth));
1094 p->colWidth[0] = 4;
1095 p->colWidth[1] = 12;
1096 p->colWidth[2] = 10;
1097 p->colWidth[3] = 10;
1098 p->colWidth[4] = 35;
1099 }
1100 else if (p->explainPrev.valid)
1101 {
1102 p->explainPrev.valid = FALSE;
1103 p->mode = p->explainPrev.mode;
1104 p->showHeader = p->explainPrev.showHeader;
1105 memcpy(p->colWidth,p->explainPrev.colWidth,sizeof(p->colWidth));
1106 }
1107 }
1108 else if( c=='h' && (strncmp(azArg[0], "header", n)==0
1109 || strncmp(azArg[0], "headers", n)==0 )&& nArg>1 )
1110 {
1111 int j;
1112 char *z = azArg[1];
1113 int val = atoi(azArg[1]);
1114 for(j=0; z[j]; j++)
1115 z[j] = tolower((unsigned char)z[j]);
1116 if( strcmp(z,"on")==0 )
1117 val = 1;
1118 else if( strcmp(z,"yes")==0 )
1119 val = 1;
1120 p->showHeader = val;
1121 }
1122 else if( c=='h' && strncmp(azArg[0], "help", n)==0 )
1123 fprintf(stderr,zHelp);
1124 else if( c=='i' && strncmp(azArg[0], "import", n)==0 && nArg>=3 )
1125 {
1126 char *zTable = azArg[2]; /* Insert data into this table */
1127 char *zFile = azArg[1]; /* The file from which to extract data */
1128 sqlite3_stmt *pStmt; /* A statement */
1129 int rc; /* Result code */
1130 int nCol; /* Number of columns in the table */
1131 int nByte; /* Number of bytes in an SQL string */
1132 int i, j; /* Loop counters */
1133 int nSep; /* Number of bytes in p->separator[] */
1134 STRPTR zSql; /* An SQL statement */
1135 STRPTR zLine; /* A single line of input from the file */
1136 char **azCol; /* zLine[] broken up into columns */
1137 STRPTR zCommit; /* How to commit changes */
1138 FILE *in; /* The input file */
1139 int lineno = 0; /* Line number of input file */
1140
1141 nSep = strlen(p->separator);
1142 if( nSep==0 )
1143 {
1144 fprintf(stderr, "non-null separator required for import\n");
1145 return 0;
1146 }
1147 zSql = sqlite3_mprintf("SELECT * FROM '%q'", zTable);
1148 if( !zSql ) return 0;
1149 nByte = strlen(zSql);
1150 rc = sqlite3_prepare(p->db, zSql, 0, &pStmt, 0);
1151 sqlite3_free(zSql);
1152 if( rc )
1153 {
1154 fprintf(stderr,"Error: %s\n", sqlite3_errmsg(db));
1155 nCol = 0;
1156 }
1157 else
1158 nCol = sqlite3_column_count(pStmt);
1159 sqlite3_finalize(pStmt);
1160 if( nCol==0 ) return 0;
1161 zSql = malloc( nByte + 20 + nCol*2 );
1162 if( zSql==0 ) return 0;
1163 sqlite3_snprintf(nByte+20, zSql, "INSERT INTO '%q' VALUES(?", zTable);
1164 j = strlen(zSql);
1165 for(i=1; i<nCol; i++)
1166 {
1167 zSql[j++] = ',';
1168 zSql[j++] = '?';
1169 }
1170 zSql[j++] = ')';
1171 zSql[j] = 0;
1172 rc = sqlite3_prepare(p->db, zSql, 0, &pStmt, 0);
1173 free(zSql);
1174 if( rc )
1175 {
1176 fprintf(stderr, "Error: %s\n", sqlite3_errmsg(db));
1177 sqlite3_finalize(pStmt);
1178 return 0;
1179 }
1180 in = fopen(zFile, "rb");
1181 if( in==0 )
1182 {
1183 fprintf(stderr, "cannot open file: %s\n", zFile);
1184 sqlite3_finalize(pStmt);
1185 return 0;
1186 }
1187 azCol = malloc( sizeof(azCol[0])*(nCol+1) );
1188 if( azCol==0 ) return 0;
1189 sqlite3_exec(p->db, "BEGIN", 0, 0, 0);
1190 zCommit = "COMMIT";
1191 while( (zLine = local_getline(0, in)))
1192 {
1193 STRPTR z;
1194 i = 0;
1195 lineno++;
1196 azCol[0] = zLine;
1197 for(i=0, z=zLine; *z && *z!='\n' && *z!='\r'; z++)
1198 {
1199 if( *z==p->separator[0] && strncmp(z, p->separator, nSep)==0 )
1200 {
1201 *z = '\0';
1202 i++;
1203 if( i<nCol )
1204 {
1205 azCol[i] = &z[nSep];
1206 z += nSep-1;
1207 }
1208 }
1209 }
1210 if( i+1!=nCol )
1211 {
1212 fprintf(stderr, "%s line %d: expected %d columns of data but "
1213 "found %d\n", zFile, lineno, nCol, i+1);
1214 zCommit = "ROLLBACK";
1215 break;
1216 }
1217 for(i=0; i<nCol; i++)
1218 sqlite3_bind_text(pStmt, i+1, azCol[i], -1, SQLITE_STATIC);
1219 sqlite3_step(pStmt);
1220 rc = sqlite3_reset(pStmt);
1221 free(zLine);
1222 if( rc!=SQLITE_OK )
1223 {
1224 fprintf(stderr,"Error: %s\n", sqlite3_errmsg(db));
1225 zCommit = "ROLLBACK";
1226 break;
1227 }
1228 }
1229 free(azCol);
1230 fclose(in);
1231 sqlite3_finalize(pStmt);
1232 sqlite3_exec(p->db, zCommit, 0, 0, 0);
1233 }
1234 else if( c=='i' && strncmp(azArg[0], "indices", n)==0 && nArg>1 )
1235 {
1236 struct callback_data data;
1237 char* zErrMsg = NULL;
1238 open_db(p);
1239 memcpy(&data, p, sizeof(data));
1240 data.showHeader = FALSE;
1241 data.mode = MODE_List;
1242 zShellStatic = azArg[1];
1243 sqlite3_exec(p->db,
1244 "SELECT name FROM sqlite_master "
1245 "WHERE type='index' AND tbl_name LIKE shellstatic() "
1246 "UNION ALL "
1247 "SELECT name FROM sqlite_temp_master "
1248 "WHERE type='index' AND tbl_name LIKE shellstatic() "
1249 "ORDER BY 1",
1250 callback, &data, &zErrMsg );
1251 zShellStatic = NULL;
1252 if( zErrMsg )
1253 {
1254 fprintf(stderr,"Error: %s\n", zErrMsg);
1255 sqlite3_free(zErrMsg);
1256 }
1257 }
1258 else if( c=='m' && strncmp(azArg[0], "mode", n)==0 && nArg>=2 )
1259 {
1260 int n2 = strlen(azArg[1]);
1261 if( strncmp(azArg[1],"line",n2)==0
1262 || strncmp(azArg[1],"lines",n2)==0 )
1263 p->mode = MODE_Line;
1264 else if( strncmp(azArg[1],"column",n2)==0
1265 || strncmp(azArg[1],"columns",n2)==0 )
1266 p->mode = MODE_Column;
1267 else if( strncmp(azArg[1],"list",n2)==0 )
1268 p->mode = MODE_List;
1269 else if( strncmp(azArg[1],"html",n2)==0 )
1270 p->mode = MODE_Html;
1271 else if( strncmp(azArg[1],"tcl",n2)==0 )
1272 p->mode = MODE_Tcl;
1273 else if( strncmp(azArg[1],"csv",n2)==0 )
1274 {
1275 p->mode = MODE_Csv;
1276 strcpy(p->separator, ",");
1277 }
1278 else if( strncmp(azArg[1],"tabs",n2)==0 )
1279 {
1280 p->mode = MODE_List;
1281 strcpy(p->separator, "\t");
1282 }
1283 else if( strncmp(azArg[1],"insert",n2)==0 )
1284 {
1285 p->mode = MODE_Insert;
1286 if( nArg>=3 )
1287 set_table_name(p, azArg[2]);
1288 else
1289 set_table_name(p, "table");
1290 }
1291 else
1292 fprintf(stderr,"mode should be on of: "
1293 "column csv html insert line list tabs tcl\n");
1294 }
1295 else if( c=='n' && strncmp(azArg[0], "nullvalue", n)==0 && nArg==2 )
1296 sprintf(p->nullvalue, "%.*s", (int)ArraySize(p->nullvalue)-1, azArg[1]);
1297 else if( c=='o' && strncmp(azArg[0], "output", n)==0 && nArg==2 )
1298 {
1299 if( p->out!=stdout )
1300 fclose(p->out);
1301 if( strcmp(azArg[1],"stdout")==0 )
1302 {
1303 p->out = stdout;
1304 strcpy(p->outfile,"stdout");
1305 }
1306 else
1307 {
1308 p->out = fopen(azArg[1], "wb");
1309 if( p->out==0 )
1310 {
1311 fprintf(stderr,"can't write to \"%s\"\n", azArg[1]);
1312 p->out = stdout;
1313 }
1314 else strcpy(p->outfile,azArg[1]);
1315 }
1316 }
1317 else if( c=='p' && strncmp(azArg[0], "prompt", n)==0
1318 && (nArg==2 || nArg==3))
1319 {
1320 if( nArg >= 2)
1321 strncpy(mainPrompt,azArg[1],(int)ArraySize(mainPrompt)-1);
1322 if( nArg >= 3)
1323 strncpy(continuePrompt,azArg[2],(int)ArraySize(continuePrompt)-1);
1324 }
1325 else if( c=='q' && strncmp(azArg[0], "quit", n)==0 )
1326 rc = 1;
1327 else if( c=='r' && strncmp(azArg[0], "read", n)==0 && nArg==2 )
1328 {
1329 FILE *alt = fopen(azArg[1], "rb");
1330 if( alt==0 )
1331 fprintf(stderr,"can't open \"%s\"\n", azArg[1]);
1332 else
1333 {
1334 process_input(p, alt);
1335 fclose(alt);
1336 }
1337 }
1338 else if(
1339 #ifdef SQLITE_HAS_CODEC
1340 c=='r' && strncmp(azArg[0],"rekey", n)==0 && nArg==4 )
1341 {
1342 char *zOld = p->zKey;
1343 if( zOld==0 ) zOld = "";
1344 if( strcmp(azArg[1],zOld) )
1345 fprintf(stderr,"old key is incorrect\n");
1346 else if( strcmp(azArg[2], azArg[3]) )
1347 fprintf(stderr,"2nd copy of new key does not match the 1st\n");
1348 else
1349 {
1350 sqlite3_free(p->zKey);
1351 p->zKey = sqlite3_mprintf("%s", azArg[2]);
1352 sqlite3_rekey(p->db, p->zKey, strlen(p->zKey));
1353 }
1354 }
1355 else if(
1356 #endif
1357 c=='s' && strncmp(azArg[0], "schema", n)==0 )
1358 {
1359 struct callback_data data;
1360 char *zErrMsg = NULL;
1361 open_db(p);
1362 memcpy(&data, p, sizeof(data));
1363 data.showHeader = FALSE;
1364 data.mode = MODE_Semi;
1365 if( nArg>1 )
1366 {
1367 int i;
1368 for(i=0; azArg[1][i]; i++) azArg[1][i] = tolower(azArg[1][i]);
1369 if( strcmp(azArg[1],"sqlite_master")==0 )
1370 {
1371 char *new_argv[2], *new_colv[2];
1372 new_argv[0] = "CREATE TABLE sqlite_master (\n"
1373 " type text,\n"
1374 " name text,\n"
1375 " tbl_name text,\n"
1376 " rootpage integer,\n"
1377 " sql text\n"
1378 ")";
1379 new_argv[1] = 0;
1380 new_colv[0] = "sql";
1381 new_colv[1] = 0;
1382 callback(&data, 1, new_argv, new_colv);
1383 }
1384 else if( strcmp(azArg[1],"sqlite_temp_master")==0 )
1385 {
1386 char *new_argv[2], *new_colv[2];
1387 new_argv[0] = "CREATE TEMP TABLE sqlite_temp_master (\n"
1388 " type text,\n"
1389 " name text,\n"
1390 " tbl_name text,\n"
1391 " rootpage integer,\n"
1392 " sql text\n"
1393 ")";
1394 new_argv[1] = 0;
1395 new_colv[0] = "sql";
1396 new_colv[1] = 0;
1397 callback(&data, 1, new_argv, new_colv);
1398 }
1399 else
1400 {
1401 zShellStatic = azArg[1];
1402 sqlite3_exec(p->db,
1403 "SELECT sql FROM "
1404 " (SELECT * FROM sqlite_master UNION ALL"
1405 " SELECT * FROM sqlite_temp_master) "
1406 "WHERE tbl_name LIKE shellstatic() "
1407 "AND type!='meta' AND sql NOTNULL "
1408 "ORDER BY substr(type,2,1), name",
1409 callback, &data, &zErrMsg);
1410 zShellStatic = NULL;
1411 }
1412 }
1413 else sqlite3_exec(p->db,
1414 "SELECT sql FROM "
1415 " (SELECT * FROM sqlite_master UNION ALL"
1416 " SELECT * FROM sqlite_temp_master) "
1417 "WHERE type!='meta' AND sql NOTNULL "
1418 "ORDER BY substr(type,2,1), name",
1419 callback, &data, &zErrMsg );
1420
1421 if( zErrMsg )
1422 {
1423 fprintf(stderr,"Error: %s\n", zErrMsg);
1424 sqlite3_free(zErrMsg);
1425 }
1426 }
1427 else if( c=='s' && strncmp(azArg[0], "separator", n)==0 && nArg==2 )
1428 sprintf(p->separator, "%.*s", (int)ArraySize(p->separator)-1, azArg[1]);
1429 else if( c=='s' && strncmp(azArg[0], "show", n)==0)
1430 {
1431 int i;
1432 fprintf(p->out,"%9.9s: %s\n","echo", p->echoOn ? "on" : "off");
1433 fprintf(p->out,"%9.9s: %s\n","explain", p->explainPrev.valid
1434 ? "on" :"off");
1435 fprintf(p->out,"%9.9s: %s\n","headers", p->showHeader ? "on" : "off");
1436 fprintf(p->out,"%9.9s: %s\n","mode", modeDescr[p->mode]);
1437 fprintf(p->out,"%9.9s: ", "nullvalue");
1438 output_c_string(p->out, p->nullvalue);
1439 fprintf(p->out, "\n");
1440 fprintf(p->out,"%9.9s: %s\n","output",
1441 strlen(p->outfile) ? p->outfile : "stdout");
1442 fprintf(p->out,"%9.9s: ", "separator");
1443 output_c_string(p->out, p->separator);
1444 fprintf(p->out, "\n");
1445 fprintf(p->out,"%9.9s: ","width");
1446 for (i=0;i<(int)ArraySize(p->colWidth) && p->colWidth[i] != 0;i++)
1447 fprintf(p->out,"%d ",p->colWidth[i]);
1448 fprintf(p->out,"\n");
1449 }
1450 else if( c=='t' && n>1 && strncmp(azArg[0], "tables", n)==0 )
1451 {
1452 char **azResult;
1453 int nRow, rc;
1454 char *zErrMsg;
1455 open_db(p);
1456 if( nArg==1 )
1457 rc = sqlite3_get_table(p->db,
1458 "SELECT name FROM sqlite_master "
1459 "WHERE type IN ('table','view') "
1460 "UNION ALL "
1461 "SELECT name FROM sqlite_temp_master "
1462 "WHERE type IN ('table','view') "
1463 "ORDER BY 1",
1464 &azResult, &nRow, 0, &zErrMsg );
1465 else
1466 {
1467 zShellStatic = azArg[1];
1468 rc = sqlite3_get_table(p->db,
1469 "SELECT name FROM sqlite_master "
1470 "WHERE type IN ('table','view') "
1471 "AND name LIKE '%'||shellstatic()||'%' "
1472 "UNION ALL "
1473 "SELECT name FROM sqlite_temp_master "
1474 "WHERE type IN ('table','view') "
1475 "AND name LIKE '%'||shellstatic()||'%' "
1476 "ORDER BY 1",
1477 &azResult, &nRow, 0, &zErrMsg
1478 );
1479 zShellStatic = NULL;
1480 }
1481 if( zErrMsg )
1482 {
1483 fprintf(stderr,"Error: %s\n", zErrMsg);
1484 sqlite3_free(zErrMsg);
1485 }
1486 if( rc==SQLITE_OK )
1487 {
1488 int len, maxlen = 0;
1489 int i, j;
1490 int nPrintCol, nPrintRow;
1491 for(i=1; i<=nRow; i++)
1492 {
1493 if( azResult[i]==0 ) continue;
1494 len = strlen(azResult[i]);
1495 if( len>maxlen ) maxlen = len;
1496 }
1497 nPrintCol = 80/(maxlen+2);
1498 if( nPrintCol<1 ) nPrintCol = 1;
1499 nPrintRow = (nRow + nPrintCol - 1)/nPrintCol;
1500 for(i=0; i<nPrintRow; i++)
1501 {
1502 for(j=i+1; j<=nRow; j+=nPrintRow)
1503 {
1504 STRPTR zSp = j<=nPrintRow ? "" : " ";
1505 printf("%s%-*s", zSp, maxlen,
1506 azResult[j] ? azResult[j] : "");
1507 }
1508 printf("\n");
1509 }
1510 }
1511 sqlite3_free_table(azResult);
1512 }
1513 else if( c=='t' && n>1 && strncmp(azArg[0], "timeout", n)==0 && nArg>=2 )
1514 {
1515 open_db(p);
1516 sqlite3_busy_timeout(p->db, atoi(azArg[1]));
1517 }
1518 else if( c=='w' && strncmp(azArg[0], "width", n)==0 )
1519 {
1520 int j;
1521 for(j=1; j<nArg && j<ArraySize(p->colWidth); j++)
1522 p->colWidth[j-1] = atoi(azArg[j]);
1523 }
1524 else fprintf(stderr, "unknown command or invalid arguments: "
1525 " \"%s\". Enter \".help\" for help\n", azArg[0]);
1526
1527 return rc;
1528 }
1529
1530
1531 /*
1532 * Return TRUE if the last non-whitespace character in z[] is a semicolon.
1533 * z[] is N characters long.
1534 * */
1535 static int _ends_with_semicolon(CONST_STRPTR z, int N)
1536 {
1537 while( N>0 && isspace((unsigned char)z[N-1]) ){ N--; }
1538 return N>0 && z[N-1]==';';
1539 }
1540
1541
1542 /*
1543 * Test to see if a line consists entirely of whitespace.
1544 * */
1545 static BOOL _all_whitespace(CONST_STRPTR z)
1546 {
1547 for(; *z; z++)
1548 {
1549 if( isspace(*(unsigned char*)z) ) continue;
1550 if( *z=='/' && z[1]=='*' )
1551 {
1552 z += 2;
1553 while( *z && (*z!='*' || z[1]!='/') ){ z++; }
1554 if( *z=='\0' ) return FALSE;
1555 z++;
1556 continue;
1557 }
1558 if( *z=='-' && z[1]=='-' )
1559 {
1560 z += 2;
1561 while( *z && *z!='\n' ){ z++; }
1562 if( *z=='\0' ) return TRUE;
1563 continue;
1564 }
1565 return FALSE;
1566 }
1567 return TRUE;
1568 }
1569
1570
1571 /*
1572 * Return TRUE if the line typed in is an SQL command terminator other than a
1573 * semi-colon. The SQL Server style "go" command is understood as is the Oracle
1574 * "/".
1575 * */
1576 static BOOL _is_command_terminator(const char *zLine)
1577 {
1578 while( isspace(*(unsigned char*)zLine) ){ zLine++; };
1579 if( zLine[0]=='/' && _all_whitespace(&zLine[1]) ) return 1; /* Oracle */
1580 if( tolower(zLine[0])=='g' && tolower(zLine[1])=='o'
1581 && _all_whitespace(&zLine[2]) )
1582 return TRUE; /* SQL Server */
1583 return FALSE;
1584 }
1585
1586
1587 /*
1588 * Read input from *in and process it. If *in==0 then input is interactive -
1589 * the user is typing it it. Otherwise, input is coming from a file or device.
1590 * A prompt is issued and history is saved only if input is interactive. An
1591 * interrupt signal will cause this routine to exit immediately, unless input
1592 * is interactive.
1593 * */
1594 static void process_input(struct callback_data *p, FILE *in)
1595 {
1596 STRPTR zLine;
1597 STRPTR zSql = NULL;
1598 int nSql = 0;
1599 char *zErrMsg;
1600 int rc;
1601
1602 while( fflush(p->out), (zLine = one_input_line(zSql, in))!=0 )
1603 {
1604 if( seenInterrupt )
1605 {
1606 if( in ) break;
1607 seenInterrupt = FALSE;
1608 }
1609 if( p->echoOn ) printf("%s\n", zLine);
1610 if( (zSql==0 || zSql[0]==0) && _all_whitespace(zLine) ) continue;
1611 if( zLine && zLine[0]=='.' && nSql==0 )
1612 {
1613 int rc = do_meta_command(zLine, p);
1614 free(zLine);
1615 if( rc ) break;
1616 continue;
1617 }
1618 if( _is_command_terminator(zLine) )
1619 strcpy(zLine,";");
1620 if( !zSql )
1621 {
1622 int i;
1623 for(i=0; zLine[i] && isspace((unsigned char)zLine[i]); i++){}
1624 if( zLine[i]!=0 )
1625 {
1626 nSql = strlen(zLine);
1627 zSql = malloc( nSql+1 );
1628 strcpy(zSql, zLine);
1629 }
1630 }
1631 else
1632 {
1633 int len = strlen(zLine);
1634 zSql = realloc( zSql, nSql + len + 2 );
1635 if( !zSql )
1636 {
1637 fprintf(stderr,"%s: out of memory!\n", Argv0);
1638 exit(1);
1639 }
1640 strcpy(&zSql[nSql++], "\n");
1641 strcpy(&zSql[nSql], zLine);
1642 nSql += len;
1643 }
1644 free(zLine);
1645 if( zSql && _ends_with_semicolon(zSql, nSql)
1646 && sqlite3_complete(zSql) )
1647 {
1648 p->cnt = 0;
1649 open_db(p);
1650 rc = sqlite3_exec(p->db, zSql, callback, p, &zErrMsg);
1651 if( rc || zErrMsg )
1652 {
1653 if( in && !p->echoOn ) printf("%s\n",zSql);
1654 if( zErrMsg )
1655 {
1656 #if defined( SQLITE_DEBUG ) && defined( OS_AROS )
1657 printf("SQL error: %s in %s\n", zErrMsg, arosErrLoc );
1658 ERRLOC( NULL );
1659 #else
1660 printf("SQL error: %s\n", zErrMsg);
1661 #endif
1662 sqlite3_free(zErrMsg);
1663 zErrMsg = NULL;
1664 }
1665 else
1666 {
1667 #if defined( SQLITE_DEBUG ) && defined( OS_AROS )
1668 printf("SQL error: %s in %s\n", zErrMsg, arosErrLoc );
1669 ERRLOC( NULL );
1670 #else
1671 printf("SQL error: %s\n", sqlite3_errmsg(p->db));
1672 #endif
1673 }
1674 }
1675 free(zSql);
1676 zSql = NULL;
1677 nSql = 0;
1678 }
1679 }
1680 if( zSql )
1681 {
1682 if( !_all_whitespace(zSql) ) printf("Incomplete SQL: %s\n", zSql);
1683 free(zSql);
1684 }
1685 }
1686
1687
1688 /*
1689 * Return a pathname which is the user's home directory. A NULL return
1690 * indicates an error of some kind. Space to hold the resulting string is
1691 * obtained from malloc(). The calling function should free the result.
1692 * */
1693 static STRPTR find_home_dir(void)
1694 {
1695 STRPTR home_dir = NULL;
1696 #if !defined(_WIN32) && !defined(WIN32) && !defined(__MACOS__)
1697 struct passwd *pwent;
1698 uid_t uid = getuid();
1699 if( (pwent=getpwuid(uid)) != NULL)
1700 home_dir = pwent->pw_dir;
1701 #endif
1702
1703 #ifdef __MACOS__
1704 char home_path[_MAX_PATH+1];
1705 home_dir = getcwd(home_path, _MAX_PATH);
1706 #endif
1707
1708 if (!home_dir)
1709 {
1710 home_dir = getenv("HOME");
1711 if (!home_dir)
1712 home_dir = getenv("HOMEPATH"); /* Windows? */
1713 }
1714
1715 #if defined(_WIN32) || defined(WIN32)
1716 if (!home_dir)
1717 home_dir = "c:";
1718 #endif
1719 #if defined(OS_AROS)
1720 if (!home_dir)
1721 home_dir = "PROGDIR:";
1722 #endif
1723
1724 if( home_dir )
1725 {
1726 STRPTR z = malloc( strlen(home_dir)+1 );
1727 if( z ) strcpy(z, home_dir);
1728 home_dir = z;
1729 }
1730 return home_dir;
1731 }
1732
1733
1734 /*
1735 * Read input from the file given by sqliterc_override. Or if that parameter is
1736 * NULL, take input from ~/.sqliterc
1737 * */
1738 static void process_sqliterc(
1739 struct callback_data *p, /* Configuration data */
1740 CONST_STRPTR sqliterc_override /* Name of config file.
1741 NULL to use default */
1742 )
1743 {
1744 STRPTR home_dir = NULL;
1745 CONST_STRPTR sqliterc = sqliterc_override;
1746 STRPTR zBuf;
1747 FILE *in = NULL;
1748
1749 if (sqliterc == NULL)
1750 {
1751 home_dir = find_home_dir();
1752 if( !home_dir )
1753 {
1754 fprintf(stderr,"%s: cannot locate your home directory!\n", Argv0);
1755 return;
1756 }
1757 zBuf = malloc(strlen(home_dir) + 15);
1758 if( !zBuf )
1759 {
1760 fprintf(stderr,"%s: out of memory!\n", Argv0);
1761 exit(1);
1762 }
1763 sprintf(zBuf,"%s/.sqliterc",home_dir);
1764 free(home_dir);
1765 sqliterc = (CONST_STRPTR)zBuf;
1766 }
1767 in = fopen(sqliterc,"rb");
1768 if( in )
1769 {
1770 if( isatty(fileno(stdout)) )
1771 {
1772 printf("Loading resources from %s\n",sqliterc);
1773 }
1774 process_input(p,in);
1775 fclose(in);
1776 }
1777 }
1778
1779
1780 /*
1781 * Show available command line options.
1782 * */
1783 static const char zOptions[] =
1784 " -init filename read/process named file\n"
1785 " -echo print commands before execution\n"
1786 " -[no]header turn headers on or off\n"
1787 " -column set output mode to 'column'\n"
1788 " -html set output mode to HTML\n"
1789 #ifdef SQLITE_HAS_CODEC
1790 " -key KEY encryption key\n"
1791 #endif
1792 " -line set output mode to 'line'\n"
1793 " -list set output mode to 'list'\n"
1794 " -separator 'x' set output field separator (|)\n"
1795 " -nullvalue 'text' set text string for NULL values\n"
1796 " -version show SQLite version\n"
1797 " -help show this text, also show dot-commands\n"
1798 ;
1799 static void usage(BOOL showDetail)
1800 {
1801 fprintf(stderr, "Usage: %s [OPTIONS] FILENAME [SQL]\n", Argv0);
1802 if( showDetail )
1803 fprintf(stderr, "Options are:\n%s", zOptions);
1804 else
1805 fprintf(stderr, "Use the -help option for additional information\n");
1806 exit(1);
1807 }
1808
1809
1810 /*
1811 * Initialize the state information in data.
1812 * */
1813 void main_init(struct callback_data *data)
1814 {
1815 memset(data, 0, sizeof(*data));
1816 data->mode = MODE_List;
1817 strcpy(data->separator,"|");
1818 data->showHeader = FALSE;
1819 strcpy(mainPrompt,"sqlite> ");
1820 strcpy(continuePrompt," ...> ");
1821 }
1822
1823
1824 int main(int argc, char **argv)
1825 {
1826 char *zErrMsg = NULL;
1827 struct callback_data data;
1828 CONST_STRPTR zInitFile = NULL;
1829 STRPTR zFirstCmd = NULL;
1830 int i;
1831
1832 #ifdef __MACOS__
1833 argc = ccommand(&argv);
1834 #endif
1835
1836 Argv0 = argv[0];
1837 main_init(&data);
1838
1839 /*
1840 * Make sure we have a valid signal handler early, before anything else is
1841 * done.
1842 * */
1843 #ifdef SIGINT
1844 signal(SIGINT, interrupt_handler);
1845 #endif
1846
1847 /*
1848 * Do an initial pass through the command-line argument to locate the name
1849 * of the database file, the name of the initialization file, and the first
1850 * command to execute.
1851 * */
1852 for(i=1; i<argc-1; i++)
1853 {
1854 if( argv[i][0]!='-' ) break;
1855 if( strcmp(argv[i],"-separator")==0 || strcmp(argv[i],"-nullvalue")==0 )
1856 i++;
1857 else if( strcmp(argv[i],"-init")==0 )
1858 {
1859 i++;
1860 zInitFile = argv[i];
1861 }
1862 else if( strcmp(argv[i],"-key")==0 )
1863 {
1864 i++;
1865 data.zKey = sqlite3_mprintf("%s",argv[i]);
1866 }
1867 }
1868 if( i<argc )
1869 data.zDbFilename = argv[i++];
1870 else
1871 {
1872 #ifndef SQLITE_OMIT_MEMORYDB
1873 data.zDbFilename = ":memory:";
1874 #else
1875 data.zDbFilename = NULL;
1876 #endif
1877 }
1878 if( i<argc )
1879 zFirstCmd = argv[i++];
1880 data.out = stdout;
1881
1882 /*
1883 * Go ahead and open the database file if it already exists. If the file
1884 * does not exist, delay opening it. This prevents empty database files
1885 * from being created if a user mistypes the database name argument to the
1886 * sqlite command-line tool.
1887 * */
1888 if( access(data.zDbFilename, 0)==0 )
1889 open_db(&data);
1890
1891 /*
1892 * Process the initialization file if there is one. If no -init option is
1893 * given on the command line, look for a file named ~/.sqliterc and try to
1894 * process it.
1895 * */
1896 process_sqliterc(&data,zInitFile);
1897
1898 /*
1899 * Make a second pass through the command-line argument and set options.
1900 * This second pass is delayed until after the initialization file is
1901 * processed so that the command-line arguments will override settings in
1902 * the initialization file.
1903 * */
1904 for(i=1; i<argc && argv[i][0]=='-'; i++)
1905 {
1906 char *z = argv[i];
1907 if( strcmp(z,"-init")==0 || strcmp(z,"-key")==0 )
1908 i++;
1909 else if( strcmp(z,"-html")==0 )
1910 data.mode = MODE_Html;
1911 else if( strcmp(z,"-list")==0 )
1912 data.mode = MODE_List;
1913 else if( strcmp(z,"-line")==0 )
1914 data.mode = MODE_Line;
1915 else if( strcmp(z,"-column")==0 )
1916 data.mode = MODE_Column;
1917 else if( strcmp(z,"-separator")==0 )
1918 {
1919 i++;
1920 sprintf(data.separator,"%.*s",(int)sizeof(data.separator)-1,
1921 argv[i]);
1922 }
1923 else if( strcmp(z,"-nullvalue")==0 )
1924 {
1925 i++;
1926 sprintf(data.nullvalue,"%.*s",(int)sizeof(data.nullvalue)-1,
1927 argv[i]);
1928 }
1929 else if( strcmp(z,"-header")==0 )
1930 data.showHeader = TRUE;
1931 else if( strcmp(z,"-noheader")==0 )
1932 data.showHeader = FALSE;
1933 else if( strcmp(z,"-echo")==0 )
1934 data.echoOn = TRUE;
1935 else if( strcmp(z,"-version")==0 )
1936 {
1937 printf("%s\n", sqlite3_libversion());
1938 return 1;
1939 }
1940 else if( strcmp(z,"-help")==0 )
1941 usage(1);
1942 else
1943 {
1944 fprintf(stderr,"%s: unknown option: %s\n", Argv0, z);
1945 fprintf(stderr,"Use -help for a list of options.\n");
1946 return 1;
1947 }
1948 }
1949
1950 if( zFirstCmd )
1951 {
1952 /* Run just the command that follows the database name. */
1953 if( zFirstCmd[0]=='.' )
1954 {
1955 do_meta_command(zFirstCmd, &data);
1956 exit(0);
1957 }
1958 else
1959 {
1960 int rc;
1961 open_db(&data);
1962 rc = sqlite3_exec(data.db, zFirstCmd, callback, &data, &zErrMsg);
1963 if( rc!=0 && zErrMsg!=0 )
1964 {
1965 fprintf(stderr,"SQL error: %s\n", zErrMsg);
1966 exit(1);
1967 }
1968 }
1969 }
1970 else
1971 {
1972 /* Run commands received from standard input. */
1973 if( isatty(fileno(stdout)) && isatty(fileno(stdin)) )
1974 {
1975 STRPTR zHome;
1976 STRPTR zHistory = NULL;
1977 printf(
1978 "SQLite version %s\n"
1979 "Enter \".help\" for instructions\n",
1980 sqlite3_libversion()
1981 );
1982 zHome = find_home_dir();
1983 if( zHome && (zHistory = malloc(strlen(zHome)+20))!=0 )
1984 sprintf(zHistory,"%s/.sqlite_history", zHome);
1985 #if defined(HAVE_READLINE) && HAVE_READLINE==1
1986 if( zHistory ) read_history(zHistory);
1987 #endif
1988 process_input(&data, 0);
1989 if( zHistory )
1990 {
1991 stifle_history(100);
1992 write_history(zHistory);
1993 }
1994 }
1995 else process_input(&data, stdin);
1996 }
1997 set_table_name(&data, 0);
1998 if( db ) sqlite3_close(db);
1999 return 0;
2000 }
Mirror of AROS' Contrib svn.