PRAGMA foreign_keys=ON;
BEGIN TRANSACTION;
CREATE TABLE IF NOT EXISTS Online (
Username varchar(64) not null,
RemoteHost varchar(64) not null,
RemotePort smallint unsigned not null,
Stamp timestamp);
CREATE TABLE IF NOT EXISTS 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 IF NOT EXISTS RemoteHost on Online (RemoteHost);
CREATE INDEX IF NOT EXISTS Username on Online (Username);
CREATE UNIQUE INDEX IF NOT EXISTS User on Online (Username, RemoteHost);
CREATE INDEX IF NOT EXISTS PubDate on Topics (PubDate);
CREATE INDEX IF NOT EXISTS PubHost on Topics (PubHost);
CREATE INDEX IF NOT EXISTS PubUser on Topics (PubUser);
CREATE INDEX IF NOT EXISTS Topic on Topics (Topic);
CREATE TRIGGER IF NOT EXISTS Online_update_t AFTER UPDATE ON Online
BEGIN
	UPDATE Online SET Stamp = strftime('%s', 'now') WHERE Username = old.Username;
END;
CREATE TRIGGER IF NOT EXISTS Online_insert_t AFTER INSERT ON Online
BEGIN
	UPDATE Online SET Stamp = strftime('%s', 'now') WHERE Username = new.Username;
END;
CREATE TRIGGER IF NOT EXISTS Topics_update_t AFTER UPDATE ON Topics
BEGIN
	UPDATE Topics SET Stamp = strftime('%s', 'now') WHERE id = old.id;
END;
CREATE TRIGGER IF NOT EXISTS Topics_insert_t AFTER INSERT ON Topics
BEGIN
	UPDATE Topics SET Stamp = strftime('%s', 'now') WHERE id = new.id;
END;
COMMIT;