{infiniteZest}
// Articles. Tutorials. Utilities.
Home  |   Search  |   Login  
Categories Skip Navigation Links
New / All
AJAX
Apple
ASP.NET
.NET
Git
Google / Android
Python / IronPython
Miscellaneous
SQL Server
Creating SQLite databases from a GUI
Summary
This article discusses creating SQLite databases using GUI-based database managers. The example used here is SQLite Database Browser.
 
Table of Contents

Via non-GUI means

SQLite Database Browser

Creating a database

Figure 1. Creating Database from SQLite Database Browser

Creating Tables

Figure 2. Creating table and the columns in them

Database Structure

Figure 3. The database structure

Browsing Data

Figure 4. Browsing SQLite data

SQL Log

Code Listing 1. The Application SQL Log

Other Features

 

Via non-GUI means

The SQLite comes with sqlite3.exe command line program for Windows-based databases. The same program is available from the shell of Android for creating SQLite databases and then creating tables, indexes, etc in them.

For additional information, on the SQLite shells, refer to the following articles:

Creating databases from SQLite shell

Using SQLite from Shell in Android

Now for the GUI Database Managers. There are no SQLite GUI managers from the sqlite.org site. In addition to the sqlite3 command line program, there is another command line based analyzer program. However, there are plenty of commercial GUI DB Managers for SQLite.

And the following SQLite Database Browser is an open source program.

SQLite Database Browser

You can get SQLite Database Browser from Source Forge:

SQLite Database Browser

Eventhough the dev activity seems to have slowed down, this program seems to work ok for all the basic tasks. Following sections describe how to do the basic SQLite db-related tasks using SQLite Database Browser.

Creating a database

In SQLite, one normal OS file is used to store all the data belonging to a database. So, to create a database, use File-New Database menu.

Figure 1. Creating Database from SQLite Database Browser

Figure 1. Creating Database from SQLite Database Browser

In the above example, contactsext.db is created. So, you will see an OS file named contactsext.db file in the path you specified.

Creating Tables

SQLite Browser lets you create tables (and the columns in them) when you created a new database. Or, you can use the menu Edit-Create Table.

Figure 2. Creating table and the columns in them

Figure 2. Creating table and the columns in them

Here the available column types are: TEXT, NUMERIC, BLOB, and INTEGER PRIMARY KEY. In the above example, _id is an integer primary key. Android seems to use ‘_id’ (underscore followed by id) for the primary key ids.

Database Structure

In the ‘Database Structure’ tab, you can see the structure of the entire database.

Figure 3. The database structure

Figure 3. The database structure

The foreign keys are not supported (i.e. enforced) in SQLite. So, you won’t see any explicit linkage between those keys (for example, _id in Address and addrid in Contacts).

Browsing Data

You can add new records (or delete them) or browse the contents of the tables from the Browse Data tab. You can also search for the records. The features here are very rudimentary.

Figure 4. Browsing SQLite data

Figure 4. Browsing SQLite data

SQL Log

One interesting feature of SQLite Database Browser is the SQL that the application (or user) submits to the database engine in the backend.

Code Listing 1. The Application SQL Log




CREATE TABLE Contacts (_id INTEGER PRIMARY KEY, addrid NUMERIC, name TEXT);

SELECT name, sql FROM sqlite_master WHERE type=’table’ ORDER BY name;
PRAGMA TABLE_INFO(Contacts);
SELECT name, sql FROM sqlite_master WHERE type=’index’ ORDER BY name;
SELECT rowid, * FROM Contacts ORDER BY rowid;

CREATE TABLE Address (_id INTEGER PRIMARY KEY, city TEXT, country TEXT, line1 TEXT, line2 TEXT, region TEXT);
SELECT name, sql FROM sqlite_master WHERE type=’table’ ORDER BY name;
PRAGMA TABLE_INFO(Address);
PRAGMA TABLE_INFO(Contacts);



This SQL can come in very handy if you first create or play with the database creation from the GUI and then you want to apply these commands from within the code (for example, from the Java files in the Android application).

Other Features

This program also comes with the commands to create/delete indices (indexes), execute SQL, and other things like importing/exporting data and compacting the database. This is a simple utility; but it will do fine for simpler tasks (and, of course, it’s open source).

Bookmark and Share This

More Articles With Similar Tags
This article talks about various aspects of creating an SQLite database. It also uses Android SQLite shell as an example.
This article explains how to get pretty / nice output from the SQLite command line shell program.
icon-sqlite-source-new-project-wizard.jpg
This article shows how to set up a project and compile the SQLite shell and database engine source code with Visual Studio. Screenshots included.
icon-android-shell-main-method.jpg
This article talks about the SQLite packages available in Android SDK.
icon-android-sqlite-query.jpg
This article looks at using SQLite from the Android shell.
About  Contact  Privacy Policy  Site Map