MythTV  master
mythplugins/mythmusic/mythmusic/dbcheck.cpp
Go to the documentation of this file.
1 #include <QString>
2 #include <QDir>
3 #include <QSqlError>
4 
5 #include <iostream>
6 using namespace std;
7 
8 #include <musicmetadata.h>
9 #include <mythcontext.h>
10 #include <mythtv/mythdb.h>
11 #include <mythtv/schemawizard.h>
12 
13 #include "dbcheck.h"
14 
15 const QString currentDatabaseVersion = "1024";
16 
17 static bool doUpgradeMusicDatabaseSchema(QString &dbver);
18 
19 static bool UpdateDBVersionNumber(const QString &newnumber)
20 {
21 
22  if (!gCoreContext->SaveSettingOnHost("MusicDBSchemaVer",newnumber,nullptr))
23  {
24  LOG(VB_GENERAL, LOG_ERR,
25  QString("DB Error (Setting new DB version number): %1\n")
26  .arg(newnumber));
27 
28  return false;
29  }
30 
31  return true;
32 }
33 
34 static bool performActualUpdate(const QString updates[], const QString& version,
35  QString &dbver)
36 {
38 
39  LOG(VB_GENERAL, LOG_NOTICE,
40  QString("Upgrading to MythMusic schema version ") + version);
41 
42  int counter = 0;
43  QString thequery = updates[counter];
44 
45  while (!thequery.isEmpty())
46  {
47  if (!query.exec(thequery))
48  {
49  QString msg =
50  QString("DB Error (Performing database upgrade): \n"
51  "Query was: %1 \nError was: %2 \nnew version: %3")
52  .arg(thequery)
53  .arg(MythDB::DBErrorMessage(query.lastError()))
54  .arg(version);
55  LOG(VB_GENERAL, LOG_ERR, msg);
56  return false;
57  }
58 
59  counter++;
60  thequery = updates[counter];
61  }
62 
64  return false;
65 
66  dbver = version;
67  return true;
68 }
69 
71 {
72 #ifdef IGNORE_SCHEMA_VER_MISMATCH
73  return true;
74 #endif
75  SchemaUpgradeWizard *schema_wizard = nullptr;
76 
77  // Suppress DB messages and turn of the settings cache,
78  // These are likely to confuse the users and the code, respectively.
79  GetMythDB()->SetSuppressDBMessages(true);
81 
82  // Get the schema upgrade lock
84  bool locked = DBUtil::TryLockSchema(query, 1);
85  for (uint i = 0; i < 2*60 && !locked; i++)
86  {
87  LOG(VB_GENERAL, LOG_INFO, "Waiting for database schema upgrade lock");
88  locked = DBUtil::TryLockSchema(query, 1);
89  if (locked)
90  LOG(VB_GENERAL, LOG_INFO, "Got schema upgrade lock");
91  }
92  if (!locked)
93  {
94  LOG(VB_GENERAL, LOG_INFO, "Failed to get schema upgrade lock");
95  goto upgrade_error_exit;
96  }
97 
98  schema_wizard = SchemaUpgradeWizard::Get(
99  "MusicDBSchemaVer", "MythMusic", currentDatabaseVersion);
100 
101  if (schema_wizard->Compare() == 0) // DB schema is what we need it to be..
102  goto upgrade_ok_exit;
103 
104  if (schema_wizard->m_DBver.isEmpty())
105  {
106  // We need to create a database from scratch
107  if (doUpgradeMusicDatabaseSchema(schema_wizard->m_DBver))
108  goto upgrade_ok_exit;
109  else
110  goto upgrade_error_exit;
111  }
112 
113  // Pop up messages, questions, warnings, et c.
114  switch (schema_wizard->PromptForUpgrade("Music", true, false))
115  {
117  goto upgrade_ok_exit;
118  case MYTH_SCHEMA_ERROR:
119  case MYTH_SCHEMA_EXIT:
120  goto upgrade_error_exit;
121  case MYTH_SCHEMA_UPGRADE:
122  break;
123  }
124 
125  if (!doUpgradeMusicDatabaseSchema(schema_wizard->m_DBver))
126  {
127  LOG(VB_GENERAL, LOG_ERR, "Database schema upgrade failed.");
128  goto upgrade_error_exit;
129  }
130 
131  LOG(VB_GENERAL, LOG_INFO, "MythMusic database schema upgrade complete.");
132 
133  // On any exit we want to re-enable the DB messages so errors
134  // are reported and we want to make sure the setting cache is
135  // enabled for good performance and we must unlock the schema
136  // lock. We use gotos with labels so it's impossible to miss
137  // these steps.
138  upgrade_ok_exit:
139  GetMythDB()->SetSuppressDBMessages(false);
141  if (locked)
142  DBUtil::UnlockSchema(query);
143  return true;
144 
145  upgrade_error_exit:
146  GetMythDB()->SetSuppressDBMessages(false);
148  if (locked)
149  DBUtil::UnlockSchema(query);
150  return false;
151 }
152 
153 
154 static bool doUpgradeMusicDatabaseSchema(QString &dbver)
155 {
156  if (dbver.isEmpty())
157  {
158  LOG(VB_GENERAL, LOG_NOTICE,
159  "Inserting MythMusic initial database information.");
160 
161  const QString updates[] =
162  {
163  "CREATE TABLE IF NOT EXISTS musicmetadata ("
164  " intid INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,"
165  " artist VARCHAR(128) NOT NULL,"
166  " album VARCHAR(128) NOT NULL,"
167  " title VARCHAR(128) NOT NULL,"
168  " genre VARCHAR(128) NOT NULL,"
169  " year INT UNSIGNED NOT NULL,"
170  " tracknum INT UNSIGNED NOT NULL,"
171  " length INT UNSIGNED NOT NULL,"
172  " filename TEXT NOT NULL,"
173  " rating INT UNSIGNED NOT NULL DEFAULT 5,"
174  " lastplay TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP "
175  " ON UPDATE CURRENT_TIMESTAMP,"
176  " playcount INT UNSIGNED NOT NULL DEFAULT 0,"
177  " INDEX (artist),"
178  " INDEX (album),"
179  " INDEX (title),"
180  " INDEX (genre)"
181  ");",
182  "CREATE TABLE IF NOT EXISTS musicplaylist ("
183  " playlistid INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,"
184  " name VARCHAR(128) NOT NULL,"
185  " hostname VARCHAR(255),"
186  " songlist TEXT NOT NULL"
187  ");",
188  ""
189  };
190 
191  if (!performActualUpdate(updates, "1000", dbver))
192  return false;
193  }
194 
195  if (dbver == "1000")
196  {
197  QString startdir = gCoreContext->GetSetting("MusicLocation");
198  startdir = QDir::cleanPath(startdir);
199  if (!startdir.endsWith("/"))
200  startdir += "/";
201 
202  MSqlQuery query(MSqlQuery::InitCon());
203  // urls as filenames are NOT officially supported yet
204  if (query.exec("SELECT filename, intid FROM musicmetadata WHERE "
205  "filename NOT LIKE ('%://%');"))
206  {
207  int i = 0;
208  QString intid, name, newname;
209 
210  MSqlQuery modify(MSqlQuery::InitCon());
211  while (query.next())
212  {
213  name = query.value(0).toString();
214  newname = name;
215  intid = query.value(1).toString();
216 
217  if (newname.startsWith(startdir))
218  {
219  newname.remove(0, startdir.length());
220  if (modify.exec(QString("UPDATE musicmetadata SET "
221  "filename = \"%1\" "
222  "WHERE filename = \"%2\" AND intid = %3;")
223  .arg(newname).arg(name).arg(intid)))
224  i += modify.numRowsAffected();
225  }
226  }
227  LOG(VB_GENERAL, LOG_NOTICE,
228  QString("Modified %1 entries for db schema 1001").arg(i));
229  }
230 
231  const QString updates[] = {""};
232 
233  if (!performActualUpdate(updates, "1001", dbver))
234  return false;
235  }
236 
237  if (dbver == "1001")
238  {
239  const QString updates[] =
240  {
241  "ALTER TABLE musicmetadata ADD mythdigest VARCHAR(255);",
242  "ALTER TABLE musicmetadata ADD size BIGINT UNSIGNED;",
243  "ALTER TABLE musicmetadata ADD date_added DATETIME;",
244  "ALTER TABLE musicmetadata ADD date_modified DATETIME;",
245  "ALTER TABLE musicmetadata ADD format VARCHAR(4);",
246  "ALTER TABLE musicmetadata ADD description VARCHAR(255);",
247  "ALTER TABLE musicmetadata ADD comment VARCHAR(255);",
248  "ALTER TABLE musicmetadata ADD compilation TINYINT DEFAULT 0;",
249  "ALTER TABLE musicmetadata ADD composer VARCHAR(255);",
250  "ALTER TABLE musicmetadata ADD disc_count SMALLINT UNSIGNED DEFAULT 0;",
251  "ALTER TABLE musicmetadata ADD disc_number SMALLINT UNSIGNED DEFAULT 0;",
252  "ALTER TABLE musicmetadata ADD track_count SMALLINT UNSIGNED DEFAULT 0;",
253  "ALTER TABLE musicmetadata ADD start_time INT UNSIGNED DEFAULT 0;",
254  "ALTER TABLE musicmetadata ADD stop_time INT UNSIGNED;",
255  "ALTER TABLE musicmetadata ADD eq_preset VARCHAR(255);",
256  "ALTER TABLE musicmetadata ADD relative_volume TINYINT DEFAULT 0;",
257  "ALTER TABLE musicmetadata ADD sample_rate INT UNSIGNED;",
258  "ALTER TABLE musicmetadata ADD bpm SMALLINT UNSIGNED;",
259  "ALTER TABLE musicmetadata ADD INDEX (mythdigest);",
260  ""
261  };
262 
263  if (!performActualUpdate(updates, "1002", dbver))
264  return false;
265  }
266 
267  if (dbver == "1002")
268  {
269  LOG(VB_GENERAL, LOG_NOTICE,
270  "Updating music metadata to be UTF-8 in the database");
271 
272  MSqlQuery query(MSqlQuery::InitCon());
273  query.prepare("SELECT intid, artist, album, title, genre, "
274  "filename FROM musicmetadata ORDER BY intid;");
275 
276  if (query.exec() && query.isActive() && query.size() > 0)
277  {
278  while (query.next())
279  {
280  int id = query.value(0).toInt();
281  QString artist = query.value(1).toString();
282  QString album = query.value(2).toString();
283  QString title = query.value(3).toString();
284  QString genre = query.value(4).toString();
285  QString filename = query.value(5).toString();
286 
287  MSqlQuery subquery(MSqlQuery::InitCon());
288  subquery.prepare("UPDATE musicmetadata SET "
289  "artist = :ARTIST, album = :ALBUM, "
290  "title = :TITLE, genre = :GENRE, "
291  "filename = :FILENAME "
292  "WHERE intid = :ID;");
293  subquery.bindValue(":ARTIST", QString(artist.toUtf8()));
294  subquery.bindValue(":ALBUM", QString(album.toUtf8()));
295  subquery.bindValue(":TITLE", QString(title.toUtf8()));
296  subquery.bindValue(":GENRE", QString(genre.toUtf8()));
297  subquery.bindValue(":FILENAME", QString(filename.toUtf8()));
298  subquery.bindValue(":ID", id);
299 
300  if (!subquery.exec() || !subquery.isActive())
301  MythDB::DBError("music utf8 update", subquery);
302  }
303  }
304 
305  query.prepare("SELECT playlistid, name FROM musicplaylist "
306  "ORDER BY playlistid;");
307 
308  if (query.exec() && query.isActive() && query.size() > 0)
309  {
310  while (query.next())
311  {
312  int id = query.value(0).toInt();
313  QString name = query.value(1).toString();
314 
315  MSqlQuery subquery(MSqlQuery::InitCon());
316  subquery.prepare("UPDATE musicplaylist SET "
317  "name = :NAME WHERE playlistid = :ID ;");
318  subquery.bindValue(":NAME", QString(name.toUtf8()));
319  subquery.bindValue(":ID", id);
320 
321  if (!subquery.exec() || !subquery.isActive())
322  MythDB::DBError("music playlist utf8 update", subquery);
323  }
324  }
325 
326  LOG(VB_GENERAL, LOG_NOTICE, "Done updating music metadata to UTF-8");
327 
328  const QString updates[] = {""};
329 
330  if (!performActualUpdate(updates, "1003", dbver))
331  return false;
332  }
333 
334  if (dbver == "1003")
335  {
336  const QString updates[] =
337  {
338  "DROP TABLE IF EXISTS smartplaylistcategory;",
339  "CREATE TABLE smartplaylistcategory ("
340  " categoryid INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,"
341  " name VARCHAR(128) NOT NULL,"
342  " INDEX (name)"
343  ");",
344 
345  // NOLINTNEXTLINE(bugprone-suspicious-missing-comma)
346  "INSERT INTO smartplaylistcategory SET categoryid = 1, "
347  " name = \"Decades\";",
348  "INSERT INTO smartplaylistcategory SET categoryid = 2, "
349  " name = \"Favourite Tracks\";",
350  "INSERT INTO smartplaylistcategory SET categoryid = 3, "
351  " name = \"New Tracks\";",
352 
353  "DROP TABLE IF EXISTS smartplaylist;",
354  "CREATE TABLE smartplaylist ("
355  " smartplaylistid INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,"
356  " name VARCHAR(128) NOT NULL,"
357  " categoryid INT UNSIGNED NOT NULL,"
358  " matchtype SET('All', 'Any') NOT NULL DEFAULT 'All',"
359  " orderby VARCHAR(128) NOT NULL DEFAULT '',"
360  " limitto INT UNSIGNED NOT NULL DEFAULT 0,"
361  " INDEX (name),"
362  " INDEX (categoryid)"
363  ");",
364  "DROP TABLE IF EXISTS smartplaylistitem;",
365  "CREATE TABLE smartplaylistitem ("
366  " smartplaylistitemid INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,"
367  " smartplaylistid INT UNSIGNED NOT NULL,"
368  " field VARCHAR(50) NOT NULL,"
369  " operator VARCHAR(20) NOT NULL,"
370  " value1 VARCHAR(255) NOT NULL,"
371  " value2 VARCHAR(255) NOT NULL,"
372  " INDEX (smartplaylistid)"
373  ");",
374  "INSERT INTO smartplaylist SET smartplaylistid = 1, name = \"1960's\", "
375  " categoryid = 1, matchtype = \"All\", orderby = \"Artist (A)\","
376  " limitto = 0;",
377  "INSERT INTO smartplaylistitem SET smartplaylistid = 1, field = \"Year\","
378  " operator = \"is between\", value1 = \"1960\", value2 = \"1969\";",
379 
380  "INSERT INTO smartplaylist SET smartplaylistid = 2, name = \"1970's\", "
381  " categoryid = 1, matchtype = \"All\", orderby = \"Artist (A)\","
382  " limitto = 0;",
383  "INSERT INTO smartplaylistitem SET smartplaylistid = 2, field = \"Year\","
384  " operator = \"is between\", value1 = \"1970\", value2 = \"1979\";",
385 
386  "INSERT INTO smartplaylist SET smartplaylistid = 3, name = \"1980's\", "
387  " categoryid = 1, matchtype = \"All\", orderby = \"Artist (A)\","
388  " limitto = 0;",
389  "INSERT INTO smartplaylistitem SET smartplaylistid = 3, field = \"Year\","
390  " operator = \"is between\", value1 = \"1980\", value2 = \"1989\";",
391 
392  "INSERT INTO smartplaylist SET smartplaylistid = 4, name = \"1990's\", "
393  " categoryid = 1, matchtype = \"All\", orderby = \"Artist (A)\","
394  " limitto = 0;",
395  "INSERT INTO smartplaylistitem SET smartplaylistid = 4, field = \"Year\","
396  " operator = \"is between\", value1 = \"1990\", value2 = \"1999\";",
397 
398  "INSERT INTO smartplaylist SET smartplaylistid = 5, name = \"2000's\", "
399  " categoryid = 1, matchtype = \"All\", orderby = \"Artist (A)\","
400  " limitto = 0;",
401  "INSERT INTO smartplaylistitem SET smartplaylistid = 5, field = \"Year\","
402  " operator = \"is between\", value1 = \"2000\", value2 = \"2009\";",
403 
404  "INSERT INTO smartplaylist SET smartplaylistid = 6, name = \"Favorite Tracks\", "
405  " categoryid = 2, matchtype = \"All\","
406  " orderby = \"Artist (A), Album (A)\", limitto = 0;",
407  "INSERT INTO smartplaylistitem SET smartplaylistid = 6, field = \"Rating\","
408  " operator = \"is greater than\", value1 = \"7\", value2 = \"0\";",
409 
410  "INSERT INTO smartplaylist SET smartplaylistid = 7, name = \"100 Most Played Tracks\", "
411  " categoryid = 2, matchtype = \"All\", orderby = \"Play Count (D)\","
412  " limitto = 100;",
413  "INSERT INTO smartplaylistitem SET smartplaylistid = 7, field = \"Play Count\","
414  " operator = \"is greater than\", value1 = \"0\", value2 = \"0\";",
415 
416  "INSERT INTO smartplaylist SET smartplaylistid = 8, name = \"Never Played Tracks\", "
417  " categoryid = 3, matchtype = \"All\", orderby = \"Artist (A), Album (A)\","
418  " limitto = 0;",
419  "INSERT INTO smartplaylistitem SET smartplaylistid = 8, field = \"Play Count\","
420  " operator = \"is equal to\", value1 = \"0\", value2 = \"0\";",
421 
422  ""
423  };
424 
425  if (!performActualUpdate(updates, "1004", dbver))
426  return false;
427  }
428 
429  if (dbver == "1004")
430  {
431  const QString updates[] =
432  {
433  "ALTER TABLE musicmetadata ADD compilation_artist VARCHAR(128) NOT NULL AFTER artist;",
434  "ALTER TABLE musicmetadata ADD INDEX (compilation_artist);",
435  ""
436  };
437 
438  if (!performActualUpdate(updates, "1005", dbver))
439  return false;
440  }
441 
442 
443  if (dbver == "1005")
444  {
445  const QString updates[] =
446  {
447  "CREATE TABLE music_albums ("
448  " album_id int(11) unsigned NOT NULL auto_increment PRIMARY KEY,"
449  " artist_id int(11) unsigned NOT NULL default '0',"
450  " album_name varchar(255) NOT NULL default '',"
451  " year smallint(6) NOT NULL default '0',"
452  " compilation tinyint(1) unsigned NOT NULL default '0',"
453  " INDEX idx_album_name(album_name)"
454  ");",
455  "CREATE TABLE music_artists ("
456  " artist_id int(11) unsigned NOT NULL auto_increment PRIMARY KEY,"
457  " artist_name varchar(255) NOT NULL default '',"
458  " INDEX idx_artist_name(artist_name)"
459  ");",
460  "CREATE TABLE music_genres ("
461  " genre_id int(11) unsigned NOT NULL auto_increment PRIMARY KEY,"
462  " genre varchar(25) NOT NULL default '',"
463  " INDEX idx_genre(genre)"
464  ");",
465  "CREATE TABLE music_playlists ("
466  " playlist_id int(11) unsigned NOT NULL auto_increment PRIMARY KEY,"
467  " playlist_name varchar(255) NOT NULL default '',"
468  " playlist_songs text NOT NULL,"
469  " last_accessed timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP "
470  " ON UPDATE CURRENT_TIMESTAMP,"
471  " length int(11) unsigned NOT NULL default '0',"
472  " songcount smallint(8) unsigned NOT NULL default '0',"
473  " hostname VARCHAR(255) NOT NULL default ''"
474  ");",
475  "CREATE TABLE music_songs ("
476  " song_id int(11) unsigned NOT NULL auto_increment PRIMARY KEY,"
477  " filename text NOT NULL,"
478  " name varchar(255) NOT NULL default '',"
479  " track smallint(6) unsigned NOT NULL default '0',"
480  " artist_id int(11) unsigned NOT NULL default '0',"
481  " album_id int(11) unsigned NOT NULL default '0',"
482  " genre_id int(11) unsigned NOT NULL default '0',"
483  " year smallint(6) NOT NULL default '0',"
484  " length int(11) unsigned NOT NULL default '0',"
485  " numplays int(11) unsigned NOT NULL default '0',"
486  " rating tinyint(4) unsigned NOT NULL default '0',"
487  " lastplay timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP "
488  " ON UPDATE CURRENT_TIMESTAMP,"
489  " date_entered datetime default NULL,"
490  " date_modified datetime default NULL,"
491  " format varchar(4) NOT NULL default '0',"
492  " mythdigest VARCHAR(255),"
493  " size BIGINT(20) unsigned,"
494  " description VARCHAR(255),"
495  " comment VARCHAR(255),"
496  " disc_count SMALLINT(5) UNSIGNED DEFAULT '0',"
497  " disc_number SMALLINT(5) UNSIGNED DEFAULT '0',"
498  " track_count SMALLINT(5) UNSIGNED DEFAULT '0',"
499  " start_time INT(10) UNSIGNED DEFAULT '0',"
500  " stop_time INT(10) UNSIGNED,"
501  " eq_preset VARCHAR(255),"
502  " relative_volume TINYINT DEFAULT '0',"
503  " sample_rate INT(10) UNSIGNED DEFAULT '0',"
504  " bitrate INT(10) UNSIGNED DEFAULT '0',"
505  " bpm SMALLINT(5) UNSIGNED,"
506  " INDEX idx_name(name),"
507  " INDEX idx_mythdigest(mythdigest)"
508  ");",
509  "CREATE TABLE music_stats ("
510  " num_artists smallint(5) unsigned NOT NULL default '0',"
511  " num_albums smallint(5) unsigned NOT NULL default '0',"
512  " num_songs mediumint(8) unsigned NOT NULL default '0',"
513  " num_genres tinyint(3) unsigned NOT NULL default '0',"
514  " total_time varchar(12) NOT NULL default '0',"
515  " total_size varchar(10) NOT NULL default '0'"
516  ");",
517  "RENAME TABLE smartplaylist TO music_smartplaylists;",
518  "RENAME TABLE smartplaylistitem TO music_smartplaylist_items;",
519  "RENAME TABLE smartplaylistcategory TO music_smartplaylist_categories;",
520  // Run necessary SQL to migrate the table structure
521  // NOLINTNEXTLINE(bugprone-suspicious-missing-comma)
522  "CREATE TEMPORARY TABLE tmp_artists"
523  " SELECT DISTINCT artist FROM musicmetadata;",
524  "INSERT INTO tmp_artists"
525  " SELECT DISTINCT compilation_artist"
526  " FROM musicmetadata"
527  " WHERE compilation_artist<>artist;",
528  "INSERT INTO music_artists (artist_name) SELECT DISTINCT artist FROM tmp_artists;",
529  "INSERT INTO music_albums (artist_id, album_name, year, compilation) "
530  " SELECT artist_id, album, ROUND(AVG(year)) AS year, IF(SUM(compilation),1,0) AS compilation"
531  " FROM musicmetadata"
532  " LEFT JOIN music_artists ON compilation_artist=artist_name"
533  " GROUP BY artist_id, album;",
534  "INSERT INTO music_genres (genre) SELECT DISTINCT genre FROM musicmetadata;",
535  "INSERT INTO music_songs "
536  " (song_id, artist_id, album_id, genre_id, year, lastplay,"
537  " date_entered, date_modified, name, track, length, size, numplays,"
538  " rating, filename)"
539  " SELECT intid, ma.artist_id, mb.album_id, mg.genre_id, mmd.year, lastplay,"
540  " date_added, date_modified, title, tracknum, length, IFNULL(size,0), playcount,"
541  " rating, filename"
542  " FROM musicmetadata AS mmd"
543  " LEFT JOIN music_artists AS ma ON mmd.artist=ma.artist_name"
544  " LEFT JOIN music_artists AS mc ON mmd.compilation_artist=mc.artist_name"
545  " LEFT JOIN music_albums AS mb ON mmd.album=mb.album_name AND mc.artist_id=mb.artist_id"
546  " LEFT JOIN music_genres AS mg ON mmd.genre=mg.genre;",
547  "INSERT INTO music_playlists"
548  " (playlist_id,playlist_name,playlist_songs,hostname)"
549  " SELECT playlistid, name, songlist, hostname"
550  " FROM musicplaylist;",
551  // Set all real playlists to be global by killing the hostname
552  "UPDATE music_playlists"
553  " SET hostname=''"
554  " WHERE playlist_name<>'default_playlist_storage'"
555  " AND playlist_name<>'backup_playlist_storage';",
556  ""
557  };
558 
559  if (!performActualUpdate(updates, "1006", dbver))
560  return false;
561  }
562 
563  if (dbver == "1006")
564  {
565  const QString updates[] =
566  {
567  "ALTER TABLE music_genres MODIFY genre VARCHAR(255) NOT NULL default '';",
568  ""
569  };
570 
571  if (!performActualUpdate(updates, "1007", dbver))
572  return false;
573  }
574 
575  if (dbver == "1007")
576  {
577  const QString updates[] =
578  {
579  "ALTER TABLE music_songs MODIFY lastplay DATETIME DEFAULT NULL;",
580  // NOLINTNEXTLINE(bugprone-suspicious-missing-comma)
581  "CREATE TABLE music_directories (directory_id int(20) NOT NULL AUTO_INCREMENT "
582  "PRIMARY KEY, path TEXT NOT NULL, "
583  "parent_id INT(20) NOT NULL DEFAULT '0') ;",
584  "INSERT IGNORE INTO music_directories (path) SELECT DISTINCT"
585  " SUBSTRING(filename FROM 1 FOR INSTR(filename, "
586  "SUBSTRING_INDEX(filename, '/', -1))-2) FROM music_songs;",
587  "CREATE TEMPORARY TABLE tmp_songs SELECT music_songs.*, directory_id "
588  "FROM music_songs, music_directories WHERE "
589  "music_directories.path=SUBSTRING(filename FROM 1 FOR "
590  "INSTR(filename, SUBSTRING_INDEX(filename, '/', -1))-2);",
591  "UPDATE tmp_songs SET filename=SUBSTRING_INDEX(filename, '/', -1);",
592  "DELETE FROM music_songs;",
593  "ALTER TABLE music_songs ADD COLUMN directory_id int(20) NOT NULL DEFAULT '0';",
594  "INSERT INTO music_songs SELECT * FROM tmp_songs;",
595  "ALTER TABLE music_songs ADD INDEX (directory_id);",
596  ""
597  };
598 
599  if (!performActualUpdate(updates, "1008", dbver))
600  return false;
601  }
602 
603  if (dbver == "1008")
604  {
605  const QString updates[] =
606  {
607  "CREATE TABLE music_albumart (albumart_id int(20) NOT NULL AUTO_INCREMENT "
608  "PRIMARY KEY, filename VARCHAR(255) NOT NULL DEFAULT '', directory_id INT(20) "
609  "NOT NULL DEFAULT '0');",
610  ""
611  };
612 
613  if (!performActualUpdate(updates, "1009", dbver))
614  return false;
615  }
616 
617  if (dbver == "1009")
618  {
619  const QString updates[] =
620  {
621  "ALTER TABLE music_albumart ADD COLUMN imagetype tinyint(3) NOT NULL DEFAULT '0';",
622  ""
623  };
624 
625  if (!performActualUpdate(updates, "1010", dbver))
626  return false;
627 
628  // scan though the music_albumart table and make a guess at what
629  // each image represents from the filename
630 
631  LOG(VB_GENERAL, LOG_NOTICE, "Updating music_albumart image types");
632 
633  MSqlQuery query(MSqlQuery::InitCon());
634  query.prepare("SELECT albumart_id, filename, directory_id, imagetype FROM music_albumart;");
635 
636  if (query.exec())
637  {
638  while (query.next())
639  {
640  int id = query.value(0).toInt();
641  QString filename = query.value(1).toString();
642  int directoryID = query.value(2).toInt();
643  MSqlQuery subquery(MSqlQuery::InitCon());
644 
645  // guess the type from the filename
647 
648  // if type is still unknown check to see how many images are available in the dir
649  // and assume that if this is the only image it must be the front cover
650  if (type == IT_UNKNOWN)
651  {
652  subquery.prepare("SELECT count(directory_id) FROM music_albumart "
653  "WHERE directory_id = :DIR;");
654  subquery.bindValue(":DIR", directoryID);
655  if (!subquery.exec() || !subquery.isActive())
656  MythDB::DBError("album art image count", subquery);
657  subquery.first();
658  if (query.value(0).toInt() == 1)
660  }
661 
662  // finally set the type in the music_albumart table
663  subquery.prepare("UPDATE music_albumart "
664  "SET imagetype = :TYPE "
665  "WHERE albumart_id = :ID;");
666  subquery.bindValue(":TYPE", type);
667  subquery.bindValue(":ID", id);
668  if (!subquery.exec() || !subquery.isActive())
669  MythDB::DBError("album art image type update", subquery);
670  }
671  }
672  }
673 
674  if (dbver == "1010")
675  {
676  const QString updates[] = {"", ""};
677 
678  // update the VisualMode setting to the new format
679  QString setting = gCoreContext->GetSetting("VisualMode");
680  setting = setting.simplified();
681  setting = setting.replace(' ', ";");
682  gCoreContext->SaveSetting("VisualMode", setting);
683 
684  if (!performActualUpdate(updates, "1011", dbver))
685  return false;
686 
687  }
688 
689  if (dbver == "1011")
690  {
691  const QString updates[] =
692  {
693  "ALTER TABLE music_albumart ADD COLUMN song_id int(11) NOT NULL DEFAULT '0', "
694  " ADD COLUMN embedded TINYINT(1) NOT NULL DEFAULT '0';",
695  ""
696  };
697 
698  if (!performActualUpdate(updates, "1012", dbver))
699  return false;
700 
701  }
702 
703  if (dbver == "1012")
704  {
705  const QString updates[] =
706  {
707  "ALTER TABLE music_songs ADD INDEX album_id (album_id);",
708  "ALTER TABLE music_songs ADD INDEX genre_id (genre_id);",
709  "ALTER TABLE music_songs ADD INDEX artist_id (artist_id);",
710  ""
711  };
712 
713  if (!performActualUpdate(updates, "1013", dbver))
714  return false;
715 
716  }
717 
718  if (dbver == "1013")
719  {
720  const QString updates[] =
721  {
722  "DROP TABLE musicmetadata;",
723  "DROP TABLE musicplaylist;",
724  ""
725  };
726 
727  if (!performActualUpdate(updates, "1014", dbver))
728  return false;
729  }
730 
731  if (dbver == "1014")
732  {
733  const QString updates[] =
734  {
735  QString("ALTER DATABASE %1 DEFAULT CHARACTER SET latin1;")
737  // NOLINTNEXTLINE(bugprone-suspicious-missing-comma)
738  "ALTER TABLE music_albumart"
739  " MODIFY filename varbinary(255) NOT NULL default '';",
740  "ALTER TABLE music_albums"
741  " MODIFY album_name varbinary(255) NOT NULL default '';",
742  "ALTER TABLE music_artists"
743  " MODIFY artist_name varbinary(255) NOT NULL default '';",
744  "ALTER TABLE music_directories"
745  " MODIFY path blob NOT NULL;",
746  "ALTER TABLE music_genres"
747  " MODIFY genre varbinary(255) NOT NULL default '';",
748  "ALTER TABLE music_playlists"
749  " MODIFY playlist_name varbinary(255) NOT NULL default '',"
750  " MODIFY playlist_songs blob NOT NULL,"
751  " MODIFY hostname varbinary(64) NOT NULL default '';",
752  "ALTER TABLE music_smartplaylist_categories"
753  " MODIFY name varbinary(128) NOT NULL;",
754  "ALTER TABLE music_smartplaylist_items"
755  " MODIFY field varbinary(50) NOT NULL,"
756  " MODIFY operator varbinary(20) NOT NULL,"
757  " MODIFY value1 varbinary(255) NOT NULL,"
758  " MODIFY value2 varbinary(255) NOT NULL;",
759  "ALTER TABLE music_smartplaylists"
760  " MODIFY name varbinary(128) NOT NULL,"
761  " MODIFY orderby varbinary(128) NOT NULL default '';",
762  "ALTER TABLE music_songs"
763  " MODIFY filename blob NOT NULL,"
764  " MODIFY name varbinary(255) NOT NULL default '',"
765  " MODIFY format varbinary(4) NOT NULL default '0',"
766  " MODIFY mythdigest varbinary(255) default NULL,"
767  " MODIFY description varbinary(255) default NULL,"
768  " MODIFY comment varbinary(255) default NULL,"
769  " MODIFY eq_preset varbinary(255) default NULL;",
770  "ALTER TABLE music_stats"
771  " MODIFY total_time varbinary(12) NOT NULL default '0',"
772  " MODIFY total_size varbinary(10) NOT NULL default '0';",
773  ""
774  };
775 
776  if (!performActualUpdate(updates, "1015", dbver))
777  return false;
778  }
779 
780 
781  if (dbver == "1015")
782  {
783  const QString updates[] =
784  {
785  QString("ALTER DATABASE %1 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;")
787  // NOLINTNEXTLINE(bugprone-suspicious-missing-comma)
788  "ALTER TABLE music_albumart"
789  " DEFAULT CHARACTER SET default,"
790  " MODIFY filename varchar(255) CHARACTER SET utf8 NOT NULL default '';",
791  "ALTER TABLE music_albums"
792  " DEFAULT CHARACTER SET default,"
793  " MODIFY album_name varchar(255) CHARACTER SET utf8 NOT NULL default '';",
794  "ALTER TABLE music_artists"
795  " DEFAULT CHARACTER SET default,"
796  " MODIFY artist_name varchar(255) CHARACTER SET utf8 NOT NULL default '';",
797  "ALTER TABLE music_directories"
798  " DEFAULT CHARACTER SET default,"
799  " MODIFY path text CHARACTER SET utf8 NOT NULL;",
800  "ALTER TABLE music_genres"
801  " DEFAULT CHARACTER SET default,"
802  " MODIFY genre varchar(255) CHARACTER SET utf8 NOT NULL default '';",
803  "ALTER TABLE music_playlists"
804  " DEFAULT CHARACTER SET default,"
805  " MODIFY playlist_name varchar(255) CHARACTER SET utf8 NOT NULL default '',"
806  " MODIFY playlist_songs text CHARACTER SET utf8 NOT NULL,"
807  " MODIFY hostname varchar(64) CHARACTER SET utf8 NOT NULL default '';",
808  "ALTER TABLE music_smartplaylist_categories"
809  " DEFAULT CHARACTER SET default,"
810  " MODIFY name varchar(128) CHARACTER SET utf8 NOT NULL;",
811  "ALTER TABLE music_smartplaylist_items"
812  " DEFAULT CHARACTER SET default,"
813  " MODIFY field varchar(50) CHARACTER SET utf8 NOT NULL,"
814  " MODIFY operator varchar(20) CHARACTER SET utf8 NOT NULL,"
815  " MODIFY value1 varchar(255) CHARACTER SET utf8 NOT NULL,"
816  " MODIFY value2 varchar(255) CHARACTER SET utf8 NOT NULL;",
817  "ALTER TABLE music_smartplaylists"
818  " DEFAULT CHARACTER SET default,"
819  " MODIFY name varchar(128) CHARACTER SET utf8 NOT NULL,"
820  " MODIFY orderby varchar(128) CHARACTER SET utf8 NOT NULL default '';",
821  "ALTER TABLE music_songs"
822  " DEFAULT CHARACTER SET default,"
823  " MODIFY filename text CHARACTER SET utf8 NOT NULL,"
824  " MODIFY name varchar(255) CHARACTER SET utf8 NOT NULL default '',"
825  " MODIFY format varchar(4) CHARACTER SET utf8 NOT NULL default '0',"
826  " MODIFY mythdigest varchar(255) CHARACTER SET utf8 default NULL,"
827  " MODIFY description varchar(255) CHARACTER SET utf8 default NULL,"
828  " MODIFY comment varchar(255) CHARACTER SET utf8 default NULL,"
829  " MODIFY eq_preset varchar(255) CHARACTER SET utf8 default NULL;",
830  "ALTER TABLE music_stats"
831  " DEFAULT CHARACTER SET default,"
832  " MODIFY total_time varchar(12) CHARACTER SET utf8 NOT NULL default '0',"
833  " MODIFY total_size varchar(10) CHARACTER SET utf8 NOT NULL default '0';",
834  ""
835  };
836 
837  if (!performActualUpdate(updates, "1016", dbver))
838  return false;
839  }
840 
841  if (dbver == "1016")
842  {
843  const QString updates[] =
844  {
845  "DELETE FROM keybindings "
846  " WHERE action = 'DELETE' AND context = 'Music';",
847  ""
848  };
849 
850  if (!performActualUpdate(updates, "1017", dbver))
851  return false;
852  }
853 
854  if (dbver == "1017")
855  {
856  const QString updates[] =
857  {
858  "ALTER TABLE music_playlists MODIFY COLUMN last_accessed "
859  " TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;",
860  ""
861  };
862 
863  if (!performActualUpdate(updates, "1018", dbver))
864  return false;
865  }
866 
867  if (dbver == "1018")
868  {
869  const QString updates[] =
870  {
871  "CREATE TEMPORARY TABLE arttype_tmp ( type INT, name VARCHAR(30) );",
872  "INSERT INTO arttype_tmp VALUES (0,'unknown'),(1,'front'),(2,'back'),(3,'cd'),(4,'inlay');",
873  "UPDATE music_albumart LEFT JOIN arttype_tmp ON type = imagetype "
874  "SET filename = CONCAT(song_id, '-', name, '.jpg') WHERE embedded=1;",
875  ""
876  };
877 
878  if (!performActualUpdate(updates, "1019", dbver))
879  return false;
880  }
881 
882  if (dbver == "1019")
883  {
884  const QString updates[] =
885  {
886  "DROP TABLE IF EXISTS music_radios;",
887  "CREATE TABLE music_radios ("
888  " intid INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,"
889  " station VARCHAR(128) NOT NULL,"
890  " channel VARCHAR(128) NOT NULL,"
891  " url VARCHAR(128) NOT NULL,"
892  " logourl VARCHAR(128) NOT NULL,"
893  " genre VARCHAR(128) NOT NULL,"
894  " metaformat VARCHAR(128) NOT NULL,"
895  " format VARCHAR(10) NOT NULL,"
896  " INDEX (station),"
897  " INDEX (channel)"
898  ");",
899  ""
900  };
901 
902  if (!performActualUpdate(updates, "1020", dbver))
903  return false;
904  }
905 
906  if (dbver == "1020")
907  {
908  const QString updates[] =
909  {
910  "ALTER TABLE music_songs ADD COLUMN hostname VARCHAR(255) NOT NULL default '';",
911  QString("UPDATE music_songs SET hostname = '%1';").arg(gCoreContext->GetMasterHostName()),
912  ""
913  };
914 
915  if (!performActualUpdate(updates, "1021", dbver))
916  return false;
917  }
918 
919  if (dbver == "1021")
920  {
921  const QString updates[] =
922  {
923  "ALTER TABLE music_albumart ADD COLUMN hostname VARCHAR(255) NOT NULL default '';",
924  QString("UPDATE music_albumart SET hostname = '%1';").arg(gCoreContext->GetMasterHostName()),
925  ""
926  };
927 
928  if (!performActualUpdate(updates, "1022", dbver))
929  return false;
930  }
931 
932  if (dbver == "1022")
933  {
934  const QString updates[] =
935  {
936  "CREATE INDEX `song_id` ON music_albumart (song_id);",
937  "CREATE INDEX `artist_id` ON music_albums (artist_id);",
938  ""
939  };
940 
941  if (!performActualUpdate(updates, "1023", dbver))
942  return false;
943  }
944 
945  if (dbver == "1023")
946  {
947  const QString updates[] =
948  {
949  "DROP INDEX station ON music_radios;",
950  "ALTER TABLE music_radios CHANGE COLUMN station broadcaster VARCHAR(100) NOT NULL default '';",
951  "ALTER TABLE music_radios MODIFY COLUMN channel VARCHAR(200) NOT NULL default '';",
952  "ALTER TABLE music_radios ADD description TEXT NOT NULL default '' AFTER channel;",
953  "ALTER TABLE music_radios CHANGE COLUMN url url1 VARCHAR(300) NOT NULL default '';",
954  "ALTER TABLE music_radios ADD COLUMN url2 VARCHAR(300) NOT NULL default '' AFTER url1;",
955  "ALTER TABLE music_radios ADD COLUMN url3 VARCHAR(300) NOT NULL default '' AFTER url2;",
956  "ALTER TABLE music_radios ADD COLUMN url4 VARCHAR(300) NOT NULL default '' AFTER url3;",
957  "ALTER TABLE music_radios ADD COLUMN url5 VARCHAR(300) NOT NULL default '' AFTER url4;",
958  "ALTER TABLE music_radios MODIFY COLUMN logourl VARCHAR(300) NOT NULL default '';",
959  "ALTER TABLE music_radios MODIFY COLUMN metaformat VARCHAR(50) NOT NULL default '';",
960  "ALTER TABLE music_radios ADD COLUMN country VARCHAR(50) NOT NULL default '' AFTER logourl;",
961  "ALTER TABLE music_radios ADD COLUMN language VARCHAR(50) NOT NULL default '' AFTER country;",
962  "CREATE INDEX broadcaster ON music_radios (broadcaster);",
963  "DROP TABLE IF EXISTS music_streams;",
964  "CREATE TABLE music_streams ("
965  " intid INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,"
966  " broadcaster VARCHAR(100) NOT NULL default '',"
967  " channel VARCHAR(200) NOT NULL default '',"
968  " description TEXT NOT NULL default '',"
969  " url1 VARCHAR(300) NOT NULL default '',"
970  " url2 VARCHAR(300) NOT NULL default '',"
971  " url3 VARCHAR(300) NOT NULL default '',"
972  " url4 VARCHAR(300) NOT NULL default '',"
973  " url5 VARCHAR(300) NOT NULL default '',"
974  " logourl VARCHAR(300) NOT NULL default '',"
975  " genre VARCHAR(100) NOT NULL default '',"
976  " metaformat VARCHAR(50) NOT NULL default '',"
977  " country VARCHAR(50) NOT NULL default '',"
978  " language VARCHAR(50) NOT NULL default '',"
979  " INDEX (broadcaster),"
980  " INDEX (channel),"
981  " INDEX (country),"
982  " INDEX (language)"
983  ");",
984  ""
985  };
986 
987  if (!performActualUpdate(updates, "1024", dbver))
988  return false;
989  }
990 
991  return true;
992 }
bool next(void)
Wrap QSqlQuery::next() so we can display the query results.
Definition: mythdbcon.cpp:781
Provides UI and helper functions for DB Schema updates.
Definition: schemawizard.h:25
void bindValue(const QString &placeholder, const QVariant &val)
Add a single binding.
Definition: mythdbcon.cpp:862
QString dbName
database name
Definition: mythdbparams.h:26
void SaveSetting(const QString &key, int newValue)
QSqlQuery wrapper that fetches a DB connection from the connection pool.
Definition: mythdbcon.h:125
int size(void) const
Definition: mythdbcon.h:203
MythCoreContext * gCoreContext
This global variable contains the MythCoreContext instance for the app.
DatabaseParams GetDatabaseParams(void)
MythContext * gContext
This global variable contains the MythContext instance for the application.
Definition: mythcontext.cpp:62
const QString currentDatabaseVersion
enum MythSchemaUpgrade PromptForUpgrade(const char *name, const bool upgradeAllowed, const bool upgradeIfNoUI, const int minDBMSmajor=0, const int minDBMSminor=0, const int minDBMSpoint=0)
Query user, to prevent silent, automatic database upgrades.
QVariant value(int i) const
Definition: mythdbcon.h:198
static SchemaUpgradeWizard * Get(const QString &DBSchemaSetting, const QString &appName, const QString &upgradeSchemaVal)
Instead of creating a new wizard, use the existing one for its DB backup file & results and expert se...
QSqlError lastError(void) const
Definition: mythdbcon.h:202
QString GetSetting(const QString &key, const QString &defaultval="")
static bool doUpgradeMusicDatabaseSchema(QString &dbver)
int Compare(void)
How many schema versions old is the DB?
bool isActive(void) const
Definition: mythdbcon.h:204
QString GetMasterHostName(void)
unsigned int uint
Definition: compat.h:140
static MSqlQueryInfo InitCon(ConnectionReuse=kNormalConnection)
Only use this in combination with MSqlQuery constructor.
Definition: mythdbcon.cpp:534
static ImageType guessImageType(const QString &filename)
bool first(void)
Wrap QSqlQuery::first() so we can display the query results.
Definition: mythdbcon.cpp:791
bool prepare(const QString &query)
QSqlQuery::prepare() is not thread safe in Qt <= 3.3.2.
Definition: mythdbcon.cpp:806
#define LOG(_MASK_, _LEVEL_, _STRING_)
Definition: mythlogging.h:41
static void UnlockSchema(MSqlQuery &)
Definition: dbutil.cpp:856
int numRowsAffected() const
Definition: mythdbcon.h:206
static QString DBErrorMessage(const QSqlError &err)
Definition: mythdb.cpp:184
static bool UpdateDBVersionNumber(const QString &newnumber)
QString m_DBver
Schema version in the database.
Definition: schemawizard.h:55
static bool TryLockSchema(MSqlQuery &, uint timeout_secs)
Try to get a lock on the table schemalock.
Definition: dbutil.cpp:849
bool exec(void)
Wrap QSqlQuery::exec() so we can display SQL.
Definition: mythdbcon.cpp:602
MythDB * GetMythDB(void)
Definition: mythdb.cpp:46
bool SaveSettingOnHost(const QString &key, const QString &newValue, const QString &host)
static void DBError(const QString &where, const MSqlQuery &query)
Definition: mythdb.cpp:179
void ActivateSettingsCache(bool activate=true)
bool UpgradeMusicDatabaseSchema(void)
static bool performActualUpdate(const QString updates[], const QString &version, QString &dbver)