Helper methods to perform and work with database queries. More...

Collaboration diagram for Sql:

Classes
class	Cutelyst::Sql::Transaction
	This is a helper class to create scoped transactions. More...

Macros
#define	CPreparedSqlQuery(str)

#define	CPreparedSqlQueryFO(str)

#define	CPreparedSqlQueryForDatabase(str, db)

#define	CPreparedSqlQueryForDatabaseFO(str, db)

#define	CPreparedSqlQueryThread(str)

#define	CPreparedSqlQueryThreadFO(str)

#define	CPreparedSqlQueryThreadForDB(str, db)

#define	CPreparedSqlQueryThreadForDBFO(str, db)

Functions
CUTELYST_PLUGIN_UTILS_SQL_EXPORT void	Cutelyst::Sql::bindParamsToQuery (QSqlQuery &query, const Cutelyst::ParamsMultiMap &params, bool htmlEscaped=true)

CUTELYST_PLUGIN_UTILS_SQL_EXPORT QString	Cutelyst::Sql::databaseNameThread (const QString &dbName=QString())

CUTELYST_PLUGIN_UTILS_SQL_EXPORT QSqlDatabase	Cutelyst::Sql::databaseThread (const QString &dbName=QString())

CUTELYST_PLUGIN_UTILS_SQL_EXPORT QSqlQuery	Cutelyst::Sql::preparedQuery (const QString &query, QSqlDatabase db=QSqlDatabase(), bool forwardOnly=false)

CUTELYST_PLUGIN_UTILS_SQL_EXPORT QSqlQuery	Cutelyst::Sql::preparedQueryThread (const QString &query, const QString &dbName=QString(), bool forwardOnly=false)

CUTELYST_PLUGIN_UTILS_SQL_EXPORT QVariantList	Cutelyst::Sql::queryToHashList (QSqlQuery &query)

CUTELYST_PLUGIN_UTILS_SQL_EXPORT QVariantHash	Cutelyst::Sql::queryToHashObject (QSqlQuery &query)

CUTELYST_PLUGIN_UTILS_SQL_EXPORT QVariantHash	Cutelyst::Sql::queryToIndexedHash (QSqlQuery &query, const QString &key)

CUTELYST_PLUGIN_UTILS_SQL_EXPORT QJsonObject	Cutelyst::Sql::queryToIndexedJsonObject (QSqlQuery &query, const QString &key)

CUTELYST_PLUGIN_UTILS_SQL_EXPORT QJsonArray	Cutelyst::Sql::queryToJsonArray (QSqlQuery &query)

CUTELYST_PLUGIN_UTILS_SQL_EXPORT QJsonObject	Cutelyst::Sql::queryToJsonObject (QSqlQuery &query)

CUTELYST_PLUGIN_UTILS_SQL_EXPORT QJsonArray	Cutelyst::Sql::queryToJsonObjectArray (QSqlQuery &query)

CUTELYST_PLUGIN_UTILS_SQL_EXPORT QVariantList	Cutelyst::Sql::queryToList (QSqlQuery &query)

CUTELYST_PLUGIN_UTILS_SQL_EXPORT QVariantList	Cutelyst::Sql::queryToMapList (QSqlQuery &query)

CUTELYST_PLUGIN_UTILS_SQL_EXPORT QVariantMap	Cutelyst::Sql::queryToMapObject (QSqlQuery &query)

Detailed Description

Header to include: #include <Cutelyst/Plugins/Utils/Sql>

The Sql plugin provides methods and classes in the Sql namespace to help with performing database queries and to handle the query results.

Logging category: cutelyst.utils.sql
Logging with Cutelyst

Usage example

Setup the database and the per thread database connection in your application class. Database connections should be established in your reimplementation of Application::postFork(). Creating the database can be done in your reimplementation of Application::init(). You could also use external tools to create the database layout. On bigger project you will mostly want to use some kind of database migration helper. In this example we will use a simple SQLite database that will be created in our init method if not already existing.

myapp.h:

#ifndef MYAPP_H
#define MYAPP_H
 
#include <Cutelyst/Application>
 
using namespace Cutelyst;
 
class MyApp : public Application
{
     Q_OBJECT
     CUTELYST_APPLICATION(IID "MyApp")
public:
     Q_INVOKABLE explicit MyApp(QObject *parent = nullptr);
     ~MyApp() override = default;
 
     // our reimplementation of the init method to initialize
     // the application
     bool init() override;
 
     // our reimplementation of postFork that is called after
     // the engine forks to establish the per thread database
     // connection
     bool postFork();
 
private:
     // function to create the database if it not exists
     // will be called inside init()
     bool createDb();
 
     // path to the SQLite database file
     QString m_dbPath;
};
#endif // MYAPP_H

