def create_in_db(self): if self.id: return c = MusicDatabase.getCursor() sql = text('INSERT INTO playlists (name, owner_id, playlist_type)' 'VALUES (:name, :owner_id, :playlist_type)') c.execute(sql.bindparams(name=self.name, owner_id=self.owner_id, playlist_type=self.playlist_type)) try: sql = "SELECT currval(pg_get_serial_sequence('playlists','id'))" result = c.execute(sql) except exc.OperationalError: # Try the sqlite way sql = 'SELECT last_insert_rowid()' result = c.execute(sql) self.id = result.fetchone()[0] pls = table('playlist_songs') for idx, item in enumerate(self.songs): entry = { 'playlist_id': self.id, 'song_id': item[0], 'recording_mbid': item[1], 'pos': idx, } MusicDatabase.insert_or_update(pls, entry, and_(pls.c.playlist_id == self.id, pls.c.pos == idx), connection=c) c.commit()
def songTags(songIDs=None): c = MusicDatabase.getCursor() if songIDs: sql = text('SELECT song_id, name, value FROM tags ' 'WHERE song_id IN :id_list ' 'ORDER BY song_id, pos') result = c.execute(sql, {'id_list': tuple(songIDs)}) else: sql = text('SELECT song_id, name, value FROM tags ' 'ORDER BY song_id, pos') result = c.execute(sql) tags = {} row = result.fetchone() current_song_id = None while row: if row.song_id != current_song_id: if current_song_id: yield current_song_id, tags tags = {} current_song_id = row.song_id if row.name not in tags: tags[row.name] = [row.value] else: tags[row.name] += [row.value] row = result.fetchone() if current_song_id: yield current_song_id, tags
def lastSongIDWithAnalysis(): c = MusicDatabase.getCursor() sel = select([func.max(Highlevel.c.song_id)]) result = c.execute(sel) x = result.fetchone() if x: return x[0] return 0
def move_song(self, from_position, to_position, *, connection=None): c = connection or MusicDatabase.getCursor() item = self.remove_song(from_position, connection=c) if to_position > from_position: to_position -= 1 self.insert_song(to_position, item=item, connection=c) if not connection: c.commit()
def get_artist_credit_info(artistCreditID): c = MusicDatabase.getCursor() sql = ('select artist_id, position, name, join_phrase' ' from musicbrainz.artist_credit_name ' ' where artist_credit_id = :artistCreditID ' ' order by position') result = c.execute(text(sql), {'artistCreditID': artistCreditID}) return result.fetchall()
def get_album_songs(albumID): c = MusicDatabase.getCursor() sql = ('select songs_mb.song_id, ' ' songs_mb.releasetrackid ' ' from album_songs, songs_mb ' ' where album_songs.song_id = songs_mb.song_id ' ' and album_songs.album_id = :albumID ') result = c.execute(text(sql), {'albumID': albumID}) return result.fetchall()
def get_release_group_secondary_types(rgID): c = MusicDatabase.getCursor() sql = ('select name ' ' from musicbrainz.release_group_secondary_type_join, ' ' musicbrainz.enum_release_group_secondary_type_values ' ' where release_group_id = :rgID ' ' and secondary_type = id_value') r = c.execute(text(sql), {'rgID': rgID}) return [x[0] for x in r.fetchall()]
def get_link_attributes(link_id): c = MusicDatabase.getCursor() sql = ('select id, name ' ' from musicbrainz.link_attribute la, ' ' musicbrainz.link_attribute_type lat ' ' where la.link_id = :link_id ' ' and la.link_attribute_type_id = lat.id ') r = c.execute(sql, {'link_id': link_id}) return r.fetchall()
def songsWithoutMBData(): c = MusicDatabase.getCursor() sql = text('SELECT id FROM songs ' 'WHERE NOT EXISTS (SELECT song_id ' ' FROM songs_mb ' ' WHERE song_id = id) ' 'ORDER BY id') result = c.execute(sql) return [x[0] for x in result.fetchall()]
def get_release_directories(releaseMBID): c = MusicDatabase.getCursor() sql = ('select path ' ' from songs ' ' where id in (select song_id ' ' from songs_mb ' ' where releaseid=:releaseMBID)') result = c.execute(text(sql), {'releaseMBID': releaseMBID}) return set(os.path.dirname(path) for (path,) in result.fetchall())
def get_song_paths_from_songs_mb(column, uuids): c = MusicDatabase.getCursor() sql = f'''select path from songs where id in (select song_id from songs_mb where {column} in :uuid_list) order by path''' result = c.execute(sql, {'uuid_list': tuple(uuids)}) return [x for (x,) in result.fetchall()]
def songsWithoutAnalysis(from_song_id=0): c = MusicDatabase.getCursor() sql = text('SELECT id, path, duration FROM songs, properties ' 'WHERE NOT EXISTS (SELECT song_id ' ' FROM analysis.highlevel ' ' WHERE song_id = id) ' ' AND songs.id = properties.song_id ' f' AND songs.id >= {from_song_id} ' 'ORDER BY id') result = c.execute(sql) return [(x[0], x[1], x[2]) for x in result.fetchall()]
def get_country_names(): _ = MusicDatabase() c = MusicDatabase.getCursor() sql = text('SELECT name ' ' FROM musicbrainz.area ' ' WHERE area_type = 1 ' ' ORDER BY name') r = c.execute(sql) return [x[0] for x in r.fetchall()]
def create_in_db(self): super(GeneratedPlaylist, self).create_in_db() c = MusicDatabase.getCursor() t = table('playlist_generators') data = {'playlist_id': self.id, 'generator': self.generator} MusicDatabase.insert_or_update(t, data, t.c.playlist_id == self.id, connection=c) c.commit()
def get_letter_offset_for_artist(letter): if letter == '0': return 0 c = MusicDatabase.getCursor() sql = ('select min(subq.offset) from (' ' select row_number() over(order by locale_name)' ' as offset,' ' locale_name' ' from artists_mb) as subq' ' where subq.locale_name ilike :search') result = c.execute(sql, {'search': letter + '%'}) return result.fetchone()[0] - 1
def fileSha256sum(self): try: return self._fileSha256sum except AttributeError: c = MusicDatabase.getCursor() sql = 'SELECT sha256sum FROM checksums where song_id = :id' result = c.execute(text(sql).bindparams(id=self.id)) sha = result.fetchone() if sha: self._fileSha256sum = sha[0] return self._fileSha256sum return ''
def get_link_type_id(name, entity_type0, entity_type1): if not getattr(MusicBrainzDatabase, 'link_types', None): c = MusicDatabase.getCursor() sql = ('select id, name, entity_type0, entity_type1 ' 'from musicbrainz.link_type') r = c.execute(sql) MusicBrainzDatabase.link_types = \ {(lt_name, lt_entity_t0, lt_entity_t1): lt_id for lt_id, lt_name, lt_entity_t0, lt_entity_t1 in r.fetchall()} return MusicBrainzDatabase.link_types[(name, entity_type0, entity_type1)]
def get_release_group_releases(rgID): c = MusicDatabase.getCursor() sql = text('select album_id, r.id, mbid, r.name, disambiguation, ' ' release_status, language, barcode, ' ' artist_credit_id, ac.name, ' ' r.release_group_id ' ' from musicbrainz.release r, ' ' musicbrainz.artist_credit ac, ' ' album_release ar' ' where ar.release_id = r.id ' ' and r.artist_credit_id = ac.id ' ' and r.release_group_id = :rgID ') r = c.execute(sql, {'rgID': rgID}) return r.fetchall()
def get_artist_release_groups(artistID): c = MusicDatabase.getCursor() sql = ('select rg.id, mbid, rg.name, disambiguation, ' 'rgt.name as release_group_type, rg.artist_credit_id, ' 'ac.name as artist_credit_name' ' from musicbrainz.release_group as rg, ' ' musicbrainz.artist_credit as ac, ' ' musicbrainz.artist_credit_name as acn, ' ' musicbrainz.enum_release_group_type_values as rgt ' 'where rg.artist_credit_id = ac.id ' ' and rg.artist_credit_id = acn.artist_credit_id ' ' and rg.release_group_type = rgt.id_value ' ' and acn.artist_id = :artistID') result = c.execute(sql, {'artistID': artistID}) return result.fetchall()
def reenumerate_songs_in_db(self): c = MusicDatabase.getCursor() sql = ('SELECT pos ' 'FROM playlist_songs ' 'WHERE playlist_id = :id ' 'ORDER BY pos') result = c.execute(text(sql).bindparams(id=self.id)) for idx, pos in enumerate(result.fetchall()): pos = pos[0] if pos != idx: sql = text('UPDATE playlist_songs SET pos = :idx ' 'WHERE playlist_id = :playlist_id ' ' AND pos = :pos') c.execute(sql.bindparams(playlist_id=self.id, pos=pos, idx=idx)) c.commit()
def load_playlist_songs(self): c = MusicDatabase.getCursor() sql = ('SELECT song_id, recording_mbid, pos ' 'FROM playlist_songs ' 'WHERE playlist_id = :id ' 'ORDER BY pos') result = c.execute(text(sql).bindparams(id=self.id)) self.songs = [] for idx, x in enumerate(result.fetchall()): song_id, recording_mbid, pos = x # song = getSongs(songID=song_id)[0] if idx != pos: print('ERROR: playlist items in the database are ' 'not correctly positioned') item = (song_id, recording_mbid) self.songs.append(item)
def getAlbumDisambiguation(release): c = MusicDatabase.getCursor() sql = text('select name, value ' ' from tags, album_songs ' ' where tags.song_id = album_songs.song_id ' ' and album_songs.album_id = :albumID ' ' and name IN (\'comment\',\'usereleasecomment\',' ' \'uselabel\') ' ' group by name, value') r = c.execute(sql, {'albumID': release['album_id']}) album = {x['name']: x['value'] for x in r.fetchall()} print(release, album) try: usereleasecomment = int(album['usereleasecomment']) except KeyError: usereleasecomment = 1 try: uselabel = int(album['uselabel']) except KeyError: uselabel = 0 result = [] if usereleasecomment == 2: result.append(album['comment']) elif usereleasecomment == 1: if release['disambiguation']: result.append(release['disambiguation']) elif usereleasecomment == 3: rg = MusicBrainzDatabase.get_release_group_info( release['release_group_id']) if rg['disambiguation']: result.append(rg['disambiguation']) if uselabel > 0: release_label = \ MusicBrainzDatabase.get_release_label(release['id'])[0] if uselabel == 1: result.append(release_label['label_name']) else: print(release_label) print(release_label['label_name']) print(release_label['catalog_number']) result.append(release_label['label_name'] + ':' + release_label['catalog_number']) return ','.join(result)
def remove_song(self, position, *, connection=None): if position < 0 or position >= len(self.songs): raise ValueError('Trying to remove an out-of-range element ' 'from the playlist') r = self.songs.pop(position) if self.id and self.store_songs_in_db: c = connection or MusicDatabase.getCursor() sql = text('DELETE FROM playlist_songs ' 'WHERE playlist_id = :playlist_id ' ' AND pos = :position') c.execute(sql.bindparams(playlist_id=self.id, position=position)) self._update_positions_in_db(position + 1, len(self.songs) + 1, -1, c) if not connection: c.commit() return r
def get_songs_information_for_webui(*, songIDs=None, query=FullSongsWebQuery()): if songIDs: query = FullSongsWebQuery(where=['als.song_id in :songIDs'], order_by=['als.song_id'], values={'songIDs': tuple(songIDs)}) c = MusicDatabase.getCursor() cq = (',' + ','.join(query.columns)) if query.columns else '' ct = (',' + ','.join(query.tables)) if query.tables else '' cw = (' AND ' + ' AND '.join(query.where)) if query.where else '' co = ('ORDER BY ' + ','.join(query.order_by)) if query.order_by else '' climit = f'LIMIT {query.limit}' if query.limit else '' coffset = f'OFFSET {query.offset}' if query.offset else '' sql = ('select als.album_id, als.song_id, ' ' m.position as medium_number, ' ' m.format as medium_format_id, ' # ' emfv.name as medium_format, ' ' r.name as release_name, ' ' m.name as medium_name, ' ' t.position as track_position, t.mbid as track_mbid, ' ' t.recording_id, t.number_text , t.name, ' ' ac.name as artist_name, t.artist_credit_id, ' ' t.is_data_track, p.duration, p.format, p.bitrate, ' f' p.bits_per_sample, p.sample_rate, p.channels {cq}' ' from album_songs als, album_release ar, ' ' musicbrainz.medium m, ' ' musicbrainz.release r, ' # ' musicbrainz.enum_medium_format_values emfv, ' ' musicbrainz.track t, ' ' musicbrainz.artist_credit ac, ' f' songs_mb smb, properties p {ct}' ' where ar.release_id = m.release_id ' ' and ar.release_id = r.id' ' and m.id = t.medium_id ' # ' and m.format = emfv.id_value ' ' and ar.album_id = als.album_id ' ' and t.artist_credit_id = ac.id ' ' and smb.song_id = als.song_id ' ' and p.song_id = als.song_id ' ' and smb.releasetrackid = t.mbid ' f' {cw} {co} {climit} {coffset}') result = c.execute(text(sql), query.values) return result.fetchall()
def __init__(self): """Create a Ratings object with ALL ratings from all users/songs.""" c = MusicDatabase.getCursor() sql = 'SELECT user_id, song_id, autorating, userrating FROM ratings' result = c.execute(sql) self.user_ratings = {} self.auto_ratings = {} for user_id, song_id, autorating, userrating in result.fetchall(): if userrating: try: self.user_ratings[user_id][song_id] = userrating except KeyError: self.user_ratings[user_id] = {} self.user_ratings[user_id][song_id] = userrating if autorating: try: self.auto_ratings[user_id][song_id] = autorating except KeyError: self.auto_ratings[user_id] = {} self.auto_ratings[user_id][song_id] = autorating
def checkAlbumsWithDifferentReleases(): c = MusicDatabase.getCursor() sql = text('SELECT album_id, path, ' ' COUNT(DISTINCT musicbrainz.release.id) ' ' FROM songs_mb, albums, album_songs, musicbrainz.release ' ' WHERE albums.id = album_songs.album_id ' ' AND releaseid = mbid ' ' AND songs_mb.song_id = album_songs.song_id ' ' GROUP BY album_songs.album_id, albums.path ' ' HAVING COUNT(DISTINCT musicbrainz.release.id) > 1') result = c.execute(sql) table = [(str(album_id), path, str(count)) for album_id, path, count in result.fetchall()] if table: table.insert(0, ('ALBUMID', 'PATH', 'NUMBER OF RELEASES')) aligned = alignColumns(table, (False, True, False)) print('Albums that contain songs from different releases:') for line in aligned: print(line) return bool(table)
def checkMusicBrainzTags(): c = MusicDatabase.getCursor() sql = text('SELECT id, path FROM songs ' 'WHERE root = :root ' ' AND NOT EXISTS (SELECT song_id ' ' FROM songs_mb ' ' WHERE recordingid is not NULL ' ' AND song_id = id)' ' ORDER BY id') table = [] for root in config['musicbrainzTaggedMusicPaths']: result = c.execute(sql, {'root': root}) table.extend((str(song_id), path) for song_id, path in result.fetchall()) if table: table.insert(0, ('SONGID', 'PATH')) aligned = alignColumns(table, (False, True)) print('Songs which should have musicbrainz tags but don\'t:') for line in aligned: print(line) return bool(table)
def get_album_tracks(albumID): c = MusicDatabase.getCursor() sql = ('select ar.album_id, ' ' m.position as medium_number,' ' m.format as medium_format_id,' # ' emfv.name as medium_format, ' ' m.name as medium_name, ' ' t.position as track_position, t.mbid as track_mbid, ' ' t.recording_id, t.number_text , t.name, ' ' ac.name as artist_name, t.artist_credit_id, ' ' t.is_data_track, t.length/1000 as duration' ' from album_release ar, musicbrainz.medium m, ' # ' musicbrainz.enum_medium_format_values emfv, ' ' musicbrainz.track t, musicbrainz.artist_credit ac ' ' where ar.release_id = m.release_id ' ' and m.id = t.medium_id ' # ' and m.format = emfv.id_value ' ' and ar.album_id = :albumID ' ' and t.artist_credit_id = ac.id ' ' order by m.position, t.position') result = c.execute(text(sql), {'albumID': albumID}) return result.fetchall()
def import_analysis(self, *, connection=None): if not self.stats or not self.frames: raise ValueError("There's no analysis to import") self.connection = connection or MusicDatabase.getCursor() if self.has_analysis_for_song_id(self.song_id, connection=self.connection): print(f'Removing existing data for song {self.song_id}...') self.remove_analysis_for_song_id(self.song_id, connection=self.connection) self.import_keys_with_stats() self.import_keys_with_lists_stats() self.import_keys_with_lists() self.import_fkeys_with_lists() self.import_fkeys_with_lists_of_lists() self.import_conversion_dict() if not connection: self.connection.commit() self.connection = None
def checkAlbumsWithDifferentFormats(): c = MusicDatabase.getCursor() sql = text('select id, path, format ' ' from albums, album_properties ' ' where id in (select album_id ' ' from (select album_id, count(*) ' ' from album_properties ' ' group by album_id ' ' having count(*)>1) ' ' as foo) ' ' and id = album_id') result = c.execute(sql) table = [(str(album_id), path, audioFormat) for album_id, path, audioFormat in result.fetchall()] if table: table.insert(0, ('ALBUMID', 'PATH', 'FORMAT')) aligned = alignColumns(table, (False, True, True)) print('Albums that contain songs with different formats:') for line in aligned: print(line) return bool(table)