SQLite

Check-in [ea315668e5]
Login

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

Overview
Comment:Split up the lang.html page into a seperate page for each command. (CVS 2116)
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: ea315668e5833befe296fc94c67f914061d2ffb2
User & Date: danielk1977 2004-11-19 11:59:24.000
Context
2004-11-20
06:05
Add a documentation page for compilation options. Still some work to go. (CVS 2117) (check-in: 5efa2d2a60 user: danielk1977 tags: trunk)
2004-11-19
11:59
Split up the lang.html page into a seperate page for each command. (CVS 2116) (check-in: ea315668e5 user: danielk1977 tags: trunk)
08:41
Ensure ALTER TABLE respects the system table convention - "sqlite_*". (CVS 2115) (check-in: f635b6aae6 user: danielk1977 tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to main.mk.
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
formatchng.html:	$(TOP)/www/formatchng.tcl
	tclsh $(TOP)/www/formatchng.tcl >formatchng.html

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

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

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

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








|







433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
formatchng.html:	$(TOP)/www/formatchng.tcl
	tclsh $(TOP)/www/formatchng.tcl >formatchng.html

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

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

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

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

Changes to www/c_interface.tcl.
1
2
3
4
5
6
7
8
9
10
11
#
# Run this Tcl script to generate the sqlite.html file.
#
set rcsid {$Id: c_interface.tcl,v 1.42 2004/11/10 05:48:57 danielk1977 Exp $}
source common.tcl
header {The C language interface to the SQLite library}
puts {
<h2>The C language interface to the SQLite library</h2>

<p>The SQLite library is designed to be very easy to use from
a C or C++ program.  This document gives an overview of the C/C++



|







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.43 2004/11/19 11:59:24 danielk1977 Exp $}
source common.tcl
header {The C language interface to the SQLite library}
puts {
<h2>The C language interface to the SQLite library</h2>

<p>The SQLite library is designed to be very easy to use from
a C or C++ program.  This document gives an overview of the C/C++
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
of pointers to column names and datatypes.
The 2nd through 4th parameters to <b>sqlite_step</b> convey the
same information as the 2nd through 4th parameters of the
<b>callback</b> routine when using
the <b>sqlite_exec</b> interface.  Except, with <b>sqlite_step</b>
the column datatype information is always included in the in the
4th parameter regardless of whether or not the
<a href="lang.html#pragma_show_datatypes">SHOW_DATATYPES</a> pragma
is on or off.
</p>

<p>
Each invocation of <b>sqlite_step</b> returns an integer code that
indicates what happened during that step.  This code may be
SQLITE_BUSY, SQLITE_ROW, SQLITE_DONE, SQLITE_ERROR, or







|







442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
of pointers to column names and datatypes.
The 2nd through 4th parameters to <b>sqlite_step</b> convey the
same information as the 2nd through 4th parameters of the
<b>callback</b> routine when using
the <b>sqlite_exec</b> interface.  Except, with <b>sqlite_step</b>
the column datatype information is always included in the in the
4th parameter regardless of whether or not the
<a href="pragma.html#pragma_show_datatypes">SHOW_DATATYPES</a> pragma
is on or off.
</p>

<p>
Each invocation of <b>sqlite_step</b> returns an integer code that
indicates what happened during that step.  This code may be
SQLITE_BUSY, SQLITE_ROW, SQLITE_DONE, SQLITE_ERROR, or
Changes to www/common.tcl.
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
  if {$date!=""} {
    puts "<small><i>This page last modified on $date</i></small>"
  }
  puts {</body></html>}
}


# The following two procs, Syntax and Section, are used to ensure
# consistent formatting in the "lang.html" and "pragma.html" pages.
#
proc Syntax {args} {
  puts {<table cellpadding="10">}
  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







|
|







53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
  if {$date!=""} {
    puts "<small><i>This page last modified on $date</i></small>"
  }
  puts {</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">}
  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
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
    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>}
}
proc Section {name {label {}}} {
  puts "\n<hr />"
  if {$label!=""} {
    puts "<a name=\"$label\"></a>"
  }
  puts "<h1>$name</h1>\n"
}








<
<
<
<
|
<
<
<
81
82
83
84
85
86
87




88



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








Changes to www/datatype3.tcl.
1
2
3
4
5
6
7
8
set rcsid {$Id: datatype3.tcl,v 1.9 2004/11/11 01:50:30 danielk1977 Exp $}
source common.tcl
header {Datatypes In SQLite Version 3}
puts {
<h2>Datatypes In SQLite Version 3</h2>

<h3>1. Storage Classes</h3>

|







1
2
3
4
5
6
7
8
set rcsid {$Id: datatype3.tcl,v 1.10 2004/11/19 11:59:24 danielk1977 Exp $}
source common.tcl
header {Datatypes In SQLite Version 3}
puts {
<h2>Datatypes In SQLite Version 3</h2>

<h3>1. Storage Classes</h3>

325
326
327
328
329
330
331
332
333
334
335
336
337
338
339


<h4>7.1 Assigning Collation Sequences from SQL</h4>

<p>
Each column of each table has a default collation type. If a collation type
other than BINARY is required, a COLLATE clause is specified as part of the
<a href="lang.html#createtable">column definition</a> to define it. 
</p>  

<p>
Whenever two text values are compared by SQLite, a collation sequence is
used to determine the results of the comparison according to the following
rules. Sections 3 and 5 of this document describe the circumstances under
which such a comparison takes place.







|







325
326
327
328
329
330
331
332
333
334
335
336
337
338
339


<h4>7.1 Assigning Collation Sequences from SQL</h4>

<p>
Each column of each table has a default collation type. If a collation type
other than BINARY is required, a COLLATE clause is specified as part of the
<a href="lang_createtable.html">column definition</a> to define it. 
</p>  

<p>
Whenever two text values are compared by SQLite, a collation sequence is
used to determine the results of the comparison according to the following
rules. Sections 3 and 5 of this document describe the circumstances under
which such a comparison takes place.
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
expression "x = y" for the purposes of determining the collation sequence
to use. The collation sequence used for expressions of the form "x IN (y, z
...)" is the default collation type of x if x is a column, or BINARY
otherwise.
</p>  

<p>
An <a href="lang.html#select">ORDER BY</a> clause that is part of a SELECT
statement may be assigned a collation sequence to be used for the sort
operation explicitly. In this case the explicit collation sequence is
always used.  Otherwise, if the expression sorted by an ORDER BY clause is
a column, then the default collation type of the column is used to
determine sort order. If the expression is not a column, then the BINARY
collation sequence is used.
</p>  







|







354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
expression "x = y" for the purposes of determining the collation sequence
to use. The collation sequence used for expressions of the form "x IN (y, z
...)" is the default collation type of x if x is a column, or BINARY
otherwise.
</p>  

<p>
An <a href="lang_select.html">ORDER BY</a> clause that is part of a SELECT
statement may be assigned a collation sequence to be used for the sort
operation explicitly. In this case the explicit collation sequence is
always used.  Otherwise, if the expression sorted by an ORDER BY clause is
a column, then the default collation type of the column is used to
determine sort order. If the expression is not a column, then the BINARY
collation sequence is used.
</p>  
Changes to www/lang.tcl.
1
2
3
4
5







6
7
8
9
10
11
12
#
# Run this Tcl script to generate the sqlite.html file.
#
set rcsid {$Id: lang.tcl,v 1.77 2004/11/16 23:21:57 drh Exp $}
source common.tcl







header {Query Language Understood by SQLite}
puts {
<h2>SQL As Understood By SQLite</h2>

<p>The SQLite library understands most of the standard SQL
language.  But it does <a href="omitted.html">omit some features</a>
while at the same time



|

>
>
>
>
>
>
>







1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
#
# Run this Tcl script to generate the sqlite.html file.
#
set rcsid {$Id: lang.tcl,v 1.78 2004/11/19 11:59:24 danielk1977 Exp $}
source common.tcl

if {[llength $argv]>0} {
  set outputdir [lindex $argv 0]
} else {
  set outputdir ""
}

header {Query Language Understood by SQLite}
puts {
<h2>SQL As Understood By SQLite</h2>

<p>The SQLite library understands most of the standard SQL
language.  But it does <a href="omitted.html">omit some features</a>
while at the same time
24
25
26
27
28
29
30
31











32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80



































81
82
83
84
85
86
87
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>
<p><ul>
}












foreach {section} [lsort -index 0 -dictionary {
  {{CREATE TABLE} #createtable}
  {{CREATE INDEX} #createindex}
  {VACUUM #vacuum}
  {{DROP TABLE} #droptable}
  {{DROP INDEX} #dropindex}
  {INSERT #insert}
  {REPLACE #replace}
  {DELETE #delete}
  {UPDATE #update}
  {SELECT #select}
  {comment #comment}
  {COPY #copy}
  {EXPLAIN #explain}
  {expression #expr}
  {{BEGIN TRANSACTION} #transaction}
  {{COMMIT TRANSACTION} #transaction}
  {{END TRANSACTION} #transaction}
  {{ROLLBACK TRANSACTION} #transaction}
  {PRAGMA pragma.html}
  {{ON CONFLICT clause} #conflict}
  {{CREATE VIEW} #createview}
  {{DROP VIEW} #dropview}
  {{CREATE TRIGGER} #createtrigger}
  {{DROP TRIGGER} #droptrigger}
  {{ATTACH DATABASE} #attach}
  {{DETACH DATABASE} #detach}
}] {
  foreach {s_title s_tag} $section {}
  puts "<li><a href=\"$s_tag\">$s_title</a></li>"
}
puts {</ul></p>

<p>Details on the implementation of each command are provided in
the sequel.</p>
}

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





































Section {ATTACH DATABASE} attach

Syntax {sql-statement} {
ATTACH [DATABASE] <database-filename> AS <database-name>
}








>
>
>
>
>
>
>
>
>
>
>

|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|

|
|
|
|
|
|
|


|



















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







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

proc slink {label} {
  if {[string match *.html $label]} {
    return $label
  }
  if {[string length $::outputdir]==0} {
    return #$label
  } else { 
    return lang_$label.html
  }
}

foreach {section} [lsort -index 0 -dictionary {
  {{CREATE TABLE} createtable}
  {{CREATE INDEX} createindex}
  {VACUUM vacuum}
  {{DROP TABLE} droptable}
  {{DROP INDEX} dropindex}
  {INSERT insert}
  {REPLACE replace}
  {DELETE delete}
  {UPDATE update}
  {SELECT select}
  {comment comment}
  {COPY copy}
  {EXPLAIN explain}
  {expression expr}
  {{BEGIN TRANSACTION} transaction}
  {{COMMIT TRANSACTION} transaction}
  {{END TRANSACTION} transaction}
  {{ROLLBACK TRANSACTION} transaction}
  {PRAGMA pragma.html}
  {{ON CONFLICT clause} conflict}
  {{CREATE VIEW} createview}
  {{DROP VIEW} dropview}
  {{CREATE TRIGGER} createtrigger}
  {{DROP TRIGGER} droptrigger}
  {{ATTACH DATABASE} attach}
  {{DETACH DATABASE} detach}
}] {
  foreach {s_title s_tag} $section {}
  puts "<li><a href=\"[slink $s_tag]\">$s_title</a></li>"
}
puts {</ul></p>

<p>Details on the implementation of each command are provided in
the sequel.</p>
}

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 outputdir

  if {[string length $outputdir]!=0} {
    if {[llength [info commands puts_standard]]>0} {
      footer $::rcsid
    }

    if {[string length $label]>0} {
      rename puts puts_standard
      proc puts {str} {
        regsub -all {href="#([a-z]+)"} $str {href="lang_\1.html"} str
        puts_standard $::section_file $str
      }
      rename footer footer_standard
      proc footer {id} {
        footer_standard $id
        rename footer ""
        rename puts ""
        rename puts_standard puts
        rename footer_standard footer
      } 
      set ::section_file [open [file join $outputdir lang_$label.html] w]
      header "SQL command \"$name\""
      puts "<h2>$name</h2>"
      return 
    }
  }
  puts "\n<hr />"
  if {$label!=""} {
    puts "<a name=\"$label\"></a>"
  }
  puts "<h1>$name</h1>\n"
}


Section {ATTACH DATABASE} attach

Syntax {sql-statement} {
ATTACH [DATABASE] <database-filename> AS <database-name>
}
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
<p> If a <database-name> is specified, then the view is created in 
the named database. It is an error to specify both a <database-name>
and the TEMP keyword, unless the <database-name> is "temp". If no
database name is specified, and the TEMP keyword is not present,
the table is created in the main database.</p>

<p>You cannot COPY, DELETE, INSERT or UPDATE a view.  Views are read-only 
in SQLite.  However, in many cases you can use a <a href="#trigger">
TRIGGER</a> on the view to accomplish the same thing.  Views are removed 
with the <a href="#dropview">DROP VIEW</a> 
command.  Non-temporary views cannot be created on tables in an attached 
database.</p>
}









|







716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
<p> If a <database-name> is specified, then the view is created in 
the named database. It is an error to specify both a <database-name>
and the TEMP keyword, unless the <database-name> is "temp". If no
database name is specified, and the TEMP keyword is not present,
the table is created in the main database.</p>

<p>You cannot COPY, DELETE, INSERT or UPDATE a view.  Views are read-only 
in SQLite.  However, in many cases you can use a <a href="#createtrigger">
TRIGGER</a> on the view to accomplish the same thing.  Views are removed 
with the <a href="#dropview">DROP VIEW</a> 
command.  Non-temporary views cannot be created on tables in an attached 
database.</p>
}


1616
1617
1618
1619
1620
1621
1622




  OID
  *ROWID
  *SQLITE_MASTER
  *SQLITE_TEMP_MASTER
}

footer $rcsid











>
>
>
>
1669
1670
1671
1672
1673
1674
1675
1676
1677
1678
1679
  OID
  *ROWID
  *SQLITE_MASTER
  *SQLITE_TEMP_MASTER
}

footer $rcsid
if {[string length $outputdir]} {
  footer $rcsid
}

Changes to www/pragma.tcl.
1
2
3
4
5
6








7
8
9
10
11
12
13
#
# Run this Tcl script to generate the pragma.html file.
#
set rcsid {$Id: pragma.tcl,v 1.3 2004/11/12 16:12:00 danielk1977 Exp $}
source common.tcl
header {Pragma statements supported by SQLite}









puts {
<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 different
different in the following important respects:



|


>
>
>
>
>
>
>
>







1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
#
# Run this Tcl script to generate the pragma.html file.
#
set rcsid {$Id: pragma.tcl,v 1.4 2004/11/19 11:59:24 danielk1977 Exp $}
source common.tcl
header {Pragma statements supported by SQLite}

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

puts {
<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 different
different in the following important respects:
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
<li><p><b>PRAGMA auto_vacuum;
       <br>PRAGMA auto_vacuum = </b><i>0 | 1</i><b>;</b></p>
    <p> Query or set the auto-vacuum flag in the database.</p>

    <p>Normally, when a transaction that deletes data from a database is
    committed, the database file remains the same size. Unused database file 
    pages are marked as such and reused later on, when data is inserted into 
    the database. In this mode the <a href="lang.html#vacuum">VACUUM</a>
    command is used to reclaim unused space.</p>

    <p>When the auto-vacuum flag is set, the database file shrinks when a
    transaction that deletes data is committed (The VACUUM command is not
    useful in a database with the auto-vacuum flag set). To support this
    functionality the database stores extra information internally, resulting
    in slightly larger database files than would otherwise be possible.</p>







|







74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
<li><p><b>PRAGMA auto_vacuum;
       <br>PRAGMA auto_vacuum = </b><i>0 | 1</i><b>;</b></p>
    <p> Query or set the auto-vacuum flag in the database.</p>

    <p>Normally, when a transaction that deletes data from a database is
    committed, the database file remains the same size. Unused database file 
    pages are marked as such and reused later on, when data is inserted into 
    the database. In this mode the <a href="lang_vacuum.html">VACUUM</a>
    command is used to reclaim unused space.</p>

    <p>When the auto-vacuum flag is set, the database file shrinks when a
    transaction that deletes data is committed (The VACUUM command is not
    useful in a database with the auto-vacuum flag set). To support this
    functionality the database stores extra information internally, resulting
    in slightly larger database files than would otherwise be possible.</p>