
DROP TABLE IF EXISTS pictures;
CREATE TABLE pictures (
    [imageid]   INTEGER NOT NULL PRIMARY KEY,
    [mime]      VARCHAR(256) NOT NULL,
    [txtinfo]   VARCHAR(256) DEFAULT NULL,
    [reference] VARCHAR(1024) NOT NULL,
    [filename]  VARCHAR(1024) NOT NULL,
    [data]      BLOB,
    [metadata]  TEXT DEFAULT '',
    [refdate]   TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    [created]   TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    [updated]   TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TRIGGER update_image_timestamp AFTER UPDATE ON pictures
BEGIN
UPDATE pictures SET updated=datetime('now') where (imageid=new.imageid OR reference=new.reference);
END;
