## Run this script to generated a faq.html output file#set rcsid {$Id: faq.tcl,v 1.36 2006/04/05 01:02:08 drh Exp $}source common.tclheader {SQLite Frequently Asked Questions</title>}set cnt 1proc 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  your primary key to TEXT and it should work.</p>  <p>Every row must have a unique primary key.  For a column with a  numeric type, SQLite thinks that <b>'0'</b> and <b>'0.0'</b> are the  same value because they compare equal to one another numerically.  (See the previous question.)  Hence the values are not unique.</p>}        faq {  My linux box is not able to read an SQLite database that was created  on my SparcStation.} {  <p>You need to upgrade your SQLite library to version 2.6.3 or later.</p>  <p>The x86 processor on your linux box is little-endian (meaning that  the least significant byte of integers comes first) but the Sparc is  big-endian (the most significant bytes comes first).  SQLite databases  created on a little-endian architecture cannot be on a big-endian  machine by version 2.6.2 or earlier of SQLite.  Beginning with  version 2.6.3, SQLite should be able to read and write database files  regardless of byte order of the machine on which the file was created.</p>}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 any moment 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>Prior to version 3.3.1,  an <b>sqlite3</b> structure could only be used in the same thread  that called <a href="capi3ref#sqlite3_open">sqlite3_open</a> to create it.  You could not open a  database in one thread then pass the handle off to another thread for  it to use.  This was due to limitations (bugs?) in many common threading  implementations such as on RedHat9.  Specifically, an fcntl() lock  created by one thread cannot be removed or modified by a different  thread on the troublesome systems.  And since SQLite uses fcntl()  locks heavily for concurrency control, serious problems arose if you   start moving database connections across threads.</p>  <p>The restriction on moving database connections across threads  was relaxed somewhat in version 3.3.1.  With that and subsequent  versions, it is safe to move a connection handle across threads  as long as the connection is not holding any fcntl() locks.  You  can safely assume that no locks are being held if no  transaction is pending and all statements have been finalized.</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 {  How do I list all tables/indices contained in an SQLite database} {  <p>If you are running the <b>sqlite3</b> command-line access program  you can type "<b>.tables</b>" to get a list of all tables.  Or you  can type "<b>.schema</b>" to see the complete database schema including  all tables and indices.  Either of these commands can be followed by  a LIKE pattern that will restrict the tables that are displayed.</p>  <p>From within a C/C++ program (or a script using Tcl/Ruby/Perl/Python  bindings) you can get access to table and index names by doing a SELECT  on a special table named "<b>SQLITE_MASTER</b>".  Every SQLite database  has an SQLITE_MASTER table that defines the schema for the database.  The SQLITE_MASTER table looks like this:</p><blockquote><pre>CREATE TABLE sqlite_master (  type TEXT,  name TEXT,  tbl_name TEXT,  rootpage INTEGER,  sql TEXT);</pre></blockquote>  <p>For tables, the <b>type</b> field will always be <b>'table'</b> and the  <b>name</b> field will be the name of the table.  So to get a list of  all tables in the database, use the following SELECT command:</p><blockquote><pre>SELECT name FROM sqlite_masterWHERE type='table'ORDER BY name;</pre></blockquote>  <p>For indices, <b>type</b> is equal to <b>'index'</b>, <b>name</b> is the  name of the index and <b>tbl_name</b> is the name of the table to which  the index belongs.  For both tables and indices, the <b>sql</b> field is  the text of the original CREATE TABLE or CREATE INDEX statement that  created the table or index.  For automatically created indices (used