# -*- coding: ISO-8859-1 -*-
########################################################################
# $Header: /var/local/cvsroot/4Suite/Ft/Rdf/Drivers/Oracle.py,v 1.14 2005/03/29 00:30:48 mbrown Exp $
"""
A persistent RDF model driver using Oracle

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

import re, os
try:
    from DCOracle2 import DCOracle2
except:
    pass

from Ft.Rdf import RDF_MS_BASE, OBJECT_TYPE_UNKNOWN, OBJECT_TYPE_LITERAL, OBJECT_TYPE_RESOURCE
from Ft.Rdf.Drivers import DataBaseExceptions, PROPERTIES


NAME_PATTERN = "[a-zA-Z_][a-zA-Z0-9\.\-_]*"
ORACLE_CONNECT_STRING = re.compile(r"(%s)/(%s)@(%s)"%(NAME_PATTERN, NAME_PATTERN, NAME_PATTERN))

CREATE_USER = """\
CREATE USER %(model)s IDENTIFIED BY %(model)s DEFAULT TABLESPACE USERS;
GRANT CONNECT, RESOURCE to %(model)s;
"""

CREATE_DDL = """
CREATE TABLE urihead (
ID  number primary key,
--PrefixKey   varchar(50),
value varchar(2000) not null
);

CREATE TABLE rdfresource (
id number primary key,
uriheadid  number references urihead(id) not null,
tail    varchar(2000) not null
);

CREATE UNIQUE INDEX rdfresourceidx on rdfresource(uriheadid, tail);

CREATE INDEX uriheadvalue ON urihead (value);

CREATE TABLE statement(
id number primary key,
subjectid number references rdfresource(ID) not null,
predicateid number references rdfresource(ID) not null,
objectid number not null,
objecttypecode number not null,
domain number references rdfresource(ID) not null
);

CREATE TABLE stringliteral (
id number primary key,
value varchar(2000)
);

CREATE TABLE numericliteral (
ID number primary key,
value number
);

CREATE SEQUENCE objectseq;
CREATE SEQUENCE statmentseq;
CREATE SEQUENCE uriheadseq;


CREATE TABLE aclident (
id number primary key,
name varchar(2000) not null
);

CREATE TABLE aclassoc (
aclidentid number references aclident(id),
rdfresourceid number references rdfresource(id)
);

CREATE INDEX aclidentname ON aclident (Name);
"""

DESTROY_DDL = """\
drop table ACLASSOC;
drop table ACLIDENT;
drop table NUMERICLITERAL;
drop table RDFRESOURCE;
drop table STATEMENT;
drop table STRINGLITERAL;
drop table URIHEAD;
drop sequence OBJECTSEQ;
drop sequence STATMENTSEQ;
drop sequence URIHEADSEQ;
"""

CREATE_FUNCS = """\
CREATE OR REPLACE FUNCTION rlookup_func ( funcid in number)
    -- Params: resource ID
    RETURN varchar2
    IS
      value_tail varchar2(4000);
    BEGIN
    SELECT decode(funcid,null,null,UH.value || RES.tail)
        INTO value_tail
    FROM urihead UH, rdfresource RES
    WHERE RES.id = funcid AND RES.uriheadid = UH.id;
    return(value_tail);
    END;

CREATE OR REPLACE FUNCTION getobj_func (objecttype in number, objectid in number)
RETURN varchar2
IS
    retval varchar2(4000);
    BEGIN
        select distinct decode(objecttype||R.id,0||objectid,rlookup_func(objectid),decode(objecttype||STR.id,1||objectid,STR.value)) into retval
        FROM stringliteral STR, rdfresource R
        WHERE STR.id = objectid OR R.id = objectid;
    return(retval);
    END;
