Knowledge – Managed

Whereas data records are typically quite manageable and simply structured, the search queries needed to manage them are quite different. Anyone who has ever formulated SQL queries manually will be familiar with this phenomenon. Management systems can help users access existing databases, but they differ considerably in terms of their options and functionality.

For smaller companies, several free software solutions make using and managing even complex databases easier. To ensure a database application's efficiency, as well as acceptance among non-IT employees, the decisive factor is how easily it can be operated in front ends. Above all, this includes how easily users can create queries that give them quick access to the data.

In this article, I look at several free front ends: Base [1], Glom [2], Kexi [3], MySQL Workbench [4], and Valentina Studio [5]. In particular, I examine how well the software helps end users create databases and queries.

Base

Base [1] is the integrated database system in LibreOffice [6] and OpenOffice [7]. It is not uncommon for Base to end up automatically on your drive when you install these office suites. If not, you can usually install manually from the package sources.

When you start Base, a wizard prompts you to select whether you want to create a new database file or load an existing one. The wizard also gives you access to external databases on request. The wizard supports a variety of servers, including Adabas, MySQL, and Oracle's JDBC, as well as veterans like dBase, which dates back to the good old days of DOS.

To access these external databases, you might need additional drivers. Look for the appropriate driver packages in the package sources.

The Base main window opens with a pane on the left displaying various options. In four steps, you can transform unstructured data into a database with queries, forms, and reports.

After a click on the Tables button, you first create a database table. It should have a structure with data fields and types. Base again offers help through a wizard (Figure 1), but users can also create tables manually.

Figure 1: Base offers a wizard for creating a database and for other tasks.

You then enter data records in the table and close the table when you are done. There is no need to save, because Base saves records immediately after input. The table name then appears at bottom center in the main window. Double-clicking on the table name opens the table view again.

To create a query, click on the second button, Queries , on the left in the main Base window. The options on the right let you use a wizard or a DIY approach to create the queries.

If you opt for the DIY approach, Base displays a pop-up window in which you first select the database. The database fields then appear in a list view at the top of the query window.

Double-click the data fields to select the ones you need. The values appear at the bottom of the table. Press the Run Query button to retrieve the data records and visualize them in a newly opened window segment below the buttonbar.

A click on the Design View On/Off button displays the corresponding SQL command. The fields can be edited, so you can adapt the SQL command to suit your needs before updating the records by pressing the Run Query button.

If you only want to apply certain selection criteria to the database, enter the criteria in the Criterion row of the table view.

Caution is advised if you want to use OR or AND. If you use these links in the SQL view and then switch to the Design View, Base unnecessarily duplicates the parameters in the criteria line. On switching back to the SQL command view, the program automatically inserts additional duplicates of the link commands, which quickly makes the SQL command code unclear.

Although this bug in Base has been known for several years, it has not been fixed in the current versions.

Base lets you create forms to simplify queries and manage data entry. Select the Forms option in the main window on the left. This again calls a wizard or lets you create a form manually.

The wizard prompts you for the data source. Decide whether a table or a query will serve as a source for the form. You can edit the form and use templates to arrange the data fields, adjust their labels, and define colors and borders.

After you create the first form, be sure to save it. In the main window, you will see the form with the data records it contains. To edit the form, right-click on the form file and select Open in Design Mode from the context menu.

You can access the individual setting dialogs in the Form Navigator, which you will find in the buttonbar at the bottom (Figure 2).

Figure 2: Settings for forms can be configured in the Form Navigator.

Glom

Glom [2] is another free database management system published under the GPL. Glom, which is maintained by the Gnome community, uses the PostgreSQL format for its databases. However, it cannot open PostgreSQL files. Instead, it can only handle files that were previously created with Glom.

Because the program has its own server, there is no need for a back end. Alternatively, Glom offers the option of connecting to an external server on the intranet.

Glom is available from the software repositories of practically all common Linux derivatives.

Glom first opens a window where you can create a new database or open an existing one. Several templates are available. If you are creating a new database, a pop-up window opens. Assign a name and add a description, and then press the Open button.

Glom has two operating modes: operator and developer. The operator is a user with limited rights who is not allowed to create new tables and only has limited authorizations for modifying existing databases. This account is always preset in Glom.

