database_albums.py 42.7 KB
Newer Older
Cédric Bellegarde's avatar
Cédric Bellegarde committed
1
# Copyright (c) 2014-2019 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/>.

13
import itertools
14

15
from lollypop.sqlcursor import SqlCursor
16
from lollypop.define import App, Type, OrderBy
17
from lollypop.logger import Logger
18
from lollypop.utils import noaccents, get_network_available
19

20

Cédric Bellegarde's avatar
Cédric Bellegarde committed
21
class AlbumsDatabase:
22
    """
23
        Albums database helper
24 25
    """

26
    def __init__(self):
27 28 29
        """
            Init albums database object
        """
30
        self.__max_count = 1
31
        self.__cached_randoms = []
32

33
    def add(self, album_name, mb_album_id, artist_ids,
34
            uri, loved, popularity, rate, synced, mtime):
35 36
        """
            Add a new album to database
37
            @param album_name as str
38
            @param mb_album_id as str
39 40
            @param artist_ids as int
            @param uri as str
41 42
            @param loved as bool
            @param popularity as int
Cédric Bellegarde's avatar
Cédric Bellegarde committed
43
            @param rate as int
44
            @param synced as int
45
            @param mtime as int
46
            @return inserted rowid as int
47 48
            @warning: commit needed
        """
49
        with SqlCursor(App().db, True) as sql:
50
            result = sql.execute("INSERT INTO albums\
Cédric Bellegarde's avatar
Cédric Bellegarde committed
51
                                  (name, mb_album_id, no_album_artist,\
52
                                  uri, loved, popularity, rate, mtime, synced)\
53
                                  VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)",
54 55
                                 (album_name, mb_album_id or None,
                                  artist_ids == [], uri, loved, popularity,
56
                                  rate, mtime, synced))
57 58 59 60
            for artist_id in artist_ids:
                sql.execute("INSERT INTO album_artists\
                             (album_id, artist_id)\
                             VALUES (?, ?)", (result.lastrowid, artist_id))
61
            return result.lastrowid
62

Cédric Bellegarde's avatar
Cédric Bellegarde committed
63 64 65
    def add_artist(self, album_id, artist_id):
        """
            Add artist to track
66
            @param album_id as int
67
            @param artist_id as int
Cédric Bellegarde's avatar
Cédric Bellegarde committed
68 69
            @warning: commit needed
        """
70
        with SqlCursor(App().db, True) as sql:
71 72
            artist_ids = self.get_artist_ids(album_id)
            if artist_id not in artist_ids:
Cédric Bellegarde's avatar
Cédric Bellegarde committed
73 74 75 76
                sql.execute("INSERT INTO "
                            "album_artists (album_id, artist_id)"
                            "VALUES (?, ?)", (album_id, artist_id))

77
    def add_genre(self, album_id, genre_id):
78 79
        """
            Add genre to album
80
            @param album_id as int
81
            @param genre_id as int
82 83
            @warning: commit needed
        """
84
        with SqlCursor(App().db, True) as sql:
85 86 87 88 89 90
            genres = self.get_genre_ids(album_id)
            if genre_id not in genres:
                sql.execute("INSERT INTO\
                             album_genres (album_id, genre_id)\
                             VALUES (?, ?)",
                            (album_id, genre_id))
91

92
    def set_artist_ids(self, album_id, artist_ids):
93 94
        """
            Set artist id
95
            @param album_id as int
96
            @param artist_ids as [int]
97 98
            @warning: commit needed
        """
99
        with SqlCursor(App().db, True) as sql:
100 101 102 103 104 105
            sql.execute("DELETE FROM album_artists\
                        WHERE album_id=?", (album_id,))
            for artist_id in artist_ids:
                sql.execute("INSERT INTO album_artists\
                            (album_id, artist_id)\
                            VALUES (?, ?)", (album_id, artist_id))
106

107 108 109
    def set_synced(self, album_id, synced):
        """
            Set album synced
110
            @param album_id as int
111
            @param synced as int
112 113
            @warning: commit needed
        """
114
        with SqlCursor(App().db, True) as sql:
115 116 117
            sql.execute("UPDATE albums SET synced=? WHERE rowid=?",
                        (synced, album_id))

Cédric Bellegarde's avatar
Cédric Bellegarde committed
118 119 120 121 122 123 124 125 126 127
    def set_mtime(self, album_id, mtime):
        """
            Set album mtime
            @param album_id as int
            @param mtime as int
        """
        with SqlCursor(App().db, True) as sql:
            sql.execute("UPDATE albums SET mtime=? WHERE rowid=?",
                        (mtime, album_id))

128 129 130
    def set_loved(self, album_id, loved):
        """
            Set album loved
131
            @param album_id as int
132 133 134
            @param loved as int
            @warning: commit needed
        """
135
        with SqlCursor(App().db, True) as sql:
136 137 138
            sql.execute("UPDATE albums SET loved=? WHERE rowid=?",
                        (loved, album_id))

Cédric Bellegarde's avatar
Cédric Bellegarde committed
139 140 141
    def set_rate(self, album_id, rate):
        """
            Set album rate
142
            @param album_id as int
Cédric Bellegarde's avatar
Cédric Bellegarde committed
143 144
            @param rate as int
        """
