Command-line tool that converts ODBC-database from DSN to DSN (primarily made for Paradox to SQLite conversion)

odbc_bridge.cpp

#include <QtCore/QCoreApplication>
#include <QtCore/QDebug>
#include <QtCore/QTextCodec>
#include <QtCore/QTime>
#include <QtSql/QSqlDatabase>
#include <QtSql/QSqlQuery>
#include <QtSql/QSqlError>
#include <QtSql/QSqlDriver>
#include <QtSql/QSqlTableModel>
#include <QtSql/QSqlRecord>
#include <QtSql/QSqlField>
#include <QtSql/QSqlIndex>

// Paradox connection string keys https://docs.microsoft.com/ru-ru/sql/odbc/microsoft/sqlconfigdatasource-paradox-driver?view=sql-server-2017
// Paradox data types https://docs.microsoft.com/ru-ru/sql/odbc/microsoft/paradox-data-types?view=sql-server-2017

/* usage examples:
 * odbc_bridge "DRIVER={Microsoft Paradox Driver (*.db )};FIL={Paradox 5.X};DBQ=D:\Temp\p_i" "DRIVER={SQLite3 ODBC Driver};Database=D:\Temp\sqlite_test.sqlite"
 * odbc_bridge "DRIVER={SQLite3 ODBC Driver};Database=D:\Temp\sqlite_test.sqlite" "DRIVER={Microsoft Paradox Driver (*.db )};FIL={Paradox 5.X};DBQ=D:\Temp\p_o"
 */

const QString tmp_id = "\"tmp_id\"";

