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").

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.

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

./sqlstudio

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).

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.

Figure 4: Structure of the example table staff.

Manual Data Entry

To enter or edit data, click on Data . You can choose between Grid view (Figure 5) and Form view .

Figure 5: Data entry in Grid view.

This window also contains a toolbar with the following functions (from left to right) refresh view , insert a new row (plus sign), delete row (minus sign),commit changes (checkmark), and rollback changes (X).

Do not click on a column with AutoIncrement when entering data – in our case, do not click on snum . The field must contain the default value of NULL in italics; otherwise, the input will not work!

The following examples relate to a small database with a total of three tables for managing phone and fax numbers, email addresses, and so on. To do this, we need to create the tables, types , and links . The information necessary for filling is located:

• for staff in Table 1,
• for types in Figure 6 and Table 2, and
• for connections in Figure 7, Figure 8, and Table 3.

Figure 6: Structure of types.

Figure 7: Structure of table connections.

Figure 8: Creating connections.type with a foreign key.

In this example, foreign keys are used for the type of connection data in connections.type . To avoid the need to continually write out the words Phone, Fax, or Cell in the connection data, there is a shortcut for each category.

To make these shortcuts binding for the entries in connections.type , use the types.typeshort as a foreign key. Figure 8 shows how to create the field. As the Constraint , select the Foreign Key and then click Configure in the same line. This opens another dialog in which you select the table (types ) and the column typeshort (primary key of types ). In the Reactions section, you can make some additional fine adjustments if needed.

You the same with connections.snum to prevent orphaned entries in staff .

Next, populate the tables with sample data. When filling out the connection.type , SQLiteStudio suggests using the matching entries for the fields with the foreign keys if you double-click the box and select the arrow that then appears.

Sorting, Filtering, Searching

In the Grid view of a table, you can double-click on the header of the desired column to sort the data. To search, first select how the search will take place. To do this, press the small arrow next to the funnel symbol next to the search field in the Grid view toolbar. The following methods are available:

• Filter by text : SQLiteStudio searches all table fields for the search term. A "T" will appear in the filter icon.
• Filter by Regular Expression : This setting supports searching by pattern. An "R" appears in the filter icon.
• Filter by SQL expression : SQLiteStudio expects the value to look for in a column. An "S" in the filter icon reflects this setting.

Figure 9 shows a search for all table entries in connections that include a cellphone number. The search was carried out in SQL mode. Clicking on the round X symbol in the search window clears the display of search results.

Figure 9: A search in SQL mode for cellphone connection data.

Views

One convenient feature that relational databases provide is Views . You can use them to automate extensive, cross-table queries and evaluations such as totals or counts without having to retype them every time. Instead, you access the output product via the view, as if it were a table.

Rather than clicking your way around, you can use an appropriate SQL query to create views in SQLiteStudio (note that, if you do not possess SQL knowledge, you might get stuck at this point). In the following example, we create a phone list that lists the corresponding connection data for each person.

First, right-click on Views in the left column of the object and select the Create a View item from the context menu that appears. Next, enter a name for the view, and then enter the corresponding SQL statement in the Editor window (Figure 10).

Figure 10: You create a view using an appropriate SQL statement.

The SQL command in the example consists of listing the corresponding table columns, enumerating the matching tables, a where clause with the condition, and a sorting instruction. You enter the columns of the table in the form <table>.<column> . After the from keyword, list the tables used, separated by commas.

The where clause in the example contains two fields from different tables, which must have the same content (= ). For numerical comparisons you can use < (lesser) and > (greater) in your other projects. For a sorted list, add the statement order by with the corresponding table columns.

Listing 1 shows the complete SQL statement with syntax highlighting such as offered by SQLiteStudio. In Figure 11, you can see the results, which you can access in the Data tab, just like a table.

Figure 11: You can work with the result of the views just like any other table.

select staff.name, staff.name, connections.type, connections.info from staff, connections where staff.snum = connections.snum order by staff.name, staff.name, connections.type

