1# Copyright (c) 2014-2021 Cedric Bellegarde <cedric.bellegarde@adishatz.org> 2# This program is free software: you can redistribute it and/or modify 3# it under the terms of the GNU General Public License as published by 4# the Free Software Foundation, either version 3 of the License, or 5# (at your option) any later version. 6# This program is distributed in the hope that it will be useful, 7# but WITHOUT ANY WARRANTY; without even the implied warranty of 8# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the 9# GNU General Public License for more details. 10# You should have received a copy of the GNU General Public License 11# along with this program. If not, see <http://www.gnu.org/licenses/>. 12 13import itertools 14from time import time 15from random import shuffle 16 17from lollypop.sqlcursor import SqlCursor 18from lollypop.define import App, Type, OrderBy, StorageType, LovedFlags 19from lollypop.logger import Logger 20from lollypop.utils import remove_static, make_subrequest 21 22 23class AlbumsDatabase: 24 """ 25 Albums database helper 26 """ 27 28 def __init__(self, db): 29 """ 30 Init albums database object 31 @param db as Database 32 """ 33 self.__db = db 34 self.__max_count = 1 35 36 def add(self, album_name, mb_album_id, lp_album_id, artist_ids, 37 uri, loved, popularity, rate, synced, mtime, storage_type): 38 """ 39 Add a new album to database 40 @param album_name as str 41 @param mb_album_id as str 42 @param lp_album_id as str 43 @param artist_ids as int 44 @param uri as str 45 @param loved as bool 46 @param popularity as int 47 @param rate as int 48 @param synced as int 49 @param mtime as int 50 @param storage_type as int 51 @return inserted rowid as int 52 """ 53 with SqlCursor(self.__db, True) as sql: 54 result = sql.execute("INSERT INTO albums\ 55 (name, mb_album_id, lp_album_id,\ 56 no_album_artist, uri,\ 57 loved, popularity, rate, mtime, synced,\ 58 storage_type)\ 59 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", 60 (album_name, mb_album_id or None, lp_album_id, 61 artist_ids == [], uri, loved, popularity, 62 rate, mtime, synced, storage_type)) 63 for artist_id in artist_ids: 64 sql.execute("INSERT INTO album_artists\ 65 (album_id, artist_id)\ 66 VALUES (?, ?)", (result.lastrowid, artist_id)) 67 return result.lastrowid 68 69 def add_artist(self, album_id, artist_id): 70 """ 71 Add artist to track 72 @param album_id as int 73 @param artist_id as int 74 """ 75 with SqlCursor(self.__db, True) as sql: 76 artist_ids = self.get_artist_ids(album_id) 77 if artist_id not in artist_ids: 78 sql.execute("INSERT INTO " 79 "album_artists (album_id, artist_id)" 80 "VALUES (?, ?)", (album_id, artist_id)) 81 82 def add_genre(self, album_id, genre_id): 83 """ 84 Add genre to album 85 @param album_id as int 86 @param genre_id as int 87 """ 88 with SqlCursor(self.__db, True) as sql: 89 genres = self.get_genre_ids(album_id) 90 if genre_id not in genres: 91 sql.execute("INSERT INTO\ 92 album_genres (album_id, genre_id)\ 93 VALUES (?, ?)", 94 (album_id, genre_id)) 95 96 def set_artist_ids(self, album_id, artist_ids): 97 """ 98 Set artist id 99 @param album_id as int 100 @param artist_ids as [int] 101 """ 102 with SqlCursor(self.__db, True) as sql: 103 sql.execute("DELETE FROM album_artists\ 104 WHERE album_id=?", (album_id,)) 105 for artist_id in artist_ids: 106 sql.execute("INSERT INTO album_artists\ 107 (album_id, artist_id)\ 108 VALUES (?, ?)", (album_id, artist_id)) 109 110 def set_synced(self, album_id, synced): 111 """ 112 Set album synced 113 @param album_id as int 114 @param synced as int 115 """ 116 with SqlCursor(self.__db, True) as sql: 117 sql.execute("UPDATE albums SET synced=? WHERE rowid=?", 118 (synced, album_id)) 119 120 def set_mtime(self, album_id, mtime): 121 """ 122 Set album mtime 123 @param album_id as int 124 @param mtime as int 125 """ 126 with SqlCursor(self.__db, True) as sql: 127 sql.execute("UPDATE albums SET mtime=? WHERE rowid=?", 128 (mtime, album_id)) 129 130 def set_lp_album_id(self, album_id, lp_album_id): 131 """ 132 Set lp album id 133 @param album_id as int 134 @param lp_album_id as int 135 """ 136 with SqlCursor(self.__db, True) as sql: 137 sql.execute("UPDATE albums SET lp_album_id=? WHERE rowid=?", 138 (lp_album_id, album_id)) 139 140 def set_loved(self, album_id, loved): 141 """ 142 Set album loved 143 @param album_id as int 144 @param loved as int 145 """ 146 with SqlCursor(self.__db, True) as sql: 147 sql.execute("UPDATE albums SET loved=? WHERE rowid=?", 148 (loved, album_id)) 149 150 def set_rate(self, album_id, rate): 151 """ 152 Set album rate 153 @param album_id as int 154 @param rate as int 155 """ 156 with SqlCursor(self.__db, True) as sql: 157 sql.execute("UPDATE albums SET rate=? WHERE rowid=?", 158 (rate, album_id)) 159 160 def set_year(self, album_id, year): 161 """ 162 Set year 163 @param album_id as int 164 @param year as int 165 """ 166 with SqlCursor(self.__db, True) as sql: 167 sql.execute("UPDATE albums SET year=? WHERE rowid=?", 168 (year, album_id)) 169 170 def set_timestamp(self, album_id, timestamp): 171 """ 172 Set timestamp 173 @param album_id as int 174 @param timestamp as int 175 """ 176 with SqlCursor(self.__db, True) as sql: 177 sql.execute("UPDATE albums SET timestamp=? WHERE rowid=?", 178 (timestamp, album_id)) 179 180 def set_uri(self, album_id, uri): 181 """ 182 Set album uri for album id 183 @param album_id as int 184 @param uri as string 185 """ 186 with SqlCursor(self.__db, True) as sql: 187 sql.execute("UPDATE albums SET uri=? WHERE rowid=?", 188 (uri, album_id)) 189 190 def set_storage_type(self, album_id, storage_type): 191 """ 192 Set storage type 193 @param album_id as int 194 @param storage_type as int 195 """ 196 with SqlCursor(self.__db, True) as sql: 197 sql.execute("UPDATE albums SET storage_type=?\ 198 WHERE rowid=?", 199 (storage_type, album_id)) 200 201 def set_popularity(self, album_id, popularity): 202 """ 203 Set popularity 204 @param album_id as int 205 @param popularity as int 206 """ 207 with SqlCursor(self.__db, True) as sql: 208 try: 209 sql.execute("UPDATE albums set popularity=? WHERE rowid=?", 210 (popularity, album_id)) 211 except: # Database is locked 212 pass 213 214 def get_synced_ids(self, index): 215 """ 216 Get synced album ids 217 @param index as int => device index from gsettings 218 """ 219 with SqlCursor(self.__db) as sql: 220 request = "SELECT DISTINCT albums.rowid\ 221 FROM albums, artists, album_artists\ 222 WHERE album_artists.album_id = albums.rowid\ 223 AND (album_artists.artist_id = artists.rowid\ 224 OR album_artists.artist_id=?)\ 225 AND synced & (1 << ?) AND albums.storage_type & ?" 226 order = " ORDER BY artists.sortname\ 227 COLLATE NOCASE COLLATE LOCALIZED,\ 228 albums.timestamp,\ 229 albums.name\ 230 COLLATE NOCASE COLLATE LOCALIZED" 231 filters = (Type.COMPILATIONS, index, StorageType.COLLECTION) 232 result = sql.execute(request + order, filters) 233 return list(itertools.chain(*result)) 234 235 def get_synced(self, album_id): 236 """ 237 Get album synced status 238 @param album_id as int 239 @return int 240 """ 241 with SqlCursor(self.__db) as sql: 242 result = sql.execute("SELECT synced FROM albums WHERE\ 243 rowid=?", (album_id,)) 244 v = result.fetchone() 245 if v is not None: 246 return v[0] 247 return 0 248 249 def get_loved(self, album_id): 250 """ 251 Get album loved 252 @param album_id as int 253 @return int 254 """ 255 with SqlCursor(self.__db) as sql: 256 result = sql.execute("SELECT loved FROM albums WHERE\ 257 rowid=?", (album_id,)) 258 259 v = result.fetchone() 260 if v is not None: 261 return v[0] 262 return 0 263 264 def get_storage_type(self, album_id): 265 """ 266 Get storage type 267 @param album_id as int 268 @return int 269 """ 270 with SqlCursor(self.__db) as sql: 271 result = sql.execute("SELECT storage_type FROM albums WHERE\ 272 rowid=?", (album_id,)) 273 274 v = result.fetchone() 275 if v is not None: 276 return v[0] 277 return StorageType.NONE 278 279 def get_for_storage_type(self, storage_type, limit=-1): 280 """ 281 Get albums by storage type 282 @param storage_type as StorageType 283 @param limit as int 284 @return [int] 285 """ 286 with SqlCursor(self.__db) as sql: 287 filters = (storage_type, limit) 288 request = "SELECT rowid\ 289 FROM albums\ 290 WHERE storage_type=? ORDER BY RANDOM() LIMIT ?" 291 result = sql.execute(request, filters) 292 return list(itertools.chain(*result)) 293 294 def get_newer_for_storage_type(self, storage_type, timestamp): 295 """ 296 Get albums newer than timestamp for storage type 297 @param storage_type as StorageType 298 @param timestamp as int 299 @return [int] 300 """ 301 with SqlCursor(self.__db) as sql: 302 filters = (storage_type, timestamp) 303 request = "SELECT rowid FROM albums\ 304 WHERE storage_type=? and mtime>?" 305 result = sql.execute(request, filters) 306 return list(itertools.chain(*result)) 307 308 def get_oldest_for_storage_type(self, storage_type, limit): 309 """ 310 Get albums by storage type 311 @param storage_type as StorageType 312 @param limit as int 313 @return [int] 314 """ 315 with SqlCursor(self.__db) as sql: 316 filters = (storage_type, limit) 317 request = "SELECT rowid FROM albums\ 318 WHERE storage_type&? ORDER BY mtime ASC LIMIT ?" 319 result = sql.execute(request, filters) 320 return list(itertools.chain(*result)) 321 322 def get_count_for_storage_type(self, storage_type): 323 """ 324 Get albums count for storage type 325 @param storage_type as StorageType 326 @return int 327 """ 328 with SqlCursor(self.__db) as sql: 329 filters = (storage_type,) 330 request = "SELECT COUNT(*) FROM albums WHERE storage_type=?" 331 result = sql.execute(request, filters) 332 v = result.fetchone() 333 if v is not None: 334 return v[0] 335 return 0 336 337 def get_rate(self, album_id): 338 """ 339 Get album rate 340 @param album_id as int 341 @return rate as int 342 """ 343 with SqlCursor(self.__db) as sql: 344 result = sql.execute("SELECT rate FROM albums WHERE\ 345 rowid=?", (album_id,)) 346 347 v = result.fetchone() 348 if v: 349 return v[0] 350 return 0 351 352 def get_popularity(self, album_id): 353 """ 354 Get popularity 355 @param album_id as int 356 @return popularity as int 357 """ 358 with SqlCursor(self.__db) as sql: 359 result = sql.execute("SELECT popularity FROM albums WHERE\ 360 rowid=?", (album_id,)) 361 362 v = result.fetchone() 363 if v is not None: 364 return v[0] 365 return 0 366 367 def set_more_popular(self, album_id, pop_to_add): 368 """ 369 Increment popularity field for album id 370 @param album_id as int 371 @param pop_to_add as int 372 @raise sqlite3.OperationalError on db update 373 """ 374 with SqlCursor(self.__db, True) as sql: 375 # First increment popularity 376 result = sql.execute("SELECT popularity FROM albums WHERE rowid=?", 377 (album_id,)) 378 pop = result.fetchone() 379 if pop: 380 current = pop[0] 381 else: 382 current = 0 383 current += pop_to_add 384 sql.execute("UPDATE albums SET popularity=? WHERE rowid=?", 385 (current, album_id)) 386 # Then increment timed popularity 387 result = sql.execute("SELECT popularity\ 388 FROM albums_timed_popularity\ 389 WHERE album_id=?", 390 (album_id,)) 391 pop = result.fetchone() 392 mtime = int(time()) 393 if pop is not None: 394 popularity = pop[0] + pop_to_add 395 sql.execute("UPDATE albums_timed_popularity\ 396 SET popularity=?, mtime=?\ 397 WHERE album_id=?", 398 (popularity, mtime, album_id)) 399 else: 400 sql.execute("INSERT INTO albums_timed_popularity\ 401 (album_id, popularity, mtime)\ 402 VALUES (?, 1, ?)", 403 (album_id, mtime)) 404 405 def get_higher_popularity(self): 406 """ 407 Get higher available popularity 408 @return int 409 """ 410 with SqlCursor(self.__db) as sql: 411 result = sql.execute("SELECT popularity\ 412 FROM albums\ 413 ORDER BY POPULARITY DESC LIMIT 1") 414 v = result.fetchone() 415 if v is not None: 416 return v[0] 417 return 0 418 419 def get_avg_popularity(self): 420 """ 421 Return avarage popularity 422 @return avarage popularity as int 423 """ 424 with SqlCursor(self.__db) as sql: 425 result = sql.execute("SELECT AVG(popularity)\ 426 FROM (SELECT popularity\ 427 FROM albums\ 428 ORDER BY POPULARITY DESC LIMIT 1000)") 429 v = result.fetchone() 430 if v and v[0] is not None and v[0] > 5: 431 return v[0] 432 return 5 433 434 def get_id(self, album_name, mb_album_id, artist_ids): 435 """ 436 Get non compilation album id 437 @param album_name as str 438 @param mb_album_id as str 439 @param artist_ids as [int] 440 @return int 441 """ 442 with SqlCursor(self.__db) as sql: 443 filters = (album_name,) 444 if artist_ids: 445 request = "SELECT albums.rowid FROM albums, album_artists\ 446 WHERE name=? COLLATE NOCASE " 447 if mb_album_id: 448 request += "AND albums.mb_album_id=? " 449 filters += (mb_album_id,) 450 else: 451 request += "AND albums.mb_album_id IS NULL " 452 request += "AND no_album_artist=0 AND\ 453 album_artists.album_id=albums.rowid AND" 454 request += make_subrequest("artist_id=?", 455 "OR", 456 len(artist_ids)) 457 filters += tuple(artist_ids) 458 else: 459 request = "SELECT rowid FROM albums\ 460 WHERE name=?\ 461 AND no_album_artist=1 " 462 if mb_album_id: 463 request += "AND albums.mb_album_id=? " 464 filters += (mb_album_id,) 465 else: 466 request += "AND albums.mb_album_id IS NULL " 467 result = sql.execute(request, filters) 468 v = result.fetchone() 469 if v is not None: 470 return v[0] 471 return None 472 473 def get_id_for_escaped_string(self, album_name, artist_ids): 474 """ 475 Get album for name and artists 476 @param album_name as escaped str 477 @param artist_ids as [int] 478 @return int 479 """ 480 with SqlCursor(self.__db) as sql: 481 filters = (album_name,) 482 request = "SELECT albums.rowid FROM albums, album_artists\ 483 WHERE sql_escape(name)=? COLLATE NOCASE AND\ 484 album_artists.album_id=albums.rowid" 485 if artist_ids: 486 request += " AND (1=0 " 487 filters += tuple(artist_ids) 488 for artist_id in artist_ids: 489 request += "OR artist_id=? " 490 request += ")" 491 result = sql.execute(request, filters) 492 v = result.fetchone() 493 if v is not None: 494 return v[0] 495 return None 496 497 def set_genre_ids(self, album_id, genre_ids): 498 """ 499 Set genre_ids for album 500 @param album_id as int 501 @param genre_ids as [int] 502 """ 503 with SqlCursor(self.__db) as sql: 504 request = "DELETE from album_genres\ 505 WHERE album_genres.album_id=?" 506 sql.execute(request, (album_id,)) 507 for genre_id in genre_ids: 508 request = "INSERT INTO album_genres (album_id, genre_id)\ 509 VALUES (?, ?)" 510 sql.execute(request, (album_id, genre_id)) 511 512 def get_genre_ids(self, album_id): 513 """ 514 Get genre ids 515 @param album_id as int 516 @return Genres id as [int] 517 """ 518 with SqlCursor(self.__db) as sql: 519 result = sql.execute("SELECT genre_id FROM album_genres\ 520 WHERE album_id=?", (album_id,)) 521 return list(itertools.chain(*result)) 522 523 def get_name(self, album_id): 524 """ 525 Get album name for album id 526 @param album_id as int 527 @return str 528 """ 529 with SqlCursor(self.__db) as sql: 530 result = sql.execute("SELECT name FROM albums where rowid=?", 531 (album_id,)) 532 v = result.fetchone() 533 if v is not None: 534 return v[0] 535 return "" 536 537 def get_artists(self, album_id): 538 """ 539 Get artist names 540 @param album_id as int 541 @return artists as [str] 542 """ 543 with SqlCursor(self.__db) as sql: 544 result = sql.execute("SELECT artists.name\ 545 FROM artists, album_artists\ 546 WHERE album_artists.album_id=?\ 547 AND album_artists.artist_id=artists.rowid", 548 (album_id,)) 549 return list(itertools.chain(*result)) 550 551 def get_artist_ids(self, album_id): 552 """ 553 Get album artist id 554 @param album_id 555 @return artist ids as [int]artist_ids 556 """ 557 with SqlCursor(self.__db) as sql: 558 result = sql.execute("SELECT artist_id\ 559 FROM album_artists\ 560 WHERE album_id=?", 561 (album_id,)) 562 return list(itertools.chain(*result)) 563 564 def get_mb_album_id(self, album_id): 565 """ 566 Get MusicBrainz album id for album id 567 @param album_id as int 568 @return MusicBrainz album id as str 569 """ 570 with SqlCursor(self.__db) as sql: 571 result = sql.execute("SELECT mb_album_id FROM albums\ 572 WHERE rowid=?", (album_id,)) 573 v = result.fetchone() 574 if v is not None: 575 return v[0] 576 return "" 577 578 def get_id_for_lp_album_id(self, lp_album_id): 579 """ 580 Get album id for Lollypop recording id 581 @param Lollypop id as str 582 @return album id as int 583 """ 584 with SqlCursor(self.__db) as sql: 585 result = sql.execute("SELECT rowid FROM albums\ 586 WHERE lp_album_id=?", (lp_album_id,)) 587 v = result.fetchone() 588 if v is not None: 589 return v[0] 590 return -1 591 592 def get_mtime(self, album_id): 593 """ 594 Get modification time 595 @param album_id as int 596 @return modification time as int 597 """ 598 with SqlCursor(self.__db) as sql: 599 request = "SELECT mtime FROM albums WHERE albums.rowid=?" 600 result = sql.execute(request, (album_id,)) 601 v = result.fetchone() 602 if v is not None: 603 return v[0] 604 return 0 605 606 def get_year(self, album_id): 607 """ 608 Get album year 609 @param album_id as int 610 @return int 611 """ 612 with SqlCursor(self.__db) as sql: 613 result = sql.execute("SELECT year FROM albums where rowid=?", 614 (album_id,)) 615 v = result.fetchone() 616 if v and v[0]: 617 return v[0] 618 return None 619 620 def get_trackcount(self, album_id): 621 """ 622 Get track count, only used to load tracks after album created 623 @param album_id as int 624 @return int 625 """ 626 with SqlCursor(self.__db) as sql: 627 result = sql.execute("SELECT trackcount FROM albums where rowid=?", 628 (album_id,)) 629 v = result.fetchone() 630 if v and v[0]: 631 return v[0] 632 return 0 633 634 def get_lp_album_id(self, album_id): 635 """ 636 Get Lollypop id 637 @param album_id as int 638 @return str 639 """ 640 with SqlCursor(self.__db) as sql: 641 result = sql.execute("SELECT lp_album_id FROM\ 642 albums where rowid=?", 643 (album_id,)) 644 v = result.fetchone() 645 if v and v[0]: 646 return v[0] 647 return "" 648 649 def get_uri(self, album_id): 650 """ 651 Get album uri for album id 652 @param album_id as int 653 @return uri 654 """ 655 with SqlCursor(self.__db) as sql: 656 result = sql.execute("SELECT uri FROM albums WHERE rowid=?", 657 (album_id,)) 658 uri = "" 659 v = result.fetchone() 660 if v is not None: 661 uri = v[0] 662 return uri 663 664 def get_uri_count(self, uri): 665 """ 666 Count album having uri as album uri 667 @param uri as str 668 @return count as int 669 """ 670 with SqlCursor(self.__db) as sql: 671 result = sql.execute("SELECT COUNT(uri) FROM albums WHERE uri=?", 672 (uri,)) 673 v = result.fetchone() 674 if v is not None: 675 return v[0] 676 return 1 677 678 def get_uris(self): 679 """ 680 Get all albums uri 681 @return [str] 682 """ 683 with SqlCursor(self.__db) as sql: 684 result = sql.execute("SELECT uri FROM albums") 685 return list(itertools.chain(*result)) 686 687 def get_rated(self, storage_type, skipped, limit): 688 """ 689 Get albums with user rating >= 4 690 @param limit as int 691 @para skipped as bool 692 @param storage_type as StorageType 693 @return [int] 694 """ 695 with SqlCursor(self.__db) as sql: 696 filters = (storage_type,) 697 request = "SELECT DISTINCT albums.rowid\ 698 FROM albums\ 699 WHERE rate>=4 AND storage_type & ?" 700 if not skipped: 701 request += " AND not loved & ?" 702 filters += (LovedFlags.SKIPPED,) 703 request += "ORDER BY popularity DESC LIMIT ?" 704 filters += (limit,) 705 result = sql.execute(request, filters) 706 return list(itertools.chain(*result)) 707 708 def get_populars(self, storage_type, skipped, limit): 709 """ 710 Get popular albums 711 @param storage_type as StorageType 712 @param limit as int 713 @param skipped as bool 714 @return [int] 715 """ 716 with SqlCursor(self.__db) as sql: 717 filters = (storage_type,) 718 request = "SELECT DISTINCT albums.rowid FROM albums\ 719 WHERE popularity!=0 AND storage_type & ?" 720 if not skipped: 721 request += " AND not loved & ?" 722 filters += (LovedFlags.SKIPPED,) 723 request += "ORDER BY popularity DESC LIMIT ?" 724 filters += (limit,) 725 result = sql.execute(request, filters) 726 return list(itertools.chain(*result)) 727 728 def get_populars_at_the_moment(self, storage_type, skipped, limit): 729 """ 730 Get popular albums at the moment 731 @param storage_type as StorageType 732 @param skipped as bool 733 @param limit as int 734 @return [int] 735 """ 736 with SqlCursor(self.__db) as sql: 737 filters = (storage_type,) 738 request = "SELECT DISTINCT albums.rowid\ 739 FROM albums, albums_timed_popularity\ 740 WHERE albums.storage_type & ? AND\ 741 albums.rowid = albums_timed_popularity.album_id" 742 if not skipped: 743 request += " AND not loved & ?" 744 filters += (LovedFlags.SKIPPED,) 745 request += "ORDER BY albums_timed_popularity.popularity DESC\ 746 LIMIT ?" 747 filters += (limit,) 748 result = sql.execute(request, filters) 749 album_ids = list(itertools.chain(*result)) 750 if album_ids: 751 return album_ids 752 return [] 753 754 def get_loved_albums(self, storage_type): 755 """ 756 Get loved albums 757 @param storage_type as StorageType 758 @return [int] 759 """ 760 with SqlCursor(self.__db) as sql: 761 request = "SELECT albums.rowid\ 762 FROM albums\ 763 WHERE loved & ? AND\ 764 storage_type & ? ORDER BY popularity DESC" 765 result = sql.execute(request, (LovedFlags.LOVED, storage_type,)) 766 return list(itertools.chain(*result)) 767 768 def get_recents(self, storage_type, skipped, limit): 769 """ 770 Return recent albums 771 @param storage_type as StorageType 772 @param skipped as bool 773 @param limit as int 774 @return [int] 775 """ 776 with SqlCursor(self.__db) as sql: 777 filters = (storage_type,) 778 request = "SELECT DISTINCT albums.rowid FROM albums\ 779 WHERE albums.storage_type & ?" 780 if not skipped: 781 request += " AND not loved & ?" 782 filters += (LovedFlags.SKIPPED,) 783 request += "ORDER BY mtime DESC LIMIT ?" 784 filters += (limit,) 785 result = sql.execute(request, filters) 786 return list(itertools.chain(*result)) 787 788 def get_randoms_by_albums(self, storage_type, genre_id, skipped, limit): 789 """ 790 Return random albums 791 @param storage_type as StorageType 792 @param genre_id as int 793 @param skipped as bool 794 @param limit as int 795 @return [int] 796 """ 797 with SqlCursor(self.__db) as sql: 798 if genre_id is not None: 799 filters = (storage_type, genre_id) 800 request = "SELECT DISTINCT albums.rowid\ 801 FROM albums, album_genres\ 802 WHERE albums.storage_type & ? AND\ 803 album_genres.album_id = albums.rowid AND\ 804 album_genres.genre_id = ?" 805 if not skipped: 806 request += " AND not loved & ?" 807 filters += (LovedFlags.SKIPPED,) 808 request += "ORDER BY random() LIMIT ?" 809 filters += (limit,) 810 else: 811 filters = (storage_type,) 812 request = "SELECT DISTINCT rowid FROM albums\ 813 WHERE storage_type & ?" 814 if not skipped: 815 request += " AND not loved & ?" 816 filters += (LovedFlags.SKIPPED,) 817 request += "ORDER BY random() LIMIT ?" 818 filters += (limit,) 819 result = sql.execute(request, filters) 820 albums = list(itertools.chain(*result)) 821 return albums 822 823 def get_randoms_by_artists(self, storage_type, genre_id, skipped, limit): 824 """ 825 Return random albums 826 @param storage_type as StorageType 827 @param genre_id as int 828 @param skipped as bool 829 @param limit as int 830 @return [int] 831 """ 832 with SqlCursor(self.__db) as sql: 833 if genre_id is not None: 834 filters = (storage_type, genre_id) 835 request = "SELECT rowid, artist_id FROM (\ 836 SELECT albums.rowid, album_artists.artist_id\ 837 FROM albums, album_genres, album_artists\ 838 WHERE albums.rowid = album_artists.album_id AND\ 839 albums.storage_type & ? AND\ 840 album_genres.album_id = albums.rowid AND\ 841 album_genres.genre_id = ?" 842 if not skipped: 843 request += " AND not loved & ?" 844 filters += (LovedFlags.SKIPPED,) 845 filters += (limit * 2, limit) 846 request += "ORDER BY random() LIMIT ?)\ 847 GROUP BY artist_id ORDER BY random() LIMIT ?" 848 else: 849 filters = (storage_type,) 850 request = "SELECT rowid, artist_id FROM (\ 851 SELECT albums.rowid, album_artists.artist_id\ 852 FROM albums, album_artists\ 853 WHERE albums.rowid = album_artists.album_id AND\ 854 albums.storage_type & ?" 855 if not skipped: 856 request += " AND not loved & ?" 857 filters += (LovedFlags.SKIPPED,) 858 filters += (limit * 2, limit) 859 request += "ORDER BY random() LIMIT ?)\ 860 GROUP BY artist_id ORDER BY random() LIMIT ?" 861 album_ids = [] 862 for (album_id, artist_id) in sql.execute(request, filters): 863 album_ids.append(album_id) 864 return album_ids 865 866 def get_randoms(self, storage_type, genre_id, skipped, limit): 867 """ 868 Return random albums 869 @param storage_type as StorageType 870 @param genre_id as int 871 @param skipped as bool 872 @param limit as int 873 @return [int] 874 """ 875 album_ids = self.get_randoms_by_artists(storage_type, genre_id, 876 skipped, limit) 877 diff = limit - len(album_ids) 878 if diff > 0: 879 album_ids += self.get_randoms_by_albums(storage_type, 880 genre_id, 881 skipped, 882 diff) 883 album_ids = list(set(album_ids)) 884 # We need to shuffle again as set() sort has sorted ids 885 shuffle(album_ids) 886 return album_ids 887 888 def get_disc_names(self, album_id, disc): 889 """ 890 Get disc names 891 @param album_id as int 892 @param disc as int 893 @return name as str 894 """ 895 with SqlCursor(self.__db) as sql: 896 request = "SELECT DISTINCT discname\ 897 FROM tracks\ 898 WHERE tracks.album_id=?\ 899 AND tracks.discnumber=?\ 900 AND discname!=''" 901 filters = (album_id, disc) 902 result = sql.execute(request, filters) 903 return list(itertools.chain(*result)) 904 905 def get_discs(self, album_id): 906 """ 907 Get disc numbers 908 @param album_id as int 909 @return [disc as int] 910 """ 911 with SqlCursor(self.__db) as sql: 912 request = "SELECT DISTINCT discnumber\ 913 FROM tracks\ 914 WHERE tracks.album_id=?\ 915 ORDER BY discnumber" 916 result = sql.execute(request, (album_id,)) 917 return list(itertools.chain(*result)) 918 919 def get_track_uris(self, album_id): 920 """ 921 Get track uris for album id/disc 922 @param album_id as int 923 @return [int] 924 """ 925 with SqlCursor(self.__db) as sql: 926 request = "SELECT DISTINCT tracks.uri\ 927 FROM tracks WHERE album_id=?" 928 result = sql.execute(request, (album_id,)) 929 return list(itertools.chain(*result)) 930 931 def get_disc_track_ids(self, album_id, genre_ids, artist_ids, 932 disc, storage_type, skipped): 933 """ 934 Get tracks ids for album id disc 935 936 @param album_id as int 937 @param genre_ids as [int] 938 @param artist_ids as [int] 939 @param disc as int 940 @param skipped as bool 941 @return [int] 942 """ 943 genre_ids = remove_static(genre_ids) 944 artist_ids = remove_static(artist_ids) 945 with SqlCursor(self.__db) as sql: 946 filters = (album_id, disc, storage_type) 947 request = "SELECT DISTINCT tracks.rowid\ 948 FROM tracks" 949 if genre_ids: 950 request += ", track_genres" 951 filters += tuple(genre_ids) 952 if artist_ids: 953 request += ", track_artists" 954 filters += tuple(artist_ids) 955 request += " WHERE album_id=? AND discnumber=? AND storage_type&?" 956 if genre_ids: 957 request += " AND track_genres.track_id = tracks.rowid AND" 958 request += make_subrequest("track_genres.genre_id=?", 959 "OR", 960 len(genre_ids)) 961 if artist_ids: 962 request += " AND track_artists.track_id=tracks.rowid AND" 963 request += make_subrequest("track_artists.artist_id=?", 964 "OR", 965 len(artist_ids)) 966 if not skipped: 967 request += " AND not tracks.loved & ?" 968 filters += (LovedFlags.SKIPPED,) 969 request += " ORDER BY discnumber, tracknumber, tracks.name" 970 result = sql.execute(request, filters) 971 return list(itertools.chain(*result)) 972 973 def get_tracks_count(self, album_id, genre_ids, artist_ids): 974 """ 975 Get tracks count for album 976 @param album_id as int 977 @param genre_ids as [int] 978 @param artist_ids as [int] 979 @return [int] 980 """ 981 genre_ids = remove_static(genre_ids) 982 artist_ids = remove_static(artist_ids) 983 with SqlCursor(self.__db) as sql: 984 filters = (album_id,) 985 request = "SELECT COUNT(*) FROM tracks" 986 if genre_ids: 987 request += ", track_genres" 988 filters += tuple(genre_ids) 989 if artist_ids: 990 request += ", track_artists" 991 filters += tuple(artist_ids) 992 request += " WHERE album_id=?" 993 if genre_ids: 994 request += " AND track_genres.track_id = tracks.rowid AND" 995 request += make_subrequest("track_genres.genre_id=?", 996 "OR", 997 len(genre_ids)) 998 if artist_ids: 999 request += " AND track_artists.track_id=tracks.rowid AND" 1000 request += make_subrequest("track_artists.artist_id=?", 1001 "OR", 1002 len(artist_ids)) 1003 result = sql.execute(request, filters) 1004 v = result.fetchone() 1005 if v is not None and v[0] > 0: 1006 return v[0] 1007 return 1 1008 1009 def get_id_by_uri(self, uri): 1010 """ 1011 Get album id for uri 1012 @param uri as str 1013 @return id as int 1014 """ 1015 with SqlCursor(self.__db) as sql: 1016 result = sql.execute("SELECT rowid\ 1017 FROM albums\ 1018 WHERE uri=?", 1019 (uri,)) 1020 v = result.fetchone() 1021 if v is not None: 1022 return v[0] 1023 return 0 1024 1025 def get_ids(self, genre_ids, artist_ids, storage_type, 1026 skipped=False, orderby=None): 1027 """ 1028 Get albums ids 1029 @param genre_ids as [int] 1030 @param artist_ids as [int] 1031 @param storage_type as StorageType 1032 @param skipped as bool 1033 @param orderby as OrderBy 1034 @return albums ids as [int] 1035 """ 1036 genre_ids = remove_static(genre_ids) 1037 artist_ids = remove_static(artist_ids) 1038 if orderby is None: 1039 orderby = App().settings.get_enum("orderby") 1040 if orderby == OrderBy.ARTIST_YEAR: 1041 order = " ORDER BY artists.sortname\ 1042 COLLATE NOCASE COLLATE LOCALIZED,\ 1043 albums.timestamp,\ 1044 albums.name\ 1045 COLLATE NOCASE COLLATE LOCALIZED" 1046 elif orderby == OrderBy.ARTIST_TITLE: 1047 order = " ORDER BY artists.sortname\ 1048 COLLATE NOCASE COLLATE LOCALIZED,\ 1049 albums.name\ 1050 COLLATE NOCASE COLLATE LOCALIZED" 1051 elif orderby == OrderBy.TITLE: 1052 order = " ORDER BY albums.name\ 1053 COLLATE NOCASE COLLATE LOCALIZED" 1054 elif orderby == OrderBy.YEAR_DESC: 1055 order = " ORDER BY albums.timestamp DESC,\ 1056 albums.name\ 1057 COLLATE NOCASE COLLATE LOCALIZED" 1058 elif orderby == OrderBy.YEAR_ASC: 1059 order = " ORDER BY albums.timestamp ASC,\ 1060 albums.name\ 1061 COLLATE NOCASE COLLATE LOCALIZED" 1062 else: 1063 order = " ORDER BY albums.popularity DESC,\ 1064 albums.name\ 1065 COLLATE NOCASE COLLATE LOCALIZED" 1066 1067 with SqlCursor(self.__db) as sql: 1068 result = [] 1069 # Get albums for all artists 1070 if not artist_ids and not genre_ids: 1071 filters = (storage_type,) 1072 request = "SELECT DISTINCT albums.rowid\ 1073 FROM albums, album_artists, artists\ 1074 WHERE albums.rowid = album_artists.album_id AND\ 1075 albums.storage_type & ? AND\ 1076 artists.rowid = album_artists.artist_id" 1077 if not skipped: 1078 request += " AND not albums.loved & ?" 1079 filters += (LovedFlags.SKIPPED,) 1080 request += order 1081 result = sql.execute(request, filters) 1082 # Get albums for genres 1083 elif not artist_ids: 1084 filters = (storage_type,) 1085 filters += tuple(genre_ids) 1086 request = "SELECT DISTINCT albums.rowid FROM albums,\ 1087 album_genres, album_artists, artists\ 1088 WHERE albums.rowid = album_artists.album_id AND\ 1089 artists.rowid = album_artists.artist_id AND\ 1090 albums.storage_type & ? AND\ 1091 album_genres.album_id=albums.rowid AND" 1092 request += make_subrequest("album_genres.genre_id=?", 1093 "OR", 1094 len(genre_ids)) 1095 if not skipped: 1096 request += " AND not albums.loved & ?" 1097 filters += (LovedFlags.SKIPPED,) 1098 request += order 1099 result = sql.execute(request, filters) 1100 # Get albums for artist 1101 elif not genre_ids: 1102 filters = (storage_type,) 1103 filters += tuple(artist_ids) 1104 request = "SELECT DISTINCT albums.rowid\ 1105 FROM albums, album_artists, artists\ 1106 WHERE album_artists.album_id=albums.rowid AND\ 1107 albums.storage_type & ? AND\ 1108 artists.rowid = album_artists.artist_id AND" 1109 request += make_subrequest("artists.rowid=?", 1110 "OR", 1111 len(artist_ids)) 1112 if not skipped: 1113 request += " AND not albums.loved & ?" 1114 filters += (LovedFlags.SKIPPED,) 1115 request += order 1116 result = sql.execute(request, filters) 1117 # Get albums for artist id and genre id 1118 else: 1119 filters = (storage_type,) 1120 filters += tuple(artist_ids) 1121 filters += tuple(genre_ids) 1122 request = "SELECT DISTINCT albums.rowid\ 1123 FROM albums, album_genres, album_artists, artists\ 1124 WHERE album_genres.album_id=albums.rowid AND\ 1125 artists.rowid = album_artists.artist_id AND\ 1126 albums.storage_type & ? AND\ 1127 album_artists.album_id=albums.rowid AND" 1128 request += make_subrequest("artists.rowid=?", 1129 "OR", 1130 len(artist_ids)) 1131 request += " AND " 1132 request += make_subrequest("album_genres.genre_id=?", 1133 "OR", 1134 len(genre_ids)) 1135 if not skipped: 1136 request += " AND not albums.loved & ?" 1137 filters += (LovedFlags.SKIPPED,) 1138 request += order 1139 result = sql.execute(request, filters) 1140 return list(itertools.chain(*result)) 1141 1142 def get_compilation_ids(self, genre_ids, storage_type, skipped=False): 1143 """ 1144 Get all compilations 1145 @param genre_ids as [int] 1146 @param storage_type as StorageType 1147 @param skipped as bool 1148 @return [int] 1149 """ 1150 genre_ids = remove_static(genre_ids) 1151 with SqlCursor(self.__db) as sql: 1152 order = " ORDER BY albums.name, albums.timestamp" 1153 result = [] 1154 # Get all compilations 1155 if not genre_ids: 1156 filters = (storage_type, Type.COMPILATIONS) 1157 request = "SELECT DISTINCT albums.rowid\ 1158 FROM albums, album_artists\ 1159 WHERE albums.storage_type & ?\ 1160 AND album_artists.artist_id=?\ 1161 AND album_artists.album_id=albums.rowid" 1162 if not skipped: 1163 request += " AND not albums.loved & ?" 1164 filters += (LovedFlags.SKIPPED,) 1165 request += order 1166 result = sql.execute(request, filters) 1167 # Get compilation for genre id 1168 else: 1169 filters = (storage_type, Type.COMPILATIONS) 1170 filters += tuple(genre_ids) 1171 request = "SELECT DISTINCT albums.rowid\ 1172 FROM albums, album_genres, album_artists\ 1173 WHERE album_genres.album_id=albums.rowid\ 1174 AND albums.storage_type & ?\ 1175 AND album_artists.album_id=albums.rowid\ 1176 AND album_artists.artist_id=? AND" 1177 request += make_subrequest("album_genres.genre_id=?", 1178 "OR", 1179 len(genre_ids)) 1180 if not skipped: 1181 request += " AND not albums.loved & ?" 1182 filters += (LovedFlags.SKIPPED,) 1183 request += order 1184 result = sql.execute(request, filters) 1185 return list(itertools.chain(*result)) 1186 1187 def get_duration(self, album_id, genre_ids, artist_ids, disc_number): 1188 """ 1189 Album duration in seconds 1190 @param album_id as int 1191 @param genre_ids as [int] 1192 @param artist_ids as [int] 1193 @param disc_number as int/None 1194 @return int 1195 """ 1196 genre_ids = remove_static(genre_ids) 1197 artist_ids = remove_static(artist_ids) 1198 with SqlCursor(self.__db) as sql: 1199 if genre_ids and artist_ids: 1200 filters = (album_id,) 1201 filters += tuple(genre_ids) 1202 filters += tuple(artist_ids) 1203 request = "SELECT SUM(duration)\ 1204 FROM tracks, track_genres, track_artists\ 1205 WHERE tracks.album_id=?\ 1206 AND track_genres.track_id = tracks.rowid\ 1207 AND track_artists.track_id = tracks.rowid AND" 1208 request += make_subrequest("track_genres.genre_id=?", 1209 "OR", 1210 len(genre_ids)) 1211 request += " AND " 1212 request += make_subrequest("track_artists.artist_id=?", 1213 "OR", 1214 len(artist_ids)) 1215 elif artist_ids: 1216 filters = (album_id,) 1217 filters += tuple(artist_ids) 1218 request = "SELECT SUM(duration)\ 1219 FROM tracks, track_artists\ 1220 WHERE tracks.album_id=?\ 1221 AND track_artists.track_id = tracks.rowid AND" 1222 request += make_subrequest("track_artists.artist_id=?", 1223 "OR", 1224 len(artist_ids)) 1225 elif genre_ids: 1226 filters = (album_id,) 1227 filters += tuple(genre_ids) 1228 request = "SELECT SUM(duration)\ 1229 FROM tracks, track_genres\ 1230 WHERE tracks.album_id=?\ 1231 AND track_genres.track_id = tracks.rowid AND" 1232 request += make_subrequest("track_genres.genre_id=?", 1233 "OR", 1234 len(genre_ids)) 1235 else: 1236 filters = (album_id,) 1237 request = "SELECT SUM(duration)\ 1238 FROM tracks\ 1239 WHERE tracks.album_id=?" 1240 if disc_number is not None: 1241 filters += (disc_number,) 1242 request += " AND discnumber=?" 1243 result = sql.execute(request, filters) 1244 v = result.fetchone() 1245 if v and v[0] is not None: 1246 return v[0] 1247 return 0 1248 1249 def get_genres(self, album_id): 1250 """ 1251 Return genres for album 1252 """ 1253 with SqlCursor(self.__db) as sql: 1254 result = sql.execute("SELECT genres.name\ 1255 FROM albums, album_genres, genres\ 1256 WHERE albums.rowid = ?\ 1257 AND album_genres.album_id = albums.rowid\ 1258 AND album_genres.genre_id = genres.rowid", 1259 (album_id,)) 1260 return list(itertools.chain(*result)) 1261 1262 def get_little_played(self, storage_type, skipped, limit): 1263 """ 1264 Return random albums little played 1265 @param storage_type as StorageType 1266 @param skipped as bool 1267 @param limit as int 1268 @return album ids as [int] 1269 """ 1270 with SqlCursor(self.__db) as sql: 1271 filters = (storage_type,) 1272 request = "SELECT album_id FROM tracks, albums\ 1273 WHERE albums.storage_type & ? AND albums.rowid=album_id" 1274 if not skipped: 1275 request += " AND not albums.loved & ?" 1276 filters += (LovedFlags.SKIPPED,) 1277 request += " GROUP BY album_id\ 1278 ORDER BY SUM(ltime)/COUNT(ltime), random() LIMIT ?" 1279 filters += (limit,) 1280 result = sql.execute(request, filters) 1281 return list(itertools.chain(*result)) 1282 1283 def search(self, searched, storage_type): 1284 """ 1285 Search for albums looking like string 1286 @param searched as str without accents 1287 @param storage_type as StorageType 1288 @return album ids as [int] 1289 """ 1290 with SqlCursor(self.__db) as sql: 1291 filters = ("%" + searched + "%", storage_type) 1292 request = "SELECT rowid, name FROM albums\ 1293 WHERE noaccents(name) LIKE ?\ 1294 AND albums.storage_type & ? LIMIT 25" 1295 result = sql.execute(request, filters) 1296 return list(result) 1297 1298 def calculate_artist_ids(self, album_id, disable_compilations): 1299 """ 1300 Calculate artist ids based on tracks 1301 @WARNING Be sure album already have a track 1302 @param album_id as int 1303 @param disable_compilations as bool 1304 @return artist_ids as [int] 1305 """ 1306 ret = [] 1307 try: 1308 with SqlCursor(self.__db) as sql: 1309 request = "SELECT DISTINCT rowid\ 1310 FROM tracks WHERE album_id=?" 1311 result = sql.execute(request, (album_id,)) 1312 for track_id in list(itertools.chain(*result)): 1313 artist_ids = App().tracks.get_artist_ids(track_id) 1314 if disable_compilations: 1315 for artist_id in artist_ids: 1316 if artist_id not in ret: 1317 ret.append(artist_id) 1318 else: 1319 # Check if previous track and 1320 # track do not have same artists 1321 if ret: 1322 if not set(ret) & set(artist_ids): 1323 return [Type.COMPILATIONS] 1324 ret = artist_ids 1325 except Exception as e: 1326 Logger.error("AlbumsDatabase::calculate_artist_ids(): %s" % e) 1327 return ret 1328 1329 def remove_device(self, index): 1330 """ 1331 Remove device from DB 1332 @param index as int => device index 1333 """ 1334 with SqlCursor(self.__db, True) as sql: 1335 sql.execute("UPDATE albums SET synced = synced & ~(1<<?)", 1336 (index,)) 1337 1338 def count(self): 1339 """ 1340 Count albums 1341 @return int 1342 """ 1343 with SqlCursor(self.__db) as sql: 1344 result = sql.execute("SELECT COUNT(1) FROM albums\ 1345 WHERE storage_type & ?", 1346 ((StorageType.COLLECTION | 1347 StorageType.SAVED,))) 1348 v = result.fetchone() 1349 if v is not None: 1350 return v[0] 1351 return 0 1352 1353 def clean(self, commit=True): 1354 """ 1355 Clean albums 1356 @param commit as bool 1357 """ 1358 storage_type = StorageType.EPHEMERAL |\ 1359 StorageType.COLLECTION | StorageType.EXTERNAL 1360 with SqlCursor(self.__db, commit) as sql: 1361 sql.execute("DELETE FROM albums WHERE\ 1362 albums.storage_type&? AND\ 1363 albums.rowid NOT IN (\ 1364 SELECT tracks.album_id FROM tracks)", 1365 (storage_type,)) 1366 sql.execute("DELETE FROM album_genres\ 1367 WHERE album_genres.album_id NOT IN (\ 1368 SELECT albums.rowid FROM albums)") 1369 sql.execute("DELETE FROM album_artists\ 1370 WHERE album_artists.album_id NOT IN (\ 1371 SELECT albums.rowid FROM albums)") 1372 sql.execute("DELETE FROM albums_timed_popularity\ 1373 WHERE albums_timed_popularity.album_id NOT IN (\ 1374 SELECT albums.rowid FROM albums)") 1375 # We clear timed popularity based on mtime 1376 # For now, we don't need to keep more data than a month 1377 month = int(time()) - 2678400 1378 sql.execute("DELETE FROM albums_timed_popularity\ 1379 WHERE albums_timed_popularity.mtime < ?", (month,)) 1380 1381 @property 1382 def max_count(self): 1383 """ 1384 Get MAX(COUNT(tracks)) for albums 1385 """ 1386 return self.__max_count 1387 1388 def update_max_count(self): 1389 """ 1390 Update MAX(COUNT(tracks)) for albums 1391 """ 1392 with SqlCursor(self.__db) as sql: 1393 result = sql.execute("SELECT MAX(num_tracks)\ 1394 FROM (SELECT COUNT(t.rowid)\ 1395 AS num_tracks FROM albums\ 1396 INNER JOIN tracks t\ 1397 ON albums.rowid=t.album_id\ 1398 GROUP BY albums.rowid)") 1399 v = result.fetchone() 1400 if v and v[0] is not None: 1401 self.__max_count = v[0] 1402 1403####################### 1404# PRIVATE # 1405####################### 1406