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", "mediaimage", "mediaimagecontainer_mediaimage"};
33 DbCreator::DbCreator(QObject *parent) : QObject(parent)
38 bool DbCreator::createDB()
45 query.exec("DROP TABLE IF EXISTS mediaimagecontainer_file");
46 query.exec("DROP TABLE IF EXISTS mediaimagecontainer");
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, "
62 "updatetime NUMERIC)");
64 qDebug() << "Creating TABLE platform";
66 ret = query.exec("CREATE TABLE IF NOT EXISTS platform "
67 "(id INTEGER PRIMARY KEY, "
69 "fileid INTEGER REFERENCES file(id))");
71 if (!ret) throw QString("platform.");
73 qDebug() << "Creating TABLE mediatype ";
75 ret = query.exec("CREATE TABLE IF NOT EXISTS mediatype "
76 "(id INTEGER PRIMARY KEY, "
78 "fileid INTEGER REFERENCES file(id))");
80 if (!ret) throw QString("mediatype.");
82 qDebug() << "Creating TABLE setup";
84 ret = query.exec("CREATE TABLE IF NOT EXISTS setup "
85 "(id INTEGER PRIMARY KEY, "
86 "platformid INTEGER REFERENCES platform(id) ON DELETE CASCADE, "
87 "mediatypeid INTEGER REFERENCES mediatype(id) ON DELETE CASCADE, "
88 "filetypeextensions TEXT)");
90 qDebug() << "Creating table executable";
92 ret = query.exec("CREATE TABLE IF NOT EXISTS executable "
93 "(id INTEGER PRIMARY KEY, "
98 "setupid INTEGER REFERENCES setup(id))");
100 /*qDebug() << "Creating TABLE filetype";
101 ret = query.exec("CREATE TABLE filetype IF NOT EXISTS"
102 "(id INTEGER PRIMARY KEY, "
104 if (!ret) throw QString("filetype.");
105 query.exec("insert into filetype (id, name) values (1, 'media image container')");
106 query.exec("insert into filetype (id, name) values (2, 'screenshot')");
107 query.exec("insert into filetype (id, name) values (3, 'platform icon')");
108 query.exec("insert into filetype (id, name) values (4, 'media type icon')");*/
110 qDebug() << "Creating TABLE filepath";
112 ret = query.exec("CREATE TABLE IF NOT EXISTS filepath "
113 "(id INTEGER PRIMARY KEY, "
115 "filetypeid INTEGER, "
117 "lastscanned NUMERIC, "
118 "FOREIGN KEY (setupid) REFERENCES setup(id))");
120 if (!ret) throw QString("filepath");
122 qDebug() << "Creating TABLE mediaimagecontainer";
124 ret = query.exec("CREATE TABLE IF NOT EXISTS mediaimagecontainer "
125 "(fileid INTEGER REFERENCES file(id), "
126 "filepathid INTEGER REFERENCES filepath(id), "
127 "updatetime NUMERIC)");
129 if (!ret) throw QString("mediaimagecontainer");
132 qDebug() << "Creating TABLE mediaimagecontainer_mediaimage";
134 ret = query.exec("CREATE TABLE IF NOT EXISTS mediaimagecontainer_mediaimage "
135 "(mediaimagecontainerid INTEGER REFERENCES file(id), "
136 "mediaimageid INTEGER REFERENCES file(id))");
138 if (!ret) throw QString("mediaimagecontainer_mediaimage");
141 "CREATE TRIGGER IF NOT EXISTS trg_onplatformdelete "
142 "AFTER DELETE ON platform "
144 " DELETE FROM setup WHERE setup.platformid = old.id;"
149 "CREATE TRIGGER IF NOT EXISTS trg_onmediatypedelete "
150 "AFTER DELETE ON mediatype "
152 " DELETE FROM setup WHERE setup.mediatypeid = old.id;"
157 "CREATE TRIGGER IF NOT EXISTS trg_onsetupdelete "
158 "AFTER DELETE ON setup "
160 " DELETE FROM filepath WHERE filepath.setupid = old.id; "
161 " DELETE FROM executable WHERE executable.setupid = old.id; "
166 "CREATE TRIGGER IF NOT EXISTS trg_onfilepathdelete "
167 "AFTER DELETE ON filepath "
169 " DELETE FROM mediaimagecontainer WHERE filepathid=old.id; "
174 // media image container is not explicitily deleted,
175 // mediaimagecontainer entry should be deleted implicitely with
176 // file tables trigger!
178 "CREATE TRIGGER IF NOT EXISTS trg_onmediaimagecontainerdelete "
179 "AFTER DELETE ON mediaimagecontainer "
181 " DELETE FROM file WHERE id=old.fileid; "
182 " DELETE FROM mediaimagecontainer_mediaimage WHERE mediaimagecontainerid=old.fileid; "
186 /* NOTE: Entries from mediaimagecontainer_mediaimage are not explicitily deleted, they
187 are deleted implicitely using file tables trigger. */
189 "CREATE TRIGGER IF NOT EXISTS trg_onmediaimagecontainer_mediaimagedelete "
190 "AFTER DELETE ON mediaimagecontainer_mediaimage "
192 " DELETE FROM file WHERE id=old.mediaimageid; "
193 " DELETE FROM mediaimagecontainer WHERE fileid=old.mediaimagecontainerid; "
197 "CREATE TRIGGER IF NOT EXISTS trg_onfiledelete "
198 "AFTER DELETE ON file "
200 " UPDATE platform SET platform.fileid=NULL WHERE platform.fileid = old.id;."
201 " UPDATE mediatype SET mediatype.fileid=NULL WHERE mediatype.fileid = old.id;"
202 " DELETE FROM mediaimagecontainer WHERE fileid = old.id;"
203 " DELETE FROM mediaimagecontainer_mediaimage WHERE mediaimagecontainer_mediaimage.mediaimageid = old.id;"
204 " DELETE FROM mediaimagecontainer_mediaimage WHERE mediaimagecontainer_mediaimage.mediaimagecontainerid = old.id;"
210 QString err = query.lastError().text();
211 throw QString("Couldn't CREATE table '%1'!").arg(tbl).append(err);
217 * Check if database already exists.
218 * Returns false if doesn't or we don't have a connection.
220 bool DbCreator::dbExists()
222 for (int i = 0; i < TABLES_COUNT; ++i)
224 if (!tableExists(TABLES[i]))
226 qDebug() << "Table " << TABLES[i] << " missing.";
229 qDebug() << "Table " << TABLES[i] << " exists.";
234 bool DbCreator::tableExists(QString TABLE)
237 query.exec(QString("SELECT name FROM sqlite_master WHERE name='%1'").arg(TABLE));
241 bool DbCreator::deleteDB()
243 // return QFile::remove(getDbPath());