def cacheMusicBrainzDB():
artist = table('musicbrainz.artist')
aa = table('musicbrainz.artist_alias')
s = select([artist.c.id, artist.c.mbid, artist.c.name,
artist.c.sort_name, artist.c.artist_type, artist.c.area_id,
artist.c.gender, artist.c.disambiguation])
locales = ['es', 'en']
for a in MusicDatabase.execute(s).fetchall():
s2 = (select([aa.c.name, aa.c.sort_name, aa.c.locale,
aa.c.artist_alias_type, aa.c.primary_for_locale])
.where(and_(aa.c.artist_id == a['id'],
aa.c.locale.in_(locales))))
current = {}
for x in MusicDatabase.execute(s2).fetchall():
if MusicBrainzDatabase.is_better_alias(x, current):
current = {'locale_name': x['name'],
'locale_sort_name': x['sort_name'],
'locale': x['locale'],
'artist_alias_type': x['artist_alias_type']}
if not current:
current = {'locale_name': a['name'],
'locale_sort_name': a['sort_name'],
'locale': None,
'artist_alias_type': None}
current['id'] = a['id']
MusicDatabase.insert_or_update('artists_mb', current)
def get_release_label(releaseID):
rl = table('musicbrainz.release_label')
label = table('musicbrainz.label')
s = (select([label.c.name.label('label_name'), rl.c.catalog_number])
.where(and_(rl.c.label_id == label.c.id,
rl.c.release_id == releaseID)))
return MusicDatabase.execute(s).fetchall()
def get_release_mediums(releaseID):
m = table('musicbrainz.medium')
emfv = table('musicbrainz.enum_medium_format_values')
s = (select([m.c.id, m.c.release_id, m.c.position,
emfv.c.name.label('format_name'),
m.c.name])
.where(and_(m.c.format == emfv.c.id_value,
m.c.release_id == releaseID))
.order_by(m.c.position))
return MusicDatabase.execute(s).fetchall()
def get_release_group_info(rgID):
rg = table('musicbrainz.release_group')
ac = table('musicbrainz.artist_credit')
s = (select([rg.c.id, rg.c.mbid, rg.c.name,
rg.c.disambiguation,
rg.c.release_group_type, rg.c.artist_credit_id,
ac.c.name.label('artist_name')])
.where(and_(rg.c.artist_credit_id == ac.c.id,
rg.c.id == rgID)))
return MusicDatabase.execute(s).fetchone()
def get_artists_info(artistIDs):
artist = table('musicbrainz.artist')
artists_mb = table('artists_mb')
s = (select([artist.c.id, artist.c.mbid, artist.c.name,
artist.c.artist_type, artist.c.area_id, artist.c.gender,
artist.c.disambiguation,
artists_mb.c.locale_name, artists_mb.c.locale_sort_name,
artists_mb.c.image_path])
.where(and_(artist.c.id == artists_mb.c.id,
artist.c.id.in_(artistIDs))))
return MusicDatabase.execute(s).fetchall()
def get_range_artists(offset=0, page_size=500, metadata=False):
artist = table('musicbrainz.artist')
artists_mb = table('artists_mb')
s = (select([artist.c.id, artist.c.mbid, artist.c.name,
artist.c.artist_type, artist.c.area_id, artist.c.gender,
artist.c.disambiguation,
artists_mb.c.locale_name, artists_mb.c.locale_sort_name,
artists_mb.c.image_path])
.where(artist.c.id == artists_mb.c.id)
.order_by(artists_mb.c.locale_name)
.limit(page_size)
.offset(offset))
return MusicDatabase.execute(s).fetchall()
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 insert_song(self, position, song_id=None, mbid=None, *,
connection=None):
assert song_id, "song_id must be used"
if position > len(self.songs):
position = len(self.songs)
if not mbid:
mbid = MusicDatabase.getRecordingMBID(song_id)
item = (song_id, mbid)
self.songs.insert(position, item)
if self.id:
c = connection or MusicDatabase.getCursor()
self._update_positions_in_db(len(self.songs) - 1, position, 1, c)
entry = {
'playlist_id': self.id,
'pos': position,
'song_id': song_id,
'recording_mbid': mbid,
}
pls = table('playlist_songs')
MusicDatabase.insert_or_update(pls, entry,
and_(pls.c.playlist_id == self.id,
pls.c.pos == position),
connection=c)
if not connection:
c.commit()
def append_song(self, song_id, mbid=None, *, connection=None):
assert song_id, "song_id must be used"
if not mbid:
print(f'Obtaining MBID for song id {song_id}')
mbid = MusicDatabase.getRecordingMBID(song_id)
item = (song_id, mbid)
self.songs.append(item)
if self.id and self.store_songs_in_db:
pos = len(self.songs) - 1
c = connection or MusicDatabase.getCursor()
entry = {
'playlist_id': self.id,
'song_id': song_id,
'recording_mbid': mbid,
'pos': pos,
}
pls = table('playlist_songs')
MusicDatabase.insert_or_update(pls,
entry,
and_(pls.c.playlist_id == self.id,
pls.c.pos == pos),
connection=c)
if not connection:
c.commit()
def insertMusicBrainzTags(song_id, mbIDs):
MusicBrainzDatabase.insertMBArtistIDs(song_id, mbIDs.artistids)
MusicBrainzDatabase.insertMBAlbumArtistIDs(song_id,
mbIDs.albumartistids)
MusicBrainzDatabase.insertMBWorkIDs(song_id, mbIDs.workids)
songs_mb = table('songs_mb')
mbTagRecord = songs_mb.select(songs_mb.c.song_id == song_id)
mbTagRecord = MusicDatabase.execute(mbTagRecord).fetchone()
if mbTagRecord:
if (mbTagRecord['releasegroupid'] != mbIDs.releasegroupid or
mbTagRecord['releaseid'] != mbIDs.releaseid or
mbTagRecord['releasetrackid'] != mbIDs.releasetrackid or
mbTagRecord['recordingid'] != mbIDs.recordingid or
mbTagRecord['confirmed'] != mbIDs.confirmed):
print(f'update mb data for {song_id}')
u = songs_mb.update() \
.where(songs_mb.c.song_id == song_id) \
.values(song_id=song_id,
releasegroupid=mbIDs.releasegroupid,
releaseid=mbIDs.releaseid,
releasetrackid=mbIDs.releasetrackid,
recordingid=mbIDs.recordingid,
confirmed=mbIDs.confirmed)
MusicDatabase.execute(u)
else:
print(f'insert mb data for {song_id}')
i = songs_mb.insert().values(song_id=song_id,
releasegroupid=mbIDs.releasegroupid,
releaseid=mbIDs.releaseid,
releasetrackid=mbIDs.releasetrackid,
recordingid=mbIDs.recordingid,
confirmed=mbIDs.confirmed)
MusicDatabase.execute(i)
def get_all_works():
songs_mb_workids = table('songs_mb_workids')
s = select([songs_mb_workids.c.workid]).distinct()
result = MusicDatabase.execute(s).fetchall()
print('works', len(result))
r = set(x['workid'] for x in result)
return r
def get_all_artists():
"""Return all artists (used by the mb importer)."""
songs_mb_artistids = table('songs_mb_artistids')
s = select([songs_mb_artistids.c.artistid]).distinct()
result_artists = MusicDatabase.execute(s).fetchall()
print(len(result_artists))
s1 = set(x['artistid'] for x in result_artists)
songs_mb_albumartistids = table('songs_mb_albumartistids')
s = select([songs_mb_albumartistids.c.albumartistid]).distinct()
result_albumartists = MusicDatabase.execute(s).fetchall()
print(len(result_albumartists))
r = s1.union(x['albumartistid'] for x in result_albumartists)
print('artists', len(r))
return r
def import_conversion_dict(self):
tables = set(x[0] for x in conversion_dict.values())
for tablename in tables:
t = table('analysis.' + tablename.lower())
vars = {
v[1]: self.stats[k]
for k, v in conversion_dict.items() if v[0] == tablename
}
vars['song_id'] = self.song_id
self.connection.execute(t.insert().values(**vars))
def import_keys_with_lists(self):
for key in keys_with_lists:
if key in keys_to_ignore:
continue
t = table('analysis.' + key.replace('.', '__').lower())
vars = {
'values': list(x.item() for x in self.stats[key]),
'song_id': self.song_id
}
self.connection.execute(t.insert().values(**vars))
def import_keys_with_stats(self):
for key in keys_with_stats:
if key in keys_to_ignore:
continue
t = table('analysis.' + key.replace('.', '__').lower() + '_stats')
vars = {
db_var: self.stats[key + '.' + st_var]
for st_var, db_var in stat_variables.items()
}
vars['song_id'] = self.song_id
self.connection.execute(t.insert().values(**vars))
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_all_elements_from_songs_mb(column=None):
if not column:
return []
songs_mb = table('songs_mb')
s = select([getattr(songs_mb.c, column)]).distinct()
result = MusicDatabase.execute(s).fetchall()
r = set(x[column] for x in result)
r.difference_update({None})
return r
def get_artist_aliases(artistID, locales=None, only_primary=False):
alias = table('musicbrainz.artist_alias')
s = (select([alias.c.name, alias.c.sort_name, alias.c.locale,
alias.c.artist_alias_type, alias.c.primary_for_locale])
.where(alias.c.artist_id == artistID)
.order_by(alias.c.locale)
.order_by(desc(alias.c.primary_for_locale)))
# query is ordered by locale and inside each locale, the primary
# is returned first
if locales:
s = s.where(alias.c.locale.in_(locales))
if only_primary:
s = s.where(alias.c.primary_for_locale.is_(True))
return MusicDatabase.execute(s).fetchall()
def import_keys_with_lists_stats(self):
for key in keys_with_lists_stats:
if key in keys_to_ignore:
continue
for pos in range(len(self.stats[key + '.var'])):
t = table('analysis.' + key.replace('.', '__').lower() +
'_stats')
vars = {
db_var: self.stats[key + '.' + st_var][pos].item()
for st_var, db_var in stat_variables.items()
}
vars['song_id'] = self.song_id
vars['pos'] = pos
self.connection.execute(t.insert().values(**vars))
def insertMBAlbumArtistIDs(song_id, albumArtistIDs):
if not albumArtistIDs:
return
songs_mb_albumartistids = table('songs_mb_albumartistids')
s = select([songs_mb_albumartistids.c.albumartistid]) \
.where(songs_mb_albumartistids.c.song_id == song_id)
result = MusicDatabase.execute(s).fetchall()
if set(albumArtistIDs) == set(x['albumartistid'] for x in result):
return
d = songs_mb_albumartistids.delete() \
.where(songs_mb_albumartistids.c.song_id == song_id)
MusicDatabase.execute(d)
for artistID in albumArtistIDs:
i = insert(songs_mb_albumartistids).values(song_id=song_id,
albumartistid=artistID)
MusicDatabase.execute(i)
def insertMBWorkIDs(song_id, workIDs):
if not workIDs:
return
songs_mb_workids = table('songs_mb_workids')
s = select([songs_mb_workids.c.workid]) \
.where(songs_mb_workids.c.song_id == song_id)
result = MusicDatabase.execute(s).fetchall()
if set(workIDs) == set(x['workid'] for x in result):
return
d = songs_mb_workids.delete() \
.where(songs_mb_workids.c.song_id == song_id)
MusicDatabase.execute(d)
for workID in workIDs:
i = insert(songs_mb_workids).values(song_id=song_id,
workid=workID)
MusicDatabase.execute(i)
