SQLite

Check-in [df601a7b37]
Login

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

Overview
Comment:Syntax documentation updates. (CVS 3294)
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: df601a7b374cb6ec21a2e91abf17af4fed404393
User & Date: drh 2006-06-26 10:37:12.000
Context
2006-06-26
11:17
Fix trivial compiler warnings. (CVS 3295) (check-in: 3538beace8 user: danielk1977 tags: trunk)
10:37
Syntax documentation updates. (CVS 3294) (check-in: df601a7b37 user: drh tags: trunk)
2006-06-24
12:39
(OS/2) Armor OS/2 against accidental deletion of files, too (patch by Daniel Kruse, verified by me) (CVS 3293) (check-in: f357efb373 user: pweilbacher tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to www/lang.tcl.
1
2
3
4
5
6
7
8
9
10
11
#
# Run this Tcl script to generate the lang-*.html files.
#
set rcsid {$Id: lang.tcl,v 1.113 2006/06/17 14:12:48 drh Exp $}
source common.tcl

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



|







1
2
3
4
5
6
7
8
9
10
11
#
# Run this Tcl script to generate the lang-*.html files.
#
set rcsid {$Id: lang.tcl,v 1.114 2006/06/26 10:37:12 drh Exp $}
source common.tcl

if {[llength $argv]>0} {
  set outputdir [lindex $argv 0]
} else {
  set outputdir ""
}
45
46
47
48
49
50
51

52
53
54
55
56
57
58
  } else { 
    return lang_$label.html
  }
}

foreach {section} [lsort -index 0 -dictionary {
  {{CREATE TABLE} createtable}

  {{CREATE INDEX} createindex}
  {VACUUM vacuum}
  {{DROP TABLE} droptable}
  {{DROP INDEX} dropindex}
  {INSERT insert}
  {REPLACE replace}
  {DELETE delete}







>







45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
  } else { 
    return lang_$label.html
  }
}

foreach {section} [lsort -index 0 -dictionary {
  {{CREATE TABLE} createtable}
  {{CREATE VIRTUAL TABLE} createvtab}
  {{CREATE INDEX} createindex}
  {VACUUM vacuum}
  {{DROP TABLE} droptable}
  {{DROP INDEX} dropindex}
  {INSERT insert}
  {REPLACE replace}
  {DELETE delete}
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
Section {ATTACH DATABASE} attach

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

puts {
<p>The ATTACH DATABASE statement adds a preexisting database 
file to the current database connection.  If the filename contains 
punctuation characters it must be quoted.  The names 'main' and 
'temp' refer to the main database and the database used for 
temporary tables.  These cannot be detached.  Attached databases 
are removed using the <a href="#detach">DETACH DATABASE</a> 
statement.</p>








|







222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
Section {ATTACH DATABASE} attach

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

puts {
<p>The ATTACH DATABASE statement adds another database 
file to the current database connection.  If the filename contains 
punctuation characters it must be quoted.  The names 'main' and 
'temp' refer to the main database and the database used for 
temporary tables.  These cannot be detached.  Attached databases 
are removed using the <a href="#detach">DETACH DATABASE</a> 
statement.</p>

243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
tables whose names are duplicates of tables in the main database.  It is 
also permissible to attach the same database file multiple times.</p>

<p>Tables in an attached database can be referred to using the syntax 
<i>database-name.table-name</i>.  If an attached table doesn't have 
a duplicate table name in the main database, it doesn't require a 
database name prefix.  When a database is attached, all of its 
tables which don't have duplicate names become the 'default' table
of that name.  Any tables of that name attached afterwards require the table 
prefix. If the 'default' table of a given name is detached, then 
the last table of that name attached becomes the new default.</p>

<p>
Transactions involving multiple attached databases are atomic,
assuming that the main database is not ":memory:".  If the main
database is ":memory:" then 
transactions continue to be atomic within each individual







|

|







244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
tables whose names are duplicates of tables in the main database.  It is 
also permissible to attach the same database file multiple times.</p>

<p>Tables in an attached database can be referred to using the syntax 
<i>database-name.table-name</i>.  If an attached table doesn't have 
a duplicate table name in the main database, it doesn't require a 
database name prefix.  When a database is attached, all of its 
tables which don't have duplicate names become the default table
of that name.  Any tables of that name attached afterwards require the table 
prefix. If the default table of a given name is detached, then 
the last table of that name attached becomes the new default.</p>

<p>
Transactions involving multiple attached databases are atomic,
assuming that the main database is not ":memory:".  If the main
database is ":memory:" then 
transactions continue to be atomic within each individual
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
command.</p>
}


Section {CREATE TABLE} {createtable}

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







|







484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
command.</p>
}


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} {
CREATE [TEMP | TEMPORARY] TABLE [<database-name>.] <table-name> AS <select-statement>
} {column-def} {
<name> [<type>] [[CONSTRAINT <name>] <column-constraint>]*
805
806
807
808
809
810
811


































812
813
814
815
816
817
818
<p>You cannot COPY, DELETE, INSERT or UPDATE a view.  Views are read-only 
in SQLite.  However, in many cases you can use a <a href="#createtrigger">
TRIGGER</a> on the view to accomplish the same thing.  Views are removed 
with the <a href="#dropview">DROP VIEW</a> 
command.</p>
}




































Section DELETE delete

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








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







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
<p>You cannot COPY, DELETE, INSERT or UPDATE a view.  Views are read-only 
in SQLite.  However, in many cases you can use a <a href="#createtrigger">
TRIGGER</a> on the view to accomplish the same thing.  Views are removed 
with the <a href="#dropview">DROP VIEW</a> 
command.</p>
}

Section {CREATE VIRTUAL TABLE} {createvtab}

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

puts {
<p>A virtual table is an interface to an external storage or computation
engine that appears to be a table but does not actually store information
in the database file.</p>

<p>In general, you can do anything with a virtual table that can be done
with an ordinary table, except that you cannot create triggers on a
virtual table.  Some virtual table implementations might impose additional
restrictions.  For example, many virtual tables are read-only.</p>

<p>The &lt;module-name&gt; is the name of an object that implements
the virtual table.  The &lt;module-name&gt; must be registered with
the SQLite database connection using
<a href="capi3ref.html#sqlite3_create_module">sqlite3_create_module</a>
prior to issuing the CREATE VIRTUAL TABLE statement.
The module takes zero or more comma-separated arguments.
The arguments can be just about any text as long as it has balanced
parentheses.  The argument syntax is sufficiently general that the
arguments can be made to appear as column definitions in a traditional
<a href="#createtable">CREATE TABLE</a> statement.  
SQLite passes the module arguments directly
to the module without any interpretation.  It is the responsibility
of the module implementation to parse and interpret its own arguments.</p>

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

Section DELETE delete

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

1192
1193
1194
1195
1196
1197
1198



1199
1200
1201
1202
1203
1204
1205
1206

<p>Both simple and aggregate functions are supported.  A simple
function can be used in any expression.  Simple functions return
a result immediately based on their inputs.  Aggregate functions
may only be used in a SELECT statement.  Aggregate functions compute
their result across all rows of the result set.</p>




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

<table border=0 cellpadding=10>
<tr>
<td valign="top" align="right" width=120>abs(<i>X</i>)</td>







>
>
>
|







1227
1228
1229
1230
1231
1232
1233
1234
1235
1236
1237
1238
1239
1240
1241
1242
1243
1244

<p>Both simple and aggregate functions are supported.  A simple
function can be used in any expression.  Simple functions return
a result immediately based on their inputs.  Aggregate functions
may only be used in a SELECT statement.  Aggregate functions compute
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="capi3ref.html#cfunc">sqlite3_create_function()</a>
API.</p>

<table border=0 cellpadding=10>
<tr>
<td valign="top" align="right" width=120>abs(<i>X</i>)</td>
1367
1368
1369
1370
1371
1372
1373









1374
1375
1376
1377
1378
1379
1380
<td valign="top" align="right">upper(<i>X</i>)</td>
<td valign="top">Return a copy of input string <i>X</i> converted to all
upper-case letters.  The implementation of this function uses the C library
routine <b>toupper()</b> which means it may not work correctly on 
UTF-8 strings.</td>
</tr>
</table>










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








>
>
>
>
>
>
>
>
>







1405
1406
1407
1408
1409
1410
1411
1412
1413
1414
1415
1416
1417
1418
1419
1420
1421
1422
1423
1424
1425
1426
1427
<td valign="top" align="right">upper(<i>X</i>)</td>
<td valign="top">Return a copy of input string <i>X</i> converted to all
upper-case letters.  The implementation of this function uses the C library
routine <b>toupper()</b> which means it may not work correctly on 
UTF-8 strings.</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="capi3ref.html#sqlite3_create_function">sqlite3_create_function()</a>
API.</p>