/
sqlmanager.cpp
140 lines (129 loc) · 8.06 KB
/
sqlmanager.cpp
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
#include "sqlmanager.h"
SqlManager::SqlManager(){
name.start();
insManager = new SettingsManager;
//Constructeur -> On vérifie si tout est OK
this->connect();
if(insManager->getSettings(Type).toString() == "films"){
//Vérification des tables
QString requete = "SELECT * FROM films LIMIT 1";
if(instance.exec(requete).lastError().isValid()){
requete = "CREATE TABLE IF NOT EXISTS `etiquettes` (`id` INTEGER PRIMARY KEY, `nom` VARCHAR(50) NOT NULL)";
instance.exec(requete); qDebug() << instance.lastError();
requete = "CREATE TABLE IF NOT EXISTS `liste_etiquettes` (`id` INTEGER PRIMARY KEY, `id_livre` INTEGER NOT NULL, `id_etiquette` INTEGER NOT NULL)";
instance.exec(requete);
requete = "CREATE TABLE IF NOT EXISTS `films` (`id` INTEGER PRIMARY KEY,`titre` VARCHAR(250) NOT NULL,`titre_original` VARCHAR(250) NOT NULL,`directeur` VARCHAR(100) NOT NULL,`acteurs` VARCHAR(1000) NOT NULL,`synopsis` TEXT NOT NULL,`annee` YEAR(4) NOT NULL,`duree` INTEGER NOT NULL,`genre` VARCHAR(500) NOT NULL,`pays` VARCHAR(150) NOT NULL,`langue` VARCHAR(100) NOT NULL,`classement` VARCHAR(100) NOT NULL,`sous_titres` VARCHAR(200) NOT NULL,`commentaire` TEXT NOT NULL,`note` INTEGER NOT NULL,`jaquette` VARCHAR(150) NOT NULL,`empruntable` TINYINT NOT NULL DEFAULT '1',`prete` TINYINT NOT NULL DEFAULT '0',`vu` TINYINT NOT NULL DEFAULT '0',`fichier` TINYINT NOT NULL DEFAULT '0',`emplacement` VARCHAR(250) NOT NULL,`qualite` VARCHAR(100) NOT NULL)";
instance.exec(requete);
requete = "CREATE TABLE IF NOT EXISTS `prets` (`id` INTEGER PRIMARY KEY,`id_item` INTEGER NOT NULL,`emprunteur` VARCHAR(75) NOT NULL, `email` VARCHAR(150) NOT NULL,`date` DATE NOT NULL,`date_rappel` DATE NOT NULL)";
instance.exec(requete);
instance.exec("ALTER TABLE `etiquettes` CHANGE `id` `id` INT(3) NOT NULL AUTO_INCREMENT;");
instance.exec("ALTER TABLE `liste_etiquettes` CHANGE `id` `id` INT(5) NOT NULL AUTO_INCREMENT;");
instance.exec("ALTER TABLE `films` CHANGE `id` `id` INT(11) NOT NULL AUTO_INCREMENT;");
instance.exec("ALTER TABLE `prets` CHANGE `id` `id` INT(4) NOT NULL AUTO_INCREMENT;");
}
}
else{
//Vérification des tables
QString requete = "SELECT * FROM livres LIMIT 1";
if(instance.exec(requete).lastError().isValid()){
//On crée les tables
requete = "CREATE TABLE IF NOT EXISTS auteurs (id INTEGER PRIMARY KEY, nom VARCHAR(250),naissance YEAR(4) NOT NULL,mort YEAR(4) NOT NULL,`biographie` TEXT NOT NULL,`photo` VARCHAR(75) NOT NULL);";
instance.exec(requete);
requete = "CREATE TABLE IF NOT EXISTS `editeurs` (`id` INTEGER PRIMARY KEY NOT NULL,`nom` VARCHAR(150) NOT NULL,`adresse` VARCHAR(250) NOT NULL,`site` VARCHAR(250) NOT NULL);";
instance.exec(requete);
requete = "CREATE TABLE IF NOT EXISTS `livres` (`id` INTEGER PRIMARY KEY NOT NULL,`titre` VARCHAR(255) NOT NULL,`ISBN` VARCHAR(13) NOT NULL,`auteur` INTEGER NOT NULL,`coauteurs` VARCHAR(50) NOT NULL,`synopsis` TEXT NOT NULL,`couverture` VARCHAR(100) NOT NULL,`editeur` INTEGER NOT NULL,`annee` YEAR(4) NOT NULL,`pages` INTEGER NOT NULL,`edition` INTEGER NOT NULL DEFAULT '1',`langue` VARCHAR(25) NOT NULL,`classement` VARCHAR(255) NOT NULL,`exemplaires` INTEGER NOT NULL DEFAULT '1',`commentaire` TEXT NOT NULL,`lu` TINYINT NOT NULL,`note` INTEGER NOT NULL DEFAULT '1',`empruntable` TINYINT NOT NULL,`prete` TINYINT NOT NULL,`ebook` TINYINT NOT NULL,`emplacement` VARCHAR(255) NOT NULL);";
instance.exec(requete);
requete = "CREATE TABLE IF NOT EXISTS `etiquettes` (`id` INTEGER PRIMARY KEY NOT NULL, `nom` VARCHAR(50) NOT NULL)";
instance.exec(requete);
requete = "CREATE TABLE IF NOT EXISTS `liste_etiquettes` (`id` INTEGER PRIMARY KEY NOT NULL, `id_livre` INTEGER NOT NULL, `id_etiquette` INTEGER NOT NULL)";
instance.exec(requete);
requete = "CREATE TABLE IF NOT EXISTS `prets` (`id` INTEGER PRIMARY KEY,`emprunteur` VARCHAR(75) NOT NULL, `email` VARCHAR(150) NOT NULL,`date` DATE NOT NULL,`date_rappel` DATE NOT NULL)";
instance.exec(requete);
//On ajoute les clés et les auto-increment
instance.exec("ALTER TABLE `auteurs` CHANGE `id` `id` INT(11) NOT NULL AUTO_INCREMENT;");
instance.exec("ALTER TABLE `editeurs` CHANGE `id` `id` INT(11) NOT NULL AUTO_INCREMENT;");
instance.exec("ALTER TABLE `etiquettes` CHANGE `id` `id` INT(3) NOT NULL AUTO_INCREMENT;");
instance.exec("ALTER TABLE `liste_etiquettes` CHANGE `id` `id` INT(5) NOT NULL AUTO_INCREMENT;");
instance.exec("ALTER TABLE `livres` CHANGE `id` `id` INT(11) NOT NULL AUTO_INCREMENT;");
instance.exec("ALTER TABLE `prets` CHANGE `id` `id` INT(4) NOT NULL AUTO_INCREMENT;");
}
}
}
QSqlQuery SqlManager::query(QString req){
if(!instance.isOpen())
this->connect();
QSqlQuery temp = instance.exec(req);
if(!temp.lastError().isValid())
return temp;
else{
qDebug() << req;
QMessageBox::critical(0, "Erreur lors de la requête", "La requête n'a pu être effectuée. Voici le message retourné : \n"+temp.lastError().text());
return temp;
}
}
SqlManager::~SqlManager (){
this->disconnect();
return;
}
void SqlManager::connect(){;
if(insManager->getSettings(Sqlite).toBool()){
instance = QSqlDatabase::addDatabase("QSQLITE", "sqlite_at_"+QString::number(name.nsecsElapsed()));
QString chemin_repertoire = insManager->getSettings(Fichier).toString();
instance.setDatabaseName(chemin_repertoire);
}
else{
instance = QSqlDatabase::addDatabase("QMYSQL", "mysql_at_"+QString::number(name.nsecsElapsed()));
instance.setHostName(insManager->getSettings(DBHost).toString());
instance.setUserName(insManager->getSettings(DBUser).toString());
instance.setPassword(insManager->getSettings(DBPass).toString());
instance.setDatabaseName(insManager->getSettings(DBBase).toString());
}
if(!instance.open()){
QMessageBox::critical(0, "Sql Error", QString("Impossible d'ouvrir la base de données\nL'erreur retournée est la suivante %1").arg(instance.lastError().text()));
exit(EXIT_FAILURE);
}
}
void SqlManager::disconnect(){
if(instance.isOpen())
instance.close();
return;
}
QList<QMultiMap<QString, QString> > SqlManager::convertToXML(QSqlQuery requete){
QStringList champs, booleanFields;
QList<QMultiMap<QString, QString> > data;
QMap<QString, QString> conversion;
conversion.insert("sous_titres", "sousTitres");
conversion.insert("titre_original", "titreOriginal");
conversion.insert("jaquette", "couverture");
conversion.insert("nom_editeur", "editeur");
conversion.insert("nom", "auteur");
booleanFields << "empruntable" << "prete" << "vu" << "lu" << "fichier" << "ebook";
for(int i=0; i<requete.record().count(); ++i){
champs.append(requete.record().fieldName(i));
}
while(requete.next()){ //Parcours des résultats
QMultiMap<QString, QString> item;
for(int i=0; i<champs.count(); ++i){ //Parcours des champs
if(booleanFields.contains(champs.at(i)))
item.insert(champs.at(i), ((requete.record().value(champs.at(i)).toBool()) ? "True" : "False"));
else{
if(conversion.contains(champs.at(i))){
item.insert(conversion.value(champs.at(i)), ToolsManager::stripSlashes(requete.record().value(champs.at(i)).toString()));
}
else{
item.insert(champs.at(i), ToolsManager::stripSlashes(requete.record().value(champs.at(i)).toString()));
}
}
}
data.append(item);
}
return data;
}
QStringList SqlManager::getListEtiquettes(){
QSqlQuery liste = this->query("SELECT nom FROM etiquettes");
QStringList etiquettes;
while(liste.next()){
etiquettes.append(liste.record().value("nom").toString());
}
return etiquettes;
}