Tag Archives: C++

Making one Qt5 application to connect to both MySQL server and embedded

QT5 usually builds with a MySQL driver that connect to a server. The documentation also provides information on how to achieve an embedded connection however, there is no much information on how to have a Qt application using both the embedded and server connection. The example below show how to achieve it:

MySQL Dual Connection (Embedded / Server) with QT5

Cheers,

QLands

Working out a table insertion/deletion order from a MySQL Schema

Sometimes for scripting purposes is good to produce a table insertion/deletion order for a MySQL schema. For InnoDB schemas this is important to satisfy constraints without the need of disabling foreign key checks.

Working out a table insertion/deletion order in MySQL is no so straight forward because MySQL does not store anywhere a position or index in which a table was created. The order must be collected from the relationships stored in the information schema.

The following snippet in C++ with Qt generates an insertion order.

#include <QStringList>
#include <QSqlDatabase>
#include <QSqlQuery>
#include <QVariant>

QStringList tables; //List of tables
QSqlQuery references; //Query to references

void log(QString message)
{
    QString temp;
    temp = message + "\n";
    printf(temp.toLocal8Bit().data());
}

//Get the minimum index of parent of a table
int getMaxParentIndex(QString table)
{
    int res;
    res = -1;
    references.first();
    while (references.isValid())
    {
        if (references.value(0).toString() == table)
        {
            if (tables.indexOf(references.value(1).toString()) >= res)
                res = tables.indexOf(references.value(1).toString());
        }
        references.next();
    }
    return res;
}

//Fillup the QStringList of tables in insertion order
int getTableOrder(QSqlDatabase db)
{
    QSqlQuery qtables(db);
    QString sql;
    //Get all the tables that are parents
    sql = "SELECT DISTINCT REFERENCED_TABLE_NAME FROM information_schema.KEY_COLUMN_USAGE";
    sql = sql + " WHERE table_schema = '" + db.databaseName() + "' AND";
    sql = sql + " REFERENCED_TABLE_NAME is not null";

    if (qtables.exec(sql))
    {
        while (qtables.next())
        {
            tables.append(qtables.value(0).toString());
        }
    }
    else
        return 1;

    //Get only neccesary references to workout the order in parent tables that are also parents
    sql = "SELECT table_name,REFERENCED_TABLE_NAME FROM";
    sql = sql + " information_schema.KEY_COLUMN_USAGE WHERE";
    sql = sql + " table_schema = '" + db.databaseName() + "' AND";
    sql = sql + " REFERENCED_TABLE_NAME is not null AND";
    sql = sql + " table_name IN (SELECT DISTINCT REFERENCED_TABLE_NAME FROM information_schema.KEY_COLUMN_USAGE";
    sql = sql + " WHERE table_schema = '" + db.databaseName() + "' AND";
    sql = sql + " REFERENCED_TABLE_NAME is not null)";
    sql = sql + " group by table_name,REFERENCED_TABLE_NAME";
    references = QSqlQuery(db);
    references.exec(sql);

    //Because a parent table can also be a child we need to organize the list so childs are after parents
    int pos;
    int parentIndex;
    QString table;
    for (pos = 0; pos <= tables.count()-1; pos++)
    {
        //Get the maximum index if the parent of this table
        parentIndex = getMaxParentIndex(tables[pos]);
        if (pos < parentIndex) //If the position of this table is before the max parent index
        {
            table = tables[pos]; //Get the table name
            tables.removeAt(pos); //Remove the table from the list
            tables.insert(parentIndex+1,table); //Insert it back in the position after the last parent
            pos = 0; //Back to beginning of the process
        }

    }

    //Append the tables that have no childs
    sql = "SELECT DISTINCT information_schema.tables.table_name FROM information_schema.tables WHERE";
    sql = sql + " information_schema.tables.table_schema = '" + db.databaseName() + "' AND";
    sql = sql + " table_type = 'BASE TABLE' AND";
    sql = sql + " information_schema.tables.table_name NOT IN";
    sql = sql + " (SELECT DISTINCT REFERENCED_TABLE_NAME FROM information_schema.KEY_COLUMN_USAGE";
    sql = sql + " WHERE table_schema = '" + db.databaseName() + "' AND";
    sql = sql + " REFERENCED_TABLE_NAME is not null)";
    if (qtables.exec(sql))
    {
        while (qtables.next())
        {
            tables.append(qtables.value(0).toString());
        }
    }
    //Display the list
    for (pos = 0; pos <= tables.count()-1; pos++)
    {
        log(tables[pos]);
    }

    return 0;
}

int main(int argc, char *argv[])
{
    {
        QSqlDatabase db = QSqlDatabase::addDatabase("QMYSQL");
        db.setHostName("localhost");
        db.setPort(3306);
        db.setDatabaseName("MySchema");
        db.setUserName("MyUser");
        db.setPassword("MyPass");
        if (db.open())
        {
            getTableOrder(db);
        }
        else
        {
            log("Cannot connect to database");
            log(db.lastError().databaseText());
            return 1;
        }
    }

    return 0;
}