lundi 7 décembre 2015

SQLite triggers using Peewee in Python

I have a SQLite table defined like so:

create table if not exists Entry (
    key         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 Entry
begin
    update Entry set lup = current_timestamp where key = new.key;
end;

create trigger if not exists entry_last_updated after update of value
on Entry
begin
    update Entry 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.PrimaryKeyField()
    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" INTEGER 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?

Aucun commentaire:

Enregistrer un commentaire