########################################################################
# $Header: /var/local/cvsroot/4Suite/Ft/Rdf/Drivers/Odbc.py,v 1.7 2005/02/27 04:17:30 jkloth Exp $
"""
A persistent RDF model driver using the pywin32 odbc driver

See http://starship.python.net/crew/mhammond/win32/
and https://sourceforge.net/projects/pywin32/

Copyright 2005 Fourthought, Inc. (USA).
Detailed license and copyright information: http://4suite.org/COPYRIGHT
Project home, documentation, distributions: http://4suite.org/
"""

import warnings
warnings.warn(" This driver is unmaintained; consider using the Postgres"
              " driver instead. PostgreSQL (starting with version 8.0) is"
              " now available for Windows and is a better option than"
              " pywin32's ODBC driver.",
              DeprecationWarning)

import sys

from Ft.Rdf import Model
from Ft.Rdf import Statement


def _connect(keywords):
    # Create an ODBC connection string (probably Windows specific)
    #   data source/username/password
    conn_str = keywords['dsn']
    if keywords.get('user'):
        conn_str += '/' + keywords['user']
        if keywords.get('password'):
            conn_str += '/' + keywords['password']
    return odbc.odbc(conn_str)

from SQL import Commands, SqlAdapter

def InitializeModule():
    """
    Post-import hook to initialize module's runtime variables that are not
    required at import time, but will be needed before the module-level
    functions are called.
    """
    global odbc
    import odbc
    return


def GetDb(dbName, modelName='default'):
    return DbAdapter(dbName, modelName)

def SplitConnectString(connString):
    # A connect string is in the following format (without spaces)
    #  [rdf:] [userName[/password]@] [hostname:[port:]] database
    keywords = {}

    username = None
    passwd = None
    hostname = None
    port = -1
    dbName = None
    #or just
    #dbName

    original = connString

    if connString.startswith('rdf:'):
        connString = connString[4:]

    fields = connString.split('@')
    if len(fields) == 2:
        # There is at least a user specified
        networkString = fields[1]

        # break apart username and possible password
        fields = fields[0].split('/')
        keywords['user'] = fields[0]

        if len(fields) == 2:
            # We have a password as well
            keywords['password'] = fields[1]
        elif len(fields) > 2:
            raise ValueError("Invalid Connect String: %s.  Too many '/'" % original)

    elif len(fields) > 2:
        raise ValueError("Invalid Connect String: %s.  Too many '@'" % original)
    else:
        networkString = fields[0]

    fields = networkString.split(':')
    keywords['dsn'] = fields[-1]
    if len(fields) > 1:
        # At least a hostname is given
        keywords['host'] = fields[0]
        if len(fields) == 3:
            keywords['password'] = fields[1]
        else:
            raise ValueError("Invalid Connect String: %s.  Too many ':'" % original)

    return keywords


def CreateDb(dbiKeywords, model='default'):

    if not isinstance(dbiKeywords, types.DictType):
        dbiKeywords = SplitConnectString(dbiKeywords)

    conn = _connect(dbiKeywords)
    cursor = conn.cursor()
    # Creation commands constructed below
    cursor.execute(CREATE_STATEMENT_TABLE % model)
    cursor.execute(CREATE_BOUND_TABLE % model)
    for (index, (table, columns)) in INDICES.items():
        cols = ','.join(columns)
        table = table % model
        cursor.execute("CREATE INDEX %s_%s_idx ON %s (%s)" %
                       (index, model, table, cols))
    cursor.close()
    conn.commit()
    conn.close()
    return DbAdapter(dbiKeywords)


