Viewing file:  Odbc.py (10.68 KB) -rw-r--r--
Select action/file-type:
 (+) |  (+) |  (+) | Code (+) | Session (+) |  (+) | SDB (+) |  (+) |  (+) |  (+) |  (+) |  (+) |
########################################################################
# $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']),
}
|