Documentation Source Text

Check-in [16b1f03012]
Login

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

Overview
Comment:An attempt to get automatic hyperlinking working on all web pages. The website builds, but there are still many problems.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 16b1f030128ae512b725ceeb7d26c36c284bb742
User & Date: drh 2007-12-20 03:29:15.000
Context
2007-12-20
23:01
Additional work toward denser hyperlinking in the documentation. (check-in: aa8a072b1b user: drh tags: trunk)
03:29
An attempt to get automatic hyperlinking working on all web pages. The website builds, but there are still many problems. (check-in: 16b1f03012 user: drh tags: trunk)
2007-12-18
18:09
SQLite does not have an ! operator. CVSTrac ticket #2849. (check-in: 253e0c9a09 user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to main.mk.
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
	sed 's/[^0-9]/ /g' | \
	$(NAWK) '{printf "%d%03d%03d",$$1,$$2,$$3}'`/ \
		$(SRC)/src/sqlite.h.in >sqlite3.h

wrap.tcl:	$(DOC)/wrap.tcl
	cp $(DOC)/wrap.tcl .

lang.html: $(DOC)/lang.tcl
	tclsh $(DOC)/lang.tcl doc >lang.html

opcode.html:	$(DOC)/opcode.tcl $(SRC)/src/vdbe.c
	tclsh $(DOC)/opcode.tcl $(SRC)/src/vdbe.c >opcode.html

capi3ref.html:	$(DOC)/mkapidoc.tcl sqlite3.h
	tclsh $(DOC)/mkapidoc.tcl <sqlite3.h >capi3ref.html

docdir:
	mkdir -p doc 

doc:	sqlite3.h docdir always
	rm -rf doc/images
	cp -r $(DOC)/images doc
	cp $(SRC)/art/*.gif doc/images







<
<
<
<
<
<
<
<
<







21
22
23
24
25
26
27









28
29
30
31
32
33
34
	sed 's/[^0-9]/ /g' | \
	$(NAWK) '{printf "%d%03d%03d",$$1,$$2,$$3}'`/ \
		$(SRC)/src/sqlite.h.in >sqlite3.h

wrap.tcl:	$(DOC)/wrap.tcl
	cp $(DOC)/wrap.tcl .










docdir:
	mkdir -p doc 