"""

UUID_PATTERN = re.compile(u'(?P<head>urn:uuid:)(?P<tail>.+)')
HTTPPLUS_PATTERN = re.compile(u'(?P<head>(http|ftp)://.+[/#])(?P<tail>.+)')
MAILTO_PATTERN = re.compile(u'(?P<head>mailto:)(?P<tail>.+)')


def MapObjectType(ot):
    #FIXME: This mapping should really be stored in the DBMS, to minimize
    #breakage across 4Suite versions

    #return as is, for now
    return ot


def SplitUri(uri):
    #FIXME: Soon, hopefully, we can expect unicode
    if not isinstance(uri, unicode):
        uri = unicode(str(uri), 'utf-8')
    #Note: this function does not have to recognize URI syntax/semantics
    #Since the results are just stitched back together
    #But it would be friendly if it did
    match = HTTPPLUS_PATTERN.match(uri)
    if not match:
        match = UUID_PATTERN.match(uri)
    if not match:
        match = MAILTO_PATTERN.match(uri)
    if not match:
        split = len(uri)/2
        return (uri[:split], uri[split:])
    return (match.group('head'), match.group('tail'))


def ConnectStringToDict(connStr):
    fields = connStr.split()
    dict = {}
    for field in fields:
        k, v = field.split('=')
        dict[k] = v
    return dict

def EscapeQuotes(qstr):
    if not qstr:
        return ""
    else:
        return str(qstr.replace("'", "''"))

def GetDb(connStr, modelName='rdfdefault'):
    return DbAdapter(connStr, modelName)

def ConnectDb(connStr, modelName='rdfdefault'):
    match = ORACLE_CONNECT_STRING.match(connStr)
    if not match:
        raise DataBaseExceptions.InvalidName
    user = match.group(1)
    password = match.group(2)
    dsn = match.group(3)
    return DCOracle2.connect(connStr)

    # This example connects via ODBC (mx.ODBC) on nt, and via DCOracle2 otherwise
    if os.name=='nt':
        from mx.ODBC import Windows
        return Windows.connect(dsn=dsn,user=user,password=password)
    else:
        from DCOracle2 import DCOracle2
        return DCOracle2.connect(dsn=dsn,user=user,password=password)

def CreateDb(connStr, modelName='rdfdefault'):
    match = ORACLE_CONNECT_STRING.match(connStr)
    return DbAdapter(connStr)
    if not match:
        raise DataBaseExceptions.InvalidName
    user = match.group(1)
    password = match.group(2)
    dsn = match.group(3)
    print connStr, user, password, dsn
    #db = DCOracle2.connect("system/manager@GPSTOFU")
    #cursor = db.cursor()
    #cursor.execute("SELECT USERNAME FROM DBA_USERS WHERE USERNAME='%s'"%(user,))
    #rt1 = cursor.fetchall()
    #if  len(rt1) == 0:
    #    cursor = db.cursor()
    #    print CREATE_USER%{'model':modelName}
    #    cursor.execute(CREATE_USER%{'model':modelName})
    #    db.commit()
    db = ConnectDb(connStr)
    cursor = db.cursor()
    cursor.execute(CREATE_DDL)
    db.commit()
    return DbAdapter(connStr)


def ExistsDb(connStr, modelName='rdfdefault'):
    match = ORACLE_CONNECT_STRING.match(connStr)
    if not match:
        raise DataBaseExceptions.InvalidName
    user = match.group(1)
    password = match.group(2)
    dsn = match.group(3)

    db = ConnectDb("system/manager@" + dsn)
    cursor = db.cursor()
    cursor.execute("SELECT USERNAME FROM DBA_USERS WHERE USERNAME='%s'",(user,))
    rt1 = cursor.fetchall()
    if  len(rt1) == 0:
        return 0
    db = ConnectDb(connStr)
    cursor = db.cursor()
    cursor.execute("SELECT * FROM DUAL")
    rt1 = cursor.fetchall()
    return len(rt1) > 0


def DestroyDb(connStr, modelName='rdfdefault'):
    db = ConnectDb(connStr)
    try:
        db.execute("DESTROY_DDL")
    except:
        pass
    try:
        db.execute("DESTROY_DDL")
    except:
       pass
    db.commit()

class DbAdapter:
    def __init__(self, connString, modelName='rdfdefault', db=None):
        #self.params = ConnectStringToDict(connString)
        self.connString = connString
        self._model = str(modelName)
        self._urihT = str(modelName + 'UriHead')
        self._stmtT = str(modelName + 'Statement')
        self._resT = str(modelName + 'RdfResource')
        self._strT = str(modelName + 'StringLiteral')
        self._urihS = str(modelName + 'UriHeadSeq')
        self._stmtS = str(modelName + 'StatementSeq')
        self._objS = str(modelName + 'ObjectSeq')
        self.props = {PROPERTIES.OBJECT_TYPE_SUPPORTED: 1}
        return

    def begin(self):
        self._db = DCOracle2.connect(self.connString)
        return

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

    def rollback(self):
        #self._db.rollback()    #Not needed: is the default
        self._db.close()
        self._db = None
        return

    ####### Legacy API #######
    def add(self, statements):
        if not self._db:
            raise DataBaseExceptions.NoTransaction
        cur = self._db.cursor()
        for (subj, pred, obj, suri, domain, obj_type) in statements:
            if obj_type == OBJECT_TYPE_UNKNOWN: obj_type = OBJECT_TYPE_LITERAL
            subj = SplitUri(subj)
            subj_res = self._addResource(cur, subj[0], subj[1])
            pred = SplitUri(pred)
            pred_res = self._addResource(cur, pred[0], pred[1])
            #FIXME: for now, every object is a string literal
            if suri:
                suri = SplitUri(suri)
            if domain:
                domain = SplitUri(domain)
                domain_res = self._addResource(cur, domain[0], domain[1])
            else:
                domain_res = 1
            if obj_type == OBJECT_TYPE_RESOURCE:
                obj = SplitUri(obj)
                obj_res = self._addResource(cur, obj[0], obj[1])
                #print ("INSERT into STATEMENT VALUES (STATMENTSEQ.nextval, %i, %i, %i, %i, %i")% (subj_res, pred_res, obj_res, obj_type, domain_res and 1)
                cur.execute(
                ("INSERT into STATEMENT VALUES (STATMENTSEQ.nextval, %i, %i, %i, %i, %i)")% (subj_res, pred_res, obj_res, obj_type, domain_res and 1))
            else:
                cur.execute(("INSERT into STRINGLITERAL VALUES (OBJECTSEQ.nextval, '%s')")%(EscapeQuotes(obj.encode('utf-8'),)))
                cur.execute(("INSERT into STATEMENT VALUES (STATMENTSEQ.nextval, %i, %i, OBJECTSEQ.currval, %i, %i)")%(subj_res, pred_res, obj_type, domain_res and 1))
        return


    def _addResource(self, cur, head, tail):
        #NOTE: A stored proc version of this would be so much more efficient
        cur.execute("SELECT id from URIHEAD WHERE value = '%s'"% (EscapeQuotes(head.encode('utf-8')),))
        hres = cur.fetchone()
        tres = None
        if hres:
            #Already have the URI head
            cur.execute("SELECT id from RDFRESOURCE WHERE uriheadid = %i AND tail = '%s'"%
                        (hres[0], EscapeQuotes(tail.encode('utf-8'))))
            tres = cur.fetchone()
            if not tres:
                #Resource tail not yet added
                cur.execute("INSERT into RDFRESOURCE VALUES (OBJECTSEQ.nextval, %i, '%s')"%(hres[0], EscapeQuotes(tail.encode('utf-8'))))
                cur.execute("SELECT id from RDFRESOURCE WHERE uriheadid = %i AND tail = '%s'"%
                            (hres[0], EscapeQuotes(tail.encode('utf-8'))))
                tres = cur.fetchone()
        else:
            cur.execute(
                "INSERT into URIHEAD VALUES (URIHEADSEQ.nextval, '%s')"% (EscapeQuotes(head.encode('utf-8'))))
            cur.execute(
                "INSERT into RDFRESOURCE VALUES (OBJECTSEQ.nextval, URIHEADSEQ.currval, '%s')"%
                (EscapeQuotes(tail.encode('utf-8'))))
            cur.execute("SELECT URIHEADSEQ.currval FROM DUAL")
            curval = cur.fetchone()[0]
            cur.execute("SELECT id from RDFRESOURCE WHERE uriheadid = %i AND tail = '%s'"%
                        (curval,EscapeQuotes(tail.encode('utf-8'))))
            tres = cur.fetchone()
        return tres[0]

    def _stmtSelect(self, subject, predicate, object, statementUri, domain,
                    flags, idsOnly=0):
        if not self._db:
            raise DataBaseExceptions.NoTransaction
        cur = self._db.cursor()
        #objsplit = ""
        #if object:
        #    objsplit = SplitUri(object)
        #if subject:
        #    subject = SplitUri(subject)
        #if predicate:
        #    predicate = SplitUri(predicate)
        #if statementUri:
        #    statementUri = SplitUri(statementUri)
        #if domain:
        #    domain = SplitUri(domain)
        if idsOnly:
            select = "SELECT S.id"
        else:
            select = """\
    SELECT rlookup_func(S.SubjectID),
           rlookup_func(S.PredicateID),
           getobj_func(S.objectTypeCode, S.ObjectID),
           NULL,
           rlookup_func(S.Domain),
        S.objectTypeCode"""
        from_ = " FROM STATEMENT S, "
        where = ""
        where_params = []
        if subject:
            from_ += "URIHEAD SU_S, RDFRESOURCE R_S, "
            if where:
                where += "AND "
            where += "S.subjectID = R_S.ID AND R_S.UriHeadID = SU_S.ID AND SU_S.value || R_S.tail = '%s' "%(EscapeQuotes(subject.encode('utf-8')))
        if predicate:
            from_ += "URIHEAD SU_P, RDFRESOURCE  R_P, "
            if where:
                where += "AND "
            where += "S.predicateID = R_P.ID AND R_P.uriHeadID = SU_P.ID AND SU_P.value || R_P.tail = '%s' "%(EscapeQuotes(predicate.encode('utf-8')))
        if domain:
            from_ += "URIHEAD SU_D, RDFRESOURCE R_D, "
            if where:
                where += "AND "
            where += "S.subjectID = R_D.ID AND R_D.uriHeadID = SU_D.ID AND SU_D.value || R_D.tail = '%s' "%(EscapeQuotes(domain.encode('utf-8')))
        if object:
            from1 = from_ + "URIHEAD SU_O, RDFRESOURCE R_O, "
            from2 = from_ + "STRINGLITERAL STR, "
            if where:
                where += "AND "
            where2 = where + "S.objectTypeCode = 1 AND S.objectID = STR.ID AND STR.value = '%s' "%(EscapeQuotes(object.encode('utf-8')))
            where += "S.objectTypeCode = 0 AND S.objectID = R_O.ID AND R_O.UriHeadID = SU_O.ID AND SU_O.value || R_O.tail = '%s' "%(EscapeQuotes(object.encode('utf-8')))
            query = select + from1[:-2]+ (" WHERE " + where)
            #print query
            cur.execute(query)
            #print cur.fetchall(); cur.execute(query)
            result = cur.fetchall()
            #print query; print result
            query = select + from2[:-2] + (" WHERE " + where2)
            cur.execute(query)
            #print cur.fetchall(); cur.execute(query)
            result += cur.fetchall()
            #print query; print obj_where_params1; print obj_where_params2; print result
            return result
        query = select + from_[:-2] + (where and " WHERE " + where or "")
        #print query
        cur.execute(query)
        #print cur.fetchall(); cur.execute(query)
        #print query, qparams, cur.fetchall(); cur.execute(query, qparams)
        return cur.fetchall()

    def complete(self, subject, predicate, object, statementUri, domain,
                 flags):
        return self._stmtSelect(subject, predicate, object, statementUri,
                                domain, flags, 0)

    def contains(self, subject, predicate, object, statementUri, domain, flags):
        return len(self.complete(subject, predicate, object, statementUri, domain, flags)) > 0

    def remove(self, statements):
        for s in statements:
            self.removePattern(s[0], s[1], s[2], s[3], s[4], {})
        return

    def removePattern(self, subject, predicate, object, statementUri, domain,
                      flags):
        ids = self._stmtSelect(subject, predicate, object, statementUri,
                               domain, flags, 1)
        cur = self._db.cursor()
        if ids:
            for id_row in ids:
                #print ("DELETE FROM %s WHERE id = %%i"%self._stmtT, id_row[0])
                cur.execute("DELETE FROM STATEMENT WHERE id = %i"%(id_row[0]))
        return

    ####### Versa API #######
    def subjectsFromPredsAndObj(self, predicates, object, scope=None):
        #FIXME: support scope
        from Ft.Rdf.Parsers.Versa import DataTypes
        #FIXME: could overflow query length limits
        if not self._db:
            raise DataBaseExceptions.NoTransaction
        cur = self._db.cursor()
        select = "SELECT DISTINCT rlookup_func(S.SubjectID)"
        from_ = " FROM STATEMENT S, "
        where = ""
        where_params = []
        if object is not None:
            obj_type_code = 1
            if isinstance(object, DataTypes.Resource):
                obj_type_code = 0
            if not isinstance(object, unicode):
                object = unicode(str(object), 'utf-8')
            if where:
                where += "AND "
            if obj_type_code == 0:
                from_ += "URIHEAD  SU_O, RDFRESOURCE R_O, "
                where += "S.objectID = R_O.ID AND R_O.UriHeadID = SU_O.ID AND SU_O.value || R_O.tail = '%s' "%(EscapeQuotes(object.encode('utf-8')))
            else:
                from_ += "STRINGLITERAL STR, "
                where += "S.objectID = STR.ID AND STR.value = '%s' "%(EscapeQuotes(object.encode('utf-8')))
            for i in range(len(predicates)):
                p = predicates[i]
                if not isinstance(p, unicode):
                    p = unicode(str(p), 'utf-8')
                from_ += "URIHEAD  SU_P%i, RDFRESOURCE R_P%i, "%(i, i)
                if where:
                    where += "AND "
                where += "S.predicateID = R_P%i.ID AND R_P%i.uriHeadID = SU_P%i.ID AND SU_P%i.value || R_P%i.tail = '%s' "%(i, i, i, i, i, EscapeQuotes(p.encode('utf-8')))
                query = select + from_[:-2] + (where and " WHERE " + where or "")
                #print query, qparams
                cur.execute(query)
                return map(lambda x: x[0], cur.fetchall())

    def subjectsFromPredAndObjs(self, predicate, objects, scope=None):
        #FIXME: support scope
        from Ft.Rdf.Parsers.Versa import DataTypes
        #FIXME: could overflow query length limits
        if not self._db:
            raise DataBaseExceptions.NoTransaction
        cur = self._db.cursor()
        select = "SELECT DISTINCT rlookup_func(S.SubjectID)"
        from_ = " FROM STATEMENT  S, "
        where = ""
        where_params = []
        if predicate is not None:
            if not isinstance(predicate, unicode):
                predicate = unicode(str(predicate), 'utf-8')
            from_ += "URIHEAD  SU_P, RDFRESOURCE  R_P, "
            if where:
                where += "AND "
            where += "S.predicateID = R_P.ID AND R_P.uriHeadID = SU_P.ID AND SU_P.value || R_P.tail = '%s' "%(EscapeQuotes(predicate.encode('utf-8')))
        #print objects
        for i in range(len(objects)):
            o = objects[i]
            obj_type_code = 1
            if isinstance(o, DataTypes.Resource):
                obj_type_code = 0
            if not isinstance(o, unicode):
                o = unicode(str(o), 'utf-8')
            if where:
                where += "AND "
            if obj_type_code == 0:
                from_ += "STRINGLITERAL STR%i, URIHEAD SU_O%i,  "%(i, i)
                where += "S.objectID = R_O%i.ID AND R_O%i.UriHeadID = SU_O%i.ID AND SU_O%i.value || R_O%i.tail = '%s' "%(i, i, i, i, i, EscapeQuotes(o.encode('utf-8')))
            else:
                from_ += "RDFRESOURCE R_O%i, "%(i)
                where += "S.objectID = STR%i.ID AND STR%i.value = '%s' "%(i, i, EscapeQuotes(o.encode('utf-8')))
            #where += "S.objectID = STR%i.ID AND STR%i.value = '%s' AND S.objectTypeCode = 1 AND S1.SubjectID = R_O%i.ID AND R_O%i.UriHeadID = SU_O%i.ID AND SU_O%i.value = '%s' AND R_O%i.tail = '%s' AND S1.objectTypeCode = 0 "%(i, i, i, i, i, i, i)
            #where += "S.objectID = STR%i.ID AND STR%i.value = '%s' "%(i, i)
            #where_params.extend([o.encode('utf-8')])
        query = select + from_[:-2] + (where and " WHERE " + where or "")
        #print query, qparams
        cur.execute(query)
        return map(lambda x: x[0], cur.fetchall())

    def objectsFromSubAndPreds(self, subject, predicates, scope=None):
        #FIXME: support scope
        #FIXME: could overflow query length limits
        if not self._db:
            raise DataBaseExceptions.NoTransaction
        cur = self._db.cursor()
        select = "SELECT DISTINCT getobj_func(S.objectTypeCode, S.ObjectID), S.objectTypeCode"
        from_ = " FROM STATEMENT S, "
        where = ""
        where_params = []
        if subject is not None:
            from_ += "URIHEAD  SU_S, RDFRESOURCE R_S, "
            if where:
                where += "AND "
            where += "S.subjectID = R_S.ID AND R_S.uriHeadID = SU_S.ID AND SU_S.value || R_S.tail = '%s' "%(EscapeQuotes(subject.encode('utf-8')))
        for i in range(len(predicates)):
            p = predicates[i]
            from_ += "URIHEAD  SU_P%i, RDFRESOURCE  R_P%i, "%(i, i)
            if where:
                where += "AND "
            where += "S.predicateID = R_P%i.ID AND R_P%i.uriHeadID = SU_P%i.ID AND SU_P%i.value || R_P%i.tail = '%s' "%(i, i, i, i, i, EscapeQuotes(p.encode('utf-8')))
        query = select + from_[:-2] + (where and " WHERE " + where or "")
        #print query, qparams
        cur.execute(query)
        #print cur.fetchall(); cur.execute(query, qparams)
        return map(lambda x: (x[0], x[1]), cur.fetchall())

    def isResource(self, res):
        if not self._db:
            raise DataBaseExceptions.NoTransaction
        cur = self._db.cursor()
        query = "SELECT COUNT(R.id) FROM RDFRESOURCE R, URIHEAD  SU WHERE R.uriHeadID = SU.id AND SU.value || R.tail = '%s'"%(EscapeQuotes(res.encode('utf-8')))
        cur.execute(query)
        return cur.fetchone()[0] > 0

    def resources(self):
        if not self._db:
            raise DataBaseExceptions.NoTransaction
        cur = self._db.cursor()
        query = "SELECT SU.value, R.tail FROM RDFRESOURCE  R, URIHEAD  SU WHERE R.uriHeadID = SU.id"
        cur.execute(query)
        return map(lambda x: x[0]+x[1], cur.fetchall())


#############################################################################
# Documentation, examples and other miscellany
#############################################################################

######### SQL EXAMPLES ##########
######### ADD TRIPLE   ##########
## Add s='spam', p='eggs', o='ni' domain='monty'
## Just fudge and assume URIs are split first char, rest

##INSERT into UriHead VALUES (nextval('UriHeadSeq'), 's')
##INSERT into RdfResource VALUES (nextval('ObjectSeq'), currval('UriHeadSeq'), 'pam')
##INSERT into UriHead VALUES (nextval('UriHeadSeq'), 'e')
##INSERT into RdfResource VALUES (nextval('ObjectSeq'), currval('UriHeadSeq'), 'ggs')
##INSERT into UriHead VALUES (nextval('UriHeadSeq'), 'm')
##INSERT into RdfResource VALUES (nextval('ObjectSeq'), currval('UriHeadSeq'), 'onty')
##INSERT into StringLiteral VALUES (nextval('ObjectSeq'), 'ni')
##INSERT into Statement VALUES (nextval('StatementSeq'), currval('ObjectSeq')-3, currval('ObjectSeq')-2, currval('ObjectSeq')-1, 1, currval('ObjectSeq'))

######### COMPLETE TRIPLE   ##########
## get just the matching subjects
##Note that in some cases, it would be more efficient not to do this join
##Mostly when one is using the same criteria over and over for query
##In which case, it's faster to just look up the corresponding resource
##Sequence numbers and plug them right in

##SELECT UriHead.ID, UriHead.value,
##       RdfResource.ID, RdfResource.UriHeadID, RdfResource.tail,
##       Statement.SubjectID, Statement.PredicateID,
##       Statement.ObjectID, Statement.Domain
##FROM UriHead, RdfResource, Statement
##WHERE Statement.SubjectID = RdfResource.ID
##AND RdfResource.UriHeadID = UriHead.ID
##AND UriHead.value = 'http://foo.com/'
##AND RdfResource.tail = 'bar'

##Here is a code snippet to select everything that matches certain subject and predicates(untested)

##SELECT S.SUBJECTID, RS.NAME, RS.SPLITURIID,
##       S.PREDICATEID, RP.NAME, RP.SPLITURIID,
##       S.OBJECTID, S.OBJECTTYPECODE
##FROM STATEMENT S, RESOURCE RS, RESOURCE RP
##WHERE S.SUBJECTID = ? AND S.PREDICATEID = ? AND S.SUBJECT = RS.ID
##AND S.PREDICATE = RP.ID




##From: Uche Ogbuji
##Date: 30 Jan 2002 20:31:44 -0700

##OK.  I've heard nothing but praise for this, so I'm giving it a try.

##First a coupla links

##http://www.rons.net.cn/english/FSM/fog
##http://initd.org/Software/psycopg

##Here's what I'm doing

##First of all requires mxDateTime:

##Grab  egenix-mx-base-2.0.3.zip from

##http://www.egenix.com/files/python/eGenix-mx-Extensions.html#Download-mxBASE

##(tgz seems to be corrupted)

##Unpack to ~/src, cd ~/src and ./setup.py install

##Then grab Psyco (psycopg-1.0.1.tar.gz)

##Unpack to ~/src and cd ~/src/psycopg-1.0.1

##$ ./configure
##--with-mxdatetime-includes=../egenix-mx-base-2.0.3/mx/DateTime/mxDateTime/ --with-postgres-includes=/usr/include/pgsql/
##$ make
##$ make install

##Try her out:

##$ createdb psy


##Then in interactive Python:

##>>> import psycopg
##>>> connect = "dbname=psy"
##>>> conn = psycopg.connect(connect)
##>>> curs.execute("CREATE TABLE stmt (s text, p text, o text)")
##>>> conn.commit()
##>>> curs.execute("INSERT INTO stmt VALUES ('urn:faux:uche',
##'urn:faux:fname', 'Uche Ogbuji')")

###This part is cool:

##>>> curs.executemany("INSERT INTO stmt VALUES (%s, %s, %s)",
##(('urn:faux:jeremy', 'urn:faux:fname', 'Jeremy Kloth'),
##('urn:faux:mike', 'urn:faux:fname', 'Mike Olson')))

##>>> conn.commit()
##>>> curs.execute("SELECT * FROM stmt")
##>>> row = curs.fetchone()
##>>> print repr(row)
##('urn:faux:uche', 'urn:faux:fname', 'Uche Ogbuji')
##>>> rows = curs.fetchall()
##>>> print [ repr(r) for r in rows ]
##["('urn:faux:jeremy', 'urn:faux:fname', 'Jeremy Kloth')",
##"('urn:faux:mike', 'urn:faux:fname', 'Mike Olson')"]
##>>>
##However, we might have a problem:

##>>> curs.execute((u"INSERT INTO stmt VALUES ('%s', '%s', '%s')"%(u'a',
##unicode('ê', "iso8859-1"), u'ê')).encode("utf-8"))
##>>> conn.commit()
##>>> rows = curs.fetchall()
##>>> curs.execute("SELECT * FROM stmt")
##>>> rows = curs.fetchall()
##>>> print [ repr(r) for r in rows ]
##["('urn:faux:uche', 'urn:faux:fname', 'Uche Ogbuji')",
##"('urn:faux:jeremy', 'urn:faux:fname', 'Jeremy Kloth')",
##"('urn:faux:mike', 'urn:faux:fname', 'Mike Olson')", "('a',
##'\\xc3\\xaa', '\\xc3\\xaa')"]

##I think we can get around this, though.


##Also see http://www.python.org/topics/database/DatabaseAPI-2.0.html


QUICK_TEST = """\
RDF_FILE = 'w3c1.rdf'
from Ft.Rdf.Drivers import Psyco
#Psyco.CreateDb('dbname=ftrdf_demo', 'rdfdefault')
from Ft.Rdf import Util
model, db = Util.DeserializeFromUri(RDF_FILE, driver=Psyco, dbName='dbname=ftrdf_demo', create=1)
db.begin()
model.complete(None, None, None)
model.complete("http://www.dlib.org", None, None)
model.complete(None, "http://purl.org/metadata/dublin_core#Title", None)
model.complete(None, None, "World Wide Web Home Page")
model.complete(None, None, "http://www.w3.org/1999/02/22-rdf-syntax-ns#Bag")
model.complete("http://www.dlib.org", "http://purl.org/metadata/dublin_core#Title", None)
model.complete("http://www.dlib.org", "http://purl.org/metadata/dublin_core#Title", "D-Lib Program - Research in Digital Libraries")
model.complete(None, "http://purl.org/metadata/dublin_core#Title", "D-Lib Program - Research in Digital Libraries")
model.complete("http://www.dlib.org", None, "D-Lib Program - Research in Digital Libraries")
model.complete("http://www.dlib.org", "http://purl.org/metadata/dublin_core#Title", "foo")

