15.1.56. tablet_qt/db/dbfunc.h

/*
    Copyright (C) 2012, University of Cambridge, Department of Psychiatry.
    Created by Rudolf Cardinal (rnc1001@cam.ac.uk).

    This file is part of CamCOPS.

    CamCOPS is free software: you can redistribute it and/or modify
    it under the terms of the GNU General Public License as published by
    the Free Software Foundation, either version 3 of the License, or
    (at your option) any later version.

    CamCOPS is distributed in the hope that it will be useful,
    but WITHOUT ANY WARRANTY; without even the implied warranty of
    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
    GNU General Public License for more details.

    You should have received a copy of the GNU General Public License
    along with CamCOPS. If not, see <https://www.gnu.org/licenses/>.
*/

#pragma once
#include <QDebug>
#include <QPair>
#include <QSqlDatabase>
#include <QString>
#include <QStringList>
#include <QVariant>

#include "common/aliases_qt.h"
#include "db/field.h"
#include "sqlargs.h"

class SqlitePragmaInfoField;

namespace dbfunc {

// ============================================================================
// Constants
// ============================================================================

extern const QString DATA_DATABASE_FILENAME;
extern const QString SYSTEM_DATABASE_FILENAME;
extern const QString DATABASE_FILENAME_TEMP_SUFFIX;
extern const QString TABLE_TEMP_SUFFIX;

// ============================================================================
// SQL fragments
// ============================================================================

// Returns an identifier (e.g. table name, field name), delimited according to
// ANSI SQL standards.
QString delimit(const QString& identifier);

// Returns "SELECT <columns> FROM <table>", with delimiting.
QString selectColumns(const QStringList& columns, const QString& table);

// Returns SQL like "UPDATE <table> SET <field1>=?, ...", with delimiting, in
// an SqlArgs object with the values.
// The updatevalues parameter maps fieldnames to values.
SqlArgs updateColumns(const UpdateValues& updatevalues, const QString& table);

// ============================================================================
// Queries
// ============================================================================

// If require, appends an " ORDER BY..." clause to the SQL given.
void addOrderByClause(const OrderBy& order_by, SqlArgs& sqlargs_altered);

// Binds arguments to a QSqlQuery, from a vector of argument values.
void addArgs(QSqlQuery& query, const ArgList& args);

// Executes a QSqlQuery. (Low-level function.)
bool execQuery(
    QSqlQuery& query, const SqlArgs& sqlargs, bool suppress_errors = false
);

// Executes a QSqlQuery. (Low-level function.)
bool execQuery(
    QSqlQuery& query,
    const QString& sql,
    const ArgList& args,
    bool suppress_errors = false
);

// Returns a string like "?,?,?" for n parameter holders
QString sqlParamHolders(int n);

// Converts a vector of ints to an ArgList; that is, a QVector<QVariant>.
ArgList argListFromIntList(const QVector<int>& intlist);

// ============================================================================
// Database structure
// ============================================================================

// Returns the field names from an SqlitePragmaInfoField object.
// If delimited is true, delimits the output.
QStringList fieldNamesFromPragmaInfo(
    const QVector<SqlitePragmaInfoField>& infolist, bool delimited = false
);

// Returns "CREATE TABLE IF NOT EXISTS ..." SQL from information describing
// the table.
QString makeCreationSqlFromPragmaInfo(
    const QString& tablename, const QVector<SqlitePragmaInfoField>& infolist
);

// ============================================================================
// Altering structure
// ============================================================================

// Returns "CREATE TABLE IF NOT EXISTS ..." SQL from a list of Field objects.
QString
    sqlCreateTable(const QString& tablename, const QVector<Field>& fieldlist);

// ============================================================================
// Encryption queries, via SQLCipher
// ============================================================================

// Encrypts an SQLite database via SQLCipher, on disk, in place (in the end;
// in practice via a temporary file).
bool encryptPlainDatabaseInPlace(
    const QString& filename,
    const QString& tempfilename,
    const QString& passphrase
);

}  // namespace dbfunc