Implementation in myapp.cpp:

#include "myapp.h"
 
#include <Cutelyst/Plugins/Utils/Sql>
 
#include <QSqlDatabase>
#include <QSqlError>
#include <QSqlQuery>
#include <QStandardPaths>
#include <QFileInfo>
#include <QMutex>
#include <QDebug>
 
// mutex used to protect the db connection setup
static QMutex dbMutex;
 
MyApp::MyApp(QObject *parent) : Application(parent)
{
}
 
bool MyApp::init()
{
     // other initialization stuff
     // ...
 
     // setting the path for the SQLite database file
     m_dbPath = QStandardPaths::writableLocation(QStandardPaths::HomeLocation) + "/myapp.sqlite";
 
     // if the database not already exists, we will create it
     if (!QFileInfo::exists(m_dbPath)) {
          if (!createDb()) {
               qCritical() << "Failed to create database" << m_dbPath;
               return false;
          }
 
          // remove the database connection used for creating the db
          QSqlDatabase::removeDatabase("db");
     }
 
     // maybe more initalization stuff
     // ...
}
 
bool MyApp::createDb()
{
     // create a new database connection only used for creating the db layout
     auto db = QSqlDatabase::addDatabae("QSQLITE", "db");
     db.setDatabaseName(m_dbPath);
     if (!db.open()) {
          qCritical() << "Failed to open database:" << db.lastError().databaseText();
          return false;
     }
 
     QSqlQuery query(db);
     qDebug() << "Creating database" << m_dbPath;
 
     if (!query.exec("CREATE TABLE users "
                     "( id INTEGER PRIMARY KEY NOT NULL AUTOINCREMENT"
                     ", username TEXT NOT NULL UNIQUE"
                     ", password TEXT NOT NULL"
                     ", email_address TEXT NOT NULL UNIQUE"
                     ", first_name TEXT"
                     ", last_name TEXT"
                     ", active INTEGER DEFAULT 1)")) {
         qCritical() << "Failed to create database:" << query.lastError().text();
         return false;
     }
 
     return true;
}
 
bool MyApp::postFork()
{
     // try to lock our mutex to protect connection establishment
     QMutexLocker locker(&dbMutex);
 
     // create a new per thread database connection,
     // later on you have to use the database name usede here ("myapp") when getting
     // the database connection for queries
     auto db = QSqlDatabase::addDatabase("QSQLITE", Cutelyst::Sql::databaseNameThread("myapp"));
     db.setDatabaseName(m_dbPath);
     if (!db.open()) {
          qCritical() << "Failed to open database:" << db.lastError().databaseText();
          return false;
     }
 
     qDebug() << "Database ready:" << db.connectionName();
 
     return true;
}

Use our database in a controller listing available users:

#include <Cutelyst/Plugins/Utils/Sql>
 
#include <QSqlQuery>
#include <QSqlError>
#include <QDebug>
 
using namespace Cutelyst;
 
void Users::index(Context *c)
{
     // get a new forward only query for the local thread for db connection named "myapp"
     QSqlQuery query = CPreparedSqlQueryThreadForDBFO("SELECT * FROM users", "myapp");
 
     if (!query.exec()) {
          qCritical() << "Failed to query users table:" << query.lastError().text();
          // handle errors
     }
 
     // convert the query result in a list of maps where the
     // map key will be the database column name
     const QVariantList users = Sql::queryToMapList(query);
 
     if (users.empty()) {
          // handle empty result
     }
 
     // put the users into the stash to use it in your template
     // or perform other actions on it
}

Macro Definition Documentation

◆ CPreparedSqlQuery

#define CPreparedSqlQuery ( str )

Constructs a static QSqlQuery with query str on the default database using Cutelyst::Sql::preparedQuery(). The created QSqlQuery object will be returned.

Definition at line 234 of file sql.h.

◆ CPreparedSqlQueryFO

#define CPreparedSqlQueryFO ( str )

Constructs a static QSqlQuery with query str on the default database using Cutelyst::Sql::preparedQuery() with forwardOnly set to true. The crated query will be returned.

Definition at line 280 of file sql.h.

◆ CPreparedSqlQueryForDatabase

#define CPreparedSqlQueryForDatabase	(	str,
		db
	)

Constructs a static thread local QSqlQuery with query str on database db using Cutelyst::Sql::preparedQuery() method. The created QSqlQuery object will be returned.

Definition at line 223 of file sql.h.

◆ CPreparedSqlQueryForDatabaseFO

#define CPreparedSqlQueryForDatabaseFO	(	str,
		db
	)

Constructs a static thread local QSqlQuery with query str on database db using Cutelyst::Sql::preparedQueryThread() with forwardOnly set to true. The created QSqlQuery will be returned.

