database_upgrade.py 36 KB
Newer Older
1
# Copyright (c) 2014-2020 Cedric Bellegarde <cedric.bellegarde@adishatz.org>
2 3 4 5 6 7 8 9 10 11 12
# 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/>.

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

15
import itertools
Cédric Bellegarde's avatar
Cédric Bellegarde committed
16
from time import time
Cédric Bellegarde's avatar
Cédric Bellegarde committed
17
from gettext import gettext as _
18

19
from lollypop.sqlcursor import SqlCursor
20
from lollypop.utils import translate_artist_name
Cédric Bellegarde's avatar
Cédric Bellegarde committed
21
from lollypop.database_history import History
Cédric Bellegarde's avatar
Cédric Bellegarde committed
22
from lollypop.define import App, Type, StorageType, LOLLYPOP_DATA_PATH
Cédric Bellegarde's avatar
Cédric Bellegarde committed
23
from lollypop.logger import Logger
Cédric Bellegarde's avatar
Cédric Bellegarde committed
24
from lollypop.helper_task import TaskHelper
25 26


27 28 29 30 31
class DatabaseUpgrade:
    """
        Manage database schema upgrades
    """

32
    def __init__(self):
33 34 35 36
        """
            Init object
        """
        # Here are schema upgrade, key is database version,
Cédric Bellegarde's avatar
Cédric Bellegarde committed
37
        # value is sql request
Cédric Bellegarde's avatar
Cédric Bellegarde committed
38
        self._UPGRADES = {
39
        }
40

41 42 43 44 45 46
    def upgrade(self, db):
        """
            Upgrade db
            @param db as Database
        """
        version = 0
Cédric Bellegarde's avatar
Cédric Bellegarde committed
47
        SqlCursor.add(db)
48
        with SqlCursor(db, True) as sql:
49 50 51 52 53
            result = sql.execute("PRAGMA user_version")
            v = result.fetchone()
            if v is not None:
                version = v[0]
            if version < self.version:
Cédric Bellegarde's avatar
Cédric Bellegarde committed
54
                for i in range(version + 1, self.version + 1):
55
                    try:
Cédric Bellegarde's avatar
Cédric Bellegarde committed
56 57
                        if isinstance(self._UPGRADES[i], str):
                            sql.execute(self._UPGRADES[i])
Cédric Bellegarde's avatar
Cédric Bellegarde committed
58
                            SqlCursor.commit(db)
59
                        else:
Cédric Bellegarde's avatar
Cédric Bellegarde committed
60
                            self._UPGRADES[i](db)
Cédric Bellegarde's avatar
Cédric Bellegarde committed
61
                            SqlCursor.commit(db)
62
                    except Exception as e:
Cédric Bellegarde's avatar
Cédric Bellegarde committed
63
                        Logger.error("DB upgrade %s failed: %s" %
Cédric Bellegarde's avatar
Cédric Bellegarde committed
64
                                     (i, e))
65
                sql.execute("PRAGMA user_version=%s" % self.version)
Cédric Bellegarde's avatar
Cédric Bellegarde committed
66
        SqlCursor.remove(db)
67

68 69 70 71 72
    @property
    def version(self):
        """
            Current wanted version
        """
Cédric Bellegarde's avatar
Cédric Bellegarde committed
73 74 75 76 77 78 79 80 81 82 83 84 85 86
        return len(self._UPGRADES)


class DatabasePlaylistsUpgrade(DatabaseUpgrade):
    """
        Manage database schema upgrades
    """

    def __init__(self):
        """
            Init upgrade
        """
        DatabaseUpgrade.__init__(self)
        self._UPGRADES = {
87 88
           1: "ALTER TABLE playlists ADD synced INT NOT NULL DEFAULT 0",
           2: "ALTER TABLE playlists ADD smart_enabled INT NOT NULL DEFAULT 0",
89 90
           3: "ALTER TABLE playlists ADD smart_sql TEXT",
           4: self.__upgrade_4,
91
           5: "ALTER TABLE playlists ADD uri TEXT"
Cédric Bellegarde's avatar
Cédric Bellegarde committed
92 93
        }

94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109
#######################
# PRIVATE             #
#######################
    def __upgrade_4(self, db):
        """
            Import tracks from loved playlist to DB
        """
        with SqlCursor(db, True) as sql1:
            result = sql1.execute("SELECT uri\
                                   FROM tracks\
                                   WHERE playlist_id=?", (Type.LOVED,))
            with SqlCursor(App().db, True) as sql2:
                for uri in list(itertools.chain(*result)):
                    sql2.execute("UPDATE tracks SET loved=1 WHERE uri=?",
                                 (uri,))

Cédric Bellegarde's avatar
Cédric Bellegarde committed
110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148

