How to write a SQLDatabase Driver

Introduction

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 lists of QVariant.

For the driver we actually need two substantial classes:

• The actual driver class inheriting QSqlDriver that can be registered with QSqlDatabase
• A class holding the result data inheriting QSqlResult

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 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 layer to get to know what database features the driver supports. There is a list of driver features 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 do 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& 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~() methods 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& query ) {
 QString data;
 int space = query.indexOf( " " );

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

If the command has a parameter 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 && 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.