To use the view outside of SQLiteStudio in a conventional SQL client, you need to add a statement to create the view before the select (create view directory as… ). You also need to close the SQL statement with a semicolon (; ).

Data Import

To add new values to the database, for example, for staff , you do not need to type in the corresponding data line by line in SQLiteStudio. For a more convenient approach, enter the data in a CSV file up front and then import into SQLiteStudio – this works fine with just a few mouse clicks.

For our example, you first create the file from Listing 2 using any text editor and save it as staff.csv . Notice the empty field at the beginning of each record: that's for the snum field. Then, in the menu of SQLiteStudio, select Tools | Import or click the Import icon (the four arrows moving toward one another).

NULL,Clark,Kent,24/12/1980,
NULL,Bruce,Banner,11/11/1981,

In the first dialog of the wizard that then opens, select the database and table for import, in this case test.staff . When you press Next > , you will see the following window (Figure 12), in which the source type CSV is the default.

Figure 12: The Import Wizard allows for detailed information on the data to be parsed.

Now specify the input file by clicking the small folder icon next to Input file . In Text encoding , select the appropriate character set, as well as in the Data source options and Field separator . Some CSV files also have column headers. In this case, check the box to ignore the header.

After you click Finish , SQLiteStudio then imports the values from the CSV file. Now looking at the staff table, you will see the new entries.

Data Export

SQLiteStudio exports data to CSV, PDF, HTML, JSON, and XML, or it can export SQL statements. To do this, either click directly on the Export icon (the arrows moving apart) or select Tools | Export in the menu.

In the first dialog of the wizard, you'll need to choose what you want to export: a whole database, a single table, or a query result. Then, confirm your choice by clicking on Next > . Usually, you will want to export a single table, such as staff . In this case, you specify the appropriate table in the next window; SQLiteStudio suggests the database and table currently in use. Below Options , you set the scope of the data export (table data, indexes, triggers) and then press Next > .

The following window is used to determine the output format and the target file. The points here depend on the output format that you selected; for CSV, they are similar to the Import function (Figure 12). The HTML export options are shown in Figure 13.

Figure 13: The selections for the export scope depend on the format used (HTML here).

The export to various formats such as CSV, JSON, or SQL works as expected. If you don't mess around with the parameters, you can also import into other programs such as LibreOffice or PostgreSQL. The stumbling block is always in the header, which needs to be considered when importing into the target application.

The HTML output is visually perfect (Figure 14) as is the PDF output.

Figure 14: The output for HTML export.

SQLiteStudio does not offer to output views in the menus; you need to manually request this with Export and select Query results . Again, you will need some basic SQL knowledge for this. The query that grabs data from our sample telephone directory looks something like this:

select * from "Telephone Directory";

The other query dialogs are identical to those for exporting databases or tables; the supported formats are also the same.

Manual SQL Queries

For more complex queries, there is an SQL editor, which you can access via Tools | Open SQL editor (Figure 15). After entering your instructions, you need to click the small blue arrow above Query or press F9 to run them.

Figure 15: The SQL Editor in SQLiteStudio allows manual queries that you can implement as a view where applicable.

In the upper input field, type in your query, and the result will be shown in the middle. You will find errors and other messages at the bottom in the status section. You can also save the queries by clicking on the floppy disk icon. To load a statement file created in this way click on the folder icon to the right of the floppy disk. SQLiteStudio does not automatically execute the loaded file with the instructions; again, you need to click on the blue arrow.

Optionally, you can create a view directly from a query. To do so, click on the appropriate button with the stylized lightning flash to the left of the floppy disc icon (Create view from query ).

Conclusions

SQLiteStudio provides users with a compact and useful tool, making it easy accomplish many things in the SQLite database. It is a fully open source project, as can easily be seen from the invitation from the developers to participate actively. If programming or translation work is not your area of expertise but you still want to help, you can donate to the project.