Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Updates to the FAQ. (CVS 2705) |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
0eaf430d9538ece1a3d1300db91f2695 |
User & Date: | drh 2005-09-17 02:34:05.000 |
Context
2005-09-17
| ||
13:07 | Make sure dependencies on the right-hand side of IN operators are checked correctly. Ticket #1433. (CVS 2706) (check-in: 21740794ab user: drh tags: trunk) | |
02:34 | Updates to the FAQ. (CVS 2705) (check-in: 0eaf430d95 user: drh tags: trunk) | |
2005-09-16
| ||
17:16 | Do not journal the locking page which doing an autovacuum. Similar to the problem of #1432 except that this one occurs on autovacuum instead of vacuum. An assert() has been added to catch any future incidents of this type. (CVS 2704) (check-in: 5b6dc12b7d user: drh tags: trunk) | |
Changes
Changes to www/datatype3.tcl.
|
| | | 1 2 3 4 5 6 7 8 | set rcsid {$Id: datatype3.tcl,v 1.11 2005/09/17 02:34:05 drh Exp $} source common.tcl header {Datatypes In SQLite Version 3} puts { <h2>Datatypes In SQLite Version 3</h2> <h3>1. Storage Classes</h3> |
︙ | ︙ | |||
52 53 54 55 56 57 58 59 60 61 62 63 64 65 | </UL> <P>The storage class of a value that is the result of an SQL scalar operator depends on the outermost operator of the expression. User-defined functions may return values with any storage class. It is not generally possible to determine the storage class of the result of an expression at compile time.</P> <h3>2. Column Affinity</h3> <p> In SQLite version 3, the type of a value is associated with the value itself, not with the column or variable in which the value is stored. (This is sometimes called <a href="http://www.cliki.net/manifest%20type%20system"> | > | 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 | </UL> <P>The storage class of a value that is the result of an SQL scalar operator depends on the outermost operator of the expression. User-defined functions may return values with any storage class. It is not generally possible to determine the storage class of the result of an expression at compile time.</P> <a name="affinity"> <h3>2. Column Affinity</h3> <p> In SQLite version 3, the type of a value is associated with the value itself, not with the column or variable in which the value is stored. (This is sometimes called <a href="http://www.cliki.net/manifest%20type%20system"> |
︙ | ︙ |
Changes to www/faq.tcl.
1 2 3 | # # Run this script to generated a faq.html output file # | | | | > > < < < < < < < < < | > | | < > > > > > > > > > | < < < < < < < < < < < < | < | > | < | | | < < < < < | < < < < < < | < < | | | | < < < | < < < < < < < < < < < < < < < < < < < < < < < | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 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 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 | # # Run this script to generated a faq.html output file # set rcsid {$Id: faq.tcl,v 1.31 2005/09/17 02:34:05 drh Exp $} source common.tcl header {SQLite Frequently Asked Questions</title>} set cnt 1 proc faq {question answer} { set ::faq($::cnt) [list [string trim $question] [string trim $answer]] incr ::cnt } ############# # Enter questions and answers here. faq { How do I create an AUTOINCREMENT field. } { <p>Short answer: A column declared INTEGER PRIMARY KEY will autoincrement.</p> <p>Here is the long answer: If you declare a column of a table to be INTEGER PRIMARY KEY, then whenever you insert a NULL into that column of the table, the NULL is automatically converted into an integer which is one greater than the largest value of that column over all other rows in the table, or 1 if the table is empty. (If the largest possible integer key, 9223372036854775807, then an unused key value is chosen at random.) For example, suppose you have a table like this: <blockquote><pre> CREATE TABLE t1( a INTEGER PRIMARY KEY, b INTEGER ); </pre></blockquote> <p>With this table, the statement</p> <blockquote><pre> INSERT INTO t1 VALUES(NULL,123); </pre></blockquote> <p>is logically equivalent to saying:</p> <blockquote><pre> INSERT INTO t1 VALUES((SELECT max(a) FROM t1)+1,123); </pre></blockquote> <p>There is a new API function named <a href="capi3ref.html#sqlite3_last_insert_rowid"> sqlite3_last_insert_rowid()</a> which will return the integer key for the most recent insert operation.</p> <p>Note that the integer key is one greater than the largest key that was in the table just prior to the insert. The new key will be unique over all keys currently in the table, but it might overlap with keys that have been previously deleted from the table. To create keys that are unique over the lifetime of the table, add the AUTOINCREMENT keyword to the INTEGER PRIMARY KEY declaration. Then the key chosen will be one more than than the largest key that has ever existed in that table. If the largest possible key has previously existed in that table, then the INSERT will fail with an SQLITE_FULL error code.</p> } faq { What datatypes does SQLite support? } { <p>See <a href="datatype3.html">http://www.sqlite.org/datatype3.html</a>.</p> } faq { SQLite lets me insert a string into a database column of type integer! } { <p>This is a feature, not a bug. SQLite does not enforce data type constraints. Any data can be inserted into any column. You can put arbitrary length strings into integer columns, floating point numbers in boolean columns, or dates in character columns. The datatype you assign to a column in the CREATE TABLE command does not restrict what data can be put into that column. Every column is able to hold an arbitrary length string. (There is one exception: Columns of type INTEGER PRIMARY KEY may only hold a 64-bit signed integer. An error will result if you try to put anything other than an integer into an INTEGER PRIMARY KEY column.)</p> <p>But SQLite does use the declared type of a column as a hint that you prefer values in that format. So, for example, if a column is of type INTEGER and you try to insert a string into that column, SQLite will attempt to convert the string into an integer. If it can, it inserts the integer instead. If not, it inserts the string. This feature is sometimes call <a href="datatype3.html#affinity">type or column affinity</a>. </p> } faq { Why does SQLite think that the expression '0'=='00' is TRUE? } { <p>As of version 2.7.0, it doesn't. See the document on <a href="datatype3.html">datatypes in SQLite version 3</a> for details.</p> } faq { Why doesn't SQLite allow me to use '0' and '0.0' as the primary key on two different rows of the same table? } { <p>Your primary key must have a numeric type. Change the datatype of |
︙ | ︙ | |||
182 183 184 185 186 187 188 | faq { Can multiple applications or multiple instances of the same application access a single database file at the same time? } { <p>Multiple processes can have the same database open at the same time. Multiple processes can be doing a SELECT at the same time. But only one process can be making changes to | | > | < < < < < < | | | > | > > > > > > | | > > > > | > | > | | > | | < < < < < < | > | > | | | < | | < < | 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 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 | faq { Can multiple applications or multiple instances of the same application access a single database file at the same time? } { <p>Multiple processes can have the same database open at the same time. Multiple processes can be doing a SELECT at the same time. But only one process can be making changes to the database at an given moement in time, however.</p> <p>SQLite uses reader/writer locks to control access to the database. (Under Win95/98/ME which lacks support for reader/writer locks, a probabilistic simulation is used instead.) But use caution: this locking mechanism might not work correctly if the database file is kept on an NFS filesystem. This is because fcntl() file locking is broken on many NFS implementations. You should avoid putting SQLite database files on NFS if multiple processes might try to access the file at the same time. On Windows, Microsoft's documentation says that locking may not work under FAT filesystems if you are not running the Share.exe daemon. People who have a lot of experience with Windows tell me that file locking of network files is very buggy and is not dependable. If what they say is true, sharing an SQLite database between two or more Windows machines might cause unexpected problems.</p> <p>We are aware of no other <i>embedded</i> SQL database engine that supports as much concurrancy as SQLite. SQLite allows multiple processes to have the database file open at once, and for multiple processes to read the database at once. When any process wants to write, it must lock the entire database file for the duration of its update. But that normally only takes a few milliseconds. Other processes just wait on the writer to finish then continue about their business. Other embedded SQL database engines typically only allow a single process to connect to the database at once.</p> <p>However, client/server database engines (such as PostgreSQL, MySQL, or Oracle) usually support a higher level of concurrency and allow multiple processes to be writing to the same database at the same time. This is possible in a client/server database because there is always a single well-controlled server process available to coordinate access. If your application has a need for a lot of concurrency, then you should consider using a client/server database. But experience suggests that most applications need much less concurrency than their designers imagine. </p> <p>When SQLite tries to access a file that is locked by another process, the default behavior is to return SQLITE_BUSY. You can adjust this behavior from C code using the <a href="capi3ref#sqlite3_busy_handler">sqlite3_busy_handler()</a> or <a href="capi3ref#sqlite3_busy_timeout">sqlite3_busy_timeout()</a> API functions.</p> } faq { Is SQLite threadsafe? } { <p>Yes. Sometimes. In order to be thread-safe, SQLite must be compiled with the THREADSAFE preprocessor macro set to 1. In the default distribution, the windows binaries are compiled to be threadsafe but the linux binaries are not. If you want to change this, you'll have to recompile.</p> <p>"Threadsafe" in the previous paragraph means that two or more threads can run SQLite at the same time on different "<b>sqlite3</b>" structures returned from separate calls to <a href="capi3ref#sqlite3_open">sqlite3_open()</a>. It is never safe to use the same <b>sqlite3</b> structure pointer in two or more threads.</p> <p>An <b>sqlite3</b> structure can only be used in the same thread that called <a href="capi3ref#sqlite3_open">sqlite3_open</a> to create it. You cannot open a database in one thread then pass the handle off to another thread for it to use. This is due to limitations (bugs?) in many common threading implementations such as on RedHat9. There may be ways to work around these limitations, but they are complex and exceedingly difficult to test for correctness. For that reason, SQLite currently takes the safe approach and disallows the sharing of handles among threads.</p> <p>Under UNIX, you should not carry an open SQLite database across a fork() system call into the child process. Problems will result if you do.</p> } faq { |
︙ | ︙ | |||
317 318 319 320 321 322 323 | ORDER BY name </pre></blockquote> } faq { Are there any known size limits to SQLite databases? } { | | > | > > | > > > | > > > > | > > > > | < > > > > | | | | > > | < < < < < < < < < < < | > > > > | | 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 | ORDER BY name </pre></blockquote> } faq { Are there any known size limits to SQLite databases? } { <p>A database is limited in size to 2 tibibytes (2<sup>41</sup> bytes). That is a theoretical limitation. In practice, you should try to keep your SQLite databases below 100 gigabytes to avoid performance problems. If you need to store 100 gigabytes or more in a database, consider using an enterprise database engine which is designed for that purpose.</p> <p>The theoretical limit on the number of rows in a table is 2<sup>64</sup>-1, though obviously you will run into the file size limitation prior to reaching the row limit. A single row can hold up to 2<sup>30</sup> bytes of data in the current implementation. The underlying file format supports row sizes up to about 2<sup>62</sup> bytes. </p> <p>There are probably limits on the number of tables or indices or the number of columns in a table or index, but nobody is sure what those limits are. In practice, SQLite must read and parse the original SQL of all table and index declarations everytime a new database file is opened, so for the best performance of <a href="capi3ref.html#sqlite3_open">sqlite3_open()</a> it is best to keep down the number of declared tables. Likewise, though there is no limit on the number of columns in a table, more than a few hundred seems extreme. Only the first 31 columns of a table are candidates for certain optimizations. You can put as many columns in an index as you like but indexes with more than 30 columns will not be used to optimize queries. </p> <p>The names of tables, indices, view, triggers, and columns can be as long as desired. However, the names of SQL functions (as created by the <a href="capi3ref.html#sqlite3_create_function">sqlite3_create_function()</a> API) may not exceed 255 characters in length.</p> } faq { What is the maximum size of a VARCHAR in SQLite? } { <p>SQLite does not enforce the length of a VARCHAR. You can declare a VARCHAR(10) and SQLite will be happy to let you put 500 characters in it. And it will keep all 500 characters intact - it never truncates. </p> } faq { Does SQLite support a BLOB type? } { <p>SQLite versions 3.0 and leter let you puts BLOB data into any column, even columns that are declared to hold some other type.</p> } faq { How do I add or delete columns from an existing table in SQLite. } { <p>SQLite has limited <a href="lang_altertable.html">ALTER TABLE</a> support that you can use to add a column to the end of a table or to change the name of a table. If you what make more complex changes the structure of a table, you will have to recreate the table. You can save existing data to a temporary table, drop the old table, create the new table, then copy the data back in from the temporary table.</p> <p>For example, suppose you have a table named "t1" with columns names "a", "b", and "c" and that you want to delete column "c" from this table. The following steps illustrate how this could be done: |
︙ | ︙ | |||
394 395 396 397 398 399 400 | } { <p>No. When you delete information from an SQLite database, the unused disk space is added to an internal "free-list" and is reused the next time you insert data. The disk space is not lost. But neither is it returned to the operating system.</p> <p>If you delete a lot of data and want to shrink the database file, | > | | > | > > | | > > > | > | > | > > > | 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 409 410 411 412 413 414 415 416 417 418 419 420 421 422 423 424 425 426 427 428 429 430 431 432 433 434 435 436 437 438 439 440 441 | } { <p>No. When you delete information from an SQLite database, the unused disk space is added to an internal "free-list" and is reused the next time you insert data. The disk space is not lost. But neither is it returned to the operating system.</p> <p>If you delete a lot of data and want to shrink the database file, run the <a href="lang_vacuum.html">VACUUM</a> command. VACUUM will reconstruct the database from scratch. This will leave the database with an empty free-list and a file that is minimal in size. Note, however, that the VACUUM can take some time to run (around a half second per megabyte on the Linux box where SQLite is developed) and it can use up to twice as much temporary disk space as the original file while it is running. </p> <p>As of SQLite version 3.1, an alternative to using the VACUUM command is auto-vacuum mode, enabled using the <a href="pragma.html#pragma_auto_vacuum">auto_vacuum pragma</a>.</p> } faq { Can I use SQLite in my commercial product without paying royalties? } { <p>Yes. SQLite is in the <a href="copyright.html">public domain</a>. No claim of ownership is made to any part of the code. You can do anything you want with it.</p> } faq { How do I use a string literal that contains an embedded single-quote (') character? } { <p>The SQL standard specifies that single-quotes in strings are escaped by putting two single quotes in a row. SQL works like the Pascal programming language in the regard. SQLite follows this standard. Example: </p> <blockquote><pre> INSERT INTO xyz VALUES('5 O''clock'); </pre></blockquote> } faq {What is an SQLITE_SCHEMA error, and why am I getting one?} { <p>An SQLITE_SCHEMA error is returned when a prepared SQL statement is no longer valid and cannot be executed. When this occurs, the statement must be recompiled from SQL using the <a href="capi3ref.html#sqlite3_prepare">sqlite3_prepare()</a> API. In SQLite version 3, an SQLITE_SCHEMA error can only occur when using the <a href="capi3ref.html#sqlite3_prepare">sqlite3_prepare()</a>/<a href="capi3ref.html#sqlite3_step">sqlite3_step()</a>/<a href="capi3ref.html#sqlite3_finalize">sqlite3_finalize()</a> API to execute SQL, not when using the <a href="capi3ref.html#sqlite3_exec">sqlite3_exec()</a>. This was not the case in version 2.</p> <p>The most common reason for a prepared statement to become invalid is that the schema of the database was modified after the SQL was prepared (possibly by another process). The other reasons this can happen are:</p> <ul> <li>A database was <a href="lang_detach.html">DETACH</a>ed. <li>The database was <a href="lang_vacuum.html">VACUUM</a>ed <li>A user-function definition was deleted or changed. <li>A collation sequence definition was deleted or changed. <li>The authorization function was changed. </ul> <p>In all cases, the solution is to recompile the statement from SQL and attempt to execute it again. Because a prepared statement can be invalidated by another process changing the database schema, all code that uses the <a href="capi3ref.html#sqlite3_prepare">sqlite3_prepare()</a>/<a href="capi3ref.html#sqlite3_step">sqlite3_step()</a>/<a href="capi3ref.html#sqlite3_finalize">sqlite3_finalize()</a> API should be prepared to handle SQLITE_SCHEMA errors. An example of one approach to this follows:</p> <blockquote><pre> int rc; sqlite3_stmt *pStmt; |
︙ | ︙ | |||
492 493 494 495 496 497 498 | SQLite uses) is 9.949999999999999289457264239899814128875732421875. So when you type "9.95", SQLite really understands the number to be the much longer value shown above. And that value rounds down.</p> <p>This kind of problem comes up all the time when dealing with floating point binary numbers. The general rule to remember is that most fractional numbers that have a finite representation in decimal | > | > | 468 469 470 471 472 473 474 475 476 477 478 479 480 481 482 483 484 | SQLite uses) is 9.949999999999999289457264239899814128875732421875. So when you type "9.95", SQLite really understands the number to be the much longer value shown above. And that value rounds down.</p> <p>This kind of problem comes up all the time when dealing with floating point binary numbers. The general rule to remember is that most fractional numbers that have a finite representation in decimal (a.k.a "base-10") do not have a finite representation in binary (a.k.a "base-2"). And so they are approximated using the closest binary number available. That approximation is usually very close, but it will be slightly off and in some cases can cause your results to be a little different from what you might expect.</p> } # End of questions and answers. |
︙ | ︙ |