SQLite ˌɛskjuːɛlˈlaɪt (ˈsiːkwɛl.laɪt) is an
ACIDIn computer science, ACID is a set of properties that guarantee database transactions are processed reliably. In the context of databases, a single logical operation on the data is called a transaction...
-compliant
embeddedAn embedded database system is a database management system which is tightly integrated with an application software that requires access to stored data, such that the database system is “hidden” from the application’s end-user and requires little or no ongoing maintenance...
relational database management systemA relational database management system is a database management system that is based on the relational model as introduced by E. F. Codd. Most popular databases currently in use are based on the relational database model....
contained in a relatively small (~275
kBThe kilobyte is a multiple of the unit byte for digital information. Although the prefix kilo- means 1000, the term kilobyte and symbol KB have historically been used to refer to either 1024 bytes or 1000 bytes, dependent upon context, in the fields of computer science and information...
)
CC is a general-purpose computer programming language developed between 1969 and 1973 by Dennis Ritchie at the Bell Telephone Laboratories for use with the Unix operating system....
programming
libraryIn computer science, a library is a collection of resources used to develop software. These may include pre-written code and subroutines, classes, values or type specifications....
. The
source codeIn computer science, source code is text written using the format and syntax of the programming language that it is being written in. Such a language is specially designed to facilitate the work of computer programmers, who specify the actions to be performed by a computer mostly by writing source...
for SQLite is in the
public domainWorks are in the public domain if the intellectual property rights have expired, if the intellectual property rights are forfeited, or if they are not covered by intellectual property rights at all...
and implements most of the
SQLSQL is a programming language designed for managing data in relational database management systems ....
standard. In contrast to other database management systems, SQLite is not a separate process that is accessed from the client application, but an integral part of it.
SQLite uses a dynamically and weakly typed SQL syntax that does not guarantee the
domain integrityIntegrity constraints are used to ensure accuracy and consistency of data in a relational database. Data integrity is handled in a relational database through the concept of referential integrity...
. SQLite read operations can be multitasked. Writes can be done only one-at-a-time. It is a popular choice for local/client storage on
web browserA web browser is a software application for retrieving, presenting, and traversing information resources on the World Wide Web. An information resource is identified by a Uniform Resource Identifier and may be a web page, image, video, or other piece of content...
s. It has many bindings to programming languages. It is arguably the most widely deployed database engine, as it is used today by several widespread browsers, operating systems, embedded systems among others.
Design
Unlike client–server database management systems, the SQLite engine has no standalone
processIn computing, a process is an instance of a computer program that is being executed. It contains the program code and its current activity. Depending on the operating system , a process may be made up of multiple threads of execution that execute instructions concurrently.A computer program is a...
es with which the application program communicates. Instead, the SQLite library is linked in and thus becomes an integral part of the application program. The library can also be called dynamically. The application program uses SQLite's functionality through simple
function callsIn computer science, a subroutine is a portion of code within a larger program that performs a specific task and is relatively independent of the remaining code....
, which reduce
latencyLatency is a measure of time delay experienced in a system, the precise definition of which depends on the system and the time being measured. Latencies may have different meaning in different contexts.-Packet-switched networks:...
in database access: function calls within a single process are more efficient than
inter-process communicationIn computing, Inter-process communication is a set of methods for the exchange of data among multiple threads in one or more processes. Processes may be running on one or more computers connected by a network. IPC methods are divided into methods for message passing, synchronization, shared...
. SQLite stores the entire database (definitions, tables, indices, and the data itself) as a single cross-platform
fileA computer file is a block of arbitrary information, or resource for storing information, which is available to a computer program and is usually based on some kind of durable storage. A file is durable in the sense that it remains available for programs to use after the current program has finished...
on a host machine. It implements this simple design by
lockingIn computer science, a lock is a synchronization mechanism for enforcing limits on access to a resource in an environment where there are many threads of execution. Locks are one way of enforcing concurrency control policies.-Types:...
the entire database file during writing.
History
D. Richard HippDwayne Richard Hipp is the architect and primary author of SQLite as well as Fossil SCM. He and his wife, Ginger G. Wyrick, currently live and work in Charlotte, North Carolina. He also authored the Lemon Parser Generator and CVSTrac. CVSTrac became the inspiration for Trac. He is also a member of...
designed SQLite in the spring of 2000 while working for
General DynamicsGeneral Dynamics Corporation is a U.S. defense conglomerate formed by mergers and divestitures, and as of 2008 it is the fifth largest defense contractor in the world. Its headquarters are in West Falls Church , unincorporated Fairfax County, Virginia, in the Falls Church area.The company has...
on contract with the
United States NavyThe United States Navy is the naval warfare service branch of the United States Armed Forces and one of the seven uniformed services of the United States. The U.S. Navy is the largest in the world; its battle fleet tonnage is greater than that of the next 13 largest navies combined. The U.S...
. Hipp was designing software used on board
guided missile destroyerA guided missile destroyer is a destroyer designed to launch guided missiles. Many are also equipped to carry out anti-submarine, anti-air, and anti-surface operations. In the U.S...
ships, which were originally based on
HP-UXHP-UX is Hewlett-Packard's proprietary implementation of the Unix operating system, based on UNIX System V and first released in 1984...
with an IBM Informix database back-end. The design goals of SQLite were to allow the program to be operated without installing a database management system or
administrationA database administrator is a person responsible for the design, implementation, maintenance and repair of an organization's database. They are also known by the titles Database Coordinator or Database Programmer, and is closely related to the Database Analyst, Database Modeller, Programmer...
. In August 2000, version 1.0 of SQLite was released, based on gdbm (GNU Database Manager). SQLite 2.0 replaced gdbm with a custom
B-treeIn computer science, a B-tree is a tree data structure that keeps data sorted and allows searches, sequential access, insertions, and deletions in logarithmic time. The B-tree is a generalization of a binary search tree in that a node can have more than two children...
implementation, adding support for
transactionsA transaction comprises a unit of work performed within a database management system against a database, and treated in a coherent and reliable way independent of other transactions...
. SQLite 3.0, partially funded by America Online, added
internationalizationIn computing, internationalization and localization are means of adapting computer software to different languages, regional differences and technical requirements of a target market...
,
manifest typingIn computer science, manifest typing is when the software programmer explicitly identifies the type of each variable being declared. For example: if variable X is going to store integers then its type must be declared as integer....
, and other major improvements.
Future plans
In 2011 Richard Hipp announced his plans to add an
UnQLUnQL is a specification for a query language for NoSQL databases, developed by the creators of SQLite and CouchDB database management systems, based on SQL used in relational databases with the elements of JSON. It is built to query collections of documents with loosely defined fields...
interface to SQLite databases and to develop
UnQLite, an embeddable
document-oriented databaseA document-oriented database is a computer program designed for storing, retrieving, and managing document-oriented, or semi structured data, information...
.
Features
SQLite implements most of the
SQL-92SQL-92 was the third revision of the SQL database query language. Unlike SQL-89, it was a major revision of the standard. For all but a few minor incompatibilities, the SQL-89 standard is forwards-compatible with SQL-92....
standard for
SQLSQL is a programming language designed for managing data in relational database management systems ....
but it lacks some features. For example it has partial support for
triggersA database trigger is procedural code that is automatically executed in response to certain events on a particular table or view in a database. The trigger is mostly used for keeping the integrity of the information on the database...
, and it can't write to
viewIn database theory, a view consists of a stored query accessible as a virtual table in a relational database or a set of documents in a document-oriented database composed of the result set of a query or map and reduce functions...
s (however it supports INSTEAD OF triggers that provide this functionality). While it supports complex queries, it still has limited ALTER TABLE support, as it can't modify or delete columns.
SQLite uses an unusual
type systemA type system associates a type with each computed value. By examining the flow of these values, a type system attempts to ensure or prove that no type errors can occur...
for a SQL-compatible 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
stringIn formal languages, which are used in mathematical logic and theoretical computer science, a string is a finite sequence of symbols that are chosen from a set or alphabet....
into an
integerThe integers are formed by the natural numbers together with the negatives of the non-zero natural numbers .They are known as Positive and Negative Integers respectively...
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 products. A common criticism is that SQLite's type system lacks the data integrity mechanism provided by statically typed columns in other products. The SQLite web site describes a "strict affinity" mode, but this feature has not yet been added. However, it can be implemented with constraints like
CHECK(typeof(x)='integer').
Several computer processes or
threadsIn computer science, a thread of execution is the smallest unit of processing that can be scheduled by an operating system. The implementation of threads and processes differs from one operating system to another, but in most cases, a thread is contained inside a process...
may access the same database concurrently. 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 codeIn computer programming, error codes are enumerated messages that correspond to faults in a specific software application. They are typically used to identify faulty hardware, software, or incorrect user input in programming languages that lack exception handling, although they are sometimes also...
(or can automatically be retried until a configurable timeout expires). This concurrent access situation would change when dealing with temporary tables. This restriction is relaxed in version 3.7 when WAL is turned on enabling concurrent reads and writes.
A standalone program called
sqlite3 is provided that can be used to create a database, define tables within it, insert and change rows, run queries and manage a 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 is a popular choice for local/client SQL storage within a web browser and within a
rich internet application framework; most notably the leaders in this area (
Google GearsGears, formerly Google Gears, is software offered by Google that "enables more powerful web applications, by adding new features to your web browser. It allows some online files to be used offline"...
, Adobe AIR, and Firefox) embed SQLite.
SQLite full
UnicodeUnicode is a computing industry standard for the consistent encoding, representation and handling of text expressed in most of the world's writing systems...
support is optional.
SQLite also has
bindingsIn computing, a binding from a programming language to a library or OS service is an API providing that service in the language.Many software libraries are written in systems programming languages such as C or C++...
for a large number of
programming languageA programming language is an artificial language designed to communicate instructions to a machine, particularly a computer. Programming languages can be used to create programs that control the behavior of a machine and/or to express algorithms precisely....
s, including
BASICBASIC is a family of general-purpose, high-level programming languages whose design philosophy emphasizes ease of use - the name is an acronym from Beginner's All-purpose Symbolic Instruction Code....
,
CC is a general-purpose computer programming language developed between 1969 and 1973 by Dennis Ritchie at the Bell Telephone Laboratories for use with the Unix operating system....
,
C++C++ is a statically typed, free-form, multi-paradigm, compiled, general-purpose programming language. It is regarded as an intermediate-level language, as it comprises a combination of both high-level and low-level language features. It was developed by Bjarne Stroustrup starting in 1979 at Bell...
, Clipper//Harbour,
Common LispCommon Lisp, commonly abbreviated CL, is a dialect of the Lisp programming language, published in ANSI standard document ANSI INCITS 226-1994 , . From the ANSI Common Lisp standard the Common Lisp HyperSpec has been derived for use with web browsers...
, C#, Curl,
DThe D programming language is an object-oriented, imperative, multi-paradigm, system programming language created by Walter Bright of Digital Mars. It originated as a re-engineering of C++, but even though it is mainly influenced by that language, it is not a variant of C++...
,
DelphiEmbarcadero Delphi is an integrated development environment for console, desktop graphical, web, and mobile applications.Delphi's compilers use its own Object Pascal dialect of Pascal and generate native code for 32- and 64-bit Windows operating systems, as well as 32-bit Mac OS X and iOS...
,
Free PascalFree Pascal Compiler is a free Pascal and Object Pascal compiler.In addition to its own Object Pascal dialect, Free Pascal supports, to varying degrees, the dialects of several other compilers, including those of Turbo Pascal, Delphi, and some historical Macintosh compilers...
,
HaskellHaskell is a standardized, general-purpose purely functional programming language, with non-strict semantics and strong static typing. It is named after logician Haskell Curry. In Haskell, "a function is a first-class citizen" of the programming language. As a functional programming language, the...
,
JavaJava is a programming language originally developed by James Gosling at Sun Microsystems and released in 1995 as a core component of Sun Microsystems' Java platform. The language derives much of its syntax from C and C++ but has a simpler object model and fewer low-level facilities...
, Lua,
newLispnewLISP is an open source scripting language in the Lisp family of programming languages developed by Lutz Mueller and released under the GNU General Public License.-History:newLISP originated in 1991 and was originally developed on a Sun-4 workstation...
,
Objective-CObjective-C is a reflective, object-oriented programming language that adds Smalltalk-style messaging to the C programming language.Today, it is used primarily on Apple's Mac OS X and iOS: two environments derived from the OpenStep standard, though not compliant with it...
(on
Mac OS XMac OS X is a series of Unix-based operating systems and graphical user interfaces developed, marketed, and sold by Apple Inc. Since 2002, has been included with all new Macintosh computer systems...
and iOS), OCaml,
PerlPerl is a high-level, general-purpose, interpreted, dynamic programming language. Perl was originally developed by Larry Wall in 1987 as a general-purpose Unix scripting language to make report processing easier. Since then, it has undergone many changes and revisions and become widely popular...
,
PHPPHP is a general-purpose server-side scripting language originally designed for web development to produce dynamic web pages. For this purpose, PHP code is embedded into the HTML source document and interpreted by a web server with a PHP processor module, which generates the web page document...
, Pike,
PythonPython is a general-purpose, high-level programming language whose design philosophy emphasizes code readability. Python claims to "[combine] remarkable power with very clear syntax", and its standard library is large and comprehensive...
,
REBOLREBOL is a cross-platform data exchange language and a multi-paradigm dynamic programming language originally designed by Carl Sassenrath for network communications and distributed computing. The language and its official implementation, which is a proprietary freely redistributable software are...
,
RR is a programming language and software environment for statistical computing and graphics. The R language is widely used among statisticians for developing statistical software, and R is widely used for statistical software development and data analysis....
,
REALbasicRealbasic is the object-oriented dialect of the BASIC programming language used in Real Studio, a programming environment, developed and commercially marketed by Real Software, Inc of Austin, Texas for Mac OS X, Microsoft Windows, 32-bit x86 Linux and the web.- Language features :RB is a strongly...
,
RubyRuby is a dynamic, reflective, general-purpose object-oriented programming language that combines syntax inspired by Perl with Smalltalk-like features. Ruby originated in Japan during the mid-1990s and was first developed and designed by Yukihiro "Matz" Matsumoto...
, Scheme,
SmalltalkSmalltalk is an object-oriented, dynamically typed, reflective programming language. Smalltalk was created as the language to underpin the "new world" of computing exemplified by "human–computer symbiosis." It was designed and created in part for educational use, more so for constructionist...
,
TclTcl is a scripting language created by John Ousterhout. Originally "born out of frustration", according to the author, with programmers devising their own languages intended to be embedded into applications, Tcl gained acceptance on its own...
,
Visual BasicVisual Basic .NET , is an object-oriented computer programming language that can be viewed as an evolution of the classic Visual Basic , which is implemented on the .NET Framework...
, and
JavaScriptJavaScript is a prototype-based scripting language that is dynamic, weakly typed and has first-class functions. It is a multi-paradigm language, supporting object-oriented, imperative, and functional programming styles....
. There is also a
COMComponent Object Model is a binary-interface standard for software componentry introduced by Microsoft in 1993. It is used to enable interprocess communication and dynamic object creation in a large range of programming languages...
(
ActiveXActiveX is a framework for defining reusable software components in a programming language-independent way. Software applications can then be composed from one or more of these components in order to provide their functionality....
) wrapper making SQLite accessible on Windows to scripted languages such as
JScriptJScript is a scripting language based on the ECMAScript standard that is used in Microsoft's Internet Explorer.JScript is implemented as a Windows Script engine. This means that it can be "plugged in" to any application that supports Windows Script, such as Internet Explorer, Active Server Pages,...
and
VBScriptVBScript is an Active Scripting language developed by Microsoft that is modeled on Visual Basic. It is designed as a “lightweight” language with a fast interpreter for use in a wide variety of Microsoft environments...
. This adds database capabilities to
HTML ApplicationAn HTML Application is a Microsoft Windows program whose source code consists of HTML, Dynamic HTML, and one or more scripting languages supported by Internet Explorer, such as VBScript or JScript. The HTML is used to generate the user interface, and the scripting language is used for the program...
s (HTA).
SQLite has automated
regression testingRegression testing is any type of software testing that seeks to uncover new errors, or regressions, in existing functionality after changes have been made to a system, such as functional enhancements, patches or configuration changes....
prior to each release. Over 2 million tests are run as part of a release's verification. Starting with the August 10, 2009 release of SQLite 3.6.17, SQLite releases have 100% branch test coverage, one of the components of
code coverageCode coverage is a measure used in software testing. It describes the degree to which the source code of a program has been tested. It is a form of testing that inspects the code directly and is therefore a form of white box testing....
.
Development
SQLite development stores revisions of its source code in
FossilFossil is a distributed version control system, bug tracking system and wiki software server for use in software development created by D. Richard Hipp.- Features :...
, a distributed version control system that is itself built upon a SQLite database.
Adoption
- Mozilla Firefox
Mozilla Firefox is a free and open source web browser descended from the Mozilla Application Suite and managed by Mozilla Corporation. , Firefox is the second most widely used browser, with approximately 25% of worldwide usage share of web browsers...
and Mozilla ThunderbirdMozilla Thunderbird is a free, open source, cross-platform e-mail and news client developed by the Mozilla Foundation. The project strategy is modeled after Mozilla Firefox, a project aimed at creating a web browser...
store a variety of configuration data (bookmarks, cookies, contacts etc.) in internally managed SQLite databases, and even offer an add-on to manage SQLite databases.
- Skype
Skype is a software application that allows users to make voice and video calls and chat over the Internet. Calls to other users within the Skype service are free, while calls to both traditional landline telephones and mobile phones can be made for a fee using a debit-based user account system...
is another widely deployed application that uses SQLite.
- Adobe Systems
Adobe Systems Incorporated is an American computer software company founded in 1982 and headquartered in San Jose, California, United States...
uses SQLite as its file format in Adobe Photoshop Lightroom, a standard database in Adobe AIR, and internally within Adobe Reader.
- Ruby on Rails
Ruby on Rails, often shortened to Rails or RoR, is an open source web application framework for the Ruby programming language.-History:...
' default database management system is also SQLite.
- Xmarks, the bookmark sharing tool has also reported to use SQLite to manage the bookmarks for each user.
- The Opera
Opera is a web browser and Internet suite developed by Opera Software with over 200 million users worldwide. The browser handles common Internet-related tasks such as displaying web sites, sending and receiving e-mail messages, managing contacts, chatting on IRC, downloading files via BitTorrent,...
Internet suite and browser uses SQLite 3.6.23 for managing WebSQL databases. This is noted in opera:about, although without the mention of WebSQL (databases can be managed through opera:webdatabases)
- The Service Management Facility
Service Management Facility is a feature of the Solaris operating system that creates a supported, unified model for services and service management on each Solaris system and replaces init.d scripts. SMF introduces:...
, used for service management within the Solaris and OpenSolarisOpenSolaris was an open source computer operating system based on Solaris created by Sun Microsystems. It was also the name of the project initiated by Sun to build a developer and user community around the software...
operating systems, uses SQLite internally.
- Embedding SQLite in web browsers has resulted in adding SQLite to the HTML5 Web Storage standard and after discussion inside the W3C Web Applications Working Group the WebSimpleDB API proposal was developed.
Due to its small size, SQLite is well suited to embedded systems, and is also included in :
- Apple's iOS (where it is used for the SMS/MMS, Calendar, Call history and Contacts storage)
- Symbian OS
- Nokia's Maemo
Maemo is a software platform developed by the Maemo community for smartphones and Internet tablets. It is based on the Debian Linux distribution, but has no relation to it...
- Google's Android
- Google's Chrome browser
Google Chrome is a web browser developed by Google that uses the WebKit layout engine. It was first released as a beta version for Microsoft Windows on September 2, 2008, and the public stable release was on December 11, 2008. The name is derived from the graphical user interface frame, or...
- RIM's BlackBerry
BlackBerry is a line of mobile email and smartphone devices developed and designed by Canadian company Research In Motion since 1999.BlackBerry devices are smartphones, designed to function as personal digital assistants, portable media players, internet browsers, gaming devices, and much more...
- Linux Foundation's MeeGo
MeeGo is a Linux-based open source mobile operating system project. Primarily targeted at mobile devices and information appliances in the consumer electronics market, MeeGo is designed to act as an operating system for hardware platforms such as netbooks, entry-level desktops, nettops, tablet...
- Palm's webOS
However, it is also suitable for desktop operating systems; Apple adopted it as an option in
Mac OS XMac OS X is a series of Unix-based operating systems and graphical user interfaces developed, marketed, and sold by Apple Inc. Since 2002, has been included with all new Macintosh computer systems...
's
Core DataCore Data is part of the Cocoa API in Mac OS X first introduced with Mac OS X 10.4 Tiger and for iOS with iPhone SDK 3.0. It allows data organised by the relational entity-attribute model to be serialised into XML, binary, or SQLite stores. The data can be manipulated using higher level objects...
API from the original implementation in Mac OS X 10.4 onwards, and also for administration of videos and songs on the
iPhoneThe iPhone is a line of Internet and multimedia-enabled smartphones marketed by Apple Inc. The first iPhone was unveiled by Steve Jobs, then CEO of Apple, on January 9, 2007, and released on June 29, 2007...
.
See also
- Comparison of relational database management systems
The following tables compare general and technical information for a number of relational database management systems. Please see the individual products' articles for further information. This article is not all-inclusive or necessarily up to date...
- List of relational database management systems
- SQL Server Compact
Microsoft SQL Server Compact is a compact relational database produced by Microsoft for applications that run on mobile devices and desktops. Prior to the introduction of the desktop platform, it was known as SQL Server for Windows CE and SQL Server Mobile Edition...
- SQLite Manager
SQLite Manager is a SQLite database manager provided as a Firefox extension.By providing the software as a Firefox extension, SQLite Manager is available on many different platforms and trivially easy to install. As well as being provided as a Firefox extension it's also available for a few other...
- H2
H2 is a relational database management system written in Java. It can be embedded in Java applications or run in the client-server mode. The disk footprint is about 1 MB....
- Berkeley DB
Berkeley DB is a computer software library that provides a high-performance embedded database for key/value data. Berkeley DB is a programmatic software library written in C with API bindings for C++, PHP, Java, Perl, Python, Ruby, Tcl, Smalltalk, and most other programming languages...
External links