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)