########################################################################
#
# File Name:            Postgres.py
#
# Documentation:        http://docs.4suite.org/4RDF/Drivers/Postgres.py.html
#
"""
Interface to Postgres for 4RDF
WWW: http://4suite.org/RDF        e-mail: support@4suite.org

Copyright (c) 2000-2001 Fourthought, Inc., USA.  All Rights Reserved.
See  http://4suite.org/COPYRIGHT  for license and copyright information
"""

import sys, string, cPickle, cStringIO
from Ft.Lib.PgDatabase import EscapeQuotes
from Ft.Rdf import Statement, Model
import _pg

from SQL import Commands, SqlAdapter

def GetDb(dbName):
    return DbAdapter(dbName)


def CreateDb(dbName):
    pg_db_name = "ft__" + dbName
    tmpl_db = _pg.connect('template1')
    res = tmpl_db.query("select datname from pg_database where datname = '%s'" % pg_db_name).getresult()
    if not res:
        tmpl_db.query('CREATE DATABASE ' + pg_db_name)
    tmpl_db.close()

    # Creation commands constructed below
    pg_db = _pg.connect(pg_db_name)
    pg_db.query('BEGIN')
    pg_db.query(CREATE_STATEMENT_TABLE)
    pg_db.query(CREATE_BOUND_TABLE)
    for (index, (table, columns)) in INDICES.items():
        cols = string.join(columns, ',')
        query = 'CREATE INDEX %s_idx ON %s (%s)' % (index, table, cols)
        pg_db.query(query)
    pg_db.query('COMMIT')
    return DbAdapter(dbName)


def DestroyDb(dbName):
    pg_db_name = "ft__" + dbName
    pg_db = _pg.connect(pg_db_name)


    for (index, (table, columns)) in INDICES.items():
        try:
            pg_db.query('DROP INDEX %s' % index)
        except _pg.error:
            pass


    for tn in ['ftrdf_statement',
               'ftrdf_bound',
               ]:
        try:
            pg_db.query('DROP TABLE %s' % tn)
        except _pg.error:
            sys.stderr.write("Unable to drop table %s\n" % tn);

    return


def ExistsDb(dbName):
    pg_db_name = "ft__" + dbName
    db = _pg.connect('template1')
    rt = db.query("SELECT datname FROM pg_database WHERE datname='%s'" % pg_db_name)
    if rt and rt.ntuples() > 0:
        pg_db_name = "ft__" + dbName
        pg_db = _pg.connect(pg_db_name)
        for (index, (table, columns)) in INDICES.items():
            if pg_db.query("select indexname from pg_indexes where indexname = '%s'" % index).getresult():
                return 1
        for tn in ['ftrdf_statement',
                   'ftrdf_bound',
                   ]:
            if pg_db.query("select tablename from pg_tables where tablename = '%s'" % tn).getresult():
                return 1
    return 0

class DbAdapter(SqlAdapter):
    def __init__(self, name):
        SqlAdapter.__init__(self, _commands, _comparisons)
        self._name = 'ft__%s' % name
        return

    def begin(self):
        self._db = _pg.connect(self._name)
        self._db.query('BEGIN')
        return

    def commit(self):
        self._db.query('COMMIT')
        self._db.close()
        self._db = None
        return

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


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

    def query(self, db, **args):
        if sys.hexversion >= 0x2000000:
            result = db.query((self._cmd % args).encode("utf-8"))
        else:
            result = db.query((self._cmd % args))
        try:
            return result and result.getresult()
        except AttributeError:
            pass
        return None

    def execute(self, db, **args):
        if sys.hexversion >= 0x2000000:
            return db.query((self._cmd % args).encode("utf-8"))
        else:
            return db.query((self._cmd % args))
        return



_commands = {
    Commands.ADD : _Cmd("INSERT INTO ftrdf_statement VALUES ('%(subject)s', '%(predicate)s', '%(object)s', '%(statementUri)s', '%(sourceUri)s')"),

    Commands.SIZE : _Cmd("SELECT COUNT(subject) FROM ftrdf_statement WHERE sourceUri='%(sourceUri)s'"),
    Commands.SIZE_ALL : _Cmd("SELECT COUNT(subject) FROM ftrdf_statement"),

##    # RIL Expressions
    Commands.BIND : _Cmd("INSERT INTO ftrdf_bound VALUES ('%(name)s', '%(object)s', '%(sourceUri)s')"),
    Commands.UNBIND : _Cmd("DELETE FROM ftrdf_bound WHERE name='%(name)s' AND sourceUri='%(sourceUri)s'"),
    Commands.LOOKUP : _Cmd("SELECT object FROM ftrdf_bound WHERE name='%(name)s' AND sourceUri='%(sourceUri)s'"),
    Commands.KEYS : _Cmd("SELECT name FROM ftrdf_bound WHERE sourceUri='%(sourceUri)s'"),
    Commands.HAS_KEY : _Cmd("SELECT COUNT(object) FROM ftrdf_bound WHERE name='%(name)s' AND sourceUri='%(sourceUri)s'"),
    }

# For the complex pattern commands: complete, remove, contains and changeAcl
_comparisons = {None : '=',
              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("sourceUri%(sourceUriOp)s'%(sourceUri)s'")

    contains = 'SELECT COUNT(subject) FROM ftrdf_statement'
    complete = 'SELECT subject, predicate, object, statementUri, sourceUri FROM ftrdf_statement '
    remove = 'DELETE FROM ftrdf_statement '

    if parts:
        where = string.join(parts, ' AND ')
        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)


# PostgreSQL commands to create necessary tables

CREATE_STATEMENT_TABLE = """
CREATE TABLE ftrdf_statement (
    subject       text,
    predicate     text,
    object        text,
    statementUri  text,
    sourceUri     text)
"""


CREATE_BOUND_TABLE = """
CREATE TABLE ftrdf_bound (
    name      text,
    object    varchar,
    sourceUri text)
"""

INDICES = {
   'full' : ('ftrdf_statement', ['subject', 'predicate', 'object',
                                 'statementUri', 'sourceUri']),
#   'subject' : ('ftrdf_statement', ['subject']),
#   'predicate' : ('ftrdf_statement', ['predicate']),
#   'object' : ('ftrdf_statement', ['object']),
#   'statement' : ('ftrdf_statement', ['statementUri']),
#   'source' : ('ftrdf_statement', ['sourceUri']),
    'binding' : ('ftrdf_bound', ['name']),
    }
