$caption" - } - puts "
" -} -proc SYNTAX {text} { - puts "" - puts $text - puts "
"
- set t2 [string map {& & < < > >} $text]
- regsub -all "/(\[^\n/\]+)/" $t2 {\1} t3
- puts "$t3"
- puts "[subst -novar -noback $t3]
\n" -} -proc resolve_link {args} { - set a2 [split $args |] - set id [string trim [lindex $a2 0]] - if {[lindex $a2 1]==""} { - set display [string trim [lindex $a2 0]] - } else { - set display [string trim [lrange $a2 1 end]] - } - regsub -all {[^a-zA-Z0-9_]} $id {} id - return "$display" -} -set level(0) 0 -set level(1) 0 -proc HEADING {n name {tag {}}} { - if {$tag!=""} { - puts "" - } - global level - incr level($n) - for {set i [expr {$n+1}]} {$i<10} {incr i} { - set level($i) 0 - } - if {$n==0} { - set num {} - } elseif {$n==1} { - set num $level(1).0 - } else { - set num $level(1) - for {set i 2} {$i<=$n} {incr i} { - append num .$level($i) - } - } - incr n 1 - puts "| Block Diagram Of SQLite |
|---|
 |
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. -
- -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_.
- -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.
- -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. -
- -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().
- -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. -
- -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. -
- -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. -
- --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. -
- --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. -
- --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. -
- -} -footer $rcsid DELETED arch2.fig Index: arch2.fig ================================================================== --- arch2.fig +++ /dev/null @@ -1,123 +0,0 @@ -#FIG 3.2 -Landscape -Center -Inches -Letter -100.00 -Single --2 -1200 2 -0 32 #000000 -0 33 #868686 -0 34 #dfefd7 -0 35 #d7efef -0 36 #efdbef -0 37 #efdbd7 -0 38 #e7efcf -0 39 #9e9e9e -6 3225 3900 4650 6000 -2 2 0 1 0 7 51 0 20 0.000 0 0 -1 0 0 5 - 3225 5475 4575 5475 4575 5925 3225 5925 3225 5475 -2 2 0 0 0 33 52 0 20 0.000 0 0 -1 0 0 5 - 3300 5550 4650 5550 4650 6000 3300 6000 3300 5550 -2 2 0 1 0 7 51 0 20 0.000 0 0 -1 0 0 5 - 3225 4650 4575 4650 4575 5100 3225 5100 3225 4650 -2 2 0 0 0 33 52 0 20 0.000 0 0 -1 0 0 5 - 3300 4725 4650 4725 4650 5175 3300 5175 3300 4725 -2 2 0 1 0 7 51 0 20 0.000 0 0 -1 0 0 5 - 3225 3900 4575 3900 4575 4350 3225 4350 3225 3900 -2 2 0 0 0 33 52 0 20 0.000 0 0 -1 0 0 5 - 3300 3975 4650 3975 4650 4425 3300 4425 3300 3975 -2 1 0 1 0 38 50 0 -1 0.000 0 0 -1 1 0 2 - 1 1 1.00 60.00 120.00 - 3900 4350 3900 4650 -2 1 0 1 0 38 50 0 -1 0.000 0 0 -1 1 0 2 - 1 1 1.00 60.00 120.00 - 3900 5100 3900 5475 -4 1 0 50 0 2 12 0.0000 4 135 1050 3900 5775 OS Interface\001 -4 1 0 50 0 2 12 0.0000 4 135 615 3900 4200 B-Tree\001 -4 1 0 50 0 2 12 0.0000 4 180 495 3900 4950 Pager\001 --6 -6 5400 4725 6825 5250 -2 2 0 1 0 7 51 0 20 0.000 0 0 -1 0 0 5 - 5400 4725 6750 4725 6750 5175 5400 5175 5400 4725 -2 2 0 0 0 33 52 0 20 0.000 0 0 -1 0 0 5 - 5475 4800 6825 4800 6825 5250 5475 5250 5475 4800 -4 1 0 50 0 2 12 0.0000 4 135 630 6000 5025 Utilities\001 --6 -6 5400 5550 6825 6075 -2 2 0 1 0 7 51 0 20 0.000 0 0 -1 0 0 5 - 5400 5550 6750 5550 6750 6000 5400 6000 5400 5550 -2 2 0 0 0 33 52 0 20 0.000 0 0 -1 0 0 5 - 5475 5625 6825 5625 6825 6075 5475 6075 5475 5625 -4 1 0 50 0 2 12 0.0000 4 135 855 6000 5850 Test Code\001 --6 -6 5400 2775 6825 3750 -2 2 0 0 0 33 52 0 20 0.000 0 0 -1 0 0 5 - 5475 2850 6825 2850 6825 3750 5475 3750 5475 2850 -2 2 0 1 0 7 51 0 20 0.000 0 0 -1 0 0 5 - 5400 2775 6750 2775 6750 3675 5400 3675 5400 2775 -4 1 0 50 0 2 12 0.0000 4 135 420 6075 3150 Code\001 -4 1 0 50 0 2 12 0.0000 4 135 855 6075 3375 Generator\001 --6 -6 5400 1950 6825 2475 -2 2 0 1 0 7 51 0 20 0.000 0 0 -1 0 0 5 - 5400 1950 6750 1950 6750 2400 5400 2400 5400 1950 -2 2 0 0 0 33 52 0 20 0.000 0 0 -1 0 0 5 - 5475 2025 6825 2025 6825 2475 5475 2475 5475 2025 -4 1 0 50 0 2 12 0.0000 4 135 570 6075 2250 Parser\001 --6 -2 2 0 1 0 7 51 0 20 0.000 0 0 -1 0 0 5 - 5400 1050 6750 1050 6750 1500 5400 1500 5400 1050 -2 2 0 0 0 33 52 0 20 0.000 0 0 -1 0 0 5 - 5475 1125 6825 1125 6825 1575 5475 1575 5475 1125 -2 2 0 1 0 7 51 0 20 0.000 0 0 -1 0 0 5 - 3225 1050 4575 1050 4575 1500 3225 1500 3225 1050 -2 2 0 0 0 33 52 0 20 0.000 0 0 -1 0 0 5 - 3300 1125 4650 1125 4650 1575 3300 1575 3300 1125 -2 2 0 1 0 7 51 0 20 0.000 0 0 -1 0 0 5 - 3225 1800 4575 1800 4575 2250 3225 2250 3225 1800 -2 2 0 0 0 33 52 0 20 0.000 0 0 -1 0 0 5 - 3300 1875 4650 1875 4650 2325 3300 2325 3300 1875 -2 2 0 1 0 7 51 0 20 0.000 0 0 -1 0 0 5 - 3225 2550 4575 2550 4575 3000 3225 3000 3225 2550 -2 2 0 0 0 33 52 0 20 0.000 0 0 -1 0 0 5 - 3300 2625 4650 2625 4650 3075 3300 3075 3300 2625 -2 1 0 1 0 38 50 0 -1 0.000 0 0 -1 1 0 2 - 1 1 1.00 60.00 120.00 - 3900 1500 3900 1800 -2 1 0 1 0 38 50 0 -1 0.000 0 0 -1 1 0 2 - 1 1 1.00 60.00 120.00 - 3900 2250 3900 2550 -2 1 0 1 0 38 50 0 -1 0.000 0 0 -1 1 0 2 - 1 1 1.00 60.00 120.00 - 3900 3000 3900 3900 -2 1 0 1 0 38 50 0 -1 0.000 0 0 -1 1 0 2 - 1 1 1.00 60.00 120.00 - 4575 1950 5400 1350 -2 1 0 1 0 38 50 0 -1 0.000 0 0 -1 1 0 2 - 1 1 1.00 60.00 120.00 - 5400 2925 4650 2325 -2 2 0 1 0 34 55 0 20 0.000 0 0 -1 0 0 5 - 2850 750 4875 750 4875 3375 2850 3375 2850 750 -2 1 0 1 0 38 50 0 -1 0.000 0 0 -1 1 0 2 - 1 1 1.00 60.00 120.00 - 6075 1500 6075 1950 -2 3 0 1 0 35 55 0 20 0.000 0 0 -1 0 0 5 - 2850 3675 4875 3675 4875 6225 2850 6225 2850 3675 -2 2 0 1 0 37 55 0 20 0.000 0 0 -1 0 0 5 - 5175 750 7200 750 7200 4050 5175 4050 5175 750 -2 2 0 1 0 38 55 0 20 0.000 0 0 -1 0 0 5 - 5175 4425 7200 4425 7200 6225 5175 6225 5175 4425 -2 1 0 1 0 38 50 0 -1 0.000 0 0 -1 1 0 2 - 1 1 1.00 60.00 120.00 - 6075 2475 6075 2775 -4 1 0 50 0 2 12 0.0000 4 135 855 6075 1350 Tokenizer\001 -4 1 0 50 0 1 12 1.5708 4 180 1020 7125 2250 SQL Compiler\001 -4 1 0 50 0 1 12 1.5708 4 135 345 3075 2025 Core\001 -4 1 0 50 0 2 12 0.0000 4 135 1290 3900 2850 Virtual Machine\001 -4 1 0 50 0 2 12 0.0000 4 165 1185 3900 1995 SQL Command\001 -4 1 0 50 0 2 12 0.0000 4 135 855 3900 2183 Processor\001 -4 1 0 50 0 2 14 0.0000 4 150 870 3900 1350 Interface\001 -4 1 0 50 0 1 12 1.5708 4 135 885 7125 5400 Accessories\001 -4 1 0 50 0 1 12 1.5708 4 135 645 3075 4875 Backend\001 DELETED arch2.gif Index: arch2.gif ================================================================== --- arch2.gif +++ /dev/null cannot compute difference between binary files DELETED arch2b.fig Index: arch2b.fig ================================================================== --- arch2b.fig +++ /dev/null @@ -1,125 +0,0 @@ -#FIG 3.2 -Landscape -Center -Inches -Letter -100.00 -Single --2 -1200 2 -0 32 #000000 -0 33 #868686 -0 34 #dfefd7 -0 35 #d7efef -0 36 #efdbef -0 37 #efdbd7 -0 38 #e7efcf -0 39 #9e9e9e -6 3225 3900 4650 6000 -2 2 0 1 0 7 51 0 20 0.000 0 0 -1 0 0 5 - 3225 5475 4575 5475 4575 5925 3225 5925 3225 5475 -2 2 0 0 0 33 52 0 20 0.000 0 0 -1 0 0 5 - 3300 5550 4650 5550 4650 6000 3300 6000 3300 5550 -2 2 0 1 0 7 51 0 20 0.000 0 0 -1 0 0 5 - 3225 4650 4575 4650 4575 5100 3225 5100 3225 4650 -2 2 0 0 0 33 52 0 20 0.000 0 0 -1 0 0 5 - 3300 4725 4650 4725 4650 5175 3300 5175 3300 4725 -2 2 0 1 0 7 51 0 20 0.000 0 0 -1 0 0 5 - 3225 3900 4575 3900 4575 4350 3225 4350 3225 3900 -2 2 0 0 0 33 52 0 20 0.000 0 0 -1 0 0 5 - 3300 3975 4650 3975 4650 4425 3300 4425 3300 3975 -2 1 0 1 0 38 50 0 -1 0.000 0 0 -1 1 0 2 - 1 1 1.00 60.00 120.00 - 3900 4350 3900 4650 -2 1 0 1 0 38 50 0 -1 0.000 0 0 -1 1 0 2 - 1 1 1.00 60.00 120.00 - 3900 5100 3900 5475 -4 1 0 50 0 2 12 0.0000 4 135 1050 3900 5775 OS Interface\001 -4 1 0 50 0 2 12 0.0000 4 135 615 3900 4200 B-Tree\001 -4 1 0 50 0 2 12 0.0000 4 180 495 3900 4950 Pager\001 --6 -6 5175 4275 7200 6150 -6 5400 4519 6825 5090 -2 2 0 1 0 7 51 0 20 0.000 0 0 -1 0 0 5 - 5400 4519 6750 4519 6750 5009 5400 5009 5400 4519 -2 2 0 0 0 33 52 0 20 0.000 0 0 -1 0 0 5 - 5475 4601 6825 4601 6825 5090 5475 5090 5475 4601 -4 1 0 50 0 2 12 0.0000 4 135 630 6000 4845 Utilities\001 --6 -6 5400 5416 6825 5987 -2 2 0 1 0 7 51 0 20 0.000 0 0 -1 0 0 5 - 5400 5416 6750 5416 6750 5906 5400 5906 5400 5416 -2 2 0 0 0 33 52 0 20 0.000 0 0 -1 0 0 5 - 5475 5498 6825 5498 6825 5987 5475 5987 5475 5498 -4 1 0 50 0 2 12 0.0000 4 135 855 6000 5742 Test Code\001 --6 -2 2 0 1 0 38 55 0 20 0.000 0 0 -1 0 0 5 - 5175 4275 7200 4275 7200 6150 5175 6150 5175 4275 -4 1 0 50 0 1 12 1.5708 4 135 885 7125 5253 Accessories\001 --6 -6 5400 2700 6825 3675 -2 2 0 0 0 33 52 0 20 0.000 0 0 -1 0 0 5 - 5475 2775 6825 2775 6825 3675 5475 3675 5475 2775 -2 2 0 1 0 7 51 0 20 0.000 0 0 -1 0 0 5 - 5400 2700 6750 2700 6750 3600 5400 3600 5400 2700 -4 1 0 50 0 2 12 0.0000 4 135 420 6075 3075 Code\001 -4 1 0 50 0 2 12 0.0000 4 135 855 6075 3300 Generator\001 --6 -6 5400 1875 6825 2400 -2 2 0 1 0 7 51 0 20 0.000 0 0 -1 0 0 5 - 5400 1875 6750 1875 6750 2325 5400 2325 5400 1875 -2 2 0 0 0 33 52 0 20 0.000 0 0 -1 0 0 5 - 5475 1950 6825 1950 6825 2400 5475 2400 5475 1950 -4 1 0 50 0 2 12 0.0000 4 135 570 6075 2175 Parser\001 --6 -2 2 0 1 0 7 51 0 20 0.000 0 0 -1 0 0 5 - 5400 1050 6750 1050 6750 1500 5400 1500 5400 1050 -2 2 0 0 0 33 52 0 20 0.000 0 0 -1 0 0 5 - 5475 1125 6825 1125 6825 1575 5475 1575 5475 1125 -2 2 0 1 0 7 51 0 20 0.000 0 0 -1 0 0 5 - 3225 1050 4575 1050 4575 1500 3225 1500 3225 1050 -2 2 0 0 0 33 52 0 20 0.000 0 0 -1 0 0 5 - 3300 1125 4650 1125 4650 1575 3300 1575 3300 1125 -2 2 0 1 0 7 51 0 20 0.000 0 0 -1 0 0 5 - 3225 1800 4575 1800 4575 2250 3225 2250 3225 1800 -2 2 0 0 0 33 52 0 20 0.000 0 0 -1 0 0 5 - 3300 1875 4650 1875 4650 2325 3300 2325 3300 1875 -2 2 0 1 0 7 51 0 20 0.000 0 0 -1 0 0 5 - 3225 2550 4575 2550 4575 3000 3225 3000 3225 2550 -2 2 0 0 0 33 52 0 20 0.000 0 0 -1 0 0 5 - 3300 2625 4650 2625 4650 3075 3300 3075 3300 2625 -2 1 0 1 0 38 50 0 -1 0.000 0 0 -1 1 0 2 - 1 1 1.00 60.00 120.00 - 3900 1500 3900 1800 -2 1 0 1 0 38 50 0 -1 0.000 0 0 -1 1 0 2 - 1 1 1.00 60.00 120.00 - 3900 2250 3900 2550 -2 1 0 1 0 38 50 0 -1 0.000 0 0 -1 1 0 2 - 1 1 1.00 60.00 120.00 - 3900 3000 3900 3900 -2 1 0 1 0 38 50 0 -1 0.000 0 0 -1 1 0 2 - 1 1 1.00 60.00 120.00 - 4575 1950 5400 1350 -2 1 0 1 0 38 50 0 -1 0.000 0 0 -1 1 0 2 - 1 1 1.00 60.00 120.00 - 5400 2925 4650 2175 -2 2 0 1 0 34 55 0 20 0.000 0 0 -1 0 0 5 - 2850 750 4875 750 4875 3375 2850 3375 2850 750 -2 1 0 1 0 38 50 0 -1 0.000 0 0 -1 1 0 2 - 1 1 1.00 60.00 120.00 - 6075 1500 6075 1800 -2 3 0 1 0 35 55 0 20 0.000 0 0 -1 0 0 5 - 2850 3675 4875 3675 4875 6150 2850 6150 2850 3675 -2 2 0 1 0 37 55 0 20 0.000 0 0 -1 0 0 5 - 5175 750 7200 750 7200 3975 5175 3975 5175 750 -2 1 0 1 0 38 50 0 -1 0.000 0 0 -1 1 0 2 - 1 1 1.00 60.00 120.00 - 6075 2400 6075 2700 -4 1 0 50 0 2 12 0.0000 4 135 855 6075 1350 Tokenizer\001 -4 1 0 50 0 1 12 1.5708 4 180 1020 7125 2250 SQL Compiler\001 -4 1 0 50 0 1 12 1.5708 4 135 345 3075 2025 Core\001 -4 1 0 50 0 2 12 0.0000 4 135 1290 3900 2850 Virtual Machine\001 -4 1 0 50 0 2 12 0.0000 4 165 1185 3900 1995 SQL Command\001 -4 1 0 50 0 2 12 0.0000 4 135 855 3900 2183 Processor\001 -4 1 0 50 0 2 14 0.0000 4 150 870 3900 1350 Interface\001 -4 1 0 50 0 1 12 1.5708 4 135 645 3075 4875 Backend\001 ADDED art/arch.fig Index: art/arch.fig ================================================================== --- /dev/null +++ art/arch.fig @@ -0,0 +1,64 @@ +#FIG 3.2 +Portrait +Center +Inches +Letter +100.00 +Single +-2 +1200 2 +2 1 0 3 0 7 100 0 -1 0.000 0 0 -1 1 0 2 + 1 1 3.00 75.00 135.00 + 3675 8550 3675 9075 +2 1 0 3 0 7 100 0 -1 0.000 0 0 -1 1 0 2 + 1 1 3.00 75.00 135.00 + 3675 7200 3675 7725 +2 1 0 3 0 7 100 0 -1 0.000 0 0 -1 1 0 2 + 1 1 3.00 75.00 135.00 + 3675 5775 3675 6300 +2 1 0 3 0 7 100 0 -1 0.000 0 0 -1 1 0 2 + 1 1 3.00 75.00 135.00 + 3675 3975 3675 4500 +2 1 0 3 0 7 100 0 -1 0.000 0 0 -1 1 0 2 + 1 1 3.00 75.00 135.00 + 3675 2625 3675 3150 +2 1 0 3 0 7 100 0 -1 0.000 0 0 -1 1 0 2 + 1 1 3.00 75.00 135.00 + 3675 1275 3675 1800 +2 1 0 3 0 7 100 0 -1 0.000 0 0 -1 1 0 2 + 1 1 3.00 75.00 135.00 + 3675 9900 3675 10425 +2 2 0 1 0 11 100 0 20 0.000 0 0 7 0 0 5 + 2550 10425 4875 10425 4875 11250 2550 11250 2550 10425 +2 2 0 1 0 11 100 0 20 0.000 0 0 7 0 0 5 + 2550 9075 4875 9075 4875 9900 2550 9900 2550 9075 +2 2 0 1 0 11 100 0 20 0.000 0 0 7 0 0 5 + 2550 7725 4875 7725 4875 8550 2550 8550 2550 7725 +2 2 0 1 0 11 100 0 20 0.000 0 0 7 0 0 5 + 2550 6300 4875 6300 4875 7200 2550 7200 2550 6300 +2 2 0 1 0 11 100 0 20 0.000 0 0 7 0 0 5 + 2550 4500 4875 4500 4875 5775 2550 5775 2550 4500 +2 2 0 1 0 11 100 0 20 0.000 0 0 7 0 0 5 + 2550 3150 4875 3150 4875 3975 2550 3975 2550 3150 +2 2 0 1 0 11 100 0 20 0.000 0 0 7 0 0 5 + 2550 1800 4875 1800 4875 2625 2550 2625 2550 1800 +2 2 0 1 0 11 100 0 20 0.000 0 0 7 0 0 5 + 2550 450 4875 450 4875 1275 2550 1275 2550 450 +4 1 0 100 0 0 20 0.0000 4 195 1020 3675 750 Interface\001 +4 1 0 100 0 0 14 0.0000 4 195 2040 3675 1125 main.c table.c tclsqlite.c\001 +4 1 0 100 0 0 20 0.0000 4 195 1920 3675 6675 Virtual Machine\001 +4 1 0 100 0 0 14 0.0000 4 150 570 3675 7050 vdbe.c\001 +4 1 0 100 0 0 20 0.0000 4 195 1830 3675 4875 Code Generator\001 +4 1 0 100 0 0 14 0.0000 4 195 1860 3675 5175 build.c delete.c expr.c\001 +4 1 0 100 0 0 14 0.0000 4 195 2115 3675 5400 insert.c select.c update.c\001 +4 1 0 100 0 0 14 0.0000 4 150 705 3675 5625 where.c\001 +4 1 0 100 0 0 20 0.0000 4 195 735 3675 3450 Parser\001 +4 1 0 100 0 0 20 0.0000 4 195 1140 3675 2100 Tokenizer\001 +4 1 0 100 0 0 14 0.0000 4 150 870 3675 2475 tokenize.c\001 +4 1 0 100 0 0 20 0.0000 4 255 1350 3675 9375 Page Cache\001 +4 1 0 100 0 0 14 0.0000 4 150 630 3675 3825 parse.y\001 +4 1 0 100 0 0 14 0.0000 4 150 600 3675 8400 btree.c\001 +4 1 0 100 0 0 14 0.0000 4 150 645 3675 9750 pager.c\001 +4 1 0 100 0 0 20 0.0000 4 195 1620 3675 8025 B-tree Driver\001 +4 1 0 100 0 0 14 0.0000 4 105 345 3675 11100 os.c\001 +4 1 0 100 0 0 20 0.0000 4 195 1470 3675 10725 OS Interface\001 ADDED art/arch2.fig Index: art/arch2.fig ================================================================== --- /dev/null +++ art/arch2.fig @@ -0,0 +1,123 @@ +#FIG 3.2 +Landscape +Center +Inches +Letter +100.00 +Single +-2 +1200 2 +0 32 #000000 +0 33 #868686 +0 34 #dfefd7 +0 35 #d7efef +0 36 #efdbef +0 37 #efdbd7 +0 38 #e7efcf +0 39 #9e9e9e +6 3225 3900 4650 6000 +2 2 0 1 0 7 51 0 20 0.000 0 0 -1 0 0 5 + 3225 5475 4575 5475 4575 5925 3225 5925 3225 5475 +2 2 0 0 0 33 52 0 20 0.000 0 0 -1 0 0 5 + 3300 5550 4650 5550 4650 6000 3300 6000 3300 5550 +2 2 0 1 0 7 51 0 20 0.000 0 0 -1 0 0 5 + 3225 4650 4575 4650 4575 5100 3225 5100 3225 4650 +2 2 0 0 0 33 52 0 20 0.000 0 0 -1 0 0 5 + 3300 4725 4650 4725 4650 5175 3300 5175 3300 4725 +2 2 0 1 0 7 51 0 20 0.000 0 0 -1 0 0 5 + 3225 3900 4575 3900 4575 4350 3225 4350 3225 3900 +2 2 0 0 0 33 52 0 20 0.000 0 0 -1 0 0 5 + 3300 3975 4650 3975 4650 4425 3300 4425 3300 3975 +2 1 0 1 0 38 50 0 -1 0.000 0 0 -1 1 0 2 + 1 1 1.00 60.00 120.00 + 3900 4350 3900 4650 +2 1 0 1 0 38 50 0 -1 0.000 0 0 -1 1 0 2 + 1 1 1.00 60.00 120.00 + 3900 5100 3900 5475 +4 1 0 50 0 2 12 0.0000 4 135 1050 3900 5775 OS Interface\001 +4 1 0 50 0 2 12 0.0000 4 135 615 3900 4200 B-Tree\001 +4 1 0 50 0 2 12 0.0000 4 180 495 3900 4950 Pager\001 +-6 +6 5400 4725 6825 5250 +2 2 0 1 0 7 51 0 20 0.000 0 0 -1 0 0 5 + 5400 4725 6750 4725 6750 5175 5400 5175 5400 4725 +2 2 0 0 0 33 52 0 20 0.000 0 0 -1 0 0 5 + 5475 4800 6825 4800 6825 5250 5475 5250 5475 4800 +4 1 0 50 0 2 12 0.0000 4 135 630 6000 5025 Utilities\001 +-6 +6 5400 5550 6825 6075 +2 2 0 1 0 7 51 0 20 0.000 0 0 -1 0 0 5 + 5400 5550 6750 5550 6750 6000 5400 6000 5400 5550 +2 2 0 0 0 33 52 0 20 0.000 0 0 -1 0 0 5 + 5475 5625 6825 5625 6825 6075 5475 6075 5475 5625 +4 1 0 50 0 2 12 0.0000 4 135 855 6000 5850 Test Code\001 +-6 +6 5400 2775 6825 3750 +2 2 0 0 0 33 52 0 20 0.000 0 0 -1 0 0 5 + 5475 2850 6825 2850 6825 3750 5475 3750 5475 2850 +2 2 0 1 0 7 51 0 20 0.000 0 0 -1 0 0 5 + 5400 2775 6750 2775 6750 3675 5400 3675 5400 2775 +4 1 0 50 0 2 12 0.0000 4 135 420 6075 3150 Code\001 +4 1 0 50 0 2 12 0.0000 4 135 855 6075 3375 Generator\001 +-6 +6 5400 1950 6825 2475 +2 2 0 1 0 7 51 0 20 0.000 0 0 -1 0 0 5 + 5400 1950 6750 1950 6750 2400 5400 2400 5400 1950 +2 2 0 0 0 33 52 0 20 0.000 0 0 -1 0 0 5 + 5475 2025 6825 2025 6825 2475 5475 2475 5475 2025 +4 1 0 50 0 2 12 0.0000 4 135 570 6075 2250 Parser\001 +-6 +2 2 0 1 0 7 51 0 20 0.000 0 0 -1 0 0 5 + 5400 1050 6750 1050 6750 1500 5400 1500 5400 1050 +2 2 0 0 0 33 52 0 20 0.000 0 0 -1 0 0 5 + 5475 1125 6825 1125 6825 1575 5475 1575 5475 1125 +2 2 0 1 0 7 51 0 20 0.000 0 0 -1 0 0 5 + 3225 1050 4575 1050 4575 1500 3225 1500 3225 1050 +2 2 0 0 0 33 52 0 20 0.000 0 0 -1 0 0 5 + 3300 1125 4650 1125 4650 1575 3300 1575 3300 1125 +2 2 0 1 0 7 51 0 20 0.000 0 0 -1 0 0 5 + 3225 1800 4575 1800 4575 2250 3225 2250 3225 1800 +2 2 0 0 0 33 52 0 20 0.000 0 0 -1 0 0 5 + 3300 1875 4650 1875 4650 2325 3300 2325 3300 1875 +2 2 0 1 0 7 51 0 20 0.000 0 0 -1 0 0 5 + 3225 2550 4575 2550 4575 3000 3225 3000 3225 2550 +2 2 0 0 0 33 52 0 20 0.000 0 0 -1 0 0 5 + 3300 2625 4650 2625 4650 3075 3300 3075 3300 2625 +2 1 0 1 0 38 50 0 -1 0.000 0 0 -1 1 0 2 + 1 1 1.00 60.00 120.00 + 3900 1500 3900 1800 +2 1 0 1 0 38 50 0 -1 0.000 0 0 -1 1 0 2 + 1 1 1.00 60.00 120.00 + 3900 2250 3900 2550 +2 1 0 1 0 38 50 0 -1 0.000 0 0 -1 1 0 2 + 1 1 1.00 60.00 120.00 + 3900 3000 3900 3900 +2 1 0 1 0 38 50 0 -1 0.000 0 0 -1 1 0 2 + 1 1 1.00 60.00 120.00 + 4575 1950 5400 1350 +2 1 0 1 0 38 50 0 -1 0.000 0 0 -1 1 0 2 + 1 1 1.00 60.00 120.00 + 5400 2925 4650 2325 +2 2 0 1 0 34 55 0 20 0.000 0 0 -1 0 0 5 + 2850 750 4875 750 4875 3375 2850 3375 2850 750 +2 1 0 1 0 38 50 0 -1 0.000 0 0 -1 1 0 2 + 1 1 1.00 60.00 120.00 + 6075 1500 6075 1950 +2 3 0 1 0 35 55 0 20 0.000 0 0 -1 0 0 5 + 2850 3675 4875 3675 4875 6225 2850 6225 2850 3675 +2 2 0 1 0 37 55 0 20 0.000 0 0 -1 0 0 5 + 5175 750 7200 750 7200 4050 5175 4050 5175 750 +2 2 0 1 0 38 55 0 20 0.000 0 0 -1 0 0 5 + 5175 4425 7200 4425 7200 6225 5175 6225 5175 4425 +2 1 0 1 0 38 50 0 -1 0.000 0 0 -1 1 0 2 + 1 1 1.00 60.00 120.00 + 6075 2475 6075 2775 +4 1 0 50 0 2 12 0.0000 4 135 855 6075 1350 Tokenizer\001 +4 1 0 50 0 1 12 1.5708 4 180 1020 7125 2250 SQL Compiler\001 +4 1 0 50 0 1 12 1.5708 4 135 345 3075 2025 Core\001 +4 1 0 50 0 2 12 0.0000 4 135 1290 3900 2850 Virtual Machine\001 +4 1 0 50 0 2 12 0.0000 4 165 1185 3900 1995 SQL Command\001 +4 1 0 50 0 2 12 0.0000 4 135 855 3900 2183 Processor\001 +4 1 0 50 0 2 14 0.0000 4 150 870 3900 1350 Interface\001 +4 1 0 50 0 1 12 1.5708 4 135 885 7125 5400 Accessories\001 +4 1 0 50 0 1 12 1.5708 4 135 645 3075 4875 Backend\001 ADDED art/arch2b.fig Index: art/arch2b.fig ================================================================== --- /dev/null +++ art/arch2b.fig @@ -0,0 +1,125 @@ +#FIG 3.2 +Landscape +Center +Inches +Letter +100.00 +Single +-2 +1200 2 +0 32 #000000 +0 33 #868686 +0 34 #dfefd7 +0 35 #d7efef +0 36 #efdbef +0 37 #efdbd7 +0 38 #e7efcf +0 39 #9e9e9e +6 3225 3900 4650 6000 +2 2 0 1 0 7 51 0 20 0.000 0 0 -1 0 0 5 + 3225 5475 4575 5475 4575 5925 3225 5925 3225 5475 +2 2 0 0 0 33 52 0 20 0.000 0 0 -1 0 0 5 + 3300 5550 4650 5550 4650 6000 3300 6000 3300 5550 +2 2 0 1 0 7 51 0 20 0.000 0 0 -1 0 0 5 + 3225 4650 4575 4650 4575 5100 3225 5100 3225 4650 +2 2 0 0 0 33 52 0 20 0.000 0 0 -1 0 0 5 + 3300 4725 4650 4725 4650 5175 3300 5175 3300 4725 +2 2 0 1 0 7 51 0 20 0.000 0 0 -1 0 0 5 + 3225 3900 4575 3900 4575 4350 3225 4350 3225 3900 +2 2 0 0 0 33 52 0 20 0.000 0 0 -1 0 0 5 + 3300 3975 4650 3975 4650 4425 3300 4425 3300 3975 +2 1 0 1 0 38 50 0 -1 0.000 0 0 -1 1 0 2 + 1 1 1.00 60.00 120.00 + 3900 4350 3900 4650 +2 1 0 1 0 38 50 0 -1 0.000 0 0 -1 1 0 2 + 1 1 1.00 60.00 120.00 + 3900 5100 3900 5475 +4 1 0 50 0 2 12 0.0000 4 135 1050 3900 5775 OS Interface\001 +4 1 0 50 0 2 12 0.0000 4 135 615 3900 4200 B-Tree\001 +4 1 0 50 0 2 12 0.0000 4 180 495 3900 4950 Pager\001 +-6 +6 5175 4275 7200 6150 +6 5400 4519 6825 5090 +2 2 0 1 0 7 51 0 20 0.000 0 0 -1 0 0 5 + 5400 4519 6750 4519 6750 5009 5400 5009 5400 4519 +2 2 0 0 0 33 52 0 20 0.000 0 0 -1 0 0 5 + 5475 4601 6825 4601 6825 5090 5475 5090 5475 4601 +4 1 0 50 0 2 12 0.0000 4 135 630 6000 4845 Utilities\001 +-6 +6 5400 5416 6825 5987 +2 2 0 1 0 7 51 0 20 0.000 0 0 -1 0 0 5 + 5400 5416 6750 5416 6750 5906 5400 5906 5400 5416 +2 2 0 0 0 33 52 0 20 0.000 0 0 -1 0 0 5 + 5475 5498 6825 5498 6825 5987 5475 5987 5475 5498 +4 1 0 50 0 2 12 0.0000 4 135 855 6000 5742 Test Code\001 +-6 +2 2 0 1 0 38 55 0 20 0.000 0 0 -1 0 0 5 + 5175 4275 7200 4275 7200 6150 5175 6150 5175 4275 +4 1 0 50 0 1 12 1.5708 4 135 885 7125 5253 Accessories\001 +-6 +6 5400 2700 6825 3675 +2 2 0 0 0 33 52 0 20 0.000 0 0 -1 0 0 5 + 5475 2775 6825 2775 6825 3675 5475 3675 5475 2775 +2 2 0 1 0 7 51 0 20 0.000 0 0 -1 0 0 5 + 5400 2700 6750 2700 6750 3600 5400 3600 5400 2700 +4 1 0 50 0 2 12 0.0000 4 135 420 6075 3075 Code\001 +4 1 0 50 0 2 12 0.0000 4 135 855 6075 3300 Generator\001 +-6 +6 5400 1875 6825 2400 +2 2 0 1 0 7 51 0 20 0.000 0 0 -1 0 0 5 + 5400 1875 6750 1875 6750 2325 5400 2325 5400 1875 +2 2 0 0 0 33 52 0 20 0.000 0 0 -1 0 0 5 + 5475 1950 6825 1950 6825 2400 5475 2400 5475 1950 +4 1 0 50 0 2 12 0.0000 4 135 570 6075 2175 Parser\001 +-6 +2 2 0 1 0 7 51 0 20 0.000 0 0 -1 0 0 5 + 5400 1050 6750 1050 6750 1500 5400 1500 5400 1050 +2 2 0 0 0 33 52 0 20 0.000 0 0 -1 0 0 5 + 5475 1125 6825 1125 6825 1575 5475 1575 5475 1125 +2 2 0 1 0 7 51 0 20 0.000 0 0 -1 0 0 5 + 3225 1050 4575 1050 4575 1500 3225 1500 3225 1050 +2 2 0 0 0 33 52 0 20 0.000 0 0 -1 0 0 5 + 3300 1125 4650 1125 4650 1575 3300 1575 3300 1125 +2 2 0 1 0 7 51 0 20 0.000 0 0 -1 0 0 5 + 3225 1800 4575 1800 4575 2250 3225 2250 3225 1800 +2 2 0 0 0 33 52 0 20 0.000 0 0 -1 0 0 5 + 3300 1875 4650 1875 4650 2325 3300 2325 3300 1875 +2 2 0 1 0 7 51 0 20 0.000 0 0 -1 0 0 5 + 3225 2550 4575 2550 4575 3000 3225 3000 3225 2550 +2 2 0 0 0 33 52 0 20 0.000 0 0 -1 0 0 5 + 3300 2625 4650 2625 4650 3075 3300 3075 3300 2625 +2 1 0 1 0 38 50 0 -1 0.000 0 0 -1 1 0 2 + 1 1 1.00 60.00 120.00 + 3900 1500 3900 1800 +2 1 0 1 0 38 50 0 -1 0.000 0 0 -1 1 0 2 + 1 1 1.00 60.00 120.00 + 3900 2250 3900 2550 +2 1 0 1 0 38 50 0 -1 0.000 0 0 -1 1 0 2 + 1 1 1.00 60.00 120.00 + 3900 3000 3900 3900 +2 1 0 1 0 38 50 0 -1 0.000 0 0 -1 1 0 2 + 1 1 1.00 60.00 120.00 + 4575 1950 5400 1350 +2 1 0 1 0 38 50 0 -1 0.000 0 0 -1 1 0 2 + 1 1 1.00 60.00 120.00 + 5400 2925 4650 2175 +2 2 0 1 0 34 55 0 20 0.000 0 0 -1 0 0 5 + 2850 750 4875 750 4875 3375 2850 3375 2850 750 +2 1 0 1 0 38 50 0 -1 0.000 0 0 -1 1 0 2 + 1 1 1.00 60.00 120.00 + 6075 1500 6075 1800 +2 3 0 1 0 35 55 0 20 0.000 0 0 -1 0 0 5 + 2850 3675 4875 3675 4875 6150 2850 6150 2850 3675 +2 2 0 1 0 37 55 0 20 0.000 0 0 -1 0 0 5 + 5175 750 7200 750 7200 3975 5175 3975 5175 750 +2 1 0 1 0 38 50 0 -1 0.000 0 0 -1 1 0 2 + 1 1 1.00 60.00 120.00 + 6075 2400 6075 2700 +4 1 0 50 0 2 12 0.0000 4 135 855 6075 1350 Tokenizer\001 +4 1 0 50 0 1 12 1.5708 4 180 1020 7125 2250 SQL Compiler\001 +4 1 0 50 0 1 12 1.5708 4 135 345 3075 2025 Core\001 +4 1 0 50 0 2 12 0.0000 4 135 1290 3900 2850 Virtual Machine\001 +4 1 0 50 0 2 12 0.0000 4 165 1185 3900 1995 SQL Command\001 +4 1 0 50 0 2 12 0.0000 4 135 855 3900 2183 Processor\001 +4 1 0 50 0 2 14 0.0000 4 150 870 3900 1350 Interface\001 +4 1 0 50 0 1 12 1.5708 4 135 645 3075 4875 Backend\001 DELETED audit.tcl Index: audit.tcl ================================================================== --- audit.tcl +++ /dev/null @@ -1,214 +0,0 @@ -# -# Run this Tcl script to generate the audit.html file. -# -set rcsid {$Id: audit.tcl,v 1.1 2002/07/13 16:52:35 drh Exp $} - -puts { - --(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. -
- --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: -
-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: -
-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. -
-Prove that SQLite exhibits the characteristics outlined below: -
- --The following are preconditions:
--CREATE TABLE t1(a CLOB); -
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: -
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. -
-Back to the SQLite Home Page
-
-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. -
- - --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 c_interface.tcl Index: c_interface.tcl ================================================================== --- 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 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.
- -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:
- -- --typedef struct sqlite sqlite; -#define SQLITE_OK 0 /* Successful result */ - -sqlite *sqlite_open(const char *dbname, int mode, char **errmsg); - -void sqlite_close(sqlite *db); - -int sqlite_exec( - sqlite *db, - char *sql, - int (*xCallback)(void*,int,char**,char**), - void *pArg, - char **errmsg -); -
-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. -
- - -Use the sqlite_open function to open an existing SQLite -database or to create a new SQLite database. The first argument -is the database name. The second argument is intended to signal -whether the database is going to be used for reading and writing -or just for reading. But in the current implementation, the -second argument to sqlite_open is ignored. -The third argument is a pointer to a string pointer. -If the third argument is not NULL and an error occurs -while trying to open the database, then an error message will be -written to memory obtained from malloc() and *errmsg will be made -to point to this error message. The calling function is responsible -for freeing the memory when it has finished with it.
- -The name of an SQLite database is the name of a file that will -contain the database. If the file does not exist, SQLite attempts -to create and initialize it. If the file is read-only (due to -permission bits or because it is located on read-only media like -a CD-ROM) then SQLite opens the database for reading only. The -entire SQL database is stored in a single file on the disk. But -additional temporary files may be created during the execution of -an SQL command in order to store the database rollback journal or -temporary and intermediate results of a query.
- -The return value of the sqlite_open function is a -pointer to an opaque sqlite structure. This pointer will -be the first argument to all subsequent SQLite function calls that -deal with the same database. NULL is returned if the open fails -for any reason.
- - -To close an SQLite database, call the sqlite_close -function passing it the sqlite structure pointer that was obtained -from a prior call to sqlite_open. -If a transaction is active when the database is closed, the transaction -is rolled back.
- - -The sqlite_exec function is used to process SQL statements -and queries. This function requires 5 parameters as follows:
- -A pointer to the sqlite structure obtained from a prior call - to sqlite_open.
A null-terminated string containing the text of one or more - SQL statements and/or queries to be processed.
A pointer to a callback function which is invoked once for each - row in the result of a query. This argument may be NULL, in which - case no callbacks will ever be invoked.
A pointer that is forwarded to become the first argument - to the callback function.
A pointer to an error string. Error messages are written to space - obtained from malloc() and the error string is made to point to - the malloced space. The calling function is responsible for freeing - this space when it has finished with it. - This argument may be NULL, in which case error messages are not - reported back to the calling function.
-The callback function is used to receive the results of a query. A -prototype for the callback function is as follows:
- -
-int Callback(void *pArg, int argc, char **argv, char **columnNames){
- return 0;
-}
-The first argument to the callback is just a copy of the fourth argument -to sqlite_exec This parameter can be used to pass arbitrary -information through to the callback function from client code. -The second argument is the number of columns in the query result. -The third argument is an array of pointers to strings where each string -is a single column of the result for that record. Note that the -callback function reports a NULL value in the database as a NULL pointer, -which is very different from an empty string. If the i-th parameter -is an empty string, we will get:
---argv[i][0] == 0 -
But if the i-th parameter is NULL we will get:
-- --argv[i] == 0 -
The names of the columns are contained in first argc -entries of the fourth argument. -If the SHOW_DATATYPES pragma -is on (it is off by default) then -the second argc entries in the 4th argument are the datatypes -for the corresponding columns. -
- -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 -
-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. - - --argv == 0 -
The callback function should normally return 0. If the callback -function returns non-zero, the query is immediately aborted and -sqlite_exec will return SQLITE_ABORT.
- --The sqlite_exec function normally returns SQLITE_OK. But -if something goes wrong it can return a different value to indicate -the type of error. Here is a complete list of the return 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 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 */ -
-The meanings of these various return values are as follows: -
- --- --
-- SQLITE_OK
-- -
This value is returned if everything worked and there were no errors. -
- SQLITE_INTERNAL
-- -
This value indicates that an internal consistency check within -the SQLite library failed. This can only happen if there is a bug in -the SQLite library. If you ever get an SQLITE_INTERNAL reply from -an sqlite_exec call, please report the problem on the SQLite -mailing list. -
- SQLITE_ERROR
-- -
This return value indicates that there was an error in the SQL -that was passed into the sqlite_exec. -
- SQLITE_PERM
-- -
This return value says that the access permissions on the database -file are such that the file cannot be opened. -
- SQLITE_ABORT
-- -
This value is returned if the callback function returns non-zero. -
- SQLITE_BUSY
-- -
This return code indicates that another program or thread has -the database locked. SQLite allows two or more threads to read the -database at the same time, but only one thread can have the database -open for writing at the same time. Locking in SQLite is on the -entire database.
-- SQLITE_LOCKED
-- -
This return code is similar to SQLITE_BUSY in that it indicates -that the database is locked. But the source of the lock is a recursive -call to sqlite_exec. This return can only occur if you attempt -to invoke sqlite_exec from within a callback routine of a query -from a prior invocation of sqlite_exec. Recursive calls to -sqlite_exec are allowed as long as they do -not attempt to write the same table. -
- SQLITE_NOMEM
-- -
This value is returned if a call to malloc fails. -
- SQLITE_READONLY
-- -
This return code indicates that an attempt was made to write to -a database file that is opened for reading only. -
- SQLITE_INTERRUPT
-- -
This value is returned if a call to sqlite_interrupt -interrupts a database operation in progress. -
- SQLITE_IOERR
-- -
This value is returned if the operating system informs SQLite -that it is unable to perform some disk I/O operation. This could mean -that there is no more space left on the disk. -
- SQLITE_CORRUPT
-- -
This value is returned if SQLite detects that the database it is -working on has become corrupted. Corruption might occur due to a rogue -process writing to the database file or it might happen due to an -perviously undetected logic error in of SQLite. This value is also -returned if a disk I/O error occurs in such a way that SQLite is forced -to leave the database file in a corrupted state. The latter should only -happen due to a hardware or operating system malfunction. -
- SQLITE_FULL
-- -
This value is returned if an insertion failed because there is -no space left on the disk, or the database is too big to hold any -more information. The latter case should only occur for databases -that are larger than 2GB in size. -
- SQLITE_CANTOPEN
-- -
This value is returned if the database file could not be opened -for some reason. -
- SQLITE_PROTOCOL
-- -
This value is returned if some other process is messing with -file locks and has violated the file locking protocol that SQLite uses -on its rollback journal files. -
- SQLITE_SCHEMA
-- -
When the database first opened, SQLite reads the database schema -into memory and uses that schema to parse new SQL statements. If another -process changes the schema, the command currently being processed will -abort because the virtual machine code generated assumed the old -schema. This is the return code for such cases. Retrying the -command usually will clear the problem. -
- SQLITE_TOOBIG
-- -
SQLite will not store more than about 1 megabyte of data in a single -row of a single table. If you attempt to store more than 1 megabyte -in a single row, this is the return code you get. -
- SQLITE_CONSTRAINT
-- -
This constant is returned if the SQL statement would have violated -a database constraint. -
- SQLITE_MISMATCH
-- -
This error occurs when there is an attempt to insert non-integer -data into a column labeled INTEGER PRIMARY KEY. For most columns, SQLite -ignores the data type and allows any kind of data to be stored. But -an INTEGER PRIMARY KEY column is only allowed to store integer data. -
- SQLITE_MISUSE
-- -
This error might occur if one or more of the SQLite API routines -is used incorrectly. Examples of incorrect usage include calling -sqlite_exec after the database has been closed using -sqlite_close or -calling sqlite_exec with the same -database pointer simultaneously from two separate threads. -
- SQLITE_NOLFS
-- -
This error means that you have attempts to create or access a file -database file that is larger that 2GB on a legacy Unix machine that -lacks large file support. -
- SQLITE_AUTH
-- -
This error indicates that the authorizer callback -has disallowed the SQL you are attempting to execute. -
- SQLITE_ROW
-- -
This is one of the return codes from the -sqlite_step routine which is part of the non-callback API. -It indicates that another row of result data is available. -
- SQLITE_DONE
-- -
This is one of the return codes from the -sqlite_step routine which is part of the non-callback API. -It indicates that the SQL statement has been completely executed and -the sqlite_finalize routine is ready to be called. -
-The sqlite_exec routine described above used to be the only -way to retrieve data from an SQLite database. But many programmers found -it inconvenient to use a callback function to obtain results. So beginning -with SQLite version 2.7.7, a second access interface is available that -does not use callbacks. -
- --The new interface uses three separate functions to replace the single -sqlite_exec function. -
- -- --typedef struct sqlite_vm sqlite_vm; - -int sqlite_compile( - sqlite *db, /* The open database */ - const char *zSql, /* SQL statement to be compiled */ - const char **pzTail, /* OUT: uncompiled tail of zSql */ - sqlite_vm **ppVm, /* OUT: the virtual machine to execute zSql */ - char **pzErrmsg /* OUT: Error message. */ -); - -int sqlite_step( - sqlite_vm *pVm, /* The virtual machine to execute */ - int *pN, /* OUT: Number of columns in result */ - const char ***pazValue, /* OUT: Column data */ - const char ***pazColName /* OUT: Column names and datatypes */ -); - -int sqlite_finalize( - sqlite_vm *pVm, /* The virtual machine to be finalized */ - char **pzErrMsg /* OUT: Error message */ -); -
-The strategy is to compile a single SQL statement using -sqlite_compile then invoke sqlite_step multiple times, -once for each row of output, and finally call sqlite_finalize -to clean up after the SQL has finished execution. -
- --The sqlite_compile "compiles" a single SQL statement (specified -by the second parameter) and generates a virtual machine that is able -to execute that statement. -As with must interface routines, the first parameter must be a pointer -to an sqlite structure that was obtained from a prior call to -sqlite_open. - -
-A pointer to the virtual machine is stored in a pointer which is passed -in as the 4th parameter. -Space to hold the virtual machine is dynamically allocated. To avoid -a memory leak, the calling function must invoke -sqlite_finalize on the virtual machine after it has finished -with it. -The 4th parameter may be set to NULL if an error is encountered during -compilation. -
- --If any errors are encountered during compilation, an error message is -written into memory obtained from malloc and the 5th parameter -is made to point to that memory. If the 5th parameter is NULL, then -no error message is generated. If the 5th parameter is not NULL, then -the calling function should dispose of the memory containing the error -message by calling sqlite_freemem. -
- --If the 2nd parameter actually contains two or more statements of SQL, -only the first statement is compiled. (This is different from the -behavior of sqlite_exec which executes all SQL statements -in its input string.) The 3rd parameter to sqlite_compile -is made to point to the first character beyond the end of the first -statement of SQL in the input. If the 2nd parameter contains only -a single SQL statement, then the 3rd parameter will be made to point -to the '\000' terminator at the end of the 2nd parameter. -
- --On success, sqlite_compile returns SQLITE_OK. -Otherwise and error code is returned. -
- --After a virtual machine has been generated using sqlite_compile -it is executed by one or more calls to sqlite_step. Each -invocation of sqlite_step, except the last one, -returns a single row of the result. -The number of columns in the result is stored in the integer that -the 2nd parameter points to. -The pointer specified by the 3rd parameter is made to point -to an array of pointers to column values. -The pointer in the 4th parameter is made to point to an array -of pointers to column names and datatypes. -The 2nd through 4th parameters to sqlite_step convey the -same information as the 2nd through 4th parameters of the -callback routine when using -the sqlite_exec interface. Except, with sqlite_step -the column datatype information is always included in the in the -4th parameter regardless of whether or not the -SHOW_DATATYPES pragma -is on or off. -
- --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. -
- --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. -
- -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: -
- -- --int sqlite_last_insert_rowid(sqlite*); - -int sqlite_changes(sqlite*); - -int sqlite_get_table( - sqlite*, - char *sql, - char ***result, - int *nrow, - int *ncolumn, - char **errmsg -); - -void sqlite_free_table(char**); - -void sqlite_interrupt(sqlite*); - -int sqlite_complete(const char *sql); - -void sqlite_busy_handler(sqlite*, int (*)(void*,const char*,int), void*); - -void sqlite_busy_timeout(sqlite*, int ms); - -const char sqlite_version[]; - -const char sqlite_encoding[]; - -int sqlite_exec_printf( - sqlite*, - char *sql, - int (*)(void*,int,char**,char**), - void*, - char **errmsg, - ... -); - -int sqlite_exec_vprintf( - sqlite*, - char *sql, - int (*)(void*,int,char**,char**), - void*, - char **errmsg, - va_list -); - -int sqlite_get_table_printf( - sqlite*, - char *sql, - char ***result, - int *nrow, - int *ncolumn, - char **errmsg, - ... -); - -int sqlite_get_table_vprintf( - sqlite*, - char *sql, - char ***result, - int *nrow, - int *ncolumn, - char **errmsg, - va_list -); - -char *sqlite_mprintf(const char *zFormat, ...); - -char *sqlite_vmprintf(const char *zFormat, va_list); - -void sqlite_freemem(char*); - -void sqlite_progress_handler(sqlite*, int, int (*)(void*), void*); - -
All of the above definitions are included in the "sqlite.h" -header file that comes in the source tree.
- -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.
- -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.
- -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:
- --nrow = 2- -
-ncolumn = 3
-result[0] = "employee_name"
-result[1] = "login"
-result[2] = "host"
-result[3] = "dummy"
-result[4] = "No such user"
-result[5] = 0
-result[6] = "D. Richard Hipp"
-result[7] = "drh"
-result[8] = "zadok" -
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: -
- --nrow = 0- -
-ncolumn = 3
-result[0] = "employee_name"
-result[1] = "login"
-result[2] = "host"
-
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.
- -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.
- -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.
- -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.
- -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.
- -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.
- -The four utility functions
- --
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:
- -
-sqlite_exec_printf(db,
- "INSERT INTO table1 VALUES('%s')",
- 0, 0, 0, zString);
-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:
- -
-sqlite_exec_printf(db,
- "INSERT INTO table1 VALUES('%q')",
- 0, 0, 0, zString);
-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:
- -- --sqlite_exec_printf(db, - "INSERT INTO table1 VALUES(%Q)", - 0, 0, 0, zString); -
Then the generated SQL will look like the following:
- -
-INSERT INTO table1 VALUES('Hi y''all')
-If the value of the zString variable is NULL, the generated SQL -will look like the following:
- -- --INSERT INTO table1 VALUES(NULL) -
All of the _printf() routines above are built around the following -two functions:
- -- --char *sqlite_mprintf(const char *zFormat, ...); -char *sqlite_vmprintf(const char *zFormat, va_list); -
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(). -
- -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. -
- - -Beginning with version 2.4.0, SQLite allows the SQL language to be -extended with new functions implemented as C code. The following interface -is used: -
- -- --typedef struct sqlite_func sqlite_func; - -int sqlite_create_function( - sqlite *db, - const char *zName, - int nArg, - void (*xFunc)(sqlite_func*,int,const char**), - void *pUserData -); -int sqlite_create_aggregate( - sqlite *db, - const char *zName, - int nArg, - void (*xStep)(sqlite_func*,int,const char**), - void (*xFinalize)(sqlite_func*), - void *pUserData -); - -char *sqlite_set_result_string(sqlite_func*,const char*,int); -void sqlite_set_result_int(sqlite_func*,int); -void sqlite_set_result_double(sqlite_func*,double); -void sqlite_set_result_error(sqlite_func*,const char*,int); - -void *sqlite_user_data(sqlite_func*); -void *sqlite_aggregate_context(sqlite_func*, int nBytes); -int sqlite_aggregate_count(sqlite_func*); -
-The sqlite_create_function() interface is used to create -regular functions and sqlite_create_aggregate() is used to -create new aggregate functions. In both cases, the db -parameter is an open SQLite database on which the functions should -be registered, zName is the name of the new function, -nArg is the number of arguments, and pUserData is -a pointer which is passed through unchanged to the C implementation -of the function. Both routines return 0 on success and non-zero -if there are any errors. -
- --The length of a function name may not exceed 255 characters. -Any attempt to create a function whose name exceeds 255 characters -in length will result in an error. -
- --For regular functions, the xFunc callback is invoked once -for each function call. The implementation of xFunc should call -one of the sqlite_set_result_... interfaces to return its -result. The sqlite_user_data() routine can be used to -retrieve the pUserData pointer that was passed in when the -function was registered. -
- --For aggregate functions, the xStep callback is invoked once -for each row in the result and then xFinalize is invoked at the -end to compute a final answer. The xStep routine can use the -sqlite_aggregate_context() interface to allocate memory that -will be unique to that particular instance of the SQL function. -This memory will be automatically deleted after xFinalize is called. -The sqlite_aggregate_count() routine can be used to find out -how many rows of data were passed to the aggregate. The xFinalize -callback should invoke one of the sqlite_set_result_... -interfaces to set the final result of the aggregate. -
- --SQLite now implements all of its built-in functions using this -interface. For additional information and examples on how to create -new SQL functions, review the SQLite source code in the file -func.c. -
- --If SQLite is compiled with the THREADSAFE preprocessor macro set to 1, -then it is safe to use SQLite from two or more threads of the same process -at the same time. But each thread should have its own sqlite* -pointer returned from sqlite_open. It is never safe for two -or more threads to access the same sqlite* pointer at the same time. -
- --In precompiled SQLite libraries available on the website, the Unix -versions are compiled with THREADSAFE turned off but the windows -versions are compiled with THREADSAFE turned on. If you need something -different that this you will have to recompile. -
- --Under Unix, an sqlite* pointer should not be carried across a -fork() system call into the child process. The child process -should open its own copy of the database after the fork(). -
- -For examples of how the SQLite C/C++ interface can be used, -refer to the source code for the sqlite program in the -file src/shell.c of the source tree. -Additional information about sqlite is available at -sqlite.html. -See also the sources to the Tcl interface for SQLite in -the source file src/tclsqlite.c.
-} -footer $rcsid DELETED capi3.tcl Index: capi3.tcl ================================================================== --- capi3.tcl +++ /dev/null @@ -1,516 +0,0 @@ -set rcsid {$Id: capi3.tcl,v 1.10 2007/04/27 17:16:22 drh Exp $} -source common.tcl -header {C/C++ Interface For SQLite Version 3} - -proc AddHyperlinks {txt} { - regsub -all {([^:alnum:>])(sqlite3_\w+)(\([^\)]*\))} $txt \ - {\1\2\3} t2 - puts $t2 -} - -AddHyperlinks { --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: -
- --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. -
- --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. -
- - -- -- 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 */ -
- -- 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: -
- -- -- #define SQLITE_INTEGER 1 - #define SQLITE_FLOAT 2 - #define SQLITE_TEXT 3 - #define SQLITE_BLOB 4 - #define SQLITE_NULL 5 -
-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 -
-User defined functions can be created using the following routine: -
- -- -- typedef struct sqlite3_value sqlite3_value; - int sqlite3_create_function( - sqlite3 *, - const char *zFunctionName, - int nArg, - int eTextRep, - void*, - 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*, - 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 -
-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*)); -
-The following routines are used to implement user-defined -collating sequences: -
- -- -- sqlite3_create_collation(sqlite3*, const char *zName, int eTextRep, void*, - int(*xCompare)(void*,int,const void*,int,const void*)); - sqlite3_create_collation16(sqlite3*, const void *zName, int eTextRep, void*, - int(*xCompare)(void*,int,const void*,int,const void*)); - sqlite3_collation_needed(sqlite3*, void*, - void(*)(void*,sqlite3*,int eTextRep,const char*)); - sqlite3_collation_needed16(sqlite3*, void*, - void(*)(void*,sqlite3*,int eTextRep,const void*)); -
-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. -
-} -footer $rcsid DELETED changes.tcl Index: changes.tcl ================================================================== --- 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. -
- --
- |
-- | - - | -- | - - | -- |
-
|
-
| " - 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] "$body |
-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. -
- -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.
-
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 "$
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 conflict.tcl Index: conflict.tcl ================================================================== --- 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 { --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. -
- --SQLite defines five constraint conflict resolution algorithms -as follows: -
- -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.
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.
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.
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.
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.
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.
- -} -footer $rcsid DELETED copyright-release.html Index: copyright-release.html ================================================================== --- copyright-release.html +++ /dev/null @@ -1,109 +0,0 @@ - - --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. -
-
|
-Signature:
- - - - | -Date: - | -Name (printed): - | - -
- -SQLite is in the -Public Domain - |
-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. -
- --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: -
- --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 -
-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. -
-} -footer $rcsid DELETED datatype3.tcl Index: datatype3.tcl ================================================================== --- datatype3.tcl +++ /dev/null @@ -1,440 +0,0 @@ -set rcsid {$Id: datatype3.tcl,v 1.17 2007/06/20 16:13:23 drh Exp $} -source common.tcl -header {Datatypes In SQLite Version 3} -puts { -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 SQLite version 3, the type of a value is associated with the value -itself, not with the column or variable in which the value is stored. -(This is sometimes called - -manifest typing.) -All other SQL databases engines that we are aware of use the more -restrictive system of static typing where the type is associated with -the container, not the value. -
- --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:
-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.
- -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.
- -
-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);
-
-
-
-
-Like SQLite version 2, version 3 -features the binary comparison operators '=', -'<', '<=', '>=' and '!=', an operation to test for set -membership, 'IN', and the ternary comparison operator 'BETWEEN'.
-The results of a comparison depend on the storage classes of the -two values being compared, according to the following rules:
-A value with storage class NULL is considered less than any - other value (including another value with storage class NULL).
- -An INTEGER or REAL value is less than any TEXT or BLOB value. - When an INTEGER or REAL is compared to another INTEGER or REAL, a - numerical comparison is performed.
- -A TEXT value is less than a BLOB value. When two TEXT values - are compared, the C library function memcmp() is usually used to - determine the result. However this can be overridden, as described - under 'User-defined collation Sequences' below.
- -When two BLOB values are compared, the result is always - determined using memcmp().
-SQLite may attempt to convert values between the numeric storage -classes (INTEGER and REAL) and TEXT before performing a comparison. -For binary comparisons, this is done in the cases enumerated below. -The term "expression" used in the bullet points below means any -SQL scalar expression or literal other than a column value. Note that -if X and Y.Z are a column names, then +X and +Y.Z are considered -expressions.
-When a column value is compared to the result of an - expression, the affinity of the column is applied to the result of - the expression before the comparison takes place.
- -When two column values are compared, if one column has - INTEGER or REAL or NUMERIC affinity and the other does not, - then NUMERIC affinity is applied to any values with storage - class TEXT extracted from the non-NUMERIC column.
- -When the results of two expressions are compared, no - conversions occur. The results are compared as is. If a string - is compared to a number, the number will always be less than the - string.
--In SQLite, the expression "a BETWEEN b AND c" is equivalent to "a >= b -AND a <= c", even if this means that different affinities are applied to -'a' in each of the comparisons required to evaluate the expression. -
- -Expressions of the type "a IN (SELECT b ....)" are handled by the three -rules enumerated above for binary comparisons (e.g. in a -similar manner to "a = b"). For example if 'b' is a column value -and 'a' is an expression, then the affinity of 'b' is applied to 'a' -before any comparisons take place.
- -SQLite treats the expression "a IN (x, y, z)" as equivalent to "a = +x OR -a = +y OR a = +z". The values to the right of the IN operator (the "x", "y", -and "z" values in this example) are considered to be expressions, even if they -happen to be column values. If the value of the left of the IN operator is -a column, then the affinity of that column is used. If the value is an -expression then no conversions occur. -
- -
-
-CREATE TABLE t1(
- a TEXT,
- b NUMERIC,
- c BLOB
-);
-
--- Storage classes for the following row:
--- TEXT, REAL, TEXT
-INSERT INTO t1 VALUES('500', '500', '500');
-
--- 60 and 40 are converted to '60' and '40' and values are compared as TEXT.
-SELECT a < 60, a < 40 FROM t1;
-1|0
-
--- Comparisons are numeric. No conversions are required.
-SELECT b < 60, b < 600 FROM t1;
-0|1
-
--- Both 60 and 600 (storage class NUMERIC) are less than '500'
--- (storage class TEXT).
-SELECT c < 60, c < 600 FROM t1;
-0|0
-
-
-All mathematical operators (which is to say, all operators other -than the concatenation operator "||") apply NUMERIC -affinity to all operands prior to being carried out. If one or both -operands cannot be converted to NUMERIC then the result of the -operation is NULL.
- -For the concatenation operator, TEXT affinity is applied to both -operands. If either operand cannot be converted to TEXT (because it -is NULL or a BLOB) then the result of the concatenation is NULL.
- -When values are sorted by an ORDER by clause, values with storage -class NULL come first, followed by INTEGER and REAL values -interspersed in numeric order, followed by TEXT values usually in -memcmp() order, and finally BLOB values in memcmp() order. No storage -class conversions occur before the sort.
- -When grouping values with the GROUP BY clause values with -different storage classes are considered distinct, except for INTEGER -and REAL values which are considered equal if they are numerically -equal. No affinities are applied to any values as the result of a -GROUP by clause.
- -The compound SELECT operators UNION, -INTERSECT and EXCEPT perform implicit comparisons between values. -Before these comparisons are performed an affinity may be applied to -each value. The same affinity, if any, is applied to all values that -may be returned in a single column of the compound SELECT result set. -The affinity applied is the affinity of the column returned by the -left most component SELECTs that has a column value (and not some -other kind of expression) in that position. If for a given compound -SELECT column none of the component SELECTs return a column value, no -affinity is applied to the values from that column before they are -compared.
- -The above sections describe the operation of the database engine -in 'normal' affinity mode. SQLite version 3 will feature two other affinity -modes, as follows:
-Strict affinity mode. In this mode if a conversion - between storage classes is ever required, the database engine - returns an error and the current statement is rolled back.
- -No affinity mode. In this mode no conversions between - storage classes are ever performed. Comparisons between values of - different storage classes (except for INTEGER and REAL) are always - false.
--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: -
--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. -
- --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 datatypes.tcl
Index: datatypes.tcl
==================================================================
--- 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 {
--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. -
- --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. -
- --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. -
- --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: -
- -
-sqlite> SELECT typeof('abc'+123);
-numeric
-sqlite> SELECT typeof('abc'||123);
-text
--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. -
- --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.
-} -footer $rcsid DELETED different.tcl Index: different.tcl ================================================================== --- different.tcl +++ /dev/null @@ -1,224 +0,0 @@ -set rcsid {$Id: different.tcl,v 1.8 2006/12/18 14:12:21 drh Exp $} -source common.tcl -header {Distinctive Features Of SQLite} -puts { --This page highlights some of the characteristics of SQLite that are -unusual and which make SQLite different from many other SQL -database engines. -
-} -proc feature {tag name text} { - puts "" - puts "$name
\n" - puts "$text\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-} - -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 direct1b.gif Index: direct1b.gif ================================================================== --- direct1b.gif +++ /dev/null cannot compute difference between binary files DELETED docs.tcl Index: docs.tcl ================================================================== --- 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 { -
- May you find forgiveness for yourself and forgive others
- May you share freely, never taking more than you give. -
| } - regsub -all { +} $name {\ } name - puts "$name | " - puts {} - puts { | } - puts $desc - puts { |
| " - puts " | "
- puts "$file ($size $units) | "
- puts "" - regsub -all VERSION $desc $version d2 - puts " | [string trim $d2] |
| $title | |||
-All SQLite source code is maintained in a -CVS repository that is -available for read-only access by anyone. You can -interactively view the -repository contents and download individual files -by visiting - -http://www.sqlite.org/cvstrac/dir?d=sqlite. -To access the repository directly, use the following -commands: -
- -- --cvs -d :pserver:anonymous@www.sqlite.org:/sqlite login -cvs -d :pserver:anonymous@www.sqlite.org:/sqlite checkout sqlite -
-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: -
- -- -} - -footer $rcsid DELETED dynload.tcl Index: dynload.tcl ================================================================== --- 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 { - --cvs update -r version_2 -
-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)
-Now do the following in the bld directory -
-gcc -shared -I. -lgdbm ../sqlite/src/tclsqlite.c libsqlite.a -o sqlite.so -
-This should produce the file sqlite.so in the bld directory
-Create a pkgIndex.tcl file that contains this line - -
-package ifneeded sqlite 1.0 [list load [file join $dir sqlite.so]] -
-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....
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? -} { -See http://www.sqlite.org/datatype3.html.
-} - -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: -
-} - -faq { - Are there any known size limits to SQLite databases? -} { --SELECT name FROM - (SELECT * FROM sqlite_master UNION ALL - SELECT * FROM sqlite_temp_master) -WHERE type='table' -ORDER BY name -
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: -
- --} - -faq { - I deleted a lot of data but the database file did not get any - smaller. Is this a bug? -} { --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; -
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');
- 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:
-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 );
-
- 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 {($i) [lindex $faq($i) 0]
\n" - puts "[lindex $faq($i) 1]
-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. -
- --SQLite is implemented in layers. -(See the architecture description.) -The format of database files is determined by three different -layers in the architecture. -
- --We will describe each layer beginning with the bottom (pager) -layer and working upwards. -
- --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. -
- --Here is a summary of the journal file format: -
- --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. -
- --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: -
- --Conceptually, a b-tree page contains N database entries and N+1 pointers -to other b-tree pages. -
- --- --
-- -Ptr -
0Entry -
0Ptr -
1Entry -
1... -Ptr -
N-1Entry -
N-1Ptr -
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. -
- --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. -
- --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. -
- -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. -
- --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. -
- --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. -
- --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 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 formatchng.tcl Index: formatchng.tcl ================================================================== --- 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 { --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: -
- -- sqlite-old old.db .dump | sqlite-new new.db --} -footer $rcsid DELETED fullscanb.gif Index: fullscanb.gif ================================================================== --- fullscanb.gif +++ /dev/null cannot compute difference between binary files ADDED images/arch.gif Index: images/arch.gif ================================================================== --- /dev/null +++ images/arch.gif cannot compute difference between binary files ADDED images/arch.png Index: images/arch.png ================================================================== --- /dev/null +++ images/arch.png cannot compute difference between binary files ADDED images/arch2.gif Index: images/arch2.gif ================================================================== --- /dev/null +++ images/arch2.gif cannot compute difference between binary files ADDED images/direct1b.gif Index: images/direct1b.gif ================================================================== --- /dev/null +++ images/direct1b.gif cannot compute difference between binary files ADDED images/foreignlogos/adobe.gif Index: images/foreignlogos/adobe.gif ================================================================== --- /dev/null +++ images/foreignlogos/adobe.gif cannot compute difference between binary files ADDED images/foreignlogos/apple.gif Index: images/foreignlogos/apple.gif ================================================================== --- /dev/null +++ images/foreignlogos/apple.gif cannot compute difference between binary files ADDED images/foreignlogos/firefox.gif Index: images/foreignlogos/firefox.gif ================================================================== --- /dev/null +++ images/foreignlogos/firefox.gif cannot compute difference between binary files ADDED images/foreignlogos/ge.gif Index: images/foreignlogos/ge.gif ================================================================== --- /dev/null +++ images/foreignlogos/ge.gif cannot compute difference between binary files ADDED images/foreignlogos/google.gif Index: images/foreignlogos/google.gif ================================================================== --- /dev/null +++ images/foreignlogos/google.gif cannot compute difference between binary files ADDED images/foreignlogos/mcaffee.gif Index: images/foreignlogos/mcaffee.gif ================================================================== --- /dev/null +++ images/foreignlogos/mcaffee.gif cannot compute difference between binary files ADDED images/foreignlogos/microsoft.gif Index: images/foreignlogos/microsoft.gif ================================================================== --- /dev/null +++ images/foreignlogos/microsoft.gif cannot compute difference between binary files ADDED images/foreignlogos/monotone.gif Index: images/foreignlogos/monotone.gif ================================================================== --- /dev/null +++ images/foreignlogos/monotone.gif cannot compute difference between binary files ADDED images/foreignlogos/philips.gif Index: images/foreignlogos/philips.gif ================================================================== --- /dev/null +++ images/foreignlogos/philips.gif cannot compute difference between binary files ADDED images/foreignlogos/php.gif Index: images/foreignlogos/php.gif ================================================================== --- /dev/null +++ images/foreignlogos/php.gif cannot compute difference between binary files ADDED images/foreignlogos/python.gif Index: images/foreignlogos/python.gif ================================================================== --- /dev/null +++ images/foreignlogos/python.gif cannot compute difference between binary files ADDED images/foreignlogos/realbasic.gif Index: images/foreignlogos/realbasic.gif ================================================================== --- /dev/null +++ images/foreignlogos/realbasic.gif cannot compute difference between binary files ADDED images/foreignlogos/skype.gif Index: images/foreignlogos/skype.gif ================================================================== --- /dev/null +++ images/foreignlogos/skype.gif cannot compute difference between binary files ADDED images/foreignlogos/sunmicro.gif Index: images/foreignlogos/sunmicro.gif ================================================================== --- /dev/null +++ images/foreignlogos/sunmicro.gif cannot compute difference between binary files ADDED images/foreignlogos/symbian.gif Index: images/foreignlogos/symbian.gif ================================================================== --- /dev/null +++ images/foreignlogos/symbian.gif cannot compute difference between binary files ADDED images/foreignlogos/toshiba.gif Index: images/foreignlogos/toshiba.gif ================================================================== --- /dev/null +++ images/foreignlogos/toshiba.gif cannot compute difference between binary files ADDED images/fullscanb.gif Index: images/fullscanb.gif ================================================================== --- /dev/null +++ images/fullscanb.gif cannot compute difference between binary files ADDED images/index-ex1-x-b.gif Index: images/index-ex1-x-b.gif ================================================================== --- /dev/null +++ images/index-ex1-x-b.gif cannot compute difference between binary files ADDED images/indirect1b1.gif Index: images/indirect1b1.gif ================================================================== --- /dev/null +++ images/indirect1b1.gif cannot compute difference between binary files ADDED images/ne.png Index: images/ne.png ================================================================== --- /dev/null +++ images/ne.png cannot compute difference between binary files ADDED images/nocopy.gif Index: images/nocopy.gif ================================================================== --- /dev/null +++ images/nocopy.gif cannot compute difference between binary files ADDED images/nw.png Index: images/nw.png ================================================================== --- /dev/null +++ images/nw.png cannot compute difference between binary files ADDED images/se.png Index: images/se.png ================================================================== --- /dev/null +++ images/se.png cannot compute difference between binary files ADDED images/shared.gif Index: images/shared.gif ================================================================== --- /dev/null +++ images/shared.gif cannot compute difference between binary files ADDED images/sw.png Index: images/sw.png ================================================================== --- /dev/null +++ images/sw.png cannot compute difference between binary files ADDED images/table-ex1b2.gif Index: images/table-ex1b2.gif ================================================================== --- /dev/null +++ images/table-ex1b2.gif cannot compute difference between binary files DELETED index-ex1-x-b.gif Index: index-ex1-x-b.gif ================================================================== --- index-ex1-x-b.gif +++ /dev/null cannot compute difference between binary files DELETED index.tcl Index: index.tcl ================================================================== --- index.tcl +++ /dev/null @@ -1,115 +0,0 @@ -#!/usr/bin/tclsh -source common.tcl -header {SQLite home page} -puts { -
-About SQLite--
-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 implements the follow syntax:
-Details on the implementation of each command are provided in -the sequel.
-} - -proc Operator {name} { - return "$name" -} -proc Nonterminal {name} { - return "$name" -} -proc Keyword {name} { - return "$name" -} -proc Example {text} { - puts "" -} - -proc Section {name label} { - global outputdir - - if {[string length $outputdir]!=0} { - if {[llength [info commands puts_standard]]>0} { - footer $::rcsid - } - - if {[string length $label]>0} { - rename puts puts_standard - proc puts {str} { - regsub -all {href="#([a-z]+)"} $str {href="lang_\1.html"} str - puts_standard $::section_file $str - } - rename footer footer_standard - proc footer {id} { - footer_standard $id - rename footer "" - rename puts "" - rename puts_standard puts - rename footer_standard footer - } - set ::section_file [open [file join $outputdir lang_$label.html] w] - header "Query Language Understood by SQLite: $name" - puts "$text
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 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.
-} - -Section {ANALYZE} analyze - -Syntax {sql-statement} { - ANALYZE -} -Syntax {sql-statement} { - ANALYZEThe 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.
-} - -Section {ATTACH DATABASE} attach - -Syntax {sql-statement} { -ATTACH [DATABASE]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.
-} - - -Section {BEGIN TRANSACTION} transaction - -Syntax {sql-statement} { -BEGIN [ DEFERRED | IMMEDIATE | EXCLUSIVE ] [TRANSACTION [-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. -
- -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:
- --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.
-} - - -Section comment comment - -Syntax {comment} {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 [ ORThe 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 \\.]\".
" - - -Section {CREATE INDEX} createindex - -Syntax {sql-statement} { -CREATE [UNIQUE] INDEX [IF NOT EXISTS] [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.
- -Indexes are removed with the DROP INDEX -command.
-} - - -Section {CREATE TABLE} {createtable} - -Syntax {sql-command} { -CREATE [TEMP | TEMPORARY] TABLE [IF NOT EXISTS] [A CREATE TABLE statement is basically the keywords "CREATE TABLE" -followed by the name of a new table and a parenthesized list of column -definitions and constraints. The table name can be either an identifier -or a string. Tables names that begin with "sqlite_" are reserved -for use by the engine.
- -Each column definition is the name of the column followed by the -datatype for that column, then one or more optional column constraints. -The datatype for the column does not restrict what data may be put -in that column. -See Datatypes In SQLite Version 3 for -additional information. -The UNIQUE constraint causes an index to be created on the specified -columns. This index must contain unique keys. -The COLLATE clause specifies what text -collating function to use when comparing text entries for the column. -The built-in BINARY collating function is used by default. -
-The DEFAULT constraint specifies a default value to use when doing an INSERT. -The value may be NULL, a string constant or a number. Starting with version -3.1.0, the default value may also be one of the special case-independant -keywords CURRENT_TIME, CURRENT_DATE or CURRENT_TIMESTAMP. If the value is -NULL, a string constant or number, it is literally inserted into the column -whenever an INSERT statement that does not specify a value for the column is -executed. If the value is CURRENT_TIME, CURRENT_DATE or CURRENT_TIMESTAMP, then -the current UTC date and/or time is inserted into the columns. For -CURRENT_TIME, the format is HH:MM:SS. For CURRENT_DATE, YYYY-MM-DD. The format -for CURRENT_TIMESTAMP is "YYYY-MM-DD HH:MM:SS". -
- -Specifying a PRIMARY KEY normally just creates a UNIQUE index -on the corresponding columns. However, if primary key is on a single column -that has datatype INTEGER, then that column is used internally -as the actual key of the B-Tree for the table. This means that the column -may only hold unique integer values. (Except for this one case, -SQLite ignores the datatype specification of columns and allows -any kind of data to be put in a column regardless of its declared -datatype.) If a table does not have an INTEGER PRIMARY KEY column, -then the B-Tree key will be a automatically generated integer. - The -B-Tree key for a row can always be accessed using one of the -special names "ROWID", "OID", or "_ROWID_". -This is true regardless of whether or not there is an INTEGER -PRIMARY KEY. An INTEGER PRIMARY KEY column can also include the -keyword AUTOINCREMENT. The AUTOINCREMENT keyword modified the way -that B-Tree keys are automatically generated. Additional detail -on automatic B-Tree key generation is available -separately.
- -According to the SQL standard, PRIMARY KEY should imply NOT NULL. -Unfortunately, due to a long-standing coding oversight, this is not -the case in SQLite. SQLite allows NULL values -in a PRIMARY KEY column. We could change SQLite to conform to the -standard (and we might do so in the future), but by the time the -oversight was discovered, SQLite was in such wide use that we feared -breaking legacy code if we fixed the problem. So for now we have -chosen to contain allowing NULLs in PRIMARY KEY columns. -Developers should be aware, however, that we may change SQLite to -conform to the SQL standard in future and should design new programs -accordingly.
- -If the "TEMP" or "TEMPORARY" keyword occurs in between "CREATE" -and "TABLE" then the table that is created is only visible -within that same database connection -and is automatically deleted when -the database connection is closed. Any indices created on a temporary table -are also temporary. Temporary tables and indices are stored in a -separate file distinct from the main database file.
- -If a <database-name> is specified, then the table is created in -the named database. It is an error to specify both a <database-name> -and the TEMP keyword, unless the <database-name> is "temp". If no -database name is specified, and the TEMP keyword is not present, -the table is created in the main database.
- -The optional conflict-clause following each constraint -allows the specification of an alternative default -constraint conflict resolution algorithm for that constraint. -The default is abort ABORT. Different constraints within the same -table may have different default conflict resolution algorithms. -If an COPY, INSERT, or UPDATE command specifies a different conflict -resolution algorithm, then that algorithm is used in place of the -default algorithm specified in the CREATE TABLE statement. -See the section titled -ON CONFLICT for additional information.
- -CHECK constraints are supported as of version 3.3.0. Prior -to version 3.3.0, CHECK constraints were parsed but not enforced.
- -There are no arbitrary limits on the number -of columns or on the number of constraints in a table. -The total amount of data in a single row is limited to about -1 megabytes in version 2.8. In version 3.0 there is no arbitrary -limit on the amount of data in a row.
- - -The CREATE TABLE AS form defines the table to be -the result set of a query. The names of the table columns are -the names of the columns in the result.
- -The exact text -of each CREATE TABLE statement is stored in the sqlite_master -table. Every time the database is opened, all CREATE TABLE statements -are read from the sqlite_master table and used to regenerate -SQLite's internal representation of the table layout. -If the original command was a CREATE TABLE AS then then an equivalent -CREATE TABLE statement is synthesized and store in sqlite_master -in place of the original command. -The text of CREATE TEMPORARY TABLE statements are stored in the -sqlite_temp_master table. -
- -If the optional IF NOT EXISTS clause is present and another table -with the same name aleady exists, then this command becomes a no-op.
- -Tables are removed using the DROP TABLE -statement.
-} - - -Section {CREATE TRIGGER} createtrigger - -Syntax {sql-statement} { -CREATE [TEMP | TEMPORARY] TRIGGER [IF NOT EXISTS]The CREATE TRIGGER statement is used to add triggers to the -database schema. Triggers are database operations (the trigger-action) -that are automatically performed when a specified database event (the -database-event) occurs.
- -A trigger may be specified to fire whenever a DELETE, INSERT or UPDATE of a -particular database table occurs, or whenever an UPDATE of one or more -specified columns of a table are updated.
- -At this time SQLite supports only FOR EACH ROW triggers, not FOR EACH -STATEMENT triggers. Hence explicitly specifying FOR EACH ROW is optional. FOR -EACH ROW implies that the SQL statements specified as trigger-steps -may be executed (depending on the WHEN clause) for each database row being -inserted, updated or deleted by the statement causing the trigger to fire.
- -Both the WHEN clause and the trigger-steps may access elements of -the row being inserted, deleted or updated using references of the form -"NEW.column-name" and "OLD.column-name", where -column-name is the name of a column from the table that the trigger -is associated with. OLD and NEW references may only be used in triggers on -trigger-events for which they are relevant, as follows:
- -| INSERT | -NEW references are valid | -
| UPDATE | -NEW and OLD references are valid | -
| DELETE | -OLD references are valid | -
If a WHEN clause is supplied, the SQL statements specified as trigger-steps are only executed for rows for which the WHEN clause is true. If no WHEN clause is supplied, the SQL statements are executed for all rows.
- -The specified trigger-time determines when the trigger-steps -will be executed relative to the insertion, modification or removal of the -associated row.
- -An ON CONFLICT clause may be specified as part of an UPDATE or INSERT -trigger-step. However if an ON CONFLICT clause is specified as part of -the statement causing the trigger to fire, then this conflict handling -policy is used instead.
- -Triggers are automatically dropped when the table that they are -associated with is dropped.
- -Triggers may be created on views, as well as ordinary tables, by specifying -INSTEAD OF in the CREATE TRIGGER statement. If one or more ON INSERT, ON DELETE -or ON UPDATE triggers are defined on a view, then it is not an error to execute -an INSERT, DELETE or UPDATE statement on the view, respectively. Thereafter, -executing an INSERT, DELETE or UPDATE on the view causes the associated - triggers to fire. The real tables underlying the view are not modified - (except possibly explicitly, by a trigger program).
- -Example:
- -Assuming that customer records are stored in the "customers" table, and -that order records are stored in the "orders" table, the following trigger -ensures that all associated orders are redirected when a customer changes -his or her address:
-} -Example { -CREATE TRIGGER update_customer_address UPDATE OF address ON customers - BEGIN - UPDATE orders SET address = new.address WHERE customer_name = old.name; - END; -} -puts { -With this trigger installed, executing the statement:
-} - -Example { -UPDATE customers SET address = '1 Main St.' WHERE name = 'Jack Jones'; -} -puts { -causes the following to be automatically executed:
-} -Example { -UPDATE orders SET address = '1 Main St.' WHERE customer_name = 'Jack Jones'; -} - -puts { -Note that currently, triggers may behave oddly when created on tables - with INTEGER PRIMARY KEY fields. If a BEFORE trigger program modifies the - INTEGER PRIMARY KEY field of a row that will be subsequently updated by the - statement that causes the trigger to fire, then the update may not occur. - The workaround is to declare the table with a PRIMARY KEY column instead - of an INTEGER PRIMARY KEY column.
-} - -puts { -A special SQL function RAISE() may be used within a trigger-program, with the following syntax
-} -Syntax {raise-function} { -RAISE ( ABORT,When one of the first three forms is called during trigger-program execution, the specified ON CONFLICT processing is performed (either ABORT, FAIL or - ROLLBACK) and the current query terminates. An error code of SQLITE_CONSTRAINT is returned to the user, along with the specified error message.
- -When RAISE(IGNORE) is called, the remainder of the current trigger program, -the statement that caused the trigger program to execute and any subsequent - trigger programs that would of been executed are abandoned. No database - changes are rolled back. If the statement that caused the trigger program - to execute is itself part of a trigger program, then that trigger program - resumes execution at the beginning of the next step. -
- -Triggers are removed using the DROP TRIGGER -statement.
-} - - -Section {CREATE VIEW} {createview} - -Syntax {sql-command} { -CREATE [TEMP | TEMPORARY] VIEW [IF NOT EXISTS] [The CREATE VIEW command assigns a name to a pre-packaged -SELECT -statement. Once the view is created, it can be used in the FROM clause -of another SELECT in place of a table name. -
- -If the "TEMP" or "TEMPORARY" keyword occurs in between "CREATE" -and "VIEW" then the view that is created is only visible to the -process that opened the database and is automatically deleted when -the database is closed.
- -If a <database-name> is specified, then the view is created in -the named database. It is an error to specify both a <database-name> -and the TEMP keyword, unless the <database-name> is "temp". If no -database name is specified, and the TEMP keyword is not present, -the table is created in the main database.
- -You cannot COPY, DELETE, INSERT or UPDATE a view. Views are read-only -in SQLite. However, in many cases you can use a -TRIGGER on the view to accomplish the same thing. Views are removed -with the DROP VIEW -command.
-} - -Section {CREATE VIRTUAL TABLE} {createvtab} - -Syntax {sql-command} { -CREATE VIRTUAL TABLE [A virtual table is an interface to an external storage or computation -engine that appears to be a table but does not actually store information -in the database file.
- -In general, you can do anything with a virtual table that can be done -with an ordinary table, except that you cannot create triggers on a -virtual table. Some virtual table implementations might impose additional -restrictions. For example, many virtual tables are read-only.
- -The <module-name> is the name of an object that implements -the virtual table. The <module-name> must be registered with -the SQLite database connection using -sqlite3_create_module -prior to issuing the CREATE VIRTUAL TABLE statement. -The module takes zero or more comma-separated arguments. -The arguments can be just about any text as long as it has balanced -parentheses. The argument syntax is sufficiently general that the -arguments can be made to appear as column definitions in a traditional -CREATE TABLE statement. -SQLite passes the module arguments directly -to the module without any interpretation. It is the responsibility -of the module implementation to parse and interpret its own arguments.
- -A virtual table is destroyed using the ordinary -DROP TABLE statement. There is no -DROP VIRTUAL TABLE statement.
-} - -Section DELETE delete - -Syntax {sql-statement} { -DELETE FROM [The DELETE command is used to remove records from a table. -The command consists of the "DELETE FROM" keywords followed by -the name of the table from which records are to be removed. -
- -Without a WHERE clause, all rows of the table are removed. -If a WHERE clause is supplied, then only those rows that match -the expression are removed.
-} - - -Section {DETACH DATABASE} detach - -Syntax {sql-command} { -DETACH [DATABASE]This statement detaches an additional database connection previously -attached using the ATTACH DATABASE statement. It -is possible to have the same database file attached multiple times using -different names, and detaching one connection to a file will leave the -others intact.
- -This statement will fail if SQLite is in the middle of a transaction.
-} - - -Section {DROP INDEX} dropindex - -Syntax {sql-command} { -DROP INDEX [IF EXISTS] [The DROP INDEX statement removes an index added -with the -CREATE INDEX statement. The index named is completely removed from -the disk. The only way to recover the index is to reenter the -appropriate CREATE INDEX command.
- -The DROP INDEX statement does not reduce the size of the database -file in the default mode. -Empty space in the database is retained for later INSERTs. To -remove free space in the database, use the VACUUM -command. If AUTOVACUUM mode is enabled for a database then space -will be freed automatically by DROP INDEX.
-} - - -Section {DROP TABLE} droptable - -Syntax {sql-command} { -DROP TABLE [IF EXISTS] [The DROP TABLE statement removes a table added with the CREATE TABLE statement. The name specified is the -table name. It is completely removed from the database schema and the -disk file. The table can not be recovered. All indices associated -with the table are also deleted.
- -The DROP TABLE statement does not reduce the size of the database -file in the default mode. Empty space in the database is retained for -later INSERTs. To -remove free space in the database, use the VACUUM -command. If AUTOVACUUM mode is enabled for a database then space -will be freed automatically by DROP TABLE.
- -The optional IF EXISTS clause suppresses the error that would normally -result if the table does not exist.
-} - - -Section {DROP TRIGGER} droptrigger -Syntax {sql-statement} { -DROP TRIGGER [IF EXISTS] [The DROP TRIGGER statement removes a trigger created by the -CREATE TRIGGER statement. The trigger is -deleted from the database schema. Note that triggers are automatically -dropped when the associated table is dropped.
-} - - -Section {DROP VIEW} dropview - -Syntax {sql-command} { -DROP VIEW [IF EXISTS]The DROP VIEW statement removes a view created by the CREATE VIEW statement. The name specified is the -view name. It is removed from the database schema, but no actual data -in the underlying base tables is modified.
-} - - -Section EXPLAIN explain - -Syntax {sql-statement} { -EXPLAINThe EXPLAIN command modifier is a non-standard extension. The -idea comes from a similar command found in PostgreSQL, but the operation -is completely different.
- -If the EXPLAIN keyword appears before any other SQLite SQL command -then instead of actually executing the command, the SQLite library will -report back the sequence of virtual machine instructions it would have -used to execute the command had the EXPLAIN keyword not been present. -For additional information about virtual machine instructions see -the architecture description or the documentation -on available opcodes for the virtual machine.
-} - - -Section expression expr - -Syntax {expr} { -This section is different from the others. Most other sections of -this document talks about a particular SQL command. This section does -not talk about a standalone command but about "expressions" which are -subcomponents of most other commands.
- -SQLite understands the following binary operators, in order from -highest to lowest precedence:
- -- --|| -* / % -+ - -<< >> & | -< <= > >= -= == != <> IN -AND -OR -
Supported unary prefix operators are these:
- -- --- + ! ~ NOT -
The COLLATE operator can be thought of as a unary postfix -operator. The COLLATE operator has the highest precedence. -It always binds more tightly than any prefix unary operator or -any binary operator.
- -The unary operator [Operator +] is a no-op. It can be applied -to strings, numbers, or blobs and it always gives as its result the -value of the operand.
- -Note that there are two variations of the equals and not equals -operators. Equals can be either} -puts "[Operator =] or [Operator ==]. -The non-equals operator can be either -[Operator !=] or [Operator {<>}]. -The [Operator ||] operator is \"concatenate\" - it joins together -the two strings of its operands. -The operator [Operator %] outputs the remainder of its left -operand modulo its right operand.
- -The result of any binary operator is a numeric value, except -for the [Operator ||] concatenation operator which gives a string -result.
" - -puts { - - --A literal value is an integer number or a floating point number. -Scientific notation is supported. The "." character is always used -as the decimal point even if the locale setting specifies "," for -this role - the use of "," for the decimal point would result in -syntactic ambiguity. A string constant is formed by enclosing the -string in single quotes ('). A single quote within the string can -be encoded by putting two single quotes in a row - as in Pascal. -C-style escapes using the backslash character are not supported because -they are not standard SQL. -BLOB literals are string literals containing hexadecimal data and -preceded by a single "x" or "X" character. For example:
- -- --X'53514C697465' -
-A literal value can also be the token "NULL". -
- --A parameter specifies a placeholder in the expression for a literal -value that is filled in at runtime using the -sqlite3_bind API. -Parameters can take several forms: -
-| ?NNN | - | A question mark followed by a number NNN holds a spot for the -NNN-th parameter. NNN must be between 1 and 999. | -
| ? | - | A question mark that is not followed by a number holds a spot for -the next unused parameter. | -
| :AAAA | - | A colon followed by an identifier name holds a spot for a named -parameter with the name AAAA. Named parameters are also numbered. -The number assigned is the next unused number. To avoid confusion, -it is best to avoid mixing named and numbered parameters. | -
| @AAAA | - | An "at" sign works exactly like a colon. | -
| $AAAA | - | A dollar-sign followed by an identifier name also holds a spot for a named -parameter with the name AAAA. The identifier name in this case can include -one or more occurances of "::" and a suffix enclosed in "(...)" containing -any text at all. This syntax is the form of a variable name in the Tcl -programming language. | -
Parameters that are not assigned values using -sqlite3_bind are treated -as NULL.
- - -The LIKE operator does a pattern matching comparison. The operand -to the right contains the pattern, the left hand operand contains the -string to match against the pattern. -} -puts "A percent symbol [Operator %] in the pattern matches any -sequence of zero or more characters in the string. An underscore -[Operator _] in the pattern matches any single character in the -string. Any other character matches itself or it's lower/upper case -equivalent (i.e. case-insensitive matching). (A bug: SQLite only -understands upper/lower case for 7-bit Latin characters. Hence the -LIKE operator is case sensitive for 8-bit iso8859 characters or UTF-8 -characters. For example, the expression 'a' LIKE 'A' -is TRUE but 'æ' LIKE 'Æ' is FALSE.).
" - -puts { -If the optional ESCAPE clause is present, then the expression -following the ESCAPE keyword must evaluate to a string consisting of -a single character. This character may be used in the LIKE pattern -to include literal percent or underscore characters. The escape -character followed by a percent symbol, underscore or itself matches a -literal percent symbol, underscore or escape character in the string, -respectively. The infix LIKE operator is implemented by calling the -user function like(X,Y).
-} - -puts { -The LIKE operator is not case sensitive and will match upper case -characters on one side against lower case characters on the other. -(A bug: SQLite only understands upper/lower case for 7-bit Latin -characters. Hence the LIKE operator is case sensitive for 8-bit -iso8859 characters or UTF-8 characters. For example, the expression -'a' LIKE 'A' is TRUE but -'æ' LIKE 'Æ' is FALSE.). - -The infix LIKE -operator is implemented by calling the user function -like(X,Y). If an ESCAPE clause is present, it adds -a third parameter to the function call. If the functionality of LIKE can be -overridden by defining an alternative implementation of the -like() SQL function.
- - - -The GLOB operator is similar to LIKE but uses the Unix -file globbing syntax for its wildcards. Also, GLOB is case -sensitive, unlike LIKE. Both GLOB and LIKE may be preceded by -the NOT keyword to invert the sense of the test. The infix GLOB -operator is implemented by calling the user function -glob(X,Y) and can be modified by overriding -that function.
- - -The REGEXP operator is a special syntax for the regexp() -user function. No regexp() user function is defined by default -and so use of the REGEXP operator will normally result in an -error message. If a user-defined function named "regexp" -is added at run-time, that function will be called in order -to implement the REGEXP operator.
- - -The MATCH operator is a special syntax for the match() -user function. The default match() function implementation -raises and exception and is not really useful for anything. -But extensions can override the match() function with more -helpful logic.
- -A column name can be any of the names defined in the CREATE TABLE -statement or one of the following special identifiers: "ROWID", -"OID", or "_ROWID_". -These special identifiers all describe the -unique integer key (the "row key") associated with every -row of every table. -The special identifiers only refer to the row key if the CREATE TABLE -statement does not define a real column with the same name. Row keys -act like read-only columns. A row key can be used anywhere a regular -column can be used, except that you cannot change the value -of a row key in an UPDATE or INSERT statement. -"SELECT * ..." does not return the row key.
- -SELECT statements can appear in expressions as either the -right-hand operand of the IN operator, as a scalar quantity, or -as the operand of an EXISTS operator. -As a scalar quantity or the operand of an IN operator, -the SELECT should have only a single column in its -result. Compound SELECTs (connected with keywords like UNION or -EXCEPT) are allowed. -With the EXISTS operator, the columns in the result set of the SELECT are -ignored and the expression returns TRUE if one or more rows exist -and FALSE if the result set is empty. -If no terms in the SELECT expression refer to value in the containing -query, then the expression is evaluated once prior to any other -processing and the result is reused as necessary. If the SELECT expression -does contain variables from the outer query, then the SELECT is reevaluated -every time it is needed.
- -When a SELECT is the right operand of the IN operator, the IN -operator returns TRUE if the result of the left operand is any of -the values generated by the select. The IN operator may be preceded -by the NOT keyword to invert the sense of the test.
- -When a SELECT appears within an expression but is not the right -operand of an IN operator, then the first row of the result of the -SELECT becomes the value used in the expression. If the SELECT yields -more than one result row, all rows after the first are ignored. If -the SELECT yields no rows, then the value of the SELECT is NULL.
- -A CAST expression changes the datatype of the
Both simple and aggregate functions are supported. A simple -function can be used in any expression. Simple functions return -a result immediately based on their inputs. Aggregate functions -may only be used in a SELECT statement. Aggregate functions compute -their result across all rows of the result set.
- - -Core Functions - -The core functions shown below are available by default. Additional -functions may be written in C and added to the database engine using -the sqlite3_create_function() -API.
- -| abs(X) | -Return the absolute value of argument X. | -
| coalesce(X,Y,...) | -Return a copy of the first non-NULL argument. If -all arguments are NULL then NULL is returned. There must be at least -2 arguments. | -
| - -glob(X,Y) | -This function is used to implement the -"X GLOB Y" syntax of SQLite. The -sqlite3_create_function() -interface can -be used to override this function and thereby change the operation -of the GLOB operator. | -
| ifnull(X,Y) | -Return a copy of the first non-NULL argument. If -both arguments are NULL then NULL is returned. This behaves the same as -coalesce() above. | -
| - -hex(X) | -The argument is interpreted as a BLOB. The result -is a hexadecimal rendering of the content of that blob. | -
| last_insert_rowid() | -Return the ROWID -of the last row insert from this -connection to the database. This is the same value that would be returned -from the sqlite_last_insert_rowid() API function. | -
| length(X) | -Return the string length of X in characters. -If SQLite is configured to support UTF-8, then the number of UTF-8 -characters is returned, not the number of bytes. | -
|
-
-like(X,Y) -like(X,Y,Z) |
--This function is used to implement the "X LIKE Y [ESCAPE Z]" -syntax of SQL. If the optional ESCAPE clause is present, then the -user-function is invoked with three arguments. Otherwise, it is -invoked with two arguments only. The - -sqlite_create_function() interface can be used to override this -function and thereby change the operation of the LIKE operator. When doing this, it may be important -to override both the two and three argument versions of the like() -function. Otherwise, different code may be called to implement the -LIKE operator depending on whether or not an ESCAPE clause was -specified. | -
| load_extension(X) -load_extension(X,Y) |
-Load SQLite extensions out of the shared library
-file named X using the entry point Y. The result
-is a NULL. If Y is omitted then the default entry point
-of sqlite3_extension_init is used. This function raises
-an exception if the extension fails to load or initialize correctly.
-
- This function will fail if the extension attempts to modify -or delete a SQL function or collating sequence. The -extension can add new functions or collating sequences, but cannot -modify or delete existing functions or collating sequences because -those functions and/or collating sequences might be used elsewhere -in the currently running SQL statement. To load an extension that -changes or deletes functions or collating sequences, use the -sqlite3_load_extension() -C-language API. - |
| lower(X) | -Return a copy of string X will all characters -converted to lower case. The C library tolower() routine is used -for the conversion, which means that this function might not -work correctly on UTF-8 characters. | -
|
-
-ltrim(X) ltrim(X,Y) |
-Return a string formed by removing any and all -characters that appear in Y from the left side of X. -If the Y argument is omitted, spaces are removed. | -
| max(X,Y,...) | -Return the argument with the maximum value. Arguments -may be strings in addition to numbers. The maximum value is determined -by the usual sort order. Note that max() is a simple function when -it has 2 or more arguments but converts to an aggregate function if given -only a single argument. | -
| min(X,Y,...) | -Return the argument with the minimum value. Arguments -may be strings in addition to numbers. The minimum value is determined -by the usual sort order. Note that min() is a simple function when -it has 2 or more arguments but converts to an aggregate function if given -only a single argument. | -
| nullif(X,Y) | -Return the first argument if the arguments are different, -otherwise return NULL. | -
| quote(X) | -This routine returns a string which is the value of -its argument suitable for inclusion into another SQL statement. -Strings are surrounded by single-quotes with escapes on interior quotes -as needed. BLOBs are encoded as hexadecimal literals. -The current implementation of VACUUM uses this function. The function -is also useful when writing triggers to implement undo/redo functionality. - | -
| random(*) | -Return a pseudo-random integer -between -9223372036854775808 and +9223372036854775807. | -
| - -replace(X,Y,Z) | -Return a string formed by substituting string Z for -every occurrance of string Y in string X. The BINARY -collating sequence is used for comparisons. | -
| - -randomblob(N) | -Return a N-byte blob containing pseudo-random bytes. -N should be a postive integer. | -
| round(X) round(X,Y) |
-Round off the number X to Y digits to the -right of the decimal point. If the Y argument is omitted, 0 is -assumed. | -
|
-
-rtrim(X) rtrim(X,Y) |
-Return a string formed by removing any and all -characters that appear in Y from the right side of X. -If the Y argument is omitted, spaces are removed. | -
| soundex(X) | -Compute the soundex encoding of the string X. -The string "?000" is returned if the argument is NULL. -This function is omitted from SQLite by default. -It is only available the -DSQLITE_SOUNDEX=1 compiler option -is used when SQLite is built. | -
| sqlite_version(*) | -Return the version string for the SQLite library -that is running. Example: "2.8.0" | -
|
- substr(X,Y,Z) - substr(X,Y) |
-Return a substring of input string X that begins -with the Y-th character and which is Z characters long. -If Z is omitted then all character through the end of the string -are returned. -The left-most character of X is number 1. If Y is negative -the the first character of the substring is found by counting from the -right rather than the left. If X is string -then characters indices refer to actual UTF-8 characters. If -X is a BLOB then the indices refer to bytes. | -
|
-
-trim(X) trim(X,Y) |
-Return a string formed by removing any and all -characters that appear in Y from both ends of X. -If the Y argument is omitted, spaces are removed. | -
| typeof(X) | -Return the type of the expression X. The only -return values are "null", "integer", "real", "text", and "blob". -SQLite's type handling is -explained in Datatypes in SQLite Version 3. | -
| upper(X) | -Return a copy of input string X converted to all -upper-case letters. The implementation of this function uses the C library -routine toupper() which means it may not work correctly on -UTF-8 strings. | -
| zeroblob(N) | --Return a BLOB consisting of N bytes of 0x00. SQLite -manages these zeroblobs very efficiently. Zeroblobs can be used to -reserve space for a BLOB that is later written using -incremental BLOB I/O. | -
Date and time functions are documented in the - -SQLite Wiki.
- - -Aggregate Functions - --The aggregate functions shown below are available by default. Additional -aggregate functions written in C may be added using the -sqlite3_create_function() -API.
- --In any aggregate function that takes a single argument, that argument -can be preceeded by the keyword DISTINCT. In such cases, duplicate -elements are filtered before being passed into the aggregate function. -For example, the function "count(distinct X)" will return the number -of distinct values of column X instead of the total number of non-null -values in column X. -
- -| avg(X) | -Return the average value of all non-NULL X within a -group. String and BLOB values that do not look like numbers are -interpreted as 0. -The result of avg() is always a floating point value even if all -inputs are integers. | -
| count(X) count(*) |
-The first form return a count of the number of times -that X is not NULL in a group. The second form (with no argument) -returns the total number of rows in the group. | -
| max(X) | -Return the maximum value of all values in the group. -The usual sort order is used to determine the maximum. | -
| min(X) | -Return the minimum non-NULL value of all values in the group. -The usual sort order is used to determine the minimum. NULL is only returned -if all values in the group are NULL. | -
| sum(X) total(X) |
-Return the numeric sum of all non-NULL values in the group.
- If there are no non-NULL input rows then sum() returns
- NULL but total() returns 0.0.
- NULL is not normally a helpful result for the sum of no rows
- but the SQL standard requires it and most other
- SQL database engines implement sum() that way so SQLite does it in the
- same way in order to be compatible. The non-standard total() function
- is provided as a convenient way to work around this design problem
- in the SQL language.
-
- The result of total() is always a floating point value. - The result of sum() is an integer value if all non-NULL inputs are integers. - If any input to sum() is neither an integer or a NULL - then sum() returns a floating point value - which might be an approximation to the true sum. - -Sum() will throw an "integer overflow" exception if all inputs - are integers or NULL - and an integer overflow occurs at any point during the computation. - Total() never throws an exception. - |
The INSERT statement comes in two basic forms. The first form -(with the "VALUES" keyword) creates a single new row in an existing table. -If no column-list is specified then the number of values must -be the same as the number of columns in the table. If a column-list -is specified, then the number of values must match the number of -specified columns. Columns of the table that do not appear in the -column list are filled with the default value, or with NULL if no -default value is specified. -
- -The second form of the INSERT statement takes it data from a -SELECT statement. The number of columns in the result of the -SELECT must exactly match the number of columns in the table if -no column list is specified, or it must match the number of columns -name in the column list. A new entry is made in the table -for every row of the SELECT result. The SELECT may be simple -or compound.
- -The optional conflict-clause allows the specification of an alternative -constraint conflict resolution algorithm to use during this one command. -See the section titled -ON CONFLICT for additional information. -For compatibility with MySQL, the parser allows the use of the -single keyword REPLACE as an alias for "INSERT OR REPLACE". -
-} - - -Section {ON CONFLICT clause} conflict - -Syntax {conflict-clause} { -ON CONFLICTThe ON CONFLICT clause is not a separate SQL command. It is a -non-standard clause that can appear in many other SQL commands. -It is given its own section in this document because it is not -part of standard SQL and therefore might not be familiar.
- -The syntax for the ON CONFLICT clause is as shown above for -the CREATE TABLE command. For the INSERT and -UPDATE commands, the keywords "ON CONFLICT" are replaced by "OR", to make -the syntax seem more natural. For example, instead of -"INSERT ON CONFLICT IGNORE" we have "INSERT OR IGNORE". -The keywords change but the meaning of the clause is the same -either way.
- -The ON CONFLICT clause specifies an algorithm used to resolve -constraint conflicts. There are five choices: ROLLBACK, ABORT, -FAIL, IGNORE, and REPLACE. The default algorithm is ABORT. This -is what they mean:
- -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.
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.
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 -but changes to rows 100 and beyond never occur.
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.
When a UNIQUE constraint violation occurs, the pre-existing rows -that are causing the constraint violation are 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. -If a NOT NULL constraint violation occurs, the NULL value is replaced -by the default value for that column. If the column has no default -value, then the ABORT algorithm is used. If a CHECK constraint violation -occurs then the IGNORE algorithm is used.
- -When this conflict resolution strategy deletes rows in order to -satisfy a constraint, it does not invoke delete triggers on those -rows. This behavior might change in a future release.
-The algorithm specified in the OR clause of a INSERT or UPDATE -overrides any algorithm specified in a CREATE TABLE. -If no algorithm is specified anywhere, the ABORT algorithm is used.
-} - -Section REINDEX reindex - -Syntax {sql-statement} { - REINDEX