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 13 14from gettext import gettext as _ 15import itertools 16 17from lollypop.sqlcursor import SqlCursor 18from lollypop.define import App, StorageType, Type, LovedFlags 19from lollypop.utils import noaccents, make_subrequest 20 21 22class TracksDatabase: 23 """ 24 All functions take a sqlite cursor as last parameter, 25 set another one if you"re in a thread 26 """ 27 28 def __init__(self, db): 29 """ 30 Init tracks database object 31 @param db as database 32 """ 33 self.__db = db 34 35 def add(self, name, uri, duration, tracknumber, discnumber, discname, 36 album_id, year, timestamp, popularity, rate, loved, ltime, mtime, 37 mb_track_id, lp_track_id, bpm, storage_type): 38 """ 39 Add a new track to database 40 @param name as string 41 @param uri as string, 42 @param duration as int 43 @param tracknumber as int 44 @param discnumber as int 45 @param discname as str 46 @param album_id as int 47 @param year as int 48 @param timestamp as int 49 @param popularity as int 50 @param rate as int 51 @param loved as bool 52 @param ltime as int 53 @param mtime as int 54 @param mb_track_id as str 55 @param lp_track_id as str 56 @param bpm as double 57 @return inserted rowid as int 58 @warning: commit needed 59 """ 60 with SqlCursor(self.__db, True) as sql: 61 result = sql.execute( 62 "INSERT INTO tracks (name, uri, duration, tracknumber,\ 63 discnumber, discname, album_id,\ 64 year, timestamp, popularity, rate, loved,\ 65 ltime, mtime, mb_track_id, lp_track_id, bpm, storage_type)\ 66 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", 67 (name, uri, duration, tracknumber, discnumber, 68 discname, album_id, year, timestamp, popularity, 69 rate, loved, ltime, mtime, mb_track_id, lp_track_id, 70 bpm, storage_type)) 71 return result.lastrowid 72 73 def add_artist(self, track_id, artist_id): 74 """ 75 Add artist to track 76 @param track_id as int 77 @param artist_id as int 78 @warning: commit needed 79 """ 80 with SqlCursor(self.__db, True) as sql: 81 artists = self.get_artist_ids(track_id) 82 if artist_id not in artists: 83 sql.execute("INSERT INTO " 84 "track_artists (track_id, artist_id)" 85 "VALUES (?, ?)", (track_id, artist_id)) 86 87 def add_genre(self, track_id, genre_id): 88 """ 89 Add genre to track 90 @param track_id as int 91 @param genre_id as int 92 @warning: commit needed 93 """ 94 with SqlCursor(self.__db, True) as sql: 95 genres = self.get_genre_ids(track_id) 96 if genre_id not in genres: 97 sql.execute("INSERT INTO\ 98 track_genres (track_id, genre_id)\ 99 VALUES (?, ?)", 100 (track_id, genre_id)) 101 102 def get_ids(self, storage_type, skipped): 103 """ 104 Return all internal track ids 105 @param storage_type as StorageType 106 @param skipped as bool 107 @return track ids as [int] 108 """ 109 with SqlCursor(self.__db) as sql: 110 filters = (storage_type,) 111 request = "SELECT rowid FROM tracks\ 112 WHERE storage_type & ?" 113 if not skipped: 114 request += " AND not loved &? " 115 filters += (LovedFlags.SKIPPED,) 116 request += " ORDER BY album_id" 117 result = sql.execute(request, filters) 118 return list(itertools.chain(*result)) 119 120 def get_ids_for_name(self, name): 121 """ 122 Return tracks ids with name 123 @param name as str 124 @return track id as [int] 125 """ 126 with SqlCursor(self.__db) as sql: 127 result = sql.execute("SELECT rowid\ 128 FROM tracks WHERE noaccents(name)=?\ 129 COLLATE NOCASE", 130 (noaccents(name),)) 131 return list(itertools.chain(*result)) 132 133 def get_id_by_uri(self, uri): 134 """ 135 Return track id for uri 136 @param uri as str 137 @return track id as int 138 """ 139 with SqlCursor(self.__db) as sql: 140 result = sql.execute("SELECT rowid FROM tracks WHERE uri=?", 141 (uri,)) 142 v = result.fetchone() 143 if v is not None: 144 return v[0] 145 return None 146 147 def get_id_by_basename_duration(self, basename, duration): 148 """ 149 Get track id by basename 150 @param basename as str 151 @param duration as int 152 @return track_id as int 153 """ 154 with SqlCursor(self.__db) as sql: 155 result = sql.execute("SELECT rowid FROM tracks\ 156 WHERE uri like ? AND duration=?", 157 ("%" + basename, duration)) 158 v = result.fetchone() 159 if v is not None: 160 return v[0] 161 return None 162 163 def get_name(self, track_id): 164 """ 165 Get track name for track id 166 @param track_id as int 167 @return Name as string 168 """ 169 with SqlCursor(self.__db) as sql: 170 result = sql.execute("SELECT name FROM tracks WHERE rowid=?", 171 (track_id,)) 172 v = result.fetchone() 173 if v is not None: 174 return v[0] 175 return "" 176 177 def get_year(self, track_id): 178 """ 179 Get track year 180 @param track_id as int 181 @return year as int 182 """ 183 with SqlCursor(self.__db) as sql: 184 result = sql.execute("SELECT year FROM tracks WHERE rowid=?", 185 (track_id,)) 186 v = result.fetchone() 187 if v and v[0]: 188 return v[0] 189 return None 190 191 def get_storage_type(self, track_id): 192 """ 193 Get storage type 194 @param track_id as int 195 @return int 196 """ 197 with SqlCursor(self.__db) as sql: 198 result = sql.execute("SELECT storage_type FROM tracks WHERE\ 199 rowid=?", (track_id,)) 200 201 v = result.fetchone() 202 if v is not None: 203 return v[0] 204 return 0 205 206 def get_timestamp(self, track_id): 207 """ 208 Get track timestamp 209 @param track_id as int 210 @return timestamp as int 211 """ 212 with SqlCursor(self.__db) as sql: 213 result = sql.execute("SELECT timestamp FROM tracks WHERE rowid=?", 214 (track_id,)) 215 v = result.fetchone() 216 if v and v[0]: 217 return v[0] 218 return None 219 220 def get_timestamp_for_album(self, album_id): 221 """ 222 Get album timestamp based on tracks 223 Use most used timestamp by tracks 224 @param album_id as int 225 @return int 226 """ 227 with SqlCursor(self.__db) as sql: 228 result = sql.execute("SELECT timestamp,\ 229 COUNT(timestamp) AS occurrence\ 230 FROM tracks\ 231 WHERE tracks.album_id=?\ 232 GROUP BY timestamp\ 233 ORDER BY occurrence DESC\ 234 LIMIT 1", (album_id,)) 235 v = result.fetchone() 236 if v is not None: 237 return v[0] 238 return None 239 240 def get_rate(self, track_id): 241 """ 242 Get track rate 243 @param track_id as int 244 @return rate as int 245 """ 246 with SqlCursor(self.__db) as sql: 247 result = sql.execute("SELECT rate FROM tracks WHERE rowid=?", 248 (track_id,)) 249 v = result.fetchone() 250 if v: 251 return v[0] 252 return 0 253 254 def get_uri(self, track_id): 255 """ 256 Get track uri for track id 257 @param track_id as int 258 @return uri as string 259 """ 260 with SqlCursor(self.__db) as sql: 261 result = sql.execute("SELECT uri FROM tracks WHERE rowid=?", 262 (track_id,)) 263 v = result.fetchone() 264 if v is not None: 265 return v[0] 266 return "" 267 268 def set_uri(self, track_id, uri): 269 """ 270 Set track uri 271 @param track_id as int 272 @param uri as string 273 """ 274 with SqlCursor(self.__db, True) as sql: 275 sql.execute("UPDATE tracks SET uri=?\ 276 WHERE rowid=?", 277 (uri, track_id)) 278 279 def set_storage_type(self, track_id, storage_type): 280 """ 281 Set storage type 282 @param track_id as int 283 @param storage_type as int 284 """ 285 with SqlCursor(self.__db, True) as sql: 286 sql.execute("UPDATE tracks SET storage_type=?\ 287 WHERE rowid=?", 288 (storage_type, track_id)) 289 290 def set_rate(self, track_id, rate): 291 """ 292 Set track rate 293 @param track_id as int 294 @param rate as int 295 """ 296 with SqlCursor(self.__db, True) as sql: 297 sql.execute("UPDATE tracks SET rate=?\ 298 WHERE rowid=?", 299 (rate, track_id)) 300 301 def get_album_id(self, track_id): 302 """ 303 Get album id for track id 304 @param track_id as int 305 @return album id as int 306 """ 307 with SqlCursor(self.__db) as sql: 308 result = sql.execute("SELECT album_id FROM tracks WHERE rowid=?", 309 (track_id,)) 310 v = result.fetchone() 311 if v is not None: 312 return v[0] 313 return -1 314 315 def get_mb_track_id(self, track_id): 316 """ 317 Get MusicBrainz recording id for track id 318 @param track_id as int 319 @return recording id as str 320 """ 321 with SqlCursor(self.__db) as sql: 322 result = sql.execute("SELECT mb_track_id FROM tracks\ 323 WHERE rowid=?", (track_id,)) 324 v = result.fetchone() 325 if v is not None: 326 return v[0] 327 return "" 328 329 def get_id_for_lp_track_id(self, lp_track_id): 330 """ 331 Get track id for Lollypop recording id 332 @param Lollypop id as str 333 @return track id as int 334 """ 335 with SqlCursor(self.__db) as sql: 336 result = sql.execute("SELECT rowid FROM tracks\ 337 WHERE lp_track_id=?", (lp_track_id,)) 338 v = result.fetchone() 339 if v is not None: 340 return v[0] 341 return -1 342 343 def get_album_name(self, track_id): 344 """ 345 Get album name for track id 346 @param track_id as int 347 @return album name as str 348 """ 349 with SqlCursor(self.__db) as sql: 350 result = sql.execute("SELECT albums.name from albums,tracks\ 351 WHERE tracks.rowid=? AND\ 352 tracks.album_id=albums.rowid", (track_id,)) 353 v = result.fetchone() 354 if v is not None: 355 return v[0] 356 return _("Unknown") 357 358 def get_artist_ids(self, track_id): 359 """ 360 Get artist ids 361 @param track_id as int 362 @return artist ids as [int] 363 """ 364 with SqlCursor(self.__db) as sql: 365 result = sql.execute("SELECT artist_id FROM track_artists\ 366 WHERE track_id=?", (track_id,)) 367 return list(itertools.chain(*result)) 368 369 def get_mb_artist_ids(self, track_id): 370 """ 371 Get MusicBrainz artist ids 372 @param track_id as int 373 @return artist ids as [int] 374 """ 375 with SqlCursor(self.__db) as sql: 376 result = sql.execute("SELECT mb_artist_id\ 377 FROM artists, track_artists\ 378 WHERE track_artists.track_id=?\ 379 AND track_artists.artist_id=artists.rowid", 380 (track_id,)) 381 return list(itertools.chain(*result)) 382 383 def get_artists(self, track_id): 384 """ 385 Get artist names 386 @param track_id as int 387 @return artists as [str] 388 """ 389 with SqlCursor(self.__db) as sql: 390 result = sql.execute("SELECT name FROM artists, track_artists\ 391 WHERE track_artists.track_id=?\ 392 AND track_artists.artist_id=artists.rowid", 393 (track_id,)) 394 return list(itertools.chain(*result)) 395 396 def get_album_genre_ids(self, album_id): 397 """ 398 Get album genre ids based on tracks 399 @param album_id as int 400 """ 401 with SqlCursor(self.__db) as sql: 402 result = sql.execute("SELECT track_genres.genre_id\ 403 FROM tracks, track_genres\ 404 WHERE tracks.album_id=? AND\ 405 track_genres.track_id=tracks.rowid", 406 (album_id,)) 407 return list(itertools.chain(*result)) 408 409 def get_genre_ids(self, track_id): 410 """ 411 Get genre ids 412 @param track_id as int 413 @return genre ids as [int] 414 """ 415 with SqlCursor(self.__db) as sql: 416 result = sql.execute("SELECT genre_id FROM track_genres\ 417 WHERE track_id=?", (track_id,)) 418 return list(itertools.chain(*result)) 419 420 def get_genres(self, track_id): 421 """ 422 Get genres 423 @param track_id as int 424 @return [str] 425 """ 426 with SqlCursor(self.__db) as sql: 427 result = sql.execute("SELECT name FROM genres, track_genres\ 428 WHERE track_genres.track_id=?\ 429 AND track_genres.genre_id=genres.rowid", 430 (track_id,)) 431 return list(itertools.chain(*result)) 432 433 def get_mtimes(self): 434 """ 435 Get mtime for tracks 436 @return dict of {uri as string: mtime as int} 437 """ 438 with SqlCursor(self.__db) as sql: 439 mtimes = {} 440 result = sql.execute("SELECT DISTINCT uri, mtime\ 441 FROM tracks WHERE storage_type & ?", 442 (StorageType.COLLECTION,)) 443 for row in result: 444 mtimes.update((row,)) 445 return mtimes 446 447 def remove_album(self, album_id, commit=True): 448 """ 449 Remove album 450 @param album_id as int 451 @param commit as bool 452 """ 453 with SqlCursor(self.__db, commit) as sql: 454 sql.execute("DELETE FROM tracks WHERE album_id=?", (album_id,)) 455 456 def del_non_persistent(self, commit=True): 457 """ 458 Delete non persistent tracks 459 @param commit as bool 460 """ 461 with SqlCursor(self.__db, commit) as sql: 462 sql.execute("DELETE FROM tracks WHERE storage_type & ?", 463 (StorageType.EPHEMERAL | StorageType.EXTERNAL,)) 464 465 def del_persistent(self, commit=True): 466 """ 467 Delete persistent tracks 468 @param commit as bool 469 """ 470 with SqlCursor(self.__db, commit) as sql: 471 sql.execute("DELETE FROM tracks WHERE storage_type & ?", 472 (StorageType.COLLECTION,)) 473 474 def get_uris(self, uris_concerned=None): 475 """ 476 Get all tracks uri 477 @param uris_concerned as [uri as str] 478 @return [str] 479 """ 480 with SqlCursor(self.__db) as sql: 481 uris = [] 482 if uris_concerned: 483 for uri in uris_concerned: 484 result = sql.execute("SELECT uri\ 485 FROM tracks\ 486 WHERE uri LIKE ? AND\ 487 storage_type & ?", 488 (uri + "%", StorageType.COLLECTION)) 489 uris += list(itertools.chain(*result)) 490 else: 491 result = sql.execute("SELECT uri FROM tracks\ 492 WHERE storage_type & ?", 493 (StorageType.COLLECTION,)) 494 uris = list(itertools.chain(*result)) 495 return uris 496 497 def get_number(self, track_id): 498 """ 499 Get track position in album 500 @param track_id as int 501 @return position as int 502 """ 503 with SqlCursor(self.__db) as sql: 504 result = sql.execute("SELECT tracknumber FROM tracks\ 505 WHERE rowid=?", (track_id,)) 506 v = result.fetchone() 507 if v is not None: 508 return v[0] 509 return 0 510 511 def get_lp_track_id(self, track_id): 512 """ 513 Get Lollypop id 514 @param track_id as int 515 @return str 516 """ 517 with SqlCursor(self.__db) as sql: 518 result = sql.execute("SELECT lp_track_id FROM\ 519 tracks where rowid=?", 520 (track_id,)) 521 v = result.fetchone() 522 if v and v[0]: 523 return v[0] 524 return "" 525 526 def get_discnumber(self, track_id): 527 """ 528 Get disc number for track id 529 @param track_id as int 530 @return discnumber as int 531 """ 532 with SqlCursor(self.__db) as sql: 533 result = sql.execute("SELECT discnumber FROM tracks\ 534 WHERE rowid=?", (track_id,)) 535 v = result.fetchone() 536 if v is not None: 537 return v[0] 538 return 0 539 540 def get_discname(self, track_id): 541 """ 542 Get disc name for track id 543 @param track_id as int 544 @return discname as str 545 """ 546 with SqlCursor(self.__db) as sql: 547 result = sql.execute("SELECT discname FROM tracks\ 548 WHERE rowid=?", (track_id,)) 549 v = result.fetchone() 550 if v is not None: 551 return v[0] 552 return "" 553 554 def get_duration(self, track_id): 555 """ 556 Get track duration for track id 557 @param track_id as int 558 @return duration as int 559 """ 560 with SqlCursor(self.__db) as sql: 561 result = sql.execute("SELECT duration FROM tracks\ 562 WHERE rowid=?", (track_id,)) 563 v = result.fetchone() 564 if v is not None: 565 return v[0] 566 return 0 567 568 def set_duration(self, track_id, duration): 569 """ 570 Get track duration for track id 571 @param track_id as int 572 @param duration as int 573 """ 574 with SqlCursor(self.__db, True) as sql: 575 sql.execute("UPDATE tracks\ 576 SET duration=?\ 577 WHERE rowid=?", (duration, track_id,)) 578 579 def set_mtime(self, track_id, mtime): 580 """ 581 Set track_mtime 582 @param track_id as int 583 @param mtime as int 584 """ 585 with SqlCursor(self.__db, True) as sql: 586 sql.execute("UPDATE tracks SET mtime=? WHERE rowid=?", 587 (mtime, track_id)) 588 589 def is_empty(self): 590 """ 591 Return True if no tracks in db 592 """ 593 with SqlCursor(self.__db) as sql: 594 result = sql.execute("SELECT COUNT(1) FROM tracks LIMIT 1") 595 v = result.fetchone() 596 if v is not None: 597 return v[0] == 0 598 return True 599 600 def get_loved_track_ids(self, artist_ids, storage_type): 601 """ 602 Get loved track ids 603 @param storage_type as StorageType 604 @return [int] 605 """ 606 with SqlCursor(self.__db) as sql: 607 filters = (LovedFlags.LOVED, storage_type) 608 request = "SELECT tracks.rowid\ 609 FROM tracks, album_artists, artists\ 610 WHERE loved=? AND\ 611 artists.rowid=album_artists.artist_id AND\ 612 tracks.album_id=album_artists.album_id AND\ 613 storage_type & ?" 614 if artist_ids: 615 filters += tuple(artist_ids) 616 request += " AND " 617 request += make_subrequest("album_artists.artist_id=?", 618 "OR", 619 len(artist_ids)) 620 request += " ORDER BY artists.name" 621 result = sql.execute(request, filters) 622 return list(itertools.chain(*result)) 623 624 def get_populars(self, artist_ids, storage_type, skipped, limit): 625 """ 626 Return populars tracks 627 @param artist_ids as int 628 @param storage_type as StorageType 629 @param skipped as bool 630 @param limit as int 631 @return track ids as [int] 632 """ 633 with SqlCursor(self.__db) as sql: 634 filters = (storage_type,) 635 request = "SELECT tracks.rowid FROM" 636 if artist_ids: 637 request += " tracks, track_artists " 638 else: 639 request += " tracks " 640 request += "WHERE rate >= 4 AND storage_type & ?" 641 if artist_ids: 642 filters += tuple(artist_ids) 643 request += " AND track_artists.track_id=tracks.rowid AND" 644 request += make_subrequest("track_artists.artist_id=?", 645 "OR", 646 len(artist_ids)) 647 if not skipped: 648 request += " AND not loved &? " 649 filters += (LovedFlags.SKIPPED,) 650 filters += (limit,) 651 request += " ORDER BY popularity DESC LIMIT ?" 652 result = sql.execute(request, filters) 653 track_ids = list(itertools.chain(*result)) 654 if len(track_ids) < limit: 655 filters = (storage_type,) 656 request = "SELECT tracks.rowid FROM" 657 if artist_ids: 658 request += " tracks, track_artists " 659 else: 660 request += " tracks " 661 request += "WHERE popularity!=0 AND\ 662 storage_type & ?" 663 if artist_ids: 664 filters += tuple(artist_ids) 665 request += " AND track_artists.track_id=tracks.rowid AND" 666 request += make_subrequest("track_artists.artist_id=?", 667 "OR", 668 len(artist_ids)) 669 if not skipped: 670 request += " AND not loved &? " 671 filters += (LovedFlags.SKIPPED,) 672 filters += (limit,) 673 request += " ORDER BY popularity DESC LIMIT ?" 674 result = sql.execute(request, filters) 675 track_ids += list(itertools.chain(*result)) 676 return list(set(track_ids)) 677 678 def get_higher_popularity(self): 679 """ 680 Get higher available popularity 681 @return int 682 """ 683 with SqlCursor(self.__db) as sql: 684 result = sql.execute("SELECT popularity\ 685 FROM tracks\ 686 ORDER BY POPULARITY DESC LIMIT 1") 687 v = result.fetchone() 688 if v is not None: 689 return v[0] 690 return 0 691 692 def get_avg_popularity(self): 693 """ 694 Return avarage popularity 695 @return avarage popularity as int 696 """ 697 with SqlCursor(self.__db) as sql: 698 result = sql.execute("SELECT AVG(popularity)\ 699 FROM (SELECT popularity\ 700 FROM tracks\ 701 ORDER BY POPULARITY DESC LIMIT 100)") 702 v = result.fetchone() 703 if v and v[0] is not None and v[0] > 5: 704 return v[0] 705 return 5 706 707 def set_more_popular(self, track_id): 708 """ 709 Increment popularity field 710 @param track_id as int 711 @raise sqlite3.OperationalError on db update 712 """ 713 with SqlCursor(self.__db, True) as sql: 714 result = sql.execute("SELECT popularity from tracks WHERE rowid=?", 715 (track_id,)) 716 pop = result.fetchone() 717 if pop: 718 current = pop[0] 719 else: 720 current = 0 721 current += 1 722 sql.execute("UPDATE tracks set popularity=? WHERE rowid=?", 723 (current, track_id)) 724 725 def set_listened_at(self, track_id, time): 726 """ 727 Set ltime for track 728 @param track_id as int 729 @param time as int 730 """ 731 with SqlCursor(self.__db, True) as sql: 732 sql.execute("UPDATE tracks set ltime=? WHERE rowid=?", 733 (time, track_id)) 734 735 def get_little_played(self, storage_type, skipped, limit): 736 """ 737 Return random tracks little played 738 @param storage_type as StorageType 739 @param skipped as bool 740 @param limit as int 741 @return tracks as [int] 742 """ 743 with SqlCursor(self.__db) as sql: 744 request = "SELECT rowid FROM tracks WHERE storage_type & ?" 745 if not skipped: 746 request += " AND loved !=-1 " 747 request += " ORDER BY ltime, random() LIMIT ?" 748 result = sql.execute(request, (storage_type, limit)) 749 return list(itertools.chain(*result)) 750 751 def get_recently_listened_to(self, storage_type, skipped, limit): 752 """ 753 Return tracks listened recently 754 @param storage_type as StorageType 755 @param skipped as bool 756 @param limit as int 757 @return tracks as [int] 758 """ 759 with SqlCursor(self.__db) as sql: 760 filters = (storage_type,) 761 request = "SELECT tracks.rowid FROM tracks\ 762 WHERE ltime!=0 AND storage_type & ?" 763 if not skipped: 764 request += " AND not loved &? " 765 filters += (LovedFlags.SKIPPED,) 766 request += " ORDER BY ltime DESC LIMIT ?" 767 filters += (limit,) 768 result = sql.execute(request, filters) 769 return list(itertools.chain(*result)) 770 771 def get_skipped(self, storage_type): 772 """ 773 Return skipped tracks 774 @param storage_type as StorageType 775 @return tracks as [int] 776 """ 777 with SqlCursor(self.__db) as sql: 778 request = "SELECT rowid FROM tracks\ 779 WHERE loved & ? AND storage_type & ?" 780 result = sql.execute(request, (LovedFlags.SKIPPED, storage_type)) 781 return list(itertools.chain(*result)) 782 783 def get_randoms(self, genre_ids, storage_type, skipped, limit): 784 """ 785 Return random tracks 786 @param genre_ids as [int] 787 @param storage_type as StorageType 788 @parma skipped as bool 789 @param limit as int 790 @return track ids as [int] 791 """ 792 with SqlCursor(self.__db) as sql: 793 filters = (storage_type,) 794 request = "SELECT tracks.rowid FROM tracks" 795 if genre_ids: 796 request += ",track_genres" 797 request += " WHERE storage_type & ? " 798 if not skipped: 799 request += " AND not loved &? " 800 filters += (LovedFlags.SKIPPED,) 801 if genre_ids: 802 request += "AND tracks.rowid=track_genres.track_id" 803 filters += tuple(genre_ids) 804 request += " AND " 805 request += make_subrequest("track_genres.genre_id=?", 806 "OR", 807 len(genre_ids)) 808 request += " ORDER BY random() LIMIT ?" 809 filters += (limit,) 810 result = sql.execute(request, filters) 811 return list(itertools.chain(*result)) 812 813 def set_popularity(self, track_id, popularity): 814 """ 815 Set popularity 816 @param track_id as int 817 @param popularity as int 818 """ 819 with SqlCursor(self.__db, True) as sql: 820 sql.execute("UPDATE tracks set popularity=? WHERE rowid=?", 821 (popularity, track_id)) 822 823 def get_popularity(self, track_id): 824 """ 825 Get popularity 826 @param track_id as int 827 @return popularity as int 828 """ 829 with SqlCursor(self.__db) as sql: 830 result = sql.execute("SELECT popularity FROM tracks WHERE\ 831 rowid=?", (track_id,)) 832 v = result.fetchone() 833 if v is not None: 834 return v[0] 835 return 0 836 837 def get_loved(self, track_id): 838 """ 839 Get track loved status 840 @param track_id as int 841 @return loved as int 842 """ 843 with SqlCursor(self.__db) as sql: 844 result = sql.execute("SELECT loved FROM tracks WHERE\ 845 rowid=?", (track_id,)) 846 847 v = result.fetchone() 848 if v is not None: 849 return v[0] 850 return 0 851 852 def get_year_for_album(self, album_id, disc_number=None): 853 """ 854 Get album year based on tracks 855 Use most used year by tracks 856 @param album_id as int 857 @param disc_number as int/None 858 @return int 859 """ 860 with SqlCursor(self.__db) as sql: 861 filters = (album_id,) 862 request = "SELECT year, COUNT(year) AS occurrence FROM tracks\ 863 WHERE tracks.album_id=?" 864 if disc_number is not None: 865 filters += (disc_number,) 866 request += " AND tracks.discnumber=?" 867 request += " GROUP BY year\ 868 ORDER BY occurrence DESC" 869 result = sql.execute(request, filters) 870 v = list(result) 871 # Ignore album with multiple original date 872 if len(v) == 1: 873 return v[0][0] 874 return None 875 876 def get_ltime(self, track_id): 877 """ 878 Get listen time 879 @param track_id as int 880 @return listen time as int 881 """ 882 with SqlCursor(self.__db) as sql: 883 result = sql.execute("SELECT ltime FROM tracks WHERE\ 884 rowid=?", (track_id,)) 885 v = result.fetchone() 886 if v is not None: 887 return v[0] 888 return 0 889 890 def get_mtime(self, track_id): 891 """ 892 Get modification time 893 @param track_id as int 894 @return modification time as int 895 """ 896 with SqlCursor(self.__db) as sql: 897 request = "SELECT mtime FROM tracks\ 898 WHERE tracks.rowid=?" 899 result = sql.execute(request, (track_id,)) 900 v = result.fetchone() 901 if v is not None: 902 return v[0] 903 return 0 904 905 def get_years(self, storage_type): 906 """ 907 Return all tracks years and if unknown years exist 908 @param storage_type as StorageType 909 @return ([int], bool) 910 """ 911 with SqlCursor(self.__db) as sql: 912 result = sql.execute("SELECT tracks.year\ 913 FROM tracks\ 914 WHERE storage_type & ?", 915 (storage_type,)) 916 years = [] 917 unknown = False 918 for year in list(itertools.chain(*result)): 919 if year is None: 920 unknown = True 921 elif year not in years: 922 years.append(year) 923 return (years, unknown) 924 925 def get_albums_by_disc_for_year(self, year, storage_type, 926 skipped, limit=-1): 927 """ 928 Return albums for year 929 @param year as int 930 @param storage_type as StorageType 931 @param skipped as bool 932 @param limit as int 933 @return discs [(int, int)] 934 """ 935 with SqlCursor(self.__db) as sql: 936 order = " ORDER BY artists.sortname\ 937 COLLATE NOCASE COLLATE LOCALIZED,\ 938 tracks.timestamp,\ 939 albums.name\ 940 COLLATE NOCASE COLLATE LOCALIZED LIMIT ?" 941 request = "SELECT DISTINCT tracks.album_id,\ 942 discnumber,\ 943 discname,\ 944 albums.year\ 945 FROM albums, tracks, album_artists, artists\ 946 WHERE albums.rowid=album_artists.album_id AND\ 947 artists.rowid=album_artists.artist_id AND\ 948 tracks.album_id=albums.rowid AND\ 949 tracks.year=? AND albums.storage_type & ?" 950 filters = (year, storage_type, limit) 951 if not skipped: 952 request += " AND not albums.loved &? " 953 filters += (LovedFlags.SKIPPED,) 954 request += " GROUP BY tracks.album_id" 955 request += order 956 result = sql.execute(request, filters) 957 return list(result) 958 959 def get_compilations_by_disc_for_year(self, year, storage_type, 960 skipped, limit=-1): 961 """ 962 Return compilations for year 963 @param year as int 964 @param storage_type as StorageType 965 @param skipped as bool 966 @param limit as int 967 @return discs [(int, int)] 968 """ 969 with SqlCursor(self.__db) as sql: 970 order = " ORDER BY albums.timestamp, albums.name\ 971 COLLATE NOCASE COLLATE LOCALIZED LIMIT ?" 972 request = "SELECT DISTINCT tracks.album_id,\ 973 discnumber,\ 974 discname,\ 975 albums.year\ 976 FROM albums, album_artists, tracks\ 977 WHERE album_artists.artist_id=?\ 978 AND album_artists.album_id=albums.rowid\ 979 AND tracks.album_id=albums.rowid\ 980 AND albums.storage_type & ?\ 981 AND tracks.year=?" 982 filters = (Type.COMPILATIONS, storage_type, year, limit) 983 if not skipped: 984 request += " AND not albums.loved &? " 985 filters += (LovedFlags.SKIPPED,) 986 request += " GROUP BY tracks.album_id" 987 request += order 988 result = sql.execute(request, filters) 989 return list(result) 990 991 def set_lp_track_id(self, track_id, lp_track_id): 992 """ 993 Set lp track id 994 @param album_id as int 995 @param lp_album_id as int 996 """ 997 with SqlCursor(self.__db, True) as sql: 998 sql.execute("UPDATE tracks SET lp_track_id=? WHERE rowid=?", 999 (lp_track_id, track_id)) 1000 1001 def set_loved(self, track_id, loved): 1002 """ 1003 Set track loved 1004 @param track_id as int 1005 @param loved as int 1006 @warning: commit needed 1007 """ 1008 with SqlCursor(self.__db, True) as sql: 1009 sql.execute("UPDATE tracks SET loved=? WHERE rowid=?", 1010 (loved, track_id)) 1011 1012 def count(self): 1013 """ 1014 Count tracks 1015 @return int 1016 """ 1017 with SqlCursor(self.__db) as sql: 1018 result = sql.execute("SELECT COUNT(1) FROM tracks\ 1019 WHERE storage_type & ?", 1020 (StorageType.COLLECTION | StorageType.SAVED,)) 1021 v = result.fetchone() 1022 if v is not None: 1023 return v[0] 1024 return 0 1025 1026 def clean(self, commit=True): 1027 """ 1028 Clean database for track id 1029 @param commit as bool 1030 """ 1031 with SqlCursor(self.__db, commit) as sql: 1032 sql.execute("DELETE FROM track_artists\ 1033 WHERE track_artists.track_id NOT IN (\ 1034 SELECT tracks.rowid FROM tracks)") 1035 sql.execute("DELETE FROM track_genres\ 1036 WHERE track_genres.track_id NOT IN (\ 1037 SELECT tracks.rowid FROM tracks)") 1038 1039 def search(self, searched, storage_type): 1040 """ 1041 Search for tracks looking like searched 1042 @param searched as str without accents 1043 @param storage_type as StorageType 1044 @return [(int, name)] 1045 """ 1046 with SqlCursor(self.__db) as sql: 1047 filters = ("%" + searched + "%", storage_type) 1048 request = "SELECT rowid, name FROM tracks\ 1049 WHERE noaccents(name) LIKE ?\ 1050 AND tracks.storage_type & ? LIMIT 25" 1051 result = sql.execute(request, filters) 1052 return list(result) 1053 1054 def search_performed(self, searched, storage_type): 1055 """ 1056 Search tracks looking like searched with performers 1057 @param searched as str without accents 1058 @param storage_type as StorageType 1059 @return [(int, name)] 1060 """ 1061 with SqlCursor(self.__db) as sql: 1062 filters = ("%" + searched + "%", storage_type) 1063 request = "SELECT DISTINCT tracks.rowid, artists.name\ 1064 FROM track_artists, tracks, artists\ 1065 WHERE track_artists.artist_id=artists.rowid AND\ 1066 track_artists.track_id=tracks.rowid AND\ 1067 noaccents(artists.name) LIKE ? AND\ 1068 tracks.storage_type & ? AND NOT EXISTS (\ 1069 SELECT album_artists.artist_id\ 1070 FROM album_artists\ 1071 WHERE album_artists.artist_id=artists.rowid)\ 1072 LIMIT 25" 1073 result = sql.execute(request, filters) 1074 return list(result) 1075 1076 def search_track(self, artist, title): 1077 """ 1078 Get track id for artist and title 1079 @param artist as string 1080 @param title as string 1081 @return track id as int 1082 """ 1083 artist = noaccents(artist.lower()) 1084 track_ids = self.get_ids_for_name(title) 1085 for track_id in track_ids: 1086 album_id = App().tracks.get_album_id(track_id) 1087 artist_ids = set(App().albums.get_artist_ids(album_id)) &\ 1088 set(App().tracks.get_artist_ids(track_id)) 1089 for artist_id in artist_ids: 1090 db_artist = noaccents( 1091 App().artists.get_name(artist_id).lower()) 1092 if artist.find(db_artist) != -1 or\ 1093 db_artist.find(artist) != -1: 1094 return track_id 1095 artists = ", ".join(App().tracks.get_artists(track_id)).lower() 1096 if noaccents(artists) == artist: 1097 return track_id 1098 return None 1099 1100 def remove(self, track_id): 1101 """ 1102 Remove track 1103 @param track_id as int 1104 """ 1105 with SqlCursor(self.__db, True) as sql: 1106 sql.execute("DELETE FROM track_genres\ 1107 WHERE track_id=?", (track_id,)) 1108 sql.execute("DELETE FROM track_artists\ 1109 WHERE track_id=?", (track_id,)) 1110 sql.execute("DELETE FROM tracks\ 1111 WHERE rowid=?", (track_id,)) 1112