[Librefm-commits] [1149] script for backfilling stids and lost tracks

[Clint Adams]

Revision: 1149
          http://svn.sv.gnu.org/viewvc/?view=rev&root=librefm&revision=1149
Author:   clint
Date:     2009-05-03 03:38:50 +0000 (Sun, 03 May 2009)
Log Message:
-----------
script for backfilling stids and lost tracks

Added Paths:
-----------
    trunk/scripts/setstids

Added: trunk/scripts/setstids
===================================================================
--- trunk/scripts/setstids                              (rev 0)
+++ trunk/scripts/setstids      2009-05-03 03:38:50 UTC (rev 1149)
@@ -0,0 +1,66 @@
+#!/usr/bin/env python
+
+import sys;
+import adodb;
+
+class CleanUp:
+
+       def __init__(self, dbname, username):
+               self.conn = adodb.NewADOConnection('postgres')
+               self.conn.Connect('user=%s dbname=%s' % (username, dbname))
+
+       def doit(self):
+
+               self.cursor = self.conn.Execute('SELECT * from Scrobbles WHERE 
stid is NULL');
+
+               while not self.cursor.EOF:
+                       row = self.cursor.GetRowAssoc(0)
+                       artist = row['artist']
+                       album = row['album']
+                       track = row['track']
+                       mbid = row['mbid']
+                       username = row['username']
+                       time = row['time']
+                       stid = self.lookupStid(artist,album,track,mbid)
+                       if not stid:
+                               tid = self.lookupTrack(artist, album, track, 
mbid)
+                               if tid:
+                                       print "Adding stid for %s/%s/%s/%s/%s" 
% (stid,artist,album,track,mbid)
+                                       self.acursor = 
self.conn.Execute("INSERT INTO Scrobble_Track (artist,album,name,mbid,track) 
VALUES(%(artist)s,%(album)s,%(name)s,%(mbid)s,%(track)s)", {'artist': artist, 
'album': album, 'name': track, 'mbid': mbid, 'track': tid})
+                               else:
+                                       print "Adding tid for %s/%s/%s/%s" % 
(artist,album,track,mbid)
+                                       self.acursor = 
self.conn.Execute("INSERT INTO Track (artist,album,name,mbid) 
VALUES(%(artist)s,%(album)s,%(name)s,%(mbid)s)", {'artist': artist, 'album': 
album, 'name': track, 'mbid': mbid})
+                       else:
+                               print "Setting %d to match %s/%s/%s/%s" % 
(stid,artist,album,track,mbid)
+                               self.scursor = self.conn.Execute("UPDATE 
Scrobbles SET stid = %(stid)s WHERE username = %(username)s AND track = 
%(track)s AND artist = %(artist)s AND time = %(time)s AND stid IS NULL", 
{'stid': stid, 'username': username, 'track': track, 'artist': artist, 'time': 
time})
+                       self.cursor.MoveNext()
+
+       def lookupStid(self, artist, album, track, mbid):
+               self.lucursor = self.conn.Execute('SELECT * from Scrobble_Track 
WHERE lower(artist) = lower(%(artist)s) AND lower(album) = lower(%(album)s) AND 
lower(name) = lower(%(track)s) AND lower(mbid) = lower(%(mbid)s)', {'artist': 
artist, 'album': album, 'track': track, 'mbid': mbid});
+               if not self.lucursor.EOF:
+                       lrow = self.lucursor.GetRowAssoc(0)
+                       return lrow['id']
+               else:
+                       return None
+
+       def lookupTrack(self, artist, album, track, mbid):
+               self.tucursor = self.conn.Execute('SELECT * from Track WHERE 
lower(artist) = lower(%(artist)s) AND lower(album) = lower(%(album)s) AND 
lower(name) = lower(%(track)s) AND lower(mbid) = lower(%(mbid)s)', {'artist': 
artist, 'album': album, 'track': track, 'mbid': mbid});
+               if not self.tucursor.EOF:
+                       trow = self.tucursor.GetRowAssoc(0)
+                       return trow['id']
+               else:
+                       return None
+
+       def close(self):
+               self.cursor.Close()
+               self.conn.Close() 
+
+if __name__ == "__main__":
+
+       if len(sys.argv) != 3:
+               print "Usage: setstids <pgsql dbname> <pgsql username>"
+               sys.exit(1)
+       
+       cleaner = CleanUp(sys.argv[1], sys.argv[2])
+       cleaner.doit()
+       cleaner.close()


Property changes on: trunk/scripts/setstids
___________________________________________________________________
Added: svn:executable
   + *