1/* Copyright (c) 2013-2017 Jeffrey Pfau
  2 *
  3 * This Source Code Form is subject to the terms of the Mozilla Public
  4 * License, v. 2.0. If a copy of the MPL was not distributed with this
  5 * file, You can obtain one at http://mozilla.org/MPL/2.0/. */
  6#include "no-intro.h"
  7
  8#include <mgba-util/string.h>
  9#include <mgba-util/vector.h>
 10#include <mgba-util/vfs.h>
 11
 12#include <sqlite3.h>
 13
 14struct NoIntroDB {
 15	sqlite3* db;
 16	sqlite3_stmt* crc32;
 17};
 18
 19struct NoIntroDB* NoIntroDBLoad(const char* path) {
 20	struct NoIntroDB* db = malloc(sizeof(*db));
 21
 22	if (sqlite3_open_v2(path, &db->db, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE | SQLITE_OPEN_FULLMUTEX, NULL)) {
 23		goto error;
 24	}
 25
 26	static const char createTables[] =
 27		"PRAGMA foreign_keys = ON;\n"
 28		"CREATE TABLE IF NOT EXISTS gamedb ("
 29			"dbid INTEGER NOT NULL PRIMARY KEY ASC,"
 30			"name TEXT,"
 31			"version TEXT,"
 32			"CONSTRAINT versioning UNIQUE (name, version)"
 33		");\n"
 34		"CREATE TABLE IF NOT EXISTS games ("
 35			"gid INTEGER NOT NULL PRIMARY KEY ASC,"
 36			"name TEXT,"
 37			"dbid INTEGER NOT NULL REFERENCES gamedb(dbid) ON DELETE CASCADE"
 38		");\n"
 39		"CREATE TABLE IF NOT EXISTS roms ("
 40			"name TEXT,"
 41			"size INTEGER,"
 42			"crc32 INTEGER,"
 43			"md5 BLOB,"
 44			"sha1 BLOB,"
 45			"flags INTEGER DEFAULT 0,"
 46			"gid INTEGER NOT NULL REFERENCES games(gid) ON DELETE CASCADE"
 47		");\n"
 48		"CREATE INDEX IF NOT EXISTS crc32 ON roms (crc32);";
 49	if (sqlite3_exec(db->db, createTables, NULL, NULL, NULL)) {
 50		goto error;
 51	}
 52
 53	static const char selectRom[] = "SELECT * FROM games JOIN roms USING (gid) WHERE roms.crc32 = ?;";
 54	if (sqlite3_prepare_v2(db->db, selectRom, -1, &db->crc32, NULL)) {
 55		goto error;
 56	}
 57
 58	return db;
 59
 60error:
 61	if (db->crc32) {
 62		sqlite3_finalize(db->crc32);
 63	}
 64	NoIntroDBDestroy(db);
 65	return NULL;
 66
 67}
 68
 69bool NoIntroDBLoadClrMamePro(struct NoIntroDB* db, struct VFile* vf) {
 70	struct NoIntroGame buffer = { 0 };
 71
 72	sqlite3_stmt* gamedbTable = NULL;
 73	sqlite3_stmt* gamedbDrop = NULL;
 74	sqlite3_stmt* gameTable = NULL;
 75	sqlite3_stmt* romTable = NULL;
 76	char* fieldName = NULL;
 77	sqlite3_int64 currentGame = -1;
 78	sqlite3_int64 currentDb = -1;
 79	char* dbType = NULL;
 80	char* dbVersion = NULL;
 81	char line[512];
 82
 83	static const char insertGamedb[] = "INSERT INTO gamedb (name, version) VALUES (?, ?);";
 84	if (sqlite3_prepare_v2(db->db, insertGamedb, -1, &gamedbTable, NULL)) {
 85		return false;
 86	}
 87
 88	static const char deleteGamedb[] = "DELETE FROM gamedb WHERE name = ? AND version < ?;";
 89	if (sqlite3_prepare_v2(db->db, deleteGamedb, -1, &gamedbDrop, NULL)) {
 90		return false;
 91	}
 92
 93	static const char insertGame[] = "INSERT INTO games (dbid, name) VALUES (?, ?);";
 94	if (sqlite3_prepare_v2(db->db, insertGame, -1, &gameTable, NULL)) {
 95		return false;
 96	}
 97
 98	static const char insertRom[] = "INSERT INTO roms (gid, name, size, crc32, md5, sha1, flags) VALUES (:game, :name, :size, :crc32, :md5, :sha1, :flags);";
 99	if (sqlite3_prepare_v2(db->db, insertRom, -1, &romTable, NULL)) {
100		return false;
101	}
102
103	while (true) {
104		ssize_t bytesRead = vf->readline(vf, line, sizeof(line));
105		if (!bytesRead) {
106			break;
107		}
108		ssize_t i;
109		const char* token;
110		for (i = 0; i < bytesRead; ++i) {
111			while (isspace((int) line[i]) && i < bytesRead) {
112				++i;
113			}
114			if (i >= bytesRead) {
115				break;
116			}
117			token = &line[i];
118			while (!isspace((int) line[i]) && i < bytesRead) {
119				++i;
120			}
121			if (i >= bytesRead) {
122				break;
123			}
124			switch (token[0]) {
125			case '(':
126				if (!fieldName) {
127					break;
128				}
129				if (strcmp(fieldName, "clrmamepro") == 0) {
130					free((void*) dbType);
131					free((void*) dbVersion);
132					dbType = NULL;
133					dbVersion = NULL;
134					currentDb = -1;
135					currentGame = -1;
136				} else if (currentDb >= 0 && strcmp(fieldName, "game") == 0) {
137					free((void*) buffer.name);
138					free((void*) buffer.romName);
139					memset(&buffer, 0, sizeof(buffer));
140					currentGame = -1;
141				} else if (currentDb >= 0 && strcmp(fieldName, "rom") == 0) {
142					sqlite3_clear_bindings(gameTable);
143					sqlite3_reset(gameTable);
144					sqlite3_bind_int64(gameTable, 1, currentDb);
145					sqlite3_bind_text(gameTable, 2, buffer.name, -1, SQLITE_TRANSIENT);
146					sqlite3_step(gameTable);
147					currentGame = sqlite3_last_insert_rowid(db->db);
148				}
149				free(fieldName);
150				fieldName = NULL;
151				break;
152			case ')':
153				if (currentDb < 0 && dbType && dbVersion) {
154					sqlite3_clear_bindings(gamedbDrop);
155					sqlite3_reset(gamedbDrop);
156					sqlite3_bind_text(gamedbDrop, 1, dbType, -1, SQLITE_TRANSIENT);
157					sqlite3_bind_text(gamedbDrop, 2, dbVersion, -1, SQLITE_TRANSIENT);
158					sqlite3_step(gamedbDrop);
159
160					sqlite3_clear_bindings(gamedbTable);
161					sqlite3_reset(gamedbTable);
162					sqlite3_bind_text(gamedbTable, 1, dbType, -1, SQLITE_TRANSIENT);
163					sqlite3_bind_text(gamedbTable, 2, dbVersion, -1, SQLITE_TRANSIENT);
164					if (sqlite3_step(gamedbTable) == SQLITE_DONE) {
165						currentDb = sqlite3_last_insert_rowid(db->db);
166					}
167					free((void*) dbType);
168					free((void*) dbVersion);
169					dbType = NULL;
170					dbVersion = NULL;
171				}
172				if (currentGame >= 0 && buffer.romName) {
173					sqlite3_clear_bindings(romTable);
174					sqlite3_reset(romTable);
175					sqlite3_bind_int64(romTable, 1, currentGame);
176					sqlite3_bind_text(romTable, 2, buffer.romName, -1, SQLITE_TRANSIENT);
177					sqlite3_bind_int64(romTable, 3, buffer.size);
178					sqlite3_bind_int(romTable, 4, buffer.crc32);
179					sqlite3_bind_blob(romTable, 5, buffer.md5, sizeof(buffer.md5), NULL);
180					sqlite3_bind_blob(romTable, 6, buffer.sha1, sizeof(buffer.sha1), NULL);
181					sqlite3_bind_int(romTable, 7, buffer.verified);
182					sqlite3_step(romTable);
183					free((void*) buffer.romName);
184					buffer.romName = NULL;
185				}
186				break;
187			case '"':
188				++token;
189				for (; line[i] != '"' && i < bytesRead; ++i);
190				// Fall through
191			default:
192				line[i] = '\0';
193				if (fieldName) {
194					if (currentGame >= 0) {
195						if (strcmp("name", fieldName) == 0) {
196							free((void*) buffer.romName);
197							buffer.romName = strdup(token);
198						} else if (strcmp("size", fieldName) == 0) {
199							char* end;
200							unsigned long value = strtoul(token, &end, 10);
201							if (end) {
202								buffer.size = value;
203							}
204						} else if (strcmp("crc", fieldName) == 0) {
205							char* end;
206							unsigned long value = strtoul(token, &end, 16);
207							if (end) {
208								buffer.crc32 = value;
209							}
210						} else if (strcmp("md5", fieldName) == 0) {
211							size_t b;
212							for (b = 0; b < sizeof(buffer.md5) && token && *token; ++b) {
213								token = hex8(token, &buffer.md5[b]);
214							}
215						} else if (strcmp("sha1", fieldName) == 0) {
216							size_t b;
217							for (b = 0; b < sizeof(buffer.sha1) && token && *token; ++b) {
218								token = hex8(token, &buffer.sha1[b]);
219							}
220						} else if (strcmp("flags", fieldName) == 0) {
221							buffer.verified = strcmp("verified", fieldName) == 0;
222						}
223					} else if (currentDb >= 0) {
224						if (strcmp("name", fieldName) == 0) {
225							free((void*) buffer.name);
226							buffer.name = strdup(token);
227						}
228					} else {
229						if (strcmp("name", fieldName) == 0) {
230							free((void*) dbType);
231							dbType = strdup(token);
232						} else if (strcmp("version", fieldName) == 0) {
233							free((void*) dbVersion);
234							dbVersion = strdup(token);
235						}
236					}
237					free(fieldName);
238					fieldName = NULL;
239				} else {
240					fieldName = strdup(token);
241				}
242				break;
243			}
244		}
245	}
246
247	free((void*) buffer.name);
248	free((void*) buffer.romName);
249	free((void*) dbType);
250	free((void*) dbVersion);
251
252	sqlite3_finalize(gameTable);
253	sqlite3_finalize(romTable);
254
255	sqlite3_exec(db->db, "VACUUM", NULL, NULL, NULL);
256
257	return true;
258}
259
260void NoIntroDBDestroy(struct NoIntroDB* db) {
261	sqlite3_close(db->db);
262	free(db);
263}
264
265bool NoIntroDBLookupGameByCRC(const struct NoIntroDB* db, uint32_t crc32, struct NoIntroGame* game) {
266	if (!db) {
267		return false;
268	}
269	sqlite3_clear_bindings(db->crc32);
270	sqlite3_reset(db->crc32);
271	sqlite3_bind_int(db->crc32, 1, crc32);
272	if (sqlite3_step(db->crc32) != SQLITE_ROW) {
273		return false;
274	}
275	game->name = (const char*) sqlite3_column_text(db->crc32, 1);
276	game->romName = (const char*) sqlite3_column_text(db->crc32, 3);
277	game->size = sqlite3_column_int(db->crc32, 4);
278	game->crc32 = sqlite3_column_int(db->crc32, 5);
279	// TODO: md5/sha1
280	game->verified = sqlite3_column_int(db->crc32, 8);
281	return true;
282}