database.py 9.04 KB
Newer Older
Cédric Bellegarde's avatar
Cédric Bellegarde committed
1
# Copyright (c) 2014-2019 Cedric Bellegarde <cedric.bellegarde@adishatz.org>
Cédric Bellegarde's avatar
Cédric Bellegarde committed
2 3 4 5 6 7 8 9 10 11
# This program is free software: you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation, either version 3 of the License, or
# (at your option) any later version.
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
# GNU General Public License for more details.
# You should have received a copy of the GNU General Public License
# along with this program. If not, see <http://www.gnu.org/licenses/>.
12

Cédric Bellegarde's avatar
Cédric Bellegarde committed
13
from gi.repository import GLib, Gio
14

15
import sqlite3
16
from threading import Lock
17
import itertools
18

19
from lollypop.define import App
20
from lollypop.database_upgrade import DatabaseAlbumsUpgrade
21
from lollypop.sqlcursor import SqlCursor
22
from lollypop.logger import Logger
23
from lollypop.localized import LocalizedCollation
24
from lollypop.utils import noaccents
25

26

27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47
class MyLock:
    """
        Lock with count
    """
    def __init__(self):
        self.__lock = Lock()
        self.__count = 0

    def acquire(self):
        self.__count += 1
        self.__lock.acquire()

    def release(self):
        self.__count -= 1
        self.__lock.release()

    @property
    def count(self):
        return self.__count


48
class Database:
49 50 51
    """
        Base database object
    """
52
    __LOCAL_PATH = GLib.get_user_data_dir() + "/lollypop"
53
    DB_PATH = "%s/lollypop.db" % __LOCAL_PATH
54

55 56 57 58 59
    # SQLite documentation:
    # In SQLite, a column with type INTEGER PRIMARY KEY
    # is an alias for the ROWID.
    # Here, we define an id INT PRIMARY KEY but never feed it,
    # this make VACUUM not destroy rowids...
Cédric Bellegarde's avatar
Cédric Bellegarde committed
60
    __create_albums = """CREATE TABLE albums (id INTEGER PRIMARY KEY,
61
                                              name TEXT NOT NULL,
62
                                              mb_album_id TEXT,
63 64
                                              no_album_artist BOOLEAN NOT NULL,
                                              year INT,
65
                                              timestamp INT,
Cédric Bellegarde's avatar
Cédric Bellegarde committed
66
                                              uri TEXT NOT NULL,
67
                                              popularity INT NOT NULL,
Cédric Bellegarde's avatar
Cédric Bellegarde committed
68 69
                                              rate INT NOT NULL,
                                              loved INT NOT NULL,
70
                                              mtime INT NOT NULL,
Cédric Bellegarde's avatar
Cédric Bellegarde committed
71 72
                                              synced INT NOT NULL)"""
    __create_artists = """CREATE TABLE artists (id INTEGER PRIMARY KEY,
73
                                               name TEXT NOT NULL,
74 75
                                               sortname TEXT NOT NULL,
                                               mb_artist_id TEXT)"""
Cédric Bellegarde's avatar
Cédric Bellegarde committed
76 77 78
    __create_genres = """CREATE TABLE genres (id INTEGER PRIMARY KEY,
                                            name TEXT NOT NULL)"""
    __create_album_artists = """CREATE TABLE album_artists (
79
                                                album_id INT NOT NULL,
Cédric Bellegarde's avatar
Cédric Bellegarde committed
80 81
                                                artist_id INT NOT NULL)"""
    __create_album_genres = """CREATE TABLE album_genres (
82
                                                album_id INT NOT NULL,
Cédric Bellegarde's avatar
Cédric Bellegarde committed
83 84
                                                genre_id INT NOT NULL)"""
    __create_tracks = """CREATE TABLE tracks (id INTEGER PRIMARY KEY,
85
                                              name TEXT NOT NULL,
86
                                              uri TEXT NOT NULL,
87 88 89 90 91 92
                                              duration INT,
                                              tracknumber INT,
                                              discnumber INT,
                                              discname TEXT,
                                              album_id INT NOT NULL,
                                              year INT,
93
                                              timestamp INT,
94
                                              popularity INT NOT NULL,
95
                                              loved INT NOT NULL DEFAULT 0,
Cédric Bellegarde's avatar
Cédric Bellegarde committed
96
                                              rate INT NOT NULL,
97
                                              ltime INT NOT NULL,
98
                                              mtime INT NOT NULL,
99 100
                                              mb_track_id TEXT,
                                              bpm DOUBLE
Cédric Bellegarde's avatar
Cédric Bellegarde committed
101 102
                                              )"""
    __create_track_artists = """CREATE TABLE track_artists (
103
                                                track_id INT NOT NULL,
Cédric Bellegarde's avatar
Cédric Bellegarde committed
104 105
                                                artist_id INT NOT NULL)"""
    __create_track_genres = """CREATE TABLE track_genres (
106
                                                track_id INT NOT NULL,
Cédric Bellegarde's avatar
Cédric Bellegarde committed
107 108 109 110 111 112 113 114 115
                                                genre_id INT NOT NULL)"""
    __create_album_artists_idx = """CREATE index idx_aa ON album_artists(
                                                album_id)"""
    __create_track_artists_idx = """CREATE index idx_ta ON track_artists(
                                                track_id)"""
    __create_album_genres_idx = """CREATE index idx_ag ON album_genres(
                                                album_id)"""
    __create_track_genres_idx = """CREATE index idx_tg ON track_genres(
                                                track_id)"""