145
        with SqlCursor(App().db, True) as sql:
Cédric Bellegarde's avatar
Cédric Bellegarde committed
146 147 148
            sql.execute("UPDATE albums SET rate=? WHERE rowid=?",
                        (rate, album_id))

149
    def set_year(self, album_id, year):
150 151
        """
            Set year
152
            @param album_id as int
153 154 155
            @param year as int
            @warning: commit needed
        """
156
        with SqlCursor(App().db, True) as sql:
157 158
            sql.execute("UPDATE albums SET year=? WHERE rowid=?",
                        (year, album_id))
159

160 161 162
    def set_timestamp(self, album_id, timestamp):
        """
            Set timestamp
163
            @param album_id as int
164 165 166
            @param timestamp as int
            @warning: commit needed
        """
167
        with SqlCursor(App().db, True) as sql:
168 169 170
            sql.execute("UPDATE albums SET timestamp=? WHERE rowid=?",
                        (timestamp, album_id))

Cédric Bellegarde's avatar
Cédric Bellegarde committed
171
    def set_uri(self, album_id, uri):
172
        """
Cédric Bellegarde's avatar
Cédric Bellegarde committed
173
            Set album uri for album id
174 175
            @param album_id as int
            @param uri as string
176 177
            @warning: commit needed
        """
178
        with SqlCursor(App().db, True) as sql:
Cédric Bellegarde's avatar
Cédric Bellegarde committed
179 180
            sql.execute("UPDATE albums SET uri=? WHERE rowid=?",
                        (uri, album_id))
181

182
    def set_popularity(self, album_id, popularity):
183 184 185 186 187
        """
            Set popularity
            @param album_id as int
            @param popularity as int
        """
188
        with SqlCursor(App().db, True) as sql:
189 190 191 192 193
            try:
                sql.execute("UPDATE albums set popularity=? WHERE rowid=?",
                            (popularity, album_id))
            except:  # Database is locked
                pass
194

195
    def get_synced_ids(self, index):
196 197
        """
            Get synced album ids
198
            @param index as int => device index from gsettings
199
        """
200
        with SqlCursor(App().db) as sql:
201
            request = "SELECT DISTINCT albums.rowid\
Cédric Bellegarde's avatar
Cédric Bellegarde committed
202 203
                       FROM albums, artists, album_artists\
                       WHERE album_artists.album_id = albums.rowid\
204 205
                       AND (album_artists.artist_id = artists.rowid\
                            OR album_artists.artist_id=?)\
206
                       AND synced & (1 << ?) AND albums.mtime != 0"
Cédric Bellegarde's avatar
Cédric Bellegarde committed
207 208
            order = " ORDER BY artists.sortname\
                     COLLATE NOCASE COLLATE LOCALIZED,\
209
                     albums.timestamp,\
Cédric Bellegarde's avatar
Cédric Bellegarde committed
210 211
                     albums.name\
                     COLLATE NOCASE COLLATE LOCALIZED"
212
            filters = (Type.COMPILATIONS, index)
213
            result = sql.execute(request + order, filters)
214 215 216 217 218 219
            return list(itertools.chain(*result))

    def get_synced(self, album_id):
        """
            Get album synced status
            @param album_id as int
220
            @return synced as int
221
        """
222
        with SqlCursor(App().db) as sql:
223 224 225 226 227 228 229
            result = sql.execute("SELECT synced FROM albums WHERE\
                                 rowid=?", (album_id,))
            v = result.fetchone()
            if v is not None:
                return v[0]
            return 0

230 231 232
    def get_loved(self, album_id):
        """
            Get album loved
233
            @param album_id as int
234
            @return loved as int
235
        """
236
        with SqlCursor(App().db) as sql:
237 238 239 240 241 242 243 244
            result = sql.execute("SELECT loved FROM albums WHERE\
                                 rowid=?", (album_id,))

            v = result.fetchone()
            if v is not None:
                return v[0]
            return 0

245 246 247 248 249
    def get_by_year(self, year):
        """
            Get albums with year
            @param year as str
        """
250
        with SqlCursor(App().db) as sql:
251
            filters = (year, )
252
            request = "SELECT DISTINCT albums.rowid\
253 254
                       FROM albums\
                       WHERE year=?"
255 256 257
            result = sql.execute(request, filters)
            return list(itertools.chain(*result))

Cédric Bellegarde's avatar
Cédric Bellegarde committed
258 259 260
    def get_rate(self, album_id):
        """
            Get album rate
261
            @param album_id as int
Cédric Bellegarde's avatar
Cédric Bellegarde committed
262 263
            @return rate as int
        """
264
        with SqlCursor(App().db) as sql:
Cédric Bellegarde's avatar
Cédric Bellegarde committed
265 266 267 268
            result = sql.execute("SELECT rate FROM albums WHERE\
                                 rowid=?", (album_id,))

            v = result.fetchone()
269
            if v:
Cédric Bellegarde's avatar
Cédric Bellegarde committed
270
                return v[0]
