What is SQLite?

SQLiteManager is a database manager for SQLite databases. You can manage any SQLite database created on any platform with SQLiteManager.

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

SQLite engine is not a standalone process like other databases, you can link it statically or dynamically as per your requirement with your application. SQLite accesses its storage files directly.

Why SQLite?

• SQLite does not require a separate server process or system to operate (serverless).
• SQLite comes with zero-configuration, which means no setup or administration needed.
• A complete SQLite database is stored in a single cross-platform disk file.
• SQLite is very small and light weight, less than 400KiB fully configured or less than 250KiB with optional features omitted.
• SQLite is self-contained, which means no external dependencies.
• SQLite transactions are fully ACID-compliant, allowing safe access from multiple processes or threads.
• SQLite supports most of the query language features found in SQL92 (SQL2) standard.
• SQLite is written in ANSI-C and provides simple and easy-to-use API.
• SQLite is available on UNIX (Linux, Mac OS-X, Android, iOS) and Windows (Win32, WinCE, WinRT).

SQLite A Brief History

• 2000 – D. Richard Hipp designed SQLite for the purpose of no administration required for operating a program.
• 2000 – In August, SQLite 1.0 released with GNU Database Manager.
• 2011 – Hipp announced to add UNQl interface to SQLite DB and to develop UNQLite (Document oriented database).

SQLite Limitations

There are few unsupported features of SQL92 in SQLite which are listed in the following table.

SQLite Commands

The standard SQLite commands to interact with relational databases are similar to SQL. They are CREATE, SELECT, INSERT, UPDATE, DELETE and DROP. These commands can be classified into groups based on their operational nature −

DDL – Data Definition Language

DML – Data Manipulation Language

DQL – Data Query Language

SQLite is a software library that provides a relational database management system. The lite in SQLite means light weight in terms of setup, database administration, and required resource.

SQLite has the following noticeable features: self-contained, serverless, zero-configuration, transactional.

Serverless

Normally, an RDBMS such as MySQL, PostgreSQL, etc., requires a separate server process to operate. The applications that want to access the database server use TCP/IP protocol to send and receive requests. This is called client/server architecture.

The following diagram illustrates the RDBMS client/server architecture:

SQLite does NOT work this way.

SQLite does NOT require a server to run.

SQLite database is integrated with the application that accesses the database. The applications interact with the SQLite database read and write directly from the database files stored on disk.

The following diagram illustrates the SQLite server-less architecture:

Self-Contained

SQLite is self-contained means it requires minimal support from the operating system or external library. This makes SQLite usable in any environments especially in embedded devices like iPhones, Android phones, game consoles, handheld media players, etc.

SQLite is developed using ANSI-C. The source code is available as a big sqlite3.c and its header file sqlite3.h. If you want to develop an application that uses SQLite, you just need to drop these files into your project and compile it with your code.

Zero-configuration

Because of the serverless architecture, you don’t need to “install” SQLite before using it. There is no server process that needs to be configured, started, and stopped.

In addition, SQLite does not use any configuration files.

Transactional

All transactions in SQLite are fully ACID-compliant. It means all queries and changes are Atomic, Consistent, Isolated, and Durable.

In other words, all changes within a transaction take place completely or not at all even when an unexpected situation like application crash, power failure, or operating system crash occurs.

SQLite – Commands

This chapter will take you through simple and useful commands used by SQLite programmers. These commands are called SQLite dot commands and exception with these commands is that they should not be terminated by a semi-colon (;).

Let’s start with typing a simple sqlite3 command at command prompt which will provide you with SQLite command prompt where you will issue various SQLite commands.

$sqlite3
SQLite version 3.3.6
Enter ".help" for instructions
sqlite>

For a listing of the available dot commands, you can enter “.help” any time. For example −

sqlite>.help

The above command will display a list of various important SQLite dot commands, which are listed in the following table.

Let’s try .show command to see default setting for your SQLite command prompt.

sqlite>.show
     echo: off
  explain: off
  headers: off
     mode: column
