maandag 14 maart 2011

Database layer working: ISI parser to proof it

Moving away from Access as the only database backend supported by SAINT has been on the TODO list for quite a long time. As it turned out, creating a system to allow for multiple database backends was not all that easy. It required changes on many levels, which I'll detail a bit below. But don't let that spoil the big news: it works!

Yes, that's right, you can now actually use different databases than just Access with SAINT. At this moment, I have it working with just the ISI parser, but that is already a very important step. I use it as a testbed for all the other tools, which basically need the same infrastructure that ISI parser does, though usually to a lesser extend. That comes down to: if it works for the ISI parser, it should (mostly) work with the rest of the tools too. I will release a beta to demonstrate the working ISI parser as soon as possible.

Nitty-gritty stuff
So... what kind of changes were needed in the SAINT software stack to get all this work? Quite a lot, as it turned out.

Driver backend
The first thing that needed to be done, was to come up with a driver backend that would support all the operations we need on databases and that will work for all of them. The standard Qt database drivers only support abstractions for manipulating the data (Data Manipulation Language or DML), not the data structure (Data Definition Language or DDL). Since SAINT manipulates the data structure all the time (creating tables to store results, for instance), we need support for all of these operations. But: not all databases do that in the same way! SQL is a standard only to point... To further complicate things, not all database support the same data types, nor do these types map to the datatypes used inside SAINT and Qt in a trivial way. To store a value like a date or a long text string, you need different data types in Access and MySql, for instance.

I choose to extend the standard Qt Sql database drivers with additional methods, and in that way create new drivers that can be used with the standard Qt database system, but that offer support for far more operations. Currently, I have drivers implemented for Access (on top of the Qt ODBC driver) and MySql (using the standard Qt MySql driver).

Abstracting database access inside the tools
In order to able to actually use these shiny new drivers, all access to the database needed to be abstracted out of the code. That is: nowhere in the code could direct SQL be used anymore, nor could I use things like type names in the form as strings any more. All SQL statements needed to be generated by the current driver based on a common format. I used Qt's own QSqlRecord for that, in combination with a hugely extended list of supported query types for QSqlDriver::sqlStatement(). Where this last statement is barely documented in the Qt documentation, is turns out to be essential if you want to make your application database agnostic.

User interface
Creating a user interface to select a file on your local file system is not all that complicated. But coming up with a good interface to select or create a generic database, either as a file or running on a server, is a different matter. It took quite a bit of effort to design a build a UI that seems to be easy enough to use, and that can be embedded into the different tools with ease. It will probably need some more tweaking, but here is an idea of the current UI:

The widget that can be embedded in the different tools to select the database:

It features a drop down box with the 10 most recently used databases (with an icon representing the type of database), and a button on the right to open up a dialog box that allows to select another database currently not in the list. This widget will allow drag & drop in the near future, so you can simply drop your exising database file or .sdbd file (more on that later) on it.

The Database Selection Dialog that you get when clicking the button:

This dialog lists the databases that are known to SAINT. For each of them, a small file is stored in your documents directory (under SAINT/database descriptions/, to be exact) containing all the information that is needed to connect with the database, along with some meta information. You can even send these files to your college to easily share access to a server-based database if you want.

Select or create another database:

This is the page that allows you to select or create a database that has not been used before. The drop down box on the top displays a list with available database types. Currently on Windows, you can choose from Microsoft Access and MySql. The rest of the dialog will adapt itself accordingly. Showing on this screenshot is what you get when selecting an Access database.

Dialog for MySql databases:

The above displays the same dialog, but with the MySql database type selected. Once you enter the relevant data on the server and your credentials, available databases on the server will automatically be displayed.

All database types will insist on an Alias to be created for the database. This alias is the name for the database that you defined by selecting a file name, or by entering a server address, port, user credentials and database name. It is used in the file name for the .sdbd (Saint DataBase Description), in the drop down for the database selector widget and in the list of existing databases. A suitable alias will be suggested automatically.

Geen opmerkingen:

Een reactie plaatsen