From caa1c3ccdf94ee20140b3964aab0ad3058e03699 Mon Sep 17 00:00:00 2001 From: Michaël Ball Date: Sun, 7 Feb 2016 15:28:56 +0000 Subject: Create test framework --- models/album.py | 113 +++++++------- models/artist.py | 124 ++++++++------- models/base.py | 10 ++ models/track.py | 456 ++++++++++++++++++++++++++++++++----------------------- 4 files changed, 404 insertions(+), 299 deletions(-) create mode 100644 models/base.py (limited to 'models') diff --git a/models/album.py b/models/album.py index 0d7cd54..96bea81 100644 --- a/models/album.py +++ b/models/album.py @@ -1,15 +1,17 @@ from common import utils from db.db_manager import DbManager +from models.base import BaseModel -class Album(): - def __init__(self, id=None, **kwargs): - if id is not None: - db = DbManager() - cursor = db.cursor() +class Album(BaseModel): - for row in cursor.execute("SELECT * FROM album WHERE id = ?", - (id,)): + def __init__(self, id=None, db=None, **kwargs): + if db: + self.db = db + + if id is not None: + for row in self.db.execute("SELECT * FROM album WHERE id = ?", + (id,)): setattr(self, "id", id) setattr(self, "name", row[1]) setattr(self, "date", row[2]) @@ -18,26 +20,32 @@ class Album(): setattr(self, key, value) def delete(self): - db = DbManager() - cursor = db.cursor() - for track in self.tracks: track.delete() - cursor.execute("BEGIN TRANSACTION") + with self.db.conn: + delete_album = "DELETE FROM album WHERE id = ?" + self.db.execute(delete_album, (self.id,)) - delete_sql = "DELETE FROM album WHERE id = ?" - cursor.execute(delete_sql, (self.id,)) + delete_track_rel = "DELETE FROM album_track WHERE album_id = ?" + self.db.execute(delete_track_rel, (self.id,)) - delete_track_rel_sql = "DELETE FROM album_track WHERE album_id = ?" - cursor.execute(delete_track_rel_sql, (self.id,)) + delete_artist_rel = "DELETE FROM album_artist WHERE album_id = ?" + self.db.execute(delete_artist_rel, (self.id,)) - delete_artist_rel_sql = "DELETE FROM album_artist WHERE album_id = ?" - cursor.execute(delete_artist_rel_sql, (self.id,)) + return True - cursor.execute("COMMIT TRANSACTION") + @property + def db(self): + try: + return self._db + except AttributeError: + self._db = DbManager() + return self._db - return True + @db.setter + def db(self, db): + self._db = db @property def artists(self): @@ -46,15 +54,13 @@ class Album(): if not hasattr(self, "_artists"): setattr(self, "_artists", []) - db = DbManager() - cursor = db.cursor() - - for row in cursor.execute("""SELECT artist.* FROM artist INNER JOIN - album_artist ON artist.id = - album_artist.artist_id WHERE album_id = ? - ORDER BY name ASC""", (self.id,)): - artist = Artist(id=row[0], name=row[1], sortname=row[2], - musicbrainz_artistid=row[3]) + for row in self.db.execute("SELECT artist.* FROM artist INNER " + "JOIN album_artist ON artist.id = " + "album_artist.artist_id WHERE " + "album_id = ? ORDER BY name ASC", + (self.id,)): + artist = Artist(id=row[0], db=self.db, name=row[1], + sortname=row[2], musicbrainz_artistid=row[3]) self._artists.append(artist) return self._artists @@ -66,16 +72,16 @@ class Album(): if not hasattr(self, "_tracks"): setattr(self, "_tracks", []) - db = DbManager() - cursor = db.cursor() - - for row in cursor.execute("""SELECT track.* FROM track - INNER JOIN album_track ON track.id = - album_track.track_id WHERE album_id = ? - ORDER BY tracknumber ASC""", (self.id,)): + for row in self.db.execute("SELECT track.* FROM track INNER " + "JOIN album_track ON track.id = " + "album_track.track_id WHERE " + "album_id = ? ORDER BY tracknumber " + "ASC", (self.id,)): - track = Track(id=row[0], tracknumber=row[1], name=row[2], - grouping=row[3], filename=row[4]) + track = Track(id=row["id"], db=self.db, + tracknumber=row["tracknumber"], + name=row["name"], grouping=row["grouping"], + filename=row["filename"]) self._tracks.append(track) return self._tracks @@ -89,18 +95,16 @@ class Album(): dirty_attributes[attr] = value if len(dirty_attributes) > 0: - db = DbManager() - cursor = db.cursor() - set_clause = utils.update_clause_from_dict(dirty_attributes) dirty_attributes[id] = self.id sql = " ".join(("UPDATE album"), set_clause, "WHERE id = :id") - cursor.execute(sql, dirty_attributes) - def search(order="album.id", direction="ASC", limit=None, - offset=None, **search_params): + with self.db.conn: + self.db.execute(sql, dirty_attributes) + + def search(db=None, **search_params): """Find an album with the given params Args: @@ -110,8 +114,8 @@ class Album(): """ albums = [] - db = DbManager() - cursor = db.cursor() + if not db: + db = DbManager() # unpack search params where_params = {} @@ -132,20 +136,21 @@ class Album(): result = None if where_clause: statement = " ".join(("SELECT * FROM album", where_clause)) - result = cursor.execute(statement, value_params) + result = db.execute(statement, value_params) else: - result = cursor.execute("SELECT * FROM album") + result = db.execute("SELECT * FROM album") for row in result: albums.append( - Album(id=row[0], name=row[1], date=row[2]) + Album(id=row["id"], db=db, name=row["name"], date=row["date"]) ) return albums - def all(order="album.id", direction="ASC", limit=None, offset=None): - db = DbManager() - cursor = db.cursor() + def all(db=None, order="album.id", direction="ASC", limit=None, + offset=None): + if not db: + db = DbManager() albums = [] @@ -156,13 +161,13 @@ class Album(): if limit is not None and offset is not None: select_string = " ".join((select_string, - "LIMIT %s OFFSET %s" % (limit, offset))) + "LIMIT %s OFFSET %s" % (limit, offset))) - result = cursor.execute(select_string) + result = db.execute(select_string) for row in result: albums.append( - Album(id=row[0], name=row[1], date=row[2]) + Album(id=row["id"], db=db, name=row["name"], date=row["date"]) ) return albums diff --git a/models/artist.py b/models/artist.py index fee081c..a76b2ee 100644 --- a/models/artist.py +++ b/models/artist.py @@ -1,44 +1,50 @@ from common import utils from db.db_manager import DbManager +from models.base import BaseModel -class Artist: - def __init__(self, id=None, **kwargs): +class Artist(BaseModel): + + def __init__(self, id=None, db=None, **kwargs): + if db: + self.db = db + if id is not None: - db = DbManager() - cursor = db.cursor() - - for row in cursor.execute("SELECT * FROM artist WHERE id = ?", - (id,)): - setattr(self, "id", id) - setattr(self, "name", row[1]) - setattr(self, "sortname", row[2]) - setattr(self, "musicbrainz_artistid", row[3]) + for row in self.db.execute("SELECT * FROM artist WHERE id = ?", + (id,)): + for key in ["id", "name", "sortname", "musicbrainz_artistid"]: + setattr(self, key, row[key]) else: for (key, value) in kwargs.items(): setattr(self, key, value) def delete(self): - db = DbManager() - cursor = db.cursor() - for album in self.albums: album.delete() - cursor.execute("BEGIN TRANSACTION") + with self.db.conn: + delete_artist = "DELETE FROM artist WHERE id = ?" + self.db.execute(delete_artist, (self.id,)) - delete_sql = "DELETE FROM artist WHERE id = ?" - cursor.execute(delete_sql, (self.id,)) + delete_track_rel = "DELETE FROM artist_track WHERE artist_id = ?" + self.db.execute(delete_track_rel, (self.id,)) - delete_track_rel_sql = "DELETE FROM artist_track WHERE artist_id = ?" - cursor.execute(delete_track_rel_sql, (self.id,)) + delete_album_rel = "DELETE FROM album_artist WHERE artist_id = ?" + self.db.execute(delete_album_rel, (self.id,)) - delete_album_rel_sql = "DELETE FROM album_artist WHERE artist_id = ?" - cursor.execute(delete_album_rel_sql, (self.id,)) + return True - cursor.execute("COMMIT TRANSACTION") + @property + def db(self): + try: + return self._db + except AttributeError: + self._db = DbManager() + return self._db - return True + @db.setter + def db(self, db): + self._db = db @property def tracks(self): @@ -47,16 +53,16 @@ class Artist: if not hasattr(self, "_tracks"): setattr(self, "_tracks", []) - db = DbManager() - cursor = db.cursor() - - for row in cursor.execute("""SELECT track.* FROM track - INNER JOIN artist_track ON track.id = - artist_track.track_id WHERE artist_id = ? - ORDER BY name ASC""", (self.id,)): + for row in self.db.execute("SELECT track.* FROM track INNER " + "JOIN artist_track ON track.id = " + "artist_track.track_id WHERE " + "artist_id = ? ORDER BY name ASC", + (self.id,)): - track = Track(id=row[0], tracknumber=row[1], name=row[2], - grouping=row[3], filename=row[4]) + track = Track(id=row["id"], db=self.db, + tracknumber=row["tracknumber"], name=row["name"], + grouping=row["grouping"], + filename=row["filename"]) self._tracks.append(track) return self._tracks @@ -68,14 +74,13 @@ class Artist: if not hasattr(self, "_albums"): setattr(self, "_albums", []) - db = DbManager() - cursor = db.cursor() - - for row in cursor.execute("""SELECT album.* FROM album - INNER JOIN album_artist ON album.id = - album_artist.album_id WHERE artist_id = ? - ORDER BY date ASC""", (self.id,)): - album = Album(id=row[0], name=row[1], date=row[2]) + for row in self.db.execute("SELECT album.* FROM album INNER " + "JOIN album_artist ON album.id = " + "album_artist.album_id WHERE " + "artist_id = ? ORDER BY date ASC", + (self.id,)): + album = Album(id=row["id"], db=self.db, name=row["name"], + date=row["date"]) self._albums.append(album) return self._albums @@ -89,17 +94,16 @@ class Artist: dirty_attributes[attr] = value if len(dirty_attributes) > 0: - db = DbManager() - cursor = db.cursor() - set_clause = utils.update_clause_from_dict(dirty_attributes) dirty_attributes[id] = self.id sql = " ".join(("UPDATE artist"), set_clause, "WHERE id = :id") - cursor.execute(sql, dirty_attributes) - def search(**search_params): + with self.db.conn: + self.db.execute(sql, dirty_attributes) + + def search(db=None, **search_params): """Find an artist with the given params Args: @@ -109,8 +113,8 @@ class Artist: """ artists = [] - db = DbManager() - cursor = db.cursor() + if not db: + db = DbManager() # unpack search params where_params = {} @@ -124,21 +128,24 @@ class Artist: result = [] if where_clause: statement = " ".join(("SELECT * FROM artist", where_clause)) - result = cursor.execute(statement, value_params) + result = db.execute(statement, value_params) else: - result = cursor.execute("SELECT * FROM artist") + result = db.execute("SELECT * FROM artist") for row in result: artists.append( - Artist(id=row[0], name=row[1], sortname=row[2], - musicbrainz_artistid=row[3]) + Artist(id=row["id"], db=db, name=row["name"], + sortname=row["sortname"], + musicbrainz_artistid=row["musicbrainz_artistid"]) ) return artists - def all(order="sortname", direction="ASC", limit=None, offset=None): - db = DbManager() - cursor = db.cursor() + def all(db=None, order="sortname", direction="ASC", limit=None, + offset=None): + if not db: + db = DbManager() + artists = [] select_string = "SELECT * FROM artist ORDER BY %s %s" % (order, @@ -146,14 +153,15 @@ class Artist: if limit is not None and offset is not None: select_string = " ".join((select_string, - "LIMIT %s OFFSET %s" % (limit, offset))) + "LIMIT %s OFFSET %s" % (limit, offset))) - result = cursor.execute(select_string) + result = db.execute(select_string) for row in result: artists.append( - Artist(id=row[0], name=row[1], sortname=row[2], - musicbrainz_artistid=row[3]) + Artist(id=row["id"], db=db, name=row["name"], + sortname=row["sortname"], + musicbrainz_artistid=row["musicbrainz_artistid"]) ) return artists diff --git a/models/base.py b/models/base.py new file mode 100644 index 0000000..fd40001 --- /dev/null +++ b/models/base.py @@ -0,0 +1,10 @@ +class BaseModel(): + + def as_dict(self): + this_dict = {} + + for k in self.__dict__.keys(): + if k != "_db": + this_dict[k] = getattr(self, k) + + return this_dict diff --git a/models/track.py b/models/track.py index 688f6ff..e0905e6 100644 --- a/models/track.py +++ b/models/track.py @@ -5,78 +5,120 @@ from common import utils from db.db_manager import DbManager from models.artist import Artist from models.album import Album +from models.base import BaseModel logging.basicConfig(format="%(asctime)s %(message)s", level=logging.DEBUG) -class Track: +class Track(BaseModel): - def __init__(self, id=None, **kwargs): + def __init__(self, id=None, db=None, **kwargs): + if db: + self.db = db - setattr(self, "__data", {}) + self.__data = {} if id is not None: - db = DbManager() - cursor = db.cursor() - - for row in cursor.execute("""SELECT * FROM track WHERE id = ?""", - (id,)): - setattr(self, "id", row[0]) - setattr(self, "tracknumber", row[1]) - setattr(self, "name", row[2]) - setattr(self, "grouping", row[3]) - setattr(self, "filename", row[4]) + for row in self.db.execute("SELECT * FROM track WHERE id = ?", + (id,)): + for key in ["id", "tracknumber", "name", "grouping", + "filename"]: + setattr(self, key, row[key]) + self.__data[key] = row[key] else: for (key, value) in kwargs.items(): setattr(self, key, value) self.__data[key] = value def delete(self): - db = DbManager() - cursor = db.cursor() delete_sql = "DELETE FROM track WHERE id = ?" - cursor.execute(delete_sql, (self.id,)) + + with self.db.conn: + self.db.execute(delete_sql, (self.id,)) + + # If there is an old album, remove it if it no longer has any + # tracks + try: + del self._album + except Exception: + pass + + old_album = self.album + + if old_album: + self.db.execute("DELETE FROM album_track WHERE track_id = ?", + (self.id,)) + + if not old_album.tracks: + old_album.delete() + + # If there are old artists, remove them if they no longer have + # any tracks + try: + del self._artists + except Exception: + pass + old_artists = self.artists + + for old_artist in old_artists: + self.db.execute("DELETE FROM artist_track WHERE track_id = " + "?", (self.id,)) + + if not old_artist.tracks: + old_artist.delete() return True + @property + def db(self): + try: + return self._db + except AttributeError: + self._db = DbManager() + return self._db + + @db.setter + def db(self, db): + self._db = db + @property def album(self): if not hasattr(self, "_album"): setattr(self, "_album", None) - db = DbManager() - cursor = db.cursor() - - for row in cursor.execute("""SELECT album.* FROM album INNER JOIN - album_track ON album.id = - album_track.album_id WHERE track_id = ? - LIMIT 1""", (self.id,)): - setattr(self, "_album", Album(row[0])) + for row in self.db.execute("SELECT album.* FROM album INNER " + "JOIN album_track ON album.id = " + "album_track.album_id WHERE " + "track_id = ? LIMIT 1", (self.id,)): + setattr(self, "_album", Album(id=row["id"], db=self.db, + name=row["name"], + date=row["date"])) return self._album @property def artists(self): if not hasattr(self, "_artists"): - db = DbManager() - cursor = db.cursor() + cursor = self.db.cursor() setattr(self, "_artists", []) - for row in cursor.execute("""SELECT artist.* FROM artist INNER JOIN - artist_track ON artist.id = - artist_track.artist_id WHERE - artist.id = ?""", (self.id,)): - self._artists.append(Artist(row[0])) + for row in cursor.execute("SELECT artist.* FROM artist INNER JOIN " + "artist_track ON artist.id = " + "artist_track.artist_id WHERE " + "artist_track.track_id = ?", + (self.id,)): + self._artists.append(Artist(id=row["id"], db=self.db, + name=row["name"], + sortname=row["sortname"], + musicbrainz_artistid=row[ + "musicbrainz_artistid"])) return self._artists def update(self, metadata): - db = DbManager() - c = db.cursor() - - c.execute("BEGIN TRANSACTION") + c = self.db.cursor() artist_names = metadata["artist"] musicbrainz_artist_ids = [] @@ -109,38 +151,38 @@ class Track: rows = None if musicbrainz_artistid: - rows = c.execute("""SELECT * FROM artist WHERE - musicbrainz_artistid = ?""", + rows = c.execute("SELECT * FROM artist WHERE " + "musicbrainz_artistid = ?", (musicbrainz_artistid,)) else: - rows = c.execute("""SELECT * FROM artist WHERE - name = ?""", (artist_name,)) + rows = c.execute("SELECT * FROM artist WHERE name = ?", + (artist_name,)) row = rows.fetchone() if row: - artist = Artist(id=row[0], name=row[1], - sortname=row[2], - musicbrainz_artistid=row[3]) + artist = Artist(id=row["id"], db=self.db, name=row["name"], + sortname=row["sortname"], + musicbrainz_artistid=row[ + "musicbrainz_artistid"]) if artist.name != artist_name: - c.execute("""UPDATE artist SET name = ? WHERE id = ?""", + c.execute("UPDATE artist SET name = ? WHERE id = ?", (artist_name, artist.id)) artist.name = artist_name if artist.sortname != artistsort: - c.execute("""UPDATE artist SET sortname = ? WHERE id = - ? """, (artistsort, id)) + c.execute("UPDATE artist SET sortname = ? WHERE id = ?", + (artistsort, id)) artist.sortname = artistsort else: - c.execute("""INSERT INTO artist - (name, sortname, musicbrainz_artistid) VALUES( - ?,?,?)""", (artist_name, artistsort, - musicbrainz_artistid)) + c.execute("INSERT INTO artist (name, sortname, " + "musicbrainz_artistid) VALUES(?, ?, ?)", + (artist_name, artistsort, musicbrainz_artistid)) artist = Artist( - id=c.lastrowid, name=artist_name, + id=c.lastrowid, db=self.db, name=artist_name, sortname=artistsort, musicbrainz_artistid=musicbrainz_artistid ) @@ -170,100 +212,135 @@ class Track: if mb_albumid: rows = c.execute( - """SELECT * FROM album WHERE musicbrainz_albumid = ?""", + "SELECT * FROM album WHERE musicbrainz_albumid = ?", (mb_albumid,) ) row = rows.fetchone() if row: - album = Album(id=row[0], name=row[1], date=row[2], - mb_albumid=row[3]) + album = Album(id=row["id"], db=self.db, name=row["name"], + date=row["date"], + musicbrainz_albumid=row["musicbrainz_albumid"]) else: - c.execute("""INSERT INTO album (name, `date`, - musicbrainz_albumid) VALUES (?,?,?)""", + c.execute("INSERT INTO album (name, `date`, " + "musicbrainz_albumid) VALUES (?, ?, ?)", (album_name, album_date, mb_albumid)) - album = Album(id=c.lastrowid, name=album_name, + album = Album(id=c.lastrowid, db=self.db, name=album_name, date=album_date, musicbrainz_albumid=mb_albumid) elif album_name: rows = c.execute( - """SELECT album.* FROM album INNER JOIN album_artist ON - album_artist.album_id = album.id WHERE album.name = ? - AND artist_id = ?""", (album_name, artist.id) + "SELECT album.* FROM album INNER JOIN album_artist ON " + "album_artist.album_id = album.id WHERE album.name = ? " + "AND artist_id = ?", (album_name, artist.id) ) row = rows.fetchone() if row: - album = Album(id=row[0], name=row[1], date=row[2]) + album = Album(id=row["id"], db=self.db, name=row["name"], + date=row["date"], + musicbrainz_albumid=row["musicbrainz_albumid"]) else: - c.execute("""INSERT INTO album (name, `date`) VALUES - (?,?)""", (album_name, album_date)) + c.execute("INSERT INTO album (name, `date`) VALUES (?, ?)", + (album_name, album_date)) - album = Album(id=c.lastrowid, name=album_name, + album = Album(id=c.lastrowid, db=self.db, name=album_name, date=album_date) if album: if album.name != album_name: - c.execute("""UPDATE album SET name = ? WHERE id = ?""", + c.execute("UPDATE album SET name = ? WHERE id = ?", (album_name, album.id)) album.name = album_name if album.date != album_date: - c.execute("""UPDATE album SET date = ? WHERE id = ?""", + c.execute("UPDATE album SET date = ? WHERE id = ?", (album_date, album.id)) album.date = album_date - for artist in artists: - if album: - try: - c.execute( - """INSERT INTO album_artist (artist_id, - album_id) VALUES(?,?)""", - (artist.id, album.id) - ) - except sqlite3.IntegrityError: - pass - track_number = None track_name = None track_grouping = None try: track_number = metadata["tracknumber"][0] + setattr(self, "tracknumber", track_number) except KeyError: pass try: track_name = metadata["title"][0] + setattr(self, "name", track_name) except KeyError: pass try: track_grouping = metadata["grouping"][0] + setattr(self, "grouping", track_grouping) except KeyError: pass - c.execute("""UPDATE track SET tracknumber = ?, name = ?, - grouping = ? WHERE id = ?""", - (track_number, track_name, track_grouping, - self.id)) + c.execute("UPDATE track SET tracknumber = ?, name = ?, grouping = ? " + "WHERE id = ?", (track_number, track_name, track_grouping, + self.id)) + + # If there is an old album, remove it if it no longer has any tracks + try: + del self._album + except Exception: + pass + + old_album = self.album + + if old_album: + c.execute("DELETE FROM album_track WHERE track_id = ?", (self.id,)) + + if not old_album.tracks: + old_album.delete() + + # If there are old artists, remove them if they no longer have + # any tracks + try: + del self._artists + except Exception: + pass + old_artists = self.artists + + for old_artist in old_artists: + c.execute("DELETE FROM artist_track WHERE track_id = ?", + (self.id,)) + + if not old_artist.tracks: + old_artist.delete() if album: try: - c.execute("""INSERT INTO album_track (album_id, - track_id) VALUES(?,?)""", - (album.id, self.id)) + c.execute("INSERT INTO album_track (album_id, track_id) " + "VALUES(?, ?)", (album.id, self.id)) except sqlite3.IntegrityError: pass + setattr(self, "_album", album) + for artist in artists: try: - c.execute("""INSERT INTO artist_track - (artist_id, track_id) VALUES(?,?)""", - (artist.id, self.id)) + c.execute("INSERT INTO artist_track (artist_id, track_id) " + "VALUES(?, ?)", (artist.id, self.id)) except sqlite3.IntegrityError: pass - db.commit() + if album: + try: + c.execute( + "INSERT INTO album_artist (artist_id, album_id) " + "VALUES(?, ?)", (artist.id, album.id)) + except sqlite3.IntegrityError: + pass + + if artists: + setattr(self, "_artists", artists) + + self.db.commit() + c.close() return True @@ -272,21 +349,25 @@ class Track: # check if the internal dict has been modified for (attr, value) in self.__dict__.items(): - if self.__data[attr] != getattr(self, attr): - dirty_attributes[attr] = value + try: + if self.__data[attr] != getattr(self, attr): + dirty_attributes[attr] = value + except AttributeError: + pass + except KeyError: + pass if len(dirty_attributes) > 0: - db = DbManager() - cursor = db.cursor() - set_clause = utils.update_clause_from_dict(dirty_attributes) - dirty_attributes[id] = self.id + dirty_attributes["id"] = self.id + + sql = " ".join(("UPDATE track", set_clause, "WHERE id = :id")) - sql = " ".join(("UPDATE track"), set_clause, "WHERE id = :id") - cursor.execute(sql, dirty_attributes) + with self.db.conn: + self.db.execute(sql, dirty_attributes) - def search(**search_params): + def search(db=None, **search_params): """Find a track with the given params Args: @@ -296,8 +377,9 @@ class Track: filename: dict, with 'data' and 'operator' keys """ - db = DbManager() - cursor = db.cursor() + if not db: + db = DbManager() + tracks = [] # unpack search params @@ -312,37 +394,40 @@ class Track: result = None if where_clause: statement = " ".join(("SELECT * FROM track", where_clause)) - result = cursor.execute(statement, value_params) + result = db.execute(statement, value_params) else: - result = cursor.execute("SELECT * FROM track") + result = db.execute("SELECT * FROM track") for row in result: tracks.append( - Track(id=row[0], tracknumber=row[1], name=row[3], - grouping=row[3], filename=row[4]) + Track(id=row["id"], db=db, tracknumber=row["tracknumber"], + name=row["name"], grouping=row["grouping"], + filename=row["filename"]) ) return tracks - def find_by_path(path): - db = DbManager() - cursor = db.cursor() + def find_by_path(path, db=None): + if not db: + db = DbManager() track = None - for row in cursor.execute("""SELECT * FROM track WHERE filename = ? - LIMIT 1""", (path,)): - track = Track(row[0]) + for row in db.execute("SELECT * FROM track WHERE filename = ? " + "LIMIT 1", (path,)): + track = Track(id=row["id"], db=db, tracknumber=row["tracknumber"], + name=row["name"], grouping=row["grouping"], + filename=row["filename"]) return track - def store(filename, metadata): - if Track.find_by_path(filename): + def store(filename, metadata, db=None): + if Track.find_by_path(filename, db=db): return True - db = DbManager() - c = db.cursor() + if not db: + db = DbManager() - c.execute("BEGIN TRANSACTION") + c = db.cursor() artist_names = metadata["artist"] musicbrainz_artist_ids = [] @@ -361,7 +446,7 @@ class Track: for artist_name in artist_names: musicbrainz_artistid = None - artistsort = artist_name + artistsort = None try: musicbrainz_artistid = musicbrainz_artist_ids[i] except IndexError: @@ -374,48 +459,47 @@ class Track: rows = None row = None if musicbrainz_artistid: - rows = c.execute("""SELECT * FROM artist WHERE - musicbrainz_artistid = ?""", + rows = c.execute("SELECT * FROM artist WHERE " + "musicbrainz_artistid = ?", (musicbrainz_artistid,)) row = rows.fetchone() if not row: - rows = c.execute("""SELECT * FROM artist WHERE - name = ? AND musicbrainz_artistid IS NULL""", + rows = c.execute("SELECT * FROM artist WHERE name = ? " + "AND musicbrainz_artistid IS NULL", (artist_name,)) row = rows.fetchone() if not row: - rows = c.execute("""SELECT * FROM artist WHERE - name = ?""", (artist_name,)) + rows = c.execute("SELECT * FROM artist WHERE name = ?", + (artist_name,)) row = rows.fetchone() if row: - artist = Artist(id=row[0], name=row[1], - sortname=row[2], - musicbrainz_artistid=row[3]) + artist = Artist(id=row["id"], db=db, name=row["name"], + sortname=row["sortname"], + musicbrainz_artistid=row[ + "musicbrainz_artistid"]) if (musicbrainz_artistid and - (not hasattr(artist, "musicbrainz_artistid") - or not artist.musicbrainz_artistid)): - c.execute("""UPDATE artist SET - musicbrainz_artistid = ? WHERE id = ?""", + (not hasattr(artist, "musicbrainz_artistid") or + not artist.musicbrainz_artistid)): + c.execute("UPDATE artist SET musicbrainz_artistid = ? " + "WHERE id = ?", (musicbrainz_artistid, artist.id)) if (artistsort and - (not hasattr(artist, "sortname") - or not artist.sortname)): - c.execute("""UPDATE artist SET - sortname = ? WHERE id = ?""", + (not hasattr(artist, "sortname") or + not artist.sortname)): + c.execute("UPDATE artist SET sortname = ? WHERE id = ?", (artistsort, artist.id)) else: - c.execute("""INSERT INTO artist - (name, sortname, musicbrainz_artistid) VALUES( - ?,?,?)""", (artist_name, artistsort, - musicbrainz_artistid)) + c.execute("INSERT INTO artist (name, sortname, " + "musicbrainz_artistid) VALUES(?, ?, ?)", + (artist_name, artistsort, musicbrainz_artistid)) artist = Artist( - id=c.lastrowid, name=artist_name, + id=c.lastrowid, db=db, name=artist_name, sortname=artistsort, musicbrainz_artistid=musicbrainz_artistid ) @@ -444,50 +528,46 @@ class Track: pass if mb_albumid: - rows = c.execute( - """SELECT * FROM album WHERE musicbrainz_albumid = ?""", - (mb_albumid,) - ) + rows = c.execute("SELECT * FROM album WHERE " + "musicbrainz_albumid = ?", (mb_albumid,)) row = rows.fetchone() if row: - album = Album(id=row[0], name=row[1], date=row[2], - mb_albumid=row[3]) + album = Album(id=row["id"], db=db, name=row["name"], + date=row["date"], musicbrainz_albumid=row[ + "musicbrainz_albumid"]) else: - c.execute("""INSERT INTO album (name, `date`, - musicbrainz_albumid) VALUES (?,?,?)""", + c.execute("INSERT INTO album (name, `date`, " + "musicbrainz_albumid) VALUES (?, ?, ?)", (album_name, album_date, mb_albumid)) - album = Album(id=c.lastrowid, name=album_name, + album = Album(id=c.lastrowid, db=db, name=album_name, date=album_date, musicbrainz_albumid=mb_albumid) elif album_name: for artist in artists: - rows = c.execute( - """SELECT album.* FROM album INNER JOIN album_artist ON - album_artist.album_id = album.id WHERE album.name = ? - AND artist_id = ?""", (album_name, artist.id) - ) + rows = c.execute("SELECT album.* FROM album INNER JOIN " + "album_artist ON album_artist.album_id = " + "album.id WHERE album.name = ? AND " + "artist_id = ?", (album_name, artist.id)) row = rows.fetchone() if row: - album = Album(id=row[0], name=row[1], date=row[2]) + album = Album(id=row["id"], db=db, name=row["name"], + date=row["date"]) else: - c.execute("""INSERT INTO album (name, `date`) VALUES - (?,?)""", (album_name, album_date)) + c.execute("INSERT INTO album (name, `date`) VALUES(?, ?)", + (album_name, album_date)) - album = Album(id=c.lastrowid, name=album_name, + album = Album(id=c.lastrowid, db=db, name=album_name, date=album_date) for artist in artists: if album: try: - c.execute( - """INSERT INTO album_artist (artist_id, - album_id) VALUES(?,?)""", - (artist.id, album.id) - ) + c.execute("INSERT INTO album_artist (artist_id, album_id) " + "VALUES(?, ?)", (artist.id, album.id)) except sqlite3.IntegrityError: pass @@ -508,63 +588,65 @@ class Track: pass track = None - rows = c.execute("""SELECT * FROM track WHERE filename = ?""", - (filename,)) + rows = c.execute("SELECT * FROM track WHERE filename = ?", (filename,)) row = rows.fetchone() if row: - track = Track(id=row[0], tracknumber=row[1], name=row[2], - grouping=row[3], filename=row[4]) + track = Track(id=row["id"], db=db, + tracknumber=row["tracknumber"], name=row["name"], + grouping=row["grouping"], filename=row["filename"]) else: - c.execute("""INSERT INTO track (tracknumber, name, - grouping, filename) VALUES(?,?,?,?)""", + c.execute("INSERT INTO track (tracknumber, name, grouping, " + "filename) VALUES(?, ?, ?, ?)", (track_number, track_name, track_grouping, filename)) - track = Track(id=c.lastrowid, tracknumber=track_number, + track = Track(id=c.lastrowid, db=db, tracknumber=track_number, name=track_name, grouping=track_grouping, filename=filename) if album: try: - c.execute("""INSERT INTO album_track (album_id, - track_id) VALUES(?,?)""", - (album.id, track.id)) + c.execute("INSERT INTO album_track (album_id, track_id) " + "VALUES(?,?)", (album.id, track.id)) except sqlite3.IntegrityError: pass for artist in artists: try: - c.execute("""INSERT INTO artist_track - (artist_id, track_id) VALUES(?,?)""", - (artist.id, track.id)) + c.execute("INSERT INTO artist_track (artist_id, track_id) " + "VALUES(?, ?)", (artist.id, track.id)) except sqlite3.IntegrityError: pass db.commit() + c.close() - return True + return track + + def all(db=None, order="track.id", direction="ASC", limit=None, + offset=None): + if not db: + db = DbManager() - def all(order="track.id", direction="ASC", limit=None, offset=None): - db = DbManager() tracks = [] - select_string = """SELECT * FROM track LEFT JOIN artist_track ON - artist_track.track_id = track.id LEFT JOIN artist ON - artist_track.artist_id = artist.id LEFT JOIN album_track ON - album_track.track_id = track.id LEFT JOIN album ON - album_track.album_id = album.id ORDER BY %s %s""" % (order, - direction) + select_string = "SELECT * FROM track LEFT JOIN artist_track ON " \ + "artist_track.track_id = track.id LEFT JOIN artist ON " \ + "artist_track.artist_id = artist.id LEFT JOIN album_track ON " \ + "album_track.track_id = track.id LEFT JOIN album ON " \ + "album_track.album_id = album.id ORDER BY %s %s" % (order, + direction) - if limit is not None and offset is not None: + if limit and offset: select_string = " ".join((select_string, - "LIMIT %s OFFSET %s" % (limit, offset))) + "LIMIT %s OFFSET %s" % (limit, offset))) result = db.execute(select_string) for row in result: - tracks.append( - Track(id=row[0], tracknumber=row[1], name=row[3], - grouping=row[3], filename=row[4]) - ) + tracks.append(Track(id=row["id"], db=db, + tracknumber=row["tracknumber"], + name=row["name"], grouping=row["name"], + filename=row["filename"])) return tracks -- cgit v1.2.3