Annotation of mqtt/etc/mqtt_pub.sql, revision 1.1.2.7
1.1.2.1 misho 1: PRAGMA foreign_keys=ON;
2: BEGIN TRANSACTION;
1.1.2.6 misho 3: CREATE TABLE IF NOT EXISTS Online (
1.1.2.1 misho 4: Username varchar(64) not null,
5: RemoteHost varchar(64) not null,
1.1.2.3 misho 6: RemotePort smallint unsigned not null,
1.1.2.1 misho 7: Stamp timestamp);
1.1.2.6 misho 8: CREATE TABLE IF NOT EXISTS Topics (
1.1.2.1 misho 9: id integer not null primary key,
1.1.2.2 misho 10: Retain char not null,
1.1.2.1 misho 11: Topic varchar(255) not null,
12: Value text,
13: PubUser varchar(64) not null,
14: PubDate datetime not null,
15: PubHost varchar(64) not null,
16: Stamp timestamp);
1.1.2.6 misho 17: CREATE INDEX IF NOT EXISTS RemoteHost on Online (RemoteHost);
18: CREATE INDEX IF NOT EXISTS Username on Online (Username);
19: CREATE UNIQUE INDEX IF NOT EXISTS User on Online (Username, RemoteHost);
20: CREATE INDEX IF NOT EXISTS PubDate on Topics (PubDate);
21: CREATE INDEX IF NOT EXISTS PubHost on Topics (PubHost);
22: CREATE INDEX IF NOT EXISTS PubUser on Topics (PubUser);
23: CREATE INDEX IF NOT EXISTS Topic on Topics (Topic);
24: CREATE TRIGGER IF NOT EXISTS Online_update_t AFTER UPDATE ON Online
1.1.2.5 misho 25: BEGIN
1.1.2.7 ! misho 26: UPDATE Online SET Stamp = strftime('%s', 'now') WHERE Username = old.Username;
1.1.2.5 misho 27: END;
1.1.2.6 misho 28: CREATE TRIGGER IF NOT EXISTS Online_insert_t AFTER INSERT ON Online
1.1.2.5 misho 29: BEGIN
1.1.2.7 ! misho 30: UPDATE Online SET Stamp = strftime('%s', 'now') WHERE Username = new.Username;
1.1.2.5 misho 31: END;
1.1.2.6 misho 32: CREATE TRIGGER IF NOT EXISTS Topics_update_t AFTER UPDATE ON Topics
1.1.2.5 misho 33: BEGIN
1.1.2.7 ! misho 34: UPDATE Topics SET Stamp = strftime('%s', 'now') WHERE id = old.id;
1.1.2.5 misho 35: END;
1.1.2.6 misho 36: CREATE TRIGGER IF NOT EXISTS Topics_insert_t AFTER INSERT ON Topics
1.1.2.5 misho 37: BEGIN
1.1.2.7 ! misho 38: UPDATE Topics SET Stamp = strftime('%s', 'now') WHERE id = new.id;
1.1.2.5 misho 39: END;
1.1.2.1 misho 40: COMMIT;
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>