.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') nots.add_cols( Column('time').integer, Column('permanent').integer.not_null.default(0), Column('taskid').integer, Column('repeats').integer.not_null.default(0), Column('locationname').text, Column('latitude').real, Column('longitude').real, Column('radius').real) nots.add_constraints(ForeignKey('taskid').references(tasks.name)\
.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() st.add_tables(persons, log) st.add_triggers(trigger) st.test_create()
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) g.write()
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") nots.add_cols( Column("time").integer, Column("permanent").integer.not_null.default(0), Column("taskid").integer, Column("repeats").integer.not_null.default(0), Column("locationname").text, Column("latitude").real, Column("longitude").real,