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