def DestroyDb(dbiKeywords, model='default'):

    if not isinstance(dbiKeywords, types.DictType):
        dbiKeywords = SplitConnectString(dbiKeywords)

    conn = _connect(dbiKeywords)
    cursor = conn.cursor()
    for (index, (table, columns)) in INDICES.items():
        index = '%s_%s_idx' % (index, model)
        try:
            cursor.execute('DROP INDEX %s' % index)
        except:
            sys.stderr.write("Unable to drop index %s\n" % index);
            pass

    for table_name in ['ftrdf_%s_statement' % model,
                       'ftrdf_%s_bound' % model,
                       ]:
        try:
            cursor.execute('DROP TABLE %s' % table_name)
        except:
            sys.stderr.write("Unable to drop table %s\n" % table_name);
    cursor.close()
    conn.commit()
    conn.close()
    return


def ExistsDb(dbiKeywords, model='default'):

    if not isinstance(dbiKeywords, types.DictType):
        dbiKeywords = SplitConnectString(dbiKeywords)

    conn = _connect(dbiKeywords)
    cursor = conn.cursor()
    exists = 1
    try:
        for table in ('ftrdf_%s_statement', 'ftrdf_%s_bound'):
            table = table % model
            cursor.execute('SELECT COUNT(*) FROM %s' % table)
            if not cursor.fetchall():
                exists = 0
    except:
        exists = 0

    cursor.close()
    conn.rollback()
    conn.close()
    return exists


class DbAdapter(SqlAdapter):
    def __init__(self, dbiKeywords, model='default', db=None):
        SqlAdapter.__init__(self, _commands, _comparisons, model)

        if not isinstance(dbiKeywords, types.DictType):
            dbiKeywords = SplitConnectString(dbiKeywords)

        self._dbi_keywords = dbiKeywords
        self.props = {}
        return

    def begin(self):
        self._conn = _connect(self._dbi_keywords)
        self._db = self._conn.cursor()
        return

    def commit(self):
        self._db.close()
        self._conn.commit()
        self._conn.close()
        self._db = None
        return

    def rollback(self):
        self._db.close()
        self._conn.rollback()
        self._conn.close()
        self._db = None
        return


# Statements for use in the SqlAdapter
class _Cmd:
    def __init__(self, command):
        self._cmd = unicode(command, 'ascii')

    def query(self, db, **args):
        db.execute((self._cmd % args).encode("utf-8"))
        try:
            return db.fetchall()
        except:
            return None

    def execute(self, db, **args):
        db.execute((self._cmd % args).encode("utf-8"))
        return



def BuildSubjectsFromPredAndObjs(predicate, objects, model):
    qstr = "SELECT DISTINCT subject from ftrdf_%s_statement where predicate='%s' AND (" % (model, predicate)
    for o in objects:
        qstr += "object='%s' OR " % o
    qstr = qstr[:-4] + ')'
    return _Cmd(qstr)

def BuildSubjectsFromPredsAndObj(predicates, object, model):
    qstr = "SELECT DISTINCT subject from ftrdf_%s_statement where object='%s' AND (" % (model, object)
    for p in predicates:
        qstr += "predicate='%s' OR " % p
    qstr = qstr[:-4] + ')'
    return _Cmd(qstr)

def BuildObjectsFromSubAndPreds(subject, predicates, model):
    qstr = "SELECT DISTINCT object from ftrdf_%s_statement where subject='%s' AND (" % (model, subject)
    for p in predicates:
        qstr += "predicate='%s' OR " % p
    qstr = qstr[:-4] + ')'
    return _Cmd(qstr)



