gestionbdd.cpp

Aller à la documentation de ce fichier.

#include "gestionbdd.h"



GestionBDD::GestionBDD(QObject *parent) :
    QObject(parent)
{
    this->bdd = QSqlDatabase::addDatabase("QSQLITE", NOM_BDD);
    //this->bdd.setDatabaseName(QString(NOM_BDD)+QString(".db"));
    this->bdd.setDatabaseName(":memory:");

    if(!this->bdd.open()){
        qDebug() << tr("Connexion à la base de données impossible: %1").arg(this->bdd.lastError().text()); //addToLog(Error, Database, tr("Connexion à la base de données impossible"));
        return;
    }
    creerBDD();

    qDebug() << tr("Le driver courant (%1) : ").arg(this->bdd.driverName());
    if(this->bdd.driver()->hasFeature(QSqlDriver::Transactions)) qDebug()             << "  " + tr("SUPPORTE les transactions.");
    if(this->bdd.driver()->hasFeature(QSqlDriver::PreparedQueries)) qDebug()          << "  " + tr("SUPPORTE les requêtes préparées.");
    if(this->bdd.driver()->hasFeature(QSqlDriver::NamedPlaceholders)) qDebug()        << "  " + tr("SUPPORTE les marques nommées.");
    if(this->bdd.driver()->hasFeature(QSqlDriver::PositionalPlaceholders)) qDebug()   << "  " + tr("SUPPORTE les marques ordrées.");
    if(this->bdd.driver()->hasFeature(QSqlDriver::QSqlDriver::LastInsertId)) qDebug() << "  " + tr("SUPPORTE le renvoi de l'ID de la dernière ligne insérée.");

    connect(qApp, SIGNAL(aboutToQuit()), this, SLOT(sauvegarderBDD()));

    QTimer::singleShot(3600000, this, SLOT(sauvegarderBDD()));
}



GestionBDD::~GestionBDD(){
    {
        if(this->bdd.isOpen()) this->bdd.close();
    }
    QSqlDatabase::removeDatabase(NOM_BDD);
}



void GestionBDD::ajouterDonnees(QString donnees){
    QJson::Parser parser;
    bool ok;

    QVariantMap donneesP = parser.parse(donnees.toAscii(), &ok).toMap();
    if(!ok){
      qDebug() << tr("Une erreur est survenue dans le parsing de la chaine JSON.");
      return;
    }
    QList<QVariant> donneesVMsP = donneesP.value("vms").toList();

    QStringList listeColonnes;
    QList<QVariantMap> liste;

    if(!this->bdd.isOpen()){
        if(!this->bdd.open()) qDebug() << tr("Connexion à la base de données impossible");
    }
    if(!this->bdd.transaction()) qDebug() << tr("Impossible de démarrer la transaction.");

    liste.append(donneesP);
    listeColonnes << "name" << "time" << "cputemp" << "mbtemp" << "cpu" << "ram" << "disk" << "network";
    QVariant id = executerRequete("INSERT INTO InfosHyperviseurs(%1) VALUES(%2)", listeColonnes, liste);

    listeColonnes.clear();
    liste.clear();

    /*QSqlQuery requete = this->bdd.exec("SELECT last_insert_rowid()");
    requete.next();
    qDebug() << requete.value(0);
    QSqlQuery requete(this->bdd);
    qDebug() << requete.lastInsertId();*/

    QListIterator<QVariant> li(donneesVMsP);
    while(li.hasNext()) liste.append(li.next().toMap());
    listeColonnes << "name" << "uuid" << "state" << "cpu" << "ram" << "maxram" << "network";
    executerRequete("INSERT INTO InfosVMs(idinfos,%1) VALUES("+id.toString()+",%2)", listeColonnes, liste);

    if(!this->bdd.commit()){
        qDebug() << tr("Impossible de valider la transaction: %1").arg(this->bdd.lastError().text());
    }

}


QVariant GestionBDD::executerRequete(QString reqStr, QStringList listeColonnes, QList<QVariantMap> liste){
    if(reqStr.isEmpty() || listeColonnes.isEmpty() || liste.isEmpty()){
        qDebug() << tr("Erreur. Un paramètre ne contient aucunes données.");
        qDebug() << listeColonnes;
        return QVariant(false);
    }

    if(!this->bdd.isOpen()){
        if(!this->bdd.open()) qDebug() << tr("Connexion à la base de données impossible");
    }
    QSqlQuery requete(this->bdd);

    QString colonnes;
    QString placeholders;

    QStringListIterator i(listeColonnes);
    while(i.hasNext()){
        QString item = i.next();
        if(liste.first().contains(item)){
            colonnes += " "+item+",";
            placeholders += " :"+item+",";
        }
    }
    colonnes = colonnes.left(colonnes.size()-1);
    placeholders = placeholders.left(placeholders.size()-1);

    requete.prepare(reqStr.arg(colonnes).arg(placeholders));

    QListIterator<QVariantMap> li(liste);
    while(li.hasNext()){
        QVariantMap VMdata = li.next();
        i.toFront();
        while(i.hasNext()){
            QString item = i.next();
            if(VMdata.contains(item)){
                requete.bindValue(":"+item, VMdata[item]);
            }
        }
        requete.exec();
        verifierErreur(requete);
    }

    return requete.lastInsertId();
}



