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: |
f9931b6e49a90c85843fcdbf309e5b04 |
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
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 <tbl>] 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 <tbl> ................................................................................ <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' LIKE 'A'</b> is TRUE but <b>'æ' LIKE 'Æ'</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 <tbl>] 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 <tbl> ................................................................................ <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' LIKE 'A'</b> is TRUE but <b>'æ' LIKE 'Æ'</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> |