Documentation Source Text

Check-in [aa8a072b1b]
Login

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

Overview
Comment:Additional work toward denser hyperlinking in the documentation.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: aa8a072b1bdad84815f0f37279663e4500bb73f2
User & Date: drh 2007-12-20 23:01:02.000
Context
2007-12-21
22:13
Additional error diagnostics when the webserver fails. (check-in: 310ef51837 user: drh tags: trunk)
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)
Changes
Unified Diff Ignore Whitespace Patch
Changes to pages/changes.in.
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
  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







|







15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
  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+)[ a-z]*\)} $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
Changes to pages/faq.in.
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
  API should be prepared to handle SQLITE_SCHEMA errors. An example
  of one approach to this follows:</p>

  <blockquote><pre>

    int rc;
    sqlite3_stmt *pStmt;
    char zSql[] = "SELECT .....";

    do {
      /* Compile the statement from SQL. Assume success. */
      sqlite3_prepare(pDb, zSql, -1, &pStmt, 0);

      while( SQLITE_ROW==sqlite3_step(pStmt) ){
        /* Do something with the row of available data */







|







388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
  API should be prepared to handle SQLITE_SCHEMA errors. An example
  of one approach to this follows:</p>

  <blockquote><pre>

    int rc;
    sqlite3_stmt *pStmt;
    char zSql&#91;] = "SELECT .....";

    do {
      /* Compile the statement from SQL. Assume success. */
      sqlite3_prepare(pDb, zSql, -1, &pStmt, 0);

      while( SQLITE_ROW==sqlite3_step(pStmt) ){
        /* Do something with the row of available data */
Changes to pages/lang.in.
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
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>
} {alteration} {
RENAME TO <new-table-name>
} {alteration} {
ADD [COLUMN] <column-def>
}
</tcl>

<p>SQLite's version of the ALTER TABLE command allows the user to 
rename or add a new column to an existing table. It is not possible
to remove a column from a table.
</p>

<p>The RENAME TO syntax is used to rename the table identified by 
<i>[database-name.]table-name</i> to <i>new-table-name</i>. This command 
cannot be used to move a table between attached databases, only to rename 
a table within the same database.</p>

<p>If the table being renamed has triggers or indices, then these remain
attached to the table after it has been renamed. However, if there are
any view definitions, or statements executed by triggers that refer to
the table being renamed, these are not automatically modified to use the new
table name. If this is required, the triggers or view definitions must be
dropped and recreated to use the new table name by hand.
</p>


<p>The ADD [COLUMN] syntax is used to add a new column to an existing table.
The new column is always appended to the end of the list of existing columns.
<i>Column-def</i> may take any of the forms permissable in a CREATE TABLE 
statement, with the following restrictions:
<ul>
<li>The column may not have a PRIMARY KEY or UNIQUE constraint.</li>
<li>The column may not have a default value of CURRENT_TIME, CURRENT_DATE 
    or CURRENT_TIMESTAMP.</li>
<li>If a NOT NULL constraint is specified, then the column must have a
    default value other than NULL.
</ul>

<p>The execution time of the ALTER TABLE command is independent of
the amount of data in the table.  The ALTER TABLE command runs as quickly
on a table with 10 million rows as it does on a table with 1 row.
</p>

<p>After ADD COLUMN has been run on a database, that database will not
be readable by SQLite version 3.1.3 and earlier until the database
is <a href="lang_vacuum.html">VACUUM</a>ed.</p>

<tcl>
##############################################################################
Section {ANALYZE} analyze

Syntax {sql-statement} {
  ANALYZE
}
Syntax {sql-statement} {
  ANALYZE <database-name>
}







|




>





|
















|











>
|

|




















|







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
proc Keyword {name} {
  return "<font color=\"#2c2cf0\">$name</font>"
}
proc Example {text} {
  hd_puts "<blockquote><pre>$text</pre></blockquote>"
}

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

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

Syntax {sql-statement} {
ALTER TABLE [<database-name> .] <table-name> <alteration>
} {alteration} {
RENAME TO <new-table-name>
} {alteration} {
ADD [COLUMN] <column-def>
}
</tcl>

<p>SQLite's version of the ALTER TABLE command allows the user to 
rename or add a new column to an existing table. It is not possible
to remove a column from a table.
</p>

<p>The RENAME TO syntax is used to rename the table identified by 
<i>&#91;database-name.&#93;table-name</i> to <i>new-table-name</i>. This command 
cannot be used to move a table between attached databases, only to rename 
a table within the same database.</p>

<p>If the table being renamed has triggers or indices, then these remain
attached to the table after it has been renamed. However, if there are
any view definitions, or statements executed by triggers that refer to
the table being renamed, these are not automatically modified to use the new
table name. If this is required, the triggers or view definitions must be
dropped and recreated to use the new table name by hand.
</p>

<p>The ADD &#91;COLUMN&#93; syntax
is used to add a new column to an existing table.
The new column is always appended to the end of the list of existing columns.
<i>Column-def</i> may take any of the forms permissable in a [CREATE TABLE]
statement, with the following restrictions:
<ul>
<li>The column may not have a PRIMARY KEY or UNIQUE constraint.</li>
<li>The column may not have a default value of CURRENT_TIME, CURRENT_DATE 
    or CURRENT_TIMESTAMP.</li>
<li>If a NOT NULL constraint is specified, then the column must have a
    default value other than NULL.
</ul>

<p>The execution time of the ALTER TABLE command is independent of
the amount of data in the table.  The ALTER TABLE command runs as quickly
on a table with 10 million rows as it does on a table with 1 row.
</p>

<p>After ADD COLUMN has been run on a database, that database will not
be readable by SQLite version 3.1.3 and earlier until the database
is <a href="lang_vacuum.html">VACUUM</a>ed.</p>

<tcl>
##############################################################################
Section {ANALYZE} analyze ANALYZE

Syntax {sql-statement} {
  ANALYZE
}
Syntax {sql-statement} {
  ANALYZE <database-name>
}
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
additional tables with the same name pattern except with the "1"
changed to a different digit.  The <b>sqlite_stat1</b> table cannot
be <a href="lang_droptable.html">DROP</a>ped,
but all the content can be <a href="lang_delete.html">DELETE</a>d which has the
same effect.</p>

<tcl>
Section {ATTACH DATABASE} attach

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

<p>The ATTACH DATABASE statement adds another database 







|







176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
additional tables with the same name pattern except with the "1"
changed to a different digit.  The <b>sqlite_stat1</b> table cannot
be <a href="lang_droptable.html">DROP</a>ped,
but all the content can be <a href="lang_delete.html">DELETE</a>d which has the
same effect.</p>

<tcl>
Section {ATTACH DATABASE} attach ATTACH

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

<p>The ATTACH DATABASE statement adds another database 
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
the main database were ":memory:".
</p>

<p>There is a compile-time limit of 10 attached database files.</p>

<tcl>
###############################################################################
Section {BEGIN TRANSACTION} transaction

Syntax {sql-statement} {
BEGIN [ DEFERRED | IMMEDIATE | EXCLUSIVE ] [TRANSACTION [<name>]]
}
Syntax {sql-statement} {
END [TRANSACTION [<name>]]
}







|







228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
the main database were ":memory:".
</p>

<p>There is a compile-time limit of 10 attached database files.</p>

<tcl>
###############################################################################
Section {BEGIN TRANSACTION} transaction {BEGIN COMMIT ROLLBACK}

Syntax {sql-statement} {
BEGIN [ DEFERRED | IMMEDIATE | EXCLUSIVE ] [TRANSACTION [<name>]]
}
Syntax {sql-statement} {
END [TRANSACTION [<name>]]
}
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
For all of these errors, SQLite attempts to undo just the one statement
it was working on and leave changes from prior statements within the
same transaction intact and continue with the transaction.  However, 
depending on the statement being evaluated and the point at which the
error occurs, it might be necessary for SQLite to rollback and
cancel the transaction.  An application can tell which
course of action SQLite took by using the
<a href="c3ref/get_autocommit.html">sqlite3_get_autocommit()</a>
C-language interface.</p>

<p>It is recommended that applications respond to the errors
listed above by explicitly issuing a ROLLBACK command.  If the 
transaction has already been rolled back automatically
by the error response, then the ROLLBACK command will fail with an
error, but no harm is caused by this.</p>

<p>Future versions of SQLite may extend the list of errors which
might cause automatic transaction rollback.  Future versions of
SQLite might change the error response.  In particular, we may
choose to simplify the interface in future versions of SQLite by
causing the errors above to force an unconditional rollback.</p>

<tcl>
###############################################################################
Section comment comment

Syntax {comment} {<SQL-comment> | <C-comment>
} {SQL-comment} {-- <single-line>
} {C-comment} {/STAR <multiple-lines> [STAR/]
}
</tcl>








<
|















|







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
For all of these errors, SQLite attempts to undo just the one statement
it was working on and leave changes from prior statements within the
same transaction intact and continue with the transaction.  However, 
depending on the statement being evaluated and the point at which the
error occurs, it might be necessary for SQLite to rollback and
cancel the transaction.  An application can tell which
course of action SQLite took by using the

[sqlite3_get_autocommit()] C-language interface.</p>

<p>It is recommended that applications respond to the errors
listed above by explicitly issuing a ROLLBACK command.  If the 
transaction has already been rolled back automatically
by the error response, then the ROLLBACK command will fail with an
error, but no harm is caused by this.</p>

<p>Future versions of SQLite may extend the list of errors which
might cause automatic transaction rollback.  Future versions of
SQLite might change the error response.  In particular, we may
choose to simplify the interface in future versions of SQLite by
causing the errors above to force an unconditional rollback.</p>

<tcl>
###############################################################################
Section comment comment {comment comments}

Syntax {comment} {<SQL-comment> | <C-comment>
} {SQL-comment} {-- <single-line>
} {C-comment} {/STAR <multiple-lines> [STAR/]
}
</tcl>

382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
including inside expressions, and in the middle of other SQL statements.
C comments do not nest.  SQL comments inside a C comment will be ignored.
</p>


<tcl>
##############################################################################
Section {CREATE INDEX} createindex

Syntax {sql-statement} {
CREATE [UNIQUE] INDEX [IF NOT EXISTS] [<database-name> .] <index-name> 
ON <table-name> ( <column-name> [, <column-name>]* )
} {column-name} {
<name> [ COLLATE <collation-name>] [ ASC | DESC ]
}







|







383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
including inside expressions, and in the middle of other SQL statements.
C comments do not nest.  SQL comments inside a C comment will be ignored.
</p>


<tcl>
##############################################################################
Section {CREATE INDEX} createindex {{CREATE INDEX}}

Syntax {sql-statement} {
CREATE [UNIQUE] INDEX [IF NOT EXISTS] [<database-name> .] <index-name> 
ON <table-name> ( <column-name> [, <column-name>]* )
} {column-name} {
<name> [ COLLATE <collation-name>] [ ASC | DESC ]
}
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444

<p>Indexes are removed with the <a href="lang_dropindex.html">DROP INDEX</a> 
command.</p>


<tcl>
##############################################################################
Section {CREATE TABLE} {createtable}

Syntax {sql-command} {
CREATE [TEMP | TEMPORARY] TABLE [IF NOT EXISTS] [<database-name> .] <table-name> (
  <column-def> [, <column-def>]*
  [, <constraint>]*
)
} {sql-command} {







|







431
432
433
434
435
436
437
438
439
440
441
442
443
444
445

<p>Indexes are removed with the <a href="lang_dropindex.html">DROP INDEX</a> 
command.</p>


<tcl>
##############################################################################
Section {CREATE TABLE} {createtable} {{CREATE TABLE}}

Syntax {sql-command} {
CREATE [TEMP | TEMPORARY] TABLE [IF NOT EXISTS] [<database-name> .] <table-name> (
  <column-def> [, <column-def>]*
  [, <constraint>]*
)
} {sql-command} {
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
with the same name aleady exists, then this command becomes a no-op.</p>

<p>Tables are removed using the <a href="lang_droptable.html">DROP TABLE</a> 
statement.  </p>

<tcl>
##############################################################################
Section {CREATE TRIGGER} createtrigger

Syntax {sql-statement} {
CREATE [TEMP | TEMPORARY] TRIGGER [IF NOT EXISTS] <trigger-name> [ BEFORE | AFTER ]
<database-event> ON [<database-name> .] <table-name>
<trigger-action>
}








|







582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
with the same name aleady exists, then this command becomes a no-op.</p>

<p>Tables are removed using the <a href="lang_droptable.html">DROP TABLE</a> 
statement.  </p>

<tcl>
##############################################################################
Section {CREATE TRIGGER} createtrigger {{CREATE TRIGGER}}

Syntax {sql-statement} {
CREATE [TEMP | TEMPORARY] TRIGGER [IF NOT EXISTS] <trigger-name> [ BEFORE | AFTER ]
<database-event> ON [<database-name> .] <table-name>
<trigger-action>
}

737
738
739
740
741
742
743
744
745
746
747
748
749
750
751

<p>Triggers are removed using the 
<a href="lang_droptrigger.html">DROP TRIGGER</a> statement.</p>


<tcl>
###############################################################################
Section {CREATE VIEW} {createview}

Syntax {sql-command} {
CREATE [TEMP | TEMPORARY] VIEW [IF NOT EXISTS] [<database-name>.] <view-name> AS <select-statement>
}
</tcl>

<p>The CREATE VIEW command assigns a name to a pre-packaged 







|







738
739
740
741
742
743
744
745
746
747
748
749
750
751
752

<p>Triggers are removed using the 
<a href="lang_droptrigger.html">DROP TRIGGER</a> statement.</p>


<tcl>
###############################################################################
Section {CREATE VIEW} {createview} {{CREATE VIEW}}

Syntax {sql-command} {
CREATE [TEMP | TEMPORARY] VIEW [IF NOT EXISTS] [<database-name>.] <view-name> AS <select-statement>
}
</tcl>

<p>The CREATE VIEW command assigns a name to a pre-packaged 
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
<a href="lang_createtrigger.html">TRIGGER</a> on the view to accomplish 
the same thing.  Views are removed 
with the <a href="lang_dropview.html">DROP VIEW</a> 
command.</p>

<tcl>
##############################################################################
Section {CREATE VIRTUAL TABLE} {createvtab}

Syntax {sql-command} {
CREATE VIRTUAL TABLE [<database-name> .] <table-name> USING <module-name> [( <arguments> )]
}
</tcl>

<p>A virtual table is an interface to an external storage or computation







|







771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
<a href="lang_createtrigger.html">TRIGGER</a> on the view to accomplish 
the same thing.  Views are removed 
with the <a href="lang_dropview.html">DROP VIEW</a> 
command.</p>

<tcl>
##############################################################################
Section {CREATE VIRTUAL TABLE} {createvtab} {{CREATE VIRTUAL_TABLE}}

Syntax {sql-command} {
CREATE VIRTUAL TABLE [<database-name> .] <table-name> USING <module-name> [( <arguments> )]
}
</tcl>

<p>A virtual table is an interface to an external storage or computation
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
855
856

<p>A virtual table is destroyed using the ordinary
<a href="lang_droptable.html">DROP TABLE</a> statement.  There is no
DROP VIRTUAL TABLE statement.</p>

<tcl>
##############################################################################
Section DELETE delete

Syntax {sql-statement} {
DELETE FROM [<database-name> .] <table-name> [WHERE <expr>]
}
</tcl>

<p>The DELETE command is used to remove records from a table.
The command consists of the "DELETE FROM" keywords followed by
the name of the table from which records are to be removed.
</p>

<p>Without a WHERE clause, all rows of the table are removed.
If a WHERE clause is supplied, then only those rows that match
the expression are removed.</p>

<tcl>
###############################################################################
Section {DETACH DATABASE} detach

Syntax {sql-command} {
DETACH [DATABASE] <database-name>
}
</tcl>

<p>This statement detaches an additional database connection previously 
attached using the <a href="lang_attach.html">ATTACH DATABASE</a> statement.  
It is possible to have the same database file attached multiple times using 
different names, and detaching one connection to a file will leave the 
others intact.</p>

<p>This statement will fail if SQLite is in the middle of a transaction.</p>


<tcl>
##############################################################################
Section {DROP INDEX} dropindex

Syntax {sql-command} {
DROP INDEX [IF EXISTS] [<database-name> .] <index-name>
}
</tcl>

<p>The DROP INDEX statement removes an index added







|

















|

















|







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
855
856
857

<p>A virtual table is destroyed using the ordinary
<a href="lang_droptable.html">DROP TABLE</a> statement.  There is no
DROP VIRTUAL TABLE statement.</p>

<tcl>
##############################################################################
Section DELETE delete {DELETE DELETEs}

Syntax {sql-statement} {
DELETE FROM [<database-name> .] <table-name> [WHERE <expr>]
}
</tcl>

<p>The DELETE command is used to remove records from a table.
The command consists of the "DELETE FROM" keywords followed by
the name of the table from which records are to be removed.
</p>

<p>Without a WHERE clause, all rows of the table are removed.
If a WHERE clause is supplied, then only those rows that match
the expression are removed.</p>

<tcl>
###############################################################################
Section {DETACH DATABASE} detach DETACH

Syntax {sql-command} {
DETACH [DATABASE] <database-name>
}
</tcl>

<p>This statement detaches an additional database connection previously 
attached using the <a href="lang_attach.html">ATTACH DATABASE</a> statement.  
It is possible to have the same database file attached multiple times using 
different names, and detaching one connection to a file will leave the 
others intact.</p>

<p>This statement will fail if SQLite is in the middle of a transaction.</p>


<tcl>
##############################################################################
Section {DROP INDEX} dropindex {{DROP INDEX}}

Syntax {sql-command} {
DROP INDEX [IF EXISTS] [<database-name> .] <index-name>
}
</tcl>

<p>The DROP INDEX statement removes an index added
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
use the <a href="lang_vacuum.html">VACUUM</a> 
command.  If AUTOVACUUM mode is enabled for a database then space
will be freed automatically by DROP INDEX.</p>


<tcl>
##############################################################################
Section {DROP TABLE} droptable

Syntax {sql-command} {
DROP TABLE [IF EXISTS] [<database-name>.] <table-name>
}
</tcl>

<p>The DROP TABLE statement removes a table added with the <a href=







|







867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
use the <a href="lang_vacuum.html">VACUUM</a> 
command.  If AUTOVACUUM mode is enabled for a database then space
will be freed automatically by DROP INDEX.</p>


<tcl>
##############################################################################
Section {DROP TABLE} droptable {{DROP TABLE}}

Syntax {sql-command} {
DROP TABLE [IF EXISTS] [<database-name>.] <table-name>
}
</tcl>

<p>The DROP TABLE statement removes a table added with the <a href=
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
will be freed automatically by DROP TABLE.</p>

<p>The optional IF EXISTS clause suppresses the error that would normally
result if the table does not exist.</p>

<tcl>
##############################################################################
Section {DROP TRIGGER} droptrigger
Syntax {sql-statement} {
DROP TRIGGER [IF EXISTS] [<database-name> .] <trigger-name>
}
</tcl>

<p>The DROP TRIGGER statement removes a trigger created by the 
<a href="lang_createtrigger.html">CREATE TRIGGER</a> statement.  The trigger is 
deleted from the database schema. Note that triggers are automatically 
dropped when the associated table is dropped.</p>

<tcl>
##############################################################################
Section {DROP VIEW} dropview

Syntax {sql-command} {
DROP VIEW [IF EXISTS] <view-name>
}
</tcl>

<p>The DROP VIEW statement removes a view created by the <a href=
"lang_createview.html">CREATE VIEW</a> statement.  The name specified is the 
view name.  It is removed from the database schema, but no actual data 
in the underlying base tables is modified.</p>

<tcl>
##############################################################################
Section EXPLAIN explain

Syntax {sql-statement} {
EXPLAIN <sql-statement>
}
</tcl>

<p>The EXPLAIN command modifier is a non-standard extension.  The
idea comes from a similar command found in PostgreSQL, but the operation
is completely different.</p>

<p>If the EXPLAIN keyword appears before any other SQLite SQL command
then instead of actually executing the command, the SQLite library will
report back the sequence of virtual machine instructions it would have
used to execute the command had the EXPLAIN keyword not been present.
For additional information about virtual machine instructions see
the <a href="arch.html">architecture description</a> or the documentation
on <a href="opcode.html">available opcodes</a> for the virtual machine.</p>

<tcl>
##############################################################################
Section expression expr

Syntax {expr} {
<expr> <binary-op> <expr> |
<expr> [NOT] <like-op> <expr> [ESCAPE <expr>] |
<unary-op> <expr> |
( <expr> ) |
<column-name> |







|












|













|




















|







893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
will be freed automatically by DROP TABLE.</p>

<p>The optional IF EXISTS clause suppresses the error that would normally
result if the table does not exist.</p>

<tcl>
##############################################################################
Section {DROP TRIGGER} droptrigger {{DROP TRIGGER}}
Syntax {sql-statement} {
DROP TRIGGER [IF EXISTS] [<database-name> .] <trigger-name>
}
</tcl>

<p>The DROP TRIGGER statement removes a trigger created by the 
<a href="lang_createtrigger.html">CREATE TRIGGER</a> statement.  The trigger is 
deleted from the database schema. Note that triggers are automatically 
dropped when the associated table is dropped.</p>

<tcl>
##############################################################################
Section {DROP VIEW} dropview {{DROP VIEW}}

Syntax {sql-command} {
DROP VIEW [IF EXISTS] <view-name>
}
</tcl>

<p>The DROP VIEW statement removes a view created by the <a href=
"lang_createview.html">CREATE VIEW</a> statement.  The name specified is the 
view name.  It is removed from the database schema, but no actual data 
in the underlying base tables is modified.</p>

<tcl>
##############################################################################
Section EXPLAIN explain EXPLAIN

Syntax {sql-statement} {
EXPLAIN <sql-statement>
}
</tcl>

<p>The EXPLAIN command modifier is a non-standard extension.  The
idea comes from a similar command found in PostgreSQL, but the operation
is completely different.</p>

<p>If the EXPLAIN keyword appears before any other SQLite SQL command
then instead of actually executing the command, the SQLite library will
report back the sequence of virtual machine instructions it would have
used to execute the command had the EXPLAIN keyword not been present.
For additional information about virtual machine instructions see
the <a href="arch.html">architecture description</a> or the documentation
on <a href="opcode.html">available opcodes</a> for the virtual machine.</p>

<tcl>
##############################################################################
Section expression expr {expression {expression syntax}}

Syntax {expr} {
<expr> <binary-op> <expr> |
<expr> [NOT] <like-op> <expr> [ESCAPE <expr>] |
<unary-op> <expr> |
( <expr> ) |
<column-name> |
998
999
1000
1001
1002
1003
1004

1005
1006
1007
1008
1009
1010
1011
1012
1013
1014
</pre></blockquote>

<p>The COLLATE operator can be thought of as a unary postfix
operator.  The COLLATE operator has the highest precedence.
It always binds more tightly than any prefix unary operator or
any binary operator.</p>


<p>The unary operator [Operator +] is a no-op.  It can be applied
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







>


|







999
1000
1001
1002
1003
1004
1005
1006
1007
1008
1009
1010
1011
1012
1013
1014
1015
1016
</pre></blockquote>

<p>The COLLATE operator can be thought of as a unary postfix
operator.  The COLLATE operator has the highest precedence.
It always binds more tightly than any prefix unary operator or
any binary operator.</p>

<tcl>hd_puts "
<p>The unary operator [Operator +] is a no-op.  It can be applied
to strings, numbers, or blobs and it always gives as its result the
value of the operand.</p>"</tcl>

<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
1043
1044
1045
1046
1047
1048
1049
1050
1051
1052
1053
1054
1055
1056
1057
<p>
A literal value can also be the token "NULL".
</p>

<p>
A parameter specifies a placeholder in the expression for a literal
value that is filled in at runtime using the
<a href="c3ref/bind_blob.html">sqlite3_bind</a> API.
Parameters can take several forms:
</p

<blockquote>
<table class="pdf_functions">
<tr>
<td align="right" valign="top"><b>?</b><i>NNN</i></td><td width="20"></td>







|







1045
1046
1047
1048
1049
1050
1051
1052
1053
1054
1055
1056
1057
1058
1059
<p>
A literal value can also be the token "NULL".
</p>

<p>
A parameter specifies a placeholder in the expression for a literal
value that is filled in at runtime using the
[sqlite3_bind_blob() | sqlite3_bind()] APIs.
Parameters can take several forms:
</p

<blockquote>
<table class="pdf_functions">
<tr>
<td align="right" valign="top"><b>?</b><i>NNN</i></td><td width="20"></td>
1082
1083
1084
1085
1086
1087
1088
1089
1090
1091
1092
1093
1094
1095
1096
1097
1098
1099
any text at all.  This syntax is the form of a variable name in the Tcl
programming language.</td>
</tr>
</table>
</blockquote>

<p>Parameters that are not assigned values using
<a href="c3ref/bind_blob.html">sqlite3_bind</a> are treated
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







|


|







1084
1085
1086
1087
1088
1089
1090
1091
1092
1093
1094
1095
1096
1097
1098
1099
1100
1101
any text at all.  This syntax is the form of a variable name in the Tcl
programming language.</td>
</tr>
</table>
</blockquote>

<p>Parameters that are not assigned values using
[sqlite3_bind_blob() | sqlite3_bind()] are treated
as NULL.</p>

<tcl>hd_fragment like LIKE ESCAPE</tcl>
<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
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
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
1185
1186
1187
1188
1189
operator is implemented by calling the user function <a href="#likeFunc">
like(<i>X</i>,<i>Y</i>)</a>.  If an ESCAPE clause is present, it adds
a third parameter to the function call. If the functionality of LIKE can be
overridden by defining an alternative implementation of the
like() SQL function.</p>
</p>

<a name="glob"></a>
<p>The GLOB operator is similar to LIKE but uses the Unix
file globbing syntax for its wildcards.  Also, GLOB is case
sensitive, unlike LIKE.  Both GLOB and LIKE may be preceded by
the NOT keyword to invert the sense of the test.  The infix GLOB 
operator is implemented by calling the user function <a href="#globFunc">
glob(<i>X</i>,<i>Y</i>)</a> and can be modified by overriding
that function.</p>

<a name="regexp"></a>
<p>The REGEXP operator is a special syntax for the regexp()
user function.  No regexp() user function is defined by default
and so use of the REGEXP operator will normally result in an
error message.  If a user-defined function named "regexp"
is added at run-time, that function will be called in order
to implement the REGEXP operator.</p>

<a name="match"></a>
<p>The MATCH operator is a special syntax for the match()
user function.  The default match() function implementation
raises and exception and is not really useful for anything.
But extensions can override the match() function with more
helpful logic.</p>

<p>A column name can be any of the names defined in the CREATE TABLE
statement or one of the following special identifiers: "<b>ROWID</b>",
"<b>OID</b>", or "<b>_ROWID_</b>".
These special identifiers all describe the
unique integer key (the "row key") associated with every 
row of every table.
The special identifiers only refer to the row key if the CREATE TABLE
statement does not define a real column with the same name.  Row keys
act like read-only columns.  A row key can be used anywhere a regular
column can be used, except that you cannot change the value
of a row key in an UPDATE or INSERT statement.
"SELECT * ..." does not return the row key.</p>

<p>SELECT statements can appear in expressions as either the
right-hand operand of the IN operator, as a scalar quantity, or
as the operand of an EXISTS operator.
As a scalar quantity or the operand of an IN operator,
the SELECT should have only a single column in its
result.  Compound SELECTs (connected with keywords like UNION or
EXCEPT) are allowed.
With the EXISTS operator, the columns in the result set of the SELECT are
ignored and the expression returns TRUE if one or more rows exist
and FALSE if the result set is empty.
If no terms in the SELECT expression refer to value in the containing
query, then the expression is evaluated once prior to any other
processing and the result is reused as necessary.  If the SELECT expression
does contain variables from the outer query, then the SELECT is reevaluated
every time it is needed.</p>

<p>When a SELECT is the right operand of the IN operator, the IN
operator returns TRUE if the result of the left operand is any of
the values generated by the select.  The IN operator may be preceded
by the NOT keyword to invert the sense of the test.</p>








|








|







|






|





|



|


|






|


|

|
|







1127
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
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
1185
1186
1187
1188
1189
1190
1191
operator is implemented by calling the user function <a href="#likeFunc">
like(<i>X</i>,<i>Y</i>)</a>.  If an ESCAPE clause is present, it adds
a third parameter to the function call. If the functionality of LIKE can be
overridden by defining an alternative implementation of the
like() SQL function.</p>
</p>

<tcl>hd_fragment glob GLOB</tcl>
<p>The GLOB operator is similar to LIKE but uses the Unix
file globbing syntax for its wildcards.  Also, GLOB is case
sensitive, unlike LIKE.  Both GLOB and LIKE may be preceded by
the NOT keyword to invert the sense of the test.  The infix GLOB 
operator is implemented by calling the user function <a href="#globFunc">
glob(<i>X</i>,<i>Y</i>)</a> and can be modified by overriding
that function.</p>

<tcl>hd_fragment regexp REGEXP</tcl>
<p>The REGEXP operator is a special syntax for the regexp()
user function.  No regexp() user function is defined by default
and so use of the REGEXP operator will normally result in an
error message.  If a user-defined function named "regexp"
is added at run-time, that function will be called in order
to implement the REGEXP operator.</p>

<tcl>hd_fragment match MATCH</tcl>
<p>The MATCH operator is a special syntax for the match()
user function.  The default match() function implementation
raises and exception and is not really useful for anything.
But extensions can override the match() function with more
helpful logic.</p>

<p>A column name can be any of the names defined in the [CREATE TABLE]
statement or one of the following special identifiers: "<b>ROWID</b>",
"<b>OID</b>", or "<b>_ROWID_</b>".
These special identifiers all describe the
unique integer key (the "row key") associated with every 
row of every table.
The special identifiers only refer to the row key if the [CREATE TABLE]
statement does not define a real column with the same name.  Row keys
act like read-only columns.  A row key can be used anywhere a regular
column can be used, except that you cannot change the value
of a row key in an [UPDATE] or [INSERT] statement.
"SELECT * ..." does not return the row key.</p>

<p>[SELECT] statements can appear in expressions as either the
right-hand operand of the IN operator, as a scalar quantity, or
as the operand of an EXISTS operator.
As a scalar quantity or the operand of an IN operator,
the SELECT should have only a single column in its
result.  Compound SELECTs (connected with keywords like UNION or
EXCEPT) are allowed.
With the EXISTS operator, the columns in the result set of the [SELECT] are
ignored and the expression returns TRUE if one or more rows exist
and FALSE if the result set is empty.
If no terms in the [SELECT] expression refer to value in the containing
query, then the expression is evaluated once prior to any other
processing and the result is reused as necessary.  If the [SELECT] expression
does contain variables from the outer query, then the [SELECT] is reevaluated
every time it is needed.</p>

<p>When a SELECT is the right operand of the IN operator, the IN
operator returns TRUE if the result of the left operand is any of
the values generated by the select.  The IN operator may be preceded
by the NOT keyword to invert the sense of the test.</p>

1205
1206
1207
1208
1209
1210
1211
1212
1213
1214
1215
1216
1217
1218
1219
1220
1221
1222
1223
1224
1225
1226
1227
1228
1229
1230
1231
1232
1233
1234
1235
1236
1237
1238
1239
1240
1241
1242
1243
1244
their result across all rows of the result set.</p>

<a name="corefunctions"></a>
<b>Core Functions</b>

<p>The core functions shown below are available by default.  Additional
functions may be written in C and added to the database engine using
the <a href="c3ref/create_function.html">sqlite3_create_function()</a>
API.</p>

<table border=0 cellpadding=10 class="pdf_functions">
<tr>
<td valign="top" align="right" width=120>abs(<i>X</i>)</td>
<td valign="top">Return the absolute value of argument <i>X</i>.</td>
</tr>

<tr>
<td valign="top" align="right">coalesce(<i>X</i>,<i>Y</i>,...)</td>
<td valign="top">Return a copy of the first non-NULL argument.  If
all arguments are NULL then NULL is returned.  There must be at least 
2 arguments.</td>
</tr>

<tr>
<td valign="top" align="right">
<a name="globFunc"></a>
glob(<i>X</i>,<i>Y</i>)</td>
<td valign="top">This function is used to implement the
"<b>X GLOB Y</b>" syntax of SQLite.  The
<a href="c3ref/create_function.html">sqlite3_create_function()</a> 
interface can
be used to override this function and thereby change the operation
of the <a href="#globFunc">GLOB</a> operator.</td>
</tr>

<tr>
<td valign="top" align="right">ifnull(<i>X</i>,<i>Y</i>)</td>
<td valign="top">Return a copy of the first non-NULL argument.  If
both arguments are NULL then NULL is returned. This behaves the same as 
<b>coalesce()</b> above.</td>







|
<



















|
<


|







1207
1208
1209
1210
1211
1212
1213
1214

1215
1216
1217
1218
1219
1220
1221
1222
1223
1224
1225
1226
1227
1228
1229
1230
1231
1232
1233
1234

1235
1236
1237
1238
1239
1240
1241
1242
1243
1244
their result across all rows of the result set.</p>

<a name="corefunctions"></a>
<b>Core Functions</b>

<p>The core functions shown below are available by default.  Additional
functions may be written in C and added to the database engine using
the [sqlite3_create_function()] API.</p>


<table border=0 cellpadding=10 class="pdf_functions">
<tr>
<td valign="top" align="right" width=120>abs(<i>X</i>)</td>
<td valign="top">Return the absolute value of argument <i>X</i>.</td>
</tr>

<tr>
<td valign="top" align="right">coalesce(<i>X</i>,<i>Y</i>,...)</td>
<td valign="top">Return a copy of the first non-NULL argument.  If
all arguments are NULL then NULL is returned.  There must be at least 
2 arguments.</td>
</tr>

<tr>
<td valign="top" align="right">
<a name="globFunc"></a>
glob(<i>X</i>,<i>Y</i>)</td>
<td valign="top">This function is used to implement the
"<b>X GLOB Y</b>" syntax of SQLite.  The [sqlite3_create_function()]

interface can
be used to override this function and thereby change the operation
of the [GLOB] operator.</td>
</tr>

<tr>
<td valign="top" align="right">ifnull(<i>X</i>,<i>Y</i>)</td>
<td valign="top">Return a copy of the first non-NULL argument.  If
both arguments are NULL then NULL is returned. This behaves the same as 
<b>coalesce()</b> above.</td>
1269
1270
1271
1272
1273
1274
1275
1276
1277
1278
1279
1280
1281
1282
1283

<tr>
<td valign="top" align="right">
<a name="likeFunc"></a>
like(<i>X</i>,<i>Y</i>)<br>
like(<i>X</i>,<i>Y</i>,<i>Z</i>)</td>
<td valign="top">
This function is used to implement the "<b>X LIKE Y [ESCAPE Z]</b>"
syntax of SQL. If the optional ESCAPE clause is present, then the
user-function is invoked with three arguments. Otherwise, it is
invoked with two arguments only. The 
<a href="c3ref/create_function.html">
sqlite_create_function()</a> interface can be used to override this
function and thereby change the operation of the <a
href= "#like">LIKE</a> operator. When doing this, it may be important







|







1269
1270
1271
1272
1273
1274
1275
1276
1277
1278
1279
1280
1281
1282
1283

<tr>
<td valign="top" align="right">
<a name="likeFunc"></a>
like(<i>X</i>,<i>Y</i>)<br>
like(<i>X</i>,<i>Y</i>,<i>Z</i>)</td>
<td valign="top">
This function is used to implement the "<b>X LIKE Y &#91;ESCAPE Z&#93;</b>"
syntax of SQL. If the optional ESCAPE clause is present, then the
user-function is invoked with three arguments. Otherwise, it is
invoked with two arguments only. The 
<a href="c3ref/create_function.html">
sqlite_create_function()</a> interface can be used to override this
function and thereby change the operation of the <a
href= "#like">LIKE</a> operator. When doing this, it may be important
1299
1300
1301
1302
1303
1304
1305
1306
1307
1308
1309
1310
1311
1312
1313
1314
<p>This function will fail if the extension attempts to modify
or delete a SQL function or collating sequence.  The
extension can add new functions or collating sequences, but cannot
modify or delete existing functions or collating sequences because
those functions and/or collating sequences might be used elsewhere
in the currently running SQL statement.  To load an extension that
changes or deletes functions or collating sequences, use the
<a href="c3ref/load_extension.html">sqlite3_load_extension()</a>
C-language API.</p>
</tr>

<tr>
<td valign="top" align="right">lower(<i>X</i>)</td>
<td valign="top">Return a copy of string <i>X</i> will all characters
converted to lower case.  The C library <b>tolower()</b> routine is used
for the conversion, which means that this function might not







<
|







1299
1300
1301
1302
1303
1304
1305

1306
1307
1308
1309
1310
1311
1312
1313
<p>This function will fail if the extension attempts to modify
or delete a SQL function or collating sequence.  The
extension can add new functions or collating sequences, but cannot
modify or delete existing functions or collating sequences because
those functions and/or collating sequences might be used elsewhere
in the currently running SQL statement.  To load an extension that
changes or deletes functions or collating sequences, use the

[sqlite3_load_extension()] C-language API.</p>
</tr>

<tr>
<td valign="top" align="right">lower(<i>X</i>)</td>
<td valign="top">Return a copy of string <i>X</i> will all characters
converted to lower case.  The C library <b>tolower()</b> routine is used
for the conversion, which means that this function might not
1351
1352
1353
1354
1355
1356
1357
1358
1359
1360
1361
1362
1363
1364
1365

<tr>
<td valign="top" align="right">quote(<i>X</i>)</td>
<td valign="top">This routine returns a string which is the value of
its argument suitable for inclusion into another SQL statement.
Strings are surrounded by single-quotes with escapes on interior quotes
as needed.  BLOBs are encoded as hexadecimal literals.
The current implementation of VACUUM uses this function.  The function
is also useful when writing triggers to implement undo/redo functionality.
</td>
</tr>

<tr>
<td valign="top" align="right">random(*)</td>
<td valign="top">Return a pseudo-random integer







|







1350
1351
1352
1353
1354
1355
1356
1357
1358
1359
1360
1361
1362
1363
1364

<tr>
<td valign="top" align="right">quote(<i>X</i>)</td>
<td valign="top">This routine returns a string which is the value of
its argument suitable for inclusion into another SQL statement.
Strings are surrounded by single-quotes with escapes on interior quotes
as needed.  BLOBs are encoded as hexadecimal literals.
The current implementation of [VACUUM] uses this function.  The function
is also useful when writing triggers to implement undo/redo functionality.
</td>
</tr>

<tr>
<td valign="top" align="right">random(*)</td>
<td valign="top">Return a pseudo-random integer
1457
1458
1459
1460
1461
1462
1463
1464
1465
1466
1467
1468
1469
1470
1471
1472
1473
1474
1475
1476
1477
1478
1479
1480
1481
1482
1483
1484
1485
1486
1487
1488

<tr>
<td valign="top" align="right">zeroblob(<i>N</i>)</td>
<td valign="top"><a name="zeroblob"></a>
Return a BLOB consisting of N bytes of 0x00.  SQLite
manages these zeroblobs very efficiently.  Zeroblobs can be used to
reserve space for a BLOB that is later written using 
<a href="c3ref/blob_open.html">incremental BLOB I/O</a>.</td>
</tr>

</table>

<b>Date And Time Functions</b>

<p>Date and time functions are documented in the 
<a href="http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions">
SQLite Wiki</a>.</p>

<a name="aggregatefunctions"></a>
<b>Aggregate Functions</b>

<p>
The aggregate functions shown below are available by default.  Additional
aggregate functions written in C may be added using the 
<a href="c3ref/create_function.html">sqlite3_create_function()</a>
API.</p>

<p>
In any aggregate function that takes a single argument, that argument
can be preceeded by the keyword DISTINCT.  In such cases, duplicate
elements are filtered before being passed into the aggregate function.
For example, the function "count(distinct X)" will return the number







|
















|







1456
1457
1458
1459
1460
1461
1462
1463
1464
1465
1466
1467
1468
1469
1470
1471
1472
1473
1474
1475
1476
1477
1478
1479
1480
1481
1482
1483
1484
1485
1486
1487

<tr>
<td valign="top" align="right">zeroblob(<i>N</i>)</td>
<td valign="top"><a name="zeroblob"></a>
Return a BLOB consisting of N bytes of 0x00.  SQLite
manages these zeroblobs very efficiently.  Zeroblobs can be used to
reserve space for a BLOB that is later written using 
[sqlite3_blob_open() | incremental BLOB I/O].</td>
</tr>

</table>

<b>Date And Time Functions</b>

<p>Date and time functions are documented in the 
<a href="http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions">
SQLite Wiki</a>.</p>

<a name="aggregatefunctions"></a>
<b>Aggregate Functions</b>

<p>
The aggregate functions shown below are available by default.  Additional
aggregate functions written in C may be added using the 
[sqlite3_create_function()]</a>
API.</p>

<p>
In any aggregate function that takes a single argument, that argument
can be preceeded by the keyword DISTINCT.  In such cases, duplicate
elements are filtered before being passed into the aggregate function.
For example, the function "count(distinct X)" will return the number
1552
1553
1554
1555
1556
1557
1558
1559
1560
1561
1562
1563
1564
1565
1566
   and an integer overflow occurs at any point during the computation.
   Total() never throws an exception.</p>
</tr>
</table>

<tcl>
##############################################################################
Section INSERT insert

Syntax {sql-statement} {
INSERT [OR <conflict-algorithm>] INTO [<database-name> .] <table-name> [(<column-list>)] VALUES(<value-list>) |
INSERT [OR <conflict-algorithm>] INTO [<database-name> .] <table-name> [(<column-list>)] <select-statement>
}
</tcl>








|







1551
1552
1553
1554
1555
1556
1557
1558
1559
1560
1561
1562
1563
1564
1565
   and an integer overflow occurs at any point during the computation.
   Total() never throws an exception.</p>
</tr>
</table>

<tcl>
##############################################################################
Section INSERT insert {INSERT INSERTs}

Syntax {sql-statement} {
INSERT [OR <conflict-algorithm>] INTO [<database-name> .] <table-name> [(<column-list>)] VALUES(<value-list>) |
INSERT [OR <conflict-algorithm>] INTO [<database-name> .] <table-name> [(<column-list>)] <select-statement>
}
</tcl>

1589
1590
1591
1592
1593
1594
1595
1596
1597
1598
1599
1600
1601
1602
1603
For compatibility with MySQL, the parser allows the use of the
single keyword <a href="lang_replace.html">REPLACE</a> as an 
alias for "INSERT OR REPLACE".
</p>

<tcl>
##############################################################################
Section {ON CONFLICT clause} conflict

Syntax {conflict-clause} {
ON CONFLICT <conflict-algorithm>
} {conflict-algorithm} {
ROLLBACK | ABORT | FAIL | IGNORE | REPLACE
}
</tcl>







|







1588
1589
1590
1591
1592
1593
1594
1595
1596
1597
1598
1599
1600
1601
1602
For compatibility with MySQL, the parser allows the use of the
single keyword <a href="lang_replace.html">REPLACE</a> as an 
alias for "INSERT OR REPLACE".
</p>

<tcl>
##############################################################################
Section {ON CONFLICT clause} conflict {{conflict clause}}

Syntax {conflict-clause} {
ON CONFLICT <conflict-algorithm>
} {conflict-algorithm} {
ROLLBACK | ABORT | FAIL | IGNORE | REPLACE
}
</tcl>
1668
1669
1670
1671
1672
1673
1674
1675
1676
1677
1678
1679
1680
1681
1682
1683
1684
1685
1686
1687
1688
1689
1690
1691

1692
1693
1694
1695
1696
1697
1698
1699
1700
1701
1702
1703
1704
1705
1706
1707
1708
1709
1710
1711
1712
1713
1714
1715
1716
1717
1718
1719
1720
1721
1722
1723
1724
1725
1726
1727
1728

<p>The algorithm specified in the OR clause of a INSERT or UPDATE
overrides any algorithm specified in a CREATE TABLE.
If no algorithm is specified anywhere, the ABORT algorithm is used.</p>

<tcl>
##############################################################################
Section REINDEX reindex

Syntax {sql-statement} {
  REINDEX <collation name>
}
Syntax {sql-statement} {
  REINDEX [<database-name> .] <table/index-name>
}
</tcl>

<p>The REINDEX command is used to delete and recreate indices from scratch.
This is useful when the definition of a collation sequence has changed.
</p>

<p>In the first form, all indices in all attached databases that use the
named collation sequence are recreated. In the second form, if 
<i>[database-name.]table/index-name</i> identifies a table, then all indices

associated with the table are rebuilt. If an index is identified, then only
this specific index is deleted and recreated.
</p>

<p>If no <i>database-name</i> is specified and there exists both a table or
index and a collation sequence of the specified name, then indices associated
with the collation sequence only are reconstructed. This ambiguity may be
dispelled by always specifying a <i>database-name</i> when reindexing a
specific table or index.

<tcl>
###############################################################################
Section REPLACE replace

Syntax {sql-statement} {
REPLACE INTO [<database-name> .] <table-name> [( <column-list> )] VALUES ( <value-list> ) |
REPLACE INTO [<database-name> .] <table-name> [( <column-list> )] <select-statement>
}
</tcl>

<p>The REPLACE command is an alias for the "INSERT OR REPLACE" variant
of the <a href="lang_insert.html">INSERT</a> command.  
This alias is provided for
compatibility with MySQL.  See the 
<a href="lang_insert.html">INSERT</a> command documentation for additional
information.</p>  

<tcl>
###############################################################################
Section SELECT select

Syntax {sql-statement} {
SELECT [ALL | DISTINCT] <result> [FROM <table-list>]
[WHERE <expr>]
[GROUP BY <expr-list>]
[HAVING <expr>]
[<compound-op> <select>]*







|















|
>












|
















|







1667
1668
1669
1670
1671
1672
1673
1674
1675
1676
1677
1678
1679
1680
1681
1682
1683
1684
1685
1686
1687
1688
1689
1690
1691
1692
1693
1694
1695
1696
1697
1698
1699
1700
1701
1702
1703
1704
1705
1706
1707
1708
1709
1710
1711
1712
1713
1714
1715
1716
1717
1718
1719
1720
1721
1722
1723
1724
1725
1726
1727
1728

<p>The algorithm specified in the OR clause of a INSERT or UPDATE
overrides any algorithm specified in a CREATE TABLE.
If no algorithm is specified anywhere, the ABORT algorithm is used.</p>

<tcl>
##############################################################################
Section REINDEX reindex REINDEX

Syntax {sql-statement} {
  REINDEX <collation name>
}
Syntax {sql-statement} {
  REINDEX [<database-name> .] <table/index-name>
}
</tcl>

<p>The REINDEX command is used to delete and recreate indices from scratch.
This is useful when the definition of a collation sequence has changed.
</p>

<p>In the first form, all indices in all attached databases that use the
named collation sequence are recreated. In the second form, if 
<i>&#91;database-name.&#93;table/index-name</i> identifies a table,
then all indices
associated with the table are rebuilt. If an index is identified, then only
this specific index is deleted and recreated.
</p>

<p>If no <i>database-name</i> is specified and there exists both a table or
index and a collation sequence of the specified name, then indices associated
with the collation sequence only are reconstructed. This ambiguity may be
dispelled by always specifying a <i>database-name</i> when reindexing a
specific table or index.

<tcl>
###############################################################################
Section REPLACE replace REPLACE

Syntax {sql-statement} {
REPLACE INTO [<database-name> .] <table-name> [( <column-list> )] VALUES ( <value-list> ) |
REPLACE INTO [<database-name> .] <table-name> [( <column-list> )] <select-statement>
}
</tcl>

<p>The REPLACE command is an alias for the "INSERT OR REPLACE" variant
of the <a href="lang_insert.html">INSERT</a> command.  
This alias is provided for
compatibility with MySQL.  See the 
<a href="lang_insert.html">INSERT</a> command documentation for additional
information.</p>  

<tcl>
###############################################################################
Section SELECT select {SELECT query}

Syntax {sql-statement} {
SELECT [ALL | DISTINCT] <result> [FROM <table-list>]
[WHERE <expr>]
[GROUP BY <expr-list>]
[HAVING <expr>]
[<compound-op> <select>]*
1844
1845
1846
1847
1848
1849
1850
1851
1852
1853
1854
1855
1856
1857
1858
left and right SELECTs.  EXCEPT takes the result of left SELECT after
removing the results of the right SELECT.  When three or more SELECTs
are connected into a compound, they group from left to right.</p>


<tcl>
##############################################################################
Section UPDATE update

Syntax {sql-statement} {
UPDATE [ OR <conflict-algorithm> ] [<database-name> .] <table-name>
SET <assignment> [, <assignment>]*
[WHERE <expr>]
} {assignment} {
<column-name> = <expr>







|







1844
1845
1846
1847
1848
1849
1850
1851
1852
1853
1854
1855
1856
1857
1858
left and right SELECTs.  EXCEPT takes the result of left SELECT after
removing the results of the right SELECT.  When three or more SELECTs
are connected into a compound, they group from left to right.</p>


<tcl>
##############################################################################
Section UPDATE update {UPDATE UPDATEs}

Syntax {sql-statement} {
UPDATE [ OR <conflict-algorithm> ] [<database-name> .] <table-name>
SET <assignment> [, <assignment>]*
[WHERE <expr>]
} {assignment} {
<column-name> = <expr>
1870
1871
1872
1873
1874
1875
1876
1877
1878
1879
1880
1881
1882
1883
1884
constraint conflict resolution algorithm to use during this one command.
See the section titled
<a href="lang_conflict.html">ON CONFLICT</a> for additional information.</p>


<tcl>
##############################################################################
Section VACUUM vacuum

Syntax {sql-statement} {
VACUUM
}
</tcl>

<p>The VACUUM command is an SQLite extension modeled after a similar







|







1870
1871
1872
1873
1874
1875
1876
1877
1878
1879
1880
1881
1882
1883
1884
constraint conflict resolution algorithm to use during this one command.
See the section titled
<a href="lang_conflict.html">ON CONFLICT</a> for additional information.</p>


<tcl>
##############################################################################
Section VACUUM vacuum VACUUM

Syntax {sql-statement} {
VACUUM
}
</tcl>

<p>The VACUUM command is an SQLite extension modeled after a similar
2043
2044
2045
2046
2047
2048
2049
2050
2051
2052
2053
2054
2055
2056
2057
   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.







|







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

hd_puts {<DIV class="pdf_section">}
Section {SQLite Keywords} keywords {{SQL keyword} {SQL 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.
2068
2069
2070
2071
2072
2073
2074
2075
2076
2077
2078
2079
2080
2081
2082
	<td>A keyword in single quotes is interpreted as a literal string
        if it occurs in a context where a string literal is allowed, otherwise
	it is understood as an identifier.</td></tr>
<tr>	<td valign="top"><b>"keyword"</b></td><td></td>
	<td>A keyword in double-quotes is interpreted as an identifier if
        it matches a known identifier.  Otherwise it is interpreted as a
        string literal.</td></tr>
<tr>	<td valign="top"><b>[keyword]</b></td><td></td>
	<td>A keyword enclosed in square brackets is always understood as
        an identifier.  This is not standard SQL.  This quoting mechanism
        is used by MS Access and SQL Server and is included in SQLite for
        compatibility.</td></tr>
</table>
</blockquote>
</p>







|







2068
2069
2070
2071
2072
2073
2074
2075
2076
2077
2078
2079
2080
2081
2082
	<td>A keyword in single quotes is interpreted as a literal string
        if it occurs in a context where a string literal is allowed, otherwise
	it is understood as an identifier.</td></tr>
<tr>	<td valign="top"><b>"keyword"</b></td><td></td>
	<td>A keyword in double-quotes is interpreted as an identifier if
        it matches a known identifier.  Otherwise it is interpreted as a
        string literal.</td></tr>
<tr>	<td valign="top"><b>&#91;keyword&#93;</b></td><td></td>
	<td>A keyword enclosed in square brackets is always understood as
        an identifier.  This is not standard SQL.  This quoting mechanism
        is used by MS Access and SQL Server and is included in SQLite for
        compatibility.</td></tr>
</table>
</blockquote>
</p>
Changes to pages/mostdeployed.in.
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
multiple copies of SQLite.  SQLite is used on servers, but
it is also used on desktop PC, and in cellphones, and PDAs,
and MP3-players, and set-top boxes.</p>

<h3>Estimates</h3>

<p>At the end of 2006, there were 100 million websites on the internet.
<a href="http://news.netcraft.com/archives/2006/11/01/november_2006_web_server_survey.html">[1]</a>
Let us use that number as a proxy for the number of deployed
SQL database engines other than SQLite.  Not every website
runs an SQL database engine.  Larger websites run multiple
database engines.  But the vast majority of smaller websites
(the long tail) share
a database engine with several other websites,
if they use database engine at all
Hopefully these two factors cancel
each other out and give 100 million as a reasonable estimate
for the number of non-SQLite SQL database engines currently
deployed.</p>

<p>Now let's consider where SQLite is used:</p>

<ul>
<li>125 million copies of Mozilla Firefox 
<a href="http://arstechnica.com/news.ars/post/20071202-mozilla-coo-over-125-million-people-use-firefox.html">[2]</a></li>
<li>20 million Mac computers, each of which contains multiple
copies of SQLite</li>
<li>20 million websites run PHP which has SQLite built in.  
<a href="http://www.php.net/usage.php">[3]</a>  We have no
way of estimating what fraction of those sites actively use
SQLite, but we think it is a significant fraction.<li>
<li>300 million downloads of the <a href="http://www.skype.com/">Skype</a>
client software and 100 million registered users
<a href="http://skypejournal.com/blog/archives/2006/04/how_many_users_does_skype_have_today.php">[4]</a>.  All recent versions of the Skype
client use SQLite internally.</li>
<li>20 million Symbian smartphones shipped in Q3 2007
<a href="http://www.symbian.com/news/pr/2007/pr20079552.html">[5]</a>
Newer versions of the SymbianOS have SQLite built in.  It is unclear
exactly how many Symbian phones actually contain SQLite, so we will
use a single quarter's sales as a lower bound.</li>
<li>Millions and millions of copies of
<a href="http://www.mcafee.com/">McAfee</a> anti-virus software all
use SQLite internally.</li>
<li>Millions of iPhones use SQLite</li>







|
















|



|




|


|







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
multiple copies of SQLite.  SQLite is used on servers, but
it is also used on desktop PC, and in cellphones, and PDAs,
and MP3-players, and set-top boxes.</p>

<h3>Estimates</h3>

<p>At the end of 2006, there were 100 million websites on the internet.
<a href="http://news.netcraft.com/archives/2006/11/01/november_2006_web_server_survey.html">&#91;1]</a>
Let us use that number as a proxy for the number of deployed
SQL database engines other than SQLite.  Not every website
runs an SQL database engine.  Larger websites run multiple
database engines.  But the vast majority of smaller websites
(the long tail) share
a database engine with several other websites,
if they use database engine at all
Hopefully these two factors cancel
each other out and give 100 million as a reasonable estimate
for the number of non-SQLite SQL database engines currently
deployed.</p>

<p>Now let's consider where SQLite is used:</p>

<ul>
<li>125 million copies of Mozilla Firefox 
<a href="http://arstechnica.com/news.ars/post/20071202-mozilla-coo-over-125-million-people-use-firefox.html">&#91;2]</a></li>
<li>20 million Mac computers, each of which contains multiple
copies of SQLite</li>
<li>20 million websites run PHP which has SQLite built in.  
<a href="http://www.php.net/usage.php">&#91;3]</a>  We have no
way of estimating what fraction of those sites actively use
SQLite, but we think it is a significant fraction.<li>
<li>300 million downloads of the <a href="http://www.skype.com/">Skype</a>
client software and 100 million registered users
<a href="http://skypejournal.com/blog/archives/2006/04/how_many_users_does_skype_have_today.php">&#91;4]</a>.  All recent versions of the Skype
client use SQLite internally.</li>
<li>20 million Symbian smartphones shipped in Q3 2007
<a href="http://www.symbian.com/news/pr/2007/pr20079552.html">&#91;5]</a>
Newer versions of the SymbianOS have SQLite built in.  It is unclear
exactly how many Symbian phones actually contain SQLite, so we will
use a single quarter's sales as a lower bound.</li>
<li>Millions and millions of copies of
<a href="http://www.mcafee.com/">McAfee</a> anti-virus software all
use SQLite internally.</li>
<li>Millions of iPhones use SQLite</li>
Changes to pages/pragma.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
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
<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
different in the following important respects:
</p>
<ul>
<li>Specific pragma statements may be removed and others added in future
    releases of SQLite. Use with caution!
<li>No error messages are generated if an unknown pragma is issued.
    Unknown pragmas are simply ignored. This means if there is a typo in 
    a pragma statement the library does not inform the user of the fact.
<li>Some pragmas take effect during the SQL compilation stage, not the
    execution stage. This means if using the C-language sqlite3_prepare(), 
    sqlite3_step(), sqlite3_finalize() API (or similar in a wrapper 
    interface), the pragma may be applied to the library during the 
    sqlite3_prepare() call.
<li>The pragma command is unlikely to be compatible with any other SQL
    engine.
</ul>

<p>The available pragmas fall into four basic categories:</p>
<ul>
<li>Pragmas used to <a href="#modify">modify the operation</a> of the 
    SQLite library in some manner, or to query for the current mode of 
    operation.
<li>Pragmas used to <a href="#schema">query the schema</a> of the current 
    database.
<li>Pragmas used to <a href="#version">query or modify the databases two 
    version values</a>, the schema-version and the user-version.
<li>Pragmas used to <a href="#debug">debug the library</a> and verify that
    database files are not corrupted.
</ul>

<tcl>
Section {PRAGMA command syntax} syntax

Syntax {sql-statement} {
PRAGMA <name> [= <value>] |
PRAGMA <function>(<arg>)
}
</tcl>

<p>The pragmas that take an integer <b><i>value</i></b> also accept 
symbolic names.  The strings "<b>on</b>", "<b>true</b>", and "<b>yes</b>" 
are equivalent to <b>1</b>.  The strings "<b>off</b>", "<b>false</b>", 
and "<b>no</b>" are equivalent to <b>0</b>.  These strings are case-
insensitive, and do not require quotes.  An unrecognized string will be 
treated as <b>1</b>, and will not generate an error.  When the <i>value</i> 
is returned it is as an integer.</p>

<tcl>Section {Pragmas to modify library operation} modify</tcl>


<ul>
<a name="pragma_auto_vacuum"></a>
<li><p><b>PRAGMA auto_vacuum;<br>
          PRAGMA auto_vacuum = </b>
            <i>0 | none | 1 | full | 2 | incremental</i><b>;</b></p>
    <p>Query or set the auto-vacuum flag in the database.</p>

    <p>Normally, (that is to say when auto_vacuum is 0 or "none")
    when a transaction that deletes data from a database is
    committed, the database file remains the same size. Unused database file 
    pages are added to a "freelist" are reused for subsequent inserts.  The
    database file does not shrink.
    In this mode the <a href="lang_vacuum.html">VACUUM</a>
    command can be used to reclaim unused space.</p>

    <p>When the auto-vacuum flag is 1 (full), the freelist pages are
    moved to the end of the file and the file is truncated to remove
    the freelist pages at every commit.
    Note, however, that auto-vacuum only truncates the freelist pages
    from the file.  Auto-vacuum does not defragment the database nor
    repack individual database pages the way that the
    <a href="lang_vacuum.html">VACUUM</a> command does.  In fact, because
    it moves pages around within the file, auto-vacuum can actually
    make fragmentation worse.</p>

    <p>Auto-vacuuming is only possible if the database stores some
    additional information that allows each database page to be
    traced backwards to its referer.  Therefore, auto-vacuuming must
    be turned on before any tables are created.  It is not possible
    to enable or disable auto-vacuum after a table has been created.</p>

    <p>When the value of auto-vacuum is 2 (incremental) then the additional
    information needed to do autovacuuming is stored in the database file
    but autovacuuming does not occur automatically at each commit as it
    does with auto_vacuum==full.  In incremental mode, the separate
    <a href="#pragma_incremental_vacuum">incremental_vacuum</a> pragma must
    be invoked to cause the vacuum to occur.</p>

    <p>The database connection can be changed between full and incremental
    autovacuum mode at will.  However, the connection cannot be changed
    in and out of the "none" mode after any table has been created in the
    database.
    </p></li>

<a name="pragma_cache_size"></a>
<li><p><b>PRAGMA cache_size;
       <br>PRAGMA cache_size = </b><i>Number-of-pages</i><b>;</b></p>
    <p>Query or change the maximum number of database disk pages that SQLite
    will hold in memory at once.  Each page uses about 1.5K of memory.
    The default cache size is 2000.  If you are doing UPDATEs or DELETEs
    that change many rows of a database and you do not mind if SQLite
    uses more memory, you can increase the cache size for a possible speed
    improvement.</p>
    <p>When you change the cache size using the cache_size pragma, the
    change only endures for the current session.  The cache size reverts
    to the default value when the database is closed and reopened.  Use
    the <a href="#pragma_default_cache_size"><b>default_cache_size</b></a> 
    pragma to check the cache size permanently.</p></li>

<a name="pragma_case_sensitive_like"></a>
<li><p><b>PRAGMA case_sensitive_like;
       <br>PRAGMA case_sensitive_like = </b><i>0 | 1</i><b>;</b></p>
    <p>The default behavior of the LIKE operator is to ignore case
    for latin1 characters. Hence, by default <b>'a' LIKE 'A'</b> is
    true.  The case_sensitive_like pragma can be turned on to change
    this behavior.  When case_sensitive_like is enabled,
    <b>'a' LIKE 'A'</b> is false but <b>'a' LIKE 'a'</b> is still true.</p>
    </li>

<a name="pragma_count_changes"></a>
<li><p><b>PRAGMA count_changes;
       <br>PRAGMA count_changes = </b><i>0 | 1</i><b>;</b></p>
    <p>Query or change the count-changes flag. Normally, when the
    count-changes flag is not set, INSERT, UPDATE and DELETE statements
    return no data. When count-changes is set, each of these commands 
    returns a single row of data consisting of one integer value - the
    number of rows inserted, modified or deleted by the command. The 
    returned change count does not include any insertions, modifications
    or deletions performed by triggers.</p>

<a name="pragma_default_cache_size"></a>
<li><p><b>PRAGMA default_cache_size;
       <br>PRAGMA default_cache_size = </b><i>Number-of-pages</i><b>;</b></p>
    <p>Query or change the maximum number of database disk pages that SQLite
    will hold in memory at once.  Each page uses 1K on disk and about
    1.5K in memory.
    This pragma works like the
    <a href="#pragma_cache_size"><b>cache_size</b></a> 
    pragma with the additional
    feature that it changes the cache size persistently.  With this pragma,
    you can set the cache size once and that setting is retained and reused
    every time you reopen the database.</p></li>

<a name="pragma_default_synchronous"></a>
<li><p><b>PRAGMA default_synchronous;</b></p>
    <p>This pragma was available in version 2.8 but was removed in version
    3.0.  It is a dangerous pragma whose use is discouraged.  To help
    dissuide users of version 2.8 from employing this pragma, the documentation
    will not tell you what it does.</p></li>


<a name="pragma_empty_result_callbacks"></a>
<li><p><b>PRAGMA empty_result_callbacks;
       <br>PRAGMA empty_result_callbacks = </b><i>0 | 1</i><b>;</b></p>
    <p>Query or change the empty-result-callbacks flag.</p> 
    <p>The empty-result-callbacks flag affects the sqlite3_exec API only.
    Normally, when the empty-result-callbacks flag is cleared, the
    callback function supplied to the sqlite3_exec() call is not invoked
    for commands that return zero rows of data. When empty-result-callbacks
    is set in this situation, the callback function is invoked exactly once,
    with the third parameter set to 0 (NULL). This is to enable programs  
    that use the sqlite3_exec() API to retrieve column-names even when
    a query returns no data.
    </p>

<a name="pragma_encoding"></a>
<li><p><b>PRAGMA encoding;
       <br>PRAGMA encoding = "UTF-8";
       <br>PRAGMA encoding = "UTF-16";
       <br>PRAGMA encoding = "UTF-16le";
       <br>PRAGMA encoding = "UTF-16be";</b></p>
    <p>In first form, if the main database has already been
    created, then this pragma returns the text encoding used by the



|


|
>
>
>



>
>
>
>
>


|

|
|









|
|

|


















|
















>


|










|








|













|








|




|






|


|


|






|



|






|





|
<





<
<
<
<
<
<

<
|



|

|



|



|







1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161

162
163
164
165
166






167

168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
<title>Pragma statements supported by SQLite</title>

<tcl>
proc Section {name {label {}} {keywords {}}} {
  hd_puts "\n<hr />"
  if {$label!=""} {
    hd_fragment $label
    if {$keywords!=""} {
      eval hd_keywords $keywords
    }
  }
  hd_puts "<h1>$name</h1>\n"
}
proc Subsection {args} {
  set f [lindex $args 0]
  hd_fragment pragma_$f
  eval hd_keywords $args
}
</tcl>

<p>The [PRAGMA] statement is a special SQL statement used to 
modify the operation of the SQLite library or to query the library for 
internal (non-table) data. The [PRAGMA] statement is issued using the same
interface as other SQLite commands (e.g. [SELECT], [INSERT]) but is
different in the following important respects:
</p>
<ul>
<li>Specific pragma statements may be removed and others added in future
    releases of SQLite. Use with caution!
<li>No error messages are generated if an unknown pragma is issued.
    Unknown pragmas are simply ignored. This means if there is a typo in 
    a pragma statement the library does not inform the user of the fact.
<li>Some pragmas take effect during the SQL compilation stage, not the
    execution stage. This means if using the C-language [sqlite3_prepare()], 
    [sqlite3_step()], [sqlite3_finalize()] API (or similar in a wrapper 
    interface), the pragma may be applied to the library during the 
    [sqlite3_prepare()] call.
<li>The pragma command is unlikely to be compatible with any other SQL
    engine.
</ul>

<p>The available pragmas fall into four basic categories:</p>
<ul>
<li>Pragmas used to <a href="#modify">modify the operation</a> of the 
    SQLite library in some manner, or to query for the current mode of 
    operation.
<li>Pragmas used to <a href="#schema">query the schema</a> of the current 
    database.
<li>Pragmas used to <a href="#version">query or modify the databases two 
    version values</a>, the schema-version and the user-version.
<li>Pragmas used to <a href="#debug">debug the library</a> and verify that
    database files are not corrupted.
</ul>

<tcl>
Section {PRAGMA command syntax} syntax {PRAGMA}

Syntax {sql-statement} {
PRAGMA <name> [= <value>] |
PRAGMA <function>(<arg>)
}
</tcl>

<p>The pragmas that take an integer <b><i>value</i></b> also accept 
symbolic names.  The strings "<b>on</b>", "<b>true</b>", and "<b>yes</b>" 
are equivalent to <b>1</b>.  The strings "<b>off</b>", "<b>false</b>", 
and "<b>no</b>" are equivalent to <b>0</b>.  These strings are case-
insensitive, and do not require quotes.  An unrecognized string will be 
treated as <b>1</b>, and will not generate an error.  When the <i>value</i> 
is returned it is as an integer.</p>

<tcl>Section {Pragmas to modify library operation} modify</tcl>
</tcl>

<ul>
<tcl>Subsection auto_vacuum</tcl>
<li><p><b>PRAGMA auto_vacuum;<br>
          PRAGMA auto_vacuum = </b>
            <i>0 | none | 1 | full | 2 | incremental</i><b>;</b></p>
    <p>Query or set the auto-vacuum flag in the database.</p>

    <p>Normally, (that is to say when auto_vacuum is 0 or "none")
    when a transaction that deletes data from a database is
    committed, the database file remains the same size. Unused database file 
    pages are added to a "freelist" are reused for subsequent inserts.  The
    database file does not shrink.
    In this mode the [VACUUM]
    command can be used to reclaim unused space.</p>

    <p>When the auto-vacuum flag is 1 (full), the freelist pages are
    moved to the end of the file and the file is truncated to remove
    the freelist pages at every commit.
    Note, however, that auto-vacuum only truncates the freelist pages
    from the file.  Auto-vacuum does not defragment the database nor
    repack individual database pages the way that the
    [VACUUM] command does.  In fact, because
    it moves pages around within the file, auto-vacuum can actually
    make fragmentation worse.</p>

    <p>Auto-vacuuming is only possible if the database stores some
    additional information that allows each database page to be
    traced backwards to its referer.  Therefore, auto-vacuuming must
    be turned on before any tables are created.  It is not possible
    to enable or disable auto-vacuum after a table has been created.</p>

    <p>When the value of auto-vacuum is 2 (incremental) then the additional
    information needed to do autovacuuming is stored in the database file
    but autovacuuming does not occur automatically at each commit as it
    does with auto_vacuum==full.  In incremental mode, the separate
    [incremental_vacuum] pragma must
    be invoked to cause the vacuum to occur.</p>

    <p>The database connection can be changed between full and incremental
    autovacuum mode at will.  However, the connection cannot be changed
    in and out of the "none" mode after any table has been created in the
    database.
    </p></li>

<tcl>Subsection cache_size</tcl>
<li><p><b>PRAGMA cache_size;
       <br>PRAGMA cache_size = </b><i>Number-of-pages</i><b>;</b></p>
    <p>Query or change the maximum number of database disk pages that SQLite
    will hold in memory at once.  Each page uses about 1.5K of memory.
    The default cache size is 2000.  If you are doing [UPDATEs] or [DELETEs]
    that change many rows of a database and you do not mind if SQLite
    uses more memory, you can increase the cache size for a possible speed
    improvement.</p>
    <p>When you change the cache size using the cache_size pragma, the
    change only endures for the current session.  The cache size reverts
    to the default value when the database is closed and reopened.  Use
    the [default_cache_size]
    pragma to check the cache size permanently.</p></li>

<tcl>Subsection case_sensitive_like</tcl>
<li><p><b>PRAGMA case_sensitive_like;
       <br>PRAGMA case_sensitive_like = </b><i>0 | 1</i><b>;</b></p>
    <p>The default behavior of the [LIKE] operator is to ignore case
    for latin1 characters. Hence, by default <b>'a' LIKE 'A'</b> is
    true.  The case_sensitive_like pragma can be turned on to change
    this behavior.  When case_sensitive_like is enabled,
    <b>'a' LIKE 'A'</b> is false but <b>'a' LIKE 'a'</b> is still true.</p>
    </li>

<tcl>Subsection count_changes</tcl>
<li><p><b>PRAGMA count_changes;
       <br>PRAGMA count_changes = </b><i>0 | 1</i><b>;</b></p>
    <p>Query or change the count-changes flag. Normally, when the
    count-changes flag is not set, [INSERT], [UPDATE] and [DELETE] statements
    return no data. When count-changes is set, each of these commands 
    returns a single row of data consisting of one integer value - the
    number of rows inserted, modified or deleted by the command. The 
    returned change count does not include any insertions, modifications
    or deletions performed by triggers.</p>

<tcl>Subsection default_cache_size</tcl>
<li><p><b>PRAGMA default_cache_size;
       <br>PRAGMA default_cache_size = </b><i>Number-of-pages</i><b>;</b></p>
    <p>Query or change the maximum number of database disk pages that SQLite
    will hold in memory at once.  Each page uses 1K on disk and about
    1.5K in memory.
    This pragma works like the [cache_size]

    pragma with the additional
    feature that it changes the cache size persistently.  With this pragma,
    you can set the cache size once and that setting is retained and reused
    every time you reopen the database.</p></li>









<tcl>Subsection empty_result_callbacks</tcl>
<li><p><b>PRAGMA empty_result_callbacks;
       <br>PRAGMA empty_result_callbacks = </b><i>0 | 1</i><b>;</b></p>
    <p>Query or change the empty-result-callbacks flag.</p> 
    <p>The empty-result-callbacks flag affects the [sqlite3_exec()] API only.
    Normally, when the empty-result-callbacks flag is cleared, the
    callback function supplied to the [sqlite3_exec()] call is not invoked
    for commands that return zero rows of data. When empty-result-callbacks
    is set in this situation, the callback function is invoked exactly once,
    with the third parameter set to 0 (NULL). This is to enable programs  
    that use the [sqlite3_exec()] API to retrieve column-names even when
    a query returns no data.
    </p>

<tcl>Subsection encoding</tcl>
<li><p><b>PRAGMA encoding;
       <br>PRAGMA encoding = "UTF-8";
       <br>PRAGMA encoding = "UTF-16";
       <br>PRAGMA encoding = "UTF-16le";
       <br>PRAGMA encoding = "UTF-16be";</b></p>
    <p>In first form, if the main database has already been
    created, then this pragma returns the text encoding used by the
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
    it is created by this session. The string "UTF-16" is interpreted
    as "UTF-16 encoding using native machine byte-ordering".  If the second
    and subsequent forms are used after the database file has already
    been created, they have no effect and are silently ignored.</p>

    <p>Once an encoding has been set for a database, it cannot be changed.</p>

    <p>Databases created by the ATTACH command always use the same encoding
    as the main database.</p>
</li>

<a name="pragma_full_column_names"></a>
<li><p><b>PRAGMA full_column_names;
       <br>PRAGMA full_column_names = </b><i>0 | 1</i><b>;</b></p>
    <p>Query or change the full-column-names flag. This flag affects
    the way SQLite names columns of data returned by SELECT statements
    when the expression for the column is a table-column name or the
    wildcard "*".  Normally, such result columns are named
    &lt;table-name/alias&gt;&lt;column-name&gt; if the SELECT statement joins 
    two or
    more tables together, or simply &lt;column-name&gt; if the SELECT
    statement queries a single table. When the full-column-names flag
    is set, such columns are always named &lt;table-name/alias&gt;
    &lt;column-name&gt; regardless of whether or not a join is performed.
    </p>
    <p>If both the short-column-names and full-column-names are set,
    then the behaviour associated with the full-column-names flag is
    exhibited.
    </p>
</li>

<a name="pragma_fullfsync"></a>
<li><p><b>PRAGMA fullfsync
       <br>PRAGMA fullfsync = </b><i>0 | 1</i><b>;</b></p>
    <p>Query or change the fullfsync flag. This flag affects
    determines whether or not the F_FULLFSYNC syncing method is used
    on systems that support it.  The default value is off.  As of this
    writing (2006-02-10) only Mac OS X supports F_FULLFSYNC.
    </p>
</li>

<a name="pragma_incremental_vacuum"></a>
<li><p><b>PRAGMA incremental_vacuum</b><i>(N)</i><b>;</b></p>
    <p>The incremental_vacuum pragma causes up to <i>N</i> pages to
    be removed from the freelist.  The database file is truncated by
    the same amount.  The incremental_vacuum pragma has no effect if
    the database is not in
    <a href="#pragma_auto_vacuum">auto_vacuum==incremental</a> mode
    or if there are no pages on the freelist.  If there are fewer than
    <i>N</i> pages on the freelist, then the entire freelist is cleared.</p>

    <p>As of version 3.4.0 (the first version that supports
    incremental_vacuum) this feature is still experimental.  Possible
    future changes include enhancing incremental vacuum to do
    defragmentation and node repacking just as the full-blown
    <a href="lang_vacuum.html">VACUUM</a> command does.  And
    incremental vacuum may be promoted from a pragma to a separate
    SQL command, or perhaps some variation on the VACUUM command.
    Programmers are cautioned to not become enamored with the
    current syntax or functionality as it is likely to change.</p>
</li>


<a name="pragma_legacy_file_format"></a>
<li><p><b>PRAGMA legacy_file_format;
       <br>PRAGMA legacy_file_format = <i>ON | OFF</i></b></p>
    <p>This pragma sets or queries the value of the legacy_file_format
    flag.  When this flag is on, new SQLite databases are created in
    a file format that is readable and writable by all versions of
    SQLite going back to 3.0.0.  When the flag is off, new databases
    are created using the latest file format which might not be
    readable or writable by older versions of SQLite.</p>

    <p>When the pragma is issued with no argument, it returns the
    setting of the flag.  This pragma does <u>not</u> tell which
    file format the current database is using.  It tells what format
    will be used by any newly created databases.</p>

    <p>This flag only affects newly created databases.  It has no
    effect on databases that already exist.</p>
</li>

<a name="pragma_locking_mode"></a>
<li><p><b>PRAGMA locking_mode;
       <br>PRAGMA locking_mode = <i>NORMAL | EXCLUSIVE</i></b></p>
    <p>This pragma sets or queries the database connection locking-mode. 
    The locking-mode is either NORMAL or EXCLUSIVE.

    <p>In NORMAL locking-mode (the default), a database connection
    unlocks the database file at the conclusion of each read or







|



|



|


|

|










|









|









|



|

|





|


















|







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
    it is created by this session. The string "UTF-16" is interpreted
    as "UTF-16 encoding using native machine byte-ordering".  If the second
    and subsequent forms are used after the database file has already
    been created, they have no effect and are silently ignored.</p>

    <p>Once an encoding has been set for a database, it cannot be changed.</p>

    <p>Databases created by the [ATTACH] command always use the same encoding
    as the main database.</p>
</li>

<tcl>Subsection full_column_names</tcl>
<li><p><b>PRAGMA full_column_names;
       <br>PRAGMA full_column_names = </b><i>0 | 1</i><b>;</b></p>
    <p>Query or change the full-column-names flag. This flag affects
    the way SQLite names columns of data returned by [SELECT] statements
    when the expression for the column is a table-column name or the
    wildcard "*".  Normally, such result columns are named
    &lt;table-name/alias&gt;&lt;column-name&gt; if the [SELECT] statement joins 
    two or
    more tables together, or simply &lt;column-name&gt; if the [SELECT]
    statement queries a single table. When the full-column-names flag
    is set, such columns are always named &lt;table-name/alias&gt;
    &lt;column-name&gt; regardless of whether or not a join is performed.
    </p>
    <p>If both the short-column-names and full-column-names are set,
    then the behaviour associated with the full-column-names flag is
    exhibited.
    </p>
</li>

<tcl>Subsection fullfsync</tcl>
<li><p><b>PRAGMA fullfsync
       <br>PRAGMA fullfsync = </b><i>0 | 1</i><b>;</b></p>
    <p>Query or change the fullfsync flag. This flag affects
    determines whether or not the F_FULLFSYNC syncing method is used
    on systems that support it.  The default value is off.  As of this
    writing (2006-02-10) only Mac OS X supports F_FULLFSYNC.
    </p>
</li>

<tcl>Subsection incremental_vacuum</tcl>
<li><p><b>PRAGMA incremental_vacuum</b><i>(N)</i><b>;</b></p>
    <p>The incremental_vacuum pragma causes up to <i>N</i> pages to
    be removed from the freelist.  The database file is truncated by
    the same amount.  The incremental_vacuum pragma has no effect if
    the database is not in
    <a href="#pragma_auto_vacuum">auto_vacuum==incremental</a> mode
    or if there are no pages on the freelist.  If there are fewer than
    <i>N</i> pages on the freelist, then the entire freelist is cleared.</p>

    <p>As of [version 3.4.0] (the first version that supports
    incremental_vacuum) this feature is still experimental.  Possible
    future changes include enhancing incremental vacuum to do
    defragmentation and node repacking just as the full-blown
    [VACUUM] command does.  And
    incremental vacuum may be promoted from a pragma to a separate
    SQL command, or perhaps some variation on the [VACUUM] command.
    Programmers are cautioned to not become enamored with the
    current syntax or functionality as it is likely to change.</p>
</li>


<tcl>Subsection legacy_file_format</tcl>
<li><p><b>PRAGMA legacy_file_format;
       <br>PRAGMA legacy_file_format = <i>ON | OFF</i></b></p>
    <p>This pragma sets or queries the value of the legacy_file_format
    flag.  When this flag is on, new SQLite databases are created in
    a file format that is readable and writable by all versions of
    SQLite going back to 3.0.0.  When the flag is off, new databases
    are created using the latest file format which might not be
    readable or writable by older versions of SQLite.</p>

    <p>When the pragma is issued with no argument, it returns the
    setting of the flag.  This pragma does <u>not</u> tell which
    file format the current database is using.  It tells what format
    will be used by any newly created databases.</p>

    <p>This flag only affects newly created databases.  It has no
    effect on databases that already exist.</p>
</li>

<tcl>Subsection locking_mode</tcl>
<li><p><b>PRAGMA locking_mode;
       <br>PRAGMA locking_mode = <i>NORMAL | EXCLUSIVE</i></b></p>
    <p>This pragma sets or queries the database connection locking-mode. 
    The locking-mode is either NORMAL or EXCLUSIVE.

    <p>In NORMAL locking-mode (the default), a database connection
    unlocks the database file at the conclusion of each read or
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
    <blockquote>
PRAGMA <b>main.</b>locking_mode=EXCLUSIVE;
    </blockquote>

    <p>Then the locking mode applies only to the named database.  If no
    database name qualifier preceeds the "locking_mode" keyword then
    the locking mode is applied to all databases, including any new
    databases added by subsequent <a href="lang_attach.html">ATTACH</a>
    commands.</p>

   <p>The "temp" database (in which TEMP tables and indices are stored)
   always uses exclusive locking mode.  The locking mode of temp cannot
   be changed.  All other databases use the normal locking mode by default
   and are affected by this pragma.</p>
</li>

<a name="pragma_page_size"></a>
<li><p><b>PRAGMA page_size;
       <br>PRAGMA page_size = </b><i>bytes</i><b>;</b></p>
    <p>Query or set the page-size of the database. The page-size
    may only be set if the database has not yet been created. The page
    size must be a power of two greater than or equal to 512 and less
    than or equal to 8192. The upper limit may be modified by setting
    the value of macro SQLITE_MAX_PAGE_SIZE during compilation.  The
    maximum upper bound is 32768.
    </p>
</li>

<a name="pragma_max_page_count"></a>
<li><p><b>PRAGMA max_page_count;
       <br>PRAGMA max_page_count = </b><i>N</i><b>;</b></p>
    <p>Query or set the maximum number of pages in the database file.
    Both forms of the pragma return the maximum page count.  The second
    form attempts to modify the maximum page count.  The maximum page
    count cannot be reduced below the current database size.
    </p>
</li>

<a name="pragma_read_uncommitted"></a>
<li><p><b>PRAGMA read_uncommitted;
       <br>PRAGMA read_uncommitted = </b><i>0 | 1</i><b>;</b></p>
    <p>Query, set, or clear READ UNCOMMITTED isolation.  The default isolation
    level for SQLite is SERIALIZABLE.  Any process or thread can select
    READ UNCOMMITTED isolation, but SERIALIZABLE will still be used except
    between connections that share a common page and schema cache.
    Cache sharing is enabled using the
    <a href="c3ref/enable_shared_cache.html">
    sqlite3_enable_shared_cache()</a> API and is only available between
    connections running the same thread.  Cache sharing is off by default.
    </p>
</li>

<a name="pragma_short_column_names"></a>
<li><p><b>PRAGMA short_column_names;
       <br>PRAGMA short_column_names = </b><i>0 | 1</i><b>;</b></p>
    <p>Query or change the short-column-names flag. This flag affects
    the way SQLite names columns of data returned by SELECT statements
    when the expression for the column is a table-column name or the
    wildcard "*".  Normally, such result columns are named
    &lt;table-name/alias&gt;lt;column-name&gt; if the SELECT statement 
    joins two or more tables together, or simply &lt;column-name&gt; if 
    the SELECT statement queries a single table. When the short-column-names 
    flag is set, such columns are always named &lt;column-name&gt; 
    regardless of whether or not a join is performed.
    </p>
    <p>If both the short-column-names and full-column-names are set,
    then the behaviour associated with the full-column-names flag is
    exhibited.
    </p>
</li>

<a name="pragma_synchronous"></a>
<li><p><b>PRAGMA synchronous;
       <br>PRAGMA synchronous = FULL; </b>(2)<b>
       <br>PRAGMA synchronous = NORMAL; </b>(1)<b>
       <br>PRAGMA synchronous = OFF; </b>(0)</p>
    <p>Query or change the setting of the "synchronous" flag.  
    The first (query) form will return the setting as an 
    integer.  When synchronous is FULL (2), the SQLite database engine will







|
<







|











|









|







<
|




|



|


|

|









|







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
    <blockquote>
PRAGMA <b>main.</b>locking_mode=EXCLUSIVE;
    </blockquote>

    <p>Then the locking mode applies only to the named database.  If no
    database name qualifier preceeds the "locking_mode" keyword then
    the locking mode is applied to all databases, including any new
    databases added by subsequent [ATTACH] commands.</p>


   <p>The "temp" database (in which TEMP tables and indices are stored)
   always uses exclusive locking mode.  The locking mode of temp cannot
   be changed.  All other databases use the normal locking mode by default
   and are affected by this pragma.</p>
</li>

<tcl>Subsection page_size</tcl>
<li><p><b>PRAGMA page_size;
       <br>PRAGMA page_size = </b><i>bytes</i><b>;</b></p>
    <p>Query or set the page-size of the database. The page-size
    may only be set if the database has not yet been created. The page
    size must be a power of two greater than or equal to 512 and less
    than or equal to 8192. The upper limit may be modified by setting
    the value of macro SQLITE_MAX_PAGE_SIZE during compilation.  The
    maximum upper bound is 32768.
    </p>
</li>

<tcl>Subsection max_page_count</tcl>
<li><p><b>PRAGMA max_page_count;
       <br>PRAGMA max_page_count = </b><i>N</i><b>;</b></p>
    <p>Query or set the maximum number of pages in the database file.
    Both forms of the pragma return the maximum page count.  The second
    form attempts to modify the maximum page count.  The maximum page
    count cannot be reduced below the current database size.
    </p>
</li>

<tcl>Subsection read_uncommitted</tcl>
<li><p><b>PRAGMA read_uncommitted;
       <br>PRAGMA read_uncommitted = </b><i>0 | 1</i><b>;</b></p>
    <p>Query, set, or clear READ UNCOMMITTED isolation.  The default isolation
    level for SQLite is SERIALIZABLE.  Any process or thread can select
    READ UNCOMMITTED isolation, but SERIALIZABLE will still be used except
    between connections that share a common page and schema cache.
    Cache sharing is enabled using the

    [sqlite3_enable_shared_cache()] API and is only available between
    connections running the same thread.  Cache sharing is off by default.
    </p>
</li>

<tcl>Subsection short_column_names</tcl>
<li><p><b>PRAGMA short_column_names;
       <br>PRAGMA short_column_names = </b><i>0 | 1</i><b>;</b></p>
    <p>Query or change the short-column-names flag. This flag affects
    the way SQLite names columns of data returned by [SELECT] statements
    when the expression for the column is a table-column name or the
    wildcard "*".  Normally, such result columns are named
    &lt;table-name/alias&gt;lt;column-name&gt; if the [SELECT] statement 
    joins two or more tables together, or simply &lt;column-name&gt; if 
    the [SELECT] statement queries a single table. When the short-column-names 
    flag is set, such columns are always named &lt;column-name&gt; 
    regardless of whether or not a join is performed.
    </p>
    <p>If both the short-column-names and full-column-names are set,
    then the behaviour associated with the full-column-names flag is
    exhibited.
    </p>
</li>

<tcl>Subsection synchronous</tcl>
<li><p><b>PRAGMA synchronous;
       <br>PRAGMA synchronous = FULL; </b>(2)<b>
       <br>PRAGMA synchronous = NORMAL; </b>(1)<b>
       <br>PRAGMA synchronous = OFF; </b>(0)</p>
    <p>Query or change the setting of the "synchronous" flag.  
    The first (query) form will return the setting as an 
    integer.  When synchronous is FULL (2), the SQLite database engine will
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
    </p>
    <p>In SQLite version 2, the default value is NORMAL. For version 3, the
    default was changed to FULL.
    </p>
</li>


<a name="pragma_temp_store"></a>
<li><p><b>PRAGMA temp_store;
       <br>PRAGMA temp_store = DEFAULT;</b> (0)<b>
       <br>PRAGMA temp_store = FILE;</b> (1)<b>
       <br>PRAGMA temp_store = MEMORY;</b> (2)</p>
    <p>Query or change the setting of the "<b>temp_store</b>" parameter.
    When temp_store is DEFAULT (0), the compile-time C preprocessor macro
    TEMP_STORE is used to determine where temporary tables and indices







|







406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
    </p>
    <p>In SQLite version 2, the default value is NORMAL. For version 3, the
    default was changed to FULL.
    </p>
</li>


<tcl>Subsection temp_store</tcl>
<li><p><b>PRAGMA temp_store;
       <br>PRAGMA temp_store = DEFAULT;</b> (0)<b>
       <br>PRAGMA temp_store = FILE;</b> (1)<b>
       <br>PRAGMA temp_store = MEMORY;</b> (2)</p>
    <p>Query or change the setting of the "<b>temp_store</b>" parameter.
    When temp_store is DEFAULT (0), the compile-time C preprocessor macro
    TEMP_STORE is used to determine where temporary tables and indices
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
        <td align="center"><em>any</em></td>
        <td align="center">memory</td></tr>
    </table>
    </blockquote>
    </li>
    <br>

<a name="pragma_temp_store_directory"></a>
<li><p><b>PRAGMA temp_store_directory;
       <br>PRAGMA temp_store_directory = 'directory-name';</b></p>
    <p>Query or change the setting of the "temp_store_directory" - the
    directory where files used for storing temporary tables and indices
    are kept.  This setting lasts for the duration of the current connection
    only and resets to its default value for each new connection opened.








|







463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
        <td align="center"><em>any</em></td>
        <td align="center">memory</td></tr>
    </table>
    </blockquote>
    </li>
    <br>

<tcl>Subsection temp_store_directory</tcl>
<li><p><b>PRAGMA temp_store_directory;
       <br>PRAGMA temp_store_directory = 'directory-name';</b></p>
    <p>Query or change the setting of the "temp_store_directory" - the
    directory where files used for storing temporary tables and indices
    are kept.  This setting lasts for the duration of the current connection
    only and resets to its default value for each new connection opened.

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
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
 
    </li>
</ul>

<tcl>Section {Pragmas to query the database schema} schema</tcl>

<ul>
<a name="pragma_database_list"></a>
<li><p><b>PRAGMA database_list;</b></p>
    <p>For each open database, invoke the callback function once with
    information about that database.  Arguments include the index and 
    the name the database was attached with.  The first row will be for 
    the main database.  The second row will be for the database used to 
    store temporary tables.</p></li>

<a name="pragma_foreign_key_list"></a>
<li><p><b>PRAGMA foreign_key_list(</b><i>table-name</i><b>);</b></p>
    <p>For each foreign key that references a column in the argument
    table, invoke the callback function with information about that
    foreign key. The callback function will be invoked once for each
    column in each foreign key.</p></li>

<a name="pragma_freelist_count"></a>
<li><p><b>PRAGMA [database].freelist_count;</b></p>
    <p>Return the number of unused pages in the database file. Running
    a <a href="#pragma_incremental_vacuum">"PRAGMA incremental_vaccum(N);"</a> 
    command with a large value of N will shrink the database file by this
    number of pages. </p></li>

<a name="pragma_index_info"></a>
<li><p><b>PRAGMA index_info(</b><i>index-name</i><b>);</b></p>
    <p>For each column that the named index references, invoke the 
    callback function
    once with information about that column, including the column name,
    and the column number.</p></li>

<a name="pragma_index_list"></a>
<li><p><b>PRAGMA index_list(</b><i>table-name</i><b>);</b></p>
    <p>For each index on the named table, invoke the callback function
    once with information about that index.  Arguments include the
    index name and a flag to indicate whether or not the index must be
    unique.</p></li>

<a name="pragma_table_info"></a>
<li><p><b>PRAGMA table_info(</b><i>table-name</i><b>);</b></p>
    <p>For each column in the named table, invoke the callback function
    once with information about that column, including the column name,
    data type, whether or not the column can be NULL, and the default
    value for the column.</p></li>
</ul>

<tcl>Section {Pragmas to query/modify version values} version</tcl>

<ul>
<a name="pragma_schema_version"></a>
<a name="pragma_user_version"></a>
<li><p><b>PRAGMA [database.]schema_version; 
       <br>PRAGMA [database.]schema_version = </b><i>integer </i><b>;
       <br>PRAGMA [database.]user_version;
       <br>PRAGMA [database.]user_version = </b><i>integer </i><b>;</b>

  
<p>    The pragmas schema_version and user_version are used to set or get
       the value of the schema-version and user-version, respectively. Both
       the schema-version and the user-version are 32-bit signed integers
       stored in the database header.</p>
  







|







|






|
|





|






|






|










|
<
|
|
|
|







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
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553

554
555
556
557
558
559
560
561
562
563
564
 
    </li>
</ul>

<tcl>Section {Pragmas to query the database schema} schema</tcl>

<ul>
<tcl>Subsection database_list</tcl>
<li><p><b>PRAGMA database_list;</b></p>
    <p>For each open database, invoke the callback function once with
    information about that database.  Arguments include the index and 
    the name the database was attached with.  The first row will be for 
    the main database.  The second row will be for the database used to 
    store temporary tables.</p></li>

<tcl>Subsection foreign_key_list</tcl>
<li><p><b>PRAGMA foreign_key_list(</b><i>table-name</i><b>);</b></p>
    <p>For each foreign key that references a column in the argument
    table, invoke the callback function with information about that
    foreign key. The callback function will be invoked once for each
    column in each foreign key.</p></li>

<tcl>Subsection freelist_count</tcl>
<li><p><b>PRAGMA &#91;database&#93;.freelist_count;</b></p>
    <p>Return the number of unused pages in the database file. Running
    a <a href="#pragma_incremental_vacuum">"PRAGMA incremental_vaccum(N);"</a> 
    command with a large value of N will shrink the database file by this
    number of pages. </p></li>

<tcl>Subsection index_info</tcl>
<li><p><b>PRAGMA index_info(</b><i>index-name</i><b>);</b></p>
    <p>For each column that the named index references, invoke the 
    callback function
    once with information about that column, including the column name,
    and the column number.</p></li>

<tcl>Subsection index_list</tcl>
<li><p><b>PRAGMA index_list(</b><i>table-name</i><b>);</b></p>
    <p>For each index on the named table, invoke the callback function
    once with information about that index.  Arguments include the
    index name and a flag to indicate whether or not the index must be
    unique.</p></li>

<tcl>Subsection table_info</tcl>
<li><p><b>PRAGMA table_info(</b><i>table-name</i><b>);</b></p>
    <p>For each column in the named table, invoke the callback function
    once with information about that column, including the column name,
    data type, whether or not the column can be NULL, and the default
    value for the column.</p></li>
</ul>

<tcl>Section {Pragmas to query/modify version values} version</tcl>

<ul>
<tcl>Subsection schema_version; Subsection user_version</tcl>

<li><p><b>PRAGMA &#91;database.&#93;schema_version; 
       <br>PRAGMA &#91;database.&#93;schema_version = </b><i>integer </i><b>;
       <br>PRAGMA &#91;database.&#93;user_version;
       <br>PRAGMA &#91;database.&#93;user_version = </b><i>integer </i><b>;</b>

  
<p>    The pragmas schema_version and user_version are used to set or get
       the value of the schema-version and user-version, respectively. Both
       the schema-version and the user-version are 32-bit signed integers
       stored in the database header.</p>
  
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
       applications for any purpose.</p>
</li>
</ul>

<tcl>Section {Pragmas to debug the library} debug</tcl>

<ul>
<a name="pragma_integrity_check"></a>
<li><p><b>PRAGMA integrity_check;
    <br>PRAGMA integrity_check(</b><i>integer</i><b>)</b></p>
    <p>The command does an integrity check of the entire database.  It
    looks for out-of-order records, missing pages, malformed records, and
    corrupt indices.
    If any problems are found, then strings are returned (as multiple
    rows with a single column per row) which describe
    the problems.  At most <i>integer</i> errors will be reported
    before the analysis quits.  The default value for <i>integer</i>
    is 100.  If no errors are found, a single row with the value "ok" is
    returned.</p></li>

<a name="pragma_parser_trace"></a>
<li><p><b>PRAGMA parser_trace = ON; </b>(1)<b>
    <br>PRAGMA parser_trace = OFF;</b> (0)</p>
    <p>Turn tracing of the SQL parser inside of the
    SQLite library on and off.  This is used for debugging.
    This only works if the library is compiled without the NDEBUG macro.
    </p></li>

<a name="pragma_vdbe_trace"></a>
<li><p><b>PRAGMA vdbe_trace = ON; </b>(1)<b>
    <br>PRAGMA vdbe_trace = OFF;</b> (0)</p>
    <p>Turn tracing of the virtual database engine inside of the
    SQLite library on and off.  This is used for debugging.  See the 
    <a href="vdbe.html#trace">VDBE documentation</a> for more 
    information.</p></li>

<a name="pragma_vdbe_listing"></a>
<li><p><b>PRAGMA vdbe_listing = ON; </b>(1)<b>
    <br>PRAGMA vdbe_listing = OFF;</b> (0)</p>
    <p>Turn listings of virtual machine programs on and off.
    With listing is on, the entire content of a program is printed
    just prior to beginning execution.  This is like automatically
    executing an EXPLAIN prior to each statement.  The statement
    executes normally after the listing is printed.
    This is used for debugging.  See the 
    <a href="vdbe.html#trace">VDBE documentation</a> for more 
    information.</p></li>
</ul>







|












|







|







|





|





576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
       applications for any purpose.</p>
</li>
</ul>

<tcl>Section {Pragmas to debug the library} debug</tcl>

<ul>
<tcl>Subsection integrity_check</tcl>
<li><p><b>PRAGMA integrity_check;
    <br>PRAGMA integrity_check(</b><i>integer</i><b>)</b></p>
    <p>The command does an integrity check of the entire database.  It
    looks for out-of-order records, missing pages, malformed records, and
    corrupt indices.
    If any problems are found, then strings are returned (as multiple
    rows with a single column per row) which describe
    the problems.  At most <i>integer</i> errors will be reported
    before the analysis quits.  The default value for <i>integer</i>
    is 100.  If no errors are found, a single row with the value "ok" is
    returned.</p></li>

<tcl>Subsection parser_trace</tcl>
<li><p><b>PRAGMA parser_trace = ON; </b>(1)<b>
    <br>PRAGMA parser_trace = OFF;</b> (0)</p>
    <p>Turn tracing of the SQL parser inside of the
    SQLite library on and off.  This is used for debugging.
    This only works if the library is compiled without the NDEBUG macro.
    </p></li>

<tcl>Subsection vdbe_trace</tcl>
<li><p><b>PRAGMA vdbe_trace = ON; </b>(1)<b>
    <br>PRAGMA vdbe_trace = OFF;</b> (0)</p>
    <p>Turn tracing of the virtual database engine inside of the
    SQLite library on and off.  This is used for debugging.  See the 
    <a href="vdbe.html#trace">VDBE documentation</a> for more 
    information.</p></li>

<tcl>Subsection vdbe_listing</tcl>
<li><p><b>PRAGMA vdbe_listing = ON; </b>(1)<b>
    <br>PRAGMA vdbe_listing = OFF;</b> (0)</p>
    <p>Turn listings of virtual machine programs on and off.
    With listing is on, the entire content of a program is printed
    just prior to beginning execution.  This is like automatically
    executing an [EXPLAIN] prior to each statement.  The statement
    executes normally after the listing is printed.
    This is used for debugging.  See the 
    <a href="vdbe.html#trace">VDBE documentation</a> for more 
    information.</p></li>
</ul>
Changes to wrap.tcl.
54
55
56
57
58
59
60

61
62
63
64
65
66
67
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







>







54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
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 {
    regsub -all {[^a-zA-Z0-9_.# -]} $kw {} kw
    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
115
116
117
118
119
120
121



122
123
124
125
126
127
128
    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







>
>
>







116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
    set lurl "#$hd(fragment)"
  }
  set fn $hd(fn-main)
  if {[info exists hd(aux)]} {
    set gurl $hd(fn-aux)
  } else {
    set gurl {}
    if {$hd(fragment)!=""} {
      set lurl $hd(fn-main)#$hd(fragment)
    }
  }
  foreach a $args {
    if {[info exists glink($a)]} {
      puts stderr "WARNING: duplicate keyword \"$a\""
    }
    if {$gurl==""} {
      set glink($a) $lurl
136
137
138
139
140
141
142
143
144
145
146

147
148
149
150
151
152
153
# 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







|



>







140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
# 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 args} {
  global hd
  set hd(fragment) $name
  puts $hd(main) "<a name=\"$name\"></a>"
  eval hd_keywords $args
}

# Write raw output to both the main file and the auxiliary.  Only write
# to files that are enabled.
#
proc hd_puts {text} {
  global hd
435
436
437
438
439
440
441


















  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
}

























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

# Generate a document showing the hyperlink keywords and their
# targets.
#
hd_open_main doc_keyword_crossref.html
hd_header {Hyperlink Crossreference} $DOC/wrap.tcl
hd_puts "<ul>"
foreach x [lsort [array names glink]] {
  set y $glink($x)
  hd_puts "<li>$x - <a href=\"$y\">$y</a></li>"
  lappend revglink($y) $x
}
hd_puts "</ul><hr><ul>"
foreach y [lsort [array names revglink]] {
  hd_puts "<li><a href=\"$y\">$y</a> - [lsort $revglink($y)]</li>"
}
hd_puts "</ul>"
hd_close_main