How to write a SQLDatabase Driver: Difference between revisions

From Qt Wiki
Jump to navigation Jump to search
No edit summary
 
(convert {{doclinkanchor}} to the improved {{doclink}})
 
(9 intermediate revisions by 3 users not shown)
Line 1: Line 1:
=<span class="caps">QSQLD</span>atabase <span class="caps">CSV</span> Driver=
[[Category:HowTo]]
== Introduction ==
This article explains how to write a new {{DocLink|QSQLDriver}} that can be used in concert with the {{DocLink|QSQLDatabase}} class. In this example we create a driver to read and write CSV files.


This article explains how to write a new <span class="caps">QSQLD</span>river that can be used in concert with the <span class="caps">QSQLD</span>atabase class. In this example we create a driver to read and write <span class="caps">CSV</span> files.
A rudimentary selection language will be used to perform queries and return lists of {{DocLink|QVariant}}.
 
A rudimentary selection language will be used to perform queries and return QVariant lists.


For the driver we actually need two substantial classes:
For the driver we actually need two substantial classes:


* The actual driver class inheriting [http://doc.qt.io/qt-5/qsqldriver.html QSqlDriver] ''[qt.io]'' that can be registered with [http://doc.qt.io/qt-5/qsqldatabase.html QSqlDatabase] ''[qt.io]''
* The actual driver class inheriting <tt>QSqlDriver</tt> that can be registered with <tt>QSqlDatabase</tt>
* A class holding the result data inheriting [http://doc.qt.io/qt-5/qsqlresult.html QSqlResult] ''[qt.io]''
* A class holding the result data inheriting {{DocLink|QSqlResult}}


So let us start with the driver. Actually we do nothing special but opening a <span class="caps">CSV</span> 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.
So let us start with the driver. Actually we do nothing special but opening a CSV file, reading the contents into a {{DocLink|QStringList}} and interpret some commands like <tt>SELECT</tt>, <tt>INSERT</tt>, <tt>DELETE</tt>, <tt>COMMIT</tt>. The insert and delete commands both manipulate the <tt>QStringList</tt> in memory, the commit command writes the list back to the file on disk.


The header for the driver sqlcsvdriver.hpp:
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.
<code>
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;
};
</code>
 
Now we head over to the implementation. The first member <tt>hasFeature</tt> is important for the <tt>QSqlDatabase</tt> abstraction layer to get to know what database features the driver supports. There is a list of [http://doc.qt.io/qt-5/qsqldriver.html#DriverFeature-enum driver features] in the {{DocLink|QSqlDriver}} documentation available.


In our driver we decide to support the following features:
In our driver we decide to support the following features:


We wanted to stay minimalistic and did not want the features <span class="caps">BLOB</span>s (Binary Large OBject) and positional placeholders. Furthermore since the entire <span class="caps">CSV</span> file is stored in memory, supporting <span class="caps">BLOB</span>s does not seem very practical.
<code>
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;
}
}
</code>
 
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 <tt>open</tt> 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.
 
<code>
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;
  }


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 <span class="caps">CSV</span> file not. So the first parameter we pass as newDB is just a string containing the path to the <span class="caps">CSV</span> file that will be opened and accessed by QFile.
  while ( !f->atEnd() ) {
  contents->append( QString( f->readLine() ).trimmed() );
  }
  f->close();
  setOpen( true );
  setOpenError( false );
  return true;
}
</code>


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”.
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 <tt>{{DocLink|QSqlError}}::ConnectionError</tt> by a call to {{DocLink|QsqlError|setLastError}}.


Important here is to set the flags setOpen and setOpenError accordingly at the end of this member.
Important here is to set the flags <tt>setOpen</tt> and <tt>setOpenError</tt> accordingly at the end of this member.
 
<code>
void CSVDriver::close() {
if ( isOpen() ) {
  delete contents;
  delete f;
  setOpen( false );
  setOpenError( false );
}
}
 
QSqlResult* CSVDriver::createResult() const {
return new CSVResult( this );
}
</code>


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.
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 <span class="caps">CSVR</span>esult object what takes us to the header file csvresult.hpp we need:
CreateResults returns a new CSVResult object what takes us to the header file csvresult.hpp we need:


The <span class="caps">CSVR</span>esult 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.
<code>
#include <QtCore>
#include <QtSql>


In reset() the actual work is done why I would like to step through it:
#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;
};
</code>
 
The <tt>CSVResult</tt> inherits <tt>QSqlResult</tt> and we need to re-implement the following methods:
 
* <tt>data()</tt> returns a QVariant of the data of the current record
* The <tt>fetch~()</tt> methods move the internal pointer to the first/next/previous/last record.
* The members size and numRowsAffected are self-explanatory.
* In <tt>reset()</tt> 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)
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…
<code>
bool CSVResult::reset( const QString& query ) {
QString data;
int space = query.indexOf( " " );


Now we decide what to do if the query string starts with a select command:<br /> 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.
if ( space > –1 ) {
  data = query.mid( space+1 );
}
</code>


Don’t forget to set this query active (has records to be retrieved) with setActive to inform the QSqlDatabase instance. Furthermore we set the <span class="caps">SELECT</span> flag to true.
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 <tt>SELECT</tt> command:
Select moves the cursor to index -1 so that a call to <tt>fetchNext</tt> will return the first record at index 0. <tt>SetAt</tt> sets the internal record pointer to an integer index.
 
Don't forget to set this query active (has records to be retrieved) with <tt>setActive</tt> to inform the QSqlDatabase instance. Furthermore we set the SELECT flag to true.
 
<code>
if ( query.startsWith( "select" ) ) {
  if ( at() > –1 ) {
  setAt( -1 );
  }
  setSelect( true );
  setActive( true );
  return true;
} else
</code>


Data is being appended to the contents string list
Data is being appended to the contents string list


The commit command writes the in-memory stringlist to the<br /> underlying file.<br /> If payload to commit is not empty take that as the output<br /> filename to write the data back to
<code>
if ( query.startsWith( "insert" ) ) {
  contents->append( data );
  setActive( true );
  return true;
} else
</code>
 
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
 
<code>
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
</code>


Delete the item specified at the index
Delete the item specified at the index


==The end…==
<code>
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 you do not agree with all this you might want to join<br />[https://www.gitorious.org/sxw-qt-projects/qsqlplaindriver/ this project on gitorious] ''[gitorious.org]'' and send me some merge requests.
// 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;
}
}
</code>


===Categories:===
== The end... ==


* [[:Category:Learning|Learning]]
If you do not agree with all this you might want to join
** [[:Category:Learning::Demos and Examples|Demos_and_Examples]]
[https://www.gitorious.org/sxw-qt-projects/qsqlplaindriver/ this project on gitorious] and send me some merge requests.

Latest revision as of 09:31, 23 October 2015

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.