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/>.
21 #include <QSqlDatabase>
26 #include "dbcreator.h"
30 const int DbCreator::TABLES_COUNT = 3;
31 const QString DbCreator::TABLES[] = {"platform", "mediatype", "filepath", "mediaimagecontainer_filepath", "mediaimage", "mediaimagecontainer_mediaimage"};
33 DbCreator::DbCreator(QObject *parent) : QObject(parent)
38 bool DbCreator::createDB()
45 query.exec("DROP TABLE IF EXISTS mediaimagecontainer_mediaimage");
46 query.exec("DROP TABLE IF EXISTS mediaimagecontainer_filepath");
47 query.exec("DROP TABLE IF EXISTS filepath");
48 query.exec("DROP TABLE IF EXISTS setup");
49 query.exec("DROP TABLE IF EXISTS mediatype");
50 query.exec("DROP TABLE IF EXISTS platform");
51 query.exec("DROP TABLE IF EXISTS file");
52 query.exec("DROP TABLE IF EXISTS executable");
54 qDebug() << "Creating TABLE file";
56 ret = query.exec("CREATE TABLE IF NOT EXISTS file"
57 "(id INTEGER PRIMARY KEY, "
58 "name TEXT, " // TODO: optional here! -> mediaimagecontainer has filepath spesific name and media image has mediaimagecontainer specific name
59 // so no name here for those file types
63 "updatetime NUMERIC)");
65 if (!ret) throw QString("tbl file");
67 qDebug() << "Creating TABLE platform";
69 ret = query.exec("CREATE TABLE IF NOT EXISTS platform "
70 "(id INTEGER PRIMARY KEY, "
72 "fileid INTEGER REFERENCES file(id))");
74 if (!ret) throw QString("tbl platform");
76 qDebug() << "Creating TABLE mediatype ";
78 ret = query.exec("CREATE TABLE IF NOT EXISTS mediatype "
79 "(id INTEGER PRIMARY KEY, "
81 "fileid INTEGER REFERENCES file(id))");
83 if (!ret) throw QString("tbl mediatype");
85 qDebug() << "Creating TABLE setup";
87 ret = query.exec("CREATE TABLE IF NOT EXISTS setup "
88 "(id INTEGER PRIMARY KEY, "
89 "platformid INTEGER REFERENCES platform(id) ON DELETE CASCADE, "
90 "mediatypeid INTEGER REFERENCES mediatype(id) ON DELETE CASCADE, "
91 "filetypeextensions TEXT)");
93 if (!ret) throw QString("tbl setup");
95 qDebug() << "Creating table executable";
97 ret = query.exec("CREATE TABLE IF NOT EXISTS executable "
98 "(id INTEGER PRIMARY KEY, "
103 "setupid INTEGER REFERENCES setup(id))");
105 if (!ret) throw QString("tbl executable");
107 qDebug() << "Creating TABLE filepath";
109 ret = query.exec("CREATE TABLE IF NOT EXISTS filepath "
110 "(id INTEGER PRIMARY KEY, "
112 "filetypeid INTEGER, "
114 "lastscanned NUMERIC, "
115 "FOREIGN KEY (setupid) REFERENCES setup(id))");
117 if (!ret) throw QString("tbl filepath");
119 qDebug() << "Creating TABLE mediaimagecontainer_filepath";
121 ret = query.exec("CREATE TABLE IF NOT EXISTS mediaimagecontainer_filepath "
122 "(fileid INTEGER REFERENCES file(id), "
123 "filepathid INTEGER REFERENCES filepath(id), "
124 "mediaimagecontainername TEXT, " // filepath specific name for media image container
125 "updatetime NUMERIC)");
127 if (!ret) throw QString("tbl mediaimagecontainer_filepath");
130 qDebug() << "Creating TABLE mediaimagecontainer_mediaimage";
132 ret = query.exec("CREATE TABLE IF NOT EXISTS mediaimagecontainer_mediaimage "
133 "(mediaimagecontainerid INTEGER REFERENCES file(id), "
134 "mediaimageid INTEGER REFERENCES file(id), "
135 "mediaimagename TEXT " // mediaimagecontainer specific name for media image
138 if (!ret) throw QString("tbl mediaimagecontainer_mediaimage");
141 "CREATE TRIGGER IF NOT EXISTS trg_onplatformdelete "
142 "AFTER DELETE ON platform "
144 " DELETE FROM setup WHERE setup.platformid = old.id;"
148 if (!ret) throw QString("trg_onplatformdelete");
151 "CREATE TRIGGER IF NOT EXISTS trg_onmediatypedelete "
152 "AFTER DELETE ON mediatype "
154 " DELETE FROM setup WHERE setup.mediatypeid = old.id;"
158 if (!ret) throw QString("trg_onmediatypedelete");
161 "CREATE TRIGGER IF NOT EXISTS trg_onsetupdelete "
162 "AFTER DELETE ON setup "
164 " DELETE FROM filepath WHERE filepath.setupid = old.id; "
165 " DELETE FROM executable WHERE executable.setupid = old.id; "
169 if (!ret) throw QString("trg_onsetupdelete");
172 "CREATE TRIGGER IF NOT EXISTS trg_onfilepathdelete "
173 "AFTER DELETE ON filepath "
175 " DELETE FROM mediaimagecontainer_filepath WHERE mediaimagecontainer_filepath.filepathid=old.id; "
179 if (!ret) throw QString("trg_onfilepathdelete");
181 // The following two triggers were removed after adding support for multiple instances of same media image container
182 // on different file paths or multiple instances of media images inside different media image containers.
183 // The trigger functionality must be done by removing the orphaned media images and media image containers
184 // after removing file paths or media image containers programmatically.
186 /* Cannot trigger this, media image container may reside also on another filepath, cannot remove the references with
189 "CREATE TRIGGER IF NOT EXISTS trg_onmediaimagecontainerdelete "
190 "AFTER DELETE ON mediaimagecontainer_filepath "
193 " DELETE FROM mediaimagecontainer_mediaimage WHERE mediaimagecontainer_mediaimage.mediaimagecontainerid=old.fileid;"
197 if (!ret) throw QString("trg_onmediaimagecontainerdelete");*/
199 /* Cannot trigger this, media image container may reside also on another filepath
200 or media image may be assigned with another media image container
202 "CREATE TRIGGER IF NOT EXISTS trg_onmediaimagecontainer_mediaimagedelete "
203 "AFTER DELETE ON mediaimagecontainer_mediaimage "
205 " DELETE FROM file WHERE file.id=old.mediaimageid; "
206 " DELETE FROM file WHERE file.id=old.mediaimagecontainerid; "
209 if (!ret) throw QString("trg_onmediaimagecontainer_mediaimagedelete");*/
214 QString err = query.lastError().text();
215 throw QString("Couldn't CREATE '%1'!").arg(tbl).append(err);
221 * Check if database already exists.
222 * Returns false if doesn't or we don't have a connection.
224 bool DbCreator::dbExists()
226 for (int i = 0; i < TABLES_COUNT; ++i)
228 if (!tableExists(TABLES[i]))
230 qDebug() << "Table " << TABLES[i] << " missing.";
233 qDebug() << "Table " << TABLES[i] << " exists.";
238 bool DbCreator::tableExists(QString TABLE)
241 query.exec(QString("SELECT name FROM sqlite_master WHERE name='%1'").arg(TABLE));
245 bool DbCreator::deleteDB()
247 // return QFile::remove(getDbPath());