tables.append(tasks) log = Table('history') log.add_cols( Column('taskid').integer, Column('title').text.not_null.default("''"), Column('note').text.not_null.default("''"), Column('deleted').integer.not_null.default(0), Column('updated').timestamp.not_null.default_current_timestamp) log.add_constraints(ForeignKey('taskid').references(tasks.name)\ .on_delete_set_null) tables.append(log) for name in ['tr_up_history', 'tr_ins_history', 'tr_del_history']: t = Trigger(name).temp.if_not_exists deleted = 'new.deleted' if 'up' in name: t.after.update_on(tasks.name) elif 'ins' in name: t.after.insert_on(tasks.name) else: t.before.delete_on(tasks.name) deleted = 1 t.do_sql("INSERT INTO {} \ (taskid, title, note, deleted) \ VALUES (new._id, new.title, new.note, {})".format(log.name, deleted)) triggers.append(t) # Notifications nots = Table('notification')
from AndroidCodeGenerator.sql_validator import SQLTester persons = Table('Person').add_cols(Column('firstname').text.not_null.default("''"),\ Column('lastname').text.not_null.default("''"),\ Column('bio').text.not_null.default("''"))\ .add_constraints(Unique('firstname').on_conflict_replace) log = Table('Log').add_cols(Column('pId').integer.not_null, Column('firstname').text.not_null, Column('lastname').text.not_null, Column('bio').text.not_null, Column('time').timestamp.default_current_timestamp) # Create a trigger that keeps the log up to date # I recommend using temp triggers unless you see a performance hit trigger = Trigger('tr_log').temp.if_not_exists # Here I have given the wrong table name on purpose. Notice that the sql # creation will fail because there is no table named bob. trigger.after.update_on("bob") #trigger.after.update_on(log.name) # Raw sql trigger.do_sql("INSERT INTO {table} ({cols}) VALUES\ ({oldcols})".format(table=log.name, cols=log.list_column_names(exclude=['_id','time']), oldcols=persons.list_column_names(exclude=[], prefix="old."))) # Let's try to create the SQL st = SQLTester()
persons = Table('Person').add_cols(Column('firstname').text.not_null.default("''"),\ Column('lastname').text.not_null.default("''"),\ Column('bio').text.not_null.default("''"))\ .add_constraints(Unique('firstname').on_conflict_replace) log = Table('Log').add_cols( Column('pId').integer.not_null, Column('firstname').text.not_null, Column('lastname').text.not_null, Column('bio').text.not_null, Column('time').timestamp.default_current_timestamp) # Create a trigger that keeps the log up to date # I recommend using temp triggers unless you see a performance hit trigger = Trigger('tr_log').temp.if_not_exists trigger.after.update_on(log.name) # Raw sql trigger.do_sql("INSERT INTO {table} ({cols}) VALUES\ ({oldcols})".format(table=log.name, cols=log.list_column_names(exclude=['_id', 'time']), oldcols=persons.list_column_names(exclude=[], prefix="old."))) g = Generator(srcdir='./sample/src/', pkg='com.example.appname.database') g.add_tables(persons, log) g.add_triggers(trigger)
tables.append(tasks) log = Table("history") log.add_cols( Column("taskid").integer, Column("title").text.not_null.default("''"), Column("note").text.not_null.default("''"), Column("deleted").integer.not_null.default(0), Column("updated").timestamp.not_null.default_current_timestamp, ) log.add_constraints(ForeignKey("taskid").references(tasks.name).on_delete_set_null) tables.append(log) for name in ["tr_up_history", "tr_ins_history", "tr_del_history"]: t = Trigger(name).temp.if_not_exists deleted = "new.deleted" if "up" in name: t.after.update_on(tasks.name) elif "ins" in name: t.after.insert_on(tasks.name) else: t.before.delete_on(tasks.name) deleted = 1 t.do_sql( "INSERT INTO {} \ (taskid, title, note, deleted) \ VALUES (new._id, new.title, new.note, {})".format( log.name, deleted ) )