SQLite – The World’s Most Prevalent RDBMS – Part 2


Warning: Illegal string offset 'filter' in /var/sites/t/theproactiveprogrammer.com/public_html/wp-includes/taxonomy.php on line 1409

Last week’s provided an introduction to SQLite, a useful and lightweight RDBMS which is in fact the most widely deployed database system in the world.

This week we will demonstrate the creation of a database in SQLite. To do this we will use the SQLite Manager add-on for Firefox, which is essentially a simple IDE.

Creating a Database

On opening SQLite Manager you are presented with the following screen.

SQLite 1

The layout is similar to SQL Server Management Studio, in that the leftmost tab is where you can select database objects, and on the right you execute SQL and view results. For now we have no database so both tabs are blank.

We will create a simple database based on the famous sample Northwind database.

To create a new database, click Database > New Database and enter ‘Northwind’ for the database name.

SQLite 2

Notice the message here: .sqlite will be automatically appended to the name. Remember, your sqlite database will exist as a single file, which will have the .sqlite extension.

After selecting a location for your database file, an empty database will be created. Well – almost empty. It contains one read-only table named sqlite_master, which defines the schema of the database. This table can be queried, for example, to obtain a list of all tables.

SQLite 3

Creating Tables and Data

Now we will create a couple of tables from the Northwind database schema: Customers and Orders.

Click on the ‘Execute SQL’ tab, and paste the following SQL into the editor*:

The Customers table on Northwind also has a few indexes, so run the following:

And finally to insert the Customer data, paste and run the following:

We are only inserting data for 10 customers here. For a SQLite script which inserts the full Northwind dataset for Customers and Orders, click here.

In order to convert the Northwind SQL Server SQL so it would run in SQLite I had to perform a few Find and Replace executions (using Notepad++). For example, INSERT had to be replaced with INSERT INTO, a semicolon was added to the end of each line, and GO statements were removed.

Incidentally, although the SQL Server script doesn’t work in SQLite, the SQLite script does work in SQL Server. The best way to learn what exactly does and doesn’t work in SQLite, assuming you are familiar with SQL, is by trial and error.

Here we have been running SQL statements to create objects and insert data, however you can also use Windows Forms as an alternative. For try right-clicking on Tables in the object tree, or clicking Browse & Search > Add to add a record.

Now let’s repeat the process for the Orders table and data. First, to create the table:

Notice how we specify the foreign key to enforce the Customer to Order relationship.

Now we will create the indexes and insert some data:

Querying Data

Now we have some data, we can execute queries using standard SQL syntax:

SQLite 4

To reiterate, the best way to learn about SQLite is to give it a try. It is so lightweight that you can be up and running in next to no time.

For a SQLite script which inserts the full Customer and Order dataset from the Northwind database, click here.

Share Button