src/code/tagdb.cpp

0001 /*
0002  *   Copyright 2018 Camilo Higuita <milo.h@aol.com>
0003  *
0004  *   This program is free software; you can redistribute it and/or modify
0005  *   it under the terms of the GNU Library General Public License as
0006  *   published by the Free Software Foundation; either version 2, or
0007  *   (at your option) any later version.
0008  *
0009  *   This program is distributed in the hope that it will be useful,
0010  *   but WITHOUT ANY WARRANTY; without even the implied warranty of
0011  *   MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
0012  *   GNU General Public License for more details
0013  *
0014  *   You should have received a copy of the GNU Library General Public
0015  *   License along with this program; if not, write to the
0016  *   Free Software Foundation, Inc.,
0017  *   51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA.
0018  */
0019
0020 #include "tagdb.h"
0021
0022 #include <QDebug>
0023 #include <QDir>
0024 #include <QList>
0025 #include <QSqlDriver>
0026 #include <QSqlError>
0027 #include <QSqlQuery>
0028 #include <QSqlRecord>
0029 #include <QUuid>
0030
0031 TAGDB::TAGDB()
0032     : QObject(nullptr)
0033 {
0034     QDir collectionDBPath_dir(TAG::TaggingPath);
0035     if (!collectionDBPath_dir.exists())
0036         collectionDBPath_dir.mkpath(QStringLiteral("."));
0037
0038     this->name = QUuid::createUuid().toString();
0039     if (!FMH::fileExists(QUrl::fromLocalFile(TAG::TaggingPath + TAG::DBName))) {
0040         this->openDB(this->name);
0041         qDebug() << "Collection doesn't exists, trying to create it" << TAG::TaggingPath + TAG::DBName;
0042         this->prepareCollectionDB();
0043     } else
0044         this->openDB(this->name);
0045 }
0046
0047 TAGDB::~TAGDB()
0048 {
0049     qDebug() << "CLOSING THE TAGGING DATA BASE";
0050     this->m_db.close();
0051 }
0052
0053 void TAGDB::openDB(const QString &name)
0054 {
0055     if (!QSqlDatabase::contains(name)) {
0056         this->m_db = QSqlDatabase::addDatabase(QStringLiteral("QSQLITE"), name);
0057         this->m_db.setDatabaseName(TAG::TaggingPath + TAG::DBName);
0058     }
0059
0060     if (!this->m_db.isOpen()) {
0061         if (!this->m_db.open())
0062             qDebug() << "ERROR OPENING DB" << this->m_db.lastError().text() << m_db.connectionName();
0063     }
0064     auto query = this->getQuery(QStringLiteral("PRAGMA synchronous=OFF"));
0065     query.exec();
0066 }
0067
0068 void TAGDB::prepareCollectionDB() const
0069 {
0070     QSqlQuery query(this->m_db);
0071
0072     QFile file(QStringLiteral(":/script.sql"));
0073
0074     if (!file.exists()) {
0075         QString log = QStringLiteral("Fatal error on build database. The file '");
0076         log.append(file.fileName() + QStringLiteral("' for database and tables creation query cannot be not found!"));
0077         qDebug() << log;
0078         return;
0079     }
0080
0081     if (!file.open(QIODevice::ReadOnly)) {
0082         qDebug() << QStringLiteral("Fatal error on try to create database! The file with sql queries for database creation cannot be opened!");
0083         return;
0084     }
0085
0086     bool hasText;
0087     QString line;
0088     QByteArray readLine;
0089     QString cleanedLine;
0090     QStringList strings;
0091
0092     while (!file.atEnd()) {
0093         hasText = false;
0094         line = QStringLiteral("");
0095         readLine = "";
0096         cleanedLine = QStringLiteral("");
0097         strings.clear();
0098         while (!hasText) {
0099             readLine = file.readLine();
0100             cleanedLine = QString::fromStdString(readLine.trimmed().toStdString());
0101             strings = cleanedLine.split(QStringLiteral("--"));
0102             cleanedLine = strings.at(0);
0103             if (!cleanedLine.startsWith(QStringLiteral("--")) && !cleanedLine.startsWith(QStringLiteral("DROP")) && !cleanedLine.isEmpty())
0104                 line += cleanedLine;
0105             if (cleanedLine.endsWith(QStringLiteral(";")))
0106                 break;
0107             if (cleanedLine.startsWith(QStringLiteral("COMMIT")))
0108                 hasText = true;
0109         }
0110         if (!line.isEmpty()) {
0111             if (!query.exec(line)) {
0112                 qDebug() << "exec failed" << query.lastQuery() << query.lastError();
0113             }
0114
0115         } else
0116             qDebug() << "exec wrong" << query.lastError();
0117     }
0118     file.close();
0119 }
0120
0121 bool TAGDB::checkExistance(const QString &tableName, const QString &searchId, const QString &search) const
0122 {
0123     const auto queryStr = QString(QStringLiteral("SELECT %1 FROM %2 WHERE %3 = \"%4\"")).arg(searchId, tableName, searchId, search);
0124     return this->checkExistance(queryStr);
0125 }
0126
0127 bool TAGDB::checkExistance(const QString &queryStr) const
0128 {
0129     qDebug() << "CHECKIGN QUERY TAG" << queryStr;
0130     auto query = this->getQuery(queryStr);
0131
0132     if (query.exec()) {
0133         if (query.next())
0134             return true;
0135     } else
0136         qDebug() << query.lastError().text();
0137
0138     return false;
0139 }
0140
0141 QSqlQuery TAGDB::getQuery(const QString &queryTxt) const
0142 {
0143     QSqlQuery query(queryTxt, this->m_db);
0144     return query;
0145 }
0146
0147 QSqlQuery TAGDB::getQuery() const
0148 {
0149     QSqlQuery query(this->m_db);
0150     return query;
0151 }
0152
0153 bool TAGDB::insert(const QString &tableName, const QVariantMap &insertData) const
0154 {
0155     if (tableName.isEmpty()) {
0156         qDebug() << QStringLiteral("Fatal error on insert! The table name is empty!");
0157         return false;
0158
0159     } else if (insertData.isEmpty()) {
0160         qDebug() << QStringLiteral("Fatal error on insert! The insertData is empty!");
0161         return false;
0162     }
0163
0164     QStringList strValues;
0165     QStringList fields = insertData.keys();
0166     QVariantList values = insertData.values();
0167     int totalFields = fields.size();
0168     for (int i = 0; i < totalFields; ++i)
0169         strValues.append(QStringLiteral("?"));
0170
0171     QString sqlQueryString = QStringLiteral("INSERT INTO ") + tableName + QStringLiteral(" (") + QString(fields.join(QStringLiteral(","))) + QStringLiteral(") VALUES(") + QString(strValues.join(QStringLiteral(","))) + QStringLiteral(")");
0172     QSqlQuery query(this->m_db);
0173     query.prepare(sqlQueryString);
0174
0175     int k = 0;
0176
0177     for(const QVariant &value : values)
0178         query.bindValue(k++, value);
0179
0180     return query.exec();
0181 }
0182
0183 bool TAGDB::update(const QString &tableName, const FMH::MODEL &updateData, const QVariantMap &where) const
0184 {
0185     if (tableName.isEmpty()) {
0186         qDebug() << QStringLiteral("Fatal error on insert! The table name is empty!");
0187         return false;
0188     } else if (updateData.isEmpty()) {
0189         qDebug() << QStringLiteral("Fatal error on insert! The insertData is empty!");
0190         return false;
0191     }
0192
0193     QStringList set;
0194     const auto updateKeys = updateData.keys();
0195     for (const auto &key : updateKeys)
0196     {
0197         set.append(FMH::MODEL_NAME[key] + QStringLiteral(" = '") + updateData[key] + QStringLiteral("'"));
0198     }
0199
0200     QStringList condition;
0201     const auto whereKeys = where.keys();
0202     for (const auto &key : whereKeys)
0203     {
0204          condition.append(key + QStringLiteral(" = '") + where[key].toString() + QStringLiteral("'"));
0205     }
0206
0207     QString sqlQueryString = QStringLiteral("UPDATE ") + tableName + QStringLiteral(" SET ") + QString(set.join(QStringLiteral(","))) + QStringLiteral(" WHERE ") + QString(condition.join(QStringLiteral(",")));
0208     auto query = this->getQuery(sqlQueryString);
0209     qDebug() << sqlQueryString;
0210     return query.exec();
0211 }
0212
0213 bool TAGDB::update(const QString &table, const QString &column, const QVariant &newValue, const QVariant &op, const QString &id) const
0214 {
0215     auto queryStr = QString(QStringLiteral("UPDATE %1 SET %2 = \"%3\" WHERE %4 = \"%5\"")).arg(table, column, newValue.toString().replace(QStringLiteral("\""), QStringLiteral("\"\"")), op.toString(), id);
0216     auto query = this->getQuery(queryStr);
0217     return query.exec();
0218 }
0219
0220 bool TAGDB::remove(const QString &tableName, const FMH::MODEL &removeData) const
0221 {
0222     if (tableName.isEmpty()) {
0223         qDebug() << QStringLiteral("Fatal error on removing! The table name is empty!");
0224         return false;
0225
0226     } else if (removeData.isEmpty()) {
0227         qDebug() << QStringLiteral("Fatal error on insert! The removeData is empty!");
0228         return false;
0229     }
0230
0231     QString strValues;
0232     auto i = 0;
0233     const auto keys = removeData.keys();
0234     for (const auto &key : keys) {
0235         strValues.append(QString(QStringLiteral("%1 = \"%2\"")).arg(FMH::MODEL_NAME[key], removeData[key]));
0236         i++;
0237
0238         if (removeData.size() > 1 && i < removeData.size())
0239         {
0240             strValues.append(QStringLiteral(" AND "));
0241         }
0242     }
0243
0244     QString sqlQueryString = QStringLiteral("DELETE FROM ") + tableName + QStringLiteral(" WHERE ") + strValues;
0245     qDebug() << sqlQueryString;
0246
0247     return this->getQuery(sqlQueryString).exec();
0248 }
0249
0250 const QSqlDatabase & TAGDB::db() const
0251 {
0252     return this->m_db;
0253 }