271
            return 0
Cédric Bellegarde's avatar
Cédric Bellegarde committed
272

273
    def get_popularity(self, album_id):
274 275 276 277 278
        """
            Get popularity
            @param album_id as int
            @return popularity as int
        """
279
        with SqlCursor(App().db) as sql:
280 281
            result = sql.execute("SELECT popularity FROM albums WHERE\
                                 rowid=?", (album_id,))
282

283 284 285 286
            v = result.fetchone()
            if v is not None:
                return v[0]
            return 0
287

288
    def set_more_popular(self, album_id, pop_to_add):
289 290
        """
            Increment popularity field for album id
291
            @param album_id as int
292
            @param pop_to_add as int
293 294
            @raise sqlite3.OperationalError on db update
        """
295
        with SqlCursor(App().db, True) as sql:
296 297 298 299 300 301 302
            result = sql.execute("SELECT popularity from albums WHERE rowid=?",
                                 (album_id,))
            pop = result.fetchone()
            if pop:
                current = pop[0]
            else:
                current = 0
303
            current += pop_to_add
304 305 306
            sql.execute("UPDATE albums set popularity=? WHERE rowid=?",
                        (current, album_id))

307 308 309 310 311
    def get_higher_popularity(self):
        """
            Get higher available popularity
            @return int
        """
312
        with SqlCursor(App().db) as sql:
313 314 315 316 317 318 319 320
            result = sql.execute("SELECT popularity\
                                  FROM albums\
                                  ORDER BY POPULARITY DESC LIMIT 1")
            v = result.fetchone()
            if v is not None:
                return v[0]
            return 0

321
    def get_avg_popularity(self):
322 323 324 325
        """
            Return avarage popularity
            @return avarage popularity as int
        """
326
        with SqlCursor(App().db) as sql:
327 328 329
            result = sql.execute("SELECT AVG(popularity)\
                                  FROM (SELECT popularity\
                                        FROM albums\
330
                                        ORDER BY POPULARITY DESC LIMIT 1000)")
331 332 333 334
            v = result.fetchone()
            if v and v[0] > 5:
                return v[0]
            return 5
335

336
    def get_id(self, album_name, mb_album_id, artist_ids):
337 338
        """
            Get non compilation album id
339
            @param album_name as str
340
            @param mb_album_id as str
341 342
            @param artist_ids as [int]
            @return int
343
        """
344
        with SqlCursor(App().db) as sql:
345
            filters = (album_name,)
346 347
            if artist_ids:
                request = "SELECT albums.rowid FROM albums, album_artists\
348
                           WHERE name=? COLLATE NOCASE "
349 350 351
                if mb_album_id:
                    request += "AND albums.mb_album_id=? "
                    filters += (mb_album_id,)
352
                else:
353
                    request += "AND albums.mb_album_id IS NULL "
354 355 356
                request += "AND no_album_artist=0 AND\
                            album_artists.album_id=albums.rowid AND (1=0 "
                filters += tuple(artist_ids)
357 358 359 360 361 362 363
                for artist_id in artist_ids:
                    request += "OR artist_id=? "
                request += ")"
            else:
                request = "SELECT rowid FROM albums\
                           WHERE name=?\
                           AND no_album_artist=1"
364
            result = sql.execute(request, filters)
365 366
            v = result.fetchone()
            if v is not None:
367 368 369
                return v[0]
            return None

370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391
    def get_id_by_name_artists(self, album_name, artist_ids):
        """
            Get non compilation album id
            @param album_name as str
            @param artist_ids as [int]
            @return int
        """
        with SqlCursor(App().db) as sql:
            filters = (album_name,)
            request = "SELECT albums.rowid FROM albums, album_artists\
                       WHERE name=? COLLATE NOCASE AND\
                       album_artists.album_id=albums.rowid AND (1=0 "
            filters += tuple(artist_ids)
            for artist_id in artist_ids:
                request += "OR artist_id=? "
            request += ")"
            result = sql.execute(request, filters)
            v = result.fetchone()
            if v is not None:
                return v[0]
            return None

392 393 394 395 396 397 398 399 400 401 402 403 404 405 406
    def set_genre_ids(self, album_id, genre_ids):
        """
            Set genre_ids for album
            @param album_id as int
            @param genre_ids as [int]
        """
        with SqlCursor(App().db) as sql:
            request = "DELETE from album_genres\
                       WHERE album_genres.album_id=?"
            sql.execute(request, (album_id,))
            for genre_id in genre_ids:
                request = "INSERT INTO album_genres (album_id, genre_id)\
                           VALUES (?, ?)"
                sql.execute(request, (album_id, genre_id))

407
    def get_genre_ids(self, album_id):
408 409
        """
            Get genre ids
410
            @param album_id as int
411 412
            @return Genres id as [int]
        """
413
        with SqlCursor(App().db) as sql:
414 415 416
            result = sql.execute("SELECT genre_id FROM album_genres\
                                  WHERE album_id=?", (album_id,))
            return list(itertools.chain(*result))
417

418
    def get_name(self, album_id):
419 420
        """
            Get album name for album id
421 422
            @param album_id as int
            @return str
423
        """
