Go to the documentation of this file.
10 #include <mythtv/mythdb.h>
11 #include <mythtv/schemawizard.h>
22 #ifdef IGNORE_SCHEMA_VER_MISMATCH
35 for (
uint i = 0; i < 2*60 && !locked; i++)
37 LOG(VB_GENERAL, LOG_INFO,
"Waiting for database schema upgrade lock");
40 LOG(VB_GENERAL, LOG_INFO,
"Got schema upgrade lock");
44 LOG(VB_GENERAL, LOG_INFO,
"Failed to get schema upgrade lock");
45 goto upgrade_error_exit;
51 if (schema_wizard->
Compare() == 0)
54 if (schema_wizard->
m_DBver.isEmpty())
60 goto upgrade_error_exit;
70 goto upgrade_error_exit;
77 LOG(VB_GENERAL, LOG_ERR,
"Database schema upgrade failed.");
78 goto upgrade_error_exit;
81 LOG(VB_GENERAL, LOG_INFO,
"MythMusic database schema upgrade complete.");
89 GetMythDB()->SetSuppressDBMessages(
false);
96 GetMythDB()->SetSuppressDBMessages(
false);
108 LOG(VB_GENERAL, LOG_NOTICE,
109 "Inserting MythMusic initial database information.");
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,"
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"
141 updates,
"1000", dbver))
148 startdir = QDir::cleanPath(startdir);
149 if (!startdir.endsWith(
"/"))
154 if (
query.
exec(
"SELECT filename, intid FROM musicmetadata WHERE "
155 "filename NOT LIKE ('%://%');"))
169 if (newname.startsWith(startdir))
171 newname.remove(0, startdir.length());
172 if (modify.
exec(QString(
"UPDATE musicmetadata SET "
174 "WHERE filename = \"%2\" AND intid = %3;")
179 LOG(VB_GENERAL, LOG_NOTICE,
180 QString(
"Modified %1 entries for db schema 1001").
arg(i));
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);"
213 updates,
"1002", dbver))
219 LOG(VB_GENERAL, LOG_NOTICE,
220 "Updating music metadata to be UTF-8 in the database");
223 query.
prepare(
"SELECT intid, artist, album, title, genre, "
224 "filename FROM musicmetadata ORDER BY intid;");
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()));
246 subquery.
bindValue(
":GENRE", QString(genre.toUtf8()));
255 query.
prepare(
"SELECT playlistid, name FROM musicplaylist "
256 "ORDER BY playlistid;");
266 subquery.
prepare(
"UPDATE musicplaylist SET "
267 "name = :NAME WHERE playlistid = :ID ;");
268 subquery.
bindValue(
":NAME", QString(name.toUtf8()));
276 LOG(VB_GENERAL, LOG_NOTICE,
"Done updating music metadata to UTF-8");
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,"
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\";",
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,"
310 " INDEX (categoryid)"
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)"
322 "INSERT INTO smartplaylist SET smartplaylistid = 1, name = \"1960's\", "
323 " categoryid = 1, matchtype = \"All\", orderby = \"Artist (A)\","
325 "INSERT INTO smartplaylistitem SET smartplaylistid = 1, field = \"Year\","
326 " operator = \"is between\", value1 = \"1960\", value2 = \"1969\";",
328 "INSERT INTO smartplaylist SET smartplaylistid = 2, name = \"1970's\", "
329 " categoryid = 1, matchtype = \"All\", orderby = \"Artist (A)\","
331 "INSERT INTO smartplaylistitem SET smartplaylistid = 2, field = \"Year\","
332 " operator = \"is between\", value1 = \"1970\", value2 = \"1979\";",
334 "INSERT INTO smartplaylist SET smartplaylistid = 3, name = \"1980's\", "
335 " categoryid = 1, matchtype = \"All\", orderby = \"Artist (A)\","
337 "INSERT INTO smartplaylistitem SET smartplaylistid = 3, field = \"Year\","
338 " operator = \"is between\", value1 = \"1980\", value2 = \"1989\";",
340 "INSERT INTO smartplaylist SET smartplaylistid = 4, name = \"1990's\", "
341 " categoryid = 1, matchtype = \"All\", orderby = \"Artist (A)\","
343 "INSERT INTO smartplaylistitem SET smartplaylistid = 4, field = \"Year\","
344 " operator = \"is between\", value1 = \"1990\", value2 = \"1999\";",
346 "INSERT INTO smartplaylist SET smartplaylistid = 5, name = \"2000's\", "
347 " categoryid = 1, matchtype = \"All\", orderby = \"Artist (A)\","
349 "INSERT INTO smartplaylistitem SET smartplaylistid = 5, field = \"Year\","
350 " operator = \"is between\", value1 = \"2000\", value2 = \"2009\";",
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\";",
358 "INSERT INTO smartplaylist SET smartplaylistid = 7, name = \"100 Most Played Tracks\", "
359 " categoryid = 2, matchtype = \"All\", orderby = \"Play Count (D)\","
361 "INSERT INTO smartplaylistitem SET smartplaylistid = 7, field = \"Play Count\","
362 " operator = \"is greater than\", value1 = \"0\", value2 = \"0\";",
364 "INSERT INTO smartplaylist SET smartplaylistid = 8, name = \"Never Played Tracks\", "
365 " categoryid = 3, matchtype = \"All\", orderby = \"Artist (A), Album (A)\","
367 "INSERT INTO smartplaylistitem SET smartplaylistid = 8, field = \"Play Count\","
368 " operator = \"is equal to\", value1 = \"0\", value2 = \"0\";"
372 updates,
"1004", dbver))
380 "ALTER TABLE musicmetadata ADD compilation_artist VARCHAR(128) NOT NULL AFTER artist;",
381 "ALTER TABLE musicmetadata ADD INDEX (compilation_artist);"
385 updates,
"1005", dbver))
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)"
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)"
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)"
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 ''"
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)"
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'"
464 "RENAME TABLE smartplaylist TO music_smartplaylists;",
465 "RENAME TABLE smartplaylistitem TO music_smartplaylist_items;",
466 "RENAME TABLE smartplaylistcategory TO music_smartplaylist_categories;",
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,"
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,"
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;",
499 "UPDATE music_playlists"
501 " WHERE playlist_name<>'default_playlist_storage'"
502 " AND playlist_name<>'backup_playlist_storage';"
506 updates,
"1006", dbver))
514 "ALTER TABLE music_genres MODIFY genre VARCHAR(255) NOT NULL default '';"
518 updates,
"1007", dbver))
526 "ALTER TABLE music_songs MODIFY lastplay DATETIME DEFAULT NULL;",
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);"
546 updates,
"1008", dbver))
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');"
560 updates,
"1009", dbver))
568 "ALTER TABLE music_albumart ADD COLUMN imagetype tinyint(3) NOT NULL DEFAULT '0';"
572 updates,
"1010", dbver))
578 LOG(VB_GENERAL, LOG_NOTICE,
"Updating music_albumart image types");
581 query.
prepare(
"SELECT albumart_id, filename, directory_id, imagetype FROM music_albumart;");
599 subquery.
prepare(
"SELECT count(directory_id) FROM music_albumart "
600 "WHERE directory_id = :DIR;");
610 subquery.
prepare(
"UPDATE music_albumart "
611 "SET imagetype = :TYPE "
612 "WHERE albumart_id = :ID;");
625 setting = setting.simplified();
626 setting = setting.replace(
' ',
";");
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';"
643 updates,
"1012", dbver))
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);"
658 updates,
"1013", dbver))
667 "DROP TABLE musicmetadata;",
668 "DROP TABLE musicplaylist;"
672 updates,
"1014", dbver))
680 qPrintable(QString(
"ALTER DATABASE %1 DEFAULT CHARACTER SET latin1;")
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';"
721 updates,
"1015", dbver))
730 qPrintable(QString(
"ALTER DATABASE %1 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;")
733 "ALTER TABLE music_albumart"
734 " DEFAULT CHARACTER SET utf8,"
735 " MODIFY filename varchar(255) CHARACTER SET utf8 NOT NULL default '';",
736 "ALTER TABLE music_albums"
737 " DEFAULT CHARACTER SET utf8,"
738 " MODIFY album_name varchar(255) CHARACTER SET utf8 NOT NULL default '';",
739 "ALTER TABLE music_artists"
740 " DEFAULT CHARACTER SET utf8,"
741 " MODIFY artist_name varchar(255) CHARACTER SET utf8 NOT NULL default '';",
742 "ALTER TABLE music_directories"
743 " DEFAULT CHARACTER SET utf8,"
744 " MODIFY path text CHARACTER SET utf8 NOT NULL;",
745 "ALTER TABLE music_genres"
746 " DEFAULT CHARACTER SET utf8,"
747 " MODIFY genre varchar(255) CHARACTER SET utf8 NOT NULL default '';",
748 "ALTER TABLE music_playlists"
749 " DEFAULT CHARACTER SET utf8,"
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 utf8,"
755 " MODIFY name varchar(128) CHARACTER SET utf8 NOT NULL;",
756 "ALTER TABLE music_smartplaylist_items"
757 " DEFAULT CHARACTER SET utf8,"
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 utf8,"
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 utf8,"
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 utf8,"
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';"
782 updates,
"1016", dbver))
790 "DELETE FROM keybindings "
791 " WHERE action = 'DELETE' AND context = 'Music';"
795 updates,
"1017", dbver))
803 "ALTER TABLE music_playlists MODIFY COLUMN last_accessed "
804 " TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;"
808 updates,
"1018", dbver))
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;"
823 updates,
"1019", dbver))
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,"
847 updates,
"1020", dbver))
855 "ALTER TABLE music_songs ADD COLUMN hostname VARCHAR(255) NOT NULL default '';",
856 qPrintable(QString(
"UPDATE music_songs SET hostname = '%1';")
861 updates,
"1021", dbver))
869 "ALTER TABLE music_albumart ADD COLUMN hostname VARCHAR(255) NOT NULL default '';",
870 qPrintable(QString(
"UPDATE music_albumart SET hostname = '%1';")
875 updates,
"1022", dbver))
883 "CREATE INDEX `song_id` ON music_albumart (song_id);",
884 "CREATE INDEX `artist_id` ON music_albums (artist_id);"
888 updates,
"1023", dbver))
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),"
934 updates,
"1024", dbver))
944 "ALTER TABLE music_albumart DEFAULT CHARACTER SET utf8;",
945 "ALTER TABLE music_albums DEFAULT CHARACTER SET utf8;",
946 "ALTER TABLE music_artists DEFAULT CHARACTER SET utf8;",
947 "ALTER TABLE music_directories DEFAULT CHARACTER SET utf8;",
948 "ALTER TABLE music_genres DEFAULT CHARACTER SET utf8;",
949 "ALTER TABLE music_playlists DEFAULT CHARACTER SET utf8;",
950 "ALTER TABLE music_smartplaylist_categories DEFAULT CHARACTER SET utf8;",
951 "ALTER TABLE music_smartplaylist_items DEFAULT CHARACTER SET utf8;",
952 "ALTER TABLE music_smartplaylists DEFAULT CHARACTER SET utf8;",
953 "ALTER TABLE music_songs DEFAULT CHARACTER SET utf8;",
954 "ALTER TABLE music_stats DEFAULT CHARACTER SET utf8;"
958 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...
DatabaseParams GetDatabaseParams(void)
int Compare(void)
How many schema versions old is the DB?
QVariant value(int i) const
arg(title).arg(filename).arg(doDelete))
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
QString m_dbName
database name
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)
MSqlQuery query(MSqlQuery::InitCon())
MythContext * gContext
This global variable contains the MythContext instance for the application.
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.