def after_table(self):
statement = self.statement
if hasattr(statement, '__call__'):
statement = statement()
if not isinstance(statement, list):
statement = [statement]
for s in statement:
ddl = DDL(s, self.on, self.context)
ddl.execute_at(self.when, self.entity.table)
def after_table(self):
statement = self.statement
if hasattr(statement, '__call__'):
statement = statement()
if not isinstance(statement, list):
statement = [statement]
for s in statement:
ddl = DDL(s, self.on, self.context)
ddl.execute_at(self.when, self.entity.table)
def create_triggers(db, tables):
db = create_engine(engine_name)
db.echo = True
db.connect()
metadata = MetaData(db)
insp = reflection.Inspector.from_engine(db)
tables = []
for table_name in insp.get_table_names():
if not table_name.endswith('_aud'):
table = Table(table_name, metadata, autoload=True, autoload_with=db)
tables.append(table)
#print("TABLE: %s"%table)
#print table.__repr__
else:
table = Table(table_name, metadata, autoload=True, autoload_with=db)
table.drop(db)
metadata.remove(table)
drop_trigger_text = """DROP TRIGGER IF EXISTS %(trigger_name)s;"""
for table in tables:
pk_cols = [c.name for c in table.primary_key]
for pk_col in pk_cols:
try:
db.execute(drop_trigger_text % {
'trigger_name' : table.name + "_ins_trig",
})
except:
pass
for pk_col in pk_cols:
try:
db.execute(drop_trigger_text % {
'trigger_name' : table.name + "_upd_trig",
})
except:
pass
#metadata.create_all()
trigger_text = """
CREATE TRIGGER
%(trigger_name)s
AFTER %(action)s ON
%(table_name)s
FOR EACH ROW
BEGIN
INSERT INTO %(table_name)s_aud
SELECT
d.*,
'%(action)s',
NULL,
NOW()
FROM
%(table_name)s
AS d
WHERE
%(pkd)s;
END
"""
for table in tables:
pk_cols = [c.name for c in table.primary_key]
pkd = []
for pk_col in pk_cols:
pkd.append("d.%s = NEW.%s"%(pk_col, pk_col))
text_dict = {
'action' : 'INSERT',
'trigger_name' : table.name + "_ins_trig",
'table_name' : table.name,
'pkd' : ' and '.join(pkd),
}
logging.info(trigger_text % text_dict)
trig_ddl = DDL(trigger_text % text_dict)
trig_ddl.execute_at('after-create', table.metadata)
text_dict['action'] = 'UPDATE'
text_dict['trigger_name'] = table.name + "_upd_trig"
trig_ddl = DDL(trigger_text % text_dict)
trig_ddl.execute_at('after-create', table.metadata)
metadata.create_all()
def create_triggers(db, tables):
db = create_engine(engine_name)
db.echo = True
db.connect()
metadata = MetaData(db)
insp = reflection.Inspector.from_engine(db)
tables = []
for table_name in insp.get_table_names():
if not table_name.endswith('_aud'):
table = Table(table_name, metadata, autoload=True, autoload_with=db)
tables.append(table)
#print("TABLE: %s"%table)
#print table.__repr__
else:
table = Table(table_name, metadata, autoload=True, autoload_with=db)
table.drop(db)
metadata.remove(table)
drop_trigger_text = """DROP TRIGGER IF EXISTS %(trigger_name)s;"""
for table in tables:
pk_cols = [c.name for c in table.primary_key]
for pk_col in pk_cols:
try:
db.execute(drop_trigger_text % {
'trigger_name' : table.name + "_ins_trig",
})
except:
pass
for pk_col in pk_cols:
try:
db.execute(drop_trigger_text % {
'trigger_name' : table.name + "_upd_trig",
})
except:
pass
#metadata.create_all()
trigger_text = """
CREATE TRIGGER
%(trigger_name)s
AFTER %(action)s ON
%(table_name)s
FOR EACH ROW
BEGIN
INSERT INTO %(table_name)s_aud
SELECT
d.*,
'%(action)s',
NULL,
date('now')
FROM
%(table_name)s
AS d
WHERE
%(pkd)s;
END
"""
for table in tables:
pk_cols = [c.name for c in table.primary_key]
pkd = []
for pk_col in pk_cols:
pkd.append("d.%s = NEW.%s"%(pk_col, pk_col))
text_dict = {
'action' : 'INSERT',
'trigger_name' : table.name + "_ins_trig",
'table_name' : table.name,
'pkd' : ' and '.join(pkd),
}
logging.info(trigger_text % text_dict)
trig_ddl = DDL(trigger_text % text_dict)
trig_ddl.execute_at('after-create', table.metadata)
text_dict['action'] = 'UPDATE'
text_dict['trigger_name'] = table.name + "_upd_trig"
trig_ddl = DDL(trigger_text % text_dict)
trig_ddl.execute_at('after-create', table.metadata)
metadata.create_all()
