MythTV: dbcheck.cpp Source File

00001 #include <iostream>
00002 using namespace std;
00003 
00004 #include <QString>
00005 #include <QSqlError>
00006 
00007 #include <mythcontext.h>
00008 #include <mythdb.h>
00009 
00010 #include "dbcheck.h"
00011 #include "gamesettings.h"
00012 
00013 const QString currentDatabaseVersion = "1018";
00014 
00015 static bool UpdateDBVersionNumber(const QString &newnumber)
00016 {
00017 
00018     if (!gCoreContext->SaveSettingOnHost("GameDBSchemaVer",newnumber,NULL))
00019     {
00020         LOG(VB_GENERAL, LOG_ERR,
00021             QString("DB Error (Setting new DB version number): %1\n")
00022                 .arg(newnumber));
00023 
00024         return false;
00025     }
00026 
00027     return true;
00028 }
00029 
00030 static bool performActualUpdate(const QString updates[], QString version,
00031                                 QString &dbver)
00032 {
00033     MSqlQuery query(MSqlQuery::InitCon());
00034 
00035     LOG(VB_GENERAL, LOG_NOTICE,
00036         QString("Upgrading to MythGame schema version ") + version);
00037 
00038     int counter = 0;
00039     QString thequery = updates[counter];
00040 
00041     while (thequery != "")
00042     {
00043         if (!query.exec(thequery))
00044         {
00045             QString msg =
00046                 QString("DB Error (Performing database upgrade): \n"
00047                         "Query was: %1 \nError was: %2 \nnew version: %3")
00048                 .arg(thequery)
00049                 .arg(MythDB::DBErrorMessage(query.lastError()))
00050                 .arg(version);
00051             LOG(VB_GENERAL, LOG_ERR, msg);
00052             return false;
00053         }
00054 
00055         counter++;
00056         thequery = updates[counter];
00057     }
00058 
00059     if (!UpdateDBVersionNumber(version))
00060         return false;
00061 
00062     dbver = version;
00063     return true;
00064 }
00065 
00066 static bool InitializeDatabase(void)
00067 {
00068     LOG(VB_GENERAL, LOG_NOTICE,
00069         "Inserting MythGame initial database information.");
00070 
00071     const QString updates[] = {
00072 "CREATE TABLE gamemetadata ("
00073 "  system varchar(128) NOT NULL default '',"
00074 "  romname varchar(128) NOT NULL default '',"
00075 "  gamename varchar(128) NOT NULL default '',"
00076 "  genre varchar(128) NOT NULL default '',"
00077 "  year varchar(10) NOT NULL default '',"
00078 "  publisher varchar(128) NOT NULL default '',"
00079 "  favorite tinyint(1) default NULL,"
00080 "  rompath varchar(255) NOT NULL default '',"
00081 "  gametype varchar(64) NOT NULL default '',"
00082 "  diskcount tinyint(1) NOT NULL default '1',"
00083 "  country varchar(128) NOT NULL default '',"
00084 "  crc_value varchar(64) NOT NULL default '',"
00085 "  display tinyint(1) NOT NULL default '1',"
00086 "  version varchar(64) NOT NULL default '',"
00087 "  KEY system (system),"
00088 "  KEY year (year),"
00089 "  KEY romname (romname),"
00090 "  KEY gamename (gamename),"
00091 "  KEY genre (genre)"
00092 ");",
00093 "CREATE TABLE gameplayers ("
00094 "  gameplayerid int(10) unsigned NOT NULL auto_increment,"
00095 "  playername varchar(64) NOT NULL default '',"
00096 "  workingpath varchar(255) NOT NULL default '',"
00097 "  rompath varchar(255) NOT NULL default '',"
00098 "  screenshots varchar(255) NOT NULL default '',"
00099 "  commandline text NOT NULL,"
00100 "  gametype varchar(64) NOT NULL default '',"
00101 "  extensions varchar(128) NOT NULL default '',"
00102 "  spandisks tinyint(1) NOT NULL default '0',"
00103 "  PRIMARY KEY  (gameplayerid),"
00104 "  UNIQUE KEY playername (playername)"
00105 ");",
00106 "CREATE TABLE romdb ("
00107 "  crc varchar(64) NOT NULL default '',"
00108 "  name varchar(128) NOT NULL default '',"
00109 "  description varchar(128) NOT NULL default '',"
00110 "  category varchar(128) NOT NULL default '',"
00111 "  year varchar(10) NOT NULL default '',"
00112 "  manufacturer varchar(128) NOT NULL default '',"
00113 "  country varchar(128) NOT NULL default '',"
00114 "  publisher varchar(128) NOT NULL default '',"
00115 "  platform varchar(64) NOT NULL default '',"
00116 "  filesize int(12) default NULL,"
00117 "  flags varchar(64) NOT NULL default '',"
00118 "  version varchar(64) NOT NULL default '',"
00119 "  KEY crc (crc),"
00120 "  KEY year (year),"
00121 "  KEY category (category),"
00122 "  KEY name (name),"
00123 "  KEY description (description),"
00124 "  KEY platform (platform)"
00125 ");",
00126 ""
00127 };
00128     QString dbver = "";
00129     if (!performActualUpdate(updates, "1011", dbver))
00130         return false;
00131 
00132     return true;
00133 }
00134 
00135 bool UpgradeGameDatabaseSchema(void)
00136 {
00137     QString dbver = gCoreContext->GetSetting("GameDBSchemaVer");
00138     MSqlQuery query(MSqlQuery::InitCon());
00139 
00140     if (dbver == currentDatabaseVersion)
00141         return true;
00142 
00143     if (dbver.isEmpty())
00144     {
00145         if (!InitializeDatabase())
00146             return false;
00147         dbver = "1011";
00148     }
00149 
00150     if (dbver == "1000")
00151     {
00152         const QString updates[] = {
00153 "ALTER TABLE gamemetadata ADD COLUMN favorite BOOL NULL;",
00154 ""
00155 };
00156         if (!performActualUpdate(updates, "1001", dbver))
00157             return false;
00158     }
00159 
00160     if ((((dbver == "1004")
00161       || (dbver == "1003"))
00162       || (dbver == "1002"))
00163       || (dbver == "1001"))
00164     {
00165         const QString updates[] = {
00166 
00167 "CREATE TABLE gameplayers ("
00168 "  gameplayerid int(10) unsigned NOT NULL auto_increment,"
00169 "  playername varchar(64) NOT NULL default '',"
00170 "  workingpath varchar(255) NOT NULL default '',"
00171 "  rompath varchar(255) NOT NULL default '',"
00172 "  screenshots varchar(255) NOT NULL default '',"
00173 " commandline varchar(255) NOT NULL default '',"
00174 "  gametype varchar(64) NOT NULL default '',"
00175 "  extensions varchar(128) NOT NULL default '',"
00176 "  PRIMARY KEY (gameplayerid),"
00177 "  UNIQUE KEY playername (playername)"
00178 ");",
00179 "ALTER TABLE gamemetadata ADD COLUMN rompath varchar(255) NOT NULL default ''; ",
00180 "ALTER TABLE gamemetadata ADD COLUMN gametype varchar(64) NOT NULL default ''; ",
00181 ""
00182 };
00183         if (!performActualUpdate(updates, "1005", dbver))
00184             return false;
00185     }
00186 
00187     if (dbver == "1005")
00188     {
00189         const QString updates[] = {
00190 "ALTER TABLE gameplayers ADD COLUMN spandisks tinyint(1) NOT NULL default 0; ",
00191 "ALTER TABLE gamemetadata ADD COLUMN diskcount tinyint(1) NOT NULL default 1; ",
00192 ""
00193 };
00194         if (!performActualUpdate(updates, "1006", dbver))
00195             return false;
00196     }
00197 
00198     if (dbver == "1006")
00199     {
00200 
00201         if (!gCoreContext->GetSetting("GameAllTreeLevels").isEmpty())
00202             if (!query.exec("UPDATE settings SET data = 'system gamename' "
00203                             "WHERE value = 'GameAllTreeLevels'; "))
00204                 MythDB::DBError("update GameAllTreeLevels", query);
00205 
00206         QString updates[] = {
00207 "ALTER TABLE gamemetadata ADD COLUMN country varchar(128) NOT NULL default ''; ",
00208 "ALTER TABLE gamemetadata ADD COLUMN crc_value varchar(64) NOT NULL default ''; ",
00209 "ALTER TABLE gamemetadata ADD COLUMN display tinyint(1) NOT NULL default 1; ",
00210 ""
00211 };
00212 
00213         if (!performActualUpdate(updates, "1007", dbver))
00214             return false;
00215     }
00216 
00217     if (dbver == "1007")
00218     {
00219         const QString updates[] = {
00220 "ALTER TABLE gameplayers MODIFY commandline TEXT NOT NULL default ''; ",
00221 ""
00222 };
00223 
00224         if (!performActualUpdate(updates, "1008", dbver))
00225             return false;
00226     }
00227 
00228     if (dbver == "1008")
00229     {
00230         const QString updates[] = {
00231 "CREATE TABLE romdb ("
00232 "  crc varchar(64) NOT NULL default '',"
00233 "  name varchar(128) NOT NULL default '',"
00234 "  description varchar(128) NOT NULL default '',"
00235 "  category varchar(128) NOT NULL default '',"
00236 "  year varchar(10) NOT NULL default '',"
00237 "  manufacturer varchar(128) NOT NULL default '',"
00238 "  country varchar(128) NOT NULL default '',"
00239 "  publisher varchar(128) NOT NULL default '',"
00240 "  platform varchar(64) NOT NULL default '',"
00241 "  filesize int(12) default NULL,"
00242 "  flags varchar(64) NOT NULL default '',"
00243 "  version varchar(64) NOT NULL default '',"
00244 "  KEY crc (crc),"
00245 "  KEY year (year),"
00246 "  KEY category (category),"
00247 "  KEY name (name),"
00248 "  KEY description (description),"
00249 "  KEY platform (platform)"
00250 ");",
00251 ""
00252 };
00253 
00254         if (!performActualUpdate(updates, "1009", dbver))
00255             return false;
00256     }
00257 
00258     if (dbver == "1009")
00259     {
00260         const QString updates[] = {
00261 "ALTER TABLE gamemetadata MODIFY year varchar(10) not null default '';",
00262 ""
00263 };
00264 
00265         if (!performActualUpdate(updates, "1010", dbver))
00266             return false;
00267     }
00268 
00269     if (dbver == "1010")
00270     {
00271         const QString updates[] = {
00272 
00273 "ALTER TABLE gamemetadata ADD COLUMN version varchar(64) NOT NULL default '';",
00274 "ALTER TABLE gamemetadata ADD COLUMN publisher varchar(128) NOT NULL default '';",
00275 ""
00276 };
00277 
00278         if (!performActualUpdate(updates, "1011", dbver))
00279             return false;
00280     }
00281 
00282 
00283     if (dbver == "1011")
00284     {
00285         const QString updates[] = {
00286 "ALTER TABLE romdb ADD COLUMN binfile varchar(64) NOT NULL default ''; ",
00287 ""
00288 };
00289 
00290         if (!performActualUpdate(updates, "1012", dbver))
00291             return false;
00292     }
00293 
00294 
00295     if (dbver == "1012")
00296     {
00297         const QString updates[] = {
00298 QString("ALTER DATABASE %1 DEFAULT CHARACTER SET latin1;")
00299         .arg(gContext->GetDatabaseParams().dbName),
00300 "ALTER TABLE gamemetadata"
00301 "  MODIFY system varbinary(128) NOT NULL default '',"
00302 "  MODIFY romname varbinary(128) NOT NULL default '',"
00303 "  MODIFY gamename varbinary(128) NOT NULL default '',"
00304 "  MODIFY genre varbinary(128) NOT NULL default '',"
00305 "  MODIFY year varbinary(10) NOT NULL default '',"
00306 "  MODIFY publisher varbinary(128) NOT NULL default '',"
00307 "  MODIFY rompath varbinary(255) NOT NULL default '',"
00308 "  MODIFY gametype varbinary(64) NOT NULL default '',"
00309 "  MODIFY country varbinary(128) NOT NULL default '',"
00310 "  MODIFY crc_value varbinary(64) NOT NULL default '',"
00311 "  MODIFY version varbinary(64) NOT NULL default '';",
00312 "ALTER TABLE gameplayers"
00313 "  MODIFY playername varbinary(64) NOT NULL default '',"
00314 "  MODIFY workingpath varbinary(255) NOT NULL default '',"
00315 "  MODIFY rompath varbinary(255) NOT NULL default '',"
00316 "  MODIFY screenshots varbinary(255) NOT NULL default '',"
00317 "  MODIFY commandline blob NOT NULL,"
00318 "  MODIFY gametype varbinary(64) NOT NULL default '',"
00319 "  MODIFY extensions varbinary(128) NOT NULL default '';",
00320 "ALTER TABLE romdb"
00321 "  MODIFY crc varbinary(64) NOT NULL default '',"
00322 "  MODIFY name varbinary(128) NOT NULL default '',"
00323 "  MODIFY description varbinary(128) NOT NULL default '',"
00324 "  MODIFY category varbinary(128) NOT NULL default '',"
00325 "  MODIFY year varbinary(10) NOT NULL default '',"
00326 "  MODIFY manufacturer varbinary(128) NOT NULL default '',"
00327 "  MODIFY country varbinary(128) NOT NULL default '',"
00328 "  MODIFY publisher varbinary(128) NOT NULL default '',"
00329 "  MODIFY platform varbinary(64) NOT NULL default '',"
00330 "  MODIFY flags varbinary(64) NOT NULL default '',"
00331 "  MODIFY version varbinary(64) NOT NULL default '',"
00332 "  MODIFY binfile varbinary(64) NOT NULL default '';",
00333 ""
00334 };
00335 
00336         if (!performActualUpdate(updates, "1013", dbver))
00337             return false;
00338     }
00339 
00340 
00341     if (dbver == "1013")
00342     {
00343         const QString updates[] = {
00344 QString("ALTER DATABASE %1 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;")
00345         .arg(gContext->GetDatabaseParams().dbName),
00346 "ALTER TABLE gamemetadata"
00347 "  DEFAULT CHARACTER SET default,"
00348 "  MODIFY system varchar(128) CHARACTER SET utf8 NOT NULL default '',"
00349 "  MODIFY romname varchar(128) CHARACTER SET utf8 NOT NULL default '',"
00350 "  MODIFY gamename varchar(128) CHARACTER SET utf8 NOT NULL default '',"
00351 "  MODIFY genre varchar(128) CHARACTER SET utf8 NOT NULL default '',"
00352 "  MODIFY year varchar(10) CHARACTER SET utf8 NOT NULL default '',"
00353 "  MODIFY publisher varchar(128) CHARACTER SET utf8 NOT NULL default '',"
00354 "  MODIFY rompath varchar(255) CHARACTER SET utf8 NOT NULL default '',"
00355 "  MODIFY gametype varchar(64) CHARACTER SET utf8 NOT NULL default '',"
00356 "  MODIFY country varchar(128) CHARACTER SET utf8 NOT NULL default '',"
00357 "  MODIFY crc_value varchar(64) CHARACTER SET utf8 NOT NULL default '',"
00358 "  MODIFY version varchar(64) CHARACTER SET utf8 NOT NULL default '';",
00359 "ALTER TABLE gameplayers"
00360 "  DEFAULT CHARACTER SET default,"
00361 "  MODIFY playername varchar(64) CHARACTER SET utf8 NOT NULL default '',"
00362 "  MODIFY workingpath varchar(255) CHARACTER SET utf8 NOT NULL default '',"
00363 "  MODIFY rompath varchar(255) CHARACTER SET utf8 NOT NULL default '',"
00364 "  MODIFY screenshots varchar(255) CHARACTER SET utf8 NOT NULL default '',"
00365 "  MODIFY commandline text CHARACTER SET utf8 NOT NULL,"
00366 "  MODIFY gametype varchar(64) CHARACTER SET utf8 NOT NULL default '',"
00367 "  MODIFY extensions varchar(128) CHARACTER SET utf8 NOT NULL default '';",
00368 "ALTER TABLE romdb"
00369 "  DEFAULT CHARACTER SET default,"
00370 "  MODIFY crc varchar(64) CHARACTER SET utf8 NOT NULL default '',"
00371 "  MODIFY name varchar(128) CHARACTER SET utf8 NOT NULL default '',"
00372 "  MODIFY description varchar(128) CHARACTER SET utf8 NOT NULL default '',"
00373 "  MODIFY category varchar(128) CHARACTER SET utf8 NOT NULL default '',"
00374 "  MODIFY year varchar(10) CHARACTER SET utf8 NOT NULL default '',"
00375 "  MODIFY manufacturer varchar(128) CHARACTER SET utf8 NOT NULL default '',"
00376 "  MODIFY country varchar(128) CHARACTER SET utf8 NOT NULL default '',"
00377 "  MODIFY publisher varchar(128) CHARACTER SET utf8 NOT NULL default '',"
00378 "  MODIFY platform varchar(64) CHARACTER SET utf8 NOT NULL default '',"
00379 "  MODIFY flags varchar(64) CHARACTER SET utf8 NOT NULL default '',"
00380 "  MODIFY version varchar(64) CHARACTER SET utf8 NOT NULL default '',"
00381 "  MODIFY binfile varchar(64) CHARACTER SET utf8 NOT NULL default '';",
00382 ""
00383 };
00384 
00385         if (!performActualUpdate(updates, "1014", dbver))
00386             return false;
00387     }
00388 
00389     if (dbver == "1014")
00390     {
00391         const QString updates[] = {
00392 
00393 "ALTER TABLE gamemetadata ADD fanart VARCHAR(255) NOT NULL AFTER rompath,"
00394 "ADD boxart VARCHAR( 255 ) NOT NULL AFTER fanart;",
00395 ""
00396 };
00397 
00398         if (!performActualUpdate(updates, "1015", dbver))
00399             return false;
00400     }
00401 
00402     if (dbver == "1015")
00403     {
00404         const QString updates[] = {
00405 
00406 "ALTER TABLE gamemetadata ADD screenshot VARCHAR(255) NOT NULL AFTER rompath,"
00407 "ADD plot TEXT NOT NULL AFTER fanart;",
00408 ""
00409 };
00410 
00411         if (!performActualUpdate(updates, "1016", dbver))
00412             return false;
00413     }
00414 
00415     if (dbver == "1016")
00416     {
00417         const QString updates[] = {
00418 
00419 "ALTER TABLE gamemetadata ADD inetref TEXT AFTER crc_value;",
00420 ""
00421 };
00422 
00423         if (!performActualUpdate(updates, "1017", dbver))
00424             return false;
00425     }
00426 
00427     if (dbver == "1017")
00428     {
00429         const QString updates[] = {
00430 
00431 "ALTER TABLE gamemetadata ADD intid int(11) NOT NULL AUTO_INCREMENT "
00432 "PRIMARY KEY FIRST;",
00433 ""
00434 };
00435 
00436         if (!performActualUpdate(updates, "1018", dbver))
00437             return false;
00438     }
00439 
00440     return true;
00441 }