00001
00002
00003
00004
00005
00006 #include <QString>
00007 #include <QStringList>
00008
00009 #include "videodbcheck.h"
00010 #include "mythdb.h"
00011 #include "mythcorecontext.h"
00012 #include "mythlogging.h"
00013 #include "remotefile.h"
00014 #include "mythmiscutil.h"
00015 #include "libmythmetadata/videoutils.h"
00016
00017 const QString minimumVideoDatabaseVersion = "1016";
00018 const QString finalVideoDatabaseVersion = "1038";
00019 const QString MythVideoVersionName = "mythvideo.DBSchemaVer";
00020
00021 static bool UpdateDBVersionNumber(const QString &field_name,
00022 const QString &newnumber)
00023 {
00024 MSqlQuery query(MSqlQuery::InitCon());
00025
00026 if (!query.exec(QString("DELETE FROM settings WHERE value='%1';")
00027 .arg(field_name)))
00028 {
00029 MythDB::DBError("UpdateDBVersionNumber - delete", query);
00030 return false;
00031 }
00032
00033 if (!query.exec(QString("INSERT INTO settings (value, data, hostname) "
00034 "VALUES ('%1', %2, NULL);")
00035 .arg(field_name).arg(newnumber)))
00036 {
00037 MythDB::DBError("UpdateDBVersionNumber - insert", query);
00038 return false;
00039 }
00040
00041 LOG(VB_GENERAL, LOG_NOTICE,
00042 QString("Upgraded to MythVideo schema version %1") .arg(newnumber));
00043 return true;
00044 }
00045
00046 static bool performActualUpdate(const QStringList &updates,
00047 const QString &version,
00048 QString &dbver, const QString &field_name)
00049 {
00050 MSqlQuery query(MSqlQuery::InitCon());
00051
00052 LOG(VB_GENERAL, LOG_NOTICE,
00053 QString("Upgrading to MythVideo schema version %1") .arg(version));
00054
00055 for (QStringList::const_iterator p = updates.begin();
00056 p != updates.end(); ++p)
00057 {
00058 if (!query.exec(*p))
00059 {
00060 MythDB::DBError("performActualUpdate", query);
00061 return false;
00062 }
00063 }
00064
00065 if (!UpdateDBVersionNumber(field_name, version))
00066 return false;
00067 dbver = version;
00068 return true;
00069 }
00070
00071 static bool performActualUpdate(const QString updates[], const QString &version,
00072 QString &dbver, const QString &field_name)
00073 {
00074 QStringList upQuery;
00075 for (int i = 0; ; ++i)
00076 {
00077 QString q = updates[i];
00078 if (q == "") break;
00079 upQuery.append(q);
00080 }
00081 return performActualUpdate(upQuery, version, dbver, field_name);
00082 }
00083
00084 static void AddFileType(const QString &extension,
00085 const QString &playCommand = QString("Internal"),
00086 bool ignored = false, bool useDefault = false)
00087 {
00088 MSqlQuery query(MSqlQuery::InitCon());
00089 query.prepare("SELECT * FROM videotypes WHERE "
00090 "LOWER(extension) = LOWER(:EXTENSION) LIMIT 1");
00091 query.bindValue(":EXTENSION", extension);
00092 if (query.exec() && !query.size())
00093 {
00094 query.prepare("INSERT INTO videotypes (extension, playcommand, "
00095 "f_ignore, use_default) VALUES (:EXTENSION, :PLAYCOMMAND, "
00096 ":IGNORE, :USEDEFAULT)");
00097 query.bindValue(":EXTENSION", extension);
00098 query.bindValue(":PLAYCOMMAND", playCommand);
00099 query.bindValue(":IGNORE", ignored);
00100 query.bindValue(":USEDEFAULT", useDefault);
00101 if (!query.exec())
00102 MythDB::DBError(QObject::tr("Error: failed to add new file "
00103 "type '%1'").arg(extension), query);
00104 }
00105 }
00106
00107 static void UpdateHashes(void)
00108 {
00109 MSqlQuery query(MSqlQuery::InitCon());
00110 query.prepare("SELECT `filename`, `host` FROM videometadata WHERE "
00111 "`hash` = \"\"");
00112 if (query.exec() && query.size())
00113 {
00114 while (query.next())
00115 {
00116 QString filename = query.value(0).toString();
00117 QString host = query.value(1).toString();
00118 QString hash;
00119
00120 if (!host.isEmpty())
00121 {
00122 QString url = generate_file_url("Videos", host, filename);
00123 hash = RemoteFile::GetFileHash(url);
00124 }
00125 else
00126 hash = FileHash(filename);
00127
00128 if (hash == "NULL")
00129 hash = QString();
00130
00131 MSqlQuery updatequery(MSqlQuery::InitCon());
00132
00133 updatequery.prepare("UPDATE videometadata set `hash` = :HASH "
00134 "WHERE `filename` = :FILENAME AND "
00135 "`host` = :HOST");
00136 updatequery.bindValue(":HASH", hash);
00137 updatequery.bindValue(":FILENAME", filename);
00138 updatequery.bindValue(":HOST", host);
00139 if (!updatequery.exec())
00140 MythDB::DBError(QObject::tr("Error: failed to hash file "
00141 "'%1'").arg(filename), updatequery);
00142 else
00143 LOG(VB_GENERAL, LOG_INFO,
00144 QString("Hash (%1) generated for file (%2)")
00145 .arg(hash).arg(filename));
00146 }
00147 }
00148 }
00149
00150 static bool InitializeVideoSchema(void)
00151 {
00152 MSqlQuery query(MSqlQuery::InitCon());
00153 LOG(VB_GENERAL, LOG_NOTICE,
00154 "Inserting initial video database information.");
00155
00156 const QString updates[] = {
00157 "CREATE TABLE dvdinput ("
00158 " intid int(10) unsigned NOT NULL,"
00159 " hsize int(10) unsigned DEFAULT NULL,"
00160 " vsize int(10) unsigned DEFAULT NULL,"
00161 " ar_num int(10) unsigned DEFAULT NULL,"
00162 " ar_denom int(10) unsigned DEFAULT NULL,"
00163 " fr_code int(10) unsigned DEFAULT NULL,"
00164 " letterbox tinyint(1) DEFAULT NULL,"
00165 " v_format varchar(16) DEFAULT NULL,"
00166 " PRIMARY KEY (intid)"
00167 ") ENGINE=MyISAM DEFAULT CHARSET=utf8;",
00168 "CREATE TABLE dvdtranscode ("
00169 " intid int(11) NOT NULL AUTO_INCREMENT,"
00170 " input int(10) unsigned DEFAULT NULL,"
00171 " `name` varchar(128) NOT NULL,"
00172 " sync_mode int(10) unsigned DEFAULT NULL,"
00173 " use_yv12 tinyint(1) DEFAULT NULL,"
00174 " cliptop int(11) DEFAULT NULL,"
00175 " clipbottom int(11) DEFAULT NULL,"
00176 " clipleft int(11) DEFAULT NULL,"
00177 " clipright int(11) DEFAULT NULL,"
00178 " f_resize_h int(11) DEFAULT NULL,"
00179 " f_resize_w int(11) DEFAULT NULL,"
00180 " hq_resize_h int(11) DEFAULT NULL,"
00181 " hq_resize_w int(11) DEFAULT NULL,"
00182 " grow_h int(11) DEFAULT NULL,"
00183 " grow_w int(11) DEFAULT NULL,"
00184 " clip2top int(11) DEFAULT NULL,"
00185 " clip2bottom int(11) DEFAULT NULL,"
00186 " clip2left int(11) DEFAULT NULL,"
00187 " clip2right int(11) DEFAULT NULL,"
00188 " codec varchar(128) NOT NULL,"
00189 " codec_param varchar(128) DEFAULT NULL,"
00190 " bitrate int(11) DEFAULT NULL,"
00191 " a_sample_r int(11) DEFAULT NULL,"
00192 " a_bitrate int(11) DEFAULT NULL,"
00193 " two_pass tinyint(1) DEFAULT NULL,"
00194 " tc_param varchar(128) DEFAULT NULL,"
00195 " PRIMARY KEY (intid)"
00196 ") ENGINE=MyISAM DEFAULT CHARSET=utf8;",
00197 "CREATE TABLE filemarkup ("
00198 " filename text NOT NULL,"
00199 " mark mediumint(8) unsigned NOT NULL DEFAULT '0',"
00200 " `offset` bigint(20) unsigned DEFAULT NULL,"
00201 " `type` tinyint(4) NOT NULL DEFAULT '0',"
00202 " KEY filename (filename(255))"
00203 ") ENGINE=MyISAM DEFAULT CHARSET=utf8;",
00204 "CREATE TABLE videocast ("
00205 " intid int(10) unsigned NOT NULL AUTO_INCREMENT,"
00206 " cast varchar(128) NOT NULL,"
00207 " PRIMARY KEY (intid)"
00208 ") ENGINE=MyISAM DEFAULT CHARSET=utf8;",
00209 "CREATE TABLE videocategory ("
00210 " intid int(10) unsigned NOT NULL AUTO_INCREMENT,"
00211 " category varchar(128) NOT NULL,"
00212 " PRIMARY KEY (intid)"
00213 ") ENGINE=MyISAM DEFAULT CHARSET=utf8;",
00214 "CREATE TABLE videocountry ("
00215 " intid int(10) unsigned NOT NULL AUTO_INCREMENT,"
00216 " country varchar(128) NOT NULL,"
00217 " PRIMARY KEY (intid)"
00218 ") ENGINE=MyISAM DEFAULT CHARSET=utf8;",
00219 "CREATE TABLE videogenre ("
00220 " intid int(10) unsigned NOT NULL AUTO_INCREMENT,"
00221 " genre varchar(128) NOT NULL,"
00222 " PRIMARY KEY (intid)"
00223 ") ENGINE=MyISAM DEFAULT CHARSET=utf8;",
00224 "CREATE TABLE videometadata ("
00225 " intid int(10) unsigned NOT NULL AUTO_INCREMENT,"
00226 " title varchar(128) NOT NULL,"
00227 " subtitle text NOT NULL,"
00228 " tagline varchar(255) DEFAULT NULL,"
00229 " director varchar(128) NOT NULL,"
00230 " studio varchar(128) DEFAULT NULL,"
00231 " plot text,"
00232 " rating varchar(128) NOT NULL,"
00233 " inetref varchar(255) NOT NULL,"
00234 " homepage text NOT NULL,"
00235 " `year` int(10) unsigned NOT NULL,"
00236 " releasedate date NOT NULL,"
00237 " userrating float NOT NULL,"
00238 " length int(10) unsigned NOT NULL,"
00239 " season smallint(5) unsigned NOT NULL DEFAULT '0',"
00240 " episode smallint(5) unsigned NOT NULL DEFAULT '0',"
00241 " showlevel int(10) unsigned NOT NULL,"
00242 " filename text NOT NULL,"
00243 " `hash` varchar(128) NOT NULL,"
00244 " coverfile text NOT NULL,"
00245 " childid int(11) NOT NULL DEFAULT '-1',"
00246 " browse tinyint(1) NOT NULL DEFAULT '1',"
00247 " watched tinyint(1) NOT NULL DEFAULT '0',"
00248 " processed tinyint(1) NOT NULL DEFAULT '0',"
00249 " playcommand varchar(255) DEFAULT NULL,"
00250 " category int(10) unsigned NOT NULL DEFAULT '0',"
00251 " trailer text,"
00252 " `host` text NOT NULL,"
00253 " screenshot text,"
00254 " banner text,"
00255 " fanart text,"
00256 " insertdate timestamp NULL DEFAULT CURRENT_TIMESTAMP,"
00257 " PRIMARY KEY (intid),"
00258 " KEY director (director),"
00259 " KEY title (title)"
00260 ") ENGINE=MyISAM DEFAULT CHARSET=utf8;",
00261 "CREATE TABLE videometadatacast ("
00262 " idvideo int(10) unsigned NOT NULL,"
00263 " idcast int(10) unsigned NOT NULL,"
00264 " UNIQUE KEY idvideo (idvideo,idcast)"
00265 ") ENGINE=MyISAM DEFAULT CHARSET=utf8;",
00266 "CREATE TABLE videometadatacountry ("
00267 " idvideo int(10) unsigned NOT NULL,"
00268 " idcountry int(10) unsigned NOT NULL,"
00269 " UNIQUE KEY idvideo_2 (idvideo,idcountry),"
00270 " KEY idvideo (idvideo),"
00271 " KEY idcountry (idcountry)"
00272 ") ENGINE=MyISAM DEFAULT CHARSET=utf8;",
00273 "CREATE TABLE videometadatagenre ("
00274 " idvideo int(10) unsigned NOT NULL,"
00275 " idgenre int(10) unsigned NOT NULL,"
00276 " UNIQUE KEY idvideo_2 (idvideo,idgenre),"
00277 " KEY idvideo (idvideo),"
00278 " KEY idgenre (idgenre)"
00279 ") ENGINE=MyISAM DEFAULT CHARSET=utf8;",
00280 "CREATE TABLE videotypes ("
00281 " intid int(10) unsigned NOT NULL AUTO_INCREMENT,"
00282 " extension varchar(128) NOT NULL,"
00283 " playcommand varchar(255) NOT NULL,"
00284 " f_ignore tinyint(1) DEFAULT NULL,"
00285 " use_default tinyint(1) DEFAULT NULL,"
00286 " PRIMARY KEY (intid)"
00287 ") ENGINE=MyISAM DEFAULT CHARSET=utf8;",
00288 "INSERT INTO dvdinput VALUES (1,720,480,16,9,1,1,'ntsc');",
00289 "INSERT INTO dvdinput VALUES (2,720,480,16,9,1,0,'ntsc');",
00290 "INSERT INTO dvdinput VALUES (3,720,480,4,3,1,1,'ntsc');",
00291 "INSERT INTO dvdinput VALUES (4,720,480,4,3,1,0,'ntsc');",
00292 "INSERT INTO dvdinput VALUES (5,720,576,16,9,3,1,'pal');",
00293 "INSERT INTO dvdinput VALUES (6,720,576,16,9,3,0,'pal');",
00294 "INSERT INTO dvdinput VALUES (7,720,576,4,3,3,1,'pal');",
00295 "INSERT INTO dvdinput VALUES (8,720,576,4,3,3,0,'pal');",
00296 "INSERT INTO dvdtranscode VALUES (1,1,'Good',2,1,16,16,0,0,2,0,0,0,0,0,32,32,8,8,'divx5',NULL,1618,NULL,NULL,0,NULL);",
00297 "INSERT INTO dvdtranscode VALUES (2,2,'Excellent',2,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,'divx5',NULL,0,NULL,NULL,1,NULL);",
00298 "INSERT INTO dvdtranscode VALUES (3,2,'Good',2,1,0,0,8,8,0,0,0,0,0,0,0,0,0,0,'divx5',NULL,1618,NULL,NULL,0,NULL);",
00299 "INSERT INTO dvdtranscode VALUES (4,2,'Medium',2,1,0,0,8,8,5,5,0,0,0,0,0,0,0,0,'divx5',NULL,1200,NULL,NULL,0,NULL);",
00300 "INSERT INTO dvdtranscode VALUES (5,3,'Good',2,1,0,0,0,0,0,0,0,0,2,0,80,80,8,8,'divx5',NULL,0,NULL,NULL,0,NULL);",
00301 "INSERT INTO dvdtranscode VALUES (6,4,'Excellent',2,1,0,0,0,0,0,0,0,0,2,0,0,0,0,0,'divx5',NULL,0,NULL,NULL,1,NULL);",
00302 "INSERT INTO dvdtranscode VALUES (7,4,'Good',2,1,0,0,8,8,0,2,0,0,0,0,0,0,0,0,'divx5',NULL,1618,NULL,NULL,0,NULL);",
00303 "INSERT INTO dvdtranscode VALUES (8,5,'Good',1,1,16,16,0,0,5,0,0,0,0,0,40,40,8,8,'divx5',NULL,1618,NULL,NULL,0,NULL);",
00304 "INSERT INTO dvdtranscode VALUES (9,6,'Good',1,1,0,0,16,16,5,0,0,0,0,0,0,0,0,0,'divx5',NULL,1618,NULL,NULL,0,NULL);",
00305 "INSERT INTO dvdtranscode VALUES (10,7,'Good',1,1,0,0,0,0,1,0,0,0,0,0,76,76,8,8,'divx5',NULL,1618,NULL,NULL,0,NULL);",
00306 "INSERT INTO dvdtranscode VALUES (11,8,'Good',1,1,0,0,0,0,1,0,0,0,0,0,0,0,0,0,'divx5',NULL,1618,NULL,NULL,0,NULL);",
00307 "INSERT INTO videotypes VALUES (1,'txt','',1,0);",
00308 "INSERT INTO videotypes VALUES (2,'log','',1,0);",
00309 "INSERT INTO videotypes VALUES (3,'mpg','Internal',0,0);",
00310 "INSERT INTO videotypes VALUES (4,'avi','',0,1);",
00311 "INSERT INTO videotypes VALUES (5,'vob','Internal',0,0);",
00312 "INSERT INTO videotypes VALUES (6,'mpeg','Internal',0,0);",
00313 "INSERT INTO videotypes VALUES (8,'iso','Internal',0,0);",
00314 "INSERT INTO videotypes VALUES (9,'img','Internal',0,0);",
00315 "INSERT INTO videotypes VALUES (10,'mkv','Internal',0,0);",
00316 "INSERT INTO videotypes VALUES (11,'mp4','Internal',0,0);",
00317 "INSERT INTO videotypes VALUES (12,'m2ts','Internal',0,0);",
00318 "INSERT INTO videotypes VALUES (13,'evo','Internal',0,0);",
00319 "INSERT INTO videotypes VALUES (14,'divx','Internal',0,0);",
00320 "INSERT INTO videotypes VALUES (15,'mov','Internal',0,0);",
00321 "INSERT INTO videotypes VALUES (16,'qt','Internal',0,0);",
00322 "INSERT INTO videotypes VALUES (17,'wmv','Internal',0,0);",
00323 "INSERT INTO videotypes VALUES (18,'3gp','Internal',0,0);",
00324 "INSERT INTO videotypes VALUES (19,'asf','Internal',0,0);",
00325 "INSERT INTO videotypes VALUES (20,'ogg','Internal',0,0);",
00326 "INSERT INTO videotypes VALUES (21,'ogm','Internal',0,0);",
00327 "INSERT INTO videotypes VALUES (22,'flv','Internal',0,0);",
00328 "INSERT INTO videotypes VALUES (23,'ogv','Internal',0,0);",
00329 "INSERT INTO videotypes VALUES (25,'nut','Internal',0,0);",
00330 "INSERT INTO videotypes VALUES (26,'mxf','Internal',0,0);",
00331 "INSERT INTO videotypes VALUES (27,'m4v','Internal',0,0);",
00332 "INSERT INTO videotypes VALUES (28,'rm','Internal',0,0);",
00333 "INSERT INTO videotypes VALUES (29,'ts','Internal',0,0);",
00334 "INSERT INTO videotypes VALUES (30,'swf','Internal',0,0);",
00335 "INSERT INTO videotypes VALUES (31,'f4v','Internal',0,0);",
00336 "INSERT INTO videotypes VALUES (32,'nuv','Internal',0,0);",
00337 NULL
00338 };
00339
00340 QString dbver = "";
00341 if (!performActualUpdate(updates, finalVideoDatabaseVersion, dbver,
00342 MythVideoVersionName))
00343 return false;
00344
00345 return true;
00346 }
00347
00348 bool doUpgradeVideoDatabaseSchema(void)
00349 {
00350 QString dbver = gCoreContext->GetSetting("mythvideo.DBSchemaVer");
00351 if (dbver == finalVideoDatabaseVersion)
00352 {
00353 return true;
00354 }
00355
00356 QString olddbver = gCoreContext->GetSetting("VideoDBSchemaVer");
00357 QString dvddbver = gCoreContext->GetSetting("DVDDBSchemaVer");
00358 if (dbver.isEmpty() && olddbver.isEmpty() && dvddbver.isEmpty())
00359 {
00360 if (!InitializeVideoSchema())
00361 return false;
00362 dbver = gCoreContext->GetSetting("mythvideo.DBSchemaVer");
00363 }
00364
00365 if (dbver.isEmpty() || dbver.toInt() < minimumVideoDatabaseVersion.toInt())
00366 {
00367 LOG(VB_GENERAL, LOG_ERR,
00368 "Unrecognized video database schema version. "
00369 "Unable to upgrade database.");
00370 LOG(VB_GENERAL, LOG_ERR, QString("mythvideo.DBSchemaVer: '%1', "
00371 "VideoDBSchemaVer: '%2', DVDDBSchemaVer: '%3'").arg(dbver)
00372 .arg(olddbver).arg(dvddbver));
00373 return false;
00374 }
00375
00376 if (dbver == "1016")
00377 {
00378 const QString updates[] = {
00379 "ALTER TABLE dvdbookmark"
00380 " MODIFY serialid varbinary(16) NOT NULL default '',"
00381 " MODIFY name varbinary(32) default NULL;",
00382 "ALTER TABLE dvdinput"
00383 " MODIFY v_format varbinary(16) default NULL;",
00384 "ALTER TABLE dvdtranscode"
00385 " MODIFY name varbinary(128) NOT NULL,"
00386 " MODIFY codec varbinary(128) NOT NULL,"
00387 " MODIFY codec_param varbinary(128) default NULL,"
00388 " MODIFY tc_param varbinary(128) default NULL;",
00389 "ALTER TABLE filemarkup"
00390 " MODIFY filename blob NOT NULL;",
00391 "ALTER TABLE videocast"
00392 " MODIFY cast varbinary(128) NOT NULL;",
00393 "ALTER TABLE videocategory"
00394 " MODIFY category varbinary(128) NOT NULL;",
00395 "ALTER TABLE videocountry"
00396 " MODIFY country varbinary(128) NOT NULL;",
00397 "ALTER TABLE videogenre"
00398 " MODIFY genre varbinary(128) NOT NULL;",
00399 "ALTER TABLE videometadata"
00400 " MODIFY title varbinary(128) NOT NULL,"
00401 " MODIFY director varbinary(128) NOT NULL,"
00402 " MODIFY plot blob,"
00403 " MODIFY rating varbinary(128) NOT NULL,"
00404 " MODIFY inetref varbinary(255) NOT NULL,"
00405 " MODIFY filename blob NOT NULL,"
00406 " MODIFY coverfile blob NOT NULL,"
00407 " MODIFY playcommand varbinary(255) default NULL;",
00408 "ALTER TABLE videotypes"
00409 " MODIFY extension varbinary(128) NOT NULL,"
00410 " MODIFY playcommand varbinary(255) NOT NULL;",
00411 ""
00412 };
00413
00414 if (!performActualUpdate(updates, "1017", dbver,
00415 MythVideoVersionName))
00416 return false;
00417 }
00418
00419
00420 if (dbver == "1017")
00421 {
00422 const QString updates[] = {
00423 "ALTER TABLE dvdbookmark"
00424 " DEFAULT CHARACTER SET default,"
00425 " MODIFY serialid varchar(16) CHARACTER SET utf8 NOT NULL default '',"
00426 " MODIFY name varchar(32) CHARACTER SET utf8 default NULL;",
00427 "ALTER TABLE dvdinput"
00428 " DEFAULT CHARACTER SET default,"
00429 " MODIFY v_format varchar(16) CHARACTER SET utf8 default NULL;",
00430 "ALTER TABLE dvdtranscode"
00431 " DEFAULT CHARACTER SET default,"
00432 " MODIFY name varchar(128) CHARACTER SET utf8 NOT NULL,"
00433 " MODIFY codec varchar(128) CHARACTER SET utf8 NOT NULL,"
00434 " MODIFY codec_param varchar(128) CHARACTER SET utf8 default NULL,"
00435 " MODIFY tc_param varchar(128) CHARACTER SET utf8 default NULL;",
00436 "ALTER TABLE filemarkup"
00437 " DEFAULT CHARACTER SET default,"
00438 " MODIFY filename text CHARACTER SET utf8 NOT NULL;",
00439 "ALTER TABLE videocast"
00440 " DEFAULT CHARACTER SET default,"
00441 " MODIFY cast varchar(128) CHARACTER SET utf8 NOT NULL;",
00442 "ALTER TABLE videocategory"
00443 " DEFAULT CHARACTER SET default,"
00444 " MODIFY category varchar(128) CHARACTER SET utf8 NOT NULL;",
00445 "ALTER TABLE videocountry"
00446 " DEFAULT CHARACTER SET default,"
00447 " MODIFY country varchar(128) CHARACTER SET utf8 NOT NULL;",
00448 "ALTER TABLE videogenre"
00449 " DEFAULT CHARACTER SET default,"
00450 " MODIFY genre varchar(128) CHARACTER SET utf8 NOT NULL;",
00451 "ALTER TABLE videometadata"
00452 " DEFAULT CHARACTER SET default,"
00453 " MODIFY title varchar(128) CHARACTER SET utf8 NOT NULL,"
00454 " MODIFY director varchar(128) CHARACTER SET utf8 NOT NULL,"
00455 " MODIFY plot text CHARACTER SET utf8,"
00456 " MODIFY rating varchar(128) CHARACTER SET utf8 NOT NULL,"
00457 " MODIFY inetref varchar(255) CHARACTER SET utf8 NOT NULL,"
00458 " MODIFY filename text CHARACTER SET utf8 NOT NULL,"
00459 " MODIFY coverfile text CHARACTER SET utf8 NOT NULL,"
00460 " MODIFY playcommand varchar(255) CHARACTER SET utf8 default NULL;",
00461 "ALTER TABLE videometadatacast"
00462 " DEFAULT CHARACTER SET default;",
00463 "ALTER TABLE videometadatacountry"
00464 " DEFAULT CHARACTER SET default;",
00465 "ALTER TABLE videometadatagenre"
00466 " DEFAULT CHARACTER SET default;",
00467 "ALTER TABLE videotypes"
00468 " DEFAULT CHARACTER SET default,"
00469 " MODIFY extension varchar(128) CHARACTER SET utf8 NOT NULL,"
00470 " MODIFY playcommand varchar(255) CHARACTER SET utf8 NOT NULL;",
00471 ""
00472 };
00473
00474 if (!performActualUpdate(updates, "1018", dbver,
00475 MythVideoVersionName))
00476 return false;
00477 }
00478
00479 if (dbver == "1018")
00480 {
00481 QStringList updates;
00482 updates += "DELETE FROM settings WHERE value="
00483 "'MovieListCommandLine' AND data LIKE '%imdb%';";
00484 updates += "DELETE FROM settings WHERE value="
00485 "'MovieDataCommandLine' AND data LIKE '%imdb%';";
00486 updates += "DELETE FROM settings WHERE value="
00487 "'MoviePosterCommandLine' AND data LIKE '%imdb%';";
00488 if (!performActualUpdate(updates, "1019", dbver,
00489 MythVideoVersionName))
00490 return false;
00491 }
00492
00493 if (dbver == "1019")
00494 {
00495 QStringList updates(
00496 "ALTER TABLE videometadata ADD `trailer` TEXT;");
00497 if (!performActualUpdate(updates, "1020", dbver,
00498 MythVideoVersionName))
00499 return false;
00500 }
00501
00502 if (dbver == "1020")
00503 {
00504 LOG(VB_GENERAL, LOG_NOTICE,
00505 "Upgrading to MythVideo schema version 1021");
00506
00507 AddFileType("mkv");
00508 AddFileType("mp4");
00509 AddFileType("m2ts");
00510 AddFileType("evo");
00511 AddFileType("divx");
00512 AddFileType("mov");
00513 AddFileType("qt");
00514 AddFileType("wmv");
00515 AddFileType("3gp");
00516 AddFileType("asf");
00517 AddFileType("ogg");
00518 AddFileType("ogm");
00519 AddFileType("flv");
00520
00521 if (!UpdateDBVersionNumber(MythVideoVersionName, "1021"))
00522 return false;
00523
00524 dbver = "1021";
00525 }
00526
00527 if (dbver == "1021")
00528 {
00529 QStringList updates;
00530 updates += "ALTER TABLE videometadata ADD host text CHARACTER SET utf8 NOT NULL;";
00531
00532 if (!performActualUpdate(updates, "1022", dbver,
00533 MythVideoVersionName))
00534 return false;
00535 }
00536
00537 if (dbver == "1022")
00538 {
00539 QStringList updates;
00540 updates += "ALTER TABLE videometadata ADD `screenshot` TEXT;";
00541 updates += "ALTER TABLE videometadata ADD `banner` TEXT;";
00542 updates += "ALTER TABLE videometadata ADD `fanart` TEXT;";
00543 if (!performActualUpdate(updates, "1023", dbver,
00544 MythVideoVersionName))
00545 return false;
00546 }
00547
00548 if (dbver == "1023")
00549 {
00550 QStringList updates;
00551 updates += "ALTER TABLE videometadata ADD `subtitle` TEXT "
00552 "NOT NULL AFTER `title`;";
00553 updates += "ALTER TABLE videometadata ADD `season` SMALLINT "
00554 "UNSIGNED NOT NULL DEFAULT '0' AFTER `length`;";
00555 updates += "ALTER TABLE videometadata ADD `episode` SMALLINT "
00556 "UNSIGNED NOT NULL DEFAULT '0' AFTER `season`;";
00557 if (!performActualUpdate(updates, "1024", dbver,
00558 MythVideoVersionName))
00559 return false;
00560 }
00561
00562 if (dbver == "1024")
00563 {
00564 QStringList updates;
00565 updates += "ALTER TABLE videometadata ADD watched BOOL "
00566 "NOT NULL DEFAULT 0 AFTER browse;";
00567 if (!performActualUpdate(updates, "1025", dbver,
00568 MythVideoVersionName))
00569 return false;
00570 }
00571
00572 if (dbver == "1025")
00573 {
00574 QStringList updates;
00575 updates += "ALTER TABLE videometadata ADD `insertdate` TIMESTAMP "
00576 "NULL DEFAULT CURRENT_TIMESTAMP AFTER `fanart`;";
00577 if (!performActualUpdate(updates, "1026", dbver,
00578 MythVideoVersionName))
00579 return false;
00580 }
00581
00582 if (dbver == "1026")
00583 {
00584 QStringList updates;
00585 updates += "DELETE FROM keybindings "
00586 " WHERE action = 'DELETE' AND context = 'Video';";
00587 if (!performActualUpdate(updates, "1027", dbver,
00588 MythVideoVersionName))
00589 return false;
00590 }
00591
00592 if (dbver == "1027")
00593 {
00594 LOG(VB_GENERAL, LOG_NOTICE,
00595 "Upgrading to MythVideo schema version 1028");
00596 LOG(VB_GENERAL, LOG_INFO,
00597 "Converting filenames in filemarkup table "
00598 "from absolute to relative paths. This may take a long "
00599 "time if you have a large number of MythVideo seektables.");
00600
00601 bool ok = true;
00602 MSqlQuery query(MSqlQuery::InitCon());
00603 MSqlQuery update(MSqlQuery::InitCon());
00604
00605 query.prepare("SELECT DISTINCT filename FROM filemarkup;");
00606 update.prepare("UPDATE filemarkup SET filename = :RELPATH "
00607 " WHERE filename = :FULLPATH;");
00608 if (query.exec())
00609 {
00610 QString origPath;
00611 QString relPath;
00612 while (query.next())
00613 {
00614 origPath = query.value(0).toString();
00615 if (origPath.startsWith("dvd:"))
00616 continue;
00617
00618 relPath = StorageGroup::GetRelativePathname(origPath);
00619 if ((!relPath.isEmpty()) &&
00620 (relPath != origPath))
00621 {
00622 update.bindValue(":RELPATH", relPath);
00623 update.bindValue(":FULLPATH", origPath);
00624 if (!update.exec())
00625 {
00626 LOG(VB_GENERAL, LOG_ERR,
00627 QString("ERROR converting '%1' to '%2' in "
00628 "filemarkup table.")
00629 .arg(origPath).arg(relPath));
00630 ok = false;
00631 }
00632 }
00633 }
00634 }
00635 else
00636 ok = false;
00637
00638 if (!ok)
00639 return false;
00640
00641 if (!UpdateDBVersionNumber(MythVideoVersionName, "1028"))
00642 return false;
00643
00644 dbver = "1028";
00645 }
00646
00647 if (dbver == "1028")
00648 {
00649 QStringList updates;
00650 updates += "ALTER TABLE videometadata ADD `releasedate` DATE "
00651 "NOT NULL AFTER `year`;";
00652 updates += "ALTER TABLE videometadata ADD `homepage` TEXT "
00653 "NOT NULL AFTER `inetref`;";
00654 if (!performActualUpdate(updates, "1029", dbver,
00655 MythVideoVersionName))
00656 return false;
00657 }
00658
00659 if (dbver == "1029")
00660 {
00661 QStringList updates;
00662 updates += "ALTER TABLE videometadata ADD `hash` VARCHAR(128) "
00663 "NOT NULL AFTER `filename`;";
00664 if (!performActualUpdate(updates, "1030", dbver,
00665 MythVideoVersionName))
00666 return false;
00667 }
00668
00669 if (dbver == "1030")
00670 {
00671 UpdateHashes();
00672 if (!UpdateDBVersionNumber(MythVideoVersionName, "1031"))
00673 return false;
00674
00675 dbver = "1031";
00676 }
00677
00678 if (dbver == "1031")
00679 {
00680 MSqlQuery query(MSqlQuery::InitCon());
00681 query.prepare("SHOW INDEX FROM videometadata");
00682
00683 if (!query.exec())
00684 {
00685 MythDB::DBError("Unable to retrieve current indices on "
00686 "videometadata.", query);
00687 }
00688 else
00689 {
00690 while (query.next())
00691 {
00692 QString index_name = query.value(2).toString();
00693
00694 if ("title_2" == index_name)
00695 {
00696 MSqlQuery update(MSqlQuery::InitCon());
00697 update.prepare("ALTER TABLE videometadata "
00698 " DROP INDEX title_2");
00699
00700 if (!update.exec())
00701 MythDB::DBError("Unable to drop duplicate index "
00702 "on videometadata. Ignoring.",
00703 update);
00704 break;
00705 }
00706 }
00707 }
00708
00709 if (!UpdateDBVersionNumber(MythVideoVersionName, "1032"))
00710 return false;
00711
00712 dbver = "1032";
00713 }
00714
00715 if (dbver == "1032")
00716 {
00717 QStringList updates;
00718 updates += "CREATE TEMPORARY TABLE bad_videometadatacast"
00719 " AS SELECT * FROM videometadatacast;";
00720 updates += "CREATE TEMPORARY TABLE bad_videometadatagenre"
00721 " AS SELECT * FROM videometadatagenre;";
00722 updates += "CREATE TEMPORARY TABLE bad_videometadatacountry"
00723 " AS SELECT * FROM videometadatacountry;";
00724 updates += "TRUNCATE TABLE videometadatacast;";
00725 updates += "TRUNCATE TABLE videometadatagenre;";
00726 updates += "TRUNCATE TABLE videometadatacountry;";
00727 updates += "INSERT videometadatacast SELECT idvideo,idcast"
00728 " FROM bad_videometadatacast GROUP BY idvideo,idcast;";
00729 updates += "INSERT videometadatagenre SELECT idvideo,idgenre"
00730 " FROM bad_videometadatagenre GROUP BY idvideo,idgenre;";
00731 updates += "INSERT videometadatacountry SELECT idvideo,idcountry"
00732 " FROM bad_videometadatacountry GROUP BY idvideo,idcountry;";
00733 updates += "DROP TEMPORARY TABLE bad_videometadatacast;";
00734 updates += "DROP TEMPORARY TABLE bad_videometadatagenre;";
00735 updates += "DROP TEMPORARY TABLE bad_videometadatacountry;";
00736 updates += "ALTER TABLE videometadatacast ADD UNIQUE INDEX (`idvideo`,`idcast`);";
00737 updates += "ALTER TABLE videometadatagenre ADD UNIQUE INDEX (`idvideo`,`idgenre`);";
00738 updates +="ALTER TABLE videometadatacountry ADD UNIQUE INDEX (`idvideo`,`idcountry`);";
00739 if (!performActualUpdate(updates, "1033", dbver,
00740 MythVideoVersionName))
00741 return false;
00742
00743 dbver = "1033";
00744 }
00745
00746 if (dbver == "1033")
00747 {
00748 AddFileType("ogv");
00749 AddFileType("BDMV");
00750 AddFileType("nut");
00751 AddFileType("mxf");
00752 AddFileType("m4v");
00753 AddFileType("rm");
00754 AddFileType("ts");
00755 AddFileType("swf");
00756 AddFileType("f4v");
00757 AddFileType("nuv");
00758
00759 if (!UpdateDBVersionNumber(MythVideoVersionName, "1034"))
00760 return false;
00761
00762 dbver = "1034";
00763 }
00764
00765 if (dbver == "1034")
00766 {
00767 QStringList updates;
00768 updates += "ALTER TABLE videometadata ADD `tagline` VARCHAR (255) "
00769 "AFTER `subtitle`;";
00770
00771 if (!performActualUpdate(updates, "1035", dbver,
00772 MythVideoVersionName))
00773 return false;
00774 }
00775
00776 if (dbver == "1035")
00777 {
00778 QStringList updates;
00779 updates += "ALTER TABLE videometadata ADD processed BOOL "
00780 "NOT NULL DEFAULT 0 AFTER watched;";
00781 if (!performActualUpdate(updates, "1036", dbver,
00782 MythVideoVersionName))
00783 return false;
00784 }
00785
00786 if (dbver == "1036")
00787 {
00788 QStringList updates;
00789 updates += "ALTER TABLE videometadata ADD `studio` VARCHAR( 128 ) "
00790 "AFTER `director`;";
00791 if (!performActualUpdate(updates, "1037", dbver,
00792 MythVideoVersionName))
00793 return false;
00794 }
00795
00796 if (dbver == "1037")
00797 {
00798 QStringList updates;
00799 updates += "DELETE FROM videotypes WHERE extension = 'VIDEO_TS';";
00800 updates += "DELETE FROM videotypes WHERE extension = 'BDMV';";
00801 if (!performActualUpdate(updates, "1038", dbver,
00802 MythVideoVersionName))
00803 return false;
00804 }
00805
00806 return true;
00807 }
00808
