def _artistMatchFromTokens(tokens):
m = mbw.mbrainzWrapper()
pops = []
entries = []
for t in tokens:
pops.append((t,wordUseCount(t,'artist',m)))
tokens_s = sorted(pops, key = lambda x: x[1])
for i in range(len(tokens_s)):
word = tokens_s[i][0]
if i == 0:
m.query("""
create temporary table matched_artists as
select artist.id as art_id, artist.name as art_name, artist.gid as gid
from artist, artistwords, wordlist
where artistwords.artistid = artist.id
and artistwords.wordid = wordlist.id
and wordlist.word = %(word)s;
""",
{"word":word})
else:
m.query("""
delete
from matched_artists
using
(select count( wordid ), art_name
from
(
select *
from
matched_artists
LEFT OUTER JOIN
(select *
from artistwords RIGHT JOIN wordlist
ON ( artistwords.wordid = wordlist.id)
where word = %(word)s ) as firstjoin
ON ( art_id = artistid )
) as joined
group by art_name) as counted
where counted.count = 0
and counted.art_name = matched_artists.art_name
""",
{'word':word})
d = m.queryToDict("select * from matched_artists")
m.close()
entries.extend(d)
return entries
def matchArtistString(artist):
matchType= 'fast'
if matchType == 'fast':
tokens = tokenize(artist)
matches = _artistMatchFromTokens(tokens)
elif matchType == 'trgm':
m = mbw.mbrainzWrapper()
match_limit = .4
d = m.queryToDict("""
select set_limit( .6 );
select gid, id, name
from artist
where name %% %(artist_name)s;
""",{'match_limit':match_limit,'artist_name':artist})
m.close()
matches = d
return matches
def addmeta(self):
mdb = swrap.sqliteWrapper(qc.query('music_dbfile'))
tracks = mdb.queryToDict("""
SELECT gid, id from tracklist
""")
mbw = mbwrap.mbrainzWrapper()
#meta fields:
#list, gid, name, number, length, album, artist
out =[]
for t in tracks:
#METADATA GATHERING LOOP!
d = mbw.queryToDict("""
SELECT
track.name as track_name,
track.length as track_length,
albumjoin.sequence as track_number,
artist.name as artist_name,
album.name as album_name,
artist.gid as artist_gid,
album.gid as album_gid
FROM
album, artist, track, albumjoin
WHERE
track.artist=artist.id AND
albumjoin.track=track.id AND
albumjoin.album=album.id AND
track.gid= %(track_gid)s
LIMIT 1;
"""
, params={'track_gid':t['gid']} )
r = d[0]
mdb.query('INSERT OR IGNORE INTO artist(name,gid) values( :artist_name, :artist_gid);',params = {'artist_name':r['artist_name'],
'artist_gid':r['artist_gid']})
ans=mdb.queryToDict('select id from artist where gid= :artist_gid;' ,params = {'artist_gid':r['artist_gid']})
artist_id = ans[0]['id']
mdb.query('INSERT OR IGNORE INTO album(name,gid,artist) values( :album_name, :album_gid,:artist_id);'
,params = {'album_name':r['album_name'],
'artist_id':artist_id,
'album_gid':r['album_gid']})
album_id = mdb.queryToDict('SELECT id FROM album WHERE gid = :album_gid',params = {'album_gid':r['album_gid']})[0]['id']
artist_id = mdb.queryToDict('''
SELECT id
FROM artist
WHERE gid = :artist_gid''',params = {'artist_gid':r['artist_gid']})[0]['id']
mdb.query("""
INSERT OR IGNORE INTO
track(listing, gid, name, number, length, album, artist)
values(:track_id, :track_gid,:track_name, :track_number, :track_length, :album_id, :artist_id)"""
,params={'album_id':album_id,
'artist_id':artist_id,
'track_number':r['track_number'],
'track_length':r['track_length'],
'track_name':r['track_name'],
'track_gid':t['gid'],
'track_id':t['id']})
mdb.query("""
INSERT OR IGNORE INTO
albumjoin(track, album, artist)
values(:track_id, :album_id, :artist_id)
""",
params = {'album_id':album_id,
'artist_id':artist_id,
'track_id':t['id']})
mdb.commit()
d = mdb.queryToDict("SELECT name FROM artist")
mdb.close()
mbw.close()
strout = ''
for i in d:
strout = strout + i['name']
return sjson.dumps(strout)
