def addtracks(self):
sb_dir = qc.query('scatterbrainz_dir')
music_lib = qc.query('music_lib')
tracks_dir = os.path.join(music_lib,'tracks')
dbfile = qc.query('music_dbfile')
sqw = swrap.sqliteWrapper(dbfile)
gidlist = []
if not os.path.isdir(tracks_dir): os.mkdir(tracks_dir)
for (base, dirs, files) in os.walk(music_lib,followlinks = True):
for f in files:
ext = os.path.splitext(f)[-1]
if ext == '.mp3':
f_abs = os.path.join(base,f)
mutagen = MP3(f_abs)
##for some reason that nobody will probably ever
##understand, this does not work with EasyID3
tkey = u'TXXX:MusicBrainz Track Id'
if tkey in mutagen.tags.keys():
gid =mutagen[tkey].text[0]
link = os.path.join(tracks_dir,gid)
if not gid in gidlist: gidlist.append(gid)
if not os.path.isfile(link):
os.link(f_abs,link)
else:
print 'no musicbrainz id for' + f_abs
for gid in gidlist:
sqw.query("""INSERT OR IGNORE INTO tracklist(gid) values('"""+gid+"""');""")
sqw.commit()
sqw.close()
n = len(gidlist)
return sjson.dumps('moved '+str(n)+' tracks to '+tracks_dir+' , indexed in '+dbfile)
def listUsers():
import sqliteWrapper as sqw
db = sqw.sqliteWrapper(qc.query('config_dbfile'))
d = db.queryToDict('select name from user;');
db.close
return d
def friendAddressesJSON(self):
sqw = swrap.sqliteWrapper(qc.query('friends_dbfile'))
knows = sqw.queryToDict("SELECT address, sb_port FROM known, friends WHERE known.id = friends.known")
sqw.close
urls = []
for k in knows:
urls.append("http://"+k['address']+':'+str(k['sb_port']))
return sjson.dumps(urls)
def autoset(prefname):
cdb = query('config_dbfile')
sqw = swrap.sqliteWrapper(cdb)
if prefname == 'sb_address':
val = unicode(autogetip())
sqw.query("""
UPDATE global SET value = :val WHERE name = :prefname;
""", params={'val':val,'prefname':prefname})
sqw.commit()
def userID(username):
import sqliteWrapper as sqw
db = sqw.sqliteWrapper(qc.query('config_dbfile'))
d = db.queryToDict('select id from user where name = :username;',params = {'username':username});
db.close
if len(d) == 0:
return None
else:
return d[0]['id']
def friendallknown(self):
sqw = swrap.sqliteWrapper(qc.query('friends_dbfile'))
knows = sqw.queryToDict("SELECT id FROM known")
for k in knows:
sqw.query("""
INSERT OR IGNORE INTO friends(known) values(:kid);
"""
,params={'kid':k['id']})
sqw.commit()
sqw.close()
return sjson.dumps("friended each known server, Total: "+str(len(knows)))
def addknown(self):
fdb = qc.query('friends_dbfile')
sqw = swrap.sqliteWrapper(fdb)
sw_address = "94.100.21.250"
sw_port = "5554"
sw_pg = "64077"
sqw.query("INSERT OR IGNORE into known(address, pg_port,sb_port) values(:address,:pg_port,:sb_port);",params={'address':sw_address,'pg_port':sw_pg,'sb_port':sw_port})
sqw.commit()
sqw.close()
return sjson.dumps("Added the default friend at swiftway")
def listPrefs(username = None):
import sqliteWrapper as sqw
db = sqw.sqliteWrapper(qc.query('config_dbfile'))
if username:
uid = userID(username);
if not uid : return None
d = db.queryToDict('select name, value from user_prefs where user = :uid;',params = {'uid':uid});
else:
d = db.queryToDict('select name, value from global;')
db.close
return d
def hearabout(self):
p = request.params;
address = p['address']
sb_port = p['sb_port']
pg_port = p['pg_port']
sqw = swrap.sqliteWrapper(qc.query('friends_dbfile'))
sqw.query("""
INSERT OR IGNORE INTO
known(address,pg_port,sb_port)
values(:address,:pg_port,:sb_port)
"""
,params={'address':address,'sb_port':sb_port,'pg_port':pg_port})
sqw.commit()
sqw.close()
def query(prefname):
dbfile = os.path.join(os.environ['SCATTERBRAINZ_DIR'],'external/dbs/config/config.sqlite')
sqw = swrap.sqliteWrapper(dbfile)
d = sqw.queryToDict('''
SELECT name, value FROM global;
''')
d = sqw.queryToDict("""
SELECT
name, value
FROM
global
WHERE
name = '"""+prefname+"""';
""")
sqw.close()
return d[0]['value']
def makeUsers():
sqw = swrap.sqliteWrapper(config_dbfile)
sb_user = os.environ['SB_USER']
sb_pass = os.environ['SB_PASS']
print( "SB USER IS: "+ sb_user)
sqw.query("""
INSERT INTO user(name,password) values(:uname,:upass);
""",params = {'uname':sb_user,'upass':sb_pass})
sqw.query("""
INSERT INTO user(name,password) values(:uname,:upass);
""",params = {'uname':'anon','upass':''})
sqw.commit()
sqw.close()
def readPref(name, username=None):
db = sqw.sqliteWrapper(qc.query("config_dbfile"))
if not username:
d = db.queryToDict("""select value from global where name = :name""", params={"name": name})
else:
d = db.queryToDict(
"""
select value
from user_prefs, user
where user_prefs.name = :name
and user.name = :username
and user_prefs.user = user.id;
""",
params={"username": username, "name": name},
)
db.close()
if len(d) == 0:
return None
else:
return d[0]["value"]
def makeGlobalPrefs():
scatterbrainz_dir = os.environ['SB_DIR']
media_dir = os.environ['SB_MUSIC_LIB_ABS']
music_dir = os.environ['SB_MUSIC_DIR']
music_dbfile = os.environ['SB_MUSIC_DB']
config_dir = os.environ['SB_CONF_DIR']
config_dbfile = os.environ['SB_CONF_DB']
mb_port = os.environ['MB_PORT']
mb_host = os.environ['MB_HOST']
friends_dir = os.environ['SB_FRIENDS_DIR']
friends_dbfile=os.environ['SB_FRIENDS_DB']
sb_address=os.environ['SB_ADDRESS']
sb_port=os.environ['SB_PORT']
sqw = swrap.sqliteWrapper(config_dbfile)
init_prefs = {
'mbhost' : mb_host,
'mbport' : mb_port,
'mbdb' : 'musicbrainz_db',
'mbuser' : 'bh0085',
'scatterbrainz_dir':scatterbrainz_dir,
'music_lib':media_dir,
'music_dir':music_dir,
'music_dbfile':music_dbfile,
'friends_dir':friends_dir,
'friends_dbfile':friends_dbfile,
'config_dir':config_dir,
'config_dbfile':config_dbfile,
'sb_address':sb_address,
'sb_port':sb_port
}
for k, item in init_prefs.iteritems():
sqw.query("""
INSERT INTO global(name,value) values('"""+k+"""','"""+item+"""');
""")
sqw.commit()
sqw.close()
def deletePref(name, username=None):
db = sqw.sqliteWrapper(qc.query("config_dbfile"))
if not username:
db.query(
"""
DELETE FROM global
WHERE name =:name;
""",
params={"name": name},
)
else:
uid = db.queryToDict("""SELECT id FROM user WHERE name = :username;""", params={"username": username})[0]["id"]
d = db.query(
"""
DELETE FROM user_prefs
WHERE name = :name
AND user = :user;
""",
params={"name": name, "user": uid},
)
db.commit()
db.close()
def writePref(name, value, username=None):
db = sqw.sqliteWrapper(qc.query("config_dbfile"))
if not username:
db.query(
"""
INSERT OR REPLACE INTO
global(name, value)
values(:name,:value) ;
""",
params={"name": name, "value": value},
)
else:
uid = db.queryToDict("""SELECT id FROM user WHERE name = :username;""", params={"username": username})[0]["id"]
d = db.query(
"""
INSERT OR REPLACE INTO
user_prefs(name, value, user)
values(:name,:value,:user);
""",
params={"name": name, "value": value, "user": uid},
)
db.commit()
db.close()
def userExists(uname):
dbf = qc.query('config_dbfile')
e = sqw.sqliteWrapper(dbf).exists("select * from user where user.name = :uname",{'uname':uname})
return e
def getFriends(self):
sqw = swrap.sqliteWrapper(qc.query('friends_dbfile'))
d = sqw.queryToDict("select address, sb_port, pg_port from friends")
sqw.close()
return sjson.dumps(d)
'sb_port':sb_port
}
for k, item in init_prefs.iteritems():
sqw.query("""
INSERT INTO global(name,value) values('"""+k+"""','"""+item+"""');
""")
sqw.commit()
sqw.close()
print 'Initializing the config db'
config_dbfile = os.environ['SB_CONF_DB']
sqw = swrap.sqliteWrapper(config_dbfile)
d = sqw.queryToDict('''select name from sqlite_master where type = 'table';''')
print 'Resetting all config DB tables.'
for table in d:
sqw.query("""drop table '""" + table['name'] + """';""")
d = sqw.queryToDict('''select name from sqlite_master where type = 'table';''')
if not 'global' in map(lambda x: x['name'],d):
sqw.query('''
CREATE TABLE global(
id INTEGER PRIMARY KEY,
name TEXT UNIQUE,
value);
''')
if not 'user_prefs' in map(lambda x: x['name'],d):
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)
def userPassword(uname):
dbf = qc.query('config_dbfile')
pw = sqw.sqliteWrapper(dbf).queryToDict("select password from user where user.name = :uname",{'uname':uname})[0]['password']
return pw
def wrap(dbname):
import sqliteWrapper as sqw
dbfile = qc.query(dbname+'_dbfile')
db = sqw.sqliteWrapper(dbfile)
return db
def list(self):
fdb = qc.query('friends_dbfile')
sqw = swrap.sqliteWrapper(fdb)
d = sqw.queryToDict("select * from friends;")
sqw.close()
return sjson.dumps(d)
def init(sb_user):
"""
Initialize the plugin db.
"""
dbfile = prefs.readPref('what_dbfile',sb_user)
db = sqw.sqliteWrapper(dbfile)
d = db.queryToDict('''select name from sqlite_master where type = 'table';''')
for table in d:
db.query("""drop table '""" + table['name'] + """';""")
db.query("""
CREATE TABLE config(
id INTEGER PRIMARY KEY,
name TEXT UNIQUE,
value TEXT
)
""")
insertPrefs(db,sb_user);
db.query("""
CREATE TABLE artist(
id INTEGER PRIMARY KEY,
whatid INTEGER UNIQUE,
name TEXT
)""")
db.query("""
CREATE TABLE release(
id INTEGER PRIMARY KEY,
whatid INTEGER UNIQUE,
name TEXT
)""")
db.query("""
CREATE TABLE artist_gids(
id INTEGER PRIMARY KEY,
artist INTEGER,
gid TEXT,
FOREIGN KEY(artist) REFERENCES artist(id)
);""")
db.query("""
CREATE TABLE release_gids(
id INTEGER PRIMARY KEY,
release INTEGER,
gid TEXT,
FOREIGN KEY(release) REFERENCES release(id)
);""")
db.query("""
CREATE TABLE artist_html(
id INTEGER PRIMARY KEY,
artist INTEGER UNIQUE,
filename TEXT,
FOREIGN KEY(artist) REFERENCES artist(id)
)""")
db.query("""
CREATE TABLE artist_release(
id INTEGER PRIMARY KEY,
artist INTEGER,
release INTEGER UNIQUE,
FOREIGN KEY(release) REFERENCES release(id),
FOREIGN KEY(artist) REFERENCES artist(id)
)""")
db.query("""
CREATE TABLE release_html(
id INTEGER PRIMARY KEY,
release INTEGER UNIQUE,
filename TEXT,
FOREIGN KEY(release) REFERENCES release(id)
)""")
db.commit()
db.close()
for opt, arg in opts:
if opt in ("-h", "--help"):
usage()
sys.exit()
elif opt == '--reset-all':
do_reset = True
elif opt == '--reset-meta':
do_reset_meta = True
sb_dir = qc.query('scatterbrainz_dir')
music_dir = qc.query('music_dir')
tracks_dir = os.path.join(music_dir,'tracks')
dbfile = qc.query('music_dbfile')
sqw = swrap.sqliteWrapper(dbfile)
if do_reset:
d = sqw.queryToDict('''select name from sqlite_master where type = 'table';''')
print 'Resetting all music tables'
for table in d:
sqw.query("""drop table '""" + table['name'] + """';""")
if do_reset_meta:
d = sqw.queryToDict('''select name from sqlite_master where type = 'table';''')
print 'Resetting music meta tables'
for table in d:
if table['name'][-4:] == 'meta':
sqw.query("""drop table '""" + table['name'] + """';""")
d = sqw.queryToDict('''select name from sqlite_master where type = 'table';''')
