From 443ed887df16f6d384b5ccedb19efcd9b60f3549 Mon Sep 17 00:00:00 2001 From: Stefan Huber Date: Sat, 18 Jan 2014 16:35:00 +0100 Subject: [PATCH] dvr-managedb: Add REGEXP support --- dvr-createmap | 149 -------------------------------------------------- dvr-managedb | 8 ++- 2 files changed, 7 insertions(+), 150 deletions(-) delete mode 100755 dvr-createmap diff --git a/dvr-createmap b/dvr-createmap deleted file mode 100755 index 5ffaa5b..0000000 --- a/dvr-createmap +++ /dev/null @@ -1,149 +0,0 @@ -#!/usr/bin/env python3 - - -import bs4 -import getopt -import os -import re -import sqlite3 -import sys -import urllib.request -import urllib.parse -import urllib.error -import json -import gzip -import time - - -def queryLocation(address): - """Get (lat, lng) pair of given postal address.""" - - urlargs = {} - urlargs['address'] = address - urlargs['sensor'] = "false" - urlparam = urllib.parse.urlencode(urlargs) - - url = "http://maps.googleapis.com/maps/api/geocode/json?" + urlparam - try: - response = urllib.request.urlopen(url).read().decode('utf-8') - data = json.loads(response) - print(data) - loc = data['results'][0]['geometry']['location'] - return loc['lat'], loc['lng'] - except urllib.error.URLError as e: - print(e, file=sys.stderr) - return None - - -def printHTML(rows): - """Print HTML page with the given result set""" - - print(""" - - - - - - - - -

- - - """) - - -def usage(): - """Print help text.""" - - print(""" -USAGE: - {0} -d FILE [where clause] - {0} -h - -Creates a html file loading a google map with bubbles at the locations of the -registrations selected using the where clause. - -OPTIONS: - -d FILE Use given sqlite3 database. - -h Print this help text. -""") - - -if __name__ == "__main__": - - add = None - dbfn = None - query = None - showLocation = False - - try: - opts, args = getopt.getopt(sys.argv[1:], "d:h") - - for opt, arg in opts: - if opt == "-d": - dbfn = arg - elif opt == "-h": - usage() - sys.exit(os.EX_OK) - else: - print("Unknown option '", opt, "'.", file=sys.stderr) - assert(False) - - except getopt.GetoptError as e: - print("Error parsing arguments:", e, file=sys.stderr) - usage() - sys.exit(os.EX_USAGE) - - if dbfn is None: - print("No database given.", file=sys.stderr) - sys.exit(os.EX_USAGE) - - conn = sqlite3.connect(dbfn, timeout=60) - conn.execute("PRAGMA foreign_keys = ON") - conn.execute("PRAGMA journal_mode = MEMORY") - conn.execute("PRAGMA synchronous = OFF") - - stmt = "SELECT * FROM dvrtable, registrations, purposes" - stmt += " WHERE dvrtable.id=registrations.dvrid AND dvrtable.id=purposes.dvrid" - if len(args) > 0: - stmt += " AND " + " ".join(args) - c = conn.cursor() - - superstmt = "SELECT DISTINCT id, dvr, name, address FROM (%s)" % stmt - c.execute(superstmt) - printHTML(c.fetchall()) - - sys.exit(os.EX_OK) diff --git a/dvr-managedb b/dvr-managedb index 7db2daa..f8684ea 100755 --- a/dvr-managedb +++ b/dvr-managedb @@ -101,7 +101,6 @@ class GeolocationProviders(object): assert(GeolocationProviders.isNameValid(name)) return [p.name().lower() for p in GeolocationProviders.providers].index(name.lower()) - class Database: """Encapsualtes a DVR database.""" @@ -111,8 +110,15 @@ class Database: self.conn.execute("PRAGMA foreign_keys = ON") self.conn.execute("PRAGMA journal_mode = MEMORY") self.conn.execute("PRAGMA synchronous = OFF") + self.conn.create_function("REGEXP", 2, Database.regexp) self.createSchema() + @staticmethod + def regexp(expr, item): + """The REGEXP method for sqlite""" + reg = re.compile(expr) + return reg.search(item) is not None + def createSchema(self): """Create the database schema""" conn = self.conn -- 2.39.5