Adding upstream version 3.40.1.upstream/3.40.1 upstream

Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
author: Daniel Baumann <daniel.baumann@progress-linux.org> 2024-05-05 17:28:19 +0000
committer: Daniel Baumann <daniel.baumann@progress-linux.org> 2024-05-05 17:28:19 +0000
commit: 18657a960e125336f704ea058e25c27bd3900dcb (patch)
tree: 17b438b680ed45a996d7b59951e6aa34023783f2 /www/faq.html
parent: Initial commit. (diff)
download: sqlite3-18657a960e125336f704ea058e25c27bd3900dcb.tar.xz
sqlite3-18657a960e125336f704ea058e25c27bd3900dcb.zip
1 files changed, 648 insertions, 0 deletions
diff --git a/www/faq.html b/www/faq.html
new file mode 100644
index 0000000..05936e3
--- /dev/null
+++ b/www/faq.html
@@ -0,0 +1,648 @@
+<!DOCTYPE html>
+<html><head>
+<meta name="viewport" content="width=device-width, initial-scale=1.0">
+<meta http-equiv="content-type" content="text/html; charset=UTF-8">
+<link href="sqlite.css" rel="stylesheet">
+<title>SQLite Frequently Asked Questions</title>
+<!-- path= -->
+</head>
+<body>
+<div class=nosearch>
+<a href="index.html">
+<img class="logo" src="images/sqlite370_banner.gif" alt="SQLite" border="0">
+</a>
+<div><!-- IE hack to prevent disappearing logo --></div>
+<div class="tagline desktoponly">
+Small. Fast. Reliable.<br>Choose any three.
+</div>
+<div class="menu mainmenu">
+<ul>
+<li><a href="index.html">Home</a>
+<li class='mobileonly'><a href="javascript:void(0)" onclick='toggle_div("submenu")'>Menu</a>
+<li class='wideonly'><a href='about.html'>About</a>
+<li class='desktoponly'><a href="docs.html">Documentation</a>
+<li class='desktoponly'><a href="download.html">Download</a>
+<li class='wideonly'><a href='copyright.html'>License</a>
+<li class='desktoponly'><a href="support.html">Support</a>
+<li class='desktoponly'><a href="prosupport.html">Purchase</a>
+<li class='search' id='search_menubutton'>
+<a href="javascript:void(0)" onclick='toggle_search()'>Search</a>
+</ul>
+</div>
+<div class="menu submenu" id="submenu">
+<ul>
+<li><a href='about.html'>About</a>
+<li><a href='docs.html'>Documentation</a>
+<li><a href='download.html'>Download</a>
+<li><a href='support.html'>Support</a>
+<li><a href='prosupport.html'>Purchase</a>
+</ul>
+</div>
+<div class="searchmenu" id="searchmenu">
+<form method="GET" action="search">
+<select name="s" id="searchtype">
+<option value="d">Search Documentation</option>
+<option value="c">Search Changelog</option>
+</select>
+<input type="text" name="q" id="searchbox" value="">
+<input type="submit" value="Go">
+</form>
+</div>
+</div>
+<script>
+function toggle_div(nm) {
+var w = document.getElementById(nm);
+if( w.style.display=="block" ){
+w.style.display = "none";
+}else{
+w.style.display = "block";
+}
+}
+function toggle_search() {
+var w = document.getElementById("searchmenu");
+if( w.style.display=="block" ){
+w.style.display = "none";
+} else {
+w.style.display = "block";
+setTimeout(function(){
+document.getElementById("searchbox").focus()
+}, 30);
+}
+}
+function div_off(nm){document.getElementById(nm).style.display="none";}
+window.onbeforeunload = function(e){div_off("submenu");}
+/* Disable the Search feature if we are not operating from CGI, since */
+/* Search is accomplished using CGI and will not work without it. */
+if( !location.origin || !location.origin.match || !location.origin.match(/http/) ){
+document.getElementById("search_menubutton").style.display = "none";
+}
+/* Used by the Hide/Show button beside syntax diagrams, to toggle the */
+function hideorshow(btn,obj){
+var x = document.getElementById(obj);
+var b = document.getElementById(btn);
+if( x.style.display!='none' ){
+x.style.display = 'none';
+b.innerHTML='show';
+}else{
+x.style.display = '';
+b.innerHTML='hide';
+}
+return false;
+}
+var antiRobot = 0;
+function antiRobotGo(){
+if( antiRobot!=3 ) return;
+antiRobot = 7;
+var j = document.getElementById("mtimelink");
+if(j && j.hasAttribute("data-href")) j.href=j.getAttribute("data-href");
+}
+function antiRobotDefense(){
+document.body.onmousedown=function(){
+antiRobot |= 2;
+antiRobotGo();
+document.body.onmousedown=null;
+}
+document.body.onmousemove=function(){
+antiRobot |= 2;
+antiRobotGo();
+document.body.onmousemove=null;
+}
+setTimeout(function(){
+antiRobot |= 1;
+antiRobotGo();
+}, 100)
+antiRobotGo();
+}
+antiRobotDefense();
+</script>
+
+
+<h2>Frequently Asked Questions</h2><ol class=nounderline><li value='1'><a href="#q1">How do I create an AUTOINCREMENT field?</a></li><li value='2'><a href="#q2">What datatypes does SQLite support?</a></li><li value='3'><a href="#q3">SQLite lets me insert a string into a database column of type integer!</a></li><li value='4'><a href="#q4">Why doesn't SQLite allow me to use '0' and '0.0' as the primary
+  key on two different rows of the same table?</a></li><li value='5'><a href="#q5">Can multiple applications or multiple instances of the same
+  application access a single database file at the same time?</a></li><li value='6'><a href="#q6">Is SQLite threadsafe?</a></li><li value='7'><a href="#q7">How do I list all tables/indices contained in an SQLite database</a></li><li value='8'><a href="#q8">Are there any known size limits to SQLite databases?</a></li><li value='9'><a href="#q9">What is the maximum size of a VARCHAR in SQLite?</a></li><li value='10'><a href="#q10">Does SQLite support a BLOB type?</a></li><li value='11'><a href="#q11">How do I add, delete or rename columns from an existing table in SQLite?</a></li><li value='12'><a href="#q12">I deleted a lot of data but the database file did not get any
+  smaller.  Is this a bug?</a></li><li value='13'><a href="#q13">Can I use SQLite in my commercial product without paying royalties?</a></li><li value='14'><a href="#q14">How do I use a string literal that contains an embedded single-quote (')
+  character?</a></li><li value='15'><a href="#q15">What is an SQLITE_SCHEMA error, and why am I getting one?</a></li><li value='17'><a href="#q17">I get some compiler warnings when I compile SQLite.
+  Isn't this a problem?  Doesn't it indicate poor code quality?</a></li><li value='18'><a href="#q18">Case-insensitive matching of Unicode characters does not work.</a></li><li value='19'><a href="#q19">INSERT is really slow - I can only do few dozen INSERTs per second</a></li><li value='20'><a href="#q20">I accidentally deleted some important information from my SQLite database.
+  How can I recover it?</a></li><li value='21'><a href="#q21">What is an SQLITE_CORRUPT error?  What does it mean for the database
+  to be "malformed"? Why am I getting this error?</a></li><li value='22'><a href="#q22">Does SQLite support foreign keys?</a></li><li value='23'><a href="#q23">I get a compiler error if I use the SQLITE_OMIT_... 
+  compile-time options when building SQLite.</a></li><li value='24'><a href="#q24">My WHERE clause expression <tt>column1="column1"</tt> does not work.
+  It causes every row of the table to be returned, not just the rows
+  where column1 has the value "column1".</a></li><li value='25'><a href="#q25">How are the syntax diagrams (a.k.a. "railroad" diagrams) for
+  SQLite generated?</a></li><li value='26'><a href="#q26">The SQL standard requires that a UNIQUE constraint be enforced even if
+  one or more of the columns in the constraint are NULL, but SQLite does
+  not do this.  Isn't that a bug?</a></li><li value='27'><a href="#q27">What is the Export Control Classification Number (ECCN) for SQLite?</a></li><li value='28'><a href="#q28">My query does not return the column name that I expect.  Is this a bug?</a></li></ol><a name="q1"></a>
+<p><b>(1) How do I create an AUTOINCREMENT field?</b></p>
+<blockquote><p>Short answer: A column declared <a href="lang_createtable.html#rowid">INTEGER PRIMARY KEY</a> will
+  autoincrement.</p>
+
+  <p>Longer answer:
+  If you declare a column of a table to be <a href="lang_createtable.html#rowid">INTEGER PRIMARY KEY</a>, 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>
+  <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 function named
+  <a href="c3ref/last_insert_rowid.html">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 <a href="autoinc.html">AUTOINCREMENT</a> keyword to the <a href="lang_createtable.html#rowid">INTEGER PRIMARY KEY</a>
+  declaration.  Then the key chosen will be one more than the
+  largest key that has ever existed in that table.  If the largest
+  possible key has previously existed in that table, then the <a href="lang_insert.html">INSERT</a>
+  will fail with an <a href="rescode.html#full">SQLITE_FULL</a> error code.</p></blockquote></li>
+<a name="q2"></a>
+<p><b>(2) What datatypes does SQLite support?</b></p>
+<blockquote>SQLite uses <a href="datatype3.html">dynamic typing</a>.  Content can be stored as INTEGER,
+  REAL, TEXT, BLOB, or as NULL.</blockquote></li>
+<a name="q3"></a>
+<p><b>(3) SQLite lets me insert a string into a database column of type integer!</b></p>
+<blockquote><p>This is a feature, not a bug.  SQLite uses <a href="datatype3.html">dynamic typing</a>. 
+  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 <a href="datatype3.html">datatype</a> 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 <a href="lang_createtable.html#rowid">INTEGER PRIMARY KEY</a> may only hold a 64-bit signed integer.
+  An error will result
+  if you try to put anything other than an integer into an
+  <a href="lang_createtable.html#rowid">INTEGER PRIMARY KEY</a> 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 called <a href="datatype3.html#affinity">type affinity</a>.
+  </p></blockquote></li>
+<a name="q4"></a>
+<p><b>(4) Why doesn't SQLite allow me to use '0' and '0.0' as the primary
+  key on two different rows of the same table?</b></p>
+<blockquote><p>This problem occurs when your primary key is a numeric type.  Change the
+  <a href="datatype3.html">datatype</a> 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></blockquote></li>
+<a name="q5"></a>
+<p><b>(5) Can multiple applications or multiple instances of the same
+  application access a single database file at the same time?</b></p>
+<blockquote><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 concurrency 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="c3ref/busy_handler.html">sqlite3_busy_handler()</a> or <a href="c3ref/busy_timeout.html">sqlite3_busy_timeout()</a>
+  API functions.</p></blockquote></li>
+<a name="q6"></a>
+<p><b>(6) Is SQLite threadsafe?</b></p>
+<blockquote><p><a href="http://www.eecs.berkeley.edu/Pubs/TechRpts/2006/EECS-2006-1.pdf">Threads are evil</a>.
+  Avoid them.
+
+  <p>SQLite is threadsafe.  We make this concession since many users choose
+  to ignore the advice given in the previous paragraph.
+  But in order to be thread-safe, SQLite must be compiled
+  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 <a href="c3ref/threadsafe.html">sqlite3_threadsafe()</a>
+  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 <a href="threadsafe.html">threading mode</a> 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></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_SCHEMA</b>".  Every SQLite database
+  has an SQLITE_SCHEMA table that defines the schema for the database.
+  The SQLITE_SCHEMA table looks like this:</p>
+<blockquote><pre>
+CREATE TABLE sqlite_schema (
+  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_schema
+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_SCHEMA table cannot be modified using UPDATE, INSERT, 
+  or DELETE (except under
+  <a href="pragma.html#pragma_writable_schema">extraordinary conditions</a>).  
+  The SQLITE_SCHEMA table is automatically updated by commands like
+  CREATE TABLE, CREATE INDEX, DROP TABLE, and DROP INDEX.</p>
+
+  <p>Temporary tables do not appear in the SQLITE_SCHEMA table.  Temporary
+  tables and their indices and triggers occur in another special table
+  named SQLITE_TEMP_SCHEMA.  SQLITE_TEMP_SCHEMA works just like SQLITE_SCHEMA
+  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_schema UNION ALL
+    SELECT * FROM sqlite_temp_schema)
+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 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></blockquote></li>
+<a name="q10"></a>
+<p><b>(10) Does SQLite support a BLOB type?</b></p>
+<blockquote><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></blockquote></li>
+<a name="q11"></a>
+<p><b>(11) How do I add, delete or rename columns from an existing table in SQLite?</b></p>
+<blockquote><p>SQLite has limited ALTER TABLE support that you can use to
+  add, rename or drop columns or to change the name of a table
+  as detailed at <a href="lang_altertable.html">ALTER TABLE</a>.</p>
+
+  <p>If you want to make more complex changes in the structure or
+  constraints of a table or its columns, you will have to recreate it.
+  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. See <a href="lang_altertable.html#otheralter">
+  Making Other Kinds Of Table Schema Changes</a> for procedure.</p></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 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></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 this regard. 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="rescode.html#schema">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="rescode.html#schema">SQLITE_SCHEMA</a> error from
+  <a href="c3ref/exec.html">sqlite3_exec()</a>.  Nor will you receive an 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="rescode.html#schema">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="q17"></a>
+<p><b>(17) I get some 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 
+  <a href="testing.html#coverage">full-coverage testing</a>,
+  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.
+  Most of the SQLite code base is devoted purely to testing.
+  The SQLite test suite runs tens of thousands of separate test cases and
+  many of those test cases are parameterized so that hundreds of millions
+  of tests involving billions 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 ensure 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 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 do not use themselves.</p></blockquote></li>
+<a name="q18"></a>
+<p><b>(18) Case-insensitive matching of Unicode characters does not work.</b></p>
+<blockquote>The default configuration of SQLite only supports case-insensitive
+  comparisons of ASCII characters.  The reason for this is that doing
+  full Unicode case-insensitive comparisons and case conversions 
+  requires tables and logic that would nearly double the size of
+  the SQLite library.  The
+  SQLite developers reason that any application that needs full
+  Unicode case support probably already has the necessary tables and
+  functions and so SQLite should not take up space to 
+  duplicate this ability.</p>
+
+  <p>Instead of providing full Unicode case support by default, 
+  SQLite provides the ability to link against external
+  Unicode comparison and conversion routines.
+  The application can overload the built-in <a href="datatype3.html#collation">NOCASE</a> collating
+  sequence (using <a href="c3ref/create_collation.html">sqlite3_create_collation()</a>) and the built-in
+  <a href="lang_corefunc.html#like">like()</a>, <a href="lang_corefunc.html#upper">upper()</a>, and <a href="lang_corefunc.html#lower">lower()</a> functions
+  (using <a href="c3ref/create_function.html">sqlite3_create_function()</a>).  
+  The SQLite source code includes an "ICU" extension that does 
+  these overloads.  Or, developers can write their own overloads
+  based on their own Unicode-aware comparison routines already
+  contained within their project.</blockquote></li>
+<a name="q19"></a>
+<p><b>(19) INSERT is really slow - I can only do few dozen INSERTs per second</b></p>
+<blockquote>Actually, SQLite will easily do 50,000 or more <a href="lang_insert.html">INSERT</a> statements per second
+  on an average desktop computer.  But it will only do a few dozen transactions
+  per second.  Transaction speed is limited by the rotational speed of
+  your disk drive.  A transaction normally requires two complete rotations
+  of the disk platter, which on a 7200RPM disk drive limits you to about
+  60 transactions per second.
+
+  <p>Transaction speed is limited by disk drive speed because (by default)
+  SQLite actually waits until the data really is safely stored on the disk
+  surface before the transaction is complete.  That way, if you suddenly lose
+  power or if your OS crashes, your data is still safe.  For details, 
+  read about <a href="atomiccommit.html">atomic commit in SQLite.</a>.
+
+  <p>By default, each INSERT statement is its own transaction.  But if you
+  surround multiple INSERT statements with <a href="lang_transaction.html">BEGIN</a>...<a href="lang_transaction.html">COMMIT</a> then all the
+  inserts are grouped into a single transaction.  The time needed to commit
+  the transaction is amortized over all the enclosed insert statements and
+  so the time per insert statement is greatly reduced.
+
+  <p>Another option is to run <a href="pragma.html#pragma_synchronous">PRAGMA synchronous=OFF</a>.  This command will
+  cause SQLite to not wait on data to reach the disk surface, which will make
+  write operations appear to be much faster.  But if you lose power in the
+  middle of a transaction, your database file might go corrupt.</blockquote></li>
+<a name="q20"></a>
+<p><b>(20) I accidentally deleted some important information from my SQLite database.
+  How can I recover it?</b></p>
+<blockquote>If you have a backup copy of your database file, recover the information
+  from your backup.
+
+  <p>If you do not have a backup, recovery is very difficult.  You might
+  be able to find partial string data in a binary dump of the raw database
+  file.  Recovering numeric data might also be possible given special tools,
+  though to our knowledge no such tools exist.  SQLite is sometimes compiled
+  with the <a href="compile.html#secure_delete">SQLITE_SECURE_DELETE</a> option which overwrites all deleted content
+  with zeros.  If that is the case then recovery is clearly impossible.
+  Recovery is also impossible if you have run <a href="lang_vacuum.html">VACUUM</a> since the data was
+  deleted.  If SQLITE_SECURE_DELETE is not used and VACUUM has not been run,
+  then some of the deleted content might still be in the database file, in
+  areas marked for reuse.  But, again, there exist no procedures or tools
+  that we know of to help you recover that data.</blockquote></li>
+<a name="q21"></a>
+<p><b>(21) What is an SQLITE_CORRUPT error?  What does it mean for the database
+  to be "malformed"? Why am I getting this error?</b></p>
+<blockquote><p>An <a href="rescode.html#corrupt">SQLITE_CORRUPT</a> error is returned when SQLite detects an error
+  in the structure, format, or other control elements of the
+  database file.</p>
+
+  <p>SQLite does not corrupt database files without external help.
+  If your application crashes in the middle of an
+  update, your data 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>You 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>You 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>
+
+  <p>Depending how badly your database is corrupted, you may be able to 
+  recover some of the data by using the CLI to dump the schema and contents
+  to a file and then recreate.  Unfortunately, once humpty-dumpty falls off 
+  the wall, it is generally not possible to put him back together again.</p></blockquote></li>
+<a name="q22"></a>
+<p><b>(22) Does SQLite support foreign keys?</b></p>
+<blockquote><p>
+  As of <a href="releaselog/3_6_19.html">version 3.6.19</a> (2009-10-14), 
+  SQLite supports <a href="foreignkeys.html">foreign key constraints</a>.  But enforcement
+  of foreign key constraints is turned off by default (for backwards compatibility).
+  To enable foreign key constraint enforcement, run 
+  <a href="pragma.html#pragma_foreign_keys">PRAGMA foreign_keys=ON</a> or compile with
+  <a href="compile.html#default_foreign_keys">-DSQLITE_DEFAULT_FOREIGN_KEYS=1</a>.</blockquote></li>
+<a name="q23"></a>
+<p><b>(23) I get a compiler error if I use the SQLITE_OMIT_... 
+  compile-time options when building SQLite.</b></p>
+<blockquote>The <a href="compile.html#omitfeatures">SQLITE_OMIT_...</a> compile-time options only work
+  when building from canonical source files.  They do <u>not</u> work
+  when you build from the SQLite <a href="amalgamation.html">amalgamation</a> or from the pre-processed
+  source files.
+
+  <p>It is possible to build a special <a href="amalgamation.html">amalgamation</a> that will work with
+  a predetermined set of SQLITE_OMIT_... options.  Instructions for doing
+  so can be found with the <a href="compile.html#omitfeatures">SQLITE_OMIT_... documentation</a>.</blockquote></li>
+<a name="q24"></a>
+<p><b>(24) My WHERE clause expression <tt>column1="column1"</tt> does not work.
+  It causes every row of the table to be returned, not just the rows
+  where column1 has the value "column1".</b></p>
+<blockquote>Use single-quotes, not double-quotes, around string literals in SQL.
+  This is what the SQL standard requires.  Your WHERE clause expression
+  should read: <tt>column1='column1'</tt>
+
+  <p>SQL uses double-quotes around identifiers (column or table names) that
+  contains special characters or which are keywords.  So double-quotes are
+  a way of escaping identifier names.  Hence, when you say
+  <tt>column1="column1"</tt> that is equivalent to 
+  <tt>column1=column1</tt> which is obviously always true.</blockquote></li>
+<a name="q25"></a>
+<p><b>(25) How are the syntax diagrams (a.k.a. "railroad" diagrams) for
+  SQLite generated?</b></p>
+<blockquote>Each diagram is hand-written using the <a href="https://pikchr.org/">Pikchr</a>
+  diagramming language.  These hand-written specifications are converted into
+  SVG and inserted inline in the HTML files as part of the documentation build process.
+  <p>
+  Many historical versions of the SQLite documentation used a different process for
+  generating the syntax diagrams.  The historical process was based on Tcl/Tk and is
+  described  at <a href="http://wiki.tcl-lang.org/21708">http://wiki.tcl-lang.org/21708</a>.  The newer Pikchr-based syntax diagrams
+  first landed on trunk on 2020-09-26.</blockquote></li>
+<a name="q26"></a>
+<p><b>(26) The SQL standard requires that a UNIQUE constraint be enforced even if
+  one or more of the columns in the constraint are NULL, but SQLite does
+  not do this.  Isn't that a bug?</b></p>
+<blockquote>Perhaps you are referring to the following statement from SQL92:
+
+  <blockquote>
+  A unique constraint is satisfied if and only if no two rows in a
+  table have the same non-null values in the unique columns. 
+  </blockquote>
+
+  That statement is ambiguous, having at least two possible interpretations:
+
+  <ol>
+  <li>A unique constraint is satisfied if and only if no two rows in a
+  table have the same values and have non-null values in the unique columns.
+  <li>A unique constraint is satisfied if and only if no two rows in a 
+  table have the same values in the subset of unique columns that are not null.
+  </ol>
+
+  SQLite follows interpretation (1), as does PostgreSQL, MySQL, Oracle,
+  and Firebird.  It is true that Informix and Microsoft SQL Server use
+  interpretation (2), however we the SQLite developers hold that 
+  interpretation (1) is the most natural reading 
+  of the requirement and we also want to maximize compatibility with other
+  SQL database engines, and most other database engines also go with (1),
+  so that is what SQLite does.</blockquote></li>
+<a name="q27"></a>
+<p><b>(27) What is the Export Control Classification Number (ECCN) for SQLite?</b></p>
+<blockquote>After careful review of the Commerce Control List (CCL), we are convinced
+  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.</blockquote></li>
+<a name="q28"></a>
+<p><b>(28) My query does not return the column name that I expect.  Is this a bug?</b></p>
+<blockquote>If the columns of your result set are named by AS clauses, then SQLite
+  is guaranteed to use the identifier 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 <a href="c3ref/column_name.html">sqlite3_column_name()</a> documentation for further information.</blockquote></li>
+</ol>
+<p align="center"><small><i>This page last modified on  <a href="https://sqlite.org/docsrc/honeypot" id="mtimelink"  data-href="https://sqlite.org/docsrc/finfo/pages/faq.in?m=bd6b58b596cda2d3d">2021-07-27 23:51:29</a> UTC </small></i></p>
+
author	Daniel Baumann <daniel.baumann@progress-linux.org>	2024-05-05 17:28:19 +0000
committer	Daniel Baumann <daniel.baumann@progress-linux.org>	2024-05-05 17:28:19 +0000
commit	18657a960e125336f704ea058e25c27bd3900dcb (patch)
tree	17b438b680ed45a996d7b59951e6aa34023783f2 /www/faq.html
parent	Initial commit. (diff)
download	sqlite3-18657a960e125336f704ea058e25c27bd3900dcb.tar.xz sqlite3-18657a960e125336f704ea058e25c27bd3900dcb.zip