SQLite

Check-in [97339efdf3]
Login

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

Overview
Comment:Comment changes only (CVS 165)
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 97339efdf3f3b02a6a4fe57d2cb01d19cec3c749
User & Date: drh 2000-11-28 20:46:39.000
Context
2000-11-28
20:47
Comment changes only (CVS 166) (check-in: 5518e012bf user: drh tags: trunk)
20:46
Comment changes only (CVS 165) (check-in: 97339efdf3 user: drh tags: trunk)
2000-10-23
13:20
Version 1.0.15 (CVS 488) (check-in: d2ad3d2b4e user: drh tags: trunk)
Changes
Side-by-Side Diff Ignore Whitespace Patch
Changes to www/c_interface.tcl. 
1
2
3
4

5
6
7
8
9
10
11

1
2
3

4
5
6
7
8
9
10
11




-
+








#
# Run this Tcl script to generate the sqlite.html file.
#
set rcsid {$Id: c_interface.tcl,v 1.12 2000/10/23 13:16:33 drh Exp $}
set rcsid {$Id: c_interface.tcl,v 1.13 2000/11/28 20:46:39 drh Exp $}

puts {<html>
<head>
  <title>The C language interface to the SQLite library</title>
</head>
<body bgcolor=white>
<h1 align=center>
︙
454
455
456
457
458
459
460
461

462
463
464
465
466
467
468

454
455
456
457
458
459
460

461
462
463
464
465
466
467
468








-
+








it into the result.  But %q translates the inserted string by
making two copies of every single-quote (') character in the
substituted string.  This has the effect of escaping the end-of-string
meaning of single-quote within a string literal.
</p>

<p>Consider an example.  Suppose you are trying to insert a string
values into a database table where the string value was obtained from
value into a database table where the string value was obtained from
user input.  Suppose the string to be inserted is stored in a variable
named zString.  The code to do the insertion might look like this:</p>

<blockquote><pre>
sqlite_exec_printf(db,
  "INSERT INTO table1 VALUES('%s')",
  0, 0, 0, zString);
︙
Changes to www/vdbe.tcl. 
1
2
3
4

5
6
7
8
9
10
11

1
2
3

4
5
6
7
8
9
10
11




-
+








#
# Run this Tcl script to generate the vdbe.html file.
#
set rcsid {$Id: vdbe.tcl,v 1.5 2000/07/30 20:04:43 drh Exp $}
set rcsid {$Id: vdbe.tcl,v 1.6 2000/11/28 20:46:41 drh Exp $}

puts {<html>
<head>
  <title>The Virtual Database Engine of SQLite</title>
</head>
<body bgcolor=white>
<h1 align=center>
︙
216
217
218
219
220
221
222
223

224
225
226
227
228
229
230

216
217
218
219
220
221
222

223
224
225
226
227
228
229
230








-
+








<a name="trace">
<h2>Tracing VDBE Program Execution</h2>

<p>If the SQLite library is compiled without the NDEBUG 
preprocessor macro, then
there is a special SQL comment that will cause the 
the VDBE to traces the execution of programs.
Though this features was originally intended for testing
Though this feature was originally intended for testing
and debugging, it might also be useful in learning about
how the VDBE operates.
Use the "<tt>--vdbe-trace-on--</tt>" comment to
turn tracing on and "<tt>--vdbe-trace-off--</tt>" to turn tracing
back off.  Like this:</p>
}
︙
302
303
304
305
306
307
308
309

310
311
312
313
314
315
316

302
303
304
305
306
307
308

309
310
311
312
313
314
315
316








-
+









<blockquote><pre>
int Callback(void *pUserData, int nColumn, char *azData[], char *azColumnName[]);
</pre></blockquote>

<p>The SQLite library supplies the VDBE with a pointer to the callback function
and the <b>pUserData</b> pointer.  (Both the callback and the user data were
originally passed in as argument to the <b>sqlite_exec()</b> API function.)
originally passed in as arguments to the <b>sqlite_exec()</b> API function.)
The job of the VDBE is to
come up with values for <b>nColumn</b>, <b>azData[]</b>, 
and <b>azColumnName[]</b>.
<b>nColumn</b> is the number of columns in the results, of course.
<b>azColumnName[]</b> is an array of strings where each string is the name
of one of the result column.  <b>azData[]</b> is an array of strings holding
the actual data.</p>
︙
765
766
767
768
769
770
771
772

773
774
775
776
777
778
779

765
766
767
768
769
770
771

772
773
774
775
776
777
778
779








-
+









<p>In the example queries above, every row of the table being
queried must be loaded off of the disk and examined, even if only
a small percentage of the rows end up in the result.  This can
take a long time on a big table.  To speed things up, SQLite
can use an index.</p>

<p>An GDBM file associates a key with some data.  For a SQLite
<p>A GDBM file associates a key with some data.  For a SQLite
table, the GDBM file is set up so that the key is a integer
and the data is the information for one row of the table.
Indices in SQLite reverse this arrangement.  The GDBM key
is (some of) the information being stored and the GDBM data 
is an integer.
To access a table row that has some particular
content, we first look up the content in the GDBM index file to find
︙
1168
1169
1170
1171
1172
1173
1174
1175

1176
1177
1178
1179
1180
1181
1182

1168
1169
1170
1171
1172
1173
1174

1175
1176
1177
1178
1179
1180
1181
1182








-
+








The data structure is an unordered set of buckets, where each bucket
has a key and one or more memory locations.  Within the query
loop, the GROUP BY clause is used to construct a key and the bucket
with that key is brought into focus.  A new bucket is created with
the key if one did not previously exist.  Once the bucket is in
focus, the memory locations of the bucket are used to accumulate
the values of the various aggregate functions.  After the query
loop terminates, the each bucket is visited once to generate a
loop terminates, each bucket is visited once to generate a
single row of the results.</p>

<p>An example will help to clarify this concept.  Consider the
following query:</p>

<blockquote><pre>
SELECT three, min(three+four)+avg(four)
︙
1472
1473
1474
1475
1476
1477
1478
1479

1480
1481
1482
1483
1484
1485
1486

1472
1473
1474
1475
1476
1477
1478

1479
1480
1481
1482
1483
1484
1485
1486








-
+








table will be automatically deleted from the disk when the
VDBE halts.</p>

<p>The inner SELECT statement is implemented by instructions 1 through 7.
All this code does is make an entry in the temporary table for each
row of the examp2 table.  The key for each temporary table entry
is the "three" column of examp2 and the data 
entries is an empty string since it is never used.</p>
is an empty string since it is never used.</p>

<p>The outer SELECT is implemented by instructions 8 through 19.  In
particular, the WHERE clause containing the IN operator is implemented
by two instructions at 13 and 14.  Instruction 13 pushes the value of
the "two" column for the current row onto the stack and instruction 14
tests to see if top of the stack matches any key in the temporary table.
All the rest of the code is the same as what has been shown before.</p>
︙