/ Check-in [97339efd]
Login

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

Overview
Comment:Comment changes only (CVS 165)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1:97339efdf3f3b02a6a4fe57d2cb01d19cec3c749
User & Date: drh 2000-11-28 20:46:39
Context
2000-11-28
20:47
Comment changes only (CVS 166) check-in: 5518e012 user: drh tags: trunk
20:46
Comment changes only (CVS 165) check-in: 97339efd user: drh tags: trunk
2000-10-23
13:20
Version 1.0.15 (CVS 488) check-in: d2ad3d2b user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to www/c_interface.tcl.

1
2
3
4
5
6
7
8
9
10
11
...
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
#
# 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 $}

puts {<html>
<head>
  <title>The C language interface to the SQLite library</title>
</head>
<body bgcolor=white>
<h1 align=center>
................................................................................
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
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);



|







 







|







1
2
3
4
5
6
7
8
9
10
11
...
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
#
# Run this Tcl script to generate the sqlite.html file.
#
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>
................................................................................
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
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
...
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
...
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
...
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
....
1168
1169
1170
1171
1172
1173
1174
1175
1176
1177
1178
1179
1180
1181
1182
....
1472
1473
1474
1475
1476
1477
1478
1479
1480
1481
1482
1483
1484
1485
1486
#
# 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 $}

puts {<html>
<head>
  <title>The Virtual Database Engine of SQLite</title>
</head>
<body bgcolor=white>
<h1 align=center>
................................................................................
<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
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>
}

................................................................................

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

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

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



|







 







|







 







|







 







|







 







|







 







|







1
2
3
4
5
6
7
8
9
10
11
...
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
...
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
...
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
....
1168
1169
1170
1171
1172
1173
1174
1175
1176
1177
1178
1179
1180
1181
1182
....
1472
1473
1474
1475
1476
1477
1478
1479
1480
1481
1482
1483
1484
1485
1486
#
# Run this Tcl script to generate the vdbe.html file.
#
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>
................................................................................
<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 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>
}

................................................................................

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

<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>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
................................................................................
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, 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) 
................................................................................
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 
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>