Kexi in Use

This article takes new users through a range of concepts in Kexi, the KOffice database management program. The features available in Kexi range from simple table and query creation to more-advanced reporting and scripting. Kexi recently was released as part of KOffice 2.2. This article is aimed at new users and existing users of Kexi 1.6 as a demonstration of how useful Kexi can be.

Kexi 2 has taken three years of development to get to this stage from Kexi 1.6, and it missed the releases of KOffice 2.0 and 2.1 due to the small number of developers. Like many open-source programs, Kexi is developed solely in developers' free time as a hobby. It is hoped that this first release now is stable enough for use and that it will provide something to build upon for future releases.

To be a useful guide, it's helpful to work on a real use case. As the owner of the Kexi Facebook page (see Resources), I get weekly updates via e-mail with various statistics. The aim of this article is to get this data out of these e-mail messages and into a Kexi database to be able to perform queries and reports to show trends over time.

To get the data for the database, I exported a selection of e-mail messages from KMail. This created a .mbox file, which basically is a text file containing all the messages. It would have been possible to go through each e-mail and enter the details into a table manually, but as I have a few built up already, I want to gather the data automatically. This provides a good challenge for writing a script within Kexi to do it for me.

If Kexi is not included in your installation, see if it is available as an update in your package manager. If not, you need to install it from source using the guides on the KOffice and KDE Wikis (see Resources).

Start by launching Kexi and selecting create a Blank Database from the startup wizard. Depending on the installed plugins, you will be able to create a database stored as a file or create a database on an existing database server, such as PostgreSQL or MySQL. Selecting to have it stored in a file is easiest for new users and is appropriate when there will be a limited number of users accessing the database at any one time. Kexi file-based databases use SQLite as the underlying format, so they are readable by any SQLite-compatible program.

The database requires a name (I chose kexi_facebook), followed by a location to save it. The default location is fine. After this, you are presented with the main Kexi window. The main window contains a toolbar along the top and a project navigator down the left-hand side. The main toolbar in Kexi is different from the other KOffice applications and uses a tab-style layout. Each opened window also has a local toolbar for options specific to that window, such as table, query, form, report and script.

From the Create tab across the top menu, choose Table to launch the table designer.

The statistics I receive via e-mail include the date, number of new fans, number of wall posts, number of visits and total fans, so I created a table with the design schema shown in Figure 1.

Figure 1. Table Design

The fields have a name, type and comment, and also several properties are available from the property editor on the right-hand side, such as constraints and a default value if none is given. Each object in the database will have numerous properties associated with it, and the property editor allows these to be displayed and edited in a single location.

Switching to Data view prompts you to save the table and show the table data editor allowing manual entry of records, but that's not much fun!

With my newly created but empty table, I needed to get the data automatically. As I mentioned earlier, the data was in a single .mbox file containing all e-mail messages. Kexi supports scripts, which can be written in ECMAScript (aka JavaScript), Python or a number of other languages supported by Kross, the KDE scripting framework. I chose to use the QTScript back end, which allows writing in JavaScript, as I am more familiar with it than Python.

My script had to open the .mbox file, read it line by line, grab the data it needed using string manipulation, and when a full set of data was read, add it as a record to the database. Scripts not only have access to built-in methods and Kexi-specific methods, but they also can import libraries containing large amounts of useful functions—the most useful being the Qt libraries. I use the Core functions to have access to the filesystem, using QTextStream for reading data, and the GUI functions for access to QMessageBox to present errors in a dialog if they occur.

From the Create menu tab, this time, I choose Script. This launches the script editor in the central window and the property editor down the right.

A script has only a few properties, the type and the interpreter. The interpreter I want is QTScript, and the type is Executable. An executable script is one that is meant to be run manually. A Module script is meant to contain generic modules of code, accessible from other scripts. And, an Object script is one that is tied to another database object, such as a report.

Figure 2. Script Design

The entire script is shown in Listing 1.