def getMusic(where_clause='', where_values=None, tables=[],
order_by=None, limit=None, metadata=False):
# print(where_clause)
c = MusicDatabase.getCursor()
if 'songs' not in tables:
tables.insert(0, 'songs')
statement = ('SELECT id, root, path, mtime, title, artist, album, '
'albumArtist, track, date, genre, discNumber, '
'coverWidth, coverHeight, coverMD5 FROM %s %s' %
(','.join(tables), where_clause))
if order_by:
statement += ' ORDER BY %s' % order_by
if limit:
statement += ' LIMIT %d' % limit
# print(statement, where_values)
if where_values:
result = c.execute(text(statement).bindparams(**where_values))
else:
result = c.execute(text(statement))
r = [Song(x) for x in result.fetchall()]
if metadata:
MusicDatabase.updateSongsTags(r)
return r
def album_info():
if request.method != 'GET':
return None
MBD = MusicBrainzDatabase
albumID = request.args.get('id', type=int)
album_info = MBD.get_album_info(albumID)
releaseID = album_info['release_id']
rgID = album_info['release_group_id']
release_group_info = MBD.get_release_group_info(rgID)
secondary_types = MBD.get_release_group_secondary_types(rgID)
release_events = MBD.get_release_events(releaseID)
result = dict(album_info)
result['covers_count'] = MusicDatabase.getAlbumCoversCount(albumID)
if result['language']:
result['language'] = LanguageEnum.name(result['language'])
if result['release_status']:
result['release_status'] = ReleaseStatusEnum.name(
result['release_status'])
result['release_group'] = dict(release_group_info)
rg_type = result['release_group']['release_group_type']
if rg_type:
rg_type = ReleaseGroupTypeEnum.name(rg_type)
result['release_group']['release_group_type'] = rg_type
result['release_group_secondary_types'] = secondary_types
result['release_events'] = [dict(x) for x in release_events]
ratings = MusicDatabase.get_albums_ratings([albumID], current_user.userID)
result['rating'] = ratings[albumID]
return jsonify(result)
def album_cover():
if request.method != 'GET':
return None
album_id = request.args.get('id', type=int)
medium_number = request.args.get('medium_number', type=int, default=None)
print(f'Delivering coverart of album {album_id} medium {medium_number}')
path = MusicDatabase.getAlbumPath(album_id, medium_number)
if not path:
print('ERROR getting album image for album'
f'{album_id}/{medium_number}')
return ''
coverfilename = coverAtPath(path)
if not coverfilename and not medium_number:
path = MusicDatabase.getAlbumPath(album_id, any_medium=True)
if not path:
print('ERROR getting album image for album'
f'{album_id}/{medium_number}')
return ''
coverfilename = coverAtPath(path)
if coverfilename:
return send_file(coverfilename)
else:
print(f'Error cover not found at {path}')
return ''
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 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 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 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 album_set_ratings():
if request.method != 'GET':
return None
album_id = request.args.get('id', type=int)
rating = request.args.get('rating', type=int)
MusicDatabase.set_album_rating(album_id, rating, current_user.userID)
return ''
def updateMusicBrainzIDs(songIDs=None):
if not songIDs:
return
for song_id, tags in MusicBrainzDatabase.songTags(songIDs):
mbIDs = getSongMusicBrainzIDs(song_id, tags)
if any(mbIDs):
MusicBrainzDatabase.insertMusicBrainzTags(song_id, mbIDs)
MusicDatabase.commit()
def release_group_set_ratings():
if request.method != 'GET':
return None
rgID = request.args.get('id', type=int)
rating = request.args.get('rating', type=int)
album_ids = MusicBrainzDatabase.get_release_group_albums(rgID)
for album_id in album_ids:
MusicDatabase.set_album_rating(album_id, rating, current_user.userID)
return ''
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 requestNewPassword(username):
if not username:
username = config['username']
userID = MusicDatabase.getUserID(username)
prompt = f'Enter the new password for user \'{username}\': '
password = getpass.getpass(prompt)
hashed = hashpw(password.encode('utf-8'), gensalt())
if MusicDatabase.setUserPassword(userID, hashed):
print('password changed successfully')
else:
print('Error changing password')
def set_name(self, name):
self.name = name
if self.id:
c = MusicDatabase.getCursor()
sql = text('UPDATE playlists SET name = :name WHERE id = :id')
c.execute(sql.bindparams(name=self.name, id=self.id))
c.commit()
def __init__(self, username=None):
"""Create a User object that can be used with Flask-Login."""
self.username = username
self.userID = MusicDatabase.getUserID(username)
self.is_authenticated = True
self.is_active = True
self.is_anonymous = False
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 load_from_db(where_clause='', where_values=None, tables=[],
order_by=None, limit=None):
c = MusicDatabase.getCursor()
if 'playlists' not in tables:
tables.insert(0, 'playlists')
statement = ('SELECT id, name, owner_id, playlist_type FROM %s %s' %
(','.join(tables), where_clause))
if order_by:
statement += ' ORDER BY %s' % order_by
if limit:
statement += ' LIMIT %d' % limit
if where_values:
print(statement)
print(where_values)
result = c.execute(text(statement).bindparams(**where_values))
else:
result = c.execute(text(statement))
r = [Playlist(x) for x in result.fetchall()]
return r
def setSongRating(self, user_id, song_id, rating):
try:
self.ratings[user_id][song_id] = rating
except KeyError:
self.ratings[user_id] = {}
self.ratings[user_id][song_id] = rating
c = MusicDatabase.conn.cursor()
sql = 'UPDATE ratings set rating = ? WHERE user_id = ? AND song_id = ?'
c.execute(sql, (rating, user_id, song_id))
if c.rowcount == 0:
c.execute(
'INSERT INTO ratings '
'(user_id, song_id, rating) '
'VALUES (?,?,?)', (user_id, song_id, rating))
MusicDatabase.commit()
def api_v1_song_search():
plman = app.bard.playlist_manager
sq = SearchQuery.from_request(request, current_user.userID, plman)
if not sq:
raise ValueError('No SearchQuery!')
pl = plman.get_search_result_playlist(sq)
if not pl:
pl = SearchPlaylist(sq)
plman.add_search_playlist(pl)
songs = MusicBrainzDatabase.search_songs_for_webui(sq.query, sq.offset,
sq.page_size)
song_ids = [song['song_id'] for song in songs]
for song_id in song_ids:
pl.append_song(song_id)
ratings = MusicDatabase.get_songs_ratings(song_ids, current_user.userID)
songs = [{
'rating': ratings[song['song_id']],
**dict(song)
} for song in songs]
result = {
'search_playlist_id': pl.searchPlaylistID,
'search_query': sq.as_dict(),
'songs': songs
}
return jsonify(result)
def songsWithSameAudio(self, song, sftp):
tgt_audio_sha256 = song.audioSha256sum()
# self.remoteFileAudioSha256Sum(target)
print(tgt_audio_sha256)
song_ids = MusicDatabase.songsByAudioTrackSha256sum(tgt_audio_sha256)
return [getSongsFromIDorPath(song_id)[0] for song_id in song_ids]
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 songAnalysis(song_id):
tab_col_keys = {}
for k, (t, c) in conversion_dict.items():
tab_col_keys.setdefault(t, {})[c] = k
c = MusicDatabase.getCursor()
result = {}
for t in tab_col_keys.keys():
sql = text(f'SELECT * FROM analysis.{t} '
'WHERE song_id = :song_id ')
values = c.execute(sql.bindparams(song_id=song_id)).fetchone()
if not values:
continue
for col, key in tab_col_keys[t].items():
if t == 'highlevel':
key = f'highlevel.{col}'
parent = result
parts = key.split('.')
for part in parts[:-1]:
parent = parent.setdefault(part, {})
# print(col)
# print(values)
# print(parts)
parent[parts[-1]] = values[col]
# result[key] = values[col]
return result
def compareAudioFiles(self, source, target, sftp):
print('Comparing songs...')
# src_song = getSongsAtPath(source, exact=True)[0]
src_song = Song(source)
tgt_song = remoteSong(target, sftp)
basename = os.path.basename(source)
colors = (Color.First, Color.Second)
printSongsInfo(src_song, tgt_song, useColors=colors)
if src_song.audioSha256sum() == tgt_song.audioSha256sum():
print(f'{basename} has exactly the same audio in source and '
'target files')
else:
print(f'Audio tracks are different in local and remote files')
comparison = src_song.audioCmp(tgt_song)
print(comparison)
tgt_audio_sha256 = tgt_song.audioSha256sum()
song_ids = MusicDatabase.songsByAudioTrackSha256sum(
tgt_audio_sha256)
same_audio_songs = [
getSongsFromIDorPath(songID)[0] for songID in song_ids
]
if tgt_audio_sha256 and same_audio_songs:
d = 'Files with exact same audio:\n'
d += '\n'.join(' ' + Color.CantCompareSongs +
song.path() + Color.ENDC
for song in same_audio_songs)
print(d)
else:
print(f'WARNING: The target file {target} will be overwritten '
'but has a unique audio track')
return False
def remove_analysis_for_song_id(self, song_id, *, connection=None):
c = connection or MusicDatabase.getCursor()
tables = list(set(x[0] for x in conversion_dict.values()))
tables += [
key.replace('.', '__').lower() + '_stats'
for key in keys_with_stats
]
tables += [
key.replace('.', '__').lower() + '_stats'
for key in keys_with_lists_stats
]
tables += [key.replace('.', '__').lower() for key in keys_with_lists]
tables += [key.replace('.', '__').lower() for key in fkeys_with_lists]
tables += [
key[0].replace('.', '__').lower()
for key in fkeys_with_lists_of_lists
]
for tablename in tables:
sql = text(f'DELETE FROM analysis.{tablename} '
'WHERE song_id = :song_id')
c.execute(sql.bindparams(song_id=song_id))
if not connection:
c.commit()
def moveFrom(self, prevSong):
"""Move prevSong to the location and values of self."""
print(f'Moving song from {prevSong.id}, {prevSong.path()}\n'
f" to {getattr(self, 'id', None)}, {self.path()}")
if not self._path:
return False
r = MusicDatabase.moveSong(prevSong.id, self._path, self._root)
if r:
self.id = prevSong.id
prevSong._path = self._path
prevSong._root = self._root
albumID = MusicDatabase.getAlbumID(albumPath(self._path))
MusicDatabase.setSongInAlbum(self.id, albumID)
return r
def has_analysis_for_song_id(self, song_id, *, connection=None):
if not connection:
connection = MusicDatabase.getCursor()
sql = text('select song_id from analysis.highlevel '
f' where song_id = {song_id}')
result = connection.execute(sql)
return bool(result.fetchone())
def setSongRating(self, user_id, song_id, rating):
try:
self.ratings[user_id][song_id] = rating
except KeyError:
self.ratings[user_id] = {}
self.ratings[user_id][song_id] = rating
c = MusicDatabase.conn.cursor()
sql = 'UPDATE ratings set rating = ? WHERE user_id = ? AND song_id = ?'
c.execute(sql, (rating, user_id, song_id))
if c.rowcount == 0:
c.execute('INSERT INTO ratings '
'(user_id, song_id, rating) '
'VALUES (?,?,?)',
(user_id, song_id, rating))
MusicDatabase.commit()
def findPairs(songs1, songs2):
pairs = {}
songsRemainingFrom1 = songs1.copy()
songsRemainingFrom2 = songs2.copy()
print('Getting pairs from', songs1)
print(' and ', songs2)
for song1 in songs1:
similarSongsIn2 = []
for song2 in songs2:
similarity = MusicDatabase.songsSimilarity(song1.id, song2.id)
# print(song1.id, song2.id, similarity)
if similarity >= 0.85:
similarSongsIn2.append((song2, similarity))
with suppress(ValueError):
songsRemainingFrom1.remove(song1)
with suppress(ValueError):
songsRemainingFrom2.remove(song2)
if similarSongsIn2:
pairs[song1] = similarSongsIn2
if len([x for x in similarSongsIn2 if x[1] == 1.0]) > 1:
colors = (TerminalColors.Warning, TerminalColors.ENDC)
print('%sWARNING: Repeated songs in the same directory!%s' %
colors)
for x in similarSongsIn2:
print(x)
return pairs, songsRemainingFrom1, songsRemainingFrom2
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 loadMetadataInfo(self):
if getattr(self, 'metadata', None) is None:
self.loadMetadata()
elif getattr(self.metadata, 'info', None) is not None:
return
(self._format, self.metadata.info, self._audioSha256sum, silences) = \
MusicDatabase.getSongProperties(self.id)
self._silenceAtStart = silences[0]
self._silenceAtEnd = silences[1]
def loadMetadata(self):
if getattr(self, 'metadata', None) is not None:
return
if getattr(self, 'id', None) is not None:
self.metadata = type('info', (dict,), {})()
self.metadata.update(MusicDatabase.getSongTags(self.id))
return
self.loadFile(self._path)
if self.metadata is None:
raise Exception("Couldn't load metadata!")
def loadCoverImageData(self, path):
self._coverWidth, self._coverHeight = 0, 0
self._coverMD5 = ''
random_number = random.randint(0, 100000)
coverfilename = os.path.join(config['tmpdir'],
'/cover-%d.jpg' % random_number)
MusicDatabase.addCover(path, coverfilename)
# c = self.conn.cursor()
# values = [ ( path, coverfilename), ]
# c.executemany('''INSERT INTO covers(path, cover) VALUES (?,?)''',
# values)
command = ['ffmpeg', '-i', path, '-map', '0:m:comment:Cover (front)',
'-c', 'copy', coverfilename]
process = subprocess.run(command, stdout=subprocess.DEVNULL,
stderr=subprocess.DEVNULL)
if process.returncode != 0:
# try with any image in the file
process = subprocess.run(['ffmpeg', '-i', path, '-c', 'copy',
coverfilename],
stdout=subprocess.DEVNULL,
stderr=subprocess.DEVNULL)
if process.returncode != 0:
return
try:
image = Image.open(coverfilename)
self._coverWidth, self._coverHeight = image.size
self._coverMD5 = md5(coverfilename)
except IOError:
print('Error reading cover file from %s' % path)
return
os.unlink(coverfilename)
def audioCmp(self, other, forceSimilar=False, interactive=True,
useColors=None, printSongsInfoCallback=None,
forceInteractive=False):
"""Compare the audio of this object with the audio of other.
Returns -1 if self has better audio than other,
1 if other has better audio than self and 0 if they have
audio of the same characteristics. Also, it can raise
a SongsNotComparableException exception if audio has
different length or it's not similar according to
chromaprint fingerprints
"""
self.loadMetadataInfo()
other.loadMetadataInfo()
if self._audioSha256sum == other._audioSha256sum:
return 0
if (not forceSimilar and getattr(self, 'id', None) and
getattr(other, 'id', None) and
not MusicDatabase.areSongsSimilar(self.id, other.id)):
raise DifferentSongsException(
'Trying to compare different songs (%d and %d)'
% (self.id, other.id))
len_diff = abs(self.durationWithoutSilences() -
other.durationWithoutSilences())
if len_diff > 30:
raise DifferentLengthException(
'Songs duration is too different (%f and %f seconds / %f and %f seconds)'
% (self.durationWithoutSilences(), other.durationWithoutSilences(),
self.metadata.info.length, other.metadata.info.length))
if len_diff > 5:
print(self.duration(), self.durationWithoutSilences(), self.silenceAtStart(), self.silenceAtEnd())
raise SlightlyDifferentLengthException(
'Songs duration is slightly different (%f and %f seconds / %f and %f seconds)'
% (self.durationWithoutSilences(), other.durationWithoutSilences(),
self.metadata.info.length, other.metadata.info.length))
if not forceInteractive:
if self.isLossless() and not other.isLossless():
return -1
if other.isLossless() and not self.isLossless():
return 1
si = self.metadata.info
oi = other.metadata.info
# Be sure the self.metadata.info structure contains all information
sbps = self.bits_per_sample()
self.bitrate()
obps = other.bits_per_sample()
other.bitrate()
if not forceInteractive:
if si.bitrate > oi.bitrate * 1.12 \
and ((sbps and obps and sbps >= obps) or
(not sbps and not obps)) \
and si.channels >= oi.channels \
and si.sample_rate >= oi.sample_rate:
return -1
if oi.bitrate > si.bitrate * 1.12 \
and ((sbps and obps and obps >= sbps) or
(not sbps and not obps)) \
and oi.channels >= si.channels \
and oi.sample_rate >= si.sample_rate:
return 1
# if self.completeness > other.completeness:
# return -1
#
# if other.completeness > self.completeness:
# return 1
if oi.bitrate//1000 == si.bitrate//1000 \
and ((sbps and obps and obps == sbps) or
(not sbps and not obps)) \
and oi.channels == si.channels:
if oi.sample_rate > si.sample_rate:
return 1
elif si.sample_rate > oi.sample_rate:
return -1
else:
return 0
if interactive or forceInteractive:
if printSongsInfoCallback:
printSongsInfoCallback(self, other)
filename1 = '/tmp/1'
filename2 = '/tmp/2'
shutil.copyfile(self.path(), filename1)
shutil.copyfile(other.path(), filename2)
result = manualAudioCmp(filename1, filename2, useColors=useColors)
os.unlink(filename1)
os.unlink(filename2)
if result or result == 0:
return result
raise CantCompareSongsException('Not sure how to compare songs')