From 585351e2bffa9b8fa2eb792cfd0e6a78a6eb1b59 Mon Sep 17 00:00:00 2001 From: alex_nanou Date: Tue, 23 Aug 2005 23:35:50 +0000 Subject: [PATCH] *** empty log message *** --- CHANGES | 2 +- pli/persistance/__init__.py | 11 + pli/persistance/sql/__init__.py | 11 + pli/persistance/sql/core.py | 533 ++++++++++++++++++++++++++++++++++ pli/persistance/sql/dispatch.py | 92 ++++++ pli/persistance/sql/mssql/__init__.py | 11 + pli/persistance/sql/pgsql/__init__.py | 11 + pli/persistance/sql/shelve.py | 58 ++++ pli/persistance/sql/sql.py | 293 +++++++++++++++++++ 9 files changed, 1021 insertions(+), 1 deletion(-) create mode 100755 pli/persistance/__init__.py create mode 100755 pli/persistance/sql/__init__.py create mode 100755 pli/persistance/sql/core.py create mode 100755 pli/persistance/sql/dispatch.py create mode 100755 pli/persistance/sql/mssql/__init__.py create mode 100755 pli/persistance/sql/pgsql/__init__.py create mode 100755 pli/persistance/sql/shelve.py create mode 100755 pli/persistance/sql/sql.py diff --git a/CHANGES b/CHANGES index 2bbbb42..47b9b9f 100644 --- a/CHANGES +++ b/CHANGES @@ -1,4 +1,4 @@ -# this file was generated on [200508240325] +# this file was generated on [200508240339] pli changes: version 0.0.143 (200507051502): diff --git a/pli/persistance/__init__.py b/pli/persistance/__init__.py new file mode 100755 index 0000000..db28e21 --- /dev/null +++ b/pli/persistance/__init__.py @@ -0,0 +1,11 @@ +#======================================================================= + +__version__ = '''0.0.01''' +__sub_version__ = '''20040115052440''' +__copyright__ = '''(c) Alex A. Naanou 2003''' + + +#----------------------------------------------------------------------- + +#======================================================================= +# vim:set ts=4 sw=4 nowrap : diff --git a/pli/persistance/sql/__init__.py b/pli/persistance/sql/__init__.py new file mode 100755 index 0000000..db28e21 --- /dev/null +++ b/pli/persistance/sql/__init__.py @@ -0,0 +1,11 @@ +#======================================================================= + +__version__ = '''0.0.01''' +__sub_version__ = '''20040115052440''' +__copyright__ = '''(c) Alex A. Naanou 2003''' + + +#----------------------------------------------------------------------- + +#======================================================================= +# vim:set ts=4 sw=4 nowrap : diff --git a/pli/persistance/sql/core.py b/pli/persistance/sql/core.py new file mode 100755 index 0000000..c7e1f68 --- /dev/null +++ b/pli/persistance/sql/core.py @@ -0,0 +1,533 @@ +#======================================================================= + +__version__ = '''0.0.01''' +__sub_version__ = '''20050824030618''' +__copyright__ = '''(c) Alex A. Naanou 2003''' + + +#----------------------------------------------------------------------- + +import pickle +import sys +import types + + + +#----------------------------------------------------------------------- +#-------------------------------------------------------transactioned--- +# XXX transaction decorator... +def transactioned(): + ''' + ''' + return + + + +#----------------------------------------------------------------------- +#-------------------------------------------------registertypehandler--- +# XXX can this be made into a generic dispatch decorator??? +def registertypehandler(type): + ''' + ''' + handlers = sys._getframe(1).f_locals['_typehandlers'] + def handler(func): + handlers[type] = func + return func + return handler + + +#-----------------------------------------------------------SQLWriter--- +##!! REVISE !!## +##!!! ADD FUNCTIONS AND OTHER TYPES (fallback to pickle) !!!## +# XXX this is not able to pickle extension types... (fix?) +# TODO make an atomic type handler constructor... (should this be +# decoratable??) +# TODO make ALL of thefolowing packable into transactions... +# XXX needs more pedantic checking... +# XXX add value check for mutable objects... (if value exists then +# return old id...) +class SQLWriter(object): + ''' + ''' + _typehandlers = {} + + __object_native_attrs__ = ('__dict__', '__class__') + + def __init__(self, sql): + ''' + ''' + self.sql = sql + # atomic type handlers... + @registertypehandler(int) + def do_int(self, o, oid=None): + ''' + ''' + obj_id = self.sql.insert('py_object', type='py_int').lastrowid + obj_id = self.sql.select('pyoid', 'py_object', self.sql.where(oid=obj_id)).fetchone()[0] + self.sql.insert('py_int', pyoid=obj_id, value=o) + return obj_id + @registertypehandler(long) + def do_long(self, o, oid=None): + ''' + ''' + obj_id = self.sql.insert('py_object', type='py_long').lastrowid + obj_id = self.sql.select('pyoid', 'py_object', self.sql.where(oid=obj_id)).fetchone()[0] + self.sql.insert('py_long', pyoid=obj_id, value=o) + return obj_id + @registertypehandler(float) + def do_float(self, o, oid=None): + ''' + ''' + obj_id = self.sql.insert('py_object', type='py_float').lastrowid + obj_id = self.sql.select('pyoid', 'py_object', self.sql.where(oid=obj_id)).fetchone()[0] + self.sql.insert('py_float', pyoid=obj_id, value=o) + return obj_id +## @registertypehandler(complex) +## def do_complex(self, o, oid=None): +## ''' +## ''' +## pass + @registertypehandler(str) + def do_str(self, o, oid=None): + ''' + ''' + obj_id = self.sql.insert('py_object', type='py_str').lastrowid + obj_id = self.sql.select('pyoid', 'py_object', self.sql.where(oid=obj_id)).fetchone()[0] + self.sql.insert('py_str', pyoid=obj_id, value=o) + return obj_id + @registertypehandler(unicode) + def do_unicode(self, o, oid=None): + ''' + ''' + obj_id = self.sql.insert('py_object', type='py_unicode').lastrowid + obj_id = self.sql.select('pyoid', 'py_object', self.sql.where(oid=obj_id)).fetchone()[0] + self.sql.insert('py_unicode', pyoid=obj_id, value=o) + return obj_id + @registertypehandler(tuple) + def do_tuple(self, tpl, oid=None): + ''' + + py_tuple row format: + pyoid(oid) -> py_object.pyoid + + py_tuple_items row format: + pyoid(oid) -> py_tuple.pyoid + order(int) + value(oid) -> py_object.pyoid + ''' + obj_id = self.sql.insert('py_object', type='py_tuple').lastrowid + obj_id = self.sql.select('pyoid', 'py_object', self.sql.where(oid=obj_id)).fetchone()[0] + self.sql.insert('py_tuple', pyoid=obj_id) + for i, o in enumerate(tpl): + # insert the element... + item_id = self.write(o) + self.sql.insert('py_tuple_item', order=i, pyoid=obj_id, value=item_id) + return obj_id + # mutable handlers... + @registertypehandler(list) + def do_list(self, lst, oid=None): + ''' + + py_list row format: + pyoid(oid) -> py_object.pyoid + + py_list_items row format: + pyoid(oid) -> py_list.pyoid + order(int) + value(oid) -> py_object.pyoid + + NOTE: if object id (oid) is given, the object will be updated. + ''' + if oid != None: + # XXX use the strategy of "keep the existing, add the new, + # remove the old." + self.sql.delete('py_list_item', self.sql.where(pyoid=oid)) + obj_id = oid + else: + obj_id = self.sql.insert('py_object', type='py_list').lastrowid + obj_id = self.sql.select('pyoid', 'py_object', self.sql.where(oid=obj_id)).fetchone()[0] + self.sql.insert('py_list', pyoid=obj_id) + # insert the list items... + for i, o in enumerate(lst): + item_id = self.write(o) + self.sql.insert('py_list_item', order=i, pyoid=obj_id, value=item_id) + return obj_id + @registertypehandler(dict) + def do_dict(self, dct, oid=None): + ''' + + py_dict row format: + pyoid(oid) -> py_object.pyoid + + py_dict_items row format: + pyoid(oid) -> py_dict.pyoid + key(oid) -> py_object.pyoid + value(oid) -> py_object.pyoid + + NOTE: if object id (oid) is given, the object will be updated. + ''' + if oid != None: + # XXX use the strategy of "keep the existing, add the new, + # remove the old." + self.sql.delete('py_dict_item', self.sql.where(pyoid=oid)) + obj_id = oid + else: + obj_id = self.sql.insert('py_object', type='py_dict').lastrowid + obj_id = self.sql.select('pyoid', 'py_object', self.sql.where(oid=obj_id)).fetchone()[0] + self.sql.insert('py_dict', pyoid=obj_id) + # insert the items... + for k, v in dct.items(): + key_id = self.write(k) + val_id = self.write(v) + self.sql.insert('py_dict_item', pyoid=obj_id, key=key_id, value=val_id) + return obj_id + @registertypehandler(object) + def do_object(self, obj, oid=None): + ''' + + NOTE: if object id (oid) is given, the object will be updated. + ''' + if oid != None: + # XXX use the strategy of "keep the existing, add the new, + # remove the old." + self.sql.delete('py_object_attribute', self.sql.where(pyoid=oid)) + obj_id = oid + else: + obj_id = self.sql.insert('py_object').lastrowid + obj_id = self.sql.select('pyoid', 'py_object', self.sql.where(oid=obj_id)).fetchone()[0] + + for n in self.__object_native_attrs__: + # insert the element... +## name_id = self.write(n) +## self.sql.insert('py_object_attribute', pyoid=obj_id, name=name_id, value=val_id) + val_id = self.write(getattr(obj, n)) + self.sql.insert('py_object_attribute', pyoid=obj_id, name=n, value=val_id) + return obj_id + # pickle handlers... + @registertypehandler(type) + def do_class(self, cls, oid=None): + ''' + ''' + obj_id = self.sql.insert('py_object', type='py_pickled_class').lastrowid + obj_id = self.sql.select('pyoid', 'py_object', self.sql.where(oid=obj_id)).fetchone()[0] + + self.sql.insert('py_pickled_class', + pyoid=obj_id, + pickle=pickle.dumps(cls)) + return obj_id + @registertypehandler(types.FunctionType) + def do_function(self, cls, oid=None): + ''' + ''' + obj_id = self.sql.insert('py_object', type='py_pickled_function').lastrowid + obj_id = self.sql.select('pyoid', 'py_object', self.sql.where(oid=obj_id)).fetchone()[0] + + self.sql.insert('py_pickled_function', + pyoid=obj_id, + pickle=pickle.dumps(cls)) + return obj_id + # HL interface methods... + # XXX make this support the pickle protocols... + def write(self, obj, oid=None): + ''' + ''' + t = type(obj) + handler = self._typehandlers.get(t, None) + if handler is None: + return self.do_object(obj, oid) + return handler(self, obj, oid) + + + +#----------------------------------------------------------------------- +#--------------------------------------------------------------Object--- +# WARNING: do not modify this here! +class Object(object): + ''' + abstract class used in object reconstruction. + ''' + pass + + +#------------------------------------------------registertablehandler--- +# XXX can this be made into a generic dispatch decorator??? +def registertablehandler(table_name): + ''' + ''' + handlers = sys._getframe(1).f_locals['_tablehandlers'] + def handler(func): + handlers[table_name] = func + return func + return handler + + +#-----------------------------------------------------------SQLReader--- +##!! REVISE !!## +##!!! ADD FUNCTIONS AND OTHER TYPES (fallback to pickle) !!!## +# TODO add lazy reconstruction option for mutable and deep objects... +# TODO make an atomic type handler constructor... (should this be +# decoratable??) +class SQLReader(object): + ''' + ''' + _tablehandlers = {} + + def __init__(self, sql): + ''' + ''' + self.sql = sql + # atomic handlers... + @registertablehandler('py_int') + def do_int(self, oid): + ''' + ''' + # sanity checks... + # XXX check if object exists (else panic?) + # get the object... + o = self.sql.select('value', 'py_int', self.sql.where(pyoid=oid)).fetchone()[0] + # XXX reconstruct attrs... + return o + @registertablehandler('py_long') + def do_long(self, oid): + ''' + ''' + # sanity checks... + # XXX check if object exists (else panic?) + # get the object... + o = self.sql.select('value', 'py_long', self.sql.where(pyoid=oid)).fetchone()[0] + # XXX reconstruct attrs... + return o + @registertablehandler('py_float') + def do_float(self, oid): + ''' + ''' + # sanity checks... + # XXX check if object exists (else panic?) + # get the object... + o = self.sql.select('value', 'py_float', self.sql.where(pyoid=oid)).fetchone()[0] + # XXX reconstruct attrs... + return o +## @registertablehandler('py_complex') +## def do_complex(self, oid): +## ''' +## ''' +## pass + @registertablehandler('py_str') + def do_str(self, oid): + ''' + ''' + # sanity checks... + # XXX check if object exists (else panic?) + # get the object... + o = self.sql.select('value', 'py_str', self.sql.where(pyoid=oid)).fetchone()[0] + # XXX reconstruct attrs... + return o + @registertablehandler('py_unicode') + def do_unicode(self, oid): + ''' + ''' + # sanity checks... + # XXX check if object exists (else panic?) + # get the object... + o = self.sql.select('value', 'py_unicode', self.sql.where(pyoid=oid)).fetchone()[0] + # XXX reconstruct attrs... + return o + @registertablehandler('py_tuple') + def do_tuple(self, oid): + ''' + ''' + # sanity checks... + # XXX check if object exists (else panic?) + # get the object... + ##!!! + o = list(self.sql.select(('order', 'value'), 'py_tuple_item', self.sql.where(pyoid=oid)).fetchall()) + o.sort() + o = tuple([ self.get(e) for (i, e) in o ]) + # XXX reconstruct attrs... + return o + # mutable handlers... + @registertablehandler('py_list') + def do_list(self, oid): + ''' + ''' + # sanity checks... + # XXX check if object exists (else panic?) + # get the object... + o = list(self.sql.select(('order', 'value'), 'py_list_item', self.sql.where(pyoid=oid)).fetchall()) + o.sort() + o = [ self.get(e) for (i, e) in o ] + # XXX reconstruct attrs... + return o + @registertablehandler('py_dict') + def do_dict(self, oid): + ''' + ''' + # sanity checks... + # XXX check if object exists (else panic?) + # get the object... + o = list(self.sql.select(('key', 'value'), 'py_dict_item', self.sql.where(pyoid=oid)).fetchall()) + o = dict([ (self.get(k), self.get(v)) for (k, v) in o ]) + # XXX reconstruct attrs... + return o + @registertablehandler('py_object') + def do_object(self, oid): + ''' + ''' + # sanity checks... + # XXX check if object exists (else panic?) + # get the object... + dct = dict(self.sql.select(('name', 'value'), 'py_object_attribute', self.sql.where(pyoid=oid)).fetchall()) + # reconstruct attrs... + for n, v in dct.items(): + dct[n] = self.get(v) + cls = dct.pop('__class__') + # generate the object... + o = Object() + for n, v in dct.items(): + setattr(o, n, v) + o.__class__ = cls + return o + # pickle handlers... + @registertablehandler('py_pickled_class') + def do_class(self, oid): + ''' + ''' + # sanity checks... + # XXX check if object exists (else panic?) + # get the object... + o = self.sql.select('pickle', 'py_pickled_class', self.sql.where(pyoid=oid)).fetchone()[0] + o = pickle.loads(o) + # XXX reconstruct attrs... + return o + @registertablehandler('py_pickled_function') + def do_function(self, oid): + ''' + ''' + # sanity checks... + # XXX check if object exists (else panic?) + # get the object... + o = self.sql.select('pickle', 'py_pickled_function', self.sql.where(pyoid=oid)).fetchone()[0] + o = pickle.loads(o) + # XXX reconstruct attrs... + return o + # HL interface methods... + # XXX make this support the pickle protocols... + def get(self, oid): + ''' + ''' + t = self.sql.select('type', 'py_object', self.sql.where(pyoid=oid)).fetchone()[0].rstrip() + # NOTE: here we compensate for a sideeffect of decorating + # methods while the class is not there yet... + return self._tablehandlers[t](self, oid) + + + +#----------------------------------------------------------------------- +#--------------------------------------------------------SQLInterface--- +# TODO special interfaces for item access of lists and dicts... +# TODO special interfaces for object length and partial data (like dict +# keys, values... etc.) +# TODO compleat the types... +# TODO pass the transation id arround to enable: +# 1) query collection into one big SQL expression. +# 2) manage multiple transactions over one or several connections +# at the same time... +# TODO keep in mind the object id of mutable objects. +# NOTE: might be a good idea to track the object id in two layers: +# 1) save time (id in the database. unique in the db) -- sOID +# 2) restore time (id in runtime) -- pOID +# the idea is to keep a record of both ids to be able to link the +# stored object to its' live version. +# when only one id is present, it means that the object is either +# not yet saved or not yet read from db. if both are present, +# then we have both versions of the object. +# +# TODO rename this! +# TODO add transaction hooks and wrappers... +# TODO split this into an abstract sql interface and a caching +# sql interface... +class AbstractSQLInterface(object): + ''' + ''' + __sql_reader__ = None + __sql_writer__ = None + + def __init__(self): + ''' + ''' + self._liveobjects = {} + # helpers... + def __update__(self, oid, obj): + ''' + be stupid and update. + + overloadable. + + WARNING: not intended for direct use. + ''' + ##!!! + return self.__sql_writer__.write(obj, oid) + def __insert__(self, obj): + ''' + be stupid and insert. + + overloadable. + + WARNING: not intended for direct use. + ''' + return self.__sql_writer__.write(obj) + def __select__(self, oid): + ''' + be stupid and get. + + overloadable. + + WARNING: not intended for direct use. + ''' + ##!!!!!! + return self.__sql_reader__.get(oid) + # interface methods + # XXX make this simpler! + def write(self, obj): + ''' + ''' + # 1) see if object has a sOID, if yes check if it is locked, if + # not then update... + # 2) disect and write/update... + # + pOID = id(obj) + tbl = dict([ (b, a) for a, b in self._liveobjects.keys() ]) + if pOID in tbl.keys(): + # update + sOID = tbl[pOID] + return self.__update__(sOID, obj) + else: + # write + sOID = self.__insert__(obj) + self._liveobjects[(sOID, pOID)] = obj + return sOID + # TODO add hook for live obj condition... + def get(self, sOID): + ''' + ''' + # 1) see if object is live, if yes see if it is locked or dirty (in + # a transaction?), if so then warn.... (???) + # 2) construct object. + # 3) save sOID, pOID, ref in self._liveobjects + # + tbl = dict(self._liveobjects.keys()) + if sOID in tbl.keys(): + ##!!! add hook... +## print 'WARNING: object already open.' + return self._liveobjects[(sOID, tbl[sOID])] + return self.__select__(sOID) + ##!!! + def delete(self, sOID): + ''' + ''' + raise NotImplementedError + + + +#======================================================================= +# vim:set ts=4 sw=4 nowrap : diff --git a/pli/persistance/sql/dispatch.py b/pli/persistance/sql/dispatch.py new file mode 100755 index 0000000..6ec7ba9 --- /dev/null +++ b/pli/persistance/sql/dispatch.py @@ -0,0 +1,92 @@ +#======================================================================= + +__version__ = '''0.0.02''' +__sub_version__ = '''20050824031152''' +__copyright__ = '''(c) Alex A. Naanou 2003''' + + +#----------------------------------------------------------------------- + + + +#------------------------------------------------------------Dispatch--- +# NOTE: might be good to split this into several classes (interigator +# and dispatch) +# TODO write docs... +# TODO write the following extencions: +# - two way dispatch... +# the rules will be something like: +# +# or +# +# - dedicated interigator (with more control over the order and +# behavior)... +class Dispatch(object): + ''' + ''' + def __init__(self, interigators=None): + ''' + ''' + if interigators == None: + interigators = [] + self._interigators = interigators + self._dispatchtable = {} + self._defailthandler = None + # interface methods: + def addhandler(self, key, handler, force=False): + ''' + ''' + if key is None: + raise TypeError, 'key can\'t be None.' + if not force and not callable(handler): + raise TypeError, 'handler must be callable.' + self._dispatchtable[key] = handler + def setdefaulthandler(self, handler): + ''' + ''' + self._defailthandler = handler + # the interigator takes the object as argument and returns a key or + # None. + ##!!! make the priority work... + ##!!! add default interigator... (???) + def addinterigator(self, interigator, prio=None): + ''' + ''' + self._interigators.insert(0, interigator) +## def getinterigationdoc(self): +## ''' +## ''' +## pass + def handle(self, obj): + ''' + ''' + # get the handler... + handler = self.gethandler(self.getkeys(obj)) + if handler != None: + return handler(obj) + raise TypeError, 'can\'t handle %s, no compatible handler defined.' + def getkeys(self, obj): + ''' + get all possible non-None keys for the input. + + the list of keys is formed using the registered interigators + and the last key is always the original object. + ''' + return [ r for r in [ i(obj) for i in self._interigators ] if r != None ] + [obj] + ##!! REVISE !!## + def gethandler(self, keys): + ''' + + NOTE: keys must be iterable. + ''' + dt = self._dispatchtable + for k in keys: + if k in dt: + return dt[k] + ##!! raise an error?? + return self._defailthandler + + + +#======================================================================= +# vim:set ts=4 sw=4 nowrap : diff --git a/pli/persistance/sql/mssql/__init__.py b/pli/persistance/sql/mssql/__init__.py new file mode 100755 index 0000000..db28e21 --- /dev/null +++ b/pli/persistance/sql/mssql/__init__.py @@ -0,0 +1,11 @@ +#======================================================================= + +__version__ = '''0.0.01''' +__sub_version__ = '''20040115052440''' +__copyright__ = '''(c) Alex A. Naanou 2003''' + + +#----------------------------------------------------------------------- + +#======================================================================= +# vim:set ts=4 sw=4 nowrap : diff --git a/pli/persistance/sql/pgsql/__init__.py b/pli/persistance/sql/pgsql/__init__.py new file mode 100755 index 0000000..db28e21 --- /dev/null +++ b/pli/persistance/sql/pgsql/__init__.py @@ -0,0 +1,11 @@ +#======================================================================= + +__version__ = '''0.0.01''' +__sub_version__ = '''20040115052440''' +__copyright__ = '''(c) Alex A. Naanou 2003''' + + +#----------------------------------------------------------------------- + +#======================================================================= +# vim:set ts=4 sw=4 nowrap : diff --git a/pli/persistance/sql/shelve.py b/pli/persistance/sql/shelve.py new file mode 100755 index 0000000..0c652a8 --- /dev/null +++ b/pli/persistance/sql/shelve.py @@ -0,0 +1,58 @@ +#======================================================================= + +__version__ = '''0.0.01''' +__sub_version__ = '''20050824025710''' +__copyright__ = '''(c) Alex A. Naanou 2003''' + + +#----------------------------------------------------------------------- + +import pli.pattern.mixin.mapping as mapping + + +#-----------------------------------------------------------SQLShelve--- +##!!! +# XXX should this be live??? +class SQLShelve(mapping.Mapping): + ''' + ''' + def __init__(self, interface, dict_id=None): + ''' + ''' + self._interface = interface + if dict_id is None: + d = self._data = {} + dict_id = interface.write(d) + else: + self._data = interface.get(dict_id) + self.dict_id = dict_id + def __getitem__(self, name): + ''' + ''' + if name in self._data: + return self._interface.get(self._data[name]) + raise KeyError, name + ##!!! make this safe... + def __setitem__(self, name, value): + ''' + ''' + data = self._data + # insert the object... + oid = self._interface.write(value) + # update the keys dict... + data[name] = oid + self._interface.write(data) + def __delitem__(self, name): + ''' + ''' + return self._interface.delete(self._data.pop(name)) + def __iter__(self): + ''' + ''' + for name in self._data: + yield name + + + +#======================================================================= +# vim:set ts=4 sw=4 nowrap : diff --git a/pli/persistance/sql/sql.py b/pli/persistance/sql/sql.py new file mode 100755 index 0000000..c497461 --- /dev/null +++ b/pli/persistance/sql/sql.py @@ -0,0 +1,293 @@ +#======================================================================= + +__version__ = '''0.0.01''' +__sub_version__ = '''20050824025606''' +__copyright__ = '''(c) Alex A. Naanou 2003''' + + +#----------------------------------------------------------------------- + + + + +#----------------------------------------------------------------------- +#-------------------------------------------------------transactioned--- +# XXX transaction decorator... +def transactioned(): + ''' + ''' + return + + + +#----------------------------------------------------------------------- +#------------------------------------------------------------SQLError--- +class SQLError(Exception): + ''' + ''' + pass + + +#----------------------------------------------------------------------- +#--------------------------------------------------------------_WHERE--- +##!!! THINK OF A BETTER WAY TO DO THIS !!!## +# XXX should the condition return the WHERE kw?? +class _WHERE(object): + ''' + compile and check a condition string... + ''' + ##!!! wrap values !!!## + def __init__(self, *p, **n): + ''' + ''' + if len(p) == 1 and type(p[0]) is str: + self.condition = p[0] + if len(p) == 0: + self.condition = ' AND '.join([ '"%s" = %s' % (k, v) for k, v in n.items() ]) + def __str__(self): + ''' + ''' + c = self.condition + return str(c) + def __repr__(self): + ''' + ''' + return str(self.condition) + + + +#----------------------------------------------------------------------- +#-----------------------------------------------------------------SQL--- +# XXX Revise extensibility to other SQL dialects... +# TODO rename to pgSQL +class SQL(object): + ''' + ''' + # the condition clause processor... + where = _WHERE + + def __init__(self, connection, cursor): + ''' + ''' + self.connection = connection + self.cursor = cursor + # utility methods + def checksqlname(self, name): + ''' + ''' + return '"%s"' % name.replace('"', '\\"') + ##!!! REWRITE !!!## + def py2sql(self, value): + ''' + translate a value into an apropriate sql type. + ''' + t = type(value) + if t in (int, float, long): + return str(value) + if t in (str, unicode): + ##!!! WRONG !!!## + return '\'%s\'' % value + if value is None: + return 'NULL' + raise TypeError, 'can\'t convert objects of type %s.' % t + ##!!! + def sql2py(self, value): + ''' + ''' + pass + # SQL expression generators + # TODO add other expressions... (CREATE, DROP, ...) + # TODO make these dialect independent... + # TODO add filter support for select (e.g. rename columns... etc.) + # TODO write more docs... + ##!!! TODO more pedantic input checking! + def select_sql(self, columns, source, + condition=None, + order=None, + # XXX the next two appear to PostgresSQL + # specific... REVISE! + count=None, + offset=None): + ''' + + SQL Syntax Reference: + + ''' + # cahe some names... + py2sql = self.py2sql + checksqlname = self.checksqlname + + # construct the source... + ##!!! CAN THIS BE ANOTHER SELECT HERE???? + source = checksqlname(source) + + # construct columns... + # NOTE: columns can be: + # - list + # - dict (???) + # - combination (???) + if type(columns) is not str: + columns = ', '.join([ checksqlname(n) for n in columns ]) + + # process the condition... + if condition is None: + condition = '' + elif type(condition) is self.where: + condition = '\n\tWHERE ' + str(condition) + else: + condition = '\n\tWHERE ' + str(self.where(condition)) + + # order... + # XXX do we need direction here??? + if order != None: + order = '\n\tORDER BY %s' % checksqlname(order) + else: + order = '' + + # count... + if count != None: + count = '\n\tLIMIT %s' % py2sql(count) + else: + count = '' + + # offset... + if offset != None: + offset = '\n\tOFFSET %s' % py2sql(offset) + else: + offset = '' + + return 'SELECT %(columns)s \n\tFROM %(source)s%(where)s%(count)s%(offset)s%(order)s ;' \ + % {'columns': columns, + 'source': source, + 'where': condition, + 'count': count, + 'order': order, + 'offset': offset,} + # TODO make setting defaults possible... (e.g. clo_x=DEFAULT) + # TODO support complex expression... (???) + ##!!! TODO more pedantic input checking! + def insert_sql(self, table, *p, **n): + ''' + generate an insert query. + + Format: + insert_sql([, [, ...]][, =[, ...]]) -> QUERY + + SQL Syntax Reference: + INSERT INTO table [ ( column [, ...] ) ] + { DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, ...] ) | query } + ''' + # cahe some names... + py2sql = self.py2sql + checksqlname = self.checksqlname + + # prepare data... + table = checksqlname(table) + columns = n.keys() + values = (tuple([ n[k] for k in columns ]) + p) or '' + # sqlify the values... + if values == '': + columns = ' DEFAULT' + else: + values = '( %s ) ' % ', '.join([ py2sql(v) for v in values ]) + # sqlify the columns... + if len(columns) == 0: + columns = '' + else: + columns = ' ( %s )' % ', '.join([ checksqlname(n) for n in columns ]) + # generate the query... + return 'INSERT INTO %s%s VALUES %s;' % (table, columns, values) + # TODO support complex expression... (???) + # TODO a more elaborate condition... (maybe a whwre method?) + ##!!! TODO more pedantic input checking! + def update_sql(self, table, condition, *p, **n): + ''' + + Format: + + SQL Syntax Reference: + UPDATE [ ONLY ] table SET column = { expression | DEFAULT } [, ...] + [ WHERE condition ] + ''' + # sanity checks... + if len(n) == 0: + raise SQLError, 'must update at least one column (none given)' + # cahe some names... + py2sql = self.py2sql + checksqlname = self.checksqlname + + # prepare data... + table = checksqlname(table) + + columns = ', '.join([ '%s = %s' % (checksqlname(k), py2sql(v)) for k, v in n.items() ]) + + # process the condition... + if condition is None: + condition = '' + elif type(condition) is self.where: + condition = '\n\tWHERE ' + str(condition) + else: + condition = '\n\tWHERE ' + str(self.where(condition)) + + # XXX should the condition return the WHERE kw?? + return 'UPDATE %s SET %s%s ;' % (table, columns, condition) + ##!!! TODO more pedantic input checking! + def delete_sql(self, table, condition=None): + ''' + + Format: + + SQL Syntax Reference: + DELETE FROM [ ONLY ] table [ WHERE condition ] + ''' + # cahe some names... + py2sql = self.py2sql + checksqlname = self.checksqlname + + # prepare data... + table = checksqlname(table) + + # process the condition... + if condition is None: + condition = '' + elif type(condition) is self.where: + condition = '\n\tWHERE ' + str(condition) + else: + condition = '\n\tWHERE ' + str(self.where(condition)) + + return 'DELETE FROM %(table)s%(where)s ;' \ + % {'table': table, + 'where': condition} + # methods + # XXX make all of the following packable into transactions!!! + # XXX do we need to process the result here??? + def select(self, columns, source, condition=None, + order=None, count=None, offset=None): + ''' + ''' + cur = self.cursor() + cur.execute(self.select_sql(columns, source, condition, order, count, offset)) + return cur + def insert(self, table, *p, **n): + ''' + ''' + cur = self.cursor() + cur.execute(self.insert_sql(table, *p, **n)) + return cur + def update(self, table, condition, *p, **n): + ''' + ''' + cur = self.cursor() + cur.execute(self.update_sql(table, condition, *p, **n)) + return cur + def delete(self, table, condition=None): + ''' + ''' + cur = self.cursor() + cur.execute(self.delete_sql(table, condition)) + return cur + + + +#======================================================================= +# vim:set ts=4 sw=4 nowrap : -- 2.11.4.GIT