SQLite

Check-in [ab9c533a3a]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment::-) (CVS 125)
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: ab9c533a3a256ca9d59a6a580c6064c903d962a5
User & Date: drh 2000-08-04 13:49:02.000
Context
2000-08-04
13:49
:-) (CVS 1702) (check-in: 14785d94fb user: drh tags: trunk)
13:49
:-) (CVS 125) (check-in: ab9c533a3a user: drh tags: trunk)
2000-08-03
15:13
spelling error (CVS 124) (check-in: 577421e5d3 user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/tclsqlite.c.
19
20
21
22
23
24
25
26
27
28
29
30
31











32
33
34
35
36
37
38
** Author contact information:
**   drh@hwaci.com
**   http://www.hwaci.com/drh/
**
*************************************************************************
** A TCL Interface to SQLite
**
** $Id: tclsqlite.c,v 1.5 2000/06/04 12:58:38 drh Exp $
*/
#include "sqlite.h"
#include <tcl.h>
#include <stdlib.h>
#include <string.h>












/*
** An instance of this structure passes information thru the sqlite
** logic from the original TCL command into the callback routine.
*/
typedef struct CallbackData CallbackData;
struct CallbackData {







|





>
>
>
>
>
>
>
>
>
>
>







19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
** Author contact information:
**   drh@hwaci.com
**   http://www.hwaci.com/drh/
**
*************************************************************************
** A TCL Interface to SQLite
**
** $Id: tclsqlite.c,v 1.6 2000/08/04 13:49:02 drh Exp $
*/
#include "sqlite.h"
#include <tcl.h>
#include <stdlib.h>
#include <string.h>

/*
** There is one instance of this structure for each SQLite database
** that has been opened by the SQLite TCL interface.
*/
typedef struct SqliteDb SqliteDb;
struct SqliteDb {
  sqlite *db;           /* The "real" database structure */
  Tcl_Interp *interp;   /* The interpreter used for this database */
  char *zBusy;          /* The name of the busy callback routine */
};

/*
** An instance of this structure passes information thru the sqlite
** logic from the original TCL command into the callback routine.
*/
typedef struct CallbackData CallbackData;
struct CallbackData {
77
78
79
80
81
82
83

84






























85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
































112
113
114
115
116
117
118
  return rc;
}

/*
** Called when the command is deleted.
*/
static void DbDeleteCmd(void *db){

  sqlite_close((sqlite*)db);






























}

/*
** The "sqlite" command below creates a new Tcl command for each
** connection it opens to an SQLite database.  This routine is invoked
** whenever one of those connection-specific commands is executed
** in Tcl.  For example, if you run Tcl code like this:
**
**       sqlite db1  "my_database"
**       db1 close
**
** The first command opens a connection to the "my_database" database
** and calls that connection "db1".  The second command causes this
** subroutine to be invoked.
*/
static int DbCmd(void *cd, Tcl_Interp *interp, int argc, char **argv){
  char *z;
  int n, c;
  sqlite *db = cd;
  if( argc<2 ){
    Tcl_AppendResult(interp,"wrong # args: should be \"", argv[0],
        " SUBCOMMAND ...\"", 0);
    return TCL_ERROR;
  }
  z = argv[1];
  n = strlen(z);
  c = z[0];

































  /*    $db close
  **
  ** Shutdown the database
  */
  if( c=='c' && n>=2 && strncmp(z,"close",n)==0 ){
    Tcl_DeleteCommand(interp, argv[0]);







>
|
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>


















|








>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
  return rc;
}

/*
** Called when the command is deleted.
*/
static void DbDeleteCmd(void *db){
  SqliteDb *pDb = (SqliteDb*)db;
  sqlite_close(pDb->db);
  if( pDb->zBusy ){
    Tcl_Free(pDb->zBusy);
  }
  Tcl_Free((char*)pDb);
}

/*
** This routine is called when a database file is locked while trying
** to execute SQL.
*/
static int DbBusyHandler(void *cd, const char *zTable, int nTries){
  SqliteDb *pDb = (SqliteDb*)cd;
  int rc;
  char zVal[30];
  char *zCmd;
  char *zResult;
  Tcl_DString cmd;

  Tcl_DStringInit(&cmd);
  Tcl_DStringAppend(&cmd, pDb->zBusy, -1);
  Tcl_DStringAppendElement(&cmd, zTable);
  sprintf(zVal, " %d", nTries);
  Tcl_DStringAppend(&cmd, zVal, -1);
  zCmd = Tcl_DStringValue(&cmd);
  rc = Tcl_Eval(pDb->interp, zCmd);
  Tcl_DStringFree(&cmd);
  if( rc!=TCL_OK || atoi(Tcl_GetStringResult(pDb->interp)) ){
    return 0;
  }
  return 1;
}

/*
** The "sqlite" command below creates a new Tcl command for each
** connection it opens to an SQLite database.  This routine is invoked
** whenever one of those connection-specific commands is executed
** in Tcl.  For example, if you run Tcl code like this:
**
**       sqlite db1  "my_database"
**       db1 close
**
** The first command opens a connection to the "my_database" database
** and calls that connection "db1".  The second command causes this
** subroutine to be invoked.
*/
static int DbCmd(void *cd, Tcl_Interp *interp, int argc, char **argv){
  char *z;
  int n, c;
  SqliteDb *pDb = (SqliteDb*)cd;
  if( argc<2 ){
    Tcl_AppendResult(interp,"wrong # args: should be \"", argv[0],
        " SUBCOMMAND ...\"", 0);
    return TCL_ERROR;
  }
  z = argv[1];
  n = strlen(z);
  c = z[0];

  /*    $db busy ?CALLBACK?
  **
  ** Invoke the given callback if an SQL statement attempts to open
  ** a locked database file.
  */
  if( c=='b' && strncmp(z,"busy",n)==0 ){
    if( argc>3 ){
      Tcl_AppendResult(interp,"wrong # args: should be \"",
         argv[0], " busy ?CALLBACK?", 0);
      return TCL_ERROR;
    }else if( argc==2 ){
      if( pDb->zBusy ){
        Tcl_AppendResult(interp, pDb->zBusy, 0);
      }
    }else{
      if( pDb->zBusy ){
        Tcl_Free(pDb->zBusy);
        pDb->zBusy = 0;
      }
      if( argv[2][0] ){
        pDb->zBusy = Tcl_Alloc( strlen(argv[2]) + 1 );
        if( pDb->zBusy ){
          strcpy(pDb->zBusy, argv[2]);
        }
      }
      if( pDb->zBusy ){
        pDb->interp = interp;
        sqlite_busy_handler(pDb->db, DbBusyHandler, pDb);
      }
    }
  }else

  /*    $db close
  **
  ** Shutdown the database
  */
  if( c=='c' && n>=2 && strncmp(z,"close",n)==0 ){
    Tcl_DeleteCommand(interp, argv[0]);
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156

157
158
159
160
161
162
163
164
165
166
167
168
169
170
171

172















173
174
175
176

177
178
179
180
181
182
183
    Tcl_SetResult(interp, zRes, TCL_VOLATILE);
  }else
   
  /*
  **    $db eval $sql ?array {  ...code... }?
  **
  ** The SQL statement in $sql is evaluated.  For each row, the values are
  ** placed in elements of the array named "array" and ...code.. is executed.
  ** If "array" and "code" are omitted, then no callback is every invoked.
  ** If "array" is an empty string, then the values are placed in variables
  ** that have the same name as the fields extracted by the query.
  */
  if( c=='e' && strncmp(z,"eval",n)==0 ){
    CallbackData cbData;
    char *zErrMsg;
    int rc;

    if( argc!=5 && argc!=3 ){
      Tcl_AppendResult(interp,"wrong # args: should be \"", argv[0],
         " eval SQL ?ARRAY-NAME CODE?", 0);
      return TCL_ERROR;
    }

    if( argc==5 ){
      cbData.interp = interp;
      cbData.once = 1;
      cbData.zArray = argv[3];
      cbData.zCode = argv[4];
      zErrMsg = 0;
      rc = sqlite_exec(db, argv[2], DbEvalCallback, &cbData, &zErrMsg);
    }else{
      rc = sqlite_exec(db, argv[2], 0, 0, &zErrMsg);
    }
    if( zErrMsg ){
      Tcl_SetResult(interp, zErrMsg, TCL_VOLATILE);
      free(zErrMsg);
    }
    return rc;

  }
















  /* The default
  */
  else{

    Tcl_AppendResult(interp,"unknown subcommand \"", z, 
        "\" - should be one of: close complete eval", 0);
    return TCL_ERROR;
  }
  return TCL_OK;
}








|














>






|

|






>
|
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>



<
>







209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266

267
268
269
270
271
272
273
274
    Tcl_SetResult(interp, zRes, TCL_VOLATILE);
  }else
   
  /*
  **    $db eval $sql ?array {  ...code... }?
  **
  ** The SQL statement in $sql is evaluated.  For each row, the values are
  ** placed in elements of the array named "array" and ...code... is executed.
  ** If "array" and "code" are omitted, then no callback is every invoked.
  ** If "array" is an empty string, then the values are placed in variables
  ** that have the same name as the fields extracted by the query.
  */
  if( c=='e' && strncmp(z,"eval",n)==0 ){
    CallbackData cbData;
    char *zErrMsg;
    int rc;

    if( argc!=5 && argc!=3 ){
      Tcl_AppendResult(interp,"wrong # args: should be \"", argv[0],
         " eval SQL ?ARRAY-NAME CODE?", 0);
      return TCL_ERROR;
    }
    pDb->interp = interp;
    if( argc==5 ){
      cbData.interp = interp;
      cbData.once = 1;
      cbData.zArray = argv[3];
      cbData.zCode = argv[4];
      zErrMsg = 0;
      rc = sqlite_exec(pDb->db, argv[2], DbEvalCallback, &cbData, &zErrMsg);
    }else{
      rc = sqlite_exec(pDb->db, argv[2], 0, 0, &zErrMsg);
    }
    if( zErrMsg ){
      Tcl_SetResult(interp, zErrMsg, TCL_VOLATILE);
      free(zErrMsg);
    }
    return rc;
  }else

  /*
  **     $db timeout MILLESECONDS
  **
  ** Delay for the number of milliseconds specified when a file is locked.
  */
  if( c=='t' && strncmp(z,"timeout",n)==0 ){
    int ms;
    if( argc!=3 ){
      Tcl_AppendResult(interp,"wrong # args: should be \"", argv[0],
          " timeout MILLISECONDS", 0);
      return TCL_ERROR;
    }
    if( Tcl_GetInt(interp, argv[2], &ms) ) return TCL_ERROR;
    sqlite_busy_timeout(pDb->db, ms);
  }else

  /* The default
  */

  {
    Tcl_AppendResult(interp,"unknown subcommand \"", z, 
        "\" - should be one of: close complete eval", 0);
    return TCL_ERROR;
  }
  return TCL_OK;
}

193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212






213
214
215

216
217
218
219
220
221
222
223
224
225
226
** connection is deleted when the DBNAME command is deleted.
**
** The second argument is the name of the directory that contains
** the sqlite database that is to be accessed.
*/
static int DbMain(void *cd, Tcl_Interp *interp, int argc, char **argv){
  int mode;
  sqlite *p;
  char *zErrMsg;
  if( argc!=3 && argc!=4 ){
    Tcl_AppendResult(interp,"wrong # args: should be \"", argv[0],
       " HANDLE FILENAME ?MODE?\"", 0);
    return TCL_ERROR;
  }
  if( argc==3 ){
    mode = 0666;
  }else if( Tcl_GetInt(interp, argv[3], &mode)!=TCL_OK ){
    return TCL_ERROR;
  }
  zErrMsg = 0;






  p = sqlite_open(argv[2], mode, &zErrMsg);
  if( p==0 ){
    Tcl_SetResult(interp, zErrMsg, TCL_VOLATILE);

    free(zErrMsg);
    return TCL_ERROR;
  }
  Tcl_CreateCommand(interp, argv[1], DbCmd, p, DbDeleteCmd);
  return TCL_OK;
}

/*
** Initialize this module.
**
** This Tcl module contains only a single new Tcl command named "sqlite".







|












>
>
>
>
>
>
|
|

>



|







284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
** connection is deleted when the DBNAME command is deleted.
**
** The second argument is the name of the directory that contains
** the sqlite database that is to be accessed.
*/
static int DbMain(void *cd, Tcl_Interp *interp, int argc, char **argv){
  int mode;
  SqliteDb *p;
  char *zErrMsg;
  if( argc!=3 && argc!=4 ){
    Tcl_AppendResult(interp,"wrong # args: should be \"", argv[0],
       " HANDLE FILENAME ?MODE?\"", 0);
    return TCL_ERROR;
  }
  if( argc==3 ){
    mode = 0666;
  }else if( Tcl_GetInt(interp, argv[3], &mode)!=TCL_OK ){
    return TCL_ERROR;
  }
  zErrMsg = 0;
  p = Tcl_Alloc( sizeof(*p) );
  if( p==0 ){
    Tcl_SetResult(interp, "malloc failed", TCL_STATIC);
    return TCL_ERROR;
  }
  memset(p, 0, sizeof(*p));
  p->db = sqlite_open(argv[2], mode, &zErrMsg);
  if( p->db==0 ){
    Tcl_SetResult(interp, zErrMsg, TCL_VOLATILE);
    Tcl_Free((char*)p);
    free(zErrMsg);
    return TCL_ERROR;
  }
  Tcl_CreateCommand(interp, argv[1], DbCmd, (char*)p, DbDeleteCmd);
  return TCL_OK;
}

/*
** Initialize this module.
**
** This Tcl module contains only a single new Tcl command named "sqlite".
Changes to www/fileformat.tcl.
1
2
3
4
5
6
7
8
9
10
11
#
# Run this Tcl script to generate the fileformat.html file.
#
set rcsid {$Id: fileformat.tcl,v 1.3 2000/08/02 12:26:30 drh Exp $}

puts {<html>
<head>
  <title>The SQLite file format</title>
</head>
<body bgcolor=white>
<h1 align=center>



|







1
2
3
4
5
6
7
8
9
10
11
#
# Run this Tcl script to generate the fileformat.html file.
#
set rcsid {$Id: fileformat.tcl,v 1.4 2000/08/04 13:49:03 drh Exp $}

puts {<html>
<head>
  <title>The SQLite file format</title>
</head>
<body bgcolor=white>
<h1 align=center>
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
are just the text values for <b>a</b> columns of table <b>t1</b>.
The data for each record of the index is a list of integers
where each integer is the GDBM key for an entry in the <b>t1</b>
table that has the corresponding value for the <b>a</b> column.</p>
The index entry for -11 contains only a single entry and is 4
bytes in size.  The index entry for 10 is 16 bytes in size but
contains only 2 entries.  The first integer is the number of
entires.  The two integer keys follow.  The last 4 bytes unused
and are set to zero.
}

puts {
<p><hr /></p>
<p><a href="index.html"><img src="/goback.jpg" border=0 />
Back to the SQLite Home Page</a>
</p>

</body></html>}







|
<









211
212
213
214
215
216
217
218

219
220
221
222
223
224
225
226
227
are just the text values for <b>a</b> columns of table <b>t1</b>.
The data for each record of the index is a list of integers
where each integer is the GDBM key for an entry in the <b>t1</b>
table that has the corresponding value for the <b>a</b> column.</p>
The index entry for -11 contains only a single entry and is 4
bytes in size.  The index entry for 10 is 16 bytes in size but
contains only 2 entries.  The first integer is the number of
entires.  The two integer keys follow.  The last 4 bytes are unused.

}

puts {
<p><hr /></p>
<p><a href="index.html"><img src="/goback.jpg" border=0 />
Back to the SQLite Home Page</a>
</p>

</body></html>}
Changes to www/index.tcl.
1
2
3
4
5
6
7
8
9
10
11
#
# Run this TCL script to generate HTML for the index.html file.
#
set rcsid {$Id: index.tcl,v 1.25 2000/08/03 15:13:30 drh Exp $}

puts {<html>
<head><title>SQLite: An SQL Database Engine Built Atop GDBM</title></head>
<body bgcolor=white>
<h1 align=center>SQLite: An SQL Database Engine Built Atop
<a href="http://www.gnu.org/software/gdbm/gdbm.html">GDBM</a></h1>
<p align=center>}



|







1
2
3
4
5
6
7
8
9
10
11
#
# Run this TCL script to generate HTML for the index.html file.
#
set rcsid {$Id: index.tcl,v 1.26 2000/08/04 13:49:03 drh Exp $}

puts {<html>
<head><title>SQLite: An SQL Database Engine Built Atop GDBM</title></head>
<body bgcolor=white>
<h1 align=center>SQLite: An SQL Database Engine Built Atop
<a href="http://www.gnu.org/software/gdbm/gdbm.html">GDBM</a></h1>
<p align=center>}
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146



147
148
149
150
151
152
153
<p>You can download a tarball containing all source
code for SQLite (including the TCL scripts that generate the
HTML files for this website) at <a href="sqlite.tar.gz">sqlite.tar.gz</a>.}
puts "This is a [file size sqlite.tar.gz] byte download.  The
tarball was last modified at [clock format [file mtime sqlite.tar.gz]]"
puts {</p>

<p>To build sqlite, just unwrap the tarball, create a separate
build directory, run configure from the build directory and then
type "make".  For example:</p>

<blockquote><pre>
$ tar xzf sqlite.tar.gz      <i> Unpacks into directory named "sqlite" </i>
$ mkdir bld                  <i> Create a separate build directory </i>
$ cd bld
$ ../sqlite/configure
$ make                       <i> Builds "sqlite" and "libsqlite.a" </i>
$ make test                  <i> Optional: run regression tests </i>
</pre></blockquote>



}

puts {<h2>Command-line Usage Example</h2>

<p>Download the source archive and compile the <b>sqlite</b>
program as described above.  The type:</p>








|











>
>
>







128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
<p>You can download a tarball containing all source
code for SQLite (including the TCL scripts that generate the
HTML files for this website) at <a href="sqlite.tar.gz">sqlite.tar.gz</a>.}
puts "This is a [file size sqlite.tar.gz] byte download.  The
tarball was last modified at [clock format [file mtime sqlite.tar.gz]]"
puts {</p>

<p>To build sqlite under Unix, just unwrap the tarball, create a separate
build directory, run configure from the build directory and then
type "make".  For example:</p>

<blockquote><pre>
$ tar xzf sqlite.tar.gz      <i> Unpacks into directory named "sqlite" </i>
$ mkdir bld                  <i> Create a separate build directory </i>
$ cd bld
$ ../sqlite/configure
$ make                       <i> Builds "sqlite" and "libsqlite.a" </i>
$ make test                  <i> Optional: run regression tests </i>
</pre></blockquote>

<p>Instructions for building SQLite for WindowsNT are
found <a href="crosscompile.html">here</a>.
}

puts {<h2>Command-line Usage Example</h2>

<p>Download the source archive and compile the <b>sqlite</b>
program as described above.  The type:</p>

Changes to www/lang.tcl.
1
2
3
4
5
6
7
8
9
10
11
#
# Run this Tcl script to generate the sqlite.html file.
#
set rcsid {$Id: lang.tcl,v 1.4 2000/06/09 14:14:34 drh Exp $}

puts {<html>
<head>
  <title>Query Language Understood By SQLite</title>
</head>
<body bgcolor=white>
<h1 align=center>



|







1
2
3
4
5
6
7
8
9
10
11
#
# Run this Tcl script to generate the sqlite.html file.
#
set rcsid {$Id: lang.tcl,v 1.5 2000/08/04 13:49:03 drh Exp $}

puts {<html>
<head>
  <title>Query Language Understood By SQLite</title>
</head>
<body bgcolor=white>
<h1 align=center>
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
ON <table-name> ( <column-name> [, <column-name>]* )
} {column-name} {
<name> [ ASC | DESC ]
}

puts {
<p>The CREATE INDEX command consists of the keywords "CREATE INDEX" followed
by the name of the new index, the keyword "ON" the name of a previously
created table that is to be indexed, and a parenthesized list of names of
columns in the table that are used for the index key.
Each column name can be followed by one of the "ASC" or "DESC" keywords
to indicate sort order, but since GDBM does not implement ordered keys,
these keywords are ignored.</p>

<p>There are no arbitrary limits on the number of indices that can be







|







135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
ON <table-name> ( <column-name> [, <column-name>]* )
} {column-name} {
<name> [ ASC | DESC ]
}

puts {
<p>The CREATE INDEX command consists of the keywords "CREATE INDEX" followed
by the name of the new index, the keyword "ON", the name of a previously
created table that is to be indexed, and a parenthesized list of names of
columns in the table that are used for the index key.
Each column name can be followed by one of the "ASC" or "DESC" keywords
to indicate sort order, but since GDBM does not implement ordered keys,
these keywords are ignored.</p>

<p>There are no arbitrary limits on the number of indices that can be
Changes to www/sqlite.tcl.
1
2
3
4
5
6
7
8
9
10
11
#
# Run this Tcl script to generate the sqlite.html file.
#
set rcsid {$Id: sqlite.tcl,v 1.11 2000/07/28 14:32:51 drh Exp $}

puts {<html>
<head>
  <title>sqlite: A program of interacting with SQLite databases</title>
</head>
<body bgcolor=white>
<h1 align=center>



|







1
2
3
4
5
6
7
8
9
10
11
#
# Run this Tcl script to generate the sqlite.html file.
#
set rcsid {$Id: sqlite.tcl,v 1.12 2000/08/04 13:49:03 drh Exp $}

puts {<html>
<head>
  <title>sqlite: A program of interacting with SQLite databases</title>
</head>
<body bgcolor=white>
<h1 align=center>
24
25
26
27
28
29
30
31

32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
<h2>Getting Started</h2>

<p>To start the <b>sqlite</b> program, just type "sqlite" followed by
the name of an SQLite database.  An SQLite database is really just
a directory full of GDBM files, so the argument to the sqlite command
should really be the name of a directory on your disk.  If that
directory did not previously contain an SQLite database, a new one
is created for you automatically.  The <b>sqlite</b> program will

prompt you to enter SQL.  Type in SQL statements (terminated by a
semicolon), press "Enter" and the SQL will be executed.  It's as
simple as that!</p>

<p>For example, to create a new SQLite database named "ex1" 
with a single table named "tbl1", you might do this:</p>
}

proc Code {body} {
  puts {<blockquote><pre>}
  regsub -all {&} [string trim $body] {\&amp;} body
  regsub -all {>} $body {\&gt;} body
  regsub -all {<} $body {\&lt;} body
  regsub -all {\(\(\(} $body {<font color="#00671f"><u>} body
  regsub -all {\)\)\)} $body {</u></font>} body
  puts $body
  puts {</pre></blockquote>}
}

Code {
$ (((mkdir ex1)))
$ (((sqlite ex1)))
Enter ".help" for instructions
sqlite> (((create table tbl1(one varchar(10), two smallint);)))
sqlite> (((insert into tbl1 values('hello!',10);)))
sqlite> (((insert into tbl1 values('goodbye', 20);)))
sqlite> (((select * from tbl1;)))
hello!|10







|
>
|



















<







24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52

53
54
55
56
57
58
59
<h2>Getting Started</h2>

<p>To start the <b>sqlite</b> program, just type "sqlite" followed by
the name of an SQLite database.  An SQLite database is really just
a directory full of GDBM files, so the argument to the sqlite command
should really be the name of a directory on your disk.  If that
directory did not previously contain an SQLite database, a new one
is created for you automatically.  If the directory did not previously
exist, it is automatically created.  The <b>sqlite</b> program will
then prompt you to enter SQL.  Type in SQL statements (terminated by a
semicolon), press "Enter" and the SQL will be executed.  It's as
simple as that!</p>

<p>For example, to create a new SQLite database named "ex1" 
with a single table named "tbl1", you might do this:</p>
}

proc Code {body} {
  puts {<blockquote><pre>}
  regsub -all {&} [string trim $body] {\&amp;} body
  regsub -all {>} $body {\&gt;} body
  regsub -all {<} $body {\&lt;} body
  regsub -all {\(\(\(} $body {<font color="#00671f"><u>} body
  regsub -all {\)\)\)} $body {</u></font>} body
  puts $body
  puts {</pre></blockquote>}
}

Code {

$ (((sqlite ex1)))
Enter ".help" for instructions
sqlite> (((create table tbl1(one varchar(10), two smallint);)))
sqlite> (((insert into tbl1 values('hello!',10);)))
sqlite> (((insert into tbl1 values('goodbye', 20);)))
sqlite> (((select * from tbl1;)))
hello!|10
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252







253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272













273
274
275
276
277
278
279
----------  ----------
hello       10        
goodbye     20        
sqlite>
}

puts {
<p>By default, each column is 10 characters wide. 
Data that is too wide to fit in a column is truncated.  You can
adjust the column widths using the ".width" command.  Like this:</p>}

Code {
sqlite> (((.width 12 6)))
sqlite> (((select * from tbl1;)))
one           two   
------------  ------
hello         10    
goodbye       20    
sqlite>
}

puts {
<p>The ".width" command in the example above sets the width of the first
column to 12 and the width of the second column to 6.  All other column
widths were unaltered.  You can gives as many arguments to ".width" as
necessary to specify the widths of as many columns as are in your
query results.</p>








<p>The column labels that appear on the first two lines of output
can be turned on and off using the ".header" dot command.  In the
examples above, the column labels are on.  To turn them off you
could do this:</p>}

Code {
sqlite> (((.header off)))
sqlite> (((select * from tbl1;)))
hello         10    
goodbye       20    
sqlite>
}

puts {
<p>Another useful output mode is "insert".  In insert mode, the output
is formatted to look like SQL INSERT statements.  You can use insert
mode to generate text that can later be used to input data into a 
different database.</p>














<p>The last output mode is "html".  In this mode, sqlite writes
the results of the query as an XHTML table.  The beginning
&lt;TABLE&gt; and the ending &lt;/TABLE&gt; are not written, but
all of the intervening &lt;TR&gt;s, &lt;TH&gt;s, and &lt;TD&gt;s
are.  The html output mode is envisioned as being useful for
CGI.</p>
}







|



















>
>
>
>
>
>
>




















>
>
>
>
>
>
>
>
>
>
>
>
>







226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
----------  ----------
hello       10        
goodbye     20        
sqlite>
}

puts {
<p>By default, each column is at least 10 characters wide. 
Data that is too wide to fit in a column is truncated.  You can
adjust the column widths using the ".width" command.  Like this:</p>}

Code {
sqlite> (((.width 12 6)))
sqlite> (((select * from tbl1;)))
one           two   
------------  ------
hello         10    
goodbye       20    
sqlite>
}

puts {
<p>The ".width" command in the example above sets the width of the first
column to 12 and the width of the second column to 6.  All other column
widths were unaltered.  You can gives as many arguments to ".width" as
necessary to specify the widths of as many columns as are in your
query results.</p>

<p>If you specify a column a width of 0, then the column
width is automatically adjusted to be the maximum of three
numbers: 10, the width of the header, and the width of the
first row of data.  This makes the column width self-adjusting.
The default width setting for every column is this 
auto-adjusting 0 value.</p>

<p>The column labels that appear on the first two lines of output
can be turned on and off using the ".header" dot command.  In the
examples above, the column labels are on.  To turn them off you
could do this:</p>}

Code {
sqlite> (((.header off)))
sqlite> (((select * from tbl1;)))
hello         10    
goodbye       20    
sqlite>
}

puts {
<p>Another useful output mode is "insert".  In insert mode, the output
is formatted to look like SQL INSERT statements.  You can use insert
mode to generate text that can later be used to input data into a 
different database.</p>

<p>When specifying insert mode, you have to give an extra argument
which is the name of the table to be inserted into.  For example:</p>
}

Code {
sqlite> (((.mode insert new_table)))
sqlite> (((select * from tbl1;)))
INSERT INTO 'new_table' VALUES('hello',10);
INSERT INTO 'new_table' VALUES('goodbye',20);
sqlite>
}

puts {
<p>The last output mode is "html".  In this mode, sqlite writes
the results of the query as an XHTML table.  The beginning
&lt;TABLE&gt; and the ending &lt;/TABLE&gt; are not written, but
all of the intervening &lt;TR&gt;s, &lt;TH&gt;s, and &lt;TD&gt;s
are.  The html output mode is envisioned as being useful for
CGI.</p>
}
361
362
363
364
365
366
367

368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383

puts {
<p>The ".schema" command accomplishes the same thing as setting
list mode, then entering the following query:</p>

<blockquote><pre>
SELECT sql FROM sqlite_master

ORDER BY tbl_name, type DESC, name
</pre></blockquote>

<p>Or, if you give an argument to ".schema" because you only
want the schema for a single table, the query looks like this:</p>

<blockquote><pre>
SELECT sql FROM sqlite_master
WHERE tbl_name LIKE '%s'
ORDER BY type DESC, name
</pre></blockquote>

<p>The <b>%s</b> in the query above is replaced by the argument
to ".schema", of course.</p>

<h2>Converting An Entire Database To An ASCII Text File</h2>







>








|







381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404

puts {
<p>The ".schema" command accomplishes the same thing as setting
list mode, then entering the following query:</p>

<blockquote><pre>
SELECT sql FROM sqlite_master
WHERE type!='meta'
ORDER BY tbl_name, type DESC, name
</pre></blockquote>

<p>Or, if you give an argument to ".schema" because you only
want the schema for a single table, the query looks like this:</p>

<blockquote><pre>
SELECT sql FROM sqlite_master
WHERE tbl_name LIKE '%s' AND type!='meta'
ORDER BY type DESC, name
</pre></blockquote>

<p>The <b>%s</b> in the query above is replaced by the argument
to ".schema", of course.</p>

<h2>Converting An Entire Database To An ASCII Text File</h2>