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