MythTV  master
videodbcheck.cpp
Go to the documentation of this file.
1 /* Integrates the MythVideo DB schema into the core MythTV DB schema. Used
2  * only by DB update 1267. Any further changes to the core MythTV schema,
3  * including changes to those parts that were previously MythVideo, should
4  * be done in mythtv/libs/libmythtv/dbcheck.cpp. */
5 
6 #include <QString>
7 #include <QStringList>
8 
9 #include "videodbcheck.h"
10 #include "mythdb.h"
11 #include "mythcorecontext.h"
12 #include "mythlogging.h"
13 #include "remotefile.h"
14 #include "mythmiscutil.h"
16 
17 const QString minimumVideoDatabaseVersion = "1016";
18 const QString finalVideoDatabaseVersion = "1038";
19 const QString MythVideoVersionName = "mythvideo.DBSchemaVer";
20 
21 static bool UpdateDBVersionNumber(const QString &field_name,
22  const QString &newnumber)
23 {
25 
26  if (!query.exec(QString("DELETE FROM settings WHERE value='%1';")
27  .arg(field_name)))
28  {
29  MythDB::DBError("UpdateDBVersionNumber - delete", query);
30  return false;
31  }
32 
33  if (!query.exec(QString("INSERT INTO settings (value, data, hostname) "
34  "VALUES ('%1', %2, NULL);")
35  .arg(field_name).arg(newnumber)))
36  {
37  MythDB::DBError("UpdateDBVersionNumber - insert", query);
38  return false;
39  }
40 
41  LOG(VB_GENERAL, LOG_NOTICE,
42  QString("Upgraded to MythVideo schema version %1") .arg(newnumber));
43  return true;
44 }
45 
46 static bool performActualUpdate(const QStringList &updates,
47  const QString &version,
48  QString &dbver, const QString &field_name)
49 {
51 
52  LOG(VB_GENERAL, LOG_NOTICE,
53  QString("Upgrading to MythVideo schema version %1") .arg(version));
54 
55  foreach (const auto & update, updates)
56  {
57  if (!query.exec(update))
58  {
59  MythDB::DBError("performActualUpdate", query);
60  return false;
61  }
62  }
63 
64  if (!UpdateDBVersionNumber(field_name, version))
65  return false;
66  dbver = version;
67  return true;
68 }
69 
70 static bool performActualUpdate(const QString updates[], const QString &version,
71  QString &dbver, const QString &field_name)
72 {
73  QStringList upQuery;
74  for (int i = 0; ; ++i)
75  {
76  QString q = updates[i];
77  if (q == "") break;
78  upQuery.append(q);
79  }
80  return performActualUpdate(upQuery, version, dbver, field_name);
81 }
82 
83 static void AddFileType(const QString &extension,
84  const QString &playCommand = QString("Internal"),
85  bool ignored = false, bool useDefault = false)
86 {
88  query.prepare("SELECT * FROM videotypes WHERE "
89  "LOWER(extension) = LOWER(:EXTENSION) LIMIT 1");
90  query.bindValue(":EXTENSION", extension);
91  if (query.exec() && !query.size())
92  {
93  query.prepare("INSERT INTO videotypes (extension, playcommand, "
94  "f_ignore, use_default) VALUES (:EXTENSION, :PLAYCOMMAND, "
95  ":IGNORE, :USEDEFAULT)");
96  query.bindValue(":EXTENSION", extension);
97  query.bindValue(":PLAYCOMMAND", playCommand);
98  query.bindValue(":IGNORE", ignored);
99  query.bindValue(":USEDEFAULT", useDefault);
100  if (!query.exec())
101  MythDB::DBError(QObject::tr("Error: failed to add new file "
102  "type '%1'").arg(extension), query);
103  }
104 }
105 
106 static void UpdateHashes(void)
107 {
108  MSqlQuery query(MSqlQuery::InitCon());
109  query.prepare("SELECT `filename`, `host` FROM videometadata WHERE "
110  "`hash` = \"\"");
111  if (query.exec() && query.size())
112  {
113  while (query.next())
114  {
115  QString filename = query.value(0).toString();
116  QString host = query.value(1).toString();
117  QString hash;
118 
119  if (!host.isEmpty())
120  {
121  QString url = generate_file_url("Videos", host, filename);
122  hash = RemoteFile::GetFileHash(url);
123  }
124  else
125  hash = FileHash(filename);
126 
127  if (hash == "NULL")
128  hash = QString();
129 
130  MSqlQuery updatequery(MSqlQuery::InitCon());
131 
132  updatequery.prepare("UPDATE videometadata set `hash` = :HASH "
133  "WHERE `filename` = :FILENAME AND "
134  "`host` = :HOST");
135  updatequery.bindValue(":HASH", hash);
136  updatequery.bindValue(":FILENAME", filename);
137  updatequery.bindValue(":HOST", host);
138  if (!updatequery.exec())
139  {
140  MythDB::DBError(QObject::tr("Error: failed to hash file "
141  "'%1'").arg(filename), updatequery);
142  }
143  else
144  {
145  LOG(VB_GENERAL, LOG_INFO,
146  QString("Hash (%1) generated for file (%2)")
147  .arg(hash).arg(filename));
148  }
149  }
150  }
151 }
152 
153 static bool InitializeVideoSchema(void)
154 {
155  MSqlQuery query(MSqlQuery::InitCon());
156  LOG(VB_GENERAL, LOG_NOTICE,
157  "Inserting initial video database information.");
158 
159  const QString updates[] = {
160 "CREATE TABLE dvdinput ("
161 " intid int(10) unsigned NOT NULL,"
162 " hsize int(10) unsigned DEFAULT NULL,"
163 " vsize int(10) unsigned DEFAULT NULL,"
164 " ar_num int(10) unsigned DEFAULT NULL,"
165 " ar_denom int(10) unsigned DEFAULT NULL,"
166 " fr_code int(10) unsigned DEFAULT NULL,"
167 " letterbox tinyint(1) DEFAULT NULL,"
168 " v_format varchar(16) DEFAULT NULL,"
169 " PRIMARY KEY (intid)"
170 ") ENGINE=MyISAM DEFAULT CHARSET=utf8;",
171 "CREATE TABLE dvdtranscode ("
172 " intid int(11) NOT NULL AUTO_INCREMENT,"
173 " input int(10) unsigned DEFAULT NULL,"
174 " `name` varchar(128) NOT NULL,"
175 " sync_mode int(10) unsigned DEFAULT NULL,"
176 " use_yv12 tinyint(1) DEFAULT NULL,"
177 " cliptop int(11) DEFAULT NULL,"
178 " clipbottom int(11) DEFAULT NULL,"
179 " clipleft int(11) DEFAULT NULL,"
180 " clipright int(11) DEFAULT NULL,"
181 " f_resize_h int(11) DEFAULT NULL,"
182 " f_resize_w int(11) DEFAULT NULL,"
183 " hq_resize_h int(11) DEFAULT NULL,"
184 " hq_resize_w int(11) DEFAULT NULL,"
185 " grow_h int(11) DEFAULT NULL,"
186 " grow_w int(11) DEFAULT NULL,"
187 " clip2top int(11) DEFAULT NULL,"
188 " clip2bottom int(11) DEFAULT NULL,"
189 " clip2left int(11) DEFAULT NULL,"
190 " clip2right int(11) DEFAULT NULL,"
191 " codec varchar(128) NOT NULL,"
192 " codec_param varchar(128) DEFAULT NULL,"
193 " bitrate int(11) DEFAULT NULL,"
194 " a_sample_r int(11) DEFAULT NULL,"
195 " a_bitrate int(11) DEFAULT NULL,"
196 " two_pass tinyint(1) DEFAULT NULL,"
197 " tc_param varchar(128) DEFAULT NULL,"
198 " PRIMARY KEY (intid)"
199 ") ENGINE=MyISAM DEFAULT CHARSET=utf8;",
200 "CREATE TABLE filemarkup ("
201 " filename text NOT NULL,"
202 " mark mediumint(8) unsigned NOT NULL DEFAULT '0',"
203 " `offset` bigint(20) unsigned DEFAULT NULL,"
204 " `type` tinyint(4) NOT NULL DEFAULT '0',"
205 " KEY filename (filename(255))"
206 ") ENGINE=MyISAM DEFAULT CHARSET=utf8;",
207 "CREATE TABLE videocast ("
208 " intid int(10) unsigned NOT NULL AUTO_INCREMENT,"
209 " cast varchar(128) NOT NULL,"
210 " PRIMARY KEY (intid)"
211 ") ENGINE=MyISAM DEFAULT CHARSET=utf8;",
212 "CREATE TABLE videocategory ("
213 " intid int(10) unsigned NOT NULL AUTO_INCREMENT,"
214 " category varchar(128) NOT NULL,"
215 " PRIMARY KEY (intid)"
216 ") ENGINE=MyISAM DEFAULT CHARSET=utf8;",
217 "CREATE TABLE videocountry ("
218 " intid int(10) unsigned NOT NULL AUTO_INCREMENT,"
219 " country varchar(128) NOT NULL,"
220 " PRIMARY KEY (intid)"
221 ") ENGINE=MyISAM DEFAULT CHARSET=utf8;",
222 "CREATE TABLE videogenre ("
223 " intid int(10) unsigned NOT NULL AUTO_INCREMENT,"
224 " genre varchar(128) NOT NULL,"
225 " PRIMARY KEY (intid)"
226 ") ENGINE=MyISAM DEFAULT CHARSET=utf8;",
227 "CREATE TABLE videometadata ("
228 " intid int(10) unsigned NOT NULL AUTO_INCREMENT,"
229 " title varchar(128) NOT NULL,"
230 " subtitle text NOT NULL,"
231 " tagline varchar(255) DEFAULT NULL,"
232 " director varchar(128) NOT NULL,"
233 " studio varchar(128) DEFAULT NULL,"
234 " plot text,"
235 " rating varchar(128) NOT NULL,"
236 " inetref varchar(255) NOT NULL,"
237 " homepage text NOT NULL,"
238 " `year` int(10) unsigned NOT NULL,"
239 " releasedate date NOT NULL,"
240 " userrating float NOT NULL,"
241 " length int(10) unsigned NOT NULL,"
242 " season smallint(5) unsigned NOT NULL DEFAULT '0',"
243 " episode smallint(5) unsigned NOT NULL DEFAULT '0',"
244 " showlevel int(10) unsigned NOT NULL,"
245 " filename text NOT NULL,"
246 " `hash` varchar(128) NOT NULL,"
247 " coverfile text NOT NULL,"
248 " childid int(11) NOT NULL DEFAULT '-1',"
249 " browse tinyint(1) NOT NULL DEFAULT '1',"
250 " watched tinyint(1) NOT NULL DEFAULT '0',"
251 " processed tinyint(1) NOT NULL DEFAULT '0',"
252 " playcommand varchar(255) DEFAULT NULL,"
253 " category int(10) unsigned NOT NULL DEFAULT '0',"
254 " trailer text,"
255 " `host` text NOT NULL,"
256 " screenshot text,"
257 " banner text,"
258 " fanart text,"
259 " insertdate timestamp NULL DEFAULT CURRENT_TIMESTAMP,"
260 " PRIMARY KEY (intid),"
261 " KEY director (director),"
262 " KEY title (title)"
263 ") ENGINE=MyISAM DEFAULT CHARSET=utf8;",
264 "CREATE TABLE videometadatacast ("
265 " idvideo int(10) unsigned NOT NULL,"
266 " idcast int(10) unsigned NOT NULL,"
267 " UNIQUE KEY idvideo (idvideo,idcast)"
268 ") ENGINE=MyISAM DEFAULT CHARSET=utf8;",
269 "CREATE TABLE videometadatacountry ("
270 " idvideo int(10) unsigned NOT NULL,"
271 " idcountry int(10) unsigned NOT NULL,"
272 " UNIQUE KEY idvideo_2 (idvideo,idcountry),"
273 " KEY idvideo (idvideo),"
274 " KEY idcountry (idcountry)"
275 ") ENGINE=MyISAM DEFAULT CHARSET=utf8;",
276 "CREATE TABLE videometadatagenre ("
277 " idvideo int(10) unsigned NOT NULL,"
278 " idgenre int(10) unsigned NOT NULL,"
279 " UNIQUE KEY idvideo_2 (idvideo,idgenre),"
280 " KEY idvideo (idvideo),"
281 " KEY idgenre (idgenre)"
282 ") ENGINE=MyISAM DEFAULT CHARSET=utf8;",
283 "CREATE TABLE videotypes ("
284 " intid int(10) unsigned NOT NULL AUTO_INCREMENT,"
285 " extension varchar(128) NOT NULL,"
286 " playcommand varchar(255) NOT NULL,"
287 " f_ignore tinyint(1) DEFAULT NULL,"
288 " use_default tinyint(1) DEFAULT NULL,"
289 " PRIMARY KEY (intid)"
290 ") ENGINE=MyISAM DEFAULT CHARSET=utf8;",
291 "INSERT INTO dvdinput VALUES (1,720,480,16,9,1,1,'ntsc');",
292 "INSERT INTO dvdinput VALUES (2,720,480,16,9,1,0,'ntsc');",
293 "INSERT INTO dvdinput VALUES (3,720,480,4,3,1,1,'ntsc');",
294 "INSERT INTO dvdinput VALUES (4,720,480,4,3,1,0,'ntsc');",
295 "INSERT INTO dvdinput VALUES (5,720,576,16,9,3,1,'pal');",
296 "INSERT INTO dvdinput VALUES (6,720,576,16,9,3,0,'pal');",
297 "INSERT INTO dvdinput VALUES (7,720,576,4,3,3,1,'pal');",
298 "INSERT INTO dvdinput VALUES (8,720,576,4,3,3,0,'pal');",
299 "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);",
300 "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);",
301 "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);",
302 "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);",
303 "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);",
304 "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);",
305 "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);",
306 "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);",
307 "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);",
308 "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);",
309 "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);",
310 "INSERT INTO videotypes VALUES (1,'txt','',1,0);",
311 "INSERT INTO videotypes VALUES (2,'log','',1,0);",
312 "INSERT INTO videotypes VALUES (3,'mpg','Internal',0,0);",
313 "INSERT INTO videotypes VALUES (4,'avi','',0,1);",
314 "INSERT INTO videotypes VALUES (5,'vob','Internal',0,0);",
315 "INSERT INTO videotypes VALUES (6,'mpeg','Internal',0,0);",
316 "INSERT INTO videotypes VALUES (8,'iso','Internal',0,0);",
317 "INSERT INTO videotypes VALUES (9,'img','Internal',0,0);",
318 "INSERT INTO videotypes VALUES (10,'mkv','Internal',0,0);",
319 "INSERT INTO videotypes VALUES (11,'mp4','Internal',0,0);",
320 "INSERT INTO videotypes VALUES (12,'m2ts','Internal',0,0);",
321 "INSERT INTO videotypes VALUES (13,'evo','Internal',0,0);",
322 "INSERT INTO videotypes VALUES (14,'divx','Internal',0,0);",
323 "INSERT INTO videotypes VALUES (15,'mov','Internal',0,0);",
324 "INSERT INTO videotypes VALUES (16,'qt','Internal',0,0);",
325 "INSERT INTO videotypes VALUES (17,'wmv','Internal',0,0);",
326 "INSERT INTO videotypes VALUES (18,'3gp','Internal',0,0);",
327 "INSERT INTO videotypes VALUES (19,'asf','Internal',0,0);",
328 "INSERT INTO videotypes VALUES (20,'ogg','Internal',0,0);",
329 "INSERT INTO videotypes VALUES (21,'ogm','Internal',0,0);",
330 "INSERT INTO videotypes VALUES (22,'flv','Internal',0,0);",
331 "INSERT INTO videotypes VALUES (23,'ogv','Internal',0,0);",
332 "INSERT INTO videotypes VALUES (25,'nut','Internal',0,0);",
333 "INSERT INTO videotypes VALUES (26,'mxf','Internal',0,0);",
334 "INSERT INTO videotypes VALUES (27,'m4v','Internal',0,0);",
335 "INSERT INTO videotypes VALUES (28,'rm','Internal',0,0);",
336 "INSERT INTO videotypes VALUES (29,'ts','Internal',0,0);",
337 "INSERT INTO videotypes VALUES (30,'swf','Internal',0,0);",
338 "INSERT INTO videotypes VALUES (31,'f4v','Internal',0,0);",
339 "INSERT INTO videotypes VALUES (32,'nuv','Internal',0,0);",
340 nullptr
341 };
342 
343  QString dbver = "";
344  return performActualUpdate(updates, finalVideoDatabaseVersion, dbver,
346 }
347 
349 {
350  QString dbver = gCoreContext->GetSetting("mythvideo.DBSchemaVer");
351  if (dbver == finalVideoDatabaseVersion)
352  {
353  return true;
354  }
355 
356  QString olddbver = gCoreContext->GetSetting("VideoDBSchemaVer");
357  QString dvddbver = gCoreContext->GetSetting("DVDDBSchemaVer");
358  if (dbver.isEmpty() && olddbver.isEmpty() && dvddbver.isEmpty())
359  {
360  if (!InitializeVideoSchema())
361  return false;
362  dbver = gCoreContext->GetSetting("mythvideo.DBSchemaVer");
363  }
364 
365  if (dbver.isEmpty() || dbver.toInt() < minimumVideoDatabaseVersion.toInt())
366  {
367  LOG(VB_GENERAL, LOG_ERR,
368  "Unrecognized video database schema version. "
369  "Unable to upgrade database.");
370  LOG(VB_GENERAL, LOG_ERR, QString("mythvideo.DBSchemaVer: '%1', "
371  "VideoDBSchemaVer: '%2', DVDDBSchemaVer: '%3'").arg(dbver)
372  .arg(olddbver).arg(dvddbver));
373  return false;
374  }
375 
376  if (dbver == "1016")
377  {
378  const QString updates[] = {
379 // NOLINTNEXTLINE(bugprone-suspicious-missing-comma)
380 "ALTER TABLE dvdbookmark"
381 " MODIFY serialid varbinary(16) NOT NULL default '',"
382 " MODIFY name varbinary(32) default NULL;",
383 "ALTER TABLE dvdinput"
384 " MODIFY v_format varbinary(16) default NULL;",
385 "ALTER TABLE dvdtranscode"
386 " MODIFY name varbinary(128) NOT NULL,"
387 " MODIFY codec varbinary(128) NOT NULL,"
388 " MODIFY codec_param varbinary(128) default NULL,"
389 " MODIFY tc_param varbinary(128) default NULL;",
390 "ALTER TABLE filemarkup"
391 " MODIFY filename blob NOT NULL;",
392 "ALTER TABLE videocast"
393 " MODIFY cast varbinary(128) NOT NULL;",
394 "ALTER TABLE videocategory"
395 " MODIFY category varbinary(128) NOT NULL;",
396 "ALTER TABLE videocountry"
397 " MODIFY country varbinary(128) NOT NULL;",
398 "ALTER TABLE videogenre"
399 " MODIFY genre varbinary(128) NOT NULL;",
400 "ALTER TABLE videometadata"
401 " MODIFY title varbinary(128) NOT NULL,"
402 " MODIFY director varbinary(128) NOT NULL,"
403 " MODIFY plot blob,"
404 " MODIFY rating varbinary(128) NOT NULL,"
405 " MODIFY inetref varbinary(255) NOT NULL,"
406 " MODIFY filename blob NOT NULL,"
407 " MODIFY coverfile blob NOT NULL,"
408 " MODIFY playcommand varbinary(255) default NULL;",
409 "ALTER TABLE videotypes"
410 " MODIFY extension varbinary(128) NOT NULL,"
411 " MODIFY playcommand varbinary(255) NOT NULL;",
412 ""
413 };
414 
415  if (!performActualUpdate(updates, "1017", dbver,
417  return false;
418  }
419 
420 
421  if (dbver == "1017")
422  {
423  const QString updates[] = {
424 // NOLINTNEXTLINE(bugprone-suspicious-missing-comma)
425 "ALTER TABLE dvdbookmark"
426 " DEFAULT CHARACTER SET default,"
427 " MODIFY serialid varchar(16) CHARACTER SET utf8 NOT NULL default '',"
428 " MODIFY name varchar(32) CHARACTER SET utf8 default NULL;",
429 "ALTER TABLE dvdinput"
430 " DEFAULT CHARACTER SET default,"
431 " MODIFY v_format varchar(16) CHARACTER SET utf8 default NULL;",
432 "ALTER TABLE dvdtranscode"
433 " DEFAULT CHARACTER SET default,"
434 " MODIFY name varchar(128) CHARACTER SET utf8 NOT NULL,"
435 " MODIFY codec varchar(128) CHARACTER SET utf8 NOT NULL,"
436 " MODIFY codec_param varchar(128) CHARACTER SET utf8 default NULL,"
437 " MODIFY tc_param varchar(128) CHARACTER SET utf8 default NULL;",
438 "ALTER TABLE filemarkup"
439 " DEFAULT CHARACTER SET default,"
440 " MODIFY filename text CHARACTER SET utf8 NOT NULL;",
441 "ALTER TABLE videocast"
442 " DEFAULT CHARACTER SET default,"
443 " MODIFY cast varchar(128) CHARACTER SET utf8 NOT NULL;",
444 "ALTER TABLE videocategory"
445 " DEFAULT CHARACTER SET default,"
446 " MODIFY category varchar(128) CHARACTER SET utf8 NOT NULL;",
447 "ALTER TABLE videocountry"
448 " DEFAULT CHARACTER SET default,"
449 " MODIFY country varchar(128) CHARACTER SET utf8 NOT NULL;",
450 "ALTER TABLE videogenre"
451 " DEFAULT CHARACTER SET default,"
452 " MODIFY genre varchar(128) CHARACTER SET utf8 NOT NULL;",
453 "ALTER TABLE videometadata"
454 " DEFAULT CHARACTER SET default,"
455 " MODIFY title varchar(128) CHARACTER SET utf8 NOT NULL,"
456 " MODIFY director varchar(128) CHARACTER SET utf8 NOT NULL,"
457 " MODIFY plot text CHARACTER SET utf8,"
458 " MODIFY rating varchar(128) CHARACTER SET utf8 NOT NULL,"
459 " MODIFY inetref varchar(255) CHARACTER SET utf8 NOT NULL,"
460 " MODIFY filename text CHARACTER SET utf8 NOT NULL,"
461 " MODIFY coverfile text CHARACTER SET utf8 NOT NULL,"
462 " MODIFY playcommand varchar(255) CHARACTER SET utf8 default NULL;",
463 "ALTER TABLE videometadatacast"
464 " DEFAULT CHARACTER SET default;",
465 "ALTER TABLE videometadatacountry"
466 " DEFAULT CHARACTER SET default;",
467 "ALTER TABLE videometadatagenre"
468 " DEFAULT CHARACTER SET default;",
469 "ALTER TABLE videotypes"
470 " DEFAULT CHARACTER SET default,"
471 " MODIFY extension varchar(128) CHARACTER SET utf8 NOT NULL,"
472 " MODIFY playcommand varchar(255) CHARACTER SET utf8 NOT NULL;",
473 ""
474 };
475 
476  if (!performActualUpdate(updates, "1018", dbver,
478  return false;
479  }
480 
481  if (dbver == "1018")
482  {
483  QStringList updates;
484  updates += "DELETE FROM settings WHERE value="
485  "'MovieListCommandLine' AND data LIKE '%imdb%';";
486  updates += "DELETE FROM settings WHERE value="
487  "'MovieDataCommandLine' AND data LIKE '%imdb%';";
488  updates += "DELETE FROM settings WHERE value="
489  "'MoviePosterCommandLine' AND data LIKE '%imdb%';";
490  if (!performActualUpdate(updates, "1019", dbver,
492  return false;
493  }
494 
495  if (dbver == "1019")
496  {
497  QStringList updates(
498  "ALTER TABLE videometadata ADD `trailer` TEXT;");
499  if (!performActualUpdate(updates, "1020", dbver,
501  return false;
502  }
503 
504  if (dbver == "1020")
505  {
506  LOG(VB_GENERAL, LOG_NOTICE,
507  "Upgrading to MythVideo schema version 1021");
508 
509  AddFileType("mkv");
510  AddFileType("mp4");
511  AddFileType("m2ts");
512  AddFileType("evo");
513  AddFileType("divx");
514  AddFileType("mov");
515  AddFileType("qt");
516  AddFileType("wmv");
517  AddFileType("3gp");
518  AddFileType("asf");
519  AddFileType("ogg");
520  AddFileType("ogm");
521  AddFileType("flv");
522 
524  return false;
525 
526  dbver = "1021";
527  }
528 
529  if (dbver == "1021")
530  {
531  QStringList updates;
532  updates += "ALTER TABLE videometadata ADD host text CHARACTER SET utf8 NOT NULL;";
533 
534  if (!performActualUpdate(updates, "1022", dbver,
536  return false;
537  }
538 
539  if (dbver == "1022")
540  {
541  QStringList updates;
542  updates += "ALTER TABLE videometadata ADD `screenshot` TEXT;";
543  updates += "ALTER TABLE videometadata ADD `banner` TEXT;";
544  updates += "ALTER TABLE videometadata ADD `fanart` TEXT;";
545  if (!performActualUpdate(updates, "1023", dbver,
547  return false;
548  }
549 
550  if (dbver == "1023")
551  {
552  QStringList updates;
553  updates += "ALTER TABLE videometadata ADD `subtitle` TEXT "
554  "NOT NULL AFTER `title`;";
555  updates += "ALTER TABLE videometadata ADD `season` SMALLINT "
556  "UNSIGNED NOT NULL DEFAULT '0' AFTER `length`;";
557  updates += "ALTER TABLE videometadata ADD `episode` SMALLINT "
558  "UNSIGNED NOT NULL DEFAULT '0' AFTER `season`;";
559  if (!performActualUpdate(updates, "1024", dbver,
561  return false;
562  }
563 
564  if (dbver == "1024")
565  {
566  QStringList updates;
567  updates += "ALTER TABLE videometadata ADD watched BOOL "
568  "NOT NULL DEFAULT 0 AFTER browse;";
569  if (!performActualUpdate(updates, "1025", dbver,
571  return false;
572  }
573 
574  if (dbver == "1025")
575  {
576  QStringList updates;
577  updates += "ALTER TABLE videometadata ADD `insertdate` TIMESTAMP "
578  "NULL DEFAULT CURRENT_TIMESTAMP AFTER `fanart`;";
579  if (!performActualUpdate(updates, "1026", dbver,
581  return false;
582  }
583 
584  if (dbver == "1026")
585  {
586  QStringList updates;
587  updates += "DELETE FROM keybindings "
588  " WHERE action = 'DELETE' AND context = 'Video';";
589  if (!performActualUpdate(updates, "1027", dbver,
591  return false;
592  }
593 
594  if (dbver == "1027")
595  {
596  LOG(VB_GENERAL, LOG_NOTICE,
597  "Upgrading to MythVideo schema version 1028");
598  LOG(VB_GENERAL, LOG_INFO,
599  "Converting filenames in filemarkup table "
600  "from absolute to relative paths. This may take a long "
601  "time if you have a large number of MythVideo seektables.");
602 
603  bool ok = true;
604  MSqlQuery query(MSqlQuery::InitCon());
605  MSqlQuery update(MSqlQuery::InitCon());
606 
607  query.prepare("SELECT DISTINCT filename FROM filemarkup;");
608  update.prepare("UPDATE filemarkup SET filename = :RELPATH "
609  " WHERE filename = :FULLPATH;");
610  if (query.exec())
611  {
612  QString origPath;
613  QString relPath;
614  while (query.next())
615  {
616  origPath = query.value(0).toString();
617  if (origPath.startsWith("dvd:"))
618  continue;
619 
620  relPath = StorageGroup::GetRelativePathname(origPath);
621  if ((!relPath.isEmpty()) &&
622  (relPath != origPath))
623  {
624  update.bindValue(":RELPATH", relPath);
625  update.bindValue(":FULLPATH", origPath);
626  if (!update.exec())
627  {
628  LOG(VB_GENERAL, LOG_ERR,
629  QString("ERROR converting '%1' to '%2' in "
630  "filemarkup table.")
631  .arg(origPath).arg(relPath));
632  ok = false;
633  }
634  }
635  }
636  }
637  else
638  ok = false;
639 
640  if (!ok)
641  return false;
642 
644  return false;
645 
646  dbver = "1028";
647  }
648 
649  if (dbver == "1028")
650  {
651  QStringList updates;
652  updates += "ALTER TABLE videometadata ADD `releasedate` DATE "
653  "NOT NULL AFTER `year`;";
654  updates += "ALTER TABLE videometadata ADD `homepage` TEXT "
655  "NOT NULL AFTER `inetref`;";
656  if (!performActualUpdate(updates, "1029", dbver,
658  return false;
659  }
660 
661  if (dbver == "1029")
662  {
663  QStringList updates;
664  updates += "ALTER TABLE videometadata ADD `hash` VARCHAR(128) "
665  "NOT NULL AFTER `filename`;";
666  if (!performActualUpdate(updates, "1030", dbver,
668  return false;
669  }
670 
671  if (dbver == "1030")
672  {
673  UpdateHashes();
675  return false;
676 
677  dbver = "1031";
678  }
679 
680  if (dbver == "1031")
681  {
682  MSqlQuery query(MSqlQuery::InitCon());
683  query.prepare("SHOW INDEX FROM videometadata");
684 
685  if (!query.exec())
686  {
687  MythDB::DBError("Unable to retrieve current indices on "
688  "videometadata.", query);
689  }
690  else
691  {
692  while (query.next())
693  {
694  QString index_name = query.value(2).toString();
695 
696  if ("title_2" == index_name)
697  {
698  MSqlQuery update(MSqlQuery::InitCon());
699  update.prepare("ALTER TABLE videometadata "
700  " DROP INDEX title_2");
701 
702  if (!update.exec())
703  {
704  MythDB::DBError("Unable to drop duplicate index "
705  "on videometadata. Ignoring.",
706  update);
707  }
708  break;
709  }
710  }
711  }
712 
714  return false;
715 
716  dbver = "1032";
717  }
718 
719  if (dbver == "1032")
720  {
721  QStringList updates;
722  updates += "CREATE TEMPORARY TABLE bad_videometadatacast"
723  " AS SELECT * FROM videometadatacast;";
724  updates += "CREATE TEMPORARY TABLE bad_videometadatagenre"
725  " AS SELECT * FROM videometadatagenre;";
726  updates += "CREATE TEMPORARY TABLE bad_videometadatacountry"
727  " AS SELECT * FROM videometadatacountry;";
728  updates += "TRUNCATE TABLE videometadatacast;";
729  updates += "TRUNCATE TABLE videometadatagenre;";
730  updates += "TRUNCATE TABLE videometadatacountry;";
731  updates += "INSERT videometadatacast SELECT idvideo,idcast"
732  " FROM bad_videometadatacast GROUP BY idvideo,idcast;";
733  updates += "INSERT videometadatagenre SELECT idvideo,idgenre"
734  " FROM bad_videometadatagenre GROUP BY idvideo,idgenre;";
735  updates += "INSERT videometadatacountry SELECT idvideo,idcountry"
736  " FROM bad_videometadatacountry GROUP BY idvideo,idcountry;";
737  updates += "DROP TEMPORARY TABLE bad_videometadatacast;";
738  updates += "DROP TEMPORARY TABLE bad_videometadatagenre;";
739  updates += "DROP TEMPORARY TABLE bad_videometadatacountry;";
740  updates += "ALTER TABLE videometadatacast ADD UNIQUE INDEX (`idvideo`,`idcast`);";
741  updates += "ALTER TABLE videometadatagenre ADD UNIQUE INDEX (`idvideo`,`idgenre`);";
742  updates +="ALTER TABLE videometadatacountry ADD UNIQUE INDEX (`idvideo`,`idcountry`);";
743  if (!performActualUpdate(updates, "1033", dbver,
745  return false;
746 
747  dbver = "1033";
748  }
749 
750  if (dbver == "1033")
751  {
752  AddFileType("ogv");
753  AddFileType("BDMV");
754  AddFileType("nut");
755  AddFileType("mxf");
756  AddFileType("m4v");
757  AddFileType("rm");
758  AddFileType("ts");
759  AddFileType("swf");
760  AddFileType("f4v");
761  AddFileType("nuv");
762 
764  return false;
765 
766  dbver = "1034";
767  }
768 
769  if (dbver == "1034")
770  {
771  QStringList updates;
772  updates += "ALTER TABLE videometadata ADD `tagline` VARCHAR (255) "
773  "AFTER `subtitle`;";
774 
775  if (!performActualUpdate(updates, "1035", dbver,
777  return false;
778  }
779 
780  if (dbver == "1035")
781  {
782  QStringList updates;
783  updates += "ALTER TABLE videometadata ADD processed BOOL "
784  "NOT NULL DEFAULT 0 AFTER watched;";
785  if (!performActualUpdate(updates, "1036", dbver,
787  return false;
788  }
789 
790  if (dbver == "1036")
791  {
792  QStringList updates;
793  updates += "ALTER TABLE videometadata ADD `studio` VARCHAR( 128 ) "
794  "AFTER `director`;";
795  if (!performActualUpdate(updates, "1037", dbver,
797  return false;
798  }
799 
800  if (dbver == "1037")
801  {
802  QStringList updates;
803  updates += "DELETE FROM videotypes WHERE extension = 'VIDEO_TS';";
804  updates += "DELETE FROM videotypes WHERE extension = 'BDMV';";
805  if (!performActualUpdate(updates, "1038", dbver,
807  return false;
808  }
809 
810  return true;
811 }
bool next(void)
Wrap QSqlQuery::next() so we can display the query results.
Definition: mythdbcon.cpp:783
void bindValue(const QString &placeholder, const QVariant &val)
Add a single binding.
Definition: mythdbcon.cpp:864
QSqlQuery wrapper that fetches a DB connection from the connection pool.
Definition: mythdbcon.h:125
const QString MythVideoVersionName
static void UpdateHashes(void)
int size(void) const
Definition: mythdbcon.h:203
MythCoreContext * gCoreContext
This global variable contains the MythCoreContext instance for the app.
bool doUpgradeVideoDatabaseSchema(void)
const QString finalVideoDatabaseVersion
QVariant value(int i) const
Definition: mythdbcon.h:198
static bool performActualUpdate(const QStringList &updates, const QString &version, QString &dbver, const QString &field_name)
QString GetSetting(const QString &key, const QString &defaultval="")
static bool UpdateDBVersionNumber(const QString &field_name, const QString &newnumber)
static void AddFileType(const QString &extension, const QString &playCommand=QString("Internal"), bool ignored=false, bool useDefault=false)
static MSqlQueryInfo InitCon(ConnectionReuse _reuse=kNormalConnection)
Only use this in combination with MSqlQuery constructor.
Definition: mythdbcon.cpp:535
bool prepare(const QString &query)
QSqlQuery::prepare() is not thread safe in Qt <= 3.3.2.
Definition: mythdbcon.cpp:808
#define LOG(_MASK_, _LEVEL_, _STRING_)
Definition: mythlogging.h:41
static bool InitializeVideoSchema(void)
QString generate_file_url(const QString &storage_group, const QString &host, const QString &path)
Definition: videoutils.h:65
static QString GetRelativePathname(const QString &filename)
Returns the relative pathname of a file by comparing the filename against all Storage Group directori...
const QString minimumVideoDatabaseVersion
bool exec(void)
Wrap QSqlQuery::exec() so we can display SQL.
Definition: mythdbcon.cpp:603
static void DBError(const QString &where, const MSqlQuery &query)
Definition: mythdb.cpp:179
QString FileHash(const QString &filename)
static QString GetFileHash(const QString &url)
Definition: remotefile.cpp:552