class DatabaseAlbumsUpgrade(DatabaseUpgrade):
    """
        Manage database schema upgrades
    """

    def __init__(self):
        """
            Init upgrade
        """
        DatabaseUpgrade.__init__(self)
        self._UPGRADES = {
            1: "UPDATE tracks SET duration=CAST(duration as INTEGER);",
            2: "UPDATE albums SET artist_id=-2001 where artist_id=-999;",
            3: self.__upgrade_3,
            4: self.__upgrade_4,
            5: "CREATE index idx_aa ON album_artists(album_id)",
            6: "CREATE index idx_ta ON track_artists(track_id)",
            7: "ALTER TABLE tracks ADD discname TEXT",
            8: "CREATE index idx_ag ON album_genres(album_id)",
            9: "CREATE index idx_tg ON track_genres(track_id)",
            10: "UPDATE tracks set ltime=0 where ltime is null",
            11: "ALTER TABLE albums ADD synced INT NOT NULL DEFAULT 0",
            12: "ALTER TABLE tracks ADD persistent INT NOT NULL DEFAULT 1",
            13: self.__upgrade_13,
            14: "UPDATE albums SET synced=-1 where mtime=0",
            15: self.__upgrade_15,
            16: self.__upgrade_16,
            17: "ALTER TABLE albums ADD loved INT NOT NULL DEFAULT 0",
            18: self.__upgrade_18,
            19: self.__upgrade_19,
            20: self.__upgrade_20,
            22: self.__upgrade_22,
            23: self.__upgrade_23,
            24: "ALTER TABLE albums ADD album_id TEXT",
            25: "ALTER TABLE tracks ADD mb_track_id TEXT",
            26: self.__upgrade_26,
            27: "UPDATE tracks SET duration=CAST(duration AS INT)",
            28: self.__upgrade_28,
149
            29: self.__upgrade_29,
Cédric Bellegarde's avatar
Cédric Bellegarde committed
150
            30: "ALTER TABLE tracks ADD loved INT NOT NULL DEFAULT 0",
151
            31: self.__upgrade_31,
152
            32: "ALTER TABLE tracks ADD bpm DOUBLE",
153
            33: "ALTER TABLE artists ADD mb_artist_id TEXT",
154 155
            34: self.__upgrade_31,
            35: "UPDATE albums SET synced=2 WHERE synced=1",
156
            36: self.__upgrade_36,
Cédric Bellegarde's avatar
Cédric Bellegarde committed
157 158 159 160
            37: self.__upgrade_37,
            38: """CREATE TABLE albums_timed_popularity (
                                                album_id INT NOT NULL,
                                                mtime INT NOT NULL,
161
                                                popularity INT NOT NULL)""",
162
            39: self.__upgrade_39,
163 164 165
            40: """UPDATE tracks SET duration = duration * 1000""",
            # Here we force an mb_album_id if empty, needed by artwork
            41: """UPDATE albums SET mb_album_id=rowid
Cédric Bellegarde's avatar
Cédric Bellegarde committed
166 167 168
                   WHERE mb_album_id is null""",
            # Fix previous update
            42: """UPDATE albums SET mb_album_id=null
169
                   WHERE storage_type=2 AND rowid=mb_album_id""",
Cédric Bellegarde's avatar
Cédric Bellegarde committed
170
            43: """CREATE TABLE featuring (artist_id INT NOT NULL,
171
                                           album_id INT NOT NULL)""",
172
            44: self.__upgrade_44,
Cédric Bellegarde's avatar
Cédric Bellegarde committed
173 174
            45: self.__upgrade_45,
            46: self.__upgrade_46
Cédric Bellegarde's avatar
Cédric Bellegarde committed
175
        }
176 177 178 179

#######################
# PRIVATE             #
#######################
180
    def __upgrade_3(self, db):
181 182 183
        """
            Add a sorted field to artists
        """
184
        with SqlCursor(db, True) as sql:
185 186 187 188 189 190 191 192 193 194
            sql.execute("ALTER TABLE artists ADD sortname TEXT")
            result = sql.execute("SELECT DISTINCT artists.rowid,\
                                  artists.name\
                                  FROM artists")
            for row in result:
                translated = translate_artist_name(row[1])
                sql.execute("UPDATE artists SET name=? WHERE rowid=?",
                            (translated, row[0]))
                sql.execute("UPDATE artists SET sortname=? WHERE rowid=?",
                            (row[1], row[0]))
195

196
    def __upgrade_4(self, db):
197 198 199
        """
            Add album artists table
        """
200
        with SqlCursor(db, True) as sql:
201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248
            sql.execute("CREATE TABLE album_artists (\
                                                album_id INT NOT NULL,\
                                                artist_id INT NOT NULL)")
            result = sql.execute("SELECT rowid from albums")
            for album_id in list(itertools.chain(*result)):
                result = sql.execute("SELECT artist_id\
                                     FROM albums\
                                     WHERE rowid=?",
                                     (album_id,))
                v = result.fetchone()
                if v is not None:
                    artist_id = v[0]
                    sql.execute("INSERT INTO album_artists\
                                (album_id, artist_id)\
                                VALUES(?, ?)",
                                (album_id, artist_id))
            sql.execute("CREATE TEMPORARY TABLE backup(id,\
                                                       name,\
                                                       no_album_artist,\
                                                       year,\
                                                       path,\
                                                       popularity,\
                                                       mtime)")
            sql.execute("INSERT INTO backup\
                        SELECT id,\
                               name,\
                               no_album_artist,\
                               year,\
                               path,\
                               popularity,\
                               mtime FROM albums")
            sql.execute("DROP TABLE albums")
            sql.execute("CREATE TABLE albums (id INTEGER PRIMARY KEY,\
                        name TEXT NOT NULL,\
                        no_album_artist BOOLEAN NOT NULL,\
                        year INT,\
                        path TEXT NOT NULL,\
                        popularity INT NOT NULL,\
                        mtime INT NOT NULL)")
            sql.execute("INSERT INTO albums\
                        SELECT id,\
                               name,\
                               no_album_artist,\
                               year,\
                               path,\
                               popularity,\
                               mtime FROM backup")
            sql.execute("DROP TABLE backup")
249

250
    def __upgrade_13(self, db):
251 252 253
        """
            Convert tracks filepath column to uri
        """
254
        with SqlCursor(db, True) as sql:
255
            sql.execute("ALTER TABLE tracks RENAME TO tmp_tracks")
Cédric Bellegarde's avatar
Cédric Bellegarde committed
256
            sql.execute("""CREATE TABLE tracks (id INTEGER PRIMARY KEY,
257 258 259 260 261 262 263 264 265 266 267 268
                                              name TEXT NOT NULL,
                                              uri TEXT NOT NULL,
                                              duration INT,
                                              tracknumber INT,
                                              discnumber INT,
                                              discname TEXT,
                                              album_id INT NOT NULL,
                                              year INT,
                                              popularity INT NOT NULL,
                                              ltime INT NOT NULL,
                                              mtime INT NOT NULL,
                                              persistent INT NOT NULL
Cédric Bellegarde's avatar
Cédric Bellegarde committed
269
                                              DEFAULT 1)""")
270

Cédric Bellegarde's avatar
Cédric Bellegarde committed
271
            sql.execute("""INSERT INTO tracks(id, name, uri, duration,
272 273 274 275 276
                        tracknumber, discnumber, discname, album_id,
                        year, popularity, ltime, mtime, persistent) SELECT
                            id, name, filepath, duration,
                            tracknumber, discnumber, discname, album_id,
                            year, popularity, ltime, mtime, persistent FROM
Cédric Bellegarde's avatar
Cédric Bellegarde committed
277
                          tmp_tracks""")
278 279 280 281 282 283 284 285 286 287 288 289
            sql.execute("DROP TABLE tmp_tracks")
            result = sql.execute("SELECT rowid FROM tracks")
            for track_id in list(itertools.chain(*result)):
                result = sql.execute("SELECT uri FROM tracks WHERE rowid=?",
                                     (track_id,))
                v = result.fetchone()
                if v is not None:
                    uri = v[0]
                    if uri.startswith("/"):
                        uri = GLib.filename_to_uri(uri)
                        sql.execute("UPDATE tracks set uri=? WHERE rowid=?",
                                    (uri, track_id))
290
        with SqlCursor(App().playlists) as sql:
291
            sql.execute("ALTER TABLE tracks RENAME TO tmp_tracks")
Cédric Bellegarde's avatar
Cédric Bellegarde committed
292 293 294 295
            sql.execute("""CREATE TABLE tracks (playlist_id INT NOT NULL,
                                                uri TEXT NOT NULL)""")
            sql.execute("""INSERT INTO tracks(playlist_id, uri) SELECT
                            playlist_id, filepath FROM tmp_tracks""")
296 297 298 299 300 301 302
            sql.execute("DROP TABLE tmp_tracks")
            result = sql.execute("SELECT uri FROM tracks")
            for path in list(itertools.chain(*result)):
                if path.startswith("/"):
                    uri = GLib.filename_to_uri(path)
                    sql.execute("UPDATE tracks set uri=? WHERE uri=?",
                                (uri, path))
303

304
    def __upgrade_15(self, db):
305 306 307
        """
            Fix broken 0.9.208 release
        """
Cédric Bellegarde's avatar
Cédric Bellegarde committed
308
        pass
Cédric Bellegarde's avatar
Cédric Bellegarde committed
309

310
    def __upgrade_16(self, db):
Cédric Bellegarde's avatar
Cédric Bellegarde committed
311
        """
Christian Perreault's avatar
Christian Perreault committed
312
            Get rid of paths
Cédric Bellegarde's avatar
Cédric Bellegarde committed
313
        """
314
        paths = App().settings.get_value("music-path")
Cédric Bellegarde's avatar
Cédric Bellegarde committed
315 316 317
        uris = []
        for path in paths:
            uris.append(GLib.filename_to_uri(path))
318
        App().settings.set_value("music-uris", GLib.Variant("as", uris))
319
        with SqlCursor(db, True) as sql:
Cédric Bellegarde's avatar
Cédric Bellegarde committed
320
            sql.execute("ALTER TABLE albums RENAME TO tmp_albums")
Cédric Bellegarde's avatar
Cédric Bellegarde committed
321
            sql.execute("""CREATE TABLE albums (
Cédric Bellegarde's avatar
Cédric Bellegarde committed
322 323 324 325 326 327 328
                                              id INTEGER PRIMARY KEY,
                                              name TEXT NOT NULL,
                                              no_album_artist BOOLEAN NOT NULL,
                                              year INT,
                                              uri TEXT NOT NULL,
                                              popularity INT NOT NULL,
                                              synced INT NOT NULL,
Cédric Bellegarde's avatar
Cédric Bellegarde committed
329
                                              mtime INT NOT NULL)""")
Cédric Bellegarde's avatar
Cédric Bellegarde committed
330

Cédric Bellegarde's avatar
Cédric Bellegarde committed
331
            sql.execute("""INSERT INTO albums(id, name, no_album_artist,
Cédric Bellegarde's avatar
Cédric Bellegarde committed
332 333
                        year, uri, popularity, synced, mtime) SELECT
                            id, name, no_album_artist,
Cédric Bellegarde's avatar
Cédric Bellegarde committed
334
                            year, path, popularity, synced, mtime FROM
Cédric Bellegarde's avatar
Cédric Bellegarde committed
335
                            tmp_albums""")
Cédric Bellegarde's avatar
Cédric Bellegarde committed
336 337
            sql.execute("DROP TABLE tmp_albums")
            result = sql.execute("SELECT rowid, uri FROM albums")
Cédric Bellegarde's avatar
Cédric Bellegarde committed
338
            for (rowid, uri) in result:
Cédric Bellegarde's avatar
Cédric Bellegarde committed
339 340
                if uri.startswith("/"):
                    uri = GLib.filename_to_uri(uri)
Cédric Bellegarde's avatar
Cédric Bellegarde committed
341
                    sql.execute("UPDATE albums set uri=? WHERE rowid=?",
Cédric Bellegarde's avatar
Cédric Bellegarde committed
342
                                (uri, rowid))
Cédric Bellegarde's avatar
Cédric Bellegarde committed
343

344
    def __upgrade_18(self, db):
Cédric Bellegarde's avatar
Cédric Bellegarde committed
345 346 347 348 349 350
        """
            Upgrade history
        """
        with SqlCursor(History()) as sql:
            sql.execute("ALTER TABLE history ADD loved_album\
                        INT NOT NULL DEFAULT 0")
Cédric Bellegarde's avatar
Cédric Bellegarde committed
351

352
    def __upgrade_19(self, db):
Cédric Bellegarde's avatar
Cédric Bellegarde committed
353 354 355 356 357 358 359 360 361 362 363
        """
            Upgrade history
        """
        with SqlCursor(History()) as sql:
            try:
                sql.execute("ALTER TABLE history ADD album_rate\
                            INT NOT NULL DEFAULT -1")
                sql.execute("ALTER TABLE history ADD rate\
                            INT NOT NULL DEFAULT -1")
            except:
                pass  # May fails if History was non existent
364
        with SqlCursor(db, True) as sql:
Cédric Bellegarde's avatar
Cédric Bellegarde committed
365 366 367 368
            sql.execute("ALTER TABLE tracks ADD rate\
                        INT NOT NULL DEFAULT -1")
            sql.execute("ALTER TABLE albums ADD rate\
                        INT NOT NULL DEFAULT -1")
Cédric Bellegarde's avatar
Cédric Bellegarde committed
369

370
    def __upgrade_20(self, db):
Cédric Bellegarde's avatar
Cédric Bellegarde committed
371 372 373 374
        """
            Add mtimes tables
        """
        mtime = int(time())
375
        with SqlCursor(db, True) as sql:
