Documentation Source Text

Check-in [f9931b6e49]
Login

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

Overview
Comment:Continuing improvements to the SQL language documentation.
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: f9931b6e49a90c85843fcdbf309e5b04ac6ac43a
User & Date: drh 2009-12-12 23:58:01
Context
2009-12-13
22:20
Updates to the SQL language documentation - mostly to fix requirements marks. check-in: b80a69c002 user: drh tags: trunk
2009-12-12
23:58
Continuing improvements to the SQL language documentation. check-in: f9931b6e49 user: drh tags: trunk
18:29
Updates to the matrix generator so that it outputs prototype evidence comments for easy cut-and-paste into test cases. Rewrite the CASE section of the SQL expression documentation. check-in: 01a939084b user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to pages/lang.in.

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
...
581
582
583
584
585
586
587
588

589
590
591
592
593
594
595
....
1135
1136
1137
1138
1139
1140
1141
1142
1143
1144
1145
1146
1147
1148
1149
1150
1151
1152
1153
1154
1155
1156
1157
....
1159
1160
1161
1162
1163
1164
1165
1166
1167
1168
1169
1170
1171
1172
1173
1174
1175
1176
1177
1178
1179
1180
....
1390
1391
1392
1393
1394
1395
1396
1397
1398
1399
1400

1401
1402
1403
1404
1405
1406
1407
1408
....
1630
1631
1632
1633
1634
1635
1636
1637
1638
1639
1640
1641
1642
1643
1644
....
2545
2546
2547
2548
2549
2550
2551
2552
2553
2554
2555
2556
2557
2558
2559
##############################################################################
Section {ATTACH DATABASE} attach *ATTACH

BubbleDiagram attach-stmt 1
</tcl>

<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 database-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 [DETACH] statement.</p>

<p> ^You cannot create a new table with the same name as a table in 
an attached database, but you can attach a database which contains
tables whose names are duplicates of tables in the main database.  ^If
the system is not running in [shared cache mode], 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 does not require a 
<i>database-name</i> 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
database 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
database file. ^But if the host computer crashes in the middle
of a [COMMIT] where two or more database files are updated,
some of those files might get the changes where others
might not.
</p>

<p> ^There is a compile-time limit of [SQLITE_MAX_ATTACHED]
attached database files.</p>

<tcl>
###############################################################################
................................................................................
^The default collating
sequence is the collating sequence defined for that column in the
[CREATE TABLE] statement.  ^Or if no collating sequence is otherwise defined,
the built-in BINARY collating sequence is used.</p>

<p>There are no arbitrary limits on the number of indices that can be
attached to a single table.  ^The number of columns in an index is 
limited to [SQLITE_MAX_COLUMN].</p>


<p>^If the UNIQUE keyword appears between CREATE and INDEX then duplicate
index entries are not allowed.  ^Any attempt to insert a duplicate entry
will result in an error.  ^For the purposes of unique indices, all NULL values
are considered to different from all other NULL values and are thus unique.
This is one of the two possible interpretations of the SQL-92 standard
(the language in the standard is ambiguious) and is the interpretation
................................................................................
BubbleDiagram drop-index-stmt 1
</tcl>

<p>^The DROP INDEX statement removes an index added
with the [CREATE INDEX] statement.  The index is completely removed from
the disk.  The only way to recover the index is to reenter the
appropriate [CREATE INDEX] command.</p>

<p>^The DROP INDEX statement does not reduce the size of the database 
file in the default mode.
^Disk space released by the DROP INDEX command is retained for use by later
[INSERT] statements.  
^To remove free space in the database, use the [VACUUM]
command.  ^If [auto_vacuum] mode is enabled for a database then space
will be freed automatically by DROP INDEX.</p>


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

BubbleDiagram drop-table-stmt 1
</tcl>
................................................................................
<p>^The DROP TABLE statement removes a table added with the
[CREATE TABLE] statement.  The name specified is the
table name.  ^The dropped table is completely removed from the database 
schema and the disk file.  The table can not be recovered.  
^All indices and triggers
associated with the table are also deleted.</p>

<p>^The DROP TABLE statement does not reduce the size of the database 
file in the default mode.  ^Disk space released by the DROP TABLE
command is retained for reuse by subsequent [INSERT] statements.  ^To 
remove free space in the database, 
use the [VACUUM] statement.
^If [auto_vacuum] mode is enabled for a database then space
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>

