faq.html

SQLite is a software library that implements a self-contained, serverless, zero-configuration, trans

  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 
<a href="releaselog/3_3_1.html">version 3.3.1</a>.  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 
  
<a href="c3ref/stmt.html">statements</a> have been 
<a href="c3ref/finalize.html">finalized</a>.</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></blockquote></li>

<a name="q7"></a>
<p><b>(7) How do I list all tables/indices contained in an SQLite database</b></p>

<blockquote><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_master
WHERE 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
  to implement the PRIMARY KEY or UNIQUE constraints) the <b>sql</b> field
  is NULL.</p>

  <p>The SQLITE_MASTER table is read-only.  You cannot change this table
  using UPDATE, INSERT, or DELETE.  The table is automatically updated by
  CREATE TABLE, CREATE INDEX, DROP TABLE, and DROP INDEX commands.</p>

  <p>Temporary tables do not appear in the SQLITE_MASTER table.  Temporary
  tables and their indices and triggers occur in another special table
  named SQLITE_TEMP_MASTER.  SQLITE_TEMP_MASTER works just like SQLITE_MASTER
  except that it is only visible to the application that created the 
  temporary tables.  To get a list of all tables, both permanent and
  temporary, one can use a command similar to the following:
<blockquote><pre>
SELECT name FROM 
   (SELECT * FROM sqlite_master UNION ALL
    SELECT * FROM sqlite_temp_master)
WHERE type='table'
ORDER BY name
</pre></blockquote></blockquote></li>

<a name="q8"></a>
<p><b>(8) Are there any known size limits to SQLite databases?</b></p>

<blockquote><p>See <a href="limits.html">limits.html</a> for a full discussion of
  the limits of SQLite.</p></blockquote></li>

<a name="q9"></a>
<p><b>(9) What is the maximum size of a VARCHAR in SQLite?</b></p>

<blockquote><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></blockquote></li>

<a name="q10"></a>
<p><b>(10) Does SQLite support a BLOB type?</b></p>

<blockquote><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></blockquote></li>

<a name="q11"></a>
<p><b>(11) How do I add or delete columns from an existing table in SQLite.</b></p>

<blockquote><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:
  </p>

  <blockquote><pre>
BEGIN TRANSACTION;
CREATE TEMPORARY TABLE t1_backup(a,b);
INSERT INTO t1_backup SELECT a,b FROM t1;
DROP TABLE t1;
CREATE TABLE t1(a,b);
INSERT INTO t1 SELECT a,b FROM t1_backup;
DROP TABLE t1_backup;
COMMIT;
</pre></blockquote></blockquote></li>

<a name="q12"></a>
<p><b>(12) I deleted a lot of data but the database file did not get any
  smaller.  Is this a bug?</b></p>

<blockquote><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></blockquote></li>

<a name="q13"></a>
<p><b>(13) Can I use SQLite in my commercial product without paying royalties?</b></p>

<blockquote><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></blockquote></li>

<a name="q14"></a>
<p><b>(14) How do I use a string literal that contains an embedded single-quote (')
  character?</b></p>

<blockquote><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></blockquote></li>

<a name="q15"></a>
<p><b>(15) What is an SQLITE_SCHEMA error, and why am I getting one?</b></p>

<blockquote><p>An 
<a href="c3ref/c_abort.html">SQLITE_SCHEMA</a> 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="c3ref/prepare.html">sqlite3_prepare()</a> API.
  An SQLITE_SCHEMA error can only occur when using the 
<a href="c3ref/prepare.html">sqlite3_prepare()</a>,
  and 
<a href="c3ref/step.html">sqlite3_step()</a> interfaces to run SQL.
  You will never receive an 
<a href="c3ref/c_abort.html">SQLITE_SCHEMA</a> error from
  
<a href="c3ref/exec.html">sqlite3_exec()</a>.  Nor will you receive a the error if you
  prepare statements using 
<a href="c3ref/prepare.html">sqlite3_prepare_v2()</a> instead of
  
<a href="c3ref/prepare.html">sqlite3_prepare()</a>.</p>

  <p>The 
<a href="c3ref/prepare.html">sqlite3_prepare_v2()</a> interface creates a
  
<a href="c3ref/stmt.html">prepared statement</a> that will automatically recompile itself if
  the schema changes.  The easiest way to deal with
  
<a href="c3ref/c_abort.html">SQLITE_SCHEMA</a> errors is to always use 
<a href="c3ref/prepare.html">sqlite3_prepare_v2()</a>
  instead of 
<a href="c3ref/prepare.html">sqlite3_prepare()</a>.</blockquote></li>

<a name="q16"></a>
<p><b>(16) Why does ROUND(9.95,1)  return 9.9 instead of 10.0?
  Shouldn't 9.95 round up?</b></p>

<blockquote><p>SQLite uses binary arithmetic and in binary, there is no
  way to write 9.95 in a finite number of bits.  The closest to
  you can get to 9.95 in a 64-bit IEEE float (which is what
  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></blockquote></li>

<a name="q17"></a>
<p><b>(17) I get hundreds of compiler warnings when I compile SQLite.
  Isn't this a problem?  Doesn't it indicate poor code quality?</b></p>

<blockquote><p>Quality assurance in SQLite is done using 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.
  Over two-thirds of the SQLite code base is devoted purely to testing.
  The SQLite test suite runs many thousands of separate test cases and
  many of those test cases are parameterized so that hundreds of thousands
  of tests involving millions of SQL statements are run and evaluated
  for correctness prior to every release.  The developers use code
  coverage tools to verify that all paths through the code are tested.
  Whenever a bug is found in SQLite, new test cases are written to
  exhibit the bug so that the bug cannot recur undetected in the future.</p>

  <p>During testing, the SQLite library is compiled with special
  instrumentation that allows the test scripts to simulate a wide
  variety of failures in order to verify that SQLite recovers
  correctly.  Memory allocation is carefully tracked and no memory
  leaks occur, even following memory allocation failures.  A custom
  VFS layer is used to simulate operating system crashes and power
  failures in order to insure that transactions are atomic across
  these events.  A mechanism for deliberately injecting I/O errors
  shows that SQLite is resilient to such malfunctions.  (As an
  experiment, try inducing these kinds of errors on other SQL database
  engines and see what happens!)</p>

  <p>We also run SQLite using <a href="http://valgrind.org">valgrind</a>
  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 have access to.</p></blockquote></li>

</ol>


<hr><small><i>
This page last modified 2008/03/17 15:45:11 UTC
</i></small></div></body></html>