SQLite, introduction

SQL stands for Structured Query Language. SQL allows working with relational databases.

Many SQL database systems have their own dialect, but a large number of SQL statements are the same for all of them.

SQLite is for embedded (non-server) databases, developed by SQLite Consortium. It reads and writes directly to disk files.

SQLite is the most widely deployed database in the world, with many millions of installations. You can find it on Windows, on all iOS and Android mobile devices and on Mac OS desktops. It is used by The United States Library of Congress (a recommended storage format for preservation of digital content), Firefox, Skype, Google, McAfee anti-virus, and many more.

Storage

A complete SQLite database with multiple tables, indices, and so on, is contained in a single disk file.

SQLite does not have any file naming requirements and thus the database file can have any custom file suffix, for example .sqlite, .db,...

Field types, aka "storage classes":

• INTEGER: for integers
• REAL: for floating point values
• TEXT: for text
• BLOB: for "blobs" of data, stored exactly as they were input.

There is no separate Boolean storage class. Instead, Boolean values are stored as integers 0 (false) and 1 (true).
There is no storage class for dates and/or times. Instead, dates and times are stored as TEXT, REAL, or INTEGER values.

Information held in an SQLite database file is easily accessible using commonly available open-source tools, such as DB Browser for SQLite (more info: sqlitebrowser.org)

SQLite and Delphi

You find the necessary Delphi components on the FireDAC tab of the component palette.

The advantage of using FireDAC is that it is fairly easy to change to a different type of database, such MSAccess or InterBase, without changing your program too much.

Tutorial project: Geo

We'll show you how to set up and program a Database Management System (DBMS) for maintaining a table with countries and some of their properties.