How to write a SQLDatabase Driver

From Qt Wiki
Revision as of 19:31, 7 March 2015 by Simow (talk | contribs) (Simow moved page QSQLDatabase CSV Driver to How to write a SQLDatabase Driver: Wording)
Jump to navigation Jump to search
This article may require cleanup to meet the Qt Wiki's quality standards. Reason: Auto-imported from ExpressionEngine.
Please improve this article if you can. Remove the {{cleanup}} tag and add this page to Updated pages list after it's clean.

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:

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:

class CSVDriver : public QSqlDriver {
 public:
 bool hasFeature( DriverFeature feature ) const;
 bool open(
 const QString& db,
 const QString& user,
 const QString& password,
 const QString& host,
 int port,
 const QString& options
 );
 void close();
 QSqlResult* createResult() const;
 QFile* getFile();
 QStringList* getContents();
 QString getCsvFileName();

private:
 QString csvFilename;
 QFile *f;
 QStringList '''contents;
};

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:

bool CSVDriver::hasFeature( DriverFeature feature ) const {
 switch ( feature ) {
 case QSqlDriver::BLOB:
 case QSqlDriver::PositionalPlaceholders:
 return false;
 case QSqlDriver::Unicode:
 case QSqlDriver::Transactions:
 case QSqlDriver::QuerySize:
 return true;
 default:
 return false;
 }
}

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.

bool CSVDriver::open(
 const QString& newDb,
 const QString& newUser = "nobody",
 const QString& newPassword = "anonymous",
 const QString& newHost = "localhost",
 int newPort = -1,
 const QString& newOptions = "nop"
 ) {
 csvFilename = newDb;
 user = newUser;
 password = newPassword;
 host = newHost;
 options = newOptions;
 port = newPort;

 f = new QFile( csvFilename );
 contents = new QStringList();

 if ( !f->open( QIODevice::ReadWrite | QIODevice::Text ) ) {
 setLastError( QSqlError( "Unable to open Backingstore: " + csvFilename, "[CSVDRIVER]",
 QSqlError::ConnectionError, 42 ) );
 return false;
 }

 while ( !f->atEnd() ) {
 contents->append( QString( f->readLine() ).trimmed() );
 }

 f->close();
 setOpen( true );
 setOpenError( false );

 return true;
}

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.

void CSVDriver::close() {
 if ( isOpen() ) {
 delete contents;
 delete f;

 setOpen( false );
 setOpenError( false );
 }
}

QSqlResult''' CSVDriver::createResult() const {
 return new CSVResult( this );
}

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:

#include <QtCore>
#include <QtSql>

#include "sqlcsvdriver.hpp"

class CSVResult : public QSqlResult {

public:
 CSVResult( const QSqlDriver* driver );

protected:
 QVariant data( int index );
 bool isNull( int );
 bool reset( const QString&amp;amp; query );
 bool fetch( int );
 bool fetchFirst();
 bool fetchNext();
 bool fetchLast();
 int size();
 int numRowsAffected();

private:
 QString csvFilename;
 QStringList *contents;
};

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)

bool CSVResult::reset( const QString&amp;amp; query ) {
 QString data;
 int space = query.indexOf( " " );

if ( space > 1 ) {
 data = query.mid( space+1 );
 }

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.

 if ( query.startsWith( "select" ) ) {
 if ( at() > 1 ) {
 setAt( -1 );
 }
 setSelect( true );
 setActive( true );

 return true;
 } else

Data is being appended to the contents string list

 if ( query.startsWith( "insert" ) ) {
 contents->append( data );
 setActive( true );
 return true;
 } else

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

 if ( query.startsWith( "commit" ) ) {
 QFile *f = new QFile( !data.isEmpty() ? data : csvFilename );
 if ( !f->open( QIODevice::WriteOnly | QIODevice::Text ) ) {
 setLastError(
 QSqlError(
 "Unable to commit: The CSV file could not be openend for write.",
 "[CSVDRIVER]",
 QSqlError::ConnectionError,
 102
 )
 );
 return false;
 }

foreach( QString buffer, *contents ) {
 f->write( buffer.toAscii() + "" );
 }

f->close();
 delete f;

return true;
 } else

Delete the item specified at the index

 if ( query.startsWith( "delete" ) ) {
 // if no argument given, return false
 if ( data.isEmpty() ) {
 setLastError(
 QSqlError(
 "No delete index supplied",
 "[CSVDRIVER]",
 QSqlError::StatementError,
 101
 )
 );
 return false;
 }

// try cast to real int from string
 int index = data.toInt();

// if cast fails it is 0 but doesn't harm due to
 // following constraint check
 if ( index > -1 &amp;&amp; index < contents->size() ) {

// if within range do remove
 contents->removeAt( index );
 return true;
 } else {
 setLastError(
 QSqlError(
 "Delete index out of valid range: " + QString::number( index ),
 "[CSVDRIVER]",
 QSqlError::StatementError,
 100
 )
 );
 return false;
 }
 } else {
 // return false on non-implemented commands
 // set last SQL error to QSqlError::StatementError
 setLastError(
 QSqlError(
 "Command not implemented: " + query,
 "[CSVDRIVER]",
 QSqlError::StatementError,
 43
 )
 );
 return false;
 }
}

The end…

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