<p>^If [foreign key constraints] are enabled, a DROP TABLE command performs an
implicit [DELETE | DELETE FROM &lt;tbl&gt;] command before removing the
table from the database schema. ^Any triggers attached to the table are
dropped from the database schema before the implicit DELETE FROM &lt;tbl&gt; 
................................................................................
<p>^The LIKE operator does a pattern matching comparison. ^The operand
to the right of the LIKE operator contains the pattern and the left hand
operand contains the string to match against the pattern.

<tcl>hd_puts "^A percent symbol (\"%\") in the LIKE pattern matches any
sequence of zero or more characters in the string.  ^An underscore
(\"_\") in the LIKE pattern matches any single character in the
string.  ^Any other character matches itself or it's lower/upper case
equivalent (i.e. case-insensitive matching).  (A bug: ^SQLite only
understands upper/lower case for ASCII characters.  ^The
LIKE operator is case sensitive for unicode characters that are beyond

the ASCII range.  For example, ^the expression <b>'a'&nbsp;LIKE&nbsp;'A'</b>
is TRUE but <b>'&aelig;'&nbsp;LIKE&nbsp;'&AElig;'</b> is FALSE.)</p>"</tcl>

<p>^If the optional ESCAPE clause is present, then the expression
following the ESCAPE keyword must evaluate to a string consisting of
a single character. ^This character may be used in the LIKE pattern
to include literal percent or underscore characters. ^The escape
character followed by a percent symbol, underscore or itself matches a
................................................................................
  of the last row insert from the database connection which invoked the
  function.
  ^The last_insert_rowid() SQL function is a wrapper around the
  [sqlite3_last_insert_rowid()] C/C++ interface function.
}

funcdef {length(X)} {} {
  ^The length(X) function returns the string length of X in 
  characters if X is a string, or in bytes if X is a blob.
  ^If X is NULL then length(X) is NULL.
  ^If X is numeric then length(X) returns the length of a string
  representation of X.
}

funcdef {like(X,Y) like(X,Y,Z)} {} {
................................................................................
<tcl>
##############################################################################
Section VACUUM vacuum VACUUM

BubbleDiagram vacuum-stmt 1
</tcl>

<p>^When an object (table, index, or trigger) is dropped from the 
database, it leaves behind empty space. 
^This empty space will be reused the next time new information is
added to the database.  But in the meantime, the database file might
be larger than strictly necessary.  Also, frequent inserts, updates,
and deletes can cause the information in the database to become
fragmented - scrattered out all across the database file rather
than clustered together in one place.</p>







|
|

|
|








|
|
|
|






|


|


|







 







|
>







 







<
<
<
<
<
<
<
<
<







 







<
<
<
<
<
<
<
<







 







|
|
|
|
>
|







 







|







 







|







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
...
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
....
1136
1137
1138
1139
1140
1141
1142









1143
1144
1145
1146
1147
1148
1149
....
1151
1152
1153
1154
1155
1156
1157








1158
1159
1160
1161
1162
1163
1164
....
1374
1375
1376
1377
1378
1379
1380
1381
1382
1383
1384
1385
1386
1387
1388
1389
1390
1391
1392
1393
....
1615
1616
1617
1618
1619
1620
1621
1622
1623
1624
1625
1626
1627
1628
1629
....
2530
2531
2532
2533
2534
2535
2536
2537
2538
2539
2540
2541
2542
2543
2544
##############################################################################
Section {ATTACH DATABASE} attach *ATTACH

BubbleDiagram attach-stmt 1
</tcl>

<p> ^The ATTACH DATABASE statement adds another database 
file to the current [database connection]. 
^The database-names 'main' and 
'temp' refer to the main database and the database used for 
temporary tables.  ^The main and temp databases cannot be attached or
detached.</p>

<p> ^You cannot create a new table with the same name as a table in 
an attached database, but you can attach a database which contains
tables whose names are duplicates of tables in the main database.  ^If
the system is not running in [shared cache mode], 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 the name of the table is unique
across all attached databases and the main and temp databases, then the
<i>database-name</i> prefix is not required.  ^When a database is attached, 
all tables which don't have duplicate names become the default table
of that name.  ^Any tables of that name attached afterwards require the
database 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
database file. But if the host computer crashes in the middle
of a [COMMIT] where two or more database files are updated,
some of those files might get the changes where others
might not.)^
</p>

