3 ** Copyright 2010 Mikko Keinänen
5 ** This file is part of EmuFront.
8 ** EmuFront is free software: you can redistribute it and/or modify
9 ** it under the terms of the GNU General Public License version 2 as published by
10 ** the Free Software Foundation and appearing in the file gpl.txt included in the
11 ** packaging of this file.
13 ** EmuFront is distributed in the hope that it will be useful,
14 ** but WITHOUT ANY WARRANTY; without even the implied warranty of
15 ** MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
16 ** GNU General Public License for more details.
18 ** You should have received a copy of the GNU General Public License
19 ** along with EmuFront. If not, see <http://www.gnu.org/licenses/>.
23 #include <QSqlDatabase>
26 #include <QSqlTableModel>
30 #include "dbcreator.h"
31 #include "emufrontexception.h"
35 const int DbCreator::DB_VERSION = 1;
36 //const int DbCreator::TABLES_COUNT = 3;
37 //const QString DbCreator::TABLES[] = {"platform", "mediatype", "filepath", "mediaimagecontainer_filepath", "mediaimage", "mediaimagecontainer_mediaimage"};
39 DbCreator::DbCreator(QObject *parent) : QObject(parent)
44 bool DbCreator::createDB()
57 When adding a new table, remember to add a drop table
60 When changing the database structure, increase
61 also the version number and create a sql command
62 for updating from last version to new version.
64 Update those version upgrade "patches" here as a version history:
65 -----------------------------------------------------------------
69 query.exec("DROP TABLE IF EXISTS mediaimagecontainer_mediaimage");
70 query.exec("DROP TABLE IF EXISTS mediaimagecontainer_filepath");
71 query.exec("DROP TABLE IF EXISTS filepath");
72 query.exec("DROP TABLE IF EXISTS setup");
73 query.exec("DROP TABLE IF EXISTS mediatype");
74 query.exec("DROP TABLE IF EXISTS platform");
75 query.exec("DROP TABLE IF EXISTS file") ;
76 query.exec("DROP TABLE IF EXISTS executable");
77 query.exec("DROP TABLE IF EXISTS config");
79 qDebug() << "Creating TABLE file";
81 ret = query.exec("CREATE TABLE IF NOT EXISTS config"
87 query.prepare("INSERT INTO config "
88 "(tmpdirpath, dbversion) "
89 "VALUES (:tmpdir, :dbversion)");
90 query.bindValue(":tmpdir", QDir::homePath());
91 query.bindValue(":dbversion", DbCreator::DB_VERSION);
95 if (!ret) throw QString("tbl config");
97 ret = query.exec("CREATE TABLE IF NOT EXISTS file"
98 "(id INTEGER PRIMARY KEY, "
103 "updatetime NUMERIC, "
106 if (!ret) throw QString("tbl file");
108 qDebug() << "Creating TABLE platform";
110 ret = query.exec("CREATE TABLE IF NOT EXISTS platform "
111 "(id INTEGER PRIMARY KEY, "
113 "fileid INTEGER REFERENCES file(id))");
115 if (!ret) throw QString("tbl platform");
117 qDebug() << "Creating TABLE mediatype ";
119 ret = query.exec("CREATE TABLE IF NOT EXISTS mediatype "
120 "(id INTEGER PRIMARY KEY, "
122 "fileid INTEGER REFERENCES file(id))");
124 if (!ret) throw QString("tbl mediatype");
126 qDebug() << "Creating TABLE setup";
128 ret = query.exec("CREATE TABLE IF NOT EXISTS setup "
129 "(id INTEGER PRIMARY KEY, "
130 "platformid INTEGER REFERENCES platform(id) ON DELETE CASCADE, "
131 "mediatypeid INTEGER REFERENCES mediatype(id) ON DELETE CASCADE, "
132 "filetypeextensions TEXT)");
134 if (!ret) throw QString("tbl setup");
136 qDebug() << "Creating table executable";
138 ret = query.exec("CREATE TABLE IF NOT EXISTS executable "
139 "(id INTEGER PRIMARY KEY, "
144 "setupid INTEGER REFERENCES setup(id))");
146 if (!ret) throw QString("tbl executable");
148 qDebug() << "Creating TABLE filepath";
150 ret = query.exec("CREATE TABLE IF NOT EXISTS filepath "
151 "(id INTEGER PRIMARY KEY, "
153 "filetypeid INTEGER, "
155 "lastscanned NUMERIC, "
156 "FOREIGN KEY (setupid) REFERENCES setup(id))");
158 if (!ret) throw QString("tbl filepath");
160 qDebug() << "Creating TABLE mediaimagecontainer_filepath";
162 ret = query.exec("CREATE TABLE IF NOT EXISTS mediaimagecontainer_filepath "
163 "(fileid INTEGER REFERENCES file(id), "
164 "filepathid INTEGER REFERENCES filepath(id), "
165 "updatetime NUMERIC)");
167 if (!ret) throw QString("tbl mediaimagecontainer_filepath");
170 qDebug() << "Creating TABLE mediaimagecontainer_mediaimage";
172 ret = query.exec("CREATE TABLE IF NOT EXISTS mediaimagecontainer_mediaimage "
173 "(mediaimagecontainerid INTEGER REFERENCES file(id), "
174 "mediaimageid INTEGER REFERENCES file(id))");
176 if (!ret) throw QString("tbl mediaimagecontainer_mediaimage");
179 "CREATE TRIGGER IF NOT EXISTS trg_onplatformdelete "
180 "AFTER DELETE ON platform "
182 " DELETE FROM setup WHERE setup.platformid = old.id;"
186 if (!ret) throw QString("trg_onplatformdelete");
189 "CREATE TRIGGER IF NOT EXISTS trg_onmediatypedelete "
190 "AFTER DELETE ON mediatype "
192 " DELETE FROM setup WHERE setup.mediatypeid = old.id;"
196 if (!ret) throw QString("trg_onmediatypedelete");
199 "CREATE TRIGGER IF NOT EXISTS trg_onsetupdelete "
200 "AFTER DELETE ON setup "
202 " DELETE FROM filepath WHERE filepath.setupid = old.id; "
203 " DELETE FROM executable WHERE executable.setupid = old.id; "
207 if (!ret) throw QString("trg_onsetupdelete");
210 "CREATE TRIGGER IF NOT EXISTS trg_onfilepathdelete "
211 "AFTER DELETE ON filepath "
213 " DELETE FROM mediaimagecontainer_filepath WHERE mediaimagecontainer_filepath.filepathid=old.id; "
217 if (!ret) throw QString("trg_onfilepathdelete");
220 "CREATE TRIGGER IF NOT EXISTS trg_onmediaimagecontainerdelete "
221 "AFTER DELETE ON mediaimagecontainer_filepath "
223 " DELETE FROM mediaimagecontainer_mediaimage WHERE mediaimagecontainer_mediaimage.mediaimagecontainerid=old.fileid;"
227 if (!ret) throw QString("trg_onmediaimagecontainerdelete");
230 "CREATE TRIGGER IF NOT EXISTS trg_onmediaimagecontainer_mediaimagedelete "
231 "AFTER DELETE ON mediaimagecontainer_mediaimage "
233 " DELETE FROM file WHERE file.id=old.mediaimageid; "
234 " DELETE FROM file WHERE file.id=old.mediaimagecontainerid; "
237 if (!ret) throw QString("trg_onmediaimagecontainer_mediaimagedelete");
242 QString err = query.lastError().text();
243 throw EmuFrontException(QString("Couldn't CREATE '%1'!").arg(tbl).append(err));
249 * Check if database already exists.
251 * Returns 0 if database doesn't exist
252 * or database version number 1 if database exists
255 int DbCreator::dbExists()
258 QString sql("SELECT dbversion FROM config");
262 ret = q.value(0).toInt();
263 qDebug() << "Database version is " << ret
264 << " the application requires " << DB_VERSION;
267 /*for (int i = 0; i < TABLES_COUNT; ++i)
269 if (!tableExists(TABLES[i]))
271 qDebug() << "Table " << TABLES[i] << " missing.";
274 qDebug() << "Table " << TABLES[i] << " exists.";
279 bool DbCreator::tableExists(QString TABLE)
282 query.exec(QString("SELECT name FROM sqlite_master WHERE name='%1'").arg(TABLE));
286 bool DbCreator::deleteDB()
288 // return QFile::remove(getDbPath());