diff -r 9767cc516b4f -r 083b4d454192 server/sources/native.py --- a/server/sources/native.py Wed Mar 10 16:07:24 2010 +0100 +++ b/server/sources/native.py Mon Mar 01 11:26:14 2010 +0100 @@ -11,8 +11,11 @@ :contact: http://www.logilab.fr/ -- mailto:contact@logilab.fr :license: GNU Lesser General Public License, v2.1 - http://www.gnu.org/licenses """ +from __future__ import with_statement + __docformat__ = "restructuredtext en" +from pickle import loads, dumps from threading import Lock from datetime import datetime from base64 import b64decode, b64encode @@ -24,12 +27,15 @@ from logilab.common.shellutils import getlogin from logilab.database import get_db_helper -from cubicweb import UnknownEid, AuthenticationError, Binary, server +from cubicweb import UnknownEid, AuthenticationError, Binary, server, neg_role +from cubicweb import transaction as tx +from cubicweb.schema import VIRTUAL_RTYPES from cubicweb.cwconfig import CubicWebNoAppConfiguration from cubicweb.server import hook from cubicweb.server.utils import crypt_password from cubicweb.server.sqlutils import SQL_PREFIX, SQLAdapterMixIn from cubicweb.server.rqlannotation import set_qdata +from cubicweb.server.session import hooks_control from cubicweb.server.sources import AbstractSource, dbg_st_search, dbg_results from cubicweb.server.sources.rql2sql import SQLGenerator @@ -93,6 +99,35 @@ table, restr, attr) +def sql_or_clauses(sql, clauses): + select, restr = sql.split(' WHERE ', 1) + restrclauses = restr.split(' AND ') + for clause in clauses: + restrclauses.remove(clause) + if restrclauses: + restr = '%s AND (%s)' % (' AND '.join(restrclauses), + ' OR '.join(clauses)) + else: + restr = '(%s)' % ' OR '.join(clauses) + return '%s WHERE %s' % (select, restr) + + +class UndoException(Exception): + """something went wrong during undoing""" + + +def _undo_check_relation_target(tentity, rdef, role): + """check linked entity has not been redirected for this relation""" + card = rdef.role_cardinality(role) + if card in '?1' and tentity.related(rdef.rtype, role): + raise UndoException(tentity._cw._( + "Can't restore %(role)s relation %(rtype)s to entity %(eid)s which " + "is already linked using this relation.") + % {'role': neg_role(role), + 'rtype': rdef.rtype, + 'eid': tentity.eid}) + + class NativeSQLSource(SQLAdapterMixIn, AbstractSource): """adapter for source using the native cubicweb schema (see below) """ @@ -370,34 +405,57 @@ def add_entity(self, session, entity): """add a new entity to the source""" attrs = self.preprocess_entity(entity) - sql = self.sqlgen.insert(SQL_PREFIX + str(entity.e_schema), attrs) + sql = self.sqlgen.insert(SQL_PREFIX + entity.__regid__, attrs) self.doexec(session, sql, attrs) + if session.undoable_action('C', entity.__regid__): + self._record_tx_action(session, 'tx_entity_actions', 'C', + etype=entity.__regid__, eid=entity.eid) def update_entity(self, session, entity): """replace an entity in the source""" attrs = self.preprocess_entity(entity) - sql = self.sqlgen.update(SQL_PREFIX + str(entity.e_schema), attrs, - [SQL_PREFIX + 'eid']) + if session.undoable_action('U', entity.__regid__): + changes = self._save_attrs(session, entity, attrs) + self._record_tx_action(session, 'tx_entity_actions', 'U', + etype=entity.__regid__, eid=entity.eid, + changes=self._binary(dumps(changes))) + sql = self.sqlgen.update(SQL_PREFIX + entity.__regid__, attrs, + ['cw_eid']) self.doexec(session, sql, attrs) - def delete_entity(self, session, etype, eid): + def delete_entity(self, session, entity): """delete an entity from the source""" - attrs = {SQL_PREFIX + 'eid': eid} - sql = self.sqlgen.delete(SQL_PREFIX + etype, attrs) + if session.undoable_action('D', entity.__regid__): + attrs = [SQL_PREFIX + r.type + for r in entity.e_schema.subject_relations() + if (r.final or r.inlined) and not r in VIRTUAL_RTYPES] + changes = self._save_attrs(session, entity, attrs) + self._record_tx_action(session, 'tx_entity_actions', 'D', + etype=entity.__regid__, eid=entity.eid, + changes=self._binary(dumps(changes))) + attrs = {'cw_eid': entity.eid} + sql = self.sqlgen.delete(SQL_PREFIX + entity.__regid__, attrs) self.doexec(session, sql, attrs) - def add_relation(self, session, subject, rtype, object, inlined=False): + def _add_relation(self, session, subject, rtype, object, inlined=False): """add a relation to the source""" if inlined is False: attrs = {'eid_from': subject, 'eid_to': object} sql = self.sqlgen.insert('%s_relation' % rtype, attrs) else: # used by data import etype = session.describe(subject)[0] - attrs = {SQL_PREFIX + 'eid': subject, SQL_PREFIX + rtype: object} + attrs = {'cw_eid': subject, SQL_PREFIX + rtype: object} sql = self.sqlgen.update(SQL_PREFIX + etype, attrs, - [SQL_PREFIX + 'eid']) + ['cw_eid']) self.doexec(session, sql, attrs) + def add_relation(self, session, subject, rtype, object, inlined=False): + """add a relation to the source""" + self._add_relation(session, subject, rtype, object, inlined) + if session.undoable_action('A', rtype): + self._record_tx_action(session, 'tx_relation_actions', 'A', + eid_from=subject, rtype=rtype, eid_to=object) + def delete_relation(self, session, subject, rtype, object): """delete a relation from the source""" rschema = self.schema.rschema(rtype) @@ -411,6 +469,9 @@ attrs = {'eid_from': subject, 'eid_to': object} sql = self.sqlgen.delete('%s_relation' % rtype, attrs) self.doexec(session, sql, attrs) + if session.undoable_action('R', rtype): + self._record_tx_action(session, 'tx_relation_actions', 'R', + eid_from=subject, rtype=rtype, eid_to=object) def doexec(self, session, query, args=None, rollback=True): """Execute a query. @@ -467,6 +528,9 @@ # short cut to method requiring advanced db helper usage ################## + def binary_to_str(self, value): + return self.dbhelper.dbapi_module.binary_to_str(value) + def create_index(self, session, table, column, unique=False): cursor = LogCursor(session.pool[self.uri]) self.dbhelper.create_index(cursor, table, column, unique) @@ -481,7 +545,7 @@ """return a tuple (type, source, extid) for the entity with id """ sql = 'SELECT type, source, extid FROM entities WHERE eid=%s' % eid try: - res = session.system_sql(sql).fetchone() + res = self.doexec(session, sql).fetchone() except: assert session.pool, 'session has no pool set' raise UnknownEid(eid) @@ -496,9 +560,10 @@ def extid2eid(self, session, source, extid): """get eid from an external id. Return None if no record found.""" assert isinstance(extid, str) - cursor = session.system_sql('SELECT eid FROM entities WHERE ' - 'extid=%(x)s AND source=%(s)s', - {'x': b64encode(extid), 's': source.uri}) + cursor = self.doexec(session, + 'SELECT eid FROM entities ' + 'WHERE extid=%(x)s AND source=%(s)s', + {'x': b64encode(extid), 's': source.uri}) # XXX testing rowcount cause strange bug with sqlite, results are there # but rowcount is 0 #if cursor.rowcount > 0: @@ -529,7 +594,7 @@ finally: self._eid_creation_lock.release() - def add_info(self, session, entity, source, extid=None, complete=True): + def add_info(self, session, entity, source, extid, complete): """add type and source info for an eid into the system table""" # begin by inserting eid/type/source/extid into the entities table if extid is not None: @@ -537,7 +602,7 @@ extid = b64encode(extid) attrs = {'type': entity.__regid__, 'eid': entity.eid, 'extid': extid, 'source': source.uri, 'mtime': datetime.now()} - session.system_sql(self.sqlgen.insert('entities', attrs), attrs) + self.doexec(session, self.sqlgen.insert('entities', attrs), attrs) # now we can update the full text index if self.do_fti and self.need_fti_indexation(entity.__regid__): if complete: @@ -545,26 +610,28 @@ FTIndexEntityOp(session, entity=entity) def update_info(self, session, entity, need_fti_update): + """mark entity as being modified, fulltext reindex if needed""" if self.do_fti and need_fti_update: # reindex the entity only if this query is updating at least # one indexable attribute FTIndexEntityOp(session, entity=entity) # update entities.mtime attrs = {'eid': entity.eid, 'mtime': datetime.now()} - session.system_sql(self.sqlgen.update('entities', attrs, ['eid']), attrs) + self.doexec(session, self.sqlgen.update('entities', attrs, ['eid']), attrs) - def delete_info(self, session, eid, etype, uri, extid): + def delete_info(self, session, entity, uri, extid): """delete system information on deletion of an entity by transfering record from the entities table to the deleted_entities table """ - attrs = {'eid': eid} - session.system_sql(self.sqlgen.delete('entities', attrs), attrs) + attrs = {'eid': entity.eid} + self.doexec(session, self.sqlgen.delete('entities', attrs), attrs) if extid is not None: assert isinstance(extid, str), type(extid) extid = b64encode(extid) - attrs = {'type': etype, 'eid': eid, 'extid': extid, - 'source': uri, 'dtime': datetime.now()} - session.system_sql(self.sqlgen.insert('deleted_entities', attrs), attrs) + attrs = {'type': entity.__regid__, 'eid': entity.eid, 'extid': extid, + 'source': uri, 'dtime': datetime.now(), + } + self.doexec(session, self.sqlgen.insert('deleted_entities', attrs), attrs) def modified_entities(self, session, etypes, mtime): """return a 2-uple: @@ -575,13 +642,315 @@ deleted since the given timestamp """ modsql = _modified_sql('entities', etypes) - cursor = session.system_sql(modsql, {'time': mtime}) + cursor = self.doexec(session, modsql, {'time': mtime}) modentities = cursor.fetchall() delsql = _modified_sql('deleted_entities', etypes) - cursor = session.system_sql(delsql, {'time': mtime}) + cursor = self.doexec(session, delsql, {'time': mtime}) delentities = cursor.fetchall() return modentities, delentities + # undo support ############################################################# + + def undoable_transactions(self, session, ueid=None, **actionfilters): + """See :class:`cubicweb.dbapi.Connection.undoable_transactions`""" + # force filtering to session's user if not a manager + if not session.user.is_in_group('managers'): + ueid = session.user.eid + restr = {} + if ueid is not None: + restr['tx_user'] = ueid + sql = self.sqlgen.select('transactions', restr, ('tx_uuid', 'tx_time', 'tx_user')) + if actionfilters: + # we will need subqueries to filter transactions according to + # actions done + tearestr = {} # filters on the tx_entity_actions table + trarestr = {} # filters on the tx_relation_actions table + genrestr = {} # generic filters, appliyable to both table + # unless public explicitly set to false, we only consider public + # actions + if actionfilters.pop('public', True): + genrestr['txa_public'] = True + # put additional filters in trarestr and/or tearestr + for key, val in actionfilters.iteritems(): + if key == 'etype': + # filtering on etype implies filtering on entity actions + # only, and with no eid specified + assert actionfilters.get('action', 'C') in 'CUD' + assert not 'eid' in actionfilters + tearestr['etype'] = val + elif key == 'eid': + # eid filter may apply to 'eid' of tx_entity_actions or to + # 'eid_from' OR 'eid_to' of tx_relation_actions + if actionfilters.get('action', 'C') in 'CUD': + tearestr['eid'] = val + if actionfilters.get('action', 'A') in 'AR': + trarestr['eid_from'] = val + trarestr['eid_to'] = val + elif key == 'action': + if val in 'CUD': + tearestr['txa_action'] = val + else: + assert val in 'AR' + trarestr['txa_action'] = val + else: + raise AssertionError('unknow filter %s' % key) + assert trarestr or tearestr, "can't only filter on 'public'" + subqsqls = [] + # append subqueries to the original query, using EXISTS() + if trarestr or (genrestr and not tearestr): + trarestr.update(genrestr) + trasql = self.sqlgen.select('tx_relation_actions', trarestr, ('1',)) + if 'eid_from' in trarestr: + # replace AND by OR between eid_from/eid_to restriction + trasql = sql_or_clauses(trasql, ['eid_from = %(eid_from)s', + 'eid_to = %(eid_to)s']) + trasql += ' AND transactions.tx_uuid=tx_relation_actions.tx_uuid' + subqsqls.append('EXISTS(%s)' % trasql) + if tearestr or (genrestr and not trarestr): + tearestr.update(genrestr) + teasql = self.sqlgen.select('tx_entity_actions', tearestr, ('1',)) + teasql += ' AND transactions.tx_uuid=tx_entity_actions.tx_uuid' + subqsqls.append('EXISTS(%s)' % teasql) + if restr: + sql += ' AND %s' % ' OR '.join(subqsqls) + else: + sql += ' WHERE %s' % ' OR '.join(subqsqls) + restr.update(trarestr) + restr.update(tearestr) + # we want results ordered by transaction's time descendant + sql += ' ORDER BY tx_time DESC' + cu = self.doexec(session, sql, restr) + # turn results into transaction objects + return [tx.Transaction(*args) for args in cu.fetchall()] + + def tx_info(self, session, txuuid): + """See :class:`cubicweb.dbapi.Connection.transaction_info`""" + return tx.Transaction(txuuid, *self._tx_info(session, txuuid)) + + def tx_actions(self, session, txuuid, public): + """See :class:`cubicweb.dbapi.Connection.transaction_actions`""" + self._tx_info(session, txuuid) + restr = {'tx_uuid': txuuid} + if public: + restr['txa_public'] = True + sql = self.sqlgen.select('tx_entity_actions', restr, + ('txa_action', 'txa_public', 'txa_order', + 'etype', 'eid', 'changes')) + cu = self.doexec(session, sql, restr) + actions = [tx.EntityAction(a,p,o,et,e,c and loads(self.binary_to_str(c))) + for a,p,o,et,e,c in cu.fetchall()] + sql = self.sqlgen.select('tx_relation_actions', restr, + ('txa_action', 'txa_public', 'txa_order', + 'rtype', 'eid_from', 'eid_to')) + cu = self.doexec(session, sql, restr) + actions += [tx.RelationAction(*args) for args in cu.fetchall()] + return sorted(actions, key=lambda x: x.order) + + def undo_transaction(self, session, txuuid): + """See :class:`cubicweb.dbapi.Connection.undo_transaction`""" + # set mode so pool isn't released subsquently until commit/rollback + session.mode = 'write' + errors = [] + with hooks_control(session, session.HOOKS_DENY_ALL, 'integrity'): + for action in reversed(self.tx_actions(session, txuuid, False)): + undomethod = getattr(self, '_undo_%s' % action.action.lower()) + errors += undomethod(session, action) + # remove the transactions record + self.doexec(session, + "DELETE FROM transactions WHERE tx_uuid='%s'" % txuuid) + return errors + + def start_undoable_transaction(self, session, uuid): + """session callback to insert a transaction record in the transactions + table when some undoable transaction is started + """ + ueid = session.user.eid + attrs = {'tx_uuid': uuid, 'tx_user': ueid, 'tx_time': datetime.now()} + self.doexec(session, self.sqlgen.insert('transactions', attrs), attrs) + + def _save_attrs(self, session, entity, attrs): + """return a pickleable dictionary containing current values for given + attributes of the entity + """ + restr = {'cw_eid': entity.eid} + sql = self.sqlgen.select(SQL_PREFIX + entity.__regid__, restr, attrs) + cu = self.doexec(session, sql, restr) + values = dict(zip(attrs, cu.fetchone())) + # ensure backend specific binary are converted back to string + eschema = entity.e_schema + for column in attrs: + # [3:] remove 'cw_' prefix + attr = column[3:] + if not eschema.subjrels[attr].final: + continue + if eschema.destination(attr) in ('Password', 'Bytes'): + value = values[column] + if value is not None: + values[column] = self.binary_to_str(value) + return values + + def _record_tx_action(self, session, table, action, **kwargs): + """record a transaction action in the given table (either + 'tx_entity_actions' or 'tx_relation_action') + """ + kwargs['tx_uuid'] = session.transaction_uuid() + kwargs['txa_action'] = action + kwargs['txa_order'] = session.transaction_inc_action_counter() + kwargs['txa_public'] = session.running_dbapi_query + self.doexec(session, self.sqlgen.insert(table, kwargs), kwargs) + + def _tx_info(self, session, txuuid): + """return transaction's time and user of the transaction with the given uuid. + + raise `NoSuchTransaction` if there is no such transaction of if the + session's user isn't allowed to see it. + """ + restr = {'tx_uuid': txuuid} + sql = self.sqlgen.select('transactions', restr, ('tx_time', 'tx_user')) + cu = self.doexec(session, sql, restr) + try: + time, ueid = cu.fetchone() + except TypeError: + raise tx.NoSuchTransaction() + if not (session.user.is_in_group('managers') + or session.user.eid == ueid): + raise tx.NoSuchTransaction() + return time, ueid + + def _undo_d(self, session, action): + """undo an entity deletion""" + errors = [] + err = errors.append + eid = action.eid + etype = action.etype + _ = session._ + # get an entity instance + try: + entity = self.repo.vreg['etypes'].etype_class(etype)(session) + except Exception: + err("can't restore entity %s of type %s, type no more supported" + % (eid, etype)) + return errors + # check for schema changes, entities linked through inlined relation + # still exists, rewrap binary values + eschema = entity.e_schema + getrschema = eschema.subjrels + for column, value in action.changes.items(): + rtype = column[3:] # remove cw_ prefix + try: + rschema = getrschema[rtype] + except KeyError: + err(_("Can't restore relation %(rtype)s of entity %(eid)s, " + "this relation does not exists anymore in the schema.") + % {'rtype': rtype, 'eid': eid}) + if not rschema.final: + assert value is None + # try: + # tentity = session.entity_from_eid(eid) + # except UnknownEid: + # err(_("Can't restore %(role)s relation %(rtype)s to " + # "entity %(eid)s which doesn't exist anymore.") + # % {'role': _('subject'), + # 'rtype': _(rtype), + # 'eid': eid}) + # continue + # rdef = rdefs[(eschema, tentity.__regid__)] + # try: + # _undo_check_relation_target(tentity, rdef, 'object') + # except UndoException, ex: + # err(unicode(ex)) + # continue + # if rschema.inlined: + # entity[rtype] = value + # else: + # # restore relation where inlined changed since the deletion + # del action.changes[column] + # self._add_relation(session, subject, rtype, object) + # # set related cache + # session.update_rel_cache_add(eid, rtype, value, + # rschema.symmetric) + elif eschema.destination(rtype) in ('Bytes', 'Password'): + action.changes[column] = self._binary(value) + entity[rtype] = Binary(value) + elif isinstance(value, str): + entity[rtype] = unicode(value, session.encoding, 'replace') + else: + entity[rtype] = value + entity.set_eid(eid) + entity.edited_attributes = set(entity) + entity.check() + self.repo.hm.call_hooks('before_add_entity', session, entity=entity) + # restore the entity + action.changes['cw_eid'] = eid + sql = self.sqlgen.insert(SQL_PREFIX + etype, action.changes) + self.doexec(session, sql, action.changes) + # restore record in entities (will update fti if needed) + self.add_info(session, entity, self, None, True) + # remove record from deleted_entities + self.doexec(session, 'DELETE FROM deleted_entities WHERE eid=%s' % eid) + self.repo.hm.call_hooks('after_add_entity', session, entity=entity) + return errors + + def _undo_r(self, session, action): + """undo a relation removal""" + errors = [] + err = errors.append + _ = session._ + subj, rtype, obj = action.eid_from, action.rtype, action.eid_to + entities = [] + for role, eid in (('subject', subj), ('object', obj)): + try: + entities.append(session.entity_from_eid(eid)) + except UnknownEid: + err(_("Can't restore relation %(rtype)s, %(role)s entity %(eid)s" + " doesn't exist anymore.") + % {'role': _(role), + 'rtype': _(rtype), + 'eid': eid}) + if not len(entities) == 2: + return errors + sentity, oentity = entities + try: + rschema = self.schema.rschema(rtype) + rdef = rschema.rdefs[(sentity.__regid__, oentity.__regid__)] + except KeyError: + err(_("Can't restore relation %(rtype)s between %(subj)s and " + "%(obj)s, that relation does not exists anymore in the " + "schema.") + % {'rtype': rtype, + 'subj': subj, + 'obj': obj}) + else: + for role, entity in (('subject', sentity), + ('object', oentity)): + try: + _undo_check_relation_target(entity, rdef, role) + except UndoException, ex: + err(unicode(ex)) + continue + if not errors: + self.repo.hm.call_hooks('before_add_relation', session, + eidfrom=subj, rtype=rtype, eidto=obj) + # add relation in the database + self._add_relation(session, subj, rtype, obj, rschema.inlined) + # set related cache + session.update_rel_cache_add(subj, rtype, obj, rschema.symmetric) + self.repo.hm.call_hooks('after_add_relation', session, + eidfrom=subj, rtype=rtype, eidto=obj) + return errors + + def _undo_c(self, session, action): + """undo an entity creation""" + return ['undoing of entity creation not yet supported.'] + + def _undo_u(self, session, action): + """undo an entity update""" + return ['undoing of entity updating not yet supported.'] + + def _undo_a(self, session, action): + """undo a relation addition""" + return ['undoing of relation addition not yet supported.'] + # full text index handling ################################################# @cached @@ -650,7 +1019,7 @@ def sql_schema(driver): helper = get_db_helper(driver) - tstamp_col_type = helper.TYPE_MAPPING['Datetime'] + typemap = helper.TYPE_MAPPING schema = """ /* Create the repository's system database */ @@ -662,10 +1031,10 @@ source VARCHAR(64) NOT NULL, mtime %s NOT NULL, extid VARCHAR(256) -); -CREATE INDEX entities_type_idx ON entities(type); -CREATE INDEX entities_mtime_idx ON entities(mtime); -CREATE INDEX entities_extid_idx ON entities(extid); +);; +CREATE INDEX entities_type_idx ON entities(type);; +CREATE INDEX entities_mtime_idx ON entities(mtime);; +CREATE INDEX entities_extid_idx ON entities(extid);; CREATE TABLE deleted_entities ( eid INTEGER PRIMARY KEY NOT NULL, @@ -673,11 +1042,58 @@ source VARCHAR(64) NOT NULL, dtime %s NOT NULL, extid VARCHAR(256) -); -CREATE INDEX deleted_entities_type_idx ON deleted_entities(type); -CREATE INDEX deleted_entities_dtime_idx ON deleted_entities(dtime); -CREATE INDEX deleted_entities_extid_idx ON deleted_entities(extid); -""" % (helper.sql_create_sequence('entities_id_seq'), tstamp_col_type, tstamp_col_type) +);; +CREATE INDEX deleted_entities_type_idx ON deleted_entities(type);; +CREATE INDEX deleted_entities_dtime_idx ON deleted_entities(dtime);; +CREATE INDEX deleted_entities_extid_idx ON deleted_entities(extid);; + +CREATE TABLE transactions ( + tx_uuid CHAR(32) PRIMARY KEY NOT NULL, + tx_user INTEGER NOT NULL, + tx_time %s NOT NULL +);; +CREATE INDEX transactions_tx_user_idx ON transactions(tx_user);; + +CREATE TABLE tx_entity_actions ( + tx_uuid CHAR(32) REFERENCES transactions(tx_uuid) ON DELETE CASCADE, + txa_action CHAR(1) NOT NULL, + txa_public %s NOT NULL, + txa_order INTEGER, + eid INTEGER NOT NULL, + etype VARCHAR(64) NOT NULL, + changes %s +);; +CREATE INDEX tx_entity_actions_txa_action_idx ON tx_entity_actions(txa_action);; +CREATE INDEX tx_entity_actions_txa_public_idx ON tx_entity_actions(txa_public);; +CREATE INDEX tx_entity_actions_eid_idx ON tx_entity_actions(eid);; +CREATE INDEX tx_entity_actions_etype_idx ON tx_entity_actions(etype);; + +CREATE TABLE tx_relation_actions ( + tx_uuid CHAR(32) REFERENCES transactions(tx_uuid) ON DELETE CASCADE, + txa_action CHAR(1) NOT NULL, + txa_public %s NOT NULL, + txa_order INTEGER, + eid_from INTEGER NOT NULL, + eid_to INTEGER NOT NULL, + rtype VARCHAR(256) NOT NULL +);; +CREATE INDEX tx_relation_actions_txa_action_idx ON tx_relation_actions(txa_action);; +CREATE INDEX tx_relation_actions_txa_public_idx ON tx_relation_actions(txa_public);; +CREATE INDEX tx_relation_actions_eid_from_idx ON tx_relation_actions(eid_from);; +CREATE INDEX tx_relation_actions_eid_to_idx ON tx_relation_actions(eid_to);; +""" % (helper.sql_create_sequence('entities_id_seq').replace(';', ';;'), + typemap['Datetime'], typemap['Datetime'], typemap['Datetime'], + typemap['Boolean'], typemap['Bytes'], typemap['Boolean']) + if helper.backend_name == 'sqlite': + # sqlite support the ON DELETE CASCADE syntax but do nothing + schema += ''' +CREATE TRIGGER fkd_transactions +BEFORE DELETE ON transactions +FOR EACH ROW BEGIN + DELETE FROM tx_entity_actions WHERE tx_uuid=OLD.tx_uuid; + DELETE FROM tx_relation_actions WHERE tx_uuid=OLD.tx_uuid; +END;; +''' return schema @@ -687,18 +1103,19 @@ %s DROP TABLE entities; DROP TABLE deleted_entities; +DROP TABLE transactions; +DROP TABLE tx_entity_actions; +DROP TABLE tx_relation_actions; """ % helper.sql_drop_sequence('entities_id_seq') def grant_schema(user, set_owner=True): result = '' - if set_owner: - result = 'ALTER TABLE entities OWNER TO %s;\n' % user - result += 'ALTER TABLE deleted_entities OWNER TO %s;\n' % user - result += 'ALTER TABLE entities_id_seq OWNER TO %s;\n' % user - result += 'GRANT ALL ON entities TO %s;\n' % user - result += 'GRANT ALL ON deleted_entities TO %s;\n' % user - result += 'GRANT ALL ON entities_id_seq TO %s;\n' % user + for table in ('entities', 'deleted_entities', 'entities_id_seq', + 'transactions', 'tx_entity_actions', 'tx_relation_actions'): + if set_owner: + result = 'ALTER TABLE %s OWNER TO %s;\n' % (table, user) + result += 'GRANT ALL ON %s TO %s;\n' % (table, user) return result