mpSorter/database.cpp

#include "database.h"

#include "DataHelper.h"

#include "QSqlQuery"
#include "QSqlError"

#include "QVector"
#include "QVariant"
#include "QFile"

#include "QMessageBox"
//#include "QDebug"


DataBase::DataBase(){ }
QSqlDatabase DataBase::db;
QVector<Group*> *DataBase::groups;
void DataBase::init(QString *dbFile)
{   
    if(db.databaseName().length() == 0)
        db = QSqlDatabase::addDatabase("QSQLITE");
    db.setDatabaseName(*dbFile);
}

void DataBase::selectTasks(QVector<Task*> *tasks){
    int rowCount = 0;
    db.open();

    QSqlQuery query("SELECT Count(*) FROM data");
    if(query.lastError().text().length() > 0)
    {
        db.close();
        QMessageBox::information(nullptr, "Ошибка открытия базы данных", query.lastError().text() + "\nубедитесь, что вы закрыли miniPoster перед тем, как открывать базу данных");
        return;
    }
    query.next();
    rowCount = query.value(0).toInt();
    if(rowCount == 0)
        return;  

    if(tasks->count() > 0)
        tasks->clear();

    tasks->reserve(rowCount);

    int index = 1;
    //CREATE TABLE data (id INTEGER PRIMARY KEY AUTOINCREMENT, id_group INTEGER, name VARCHAR (50) UNIQUE, Timer INT, Status BOOLEAN);
    query.exec("SELECT * FROM data;");
    while (query.next()) {
        Task *t      = new Task;
        t->id        = index++;
        t->group     = groupById(query.value(1).toInt());
        t->timer     = query.value(3).toInt();
        t->status    = query.value(4).toBool();
        t->name      = new QString(query.value(2).toString());
        t->firstName = t->name;

        tasks->append(t);
    }
    db.close();
}

void DataBase::selectGroups(QVector<Group*> *groups){
    db.open();
    int rowCount = 0;

    DataBase::groups = groups;

    QSqlQuery query("SELECT Count(*) FROM groupName");
    query.next();
    rowCount = query.value(0).toInt();
    if(rowCount == 0)
        return;   

    if(groups->count() > 0)
        groups->clear();

    groups->reserve(rowCount);

    //CREATE TABLE groupName (id INTEGER PRIMARY KEY AUTOINCREMENT, name VARCHAR (50) UNIQUE, color VARCHAR(15))
    if(query.exec("SELECT * FROM groupName;")){
        while (query.next()) {
            Group *g = new Group;
            g->id    = query.value(0).toInt();
            g->name  = new QString(query.value(1).toString());
            g->color = QColor(query.value(2).toString());

            groups->append(g);
        }
    }
    db.close();
}

void DataBase::saveTasks(QVector<Task*> *tasks){
    db.open();

    QSqlQuery query;
    query.exec("DELETE FROM data;");
    query.exec("VACUUM;");

    int index = 1;
    //CREATE TABLE data (id INTEGER PRIMARY KEY AUTOINCREMENT, id_group INTEGER, name VARCHAR (50) UNIQUE, Timer INT,Status BOOLEAN);
    for(auto &task : *tasks){
        query.exec(QString("INSERT INTO data (id,id_group,name,Timer,Status) \
                           VALUES (%1,%2,'%3',%4,%5)")
                           .arg(index++)
                           .arg(task->group->id)
                           .arg(*task->name)
                           .arg(task->timer)
                           .arg(task->status));
    }

    db.close();
}

void DataBase::saveGroups(QVector<Group*> *groups){
    db.open();

    QSqlQuery query;
    query.exec("DELETE FROM groupName;");
    query.exec("VACUUM;");

    //CREATE TABLE groupName (id INTEGER PRIMARY KEY AUTOINCREMENT, name VARCHAR (50) UNIQUE, color VARCHAR(15))
    for(auto &group : *groups){
        query.exec(QString("INSERT INTO groupName (id,name,color) \
                           VALUES (%1,'%2','%3')")
                           .arg(QString::number(group->id), *group->name, group->color.name())
                           );
    }

    db.close();
}

Group* DataBase::groupById(int id){
    for(auto &group : *groups){
        if(group->id == id)
            return group;
    }
    return new Group;
}