SQLite
From Wikipedia, the free encyclopedia
| SQLite | |
 |
|
| Developer: | D. Richard Hipp |
|---|---|
| Latest release: | 3.3.14 / April 2, 2007 |
| OS: | Cross-platform |
| Use: | Development Library |
| License: | Public domain |
| Website: | SQLite |
SQLite is an ACID-compliant relational database management system contained in a relatively small C library. It is a public domain project created by D. Richard Hipp.
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. This reduces latency in database access because 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's author sells training, direct technical support contracts and add-ons such as compression and encryption.
Contents |
[edit] Features
The library implements most of the SQL-92 standard, including transactions that are atomic, consistent, isolated, and durable (ACID), triggers and most of the 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, 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). Some users see this as an innovation that makes the database much more useful, especially when bound to a dynamically typed scripting language. Other users see it as a major drawback, since the technique is not portable to other SQL databases. SQLite prior to version 3 made no attempt to coerce data to the column's type.
Several 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.
[edit] Language bindings
The SQLite website has a complete list of bindings at http://www.sqlite.org/cvstrac/wiki?p=SqliteWrappers
- The library can be used from C/C++ programs; bindings for Tcl and several other scripting languages are also available.
- SQLite is embedded in the REALbasic framework, enabling 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 on CPAN, DBD::SQLite, that is not an interface to SQLite, but rather includes the entire SQLite database engine in itself and doesn't need any additional software.
- There is also a Python module for SQLite, called pysqlite, which implements the Python DB API version 2.0 (PEP 249).[1] It is part of the standard library in Python 2.5.
- There is another Python module called APSW (Another Python SQLite Wrapper), which does not implement Python DB API, but rather binds directly with SQLite C interface. [2].
- There is a Ruby binding called SQLite-Ruby, available through RubyGems.
- PHP comes with SQLite included, starting with PHP 5. SQLite also works with PHP 4, but it was not incorporated into the default functionality.[3]
- There are bindings for Object Pascal (Borland Delphi)[4]
- Since Lazarus 0.9.8 and Free Pascal 2.0.0, SQLite is available for Pascal programmers.[5]
- Mac OS X v10.4 ships with SQLite built in (
sqlite3), and it's one of the data store options in Apple's Core Data API. AppleScript can store data in SQLite databases via Mac OS X 10.4's "Database Events" helper app. - PLT Scheme has SQLite support through multiple APIs, where elemental programming provides the SQLID (SQL Interface, Driver) interface.[6]
- There are Java bindings to SQLite native library provided as a JDBC driver.
- System.Data.SQLite is an ADO.NET 2.0 implementation of the SQlite database engine maintained by Robert Simpson.[7]
- mIRC SQLite is a SQLite wrapper for the popular Windows IRC client, mIRC.[8]
- D DBI contains a wrapper for SQLite 3 for the D programming language. It also contains an object-oriented interface. It is being maintained by the D DBI project.[9]
- QuickLite is a Cocoa (Objective-C) wrapper for SQLite.[10]
- altSQLite is a library for Revolution that incorporates SQLite support into the Revolution development environment.[11]
[edit] Products using SQLite
- POPFile uses SQLite as its default built-in database engine.
- SQLite is used by Kexi database environment, as a default built-in database engine.
- SQLite has been used in storing the index for a DVD set containing all the published issues of The New Yorker magazine.
- Yum, the package management tool for Fedora Core, has switched to SQLite and pysqlite for data storage and XML parsing since Fedora Core 4.
- The OpenOffice.org developers have considered embedding SQLite in the Base database module, but this depends heavily on the progress of sqlite-sdbc-driver,[12] which is still in alpha status at the moment. Currently they have decided[13] to use HSQLDB, but the option is still open if and when the sqlite-sdbc-driver becomes mature and reliable.[14]
- The Yahoo! Widget Engine supports SQLite as of version 4, allowing widgets to easily store data locally.
- Afghan Calendar Project, provides GPL Afghan calendars. They use SQLite for data storage and manipulation.[15]
- Open Outlook Connector, an open source Microsoft Outlook/MAPI connector has also made use of SQLite.[16]
- A number of Apple's offerings utilise SQLite, including Apple Mail and the RSS manager which ship with OS X. Apple's Aperture software stores image information as an SQLite database, using Core Data API atop.
- Monotone, a free distributed version control system, uses it.
- The new storage system implemented in the Mozilla Core (MozStorage) uses SQLite. This functionality is now present in public releases of Firefox, from Firefox 2.0 onwards[17]. A future version of Mozilla Firefox will also make use of this SQLite-based system to store bookmarks and browsing history, replacing the existing ad-hoc storage system. SQLite is also used or considered for Mozilla Calendar and Mozilla Thunderbird.
- PyKota, a free print quota and accounting solution, can use SQLite as its database backend.[18]
- musikCube, a fast, lightweight, opensource music management system, uses SQLite as its database backend.[19]
- Amarok music player uses SQLite to store collection data by default; in newer versions, it can also use external databases.
- XMMS2 uses SQLite for its built in Media Library. The media library consists of an index of metadata from the songs in the library, as well as a place to store playlists.
- SQLFilter, a plugin for OmniPeek, uses SQLite to index packets into a database that can then be searched using SQL. The matching packets are loaded directly into OmniPeek.
- HaXe uses SQLite as part of its embedded server.
- TVedia uses SQLite as Media Library database.
- The Banshee music player also uses SQLite to store audio library, track and playlist data.
- Solaris 10 uses SQLite as the backend for its Service Management Facility.
- MiniNova.org uses SQLite for serving its torrent files.
- Kerio MailServer stores mailbox indexes in malformed/compressed/encrypted sqlite2 files.
- Ptimemo uses a SQLite database to store its digital Post-its.
- REAL SQL Server, a multi-user database server from REALbasic, is based on the public domain SQLite database engine.
- SQLite is used by Jana-Server, a free Proxy / HTTP / E-mail / FTP / DNS server, to store mail index files.
- AdWords Editor uses SQLite database.
- Identipark, an international parking guidance system for malls, airports and transport terminals uses SQLite.
- F-spot uses an internal SQLite database to store information about photos.
- PunBB, a bulletin board system, can be set to use SQLite.
- Sokkit, a web server package, includes sqlite.
- Spiceworks, a free IT Management Software solution, uses SQLite as its default built-in database engine.
[edit] See also
- SQLite Database Browser, a graphical client to access SQLite databases
- List of relational database management systems
- Comparison of relational database management systems
[edit] Publications
- The Definitive Guide to SQLite, ISBN 1-59059-673-0
[edit] References
- ^ Python DB API version 2.0 (PEP 249)
- ^ APSW can be found here
- ^ See the manual and PECL info for details
- ^ http://www.itwriting.com/sqlitesimple.php
- ^ There is a tutorial at http://wiki.lazarus.freepascal.org/index.php/Lazarus_Database_Tutorial
- ^ http://www.elemental-programming.org/Bigloo%20SQLI.html
- ^ http://sqlite.phxsoftware.com/
- ^ http://www.mirc.net/projects.php?go=1155286814
- ^ http://www.dsource.org/projects/ddbi/wiki/SQLite3Subproject
- ^ http://www.webbotech.com/
- ^ http://http://www.altuit.com/webs/altuit2/altSQLiteCover/default.htm
- ^ http://dba.openoffice.org/drivers/sqlite/index.html
- ^ http://dba.openoffice.org/miscellaneous/dba20.html
- ^ Using SQLite Database with OpenOffice.org
- ^ http://www.nongnu.org/afghancalendar/
- ^ http://openconnector.org/
- ^ http://developer.mozilla.org/en/docs/Storage
- ^ http://www.pykota.com/
- ^ http://www.musikCube.com/
[edit] External links
- SQLite home page
- A tutorial on SQLite3
- Interview with Richard Hipp
- Webinar Introduction to SQLite (Google Video)
- Dynamic Relational Concepts
- Using SQLite Database with OpenOffice.org (via ODBC or sqlite-sdbc-driver)
- SQLite management tools by SQL Maestro Group (commercial)
- GalaXQL - Interactive SQL tutorial by Jari Komppa
- SQLite DB tool by Arke Systems LLC (commercial)
- SQLite Administrator for Windows 2000/XP and Vista (freeware)
