Generating a talk database front end for Qt5 from SQL

published at 18.03.2013 14:22 by Jens Weller
Save to Instapaper Pocket

With the announcement of this years Meeting C++ conference, it came to my mind that I will need this time a local Database for the talks, which later is able to export the talks for being rated by the program committee. A while back in 2010, I had finished the work on a program, which could generate C++ Code for Databases. I decided it was time to put this old project to the test.

My talk database is fairly easy, for now there are two tables, speaker and talks. I need a front end, which lets me enter data in both, and also displays the data in table view. As the database I chose SQLite, as all I need is local storage. Dealing with SQL in C++ can be sometimes a bit cumbersome, and often involves writing large portions of boilerplate code. That was one of the reasons, I decided a few years ago, to write a code generator for exactly this purpose. For now its able to generate code for DTL, SOCI and Qt. For Qt it is able to also generate the code of the front end.

So, instead of writing the front end code my self, I simply tested my own tools output, and I was quite surprised how well it worked. I had to fix some issues with Qt5, quite surprising, there seem to be a few minor differences between the widgets in Qt5 and Qt4.x, which the generator currently targets. But those were easy to fix, some of the includes I had to correct, and one method is now toPlainText instead of plainText(), in QTextEdit. After this, I only had to dig to some of my own samples, to understand, what the program did not generate, and I still had to add. Not much, in less then 2 hours I had a working database. Still I need to do some improvements, and the experience with the talk database will enhance my generator further for sure.

The project it self is a 6750 LOC monster, which I will try to enhance now to be able to further generate C++ code for databases and frontends. I'm also thinking about rewriting parts of it, like the code generation. The codebase it self is surely not convertible to C++11, but a rewrite from scratch will take some time. But lets have a look at its capabilities for now. Two examples, generating a Qt frontend, and generating code for SOCI:

Qt Frontend

Currently for each table there are two front ends generated, a general table view, which shows the stored data in the table, and is editable in its cells:

../../files/blog/sql2cpp/tbl_view.png

This shows one of the current weakness of the front end: not all data should be made editable here, Boolean data is presented as "true" or "false" instead of checkboxes. I have made some changes to the talk database, which adds a edit button to this view, which reuses the dialog also used for creating a new record for editing. This dialog contains the proper data fields and controls:

../../files/blog/sql2cpp/tbl_dlg.png

The frontend code is the most complex one, and also has still the most potential for being evolved into a more functional state. But for now its a first usable state, and is probably right now most useful to build prototypes.

SOCI & DTL

As I already have mentioned, the tool also can create code for SOCI or DTL. I will show you the code which it currently generates for SOCI. A simple example, our table:

CREATE TABLE test( 
  id INTEGER AUTOINCREMENT PRIMARY KEY, 
  name VARCHAR(100), 
  num INTEGER );

Of course this is a bit of pseudo SQL, but basically my program parses for now Create Table statements, and extracts the field information, to be able to generate code with this information. In our example the class for this table could look in UML like this:

../../files/blog/sql2cpp/exampleclass.png

This is the code my tool has generated for this class:

../../files/blog/sql2cpp/Datenklasse.png

My tool can generate enclosing namespaces too. I'll leave the .cpp file for now, as its only containing boilerplate code. SOCI now needs some code, to be able to connect this class with the table in the database, this is also generated:

../../files/blog/sql2cpp/sociexamlpe.png

Note that this is from a run, where the class was named test. While its maybe easier to type this for 1-2 tables, like for my talk database, this approach comes to shine if you do have a few more tables. I had the idea for this while working at a database related project as a freelancer, but later my projects usually did not involve database access again, so I haven't touched this code for some time.

Future

I'm looking to put this to use, so if you have a need for this program, I can generate code for you, or sell you a license. And of course I do look for feedback. The next plan is to improve the code for the front end, as I have a few more uses for generating database front ends for my conference and business. You can download a trialversion here.

Join the Meeting C++ patreon community!
This and other posts on Meeting C++ are enabled by my supporters on patreon!