SQLite

From Encoresoup - The Ultimate Guide to Free/Open Source Software

SQLite is an ACID-compliant relational database management system contained in a relatively small (~500kB) C programming library.

Unlike client-server database management systems, the SQLite engine is not a standalone process with which the program communicates. Instead, the SQLite library is linked in and thus becomes an integral part of the program. The program uses SQLite's functionality through simple function calls, which reduces latency in database access as function calls are more efficient than inter-process communication. The entire database (definitions, tables, indices, and the data itself) is stored as a single cross-platform file on a host machine. This simple design is achieved by locking the entire database file at the beginning of a transaction.

SQLite was created by D. Richard Hipp, who sells training, direct technical support contracts and add-ons such as compression and encryption. The source code for SQLite is in the public domain.

Contents 1 Features 2 High-profile deployment 3 See also 4 Further reading 5 References 6 External links

[edit] Features

SQLite implements most of the SQL-92 standard for SQL. For example it has partial support for triggers and supports most complex queries, but silently ignores referential integrity constraints (foreign key constraints).^[1]

SQLite uses an unusual type system for an SQL DBMS. Instead of assigning a type to a column as in most SQL database systems, types are assigned to individual values; in language terms it is dynamically typed. Moreover, it is weakly typed in some of the same ways that Perl is: one can insert a string into an integer column (although SQLite will try to convert the string to an integer first, if the column's preferred type is integer). This adds flexibility to columns, especially when bound to a dynamically typed scripting language. However, the technique is not portable to other SQL databases. The inability to have strictly typed columns, as in typical databases, is a common criticism. The SQLite web site describes a "strict affinity" mode, but this feature has not yet been added.^[2]

Several computer processes or threads may access the same database without problems. Several read accesses can be satisfied in parallel. A write access can only be satisfied if no other accesses are currently being serviced, otherwise the write access fails with an error code (or can automatically be retried until a configurable timeout expires). This concurrent access situation would change when dealing with temporary tables.

A standalone program called sqlite3 is provided which can be used to create a database, define tables within it, insert and change rows, run queries and manage an SQLite database file. This program is a single executable file on the host machine. It also serves as an example for writing applications that use the SQLite library.

SQLite also has bindings for a large number of programming languages, including BASIC, C, C++, Common Lisp, Java, C#, Delphi, Curl, Lua, Tcl, R, PHP, Perl, Ruby, Objective-C (on Mac OS X), Python, newLisp and Smalltalk. There is also a COM (ActiveX) wrapper making SQLite accessible on Windows to scripted languages such as Javascript and VBScript. This adds database capabilities to HTML Applications (HTA).^[3]

[edit] High-profile deployment

SQLite's developers present a case for SQLite being the most widely deployed SQL database based on some of the more widely-deployed products known to be using SQLite.^[4] These include:

• 125 million copies in Mozilla Firefox.
• 20 million Mac OS X computers, each of which contains multiple copies of SQLite. (In Mac OS X v10.4 Apple introduced SQLite as a persistence layer of the Core Data API.)
• 300 million downloads of Skype. (The Skype service has 100 million registered users.)
• Nokia phones with Symbian OS version 9.4 or later. (The first one is Nokia 5800)
• Every iPhone and iPod touch.
• Google Android phones like the T-Mobile G1

[edit] See also

• List of relational database management systems
• Comparison of relational database management systems
• SQLite Manager
• SQLPro SQL Client

[edit] Further reading

• Owens, Michael (2006). The Definitive Guide to SQLite. Apress. ISBN 978-1-59059-673-9. 
• SQLite Tutorial An article exploring the power and simplicity of SQLite.

[edit] References

[edit] External links

• SQLite home page
• Linux Format interview with Richard Hipp
• Introduction to SQLite at Google Video
• Guardian interview with Richard Hipp
• FLOSS Weekly audio interview with Richard Hipp

v • d • e Database management systems
Database models · Database normalization · Database storage · Distributed DBMS · Referential integrity · Relational algebra · Relational calculus · Relational database · Relational DBMS · Relational model · Transaction processing
Concepts	Database · ACID · Null · Candidate key · Foreign key · Primary key · Superkey · Surrogate key
Objects	Trigger · View · Table · Cursor · Log · Transaction · Index · Stored procedure · Partition
SQL	Select · Insert · Update · Merge · Delete · Join · Union · Create · Drop · Begin work · Commit · Rollback · Truncate · Alter
Implementations	Relational · Flat file · Deductive · Dimensional · Hierarchical · Network  · Document-oriented · Object-oriented · Object-relational · Temporal · XML data stores
Components	Concurrency control · Data dictionary · JDBC · ODBC · Query language · Query optimizer · Query plan
Database products: Object-oriented (comparison) · Relational (comparison)  · Document-oriented