1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
|
#!/usr/bin/env python
import sqlite3
import bottle
import urllib
db = sqlite3.connect('iTunesLibrary.db')
db.row_factory = sqlite3.Row
ARTIST_COUNT_QUERY = """
select a.*, count(t.track_id) as total_tracks
from track t
inner join artist a on t.artist = a.id
{where}
group by t.artist
"""
GENRE_COUNT_QUERY = """
select g.*, count(t.track_id) as total_tracks
from track t
inner join genre g on t.genre = g.id
{where}
group by t.genre
"""
ALBUM_COUNT_QUERY = """
select a.id, a.title, count(t.track_id) as total_tracks
from track t
inner join album a on t.album = a.id
{where}
group by t.album
"""
TRACK_QUERY = """
select
t.track_id, t.name, t.track_number, t.disc_number,
ar.artist,
ab.title, ab.track_count, ab.disc_count,
g.name
from track t
inner join artist ar on t.artist = ar.id
inner join album ab on t.album = ab.id
inner join genre g on t.genre = g.id
{where}
"""
def _format_json(curs):
data = []
for item in curs.fetchall():
data.append({
'id': item[0],
'name': item[1].encode('utf-8'),
'tracks': item[2],
})
return data
def _run_query(curs, query, where="", params=None):
query = query.format(where=where)
if params:
curs.execute(query, params)
else:
curs.execute(query)
def _build_where(query):
where = []
params = []
if 'genre' in query:
where.append('t.genre = ?')
params.append(query['genre'])
if 'artist' in query:
where.append('t.artist = ?')
params.append(query['artist'])
if 'album' in query:
where.append('t.album = ?')
params.append(query['album'])
if where:
where = 'WHERE ' + ' AND '.join(where)
else:
where = ""
return where, params
@bottle.route('/genre')
def genre_controller():
curs = db.cursor()
curs.execute(GENRE_COUNT_QUERY.format(where=""))
return { 'data': _format_json(curs) }
@bottle.route('/browser')
def browser_controller():
curs = db.cursor()
where, params = _build_where(bottle.request.query)
_run_query(curs, ARTIST_COUNT_QUERY, where, params)
artists = _format_json(curs)
_run_query(curs, GENRE_COUNT_QUERY, where, params)
genres = _format_json(curs)
_run_query(curs, ALBUM_COUNT_QUERY, where, params)
albums = _format_json(curs)
return {
'data': {
'artists': artists,
'genres': genres,
'albums': albums,
}
}
@bottle.route('/tracks')
def tracks_controller():
curs = db.cursor()
where, params = _build_where(bottle.request.query)
_run_query(curs, TRACK_QUERY, where, params)
return { 'data': [dict(row) for row in curs.fetchall()] }
bottle.debug(True)
bottle.run(host='localhost', port=8100)
|
