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  {
200  if (!query.exec("UPDATE settings SET data = 'system gamename' "
201  "WHERE value = 'GameAllTreeLevels'; "))
202  MythDB::DBError("update GameAllTreeLevels", query);
203  }
204 
205  QString updates[] = {
206 "ALTER TABLE gamemetadata ADD COLUMN country varchar(128) NOT NULL default ''; ",
207 "ALTER TABLE gamemetadata ADD COLUMN crc_value varchar(64) NOT NULL default ''; ",
208 "ALTER TABLE gamemetadata ADD COLUMN display tinyint(1) NOT NULL default 1; ",
209 ""
210 };
211 
212  if (!performActualUpdate(updates, "1007", dbver))
213  return false;
214  }
215 
216  if (dbver == "1007")
217  {
218  const QString updates[] = {
219 "ALTER TABLE gameplayers MODIFY commandline TEXT NOT NULL default ''; ",
220 ""
221 };
222 
223  if (!performActualUpdate(updates, "1008", dbver))
224  return false;
225  }
226 
227  if (dbver == "1008")
228  {
229  const QString updates[] = {
230 "CREATE TABLE romdb ("
231 " crc varchar(64) NOT NULL default '',"
232 " name varchar(128) NOT NULL default '',"
233 " description varchar(128) NOT NULL default '',"
234 " category varchar(128) NOT NULL default '',"
235 " year varchar(10) NOT NULL default '',"
236 " manufacturer varchar(128) NOT NULL default '',"
237 " country varchar(128) NOT NULL default '',"
238 " publisher varchar(128) NOT NULL default '',"
239 " platform varchar(64) NOT NULL default '',"
240 " filesize int(12) default NULL,"
241 " flags varchar(64) NOT NULL default '',"
242 " version varchar(64) NOT NULL default '',"
243 " KEY crc (crc),"
244 " KEY year (year),"
245 " KEY category (category),"
246 " KEY name (name),"
247 " KEY description (description),"
248 " KEY platform (platform)"
249 ");",
250 ""
251 };
252 
253  if (!performActualUpdate(updates, "1009", dbver))
254  return false;
255  }
256 
257  if (dbver == "1009")
258  {
259  const QString updates[] = {
260 "ALTER TABLE gamemetadata MODIFY year varchar(10) not null default '';",
261 ""
262 };
263 
264  if (!performActualUpdate(updates, "1010", dbver))
265  return false;
266  }
267 
268  if (dbver == "1010")
269  {
270  const QString updates[] = {
271 
272 "ALTER TABLE gamemetadata ADD COLUMN version varchar(64) NOT NULL default '';",
273 "ALTER TABLE gamemetadata ADD COLUMN publisher varchar(128) NOT NULL default '';",
274 ""
275 };
276 
277  if (!performActualUpdate(updates, "1011", dbver))
278  return false;
279  }
280 
281 
282  if (dbver == "1011")
283  {
284  const QString updates[] = {
285 "ALTER TABLE romdb ADD COLUMN binfile varchar(64) NOT NULL default ''; ",
286 ""
287 };
288 
289  if (!performActualUpdate(updates, "1012", dbver))
290  return false;
291  }
292 
293 
294  if (dbver == "1012")
295  {
296  const QString updates[] = {
297 QString("ALTER DATABASE %1 DEFAULT CHARACTER SET latin1;")
299 "ALTER TABLE gamemetadata"
300 " MODIFY `system` varbinary(128) NOT NULL default '',"
301 " MODIFY romname varbinary(128) NOT NULL default '',"
302 " MODIFY gamename varbinary(128) NOT NULL default '',"
303 " MODIFY genre varbinary(128) NOT NULL default '',"
304 " MODIFY year varbinary(10) NOT NULL default '',"
305 " MODIFY publisher varbinary(128) NOT NULL default '',"
306 " MODIFY rompath varbinary(255) NOT NULL default '',"
307 " MODIFY gametype varbinary(64) NOT NULL default '',"
308 " MODIFY country varbinary(128) NOT NULL default '',"
309 " MODIFY crc_value varbinary(64) NOT NULL default '',"
310 " MODIFY version varbinary(64) NOT NULL default '';",
311 "ALTER TABLE gameplayers"
312 " MODIFY playername varbinary(64) NOT NULL default '',"
313 " MODIFY workingpath varbinary(255) NOT NULL default '',"
314 " MODIFY rompath varbinary(255) NOT NULL default '',"
315 " MODIFY screenshots varbinary(255) NOT NULL default '',"
316 " MODIFY commandline blob NOT NULL,"
317 " MODIFY gametype varbinary(64) NOT NULL default '',"
318 " MODIFY extensions varbinary(128) NOT NULL default '';",
319 "ALTER TABLE romdb"
320 " MODIFY crc varbinary(64) NOT NULL default '',"
321 " MODIFY name varbinary(128) NOT NULL default '',"
322 " MODIFY description varbinary(128) NOT NULL default '',"
323 " MODIFY category varbinary(128) NOT NULL default '',"
324 " MODIFY year varbinary(10) NOT NULL default '',"
325 " MODIFY manufacturer varbinary(128) NOT NULL default '',"
326 " MODIFY country varbinary(128) NOT NULL default '',"
327 " MODIFY publisher varbinary(128) NOT NULL default '',"
328 " MODIFY platform varbinary(64) NOT NULL default '',"
329 " MODIFY flags varbinary(64) NOT NULL default '',"
330 " MODIFY version varbinary(64) NOT NULL default '',"
331 " MODIFY binfile varbinary(64) NOT NULL default '';",
332 ""
333 };
334 
335  if (!performActualUpdate(updates, "1013", dbver))
336  return false;
337  }
338 
339 
340  if (dbver == "1013")
341  {
342  const QString updates[] = {
343 QString("ALTER DATABASE %1 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;")
345 "ALTER TABLE gamemetadata"
346 " DEFAULT CHARACTER SET default,"
347 " MODIFY `system` varchar(128) CHARACTER SET utf8 NOT NULL default '',"
348 " MODIFY romname varchar(128) CHARACTER SET utf8 NOT NULL default '',"
349 " MODIFY gamename varchar(128) CHARACTER SET utf8 NOT NULL default '',"
350 " MODIFY genre varchar(128) CHARACTER SET utf8 NOT NULL default '',"
351 " MODIFY year varchar(10) CHARACTER SET utf8 NOT NULL default '',"
352 " MODIFY publisher varchar(128) CHARACTER SET utf8 NOT NULL default '',"
353 " MODIFY rompath varchar(255) CHARACTER SET utf8 NOT NULL default '',"
354 " MODIFY gametype varchar(64) CHARACTER SET utf8 NOT NULL default '',"
355 " MODIFY country varchar(128) CHARACTER SET utf8 NOT NULL default '',"
356 " MODIFY crc_value varchar(64) CHARACTER SET utf8 NOT NULL default '',"
357 " MODIFY version varchar(64) CHARACTER SET utf8 NOT NULL default '';",
358 "ALTER TABLE gameplayers"
359 " DEFAULT CHARACTER SET default,"
360 " MODIFY playername varchar(64) CHARACTER SET utf8 NOT NULL default '',"
361 " MODIFY workingpath varchar(255) CHARACTER SET utf8 NOT NULL default '',"
362 " MODIFY rompath varchar(255) CHARACTER SET utf8 NOT NULL default '',"
363 " MODIFY screenshots varchar(255) CHARACTER SET utf8 NOT NULL default '',"
364 " MODIFY commandline text CHARACTER SET utf8 NOT NULL,"
365 " MODIFY gametype varchar(64) CHARACTER SET utf8 NOT NULL default '',"
366 " MODIFY extensions varchar(128) CHARACTER SET utf8 NOT NULL default '';",
367 "ALTER TABLE romdb"
368 " DEFAULT CHARACTER SET default,"
369 " MODIFY crc varchar(64) CHARACTER SET utf8 NOT NULL default '',"
370 " MODIFY name varchar(128) CHARACTER SET utf8 NOT NULL default '',"
371 " MODIFY description varchar(128) CHARACTER SET utf8 NOT NULL default '',"
372 " MODIFY category varchar(128) CHARACTER SET utf8 NOT NULL default '',"
373 " MODIFY year varchar(10) CHARACTER SET utf8 NOT NULL default '',"
374 " MODIFY manufacturer varchar(128) CHARACTER SET utf8 NOT NULL default '',"
375 " MODIFY country varchar(128) CHARACTER SET utf8 NOT NULL default '',"
376 " MODIFY publisher varchar(128) CHARACTER SET utf8 NOT NULL default '',"
377 " MODIFY platform varchar(64) CHARACTER SET utf8 NOT NULL default '',"
378 " MODIFY flags varchar(64) CHARACTER SET utf8 NOT NULL default '',"
379 " MODIFY version varchar(64) CHARACTER SET utf8 NOT NULL default '',"
380 " MODIFY binfile varchar(64) CHARACTER SET utf8 NOT NULL default '';",
381 ""
382 };
383 
384  if (!performActualUpdate(updates, "1014", dbver))
385  return false;
386  }
387 
388  if (dbver == "1014")
389  {
390  const QString updates[] = {
391 
392 "ALTER TABLE gamemetadata ADD fanart VARCHAR(255) NOT NULL AFTER rompath,"
393 "ADD boxart VARCHAR( 255 ) NOT NULL AFTER fanart;",
394 ""
395 };
396 
397  if (!performActualUpdate(updates, "1015", dbver))
398  return false;
399  }
400 
401  if (dbver == "1015")
402  {
403  const QString updates[] = {
404 
405 "ALTER TABLE gamemetadata ADD screenshot VARCHAR(255) NOT NULL AFTER rompath,"
406 "ADD plot TEXT NOT NULL AFTER fanart;",
407 ""
408 };
409 
410  if (!performActualUpdate(updates, "1016", dbver))
411  return false;
412  }
413 
414  if (dbver == "1016")
415  {
416  const QString updates[] = {
417 
418 "ALTER TABLE gamemetadata ADD inetref TEXT AFTER crc_value;",
419 ""
420 };
421 
422  if (!performActualUpdate(updates, "1017", dbver))
423  return false;
424  }
425 
426  if (dbver == "1017")
427  {
428  const QString updates[] = {
429 
430 "ALTER TABLE gamemetadata ADD intid int(11) NOT NULL AUTO_INCREMENT "
431 "PRIMARY KEY FIRST;",
432 ""
433 };
434 
435  if (!performActualUpdate(updates, "1018", dbver))
436  return false;
437  }
438 
439  if (dbver == "1018")
440  {
441  const QString updates[] = {
442 "ALTER TABLE romdb MODIFY description varchar(192) CHARACTER SET utf8 NOT NULL default '';",
443 "ALTER TABLE romdb MODIFY binfile varchar(128) CHARACTER SET utf8 NOT NULL default '';",
444 "ALTER TABLE romdb MODIFY filesize int(12) unsigned default NULL;",
445 ""
446 };
447 
448  if (!performActualUpdate(updates, "1019", dbver))
449  return false;
450  }
451 
452  return true;
453 }
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:62
QSqlError lastError(void) const
Definition: mythdbcon.h:202
QString GetSetting(const QString &key, const QString &defaultval="")
const QString currentDatabaseVersion
QString m_dbName
database name
Definition: mythdbparams.h:26
static MSqlQueryInfo InitCon(ConnectionReuse _reuse=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