int main(int argc, char *argv[])
{
    QCoreApplication a(argc, argv);

    QTextCodec::setCodecForLocale(QTextCodec::codecForName("cp866"));
    qDebug() << "ODBC bridge";

    if (argc < 3)
    {
        qDebug() << "Usage: odbc_bridge dsn_src dsn_dst [table1, table2, ...]";
        return 0;
    }

    QTime database_time;
    database_time.start();
    qDebug() << "Converting database" << QString(argv[1]) << "to" << QString(argv[2]) << "...";

    QSqlDatabase db_src = QSqlDatabase::addDatabase("QODBC", "src");
    db_src.setDatabaseName(argv[1]);
    if (!db_src.open())
    {
        qDebug() << "Can't open source database" << QString(argv[1]);
        return 0;
    }

    QSqlDatabase db_dst = QSqlDatabase::addDatabase("QODBC", "dst");
    db_dst.setDatabaseName(argv[2]);
    if (!db_dst.open())
    {
        qDebug() << "Can't open destination database" << QString(argv[2]);
        return 0;
    }

    QStringList db_tables = db_src.tables();
    QStringList tables;
    if (argc > 3)
    {
        for (int i = 3; i < argc; i++)
        {
            QString table_name(argv[i]);
            if (!db_tables.contains(table_name, Qt::CaseInsensitive))
            {
                qDebug() << "Can't find table" << table_name;
                continue;
            }
            tables << table_name;
        }
    }
    else
    {
         tables = db_tables;
    }

    if (!tables.isEmpty())
    {
        qDebug() << "Tables to convert:" << tables.join(", ");
    }

    foreach (QString table_name, tables)
    {
        QTime table_time;
        table_time.start();
        qDebug() << "Converting table" << table_name << "...";
        qDebug() << "Extracting columns and types";
        QStringList fields, field_names;
        QList<QVariant::Type> field_types;
        QSqlRecord table_rec = db_src.record(table_name);
        if (!table_rec.isEmpty())
        {
            for (int col = 0; col < table_rec.count(); col++)
            {
                QSqlField field = table_rec.field(col);
                QString field_type;
                switch (field.type())
                {
                    case QVariant::Int:
                    case QVariant::UInt:
                    case QVariant::LongLong:
                    case QVariant::ULongLong:
                        if (field.length() <= 5)
                        {
                            field_type = "SMALLINT";
                        }
                        else
                        {
                            field_type = "INTEGER";
                        }
                        break;
                    case QVariant::Double:
                        field_type = "FLOAT";
                        break;
                    case QVariant::Date:
                        field_type = "DATE";
                        break;
                    case QVariant::DateTime:
                        field_type = "TIMESTAMP";
                        break;
                    case QVariant::Time:
                        field_type = "TIME";
                        break;
                    case QVariant::String:
                        field_type = "VARCHAR(" + QString::number(qMin(field.length(), 255)) + ")";
                        break;
                    default:
                        field_type = "VARCHAR";
                        break;
                }
//                qDebug() << field.type() << field.length();
                field_names << "\"" + field.name() + "\"";
                field_types << field.type();
                fields << field_names.back() + " " + field_type;
            }
        }
        else
        {
            qDebug() << "Can't get list of columns";
            continue;
        }
        QSqlIndex pkey = db_src.primaryIndex(table_name);
        bool has_tmp_id = pkey.isEmpty();
        if (has_tmp_id)
        {
            qDebug() << "No primary key found, initializing temporary index";
            field_names.prepend(tmp_id);
            fields.prepend(tmp_id + " INTEGER");
        }

        qDebug() << "Creating destination table";
        QSqlQuery query_dst(db_dst);
        query_dst.exec("DROP TABLE " + table_name);
        if (!query_dst.exec("CREATE TABLE " + table_name + " (" + fields.join(", ") + ")"))
        {
            qDebug() << "Can't create table" << table_name << ", error" << query_dst.lastError().text();
            qDebug() << query_dst.lastQuery();
            continue;
        }

        qDebug() << "Creating destination index";
        const QString index_field = has_tmp_id ? tmp_id : pkey.field(0).name();
        qDebug() << "Index field" << index_field;
        bool create_pkey = false;
        create_pkey |= query_dst.exec("CREATE UNIQUE INDEX " + table_name + " ON " + table_name + "(" + index_field + ")"); // paradox
        create_pkey |= query_dst.exec("CREATE UNIQUE INDEX " + table_name + "index ON " + table_name + "(" + index_field + ")"); //not paradox
        if (!create_pkey)
        {
            qDebug() << "Can't create index on table" << table_name;
        }

        qDebug() << "Selecting source data";
        QSqlTableModel model_src(0, db_src);
        model_src.setTable(table_name);
        if (!model_src.select())
        {
            qDebug() << "Can't select source table" << table_name << ", error" << model_src.lastError().text();
            continue;
        }
        while (model_src.canFetchMore())
        {
            model_src.fetchMore();
        }
        const int row_count = model_src.rowCount();
        qDebug() << "Inserting selected data to destination table, total row(s)" << row_count;
        for (int row = 0; row < row_count; row++)
        {
            QSqlRecord src_rec = model_src.record(row);
            QStringList values;
            for (int col = 0; col < src_rec.count(); col++)
            {
                QString value_str;
                QVariant value = src_rec.field(col).value().toString();
                QVariant::Type type = field_types[col];
                if (type == QVariant::String)
                {
                    value_str = "'" + value.toString() + "'";
                }
                else
                if (type == QVariant::Date)
                {
                    QDate d = value.toDate();
                    if (!d.isValid())
                    {
                        d = QDateTime::fromMSecsSinceEpoch(0).date();
                    }
                    value_str = d.toString("yyyy-MM-dd");
                    value_str = "'" + value_str + "'";
                }
                else
                if (type == QVariant::Time)
                {
                    QTime t = value.toTime();
                    if (!t.isValid())
                    {
                        t = QTime(0, 0, 0);
                    }
                    value_str = t.toString("hh:mm:ss");
                    value_str = "'" + value_str + "'";
                }
                else
                if (type == QVariant::DateTime)
                {
                    QDateTime dt = value.toDateTime();
                    if (!dt.date().isValid())
                    {
                        dt = QDateTime::fromMSecsSinceEpoch(0);
                    }
                    value_str = dt.toString("yyyy-MM-dd hh:mm:ss");
                    value_str = "'" + value_str + "'";
                }
                else
                {
                    value_str = value.toString();
                }
                values << value_str;
            }
            if (has_tmp_id)
            {
                values.prepend(QString::number(row + 1));
            }
            if (!query_dst.exec("INSERT INTO " + table_name + " (" + field_names.join(", ") + ") VALUES (" + values.join(", ") + ")"))
            {
                qDebug() << "Can't insert row" << QString::number(row) << "of table" << table_name << ", error" << query_dst.lastError().text();
                qDebug() << query_dst.lastQuery();
            }
        }
        if (has_tmp_id)
        {
            qDebug() << "Removing temporary index";
            query_dst.exec("DROP TABLE temporary_table");
            if (!query_dst.exec("CREATE TABLE temporary_table (" + fields.mid(1).join(", ") + ")"))
            {
                qDebug() << "Can't create table temporary_table" << ", error" << query_dst.lastError().text();
                qDebug() << query_dst.lastQuery();
                continue;
            }
            if (!query_dst.exec("INSERT INTO temporary_table (" + field_names.mid(1).join(", ") + ") SELECT " + field_names.mid(1).join(", ") + " FROM " + table_name))
            {
                qDebug() << "Can't INSERT INTO, error" << query_dst.lastError().text();
                qDebug() << query_dst.lastQuery();
                continue;
            }
            query_dst.exec("DROP TABLE " + table_name);
            if (!query_dst.exec("CREATE TABLE " + table_name + " (" + fields.mid(1).join(", ") + ")"))
            {
                qDebug() << "Can't create table" << table_name << ", error" << query_dst.lastError().text();
                qDebug() << query_dst.lastQuery();
                continue;
            }
            if (!query_dst.exec("INSERT INTO " + table_name + " (" + field_names.mid(1).join(", ") + ") SELECT " + field_names.mid(1).join(", ") + " FROM temporary_table"))
            {
                qDebug() << "Can't INSERT INTO, error" << query_dst.lastError().text();
                qDebug() << query_dst.lastQuery();
                continue;
            }
            query_dst.exec("DROP TABLE temporary_table");
        }
        qDebug() << "Table" << table_name << "converted, elapsed time" << QString::number(table_time.elapsed() / 1000.0, 'f', 1).toLatin1().constData() << "seconds";
    }
    qDebug() << "Database converted, elapsed time" << QString::number(database_time.elapsed() / 1000.0, 'f', 1).toLatin1().constData() << "seconds";

    return 0;
}