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