SQLite

Check-in [7e0bacedf9]
Login

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

Overview
Comment:Documenting the TCL interface (CVS 149)
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 7e0bacedf928095b29e7166eacd2356e8169d6dd
User & Date: drh 2000-09-30 22:46:06.000
Context
2000-09-30
23:00
Version 1.0.8 (CVS 494) (check-in: 384909e50f user: drh tags: trunk)
22:46
Documenting the TCL interface (CVS 149) (check-in: 7e0bacedf9 user: drh tags: trunk)
2000-09-29
15:15
Fix a problem with the configure script (CVS 1703) (check-in: ed5f5404ad user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to Makefile.in.
︙︙
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


crosscompile.html:	$(TOP)/www/crosscompile.tcl
	tclsh $(TOP)/www/crosscompile.tcl >crosscompile.html

mingw.html:	$(TOP)/www/mingw.tcl
	tclsh $(TOP)/www/mingw.tcl >mingw.html





# Files to be published on the website.
#
PUBLISH = \
  sqlite.tar.gz \
  index.html \
  sqlite.html \
  changes.html \
  fileformat.html \
  lang.html \
  opcode.html \
  arch.html \
  arch.png \
  vdbe.html \
  c_interface.html \
  crosscompile.html \
  mingw.html


website:	$(PUBLISH)

publish:	$(PUBLISH)
	chmod 0644 $(PUBLISH)
	scp $(PUBLISH) hwaci@oak.he.net:public_html/sw/sqlite









>
>
>
















|
>








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


crosscompile.html:	$(TOP)/www/crosscompile.tcl
	tclsh $(TOP)/www/crosscompile.tcl >crosscompile.html

mingw.html:	$(TOP)/www/mingw.tcl
	tclsh $(TOP)/www/mingw.tcl >mingw.html

tclsqlite.html:	$(TOP)/www/tclsqlite.tcl
	tclsh $(TOP)/www/tclsqlite.tcl >tclsqlite.html


# Files to be published on the website.
#
PUBLISH = \
  sqlite.tar.gz \
  index.html \
  sqlite.html \
  changes.html \
  fileformat.html \
  lang.html \
  opcode.html \
  arch.html \
  arch.png \
  vdbe.html \
  c_interface.html \
  crosscompile.html \
  mingw.html \
  tclsqlite.html

website:	$(PUBLISH)

publish:	$(PUBLISH)
	chmod 0644 $(PUBLISH)
	scp $(PUBLISH) hwaci@oak.he.net:public_html/sw/sqlite
︙︙
Changes to VERSION. 
1

1.0.7

|

1

1.0.8
Changes to src/tclsqlite.c.
︙︙
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

** Author contact information:
**   drh@hwaci.com
**   http://www.hwaci.com/drh/
**
*************************************************************************
** A TCL Interface to SQLite
**
** $Id: tclsqlite.c,v 1.9 2000/09/21 13:01:37 drh Exp $
*/
#ifndef NO_TCL     /* Omit this whole file if TCL is unavailable */

#include "sqlite.h"
#include <tcl.h>
#include <stdlib.h>
#include <string.h>








|








19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

** Author contact information:
**   drh@hwaci.com
**   http://www.hwaci.com/drh/
**
*************************************************************************
** A TCL Interface to SQLite
**
** $Id: tclsqlite.c,v 1.10 2000/09/30 22:46:07 drh Exp $
*/
#ifndef NO_TCL     /* Omit this whole file if TCL is unavailable */

#include "sqlite.h"
#include <tcl.h>
#include <stdlib.h>
#include <string.h>
︙︙
64
65
66
67
68
69
70

71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97

  char ** azCol,         /* Data for each column */
  char ** azN            /* Name for each column */
){
  CallbackData *cbData = (CallbackData*)clientData;
  int i, rc;
  if( cbData->zArray[0] ){
    if( cbData->once ){

      for(i=0; i<nCol; i++){
        Tcl_SetVar2(cbData->interp, cbData->zArray, "*", azN[i],
           TCL_LIST_ELEMENT|TCL_APPEND_VALUE);
      }
    }
    for(i=0; i<nCol; i++){
      char *z = azCol[i];
      if( z==0 ) z = "";
      Tcl_SetVar2(cbData->interp, cbData->zArray, azN[i], z, 0);
    }
  }else{
    for(i=0; i<nCol; i++){
      char *z = azCol[i];
      if( z==0 ) z = "";
      Tcl_SetVar(cbData->interp, azN[i], z, 0);
    }
  }
  cbData->once = 0;
  rc = Tcl_EvalObj(cbData->interp, cbData->pCode);
  return rc;
}