<p> ^There is a compile-time limit of [SQLITE_MAX_ATTACHED]
attached database files.</p>

<tcl>
###############################################################################
................................................................................
^The default collating
sequence is the collating sequence defined for that column in the
[CREATE TABLE] statement.  ^Or if no collating sequence is otherwise defined,
the built-in BINARY collating sequence is used.</p>

<p>There are no arbitrary limits on the number of indices that can be
attached to a single table.  ^The number of columns in an index is 
limited to the value set by
[sqlite3_limit]([SQLITE_LIMIT_COLUMN],...).</p>

<p>^If the UNIQUE keyword appears between CREATE and INDEX then duplicate
index entries are not allowed.  ^Any attempt to insert a duplicate entry
will result in an error.  ^For the purposes of unique indices, all NULL values
are considered to different from all other NULL values and are thus unique.
This is one of the two possible interpretations of the SQL-92 standard
(the language in the standard is ambiguious) and is the interpretation
................................................................................
BubbleDiagram drop-index-stmt 1
</tcl>

<p>^The DROP INDEX statement removes an index added
with the [CREATE INDEX] statement.  The index is completely removed from
the disk.  The only way to recover the index is to reenter the
appropriate [CREATE INDEX] command.</p>










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

BubbleDiagram drop-table-stmt 1
</tcl>
................................................................................
<p>^The DROP TABLE statement removes a table added with the
[CREATE TABLE] statement.  The name specified is the
table name.  ^The dropped table is completely removed from the database 
schema and the disk file.  The table can not be recovered.  
^All indices and triggers
associated with the table are also deleted.</p>









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

<p>^If [foreign key constraints] are enabled, a DROP TABLE command performs an
implicit [DELETE | DELETE FROM &lt;tbl&gt;] command before removing the
table from the database schema. ^Any triggers attached to the table are
dropped from the database schema before the implicit DELETE FROM &lt;tbl&gt; 
................................................................................
<p>^The LIKE operator does a pattern matching comparison. ^The operand
to the right of the LIKE operator contains the pattern and the left hand
operand contains the string to match against the pattern.

<tcl>hd_puts "^A percent symbol (\"%\") in the LIKE pattern matches any
sequence of zero or more characters in the string.  ^An underscore
(\"_\") in the LIKE pattern matches any single character in the
string.  ^(Any other character matches itself or it's lower/upper case
equivalent (i.e. case-insensitive matching).)^  (A bug: ^SQLite only
understands upper/lower case for ASCII characters by default.  ^The
LIKE operator is by default case sensitive for unicode characters that are
beyond the ASCII range.  For example,
^the expression <b>'a'&nbsp;LIKE&nbsp;'A'</b>
is TRUE but <b>'&aelig;'&nbsp;LIKE&nbsp;'&AElig;'</b> is FALSE.)</p>"</tcl>

<p>^If the optional ESCAPE clause is present, then the expression
following the ESCAPE keyword must evaluate to a string consisting of
a single character. ^This character may be used in the LIKE pattern
to include literal percent or underscore characters. ^The escape
character followed by a percent symbol, underscore or itself matches a
................................................................................
  of the last row insert from the database connection which invoked the
  function.
  ^The last_insert_rowid() SQL function is a wrapper around the
  [sqlite3_last_insert_rowid()] C/C++ interface function.
}

funcdef {length(X)} {} {
  ^The length(X) function returns the length of X in 
  characters if X is a string, or in bytes if X is a blob.
  ^If X is NULL then length(X) is NULL.
  ^If X is numeric then length(X) returns the length of a string
  representation of X.
}

funcdef {like(X,Y) like(X,Y,Z)} {} {
................................................................................
<tcl>
##############################################################################
Section VACUUM vacuum VACUUM

BubbleDiagram vacuum-stmt 1
</tcl>

<p>^When an object (table, index, trigger, or view) is dropped from the 
database, it leaves behind empty space. 
^This empty space will be reused the next time new information is
added to the database.  But in the meantime, the database file might
be larger than strictly necessary.  Also, frequent inserts, updates,
and deletes can cause the information in the database to become
fragmented - scrattered out all across the database file rather
than clustered together in one place.</p>