Billed as the "Microsoft Access for Linux," Kexi is an integrated data management application that allows you to easily work with SQLite, MySQL, and PostgreSQL databases.
Buried amongst the fray of KOffice applications is Kexi, the little application that could change your dependency on Microsoft Access or other proprietary desktop databases. The tagline on the Kexi website is “Microsoft Access for Linux.” Kexi is an integrated data management application that allows you to work with SQLite, MySQL, and PostgreSQL databases.
Upon first glance, Kexi closely resembles Access in its organization and appearance. Like Access, Kexi creates and operates on file-based databases and can connect to an ever-increasing list of remote database systems. You can create and operate on Tables, Forms, build Queries, and use Scripts with your databases within the Kexi application.
Figure 1 shows the Kexi interface ready to create databases, tables, forms, queries, and scripts.
Figure One: The Kexi Interface
KEXI, YOU’RE NOT ALONE
Kexi is not a standalone product. It’s part of the KOffice suite and must be used as such due to its integrated design. Lighter binary versions are available for most distributions via the Downloads page on the project website but still contain a significant chunk of KOffice.
For you purists, or those of you who can’t obtain KOffice packages with apt-get or yum, download the source tarball and compile for your platform. The latest stable release, as of this writing, is Kexi 1.1.3 (KOffice 1.6.3). The KOffice suite, once compiled and installed, assimilates nicely into Gnome and KDE. The requirements for KOffice are about 250MB of diskspace for sources and compiled programs and you need KDE > 3.2.
KEXI’S BAG OF TRICKS
Kexi is a cross-platform application and is available at no cost on all Unix-based systems. The Windows version, available here, is a crippled demo. You may purchase the fully functional Windows version for about $55 USD which includes updates and support. This fee also helps pay for development costs associated with using the Windows platform.
- Mac OS X
Kexi is relatively secure by design since it is file-based. Moreover, there is no daemon or service running on a TCP port, so your use of SQLite and Kexi are hidden from network sniffers and scanners. Your personal databases have the same security as all your other files owned by you. Since SQLite has no internal security, shared databases require more stringent permissions to restrict access.
SQLite is a good choice for this kind of tool because it is open source and free so that it may be used for any commercial or non-commercial purpose. The free use of SQLite assures a long life for Kexi.
Kexi includes a Forms Designer that is very simple to use. All of the Form widgets are drag and drop allowing you to quickly create data entry forms. Users of similar tools will feel comfortable using this tool to create new Forms. All widgets snap to a grid or aligned in free-form fashion. Once a widget is chosen and dropped onto the Form, a Property Window appears allowing you to edit all parameters associated with that widget.
One very familiar feature to Access users is the Compact Database feature as shown in Figures 2 and 3. This feature is located under Tools on Kexi’s main menu.
Figure Two: The Kexi Compact Database Tool
Figure Three: The Compacted Database Response Statistics
Instead of including its own programming language, like Access does with Visual Basic, Kexi includes the ability to use multiple scripting languages. Kexi uses the KDE KROSS scripting framework to provide a seamless solution for scripting language support. KROSS is not a programming or scripting language but rather a gateway to a language interpreter. Currently, only Python and Ruby are supported via plugin.
KEXI AT WORK
It’s easy to create your own databases with Kexi. In just a few steps, you can have a fully functional relational database ready to receive and retrieve data.
- Open Kexi by using the menus or from a command line.
Note: If you are going to use scripting languages in your database work, open Kexi from the command line because any output from your scripts is sent to stdout.
Once Kexi is invoked, you are prompted to create or open an existing project.
- Select Blank Database and Click OK.
- Click Next on the following screen to continue creating your new database project.
- Enter a name for your new database project (Guestbook, for example) and click Next.
Kexy automatically names your new database with the .kexi extension.
- Click Create to complete the process.
Your Guestbook database is ready for tables and data.
- To create a new Table, right click the Tables object and select Create Object: Table.
A new table, table1, is created for you in Design View as shown in Figure 4.
Figure Fout: Creating a New Table in the Guestbook Database
- Enter your Field (Column) Captions and Data Types.
- To enter data into your table, switch to Data View.
If you are familiar with Microsoft Access, creating Queries, Forms, and Scripts will be an easy task. For more Kexi tutorials, see the screencasts on their website.
DESPERATELY SEEKING SQL
Athough Kexi is an interesting application, don’t uninstall Access just yet. There are several things that Kexi lacks, and desperately needs, before becoming a real contender for the Access user base.
One of the main drawbacks to Kexi is the inability to enter SQL commands (other than SELECT) into the SQL Window. To use CREATE, INSERT, UPDATE, DELETE, and others, you must use another tool or the command line interface for SQLite. You can perform these functions within the Kexi interface but, like Access, you will find this to be very inefficient.
Second, Kexi only supports Python and Ruby scripting languages. If you don’t program in either of those, then you have to opt for manipulating the database with an external scripting tool like PHP.
The third significant issue is that you cannot connect to external data via ODBC or any other method. You can only import data from an external source.
Finally, and perhaps most frustrating of all, Kexi won’t display any externally created tables. If you create tables via command line SQLite, or other tool, you won’t be able to see or manipulate them inside Kexi’s interface.
Kexi will most likely remain tied to SQLite for the foreseeable future due to its maturity and zero configuration requirements. SQLite is also actively developed which makes it an excellent candidate for future development of applications like Kexi.
I usually don’t write about applications that I don’t like and Kexi is no exception. Although it has some significant shortcomings and really doesn’t deliver on the “Microsoft Access for Linux” promise, I do like it. Kexi’s abilities did surprise and impress me. I especially like the fact that I am not tied to a single scripting language inside the application. That is one of my major complaints about Microsoft Access. I really don’t like Visual Basic nor do I think it’s necessary to have a language built in to an application. Kexi does it right with plugins. Its plugin architecture means that there’s no limit to the number of scripting languages it can support. It isn’t Access quite yet but it is an exciting project that deserves notice and it did leave a lasting impression.
Kenneth Hess is a Linux evangelist and freelance technical writer on a variety of open source topics including Linux, SQL, databases, and web services. Ken can be reached via his website at http://www.kenhess.com
. Practical Virtualization Solutions by Kenneth Hess and Amy Newman is available now.