使用Python中的Peewee在sqlddl中使用SQLite触发器和日期时间默认值

Question

我有一个SQLite表定义如下：

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;

我试图用Python为这个表写一个peewee.Model。到目前为止，我得到的是：

^{pr2}$

当我检查最后一行生成的SQL时，我得到：

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

所以我现在有两个问题：

1. 我无法找到实现entry_first_insert和entry_last_updated触发器行为的方法。peewee是否支持触发器？如果不是，有没有办法只从.sql文件而不是Model类定义创建表？在
2. 有没有办法使fup和lup的默认值分配给SQL定义吗？在

Answer 1

这两个问题我都想好了。此解决方案实际上在sqlddl中强制使用所需的触发器和默认时间戳。

首先，我们定义一个便利类来包装触发器的SQL。对于peewee.Node对象，有一种更合适的方法来实现这一点，但我没有时间为这个项目深入研究所有这些内容。这个Trigger类只提供字符串格式，以输出正确的sql来创建触发器。

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

接下来，我们定义一个继承BaseModel的类TriggerTable。这个类重写默认的create_table，以便在创建表之后创建触发器。如果任何触发器未能创建，则整个创建操作都将回滚。

^{pr2}$

下一步是创建一个类BetterDateTimeField。如果default实例变量设置为datetime.datetime.now函数，则此Field对象重写默认的__ddl__以附加一个“default current}时间戳”字符串。当然有更好的方法可以做到这一点，但这一个抓住了基本的用例。

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

最后，我们定义了新的和改进的KeyValuePair模型，其中包含了触发器和日期时间字段的改进。我们通过创建表来结束Python代码。

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

现在模式已正确创建：

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