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