1 #include <QApplication>
2 #include <QSqlDriver>
3 #include <QSqlQuery>
4 #include <QSqlError>
5 #include <QTreeWidgetItem>
6 #include <QDir>
7 #include <QUuid>
8 #include <QHash>
9
10 #include "macros.h"
11 #include "qmc2main.h"
12 #include "settings.h"
13 #include "options.h"
14 #include "machinelist.h"
15 #include "machinelistdbmgr.h"
16
17 // external global variables
18 extern MainWindow *qmc2MainWindow;
19 extern Settings *qmc2Config;
20 extern MachineList *qmc2MachineList;
21
MachineListDatabaseManager(QObject * parent)22 MachineListDatabaseManager::MachineListDatabaseManager(QObject *parent) :
23 QObject(parent),
24 m_lastRowId(-1),
25 m_logActive(false),
26 m_resetRowCount(true),
27 m_lastRowCount(-1)
28 {
29 m_connectionName = QString("machine-list-db-connection-%1").arg(QUuid::createUuid().toString());
30 m_db = QSqlDatabase::addDatabase("QSQLITE", m_connectionName);
31 m_db.setDatabaseName(qmc2Config->value(QMC2_EMULATOR_PREFIX + "FilesAndDirectories/MachineListDatabase", QString(Options::configPath() + "/%1-machine-list.db").arg(QMC2_EMU_NAME.toLower())).toString());
32 m_tableBasename = QString("%1_machine_list").arg(QMC2_EMU_NAME.toLower());
33 if ( m_db.open() ) {
34 QStringList tables(m_db.driver()->tables(QSql::Tables));
35 if ( tables.count() < 2 || !tables.contains(m_tableBasename) || !tables.contains(QString("%1_metadata").arg(m_tableBasename)) )
36 recreateDatabase();
37 } else
38 qmc2MainWindow->log(QMC2_LOG_FRONTEND, tr("WARNING: failed to open machine list database '%1': error = '%2'").arg(m_db.databaseName()).arg(m_db.lastError().text()));
39 }
40
~MachineListDatabaseManager()41 MachineListDatabaseManager::~MachineListDatabaseManager()
42 {
43 if ( m_db.isOpen() )
44 m_db.close();
45 }
46
emulatorVersion()47 QString MachineListDatabaseManager::emulatorVersion()
48 {
49 QString emu_version;
50 QSqlQuery query(m_db);
51 query.prepare(QString("SELECT emu_version FROM %1_metadata WHERE row=0").arg(m_tableBasename));
52 if ( query.exec() ) {
53 if ( query.first() )
54 emu_version = query.value(0).toString();
55 query.finish();
56 } else
57 qmc2MainWindow->log(QMC2_LOG_FRONTEND, tr("WARNING: failed to fetch '%1' from machine list database: query = '%2', error = '%3'").arg("emu_version").arg(query.lastQuery()).arg(query.lastError().text()));
58 return emu_version;
59 }
60
setEmulatorVersion(QString emu_version)61 void MachineListDatabaseManager::setEmulatorVersion(QString emu_version)
62 {
63 QSqlQuery query(m_db);
64 query.prepare(QString("SELECT emu_version FROM %1_metadata WHERE row=0").arg(m_tableBasename));
65 if ( query.exec() ) {
66 if ( !query.next() ) {
67 query.finish();
68 query.prepare(QString("INSERT INTO %1_metadata (emu_version, row) VALUES (:emu_version, 0)").arg(m_tableBasename));
69 query.bindValue(":emu_version", emu_version);
70 if ( !query.exec() )
71 qmc2MainWindow->log(QMC2_LOG_FRONTEND, tr("WARNING: failed to add '%1' to machine list database: query = '%2', error = '%3'").arg("emu_version").arg(query.lastQuery()).arg(query.lastError().text()));
72 } else {
73 query.finish();
74 query.prepare(QString("UPDATE %1_metadata SET emu_version=:emu_version WHERE row=0").arg(m_tableBasename));
75 query.bindValue(":emu_version", emu_version);
76 if ( !query.exec() )
77 qmc2MainWindow->log(QMC2_LOG_FRONTEND, tr("WARNING: failed to update '%1' in machine list database: query = '%2', error = '%3'").arg("emu_version").arg(query.lastQuery()).arg(query.lastError().text()));
78 }
79 query.finish();
80 } else
81 qmc2MainWindow->log(QMC2_LOG_FRONTEND, tr("WARNING: failed to fetch '%1' from machine list database: query = '%2', error = '%3'").arg("emu_version").arg(query.lastQuery()).arg(query.lastError().text()));
82 }
83
qmc2Version()84 QString MachineListDatabaseManager::qmc2Version()
85 {
86 QString qmc2_version;
87 QSqlQuery query(m_db);
88 query.prepare(QString("SELECT qmc2_version FROM %1_metadata WHERE row=0").arg(m_tableBasename));
89 if ( query.exec() ) {
90 if ( query.first() )
91 qmc2_version = query.value(0).toString();
92 query.finish();
93 } else
94 qmc2MainWindow->log(QMC2_LOG_FRONTEND, tr("WARNING: failed to fetch '%1' from machine list database: query = '%2', error = '%3'").arg("qmc2_version").arg(query.lastQuery()).arg(query.lastError().text()));
95 return qmc2_version;
96 }
97
setQmc2Version(QString qmc2_version)98 void MachineListDatabaseManager::setQmc2Version(QString qmc2_version)
99 {
100 QSqlQuery query(m_db);
101 query.prepare(QString("SELECT qmc2_version FROM %1_metadata WHERE row=0").arg(m_tableBasename));
102 if ( query.exec() ) {
103 if ( !query.next() ) {
104 query.finish();
105 query.prepare(QString("INSERT INTO %1_metadata (qmc2_version, row) VALUES (:qmc2_version, 0)").arg(m_tableBasename));
106 query.bindValue(":qmc2_version", qmc2_version);
107 if ( !query.exec() )
108 qmc2MainWindow->log(QMC2_LOG_FRONTEND, tr("WARNING: failed to add '%1' to machine list database: query = '%2', error = '%3'").arg("qmc2_version").arg(query.lastQuery()).arg(query.lastError().text()));
109 } else {
110 query.finish();
111 query.prepare(QString("UPDATE %1_metadata SET qmc2_version=:qmc2_version WHERE row=0").arg(m_tableBasename));
112 query.bindValue(":qmc2_version", qmc2_version);
113 if ( !query.exec() )
114 qmc2MainWindow->log(QMC2_LOG_FRONTEND, tr("WARNING: failed to update '%1' in machine list database: query = '%2', error = '%3'").arg("qmc2_version").arg(query.lastQuery()).arg(query.lastError().text()));
115 }
116 query.finish();
117 } else
118 qmc2MainWindow->log(QMC2_LOG_FRONTEND, tr("WARNING: failed to fetch '%1' from machine list database: query = '%2', error = '%3'").arg("qmc2_version").arg(query.lastQuery()).arg(query.lastError().text()));
119 }
120
machineListVersion()121 int MachineListDatabaseManager::machineListVersion()
122 {
123 int machinelist_version = -1;
124 QSqlQuery query(m_db);
125 query.prepare(QString("SELECT machinelist_version FROM %1_metadata WHERE row=0").arg(m_tableBasename));
126 if ( query.exec() ) {
127 if ( query.first() )
128 machinelist_version = query.value(0).toInt();
129 query.finish();
130 } else
131 qmc2MainWindow->log(QMC2_LOG_FRONTEND, tr("WARNING: failed to fetch '%1' from machine list database: query = '%2', error = '%3'").arg("machinelist_version").arg(query.lastQuery()).arg(query.lastError().text()));
132 return machinelist_version;
133 }
134
setMachineListVersion(int machinelist_version)135 void MachineListDatabaseManager::setMachineListVersion(int machinelist_version)
136 {
137 QSqlQuery query(m_db);
138 query.prepare(QString("SELECT machinelist_version FROM %1_metadata WHERE row=0").arg(m_tableBasename));
139 if ( query.exec() ) {
140 if ( !query.next() ) {
141 query.finish();
142 query.prepare(QString("INSERT INTO %1_metadata (machinelist_version, row) VALUES (:machinelist_version, 0)").arg(m_tableBasename));
143 query.bindValue(":machinelist_version", machinelist_version);
144 if ( !query.exec() )
145 qmc2MainWindow->log(QMC2_LOG_FRONTEND, tr("WARNING: failed to add '%1' to machine list database: query = '%2', error = '%3'").arg("machinelist_version").arg(query.lastQuery()).arg(query.lastError().text()));
146 } else {
147 query.finish();
148 query.prepare(QString("UPDATE %1_metadata SET machinelist_version=:machinelist_version WHERE row=0").arg(m_tableBasename));
149 query.bindValue(":machinelist_version", machinelist_version);
150 if ( !query.exec() )
151 qmc2MainWindow->log(QMC2_LOG_FRONTEND, tr("WARNING: failed to update '%1' in machine list database: query = '%2', error = '%3'").arg("machinelist_version").arg(query.lastQuery()).arg(query.lastError().text()));
152 }
153 query.finish();
154 } else
155 qmc2MainWindow->log(QMC2_LOG_FRONTEND, tr("WARNING: failed to fetch '%1' from machine list database: query = '%2', error = '%3'").arg("machinelist_version").arg(query.lastQuery()).arg(query.lastError().text()));
156 }
157
machineListRowCount(bool reset)158 qint64 MachineListDatabaseManager::machineListRowCount(bool reset)
159 {
160 m_resetRowCount |= reset;
161 if ( m_resetRowCount ) {
162 QSqlQuery query(m_db);
163 if ( query.exec(QString("SELECT COUNT(*) FROM %1").arg(m_tableBasename)) ) {
164 if ( query.first() )
165 m_lastRowCount = query.value(0).toLongLong();
166 else
167 m_lastRowCount = -1;
168 } else {
169 qmc2MainWindow->log(QMC2_LOG_FRONTEND, tr("WARNING: failed to fetch row count from machine list database: query = '%1', error = '%2'").arg(query.lastQuery()).arg(query.lastError().text()));
170 m_lastRowCount = -1;
171 }
172 m_resetRowCount = false;
173 }
174 return m_lastRowCount;
175 }
176
isEmpty()177 bool MachineListDatabaseManager::isEmpty()
178 {
179 QSqlQuery query(m_db);
180 if ( query.exec(QString("SELECT * FROM %1 LIMIT 1").arg(m_tableBasename)) )
181 return !query.first();
182 else
183 return true;
184 }
185
nextRowId(bool refreshRowIds)186 qint64 MachineListDatabaseManager::nextRowId(bool refreshRowIds)
187 {
188 if ( refreshRowIds ) {
189 m_rowIdList.clear();
190 m_lastRowId = -1;
191 QSqlQuery query(m_db);
192 if ( query.exec(QString("SELECT rowid FROM %1").arg(m_tableBasename)) ) {
193 if ( query.first() ) {
194 do {
195 m_rowIdList << query.value(0).toLongLong();
196 } while ( query.next() );
197 m_lastRowId = 0;
198 return m_rowIdList.at(0);
199 }
200 } else {
201 qmc2MainWindow->log(QMC2_LOG_FRONTEND, tr("WARNING: failed to fetch row IDs from machine list database: query = '%1', error = '%2'").arg(query.lastQuery()).arg(query.lastError().text()));
202 return -1;
203 }
204 } else if ( m_lastRowId > -1 ) {
205 m_lastRowId++;
206 if ( m_lastRowId < m_rowIdList.count() )
207 return m_rowIdList.at(m_lastRowId);
208 else
209 return -1;
210 }
211 return -1;
212 }
213
id(int rowid)214 QString MachineListDatabaseManager::id(int rowid)
215 {
216 QSqlQuery query(m_db);
217 query.prepare(QString("SELECT id FROM %1 WHERE rowid=:rowid").arg(m_tableBasename));
218 query.bindValue(":rowid", rowid);
219 if ( query.exec() ) {
220 if ( query.first() )
221 return query.value(0).toString();
222 else
223 return QString();
224 } else {
225 qmc2MainWindow->log(QMC2_LOG_FRONTEND, tr("WARNING: failed to fetch '%1' from machine list database: query = '%2', error = '%3'").arg("id").arg(query.lastQuery()).arg(query.lastError().text()));
226 return QString();
227 }
228 }
229
exists(QString id)230 bool MachineListDatabaseManager::exists(QString id)
231 {
232 QSqlQuery query(m_db);
233 query.prepare(QString("SELECT id FROM %1 WHERE id=:id LIMIT 1").arg(m_tableBasename));
234 query.bindValue(":id", id);
235 if ( query.exec() )
236 return query.first();
237 else {
238 qmc2MainWindow->log(QMC2_LOG_FRONTEND, tr("WARNING: failed to fetch '%1' from machine list database: query = '%2', error = '%3'").arg("id").arg(query.lastQuery()).arg(query.lastError().text()));
239 return false;
240 }
241 }
242
setData(const QString & id,const QString & description,const QString & manufacturer,const QString & year,const QString & cloneof,bool is_bios,bool is_device,bool has_roms,bool has_chds,int players,const QString & drvstat,const QString & srcfile)243 void MachineListDatabaseManager::setData(const QString &id, const QString &description, const QString &manufacturer, const QString &year, const QString &cloneof, bool is_bios, bool is_device, bool has_roms, bool has_chds, int players, const QString &drvstat, const QString &srcfile)
244 {
245 QSqlQuery query(m_db);
246 query.prepare(QString("INSERT INTO %1 (id, description, manufacturer, year, cloneof, is_bios, is_device, has_roms, has_chds, players, drvstat, srcfile) VALUES (:id, :description, :manufacturer, :year, :cloneof, :is_bios, :is_device, :has_roms, :has_chds, :players, :drvstat, :srcfile)").arg(m_tableBasename));
247 query.bindValue(":id", id);
248 query.bindValue(":description", description);
249 query.bindValue(":manufacturer", manufacturer);
250 query.bindValue(":year", year);
251 query.bindValue(":cloneof", cloneof);
252 query.bindValue(":is_bios", is_bios);
253 query.bindValue(":is_device", is_device);
254 query.bindValue(":has_roms", has_roms);
255 query.bindValue(":has_chds", has_chds);
256 query.bindValue(":players", players);
257 query.bindValue(":drvstat", drvstat);
258 query.bindValue(":srcfile", srcfile);
259 if ( !query.exec() )
260 qmc2MainWindow->log(QMC2_LOG_FRONTEND, tr("WARNING: failed to add '%1' to machine list database: query = '%2', error = '%3'").arg(id).arg(query.lastQuery()).arg(query.lastError().text()));
261 }
262
queryRecords(QSqlQuery * query)263 void MachineListDatabaseManager::queryRecords(QSqlQuery *query)
264 {
265 m_queryMutex.lock();
266 query->clear();
267 query->prepare(QString("SELECT id, description, manufacturer, year, cloneof, is_bios, is_device, has_roms, has_chds, players, drvstat, srcfile FROM %1").arg(m_tableBasename));
268 query->exec();
269 m_queryMutex.unlock();
270 }
271
nextRecord(QSqlQuery * query,QString * id,QString * description,QString * manufacturer,QString * year,QString * cloneof,bool * is_bios,bool * is_device,bool * has_roms,bool * has_chds,int * players,QString * drvstat,QString * srcfile)272 bool MachineListDatabaseManager::nextRecord(QSqlQuery *query, QString *id, QString *description, QString *manufacturer, QString *year, QString *cloneof, bool *is_bios, bool *is_device, bool *has_roms, bool *has_chds, int *players, QString *drvstat, QString *srcfile)
273 {
274 if ( query->next() ) {
275 *id = query->value(QMC2_MLDB_INDEX_ID).toString();
276 *description = query->value(QMC2_MLDB_INDEX_DESCRIPTION).toString();
277 *manufacturer = query->value(QMC2_MLDB_INDEX_MANUFACTURER).toString();
278 *year = query->value(QMC2_MLDB_INDEX_YEAR).toString();
279 *cloneof = query->value(QMC2_MLDB_INDEX_CLONEOF).toString();
280 *is_bios = query->value(QMC2_MLDB_INDEX_IS_BIOS).toBool();
281 *is_device = query->value(QMC2_MLDB_INDEX_IS_DEVICE).toBool();
282 *has_roms = query->value(QMC2_MLDB_INDEX_HAS_ROMS).toBool();
283 *has_chds = query->value(QMC2_MLDB_INDEX_HAS_CHDS).toBool();
284 *players = query->value(QMC2_MLDB_INDEX_PLAYERS).toInt();
285 *drvstat = query->value(QMC2_MLDB_INDEX_DRVSTAT).toString();
286 *srcfile = query->value(QMC2_MLDB_INDEX_SRCFILE).toString();
287 return true;
288 } else
289 return false;
290 }
291
databaseSize()292 quint64 MachineListDatabaseManager::databaseSize()
293 {
294 QSqlQuery query(m_db);
295 if ( query.exec("PRAGMA page_count") ) {
296 if ( query.first() ) {
297 quint64 page_count = query.value(0).toULongLong();
298 query.finish();
299 if ( query.exec("PRAGMA page_size") ) {
300 if ( query.first() ) {
301 quint64 page_size = query.value(0).toULongLong();
302 return page_count * page_size;
303 } else
304 return 0;
305 } else
306 return 0;
307 } else
308 return 0;
309 } else
310 return 0;
311 }
312
setCacheSize(quint64 kiloBytes)313 void MachineListDatabaseManager::setCacheSize(quint64 kiloBytes)
314 {
315 QSqlQuery query(m_db);
316 if ( !query.exec(QString("PRAGMA cache_size = -%1").arg(kiloBytes)) )
317 qmc2MainWindow->log(QMC2_LOG_FRONTEND, tr("WARNING: failed to change the '%1' setting for the machine list database: query = '%2', error = '%3'").arg("cache_size").arg(query.lastQuery()).arg(query.lastError().text()));
318 }
319
setSyncMode(uint syncMode)320 void MachineListDatabaseManager::setSyncMode(uint syncMode)
321 {
322 static QStringList dbSyncModes = QStringList() << "OFF" << "NORMAL" << "FULL";
323 if ( (int)syncMode > dbSyncModes.count() - 1 )
324 return;
325 QSqlQuery query(m_db);
326 if ( !query.exec(QString("PRAGMA synchronous = %1").arg(dbSyncModes.at(syncMode))) )
327 qmc2MainWindow->log(QMC2_LOG_FRONTEND, tr("WARNING: failed to change the '%1' setting for the machine list database: query = '%2', error = '%3'").arg("synchronous").arg(query.lastQuery()).arg(query.lastError().text()));
328 }
329
setJournalMode(uint journalMode)330 void MachineListDatabaseManager::setJournalMode(uint journalMode)
331 {
332 static QStringList dbJournalModes = QStringList() << "DELETE" << "TRUNCATE" << "PERSIST" << "MEMORY" << "WAL" << "OFF";
333 if ( (int)journalMode > dbJournalModes.count() - 1 )
334 return;
335 QSqlQuery query(m_db);
336 if ( !query.exec(QString("PRAGMA journal_mode = %1").arg(dbJournalModes.at(journalMode))) )
337 qmc2MainWindow->log(QMC2_LOG_FRONTEND, tr("WARNING: failed to change the '%1' setting for the machine list database: query = '%2', error = '%3'").arg("journal_mode").arg(query.lastQuery()).arg(query.lastError().text()));
338 }
339
queryColumnInfo()340 void MachineListDatabaseManager::queryColumnInfo()
341 {
342 QSqlQuery query(m_db);
343 columnInfoHash().clear();
344 if ( query.exec(QString("PRAGMA TABLE_INFO(%1)").arg(m_tableBasename)) ) {
345 while ( query.next() )
346 // cid, name, type, notnull, dflt_value, pk
347 columnInfoHash().insert(query.value(1).toString(), DbColumnInfo(query.value(0).toULongLong(), query.value(1).toString(), query.value(2).toString(), query.value(4).toString(), query.value(3).toBool(), query.value(5).toBool()));
348 }
349 }
350
recreateDatabase()351 void MachineListDatabaseManager::recreateDatabase()
352 {
353 QSqlQuery query(m_db);
354 if ( !query.exec(QString("DROP INDEX IF EXISTS %1_index").arg(m_tableBasename)) ) {
355 qmc2MainWindow->log(QMC2_LOG_FRONTEND, tr("WARNING: failed to remove machine list database: query = '%1', error = '%2'").arg(query.lastQuery()).arg(query.lastError().text()));
356 return;
357 }
358 query.finish();
359 if ( !query.exec(QString("DROP TABLE IF EXISTS %1").arg(m_tableBasename)) ) {
360 qmc2MainWindow->log(QMC2_LOG_FRONTEND, tr("WARNING: failed to remove machine list database: query = '%1', error = '%2'").arg(query.lastQuery()).arg(query.lastError().text()));
361 return;
362 }
363 query.finish();
364 if ( !query.exec(QString("DROP TABLE IF EXISTS %1_metadata").arg(m_tableBasename)) ) {
365 qmc2MainWindow->log(QMC2_LOG_FRONTEND, tr("WARNING: failed to remove machine list database: query = '%1', error = '%2'").arg(query.lastQuery()).arg(query.lastError().text()));
366 return;
367 }
368 query.finish();
369 // vaccum'ing the database frees all disk-space previously used
370 query.exec("VACUUM");
371 query.finish();
372 if ( !query.exec(QString("CREATE TABLE %1 (id TEXT PRIMARY KEY, description TEXT, manufacturer TEXT, year TEXT, cloneof TEXT, is_bios BOOL, is_device BOOL, has_roms BOOL, has_chds BOOL, players INT, drvstat TEXT, srcfile TEXT, CONSTRAINT %1_unique_id UNIQUE (id))").arg(m_tableBasename)) ) {
373 qmc2MainWindow->log(QMC2_LOG_FRONTEND, tr("WARNING: failed to create machine list database: query = '%1', error = '%2'").arg(query.lastQuery()).arg(query.lastError().text()));
374 return;
375 }
376 query.finish();
377 if ( !query.exec(QString("CREATE INDEX %1_index ON %1 (id)").arg(m_tableBasename)) ) {
378 qmc2MainWindow->log(QMC2_LOG_FRONTEND, tr("WARNING: failed to create machine list database: query = '%1', error = '%2'").arg(query.lastQuery()).arg(query.lastError().text()));
379 return;
380 }
381 query.finish();
382 if ( !query.exec(QString("CREATE TABLE %1_metadata (row INTEGER PRIMARY KEY, emu_version TEXT, qmc2_version TEXT, machinelist_version INTEGER)").arg(m_tableBasename)) ) {
383 qmc2MainWindow->log(QMC2_LOG_FRONTEND, tr("WARNING: failed to create machine list database: query = '%1', error = '%2'").arg(query.lastQuery()).arg(query.lastError().text()));
384 return;
385 }
386 if ( logActive() )
387 qmc2MainWindow->log(QMC2_LOG_FRONTEND, tr("machine list database '%1' initialized").arg(m_db.databaseName()));
388 }
389