How to write a SQLDatabase Driver: Difference between revisions

From Qt Wiki
Jump to navigation Jump to search
(Add "cleanup" tag)
(convert {{doclinkanchor}} to the improved {{doclink}})
 
(4 intermediate revisions by 2 users not shown)
Line 1: Line 1:
{{Cleanup | reason=Auto-imported from ExpressionEngine.}}
[[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 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 {{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 "QSqlDriver":http://doc.qt.io/qt-5/qsqldriver.html that can be registered with "QSqlDatabase":http://doc.qt.io/qt-5/qsqldatabase.html
* The actual driver class inheriting <tt>QSqlDriver</tt> that can be registered with <tt>QSqlDatabase</tt>
* A class holding the result data inheriting "QSqlResult":http://doc.qt.io/qt-5/qsqlresult.html
* A class holding the result data inheriting {{DocLink|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 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:
Line 17: Line 17:
class CSVDriver : public QSqlDriver {
class CSVDriver : public QSqlDriver {
  public:
  public:
bool hasFeature( DriverFeature feature ) const;
  bool hasFeature( DriverFeature feature ) const;
bool open(
  bool open(
const QString&amp;amp; db,
  const QString& db,
const QString&amp;amp; user,
  const QString& user,
const QString&amp;amp; password,
  const QString& password,
const QString&amp;amp; host,
  const QString& host,
int port,
  int port,
const QString&amp;amp; options
  const QString& options );
);
  void close();
void close();
  QSqlResult* createResult() const;
QSqlResult* createResult() const;
  QFile* getFile();
QFile* getFile();
  QStringList* getContents();
QStringList* getContents();
  QString getCsvFileName();
QString getCsvFileName();


private:
private:
QString csvFilename;
  QString csvFilename;
QFile *f;
  QFile *f;
QStringList '''contents;
  QStringList *contents;
};
};
</code>
</code>


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.
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:
Line 46: Line 45:
bool CSVDriver::hasFeature( DriverFeature feature ) const {
bool CSVDriver::hasFeature( DriverFeature feature ) const {
  switch ( feature ) {
  switch ( feature ) {
case QSqlDriver::BLOB:
  case QSqlDriver::BLOB:
case QSqlDriver::PositionalPlaceholders:
  case QSqlDriver::PositionalPlaceholders:
return false;
  return false;
case QSqlDriver::Unicode:
 
case QSqlDriver::Transactions:
  case QSqlDriver::Unicode:
case QSqlDriver::QuerySize:
  case QSqlDriver::Transactions:
return true;
  case QSqlDriver::QuerySize:
default:
  return true;
return false;
 
  default:
  return false;
  }
  }
}
}
</code>
</code>


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.
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.
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>
<code>
bool CSVDriver::open(
bool CSVDriver::open(
  const QString&amp;amp; newDb,
  const QString& newDb,
  const QString&amp;amp; newUser = "nobody",
  const QString& newUser = "nobody",
  const QString&amp;amp; newPassword = "anonymous",
  const QString& newPassword = "anonymous",
  const QString&amp;amp; newHost = "localhost",
  const QString& newHost = "localhost",
  int newPort = -1,
  int newPort = -1,
  const QString&amp;amp; newOptions = "nop"
  const QString& newOptions = "nop" ) {
) {
  csvFilename = newDb;
csvFilename = newDb;
  user = newUser;
user = newUser;
  password = newPassword;
password = newPassword;
  host = newHost;
host = newHost;
  options = newOptions;
options = newOptions;
  port = newPort;
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() ) {
  f = new QFile( csvFilename );
contents->append( QString( f->readLine() ).trimmed() );
  contents = new QStringList();
}


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


return true;
  while ( !f->atEnd() ) {
  contents->append( QString( f->readLine() ).trimmed() );
  }
  f->close();
  setOpen( true );
  setOpenError( false );
  return true;
}
}
</code>
</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>
<code>
void CSVDriver::close() {
void CSVDriver::close() {
  if ( isOpen() ) {
  if ( isOpen() ) {
delete contents;
  delete contents;
delete f;
  delete f;
 
  setOpen( false );
setOpen( false );
  setOpenError( false );
setOpenError( false );
  }
  }
}
}


QSqlResult''' CSVDriver::createResult() const {
QSqlResult* CSVDriver::createResult() const {
  return new CSVResult( this );
  return new CSVResult( this );
}
}
Line 132: Line 129:
class CSVResult : public QSqlResult {
class CSVResult : public QSqlResult {


public:
public:
CSVResult( const QSqlDriver* driver );
  CSVResult( const QSqlDriver* driver );


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


private:
private:
QString csvFilename;
  QString csvFilename;
QStringList *contents;
  QStringList *contents;
};
};
</code>
</code>


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.
The <tt>CSVResult</tt> inherits <tt>QSqlResult</tt> and we need to re-implement the following methods:


In reset() the actual work is done why I would like to step through it:
* <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)


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


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


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…
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:
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 fetchNext will return the first record at index 0. SetAt sets the internal record pointer to an integer index.
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 setActive to inform the QSqlDatabase instance. Furthermore we set the SELECT flag to true.
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>
<code>
  if ( query.startsWith( "select" ) ) {
  if ( query.startsWith( "select" ) ) {
if ( at() > –1 ) {
  if ( at() > –1 ) {
setAt( -1 );
  setAt( -1 );
}
  }
setSelect( true );
  setSelect( true );
setActive( true );
  setActive( true );
 
  return true;
return true;
  } else
  } else
</code>
</code>
Line 191: Line 190:
<code>
<code>
  if ( query.startsWith( "insert" ) ) {
  if ( query.startsWith( "insert" ) ) {
contents->append( data );
  contents->append( data );
setActive( true );
  setActive( true );
return true;
  return true;
  } else
  } else
</code>
</code>


The commit command writes the in-memory stringlist to the
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
underlying file.
If payload to commit is not empty take that as the output
filename to write the data back to


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


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


f->close();
f->close();
  delete f;
  delete f;


return true;
return true;
} else
} else
</code>
</code>


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


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


// if cast fails it is 0 but doesn't harm due to
// if cast fails it is 0 but doesn't harm due to
  // following constraint check
  // following constraint check
  if ( index > -1 &amp;&amp; index < contents->size() ) {
  if ( index > -1 && index < contents->size() ) {
 
  // if within range do remove
// if within range do remove
  contents->removeAt( index );
contents->removeAt( index );
  return true;
return true;
  } else {
  } else {
setLastError(
  setLastError( QSqlError( "Delete index out of valid range: " + QString::number( index ),
QSqlError(
  "[CSVDRIVER]", QSqlError::StatementError, 100 )
"Delete index out of valid range: " + QString::number( index ),
  );
"[CSVDRIVER]",
  return false;
QSqlError::StatementError,
100
)
);
return false;
  }
  }
} else {
} else {
  // return false on non-implemented commands
  // return false on non-implemented commands
  // set last SQL error to QSqlError::StatementError
  // set last SQL error to QSqlError::StatementError
  setLastError(
  setLastError(
QSqlError(
  QSqlError( "Command not implemented: " + query, "[CSVDRIVER]",
"Command not implemented: " + query,
  QSqlError::StatementError, 43 ) );
"[CSVDRIVER]",
  return false;
QSqlError::StatementError,
43
)
);
return false;
  }
  }
}
}
</code>
</code>


== The end… ==
== The end... ==


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

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.