116

117
    def __init__(self):
118 119 120
        """
            Create database tables or manage update if needed
        """
121
        self.thread_lock = MyLock()
122
        f = Gio.File.new_for_path(self.DB_PATH)
123
        upgrade = DatabaseAlbumsUpgrade()
124
        if not f.query_exists():
125
            try:
126
                d = Gio.File.new_for_path(self.__LOCAL_PATH)
127 128
                if not d.query_exists():
                    d.make_directory_with_parents()
129
                # Create db schema
130
                with SqlCursor(self, True) as sql:
131 132 133 134 135 136 137 138 139 140 141 142
                    sql.execute(self.__create_albums)
                    sql.execute(self.__create_artists)
                    sql.execute(self.__create_genres)
                    sql.execute(self.__create_album_genres)
                    sql.execute(self.__create_album_artists)
                    sql.execute(self.__create_tracks)
                    sql.execute(self.__create_track_artists)
                    sql.execute(self.__create_track_genres)
                    sql.execute(self.__create_album_artists_idx)
                    sql.execute(self.__create_track_artists_idx)
                    sql.execute(self.__create_album_genres_idx)
                    sql.execute(self.__create_track_genres_idx)
143
                    sql.execute("PRAGMA user_version=%s" % upgrade.version)
144
            except Exception as e:
145
                Logger.error("Database::__init__(): %s" % e)
146 147
        else:
            upgrade.upgrade(self)
148

149 150
    def execute(self, request):
        """
151
            Execute SQL request (only smart one)
152 153 154
            @param request as str
            @return list
        """
155 156 157 158 159 160 161 162 163 164 165 166 167
        try:
            with SqlCursor(App().db) as sql:
                result = sql.execute(request)
                # Special case for OR request
                if request.find("ORDER BY random()") == -1 and\
                        request.find("UNION") != -1:
                    ids = []
                    for (id, other) in list(result):
                        ids.append(id)
                    return ids
                else:
                    return list(itertools.chain(*result))
        except Exception as e:
168
            Logger.error("Database::execute(): %s -> %s", e, request)
169
        return []
170

171
    def get_cursor(self):
172 173 174
        """
            Return a new sqlite cursor
        """
175
        try:
176
            c = sqlite3.connect(self.DB_PATH, 600.0)
Cédric Bellegarde's avatar
Cédric Bellegarde committed
177
            c.create_collation("LOCALIZED", LocalizedCollation())
178
            c.create_function("noaccents", 1, noaccents)
179
            return c
180 181
        except:
            exit(-1)
182

Cédric Bellegarde's avatar
Cédric Bellegarde committed
183 184 185 186 187 188 189 190
    def drop_db(self):
        """
            Drop database
        """
        try:
            f = Gio.File.new_for_path(self.DB_PATH)
            f.trash()
        except Exception as e:
191
            Logger.error("Database::drop_db():", e)
Cédric Bellegarde's avatar
Cédric Bellegarde committed
192

193
    def exists_in_db(self, album, artists, track):
194
        """
195 196 197 198
            Search if item exists in db
            @param track as str
            @param album as str
            @param artists as [str]
199
            @return object
200
        """
201 202 203 204 205
        artist_ids = []
        for artist in artists:
            artist_id = App().artists.get_id(artist)
            artist_ids.append(artist_id)
        album_id = App().albums.get_id_by_name_artists(album, artist_ids)
206
        if album_id is None:
207
            return None
208
        elif App().albums.get_mtime(album_id) > 0 or track is None:
209
            return album_id
210
        else:
211 212 213
            track_id = App().tracks.get_id_by(track,
                                              album_id,
                                              artist_ids)
214
            return track_id
215

216 217 218
#######################
# PRIVATE             #
#######################