PRAGMA foreign_keys=ON;
BEGIN TRANSACTION;
CREATE TABLE Online (
Username varchar(64) not null,
RemoteHost varchar(64) not null,
RemotePort smallint unsigned not null,
Stamp timestamp);
CREATE TABLE Topics (
id integer not null primary key,
Retain char not null,
Topic varchar(255) not null,
Value text,
PubUser varchar(64) not null,
PubDate datetime not null,
PubHost varchar(64) not null,
Stamp timestamp);
CREATE INDEX RemoteHost on Online (RemoteHost);
CREATE INDEX Username on Online (Username);
CREATE UNIQUE INDEX User on Online (Username, RemoteHost);
CREATE INDEX PubDate on Topics (PubDate);
CREATE INDEX PubHost on Topics (PubHost);
CREATE INDEX PubUser on Topics (PubUser);
CREATE INDEX Topic on Topics (Topic);
CREATE TRIGGER Online_update_t AFTER UPDATE ON Online
BEGIN
	UPDATE Online SET Stamp = strftime("%s", "now") WHERE Username = old.Username;
END;
CREATE TRIGGER Online_insert_t AFTER INSERT ON Online
BEGIN
	UPDATE Online SET Stamp = strftime("%s", "now") WHERE Username = new.Username;
END;
CREATE TRIGGER Topics_update_t AFTER UPDATE ON Topics
BEGIN
	UPDATE Topics SET Stamp = strftime("%s", "now") WHERE id = old.id;
END;
CREATE TRIGGER Topics_insert_t AFTER INSERT ON Topics
BEGIN
	UPDATE Topics SET Stamp = strftime("%s", "now") WHERE id = new.id;
END;
COMMIT;