2 // Copyright 2010 Mikko Keinänen
4 // This file is part of EmuFront.
7 // EmuFront is free software: you can redistribute it and/or modify
8 // it under the terms of the GNU General Public License version 2 as published by
9 // the Free Software Foundation and appearing in the file gpl.txt included in the
10 // packaging of this file.
12 // EmuFront is distributed in the hope that it will be useful,
13 // but WITHOUT ANY WARRANTY; without even the implied warranty of
14 // MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
15 // GNU General Public License for more details.
17 // You should have received a copy of the GNU General Public License
18 // along with EmuFront. If not, see <http://www.gnu.org/licenses/>.
22 #include <QSqlDatabase>
25 #include <QSqlTableModel>
29 #include "dbcreator.h"
30 #include "emufrontexception.h"
34 const int DbCreator::DB_VERSION = 1;
35 //const int DbCreator::TABLES_COUNT = 3;
36 //const QString DbCreator::TABLES[] = {"platform", "mediatype", "filepath", "mediaimagecontainer_filepath", "mediaimage", "mediaimagecontainer_mediaimage"};
38 DbCreator::DbCreator(QObject *parent) : QObject(parent)
43 bool DbCreator::createDB()
56 When adding a new table, remember to add a drop table
59 When changing the database structure, increase
60 also the version number and create a sql command
61 for updating from last version to new version.
63 Update those version upgrade "patches" here as a version history:
64 -----------------------------------------------------------------
68 query.exec("DROP TABLE IF EXISTS mediaimagecontainer_mediaimage");
69 query.exec("DROP TABLE IF EXISTS mediaimagecontainer_filepath");
70 query.exec("DROP TABLE IF EXISTS filepath");
71 query.exec("DROP TABLE IF EXISTS setup");
72 query.exec("DROP TABLE IF EXISTS mediatype");
73 query.exec("DROP TABLE IF EXISTS platform");
74 query.exec("DROP TABLE IF EXISTS file") ;
75 query.exec("DROP TABLE IF EXISTS executable");
76 query.exec("DROP TABLE IF EXISTS config");
78 qDebug() << "Creating TABLE file";
80 ret = query.exec("CREATE TABLE IF NOT EXISTS config"
86 query.prepare("INSERT INTO config "
87 "(tmpdirpath, dbversion) "
88 "VALUES (:tmpdir, :dbversion)");
89 query.bindValue(":tmpdir", QDir::homePath());
90 query.bindValue(":dbversion", DbCreator::DB_VERSION);
94 if (!ret) throw QString("tbl config");
96 ret = query.exec("CREATE TABLE IF NOT EXISTS file"
97 "(id INTEGER PRIMARY KEY, "
102 "updatetime NUMERIC, "
105 if (!ret) throw QString("tbl file");
107 qDebug() << "Creating TABLE platform";
109 ret = query.exec("CREATE TABLE IF NOT EXISTS platform "
110 "(id INTEGER PRIMARY KEY, "
112 "fileid INTEGER REFERENCES file(id))");
114 if (!ret) throw QString("tbl platform");
116 qDebug() << "Creating TABLE mediatype ";
118 ret = query.exec("CREATE TABLE IF NOT EXISTS mediatype "
119 "(id INTEGER PRIMARY KEY, "
121 "fileid INTEGER REFERENCES file(id))");
123 if (!ret) throw QString("tbl mediatype");
125 qDebug() << "Creating TABLE setup";
127 ret = query.exec("CREATE TABLE IF NOT EXISTS setup "
128 "(id INTEGER PRIMARY KEY, "
129 "platformid INTEGER REFERENCES platform(id) ON DELETE CASCADE, "
130 "mediatypeid INTEGER REFERENCES mediatype(id) ON DELETE CASCADE, "
131 "filetypeextensions TEXT)");
133 if (!ret) throw QString("tbl setup");
135 qDebug() << "Creating table executable";
137 ret = query.exec("CREATE TABLE IF NOT EXISTS executable "
138 "(id INTEGER PRIMARY KEY, "
143 "setupid INTEGER REFERENCES setup(id))");
145 if (!ret) throw QString("tbl executable");
147 qDebug() << "Creating TABLE filepath";
149 ret = query.exec("CREATE TABLE IF NOT EXISTS filepath "
150 "(id INTEGER PRIMARY KEY, "
152 "filetypeid INTEGER, "
154 "lastscanned NUMERIC, "
155 "FOREIGN KEY (setupid) REFERENCES setup(id))");
157 if (!ret) throw QString("tbl filepath");
159 qDebug() << "Creating TABLE mediaimagecontainer_filepath";
161 ret = query.exec("CREATE TABLE IF NOT EXISTS mediaimagecontainer_filepath "
162 "(fileid INTEGER REFERENCES file(id), "
163 "filepathid INTEGER REFERENCES filepath(id), "
164 "updatetime NUMERIC)");
166 if (!ret) throw QString("tbl mediaimagecontainer_filepath");
169 qDebug() << "Creating TABLE mediaimagecontainer_mediaimage";
171 ret = query.exec("CREATE TABLE IF NOT EXISTS mediaimagecontainer_mediaimage "
172 "(mediaimagecontainerid INTEGER REFERENCES file(id), "
173 "mediaimageid INTEGER REFERENCES file(id))");
175 if (!ret) throw QString("tbl mediaimagecontainer_mediaimage");
178 "CREATE TRIGGER IF NOT EXISTS trg_onplatformdelete "
179 "AFTER DELETE ON platform "
181 " DELETE FROM setup WHERE setup.platformid = old.id;"
185 if (!ret) throw QString("trg_onplatformdelete");
188 "CREATE TRIGGER IF NOT EXISTS trg_onmediatypedelete "
189 "AFTER DELETE ON mediatype "
191 " DELETE FROM setup WHERE setup.mediatypeid = old.id;"
195 if (!ret) throw QString("trg_onmediatypedelete");
198 "CREATE TRIGGER IF NOT EXISTS trg_onsetupdelete "
199 "AFTER DELETE ON setup "
201 " DELETE FROM filepath WHERE filepath.setupid = old.id; "
202 " DELETE FROM executable WHERE executable.setupid = old.id; "
206 if (!ret) throw QString("trg_onsetupdelete");
209 "CREATE TRIGGER IF NOT EXISTS trg_onfilepathdelete "
210 "AFTER DELETE ON filepath "
212 " DELETE FROM mediaimagecontainer_filepath WHERE mediaimagecontainer_filepath.filepathid=old.id; "
216 if (!ret) throw QString("trg_onfilepathdelete");
219 "CREATE TRIGGER IF NOT EXISTS trg_onmediaimagecontainerdelete "
220 "AFTER DELETE ON mediaimagecontainer_filepath "
222 " DELETE FROM mediaimagecontainer_mediaimage WHERE mediaimagecontainer_mediaimage.mediaimagecontainerid=old.fileid;"
226 if (!ret) throw QString("trg_onmediaimagecontainerdelete");
229 "CREATE TRIGGER IF NOT EXISTS trg_onmediaimagecontainer_mediaimagedelete "
230 "AFTER DELETE ON mediaimagecontainer_mediaimage "
232 " DELETE FROM file WHERE file.id=old.mediaimageid; "
233 " DELETE FROM file WHERE file.id=old.mediaimagecontainerid; "
236 if (!ret) throw QString("trg_onmediaimagecontainer_mediaimagedelete");
241 QString err = query.lastError().text();
242 throw EmuFrontException(QString("Couldn't CREATE '%1'!").arg(tbl).append(err));
248 * Check if database already exists.
250 * Returns 0 if database doesn't exist
251 * or database version number 1 if database exists
254 int DbCreator::dbExists()
257 QString sql("SELECT dbversion FROM config");
261 ret = q.value(0).toInt();
262 qDebug() << "Database version is " << ret
263 << " the application requires " << DB_VERSION;
266 /*for (int i = 0; i < TABLES_COUNT; ++i)
268 if (!tableExists(TABLES[i]))
270 qDebug() << "Table " << TABLES[i] << " missing.";
273 qDebug() << "Table " << TABLES[i] << " exists.";
278 bool DbCreator::tableExists(QString TABLE)
281 query.exec(QString("SELECT name FROM sqlite_master WHERE name='%1'").arg(TABLE));
285 bool DbCreator::deleteDB()
287 // return QFile::remove(getDbPath());