How to write a SQLDatabase Driver

From Qt Wiki
Revision as of 16:39, 14 January 2015 by Maintenance script (talk | contribs)
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to navigation Jump to search

QSQLDatabase CSV Driver

This article explains how to write a new QSQLDriver that can be used in concert with the QSQLDatabase class. In this example we create a driver to read and write CSV files.

A rudimentary selection language will be used to perform queries and return QVariant lists.

For the driver we actually need two substantial classes:

  • The actual driver class inheriting QSqlDriver [qt.io] that can be registered with QSqlDatabase [qt.io]
  • A class holding the result data inheriting QSqlResult [qt.io]

So let us start with the driver. Actually we do nothing special but opening a CSV file, reading the contents into a QStringList and interpret some commands like “select”, “insert”, “delete”, “commit”. The insert and delete commands both manipulate the QStringList in memory, the commit command writes the changed list back to the file on disk.

The header for the driver sqlcsvdriver.hpp:

Now we head over to the implementation. The first member “hasFeature” is important for the QSqlDatabase abstraction to get to know what database features the driver supports. There is a list of driver features ( http://doc.qt.io/qt-5/qsqldriver.html#DriverFeature-enum ) in the QSqlDriver documentation available.

In our driver we decide to support the following features:

We wanted to stay minimalistic and did not want the features BLOBs (Binary Large OBject) and positional placeholders. Furthermore since the entire CSV file is stored in memory, supporting BLOBs does not seem very practical.

Next is the member “open” that takes quite a number of arguments because we need to cover different cases for different database engines. In some cases you need to supply username and password, in our case for a simple CSV file not. So the first parameter we pass as newDB is just a string containing the path to the CSV file that will be opened and accessed by QFile.

After we read all the contents of the file line by line into a QStringList we return true to signal success. In case the file could not be opened we raise a QSqlError::ConnectionError by a call to “setLastError”.

Important here is to set the flags setOpen and setOpenError accordingly at the end of this member.

The rest of the members are almost self-explanatory. Close does the opposite of open in opposite order: Delete the file object and the string list and set the flags.

CreateResults returns a new CSVResult object what takes us to the header file csvresult.hpp we need:

The CSVResult inherits QSqlResult and we need to re-implement the following methods: data returns a QVariant of the data of the current record. The fetch~ method move the internal pointer to the first/next/previous/last record. The members size and numRowsAffected are self-explanatory.

In reset() the actual work is done why I would like to step through it:

The query string normally consists of two tokens: The command followed by a space followed by a parameter (e.g. a record number)

If the command has a parametr and we encountered a space, store the parameter in “data”. Sure, we could have used a QRegExp here and used the capturing groups but this was developed when I was young and you know…

Now we decide what to do if the query string starts with a select command:
Select moves the cursor to index -1 so that a call to fetchNext will return the first record at index 0. SetAt sets the internal record pointer to an integer index.

Don’t forget to set this query active (has records to be retrieved) with setActive to inform the QSqlDatabase instance. Furthermore we set the SELECT flag to true.

Data is being appended to the contents string list

The commit command writes the in-memory stringlist to the
underlying file.
If payload to commit is not empty take that as the output
filename to write the data back to

Delete the item specified at the index

The end…

If you do not agree with all this you might want to join
this project on gitorious [gitorious.org] and send me some merge requests.

Categories: