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