Documentation Source Text

Check-in [8e1d4f3bb5]
Login

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

Overview
Comment:Enhance wrap.tcl to recognize <yyterm> elements in the input HTML and convert them into real HTML that renders an terminal-symbol oval around the enclosed text. Use this markup when talking about terminal symbols in the language specification.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 8e1d4f3bb5b9fc6a0f0036baf72d4cf5f38a6c95
User & Date: drh 2015-09-09 15:39:43.051
Context
2015-09-09
19:01
Add examples to the json1 documentation. (check-in: bc16b0015f user: drh tags: trunk)
15:39
Enhance wrap.tcl to recognize <yyterm> elements in the input HTML and convert them into real HTML that renders an terminal-symbol oval around the enclosed text. Use this markup when talking about terminal symbols in the language specification. (check-in: 8e1d4f3bb5 user: drh tags: trunk)
02:06
Fix a typo in the json1 documentation. (check-in: 57b4eafa71 user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to pages/lang.in.
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
</tcl>

<p>SQLite supports a limited subset of ALTER TABLE.
The ALTER TABLE command in SQLite allows the user to rename a table
or to add a new column to an existing table.
</p>

<p> ^(The RENAME TO syntax is used to rename the table identified by 
<i>&#91;schema-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







|
|







125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
</tcl>

<p>SQLite supports a limited subset of ALTER TABLE.
The ALTER TABLE command in SQLite allows the user to rename a table
or to add a new column to an existing table.
</p>

<p> ^(The RENAME TO syntax changes the name of <yyterm>table-name</yyterm>
to <yyterm>new-table-name</yyterm>.)^
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
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493

<p>
^END TRANSACTION is an alias for COMMIT.
</p>

<p> ^(Transactions created using BEGIN...COMMIT do not nest.)^
^For nested transactions, use the [SAVEPOINT] and [RELEASE] commands.
The "TO SAVEPOINT <i>name</i>" clause of the ROLLBACK command shown
in the syntax diagram above is only applicable to [SAVEPOINT]
transactions.  ^An attempt to invoke the BEGIN command within
a transaction will fail with an error, regardless of whether
the transaction was started by [SAVEPOINT] or a prior BEGIN.
^The COMMIT command and the ROLLBACK command without the TO clause
work the same on [SAVEPOINT] transactions as they do with transactions
started by BEGIN.</p>







|







479
480
481
482
483
484
485
486
487
488
489
490
491
492
493

<p>
^END TRANSACTION is an alias for COMMIT.
</p>

<p> ^(Transactions created using BEGIN...COMMIT do not nest.)^
^For nested transactions, use the [SAVEPOINT] and [RELEASE] commands.
The "TO SAVEPOINT <yyterm>name</yyterm>" clause of the ROLLBACK command shown
in the syntax diagram above is only applicable to [SAVEPOINT]
transactions.  ^An attempt to invoke the BEGIN command within
a transaction will fail with an error, regardless of whether
the transaction was started by [SAVEPOINT] or a prior BEGIN.
^The COMMIT command and the ROLLBACK command without the TO clause
work the same on [SAVEPOINT] transactions as they do with transactions
started by BEGIN.</p>
843
844
845
846
847
848
849
850
851
852
853
854
855

856
857
858
859
860
861
862
863
</ul>

<p>Every CREATE TABLE statement must specify a name for the new table.
  ^(Table names that begin with "sqlite_" are reserved for internal use. It
  is an error to attempt to create a table with a name that starts with
  "sqlite_".)^

<p> ^If a &lt;schema-name&gt; is specified, it must be either "main", 
  "temp", or the name of an [ATTACH DATABASE|attached database]. ^In this case
  the new table is created in the named database. ^If the "TEMP" or "TEMPORARY"
  keyword occurs between the "CREATE" and "TABLE" then the new table is
  created in the temp database. ^It is an error to specify both a 
  &lt;schema-name&gt; and the TEMP or TEMPORARY keyword, unless the

  &lt;schema-name&gt; is "temp". ^If no schema name is specified and the
  TEMP keyword is not present then the table is created in the main
  database.

<p>
  ^It is usually an error to attempt to create a new table in a database that
  already contains a table, index or view of the same name. ^However, if the
  "IF NOT EXISTS" clause is specified as part of the CREATE TABLE statement and







|




|
>
|







843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
</ul>

<p>Every CREATE TABLE statement must specify a name for the new table.
  ^(Table names that begin with "sqlite_" are reserved for internal use. It
  is an error to attempt to create a table with a name that starts with
  "sqlite_".)^

<p> ^If a <yyterm>schema-name</yyterm> is specified, it must be either "main", 
  "temp", or the name of an [ATTACH DATABASE|attached database]. ^In this case
  the new table is created in the named database. ^If the "TEMP" or "TEMPORARY"
  keyword occurs between the "CREATE" and "TABLE" then the new table is
  created in the temp database. ^It is an error to specify both a 
  <yyterm>schema-name</yyterm> and the TEMP or TEMPORARY keyword, unless the
  <yyterm>schema-name</yyterm> is "temp". 
  ^If no schema name is specified and the
  TEMP keyword is not present then the table is created in the main
  database.

<p>
  ^It is usually an error to attempt to create a new table in a database that
  already contains a table, index or view of the same name. ^However, if the
  "IF NOT EXISTS" clause is specified as part of the CREATE TABLE statement and
917
918
919
920
921
922
923

924
925
926
927
928
929
930
described under [constraints|SQL Data Constraints] below.

<p>^Unlike most SQL databases, SQLite does not restrict the type of data that
may be inserted into a column based on the columns declared type. Instead,
SQLite uses [dynamic typing]. ^The declared type of a column is used to
determine the [affinity] of the column only.


<p>The DEFAULT clause specifies a default value to use for the column if no
value is explicitly provided by the user when doing an [INSERT]. ^If there
is no explicit DEFAULT clause attached to a column definition, then the 
default value of the column is NULL. ^(An explicit DEFAULT clause may specify
that the default value is NULL, a string constant, a blob constant, a
signed-number, or any constant expression enclosed in parentheses. A
default value may also be one of the special case-independent keywords







>







918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
described under [constraints|SQL Data Constraints] below.

<p>^Unlike most SQL databases, SQLite does not restrict the type of data that
may be inserted into a column based on the columns declared type. Instead,
SQLite uses [dynamic typing]. ^The declared type of a column is used to
determine the [affinity] of the column only.

<tcl>hd_fragment dfltval {default column value} {default value}</tcl>
<p>The DEFAULT clause specifies a default value to use for the column if no
value is explicitly provided by the user when doing an [INSERT]. ^If there
is no explicit DEFAULT clause attached to a column definition, then the 
default value of the column is NULL. ^(An explicit DEFAULT clause may specify
that the default value is NULL, a string constant, a blob constant, a
signed-number, or any constant expression enclosed in parentheses. A
default value may also be one of the special case-independent keywords
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
<tcl>hd_fragment primkeyconst {PRIMARY KEY} {PRIMARY KEY constraint}</tcl>
<p>^Each table in SQLite may have at most one <b>PRIMARY KEY</b>. ^If the
  keywords PRIMARY KEY are added to a column definition, then the primary key
  for the table consists of that single column. ^Or, if a PRIMARY KEY clause 
  is specified as a [table-constraint], then the primary key of the table
  consists of the list of columns specified as part of the PRIMARY KEY clause.
  ^The PRIMARY KEY clause must contain only column names &mdash; the use of 
  expressions in an &lt;indexed-column&gt; of a PRIMARY KEY is not supported.
  ^An error is raised if more than one PRIMARY KEY clause appears in a
  CREATE TABLE statement.  ^The PRIMARY KEY is optional for ordinary tables
  but is required for [WITHOUT ROWID] tables.

<p>If a table has a single column primary key and the declared type of that
  column is "INTEGER" and the table is not a [WITHOUT ROWID] table,
  then the column is known as an [INTEGER PRIMARY KEY].
  See below for a description of the special properties and behaviors
  associated with an [INTEGER PRIMARY KEY].

<p>^Each row in a table with a primary key must have a unique combination
  of values in its primary key columns. ^For the purposes of determining
  the uniqueness of primary key values, NULL values are considered distinct from
  all other values, including other NULLs. ^If an [INSERT] or [UPDATE]
  statement attempts to modify the table content so that two or more rows







|







|







969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
<tcl>hd_fragment primkeyconst {PRIMARY KEY} {PRIMARY KEY constraint}</tcl>
<p>^Each table in SQLite may have at most one <b>PRIMARY KEY</b>. ^If the
  keywords PRIMARY KEY are added to a column definition, then the primary key
  for the table consists of that single column. ^Or, if a PRIMARY KEY clause 
  is specified as a [table-constraint], then the primary key of the table
  consists of the list of columns specified as part of the PRIMARY KEY clause.
  ^The PRIMARY KEY clause must contain only column names &mdash; the use of 
  expressions in an [indexed-column] of a PRIMARY KEY is not supported.
  ^An error is raised if more than one PRIMARY KEY clause appears in a
  CREATE TABLE statement.  ^The PRIMARY KEY is optional for ordinary tables
  but is required for [WITHOUT ROWID] tables.

<p>If a table has a single column primary key and the declared type of that
  column is "INTEGER" and the table is not a [WITHOUT ROWID] table,
  then the column is known as an [INTEGER PRIMARY KEY].
  See [rowid|below] for a description of the special properties and behaviors
  associated with an [INTEGER PRIMARY KEY].

<p>^Each row in a table with a primary key must have a unique combination
  of values in its primary key columns. ^For the purposes of determining
  the uniqueness of primary key values, NULL values are considered distinct from
  all other values, including other NULLs. ^If an [INSERT] or [UPDATE]
  statement attempts to modify the table content so that two or more rows
1003
1004
1005
1006
1007
1008
1009
1010
1011
1012
1013
1014
1015
1016
1017
  that a single table may have any number of UNIQUE constraints. ^For each
  UNIQUE constraint on the table, each row must contain a unique combination
  of values in the columns identified by the UNIQUE constraint. 
  ^For the purposes of UNIQUE constraints, NULL values
  are considered distinct from all other values, including other NULLs.
  ^As with PRIMARY KEYs, a UNIQUE [table-constraint] clause must contain
  only column names &mdash; the use of 
  expressions in an &lt;indexed-column&gt; of a UNIQUE [table-constraint]
  is not supported.
  

<p>^In most cases, UNIQUE and PRIMARY KEY
  constraints are implemented by creating a unique index in the database.
  (The exceptions are [INTEGER PRIMARY KEY] and PRIMARY KEYs on 
  [WITHOUT ROWID] tables.)







|







1005
1006
1007
1008
1009
1010
1011
1012
1013
1014
1015
1016
1017
1018
1019
  that a single table may have any number of UNIQUE constraints. ^For each
  UNIQUE constraint on the table, each row must contain a unique combination
  of values in the columns identified by the UNIQUE constraint. 
  ^For the purposes of UNIQUE constraints, NULL values
  are considered distinct from all other values, including other NULLs.
  ^As with PRIMARY KEYs, a UNIQUE [table-constraint] clause must contain
  only column names &mdash; the use of 
  expressions in an [indexed-column] of a UNIQUE [table-constraint]
  is not supported.
  

<p>^In most cases, UNIQUE and PRIMARY KEY
  constraints are implemented by creating a unique index in the database.
  (The exceptions are [INTEGER PRIMARY KEY] and PRIMARY KEYs on 
  [WITHOUT ROWID] tables.)
1394
1395
1396
1397
1398
1399
1400
1401

1402
1403

1404
1405
1406
1407
1408
1409
1410
1411
1412
1413

1414
1415
1416
1417
1418
1419
1420
1421
1422
1423
1424
1425
1426
1427
1428
1429
1430
1431
1432
1433
1434
1435
1436
1437
1438
1439
1440
1441
1442
1443
1444
1445
1446
</p>

<p>^If the "TEMP" or "TEMPORARY" keyword occurs in between "CREATE"
and "VIEW" then the view that is created is only visible to the
[database connection] that created it and is automatically deleted when
the database connection is closed.</p>

<p> ^If a &lt;schema-name&gt; is specified, then the view is created in 

the specified database. ^It is an error to specify both a &lt;schema-name&gt;
and the TEMP keyword on a VIEW, unless the &lt;schema-name&gt; is "temp".

^If no schema name is specified, and the TEMP keyword is not present,
the VIEW is created in the main database.</p>

<p>^You cannot [DELETE], [INSERT], or [UPDATE] a view.  ^Views are read-only 
in SQLite.  ^However, in many cases you can use an
[INSTEAD OF trigger] on the view to accomplish 
the same thing.  ^Views are removed 
with the [DROP VIEW] command.</p>

<p>^If a list of column names follows the &lt;view-name&gt;, then those

becomes the names of the columns for the view.  ^If no list of column
names is provided, then the names of the columns in the view are derived
from the names of the result-set columns in the &lt;select-stmt&gt;.

<p>Note that the list of column names following the view-name is only
supported in SQLite versions 3.8.12 and later.  Views with specified column
names will not work in earlier versions of SQLite.

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

RecursiveBubbleDiagram create-virtual-table-stmt
</tcl>

<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 indices or 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
[sqlite3_create_module()] or [sqlite3_create_module_v2()]
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







|
>
|
|
>









|
>
|
|
|
|
<
|
<

















|
|







1396
1397
1398
1399
1400
1401
1402
1403
1404
1405
1406
1407
1408
1409
1410
1411
1412
1413
1414
1415
1416
1417
1418
1419
1420
1421
1422

1423

1424
1425
1426
1427
1428
1429
1430
1431
1432
1433
1434
1435
1436
1437
1438
1439
1440
1441
1442
1443
1444
1445
1446
1447
1448
1449
</p>

<p>^If the "TEMP" or "TEMPORARY" keyword occurs in between "CREATE"
and "VIEW" then the view that is created is only visible to the
[database connection] that created it and is automatically deleted when
the database connection is closed.</p>

<p> ^If a <yyterm>schema-name</yyterm> is specified, then the view 
is created in the specified database.
^It is an error to specify both a <yyterm>schema-name</yyterm>
and the TEMP keyword on a VIEW, unless the <yyterm>schema-name</yyterm> 
is "temp".
^If no schema name is specified, and the TEMP keyword is not present,
the VIEW is created in the main database.</p>

<p>^You cannot [DELETE], [INSERT], or [UPDATE] a view.  ^Views are read-only 
in SQLite.  ^However, in many cases you can use an
[INSTEAD OF trigger] on the view to accomplish 
the same thing.  ^Views are removed 
with the [DROP VIEW] command.</p>

<p>^If a <yyterm>column-name</yyterm> list follows 
the <yyterm>view-name</yyterm>, then that list determines
the names of the columns for the view.  ^If the <yyterm>column-name</yyterm>
list is omitted, then the names of the columns in the view are derived
from the names of the result-set columns in the [select-stmt].
Note that the <yyterm>column-name</yyterm> list syntax is only

supported in SQLite versions 3.8.12 and later.


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

RecursiveBubbleDiagram create-virtual-table-stmt
</tcl>

<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 indices or triggers on a
virtual table.  ^Some virtual table implementations might impose additional
restrictions.  For example, many virtual tables are read-only.</p>

<p>The <yyterm>module-name</yyterm> is the name of an object that implements
the virtual table.  ^The <yyterm>module-name</yyterm> must be registered with
the SQLite database connection using
[sqlite3_create_module()] or [sqlite3_create_module_v2()]
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
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
##############################################################################
Section DELETE delete {DELETE *DELETEs}

RecursiveBubbleDiagram delete-stmt
</tcl>

<p>The DELETE command removes records from the table identified by the
   <i>qualified-table-name</i>. 

<p>^If the WHERE clause is not present, all records in the table are deleted.
   ^If a WHERE clause is supplied, then only those rows for which the
   result of evaluating the WHERE clause as a [boolean expression|
   boolean expression is true] are deleted.

<h3>Restrictions on DELETE Statements Within CREATE TRIGGER</h3>

<p>The following restrictions apply to DELETE statements that occur within the
   body of a [CREATE TRIGGER] statement:

<ul>

  <li><p>^The <i>table-name</i> specified as part of a DELETE statement within
    a trigger body must be unqualified.  ^(In other words, the
    <i>schema-name</i><b>.</b> prefix on the table name is not allowed 
    within triggers.)^ ^If the table to which the trigger is attached is
    not in the temp database, then DELETE statements within the trigger
    body must operate on tables within the same database as it. ^If the table
    to which the trigger is attached is in the TEMP database, then the
    unqualified name of the table being deleted is resolved in the same way as







|



|
|







>
|







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
1489
##############################################################################
Section DELETE delete {DELETE *DELETEs}

RecursiveBubbleDiagram delete-stmt
</tcl>

<p>The DELETE command removes records from the table identified by the
   [qualified-table-name].

<p>^If the WHERE clause is not present, all records in the table are deleted.
   ^If a WHERE clause is supplied, then only those rows for which the
   WHERE clause [boolean expression] is true are deleted.
   ^Rows for which the expression is false or NULL are retained.

<h3>Restrictions on DELETE Statements Within CREATE TRIGGER</h3>

<p>The following restrictions apply to DELETE statements that occur within the
   body of a [CREATE TRIGGER] statement:

<ul>
  <li><p>^The <yyterm>table-name</yyterm> specified as part of a 
    DELETE statement within
    a trigger body must be unqualified.  ^(In other words, the
    <i>schema-name</i><b>.</b> prefix on the table name is not allowed 
    within triggers.)^ ^If the table to which the trigger is attached is
    not in the temp database, then DELETE statements within the trigger
    body must operate on tables within the same database as it. ^If the table
    to which the trigger is attached is in the TEMP database, then the
    unqualified name of the table being deleted is resolved in the same way as
1600
1601
1602
1603
1604
1605
1606
1607
1608
1609
1610
1611
1612
1613
1614
1615
1616
1617
1618
1619
1620
1621
1622
1623
^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; 
is executed, so this cannot cause any triggers to fire. By contrast, ^an
implicit DELETE FROM &lt;tbl&gt; does cause any configured
[foreign key actions] to take place. 
^If the implicit DELETE FROM &lt;tbl&gt; executed
as part of a DROP TABLE command violates any immediate foreign key constraints,
an error is returned and the table is not dropped. ^If 
the implicit DELETE FROM &lt;tbl&gt; causes any 
deferred foreign key constraints to be violated, and the violations still
exist when the transaction is committed, an error is returned at the time
of commit.

<tcl>
##############################################################################
Section {DROP TRIGGER} droptrigger {{DROP TRIGGER}}







|

|

|

|


|







1604
1605
1606
1607
1608
1609
1610
1611
1612
1613
1614
1615
1616
1617
1618
1619
1620
1621
1622
1623
1624
1625
1626
1627
^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] 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
is executed, so this cannot cause any triggers to fire. By contrast, ^an
implicit DELETE FROM does cause any configured
[foreign key actions] to take place. 
^If the implicit DELETE FROM executed
as part of a DROP TABLE command violates any immediate foreign key constraints,
an error is returned and the table is not dropped. ^If 
the implicit DELETE FROM causes any 
deferred foreign key constraints to be violated, and the violations still
exist when the transaction is committed, an error is returned at the time
of commit.

<tcl>
##############################################################################
Section {DROP TRIGGER} droptrigger {{DROP TRIGGER}}
2175
2176
2177
2178
2179
2180
2181
2182
2183
2184
2185
2186
2187
2188
2189
2190
2191
2192
2193
2194
2195
2196
2197
2198
2199
2200
2201

2202
2203
2204
2205
2206
2207
2208
2209
a correlated subquery. ^A correlated subquery is reevaluated each time
its result is required. ^An uncorrelated subquery is evaluated only once
and the result reused as necessary.

<tcl>hd_fragment castexpr {CAST expression} {CAST} {cast} {CAST operator}</tcl>
<h3>CAST expressions</h3>

<p>A CAST expression of the form "CAST(&lt;expr&gt; AS &lt;type-name&gt;)"
is used to convert the value of &lt;expr&gt; to 
a different [storage class] specified by &lt;type-name&gt;.
^A CAST conversion is similar to the conversion that takes
place when a [column affinity] is applied to a value except that with
the CAST operator the conversion always takes place even if the conversion
lossy and irreversible, whereas column affinity only changes the data type
of a value if the change is lossless and reversible.

<p>^If the value of &lt;expr&gt; is NULL, then the result of the CAST
expression is also NULL. ^Otherwise, the storage class of the result
is determined by applying the [rules for determining column affinity] to
the &lt;type-name&gt;.

<table border=1>
<tr>
  <th> Affinity of &lt;type-name&gt;
  <th> Conversion Processing
<tr>
  <td> NONE 

  <td> ^Casting a value to a &lt;type-name&gt; with no affinity causes the value to
  be converted into a BLOB.  ^Casting to a BLOB consists of first casting
  the value to TEXT in the [encoding] of the database connection, then
  interpreting the resulting byte sequence as a BLOB instead of as TEXT.

<tr>
  <td> TEXT
  <td> ^To cast a BLOB value to TEXT, the sequence of bytes that make up the







|
|
|






|


|



|



>
|







2179
2180
2181
2182
2183
2184
2185
2186
2187
2188
2189
2190
2191
2192
2193
2194
2195
2196
2197
2198
2199
2200
2201
2202
2203
2204
2205
2206
2207
2208
2209
2210
2211
2212
2213
2214
a correlated subquery. ^A correlated subquery is reevaluated each time
its result is required. ^An uncorrelated subquery is evaluated only once
and the result reused as necessary.

<tcl>hd_fragment castexpr {CAST expression} {CAST} {cast} {CAST operator}</tcl>
<h3>CAST expressions</h3>

<p>A CAST expression of the form "CAST(<i>expr</i> AS <i>type-name</i>)"
is used to convert the value of <i>expr</i> to 
a different [storage class] specified by <yyterm>type-name</yyterm>.
^A CAST conversion is similar to the conversion that takes
place when a [column affinity] is applied to a value except that with
the CAST operator the conversion always takes place even if the conversion
lossy and irreversible, whereas column affinity only changes the data type
of a value if the change is lossless and reversible.

<p>^If the value of <i>expr</i> is NULL, then the result of the CAST
expression is also NULL. ^Otherwise, the storage class of the result
is determined by applying the [rules for determining column affinity] to
the <yyterm>type-name</yyterm>.

<table border=1>
<tr>
  <th> Affinity of <yyterm>type-name</yyterm>
  <th> Conversion Processing
<tr>
  <td> NONE 
  <td> ^Casting a value to a <yyterm>type-name</yyterm> with no affinity 
  causes the value to
  be converted into a BLOB.  ^Casting to a BLOB consists of first casting
  the value to TEXT in the [encoding] of the database connection, then
  interpreting the resulting byte sequence as a BLOB instead of as TEXT.

<tr>
  <td> TEXT
  <td> ^To cast a BLOB value to TEXT, the sequence of bytes that make up the
2229
2230
2231
2232
2233
2234
2235
2236
2237
2238
2239
2240
2241
2242
2243
2244
2245
2246
  <td> ^When casting a BLOB value to INTEGER, the value is first converted to
        TEXT.
       <p>^When casting a TEXT value to INTEGER, the longest possible prefix of
        the value that can be interpreted as an integer number is extracted from
        the TEXT value and the remainder ignored. ^Any leading spaces in the
        TEXT value when converting from TEXT to INTEGER are ignored. ^If there
        is no prefix that can be interpreted as an integer number, the result
        of the conversion is 0.  ^(The CAST operator understands decimal integers
        only &mdash; conversion of [hexadecimal integers] stops at the "x" in the
        "0x" prefix of the hexadecimal integer string and thus result of the 
        CAST is always zero.)^

      <p>^A cast of a REAL value into an INTEGER results in the integer
      between the REAL value and zero that is closest to the REAL value.
      ^If a REAL is greater than the greatest possible signed
      integer (+9223372036854775807) then the result is the greatest possible
      signed integer and if the REAL is less than the least possible signed
      integer (-9223372036854775808) then the result is the least possible







|
|
|
|







2234
2235
2236
2237
2238
2239
2240
2241
2242
2243
2244
2245
2246
2247
2248
2249
2250
2251
  <td> ^When casting a BLOB value to INTEGER, the value is first converted to
        TEXT.
       <p>^When casting a TEXT value to INTEGER, the longest possible prefix of
        the value that can be interpreted as an integer number is extracted from
        the TEXT value and the remainder ignored. ^Any leading spaces in the
        TEXT value when converting from TEXT to INTEGER are ignored. ^If there
        is no prefix that can be interpreted as an integer number, the result
        of the conversion is 0.  ^(The CAST operator understands decimal
        integers only &mdash; conversion of [hexadecimal integers] stops 
        at the "x" in the "0x" prefix of the hexadecimal integer string 
        and thus result of the CAST is always zero.)^

      <p>^A cast of a REAL value into an INTEGER results in the integer
      between the REAL value and zero that is closest to the REAL value.
      ^If a REAL is greater than the greatest possible signed
      integer (+9223372036854775807) then the result is the greatest possible
      signed integer and if the REAL is less than the least possible signed
      integer (-9223372036854775808) then the result is the least possible
3115
3116
3117
3118
3119
3120
3121
3122

3123
3124
3125
3126
3127
3128
3129
3130
3131
3132
3133
3134
3135
3136
3137
3138
3139
3140
3141
3142
3143
3144
3145
3146
3147
3148
3149
3150
3151
3152
3153
3154
3155
3156
3157
3158
3159
3160
3161

3162
3163
3164
3165
3166
3167
3168
RecursiveBubbleDiagram insert-stmt
</tcl>

<p>The INSERT statement comes in three basic forms.  
<ul>
<li><p>^The first form (with the "VALUES" keyword) creates one or more
new rows in
an existing table. ^If no column-list is specified then the number

of values inserted into each row
must be the same as the number of columns in the table. ^In this case
the result of evaluating the left-most expression from each term of
the VALUES list is inserted into the left-most column of each new row,
and so forth for each subsequent expression. ^If a
column-list is specified, then the number of values in each term of the
VALUE list must match the number of
specified columns. ^Each of the named columns of the new row is populated
with the results of evaluating the corresponding VALUES expression. ^Table
columns that do not appear in the column list are populated with the default
column value (specified as part of the CREATE TABLE statement), or with NULL if
no default value is specified.

<li><p>The second form of the INSERT statement contains a SELECT statement
instead of a VALUES clause. ^A new entry is inserted into the table for each
row of data returned by executing the SELECT statement. ^If a column-list is
specified, the number of columns in the result of the SELECT must be the same
as the number of items in the column-list. ^Otherwise, if no column-list is
specified, the number of columns in the result of the SELECT must be the same
as the number of columns in the table. ^Any SELECT statement, including
[compound SELECTs] and SELECT statements with [ORDER BY] and/or [LIMIT] clauses, 
may be used in an INSERT statement of this form.

<li><p>The third form of an INSERT statement is with DEFAULT VALUES.
^(The INSERT ... DEFAULT VALUES statement inserts a single new row into the
named table.)^ ^Each column of the new row is populated with its default value,
or with a NULL if no default value is specified as part of the column
definition in the CREATE TABLE statement.

</ul>

<p>^The "REPLACE" and "INSERT OR <i>action</i>" forms specify an alternative
constraint conflict resolution algorithm to use during this one INSERT command.
See the section titled [ON CONFLICT] for additional information.
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>^(The optional "<i>schema-name</i><b>.</b>" prefix on the <i>table-name</i>

is support for top-level INSERT statements only.)^  ^The table name must be
unqualified for INSERT statements that occur within [CREATE TRIGGER] statements.
^Similarly, the "DEFAULT VALUES" form of the INSERT statement is supported for
top-level INSERT statements only and not for INSERT statements within
triggers.
<p>








|
>




|
|



|
|
|

|











|
|
|










|
>







3120
3121
3122
3123
3124
3125
3126
3127
3128
3129
3130
3131
3132
3133
3134
3135
3136
3137
3138
3139
3140
3141
3142
3143
3144
3145
3146
3147
3148
3149
3150
3151
3152
3153
3154
3155
3156
3157
3158
3159
3160
3161
3162
3163
3164
3165
3166
3167
3168
3169
3170
3171
3172
3173
3174
3175
RecursiveBubbleDiagram insert-stmt
</tcl>

<p>The INSERT statement comes in three basic forms.  
<ul>
<li><p>^The first form (with the "VALUES" keyword) creates one or more
new rows in
an existing table. ^If the <yyterm>column-name</yyterm> list after
<yyterm>table-name</yyterm> is omitted then the number
of values inserted into each row
must be the same as the number of columns in the table. ^In this case
the result of evaluating the left-most expression from each term of
the VALUES list is inserted into the left-most column of each new row,
and so forth for each subsequent expression. ^If a <yyterm>column-name</yyterm>
list is specified, then the number of values in each term of the
VALUE list must match the number of
specified columns. ^Each of the named columns of the new row is populated
with the results of evaluating the corresponding VALUES expression. ^Table
columns that do not appear in the column list are populated with the 
[default column value] (specified as part of the [CREATE TABLE] statement), or
with NULL if no [default value] is specified.

<li><p>The second form of the INSERT statement contains a [SELECT] statement
instead of a VALUES clause. ^A new entry is inserted into the table for each
row of data returned by executing the SELECT statement. ^If a column-list is
specified, the number of columns in the result of the SELECT must be the same
as the number of items in the column-list. ^Otherwise, if no column-list is
specified, the number of columns in the result of the SELECT must be the same
as the number of columns in the table. ^Any SELECT statement, including
[compound SELECTs] and SELECT statements with [ORDER BY] and/or [LIMIT] clauses, 
may be used in an INSERT statement of this form.

<li><p>The third form of an INSERT statement is with DEFAULT VALUES.
^(The INSERT ... DEFAULT VALUES statement inserts a single new row into the
named table.)^ ^Each column of the new row is populated with its 
[default value], or with a NULL if no default value is specified 
as part of the column definition in the [CREATE TABLE] statement.

</ul>

<p>^The "REPLACE" and "INSERT OR <i>action</i>" forms specify an alternative
constraint conflict resolution algorithm to use during this one INSERT command.
See the section titled [ON CONFLICT] for additional information.
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>^(The optional "<i>schema-name</i><b>.</b>" prefix on the 
<yyterm>table-name</yyterm>
is support for top-level INSERT statements only.)^  ^The table name must be
unqualified for INSERT statements that occur within [CREATE TRIGGER] statements.
^Similarly, the "DEFAULT VALUES" form of the INSERT statement is supported for
top-level INSERT statements only and not for INSERT statements within
triggers.
<p>

3282
3283
3284
3285
3286
3287
3288

3289
3290
3291
3292
3293
3294
3295
3296
3297
3298
3299
3300
all indices in all attached databases that use the named collation sequences
are recreated. 

<p>^Or, if the argument attached to the REINDEX identifies a specific 
database table, then all indices attached to the database table are rebuilt. 
^If it identifies a specific database index, then just that index is recreated.


<p>^If no <i>schema-name</i> is specified and there exists both a table or
index and a collation sequence of the specified name, SQLite interprets
this as a request to rebuild the indices that use the named collation sequence.
This ambiguity in the syntax may be avoided by always specifying a
<i>schema-name</i> when reindexing a specific table or index.

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

</tcl>








>
|



|







3289
3290
3291
3292
3293
3294
3295
3296
3297
3298
3299
3300
3301
3302
3303
3304
3305
3306
3307
3308
all indices in all attached databases that use the named collation sequences
are recreated. 

<p>^Or, if the argument attached to the REINDEX identifies a specific 
database table, then all indices attached to the database table are rebuilt. 
^If it identifies a specific database index, then just that index is recreated.

<p>^If no <yyterm>schema-name</yyterm> is specified and 
there exists both a table or
index and a collation sequence of the specified name, SQLite interprets
this as a request to rebuild the indices that use the named collation sequence.
This ambiguity in the syntax may be avoided by always specifying a
<yyterm>schema-name</yyterm> when reindexing a specific table or index.

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

</tcl>

3360
3361
3362
3363
3364
3365
3366
3367
3368
3369
3370

3371

3372

3373
3374
3375
3376
3377
3378
3379
3380
3381
3382
3383
3384
3385
3386
3387
3388
3389
3390
3391
3392
3393
3394
3395
3396
3397

3398
3399
3400
3401
3402

3403
3404
3405
3406
3407
3408
3409
3410
3411
3412
3413
3414
3415

3416
3417
3418
3419
3420
3421
3422
</ol>

<p>To put it another way, a recursive common table expression must
look like the following:

<tcl>RecursiveBubbleDiagram recursive-cte</tcl>

<p>We refer to the table named by the cte-table-name in a recursive
common table expression as the "recursive table".
In the recursive-cte bubble diagram above, the recursive
table must appear exactly once in the FROM clause of the recursive-select

and must not appear anywhere else in either the initial-select or the

recursive-select, including subqueries.  The initial-select may be

a [compound select], but it may not include an ORDER BY, LIMIT, or OFFSET.
The recursive-select must be a simple select, not a compound.  The
recursive-select is allowed to include an ORDER BY, LIMIT, and/or OFFSET.

<p>The basic algorithm for computing the content of the recursive table
is as follows:

<ol>
<li> Run the initial-select and add the results to a queue.
<li> While the queue is not empty:
<ol type="a">
<li> Extract a single row from the queue.
<li> Insert that single row into the recursive table
<li> Pretend that the single row just extracted is the only
     row in the recursive table and run the recursive-select,
     adding all results to the queue.
</ol>
</ol>

<p>The basic procedure above may modified by the following additional rules:

<ul>
<li><p>
  If a UNION operator connects the initial-select with the
  recursive-select, then only add rows to the queue if no identical row has

  been previously added to the queue.  Repeated rows are discarded before being
  added to the queue even if the repeated rows have already been extracted
  from the queue by the recursion step.  If the operator is UNION ALL,
  then all rows generated by both the initial-select and the
  recursive-select are always added to the queue even if they are repeats.

  When determining if a row is repeated, NULL values compare
  equal to one another and not equal to any other value.
<li><p>
  The LIMIT clause, if present, determines the maximum number of rows that
  will ever be added to the recursive table in step 2b.
  Once the limit is reached, the recursion stops.
  A limit of zero means that no rows are ever added to the
  recursive table, and a negative limit means an unlimited number of rows
  may be added to the recursive table.
<li><p>
  The OFFSET clause, if it is present and has a positive value N, prevents the
  first N rows from being added to the recursive table.
  The first N rows are still processed by the recursive-select; they

  just are not added to the recursive table.  Rows are not counted toward
  fulfilling the LIMIT until all OFFSET rows have been skipped.
<li><p>
  If an ORDER BY clause is present, it determines the order in which rows
  are extracted from the queue in step 2a.  If there is no ORDER BY clause,
  then the order in which rows are extracted is undefined.  (In the current
  implementation, the queue becomes a FIFO if the ORDER BY clause is omitted,







|
|
|
|
>
|
>
|
>








|














|
|
>



|
|
>












|
>







3368
3369
3370
3371
3372
3373
3374
3375
3376
3377
3378
3379
3380
3381
3382
3383
3384
3385
3386
3387
3388
3389
3390
3391
3392
3393
3394
3395
3396
3397
3398
3399
3400
3401
3402
3403
3404
3405
3406
3407
3408
3409
3410
3411
3412
3413
3414
3415
3416
3417
3418
3419
3420
3421
3422
3423
3424
3425
3426
3427
3428
3429
3430
3431
3432
3433
3434
3435
3436
</ol>

<p>To put it another way, a recursive common table expression must
look like the following:

<tcl>RecursiveBubbleDiagram recursive-cte</tcl>

<p>Call the table named by the [cte-table-name] in a recursive
common table expression the "recursive table".
In the [recursive-cte] bubble diagram above, the recursive
table must appear exactly once in the FROM clause of the 
<yyterm>recursive-select</yyterm>
and must not appear anywhere else in either the
<yyterm>initial-select</yyterm> or the
<yyterm>recursive-select</yyterm>, including subqueries.
The <yyterm>initial-select</yyterm> may be
a [compound select], but it may not include an ORDER BY, LIMIT, or OFFSET.
The recursive-select must be a simple select, not a compound.  The
recursive-select is allowed to include an ORDER BY, LIMIT, and/or OFFSET.

<p>The basic algorithm for computing the content of the recursive table
is as follows:

<ol>
<li> Run the <yyterm>initial-select</yyterm> and add the results to a queue.
<li> While the queue is not empty:
<ol type="a">
<li> Extract a single row from the queue.
<li> Insert that single row into the recursive table
<li> Pretend that the single row just extracted is the only
     row in the recursive table and run the recursive-select,
     adding all results to the queue.
</ol>
</ol>

<p>The basic procedure above may modified by the following additional rules:

<ul>
<li><p>
  If a UNION operator connects the <yyterm>initial-select</yyterm> with the
  <yyterm>recursive-select</yyterm>, then only add rows to the queue if 
  no identical row has
  been previously added to the queue.  Repeated rows are discarded before being
  added to the queue even if the repeated rows have already been extracted
  from the queue by the recursion step.  If the operator is UNION ALL,
  then all rows generated by both the <yyterm>initial-select</yyterm> and the
  <yyterm>recursive-select</yyterm> are always added to the queue even if
  they are repeats.
  When determining if a row is repeated, NULL values compare
  equal to one another and not equal to any other value.
<li><p>
  The LIMIT clause, if present, determines the maximum number of rows that
  will ever be added to the recursive table in step 2b.
  Once the limit is reached, the recursion stops.
  A limit of zero means that no rows are ever added to the
  recursive table, and a negative limit means an unlimited number of rows
  may be added to the recursive table.
<li><p>
  The OFFSET clause, if it is present and has a positive value N, prevents the
  first N rows from being added to the recursive table.
  The first N rows are still processed
  by the <yyterm>recursive-select</yyterm> &mdash; they
  just are not added to the recursive table.  Rows are not counted toward
  fulfilling the LIMIT until all OFFSET rows have been skipped.
<li><p>
  If an ORDER BY clause is present, it determines the order in which rows
  are extracted from the queue in step 2a.  If there is no ORDER BY clause,
  then the order in which rows are extracted is undefined.  (In the current
  implementation, the queue becomes a FIFO if the ORDER BY clause is omitted,
4399
4400
4401
4402
4403
4404
4405
4406
4407
4408
4409
4410
4411
4412
4413
4414
4415
4416

4417
4418
4419
4420
4421
4422
4423
4424
4425

4426
4427
4428
4429
4430
4431
4432
4433
4434
4435
4436

4437
4438
4439
4440
4441
4442
4443
Section UPDATE update {UPDATE *UPDATEs}

RecursiveBubbleDiagram update-stmt
</tcl>

<p>^An UPDATE statement is used to modify a subset of the values stored in 
zero or more rows of the database table identified by the 
<i>qualified-table-name</i> specified as part of the UPDATE statement.

<p>^If the UPDATE statement does not have a WHERE clause, all rows in the
table are modified by the UPDATE. ^Otherwise, the UPDATE affects only those
rows for which the result of evaluating the WHERE clause expression as a 
[boolean expression|boolean expression is true]. ^It is not an error if the
WHERE clause does not evaluate to true for any row in the table - this just
means that the UPDATE statement affects zero rows.

<p>The modifications made to each row affected by an UPDATE statement are
determined by the list of assignments following the SET keyword. Each

assignment specifies a column name to the left of the equals sign and a
scalar expression to the right. ^For each affected row, the named columns
are set to the values found by evaluating the corresponding scalar 
expressions. ^If a single column-name appears more than once in the list of
assignment expressions, all but the rightmost occurrence is ignored. ^Columns
that do not appear in the list of assignments are left unmodified. ^The scalar
expressions may refer to columns of the row being updated. ^In this case all
scalar expressions are evaluated before any assignments are made.


<p>^The optional conflict-clause allows the user to nominate a specific
constraint conflict resolution algorithm to use during this one UPDATE command.
Refer to the section entitled [ON CONFLICT] for additional information.

<h3>Restrictions on UPDATE Statements Within CREATE TRIGGER</h3>

<p>The following additional syntax restrictions apply to UPDATE statements that
occur within the body of a [CREATE TRIGGER] statement.  

<ul>
  <li><p>^The <i>table-name</i> specified as part of an UPDATE statement within

      a trigger body must be unqualified. ^(In other words, the
      <i>schema-name</i><b>.</b> prefix on the table name of the UPDATE is
      not allowed within triggers.)^ ^Unless the table to which the trigger
      is attached is in the TEMP database, the table being updated by the
      trigger program must reside in the same database as it. ^If the table
      to which the trigger is attached is in the TEMP database, then the
      unqualified name of the table being updated is resolved in the same way







|



|






>
|








>
|









|
>







4413
4414
4415
4416
4417
4418
4419
4420
4421
4422
4423
4424
4425
4426
4427
4428
4429
4430
4431
4432
4433
4434
4435
4436
4437
4438
4439
4440
4441
4442
4443
4444
4445
4446
4447
4448
4449
4450
4451
4452
4453
4454
4455
4456
4457
4458
4459
4460
Section UPDATE update {UPDATE *UPDATEs}

RecursiveBubbleDiagram update-stmt
</tcl>

<p>^An UPDATE statement is used to modify a subset of the values stored in 
zero or more rows of the database table identified by the 
[qualified-table-name] specified as part of the UPDATE statement.

<p>^If the UPDATE statement does not have a WHERE clause, all rows in the
table are modified by the UPDATE. ^Otherwise, the UPDATE affects only those
rows for which the WHERE clause
[boolean expression|boolean expression is true]. ^It is not an error if the
WHERE clause does not evaluate to true for any row in the table - this just
means that the UPDATE statement affects zero rows.

<p>The modifications made to each row affected by an UPDATE statement are
determined by the list of assignments following the SET keyword. Each
assignment specifies a <yyterm>column-name</yyterm> to the left of the 
equals sign and a
scalar expression to the right. ^For each affected row, the named columns
are set to the values found by evaluating the corresponding scalar 
expressions. ^If a single column-name appears more than once in the list of
assignment expressions, all but the rightmost occurrence is ignored. ^Columns
that do not appear in the list of assignments are left unmodified. ^The scalar
expressions may refer to columns of the row being updated. ^In this case all
scalar expressions are evaluated before any assignments are made.

<p>^The optional "OR <i>action</i>" conflict clause that follows the
UPDATE keyword allows the user to nominate a specific
constraint conflict resolution algorithm to use during this one UPDATE command.
Refer to the section entitled [ON CONFLICT] for additional information.

<h3>Restrictions on UPDATE Statements Within CREATE TRIGGER</h3>

<p>The following additional syntax restrictions apply to UPDATE statements that
occur within the body of a [CREATE TRIGGER] statement.  

<ul>
  <li><p>^The <yyterm>table-name</yyterm> specified as part of an UPDATE 
      statement within
      a trigger body must be unqualified. ^(In other words, the
      <i>schema-name</i><b>.</b> prefix on the table name of the UPDATE is
      not allowed within triggers.)^ ^Unless the table to which the trigger
      is attached is in the TEMP database, the table being updated by the
      trigger program must reside in the same database as it. ^If the table
      to which the trigger is attached is in the TEMP database, then the
      unqualified name of the table being updated is resolved in the same way
4555
4556
4557
4558
4559
4560
4561

4562
4563
4564
4565
4566
4567
4568
4569
4570
4571
4572
4573
4574
4575
4576
4577
4578
4579
4580
4581
4582
4583
4584
4585

4586
4587
4588
4589
4590
4591
4592
4593
The INDEXED BY phrase is an SQLite extension and
is not portable to other SQL database engines.</p>

<tcl>
RecursiveBubbleDiagram qualified-table-name
</tcl>


<p>^The "INDEXED BY index-name" phrase specifies that the named index
must be used in order to look up values on the preceding table.
^If index-name does not exist or cannot be used for the query, then
the preparation of the SQL statement fails.
^(The "NOT INDEXED" clause specifies that no index shall be used when
accessing the preceding table, including implied indices create by
UNIQUE and PRIMARY KEY constraints.  However, the [rowid]
can still be used to look up entries even when "NOT INDEXED" is specified.)^</p>

<p>Some SQL database engines provide non-standard "hint" mechanisms which
can be used to give the query optimizer clues about what indices it should
use for a particular statement.  The INDEX BY clause of SQLite is 
<em>not</em> a hinting mechanism and it should not be used as such.
^The INDEXED BY clause does not give the optimizer hints about which index
to use; it gives the optimizer a requirement of which index to use.
^If the query optimizer is unable to use the index specified by the
INDEX BY clause, then the query will fail with an error.</p>

<p>The INDEXED BY clause is <em>not</em> intended for use in tuning
the performance of a query.  The intent of the INDEXED BY clause is
to raise a run-time error if a schema change, such as dropping or
creating an index, causes the query plan for a time-sensitive query
to change.  The INDEXED BY clause is designed to help detect
undesirable query plan changes during regression testing.

Developers are admonished to omit all use of INDEXED BY during
application design, implementation, testing, and tuning.  If
INDEXED BY is to be used at all, it should be inserted at the very
end of the development process when "locking down" a design.</p>

<h3>See Also:</h3>

<ol>







>
|

|
|




















>
|







4572
4573
4574
4575
4576
4577
4578
4579
4580
4581
4582
4583
4584
4585
4586
4587
4588
4589
4590
4591
4592
4593
4594
4595
4596
4597
4598
4599
4600
4601
4602
4603
4604
4605
4606
4607
4608
4609
4610
4611
4612
The INDEXED BY phrase is an SQLite extension and
is not portable to other SQL database engines.</p>

<tcl>
RecursiveBubbleDiagram qualified-table-name
</tcl>

<p>^The "INDEXED BY <yyterm>index-name</yyterm>" phrase specifies 
that the named index
must be used in order to look up values on the preceding table.
^If <yyterm>index-name</yyterm> does not exist or cannot be used 
for the query, then the preparation of the SQL statement fails.
^(The "NOT INDEXED" clause specifies that no index shall be used when
accessing the preceding table, including implied indices create by
UNIQUE and PRIMARY KEY constraints.  However, the [rowid]
can still be used to look up entries even when "NOT INDEXED" is specified.)^</p>

<p>Some SQL database engines provide non-standard "hint" mechanisms which
can be used to give the query optimizer clues about what indices it should
use for a particular statement.  The INDEX BY clause of SQLite is 
<em>not</em> a hinting mechanism and it should not be used as such.
^The INDEXED BY clause does not give the optimizer hints about which index
to use; it gives the optimizer a requirement of which index to use.
^If the query optimizer is unable to use the index specified by the
INDEX BY clause, then the query will fail with an error.</p>

<p>The INDEXED BY clause is <em>not</em> intended for use in tuning
the performance of a query.  The intent of the INDEXED BY clause is
to raise a run-time error if a schema change, such as dropping or
creating an index, causes the query plan for a time-sensitive query
to change.  The INDEXED BY clause is designed to help detect
undesirable query plan changes during regression testing.
Application 
developers are admonished to omit all use of INDEXED BY during
application design, implementation, testing, and tuning.  If
INDEXED BY is to be used at all, it should be inserted at the very
end of the development process when "locking down" a design.</p>

<h3>See Also:</h3>

<ol>
Changes to pages/pragma.in.
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
}
unset -nocomplain PragmaBody PragmaRef PragmaDud PragmaKeys

# Each pragma is recorded by invoking this procedure.
proc Pragma {namelist content} {
  global PragmaBody PragmaRef PragmaKeys
  set main_name [lindex $namelist 0]
  regsub -all {PRAGMA DB\.} $content {PRAGMA </b><i>database.</i><b>} content
  set PragmaBody($main_name) $content
  set PragmaKeys($main_name) $namelist
  foreach x $namelist {
    set PragmaRef($x) $main_name
  }
}
proc LegacyDisclaimer {} {







|







14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
}
unset -nocomplain PragmaBody PragmaRef PragmaDud PragmaKeys

# Each pragma is recorded by invoking this procedure.
proc Pragma {namelist content} {
  global PragmaBody PragmaRef PragmaKeys
  set main_name [lindex $namelist 0]
  regsub -all {PRAGMA DB\.} $content {PRAGMA </b><i>schema.</i><b>} content
  set PragmaBody($main_name) $content
  set PragmaKeys($main_name) $namelist
  foreach x $namelist {
    set PragmaRef($x) $main_name
  }
}
proc LegacyDisclaimer {} {
124
125
126
127
128
129
130
131

132
133
134
135
136
137
138
139
140
141
142
143
144
(Example:  <tt>'yes' &#91;FALSE&#93;</tt>.) Some pragmas
takes a string literal as their argument.  When pragma takes a keyword
argument, it will usually also take a numeric equivalent as well.
For example, "0" and "no" mean the same thing, as does "1" and "yes".
When querying the value of a setting, many pragmas return the number
rather than the keyword.</p>

<p>^A pragma may have an optional database name before the pragma name.

^The database name is the name of an [ATTACH]-ed database or it can be
"main" or "temp" for the main and the TEMP databases.  ^If the optional
database name is omitted, "main" is assumed.  ^In some pragmas, the database
name is meaningless and is simply ignored.  In the documentation below,
pragmas for which the database name is meaningful are shown with a
"<i>database.</i>" prefix.</p>



<tcl>Pragma {application_id} {
    <p><b>PRAGMA DB.application_id;
     <br>PRAGMA DB.application_id = </b><i>integer </i><b>;</b>
  







|
>
|
|
|

|
|







124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
(Example:  <tt>'yes' &#91;FALSE&#93;</tt>.) Some pragmas
takes a string literal as their argument.  When pragma takes a keyword
argument, it will usually also take a numeric equivalent as well.
For example, "0" and "no" mean the same thing, as does "1" and "yes".
When querying the value of a setting, many pragmas return the number
rather than the keyword.</p>

<p>^A pragma may have an optional <yyterm>schema-name</yyterm>
before the pragma name.
^The <yyterm>schema-name</yyterm> is the name of an [ATTACH]-ed database 
or "main" or "temp" for the main and the TEMP databases.  ^If the optional
schema name is omitted, "main" is assumed.  ^In some pragmas, the schema
name is meaningless and is simply ignored.  In the documentation below,
pragmas for which the schema name is meaningful are shown with a
"<i>schema.</i>" prefix.</p>



<tcl>Pragma {application_id} {
    <p><b>PRAGMA DB.application_id;
     <br>PRAGMA DB.application_id = </b><i>integer </i><b>;</b>
  
Changes to wrap.tcl.
71
72
73
74
75
76
77


78
79
80
81
82
83
84
# appropriate <a href=""> markup.
#
# Links to keywords within the same main file are resolved using
# $::llink() if possible.  All other links and links that could
# not be resolved using $::llink() are resolved using $::glink().
# 
proc hd_resolve_2ndpass {text} {


  regsub -all {\[(.*?)\]} $text \
      "\175; hd_resolve_one \173\\1\175; hd_puts \173" text
  eval "hd_puts \173$text\175"
}
proc hd_resolve_one {x} {
  if {[string is integer $x]} {
    hd_puts \[$x\]







>
>







71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
# appropriate <a href=""> markup.
#
# Links to keywords within the same main file are resolved using
# $::llink() if possible.  All other links and links that could
# not be resolved using $::llink() are resolved using $::glink().
# 
proc hd_resolve_2ndpass {text} {
  regsub -all {<yyterm>} $text {<span class='yyterm'>} text
  regsub -all {</yyterm>} $text {</span>} text
  regsub -all {\[(.*?)\]} $text \
      "\175; hd_resolve_one \173\\1\175; hd_puts \173" text
  eval "hd_puts \173$text\175"
}
proc hd_resolve_one {x} {
  if {[string is integer $x]} {
    hd_puts \[$x\]
460
461
462
463
464
465
466







467
468
469
470
471
472
473
    .fancy p.todo        { border: solid #AA3333 1px; padding: 1ex }
    .fancy img { display:block; }
    .fancy :link:hover, .fancy :visited:hover { background: wheat }
    .fancy p,.fancy ul,.fancy ol,.fancy dl { margin: 1em 5ex }
    .fancy li p { margin: 1em 0 }
    /* End of "fancyformat" specific rules. */








    </style>
  }
  puts $fd {</head>}
  if {[file exists DRAFT]} {
    set tagline {<font size="6" color="red">*** DRAFT ***</font>}
  } else {
    set tagline {Small. Fast. Reliable.<br>Choose any three.}







>
>
>
>
>
>
>







462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
    .fancy p.todo        { border: solid #AA3333 1px; padding: 1ex }
    .fancy img { display:block; }
    .fancy :link:hover, .fancy :visited:hover { background: wheat }
    .fancy p,.fancy ul,.fancy ol,.fancy dl { margin: 1em 5ex }
    .fancy li p { margin: 1em 0 }
    /* End of "fancyformat" specific rules. */

    .yyterm {
      background: #fff;
      border: 1px solid #000;
      border-radius: 11px;
      padding-left: 4px;
      padding-right: 4px;
    }
    </style>
  }
  puts $fd {</head>}
  if {[file exists DRAFT]} {
    set tagline {<font size="6" color="red">*** DRAFT ***</font>}
  } else {
    set tagline {Small. Fast. Reliable.<br>Choose any three.}