This document describes the architecture of the SQLite library.
-The information here is useful to those who want to understand or
-modify the inner workings of SQLite.
-
-
-
-A block diagram showing the main components of SQLite
-and how they interrelate is shown at the right. The text that
-follows will provide a quick overview of each of these components.
-
-
-
-
-This document describes SQLite version 3.0. Version 2.8 and
-earlier are similar but the details differ.
-
-
-
Interface
-
-
Much of the public interface to the SQLite library is implemented by
-functions found in the main.c, legacy.c, and
-vdbeapi.c source files
-though some routines are
-scattered about in other files where they can have access to data
-structures with file scope. The
-sqlite3_get_table() routine is implemented in table.c.
-sqlite3_mprintf() is found in printf.c.
-sqlite3_complete() is in tokenize.c.
-The Tcl interface is implemented by tclsqlite.c. More
-information on the C interface to SQLite is
-available separately.
-
-
To avoid name collisions with other software, all external
-symbols in the SQLite library begin with the prefix sqlite3.
-Those symbols that are intended for external use (in other words,
-those symbols which form the API for SQLite) begin
-with sqlite3_.
-
-
Tokenizer
-
-
When a string containing SQL statements is to be executed, the
-interface passes that string to the tokenizer. The job of the tokenizer
-is to break the original string up into tokens and pass those tokens
-one by one to the parser. The tokenizer is hand-coded in C in
-the file tokenize.c.
-
-
Note that in this design, the tokenizer calls the parser. People
-who are familiar with YACC and BISON may be used to doing things the
-other way around -- having the parser call the tokenizer. The author
-of SQLite
-has done it both ways and finds things generally work out nicer for
-the tokenizer to call the parser. YACC has it backwards.
-
-
Parser
-
-
The parser is the piece that assigns meaning to tokens based on
-their context. The parser for SQLite is generated using the
-Lemon LALR(1) parser
-generator. Lemon does the same job as YACC/BISON, but it uses
-a different input syntax which is less error-prone.
-Lemon also generates a parser which is reentrant and thread-safe.
-And lemon defines the concept of a non-terminal destructor so
-that it does not leak memory when syntax errors are encountered.
-The source file that drives Lemon is found in parse.y.
-
-
Because
-lemon is a program not normally found on development machines, the
-complete source code to lemon (just one C file) is included in the
-SQLite distribution in the "tool" subdirectory. Documentation on
-lemon is found in the "doc" subdirectory of the distribution.
-
-
-
Code Generator
-
-
After the parser assembles tokens into complete SQL statements,
-it calls the code generator to produce virtual machine code that
-will do the work that the SQL statements request. There are many
-files in the code generator:
-attach.c,
-auth.c,
-build.c,
-delete.c,
-expr.c,
-insert.c,
-pragma.c,
-select.c,
-trigger.c,
-update.c,
-vacuum.c
-and where.c.
-In these files is where most of the serious magic happens.
-expr.c handles code generation for expressions.
-where.c handles code generation for WHERE clauses on
-SELECT, UPDATE and DELETE statements. The files attach.c,
-delete.c, insert.c, select.c, trigger.c
-update.c, and vacuum.c handle the code generation
-for SQL statements with the same names. (Each of these files calls routines
-in expr.c and where.c as necessary.) All other
-SQL statements are coded out of build.c.
-The auth.c file implements the functionality of
-sqlite3_set_authorizer().
-
-
Virtual Machine
-
-
The program generated by the code generator is executed by
-the virtual machine. Additional information about the virtual
-machine is available separately.
-To summarize, the virtual machine implements an abstract computing
-engine specifically designed to manipulate database files. The
-machine has a stack which is used for intermediate storage.
-Each instruction contains an opcode and
-up to three additional operands.
-
-
The virtual machine itself is entirely contained in a single
-source file vdbe.c. The virtual machine also has
-its own header files: vdbe.h that defines an interface
-between the virtual machine and the rest of the SQLite library and
-vdbeInt.h which defines structure private the virtual machine.
-The vdbeaux.c file contains utilities used by the virtual
-machine and interface modules used by the rest of the library to
-construct VM programs. The vdbeapi.c file contains external
-interfaces to the virtual machine such as the
-sqlite3_bind_... family of functions. Individual values
-(strings, integer, floating point numbers, and BLOBs) are stored
-in an internal object named "Mem" which is implemented by
-vdbemem.c.
-
-
-SQLite implements SQL functions using callbacks to C-language routines.
-Even the built-in SQL functions are implemented this way. Most of
-the built-in SQL functions (ex: coalesce(), count(),
-substr(), and so forth) can be found in func.c.
-Date and time conversion functions are found in date.c.
-
-
-
B-Tree
-
-
An SQLite database is maintained on disk using a B-tree implementation
-found in the btree.c source file. A separate B-tree is used for
-each table and index in the database. All B-trees are stored in the
-same disk file. Details of the file format are recorded in a large
-comment at the beginning of btree.c.
-
-
The interface to the B-tree subsystem is defined by the header file
-btree.h.
-
-
-
Page Cache
-
-
The B-tree module requests information from the disk in fixed-size
-chunks. The default chunk size is 1024 bytes but can vary between 512
-and 65536 bytes.
-The page cache is responsible for reading, writing, and
-caching these chunks.
-The page cache also provides the rollback and atomic commit abstraction
-and takes care of locking of the database file. The
-B-tree driver requests particular pages from the page cache and notifies
-the page cache when it wants to modify pages or commit or rollback
-changes and the page cache handles all the messy details of making sure
-the requests are handled quickly, safely, and efficiently.
-
-
The code to implement the page cache is contained in the single C
-source file pager.c. The interface to the page cache subsystem
-is defined by the header file pager.h.
-
-
-
OS Interface
-
-
-In order to provide portability between POSIX and Win32 operating systems,
-SQLite uses an abstraction layer to interface with the operating system.
-The interface to the OS abstraction layer is defined in
-os.h. Each supported operating system has its own implementation:
-os_unix.c for Unix, os_win.c for windows, and so forth.
-Each of these operating-specific implements typically has its own
-header file: os_unix.h, os_win.h, etc.
-
-
-
Utilities
-
-
-Memory allocation and caseless string comparison routines are located
-in util.c.
-Symbol tables used by the parser are maintained by hash tables found
-in hash.c. The utf.c source file contains Unicode
-conversion subroutines.
-SQLite has its own private implementation of printf() (with
-some extensions) in printf.c and its own random number generator
-in random.c.
-
-
-
Test Code
-
-
-If you count regression test scripts,
-more than half the total code base of SQLite is devoted to testing.
-There are many assert() statements in the main code files.
-In additional, the source files test1.c through test5.c
-together with md5.c implement extensions used for testing
-purposes only. The os_test.c backend interface is used to
-simulate power failures to verify the crash-recovery mechanism in
-the pager.
-
-(This page was last modified on [lrange $rcsid 3 4] UTC)
-
"
-
-puts {
-
-A security audit for SQLite consists of two components. First, there is
-a check for common errors that often lead to security problems. Second,
-an attempt is made to construct a proof that SQLite has certain desirable
-security properties.
-
-
-
Part I: Things to check
-
-
-Scan all source code and check for the following common errors:
-
-
-
-
-Verify that the destination buffer is large enough to hold its result
-in every call to the following routines:
-
-
strcpy()
-
strncpy()
-
strcat()
-
memcpy()
-
memset()
-
memmove()
-
bcopy()
-
sprintf()
-
scanf()
-
-
-
-Verify that pointers returned by subroutines are not NULL before using
-the pointers. In particular, make sure the return values for the following
-routines are checked before they are used:
-
-
malloc()
-
realloc()
-
sqliteMalloc()
-
sqliteRealloc()
-
sqliteStrDup()
-
sqliteStrNDup()
-
sqliteExpr()
-
sqliteExprFunction()
-
sqliteExprListAppend()
-
sqliteResultSetOfSelect()
-
sqliteIdListAppend()
-
sqliteSrcListAppend()
-
sqliteSelectNew()
-
sqliteTableNameToTable()
-
sqliteTableTokenToSrcList()
-
sqliteWhereBegin()
-
sqliteFindTable()
-
sqliteFindIndex()
-
sqliteTableNameFromToken()
-
sqliteGetVdbe()
-
sqlite_mprintf()
-
sqliteExprDup()
-
sqliteExprListDup()
-
sqliteSrcListDup()
-
sqliteIdListDup()
-
sqliteSelectDup()
-
sqliteFindFunction()
-
sqliteTriggerSelectStep()
-
sqliteTriggerInsertStep()
-
sqliteTriggerUpdateStep()
-
sqliteTriggerDeleteStep()
-
-
-
-On all functions and procedures, verify that pointer parameters are not NULL
-before dereferencing those parameters.
-
-
-Check to make sure that temporary files are opened safely: that the process
-will not overwrite an existing file when opening the temp file and that
-another process is unable to substitute a file for the temp file being
-opened.
-
-
-
-
-
-
Part II: Things to prove
-
-
-Prove that SQLite exhibits the characteristics outlined below:
-
-
-
-
-The following are preconditions:
-
-
Z is an arbitrary-length NUL-terminated string.
-
An existing SQLite database has been opened. The return value
- from the call to sqlite_open() is stored in the variable
- db.
-
The database contains at least one table of the form:
-
-CREATE TABLE t1(a CLOB);
-
-
There are no user-defined functions other than the standard
- build-in functions.
-
-
The following statement of C code is executed:
-
-sqlite_exec_printf(
- db,
- "INSERT INTO t1(a) VALUES('%q');",
- 0, 0, 0, Z
-);
-
-
Prove the following are true for all possible values of string Z:
-
-
-The call to sqlite_exec_printf() will
-return in a length of time that is a polynomial in strlen(Z).
-It might return an error code but it will not crash.
-
-
-At most one new row will be inserted into table t1.
-
-
-No preexisting rows of t1 will be deleted or modified.
-
-
-No tables other than t1 will be altered in any way.
-
-
-No preexisting files on the host computers filesystem, other than
-the database file itself, will be deleted or modified.
-
-
-For some constants K1 and K2,
-if at least K1*strlen(Z) + K2 bytes of contiguous memory are
-available to malloc(), then the call to sqlite_exec_printf()
-will not return SQLITE_NOMEM.
-
-
-
-
-
-
-The following are preconditions:
-
-
Z is an arbitrary-length NUL-terminated string.
-
An existing SQLite database has been opened. The return value
- from the call to sqlite_open() is stored in the variable
- db.
-
There exists a callback function cb() that appends all
- information passed in through its parameters into a single
- data buffer called Y.
-
There are no user-defined functions other than the standard
- build-in functions.
-
-
The following statement of C code is executed:
-
-sqlite_exec(db, Z, cb, 0, 0);
-
-
Prove the following are true for all possible values of string Z:
-
-
-The call to sqlite_exec() will
-return in a length of time which is a polynomial in strlen(Z).
-It might return an error code but it will not crash.
-
-
-After sqlite_exec() returns, the buffer Y will not contain
-any content from any preexisting file on the host computers file system,
-except for the database file.
-
-
-After the call to sqlite_exec() returns, the database file will
-still be well-formed. It might not contain the same data, but it will
-still be a properly constructed SQLite database file.
-
-
-No preexisting files on the host computers filesystem, other than
-the database file itself, will be deleted or modified.
-
-
-For some constants K1 and K2,
-if at least K1*strlen(Z) + K2 bytes of contiguous memory are
-available to malloc(), then the call to sqlite_exec()
-will not return SQLITE_NOMEM.
-
-
-}
DELETED www/autoinc.tcl
Index: www/autoinc.tcl
==================================================================
--- www/autoinc.tcl
+++ /dev/null
@@ -1,109 +0,0 @@
-#
-# Run this Tcl script to generate the autoinc.html file.
-#
-set rcsid {$Id: }
-source common.tcl
-
-if {[llength $argv]>0} {
- set outputdir [lindex $argv 0]
-} else {
- set outputdir ""
-}
-
-header {SQLite Autoincrement}
-puts {
-
SQLite Autoincrement
-
-
-In SQLite, every row of every table has an integer ROWID.
-The ROWID for each row is unique among all rows in the same table.
-In SQLite version 2.8 the ROWID is a 32-bit signed integer.
-Version 3.0 of SQLite expanded the ROWID to be a 64-bit signed integer.
-
-
-
-You can access the ROWID of an SQLite table using one the special column
-names ROWID, _ROWID_, or OID.
-Except if you declare an ordinary table column to use one of those special
-names, then the use of that name will refer to the declared column not
-to the internal ROWID.
-
-
-
-If a table contains a column of type INTEGER PRIMARY KEY, then that
-column becomes an alias for the ROWID. You can then access the ROWID
-using any of four different names, the original three names described above
-or the name given to the INTEGER PRIMARY KEY column. All these names are
-aliases for one another and work equally well in any context.
-
-
-
-When a new row is inserted into an SQLite table, the ROWID can either
-be specified as part of the INSERT statement or it can be assigned
-automatically by the database engine. To specify a ROWID manually,
-just include it in the list of values to be inserted. For example:
-
-
-
-CREATE TABLE test1(a INT, b TEXT);
-INSERT INTO test1(rowid, a, b) VALUES(123, 5, 'hello');
-
-
-
-If no ROWID is specified on the insert, an appropriate ROWID is created
-automatically. The usual algorithm is to give the newly created row
-a ROWID that is one larger than the largest ROWID in the table prior
-to the insert. If the table is initially empty, then a ROWID of 1 is
-used. If the largest ROWID is equal to the largest possible integer
-(9223372036854775807 in SQLite version 3.0 and later) then the database
-engine starts picking candidate ROWIDs at random until it finds one
-that is not previously used.
-
-
-
-The normal ROWID selection algorithm described above
-will generate monotonically increasing
-unique ROWIDs as long as you never use the maximum ROWID value and you never
-delete the entry in the table with the largest ROWID.
-If you ever delete rows or if you ever create a row with the maximum possible
-ROWID, then ROWIDs from previously deleted rows might be reused when creating
-new rows and newly created ROWIDs might not be in strictly accending order.
-
-
-
-
The AUTOINCREMENT Keyword
-
-
-If a column has the type INTEGER PRIMARY KEY AUTOINCREMENT then a slightly
-different ROWID selection algorithm is used.
-The ROWID chosen for the new row is one larger than the largest ROWID
-that has ever before existed in that same table. If the table has never
-before contained any data, then a ROWID of 1 is used. If the table
-has previously held a row with the largest possible ROWID, then new INSERTs
-are not allowed and any attempt to insert a new row will fail with an
-SQLITE_FULL error.
-
-
-
-SQLite keeps track of the largest ROWID that a table has ever held using
-the special SQLITE_SEQUENCE table. The SQLITE_SEQUENCE table is created
-and initialized automatically whenever a normal table that contains an
-AUTOINCREMENT column is created. The content of the SQLITE_SEQUENCE table
-can be modified using ordinary UPDATE, INSERT, and DELETE statements.
-But making modifications to this table will likely perturb the AUTOINCREMENT
-key generation algorithm. Make sure you know what you are doing before
-you undertake such changes.
-
-
-
-The behavior implemented by the AUTOINCREMENT keyword is subtly different
-from the default behavior. With AUTOINCREMENT, rows with automatically
-selected ROWIDs are guaranteed to have ROWIDs that have never been used
-before by the same table in the same database. And the automatically generated
-ROWIDs are guaranteed to be monotonically increasing. These are important
-properties in certain applications. But if your application does not
-need these properties, you should probably stay with the default behavior
-since the use of AUTOINCREMENT requires additional work to be done
-as each row is inserted and thus causes INSERTs to run a little slower.
-}
-footer $rcsid
DELETED www/c_interface.tcl
Index: www/c_interface.tcl
==================================================================
--- www/c_interface.tcl
+++ /dev/null
@@ -1,1116 +0,0 @@
-#
-# Run this Tcl script to generate the sqlite.html file.
-#
-set rcsid {$Id: c_interface.tcl,v 1.43 2004/11/19 11:59:24 danielk1977 Exp $}
-source common.tcl
-header {The C language interface to the SQLite library}
-puts {
-
The C language interface to the SQLite library
-
-
The SQLite library is designed to be very easy to use from
-a C or C++ program. This document gives an overview of the C/C++
-programming interface.
-
-
1.0 The Core API
-
-
The interface to the SQLite library consists of three core functions,
-one opaque data structure, and some constants used as return values.
-The core interface is as follows:
-The above is all you really need to know in order to use SQLite
-in your C or C++ programs. There are other interface functions
-available (and described below) but we will begin by describing
-the core functions shown above.
-
If the
-EMPTY_RESULT_CALLBACKS pragma is set to ON and the result of
-a query is an empty set, then the callback is invoked once with the
-third parameter (argv) set to 0. In other words
-
-argv == 0
-
-The second parameter (argc)
-and the fourth parameter (columnNames) are still valid
-and can be used to determine the number and names of the result
-columns if there had been a result.
-The default behavior is not to invoke the callback at all if the
-result set is empty.
-
-
-
-Each invocation of sqlite_step returns an integer code that
-indicates what happened during that step. This code may be
-SQLITE_BUSY, SQLITE_ROW, SQLITE_DONE, SQLITE_ERROR, or
-SQLITE_MISUSE.
-
-
-
-If the virtual machine is unable to open the database file because
-it is locked by another thread or process, sqlite_step
-will return SQLITE_BUSY. The calling function should do some other
-activity, or sleep, for a short amount of time to give the lock a
-chance to clear, then invoke sqlite_step again. This can
-be repeated as many times as desired.
-
-
-
-Whenever another row of result data is available,
-sqlite_step will return SQLITE_ROW. The row data is
-stored in an array of pointers to strings and the 2nd parameter
-is made to point to this array.
-
-
-
-When all processing is complete, sqlite_step will return
-either SQLITE_DONE or SQLITE_ERROR. SQLITE_DONE indicates that the
-statement completed successfully and SQLITE_ERROR indicates that there
-was a run-time error. (The details of the error are obtained from
-sqlite_finalize.) It is a misuse of the library to attempt
-to call sqlite_step again after it has returned SQLITE_DONE
-or SQLITE_ERROR.
-
-
-
-When sqlite_step returns SQLITE_DONE or SQLITE_ERROR,
-the *pN and *pazColName values are set to the number of columns
-in the result set and to the names of the columns, just as they
-are for an SQLITE_ROW return. This allows the calling code to
-find the number of result columns and the column names and datatypes
-even if the result set is empty. The *pazValue parameter is always
-set to NULL when the return codes is SQLITE_DONE or SQLITE_ERROR.
-If the SQL being executed is a statement that does not
-return a result (such as an INSERT or an UPDATE) then *pN will
-be set to zero and *pazColName will be set to NULL.
-
-
-
-If you abuse the library by trying to call sqlite_step
-inappropriately it will attempt return SQLITE_MISUSE.
-This can happen if you call sqlite_step() on the same virtual machine
-at the same
-time from two or more threads or if you call sqlite_step()
-again after it returned SQLITE_DONE or SQLITE_ERROR or if you
-pass in an invalid virtual machine pointer to sqlite_step().
-You should not depend on the SQLITE_MISUSE return code to indicate
-an error. It is possible that a misuse of the interface will go
-undetected and result in a program crash. The SQLITE_MISUSE is
-intended as a debugging aid only - to help you detect incorrect
-usage prior to a mishap. The misuse detection logic is not guaranteed
-to work in every case.
-
-
-
2.3 Deleting A Virtual Machine
-
-
-Every virtual machine that sqlite_compile creates should
-eventually be handed to sqlite_finalize. The sqlite_finalize()
-procedure deallocates the memory and other resources that the virtual
-machine uses. Failure to call sqlite_finalize() will result in
-resource leaks in your program.
-
-
-
-The sqlite_finalize routine also returns the result code
-that indicates success or failure of the SQL operation that the
-virtual machine carried out.
-The value returned by sqlite_finalize() will be the same as would
-have been returned had the same SQL been executed by sqlite_exec.
-The error message returned will also be the same.
-
-
-
-It is acceptable to call sqlite_finalize on a virtual machine
-before sqlite_step has returned SQLITE_DONE. Doing so has
-the effect of interrupting the operation in progress. Partially completed
-changes will be rolled back and the database will be restored to its
-original state (unless an alternative recovery algorithm is selected using
-an ON CONFLICT clause in the SQL being executed.) The effect is the
-same as if a callback function of sqlite_exec had returned
-non-zero.
-
-
-
-It is also acceptable to call sqlite_finalize on a virtual machine
-that has never been passed to sqlite_step even once.
-
-
-
3.0 The Extended API
-
-
Only the three core routines described in section 1.0 are required to use
-SQLite. But there are many other functions that provide
-useful interfaces. These extended routines are as follows:
-
All of the above definitions are included in the "sqlite.h"
-header file that comes in the source tree.
-
-
3.1 The ROWID of the most recent insert
-
-
Every row of an SQLite table has a unique integer key. If the
-table has a column labeled INTEGER PRIMARY KEY, then that column
-serves as the key. If there is no INTEGER PRIMARY KEY column then
-the key is a unique integer. The key for a row can be accessed in
-a SELECT statement or used in a WHERE or ORDER BY clause using any
-of the names "ROWID", "OID", or "_ROWID_".
-
-
When you do an insert into a table that does not have an INTEGER PRIMARY
-KEY column, or if the table does have an INTEGER PRIMARY KEY but the value
-for that column is not specified in the VALUES clause of the insert, then
-the key is automatically generated. You can find the value of the key
-for the most recent INSERT statement using the
-sqlite_last_insert_rowid API function.
-
-
3.2 The number of rows that changed
-
-
The sqlite_changes API function returns the number of rows
-that have been inserted, deleted, or modified since the database was
-last quiescent. A "quiescent" database is one in which there are
-no outstanding calls to sqlite_exec and no VMs created by
-sqlite_compile that have not been finalized by sqlite_finalize.
-In common usage, sqlite_changes returns the number
-of rows inserted, deleted, or modified by the most recent sqlite_exec
-call or since the most recent sqlite_compile. But if you have
-nested calls to sqlite_exec (that is, if the callback routine
-of one sqlite_exec invokes another sqlite_exec) or if
-you invoke sqlite_compile to create a new VM while there is
-still another VM in existance, then
-the meaning of the number returned by sqlite_changes is more
-complex.
-The number reported includes any changes
-that were later undone by a ROLLBACK or ABORT. But rows that are
-deleted because of a DROP TABLE are not counted.
-
-
SQLite implements the command "DELETE FROM table" (without
-a WHERE clause) by dropping the table then recreating it.
-This is much faster than deleting the elements of the table individually.
-But it also means that the value returned from sqlite_changes
-will be zero regardless of the number of elements that were originally
-in the table. If an accurate count of the number of elements deleted
-is necessary, use "DELETE FROM table WHERE 1" instead.
-
-
3.3 Querying into memory obtained from malloc()
-
-
The sqlite_get_table function is a wrapper around
-sqlite_exec that collects all the information from successive
-callbacks and writes it into memory obtained from malloc(). This
-is a convenience function that allows the application to get the
-entire result of a database query with a single function call.
-
-
The main result from sqlite_get_table is an array of pointers
-to strings. There is one element in this array for each column of
-each row in the result. NULL results are represented by a NULL
-pointer. In addition to the regular data, there is an added row at the
-beginning of the array that contains the name of each column of the
-result.
-
-
As an example, consider the following query:
-
-
-SELECT employee_name, login, host FROM users WHERE login LIKE 'd%';
-
-
-
This query will return the name, login and host computer name
-for every employee whose login begins with the letter "d". If this
-query is submitted to sqlite_get_table the result might
-look like this:
Notice that the "host" value for the "dummy" record is NULL so
-the result[] array contains a NULL pointer at that slot.
-
-
If the result set of a query is empty, then by default
-sqlite_get_table will set nrow to 0 and leave its
-result parameter is set to NULL. But if the EMPTY_RESULT_CALLBACKS
-pragma is ON then the result parameter is initialized to the names
-of the columns only. For example, consider this query which has
-an empty result set:
-
-
-SELECT employee_name, login, host FROM users WHERE employee_name IS NULL;
-
-
-
-The default behavior gives this results:
-
-
-
-nrow = 0
-ncolumn = 0
-result = 0
-
-
-
-But if the EMPTY_RESULT_CALLBACKS pragma is ON, then the following
-is returned:
-
Memory to hold the information returned by sqlite_get_table
-is obtained from malloc(). But the calling function should not try
-to free this information directly. Instead, pass the complete table
-to sqlite_free_table when the table is no longer needed.
-It is safe to call sqlite_free_table with a NULL pointer such
-as would be returned if the result set is empty.
-
-
The sqlite_get_table routine returns the same integer
-result code as sqlite_exec.
-
-
3.4 Interrupting an SQLite operation
-
-
The sqlite_interrupt function can be called from a
-different thread or from a signal handler to cause the current database
-operation to exit at its first opportunity. When this happens,
-the sqlite_exec routine (or the equivalent) that started
-the database operation will return SQLITE_INTERRUPT.
-
-
3.5 Testing for a complete SQL statement
-
-
The next interface routine to SQLite is a convenience function used
-to test whether or not a string forms a complete SQL statement.
-If the sqlite_complete function returns true when its input
-is a string, then the argument forms a complete SQL statement.
-There are no guarantees that the syntax of that statement is correct,
-but we at least know the statement is complete. If sqlite_complete
-returns false, then more text is required to complete the SQL statement.
-
-
For the purpose of the sqlite_complete function, an SQL
-statement is complete if it ends in a semicolon.
-
-
The sqlite command-line utility uses the sqlite_complete
-function to know when it needs to call sqlite_exec. After each
-line of input is received, sqlite calls sqlite_complete
-on all input in its buffer. If sqlite_complete returns true,
-then sqlite_exec is called and the input buffer is reset. If
-sqlite_complete returns false, then the prompt is changed to
-the continuation prompt and another line of text is read and added to
-the input buffer.
-
-
3.6 Library version string
-
-
The SQLite library exports the string constant named
-sqlite_version which contains the version number of the
-library. The header file contains a macro SQLITE_VERSION
-with the same information. If desired, a program can compare
-the SQLITE_VERSION macro against the sqlite_version
-string constant to verify that the version number of the
-header file and the library match.
-
-
3.7 Library character encoding
-
-
By default, SQLite assumes that all data uses a fixed-size
-8-bit character (iso8859). But if you give the --enable-utf8 option
-to the configure script, then the library assumes UTF-8 variable
-sized characters. This makes a difference for the LIKE and GLOB
-operators and the LENGTH() and SUBSTR() functions. The static
-string sqlite_encoding will be set to either "UTF-8" or
-"iso8859" to indicate how the library was compiled. In addition,
-the sqlite.h header file will define one of the
-macros SQLITE_UTF8 or SQLITE_ISO8859, as appropriate.
-
-
Note that the character encoding mechanism used by SQLite cannot
-be changed at run-time. This is a compile-time option only. The
-sqlite_encoding character string just tells you how the library
-was compiled.
-
-
3.8 Changing the library's response to locked files
-
-
The sqlite_busy_handler procedure can be used to register
-a busy callback with an open SQLite database. The busy callback will
-be invoked whenever SQLite tries to access a database that is locked.
-The callback will typically do some other useful work, or perhaps sleep,
-in order to give the lock a chance to clear. If the callback returns
-non-zero, then SQLite tries again to access the database and the cycle
-repeats. If the callback returns zero, then SQLite aborts the current
-operation and returns SQLITE_BUSY.
-
-
The arguments to sqlite_busy_handler are the opaque
-structure returned from sqlite_open, a pointer to the busy
-callback function, and a generic pointer that will be passed as
-the first argument to the busy callback. When SQLite invokes the
-busy callback, it sends it three arguments: the generic pointer
-that was passed in as the third argument to sqlite_busy_handler,
-the name of the database table or index that the library is trying
-to access, and the number of times that the library has attempted to
-access the database table or index.
-
-
For the common case where we want the busy callback to sleep,
-the SQLite library provides a convenience routine sqlite_busy_timeout.
-The first argument to sqlite_busy_timeout is a pointer to
-an open SQLite database and the second argument is a number of milliseconds.
-After sqlite_busy_timeout has been executed, the SQLite library
-will wait for the lock to clear for at least the number of milliseconds
-specified before it returns SQLITE_BUSY. Specifying zero milliseconds for
-the timeout restores the default behavior.
-
-
3.9 Using the _printf() wrapper functions
-
-
The four utility functions
-
-
-
-
sqlite_exec_printf()
-
sqlite_exec_vprintf()
-
sqlite_get_table_printf()
-
sqlite_get_table_vprintf()
-
-
-
-
implement the same query functionality as sqlite_exec
-and sqlite_get_table. But instead of taking a complete
-SQL statement as their second argument, the four _printf
-routines take a printf-style format string. The SQL statement to
-be executed is generated from this format string and from whatever
-additional arguments are attached to the end of the function call.
-
-
There are two advantages to using the SQLite printf
-functions instead of sprintf. First of all, with the
-SQLite printf routines, there is never a danger of overflowing a
-static buffer as there is with sprintf. The SQLite
-printf routines automatically allocate (and later frees)
-as much memory as is
-necessary to hold the SQL statements generated.
-
-
The second advantage the SQLite printf routines have over
-sprintf are two new formatting options specifically designed
-to support string literals in SQL. Within the format string,
-the %q formatting option works very much like %s in that it
-reads a null-terminated string from the argument list and inserts
-it into the result. But %q translates the inserted string by
-making two copies of every single-quote (') character in the
-substituted string. This has the effect of escaping the end-of-string
-meaning of single-quote within a string literal. The %Q formatting
-option works similar; it translates the single-quotes like %q and
-additionally encloses the resulting string in single-quotes.
-If the argument for the %Q formatting options is a NULL pointer,
-the resulting string is NULL without single quotes.
-
-
-
Consider an example. Suppose you are trying to insert a string
-value into a database table where the string value was obtained from
-user input. Suppose the string to be inserted is stored in a variable
-named zString. The code to do the insertion might look like this:
If the zString variable holds text like "Hello", then this statement
-will work just fine. But suppose the user enters a string like
-"Hi y'all!". The SQL statement generated reads as follows:
-
-
-INSERT INTO table1 VALUES('Hi y'all')
-
-
-
This is not valid SQL because of the apostrophy in the word "y'all".
-But if the %q formatting option is used instead of %s, like this:
Then the generated SQL will look like the following:
-
-
-INSERT INTO table1 VALUES('Hi y''all')
-
-
-
Here the apostrophy has been escaped and the SQL statement is well-formed.
-When generating SQL on-the-fly from data that might contain a
-single-quote character ('), it is always a good idea to use the
-SQLite printf routines and the %q formatting option instead of sprintf.
-
-
-
If the %Q formatting option is used instead of %q, like this:
The sqlite_mprintf() routine works like the the standard library
-sprintf() except that it writes its results into memory obtained
-from malloc() and returns a pointer to the malloced buffer.
-sqlite_mprintf() also understands the %q and %Q extensions described
-above. The sqlite_vmprintf() is a varargs version of the same
-routine. The string pointer that these routines return should be freed
-by passing it to sqlite_freemem().
-
-
-
3.10 Performing background jobs during large queries
-
-
The sqlite_progress_handler() routine can be used to register a
-callback routine with an SQLite database to be invoked periodically during long
-running calls to sqlite_exec(), sqlite_step() and the various
-wrapper functions.
-
-
-
The callback is invoked every N virtual machine operations, where N is
-supplied as the second argument to sqlite_progress_handler(). The third
-and fourth arguments to sqlite_progress_handler() are a pointer to the
-routine to be invoked and a void pointer to be passed as the first argument to
-it.
-
-
-
The time taken to execute each virtual machine operation can vary based on
-many factors. A typical value for a 1 GHz PC is between half and three million
-per second but may be much higher or lower, depending on the query. As such it
-is difficult to schedule background operations based on virtual machine
-operations. Instead, it is recommended that a callback be scheduled relatively
-frequently (say every 1000 instructions) and external timer routines used to
-determine whether or not background jobs need to be run.
-
-SQLite version 3.0 is a new version of SQLite, derived from
-the SQLite 2.8.13 code base, but with an incompatible file format
-and API.
-SQLite version 3.0 was created to answer demand for the following features:
-
-
-
-
Support for UTF-16.
-
User-definable text collating sequences.
-
The ability to store BLOBs in indexed columns.
-
-
-
-It was necessary to move to version 3.0 to implement these features because
-each requires incompatible changes to the database file format. Other
-incompatible changes, such as a cleanup of the API, were introduced at the
-same time under the theory that it is best to get your incompatible changes
-out of the way all at once.
-
-
-
-The API for version 3.0 is similar to the version 2.X API,
-but with some important changes. Most noticeably, the "sqlite_"
-prefix that occurs on the beginning of all API functions and data
-structures are changed to "sqlite3_".
-This avoids confusion between the two APIs and allows linking against both
-SQLite 2.X and SQLite 3.0 at the same time.
-
-
-
-There is no agreement on what the C datatype for a UTF-16
-string should be. Therefore, SQLite uses a generic type of void*
-to refer to UTF-16 strings. Client software can cast the void*
-to whatever datatype is appropriate for their system.
-
-
-
2.0 C/C++ Interface
-
-
-The API for SQLite 3.0 includes 83 separate functions in addition
-to several data structures and #defines. (A complete
-API reference is provided as a separate document.)
-Fortunately, the interface is not nearly as complex as its size implies.
-Simple programs can still make do with only 3 functions:
-sqlite3_open(),
-sqlite3_exec(), and
-sqlite3_close().
-More control over the execution of the database engine is provided
-using
-sqlite3_prepare()
-to compile an SQLite statement into byte code and
-sqlite3_step()
-to execute that bytecode.
-A family of routines with names beginning with
-sqlite3_column_
-is used to extract information about the result set of a query.
-Many interface functions come in pairs, with both a UTF-8 and
-UTF-16 version. And there is a collection of routines
-used to implement user-defined SQL functions and user-defined
-text collating sequences.
-
-
-
-
2.1 Opening and closing a database
-
-
- typedef struct sqlite3 sqlite3;
- int sqlite3_open(const char*, sqlite3**);
- int sqlite3_open16(const void*, sqlite3**);
- int sqlite3_close(sqlite3*);
- const char *sqlite3_errmsg(sqlite3*);
- const void *sqlite3_errmsg16(sqlite3*);
- int sqlite3_errcode(sqlite3*);
-
-
-
-The sqlite3_open() routine returns an integer error code rather than
-a pointer to the sqlite3 structure as the version 2 interface did.
-The difference between sqlite3_open()
-and sqlite3_open16() is that sqlite3_open16() takes UTF-16 (in host native
-byte order) for the name of the database file. If a new database file
-needs to be created, then sqlite3_open16() sets the internal text
-representation to UTF-16 whereas sqlite3_open() sets the text
-representation to UTF-8.
-
-
-
-The opening and/or creating of the database file is deferred until the
-file is actually needed. This allows options and parameters, such
-as the native text representation and default page size, to be
-set using PRAGMA statements.
-
-
-
-The sqlite3_errcode() routine returns a result code for the most
-recent major API call. sqlite3_errmsg() returns an English-language
-text error message for the most recent error. The error message is
-represented in UTF-8 and will be ephemeral - it could disappear on
-the next call to any SQLite API function. sqlite3_errmsg16() works like
-sqlite3_errmsg() except that it returns the error message represented
-as UTF-16 in host native byte order.
-
-
-
-The error codes for SQLite version 3 are unchanged from version 2.
-They are as follows:
-
-
-
-#define SQLITE_OK 0 /* Successful result */
-#define SQLITE_ERROR 1 /* SQL error or missing database */
-#define SQLITE_INTERNAL 2 /* An internal logic error in SQLite */
-#define SQLITE_PERM 3 /* Access permission denied */
-#define SQLITE_ABORT 4 /* Callback routine requested an abort */
-#define SQLITE_BUSY 5 /* The database file is locked */
-#define SQLITE_LOCKED 6 /* A table in the database is locked */
-#define SQLITE_NOMEM 7 /* A malloc() failed */
-#define SQLITE_READONLY 8 /* Attempt to write a readonly database */
-#define SQLITE_INTERRUPT 9 /* Operation terminated by sqlite_interrupt() */
-#define SQLITE_IOERR 10 /* Some kind of disk I/O error occurred */
-#define SQLITE_CORRUPT 11 /* The database disk image is malformed */
-#define SQLITE_NOTFOUND 12 /* (Internal Only) Table or record not found */
-#define SQLITE_FULL 13 /* Insertion failed because database is full */
-#define SQLITE_CANTOPEN 14 /* Unable to open the database file */
-#define SQLITE_PROTOCOL 15 /* Database lock protocol error */
-#define SQLITE_EMPTY 16 /* (Internal Only) Database table is empty */
-#define SQLITE_SCHEMA 17 /* The database schema changed */
-#define SQLITE_TOOBIG 18 /* Too much data for one row of a table */
-#define SQLITE_CONSTRAINT 19 /* Abort due to contraint violation */
-#define SQLITE_MISMATCH 20 /* Data type mismatch */
-#define SQLITE_MISUSE 21 /* Library used incorrectly */
-#define SQLITE_NOLFS 22 /* Uses OS features not supported on host */
-#define SQLITE_AUTH 23 /* Authorization denied */
-#define SQLITE_ROW 100 /* sqlite_step() has another row ready */
-#define SQLITE_DONE 101 /* sqlite_step() has finished executing */
-
-
-
2.2 Executing SQL statements
-
-
- typedef int (*sqlite_callback)(void*,int,char**, char**);
- int sqlite3_exec(sqlite3*, const char *sql, sqlite_callback, void*, char**);
-
-
-
-The sqlite3_exec function works much as it did in SQLite version 2.
-Zero or more SQL statements specified in the second parameter are compiled
-and executed. Query results are returned to a callback routine.
-See the API reference for additional
-information.
-
-
-
-In SQLite version 3, the sqlite3_exec routine is just a wrapper around
-calls to the prepared statement interface.
-
-
-
- typedef struct sqlite3_stmt sqlite3_stmt;
- int sqlite3_prepare(sqlite3*, const char*, int, sqlite3_stmt**, const char**);
- int sqlite3_prepare16(sqlite3*, const void*, int, sqlite3_stmt**, const void**);
- int sqlite3_finalize(sqlite3_stmt*);
- int sqlite3_reset(sqlite3_stmt*);
-
-
-
-The sqlite3_prepare interface compiles a single SQL statement into byte code
-for later execution. This interface is now the preferred way of accessing
-the database.
-
-
-
-The SQL statement is a UTF-8 string for sqlite3_prepare().
-The sqlite3_prepare16() works the same way except
-that it expects a UTF-16 string as SQL input.
-Only the first SQL statement in the input string is compiled.
-The fourth parameter is filled in with a pointer to the next (uncompiled)
-SQLite statement in the input string, if any.
-The sqlite3_finalize() routine deallocates a prepared SQL statement.
-All prepared statements must be finalized before the database can be
-closed.
-The sqlite3_reset() routine resets a prepared SQL statement so that it
-can be executed again.
-
-
-
-The SQL statement may contain tokens of the form "?" or "?nnn" or ":aaa"
-where "nnn" is an integer and "aaa" is an identifier.
-Such tokens represent unspecified literal values (or "wildcards")
-to be filled in later by the
-sqlite3_bind interface.
-Each wildcard has an associated number which is its sequence in the
-statement or the "nnn" in the case of a "?nnn" form.
-It is allowed for the same wildcard
-to occur more than once in the same SQL statement, in which case
-all instance of that wildcard will be filled in with the same value.
-Unbound wildcards have a value of NULL.
-
-
-
- int sqlite3_bind_blob(sqlite3_stmt*, int, const void*, int n, void(*)(void*));
- int sqlite3_bind_double(sqlite3_stmt*, int, double);
- int sqlite3_bind_int(sqlite3_stmt*, int, int);
- int sqlite3_bind_int64(sqlite3_stmt*, int, long long int);
- int sqlite3_bind_null(sqlite3_stmt*, int);
- int sqlite3_bind_text(sqlite3_stmt*, int, const char*, int n, void(*)(void*));
- int sqlite3_bind_text16(sqlite3_stmt*, int, const void*, int n, void(*)(void*));
- int sqlite3_bind_value(sqlite3_stmt*, int, const sqlite3_value*);
-
-
-
-There is an assortment of sqlite3_bind routines used to assign values
-to wildcards in a prepared SQL statement. Unbound wildcards
-are interpreted as NULLs. Bindings are not reset by sqlite3_reset().
-But wildcards can be rebound to new values after an sqlite3_reset().
-
-
-
-After an SQL statement has been prepared (and optionally bound), it
-is executed using:
-
-
-
- int sqlite3_step(sqlite3_stmt*);
-
-
-
-The sqlite3_step() routine return SQLITE_ROW if it is returning a single
-row of the result set, or SQLITE_DONE if execution has completed, either
-normally or due to an error. It might also return SQLITE_BUSY if it is
-unable to open the database file. If the return value is SQLITE_ROW, then
-the following routines can be used to extract information about that row
-of the result set:
-
-
-
- const void *sqlite3_column_blob(sqlite3_stmt*, int iCol);
- int sqlite3_column_bytes(sqlite3_stmt*, int iCol);
- int sqlite3_column_bytes16(sqlite3_stmt*, int iCol);
- int sqlite3_column_count(sqlite3_stmt*);
- const char *sqlite3_column_decltype(sqlite3_stmt *, int iCol);
- const void *sqlite3_column_decltype16(sqlite3_stmt *, int iCol);
- double sqlite3_column_double(sqlite3_stmt*, int iCol);
- int sqlite3_column_int(sqlite3_stmt*, int iCol);
- long long int sqlite3_column_int64(sqlite3_stmt*, int iCol);
- const char *sqlite3_column_name(sqlite3_stmt*, int iCol);
- const void *sqlite3_column_name16(sqlite3_stmt*, int iCol);
- const unsigned char *sqlite3_column_text(sqlite3_stmt*, int iCol);
- const void *sqlite3_column_text16(sqlite3_stmt*, int iCol);
- int sqlite3_column_type(sqlite3_stmt*, int iCol);
-
-
-
-The
-sqlite3_column_count()
-function returns the number of columns in
-the results set. sqlite3_column_count() can be called at any time after
-sqlite3_prepare().
-sqlite3_data_count()
-works similarly to
-sqlite3_column_count() except that it only works following sqlite3_step().
-If the previous call to sqlite3_step() returned SQLITE_DONE or an error code,
-then sqlite3_data_count() will return 0 whereas sqlite3_column_count() will
-continue to return the number of columns in the result set.
-
-
-
Returned data is examined using the other sqlite3_column_***() functions,
-all of which take a column number as their second parameter. Columns are
-zero-indexed from left to right. Note that this is different to parameters,
-which are indexed starting at one.
-
-
-
-The sqlite3_column_type() function returns the
-datatype for the value in the Nth column. The return value is one
-of these:
-
-The sqlite3_column_decltype() routine returns text which is the
-declared type of the column in the CREATE TABLE statement. For an
-expression, the return type is an empty string. sqlite3_column_name()
-returns the name of the Nth column. sqlite3_column_bytes() returns
-the number of bytes in a column that has type BLOB or the number of bytes
-in a TEXT string with UTF-8 encoding. sqlite3_column_bytes16() returns
-the same value for BLOBs but for TEXT strings returns the number of bytes
-in a UTF-16 encoding.
-sqlite3_column_blob() return BLOB data.
-sqlite3_column_text() return TEXT data as UTF-8.
-sqlite3_column_text16() return TEXT data as UTF-16.
-sqlite3_column_int() return INTEGER data in the host machines native
-integer format.
-sqlite3_column_int64() returns 64-bit INTEGER data.
-Finally, sqlite3_column_double() return floating point data.
-
-
-
-It is not necessary to retrieve data in the format specify by
-sqlite3_column_type(). If a different format is requested, the data
-is converted automatically.
-
-
-
-Data format conversions can invalidate the pointer returned by
-prior calls to sqlite3_column_blob(), sqlite3_column_text(), and/or
-sqlite3_column_text16(). Pointers might be invalided in the following
-cases:
-
-
-
-The initial content is a BLOB and sqlite3_column_text()
-or sqlite3_column_text16()
-is called. A zero-terminator might need to be added to the string.
-
-
-The initial content is UTF-8 text and sqlite3_column_bytes16() or
-sqlite3_column_text16() is called. The content must be converted to UTF-16.
-
-
-The initial content is UTF-16 text and sqlite3_column_bytes() or
-sqlite3_column_text() is called. The content must be converted to UTF-8.
-
-
-
-Note that conversions between UTF-16be and UTF-16le
-are always done in place and do
-not invalidate a prior pointer, though of course the content of the buffer
-that the prior pointer points to will have been modified. Other kinds
-of conversion are done in place when it is possible, but sometime it is
-not possible and in those cases prior pointers are invalidated.
-
-
-
-The safest and easiest to remember policy is this: assume that any
-result from
-
-
sqlite3_column_blob(),
-
sqlite3_column_text(), or
-
sqlite3_column_text16()
-
-is invalided by subsequent calls to
-
-
sqlite3_column_bytes(),
-
sqlite3_column_bytes16(),
-
sqlite3_column_text(), or
-
sqlite3_column_text16().
-
-This means that you should always call sqlite3_column_bytes() or
-sqlite3_column_bytes16() before calling sqlite3_column_blob(),
-sqlite3_column_text(), or sqlite3_column_text16().
-
-
-
2.3 User-defined functions
-
-
-User defined functions can be created using the following routine:
-
-The nArg parameter specifies the number of arguments to the function.
-A value of 0 indicates that any number of arguments is allowed. The
-eTextRep parameter specifies what representation text values are expected
-to be in for arguments to this function. The value of this parameter should
-be one of the parameters defined above. SQLite version 3 allows multiple
-implementations of the same function using different text representations.
-The database engine chooses the function that minimization the number
-of text conversions required.
-
-
-
-Normal functions specify only xFunc and leave xStep and xFinal set to NULL.
-Aggregate functions specify xStep and xFinal and leave xFunc set to NULL.
-There is no separate sqlite3_create_aggregate() API.
-
-
-
-The function name is specified in UTF-8. A separate sqlite3_create_function16()
-API works the same as sqlite_create_function()
-except that the function name is specified in UTF-16 host byte order.
-
-
-
-Notice that the parameters to functions are now pointers to sqlite3_value
-structures instead of pointers to strings as in SQLite version 2.X.
-The following routines are used to extract useful information from these
-"values":
-
-
-
- const void *sqlite3_value_blob(sqlite3_value*);
- int sqlite3_value_bytes(sqlite3_value*);
- int sqlite3_value_bytes16(sqlite3_value*);
- double sqlite3_value_double(sqlite3_value*);
- int sqlite3_value_int(sqlite3_value*);
- long long int sqlite3_value_int64(sqlite3_value*);
- const unsigned char *sqlite3_value_text(sqlite3_value*);
- const void *sqlite3_value_text16(sqlite3_value*);
- int sqlite3_value_type(sqlite3_value*);
-
-
-
-Function implementations use the following APIs to acquire context and
-to report results:
-
-
-
- void *sqlite3_aggregate_context(sqlite3_context*, int nbyte);
- void *sqlite3_user_data(sqlite3_context*);
- void sqlite3_result_blob(sqlite3_context*, const void*, int n, void(*)(void*));
- void sqlite3_result_double(sqlite3_context*, double);
- void sqlite3_result_error(sqlite3_context*, const char*, int);
- void sqlite3_result_error16(sqlite3_context*, const void*, int);
- void sqlite3_result_int(sqlite3_context*, int);
- void sqlite3_result_int64(sqlite3_context*, long long int);
- void sqlite3_result_null(sqlite3_context*);
- void sqlite3_result_text(sqlite3_context*, const char*, int n, void(*)(void*));
- void sqlite3_result_text16(sqlite3_context*, const void*, int n, void(*)(void*));
- void sqlite3_result_value(sqlite3_context*, sqlite3_value*);
- void *sqlite3_get_auxdata(sqlite3_context*, int);
- void sqlite3_set_auxdata(sqlite3_context*, int, void*, void (*)(void*));
-
-
-
2.4 User-defined collating sequences
-
-
-The following routines are used to implement user-defined
-collating sequences:
-
-The sqlite3_create_collation() function specifies a collating sequence name
-and a comparison function to implement that collating sequence. The
-comparison function is only used for comparing text values. The eTextRep
-parameter is one of SQLITE_UTF8, SQLITE_UTF16LE, SQLITE_UTF16BE, or
-SQLITE_ANY to specify which text representation the comparison function works
-with. Separate comparison functions can exist for the same collating
-sequence for each of the UTF-8, UTF-16LE and UTF-16BE text representations.
-The sqlite3_create_collation16() works like sqlite3_create_collation() except
-that the collation name is specified in UTF-16 host byte order instead of
-in UTF-8.
-
-
-
-The sqlite3_collation_needed() routine registers a callback which the
-database engine will invoke if it encounters an unknown collating sequence.
-The callback can lookup an appropriate comparison function and invoke
-sqlite_3_create_collation() as needed. The fourth parameter to the callback
-is the name of the collating sequence in UTF-8. For sqlite3_collation_need16()
-the callback sends the collating sequence name in UTF-16 host byte order.
-
-}
-
-proc api {name prototype desc {notused x}} {
- global apilist specialname
- if {$name==""} {
- regsub -all {sqlite3_[a-z0-9_]+\(} $prototype \
- {[lappend name [string trimright & (]]} x1
- subst $x1
- } else {
- lappend specialname $name
- }
- lappend apilist [list $name $prototype $desc]
-}
-
-api {extended-result-codes} {
-#define SQLITE_IOERR_READ
-#define SQLITE_IOERR_SHORT_READ
-#define SQLITE_IOERR_WRITE
-#define SQLITE_IOERR_FSYNC
-#define SQLITE_IOERR_DIR_FSYNC
-#define SQLITE_IOERR_TRUNCATE
-#define SQLITE_IOERR_FSTAT
-#define SQLITE_IOERR_UNLOCK
-#define SQLITE_IOERR_RDLOCK
-...
-} {
-In its default configuration, SQLite API routines return one of 26 integer
-result codes described at result-codes. However, experience has shown that
-many of these result codes are too course-grained. They do not provide as
-much information about problems as users might like. In an effort to
-address this, newer versions of SQLite (version 3.3.8 and later) include
-support for additional result codes that provide more detailed information
-about errors. The extended result codes are enabled (or disabled) for
-each database
-connection using the sqlite3_extended_result_codes() API.
-
-Some of the available extended result codes are listed above.
-We expect the number of extended result codes will be expand
-over time. Software that uses extended result codes should expect
-to see new result codes in future releases of SQLite.
-
-The symbolic name for an extended result code always contains a related
-primary result code as a prefix. Primary result codes contain a single
-"_" character. Extended result codes contain two or more "_" characters.
-The numeric value of an extended result code can be converted to its
-corresponding primary result code by masking off the lower 8 bytes.
-
-A complete list of available extended result codes and
-details about the meaning of the various extended result codes can be
-found by consulting the C code, especially the sqlite3.h header
-file and its antecedent sqlite.h.in. Additional information
-is also available at the SQLite wiki:
-http://www.sqlite.org/cvstrac/wiki?p=ExtendedResultCodes
-}
-
-
-api {result-codes} {
-#define SQLITE_OK 0 /* Successful result */
-#define SQLITE_ERROR 1 /* SQL error or missing database */
-#define SQLITE_INTERNAL 2 /* An internal logic error in SQLite */
-#define SQLITE_PERM 3 /* Access permission denied */
-#define SQLITE_ABORT 4 /* Callback routine requested an abort */
-#define SQLITE_BUSY 5 /* The database file is locked */
-#define SQLITE_LOCKED 6 /* A table in the database is locked */
-#define SQLITE_NOMEM 7 /* A malloc() failed */
-#define SQLITE_READONLY 8 /* Attempt to write a readonly database */
-#define SQLITE_INTERRUPT 9 /* Operation terminated by sqlite_interrupt() */
-#define SQLITE_IOERR 10 /* Some kind of disk I/O error occurred */
-#define SQLITE_CORRUPT 11 /* The database disk image is malformed */
-#define SQLITE_NOTFOUND 12 /* (Internal Only) Table or record not found */
-#define SQLITE_FULL 13 /* Insertion failed because database is full */
-#define SQLITE_CANTOPEN 14 /* Unable to open the database file */
-#define SQLITE_PROTOCOL 15 /* Database lock protocol error */
-#define SQLITE_EMPTY 16 /* (Internal Only) Database table is empty */
-#define SQLITE_SCHEMA 17 /* The database schema changed */
-#define SQLITE_TOOBIG 18 /* Too much data for one row of a table */
-#define SQLITE_CONSTRAINT 19 /* Abort due to constraint violation */
-#define SQLITE_MISMATCH 20 /* Data type mismatch */
-#define SQLITE_MISUSE 21 /* Library used incorrectly */
-#define SQLITE_NOLFS 22 /* Uses OS features not supported on host */
-#define SQLITE_AUTH 23 /* Authorization denied */
-#define SQLITE_ROW 100 /* sqlite_step() has another row ready */
-#define SQLITE_DONE 101 /* sqlite_step() has finished executing */
-} {
-Many SQLite functions return an integer result code from the set shown
-above in order to indicates success or failure.
-
-The result codes above are the only ones returned by SQLite in its
-default configuration. However, the sqlite3_extended_result_codes()
-API can be used to set a database connectoin to return more detailed
-result codes. See the documentation on sqlite3_extended_result_codes()
-or extended-result-codes for additional information.
-}
-
-api {} {
- int sqlite3_extended_result_codes(sqlite3*, int onoff);
-} {
-This routine enables or disabled extended-result-codes feature.
-By default, SQLite API routines return one of only 26 integer
-result codes described at result-codes. When extended result codes
-are enabled by this routine, the repetoire of result codes can be
-much larger and can (hopefully) provide more detailed information
-about the cause of an error.
-
-The second argument is a boolean value that turns extended result
-codes on and off. Extended result codes are off by default for
-backwards compatibility with older versions of SQLite.
-}
-
-api {} {
- const char *sqlite3_libversion(void);
-} {
- Return a pointer to a string which contains the version number of
- the library. The same string is available in the global
- variable named "sqlite3_version". This interface is provided since
- windows is unable to access global variables in DLLs.
-}
-
-api {} {
- void *sqlite3_aggregate_context(sqlite3_context*, int nBytes);
-} {
- Aggregate functions use this routine to allocate
- a structure for storing their state. The first time this routine
- is called for a particular aggregate, a new structure of size nBytes
- is allocated, zeroed, and returned. On subsequent calls (for the
- same aggregate instance) the same buffer is returned. The implementation
- of the aggregate can use the returned buffer to accumulate data.
-
- The buffer is freed automatically by SQLite when the query that
- invoked the aggregate function terminates.
-}
-
-api {} {
- int sqlite3_aggregate_count(sqlite3_context*);
-} {
- This function is deprecated. It continues to exist so as not to
- break any legacy code that might happen to use it. But it should not
- be used in any new code.
-
- In order to encourage people to not use this function, we are not going
- to tell you what it does.
-}
-
-api {} {
- int sqlite3_bind_blob(sqlite3_stmt*, int, const void*, int n, void(*)(void*));
- int sqlite3_bind_double(sqlite3_stmt*, int, double);
- int sqlite3_bind_int(sqlite3_stmt*, int, int);
- int sqlite3_bind_int64(sqlite3_stmt*, int, long long int);
- int sqlite3_bind_null(sqlite3_stmt*, int);
- int sqlite3_bind_text(sqlite3_stmt*, int, const char*, int n, void(*)(void*));
- int sqlite3_bind_text16(sqlite3_stmt*, int, const void*, int n, void(*)(void*));
- #define SQLITE_STATIC ((void(*)(void *))0)
- #define SQLITE_TRANSIENT ((void(*)(void *))-1)
-} {
- In the SQL strings input to sqlite3_prepare_v2() and sqlite3_prepare16_v2(),
- one or more literals can be replace by a parameter "?" or "?NNN"
- or ":AAA" or "@AAA" or "\$VVV" where NNN is an integer literal,
- AAA is an alphanumeric identifier and VVV is a variable name according
- to the syntax rules of the TCL programming language.
- The values of these parameters (also called "host parameter names")
- can be set using the sqlite3_bind_*() routines.
-
- The first argument to the sqlite3_bind_*() routines always is a pointer
- to the sqlite3_stmt structure returned from sqlite3_prepare_v2(). The second
- argument is the index of the parameter to be set. The first parameter has
- an index of 1. When the same named parameter is used more than once, second
- and subsequent
- occurrences have the same index as the first occurrence. The index for
- named parameters can be looked up using the
- sqlite3_bind_parameter_name() API if desired. The index for "?NNN"
- parametes is the value of NNN. The NNN value must be between 1 and 999.
-
-
- The third argument is the value to bind to the parameter.
-
- In those
- routines that have a fourth argument, its value is the number of bytes
- in the parameter. To be clear: the value is the number of bytes in the
- string, not the number of characters. The number
- of bytes does not include the zero-terminator at the end of strings.
- If the fourth parameter is negative, the length of the string is
- number of bytes up to the first zero terminator.
-
- The fifth argument to sqlite3_bind_blob(), sqlite3_bind_text(), and
- sqlite3_bind_text16() is a destructor used to dispose of the BLOB or
- text after SQLite has finished with it. If the fifth argument is the
- special value SQLITE_STATIC, then the library assumes that the information
- is in static, unmanaged space and does not need to be freed. If the
- fifth argument has the value SQLITE_TRANSIENT, then SQLite makes its
- own private copy of the data immediately, before the sqlite3_bind_*()
- routine returns.
-
- The sqlite3_bind_*() routines must be called after
- sqlite3_prepare_v2() or sqlite3_reset() and before sqlite3_step().
- Bindings are not cleared by the sqlite3_reset() routine.
- Unbound parameters are interpreted as NULL.
-
- These routines return SQLITE_OK on success or an error code if
- anything goes wrong. SQLITE_RANGE is returned if the parameter
- index is out of range. SQLITE_NOMEM is returned if malloc fails.
- SQLITE_MISUSE is returned if these routines are called on a virtual
- machine that is the wrong state or which has already been finalized.
-}
-
-api {} {
- int sqlite3_bind_parameter_count(sqlite3_stmt*);
-} {
- Return the number of parameters in the precompiled statement given as
- the argument.
-}
-
-api {} {
- const char *sqlite3_bind_parameter_name(sqlite3_stmt*, int n);
-} {
- Return the name of the n-th parameter in the precompiled statement.
- Parameters of the form ":AAA" or "@AAA" or "\$VVV" have a name which is the
- string ":AAA" or "@AAA" or "\$VVV".
- In other words, the initial ":" or "$" or "@"
- is included as part of the name.
- Parameters of the form "?" or "?NNN" have no name.
-
- The first bound parameter has an index of 1, not 0.
-
- If the value n is out of range or if the n-th parameter is nameless,
- then NULL is returned. The returned string is always in the
- UTF-8 encoding even if the named parameter was originally specified
- as UTF-16 in sqlite3_prepare16_v2().
-}
-
-api {} {
- int sqlite3_bind_parameter_index(sqlite3_stmt*, const char *zName);
-} {
- Return the index of the parameter with the given name.
- The name must match exactly.
- If there is no parameter with the given name, return 0.
- The string zName is always in the UTF-8 encoding.
-}
-
-api {} {
- int sqlite3_busy_handler(sqlite3*, int(*)(void*,int), void*);
-} {
- This routine identifies a callback function that might be invoked
- whenever an attempt is made to open a database table
- that another thread or process has locked.
- If the busy callback is NULL, then SQLITE_BUSY is returned immediately
- upon encountering the lock.
- If the busy callback is not NULL, then the
- callback will be invoked with two arguments. The
- first argument to the handler is a copy of the void* pointer which
- is the third argument to this routine. The second argument to
- the handler is the number of times that the busy handler has
- been invoked for this locking event. If the
- busy callback returns 0, then no additional attempts are made to
- access the database and SQLITE_BUSY is returned.
- If the callback returns non-zero, then another attempt is made to open the
- database for reading and the cycle repeats.
-
- The presence of a busy handler does not guarantee that
- it will be invoked when there is lock contention.
- If SQLite determines that invoking the busy handler could result in
- a deadlock, it will return SQLITE_BUSY instead.
- Consider a scenario where one process is holding a read lock that
- it is trying to promote to a reserved lock and
- a second process is holding a reserved lock that it is trying
- to promote to an exclusive lock. The first process cannot proceed
- because it is blocked by the second and the second process cannot
- proceed because it is blocked by the first. If both processes
- invoke the busy handlers, neither will make any progress. Therefore,
- SQLite returns SQLITE_BUSY for the first process, hoping that this
- will induce the first process to release its read lock and allow
- the second process to proceed.
-
- The default busy callback is NULL.
-
- Sqlite is re-entrant, so the busy handler may start a new query.
- (It is not clear why anyone would every want to do this, but it
- is allowed, in theory.) But the busy handler may not close the
- database. Closing the database from a busy handler will delete
- data structures out from under the executing query and will
- probably result in a coredump.
-
- There can only be a single busy handler defined for each database
- connection. Setting a new busy handler clears any previous one.
- Note that calling sqlite3_busy_timeout() will also set or clear
- the busy handler.
-}
-
-api {} {
- int sqlite3_busy_timeout(sqlite3*, int ms);
-} {
- This routine sets a busy handler that sleeps for a while when a
- table is locked. The handler will sleep multiple times until
- at least "ms" milliseconds of sleeping have been done. After
- "ms" milliseconds of sleeping, the handler returns 0 which
- causes sqlite3_exec() to return SQLITE_BUSY.
-
- Calling this routine with an argument less than or equal to zero
- turns off all busy handlers.
-
- There can only be a single busy handler for a particular database
- connection. If another busy handler was defined
- (using sqlite3_busy_handler()) prior to calling
- this routine, that other busy handler is cleared.
-}
-
-api {} {
- int sqlite3_changes(sqlite3*);
-} {
- This function returns the number of database rows that were changed
- (or inserted or deleted) by the most recently completed
- INSERT, UPDATE, or DELETE
- statement. Only changes that are directly specified by the INSERT,
- UPDATE, or DELETE statement are counted. Auxiliary changes caused by
- triggers are not counted. Use the sqlite3_total_changes() function
- to find the total number of changes including changes caused by triggers.
-
- Within the body of a trigger, the sqlite3_changes() function does work
- to report the number of rows that were changed for the most recently
- completed INSERT, UPDATE, or DELETE statement within the trigger body.
-
- SQLite implements the command "DELETE FROM table" without a WHERE clause
- by dropping and recreating the table. (This is much faster than going
- through and deleting individual elements from the table.) Because of
- this optimization, the change count for "DELETE FROM table" will be
- zero regardless of the number of elements that were originally in the
- table. To get an accurate count of the number of rows deleted, use
- "DELETE FROM table WHERE 1" instead.
-}
-
-api {} {
- int sqlite3_total_changes(sqlite3*);
-} {
- This function returns the total number of database rows that have
- be modified, inserted, or deleted since the database connection was
- created using sqlite3_open(). All changes are counted, including
- changes by triggers and changes to TEMP and auxiliary databases.
- Except, changes to the SQLITE_MASTER table (caused by statements
- such as CREATE TABLE) are not counted. Nor are changes counted when
- an entire table is deleted using DROP TABLE.
-
- See also the sqlite3_changes() API.
-
- SQLite implements the command "DELETE FROM table" without a WHERE clause
- by dropping and recreating the table. (This is much faster than going
- through and deleting individual elements form the table.) Because of
- this optimization, the change count for "DELETE FROM table" will be
- zero regardless of the number of elements that were originally in the
- table. To get an accurate count of the number of rows deleted, use
- "DELETE FROM table WHERE 1" instead.
-}
-
-api {} {
- int sqlite3_close(sqlite3*);
-} {
- Call this function with a pointer to a structure that was previously
- returned from sqlite3_open() or sqlite3_open16()
- and the corresponding database will by closed.
-
- SQLITE_OK is returned if the close is successful. If there are
- prepared statements that have not been finalized, then SQLITE_BUSY
- is returned. SQLITE_ERROR might be returned if the argument is not
- a valid connection pointer returned by sqlite3_open() or if the connection
- pointer has been closed previously.
-}
-
-api {} {
-const void *sqlite3_column_blob(sqlite3_stmt*, int iCol);
-int sqlite3_column_bytes(sqlite3_stmt*, int iCol);
-int sqlite3_column_bytes16(sqlite3_stmt*, int iCol);
-double sqlite3_column_double(sqlite3_stmt*, int iCol);
-int sqlite3_column_int(sqlite3_stmt*, int iCol);
-long long int sqlite3_column_int64(sqlite3_stmt*, int iCol);
-const unsigned char *sqlite3_column_text(sqlite3_stmt*, int iCol);
-const void *sqlite3_column_text16(sqlite3_stmt*, int iCol);
-int sqlite3_column_type(sqlite3_stmt*, int iCol);
-#define SQLITE_INTEGER 1
-#define SQLITE_FLOAT 2
-#define SQLITE_TEXT 3
-#define SQLITE_BLOB 4
-#define SQLITE_NULL 5
-} {
- These routines return information about the information
- in a single column of the current result row of a query. In every
- case the first argument is a pointer to the SQL statement that is being
- executed (the sqlite_stmt* that was returned from sqlite3_prepare_v2()) and
- the second argument is the index of the column for which information
- should be returned. iCol is zero-indexed. The left-most column has an
- index of 0.
-
- If the SQL statement is not currently point to a valid row, or if the
- the column index is out of range, the result is undefined.
-
- The sqlite3_column_type() routine returns the initial data type
- of the result column. The returned value is one of SQLITE_INTEGER,
- SQLITE_FLOAT, SQLITE_TEXT, SQLITE_BLOB, or SQLITE_NULL. The value
- returned by sqlite3_column_type() is only meaningful if no type
- conversions have occurred as described below. After a type conversion,
- the value returned by sqlite3_column_type() is undefined. Future
- versions of SQLite may change the behavior of sqlite3_column_type()
- following a type conversion.
-
- If the result is a BLOB or UTF-8 string then the sqlite3_column_bytes()
- routine returns the number of bytes in that BLOB or string.
- If the result is a UTF-16 string, then sqlite3_column_bytes() converts
- the string to UTF-8 and then returns the number of bytes.
- If the result is a numeric value then sqlite3_column_bytes() uses
- sqlite3_snprintf() to convert that value to a UTF-8 string and returns
- the number of bytes in that string.
- The value returned does
- not include the \\000 terminator at the end of the string.
-
- The sqlite3_column_bytes16() routine is similar to sqlite3_column_bytes()
- but leaves the result in UTF-16 instead of UTF-8.
- The \\u0000 terminator is not included in this count.
-
- These routines attempt to convert the value where appropriate. For
- example, if the internal representation is FLOAT and a text result
- is requested, sqlite3_snprintf() is used internally to do the conversion
- automatically. The following table details the conversions that
- are applied:
-
-
-
-
Internal Type
Requested Type
Conversion
-
NULL
INTEGER
Result is 0
-
NULL
FLOAT
Result is 0.0
-
NULL
TEXT
Result is NULL pointer
-
NULL
BLOB
Result is NULL pointer
-
INTEGER
FLOAT
Convert from integer to float
-
INTEGER
TEXT
ASCII rendering of the integer
-
INTEGER
BLOB
Same as for INTEGER->TEXT
-
FLOAT
INTEGER
Convert from float to integer
-
FLOAT
TEXT
ASCII rendering of the float
-
FLOAT
BLOB
Same as FLOAT->TEXT
-
TEXT
INTEGER
Use atoi()
-
TEXT
FLOAT
Use atof()
-
TEXT
BLOB
No change
-
BLOB
INTEGER
Convert to TEXT then use atoi()
-
BLOB
FLOAT
Convert to TEXT then use atof()
-
BLOB
TEXT
Add a \\000 terminator if needed
-
-
-
- Note that when type conversions occur, pointers returned by prior
- calls to sqlite3_column_blob(), sqlite3_column_text(), and/or
- sqlite3_column_text16() may be invalidated.
- Type conversions and pointer invalidations might occur
- in the following cases:
-
-
-
- The initial content is a BLOB and sqlite3_column_text()
- or sqlite3_column_text16()
- is called. A zero-terminator might need to be added to the string.
-
-
- The initial content is UTF-8 text and sqlite3_column_bytes16() or
- sqlite3_column_text16() is called. The content must be converted to UTF-16.
-
-
- The initial content is UTF-16 text and sqlite3_column_bytes() or
- sqlite3_column_text() is called. The content must be converted to UTF-8.
-
-
-
- Conversions between UTF-16be and UTF-16le
- are always done in place and do
- not invalidate a prior pointer, though of course the content of the buffer
- that the prior pointer points to will have been modified. Other kinds
- of conversion are done in place when it is possible, but sometime it is
- not possible and in those cases prior pointers are invalidated.
-
- The safest and easiest to remember policy is to invoke these routines
- in one of the following ways:
-
-
-
sqlite3_column_text() followed by sqlite3_column_bytes()
-
sqlite3_column_blob() followed by sqlite3_column_bytes()
-
sqlite3_column_text16() followed by sqlite3_column_bytes16()
-
-
- In other words, you should call sqlite3_column_text(), sqlite3_column_blob(),
- or sqlite3_column_text16() first to force the result into the desired
- format, then invoke sqlite3_column_bytes() or sqlite3_column_bytes16() to
- find the size of the result. Do not mix call to sqlite3_column_text() or
- sqlite3_column_blob() with calls to sqlite3_column_bytes16(). And do not
- mix calls to sqlite3_column_text16() with calls to sqlite3_column_bytes().
-}
-
-api {} {
-int sqlite3_column_count(sqlite3_stmt *pStmt);
-} {
- Return the number of columns in the result set returned by the prepared
- SQL statement. This routine returns 0 if pStmt is an SQL statement
- that does not return data (for example an UPDATE).
-
- See also sqlite3_data_count().
-}
-
-api {} {
-const char *sqlite3_column_decltype(sqlite3_stmt *, int i);
-const void *sqlite3_column_decltype16(sqlite3_stmt*,int);
-} {
- The first argument is a prepared SQL statement. If this statement
- is a SELECT statement, the Nth column of the returned result set
- of the SELECT is a table column then the declared type of the table
- column is returned. If the Nth column of the result set is not a table
- column, then a NULL pointer is returned. The returned string is
- UTF-8 encoded for sqlite3_column_decltype() and UTF-16 encoded
- for sqlite3_column_decltype16(). For example, in the database schema:
-
-
- CREATE TABLE t1(c1 INTEGER);
-
-
- And the following statement compiled:
-
-
- SELECT c1 + 1, c1 FROM t1;
-
-
- Then this routine would return the string "INTEGER" for the second
- result column (i==1), and a NULL pointer for the first result column
- (i==0).
-
- If the following statements were compiled then this routine would
- return "INTEGER" for the first (only) result column.
-
-
- SELECT (SELECT c1) FROM t1;
- SELECT (SELECT c1 FROM t1);
- SELECT c1 FROM (SELECT c1 FROM t1);
- SELECT * FROM (SELECT c1 FROM t1);
- SELECT * FROM (SELECT * FROM t1);
-
-}
-
-api {} {
- int sqlite3_table_column_metadata(
- sqlite3 *db, /* Connection handle */
- const char *zDbName, /* Database name or NULL */
- const char *zTableName, /* Table name */
- const char *zColumnName, /* Column name */
- char const **pzDataType, /* OUTPUT: Declared data type */
- char const **pzCollSeq, /* OUTPUT: Collation sequence name */
- int *pNotNull, /* OUTPUT: True if NOT NULL constraint exists */
- int *pPrimaryKey, /* OUTPUT: True if column part of PK */
- int *pAutoinc /* OUTPUT: True if colums is auto-increment */
- );
-} {
- This routine is used to obtain meta information about a specific column of a
- specific database table accessible using the connection handle passed as the
- first function argument.
-
- The column is identified by the second, third and fourth parameters to
- this function. The second parameter is either the name of the database
- (i.e. "main", "temp" or an attached database) containing the specified
- table or NULL. If it is NULL, then all attached databases are searched
- for the table using the same algorithm as the database engine uses to
- resolve unqualified table references.
-
- The third and fourth parameters to this function are the table and column
- name of the desired column, respectively. Neither of these parameters
- may be NULL.
-
- Meta information is returned by writing to the memory locations passed as
- the 5th and subsequent parameters to this function. Any of these
- arguments may be NULL, in which case the corresponding element of meta
- information is ommitted.
-
-
- Parameter Output Type Description
- -----------------------------------
- 5th const char* Declared data type
- 6th const char* Name of the columns default collation sequence
- 7th int True if the column has a NOT NULL constraint
- 8th int True if the column is part of the PRIMARY KEY
- 9th int True if the column is AUTOINCREMENT
-
-
- The memory pointed to by the character pointers returned for the
- declaration type and collation sequence is valid only until the next
- call to any sqlite API function.
-
- This function may load one or more schemas from database files. If an
- error occurs during this process, or if the requested table or column
- cannot be found, an SQLITE error code is returned and an error message
- left in the database handle (to be retrieved using sqlite3_errmsg()).
- Specifying an SQL view instead of a table as the third argument is also
- considered an error.
-
- If the specified column is "rowid", "oid" or "_rowid_" and an
- INTEGER PRIMARY KEY column has been explicitly declared, then the output
- parameters are set for the explicitly declared column. If there is no
- explicitly declared IPK column, then the data-type is "INTEGER", the
- collation sequence "BINARY" and the primary-key flag is set. Both
- the not-null and auto-increment flags are clear.
-
- This API is only available if the library was compiled with the
- SQLITE_ENABLE_COLUMN_METADATA preprocessor symbol defined.
-}
-
-api {} {
-const char *sqlite3_column_database_name(sqlite3_stmt *pStmt, int N);
-const void *sqlite3_column_database_name16(sqlite3_stmt *pStmt, int N);
-} {
-If the Nth column returned by statement pStmt is a column reference,
-these functions may be used to access the name of the database (either
-"main", "temp" or the name of an attached database) that contains
-the column. If the Nth column is not a column reference, NULL is
-returned.
-
-See the description of function sqlite3_column_decltype() for a
-description of exactly which expressions are considered column references.
-
-Function sqlite3_column_database_name() returns a pointer to a UTF-8
-encoded string. sqlite3_column_database_name16() returns a pointer
-to a UTF-16 encoded string.
-}
-
-api {} {
-const char *sqlite3_column_origin_name(sqlite3_stmt *pStmt, int N);
-const void *sqlite3_column_origin_name16(sqlite3_stmt *pStmt, int N);
-} {
-If the Nth column returned by statement pStmt is a column reference,
-these functions may be used to access the schema name of the referenced
-column in the database schema. If the Nth column is not a column
-reference, NULL is returned.
-
-See the description of function sqlite3_column_decltype() for a
-description of exactly which expressions are considered column references.
-
-Function sqlite3_column_origin_name() returns a pointer to a UTF-8
-encoded string. sqlite3_column_origin_name16() returns a pointer
-to a UTF-16 encoded string.
-}
-
-api {} {
-const char *sqlite3_column_table_name(sqlite3_stmt *pStmt, int N);
-const void *sqlite3_column_table_name16(sqlite3_stmt *pStmt, int N);
-} {
-If the Nth column returned by statement pStmt is a column reference,
-these functions may be used to access the name of the table that
-contains the column. If the Nth column is not a column reference,
-NULL is returned.
-
-See the description of function sqlite3_column_decltype() for a
-description of exactly which expressions are considered column references.
-
-Function sqlite3_column_table_name() returns a pointer to a UTF-8
-encoded string. sqlite3_column_table_name16() returns a pointer
-to a UTF-16 encoded string.
-}
-
-api {} {
-const char *sqlite3_column_name(sqlite3_stmt*,int);
-const void *sqlite3_column_name16(sqlite3_stmt*,int);
-} {
- The first argument is a prepared SQL statement. This function returns
- the column heading for the Nth column of that statement, where N is the
- second function argument. The string returned is UTF-8 for
- sqlite3_column_name() and UTF-16 for sqlite3_column_name16().
-}
-
-api {} {
-void *sqlite3_commit_hook(sqlite3*, int(*xCallback)(void*), void *pArg);
-} {
- Experimental
-
- Register a callback function to be invoked whenever a new transaction
- is committed. The pArg argument is passed through to the callback.
- callback. If the callback function returns non-zero, then the commit
- is converted into a rollback.
-
- If another function was previously registered, its pArg value is returned.
- Otherwise NULL is returned.
-
- Registering a NULL function disables the callback. Only a single commit
- hook callback can be registered at a time.
-}
-
-api {} {
-int sqlite3_complete(const char *sql);
-int sqlite3_complete16(const void *sql);
-} {
- These functions return true if the given input string comprises
- one or more complete SQL statements.
- The argument must be a nul-terminated UTF-8 string for sqlite3_complete()
- and a nul-terminated UTF-16 string for sqlite3_complete16().
-
- These routines do not check to see if the SQL statement is well-formed.
- They only check to see that the statement is terminated by a semicolon
- that is not part of a string literal and is not inside
- the body of a trigger.
-} {}
-
-api {} {
-int sqlite3_create_collation(
- sqlite3*,
- const char *zName,
- int pref16,
- void*,
- int(*xCompare)(void*,int,const void*,int,const void*)
-);
-int sqlite3_create_collation16(
- sqlite3*,
- const char *zName,
- int pref16,
- void*,
- int(*xCompare)(void*,int,const void*,int,const void*)
-);
-#define SQLITE_UTF8 1
-#define SQLITE_UTF16BE 2
-#define SQLITE_UTF16LE 3
-#define SQLITE_UTF16 4
-} {
- These two functions are used to add new collation sequences to the
- sqlite3 handle specified as the first argument.
-
- The name of the new collation sequence is specified as a UTF-8 string
- for sqlite3_create_collation() and a UTF-16 string for
- sqlite3_create_collation16(). In both cases the name is passed as the
- second function argument.
-
- The third argument must be one of the constants SQLITE_UTF8,
- SQLITE_UTF16LE or SQLITE_UTF16BE, indicating that the user-supplied
- routine expects to be passed pointers to strings encoded using UTF-8,
- UTF-16 little-endian or UTF-16 big-endian respectively. The
- SQLITE_UTF16 constant indicates that text strings are expected in
- UTF-16 in the native byte order of the host machine.
-
- A pointer to the user supplied routine must be passed as the fifth
- argument. If it is NULL, this is the same as deleting the collation
- sequence (so that SQLite cannot call it anymore). Each time the user
- supplied function is invoked, it is passed a copy of the void* passed as
- the fourth argument to sqlite3_create_collation() or
- sqlite3_create_collation16() as its first argument.
-
- The remaining arguments to the user-supplied routine are two strings,
- each represented by a [length, data] pair and encoded in the encoding
- that was passed as the third argument when the collation sequence was
- registered. The user routine should return negative, zero or positive if
- the first string is less than, equal to, or greater than the second
- string. i.e. (STRING1 - STRING2).
-}
-
-api {} {
-int sqlite3_collation_needed(
- sqlite3*,
- void*,
- void(*)(void*,sqlite3*,int eTextRep,const char*)
-);
-int sqlite3_collation_needed16(
- sqlite3*,
- void*,
- void(*)(void*,sqlite3*,int eTextRep,const void*)
-);
-} {
- To avoid having to register all collation sequences before a database
- can be used, a single callback function may be registered with the
- database handle to be called whenever an undefined collation sequence is
- required.
-
- If the function is registered using the sqlite3_collation_needed() API,
- then it is passed the names of undefined collation sequences as strings
- encoded in UTF-8. If sqlite3_collation_needed16() is used, the names
- are passed as UTF-16 in machine native byte order. A call to either
- function replaces any existing callback.
-
- When the user-function is invoked, the first argument passed is a copy
- of the second argument to sqlite3_collation_needed() or
- sqlite3_collation_needed16(). The second argument is the database
- handle. The third argument is one of SQLITE_UTF8, SQLITE_UTF16BE or
- SQLITE_UTF16LE, indicating the most desirable form of the collation
- sequence function required. The fourth argument is the name of the
- required collation sequence.
-
- The collation sequence is returned to SQLite by a collation-needed
- callback using the sqlite3_create_collation() or
- sqlite3_create_collation16() APIs, described above.
-}
-
-api {} {
-int sqlite3_create_function(
- sqlite3 *,
- const char *zFunctionName,
- int nArg,
- int eTextRep,
- void *pUserData,
- void (*xFunc)(sqlite3_context*,int,sqlite3_value**),
- void (*xStep)(sqlite3_context*,int,sqlite3_value**),
- void (*xFinal)(sqlite3_context*)
-);
-int sqlite3_create_function16(
- sqlite3*,
- const void *zFunctionName,
- int nArg,
- int eTextRep,
- void *pUserData,
- void (*xFunc)(sqlite3_context*,int,sqlite3_value**),
- void (*xStep)(sqlite3_context*,int,sqlite3_value**),
- void (*xFinal)(sqlite3_context*)
-);
-#define SQLITE_UTF8 1
-#define SQLITE_UTF16 2
-#define SQLITE_UTF16BE 3
-#define SQLITE_UTF16LE 4
-#define SQLITE_ANY 5
-} {
- These two functions are used to add SQL functions or aggregates
- implemented in C. The
- only difference between these two routines is that the second argument, the
- name of the (scalar) function or aggregate, is encoded in UTF-8 for
- sqlite3_create_function() and UTF-16 for sqlite3_create_function16().
- The length of the name is limited to 255 bytes, exclusive of the
- zero-terminator. Note that the name length limit is in bytes, not
- characters. Any attempt to create a function with a longer name
- will result in an SQLITE_ERROR error.
-
- The first argument is the database handle that the new function or
- aggregate is to be added to. If a single program uses more than one
- database handle internally, then user functions or aggregates must
- be added individually to each database handle with which they will be
- used.
-
- The third argument is the number of arguments that the function or
- aggregate takes. If this argument is -1 then the function or
- aggregate may take any number of arguments. The maximum number
- of arguments to a new SQL function is 127. A number larger than
- 127 for the third argument results in an SQLITE_ERROR error.
-
- The fourth argument, eTextRep, specifies what type of text arguments
- this function prefers to receive. Any function should be able to work
- work with UTF-8, UTF-16le, or UTF-16be. But some implementations may be
- more efficient with one representation than another. Users are allowed
- to specify separate implementations for the same function which are called
- depending on the text representation of the arguments. The the implementation
- which provides the best match is used. If there is only a single
- implementation which does not care what text representation is used,
- then the fourth argument should be SQLITE_ANY.
-
- The fifth argument is an arbitrary pointer. The function implementations
- can gain access to this pointer using the sqlite_user_data() API.
-
- The sixth, seventh and eighth argumens, xFunc, xStep and xFinal, are
- pointers to user implemented C functions that implement the user
- function or aggregate. A scalar function requires an implementation of
- the xFunc callback only, NULL pointers should be passed as the xStep
- and xFinal arguments. An aggregate function requires an implementation
- of xStep and xFinal, and NULL should be passed for xFunc. To delete an
- existing user function or aggregate, pass NULL for all three function
- callbacks. Specifying an inconstant set of callback values, such as an
- xFunc and an xFinal, or an xStep but no xFinal, results in an SQLITE_ERROR
- return.
-}
-
-api {} {
-int sqlite3_data_count(sqlite3_stmt *pStmt);
-} {
- Return the number of values in the current row of the result set.
-
- After a call to sqlite3_step() that returns SQLITE_ROW, this routine
- will return the same value as the sqlite3_column_count() function.
- After sqlite3_step() has returned an SQLITE_DONE, SQLITE_BUSY or
- error code, or before sqlite3_step() has been called on a
- prepared SQL statement, this routine returns zero.
-}
-
-api {} {
-int sqlite3_errcode(sqlite3 *db);
-} {
- Return the error code for the most recent failed sqlite3_* API call associated
- with sqlite3 handle 'db'. If a prior API call failed but the most recent
- API call succeeded, the return value from this routine is undefined.
-
- Calls to many sqlite3_* functions set the error code and string returned
- by sqlite3_errcode(), sqlite3_errmsg() and sqlite3_errmsg16()
- (overwriting the previous values). Note that calls to sqlite3_errcode(),
- sqlite3_errmsg() and sqlite3_errmsg16() themselves do not affect the
- results of future invocations. Calls to API routines that do not return
- an error code (examples: sqlite3_data_count() or sqlite3_mprintf()) do
- not change the error code returned by this routine.
-
- Assuming no other intervening sqlite3_* API calls are made, the error
- code returned by this function is associated with the same error as
- the strings returned by sqlite3_errmsg() and sqlite3_errmsg16().
-} {}
-
-api {} {
-const char *sqlite3_errmsg(sqlite3*);
-const void *sqlite3_errmsg16(sqlite3*);
-} {
- Return a pointer to a UTF-8 encoded string (sqlite3_errmsg)
- or a UTF-16 encoded string (sqlite3_errmsg16) describing in English the
- error condition for the most recent sqlite3_* API call. The returned
- string is always terminated by an 0x00 byte.
-
- The string "not an error" is returned when the most recent API call was
- successful.
-}
-
-api {} {
-int sqlite3_exec(
- sqlite3*, /* An open database */
- const char *sql, /* SQL to be executed */
- sqlite_callback, /* Callback function */
- void *, /* 1st argument to callback function */
- char **errmsg /* Error msg written here */
-);
-} {
- A function to executes one or more statements of SQL.
-
- If one or more of the SQL statements are queries, then
- the callback function specified by the 3rd argument is
- invoked once for each row of the query result. This callback
- should normally return 0. If the callback returns a non-zero
- value then the query is aborted, all subsequent SQL statements
- are skipped and the sqlite3_exec() function returns the SQLITE_ABORT.
-
- The 1st argument is an arbitrary pointer that is passed
- to the callback function as its first argument.
-
- The 2nd argument to the callback function is the number of
- columns in the query result. The 3rd argument to the callback
- is an array of strings holding the values for each column.
- The 4th argument to the callback is an array of strings holding
- the names of each column.
-
- The callback function may be NULL, even for queries. A NULL
- callback is not an error. It just means that no callback
- will be invoked.
-
- If an error occurs while parsing or evaluating the SQL (but
- not while executing the callback) then an appropriate error
- message is written into memory obtained from malloc() and
- *errmsg is made to point to that message. The calling function
- is responsible for freeing the memory that holds the error
- message. Use sqlite3_free() for this. If errmsg==NULL,
- then no error message is ever written.
-
- The return value is is SQLITE_OK if there are no errors and
- some other return code if there is an error. The particular
- return value depends on the type of error.
-
- If the query could not be executed because a database file is
- locked or busy, then this function returns SQLITE_BUSY. (This
- behavior can be modified somewhat using the sqlite3_busy_handler()
- and sqlite3_busy_timeout() functions.)
-} {}
-
-api {} {
-int sqlite3_finalize(sqlite3_stmt *pStmt);
-} {
- The sqlite3_finalize() function is called to delete a prepared
- SQL statement obtained by a previous call to sqlite3_prepare(),
- sqlite3_prepare_v2(), sqlite3_prepare16(), or sqlite3_prepare16_v2().
- If the statement was executed successfully, or
- not executed at all, then SQLITE_OK is returned. If execution of the
- statement failed then an error code is returned.
-
- After sqlite_finalize() has been called, the statement handle is
- invalidated. Passing it to any other SQLite function may cause a
- crash.
-
- All prepared statements must finalized before sqlite3_close() is
- called or else the close will fail with a return code of SQLITE_BUSY.
-
- This routine can be called at any point during the execution of the
- virtual machine. If the virtual machine has not completed execution
- when this routine is called, that is like encountering an error or
- an interrupt. (See sqlite3_interrupt().) Incomplete updates may be
- rolled back and transactions canceled, depending on the circumstances,
- and the result code returned will be SQLITE_ABORT.
-}
-
-api {} {
-void *sqlite3_malloc(int);
-void *sqlite3_realloc(void*, int);
-void sqlite3_free(void*);
-} {
- These routines provide access to the memory allocator used by SQLite.
- Depending on how SQLite has been compiled and the OS-layer backend,
- the memory allocator used by SQLite might be the standard system
- malloc()/realloc()/free(), or it might be something different. With
- certain compile-time flags, SQLite will add wrapper logic around the
- memory allocator to add memory leak and buffer overrun detection. The
- OS layer might substitute a completely different memory allocator.
- Use these APIs to be sure you are always using the correct memory
- allocator.
-
- The sqlite3_free() API, not the standard free() from the system library,
- should always be used to free the memory buffer returned by
- sqlite3_mprintf() or sqlite3_vmprintf() and to free the error message
- string returned by sqlite3_exec(). Using free() instead of sqlite3_free()
- might accidentally work on some systems and build configurations but
- will fail on others.
-
- Compatibility Note: Prior to version 3.4.0, the sqlite3_free API
- was prototyped to take a char* parameter rather than
- void*. Like this:
-
-void sqlite3_free(char*);
-
- The change to using void* might cause warnings when
- compiling older code against
- newer libraries, but everything should still work correctly.
-}
-
-api {} {
-int sqlite3_get_table(
- sqlite3*, /* An open database */
- const char *sql, /* SQL to be executed */
- char ***resultp, /* Result written to a char *[] that this points to */
- int *nrow, /* Number of result rows written here */
- int *ncolumn, /* Number of result columns written here */
- char **errmsg /* Error msg written here */
-);
-void sqlite3_free_table(char **result);
-} {
- This next routine is really just a wrapper around sqlite3_exec().
- Instead of invoking a user-supplied callback for each row of the
- result, this routine remembers each row of the result in memory
- obtained from malloc(), then returns all of the result after the
- query has finished.
-
- As an example, suppose the query result where this table:
-
-
- Name | Age
- -----------------------
- Alice | 43
- Bob | 28
- Cindy | 21
-
-
- If the 3rd argument were &azResult then after the function returns
- azResult will contain the following data:
-
-
-
- Notice that there is an extra row of data containing the column
- headers. But the *nrow return value is still 3. *ncolumn is
- set to 2. In general, the number of values inserted into azResult
- will be ((*nrow) + 1)*(*ncolumn).
-
- After the calling function has finished using the result, it should
- pass the result data pointer to sqlite3_free_table() in order to
- release the memory that was malloc-ed. Because of the way the
- malloc() happens, the calling function must not try to call
- malloc() directly. Only sqlite3_free_table() is able to release
- the memory properly and safely.
-
- The return value of this routine is the same as from sqlite3_exec().
-}
-
-api {sqlite3_interrupt} {
- void sqlite3_interrupt(sqlite3*);
-} {
- This function causes any pending database operation to abort and
- return at its earliest opportunity. This routine is typically
- called in response to a user action such as pressing "Cancel"
- or Ctrl-C where the user wants a long query operation to halt
- immediately.
-} {}
-
-api {} {
-long long int sqlite3_last_insert_rowid(sqlite3*);
-} {
- Each entry in an SQLite table has a unique integer key called the "rowid".
- The rowid is always available as an undeclared column
- named ROWID, OID, or _ROWID_.
- If the table has a column of type INTEGER PRIMARY KEY then that column
- is another an alias for the rowid.
-
- This routine
- returns the rowid of the most recent INSERT into the database
- from the database connection given in the first argument. If
- no inserts have ever occurred on this database connection, zero
- is returned.
-
- If an INSERT occurs within a trigger, then the rowid of the
- inserted row is returned by this routine as long as the trigger
- is running. But once the trigger terminates, the value returned
- by this routine reverts to the last value inserted before the
- trigger fired.
-} {}
-
-api {} {
-char *sqlite3_mprintf(const char*,...);
-char *sqlite3_vmprintf(const char*, va_list);
-} {
- These routines are variants of the "sprintf()" from the
- standard C library. The resulting string is written into memory
- obtained from malloc() so that there is never a possibility of buffer
- overflow. These routines also implement some additional formatting
- options that are useful for constructing SQL statements.
-
- The strings returned by these routines should be freed by calling
- sqlite3_free().
-
- All of the usual printf formatting options apply. In addition, there
- is a "%q" option. %q works like %s in that it substitutes a null-terminated
- string from the argument list. But %q also doubles every '\\'' character.
- %q is designed for use inside a string literal. By doubling each '\\''
- character it escapes that character and allows it to be inserted into
- the string.
-
- For example, so some string variable contains text as follows:
-
-
- char *zText = "It's a happy day!";
-
-
- One can use this text in an SQL statement as follows:
-
-
-
- Because the %q format string is used, the '\\'' character in zText
- is escaped and the SQL generated is as follows:
-
-
- INSERT INTO table1 VALUES('It''s a happy day!')
-
-
- This is correct. Had we used %s instead of %q, the generated SQL
- would have looked like this:
-
-
- INSERT INTO table1 VALUES('It's a happy day!');
-
-
- This second example is an SQL syntax error. As a general rule you
- should always use %q instead of %s when inserting text into a string
- literal.
-} {}
-
-api {} {
-char *sqlite3_snprintf(int bufSize, char *buf, const char *zFormat, ...);
-} {
- This routine works like "sprintf()", writing a formatted string into
- the buf[]. However, no more than bufSize characters will be written
- into buf[]. This routine returns a pointer to buf[]. If bufSize is
- greater than zero, then buf[] is guaranteed to be zero-terminated.
-
- This routine uses the same extended formatting options as
- sqlite3_mprintf() and sqlite3_vmprintf().
-
- Note these differences with the snprintf() function found in many
- standard libraries: (1) sqlite3_snprintf() returns a pointer to the
- buffer rather than the number of characters written. (It would,
- arguably, be more useful to return the number of characters written,
- but we discovered that after the interface had been published and
- are unwilling to break backwards compatibility.) (2) The order
- of the bufSize and buf parameter is reversed from snprintf().
- And (3) sqlite3_snprintf() always writes a zero-terminator if bufSize
- is positive.
-
- Please do not use the return value of this routine. We may
- decide to make the minor compatibility break and change this routine
- to return the number of characters written rather than a pointer to
- the buffer in a future minor version increment.
-}
-
-api {} {
-int sqlite3_open(
- const char *filename, /* Database filename (UTF-8) */
- sqlite3 **ppDb /* OUT: SQLite db handle */
-);
-int sqlite3_open16(
- const void *filename, /* Database filename (UTF-16) */
- sqlite3 **ppDb /* OUT: SQLite db handle */
-);
-} {
- Open the sqlite database file "filename". The "filename" is UTF-8
- encoded for sqlite3_open() and UTF-16 encoded in the native byte order
- for sqlite3_open16(). An sqlite3* handle is returned in *ppDb, even
- if an error occurs. If the database is opened (or created) successfully,
- then SQLITE_OK is returned. Otherwise an error code is returned. The
- sqlite3_errmsg() or sqlite3_errmsg16() routines can be used to obtain
- an English language description of the error.
-
- If the database file does not exist, then a new database will be created
- 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.
-
- Note to windows users: The encoding used for the filename argument
- of sqlite3_open() must be UTF-8, not whatever codepage is currently
- defined. Filenames containing international characters must be converted
- to UTF-8 prior to passing them into sqlite3_open().
-}
-
-api {} {
-int sqlite3_prepare_v2(
- sqlite3 *db, /* Database handle */
- const char *zSql, /* SQL statement, UTF-8 encoded */
- int nBytes, /* Length of zSql in bytes. */
- sqlite3_stmt **ppStmt, /* OUT: Statement handle */
- const char **pzTail /* OUT: Pointer to unused portion of zSql */
-);
-int sqlite3_prepare16_v2(
- sqlite3 *db, /* Database handle */
- const void *zSql, /* SQL statement, UTF-16 encoded */
- int nBytes, /* Length of zSql in bytes. */
- sqlite3_stmt **ppStmt, /* OUT: Statement handle */
- const void **pzTail /* OUT: Pointer to unused portion of zSql */
-);
-
-/* Legacy Interfaces */
-int sqlite3_prepare(
- sqlite3 *db, /* Database handle */
- const char *zSql, /* SQL statement, UTF-8 encoded */
- int nBytes, /* Length of zSql in bytes. */
- sqlite3_stmt **ppStmt, /* OUT: Statement handle */
- const char **pzTail /* OUT: Pointer to unused portion of zSql */
-);
-int sqlite3_prepare16(
- sqlite3 *db, /* Database handle */
- const void *zSql, /* SQL statement, UTF-16 encoded */
- int nBytes, /* Length of zSql in bytes. */
- sqlite3_stmt **ppStmt, /* OUT: Statement handle */
- const void **pzTail /* OUT: Pointer to unused portion of zSql */
-);
-} {
- To execute an SQL query, it must first be compiled into a byte-code
- program using one of these routines.
-
- The first argument "db" is an SQLite database handle. The second
- argument "zSql" is the statement to be compiled, encoded as either
- UTF-8 or UTF-16. The sqlite3_prepare_v2()
- interfaces uses UTF-8 and sqlite3_prepare16_v2()
- use UTF-16. If the next argument, "nBytes", is less
- than zero, then zSql is read up to the first nul terminator. If
- "nBytes" is not less than zero, then it is the length of the string zSql
- in bytes (not characters).
-
- *pzTail is made to point to the first byte past the end of the first
- SQL statement in zSql. This routine only compiles the first statement
- in zSql, so *pzTail is left pointing to what remains uncompiled.
-
- *ppStmt is left pointing to a compiled SQL statement that can be
- executed using sqlite3_step(). Or if there is an error, *ppStmt may be
- set to NULL. If the input text contained no SQL (if the input is and
- empty string or a comment) then *ppStmt is set to NULL. The calling
- procedure is responsible for deleting this compiled SQL statement
- using sqlite3_finalize() after it has finished with it.
-
- On success, SQLITE_OK is returned. Otherwise an error code is returned.
-
- The sqlite3_prepare_v2() and sqlite3_prepare16_v2() interfaces are
- recommended for all new programs. The two older interfaces are retained
- for backwards compatibility, but their use is discouraged.
- In the "v2" interfaces, the prepared statement
- that is returned (the sqlite3_stmt object) contains a copy of the original
- SQL. This causes the sqlite3_step() interface to behave a differently in
- two ways:
-
-
-
- If the database schema changes, instead of returning SQLITE_SCHEMA as it
- always used to do, sqlite3_step() will automatically recompile the SQL
- statement and try to run it again. If the schema has changed in a way
- that makes the statement no longer valid, sqlite3_step() will still
- return SQLITE_SCHEMA. But unlike the legacy behavior, SQLITE_SCHEMA is
- now a fatal error. Calling sqlite3_prepare_v2() again will not make the
- error go away. Note: use sqlite3_errmsg() to find the text of the parsing
- error that results in an SQLITE_SCHEMA return.
-
-
-
- When an error occurs,
- sqlite3_step() will return one of the detailed result-codes
- like SQLITE_IOERR or SQLITE_FULL or SQLITE_SCHEMA directly. The
- legacy behavior was that sqlite3_step() would only return a generic
- SQLITE_ERROR code and you would have to make a second call to
- sqlite3_reset() in order to find the underlying cause of the problem.
- With the "v2" prepare interfaces, the underlying reason for the error is
- returned directly.
-
-
-}
-
-api {} {
-void sqlite3_progress_handler(sqlite3*, int, int(*)(void*), void*);
-} {
- Experimental
-
- This routine configures a callback function - the progress callback - that
- is invoked periodically during long running calls to sqlite3_exec(),
- sqlite3_step() and sqlite3_get_table().
- An example use for this API is to keep
- a GUI updated during a large query.
-
- The progress callback is invoked once for every N virtual machine opcodes,
- where N is the second argument to this function. The progress callback
- itself is identified by the third argument to this function. The fourth
- argument to this function is a void pointer passed to the progress callback
- function each time it is invoked.
-
- If a call to sqlite3_exec(), sqlite3_step() or sqlite3_get_table() results
- in less than N opcodes being executed, then the progress callback is not
- invoked.
-
- To remove the progress callback altogether, pass NULL as the third
- argument to this function.
-
- If the progress callback returns a result other than 0, then the current
- query is immediately terminated and any database changes rolled back. If the
- query was part of a larger transaction, then the transaction is not rolled
- back and remains active. The sqlite3_exec() call returns SQLITE_ABORT.
-
-}
-
-api {} {
-int sqlite3_reset(sqlite3_stmt *pStmt);
-} {
- The sqlite3_reset() function is called to reset a prepared SQL
- statement obtained by a previous call to
- sqlite3_prepare_v2() or
- sqlite3_prepare16_v2() back to it's initial state, ready to be re-executed.
- Any SQL statement variables that had values bound to them using
- the sqlite3_bind_*() API retain their values.
-}
-
-api {} {
-void sqlite3_result_blob(sqlite3_context*, const void*, int n, void(*)(void*));
-void sqlite3_result_double(sqlite3_context*, double);
-void sqlite3_result_error(sqlite3_context*, const char*, int);
-void sqlite3_result_error16(sqlite3_context*, const void*, int);
-void sqlite3_result_int(sqlite3_context*, int);
-void sqlite3_result_int64(sqlite3_context*, long long int);
-void sqlite3_result_null(sqlite3_context*);
-void sqlite3_result_text(sqlite3_context*, const char*, int n, void(*)(void*));
-void sqlite3_result_text16(sqlite3_context*, const void*, int n, void(*)(void*));
-void sqlite3_result_text16be(sqlite3_context*, const void*, int n, void(*)(void*));
-void sqlite3_result_text16le(sqlite3_context*, const void*, int n, void(*)(void*));
-void sqlite3_result_value(sqlite3_context*, sqlite3_value*);
-} {
- User-defined functions invoke these routines in order to
- set their return value. The sqlite3_result_value() routine is used
- to return an exact copy of one of the arguments to the function.
-
- The operation of these routines is very similar to the operation of
- sqlite3_bind_blob() and its cousins. Refer to the documentation there
- for additional information.
-}
-
-api {} {
-int sqlite3_set_authorizer(
- sqlite3*,
- int (*xAuth)(void*,int,const char*,const char*,const char*,const char*),
- void *pUserData
-);
-#define SQLITE_CREATE_INDEX 1 /* Index Name Table Name */
-#define SQLITE_CREATE_TABLE 2 /* Table Name NULL */
-#define SQLITE_CREATE_TEMP_INDEX 3 /* Index Name Table Name */
-#define SQLITE_CREATE_TEMP_TABLE 4 /* Table Name NULL */
-#define SQLITE_CREATE_TEMP_TRIGGER 5 /* Trigger Name Table Name */
-#define SQLITE_CREATE_TEMP_VIEW 6 /* View Name NULL */
-#define SQLITE_CREATE_TRIGGER 7 /* Trigger Name Table Name */
-#define SQLITE_CREATE_VIEW 8 /* View Name NULL */
-#define SQLITE_DELETE 9 /* Table Name NULL */
-#define SQLITE_DROP_INDEX 10 /* Index Name Table Name */
-#define SQLITE_DROP_TABLE 11 /* Table Name NULL */
-#define SQLITE_DROP_TEMP_INDEX 12 /* Index Name Table Name */
-#define SQLITE_DROP_TEMP_TABLE 13 /* Table Name NULL */
-#define SQLITE_DROP_TEMP_TRIGGER 14 /* Trigger Name Table Name */
-#define SQLITE_DROP_TEMP_VIEW 15 /* View Name NULL */
-#define SQLITE_DROP_TRIGGER 16 /* Trigger Name Table Name */
-#define SQLITE_DROP_VIEW 17 /* View Name NULL */
-#define SQLITE_INSERT 18 /* Table Name NULL */
-#define SQLITE_PRAGMA 19 /* Pragma Name 1st arg or NULL */
-#define SQLITE_READ 20 /* Table Name Column Name */
-#define SQLITE_SELECT 21 /* NULL NULL */
-#define SQLITE_TRANSACTION 22 /* NULL NULL */
-#define SQLITE_UPDATE 23 /* Table Name Column Name */
-#define SQLITE_ATTACH 24 /* Filename NULL */
-#define SQLITE_DETACH 25 /* Database Name NULL */
-#define SQLITE_ALTER_TABLE 26 /* Database Name Table Name */
-#define SQLITE_REINDEX 27 /* Index Name NULL */
-#define SQLITE_ANALYZE 28 /* Table Name NULL */
-#define SQLITE_CREATE_VTABLE 29 /* Table Name Module Name */
-#define SQLITE_DROP_VTABLE 30 /* Table Name Module Name */
-#define SQLITE_FUNCTION 31 /* Function Name NULL */
-
-#define SQLITE_DENY 1 /* Abort the SQL statement with an error */
-#define SQLITE_IGNORE 2 /* Don't allow access, but don't generate an error */
-} {
- This routine registers a callback with the SQLite library. The
- callback is invoked by sqlite3_prepare_v2() to authorize various
- operations against the database. The callback should
- return SQLITE_OK if access is allowed, SQLITE_DENY if the entire
- SQL statement should be aborted with an error and SQLITE_IGNORE
- if the operation should be treated as a no-op.
-
- Each database connection have at most one authorizer registered
- at a time one time. Each call
- to sqlite3_set_authorizer() overrides the previous authorizer.
- Setting the callback to NULL disables the authorizer.
-
- The second argument to the access authorization function will be one
- of the defined constants shown. These values signify what kind of operation
- is to be authorized. The 3rd and 4th arguments to the authorization
- function will be arguments or NULL depending on which of the
- codes is used as the second argument. For example, if the the
- 2nd argument code is SQLITE_READ then the 3rd argument will be the name
- of the table that is being read from and the 4th argument will be the
- name of the column that is being read from. Or if the 2nd argument
- is SQLITE_FUNCTION then the 3rd argument will be the name of the
- function that is being invoked and the 4th argument will be NULL.
-
- The 5th argument is the name
- of the database ("main", "temp", etc.) where applicable. The 6th argument
- is the name of the inner-most trigger or view that is responsible for
- the access attempt or NULL if this access attempt is directly from
- input SQL code.
-
- The return value of the authorization callback function should be one of the
- constants SQLITE_OK, SQLITE_DENY, or SQLITE_IGNORE. A return of
- SQLITE_OK means that the operation is permitted and that
- sqlite3_prepare_v2() can proceed as normal.
- A return of SQLITE_DENY means that the sqlite3_prepare_v2()
- should fail with an error. A return of SQLITE_IGNORE causes the
- sqlite3_prepare_v2() to continue as normal but the requested
- operation is silently converted into a no-op. A return of SQLITE_IGNORE
- in response to an SQLITE_READ or SQLITE_FUNCTION causes the column
- being read or the function being invoked to return a NULL.
-
- The intent of this routine is to allow applications to safely execute
- user-entered SQL. An appropriate callback can deny the user-entered
- SQL access certain operations (ex: anything that changes the database)
- or to deny access to certain tables or columns within the database.
-
- SQLite is not reentrant through the authorization callback function.
- The authorization callback function should not attempt to invoke
- any other SQLite APIs for the same database connection. If the
- authorization callback function invokes some other SQLite API, an
- SQLITE_MISUSE error or a segmentation fault may result.
-}
-
-api {} {
-int sqlite3_step(sqlite3_stmt*);
-} {
- After an SQL query has been prepared with a call to either
- sqlite3_prepare_v2() or sqlite3_prepare16_v2() or to one of
- the legacy interfaces sqlite3_prepare() or sqlite3_prepare16(),
- then this function must be
- called one or more times to execute the statement.
-
- The details of the behavior of this sqlite3_step() interface depend
- on whether the statement was prepared using the newer "v2" interface
- sqlite3_prepare_v2() and sqlite3_prepare16_v2() or the older legacy
- interface sqlite3_prepare() and sqlite3_prepare16(). The use of the
- new "v2" interface is recommended for new applications but the legacy
- interface will continue to be supported.
-
- In the lagacy interface, the return value will be either SQLITE_BUSY,
- SQLITE_DONE, SQLITE_ROW, SQLITE_ERROR, or SQLITE_MISUSE. With the "v2"
- interface, any of the other SQLite result-codes might be returned as
- well.
-
- SQLITE_BUSY means that the database engine attempted to open
- a locked database and there is no busy callback registered.
- Call sqlite3_step() again to retry the open.
-
- SQLITE_DONE means that the statement has finished executing
- successfully. sqlite3_step() should not be called again on this virtual
- machine without first calling sqlite3_reset() to reset the virtual
- machine back to its initial state.
-
- If the SQL statement being executed returns any data, then
- SQLITE_ROW is returned each time a new row of data is ready
- for processing by the caller. The values may be accessed using
- the sqlite3_column_int(), sqlite3_column_text(), and similar functions.
- sqlite3_step() is called again to retrieve the next row of data.
-
- 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().
- A more specific error code (example: SQLITE_INTERRUPT, SQLITE_SCHEMA,
- SQLITE_CORRUPT, and so forth) can be obtained by calling
- sqlite3_reset() on the prepared statement. In the "v2" interface,
- the more specific error code is returned directly by sqlite3_step().
-
- 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.
-
- Goofy Interface Alert:
- In the legacy interface,
- the sqlite3_step() API always returns a generic error code,
- SQLITE_ERROR, following any error other than SQLITE_BUSY and SQLITE_MISUSE.
- You must call sqlite3_reset() (or sqlite3_finalize()) in order to find
- one of the specific result-codes that better describes the error.
- We admit that this is a goofy design. The problem has been fixed
- with the "v2" interface. If you prepare all of your SQL statements
- using either sqlite3_prepare_v2() or sqlite3_prepare16_v2() instead
- of the legacy sqlite3_prepare() and sqlite3_prepare16(), then the
- more specific result-codes are returned directly by sqlite3_step().
- The use of the "v2" interface is recommended.
-}
-
-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
- calls to sqlite3_prepare_v2() or sqlite3_reset().
- This function can be used (for example) to generate
- a log file of all SQL executed against a database. This can be
- useful when debugging an application that uses SQLite.
-}
-
-api {} {
-void *sqlite3_user_data(sqlite3_context*);
-} {
- The pUserData argument to the sqlite3_create_function() and
- sqlite3_create_function16() routines used to register user functions
- is available to the implementation of the function using this
- call.
-}
-
-api {} {
-const void *sqlite3_value_blob(sqlite3_value*);
-int sqlite3_value_bytes(sqlite3_value*);
-int sqlite3_value_bytes16(sqlite3_value*);
-double sqlite3_value_double(sqlite3_value*);
-int sqlite3_value_int(sqlite3_value*);
-long long int sqlite3_value_int64(sqlite3_value*);
-const unsigned char *sqlite3_value_text(sqlite3_value*);
-const void *sqlite3_value_text16(sqlite3_value*);
-const void *sqlite3_value_text16be(sqlite3_value*);
-const void *sqlite3_value_text16le(sqlite3_value*);
-int sqlite3_value_type(sqlite3_value*);
-} {
- This group of routines returns information about arguments to
- a user-defined function. Function implementations use these routines
- to access their arguments. These routines are the same as the
- sqlite3_column_... routines except that these routines take a single
- sqlite3_value* pointer instead of an sqlite3_stmt* and an integer
- column number.
-
- See the documentation under sqlite3_column_blob for additional
- information.
-
- Please pay particular attention to the fact that the pointer that
- is returned from sqlite3_value_blob(), sqlite3_value_text(), or
- sqlite3_value_text16() can be invalidated by a subsequent call to
- sqlite3_value_bytes(), sqlite3_value_bytes16(), sqlite_value_text(),
- or sqlite3_value_text16().
-}
-
-api {} {
- int sqlite3_sleep(int);
-} {
- Sleep for a little while. The second parameter is the number of
- miliseconds to sleep for.
-
- If the operating system does not support sleep requests with
- milisecond time resolution, then the time will be rounded up to
- the nearest second. The number of miliseconds of sleep actually
- requested from the operating system is returned.
-}
-
-api {} {
- int sqlite3_expired(sqlite3_stmt*);
-} {
- Return TRUE (non-zero) if the statement supplied as an argument needs
- to be recompiled. A statement needs to be recompiled whenever the
- execution environment changes in a way that would alter the program
- that sqlite3_prepare() generates. For example, if new functions or
- collating sequences are registered or if an authorizer function is
- added or changed.
-}
-
-api {} {
- int sqlite3_transfer_bindings(sqlite3_stmt*, sqlite3_stmt*);
-} {
- Move all bindings from the first prepared statement over to the second.
- This routine is useful, for example, if the first prepared statement
- fails with an SQLITE_SCHEMA error. The same SQL can be prepared into
- the second prepared statement then all of the bindings transfered over
- to the second statement before the first statement is finalized.
-}
-
-api {} {
- int sqlite3_global_recover();
-} {
- This function used to be involved in recovering from out-of-memory
- errors. But as of SQLite version 3.3.0, out-of-memory recovery is
- automatic and this routine now does nothing. THe interface is retained
- to avoid link errors with legacy code.
-}
-
-api {} {
- int sqlite3_get_autocommit(sqlite3*);
-} {
- Test to see whether or not the database connection is in autocommit
- mode. Return TRUE if it is and FALSE if not. Autocommit mode is on
- by default. Autocommit is disabled by a BEGIN statement and reenabled
- by the next COMMIT or ROLLBACK.
-}
-
-api {} {
- int sqlite3_clear_bindings(sqlite3_stmt*);
-} {
- Set all the parameters in the compiled SQL statement back to NULL.
-}
-
-api {} {
- sqlite3 *sqlite3_db_handle(sqlite3_stmt*);
-} {
- Return the sqlite3* database handle to which the prepared statement given
- in the argument belongs. This is the same database handle that was
- the first argument to the sqlite3_prepare() that was used to create
- the statement in the first place.
-}
-
-api {} {
- void *sqlite3_update_hook(
- sqlite3*,
- void(*)(void *,int ,char const *,char const *,sqlite_int64),
- void*
- );
-} {
- Register a callback function with the database connection identified by the
- first argument to be invoked whenever a row is updated, inserted or deleted.
- Any callback set by a previous call to this function for the same
- database connection is overridden.
-
- The second argument is a pointer to the function to invoke when a
- row is updated, inserted or deleted. The first argument to the callback is
- a copy of the third argument to sqlite3_update_hook. The second callback
- argument is one of SQLITE_INSERT, SQLITE_DELETE or SQLITE_UPDATE, depending
- on the operation that caused the callback to be invoked. The third and
- fourth arguments to the callback contain pointers to the database and
- table name containing the affected row. The final callback parameter is
- the rowid of the row. In the case of an update, this is the rowid after
- the update takes place.
-
- The update hook is not invoked when internal system tables are
- modified (i.e. sqlite_master and sqlite_sequence).
-
- If another function was previously registered, its pArg value is returned.
- Otherwise NULL is returned.
-
- See also: sqlite3_commit_hook(), sqlite3_rollback_hook()
-}
-
-api {} {
- void *sqlite3_rollback_hook(sqlite3*, void(*)(void *), void*);
-} {
- Register a callback to be invoked whenever a transaction is rolled
- back.
-
- The new callback function overrides any existing rollback-hook
- callback. If there was an existing callback, then it's pArg value
- (the third argument to sqlite3_rollback_hook() when it was registered)
- is returned. Otherwise, NULL is returned.
-
- For the purposes of this API, a transaction is said to have been
- rolled back if an explicit "ROLLBACK" statement is executed, or
- an error or constraint causes an implicit rollback to occur. The
- callback is not invoked if a transaction is automatically rolled
- back because the database connection is closed.
-}
-
-api {} {
- int sqlite3_enable_shared_cache(int);
-} {
- This routine enables or disables the sharing of the database cache
- and schema data structures between connections to the same database.
- Sharing is enabled if the argument is true and disabled if the argument
- is false.
-
- Cache sharing is enabled and disabled on a thread-by-thread basis.
- Each call to this routine enables or disables cache sharing only for
- connections created in the same thread in which this routine is called.
- There is no mechanism for sharing cache between database connections
- running in different threads.
-
- Sharing must be disabled prior to shutting down a thread or else
- the thread will leak memory. Call this routine with an argument of
- 0 to turn off sharing. Or use the sqlite3_thread_cleanup() API.
-
- This routine must not be called when any database connections
- are active in the current thread. Enabling or disabling shared
- cache while there are active database connections will result
- in memory corruption.
-
- When the shared cache is enabled, the
- following routines must always be called from the same thread:
- sqlite3_open(), sqlite3_prepare_v2(), sqlite3_step(), sqlite3_reset(),
- sqlite3_finalize(), and sqlite3_close().
- This is due to the fact that the shared cache makes use of
- thread-specific storage so that it will be available for sharing
- with other connections.
-
- Virtual tables cannot be used with a shared cache. When shared
- cache is enabled, the sqlite3_create_module() API used to register
- virtual tables will always return an error.
-
- This routine returns SQLITE_OK if shared cache was
- enabled or disabled successfully. An error code is returned
- otherwise.
-
- Shared cache is disabled by default for backward compatibility.
-}
-
-api {} {
- void sqlite3_thread_cleanup(void);
-} {
- This routine makes sure that all thread local storage used by SQLite
- in the current thread has been deallocated. A thread can call this
- routine prior to terminating in order to make sure there are no memory
- leaks.
-
- This routine is not strictly necessary. If cache sharing has been
- disabled using sqlite3_enable_shared_cache() and if all database
- connections have been closed and if SQLITE_ENABLE_MEMORY_MANAGMENT is
- on and all memory has been freed, then the thread local storage will
- already have been automatically deallocated. This routine is provided
- as a convenience to the program who just wants to make sure that there
- are no leaks.
-}
-
-api {} {
- int sqlite3_release_memory(int N);
-} {
- This routine attempts to free at least N bytes of memory from the caches
- of database connecions that were created in the same thread from which this
- routine is called. The value returned is the number of bytes actually
- freed.
-
- This routine is only available if memory management has been enabled
- by compiling with the SQLITE_ENABLE_MEMORY_MANAGMENT macro.
-}
-
-api {} {
- void sqlite3_soft_heap_limit(int N);
-} {
- This routine sets the soft heap limit for the current thread to N.
- If the total heap usage by SQLite in the current thread exceeds N,
- then sqlite3_release_memory() is called to try to reduce the memory usage
- below the soft limit.
-
- Prior to shutting down a thread sqlite3_soft_heap_limit() must be set to
- zero (the default) or else the thread will leak memory. Alternatively, use
- the sqlite3_thread_cleanup() API.
-
- A negative or zero value for N means that there is no soft heap limit and
- sqlite3_release_memory() will only be called when memory is exhaused.
- The default value for the soft heap limit is zero.
-
- SQLite makes a best effort to honor the soft heap limit. But if it
- is unable to reduce memory usage below the soft limit, execution will
- continue without error or notification. This is why the limit is
- called a "soft" limit. It is advisory only.
-
- This routine is only available if memory management has been enabled
- by compiling with the SQLITE_ENABLE_MEMORY_MANAGMENT macro.
-}
-
-api {} {
- void sqlite3_thread_cleanup(void);
-} {
- This routine ensures that a thread that has used SQLite in the past
- has released any thread-local storage it might have allocated.
- When the rest of the API is used properly, the cleanup of
- thread-local storage should be completely automatic. You should
- never really need to invoke this API. But it is provided to you
- as a precaution and as a potential work-around for future
- thread-releated memory-leaks.
-}
-
-set n 0
-set i 0
-foreach item $apilist {
- set namelist [lindex $item 0]
- foreach name $namelist {
- set n_to_name($n) $name
- set n_to_idx($n) $i
- set name_to_idx($name) $i
- incr n
- }
- incr i
-}
-set i 0
-foreach name [lsort [array names name_to_idx]] {
- set sname($i) $name
- incr i
-}
-#parray n_to_name
-#parray n_to_idx
-#parray name_to_idx
-#parray sname
-incr n -1
-puts "
"
DELETED www/changes.tcl
Index: www/changes.tcl
==================================================================
--- www/changes.tcl
+++ /dev/null
@@ -1,1880 +0,0 @@
-#
-# Run this script to generated a changes.html output file
-#
-source common.tcl
-header {SQLite changes}
-puts {
-
-This page provides a high-level summary of changes to SQLite.
-For more detail, refer the the checkin logs generated by
-CVS at
-
-http://www.sqlite.org/cvstrac/timeline.
-
Dropped support for the SQLITE_OMIT_MEMORY_ALLOCATION compile-time
-option.
-
Always open files using FILE_FLAG_RANDOM_ACCESS under windows.
-
The 3rd parameter of the built-in SUBSTR() function is now optional.
-
Bug fix: do not invoke the authorizer when reparsing the schema after
-a schema change.
-
Added the experimental malloc-free memory allocator in mem3.c.
-
Virtual machine stores 64-bit integer and floating point constants
-in binary instead of text for a performance boost.
-
Fix a race condition in test_async.c.
-
Added the ".timer" command to the CLI
-}
-
-chng {2007 Oct 04 (3.5.1)} {
-
Nota Bene: We are not using terms "alpha" or "beta" on this
- release because the code is stable and because if we use those terms,
- nobody will upgrade. However, we still reserve the right to make
- incompatible changes to the new VFS interface in future releases.
-
-
Fix a bug in the handling of SQLITE_FULL errors that could lead
- to database corruption. Ticket #2686.
-
The test_async.c drive now does full file locking and works correctly
- when used simultaneously by multiple processes on the same database.
-
The CLI ignores whitespace (including comments) at the end of lines
-
Make sure the query optimizer checks dependences on all terms of
- a compound SELECT statement. Ticket #2640.
-
Add demonstration code showing how to build a VFS for a raw
- mass storage without a filesystem.
-
Added an output buffer size parameter to the xGetTempname() method
- of the VFS layer.
-
Sticky SQLITE_FULL or SQLITE_IOERR errors in the pager are reset
- when a new transaction is started.
-}
-
-
-chng {2007 Sep 04 (3.5.0) alpha} {
-
Redesign the OS interface layer. See
- 34to35.html for details.
- *** Potentially incompatible change ***
-
Improved error detection of misused aggregate functions.
-
-
Improvements to the amalgamation generator script so that all symbols
-are prefixed with either SQLITE_PRIVATE or SQLITE_API.
-}
-
-chng {2007 July 20 (3.4.1)} {
-
Fix a bug in VACUUM that can lead to
-
- database corruption if two
- processes are connected to the database at the same time and one
- VACUUMs then the other then modifies the database.
-
The expression "+column" is now considered the same as "column"
- when computing the collating sequence to use on the expression.
-
In the TCL language interface,
- "@variable" instead of "$variable" always binds as a blob.
Make sure the TCL language interface works correctly with 64-bit
- integers on 64-bit machines.
-
Allow the value -9223372036854775808 as an integer literal in SQL
- statements.
-
Add the capability of "hidden" columns in virtual tables.
-
Use the macro SQLITE_PRIVATE (defaulting to "static") on all
- internal functions in the amalgamation.
-
Add pluggable tokenizers and ICU
- tokenization support to FTS2
-
Other minor bug fixes and documentation enhancements
-}
-
-chng {2007 June 18 (3.4.0)} {
-
Fix a bug that can lead to database corruption if an SQLITE_BUSY error
- occurs in the middle of an explicit transaction and that transaction
- is later committed.
- Ticket #2409.
- See the
-
- CorruptionFollowingBusyError wiki page for details.
-
Fix a bug that can lead to database corruption if autovacuum mode is
- on and a malloc() failure follows a CREATE TABLE or CREATE INDEX statement
- which itself follows a cache overflow inside a transaction. See
- ticket #2418.
-
-
Added explicit upper bounds on the sizes and
- quantities of things SQLite can process. This change might cause
- compatibility problems for
- applications that use SQLite in the extreme, which is why the current
- release is 3.4.0 instead of 3.3.18.
In the windows OS driver, reacquire a SHARED lock if an attempt to
- acquire an EXCLUSIVE lock fails. Ticket #2354
-
Fix the REPLACE() function so that it returns NULL if the second argument
- is an empty string. Ticket #2324.
-
Document the hazards of type coversions in
- sqlite3_column_blob()
- and related APIs. Fix unnecessary type conversions. Ticket #2321.
-
Internationalization of the TRIM() function. Ticket #2323
-
Use memmove() instead of memcpy() when moving between memory regions
- that might overlap. Ticket #2334
-
Fix an optimizer bug involving subqueries in a compound SELECT that has
- both an ORDER BY and a LIMIT clause. Ticket #2339.
-
Make sure the sqlite3_snprintf()
- interface does not zero-terminate the buffer if the buffer size is
- less than 1. Ticket #2341
-
Fix the built-in printf logic so that it prints "NaN" not "Inf" for
- floating-point NaNs. Ticket #2345
-
When converting BLOB to TEXT, use the text encoding of the main database.
- Ticket #2349
-
Keep the full precision of integers (if possible) when casting to
- NUMERIC. Ticket #2364
-
Fix a bug in the handling of UTF16 codepoint 0xE000
-
Consider explicit collate clauses when matching WHERE constraints
- to indices in the query optimizer. Ticket #2391
-
Fix the query optimizer to correctly handle constant expressions in
- the ON clause of a LEFT JOIN. Ticket #2403
-
Fix the query optimizer to handle rowid comparisions to NULL
- correctly. Ticket #2404
-
Fix many potental segfaults that could be caused by malicious SQL
- statements.
-}
-
-chng {2007 April 25 (3.3.17)} {
-
When the "write_version" value of the database header is larger than
- what the library understands, make the database read-only instead of
- unreadable.
-
Other minor bug fixes
-}
-
-chng {2007 April 18 (3.3.16)} {
-
Fix a bug that caused VACUUM to fail if NULLs appeared in a
- UNIQUE column.
-
Reinstate performance improvements that were added in 3.3.14
- but regressed in 3.3.15.
-
Fix problems with the handling of ORDER BY expressions on
- compound SELECT statements in subqueries.
-
Fix a potential segfault when destroying locks on WinCE in
- a multi-threaded environment.
-
Documentation updates.
-}
-
-chng {2007 April 9 (3.3.15)} {
-
Fix a bug introduced in 3.3.14 that caused a rollback of
- CREATE TEMP TABLE to leave the database connection wedged.
-
Fix a bug that caused an extra NULL row to be returned when
- a descending query was interrupted by a change to the database.
-
The FOR EACH STATEMENT clause on a trigger now causes a syntax
- error. It used to be silently ignored.
-
Fix an obscure and relatively harmless problem that might have caused
- a resource leak following an I/O error.
-
Many improvements to the test suite. Test coverage now exceeded 98%
-}
-
-chng {2007 April 2 (3.3.14)} {
-
Fix a bug
- in 3.3.13 that could cause a segfault when the IN operator
- is used one one term of a two-column index and the right-hand side of
- the IN operator contains a NULL.
-
Added a new OS interface method for determining the sector size
- of underlying media: sqlite3OsSectorSize().
-
A new algorithm for statements of the form
- INSERT INTO table1 SELECT * FROM table2
- is faster and reduces fragmentation. VACUUM uses statements of
- this form and thus runs faster and defragments better.
-
Performance enhancements through reductions in disk I/O:
-
-
Do not read the last page of an overflow chain when
- deleting the row - just add that page to the freelist.
-
Do not store pages being deleted in the
- rollback journal.
-
Do not read in the (meaningless) content of
- pages extracted from the freelist.
-
Do not flush the page cache (and thus avoiding
- a cache refill) unless another process changes the underlying
- database file.
-
Truncate rather than delete the rollback journal when committing
- a transaction in exclusive access mode, or when committing the TEMP
- database.
Use heap space instead of stack space for large buffers in the
- pager - useful on embedded platforms with stack-space
- limitations.
-
Add a makefile target "sqlite3.c" that builds an amalgamation containing
- the core SQLite library C code in a single file.
-
Get the library working correctly when compiled
- with GCC option "-fstrict-aliasing".
-
Removed the vestigal SQLITE_PROTOCOL error.
-
Improvements to test coverage, other minor bugs fixed,
- memory leaks plugged,
- code refactored and/or recommented in places for easier reading.
-}
-
-chng {2007 February 13 (3.3.13)} {
-
Add a "fragmentation" measurement in the output of sqlite3_analyzer.
-
Add the COLLATE operator used to explicitly set the collating sequence
-used by an expression. This feature is considered experimental pending
-additional testing.
-
Allow up to 64 tables in a join - the old limit was 32.
-
Added two new experimental functions:
-randomBlob() and
-hex().
-Their intended use is to facilitate generating
-UUIDs.
-
-
Fix a problem where
-PRAGMA count_changes was
-causing incorrect results for updates on tables with triggers
-
Fix a bug in the ORDER BY clause optimizer for joins where the
-left-most table in the join is constrained by a UNIQUE index.
-
Fixed a bug in the "copy" method of the TCL interface.
-
Bug fixes in fts1 and fts2 modules.
-}
-
-chng {2007 January 27 (3.3.12)} {
-
Fix another bug in the IS NULL optimization that was added in
-version 3.3.9.
-
Fix a assertion fault that occurred on deeply nested views.
Minor syntactic changes to support a wider variety of compilers.
-}
-
-chng {2007 January 22 (3.3.11)} {
-
Fix another bug in the implementation of the new
-sqlite3_prepare_v2() API.
-We'll get it right eventually...
-
Fix a bug in the IS NULL optimization that was added in version 3.3.9 -
-the bug was causing incorrect results on certain LEFT JOINs that included
-in the WHERE clause an IS NULL constraint for the right table of the
-LEFT JOIN.
-
Make AreFileApisANSI() a no-op macro in winCE since winCE does not
-support this function.
-}
-
-chng {2007 January 9 (3.3.10)} {
-
Fix bugs in the implementation of the new
-sqlite3_prepare_v2() API
-that can lead to segfaults.
-
Fix 1-second round-off errors in the
-
-strftime() function
-
Enhance the windows OS layer to provide detailed error codes
-
Work around a win2k problem so that SQLite can use single-character
-database file names
The default file format is now 1.
-}
-
-chng {2006 June 6 (3.3.6)} {
-
Plays better with virus scanners on windows
-
Faster :memory: databases
-
Fix an obscure segfault in UTF-8 to UTF-16 conversions
-
Added driver for OS/2
-
Correct column meta-information returned for aggregate queries
-
Enhanced output from EXPLAIN QUERY PLAN
-
LIMIT 0 now works on subqueries
-
Bug fixes and performance enhancements in the query optimizer
-
Correctly handle NULL filenames in ATTACH and DETACH
-
Inproved syntax error messages in the parser
-
Fix type coercion rules for the IN operator
-}
-
-chng {2006 April 5 (3.3.5)} {
-
CHECK constraints use conflict resolution algorithms correctly.
-
The SUM() function throws an error on integer overflow.
-
Choose the column names in a compound query from the left-most SELECT
- instead of the right-most.
-
The sqlite3_create_collation() function
- honors the SQLITE_UTF16_ALIGNED flag.
-
SQLITE_SECURE_DELETE compile-time option causes deletes to overwrite
- old data with zeros.
-
Detect integer overflow in abs().
-
The random() function provides 64 bits of randomness instead of
- only 32 bits.
-
Parser detects and reports automaton stack overflow.
-
Change the round() function to return REAL instead of TEXT.
-
Allow WHERE clause terms on the left table of a LEFT OUTER JOIN to
- contain aggregate subqueries.
-
Skip over leading spaces in text to numeric conversions.
-
Various minor bug and documentation typo fixes and
- performance enhancements.
-}
-
-chng {2006 February 11 (3.3.4)} {
-
Fix a blunder in the Unix mutex implementation that can lead to
-deadlock on multithreaded systems.
-
Fix an alignment problem on 64-bit machines
-
Added the fullfsync pragma.
-
Fix an optimizer bug that could have caused some unusual LEFT OUTER JOINs
-to give incorrect results.
-
The SUM function detects integer overflow and converts to accumulating
-an approximate result using floating point numbers
-
Host parameter names can begin with '@' for compatibility with SQL Server.
-
-
Other miscellaneous bug fixes
-}
-
-chng {2006 January 31 (3.3.3)} {
-
Removed support for an ON CONFLICT clause on CREATE INDEX - it never
-worked correctly so this should not present any backward compatibility
-problems.
-
Authorizer callback now notified of ALTER TABLE ADD COLUMN commands
-
After any changes to the TEMP database schema, all prepared statements
-are invalidated and must be recreated using a new call to
-sqlite3_prepare()
-
Other minor bug fixes in preparation for the first stable release
-of version 3.3
-}
-
-chng {2006 January 24 (3.3.2 beta)} {
-
Bug fixes and speed improvements. Improved test coverage.
-
Changes to the OS-layer interface: mutexes must now be recursive.
-
Discontinue the use of thread-specific data for out-of-memory
-exception handling
-}
-
-chng {2006 January 16 (3.3.1 alpha)} {
-
Countless bug fixes
-
Speed improvements
-
Database connections can now be used by multiple threads, not just
-the thread in which they were created.
-}
-
-chng {2006 January 10 (3.3.0 alpha)} {
-
CHECK constraints
-
IF EXISTS and IF NOT EXISTS clauses on CREATE/DROP TABLE/INDEX.
-
DESC indices
-
More efficient encoding of boolean values resulting in smaller database
-files
-
More aggressive SQLITE_OMIT_FLOATING_POINT
-
Separate INTEGER and REAL affinity
-
Added a virtual function layer for the OS interface
-
"exists" method added to the TCL interface
-
Improved response to out-of-memory errors
-
Database cache can be optionally shared between connections
-in the same thread
-
Optional READ UNCOMMITTED isolation (instead of the default
-isolation level of SERIALIZABLE) and table level locking when
-database connections share a common cache.
-}
-
-chng {2005 December 19 (3.2.8)} {
-
Fix an obscure bug that can cause database corruption under the
-following unusual circumstances: A large INSERT or UPDATE statement which
-is part of an even larger transaction fails due to a uniqueness contraint
-but the containing transaction commits.
-}
-
-chng {2005 December 19 (2.8.17)} {
-
Fix an obscure bug that can cause database corruption under the
-following unusual circumstances: A large INSERT or UPDATE statement which
-is part of an even larger transaction fails due to a uniqueness contraint
-but the containing transaction commits.
-}
-
-chng {2005 September 24 (3.2.7)} {
-
GROUP BY now considers NULLs to be equal again, as it should
-
-
Now compiles on Solaris and OpenBSD and other Unix variants
-that lack the fdatasync() function
-
Now compiles on MSVC++6 again
-
Fix uninitialized variables causing malfunctions for various obscure
-queries
-
Correctly compute a LEFT OUTER JOINs that is constrained on the
-left table only
-}
-
-chng {2005 September 17 (3.2.6)} {
-
Fix a bug that can cause database corruption if a VACUUM (or
- autovacuum) fails and is rolled back on a database that is
- larger than 1GiB
-
LIKE optiization now works for columns with COLLATE NOCASE
-
ORDER BY and GROUP BY now use bounded memory
-
Added support for COUNT(DISTINCT expr)
-
Change the way SUM() handles NULL values in order to comply with
- the SQL standard
-
Use fdatasync() instead of fsync() where possible in order to speed
- up commits slightly
-
Use of the CROSS keyword in a join turns off the table reordering
- optimization
-
Added the experimental and undocumented EXPLAIN QUERY PLAN capability
-
Use the unicode API in windows
-}
-
-chng {2005 August 27 (3.2.5)} {
-
Fix a bug effecting DELETE and UPDATE statements that changed
-more than 40960 rows.
-
Change the makefile so that it no longer requires GNUmake extensions
-
Fix the --enable-threadsafe option on the configure script
-
Fix a code generator bug that occurs when the left-hand side of an IN
-operator is constant and the right-hand side is a SELECT statement
-
The PRAGMA synchronous=off statement now disables syncing of the
-master journal file in addition to the normal rollback journals
-}
-
-chng {2005 August 24 (3.2.4)} {
-
Fix a bug introduced in the previous release
-that can cause a segfault while generating code
-for complex WHERE clauses.
-
Allow floating point literals to begin or end with a decimal point.
-}
-
-chng {2005 August 21 (3.2.3)} {
-
Added support for the CAST operator
-
Tcl interface allows BLOB values to be transferred to user-defined
-functions
-
Added the "transaction" method to the Tcl interface
-
Allow the DEFAULT value of a column to call functions that have constant
-operands
-
Added the ANALYZE command for gathering statistics on indices and
-using those statistics when picking an index in the optimizer
-
Remove the limit (formerly 100) on the number of terms in the
-WHERE clause
-
The right-hand side of the IN operator can now be a list of expressions
-instead of just a list of constants
-
Rework the optimizer so that it is able to make better use of indices
-
The order of tables in a join is adjusted automatically to make
-better use of indices
-
The IN operator is now a candidate for optimization even if the left-hand
-side is not the left-most term of the index. Multiple IN operators can be
-used with the same index.
-
WHERE clause expressions using BETWEEN and OR are now candidates
-for optimization
-
Added the "case_sensitive_like" pragma and the SQLITE_CASE_SENSITIVE_LIKE
-compile-time option to set its default value to "on".
-
Use indices to help with GLOB expressions and LIKE expressions too
-when the case_sensitive_like pragma is enabled
-
Added support for grave-accent quoting for compatibility with MySQL
-
Improved test coverage
-
Dozens of minor bug fixes
-}
-
-chng {2005 June 13 (3.2.2)} {
-
Added the sqlite3_db_handle() API
-
Added the sqlite3_get_autocommit() API
-
Added a REGEXP operator to the parser. There is no function to back
-up this operator in the standard build but users can add their own using
-sqlite3_create_function()
-
Speed improvements and library footprint reductions.
-
Fix byte alignment problems on 64-bit architectures.
-
Many, many minor bug fixes and documentation updates.
-}
-
-chng {2005 March 29 (3.2.1)} {
-
Fix a memory allocation error in the new ADD COLUMN comment.
-
Documentation updates
-}
-
-chng {2005 March 21 (3.2.0)} {
-
Added support for ALTER TABLE ADD COLUMN.
-
Added support for the "T" separator in ISO-8601 date/time strings.
-
Improved support for Cygwin.
-
Numerous bug fixes and documentation updates.
-}
-
-chng {2005 March 16 (3.1.6)} {
-
Fix a bug that could cause database corruption when inserting
- record into tables with around 125 columns.
-
sqlite3_step() is now much more likely to invoke the busy handler
- and less likely to return SQLITE_BUSY.
-
Fix memory leaks that used to occur after a malloc() failure.
-}
-
-chng {2005 March 11 (3.1.5)} {
-
The ioctl on OS-X to control syncing to disk is F_FULLFSYNC,
- not F_FULLSYNC. The previous release had it wrong.
-}
-
-chng {2005 March 10 (3.1.4)} {
-
Fix a bug in autovacuum that could cause database corruption if
-a CREATE UNIQUE INDEX fails because of a constraint violation.
-This problem only occurs if the new autovacuum feature introduced in
-version 3.1 is turned on.
-
The F_FULLSYNC ioctl (currently only supported on OS-X) is disabled
-if the synchronous pragma is set to something other than "full".
-
Add additional forward compatibility to the future version 3.2 database
-file format.
-
Fix a bug in WHERE clauses of the form (rowid<'2')
-
New SQLITE_OMIT_... compile-time options added
-
Updates to the man page
-
Remove the use of strcasecmp() from the shell
-
Windows DLL exports symbols Tclsqlite_Init and Sqlite_Init
-}
-
-chng {2005 February 19 (3.1.3)} {
-
Fix a problem with VACUUM on databases from which tables containing
-AUTOINCREMENT have been dropped.
-
Add forward compatibility to the future version 3.2 database file
-format.
-
Documentation updates
-}
-
-chng {2005 February 15 (3.1.2)} {
-
Fix a bug that can lead to database corruption if there are two
-open connections to the same database and one connection does a VACUUM
-and the second makes some change to the database.
-
Allow "?" parameters in the LIMIT clause.
-
Fix VACUUM so that it works with AUTOINCREMENT.
-
Fix a race condition in AUTOVACUUM that can lead to corrupt databases
-
Add a numeric version number to the sqlite3.h include file.
-
Other minor bug fixes and performance enhancements.
-}
-
-chng {2005 February 15 (2.8.16)} {
-
Fix a bug that can lead to database corruption if there are two
-open connections to the same database and one connection does a VACUUM
-and the second makes some change to the database.
-
Correctly handle quoted names in CREATE INDEX statements.
-
Fix a naming conflict between sqlite.h and sqlite3.h.
-
Avoid excess heap usage when copying expressions.
-
Other minor bug fixes.
-}
-
-chng {2005 February 1 (3.1.1 BETA)} {
-
Automatic caching of prepared statements in the TCL interface
-
ATTACH and DETACH as well as some other operations cause existing
- prepared statements to expire.
-
Numerious minor bug fixes
-}
-
-chng {2005 January 21 (3.1.0 ALPHA)} {
-
Autovacuum support added
-
CURRENT_TIME, CURRENT_DATE, and CURRENT_TIMESTAMP added
-
Support for the EXISTS clause added.
-
Support for correlated subqueries added.
-
Added the ESCAPE clause on the LIKE operator.
-
Support for ALTER TABLE ... RENAME TABLE ... added
-
AUTOINCREMENT keyword supported on INTEGER PRIMARY KEY
-
Many SQLITE_OMIT_ macros inserts to omit features at compile-time
- and reduce the library footprint.
-
The REINDEX command was added.
-
The engine no longer consults the main table if it can get
- all the information it needs from an index.
-
Many nuisance bugs fixed.
-}
-
-chng {2004 October 11 (3.0.8)} {
-
Add support for DEFERRED, IMMEDIATE, and EXCLUSIVE transactions.
-
Allow new user-defined functions to be created when there are
-already one or more precompiled SQL statements.
-
Fix portability problems for Mingw/MSYS.
-
Fix a byte alignment problem on 64-bit Sparc machines.
-
Fix the ".import" command of the shell so that it ignores \r
-characters at the end of lines.
-
The "csv" mode option in the shell puts strings inside double-quotes.
-
Fix typos in documentation.
-
Convert array constants in the code to have type "const".
-
Numerous code optimizations, specially optimizations designed to
-make the code footprint smaller.
-}
-
-chng {2004 September 18 (3.0.7)} {
-
The BTree module allocates large buffers using malloc() instead of
- off of the stack, in order to play better on machines with limited
- stack space.
-
Fixed naming conflicts so that versions 2.8 and 3.0 can be
- linked and used together in the same ANSI-C source file.
-
New interface: sqlite3_bind_parameter_index()
-
Add support for wildcard parameters of the form: "?nnn"
-
Fix problems found on 64-bit systems.
-
Removed encode.c file (containing unused routines) from the
- version 3.0 source tree.
-
The sqlite3_trace() callbacks occur before each statement
- is executed, not when the statement is compiled.
-
Makefile updates and miscellaneous bug fixes.
-}
-
-chng {2004 September 02 (3.0.6 beta)} {
-
Better detection and handling of corrupt database files.
-
The sqlite3_step() interface returns SQLITE_BUSY if it is unable
- to commit a change because of a lock
-
Combine the implementations of LIKE and GLOB into a single
- pattern-matching subroutine.
-
Miscellaneous code size optimizations and bug fixes
-}
-
-chng {2004 August 29 (3.0.5 beta)} {
-
Support for ":AAA" style bind parameter names.
-
Added the new sqlite3_bind_parameter_name() interface.
-
Support for TCL variable names embedded in SQL statements in the
- TCL bindings.
-
The TCL bindings transfer data without necessarily doing a conversion
- to a string.
-
The database for TEMP tables is not created until it is needed.
-
Add the ability to specify an alternative temporary file directory
- using the "sqlite_temp_directory" global variable.
-
A compile-time option (SQLITE_BUSY_RESERVED_LOCK) causes the busy
- handler to be called when there is contention for a RESERVED lock.
-
Various bug fixes and optimizations
-}
-
-chng {2004 August 8 (3.0.4 beta)} {
-
CREATE TABLE and DROP TABLE now work correctly as prepared statements.
-
Fix a bug in VACUUM and UNIQUE indices.
-
Add the ".import" command to the command-line shell.
-
Fix a bug that could cause index corruption when an attempt to
- delete rows of a table is blocked by a pending query.
-
Library size optimizations.
-
Other minor bug fixes.
-}
-
-chng {2004 July 22 (2.8.15)} {
-
This is a maintenance release only. Various minor bugs have been
-fixed and some portability enhancements are added.
-}
-
-chng {2004 July 22 (3.0.3 beta)} {
-
The second beta release for SQLite 3.0.
-
Add support for "PRAGMA page_size" to adjust the page size of
-the database.
-
Various bug fixes and documentation updates.
-}
-
-chng {2004 June 30 (3.0.2 beta)} {
-
The first beta release for SQLite 3.0.
-}
-
-chng {2004 June 22 (3.0.1 alpha)} {
-
- *** Alpha Release - Research And Testing Use Only ***
-
Lots of bug fixes.
-}
-
-chng {2004 June 18 (3.0.0 alpha)} {
-
- *** Alpha Release - Research And Testing Use Only ***
-
Support for internationalization including UTF-8, UTF-16, and
- user defined collating sequences.
-
New file format that is 25% to 35% smaller for typical use.
Fix the min() and max() optimizer so that it works when the FROM
- clause consists of a subquery.
-
Ignore extra whitespace at the end of of "." commands in the shell.
-
Bundle sqlite_encode_binary() and sqlite_decode_binary() with the
- library.
-
The TEMP_STORE and DEFAULT_TEMP_STORE pragmas now work.
-
Code changes to compile cleanly using OpenWatcom.
-
Fix VDBE stack overflow problems with INSTEAD OF triggers and
- NULLs in IN operators.
-
Add the global variable sqlite_temp_directory which if set defines the
- directory in which temporary files are stored.
-
sqlite_interrupt() plays well with VACUUM.
-
Other minor bug fixes.
-}
-
-chng {2004 March 8 (2.8.13)} {
-
Refactor parts of the code in order to make the code footprint
- smaller. The code is now also a little bit faster.
-
sqlite_exec() is now implemented as a wrapper around sqlite_compile()
- and sqlite_step().
-
The built-in min() and max() functions now honor the difference between
- NUMERIC and TEXT datatypes. Formerly, min() and max() always assumed
- their arguments were of type NUMERIC.
-
New HH:MM:SS modifier to the built-in date/time functions.
-
Experimental sqlite_last_statement_changes() API added. Fixed the
- the last_insert_rowid() function so that it works correctly with
- triggers.
-
Add functions prototypes for the database encryption API.
-
Fix several nuisance bugs.
-}
-
-chng {2004 February 8 (2.8.12)} {
-
Fix a bug that will might corrupt the rollback journal if a power failure
- or external program halt occurs in the middle of a COMMIT. The corrupt
- journal can lead to database corruption when it is rolled back.
-
Reduce the size and increase the speed of various modules, especially
- the virtual machine.
-
Allow "<expr> IN <table>" as a shorthand for
- "<expr> IN (SELECT * FROM <table>".
-
Optimizations to the sqlite_mprintf() routine.
-
Make sure the MIN() and MAX() optimizations work within subqueries.
-}
-
-chng {2004 January 14 (2.8.11)} {
-
Fix a bug in how the IN operator handles NULLs in subqueries. The bug
- was introduced by the previous release.
-}
-
-chng {2004 January 13 (2.8.10)} {
-
Fix a potential database corruption problem on Unix caused by the fact
- that all posix advisory locks are cleared whenever you close() a file.
- The work around it to embargo all close() calls while locks are
- outstanding.
-
Performance enhancements on some corner cases of COUNT(*).
-
Make sure the in-memory backend response sanely if malloc() fails.
-
Allow sqlite_exec() to be called from within user-defined SQL
- functions.
-
Improved accuracy of floating-point conversions using "long double".
-
Bug fixes in the experimental date/time functions.
-}
-
-chng {2004 January 5 (2.8.9)} {
-
Fix a 32-bit integer overflow problem that could result in corrupt
- indices in a database if large negative numbers (less than -2147483648)
- were inserted into a indexed numeric column.
-
Fix a locking problem on multi-threaded Linux implementations.
-
Always use "." instead of "," as the decimal point even if the locale
- requests ",".
-
Added UTC to localtime conversions to the experimental date/time
- functions.
-
Bug fixes to date/time functions.
-}
-
-chng {2003 December 17 (2.8.8)} {
-
Fix a critical bug introduced into 2.8.0 which could cause
- database corruption.
-
Fix a problem with 3-way joins that do not use indices
-
The VACUUM command now works with the non-callback API
-
Improvements to the "PRAGMA integrity_check" command
-}
-
-chng {2003 December 4 (2.8.7)} {
-
Added experimental sqlite_bind() and sqlite_reset() APIs.
-
If the name of the database is an empty string, open a new database
- in a temporary file that is automatically deleted when the database
- is closed.
-
Performance enhancements in the lemon-generated parser
-
Experimental date/time functions revised.
-
Disallow temporary indices on permanent tables.
-
Documentation updates and typo fixes
-
Added experimental sqlite_progress_handler() callback API
-
Removed support for the Oracle8 outer join syntax.
-
Allow GLOB and LIKE operators to work as functions.
-
Other minor documentation and makefile changes and bug fixes.
-}
-
-chng {2003 August 21 (2.8.6)} {
-
Moved the CVS repository to www.sqlite.org
-
Update the NULL-handling documentation.
-
Experimental date/time functions added.
-
Bug fix: correctly evaluate a view of a view without segfaulting.
-
Bug fix: prevent database corruption if you dropped a
- trigger that had the same name as a table.
-
Bug fix: allow a VACUUM (without segfaulting) on an empty
- database after setting the EMPTY_RESULT_CALLBACKS pragma.
-
Bug fix: if an integer value will not fit in a 32-bit int, store it in
- a double instead.
-
Bug fix: Make sure the journal file directory entry is committed to disk
- before writing the database file.
-}
-
-chng {2003 July 22 (2.8.5)} {
-
Make LIMIT work on a compound SELECT statement.
-
LIMIT 0 now shows no rows. Use LIMIT -1 to see all rows.
-
Correctly handle comparisons between an INTEGER PRIMARY KEY and
- a floating point number.
-
Fix several important bugs in the new ATTACH and DETACH commands.
Allow NULL arguments in sqlite_compile() and sqlite_step().
-
Many minor bug fixes
-}
-
-chng {2003 June 29 (2.8.4)} {
-
Enhanced the "PRAGMA integrity_check" command to verify indices.
-
Added authorization hooks for the new ATTACH and DETACH commands.
-
Many documentation updates
-
Many minor bug fixes
-}
-
-chng {2003 June 4 (2.8.3)} {
-
Fix a problem that will corrupt the indices on a table if you
- do an INSERT OR REPLACE or an UPDATE OR REPLACE on a table that
- contains an INTEGER PRIMARY KEY plus one or more indices.
-
Fix a bug in windows locking code so that locks work correctly
- when simultaneously accessed by Win95 and WinNT systems.
-
Add the ability for INSERT and UPDATE statements to refer to the
- "rowid" (or "_rowid_" or "oid") columns.
-
Other important bug fixes
-}
-
-chng {2003 May 17 (2.8.2)} {
-
Fix a problem that will corrupt the database file if you drop a
- table from the main database that has a TEMP index.
-}
-
-chng {2003 May 16 (2.8.1)} {
-
Reactivated the VACUUM command that reclaims unused disk space in
- a database file.
-
Added the ATTACH and DETACH commands to allow interacting with multiple
- database files at the same time.
-
Added support for TEMP triggers and indices.
-
Added support for in-memory databases.
-
Removed the experimental sqlite_open_aux_file(). Its function is
- subsumed in the new ATTACH command.
-
The precedence order for ON CONFLICT clauses was changed so that
- ON CONFLICT clauses on BEGIN statements have a higher precedence than
- ON CONFLICT clauses on constraints.
-
Many, many bug fixes and compatibility enhancements.
-}
-
-chng {2003 Feb 16 (2.8.0)} {
-
Modified the journal file format to make it more resistant to corruption
- that can occur after an OS crash or power failure.
-
Added a new C/C++ API that does not use callback for returning data.
-}
-
-chng {2003 Jan 25 (2.7.6)} {
-
Performance improvements. The library is now much faster.
-
Added the sqlite_set_authorizer() API. Formal documentation has
- not been written - see the source code comments for instructions on
- how to use this function.
-
Fix a bug in the GLOB operator that was preventing it from working
- with upper-case letters.
-
Various minor bug fixes.
-}
-
-chng {2002 Dec 27 (2.7.5)} {
-
Fix an uninitialized variable in pager.c which could (with a probability
- of about 1 in 4 billion) result in a corrupted database.
-}
-
-chng {2002 Dec 17 (2.7.4)} {
-
Database files can now grow to be up to 2^41 bytes. The old limit
- was 2^31 bytes.
-
The optimizer will now scan tables in the reverse if doing so will
- satisfy an ORDER BY ... DESC clause.
-
The full pathname of the database file is now remembered even if
- a relative path is passed into sqlite_open(). This allows
- the library to continue operating correctly after a chdir().
-
Speed improvements in the VDBE.
-
Lots of little bug fixes.
-}
-
-chng {2002 Oct 30 (2.7.3)} {
-
Various compiler compatibility fixes.
-
Fix a bug in the "expr IN ()" operator.
-
Accept column names in parentheses.
-
Fix a problem with string memory management in the VDBE
-
Fix a bug in the "table_info" pragma"
-
Export the sqlite_function_type() API function in the Windows DLL
-
Fix locking behavior under windows
-
Fix a bug in LEFT OUTER JOIN
-}
-
-chng {2002 Sep 25 (2.7.2)} {
-
Prevent journal file overflows on huge transactions.
-
Fix a memory leak that occurred when sqlite_open() failed.
-
Honor the ORDER BY and LIMIT clause of a SELECT even if the
- result set is used for an INSERT.
-
Do not put write locks on the file used to hold TEMP tables.
-
Added documentation on SELECT DISTINCT and on how SQLite handles NULLs.
-
Fix a problem that was causing poor performance when many thousands
- of SQL statements were executed by a single sqlite_exec() call.
-}
-
-chng {2002 Aug 31 (2.7.1)} {
-
Fix a bug in the ORDER BY logic that was introduced in version 2.7.0
-
C-style comments are now accepted by the tokenizer.
-
INSERT runs a little faster when the source is a SELECT statement.
-}
-
-chng {2002 Aug 25 (2.7.0)} {
-
Make a distinction between numeric and text values when sorting.
- Text values sort according to memcmp(). Numeric values sort in
- numeric order.
-
Allow multiple simultaneous readers under windows by simulating
- the reader/writers locks that are missing from Win95/98/ME.
-
An error is now returned when trying to start a transaction if
- another transaction is already active.
-}
-
-chng {2002 Aug 12 (2.6.3)} {
-
Add the ability to read both little-endian and big-endian databases.
- So database created under SunOS or MacOSX can be read and written
- under Linux or Windows and vice versa.
-
Convert to the new website: http://www.sqlite.org/
-
Allow transactions to span Linux Threads
-
Bug fix in the processing of the ORDER BY clause for GROUP BY queries
-}
-
-chng {2002 Jly 30 (2.6.2)} {
-
Text files read by the COPY command can now have line terminators
- of LF, CRLF, or CR.
-
SQLITE_BUSY is handled correctly if encountered during database
- initialization.
-
Fix to UPDATE triggers on TEMP tables.
-
Documentation updates.
-}
-
-chng {2002 Jly 19 (2.6.1)} {
-
Include a static string in the library that responds to the RCS
- "ident" command and which contains the library version number.
-
Fix an assertion failure that occurred when deleting all rows of
- a table with the "count_changes" pragma turned on.
-
Better error reporting when problems occur during the automatic
- 2.5.6 to 2.6.0 database format upgrade.
-}
-
-chng {2002 Jly 17 (2.6.0)} {
-
Change the format of indices to correct a design flaw the originated
- with version 2.1.0. *** This is an incompatible
- file format change *** When version 2.6.0 or later of the
- library attempts to open a database file created by version 2.5.6 or
- earlier, it will automatically and irreversibly convert the file format.
- Make backup copies of older database files before opening them with
- version 2.6.0 of the library.
-
-}
-
-chng {2002 Jly 7 (2.5.6)} {
-
Fix more problems with rollback. Enhance the test suite to exercise
- the rollback logic extensively in order to prevent any future problems.
-
-}
-
-chng {2002 Jly 6 (2.5.5)} {
-
Fix a bug which could cause database corruption during a rollback.
- This bugs was introduced in version 2.4.0 by the freelist
- optimization of checking [410].
-
Fix a bug in aggregate functions for VIEWs.
-
Other minor changes and enhancements.
-}
-
-chng {2002 Jly 1 (2.5.4)} {
-
Make the "AS" keyword optional again.
-
The datatype of columns now appear in the 4th argument to the
- callback.
-
Added the sqlite_open_aux_file() API, though it is still
- mostly undocumented and untested.
-
Added additional test cases and fixed a few bugs that those
- test cases found.
-}
-
-chng {2002 Jun 24 (2.5.3)} {
-
Bug fix: Database corruption can occur due to the optimization
- that was introduced in version 2.4.0 (check-in [410]). The problem
- should now be fixed. The use of versions 2.4.0 through 2.5.2 is
- not recommended.
-}
-
-chng {2002 Jun 24 (2.5.2)} {
-
Added the new SQLITE_TEMP_MASTER table which records the schema
- for temporary tables in the same way that SQLITE_MASTER does for
- persistent tables.
-
Added an optimization to UNION ALL
-
Fixed a bug in the processing of LEFT OUTER JOIN
-
The LIMIT clause now works on subselects
-
ORDER BY works on subselects
-
There is a new TypeOf() function used to determine if an expression
- is numeric or text.
-
Autoincrement now works for INSERT from a SELECT.
-}
-
-chng {2002 Jun 19 (2.5.1)} {
-
The query optimizer now attempts to implement the ORDER BY clause
- using an index. Sorting is still used if not suitable index is
- available.
-}
-
-chng {2002 Jun 17 (2.5.0)} {
-
Added support for row triggers.
-
Added SQL-92 compliant handling of NULLs.
-
Add support for the full SQL-92 join syntax and LEFT OUTER JOINs.
-
Double-quoted strings interpreted as column names not text literals.
-
Parse (but do not implement) foreign keys.
-
Performance improvements in the parser, pager, and WHERE clause code
- generator.
-
Make the LIMIT clause work on subqueries. (ORDER BY still does not
- work, though.)
-
Added the "%Q" expansion to sqlite_*_printf().
-
Bug fixes too numerous to mention (see the change log).
-}
-
-chng {2002 May 09 (2.4.12)} {
-
Added logic to detect when the library API routines are called out
- of sequence.
-}
-
-chng {2002 May 08 (2.4.11)} {
-
Bug fix: Column names in the result set were not being generated
- correctly for some (rather complex) VIEWs. This could cause a
- segfault under certain circumstances.
-}
-
-chng {2002 May 02 (2.4.10)} {
-
Bug fix: Generate correct column headers when a compound SELECT is used
- as a subquery.
-
Added the sqlite_encode_binary() and sqlite_decode_binary() functions to
- the source tree. But they are not yet linked into the library.
-
Documentation updates.
-
Export the sqlite_changes() function from windows DLLs.
-
Bug fix: Do not attempt the subquery flattening optimization on queries
- that lack a FROM clause. To do so causes a segfault.
-}
-
-chng {2002 Apr 21 (2.4.9)} {
-
Fix a bug that was causing the precompiled binary of SQLITE.EXE to
- report "out of memory" under Windows 98.
-}
-
-chng {2002 Apr 20 (2.4.8)} {
-
Make sure VIEWs are created after their corresponding TABLEs in the
- output of the .dump command in the shell.
-
Speed improvements: Do not do synchronous updates on TEMP tables.
-
Many improvements and enhancements to the shell.
-
Make the GLOB and LIKE operators functions that can be overridden
- by a programmer. This allows, for example, the LIKE operator to
- be changed to be case sensitive.
-}
-
-chng {2002 Apr 06 (2.4.7)} {
-
Add the ability to put TABLE.* in the column list of a
- SELECT statement.
-
Permit SELECT statements without a FROM clause.
-
Added the last_insert_rowid() SQL function.
-
Do not count rows where the IGNORE conflict resolution occurs in
- the row count.
-
Make sure functions expressions in the VALUES clause of an INSERT
- are correct.
-
Added the sqlite_changes() API function to return the number
- of row that changed in the most recent operation.
-}
-
-chng {2002 Apr 02 (2.4.6)} {
-
Bug fix: Correctly handle terms in the WHERE clause of a join that
- do not contain a comparison operator.
-}
-
-chng {2002 Apr 01 (2.4.5)} {
-
Bug fix: Correctly handle functions that appear in the WHERE clause
- of a join.
-
When the PRAGMA vdbe_trace=ON is set, correctly print the P3 operand
- value when it is a pointer to a structure rather than a pointer to
- a string.
-
When inserting an explicit NULL into an INTEGER PRIMARY KEY, convert
- the NULL value into a unique key automatically.
-}
-
-chng {2002 Mar 24 (2.4.4)} {
-
Allow "VIEW" to be a column name
-
Added support for CASE expressions (patch from Dan Kennedy)
-
Added RPMS to the delivery (patches from Doug Henry)
-
Fix typos in the documentation
-
Cut over configuration management to a new CVS repository with
- its own CVSTrac bug tracking system.
-}
-
-chng {2002 Mar 22 (2.4.3)} {
-
Fix a bug in SELECT that occurs when a compound SELECT is used as a
- subquery in the FROM of a SELECT.
-
The sqlite_get_table() function now returns an error if you
- give it two or more SELECTs that return different numbers of columns.
-}
-
-chng {2002 Mar 14 (2.4.2)} {
-
Bug fix: Fix an assertion failure that occurred when ROWID was a column
- in a SELECT statement on a view.
-
Bug fix: Fix an uninitialized variable in the VDBE that would could an
- assert failure.
-
Make the os.h header file more robust in detecting when the compile is
- for windows and when it is for unix.
-}
-
-chng {2002 Mar 13 (2.4.1)} {
-
Using an unnamed subquery in a FROM clause would cause a segfault.
-
The parser now insists on seeing a semicolon or the end of input before
- executing a statement. This avoids an accidental disaster if the
- WHERE keyword is misspelled in an UPDATE or DELETE statement.
-}
-
-
-chng {2002 Mar 10 (2.4.0)} {
-
Change the name of the sanity_check PRAGMA to integrity_check
- and make it available in all compiles.
-
SELECT min() or max() of an indexed column with no WHERE or GROUP BY
- clause is handled as a special case which avoids a complete table scan.
-
Automatically generated ROWIDs are now sequential.
-
Do not allow dot-commands of the command-line shell to occur in the
- middle of a real SQL command.
-
Modifications to the "lemon" parser generator so that the parser tables
- are 4 times smaller.
-
Added support for user-defined functions implemented in C.
-
Added support for new functions: coalesce(), lower(),
- upper(), and random()
-
Added support for VIEWs.
-
Added the subquery flattening optimizer.
-
Modified the B-Tree and Pager modules so that disk pages that do not
- contain real data (free pages) are not journaled and are not
- written from memory back to the disk when they change. This does not
- impact database integrity, since the
- pages contain no real data, but it does make large INSERT operations
- about 2.5 times faster and large DELETEs about 5 times faster.
-
Made the CACHE_SIZE pragma persistent
-
Added the SYNCHRONOUS pragma
-
Fixed a bug that was causing updates to fail inside of transactions when
- the database contained a temporary table.
-}
-
-chng {2002 Feb 18 (2.3.3)} {
-
Allow identifiers to be quoted in square brackets, for compatibility
- with MS-Access.
-
Added support for sub-queries in the FROM clause of a SELECT.
-
More efficient implementation of sqliteFileExists() under Windows.
- (by Joel Luscy)
-
The VALUES clause of an INSERT can now contain expressions, including
- scalar SELECT clauses.
-
Added support for CREATE TABLE AS SELECT
-
Bug fix: Creating and dropping a table all within a single
- transaction was not working.
-}
-
-chng {2002 Feb 14 (2.3.2)} {
-
Bug fix: There was an incorrect assert() in pager.c. The real code was
- all correct (as far as is known) so everything should work OK if you
- compile with -DNDEBUG=1. When asserts are not disabled, there
- could be a fault.
-}
-
-chng {2002 Feb 13 (2.3.1)} {
-
Bug fix: An assertion was failing if "PRAGMA full_column_names=ON;" was
- set and you did a query that used a rowid, like this:
- "SELECT rowid, * FROM ...".
-}
-
-chng {2002 Jan 30 (2.3.0)} {
-
Fix a serious bug in the INSERT command which was causing data to go
- into the wrong columns if the data source was a SELECT and the INSERT
- clauses specified its columns in some order other than the default.
-
Added the ability to resolve constraint conflicts is ways other than
- an abort and rollback. See the documentation on the "ON CONFLICT"
- clause for details.
-
Temporary files are now automatically deleted by the operating system
- when closed. There are no more dangling temporary files on a program
- crash. (If the OS crashes, fsck will delete the file after reboot
- under Unix. I do not know what happens under Windows.)
-
NOT NULL constraints are honored.
-
The COPY command puts NULLs in columns whose data is '\N'.
-
In the COPY command, backslash can now be used to escape a newline.
-
Added the SANITY_CHECK pragma.
-}
-
-chng {2002 Jan 28 (2.2.5)} {
-
Important bug fix: the IN operator was not working if either the
- left-hand or right-hand side was derived from an INTEGER PRIMARY KEY.
-
Do not escape the backslash '\' character in the output of the
- sqlite command-line access program.
-}
-
-chng {2002 Jan 22 (2.2.4)} {
-
The label to the right of an AS in the column list of a SELECT can now
- be used as part of an expression in the WHERE, ORDER BY, GROUP BY, and/or
- HAVING clauses.
-
Fix a bug in the -separator command-line option to the sqlite
- command.
-
Fix a problem with the sort order when comparing upper-case strings against
- characters greater than 'Z' but less than 'a'.
-
Report an error if an ORDER BY or GROUP BY expression is constant.
-}
-
-chng {2002 Jan 16 (2.2.3)} {
-
Fix warning messages in VC++ 7.0. (Patches from nicolas352001)
-
Make the library thread-safe. (The code is there and appears to work
- but has not been stressed.)
-
Added the new sqlite_last_insert_rowid() API function.
-}
-
-chng {2002 Jan 13 (2.2.2)} {
-
Bug fix: An assertion was failing when a temporary table with an index
- had the same name as a permanent table created by a separate process.
-
Bug fix: Updates to tables containing an INTEGER PRIMARY KEY and an
- index could fail.
-}
-
-chng {2002 Jan 9 (2.2.1)} {
-
Bug fix: An attempt to delete a single row of a table with a WHERE
- clause of "ROWID=x" when no such rowid exists was causing an error.
-
Bug fix: Passing in a NULL as the 3rd parameter to sqlite_open()
- would sometimes cause a coredump.
-
Bug fix: DROP TABLE followed by a CREATE TABLE with the same name all
- within a single transaction was causing a coredump.
-
Makefile updates from A. Rottmann
-}
-
-chng {2001 Dec 22 (2.2.0)} {
-
Columns of type INTEGER PRIMARY KEY are actually used as the primary
- key in underlying B-Tree representation of the table.
-
Several obscure, unrelated bugs were found and fixed while
- implemented the integer primary key change of the previous bullet.
-
Added the ability to specify "*" as part of a larger column list in
- the result section of a SELECT statement. For example:
- "SELECT rowid, * FROM table1;".
-
Updates to comments and documentation.
-}
-
-chng {2001 Dec 14 (2.1.7)} {
-
Fix a bug in CREATE TEMPORARY TABLE which was causing the
- table to be initially allocated in the main database file instead
- of in the separate temporary file. This bug could cause the library
- to suffer an assertion failure and it could cause "page leaks" in the
- main database file.
-
Fix a bug in the b-tree subsystem that could sometimes cause the first
- row of a table to be repeated during a database scan.
-}
-
-chng {2001 Dec 14 (2.1.6)} {
-
Fix the locking mechanism yet again to prevent
- sqlite_exec() from returning SQLITE_PROTOCOL
- unnecessarily. This time the bug was a race condition in
- the locking code. This change effects both POSIX and Windows users.
-}
-
-chng {2001 Dec 6 (2.1.5)} {
-
Fix for another problem (unrelated to the one fixed in 2.1.4)
- that sometimes causes sqlite_exec() to return SQLITE_PROTOCOL
- unnecessarily. This time the bug was
- in the POSIX locking code and should not effect windows users.
-}
-
-chng {2001 Dec 4 (2.1.4)} {
-
Sometimes sqlite_exec() would return SQLITE_PROTOCOL when it
- should have returned SQLITE_BUSY.
-
The fix to the previous bug uncovered a deadlock which was also
- fixed.
-
Add the ability to put a single .command in the second argument
- of the sqlite shell
-
Updates to the FAQ
-}
-
-chng {2001 Nov 23 (2.1.3)} {
-
Fix the behavior of comparison operators
- (ex: "<", "==", etc.)
- so that they are consistent with the order of entries in an index.
-
Correct handling of integers in SQL expressions that are larger than
- what can be represented by the machine integer.
-}
-
-chng {2001 Nov 22 (2.1.2)} {
-
Changes to support 64-bit architectures.
-
Fix a bug in the locking protocol.
-
Fix a bug that could (rarely) cause the database to become
- unreadable after a DROP TABLE due to corruption to the SQLITE_MASTER
- table.
-
Change the code so that version 2.1.1 databases that were rendered
- unreadable by the above bug can be read by this version of
- the library even though the SQLITE_MASTER table is (slightly)
- corrupted.
-}
-
-chng {2001 Nov 13 (2.1.1)} {
-
Bug fix: Sometimes arbitrary strings were passed to the callback
- function when the actual value of a column was NULL.
-}
-
-chng {2001 Nov 12 (2.1.0)} {
-
Change the format of data records so that records up to 16MB in size
- can be stored.
-
Change the format of indices to allow for better query optimization.
-
Implement the "LIMIT ... OFFSET ..." clause on SELECT statements.
-}
-
-chng {2001 Nov 3 (2.0.8)} {
-
Made selected parameters in API functions const. This should
- be fully backwards compatible.
-
Documentation updates
-
Simplify the design of the VDBE by restricting the number of sorters
- and lists to 1.
- In practice, no more than one sorter and one list was ever used anyhow.
-
-}
-
-chng {2001 Oct 21 (2.0.7)} {
-
Any UTF-8 character or ISO8859 character can be used as part of
- an identifier.
-
Patches from Christian Werner to improve ODBC compatibility and to
- fix a bug in the round() function.
-
Plug some memory leaks that use to occur if malloc() failed.
- We have been and continue to be memory leak free as long as
- malloc() works.
-
Changes to some test scripts so that they work on Windows in
- addition to Unix.
-}
-
-chng {2001 Oct 19 (2.0.6)} {
-
Added the EMPTY_RESULT_CALLBACKS pragma
-
Support for UTF-8 and ISO8859 characters in column and table names.
-
Bug fix: Compute correct table names with the FULL_COLUMN_NAMES pragma
- is turned on.
-}
-
-chng {2001 Oct 14 (2.0.5)} {
-
Added the COUNT_CHANGES pragma.
-
Changes to the FULL_COLUMN_NAMES pragma to help out the ODBC driver.
-
Bug fix: "SELECT count(*)" was returning NULL for empty tables.
- Now it returns 0.
-}
-
-chng {2001 Oct 13 (2.0.4)} {
-
Bug fix: an obscure and relatively harmless bug was causing one of
- the tests to fail when gcc optimizations are turned on. This release
- fixes the problem.
-}
-
-chng {2001 Oct 13 (2.0.3)} {
-
Bug fix: the sqlite_busy_timeout() function was delaying 1000
- times too long before failing.
-
Bug fix: an assertion was failing if the disk holding the database
- file became full or stopped accepting writes for some other reason.
- New tests were added to detect similar problems in the future.
Fix two bugs in the locking protocol. (One was masking the other.)
-
Removed some unused "#include " that were causing problems
- for VC++.
-
Fixed sqlite.h so that it is usable from C++
-
Added the FULL_COLUMN_NAMES pragma. When set to "ON", the names of
- columns are reported back as TABLE.COLUMN instead of just COLUMN.
-
Added the TABLE_INFO() and INDEX_INFO() pragmas to help support the
- ODBC interface.
-
Added support for TEMPORARY tables and indices.
-}
-
-chng {2001 Oct 2 (2.0.1)} {
-
Remove some C++ style comments from btree.c so that it will compile
- using compilers other than gcc.
-
The ".dump" output from the shell does not work if there are embedded
- newlines anywhere in the data. This is an old bug that was carried
- forward from version 1.0. To fix it, the ".dump" output no longer
- uses the COPY command. It instead generates INSERT statements.
-
Extend the expression syntax to support "expr NOT NULL" (with a
- space between the "NOT" and the "NULL") in addition to "expr NOTNULL"
- (with no space).
-}
-
-chng {2001 Sep 28 (2.0.0)} {
-
Automatically build binaries for Linux and Windows and put them on
- the website.
-}
-
-chng {2001 Sep 28 (2.0-alpha-4)} {
-
Incorporate makefile patches form A. Rottmann to use LIBTOOL
-}
-
-chng {2001 Sep 27 (2.0-alpha-3)} {
-
SQLite now honors the UNIQUE keyword in CREATE UNIQUE INDEX. Primary
- keys are required to be unique.
-
File format changed back to what it was for alpha-1
-
Fixes to the rollback and locking behavior
-}
-
-chng {2001 Sep 20 (2.0-alpha-2)} {
-
Initial release of version 2.0. The idea of renaming the library
- to "SQLus" was abandoned in favor of keeping the "SQLite" name and
- bumping the major version number.
-
The pager and btree subsystems added back. They are now the only
- available backend.
-
The Dbbe abstraction and the GDBM and memory drivers were removed.
-
Copyright on all code was disclaimed. The library is now in the
- public domain.
-}
-
-chng {2001 Jul 23 (1.0.32)} {
-
Pager and btree subsystems removed. These will be used in a follow-on
- SQL server library named "SQLus".
-
Add the ability to use quoted strings as table and column names in
- expressions.
-}
-
-chng {2001 Apr 14 (1.0.31)} {
-
Pager subsystem added but not yet used.
-
More robust handling of out-of-memory errors.
-
New tests added to the test suite.
-}
-
-chng {2001 Apr 6 (1.0.30)} {
-
Remove the sqlite_encoding TCL variable that was introduced
- in the previous version.
-
Add options -encoding and -tcl-uses-utf to the
- sqlite TCL command.
-
Add tests to make sure that tclsqlite was compiled using Tcl header
- files and libraries that match.
-}
-
-chng {2001 Apr 5 (1.0.29)} {
-
The library now assumes data is stored as UTF-8 if the --enable-utf8
- option is given to configure. The default behavior is to assume
- iso8859-x, as it has always done. This only makes a difference for
- LIKE and GLOB operators and the LENGTH and SUBSTR functions.
-
If the library is not configured for UTF-8 and the Tcl library
- is one of the newer ones that uses UTF-8 internally,
- then a conversion from UTF-8 to iso8859 and
- back again is done inside the TCL interface.
-}
-
-chng {2001 Apr 4 (1.0.28)} {
-
Added limited support for transactions. At this point, transactions
- will do table locking on the GDBM backend. There is no support (yet)
- for rollback or atomic commit.
-
Added special column names ROWID, OID, and _ROWID_ that refer to the
- unique random integer key associated with every row of every table.
-
Additional tests added to the regression suite to cover the new ROWID
- feature and the TCL interface bugs mentioned below.
-
Changes to the "lemon" parser generator to help it work better when
- compiled using MSVC.
-
Bug fixes in the TCL interface identified by Oleg Oleinick.
-}
-
-chng {2001 Mar 20 (1.0.27)} {
-
When doing DELETE and UPDATE, the library used to write the record
- numbers of records to be deleted or updated into a temporary file.
- This is changed so that the record numbers are held in memory.
-
The DELETE command without a WHILE clause just removes the database
- files from the disk, rather than going through and deleting record
- by record.
-}
-
-chng {2001 Mar 20 (1.0.26)} {
-
A serious bug fixed on Windows. Windows users should upgrade.
- No impact to Unix.
-}
-
-chng {2001 Mar 15 (1.0.25)} {
-
Modify the test scripts to identify tests that depend on system
- load and processor speed and
- to warn the user that a failure of one of those (rare) tests does
- not necessarily mean the library is malfunctioning. No changes to
- code.
-
-}
-
-chng {2001 Mar 14 (1.0.24)} {
-
Fix a bug which was causing
- the UPDATE command to fail on systems where "malloc(0)" returns
- NULL. The problem does not appear Windows, Linux, or HPUX but does
- cause the library to fail on QNX.
-
-}
-
-chng {2001 Feb 19 (1.0.23)} {
-
An unrelated (and minor) bug from Mark Muranwski fixed. The algorithm
- for figuring out where to put temporary files for a "memory:" database
- was not working quite right.
-
-}
-
-chng {2001 Feb 19 (1.0.22)} {
-
The previous fix was not quite right. This one seems to work better.
-
-}
-
-chng {2001 Feb 19 (1.0.21)} {
-
The UPDATE statement was not working when the WHERE clause contained
- some terms that could be satisfied using indices and other terms that
- could not. Fixed.
-}
-
-chng {2001 Feb 11 (1.0.20)} {
-
Merge development changes into the main trunk. Future work toward
- using a BTree file structure will use a separate CVS source tree. This
- CVS tree will continue to support the GDBM version of SQLite only.
-}
-
-chng {2001 Feb 6 (1.0.19)} {
-
Fix a strange (but valid) C declaration that was causing problems
- for QNX. No logical changes.
-}
-
-chng {2001 Jan 4 (1.0.18)} {
-
Print the offending SQL statement when an error occurs.
-
Do not require commas between constraints in CREATE TABLE statements.
-
Added the "-echo" option to the shell.
-
Changes to comments.
-}
-
-chng {2000 Dec 10 (1.0.17)} {
-
Rewrote sqlite_complete() to make it faster.
-
Minor tweaks to other code to make it run a little faster.
-
Added new tests for sqlite_complete() and for memory leaks.
-}
-
-chng {2000 Dec 4 (1.0.16)} {
-
Documentation updates. Mostly fixing of typos and spelling errors.
-}
-
-chng {2000 Oct 23 (1.0.15)} {
-
Documentation updates
-
Some sanity checking code was removed from the inner loop of vdbe.c
- to help the library to run a little faster. The code is only
- removed if you compile with -DNDEBUG.
-}
-
-chng {2000 Oct 19 (1.0.14)} {
-
Added a "memory:" backend driver that stores its database in an
- in-memory hash table.
-}
-
-chng {2000 Oct 18 (1.0.13)} {
-
Break out the GDBM driver into a separate file in anticipation
- to added new drivers.
-
Allow the name of a database to be prefixed by the driver type.
- For now, the only driver type is "gdbm:".
-}
-
-chng {2000 Oct 16 (1.0.12)} {
-
Fixed an off-by-one error that was causing a coredump in
- the '%q' format directive of the new
- sqlite_..._printf() routines.
-
Added the sqlite_interrupt() interface.
-
In the shell, sqlite_interrupt() is invoked when the
- user presses Control-C
-
Fixed some instances where sqlite_exec() was
- returning the wrong error code.
-}
-
-chng {2000 Oct 11 (1.0.10)} {
-
Added notes on how to compile for Windows95/98.
-
Removed a few variables that were not being used. Etc.
-}
-
-chng {2000 Oct 8 (1.0.9)} {
-
Added the sqlite_..._printf() interface routines.
-
Modified the sqlite shell program to use the new interface
- routines.
-
Modified the sqlite shell program to print the schema for
- the built-in SQLITE_MASTER table, if explicitly requested.
-}
-
-chng {2000 Sep 30 (1.0.8)} {
-
Begin writing documentation on the TCL interface.
-}
-
-chng {2000 Sep 29 (Not Released)} {
-
Added the sqlite_get_table() API
-
Updated the documentation for due to the above change.
-
Modified the sqlite shell to make use of the new
- sqlite_get_table() API in order to print a list of tables
- in multiple columns, similar to the way "ls" prints filenames.
-
Modified the sqlite shell to print a semicolon at the
- end of each CREATE statement in the output of the ".schema" command.
-}
-
-chng {2000 Sep 21 (Not Released)} {
-
Change the tclsqlite "eval" method to return a list of results if
- no callback script is specified.
-
Change tclsqlite.c to use the Tcl_Obj interface
-
Add tclsqlite.c to the libsqlite.a library
-}
-
-chng {2000 Sep 13 (Version 1.0.5)} {
-
Changed the print format for floating point values from "%g" to "%.15g".
-
-
Changed the comparison function so that numbers in exponential notation
- (ex: 1.234e+05) sort in numerical order.
-}
-
-chng {2000 Aug 28 (Version 1.0.4)} {
-
Added functions length() and substr().
-
Fix a bug in the sqlite shell program that was causing
- a coredump when the output mode was "column" and the first row
- of data contained a NULL.
-}
-
-chng {2000 Aug 22 (Version 1.0.3)} {
-
In the sqlite shell, print the "Database opened READ ONLY" message
- to stderr instead of stdout.
-
In the sqlite shell, now print the version number on initial startup.
-
Add the sqlite_version[] string constant to the library
-
Makefile updates
-
Bug fix: incorrect VDBE code was being generated for the following
- circumstance: a query on an indexed table containing a WHERE clause with
- an IN operator that had a subquery on its right-hand side.
-}
-
-chng {2000 Aug 18 (Version 1.0.1)} {
-
Fix a bug in the configure script.
-
Minor revisions to the website.
-}
-
-chng {2000 Aug 17 (Version 1.0)} {
-
Change the sqlite program so that it can read
- databases for which it lacks write permission. (It used to
- refuse all access if it could not write.)
-}
-
-chng {2000 Aug 9} {
-
Treat carriage returns as white space.
-}
-
-chng {2000 Aug 8} {
-
Added pattern matching to the ".table" command in the "sqlite"
-command shell.
-}
-
-chng {2000 Aug 4} {
-
Documentation updates
-
Added "busy" and "timeout" methods to the Tcl interface
-}
-
-chng {2000 Aug 3} {
-
File format version number was being stored in sqlite_master.tcl
- multiple times. This was harmless, but unnecessary. It is now fixed.
-}
-
-chng {2000 Aug 2} {
-
The file format for indices was changed slightly in order to work
- around an inefficiency that can sometimes come up with GDBM when
- there are large indices having many entries with the same key.
- ** Incompatible Change **
-}
-
-chng {2000 Aug 1} {
-
The parser's stack was overflowing on a very long UPDATE statement.
- This is now fixed.
Clean up comments and variable names. Changes to documentation.
- No functional changes to the code.
-}
-
-chng {2000 June 19} {
-
Column names in UPDATE statements were case sensitive.
- This mistake has now been fixed.
-}
-
-chng {2000 June 16} {
-
Added the concatenate string operator (||)
-}
-
-chng {2000 June 12} {
-
Added the fcnt() function to the SQL interpreter. The fcnt() function
- returns the number of database "Fetch" operations that have occurred.
- This function is designed for use in test scripts to verify that
- queries are efficient and appropriately optimized. Fcnt() has no other
- useful purpose, as far as I know.
-
Added a bunch more tests that take advantage of the new fcnt() function.
- The new tests did not uncover any new problems.
-}
-
-chng {2000 June 8} {
-
Added lots of new test cases
-
Fix a few bugs discovered while adding test cases
-
Begin adding lots of new documentation
-}
-
-chng {2000 June 6} {
-
Added compound select operators: UNION, UNION ALL,
-INTERSECT, and EXCEPT
-
Added support for using (SELECT ...) within expressions
-
Added support for IN and BETWEEN operators
-
Added support for GROUP BY and HAVING
-
NULL values are now reported to the callback as a NULL pointer
- rather than an empty string.
-}
-
-chng {2000 June 3} {
-
Added support for default values on columns of a table.
-
Improved test coverage. Fixed a few obscure bugs found by the
-improved tests.
-}
-
-chng {2000 June 2} {
-
All database files to be modified by an UPDATE, INSERT or DELETE are
-now locked before any changes are made to any files.
-This makes it safe (I think) to access
-the same database simultaneously from multiple processes.
-
The code appears stable so we are now calling it "beta".
-}
-
-chng {2000 June 1} {
-
Better support for file locking so that two or more processes
-(or threads)
-can access the same database simultaneously. More work needed in
-this area, though.
-}
-
-chng {2000 May 31} {
-
Added support for aggregate functions (Ex: COUNT(*), MIN(...))
-to the SELECT statement.
-
Added support for SELECT DISTINCT ...
-}
-
-chng {2000 May 30} {
-
Added the LIKE operator.
-
Added a GLOB operator: similar to LIKE
-but it uses Unix shell globbing wildcards instead of the '%'
-and '_' wildcards of SQL.
-
Added the COPY command patterned after
-PostgreSQL so that SQLite
-can now read the output of the pg_dump database dump utility
-of PostgreSQL.
-
Added a VACUUM command that that calls the
-gdbm_reorganize() function on the underlying database
-files.
-
And many, many bug fixes...
-}
-
-chng {2000 May 29} {
-
Initial Public Release of Alpha code
-}
-
-puts {
-
-}
-footer {$Id:}
DELETED www/common.tcl
Index: www/common.tcl
==================================================================
--- www/common.tcl
+++ /dev/null
@@ -1,90 +0,0 @@
-# This file contains TCL procedures used to generate standard parts of
-# web pages.
-#
-
-proc header {txt} {
- puts "$txt"
- puts {
}
- set date [lrange $rcsid 3 4]
- if {$date!=""} {
- puts "This page last modified on $date"
- }
- puts {}
-}
-
-
-# The following proc is used to ensure consistent formatting in the
-# HTML generated by lang.tcl and pragma.tcl.
-#
-proc Syntax {args} {
- puts {
}
- foreach {rule body} $args {
- puts "
"
- puts "$rule ::=
"
- regsub -all < $body {%LT} body
- regsub -all > $body {%GT} body
- regsub -all %LT $body {} body
- regsub -all %GT $body {} body
- regsub -all {[]|[*?]} $body {&} body
- regsub -all "\n" [string trim $body] " \n" body
- regsub -all "\n *" $body "\n\\ \\ \\ \\ " body
- regsub -all {[|,.*()]} $body {&} body
- regsub -all { = } $body { = } body
- regsub -all {STAR} $body {*} body
- ## These metacharacters must be handled to undo being
- ## treated as SQL punctuation characters above.
- regsub -all {RPPLUS} $body {)+} body
- regsub -all {LP} $body {(} body
- regsub -all {RP} $body {)} body
- ## Place the left-hand side of the rule in the 2nd table column.
- puts "
$body
"
- }
- puts {
}
-}
DELETED www/compile.tcl
Index: www/compile.tcl
==================================================================
--- www/compile.tcl
+++ /dev/null
@@ -1,278 +0,0 @@
-#
-# Run this Tcl script to generate the compile.html file.
-#
-set rcsid {$Id: compile.tcl,v 1.5 2005/03/19 15:10:45 drh Exp $ }
-source common.tcl
-header {Compilation Options For SQLite}
-
-puts {
-
Compilation Options For SQLite
-
-
-For most purposes, SQLite can be built just fine using the default
-compilation options. However, if required, the compile-time options
-documented below can be used to
-omit SQLite features (resulting in
-a smaller compiled library size) or to change the
-default values of some parameters.
-
-
-Every effort has been made to ensure that the various combinations
-of compilation options work harmoniously and produce a working library.
-Nevertheless, it is strongly recommended that the SQLite test-suite
-be executed to check for errors before using an SQLite library built
-with non-standard compilation options.
-
-
-
Options To Set Default Parameter Values
-
-
SQLITE_DEFAULT_AUTOVACUUM=<1 or 0>
-This macro determines if SQLite creates databases with the
-auto-vacuum
-flag set by default. The default value is 0 (do not create auto-vacuum
-databases). In any case the compile-time default may be overridden by the
-"PRAGMA auto_vacuum" command.
-
-
-
SQLITE_DEFAULT_CACHE_SIZE=<pages>
-This macro sets the default size of the page-cache for each attached
-database, in pages. This can be overridden by the "PRAGMA cache_size"
-comamnd. The default value is 2000.
-
-
-
SQLITE_DEFAULT_PAGE_SIZE=<bytes>
-This macro is used to set the default page-size used when a
-database is created. The value assigned must be a power of 2. The
-default value is 1024. The compile-time default may be overridden at
-runtime by the "PRAGMA page_size" command.
-
-
-
SQLITE_DEFAULT_TEMP_CACHE_SIZE=<pages>
-This macro sets the default size of the page-cache for temporary files
-created by SQLite to store intermediate results, in pages. It does
-not affect the page-cache for the temp database, where tables created
-using "CREATE TEMP TABLE" are stored. The default value is 500.
-
-
-
SQLITE_MAX_PAGE_SIZE=<bytes>
-This is used to set the maximum allowable page-size that can
-be specified by the "PRAGMA page_size" command. The default value
-is 8192.
-
-
-
-
Options To Omit Features
-
-
The following options are used to reduce the size of the compiled
-library by omiting optional features. This is probably only useful
-in embedded systems where space is especially tight, as even with all
-features included the SQLite library is relatively small. Don't forget
-to tell your compiler to optimize for binary size! (the -Os option if
-using GCC).
-
-
The macros in this section do not require values. The following
-compilation switches all have the same effect:
--DSQLITE_OMIT_ALTERTABLE
--DSQLITE_OMIT_ALTERTABLE=1
--DSQLITE_OMIT_ALTERTABLE=0
-
-
-
If any of these options are defined, then the same set of SQLITE_OMIT_XXX
-options must also be defined when using the 'lemon' tool to generate a parse.c
-file. Because of this, these options may only used when the library is built
-from source, not from the collection of pre-packaged C files provided for
-non-UNIX like platforms on the website.
-
-
-
SQLITE_OMIT_ALTERTABLE
-When this option is defined, the
-ALTER TABLE command is not included in the
-library. Executing an ALTER TABLE statement causes a parse error.
-
-
-
SQLITE_OMIT_AUTHORIZATION
-Defining this option omits the authorization callback feature from the
-library. The
-sqlite3_set_authorizer() API function is not present in the library.
-
-
-
SQLITE_OMIT_AUTOVACUUM
-If this option is defined, the library cannot create or write to
-databases that support
-auto-vacuum. Executing a
-"PRAGMA auto_vacuum" statement is not an error, but does not return a value
-or modify the auto-vacuum flag in the database file. If a database that
-supports auto-vacuum is opened by a library compiled with this option, it
-is automatically opened in read-only mode.
-
-
-
SQLITE_OMIT_AUTOINCREMENT
-This option is used to omit the AUTOINCREMENT functionality. When this
-is macro is defined, columns declared as "INTEGER PRIMARY KEY AUTOINCREMENT"
-behave in the same way as columns declared as "INTEGER PRIMARY KEY" when a
-NULL is inserted. The sqlite_sequence system table is neither created, nor
-respected if it already exists.
-
-
TODO: Need a link here - AUTOINCREMENT is not yet documented
-
-
SQLITE_OMIT_BLOB_LITERAL
-When this option is defined, it is not possible to specify a blob in
-an SQL statement using the X'ABCD' syntax.
-}
-#
WARNING: The VACUUM command depends on this syntax for vacuuming databases
-#that contain blobs, so disabling this functionality may render a database
-#unvacuumable.
-#
-#
TODO: Need a link here - is that syntax documented anywhere?
-puts {
-
-
SQLITE_OMIT_COMPLETE
-This option causes the
-sqlite3_complete API to be omitted.
-
-
-
SQLITE_OMIT_COMPOUND_SELECT
-This option is used to omit the compound SELECT functionality.
-SELECT statements that use the
-UNION, UNION ALL, INTERSECT or EXCEPT compound SELECT operators will
-cause a parse error.
-
-
-
SQLITE_OMIT_CONFLICT_CLAUSE
-In the future, this option will be used to omit the
-ON CONFLICT clause from the library.
-
-
-
SQLITE_OMIT_DATETIME_FUNCS
-If this option is defined, SQLite's built-in date and time manipulation
-functions are omitted. Specifically, the SQL functions julianday(), date(),
-time(), datetime() and strftime() are not available. The default column
-values CURRENT_TIME, CURRENT_DATE and CURRENT_DATETIME are still available.
-
-
-
SQLITE_OMIT_EXPLAIN
-Defining this option causes the EXPLAIN command to be omitted from the
-library. Attempting to execute an EXPLAIN statement will cause a parse
-error.
-
-
-
SQLITE_OMIT_FLOATING_POINT
-This option is used to omit floating-point number support from the SQLite
-library. When specified, specifying a floating point number as a literal
-(i.e. "1.01") results in a parse error.
-
-
In the future, this option may also disable other floating point
-functionality, for example the sqlite3_result_double(),
-sqlite3_bind_double(), sqlite3_value_double() and sqlite3_column_double()
-API functions.
-
-
-
SQLITE_OMIT_FOREIGN_KEY
-If this option is defined, FOREIGN KEY clauses in column declarations are
-ignored.
-
-
-
SQLITE_OMIT_INTEGRITY_CHECK
-This option may be used to omit the
-"PRAGMA integrity_check"
-command from the compiled library.
-
-
-
SQLITE_OMIT_MEMORYDB
-When this is defined, the library does not respect the special database
-name ":memory:" (normally used to create an in-memory database). If
-":memory:" is passed to sqlite3_open(), a file with this name will be
-opened or created.
-
-
-
SQLITE_OMIT_PAGER_PRAGMAS
-Defining this option omits pragmas related to the pager subsystem from
-the build. Currently, the
-default_cache_size and
-cache_size pragmas are omitted.
-
-
-
SQLITE_OMIT_PRAGMA
-This option is used to omit the PRAGMA command
-from the library. Note that it is useful to define the macros that omit
-specific pragmas in addition to this, as they may also remove supporting code
-in other sub-systems. This macro removes the PRAGMA command only.
-
-
-
SQLITE_OMIT_PROGRESS_CALLBACK
-This option may be defined to omit the capability to issue "progress"
-callbacks during long-running SQL statements. The
-sqlite3_progress_handler()
-API function is not present in the library.
-
-
SQLITE_OMIT_REINDEX
-When this option is defined, the REINDEX
-command is not included in the library. Executing a REINDEX statement causes
-a parse error.
-
-
-
SQLITE_OMIT_SCHEMA_PRAGMAS
-Defining this option omits pragmas for querying the database schema from
-the build. Currently, the
-table_info,
-index_info,
-index_list and
-database_list
-pragmas are omitted.
-
-
-
SQLITE_OMIT_SCHEMA_VERSION_PRAGMAS
-Defining this option omits pragmas for querying and modifying the
-database schema version and user version from the build. Specifically, the
-schema_version and
-user_version
-pragmas are omitted.
-
-
SQLITE_OMIT_SUBQUERY
-
If defined, support for sub-selects and the IN() operator are omitted.
-
-
-
SQLITE_OMIT_TCL_VARIABLE
-
If this macro is defined, then the special "$" syntax
-used to automatically bind SQL variables to TCL variables is omitted.
-
-
-
SQLITE_OMIT_TRIGGER
-Defining this option omits support for VIEW objects. Neither the
-CREATE TRIGGER or
-DROP TRIGGER
-commands are available in this case, attempting to execute either will result
-in a parse error.
-
-
-WARNING: If this macro is defined, it will not be possible to open a database
-for which the schema contains TRIGGER objects.
-
-
-
SQLITE_OMIT_UTF16
-This macro is used to omit support for UTF16 text encoding. When this is
-defined all API functions that return or accept UTF16 encoded text are
-unavailable. These functions can be identified by the fact that they end
-with '16', for example sqlite3_prepare16(), sqlite3_column_text16() and
-sqlite3_bind_text16().
-
-
-
SQLITE_OMIT_VACUUM
-When this option is defined, the VACUUM
-command is not included in the library. Executing a VACUUM statement causes
-a parse error.
-
-
-
SQLITE_OMIT_VIEW
-Defining this option omits support for VIEW objects. Neither the
-CREATE VIEW or
-DROP VIEW
-commands are available in this case, attempting to execute either will result
-in a parse error.
-
-
-WARNING: If this macro is defined, it will not be possible to open a database
-for which the schema contains VIEW objects.
-
-}
-footer $rcsid
DELETED www/conflict.tcl
Index: www/conflict.tcl
==================================================================
--- www/conflict.tcl
+++ /dev/null
@@ -1,91 +0,0 @@
-#
-# Run this Tcl script to generate the constraint.html file.
-#
-set rcsid {$Id: conflict.tcl,v 1.4 2004/10/10 17:24:55 drh Exp $ }
-source common.tcl
-header {Constraint Conflict Resolution in SQLite}
-puts {
-
Constraint Conflict Resolution in SQLite
-
-
-In most SQL databases, if you have a UNIQUE constraint on
-a table and you try to do an UPDATE or INSERT that violates
-the constraint, the database will abort the operation in
-progress, back out any prior changes associated with
-UPDATE or INSERT command, and return an error.
-This is the default behavior of SQLite.
-Beginning with version 2.3.0, though, SQLite allows you to
-define alternative ways for dealing with constraint violations.
-This article describes those alternatives and how to use them.
-
-
-
Conflict Resolution Algorithms
-
-
-SQLite defines five constraint conflict resolution algorithms
-as follows:
-
-
-
-
ROLLBACK
-
When a constraint violation occurs, an immediate ROLLBACK
-occurs, thus ending the current transaction, and the command aborts
-with a return code of SQLITE_CONSTRAINT. If no transaction is
-active (other than the implied transaction that is created on every
-command) then this algorithm works the same as ABORT.
-
-
ABORT
-
When a constraint violation occurs, the command backs out
-any prior changes it might have made and aborts with a return code
-of SQLITE_CONSTRAINT. But no ROLLBACK is executed so changes
-from prior commands within the same transaction
-are preserved. This is the default behavior for SQLite.
-
-
FAIL
-
When a constraint violation occurs, the command aborts with a
-return code SQLITE_CONSTRAINT. But any changes to the database that
-the command made prior to encountering the constraint violation
-are preserved and are not backed out. For example, if an UPDATE
-statement encountered a constraint violation on the 100th row that
-it attempts to update, then the first 99 row changes are preserved
-by change to rows 100 and beyond never occur.
-
-
IGNORE
-
When a constraint violation occurs, the one row that contains
-the constraint violation is not inserted or changed. But the command
-continues executing normally. Other rows before and after the row that
-contained the constraint violation continue to be inserted or updated
-normally. No error is returned.
-
-
REPLACE
-
When a UNIQUE constraint violation occurs, the pre-existing row
-that caused the constraint violation is removed prior to inserting
-or updating the current row. Thus the insert or update always occurs.
-The command continues executing normally. No error is returned.
-
-
-
Why So Many Choices?
-
-
SQLite provides multiple conflict resolution algorithms for a
-couple of reasons. First, SQLite tries to be roughly compatible with as
-many other SQL databases as possible, but different SQL database
-engines exhibit different conflict resolution strategies. For
-example, PostgreSQL always uses ROLLBACK, Oracle always uses ABORT, and
-MySQL usually uses FAIL but can be instructed to use IGNORE or REPLACE.
-By supporting all five alternatives, SQLite provides maximum
-portability.
-
-
Another reason for supporting multiple algorithms is that sometimes
-it is useful to use an algorithm other than the default.
-Suppose, for example, you are
-inserting 1000 records into a database, all within a single
-transaction, but one of those records is malformed and causes
-a constraint error. Under PostgreSQL or Oracle, none of the
-1000 records would get inserted. In MySQL, some subset of the
-records that appeared before the malformed record would be inserted
-but the rest would not. Neither behavior is especially helpful.
-What you really want is to use the IGNORE algorithm to insert
-all but the malformed record.
-SQLite is software that implements an embeddable SQL database engine.
-SQLite is available for free download from http://www.sqlite.org/.
-The principal author and maintainer of SQLite has disclaimed all
-copyright interest in his contributions to SQLite
-and thus released his contributions into the public domain.
-In order to keep the SQLite software unencumbered by copyright
-claims, the principal author asks others who may from time to
-time contribute changes and enhancements to likewise disclaim
-their own individual copyright interest.
-
-
-
-Because the SQLite software found at http://www.sqlite.org/ is in the
-public domain, anyone is free to download the SQLite software
-from that website, make changes to the software, use, distribute,
-or sell the modified software, under either the original name or
-under some new name, without any need to obtain permission, pay
-royalties, acknowledge the original source of the software, or
-in any other way compensate, identify, or notify the original authors.
-Nobody is in any way compelled to contribute their SQLite changes and
-enhancements back to the SQLite website. This document concerns
-only changes and enhancements to SQLite that are intentionally and
-deliberately contributed back to the SQLite website.
-
-
-
-For the purposes of this document, "SQLite software" shall mean any
-computer source code, documentation, makefiles, test scripts, or
-other information that is published on the SQLite website,
-http://www.sqlite.org/. Precompiled binaries are excluded from
-the definition of "SQLite software" in this document because the
-process of compiling the software may introduce information from
-outside sources which is not properly a part of SQLite.
-
-
-
-The header comments on the SQLite source files exhort the reader to
-share freely and to never take more than one gives.
-In the spirit of that exhortation I make the following declarations:
-
-
-
-
-I dedicate to the public domain
-any and all copyright interest in the SQLite software that
-was publicly available on the SQLite website (http://www.sqlite.org/) prior
-to the date of the signature below and any changes or enhancements to
-the SQLite software
-that I may cause to be published on that website in the future.
-I make this dedication for the benefit of the public at large and
-to the detriment of my heirs and successors. I intend this
-dedication to be an overt act of relinquishment in perpetuity of
-all present and future rights to the SQLite software under copyright
-law.
-
-
-
-To the best of my knowledge and belief, the changes and enhancements that
-I have contributed to SQLite are either originally written by me
-or are derived from prior works which I have verified are also
-in the public domain and are not subject to claims of copyright
-by other parties.
-
-
-
-To the best of my knowledge and belief, no individual, business, organization,
-government, or other entity has any copyright interest
-in the SQLite software as it existed on the
-SQLite website as of the date on the signature line below.
-
-
-
-I agree never to publish any additional information
-to the SQLite website (by CVS, email, scp, FTP, or any other means) unless
-that information is an original work of authorship by me or is derived from
-prior published versions of SQLite.
-I agree never to copy and paste code into the SQLite code base from
-other sources.
-I agree never to publish on the SQLite website any information that
-would violate a law or breach a contract.
-
-All of the deliverable code in SQLite has been dedicated to the
-public domain
-by the authors.
-All code authors, and representatives of the companies they work for,
-have signed affidavits dedicating their contributions to
-the public domain and originals of
-those signed affidavits are stored in a firesafe at the main offices
-of Hwaci.
-Anyone is free to copy, modify, publish, use, compile, sell, or distribute
-the original SQLite code, either in source code form or as a compiled binary,
-for any purpose, commercial or non-commercial, and by any means.
-
-
-
-The previous paragraph applies to the deliverable code in SQLite -
-those parts of the SQLite library that you actually bundle and
-ship with a larger application. Portions of the documentation and
-some code used as part of the build process might fall under
-other licenses. The details here are unclear. We do not worry
-about the licensing of the documentation and build code so much
-because none of these things are part of the core deliverable
-SQLite library.
-
-
-
-All of the deliverable code in SQLite has been written from scratch.
-No code has been taken from other projects or from the open
-internet. Every line of code can be traced back to its original
-author, and all of those authors have public domain dedications
-on file. So the SQLite code base is clean and is
-uncontaminated with licensed code from other projects.
-
-
-
Obtaining An Explicit License To Use SQLite
-
-
-Even though SQLite is in the public domain and does not require
-a license, some users want to obtain a license anyway. Some reasons
-for obtaining a license include:
-
-
-
-
You are using SQLite in a jurisdiction that does not recognize
- the public domain.
-
You are using SQLite in a jurisdiction that does not recognize
- the right of an author to dedicate their work to the public
- domain.
-
You want to hold a tangible legal document
- as evidence that you have the legal right to use and distribute
- SQLite.
-
Your legal department tells you that you have to purchase a license.
-
-
-
-
-If you feel like you really have to purchase a license for SQLite,
-Hwaci, the company that employs
-the architect and principal developers of SQLite, will sell you
-one.
-Please contact:
-
-
-
-D. Richard Hipp
-Hwaci - Applied Software Research
-704.948.4565
-drh@hwaci.com
-
-
-
Contributed Code
-
-
-In order to keep SQLite completely free and unencumbered by copyright,
-all new contributors to the SQLite code base are asked to dedicate
-their contributions to the public domain.
-If you want to send a patch or enhancement for possible inclusion in the
-SQLite source tree, please accompany the patch with the following statement:
-
-
-
-The author or authors of this code dedicate any and all copyright interest
-in this code to the public domain. We make this dedication for the benefit
-of the public at large and to the detriment of our heirs and successors.
-We intend this dedication to be an overt act of relinquishment in
-perpetuity of all present and future rights to this code under copyright law.
-
-
-
-We are not able to accept patches or changes to
-SQLite that are not accompanied by a statement such as the above.
-In addition, if you make
-changes or enhancements as an employee, then a simple statement such as the
-above is insufficient. You must also send by surface mail a copyright release
-signed by a company officer.
-A signed original of the copyright release should be mailed to:
-
-
-Hwaci
-6200 Maple Cove Lane
-Charlotte, NC 28269
-USA
-
-
-
-A template copyright release is available
-in PDF or
-HTML.
-You can use this release to make future changes.
-
Version 2 of SQLite stores all column values as ASCII text.
-Version 3 enhances this by providing the ability to store integer and
-real numbers in a more compact format and the capability to store
-BLOB data.
-
-
Each value stored in an SQLite database (or manipulated by the
-database engine) has one of the following storage classes:
-
-
NULL. The value is a NULL value.
-
INTEGER. The value is a signed integer, stored in 1,
- 2, 3, 4, 6, or 8 bytes depending on the magnitude of the value.
-
REAL. The value is a floating point value, stored as
- an 8-byte IEEE floating point number.
-
TEXT. The value is a text string, stored using the
- database encoding (UTF-8, UTF-16BE or UTF-16-LE).
-
BLOB. The value is a blob of data, stored exactly as
- it was input.
-
-
-
As in SQLite version 2, any column in a version 3 database except an INTEGER
-PRIMARY KEY may be used to store any type of value. The exception to
-this rule is described below under 'Strict Affinity Mode'.
-
-
All values supplied to SQLite, whether as literals embedded in SQL
-statements or values bound to pre-compiled SQL statements
-are assigned a storage class before the SQL statement is executed.
-Under circumstances described below, the
-database engine may convert values between numeric storage classes
-(INTEGER and REAL) and TEXT during query execution.
-
-
-
Storage classes are initially assigned as follows:
-
-
Values specified as literals as part of SQL statements are
- assigned storage class TEXT if they are enclosed by single or double
- quotes, INTEGER if the literal is specified as an unquoted number
- with no decimal point or exponent, REAL if the literal is an
- unquoted number with a decimal point or exponent and NULL if the
- value is a NULL. Literals with storage class BLOB are specified
- using the X'ABCD' notation.
-
Values supplied using the sqlite3_bind_* APIs are assigned
- the storage class that most closely matches the native type bound
- (i.e. sqlite3_bind_blob() binds a value with storage class BLOB).
-
-
The storage class of a value that is the result of an SQL scalar
-operator depends on the outermost operator of the expression.
-User-defined functions may return values with any storage class. It
-is not generally possible to determine the storage class of the
-result of an expression at compile time.
-In order to maximize compatibility between SQLite and other database
-engines, SQLite support the concept of "type affinity" on columns.
-The type affinity of a column is the recommended type for data stored
-in that column. The key here is that the type is recommended, not
-required. Any column can still store any type of data, in theory.
-It is just that some columns, given the choice, will prefer to use
-one storage class over another. The preferred storage class for
-a column is called its "affinity".
-
-
-
Each column in an SQLite 3 database is assigned one of the
-following type affinities:
-
-
TEXT
-
NUMERIC
-
INTEGER
-
REAL
-
NONE
-
-
-
A column with TEXT affinity stores all data using storage classes
-NULL, TEXT or BLOB. If numerical data is inserted into a column with
-TEXT affinity it is converted to text form before being stored.
-
-
A column with NUMERIC affinity may contain values using all five
-storage classes. When text data is inserted into a NUMERIC column, an
-attempt is made to convert it to an integer or real number before it
-is stored. If the conversion is successful, then the value is stored
-using the INTEGER or REAL storage class. If the conversion cannot be
-performed the value is stored using the TEXT storage class. No
-attempt is made to convert NULL or blob values.
-
-
A column that uses INTEGER affinity behaves in the same way as a
-column with NUMERIC affinity, except that if a real value with no
-floating point component (or text value that converts to such) is
-inserted it is converted to an integer and stored using the INTEGER
-storage class.
-
-
A column with REAL affinity behaves like a column with NUMERIC
-affinity except that it forces integer values into floating point
-representation. (As an optimization, integer values are stored on
-disk as integers in order to take up less space and are only converted
-to floating point as the value is read out of the table.)
-
-
A column with affinity NONE does not prefer one storage class over
-another. It makes no attempt to coerce data before
-it is inserted.
-
-
2.1 Determination Of Column Affinity
-
-
The type affinity of a column is determined by the declared type
-of the column, according to the following rules:
-
-
If the datatype contains the string "INT" then it
- is assigned INTEGER affinity.
-
-
If the datatype of the column contains any of the strings
- "CHAR", "CLOB", or "TEXT" then that
- column has TEXT affinity. Notice that the type VARCHAR contains the
- string "CHAR" and is thus assigned TEXT affinity.
-
-
If the datatype for a column
- contains the string "BLOB" or if
- no datatype is specified then the column has affinity NONE.
-
-
If the datatype for a column
- contains any of the strings "REAL", "FLOA",
- or "DOUB" then the column has REAL affinity
-
-
Otherwise, the affinity is NUMERIC.
-
-
-
If a table is created using a "CREATE TABLE <table> AS
-SELECT..." statement, then all columns have no datatype specified
-and they are given no affinity.
-
-
2.2 Column Affinity Example
-
-
-
CREATE TABLE t1(
- t TEXT,
- nu NUMERIC,
- i INTEGER,
- no BLOB
-);
-
--- Storage classes for the following row:
--- TEXT, REAL, INTEGER, TEXT
-INSERT INTO t1 VALUES('500.0', '500.0', '500.0', '500.0');
-
--- Storage classes for the following row:
--- TEXT, REAL, INTEGER, REAL
-INSERT INTO t1 VALUES(500.0, 500.0, 500.0, 500.0);
-
-By default, when SQLite compares two text values, the result of the
-comparison is determined using memcmp(), regardless of the encoding of the
-string. SQLite v3 provides the ability for users to supply arbitrary
-comparison functions, known as user-defined collation sequences, to be used
-instead of memcmp().
-
-
-Aside from the default collation sequence BINARY, implemented using
-memcmp(), SQLite features one extra built-in collation sequences
-intended for testing purposes, the NOCASE collation:
-
-
-
BINARY - Compares string data using memcmp(), regardless
- of text encoding.
-
NOCASE - The same as binary, except the 26 upper case
- characters used by the English language are
- folded to their lower case equivalents before
- the comparison is performed.
-
-
-
7.1 Assigning Collation Sequences from SQL
-
-
-Each column of each table has a default collation type. If a collation type
-other than BINARY is required, a COLLATE clause is specified as part of the
-column definition to define it.
-
-
-
-Whenever two text values are compared by SQLite, a collation sequence is
-used to determine the results of the comparison according to the following
-rules. Sections 3 and 5 of this document describe the circumstances under
-which such a comparison takes place.
-
-
-
-For binary comparison operators (=, <, >, <= and >=) if either operand is a
-column, then the default collation type of the column determines the
-collation sequence to use for the comparison. If both operands are columns,
-then the collation type for the left operand determines the collation
-sequence used. If neither operand is a column, then the BINARY collation
-sequence is used. For the purposes of this paragraph, a column name
-preceded by one or more unary "+" operators is considered a column name.
-
-
-
-The expression "x BETWEEN y and z" is equivalent to "x >= y AND x <=
-z". The expression "x IN (SELECT y ...)" is handled in the same way as the
-expression "x = y" for the purposes of determining the collation sequence
-to use. The collation sequence used for expressions of the form "x IN (y, z
-...)" is the default collation type of x if x is a column, or BINARY
-otherwise.
-
-
-
-An ORDER BY clause that is part of a SELECT
-statement may be assigned a collation sequence to be used for the sort
-operation explicitly. In this case the explicit collation sequence is
-always used. Otherwise, if the expression sorted by an ORDER BY clause is
-a column, then the default collation type of the column is used to
-determine sort order. If the expression is not a column, then the BINARY
-collation sequence is used.
-
-
-
7.2 Collation Sequences Example
-
-The examples below identify the collation sequences that would be used to
-determine the results of text comparisons that may be performed by various
-SQL statements. Note that a text comparison may not be required, and no
-collation sequence used, in the case of numeric, blob or NULL values.
-
-
-
-CREATE TABLE t1(
- a, -- default collation type BINARY
- b COLLATE BINARY, -- default collation type BINARY
- c COLLATE REVERSE, -- default collation type REVERSE
- d COLLATE NOCASE -- default collation type NOCASE
-);
-
--- Text comparison is performed using the BINARY collation sequence.
-SELECT (a = b) FROM t1;
-
--- Text comparison is performed using the NOCASE collation sequence.
-SELECT (d = a) FROM t1;
-
--- Text comparison is performed using the BINARY collation sequence.
-SELECT (a = d) FROM t1;
-
--- Text comparison is performed using the REVERSE collation sequence.
-SELECT ('abc' = c) FROM t1;
-
--- Text comparison is performed using the REVERSE collation sequence.
-SELECT (c = 'abc') FROM t1;
-
--- Grouping is performed using the NOCASE collation sequence (i.e. values
--- 'abc' and 'ABC' are placed in the same group).
-SELECT count(*) GROUP BY d FROM t1;
-
--- Grouping is performed using the BINARY collation sequence.
-SELECT count(*) GROUP BY (d || '') FROM t1;
-
--- Sorting is performed using the REVERSE collation sequence.
-SELECT * FROM t1 ORDER BY c;
-
--- Sorting is performed using the BINARY collation sequence.
-SELECT * FROM t1 ORDER BY (c || '');
-
--- Sorting is performed using the NOCASE collation sequence.
-SELECT * FROM t1 ORDER BY c COLLATE NOCASE;
-
-
-
-
-}
-footer $rcsid
DELETED www/datatypes.tcl
Index: www/datatypes.tcl
==================================================================
--- www/datatypes.tcl
+++ /dev/null
@@ -1,243 +0,0 @@
-#
-# Run this script to generated a datatypes.html output file
-#
-set rcsid {$Id: datatypes.tcl,v 1.8 2004/10/10 17:24:55 drh Exp $}
-source common.tcl
-header {Datatypes In SQLite version 2}
-puts {
-
Datatypes In SQLite Version 2
-
-
1.0 Typelessness
-
-SQLite is "typeless". This means that you can store any
-kind of data you want in any column of any table, regardless of the
-declared datatype of that column.
-(See the one exception to this rule in section 2.0 below.)
-This behavior is a feature, not
-a bug. A database is suppose to store and retrieve data and it
-should not matter to the database what format that data is in.
-The strong typing system found in most other SQL engines and
-codified in the SQL language spec is a misfeature -
-it is an example of the implementation showing through into the
-interface. SQLite seeks to overcome this misfeature by allowing
-you to store any kind of data into any kind of column and by
-allowing flexibility in the specification of datatypes.
-
-
-
-A datatype to SQLite is any sequence of zero or more names
-optionally followed by a parenthesized lists of one or two
-signed integers. Notice in particular that a datatype may
-be zero or more names. That means that an empty
-string is a valid datatype as far as SQLite is concerned.
-So you can declare tables where the datatype of each column
-is left unspecified, like this:
-
-
-
-CREATE TABLE ex1(a,b,c);
-
-
-
-Even though SQLite allows the datatype to be omitted, it is
-still a good idea to include it in your CREATE TABLE statements,
-since the data type often serves as a good hint to other
-programmers about what you intend to put in the column. And
-if you ever port your code to another database engine, that
-other engine will probably require a datatype of some kind.
-SQLite accepts all the usual datatypes. For example:
-
-
-
-CREATE TABLE ex2(
- a VARCHAR(10),
- b NVARCHAR(15),
- c TEXT,
- d INTEGER,
- e FLOAT,
- f BOOLEAN,
- g CLOB,
- h BLOB,
- i TIMESTAMP,
- j NUMERIC(10,5)
- k VARYING CHARACTER (24),
- l NATIONAL VARYING CHARACTER(16)
-);
-
-
-
-And so forth. Basically any sequence of names optionally followed by
-one or two signed integers in parentheses will do.
-
-
-
2.0 The INTEGER PRIMARY KEY
-
-
-One exception to the typelessness of SQLite is a column whose type
-is INTEGER PRIMARY KEY. (And you must use "INTEGER" not "INT".
-A column of type INT PRIMARY KEY is typeless just like any other.)
-INTEGER PRIMARY KEY columns must contain a 32-bit signed integer. Any
-attempt to insert non-integer data will result in an error.
-
-
-
-INTEGER PRIMARY KEY columns can be used to implement the equivalent
-of AUTOINCREMENT. If you try to insert a NULL into an INTEGER PRIMARY
-KEY column, the column will actually be filled with a integer that is
-one greater than the largest key already in the table. Or if the
-largest key is 2147483647, then the column will be filled with a
-random integer. Either way, the INTEGER PRIMARY KEY column will be
-assigned a unique integer. You can retrieve this integer using
-the sqlite_last_insert_rowid() API function or using the
-last_insert_rowid() SQL function in a subsequent SELECT statement.
-
-
-
3.0 Comparison and Sort Order
-
-
-SQLite is typeless for the purpose of deciding what data is allowed
-to be stored in a column. But some notion of type comes into play
-when sorting and comparing data. For these purposes, a column or
-an expression can be one of two types: numeric and text.
-The sort or comparison may give different results depending on which
-type of data is being sorted or compared.
-
-
-
-If data is of type text then the comparison is determined by
-the standard C data comparison functions memcmp() or
-strcmp(). The comparison looks at bytes from two inputs one
-by one and returns the first non-zero difference.
-Strings are '\000' terminated so shorter
-strings sort before longer strings, as you would expect.
-
-
-
-For numeric data, this situation is more complex. If both inputs
-look like well-formed numbers, then they are converted
-into floating point values using atof() and compared numerically.
-If one input is not a well-formed number but the other is, then the
-number is considered to be less than the non-number. If neither inputs
-is a well-formed number, then strcmp() is used to do the
-comparison.
-
-
-
-Do not be confused by the fact that a column might have a "numeric"
-datatype. This does not mean that the column can contain only numbers.
-It merely means that if the column does contain a number, that number
-will sort in numerical order.
-
-
-
-For both text and numeric values, NULL sorts before any other value.
-A comparison of any value against NULL using operators like "<" or
-">=" is always false.
-
-
-
4.0 How SQLite Determines Datatypes
-
-
-For SQLite version 2.6.3 and earlier, all values used the numeric datatype.
-The text datatype appears in version 2.7.0 and later. In the sequel it
-is assumed that you are using version 2.7.0 or later of SQLite.
-
-
-
-For an expression, the datatype of the result is often determined by
-the outermost operator. For example, arithmetic operators ("+", "*", "%")
-always return a numeric results. The string concatenation operator
-("||") returns a text result. And so forth. If you are ever in doubt
-about the datatype of an expression you can use the special typeof()
-SQL function to determine what the datatype is. For example:
-
-For table columns, the datatype is determined by the type declaration
-of the CREATE TABLE statement. The datatype is text if and only if
-the type declaration contains one or more of the following strings:
-
-
-
-BLOB
-CHAR
-CLOB
-TEXT
-
-
-
-The search for these strings in the type declaration is case insensitive,
-of course. If any of the above strings occur anywhere in the type
-declaration, then the datatype of the column is text. Notice that
-the type "VARCHAR" contains "CHAR" as a substring so it is considered
-text.
-
-
If none of the strings above occur anywhere in the type declaration,
-then the datatype is numeric. Note in particular that the datatype for columns
-with an empty type declaration is numeric.
-
-
-
5.0 Examples
-
-
-Consider the following two command sequences:
-
-
-
-CREATE TABLE t1(a INTEGER UNIQUE); CREATE TABLE t2(b TEXT UNIQUE);
-INSERT INTO t1 VALUES('0'); INSERT INTO t2 VALUES(0);
-INSERT INTO t1 VALUES('0.0'); INSERT INTO t2 VALUES(0.0);
-
-
-
In the sequence on the left, the second insert will fail. In this case,
-the strings '0' and '0.0' are treated as numbers since they are being
-inserted into a numeric column but 0==0.0 which violates the uniqueness
-constraint. However, the second insert in the right-hand sequence works. In
-this case, the constants 0 and 0.0 are treated a strings which means that
-they are distinct.
-
-
SQLite always converts numbers into double-precision (64-bit) floats
-for comparison purposes. This means that a long sequence of digits that
-differ only in insignificant digits will compare equal if they
-are in a numeric column but will compare unequal if they are in a text
-column. We have:
-
-
-INSERT INTO t1 INSERT INTO t2
- VALUES('12345678901234567890'); VALUES(12345678901234567890);
-INSERT INTO t1 INSERT INTO t2
- VALUES('12345678901234567891'); VALUES(12345678901234567891);
-
-
-
As before, the second insert on the left will fail because the comparison
-will convert both strings into floating-point number first and the only
-difference in the strings is in the 20-th digit which exceeds the resolution
-of a 64-bit float. In contrast, the second insert on the right will work
-because in that case, the numbers being inserted are strings and are
-compared using memcmp().
-
-
-Numeric and text types make a difference for the DISTINCT keyword too:
-
-
-
-CREATE TABLE t3(a INTEGER); CREATE TABLE t4(b TEXT);
-INSERT INTO t3 VALUES('0'); INSERT INTO t4 VALUES(0);
-INSERT INTO t3 VALUES('0.0'); INSERT INTO t4 VALUES(0.0);
-SELECT DISTINCT * FROM t3; SELECT DISTINCT * FROM t4;
-
-
-
-The SELECT statement on the left returns a single row since '0' and '0.0'
-are treated as numbers and are therefore indistinct. But the SELECT
-statement on the right returns two rows since 0 and 0.0 are treated
-a strings which are different.
\n"
-}
-
-feature zeroconfig {Zero-Configuration} {
- SQLite does not need to be "installed" before it is used.
- There is no "setup" procedure. There is no
- server process that needs to be started, stopped, or configured.
- There is
- no need for an administrator to create a new database instance or assign
- access permissions to users.
- SQLite uses no configuration files.
- Nothing needs to be done to tell the system that SQLite is running.
- No actions are required to recover after a system crash or power failure.
- There is nothing to troubleshoot.
-
- SQLite just works.
-
- Other more familiar database engines run great once you get them going.
- But doing the initial installation and configuration can be
- intimidatingly complex.
-}
-
-feature serverless {Serverless} {
- Most SQL database engines are implemented as a separate server
- process. Programs that want to access the database communicate
- with the server using some kind of interprocess communcation
- (typically TCP/IP) to send requests to the server and to receive
- back results. SQLite does not work this way. With SQLite, the
- process that wants to access the database reads and writes
- directly from the database files on disk. There is no intermediary
- server process.
-
- There are advantages and disadvantages to being serverless. The
- main advantage is that there is no separate server process
- to install, setup, configure, initialize, manage, and troubleshoot.
- This is one reason why SQLite is a "zero-configuration" database
- engine. Programs that use SQLite require no administrative support
- for setting up the database engine before they are run. Any program
- that is able to access the disk is able to use an SQLite database.
-
- On the other hand, a database engine that uses a server can provide
- better protection from bugs in the client application - stray pointers
- in a client cannot corrupt memory on the server. And because a server
- is a single persistent process, it is able control database access with
- more precision, allowing for finer grain locking and better concurrancy.
-
- Most SQL database engines are client/server based. Of those that are
- serverless, SQLite is the only one that this author knows of that
- allows multiple applications to access the same database at the same time.
-}
-
-feature onefile {Single Database File} {
- An SQLite database is a single ordinary disk file that can be located
- anywhere in the directory hierarchy. If SQLite can read
- the disk file then it can read anything in the database. If the disk
- file and its directory are writable, then SQLite can change anything
- in the database. Database files can easily be copied onto a USB
- memory stick or emailed for sharing.
-
- Other SQL database engines tend to store data as a large collection of
- files. Often these files are in a standard location that only the
- database engine itself can access. This makes the data more secure,
- but also makes it harder to access. Some SQL database engines provide
- the option of writing directly to disk and bypassing the filesystem
- all together. This provides added performance, but at the cost of
- considerable setup and maintenance complexity.
-}
-
-feature small {Compact} {
- When optimized for size, the whole SQLite library with everything enabled
- is less than 225KiB in size (as measured on an ix86 using the "size"
- utility from the GNU compiler suite.) Unneeded features can be disabled
- at compile-time to further reduce the size of the library to under
- 170KiB if desired.
-
- Most other SQL database engines are much larger than this. IBM boasts
- that it's recently released CloudScape database engine is "only" a 2MiB
- jar file - 10 times larger than SQLite even after it is compressed!
- Firebird boasts that it's client-side library is only 350KiB. That's
- 50% larger than SQLite and does not even contain the database engine.
- The Berkeley DB library from Sleepycat is 450KiB and it omits SQL
- support, providing the programmer with only simple key/value pairs.
-}
-
-feature typing {Manifest typing} {
- Most SQL database engines use static typing. A datatype is associated
- with each column in a table and only values of that particular datatype
- are allowed to be stored in that column. SQLite relaxes this restriction
- by using manifest typing.
- In manifest typing, the datatype is a property of the value itself, not
- of the column in which the value is stored.
- SQLite thus allows the user to store
- any value of any datatype into any column regardless of the declared type
- of that column. (There are some exceptions to this rule: An INTEGER
- PRIMARY KEY column may only store integers. And SQLite attempts to coerce
- values into the declared datatype of the column when it can.)
-
- As far as we can tell, the SQL language specification allows the use
- of manifest typing. Nevertheless, most other SQL database engines are
- statically typed and so some people
- feel that the use of manifest typing is a bug in SQLite. But the authors
- of SQLite feel very strongly that this is a feature. The use of manifest
- typing in SQLite is a deliberate design decision which has proven in practice
- to make SQLite more reliable and easier to use, especially when used in
- combination with dynamically typed programming languages such as Tcl and
- Python.
-}
-
-feature flex {Variable-length records} {
- Most other SQL database engines allocated a fixed amount of disk space
- for each row in most tables. They play special tricks for handling
- BLOBs and CLOBs which can be of wildly varying length. But for most
- tables, if you declare a column to be a VARCHAR(100) then the database
- engine will allocate
- 100 bytes of disk space regardless of how much information you actually
- store in that column.
-
- SQLite, in contrast, use only the amount of disk space actually
- needed to store the information in a row. If you store a single
- character in a VARCHAR(100) column, then only a single byte of disk
- space is consumed. (Actually two bytes - there is some overhead at
- the beginning of each column to record its datatype and length.)
-
- The use of variable-length records by SQLite has a number of advantages.
- It results in smaller database files, obviously. It also makes the
- database run faster, since there is less information to move to and from
- disk. And, the use of variable-length records makes it possible for
- SQLite to employ manifest typing instead of static typing.
-}
-
-feature readable {Readable source code} {
- The source code to SQLite is designed to be readable and accessible to
- the average programmer. All procedures and data structures and many
- automatic variables are carefully commented with useful information about
- what they do. Boilerplate commenting is omitted.
-}
-
-feature vdbe {SQL statements compile into virtual machine code} {
- Every SQL database engine compiles each SQL statement into some kind of
- internal data structure which is then used to carry out the work of the
- statement. But in most SQL engines that internal data structure is a
- complex web of interlinked structures and objects. In SQLite, the compiled
- form of statements is a short program in a machine-language like
- representation. Users of the database can view this
- virtual machine language
- by prepending the EXPLAIN keyword
- to a query.
-
- The use of a virtual machine in SQLite has been a great benefit to
- library's development. The virtual machine provides a crisp, well-defined
- junction between the front-end of SQLite (the part that parses SQL
- statements and generates virtual machine code) and the back-end (the
- part that executes the virtual machine code and computes a result.)
- The virtual machine allows the developers to see clearly and in an
- easily readable form what SQLite is trying to do with each statement
- it compiles, which is a tremendous help in debugging.
- Depending on how it is compiled, SQLite also has the capability of
- tracing the execution of the virtual machine - printing each
- virtual machine instruction and its result as it executes.
-}
-
-#feature binding {Tight bindings to dynamic languages} {
-# Because it is embedded, SQLite can have a much tighter and more natural
-# binding to high-level dynamic languages such as Tcl, Perl, Python,
-# PHP, and Ruby.
-# For example,
-#}
-
-feature license {Public domain} {
- The source code for SQLite is in the public domain. No claim of copyright
- is made on any part of the core source code. (The documentation and test
- code is a different matter - some sections of documentation and test logic
- are governed by open-sources licenses.) All contributors to the
- SQLite core software have signed affidavits specifically disavowing any
- copyright interest in the code. This means that anybody is able to legally
- do anything they want with the SQLite source code.
-
- There are other SQL database engines with liberal licenses that allow
- the code to be broadly and freely used. But those other engines are
- still governed by copyright law. SQLite is different in that copyright
- law simply does not apply.
-
- The source code files for other SQL database engines typically begin
- with a comment describing your license rights to view and copy that file.
- The SQLite source code contains no license since it is not governed by
- copyright. Instead of a license, the SQLite source code offers a blessing:
-
- May you do good and not evil
- May you find forgiveness for yourself and forgive others
- May you share freely, never taking more than you give.
-
-}
-
-feature extensions {SQL language extensions} {
- SQLite provides a number of enhancements to the SQL language
- not normally found in other database engines.
- The EXPLAIN keyword and manifest typing have already been mentioned
- above. SQLite also provides statements such as
- REPLACE and the
- ON CONFLICT clause that allow for
- added control over the resolution of constraint conflicts.
- SQLite supports ATTACH and
- DETACH commands that allow multiple
- independent databases to be used together in the same query.
- And SQLite defines APIs that allows the user to add new
- SQL functions
- and collating sequences.
-}
-
-
-footer $rcsid
DELETED www/direct1b.gif
Index: www/direct1b.gif
==================================================================
--- www/direct1b.gif
+++ /dev/null
cannot compute difference between binary files
DELETED www/docs.tcl
Index: www/docs.tcl
==================================================================
--- www/docs.tcl
+++ /dev/null
@@ -1,159 +0,0 @@
-# This script generates the "docs.html" page that describes various
-# sources of documentation available for SQLite.
-#
-set rcsid {$Id: docs.tcl,v 1.15 2007/10/04 00:29:29 drh Exp $}
-source common.tcl
-header {SQLite Documentation}
-puts {
-
}
-}
-
-doc {Appropriate Uses For SQLite} {whentouse.html} {
- This document describes situations where SQLite is an approriate
- database engine to use versus situations where a client/server
- database engine might be a better choice.
-}
-
-doc {Distinctive Features} {different.html} {
- This document enumerates and describes some of the features of
- SQLite that make it different from other SQL database engines.
-}
-
-doc {SQLite In 5 Minutes Or Less} {quickstart.html} {
- A very quick introduction to programming with SQLite.
-}
-
-doc {SQL Syntax} {lang.html} {
- This document describes the SQL language that is understood by
- SQLite.
-}
-doc {Version 3 C/C++ API Reference} {capi3ref.html} {
- This document describes each API function separately.
-}
-doc {Sharing Cache Mode} {sharedcache.html} {
- Version 3.3.0 and later supports the ability for two or more
- database connections to share the same page and schema cache.
- This feature is useful for certain specialized applications.
-}
-doc {Tcl API} {tclsqlite.html} {
- A description of the TCL interface bindings for SQLite.
-}
-
-doc {How SQLite Implements Atomic Commit} {ac/atomiccommit.html} {
- A description of the logic within SQLite that implements
- transactions with atomic commit, even in the face of power
- failures.
-}
-doc {Moving From SQLite 3.4 to 3.5} {34to35.html} {
- A document describing the differences between SQLite version 3.4.2
- and 3.5.0.
-}
-
-doc {Pragma commands} {pragma.html} {
- This document describes SQLite performance tuning options and other
- special purpose database commands.
-}
-doc {SQLite Version 3} {version3.html} {
- A summary of of the changes between SQLite version 2.8 and SQLite version 3.0.
-}
-doc {Version 3 C/C++ API} {capi3.html} {
- A description of the C/C++ interface bindings for SQLite version 3.0.0
- and following.
-}
-doc {Version 3 DataTypes } {datatype3.html} {
- SQLite version 3 introduces the concept of manifest typing, where the
- type of a value is associated with the value itself, not the column that
- it is stored in.
- This page describes data typing for SQLite version 3 in further detail.
-}
-
-doc {Locking And Concurrency In SQLite Version 3} {lockingv3.html} {
- A description of how the new locking code in version 3 increases
- concurrancy and decreases the problem of writer starvation.
-}
-
-doc {Overview Of The Optimizer} {optoverview.html} {
- A quick overview of the various query optimizations that are
- attempted by the SQLite code generator.
-}
-
-
-doc {Null Handling} {nulls.html} {
- Different SQL database engines handle NULLs in different ways. The
- SQL standards are ambiguous. This document describes how SQLite handles
- NULLs in comparison with other SQL database engines.
-}
-
-doc {Copyright} {copyright.html} {
- SQLite is in the public domain. This document describes what that means
- and the implications for contributors.
-}
-
-doc {Unsupported SQL} {omitted.html} {
- This page describes features of SQL that SQLite does not support.
-}
-
-doc {Version 2 C/C++ API} {c_interface.html} {
- A description of the C/C++ interface bindings for SQLite through version
- 2.8
-}
-
-
-doc {Version 2 DataTypes } {datatypes.html} {
- A description of how SQLite version 2 handles SQL datatypes.
- Short summary: Everything is a string.
-}
-
-doc {Release History} {changes.html} {
- A chronology of SQLite releases going back to version 1.0.0
-}
-
-
-doc {Speed Comparison} {speed.html} {
- The speed of version 2.7.6 of SQLite is compared against PostgreSQL and
- MySQL.
-}
-
-doc {Architecture} {arch.html} {
- An architectural overview of the SQLite library, useful for those who want
- to hack the code.
-}
-
-doc {VDBE Tutorial} {vdbe.html} {
- The VDBE is the subsystem within SQLite that does the actual work of
- executing SQL statements. This page describes the principles of operation
- for the VDBE in SQLite version 2.7. This is essential reading for anyone
- who want to modify the SQLite sources.
-}
-
-doc {VDBE Opcodes} {opcode.html} {
- This document is an automatically generated description of the various
- opcodes that the VDBE understands. Programmers can use this document as
- a reference to better understand the output of EXPLAIN listings from
- SQLite.
-}
-
-doc {Compilation Options} {compile.html} {
- This document describes the compile time options that may be set to
- modify the default behaviour of the library or omit optional features
- in order to reduce binary size.
-}
-
-doc {Backwards Compatibility} {formatchng.html} {
- This document details all of the incompatible changes to the SQLite
- file format that have occurred since version 1.0.0.
-}
-
-puts {
}
-footer $rcsid
DELETED www/download.tcl
Index: www/download.tcl
==================================================================
--- www/download.tcl
+++ /dev/null
@@ -1,236 +0,0 @@
-#
-# Run this TCL script to generate HTML for the download.html file.
-#
-set rcsid {$Id: download.tcl,v 1.27 2007/05/08 18:30:36 drh Exp $}
-source common.tcl
-header {SQLite Download Page}
-
-puts {
-
SQLite Download Page
-
-}
-
-proc Product {pattern desc} {
- regsub {V[23]} $pattern {*} p3
- regsub V2 $pattern {(2[0-9a-z._]+)} pattern
- regsub V3 $pattern {(3[0-9a-z._]+)} pattern
- set p2 [string map {* .*} $pattern]
- set flist [glob -nocomplain $p3]
- foreach file [lsort -dict $flist] {
- if {![regexp ^$p2\$ $file all version]} continue
- regsub -all _ $version . version
- set size [file size $file]
- set units bytes
- if {$size>1024*1024} {
- set size [format %.2f [expr {$size/(1024.0*1024.0)}]]
- set units MiB
- } elseif {$size>1024} {
- set size [format %.2f [expr {$size/(1024.0)}]]
- set units KiB
- }
- puts "
"
-}
-
-Heading {Precompiled Binaries for Linux}
-
-Product sqlite3-V3.bin.gz {
- A command-line program for accessing and modifying
- SQLite version 3.* databases.
- See the documentation for additional information.
-}
-
-Product sqlite-V3.bin.gz {
- A command-line program for accessing and modifying
- SQLite databases.
- See the documentation for additional information.
-}
-
-Product tclsqlite-V3.so.gz {
- Bindings for Tcl/Tk.
- You can import this shared library into either
- tclsh or wish to get SQLite database access from Tcl/Tk.
- See the documentation for details.
-}
-
-Product sqlite-V3.so.gz {
- A precompiled shared-library for Linux without the TCL bindings.
-}
-
-Product fts1-V3.so.gz {
- A precompiled
- FTS1 Module
- for Linux.
-}
-
-Product fts2-V3.so.gz {
- A precompiled
- FTS2 Module
- for Linux.
-}
-
-Product sqlite-devel-V3.i386.rpm {
- RPM containing documentation, header files, and static library for
- SQLite version VERSION.
-}
-Product sqlite-V3-1.i386.rpm {
- RPM containing shared libraries and the sqlite command-line
- program for SQLite version VERSION.
-}
-
-Product sqlite*_analyzer-V3.bin.gz {
- An analysis program for database files compatible with SQLite
- version VERSION and later.
-}
-
-Heading {Precompiled Binaries For Windows}
-
-Product sqlite-V3.zip {
- A command-line program for accessing and modifing SQLite databases.
- See the documentation for additional information.
-}
-Product tclsqlite-V3.zip {
- Bindings for Tcl/Tk.
- You can import this shared library into either
- tclsh or wish to get SQLite database access from Tcl/Tk.
- See the documentation for details.
-}
-Product sqlitedll-V3.zip {
- This is a DLL of the SQLite library without the TCL bindings.
- The only external dependency is MSVCRT.DLL.
-}
-
-Product fts1dll-V3.zip {
- A precompiled
- FTS1 Module
- for win32.
-}
-
-Product fts2dll-V3.zip {
- A precompiled
- FTS2 Module
- for win32.
-}
-
-Product sqlite*_analyzer-V3.zip {
- An analysis program for database files compatible with SQLite version
- VERSION and later.
-}
-
-
-Heading {Source Code}
-
-Product {sqlite-V3.tar.gz} {
- A tarball of the complete source tree for SQLite version VERSION
- including all of the documentation.
-}
-
-Product {sqlite-source-V3.zip} {
- This ZIP archive contains preprocessed C code for the SQLite library as
- individual source files.
- Unlike the tarballs below, all of the preprocessing and automatic
- code generation has already been done on these C code files, so they
- can be converted to object code directly with any ordinary C compiler.
-}
-
-Product {sqlite-amalgamation-V3.zip} {
- This ZIP archive contains all preprocessed C code combined into a
- single source file (the
-
- amalgamation).
-}
-
-Product {sqlite-V3-tea.tar.gz} {
- A tarball of proprocessed source code together with a
- Tcl Extension Architecture (TEA)
- compatible configure script and makefile.
-}
-
-Product {sqlite-V3.src.rpm} {
- An RPM containing complete source code for SQLite version VERSION
-}
-
-Heading {Cross-Platform Binaries}
-
-Product {sqlite-V3.kit} {
- A starkit containing
- precompiled SQLite binaries and Tcl bindings for Linux-x86, Windows,
- and Mac OS-X ppc and x86.
-}
-
-Heading {Historical Binaries And Source Code}
-
-Product sqlite-V2.bin.gz {
- A command-line program for accessing and modifying
- SQLite version 2.* databases on Linux-x86.
-}
-Product sqlite-V2.zip {
- A command-line program for accessing and modifying
- SQLite version 2.* databases on win32.
-}
-
-Product sqlite*_analyzer-V2.bin.gz {
- An analysis program for version 2.* database files on Linux-x86
-}
-Product sqlite*_analyzer-V2.zip {
- An analysis program for version 2.* database files on win32.
-}
-Product {sqlite-source-V2.zip} {
- This ZIP archive contains C source code for the SQLite library
- version VERSION.
-}
-
-
-
-
-puts {
-
-When the first command prompts you for a password, enter "anonymous".
-
-
-
-To access the SQLite version 2.8 sources, begin by getting the 3.0
-tree as described above. Then update to the "version_2" branch
-as follows:
-
-
-
-cvs update -r version_2
-
-
-}
-
-footer $rcsid
DELETED www/dynload.tcl
Index: www/dynload.tcl
==================================================================
--- www/dynload.tcl
+++ /dev/null
@@ -1,70 +0,0 @@
-#
-# Run this Tcl script to generate the dynload.html file.
-#
-set rcsid {$Id: dynload.tcl,v 1.1 2001/02/11 16:58:22 drh Exp $}
-
-puts {
-
- How to build a dynamically loaded Tcl extension for SQLite
-
-
-
-How To Build A Dynamically Loaded Tcl Extension
-
}
-puts {
-This note was contributed by
-Bill Saunders. Thanks, Bill!
-
-
-To compile the SQLite Tcl extension into a dynamically loaded module
-I did the following:
-
-
-
-
Do a standard compile
-(I had a dir called bld at the same level as sqlite ie
- /root/bld
- /root/sqlite
-I followed the directions and did a standard build in the bld
-directory)
-To use this put sqlite.so and pkgIndex.tcl in the same directory
-
-
-From that directory start wish
-
-
-Execute the following tcl command (tells tcl where to fine loadable
-modules)
-
-lappend auto_path [exec pwd]
-
-
-
-Load the package
-
-package require sqlite
-
-
-
-Have fun....
-
-
-}
DELETED www/faq.tcl
Index: www/faq.tcl
==================================================================
--- www/faq.tcl
+++ /dev/null
@@ -1,463 +0,0 @@
-#
-# Run this script to generated a faq.html output file
-#
-set rcsid {$Id: faq.tcl,v 1.40 2007/09/04 01:58:27 drh Exp $}
-source common.tcl
-header {SQLite Frequently Asked Questions}
-
-set cnt 1
-proc 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.
-} {
-
Short answer: A column declared INTEGER PRIMARY KEY will
- autoincrement.
-
-
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:
-
-CREATE TABLE t1(
- a INTEGER PRIMARY KEY,
- b INTEGER
-);
-
-
With this table, the statement
-
-INSERT INTO t1 VALUES(NULL,123);
-
-
is logically equivalent to saying:
-
-INSERT INTO t1 VALUES((SELECT max(a) FROM t1)+1,123);
-
-
-
There is a new API function named
-
- sqlite3_last_insert_rowid() which will return the integer key
- for the most recent insert operation.
-
-
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.
-}
-
-faq {
- What datatypes does SQLite support?
-} {
-
-}
-
-faq {
- SQLite lets me insert a string into a database column of type integer!
-} {
-
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.)
-
-
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 type or column affinity.
-
-}
-
-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?
-} {
-
Your primary key must have a numeric type. Change the datatype of
- your primary key to TEXT and it should work.
-
-
Every row must have a unique primary key. For a column with a
- numeric type, SQLite thinks that '0' and '0.0' are the
- same value because they compare equal to one another numerically.
- (See the previous question.) Hence the values are not unique.
-}
-
-
-faq {
- Can multiple applications or multiple instances of the same
- application access a single database file at the same time?
-} {
-
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.
-
-
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.
-
-
We are aware of no other embedded 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.
-
-
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.
-
-
-
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
- sqlite3_busy_handler() or
- sqlite3_busy_timeout()
- API functions.
-}
-
-faq {
- Is SQLite threadsafe?
-} {
-
Yes. Sometimes. 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
- sqlite3_threadsafe()
- interface to find out.
-
-
-
Prior to version 3.3.1,
- an sqlite3 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.
-
-
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.
-
-
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.
-}
-
-faq {
- How do I list all tables/indices contained in an SQLite database
-} {
-
If you are running the sqlite3 command-line access program
- you can type ".tables" to get a list of all tables. Or you
- can type ".schema" 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.
-
-
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 "SQLITE_MASTER". Every SQLite database
- has an SQLITE_MASTER table that defines the schema for the database.
- The SQLITE_MASTER table looks like this:
-
-CREATE TABLE sqlite_master (
- type TEXT,
- name TEXT,
- tbl_name TEXT,
- rootpage INTEGER,
- sql TEXT
-);
-
-
For tables, the type field will always be 'table' and the
- name field will be the name of the table. So to get a list of
- all tables in the database, use the following SELECT command:
-
-SELECT name FROM sqlite_master
-WHERE type='table'
-ORDER BY name;
-
-
For indices, type is equal to 'index', name is the
- name of the index and tbl_name is the name of the table to which
- the index belongs. For both tables and indices, the sql 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 sql field
- is NULL.
-
-
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.
-
-
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:
-
-SELECT name FROM
- (SELECT * FROM sqlite_master UNION ALL
- SELECT * FROM sqlite_temp_master)
-WHERE type='table'
-ORDER BY name
-
-}
-
-faq {
- Are there any known size limits to SQLite databases?
-} {
-
See limits.html for a full discussion of
- the limits of SQLite.
-}
-
-faq {
- What is the maximum size of a VARCHAR in SQLite?
-} {
-
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.
-
-}
-
-faq {
- Does SQLite support a BLOB type?
-} {
-
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.
-}
-
-faq {
- How do I add or delete columns from an existing table in SQLite.
-} {
-
SQLite has limited
- ALTER TABLE 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.
-
-
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:
-
-
-
-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;
-
-}
-
-faq {
- I deleted a lot of data but the database file did not get any
- smaller. Is this a bug?
-} {
-
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.
-
-
If you delete a lot of data and want to shrink the database file,
- run the VACUUM 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.
-
-
-
As of SQLite version 3.1, an alternative to using the VACUUM command
- is auto-vacuum mode, enabled using the
- auto_vacuum pragma.
-}
-
-faq {
- Can I use SQLite in my commercial product without paying royalties?
-} {
-
Yes. SQLite is in the
- public domain. No claim of ownership is made
- to any part of the code. You can do anything you want with it.
-}
-
-faq {
- How do I use a string literal that contains an embedded single-quote (')
- character?
-} {
-
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:
-
-
-
- INSERT INTO xyz VALUES('5 O''clock');
-
-}
-
-faq {What is an SQLITE_SCHEMA error, and why am I getting one?} {
-
An SQLITE_SCHEMA 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
- sqlite3_prepare() API.
- In SQLite version 3, an SQLITE_SCHEMA error can
- only occur when using the
- sqlite3_prepare()/sqlite3_step()/sqlite3_finalize()
- API to execute SQL, not when using the
- sqlite3_exec(). This was not
- the case in version 2.
-
-
The most common reason for a prepared statement to become invalid
- is that the schema of the database was modified after the SQL was
- prepared (possibly by another process). The other reasons this can
- happen are:
A user-function definition was deleted or changed.
-
A collation sequence definition was deleted or changed.
-
The authorization function was changed.
-
-
-
In all cases, the solution is to recompile the statement from SQL
- and attempt to execute it again. Because a prepared statement can be
- invalidated by another process changing the database schema, all code
- that uses the
- sqlite3_prepare()/sqlite3_step()/sqlite3_finalize()
- API should be prepared to handle SQLITE_SCHEMA errors. An example
- of one approach to this follows:
-
-
-
- int rc;
- sqlite3_stmt *pStmt;
- char zSql[] = "SELECT .....";
-
- do {
- /* Compile the statement from SQL. Assume success. */
- sqlite3_prepare(pDb, zSql, -1, &pStmt, 0);
-
- while( SQLITE_ROW==sqlite3_step(pStmt) ){
- /* Do something with the row of available data */
- }
-
- /* Finalize the statement. If an SQLITE_SCHEMA error has
- ** occured, then the above call to sqlite3_step() will have
- ** returned SQLITE_ERROR. sqlite3_finalize() will return
- ** SQLITE_SCHEMA. In this case the loop will execute again.
- */
- rc = sqlite3_finalize(pStmt);
- } while( rc==SQLITE_SCHEMA );
-
-
-}
-
-faq {Why does ROUND(9.95,1) return 9.9 instead of 10.0?
- Shouldn't 9.95 round up?} {
-
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.
-
-
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.
-}
-
-# End of questions and answers.
-#############
-
-puts {
-This document describes the disk file format for SQLite versions 2.1
-through 2.8. SQLite version 3.0 and following uses a very different
-format which is described separately.
-
-
-
1.0 Layers
-
-
-SQLite is implemented in layers.
-(See the architecture description.)
-The format of database files is determined by three different
-layers in the architecture.
-
-
-
-
The schema layer implemented by the VDBE.
-
The b-tree layer implemented by btree.c
-
The pager layer implemented by pager.c
-
-
-
-We will describe each layer beginning with the bottom (pager)
-layer and working upwards.
-
-
-
2.0 The Pager Layer
-
-
-An SQLite database consists of
-"pages" of data. Each page is 1024 bytes in size.
-Pages are numbered beginning with 1.
-A page number of 0 is used to indicate "no such page" in the
-B-Tree and Schema layers.
-
-
-
-The pager layer is responsible for implementing transactions
-with atomic commit and rollback. It does this using a separate
-journal file. Whenever a new transaction is started, a journal
-file is created that records the original state of the database.
-If the program terminates before completing the transaction, the next
-process to open the database can use the journal file to restore
-the database to its original state.
-
-
-
-The journal file is located in the same directory as the database
-file and has the same name as the database file but with the
-characters "-journal" appended.
-
-
-
-The pager layer does not impose any content restrictions on the
-main database file. As far as the pager is concerned, each page
-contains 1024 bytes of arbitrary data. But there is structure to
-the journal file.
-
-
-
-A journal file begins with 8 bytes as follows:
-0xd9, 0xd5, 0x05, 0xf9, 0x20, 0xa1, 0x63, and 0xd6.
-Processes that are attempting to rollback a journal use these 8 bytes
-as a sanity check to make sure the file they think is a journal really
-is a valid journal. Prior version of SQLite used different journal
-file formats. The magic numbers for these prior formats are different
-so that if a new version of the library attempts to rollback a journal
-created by an earlier version, it can detect that the journal uses
-an obsolete format and make the necessary adjustments. This article
-describes only the newest journal format - supported as of version
-2.8.0.
-
-
-
-Following the 8 byte prefix is a three 4-byte integers that tell us
-the number of pages that have been committed to the journal,
-a magic number used for
-sanity checking each page, and the
-original size of the main database file before the transaction was
-started. The number of committed pages is used to limit how far
-into the journal to read. The use of the checksum magic number is
-described below.
-The original size of the database is used to restore the database
-file back to its original size.
-The size is expressed in pages (1024 bytes per page).
-
-
-
-All three integers in the journal header and all other multi-byte
-numbers used in the journal file are big-endian.
-That means that the most significant byte
-occurs first. That way, a journal file that is
-originally created on one machine can be rolled back by another
-machine that uses a different byte order. So, for example, a
-transaction that failed to complete on your big-endian SparcStation
-can still be rolled back on your little-endian Linux box.
-
-
-
-After the 8-byte prefix and the three 4-byte integers, the
-journal file consists of zero or more page records. Each page
-record is a 4-byte (big-endian) page number followed by 1024 bytes
-of data and a 4-byte checksum.
-The data is the original content of the database page
-before the transaction was started. So to roll back the transaction,
-the data is simply written into the corresponding page of the
-main database file. Pages can appear in the journal in any order,
-but they are guaranteed to appear only once. All page numbers will be
-between 1 and the maximum specified by the page size integer that
-appeared at the beginning of the journal.
-
-
-
-The so-called checksum at the end of each record is not really a
-checksum - it is the sum of the page number and the magic number which
-was the second integer in the journal header. The purpose of this
-value is to try to detect journal corruption that might have occurred
-because of a power loss or OS crash that occurred which the journal
-file was being written to disk. It could have been the case that the
-meta-data for the journal file, specifically the size of the file, had
-been written to the disk so that when the machine reboots it appears that
-file is large enough to hold the current record. But even though the
-file size has changed, the data for the file might not have made it to
-the disk surface at the time of the OS crash or power loss. This means
-that after reboot, the end of the journal file will contain quasi-random
-garbage data. The checksum is an attempt to detect such corruption. If
-the checksum does not match, that page of the journal is not rolled back.
-
-The B-Tree layer builds on top of the pager layer to implement
-one or more separate b-trees all in the same disk file. The
-algorithms used are taken from Knuth's The Art Of Computer
-Programming.
-
-
-Page 1 of a database contains a header string used for sanity
-checking, a few 32-bit words of configuration data, and a pointer
-to the beginning of a list of unused pages in the database.
-All other pages in the
-database are either pages of a b-tree, overflow pages, or unused
-pages on the freelist.
-
-
-
-Each b-tree page contains zero or more database entries.
-Each entry has an unique key of one or more bytes and data of
-zero or more bytes.
-Both the key and data are arbitrary byte sequences. The combination
-of key and data are collectively known as "payload". The current
-implementation limits the amount of payload in a single entry to
-1048576 bytes. This limit can be raised to 16777216 by adjusting
-a single #define in the source code and recompiling. But most entries
-contain less than a hundred bytes of payload so a megabyte limit seems
-more than enough.
-
-
-
-Up to 238 bytes of payload for an entry can be held directly on
-a b-tree page. Any additional payload is contained on a linked list
-of overflow pages. This limit on the amount of payload held directly
-on b-tree pages guarantees that each b-tree page can hold at least
-4 entries. In practice, most entries are smaller than 238 bytes and
-thus most pages can hold more than 4 entries.
-
-
-
-A single database file can hold any number of separate, independent b-trees.
-Each b-tree is identified by its root page, which never changes.
-Child pages of the b-tree may change as entries are added and removed
-and pages split and combine. But the root page always stays the same.
-The b-tree itself does not record which pages are root pages and which
-are not. That information is handled entirely at the schema layer.
-
-
-
3.1 B-Tree Page 1 Details
-
-
-Page 1 begins with the following 48-byte string:
-
-
-
-** This file contains an SQLite 2.1 database **
-
-
-
-If you count the number of characters in the string above, you will
-see that there are only 47. A '\000' terminator byte is added to
-bring the total to 48.
-
-
-
-A frequent question is why the string says version 2.1 when (as
-of this writing) we are up to version 2.7.0 of SQLite and any
-change to the second digit of the version is suppose to represent
-a database format change. The answer to this is that the B-tree
-layer has not changed any since version 2.1. There have been
-database format changes since version 2.1 but those changes have
-all been in the schema layer. Because the format of the b-tree
-layer is unchanged since version 2.1.0, the header string still
-says version 2.1.
-
-
-
-After the format string is a 4-byte integer used to determine the
-byte-order of the database. The integer has a value of
-0xdae37528. If this number is expressed as 0xda, 0xe3, 0x75, 0x28, then
-the database is in a big-endian format and all 16 and 32-bit integers
-elsewhere in the b-tree layer are also big-endian. If the number is
-expressed as 0x28, 0x75, 0xe3, and 0xda, then the database is in a
-little-endian format and all other multi-byte numbers in the b-tree
-layer are also little-endian.
-Prior to version 2.6.3, the SQLite engine was only able to read databases
-that used the same byte order as the processor they were running on.
-But beginning with 2.6.3, SQLite can read or write databases in any
-byte order.
-
-
-
-After the byte-order code are six 4-byte integers. Each integer is in the
-byte order determined by the byte-order code. The first integer is the
-page number for the first page of the freelist. If there are no unused
-pages in the database, then this integer is 0. The second integer is
-the number of unused pages in the database. The last 4 integers are
-not used by the b-tree layer. These are the so-called "meta" values that
-are passed up to the schema layer
-and used there for configuration and format version information.
-All bytes of page 1 past beyond the meta-value integers are unused
-and are initialized to zero.
-
-
-
-Here is a summary of the information contained on page 1 in the b-tree layer:
-
-
-
-
48 byte header string
-
4 byte integer used to determine the byte-order
-
4 byte integer which is the first page of the freelist
-
4 byte integer which is the number of pages on the freelist
-
36 bytes of meta-data arranged as nine 4-byte integers
-
928 bytes of unused space
-
-
-
3.2 Structure Of A Single B-Tree Page
-
-
-Conceptually, a b-tree page contains N database entries and N+1 pointers
-to other b-tree pages.
-
-
-
-
-
-
Ptr 0
-
Entry 0
-
Ptr 1
-
Entry 1
-
...
-
Ptr N-1
-
Entry N-1
-
Ptr N
-
-
-
-
-
-The entries are arranged in increasing order. That is, the key to
-Entry 0 is less than the key to Entry 1, and the key to Entry 1 is
-less than the key of Entry 2, and so forth. The pointers point to
-pages containing additional entries that have keys in between the
-entries on either side. So Ptr 0 points to another b-tree page that
-contains entries that all have keys less than Key 0, and Ptr 1
-points to a b-tree pages where all entries have keys greater than Key 0
-but less than Key 1, and so forth.
-
-
-
-Each b-tree page in SQLite consists of a header, zero or more "cells"
-each holding a single entry and pointer, and zero or more "free blocks"
-that represent unused space on the page.
-
-
-
-The header on a b-tree page is the first 8 bytes of the page.
-The header contains the value
-of the right-most pointer (Ptr N) and the byte offset into the page
-of the first cell and the first free block. The pointer is a 32-bit
-value and the offsets are each 16-bit values. We have:
-
-
-
-
-
-
0
-
1
-
2
-
3
-
4
-
5
-
6
-
7
-
-
-
Ptr N
-
Cell 0
-
Freeblock 0
-
-
-
-
-
-The 1016 bytes of a b-tree page that come after the header contain
-cells and freeblocks. All 1016 bytes are covered by either a cell
-or a freeblock.
-
-
-
-The cells are connected in a linked list. Cell 0 contains Ptr 0 and
-Entry 0. Bytes 4 and 5 of the header point to Cell 0. Cell 0 then
-points to Cell 1 which contains Ptr 1 and Entry 1. And so forth.
-Cells vary in size. Every cell has a 12-byte header and at least 4
-bytes of payload space. Space is allocated to payload in increments
-of 4 bytes. Thus the minimum size of a cell is 16 bytes and up to
-63 cells can fit on a single page. The size of a cell is always a multiple
-of 4 bytes.
-A cell can have up to 238 bytes of payload space. If
-the payload is more than 238 bytes, then an additional 4 byte page
-number is appended to the cell which is the page number of the first
-overflow page containing the additional payload. The maximum size
-of a cell is thus 254 bytes, meaning that a least 4 cells can fit into
-the 1016 bytes of space available on a b-tree page.
-An average cell is usually around 52 to 100 bytes in size with about
-10 or 20 cells to a page.
-
-
-
-The data layout of a cell looks like this:
-
-
-
-
-
-
0
-
1
-
2
-
3
-
4
-
5
-
6
-
7
-
8
-
9
-
10
-
11
-
12 ... 249
-
250
-
251
-
252
-
253
-
-
-
Ptr
-
Keysize (low)
-
Next
-
Ksz (hi)
-
Dsz (hi)
-
Datasize (low)
-
Payload
-
Overflow Pointer
-
-
-
-
-
-The first four bytes are the pointer. The size of the key is a 24-bit
-where the upper 8 bits are taken from byte 8 and the lower 16 bits are
-taken from bytes 4 and 5 (or bytes 5 and 4 on little-endian machines.)
-The size of the data is another 24-bit value where the upper 8 bits
-are taken from byte 9 and the lower 16 bits are taken from bytes 10 and
-11 or 11 and 10, depending on the byte order. Bytes 6 and 7 are the
-offset to the next cell in the linked list of all cells on the current
-page. This offset is 0 for the last cell on the page.
-
-
-
-The payload itself can be any number of bytes between 1 and 1048576.
-But space to hold the payload is allocated in 4-byte chunks up to
-238 bytes. If the entry contains more than 238 bytes of payload, then
-additional payload data is stored on a linked list of overflow pages.
-A 4 byte page number is appended to the cell that contains the first
-page of this linked list.
-
-
-
-Each overflow page begins with a 4-byte value which is the
-page number of the next overflow page in the list. This value is
-0 for the last page in the list. The remaining
-1020 bytes of the overflow page are available for storing payload.
-Note that a full page is allocated regardless of the number of overflow
-bytes stored. Thus, if the total payload for an entry is 239 bytes,
-the first 238 are stored in the cell and the overflow page stores just
-one byte.
-
-
-
-The structure of an overflow page looks like this:
-
-
-
-
-
-
0
-
1
-
2
-
3
-
4 ... 1023
-
-
-
Next Page
-
Overflow Data
-
-
-
-
-
-All space on a b-tree page which is not used by the header or by cells
-is filled by freeblocks. Freeblocks, like cells, are variable in size.
-The size of a freeblock is at least 4 bytes and is always a multiple of
-4 bytes.
-The first 4 bytes contain a header and the remaining bytes
-are unused. The structure of the freeblock is as follows:
-
-
-
-
-
-
0
-
1
-
2
-
3
-
4 ... 1015
-
-
-
Size
-
Next
-
Unused
-
-
-
-
-
-Freeblocks are stored in a linked list in increasing order. That is
-to say, the first freeblock occurs at a lower index into the page than
-the second free block, and so forth. The first 2 bytes of the header
-are an integer which is the total number of bytes in the freeblock.
-The second 2 bytes are the index into the page of the next freeblock
-in the list. The last freeblock has a Next value of 0.
-
-
-
-When a new b-tree is created in a database, the root page of the b-tree
-consist of a header and a single 1016 byte freeblock. As entries are
-added, space is carved off of that freeblock and used to make cells.
-When b-tree entries are deleted, the space used by their cells is converted
-into freeblocks. Adjacent freeblocks are merged, but the page can still
-become fragmented. The b-tree code will occasionally try to defragment
-the page by moving all cells to the beginning and constructing a single
-freeblock at the end to take up all remaining space.
-
-
-
3.3 The B-Tree Free Page List
-
-
-When information is removed from an SQLite database such that one or
-more pages are no longer needed, those pages are added to a list of
-free pages so that they can be reused later when new information is
-added. This subsection describes the structure of this freelist.
-
-
-
-The 32-bit integer beginning at byte-offset 52 in page 1 of the database
-contains the address of the first page in a linked list of free pages.
-If there are no free pages available, this integer has a value of 0.
-The 32-bit integer at byte-offset 56 in page 1 contains the number of
-free pages on the freelist.
-
-
-
-The freelist contains a trunk and many branches. The trunk of
-the freelist is composed of overflow pages. That is to say, each page
-contains a single 32-bit integer at byte offset 0 which
-is the page number of the next page on the freelist trunk.
-The payload area
-of each trunk page is used to record pointers to branch pages.
-The first 32-bit integer in the payload area of a trunk page
-is the number of branch pages to follow (between 0 and 254)
-and each subsequent 32-bit integer is a page number for a branch page.
-The following diagram shows the structure of a trunk freelist page:
-
-
-
-
-
-
0
-
1
-
2
-
3
-
4
-
5
-
6
-
7
-
8 ... 1023
-
-
-
Next trunk page
-
# of branch pages
-
Page numbers for branch pages
-
-
-
-
-
-It is important to note that only the pages on the trunk of the freelist
-contain pointers to other pages. The branch pages contain no
-data whatsoever. The fact that the branch pages are completely
-blank allows for an important optimization in the paging layer. When
-a branch page is removed from the freelist to be reused, it is not
-necessary to write the original content of that page into the rollback
-journal. The branch page contained no data to begin with, so there is
-no need to restore the page in the event of a rollback. Similarly,
-when a page is not longer needed and is added to the freelist as a branch
-page, it is not necessary to write the content of that page
-into the database file.
-Again, the page contains no real data so it is not necessary to record the
-content of that page. By reducing the amount of disk I/O required,
-these two optimizations allow some database operations
-to go four to six times faster than they would otherwise.
-
-
-
4.0 The Schema Layer
-
-
-The schema layer implements an SQL database on top of one or more
-b-trees and keeps track of the root page numbers for all b-trees.
-Where the b-tree layer provides only unformatted data storage with
-a unique key, the schema layer allows each entry to contain multiple
-columns. The schema layer also allows indices and non-unique key values.
-
-
-
-The schema layer implements two separate data storage abstractions:
-tables and indices. Each table and each index uses its own b-tree
-but they use the b-tree capabilities in different ways. For a table,
-the b-tree key is a unique 4-byte integer and the b-tree data is the
-content of the table row, encoded so that columns can be separately
-extracted. For indices, the b-tree key varies in size depending on the
-size of the fields being indexed and the b-tree data is empty.
-
-
-
4.1 SQL Table Implementation Details
-
-
Each row of an SQL table is stored in a single b-tree entry.
-The b-tree key is a 4-byte big-endian integer that is the ROWID
-or INTEGER PRIMARY KEY for that table row.
-The key is stored in a big-endian format so
-that keys will sort in numerical order using memcmp() function.
-
-
The content of a table row is stored in the data portion of
-the corresponding b-tree table. The content is encoded to allow
-individual columns of the row to be extracted as necessary. Assuming
-that the table has N columns, the content is encoded as N+1 offsets
-followed by N column values, as follows:
-
-
-
-
-
-
offset 0
-
offset 1
-
...
-
offset N-1
-
offset N
-
value 0
-
value 1
-
...
-
value N-1
-
-
-
-
-
-The offsets can be either 8-bit, 16-bit, or 24-bit integers depending
-on how much data is to be stored. If the total size of the content
-is less than 256 bytes then 8-bit offsets are used. If the total size
-of the b-tree data is less than 65536 then 16-bit offsets are used.
-24-bit offsets are used otherwise. Offsets are always little-endian,
-which means that the least significant byte occurs first.
-
-
-
-Data is stored as a nul-terminated string. Any empty string consists
-of just the nul terminator. A NULL value is an empty string with no
-nul-terminator. Thus a NULL value occupies zero bytes and an empty string
-occupies 1 byte.
-
-
-
-Column values are stored in the order that they appear in the CREATE TABLE
-statement. The offsets at the beginning of the record contain the
-byte index of the corresponding column value. Thus, Offset 0 contains
-the byte index for Value 0, Offset 1 contains the byte offset
-of Value 1, and so forth. The number of bytes in a column value can
-always be found by subtracting offsets. This allows NULLs to be
-recovered from the record unambiguously.
-
-
-
-Most columns are stored in the b-tree data as described above.
-The one exception is column that has type INTEGER PRIMARY KEY.
-INTEGER PRIMARY KEY columns correspond to the 4-byte b-tree key.
-When an SQL statement attempts to read the INTEGER PRIMARY KEY,
-the 4-byte b-tree key is read rather than information out of the
-b-tree data. But there is still an Offset associated with the
-INTEGER PRIMARY KEY, just like any other column. But the Value
-associated with that offset is always NULL.
-
-
-
4.2 SQL Index Implementation Details
-
-
-SQL indices are implement using a b-tree in which the key is used
-but the data is always empty. The purpose of an index is to map
-one or more column values into the ROWID for the table entry that
-contains those column values.
-
-
-
-Each b-tree in an index consists of one or more column values followed
-by a 4-byte ROWID. Each column value is nul-terminated (even NULL values)
-and begins with a single character that indicates the datatype for that
-column value. Only three datatypes are supported: NULL, Number, and
-Text. NULL values are encoded as the character 'a' followed by the
-nul terminator. Numbers are encoded as the character 'b' followed by
-a string that has been crafted so that sorting the string using memcmp()
-will sort the corresponding numbers in numerical order. (See the
-sqliteRealToSortable() function in util.c of the SQLite sources for
-additional information on this encoding.) Numbers are also nul-terminated.
-Text values consists of the character 'c' followed by a copy of the
-text string and a nul-terminator. These encoding rules result in
-NULLs being sorted first, followed by numerical values in numerical
-order, followed by text values in lexicographical order.
-
-
-
4.4 SQL Schema Storage And Root B-Tree Page Numbers
-
-
-The database schema is stored in the database in a special tabled named
-"sqlite_master" and which always has a root b-tree page number of 2.
-This table contains the original CREATE TABLE,
-CREATE INDEX, CREATE VIEW, and CREATE TRIGGER statements used to define
-the database to begin with. Whenever an SQLite database is opened,
-the sqlite_master table is scanned from beginning to end and
-all the original CREATE statements are played back through the parser
-in order to reconstruct an in-memory representation of the database
-schema for use in subsequent command parsing. For each CREATE TABLE
-and CREATE INDEX statement, the root page number for the corresponding
-b-tree is also recorded in the sqlite_master table so that SQLite will
-know where to look for the appropriate b-tree.
-
-
-
-SQLite users can query the sqlite_master table just like any other table
-in the database. But the sqlite_master table cannot be directly written.
-The sqlite_master table is automatically updated in response to CREATE
-and DROP statements but it cannot be changed using INSERT, UPDATE, or
-DELETE statements as that would risk corrupting the database.
-
-
-
-SQLite stores temporary tables and indices in a separate
-file from the main database file. The temporary table database file
-is the same structure as the main database file. The schema table
-for the temporary tables is stored on page 2 just as in the main
-database. But the schema table for the temporary database named
-"sqlite_temp_master" instead of "sqlite_master". Other than the
-name change, it works exactly the same.
-
-
-
4.4 Schema Version Numbering And Other Meta-Information
-
-
-The nine 32-bit integers that are stored beginning at byte offset
-60 of Page 1 in the b-tree layer are passed up into the schema layer
-and used for versioning and configuration information. The meaning
-of the first four integers is shown below. The other five are currently
-unused.
-
-
-
-
The schema version number
-
The format version number
-
The recommended pager cache size
-
The safety level
-
-
-
-The first meta-value, the schema version number, is used to detect when
-the schema of the database is changed by a CREATE or DROP statement.
-Recall that when a database is first opened the sqlite_master table is
-scanned and an internal representation of the tables, indices, views,
-and triggers for the database is built in memory. This internal
-representation is used for all subsequent SQL command parsing and
-execution. But what if another process were to change the schema
-by adding or removing a table, index, view, or trigger? If the original
-process were to continue using the old schema, it could potentially
-corrupt the database by writing to a table that no longer exists.
-To avoid this problem, the schema version number is changed whenever
-a CREATE or DROP statement is executed. Before each command is
-executed, the current schema version number for the database file
-is compared against the schema version number from when the sqlite_master
-table was last read. If those numbers are different, the internal
-schema representation is erased and the sqlite_master table is reread
-to reconstruct the internal schema representation.
-(Calls to sqlite_exec() generally return SQLITE_SCHEMA when this happens.)
-
-
-
-The second meta-value is the schema format version number. This
-number tells what version of the schema layer should be used to
-interpret the file. There have been changes to the schema layer
-over time and this number is used to detect when an older database
-file is being processed by a newer version of the library.
-As of this writing (SQLite version 2.7.0) the current format version
-is "4".
-
-
-
-The third meta-value is the recommended pager cache size as set
-by the DEFAULT_CACHE_SIZE pragma. If the value is positive it
-means that synchronous behavior is enable (via the DEFAULT_SYNCHRONOUS
-pragma) and if negative it means that synchronous behavior is
-disabled.
-
-
-
-The fourth meta-value is safety level added in version 2.8.0.
-A value of 1 corresponds to a SYNCHRONOUS setting of OFF. In other
-words, SQLite does not pause to wait for journal data to reach the disk
-surface before overwriting pages of the database. A value of 2 corresponds
-to a SYNCHRONOUS setting of NORMAL. A value of 3 corresponds to a
-SYNCHRONOUS setting of FULL. If the value is 0, that means it has not
-been initialized so the default synchronous setting of NORMAL is used.
-
-}
-footer $rcsid
DELETED www/formatchng.tcl
Index: www/formatchng.tcl
==================================================================
--- www/formatchng.tcl
+++ /dev/null
@@ -1,285 +0,0 @@
-#
-# Run this Tcl script to generate the formatchng.html file.
-#
-set rcsid {$Id: formatchng.tcl,v 1.20 2007/09/03 20:32:45 drh Exp $ }
-source common.tcl
-header {File Format Changes in SQLite}
-puts {
-
File Format Changes in SQLite
-
-
-Every effort is made to keep SQLite fully backwards compatible from
-one release to the next. Rarely, however, some
-enhancements or bug fixes may require a change to
-the underlying file format. When this happens and you
-must convert the contents of your
-databases into a portable ASCII representation using the old version
-of the library then reload the data using the new version of the
-library.
-
-
-
-You can tell if you should reload your databases by comparing the
-version numbers of the old and new libraries. If the first digit
-of the version number is different, then a reload of the database will
-be required. If the second digit changes, newer versions of SQLite
-will be able to read and write older database files, but older versions
-of the library may have difficulty reading or writing newer database
-files.
-For example, upgrading from
-version 2.8.14 to 3.0.0 requires a reload. Going from
-version 3.0.8 to 3.1.0 is backwards compatible but not necessarily
-forwards compatible.
-
-
-
-The following table summarizes the SQLite file format changes that have
-occurred since version 1.0.0:
-
-
-
-
-
-
Version Change
-
Approx. Date
-
Description Of File Format Change
-
-
-
1.0.32 to 2.0.0
-
2001-Sep-20
-
Version 1.0.X of SQLite used the GDBM library as its backend
- interface to the disk. Beginning in version 2.0.0, GDBM was replaced
- by a custom B-Tree library written especially for SQLite. The new
- B-Tree backend is twice as fast as GDBM, supports atomic commits and
- rollback, and stores an entire database in a single disk file instead
- using a separate file for each table as GDBM does. The two
- file formats are not even remotely similar.
-
-
-
2.0.8 to 2.1.0
-
2001-Nov-12
-
The same basic B-Tree format is used but the details of the
- index keys were changed in order to provide better query
- optimization opportunities. Some of the headers were also changed in order
- to increase the maximum size of a row from 64KB to 24MB.
-
- This change is an exception to the version number rule described above
- in that it is neither forwards or backwards compatible. A complete
- reload of the database is required. This is the only exception.
-
-
-
2.1.7 to 2.2.0
-
2001-Dec-21
-
Beginning with version 2.2.0, SQLite no longer builds an index for
- an INTEGER PRIMARY KEY column. Instead, it uses that column as the actual
- B-Tree key for the main table.
Version 2.2.0 and later of the library
- will automatically detect when it is reading a 2.1.x database and will
- disable the new INTEGER PRIMARY KEY feature. In other words, version
- 2.2.x is backwards compatible to version 2.1.x. But version 2.1.x is not
- forward compatible with version 2.2.x. If you try to open
- a 2.2.x database with an older 2.1.x library and that database contains
- an INTEGER PRIMARY KEY, you will likely get a coredump. If the database
- schema does not contain any INTEGER PRIMARY KEYs, then the version 2.1.x
- and version 2.2.x database files will be identical and completely
- interchangeable.
-
-
-
2.2.5 to 2.3.0
-
2002-Jan-30
-
Beginning with version 2.3.0, SQLite supports some additional syntax
- (the "ON CONFLICT" clause) in the CREATE TABLE and CREATE INDEX statements
- that are stored in the SQLITE_MASTER table. If you create a database that
- contains this new syntax, then try to read that database using version 2.2.5
- or earlier, the parser will not understand the new syntax and you will get
- an error. Otherwise, databases for 2.2.x and 2.3.x are interchangeable.
-
-
-
2.3.3 to 2.4.0
-
2002-Mar-10
-
Beginning with version 2.4.0, SQLite added support for views.
- Information about views is stored in the SQLITE_MASTER table. If an older
- version of SQLite attempts to read a database that contains VIEW information
- in the SQLITE_MASTER table, the parser will not understand the new syntax
- and initialization will fail. Also, the
- way SQLite keeps track of unused disk blocks in the database file
- changed slightly.
- If an older version of SQLite attempts to write a database that
- was previously written by version 2.4.0 or later, then it may leak disk
- blocks.
-
-
-
2.4.12 to 2.5.0
-
2002-Jun-17
-
Beginning with version 2.5.0, SQLite added support for triggers.
- Information about triggers is stored in the SQLITE_MASTER table. If an older
- version of SQLite attempts to read a database that contains a CREATE TRIGGER
- in the SQLITE_MASTER table, the parser will not understand the new syntax
- and initialization will fail.
-
-
-
-
2.5.6 to 2.6.0
-
2002-July-17
-
A design flaw in the layout of indices required a file format change
- to correct. This change appeared in version 2.6.0.
-
- If you use version 2.6.0 or later of the library to open a database file
- that was originally created by version 2.5.6 or earlier, an attempt to
- rebuild the database into the new format will occur automatically.
- This can take some time for a large database. (Allow 1 or 2 seconds
- per megabyte of database under Unix - longer under Windows.) This format
- conversion is irreversible. It is strongly suggested
- that you make a backup copy of older database files prior to opening them
- with version 2.6.0 or later of the library, in case there are errors in
- the format conversion logic.
-
- Version 2.6.0 or later of the library cannot open read-only database
- files from version 2.5.6 or earlier, since read-only files cannot be
- upgraded to the new format.
-
-
-
-
2.6.3 to 2.7.0
-
2002-Aug-13
-
Beginning with version 2.7.0, SQLite understands two different
- datatypes: text and numeric. Text data sorts in memcmp() order.
- Numeric data sorts in numerical order if it looks like a number,
- or in memcmp() order if it does not.
-
-
When SQLite version 2.7.0 or later opens a 2.6.3 or earlier database,
- it assumes all columns of all tables have type "numeric". For 2.7.0
- and later databases, columns have type "text" if their datatype
- string contains the substrings "char" or "clob" or "blob" or "text".
- Otherwise they are of type "numeric".
-
-
Because "text" columns have a different sort order from numeric,
- indices on "text" columns occur in a different order for version
- 2.7.0 and later database. Hence version 2.6.3 and earlier of SQLite
- will be unable to read a 2.7.0 or later database. But version 2.7.0
- and later of SQLite will read earlier databases.
-
-
-
-
2.7.6 to 2.8.0
-
2003-Feb-14
-
Version 2.8.0 introduces a change to the format of the rollback
- journal file. The main database file format is unchanged. Versions
- 2.7.6 and earlier can read and write 2.8.0 databases and vice versa.
- Version 2.8.0 can rollback a transaction that was started by version
- 2.7.6 and earlier. But version 2.7.6 and earlier cannot rollback a
- transaction started by version 2.8.0 or later.
-
-
The only time this would ever be an issue is when you have a program
- using version 2.8.0 or later that crashes with an incomplete
- transaction, then you try to examine the database using version 2.7.6 or
- earlier. The 2.7.6 code will not be able to read the journal file
- and thus will not be able to rollback the incomplete transaction
- to restore the database.
-
-
-
-
2.8.14 to 3.0.0
-
2004-Jun-18
-
Version 3.0.0 is a major upgrade for SQLite that incorporates
- support for UTF-16, BLOBs, and a more compact encoding that results
- in database files that are typically 25% to 50% smaller. The new file
- format is very different and is completely incompatible with the
- version 2 file format.
-
-
-
-
3.0.8 to 3.1.0
-
2005-Jan-21
-
Version 3.1.0 adds support for
- autovacuum mode.
- Prior versions of SQLite will be able to read an autovacuumed
- database but will not be able to write it. If autovaccum is disabled
- (which is the default condition)
- then databases are fully forwards and backwards compatible.
-
-
-
-
3.1.6 to 3.2.0
-
2005-Mar-19
-
Version 3.2.0 adds support for the
- ALTER TABLE ADD COLUMN
- command. A database that has been modified by this command can
- not be read by a version of SQLite prior to 3.1.4. Running
- VACUUM
- after the ALTER TABLE
- restores the database to a format such that it can be read by earlier
- SQLite versions.
-
-
-
-
3.2.8 to 3.3.0
-
2006-Jan-10
-
Version 3.3.0 adds support for descending indices and
- uses a new encoding for boolean values that requires
- less disk space. Version 3.3.0 can read and write database
- files created by prior versions of SQLite. But prior versions
- of SQLite will not be able to read or write databases created
- by Version 3.3.0
-
If you need backwards and forwards capatibility, you can
- compile with -DSQLITE_DEFAULT_FILE_FORMAT=1. Or at runtime
- you can say "PRAGMA legacy_file_format=ON" prior to creating
- a new database file
-
Once a database file is created, its format is fixed. So
- a database file created by SQLite 3.2.8 and merely modified
- by version 3.3.0 or later will retain the old format. Except,
- the VACUUM command recreates the database so running VACUUM
- on 3.3.0 or later will change the file format to the latest
- edition.
-
-
-
-
3.3.6 to 3.3.7
-
2006-Aug-12
-
The previous file format change has caused so much
- grief that the default behavior has been changed back to
- the original file format. This means that DESC option on
- indices is ignored by default that the more efficient encoding
- of boolean values is not used. In that way, older versions
- of SQLite can read and write databases created by newer
- versions. If the new features are desired, they can be
- enabled using pragma: "PRAGMA legacy_file_format=OFF".
-
To be clear: both old and new file formats continue to
- be understood and continue to work. But the old file format
- is used by default instead of the new. This might change
- again in some future release - we may go back to generating
- the new file format by default - but probably not until
- all users have upgraded to a version of SQLite that will
- understand the new file format. That might take several
- years.
-
-
-
3.4.2 to 3.5.0
-
2007-Sep-3
-
The design of the OS interface layer was changed for
- release 3.5.0. Applications that implemented a custom OS
- interface will need to be modified in order to upgrade.
- There are also some subtly different semantics a few obscure
- APIs. An article is avilable which
- describing the changes in detail.
-
-
The on-disk file format is unchanged.
-
-
-
-
-
-
-To perform a database reload, have ready versions of the
-sqlite command-line utility for both the old and new
-version of SQLite. Call these two executables "sqlite-old"
-and "sqlite-new". Suppose the name of your old database
-is "old.db" and you want to create a new database with
-the same information named "new.db". The command to do
-this is as follows:
-
Sources are in the public domain.
- Use for any purpose.
-
-
-
-
-The SQLite distribution comes with a standalone command-line
-access program (sqlite) that can
-be used to administer an SQLite database and which serves as
-an example of how to use the SQLite library.
-
-
-
-
-
-
News
-}
-
-proc newsitem {date title text} {
- puts "
$date - $title
"
- regsub -all "\n( *\n)+" $text "\n\n
" txt
- puts "
$txt
"
- puts ""
-}
-
-newsitem {2007-Nov-05} {Version 3.5.2} {
- This is an incremental release that fixes several minor problems,
- adds some obscure features, and provides some performance tweaks.
- Upgrading is optional.
-
- The experimental compile-time option
- SQLITE_OMIT_MEMORY_ALLOCATION is no longer supported. On the other
- hand, it is now possible to compile SQLite so that it uses a static
- array for all its dynamic memory allocation needs and never calls
- malloc. Expect to see additional radical changes to the memory
- allocation subsystem in future releases.
-}
-
-newsitem {2007-Oct-04} {Version 3.5.1} {
- Fix a long-standing bug that might cause database corruption if a
- disk-full error occurs in the middle of a transaction and that
- transaction is not rolled back.
- Ticket #2686.
-
- The new VFS layer is stable. However, we still reserve the right to
- make tweaks to the interface definition of the VFS if necessary.
-}
-
-newsitem {2007-Sep-04} {Version 3.5.0 alpha} {
- The OS interface layer and the memory allocation subsystems in
- SQLite have been reimplemented. The published API is largely unchanged
- but the (unpublished) OS interface has been modified extensively.
- Applications that implement their own OS interface will require
- modification. See
- 34to35.html for details.
-
- This is a large change. Approximately 10% of the source code was
- modified. We are calling this first release "alpha" in order to give
- the user community time to test and evaluate the changes before we
- freeze the new design.
-}
-
-puts {
-
The SQLite library understands most of the standard SQL
-language. But it does omit some features
-while at the same time
-adding a few features of its own. This document attempts to
-describe precisely what parts of the SQL language SQLite does
-and does not support. A list of keywords is
-also provided.
-
-
In all of the syntax diagrams that follow, literal text is shown in
-bold blue. Non-terminal symbols are shown in italic red. Operators
-that are part of the syntactic markup itself are shown in black roman.
-
-
This document is just an overview of the SQL syntax implemented
-by SQLite. Many low-level productions are omitted. For detailed information
-on the language that SQLite understands, refer to the source code and
-the grammar file "parse.y".
SQLite's version of the ALTER TABLE command allows the user to
-rename or add a new column to an existing table. It is not possible
-to remove a column from a table.
-
-
-
The RENAME TO syntax is used to rename the table identified by
-[database-name.]table-name to new-table-name. This command
-cannot be used to move a table between attached databases, only to rename
-a table within the same database.
-
-
If the table being renamed has triggers or indices, then these remain
-attached to the table after it has been renamed. However, if there are
-any view definitions, or statements executed by triggers that refer to
-the table being renamed, these are not automatically modified to use the new
-table name. If this is required, the triggers or view definitions must be
-dropped and recreated to use the new table name by hand.
-
-
-
The ADD [COLUMN] syntax is used to add a new column to an existing table.
-The new column is always appended to the end of the list of existing columns.
-Column-def may take any of the forms permissable in a CREATE TABLE
-statement, with the following restrictions:
-
-
The column may not have a PRIMARY KEY or UNIQUE constraint.
-
The column may not have a default value of CURRENT_TIME, CURRENT_DATE
- or CURRENT_TIMESTAMP.
-
If a NOT NULL constraint is specified, then the column must have a
- default value other than NULL.
-
-
-
The execution time of the ALTER TABLE command is independent of
-the amount of data in the table. The ALTER TABLE command runs as quickly
-on a table with 10 million rows as it does on a table with 1 row.
-
-
-
After ADD COLUMN has been run on a database, that database will not
-be readable by SQLite version 3.1.3 and earlier until the database
-is VACUUMed.
The ANALYZE command gathers statistics about indices and stores them
-in a special tables in the database where the query optimizer can use
-them to help make better index choices.
-If no arguments are given, all indices in all attached databases are
-analyzed. If a database name is given as the argument, all indices
-in that one database are analyzed. If the argument is a table name,
-then only indices associated with that one table are analyzed.
-
-
The initial implementation stores all statistics in a single
-table named sqlite_stat1. Future enhancements may create
-additional tables with the same name pattern except with the "1"
-changed to a different digit. The sqlite_stat1 table cannot
-be DROPped,
-but all the content can be DELETEd which has the
-same effect.
The ATTACH DATABASE statement adds another database
-file to the current database connection. If the filename contains
-punctuation characters it must be quoted. The names 'main' and
-'temp' refer to the main database and the database used for
-temporary tables. These cannot be detached. Attached databases
-are removed using the DETACH DATABASE
-statement.
-
-
You can read from and write to an attached database and you
-can modify the schema of the attached database. This is a new
-feature of SQLite version 3.0. In SQLite 2.8, schema changes
-to attached databases were not allowed.
-
-
You cannot create a new table with the same name as a table in
-an attached database, but you can attach a database which contains
-tables whose names are duplicates of tables in the main database. It is
-also permissible to attach the same database file multiple times.
-
-
Tables in an attached database can be referred to using the syntax
-database-name.table-name. If an attached table doesn't have
-a duplicate table name in the main database, it doesn't require a
-database name prefix. When a database is attached, all of its
-tables which don't have duplicate names become the default table
-of that name. Any tables of that name attached afterwards require the table
-prefix. If the default table of a given name is detached, then
-the last table of that name attached becomes the new default.
-
-
-Transactions involving multiple attached databases are atomic,
-assuming that the main database is not ":memory:". If the main
-database is ":memory:" then
-transactions continue to be atomic within each individual
-database file. But if the host computer crashes in the middle
-of a COMMIT where two or more database files are updated,
-some of those files might get the changes where others
-might not.
-Atomic commit of attached databases is a new feature of SQLite version 3.0.
-In SQLite version 2.8, all commits to attached databases behaved as if
-the main database were ":memory:".
-
-
-
There is a compile-time limit of 10 attached database files.
-No changes can be made to the database except within a transaction.
-Any command that changes the database (basically, any SQL command
-other than SELECT) will automatically start a transaction if
-one is not already in effect. Automatically started transactions
-are committed at the conclusion of the command.
-
-
-
-Transactions can be started manually using the BEGIN
-command. Such transactions usually persist until the next
-COMMIT or ROLLBACK command. But a transaction will also
-ROLLBACK if the database is closed or if an error occurs
-and the ROLLBACK conflict resolution algorithm is specified.
-See the documentation on the ON CONFLICT
-clause for additional information about the ROLLBACK
-conflict resolution algorithm.
-
-
-
-END TRANSACTION is an alias for COMMIT.
-
-
-
The optional transaction name is current ignored. SQLite
-does not recognize nested transactions at this time.
-However, future versions of SQLite may be enhanced to support nested
-transactions and the transaction name would then become significant.
-Application are advised not to use the transaction name in order
-to avoid future compatibility problems.
-
-
-Transactions can be deferred, immediate, or exclusive.
-The default transaction behavior is deferred.
-Deferred means that no locks are acquired
-on the database until the database is first accessed. Thus with a
-deferred transaction, the BEGIN statement itself does nothing. Locks
-are not acquired until the first read or write operation. The first read
-operation against a database creates a SHARED lock and the first
-write operation creates a RESERVED lock. Because the acquisition of
-locks is deferred until they are needed, it is possible that another
-thread or process could create a separate transaction and write to
-the database after the BEGIN on the current thread has executed.
-If the transaction is immediate, then RESERVED locks
-are acquired on all databases as soon as the BEGIN command is
-executed, without waiting for the
-database to be used. After a BEGIN IMMEDIATE, you are guaranteed that
-no other thread or process will be able to write to the database or
-do a BEGIN IMMEDIATE or BEGIN EXCLUSIVE. Other processes can continue
-to read from the database, however. An exclusive transaction causes
-EXCLUSIVE locks to be acquired on all databases. After a BEGIN
-EXCLUSIVE, you are guaranteed that no other thread or process will
-be able to read or write the database until the transaction is
-complete.
-
-
-
-A description of the meaning of SHARED, RESERVED, and EXCLUSIVE locks
-is available separately.
-
-
-
-The COMMIT command does not actually perform a commit until all
-pending SQL commands finish. Thus if two or more SELECT statements
-are in the middle of processing and a COMMIT is executed, the commit
-will not actually occur until all SELECT statements finish.
-
-
-
-An attempt to execute COMMIT might result in an SQLITE_BUSY return code.
-This indicates that another thread or process had a read lock on the database
-that prevented the database from being updated. When COMMIT fails in this
-way, the transaction remains active and the COMMIT can be retried later
-after the reader has had a chance to clear.
-
-
-
Response To Errors Within A Transaction
-
-
If certain kinds of errors occur within a transaction, the
-transaction may or may not be rolled back automatically. The
-errors that cause the behavior include:
-
-
-
SQLITE_FULL: database or disk full
-
SQLITE_IOERR: disk I/O error
-
SQLITE_BUSY: database in use by another process
-
SQLITE_NOMEM: out or memory
-
SQLITE_INTERRUPT: processing interrupted by user request
-
-
-
-For all of these errors, SQLite attempts to undo just the one statement
-it was working on and leave changes from prior statements within the
-same transaction intact and continue with the transaction. However,
-depending on the statement being evaluated and the point at which the
-error occurs, it might be necessary for SQLite to rollback and
-cancel the transaction. An application can tell which
-course of action SQLite took by using the
-sqlite3_get_autocommit()
-C-language interface.
-
-
It is recommended that applications respond to the errors
-listed above by explicitly issuing a ROLLBACK command. If the
-transaction has already been rolled back automatically
-by the error response, then the ROLLBACK command will fail with an
-error, but no harm is caused by this.
-
-
Future versions of SQLite may extend the list of errors which
-might cause automatic transaction rollback. Future versions of
-SQLite might change the error response. In particular, we may
-choose to simplify the interface in future versions of SQLite by
-causing the errors above to force an unconditional rollback.
Comments aren't SQL commands, but can occur in SQL queries. They are
-treated as whitespace by the parser. They can begin anywhere whitespace
-can be found, including inside expressions that span multiple lines.
-
-
-
SQL comments only extend to the end of the current line.
-
-
C comments can span any number of lines. If there is no terminating
-delimiter, they extend to the end of the input. This is not treated as
-an error. A new SQL statement can begin on a line after a multiline
-comment ends. C comments can be embedded anywhere whitespace can occur,
-including inside expressions, and in the middle of other SQL statements.
-C comments do not nest. SQL comments inside a C comment will be ignored.
-
-}
-
-
-Section COPY copy
-
-Syntax {sql-statement} {
-COPY [ OR ] [ .] FROM
-[ USING DELIMITERS ]
-}
-
-puts {
-
The COPY command is available in SQLite version 2.8 and earlier.
-The COPY command has been removed from SQLite version 3.0 due to
-complications in trying to support it in a mixed UTF-8/16 environment.
-In version 3.0, the command-line shell
-contains a new command .import that can be used as a substitute
-for COPY.
-
-
-
The COPY command is an extension used to load large amounts of
-data into a table. It is modeled after a similar command found
-in PostgreSQL. In fact, the SQLite COPY command is specifically
-designed to be able to read the output of the PostgreSQL dump
-utility pg_dump so that data can be easily transferred from
-PostgreSQL into SQLite.
-
-
The table-name is the name of an existing table which is to
-be filled with data. The filename is a string or identifier that
-names a file from which data will be read. The filename can be
-the STDIN to read data from standard input.
-
-
Each line of the input file is converted into a single record
-in the table. Columns are separated by tabs. If a tab occurs as
-data within a column, then that tab is preceded by a baskslash "\"
-character. A baskslash in the data appears as two backslashes in
-a row. The optional USING DELIMITERS clause can specify a delimiter
-other than tab.
-
-
If a column consists of the character "\N", that column is filled
-with the value NULL.
-
-
The optional conflict-clause allows the specification of an alternative
-constraint conflict resolution algorithm to use for this one command.
-See the section titled
-ON CONFLICT for additional information.
-
-
When the input data source is STDIN, the input can be terminated
-by a line that contains only a baskslash and a dot:}
-puts "\"[Operator \\.]\".
The CREATE INDEX command consists of the keywords "CREATE INDEX" followed
-by the name of the new index, the keyword "ON", the name of a previously
-created table that is to be indexed, and a parenthesized list of names of
-columns in the table that are used for the index key.
-Each column name can be followed by one of the "ASC" or "DESC" keywords
-to indicate sort order, but the sort order is ignored in the current
-implementation. Sorting is always done in ascending order.
-
-
The COLLATE clause following each column name defines a collating
-sequence used for text entires in that column. The default collating
-sequence is the collating sequence defined for that column in the
-CREATE TABLE statement. Or if no collating sequence is otherwise defined,
-the built-in BINARY collating sequence is used.
-
-
There are no arbitrary limits on the number of indices that can be
-attached to a single table, nor on the number of columns in an index.
-
-
If the UNIQUE keyword appears between CREATE and INDEX then duplicate
-index entries are not allowed. Any attempt to insert a duplicate entry
-will result in an error.
-
-
The exact text
-of each CREATE INDEX statement is stored in the sqlite_master
-or sqlite_temp_master table, depending on whether the table
-being indexed is temporary. Every time the database is opened,
-all CREATE INDEX statements
-are read from the sqlite_master table and used to regenerate
-SQLite's internal representation of the index layout.
-
-
If the optional IF NOT EXISTS clause is present and another index
-with the same name aleady exists, then this command becomes a no-op.