2 # Run this script to generated a faq.html output file
4 set rcsid
{$Id: faq.tcl
,v
1.28 2005/01/26 10:39:58 danielk1977 Exp
$}
6 header
{SQLite Frequently Asked Questions
</title
>}
9 proc faq
{question answer
} {
10 set ::faq($::cnt) [list [string trim
$question] [string trim
$answer]]
15 # Enter questions and answers here.
18 How do I create an AUTOINCREMENT field.
20 <p
>Short answer
: A column declared INTEGER PRIMARY KEY will
23 <p
>Here is the long answer
:
24 Beginning with version SQLite
2.3.4, If you declare a column of
25 a table to be INTEGER PRIMARY KEY
, then whenever you insert a NULL
26 into that column of the table
, the NULL is automatically converted
27 into an integer which is one greater than the largest value of that
28 column over all other rows in the table
, or
1 if the table is empty.
29 For example
, suppose you have a table like this
:
32 a INTEGER PRIMARY KEY
,
36 <p
>With this table
, the statement
</p
>
38 INSERT INTO t1 VALUES
(NULL
,123);
40 <p
>is logically equivalent to saying
:</p
>
42 INSERT INTO t1 VALUES
((SELECT max
(a
) FROM t1
)+1,123);
44 <p
>For SQLite version
2.2.0 through
2.3.3, if you insert a NULL into
45 an INTEGER PRIMARY KEY column
, the NULL will be changed to a unique
46 integer
, but it will a semi-random integer. Unique keys generated this
47 way will not be sequential. For SQLite version
2.3.4 and beyond
, the
48 unique keys will be sequential until the largest key reaches a value
49 of
2147483647. That is the largest
32-bit signed integer and cannot
50 be incremented
, so subsequent insert attempts will revert to the
51 semi-random key generation algorithm of SQLite version
2.3.3 and
54 <p
>Beginning with version
2.2.3, there is a new API function named
55 <b
>sqlite_last_insert_rowid
()</b
> which will
return the integer key
56 for the most recent insert operation. See the API documentation
for
59 <p
>SQLite version
3.0 expands the size of the rowid to
64 bits.
</p
>
63 What datatypes does SQLite support?
66 the datatype information that follows the column name in CREATE TABLE.
67 You can put any type of data you want
68 into any column
, without regard to the declared datatype of that column.
71 <p
>An exception to this rule is a column of type INTEGER PRIMARY KEY.
72 Such columns must hold an integer. An attempt to put a non-integer
73 value into an INTEGER PRIMARY KEY column will generate an
error.
</p
>
75 <p
>There is a page on
<a href
="datatypes.html">datatypes in SQLite
77 and another
for <a href
="datatype3.html">version
3.0</a
>
78 that explains this concept further.
</p
>
82 SQLite lets me insert a
string into a database column of type integer
!
84 <p
>This is a feature
, not a bug. SQLite does not enforce data type
85 constraints. Any data can be
86 inserted into any column. You can put arbitrary length strings into
87 integer columns
, floating point numbers in boolean columns
, or dates
88 in character columns. The datatype you assign to a column in the
89 CREATE TABLE command does not restrict what data can be put into
90 that column. Every column is able to hold
91 an arbitrary length
string.
(There is one exception
: Columns of
92 type INTEGER PRIMARY KEY may only hold an integer. An
error will result
93 if you try to put anything other than an integer into an
94 INTEGER PRIMARY KEY column.
)</p
>
96 <p
>The datatype does effect how values are compared
, however. For
97 columns with a numeric type
(such as
"integer") any
string that looks
98 like a number is treated as a number
for comparison and sorting purposes.
99 Consider these two command sequences
:</p
>
102 CREATE TABLE t1
(a INTEGER UNIQUE
); CREATE TABLE t2
(b TEXT UNIQUE
);
103 INSERT INTO t1 VALUES
('
0'
); INSERT INTO t2 VALUES
(0);
104 INSERT INTO t1 VALUES
('
0.0'
); INSERT INTO t2 VALUES
(0.0);
107 <p
>In the sequence on the left
, the second insert will fail. In this case
,
108 the strings '
0' and '
0.0' are treated as numbers since they are being
109 inserted into a numeric column and
0==0.0 which violates the uniqueness
110 constraint. But the second insert in the right-hand sequence works. In
111 this case
, the constants
0 and
0.0 are treated a strings which means that
112 they are distinct.
</p
>
114 <p
>There is a page on
<a href
="datatypes.html">datatypes in SQLite
116 and another
for <a href
="datatype3.html">version
3.0</a
>
117 that explains this concept further.
</p
>
121 Why does SQLite think that the expression '
0'
=='
00' is TRUE?
123 <p
>As of version
2.7.0, it doesn't.
</p
>
125 <p
>But
if one of the two values being compared is stored in a column that
126 has a numeric type
, the the other value is treated as a number
, not a
127 string and the result succeeds. For example
:</p
>
130 CREATE TABLE t3
(a INTEGER
, b TEXT
);
131 INSERT INTO t3 VALUES
(0,0);
132 SELECT count
(*) FROM t3 WHERE a
=='
00'
;
135 <p
>The SELECT in the above series of commands returns
1. The
"a" column
136 is numeric so in the WHERE clause the
string '
00' is converted into a
137 number
for comparison against
"a".
0==00 so the test is true. Now
138 consider a different SELECT
:</p
>
141 SELECT count
(*) FROM t3 WHERE b
=='
00'
;
144 <p
>In this case the answer is
0. B is a
text column so a
text comparison
145 is done against '
00'. '
0'
!='
00' so the WHERE clause returns FALSE and
146 the count is zero.
</p
>
148 <p
>There is a page on
<a href
="datatypes.html">datatypes in SQLite
150 and another
for <a href
="datatype3.html">version
3.0</a
>
151 that explains this concept further.
</p
>
155 Why doesn't SQLite allow me to use '
0' and '
0.0' as the primary
156 key on two different rows of the same table?
158 <p
>Your primary key must have a numeric type. Change the datatype of
159 your primary key to TEXT and it should work.
</p
>
161 <p
>Every row must have a unique primary key. For a column with a
162 numeric type
, SQLite thinks that
<b
>'
0'
</b
> and
<b
>'
0.0'
</b
> are the
163 same value because they compare equal to one another numerically.
164 (See the previous question.
) Hence the values are not unique.
</p
>
168 My linux box is not able to
read an SQLite database that was created
171 <p
>You need to upgrade your SQLite library to version
2.6.3 or later.
</p
>
173 <p
>The x86 processor on your linux box is little-endian
(meaning that
174 the least significant byte of integers comes first
) but the Sparc is
175 big-endian
(the most significant bytes comes first
). SQLite databases
176 created on a little-endian architecture cannot be on a big-endian
177 machine by version
2.6.2 or earlier of SQLite. Beginning with
178 version
2.6.3, SQLite should be able to
read and write database files
179 regardless of byte order of the machine on which the
file was created.
</p
>
183 Can multiple applications or multiple instances of the same
184 application access a single database
file at the same
time?
186 <p
>Multiple processes can have the same database
open at the same
187 time. Multiple processes can be doing a SELECT
188 at the same
time. But only one process can be making changes to
189 the database at once.
</p
>
191 <p
>Win95
/98/ME lacks support
for reader
/writer locks in the operating
192 system. Prior to version
2.7.0, this meant that under windows you
193 could only have a single process reading the database at one
time.
194 This problem was resolved in version
2.7.0 by implementing a user-space
195 probabilistic reader
/writer locking strategy in the windows interface
197 now works like Unix in allowing multiple simultaneous readers.
</p
>
199 <p
>The locking mechanism used to control simultaneous access might
200 not work correctly
if the database
file is kept on an NFS filesystem.
201 This is because
file locking is broken on some NFS implementations.
202 You should avoid putting SQLite database files on NFS
if multiple
203 processes might try to access the
file at the same
time. On Windows
,
204 Microsoft's documentation says that locking may not work under FAT
205 filesystems
if you are not running the Share.exe daemon. People who
206 have a lot of experience with Windows
tell me that
file locking of
207 network files is very buggy and is not dependable. If what they
208 say is true
, sharing an SQLite database between two or more Windows
209 machines might cause unexpected problems.
</p
>
211 <p
>Locking in SQLite is very course-grained. SQLite locks the
212 entire database. Big database servers
(PostgreSQL
, Oracle
, etc.
)
213 generally have finer grained locking
, such as locking on a single
214 table or a single row within a table. If you have a massively
215 parallel database application
, you should consider using a big database
216 server instead of SQLite.
</p
>
218 <p
>When SQLite tries to access a
file that is locked by another
219 process
, the
default behavior is to
return SQLITE_BUSY. You can
220 adjust this behavior from C code using the
<b
>sqlite_busy_handler
()</b
> or
221 <b
>sqlite_busy_timeout
()</b
> API functions. See the API documentation
224 <p
>If two or more processes have the same database
open and one
225 process creates a new table or index
, the other processes might
226 not be able to see the new table right away. You might have to
227 get the other processes to
close and reopen their connection to
228 the database before they will be able to see the new table.
</p
>
232 Is SQLite threadsafe?
234 <p
>Yes. Sometimes. In order to be thread-safe
, SQLite must be compiled
235 with the THREADSAFE preprocessor macro
set to
1. In the
default
236 distribution
, the windows binaries are compiled to be threadsafe but
237 the linux binaries are not. If you want to change this
, you'll have to
240 <p
>"Threadsafe" in the previous paragraph means that two or more threads
241 can run SQLite at the same
time on different
"<b>sqlite</b>" structures
242 returned from separate calls to
<b
>sqlite_open
()</b
>. It is never safe
243 to use the same
<b
>sqlite
</b
> structure pointer simultaneously in two
246 <p
>Note that
if two or more threads have the same database
open and one
247 thread creates a new table or index
, the other threads might
248 not be able to see the new table right away. You might have to
249 get the other threads to
close and reopen their connection to
250 the database before they will be able to see the new table.
</p
>
252 <p
>Under UNIX
, you should not carry an
open SQLite database across
253 a fork
() system call into the child process. Problems will result
258 How do I
list all tables
/indices contained in an SQLite database
260 <p
>If you are running the
<b
>sqlite
</b
> command-line access program
261 you can type
"<b>.tables</b>" to get a
list of all tables. Or you
262 can type
"<b>.schema</b>" to see the complete database schema including
263 all tables and indices. Either of these commands can be followed by
264 a LIKE pattern that will restrict the tables that are displayed.
</p
>
266 <p
>From within a C
/C
++ program
(or a script using
Tcl/Ruby
/Perl
/Python
267 bindings
) you can get access to table and index names by doing a SELECT
268 on a special table named
"<b>SQLITE_MASTER</b>". Every SQLite database
269 has an SQLITE_MASTER table that defines the schema
for the database.
270 The SQLITE_MASTER table looks like this
:</p
>
272 CREATE TABLE sqlite_master
(
280 <p
>For tables
, the
<b
>type
</b
> field will always be
<b
>'table'
</b
> and the
281 <b
>name
</b
> field will be the name of the table. So to get a
list of
282 all tables in the database
, use the following SELECT command
:</p
>
284 SELECT name FROM sqlite_master
288 <p
>For indices
, <b
>type
</b
> is equal to
<b
>'index'
</b
>, <b
>name
</b
> is the
289 name of the index and
<b
>tbl_name
</b
> is the name of the table to which
290 the index belongs. For both tables and indices
, the
<b
>sql
</b
> field is
291 the
text of the original CREATE TABLE or CREATE INDEX statement that
292 created the table or index. For automatically created indices
(used
293 to implement the PRIMARY KEY or UNIQUE constraints
) the
<b
>sql
</b
> field
296 <p
>The SQLITE_MASTER table is read-only. You cannot change this table
297 using UPDATE
, INSERT
, or DELETE. The table is automatically updated by
298 CREATE TABLE
, CREATE INDEX
, DROP TABLE
, and DROP INDEX commands.
</p
>
300 <p
>Temporary tables do not appear in the SQLITE_MASTER table. Temporary
301 tables and their indices and triggers occur in another special table
302 named SQLITE_TEMP_MASTER. SQLITE_TEMP_MASTER works just like SQLITE_MASTER
303 except that it is only visible to the application that created the
304 temporary tables. To get a
list of all tables
, both permanent and
305 temporary
, one can use a command similar to the following
:
308 (SELECT
* FROM sqlite_master UNION ALL
309 SELECT
* FROM sqlite_temp_master
)
316 Are there any known size limits to SQLite databases?
318 <p
>As of version
2.7.4,
319 SQLite can handle databases up to
2<sup
>41</sup
> bytes
(2 terabytes
)
320 in size on both Windows and Unix. Older version of SQLite
321 were limited to databases of
2<sup
>31</sup
> bytes
(2 gigabytes
).
</p
>
323 <p
>SQLite version
2.8 limits the amount of data in one row to
324 1 megabyte. SQLite version
3.0 has no limit on the amount of
325 data that can be stored in a single row.
328 <p
>The names of tables
, indices
, view
, triggers
, and columns can be
329 as long as desired. However
, the names of SQL functions
(as created
330 by the
<a href
="c_interface.html#cfunc">sqlite_create_function
()</a
> API
)
331 may not exceed
255 characters in length.
</p
>
335 What is the maximum size of a VARCHAR in SQLite?
337 <p
>SQLite does not enforce datatype constraints.
338 A VARCHAR column can hold as much data as you care to put in it.
</p
>
342 Does SQLite support a BLOB type?
344 <p
>SQLite version
3.0 lets you
puts BLOB data into any column
, even
345 columns that are declared to hold some other type.
</p
>
347 <p
>SQLite version
2.8 will store any
text data without embedded
348 '
\000' characters. If you need to store BLOB data in SQLite version
349 2.8 you'll want to encode that data first.
350 There is a
source file named
351 "<b>src/encode.c</b>" in the SQLite version
2.8 distribution that contains
352 implementations of functions named
"<b>sqlite_encode_binary()</b>
353 and <b>sqlite_decode_binary()</b> that can be used for converting
354 binary data to ASCII and back again, if you like.</p>
360 How do I add or delete columns from an existing table in SQLite.
362 <p>SQLite does yes not support the "ALTER TABLE
" SQL command. If you
363 what to change the structure of a table, you have to recreate the
364 table. You can save existing data to a temporary table, drop the
365 old table, create the new table, then copy the data back in from
366 the temporary table.</p>
368 <p>For example, suppose you have a table named "t1
" with columns
369 names "a
", "b
", and "c
" and that you want to delete column "c
" from
370 this table. The following steps illustrate how this could be done:
375 CREATE TEMPORARY TABLE t1_backup(a,b);
376 INSERT INTO t1_backup SELECT a,b FROM t1;
378 CREATE TABLE t1(a,b);
379 INSERT INTO t1 SELECT a,b FROM t1_backup;
380 DROP TABLE t1_backup;
386 I deleted a lot of data but the database file did not get any
387 smaller. Is this a bug?
389 <p>No. When you delete information from an SQLite database, the
390 unused disk space is added to an internal "free-list
" and is reused
391 the next time you insert data. The disk space is not lost. But
392 neither is it returned to the operating system.</p>
394 <p>If you delete a lot of data and want to shrink the database file,
395 run the VACUUM command (version 2.8.1 and later). VACUUM will reconstruct
396 the database from scratch. This will leave the database with an empty
397 free-list and a file that is minimal in size. Note, however, that the
398 VACUUM can take some time to run (around a half second per megabyte
399 on the Linux box where SQLite is developed) and it can use up to twice
400 as much temporary disk space as the original file while it is running.
403 <p>As of SQLite version 3.1, an alternative to using the VACUUM command
404 is auto-vacuum mode, enabled using the
405 <a href="pragma.html
#pragma_auto_vacuum">auto_vacuum pragma</a>.</p>
409 Can I use SQLite in my commercial product without paying royalties?
411 <p
>Yes. SQLite is in the public domain. No claim of ownership is made
412 to any part of the code. You can do anything you want with it.
</p
>
416 How do I use a
string literal that contains an embedded single-quote
('
)
419 <p
>The SQL standard specifies that single-quotes in strings are escaped
420 by putting two single quotes in a row. SQL works like the Pascal programming
421 language in the regard. SQLite follows this standard. Example
:
425 INSERT INTO xyz VALUES
('
5 O''
clock'
);
429 faq
{What is an SQLITE_SCHEMA
error, and why am I getting one?
} {
430 <p
>In version
3 of SQLite
, an SQLITE_SCHEMA
error is returned when a
431 prepared SQL statement is no longer valid and cannot be executed.
432 When this occurs
, the statement must be recompiled from SQL using
433 the sqlite3_prepare
() API. In SQLite
3, an SQLITE_SCHEMA
error can
434 only occur when using the sqlite3_prepare
()/sqlite3_step
()/sqlite3_finalize
()
435 API to execute SQL
, not when using the sqlite3_exec
(). This was not
436 the case in version
2.
</p
>
438 <p
>The most common reason
for a prepared statement to become invalid
439 is that the schema of the database was modified
after the SQL was
440 prepared
(possibly by another process
). The other reasons this can
443 <li
>A database was DETACHed.
444 <li
>A user-function definition was deleted or changed.
445 <li
>A collation sequence definition was deleted or changed.
446 <li
>The authorization function was changed.
449 <p
>In all cases
, the solution is to recompile the statement from SQL
450 and attempt to execute it again. Because a prepared statement can be
451 invalidated by another process changing the database schema
, all code
452 that uses the sqlite3_prepare
()/sqlite3_step
()/sqlite3_finalize
()
453 API should be prepared to handle SQLITE_SCHEMA errors. An example
454 of one approach to this follows
:</p
>
460 char zSql
[] = "SELECT .....";
463 /* Compile the statement from SQL. Assume
success.
*/
464 sqlite3_prepare
(pDb
, zSql
, -1, &pStmt
, 0);
466 while( SQLITE_ROW
==sqlite3_step
(pStmt
) ){
467 /* Do something with the row of available data
*/
470 /* Finalize the statement. If an SQLITE_SCHEMA
error has
471 ** occured
, then the above call to sqlite3_step
() will have
472 ** returned SQLITE_ERROR. sqlite3_finalize
() will
return
473 ** SQLITE_SCHEMA. In this case the loop will execute again.
475 rc
= sqlite3_finalize
(pStmt
);
476 } while( rc
==SQLITE_SCHEMA
);
481 # End of questions and answers.
484 puts {<h2
>Frequently Asked Questions
</h2
>}
486 # puts {<DL COMPACT>}
487 # for {set i 1} {$i<$cnt} {incr i} {
488 # puts " <DT><A HREF=\"#q$i\">($i)</A></DT>"
489 # puts " <DD>[lindex $faq($i) 0]</DD>"
493 for {set i
1} {$i<$cnt} {incr i
} {
494 puts "<li><a href=\"#q$i\">[lindex $faq($i) 0]</a></li>"
498 for {set i
1} {$i<$cnt} {incr i
} {
499 puts "<A NAME=\"q$i\"><HR />"
500 puts "<P><B>($i) [lindex $faq($i) 0]</B></P>\n"
501 puts "<BLOCKQUOTE>[lindex $faq($i) 1]</BLOCKQUOTE></LI>\n"