SQLite БД из памяти в файл, из файла в память, А также вопрос подключения библиотеки SQLite Опции

[Frigolem]

Собственно, очень интересует, как по-нормальному реализовать сохранение/загрузку SQLite БД из памяти / в память.

Сейчас я покажу, как делать не надо. Примеры нехорошие, но рабочие.
Я их показываю только потому, что пока что иных рабочих способов не нашёл.

Нехороший рабочий пример записи SQLite БД из :memory: в файл

bool writeDBMemory2File( QString filename )
{
    // Указанный для записи файл должен быть предварительно уничтожен
    QFile file( filename );
    if( file.exists() ) file.remove();

    // Берём подключение к исходной БД. Подразумевается, что БД уже открыта.
    // БД может быть расположена в :memory:.
    QSqlDatabase srcdb = QSqlDatabase::database( "main_db" );
    // Конструируем запросники для srcdb
    QSqlQuery srcQuery( srcdb ), srcSubQuery( srcdb );

    // Приаттачиваем БД файла к БД из памяти (Наоборот приаттачивать нельзя,
    // так как при приаттачивании или открытии :memory: создаётся новый пустой
    // блок памяти. Уже созданный :memory: приаттачить не получится).
    if( !srcQuery.exec( QString("ATTACH DATABASE '%1' AS trgdb;").arg(filename) ) )
        qDebug() << "[writeDBMemory2File]\n" << srcQuery.lastQuery() << "\n" << srcQuery.lastError().text();

    // Перегоняем таблицы из исходной БД в конечную БД
    if( !srcQuery.exec( "SELECT name FROM sqlite_master WHERE type = 'table';" ) )
        qDebug() << "[writeDBMemory2File]\n" << srcQuery.lastQuery() << "\n" << srcQuery.lastError().text();
    while( srcQuery.next() )
    {
        QString tableName = srcQuery.record().value( "name" ).toString();
        // Для таблицы 'sqlite_sequence' требуется особый подход
        if( tableName == "sqlite_sequence" ) continue;
        if( !srcSubQuery.exec( QString("CREATE TABLE trgdb.'%1' AS SELECT * FROM '%1';").arg(tableName) ) )
            qDebug() << "[writeDBMemory2File]\n" << srcSubQuery.lastQuery() << "\n" << srcSubQuery.lastError().text();
    }

    // Провоцируем создание таблицы sqlie_sequence, так как напрямую её
    // создавать нельзя.
    if( !srcQuery.exec( "CREATE TABLE trgdb.___tmp ( id INTEGER PRIMARY KEY AUTOINCREMENT, value INTEGER );" ) )
        qDebug() << "[writeDBMemory2File]\n" << srcQuery.lastQuery() << "\n" << srcQuery.lastError().text();
    if( !srcQuery.exec( "INSERT INTO trgdb.___tmp( value ) VALUES( 1 );" ) )
        qDebug() << "[writeDBMemory2File]\n" << srcQuery.lastQuery() << "\n" << srcQuery.lastError().text();
    if( !srcQuery.exec( "DROP TABLE trgdb.___tmp;" ) )
        qDebug() << "[writeDBMemory2File]\n" << srcQuery.lastQuery() << "\n" << srcQuery.lastError().text();
    // Перегоняем счётчики генераторов ключей
    if( !srcQuery.exec( "SELECT name, seq FROM sqlite_sequence;" ) )
        qDebug() << "[writeDBMemory2File]\n" << srcQuery.lastQuery() << "\n" << srcQuery.lastError().text();
    while( srcQuery.next() )
    {
        QString sName = srcQuery.record().value( "name" ).toString();
        int sSeq = srcQuery.record().value( "seq" ).toInt();
        if( !srcSubQuery.exec( QString("INSERT INTO trgdb.sqlite_sequence(name, seq) VALUES( '%1', %2 );").arg(sName).arg(sSeq) ) )
            qDebug() << "[writeDBMemory2File]\n" << srcSubQuery.lastQuery() << "\n" << srcSubQuery.lastError().text();
    }

    // Отцепляем приаттаченную БД файла
    if( !srcQuery.exec( "DETACH trgdb;" ) )
        qDebug() << "[writeDBMemory2File]\n" << srcQuery.lastQuery() << "\n" << srcQuery.lastError().text();

    // Для перегонки индексов файловая БД должна быть корневой
    // Поэтому создаём отдельное подключение и пытаемся подключиться
    QSqlDatabase::addDatabase( "QSQLITE", "file_db" );
    {
        // Процесс работы с БД изолирован, так как в случае ошибки
        // removeDatabase требует деструкцию всех компонентов, относящихся
        // к удаляемому соединению
        QSqlDatabase trgdb = QSqlDatabase::database( "file_db", false );
        if( trgdb.open() )
        {
            // Конструируем запросник для новооткрытого соединения. Конструировать
            // запросники можно только для уже открытых соединений, иначе
            // будем получать ошибки типа "Out of memory".
            QSqlQuery trgQuery( trgdb );
            // Перегоняем индексы
            if( !srcQuery.exec( "SELECT sql FROM sqlite_master WHERE type = 'index';" ) )
                qDebug() << "[writeDBMemory2File]\n" << srcQuery.lastQuery() << "\n" << srcQuery.lastError().text();
            while( srcQuery.next() )
            {
                if( !trgQuery.exec( QString("%1;").arg(srcQuery.record().value( "sql" ).toString()) ) )
                    qDebug() << "[writeDBMemory2File]\n" << trgQuery.lastQuery() << "\n" << trgQuery.lastError().text();
            }
        }
        trgdb.close();
    }
    QSqlDatabase::removeDatabase( "file_db" );

    return true;
}