424
        with SqlCursor(App().db) as sql:
425 426 427 428 429
            result = sql.execute("SELECT name FROM albums where rowid=?",
                                 (album_id,))
            v = result.fetchone()
            if v is not None:
                return v[0]
430
            return None
431

432
    def get_artists(self, album_id):
433
        """
434
            Get artist names
435
            @param album_id as int
436
            @return artists as [str]
437
        """
438
        with SqlCursor(App().db) as sql:
439 440 441 442 443 444
            result = sql.execute("SELECT artists.name\
                                 FROM artists, album_artists\
                                 WHERE album_artists.album_id=?\
                                 AND album_artists.artist_id=artists.rowid",
                                 (album_id,))
            return list(itertools.chain(*result))
445

446
    def get_artist_ids(self, album_id):
447 448 449
        """
            Get album artist id
            @param album_id
450
            @return artist ids as [int]
451
        """
452
        with SqlCursor(App().db) as sql:
453 454 455
            result = sql.execute("SELECT artist_id\
                                  FROM album_artists\
                                  WHERE album_id=?",
456
                                 (album_id,))
457
            return list(itertools.chain(*result))
458

459 460 461
    def get_mb_album_id(self, album_id):
        """
            Get MusicBrainz album id for album id
462
            @param album_id as int
463 464 465 466 467 468 469 470 471 472
            @return MusicBrainz album id as str
        """
        with SqlCursor(App().db) as sql:
            result = sql.execute("SELECT mb_album_id FROM albums\
                                  WHERE rowid=?", (album_id,))
            v = result.fetchone()
            if v is not None:
                return v[0]
            return -1

Cédric Bellegarde's avatar
Cédric Bellegarde committed
473 474 475 476 477 478 479 480 481 482 483 484 485 486
    def get_mtime(self, album_id):
        """
            Get modification time
            @param album_id as int
            @return modification time as int
        """
        with SqlCursor(App().db) as sql:
            request = "SELECT mtime FROM albums WHERE albums.rowid=?"
            result = sql.execute(request, (album_id,))
            v = result.fetchone()
            if v is not None:
                return v[0]
            return 0

487
    def get_year(self, album_id):
488 489
        """
            Get album year
490
            @param album_id as int
Cédric Bellegarde's avatar
Cédric Bellegarde committed
491
            @return album year as int
492
        """
493
        with SqlCursor(App().db) as sql:
494 495 496 497
            result = sql.execute("SELECT year FROM albums where rowid=?",
                                 (album_id,))
            v = result.fetchone()
            if v and v[0]:
Cédric Bellegarde's avatar
Cédric Bellegarde committed
498 499
                return v[0]
            return None
500

Cédric Bellegarde's avatar
Cédric Bellegarde committed
501
    def get_uri(self, album_id):
502
        """
Cédric Bellegarde's avatar
Cédric Bellegarde committed
503
            Get album uri for album id
504
            @param album_id as int
Cédric Bellegarde's avatar
Cédric Bellegarde committed
505
            @return Album uri as string
506
        """
507
        with SqlCursor(App().db) as sql:
Cédric Bellegarde's avatar
Cédric Bellegarde committed
508
            result = sql.execute("SELECT uri FROM albums WHERE rowid=?",
509
                                 (album_id,))
Cédric Bellegarde's avatar
Cédric Bellegarde committed
510
            uri = ""
511 512
            v = result.fetchone()
            if v is not None:
Cédric Bellegarde's avatar
Cédric Bellegarde committed
513 514 515 516 517 518
                uri = v[0]
            return uri

    def get_uri_count(self, uri):
        """
            Count album having uri as album uri
519 520
            @param uri as str
            @return count as int
521
        """
522
        with SqlCursor(App().db) as sql:
523
            result = sql.execute("SELECT COUNT(uri) FROM albums WHERE uri=?",
Cédric Bellegarde's avatar
Cédric Bellegarde committed
524
                                 (uri,))
525 526 527 528
            v = result.fetchone()
            if v is not None:
                return v[0]
            return 1
529

530 531 532 533 534 535 536 537 538
    def get_uris(self):
        """
            Get all albums uri
            @return [str]
        """
        with SqlCursor(App().db) as sql:
            result = sql.execute("SELECT uri FROM albums")
            return list(itertools.chain(*result))

539 540 541
    def get_tracks_count(self, album_id):
        """
            Return tracks count
542
            @param album_id as int
543 544
            @return count as int
        """
545
        with SqlCursor(App().db) as sql:
546 547 548 549 550 551 552 553
            result = sql.execute("SELECT COUNT(tracks.rowid)\
                                  FROM tracks WHERE album_id=?",
                                 (album_id,))
            v = result.fetchone()
            if v is not None:
                return v[0]
            return 1

554 555 556 557
    def get_rated(self, limit=100):
        """
            Get albums with user rating >= 4
            @param limit as int
558
            @return [int]
559
        """
560
        with SqlCursor(App().db) as sql:
561
            request = "SELECT DISTINCT albums.rowid\
562 563
                       FROM albums\
                       WHERE rate>=4 AND loved != -1 AND mtime != 0\
