DBTest - main.cpp

#include "main.h"

#include <QApplication>
#include <QMessageBox>
#include <QPlainTextEdit>
#include <QSqlDatabase>
#include <QSqlError>
#include <QSqlQuery>
#include <QSqlQueryModel>
#include <QString>
#include <QTableView>

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

    QPlainTextEdit log;

    log.setReadOnly(true);
    log.resize(800, 800);

    // try to connect
    QSqlDatabase db = QSqlDatabase::addDatabase("QMYSQL");

    db.setHostName(SQL_HOSTNAME);
    db.setPort(SQL_PORT);
    db.setDatabaseName(SQL_DATABASE);
    db.setUserName(SQL_USERNAME);
    db.setPassword(SQL_PASSWORD);

    if (!db.open())
        log.appendPlainText(QString("Error during connection to SQL DB:\n%1").arg(db.lastError().text()));
    else {
        log.appendPlainText(QString("Successfull connection to SQL DB"));

        // just an example string to search
        QString search_txt = "05";

        // SQL SELECT query to work with DATE types
        QString basic_query_dates =
            "SELECT DISTINCT e.id, e.name, e.type, v.ts, v.upto, v.d_left "
            "FROM Equipment AS e "
            "INNER JOIN Users AS u ON u.id = e.responsible_id "
            "LEFT JOIN (SELECT equipment_id, MAX(ts) AS ts, MAX(upto) AS upto, DATEDIFF(MAX(upto), NOW()) AS d_left FROM EquipmentVerifications GROUP BY equipment_id) AS v ON v.equipment_id = e.id "
            "LEFT JOIN Equipment_Supplies AS es ON es.equipment_id = e.id "
            "LEFT JOIN Supplies AS s ON s.id = es.supply_id "
            "WHERE %1 "
            "ORDER BY e.name";

        // 1. try to use in-place query
        QSqlQuery q1(basic_query_dates.arg("e.name LIKE '%%1%' OR s.name LIKE '%%1%' OR s.description LIKE '%%1%' OR s.sn LIKE '%%1%'").arg(search_txt));

        // dump output
        log.appendPlainText(QString("\nQUERY DEBUG, DATE TYPES, IN-PLACE QUERY\n======"));

        while (q1.next())
            log.appendPlainText(QString("%1\t%2\t%3\t%4\t%5\t%6").arg(
                q1.value(0).toString(),
                q1.value(1).toString(),
                q1.value(2).toString(),
                q1.value(3).toString(),
                q1.value(4).toString(),
                q1.value(5).toString()));

        q1.finish();

        // 2. try to use parameters binding
        QSqlQuery q2;

        q2.prepare(basic_query_dates.arg("e.name LIKE :search OR s.name LIKE :search OR s.description LIKE :search OR s.sn LIKE :search"));
        q2.bindValue(":search", QString("%%1%").arg(search_txt));
        q2.exec();

        // dump output
        log.appendPlainText(QString("\nQUERY DEBUG, DATE TYPES, PREPARED QUERY\n======"));

        while (q2.next())
            log.appendPlainText(QString("%1\t%2\t%3\t%4\t%5\t%6").arg(
                q2.value(0).toString(),
                q2.value(1).toString(),
                q2.value(2).toString(),
                q2.value(3).toString(),
                q2.value(4).toString(),
                q2.value(5).toString()));

        q2.finish();

        // 3. try to use model + in-place query
        QSqlQueryModel *model1 = new QSqlQueryModel;

        model1->setQuery(basic_query_dates.arg("e.name LIKE '%%1%' OR s.name LIKE '%%1%' OR s.description LIKE '%%1%' OR s.sn LIKE '%%1%'").arg(search_txt));

        // dump output
        log.appendPlainText(QString("\nMODEL+QUERY DEBUG, DATE TYPES, IN-PLACE QUERY\n======"));

        for (int i = 0; i < model1->rowCount(); i++) {
            QString tmp;

            for (int j = 0; j < model1->columnCount(); j++)
                tmp += model1->index(i, j).data(Qt::DisplayRole).toString() + "\t";

            log.appendPlainText(tmp);
        }

        // 4. try to use model + prepared query
        QSqlQueryModel *model2 = new QSqlQueryModel;
        QSqlQuery q3;

        q3.prepare(basic_query_dates.arg("e.name LIKE :search OR s.name LIKE :search OR s.description LIKE :search OR s.sn LIKE :search"));
        q3.bindValue(":search", QString("%%1%").arg(search_txt));
        q3.exec();

        model2->setQuery(std::move(q3));

        // dump output
        log.appendPlainText(QString("\nMODEL+QUERY DEBUG, DATE TYPES, PREPARED QUERY\n======"));

        for (int i = 0; i < model2->rowCount(); i++) {
            QString tmp;

            for (int j = 0; j < model2->columnCount(); j++)
                tmp += model2->index(i, j).data(Qt::DisplayRole).toString() + "\t";

            log.appendPlainText(tmp);
        }

        // 5. try to use view + model + in-place query;
        QTableView *view1 = new QTableView;
        view1->setModel(model1);
        view1->resize(600, 200);
        view1->show();
        view1->setWindowTitle(QString("DATE + IN-PLACE"));

        // 6. try to use view + model + prepared query;
        QTableView *view2 = new QTableView;
        view2->setModel(model2);
        view2->resize(600, 200);
        view2->show();
        view2->setWindowTitle(QString("DATE + PREPARED"));

        // SQL SELECT query to work with INT types (2234 is the replacement equivalent of NOW())
        QString basic_query_ints =
            "SELECT DISTINCT e.id, e.name, e.type, v.ts, v.upto, v.d_left "
            "FROM Equipment AS e "
            "INNER JOIN Users AS u ON u.id = e.responsible_id "
            "LEFT JOIN (SELECT equipment_id, MAX(ts) AS ts, MAX(upto) AS upto, (MAX(upto) - 2234) AS d_left FROM EquipmentVerifications2 GROUP BY equipment_id) AS v ON v.equipment_id = e.id "
            "LEFT JOIN Equipment_Supplies AS es ON es.equipment_id = e.id "
            "LEFT JOIN Supplies AS s ON s.id = es.supply_id "
            "WHERE %1 "
            "ORDER BY e.name";

        // 7. try to use in-place query
        QSqlQuery q4(basic_query_ints.arg("e.name LIKE '%%1%' OR s.name LIKE '%%1%' OR s.description LIKE '%%1%' OR s.sn LIKE '%%1%'").arg(search_txt));

        // dump output
        log.appendPlainText(QString("\nQUERY DEBUG, INT TYPES, IN-PLACE QUERY\n======"));

        while (q4.next())
            log.appendPlainText(QString("%1\t%2\t%3\t%4\t%5\t%6").arg(
                q4.value(0).toString(),
                q4.value(1).toString(),
                q4.value(2).toString(),
                q4.value(3).toString(),
                q4.value(4).toString(),
                q4.value(5).toString()));

        q4.finish();

        // 8. try to use parameters binding
        QSqlQuery q5;

        q5.prepare(basic_query_ints.arg("e.name LIKE :search OR s.name LIKE :search OR s.description LIKE :search OR s.sn LIKE :search"));
        q5.bindValue(":search", QString("%%1%").arg(search_txt));
        q5.exec();

        // dump output
        log.appendPlainText(QString("\nQUERY DEBUG, INT TYPES, PREPARED QUERY\n======"));

        while (q5.next())
            log.appendPlainText(QString("%1\t%2\t%3\t%4\t%5\t%6").arg(
                q5.value(0).toString(),
                q5.value(1).toString(),
                q5.value(2).toString(),
                q5.value(3).toString(),
                q5.value(4).toString(),
                q5.value(5).toString()));

        q5.finish();

        // 9. try to use model + in-place query
        QSqlQueryModel *model3 = new QSqlQueryModel;

        model3->setQuery(basic_query_ints.arg("e.name LIKE '%%1%' OR s.name LIKE '%%1%' OR s.description LIKE '%%1%' OR s.sn LIKE '%%1%'").arg(search_txt));

        // dump output
        log.appendPlainText(QString("\nMODEL+QUERY DEBUG, INT TYPES, IN-PLACE QUERY\n======"));

        for (int i = 0; i < model3->rowCount(); i++) {
            QString tmp;

            for (int j = 0; j < model3->columnCount(); j++)
                tmp += model3->index(i, j).data(Qt::DisplayRole).toString() + "\t";

            log.appendPlainText(tmp);
        }

        // 10. try to use model + prepared query
        QSqlQueryModel *model4 = new QSqlQueryModel;
        QSqlQuery q6;

        q6.prepare(basic_query_ints.arg("e.name LIKE :search OR s.name LIKE :search OR s.description LIKE :search OR s.sn LIKE :search"));
        q6.bindValue(":search", QString("%%1%").arg(search_txt));
        q6.exec();

        model4->setQuery(std::move(q6));

        // dump output
        log.appendPlainText(QString("\nMODEL+QUERY DEBUG, INT TYPES, PREPARED QUERY\n======"));

        for (int i = 0; i < model4->rowCount(); i++) {
            QString tmp;

            for (int j = 0; j < model4->columnCount(); j++)
                tmp += model4->index(i, j).data(Qt::DisplayRole).toString() + "\t";

            log.appendPlainText(tmp);
        }

        // 11. try to use view + model + in-place query;
        QTableView *view3 = new QTableView;
        view3->setModel(model3);
        view3->resize(600, 200);
        view3->show();
        view3->setWindowTitle(QString("INT + IN-PLACE"));

        // 12. try to use view + model + prepared query;
        QTableView *view4 = new QTableView;
        view4->setModel(model4);
        view4->resize(600, 200);
        view4->show();
        view4->setWindowTitle(QString("INT + PREPARED"));
    }

    log.show();

    return a.exec();
}