Cédric Bellegarde's avatar
Cédric Bellegarde committed
376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 409 410 411 412 413 414 415 416 417 418 419 420 421 422 423 424 425 426 427 428 429 430 431 432 433 434 435 436 437 438 439 440 441 442 443 444 445 446 447 448 449 450 451 452 453 454 455 456 457 458 459 460 461 462 463 464 465 466 467 468 469 470 471 472 473 474 475 476 477 478 479 480 481
            sql.execute("ALTER TABLE album_genres\
                         ADD mtime INT NOT NULL DEFAULT %s" % mtime)
            sql.execute("ALTER TABLE track_genres\
                         ADD mtime INT NOT NULL DEFAULT %s" % mtime)
            # Remove mtimes from albums table
            sql.execute("CREATE TEMPORARY TABLE backup(\
                                          id INTEGER PRIMARY KEY,\
                                          name TEXT NOT NULL,\
                                          no_album_artist BOOLEAN NOT NULL,\
                                          year INT,\
                                          uri TEXT NOT NULL,\
                                          popularity INT NOT NULL,\
                                          rate INT NOT NULL,\
                                          loved INT NOT NULL,\
                                          synced INT NOT NULL)")
            sql.execute("INSERT INTO backup\
                            SELECT id,\
                                   name,\
                                   no_album_artist,\
                                   year,\
                                   uri,\
                                   popularity,\
                                   rate,\
                                   loved,\
                                   synced FROM albums")
            sql.execute("DROP TABLE albums")
            sql.execute("CREATE TABLE albums(\
                                          id INTEGER PRIMARY KEY,\
                                          name TEXT NOT NULL,\
                                          no_album_artist BOOLEAN NOT NULL,\
                                          year INT,\
                                          uri TEXT NOT NULL,\
                                          popularity INT NOT NULL,\
                                          rate INT NOT NULL,\
                                          loved INT NOT NULL,\
                                          synced INT NOT NULL)")
            sql.execute("INSERT INTO albums\
                            SELECT id,\
                                   name,\
                                   no_album_artist,\
                                   year,\
                                   uri,\
                                   popularity,\
                                   rate,\
                                   loved,\
                                   synced FROM backup")
            sql.execute("DROP TABLE backup")
            # Remove mtimes from tracks table
            sql.execute("CREATE TEMPORARY TABLE backup(\
                                          id INTEGER PRIMARY KEY,\
                                          name TEXT NOT NULL,\
                                          uri TEXT NOT NULL,\
                                          duration INT,\
                                          tracknumber INT,\
                                          discnumber INT,\
                                          discname TEXT,\
                                          album_id INT NOT NULL,\
                                          year INT,\
                                          popularity INT NOT NULL,\
                                          rate INT NOT NULL,\
                                          ltime INT NOT NULL,\
                                          persistent INT NOT NULL)")
            sql.execute("INSERT INTO backup\
                            SELECT id,\
                                   name,\
                                   uri,\
                                   duration,\
                                   tracknumber,\
                                   discnumber,\
                                   discname,\
                                   album_id,\
                                   year,\
                                   popularity,\
                                   rate,\
                                   ltime,\
                                   persistent FROM tracks")
            sql.execute("DROP TABLE tracks")
            sql.execute("CREATE TABLE tracks(\
                                          id INTEGER PRIMARY KEY,\
                                          name TEXT NOT NULL,\
                                          uri TEXT NOT NULL,\
                                          duration INT,\
                                          tracknumber INT,\
                                          discnumber INT,\
                                          discname TEXT,\
                                          album_id INT NOT NULL,\
                                          year INT,\
                                          popularity INT NOT NULL,\
                                          rate INT NOT NULL,\
                                          ltime INT NOT NULL,\
                                          persistent INT NOT NULL)")
            sql.execute("INSERT INTO tracks\
                            SELECT id,\
                                   name,\
                                   uri,\
                                   duration,\
                                   tracknumber,\
                                   discnumber,\
                                   discname,\
                                   album_id,\
                                   year,\
                                   popularity,\
                                   rate,\
                                   ltime,\
                                   persistent FROM backup")
            sql.execute("DROP TABLE backup")
482

483
    def __upgrade_22(self, db):
484 485 486
        """
            Remove Charts/Web entries
        """
487
        with SqlCursor(db, True) as sql:
488 489 490 491 492 493 494 495 496 497 498 499 500 501 502 503 504 505 506 507 508 509 510 511 512 513 514 515 516 517 518 519 520 521 522 523 524 525 526 527 528 529 530 531 532 533 534 535 536 537 538 539 540 541 542
            # Remove persistent from tracks table
            sql.execute("CREATE TEMPORARY TABLE backup(\
                                          id INTEGER PRIMARY KEY,\
                                          name TEXT NOT NULL,\
                                          uri TEXT NOT NULL,\
                                          duration INT,\
                                          tracknumber INT,\
                                          discnumber INT,\
                                          discname TEXT,\
                                          album_id INT NOT NULL,\
                                          year INT,\
                                          popularity INT NOT NULL,\
                                          rate INT NOT NULL,\
                                          ltime INT NOT NULL)")
            sql.execute("INSERT INTO backup\
                            SELECT id,\
                                   name,\
                                   uri,\
                                   duration,\
                                   tracknumber,\
                                   discnumber,\
                                   discname,\
                                   album_id,\
                                   year,\
                                   popularity,\
                                   rate,\
                                   ltime FROM tracks")
            sql.execute("DROP TABLE tracks")
            sql.execute("CREATE TABLE tracks(\
                                          id INTEGER PRIMARY KEY,\
                                          name TEXT NOT NULL,\
                                          uri TEXT NOT NULL,\
                                          duration INT,\
                                          tracknumber INT,\
                                          discnumber INT,\
                                          discname TEXT,\
                                          album_id INT NOT NULL,\
                                          year INT,\
                                          popularity INT NOT NULL,\
                                          rate INT NOT NULL,\
                                          ltime INT NOT NULL)")
            sql.execute("INSERT INTO tracks\
                            SELECT id,\
                                   name,\
                                   uri,\
                                   duration,\
                                   tracknumber,\
                                   discnumber,\
                                   discname,\
                                   album_id,\
                                   year,\
                                   popularity,\
                                   rate,\
                                   ltime FROM backup")
            sql.execute("DROP TABLE backup")
543

544
    def __upgrade_23(self, db):
545 546 547
        """
            Restore back mtime in tracks
        """
548
        with SqlCursor(db, True) as sql:
549 550 551 552 553 554 555 556 557 558 559 560 561 562 563 564 565 566 567 568 569 570 571 572 573 574 575 576 577
            sql.execute("ALTER TABLE tracks ADD mtime INT")
            sql.execute("ALTER TABLE albums ADD mtime INT")

            sql.execute("UPDATE tracks SET mtime = (\
                            SELECT mtime FROM track_genres\
                            WHERE track_genres.track_id=tracks.rowid)")

            sql.execute("UPDATE albums SET mtime = (\
                            SELECT mtime FROM album_genres\
                            WHERE album_genres.album_id=albums.rowid)")
            # Remove mtime from album_genres table
            sql.execute("CREATE TABLE album_genres2 (\
                                                album_id INT NOT NULL,\
                                                genre_id INT NOT NULL)")
            sql.execute("INSERT INTO album_genres2\
                            SELECT album_id,\
                                   genre_id FROM album_genres")
            sql.execute("DROP TABLE album_genres")
            sql.execute("ALTER TABLE album_genres2 RENAME TO album_genres")

            # Remove mtime from track_genres table
            sql.execute("CREATE TABLE track_genres2 (\
                                                track_id INT NOT NULL,\
                                                genre_id INT NOT NULL)")
            sql.execute("INSERT INTO track_genres2\
                            SELECT track_id,\
                                   genre_id FROM track_genres")
            sql.execute("DROP TABLE track_genres")
            sql.execute("ALTER TABLE track_genres2 RENAME TO track_genres")
578

Cédric Bellegarde's avatar
Cédric Bellegarde committed
579
    def __upgrade_26(self, db):
580 581 582
        """
            Rename album_id to mb_album_id in albums
        """
583
        with SqlCursor(db, True) as sql:
584 585 586 587 588 589 590 591 592 593 594 595 596 597 598 599 600 601 602 603
            sql.execute("ALTER TABLE albums RENAME TO tmp_albums")
            sql.execute("""CREATE TABLE albums (
                               id INTEGER PRIMARY KEY,
                               name TEXT NOT NULL,
                               mb_album_id TEXT,
                               no_album_artist BOOLEAN NOT NULL,
                               year INT,
                               uri TEXT NOT NULL,
                               popularity INT NOT NULL,
                               rate INT NOT NULL,
                               loved INT NOT NULL,
                               mtime INT NOT NULL,
                               synced INT NOT NULL)""")

            sql.execute("""INSERT INTO albums (id, name, mb_album_id,
                            no_album_artist, year, uri, popularity, rate,
                            loved, mtime, synced) SELECT id, name, album_id,
                            no_album_artist, year, uri, popularity, rate,
                            loved, mtime, synced FROM tmp_albums""")
            sql.execute("DROP TABLE tmp_albums")
Cédric Bellegarde's avatar
Cédric Bellegarde committed
604 605 606 607 608 609

    def __upgrade_28(self, db):
        """
            Upgrade setting based on db
            https://gitlab.gnome.org/gnumdk/lollypop/issues/1368
        """
610
        with SqlCursor(db, True) as sql:
Cédric Bellegarde's avatar
Cédric Bellegarde committed
611 612 613 614 615 616 617 618 619
            result = sql.execute("SELECT albums.rowid\
                                  FROM albums, album_artists\
                                  WHERE album_artists.artist_id=?\
                                  AND album_artists.album_id=albums.rowid\
                                  LIMIT 1",
                                 (Type.COMPILATIONS,))
            if list(itertools.chain(*result)):
                App().settings.set_value("show-compilations",
                                         GLib.Variant("b", True))