#Versa
NSMAP = {"rdf": "http://www.w3.org/1999/02/22-rdf-syntax-ns#", "dc":"http://purl.org/metadata/dublin_core#"}
r = Util.VersaQuery("all()", model, nsMap=NSMAP)
print r
print Util.VersaDataToXml(r)
r = Util.VersaQuery("all() - dc:Title -> *", model, nsMap=NSMAP)
print r
print Util.VersaDataToXml(r)
r = Util.VersaQuery("all() - rdf:type -> *", model, nsMap=NSMAP)
print r
print Util.VersaDataToXml(r)
r = Util.VersaQuery("rdf:Bag <- rdf:type - *", model, nsMap=NSMAP)
print r
print Util.VersaDataToXml(r)

"""

##SQL Warning: spurious FROM source tables that are not narrowed down
##in the WHERE clause can cause weird duplicate rows in the result because of the cartesian product
##For instance, if there are 4 rows in the defaultUriHead table and
##15 in defaultRdfResource, the following query will return 60 (4*15)
##duplicates of each resultant row in the defaultStatement table:

##SELECT S.*
##    FROM  defaultStatement AS S, defaultStringLiteral AS STR,
##          defaultUriHead AS SU_O, defaultRdfResource AS R_O
##    WHERE S.objectTypeCode = 1 AND S.objectID = STR.ID AND
##          STR.value = 'World Wide Web Home Page'

##Simple fix is to eliminate the spurious source tables:

##SELECT S.*
##    FROM  defaultStatement AS S, defaultStringLiteral AS STR
##    WHERE S.objectTypeCode = 1 AND S.objectID = STR.ID AND
##          STR.value = 'World Wide Web Home Page'
