import subprocess
import urllib2
import sqlite3
import pickle
import httplib
import urllib2
import json

mysql_bin = '/opt/local/lib/mysql56/bin/mysql'


def run_query(dbconf, query):
    """Run a SQL query and return a list of lists of values."""
    proc_output = subprocess.check_output([
        mysql_bin, 
        '--defaults-extra-file=%s' % dbconf,
        '--raw', 
        '--batch', 
        '--skip-column-names', 
        '-e',
        query
        ]).split("\n")[:-1]
    return map(lambda line: line.split("\t"), proc_output)


class Cache:
    def __init__(self, name):
        self.name = name
        self.dbconn = sqlite3.connect(name + ".db")
        c = self.dbconn.cursor()
        c.execute("CREATE TABLE IF NOT EXISTS cache (name TEXT, value BLOB, created TEXT, ttl INTEGER, PRIMARY KEY(name))")
        self.dbconn.commit()

    def get(self, key):
        c = self.dbconn.cursor()
        c.execute("SELECT value FROM cache WHERE name = ? AND datetime(created, ttl || ' seconds') > datetime('now')", (str(key),))
        row = c.fetchone()
        if row:
            return pickle.loads(row[0])
        else:
            return None

    def get_or_else(self, key, f, ttl_secs):
        value_from_cache = self.get(key)
        if value_from_cache:
            return value_from_cache
        else:
            try:
                value_from_f = f()
                self.put(key, value_from_f, ttl_secs)
                return value_from_f
            except:
                raise

    def put(self, key, value, ttl_secs):
        c = self.dbconn.cursor()
        c.execute("INSERT OR REPLACE INTO cache (name, value, created, ttl) VALUES (?, ?, datetime('now'), ?)", (key, pickle.dumps(value), ttl_secs))
        self.dbconn.commit()

    def delete(self, key):
        c = self.dbconn.cursor()
        c.execute("DELETE FROM cache WHERE name = ?", (str(key),))

def http_get_json(conn, path):
    print("Fetch http://%s:%d%s" % (conn.host, conn.port, path))
    conn.request('GET', path)
    json_data = conn.getresponse().read()
    return json.loads(json_data)

def http_get_json_simple(url):
    print("Fetch %s" % url)
    return json.loads(urllib2.urlopen(url).read())