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