Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Use <codeblock> in more places, instead of <blockquote><pre>. Improved CSS for codeblock and for syntax diagrams. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
14e4769852193d9ac5ab5319c4452add |
User & Date: | drh 2016-09-13 01:12:14.784 |
Context
2016-09-13
| ||
20:28 | Add a section to the WAL documentation that describes some of the scenarios in which a query can get SQLITE_BUSY. (check-in: 9466a94ec8 user: drh tags: trunk) | |
01:12 | Use <codeblock> in more places, instead of <blockquote><pre>. Improved CSS for codeblock and for syntax diagrams. (check-in: 14e4769852 user: drh tags: trunk) | |
2016-09-12
| ||
21:06 | Fix formatting problems in vtab.html associated with <yyterm> marks. (check-in: 2262f22f31 user: drh tags: trunk) | |
Changes
Changes to pages/amalgamation.in.
︙ | ︙ | |||
74 75 76 77 78 79 80 | <p>To circumvent this limitation, the amalgamation is also available in a split form, consisting of files "sqlite3-1.c", "sqlite3-2.c", and so forth, where each file is less than 32,768 lines in length and where the concatenation of the files contain all of the code for the complete amalgamation. Then there is a separate source file named "sqlite3-all.c" which basically consists of code like this: | | | | 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 | <p>To circumvent this limitation, the amalgamation is also available in a split form, consisting of files "sqlite3-1.c", "sqlite3-2.c", and so forth, where each file is less than 32,768 lines in length and where the concatenation of the files contain all of the code for the complete amalgamation. Then there is a separate source file named "sqlite3-all.c" which basically consists of code like this: <codeblock> #include "sqlite3-1.c" #include "sqlite3-2.c" #include "sqlite3-3.c" #include "sqlite3-4.c" #include "sqlite3-5.c" #include "sqlite3-6.c" #include "sqlite3-7.c" </codeblock> <p>Applications using the split amalgamation simply compile against "sqlite3-all.c" instead of "sqlite3.c". The two files work exactly the same. But with "sqlite3-all.c", no single source file contains more than 32,767 lines of code, and so it is more convenient to use some debuggers. The downside of the split amalgamation is that it consists of 6 C source code files instead of just 1. |
︙ | ︙ | |||
110 111 112 113 114 115 116 | split amalgamation), first [get the canonical source code] from one of the three servers. Then, on both unix-like systems and on Windows systems that have the free [http://mingw.org/wiki/msys|MinGW] development environment installed, the amalgamation can be built using the following commands: | | | | | | 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 | split amalgamation), first [get the canonical source code] from one of the three servers. Then, on both unix-like systems and on Windows systems that have the free [http://mingw.org/wiki/msys|MinGW] development environment installed, the amalgamation can be built using the following commands: <codeblock> sh configure make sqlite3.c </codeblock> <p>To build using Microsoft Visual C++, run this command: <codeblock> nmake /f makefile.msc sqlite3.c </codeblock> <p>In both cases, the split amalgamation can be obtained by substituting "sqlite3-all.c" for "sqlite3.c" as the make target. <h2>Dependencies</h2> <p>The build process makes extensive use of the |
︙ | ︙ |
Changes to pages/autoinc.in.
︙ | ︙ | |||
53 54 55 56 57 58 59 | <p> ^When a new row is inserted into an SQLite table, the ROWID can either be specified as part of the INSERT statement or it can be assigned automatically by the database engine. ^(To specify a ROWID manually, just include it in the list of values to be inserted. For example: </p> | | | | 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 | <p> ^When a new row is inserted into an SQLite table, the ROWID can either be specified as part of the INSERT statement or it can be assigned automatically by the database engine. ^(To specify a ROWID manually, just include it in the list of values to be inserted. For example: </p> <codeblock> CREATE TABLE test1(a INT, b TEXT); INSERT INTO test1(rowid, a, b) VALUES(123, 5, 'hello'); </codeblock> <p> ^If no ROWID is specified on the insert, or if the specified ROWID has a value of NULL, then an appropriate ROWID is created automatically. ^The usual algorithm is to give the newly created row a ROWID that is one larger than the largest ROWID in the table prior to the insert. ^If the table is initially empty, then a ROWID of 1 is |
︙ | ︙ |
Changes to pages/dbstat.in.
︙ | ︙ | |||
26 27 28 29 30 31 32 | <p> ^The dbstat virtual table is an [eponymous virtual table], meaning that is not necessary to run [CREATE VIRTUAL TABLE] to create an instance of the dbstat virtual table before using it. The "dbstat" module name can be used as if it were a table name to query the dbstat virtual table directly. For example: | | | | | | | | 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 | <p> ^The dbstat virtual table is an [eponymous virtual table], meaning that is not necessary to run [CREATE VIRTUAL TABLE] to create an instance of the dbstat virtual table before using it. The "dbstat" module name can be used as if it were a table name to query the dbstat virtual table directly. For example: <codeblock> SELECT * FROM dbstat; </codeblock> <p> If a named virtual table that uses the dbstat module is desired, then the recommended way to create an instance of the dbstat virtual table is as follows: <codeblock> CREATE VIRTUAL TABLE temp.stat USING dbstat(main); </codeblock> <p> Note the "temp." qualifier before the virtual table name ("stat"). This qualifier causes the virtual table to be temporary - to only exist for the duration of the current database connection. This is the recommended approach. <p> The "main" argument to dbstat is default schema for which information is to be provided. The default is "main", and so the use of "main" in the example above is redundant. For any particular query, the schema can be changed by specifying the alternative schema as a function argument to the virtual table name in the FROM clause of the query. (See further discussion of [table-valued functions in the FROM clause] for more details.) <p> The schema for the dbstat virtual table is this: <codeblock> CREATE TABLE dbstat( name STRING, -- Name of table or index path INTEGER, -- Path to page from root pageno INTEGER, -- Page number pagetype STRING, -- 'internal', 'leaf' or 'overflow' ncell INTEGER, -- Cells on page (0 for overflow) payload INTEGER, -- Bytes of payload on this page unused INTEGER, -- Bytes of unused space on this page mx_payload INTEGER, -- Largest payload size of all cells on this page pgoffset INTEGER, -- Offset of page in file pgsize INTEGER, -- Size of the page schema TEXT HIDDEN -- The database being analyzed ); </codeblock> <p> There is a single row of the dbstat table for each page in the database file. Freelist pages, the lock page, and pointer-map pages of the database file do not appear in the dbstat virtual table. |
︙ | ︙ | |||
99 100 101 102 103 104 105 | Overflow pages are specified by appending a '+' character and a six-digit hexadecimal value to the path to the cell they are linked from. For example, the three overflow pages in a chain linked from the left-most cell of the 450th child of the root page are identified by the paths: | | | | | | | | | | | | | | 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 | Overflow pages are specified by appending a '+' character and a six-digit hexadecimal value to the path to the cell they are linked from. For example, the three overflow pages in a chain linked from the left-most cell of the 450th child of the root page are identified by the paths: <codeblock> '/1c2/000+000000' // First page in overflow chain '/1c2/000+000001' // Second page in overflow chain '/1c2/000+000002' // Third page in overflow chain </codeblock> <p> If the paths are sorted using the BINARY collation sequence, then the overflow pages associated with a cell will appear earlier in the sort-order than its child page: <codeblock> '/1c2/000/' // Left-most child of 451st child of root </codeblock> <h1>Example uses of the dbstat virtual table</h1> <p> To find the total number of pages used to store table "xyz" in schema "aux1", use: <codeblock> SELECT count(*) FROM dbstat('aux1') WHERE name='xyz'; </codeblock> <p> To see how efficiently the content of a table is stored on disk, compute the amount of space used to hold actual content divided by the total amount of disk space used. The closer this number is to 100%, the more efficient the packing. (In this example, the 'xyz' table is assumed to be in the 'main' schema.) <codeblock> SELECT sum(pgsize-unused)/sum(pgsize) FROM dbstat WHERE name='xyz'; </codeblock> <p> To find the average fan-out for a table, run: <codeblock> SELECT avg(ncell) FROM dbstat WHERE name='xyz' AND pagetype='internal'; </codeblock> <p> Modern filesystems operate faster when disk accesses are sequential. Hence, SQLite will run faster if the content of the database file is on sequential pages. To find out what fraction of the pages in a database are sequential (and thus obtain a measurement that might be useful in determining when to [VACUUM]), run a query like the following: <codeblock> CREATE TEMP TABLE s(rowid INTEGER PRIMARY KEY, pageno INT); INSERT INTO s(pageno) SELECT pageno FROM dbstat ORDER BY path; SELECT sum(s1.pageno+1==s2.pageno)*1.0/count(*) FROM s AS s1, s AS s2 WHERE s1.rowid+1=s2.rowid; DROP TABLE s; </codeblock> |
Changes to pages/partialindex.in.
1 2 3 4 | <title>Partial Indexes</title> <tcl> hd_keywords {partial index} {partial indexes} {partial indices} </tcl> | | | 1 2 3 4 5 6 7 8 9 10 11 12 | <title>Partial Indexes</title> <tcl> hd_keywords {partial index} {partial indexes} {partial indices} </tcl> <table_of_contents> <h1>Introduction</h1> <p> A partial index is an index over a subset of the rows of a table. </p> |
︙ | ︙ | |||
55 56 57 58 59 60 61 | <p> ^The columns referenced in the WHERE clause of a partial index can be any of the columns in the table, not just columns that happen to be indexed. However, it is very common for the WHERE clause expression of a partial index to be a simple expression on the column being indexed. The following is a typical example:</p> | | | | | | | | | | | | 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 | <p> ^The columns referenced in the WHERE clause of a partial index can be any of the columns in the table, not just columns that happen to be indexed. However, it is very common for the WHERE clause expression of a partial index to be a simple expression on the column being indexed. The following is a typical example:</p> <codeblock> ^(CREATE INDEX po_parent ON purchaseorder(parent_po) WHERE parent_po IS NOT NULL;)^ </codeblock> <p>In the example above, if most purchase orders do not have a "parent" purchase order, then most parent_po values will be NULL. That means only a small subset of the rows in the purchaseorder table will be indexed. Hence the index will take up much less space. And changes to the original purchaseorder table will run faster since the po_parent index only needs to be updated for those exceptional rows where parent_po is not NULL. But the index is still useful for querying. ^(In particular, if one wants to know all "children" of a particular purchase order "?1", the query would be: <codeblock> SELECT po_num FROM purchaseorder WHERE parent_po=?1; </codeblock>)^ <p>^The query above will use the po_parent index to help find the answer, since the po_parent index contains entries for all rows of interest. Note that since po_parent is smaller than a full index, the query will likely run faster too.</p> <h2>Unique Partial Indexes</h2> <p>^A partial index definition may include the UNIQUE keyword. ^If it does, then SQLite requires every entry <em>in the index</em> to be unique. This provides a mechanism for enforcing uniqueness across some subset of the rows in a table.</p> <p>For example, suppose you have a database of the members of a large organization where each person is assigned to a particular "team". Each team has a "leader" who is also a member of that team. ^(The table might look something like this:</p> <codeblock> CREATE TABLE person( person_id INTEGER PRIMARY KEY, team_id INTEGER REFERENCES team, is_team_leader BOOLEAN, -- other fields elided ); </codeblock>)^ <p>The team_id field cannot be unique because there usually multiple people on the same team. One cannot make the combination of team_id and is_team_leader unique since there are usually multiple non-leaders on each team. ^(The solution to enforcing one leader per team is to create a unique index on team_id but restricted to those entries for which is_team_leader is true:</p> <codeblock> CREATE UNIQUE INDEX team_leader ON person(team_id) WHERE is_team_leader; </codeblock>)^ ^(<p>Coincidentally, that same index is useful for locating the team leader of a particular team:</p> <codeblock> SELECT person_id FROM person WHERE is_team_leader AND team_id=?1; </codeblock>)^ <h1>Queries Using Partial Indexes</h1> <p>Let X be the expression in the WHERE clause of a partial index, and let W be the WHERE clause of a query that uses the table that is indexed. Then, the query is permitted to use the partial index if W⇒X, where the ⇒ operator |
︙ | ︙ | |||
141 142 143 144 145 146 147 | <ol> <li><p>If W is AND-connected terms and X is OR-connected terms and if any term of W appears as a term of X, then the partial index is usable.</p> <p>^(For example, let the index be | | | | | | | | | | | | > | 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 | <ol> <li><p>If W is AND-connected terms and X is OR-connected terms and if any term of W appears as a term of X, then the partial index is usable.</p> <p>^(For example, let the index be <codeblock> CREATE INDEX ex1 ON tab1(a,b) WHERE a=5 OR b=6; </codeblock> <p>And let the query be: <codeblock> SELECT * FROM tab1 WHERE b=6 AND a=7; <i>-- uses partial index</i> </codeblock> <p>Then the index is usable by the query because the "b=6" term appears in both the index definition and in the query.)^ Remember: terms in the index should be OR-connected and terms in the query should be AND-connected.</p> <p>The terms in W and X must match exactly. SQLite does not do algebra to try to get them to look the same. ^The term "b=6" does not match "b=3+3" or "b-6=0" or "b BETWEEN 6 AND 6". ^"b=6" will match to "6=b" as long as "b=6" is on the index and "6=b" is in the query. ^If a term of the form "6=b" appears in the index, it will never match anything.</p> <li><p>^If a term in X is of the form "z IS NOT NULL" and if a term in W is a comparison operator on "z" other than "IS", then those terms match.</p> ^(<p>Example: Let the index be <codeblock> CREATE INDEX ex2 ON tab2(b,c) WHERE c IS NOT NULL; </codeblock> <p>Then any query that uses operators =, <, >, <=, >=, <>, or IN on column "c" would be usable with the partial index because those comparison operators are only true if "c" is not NULL.)^ ^(So the following query could use the partial index: <codeblock> SELECT * FROM tab2 WHERE b=456 AND c<>0; <i>-- uses partial index</i> </codeblock>)^ ^(<p>But the next query can not use the partial index: <codeblock> SELECT * FROM tab2 WHERE b=456; <i>-- cannot use partial index</i> </codeblock>)^ <p>The latter query can not use the partial index because there might be rows in the table with b=456 and where c is NULL. But those rows would not be in the partial index. </ol> <p>These two rules describe how the query planner for SQLite works as of this writing (2013-08-01). And the rules above will always be honored. However, future versions of SQLite might incorporate a better theorem prover that can find other cases where W⇒X is true and thus may find more instances where partial indexes are useful.</p> <h1>Supported Versions</h1> <p> Partial indexes have been supported in SQLite since version 3.8.0 (2013-08-26). </p> <p>Database files that contain partial indices are not readable or writable by versions of SQLite prior to 3.8.0. However, a database file created by SQLite 3.8.0 is still readable and writable by prior versions as long as its schema contains no partial indexes. A database that is unreadable by legacy versions of SQLite can be made readable simply by running [DROP INDEX] on the partial indexes.</p> |
Changes to rawpages/sqlite.css.
︙ | ︙ | |||
194 195 196 197 198 199 200 201 202 203 204 205 206 207 | .fancy-toc1 {margin-left:6ex;} .fancy-toc2 {margin-left:12ex;} .fancy-toc3 {margin-left:18ex;} .fancy-toc4 {margin-left:24ex;} .fancy-toc5 {margin-left:30ex;} .fancy-toc6 {margin-left:36ex;} .fancy .codeblock {margin:0 10ex;} } @media screen and (max-width: 600px){ /* Indentation amounts for narrow mobile screens */ .toct {margin-left:0;} .fancy-toc1 {padding-left:4ex;text-indent:-2ex;} .fancy-toc2 {padding-left:8ex;text-indent:-4ex;} .fancy-toc3 {padding-left:12ex;text-indent:-6ex;} | > > | 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 | .fancy-toc1 {margin-left:6ex;} .fancy-toc2 {margin-left:12ex;} .fancy-toc3 {margin-left:18ex;} .fancy-toc4 {margin-left:24ex;} .fancy-toc5 {margin-left:30ex;} .fancy-toc6 {margin-left:36ex;} .fancy .codeblock {margin:0 10ex;} .fancy ol .codeblock {margin:0 5ex;} .fancy .imgcontainer {margin: 1em 5ex} } @media screen and (max-width: 600px){ /* Indentation amounts for narrow mobile screens */ .toct {margin-left:0;} .fancy-toc1 {padding-left:4ex;text-indent:-2ex;} .fancy-toc2 {padding-left:8ex;text-indent:-4ex;} .fancy-toc3 {padding-left:12ex;text-indent:-6ex;} |
︙ | ︙ |