SQLite

SQLite is an ACID-compliant relational database management system, contained in a relatively small (~275 kiB) library written in C. SQLite is a public domain project created by Mr Richard Hipp.

Unlike client-server database management systems, the SQLite engine is not a separate process that the main program communicates with. Instead, the SQLite library is linked into the program, becoming an integral part of it. The program uses the functionality of SQLite through simple calls to subroutines and functions. This reduces latency in accessing the database, since function calls are more efficient than interprocess communication. The entire database (definitions, tables, indexes, and the data itself) are saved as a single standard file on the host machine. This simple design is achieved by locking the entire database file at the beginning of each transaction.

In its version 3, SQLite allows databases of up to 2 Terabytes in size, and also allows the inclusion of BLOB-type fields.

The author of SQLite offers training, support contracts, and additional features such as compression and encryption.

Features

The library implements most of the SQL-92 standard, including atomic database transactions, database consistency, isolation, and durability (ACID), triggers, and most complex queries.

SQLite uses an unusual type system. Instead of assigning a type to a column as in most SQL database systems, types are assigned to individual values. For example, you can insert a string into an integer column (although SQLite will try to convert the string to an integer in the first instance). Some users see this as an innovation that makes the database much more useful, especially when used from a dynamically typed scripting language. Other users see this as a big drawback, since the technique is not portable to other SQL databases. SQLite didn't try to transform the data to the column type until version 3.

Several processes or threads can access the same database without problems. Several read accesses can be served in parallel. A write access can only be served if no other access is being served concurrently. Otherwise, the write access fails, returning an error code (or it can be automatically retried until a configurable timeout expires). This concurrent access situation could change when you are working with temporary tables. However, a deadlock could occur due to multithreading.[1] Archived 2006-02-21 at the Wayback Machine This point was addressed in version 3.3.4, released on February 11, 2006.

There is a separate program called sqlite that can be used to query and manage SQLite database files. It also serves as an example for writing applications using the SQLite library.

Programming languages

• The library can be used from C/C++ programs, although links are available for Tcl and many other interpreted programming languages.
• SQLite is embedded in the REALbasic framework, making it possible for applications developed in REALbasic for Windows, Linux or Mac OS X to use the SQLite database.
• There is a DBI/DBD module for Perl available at CPAN, DBD::SQLite, is not an interface for SQLite, but includes the full SQLite engine itself so you don't need any additional software.
• Python includes support for SQLite natively from version 2.5 incorporated into the Standard Library as the sqlite3 module. For previous versions of Python, the module is not incorporated and must be installed (the name is PySQLite).
• There is another module for Visual Basic 6 called VBSqlite
• From Delphi you can use SQLite through ZeosLib free components.
• PHP includes SQLite, from version 5. SQLite also works with PHP 4 but is not included in it. For more details see the manual and PECL info.
• From Java you can access via the SQLite JDBC driver Archived on May 27, 2013 at Wayback Machine.
• From.NET you can access using the open source project System.Data.SQLite
• From Lazarus 0.9.8 and Free Pascal 2.0.0, SQLite is available for Pascal programmers. Tutorial: «Lazarus Database Tutorial, Lazarus and SQLite» (in English).
• Mac OS X v10.4 includes SQLite, and is one of the options in Apple's Core Data API. AppleScript can open, create, and manipulate SQLite database through Mac OS X 10.4's "Database Events" help application.
• BlitzMAX has an MOD that allows you to work with SQLite databases. For more details and download of the MOD see [2].
• Gambas database component (gb.db) supports SQLite in its versions 1, 2 and 3
• The programming language of video games Bennu has a SQlite mod available
• The programming language of scripting for Windows AutoIt v.3.x through DLL SQLite.dll.
• A Go (schedule language) can be added to third-party drivers. As mattn/go-sqlite3

Software that uses SQLite

SQLite is used in a wide variety of applications, highlighting the following:

• Adobe Photoshop Elements uses SQLite as a database engine in its latest version of the product (the 6.0) in replacement of Microsoft Access, used in previous versions.
• Clementine uses SQLite to save your default data collection.
• Kexi uses SQLite as an internal database engine by default.
• Mozilla Firefox uses SQLite to store, among others, cookies, favorites, history and valid network addresses.
• OpenOffice.org developers have considered including SQLite in the database model of Base, but this depends largely on the progress of sqlite-sdbc-driver, which is still in alpha status. They have now decided to use HSQLDB.
• Several Apple applications use SQLite, including Apple Mail and the RSS feeder that is distributed with Mac OS X. Apple's Aperture software saves the image information in a SQLite database, using the Core Data API.
• Opera Web browser uses SQLite for WebSQL database management.
• Skype is another large deployment application that uses SQLite.
• SQLFilter, a plugin for OmniPeek, uses SQLite to index packages in a database to be consulted by means of SQL.
• The New Yorker keeps the index for a DVD set containing all the numbers published by the magazine.
• SPIP can use SQLite for your database system (after 2007).
• XBMC Media Center (formerly known as "XBox Media Center") is a multi-platform, audio media player, video, photos, etc. free code (open source) while a entertainment center. Use SQLite to manage the music bookstores, video and photos, playlists and bookmarks among other minor utilities.

Due to its small size, SQLite is well suited for embedded systems, and is also included in:

• Android
• BlackBerry
• Windows Phone 8
• Google Chrome
• iOS
• Firefox OS
• Maemo
• MeeGo
• Symbian OS
• web sites

Posts

• Owens, Mike (2006). The Definitive Guide to SQLite. Apress. ISBN 1-59059-673-0.
• Kreibich, Jay A. (2010). Using SQLite. O'Reilly Media. ISBN 978-0596521189.