SQLite triggers & datetime defaults in SQL DDL using Peewee in Python
Question: I have a SQLite table defined like so:

    
    
    create table if not exists KeyValuePair (
        key         CHAR(255) primary key not null,
        val         text not null,
        fup         timestamp default current_timestamp not null,  -- time of first upload
        lup         timestamp default current_timestamp not null  -- time of last upload
    );
    
    create trigger if not exists entry_first_insert after insert
    on KeyValuePair
    begin
        update KeyValuePair set lup = current_timestamp where key = new.key;
    end;
    
    create trigger if not exists entry_last_updated after update of value
    on KeyValuePair
    begin
        update KeyValuePair set lup = current_timestamp where key = old.key;
    end;
    

I'm trying to write a `peewee.Model` for this table in Python. This is what I
have so far:

    
    
    import peewee as pw
    
    
    db = pw.SqliteDatabase('dhm.db')
    class BaseModel(pw.Model):
        class Meta:
            database = db
    
    class KeyValuePair(BaseModel):
        key = pw.FixedCharField(primary_key=True, max_length=255)
        val = pw.TextField(null=False)
        fup = pw.DateTimeField(
            verbose_name='first_updated', null=False, default=datetime.datetime.now)
        lup = pw.DateTimeField(
            verbose_name='last_updated', null=False, default=datetime.datetime.now)
    
    db.connect()
    db.create_tables([KeyValuePair])
    

When I inspect the SQL produced by the last line I get:

    
    
    CREATE TABLE "keyvaluepair" (
        "key" CHAR(255) NOT NULL PRIMARY KEY,
        "val" TEXT NOT NULL,
        "fup" DATETIME NOT NULL,
        "lup" DATETIME NOT NULL
    );
    

So I have two questions at this point:

  1. I've been unable to find a way to achieve the behavior of the `entry_first_insert` and `entry_last_updated` triggers. Does `peewee` support triggers? If not, is there a way to just create a table from a .sql file rather than the `Model` class definition?
  2. Is there a way to make the default for `fup` and `lup` propogate to the SQL definitions?


Answer: I've figured out a proper answer to both questions. This solution actually
enforces the desired triggers and default timestamps in the SQL DDL.

First we define a convenience class to wrap up the SQL for a trigger. There is
a more proper way to do this with the `peewee.Node` objects, but I didn't have
time to delve into all of that for this project. This `Trigger` class simply
provides string formatting to output proper sql for trigger creation.

    
    
    class Trigger(object):
        """Trigger template wrapper for use with peewee ORM."""
    
        _template = """
        {create} {name} {when} {trigger_op}
        on {tablename}
        begin
            {op} {tablename} {sql} where {pk} = {old_new}.{pk};
        end;
        """
    
        def __init__(self, table, name, when, trigger_op, op, sql, safe=True):
            self.create = 'create trigger' + (' if not exists' if safe else '')
            self.tablename = table._meta.name
            self.pk = table._meta.primary_key.name
            self.name = name
            self.when = when
            self.trigger_op = trigger_op
            self.op = op
            self.sql = sql
            self.old_new = 'new' if trigger_op.lower() == 'insert' else 'old'
    
        def __str__(self):
            return self._template.format(**self.__dict__)
    

Next we define a class `TriggerTable` that inherits from the `BaseModel`. This
class overrides the default `create_table` to follow table creation with
trigger creation. If any triggers fail to create, the whole create is rolled
back.

    
    
    class TriggerTable(BaseModel):
        """Table with triggers."""
    
        @classmethod
        def triggers(cls):
            """Return an iterable of `Trigger` objects to create upon table creation."""
            return tuple()
    
        @classmethod
        def new_trigger(cls, name, when, trigger_op, op, sql):
            """Create a new trigger for this class's table."""
            return Trigger(cls, name, when, trigger_op, op, sql)
    
        @classmethod
        def create_table(cls, fail_silently=False):
            """Create this table in the underlying database."""
            super(TriggerTable, cls).create_table(fail_silently)
            for trigger in cls.triggers():
                try:
                    cls._meta.database.execute_sql(str(trigger))
                except:
                    cls._meta.database.drop_table(cls, fail_silently)
                    raise
    

The next step is to create a class `BetterDateTimeField`. This `Field` object
overrides the default `__ddl__` to append a "DEFAULT current_timestamp" string
if the `default` instance variable is set to the `datetime.datetime.now`
function. There are certainly better ways to do this, but this one captures
the basic use case.

    
    
    class BetterDateTimeField(pw.DateTimeField):
        """Propogate defaults to database layer."""
    
        def __ddl__(self, column_type):
            """Return a list of Node instances that defines the column."""
            ddl = super(BetterDateTimeField, self).__ddl__(column_type)
            if self.default == datetime.datetime.now:
                ddl.append(pw.SQL('DEFAULT current_timestamp'))
            return ddl
    

Finally, we define the new and improved `KeyValuePair` Model, incorporating
our trigger and datetime field improvements. We conclude the Python code by
creating the table.

    
    
    class KeyValuePair(TriggerTable):
        """DurableHashMap entries are key-value pairs."""
    
        key = pw.FixedCharField(primary_key=True, max_length=255)
        val = pw.TextField(null=False)
        fup = BetterDateTimeField(
            verbose_name='first_updated', null=False, default=datetime.datetime.now)
        lup = BetterDateTimeField(
            verbose_name='last_updated', null=False, default=datetime.datetime.now)
    
        @classmethod
        def triggers(cls):
            return (
                cls.new_trigger(
                    'kvp_first_insert', 'after', 'insert', 'update',
                    'set lup = current_timestamp'),
                cls.new_trigger(
                    'kvp_last_udpated', 'after', 'update', 'update',
                    'set lup = current_timestamp')
            )
    
    KeyValuePair.create_table()
    

Now the schema is created properly:

    
    
    sqlite> .schema keyvaluepair
    CREATE TABLE "keyvaluepair" ("key" CHAR(255) NOT NULL PRIMARY KEY, "val" TEXT NOT NULL, "fup" DATETIME NOT NULL DEFAULT current_timestamp, "lup" DATETIME NOT NULL DEFAULT current_timestamp);
    CREATE TRIGGER kvp_first_insert after insert
        on keyvaluepair
        begin
            update keyvaluepair set lup = current_timestamp where key = new.key;
        end;
    CREATE TRIGGER kvp_last_udpated after update
        on keyvaluepair
        begin
            update keyvaluepair set lup = current_timestamp where key = old.key;
        end;
    sqlite> insert into keyvaluepair (key, val) values ('test', 'test-value');
    sqlite> select * from keyvaluepair;
    test|test-value|2015-12-07 21:58:05|2015-12-07 21:58:05
    sqlite> update keyvaluepair set val = 'test-value-two' where key = 'test';
    sqlite> select * from keyvaluepair;
    test|test-value-two|2015-12-07 21:58:05|2015-12-07 21:58:22
    