/*
** This is an alternative callback for database queries.  Instead
** of invoking a TCL script to handle the result, this callback just
** appends each column of the result to a list.  After the query
** is complete, the list is returned.








>



















|








64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98

  char ** azCol,         /* Data for each column */
  char ** azN            /* Name for each column */
){
  CallbackData *cbData = (CallbackData*)clientData;
  int i, rc;
  if( cbData->zArray[0] ){
    if( cbData->once ){
      Tcl_SetVar2(cbData->interp, cbData->zArray, "*", "", 0);
      for(i=0; i<nCol; i++){
        Tcl_SetVar2(cbData->interp, cbData->zArray, "*", azN[i],
           TCL_LIST_ELEMENT|TCL_APPEND_VALUE);
      }
    }
    for(i=0; i<nCol; i++){
      char *z = azCol[i];
      if( z==0 ) z = "";
      Tcl_SetVar2(cbData->interp, cbData->zArray, azN[i], z, 0);
    }
  }else{
    for(i=0; i<nCol; i++){
      char *z = azCol[i];
      if( z==0 ) z = "";
      Tcl_SetVar(cbData->interp, azN[i], z, 0);
    }
  }
  cbData->once = 0;
  rc = Tcl_EvalObj(cbData->interp, cbData->pCode);
  return rc!=TCL_OK && rc!=TCL_CONTINUE;
}