void GestionBDD::creerBDD(){
    if(!this->bdd.isOpen()){
        if(!this->bdd.open()) qDebug() << tr("Connexion à la base de données impossible");
    }

    QSqlQuery requete(this->bdd);

    requete.exec("PRAGMA foreign_keys = ON;");
    verifierErreur(requete);

    requete.exec(
        "CREATE TABLE IF NOT EXISTS InfosHyperviseurs ("
            "id        INTEGER, "
            "name      VARCHAR(500)  NOT NULL, "
            "time      INTEGER       NOT NULL, "
            "cputemp   FLOAT, "
            "mbtemp    FLOAT, "
            "cpu       FLOAT, "
            "ram       FLOAT, "
            "disk      FLOAT, "
            "network   FLOAT, "
        "PRIMARY KEY(id))"
    );
    verifierErreur(requete);

    requete.exec(
        "CREATE TABLE IF NOT EXISTS InfosVMs ("
            "id       INTEGER, "
            "idinfos  INTEGER        NOT NULL, "
            "name     VARCHAR(500)   NOT NULL, "
            "uuid     VARCHAR(50), "
            "state    SMALLINT, "
            "cpu      FLOAT, "
            "ram      INTEGER, "
            "maxram   INTEGER, "
            "network  FLOAT, "
        "PRIMARY KEY(id), "
        "FOREIGN KEY(idinfos) REFERENCES InfosHyperviseurs(id) ON DELETE CASCADE)"
    );
    verifierErreur(requete);

    requete.exec("CREATE INDEX IF NOT EXISTS InfosName  ON InfosHyperviseurs(name)");
    verifierErreur(requete);
    requete.exec("CREATE INDEX IF NOT EXISTS InfosTime  ON InfosHyperviseurs(time)");
    verifierErreur(requete);
    requete.exec("CREATE INDEX IF NOT EXISTS VMsIdinfos ON InfosVMs(idinfos)");
    verifierErreur(requete);
    requete.exec("CREATE INDEX IF NOT EXISTS VMsName    ON InfosVMs(name)");
    verifierErreur(requete);

}



void GestionBDD::sauvegarderBDD(){
    if(!this->bdd.isOpen()){
        if(!this->bdd.open()) qDebug() << tr("Connexion à la base de données impossible");
    }
    {
        QSqlDatabase sauvegarde = QSqlDatabase::addDatabase("QSQLITE", QString(NOM_BDD)+QString("_sauvegarde"));
        sauvegarde.setDatabaseName(QString(NOM_BDD)+QDateTime::currentDateTime().toString("dd-MM-yyyy_hh-mm-ss")+QString(".db"));
        if(!sauvegarde.isOpen()){
            if(!sauvegarde.open()) qDebug() << tr("Connexion à la base de données impossible");
        }

        // Optimisations
        sauvegarde.exec("PRAGMA foreign_keys = ON;");
        sauvegarde.exec("PRAGMA case_sensitive_like=true;");
        sauvegarde.exec("PRAGMA journal_mode=MEMORY;");
        sauvegarde.exec("PRAGMA temp_store=MEMORY;");
        sauvegarde.exec("PRAGMA locking_mode=EXCLUSIVE;");
        sauvegarde.exec("PRAGMA synchronous = OFF;");

        // Copie des tables
        QStringList listeTables;
        QSqlQuery requete = this->bdd.exec("SELECT sql, tbl_name FROM sqlite_master WHERE type='table' AND sql NOT NULL and name NOT IN ('sqlite_stat1', 'sqlite_sequence')");
        verifierErreur(requete);
        while(requete.next()){
            verifierErreur(sauvegarde.exec(requete.value(0).toString()));
            listeTables << requete.value(1).toString();
        }

        // Copie des indexes
        requete = this->bdd.exec("SELECT sql FROM sqlite_master WHERE type='index' AND sql NOT NULL and name NOT IN ('sqlite_stat1', 'sqlite_sequence')");
        verifierErreur(requete);
        while(requete.next()){
            verifierErreur(sauvegarde.exec(requete.value(0).toString()));
        }

        sauvegarde.close();

        requete = this->bdd.exec("ATTACH DATABASE '" + sauvegarde.databaseName() + "' as sauvegarde");
        verifierErreur(requete);

        if(!this->bdd.transaction()) qDebug() << tr("Impossible de démarrer la transaction.");

        int i;
        for(i=0; i < listeTables.count(); i++){
            verifierErreur(this->bdd.exec("INSERT INTO sauvegarde."+listeTables.at(i)+" SELECT * FROM main."+listeTables.at(i)));
        }
        for(i=0; i < listeTables.count(); i++){
            verifierErreur(this->bdd.exec("DELETE FROM main."+listeTables.at(i)));
        }

        if(!this->bdd.commit()) qDebug() << tr("Impossible de valider la transaction: %1").arg(this->bdd.lastError().text());

        requete = this->bdd.exec("DETACH DATABASE sauvegarde");
        verifierErreur(requete);
    }
    QSqlDatabase::removeDatabase(QString(NOM_BDD)+QString("_sauvegarde"));

    QTimer::singleShot(3600000, this, SLOT(sauvegarderBDD()));
}



bool GestionBDD::verifierErreur(QSqlQuery requete){
    QSqlError erreur      = requete.lastError();
    QString   requetePrec = requete.lastQuery();

    QMapIterator<QString, QVariant> i(requete.boundValues());
    while(i.hasNext()){
        i.next();
        requetePrec.replace(i.key(), i.value().toString());
    }

    if(erreur.type() != QSqlError::NoError){
        qDebug() << tr("%1 : %2").arg(erreur.text()).arg(requetePrec);
        return true;
    }
    return false;
}