SQLite

set rcsid {$Id: capi3ref.tcl,v 1.23 2005/06/16 19:48:39 drh Exp $}
source common.tcl
header {C/C++ Interface For SQLite Version 3}
puts {
<h2>C/C++ Interface For SQLite Version 3</h2>
}

proc api {name prototype desc {notused x}} {

 as needed.
 The encoding for the database will be UTF-8 if sqlite3_open() is called and
 UTF-16 if sqlite3_open16 is used.

 Whether or not an error occurs when it is opened, resources associated
 with the sqlite3* handle should be released by passing it to
 sqlite3_close() when it is no longer required.

 The returned sqlite3* can only be used in the same thread in which it
 was created.  It is an error to call sqlite3_open() in one thread then
 pass the resulting database handle off to another thread to use.  This
 restriction is due to goofy design decisions (bugs?) in the way some
 threading implementations interact with file locks.
}

api {} {
int sqlite3_prepare(
  sqlite3 *db,            /* Database handle */
  const char *zSql,       /* SQL statement, UTF-8 encoded */
  int nBytes,             /* Length of zSql in bytes. */

 SQLITE_ERROR means that a run-time error (such as a constraint
 violation) has occurred.  sqlite3_step() should not be called again on
 the VM. More information may be found by calling sqlite3_errmsg().

 SQLITE_MISUSE means that the this routine was called inappropriately.
 Perhaps it was called on a virtual machine that had already been
 finalized or on one that had previously returned SQLITE_ERROR or
 SQLITE_DONE.  Or it could be the case that a database connection
 is being used by a different thread than the one it was created it.
}

api {} {
void *sqlite3_trace(sqlite3*, void(*xTrace)(void*,const char*), void*);
} {
 Register a function that is called each time an SQL statement is evaluated.
 The callback function is invoked on the first call to sqlite3_step() after

#
# Run this script to generated a faq.html output file
#
set rcsid {$Id: faq.tcl,v 1.29 2005/06/16 19:48:40 drh Exp $}
source common.tcl
header {SQLite Frequently Asked Questions</title>}

set cnt 1
proc faq {question answer} {
  set ::faq($::cnt) [list [string trim $question] [string trim $answer]]
  incr ::cnt

  unique keys will be sequential until the largest key reaches a value
  of 2147483647.  That is the largest 32-bit signed integer and cannot
  be incremented, so subsequent insert attempts will revert to the
  semi-random key generation algorithm of SQLite version 2.3.3 and
  earlier.</p>

  <p>Beginning with version 2.2.3, there is a new API function named
  <b>sqlite3_last_insert_rowid()</b> which will return the integer key
  for the most recent insert operation.  See the API documentation for
  details.</p>

  <p>SQLite version 3.0 expands the size of the rowid to 64 bits.</p>
}

faq {

  generally have finer grained locking, such as locking on a single
  table or a single row within a table.  If you have a massively
  parallel database application, you should consider using a big database
  server instead of SQLite.</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 <b>sqlite3_busy_handler()</b> or
  <b>sqlite3_busy_timeout()</b> API functions.  See the API documentation
  for details.</p>

  <p>If two or more processes have the same database open and one
  process creates a new table or index, the other processes might
  not be able to see the new table right away.  You might have to
  get the other processes to close and reopen their connection to
  the database before they will be able to see the new table.</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 <b>sqlite3_open()</b>.  It is never safe
  to use the same <b>sqlite3</b> structure pointer simultaneously in two
  or more threads.</p>

  <p>An <b>sqlite3</b> structure can only be used in the same thread
  that called <b>sqlite3_open</b> to create it.  You cannot open a
  database in one thread then pass the handle off to another thread for
  it to use.  This is due to limitations (bugs?) in many common threading
  implementations such as on RedHat9.</p>

  <p>Note that if two or more threads have the same database open and one
  thread creates a new table or index, the other threads might
  not be able to see the new table right away.  You might have to
  get the other threads to close and reopen their connection to
  the database before they will be able to see the new table.</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

  <p>SQLite version 2.8 limits the amount of data in one row to 
  1 megabyte.  SQLite version 3.0 has no limit on the amount of
  data that can be stored in a single row.
  </p>

  <p>The names of tables, indices, view, triggers, and columns can be
  as long as desired.  However, the names of SQL functions (as created
  by the <a href="c_interface.html#cfunc">sqlite3_create_function()</a> API)
  may not exceed 255 characters in length.</p>
}

faq {
  What is the maximum size of a VARCHAR in SQLite?
} {
  <p>SQLite does not enforce datatype constraints.