[SOLVED] How can I dump database tables with Qt?

[panosk]

Hi,

Is there a way to dump tables from an Sqlite database (or the whole database) into a text file using Qt's QSqlDatabase, QSqlQuery, and friends? This file will be used to feed data to another database system.

Thanks in advance.

[SergioDanielG]

Hi panosk.
Maybe you can see how "qsqlitebrowser":http://sqlitebrowser.sourceforge.net/ do it.
From qsqlitebrowser

bq. What it is
SQLite Database Browser is a freeware, public domain, open source visual tool used to create, design and edit database files compatible with SQLite.

• Import and export databases from/to SQL dump files
  bq.

Hope it's util.

Regards.

[panosk]

Hi SergioDanielG,

Thanks for the link, but I need to do this programmatically from within my code. As a last resort, I may try to create a QProcess that will run the sqlite3 client utility and then use ".dump", but I would like to avoid it if there is another way.

[SergioDanielG]

My idea is: Download source and read the code of qsqlitebrowser and watch how qsqlitebrowser dump tables.
Or use "QSqlDatabase":http://qt-project.org/doc/qt-5.0/qtsql/qsqldatabase.html , "QSqlQuery":http://qt-project.org/doc/qt-5.0/qtsql/qsqlquery.html , etc and sql commands like
@
select * from sqlite_master;
@

to search database's tables structure, then filter data of tables and then write to a file.
Hope it's util.
Regards.

[panosk]

Hi,

I finally came up with this solution which seems to work for my purposes and the resulting file imports fine into PostgreSQL. Note that I don't need the schema, only the data, but it should be easy to include schemas too. As the code is very fresh, any comments, suggestions, or improvements are most welcome.

@
void DbManager::exportTables()
{
QHash<QString,QStringList> tablesWithFields; //It holds the table name and its fields
QStringList tables = sourceDb.tables();
QSqlQuery query(sourceDb);
foreach(QString table,tables) {
query.exec(QString("PRAGMA TABLE_INFO(%1)").arg(table));
QStringList fields;
while(query.next()) {
fields << query.value(1).toString();
}
tablesWithFields.insert(table,fields);
}
QFile f(QDir::homePath() + "/myDump.sql");
f.open(QIODevice::Append | QIODevice::Text);
QTextStream streamer(&f);

/*If constraints can't be dropped in the target database, some reordering of the INSERT statements may be needed
*/
QStringList sortedTables = tablesWithFields.keys();
sortedTables.move(sorted.indexOf("table1"),0);
...

streamer << "BEGIN;\n";
foreach(const QString &table,sortedTables) {
    if(table=="sqlite_sequence" /*|| table=="table4", etc*/) continue;
    QString statement = QString("INSERT INTO %1 VALUES('").arg(table);
    QStringList fields = tablesWithFields.value(table);
    QString fieldsString = fields.join(",");
    query.exec&#40;QString("SELECT %1 FROM %2"&#41;.arg(fieldsString).arg(table));
    if(!query.next()) continue;
    query.previous();
    while(query.next()) {
        for(int i=0; i < fields.size(); ++i) {
            QString value = query.value(i).toString();
            value.replace("'","''"); //Handle single quotes inside strings
            if(value.isEmpty()) {
                value = "NULL";
                statement.chop(1); //NULL should not appear inside quotes
                statement.append(value+",'");
            } else {
            statement.append(value+"','");
            }
        }
        statement.chop(2); //Remove comma and single quote from the end of value group
        statement.append("),('"); //Close the value group and start a new one
    }
    statement.chop(3); //Remove comma, opening parenthesis and single quote from the end
    streamer << statement << ";\n"; //Complete the INSERT statement
}
streamer << "COMMIT;";
f.close();

}
@

And then I batch execute the sql file like this:

@
...
exportTables();
QSqlQuery query(targetDb);
QFile f(QDir::homePath()+"/myDump.sql");
f.open(QIODevice::ReadOnly | QIODevice::Text);
if(!query.exec(f.readAll()))
qCritical() << "Can't execute sql file: " << query.lastError().text();
@