Definition at line 268 of file sql.h.

◆ CPreparedSqlQueryThread

#define CPreparedSqlQueryThread ( str )

Constructs a static thread local QSqlQuery with query str on the database for the current thread using Cutelyst::Sql::preparedQueryThread(). The created QSqlQuery object will be returned.

Definition at line 245 of file sql.h.

◆ CPreparedSqlQueryThreadFO

#define CPreparedSqlQueryThreadFO ( str )

Constructs a static thread local QSqlQuery with query str on the database for the current thread using Cutelyst::Sql::preparedQueryThread() with forwardOnly set to true. The created QSqlQuery object will be returned.

Definition at line 292 of file sql.h.

◆ CPreparedSqlQueryThreadForDB

#define CPreparedSqlQueryThreadForDB	(	str,
		db
	)

Constructs a static thread local QSqlQuery with query str on database db using Cutelyst::Sql::preparedQueryThread(). The created QSqlQuery object will be returned.

Definition at line 256 of file sql.h.

◆ CPreparedSqlQueryThreadForDBFO

#define CPreparedSqlQueryThreadForDBFO	(	str,
		db
	)

Constructs a static thread local QSqlQuery with query str on database db using Cutelyst::Sql::preparedQueryThread() with forwardOnly set to true. The created QSqlQuery object will be returned.

Definition at line 305 of file sql.h.

Function Documentation

◆ bindParamsToQuery()

void Cutelyst::Sql::bindParamsToQuery	(	QSqlQuery &	query,
		const Cutelyst::ParamsMultiMap &	params,
		bool	htmlEscaped = `true`
	)

Bind params to the query, using the param name as the placeholder prebended with ':', if htmlEscaped is true the bound values will be the return of toHtmlEscaped()

Definition at line 215 of file sql.cpp.

References QSqlQuery::bindValue(), QMultiMap::constBegin(), and QMultiMap::constEnd().

◆ databaseNameThread()

QString Cutelyst::Sql::databaseNameThread ( const QString & dbName = QString() )

Returns a string with as "dbName-threadNumber" to be used for connecting

Definition at line 263 of file sql.cpp.

References QThread::currentThread(), and QObject::objectName.

Referenced by Cutelyst::Sql::databaseThread(), and Cutelyst::Sql::preparedQueryThread().

◆ databaseThread()

QSqlDatabase Cutelyst::Sql::databaseThread ( const QString & dbName = QString() )

Returns a QSqlDatabase named as "dbName-threadNumber" to be used for QSqlQuery

Definition at line 268 of file sql.cpp.

References QSqlDatabase::database(), and Cutelyst::Sql::databaseNameThread().

◆ preparedQuery()

QSqlQuery Cutelyst::Sql::preparedQuery	(	const QString &	query,
		QSqlDatabase	db = `QSqlDatabase()`,
		bool	forwardOnly = `false`
	)

Returns a QSqlQuery object prepared with query using the database db. This is specially useful to avoid pointers to prepared queries.

For applications that use default QSqlDatabase() connection, not thread-safe: QSqlQuery query = CPreparedSqlQuery("SELECT * FROM");

For applications that do not use default QSqlDatabase(), the returned QSqlQuery is a CUTELYST_PLUGIN_UTILS_SQL_EXPORT thread_local which glues QSqlQuery to the current thread but you must have a per thread QSqlDatabase() connection for this to be completely safe: QSqlQuery query = CPreparedSqlQueryForDatabase("SELECT * FROM", QSqlDatabase::data);

The returned object is set to forward only and you must use a different database connection and thread_local on CUTELYST_PLUGIN_UTILS_SQL_EXPORT objects to be thread-safe.

Definition at line 241 of file sql.cpp.

References QSqlError::databaseText(), QSqlQuery::lastError(), QSqlQuery::prepare(), and QSqlQuery::setForwardOnly().

◆ preparedQueryThread()

QSqlQuery Cutelyst::Sql::preparedQueryThread	(	const QString &	query,
		const QString &	dbName = `QString()`,
		bool	forwardOnly = `false`
	)

Returns a QSqlQuery object prepared with query using the dbName database which will automatically get's in the form of databaseNameThread(). This is specially useful to avoid pointers to prepered queries.

For applications that uses default QSqlDatabase() connection, not thread-safe: QSqlQuery query = CPreparedSqlQuery("SELECT * FROM");

For applications that do not use default QSqlDatabase(), the returned QSqlQuery is a CUTELYST_PLUGIN_UTILS_SQL_EXPORT thread_local which glues QSqlQuery to the current thread but you must have a per thread QSqlDatabase() connection for this to be completely safe: QSqlQuery query = CPreparedSqlQueryForDatabase("SELECT * FROM", QSqlDatabase::data);