620 621 622 623 624 625 626 627

    def __upgrade_29(self, db):
        """
            Upgrade year to year
        """
        from time import strptime, mktime
        from datetime import datetime
        for item in ["albums", "tracks"]:
628
            with SqlCursor(db, True) as sql:
629 630 631 632 633
                sql.execute("ALTER TABLE %s ADD timestamp INT" % item)
                result = sql.execute("SELECT rowid, year FROM %s" % item)
                for (rowid, year) in result:
                    if year is None:
                        continue
Cédric Bellegarde's avatar
Cédric Bellegarde committed
634
                    elif len(str(year)) == 2:
635
                        struct = strptime(str(year), "%y")
Cédric Bellegarde's avatar
Cédric Bellegarde committed
636
                    elif len(str(year)) == 4:
637 638 639
                        struct = strptime(str(year), "%Y")
                    else:
                        continue
640 641 642 643 644
                    dt = datetime.fromtimestamp(mktime(struct))
                    timestamp = dt.timestamp()
                    sql.execute(
                        "UPDATE %s set timestamp=? WHERE rowid=?" % item,
                        (timestamp, rowid))
Cédric Bellegarde's avatar
Cédric Bellegarde committed
645 646 647 648 649 650 651 652 653 654 655 656

    def __upgrade_31(self, db):
        """
            Delete history database related to upgrade 30
        """
        try:
            LOCAL_PATH = GLib.get_user_data_dir() + "/lollypop"
            DB_PATH = "%s/history.db" % LOCAL_PATH
            f = Gio.File.new_for_path(DB_PATH)
            f.delete(None)
        except Exception as e:
            Logger.error("DatabaseAlbumsUpgrade::__upgrade_31(): %s", e)
657 658 659 660 661 662 663 664 665 666 667 668 669 670 671 672

    def __upgrade_36(self, db):
        """
            Restore back mtime in tracks
        """
        with SqlCursor(db, True) as sql:
            sql.execute("ALTER TABLE tracks ADD storage_type INT")
            sql.execute("ALTER TABLE albums ADD storage_type INT")
            sql.execute("UPDATE tracks SET storage_type=?\
                         WHERE mtime > 0", (StorageType.COLLECTION,))
            sql.execute("UPDATE albums SET storage_type=?\
                         WHERE mtime > 0", (StorageType.COLLECTION,))
            sql.execute("UPDATE tracks SET storage_type=?\
                         WHERE mtime = -1", (StorageType.SAVED,))
            sql.execute("UPDATE albums SET storage_type=?\
                         WHERE mtime = -1", (StorageType.SAVED,))
673 674 675 676 677 678 679 680 681 682 683 684 685

    def __upgrade_37(self, db):
        """
            Update Type.WEB and Type.COMPILATIONS
        """
        App().settings.reset("shown-album-lists")
        with SqlCursor(db, True) as sql:
            sql.execute("UPDATE track_genres SET genre_id=-9\
                         WHERE genre_id=-22")
            sql.execute("UPDATE album_genres SET genre_id=-9\
                         WHERE genre_id=-22")
            sql.execute("UPDATE album_artists SET artist_id=-10\
                         WHERE artist_id=-2001")
686 687 688 689 690 691 692 693 694 695 696 697 698 699 700 701 702 703 704 705 706 707 708 709 710 711 712 713 714 715 716 717 718

    def __upgrade_39(self, db):
        """
            Reset Spotify tracks: we are now using Spotify id as MusicBrainz id
        """
        with SqlCursor(db, True) as sql:
            for storage_type in [StorageType.SPOTIFY_NEW_RELEASES,
                                 StorageType.SPOTIFY_SIMILARS]:
                sql.execute("DELETE FROM tracks WHERE\
                             storage_type=? AND mb_track_id=''",
                            (storage_type,))
            sql.execute("DELETE FROM track_artists\
                         WHERE track_artists.track_id NOT IN (\
                            SELECT tracks.rowid FROM tracks)")
            sql.execute("DELETE FROM track_genres\
                         WHERE track_genres.track_id NOT IN (\
                            SELECT tracks.rowid FROM tracks)")
            sql.execute("DELETE FROM albums WHERE albums.rowid NOT IN (\
                            SELECT tracks.album_id FROM tracks)")
            sql.execute("DELETE FROM album_genres\
                         WHERE album_genres.album_id NOT IN (\
                            SELECT albums.rowid FROM albums)")
            sql.execute("DELETE FROM album_artists\
                         WHERE album_artists.album_id NOT IN (\
                            SELECT albums.rowid FROM albums)")
            sql.execute("DELETE FROM albums_timed_popularity\
                         WHERE albums_timed_popularity.album_id NOT IN (\
                            SELECT albums.rowid FROM albums)")
            sql.execute("DELETE FROM artists WHERE artists.rowid NOT IN (\
                            SELECT album_artists.artist_id\
                            FROM album_artists) AND artists.rowid NOT IN (\
                                SELECT track_artists.artist_id\
                                FROM track_artists)")