doc:	sqlite3.h docdir always
	rm -rf doc/images
	cp -r $(DOC)/images doc
	cp $(SRC)/art/*.gif doc/images
Changes to pages/34to35.in.
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
<title>SQLite Changes From Version 3.4.2 To 3.5.0</title>
<tcl>
proc CODE {text} {
  puts "<blockquote><pre>"
  puts $text
  puts "</pre></blockquote>"
}
proc PARAGRAPH {text} {
  # regsub -all "/(\[a-zA-Z0-9\]+)/" $text {<i>\1</i>} t2
  #regsub -all "\\*(\[^\n*\]+)\\*" $text {<tt><b><big>\1</big></b></tt>} t3
  regsub -all {\[([^]\n]+)\]} $text {[resolve_link \1]} t3
  puts "<p>[subst -novar -noback $t3]</p>\n"
}
proc resolve_link {args} {
  set a2 [split $args |]
  set id [string trim [lindex $a2 0]]
  if {[lindex $a2 1]==""} {
    set display [string trim [lindex $a2 0]]
  } else {
    set display [string trim [lrange $a2 1 end]]
  }
  regsub -all {[^a-zA-Z0-9_]} $id {} id
  return "<a href=\"capi3ref.html#$id\">$display</a>"
}
set level(0) 0
set level(1) 0
proc HEADING {n name {tag {}}} {
  if {$tag!=""} {
    puts "<a name=\"$tag\"></a>"
  }
  global level
  incr level($n)
  for {set i [expr {$n+1}]} {$i<10} {incr i} {
    set level($i) 0
  }
  if {$n==0} {
    set num {}
  } elseif {$n==1} {
    set num $level(1).0
  } else {
    set num $level(1)
    for {set i 2} {$i<=$n} {incr i} {
      append num .$level($i)
    }
  }
  incr n 1
  puts "<h$n>$num $name</h$n>"
}

HEADING 0 {Moving From SQLite 3.4.2 to 3.5.0}

PARAGRAPH {
  SQLite version 3.5.0 introduces a new OS interface layer that
  is incompatible with all prior versions of SQLite.  In addition,



|
|
|


<
<
<
<
<
|
<
<
<
<
<
<
<
<
<





|

















|







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
<title>SQLite Changes From Version 3.4.2 To 3.5.0</title>
<tcl>
proc CODE {text} {
  hd_puts "<blockquote><pre>"
  hd_puts $text
  hd_puts "</pre></blockquote>"
}
proc PARAGRAPH {text} {





  hd_resolve <p>$text</p>\n









}
set level(0) 0
set level(1) 0
proc HEADING {n name {tag {}}} {
  if {$tag!=""} {
    hd_fragment $tag
  }
  global level
  incr level($n)
  for {set i [expr {$n+1}]} {$i<10} {incr i} {
    set level($i) 0
  }
  if {$n==0} {
    set num {}
  } elseif {$n==1} {
    set num $level(1).0
  } else {
    set num $level(1)
    for {set i 2} {$i<=$n} {incr i} {
      append num .$level($i)
    }
  }
  incr n 1
  hd_puts "<h$n>$num $name</h$n>"
}

HEADING 0 {Moving From SQLite 3.4.2 to 3.5.0}

PARAGRAPH {
  SQLite version 3.5.0 introduces a new OS interface layer that
  is incompatible with all prior versions of SQLite.  In addition,
Changes to pages/c_interface.in.
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
The second argument is the number of columns in the query result.
The third argument is an array of pointers to strings where each string
is a single column of the result for that record.  Note that the
callback function reports a NULL value in the database as a NULL pointer,
which is very different from an empty string.  If the i-th parameter
is an empty string, we will get:</p>
<blockquote><pre>
argv[i][0] == 0
</pre></blockquote>
<p>But if the i-th parameter is NULL we will get:</p>
<blockquote><pre>
argv[i] == 0
</pre></blockquote>

<p>The names of the columns are contained in first <i>argc</i>
entries of the fourth argument.
If the <a href="pragma.html#pragma_show_datatypes">SHOW_DATATYPES</a> pragma
is on (it is off by default) then
the second <i>argc</i> entries in the 4th argument are the datatypes







|



|







118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
The second argument is the number of columns in the query result.
The third argument is an array of pointers to strings where each string
is a single column of the result for that record.  Note that the
callback function reports a NULL value in the database as a NULL pointer,
which is very different from an empty string.  If the i-th parameter
is an empty string, we will get:</p>
<blockquote><pre>
argv&#91;i]&#91;0] == 0
</pre></blockquote>
<p>But if the i-th parameter is NULL we will get:</p>
<blockquote><pre>
argv&#91;i] == 0
</pre></blockquote>

<p>The names of the columns are contained in first <i>argc</i>
entries of the fourth argument.
If the <a href="pragma.html#pragma_show_datatypes">SHOW_DATATYPES</a> pragma
is on (it is off by default) then
the second <i>argc</i> entries in the 4th argument are the datatypes
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585

int sqlite_complete(const char *sql);

void sqlite_busy_handler(sqlite*, int (*)(void*,const char*,int), void*);

void sqlite_busy_timeout(sqlite*, int ms);

const char sqlite_version[];

const char sqlite_encoding[];

int sqlite_exec_printf(
  sqlite*,
  char *sql,
  int (*)(void*,int,char**,char**),
  void*,
  char **errmsg,







|

|







569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585

int sqlite_complete(const char *sql);

void sqlite_busy_handler(sqlite*, int (*)(void*,const char*,int), void*);

void sqlite_busy_timeout(sqlite*, int ms);

const char sqlite_version&#91;];

const char sqlite_encoding&#91;];

int sqlite_exec_printf(
  sqlite*,
  char *sql,
  int (*)(void*,int,char**,char**),
  void*,
  char **errmsg,
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
for every employee whose login begins with the letter "d".  If this
query is submitted to <b>sqlite_get_table</b> the result might
look like this:</p>

<blockquote>
nrow = 2<br>
ncolumn = 3<br>
result[0] = "employee_name"<br>
result[1] = "login"<br>
result[2] = "host"<br>
result[3] = "dummy"<br>
result[4] = "No such user"<br>
result[5] = 0<br>
result[6] = "D. Richard Hipp"<br>
result[7] = "drh"<br>
result[8] = "zadok"
</blockquote>

<p>Notice that the "host" value for the "dummy" record is NULL so
the result[] array contains a NULL pointer at that slot.</p>

<p>If the result set of a query is empty, then by default
<b>sqlite_get_table</b> will set nrow to 0 and leave its
result parameter is set to NULL.  But if the EMPTY_RESULT_CALLBACKS
pragma is ON then the result parameter is initialized to the names
of the columns only.  For example, consider this query which has
an empty result set:</p>







|
|
|
|
|
|
|
|
|



|







697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
for every employee whose login begins with the letter "d".  If this
query is submitted to <b>sqlite_get_table</b> the result might
look like this:</p>

<blockquote>
nrow = 2<br>
ncolumn = 3<br>
result&#91;0] = "employee_name"<br>
result&#91;1] = "login"<br>
result&#91;2] = "host"<br>
result&#91;3] = "dummy"<br>
result&#91;4] = "No such user"<br>
result&#91;5] = 0<br>
result&#91;6] = "D. Richard Hipp"<br>
result&#91;7] = "drh"<br>
result&#91;8] = "zadok"
</blockquote>

<p>Notice that the "host" value for the "dummy" record is NULL so
the result&#91;] array contains a NULL pointer at that slot.</p>

<p>If the result set of a query is empty, then by default
<b>sqlite_get_table</b> will set nrow to 0 and leave its
result parameter is set to NULL.  But if the EMPTY_RESULT_CALLBACKS
pragma is ON then the result parameter is initialized to the names
of the columns only.  For example, consider this query which has
an empty result set:</p>
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
But if the EMPTY_RESULT_CALLBACKS pragma is ON, then the following
is returned:
</p>

<blockquote>
nrow = 0<br>
ncolumn = 3<br>
result[0] = "employee_name"<br>
result[1] = "login"<br>
result[2] = "host"<br>
</blockquote>

<p>Memory to hold the information returned by <b>sqlite_get_table</b>
is obtained from malloc().  But the calling function should not try
to free this information directly.  Instead, pass the complete table
to <b>sqlite_free_table</b> when the table is no longer needed.
It is safe to call <b>sqlite_free_table</b> with a NULL pointer such







|
|
|







740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
But if the EMPTY_RESULT_CALLBACKS pragma is ON, then the following
is returned:
</p>

<blockquote>
nrow = 0<br>
ncolumn = 3<br>
result&#91;0] = "employee_name"<br>
result&#91;1] = "login"<br>
result&#91;2] = "host"<br>
</blockquote>

<p>Memory to hold the information returned by <b>sqlite_get_table</b>
is obtained from malloc().  But the calling function should not try
to free this information directly.  Instead, pass the complete table
to <b>sqlite_free_table</b> when the table is no longer needed.
It is safe to call <b>sqlite_free_table</b> with a NULL pointer such
Changes to pages/capi3.in.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
<title>C/C++ Interface For SQLite Version 3</title>

<tcl>
proc AddHyperlinks {txt} {
  regsub -all {([^:alnum:>])(sqlite3_\w+)(\([^\)]*\))} $txt \
      {\1<a href="capi3ref.html#\2">\2</a>\3} t2
  puts $t2
}

AddHyperlinks {
<h2>C/C++ Interface For SQLite Version 3</h2>

<h3>1.0 Overview</h3>

<p>
SQLite version 3.0 is a new version of SQLite, derived from
the SQLite 2.8.13 code base, but with an incompatible file format


<
<
<
<
<
<
<
<







1
2








3
4
5
6
7
8
9
<title>C/C++ Interface For SQLite Version 3</title>









<h2>C/C++ Interface For SQLite Version 3</h2>

<h3>1.0 Overview</h3>

<p>
SQLite version 3.0 is a new version of SQLite, derived from
the SQLite 2.8.13 code base, but with an incompatible file format
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
</p>

<h3>2.0 C/C++ Interface</h3>

<p>
The API for SQLite 3.0 includes 83 separate functions in addition
to several data structures and #defines.  (A complete
<a href="capi3ref.html">API reference</a> is provided as a separate document.)

Fortunately, the interface is not nearly as complex as its size implies.
Simple programs can still make do with only 3 functions:
<a href="capi3ref.html#sqlite3_open">sqlite3_open()</a>,
<a href="capi3ref.html#sqlite3_exec">sqlite3_exec()</a>, and
<a href="capi3ref.html#sqlite3_close">sqlite3_close()</a>.
More control over the execution of the database engine is provided
using
<a href="capi3ref.html#sqlite3_prepare">sqlite3_prepare()</a>
to compile an SQLite statement into byte code and
<a href="capi3ref.html#sqlite3_prepare">sqlite3_step()</a>
to execute that bytecode.
A family of routines with names beginning with 
<a href="capi3ref.html#sqlite3_column_blob">sqlite3_column_</a>
is used to extract information about the result set of a query.
Many interface functions come in pairs, with both a UTF-8 and
UTF-16 version.  And there is a collection of routines
used to implement user-defined SQL functions and user-defined
text collating sequences.
</p>








|
>


|
<
<

<
|

<
|

|







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
</p>

<h3>2.0 C/C++ Interface</h3>

<p>
The API for SQLite 3.0 includes 83 separate functions in addition
to several data structures and #defines.  (A complete
<a href="c3ref/intro.html">API reference</a> is provided as a separate 
document.)
Fortunately, the interface is not nearly as complex as its size implies.
Simple programs can still make do with only 3 functions:
[sqlite3_open()], [sqlite3_exec()], and [sqlite3_close()].


More control over the execution of the database engine is provided

using [sqlite3_prepare_v2()]
to compile an SQLite statement into byte code and

[sqlite3_step()] to execute that bytecode.
A family of routines with names beginning with 
[sqlite3_column_blob | sqlite3_column_]
is used to extract information about the result set of a query.
Many interface functions come in pairs, with both a UTF-8 and
UTF-16 version.  And there is a collection of routines
used to implement user-defined SQL functions and user-defined
text collating sequences.
</p>

154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172

<blockquote><pre>
   typedef int (*sqlite_callback)(void*,int,char**, char**);
   int sqlite3_exec(sqlite3*, const char *sql, sqlite_callback, void*, char**);
</pre></blockquote>

<p>
The sqlite3_exec function works much as it did in SQLite version 2.
Zero or more SQL statements specified in the second parameter are compiled
and executed.  Query results are returned to a callback routine.
See the <a href="capi3ref.html#sqlite3_exec">API reference</a> for additional
information.
</p>

<p>
In SQLite version 3, the sqlite3_exec routine is just a wrapper around
calls to the prepared statement interface.
</p>








|


<
<







143
144
145
146
147
148
149
150
151
152


153
154
155
156
157
158
159

<blockquote><pre>
   typedef int (*sqlite_callback)(void*,int,char**, char**);
   int sqlite3_exec(sqlite3*, const char *sql, sqlite_callback, void*, char**);
</pre></blockquote>

<p>
The [sqlite3_exec()] function works much as it did in SQLite version 2.
Zero or more SQL statements specified in the second parameter are compiled
and executed.  Query results are returned to a callback routine.


</p>

<p>
In SQLite version 3, the sqlite3_exec routine is just a wrapper around
calls to the prepared statement interface.
</p>

199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
</p>

<p>
The SQL statement may contain tokens of the form "?" or "?nnn" or ":aaa"
where "nnn" is an integer and "aaa" is an identifier.
Such tokens represent unspecified literal values (or "wildcards")
to be filled in later by the 
<a href="capi3ref.html#sqlite3_bind_blob">sqlite3_bind</a> interface.
Each wildcard has an associated number which is its sequence in the
statement or the "nnn" in the case of a "?nnn" form. 
It is allowed for the same wildcard
to occur more than once in the same SQL statement, in which case
all instance of that wildcard will be filled in with the same value.
Unbound wildcards have a value of NULL.
</p>







|







186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
</p>

<p>
The SQL statement may contain tokens of the form "?" or "?nnn" or ":aaa"
where "nnn" is an integer and "aaa" is an identifier.
Such tokens represent unspecified literal values (or "wildcards")
to be filled in later by the 
[sqlite3_bind_blob|sqlite3_bind] interface.
Each wildcard has an associated number which is its sequence in the
statement or the "nnn" in the case of a "?nnn" form. 
It is allowed for the same wildcard
to occur more than once in the same SQL statement, in which case
all instance of that wildcard will be filled in with the same value.
Unbound wildcards have a value of NULL.
</p>
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
   const void *sqlite3_column_name16(sqlite3_stmt*, int iCol);
   const unsigned char *sqlite3_column_text(sqlite3_stmt*, int iCol);
   const void *sqlite3_column_text16(sqlite3_stmt*, int iCol);
   int sqlite3_column_type(sqlite3_stmt*, int iCol);
</pre></blockquote>

<p>
The 
<a href="capi3ref.html#sqlite3_column_count">sqlite3_column_count()</a>
function returns the number of columns in
the results set.  sqlite3_column_count() can be called at any time after
sqlite3_prepare().  
<a href="capi3ref.html#sqlite3_data_count">sqlite3_data_count()</a>
works similarly to
sqlite3_column_count() except that it only works following sqlite3_step().
If the previous call to sqlite3_step() returned SQLITE_DONE or an error code,
then sqlite3_data_count() will return 0 whereas sqlite3_column_count() will
continue to return the number of columns in the result set.
</p>

<p>Returned data is examined using the other sqlite3_column_***() functions, 

all of which take a column number as their second parameter. Columns are
zero-indexed from left to right. Note that this is different to parameters,
which are indexed starting at one.
</p>

<p>
The sqlite3_column_type() function returns the
datatype for the value in the Nth column.  The return value is one
of these:
</p>

<blockquote><pre>
   #define SQLITE_INTEGER  1
   #define SQLITE_FLOAT    2







<
|


<
<
|
|
|
|



|
>






|







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
   const void *sqlite3_column_name16(sqlite3_stmt*, int iCol);
   const unsigned char *sqlite3_column_text(sqlite3_stmt*, int iCol);
   const void *sqlite3_column_text16(sqlite3_stmt*, int iCol);
   int sqlite3_column_type(sqlite3_stmt*, int iCol);
</pre></blockquote>

<p>

The [sqlite3_column_count()]
function returns the number of columns in
the results set.  sqlite3_column_count() can be called at any time after


[sqlite3_prepare_v2()].  [sqlite3_data_count()] works similarly to
[sqlite3_column_count()] except that it only works following [sqlite3_step()].
If the previous call to [sqlite3_step()] returned SQLITE_DONE or an error code,
then [sqlite3_data_count()] will return 0 whereas [sqlite3_column_count()] will
continue to return the number of columns in the result set.
</p>

<p>Returned data is examined using the other 
[sqlite3_column_blob|sqlite3_column_***()] functions, 
all of which take a column number as their second parameter. Columns are
zero-indexed from left to right. Note that this is different to parameters,
which are indexed starting at one.
</p>

<p>
The [sqlite3_column_type()] function returns the
datatype for the value in the Nth column.  The return value is one
of these:
</p>

<blockquote><pre>
   #define SQLITE_INTEGER  1
   #define SQLITE_FLOAT    2
507
508
509
510
511
512
513
514
515
The sqlite3_collation_needed() routine registers a callback which the
database engine will invoke if it encounters an unknown collating sequence.
The callback can lookup an appropriate comparison function and invoke
sqlite_3_create_collation() as needed.  The fourth parameter to the callback
is the name of the collating sequence in UTF-8.  For sqlite3_collation_need16()
the callback sends the collating sequence name in UTF-16 host byte order.
</p>
}
</tcl>







<
<
492
493
494
495
496
497
498


The sqlite3_collation_needed() routine registers a callback which the
database engine will invoke if it encounters an unknown collating sequence.
The callback can lookup an appropriate comparison function and invoke
sqlite_3_create_collation() as needed.  The fourth parameter to the callback
is the name of the collating sequence in UTF-8.  For sqlite3_collation_need16()
the callback sends the collating sequence name in UTF-16 host byte order.
</p>


Changes to pages/capi3ref.in.
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
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223

224
225
226
227
228
229
230
# multi-file version.
#
# Constants begin with SQLITE_.  The names are converted
# to lower case and prefixed with "c_".  If we did not
# do this, then the names "SQLITE_BLOB" and "sqlite3_blob"
# would collide.
#
proc convert_tag_name {oldname} {
  set oldname [string tolower $oldname]
  regsub {^sqlite_} $oldname {c_} oldname
  regsub {^sqlite3_} $oldname {} name
  return $name.html
}

# Compute a mapping from keywords to filenames.
#
unset -nocomplain keyword_to_file
foreach c [lsort $content] {
  foreach {key title type keywords body code} $c break
  set file [convert_tag_name [lindex $keywords 0]]
  foreach k $keywords {
    set keyword_to_file($k) $file
  }
}
#foreach k [lsort [array names keyword_to_file]] {
#  real_puts [format {%-30s %s} $k $keyword_to_file($k)]
#}

# Output HTML that displays the given list in N columns
#
proc output_list {N lx multi} {
  global keyword_to_file
  puts {<table width="100%" cellpadding="5"><tr>}
  set len [llength $lx]
  set n [expr {($len + $N - 1)/$N}]
  for {set i 0} {$i<$N} {incr i} {
    set start [expr {$i*$n}]
    set end [expr {($i+1)*$n}]
    puts {<td valign="top"><ul>}
    for {set j $start} {$j<$end} {incr j} {
      set entry [lindex $lx $j]
      if {$entry!=""} {
        foreach {link label} $entry break
        if {$multi} {
          set link $keyword_to_file($link)
        } else {
          set link "#$link"
        }
        puts "<li><a href=\"$link\">$label</a></li>"
      }
    }
    puts {</ul></td>}
  }
  puts {</tr></table>}
}

# Open a separate output file for a single interface
#
proc c3ref_open_file {filename title} {
  global DEST OUT save_OUT
  file mkdir $DEST/c3ref
  set save_OUT $OUT
  set OUT [open $DEST/c3ref/$filename w]
  PutsHeader "SQLite C Interface: $title" ../
  puts {<a href="intro.html"><h2>SQLite C Interface</h2></a>}
  puts "<h3>$title</h3>"
}
proc c3ref_close_file {} {
  global HOMEDIR OUT save_OUT
  PutsFooter $HOMEDIR/sqlite3.h
  close $OUT
  set OUT $save_OUT
}

c3ref_open_file intro.html Introduction

</tcl>

<p>These pages defined the C-language interface to SQLite.
These pages are intended as a reference to what SQLite
is suppose to do.  This is not a tutorial.  These
pages are designed to be precise, not easy to read.</p>








|






<
<
<
<
<
<
<
<
<
<
<
<
<
<
|

|
<
|





|




<
<
<
|
|
<
|
<
|

|


<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
|
|
>







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
# multi-file version.
#
# Constants begin with SQLITE_.  The names are converted
# to lower case and prefixed with "c_".  If we did not
# do this, then the names "SQLITE_BLOB" and "sqlite3_blob"
# would collide.
#
proc convert_keyword_to_filename {oldname} {
  set oldname [string tolower $oldname]
  regsub {^sqlite_} $oldname {c_} oldname
  regsub {^sqlite3_} $oldname {} name
  return $name.html
}















# Output HTML that displays the list $lx in $N columns
#
proc output_list {N lx} {

  hd_puts {<table width="100%" cellpadding="5"><tr>}
  set len [llength $lx]
  set n [expr {($len + $N - 1)/$N}]
  for {set i 0} {$i<$N} {incr i} {
    set start [expr {$i*$n}]
    set end [expr {($i+1)*$n}]
    hd_puts {<td valign="top"><ul>}
    for {set j $start} {$j<$end} {incr j} {
      set entry [lindex $lx $j]
      if {$entry!=""} {
        foreach {link label} $entry break



        hd_resolve "<li>\[$link|$label\]</li>"
      }

    }

    hd_puts {</ul></td>}
  }
  hd_puts {</tr></table>}
}


















hd_open_aux c3ref/intro.html
hd_header Introduction
hd_enable_main 0
</tcl>

<p>These pages defined the C-language interface to SQLite.
These pages are intended as a reference to what SQLite
is suppose to do.  This is not a tutorial.  These
pages are designed to be precise, not easy to read.</p>

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





326
327
328
329
330
331

332
333
334

335

336
337
338
339
340
341
342
343
344
345
346
347
348
349





350
351
352
353
354
355

356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402

403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428

429
430
431
432

433

434
435
<p>The interface is broken down into three catagories:</p>

<ol>
<li><p><a href="objlist.html"><b>List Of Objects.</b></a>
    All abstract objects and datatypes used by the
    SQLite library.  There are a handful of objects, but
    only three which most users need to be aware of:
    A database connection object
    <a href="sqlite3.html">sqlite3</a>, a prepared statement
    object <a href="stmt.html">sqlite3_stmt</a>, and the 64-bit integer
    type <a href="int64.html">sqlite3_int64</a>.</p></li>

<li><p><a href="constlist.html"><b>List Of Constants.</b></a>
    Numeric constants just by SQLite and represented by
    #defines in the sqlite3.h header file.  These constants
    are things such as numeric return parameters from
    various interfaces (ex: 
    <a href="ABORT.html">SQLITE_OK</a>) or flags passed
    into functions to control behavior
    (ex: <a href="OPEN_CREATE.html">SQLITE_OPEN_READONLY</a>).</p></li>

<li><p><a href="funclist.html"><b>List Of Functions.</b></a>
    Functions and/or methods operating on the 
    <a href="objlist.html">objects</a> and using and/or
    returning <a href="constlist.html">constants</a>.  There
    are many function, but most applications only use a handful.
    </p></li>
</ol>

<tcl>
c3ref_close_file

</tcl>

<p>This page defined the C-language interface to SQLite.
This page is intended as a reference to what SQLite
is suppose to do.  This is not a tutorial.  This
page is designed to be precise, not easy to read.</p>

<p>This page contains all C-language interface information
in a single HTML file.  The same information is also
available broken out into 
<a href="c3ref/intro.html">lots of small pages</a>
for easier viewing, if you prefer.</p>

<p>The content on this document is extracted from comments
in the source code.</p>

<hr>

<tcl>

# Do a table of contents for objects
#
set objlist {}
foreach c $content {
  foreach {key title type keywords body code} $c break
  if {$type!="datatype"} continue
  set keywords [lsort $keywords]
  set k [lindex $keywords 0]
  foreach kw $keywords {
    lappend objlist [list $k $kw]
  }
}





puts {<h2>Objects:</h2>}
output_list 3 $objlist 0
puts {<hr>}
c3ref_open_file objlist.html Objects
output_list 3 $objlist 1

puts {<p>Other lists:
<a href="constlist.html">Constants</a> and
<a href="funclist.html">Functions</a>.}
c3ref_close_file



# Do a table of contents for constants
#
set clist {}
foreach c $content {
  foreach {key title type keywords body code} $c break
  if {$type!="constant"} continue
  set keywords [lsort $keywords]
  set k [lindex $keywords 0]
  foreach kw $keywords {
    lappend clist [list $k $kw]
  }
}





puts {<h2>Constants:</h2>}
set clist [lsort -index 1 $clist]
output_list 2 $clist 0
puts {<hr>}
c3ref_open_file constlist.html Constants
output_list 2 $clist 1

puts {<p>Other lists:
<a href="objlist.html">Objects</a> and
<a href="funclist.html">Functions</a>.</p>}

c3ref_close_file



# Do a table of contents for functions
#
set funclist {}
foreach c $content {
  foreach {key title type keywords body code} $c break
  if {$type!="function"} continue
  set keywords [lsort $keywords]
  set k [lindex $keywords 0]
  foreach kw $keywords {
    lappend funclist [list $k $kw]
  }
}





puts {<h2>Functions:</h2>}
set funclist [lsort -index 1 $funclist]
output_list 3 $funclist 0
puts {<hr>}
c3ref_open_file funclist.html Functions
output_list 3 $funclist 1

puts {<p>Other lists:
<a href="constlist.html">Constants</a> and
<a href="objlist.html">Objects</a>.</p>}
c3ref_close_file


# Resolve links to anchors in the single-file spec.
#
proc resolve_links {args} {
  set tag [lindex $args 0]
  regsub -all {[^a-zA-Z0-9_]} $tag {} tag
  set x "<a href=\"#$tag\">"
  if {[llength $args]>2} {
    append x [lrange $args 2 end]</a>
  } else {
    append x [lindex $args 0]</a>
  }
  return $x
}

# Resolve links to anchors in the multi-file spec.
#
proc resolve_m_links {args} {
  set tag [lindex $args 0]
  regsub -all {[^a-zA-Z0-9_]} $tag {} tag
  global keyword_to_file
  if {[info exists keyword_to_file($tag)]} {
    set tag $keyword_to_file($tag)
    set begin "<a href=\"$tag\">"
    set end "</a>"
  } else {
    set begin ""
    set end ""
  }
  if {[llength $args]>2} {
    append x $begin[lrange $args 2 end]$end
  } else {
    append x $begin[lindex $args 0]$end
  }
  return $x
}

# Output all the records
#
foreach c [lsort $content] {
  foreach {key title type keywords body code} $c break
  foreach k $keywords {

    puts "<a name=\"$k\"></a></a>"
  }
  puts "<h2>$title</h2>"
  puts "<blockquote><pre>"
  puts "$code"
  puts "</pre></blockquote>"
  regsub -all "\n\n+" $body "</p>\n\n<p>" body
  regsub -all {\[} <p>$body</p> {[resolve_links } body
  set body [subst -novar -noback $body]
  puts "$body"
  puts "<hr>"
  set fkey [lindex $keywords 0]
  if {![info exists keyword_to_file($fkey)]} {
    real_puts fkey=$fkey
    real_puts c=$c
    error "no such key: $fkey"
  }
  c3ref_open_file $::keyword_to_file($fkey) $title
  puts "<blockquote><pre>"
  puts "$code"
  puts "</pre></blockquote>"
  set body [lindex $c 4]
  regsub -all "\n\n+" $body "</p>\n\n<p>" body
  regsub -all {\[} <p>$body</p> {[resolve_m_links } body
  set body [subst -novar -noback $body]
  puts "$body"

  puts {<p>See also lists of
  <a href="objlist.html">Objects</a>,
  <a href="constlist.html">Constants</a>, and
  <a href="funclist.html">Functions</a>.</p>}

  c3ref_close_file

}
</tcl>







|
<
|
|





|
<

|










|
|













|
|




<












>
>
>
>
>
|
|
<
<
<
>
|


|
>
>













>
>
>
>
>
|

|
<
<
<
>
|


>
|
>














>
>
>
>
>
|

|
<
<
<
>
|


<
|
|
<
<
<
<
<
<
<
<
<
<
<
<
<
|
<
|
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<





|
>
|
<
|
|
|
|
<
<
<
<
<
|
<
<
<
<
|
|
|
|
|
<

|
<
<
>
|



>
|
>


204
205
206
207
208
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
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



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
325
326
327
328



329
330
331
332

333
334













335

336



















337
338
339
340
341
342
343
344

345
346
347
348





349




350
351
352
353
354

355
356


357
358
359
360
361
362
363
364
365
366
<p>The interface is broken down into three catagories:</p>

<ol>
<li><p><a href="objlist.html"><b>List Of Objects.</b></a>
    All abstract objects and datatypes used by the
    SQLite library.  There are a handful of objects, but
    only three which most users need to be aware of:
    A database connection object [sqlite3], 

    prepared statement object [sqlite3_stmt], and the 64-bit integer
    type [sqlite3_int64].</p></li>

<li><p><a href="constlist.html"><b>List Of Constants.</b></a>
    Numeric constants just by SQLite and represented by
    #defines in the sqlite3.h header file.  These constants
    are things such as numeric return parameters from
    various interfaces (ex: [SQLITE_OK] or flags passed

    into functions to control behavior
    (ex: [SQLITE_OPEN_READONLY]).</p></li>

<li><p><a href="funclist.html"><b>List Of Functions.</b></a>
    Functions and/or methods operating on the 
    <a href="objlist.html">objects</a> and using and/or
    returning <a href="constlist.html">constants</a>.  There
    are many function, but most applications only use a handful.
    </p></li>
</ol>

<tcl>
hd_close_aux
hd_enable_main 1
</tcl>

<p>This page defined the C-language interface to SQLite.
This page is intended as a reference to what SQLite
is suppose to do.  This is not a tutorial.  This
page is designed to be precise, not easy to read.</p>

<p>This page contains all C-language interface information
in a single HTML file.  The same information is also
available broken out into 
<a href="c3ref/intro.html">lots of small pages</a>
for easier viewing, if you prefer.</p>

<p>This document is created by a script which scans comments
in the source code files.</p>

<hr>

<tcl>

# Do a table of contents for objects
#
set objlist {}
foreach c $content {
  foreach {key title type keywords body code} $c break
  if {$type!="datatype"} continue
  set keywords [lsort $keywords]
  set k [lindex $keywords 0]
  foreach kw $keywords {
    lappend objlist [list $k $kw]
  }
}
hd_open_aux c3ref/objlist.html
hd_header {List Of SQLite Objects}
hd_enable_main 0
hd_puts {<a href="intro.html"><h2>SQLite C Interface</h2></a>}
hd_enable_main 1
hd_puts {<h2>Objects:</h2>}
output_list 3 $objlist



hd_enable_main 0
hd_puts {<p>Other lists:
<a href="constlist.html">Constants</a> and
<a href="funclist.html">Functions</a>.}
hd_close_aux
hd_enable_main 1
hd_puts {<hr>}

# Do a table of contents for constants
#
set clist {}
foreach c $content {
  foreach {key title type keywords body code} $c break
  if {$type!="constant"} continue
  set keywords [lsort $keywords]
  set k [lindex $keywords 0]
  foreach kw $keywords {
    lappend clist [list $k $kw]
  }
}
hd_open_aux c3ref/constlist.html
hd_header {List Of SQLite Constants}
hd_enable_main 0
hd_puts {<a href="intro.html"><h2>SQLite C Interface</h2></a>}
hd_enable_main 1
hd_puts {<h2>Constants:</h2>}
set clist [lsort -index 1 $clist]
output_list 2 $clist



hd_enable_main 0
hd_puts {<p>Other lists:
<a href="objlist.html">Objects</a> and
<a href="funclist.html">Functions</a>.</p>}
hd_enable_main 1
hd_close_aux
hd_puts {<hr>}


# Do a table of contents for functions
#
set funclist {}
foreach c $content {
  foreach {key title type keywords body code} $c break
  if {$type!="function"} continue
  set keywords [lsort $keywords]
  set k [lindex $keywords 0]
  foreach kw $keywords {
    lappend funclist [list $k $kw]
  }
}
hd_open_aux c3ref/funclist.html
hd_header {List Of SQLite Functions}
hd_enable_main 0
hd_puts {<a href="intro.html"><h2>SQLite C Interface</h2></a>}
hd_enable_main 1
hd_puts {<h2>Functions:</h2>}
set funclist [lsort -index 1 $funclist]
output_list 3 $funclist



hd_enable_main 0
hd_puts {<p>Other lists:
<a href="constlist.html">Constants</a> and
<a href="objlist.html">Objects</a>.</p>}

hd_enable_main 1
hd_close_aux













hd_puts {<hr>}






















# Output all the records
#
foreach c [lsort $content] {
  foreach {key title type keywords body code} $c break
  set kw [lindex [lsort $keywords] 0]
  hd_fragment $kw
  hd_open_aux c3ref/[convert_keyword_to_filename $kw]

  hd_header $title
  hd_enable_main 0
  hd_puts {<a href="intro.html"><h2>SQLite C Interface</h2></a>}
  hd_enable_main 1





  eval hd_keywords $keywords





  hd_puts "<h2>$title</h2>"
  hd_puts "<blockquote><pre>"
  hd_puts "$code"
  hd_puts "</pre></blockquote>"

  regsub -all "\n\n+" $body "</p>\n\n<p>" body
  hd_resolve <p>$body</p>


  hd_enable_main 0
  hd_puts {<p>See also lists of
  <a href="objlist.html">Objects</a>,
  <a href="constlist.html">Constants</a>, and
  <a href="funclist.html">Functions</a>.</p>}
  hd_enable_main 1
  hd_close_aux
  hd_puts "<hr>"
}
</tcl>
Changes to pages/changes.in.
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
<title>SQLite changes</title>

<p>
This page provides a high-level summary of changes to SQLite.
For more detail, refer the the checkin logs generated by
CVS at
<a href="http://www.sqlite.org/cvstrac/timeline">
http://www.sqlite.org/cvstrac/timeline</a>.
</p>


<tcl>
proc chng {date desc} {

  if {[regexp {\(([0-9.]+)\)} $date all vers]} {
    set label [string map {. _} $vers]
    puts "<A NAME=\"version_$label\"></a>"
  }
  puts "<DT><B>$date</B></DT>"
  regsub -all {[Tt]icket #(\d+)} $desc \
      {<a href="http://www.sqlite.org/cvstrac/tktview?tn=\1">\0</a>} desc
  puts "<DD><P><UL>$desc</UL></P>"
  puts "</DD>"
  if {[regexp {\(3\.\d+\.\d+\)} $date tag]} {
    set tag [string map {. _} [string range $tag 1 end-1]]
    global OUT DEST DOC
    set saved_OUT $OUT
    file mkdir $DEST/releaselog
    set filename $DEST/releaselog/$tag.html
    set OUT [open $filename w]
    PutsHeader "SQLite Release $date" ../


    puts "<h2>SQLite Release of $date</h2>"
    puts "<p>Changes associated with this release include the following:</p>"
    regsub -all {<a href="(?!http:)} $desc {<a href="../} desc
    puts "<p><ul>$desc</ul></p>"
    putsin4 {
      <p>A <a href="../changes.html">complete list of SQLite releases</a>
      in a single pages is also available.  A detailed history of every
      check-in is available at
      <a href="http://www.sqlite.org/cvstrac/timeline">
      http://www.sqlite.org/cvstrac/timeline</a>.</p>
    }
    PutsFooter $DOC/pages/changes.in
    close $OUT
    set OUT $saved_OUT
  }
}

chng {2007 Dec 14 (3.5.4)} {
<li>Fix a critical bug in UPDATE or DELETE that occurs when an
OR REPLACE clause or a trigger causes rows in the same table to
be deleted as side effects.  (See ticket #2832.)  The most likely
result of this bug is a segmentation fault, though database
corruption is a possibility.</li>
<li>Bring the processing of ORDER BY into compliance with the 
SQL standard for case where a result alias and a table column name
are in conflict.  Correct behavior is to prefer the result alias.
Older versions of SQLite incorrectly picked the table column.
(See ticket #2822.)</li>
<li>The <a href="lang_vacuum.html">VACUUM</a> command preserves
the setting of the 
<a href="pragma.html#pragma_legacy_file_format">legacy_file_format pragma</a>.
(Ticket #2804.)</li>
<li>Productize and officially support the group_concat() SQL function.</li>
<li>Better optimization of some IN operator expressions.</li>
<li>Add the ability to change the 
<a href="pragma.html#pragma_auto_vacuum">auto_vacuum</a> status of a
database by setting the auto_vaccum pragma and VACUUMing the database.</li>
<li>Prefix search in FTS3 is much more efficient.</li>
<li>Relax the SQL statement length restriction in the CLI so that









>



>


|

|
<
<
|
|
|
|
<
<

|
|
|
>
>
|
|

|
|






<
|
|






|






|



|







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
<title>SQLite changes</title>

<p>
This page provides a high-level summary of changes to SQLite.
For more detail, refer the the checkin logs generated by
CVS at
<a href="http://www.sqlite.org/cvstrac/timeline">
http://www.sqlite.org/cvstrac/timeline</a>.
</p>
<dl>

<tcl>
proc chng {date desc} {
  global DEST
  if {[regexp {\(([0-9.]+)\)} $date all vers]} {
    set label [string map {. _} $vers]
    hd_fragment version_$label
  }
  hd_puts "<dt><b>$date</b></dt>"


  hd_resolve "<dd><p><ul>$desc</ul></p>"
  hd_puts "</dd>"
  if {[regexp {\((3\.\d+\.\d+)\)} $date all vers]} {
    set tag [string map {. _} $vers]


    file mkdir $DEST/releaselog
    set filename releaselog/$tag.html
    hd_open_aux $filename
    hd_header "SQLite Release $vers On $date"
    hd_keywords "Version $vers" "version $vers"
    hd_enable_main 0
    hd_puts "<h2>SQLite Release $vers On $date</h2>"
    hd_puts "<p>Changes associated with this release include the following:</p>"
    regsub -all {<a href="(?!http:)} $desc {<a href="../} desc
    hd_resolve "<p><ul>$desc</ul></p>"
    hd_puts {
      <p>A <a href="../changes.html">complete list of SQLite releases</a>
      in a single pages is also available.  A detailed history of every
      check-in is available at
      <a href="http://www.sqlite.org/cvstrac/timeline">
      http://www.sqlite.org/cvstrac/timeline</a>.</p>
    }

    hd_close_aux
    hd_enable_main 1
  }
}

chng {2007 Dec 14 (3.5.4)} {
<li>Fix a critical bug in UPDATE or DELETE that occurs when an
OR REPLACE clause or a trigger causes rows in the same table to
be deleted as side effects.  (See [ticket #2832].)  The most likely
result of this bug is a segmentation fault, though database
corruption is a possibility.</li>
<li>Bring the processing of ORDER BY into compliance with the 
SQL standard for case where a result alias and a table column name
are in conflict.  Correct behavior is to prefer the result alias.
Older versions of SQLite incorrectly picked the table column.
(See [ticket #2822].)</li>
<li>The <a href="lang_vacuum.html">VACUUM</a> command preserves
the setting of the 
<a href="pragma.html#pragma_legacy_file_format">legacy_file_format pragma</a>.
([Ticket #2804].)</li>
<li>Productize and officially support the group_concat() SQL function.</li>
<li>Better optimization of some IN operator expressions.</li>
<li>Add the ability to change the 
<a href="pragma.html#pragma_auto_vacuum">auto_vacuum</a> status of a
database by setting the auto_vaccum pragma and VACUUMing the database.</li>
<li>Prefix search in FTS3 is much more efficient.</li>
<li>Relax the SQL statement length restriction in the CLI so that
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
<li>Fix a long-standing bug in INSERT INTO ... SELECT ... statements
where the SELECT is compound.
<li>Fix a long-standing bug in RAISE(IGNORE) as used in BEFORE triggers.
<li>Fixed the operator precedence for the ~ operator.
<li>On win32, do not return an error when attempting to delete a file
that does not exist.
<li>Allow collating sequence names to be quoted.
<li>Modify the TCL interface to use sqlite3_prepare_v2().
<li>Fix multiple bugs that can occur following a malloc() failure.
<li>sqlite3_step() returns SQLITE_MISUSE instead of crashing when
called with a NULL parameter.
<li>FTS3 now uses the SQLite memory allocator exclusively.  The
FTS3 amalgamation can now be appended to the SQLite amalgamation to
generate a super-amalgamation containing both.
<li>The DISTINCT keyword now will sometimes use an INDEX if an
appropriate index is available and the optimizer thinks its use
might be advantageous.







|

|







76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
<li>Fix a long-standing bug in INSERT INTO ... SELECT ... statements
where the SELECT is compound.
<li>Fix a long-standing bug in RAISE(IGNORE) as used in BEFORE triggers.
<li>Fixed the operator precedence for the ~ operator.
<li>On win32, do not return an error when attempting to delete a file
that does not exist.
<li>Allow collating sequence names to be quoted.
<li>Modify the TCL interface to use [sqlite3_prepare_v2()].
<li>Fix multiple bugs that can occur following a malloc() failure.
<li>[sqlite3_step()] returns [SQLITE_MISUSE] instead of crashing when
called with a NULL parameter.
<li>FTS3 now uses the SQLite memory allocator exclusively.  The
FTS3 amalgamation can now be appended to the SQLite amalgamation to
generate a super-amalgamation containing both.
<li>The DISTINCT keyword now will sometimes use an INDEX if an
appropriate index is available and the optimizer thinks its use
might be advantageous.
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

chng {2007 Oct 04 (3.5.1)} {
<li><i><b>Nota Bene:</b> We are not using terms "alpha" or "beta" on this
    release because the code is stable and because if we use those terms,
    nobody will upgrade.  However, we still reserve the right to make
    incompatible changes to the new VFS interface in future releases.</i></li>

<li>Fix a bug in the handling of SQLITE_FULL errors that could lead
    to database corruption.  Ticket #2686.
<li>The test_async.c drive now does full file locking and works correctly
    when used simultaneously by multiple processes on the same database.
<li>The CLI ignores whitespace (including comments) at the end of lines
<li>Make sure the query optimizer checks dependences on all terms of
    a compound SELECT statement.  Ticket #2640.
<li>Add demonstration code showing how to build a VFS for a raw
    mass storage without a filesystem.
<li>Added an output buffer size parameter to the xGetTempname() method
    of the VFS layer.
<li>Sticky SQLITE_FULL or SQLITE_IOERR errors in the pager are reset
    when a new transaction is started.
}


chng {2007 Sep 04 (3.5.0) alpha} {
<li>Redesign the OS interface layer.  See
    <a href="34to35.html">34to35.html</a> for details.
    <font color="red">*** Potentially incompatible change ***</font>
<li>The <a href="c3ref/release_memory.html">
    sqlite3_release_memory()</a>,
    <a href="c3ref/soft_heap_limit.html">
    sqlite3_soft_heap_limit()</a>,
    and <a href="c3ref/enable_shared_cache.html">
    sqlite3_enable_shared_cache()</a> interfaces now work cross all
    threads in the process, not just the single thread in which they
    are invoked.
    <font color="red">*** Potentially incompatible change ***</font>
<li>Added the 
    <a href="c3ref/open.html">sqlite3_open_v2()</a>
    interface.  
<li>Reimplemented the memory allocation subsystem and made it 
    replacable at compile-time.
<li>Created a new mutex subsystem and made it replacable at
    compile-time.
<li>The same database connection may now be used simultaneously by
    separate threads.
}


chng {2007 August 13 (3.4.2)} {
<li>Fix a database corruption bug that might occur if a ROLLBACK command
is executed in <a href="pragma.html#pragma_auto_vacuum">auto-vacuum mode</a>
and a very small <a href="c3ref/soft_heap_limit.html">
soft_heap_limit</a> is set. 
<a href="http://www.sqlite.org/cvstrac/tktview?tn=2565">Ticket #2565</a>.

<li>Add the ability to run a full regression test with a small
<a href="c3ref/soft_heap_limit.html">soft_heap_limit</a>.

<li>Fix other minor problems with using small soft heap limits.

<li>Work-around for 
<a href="http://gcc.gnu.org/bugzilla/show_bug.cgi?id=32575">GCC bug 32575</a>.

<li>Improved error detection of misused aggregate functions.







|
|




|




|








<
|
<
<
<
|



|
<
<












<
|
|


|







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

chng {2007 Oct 04 (3.5.1)} {
<li><i><b>Nota Bene:</b> We are not using terms "alpha" or "beta" on this
    release because the code is stable and because if we use those terms,
    nobody will upgrade.  However, we still reserve the right to make
    incompatible changes to the new VFS interface in future releases.</i></li>

<li>Fix a bug in the handling of [SQLITE_FULL] errors that could lead
    to database corruption.  [Ticket #2686].
<li>The test_async.c drive now does full file locking and works correctly
    when used simultaneously by multiple processes on the same database.
<li>The CLI ignores whitespace (including comments) at the end of lines
<li>Make sure the query optimizer checks dependences on all terms of
    a compound SELECT statement.  [Ticket #2640].
<li>Add demonstration code showing how to build a VFS for a raw
    mass storage without a filesystem.
<li>Added an output buffer size parameter to the xGetTempname() method
    of the VFS layer.
<li>Sticky [SQLITE_FULL] or [SQLITE_IOERR] errors in the pager are reset
    when a new transaction is started.
}


chng {2007 Sep 04 (3.5.0) alpha} {
<li>Redesign the OS interface layer.  See
    <a href="34to35.html">34to35.html</a> for details.
    <font color="red">*** Potentially incompatible change ***</font>

<li>The [sqlite3_release_memory()], [sqlite3_soft_heap_limit()],



    and [sqlite3_enable_shared_cache()] interfaces now work cross all
    threads in the process, not just the single thread in which they
    are invoked.
    <font color="red">*** Potentially incompatible change ***</font>
<li>Added the [sqlite3_open_v2()] interface.  


<li>Reimplemented the memory allocation subsystem and made it 
    replacable at compile-time.
<li>Created a new mutex subsystem and made it replacable at
    compile-time.
<li>The same database connection may now be used simultaneously by
    separate threads.
}


chng {2007 August 13 (3.4.2)} {
<li>Fix a database corruption bug that might occur if a ROLLBACK command
is executed in <a href="pragma.html#pragma_auto_vacuum">auto-vacuum mode</a>

and a very small [sqlite3_soft_heap_limit] is set. 
[Ticket #2565].

<li>Add the ability to run a full regression test with a small
[sqlite3_soft_heap_limit].

<li>Fix other minor problems with using small soft heap limits.

<li>Work-around for 
<a href="http://gcc.gnu.org/bugzilla/show_bug.cgi?id=32575">GCC bug 32575</a>.

<li>Improved error detection of misused aggregate functions.
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
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
300
301
302
303
304
305
306
307
308
309
310
311
312
    internal functions in the amalgamation.</li>
<li>Add pluggable tokenizers and <a href="http://www.icu-project.org/">ICU</a>
    tokenization support to FTS2</li>
<li>Other minor bug fixes and documentation enhancements</li>
}

chng {2007 June 18 (3.4.0)} {
<li>Fix a bug that can lead to database corruption if an SQLITE_BUSY error
    occurs in the middle of an explicit transaction and that transaction
    is later committed.  
    <a href="http://www.sqlite.org/cvstrac/tktview?tn=2409">Ticket #2409.</a>
    See the
    <a href="http://www.sqlite.org/cvstrac/wiki?p=CorruptionFollowingBusyError">
    CorruptionFollowingBusyError</a> wiki page for details.</i>
<li>Fix a bug that can lead to database corruption if autovacuum mode is
    on and a malloc() failure follows a CREATE TABLE or CREATE INDEX statement
    which itself follows a cache overflow inside a transaction.  See
    <a href="http://www.sqlite.org/cvstrac/tktview?tn=2418">ticket #2418</a>. 
    </li>
<li>Added explicit <a href="limits.html">upper bounds</a> on the sizes and
    quantities of things SQLite can process.  This change might cause
    compatibility problems for
    applications that use SQLite in the extreme, which is why the current
    release is 3.4.0 instead of 3.3.18.</li>
<li>Added support for <a href="c3ref/blob_open.html">
    Incremental BLOB I/O</a>.</li>
<li>Added the <a href="c3ref/bind_blob.html">zeroblob API</a>
    and the <a href="lang_expr.html#zeroblob">zeroblob()</a> SQL function.</li>
<li>Added support for <a href="pragma.html#pragma_incremental_vacuum">
    Incremental Vacuum</a>.</li>
<li>Added the SQLITE_MIXED_ENDIAN_64BIT_FLOAT compile-time option to suppport
    ARM7 processors with goofy endianness.</li>
<li>Removed all instances of sprintf() and strcpy() from the core library.</li>
<li>Added support for <a href="http://www.icu-project.org/">
    International Components for Unicode (ICU)</a> to the full-text search
    extensions.
</ul><p>
<ul type="circle">
<li>In the windows OS driver, reacquire a SHARED lock if an attempt to
    acquire an EXCLUSIVE lock fails.  Ticket #2354</li>
<li>Fix the REPLACE() function so that it returns NULL if the second argument
    is an empty string.  Ticket #2324.</li>
<li>Document the hazards of type coversions in
    <a href="c3ref/column_blob.html">sqlite3_column_blob()</a>
    and related APIs.  Fix unnecessary type conversions.  Ticket #2321.</li>
<li>Internationalization of the TRIM() function.  Ticket #2323</li>
<li>Use memmove() instead of memcpy() when moving between memory regions
    that might overlap.  Ticket #2334</li>
<li>Fix an optimizer bug involving subqueries in a compound SELECT that has
    both an ORDER BY and a LIMIT clause.  Ticket #2339.</li>
<li>Make sure the <a href="c3ref/mprintf.html">sqlite3_snprintf()
    </a> interface does not zero-terminate the buffer if the buffer size is
    less than 1.  Ticket #2341</li>
<li>Fix the built-in printf logic so that it prints "NaN" not "Inf" for
    floating-point NaNs.  Ticket #2345</li>
<li>When converting BLOB to TEXT, use the text encoding of the main database.
    Ticket #2349</li>
<li>Keep the full precision of integers (if possible) when casting to
    NUMERIC.  Ticket #2364</li>
<li>Fix a bug in the handling of UTF16 codepoint 0xE000</li>
<li>Consider explicit collate clauses when matching WHERE constraints
    to indices in the query optimizer.  Ticket #2391</li>
<li>Fix the query optimizer to correctly handle constant expressions in 
    the ON clause of a LEFT JOIN.  Ticket #2403</li>
<li>Fix the query optimizer to handle rowid comparisions to NULL
    correctly.  Ticket #2404</li>
<li>Fix many potental segfaults that could be caused by malicious SQL
    statements.</li>
}

chng {2007 April 25 (3.3.17)} {
<li>When the "write_version" value of the database header is larger than
    what the library understands, make the database read-only instead of
    unreadable.</li>
<li>Other minor bug fixes</li>
}

chng {2007 April 18 (3.3.16)} {
<li>Fix a bug that caused VACUUM to fail if NULLs appeared in a
    UNIQUE column.</li>
<li>Reinstate performance improvements that were added in 3.3.14

    but regressed in 3.3.15.</li>
<li>Fix problems with the handling of ORDER BY expressions on
    compound SELECT statements in subqueries.</li>
<li>Fix a potential segfault when destroying locks on WinCE in
    a multi-threaded environment.</li>
<li>Documentation updates.</li>
}

chng {2007 April 9 (3.3.15)} {
<li>Fix a bug introduced in 3.3.14 that caused a rollback of
    CREATE TEMP TABLE to leave the database connection wedged.</li>
<li>Fix a bug that caused an extra NULL row to be returned when
    a descending query was interrupted by a change to the database.</li>
<li>The FOR EACH STATEMENT clause on a trigger now causes a syntax
    error.  It used to be silently ignored.</li>
<li>Fix an obscure and relatively harmless problem that might have caused
    a resource leak following an I/O error.</li>
<li>Many improvements to the test suite.  Test coverage now exceeded 98%</li>
}

chng {2007 April 2 (3.3.14)} {
<li>Fix a <a href="http://www.sqlite.org/cvstrac/tktview?tn=2273">bug</a>
    in 3.3.13 that could cause a segfault when the IN operator
    is used one one term of a two-column index and the right-hand side of
    the IN operator contains a NULL.</li>
<li>Added a new OS interface method for determining the sector size
    of underlying media:  sqlite3OsSectorSize().</li>
<li>A new algorithm for statements of the form
    INSERT INTO <i>table1</i> SELECT * FROM <i>table2</i>
    is faster and reduces fragmentation.  VACUUM uses statements of







|

|
<






|






<
|
|












|

|

|
|
|

|

|
|
|
|

|

|

|


|

|

|














|
>
|




















|
|







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
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
300
301
302
303
    internal functions in the amalgamation.</li>
<li>Add pluggable tokenizers and <a href="http://www.icu-project.org/">ICU</a>
    tokenization support to FTS2</li>
<li>Other minor bug fixes and documentation enhancements</li>
}

chng {2007 June 18 (3.4.0)} {
<li>Fix a bug that can lead to database corruption if an [SQLITE_BUSY] error
    occurs in the middle of an explicit transaction and that transaction
    is later committed.  [Ticket #2409].

    See the
    <a href="http://www.sqlite.org/cvstrac/wiki?p=CorruptionFollowingBusyError">
    CorruptionFollowingBusyError</a> wiki page for details.</i>
<li>Fix a bug that can lead to database corruption if autovacuum mode is
    on and a malloc() failure follows a CREATE TABLE or CREATE INDEX statement
    which itself follows a cache overflow inside a transaction.  See
    [ticket #2418]. 
    </li>
<li>Added explicit <a href="limits.html">upper bounds</a> on the sizes and
    quantities of things SQLite can process.  This change might cause
    compatibility problems for
    applications that use SQLite in the extreme, which is why the current
    release is 3.4.0 instead of 3.3.18.</li>

<li>Added support for [sqlite3_blob_open|Incremental BLOB I/O].</li>
<li>Added the [sqlite3_bind_zeroblob()] API</a>
    and the <a href="lang_expr.html#zeroblob">zeroblob()</a> SQL function.</li>
<li>Added support for <a href="pragma.html#pragma_incremental_vacuum">
    Incremental Vacuum</a>.</li>
<li>Added the SQLITE_MIXED_ENDIAN_64BIT_FLOAT compile-time option to suppport
    ARM7 processors with goofy endianness.</li>
<li>Removed all instances of sprintf() and strcpy() from the core library.</li>
<li>Added support for <a href="http://www.icu-project.org/">
    International Components for Unicode (ICU)</a> to the full-text search
    extensions.
</ul><p>
<ul type="circle">
<li>In the windows OS driver, reacquire a SHARED lock if an attempt to
    acquire an EXCLUSIVE lock fails.  [Ticket #2354]</li>
<li>Fix the REPLACE() function so that it returns NULL if the second argument
    is an empty string.  [Ticket #2324].</li>
<li>Document the hazards of type coversions in
    [sqlite3_column_blob()]
    and related APIs.  Fix unnecessary type conversions.  [Ticket #2321].</li>
<li>Internationalization of the TRIM() function.  [Ticket #2323]</li>
<li>Use memmove() instead of memcpy() when moving between memory regions
    that might overlap.  [Ticket #2334]</li>
<li>Fix an optimizer bug involving subqueries in a compound SELECT that has
    both an ORDER BY and a LIMIT clause.  [Ticket #2339].</li>
<li>Make sure the [sqlite3_snprintf()]
    interface does not zero-terminate the buffer if the buffer size is
    less than 1.  [Ticket #2341]</li>
<li>Fix the built-in printf logic so that it prints "NaN" not "Inf" for
    floating-point NaNs.  [Ticket #2345]</li>
<li>When converting BLOB to TEXT, use the text encoding of the main database.
    [Ticket #2349]</li>
<li>Keep the full precision of integers (if possible) when casting to
    NUMERIC.  [Ticket #2364]</li>
<li>Fix a bug in the handling of UTF16 codepoint 0xE000</li>
<li>Consider explicit collate clauses when matching WHERE constraints
    to indices in the query optimizer.  [Ticket #2391]</li>
<li>Fix the query optimizer to correctly handle constant expressions in 
    the ON clause of a LEFT JOIN.  [Ticket #2403]</li>
<li>Fix the query optimizer to handle rowid comparisions to NULL
    correctly.  [Ticket #2404]</li>
<li>Fix many potental segfaults that could be caused by malicious SQL
    statements.</li>
}

chng {2007 April 25 (3.3.17)} {
<li>When the "write_version" value of the database header is larger than
    what the library understands, make the database read-only instead of
    unreadable.</li>
<li>Other minor bug fixes</li>
}

chng {2007 April 18 (3.3.16)} {
<li>Fix a bug that caused VACUUM to fail if NULLs appeared in a
    UNIQUE column.</li>
<li>Reinstate performance improvements that were added in
    [Version 3.3.14]
    but regressed in [Version 3.3.15].</li>
<li>Fix problems with the handling of ORDER BY expressions on
    compound SELECT statements in subqueries.</li>
<li>Fix a potential segfault when destroying locks on WinCE in
    a multi-threaded environment.</li>
<li>Documentation updates.</li>
}

chng {2007 April 9 (3.3.15)} {
<li>Fix a bug introduced in 3.3.14 that caused a rollback of
    CREATE TEMP TABLE to leave the database connection wedged.</li>
<li>Fix a bug that caused an extra NULL row to be returned when
    a descending query was interrupted by a change to the database.</li>
<li>The FOR EACH STATEMENT clause on a trigger now causes a syntax
    error.  It used to be silently ignored.</li>
<li>Fix an obscure and relatively harmless problem that might have caused
    a resource leak following an I/O error.</li>
<li>Many improvements to the test suite.  Test coverage now exceeded 98%</li>
}

chng {2007 April 2 (3.3.14)} {
<li>Fix a bug ([ticket #2273])
    that could cause a segfault when the IN operator
    is used one one term of a two-column index and the right-hand side of
    the IN operator contains a NULL.</li>
<li>Added a new OS interface method for determining the sector size
    of underlying media:  sqlite3OsSectorSize().</li>
<li>A new algorithm for statements of the form
    INSERT INTO <i>table1</i> SELECT * FROM <i>table2</i>
    is faster and reduces fragmentation.  VACUUM uses statements of
1128
1129
1130
1131
1132
1133
1134
1135
1136
1137
1138
1139
1140
1141
1142
1143
1144
1145
1146
1147
1148
1149
1150
1151
1152
1153
1154
1155
1156
1157
1158
1159
    the rollback logic extensively in order to prevent any future problems.
    </li>
}

chng {2002 Jly 6 (2.5.5)} {
<li>Fix a bug which could cause database corruption during a rollback.
    This bugs was introduced in version 2.4.0 by the freelist
    optimization of checking [410].</li>
<li>Fix a bug in aggregate functions for VIEWs.</li>
<li>Other minor changes and enhancements.</li>
}

chng {2002 Jly 1 (2.5.4)} {
<li>Make the "AS" keyword optional again.</li>
<li>The datatype of columns now appear in the 4th argument to the
    callback.</li>
<li>Added the <b>sqlite_open_aux_file()</b> API, though it is still
    mostly undocumented and untested.</li>
<li>Added additional test cases and fixed a few bugs that those
    test cases found.</li>
}

chng {2002 Jun 24 (2.5.3)} {
<li>Bug fix:  Database corruption can occur due to the optimization
    that was introduced in version 2.4.0 (check-in [410]).  The problem
    should now be fixed.  The use of versions 2.4.0 through 2.5.2 is
    not recommended.</li>
}

chng {2002 Jun 24 (2.5.2)} {
<li>Added the new <b>SQLITE_TEMP_MASTER</b> table which records the schema
    for temporary tables in the same way that <b>SQLITE_MASTER</b> does for







|
















|







1119
1120
1121
1122
1123
1124
1125
1126
1127
1128
1129
1130
1131
1132
1133
1134
1135
1136
1137
1138
1139
1140
1141
1142
1143
1144
1145
1146
1147
1148
1149
1150
    the rollback logic extensively in order to prevent any future problems.
    </li>
}

chng {2002 Jly 6 (2.5.5)} {
<li>Fix a bug which could cause database corruption during a rollback.
    This bugs was introduced in version 2.4.0 by the freelist
    optimization of checkin &#91;410&#93;.</li>
<li>Fix a bug in aggregate functions for VIEWs.</li>
<li>Other minor changes and enhancements.</li>
}

chng {2002 Jly 1 (2.5.4)} {
<li>Make the "AS" keyword optional again.</li>
<li>The datatype of columns now appear in the 4th argument to the
    callback.</li>
<li>Added the <b>sqlite_open_aux_file()</b> API, though it is still
    mostly undocumented and untested.</li>
<li>Added additional test cases and fixed a few bugs that those
    test cases found.</li>
}

chng {2002 Jun 24 (2.5.3)} {
<li>Bug fix:  Database corruption can occur due to the optimization
    that was introduced in version 2.4.0 (check-in &#91;410&#93;).  The problem
    should now be fixed.  The use of versions 2.4.0 through 2.5.2 is
    not recommended.</li>
}

chng {2002 Jun 24 (2.5.2)} {
<li>Added the new <b>SQLITE_TEMP_MASTER</b> table which records the schema
    for temporary tables in the same way that <b>SQLITE_MASTER</b> does for
1780
1781
1782
1783
1784
1785
1786
1787
1788
1789
1790
1791
1792
1793
1794
    of data contained a NULL.</li>
}

chng {2000 Aug 22 (Version 1.0.3)} {
<li>In the sqlite shell, print the "Database opened READ ONLY" message
    to stderr instead of stdout.</li>
<li>In the sqlite shell, now print the version number on initial startup.</li>
<li>Add the <b>sqlite_version[]</b> string constant to the library</li>
<li>Makefile updates</li>
<li>Bug fix: incorrect VDBE code was being generated for the following
    circumstance: a query on an indexed table containing a WHERE clause with
    an IN operator that had a subquery on its right-hand side.</li>
}

chng {2000 Aug 18 (Version 1.0.1)} {







|







1771
1772
1773
1774
1775
1776
1777
1778
1779
1780
1781
1782
1783
1784
1785
    of data contained a NULL.</li>
}

chng {2000 Aug 22 (Version 1.0.3)} {
<li>In the sqlite shell, print the "Database opened READ ONLY" message
    to stderr instead of stdout.</li>
<li>In the sqlite shell, now print the version number on initial startup.</li>
<li>Add the <b>sqlite_version&#91;&#93;</b> string constant to the library</li>
<li>Makefile updates</li>
<li>Bug fix: incorrect VDBE code was being generated for the following
    circumstance: a query on an indexed table containing a WHERE clause with
    an IN operator that had a subquery on its right-hand side.</li>
}

chng {2000 Aug 18 (Version 1.0.1)} {
1934
1935
1936
1937
1938
1939
1940
1941
1942
1943
1944
1945

files.</li>
<li>And many, many bug fixes...</li>
}

chng {2000 May 29} {
<li>Initial Public Release of Alpha code</li>
}

puts {
</DL>
}
</tcl>








<
<
<
<

>
1925
1926
1927
1928
1929
1930
1931




1932
1933
files.</li>
<li>And many, many bug fixes...</li>
}

chng {2000 May 29} {
<li>Initial Public Release of Alpha code</li>
}




</tcl>
</dl>
Changes to pages/different.in.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
<title>Distinctive Features Of SQLite</title>

<p>
This page highlights some of the characteristics of SQLite that are
unusual and which make SQLite different from many other SQL
database engines.
</p>

<tcl>
proc feature {tag name text} {
  puts "<a name=\"$tag\" /></a>"
  puts "<p><b>$name</b></p>\n"
  puts "<blockquote>$text</blockquote>\n"
}

feature zeroconfig {Zero-Configuration} {
  SQLite does not need to be "installed" before it is used. 
  There is no "setup" procedure.  There is no
  server process that needs to be started, stopped, or configured.
  There is










|
|
|







1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
<title>Distinctive Features Of SQLite</title>

<p>
This page highlights some of the characteristics of SQLite that are
unusual and which make SQLite different from many other SQL
database engines.
</p>

<tcl>
proc feature {tag name text} {
  hd_fragment $tag
  hd_puts "<p><b>$name</b></p>\n"
  hd_puts "<blockquote>$text</blockquote>\n"
}

feature zeroconfig {Zero-Configuration} {
  SQLite does not need to be "installed" before it is used. 
  There is no "setup" procedure.  There is no
  server process that needs to be started, stopped, or configured.
  There is
Changes to pages/docs.in.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
<title>SQLite Documentation</title>

<h2>Available Documentation</h2>
<table width="100%" cellpadding="5">

<tcl>
proc doc {name url desc} {
  puts {<tr><td valign="top" align="right">}
  regsub -all { +} $name {\&nbsp;} name
  puts "<a href=\"$url\">$name</a></td>"
  puts {<td width="10"></td>}
  puts {<td valign="top" align="left">}
  puts $desc
  puts {</td></tr>}
}

doc {Appropriate Uses For SQLite} {whentouse.html} {
  This document describes situations where SQLite is an appropriate
  database engine to use versus situations where a client/server
  database engine might be a better choice.
}







|

|
|
|
|
|







1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
<title>SQLite Documentation</title>

<h2>Available Documentation</h2>
<table width="100%" cellpadding="5">

<tcl>
proc doc {name url desc} {
  hd_puts {<tr><td valign="top" align="right">}
  regsub -all { +} $name {\&nbsp;} name
  hd_puts "<a href=\"$url\">$name</a></td>"
  hd_puts {<td width="10"></td>}
  hd_puts {<td valign="top" align="left">}
  hd_resolve $desc
  hd_puts {</td></tr>}
}

doc {Appropriate Uses For SQLite} {whentouse.html} {
  This document describes situations where SQLite is an appropriate
  database engine to use versus situations where a client/server
  database engine might be a better choice.
}
Changes to pages/download.in.
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
      set units MiB
    } elseif {$size>1024} {
      set size [format %.2f [expr {$size/(1024.0)}]]
      set units KiB
    }
    global pending_heading
    if {$pending_heading!=""} {
      puts "<tr><td colspan=\"4\"><b>$pending_heading</b></td></tr>"
      set pending_heading {}
    }
    puts "<tr><td width=\"10\"></td>"
    puts "<td valign=\"top\" align=\"right\">"
    puts "<a href=\"$file\">$file</a><br>($size $units)</td>"
    puts "<td width=\"5\"></td>"
    regsub -all VERSION $desc $version d2
    puts "<td valign=\"top\">[string trim $d2]</td></tr>"
  }
}
cd $::DEST

proc Heading {title} {
  set ::pending_heading $title
  # puts "<tr><td colspan=4><big><b>$title</b></big></td></tr>"
}

Heading {Precompiled Binaries for Linux}

Product sqlite3-V3.bin.gz {
  A command-line program for accessing and modifying
  SQLite version 3.* databases.







|


|
|
|
|

|






|







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
      set units MiB
    } elseif {$size>1024} {
      set size [format %.2f [expr {$size/(1024.0)}]]
      set units KiB
    }
    global pending_heading
    if {$pending_heading!=""} {
      hd_puts "<tr><td colspan=\"4\"><b>$pending_heading</b></td></tr>"
      set pending_heading {}
    }
    hd_puts "<tr><td width=\"10\"></td>"
    hd_puts "<td valign=\"top\" align=\"right\">"
    hd_puts "<a href=\"$file\">$file</a><br>($size $units)</td>"
    hd_puts "<td width=\"5\"></td>"
    regsub -all VERSION $desc $version d2
    hd_puts "<td valign=\"top\">[string trim $d2]</td></tr>"
  }
}
cd $::DEST

proc Heading {title} {
  set ::pending_heading $title
  # hd_puts "<tr><td colspan=4><big><b>$title</b></big></td></tr>"
}

Heading {Precompiled Binaries for Linux}

Product sqlite3-V3.bin.gz {
  A command-line program for accessing and modifying
  SQLite version 3.* databases.
Changes to pages/dynload.in.
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
<li><p>
This should produce the file sqlite.so in the bld directory</p></li>

<li><p>
Create a pkgIndex.tcl file that contains this line

<blockquote><pre>
package ifneeded sqlite 1.0 [list load [file join $dir sqlite.so]]
</pre></blockquote></p></li>

<li><p>
To use this put sqlite.so and pkgIndex.tcl in the same directory</p></li>

<li><p>
From that directory start wish</p></li>

<li><p>
Execute the following tcl command (tells tcl where to fine loadable
modules)
<blockquote><pre>
lappend auto_path [exec pwd]
</pre></blockquote></p></li>

<li><p>
Load the package 
<blockquote><pre>
package require sqlite
</pre></blockquote></p></li>







|












|







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
<li><p>
This should produce the file sqlite.so in the bld directory</p></li>

<li><p>
Create a pkgIndex.tcl file that contains this line

<blockquote><pre>
package ifneeded sqlite 1.0 &#91;list load &#91;file join $dir sqlite.so]]
</pre></blockquote></p></li>

<li><p>
To use this put sqlite.so and pkgIndex.tcl in the same directory</p></li>

<li><p>
From that directory start wish</p></li>

<li><p>
Execute the following tcl command (tells tcl where to fine loadable
modules)
<blockquote><pre>
lappend auto_path &#91;exec pwd]
</pre></blockquote></p></li>

<li><p>
Load the package 
<blockquote><pre>
package require sqlite
</pre></blockquote></p></li>
Changes to pages/faq.in.
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
</pre></blockquote>
  <p>is logically equivalent to saying:</p>
<blockquote><pre>
INSERT INTO t1 VALUES((SELECT max(a) FROM t1)+1,123);
</pre></blockquote>

  <p>There is a new API function named
  <a href="c3ref/last_insert_rowid.html">
  sqlite3_last_insert_rowid()</a> which will return the integer key
  for the most recent insert operation.</p>

  <p>Note that the integer key is one greater than the largest
  key that was in the table just prior to the insert.  The new key
  will be unique over all keys currently in the table, but it might
  overlap with keys that have been previously deleted from the
  table.  To create keys that are unique over the lifetime of the







<
|







37
38
39
40
41
42
43

44
45
46
47
48
49
50
51
</pre></blockquote>
  <p>is logically equivalent to saying:</p>
<blockquote><pre>
INSERT INTO t1 VALUES((SELECT max(a) FROM t1)+1,123);
</pre></blockquote>

  <p>There is a new API function named

  [sqlite3_last_insert_rowid()] which will return the integer key
  for the most recent insert operation.</p>

  <p>Note that the integer key is one greater than the largest
  key that was in the table just prior to the insert.  The new key
  will be unique over all keys currently in the table, but it might
  overlap with keys that have been previously deleted from the
  table.  To create keys that are unique over the lifetime of the
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
  consider using a client/server database.  But experience suggests that
  most applications need much less concurrency than their designers imagine.
  </p>

  <p>When SQLite tries to access a file that is locked by another
  process, the default behavior is to return SQLITE_BUSY.  You can
  adjust this behavior from C code using the 
  <a href="c3ref/busy_handler.html">sqlite3_busy_handler()</a> or
  <a href="c3ref/busy_timeout.html">sqlite3_busy_timeout()</a>
  API functions.</p>
}

faq {
  Is SQLite threadsafe?
} {
  <p><a href="http://www.eecs.berkeley.edu/Pubs/TechRpts/2006/EECS-2006-1.pdf">
  Threads are evil</a>.  Avoid them.

  <p>SQLite is threadsafe.  We make this concession since many users choose
  to ignore the advice given in the previous paragraph.
  But in order to be thread-safe, SQLite must be compiled
  with the SQLITE_THREADSAFE preprocessor macro set to 1.  Both the windows
  and linux precompiled binaries in the distribution are compiled this way.
  If you are unsure if the SQLite library you are linking against is compiled
  to be threadsafe you can call the
  <a href="c3ref/threadsafe.html">sqlite3_threadsafe()</a>
  interface to find out.
  </p>

  <p>Prior to version 3.3.1,
  an <b>sqlite3</b> structure could only be used in the same thread
  that called <a href="c3ref/open.html">sqlite3_open</a>
  to create it.
  You could not open a
  database in one thread then pass the handle off to another thread for
  it to use.  This was due to limitations (bugs?) in many common threading
  implementations such as on RedHat9.  Specifically, an fcntl() lock
  created by one thread cannot be removed or modified by a different
  thread on the troublesome systems.  And since SQLite uses fcntl()
  locks heavily for concurrency control, serious problems arose if you 
  start moving database connections across threads.</p>

  <p>The restriction on moving database connections across threads
  was relaxed somewhat in version 3.3.1.  With that and subsequent
  versions, it is safe to move a connection handle across threads
  as long as the connection is not holding any fcntl() locks.  You
  can safely assume that no locks are being held if no
  transaction is pending and all statements have been finalized.</p>


  <p>Under UNIX, you should not carry an open SQLite database across
  a fork() system call into the child process.  Problems will result
  if you do.</p>
}

faq {







|
<















|
<



|

<
|










|



|
>







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
  consider using a client/server database.  But experience suggests that
  most applications need much less concurrency than their designers imagine.
  </p>

  <p>When SQLite tries to access a file that is locked by another
  process, the default behavior is to return SQLITE_BUSY.  You can
  adjust this behavior from C code using the 
  [sqlite3_busy_handler()] or [sqlite3_busy_timeout()]

  API functions.</p>
}

faq {
  Is SQLite threadsafe?
} {
  <p><a href="http://www.eecs.berkeley.edu/Pubs/TechRpts/2006/EECS-2006-1.pdf">
  Threads are evil</a>.  Avoid them.

  <p>SQLite is threadsafe.  We make this concession since many users choose
  to ignore the advice given in the previous paragraph.
  But in order to be thread-safe, SQLite must be compiled
  with the SQLITE_THREADSAFE preprocessor macro set to 1.  Both the windows
  and linux precompiled binaries in the distribution are compiled this way.
  If you are unsure if the SQLite library you are linking against is compiled
  to be threadsafe you can call the [sqlite3_threadsafe()]

  interface to find out.
  </p>

  <p>Prior to [version 3.3.1],
  an <b>sqlite3</b> structure could only be used in the same thread

  that called [sqlite3_open()] to create it.
  You could not open a
  database in one thread then pass the handle off to another thread for
  it to use.  This was due to limitations (bugs?) in many common threading
  implementations such as on RedHat9.  Specifically, an fcntl() lock
  created by one thread cannot be removed or modified by a different
  thread on the troublesome systems.  And since SQLite uses fcntl()
  locks heavily for concurrency control, serious problems arose if you 
  start moving database connections across threads.</p>

  <p>The restriction on moving database connections across threads
  was relaxed somewhat in [version 3.3.1].  With that and subsequent
  versions, it is safe to move a connection handle across threads
  as long as the connection is not holding any fcntl() locks.  You
  can safely assume that no locks are being held if no
  transaction is pending and all 
  [sqlite3_stmt|statements] have been [sqlite3_finalize|finalized].</p>

  <p>Under UNIX, you should not carry an open SQLite database across
  a fork() system call into the child process.  Problems will result
  if you do.</p>
}

faq {
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
  and in some cases can cause your results to be a little different
  from what you might expect.</p>
}

# End of questions and answers.
#############

puts {<h2>Frequently Asked Questions</h2>}

# puts {<DL COMPACT>}
# for {set i 1} {$i<$cnt} {incr i} {
#   puts "  <DT><A HREF=\"#q$i\">($i)</A></DT>"
#   puts "  <DD>[lindex $faq($i) 0]</DD>"
# }
# puts {</DL>}
puts {<OL>}
for {set i 1} {$i<$cnt} {incr i} {
  puts "<li><a href=\"#q$i\">[lindex $faq($i) 0]</a></li>"
}
puts {</OL>}

for {set i 1} {$i<$cnt} {incr i} {
  puts "<A NAME=\"q$i\"></A><HR />"
  puts "<P><B>($i) [lindex $faq($i) 0]</B></P>\n"
  puts "<BLOCKQUOTE>[lindex $faq($i) 1]</BLOCKQUOTE></LI>\n"
}
puts {</OL>}
</tcl>







|

<
<
<
<
<
<
|

|

|


|
|
|

|

433
434
435
436
437
438
439
440
441






442
443
444
445
446
447
448
449
450
451
452
453
454
  and in some cases can cause your results to be a little different
  from what you might expect.</p>
}

# End of questions and answers.
#############

hd_puts {<h2>Frequently Asked Questions</h2>}







hd_puts {<oL>}
for {set i 1} {$i<$cnt} {incr i} {
  hd_puts "<li><a href=\"#q$i\">[lindex $faq($i) 0]</a></li>"
}
hd_puts {</ol>}

for {set i 1} {$i<$cnt} {incr i} {
  hd_fragment q$i
  hd_puts "<p><b>($i) [lindex $faq($i) 0]</b></p>\n"
  hd_resolve "<blockquote>[lindex $faq($i) 1]</blockquote></li>\n"
}
hd_puts {</ol>}
</tcl>
Changes to pages/lang.in.
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
<title>Query Language Understood by SQLite</title>

<h2 class="pdf_section">SQL As Understood By SQLite</h2>

<p>SQLite understands most of the standard SQL
language.  But it does <a href="omitted.html">omit some features</a>
while at the same time
adding a few features of its own.  This document attempts to
describe precisely what parts of the SQL language SQLite does
and does not support.  A list of <a href="lang_keywords.html">keywords</a> is 
also provided.</p>

<p>In all of the syntax diagrams that follow, literal text is shown in
bold blue.  Non-terminal symbols are shown in italic red.  Operators
that are part of the syntactic markup itself are shown in black roman.</p>

<p>This document is just an overview of the SQL syntax implemented
by SQLite.  Many low-level productions are omitted.  For detailed information
on the language that SQLite understands, refer to the source code and
the grammar file "parse.y".</p>

<div class="pdf_ignore">
<p>SQLite implements the follow syntax:</p>

<table width="100%" cellpadding="5" border="0">
<tr><td valign="top"><ul>

<tcl>
set i 0

>
|


















<







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
<title>Query Language Understood by SQLite</title>

<h2>SQL As Understood By SQLite</h2>

<p>SQLite understands most of the standard SQL
language.  But it does <a href="omitted.html">omit some features</a>
while at the same time
adding a few features of its own.  This document attempts to
describe precisely what parts of the SQL language SQLite does
and does not support.  A list of <a href="lang_keywords.html">keywords</a> is 
also provided.</p>

<p>In all of the syntax diagrams that follow, literal text is shown in
bold blue.  Non-terminal symbols are shown in italic red.  Operators
that are part of the syntactic markup itself are shown in black roman.</p>

<p>This document is just an overview of the SQL syntax implemented
by SQLite.  Many low-level productions are omitted.  For detailed information
on the language that SQLite understands, refer to the source code and
the grammar file "parse.y".</p>


<p>SQLite implements the follow syntax:</p>

<table width="100%" cellpadding="5" border="0">
<tr><td valign="top"><ul>

<tcl>
set i 0
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
}] {
  foreach {s_title s_tag} $section {}
  if {$s_tag=="pragma.html"} {
    set url $s_tag
  } else {
    set url lang_$s_tag.html
  }
  puts "<li><a href=\"$url\">$s_title</a></li>"
  incr i
  if {$i==15} {
    puts "</ul></td><td valign=\"top\"><ul>"
  }
}
</tcl>
</ul></td></tr></table>

<tcl>
proc Operator {name} {
  return "<font color=\"#2c2cf0\"><big>$name</big></font>"
}
proc Nonterminal {name} {
  return "<i><font color=\"#ff3434\">$name</font></i>"
}
proc Keyword {name} {
  return "<font color=\"#2c2cf0\">$name</font>"
}
proc Example {text} {
  puts "<blockquote><pre>$text</pre></blockquote>"
}

proc Section {name label} {
  global OUT DOC DEST
  PutsFooter $DOC/pages/lang.in
  close $OUT
  set OUT [open $DEST/lang_$label.html w]
  PutsHeader "SQLite Query Language: $name"
  puts {<a href="lang.html"><h2>SQL As Understood By SQLite</h2></a>}
  puts "<h3>$name</h3>"
}

###############################################################################
Section {ALTER TABLE} altertable

Syntax {sql-statement} {
ALTER TABLE [<database-name> .] <table-name> <alteration>







|


|
















|



|
<
|
|
|
|
|







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
}] {
  foreach {s_title s_tag} $section {}
  if {$s_tag=="pragma.html"} {
    set url $s_tag
  } else {
    set url lang_$s_tag.html
  }
  hd_puts "<li><a href=\"$url\">$s_title</a></li>"
  incr i
  if {$i==15} {
    hd_puts "</ul></td><td valign=\"top\"><ul>"
  }
}
</tcl>
</ul></td></tr></table>

<tcl>
proc Operator {name} {
  return "<font color=\"#2c2cf0\"><big>$name</big></font>"
}
proc Nonterminal {name} {
  return "<i><font color=\"#ff3434\">$name</font></i>"
}
proc Keyword {name} {
  return "<font color=\"#2c2cf0\">$name</font>"
}
proc Example {text} {
  hd_puts "<blockquote><pre>$text</pre></blockquote>"
}

proc Section {name label} {
  global DOC

  hd_close_main
  hd_open_main lang_$label.html
  hd_header "SQLite Query Language: $name" $DOC/pages/lang.in
  hd_puts {<a href="lang.html"><h2>SQL As Understood By SQLite</h2></a>}
  hd_puts "<h3>$name</h3>"
}

###############################################################################
Section {ALTER TABLE} altertable

Syntax {sql-statement} {
ALTER TABLE [<database-name> .] <table-name> <alteration>
1007
1008
1009
1010
1011
1012
1013
1014
1015
1016
1017
1018
1019
1020
1021
to strings, numbers, or blobs and it always gives as its result the
value of the operand.</p>

<p>Note that there are two variations of the equals and not equals
operators.  Equals can be either

<tcl>
puts "[Operator =] or [Operator ==].
The non-equals operator can be either
[Operator !=] or [Operator {&lt;&gt;}].
The [Operator ||] operator is \"concatenate\" - it joins together
the two strings of its operands.
The operator [Operator %] outputs the remainder of its left 
operand modulo its right operand.</p>








|







1006
1007
1008
1009
1010
1011
1012
1013
1014
1015
1016
1017
1018
1019
1020
to strings, numbers, or blobs and it always gives as its result the
value of the operand.</p>

<p>Note that there are two variations of the equals and not equals
operators.  Equals can be either

<tcl>
hd_puts "[Operator =] or [Operator ==].
The non-equals operator can be either
[Operator !=] or [Operator {&lt;&gt;}].
The [Operator ||] operator is \"concatenate\" - it joins together
the two strings of its operands.
The operator [Operator %] outputs the remainder of its left 
operand modulo its right operand.</p>

1091
1092
1093
1094
1095
1096
1097
1098
1099
1100
1101
1102
1103
1104
1105
as NULL.</p>

<a name="like"></a>
<p>The LIKE operator does a pattern matching comparison. The operand
to the right contains the pattern, the left hand operand contains the
string to match against the pattern. 

<tcl>puts "A percent symbol [Operator %] in the pattern matches any
sequence of zero or more characters in the string.  An underscore
[Operator _] in the pattern matches any single character in the
string.  Any other character matches itself or it's lower/upper case
equivalent (i.e. case-insensitive matching).  (A bug: SQLite only
understands upper/lower case for 7-bit Latin characters.  Hence the
LIKE operator is case sensitive for 8-bit iso8859 characters or UTF-8
characters.  For example, the expression <b>'a'&nbsp;LIKE&nbsp;'A'</b>







|







1090
1091
1092
1093
1094
1095
1096
1097
1098
1099
1100
1101
1102
1103
1104
as NULL.</p>

<a name="like"></a>
<p>The LIKE operator does a pattern matching comparison. The operand
to the right contains the pattern, the left hand operand contains the
string to match against the pattern. 

<tcl>hd_puts "A percent symbol [Operator %] in the pattern matches any
sequence of zero or more characters in the string.  An underscore
[Operator _] in the pattern matches any single character in the
string.  Any other character matches itself or it's lower/upper case
equivalent (i.e. case-insensitive matching).  (A bug: SQLite only
understands upper/lower case for 7-bit Latin characters.  Hence the
LIKE operator is case sensitive for 8-bit iso8859 characters or UTF-8
characters.  For example, the expression <b>'a'&nbsp;LIKE&nbsp;'A'</b>
1753
1754
1755
1756
1757
1758
1759
1760
1761
1762
1763
1764
1765
1766
1767
1768
1769
1770

<p>The SELECT statement is used to query the database.  The
result of a SELECT is zero or more rows of data where each row
has a fixed number of columns.  The number of columns in the
result is specified by the expression list in between the
SELECT and FROM keywords.  Any arbitrary expression can be used
as a result.  If a result expression is }
puts "[Operator *] then all columns of all tables are substituted"
puts {for that one expression.  If the expression is the name of}
puts "a table followed by [Operator .*] then the result is all columns"
puts {in that one table.</p>

<p>The DISTINCT keyword causes a subset of result rows to be returned, 
in which each result row is different.  NULL values are not treated as 
distinct from each other.  The default behavior is that all result rows 
be returned, which can be made explicit with the keyword ALL.</p>

<p>The query is executed against one or more tables specified after







|
|
|
|







1752
1753
1754
1755
1756
1757
1758
1759
1760
1761
1762
1763
1764
1765
1766
1767
1768
1769

<p>The SELECT statement is used to query the database.  The
result of a SELECT is zero or more rows of data where each row
has a fixed number of columns.  The number of columns in the
result is specified by the expression list in between the
SELECT and FROM keywords.  Any arbitrary expression can be used
as a result.  If a result expression is }
hd_puts "[Operator *] then all columns of all tables are substituted"
hd_puts {for that one expression.  If the expression is the name of}
hd_puts "a table followed by [Operator .*] then the result is all columns"
hd_puts {in that one table.</p>

<p>The DISTINCT keyword causes a subset of result rows to be returned, 
in which each result row is different.  NULL values are not treated as 
distinct from each other.  The default behavior is that all result rows 
be returned, which can be made explicit with the keyword ALL.</p>

<p>The query is executed against one or more tables specified after
2043
2044
2045
2046
2047
2048
2049
2050
2051
2052
2053
2054
2055
2056
2057
2058
2059
   VALUES
   VIEW*
   VIRTUAL*
   WHEN
   WHERE
}]

puts {<DIV class="pdf_section">}
Section {SQLite Keywords} keywords 
puts {</DIV>}
</tcl>

<p>The SQL standard specifies a huge number of keywords which may not
be used as the names of tables, indices, columns, databases, user-defined
functions, collations, virtual table modules, or any other named object.
The list of keywords is so long that few people can remember them all.
For most SQL code, your safest bet is to never use any English language







|

|







2042
2043
2044
2045
2046
2047
2048
2049
2050
2051
2052
2053
2054
2055
2056
2057
2058
   VALUES
   VIEW*
   VIRTUAL*
   WHEN
   WHERE
}]

hd_puts {<DIV class="pdf_section">}
Section {SQLite Keywords} keywords 
hd_puts {</DIV>}
</tcl>

<p>The SQL standard specifies a huge number of keywords which may not
be used as the names of tables, indices, columns, databases, user-defined
functions, collations, virtual table modules, or any other named object.
The list of keywords is so long that few people can remember them all.
For most SQL code, your safest bet is to never use any English language
2116
2117
2118
2119
2120
2121
2122
2123
2124
2125
2126
2127
2128
2129
2130
2131
2132
2133
2134
2135
  if {[string index $word end]=="*"} {
    set word [string range $word 0 end-1]
    set font i
  } else {
    set font b
  }
  if {$i==$nRow} {
    puts "</td><td valign=\"top\" align=\"left\" width=\"20%\">"
    set i 1
  } else {
    incr i
  }
  puts "<$font>$word</$font><br>"
}
</tcl>
</td></tr></table></blockquote>

<h2>Special names</h2>

<p>The following are not keywords in SQLite, but are used as names of 







|




|







2115
2116
2117
2118
2119
2120
2121
2122
2123
2124
2125
2126
2127
2128
2129
2130
2131
2132
2133
2134
  if {[string index $word end]=="*"} {
    set word [string range $word 0 end-1]
    set font i
  } else {
    set font b
  }
  if {$i==$nRow} {
    hd_puts "</td><td valign=\"top\" align=\"left\" width=\"20%\">"
    set i 1
  } else {
    incr i
  }
  hd_puts "<$font>$word</$font><br>"
}
</tcl>
</td></tr></table></blockquote>

<h2>Special names</h2>

<p>The following are not keywords in SQLite, but are used as names of 
Changes to pages/limits.in.
37
38
39
40
41
42
43
44

45
46
47
48
49
50
51
52
53
54
55
application.  Some applications may what to increase a limit here
or there, but we expect such needs to be rare.  More commonly,
an application might want to recompile SQLite with much lower
limits to avoid excess resource utilization in the event of
bug in higher-level SQL statement generators or to help thwart 
attackers who inject malicious SQL statements.
</p>
}

proc limititem {title text} {
  puts "<li><p><b>$title</b></p>\n$text</li>"
}
puts {
<ol>
}

limititem {Maximum length of a string or BLOB} {
<p>
The maximum number of bytes in a string or BLOB in SQLite is defined
by the preprocessor macro SQLITE_MAX_LENGTH.  The default value







|
>

|

|







37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
application.  Some applications may what to increase a limit here
or there, but we expect such needs to be rare.  More commonly,
an application might want to recompile SQLite with much lower
limits to avoid excess resource utilization in the event of
bug in higher-level SQL statement generators or to help thwart 
attackers who inject malicious SQL statements.
</p>

<tcl>
proc limititem {title text} {
  hd_resolve "<li><p><b>$title</b></p>\n$text</li>"
}
hd_puts {
<ol>
}

limititem {Maximum length of a string or BLOB} {
<p>
The maximum number of bytes in a string or BLOB in SQLite is defined
by the preprocessor macro SQLITE_MAX_LENGTH.  The default value
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121

<p>
In most applications, the number of columns is small - a few dozen.
There are places in the SQLite code generator that use algorithms
that are O(N&sup2;) where N is the number of columns.  
So if you redefine SQLITE_MAX_COLUMN to be a
really huge number and you generate SQL that uses a large number of
columns, you may find that 
<a href="c3ref/prepare.html">sqlite3_prepare_v2()</a>
runs slowly.
}

limititem {Maximum Length Of An SQL Statement} {
<p>
The maximum number of bytes in the text of an SQL statement is 
limited to SQLITE_MAX_SQL_LENGTH which defaults to 1000000.  You







|
<







107
108
109
110
111
112
113
114

115
116
117
118
119
120
121

<p>
In most applications, the number of columns is small - a few dozen.
There are places in the SQLite code generator that use algorithms
that are O(N&sup2;) where N is the number of columns.  
So if you redefine SQLITE_MAX_COLUMN to be a
really huge number and you generate SQL that uses a large number of
columns, you may find that [sqlite3_prepare_v2()]

runs slowly.
}

limititem {Maximum Length Of An SQL Statement} {
<p>
The maximum number of bytes in the text of an SQL statement is 
limited to SQLITE_MAX_SQL_LENGTH which defaults to 1000000.  You
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
</p>

<blockquote>
INSERT INTO tab1 VALUES(?,?,?);
</blockquote>

<p>
Then use the
<a href="c3ref/bind_blob.html">sqlite3_bind_XXXX()</a> functions
to bind your large string values to the SQL statement.  The use of binding
obviates the need to escape quote characters in the string, reducing the
risk of SQL injection attacks.  It is also runs faster since the large
string does not need to be parsed or copied as much.
</p>
}








|
<







132
133
134
135
136
137
138
139

140
141
142
143
144
145
146
</p>

<blockquote>
INSERT INTO tab1 VALUES(?,?,?);
</blockquote>

<p>
Then use the [sqlite3_bind_blob|sqlite3_bind_XXXX()] functions

to bind your large string values to the SQL statement.  The use of binding
obviates the need to escape quote characters in the string, reducing the
risk of SQL injection attacks.  It is also runs faster since the large
string does not need to be parsed or copied as much.
</p>
}

303
304
305
306
307
308
309


and persistent stores for desktop applications.  In other words,
SQLite is designed for use with databases sized in kilobytes or 
megabytes not gigabytes.  If you are building an application to
work with databases that are hundreds of gigabytes or more 
in size, then you should perhaps consider using a different database 
engine that is explicitly designed for such large data sets.
</p>









>
>
302
303
304
305
306
307
308
309
310
and persistent stores for desktop applications.  In other words,
SQLite is designed for use with databases sized in kilobytes or 
megabytes not gigabytes.  If you are building an application to
work with databases that are hundreds of gigabytes or more 
in size, then you should perhaps consider using a different database 
engine that is explicitly designed for such large data sets.
</p>
}
</tcl>
Changes to pages/lockingv3.in.
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
  set h [expr {$level+1}]
  if {$h>6} {set h 6}
  set n $pnum(1).$pnum(2)
  for {set i 3} {$i<=$level} {incr i} {
    append n .$pnum($i)
  }
  if {$label!=""} {
    puts "<a name=\"$label\"></a>"
  }
  puts "<h$h>$n $title</h$h>"
}
set pnum(1) 0
set pnum(2) 0
set pnum(3) 0
set pnum(4) 0
set pnum(5) 0
set pnum(6) 0
set pnum(7) 0
set pnum(8) 0

HEADING 1 {File Locking And Concurrency In SQLite Version 3}

puts {
<p>Version 3 of SQLite introduces a more complex locking and journaling 
mechanism designed to improve concurrency and reduce the writer starvation 
problem.  The new mechanism also allows atomic commits of transactions
involving multiple database files.
This document describes the new locking mechanism.
The intended audience is programmers who want to understand and/or modify
the pager code and reviewers working to verify the design
of SQLite version 3.
</p>
}

HEADING 1 {Overview} overview

puts {
<p>
Locking and concurrency control are handled by the the 
<a href="http://www.sqlite.org/cvstrac/getfile/sqlite/src/pager.c">
pager module</a>.
The pager module is responsible for making SQLite "ACID" (Atomic,
Consistent, Isolated, and Durable).  The pager module makes sure changes
happen all at once, that either all changes occur or none of them do,







|

|












|













|







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
  set h [expr {$level+1}]
  if {$h>6} {set h 6}
  set n $pnum(1).$pnum(2)
  for {set i 3} {$i<=$level} {incr i} {
    append n .$pnum($i)
  }
  if {$label!=""} {
    hd_puts "<a name=\"$label\"></a>"
  }
  hd_puts "<h$h>$n $title</h$h>"
}
set pnum(1) 0
set pnum(2) 0
set pnum(3) 0
set pnum(4) 0
set pnum(5) 0
set pnum(6) 0
set pnum(7) 0
set pnum(8) 0

HEADING 1 {File Locking And Concurrency In SQLite Version 3}

hd_puts {
<p>Version 3 of SQLite introduces a more complex locking and journaling 
mechanism designed to improve concurrency and reduce the writer starvation 
problem.  The new mechanism also allows atomic commits of transactions
involving multiple database files.
This document describes the new locking mechanism.
The intended audience is programmers who want to understand and/or modify
the pager code and reviewers working to verify the design
of SQLite version 3.
</p>
}

HEADING 1 {Overview} overview

hd_puts {
<p>
Locking and concurrency control are handled by the the 
<a href="http://www.sqlite.org/cvstrac/getfile/sqlite/src/pager.c">
pager module</a>.
The pager module is responsible for making SQLite "ACID" (Atomic,
Consistent, Isolated, and Durable).  The pager module makes sure changes
happen all at once, that either all changes occur or none of them do,
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
separate processes, or both.  Throughout this document whenever the
word "process" is written you may substitute the word "thread" without
changing the truth of the statement.</p>
}

HEADING 1 {Locking} locking

puts {
<p>
From the point of view of a single process, a database file
can be in one of five locking states:
</p>

<p>
<table cellpadding="20">







|







78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
separate processes, or both.  Throughout this document whenever the
word "process" is written you may substitute the word "thread" without
changing the truth of the statement.</p>
}

HEADING 1 {Locking} locking

hd_puts {
<p>
From the point of view of a single process, a database file
can be in one of five locking states:
</p>

<p>
<table cellpadding="20">
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
stepping stone on the path to an EXCLUSIVE lock and so the pager module
does not track PENDING locks.
</p>
}

HEADING 1 {The Rollback Journal} rollback

puts {
<p>Any time a process wants to make a changes to a database file, it
first records enough information in the <em>rollback journal</em> to
restore the database file back to its initial condition.  Thus, before
altering any page of the database, the original contents of that page
must be written into the journal.  The journal also records the initial
size of the database so that if the database file grows it can be truncated
back to its original size on a rollback.</p>







|







146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
stepping stone on the path to an EXCLUSIVE lock and so the pager module
does not track PENDING locks.
</p>
}

HEADING 1 {The Rollback Journal} rollback

hd_puts {
<p>Any time a process wants to make a changes to a database file, it
first records enough information in the <em>rollback journal</em> to
restore the database file back to its initial condition.  Thus, before
altering any page of the database, the original contents of that page
must be written into the journal.  The journal also records the initial
size of the database so that if the database file grows it can be truncated
back to its original size on a rollback.</p>
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
    </ul>
</li>
</ul>
}

HEADING 2 {Dealing with hot journals} hot_journals

puts {
<p>
Before reading from a a database file, SQLite always checks to see if that
database file has a hot journal.  If the file does have a hot journal, then
the journal is rolled back before the file is read.  In this way, we ensure
that the database file is in a consistent state before it is read.
</p>








|







210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
    </ul>
</li>
</ul>
}

HEADING 2 {Dealing with hot journals} hot_journals

hd_puts {
<p>
Before reading from a a database file, SQLite always checks to see if that
database file has a hot journal.  If the file does have a hot journal, then
the journal is rolled back before the file is read.  In this way, we ensure
that the database file is in a consistent state before it is read.
</p>

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
<p>After the algorithm above completes successfully, it is safe to 
read from the database file.  Once all reading has completed, the
SHARED lock is dropped.</p>
}

HEADING 2 {Deleting stale master journals} stale_master_journals

puts {
<p>A stale master journal is a master journal that is no longer being
used for anything.  There is no requirement that stale master journals
be deleted.  The only reason for doing so is to free up disk space.</p>

<p>A master journal is stale if no individual file journals are pointing
to it.  To figure out if a master journal is stale, we first read the
master journal to obtain the names of all of its file journals.  Then
we check each of those file journals.  If any of the file journals named
in the master journal exists and points back to the master journal, then
the master journal is not stale.  If all file journals are either missing
or refer to other master journals or no master journal at all, then the
master journal we are testing is stale and can be safely deleted.</p>
}

HEADING 1 {Writing to a database file} writing

puts {
<p>To write to a database, a process must first acquire a SHARED lock
as described above (possibly rolling back incomplete changes if there
is a hot journal). 
After a SHARED lock is obtained, a RESERVED lock must be acquired.
The RESERVED lock signals that the process intends to write to the
database at some point in the future.  Only one process at a time
can hold a RESERVED lock.  But other processes can continue to read







|
















|







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
<p>After the algorithm above completes successfully, it is safe to 
read from the database file.  Once all reading has completed, the
SHARED lock is dropped.</p>
}

HEADING 2 {Deleting stale master journals} stale_master_journals

hd_puts {
<p>A stale master journal is a master journal that is no longer being
used for anything.  There is no requirement that stale master journals
be deleted.  The only reason for doing so is to free up disk space.</p>

<p>A master journal is stale if no individual file journals are pointing
to it.  To figure out if a master journal is stale, we first read the
master journal to obtain the names of all of its file journals.  Then
we check each of those file journals.  If any of the file journals named
in the master journal exists and points back to the master journal, then
the master journal is not stale.  If all file journals are either missing
or refer to other master journals or no master journal at all, then the
master journal we are testing is stale and can be safely deleted.</p>
}

HEADING 1 {Writing to a database file} writing

hd_puts {
<p>To write to a database, a process must first acquire a SHARED lock
as described above (possibly rolling back incomplete changes if there
is a hot journal). 
After a SHARED lock is obtained, a RESERVED lock must be acquired.
The RESERVED lock signals that the process intends to write to the
database at some point in the future.  Only one process at a time
can hold a RESERVED lock.  But other processes can continue to read
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
<li>Drop the EXCLUSIVE and PENDING locks from all database files.
    </li>
</ol>
}

HEADING 2 {Writer starvation} writer_starvation

puts {
<p>In SQLite version 2, if many processes are reading from the database,
it might be the case that there is never a time when there are
no active readers.  And if there is always at least one read lock on the
database, no process would ever be able to make changes to the database
because it would be impossible to acquire a write lock.  This situation
is called <em>writer starvation</em>.</p>

<p>SQLite version 3 seeks to avoid writer starvation through the use of
the PENDING lock.  The PENDING lock allows existing readers to continue
but prevents new readers from connecting to the database.  So when a
process wants to write a busy database, it can set a PENDING lock which
will prevent new readers from coming in.  Assuming existing readers do
eventually complete, all SHARED locks will eventually clear and the
writer will be given a chance to make its changes.</p>
}

HEADING 1 {How To Corrupt Your Database Files} how_to_corrupt

puts {
<p>The pager module is robust but it is not completely failsafe.
It can be subverted.  This section attempts to identify and explain
the risks.</p>

<p>
Clearly, a hardware or operating system fault that introduces incorrect data
into the middle of the database file or journal will cause problems.







|


















|







398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
<li>Drop the EXCLUSIVE and PENDING locks from all database files.
    </li>
</ol>
}

HEADING 2 {Writer starvation} writer_starvation

hd_puts {
<p>In SQLite version 2, if many processes are reading from the database,
it might be the case that there is never a time when there are
no active readers.  And if there is always at least one read lock on the
database, no process would ever be able to make changes to the database
because it would be impossible to acquire a write lock.  This situation
is called <em>writer starvation</em>.</p>

<p>SQLite version 3 seeks to avoid writer starvation through the use of
the PENDING lock.  The PENDING lock allows existing readers to continue
but prevents new readers from connecting to the database.  So when a
process wants to write a busy database, it can set a PENDING lock which
will prevent new readers from coming in.  Assuming existing readers do
eventually complete, all SHARED locks will eventually clear and the
writer will be given a chance to make its changes.</p>
}

HEADING 1 {How To Corrupt Your Database Files} how_to_corrupt

hd_puts {
<p>The pager module is robust but it is not completely failsafe.
It can be subverted.  This section attempts to identify and explain
the risks.</p>

<p>
Clearly, a hardware or operating system fault that introduces incorrect data
into the middle of the database file or journal will cause problems.
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
on the same disk volume and/or remount disks using exactly the same names
after a power failure.
</p>
}

HEADING 1 {Transaction Control At The SQL Level} transaction_control

puts {
<p>
The changes to locking and concurrency control in SQLite version 3 also
introduce some subtle changes in the way transactions work at the SQL
language level.
By default, SQLite version 3 operates in <em>autocommit</em> mode.
In autocommit mode,
all changes to the database are committed as soon as all operations associated







|







512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
on the same disk volume and/or remount disks using exactly the same names
after a power failure.
</p>
}

HEADING 1 {Transaction Control At The SQL Level} transaction_control

hd_puts {
<p>
The changes to locking and concurrency control in SQLite version 3 also
introduce some subtle changes in the way transactions work at the SQL
language level.
By default, SQLite version 3 operates in <em>autocommit</em> mode.
In autocommit mode,
all changes to the database are committed as soon as all operations associated
Changes to pages/mingw.in.
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
<tcl>
proc Link {path {file {}}} {
  if {$file!=""} {
    set path $path/$file
  } else {
    set file $path
  }
  puts "<a href=\"$path\">$file</a>"
}
</tcl>

<p>Here are the steps:</p>

<ol>
<li>







|







18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
<tcl>
proc Link {path {file {}}} {
  if {$file!=""} {
    set path $path/$file
  } else {
    set file $path
  }
  hd_puts "<a href=\"$path\">$file</a>"
}
</tcl>

<p>Here are the steps:</p>

<ol>
<li>
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
</tcl>

The three files you will need are:</p>
<ul>
<li>
  <tcl>
  Link $ftpsite binutils-19990818-1-src.tar.gz
  puts </li><li>
  Link $ftpsite gcc-2.95.2-1-src.tar.gz
  puts </li><li>
  Link $ftpsite mingw-20000203.zip
</tcl>
</li>
</ul>

<p>Put all the downloads in a directory out of the way.  The sequel
will assume all downloads are in a directory named







|

|







42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
</tcl>

The three files you will need are:</p>
<ul>
<li>
  <tcl>
  Link $ftpsite binutils-19990818-1-src.tar.gz
  hd_puts </li><li>
  Link $ftpsite gcc-2.95.2-1-src.tar.gz
  hd_puts </li><li>
  Link $ftpsite mingw-20000203.zip
</tcl>
</li>
</ul>

<p>Put all the downloads in a directory out of the way.  The sequel
will assume all downloads are in a directory named
Changes to pages/news.in.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
<title>Recent SQLite News</title>

<h2>Recent News</h2>

<tcl>
proc newsitem {date title text} {
  regsub -all {[^a-z0-9]} [string tolower $date] _ tag
  puts "<a name=\"$tag\"></a>"
  puts "<h3>$date - $title</h3>"
  regsub -all "\n( *\n)+" $text "</p>\n\n<p>" txt
  regsub -all {[Tt]icket #(\d+)} $txt \
      {<a href="http://www.sqlite.org/cvstrac/tktview?tn=\1">\0</a>} txt
  puts "<p>$txt</p>"
  puts "<hr width=\"50%\">"
}

newsitem {2007-Dec-14} {Version 3.5.4} {
  Version 3.5.4 fixes an long-standing but obscure bug in UPDATE and
  DELETE which might cause database corruption.  (See ticket #2832.)
  Upgrading is recommended for all users.








|
|



|
|







1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
<title>Recent SQLite News</title>

<h2>Recent News</h2>

<tcl>
proc newsitem {date title text} {
  regsub -all {[^a-z0-9]} [string tolower $date] _ tag
  hd_puts "<a name=\"$tag\"></a>"
  hd_puts "<h3>$date - $title</h3>"
  regsub -all "\n( *\n)+" $text "</p>\n\n<p>" txt
  regsub -all {[Tt]icket #(\d+)} $txt \
      {<a href="http://www.sqlite.org/cvstrac/tktview?tn=\1">\0</a>} txt
  hd_resolve "<p>$txt</p>"
  hd_puts "<hr width=\"50%\">"
}

newsitem {2007-Dec-14} {Version 3.5.4} {
  Version 3.5.4 fixes an long-standing but obscure bug in UPDATE and
  DELETE which might cause database corruption.  (See ticket #2832.)
  Upgrading is recommended for all users.

Changes to pages/oldnews.in.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
<title>SQLite Older News</title>

<tcl>
proc newsitem {date title text} {
  regsub -all {[^a-z0-9]} $date _ tag
  puts "<a name=\"$tag\"></a>"
  puts "<h3>$date - $title</h3>"
  regsub -all "\n( *\n)+" $text "</p>\n\n<p>" txt
  puts "<p>$txt</p>"
  puts "<hr width=\"50%\">"
}

newsitem {2007-Nov-05} {Version 3.5.2} {
  This is an incremental release that fixes several minor problems,
  adds some obscure features, and provides some performance tweaks.  
  Upgrading is optional.






|
|

|
|







1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
<title>SQLite Older News</title>

<tcl>
proc newsitem {date title text} {
  regsub -all {[^a-z0-9]} $date _ tag
  hd_puts "<a name=\"$tag\"></a>"
  hd_puts "<h3>$date - $title</h3>"
  regsub -all "\n( *\n)+" $text "</p>\n\n<p>" txt
  hd_resolve "<p>$txt</p>"
  hd_puts "<hr width=\"50%\">"
}

newsitem {2007-Nov-05} {Version 3.5.2} {
  This is an incremental release that fixes several minor problems,
  adds some obscure features, and provides some performance tweaks.  
  Upgrading is optional.

Changes to pages/omitted.in.
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
plans to add features near the bottom of the list.
</p>

<table cellpadding="10">

<tcl>
proc feature {name desc} {
  puts "<tr><td valign=\"top\"><b><nobr>$name</nobr></b></td>"
  puts "<td width=\"10\">&nbsp;</th>"
  puts "<td valign=\"top\">$desc</td></tr>"
}

feature {FOREIGN KEY constraints} {
  FOREIGN KEY constraints are parsed but are not enforced.
  However, the equivalent constraint enforcement can be achieved
  using triggers.
}







|
|
|







14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
plans to add features near the bottom of the list.
</p>

<table cellpadding="10">

<tcl>
proc feature {name desc} {
  hd_puts "<tr><td valign=\"top\"><b><nobr>$name</nobr></b></td>"
  hd_puts "<td width=\"10\">&nbsp;</th>"
  hd_puts "<td valign=\"top\">$desc</td></tr>"
}

feature {FOREIGN KEY constraints} {
  FOREIGN KEY constraints are parsed but are not enforced.
  However, the equivalent constraint enforcement can be achieved
  using triggers.
}
Changes to pages/opcode.in.
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174

<p>You can use the <b>sqlite</b> command-line tool to see the
instructions generated by an SQL statement.  The following is
an example:</p>

<tcl>
proc Code {body} {
  puts {<blockquote><tt>}
  regsub -all {&} [string trim $body] {\&amp;} body
  regsub -all {>} $body {\&gt;} body
  regsub -all {<} $body {\&lt;} body
  regsub -all {\(\(\(} $body {<b>} body
  regsub -all {\)\)\)} $body {</b>} body
  regsub -all { } $body {\&nbsp;} body
  regsub -all \n $body <br>\n body
  puts $body
  puts {</tt></blockquote>}
}

Code {
$ (((sqlite ex1)))
sqlite> (((.explain)))
sqlite> (((explain delete from tbl1 where two<20;)))
addr  opcode        p1     p2     p3                                      







|







|
|







151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174

<p>You can use the <b>sqlite</b> command-line tool to see the
instructions generated by an SQL statement.  The following is
an example:</p>

<tcl>
proc Code {body} {
  hd_puts {<blockquote><tt>}
  regsub -all {&} [string trim $body] {\&amp;} body
  regsub -all {>} $body {\&gt;} body
  regsub -all {<} $body {\&lt;} body
  regsub -all {\(\(\(} $body {<b>} body
  regsub -all {\)\)\)} $body {</b>} body
  regsub -all { } $body {\&nbsp;} body
  regsub -all \n $body <br>\n body
  hd_puts $body
  hd_puts {</tt></blockquote>}
}

Code {
$ (((sqlite ex1)))
sqlite> (((.explain)))
sqlite> (((explain delete from tbl1 where two<20;)))
addr  opcode        p1     p2     p3                                      
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239

<p>
You can turn tracing back off by entering a similar statement but
changing the value "on" to "off".</p>

<h3>The Opcodes</h3>

<p>There are currently <tcl>puts [llength $OpcodeList]</tcl>
opcodes defined by the virtual machine.
All currently defined opcodes are described in the table below.
This table was generated automatically by scanning the source code
from the file <b>vdbe.c</b>.</p>

<p><table cellspacing="1" border="1" cellpadding="10">
<tr><th>Opcode&nbsp;Name</th><th>Description</th></tr>

<tcl>
  foreach op [lsort -dictionary $OpcodeList] {
    puts {<tr><td valign="top" align="center">}
    puts "<a name=\"$op\"></a>$op"
    puts "<td>[string trim $Opcode($op:text)]</td></tr>"
  }
</tcl>
</table></p>







|










|
|
|



216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239

<p>
You can turn tracing back off by entering a similar statement but
changing the value "on" to "off".</p>

<h3>The Opcodes</h3>

<p>There are currently <tcl>hd_puts [llength $OpcodeList]</tcl>
opcodes defined by the virtual machine.
All currently defined opcodes are described in the table below.
This table was generated automatically by scanning the source code
from the file <b>vdbe.c</b>.</p>

<p><table cellspacing="1" border="1" cellpadding="10">
<tr><th>Opcode&nbsp;Name</th><th>Description</th></tr>

<tcl>
  foreach op [lsort -dictionary $OpcodeList] {
    hd_puts {<tr><td valign="top" align="center">}
    hd_puts "<a name=\"$op\"></a>$op"
    hd_resolve "<td>[string trim $Opcode($op:text)]</td></tr>"
  }
</tcl>
</table></p>
Changes to pages/optoverview.in.
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
<title>The SQLite Query Optimizer Overview</title>

<tcl>
proc CODE {text} {
  puts "<blockquote><pre>"
  puts $text
  puts "</pre></blockquote>"
}
proc SYNTAX {text} {
  puts "<blockquote><pre>"
  set t2 [string map {& &amp; < &lt; > &gt;} $text]
  regsub -all "/(\[^\n/\]+)/" $t2 {</b><i>\1</i><b>} t3
  puts "<b>$t3</b>"
  puts "</pre></blockquote>"
}
proc PARAGRAPH {text} {
  # regsub -all "/(\[a-zA-Z0-9\]+)/" $text {<i>\1</i>} t2
  regsub -all "\\*(\[^\n*\]+)\\*" $text {<tt><b><big>\1</big></b></tt>} t3
  puts "<p>$t3</p>\n"
}
set level(0) 0
set level(1) 0
proc HEADING {n name {tag {}}} {
  if {$tag!=""} {
    puts "<a name=\"$tag\"></a>"
  }
  global level
  incr level($n)
  for {set i [expr {$n+1}]} {$i<10} {incr i} {
    set level($i) 0
  }
  if {$n==0} {
    set num {}
  } elseif {$n==1} {
    set num $level(1).0
  } else {
    set num $level(1)
    for {set i 2} {$i<=$n} {incr i} {
      append num .$level($i)
    }
  }
  incr n 1
  puts "<h$n>$num $name</h$n>"
}

HEADING 0 {The SQLite Query Optimizer Overview}

PARAGRAPH {
  This document provides a terse overview of how the query optimizer
  for SQLite works.  This is not a tutorial.  The reader is likely to




|
|
|


|


|
|




|




|
<
<
















|







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
<title>The SQLite Query Optimizer Overview</title>

<tcl>
proc CODE {text} {
  hd_puts "<blockquote><pre>"
  hd_puts $text
  hd_puts "</pre></blockquote>"
}
proc SYNTAX {text} {
  hd_puts "<blockquote><pre>"
  set t2 [string map {& &amp; < &lt; > &gt;} $text]
  regsub -all "/(\[^\n/\]+)/" $t2 {</b><i>\1</i><b>} t3
  hd_puts "<b>$t3</b>"
  hd_puts "</pre></blockquote>"
}
proc PARAGRAPH {text} {
  # regsub -all "/(\[a-zA-Z0-9\]+)/" $text {<i>\1</i>} t2
  regsub -all "\\*(\[^\n*\]+)\\*" $text {<tt><b><big>\1</big></b></tt>} t3
  hd_resolve "<p>$t3</p>\n"
}
set level(0) 0
set level(1) 0
proc HEADING {n name {tag {}}} {
  hd_fragment $tag


  global level
  incr level($n)
  for {set i [expr {$n+1}]} {$i<10} {incr i} {
    set level($i) 0
  }
  if {$n==0} {
    set num {}
  } elseif {$n==1} {
    set num $level(1).0
  } else {
    set num $level(1)
    for {set i 2} {$i<=$n} {incr i} {
      append num .$level($i)
    }
  }
  incr n 1
  hd_puts "<h$n>$num $name</h$n>"
}

HEADING 0 {The SQLite Query Optimizer Overview}

PARAGRAPH {
  This document provides a terse overview of how the query optimizer
  for SQLite works.  This is not a tutorial.  The reader is likely to
Changes to pages/pragma.in.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
<title>Pragma statements supported by SQLite</title>

<tcl>
proc Section {name {label {}}} {
  puts "\n<hr />"
  if {$label!=""} {
    puts "<a name=\"$label\"></a>"
  }
  puts "<h1>$name</h1>\n"
}
</tcl>

<p>The <a href="#syntax">PRAGMA command</a> is a special command used to 
modify the operation of the SQLite library or to query the library for 
internal (non-table) data. The PRAGMA command is issued using the same
interface as other SQLite commands (e.g. SELECT, INSERT) but is




|

|

|







1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
<title>Pragma statements supported by SQLite</title>

<tcl>
proc Section {name {label {}}} {
  hd_puts "\n<hr />"
  if {$label!=""} {
    hd_puts "<a name=\"$label\"></a>"
  }
  hd_puts "<h1>$name</h1>\n"
}
</tcl>

<p>The <a href="#syntax">PRAGMA command</a> is a special command used to 
modify the operation of the SQLite library or to query the library for 
internal (non-table) data. The PRAGMA command is issued using the same
interface as other SQLite commands (e.g. SELECT, INSERT) but is
Changes to pages/quickstart.in.
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
<blockquote><pre>
#!/usr/bin/tclsh
if {$argc!=2} {
  puts stderr "Usage: %s DATABASE SQL-STATEMENT"
  exit 1
}
load /usr/lib/tclsqlite3.so Sqlite3
<b>sqlite3</b> db [lindex $argv 0]
<b>db</b> eval [lindex $argv 1] x {
  foreach v $x(*) {
    puts "$v = $x($v)"
  }
  puts ""
}
<b>db</b> close
</pre></blockquote>







|
|







38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
<blockquote><pre>
#!/usr/bin/tclsh
if {$argc!=2} {
  puts stderr "Usage: %s DATABASE SQL-STATEMENT"
  exit 1
}
load /usr/lib/tclsqlite3.so Sqlite3
<b>sqlite3</b> db &#91;lindex $argv 0]
<b>db</b> eval &#91;lindex $argv 1] x {
  foreach v $x(*) {
    puts "$v = $x($v)"
  }
  puts ""
}
<b>db</b> close
</pre></blockquote>
Changes to pages/sharedcache.in.
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
  }
  set h [expr {$level+1}]
  if {$h>6} {set h 6}
  set n $pnum(1).$pnum(2)
  for {set i 3} {$i<=$level} {incr i} {
    append n .$pnum($i)
  }
  puts "<h$h>$n $title</h$h>"
}
set pnum(1) 0
set pnum(2) 0
set pnum(3) 0
set pnum(4) 0
set pnum(5) 0
set pnum(6) 0







|







9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
  }
  set h [expr {$level+1}]
  if {$h>6} {set h 6}
  set n $pnum(1).$pnum(2)
  for {set i 3} {$i<=$level} {incr i} {
    append n .$pnum($i)
  }
  hd_puts "<h$h>$n $title</h$h>"
}
set pnum(1) 0
set pnum(2) 0
set pnum(3) 0
set pnum(4) 0
set pnum(5) 0
set pnum(6) 0
Changes to pages/specification.in.
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
set spectag {}     ;# Current specification tag number
set specbody {}    ;# Text of current specification

proc endspec {} {
  global spectag specbody spec
  if {$spectag!="" && $specbody!=""} {
    if {[info exists spec($spectag)]} {
       real_puts "WARNING: duplicate specification: $spectag"
    }
    set body [string map \
          {<todo> {<font color="red">(TODO: } </todo> )</font>} $specbody]
    set spec($spectag) [string trim $body]
  }
  set spectag {}
  set specbody {}







|







29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
set spectag {}     ;# Current specification tag number
set specbody {}    ;# Text of current specification

proc endspec {} {
  global spectag specbody spec
  if {$spectag!="" && $specbody!=""} {
    if {[info exists spec($spectag)]} {
       puts stderr "WARNING: duplicate specification: $spectag"
    }
    set body [string map \
          {<todo> {<font color="red">(TODO: } </todo> )</font>} $specbody]
    set spec($spectag) [string trim $body]
  }
  set spectag {}
  set specbody {}
Changes to pages/sqlite.in.
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
semicolon), press "Enter" and the SQL will be executed.</p>

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

<tcl>
proc Code {body} {
  puts {<blockquote><tt>}
  regsub -all {&} [string trim $body] {\&amp;} body
  regsub -all {>} $body {\&gt;} body
  regsub -all {<} $body {\&lt;} body
  regsub -all {\(\(\(} $body {<b>} body
  regsub -all {\)\)\)} $body {</b>} body
  regsub -all { } $body {\&nbsp;} body
  regsub -all \n $body <br>\n body
  puts $body
  puts {</tt></blockquote>}
}

Code {
$ (((sqlite3 ex1)))
SQLite version 3.3.10
Enter ".help" for instructions
sqlite> (((create table tbl1(one varchar(10), two smallint);)))







|







|
|







17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
semicolon), press "Enter" and the SQL will be executed.</p>

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

<tcl>
proc Code {body} {
  hd_puts {<blockquote><tt>}
  regsub -all {&} [string trim $body] {\&amp;} body
  regsub -all {>} $body {\&gt;} body
  regsub -all {<} $body {\&lt;} body
  regsub -all {\(\(\(} $body {<b>} body
  regsub -all {\)\)\)} $body {</b>} body
  regsub -all { } $body {\&nbsp;} body
  regsub -all \n $body <br>\n body
  hd_puts $body
  hd_puts {</tt></blockquote>}
}

Code {
$ (((sqlite3 ex1)))
SQLite version 3.3.10
Enter ".help" for instructions
sqlite> (((create table tbl1(one varchar(10), two smallint);)))
Changes to pages/tclsqlite.in.
1
2
3
4
5
6

7
8
9
10
11
12
13
14
<title>The Tcl interface to the SQLite library</title>
<tcl>
proc METHOD {name text} {
  puts "<a name=\"$name\"></a>\n<h3>The \"$name\" method</h3>\n"
  puts $text
}

puts {
<h2>The Tcl interface to the SQLite library</h2>

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

<h3>The API</h3>



|
|

>
|







1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
<title>The Tcl interface to the SQLite library</title>
<tcl>
proc METHOD {name text} {
  hd_puts "<a name=\"$name\"></a>\n<h3>The \"$name\" method</h3>\n"
  hd_puts $text
}
</tcl>

<h2>The Tcl interface to the SQLite library</h2>

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

<h3>The API</h3>
45
46
47
48
49
50
51
52

53
54
55
56
57
58
59
<p>
Once an SQLite database is open, it can be controlled using 
methods of the <i>dbcmd</i>.  There are currently 22 methods
defined.</p>

<p>
<ul>
}

foreach m [lsort {
 authorizer
 busy
 cache
 changes
 close
 collate







|
>







46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
<p>
Once an SQLite database is open, it can be controlled using 
methods of the <i>dbcmd</i>.  There are currently 22 methods
defined.</p>

<p>
<ul>

<tcl>
foreach m [lsort {
 authorizer
 busy
 cache
 changes
 close
 collate
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
 timeout
 total_changes
 trace
 transaction
 update_hook
 version
}] {
 puts "<li><a href=\"#$m\">$m</a></li>"
}
puts {
</ul>
</p>

<p>The use of each of these methods will be explained in the sequel, though
not in the order shown above.</p>

}

##############################################################################
METHOD eval {
<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>








|

|






<
|







77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92

93
94
95
96
97
98
99
100
 timeout
 total_changes
 trace
 transaction
 update_hook
 version
}] {
 hd_puts "<li><a href=\"#$m\">$m</a></li>"
}
</tcl>
</ul>
</p>

<p>The use of each of these methods will be explained in the sequel, though
not in the order shown above.</p>


<tcl>
##############################################################################
METHOD eval {
<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>

Changes to pages/vdbe.in.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
<title>The Virtual Database Engine of SQLite</title>
<tcl>
puts {
<h2>The Virtual Database Engine of SQLite</h2>

<blockquote><b>
This document describes the virtual machine used in SQLite version 2.8.0. 
The virtual machine in SQLite version 3.0 and 3.1 is very similar in
concept but many of the opcodes have changed and the algorithms are
somewhat different.  Use this document as a rough guide to the idea
behind the virtual machine in SQLite version 3, not as a reference on
how the virtual machine works.
</b></blockquote>
}

puts {
<p>If you want to know how the SQLite library works internally,
you need to begin with a solid understanding of the Virtual Database
Engine or VDBE.  The VDBE occurs right in the middle of the
processing stream (see the <a href="arch.html">architecture diagram</a>)
and so it seems to touch most parts of the library.  Even
parts of the code that do not directly interact with the VDBE
are usually in a supporting role.  The VDBE really is the heart of


|












|







1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
<title>The Virtual Database Engine of SQLite</title>
<tcl>
hd_puts {
<h2>The Virtual Database Engine of SQLite</h2>

<blockquote><b>
This document describes the virtual machine used in SQLite version 2.8.0. 
The virtual machine in SQLite version 3.0 and 3.1 is very similar in
concept but many of the opcodes have changed and the algorithms are
somewhat different.  Use this document as a rough guide to the idea
behind the virtual machine in SQLite version 3, not as a reference on
how the virtual machine works.
</b></blockquote>
}

hd_puts {
<p>If you want to know how the SQLite library works internally,
you need to begin with a solid understanding of the Virtual Database
Engine or VDBE.  The VDBE occurs right in the middle of the
processing stream (see the <a href="arch.html">architecture diagram</a>)
and so it seems to touch most parts of the library.  Even
parts of the code that do not directly interact with the VDBE
are usually in a supporting role.  The VDBE really is the heart of
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
".explain" command.
Finally, enter the INSERT statement shown above, but precede the
INSERT with the special keyword "EXPLAIN".  The EXPLAIN keyword
will cause <b>sqlite</b> to print the VDBE program rather than 
execute it.  We have:</p>
}
proc Code {body} {
  puts {<blockquote><tt>}
  regsub -all {&} [string trim $body] {\&amp;} body
  regsub -all {>} $body {\&gt;} body
  regsub -all {<} $body {\&lt;} body
  regsub -all {\(\(\(} $body {<b>} body
  regsub -all {\)\)\)} $body {</b>} body
  regsub -all { } $body {\&nbsp;} body
  regsub -all \n $body <br>\n body
  puts $body
  puts {</tt></blockquote>}
}

Code {
$ (((sqlite test_database_1)))
sqlite> (((CREATE TABLE examp(one text, two int);)))
sqlite> (((.explain)))
sqlite> (((EXPLAIN INSERT INTO examp VALUES('Hello, World!',99);)))







|







|
|







94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
".explain" command.
Finally, enter the INSERT statement shown above, but precede the
INSERT with the special keyword "EXPLAIN".  The EXPLAIN keyword
will cause <b>sqlite</b> to print the VDBE program rather than 
execute it.  We have:</p>
}
proc Code {body} {
  hd_puts {<blockquote><tt>}
  regsub -all {&} [string trim $body] {\&amp;} body
  regsub -all {>} $body {\&gt;} body
  regsub -all {<} $body {\&lt;} body
  regsub -all {\(\(\(} $body {<b>} body
  regsub -all {\)\)\)} $body {</b>} body
  regsub -all { } $body {\&nbsp;} body
  regsub -all \n $body <br>\n body
  hd_puts $body
  hd_puts {</tt></blockquote>}
}

Code {
$ (((sqlite test_database_1)))
sqlite> (((CREATE TABLE examp(one text, two int);)))
sqlite> (((.explain)))
sqlite> (((EXPLAIN INSERT INTO examp VALUES('Hello, World!',99);)))
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
8     MakeRecord    2      0                                         
9     PutIntKey     0      1                                         
10    Close         0      0                                         
11    Commit        0      0                                         
12    Halt          0      0                                         
}

puts {<p>As you can see above, our simple insert statement is
implemented in 12 instructions.  The first 3 and last 2 instructions are 
a standard prologue and epilogue, so the real work is done in the middle 
7 instructions.  There are no jumps, so the program executes once through 
from top to bottom.  Let's now look at each instruction in detail.<p>
}

Code {
0     Transaction   0      0                                         
1     VerifyCookie  0      81                                        
2     Transaction   1      0                                         
}
puts {
<p>The instruction <a href="opcode.html#Transaction">Transaction</a> 
begins a transaction.  The transaction ends when a Commit or Rollback 
opcode is encountered.  P1 is the index of the database file on which 
the transaction is started.  Index 0 is the main database file.  A write 
lock is obtained on the database file when a transaction is started.  
No other process can read or write the file while the transaction is 
underway.  Starting a transaction also creates a rollback journal.  A 







|











|







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
8     MakeRecord    2      0                                         
9     PutIntKey     0      1                                         
10    Close         0      0                                         
11    Commit        0      0                                         
12    Halt          0      0                                         
}

hd_puts {<p>As you can see above, our simple insert statement is
implemented in 12 instructions.  The first 3 and last 2 instructions are 
a standard prologue and epilogue, so the real work is done in the middle 
7 instructions.  There are no jumps, so the program executes once through 
from top to bottom.  Let's now look at each instruction in detail.<p>
}

Code {
0     Transaction   0      0                                         
1     VerifyCookie  0      81                                        
2     Transaction   1      0                                         
}
hd_puts {
<p>The instruction <a href="opcode.html#Transaction">Transaction</a> 
begins a transaction.  The transaction ends when a Commit or Rollback 
opcode is encountered.  P1 is the index of the database file on which 
the transaction is started.  Index 0 is the main database file.  A write 
lock is obtained on the database file when a transaction is started.  
No other process can read or write the file while the transaction is 
underway.  Starting a transaction also creates a rollback journal.  A 
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
207
208
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
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
300

<p> The second <a href="opcode.html#Transaction">Transaction</a> 
instruction begins a transaction and starts a rollback journal for 
database 1, the database used for temporary tables.</p>
}

proc stack args {
  puts "<blockquote><table border=2>"
  foreach elem $args {
    puts "<tr><td align=left>$elem</td></tr>"
  }
  puts "</table></blockquote>"
}

Code {
3     Integer       0      0                                    
4     OpenWrite     0      3      examp                         
}
puts {
<p> The instruction <a href="opcode.html#Integer">Integer</a> pushes 
the integer value P1 (0) onto the stack.  Here 0 is the number of the 
database to use in the following OpenWrite instruction.  If P3 is not 
NULL then it is a string representation of the same integer.  Afterwards 
the stack looks like this:</p>
}
stack {(integer) 0}

puts {
<p> The instruction <a href="opcode.html#OpenWrite">OpenWrite</a> opens 
a new read/write cursor with handle P1 (0 in this case) on table "examp", 
whose root page is P2 (3, in this database file).  Cursor handles can be 
any non-negative integer.  But the VDBE allocates cursors in an array 
with the size of the array being one more than the largest cursor.  So 
to conserve memory, it is best to use handles beginning with zero and 
working upward consecutively.  Here P3 ("examp") is the name of the 
table being opened, but this is unused, and only generated to make the 
code easier to read.  This instruction pops the database number to use 
(0, the main database) from the top of the stack, so afterwards the 
stack is empty again.</p>
}

Code {
5     NewRecno      0      0                                    
}
puts {
<p> The instruction <a href="opcode.html#NewRecno">NewRecno</a> creates 
a new integer record number for the table pointed to by cursor P1.  The 
record number is one not currently used as a key in the table.  The new 
record number is pushed onto the stack.  Afterwards the stack looks like 
this:</p>
}
stack {(integer) new record key}

Code {
6     String        0      0      Hello, World!                 
}
puts {
<p> The instruction <a href="opcode.html#String">String</a> pushes its 
P3 operand onto the stack.  Afterwards the stack looks like this:</p>
}
stack {(string) "Hello, World!"} \
 {(integer) new record key}

Code {
7     Integer       99     0      99                            
}
puts {
<p> The instruction <a href="opcode.html#Integer">Integer</a> pushes 
its P1 operand (99) onto the stack.  Afterwards the stack looks like 
this:</p>
}
stack {(integer) 99} \
 {(string) "Hello, World!"} \
 {(integer) new record key}

Code {
8     MakeRecord    2      0                                    
}
puts {
<p> The instruction <a href="opcode.html#MakeRecord">MakeRecord</a> pops 
the top P1 elements off the stack (2 in this case) and converts them into 
the binary format used for storing records in a database file.  
(See the <a href="fileformat.html">file format</a> description for 
details.)  The new record generated by the MakeRecord instruction is 
pushed back onto the stack.  Afterwards the stack looks like this:</p>
</ul>
}
stack {(record) "Hello, World!", 99} \
 {(integer) new record key}

Code {
9     PutIntKey     0      1                                    
}
puts {
<p> The instruction <a href="opcode.html#PutIntKey">PutIntKey</a> uses 
the top 2 stack entries to write an entry into the table pointed to by 
cursor P1.  A new entry is created if it doesn't already exist or the 
data for an existing entry is overwritten.  The record data is the top 
stack entry, and the key is the next entry down.  The stack is popped 
twice by this instruction.  Because operand P2 is 1 the row change count 
is incremented and the rowid is stored for subsequent return by the 
sqlite_last_insert_rowid() function.  If P2 is 0 the row change count is 
unmodified.  This instruction is where the insert actually occurs.</p>
}

Code {
10    Close         0      0                                         
}
puts {
<p> The instruction <a href="opcode.html#Close">Close</a> closes a 
cursor previously opened as P1 (0, the only open cursor). If P1 is not 
currently open, this instruction is a no-op.</p>
}

Code {
11    Commit        0      0                                         
}
puts {
<p> The instruction <a href="opcode.html#Commit">Commit</a> causes all 
modifications to the database that have been made since the last 
Transaction to actually take effect.  No additional modifications are 
allowed until another transaction is started.  The Commit instruction 
deletes the journal file and releases the write lock on the database.  
A read lock continues to be held if there are still cursors open.</p>
}

Code {
12    Halt          0      0                                         
}
puts {
<p> The instruction <a href="opcode.html#Halt">Halt</a> causes the VDBE 
engine to exit immediately.  All open cursors, Lists, Sorts, etc are 
closed automatically.  P1 is the result code returned by sqlite_exec().  
For a normal halt, this should be SQLITE_OK (0).  For errors, it can be 
some other value.  The operand P2 is only used when there is an error.  
There is an implied "Halt 0 0 0" instruction at the end of every 
program, which the VDBE appends when it prepares a program to run.</p>







|

|

|






|








|
















|











|









|











|














|














|








|











|







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
207
208
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
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
300

<p> The second <a href="opcode.html#Transaction">Transaction</a> 
instruction begins a transaction and starts a rollback journal for 
database 1, the database used for temporary tables.</p>
}

proc stack args {
  hd_puts "<blockquote><table border=2>"
  foreach elem $args {
    hd_puts "<tr><td align=left>$elem</td></tr>"
  }
  hd_puts "</table></blockquote>"
}

Code {
3     Integer       0      0                                    
4     OpenWrite     0      3      examp                         
}
hd_puts {
<p> The instruction <a href="opcode.html#Integer">Integer</a> pushes 
the integer value P1 (0) onto the stack.  Here 0 is the number of the 
database to use in the following OpenWrite instruction.  If P3 is not 
NULL then it is a string representation of the same integer.  Afterwards 
the stack looks like this:</p>
}
stack {(integer) 0}

hd_puts {
<p> The instruction <a href="opcode.html#OpenWrite">OpenWrite</a> opens 
a new read/write cursor with handle P1 (0 in this case) on table "examp", 
whose root page is P2 (3, in this database file).  Cursor handles can be 
any non-negative integer.  But the VDBE allocates cursors in an array 
with the size of the array being one more than the largest cursor.  So 
to conserve memory, it is best to use handles beginning with zero and 
working upward consecutively.  Here P3 ("examp") is the name of the 
table being opened, but this is unused, and only generated to make the 
code easier to read.  This instruction pops the database number to use 
(0, the main database) from the top of the stack, so afterwards the 
stack is empty again.</p>
}

Code {
5     NewRecno      0      0                                    
}
hd_puts {
<p> The instruction <a href="opcode.html#NewRecno">NewRecno</a> creates 
a new integer record number for the table pointed to by cursor P1.  The 
record number is one not currently used as a key in the table.  The new 
record number is pushed onto the stack.  Afterwards the stack looks like 
this:</p>
}
stack {(integer) new record key}

Code {
6     String        0      0      Hello, World!                 
}
hd_puts {
<p> The instruction <a href="opcode.html#String">String</a> pushes its 
P3 operand onto the stack.  Afterwards the stack looks like this:</p>
}
stack {(string) "Hello, World!"} \
 {(integer) new record key}

Code {
7     Integer       99     0      99                            
}
hd_puts {
<p> The instruction <a href="opcode.html#Integer">Integer</a> pushes 
its P1 operand (99) onto the stack.  Afterwards the stack looks like 
this:</p>
}
stack {(integer) 99} \
 {(string) "Hello, World!"} \
 {(integer) new record key}

Code {
8     MakeRecord    2      0                                    
}
hd_puts {
<p> The instruction <a href="opcode.html#MakeRecord">MakeRecord</a> pops 
the top P1 elements off the stack (2 in this case) and converts them into 
the binary format used for storing records in a database file.  
(See the <a href="fileformat.html">file format</a> description for 
details.)  The new record generated by the MakeRecord instruction is 
pushed back onto the stack.  Afterwards the stack looks like this:</p>
</ul>
}
stack {(record) "Hello, World!", 99} \
 {(integer) new record key}

Code {
9     PutIntKey     0      1                                    
}
hd_puts {
<p> The instruction <a href="opcode.html#PutIntKey">PutIntKey</a> uses 
the top 2 stack entries to write an entry into the table pointed to by 
cursor P1.  A new entry is created if it doesn't already exist or the 
data for an existing entry is overwritten.  The record data is the top 
stack entry, and the key is the next entry down.  The stack is popped 
twice by this instruction.  Because operand P2 is 1 the row change count 
is incremented and the rowid is stored for subsequent return by the 
sqlite_last_insert_rowid() function.  If P2 is 0 the row change count is 
unmodified.  This instruction is where the insert actually occurs.</p>
}

Code {
10    Close         0      0                                         
}
hd_puts {
<p> The instruction <a href="opcode.html#Close">Close</a> closes a 
cursor previously opened as P1 (0, the only open cursor). If P1 is not 
currently open, this instruction is a no-op.</p>
}

Code {
11    Commit        0      0                                         
}
hd_puts {
<p> The instruction <a href="opcode.html#Commit">Commit</a> causes all 
modifications to the database that have been made since the last 
Transaction to actually take effect.  No additional modifications are 
allowed until another transaction is started.  The Commit instruction 
deletes the journal file and releases the write lock on the database.  
A read lock continues to be held if there are still cursors open.</p>
}

Code {
12    Halt          0      0                                         
}
hd_puts {
<p> The instruction <a href="opcode.html#Halt">Halt</a> causes the VDBE 
engine to exit immediately.  All open cursors, Lists, Sorts, etc are 
closed automatically.  P1 is the result code returned by sqlite_exec().  
For a normal halt, this should be SQLITE_OK (0).  For errors, it can be 
some other value.  The operand P2 is only used when there is an error.  
There is an implied "Halt 0 0 0" instruction at the end of every 
program, which the VDBE appends when it prepares a program to run.</p>
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
Stack: s[...Hello,.World!.99] i:2
   9 PutIntKey       0    1
  10 Close           0    0
  11 Commit          0    0
  12 Halt            0    0
}

puts {
<p>With tracing mode on, the VDBE prints each instruction prior
to executing it.  After the instruction is executed, the top few
entries in the stack are displayed.  The stack display is omitted
if the stack is empty.</p>

<p>On the stack display, most entries are shown with a prefix
that tells the datatype of that stack entry.  Integers begin







|







333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
Stack: s[...Hello,.World!.99] i:2
   9 PutIntKey       0    1
  10 Close           0    0
  11 Commit          0    0
  12 Halt            0    0
}

hd_puts {
<p>With tracing mode on, the VDBE prints each instruction prior
to executing it.  After the instruction is executed, the top few
entries in the stack are displayed.  The stack display is omitted
if the stack is empty.</p>

<p>On the stack display, most entries are shown with a prefix
that tells the datatype of that stack entry.  Integers begin
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
7     Column        0      1                                         
8     Callback      2      0                                         
9     Next          0      6                                         
10    Close         0      0                                         
11    Halt          0      0                                         
}

puts {
<p>Before we begin looking at this problem, let's briefly review
how queries work in SQLite so that we will know what we are trying
to accomplish.  For each row in the result of a query,
SQLite will invoke a callback function with the following
prototype:</p>

<blockquote><pre>







|







392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
7     Column        0      1                                         
8     Callback      2      0                                         
9     Next          0      6                                         
10    Close         0      0                                         
11    Halt          0      0                                         
}

hd_puts {
<p>Before we begin looking at this problem, let's briefly review
how queries work in SQLite so that we will know what we are trying
to accomplish.  For each row in the result of a query,
SQLite will invoke a callback function with the following
prototype:</p>

<blockquote><pre>
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
the actual data.</p>
}

Code {
0     ColumnName    0      0      one                                
1     ColumnName    1      0      two                                
}
puts {
<p>The first two instructions in the VDBE program for our query are
concerned with setting up values for <b>azColumn</b>.
The <a href="opcode.html#ColumnName">ColumnName</a> instructions tell 
the VDBE what values to fill in for each element of the <b>azColumnName[]</b> 
array.  Every query will begin with one ColumnName instruction for each 
column in the result, and there will be a matching Column instruction for 
each one later in the query.
</p>
}

Code {
2     Integer       0      0                                         
3     OpenRead      0      3      examp                              
4     VerifyCookie  0      81                                        
}
puts {
<p>Instructions 2 and 3 open a read cursor on the database table that is 
to be queried.  This works the same as the OpenWrite instruction in the 
INSERT example except that the cursor is opened for reading this time 
instead of for writing.  Instruction 4 verifies the database schema as 
in the INSERT example.</p>
}

Code {
5     Rewind        0      10                                        
}
puts {
<p> The <a href="opcode.html#Rewind">Rewind</a> instruction initializes 
a loop that iterates over the "examp" table. It rewinds the cursor P1 
to the first entry in its table.  This is required by the the Column and 
Next instructions, which use the cursor to iterate through the table.  
If the table is empty, then jump to P2 (10), which is the instruction just 
past the loop.  If the table is not empty, fall through to the following 
instruction at 6, which is the beginning of the loop body.</p>
}

Code {
6     Column        0      0                                         
7     Column        0      1                                         
8     Callback      2      0                                         
}
puts {
<p> The instructions 6 through 8 form the body of the loop that will 
execute once for each record in the database file.  

The <a href="opcode.html#Column">Column</a> instructions at addresses 6 
and 7 each take the P2-th column from the P1-th cursor and push it onto 
the stack.  In this example, the first Column instruction is pushing the 
value for the column "one" onto the stack and the second Column 
instruction is pushing the value for column "two".  

The <a href="opcode.html#Callback">Callback</a> instruction at address 8 
invokes the callback() function.  The P1 operand to Callback becomes the 
value for <b>nColumn</b>.  The Callback instruction pops P1 values from
the stack and uses them to fill the <b>azData[]</b> array.</p>
}

Code {
9     Next          0      6                                              
}
puts {
<p>The instruction at address 9 implements the branching part of the 
loop.  Together with the Rewind at address 5 it forms the loop logic.  
This is a key concept that you should pay close attention to.   
The <a href="opcode.html#Next">Next</a> instruction advances the cursor 
P1 to the next record.  If the cursor advance was successful, then jump 
immediately to P2 (6, the beginning of the loop body).  If the cursor 
was at the end, then fall through to the following instruction, which 
ends the loop.</p>
}

Code {
10    Close         0      0                                         
11    Halt          0      0                                         
}
puts {
<p>The Close instruction at the end of the program closes the
cursor that points into the table "examp".  It is not really necessary
to call Close here since all cursors will be automatically closed
by the VDBE when the program halts.  But we needed an instruction
for the Rewind to jump to so we might as well go ahead and have that
instruction do something useful.
The Halt instruction ends the VDBE program.</p>

<p>Note that the program for this SELECT query didn't contain the 
Transaction and Commit instructions used in the INSERT example.  Because 
the SELECT is a read operation that doesn't alter the database, it 
doesn't require a transaction.</p>
}


puts {
<a name="query2"></a>
<h2>A Slightly More Complex Query</h2>

<p>The key points of the previous example were the use of the Callback
instruction to invoke the callback function, and the use of the Next
instruction to implement a loop over all records of the database file.
This example attempts to drive home those ideas by demonstrating a







|















|










|














|


















|














|















|







419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
the actual data.</p>
}

Code {
0     ColumnName    0      0      one                                
1     ColumnName    1      0      two                                
}
hd_puts {
<p>The first two instructions in the VDBE program for our query are
concerned with setting up values for <b>azColumn</b>.
The <a href="opcode.html#ColumnName">ColumnName</a> instructions tell 
the VDBE what values to fill in for each element of the <b>azColumnName[]</b> 
array.  Every query will begin with one ColumnName instruction for each 
column in the result, and there will be a matching Column instruction for 
each one later in the query.
</p>
}

Code {
2     Integer       0      0                                         
3     OpenRead      0      3      examp                              
4     VerifyCookie  0      81                                        
}
hd_puts {
<p>Instructions 2 and 3 open a read cursor on the database table that is 
to be queried.  This works the same as the OpenWrite instruction in the 
INSERT example except that the cursor is opened for reading this time 
instead of for writing.  Instruction 4 verifies the database schema as 
in the INSERT example.</p>
}

Code {
5     Rewind        0      10                                        
}
hd_puts {
<p> The <a href="opcode.html#Rewind">Rewind</a> instruction initializes 
a loop that iterates over the "examp" table. It rewinds the cursor P1 
to the first entry in its table.  This is required by the the Column and 
Next instructions, which use the cursor to iterate through the table.  
If the table is empty, then jump to P2 (10), which is the instruction just 
past the loop.  If the table is not empty, fall through to the following 
instruction at 6, which is the beginning of the loop body.</p>
}

Code {
6     Column        0      0                                         
7     Column        0      1                                         
8     Callback      2      0                                         
}
hd_puts {
<p> The instructions 6 through 8 form the body of the loop that will 
execute once for each record in the database file.  

The <a href="opcode.html#Column">Column</a> instructions at addresses 6 
and 7 each take the P2-th column from the P1-th cursor and push it onto 
the stack.  In this example, the first Column instruction is pushing the 
value for the column "one" onto the stack and the second Column 
instruction is pushing the value for column "two".  

The <a href="opcode.html#Callback">Callback</a> instruction at address 8 
invokes the callback() function.  The P1 operand to Callback becomes the 
value for <b>nColumn</b>.  The Callback instruction pops P1 values from
the stack and uses them to fill the <b>azData[]</b> array.</p>
}

Code {
9     Next          0      6                                              
}
hd_puts {
<p>The instruction at address 9 implements the branching part of the 
loop.  Together with the Rewind at address 5 it forms the loop logic.  
This is a key concept that you should pay close attention to.   
The <a href="opcode.html#Next">Next</a> instruction advances the cursor 
P1 to the next record.  If the cursor advance was successful, then jump 
immediately to P2 (6, the beginning of the loop body).  If the cursor 
was at the end, then fall through to the following instruction, which 
ends the loop.</p>
}

Code {
10    Close         0      0                                         
11    Halt          0      0                                         
}
hd_puts {
<p>The Close instruction at the end of the program closes the
cursor that points into the table "examp".  It is not really necessary
to call Close here since all cursors will be automatically closed
by the VDBE when the program halts.  But we needed an instruction
for the Rewind to jump to so we might as well go ahead and have that
instruction do something useful.
The Halt instruction ends the VDBE program.</p>

<p>Note that the program for this SELECT query didn't contain the 
Transaction and Commit instructions used in the INSERT example.  Because 
the SELECT is a read operation that doesn't alter the database, it 
doesn't require a transaction.</p>
}


hd_puts {
<a name="query2"></a>
<h2>A Slightly More Complex Query</h2>

<p>The key points of the previous example were the use of the Callback
instruction to invoke the callback function, and the use of the Next
instruction to implement a loop over all records of the database file.
This example attempts to drive home those ideas by demonstrating a
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
15    Concat        2      0
16    Callback      3      0
17    Next          0      7
18    Close         0      0
19    Halt          0      0
}

puts {
<p>Except for the WHERE clause, the structure of the program for
this example is very much like the prior example, just with an
extra column.  There are now 3 columns, instead of 2 as before,
and there are three ColumnName instructions.
A cursor is opened using the OpenRead instruction, just like in the
prior example.  The Rewind instruction at address 6 and the
Next at address 17 form a loop over all records of the table.  







|







567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
15    Concat        2      0
16    Callback      3      0
17    Next          0      7
18    Close         0      0
19    Halt          0      0
}

hd_puts {
<p>Except for the WHERE clause, the structure of the program for
this example is very much like the prior example, just with an
extra column.  There are now 3 columns, instead of 2 as before,
and there are three ColumnName instructions.
A cursor is opened using the OpenRead instruction, just like in the
prior example.  The Rewind instruction at address 6 and the
Next at address 17 form a loop over all records of the table.  
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
19    Goto          0      16
20    ListReset     0      0
21    Close         0      0
22    Commit        0      0
23    Halt          0      0
}

puts {
<p>Here is what the program must do.  First it has to locate all of
the records in the table "examp" that are to be deleted.  This is
done using a loop very much like the loop used in the SELECT examples
above.  Once all records have been located, then we can go back through
and delete them one by one.  Note that we cannot delete each record
as soon as we find it.  We have to locate all records first, then
go back and delete them.  This is because the SQLite database







|







691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
19    Goto          0      16
20    ListReset     0      0
21    Close         0      0
22    Commit        0      0
23    Halt          0      0
}

hd_puts {
<p>Here is what the program must do.  First it has to locate all of
the records in the table "examp" that are to be deleted.  This is
done using a loop very much like the loop used in the SELECT examples
above.  Once all records have been located, then we can go back through
and delete them one by one.  Note that we cannot delete each record
as soon as we find it.  We have to locate all records first, then
go back and delete them.  This is because the SQLite database
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
Code {
0     Transaction   1      0
1     Transaction   0      0
2     VerifyCookie  0      178
3     Integer       0      0
4     OpenRead      0      3      examp
}
puts {
<p>Instructions 0 though 4 are as in the INSERT example.  They start 
transactions for the main and temporary databases, verify the database 
schema for the main database, and open a read cursor on the table 
"examp".  Notice that the cursor is opened for reading, not writing.  At 
this stage of the program we are only going to be scanning the table, 
not changing it.  We will reopen the same table for writing later, at 
instruction 15.</p>
}

Code {
5     Rewind        0      12
}
puts {
<p>As in the SELECT example, the <a href="opcode.html#Rewind">Rewind</a> 
instruction rewinds the cursor to the beginning of the table, readying 
it for use in the loop body.</p>
}

Code {
6     Column        0      1
7     Integer       50     0      50
8     Ge            1      11
}
puts {
<p>The WHERE clause is implemented by instructions 6 through 8.
The job of the where clause is to skip the ListWrite if the WHERE
condition is false.  To this end, it jumps ahead to the Next instruction
if the "two" column (extracted by the Column instruction) is
greater than or equal to 50.</p>

<p>As before, the Column instruction uses cursor P1 and pushes the data 
record in column P2 (1, column "two") onto the stack.  The Integer 
instruction pushes the value 50 onto the top of the stack.  After these 
two instructions the stack looks like:</p>
}
stack {(integer) 50} \
  {(record) current record for column "two" }

puts {
<p>The <a href="opcode.html#Ge">Ge</a> operator compares the top two 
elements on the stack, pops them, and then branches based on the result 
of the comparison.  If the second element is >= the top element, then 
jump to address P2 (the Next instruction at the end of the loop).  
Because P1 is true, if either operand is NULL (and thus the result is 
NULL) then take the jump.  If we don't jump, just advance to the next 
instruction.</p>
}

Code {
9     Recno         0      0
10    ListWrite     0      0
}
puts {
<p>The <a href="opcode.html#Recno">Recno</a> instruction pushes onto the 
stack an integer which is the first 4 bytes of the the key to the current 
entry in a sequential scan of the table pointed to by cursor P1.
The <a href="opcode.html#ListWrite">ListWrite</a> instruction writes the 
integer on the top of the stack into a temporary storage list and pops 
the top element.  This is the important work of this loop, to store the 
keys of the records to be deleted so we can delete them in the second 
loop.  After this ListWrite instruction the stack is empty again.</p>
}

Code {
11    Next          0      6
12    Close         0      0
}
puts {
<p> The Next instruction increments the cursor to point to the next 
element in the table pointed to by cursor P0, and if it was successful 
branches to P2 (6, the beginning of the loop body).  The Close 
instruction closes cursor P1.  It doesn't affect the temporary storage 
list because it isn't associated with cursor P1; it is instead a global 
working list (which can be saved with ListPush).</p>
}

Code {
13    ListRewind    0      0
}
puts {
<p> The <a href="opcode.html#ListRewind">ListRewind</a> instruction 
rewinds the temporary storage list to the beginning.  This prepares it 
for use in the second loop.</p>
}

Code {
14    Integer       0      0
15    OpenWrite     0      3
}
puts {
<p> As in the INSERT example, we push the database number P1 (0, the main 
database) onto the stack and use OpenWrite to open the cursor P1 on table 
P2 (base page 3, "examp") for modification.</p>
}

Code {
16    ListRead      0      20
17    NotExists     0      19
18    Delete        0      1
19    Goto          0      16
}
puts {
<p>This loop does the actual deleting.  It is organized differently from 
the one in the UPDATE example.  The ListRead instruction plays the role 
that the Next did in the INSERT loop, but because it jumps to P2 on 
failure, and Next jumps on success, we put it at the start of the loop 
instead of the end.  This means that we have to put a Goto at the end of 
the loop to jump back to the the loop test at the beginning.  So this 
loop has the form of a C while(){...} loop, while the loop in the INSERT 
example had the form of a do{...}while() loop.  The Delete instruction 
fills the role that the callback function did in the preceding examples.
</p>
<p>The <a href="opcode.html#ListRead">ListRead</a> instruction reads an 
element from the temporary storage list and pushes it onto the stack.  
If this was successful, it continues to the next instruction.  If this 
fails because the list is empty, it branches to P2, which is the 
instruction just after the loop.  Afterwards the stack looks like:</p>
}
stack {(integer) key for current record}

puts {
<p>Notice the similarity between the ListRead and Next instructions.  
Both operations work according to this rule:
</p>
<blockquote>
Push the next "thing" onto the stack and fall through OR jump to P2, 
depending on whether or not there is a next "thing" to push.
</blockquote>







|












|










|














|













|














|











|









|











|


















|







719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
Code {
0     Transaction   1      0
1     Transaction   0      0
2     VerifyCookie  0      178
3     Integer       0      0
4     OpenRead      0      3      examp
}
hd_puts {
<p>Instructions 0 though 4 are as in the INSERT example.  They start 
transactions for the main and temporary databases, verify the database 
schema for the main database, and open a read cursor on the table 
"examp".  Notice that the cursor is opened for reading, not writing.  At 
this stage of the program we are only going to be scanning the table, 
not changing it.  We will reopen the same table for writing later, at 
instruction 15.</p>
}

Code {
5     Rewind        0      12
}
hd_puts {
<p>As in the SELECT example, the <a href="opcode.html#Rewind">Rewind</a> 
instruction rewinds the cursor to the beginning of the table, readying 
it for use in the loop body.</p>
}

Code {
6     Column        0      1
7     Integer       50     0      50
8     Ge            1      11
}
hd_puts {
<p>The WHERE clause is implemented by instructions 6 through 8.
The job of the where clause is to skip the ListWrite if the WHERE
condition is false.  To this end, it jumps ahead to the Next instruction
if the "two" column (extracted by the Column instruction) is
greater than or equal to 50.</p>

<p>As before, the Column instruction uses cursor P1 and pushes the data 
record in column P2 (1, column "two") onto the stack.  The Integer 
instruction pushes the value 50 onto the top of the stack.  After these 
two instructions the stack looks like:</p>
}
stack {(integer) 50} \
  {(record) current record for column "two" }

hd_puts {
<p>The <a href="opcode.html#Ge">Ge</a> operator compares the top two 
elements on the stack, pops them, and then branches based on the result 
of the comparison.  If the second element is >= the top element, then 
jump to address P2 (the Next instruction at the end of the loop).  
Because P1 is true, if either operand is NULL (and thus the result is 
NULL) then take the jump.  If we don't jump, just advance to the next 
instruction.</p>
}

Code {
9     Recno         0      0
10    ListWrite     0      0
}
hd_puts {
<p>The <a href="opcode.html#Recno">Recno</a> instruction pushes onto the 
stack an integer which is the first 4 bytes of the the key to the current 
entry in a sequential scan of the table pointed to by cursor P1.
The <a href="opcode.html#ListWrite">ListWrite</a> instruction writes the 
integer on the top of the stack into a temporary storage list and pops 
the top element.  This is the important work of this loop, to store the 
keys of the records to be deleted so we can delete them in the second 
loop.  After this ListWrite instruction the stack is empty again.</p>
}

Code {
11    Next          0      6
12    Close         0      0
}
hd_puts {
<p> The Next instruction increments the cursor to point to the next 
element in the table pointed to by cursor P0, and if it was successful 
branches to P2 (6, the beginning of the loop body).  The Close 
instruction closes cursor P1.  It doesn't affect the temporary storage 
list because it isn't associated with cursor P1; it is instead a global 
working list (which can be saved with ListPush).</p>
}

Code {
13    ListRewind    0      0
}
hd_puts {
<p> The <a href="opcode.html#ListRewind">ListRewind</a> instruction 
rewinds the temporary storage list to the beginning.  This prepares it 
for use in the second loop.</p>
}

Code {
14    Integer       0      0
15    OpenWrite     0      3
}
hd_puts {
<p> As in the INSERT example, we push the database number P1 (0, the main 
database) onto the stack and use OpenWrite to open the cursor P1 on table 
P2 (base page 3, "examp") for modification.</p>
}

Code {
16    ListRead      0      20
17    NotExists     0      19
18    Delete        0      1
19    Goto          0      16
}
hd_puts {
<p>This loop does the actual deleting.  It is organized differently from 
the one in the UPDATE example.  The ListRead instruction plays the role 
that the Next did in the INSERT loop, but because it jumps to P2 on 
failure, and Next jumps on success, we put it at the start of the loop 
instead of the end.  This means that we have to put a Goto at the end of 
the loop to jump back to the the loop test at the beginning.  So this 
loop has the form of a C while(){...} loop, while the loop in the INSERT 
example had the form of a do{...}while() loop.  The Delete instruction 
fills the role that the callback function did in the preceding examples.
</p>
<p>The <a href="opcode.html#ListRead">ListRead</a> instruction reads an 
element from the temporary storage list and pushes it onto the stack.  
If this was successful, it continues to the next instruction.  If this 
fails because the list is empty, it branches to P2, which is the 
instruction just after the loop.  Afterwards the stack looks like:</p>
}
stack {(integer) key for current record}

hd_puts {
<p>Notice the similarity between the ListRead and Next instructions.  
Both operations work according to this rule:
</p>
<blockquote>
Push the next "thing" onto the stack and fall through OR jump to P2, 
depending on whether or not there is a next "thing" to push.
</blockquote>
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892

Code {
20    ListReset     0      0
21    Close         0      0
22    Commit        0      0
23    Halt          0      0
}
puts {
<p>This block of instruction cleans up the VDBE program. Three of these 
instructions aren't really required, but are generated by the SQLite 
parser from its code templates, which are designed to handle more 
complicated cases.</p>
<p>The <a href="opcode.html#ListReset">ListReset</a> instruction empties 
the temporary storage list.  This list is emptied automatically when the 
VDBE program terminates, so it isn't necessary in this case.  The Close 







|







878
879
880
881
882
883
884
885
886
887
888
889
890
891
892

Code {
20    ListReset     0      0
21    Close         0      0
22    Commit        0      0
23    Halt          0      0
}
hd_puts {
<p>This block of instruction cleans up the VDBE program. Three of these 
instructions aren't really required, but are generated by the SQLite 
parser from its code templates, which are designed to handle more 
complicated cases.</p>
<p>The <a href="opcode.html#ListReset">ListReset</a> instruction empties 
the temporary storage list.  This list is emptied automatically when the 
VDBE program terminates, so it isn't necessary in this case.  The Close 
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
27    Goto          0      16                                             
28    ListReset     0      0                                         
29    Close         0      0                                         
30    Commit        0      0                                         
31    Halt          0      0                                         
}

puts {
<p>This program is essentially the same as the DELETE program except 
that the body of the second loop has been replace by a sequence of 
instructions (at addresses 17 through 26) that update the record rather 
than delete it.  Most of this instruction sequence should already be 
familiar to you, but there are a couple of minor twists so we will go 
over it briefly.  Also note that the order of some of the instructions 
before and after the 2nd loop has changed.  This is just the way the 







|







945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
27    Goto          0      16                                             
28    ListReset     0      0                                         
29    Close         0      0                                         
30    Commit        0      0                                         
31    Halt          0      0                                         
}

hd_puts {
<p>This program is essentially the same as the DELETE program except 
that the body of the second loop has been replace by a sequence of 
instructions (at addresses 17 through 26) that update the record rather 
than delete it.  Most of this instruction sequence should already be 
familiar to you, but there are a couple of minor twists so we will go 
over it briefly.  Also note that the order of some of the instructions 
before and after the 2nd loop has changed.  This is just the way the 
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
the same kind of code that we saw 
in the description of INSERT and will not be described further.
After instruction 25 executes, the stack looks like this:</p>
}

stack {(record) new data record} {(integer) key}

puts {
<p>The PutIntKey instruction (also described
during the discussion about INSERT) writes an entry into the
database file whose data is the top of the stack and whose key
is the next on the stack, and then pops the stack twice.  The
PutIntKey instruction will overwrite the data of an existing record
with the same key, which is what we want here.  Overwriting was not
an issue with INSERT because with INSERT the key was generated







|







981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
the same kind of code that we saw 
in the description of INSERT and will not be described further.
After instruction 25 executes, the stack looks like this:</p>
}

stack {(record) new data record} {(integer) key}

hd_puts {
<p>The PutIntKey instruction (also described
during the discussion about INSERT) writes an entry into the
database file whose data is the top of the stack and whose key
is the next on the stack, and then pops the stack twice.  The
PutIntKey instruction will overwrite the data of an existing record
with the same key, which is what we want here.  Overwriting was not
an issue with INSERT because with INSERT the key was generated
1003
1004
1005
1006
1007
1008
1009
1010
1011
1012
1013
1014
1015
1016
1017
rows.  Actually, the Key instruction starts to become
very inefficient as you approach this upper bound, so it
is best to keep the number of entries below 2<sup>31</sup>
or so.  Surely a couple billion records will be enough for
most applications!)</p>
}

puts {
<h2>CREATE and DROP</h2>

<p>Using CREATE or DROP to create or destroy a table or index is
really the same as doing an INSERT or DELETE from the special
"sqlite_master" table, at least from the point of view of the VDBE.
The sqlite_master table is a special table that is automatically
created for every SQLite database.  It looks like this:</p>







|







1003
1004
1005
1006
1007
1008
1009
1010
1011
1012
1013
1014
1015
1016
1017
rows.  Actually, the Key instruction starts to become
very inefficient as you approach this upper bound, so it
is best to keep the number of entries below 2<sup>31</sup>
or so.  Surely a couple billion records will be enough for
most applications!)</p>
}

hd_puts {
<h2>CREATE and DROP</h2>

<p>Using CREATE or DROP to create or destroy a table or index is
really the same as doing an INSERT or DELETE from the special
"sqlite_master" table, at least from the point of view of the VDBE.
The sqlite_master table is a special table that is automatically
created for every SQLite database.  It looks like this:</p>
1140
1141
1142
1143
1144
1145
1146
1147
1148
1149
1150
1151
1152
1153
1154
1155
1156
1157
1158
1159
1160
1161
1162
1163
1164
1165
1166
1167
1168
1169
1170
1171
1172
1173
1174
1175
1176
1177
1178
1179
1180
1181
1182
1183
1184
26    Integer       333    0                                         
27    SetCookie     0      0                                         
28    Close         0      0                                         
29    Commit        0      0                                         
30    Halt          0      0                                         
}

puts {
<p>Remember that every table (except sqlite_master) and every named
index has an entry in the sqlite_master table.  Since we are creating
a new index, we have to add a new entry to sqlite_master.  This is
handled by instructions 3 through 15.  Adding an entry to sqlite_master
works just like any other INSERT statement so we will not say anymore
about it here.  In this example, we want to focus on populating the
new index with valid data, which happens on instructions 16 through 
23.</p>
}

Code {
16    Integer       0      0                                         
17    OpenRead      2      3      examp                              
}
puts {
<p>The first thing that happens is that we open the table being
indexed for reading.  In order to construct an index for a table,
we have to know what is in that table.  The index has already been 
opened for writing using cursor 0 by instructions 3 and 4.</p>
}

Code {
18    Rewind        2      24                                             
19    Recno         2      0                                         
20    Column        2      1                                         
21    MakeIdxKey    1      0      n                                  
22    IdxPut        1      0      indexed columns are not unique     
23    Next          2      19                                             
}
puts {
<p>Instructions 18 through 23 implement a loop over every row of the 
table being indexed.  For each table row, we first extract the integer 
key for that row using Recno in instruction 19, then get the value of 
the "two" column using Column in instruction 20.  
The <a href="opcode.html#MakeIdxKey">MakeIdxKey</a> instruction at 21 
converts data from the "two" column (which is on the top of the stack) 
into a valid index key.  For an index on a single column, this is 







|














|














|







1140
1141
1142
1143
1144
1145
1146
1147
1148
1149
1150
1151
1152
1153
1154
1155
1156
1157
1158
1159
1160
1161
1162
1163
1164
1165
1166
1167
1168
1169
1170
1171
1172
1173
1174
1175
1176
1177
1178
1179
1180
1181
1182
1183
1184
26    Integer       333    0                                         
27    SetCookie     0      0                                         
28    Close         0      0                                         
29    Commit        0      0                                         
30    Halt          0      0                                         
}

hd_puts {
<p>Remember that every table (except sqlite_master) and every named
index has an entry in the sqlite_master table.  Since we are creating
a new index, we have to add a new entry to sqlite_master.  This is
handled by instructions 3 through 15.  Adding an entry to sqlite_master
works just like any other INSERT statement so we will not say anymore
about it here.  In this example, we want to focus on populating the
new index with valid data, which happens on instructions 16 through 
23.</p>
}

Code {
16    Integer       0      0                                         
17    OpenRead      2      3      examp                              
}
hd_puts {
<p>The first thing that happens is that we open the table being
indexed for reading.  In order to construct an index for a table,
we have to know what is in that table.  The index has already been 
opened for writing using cursor 0 by instructions 3 and 4.</p>
}

Code {
18    Rewind        2      24                                             
19    Recno         2      0                                         
20    Column        2      1                                         
21    MakeIdxKey    1      0      n                                  
22    IdxPut        1      0      indexed columns are not unique     
23    Next          2      19                                             
}
hd_puts {
<p>Instructions 18 through 23 implement a loop over every row of the 
table being indexed.  For each table row, we first extract the integer 
key for that row using Recno in instruction 19, then get the value of 
the "two" column using Column in instruction 20.  
The <a href="opcode.html#MakeIdxKey">MakeIdxKey</a> instruction at 21 
converts data from the "two" column (which is on the top of the stack) 
into a valid index key.  For an index on a single column, this is 
1229
1230
1231
1232
1233
1234
1235
1236
1237
1238
1239
1240
1241
1242
1243
17    Callback      2      0                                         
18    Next          1      11                                        
19    Close         0      0                                         
20    Close         1      0                                         
21    Halt          0      0                                         
}

puts {
<p>The SELECT begins in a familiar fashion.  First the column
names are initialized and the table being queried is opened.
Things become different beginning with instructions 5 and 6 where
the index file is also opened.  Instructions 7 and 8 make
a key with the value of 50.  
The <a href="opcode.html#MemStore">MemStore</a> instruction at 9 stores 
the index key in VDBE memory location 0.  The VDBE memory is used to 







|







1229
1230
1231
1232
1233
1234
1235
1236
1237
1238
1239
1240
1241
1242
1243
17    Callback      2      0                                         
18    Next          1      11                                        
19    Close         0      0                                         
20    Close         1      0                                         
21    Halt          0      0                                         
}

hd_puts {
<p>The SELECT begins in a familiar fashion.  First the column
names are initialized and the table being queried is opened.
Things become different beginning with instructions 5 and 6 where
the index file is also opened.  Instructions 7 and 8 make
a key with the value of 50.  
The <a href="opcode.html#MemStore">MemStore</a> instruction at 9 stores 
the index key in VDBE memory location 0.  The VDBE memory is used to 
1308
1309
1310
1311
1312
1313
1314
1315
1316
1317
1318
1319
1320
1321
1322
15    PutIntKey     0      1                                         
16    Close         0      0                                         
17    Close         1      0                                         
18    Commit        0      0                                         
19    Halt          0      0                                         
}

puts {
<p>At this point, you should understand the VDBE well enough to
figure out on your own how the above program works.  So we will
not discuss it further in this text.</p>

<h2>Joins</h2>

<p>In a join, two or more tables are combined to generate a single







|







1308
1309
1310
1311
1312
1313
1314
1315
1316
1317
1318
1319
1320
1321
1322
15    PutIntKey     0      1                                         
16    Close         0      0                                         
17    Close         1      0                                         
18    Commit        0      0                                         
19    Halt          0      0                                         
}

hd_puts {
<p>At this point, you should understand the VDBE well enough to
figure out on your own how the above program works.  So we will
not discuss it further in this text.</p>

<h2>Joins</h2>

<p>In a join, two or more tables are combined to generate a single
1425
1426
1427
1428
1429
1430
1431
1432
1433
1434
1435
1436
1437
1438
1439
22    Next          1      14                                             
23    Next          0      10                                        
24    Close         0      0                                         
25    Close         1      0                                         
26    Halt          0      0                                         
}

puts {
<p>The outer loop over table examp is implement by instructions
7 through 23.  The inner loop is instructions 13 through 22.
Notice that the "two<50" term of the WHERE expression involves
only columns from the first table and can be factored out of
the inner loop.  SQLite does this and implements the "two<50"
test in instructions 10 through 12.  The "four==two" test is
implement by instructions 14 through 16 in the inner loop.</p>







|







1425
1426
1427
1428
1429
1430
1431
1432
1433
1434
1435
1436
1437
1438
1439
22    Next          1      14                                             
23    Next          0      10                                        
24    Close         0      0                                         
25    Close         1      0                                         
26    Halt          0      0                                         
}

hd_puts {
<p>The outer loop over table examp is implement by instructions
7 through 23.  The inner loop is instructions 13 through 22.
Notice that the "two<50" term of the WHERE expression involves
only columns from the first table and can be factored out of
the inner loop.  SQLite does this and implements the "two<50"
test in instructions 10 through 12.  The "four==two" test is
implement by instructions 14 through 16 in the inner loop.</p>
1485
1486
1487
1488
1489
1490
1491
1492
1493
1494
1495
1496
1497
1498
1499
16    SortNext      0      19                                             
17    SortCallback  2      0                                              
18    Goto          0      16                                             
19    SortReset     0      0                                         
20    Halt          0      0                                         
}

puts {
<p>There is only one sorter object, so there are no instructions to open 
or close it.  It is opened automatically when needed, and it is closed 
when the VDBE program halts.</p>

<p>The query loop is built from instructions 5 through 13.  Instructions
6 through 8 build a record that contains the azData[] values for a single
invocation of the callback.  A sort key is generated by instructions







|







1485
1486
1487
1488
1489
1490
1491
1492
1493
1494
1495
1496
1497
1498
1499
16    SortNext      0      19                                             
17    SortCallback  2      0                                              
18    Goto          0      16                                             
19    SortReset     0      0                                         
20    Halt          0      0                                         
}

hd_puts {
<p>There is only one sorter object, so there are no instructions to open 
or close it.  It is opened automatically when needed, and it is closed 
when the VDBE program halts.</p>

<p>The query loop is built from instructions 5 through 13.  Instructions
6 through 8 build a record that contains the azData[] values for a single
invocation of the callback.  A sort key is generated by instructions
1579
1580
1581
1582
1583
1584
1585
1586
1587
1588
1589
1590
1591
1592
1593
28    Add           0      0                                         
29    Callback      2      0                                         
30    Goto          0      24                                             
31    Noop          0      0                                         
32    Halt          0      0                                         
}

puts {
<p>The first instruction of interest is the 
<a href="opcode.html#AggReset">AggReset</a> at 2.
The AggReset instruction initializes the set of buckets to be the
empty set and specifies the number of memory slots available in each
bucket as P2.  In this example, each bucket will hold 3 memory slots.
It is not obvious, but if you look closely at the rest of the program
you can figure out what each of these slots is intended for.</p>







|







1579
1580
1581
1582
1583
1584
1585
1586
1587
1588
1589
1590
1591
1592
1593
28    Add           0      0                                         
29    Callback      2      0                                         
30    Goto          0      24                                             
31    Noop          0      0                                         
32    Halt          0      0                                         
}

hd_puts {
<p>The first instruction of interest is the 
<a href="opcode.html#AggReset">AggReset</a> at 2.
The AggReset instruction initializes the set of buckets to be the
empty set and specifies the number of memory slots available in each
bucket as P2.  In this example, each bucket will hold 3 memory slots.
It is not obvious, but if you look closely at the rest of the program
you can figure out what each of these slots is intended for.</p>
1685
1686
1687
1688
1689
1690
1691
1692
1693
1694
1695
1696
1697
1698
1699
34    Add           0      0                                         
35    Callback      2      0                                         
36    Goto          0      27                                             
37    Noop          0      0                                         
38    Halt          0      0                                         
}

puts {
<p>The code generated in this last example is the same as the
previous except for the addition of two conditional jumps used
to implement the extra WHERE and HAVING clauses.  The WHERE
clause is implemented by instructions 9 through 11 in the query
loop.  The HAVING clause is implemented by instruction 28 through
30 in the output loop.</p>








|







1685
1686
1687
1688
1689
1690
1691
1692
1693
1694
1695
1696
1697
1698
1699
34    Add           0      0                                         
35    Callback      2      0                                         
36    Goto          0      27                                             
37    Noop          0      0                                         
38    Halt          0      0                                         
}

hd_puts {
<p>The code generated in this last example is the same as the
previous except for the addition of two conditional jumps used
to implement the extra WHERE and HAVING clauses.  The WHERE
clause is implemented by instructions 9 through 11 in the query
loop.  The HAVING clause is implemented by instruction 28 through
30 in the output loop.</p>

1751
1752
1753
1754
1755
1756
1757
1758
1759
1760
1761
1762
1763
1764
1765
23    Column        0      1                                         
24    Callback      2      0                                         
25    Next          0      19                                             
26    Close         0      0                                         
27    Halt          0      0                                         
}

puts {
<p>The private memory cell is initialized to NULL by the first
two instructions.  Instructions 2 through 13 implement the inner
SELECT statement against the examp2 table.  Notice that instead of
sending the result to a callback or storing the result on a sorter,
the result of the query is pushed into the memory cell by instruction
10 and the loop is abandoned by the jump at instruction 11.  
The jump at instruction at 11 is vestigial and never executes.</p>







|







1751
1752
1753
1754
1755
1756
1757
1758
1759
1760
1761
1762
1763
1764
1765
23    Column        0      1                                         
24    Callback      2      0                                         
25    Next          0      19                                             
26    Close         0      0                                         
27    Halt          0      0                                         
}

hd_puts {
<p>The private memory cell is initialized to NULL by the first
two instructions.  Instructions 2 through 13 implement the inner
SELECT statement against the examp2 table.  Notice that instead of
sending the result to a callback or storing the result on a sorter,
the result of the query is pushed into the memory cell by instruction
10 and the loop is abandoned by the jump at instruction 11.  
The jump at instruction at 11 is vestigial and never executes.</p>
1816
1817
1818
1819
1820
1821
1822
1823
1824
1825
1826
1827
1828
1829
1830
22    Column        0      1                                         
23    Callback      2      0                                         
24    Next          0      16                                             
25    Close         0      0                                         
26    Halt          0      0                                         
}

puts {
<p>The transient table in which the results of the inner SELECT are
stored is created by the <a href="opcode.html#OpenTemp">OpenTemp</a> 
instruction at 0.  This opcode is used for tables that exist for the 
duration of a single SQL statement only.  The transient cursor is always 
opened read/write even if the main database is read-only.  The transient 
table is deleted automatically when the cursor is closed.  The P2 value 
of 1 means the cursor points to a BTree index, which has no data but can 







|







1816
1817
1818
1819
1820
1821
1822
1823
1824
1825
1826
1827
1828
1829
1830
22    Column        0      1                                         
23    Callback      2      0                                         
24    Next          0      16                                             
25    Close         0      0                                         
26    Halt          0      0                                         
}

hd_puts {
<p>The transient table in which the results of the inner SELECT are
stored is created by the <a href="opcode.html#OpenTemp">OpenTemp</a> 
instruction at 0.  This opcode is used for tables that exist for the 
duration of a single SQL statement only.  The transient cursor is always 
opened read/write even if the main database is read-only.  The transient 
table is deleted automatically when the cursor is closed.  The P2 value 
of 1 means the cursor points to a BTree index, which has no data but can 
1895
1896
1897
1898
1899
1900
1901
1902
1903
1904
1905
1906
1907
1908
1909
23    Column        0      0                                         
24    Callback      1      0                                         
25    Next          0      23                                             
26    Close         0      0                                         
27    Halt          0      0                                         
}

puts {
<p>The transient table in which the result is built is created by
instruction 0.  Three loops then follow.  The loop at instructions
5 through 10 implements the first SELECT statement.  The second
SELECT statement is implemented by the loop at instructions 14 through
19.  Finally, a loop at instructions 22 through 25 reads the transient
table and invokes the callback once for each row in the result.</p>








|







1895
1896
1897
1898
1899
1900
1901
1902
1903
1904
1905
1906
1907
1908
1909
23    Column        0      0                                         
24    Callback      1      0                                         
25    Next          0      23                                             
26    Close         0      0                                         
27    Halt          0      0                                         
}

hd_puts {
<p>The transient table in which the result is built is created by
instruction 0.  Three loops then follow.  The loop at instructions
5 through 10 implements the first SELECT statement.  The second
SELECT statement is implemented by the loop at instructions 14 through
19.  Finally, a loop at instructions 22 through 25 reads the transient
table and invokes the callback once for each row in the result.</p>

Changes to wrap.tcl.
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
# 
#
set DOC [lindex $argv 0]
set SRC [lindex $argv 1]
set DEST [lindex $argv 2]
set HOMEDIR [pwd]            ;# Also remember our home directory.









# We are going to overload the puts command, so remember the






















































# original puts command using an alternative name.




















































rename puts real_puts



proc puts {text} {








  real_puts $::OUT $text






  flush $::OUT








































































}


# putsin4 is like puts except that it removes the first 4 indentation
# characters from each line.  It also does variable substitution in
# the namespace of its calling procedure.
#
proc putsin4 {text} {
  regsub -all "\n    " $text \n text
  real_puts $::OUT [uplevel 1 [list subst -noback -nocom $text]]
  flush $::OUT
}

# A procedure to write the common header found on every HTML file on
# the SQLite website.
#
proc PutsHeader {title {path {}}} {









  puts {<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">}
  puts {<html><head>}
  puts "<title>$title</title>"
  putsin4 {<style type="text/css">
    body {
        margin: auto;
        font-family: "Verdana" "sans-serif";
        padding: 8px 1%;
    }
    
    a { color: #45735f }







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


>
|



|

|
<





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







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
207
208
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
275
276
# 
#
set DOC [lindex $argv 0]
set SRC [lindex $argv 1]
set DEST [lindex $argv 2]
set HOMEDIR [pwd]            ;# Also remember our home directory.

# This is the first-pass implementation of procedure that renders
# hyperlinks.  Do not even bother trying to do anything during the
# first pass.  We have to collect keyword information before the
# hyperlinks are meaningful.  
#
proc hd_resolve {text} {
  hd_puts $text
}

# This is the second-pass implementation of the procedure that
# renders hyperlinks.  Convert all hyperlinks in $text into 
# appropriate <a href=""> markup.
#
# Links to keywords within the same main file are resolved using
# $::llink() if possible.  All other links and links that could
# not be resolved using $::llink() are resolved using $::glink().
# 
proc hd_resolve_2ndpass {text} {
  regsub -all {\[(.*?)\]} $text \
      "\175; hd_resolve_one \173\\1\175; hd_puts \173" text
  eval "hd_puts \173$text\175"
}
proc hd_resolve_one {x} {
  set x2 [split $x |]
  set kw [string trim [lindex $x2 0]]
  if {[llength $x2]==1} {
    set content $kw
    regsub -all {[^a-zA-Z0-9_.# -]} $content {} kw
  } else {
    set content [string trim [lindex $x2 1]]
  }
  global hd llink glink
  if {$hd(enable-main)} {
    set fn $hd(fn-main)
    if {[regexp {^[Tt]icket #(\d+)$} $kw all tktid]} {
      set url http://www.sqlite.org/cvstrac/tktview?tn=$tktid
      puts -nonewline $hd(main) \
        "<a href=\"$url\">$content</a>"
    } elseif {[info exists llink($fn:$kw)]} {
      puts -nonewline $hd(main) \
        "<a href=\"$hd(rootpath-main)$llink($fn:$kw)\">$content</a>"
    } elseif {[info exists glink($kw)]} {
      puts -nonewline $hd(main) \
        "<a href=\"$hd(rootpath-main)$glink($kw)\">$content</a>"
    } else {
      puts stderr "ERROR: unknown hyperlink target: $kw"
      puts -nonewline $hd(main) "<font color=\"red\">$content</font>"
    }
  }
  if {$hd(enable-aux)} {
    if {[regexp {^[Tt]icket #(\d+)$} $kw all tktid]} {
      set url http://www.sqlite.org/cvstrac/tktview?tn=$tktid
      puts -nonewline $hd(main) \
        "<a href=\"$url\">$content</a>"
    } elseif {[info exists glink($kw)]} {
      puts -nonewline $hd(aux) \
        "<a href=\"$hd(rootpath-aux)$glink($kw)\">$content</a>"
    } else {
      puts stderr "ERROR: unknown hyperlink target: $kw"
      puts -nonewline $hd(aux) "<font color=\"red\">$content</font>"
    }
  }
}



# Record the fact that the keywords given in the argument list should
# cause a jump to the current location in the current file.
#
# If only the main output file is open, then all references to the
# keyword jump to the main output file.  If both main and aux are
# open then references in the main file jump to the main file and all
# other references jump to the auxiliary file.
#
# This procedure is only active during the first pass when we are
# collecting hyperlink information.  This procedure is redefined to
# be a no-op before the start of the second pass.
#
proc hd_keywords {args} {
  global glink llink hd
  if {$hd(fragment)==""} {
    set lurl $hd(fn-main)
  } else {
    set lurl "#$hd(fragment)"
  }
  set fn $hd(fn-main)
  if {[info exists hd(aux)]} {
    set gurl $hd(fn-aux)
  } else {
    set gurl {}
  }
  foreach a $args {
    if {[info exists glink($a)]} {
      puts stderr "WARNING: duplicate keyword \"$a\""
    }
    if {$gurl==""} {
      set glink($a) $lurl
    } else {
      set glink($a) $gurl
      set llink($fn:$a) $lurl
    }
  }
}

# Start a new fragment in the main file.  Give the new fragment the
# indicated name.  Any keywords defined after this point will refer
# to the fragment, not to the beginning of the file.
#
# Only the main file may have fragments.  Auxiliary files are assumed
# to be small enough that fragments are not helpful.
#
proc hd_fragment {name} {
  global hd
  set hd(fragment) $name
  puts $hd(main) "<a name=\"$name\"></a>"
}

# Write raw output to both the main file and the auxiliary.  Only write
# to files that are enabled.
#
proc hd_puts {text} {
  global hd
  if {$hd(enable-main)} {
    puts $hd(main) $text
  }
  if {$hd(enable-aux)} {
    puts $hd(aux) $text
  }
}

# Enable or disable the main output file.
#
proc hd_enable_main {boolean} {
  global hd
  set hd(enable-main) $boolean
}

# Enable or disable the auxiliary output file.
#
proc hd_enable_aux {boolean} {
  global hd
  set hd(enable-aux) $boolean
}
set hd(enable-aux) 0

# Open the main output file.  $filename is relative to $::DEST.  
#
proc hd_open_main {filename} {
  global hd DEST
  hd_close_main
  set hd(fn-main) $filename
  set hd(rootpath-main) [hd_rootpath $filename]
  set hd(main) [open $DEST/$filename w]
  set hd(enable-main) 1
  set hd(fragment) {}
}

# If $filename is a path from $::DEST to a file, return a path
# from the directory containing $filename back to the directory $::DEST.
#
proc hd_rootpath {filename} {
  set up {}
  set n [llength [split $filename /]]
  if {$n<=1} {
    return {}
  } else {
    return [string repeat ../ [expr {$n-1}]]
  }
}

# Close the main output file.
#
proc hd_close_main {} {
  global hd
  hd_close_aux
  if {[info exists hd(main)]} {
    puts $hd(main) $hd(footer)
    close $hd(main)
    unset hd(main)
  }
}

# Open the auxiliary output file.
#
# Most documents have only a main file and no auxiliary.  However, some
# large documents are broken up into smaller pieces were each smaller piece
# is an auxiliary file.  There will typically be either many auxiliary files
# or no auxiliary files associated with each main file.
#
proc hd_open_aux {filename} {
  global hd DEST
  hd_close_aux
  set hd(fn-aux) $filename
  set hd(rootpath-aux) [hd_rootpath $filename]
  set hd(aux) [open $DEST/$filename w]
  set hd(enable-aux) 1
}

# Close the auxiliary output file
#
proc hd_close_aux {} {
  global hd
  if {[info exists hd(aux)]} {
    puts $hd(aux) $hd(footer)
    close $hd(aux)
    unset hd(aux)
    set hd(enable-aux) 0
    set hd(enable-main) 1
  }
}


# hd_putsin4 is like puts except that it removes the first 4 indentation
# characters from each line.  It also does variable substitution in
# the namespace of its calling procedure.
#
proc putsin4 {fd text} {
  regsub -all "\n    " $text \n text
  puts $fd [uplevel 1 [list subst -noback -nocom $text]]

}

# A procedure to write the common header found on every HTML file on
# the SQLite website.
#
proc hd_header {title {srcfile {}}} {
  global hd
  set saved_enable $hd(enable-main)
  if {$srcfile==""} {
    set fd $hd(aux)
    set path $hd(rootpath-aux)
  } else {
    set fd $hd(main)
    set path $hd(rootpath-main)
  }
  puts $fd {<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">}
  puts $fd {<html><head>}
  puts $fd "<title>$title</title>"
  putsin4 $fd {<style type="text/css">
    body {
        margin: auto;
        font-family: "Verdana" "sans-serif";
        padding: 8px 1%;
    }
    
    a { color: #45735f }
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
    .sw  { background: url(${path}images/sw.png) 0% 100% no-repeat }
    .ne  { background: url(${path}images/ne.png) 100% 0% no-repeat }
    .nw  { background: url(${path}images/nw.png) 0% 0% no-repeat }

    </style>
    <meta http-equiv="content-type" content="text/html; charset=UTF-8">
  }
  puts {</head>}
  putsin4 {<body>
    <div><!-- container div to satisfy validator -->
    
    <a href="${path}index.html">
    <img class="logo" src="${path}images/SQLite.gif" alt="SQLite Logo"
     border="0"></a>
    <div><!-- IE hack to prevent disappearing logo--></div>
    <div class="tagline">Small. Fast. Reliable.<br>Choose any three.</div>







|
|







307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
    .sw  { background: url(${path}images/sw.png) 0% 100% no-repeat }
    .ne  { background: url(${path}images/ne.png) 100% 0% no-repeat }
    .nw  { background: url(${path}images/nw.png) 0% 0% no-repeat }

    </style>
    <meta http-equiv="content-type" content="text/html; charset=UTF-8">
  }
  puts $fd {</head>}
  putsin4 $fd {<body>
    <div><!-- container div to satisfy validator -->
    
    <a href="${path}index.html">
    <img class="logo" src="${path}images/SQLite.gif" alt="SQLite Logo"
     border="0"></a>
    <div><!-- IE hack to prevent disappearing logo--></div>
    <div class="tagline">Small. Fast. Reliable.<br>Choose any three.</div>
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
        <a href="${path}copyright.html">License</a>
        <a href="${path}news.html">News</a>
        <a href="http://www.sqlite.org/cvstrac/index">Developers</a>
        <a href="${path}support.html">Support</a>
      </div></div></div></div></div>
    </td></tr></table>
  }









}

# A procedure to write the common footer found at the bottom of
# every HTML file.  $srcfile is the name of the file that is the
# source of the HTML content.  The modification time of this file
# is used to add the "last modified on" line at the bottom of the
# file.
#


proc PutsFooter {srcfile} {
  puts {<hr><small><i>}
  set mtime [file mtime $srcfile]
  set date [clock format $mtime -format {%Y/%m/%d %H:%M:%S UTC} -gmt 1]
  puts "This page last modified $date"
  puts {</i></small></div></body></html>}
}

# The following proc is used to ensure consistent formatting in the 
# HTML generated by lang.tcl and pragma.tcl.
#
proc Syntax {args} {
  puts {<table cellpadding="10" class=pdf_syntax>}
  foreach {rule body} $args {
    puts "<tr><td align=\"right\" valign=\"top\">"
    puts "<i><font color=\"#ff3434\">$rule</font></i>&nbsp;::=</td>"
    regsub -all < $body {%LT} body
    regsub -all > $body {%GT} body
    regsub -all %LT $body {</font></b><i><font color="#ff3434">} body
    regsub -all %GT $body {</font></i><b><font color="#2c2cf0">} body
    regsub -all {[]|[*?]} $body {</font></b>&<b><font color="#2c2cf0">} body
    regsub -all "\n" [string trim $body] "<br>\n" body
    regsub -all "\n  *" $body "\n\\&nbsp;\\&nbsp;\\&nbsp;\\&nbsp;" body
    regsub -all {[|,.*()]} $body {<big>&</big>} body
    regsub -all { = } $body { <big>=</big> } body
    regsub -all {STAR} $body {<big>*</big>} body
    ## These metacharacters must be handled to undo being
    ## treated as SQL punctuation characters above.
    regsub -all {RPPLUS} $body {</font></b>)+<b><font color="#2c2cf0">} body
    regsub -all {LP} $body {</font></b>(<b><font color="#2c2cf0">} body
    regsub -all {RP} $body {</font></b>)<b><font color="#2c2cf0">} body
    ## Place the left-hand side of the rule in the 2nd table column.
    puts "<td><b><font color=\"#2c2cf0\">$body</font></b></td></tr>"
  }
  puts {</table>}
}

# Loop over all input files and process them one by one



#
foreach infile [lrange $argv 3 end] {
  cd $HOMEDIR
  real_puts "Processing $infile"

























  set fd [open $infile r]
  set in [read $fd]
  close $fd
  set title {No Title}
  regexp {<title>([^\n]*)</title>} $in all title
  regsub {<title>[^\n]*</title>} $in {} in
  set outfile [file root [file tail $infile]].html
  set ::OUT [open $::DEST/$outfile w]
  PutsHeader $title
  regsub -all {<tcl>} $in "\175; eval \173" in
  regsub -all {</tcl>} $in "\175; puts \173" in
  eval "puts \173$in\175"
  cd $::HOMEDIR
  PutsFooter $infile
  close $::OUT
}







>
>
>
>
>
>
>
>
>








>
>
|
|


|
|






|

|
|
















|

|


|
>
>
>



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







|
|

|
|

<
|

331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439

440
441
        <a href="${path}copyright.html">License</a>
        <a href="${path}news.html">News</a>
        <a href="http://www.sqlite.org/cvstrac/index">Developers</a>
        <a href="${path}support.html">Support</a>
      </div></div></div></div></div>
    </td></tr></table>
  }
  if {$srcfile!=""} {
    set hd(footer) "<hr><small<i>\n"
    set mtime [file mtime $srcfile]
    set date [clock format $mtime -format {%Y/%m/%d %H:%M:%S UTC} -gmt 1]
    append hd(footer) "This page last modified $date\n"
    append hd(footer) "</i></small></div></body></html>"
  } else {
    set hd(enable-main) $saved_enable
  }
}

# A procedure to write the common footer found at the bottom of
# every HTML file.  $srcfile is the name of the file that is the
# source of the HTML content.  The modification time of this file
# is used to add the "last modified on" line at the bottom of the
# file.
#
proc hd_footer {} {
  global hd
  
  hd_puts {<hr><small><i>}
  set mtime [file mtime $srcfile]
  set date [clock format $mtime -format {%Y/%m/%d %H:%M:%S UTC} -gmt 1]
  hd_puts "This page last modified $date"
  hd_puts {</i></small></div></body></html>}
}

# The following proc is used to ensure consistent formatting in the 
# HTML generated by lang.tcl and pragma.tcl.
#
proc Syntax {args} {
  hd_puts {<table cellpadding="10">}
  foreach {rule body} $args {
    hd_puts "<tr><td align=\"right\" valign=\"top\">"
    hd_puts "<i><font color=\"#ff3434\">$rule</font></i>&nbsp;::=</td>"
    regsub -all < $body {%LT} body
    regsub -all > $body {%GT} body
    regsub -all %LT $body {</font></b><i><font color="#ff3434">} body
    regsub -all %GT $body {</font></i><b><font color="#2c2cf0">} body
    regsub -all {[]|[*?]} $body {</font></b>&<b><font color="#2c2cf0">} body
    regsub -all "\n" [string trim $body] "<br>\n" body
    regsub -all "\n  *" $body "\n\\&nbsp;\\&nbsp;\\&nbsp;\\&nbsp;" body
    regsub -all {[|,.*()]} $body {<big>&</big>} body
    regsub -all { = } $body { <big>=</big> } body
    regsub -all {STAR} $body {<big>*</big>} body
    ## These metacharacters must be handled to undo being
    ## treated as SQL punctuation characters above.
    regsub -all {RPPLUS} $body {</font></b>)+<b><font color="#2c2cf0">} body
    regsub -all {LP} $body {</font></b>(<b><font color="#2c2cf0">} body
    regsub -all {RP} $body {</font></b>)<b><font color="#2c2cf0">} body
    ## Place the left-hand side of the rule in the 2nd table column.
    hd_puts "<td><b><font color=\"#2c2cf0\">$body</font></b></td></tr>"
  }
  hd_puts {</table>}
}


# First pass.  Process all files.  But do not render hyperlinks.
# Merely collect keyword information so that hyperlinks can be
# correctly rendered on the second pass.
#
foreach infile [lrange $argv 3 end] {
  cd $HOMEDIR
  puts "Processing $infile"
  set fd [open $infile r]
  set in [read $fd]
  close $fd
  set title {No Title}
  regexp {<title>([^\n]*)</title>} $in all title
  regsub {<title>[^\n]*</title>} $in {} in
  set outfile [file root [file tail $infile]].html
  hd_open_main $outfile
  hd_header $title $infile
  regsub -all {<tcl>} $in "\175; eval \173" in
  regsub -all {</tcl>} $in "\175; hd_puts \173" in
  eval "hd_puts \173$in\175"
  cd $::HOMEDIR
  hd_close_main
}

# Second pass.  Process all files again.  This time render hyperlinks
# according to the keyword information collected on the first pass.
#
proc hd_keywords {args} {}
rename hd_resolve {}
rename hd_resolve_2ndpass hd_resolve
foreach infile [lrange $argv 3 end] {
  cd $HOMEDIR
  puts "Processing $infile"
  set fd [open $infile r]
  set in [read $fd]
  close $fd
  set title {No Title}
  regexp {<title>([^\n]*)</title>} $in all title
  regsub {<title>[^\n]*</title>} $in {} in
  set outfile [file root [file tail $infile]].html
  hd_open_main $outfile
  hd_header $title $infile
  regsub -all {<tcl>} $in "\175; eval \173" in
  regsub -all {</tcl>} $in "\175; hd_resolve \173" in
  eval "hd_resolve \173$in\175"
  cd $::HOMEDIR

  hd_close_main
}