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"
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()
50 query.exec("DROP TABLE IF EXISTS mediaimagecontainer_mediaimage");
51 query.exec("DROP TABLE IF EXISTS mediaimagecontainer_filepath");
52 query.exec("DROP TABLE IF EXISTS filepath");
53 query.exec("DROP TABLE IF EXISTS setup");
54 query.exec("DROP TABLE IF EXISTS mediatype");
55 query.exec("DROP TABLE IF EXISTS platform");
56 query.exec("DROP TABLE IF EXISTS file") ;
57 query.exec("DROP TABLE IF EXISTS executable");
58 query.exec("DROP TABLE IF EXISTS config");
60 qDebug() << "Creating TABLE file";
62 ret = query.exec("CREATE TABLE IF NOT EXISTS config"
68 query.prepare("INSERT INTO config "
69 "(tmpdirpath, dbversion) "
70 "VALUES (:tmpdir, :dbversion)");
71 query.bindValue(":tmpdir", QDir::homePath());
72 query.bindValue(":dbversion", DbCreator::DB_VERSION);
76 if (!ret) throw QString("tbl config");
78 ret = query.exec("CREATE TABLE IF NOT EXISTS file"
79 "(id INTEGER PRIMARY KEY, "
84 "updatetime NUMERIC, "
87 if (!ret) throw QString("tbl file");
89 qDebug() << "Creating TABLE platform";
91 ret = query.exec("CREATE TABLE IF NOT EXISTS platform "
92 "(id INTEGER PRIMARY KEY, "
94 "fileid INTEGER REFERENCES file(id))");
96 if (!ret) throw QString("tbl platform");
98 qDebug() << "Creating TABLE mediatype ";
100 ret = query.exec("CREATE TABLE IF NOT EXISTS mediatype "
101 "(id INTEGER PRIMARY KEY, "
103 "fileid INTEGER REFERENCES file(id))");
105 if (!ret) throw QString("tbl mediatype");
107 qDebug() << "Creating TABLE setup";
109 ret = query.exec("CREATE TABLE IF NOT EXISTS setup "
110 "(id INTEGER PRIMARY KEY, "
111 "platformid INTEGER REFERENCES platform(id) ON DELETE CASCADE, "
112 "mediatypeid INTEGER REFERENCES mediatype(id) ON DELETE CASCADE, "
113 "filetypeextensions TEXT)");
115 if (!ret) throw QString("tbl setup");
117 qDebug() << "Creating table executable";
119 ret = query.exec("CREATE TABLE IF NOT EXISTS executable "
120 "(id INTEGER PRIMARY KEY, "
125 "setupid INTEGER REFERENCES setup(id))");
127 if (!ret) throw QString("tbl executable");
129 qDebug() << "Creating TABLE filepath";
131 ret = query.exec("CREATE TABLE IF NOT EXISTS filepath "
132 "(id INTEGER PRIMARY KEY, "
134 "filetypeid INTEGER, "
136 "lastscanned NUMERIC, "
137 "FOREIGN KEY (setupid) REFERENCES setup(id))");
139 if (!ret) throw QString("tbl filepath");
141 qDebug() << "Creating TABLE mediaimagecontainer_filepath";
143 ret = query.exec("CREATE TABLE IF NOT EXISTS mediaimagecontainer_filepath "
144 "(fileid INTEGER REFERENCES file(id), "
145 "filepathid INTEGER REFERENCES filepath(id), "
146 "updatetime NUMERIC)");
148 if (!ret) throw QString("tbl mediaimagecontainer_filepath");
151 qDebug() << "Creating TABLE mediaimagecontainer_mediaimage";
153 ret = query.exec("CREATE TABLE IF NOT EXISTS mediaimagecontainer_mediaimage "
154 "(mediaimagecontainerid INTEGER REFERENCES file(id), "
155 "mediaimageid INTEGER REFERENCES file(id))");
157 if (!ret) throw QString("tbl mediaimagecontainer_mediaimage");
160 "CREATE TRIGGER IF NOT EXISTS trg_onplatformdelete "
161 "AFTER DELETE ON platform "
163 " DELETE FROM setup WHERE setup.platformid = old.id;"
167 if (!ret) throw QString("trg_onplatformdelete");
170 "CREATE TRIGGER IF NOT EXISTS trg_onmediatypedelete "
171 "AFTER DELETE ON mediatype "
173 " DELETE FROM setup WHERE setup.mediatypeid = old.id;"
177 if (!ret) throw QString("trg_onmediatypedelete");
180 "CREATE TRIGGER IF NOT EXISTS trg_onsetupdelete "
181 "AFTER DELETE ON setup "
183 " DELETE FROM filepath WHERE filepath.setupid = old.id; "
184 " DELETE FROM executable WHERE executable.setupid = old.id; "
188 if (!ret) throw QString("trg_onsetupdelete");
191 "CREATE TRIGGER IF NOT EXISTS trg_onfilepathdelete "
192 "AFTER DELETE ON filepath "
194 " DELETE FROM mediaimagecontainer_filepath WHERE mediaimagecontainer_filepath.filepathid=old.id; "
198 if (!ret) throw QString("trg_onfilepathdelete");
201 "CREATE TRIGGER IF NOT EXISTS trg_onmediaimagecontainerdelete "
202 "AFTER DELETE ON mediaimagecontainer_filepath "
204 " DELETE FROM mediaimagecontainer_mediaimage WHERE mediaimagecontainer_mediaimage.mediaimagecontainerid=old.fileid;"
208 if (!ret) throw QString("trg_onmediaimagecontainerdelete");
211 "CREATE TRIGGER IF NOT EXISTS trg_onmediaimagecontainer_mediaimagedelete "
212 "AFTER DELETE ON mediaimagecontainer_mediaimage "
214 " DELETE FROM file WHERE file.id=old.mediaimageid; "
215 " DELETE FROM file WHERE file.id=old.mediaimagecontainerid; "
218 if (!ret) throw QString("trg_onmediaimagecontainer_mediaimagedelete");
223 QString err = query.lastError().text();
224 throw QString("Couldn't CREATE '%1'!").arg(tbl).append(err);
230 * Check if database already exists.
232 * Returns 0 if database doesn't exist
233 * or database version number 1 if database exists
236 int DbCreator::dbExists()
239 QString sql("SELECT dbversion FROM config");
243 ret = q.value(0).toInt();
244 qDebug() << "Database version is " << ret
245 << " the application requires " << DB_VERSION;
248 /*for (int i = 0; i < TABLES_COUNT; ++i)
250 if (!tableExists(TABLES[i]))
252 qDebug() << "Table " << TABLES[i] << " missing.";
255 qDebug() << "Table " << TABLES[i] << " exists.";
260 bool DbCreator::tableExists(QString TABLE)
263 query.exec(QString("SELECT name FROM sqlite_master WHERE name='%1'").arg(TABLE));
267 bool DbCreator::deleteDB()
269 // return QFile::remove(getDbPath());