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 @@
+#!/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)