simadb: Fixed timezone, record play timestamp as UTC (Closes #51)

[mpd-sima.git] / sima / lib / simadb.py

diff --git a/sima/lib/simadb.py b/sima/lib/simadb.py
index 5646a498f4632f4f11f01b346af6beeaa1ed0a3c..a966cf527f26da1f4293dbcbe6af2f7e8dff8593 100644 (file)
--- a/sima/lib/simadb.py
+++ b/sima/lib/simadb.py
@@ -26,6 +26,8 @@ import sqlite3
 
 from collections import deque
 from datetime import (datetime, timedelta)
+from datetime import timezone
+
 
 from sima.lib.meta import Artist, Album
 from sima.lib.track import Track
@@ -45,8 +47,7 @@ class SimaDB:
 
     def get_database_connection(self):
         """get database reference"""
-        connection = sqlite3.connect(
-            self._db_path, isolation_level=None)
+        connection = sqlite3.connect(self._db_path, isolation_level=None)
         return connection
 
     def get_info(self):
@@ -383,7 +384,8 @@ class SimaDB:
                 connection.close()
             return row[0]
         if not add:  # Not adding non existing track
-            connection.close()
+            if not with_connection:
+                connection.close()
             return None
         # Get an artist record or None
         if track.artist:
@@ -435,10 +437,11 @@ class SimaDB:
                     VALUES (?, ?)""", (trk_id, gen_id))
 
     def add_history(self, track, date=None):
-        """Record last play date of track (ie. not a real exhautive play history).
-        :param track sima.lib.track.Track: track to add to history"""
+        """Record last play date of track (ie. not a real play history).
+        :param track sima.lib.track.Track: track to add to history
+        :param date datetime.datetime: UTC datetime object (use "datetime.now(timezone.utc)" is not set)"""
         if not date:
-            date = datetime.now()
+            date = datetime.now(timezone.utc)
         connection = self.get_database_connection()
         track_id = self.get_track(track, with_connection=connection)
         rows = connection.execute("SELECT * FROM history WHERE track = ? ",
@@ -466,7 +469,7 @@ class SimaDB:
         """
         :param sima.lib.meta.Artist needle: When specified, returns albums history for this artist.
         """
-        date = datetime.utcnow() - timedelta(hours=duration)
+        date = datetime.now(timezone.utc) - timedelta(hours=duration)
         connection = self.get_database_connection()
         connection.row_factory = sqlite3.Row
         rows = connection.execute("""
@@ -501,7 +504,7 @@ class SimaDB:
         :param sima.lib.meta.Artist|sima.lib.meta.MetaContainer needle: When specified, returns history for this artist, it's actually testing the artist presence in history.
         :param sima.lib.meta.MetaContainer needle: When specified, returns history for these artists only
         """
-        date = datetime.utcnow() - timedelta(hours=duration)
+        date = datetime.now(timezone.utc) - timedelta(hours=duration)
         connection = self.get_database_connection()
         connection.row_factory = sqlite3.Row
         rows = connection.execute("""
@@ -533,7 +536,7 @@ class SimaDB:
         return hist
 
     def fetch_genres_history(self, duration=__HIST_DURATION__, limit=20):
-        date = datetime.utcnow() - timedelta(hours=duration)
+        date = datetime.now(timezone.utc) - timedelta(hours=duration)
         connection = self.get_database_connection()
         rows = connection.execute("""
                 SELECT genres.name, artists.name
@@ -558,7 +561,7 @@ class SimaDB:
         :param sima.lib.meta.Artist artist: limit history to this artist
         :param int duration: How long ago to fetch history from
         """
-        date = datetime.utcnow() - timedelta(hours=duration)
+        date = datetime.now(timezone.utc) - timedelta(hours=duration)
         connection = self.get_database_connection()
         connection.row_factory = sqlite3.Row
         sql = """
@@ -609,6 +612,8 @@ class SimaDB:
             "SELECT id FROM blocklist WHERE track = ?", (track_id,))
         if not rows.fetchone():
             if not add:
+                if not with_connection:
+                    connection.close()
                 return None
             connection.execute('INSERT INTO blocklist (track) VALUES (?)',
                                (track_id,))
@@ -634,6 +639,8 @@ class SimaDB:
             "SELECT id FROM blocklist WHERE album = ?", (album_id,))
         if not rows.fetchone():
             if not add:
+                if not with_connection:
+                    connection.close()
                 return None
             connection.execute('INSERT INTO blocklist (album) VALUES (?)',
                                (album_id,))
@@ -679,6 +686,7 @@ class SimaDB:
                albums.mbid AS musicbrainz_album,
                tracks.title AS title,
                tracks.mbid AS musicbrainz_title,
+               tracks.file AS file,
                blocklist.id
                FROM blocklist
                LEFT OUTER JOIN artists ON blocklist.artist = artists.id