

DROP TABLE IF EXISTS contents;
CREATE TABLE contents (
    [contentid]   INTEGER NOT NULL PRIMARY KEY,
    [content]     TEXT DEFAULT NULL,
    [title]       VARCHAR(256) DEFAULT NULL,
    [type]        VARCHAR(64) NOT NULL,
    [metadata]    TEXT DEFAULT NULL,
    [mime]        VARCHAR(256) DEFAULT 'text/html',
    [reference]   VARCHAR(1024) NOT NULL,
    [decoration]  VARCHAR(1024) DEFAULT NULL,
    [attachment]  VARCHAR(1024) DEFAULT NULL,
    [author]      VARCHAR(1024) DEFAULT NULL,
    [refdate]     DATE DEFAULT NULL,
    [created]     TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    [updated]     TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TRIGGER update_content_timestamp AFTER UPDATE ON contents 
BEGIN
UPDATE contents SET updated=datetime('now') WHERE (contentid=new.contentid OR reference=new.reference);
END;