Нехороший рабочий пример чтения SQLite БД из файла в :memory:

bool readDBFile2Memory( QString filename )
{
    // Указанный файл должен существовать
    QFile file( filename );
    if( !file.exists() ) return false;

    // Берём подключение к исходной БД. Подразумевается, что БД уже открыта.
    // БД может быть расположена в :memory:.
    QSqlDatabase srcdb = QSqlDatabase::database( "main_db" );
    // Очищаем БД в памяти
    srcdb.close();
    srcdb.open();

    // Конструируем запросники для srcdb (создавать их можно только после
    // операций открытия/переоткрытия БД)
    QSqlQuery srcQuery( srcdb ), srcSubQuery( srcdb );

    // Приаттачиваем БД файла к БД из памяти (Наоборот приаттачивать нельзя,
    // так как при приаттачивании или открытии :memory: создаётся новый пустой
    // блок памяти. Уже созданный :memory: приаттачить не получится).
    if( !srcQuery.exec( QString("ATTACH DATABASE '%1' AS trgdb;").arg(filename) ) )
        qDebug() << "[readDBFile2Memory]\n" << srcQuery.lastQuery() << "\n" << srcQuery.lastError().text();

    // Перегоняем таблицы из БД файла в БД памяти
    if( !srcQuery.exec( "SELECT name FROM trgdb.sqlite_master WHERE type = 'table';" ) )
        qDebug() << "[readDBFile2Memory]\n" << srcQuery.lastQuery() << "\n" << srcQuery.lastError().text();
    while( srcQuery.next() )
    {
        QString tableName = srcQuery.record().value( "name" ).toString();
        // Для таблицы 'sqlite_sequence' требуется особый подход
        if( tableName == "sqlite_sequence" ) continue;
        if( !srcSubQuery.exec( QString("CREATE TABLE '%1' AS SELECT * FROM trgdb.'%1';").arg(tableName) ) )
            qDebug() << "[readDBFile2Memory]\n" << srcSubQuery.lastQuery() << "\n" << srcSubQuery.lastError().text();
    }

    // Провоцируем создание таблицы sqlie_sequence, так как напрямую её
    // создавать нельзя.
    if( !srcQuery.exec( "CREATE TABLE ___tmp ( id INTEGER PRIMARY KEY AUTOINCREMENT, value INTEGER );" ) )
        qDebug() << "[readDBFile2Memory]\n" << srcQuery.lastQuery() << "\n" << srcQuery.lastError().text();
    if( !srcQuery.exec( "INSERT INTO ___tmp( value ) VALUES( 1 );" ) )
        qDebug() << "[readDBFile2Memory]\n" << srcQuery.lastQuery() << "\n" << srcQuery.lastError().text();
    if( !srcQuery.exec( "DROP TABLE ___tmp;" ) )
        qDebug() << "[readDBFile2Memory]\n" << srcQuery.lastQuery() << "\n" << srcQuery.lastError().text();
    // Перегоняем счётчики генераторов ключей
    if( !srcQuery.exec( "SELECT name, seq FROM trgdb.sqlite_sequence;" ) )
        qDebug() << "[readDBFile2Memory]\n" << srcQuery.lastQuery() << "\n" << srcQuery.lastError().text();
    while( srcQuery.next() )
    {
        QString sName = srcQuery.record().value( "name" ).toString();
        int sSeq = srcQuery.record().value( "seq" ).toInt();
        if( !srcSubQuery.exec( QString("INSERT INTO sqlite_sequence(name, seq) VALUES( '%1', %2 );").arg(sName).arg(sSeq) ) )
            qDebug() << "[readDBFile2Memory]\n" << srcSubQuery.lastQuery() << "\n" << srcSubQuery.lastError().text();
    }

    // Перегоняем индексы
    if( !srcQuery.exec( "SELECT sql FROM trgdb.sqlite_master WHERE type = 'index';" ) )
        qDebug() << "[readDBFile2Memory]\n" << srcQuery.lastQuery() << "\n" << srcQuery.lastError().text();
    while( srcQuery.next() )
    {
        if( !srcSubQuery.exec( QString("%1;").arg(srcQuery.record().value( "sql" ).toString()) ) )
            qDebug() << "[readDBFile2Memory]\n" << srcSubQuery.lastQuery() << "\n" << srcSubQuery.lastError().text();
    }

    // Отцепляем приаттаченную БД файла
    if( !srcQuery.exec( "DETACH trgdb;" ) )
        qDebug() << "[readDBFile2Memory]\n" << srcQuery.lastQuery() << "\n" << srcQuery.lastError().text();

    return true;
}

