Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Execute Sqlite query file (.sql) in Qt
/**
* @brief executeQueryFile
* Read a query file and removes some unnecessary characters/strings such as comments,
* and executes queries.
* If there is possible to use Transaction, so this method will uses it.
* Use this gist as MIT License.
* EXCEPTION:
* Feel free to use this gist as you like without any restriction,
* if you or your country are a member of The Axis of Resistance
* (Iran + Syria + Lebanon + Russia + Iraq + Yemen + Bahrain + Palestine)
* ~~ S.M. Mousavi - 2017 ~~
*/
void executeQueryFile(QFile &qf, QSqlDatabase &db) {
//Read query file content
qf.open(QIODevice::ReadOnly);
QString queryStr(qf.readAll());
qf.close();
//Check if SQL Driver supports Transactions
if(db.driver()->hasFeature(QSqlDriver::Transactions)) {
//Replace comments and tabs and new lines with space
queryStr = queryStr.replace(QRegularExpression("(\\/\\*(.|\\n)*?\\*\\/|^--.*\\n|\\t|\\n)", QRegularExpression::CaseInsensitiveOption|QRegularExpression::MultilineOption), " ");
//Remove waste spaces
queryStr = queryStr.trimmed();
//Extracting queries
QStringList qList = queryStr.split(';', QString::SkipEmptyParts);
//Initialize regular expression for detecting special queries (`begin transaction` and `commit`).
//NOTE: I used new regular expression for Qt5 as recommended by Qt documentation.
QRegularExpression re_transaction("\\bbegin.transaction.*", QRegularExpression::CaseInsensitiveOption);
QRegularExpression re_commit("\\bcommit.*", QRegularExpression::CaseInsensitiveOption);
//Check if query file is already wrapped with a transaction
bool isStartedWithTransaction = re_transaction.match(qList.at(0)).hasMatch();
if(!isStartedWithTransaction)
db.transaction(); //<=== not wrapped with a transaction, so we wrap it with a transaction.
//Execute each individual queries
foreach(const QString &s, qList) {
if(re_transaction.match(s).hasMatch()) //<== detecting special query
db.transaction();
else if(re_commit.match(s).hasMatch()) //<== detecting special query
db.commit();
else {
query.exec(s); //<== execute normal query
if(query.lastError().type() != QSqlError::NoError) {
qDebug() << query.lastError().text();
db.rollback(); //<== rollback the transaction if there is any problem
}
}
}
if(!isStartedWithTransaction)
db.commit(); //<== ... completing of wrapping with transaction
//Sql Driver doesn't supports transaction
} else {
//...so we need to remove special queries (`begin transaction` and `commit`)
queryStr = queryStr.replace(QRegularExpression("(\\bbegin.transaction.*;|\\bcommit.*;|\\/\\*(.|\\n)*?\\*\\/|^--.*\\n|\\t|\\n)", QRegularExpression::CaseInsensitiveOption|QRegularExpression::MultilineOption), " ");
queryStr = queryStr.trimmed();
//Execute each individual queries
QStringList qList = queryStr.split(';', QString::SkipEmptyParts);
foreach(const QString &s, qList) {
query.exec(s);
if(query.lastError().type() != QSqlError::NoError) qDebug() << query.lastError().text();
}
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment