Enclosed is a patch that vastly improves the performance of importing crates
when the tracks are already in the database.
I ran into this problem while trying to build crates for various sub-sets of
already-imported tracks.
All it does is detect when tracks referred to during a crate-import already
exist in the database, and avoids trying to add them again.
Before, an .m3u file with ~1900 tracks in it, imported in about 5 minutes.
Now, it's about 2 seconds.
So I think this is a worthy change :-)
I was thinking about doing the same for the crate_tracks database, but I wasn't
sure if that'd be more optimal. Right now, CrateDAO::addTracksToCrate() logs
an error every time there's a duplicate crate/track pair. However,
crate_tracks is also set up so that each crate/track pair has to be unique. So
it might be faster to create a temporary table with the crates/tracks to add,
then do an SQL query to get only the crate/track pairs that aren't already in
crate_tracks, and only insert those, solely because of the "unique" constraint
on crate_tracks. Besides, the same test (i.e. the ~1900-track .m3u file), run
when the playlist already contains those tracks, still runs in ~2 seconds, so
maybe it's no big deal. Any opinions on this?
BTW, the performance problem I found in CrateFeature::slotImportPlaylist()
(i.e. calling addTrack() in a loop, instead of just calling addTracks() once)
also exists in BasePlaylistFeature::slotImportPlaylist(). Enclosed is another
patch to fix that.
Steven Boswell
--- mixxx-patched/src/library/cratetablemodel.cpp 2013-04-07 19:41:28.496946000 -0700
+++ mixxx-build/src/library/cratetablemodel.cpp 2013-04-21 09:48:51.269970571 -0700
@@ -93,7 +93,8 @@
}
}
-int CrateTableModel::addTracks(const QModelIndex& index, QList<QString> locations) {
+int CrateTableModel::addTracks(const QModelIndex& index,
+ const QList<QString> &locations) {
Q_UNUSED(index);
// If a track is dropped but it isn't in the library, then add it because
// the user probably dropped a file from outside Mixxx into this playlist.
--- mixxx-patched/src/library/cratetablemodel.h 2013-04-07 19:41:28.496946000 -0700
+++ mixxx-build/src/library/cratetablemodel.h 2013-04-21 09:48:55.862554265 -0700
@@ -31,7 +31,8 @@
virtual void removeTracks(const QModelIndexList& indices);
virtual bool addTrack(const QModelIndex& index, QString location);
// Returns the number of unsuccessful track additions
- virtual int addTracks(const QModelIndex& index, QList <QString> locations);
+ virtual int addTracks(const QModelIndex& index,
+ const QList <QString> &locations);
virtual void moveTrack(const QModelIndex& sourceIndex,
const QModelIndex& destIndex);
TrackModel::CapabilitiesFlags getCapabilities() const;
--- mixxx-patched/src/library/dao/trackdao.cpp 2013-04-07 19:41:28.496946000 -0700
+++ mixxx-build/src/library/dao/trackdao.cpp 2013-04-21 09:49:56.637278419 -0700
@@ -523,26 +523,96 @@
addTracksFinish();
}
-QList<int> TrackDAO::addTracks(QList<QFileInfo> fileInfoList, bool unremove) {
+QList<int> TrackDAO::addTracks(const QList<QFileInfo> &fileInfoList,
+ bool unremove) {
+ QSqlQuery query(m_database);
QList<int> trackIDs;
- TrackInfoObject* pTrack;
+ TrackInfoObject* pTrack;
+ // Prepare to add tracks to the database.
+ // This also begins an SQL transaction.
addTracksPrepare();
- //create the list of TrackInfoObjects from the fileInfoList
- QMutableListIterator<QFileInfo> it(fileInfoList);
- while (it.hasNext()) {
- QFileInfo& info = it.next();
- pTrack = new TrackInfoObject(info);
+ // Create a temporary database of the paths of all the imported tracks.
+ query.prepare (QString ("CREATE TEMP TABLE playlist_import "
+ "(location varchar (512))"));
+ if (!query.exec()) {
+ LOG_FAILED_QUERY(query);
+ goto err0;
+ }
+
+ // All all the track paths to this database.
+ query.prepare (QString ("INSERT INTO playlist_import (location) "
+ "VALUES (:location)"));
+ foreach (const QFileInfo &rFileInfo, fileInfoList) {
+ query.bindValue (":location", rFileInfo.absoluteFilePath());
+ if (!query.exec()) {
+ LOG_FAILED_QUERY(query);
+ goto err1;
+ }
+ }
+
+ // Get the IDs of all tracks that are already in the database.
+ query.prepare (QString ("SELECT id FROM playlist_import, track_locations "
+ "WHERE playlist_import.location = track_locations.location"));
+ if (!query.exec()) {
+ LOG_FAILED_QUERY(query);
+ goto err1;
+ }
+ while (query.next()) {
+ int trackId = query.value(query.record().indexOf("id")).toInt();
+ trackIDs.append (trackId);
+ }
+
+ // If imported-playlist tracks are to be unremoved, do that for all playlist
+ // tracks that were already in the database.
+ if (unremove)
+ {
+ QStringList idStringList;
+ foreach (int id, trackIDs)
+ idStringList.append(QString::number(id));
+ query.prepare (QString ("UPDATE library SET mixxx_deleted=0 "
+ "WHERE id in (%1) AND mixxx_deleted=1")
+ .arg (idStringList.join (",")));
+ if (!query.exec()) {
+ LOG_FAILED_QUERY(query);
+ goto err1;
+ }
+ }
+
+ // Any tracks not already in the database need to be added.
+ query.prepare (QString ("SELECT location FROM playlist_import "
+ "WHERE NOT EXISTS (SELECT location FROM track_locations "
+ "WHERE playlist_import.location = track_locations.location)"));
+ if (!query.exec()) {
+ LOG_FAILED_QUERY(query);
+ goto err1;
+ }
+ while (query.next()) {
+ QString strFilePath = query.value(query.record().indexOf("location"))
+ .toString();
+ pTrack = new TrackInfoObject(QFileInfo (strFilePath));
addTracksAdd(pTrack, unremove);
int trackID = pTrack->getId();
- if (trackID >= 0) {
+ if (trackID >= 0)
trackIDs.append(trackID);
- }
delete pTrack;
}
+err1:
+
+ // Drop the temporary playlist-import table.
+ query.prepare (QString ("DROP TABLE IF EXISTS playlist_import"));
+ if (!query.exec()) {
+ LOG_FAILED_QUERY(query);
+ }
+
+err0:
+
+ // Finish adding tracks to the database.
addTracksFinish();
+
+ // Return the list of track IDs added to the database.
return trackIDs;
}
@@ -559,7 +629,7 @@
LOG_FAILED_QUERY(query);
}
- // This is signal is received by beasetrackcache to remove the tracks from cache
+ // This signal is received by basetrackcache to remove the tracks from cache
QSet<int> tracksRemovedSet = QSet<int>::fromList(ids);
emit(tracksRemoved(tracksRemovedSet));
}
--- mixxx-patched/src/library/dao/trackdao.h 2013-04-07 19:41:28.496946000 -0700
+++ mixxx-build/src/library/dao/trackdao.h 2013-04-21 09:49:03.310500860 -0700
@@ -83,7 +83,7 @@
void addTracksPrepare();
bool addTracksAdd(TrackInfoObject* pTrack, bool unremove);
void addTracksFinish();
- QList<int> addTracks(QList<QFileInfo> fileInfoList, bool unremove);
+ QList<int> addTracks(const QList<QFileInfo> &fileInfoList, bool unremove);
void hideTracks(QList<int> ids);
void purgeTracks(QList<int> ids);
void unhideTracks(QList<int> ids);
--- mixxx/src/library/baseplaylistfeature.cpp 2013-04-07 19:41:28.496946000 -0700
+++ mixxx-build/src/library/baseplaylistfeature.cpp 2013-04-21 10:54:42.800189742 -0700
@@ -306,9 +306,7 @@
QList<QString> entries = playlist_parser->parse(playlist_file);
// Iterate over the List that holds URLs of playlist entires
- for (int i = 0; i < entries.size(); ++i) {
- m_pPlaylistTableModel->addTrack(QModelIndex(), entries[i]);
- }
+ m_pPlaylistTableModel->addTracks(QModelIndex(), entries);
// delete the parser object
if (playlist_parser) {
------------------------------------------------------------------------------
Precog is a next-generation analytics platform capable of advanced
analytics on semi-structured data. The platform includes APIs for building
apps and a phenomenal toolset for data science. Developers can use
our toolset for easy data analysis & visualization. Get a free account!
http://www2.precog.com/precogplatform/slashdotnewsletter
_______________________________________________
Get Mixxx, the #1 Free MP3 DJ Mixing software Today
http://mixxx.org
Mixxx-devel mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/mixxx-devel