/*
** This is an alternative callback for database queries.  Instead
** of invoking a TCL script to handle the result, this callback just
** appends each column of the result to a list.  After the query
** is complete, the list is returned.
︙︙
Changes to www/changes.tcl.
︙︙
12
13
14
15
16
17
18




19
20
21
22
23
24
25

}


proc chng {date desc} {
  puts "<DT><B>$date</B></DT>"
  puts "<DD><P><UL>$desc</UL></P></DD>"
}





chng {2000 Sep 29 (Not Released)} {
<li>Added the <b>sqlite_get_table()</b> API</li>
<li>Updated the documtation for due to the above change.</li>
<li>Modified the <b>sqlite</b> shell to make use of the new
    sqlite_get_table() API in order to print a list of tables
    in multiple columns, similar to the way "ls" prints filenames.</li>








>
>
>
>








12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

}


proc chng {date desc} {
  puts "<DT><B>$date</B></DT>"
  puts "<DD><P><UL>$desc</UL></P></DD>"
}

chng {2000 Sep 30 (1.0.8)} {
<li>Begin writing documentation on the TCL interface.</li>
}

chng {2000 Sep 29 (Not Released)} {
<li>Added the <b>sqlite_get_table()</b> API</li>
<li>Updated the documtation for due to the above change.</li>
<li>Modified the <b>sqlite</b> shell to make use of the new
    sqlite_get_table() API in order to print a list of tables
    in multiple columns, similar to the way "ls" prints filenames.</li>
︙︙
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.28 2000/08/18 09:58:52 drh Exp $}

puts {<html>
<head><title>SQLite: An SQL Database Library Built Atop GDBM</title></head>
<body bgcolor=white>
<h1 align=center>SQLite: An SQL Database Library 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.29 2000/09/30 22:46:07 drh Exp $}

puts {<html>
<head><title>SQLite: An SQL Database Library Built Atop GDBM</title></head>
<body bgcolor=white>
<h1 align=center>SQLite: An SQL Database Library Built Atop
<a href="http://www.gnu.org/software/gdbm/gdbm.html">GDBM</a></h1>
<p align=center>}
︙︙
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50

<li>A database is just a directory of GDBM files.</li>
<li>Unlimited length records.</li>
<li>Import and export data from 
<a href="http://www.postgresql.org/">PostgreSQL</a>.</li>
<li>Very simple 
<a href="c_interface.html">C/C++ interface</a> requires the use of only
three functions and one opaque structure.</li>
<li>A <a href="http://dev.scriptics.com/">Tcl</a> interface is
included.</li>
<li>Command-line access program <a href="sqlite.html">sqlite</a> uses
the <a href="http://www.google.com/search?q=gnu+readline+library">GNU
Readline library</a></li>
<li>A Tcl-based test suite provides near 100% code coverage</li>
<li>7500+ lines of C code.  No external dependencies other than GDBM.</li>
<li>Built and tested under Linux, HPUX, and WinNT.</li>








|








36
37
38
39
40
41
42
43
44
45
46
47
48
49
50

<li>A database is just a directory of GDBM files.</li>
<li>Unlimited length records.</li>
<li>Import and export data from 
<a href="http://www.postgresql.org/">PostgreSQL</a>.</li>
<li>Very simple 
<a href="c_interface.html">C/C++ interface</a> requires the use of only
three functions and one opaque structure.</li>
<li>A <a href="tclsqlite.html">Tcl</a> interface is
included.</li>
<li>Command-line access program <a href="sqlite.html">sqlite</a> uses
the <a href="http://www.google.com/search?q=gnu+readline+library">GNU
Readline library</a></li>
<li>A Tcl-based test suite provides near 100% code coverage</li>
<li>7500+ lines of C code.  No external dependencies other than GDBM.</li>
<li>Built and tested under Linux, HPUX, and WinNT.</li>
︙︙
96
97
98
99
100
101
102

103
104
105
106
107
108
109

<p>The following documentation is currently available:</p>

<p><ul>
<li>Information on the <a href="sqlite.html">sqlite</a>
    command-line utility.</li>
<li>The <a href="lang.html">SQL Language</a> subset understood by SQLite.</li>
<li>The <a href="c_interface.html">C/C++ Interface</a>.</li>

<li>The <a href="fileformat.html">file format</a> used by SQLite databases.</li>
<li>The <a href="arch.html">Architecture of the SQLite Library</a> describes
    how the library is put together.</li>
<li>A description of the <a href="opcode.html">virtual machine</a> that
    SQLite uses to access the database.</li>
<li>Instructions for building 
    <a href="crosscompile.html">SQLite for Win98/NT</a> using the








>








96
97
98
99
100
101
102
103
104
105
106
107
108
109
110

<p>The following documentation is currently available:</p>

<p><ul>
<li>Information on the <a href="sqlite.html">sqlite</a>
    command-line utility.</li>
<li>The <a href="lang.html">SQL Language</a> subset understood by SQLite.</li>
<li>The <a href="c_interface.html">C/C++ Interface</a>.</li>
<li>The <a href="tclsqlite.html">Tcl Interface</a>.</li>
<li>The <a href="fileformat.html">file format</a> used by SQLite databases.</li>
<li>The <a href="arch.html">Architecture of the SQLite Library</a> describes
    how the library is put together.</li>
<li>A description of the <a href="opcode.html">virtual machine</a> that
    SQLite uses to access the database.</li>
<li>Instructions for building 
    <a href="crosscompile.html">SQLite for Win98/NT</a> using the
︙︙
Added www/tclsqlite.tcl. 






























































































































































































































































































































































































































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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
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
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
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
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
193
194
195
196
197
198
199
200
201
202
203
204
205
206

#
# Run this Tcl script to generate the tclsqlite.html file.
#
set rcsid {$Id: tclsqlite.tcl,v 1.1 2000/09/30 22:46:07 drh Exp $}

puts {<html>
<head>
  <title>The Tcl interface to the SQLite library</title>
</head>
<body bgcolor=white>
<h1 align=center>
The Tcl interface to the SQLite library
</h1>}
puts "<p align=center>
(This page was last modified on [lrange $rcsid 3 4] GMT)
</p>"

puts {
<p>The SQLite library is designed to be very easy to use from
a Tcl or Tcl/Tk script.  This document gives an overview of the Tcl
programming interface.</p>

<h2>The API</h2>

<p>The interface to the SQLite library consists of single
tcl command named <b>sqlite</b>.  Because there is only this
one interface command, the interface is not placed in a separate
namespace.</p>

<p>The <b>sqlite</b> command is used as follows:</p>

<blockquote>
<b>sqlite</b>&nbsp;&nbsp;<i>dbcmd&nbsp;&nbsp;database-directory-name</i>
</blockquote>

<p>
The <b>sqlite</b> command opens the SQLite database located in the
directory named by the second argument.  If the database or directory
does not exist, it is created.  The <b>sqlite</b> command 
also creates a new Tcl
command to control the database.  The name of the new Tcl command
is given by the first argument.  This approach is similar to the
way widgets are created in Tk.
</p>

<p>
Once an SQLite database is open, it can be controlled using 
methods of the <i>dbcmd</i>.  There are currently 5 methods
defined:</p>

<p>
<ul>
<li> busy
<li> close
<li> complete
<li> eval
<li> timeout
</ul>
</p>

<p>We will explain all of these methods, though not in that order.
We will be begin with the "close" method.</p>

<h2>The "close" method</h2>

<p>
As its name suggests, the "close" method to an SQLite database just
closes the database.  This has the side-effect of deleting the
<i>dbcmd</i> Tcl command.  Here is an example of opening and then
immediately closing a database:
</p>

<blockquote>
<b>sqlite db1 ./testdb<br>
db1 close</b>
</blockquote>

<p>
If you delete the <i>dbcmd</i> directly, that has the same effect
as invoking the "close" method.  So the following code is equivalent
to the previous:</p>

<blockquote>
<b>sqlite db1 ./testdb<br>
rename db1 {}</b>
</blockquote>

<h2>The "eval" method</h2>

<p>
The most useful <i>dbcmd</i> method is "eval".  The eval method is used
to execute SQL on the database.  The syntax of the eval method looks
like this:</p>

<blockquote>
<i>dbcmd</i>&nbsp;&nbsp;<b>eval</b>&nbsp;&nbsp;<i>sql</i>
&nbsp;&nbsp;?<i>array-name&nbsp;&nbsp;script</i>?
</blockquote>

<p>
The job of the eval method is to execute the SQL statement or statements
given in the second argument.  For example, to create a new table in
a database, you can do this:</p>

<blockquote>
<b>sqlite db1 ./testdb<br>
db1 eval {CREATE TABLE t1(a int, b text)}</b>
</blockquote>

<p>The above code creates a new table named <b>t1</b> with columns
<b>a</b> and <b>b</b>.  What could be simplier?</p>

<p>Query results are returned as a list of column values.  If a
query requests 2 columns and there are 3 rows matching the query,
then the returned list will contain 6 elements.  For example:</p>

<blockquote>
<b>db1 eval {INSERT INTO t1 VALUES(1,'hello')}<br>
db1 eval {INSERT INTO t1 VALUES(2,'goodbye')}<br>
db1 eval {INSERT INTO t1 VALUES(3,'howdy!')}<br>
set x [db1 eval {SELECT * FROM t1 ORDER BY a}]</b>
</blockquote>

<p>The variable <b>$x</b> is set by the above code to</p>

<blockquote>
<b>1 hello 2 goodbye 3 howdy!</b>
</blockquote>

<p>You can also process the results of a query one row at a time
by specifying the name of an array variable and a script following
the SQL code.  For each row of the query result, the value of each
column will be inserted into the array variable and the script will
be executed.  For instance:</p>

<blockquote>
<b>db1 eval {SELECT * FROM t1 ORDER BY a} values {<br>
&nbsp;&nbsp;&nbsp;&nbsp;parray values<br>
&nbsp;&nbsp;&nbsp;&nbsp;puts ""<br>
}</b>
</blockquote>

<p>This last code will give the following output:</p>

<blockquote><b>
values(*) = a b<br>
values(a) = 1<br>
values(b) = hello<p>

values(*) = a b<br>
values(a) = 2<br>
values(b) = goodbye<p>

values(*) = a b<br>
values(a) = 3<br>
values(b) = howdy!</b>
</blockquote>

<p>
For each column in a row of the result, the name of that column
is used as an index in to array.  The value of the column is stored
in the corresponding array entry.  The special array index * is
used to store a list of column names in the order that they appear.
</p>

<p>
If the array variable name is the empty string, then the value of
each column is stored in a variable with the same name as the column
itself.  For example:
</p>

<blockquote>
<b>db1 eval {SELECT * FROM t1 ORDER BY a} {} {<br>
&nbsp;&nbsp;&nbsp;&nbsp;puts "a=$a b=$b"<br>
}</b>
</blockquote>

<p>
From this we get the following output
</p>

<blockquote><b>
a=1 b=hello<br>
a=2 b=goodbye<br>
a=3 b=howdy!</b>
</blockquote>

<h2>The "complete" method</h2>
<i>TBD</i>

<h2>The "timeout" method</h2>
<i>TBD</i>

<h2>The "busy" method</h2>
<i>TBD</i>


}

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>}