« back to all changes in this revision
Viewing changes to spatialite-tools/shell.c
- browse files at revision 6
- compare with another revision
- download diff
- download tarball
- view history from revision 6
- Committer: Package Import Robot
- Author(s): David Paleino, Francesco Paolo Lovergine, David Paleino
- Date: 2011-11-21 12:10:43 UTC
- mfrom: (4.1.3 sid)
- Revision ID: package-import@ubuntu.com-20111121121043-0g14o2uf0r343a82
Tags: 3.0.0~beta20110817-3
[ Francesco Paolo Lovergine ]
* Fixed linking order for sqlite3 in debian patch 00-systemlibs.patch.
(closes: #638929)
[ David Paleino ]
* Conditionally disable full EPSG initialization (for srs_init.c)
on powerpc, and document what projections are available on that
architecture (Closes: #649302)
* Fixed linking order for sqlite3 in debian patch 00-systemlibs.patch.
(closes: #638929)
[ David Paleino ]
* Conditionally disable full EPSG initialization (for srs_init.c)
on powerpc, and document what projections are available on that
architecture (Closes: #649302)
- files added:
- .pc
- .pc/01-fix_local_build.patch
- .pc/01-fix_local_build.patch/spatialite-tools
- .pc/02-fix_version-info.patch
- .pc/02-fix_version-info.patch/libspatialite
- .pc/02-fix_version-info.patch/libspatialite/src
- .pc/03-fix_typos.patch
- .pc/03-fix_typos.patch/libspatialite
- .pc/03-fix_typos.patch/libspatialite/src
- .pc/03-fix_typos.patch/libspatialite/src/spatialite
- .pc/03-fix_typos.patch/libspatialite/src/srsinit
- .pc/03-fix_typos.patch/spatialite-tools
- .pc/04-fix_missing_define.patch
- .pc/04-fix_missing_define.patch/libspatialite
- .pc/04-fix_missing_define.patch/libspatialite/src
- .pc/04-fix_missing_define.patch/libspatialite/src/headers
- .pc/04-fix_missing_define.patch/libspatialite/src/headers/spatialite
- debian/source
- libspatialite
- libspatialite/src
- libspatialite/src/gaiaaux
- libspatialite/src/gaiaexif
- libspatialite/src/gaiageo
- libspatialite/src/headers
- libspatialite/src/headers/spatialite
- libspatialite/src/spatialite
- libspatialite/src/srsinit
- libspatialite/src/versioninfo
- libspatialite/src/virtualtext
- spatialite-tools
- files removed:
- debian/libspatialite2.install
- upstream
- files modified:
- debian/changelog
added
removed
spatialite-tools/shell.c
1
/*
2
/ spatialite
3
/
4
/ a CLI backend for SpatiaLite
5
/
6
/ version 1.0, 2008 Decmber 11
7
/
8
/ Author: Sandro Furieri a.furieri@lqt.it
9
/
10
/ Copyright (C) 2008 Alessandro Furieri
11
/
12
/ This program is free software: you can redistribute it and/or modify
13
/ it under the terms of the GNU General Public License as published by
14
/ the Free Software Foundation, either version 3 of the License, or
15
/ (at your option) any later version.
16
/
17
/ This program is distributed in the hope that it will be useful,
18
/ but WITHOUT ANY WARRANTY; without even the implied warranty of
19
/ MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
20
/ GNU General Public License for more details.
21
/
22
/ You should have received a copy of the GNU General Public License
23
/ along with this program. If not, see <http://www.gnu.org/licenses/>.
24
/
25
*/
26
27
/*
28
29
DISCLAIMER
30
==========
31
32
This file is the original SQLite command-line backend
33
slightly modified by Alessandro Furieri in order to
34
fully support the SpatiaLite extensions
35
36
*/
37
38
/* Sandro Furieri 30 May 2008
39
/ #include "sqlite3.h"
40
*/
41
#ifdef SPATIALITE_AMALGAMATION
42
#include <spatialite/sqlite3.h>
43
#else
44
#include <sqlite3.h>
45
#endif
46
47
#include <spatialite.h>
48
#ifdef __MINGW32__
49
#define LIBICONV_STATIC
50
#endif
51
#include <iconv.h>
52
/* end Sandro Furieri 30 May 2008 */
53
#include <stdlib.h>
54
#include <string.h>
55
#include <stdio.h>
56
#include <assert.h>
57
#include <ctype.h>
58
#include <stdarg.h>
59
60
#if !defined(_WIN32) && !defined(WIN32) && !defined(__OS2__)
61
#include <signal.h>
62
#include <pwd.h>
63
#include <unistd.h>
64
#include <sys/types.h>
65
#endif
66
67
#ifdef __OS2__
68
#include <unistd.h>
69
#endif
70
71
#if defined(HAVE_READLINE) && HAVE_READLINE==1
72
#include <readline/readline.h>
73
#include <readline/history.h>
74
#else
75
#define readline(p) local_getline(p,stdin)
76
#define add_history(X)
77
#define read_history(X)
78
#define write_history(X)
79
#define stifle_history(X)
80
#endif
81
82
#if defined(_WIN32) || defined(WIN32)
83
#include <io.h>
84
#define isatty _isatty
85
#define access _access
86
#else
87
/* Make sure isatty() has a prototype.
88
*/
89
extern int isatty ();
90
#endif
91
92
#if defined(_WIN32_WCE)
93
/* Windows CE (arm-wince-mingw32ce-gcc) does not provide isatty()
94
* thus we always assume that we have a console. That can be
95
* overridden with the -batch command line option.
96
*/
97
#define isatty(x) 1
98
#endif
99
100
#if !defined(_WIN32) && !defined(WIN32) && !defined(__OS2__)
101
#include <sys/time.h>
102
#include <sys/resource.h>
103
104
/* Saved resource information for the beginning of an operation */
105
static struct rusage sBegin;
106
107
/* True if the timer is enabled */
108
static int enableTimer = 0;
109
110
/*
111
** Begin timing an operation
112
*/
113
static void
114
beginTimer (void)
115
{
116
if (enableTimer)
117
{
118
getrusage (RUSAGE_SELF, &sBegin);
119
}
120
}
121
122
/* Return the difference of two time_structs in microseconds */
123
static int
124
timeDiff (struct timeval *pStart, struct timeval *pEnd)
125
{
126
return (pEnd->tv_usec - pStart->tv_usec) +
127
1000000 * (pEnd->tv_sec - pStart->tv_sec);
128
}
129
130
/*
131
** Print the timing results.
132
*/
133
static void
134
endTimer (void)
135
{
136
if (enableTimer)
137
{
138
struct rusage sEnd;
139
getrusage (RUSAGE_SELF, &sEnd);
140
printf ("CPU Time: user %f sys %f\n",
141
0.000001 * timeDiff (&sBegin.ru_utime, &sEnd.ru_utime),
142
0.000001 * timeDiff (&sBegin.ru_stime, &sEnd.ru_stime));
143
}
144
}
145
146
#define BEGIN_TIMER beginTimer()
147
#define END_TIMER endTimer()
148
#define HAS_TIMER 1
149
#else
150
#define BEGIN_TIMER
151
#define END_TIMER
152
#define HAS_TIMER 0
153
#endif
154
155
156
/*
157
** If the following flag is set, then command execution stops
158
** at an error if we are not interactive.
159
*/
160
static int bail_on_error = 0;
161
162
/*
163
** Threat stdin as an interactive input if the following variable
164
** is true. Otherwise, assume stdin is connected to a file or pipe.
165
*/
166
static int stdin_is_interactive = 1;
167
168
/*
169
** The following is the open SQLite database. We make a pointer
170
** to this database a static variable so that it can be accessed
171
** by the SIGINT handler to interrupt database processing.
172
*/
173
static sqlite3 *db = 0;
174
175
/*
176
** True if an interrupt (Control-C) has been received.
177
*/
178
static volatile int seenInterrupt = 0;
179
180
/*
181
** This is the name of our program. It is set in main(), used
182
** in a number of other places, mostly for error messages.
183
*/
184
static char *Argv0;
185
186
/*
187
** Prompt strings. Initialized in main. Settable with
188
** .prompt main continue
189
*/
190
static char mainPrompt[20]; /* First line prompt. default: "sqlite> " */
191
static char continuePrompt[20]; /* Continuation prompt. default: " ...> " */
192
193
/*
194
** Write I/O traces to the following stream.
195
*/
196
#ifdef SQLITE_ENABLE_IOTRACE
197
static FILE *iotrace = 0;
198
#endif
199
200
/*
201
** This routine works like printf in that its first argument is a
202
** format string and subsequent arguments are values to be substituted
203
** in place of % fields. The result of formatting this string
204
** is written to iotrace.
205
*/
206
#ifdef SQLITE_ENABLE_IOTRACE
207
static void
208
iotracePrintf (const char *zFormat, ...)
209
{
210
va_list ap;
211
char *z;
212
if (iotrace == 0)
213
return;
214
va_start (ap, zFormat);
215
z = sqlite3_vmprintf (zFormat, ap);
216
va_end (ap);
217
fprintf (iotrace, "%s", z);
218
sqlite3_free (z);
219
}
220
#endif
221
222
223
224
/*
225
Sandro Furieri 2008-11-20
226
implementing AUTO FDO
227
*/
228
229
struct auto_fdo_table
230
{
231
char *name;
232
struct auto_fdo_table *next;
233
};
234
235
struct auto_fdo_tables
236
{
237
struct auto_fdo_table *first;
238
struct auto_fdo_table *last;
239
};
240
241
static struct auto_fdo_tables *
242
fdo_tables_alloc ()
243
{
244
struct auto_fdo_tables *p = malloc (sizeof (struct auto_fdo_tables));
245
p->first = NULL;
246
p->last = NULL;
247
return p;
248
}
249
250
static void
251
fdo_tables_free (struct auto_fdo_tables *p)
252
{
253
struct auto_fdo_table *pt;
254
struct auto_fdo_table *ptn;
255
if (!p)
256
return;
257
pt = p->first;
258
while (pt)
259
{
260
ptn = pt->next;
261
free (pt->name);
262
free (pt);
263
pt = ptn;
264
}
265
free (p);
266
}
267
268
static void
269
add_to_fdo_tables (struct auto_fdo_tables *pt, const char *name, int len)
270
{
271
struct auto_fdo_table *p = malloc (sizeof (struct auto_fdo_table));
272
p->name = malloc (len + 1);
273
strcpy (p->name, name);
274
p->next = NULL;
275
if (!(pt->first))
276
pt->first = p;
277
if (pt->last)
278
pt->last->next = p;
279
pt->last = p;
280
}
281
282
static void
283
auto_fdo_start (sqlite3 * db)
284
{
285
/* trying to start the FDO-OGR auto-wrapper */
286
int ret;
287
const char *name;
288
int i;
289
char **results;
290
int rows;
291
int columns;
292
char sql[1024];
293
int count = 0;
294
int len;
295
int spatial_type = 0;
296
struct auto_fdo_tables *tables;
297
struct auto_fdo_table *p;
298
if (!db)
299
return;
300
strcpy (sql, "SELECT CheckSpatialMetadata()");
301
ret = sqlite3_get_table (db, sql, &results, &rows, &columns, NULL);
302
if (ret != SQLITE_OK)
303
goto error1;
304
if (rows < 1)
305
;
306
else
307
{
308
for (i = 1; i <= rows; i++)
309
spatial_type = atoi (results[(i * columns) + 0]);
310
}
311
sqlite3_free_table (results);
312
error1:
313
if (spatial_type == 2)
314
{
315
/* ok, creating VirtualFDO tables */
316
tables = fdo_tables_alloc ();
317
strcpy (sql, "SELECT DISTINCT f_table_name FROM geometry_columns");
318
ret = sqlite3_get_table (db, sql, &results, &rows, &columns, NULL);
319
if (ret != SQLITE_OK)
320
goto error;
321
if (rows < 1)
322
;
323
else
324
{
325
for (i = 1; i <= rows; i++)
326
{
327
name = results[(i * columns) + 0];
328
if (name)
329
{
330
len = strlen (name);
331
add_to_fdo_tables (tables, name, len);
332
}
333
}
334
}
335
sqlite3_free_table (results);
336
p = tables->first;
337
if (p)
338
printf
339
("\n================ FDO-OGR Spatial Metadata detected ===============\n");
340
while (p)
341
{
342
/* destroying the VirtualFDO table [if existing] */
343
sprintf (sql, "DROP TABLE IF EXISTS fdo_%s", p->name);
344
ret = sqlite3_exec (db, sql, NULL, 0, NULL);
345
if (ret != SQLITE_OK)
346
goto error;
347
/* creating the VirtualFDO table */
348
sprintf (sql,
349
"CREATE VIRTUAL TABLE fdo_%s USING VirtualFDO(%s)",
350
p->name, p->name);
351
ret = sqlite3_exec (db, sql, NULL, 0, NULL);
352
if (ret != SQLITE_OK)
353
goto error;
354
printf ("\tcreated VirtualFDO table 'fdo_%s'\n", p->name);
355
count++;
356
p = p->next;
357
}
358
error:
359
if (count++)
360
{
361
printf
362
("Accessing these fdo_XX tables you can take full advantage of\n");
363
printf ("FDO-OGR auto-wrapping facility\n");
364
printf
365
("This allows you to access any specific FDO-OGR Geometry as if it\n");
366
printf
367
("where native SpatiaLite ones in a completely transparent way\n");
368
printf
369
("==================================================================\n\n");
370
fdo_tables_free (tables);
371
}
372
return;
373
}
374
}
375
376
static void
377
auto_fdo_stop (sqlite3 * db)
378
{
379
/* trying to stop the FDO-OGR auto-wrapper */
380
int ret;
381
const char *name;
382
int i;
383
char **results;
384
int rows;
385
int columns;
386
char sql[1024];
387
int count = 0;
388
int len;
389
int spatial_type = 0;
390
struct auto_fdo_tables *tables;
391
struct auto_fdo_table *p;
392
if (!db)
393
return;
394
strcpy (sql, "SELECT CheckSpatialMetadata()");
395
ret = sqlite3_get_table (db, sql, &results, &rows, &columns, NULL);
396
if (ret != SQLITE_OK)
397
goto error1;
398
if (rows < 1)
399
;
400
else
401
{
402
for (i = 1; i <= rows; i++)
403
spatial_type = atoi (results[(i * columns) + 0]);
404
}
405
sqlite3_free_table (results);
406
error1:
407
if (spatial_type == 2)
408
{
409
/* ok, destroying VirtualFDO tables */
410
tables = fdo_tables_alloc ();
411
strcpy (sql, "SELECT DISTINCT f_table_name FROM geometry_columns");
412
ret = sqlite3_get_table (db, sql, &results, &rows, &columns, NULL);
413
if (ret != SQLITE_OK)
414
goto error;
415
if (rows < 1)
416
;
417
else
418
{
419
for (i = 1; i <= rows; i++)
420
{
421
name = results[(i * columns) + 0];
422
if (name)
423
{
424
len = strlen (name);
425
add_to_fdo_tables (tables, name, len);
426
}
427
}
428
}
429
sqlite3_free_table (results);
430
p = tables->first;
431
while (p)
432
{
433
/* destroying the VirtualFDO table [if existing] */
434
sprintf (sql, "DROP TABLE IF EXISTS fdo_%s", p->name);
435
ret = sqlite3_exec (db, sql, NULL, 0, NULL);
436
if (ret != SQLITE_OK)
437
goto error;
438
count++;
439
p = p->next;
440
}
441
error:
442
if (count++)
443
printf ("\n*** FDO-OGR auto-wrapping shutdown done ***\n");
444
fdo_tables_free (tables);
445
return;
446
}
447
}
448
449
/* end Sandro Furieri 11 July 2008 */
450
451
452
453
454
/*
455
Sandro Furieri 11 July 2008
456
implementing full UNICODE support
457
*/
458
459
static iconv_t locale_to_utf8 = NULL;
460
static iconv_t utf8_to_locale = NULL;
461
static iconv_t in_charset_to_utf8 = NULL;
462
static char spatialite_charset[1024] = "";
463
464
static void
465
create_utf8_converter (char *charset)
466
{
467
/* creating the UTF8 structs */
468
*spatialite_charset = '\0';
469
if (locale_to_utf8)
470
{
471
/* destroying old converter, if exists */
472
iconv_close (locale_to_utf8);
473
locale_to_utf8 = NULL;
474
}
475
if (utf8_to_locale)
476
{
477
/* destroying old converter, if exists */
478
iconv_close (utf8_to_locale);
479
utf8_to_locale = NULL;
480
}
481
/* creating new converters */
482
locale_to_utf8 = iconv_open ("UTF-8", charset);
483
if (locale_to_utf8 == (iconv_t) (-1))
484
{
485
locale_to_utf8 = NULL;
486
fprintf (stderr,
487
"*** charset ERROR *** cannot convert from '%s' to 'UTF-8'\n",
488
charset);
489
fflush (stderr);
490
return;
491
}
492
utf8_to_locale = iconv_open (charset, "UTF-8");
493
if (utf8_to_locale == (iconv_t) (-1))
494
{
495
utf8_to_locale = NULL;
496
fprintf (stderr,
497
"*** charset ERROR *** cannot convert from 'UTF-8' to '%s'\n",
498
charset);
499
fflush (stderr);
500
return;
501
}
502
strncpy (spatialite_charset, charset, sizeof (spatialite_charset) - 1);
503
spatialite_charset[sizeof (spatialite_charset) - 1] = '\0';
504
}
505
506
static void
507
create_input_utf8_converter (char *charset)
508
{
509
/* creating the UTF8 structs */
510
if (in_charset_to_utf8)
511
{
512
/* destroying old converter, if exists */
513
iconv_close (in_charset_to_utf8);
514
in_charset_to_utf8 = NULL;
515
}
516
/* creating new converter */
517
in_charset_to_utf8 = iconv_open ("UTF-8", charset);
518
if (in_charset_to_utf8 == (iconv_t) (-1))
519
{
520
in_charset_to_utf8 = NULL;
521
fprintf (stderr,
522
"*** charset ERROR *** cannot convert from '%s' to 'UTF-8'\n",
523
charset);
524
fflush (stderr);
525
return;
526
}
527
}
528
529
static void
530
convert_from_utf8 (char *buf, int maxlen)
531
{
532
/* converting from UTF8 to locale charset */
533
char *utf8buf = 0;
534
#ifdef __MINGW32__
535
const char *pBuf;
536
#else
537
char *pBuf;
538
#endif
539
size_t len;
540
size_t utf8len;
541
char *pUtf8buf;
542
if (!utf8_to_locale)
543
return;
544
utf8buf = malloc (maxlen);
545
if (utf8buf == 0)
546
{
547
fprintf (stderr, "out of memory!\n");
548
exit (1);
549
}
550
len = strlen (buf);
551
utf8len = maxlen;
552
pBuf = buf;
553
pUtf8buf = utf8buf;
554
if (iconv (utf8_to_locale, &pBuf, &len, &pUtf8buf, &utf8len) ==
555
(size_t) (-1))
556
{
557
fprintf (stderr, "\n*** ILLEGAL CHARACTER SEQUENCE ***\n\n");
558
fflush (stderr);
559
free (utf8buf);
560
return;
561
}
562
utf8buf[maxlen - utf8len] = '\0';
563
memcpy (buf, utf8buf, (maxlen - utf8len) + 1);
564
free (utf8buf);
565
}
566
567
static void
568
convert_to_utf8 (char *buf, int maxlen)
569
{
570
/* converting from locale charset to UTF8 */
571
char *utf8buf = 0;
572
#ifdef __MINGW32__
573
const char *pBuf;
574
#else
575
char *pBuf;
576
#endif
577
size_t len;
578
size_t utf8len;
579
char *pUtf8buf;
580
if (!locale_to_utf8)
581
return;
582
utf8buf = malloc (maxlen);
583
if (utf8buf == 0)
584
{
585
fprintf (stderr, "out of memory!\n");
586
exit (1);
587
}
588
len = strlen (buf);
589
utf8len = maxlen;
590
pBuf = buf;
591
pUtf8buf = utf8buf;
592
if (iconv (locale_to_utf8, &pBuf, &len, &pUtf8buf, &utf8len) ==
593
(size_t) (-1))
594
{
595
fprintf (stderr, "\n*** ILLEGAL CHARACTER SEQUENCE ***\n\n");
596
fflush (stderr);
597
free (utf8buf);
598
return;
599
}
600
utf8buf[maxlen - utf8len] = '\0';
601
memcpy (buf, utf8buf, (maxlen - utf8len) + 1);
602
free (utf8buf);
603
}
604
605
static void
606
convert_input_to_utf8 (char *buf, int maxlen)
607
{
608
/* converting from required charset to UTF8 */
609
char *utf8buf = 0;
610
#ifdef __MINGW32__
611
const char *pBuf;
612
#else
613
char *pBuf;
614
#endif
615
size_t len;
616
size_t utf8len;
617
char *pUtf8buf;
618
if (!in_charset_to_utf8)
619
return;
620
utf8buf = malloc (maxlen);
621
if (utf8buf == 0)
622
{
623
fprintf (stderr, "out of memory!\n");
624
exit (1);
625
}
626
len = strlen (buf);
627
utf8len = maxlen;
628
pBuf = buf;
629
pUtf8buf = utf8buf;
630
if (iconv (in_charset_to_utf8, &pBuf, &len, &pUtf8buf, &utf8len) ==
631
(size_t) (-1))
632
{
633
fprintf (stderr, "\n*** ILLEGAL CHARACTER SEQUENCE ***\n\n");
634
fflush (stderr);
635
free (utf8buf);
636
return;
637
}
638
utf8buf[maxlen - utf8len] = '\0';
639
memcpy (buf, utf8buf, (maxlen - utf8len) + 1);
640
free (utf8buf);
641
}
642
643
/* end Sandro Furieri 11 July 2008 */
644
645
646
647
/*
648
** Determines if a string is a number of not.
649
*/
650
static int
651
isNumber (const char *z, int *realnum)
652
{
653
if (*z == '-' || *z == '+')
654
z++;
655
if (!isdigit (*z))
656
{
657
return 0;
658
}
659
z++;
660
if (realnum)
661
*realnum = 0;
662
while (isdigit (*z))
663
{
664
z++;
665
}
666
if (*z == '.')
667
{
668
z++;
669
if (!isdigit (*z))
670
return 0;
671
while (isdigit (*z))
672
{
673
z++;
674
}
675
if (realnum)
676
*realnum = 1;
677
}
678
if (*z == 'e' || *z == 'E')
679
{
680
z++;
681
if (*z == '+' || *z == '-')
682
z++;
683
if (!isdigit (*z))
684
return 0;
685
while (isdigit (*z))
686
{
687
z++;
688
}
689
if (realnum)
690
*realnum = 1;
691
}
692
return *z == 0;
693
}
694
695
/*
696
** A global char* and an SQL function to access its current value
697
** from within an SQL statement. This program used to use the
698
** sqlite_exec_printf() API to substitue a string into an SQL statement.
699
** The correct way to do this with sqlite3 is to use the bind API, but
700
** since the shell is built around the callback paradigm it would be a lot
701
** of work. Instead just use this hack, which is quite harmless.
702
*/
703
static const char *zShellStatic = 0;
704
static void
705
shellstaticFunc (sqlite3_context * context, int argc, sqlite3_value ** argv)
706
{
707
if (argv == NULL)
708
argv = NULL; /* suppressing stupid compiler warnings [unused] */
709
assert (0 == argc);
710
assert (zShellStatic);
711
sqlite3_result_text (context, zShellStatic, -1, SQLITE_STATIC);
712
}
713
714
715
/*
716
** This routine reads a line of text from FILE in, stores
717
** the text in memory obtained from malloc() and returns a pointer
718
** to the text. NULL is returned at end of file, or if malloc()
719
** fails.
720
**
721
** The interface is like "readline" but no command-line editing
722
** is done.
723
*/
724
static char *
725
local_getline (char *zPrompt, FILE * in)
726
{
727
char *zLine;
728
int nLine;
729
int n;
730
int eol;
731
732
if (zPrompt && *zPrompt)
733
{
734
printf ("%s", zPrompt);
735
fflush (stdout);
736
}
737
nLine = 100;
738
zLine = malloc (nLine);
739
if (zLine == 0)
740
return 0;
741
n = 0;
742
eol = 0;
743
while (!eol)
744
{
745
if (n + 100 > nLine)
746
{
747
nLine = nLine * 2 + 100;
748
zLine = realloc (zLine, nLine);
749
if (zLine == 0)
750
return 0;
751
}
752
if (fgets (&zLine[n], nLine - n, in) == 0)
753
{
754
if (n == 0)
755
{
756
free (zLine);
757
return 0;
758
}
759
zLine[n] = 0;
760
eol = 1;
761
break;
762
}
763
while (zLine[n])
764
{
765
n++;
766
}
767
if (n > 0 && zLine[n - 1] == '\n')
768
{
769
n--;
770
zLine[n] = 0;
771
eol = 1;
772
}
773
}
774
zLine = realloc (zLine, n + 1);
775
return zLine;
776
}
777
778
/*
779
** Retrieve a single line of input text.
780
**
781
** zPrior is a string of prior text retrieved. If not the empty
782
** string, then issue a continuation prompt.
783
*/
784
static char *
785
one_input_line (const char *zPrior, FILE * in)
786
{
787
char *zPrompt;
788
char *zResult;
789
if (in != 0)
790
{
791
return local_getline (0, in);
792
}
793
if (zPrior && zPrior[0])
794
{
795
zPrompt = continuePrompt;
796
}
797
else
798
{
799
zPrompt = mainPrompt;
800
}
801
zResult = readline (zPrompt);
802
#if defined(HAVE_READLINE) && HAVE_READLINE==1
803
if (zResult && *zResult)
804
add_history (zResult);
805
#endif
806
return zResult;
807
}
808
809
struct previous_mode_data
810
{
811
int valid; /* Is there legit data in here? */
812
int mode;
813
int showHeader;
814
int colWidth[100];
815
};
816
817
/*
818
** An pointer to an instance of this structure is passed from
819
** the main program to the callback. This is used to communicate
820
** state and mode information.
821
*/
822
struct callback_data
823
{
824
sqlite3 *db; /* The database */
825
int echoOn; /* True to echo input commands */
826
int cnt; /* Number of records displayed so far */
827
FILE *out; /* Write results here */
828
int mode; /* An output mode setting */
829
int writableSchema; /* True if PRAGMA writable_schema=ON */
830
int showHeader; /* True to show column names in List or Column mode */
831
char *zDestTable; /* Name of destination table when MODE_Insert */
832
char separator[20]; /* Separator character for MODE_List */
833
int colWidth[100]; /* Requested width of each column when in column mode */
834
int actualWidth[100]; /* Actual width of each column */
835
char nullvalue[20]; /* The text to print when a NULL comes back from
836
** the database */
837
struct previous_mode_data explainPrev;
838
/* Holds the mode information just before
839
** .explain ON */
840
char outfile[FILENAME_MAX]; /* Filename for *out */
841
const char *zDbFilename; /* name of the database file */
842
};
843
844
/*
845
** These are the allowed modes.
846
*/
847
#define MODE_Line 0 /* One column per line. Blank line between records */
848
#define MODE_Column 1 /* One record per line in neat columns */
849
#define MODE_List 2 /* One record per line with a separator */
850
#define MODE_Semi 3 /* Same as MODE_List but append ";" to each line */
851
#define MODE_Html 4 /* Generate an XHTML table */
852
#define MODE_Insert 5 /* Generate SQL "insert" statements */
853
#define MODE_Tcl 6 /* Generate ANSI-C or TCL quoted elements */
854
#define MODE_Csv 7 /* Quote strings, numbers are plain */
855
#define MODE_Explain 8 /* Like MODE_Column, but do not truncate data */
856
857
static const char *modeDescr[] = {
858
"line",
859
"column",
860
"list",
861
"semi",
862
"html",
863
"insert",
864
"tcl",
865
"csv",
866
"explain",
867
};
868
869
/*
870
** Number of elements in an array
871
*/
872
#define ArraySize(X) (sizeof(X)/sizeof(X[0]))
873
874
/*
875
** Output the given string as a quoted string using SQL quoting conventions.
876
*/
877
static void
878
output_quoted_string (FILE * out, const char *z)
879
{
880
int i;
881
int nSingle = 0;
882
for (i = 0; z[i]; i++)
883
{
884
if (z[i] == '\'')
885
nSingle++;
886
}
887
if (nSingle == 0)
888
{
889
fprintf (out, "'%s'", z);
890
}
891
else
892
{
893
fprintf (out, "'");
894
while (*z)
895
{
896
for (i = 0; z[i] && z[i] != '\''; i++)
897
{
898
}
899
if (i == 0)
900
{
901
fprintf (out, "''");
902
z++;
903
}
904
else if (z[i] == '\'')
905
{
906
fprintf (out, "%.*s''", i, z);
907
z += i + 1;
908
}
909
else
910
{
911
fprintf (out, "%s", z);
912
break;
913
}
914
}
915
fprintf (out, "'");
916
}
917
}
918
919
/*
920
** Output the given string as a quoted according to C or TCL quoting rules.
921
*/
922
static void
923
output_c_string (FILE * out, const char *z)
924
{
925
unsigned int c;
926
fputc ('"', out);
927
while ((c = *(z++)) != 0)
928
{
929
if (c == '\\')
930
{
931
fputc (c, out);
932
fputc (c, out);
933
}
934
else if (c == '\t')
935
{
936
fputc ('\\', out);
937
fputc ('t', out);
938
}
939
else if (c == '\n')
940
{
941
fputc ('\\', out);
942
fputc ('n', out);
943
}
944
else if (c == '\r')
945
{
946
fputc ('\\', out);
947
fputc ('r', out);
948
}
949
else if (!isprint (c))
950
{
951
fprintf (out, "\\%03o", c & 0xff);
952
}
953
else
954
{
955
fputc (c, out);
956
}
957
}
958
fputc ('"', out);
959
}
960
961
/*
962
** Output the given string with characters that are special to
963
** HTML escaped.
964
*/
965
static void
966
output_html_string (FILE * out, const char *z)
967
{
968
int i;
969
while (*z)
970
{
971
for (i = 0; z[i] && z[i] != '<' && z[i] != '&'; i++)
972
{
973
}
974
if (i > 0)
975
{
976
fprintf (out, "%.*s", i, z);
977
}
978
if (z[i] == '<')
979
{
980
fprintf (out, "<");
981
}
982
else if (z[i] == '&')
983
{
984
fprintf (out, "&");
985
}
986
else
987
{
988
break;
989
}
990
z += i + 1;
991
}
992
}
993
994
/*
995
** If a field contains any character identified by a 1 in the following
996
** array, then the string must be quoted for CSV.
997
*/
998
static const char needCsvQuote[] = {
999
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
1000
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
1001
1, 0, 1, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0,
1002
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
1003
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
1004
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
1005
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
1006
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1,
1007
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
1008
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
1009
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
1010
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
1011
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
1012
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
1013
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
1014
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
1015
};
1016
1017
/*
1018
** Output a single term of CSV. Actually, p->separator is used for
1019
** the separator, which may or may not be a comma. p->nullvalue is
1020
** the null value. Strings are quoted using ANSI-C rules. Numbers
1021
** appear outside of quotes.
1022
*/
1023
static void
1024
output_csv (struct callback_data *p, const char *z, int bSep)
1025
{
1026
FILE *out = p->out;
1027
if (z == 0)
1028
{
1029
fprintf (out, "%s", p->nullvalue);
1030
}
1031
else
1032
{
1033
int i;
1034
int nSep = strlen (p->separator);
1035
for (i = 0; z[i]; i++)
1036
{
1037
if (needCsvQuote[((unsigned char *) z)[i]]
1038
|| (z[i] == p->separator[0] &&
1039
(nSep == 1 || memcmp (z, p->separator, nSep) == 0)))
1040
{
1041
i = 0;
1042
break;
1043
}
1044
}
1045
if (i == 0)
1046
{
1047
putc ('"', out);
1048
for (i = 0; z[i]; i++)
1049
{
1050
if (z[i] == '"')
1051
putc ('"', out);
1052
putc (z[i], out);
1053
}
1054
putc ('"', out);
1055
}
1056
else
1057
{
1058
fprintf (out, "%s", z);
1059
}
1060
}
1061
if (bSep)
1062
{
1063
fprintf (p->out, "%s", p->separator);
1064
}
1065
}
1066
1067
#ifdef SIGINT
1068
/*
1069
** This routine runs when the user presses Ctrl-C
1070
*/
1071
static void
1072
interrupt_handler (int NotUsed)
1073
{
1074
seenInterrupt = 1;
1075
if (db)
1076
sqlite3_interrupt (db);
1077
}
1078
#endif
1079
1080
/*
1081
** This is the callback routine that the SQLite library
1082
** invokes for each row of a query result.
1083
*/
1084
static int
1085
callback (void *pArg, int nArg, char **azArg, char **azCol)
1086
{
1087
int i;
1088
/* Sandro Furieri 11 July 2008 - supporting full UNICODE */
1089
char *buf = NULL;
1090
int len;
1091
/* end Sandro Furieri 11 July 2008 */
1092
struct callback_data *p = (struct callback_data *) pArg;
1093
switch (p->mode)
1094
{
1095
case MODE_Line:
1096
{
1097
int w = 5;
1098
if (azArg == 0)
1099
break;
1100
for (i = 0; i < nArg; i++)
1101
{
1102
int len = strlen (azCol[i] ? azCol[i] : "");
1103
if (len > w)
1104
w = len;
1105
}
1106
if (p->cnt++ > 0)
1107
fprintf (p->out, "\n");
1108
for (i = 0; i < nArg; i++)
1109
{
1110
/* Sandro Furieri 11 July 2008
1111
fprintf (p->out, "%*s = %s\n", w, azCol[i],
1112
azArg[i] ? azArg[i] : p->nullvalue);
1113
*/
1114
if (azArg[i] == 0)
1115
fprintf (p->out, "%*s = %s\n", w, azCol[i],
1116
p->nullvalue);
1117
else
1118
{
1119
len = strlen (azArg[i]) + 1;
1120
if (buf)
1121
free (buf);
1122
buf = malloc (len * 4);
1123
strcpy (buf, azArg[i]);
1124
convert_from_utf8 (buf, len * 4);
1125
fprintf (p->out, "%*s = %s\n", w, azCol[i],
1126
azArg[i] ? buf : p->nullvalue);
1127
}
1128
/* end Sandro Furieri 11 July 2008 */
1129
}
1130
break;
1131
}
1132
case MODE_Explain:
1133
case MODE_Column:
1134
{
1135
if (p->cnt++ == 0)
1136
{
1137
for (i = 0; i < nArg; i++)
1138
{
1139
int w, n;
1140
if (i < (int) ArraySize (p->colWidth))
1141
{
1142
w = p->colWidth[i];
1143
}
1144
else
1145
{
1146
w = 0;
1147
}
1148
if (w <= 0)
1149
{
1150
w = strlen (azCol[i] ? azCol[i] : "");
1151
if (w < 10)
1152
w = 10;
1153
n = strlen (azArg
1154
&& azArg[i] ? azArg[i] :
1155
p->nullvalue);
1156
if (w < n)
1157
w = n;
1158
}
1159
if (i < (int) ArraySize (p->actualWidth))
1160
{
1161
p->actualWidth[i] = w;
1162
}
1163
if (p->showHeader)
1164
{
1165
fprintf (p->out, "%-*.*s%s", w, w, azCol[i],
1166
i == nArg - 1 ? "\n" : " ");
1167
}
1168
}
1169
if (p->showHeader)
1170
{
1171
for (i = 0; i < nArg; i++)
1172
{
1173
int w;
1174
if (i < (int) ArraySize (p->actualWidth))
1175
{
1176
w = p->actualWidth[i];
1177
}
1178
else
1179
{
1180
w = 10;
1181
}
1182
fprintf (p->out, "%-*.*s%s", w, w,
1183
"-----------------------------------"
1184
"----------------------------------------------------------",
1185
i == nArg - 1 ? "\n" : " ");
1186
}
1187
}
1188
}
1189
if (azArg == 0)
1190
break;
1191
for (i = 0; i < nArg; i++)
1192
{
1193
int w;
1194
if (i < (int) ArraySize (p->actualWidth))
1195
{
1196
w = p->actualWidth[i];
1197
}
1198
else
1199
{
1200
w = 10;
1201
}
1202
if (p->mode == MODE_Explain && azArg[i]
1203
&& (int) strlen (azArg[i]) > w)
1204
{
1205
w = strlen (azArg[i]);
1206
}
1207
/* Sandro Furieri 11 July 2008
1208
fprintf (p->out, "%-*.*s%s", w, w,
1209
azArg[i] ? azArg[i] : p->nullvalue,
1210
i == nArg - 1 ? "\n" : " ");
1211
*/
1212
if (azArg[i] == 0)
1213
fprintf (p->out, "%-*.*s%s", w, w, p->nullvalue,
1214
i == nArg - 1 ? "\n" : " ");
1215
else
1216
{
1217
len = strlen (azArg[i]) + 1;
1218
if (buf)
1219
free (buf);
1220
buf = malloc (len * 4);
1221
strcpy (buf, azArg[i]);
1222
convert_from_utf8 (buf, len * 4);
1223
fprintf (p->out, "%-*.*s%s", w, w,
1224
azArg[i] ? buf : p->nullvalue,
1225
i == nArg - 1 ? "\n" : " ");
1226
}
1227
/* end Sandro Furieri 11 July 2008 */
1228
}
1229
break;
1230
}
1231
case MODE_Semi:
1232
case MODE_List:
1233
{
1234
if (p->cnt++ == 0 && p->showHeader)
1235
{
1236
for (i = 0; i < nArg; i++)
1237
{
1238
fprintf (p->out, "%s%s", azCol[i],
1239
i == nArg - 1 ? "\n" : p->separator);
1240
}
1241
}
1242
if (azArg == 0)
1243
break;
1244
for (i = 0; i < nArg; i++)
1245
{
1246
/* Sandro Furieri 11 July 2008
1247
char *z = azArg[i];
1248
if (z == 0)
1249
z = p->nullvalue;
1250
*/
1251
char *z;
1252
if (azArg[i] == 0)
1253
z = p->nullvalue;
1254
else
1255
{
1256
len = strlen (azArg[i]) + 1;
1257
if (buf)
1258
free (buf);
1259
buf = malloc (len * 4);
1260
z = buf;
1261
strcpy (buf, azArg[i]);
1262
convert_from_utf8 (buf, len * 4);
1263
}
1264
/* end Sandro Furieri 11 July 2008 */
1265
fprintf (p->out, "%s", z);
1266
if (i < nArg - 1)
1267
{
1268
fprintf (p->out, "%s", p->separator);
1269
}
1270
else if (p->mode == MODE_Semi)
1271
{
1272
fprintf (p->out, ";\n");
1273
}
1274
else
1275
{
1276
fprintf (p->out, "\n");
1277
}
1278
}
1279
break;
1280
}
1281
case MODE_Html:
1282
{
1283
if (p->cnt++ == 0 && p->showHeader)
1284
{
1285
fprintf (p->out, "<TR>");
1286
for (i = 0; i < nArg; i++)
1287
{
1288
fprintf (p->out, "<TH>%s</TH>", azCol[i]);
1289
}
1290
fprintf (p->out, "</TR>\n");
1291
}
1292
if (azArg == 0)
1293
break;
1294
fprintf (p->out, "<TR>");
1295
for (i = 0; i < nArg; i++)
1296
{
1297
fprintf (p->out, "<TD>");
1298
/* Sandro Furieri 11 July 2008
1299
output_html_string (p->out,
1300
azArg[i] ? azArg[i] : p->nullvalue);
1301
*/
1302
if (azArg[i] == 0)
1303
output_html_string (p->out, p->nullvalue);
1304
else
1305
{
1306
len = strlen (azArg[i]) + 1;
1307
if (buf)
1308
free (buf);
1309
buf = malloc (len * 4);
1310
strcpy (buf, azArg[i]);
1311
convert_from_utf8 (buf, len * 4);
1312
output_html_string (p->out,
1313
azArg[i] ? buf : p->nullvalue);
1314
}
1315
/* end Sandro Furieri 11 July 2008 */
1316
fprintf (p->out, "</TD>\n");
1317
}
1318
fprintf (p->out, "</TR>\n");
1319
break;
1320
}
1321
case MODE_Tcl:
1322
{
1323
if (p->cnt++ == 0 && p->showHeader)
1324
{
1325
for (i = 0; i < nArg; i++)
1326
{
1327
output_c_string (p->out, azCol[i] ? azCol[i] : "");
1328
fprintf (p->out, "%s", p->separator);
1329
}
1330
fprintf (p->out, "\n");
1331
}
1332
if (azArg == 0)
1333
break;
1334
for (i = 0; i < nArg; i++)
1335
{
1336
/* Sandro Furieri 11 July 2008
1337
output_c_string (p->out,
1338
azArg[i] ? azArg[i] : p->nullvalue);
1339
*/
1340
if (azArg[i] == 0)
1341
output_c_string (p->out, p->nullvalue);
1342
else
1343
{
1344
len = strlen (azArg[i]) + 1;
1345
if (buf)
1346
free (buf);
1347
buf = malloc (len * 4);
1348
strcpy (buf, azArg[i]);
1349
convert_from_utf8 (buf, len * 4);
1350
output_c_string (p->out,
1351
azArg[i] ? buf : p->nullvalue);
1352
}
1353
/* end Sandro Furieri 11 July 2008 */
1354
fprintf (p->out, "%s", p->separator);
1355
}
1356
fprintf (p->out, "\n");
1357
break;
1358
}
1359
case MODE_Csv:
1360
{
1361
if (p->cnt++ == 0 && p->showHeader)
1362
{
1363
for (i = 0; i < nArg; i++)
1364
{
1365
output_csv (p, azCol[i] ? azCol[i] : "",
1366
i < nArg - 1);
1367
}
1368
fprintf (p->out, "\n");
1369
}
1370
if (azArg == 0)
1371
break;
1372
for (i = 0; i < nArg; i++)
1373
{
1374
/* Sandro Furieri 11 July 2008
1375
output_csv (p, azArg[i], i < nArg - 1);
1376
*/
1377
if (azArg[i] == 0)
1378
output_csv (p, azArg[i], i < nArg - 1);
1379
else
1380
{
1381
len = strlen (azArg[i]) + 1;
1382
if (buf)
1383
free (buf);
1384
buf = malloc (len * 4);
1385
strcpy (buf, azArg[i]);
1386
convert_from_utf8 (buf, len * 4);
1387
output_csv (p, buf, i < nArg - 1);
1388
}
1389
/* end Sandro Furieri 11 July 2008 */
1390
}
1391
fprintf (p->out, "\n");
1392
break;
1393
}
1394
case MODE_Insert:
1395
{
1396
if (azArg == 0)
1397
break;
1398
fprintf (p->out, "INSERT INTO %s VALUES(", p->zDestTable);
1399
for (i = 0; i < nArg; i++)
1400
{
1401
char *zSep = i > 0 ? "," : "";
1402
if (azArg[i] == 0)
1403
{
1404
fprintf (p->out, "%sNULL", zSep);
1405
}
1406
else if (isNumber (azArg[i], 0))
1407
{
1408
fprintf (p->out, "%s%s", zSep, azArg[i]);
1409
}
1410
else
1411
{
1412
if (zSep[0])
1413
fprintf (p->out, "%s", zSep);
1414
/* Sandro Furieri 11 July 2008
1415
output_quoted_string (p->out, azArg[i]);
1416
*/
1417
if (azArg[i] == 0)
1418
output_quoted_string (p->out, azArg[i]);
1419
else
1420
{
1421
len = strlen (azArg[i]) + 1;
1422
if (buf)
1423
free (buf);
1424
buf = malloc (len * 4);
1425
strcpy (buf, azArg[i]);
1426
convert_from_utf8 (buf, len * 4);
1427
output_quoted_string (p->out, buf);
1428
}
1429
/* end Sandro Furieri 11 July 2008 */
1430
}
1431
}
1432
fprintf (p->out, ");\n");
1433
break;
1434
}
1435
}
1436
if (buf)
1437
free (buf);
1438
return 0;
1439
}
1440
1441
/*
1442
** Set the destination table field of the callback_data structure to
1443
** the name of the table given. Escape any quote characters in the
1444
** table name.
1445
*/
1446
static void
1447
set_table_name (struct callback_data *p, const char *zName)
1448
{
1449
int i, n;
1450
int needQuote;
1451
char *z;
1452
1453
if (p->zDestTable)
1454
{
1455
free (p->zDestTable);
1456
p->zDestTable = 0;
1457
}
1458
if (zName == 0)
1459
return;
1460
needQuote = !isalpha ((unsigned char) *zName) && *zName != '_';
1461
for (i = n = 0; zName[i]; i++, n++)
1462
{
1463
if (!isalnum ((unsigned char) zName[i]) && zName[i] != '_')
1464
{
1465
needQuote = 1;
1466
if (zName[i] == '\'')
1467
n++;
1468
}
1469
}
1470
if (needQuote)
1471
n += 2;
1472
z = p->zDestTable = malloc (n + 1);
1473
if (z == 0)
1474
{
1475
fprintf (stderr, "Out of memory!\n");
1476
exit (1);
1477
}
1478
n = 0;
1479
if (needQuote)
1480
z[n++] = '\'';
1481
for (i = 0; zName[i]; i++)
1482
{
1483
z[n++] = zName[i];
1484
if (zName[i] == '\'')
1485
z[n++] = '\'';
1486
}
1487
if (needQuote)
1488
z[n++] = '\'';
1489
z[n] = 0;
1490
}
1491
1492
/* zIn is either a pointer to a NULL-terminated string in memory obtained
1493
** from malloc(), or a NULL pointer. The string pointed to by zAppend is
1494
** added to zIn, and the result returned in memory obtained from malloc().
1495
** zIn, if it was not NULL, is freed.
1496
**
1497
** If the third argument, quote, is not '\0', then it is used as a
1498
** quote character for zAppend.
1499
*/
1500
static char *
1501
appendText (char *zIn, char const *zAppend, char quote)
1502
{
1503
int len;
1504
int i;
1505
int nAppend = strlen (zAppend);
1506
int nIn = (zIn ? strlen (zIn) : 0);
1507
1508
len = nAppend + nIn + 1;
1509
if (quote)
1510
{
1511
len += 2;
1512
for (i = 0; i < nAppend; i++)
1513
{
1514
if (zAppend[i] == quote)
1515
len++;
1516
}
1517
}
1518
1519
zIn = (char *) realloc (zIn, len);
1520
if (!zIn)
1521
{
1522
return 0;
1523
}
1524
1525
if (quote)
1526
{
1527
char *zCsr = &zIn[nIn];
1528
*zCsr++ = quote;
1529
for (i = 0; i < nAppend; i++)
1530
{
1531
*zCsr++ = zAppend[i];
1532
if (zAppend[i] == quote)
1533
*zCsr++ = quote;
1534
}
1535
*zCsr++ = quote;
1536
*zCsr++ = '\0';
1537
assert ((zCsr - zIn) == len);
1538
}
1539
else
1540
{
1541
memcpy (&zIn[nIn], zAppend, nAppend);
1542
zIn[len - 1] = '\0';
1543
}
1544
1545
return zIn;
1546
}
1547
1548
1549
/*
1550
** Execute a query statement that has a single result column. Print
1551
** that result column on a line by itself with a semicolon terminator.
1552
**
1553
** This is used, for example, to show the schema of the database by
1554
** querying the SQLITE_MASTER table.
1555
*/
1556
static int
1557
run_table_dump_query (FILE * out, sqlite3 * db, const char *zSelect)
1558
{
1559
sqlite3_stmt *pSelect;
1560
int rc;
1561
rc = sqlite3_prepare (db, zSelect, -1, &pSelect, 0);
1562
if (rc != SQLITE_OK || !pSelect)
1563
{
1564
return rc;
1565
}
1566
rc = sqlite3_step (pSelect);
1567
while (rc == SQLITE_ROW)
1568
{
1569
fprintf (out, "%s;\n", sqlite3_column_text (pSelect, 0));
1570
rc = sqlite3_step (pSelect);
1571
}
1572
return sqlite3_finalize (pSelect);
1573
}
1574
1575
1576
/*
1577
** This is a different callback routine used for dumping the database.
1578
** Each row received by this callback consists of a table name,
1579
** the table type ("index" or "table") and SQL to create the table.
1580
** This routine should print text sufficient to recreate the table.
1581
*/
1582
static int
1583
dump_callback (void *pArg, int nArg, char **azArg, char **azCol)
1584
{
1585
int rc;
1586
const char *zTable;
1587
const char *zType;
1588
const char *zSql;
1589
struct callback_data *p = (struct callback_data *) pArg;
1590
1591
if (azCol == NULL)
1592
azCol = NULL; /* suppressing stupid compiler warnings [unused] */
1593
if (nArg != 3)
1594
return 1;
1595
zTable = azArg[0];
1596
zType = azArg[1];
1597
zSql = azArg[2];
1598
1599
if (strcmp (zTable, "sqlite_sequence") == 0)
1600
{
1601
fprintf (p->out, "DELETE FROM sqlite_sequence;\n");
1602
}
1603
else if (strcmp (zTable, "sqlite_stat1") == 0)
1604
{
1605
fprintf (p->out, "ANALYZE sqlite_master;\n");
1606
}
1607
else if (strncmp (zTable, "sqlite_", 7) == 0)
1608
{
1609
return 0;
1610
}
1611
else if (strncmp (zSql, "CREATE VIRTUAL TABLE", 20) == 0)
1612
{
1613
char *zIns;
1614
if (!p->writableSchema)
1615
{
1616
fprintf (p->out, "PRAGMA writable_schema=ON;\n");
1617
p->writableSchema = 1;
1618
}
1619
zIns =
1620
sqlite3_mprintf
1621
("INSERT INTO sqlite_master(type,name,tbl_name,rootpage,sql)"
1622
"VALUES('table','%q','%q',0,'%q');", zTable, zTable, zSql);
1623
fprintf (p->out, "%s\n", zIns);
1624
sqlite3_free (zIns);
1625
return 0;
1626
}
1627
else
1628
{
1629
fprintf (p->out, "%s;\n", zSql);
1630
}
1631
1632
if (strcmp (zType, "table") == 0)
1633
{
1634
sqlite3_stmt *pTableInfo = 0;
1635
char *zSelect = 0;
1636
char *zTableInfo = 0;
1637
char *zTmp = 0;
1638
1639
zTableInfo = appendText (zTableInfo, "PRAGMA table_info(", 0);
1640
zTableInfo = appendText (zTableInfo, zTable, '"');
1641
zTableInfo = appendText (zTableInfo, ");", 0);
1642
1643
rc = sqlite3_prepare (p->db, zTableInfo, -1, &pTableInfo, 0);
1644
if (zTableInfo)
1645
free (zTableInfo);
1646
if (rc != SQLITE_OK || !pTableInfo)
1647
{
1648
return 1;
1649
}
1650
1651
zSelect = appendText (zSelect, "SELECT 'INSERT INTO ' || ", 0);
1652
zTmp = appendText (zTmp, zTable, '"');
1653
if (zTmp)
1654
{
1655
zSelect = appendText (zSelect, zTmp, '\'');
1656
free (zTmp);
1657
}
1658
zSelect = appendText (zSelect, " || ' VALUES(' || ", 0);
1659
rc = sqlite3_step (pTableInfo);
1660
while (rc == SQLITE_ROW)
1661
{
1662
const char *zText =
1663
(const char *) sqlite3_column_text (pTableInfo, 1);
1664
zSelect = appendText (zSelect, "quote(", 0);
1665
zSelect = appendText (zSelect, zText, '"');
1666
rc = sqlite3_step (pTableInfo);
1667
if (rc == SQLITE_ROW)
1668
{
1669
zSelect = appendText (zSelect, ") || ',' || ", 0);
1670
}
1671
else
1672
{
1673
zSelect = appendText (zSelect, ") ", 0);
1674
}
1675
}
1676
rc = sqlite3_finalize (pTableInfo);
1677
if (rc != SQLITE_OK)
1678
{
1679
if (zSelect)
1680
free (zSelect);
1681
return 1;
1682
}
1683
zSelect = appendText (zSelect, "|| ')' FROM ", 0);
1684
zSelect = appendText (zSelect, zTable, '"');
1685
1686
rc = run_table_dump_query (p->out, p->db, zSelect);
1687
if (rc == SQLITE_CORRUPT)
1688
{
1689
zSelect = appendText (zSelect, " ORDER BY rowid DESC", 0);
1690
rc = run_table_dump_query (p->out, p->db, zSelect);
1691
}
1692
if (zSelect)
1693
free (zSelect);
1694
}
1695
return 0;
1696
}
1697
1698
static void
1699
spatialite_autocreate (sqlite3 * db)
1700
{
1701
/* attempting to perform self-initialization for a newly created DB */
1702
int ret;
1703
char sql[1024];
1704
char *err_msg = NULL;
1705
int count;
1706
int i;
1707
char **results;
1708
int rows;
1709
int columns;
1710
1711
/* checking if this DB is really empty */
1712
strcpy (sql, "SELECT Count(*) from sqlite_master");
1713
ret = sqlite3_get_table (db, sql, &results, &rows, &columns, NULL);
1714
if (ret != SQLITE_OK)
1715
return;
1716
if (rows < 1)
1717
;
1718
else
1719
{
1720
for (i = 1; i <= rows; i++)
1721
count = atoi (results[(i * columns) + 0]);
1722
}
1723
sqlite3_free_table (results);
1724
1725
if (count > 0)
1726
return;
1727
1728
/* all right, it's empty: proceding to initialize */
1729
strcpy (sql, "SELECT InitSpatialMetadata()");
1730
ret = sqlite3_exec (db, sql, NULL, NULL, &err_msg);
1731
if (ret != SQLITE_OK)
1732
{
1733
fprintf (stderr, "InitSpatialMetadata() error: %s\n", err_msg);
1734
sqlite3_free (err_msg);
1735
return;
1736
}
1737
spatial_ref_sys_init (db, 1);
1738
}
1739
1740
/*
1741
** Run zQuery. Use dump_callback() as the callback routine so that
1742
** the contents of the query are output as SQL statements.
1743
**
1744
** If we get a SQLITE_CORRUPT error, rerun the query after appending
1745
** "ORDER BY rowid DESC" to the end.
1746
*/
1747
static int
1748
run_schema_dump_query (struct callback_data *p,
1749
const char *zQuery, char **pzErrMsg)
1750
{
1751
int rc;
1752
rc = sqlite3_exec (p->db, zQuery, dump_callback, p, pzErrMsg);
1753
if (rc == SQLITE_CORRUPT)
1754
{
1755
char *zQ2;
1756
int len = strlen (zQuery);
1757
if (pzErrMsg)
1758
sqlite3_free (*pzErrMsg);
1759
zQ2 = malloc (len + 100);
1760
if (zQ2 == 0)
1761
return rc;
1762
sqlite3_snprintf (sizeof (zQ2), zQ2, "%s ORDER BY rowid DESC",
1763
zQuery);
1764
rc = sqlite3_exec (p->db, zQ2, dump_callback, p, pzErrMsg);
1765
free (zQ2);
1766
}
1767
return rc;
1768
}
1769
1770
/*
1771
** Text of a help message
1772
*/
1773
static char zHelp[] =
1774
".bail ON|OFF Stop after hitting an error. Default OFF\n"
1775
".databases List names and files of attached databases\n"
1776
".dump ?TABLE? ... Dump the database in an SQL text format\n"
1777
".echo ON|OFF Turn command echo on or off\n"
1778
".exit Exit this program\n"
1779
".explain ON|OFF Turn output mode suitable for EXPLAIN on or off.\n"
1780
".header(s) ON|OFF Turn display of headers on or off\n"
1781
".help Show this message\n"
1782
".import FILE TABLE Import data from FILE into TABLE\n"
1783
".indices TABLE Show names of all indices on TABLE\n"
1784
#ifdef SQLITE_ENABLE_IOTRACE
1785
".iotrace FILE Enable I/O diagnostic logging to FILE\n"
1786
#endif
1787
#ifndef SQLITE_OMIT_LOAD_EXTENSION
1788
".load FILE ?ENTRY? Load an extension library\n"
1789
#endif
1790
".mode MODE ?TABLE? Set output mode where MODE is one of:\n"
1791
" csv Comma-separated values\n"
1792
" column Left-aligned columns. (See .width)\n"
1793
" html HTML <table> code\n"
1794
" insert SQL insert statements for TABLE\n"
1795
" line One value per line\n"
1796
" list Values delimited by .separator string\n"
1797
" tabs Tab-separated values\n"
1798
" tcl TCL list elements\n"
1799
".nullvalue STRING Print STRING in place of NULL values\n"
1800
".output FILENAME Send output to FILENAME\n"
1801
".output stdout Send output to the screen\n"
1802
".prompt MAIN CONTINUE Replace the standard prompts\n"
1803
".quit Exit this program\n"
1804
".schema ?TABLE? Show the CREATE statements\n"
1805
".separator STRING Change separator used by output mode and .import\n"
1806
".show Show the current values for various settings\n"
1807
".tables ?PATTERN? List names of tables matching a LIKE pattern\n"
1808
".timeout MS Try opening locked tables for MS milliseconds\n"
1809
#if HAS_TIMER
1810
".timer ON|OFF Turn the CPU timer measurement on or off\n"
1811
#endif
1812
".width NUM NUM ... Set column widths for \"column\" mode\n"
1813
/* Sandro Furieri 2008-06-20 */
1814
"\n====================== SpatiaLite ========================================\n\n"
1815
".charset Report the current locale charset setting\n\n"
1816
".charset <charset-name>\n"
1817
" Set the charset encoding according to the command shell\n"
1818
" e.g.: when working on Windows Command Prompt, if you notice\n"
1819
" 'strange' chars, a very good idea may be to type\n"
1820
" the following command:\n"
1821
" .charset CP850\n"
1822
" [use the Windows CHCP command in order to check better\n"
1823
" which one charset is used on your Command Prompt]\n\n"
1824
".chkdupl <table> Check a TABLE for duplicated rows\n\n"
1825
".remdupl <table> Removes any duplicated row from a TABLE\n\n"
1826
".loadshp <args> Loads a SHAPEFILE into a SpatiaLite table\n"
1827
" arg_list: shp_path table_name charset [SRID] [column_name]\n"
1828
" [2d | 3d] [compressed]\n\n"
1829
".dumpshp <args> Dumps a SpatiaLite table into a SHAPEFILE\n"
1830
" arg_list: table_name column_name shp_path charset [geom_type]\n"
1831
" geom_type={ POINT | LINESTRING | POLYGON | MULTIPOINT }\n\n"
1832
".loaddbf <args> Loads a DBF into a SpatiaLite table\n"
1833
" arg_list: dbf_path table_name charset\n\n"
1834
".dumpdbf <args> Dumps a SpatiaLite table into a DBF\n"
1835
" arg_list: table_name dbf_path charset\n\n"
1836
".loadxl <args> Loads a XL spreadsheet (.xls) into a SpatiaLite table\n"
1837
" arg_list: xl_path table_name \n"
1838
" [worksheet_index [first_line_titles{0/1}]]\n\n"
1839
".dumpkml <args> Dumps a SpatiaLite table as a KML file\n"
1840
" arg_list: table_name geom_column kml_path\n"
1841
" [precision] [name_column] [desc_column]\n\n"
1842
".read <args> Execute an SQL script\n"
1843
" arg_list: script_path charset\n"
1844
/* end Sandro Furieri 2008-06-20 */
1845
;
1846
1847
/* Forward reference */
1848
static int process_input (struct callback_data *p, FILE * in, char *in_charset);
1849
1850
/*
1851
** Make sure the database is open. If it is not, then open it. If
1852
** the database fails to open, print an error message and exit.
1853
*/
1854
static void
1855
open_db (struct callback_data *p)
1856
{
1857
if (p->db == 0)
1858
{
1859
sqlite3_open (p->zDbFilename, &p->db);
1860
db = p->db;
1861
if (db && sqlite3_errcode (db) == SQLITE_OK)
1862
{
1863
sqlite3_create_function (db, "shellstatic", 0, SQLITE_UTF8, 0,
1864
shellstaticFunc, 0, 0);
1865
}
1866
if (db == 0 || SQLITE_OK != sqlite3_errcode (db))
1867
{
1868
fprintf (stderr, "Unable to open database \"%s\": %s\n",
1869
p->zDbFilename, sqlite3_errmsg (db));
1870
exit (1);
1871
}
1872
#ifndef SQLITE_OMIT_LOAD_EXTENSION
1873
sqlite3_enable_load_extension (p->db, 1);
1874
#endif
1875
1876
/* Sandro Furieri 2009-11-08 */
1877
sqlite3_exec (p->db, "PRAGMA foreign_keys = 1", NULL, 0, NULL);
1878
/* end Sandro Furieri 2008-11-08 */
1879
/* Sandro Furieri 2010-08-07 */
1880
spatialite_autocreate (p->db);
1881
/* end Sandro Furieri 2010-08-07 */
1882
}
1883
}
1884
1885
/*
1886
** Do C-language style dequoting.
1887
**
1888
** \t -> tab
1889
** \n -> newline
1890
** \r -> carriage return
1891
** \NNN -> ascii character NNN in octal
1892
** \\ -> backslash
1893
*/
1894
static void
1895
resolve_backslashes (char *z)
1896
{
1897
int i, j, c;
1898
for (i = j = 0; (c = z[i]) != 0; i++, j++)
1899
{
1900
if (c == '\\')
1901
{
1902
c = z[++i];
1903
if (c == 'n')
1904
{
1905
c = '\n';
1906
}
1907
else if (c == 't')
1908
{
1909
c = '\t';
1910
}
1911
else if (c == 'r')
1912
{
1913
c = '\r';
1914
}
1915
else if (c >= '0' && c <= '7')
1916
{
1917
c -= '0';
1918
if (z[i + 1] >= '0' && z[i + 1] <= '7')
1919
{
1920
i++;
1921
c = (c << 3) + z[i] - '0';
1922
if (z[i + 1] >= '0' && z[i + 1] <= '7')
1923
{
1924
i++;
1925
c = (c << 3) + z[i] - '0';
1926
}
1927
}
1928
}
1929
}
1930
z[j] = c;
1931
}
1932
z[j] = 0;
1933
}
1934
1935
/*
1936
** Interpret zArg as a boolean value. Return either 0 or 1.
1937
*/
1938
static int
1939
booleanValue (char *zArg)
1940
{
1941
int val = atoi (zArg);
1942
int j;
1943
for (j = 0; zArg[j]; j++)
1944
{
1945
zArg[j] = tolower (zArg[j]);
1946
}
1947
if (strcmp (zArg, "on") == 0)
1948
{
1949
val = 1;
1950
}
1951
else if (strcmp (zArg, "yes") == 0)
1952
{
1953
val = 1;
1954
}
1955
return val;
1956
}
1957
1958
/*
1959
** If an input line begins with "." then invoke this routine to
1960
** process that line.
1961
**
1962
** Return 1 on error, 2 to exit, and 0 otherwise.
1963
*/
1964
static int
1965
do_meta_command (char *zLine, struct callback_data *p)
1966
{
1967
int i = 1;
1968
int nArg = 0;
1969
int n, c;
1970
int rc = 0;
1971
char *azArg[50];
1972
1973
/* Parse the input line into tokens.
1974
*/
1975
while (zLine[i] && nArg < (int) ArraySize (azArg))
1976
{
1977
while (isspace ((unsigned char) zLine[i]))
1978
{
1979
i++;
1980
}
1981
if (zLine[i] == 0)
1982
break;
1983
if (zLine[i] == '\'' || zLine[i] == '"')
1984
{
1985
int delim = zLine[i++];
1986
azArg[nArg++] = &zLine[i];
1987
while (zLine[i] && zLine[i] != delim)
1988
{
1989
i++;
1990
}
1991
if (zLine[i] == delim)
1992
{
1993
zLine[i++] = 0;
1994
}
1995
if (delim == '"')
1996
resolve_backslashes (azArg[nArg - 1]);
1997
}
1998
else
1999
{
2000
azArg[nArg++] = &zLine[i];
2001
while (zLine[i] && !isspace ((unsigned char) zLine[i]))
2002
{
2003
i++;
2004
}
2005
if (zLine[i])
2006
zLine[i++] = 0;
2007
resolve_backslashes (azArg[nArg - 1]);
2008
}
2009
}
2010
2011
/* Process the input line.
2012
*/
2013
if (nArg == 0)
2014
return rc;
2015
n = strlen (azArg[0]);
2016
c = azArg[0][0];
2017
2018
/* Sandro Furieri 2008-06-20 */
2019
if (c == 'c' && n > 1 && strncmp (azArg[0], "charset", n) == 0 && nArg == 1)
2020
{
2021
/* reporting the charset */
2022
if (*spatialite_charset == '\0')
2023
{
2024
printf
2025
("the shell's default LOCALE CHARSET is currently in use\n");
2026
fflush (stdout);
2027
}
2028
else
2029
{
2030
printf ("the %s charset is currently used as LOCALE CHARSET\n",
2031
spatialite_charset);
2032
fflush (stdout);
2033
}
2034
}
2035
else if (c == 'c' && n > 1 && strncmp (azArg[0], "charset", n) == 0
2036
&& nArg == 2)
2037
{
2038
/* setting the charset */
2039
create_utf8_converter (azArg[1]);
2040
}
2041
else if (c == 'd' && n > 1 && strncmp (azArg[0], "dumpshp", n) == 0
2042
&& (nArg == 5 || nArg == 6))
2043
{
2044
/* dumping a spatial table to SHAPEFILE */
2045
char *table = azArg[1];
2046
char *column = azArg[2];
2047
char *shp_path = azArg[3];
2048
char *outCS = azArg[4];
2049
char *type = NULL;
2050
if (nArg == 6)
2051
type = azArg[5];
2052
open_db (p);
2053
dump_shapefile (p->db, table, column, shp_path, outCS, type, 1, NULL);
2054
}
2055
else if (c == 'd' && n > 1 && strncmp (azArg[0], "dumpdbf", n) == 0
2056
&& (nArg == 4))
2057
{
2058
/* dumping a spatial table to DBF */
2059
char *table = azArg[1];
2060
char *dbf_path = azArg[2];
2061
char *outCS = azArg[3];
2062
open_db (p);
2063
dump_dbf (p->db, table, dbf_path, outCS);
2064
}
2065
else if (c == 'd' && n > 1 && strncmp (azArg[0], "dumpkml", n) == 0
2066
&& (nArg == 4 || nArg == 5 || nArg == 6 || nArg == 7))
2067
{
2068
/* dumping a spatial table as KML file */
2069
char *table = azArg[1];
2070
char *geom = azArg[2];
2071
char *kml_path = azArg[3];
2072
char *name = NULL;
2073
char *desc = NULL;
2074
int precision = 8;
2075
if (nArg >= 5)
2076
precision = atoi (azArg[4]);
2077
if (nArg >= 6)
2078
name = azArg[5];
2079
if (nArg == 7)
2080
desc = azArg[6];
2081
open_db (p);
2082
dump_kml (p->db, table, geom, kml_path, name, desc, precision);
2083
}
2084
else if (c == 'l' && n > 1 && strncmp (azArg[0], "loadshp", n) == 0
2085
&& (nArg == 4 || nArg == 5 || nArg == 6 || nArg == 7 || nArg == 8))
2086
{
2087
char *shp_path = azArg[1];
2088
char *table = azArg[2];
2089
char *inCS = azArg[3];
2090
int srid = -1;
2091
int coerce2d = 0;
2092
int compressed = 0;
2093
char *column = NULL;
2094
if (nArg >= 5)
2095
srid = atoi (azArg[4]);
2096
if (nArg >= 6)
2097
column = azArg[5];
2098
if (nArg >= 7)
2099
{
2100
if (strcasecmp (azArg[6], "2d") == 0)
2101
coerce2d = 1;
2102
}
2103
if (nArg == 8)
2104
compressed = 1;
2105
open_db (p);
2106
load_shapefile (p->db, shp_path, table, inCS, srid, column, coerce2d,
2107
compressed, 1, NULL);
2108
}
2109
else if (c == 'l' && n > 1 && strncmp (azArg[0], "loaddbf", n) == 0
2110
&& nArg == 4)
2111
{
2112
char *dbf_path = azArg[1];
2113
char *table = azArg[2];
2114
char *inCS = azArg[3];
2115
open_db (p);
2116
load_dbf (p->db, dbf_path, table, inCS, 1, NULL);
2117
}
2118
else if (c == 'l' && n > 1 && strncmp (azArg[0], "loadxl", n) == 0
2119
&& (nArg == 3 || nArg == 4 || nArg == 5))
2120
{
2121
char *xl_path = azArg[1];
2122
char *table = azArg[2];
2123
unsigned int worksheet = 0;
2124
int firstLine = 0;
2125
if (nArg == 4 || nArg == 5)
2126
worksheet = atoi (azArg[3]);
2127
if (nArg == 5)
2128
{
2129
if (atoi (azArg[4]) == 1)
2130
firstLine = 1;
2131
}
2132
open_db (p);
2133
load_XL (p->db, xl_path, table, worksheet, firstLine);
2134
}
2135
else if (c == 'r' && strncmp (azArg[0], "read", n) == 0)
2136
{
2137
FILE *alt;
2138
if (nArg != 3)
2139
{
2140
fprintf (stderr,
2141
"invalid arguments: .read script_path charset\n");
2142
return rc;
2143
}
2144
alt = fopen (azArg[1], "rb");
2145
if (alt == 0)
2146
{
2147
fprintf (stderr, "can't open \"%s\"\n", azArg[1]);
2148
}
2149
else
2150
{
2151
process_input (p, alt, azArg[2]);
2152
fclose (alt);
2153
}
2154
}
2155
else if (c == 'c' && strncmp (azArg[0], "chkdupl", n) == 0 && nArg == 2)
2156
{
2157
char *table = azArg[1];
2158
open_db (p);
2159
check_duplicated_rows (p->db, table);
2160
}
2161
else if (c == 'r' && strncmp (azArg[0], "remdupl", n) == 0 && nArg == 2)
2162
{
2163
char *table = azArg[1];
2164
open_db (p);
2165
remove_duplicated_rows (p->db, table);
2166
}
2167
else if (c == 'b' && n > 1 && strncmp (azArg[0], "bail", n) == 0
2168
&& nArg > 1)
2169
{
2170
bail_on_error = booleanValue (azArg[1]);
2171
}
2172
else if (c == 'd' && n > 1 && strncmp (azArg[0], "databases", n) == 0)
2173
{
2174
struct callback_data data;
2175
char *zErrMsg = 0;
2176
open_db (p);
2177
memcpy (&data, p, sizeof (data));
2178
data.showHeader = 1;
2179
data.mode = MODE_Column;
2180
data.colWidth[0] = 3;
2181
data.colWidth[1] = 15;
2182
data.colWidth[2] = 58;
2183
data.cnt = 0;
2184
sqlite3_exec (p->db, "PRAGMA database_list; ", callback, &data,
2185
&zErrMsg);
2186
if (zErrMsg)
2187
{
2188
fprintf (stderr, "Error: %s\n", zErrMsg);
2189
sqlite3_free (zErrMsg);
2190
}
2191
}
2192
else if (c == 'd' && strncmp (azArg[0], "dump", n) == 0)
2193
{
2194
char *zErrMsg = 0;
2195
open_db (p);
2196
fprintf (p->out, "BEGIN TRANSACTION;\n");
2197
p->writableSchema = 0;
2198
if (nArg == 1)
2199
{
2200
run_schema_dump_query (p,
2201
"SELECT name, type, sql FROM sqlite_master "
2202
"WHERE sql NOT NULL AND type=='table'",
2203
0);
2204
run_table_dump_query (p->out, p->db,
2205
"SELECT sql FROM sqlite_master "
2206
"WHERE sql NOT NULL AND type IN ('index','trigger','view')");
2207
}
2208
else
2209
{
2210
int i;
2211
for (i = 1; i < nArg; i++)
2212
{
2213
zShellStatic = azArg[i];
2214
run_schema_dump_query (p,
2215
"SELECT name, type, sql FROM sqlite_master "
2216
"WHERE tbl_name LIKE shellstatic() AND type=='table'"
2217
" AND sql NOT NULL", 0);
2218
run_table_dump_query (p->out, p->db,
2219
"SELECT sql FROM sqlite_master "
2220
"WHERE sql NOT NULL"
2221
" AND type IN ('index','trigger','view')"
2222
" AND tbl_name LIKE shellstatic()");
2223
zShellStatic = 0;
2224
}
2225
}
2226
if (p->writableSchema)
2227
{
2228
fprintf (p->out, "PRAGMA writable_schema=OFF;\n");
2229
p->writableSchema = 0;
2230
}
2231
if (zErrMsg)
2232
{
2233
fprintf (stderr, "Error: %s\n", zErrMsg);
2234
sqlite3_free (zErrMsg);
2235
}
2236
else
2237
{
2238
fprintf (p->out, "COMMIT;\n");
2239
}
2240
}
2241
else if (c == 'e' && strncmp (azArg[0], "echo", n) == 0 && nArg > 1)
2242
{
2243
p->echoOn = booleanValue (azArg[1]);
2244
}
2245
else if (c == 'e' && strncmp (azArg[0], "exit", n) == 0)
2246
{
2247
rc = 2;
2248
}
2249
else if (c == 'e' && strncmp (azArg[0], "explain", n) == 0)
2250
{
2251
int val = nArg >= 2 ? booleanValue (azArg[1]) : 1;
2252
if (val == 1)
2253
{
2254
if (!p->explainPrev.valid)
2255
{
2256
p->explainPrev.valid = 1;
2257
p->explainPrev.mode = p->mode;
2258
p->explainPrev.showHeader = p->showHeader;
2259
memcpy (p->explainPrev.colWidth, p->colWidth,
2260
sizeof (p->colWidth));
2261
}
2262
/* We could put this code under the !p->explainValid
2263
** condition so that it does not execute if we are already in
2264
** explain mode. However, always executing it allows us an easy
2265
** was to reset to explain mode in case the user previously
2266
** did an .explain followed by a .width, .mode or .header
2267
** command.
2268
*/
2269
p->mode = MODE_Explain;
2270
p->showHeader = 1;
2271
memset (p->colWidth, 0, ArraySize (p->colWidth));
2272
p->colWidth[0] = 4; /* addr */
2273
p->colWidth[1] = 13; /* opcode */
2274
p->colWidth[2] = 4; /* P1 */
2275
p->colWidth[3] = 4; /* P2 */
2276
p->colWidth[4] = 4; /* P3 */
2277
p->colWidth[5] = 13; /* P4 */
2278
p->colWidth[6] = 2; /* P5 */
2279
p->colWidth[7] = 13; /* Comment */
2280
}
2281
else if (p->explainPrev.valid)
2282
{
2283
p->explainPrev.valid = 0;
2284
p->mode = p->explainPrev.mode;
2285
p->showHeader = p->explainPrev.showHeader;
2286
memcpy (p->colWidth, p->explainPrev.colWidth,
2287
sizeof (p->colWidth));
2288
}
2289
}
2290
else if (c == 'h' && (strncmp (azArg[0], "header", n) == 0 ||
2291
strncmp (azArg[0], "headers", n) == 0) && nArg > 1)
2292
{
2293
p->showHeader = booleanValue (azArg[1]);
2294
}
2295
else if (c == 'h' && strncmp (azArg[0], "help", n) == 0)
2296
{
2297
fprintf (stderr, zHelp);
2298
}
2299
else if (c == 'i' && strncmp (azArg[0], "import", n) == 0 && nArg >= 3)
2300
{
2301
char *zTable = azArg[2]; /* Insert data into this table */
2302
char *zFile = azArg[1]; /* The file from which to extract data */
2303
sqlite3_stmt *pStmt; /* A statement */
2304
int rc; /* Result code */
2305
int nCol; /* Number of columns in the table */
2306
int nByte; /* Number of bytes in an SQL string */
2307
int i, j; /* Loop counters */
2308
int nSep; /* Number of bytes in p->separator[] */
2309
char *zSql; /* An SQL statement */
2310
char *zLine; /* A single line of input from the file */
2311
char **azCol; /* zLine[] broken up into columns */
2312
char *zCommit; /* How to commit changes */
2313
FILE *in; /* The input file */
2314
int lineno = 0; /* Line number of input file */
2315
2316
open_db (p);
2317
nSep = strlen (p->separator);
2318
if (nSep == 0)
2319
{
2320
fprintf (stderr, "non-null separator required for import\n");
2321
return 0;
2322
}
2323
zSql = sqlite3_mprintf ("SELECT * FROM '%q'", zTable);
2324
if (zSql == 0)
2325
return 0;
2326
nByte = strlen (zSql);
2327
rc = sqlite3_prepare (p->db, zSql, -1, &pStmt, 0);
2328
sqlite3_free (zSql);
2329
if (rc)
2330
{
2331
fprintf (stderr, "Error: %s\n", sqlite3_errmsg (db));
2332
nCol = 0;
2333
rc = 1;
2334
}
2335
else
2336
{
2337
nCol = sqlite3_column_count (pStmt);
2338
}
2339
sqlite3_finalize (pStmt);
2340
if (nCol == 0)
2341
return 0;
2342
zSql = malloc (nByte + 20 + nCol * 2);
2343
if (zSql == 0)
2344
return 0;
2345
sqlite3_snprintf (nByte + 20, zSql, "INSERT INTO '%q' VALUES(?",
2346
zTable);
2347
j = strlen (zSql);
2348
for (i = 1; i < nCol; i++)
2349
{
2350
zSql[j++] = ',';
2351
zSql[j++] = '?';
2352
}
2353
zSql[j++] = ')';
2354
zSql[j] = 0;
2355
rc = sqlite3_prepare (p->db, zSql, -1, &pStmt, 0);
2356
free (zSql);
2357
if (rc)
2358
{
2359
fprintf (stderr, "Error: %s\n", sqlite3_errmsg (db));
2360
sqlite3_finalize (pStmt);
2361
return 1;
2362
}
2363
in = fopen (zFile, "rb");
2364
if (in == 0)
2365
{
2366
fprintf (stderr, "cannot open file: %s\n", zFile);
2367
sqlite3_finalize (pStmt);
2368
return 0;
2369
}
2370
azCol = malloc (sizeof (azCol[0]) * (nCol + 1));
2371
if (azCol == 0)
2372
{
2373
fclose (in);
2374
return 0;
2375
}
2376
sqlite3_exec (p->db, "BEGIN", 0, 0, 0);
2377
zCommit = "COMMIT";
2378
while ((zLine = local_getline (0, in)) != 0)
2379
{
2380
char *z;
2381
i = 0;
2382
lineno++;
2383
azCol[0] = zLine;
2384
for (i = 0, z = zLine; *z && *z != '\n' && *z != '\r'; z++)
2385
{
2386
if (*z == p->separator[0]
2387
&& strncmp (z, p->separator, nSep) == 0)
2388
{
2389
*z = 0;
2390
i++;
2391
if (i < nCol)
2392
{
2393
azCol[i] = &z[nSep];
2394
z += nSep - 1;
2395
}
2396
}
2397
}
2398
*z = 0;
2399
if (i + 1 != nCol)
2400
{
2401
fprintf (stderr,
2402
"%s line %d: expected %d columns of data but found %d\n",
2403
zFile, lineno, nCol, i + 1);
2404
zCommit = "ROLLBACK";
2405
break;
2406
}
2407
for (i = 0; i < nCol; i++)
2408
{
2409
sqlite3_bind_text (pStmt, i + 1, azCol[i], -1,
2410
SQLITE_STATIC);
2411
}
2412
sqlite3_step (pStmt);
2413
rc = sqlite3_reset (pStmt);
2414
free (zLine);
2415
if (rc != SQLITE_OK)
2416
{
2417
fprintf (stderr, "Error: %s\n", sqlite3_errmsg (db));
2418
zCommit = "ROLLBACK";
2419
rc = 1;
2420
break;
2421
}
2422
}
2423
free (azCol);
2424
fclose (in);
2425
sqlite3_finalize (pStmt);
2426
sqlite3_exec (p->db, zCommit, 0, 0, 0);
2427
}
2428
else if (c == 'i' && strncmp (azArg[0], "indices", n) == 0 && nArg > 1)
2429
{
2430
struct callback_data data;
2431
char *zErrMsg = 0;
2432
open_db (p);
2433
memcpy (&data, p, sizeof (data));
2434
data.showHeader = 0;
2435
data.mode = MODE_List;
2436
zShellStatic = azArg[1];
2437
sqlite3_exec (p->db,
2438
"SELECT name FROM sqlite_master "
2439
"WHERE type='index' AND tbl_name LIKE shellstatic() "
2440
"UNION ALL "
2441
"SELECT name FROM sqlite_temp_master "
2442
"WHERE type='index' AND tbl_name LIKE shellstatic() "
2443
"ORDER BY 1", callback, &data, &zErrMsg);
2444
zShellStatic = 0;
2445
if (zErrMsg)
2446
{
2447
fprintf (stderr, "Error: %s\n", zErrMsg);
2448
sqlite3_free (zErrMsg);
2449
}
2450
}
2451
else
2452
#ifdef SQLITE_ENABLE_IOTRACE
2453
if (c == 'i' && strncmp (azArg[0], "iotrace", n) == 0)
2454
{
2455
extern void (*sqlite3IoTrace) (const char *, ...);
2456
if (iotrace && iotrace != stdout)
2457
fclose (iotrace);
2458
iotrace = 0;
2459
if (nArg < 2)
2460
{
2461
sqlite3IoTrace = 0;
2462
}
2463
else if (strcmp (azArg[1], "-") == 0)
2464
{
2465
sqlite3IoTrace = iotracePrintf;
2466
iotrace = stdout;
2467
}
2468
else
2469
{
2470
iotrace = fopen (azArg[1], "w");
2471
if (iotrace == 0)
2472
{
2473
fprintf (stderr, "cannot open \"%s\"\n", azArg[1]);
2474
sqlite3IoTrace = 0;
2475
}
2476
else
2477
{
2478
sqlite3IoTrace = iotracePrintf;
2479
}
2480
}
2481
}
2482
else
2483
#endif
2484
2485
#ifndef SQLITE_OMIT_LOAD_EXTENSION
2486
if (c == 'l' && strncmp (azArg[0], "load", n) == 0 && nArg >= 2)
2487
{
2488
const char *zFile, *zProc;
2489
char *zErrMsg = 0;
2490
int rc;
2491
zFile = azArg[1];
2492
zProc = nArg >= 3 ? azArg[2] : 0;
2493
open_db (p);
2494
rc = sqlite3_load_extension (p->db, zFile, zProc, &zErrMsg);
2495
if (rc != SQLITE_OK)
2496
{
2497
fprintf (stderr, "%s\n", zErrMsg);
2498
sqlite3_free (zErrMsg);
2499
rc = 1;
2500
}
2501
}
2502
else
2503
#endif
2504
2505
if (c == 'm' && strncmp (azArg[0], "mode", n) == 0 && nArg >= 2)
2506
{
2507
int n2 = strlen (azArg[1]);
2508
if (strncmp (azArg[1], "line", n2) == 0
2509
|| strncmp (azArg[1], "lines", n2) == 0)
2510
{
2511
p->mode = MODE_Line;
2512
}
2513
else if (strncmp (azArg[1], "column", n2) == 0
2514
|| strncmp (azArg[1], "columns", n2) == 0)
2515
{
2516
p->mode = MODE_Column;
2517
}
2518
else if (strncmp (azArg[1], "list", n2) == 0)
2519
{
2520
p->mode = MODE_List;
2521
}
2522
else if (strncmp (azArg[1], "html", n2) == 0)
2523
{
2524
p->mode = MODE_Html;
2525
}
2526
else if (strncmp (azArg[1], "tcl", n2) == 0)
2527
{
2528
p->mode = MODE_Tcl;
2529
}
2530
else if (strncmp (azArg[1], "csv", n2) == 0)
2531
{
2532
p->mode = MODE_Csv;
2533
sqlite3_snprintf (sizeof (p->separator), p->separator, ",");
2534
}
2535
else if (strncmp (azArg[1], "tabs", n2) == 0)
2536
{
2537
p->mode = MODE_List;
2538
sqlite3_snprintf (sizeof (p->separator), p->separator, "\t");
2539
}
2540
else if (strncmp (azArg[1], "insert", n2) == 0)
2541
{
2542
p->mode = MODE_Insert;
2543
if (nArg >= 3)
2544
{
2545
set_table_name (p, azArg[2]);
2546
}
2547
else
2548
{
2549
set_table_name (p, "table");
2550
}
2551
}
2552
else
2553
{
2554
fprintf (stderr, "mode should be one of: "
2555
"column csv html insert line list tabs tcl\n");
2556
}
2557
}
2558
else if (c == 'n' && strncmp (azArg[0], "nullvalue", n) == 0 && nArg == 2)
2559
{
2560
sqlite3_snprintf (sizeof (p->nullvalue), p->nullvalue,
2561
"%.*s", (int) ArraySize (p->nullvalue) - 1,
2562
azArg[1]);
2563
}
2564
else if (c == 'o' && strncmp (azArg[0], "output", n) == 0 && nArg == 2)
2565
{
2566
if (p->out != stdout)
2567
{
2568
fclose (p->out);
2569
}
2570
if (strcmp (azArg[1], "stdout") == 0)
2571
{
2572
p->out = stdout;
2573
sqlite3_snprintf (sizeof (p->outfile), p->outfile, "stdout");
2574
}
2575
else
2576
{
2577
p->out = fopen (azArg[1], "wb");
2578
if (p->out == 0)
2579
{
2580
fprintf (stderr, "can't write to \"%s\"\n", azArg[1]);
2581
p->out = stdout;
2582
}
2583
else
2584
{
2585
sqlite3_snprintf (sizeof (p->outfile), p->outfile, "%s",
2586
azArg[1]);
2587
}
2588
}
2589
}
2590
else if (c == 'p' && strncmp (azArg[0], "prompt", n) == 0
2591
&& (nArg == 2 || nArg == 3))
2592
{
2593
if (nArg >= 2)
2594
{
2595
strncpy (mainPrompt, azArg[1],
2596
(int) ArraySize (mainPrompt) - 1);
2597
}
2598
if (nArg >= 3)
2599
{
2600
strncpy (continuePrompt, azArg[2],
2601
(int) ArraySize (continuePrompt) - 1);
2602
}
2603
}
2604
else if (c == 'q' && strncmp (azArg[0], "quit", n) == 0)
2605
{
2606
rc = 2;
2607
}
2608
else if (c == 's' && strncmp (azArg[0], "schema", n) == 0)
2609
{
2610
struct callback_data data;
2611
char *zErrMsg = 0;
2612
open_db (p);
2613
memcpy (&data, p, sizeof (data));
2614
data.showHeader = 0;
2615
data.mode = MODE_Semi;
2616
if (nArg > 1)
2617
{
2618
int i;
2619
for (i = 0; azArg[1][i]; i++)
2620
azArg[1][i] = tolower (azArg[1][i]);
2621
if (strcmp (azArg[1], "sqlite_master") == 0)
2622
{
2623
char *new_argv[2], *new_colv[2];
2624
new_argv[0] = "CREATE TABLE sqlite_master (\n"
2625
" type text,\n"
2626
" name text,\n"
2627
" tbl_name text,\n"
2628
" rootpage integer,\n" " sql text\n" ")";
2629
new_argv[1] = 0;
2630
new_colv[0] = "sql";
2631
new_colv[1] = 0;
2632
callback (&data, 1, new_argv, new_colv);
2633
}
2634
else if (strcmp (azArg[1], "sqlite_temp_master") == 0)
2635
{
2636
char *new_argv[2], *new_colv[2];
2637
new_argv[0] = "CREATE TEMP TABLE sqlite_temp_master (\n"
2638
" type text,\n"
2639
" name text,\n"
2640
" tbl_name text,\n"
2641
" rootpage integer,\n" " sql text\n" ")";
2642
new_argv[1] = 0;
2643
new_colv[0] = "sql";
2644
new_colv[1] = 0;
2645
callback (&data, 1, new_argv, new_colv);
2646
}
2647
else
2648
{
2649
zShellStatic = azArg[1];
2650
sqlite3_exec (p->db,
2651
"SELECT sql FROM "
2652
" (SELECT * FROM sqlite_master UNION ALL"
2653
" SELECT * FROM sqlite_temp_master) "
2654
"WHERE tbl_name LIKE shellstatic() AND type!='meta' AND sql NOTNULL "
2655
"ORDER BY substr(type,2,1), name",
2656
callback, &data, &zErrMsg);
2657
zShellStatic = 0;
2658
}
2659
}
2660
else
2661
{
2662
sqlite3_exec (p->db,
2663
"SELECT sql FROM "
2664
" (SELECT * FROM sqlite_master UNION ALL"
2665
" SELECT * FROM sqlite_temp_master) "
2666
"WHERE type!='meta' AND sql NOTNULL AND name NOT LIKE 'sqlite_%'"
2667
"ORDER BY substr(type,2,1), name",
2668
callback, &data, &zErrMsg);
2669
}
2670
if (zErrMsg)
2671
{
2672
fprintf (stderr, "Error: %s\n", zErrMsg);
2673
sqlite3_free (zErrMsg);
2674
}
2675
}
2676
else if (c == 's' && strncmp (azArg[0], "separator", n) == 0 && nArg == 2)
2677
{
2678
sqlite3_snprintf (sizeof (p->separator), p->separator,
2679
"%.*s", (int) sizeof (p->separator) - 1, azArg[1]);
2680
}
2681
else if (c == 's' && strncmp (azArg[0], "show", n) == 0)
2682
{
2683
int i;
2684
fprintf (p->out, "%9.9s: %s\n", "echo", p->echoOn ? "on" : "off");
2685
fprintf (p->out, "%9.9s: %s\n", "explain",
2686
p->explainPrev.valid ? "on" : "off");
2687
fprintf (p->out, "%9.9s: %s\n", "headers",
2688
p->showHeader ? "on" : "off");
2689
fprintf (p->out, "%9.9s: %s\n", "mode", modeDescr[p->mode]);
2690
fprintf (p->out, "%9.9s: ", "nullvalue");
2691
output_c_string (p->out, p->nullvalue);
2692
fprintf (p->out, "\n");
2693
fprintf (p->out, "%9.9s: %s\n", "output",
2694
strlen (p->outfile) ? p->outfile : "stdout");
2695
fprintf (p->out, "%9.9s: ", "separator");
2696
output_c_string (p->out, p->separator);
2697
fprintf (p->out, "\n");
2698
fprintf (p->out, "%9.9s: ", "width");
2699
for (i = 0; i < (int) ArraySize (p->colWidth) && p->colWidth[i] != 0;
2700
i++)
2701
{
2702
fprintf (p->out, "%d ", p->colWidth[i]);
2703
}
2704
fprintf (p->out, "\n");
2705
}
2706
else if (c == 't' && n > 1 && strncmp (azArg[0], "tables", n) == 0)
2707
{
2708
char **azResult;
2709
int nRow, rc;
2710
char *zErrMsg;
2711
open_db (p);
2712
if (nArg == 1)
2713
{
2714
rc = sqlite3_get_table (p->db,
2715
"SELECT name FROM sqlite_master "
2716
"WHERE type IN ('table','view') AND name NOT LIKE 'sqlite_%'"
2717
"UNION ALL "
2718
"SELECT name FROM sqlite_temp_master "
2719
"WHERE type IN ('table','view') "
2720
"ORDER BY 1",
2721
&azResult, &nRow, 0, &zErrMsg);
2722
}
2723
else
2724
{
2725
zShellStatic = azArg[1];
2726
rc = sqlite3_get_table (p->db,
2727
"SELECT name FROM sqlite_master "
2728
"WHERE type IN ('table','view') AND name LIKE '%'||shellstatic()||'%' "
2729
"UNION ALL "
2730
"SELECT name FROM sqlite_temp_master "
2731
"WHERE type IN ('table','view') AND name LIKE '%'||shellstatic()||'%' "
2732
"ORDER BY 1",
2733
&azResult, &nRow, 0, &zErrMsg);
2734
zShellStatic = 0;
2735
}
2736
if (zErrMsg)
2737
{
2738
fprintf (stderr, "Error: %s\n", zErrMsg);
2739
sqlite3_free (zErrMsg);
2740
}
2741
if (rc == SQLITE_OK)
2742
{
2743
int len, maxlen = 0;
2744
int i, j;
2745
int nPrintCol, nPrintRow;
2746
for (i = 1; i <= nRow; i++)
2747
{
2748
if (azResult[i] == 0)
2749
continue;
2750
len = strlen (azResult[i]);
2751
if (len > maxlen)
2752
maxlen = len;
2753
}
2754
nPrintCol = 80 / (maxlen + 2);
2755
if (nPrintCol < 1)
2756
nPrintCol = 1;
2757
nPrintRow = (nRow + nPrintCol - 1) / nPrintCol;
2758
for (i = 0; i < nPrintRow; i++)
2759
{
2760
for (j = i + 1; j <= nRow; j += nPrintRow)
2761
{
2762
char *zSp = j <= nPrintRow ? "" : " ";
2763
printf ("%s%-*s", zSp, maxlen,
2764
azResult[j] ? azResult[j] : "");
2765
}
2766
printf ("\n");
2767
}
2768
}
2769
else
2770
{
2771
rc = 1;
2772
}
2773
sqlite3_free_table (azResult);
2774
}
2775
else if (c == 't' && n > 4 && strncmp (azArg[0], "timeout", n) == 0
2776
&& nArg >= 2)
2777
{
2778
open_db (p);
2779
sqlite3_busy_timeout (p->db, atoi (azArg[1]));
2780
}
2781
else
2782
#if HAS_TIMER
2783
if (c == 't' && n >= 5 && strncmp (azArg[0], "timer", n) == 0 && nArg > 1)
2784
{
2785
enableTimer = booleanValue (azArg[1]);
2786
}
2787
else
2788
#endif
2789
2790
if (c == 'w' && strncmp (azArg[0], "width", n) == 0)
2791
{
2792
int j;
2793
assert (nArg <= (int) ArraySize (azArg));
2794
for (j = 1; j < nArg && j < (int) ArraySize (p->colWidth); j++)
2795
{
2796
p->colWidth[j - 1] = atoi (azArg[j]);
2797
}
2798
}
2799
else
2800
2801
2802
{
2803
fprintf (stderr, "unknown command or invalid arguments: "
2804
" \"%s\". Enter \".help\" for help\n", azArg[0]);
2805
}
2806
2807
return rc;
2808
}
2809
2810
/*
2811
** Return TRUE if a semicolon occurs anywhere in the first N characters
2812
** of string z[].
2813
*/
2814
static int
2815
_contains_semicolon (const char *z, int N)
2816
{
2817
int i;
2818
for (i = 0; i < N; i++)
2819
{
2820
if (z[i] == ';')
2821
return 1;
2822
}
2823
return 0;
2824
}
2825
2826
/*
2827
** Test to see if a line consists entirely of whitespace.
2828
*/
2829
static int
2830
_all_whitespace (const char *z)
2831
{
2832
for (; *z; z++)
2833
{
2834
if (isspace (*(unsigned char *) z))
2835
continue;
2836
if (*z == '/' && z[1] == '*')
2837
{
2838
z += 2;
2839
while (*z && (*z != '*' || z[1] != '/'))
2840
{
2841
z++;
2842
}
2843
if (*z == 0)
2844
return 0;
2845
z++;
2846
continue;
2847
}
2848
if (*z == '-' && z[1] == '-')
2849
{
2850
z += 2;
2851
while (*z && *z != '\n')
2852
{
2853
z++;
2854
}
2855
if (*z == 0)
2856
return 1;
2857
continue;
2858
}
2859
return 0;
2860
}
2861
return 1;
2862
}
2863
2864
/*
2865
** Return TRUE if the line typed in is an SQL command terminator other
2866
** than a semi-colon. The SQL Server style "go" command is understood
2867
** as is the Oracle "/".
2868
*/
2869
static int
2870
_is_command_terminator (const char *zLine)
2871
{
2872
while (isspace (*(unsigned char *) zLine))
2873
{
2874
zLine++;
2875
};
2876
if (zLine[0] == '/' && _all_whitespace (&zLine[1]))
2877
return 1; /* Oracle */
2878
if (tolower (zLine[0]) == 'g' && tolower (zLine[1]) == 'o'
2879
&& _all_whitespace (&zLine[2]))
2880
{
2881
return 1; /* SQL Server */
2882
}
2883
return 0;
2884
}
2885
2886
/*
2887
** Read input from *in and process it. If *in==0 then input
2888
** is interactive - the user is typing it it. Otherwise, input
2889
** is coming from a file or device. A prompt is issued and history
2890
** is saved only if input is interactive. An interrupt signal will
2891
** cause this routine to exit immediately, unless input is interactive.
2892
**
2893
** Return the number of errors.
2894
*/
2895
static int
2896
process_input (struct callback_data *p, FILE * in, char *in_charset)
2897
{
2898
char *zLine = 0;
2899
char *zSql = 0;
2900
/* Sandro Furieri - 11 July 2008 - supporting UNICODE */
2901
int utf8len;
2902
char *utf8Sql = 0;
2903
/* End Sandro Furieri - 11 July 2008 */
2904
int nSql = 0;
2905
int nSqlPrior = 0;
2906
char *zErrMsg;
2907
int rc;
2908
int errCnt = 0;
2909
int lineno = 0;
2910
int startline = 0;
2911
2912
/* Sandro Furieri - 11 July 2008 - supporting UNICODE */
2913
if (in_charset)
2914
create_input_utf8_converter (in_charset);
2915
/* End Sandro Furieri - 11 July 2008 */
2916
2917
while (errCnt == 0 || !bail_on_error || (in == 0 && stdin_is_interactive))
2918
{
2919
fflush (p->out);
2920
free (zLine);
2921
zLine = one_input_line (zSql, in);
2922
if (zLine == 0)
2923
{
2924
break; /* We have reached EOF */
2925
}
2926
if (seenInterrupt)
2927
{
2928
if (in != 0)
2929
break;
2930
seenInterrupt = 0;
2931
}
2932
lineno++;
2933
if (p->echoOn)
2934
printf ("%s\n", zLine);
2935
if ((zSql == 0 || zSql[0] == 0) && _all_whitespace (zLine))
2936
continue;
2937
if (zLine && zLine[0] == '.' && nSql == 0)
2938
{
2939
rc = do_meta_command (zLine, p);
2940
if (rc == 2)
2941
{
2942
break;
2943
}
2944
else if (rc)
2945
{
2946
errCnt++;
2947
}
2948
continue;
2949
}
2950
if (_is_command_terminator (zLine))
2951
{
2952
memcpy (zLine, ";", 2);
2953
}
2954
nSqlPrior = nSql;
2955
if (zSql == 0)
2956
{
2957
int i;
2958
for (i = 0; zLine[i] && isspace ((unsigned char) zLine[i]); i++)
2959
{
2960
}
2961
if (zLine[i] != 0)
2962
{
2963
nSql = strlen (zLine);
2964
zSql = malloc (nSql + 1);
2965
if (zSql == 0)
2966
{
2967
fprintf (stderr, "out of memory\n");
2968
exit (1);
2969
}
2970
memcpy (zSql, zLine, nSql + 1);
2971
startline = lineno;
2972
}
2973
}
2974
else
2975
{
2976
int len = strlen (zLine);
2977
zSql = realloc (zSql, nSql + len + 2);
2978
if (zSql == 0)
2979
{
2980
fprintf (stderr, "%s: out of memory!\n", Argv0);
2981
exit (1);
2982
}
2983
zSql[nSql++] = '\n';
2984
memcpy (&zSql[nSql], zLine, len + 1);
2985
nSql += len;
2986
}
2987
if (zSql && _contains_semicolon (&zSql[nSqlPrior], nSql - nSqlPrior)
2988
&& sqlite3_complete (zSql))
2989
{
2990
p->cnt = 0;
2991
open_db (p);
2992
/* Sandro Furieri - 11 July 2008
2993
BEGIN_TIMER;
2994
rc = sqlite3_exec (p->db, zSql, callback, p, &zErrMsg);
2995
END_TIMER;
2996
*/
2997
utf8len = strlen (zSql) * 4;
2998
utf8Sql = malloc (utf8len);
2999
if (utf8Sql == 0)
3000
{
3001
fprintf (stderr, "%s: out of memory!\n", Argv0);
3002
exit (1);
3003
}
3004
strncpy (utf8Sql, zSql, utf8len - 1);
3005
utf8Sql[utf8len - 1] = '\0';
3006
if (!in_charset)
3007
{
3008
/* assuming input is locale_charset encoded */
3009
convert_to_utf8 (utf8Sql, utf8len);
3010
}
3011
else
3012
{
3013
/* input has an explicit charset */
3014
convert_input_to_utf8 (utf8Sql, utf8len);
3015
}
3016
BEGIN_TIMER;
3017
rc = sqlite3_exec (p->db, utf8Sql, callback, p, &zErrMsg);
3018
END_TIMER;
3019
free (utf8Sql);
3020
/* End Sandro Furieri - 11 July 2008 */
3021
if (rc || zErrMsg)
3022
{
3023
char zPrefix[100];
3024
if (in != 0 || !stdin_is_interactive)
3025
{
3026
sqlite3_snprintf (sizeof (zPrefix), zPrefix,
3027
"SQL error near line %d:",
3028
startline);
3029
}
3030
else
3031
{
3032
sqlite3_snprintf (sizeof (zPrefix), zPrefix,
3033
"SQL error:");
3034
}
3035
if (zErrMsg != 0)
3036
{
3037
printf ("%s %s\n", zPrefix, zErrMsg);
3038
sqlite3_free (zErrMsg);
3039
zErrMsg = 0;
3040
}
3041
else
3042
{
3043
printf ("%s %s\n", zPrefix, sqlite3_errmsg (p->db));
3044
}
3045
errCnt++;
3046
}
3047
free (zSql);
3048
zSql = 0;
3049
nSql = 0;
3050
}
3051
}
3052
if (zSql)
3053
{
3054
if (!_all_whitespace (zSql))
3055
printf ("Incomplete SQL: %s\n", zSql);
3056
free (zSql);
3057
}
3058
free (zLine);
3059
3060
/* Sandro Furieri - 11 July 2008 */
3061
if (in_charset_to_utf8)
3062
{
3063
/* destroying input converter, if exists */
3064
iconv_close (in_charset_to_utf8);
3065
in_charset_to_utf8 = NULL;
3066
}
3067
/* End Sandro Furieri - 11 July 2008 */
3068
3069
return errCnt;
3070
}
3071
3072
/*
3073
** Return a pathname which is the user's home directory. A
3074
** 0 return indicates an error of some kind. Space to hold the
3075
** resulting string is obtained from malloc(). The calling
3076
** function should free the result.
3077
*/
3078
static char *
3079
find_home_dir (void)
3080
{
3081
char *home_dir = NULL;
3082
3083
#if !defined(_WIN32) && !defined(WIN32) && !defined(__OS2__) && !defined(_WIN32_WCE)
3084
struct passwd *pwent;
3085
uid_t uid = getuid ();
3086
if ((pwent = getpwuid (uid)) != NULL)
3087
{
3088
home_dir = pwent->pw_dir;
3089
}
3090
#endif
3091
3092
#if defined(_WIN32_WCE)
3093
/* Windows CE (arm-wince-mingw32ce-gcc) does not provide getenv()
3094
*/
3095
home_dir = strdup ("/");
3096
#else
3097
3098
#if defined(_WIN32) || defined(WIN32) || defined(__OS2__)
3099
if (!home_dir)
3100
{
3101
home_dir = getenv ("USERPROFILE");
3102
}
3103
#endif
3104
3105
if (!home_dir)
3106
{
3107
home_dir = getenv ("HOME");
3108
}
3109
3110
#if defined(_WIN32) || defined(WIN32) || defined(__OS2__)
3111
if (!home_dir)
3112
{
3113
char *zDrive, *zPath;
3114
int n;
3115
zDrive = getenv ("HOMEDRIVE");
3116
zPath = getenv ("HOMEPATH");
3117
if (zDrive && zPath)
3118
{
3119
n = strlen (zDrive) + strlen (zPath) + 1;
3120
home_dir = malloc (n);
3121
if (home_dir == 0)
3122
return 0;
3123
sqlite3_snprintf (n, home_dir, "%s%s", zDrive, zPath);
3124
return home_dir;
3125
}
3126
home_dir = "c:\\";
3127
}
3128
#endif
3129
3130
#endif /* !_WIN32_WCE */
3131
3132
if (home_dir)
3133
{
3134
int n = strlen (home_dir) + 1;
3135
char *z = malloc (n);
3136
if (z)
3137
memcpy (z, home_dir, n);
3138
home_dir = z;
3139
}
3140
3141
return home_dir;
3142
}
3143
3144
/*
3145
** Read input from the file given by sqliterc_override. Or if that
3146
** parameter is NULL, take input from ~/.sqliterc
3147
*/
3148
static void
3149
process_sqliterc (struct callback_data *p, /* Configuration data */
3150
const char *sqliterc_override /* Name of config file. NULL to use default */
3151
)
3152
{
3153
char *home_dir = NULL;
3154
const char *sqliterc = sqliterc_override;
3155
char *zBuf = 0;
3156
FILE *in = NULL;
3157
int nBuf;
3158
3159
if (sqliterc == NULL)
3160
{
3161
home_dir = find_home_dir ();
3162
if (home_dir == 0)
3163
{
3164
fprintf (stderr, "%s: cannot locate your home directory!\n",
3165
Argv0);
3166
return;
3167
}
3168
nBuf = strlen (home_dir) + 16;
3169
zBuf = malloc (nBuf);
3170
if (zBuf == 0)
3171
{
3172
fprintf (stderr, "%s: out of memory!\n", Argv0);
3173
exit (1);
3174
}
3175
sqlite3_snprintf (nBuf, zBuf, "%s/.sqliterc", home_dir);
3176
free (home_dir);
3177
sqliterc = (const char *) zBuf;
3178
}
3179
in = fopen (sqliterc, "rb");
3180
if (in)
3181
{
3182
if (stdin_is_interactive)
3183
{
3184
printf ("-- Loading resources from %s\n", sqliterc);
3185
}
3186
process_input (p, in, 0);
3187
fclose (in);
3188
}
3189
free (zBuf);
3190
return;
3191
}
3192
3193
/*
3194
** Show available command line options
3195
*/
3196
static const char zOptions[] =
3197
" -init filename read/process named file\n"
3198
" -echo print commands before execution\n"
3199
" -[no]header turn headers on or off\n"
3200
" -bail stop after hitting an error\n"
3201
" -interactive force interactive I/O\n"
3202
" -batch force batch I/O\n"
3203
" -column set output mode to 'column'\n"
3204
" -csv set output mode to 'csv'\n"
3205
" -html set output mode to HTML\n"
3206
" -line set output mode to 'line'\n"
3207
" -list set output mode to 'list'\n"
3208
" -separator 'x' set output field separator (|)\n"
3209
" -nullvalue 'text' set text string for NULL values\n"
3210
" -version show SQLite version\n";
3211
static void
3212
usage (int showDetail)
3213
{
3214
fprintf (stderr,
3215
"Usage: %s [OPTIONS] FILENAME [SQL]\n"
3216
"FILENAME is the name of an SQLite database. A new database is created\n"
3217
"if the file does not previously exist.\n", Argv0);
3218
if (showDetail)
3219
{
3220
fprintf (stderr, "OPTIONS include:\n%s", zOptions);
3221
}
3222
else
3223
{
3224
fprintf (stderr, "Use the -help option for additional information\n");
3225
}
3226
exit (1);
3227
}
3228
3229
/*
3230
** Initialize the state information in data
3231
*/
3232
static void
3233
main_init (struct callback_data *data)
3234
{
3235
memset (data, 0, sizeof (*data));
3236
data->mode = MODE_List;
3237
memcpy (data->separator, "|", 2);
3238
data->showHeader = 0;
3239
sqlite3_snprintf (sizeof (mainPrompt), mainPrompt, "spatialite> ");
3240
sqlite3_snprintf (sizeof (continuePrompt), continuePrompt, " ...> ");
3241
}
3242
3243
int
3244
main (int argc, char **argv)
3245
{
3246
char *zErrMsg = 0;
3247
struct callback_data data;
3248
const char *zInitFile = 0;
3249
char *zFirstCmd = 0;
3250
int i;
3251
int rc = 0;
3252
3253
Argv0 = argv[0];
3254
main_init (&data);
3255
3256
/*
3257
Sandro Furieri 30 May 2008
3258
===========================
3259
registering the SpatiaLite extension
3260
*/
3261
spatialite_init (1);
3262
3263
stdin_is_interactive = isatty (0);
3264
3265
/* Make sure we have a valid signal handler early, before anything
3266
** else is done.
3267
*/
3268
#ifdef SIGINT
3269
signal (SIGINT, interrupt_handler);
3270
#endif
3271
3272
/* Do an initial pass through the command-line argument to locate
3273
** the name of the database file, the name of the initialization file,
3274
** and the first command to execute.
3275
*/
3276
for (i = 1; i < argc - 1; i++)
3277
{
3278
char *z;
3279
if (argv[i][0] != '-')
3280
break;
3281
z = argv[i];
3282
if (z[0] == '-' && z[1] == '-')
3283
z++;
3284
if (strcmp (argv[i], "-separator") == 0
3285
|| strcmp (argv[i], "-nullvalue") == 0)
3286
{
3287
i++;
3288
}
3289
else if (strcmp (argv[i], "-init") == 0)
3290
{
3291
i++;
3292
zInitFile = argv[i];
3293
}
3294
}
3295
if (i < argc)
3296
{
3297
#ifdef OS_OS2
3298
data.zDbFilename = (const char *) convertCpPathToUtf8 (argv[i++]);
3299
#else
3300
data.zDbFilename = argv[i++];
3301
#endif
3302
}
3303
else
3304
{
3305
#ifndef SQLITE_OMIT_MEMORYDB
3306
data.zDbFilename = ":memory:";
3307
#else
3308
data.zDbFilename = 0;
3309
#endif
3310
}
3311
if (i < argc)
3312
{
3313
zFirstCmd = argv[i++];
3314
}
3315
data.out = stdout;
3316
3317
#ifdef SQLITE_OMIT_MEMORYDB
3318
if (data.zDbFilename == 0)
3319
{
3320
fprintf (stderr, "%s: no database filename specified\n", argv[0]);
3321
exit (1);
3322
}
3323
#endif
3324
3325
/* Go ahead and open the database file if it already exists. If the
3326
** file does not exist, delay opening it. This prevents empty database
3327
** files from being created if a user mistypes the database name argument
3328
** to the sqlite command-line tool.
3329
*/
3330
if (access (data.zDbFilename, 0) == 0)
3331
{
3332
open_db (&data);
3333
}
3334
3335
/* Process the initialization file if there is one. If no -init option
3336
** is given on the command line, look for a file named ~/.sqliterc and
3337
** try to process it.
3338
*/
3339
process_sqliterc (&data, zInitFile);
3340
3341
/* Make a second pass through the command-line argument and set
3342
** options. This second pass is delayed until after the initialization
3343
** file is processed so that the command-line arguments will override
3344
** settings in the initialization file.
3345
*/
3346
for (i = 1; i < argc && argv[i][0] == '-'; i++)
3347
{
3348
char *z = argv[i];
3349
if (z[1] == '-')
3350
{
3351
z++;
3352
}
3353
if (strcmp (z, "-init") == 0)
3354
{
3355
i++;
3356
}
3357
else if (strcmp (z, "-html") == 0)
3358
{
3359
data.mode = MODE_Html;
3360
}
3361
else if (strcmp (z, "-list") == 0)
3362
{
3363
data.mode = MODE_List;
3364
}
3365
else if (strcmp (z, "-line") == 0)
3366
{
3367
data.mode = MODE_Line;
3368
}
3369
else if (strcmp (z, "-column") == 0)
3370
{
3371
data.mode = MODE_Column;
3372
}
3373
else if (strcmp (z, "-csv") == 0)
3374
{
3375
data.mode = MODE_Csv;
3376
memcpy (data.separator, ",", 2);
3377
}
3378
else if (strcmp (z, "-separator") == 0)
3379
{
3380
i++;
3381
sqlite3_snprintf (sizeof (data.separator), data.separator,
3382
"%.*s", (int) sizeof (data.separator) - 1,
3383
argv[i]);
3384
}
3385
else if (strcmp (z, "-nullvalue") == 0)
3386
{
3387
i++;
3388
sqlite3_snprintf (sizeof (data.nullvalue), data.nullvalue,
3389
"%.*s", (int) sizeof (data.nullvalue) - 1,
3390
argv[i]);
3391
}
3392
else if (strcmp (z, "-header") == 0)
3393
{
3394
data.showHeader = 1;
3395
}
3396
else if (strcmp (z, "-noheader") == 0)
3397
{
3398
data.showHeader = 0;
3399
}
3400
else if (strcmp (z, "-echo") == 0)
3401
{
3402
data.echoOn = 1;
3403
}
3404
else if (strcmp (z, "-bail") == 0)
3405
{
3406
bail_on_error = 1;
3407
}
3408
else if (strcmp (z, "-version") == 0)
3409
{
3410
printf ("%s\n", sqlite3_libversion ());
3411
return 0;
3412
}
3413
else if (strcmp (z, "-interactive") == 0)
3414
{
3415
stdin_is_interactive = 1;
3416
}
3417
else if (strcmp (z, "-batch") == 0)
3418
{
3419
stdin_is_interactive = 0;
3420
}
3421
else if (strcmp (z, "-help") == 0 || strcmp (z, "--help") == 0)
3422
{
3423
usage (1);
3424
}
3425
else
3426
{
3427
fprintf (stderr, "%s: unknown option: %s\n", Argv0, z);
3428
fprintf (stderr, "Use -help for a list of options.\n");
3429
return 1;
3430
}
3431
}
3432
3433
if (zFirstCmd)
3434
{
3435
/* Run just the command that follows the database name
3436
*/
3437
if (zFirstCmd[0] == '.')
3438
{
3439
do_meta_command (zFirstCmd, &data);
3440
exit (0);
3441
}
3442
else
3443
{
3444
int rc;
3445
open_db (&data);
3446
rc = sqlite3_exec (data.db, zFirstCmd, callback, &data,
3447
&zErrMsg);
3448
if (rc != 0 && zErrMsg != 0)
3449
{
3450
fprintf (stderr, "SQL error: %s\n", zErrMsg);
3451
exit (1);
3452
}
3453
}
3454
}
3455
else
3456
{
3457
/* Run commands received from standard input
3458
*/
3459
if (stdin_is_interactive)
3460
{
3461
char *zHome;
3462
char *zHistory = 0;
3463
int nHistory;
3464
/* Sandro Furieri 2008-11-20
3465
printf ("SQLite version ......: %s\n"
3466
"Enter \".help\" for instructions\n",
3467
sqlite3_libversion ());
3468
*/
3469
if (isatty (1))
3470
printf ("SQLite version ......: %s\n",
3471
sqlite3_libversion ());
3472
auto_fdo_start (data.db);
3473
if (isatty (1))
3474
printf ("Enter \".help\" for instructions\n");
3475
/* end Sandro Furieri 2008-11-20 */
3476
zHome = find_home_dir ();
3477
if (zHome
3478
&& (zHistory =
3479
malloc (nHistory = strlen (zHome) + 20)) != 0)
3480
{
3481
sqlite3_snprintf (nHistory, zHistory,
3482
"%s/.sqlite_history", zHome);
3483
}
3484
#if defined(HAVE_READLINE) && HAVE_READLINE==1
3485
if (zHistory)
3486
read_history (zHistory);
3487
#endif
3488
rc = process_input (&data, 0, 0);
3489
if (zHistory)
3490
{
3491
stifle_history (100);
3492
write_history (zHistory);
3493
free (zHistory);
3494
}
3495
free (zHome);
3496
}
3497
else
3498
{
3499
rc = process_input (&data, stdin, 0);
3500
}
3501
}
3502
set_table_name (&data, 0);
3503
if (db)
3504
{
3505
/* Sandro Furieri 2008-11-20 */
3506
auto_fdo_stop (db);
3507
/* end Sandro Furieri 2008-11-20 */
3508
if (sqlite3_close (db) != SQLITE_OK)
3509
{
3510
fprintf (stderr, "error closing database: %s\n",
3511
sqlite3_errmsg (db));
3512
}
3513
}
3514
3515
3516
/*
3517
Sandro Furieri 30 May 2008
3518
===========================
3519
memory cleanup for SpatiaLite extension
3520
*/
3521
sqlite3_reset_auto_extension ();
3522
3523
return rc;
3524
}
Loggerhead is a web-based interface for Breezy
Version: 2.0.1