Documentation Source Text

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 is in use 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>
................................................................................
  REAL, TEXT, BLOB, or as NULL.
}

faq {
  SQLite lets me insert a string into a database column of type integer!
} {
  <p>This is a feature, not a bug.  SQLite uses [dynamic typing]. 
  It 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>
................................................................................
  with the SQLITE_THREADSAFE preprocessor macro set to 1.  Both the Windows
  and Linux precompiled binaries in the distribution are compiled this way.
  If you are unsure if the SQLite library you are linking against is compiled
  to be threadsafe you can call the [sqlite3_threadsafe()]
  interface to find out.
  </p>

  <p>Prior to [version 3.3.1],
  an <b>sqlite3</b> structure could only be used in the same thread
  that called [sqlite3_open()] 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 
  [sqlite3_stmt|statements] have been [sqlite3_finalize|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
................................................................................
  the limits of SQLite.</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 later allow you to store BLOB data in 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
................................................................................
  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?
} {
................................................................................
  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>
}

faq {
  I get hundreds of compiler warnings when I compile SQLite.
  Isn't this a problem?  Doesn't it indicate poor code quality?
} {
  <p>Quality assurance in SQLite is done using 
  [test coverage | full-coverage testing],
  not by compiler warnings or other static code analysis tools.
  In other words, we verify that SQLite actually gets the
  correct answer, not that it merely satisfies stylistic constraints.
................................................................................

  <p>We also run SQLite using [http://valgrind.org | Valgrind]
  on Linux and verify that it detects no problems.</p>

  <p>Some people say that we should eliminate all warnings because
  benign warnings mask real warnings that might arise in future changes.
  This is true enough.  But in reply, the developers observe that all
  warnings have already been fixed in the
  compilers used for SQLite development (various versions of GCC).
  Compiler warnings only arise from compilers that the developers do
  not use on a daily basis (Ex: MSVC).</p>

}

faq {
  Case-insensitive matching of Unicode characters does not work.
} {
  The default configuration of SQLite only supports case-insensitive
  comparisons of ASCII characters.  The reason for this is that doing
................................................................................
  rare bugs (see 
  <a href="http://www.sqlite.org/cvstrac/wiki?p=DatabaseCorruption">DatabaseCorruption</a>) 
  and even then the bugs are normally difficult to
  reproduce.  Even if your application crashes in the middle of an
  update, your database is safe.  The database is safe even if your OS
  crashes or takes a power loss.  The crash-resistance of SQLite has
  been extensively studied and tested and is attested by years of real-world 
  experience by millions of users.</p>

  <p>That said, there are a number of things that external programs or bugs
  in your hardware or OS can do to corrupt a database file.  Details
  can be found in the discussions on the 
  <a href="atomiccommit.html">atomic commit</a> and 
  <a href="lockingv3.html">locking</a> support in SQLite
  as well as in the mailing list archives.</p>

  <p>Your can use <a href="pragma.html#pragma_integrity_check">PRAGMA integrity_check</a> 
  to do a thorough but time intensive test of the database integrity.</p>

  <p>Your can use <a href="pragma.html#pragma_quick_check">PRAGMA quick_check</a> to do a faster 
  but less thorough test of the database integrity.</p>

................................................................................
  the wall, it is generally not possible to put him back together again.</p>
}

faq {
  Does SQLite support foreign keys?
} {
  <p>
    As of version 3.6.19, SQLite supports [foreign key constraints].

  <p>

    Prior versions of SQLite parsed foreign key constraints, but did not

    enforce them. The equivalent functionality could be implemented using
    [CREATE TRIGGER|SQL triggers]. Versions 3.6.12 and later of the SQLite
    shell tool provided the ".genfkey" command to generate such triggers
    automatically. The <a href="http://www.sqlite.org/cvstrac/fileview?f=sqlite/tool/genfkey.README">
    readme</a> for the genfkey utility contains more information.
}

faq {
  I get a compiler error if I use the SQLITE_OMIT_... 
  compile-time options when building SQLite.
} {
  The [omitfeatures | SQLITE_OMIT_...] compile-time options only work
................................................................................
  that the core public-domain SQLite source code is not described by any ECCN,
  hence the ECCN should be reported as <b>EAR99</b>.

  <p>The above is true for the core public-domain SQLite.  If you extend
  SQLite by adding new code, or if you statically link SQLite with your
  application, that might change the ECCN in your particular case.
}











# End of questions and answers.
#############

hd_puts {<h2>Frequently Asked Questions</h2>}

    hd_puts {<oL>}

faq {
  How do I create an AUTOINCREMENT field.
} {
  <p>Short answer: A column declared [INTEGER PRIMARY KEY] will
  autoincrement.</p>

  <p>Longer 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.
  Or, if the largest existing integer key 9223372036854775807 is in use 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>
................................................................................
  REAL, TEXT, BLOB, or as NULL.
}

faq {
  SQLite lets me insert a string into a database column of type integer!
} {
  <p>This is a feature, not a bug.  SQLite uses [dynamic typing]. 
  It does not enforce data type  constraints.  Data of any type can
  (usually) 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>
................................................................................
  with the SQLITE_THREADSAFE preprocessor macro set to 1.  Both the Windows
  and Linux precompiled binaries in the distribution are compiled this way.
  If you are unsure if the SQLite library you are linking against is compiled
  to be threadsafe you can call the [sqlite3_threadsafe()]
  interface to find out.
  </p>

  <p>SQLite is threadsafe because it uses mutexes to serialize
  access to common data structures.  However, the work of acquiring and
  releasing these mutexes will slow SQLite down slightly.  Hence, if you
  do not need SQLite to be threadsafe, you should disable the mutexes
  for maximum performance.  See the [threading mode] documentation for
  additional information.</p>














  <p>Under Unix, you should not carry an open SQLite database across
  a fork() system call into the child process.</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
................................................................................
  the limits of SQLite.</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 store a 500-million character
  string there.  And it will keep all 500-million characters intact.
  Your content is never truncated.  SQLite understands the column type
  of "VARCHAR(<i>N</i>)" to be the same as "TEXT", regardless of the value
  of <i>N</i>.
  </p>
}

faq {
  Does SQLite support a BLOB type?
} {
  <p>SQLite allows you to store BLOB data in any 
  column, even columns that are declared to hold some other type.
  BLOBs can even be used as PRIMARY KEYs.</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
................................................................................
  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 and it can use up to twice

  as much temporary disk space as the original file while it is running.
  </p>

  <p>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?
} {
................................................................................
  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>
}

faq {
  I get some compiler warnings when I compile SQLite.
  Isn't this a problem?  Doesn't it indicate poor code quality?
} {
  <p>Quality assurance in SQLite is done using 
  [test coverage | full-coverage testing],
  not by compiler warnings or other static code analysis tools.
  In other words, we verify that SQLite actually gets the
  correct answer, not that it merely satisfies stylistic constraints.
................................................................................

  <p>We also run SQLite using [http://valgrind.org | Valgrind]
  on Linux and verify that it detects no problems.</p>

  <p>Some people say that we should eliminate all warnings because
  benign warnings mask real warnings that might arise in future changes.
  This is true enough.  But in reply, the developers observe that all
  warnings have already been fixed in the builds
  used for SQLite development (various versions of GCC, MSVC,
  and clang).
  Compiler warnings usually only arise from compilers or compile-time 
  options that the SQLite developers to not use themselves.</p>
}

faq {
  Case-insensitive matching of Unicode characters does not work.
} {
  The default configuration of SQLite only supports case-insensitive
  comparisons of ASCII characters.  The reason for this is that doing
................................................................................
  rare bugs (see 
  <a href="http://www.sqlite.org/cvstrac/wiki?p=DatabaseCorruption">DatabaseCorruption</a>) 
  and even then the bugs are normally difficult to
  reproduce.  Even if your application crashes in the middle of an
  update, your database is safe.  The database is safe even if your OS
  crashes or takes a power loss.  The crash-resistance of SQLite has
  been extensively studied and tested and is attested by years of real-world 
  experience by billions of users.</p>

  <p>That said, there are a number of things that external programs or bugs
  in your hardware or OS can do to corrupt a database file.  See
  <a href="howtocorrupt.html">How To Corrupt An SQLite DAtabase File</a> for
  further information.



  <p>Your can use <a href="pragma.html#pragma_integrity_check">PRAGMA integrity_check</a> 
  to do a thorough but time intensive test of the database integrity.</p>

  <p>Your can use <a href="pragma.html#pragma_quick_check">PRAGMA quick_check</a> to do a faster 
  but less thorough test of the database integrity.</p>

................................................................................
  the wall, it is generally not possible to put him back together again.</p>
}

faq {
  Does SQLite support foreign keys?
} {
  <p>
  As of version 3.6.19, SQLite supports [foreign key constraints].  But enforcement


  of foreign key constraints is turned off by default (for backwards compatibility).
  To enable foreign key constraint enforcements, run 
  [PRAGMA foreign_keys|PRAGMA foreign_keys=ON] or compile with
  [SQLITE_DEFAULT_FOREIGN_KEYS | -DSQLITE_DEFAULT_FOREIGN_KEYS=1].




}

faq {
  I get a compiler error if I use the SQLITE_OMIT_... 
  compile-time options when building SQLite.
} {
  The [omitfeatures | SQLITE_OMIT_...] compile-time options only work
................................................................................
  that the core public-domain SQLite source code is not described by any ECCN,
  hence the ECCN should be reported as <b>EAR99</b>.

  <p>The above is true for the core public-domain SQLite.  If you extend
  SQLite by adding new code, or if you statically link SQLite with your
  application, that might change the ECCN in your particular case.
}

faq {
  My query does not return the column name that I expect.  Is this a bug?
} {
  If the columns of your result set are named by AS clauses, then SQLite
  is guaranteed to use the identifer to the right of the AS keyword as the
  column name.  If the result set does not use an AS clause, then SQLite
  is free to name the column anything it wants.
  See the [sqlite3_column_name()] documentation for further information.
}

# End of questions and answers.
#############

hd_puts {<h2>Frequently Asked Questions</h2>}

    hd_puts {<oL>}