add package search feature

3 files changed, 97 insertions, 1 deletions

diff --git a/server/app.py b/server/app.py
index e957cde..fcef84f 100755
--- a/server/app.py
+++ b/server/app.py
@@ -14,6 +14,7 @@ from repo import Repo
 from lang import Lang
 from package import Package
 from host import Host
+from search import Search
 
 urls = (
         r'', 'Index',
@@ -30,7 +31,8 @@ urls = (
         r'/package', 'Package',
         r'/use/(.+)', 'Use',
         r'/use', 'Use',
-        r'/host/(.+)', 'Host'
+        r'/host/(.+)', 'Host',
+        r'/search', 'Search'
         )
 
 app = web.application(urls, globals(), autoreload=True)
diff --git a/server/search.py b/server/search.py
new file mode 100644
index 0000000..69170ac
--- /dev/null
+++ b/server/search.py
@@ -0,0 +1,80 @@
+
+import web
+import string
+from config import render, db
+
+what = ['CAT', 'PKG', 'VER', 'REPO', 'COUNT(DISTINCT UUID) AS HOSTS']
+order_by = ['HOSTS DESC','CAT', 'PKG', 'VER', 'REPO']
+group_by = ['CAT', 'PKG', 'VER', 'REPO']
+which = ['PACKAGES','INSTALLED_PACKAGES','REPOSITORIES']
+
+class Search(object):
+
+    def GET(self):
+        self.args = web.input(cat='any', pkg='any', ver='any', repo='any')
+
+        try:
+            self.min_hosts = int(web.input(min_hosts=-1).min_hosts)
+        except ValueError:
+            self.min_hosts = -1
+
+        try:
+            self.max_hosts = int(web.input(max_hosts=-1).max_hosts)
+        except ValueError:
+            self.max_hosts = -1
+
+        where = self._build_where()
+        having = self._build_having()
+        query = self._build_query(where, having)
+        search_tuples = db.query(query, vars={
+            'cat':self.args.cat,
+            'pkg':self.args.pkg,
+            'ver':self.args.ver,
+            'repo':self.args.repo,
+            'min_hosts':self.min_hosts,
+            'max_hosts':self.max_hosts})
+        return render.search(search_tuples)
+
+    def _build_query(self, where, having):
+        sep = ' '
+        query = ''
+        query += 'SELECT' + sep + ','.join(what) + sep
+        query += 'FROM' + sep + (sep + 'NATURAL LEFT OUTER JOIN' + sep).join(which) + sep
+        if len(where) != 0:
+            query += 'WHERE' + sep
+            query += (sep + 'AND' + sep).join(where)
+        query += sep
+        query += 'GROUP BY' + sep + ','.join(group_by) + sep
+        if len(having) != 0:
+            query += 'HAVING' + sep
+            query += (sep + 'AND' + sep).join(having)
+        query += sep
+        query += 'ORDER BY' + sep + ','.join(order_by) + sep
+        return query.strip()
+
+    def _build_where(self):
+        where = []
+        cat = string.lower(self.args.cat)
+        if cat != 'any':
+            where.append('CAT=$cat')
+
+        pkg = string.lower(self.args.pkg)
+        if pkg != 'any':
+            where.append('PKG=$pkg')
+
+        ver = string.lower(self.args.ver)
+        if ver != 'any':
+            where.append('VER=$ver')
+
+        repo = string.lower(self.args.repo)
+        if repo != 'any':
+            where.append('REPO=$repo')
+        return where
+
+    def _build_having(self):
+        having = []
+        if self.min_hosts != -1:
+            having.append('HOSTS>=$min_hosts')
+        if self.max_hosts != -1:
+            having.append('HOSTS<=$max_hosts')
+        return having
diff --git a/server/templates/search.html b/server/templates/search.html
new file mode 100644
index 0000000..c2ae39c
--- /dev/null
+++ b/server/templates/search.html
@@ -0,0 +1,14 @@
+$def with (tuples)
+$var title: Search
+
+<table border="1">
+    <tr>
+        <th>Category</th>
+        <th>Package</th>
+        <th>Version</th>
+        <th>Repository</th>
+        <th>Hosts</th>
+    </tr>
+    $for t in tuples:
+      <tr><td>$t['CAT']</td><td>$t['PKG']</td><td>$t['VER']</td><td>$t['REPO']</td><td>$t['HOSTS']</td></tr>
+</table>