MythTV  master
mythplugins/mythgame/mythgame/dbcheck.cpp
Go to the documentation of this file.
1 #include <iostream>
2 using namespace std;
3 
4 #include <QString>
5 #include <QSqlError>
6 
7 #include <mythcontext.h>
8 #include <mythdb.h>
9 
10 #include "dbcheck.h"
11 #include "gamesettings.h"
12 
13 const QString currentDatabaseVersion = "1019";
14 
15 static bool UpdateDBVersionNumber(const QString &newnumber)
16 {
17 
18  if (!gCoreContext->SaveSettingOnHost("GameDBSchemaVer",newnumber,nullptr))
19  {
20  LOG(VB_GENERAL, LOG_ERR,
21  QString("DB Error (Setting new DB version number): %1\n")
22  .arg(newnumber));
23 
24  return false;
25  }
26 
27  return true;
28 }
29 
30 static bool performActualUpdate(const QString updates[], const QString& version,
31  QString &dbver)
32 {
34 
35  LOG(VB_GENERAL, LOG_NOTICE,
36  QString("Upgrading to MythGame schema version ") + version);
37 
38  int counter = 0;
39  QString thequery = updates[counter];
40 
41  while (thequery != "")
42  {
43  if (!query.exec(thequery))
44  {
45  QString msg =
46  QString("DB Error (Performing database upgrade): \n"
47  "Query was: %1 \nError was: %2 \nnew version: %3")
48  .arg(thequery)
49  .arg(MythDB::DBErrorMessage(query.lastError()))
50  .arg(version);
51  LOG(VB_GENERAL, LOG_ERR, msg);
52  return false;
53  }
54 
55  counter++;
56  thequery = updates[counter];
57  }
58 
60  return false;
61 
62  dbver = version;
63  return true;
64 }
65 
66 static bool InitializeDatabase(void)
67 {
68  LOG(VB_GENERAL, LOG_NOTICE,
69  "Inserting MythGame initial database information.");
70 
71  const QString updates[] = {
72 "CREATE TABLE gamemetadata ("
73 " system varchar(128) NOT NULL default '',"
74 " romname varchar(128) NOT NULL default '',"
75 " gamename varchar(128) NOT NULL default '',"
76 " genre varchar(128) NOT NULL default '',"
77 " year varchar(10) NOT NULL default '',"
78 " publisher varchar(128) NOT NULL default '',"
79 " favorite tinyint(1) default NULL,"
80 " rompath varchar(255) NOT NULL default '',"
81 " gametype varchar(64) NOT NULL default '',"
82 " diskcount tinyint(1) NOT NULL default '1',"
83 " country varchar(128) NOT NULL default '',"
84 " crc_value varchar(64) NOT NULL default '',"
85 " display tinyint(1) NOT NULL default '1',"
86 " version varchar(64) NOT NULL default '',"
87 " KEY system (system),"
88 " KEY year (year),"
89 " KEY romname (romname),"
90 " KEY gamename (gamename),"
91 " KEY genre (genre)"
92 ");",
93 "CREATE TABLE gameplayers ("
94 " gameplayerid int(10) unsigned NOT NULL auto_increment,"
95 " playername varchar(64) NOT NULL default '',"
96 " workingpath varchar(255) NOT NULL default '',"
97 " rompath varchar(255) NOT NULL default '',"
98 " screenshots varchar(255) NOT NULL default '',"
99 " commandline text NOT NULL,"
100 " gametype varchar(64) NOT NULL default '',"
101 " extensions varchar(128) NOT NULL default '',"
102 " spandisks tinyint(1) NOT NULL default '0',"
103 " PRIMARY KEY (gameplayerid),"
104 " UNIQUE KEY playername (playername)"
105 ");",
106 "CREATE TABLE romdb ("
107 " crc varchar(64) NOT NULL default '',"
108 " name varchar(128) NOT NULL default '',"
109 " description varchar(128) NOT NULL default '',"
110 " category varchar(128) NOT NULL default '',"
111 " year varchar(10) NOT NULL default '',"
112 " manufacturer varchar(128) NOT NULL default '',"
113 " country varchar(128) NOT NULL default '',"
114 " publisher varchar(128) NOT NULL default '',"
115 " platform varchar(64) NOT NULL default '',"
116 " filesize int(12) default NULL,"
117 " flags varchar(64) NOT NULL default '',"
118 " version varchar(64) NOT NULL default '',"
119 " KEY crc (crc),"
120 " KEY year (year),"
121 " KEY category (category),"
122 " KEY name (name),"
123 " KEY description (description),"
124 " KEY platform (platform)"
125 ");",
126 ""
127 };
128  QString dbver = "";
129  return performActualUpdate(updates, "1011", dbver);
130 }
131 
133 {
134  QString dbver = gCoreContext->GetSetting("GameDBSchemaVer");
135  MSqlQuery query(MSqlQuery::InitCon());
136 
137  if (dbver == currentDatabaseVersion)
138  return true;
139 
140  if (dbver.isEmpty())
141  {
142  if (!InitializeDatabase())
143  return false;
144  dbver = "1011";
145  }
146 
147  if (dbver == "1000")
148  {
149  const QString updates[] = {
150 "ALTER TABLE gamemetadata ADD COLUMN favorite BOOL NULL;",
151 ""
152 };
153  if (!performActualUpdate(updates, "1001", dbver))
154  return false;
155  }
156 
157  if ((((dbver == "1004")
158  || (dbver == "1003"))
159  || (dbver == "1002"))
160  || (dbver == "1001"))
161  {
162  const QString updates[] = {
163 
164 "CREATE TABLE gameplayers ("
165 " gameplayerid int(10) unsigned NOT NULL auto_increment,"
166 " playername varchar(64) NOT NULL default '',"
167 " workingpath varchar(255) NOT NULL default '',"
168 " rompath varchar(255) NOT NULL default '',"
169 " screenshots varchar(255) NOT NULL default '',"
170 " commandline varchar(255) NOT NULL default '',"
171 " gametype varchar(64) NOT NULL default '',"
172 " extensions varchar(128) NOT NULL default '',"
173 " PRIMARY KEY (gameplayerid),"
174 " UNIQUE KEY playername (playername)"
175 ");",
176 "ALTER TABLE gamemetadata ADD COLUMN rompath varchar(255) NOT NULL default ''; ",
177 "ALTER TABLE gamemetadata ADD COLUMN gametype varchar(64) NOT NULL default ''; ",
178 ""
179 };
180  if (!performActualUpdate(updates, "1005", dbver))
181  return false;
182  }
183 
184  if (dbver == "1005")
185  {
186  const QString updates[] = {
187 "ALTER TABLE gameplayers ADD COLUMN spandisks tinyint(1) NOT NULL default 0; ",
188 "ALTER TABLE gamemetadata ADD COLUMN diskcount tinyint(1) NOT NULL default 1; ",
189 ""
190 };
191  if (!performActualUpdate(updates, "1006", dbver))
192  return false;
193  }
194 
195  if (dbver == "1006")
196  {
197 
198  if (!gCoreContext->GetSetting("GameAllTreeLevels").isEmpty())
199  if (!query.exec("UPDATE settings SET data = 'system gamename' "
200  "WHERE value = 'GameAllTreeLevels'; "))
201  MythDB::DBError("update GameAllTreeLevels", query);
202 
203  QString updates[] = {
204 "ALTER TABLE gamemetadata ADD COLUMN country varchar(128) NOT NULL default ''; ",
205 "ALTER TABLE gamemetadata ADD COLUMN crc_value varchar(64) NOT NULL default ''; ",
206 "ALTER TABLE gamemetadata ADD COLUMN display tinyint(1) NOT NULL default 1; ",
207 ""
208 };
209 
210  if (!performActualUpdate(updates, "1007", dbver))
211  return false;
212  }
213 
214  if (dbver == "1007")
215  {
216  const QString updates[] = {
217 "ALTER TABLE gameplayers MODIFY commandline TEXT NOT NULL default ''; ",
218 ""
219 };
220 
221  if (!performActualUpdate(updates, "1008", dbver))
222  return false;
223  }
224 
225  if (dbver == "1008")
226  {
227  const QString updates[] = {
228 "CREATE TABLE romdb ("
229 " crc varchar(64) NOT NULL default '',"
230 " name varchar(128) NOT NULL default '',"
231 " description varchar(128) NOT NULL default '',"
232 " category varchar(128) NOT NULL default '',"
233 " year varchar(10) NOT NULL default '',"
234 " manufacturer varchar(128) NOT NULL default '',"
235 " country varchar(128) NOT NULL default '',"
236 " publisher varchar(128) NOT NULL default '',"
237 " platform varchar(64) NOT NULL default '',"
238 " filesize int(12) default NULL,"
239 " flags varchar(64) NOT NULL default '',"
240 " version varchar(64) NOT NULL default '',"
241 " KEY crc (crc),"
242 " KEY year (year),"
243 " KEY category (category),"
244 " KEY name (name),"
245 " KEY description (description),"
246 " KEY platform (platform)"
247 ");",
248 ""
249 };
250 
251  if (!performActualUpdate(updates, "1009", dbver))
252  return false;
253  }
254 
255  if (dbver == "1009")
256  {
257  const QString updates[] = {
258 "ALTER TABLE gamemetadata MODIFY year varchar(10) not null default '';",
259 ""
260 };
261 
262  if (!performActualUpdate(updates, "1010", dbver))
263  return false;
264  }
265 
266  if (dbver == "1010")
267  {
268  const QString updates[] = {
269 
270 "ALTER TABLE gamemetadata ADD COLUMN version varchar(64) NOT NULL default '';",
271 "ALTER TABLE gamemetadata ADD COLUMN publisher varchar(128) NOT NULL default '';",
272 ""
273 };
274 
275  if (!performActualUpdate(updates, "1011", dbver))
276  return false;
277  }
278 
279 
280  if (dbver == "1011")
281  {
282  const QString updates[] = {
283 "ALTER TABLE romdb ADD COLUMN binfile varchar(64) NOT NULL default ''; ",
284 ""
285 };
286 
287  if (!performActualUpdate(updates, "1012", dbver))
288  return false;
289  }
290 
291 
292  if (dbver == "1012")
293  {
294  const QString updates[] = {
295 QString("ALTER DATABASE %1 DEFAULT CHARACTER SET latin1;")
297 "ALTER TABLE gamemetadata"
298 " MODIFY system varbinary(128) NOT NULL default '',"
299 " MODIFY romname varbinary(128) NOT NULL default '',"
300 " MODIFY gamename varbinary(128) NOT NULL default '',"
301 " MODIFY genre varbinary(128) NOT NULL default '',"
302 " MODIFY year varbinary(10) NOT NULL default '',"
303 " MODIFY publisher varbinary(128) NOT NULL default '',"
304 " MODIFY rompath varbinary(255) NOT NULL default '',"
305 " MODIFY gametype varbinary(64) NOT NULL default '',"
306 " MODIFY country varbinary(128) NOT NULL default '',"
307 " MODIFY crc_value varbinary(64) NOT NULL default '',"
308 " MODIFY version varbinary(64) NOT NULL default '';",
309 "ALTER TABLE gameplayers"
310 " MODIFY playername varbinary(64) NOT NULL default '',"
311 " MODIFY workingpath varbinary(255) NOT NULL default '',"
312 " MODIFY rompath varbinary(255) NOT NULL default '',"
313 " MODIFY screenshots varbinary(255) NOT NULL default '',"
314 " MODIFY commandline blob NOT NULL,"
315 " MODIFY gametype varbinary(64) NOT NULL default '',"
316 " MODIFY extensions varbinary(128) NOT NULL default '';",
317 "ALTER TABLE romdb"
318 " MODIFY crc varbinary(64) NOT NULL default '',"
319 " MODIFY name varbinary(128) NOT NULL default '',"
320 " MODIFY description varbinary(128) NOT NULL default '',"
321 " MODIFY category varbinary(128) NOT NULL default '',"
322 " MODIFY year varbinary(10) NOT NULL default '',"
323 " MODIFY manufacturer varbinary(128) NOT NULL default '',"
324 " MODIFY country varbinary(128) NOT NULL default '',"
325 " MODIFY publisher varbinary(128) NOT NULL default '',"
326 " MODIFY platform varbinary(64) NOT NULL default '',"
327 " MODIFY flags varbinary(64) NOT NULL default '',"
328 " MODIFY version varbinary(64) NOT NULL default '',"
329 " MODIFY binfile varbinary(64) NOT NULL default '';",
330 ""
331 };
332 
333  if (!performActualUpdate(updates, "1013", dbver))
334  return false;
335  }
336 
337 
338  if (dbver == "1013")
339  {
340  const QString updates[] = {
341 QString("ALTER DATABASE %1 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;")
343 "ALTER TABLE gamemetadata"
344 " DEFAULT CHARACTER SET default,"
345 " MODIFY system varchar(128) CHARACTER SET utf8 NOT NULL default '',"
346 " MODIFY romname varchar(128) CHARACTER SET utf8 NOT NULL default '',"
347 " MODIFY gamename varchar(128) CHARACTER SET utf8 NOT NULL default '',"
348 " MODIFY genre varchar(128) CHARACTER SET utf8 NOT NULL default '',"
349 " MODIFY year varchar(10) CHARACTER SET utf8 NOT NULL default '',"
350 " MODIFY publisher varchar(128) CHARACTER SET utf8 NOT NULL default '',"
351 " MODIFY rompath varchar(255) CHARACTER SET utf8 NOT NULL default '',"
352 " MODIFY gametype varchar(64) CHARACTER SET utf8 NOT NULL default '',"
353 " MODIFY country varchar(128) CHARACTER SET utf8 NOT NULL default '',"
354 " MODIFY crc_value varchar(64) CHARACTER SET utf8 NOT NULL default '',"
355 " MODIFY version varchar(64) CHARACTER SET utf8 NOT NULL default '';",
356 "ALTER TABLE gameplayers"
357 " DEFAULT CHARACTER SET default,"
358 " MODIFY playername varchar(64) CHARACTER SET utf8 NOT NULL default '',"
359 " MODIFY workingpath varchar(255) CHARACTER SET utf8 NOT NULL default '',"
360 " MODIFY rompath varchar(255) CHARACTER SET utf8 NOT NULL default '',"
361 " MODIFY screenshots varchar(255) CHARACTER SET utf8 NOT NULL default '',"
362 " MODIFY commandline text CHARACTER SET utf8 NOT NULL,"
363 " MODIFY gametype varchar(64) CHARACTER SET utf8 NOT NULL default '',"
364 " MODIFY extensions varchar(128) CHARACTER SET utf8 NOT NULL default '';",
365 "ALTER TABLE romdb"
366 " DEFAULT CHARACTER SET default,"
367 " MODIFY crc varchar(64) CHARACTER SET utf8 NOT NULL default '',"
368 " MODIFY name varchar(128) CHARACTER SET utf8 NOT NULL default '',"
369 " MODIFY description varchar(128) CHARACTER SET utf8 NOT NULL default '',"
370 " MODIFY category varchar(128) CHARACTER SET utf8 NOT NULL default '',"
371 " MODIFY year varchar(10) CHARACTER SET utf8 NOT NULL default '',"
372 " MODIFY manufacturer varchar(128) CHARACTER SET utf8 NOT NULL default '',"
373 " MODIFY country varchar(128) CHARACTER SET utf8 NOT NULL default '',"
374 " MODIFY publisher varchar(128) CHARACTER SET utf8 NOT NULL default '',"
375 " MODIFY platform varchar(64) CHARACTER SET utf8 NOT NULL default '',"
376 " MODIFY flags varchar(64) CHARACTER SET utf8 NOT NULL default '',"
377 " MODIFY version varchar(64) CHARACTER SET utf8 NOT NULL default '',"
378 " MODIFY binfile varchar(64) CHARACTER SET utf8 NOT NULL default '';",
379 ""
380 };
381 
382  if (!performActualUpdate(updates, "1014", dbver))
383  return false;
384  }
385 
386  if (dbver == "1014")
387  {
388  const QString updates[] = {
389 
390 "ALTER TABLE gamemetadata ADD fanart VARCHAR(255) NOT NULL AFTER rompath,"
391 "ADD boxart VARCHAR( 255 ) NOT NULL AFTER fanart;",
392 ""
393 };
394 
395  if (!performActualUpdate(updates, "1015", dbver))
396  return false;
397  }
398 
399  if (dbver == "1015")
400  {
401  const QString updates[] = {
402 
403 "ALTER TABLE gamemetadata ADD screenshot VARCHAR(255) NOT NULL AFTER rompath,"
404 "ADD plot TEXT NOT NULL AFTER fanart;",
405 ""
406 };
407 
408  if (!performActualUpdate(updates, "1016", dbver))
409  return false;
410  }
411 
412  if (dbver == "1016")
413  {
414  const QString updates[] = {
415 
416 "ALTER TABLE gamemetadata ADD inetref TEXT AFTER crc_value;",
417 ""
418 };
419 
420  if (!performActualUpdate(updates, "1017", dbver))
421  return false;
422  }
423 
424  if (dbver == "1017")
425  {
426  const QString updates[] = {
427 
428 "ALTER TABLE gamemetadata ADD intid int(11) NOT NULL AUTO_INCREMENT "
429 "PRIMARY KEY FIRST;",
430 ""
431 };
432 
433  if (!performActualUpdate(updates, "1018", dbver))
434  return false;
435  }
436 
437  if (dbver == "1018")
438  {
439  const QString updates[] = {
440 "ALTER TABLE romdb MODIFY description varchar(192) CHARACTER SET utf8 NOT NULL default '';",
441 "ALTER TABLE romdb MODIFY binfile varchar(128) CHARACTER SET utf8 NOT NULL default '';",
442 "ALTER TABLE romdb MODIFY filesize int(12) unsigned default NULL;",
443 ""
444 };
445 
446  if (!performActualUpdate(updates, "1019", dbver))
447  return false;
448  }
449 
450  return true;
451 }
QString dbName
database name
Definition: mythdbparams.h:26
bool UpgradeGameDatabaseSchema(void)
QSqlQuery wrapper that fetches a DB connection from the connection pool.
Definition: mythdbcon.h:125
static bool performActualUpdate(const QString updates[], const QString &version, QString &dbver)
MythCoreContext * gCoreContext
This global variable contains the MythCoreContext instance for the app.
DatabaseParams GetDatabaseParams(void)
MythContext * gContext
This global variable contains the MythContext instance for the application.
Definition: mythcontext.cpp:63
QSqlError lastError(void) const
Definition: mythdbcon.h:202
QString GetSetting(const QString &key, const QString &defaultval="")
const QString currentDatabaseVersion
static MSqlQueryInfo InitCon(ConnectionReuse=kNormalConnection)
Only use this in combination with MSqlQuery constructor.
Definition: mythdbcon.cpp:535
static bool UpdateDBVersionNumber(const QString &newnumber)
#define LOG(_MASK_, _LEVEL_, _STRING_)
Definition: mythlogging.h:41
static bool InitializeDatabase(void)
static QString DBErrorMessage(const QSqlError &err)
Definition: mythdb.cpp:184
bool exec(void)
Wrap QSqlQuery::exec() so we can display SQL.
Definition: mythdbcon.cpp:603
bool SaveSettingOnHost(const QString &key, const QString &newValue, const QString &host)
static void DBError(const QString &where, const MSqlQuery &query)
Definition: mythdb.cpp:179