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  "INSERT INTO smartplaylistcategory SET categoryid = 1, "
346  " name = \"Decades\";",
347  "INSERT INTO smartplaylistcategory SET categoryid = 2, "
348  " name = \"Favourite Tracks\";",
349  "INSERT INTO smartplaylistcategory SET categoryid = 3, "
350  " name = \"New Tracks\";",
351 
352  "DROP TABLE IF EXISTS smartplaylist;",
353  "CREATE TABLE smartplaylist ("
354  " smartplaylistid INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,"
355  " name VARCHAR(128) NOT NULL,"
356  " categoryid INT UNSIGNED NOT NULL,"
357  " matchtype SET('All', 'Any') NOT NULL DEFAULT 'All',"
358  " orderby VARCHAR(128) NOT NULL DEFAULT '',"
359  " limitto INT UNSIGNED NOT NULL DEFAULT 0,"
360  " INDEX (name),"
361  " INDEX (categoryid)"
362  ");",
363  "DROP TABLE IF EXISTS smartplaylistitem;",
364  "CREATE TABLE smartplaylistitem ("
365  " smartplaylistitemid INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,"
366  " smartplaylistid INT UNSIGNED NOT NULL,"
367  " field VARCHAR(50) NOT NULL,"
368  " operator VARCHAR(20) NOT NULL,"
369  " value1 VARCHAR(255) NOT NULL,"
370  " value2 VARCHAR(255) NOT NULL,"
371  " INDEX (smartplaylistid)"
372  ");",
373  "INSERT INTO smartplaylist SET smartplaylistid = 1, name = \"1960's\", "
374  " categoryid = 1, matchtype = \"All\", orderby = \"Artist (A)\","
375  " limitto = 0;",
376  "INSERT INTO smartplaylistitem SET smartplaylistid = 1, field = \"Year\","
377  " operator = \"is between\", value1 = \"1960\", value2 = \"1969\";",
378 
379  "INSERT INTO smartplaylist SET smartplaylistid = 2, name = \"1970's\", "
380  " categoryid = 1, matchtype = \"All\", orderby = \"Artist (A)\","
381  " limitto = 0;",
382  "INSERT INTO smartplaylistitem SET smartplaylistid = 2, field = \"Year\","
383  " operator = \"is between\", value1 = \"1970\", value2 = \"1979\";",
384 
385  "INSERT INTO smartplaylist SET smartplaylistid = 3, name = \"1980's\", "
386  " categoryid = 1, matchtype = \"All\", orderby = \"Artist (A)\","
387  " limitto = 0;",
388  "INSERT INTO smartplaylistitem SET smartplaylistid = 3, field = \"Year\","
389  " operator = \"is between\", value1 = \"1980\", value2 = \"1989\";",
390 
391  "INSERT INTO smartplaylist SET smartplaylistid = 4, name = \"1990's\", "
392  " categoryid = 1, matchtype = \"All\", orderby = \"Artist (A)\","
393  " limitto = 0;",
394  "INSERT INTO smartplaylistitem SET smartplaylistid = 4, field = \"Year\","
395  " operator = \"is between\", value1 = \"1990\", value2 = \"1999\";",
396 
397  "INSERT INTO smartplaylist SET smartplaylistid = 5, name = \"2000's\", "
398  " categoryid = 1, matchtype = \"All\", orderby = \"Artist (A)\","
399  " limitto = 0;",
400  "INSERT INTO smartplaylistitem SET smartplaylistid = 5, field = \"Year\","
401  " operator = \"is between\", value1 = \"2000\", value2 = \"2009\";",
402 
403  "INSERT INTO smartplaylist SET smartplaylistid = 6, name = \"Favorite Tracks\", "
404  " categoryid = 2, matchtype = \"All\","
405  " orderby = \"Artist (A), Album (A)\", limitto = 0;",
406  "INSERT INTO smartplaylistitem SET smartplaylistid = 6, field = \"Rating\","
407  " operator = \"is greater than\", value1 = \"7\", value2 = \"0\";",
408 
409  "INSERT INTO smartplaylist SET smartplaylistid = 7, name = \"100 Most Played Tracks\", "
410  " categoryid = 2, matchtype = \"All\", orderby = \"Play Count (D)\","
411  " limitto = 100;",
412  "INSERT INTO smartplaylistitem SET smartplaylistid = 7, field = \"Play Count\","
413  " operator = \"is greater than\", value1 = \"0\", value2 = \"0\";",
414 
415  "INSERT INTO smartplaylist SET smartplaylistid = 8, name = \"Never Played Tracks\", "
416  " categoryid = 3, matchtype = \"All\", orderby = \"Artist (A), Album (A)\","
417  " limitto = 0;",
418  "INSERT INTO smartplaylistitem SET smartplaylistid = 8, field = \"Play Count\","
419  " operator = \"is equal to\", value1 = \"0\", value2 = \"0\";",
420 
421  ""
422  };
423 
424  if (!performActualUpdate(updates, "1004", dbver))
425  return false;
426  }
427 
428  if (dbver == "1004")
429  {
430  const QString updates[] =
431  {
432  "ALTER TABLE musicmetadata ADD compilation_artist VARCHAR(128) NOT NULL AFTER artist;",
433  "ALTER TABLE musicmetadata ADD INDEX (compilation_artist);",
434  ""
435  };
436 
437  if (!performActualUpdate(updates, "1005", dbver))
438  return false;
439  }
440 
441 
442  if (dbver == "1005")
443  {
444  const QString updates[] =
445  {
446  "CREATE TABLE music_albums ("
447  " album_id int(11) unsigned NOT NULL auto_increment PRIMARY KEY,"
448  " artist_id int(11) unsigned NOT NULL default '0',"
449  " album_name varchar(255) NOT NULL default '',"
450  " year smallint(6) NOT NULL default '0',"
451  " compilation tinyint(1) unsigned NOT NULL default '0',"
452  " INDEX idx_album_name(album_name)"
453  ");",
454  "CREATE TABLE music_artists ("
455  " artist_id int(11) unsigned NOT NULL auto_increment PRIMARY KEY,"
456  " artist_name varchar(255) NOT NULL default '',"
457  " INDEX idx_artist_name(artist_name)"
458  ");",
459  "CREATE TABLE music_genres ("
460  " genre_id int(11) unsigned NOT NULL auto_increment PRIMARY KEY,"
461  " genre varchar(25) NOT NULL default '',"
462  " INDEX idx_genre(genre)"
463  ");",
464  "CREATE TABLE music_playlists ("
465  " playlist_id int(11) unsigned NOT NULL auto_increment PRIMARY KEY,"
466  " playlist_name varchar(255) NOT NULL default '',"
467  " playlist_songs text NOT NULL,"
468  " last_accessed timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP "
469  " ON UPDATE CURRENT_TIMESTAMP,"
470  " length int(11) unsigned NOT NULL default '0',"
471  " songcount smallint(8) unsigned NOT NULL default '0',"
472  " hostname VARCHAR(255) NOT NULL default ''"
473  ");",
474  "CREATE TABLE music_songs ("
475  " song_id int(11) unsigned NOT NULL auto_increment PRIMARY KEY,"
476  " filename text NOT NULL,"
477  " name varchar(255) NOT NULL default '',"
478  " track smallint(6) unsigned NOT NULL default '0',"
479  " artist_id int(11) unsigned NOT NULL default '0',"
480  " album_id int(11) unsigned NOT NULL default '0',"
481  " genre_id int(11) unsigned NOT NULL default '0',"
482  " year smallint(6) NOT NULL default '0',"
483  " length int(11) unsigned NOT NULL default '0',"
484  " numplays int(11) unsigned NOT NULL default '0',"
485  " rating tinyint(4) unsigned NOT NULL default '0',"
486  " lastplay timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP "
487  " ON UPDATE CURRENT_TIMESTAMP,"
488  " date_entered datetime default NULL,"
489  " date_modified datetime default NULL,"
490  " format varchar(4) NOT NULL default '0',"
491  " mythdigest VARCHAR(255),"
492  " size BIGINT(20) unsigned,"
493  " description VARCHAR(255),"
494  " comment VARCHAR(255),"
495  " disc_count SMALLINT(5) UNSIGNED DEFAULT '0',"
496  " disc_number SMALLINT(5) UNSIGNED DEFAULT '0',"
497  " track_count SMALLINT(5) UNSIGNED DEFAULT '0',"
498  " start_time INT(10) UNSIGNED DEFAULT '0',"
499  " stop_time INT(10) UNSIGNED,"
500  " eq_preset VARCHAR(255),"
501  " relative_volume TINYINT DEFAULT '0',"
502  " sample_rate INT(10) UNSIGNED DEFAULT '0',"
503  " bitrate INT(10) UNSIGNED DEFAULT '0',"
504  " bpm SMALLINT(5) UNSIGNED,"
505  " INDEX idx_name(name),"
506  " INDEX idx_mythdigest(mythdigest)"
507  ");",
508  "CREATE TABLE music_stats ("
509  " num_artists smallint(5) unsigned NOT NULL default '0',"
510  " num_albums smallint(5) unsigned NOT NULL default '0',"
511  " num_songs mediumint(8) unsigned NOT NULL default '0',"
512  " num_genres tinyint(3) unsigned NOT NULL default '0',"
513  " total_time varchar(12) NOT NULL default '0',"
514  " total_size varchar(10) NOT NULL default '0'"
515  ");",
516  "RENAME TABLE smartplaylist TO music_smartplaylists;",
517  "RENAME TABLE smartplaylistitem TO music_smartplaylist_items;",
518  "RENAME TABLE smartplaylistcategory TO music_smartplaylist_categories;",
519  // Run necessary SQL to migrate the table structure
520  "CREATE TEMPORARY TABLE tmp_artists"
521  " SELECT DISTINCT artist FROM musicmetadata;",
522  "INSERT INTO tmp_artists"
523  " SELECT DISTINCT compilation_artist"
524  " FROM musicmetadata"
525  " WHERE compilation_artist<>artist;",
526  "INSERT INTO music_artists (artist_name) SELECT DISTINCT artist FROM tmp_artists;",
527  "INSERT INTO music_albums (artist_id, album_name, year, compilation) "
528  " SELECT artist_id, album, ROUND(AVG(year)) AS year, IF(SUM(compilation),1,0) AS compilation"
529  " FROM musicmetadata"
530  " LEFT JOIN music_artists ON compilation_artist=artist_name"
531  " GROUP BY artist_id, album;",
532  "INSERT INTO music_genres (genre) SELECT DISTINCT genre FROM musicmetadata;",
533  "INSERT INTO music_songs "
534  " (song_id, artist_id, album_id, genre_id, year, lastplay,"
535  " date_entered, date_modified, name, track, length, size, numplays,"
536  " rating, filename)"
537  " SELECT intid, ma.artist_id, mb.album_id, mg.genre_id, mmd.year, lastplay,"
538  " date_added, date_modified, title, tracknum, length, IFNULL(size,0), playcount,"
539  " rating, filename"
540  " FROM musicmetadata AS mmd"
541  " LEFT JOIN music_artists AS ma ON mmd.artist=ma.artist_name"
542  " LEFT JOIN music_artists AS mc ON mmd.compilation_artist=mc.artist_name"
543  " LEFT JOIN music_albums AS mb ON mmd.album=mb.album_name AND mc.artist_id=mb.artist_id"
544  " LEFT JOIN music_genres AS mg ON mmd.genre=mg.genre;",
545  "INSERT INTO music_playlists"
546  " (playlist_id,playlist_name,playlist_songs,hostname)"
547  " SELECT playlistid, name, songlist, hostname"
548  " FROM musicplaylist;",
549  // Set all real playlists to be global by killing the hostname
550  "UPDATE music_playlists"
551  " SET hostname=''"
552  " WHERE playlist_name<>'default_playlist_storage'"
553  " AND playlist_name<>'backup_playlist_storage';",
554  ""
555  };
556 
557  if (!performActualUpdate(updates, "1006", dbver))
558  return false;
559  }
560 
561  if (dbver == "1006")
562  {
563  const QString updates[] =
564  {
565  "ALTER TABLE music_genres MODIFY genre VARCHAR(255) NOT NULL default '';",
566  ""
567  };
568 
569  if (!performActualUpdate(updates, "1007", dbver))
570  return false;
571  }
572 
573  if (dbver == "1007")
574  {
575  const QString updates[] =
576  {
577  "ALTER TABLE music_songs MODIFY lastplay DATETIME DEFAULT NULL;",
578  "CREATE TABLE music_directories (directory_id int(20) NOT NULL AUTO_INCREMENT "
579  "PRIMARY KEY, path TEXT NOT NULL, "
580  "parent_id INT(20) NOT NULL DEFAULT '0') ;",
581  "INSERT IGNORE INTO music_directories (path) SELECT DISTINCT"
582  " SUBSTRING(filename FROM 1 FOR INSTR(filename, "
583  "SUBSTRING_INDEX(filename, '/', -1))-2) FROM music_songs;",
584  "CREATE TEMPORARY TABLE tmp_songs SELECT music_songs.*, directory_id "
585  "FROM music_songs, music_directories WHERE "
586  "music_directories.path=SUBSTRING(filename FROM 1 FOR "
587  "INSTR(filename, SUBSTRING_INDEX(filename, '/', -1))-2);",
588  "UPDATE tmp_songs SET filename=SUBSTRING_INDEX(filename, '/', -1);",
589  "DELETE FROM music_songs;",
590  "ALTER TABLE music_songs ADD COLUMN directory_id int(20) NOT NULL DEFAULT '0';",
591  "INSERT INTO music_songs SELECT * FROM tmp_songs;",
592  "ALTER TABLE music_songs ADD INDEX (directory_id);",
593  ""
594  };
595 
596  if (!performActualUpdate(updates, "1008", dbver))
597  return false;
598  }
599 
600  if (dbver == "1008")
601  {
602  const QString updates[] =
603  {
604  "CREATE TABLE music_albumart (albumart_id int(20) NOT NULL AUTO_INCREMENT "
605  "PRIMARY KEY, filename VARCHAR(255) NOT NULL DEFAULT '', directory_id INT(20) "
606  "NOT NULL DEFAULT '0');",
607  ""
608  };
609 
610  if (!performActualUpdate(updates, "1009", dbver))
611  return false;
612  }
613 
614  if (dbver == "1009")
615  {
616  const QString updates[] =
617  {
618  "ALTER TABLE music_albumart ADD COLUMN imagetype tinyint(3) NOT NULL DEFAULT '0';",
619  ""
620  };
621 
622  if (!performActualUpdate(updates, "1010", dbver))
623  return false;
624 
625  // scan though the music_albumart table and make a guess at what
626  // each image represents from the filename
627 
628  LOG(VB_GENERAL, LOG_NOTICE, "Updating music_albumart image types");
629 
630  MSqlQuery query(MSqlQuery::InitCon());
631  query.prepare("SELECT albumart_id, filename, directory_id, imagetype FROM music_albumart;");
632 
633  if (query.exec())
634  {
635  while (query.next())
636  {
637  int id = query.value(0).toInt();
638  QString filename = query.value(1).toString();
639  int directoryID = query.value(2).toInt();
640  MSqlQuery subquery(MSqlQuery::InitCon());
641 
642  // guess the type from the filename
643  int type = AlbumArtImages::guessImageType(filename);
644 
645  // if type is still unknown check to see how many images are available in the dir
646  // and assume that if this is the only image it must be the front cover
647  if (type == IT_UNKNOWN)
648  {
649  subquery.prepare("SELECT count(directory_id) FROM music_albumart "
650  "WHERE directory_id = :DIR;");
651  subquery.bindValue(":DIR", directoryID);
652  if (!subquery.exec() || !subquery.isActive())
653  MythDB::DBError("album art image count", subquery);
654  subquery.first();
655  if (query.value(0).toInt() == 1)
657  }
658 
659  // finally set the type in the music_albumart table
660  subquery.prepare("UPDATE music_albumart "
661  "SET imagetype = :TYPE "
662  "WHERE albumart_id = :ID;");
663  subquery.bindValue(":TYPE", type);
664  subquery.bindValue(":ID", id);
665  if (!subquery.exec() || !subquery.isActive())
666  MythDB::DBError("album art image type update", subquery);
667  }
668  }
669  }
670 
671  if (dbver == "1010")
672  {
673  const QString updates[] = {"", ""};
674 
675  // update the VisualMode setting to the new format
676  QString setting = gCoreContext->GetSetting("VisualMode");
677  setting = setting.simplified();
678  setting = setting.replace(' ', ";");
679  gCoreContext->SaveSetting("VisualMode", setting);
680 
681  if (!performActualUpdate(updates, "1011", dbver))
682  return false;
683 
684  }
685 
686  if (dbver == "1011")
687  {
688  const QString updates[] =
689  {
690  "ALTER TABLE music_albumart ADD COLUMN song_id int(11) NOT NULL DEFAULT '0', "
691  " ADD COLUMN embedded TINYINT(1) NOT NULL DEFAULT '0';",
692  ""
693  };
694 
695  if (!performActualUpdate(updates, "1012", dbver))
696  return false;
697 
698  }
699 
700  if (dbver == "1012")
701  {
702  const QString updates[] =
703  {
704  "ALTER TABLE music_songs ADD INDEX album_id (album_id);",
705  "ALTER TABLE music_songs ADD INDEX genre_id (genre_id);",
706  "ALTER TABLE music_songs ADD INDEX artist_id (artist_id);",
707  ""
708  };
709 
710  if (!performActualUpdate(updates, "1013", dbver))
711  return false;
712 
713  }
714 
715  if (dbver == "1013")
716  {
717  const QString updates[] =
718  {
719  "DROP TABLE musicmetadata;",
720  "DROP TABLE musicplaylist;",
721  ""
722  };
723 
724  if (!performActualUpdate(updates, "1014", dbver))
725  return false;
726  }
727 
728  if (dbver == "1014")
729  {
730  const QString updates[] =
731  {
732  QString("ALTER DATABASE %1 DEFAULT CHARACTER SET latin1;")
734  "ALTER TABLE music_albumart"
735  " MODIFY filename varbinary(255) NOT NULL default '';",
736  "ALTER TABLE music_albums"
737  " MODIFY album_name varbinary(255) NOT NULL default '';",
738  "ALTER TABLE music_artists"
739  " MODIFY artist_name varbinary(255) NOT NULL default '';",
740  "ALTER TABLE music_directories"
741  " MODIFY path blob NOT NULL;",
742  "ALTER TABLE music_genres"
743  " MODIFY genre varbinary(255) NOT NULL default '';",
744  "ALTER TABLE music_playlists"
745  " MODIFY playlist_name varbinary(255) NOT NULL default '',"
746  " MODIFY playlist_songs blob NOT NULL,"
747  " MODIFY hostname varbinary(64) NOT NULL default '';",
748  "ALTER TABLE music_smartplaylist_categories"
749  " MODIFY name varbinary(128) NOT NULL;",
750  "ALTER TABLE music_smartplaylist_items"
751  " MODIFY field varbinary(50) NOT NULL,"
752  " MODIFY operator varbinary(20) NOT NULL,"
753  " MODIFY value1 varbinary(255) NOT NULL,"
754  " MODIFY value2 varbinary(255) NOT NULL;",
755  "ALTER TABLE music_smartplaylists"
756  " MODIFY name varbinary(128) NOT NULL,"
757  " MODIFY orderby varbinary(128) NOT NULL default '';",
758  "ALTER TABLE music_songs"
759  " MODIFY filename blob NOT NULL,"
760  " MODIFY name varbinary(255) NOT NULL default '',"
761  " MODIFY format varbinary(4) NOT NULL default '0',"
762  " MODIFY mythdigest varbinary(255) default NULL,"
763  " MODIFY description varbinary(255) default NULL,"
764  " MODIFY comment varbinary(255) default NULL,"
765  " MODIFY eq_preset varbinary(255) default NULL;",
766  "ALTER TABLE music_stats"
767  " MODIFY total_time varbinary(12) NOT NULL default '0',"
768  " MODIFY total_size varbinary(10) NOT NULL default '0';",
769  ""
770  };
771 
772  if (!performActualUpdate(updates, "1015", dbver))
773  return false;
774  }
775 
776 
777  if (dbver == "1015")
778  {
779  const QString updates[] =
780  {
781  QString("ALTER DATABASE %1 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;")
783  "ALTER TABLE music_albumart"
784  " DEFAULT CHARACTER SET default,"
785  " MODIFY filename varchar(255) CHARACTER SET utf8 NOT NULL default '';",
786  "ALTER TABLE music_albums"
787  " DEFAULT CHARACTER SET default,"
788  " MODIFY album_name varchar(255) CHARACTER SET utf8 NOT NULL default '';",
789  "ALTER TABLE music_artists"
790  " DEFAULT CHARACTER SET default,"
791  " MODIFY artist_name varchar(255) CHARACTER SET utf8 NOT NULL default '';",
792  "ALTER TABLE music_directories"
793  " DEFAULT CHARACTER SET default,"
794  " MODIFY path text CHARACTER SET utf8 NOT NULL;",
795  "ALTER TABLE music_genres"
796  " DEFAULT CHARACTER SET default,"
797  " MODIFY genre varchar(255) CHARACTER SET utf8 NOT NULL default '';",
798  "ALTER TABLE music_playlists"
799  " DEFAULT CHARACTER SET default,"
800  " MODIFY playlist_name varchar(255) CHARACTER SET utf8 NOT NULL default '',"
801  " MODIFY playlist_songs text CHARACTER SET utf8 NOT NULL,"
802  " MODIFY hostname varchar(64) CHARACTER SET utf8 NOT NULL default '';",
803  "ALTER TABLE music_smartplaylist_categories"
804  " DEFAULT CHARACTER SET default,"
805  " MODIFY name varchar(128) CHARACTER SET utf8 NOT NULL;",
806  "ALTER TABLE music_smartplaylist_items"
807  " DEFAULT CHARACTER SET default,"
808  " MODIFY field varchar(50) CHARACTER SET utf8 NOT NULL,"
809  " MODIFY operator varchar(20) CHARACTER SET utf8 NOT NULL,"
810  " MODIFY value1 varchar(255) CHARACTER SET utf8 NOT NULL,"
811  " MODIFY value2 varchar(255) CHARACTER SET utf8 NOT NULL;",
812  "ALTER TABLE music_smartplaylists"
813  " DEFAULT CHARACTER SET default,"
814  " MODIFY name varchar(128) CHARACTER SET utf8 NOT NULL,"
815  " MODIFY orderby varchar(128) CHARACTER SET utf8 NOT NULL default '';",
816  "ALTER TABLE music_songs"
817  " DEFAULT CHARACTER SET default,"
818  " MODIFY filename text CHARACTER SET utf8 NOT NULL,"
819  " MODIFY name varchar(255) CHARACTER SET utf8 NOT NULL default '',"
820  " MODIFY format varchar(4) CHARACTER SET utf8 NOT NULL default '0',"
821  " MODIFY mythdigest varchar(255) CHARACTER SET utf8 default NULL,"
822  " MODIFY description varchar(255) CHARACTER SET utf8 default NULL,"
823  " MODIFY comment varchar(255) CHARACTER SET utf8 default NULL,"
824  " MODIFY eq_preset varchar(255) CHARACTER SET utf8 default NULL;",
825  "ALTER TABLE music_stats"
826  " DEFAULT CHARACTER SET default,"
827  " MODIFY total_time varchar(12) CHARACTER SET utf8 NOT NULL default '0',"
828  " MODIFY total_size varchar(10) CHARACTER SET utf8 NOT NULL default '0';",
829  ""
830  };
831 
832  if (!performActualUpdate(updates, "1016", dbver))
833  return false;
834  }
835 
836  if (dbver == "1016")
837  {
838  const QString updates[] =
839  {
840  "DELETE FROM keybindings "
841  " WHERE action = 'DELETE' AND context = 'Music';",
842  ""
843  };
844 
845  if (!performActualUpdate(updates, "1017", dbver))
846  return false;
847  }
848 
849  if (dbver == "1017")
850  {
851  const QString updates[] =
852  {
853  "ALTER TABLE music_playlists MODIFY COLUMN last_accessed "
854  " TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;",
855  ""
856  };
857 
858  if (!performActualUpdate(updates, "1018", dbver))
859  return false;
860  }
861 
862  if (dbver == "1018")
863  {
864  const QString updates[] =
865  {
866  "CREATE TEMPORARY TABLE arttype_tmp ( type INT, name VARCHAR(30) );",
867  "INSERT INTO arttype_tmp VALUES (0,'unknown'),(1,'front'),(2,'back'),(3,'cd'),(4,'inlay');",
868  "UPDATE music_albumart LEFT JOIN arttype_tmp ON type = imagetype "
869  "SET filename = CONCAT(song_id, '-', name, '.jpg') WHERE embedded=1;",
870  ""
871  };
872 
873  if (!performActualUpdate(updates, "1019", dbver))
874  return false;
875  }
876 
877  if (dbver == "1019")
878  {
879  const QString updates[] =
880  {
881  "DROP TABLE IF EXISTS music_radios;",
882  "CREATE TABLE music_radios ("
883  " intid INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,"
884  " station VARCHAR(128) NOT NULL,"
885  " channel VARCHAR(128) NOT NULL,"
886  " url VARCHAR(128) NOT NULL,"
887  " logourl VARCHAR(128) NOT NULL,"
888  " genre VARCHAR(128) NOT NULL,"
889  " metaformat VARCHAR(128) NOT NULL,"
890  " format VARCHAR(10) NOT NULL,"
891  " INDEX (station),"
892  " INDEX (channel)"
893  ");",
894  ""
895  };
896 
897  if (!performActualUpdate(updates, "1020", dbver))
898  return false;
899  }
900 
901  if (dbver == "1020")
902  {
903  const QString updates[] =
904  {
905  "ALTER TABLE music_songs ADD COLUMN hostname VARCHAR(255) NOT NULL default '';",
906  QString("UPDATE music_songs SET hostname = '%1';").arg(gCoreContext->GetMasterHostName()),
907  ""
908  };
909 
910  if (!performActualUpdate(updates, "1021", dbver))
911  return false;
912  }
913 
914  if (dbver == "1021")
915  {
916  const QString updates[] =
917  {
918  "ALTER TABLE music_albumart ADD COLUMN hostname VARCHAR(255) NOT NULL default '';",
919  QString("UPDATE music_albumart SET hostname = '%1';").arg(gCoreContext->GetMasterHostName()),
920  ""
921  };
922 
923  if (!performActualUpdate(updates, "1022", dbver))
924  return false;
925  }
926 
927  if (dbver == "1022")
928  {
929  const QString updates[] =
930  {
931  "CREATE INDEX `song_id` ON music_albumart (song_id);",
932  "CREATE INDEX `artist_id` ON music_albums (artist_id);",
933  ""
934  };
935 
936  if (!performActualUpdate(updates, "1023", dbver))
937  return false;
938  }
939 
940  if (dbver == "1023")
941  {
942  const QString updates[] =
943  {
944  "DROP INDEX station ON music_radios;",
945  "ALTER TABLE music_radios CHANGE COLUMN station broadcaster VARCHAR(100) NOT NULL default '';",
946  "ALTER TABLE music_radios MODIFY COLUMN channel VARCHAR(200) NOT NULL default '';",
947  "ALTER TABLE music_radios ADD description TEXT NOT NULL default '' AFTER channel;",
948  "ALTER TABLE music_radios CHANGE COLUMN url url1 VARCHAR(300) NOT NULL default '';",
949  "ALTER TABLE music_radios ADD COLUMN url2 VARCHAR(300) NOT NULL default '' AFTER url1;",
950  "ALTER TABLE music_radios ADD COLUMN url3 VARCHAR(300) NOT NULL default '' AFTER url2;",
951  "ALTER TABLE music_radios ADD COLUMN url4 VARCHAR(300) NOT NULL default '' AFTER url3;",
952  "ALTER TABLE music_radios ADD COLUMN url5 VARCHAR(300) NOT NULL default '' AFTER url4;",
953  "ALTER TABLE music_radios MODIFY COLUMN logourl VARCHAR(300) NOT NULL default '';",
954  "ALTER TABLE music_radios MODIFY COLUMN metaformat VARCHAR(50) NOT NULL default '';",
955  "ALTER TABLE music_radios ADD COLUMN country VARCHAR(50) NOT NULL default '' AFTER logourl;",
956  "ALTER TABLE music_radios ADD COLUMN language VARCHAR(50) NOT NULL default '' AFTER country;",
957  "CREATE INDEX broadcaster ON music_radios (broadcaster);",
958  "DROP TABLE IF EXISTS music_streams;",
959  "CREATE TABLE music_streams ("
960  " intid INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,"
961  " broadcaster VARCHAR(100) NOT NULL default '',"
962  " channel VARCHAR(200) NOT NULL default '',"
963  " description TEXT NOT NULL default '',"
964  " url1 VARCHAR(300) NOT NULL default '',"
965  " url2 VARCHAR(300) NOT NULL default '',"
966  " url3 VARCHAR(300) NOT NULL default '',"
967  " url4 VARCHAR(300) NOT NULL default '',"
968  " url5 VARCHAR(300) NOT NULL default '',"
969  " logourl VARCHAR(300) NOT NULL default '',"
970  " genre VARCHAR(100) NOT NULL default '',"
971  " metaformat VARCHAR(50) NOT NULL default '',"
972  " country VARCHAR(50) NOT NULL default '',"
973  " language VARCHAR(50) NOT NULL default '',"
974  " INDEX (broadcaster),"
975  " INDEX (channel),"
976  " INDEX (country),"
977  " INDEX (language)"
978  ");",
979  ""
980  };
981 
982  if (!performActualUpdate(updates, "1024", dbver))
983  return false;
984  }
985 
986  return true;
987 }
bool next(void)
Wrap QSqlQuery::next() so we can display the query results.
Definition: mythdbcon.cpp:782
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:863
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
unsigned int uint
Definition: compat.h:140
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:63
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)
static MSqlQueryInfo InitCon(ConnectionReuse=kNormalConnection)
Only use this in combination with MSqlQuery constructor.
Definition: mythdbcon.cpp:535
const char * name
Definition: ParseText.cpp:328
static ImageType guessImageType(const QString &filename)
bool first(void)
Wrap QSqlQuery::first() so we can display the query results.
Definition: mythdbcon.cpp:792
bool prepare(const QString &query)
QSqlQuery::prepare() is not thread safe in Qt <= 3.3.2.
Definition: mythdbcon.cpp:807
#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:603
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)