The returned object is set to forward only and you must use a different database connection and thread_local on CUTELYST_PLUGIN_UTILS_SQL_EXPORT objects to be thread-safe.

Definition at line 252 of file sql.cpp.

References QSqlDatabase::database(), Cutelyst::Sql::databaseNameThread(), QSqlError::databaseText(), QSqlQuery::lastError(), QSqlQuery::prepare(), and QSqlQuery::setForwardOnly().

◆ queryToHashList()

QVariantList Cutelyst::Sql::queryToHashList ( QSqlQuery & query )

Returns a variant list of QVariant hashes for all the rows in the query object, it’s useful for creating stash objects for say a list of users.

Definition at line 33 of file sql.cpp.

References QList::append(), QList::at(), QSqlRecord::count(), QSqlRecord::fieldName(), QSqlQuery::next(), QSqlQuery::record(), and QSqlQuery::value().

◆ queryToHashObject()

QVariantHash Cutelyst::Sql::queryToHashObject ( QSqlQuery & query )

Returns a QVariant hash for the first (if any) row in the query object, it’s useful for creating stash objects for say an user.

Definition at line 20 of file sql.cpp.

References QSqlRecord::count(), QSqlRecord::fieldName(), QSqlQuery::next(), QSqlQuery::record(), and QSqlQuery::value().

◆ queryToIndexedHash()

QVariantHash Cutelyst::Sql::queryToIndexedHash	(	QSqlQuery &	query,
		const QString &	key
	)

Returns a QVariantHash of QVariantHashes where the key parameter is the field name in the query result. This is useful when you want to access specific user by user name or user id.

Definition at line 151 of file sql.cpp.

References QList::append(), QList::at(), QSqlRecord::count(), QSqlRecord::fieldName(), QSqlRecord::indexOf(), QSqlQuery::next(), QSqlQuery::record(), QVariant::toString(), and QSqlQuery::value().

◆ queryToIndexedJsonObject()

QJsonObject Cutelyst::Sql::queryToIndexedJsonObject	(	QSqlQuery &	query,
		const QString &	key
	)

Returns a QJsonObject of QJsonObject where the key parameter is the field name in the query result. This is useful when you want to access specific user by user name or user id.

Definition at line 183 of file sql.cpp.

References QList::append(), QList::at(), QSqlRecord::count(), QSqlRecord::fieldName(), QJsonValue::fromVariant(), QSqlRecord::indexOf(), QJsonObject::insert(), QSqlQuery::next(), QSqlQuery::record(), QVariant::toString(), and QSqlQuery::value().

◆ queryToJsonArray()

QJsonArray Cutelyst::Sql::queryToJsonArray ( QSqlQuery & query )

Returns a QJsonArray of arrays for all the rows in the query object, columns are indexed by it’s position instead of a QString map lookup.

Definition at line 135 of file sql.cpp.

References QJsonArray::append(), QSqlRecord::count(), QJsonValue::fromVariant(), QSqlQuery::next(), QSqlQuery::record(), and QSqlQuery::value().

◆ queryToJsonObject()

QJsonObject Cutelyst::Sql::queryToJsonObject ( QSqlQuery & query )

Returns a QJsonObject for the first (if any) row in the query object. Each column name is a key in the object.

Definition at line 66 of file sql.cpp.

References QSqlRecord::count(), QSqlRecord::fieldName(), QJsonValue::fromVariant(), QJsonObject::insert(), QSqlQuery::next(), QSqlQuery::record(), and QSqlQuery::value().

◆ queryToJsonObjectArray()

QJsonArray Cutelyst::Sql::queryToJsonObjectArray ( QSqlQuery & query )

Returns an array of QJsonObject objects for all the rows in the query object.

Definition at line 99 of file sql.cpp.

References QJsonArray::append(), QList::append(), QList::at(), QSqlRecord::count(), QSqlRecord::fieldName(), QJsonValue::fromVariant(), QJsonObject::insert(), QSqlQuery::next(), QSqlQuery::record(), and QSqlQuery::value().

◆ queryToList()

QVariantList Cutelyst::Sql::queryToList ( QSqlQuery & query )

Returns a list of QVariantLists for all the rows in the query object, it’s fastest option to pass to Cutelee view as columns are indexed by it’s position instead of a QString hash lookup.

Definition at line 119 of file sql.cpp.

References QSqlRecord::count(), QVariant::fromValue(), QSqlQuery::next(), QSqlQuery::record(), and QSqlQuery::value().

◆ queryToMapList()

QVariantList Cutelyst::Sql::queryToMapList ( QSqlQuery & query )

Returns a variant list of QVariant maps for all the rows in the query object, it’s useful for creating stash objects for say a list of users to be used by JSON serializer.

Definition at line 79 of file sql.cpp.