[mythtv] sql code
Andy Davidoff
dert at pobox.com
Thu Feb 20 05:11:33 EST 2003
The attached patch is against current CVS. This is straightforward
stuff, but I put in copious comments since they were absent.
The patch causes mythmusic to read old or new playlist tables, but to
only save in the new format. Saving will blow away the older tables,
but unless you have a custom interface or something, you won't notice
any changes, except in the speed at which MythMusic loads playlists.
Playlists have names and descriptions, neither of which are visible
in the existing interface, of course. Only the default playlist is
used currently, as ever. Playlist length is somewhat arbitrarily
limited to 2^16 entries and there may be 2^8 playlists, total. The
order of entries may now be specified, but of course there's no UI.
I probably won't bother with an interface. This is more an exercise
for me with Qt/SQL. Is anyone in particular maintaining MythMusic?
The UI will require its own playlist objects for the new features.
Lemme know of any questions/bugs,
PS. Is there an indent.pro for the codebase?
#if thor /* Feb 20, 02:55 */
> Please include lots of explanation. Isaac will probably be able to
> follow anything you come up with, but myself (and others) may not be
> able to keep up without some hand holding.
#endif /* mythtv at lamedomainname.com */
-------------- next part --------------
diff -Naur mythmusic/mythmusic/metadata.cpp mymythmusic/mythmusic/metadata.cpp
--- mythmusic/mythmusic/metadata.cpp 2003-02-09 11:33:48.000000000 -0500
+++ mymythmusic/mythmusic/metadata.cpp 2003-02-20 01:47:59.000000000 -0500
@@ -84,6 +84,8 @@
title = data;
else if (field == "genre")
genre = data;
+ else if (field == "filename")
+ filename = data;
else if (field == "year")
year = data.toInt();
else if (field == "tracknum")
diff -Naur mythmusic/mythmusic/playlist.cpp mymythmusic/mythmusic/playlist.cpp
--- mythmusic/mythmusic/playlist.cpp 2002-10-09 02:10:04.000000000 -0400
+++ mymythmusic/mythmusic/playlist.cpp 2003-02-20 04:30:17.000000000 -0500
@@ -2,68 +2,167 @@
void LoadDefaultPlaylist(QSqlDatabase *db, QValueList<Metadata> &playlist)
{
- QString thequery = "SELECT songlist FROM musicplaylist WHERE "
- "name = \"default_playlist_storage\";";
+ QSqlQuery query;
- QSqlQuery query = db->exec(thequery);
-
- if (query.isActive() && query.numRowsAffected() > 0)
- {
- query.next();
-
- QString songlist = query.value(0).toString();
-
- QStringList list = QStringList::split(",", songlist);
-
- QStringList::iterator it = list.begin();
- for (; it != list.end(); it++)
- {
- unsigned int id = QString(*it).toUInt();
-
- Metadata mdata;
-
- mdata.setID(id);
-
- mdata.fillDataFromID(db);
-
- if (mdata.Filename() != "")
- playlist.push_back(mdata);
- }
- }
+ // check to see if we've got fields from the newer table form
+ query = QSqlQuery("show fields from musicplaylist like 'intid'", db);
+ if (query.size() <= 0)
+ {
+ // it's the old table schema, so load the playlist the old way
+ query = QSqlQuery("SELECT songlist FROM musicplaylist WHERE "
+ "name = \"default_playlist_storage\";", db);
+ if (query.isActive() && query.size() > 0)
+ {
+ query.next();
+
+ QString songlist = query.value(0).toString();
+
+ QStringList list = QStringList::split(",", songlist);
+
+ QStringList::iterator it = list.begin();
+ for (; it != list.end(); it++)
+ {
+ unsigned int id = QString(*it).toUInt();
+
+ Metadata mdata;
+
+ mdata.setID(id);
+
+ mdata.fillDataFromID(db);
+
+ if (mdata.Filename() != "")
+ playlist.push_back(mdata);
+ }
+ }
+ }
+ // load from newer table schema, since we've verified format above
+ else
+ {
+ // we fully qualify column names in joins so that new columns
+ // added later will not create any abiguity
+ // playlist ordering is supported by the schema, not the FE.
+ // we ignore (here) items in the playlist without filenames.
+ query = QSqlQuery("SELECT musicmetadata.intid,musicmetadata.artist,"
+ "musicmetadata.title,musicmetadata.album,musicmetadata.genre,"
+ "musicmetadata.year,musicmetadata.length,musicmetadata.tracknum,"
+ "musicmetadata.filename "
+ "FROM musicplaylisthead left join musicplaylist using (playlistid) "
+ "left join musicmetadata using (intid) "
+ "WHERE musicplaylisthead.title = \"Default\" "
+ "AND musicmetadata.filename != \"\" "
+ "ORDER BY musicplaylist.playorder;", db);
+ if (query.isActive() && query.size() > 0)
+ {
+ Metadata mdata;
+ while(query.next())
+ {
+ mdata.setID( query.value(0).asUInt());
+ mdata.setField("artist", query.value(1).asString());
+ mdata.setField("title", query.value(2).asString());
+ mdata.setField("album", query.value(3).asString());
+ mdata.setField("genre", query.value(4).asString());
+ mdata.setField("year", query.value(5).asString());
+ mdata.setField("length", query.value(6).asString());
+ mdata.setField("tracknum", query.value(7).asString());
+ mdata.setField("filename", query.value(8).asString());
+
+ playlist.push_back(mdata);
+ }
+ }
+ }
}
void SaveDefaultPlaylist(QSqlDatabase *db, QValueList<Metadata> &playlist)
{
- QString playliststring;
-
- QValueList<Metadata>::iterator it = playlist.begin();
+ QSqlQuery query;
- bool first = true;
- for (; it != playlist.end(); it++)
- {
- unsigned int id = (*it).ID();
-
- if (!first)
- playliststring += ",";
- playliststring += QString("%1").arg(id);
- first = false;
- }
-
- QString thequery = "SELECT NULL FROM musicplaylist WHERE name = "
- "\"default_playlist_storage\";";
- QSqlQuery query = db->exec(thequery);
-
- if (query.isActive() && query.numRowsAffected() > 0)
- {
- thequery = QString("UPDATE musicplaylist SET songlist = \"%1\" WHERE "
- "name = \"default_playlist_storage\";")
- .arg(playliststring);
- }
- else
- {
- thequery = QString("INSERT musicplaylist (name,songlist) "
- "VALUES(\"default_playlist_storage\",\"%1\");")
- .arg(playliststring);
- }
- query = db->exec(thequery);
+ query = QSqlQuery("show fields from musicplaylist like 'intid'", db);
+ if (query.size() <= 0)
+ {
+ // here we know that the database schema is out-of-date
+ // so we get rid of all of it and put in our new schema
+ query = QSqlQuery("DROP TABLE IF EXISTS musicplaylisthead;", db);
+ if (!query.isActive())
+ {
+ return;
+ }
+ query = QSqlQuery("CREATE TABLE musicplaylisthead ( "
+ "playlistid tinyint unsigned NOT NULL auto_increment, "
+ "title char(64) NOT NULL default '', "
+ "description char(255) NOT NULL default '', "
+ "PRIMARY KEY (playlistid) );", db);
+ if (!query.isActive())
+ {
+ return;
+ }
+ // setup the default playlist, since it's the only one we
+ // support!
+ query = QSqlQuery("INSERT musicplaylisthead (playlistid,title,"
+ "description) values (1, 'Default',"
+ "'This is the default playlist.');", db);
+ if (!query.isActive())
+ {
+ return;
+ }
+ query = QSqlQuery("DROP TABLE IF EXISTS musicplaylist;", db);
+ if (!query.isActive())
+ {
+ return;
+ }
+ // unique indexes will prevent violation of the schema
+ query = QSqlQuery("CREATE TABLE musicplaylist ( "
+ "playlistid tinyint unsigned not null, "
+ "playorder smallint unsigned not null, "
+ "intid int unsigned not null, "
+ "unique index(playlistid, playorder), "
+ "unique index(intid, playlistid));", db);
+ if (!query.isActive())
+ {
+ return;
+ }
+ }
+ // no caching, so we have to write out the entire playlist
+ // each time we save it. since there's only one, we know
+ // its playlistid will be '1'.
+ query = QSqlQuery("DELETE from musicplaylist "
+ "WHERE playlistid = 1;", db);
+ if (!query.isActive())
+ {
+ return;
+ }
+ QValueList<Metadata>::iterator it = playlist.begin();
+ bool first = true;
+ QString values = "";
+ for (unsigned int i=0; it != playlist.end(); it++, i++)
+ {
+ if (!first)
+ values += ", ";
+ values += "(" + QString::number(1) + ", "; // playlistid
+ values += QString::number(i+1) + ", "; // playorder
+ values += QString::number((*it).ID()) + ")"; // intid (track)
+ first = false;
+
+ // we "flush" a bulk-insert out to the database every 50
+ // elements. the only limitation on this figure is the
+ // mysql packet size and the load spikes on the database.
+ if (!(i+1 % 50))
+ {
+ query = QSqlQuery("INSERT musicplaylist (playlistid, "
+ "playorder, intid) values " + values + ";", db);
+ if (!query.isActive())
+ {
+ return;
+ }
+ values = "";
+ first = true;
+ }
+ }
+ // flush any remaining inserts; this gets executed any
+ // time the number of items in the playlist isn't a multiple
+ // of the constant above.
+ if(!first)
+ {
+ query = QSqlQuery("INSERT musicplaylist (playlistid, "
+ "playorder, intid) values " + values + ";", db);
+ }
}
More information about the mythtv-dev
mailing list