Так делать в первую очередь не хорошо, потому что лично мне не известно, гарантированно ли вся БД перенесётся (достаточно ли только таблиц, индексов и sqlite_sequence). Во-вторых, если записей много, то операция получается вовсе не быстрой, так как делается множество малополезных в данной ситуации построчных операций.

Теперь о том, как было бы неплохо реализовать по-нормальному.

На сайте SQLite есть статья, где рассказывается о том, что вообще для сохранения БД из памяти в файл и наоборот используется механизм резервирования. Приведены даже примеры как. Но, как становится понятно из статьи, делается это через SQLite API.

В Qt для таких случаев, вроде бы, предусмотрена возможность получить хэндлер API-шного подключения: QSqlDriver->Handle();
Есть в справке даже пример получения такого хэндлера.

Процитирую пример

 QSqlDatabase db = ...;
 QVariant v = db.driver()->handle();
 if (v.isValid() && qstrcmp(v.typeName(), "sqlite3*")==0) {
     // v.data() returns a pointer to the handle
     sqlite3 *handle = *static_cast<sqlite3 **>(v.data());
     if (handle != 0) { // check that it is not NULL
         ...
     }
 }

Но вот только для того, чтобы такой код откомпилировался, нужно подключить библиотеку SQLite. А вот как это сделать, чего-то мне найти не удалося.
Я нашёл ещё одну возможно полезную в данной ситуации статью. Там как раз вопрос про работу с SQLite API, и там у автора темы даже что-то вроде бы получилось.
Автор говорит, что ему помогло вот так:

The problem is solved by adding
the following line to the .pro file

LIBS += -L/usr/lib -lsqlite3

and added #include "sqlite3.h"

Судя по "/usr/lib" это, скорее всего, вариант для Linux. Также, скорее всего, SQLite там был установлен отдельно (коли в /usr/lib).

Собственно вопрос
Господа, не подскажете, а как можно подключить SQLite библиотеку под Windows? И обязательно ли это должна быть отдельная библиотека (а не из комплекта Qt)?

[Frigolem]

О! Получилось таки
Вот стоило спросить на форуме, как наконец докопался.

Можно использовать SQLite API, идущий в комплекте с Qt.
Оказалось, что никаких дополнительных библиотек делать не обязательно, достаточно просто приинклудить sqlite3.h, но при этом sqlite3.c соответственно нужно откомпилировать (оказывается, Qt без особых указаний на это его не откомпилирует).

Итак, действия примерно такие:

1. В .pro файл добавляем две строки:

INCLUDEPATH = $$[QT_INSTALL_PREFIX]/src/3rdparty/sqlite
SOURCES += $$[QT_INSTALL_PREFIX]/src/3rdparty/sqlite/sqlite3.c

2. В файл, где собираемся использовать SQLite API, добавляем:

#include <sqlite3.h>

3. После чего можно использовать примерно вот такую функцию для загрузки/сохранения SQLite БД из памяти / в память:

Нормальная функция сохранения/загрузки БД SQLite