564 565
                       ORDER BY popularity DESC LIMIT ?"
            result = sql.execute(request, (limit,))
566 567 568
            return list(itertools.chain(*result))

    def get_populars(self, limit=100):
569
        """
570
            Get popular albums
571
            @param limit as int
572
            @return [int]
573
        """
574
        with SqlCursor(App().db) as sql:
575
            request = "SELECT DISTINCT albums.rowid FROM albums\
576
                       WHERE popularity!=0 AND loved != -1 AND mtime != 0\
577 578
                       ORDER BY popularity DESC LIMIT ?"
            result = sql.execute(request, (limit,))
579
            return list(itertools.chain(*result))
580

581
    def get_loved_albums(self):
582
        """
583 584
            Get loved albums
            @return [int]
585
        """
586
        with SqlCursor(App().db) as sql:
587
            request = "SELECT DISTINCT albums.rowid\
588
                       FROM albums\
589 590
                       WHERE loved=1 AND\
                       mtime != 0 ORDER BY popularity DESC"
591
            result = sql.execute(request)
592 593
            return list(itertools.chain(*result))

594
    def get_recents(self):
595 596
        """
            Return recent albums
597
            @return [int]
598
        """
599
        with SqlCursor(App().db) as sql:
600
            request = "SELECT DISTINCT albums.rowid FROM albums\
601 602
                       WHERE albums.loved != -1 AND\
                       albums.mtime != 0\
603 604
                       ORDER BY mtime DESC LIMIT 100"
            result = sql.execute(request)
605
            return list(itertools.chain(*result))
Cédric Bellegarde's avatar
Cédric Bellegarde committed
606

607
    def get_randoms(self):
608 609
        """
            Return random albums
610
            @return [int]
611
        """
612 613
        if self.__cached_randoms:
            return self.__cached_randoms
614
        with SqlCursor(App().db) as sql:
615
            albums = []
616
            request = "SELECT DISTINCT albums.rowid FROM albums\
617 618
                       WHERE albums.loved != -1 AND\
                       albums.mtime != 0 ORDER BY random() LIMIT 100"
619
            result = sql.execute(request)
620
            albums = list(itertools.chain(*result))
621
            self.__cached_randoms = list(albums)
622
            return albums
623

624
    def clear_cached_randoms(self):
625
        """
626
            Clear cached random albums
627
        """
628
        self.__cached_randoms = []
629

630 631 632
    def get_disc_names(self, album_id, disc):
        """
            Get disc names
633
            @param album_id as int
634 635 636
            @param disc as int
            @return name as str
        """
637
        with SqlCursor(App().db) as sql:
638 639 640 641
            request = "SELECT DISTINCT discname\
                       FROM tracks\
                       WHERE tracks.album_id=?\
                       AND tracks.discnumber=?\
642
                       AND discname!=''"
643 644 645 646
            filters = (album_id, disc)
            result = sql.execute(request, filters)
            return list(itertools.chain(*result))

647
    def get_discs(self, album_id, genre_ids):
648 649
        """
            Get disc numbers
650
            @param album_id as int
651
            @param genre_ids as [int]
652
            @return [disc as int]
653
        """
654
        with SqlCursor(App().db) as sql:
655 656 657 658 659 660 661 662
            filters = (album_id,)
            filters += tuple(genre_ids)
            request = "SELECT DISTINCT discnumber\
                       FROM tracks, track_genres\
                       WHERE tracks.album_id=?\
                       AND track_genres.track_id = tracks.rowid"
            if genre_ids:
                request += " AND ("
663 664
                for genre_id in genre_ids:
                    request += "track_genres.genre_id=? OR "
665 666 667
                request += "1=0)"
            request += " ORDER BY discnumber"
            result = sql.execute(request, filters)
668 669
            return list(itertools.chain(*result))

670
    def get_track_uris(self, album_id):
671
        """
672
            Get track uris for album id/disc
673
            @param album_id as int
674
            @return [int]
675
        """
676
        with SqlCursor(App().db) as sql:
677
            request = "SELECT DISTINCT tracks.uri\
678 679
                       FROM tracks WHERE album_id=?"
            result = sql.execute(request, (album_id,))
680 681
            return list(itertools.chain(*result))

682 683
    def get_disc_track_ids(self, album_id, genre_ids, artist_ids,
                           disc, disallow_ignored_tracks):
684
        """
685 686
            Get tracks ids for album id disc

687
            @param album_id as int
688
            @param genre_ids as [int]
689
            @param artist_ids as [int]
690
            @param disc as int
691
            @param disallow_ignored_tracks as bool
692
            @return [int]
693
        """
694
        with SqlCursor(App().db) as sql:
695
            filters = (album_id, disc)
696
            request = "SELECT DISTINCT tracks.rowid\
697 698 699
                       FROM tracks"
            if genre_ids:
                request += ", track_genres"
700
                filters += tuple(genre_ids)
701 702 703 704 705 706 707
            if artist_ids:
                request += ", track_artists"
                filters += tuple(artist_ids)
            request += " WHERE album_id=?\
                       AND discnumber=?"
            if genre_ids:
                request += " AND track_genres.track_id = tracks.rowid AND ("