_commands = {
    Commands.ADD : _Cmd("INSERT INTO ftrdf_%(modelName)s_statement VALUES ('%(subject)s', '%(predicate)s', '%(object)s', '%(statementUri)s', '%(domain)s')"),

    Commands.SIZE : _Cmd("SELECT COUNT(subject) FROM ftrdf_%(modelName)s_statement WHERE domain='%(domain)s'"),
    Commands.SIZE_ALL : _Cmd("SELECT COUNT(subject) FROM ftrdf_%(modelName)s_statement"),

##    # RIL Expressions
    Commands.BIND : _Cmd("INSERT INTO ftrdf_%(modelName)s_bound VALUES "
                         "('%(name)s', '%(object)s', '%(domain)s')"),
    Commands.UNBIND : _Cmd("DELETE FROM ftrdf_%(modelName)s_bound "
                           "WHERE name='%(name)s' "
                           "AND domain='%(domain)s'"),
    Commands.LOOKUP : _Cmd("SELECT object FROM ftrdf_%(modelName)s_bound "
                           "WHERE name='%(name)s' "
                           "AND domain='%(domain)s'"),
    Commands.KEYS : _Cmd("SELECT name FROM ftrdf_%(modelName)s_bound "
                         "WHERE domain='%(domain)s'"),
    Commands.HAS_KEY : _Cmd("SELECT COUNT(object) FROM ftrdf_%(modelName)s_bound "
                            "WHERE name='%(name)s' AND domain='%(domain)s'"),
    Commands.SUBJECT_LIST : _Cmd("SELECT DISTINCT subject FROM ftrdf_%(modelName)s_statement"),
    Commands.BUILD_SUBJS_FROM_PREDS_AND_OBJ : BuildSubjectsFromPredsAndObj,
    Commands.OBJECT_LIST : _Cmd("SELECT DISTINCT object FROM ftrdf_%(modelName)s_statement"),
    Commands.BUILD_OBJS_FROM_SUB_AND_PREDS : BuildObjectsFromSubAndPreds,
    Commands.IS_RESOURCE : _Cmd("SELECT COUNT(subject) FROM ftrdf_%(modelName)s_statement "
                                "WHERE subject='%(subject)s'"),
    Commands.BUILD_SUBJS_FROM_PRED_AND_OBJS : BuildSubjectsFromPredAndObjs,
    }

# For the complex pattern commands: complete, remove, contains and changeAcl
_comparisons = {None : '=',
                Model.NORMAL : '=',
              Model.IGNORE_CASE : '~*',
              Model.REGEX : '~',
              Model.IGNORE_CASE + Model.REGEX : '~*',
              }

for bits in range(32):
    parts = []
    if bits & 16:
        parts.append("subject%(subjectOp)s'%(subject)s'")
    if bits & 8:
        parts.append("predicate%(predicateOp)s'%(predicate)s'")
    if bits & 4:
        parts.append("object%(objectOp)s'%(object)s'")
    if bits & 2:
        parts.append("statementUri%(statementUriOp)s'%(statementUri)s'")
    if bits & 1:
        parts.append("domain%(domainOp)s'%(domain)s'")

    contains = 'SELECT COUNT(subject) FROM ftrdf_%(modelName)s_statement'
    complete = 'SELECT * FROM ftrdf_%(modelName)s_statement '
    remove = 'DELETE FROM ftrdf_%(modelName)s_statement '

    if parts:
        where = ' AND '.join(parts)
        contains =  '%s WHERE %s' % (contains, where)
        complete = '%s WHERE %s' % (complete, where)
        remove = '%s WHERE %s' % (remove, where)

    key = (bits & 16 > 0,
           bits & 8 > 0,
           bits & 4 > 0,
           bits & 2 > 0,
           bits & 1)

    _commands[(Commands.CONTAINS,) + key] = _Cmd(contains)
    _commands[(Commands.COMPLETE,) + key] = _Cmd(complete)
    _commands[(Commands.REMOVE,) + key] = _Cmd(remove)


# SQL commands to create necessary tables

CREATE_STATEMENT_TABLE = """
CREATE TABLE ftrdf_%s_statement (
    subject       varchar,
    predicate     varchar,
    object        varchar,
    statementUri  varchar,
    domain     varchar)
"""


CREATE_BOUND_TABLE = """
CREATE TABLE ftrdf_%s_bound (
    name      varchar,
    object    varchar,
    domain varchar)
"""

INDICES = {
    'sp' : ('ftrdf_%s_statement', ['subject', 'predicate']),
    'po' : ('ftrdf_%s_statement', ['predicate', 'object']),
    'source' : ('ftrdf_%s_statement', ['domain']),
    'binding' : ('ftrdf_%s_bound', ['name']),
    }
