Go to the documentation of this file.
26 #ifdef IGNORE_SCHEMA_VER_MISMATCH
39 for (
uint i = 0; i < 2*60 && !locked; i++)
41 LOG(VB_GENERAL, LOG_INFO,
"Waiting for database schema upgrade lock");
44 LOG(VB_GENERAL, LOG_INFO,
"Got schema upgrade lock");
48 LOG(VB_GENERAL, LOG_INFO,
"Failed to get schema upgrade lock");
49 goto upgrade_error_exit;
55 if (schema_wizard->
Compare() == 0)
58 if (schema_wizard->
m_DBver.isEmpty())
64 goto upgrade_error_exit;
74 goto upgrade_error_exit;
81 LOG(VB_GENERAL, LOG_ERR,
"Database schema upgrade failed.");
82 goto upgrade_error_exit;
85 LOG(VB_GENERAL, LOG_INFO,
"MythMusic database schema upgrade complete.");
93 GetMythDB()->SetSuppressDBMessages(
false);
100 GetMythDB()->SetSuppressDBMessages(
false);
112 LOG(VB_GENERAL, LOG_NOTICE,
113 "Inserting MythMusic initial database information.");
117 "CREATE TABLE IF NOT EXISTS musicmetadata ("
118 " intid INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,"
119 " artist VARCHAR(128) NOT NULL,"
120 " album VARCHAR(128) NOT NULL,"
121 " title VARCHAR(128) NOT NULL,"
122 " genre VARCHAR(128) NOT NULL,"
123 " year INT UNSIGNED NOT NULL,"
124 " tracknum INT UNSIGNED NOT NULL,"
125 " length INT UNSIGNED NOT NULL,"
126 " filename TEXT NOT NULL,"
127 " rating INT UNSIGNED NOT NULL DEFAULT 5,"
128 " lastplay TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP "
129 " ON UPDATE CURRENT_TIMESTAMP,"
130 " playcount INT UNSIGNED NOT NULL DEFAULT 0,"
136 "CREATE TABLE IF NOT EXISTS musicplaylist ("
137 " playlistid INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,"
138 " name VARCHAR(128) NOT NULL,"
139 " hostname VARCHAR(255),"
140 " songlist TEXT NOT NULL"
145 updates,
"1000", dbver))
152 startdir = QDir::cleanPath(startdir);
153 if (!startdir.endsWith(
"/"))
158 if (query.
exec(
"SELECT filename, intid FROM musicmetadata WHERE "
159 "filename NOT LIKE ('%://%');"))
169 name = query.
value(0).toString();
171 intid = query.
value(1).toString();
173 if (newname.startsWith(startdir))
175 newname.remove(0, startdir.length());
176 if (modify.
exec(QString(
"UPDATE musicmetadata SET "
178 "WHERE filename = \"%2\" AND intid = %3;")
179 .arg(newname, name, intid)))
183 LOG(VB_GENERAL, LOG_NOTICE,
184 QString(
"Modified %1 entries for db schema 1001").arg(i));
195 "ALTER TABLE musicmetadata ADD mythdigest VARCHAR(255);",
196 "ALTER TABLE musicmetadata ADD size BIGINT UNSIGNED;",
197 "ALTER TABLE musicmetadata ADD date_added DATETIME;",
198 "ALTER TABLE musicmetadata ADD date_modified DATETIME;",
199 "ALTER TABLE musicmetadata ADD format VARCHAR(4);",
200 "ALTER TABLE musicmetadata ADD description VARCHAR(255);",
201 "ALTER TABLE musicmetadata ADD comment VARCHAR(255);",
202 "ALTER TABLE musicmetadata ADD compilation TINYINT DEFAULT 0;",
203 "ALTER TABLE musicmetadata ADD composer VARCHAR(255);",
204 "ALTER TABLE musicmetadata ADD disc_count SMALLINT UNSIGNED DEFAULT 0;",
205 "ALTER TABLE musicmetadata ADD disc_number SMALLINT UNSIGNED DEFAULT 0;",
206 "ALTER TABLE musicmetadata ADD track_count SMALLINT UNSIGNED DEFAULT 0;",
207 "ALTER TABLE musicmetadata ADD start_time INT UNSIGNED DEFAULT 0;",
208 "ALTER TABLE musicmetadata ADD stop_time INT UNSIGNED;",
209 "ALTER TABLE musicmetadata ADD eq_preset VARCHAR(255);",
210 "ALTER TABLE musicmetadata ADD relative_volume TINYINT DEFAULT 0;",
211 "ALTER TABLE musicmetadata ADD sample_rate INT UNSIGNED;",
212 "ALTER TABLE musicmetadata ADD bpm SMALLINT UNSIGNED;",
213 "ALTER TABLE musicmetadata ADD INDEX (mythdigest);"
217 updates,
"1002", dbver))
223 LOG(VB_GENERAL, LOG_NOTICE,
224 "Updating music metadata to be UTF-8 in the database");
227 query.
prepare(
"SELECT intid, artist, album, title, genre, "
228 "filename FROM musicmetadata ORDER BY intid;");
234 int id = query.
value(0).toInt();
235 QString artist = query.
value(1).toString();
236 QString album = query.
value(2).toString();
237 QString title = query.
value(3).toString();
238 QString genre = query.
value(4).toString();
242 subquery.
prepare(
"UPDATE musicmetadata SET "
243 "artist = :ARTIST, album = :ALBUM, "
244 "title = :TITLE, genre = :GENRE, "
245 "filename = :FILENAME "
246 "WHERE intid = :ID;");
247 subquery.
bindValue(
":ARTIST", QString(artist.toUtf8()));
248 subquery.
bindValue(
":ALBUM", QString(album.toUtf8()));
249 subquery.
bindValue(
":TITLE", QString(title.toUtf8()));
250 subquery.
bindValue(
":GENRE", QString(genre.toUtf8()));
259 query.
prepare(
"SELECT playlistid, name FROM musicplaylist "
260 "ORDER BY playlistid;");
266 int id = query.
value(0).toInt();
267 QString name = query.
value(1).toString();
270 subquery.
prepare(
"UPDATE musicplaylist SET "
271 "name = :NAME WHERE playlistid = :ID ;");
272 subquery.
bindValue(
":NAME", QString(name.toUtf8()));
280 LOG(VB_GENERAL, LOG_NOTICE,
"Done updating music metadata to UTF-8");
290 "DROP TABLE IF EXISTS smartplaylistcategory;",
291 "CREATE TABLE smartplaylistcategory ("
292 " categoryid INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,"
293 " name VARCHAR(128) NOT NULL,"
298 "INSERT INTO smartplaylistcategory SET categoryid = 1, "
299 " name = \"Decades\";",
300 "INSERT INTO smartplaylistcategory SET categoryid = 2, "
301 " name = \"Favourite Tracks\";",
302 "INSERT INTO smartplaylistcategory SET categoryid = 3, "
303 " name = \"New Tracks\";",
305 "DROP TABLE IF EXISTS smartplaylist;",
306 "CREATE TABLE smartplaylist ("
307 " smartplaylistid INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,"
308 " name VARCHAR(128) NOT NULL,"
309 " categoryid INT UNSIGNED NOT NULL,"
310 " matchtype SET('All', 'Any') NOT NULL DEFAULT 'All',"
311 " orderby VARCHAR(128) NOT NULL DEFAULT '',"
312 " limitto INT UNSIGNED NOT NULL DEFAULT 0,"
314 " INDEX (categoryid)"
316 "DROP TABLE IF EXISTS smartplaylistitem;",
317 "CREATE TABLE smartplaylistitem ("
318 " smartplaylistitemid INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,"
319 " smartplaylistid INT UNSIGNED NOT NULL,"
320 " field VARCHAR(50) NOT NULL,"
321 " operator VARCHAR(20) NOT NULL,"
322 " value1 VARCHAR(255) NOT NULL,"
323 " value2 VARCHAR(255) NOT NULL,"
324 " INDEX (smartplaylistid)"
326 "INSERT INTO smartplaylist SET smartplaylistid = 1, name = \"1960's\", "
327 " categoryid = 1, matchtype = \"All\", orderby = \"Artist (A)\","
329 "INSERT INTO smartplaylistitem SET smartplaylistid = 1, field = \"Year\","
330 " operator = \"is between\", value1 = \"1960\", value2 = \"1969\";",
332 "INSERT INTO smartplaylist SET smartplaylistid = 2, name = \"1970's\", "
333 " categoryid = 1, matchtype = \"All\", orderby = \"Artist (A)\","
335 "INSERT INTO smartplaylistitem SET smartplaylistid = 2, field = \"Year\","
336 " operator = \"is between\", value1 = \"1970\", value2 = \"1979\";",
338 "INSERT INTO smartplaylist SET smartplaylistid = 3, name = \"1980's\", "
339 " categoryid = 1, matchtype = \"All\", orderby = \"Artist (A)\","
341 "INSERT INTO smartplaylistitem SET smartplaylistid = 3, field = \"Year\","
342 " operator = \"is between\", value1 = \"1980\", value2 = \"1989\";",
344 "INSERT INTO smartplaylist SET smartplaylistid = 4, name = \"1990's\", "
345 " categoryid = 1, matchtype = \"All\", orderby = \"Artist (A)\","
347 "INSERT INTO smartplaylistitem SET smartplaylistid = 4, field = \"Year\","
348 " operator = \"is between\", value1 = \"1990\", value2 = \"1999\";",
350 "INSERT INTO smartplaylist SET smartplaylistid = 5, name = \"2000's\", "
351 " categoryid = 1, matchtype = \"All\", orderby = \"Artist (A)\","
353 "INSERT INTO smartplaylistitem SET smartplaylistid = 5, field = \"Year\","
354 " operator = \"is between\", value1 = \"2000\", value2 = \"2009\";",
356 "INSERT INTO smartplaylist SET smartplaylistid = 6, name = \"Favorite Tracks\", "
357 " categoryid = 2, matchtype = \"All\","
358 " orderby = \"Artist (A), Album (A)\", limitto = 0;",
359 "INSERT INTO smartplaylistitem SET smartplaylistid = 6, field = \"Rating\","
360 " operator = \"is greater than\", value1 = \"7\", value2 = \"0\";",
362 "INSERT INTO smartplaylist SET smartplaylistid = 7, name = \"100 Most Played Tracks\", "
363 " categoryid = 2, matchtype = \"All\", orderby = \"Play Count (D)\","
365 "INSERT INTO smartplaylistitem SET smartplaylistid = 7, field = \"Play Count\","
366 " operator = \"is greater than\", value1 = \"0\", value2 = \"0\";",
368 "INSERT INTO smartplaylist SET smartplaylistid = 8, name = \"Never Played Tracks\", "
369 " categoryid = 3, matchtype = \"All\", orderby = \"Artist (A), Album (A)\","
371 "INSERT INTO smartplaylistitem SET smartplaylistid = 8, field = \"Play Count\","
372 " operator = \"is equal to\", value1 = \"0\", value2 = \"0\";"
376 updates,
"1004", dbver))
384 "ALTER TABLE musicmetadata ADD compilation_artist VARCHAR(128) NOT NULL AFTER artist;",
385 "ALTER TABLE musicmetadata ADD INDEX (compilation_artist);"
389 updates,
"1005", dbver))
398 "CREATE TABLE music_albums ("
399 " album_id int(11) unsigned NOT NULL auto_increment PRIMARY KEY,"
400 " artist_id int(11) unsigned NOT NULL default '0',"
401 " album_name varchar(255) NOT NULL default '',"
402 " year smallint(6) NOT NULL default '0',"
403 " compilation tinyint(1) unsigned NOT NULL default '0',"
404 " INDEX idx_album_name(album_name)"
406 "CREATE TABLE music_artists ("
407 " artist_id int(11) unsigned NOT NULL auto_increment PRIMARY KEY,"
408 " artist_name varchar(255) NOT NULL default '',"
409 " INDEX idx_artist_name(artist_name)"
411 "CREATE TABLE music_genres ("
412 " genre_id int(11) unsigned NOT NULL auto_increment PRIMARY KEY,"
413 " genre varchar(25) NOT NULL default '',"
414 " INDEX idx_genre(genre)"
416 "CREATE TABLE music_playlists ("
417 " playlist_id int(11) unsigned NOT NULL auto_increment PRIMARY KEY,"
418 " playlist_name varchar(255) NOT NULL default '',"
419 " playlist_songs text NOT NULL,"
420 " last_accessed timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP "
421 " ON UPDATE CURRENT_TIMESTAMP,"
422 " length int(11) unsigned NOT NULL default '0',"
423 " songcount smallint(8) unsigned NOT NULL default '0',"
424 " hostname VARCHAR(255) NOT NULL default ''"
426 "CREATE TABLE music_songs ("
427 " song_id int(11) unsigned NOT NULL auto_increment PRIMARY KEY,"
428 " filename text NOT NULL,"
429 " name varchar(255) NOT NULL default '',"
430 " track smallint(6) unsigned NOT NULL default '0',"
431 " artist_id int(11) unsigned NOT NULL default '0',"
432 " album_id int(11) unsigned NOT NULL default '0',"
433 " genre_id int(11) unsigned NOT NULL default '0',"
434 " year smallint(6) NOT NULL default '0',"
435 " length int(11) unsigned NOT NULL default '0',"
436 " numplays int(11) unsigned NOT NULL default '0',"
437 " rating tinyint(4) unsigned NOT NULL default '0',"
438 " lastplay timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP "
439 " ON UPDATE CURRENT_TIMESTAMP,"
440 " date_entered datetime default NULL,"
441 " date_modified datetime default NULL,"
442 " format varchar(4) NOT NULL default '0',"
443 " mythdigest VARCHAR(255),"
444 " size BIGINT(20) unsigned,"
445 " description VARCHAR(255),"
446 " comment VARCHAR(255),"
447 " disc_count SMALLINT(5) UNSIGNED DEFAULT '0',"
448 " disc_number SMALLINT(5) UNSIGNED DEFAULT '0',"
449 " track_count SMALLINT(5) UNSIGNED DEFAULT '0',"
450 " start_time INT(10) UNSIGNED DEFAULT '0',"
451 " stop_time INT(10) UNSIGNED,"
452 " eq_preset VARCHAR(255),"
453 " relative_volume TINYINT DEFAULT '0',"
454 " sample_rate INT(10) UNSIGNED DEFAULT '0',"
455 " bitrate INT(10) UNSIGNED DEFAULT '0',"
456 " bpm SMALLINT(5) UNSIGNED,"
457 " INDEX idx_name(name),"
458 " INDEX idx_mythdigest(mythdigest)"
460 "CREATE TABLE music_stats ("
461 " num_artists smallint(5) unsigned NOT NULL default '0',"
462 " num_albums smallint(5) unsigned NOT NULL default '0',"
463 " num_songs mediumint(8) unsigned NOT NULL default '0',"
464 " num_genres tinyint(3) unsigned NOT NULL default '0',"
465 " total_time varchar(12) NOT NULL default '0',"
466 " total_size varchar(10) NOT NULL default '0'"
468 "RENAME TABLE smartplaylist TO music_smartplaylists;",
469 "RENAME TABLE smartplaylistitem TO music_smartplaylist_items;",
470 "RENAME TABLE smartplaylistcategory TO music_smartplaylist_categories;",
473 "CREATE TEMPORARY TABLE tmp_artists"
474 " SELECT DISTINCT artist FROM musicmetadata;",
475 "INSERT INTO tmp_artists"
476 " SELECT DISTINCT compilation_artist"
477 " FROM musicmetadata"
478 " WHERE compilation_artist<>artist;",
479 "INSERT INTO music_artists (artist_name) SELECT DISTINCT artist FROM tmp_artists;",
480 "INSERT INTO music_albums (artist_id, album_name, year, compilation) "
481 " SELECT artist_id, album, ROUND(AVG(year)) AS year, IF(SUM(compilation),1,0) AS compilation"
482 " FROM musicmetadata"
483 " LEFT JOIN music_artists ON compilation_artist=artist_name"
484 " GROUP BY artist_id, album;",
485 "INSERT INTO music_genres (genre) SELECT DISTINCT genre FROM musicmetadata;",
486 "INSERT INTO music_songs "
487 " (song_id, artist_id, album_id, genre_id, year, lastplay,"
488 " date_entered, date_modified, name, track, length, size, numplays,"
490 " SELECT intid, ma.artist_id, mb.album_id, mg.genre_id, mmd.year, lastplay,"
491 " date_added, date_modified, title, tracknum, length, IFNULL(size,0), playcount,"
493 " FROM musicmetadata AS mmd"
494 " LEFT JOIN music_artists AS ma ON mmd.artist=ma.artist_name"
495 " LEFT JOIN music_artists AS mc ON mmd.compilation_artist=mc.artist_name"
496 " LEFT JOIN music_albums AS mb ON mmd.album=mb.album_name AND mc.artist_id=mb.artist_id"
497 " LEFT JOIN music_genres AS mg ON mmd.genre=mg.genre;",
498 "INSERT INTO music_playlists"
499 " (playlist_id,playlist_name,playlist_songs,hostname)"
500 " SELECT playlistid, name, songlist, hostname"
501 " FROM musicplaylist;",
503 "UPDATE music_playlists"
505 " WHERE playlist_name<>'default_playlist_storage'"
506 " AND playlist_name<>'backup_playlist_storage';"
510 updates,
"1006", dbver))
518 "ALTER TABLE music_genres MODIFY genre VARCHAR(255) NOT NULL default '';"
522 updates,
"1007", dbver))
530 "ALTER TABLE music_songs MODIFY lastplay DATETIME DEFAULT NULL;",
532 "CREATE TABLE music_directories (directory_id int(20) NOT NULL AUTO_INCREMENT "
533 "PRIMARY KEY, path TEXT NOT NULL, "
534 "parent_id INT(20) NOT NULL DEFAULT '0') ;",
535 "INSERT IGNORE INTO music_directories (path) SELECT DISTINCT"
536 " SUBSTRING(filename FROM 1 FOR INSTR(filename, "
537 "SUBSTRING_INDEX(filename, '/', -1))-2) FROM music_songs;",
538 "CREATE TEMPORARY TABLE tmp_songs SELECT music_songs.*, directory_id "
539 "FROM music_songs, music_directories WHERE "
540 "music_directories.path=SUBSTRING(filename FROM 1 FOR "
541 "INSTR(filename, SUBSTRING_INDEX(filename, '/', -1))-2);",
542 "UPDATE tmp_songs SET filename=SUBSTRING_INDEX(filename, '/', -1);",
543 "DELETE FROM music_songs;",
544 "ALTER TABLE music_songs ADD COLUMN directory_id int(20) NOT NULL DEFAULT '0';",
545 "INSERT INTO music_songs SELECT * FROM tmp_songs;",
546 "ALTER TABLE music_songs ADD INDEX (directory_id);"
550 updates,
"1008", dbver))
558 "CREATE TABLE music_albumart (albumart_id int(20) NOT NULL AUTO_INCREMENT "
559 "PRIMARY KEY, filename VARCHAR(255) NOT NULL DEFAULT '', directory_id INT(20) "
560 "NOT NULL DEFAULT '0');"
564 updates,
"1009", dbver))
572 "ALTER TABLE music_albumart ADD COLUMN imagetype tinyint(3) NOT NULL DEFAULT '0';"
576 updates,
"1010", dbver))
582 LOG(VB_GENERAL, LOG_NOTICE,
"Updating music_albumart image types");
585 query.
prepare(
"SELECT albumart_id, filename, directory_id, imagetype FROM music_albumart;");
591 int id = query.
value(0).toInt();
593 int directoryID = query.
value(2).toInt();
603 subquery.
prepare(
"SELECT count(directory_id) FROM music_albumart "
604 "WHERE directory_id = :DIR;");
609 if (query.
value(0).toInt() == 1)
614 subquery.
prepare(
"UPDATE music_albumart "
615 "SET imagetype = :TYPE "
616 "WHERE albumart_id = :ID;");
629 setting = setting.simplified();
630 setting = setting.replace(
' ',
";");
642 "ALTER TABLE music_albumart ADD COLUMN song_id int(11) NOT NULL DEFAULT '0', "
643 " ADD COLUMN embedded TINYINT(1) NOT NULL DEFAULT '0';"
647 updates,
"1012", dbver))
656 "ALTER TABLE music_songs ADD INDEX album_id (album_id);",
657 "ALTER TABLE music_songs ADD INDEX genre_id (genre_id);",
658 "ALTER TABLE music_songs ADD INDEX artist_id (artist_id);"
662 updates,
"1013", dbver))
671 "DROP TABLE musicmetadata;",
672 "DROP TABLE musicplaylist;"
676 updates,
"1014", dbver))
684 qPrintable(QString(
"ALTER DATABASE %1 DEFAULT CHARACTER SET latin1;")
687 "ALTER TABLE music_albumart"
688 " MODIFY filename varbinary(255) NOT NULL default '';",
689 "ALTER TABLE music_albums"
690 " MODIFY album_name varbinary(255) NOT NULL default '';",
691 "ALTER TABLE music_artists"
692 " MODIFY artist_name varbinary(255) NOT NULL default '';",
693 "ALTER TABLE music_directories"
694 " MODIFY path blob NOT NULL;",
695 "ALTER TABLE music_genres"
696 " MODIFY genre varbinary(255) NOT NULL default '';",
697 "ALTER TABLE music_playlists"
698 " MODIFY playlist_name varbinary(255) NOT NULL default '',"
699 " MODIFY playlist_songs blob NOT NULL,"
700 " MODIFY hostname varbinary(64) NOT NULL default '';",
701 "ALTER TABLE music_smartplaylist_categories"
702 " MODIFY name varbinary(128) NOT NULL;",
703 "ALTER TABLE music_smartplaylist_items"
704 " MODIFY field varbinary(50) NOT NULL,"
705 " MODIFY operator varbinary(20) NOT NULL,"
706 " MODIFY value1 varbinary(255) NOT NULL,"
707 " MODIFY value2 varbinary(255) NOT NULL;",
708 "ALTER TABLE music_smartplaylists"
709 " MODIFY name varbinary(128) NOT NULL,"
710 " MODIFY orderby varbinary(128) NOT NULL default '';",
711 "ALTER TABLE music_songs"
712 " MODIFY filename blob NOT NULL,"
713 " MODIFY name varbinary(255) NOT NULL default '',"
714 " MODIFY format varbinary(4) NOT NULL default '0',"
715 " MODIFY mythdigest varbinary(255) default NULL,"
716 " MODIFY description varbinary(255) default NULL,"
717 " MODIFY comment varbinary(255) default NULL,"
718 " MODIFY eq_preset varbinary(255) default NULL;",
719 "ALTER TABLE music_stats"
720 " MODIFY total_time varbinary(12) NOT NULL default '0',"
721 " MODIFY total_size varbinary(10) NOT NULL default '0';"
725 updates,
"1015", dbver))
734 qPrintable(QString(
"ALTER DATABASE %1 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;")
737 "ALTER TABLE music_albumart"
738 " DEFAULT CHARACTER SET utf8,"
739 " MODIFY filename varchar(255) CHARACTER SET utf8 NOT NULL default '';",
740 "ALTER TABLE music_albums"
741 " DEFAULT CHARACTER SET utf8,"
742 " MODIFY album_name varchar(255) CHARACTER SET utf8 NOT NULL default '';",
743 "ALTER TABLE music_artists"
744 " DEFAULT CHARACTER SET utf8,"
745 " MODIFY artist_name varchar(255) CHARACTER SET utf8 NOT NULL default '';",
746 "ALTER TABLE music_directories"
747 " DEFAULT CHARACTER SET utf8,"
748 " MODIFY path text CHARACTER SET utf8 NOT NULL;",
749 "ALTER TABLE music_genres"
750 " DEFAULT CHARACTER SET utf8,"
751 " MODIFY genre varchar(255) CHARACTER SET utf8 NOT NULL default '';",
752 "ALTER TABLE music_playlists"
753 " DEFAULT CHARACTER SET utf8,"
754 " MODIFY playlist_name varchar(255) CHARACTER SET utf8 NOT NULL default '',"
755 " MODIFY playlist_songs text CHARACTER SET utf8 NOT NULL,"
756 " MODIFY hostname varchar(64) CHARACTER SET utf8 NOT NULL default '';",
757 "ALTER TABLE music_smartplaylist_categories"
758 " DEFAULT CHARACTER SET utf8,"
759 " MODIFY name varchar(128) CHARACTER SET utf8 NOT NULL;",
760 "ALTER TABLE music_smartplaylist_items"
761 " DEFAULT CHARACTER SET utf8,"
762 " MODIFY field varchar(50) CHARACTER SET utf8 NOT NULL,"
763 " MODIFY operator varchar(20) CHARACTER SET utf8 NOT NULL,"
764 " MODIFY value1 varchar(255) CHARACTER SET utf8 NOT NULL,"
765 " MODIFY value2 varchar(255) CHARACTER SET utf8 NOT NULL;",
766 "ALTER TABLE music_smartplaylists"
767 " DEFAULT CHARACTER SET utf8,"
768 " MODIFY name varchar(128) CHARACTER SET utf8 NOT NULL,"
769 " MODIFY orderby varchar(128) CHARACTER SET utf8 NOT NULL default '';",
770 "ALTER TABLE music_songs"
771 " DEFAULT CHARACTER SET utf8,"
772 " MODIFY filename text CHARACTER SET utf8 NOT NULL,"
773 " MODIFY name varchar(255) CHARACTER SET utf8 NOT NULL default '',"
774 " MODIFY format varchar(4) CHARACTER SET utf8 NOT NULL default '0',"
775 " MODIFY mythdigest varchar(255) CHARACTER SET utf8 default NULL,"
776 " MODIFY description varchar(255) CHARACTER SET utf8 default NULL,"
777 " MODIFY comment varchar(255) CHARACTER SET utf8 default NULL,"
778 " MODIFY eq_preset varchar(255) CHARACTER SET utf8 default NULL;",
779 "ALTER TABLE music_stats"
780 " DEFAULT CHARACTER SET utf8,"
781 " MODIFY total_time varchar(12) CHARACTER SET utf8 NOT NULL default '0',"
782 " MODIFY total_size varchar(10) CHARACTER SET utf8 NOT NULL default '0';"
786 updates,
"1016", dbver))
794 "DELETE FROM keybindings "
795 " WHERE action = 'DELETE' AND context = 'Music';"
799 updates,
"1017", dbver))
807 "ALTER TABLE music_playlists MODIFY COLUMN last_accessed "
808 " TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;"
812 updates,
"1018", dbver))
820 "CREATE TEMPORARY TABLE arttype_tmp ( type INT, name VARCHAR(30) );",
821 "INSERT INTO arttype_tmp VALUES (0,'unknown'),(1,'front'),(2,'back'),(3,'cd'),(4,'inlay');",
822 "UPDATE music_albumart LEFT JOIN arttype_tmp ON type = imagetype "
823 "SET filename = CONCAT(song_id, '-', name, '.jpg') WHERE embedded=1;"
827 updates,
"1019", dbver))
835 "DROP TABLE IF EXISTS music_radios;",
836 "CREATE TABLE music_radios ("
837 " intid INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,"
838 " station VARCHAR(128) NOT NULL,"
839 " channel VARCHAR(128) NOT NULL,"
840 " url VARCHAR(128) NOT NULL,"
841 " logourl VARCHAR(128) NOT NULL,"
842 " genre VARCHAR(128) NOT NULL,"
843 " metaformat VARCHAR(128) NOT NULL,"
844 " format VARCHAR(10) NOT NULL,"
851 updates,
"1020", dbver))
859 "ALTER TABLE music_songs ADD COLUMN hostname VARCHAR(255) NOT NULL default '';",
860 qPrintable(QString(
"UPDATE music_songs SET hostname = '%1';")
865 updates,
"1021", dbver))
873 "ALTER TABLE music_albumart ADD COLUMN hostname VARCHAR(255) NOT NULL default '';",
874 qPrintable(QString(
"UPDATE music_albumart SET hostname = '%1';")
879 updates,
"1022", dbver))
887 "CREATE INDEX `song_id` ON music_albumart (song_id);",
888 "CREATE INDEX `artist_id` ON music_albums (artist_id);"
892 updates,
"1023", dbver))
900 "DROP INDEX station ON music_radios;",
901 "ALTER TABLE music_radios CHANGE COLUMN station broadcaster VARCHAR(100) NOT NULL default '';",
902 "ALTER TABLE music_radios MODIFY COLUMN channel VARCHAR(200) NOT NULL default '';",
903 "ALTER TABLE music_radios ADD description TEXT NOT NULL default '' AFTER channel;",
904 "ALTER TABLE music_radios CHANGE COLUMN url url1 VARCHAR(300) NOT NULL default '';",
905 "ALTER TABLE music_radios ADD COLUMN url2 VARCHAR(300) NOT NULL default '' AFTER url1;",
906 "ALTER TABLE music_radios ADD COLUMN url3 VARCHAR(300) NOT NULL default '' AFTER url2;",
907 "ALTER TABLE music_radios ADD COLUMN url4 VARCHAR(300) NOT NULL default '' AFTER url3;",
908 "ALTER TABLE music_radios ADD COLUMN url5 VARCHAR(300) NOT NULL default '' AFTER url4;",
909 "ALTER TABLE music_radios MODIFY COLUMN logourl VARCHAR(300) NOT NULL default '';",
910 "ALTER TABLE music_radios MODIFY COLUMN metaformat VARCHAR(50) NOT NULL default '';",
911 "ALTER TABLE music_radios ADD COLUMN country VARCHAR(50) NOT NULL default '' AFTER logourl;",
912 "ALTER TABLE music_radios ADD COLUMN language VARCHAR(50) NOT NULL default '' AFTER country;",
913 "CREATE INDEX broadcaster ON music_radios (broadcaster);",
914 "DROP TABLE IF EXISTS music_streams;",
915 "CREATE TABLE music_streams ("
916 " intid INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,"
917 " broadcaster VARCHAR(100) NOT NULL default '',"
918 " channel VARCHAR(200) NOT NULL default '',"
919 " description TEXT NOT NULL default '',"
920 " url1 VARCHAR(300) NOT NULL default '',"
921 " url2 VARCHAR(300) NOT NULL default '',"
922 " url3 VARCHAR(300) NOT NULL default '',"
923 " url4 VARCHAR(300) NOT NULL default '',"
924 " url5 VARCHAR(300) NOT NULL default '',"
925 " logourl VARCHAR(300) NOT NULL default '',"
926 " genre VARCHAR(100) NOT NULL default '',"
927 " metaformat VARCHAR(50) NOT NULL default '',"
928 " country VARCHAR(50) NOT NULL default '',"
929 " language VARCHAR(50) NOT NULL default '',"
930 " INDEX (broadcaster),"
938 updates,
"1024", dbver))
948 "ALTER TABLE music_albumart DEFAULT CHARACTER SET utf8;",
949 "ALTER TABLE music_albums DEFAULT CHARACTER SET utf8;",
950 "ALTER TABLE music_artists DEFAULT CHARACTER SET utf8;",
951 "ALTER TABLE music_directories DEFAULT CHARACTER SET utf8;",
952 "ALTER TABLE music_genres DEFAULT CHARACTER SET utf8;",
953 "ALTER TABLE music_playlists DEFAULT CHARACTER SET utf8;",
954 "ALTER TABLE music_smartplaylist_categories DEFAULT CHARACTER SET utf8;",
955 "ALTER TABLE music_smartplaylist_items DEFAULT CHARACTER SET utf8;",
956 "ALTER TABLE music_smartplaylists DEFAULT CHARACTER SET utf8;",
957 "ALTER TABLE music_songs DEFAULT CHARACTER SET utf8;",
958 "ALTER TABLE music_stats DEFAULT CHARACTER SET utf8;"
962 updates,
"1025", dbver))
static bool doUpgradeMusicDatabaseSchema(QString &dbver)
bool isActive(void) const
bool next(void)
Wrap QSqlQuery::next() so we can display the query results.
QSqlQuery wrapper that fetches a DB connection from the connection pool.
QString GetMasterHostName(void)
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...
int Compare(void)
How many schema versions old is the DB?
QVariant value(int i) const
bool exec(void)
Wrap QSqlQuery::exec() so we can display SQL.
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.
#define LOG(_MASK_, _LEVEL_, _QSTRING_)
const QString MythMusicVersionName
static ImageType guessImageType(const QString &filename)
@ MYTH_SCHEMA_USE_EXISTING
bool first(void)
Wrap QSqlQuery::first() so we can display the query results.
static MSqlQueryInfo InitCon(ConnectionReuse _reuse=kNormalConnection)
Only use this in combination with MSqlQuery constructor.
static void DBError(const QString &where, const MSqlQuery &query)
const QString currentDatabaseVersion
MythCoreContext * gCoreContext
This global variable contains the MythCoreContext instance for the app.
static bool TryLockSchema(MSqlQuery &query, uint timeout_secs)
Try to get a lock on the table schemalock.
bool UpdateDBVersionNumber(const QString &component, const QString &versionkey, const QString &newnumber, QString &dbver)
Updates the schema version stored in the database.
bool UpgradeMusicDatabaseSchema(void)
bool performActualUpdate(const QString &component, const QString &versionkey, const DBUpdates &updates, const QString &version, QString &dbver)
Provides UI and helper functions for DB Schema updates.
void bindValue(const QString &placeholder, const QVariant &val)
Add a single binding.
void ActivateSettingsCache(bool activate=true)
static void UnlockSchema(MSqlQuery &query)
int numRowsAffected() const
std::vector< std::string > DBUpdates
void SaveSetting(const QString &key, int newValue)
QString m_DBver
Schema version in the database.
QString GetSetting(const QString &key, const QString &defaultval="")
bool prepare(const QString &query)
QSqlQuery::prepare() is not thread safe in Qt <= 3.3.2.