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 
14 struct NoIntroDB {
15 	sqlite3* db;
16 	sqlite3_stmt* crc32;
17 };
18 
NoIntroDBLoad(const char * path)19 struct 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 
62 error:
63 	if (db->crc32) {
64 		sqlite3_finalize(db->crc32);
65 	}
66 	NoIntroDBDestroy(db);
67 	return NULL;
68 
69 }
70 
NoIntroDBLoadClrMamePro(struct NoIntroDB * db,struct VFile * vf)71 bool 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 
NoIntroDBDestroy(struct NoIntroDB * db)279 void NoIntroDBDestroy(struct NoIntroDB* db) {
280 	sqlite3_finalize(db->crc32);
281 	sqlite3_close(db->db);
282 	free(db);
283 }
284 
NoIntroDBLookupGameByCRC(const struct NoIntroDB * db,uint32_t crc32,struct NoIntroGame * game)285 bool 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 }
303