Sqlite

INTRODUCTION This is SQLite tutorial. It covers the SQLite database engine, sqlite3 command line tool and the SQL language covered by the database engine. It is an introductory tutorial for the beginners. It covers SQLite 3.0 version. SQLite is an embedded relational database engine. Its developers call it a self-contained, serverless, zero-configuration and transactional SQL database engine. It is very popular and there are hundreds of millions copies worldwide in use today. SQLite is used in Solaris 10 and Mac OS operating systems, iPhone or Skype. Qt4 library has a buit-in support for the SQLite as well as the Python or the PHP language. Many popular applications use SQLite internally such as Firefox, Google Chrome or Amarok. SQLite implements most of the SQL-92 standard for SQL. The SQLite engine is not a standalone process. Instead, it is statically or dynamically linked into the application. SQLite library has a small size. It could take less than 300 KiB. An SQLite database is a single ordinary disk file that can be located anywhere in the directory hierarchy. It is a cross platform file. It can be used on various operating systems, both 32 and 64 bit architectures. SQLite is created in C programming language and has bindings for many languages like C++, Java, C#, Python, Perl, Ruby, Visual Basic, Tcl and others. The source code of SQLite is in public domain. VENDORS IN THE PHILIPPINES Datatypes In SQLite Version 3 Most SQL database engines (every SQL database engine other than SQLite, as far as we know) uses static, rigid typing. With static typing, the datatype of a value is determined by its container - the particular column in which the value is stored. SQLite uses a more general dynamic type system. In SQLite, the datatype of a value is associated with the value itself, not with its container. The dynamic type system of SQLite is backwards compatible with the more common static type systems of other database engines in the sense that SQL statement that work on statically typed databases should work the same way in SQLite. However, the dynamic typing in SQLite allows it to do things which are not possible in traditional rigidly typed databases. 1.0 Storage Classes and Datatypes 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-16LE). * BLOB. The value is a blob of data, stored exactly as it was input. Note that a storage class is slightly more general than a datatype. The INTEGER storage class, for example, includes 6 different integer datatypes of different lengths. This makes a difference on disk. But as soon as INTEGER values are read off of disk and into memory for processing, they are converted to the most general datatype (8-byte signed integer). And so for the most part, "storage class" is indistinguishable from "datatype" and the two terms can be used interchangeably. Any column in an SQLite version 3 database, except an INTEGER PRIMARY KEY column, may be used to store a value of any storage class. All values in SQL statements, whether they are literals embedded in SQL statement text or parameters bound toprecompiled SQL statements have an implicit storage class. Under circumstances described below, the database engine may convert values between numeric storage classes (INTEGER and REAL) and TEXT during query execution. 1.1 Boolean Datatype SQLite does not have a separate Boolean storage class. Instead, Boolean values are stored as integers 0 (false) and 1 (true). 1.2 Date and Time Datatype SQLite does not have a storage class set aside for storing dates and/or times. Instead, the built-in Date And Time Functions of SQLite are capable of storing dates and times as TEXT, REAL, or INTEGER values: * TEXT as ISO8601 strings ("YYYY-MM-DD HH:MM:SS.SSS"). * REAL as Julian day numbers, the number of days since noon in Greenwich on November 24, 4714 B.C. according to the proleptic Gregorian calendar. * INTEGER as Unix Time, the number of seconds since 1970-01-01 00:00:00 UTC. Applications can chose to store dates and times in any of these formats and freely convert between formats using the built-in date and time functions. 2.0 Type Affinity In order to maximize compatibility between SQLite and other database engines, SQLite supports the concept of "type affinity" on columns. The type affinity of a column is the recommended type for data stored in that column. The important idea here is that the type is recommended, not required. Any column can still store any type of data. It is just that some columns, given the choice, will prefer to use one storage class over another. The preferred storage class for a column is called its "affinity". Each column in an SQLite 3 database is assigned one of the following type affinities: * TEXT * NUMERIC * INTEGER * REAL * NONE A column with TEXT affinity stores all data using storage classes NULL, TEXT or BLOB. If numerical data is inserted into a column with TEXT affinity it is converted into 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, the storage class of the text is converted to INTEGER or REAL (in order of preference) if such conversion is lossless and reversible. For conversions between TEXT and REAL storage classes, SQLite considers the conversion to be lossless and reversible if the first 15 significant decimal digits of the number are preserved. If the lossless conversion of TEXT to INTEGER or REAL is not possible then the value is stored using the TEXT storage class. No attempt is made to convert NULL or BLOB values. A string might look like a floating-point literal with a decimal point and/or exponent notation but as long as the value can be expressed as an integer, the NUMERIC affinity will convert it into an integer. Hence, the string '3.0e+5' is stored in a column with NUMERIC affinity as the integer 300000, not as the floating point value 300000.0. A column that uses INTEGER affinity behaves the same as a column with NUMERIC affinity. The difference between INTEGER and NUMERIC affinity is only evident in a CAST expression. A column with REAL affinity behaves like a column with NUMERIC affinity except that it forces integer values into floating point representation. (As an internal optimization, small floating point values with no fractional component and stored in columns with REAL affinity are written to disk as integers in order to take up less space and are automatically converted back into floating point as the value is read out. This optimization is completely invisible at the SQL level and can only be detected by examining the raw bits of the database file.) A column with affinity NONE does not prefer one storage class over another and no attempt is made to coerce data from one storage class into another. 2.1 Determination Of Column Affinity The affinity of a column is determined by the declared type of the column, according to the following rules in the order shown: 1. If the declared type contains the string "INT" then it is assigned INTEGER affinity. 2. If the declared type 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. 3. If the declared type for a column contains the string "BLOB" or if no type is specified then the column has affinity NONE. 4. If the declared type for a column contains any of the strings "REAL", "FLOA", or "DOUB" then the column has REAL affinity. 5. Otherwise, the affinity is NUMERIC. Note that the order of the rules for determining column affinity is important. A column whose declared type is "CHARINT" will match both rules 1 and 2 but the first rule takes precedence and so the column affinity will be INTEGER. 2.2 Affinity Name Examples The following table shows how many common datatype names from more traditional SQL implementations are converted into affinities by the five rules of the previous section. This table shows only a small subset of the datatype names that SQLite will accept. Note that numeric arguments in parentheses that following the type name (ex: "VARCHAR(255)") are ignored by SQLite - SQLite does not impose any length restrictions (other than the large globalSQLITE_MAX_LENGTH limit) on the length of strings, BLOBs or numeric values. Example Typenames From The CREATE TABLE Statement or CAST Expression | Resulting Affinity | Rule Used To Determine Affinity | INT INTEGER TINYINT SMALLINT MEDIUMINT BIGINT UNSIGNED BIG INT INT2 INT8 | INTEGER | 1 | CHARACTER(20) VARCHAR(255) VARYING CHARACTER(255) NCHAR(55) NATIVE CHARACTER(70) NVARCHAR(100) TEXT CLOB | TEXT | 2 | BLOB no datatype specified | NONE | 3 | REAL DOUBLE DOUBLE PRECISION FLOAT | REAL | 4 | NUMERIC DECIMAL(10,5) BOOLEAN DATE DATETIME | NUMERIC | 5 | Note that a declared type of "FLOATING POINT" would give INTEGER affinity, not REAL affinity, due to the "INT" at the end of "POINT". And the declared type of "STRING" has an affinity of NUMERIC, not TEXT. 2.3 Column Affinity Behavior Example The following SQL demonstrates how SQLite uses column affinity to do type conversions when values are inserted into a table. CREATE TABLE t1( t TEXT, -- text affinity by rule 2 nu NUMERIC, -- numeric affinity by rule 5 i INTEGER, -- integer affinity by rule 1 r REAL, -- real affinity by rule 4 no BLOB -- no affinity by rule 3 ); -- Values stored as TEXT, INTEGER, INTEGER, REAL, TEXT. INSERT INTO t1 VALUES('500.0', '500.0', '500.0', '500.0', '500.0'); SELECT typeof(t), typeof(nu), typeof(i), typeof(r), typeof(no) FROM t1; text|integer|integer|real|text -- Values stored as TEXT, INTEGER, INTEGER, REAL, REAL. DELETE FROM t1; INSERT INTO t1 VALUES(500.0, 500.0, 500.0, 500.0, 500.0); SELECT typeof(t), typeof(nu), typeof(i), typeof(r), typeof(no) FROM t1; text|integer|integer|real|real -- Values stored as TEXT, INTEGER, INTEGER, REAL, INTEGER. DELETE FROM t1; INSERT INTO t1 VALUES(500, 500, 500, 500, 500); SELECT typeof(t), typeof(nu), typeof(i), typeof(r), typeof(no) FROM t1; text|integer|integer|real|integer -- BLOBs are always stored as BLOBs regardless of column affinity. DELETE FROM t1; INSERT INTO t1 VALUES(x'0500', x'0500', x'0500', x'0500', x'0500'); SELECT typeof(t), typeof(nu), typeof(i), typeof(r), typeof(no) FROM t1; blob|blob|blob|blob|blob -- NULLs are also unaffected by affinity DELETE FROM t1; INSERT INTO t1 VALUES(NULL,NULL,NULL,NULL,NULL); SELECT typeof(t), typeof(nu), typeof(i), typeof(r), typeof(no) FROM t1; null|null|null|null|null 3.0 Comparison Expressions SQLite version 3 has the usual set of SQL comparison operators including "=", "==", "<", "<=", ">", ">=", "!=", "<>", "IN", "NOT IN", "BETWEEN", "IS", and "IS NOT", . 3.1 Sort Order The results of a comparison depend on the storage classes of the operands, 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 an appropriate collating sequence is used to determine the result. * When two BLOB values are compared, the result is determined using memcmp(). 3.2 Affinity Of Comparison Operands SQLite may attempt to convert values between the storage classes INTEGER, REAL, and/or TEXT before performing a comparison. Whether or not any conversions are attempted before the comparison takes place depends on the affinity of the operands. Operand affinity is determined by the following rules: * An expression that is a simple reference to a column value has the same affinity as the column. Note that if X and Y.Z are column names, then +X and +Y.Z are considered expressions for the purpose of determining affinity. * An expression of the form "CAST(expr AS type)" has an affinity that is the same as a column with a declared type of "type". * Otherwise, an expression has NONE affinity. 3.3 Type Conversions Prior To Comparison To "apply affinity" means to convert an operand to a particular storage class if and only if the conversion is lossless and reversible. Affinity is applied to operands of a comparison operator prior to the comparison according to the following rules in the order shown: * If one operand has INTEGER, REAL or NUMERIC affinity and the other operand as TEXT or NONE affinity then NUMERIC affinity is applied to other operand. * If one operand has TEXT affinity and the other has NONE affinity, then TEXT affinity is applied to the other operand. * Otherwise, no affinity is applied and both operands are compared as is. The expression "a BETWEEN b AND c" is treated as two separate binary comparisons "a >= b AND a <= c", even if that means different affinities are applied to 'a' in each of the comparisons. Datatype conversions in comparisons of the form "x IN (SELECT y ...)" are handled is if the comparison were really "x=y". The expression "a IN (x, y, z, ...)" is equivalent to "a = +x OR a = +y OR a = +z OR ...". In other words, the values to the right of the IN operator (the "x", "y", and "z" values in this example) are considered to have no affinity, even if they happen to be column values or CAST expressions. 3.4 Comparison Example CREATE TABLE t1( a TEXT, -- text affinity b NUMERIC, -- numeric affinity c BLOB, -- no affinity d -- no affinity ); -- Values will be stored as TEXT, INTEGER, TEXT, and INTEGER respectively INSERT INTO t1 VALUES('500', '500', '500', 500); SELECT typeof(a), typeof(b), typeof(c), typeof(d) FROM t1; text|integer|text|integer -- Because column "a" has text affinity, numeric values on the -- right-hand side of the comparisons are converted to text before -- the comparison occurs. SELECT a < 40, a < 60, a < 600 FROM t1; 0|1|1 -- Text affinity is applied to the right-hand operands but since -- they are already TEXT this is a no-op; no conversions occur. SELECT a < '40', a < '60', a < '600' FROM t1; 0|1|1 -- Column "b" has numeric affinity and so numeric affinity is applied -- to the operands on the right. Since the operands are already numeric, -- the application of affinity is a no-op; no conversions occur. All -- values are compared numerically. SELECT b < 40, b < 60, b < 600 FROM t1; 0|0|1 -- Numeric affinity is applied to operands on the right, converting them -- from text to integers. Then a numeric comparison occurs. SELECT b < '40', b < '60', b < '600' FROM t1; 0|0|1 -- No affinity conversions occur. Right-hand side values all have -- storage class INTEGER which are always less than the TEXT values -- on the left. SELECT c < 40, c < 60, c < 600 FROM t1; 0|0|0 -- No affinity conversions occur. Values are compared as TEXT. SELECT c < '40', c < '60', c < '600' FROM t1; 0|1|1 -- No affinity conversions occur. Right-hand side values all have -- storage class INTEGER which compare numerically with the INTEGER -- values on the left. SELECT d < 40, d < 60, d < 600 FROM t1; 0|0|1 -- No affinity conversions occur. INTEGER values on the left are -- always less than TEXT values on the right. SELECT d < '40', d < '60', d < '600' FROM t1; 1|1|1 All of the result in the example are the same if the comparisons are commuted - if expressions of the form "a<40" are rewritten as "40>a". 4.0 Operators All mathematical operators (+, -, *, /, %, <<, >>, &, and |) cast both operands to the NUMERIC storage class prior to being carried out. The cast is carried through even if it is lossy and irreversible. A NULL operand on a mathematical operator yields a NULL result. An operand on a mathematical operator that does not look in any way numeric and is not NULL is converted to 0 or 0.0. 5.0 Sorting, Grouping and Compound SELECTs When query results 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 in collating sequence 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. No affinity is applied to comparison operands for the implicit comparisons associated with UNION, INTERSECT, or EXCEPT - the values are compared as is. 6.0 Collating Sequences When SQLite compares two strings, it uses a collating sequence or collating function (two words for the same thing) to determine which string is greater or if the two strings are equal. SQLite has three built-in collating functions: BINARY, NOCASE, and RTRIM. * BINARY - Compares string data using memcmp(), regardless of text encoding. * NOCASE - The same as binary, except the 26 upper case characters of ASCII are folded to their lower case equivalents before the comparison is performed. Note that only ASCII characters are case folded. SQLite does not attempt to do full UTF case folding due to the size of the tables required. * RTRIM - The same as binary, except that trailing space characters are ignored. An application can register additional collating functions using the sqlite3_create_collation() interface. 6.1 Assigning Collating Sequences from SQL Every column of every table has an associated collating function. If no collating function is explicitly defined, then the collating function defaults to BINARY. The COLLATE clause of the column definition is used to define alternative collating functions for a column. The rules for determining which collating function to use for a binary comparison operator (=, <, >, <=, >=, !=, IS, and IS NOT) are as follows and in the order shown: 1. If either operand has an explicit collating function assignment using the postfix COLLATE operator, then the explicit collating function is used for comparison, with precedence to the collating function of the left operand. 2. If either operand is a column, then the collating function of that column is used with precedence to the left operand. For the purposes of the previous sentence, a column name preceded by one or more unary "+" operators is still considered a column name. 3. Otherwise, the BINARY collating function is used for comparison. An operand of a comparison is considered to have an explicit collating function assignment (rule 1 above) if any subexpression of the operand uses the postfix COLLATE operator. Thus, if a COLLATE operator is used anywhere in a comparision expression, the collating function defined by that operator is used for string comparison regardless of what table columns might be a part of that expression. If two or more COLLATE operator subexpressions appear anywhere in a comparison, the left most explicit collating function is used regardless of how deeply the COLLATE operators are nested in the expression and regardless of how the expression is parenthesized. The expression "x BETWEEN y and z" is logically equivalent to two comparisons "x >= y AND x <= z" and works with respect to collating functions as if it were two separate comparisons. The expression "x IN (SELECT y ...)" is handled in the same way as the expression "x = y" for the purposes of determining the collating sequence. The collating sequence used for expressions of the form "x IN (y, z, ...)" is the collating sequence of x. Terms of the ORDER BY clause that is part of a SELECT statement may be assigned a collating sequence using theCOLLATE operator, in which case the specified collating function is used for sorting. Otherwise, if the expression sorted by an ORDER BY clause is a column, then the collating sequence of the column is used to determine sort order. If the expression is not a column and has no COLLATE clause, then the BINARY collating sequence is used. TARGET CLIENTS 1.SQLiteClientSyncProvider alpha | September 1 2009 | License: Public Domain | SQLite client synchronization provider for Microsoft Sync Framework. Can be used as a replacement for the standard SQLCeClientSyncProvider. Note: This library depends on System.Data.Sqlite (http://sqlite.phxsoftware.com/).... | 2.dbExpress Driver for SQLite Version 1.00 | March 24 2011 | License: $129.95 | dbExpress is a database-independent layer that defines common interface to provide fast access to SQLite. For this database engine, dbExpress provides a driver as an independent library. As data-access layer is thin and simple, dbExpress provides high performance database connectivity and is easy to... | 3.Navicat for SQLite Version 9.1.9 | February 22 2011 | License: Free | Navicat for SQLite was designed to be a straight forward database administration and development tool for SQLite. It works with SQLite version 2 and 3 and supports most of the SQLite features including Trigger, Index, View, and so on. Features in Navicat are sophisticated enough to provide... | 4.Navicat for SQLite - The World Best SQLite Administrator Tool for Windows - Download Now! - 10.0.3 | September 14 2011 | License: $79.00 | Navicat is an ideal solution for SQLite administration and development. This is an all-inclusive SQLite front end provides a powerful graphical interface for databases management, development and maintenance. Easy installation and intuitive interface make it an irreplaceable tool for SQLite on the... | 5.Navicat for SQLite - The World Best SQLite GUI Admin Tool for Mac OS X - Download Now! - 10.0.0 | September 17 2011 | License: $69.00 | Navicat is an ideal solution for SQLite administration and development. This is an all-inclusive SQLite front end provides a powerful graphical interface for databases management, development and maintenance. Easy installation and intuitive interface make it an irreplaceable tool for SQLite on the... | 6.Navicat Essentials for SQLite 10.0.8 | January 4 2012 | License: Free | Navicat Essentials for SQLite is a professional application designed to manage SQLite databases. It provides basic and necessary features you will need to perform simple administration on a database. It is a fast, reliable and affordable. It supports the latest features including Trigger,... | 7.SQLite PHP Generator 10.8.0.7 | January 26 2011 | License: Freeware | SQLite PHP Generator is a freeware but powerful SQLite GUI frontend. SQLite PHP Generator allows you to generate high-quality SQLite PHP scripts for the selected tables, views and queries for the further working with these objects through the Internet. Save the huge costs of hiring a programmer,... | 8.SQLite Entity Framework prealpha | September 20 2008 | License: Public Domain | A utility to generate a set of entity and entity collection classes directly from an SQLite database so that developers can rapidly interact with a client side... | 9.Sqlite Query and Managemen alpha | October 9 2003 | License: | Sqlite Query and Management is a client application running on Windows(c) platform intended to manage visualy SQLITE (www.sqlite.org) databases via tools and the usage of SQL language. It is developed under C++ builder 3 and use the SynEdit... | 10.SQLiteDataset alpha | February 27 2005 | License: MIT License | C++ library for manipulations with SQLite Database Engine and MySQL Database. It is "client" interface and is convenient to handle few tables simultaneously, for querying, data navigation, records inserting and editing, transactions... | 11.FTP Client Screen Saver 1.0 | April 25 2008 | License: Freeware | FTP Client Screen Saver is a free and useful screensaver for users of the BitKinex FTP client FTP Client Screen Saver is a free and useful screensaver for users of the BitKinex FTP Client. It shows the tips for using this popular software. BitKinex integrates the functionality of an innovative FTP... | 12.NTP Client 1.0.0 | April 20 2008 | License: Freeware | TimeTools NTP clients software is a simple SNTP client. TimeTools NTP clients software is a simple Windows SNTP client that will synchronise time on any Windows XP/2000/NT/95/98/Me computer with a specified intranet or internet NTP time... | 13.Simple FTP Client 1.0.22 | November 12 2010 | License: Freeware | A FTP client program for Windows that provides an easy way to transfer files between your PC and remote computers Simple FTP Client is a free ftp software client with a simple interface that helps you easily download and upload files to a web server. Want a Simple FTP Client' You are looking at an... | 14.ClientIP Checker 1.0 | January 18 2011 | License: Freeware | With ClientIP Checker, you can see your IP to the clipboard and direct access to detailed information such as user agent client to copy ISP, OS, location, and browser settings. ClientIP Checker main features: Choose whether you want displayed to internal or external IP in the System Tray ... | 15.TS DOS Client | July 20 2006 | License: Shareware\ | The Terminal Services Client for DOS is the only RDP Client available for the DOS platform. Many features like multiple keyboard layouts, remote boot and so on are now available. Any 486 machine with a 1MB video card can be used as a client, connecting to a Windows 2000 Terminal Server | The Architecture Of SQLite Introduction 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. Interface Much of the public interface to the SQLite library is implemented by functions found in the main.c, legacy.c, and vdbeapi.c source files though some routines are scattered about in other files where they can have access to data structures with file scope. The sqlite3_get_table() routine is implemented in table.c. sqlite3_mprintf() is found in printf.c.sqlite3_complete() is in tokenize.c. The Tcl interface is implemented bytclsqlite.c. More information on the C interface to SQLite is available separately. To avoid name collisions with other software, all external symbols in the SQLite library begin with the prefix sqlite3. Those symbols that are intended for external use (in other words, those symbols which form the API for SQLite) begin with sqlite3_. Tokenizer When a string containing SQL statements is to be executed, the interface passes that string to the tokenizer. The job of the tokenizer is to break the original string up into tokens and pass those tokens one by one to the parser. The tokenizer is hand-coded in C in the file tokenize.c. Note that in this design, the tokenizer calls the parser. People who are familiar with YACC and BISON may be used to doing things the other way around -- having the parser call the tokenizer. The author of SQLite has done it both ways and finds things generally work out nicer for the tokenizer to call the parser. YACC has it backwards. Parser The parser is the piece that assigns meaning to tokens based on their context. The parser for SQLite is generated using the Lemon LALR(1) parser generator. Lemon does the same job as YACC/BISON, but it uses a different input syntax which is less error-prone. Lemon also generates a parser which is reentrant and thread-safe. And lemon defines the concept of a non-terminal destructor so that it does not leak memory when syntax errors are encountered. The source file that drives Lemon is found in parse.y. Because lemon is a program not normally found on development machines, the complete source code to lemon (just one C file) is included in the SQLite distribution in the "tool" subdirectory. Documentation on lemon is found in the "doc" subdirectory of the distribution. Code Generator After the parser assembles tokens into complete SQL statements, it calls the code generator to produce virtual machine code that will do the work that the SQL statements request. There are many files in the code generator:attach.c, auth.c, build.c, delete.c, expr.c, insert.c, pragma.c, select.c, trigger.c, update.c, vacuum.c andwhere.c. In these files is where most of the serious magic happens. expr.c handles code generation for expressions.where.c handles code generation for WHERE clauses on SELECT, UPDATE and DELETE statements. The files attach.c,delete.c, insert.c, select.c, trigger.c update.c, and vacuum.c handle the code generation for SQL statements with the same names. (Each of these files calls routines in expr.c and where.c as necessary.) All other SQL statements are coded out of build.c. The auth.c file implements the functionality of sqlite3_set_authorizer(). Virtual Machine The program generated by the code generator is executed by the virtual machine. Additional information about the virtual machine is available separately. To summarize, the virtual machine implements an abstract computing engine specifically designed to manipulate database files. The machine has a stack which is used for intermediate storage. Each instruction contains an opcode and up to three additional operands. The virtual machine itself is entirely contained in a single source file vdbe.c. The virtual machine also has its own header files: vdbe.h that defines an interface between the virtual machine and the rest of the SQLite library andvdbeInt.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 byvdbemem.c. SQLite implements SQL functions using callbacks to C-language routines. Even the built-in SQL functions are implemented this way. Most of the built-in SQL functions (ex: coalesce(), count(), substr(), and so forth) can be found in func.c. Date and time conversion functions are found in date.c. B-Tree An SQLite database is maintained on disk using a B-tree implementation found in the btree.c source file. A separate B-tree is used for each table and index in the database. All B-trees are stored in the same disk file. Details of the file format are recorded in a large comment at the beginning of btree.c. The interface to the B-tree subsystem is defined by the header file btree.h. Page Cache The B-tree module requests information from the disk in fixed-size chunks. The default chunk size is 1024 bytes but can vary between 512 and 65536 bytes. The page cache is responsible for reading, writing, and caching these chunks. The page cache also provides the rollback and atomic commit abstraction and takes care of locking of the database file. The B-tree driver requests particular pages from the page cache and notifies the page cache when it wants to modify pages or commit or rollback changes. The page cache handles all the messy details of making sure the requests are handled quickly, safely, and efficiently. The code to implement the page cache is contained in the single C source file pager.c. The interface to the page cache subsystem is defined by the header file pager.h. OS Interface In order to provide portability between POSIX and Win32 operating systems, SQLite uses an abstraction layer to interface with the operating system. The interface to the OS abstraction layer is defined in os.h. Each supported operating system has its own implementation: os_unix.c for Unix, os_win.c for Windows, and so forth. Each of these operating-specific implements typically has its own header file: os_unix.h, os_win.h, etc. Utilities Memory allocation and caseless string comparison routines are located in util.c. Symbol tables used by the parser are maintained by hash tables found in hash.c. The utf.c source file contains Unicode conversion subroutines. SQLite has its own private implementation of printf() (with some extensions) in printf.c and its own random number generator inrandom.c. Test Code If you count regression test scripts, more than half the total code base of SQLite is devoted to testing. There are many assert() statements in the main code files. In additional, the source files test1.c through test5.c together withmd5.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. Features Of SQLite * Transactions are atomic, consistent, isolated, and durable (ACID) even after system crashes and power failures. * Zero-configuration - no setup or administration needed. * Implements most of SQL92. (Features not supported) * A complete database is stored in a single cross-platform disk file. * Supports terabyte-sized databases and gigabyte-sized strings and blobs. (See limits.html.) * Small code footprint: less than 400KiB fully configured or less than 250KiB with optional features omitted. * Faster than popular client/server database engines for most common operations. * Simple, easy to use API. * Written in ANSI-C. TCL bindings included. Bindings for dozens of other languages available separately. * Well-commented source code with 100% branch test coverage. * Available as a single ANSI-C source-code file that you can easily drop into another project. * Self-contained: no external dependencies. * Cross-platform: Unix (Linux, Mac OS-X, Android, iOS) and Windows (Win32, WinCE, WinRT) are supported out of the box. Easy to port to other systems. * Sources are in the public domain. Use for any purpose. * Comes with a standalone command-line interface (CLI) client that can be used to administer SQLite databases. Suggested Uses For SQLite: * Application File Format. Rather than using fopen() to write XML or some proprietary format into disk files used by your application, use an SQLite database instead. You'll avoid having to write and troubleshoot a parser, your data will be more easily accessible and cross-platform, and your updates will be transactional. * Database For Gadgets. SQLite is popular choice for the database engine in cellphones, PDAs, MP3 players, set-top boxes, and other electronic gadgets. SQLite has a small code footprint, makes efficient use of memory, disk space, and disk bandwidth, is highly reliable, and requires no maintenance from a Database Administrator. * Website Database. Because it requires no configuration and stores information in ordinary disk files, SQLite is a popular choice as the database to back small to medium-sized websites. * Stand-in For An Enterprise RDBMS. SQLite is often used as a surrogate for an enterprise RDBMS for demonstration purposes or for testing. SQLite is fast and requires no setup, which takes a lot of the hassle out of testing and which makes demos perky and easy to launch. INSTALLATION OF SQLITE How to install SQLite3 By Mislav Marohnić on 22 Dec 2007 The default database for development in Rails is SQLite3, which I personally think is great. Any Ruby developer (using Windows or any other OS) should have SQLite installed on their development environments. It only takes a minute. Install SQLite3 on Windows 1. Go to SQLite3 download page, “Precompiled Binaries For Windows” section; 2. Download “sqlite-shell” and “sqlite-dll” archive files; 3. Unpack them in C:\WINDOWS\system32 folder (or any other that is in your PATH); 4. Install the sqlite3 Ruby gem. Install SQLite3 on Ubuntu Linux 1. Install the sqlite3 and libsqlite3-dev packages; 2. Install the sqlite3 gem. Install SQLite3 on Mac OS X On Mac OS Leopard or later, you don’t have to! It comes pre-installed. You can upgrade it, if you absolutely need to, with Homebrew. SCReENSHOTS SQLite Maestro screenshots SQL As Understood By SQLite SQLite 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 SQL keywords is also provided. The SQL language syntax is described by syntax diagrams. The following syntax documentation topics are available: * aggregate functions * ALTER TABLE * ANALYZE * ATTACH DATABASE * BEGIN TRANSACTION * comment * COMMIT TRANSACTION * core functions * CREATE INDEX * CREATE TABLE * CREATE TRIGGER * CREATE VIEW | * CREATE VIRTUAL TABLE * date and time functions * DELETE * DETACH DATABASE * DROP INDEX * DROP TABLE * DROP TRIGGER * DROP VIEW * END TRANSACTION * EXPLAIN * expression * INDEXED BY | * INSERT * keywords * ON CONFLICT clause * PRAGMA * REINDEX * RELEASE SAVEPOINT * REPLACE * ROLLBACK TRANSACTION * SAVEPOINT * SELECT * UPDATE * VACUUM | The routines sqlite3_prepare_v2(), sqlite3_prepare(), sqlite3_prepare16(), sqlite3_prepare16_v2(), sqlite3_exec(), andsqlite3_get_table() accept an SQL statement list (sql-stmt-list) which is a semicolon-separated list of statements. sql-stmt-list: Each SQL statement in the statement list is an instance of the following: sql-stmt: ------------------------------------------------- ------------------------------------------------- References * Allen, Grant; Owens, Mike (November 5, 2010). The Definitive Guide to SQLite (2nd ed.). Apress. p. 368. ISBN 1-4302-3225-0. * Kreibich, Jay A. (August 17, 2010). Using SQLite (1st ed.). O'Reilly Media. p. 528. ISBN 0-596-52118-9. * van der Lans, Rick F. (September 7, 2009). The SQL Guide to SQLite (1st ed.). lulu.com. p. 542. ISBN 0-557-07676-5. * Newman, Chris (November 9, 2004). SQLite (Developer's Library) (1st ed.). Sams. p. 336. ISBN 0-672-32685-X. [edit]Notes 1. ^ a b "SQLite Copyright". sqlite.org. Retrieved May 17, 2010. 2. ^ D. Richard Hipp (presenter) (May 31, 2006) (video). An Introduction to SQLite. Google Inc.. Event occurs at 00:01:14. Retrieved March 23, 2010. "[...] ess-kju-ellite [...]" 3. ^ D. Richard Hipp (presenter) (May 31, 2006). An Introduction to SQLite. Google Inc.. Event occurs at 00:48:15. Retrieved March 23, 2010. "[...] sequelite [...]" 4. ^ "Distinctive Features Of SQLite". SQLite. June 14, 2012. Retrieved August 7, 2012. 5. ^ "Most Widely Deployed SQL Database Estimates". Sqlite.org. Retrieved May 11, 2011. 6. ^ "The source code for SQLite is in the public domain". Sqlite.org. Retrieved May 11, 2011. 7. ^ a b Owens, Michael (2006). The Definitive Guide to SQLite.Apress. doi:10.1007/978-1-4302-0172-4_1. ISBN 978-1-59059-673-9. 8. ^ "Interview: Richard Hipp on UnQL, a New Query Language for Document Databases". InfoQ. August 4, 2011. Retrieved October 5, 2011. 9. ^ MDB: A Memory-Mapped Database and Backend for OpenLDAP, Howard Chu, [MDB: A Memory-Mapped Database and Backend for OpenLDAP LDAPCon 2011]. 10. ^ sqlightning source code. 11. ^ "SQL Features That SQLite Does Not Implement". SQLite. January 1, 2009. Retrieved October 14, 2009. 12. ^ "Frequently Asked Questions". SQLite. January 26, 2009. Retrieved February 7, 2009. 13. ^ "Write Ahead Logging in SQLite 3.7". SQLite. Retrieved September 3, 2011. "WAL provides more concurrency as readers do not block writers and a writer does not block readers. Reading and writing can proceed concurrently" 14. ^ "Web SQL Database". World Wide Web Consortium. December 22, 2009. Retrieved January 26, 2010. "all interested implementors have used the same SQL backend (Sqlite)" 15. ^ "Gears API". Database API. Google. Retrieved April 2, 2010. 16. ^ Coenraets, Christophe (January 19, 2010). "Using the SQLite database access API in Adobe AIR". Adobe. Retrieved April 2, 2010. 17. ^ "DOM Storage". Mozilla Developer Center. Retrieved February 9, 2010. 18. ^ "Case-insensitive matching of Unicode characters does not work". SQLite Frequently Asked Questions. Sqlite.org. Retrieved May 11, 2011. 19. ^ DBD::SQLite: Perl DBI Interface to SQLite 20. ^ PySQLite: Python bindings for SQLite 21. ^ SQLite/Ruby: Ruby bindings for SQLite 22. ^ JSPDO JavaScript database access abstraction API 23. ^http://system.data.sqlite.org/index.html/doc/trunk/www/index.wiki 24. ^ http://www.ch-werner.de/sqliteodbc/ 25. ^http://documentation.openoffice.org/HOW_TO/data_source/SQLite.pdf 26. ^ "sqlite — Sqlite Wrappers". SQLite. February 7, 2009. Retrieved February 7, 2009. 27. ^ "How SQLite Is Tested". SQLite. Retrieved September 12, 2009. 28. ^ "Fossil: Fossil Performance". Fossil-scm.org. August 23, 2009. Retrieved September 12, 2009. 29. ^ "Web Storage & SQL". W3C Web Applications Working Group mailing list. Retrieved February 10, 2011. 30. ^ "W3C WebSimpleDB API". W3C, www.w3.org. Retrieved February 10, 2011. 31. ^ "Databases". Django Documentation. Django Software Foundation. Retrieved 13 November 2012. 32. ^ "Drupal 7". 33. ^ "Havalite CMS". 34. ^ "Skype client using SQLite'". Mail-archive.com. August 28, 2007. Retrieved June 14, 2010. 35. ^ "Well-Known Users of SQLite". Sqlite.org. Retrieved June 14, 2010. 36. ^ "Possible virus infection by Xmarks'". virusremoval.pro. June 23, 2010. Retrieved July 28, 2010. 37. ^ "Well-known Users of SQLite". Sqlite.org. Retrieved June 14, 2010.