emmental/db/track.py

# Copyright 2021 (c) Anna Schumaker.
#
# Table: tracks
# +---------+-----------+------------+---------+--------+--------+
# | trackid | libraryid | artistid   | albumid | discid | yearid |
# +---------+-----------+------------+---------+--------+--------+
# | number  | playcount | lastplayed | length  | title  | path   |
# +---------+-----------+------------+---------+--------+--------|
import datetime
import pathlib
from gi.repository import GObject
from . import artist
from . import album
from . import decade
from . import disc
from . import genre
from . import library
from . import sql
from . import table
from . import user
from . import year

class Track(GObject.GObject):
    def __init__(self, row):
        GObject.GObject.__init__(self)
        self._trackid = row["trackid"]
        self._library = library.Table.get(row["libraryid"])
        self._artist = artist.Table.get(row["artistid"])
        self._album = album.Table.get(row["albumid"])
        self._disc = disc.Table.get(row["discid"])
        self._decade = decade.Table.get(row["decadeid"])
        self._year = year.Table.get(row["yearid"])
        self._number = row["number"]
        self._playcount = row["playcount"]
        self._lastplayed = row["lastplayed"]
        self._length = row["length"]
        self._title = row["title"]
        self._path = pathlib.Path(row["path"])

    @GObject.Property
    def rowid(self): return self._trackid

    @GObject.Property
    def library(self): return self._library

    @GObject.Property
    def artist(self): return self._artist

    @GObject.Property
    def album(self): return self._album

    @GObject.Property
    def disc(self): return self._disc

    @GObject.Property
    def decade(self): return self._decade

    @GObject.Property
    def year(self): return self._year

    @GObject.Property
    def number(self): return self._number

    @GObject.Property
    def playcount(self): return self._playcount

    @playcount.setter
    def playcount(self, newval):
        self._playcount = self.update("playcount", newval)

    @GObject.Property
    def lastplayed(self): return self._lastplayed

    @lastplayed.setter
    def lastplayed(self, newval):
        self._lastplayed = self.update("lastplayed", newval)

    @GObject.Property
    def length(self): return self._length

    @GObject.Property
    def title(self): return self._title

    @GObject.Property
    def path(self): return self._path

    def genres(self):
        rows = sql.execute(f"SELECT genreid FROM genre_map WHERE trackid=?",
                           [ self.rowid ]).fetchall()
        return [ genre.Table.get(row[0]) for row in rows ]

    def playlists(self):
        rows = sql.execute(f"SELECT playlistid FROM playlist_map UNION "
                           f"SELECT playlistid FROM temp_playlist_map "
                           f"WHERE trackid=?", [ self.rowid ]).fetchall()
        return [ user.Table.get(row[0]) for row in rows ]

    def update(self, column, newval):
        sql.execute(f"UPDATE tracks SET {column}=? WHERE trackid=?",
                    [ newval, self.rowid ])
        return newval

    def played(self):
        self.playcount += 1
        self.lastplayed = datetime.datetime.now()
        sql.commit()


class TrackTable(table.Table):
    def __init__(self):
        table.Table.__init__(self, "tracks")

    def do_create(self):
        sql.execute("CREATE TABLE IF NOT EXISTS tracks "
                        "(trackid INTEGER PRIMARY KEY, "
                        " libraryid INTEGER, "
                        " artistid INTEGER, "
                        " albumid INTEGER, "
                        " discid INTEGER, "
                        " decadeid INTEGER, "
                        " yearid INTEGER, "
                        " number INTEGER, "
                        " playcount INTEGER DEFAULT 0,"
                        " lastplayed TIMESTAMP DEFAULT NULL, "
                        " length REAL, "
                        " title TEXT, "
                        " path TEXT UNIQUE, "
                        " FOREIGN KEY(libraryid) REFERENCES libraries(libraryid), "
                        " FOREIGN KEY(artistid) REFERENCES artists(artistid), "
                        " FOREIGN KEY(albumid) REFERENCES albums(albumid), "
                        " FOREIGN KEY(discid) REFERENCES discs(discid), "
                        " FOREIGN KEY(yearid) REFERENCES years(yearid))")

    def do_factory(self, row):
        return Track(row)

    def do_insert(self, library, artist, album, disc, decade,
                        year, number, length, title, path):
        return sql.execute("INSERT INTO tracks (libraryid, artistid, albumid, "
                                               "discid, decadeid, yearid, "
                                               "number, length, title, path) "
                           "VALUES (?,?,?,?,?,?,?,?,?,?)",
                          [ library.rowid, artist.rowid, album.rowid, disc.rowid,
                            decade.rowid, year.rowid, number, length, title, str(path) ])

    def do_lookup(self, path):
        return sql.execute("SELECT * FROM tracks WHERE path=?", [ str(path) ])

    def delete(self, track):
        for plist in track.genres() + track.playlists():
            plist.remove_track(track)

        plists = [ track.artist, track.album, track.disc,
                   track.decade, track.year, track.library,
                   user.Table.find("Collection") ]
        adjust = [ p.track_adjusts_current(track) for p in plists ]

        super().delete(track)
        for (plist, adjust) in zip(plists, adjust):
            plist.remove_track(track, adjust)

    def find(self, *args):
        raise NotImplementedError

    def insert(self, library, artist, album, disc, decade,
                     year, number, length, title, path):
        track = super().insert(library, artist, album, disc, decade,
                               year, number, length, title, path)

        user.Table.find("New Tracks").add_track(track)
        for plist in [ artist, album, disc, decade, year, library,
                       user.Table.find("Collection") ]:
            plist.add_track(track)

        return track


Table = TrackTable()