nullvalue: ""
   output: stdout
separator: "|"
    width:
sqlite>

Make sure there is no space in between sqlite> prompt and dot command, otherwise it will not work.

Formatting Output

You can use the following sequence of dot commands to format your output.

sqlite>.header on
sqlite>.mode column
sqlite>.timer on
sqlite>

The above setting will produce the output in the following format.

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0
CPU Time: user 0.000000 sys 0.000000

The sqlite_master Table

The master table holds the key information about your database tables and it is called sqlite_master. You can see its schema as follows −

sqlite>.schema sqlite_master

This will produce the following result.

CREATE TABLE sqlite_master (
   type text,
   name text,
   tbl_name text,
   rootpage integer,
   sql text
);

Introduction to chinook SQLite sample database

We provide you with the SQLite sample database named chinook. The chinook sample database is a good database for practicing with SQL, especially SQLite.

The following database diagram illustrates the chinook database tables and their relationships.

Chinook sample database tables

There are 11 tables in the chinook sample database.

•  employees table stores employees data such as employee id, last name, first name, etc. It also has a field named ReportsTo to specify who reports to whom.
•  customers table stores customers data.
•  invoices & invoice_items tables: these two tables store invoice data. The invoices table stores invoice header data and the invoice_items table stores the invoice line items data.
•  artists table stores artists data. It is a simple table that contains only the artist id and name.
•  albums table stores data about a list of tracks. Each album belongs to one artist. However, one artist may have multiple albums.
•  media_types table stores media types such as MPEG audio and AAC audio files.
•  genres table stores music types such as rock, jazz, metal, etc.
•  tracks table stores the data of songs. Each track belongs to one album.
•  playlists & playlist_track tables: playlists table store data about playlists. Each playlist contains a list of tracks. Each track may belong to multiple playlists. The relationship between the playlists table and tracks table is many-to-many. The playlist_track table is used to reflect this relationship.

How To Download & Install SQLite Tools

Create database

Database structure

Execute test

Tables

SQLiteManager is released under the GNU/GPL terms.

Major Features of SQLite MANAGER

• Management of several databases (Creation, access or upload)
• Management of the attached databases
• Create, edit and delete tables and indexes.
• IInsert, edit, delete records in these tables
• Management of the View, possibilities of creating a view from a SELECT
• Management of Trigger
• Management of the users defined functions, these functions are in continuation usable in the requests manual, as in the form of insertion/modification of data
• Manual request and from file, it is possible of defined the format of the requests, sqlite or MySQL; a conversion is doing in order to directly import a MySQL base in SQLite.
• Importing of records from a formatted text file
• Export of the structure and the data
• Choice of several display skin
• Transactions are atomic, consistent, isolated, and durable (ACID) even after system crashes and power failures.
• Zero-configuration – no setup or administration needed.
• Full-featured SQL implementation with advanced capabilities like partial indexes, indexes on expressions, JSON, common table expressions, and window functions. (Omitted features)
• A complete database is stored in a single cross-platform disk file. Great for use as an application file format.
• Supports terabyte-sized databases and gigabyte-sized strings and blobs. (See limits.html.)
• Small code footprint: less than 600KiB fully configured or much less with optional features omitted.
• Simple, easy to use API.
• Fast: In some cases, SQLite is faster than direct filesystem I/O
• Written in ANSI-C. TCL bindings included. Bindings for dozens of other languages available separately.
• Well-commented source code with 100% branch test coverage.
• Available as a single ANSI-C source-code file that is easy to compile and hence is easy to add into a larger project.
• Self-contained: no external dependencies.
• Cross-platform: Android, *BSD, iOS, Linux, Mac, Solaris, VxWorks, and Windows (Win32, WinCE, WinRT) are supported out of the box. Easy to port to other systems.
• Sources are in the public domain. Use for any purpose.
• Comes with a standalone command-line interface (CLI) client that can be used to administer SQLite databases.

SQLite Manager Basic