2011-12-13, 20:58
I have been looking to add play counts from the XBMC database back into my main iTunes library. Principally it doesn't look too challenging - I will use 1 query to dump all records with a iTimesPlayed > 0 to a text file, which will be the base of a separate AppleScript (on OS X) to be used to import back into iTunes.
the syntax will be along the lines of:
.output xbmc_music_lastplayed_toitunes.txt
SELECT s.strTitle, art.strArtist, a.strAlbum, s.iTimesPlayed, s.lastplayed
FROM song as s, album as a, artist as art
WHERE s.idAlbum = a.idAlbum
AND s.idArtist = art.idArtist
AND s.iTimesPlayed > 0
however the logic will need to reset the play count to zero after exporting/dumping, but retaining the "lastplayed" field so it won't look like a new track (for other playlists, though i haven't tested that this actually works either)
how do i complete this for each line of the above query, so that once dumped to a text file, i can issue a "UPDATE song SET iTimesPlayed = 0 WHERE idSong = x"...?
any help or pointers to the solution or sqlite with bash stuff would be great too
cheers
jingo_man
the syntax will be along the lines of:
.output xbmc_music_lastplayed_toitunes.txt
SELECT s.strTitle, art.strArtist, a.strAlbum, s.iTimesPlayed, s.lastplayed
FROM song as s, album as a, artist as art
WHERE s.idAlbum = a.idAlbum
AND s.idArtist = art.idArtist
AND s.iTimesPlayed > 0
however the logic will need to reset the play count to zero after exporting/dumping, but retaining the "lastplayed" field so it won't look like a new track (for other playlists, though i haven't tested that this actually works either)
how do i complete this for each line of the above query, so that once dumped to a text file, i can issue a "UPDATE song SET iTimesPlayed = 0 WHERE idSong = x"...?
any help or pointers to the solution or sqlite with bash stuff would be great too
cheers
jingo_man