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
)
)
