Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Updates to the CREATE TABLE documentation. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
be570dc0313947a050fe49535b0c9bea |
User & Date: | drh 2008-04-27 15:10:15.000 |
Context
2008-04-27
| ||
17:51 | Updates to the SQL language documentation with many added hyperlink targets. (check-in: f060763119 user: drh tags: trunk) | |
15:10 | Updates to the CREATE TABLE documentation. (check-in: be570dc031 user: drh tags: trunk) | |
2008-04-25
| ||
12:31 | Update the atomiccommit document to reflect the latest PRAGMA journal_mode changes. (check-in: ee0e82d0aa user: drh tags: trunk) | |
Changes
Changes to pages/lang.in.
︙ | ︙ | |||
518 519 520 521 522 523 524 | <p>According to the SQL standard, PRIMARY KEY should imply NOT NULL. Unfortunately, due to a long-standing coding oversight, this is not the case in SQLite. SQLite allows NULL values in a PRIMARY KEY column. We could change SQLite to conform to the standard (and we might do so in the future), but by the time the oversight was discovered, SQLite was in such wide use that we feared breaking legacy code if we fixed the problem. So for now we have | | | 518 519 520 521 522 523 524 525 526 527 528 529 530 531 532 | <p>According to the SQL standard, PRIMARY KEY should imply NOT NULL. Unfortunately, due to a long-standing coding oversight, this is not the case in SQLite. SQLite allows NULL values in a PRIMARY KEY column. We could change SQLite to conform to the standard (and we might do so in the future), but by the time the oversight was discovered, SQLite was in such wide use that we feared breaking legacy code if we fixed the problem. So for now we have chosen to continue allowing NULLs in PRIMARY KEY columns. Developers should be aware, however, that we may change SQLite to conform to the SQL standard in future and should design new programs accordingly.</p> <p>If the "TEMP" or "TEMPORARY" keyword occurs in between "CREATE" and "TABLE" then the table that is created is only visible within that same database connection |
︙ | ︙ | |||
551 552 553 554 555 556 557 | default algorithm specified in the CREATE TABLE statement. See the section titled <a href="lang_conflict.html">ON CONFLICT</a> for additional information.</p> <p>CHECK constraints are supported as of version 3.3.0. Prior to version 3.3.0, CHECK constraints were parsed but not enforced.</p> | < | > | | > | | | 551 552 553 554 555 556 557 558 559 560 561 562 563 564 565 566 567 568 569 570 571 572 573 574 575 576 577 | default algorithm specified in the CREATE TABLE statement. See the section titled <a href="lang_conflict.html">ON CONFLICT</a> for additional information.</p> <p>CHECK constraints are supported as of version 3.3.0. Prior to version 3.3.0, CHECK constraints were parsed but not enforced.</p> <p>The number of columns in a table is limited by the [SQLITE_MAX_COLUMN] compile-time parameter. A single row of a table cannot store more than [SQLITE_MAX_LENGTH] bytes of data. Both of these limits can be lowered at runtime using the [sqlite3_limit()] C/C++ interface.</p> <p>The CREATE TABLE AS form defines the table to be the result set of a query. The names of the table columns are the names of the columns in the result.</p> <p>The text of each CREATE TABLE statement is stored in the <b>sqlite_master</b> table. Every time the database is opened, all CREATE TABLE statements are read from the <b>sqlite_master</b> table and used to regenerate SQLite's internal representation of the table layout. If the original command was a CREATE TABLE AS then then an equivalent CREATE TABLE statement is synthesized and store in <b>sqlite_master</b> in place of the original command. |
︙ | ︙ |
Changes to pages/limits.in.
︙ | ︙ | |||
39 40 41 42 43 44 45 | an application might want to recompile SQLite with much lower limits to avoid excess resource utilization in the event of bug in higher-level SQL statement generators or to help thwart attackers who inject malicious SQL statements. </p> <tcl> | | > > > > > > | | 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 | an application might want to recompile SQLite with much lower limits to avoid excess resource utilization in the event of bug in higher-level SQL statement generators or to help thwart attackers who inject malicious SQL statements. </p> <tcl> proc limititem {title keywords text} { if {[llength $keywords]} { set frag [string tolower [lindex $keywords 0]] regsub {sqlite_} $frag {} frag hd_fragment $frag foreach kw $keywords {hd_keywords $kw} } hd_resolve "<li><p><b>$title</b></p>\n$text</li>" } hd_puts { <ol> } limititem {Maximum length of a string or BLOB} SQLITE_MAX_LENGTH { <p> The maximum number of bytes in a string or BLOB in SQLite is defined by the preprocessor macro SQLITE_MAX_LENGTH. The default value of this macro is 1 billion (1 thousand million or 1,000,000,000). You can raise or lower this value at compile-time using a command-line option like this: </p> |
︙ | ︙ | |||
76 77 78 79 80 81 82 | During part of SQLite's INSERT and SELECT processing, the complete content of each row in the database is encoded as a single BLOB. So the SQLITE_MAX_LENGTH parameter also determines the maximum number of bytes in a row. </p> } | | | 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 | During part of SQLite's INSERT and SELECT processing, the complete content of each row in the database is encoded as a single BLOB. So the SQLITE_MAX_LENGTH parameter also determines the maximum number of bytes in a row. </p> } limititem {Maximum Number Of Columns} SQLITE_MAX_COLUMN { <p> The SQLITE_MAX_COLUMN compile-time parameter is used to set an upper bound on: </p> <ul> <li>The number of columns in a table</li> |
︙ | ︙ | |||
111 112 113 114 115 116 117 | that are O(N²) where N is the number of columns. So if you redefine SQLITE_MAX_COLUMN to be a really huge number and you generate SQL that uses a large number of columns, you may find that [sqlite3_prepare_v2()] runs slowly. } | | | 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 | that are O(N²) where N is the number of columns. So if you redefine SQLITE_MAX_COLUMN to be a really huge number and you generate SQL that uses a large number of columns, you may find that [sqlite3_prepare_v2()] runs slowly. } limititem {Maximum Length Of An SQL Statement} SQLITE_MAX_SQL_LENGTH { <p> The maximum number of bytes in the text of an SQL statement is limited to SQLITE_MAX_SQL_LENGTH which defaults to 1000000. You can redefine this limit to be as large as the smaller of SQLITE_MAX_LENGTH and 1073741824. </p> |
︙ | ︙ | |||
140 141 142 143 144 145 146 | to bind your large string values to the SQL statement. The use of binding obviates the need to escape quote characters in the string, reducing the risk of SQL injection attacks. It is also runs faster since the large string does not need to be parsed or copied as much. </p> } | | | | | > | > | > | 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 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 | to bind your large string values to the SQL statement. The use of binding obviates the need to escape quote characters in the string, reducing the risk of SQL injection attacks. It is also runs faster since the large string does not need to be parsed or copied as much. </p> } limititem {Maximum Number Of Tables In A Join} {} { <p> SQLite does not support joins containing more than 64 tables. This limit arises from the fact that the SQLite code generator uses bitmaps with one bit per join-table in the query optimizer. </p> } limititem {Maximum Depth Of An Expression Tree} SQLITE_MAX_EXPR_DEPTH { <p> SQLite parses expressions into a tree for processing. During code generation, SQLite walks this tree recursively. The depth of expression trees is therefore limited in order to avoid using too much stack space. </p> <p> The SQLITE_MAX_EXPR_DEPTH parameter determines the maximum expression tree depth. If the value is 0, then no limit is enforced. The current implementation has a default value of 1000. </p> } limititem {Maximum Number Of Arguments On A Function} SQLITE_MAX_FUNCTION_ARG { <p> The SQLITE_MAX_FUNCTION_ARG parameter determines the maximum number of parameters that can be passed to an SQL function. The default value of this limit is 100. We know of no technical reason why SQLite would not work with functions that have millions of parameters. However, we suspect that anybody who tries to invoke a function with millions of parameters is really trying to find security exploits in systems that use SQLite, not do useful work, and so for that reason we have set this parameter relatively low. } limititem {Maximum Number Of Terms In A Compound SELECT Statement} \ SQLITE_MAX_COMPOUND_SELECT { <p> A compound SELECT statement is two or more SELECT statements connected by operators UNION, UNION ALL, EXCEPT, or INTERSECT. We call each individual SELECT statement within a compound SELECT a "term". </p> <p> The code generator in SQLite processes compound SELECT statements using a recursive algorithm. In order to limit the size of the stack, we therefore limit the number of terms in a compound SELECT. The maximum number of terms is SQLITE_MAX_COMPOUND_SELECT which defaults to 500. We think this is a generous allotment since in practice we almost never see the number of terms in a compound select exceed single digits. </p> } limititem {Maximum Length Of A LIKE Or GLOB Pattern} \ SQLITE_MAX_LIKE_PATTERN_LENGTH { <p> The pattern matching algorithm used in the default LIKE and GLOB implementation of SQLite can exhibit O(N²) performance (where N is the number of characters in the pattern) for certain pathological cases. To avoid denial-of-service attacks from miscreants who are able to specify their own LIKE or GLOB patterns, the length of the LIKE or GLOB pattern is limited to SQLITE_MAX_LIKE_PATTERN_LENGTH bytes. The default value of this limit is 50000. A modern workstation can evaluate even a pathological LIKE or GLOB pattern of 50000 bytes relatively quickly. The denial of service problem only comes into play when the pattern length gets into millions of bytes. Nevertheless, since most useful LIKE or GLOB patterns are at most a few dozen bytes in length, paranoid application developers may want to reduce this parameter to something in the range of a few hundred if they know that external users are able to generate arbitrary patterns. </p> } limititem {Maximum Number Of Host Parameters In A Single SQL Statement} \ SQLITE_MAX_VARIABLE_NUMBER { <p> A host parameter is a place-holder in an SQL statement that is filled in using one of the <a href="c3ref/bind_blob.html">sqlite3_bind_XXXX()</a> interfaces. Many SQL programmers are familiar with using a question mark ("?") as a host parameter. SQLite also supports named host parameters prefaced by ":", "$", or "@" and numbered host parameters of the form "?123". |
︙ | ︙ | |||
235 236 237 238 239 240 241 | <p> The maximum value of a host parameter number is SQLITE_MAX_VARIABLE_NUMBER. This setting defaults to 999. </p> } | | | | > | 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 | <p> The maximum value of a host parameter number is SQLITE_MAX_VARIABLE_NUMBER. This setting defaults to 999. </p> } limititem {Maximum Number Of Attached Databases} SQLITE_MAX_ATTACHED { <p> The <a href="lang_attach.html">ATTACH</a> statement is an SQLite extension that allows two or more databases to be associated to the same database connection and to operate as if they were a single database. The number of simulataneously attached databases is limited to SQLITE_MAX_ATTACHED which is set to 10 by default. The code generator in SQLite uses bitmaps to keep track of attached databases. That means that the number of attached databases cannot be increased above 30 on a 32-bit machine or 62 on a 64-bit machine. } limititem {Maximum Database Page Size} SQLITE_MAX_PAGE_SIZE { <p> An SQLite database file is organized as pages. The size of each page is a power of 2 between 512 and SQLITE_MAX_PAGE_SIZE. The default value for SQLITE_MAX_PAGE_SIZE is 32768. The current implementation will not support a larger value. </p> <p> It used to be the case that SQLite would allocate some stack structures whose size was proportional to the maximum page size. For this reason, SQLite would sometimes be compiled with a smaller maximum page size on embedded devices with limited stack memory. But more recent versions of SQLite put these large structures on the heap, not on the stack, so reducing the maximum page size is no longer necessary on embedded devices. </p> } limititem {Maximum Number Of Pages In A Database File} \ SQLITE_MAX_PAGE_COUNT { <p> SQLite is able to limit the size of a database file to prevent the database file from growing too large and consuming too much disk or flash space. The SQLITE_MAX_PAGE_COUNT parameter, which is normally set to 1073741823, is the maximum number of pages allowed in a single database file. An attempt to insert new data that would cause |
︙ | ︙ |