[mythtv] sql code
Andy Davidoff
dert at pobox.com
Thu Feb 20 15:33:07 EST 2003
Here's a patch that can't read prior playlists and which includes
a SQL script in UPGRADING style. I ran it through the indent.pro
I posted.
#if Andy Davidoff /* Feb 20, 15:13 */
> So, I should have just said "to hell with your playlists" and
> wiped the playlist tables with a SQL script?
#endif /* dert at pobox.com */
-------------- next part --------------
diff -Naur mythmusic/musicdb/playlists.sql mymythmusic/musicdb/playlists.sql
--- mythmusic/musicdb/playlists.sql 1969-12-31 19:00:00.000000000 -0500
+++ mymythmusic/musicdb/playlists.sql 2003-02-20 15:28:37.000000000 -0500
@@ -0,0 +1,19 @@
+USE mythconverg;
+DROP TABLE IF EXISTS musicplaylist;
+CREATE TABLE musicplaylist (
+ playlistid tinyint(3) unsigned NOT NULL default '0',
+ playorder smallint(5) unsigned NOT NULL default '0',
+ intid int(10) unsigned NOT NULL default '0',
+ UNIQUE KEY intid (intid,playlistid),
+ UNIQUE KEY playlistid (playlistid,playorder)
+) TYPE=MyISAM;
+
+DROP TABLE IF EXISTS musicplaylisthead;
+CREATE TABLE musicplaylisthead (
+ playlistid tinyint(3) unsigned NOT NULL auto_increment,
+ title char(64) NOT NULL default '',
+ description char(255) NOT NULL default '',
+ PRIMARY KEY (playlistid)
+) TYPE=MyISAM;
+
+INSERT INTO musicplaylisthead values (1, 'Default', 'A playlist shared by all front-ends.');
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 15:20:08.000000000 -0500
@@ -2,68 +2,87 @@
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);
- }
- }
+ // 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);
+ // 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 1000
+ // elements. the only limitation on this figure is the
+ // mysql packet size and the load spikes on the database.
+ if (!(i+1 % 1000))
+ {
+ 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