708 709
                for genre_id in genre_ids:
                    request += "track_genres.genre_id=? OR "
710 711 712 713 714 715
                request += "1=0)"
            if artist_ids:
                request += " AND track_artists.track_id=tracks.rowid AND ("
                for artist_id in artist_ids:
                    request += "track_artists.artist_id=? OR "
                request += "1=0)"
716 717
            if disallow_ignored_tracks:
                request += " AND tracks.loved != -1"
718
            request += " ORDER BY discnumber, tracknumber, tracks.name"
719
            result = sql.execute(request, filters)
720 721
            return list(itertools.chain(*result))

722 723 724 725 726 727
    def get_id_by_uri(self, uri):
        """
            Get album id for uri
            @param uri as str
            @return id as int
        """
728
        with SqlCursor(App().db) as sql:
729 730 731 732 733 734 735 736 737
            result = sql.execute("SELECT rowid\
                                  FROM albums\
                                  WHERE uri=?",
                                 (uri,))
            v = result.fetchone()
            if v is not None:
                return v[0]
            return 0

738
    def get_ids(self, artist_ids, genre_ids, ignore=False):
739 740
        """
            Get albums ids
741
            @param artist_ids as [int]
742
            @param genre_ids as [int]
743
            @param ignore as bool => ignore albums with loved == 1
744
            @return albums ids as [int]
745
        """
746
        orderby = App().settings.get_enum("orderby")
747
        if artist_ids or orderby == OrderBy.ARTIST:
748
            order = " ORDER BY artists.sortname\
749
                     COLLATE NOCASE COLLATE LOCALIZED,\
750
                     albums.timestamp,\
751 752 753
                     albums.name\
                     COLLATE NOCASE COLLATE LOCALIZED"
        elif orderby == OrderBy.NAME:
754
            order = " ORDER BY albums.name\
755 756
                     COLLATE NOCASE COLLATE LOCALIZED"
        elif orderby == OrderBy.YEAR:
757
            order = " ORDER BY albums.timestamp DESC,\
758 759 760
                     albums.name\
                     COLLATE NOCASE COLLATE LOCALIZED"
        else:
761
            order = " ORDER BY albums.popularity DESC,\
762 763 764
                     albums.name\
                     COLLATE NOCASE COLLATE LOCALIZED"

765
        with SqlCursor(App().db) as sql:
766
            result = []
767 768
            # Get albums for all artists
            if not artist_ids and not genre_ids:
Cédric Bellegarde's avatar
Cédric Bellegarde committed
769
                request = "SELECT DISTINCT albums.rowid\
770 771
                           FROM albums, album_artists, artists\
                           WHERE albums.rowid = album_artists.album_id AND\
772
                           albums.mtime!=0 AND\
773
                           artists.rowid = album_artists.artist_id"
774 775
                if ignore:
                    request += " AND albums.loved != -1"
776 777
                if not get_network_available("YOUTUBE"):
                    request += " AND albums.mtime != -1"
778
                request += order
779 780
                result = sql.execute(request)
            # Get albums for genres
781
            elif not artist_ids:
782
                filters = tuple(genre_ids)
783
                request = "SELECT DISTINCT albums.rowid FROM albums,\
784 785 786
                           album_genres, album_artists, artists\
                           WHERE albums.rowid = album_artists.album_id AND\
                           artists.rowid = album_artists.artist_id AND\
787
                           albums.mtime!=0 AND\