A developer , on the other hand, has full access to the databases and can also create new ones. To change the mode, click on Developer button in the Developer menu, then create a structure for the new table. Enable the Fields option in the Developer menu. Enter the field names and types in an overlapping window. The Name column denotes the object name in the database, the Title column denotes the column header, and the Type column denotes the field type (Figure 3).

Figure 3: Glom provides a simple dialog for entering field definitions.

Once the collection is complete, save the structure by pressing Close and select the Layout option in the Developer menu. In a separate window, enable the fields you want to appear in the form. Add field lets you conveniently select the desired fields from a list of available fields and add them to the list.

For a detailed view, which you access by pressing Details , you need to design the layout separately. The dialogs are very much like those for the list view. After pressing Close , fill the table with values. The best way to do this is to use the detailed view, where you will first want to enable operator mode in the Developer menu. Then press Add to add new records to the form and the database (Figure 4).

Figure 4: The table view in Glom also comes without bells and whistles.

The Edit | Search menu or the keyboard shortcut Ctrl+F lets you search the current database for terms and values. An input line at the top of the detail view lets you call up a search term. Glom now displays the values it finds in the list view. However, the program does not allow several search terms to be linked together or placeholders to be used in the search line.

Alternatively, you can launch a query by entering the character string you want to find in the displayed table form's desired field. After you press Find , the first record found that matches the search criterion appears. You can navigate the individual datasets with the buttons First , Back , Forward , and Last .

An editor for entering SQL command sequences directly is not available in Glom, nor is a dedicated query function for saving queries for later use.

Kexi

Kexi [3] is an integrated database system that is part of Calligra Suite [8]. In the current 3.1.0 version, Kexi runs independently of KDE's Plasma desktop and Calligra Suite. The program works across platforms and is also included in the software repositories of various BSD derivatives.

In addition to its own database engine, which Kexi installs locally, the software works with external database servers, supporting MySQL and PostgreSQL servers.

When launched, Kexi asks whether you want to construct a new database or load an existing one. Note that when loading MySQL or PostgreSQL databases, you first need to install the necessary drivers from the package sources.

Clicking on the New option opens a wizard that helps the user create a database structure in just a few steps. When creating a new database, first select the Blank database option on the right side of the window. In the next step, the wizard prompts you for the storage option: Kexi saves the database either locally or on a server. In the third step, you define the project title and file name.

The main window then appears with the Create tab active. Below this is a buttonbar with which you can create tables, queries, forms, and reports. Pressing the Table button opens an empty table in the lower part of the window.

Enter the Field Name and Data Type in the table for each row (Figure 5); you can select the Data Type from a list. To the right of the table, the software displays a Properties tab that provides information about the field's properties. In this dialog box, you can adjust various attributes of the data field.

Figure 5: Kexi's Table dialog is limited to the bare essentials.

Once you have entered all the data fields, press Save . Enter a meaningful name for the table in a separate window; then, switch from design mode to data mode by pressing the Data button above the table.

An empty table with the created data fields appears in column form. Enter your data and allow it to be sorted in ascending or descending order. Additionally, a search function filters certain character strings from the database. Search criteria also let you delimit the search.

You do not have to save the individual records explicitly; Kexi does this for you automatically. Once you have finished entering the data records, switch to the Query button.

This dialog contains controls similar to those in Base. You can select the desired table in the middle of the selection field and add it to the query by pressing Insert . You now see the table's individual fields in the query area directly below. You can enter several tables in queries; drag and drop field names to create relationships among the tables.

A click on one of the field names moves it to the column view at the bottom of the program window. This window lists the columns relevant to the query in a table (Figure 6). Manually insert criteria for the field in separate columns in the table or hide individual columns.

Figure 6: With just a few clicks, users can generate a query in Kexi.

Along with the criteria, you can also pass in conventional parameters. Press the Data button to activate the data view. You can assemble the results in ascending or descending order.

Once the query meets your requirements, save it by clicking on the button above the Query view. The SQL button provides an insight into the SQL code automatically generated by Kexi and lets you modify the code.

To check the modified SQL code's correctness, click the Check query button. Kexi outputs a warning message if the parameters contain errors.

After clicking the Form button, Kexi opens the form editor. All the existing tables and queries that serve as the basis for the form appear on the left in the program window's navigator area. The software displays numerous design elements for the form at the top horizontally, and an empty template appears in the middle of the editor.

You can insert various fields into the template, freely positioning the fields and changing their size. Once you are satisfied with the form, save it by pressing the Save button and entering a name.