1.2. Introduction to SQLite

This tutorial helps you understand what SQLite is, how it differs from SQL, why it is needed, and how its application database is handled.

SQLite is a software library that implements a self-sufficient, serverless, zero-configuration, transactional SQL database engine. SQLite is one of the fastest growing database engines, which is an increase in popularity, regardless of its size. The SQLite source code is not subject to copyright restrictions.

1.2.1. What is SQLite? ¶

SQLite is an in-process library that implements a self-sufficient, serverless, zero-configured, transactional SQL database engine. It is a zero-configuration database, which means that, unlike other databases, you do not need to configure it in the system.

Like other databases, the SQLite engine is not a separate process and can be connected statically or dynamically according to the needs of the application. SQLite directly accesses its stored files.

1.2.2. Why use SQLite? ¶

A system that does not require a separate server process or operation (serverless).
SQLite does not need to be configured, which means that it does not require installation or administration.
A complete SQLite database is stored in a single cross-platform disk file.
SQLite is very small and lightweight, smaller than 400KiB when fully configured, and less than 250KiB when omitting optional feature configurations.
SQLite is self-sufficient, which means that no external dependencies are required.
SQLite transactions are fully ACID compatible, allowing safe access from multiple processes or threads.
SQLite supports the functionality of most query languages of the SQL92 (SQL2) standard.
SQLite is written in ANSI-C and provides a simple and easy-to-use API.
SQLite can be run in UNIX (Linux, Mac OS-X, Android, iOS) and Windows (Win32, WinCE, WinRT).

1.2.3. History ¶

2000-D. Richard Hipp designed SQLite to operate programs without management.
2000-in August, SQLite1.0 released the GNU Database Manager (GNU Database Manager).
2011-Hipp announced the addition of a UNQl interface to SQLite DB and the development of UNQLite (a document-oriented database).

1.2.4. Limitations of SQLite ¶

In SQLite, the features that SQL92 does not support are as follows:

Characteristics	Description
RIGHT OUTER JOIN	Only LEFT OUTER JOIN is implemented.
FULL OUTER JOIN	Only LEFT OUTER JOIN is implemented.
ALTER TABLE	ADD COLUMN variants commands for RENAME TABLE and ALTER TABLE are supported, but DROP COLUMN, ALTER COLUMN and ADD CONSTRAINT are not supported.
Trigger support	FOR EACH ROW triggers are supported, but FOR EACH STATEMENT triggers are not.
VIEWs	In SQLite, views are read-only. You cannot execute DELETE, INSERT, or UPDATE statements on a view.
GRANT and REVOKE	The only access permissions that can be applied are the normal file access permissions of the underlying operating system.

1.2.5. SQLite command ¶

The standard SQLite commands that interact with relational databases are similar to SQL. Commands include CREATE, SELECT, INSERT, UPDATE, DELETE, and DROP. These commands can be divided into the following categories based on the nature of their operations:

DDL-data definition language ¶

Command	Description
CREATE	Create a new table, a view of a table, or other objects in the database.
ALTER	Modify an existing database object in the database, such as a table.
DROP	Delete the entire table, or the view of the table, or other objects in the database.

DML-data manipulation language ¶

Command	Description
INSERT	Create a record.
UPDATE	Modify the record.
DELETE	Delete the record.

DQL-data query language ¶

Command	Description
SELECT	Retrieve some records from one or more tables.