Working with SQLiteStudio / 29 / 2016 / Archive / Magazine / Home

Working with SQLiteStudio

Alexander Kirch, 123RF

Data Workshop

Harald Zisler , Jörg Luther

SQLiteStudio, a compact graphical tool, lets you complete everyday tasks relating to SQLite databases quickly and easily.

In many desktop scenarios, using a RDBMS server proves to be overkill and can even cause security problems in case of misconfiguration. If only one user with one application needs access to the data, the SQLite database gives you an option, without having to do without the benefits of the query language (see box "SQLite").

SQLite

SQLite is simply a 1MB program library that implements a relational database system. The library can be integrated directly into applications and extends them to include database functions; additional server software is not needed.

SQLite supports most SQL92 commands, including transactions, subqueries, views, triggers, and user-defined functions. However, it lacks functions for managing object permissions and a client-server architecture; the entire database is located in a single file.

Many well-known applications rely on SQLite because of its benefits, including the Mozilla Firefox, Google Chrome, and Apple Safari browsers; the Skype VoIP software; and mobile operating systems like Android, iOS, Symbian, and Windows phone. Additionally, Mac OS X uses SQLite.

SQLiteStudio [1] turns out to be a compact and useful tool, giving users the ability to easily accomplish many daily tasks related to SQLite databases. To discover how to set up the practical application quickly, refer to the "Installing SQLiteStudio" box.

Installing SQLiteStudio

Many distributions have SQLiteStudio in their software repositories; you can generally set up the program conveniently with the respective package manager.

However, SQLiteStudio is conspicuously absent from Ubuntu. SQLiteStudio is available via the project website [2] in the form of 32-bit or 64-bit binaries for Linux, Mac OS X, and Windows, as well as a source tarball.

Download the binary package, and then unpack it with:

tar xvf sqlstudio-X.Y.Z.tar.xz

where X.Y.Z is the version number – 3.0.7 at the time of writing – and SQLiteStudio is ready to use.

You can run it from within its own directory by typing

./sqlstudio

at the command line.

When you first start, SQLiteStudio prompts you for the desired localization. There are localizations for Chinese, French, Polish, Russian, and Slovenian, or you can leave the default value of American English . All the information in this article assumes you did the latter.

Creating a Database

To create a new database, launch SQLiteStudio and then click Database | Add a database or simply type Ctrl+O. In the dialog box that appears (Figure 1), choose SQLite 3 as the Database type . In File , click on the green plus sign to the right of the input box to create a data directory.

Figure 1: SQLiteStudio dialog for creating a database.

If the Generate automatically box is checked for Name (on the list) , the tool creates a database name from the directory name. If you keep the Permanent (keep it in configuration) box checked in Options , SQLiteStudio will remember this database in your next session.

Finally, click on Test connection . If the SQLite database is operational, you will see a green checkmark as confirmation. Clicking OK then quits the dialog.

Before you can create a table, you first need to connect to the database. To do so, select Database |Connect to the database , or press the icon with the two connectors on the far left. If multiple databases are available later on, you will need to select the required database below Databases on the left.

After successfully connecting, SQLiteStudio shows you the database in a tree structure on the left edge of the program window.

Creating Tables

The dialog for creating a database table can be opened via the Structure | Create a table menu item, by right-clicking on Tables in the tree display of the database on the left or via the table icon with the plus sign in the toolbar.

Enter the table name in the Table Name field – in our example, it is staff (see Table 1). To create a new table column, click on the appropriate icon in the toolbar of this window or press Ins . This opens the window for creating the column (Figure 2).

Table 1

Staff

name	surname	born
Joe	Miller	03/04/1976
Henry	Mitchell	01/02/1993
Laura	Smith	09/09/1990
The content of the snum field is created automatically.

Figure 2: After naming the table, create a table field.

In the resulting dialog, you can set all of the properties for the table column. In each table, one of the fields must have a primary or foreign key. In our example, the snum column is used as the primary key (Figure 3), and its value is automatically incremented (AutoIncrement ).

Figure 3: Use this dialog box to set up the primary key.

After creating all the required columns in the table – the structure of the sample table staff is shown in Figure 4 – press the green checkmark icon in the tool bar. This will show the SQL command that will create the table in your database. The toolbar also contains all the tools necessary for further editing of the table, including editing the properties of a column or deleting columns.