# Database access

import random

import re

import sys

import ex_util

def _add_cursor_info(ex, cur): ex_util.add_msg(ex, 'query: '+cur.query)

class NameException(Exception): pass

class DbException(ex_util.ExceptionWithCause):

    def __init__(self, msg, cause=None, cur=None):

        ex_util.ExceptionWithCause.__init__(self, msg, cause)

        if cur != None: _add_cursor_info(self, cur)

class ExceptionWithColumn(DbException):

    def __init__(self, col, cause=None):

        DbException.__init__(self, 'column: '+col, cause)

        self.col = col

class DuplicateKeyException(ExceptionWithColumn): pass

class NullValueException(ExceptionWithColumn): pass

def check_name(name):

    if re.search(r'\W', name) != None: raise NameException('Name "'+name

        +'" may contain only alphanumeric characters and _')

def run_query(db, query, params=None):

    cur = db.cursor()

    try: cur.execute(query, params)

    except Exception, ex:

        _add_cursor_info(ex, cur)

        raise

    return cur

def row(cur): return iter(lambda: cur.fetchone(), None).next()

def value(cur): return row(cur)[0]

def with_savepoint(db, func):

    savepoint = 'savepoint_'+str(random.randint(0, sys.maxint)) # must be unique

    run_query(db, 'SAVEPOINT '+savepoint)

    try: return_val = func()

    except:

        run_query(db, 'ROLLBACK TO SAVEPOINT '+savepoint)

        raise

    else:

        run_query(db, 'RELEASE SAVEPOINT '+savepoint)

        return return_val

def select(db, table, fields, conds):

    for field in fields: check_name(field)

    for col in conds.keys(): check_name(col)

    def cond(entry):

        col, value = entry

        cond_ = col+' '

        if value == None: cond_ += 'IS'

        else: cond_ += '='

        cond_ += ' %s'

        return cond_

    return run_query(db, 'SELECT '+', '.join(fields)+' FROM '+table+' WHERE '

        +' AND '.join(map(cond, conds.iteritems())), conds.values())

def insert(db, table, row):

    check_name(table)

    cols = row.keys()

    for col in cols: check_name(col)

    return run_query(db, 'INSERT INTO '+table+' ('+', '.join(cols)

        +') VALUES ('+', '.join(['%s']*len(cols))+')', row.values())

def last_insert_id(db): return value(run_query(db, 'SELECT lastval()'))

def try_insert(db, table, row):

    try: return with_savepoint(db, lambda: insert(db, table, row))

    except Exception, ex:

        msg = str(ex)

        match = re.search(r'duplicate key value violates unique constraint "'

            +table+'_(\w+)_index"', msg)

        if match: raise DuplicateKeyException(match.group(1), ex)

        match = re.search(r'null value in column "(\w+)" violates not-null '

            'constraint', msg)

        if match: raise NullValueException(match.group(1), ex)

        raise # no specific exception raised

def insert_or_get(db, table, row, pkey, row_ct_ref=None):

    try: return value(select(db, table, [pkey], row))

    except StopIteration:

        try:

            row_ct = try_insert(db, table, row).rowcount

            if row_ct_ref != None and row_ct >= 0: row_ct_ref[0] += row_ct

            return last_insert_id(db)

        except DuplicateKeyException, ex:

            return value(select(db, table, [pkey], {ex.col: row[ex.col]}))