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");
98 "CREATE TABLE IF NOT EXISTS titlename "
99 "(id INTEGER PRIMARY KEY, "
101 // TODO: more fields here...
104 if (!ret) throw QString("tbl titlename");
106 ret = query.exec("CREATE TABLE IF NOT EXISTS file "
107 "(id INTEGER PRIMARY KEY, "
112 "updatetime NUMERIC, "
115 if (!ret) throw QString("tbl file");
118 "CREATE TABLE IF NOT EXISTS file titlename_file "
119 "(titlenameid INTEGER REFERENCES titlename(id), "
120 "fileid INTEGER REFERENCES file(id))"
123 qDebug() << "Creating TABLE platform";
125 ret = query.exec("CREATE TABLE IF NOT EXISTS platform "
126 "(id INTEGER PRIMARY KEY, "
128 "fileid INTEGER REFERENCES file(id))");
130 if (!ret) throw QString("tbl platform");
132 qDebug() << "Creating TABLE mediatype ";
134 ret = query.exec("CREATE TABLE IF NOT EXISTS mediatype "
135 "(id INTEGER PRIMARY KEY, "
137 "fileid INTEGER REFERENCES file(id))");
139 if (!ret) throw QString("tbl mediatype");
141 qDebug() << "Creating TABLE setup";
143 ret = query.exec("CREATE TABLE IF NOT EXISTS setup "
144 "(id INTEGER PRIMARY KEY, "
145 "platformid INTEGER REFERENCES platform(id) ON DELETE CASCADE, "
146 "mediatypeid INTEGER REFERENCES mediatype(id) ON DELETE CASCADE, "
147 "filetypeextensions TEXT)");
149 if (!ret) throw QString("tbl setup");
151 qDebug() << "Creating table executable";
153 ret = query.exec("CREATE TABLE IF NOT EXISTS executable "
154 "(id INTEGER PRIMARY KEY, "
159 "setupid INTEGER REFERENCES setup(id))");
161 if (!ret) throw QString("tbl executable");
163 qDebug() << "Creating TABLE filepath";
165 ret = query.exec("CREATE TABLE IF NOT EXISTS filepath "
166 "(id INTEGER PRIMARY KEY, "
168 "filetypeid INTEGER, "
170 "lastscanned NUMERIC, "
171 "FOREIGN KEY (setupid) REFERENCES setup(id))");
173 if (!ret) throw QString("tbl filepath");
175 qDebug() << "Creating TABLE mediaimagecontainer_filepath";
177 ret = query.exec("CREATE TABLE IF NOT EXISTS mediaimagecontainer_filepath "
178 "(fileid INTEGER REFERENCES file(id), "
179 "filepathid INTEGER REFERENCES filepath(id), "
180 "updatetime NUMERIC)");
182 if (!ret) throw QString("tbl mediaimagecontainer_filepath");
185 qDebug() << "Creating TABLE mediaimagecontainer_mediaimage";
187 ret = query.exec("CREATE TABLE IF NOT EXISTS mediaimagecontainer_mediaimage "
188 "(mediaimagecontainerid INTEGER REFERENCES file(id), "
189 "mediaimageid INTEGER REFERENCES file(id))");
191 if (!ret) throw QString("tbl mediaimagecontainer_mediaimage");
194 "CREATE TRIGGER IF NOT EXISTS trg_onplatformdelete "
195 "AFTER DELETE ON platform "
197 " DELETE FROM setup WHERE setup.platformid = old.id;"
201 if (!ret) throw QString("trg_onplatformdelete");
204 "CREATE TRIGGER IF NOT EXISTS trg_onmediatypedelete "
205 "AFTER DELETE ON mediatype "
207 " DELETE FROM setup WHERE setup.mediatypeid = old.id;"
211 if (!ret) throw QString("trg_onmediatypedelete");
214 "CREATE TRIGGER IF NOT EXISTS trg_onsetupdelete "
215 "AFTER DELETE ON setup "
217 " DELETE FROM filepath WHERE filepath.setupid = old.id; "
218 " DELETE FROM executable WHERE executable.setupid = old.id; "
222 if (!ret) throw QString("trg_onsetupdelete");
225 "CREATE TRIGGER IF NOT EXISTS trg_onfilepathdelete "
226 "AFTER DELETE ON filepath "
228 " DELETE FROM mediaimagecontainer_filepath WHERE mediaimagecontainer_filepath.filepathid=old.id; "
232 if (!ret) throw QString("trg_onfilepathdelete");
235 "CREATE TRIGGER IF NOT EXISTS trg_onmediaimagecontainerdelete "
236 "AFTER DELETE ON mediaimagecontainer_filepath "
238 " DELETE FROM mediaimagecontainer_mediaimage WHERE mediaimagecontainer_mediaimage.mediaimagecontainerid=old.fileid;"
242 if (!ret) throw QString("trg_onmediaimagecontainerdelete");
245 "CREATE TRIGGER IF NOT EXISTS trg_onmediaimagecontainer_mediaimagedelete "
246 "AFTER DELETE ON mediaimagecontainer_mediaimage "
248 " DELETE FROM file WHERE file.id=old.mediaimageid; "
249 " DELETE FROM file WHERE file.id=old.mediaimagecontainerid; "
252 if (!ret) throw QString("trg_onmediaimagecontainer_mediaimagedelete");
257 QString err = query.lastError().text();
258 throw EmuFrontException(QString("Couldn't CREATE '%1'!").arg(tbl).append(err));
264 * Check if database already exists.
266 * Returns 0 if database doesn't exist
267 * or database version number 1 if database exists
270 int DbCreator::dbExists()
273 QString sql("SELECT dbversion FROM config");
277 ret = q.value(0).toInt();
278 qDebug() << "Database version is " << ret
279 << " the application requires " << DB_VERSION;
282 /*for (int i = 0; i < TABLES_COUNT; ++i)
284 if (!tableExists(TABLES[i]))
286 qDebug() << "Table " << TABLES[i] << " missing.";
289 qDebug() << "Table " << TABLES[i] << " exists.";
294 bool DbCreator::tableExists(QString TABLE)
297 query.exec(QString("SELECT name FROM sqlite_master WHERE name='%1'").arg(TABLE));
301 bool DbCreator::deleteDB()
303 // return QFile::remove(getDbPath());