788
                           album_genres.album_id=albums.rowid AND ( "
789
                for genre_id in genre_ids:
790
                    request += "album_genres.genre_id=? OR "
791
                request += "1=0)"
792 793
                if ignore:
                    request += " AND albums.loved != -1"
794 795
                if not get_network_available("YOUTUBE"):
                    request += " AND albums.mtime != -1"
796
                request += order
Cédric Bellegarde's avatar
Cédric Bellegarde committed
797
                result = sql.execute(request, filters)
798 799
            # Get albums for artist
            elif not genre_ids:
800
                filters = tuple(artist_ids)
801
                request = "SELECT DISTINCT albums.rowid\
802 803
                           FROM albums, album_artists, artists\
                           WHERE album_artists.album_id=albums.rowid AND\
804
                           albums.mtime!=0 AND\
805
                           artists.rowid = album_artists.artist_id AND ("
806
                for artist_id in artist_ids:
807
                    request += "artists.rowid=? OR "
808
                request += "1=0)"
809 810
                if ignore:
                    request += " AND albums.loved != -1"
811 812
                if not get_network_available("YOUTUBE"):
                    request += " AND albums.mtime != -1"
813
                request += order
Cédric Bellegarde's avatar
Cédric Bellegarde committed
814
                result = sql.execute(request, filters)
815
            # Get albums for artist id and genre id
816
            else:
817
                filters = tuple(artist_ids)
818
                filters += tuple(genre_ids)
819
                request = "SELECT DISTINCT albums.rowid\
820 821 822
                           FROM albums, album_genres, album_artists, artists\
                           WHERE album_genres.album_id=albums.rowid AND\
                           artists.rowid = album_artists.artist_id AND\
823
                           albums.mtime!=0 AND\
824
                           album_artists.album_id=albums.rowid AND ("
825
                for artist_id in artist_ids:
826
                    request += "artists.rowid=? OR "
827 828
                request += "1=0) AND ("
                for genre_id in genre_ids:
829
                    request += "album_genres.genre_id=? OR "
830
                request += "1=0)"
831 832
                if ignore:
                    request += " AND albums.loved != -1"
833 834
                if not get_network_available("YOUTUBE"):
                    request += " AND albums.mtime != -1"
835
                request += order
836
                result = sql.execute(request, filters)
837 838
            return list(itertools.chain(*result))

839
    def get_compilation_ids(self, genre_ids, ignore=False):
840 841
        """
            Get all compilations
842 843 844
            @param genre_ids as [int]
            @param ignore as bool => ignore albums with loved == 1
            @return [int]
845
        """
846
        with SqlCursor(App().db) as sql:
847
            order = " ORDER BY albums.name, albums.timestamp"
848 849
            result = []
            # Get all compilations
850
            if not genre_ids or genre_ids[0] == Type.ALL:
851 852 853 854
                filters = (Type.COMPILATIONS,)
                request = "SELECT DISTINCT albums.rowid\
                           FROM albums, album_artists\
                           WHERE album_artists.artist_id=?\
855
                           AND albums.mtime != 0\
856 857 858
                           AND album_artists.album_id=albums.rowid"
                if ignore:
                    request += " AND albums.loved != -1"
859 860
                if not get_network_available("YOUTUBE"):
                    request += " AND albums.mtime != -1"
861 862
                request += order
                result = sql.execute(request, filters)
863 864
            # Get compilation for genre id
            else:
865 866
                filters = (Type.COMPILATIONS,)
                filters += tuple(genre_ids)
867
                request = "SELECT DISTINCT albums.rowid\
868
                           FROM albums, album_genres, album_artists\
869
                           WHERE album_genres.album_id=albums.rowid\
870
                           AND albums.mtime != 0\
871
                           AND albums.loved != -1\
872 873
                           AND album_artists.album_id=albums.rowid\
                           AND album_artists.artist_id=? AND ( "
874 875
                for genre_id in genre_ids:
                    request += "album_genres.genre_id=? OR "
876 877 878
                request += "1=0)"
                if ignore:
                    request += " AND albums.loved != -1"
879 880
                if not get_network_available("YOUTUBE"):
                    request += " AND albums.mtime != -1"
881
                request += order
882
                result = sql.execute(request, filters)
883 884
            return list(itertools.chain(*result))

885
    def get_duration(self, album_id, genre_ids):
886 887
        """
            Album duration in seconds
888
            @param album_id as int
889
            @param genre_ids as [int]
890 891
            @return album duration as int
        """
892
        with SqlCursor(App().db) as sql:
893 894 895 896 897 898 899 900 901 902 903
            if genre_ids and genre_ids[0] > 0:
                filters = (album_id,)
                filters += tuple(genre_ids)
                request = "SELECT SUM(duration)\
                           FROM tracks, track_genres\
                           WHERE tracks.album_id=?\
                           AND track_genres.track_id = tracks.rowid AND ("
                for genre_id in genre_ids:
                    request += "track_genres.genre_id=? OR "
                request += "1=0)"
                result = sql.execute(request, filters)
904 905 906 907 908 909 910
            else:
                result = sql.execute("SELECT SUM(duration) FROM tracks\
                                      WHERE album_id=?", (album_id,))
            v = result.fetchone()
            if v and v[0] is not None:
                return v[0]
            return 0
911

912 913 914 915
    def get_genres(self, album_id):
        """
            Return genres for album
        """
916
        with SqlCursor(App().db) as sql:
917 918 919 920 921 922 923 924
            result = sql.execute("SELECT genres.name\
                                  FROM albums, album_genres, genres\
                                  WHERE albums.rowid = ?\
                                  AND album_genres.album_id = albums.rowid\
                                  AND album_genres.genre_id = genres.rowid",
                                 (album_id,))
            return list(itertools.chain(*result))

925 926 927 928 929
    def get_never_listened_to(self):
        """
            Return random albums never listened to
            @return album ids as [int]
        """
930
        with SqlCursor(App().db) as sql:
931 932 933
            result = sql.execute("SELECT DISTINCT albums.rowid\
                                  FROM albums, tracks\
                                  WHERE tracks.ltime=0 AND\
934
                                  albums.loved != -1 AND\
Cédric Bellegarde's avatar
Cédric Bellegarde committed
935
                                  albums.mtime != 0 AND\
936 937 938 939 940
                                  albums.rowid=tracks.album_id\
                                  AND albums.popularity < 10\
                                  ORDER BY random() LIMIT 100")
            return list(itertools.chain(*result))

Cédric Bellegarde's avatar
Cédric Bellegarde committed
941
    def get_years(self):
942
        """
943 944
            Return all albums years and if unknown album exists
            @return ([int], bool)
945 946 947 948
        """
        with SqlCursor(App().db) as sql:
            result = sql.execute("SELECT albums.year FROM albums")
            years = []
949
            unknown = False
950
            for year in list(itertools.chain(*result)):
951 952 953
                if year is None:
                    unknown = True
                elif year not in years:
954
                    years.append(year)
955
            return (years, unknown)
956

Cédric Bellegarde's avatar
Cédric Bellegarde committed
957
    def get_albums_for_year(self, year, limit=-1):
958
        """
959
            Return albums for year
960
            @param year as int
Cédric Bellegarde's avatar
Cédric Bellegarde committed
961
            @param limit as int
962
            @return album ids as [int]
963 964
        """
        with SqlCursor(App().db) as sql:
Cédric Bellegarde's avatar
Cédric Bellegarde committed
965 966 967
            if limit != -1:
                result = sql.execute("SELECT albums.rowid\
                                      FROM albums\
968
                                      WHERE year=? AND mtime != 0\
969
                                      ORDER BY random() LIMIT ?",
Cédric Bellegarde's avatar
Cédric Bellegarde committed
970 971 972 973
                                     (year, limit))
            else:
                order = " ORDER BY artists.sortname\
                         COLLATE NOCASE COLLATE LOCALIZED,\
974
                         albums.timestamp,\
Cédric Bellegarde's avatar
Cédric Bellegarde committed
975 976
                         albums.name\
                         COLLATE NOCASE COLLATE LOCALIZED"
977 978 979 980 981
                if year == Type.NONE:
                    request = "SELECT DISTINCT albums.rowid\
                               FROM albums, album_artists, artists\
                               WHERE albums.rowid=album_artists.album_id AND\
                               artists.rowid=album_artists.artist_id AND\
982
                               albums.year is null AND albums.mtime != 0"
983 984 985 986 987 988
                    filter = ()
                else:
                    request = "SELECT DISTINCT albums.rowid\
                               FROM albums, album_artists, artists\
                               WHERE albums.rowid=album_artists.album_id AND\
                               artists.rowid=album_artists.artist_id AND\
989
                               albums.year=? AND albums.mtime != 0"
990
                    filter = (year,)
Cédric Bellegarde's avatar
Cédric Bellegarde committed
991
                request += order
992
                result = sql.execute(request, filter)
993 994
            return list(itertools.chain(*result))

995 996 997
    def get_compilations_for_year(self, year, limit=-1):
        """
            Return compilations for year
998
            @param year as int
999
            @param limit as int
1000
            @return album ids as [int]
1001 1002 1003 1004
        """
        with SqlCursor(App().db) as sql:
            if limit != -1:
                result = sql.execute("SELECT albums.rowid\
Cédric Bellegarde's avatar
Cédric Bellegarde committed
1005
                                      FROM albums, album_artists\
1006 1007
                                      WHERE album_artists.artist_id=?\
                                      AND album_artists.album_id=albums.rowid\
1008
                                      AND albums.mtime != 0\
1009 1010 1011
                                      AND albums.year=? LIMIT ?",
                                     (Type.COMPILATIONS, year, limit))
            else:
1012
                order = " ORDER BY albums.timestamp, albums.name\
1013 1014 1015 1016 1017 1018
                         COLLATE NOCASE COLLATE LOCALIZED"
                if year == Type.NONE:
                    request = "SELECT DISTINCT albums.rowid\
                               FROM albums, album_artists\
                               WHERE album_artists.artist_id=?\
                               AND album_artists.album_id=albums.rowid\
1019
                               AND albums.mtime != 0\
1020 1021 1022 1023 1024 1025 1026
                               AND albums.year is null"
                    filter = (Type.COMPILATIONS,)
                else:
                    request = "SELECT DISTINCT albums.rowid\
                               FROM albums, album_artists\
                               WHERE album_artists.artist_id=?\
                               AND album_artists.album_id=albums.rowid\
1027
                               AND albums.mtime != 0\
1028 1029 1030 1031 1032 1033
                               AND albums.year=?"
                    filter = (Type.COMPILATIONS, year)
                request += order
                result = sql.execute(request, filter)
            return list(itertools.chain(*result))

1034
    def search(self, searched):
1035 1036
        """
            Search for albums looking like string
1037
            @param searched as str
Cédric Bellegarde's avatar
Cédric Bellegarde committed
1038
            @return album ids as [int]
1039
        """
1040
        no_accents = noaccents(searched)
1041
        with SqlCursor(App().db) as sql:
1042 1043 1044 1045
            items = []
            for filter in [(no_accents + "%",),
                           ("%" + no_accents,),
                           ("%" + no_accents + "%",)]:
1046
                request = "SELECT albums.rowid FROM albums\
1047 1048
                           WHERE noaccents(name) LIKE ?\
                           AND albums.mtime!=0 LIMIT 25"
1049
                result = sql.execute(request, filter)
1050 1051
                items += list(itertools.chain(*result))
            return items
1052

1053
    def calculate_artist_ids(self, album_id):
1054
        """
1055 1056
            Calculate artist ids based on tracks
            @WARNING Be sure album already have a track
1057
            @param album_id as int