/*
** This function is used to load the contents of a database file on disk
** into the "main" database of open database connection pInMemory, or
** to save the current contents of the database opened by pInMemory into
** a database file on disk. pInMemory is probably an in-memory database,
** but this function will also work fine if it is not.
**
** Parameter zFilename points to a nul-terminated string containing the
** name of the database file on disk to load from or save to. If parameter
** isSave is non-zero, then the contents of the file zFilename are
** overwritten with the contents of the database opened by pInMemory. If
** parameter isSave is zero, then the contents of the database opened by
** pInMemory are replaced by data loaded from the file zFilename.
**
** If the operation is successful, SQLITE_OK is returned. Otherwise, if
** an error occurs, an SQLite error code is returned.
*/
bool QOrg::sqliteDBMemFile( QSqlDatabase memdb, QString filename, bool save )
{
    bool state = false;
    QVariant v = memdb.driver()->handle();
    if( v.isValid() && qstrcmp(v.typeName(),"sqlite3*") == 0 )
    {
        // v.data() returns a pointer to the handle
        sqlite3 * handle = *static_cast<sqlite3 **>(v.data());
        if( handle != 0 ) // check that it is not NULL
        {
            sqlite3 * pInMemory = handle;
            const char * zFilename = filename.toLocal8Bit().data();
            int rc;                   /* Function return code */
            sqlite3 *pFile;           /* Database connection opened on zFilename */
            sqlite3_backup *pBackup;  /* Backup object used to copy data */
            sqlite3 *pTo;             /* Database to copy to (pFile or pInMemory) */
            sqlite3 *pFrom;           /* Database to copy from (pFile or pInMemory) */

            /* Open the database file identified by zFilename. Exit early if this fails
            ** for any reason. */
            rc = sqlite3_open( zFilename, &pFile );
            if( rc==SQLITE_OK ){

              /* If this is a 'load' operation (isSave==0), then data is copied
              ** from the database file just opened to database pInMemory.
              ** Otherwise, if this is a 'save' operation (isSave==1), then data
              ** is copied from pInMemory to pFile.  Set the variables pFrom and
              ** pTo accordingly. */
              pFrom = ( save ? pInMemory : pFile);
              pTo   = ( save ? pFile     : pInMemory);

              /* Set up the backup procedure to copy from the "main" database of
              ** connection pFile to the main database of connection pInMemory.
              ** If something goes wrong, pBackup will be set to NULL and an error
              ** code and  message left in connection pTo.
              **
              ** If the backup object is successfully created, call backup_step()
              ** to copy data from pFile to pInMemory. Then call backup_finish()
              ** to release resources associated with the pBackup object.  If an
              ** error occurred, then  an error code and message will be left in
              ** connection pTo. If no error occurred, then the error code belonging
              ** to pTo is set to SQLITE_OK.
              */
              pBackup = sqlite3_backup_init(pTo, "main", pFrom, "main");
              if( pBackup ){
                (void)sqlite3_backup_step(pBackup, -1);
                (void)sqlite3_backup_finish(pBackup);
              }
              rc = sqlite3_errcode(pTo);
            }

            /* Close the database connection opened on database file zFilename
            ** and return the result of this function. */
            (void)sqlite3_close(pFile);

            if( rc == SQLITE_OK ) state = true;
        }
    }
    return state;
}

Получилось и кроссплатформенно вроде, и надёжно. МангаджусЪ.

[igor_bogomolov]

Frigolem, может оформите заметку на нашу Wiki?

[Frigolem]

Попробую написать заметки в раздел Qt/FAQ

[Frigolem]

Бага обнаружилась.

Вот так делать нельзя:

const char * zFilename = filename.toLocal8Bit().data();

Я чегой-то думал, что суровый будничный стек не уничтожит промежуточно создаваемый QByteArray ( его делает метод .toLocal8Bit() ) и я успею без проблем по-использовать указатель на строчку. Ан-нет, массив убивается уже походу следующих операций, наверно в результате оптимизации.

Поэтому, предлагаю в коде поста №2 сделать замену приведённой строки на эти две:

QByteArray array = filename.toLocal8Bit();
const char * zFilename = array.data();

В wiki также поправлю...

[Litkevich Yuriy]

Я чегой-то думал, что суровый будничный стек не уничтожит промежуточно создаваемый QByteArray ( его делает метод .toLocal8Bit() ) и я успею без проблем по-использовать указатель на строчку. Ан-нет, массив убивается уже походу следующих операций, наверно в результате оптимизации.

это поведение стандартное. Т.к. QByteArray объект временный.

Также ожидай проблем и тут:

const char * modifiedFilename =  someFunction(filename.toLocal8Bit().data());

Т.к. на следующей строке после этой функции временный объект умрёт