719 720 721 722 723

    def __upgrade_44(self, db):
        """
            Delete spotify albums as spotify id is not stored in URI
        """
724 725 726 727 728 729 730
        from lollypop.database_albums import AlbumsDatabase
        from lollypop.database_artists import ArtistsDatabase
        from lollypop.database_tracks import TracksDatabase
        albums = AlbumsDatabase(db)
        artists = ArtistsDatabase(db)
        tracks = TracksDatabase(db)
        for storage_type in [StorageType.SPOTIFY_NEW_RELEASES,
731 732
                             StorageType.SPOTIFY_SIMILARS,
                             StorageType.DEEZER_CHARTS]:
733 734 735 736 737 738 739 740
            album_ids = albums.get_for_storage_type(storage_type)
            for album_id in album_ids:
                # EPHEMERAL with not tracks will be cleaned below
                albums.set_storage_type(album_id, StorageType.EPHEMERAL)
                tracks.remove_album(album_id)
        tracks.clean()
        albums.clean()
        artists.clean()
741 742 743

    def __upgrade_45(self, db):
        """
Cédric Bellegarde's avatar
Cédric Bellegarde committed
744
            Add lp_album_id/lp_track_id
745 746 747 748
        """
        with SqlCursor(db, True) as sql:
            sql.execute("ALTER TABLE tracks ADD lp_track_id TEXT")
            sql.execute("ALTER TABLE albums ADD lp_album_id TEXT")
Cédric Bellegarde's avatar
Cédric Bellegarde committed
749 750 751 752 753

    def __upgrade_46(self, db):
        """
            Populate lp_album_id/lp_track_id
        """
Cédric Bellegarde's avatar
Cédric Bellegarde committed
754
        queue = LOLLYPOP_DATA_PATH + "/queue.bin"
Cédric Bellegarde's avatar
Cédric Bellegarde committed
755 756 757 758 759
        try:
            f = Gio.File.new_for_path(queue)
            f.delete(None)
        except:
            pass
Cédric Bellegarde's avatar
Cédric Bellegarde committed
760 761 762 763 764 765 766 767 768 769 770
        from lollypop.database_albums import AlbumsDatabase
        from lollypop.database_tracks import TracksDatabase
        from lollypop.utils import get_lollypop_album_id, get_lollypop_track_id
        albums = AlbumsDatabase(db)
        tracks = TracksDatabase(db)

        def do_migration(dialog, label, progress):
            GLib.idle_add(
                label.set_text,
                _("Please wait while Lollypop is updating albums"))
            album_ids = albums.get_ids([], [], StorageType.ALL, True)
771
            album_ids += albums.get_compilation_ids([], StorageType.ALL, True)
Cédric Bellegarde's avatar
Cédric Bellegarde committed
772 773 774 775 776 777 778 779 780 781 782
            count = len(album_ids)
            i = 0
            for album_id in album_ids:
                if i % 10 == 0:
                    GLib.idle_add(progress.set_fraction, i / count)
                name = albums.get_name(album_id)
                artists = ";".join(albums.get_artists(album_id))
                lp_album_id = get_lollypop_album_id(name, artists)
                albums.set_lp_album_id(album_id, lp_album_id)
                i += 1

783
            track_ids = tracks.get_ids(StorageType.ALL, True)
Cédric Bellegarde's avatar
Cédric Bellegarde committed
784 785 786 787 788 789 790 791 792 793 794 795 796 797 798 799 800 801 802 803 804 805 806 807 808 809 810 811 812 813 814
            count = len(track_ids)
            i = 0
            GLib.idle_add(
                label.set_text,
                _("Please wait while Lollypop is updating tracks"))
            for track_id in track_ids:
                if i % 10 == 0:
                    GLib.idle_add(progress.set_fraction, i / count)
                name = tracks.get_name(track_id)
                artists = ";".join(tracks.get_artists(track_id))
                album_name = tracks.get_album_name(track_id)
                lp_track_id = get_lollypop_track_id(name, artists, album_name)
                tracks.set_lp_track_id(track_id, lp_track_id)
                i += 1
            GLib.idle_add(dialog.destroy)

        dialog = Gtk.MessageDialog(buttons=Gtk.ButtonsType.NONE)
        progress = Gtk.ProgressBar.new()
        progress.show()
        label = Gtk.Label.new()
        label.show()
        grid = Gtk.Grid.new()
        grid.set_orientation(Gtk.Orientation.VERTICAL)
        grid.set_row_spacing(10)
        grid.show()
        grid.add(label)
        grid.add(progress)
        dialog.set_image(grid)
        helper = TaskHelper()
        helper.run(do_migration, dialog, label, progress)
        dialog.run()