Annotation of mqtt/etc/mqtt_pub.sql, revision 1.1.2.9
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.9 ! misho 26: CREATE UNIQUE INDEX IF NOT EXISTS ConnID on Online (ConnID);
1.1.2.6 misho 27: CREATE INDEX IF NOT EXISTS PubDate on Topics (PubDate);
28: CREATE INDEX IF NOT EXISTS PubHost on Topics (PubHost);
29: CREATE INDEX IF NOT EXISTS PubUser on Topics (PubUser);
1.1.2.8 misho 30: CREATE INDEX IF NOT EXISTS MsgID on Topics (MsgID);
1.1.2.6 misho 31: CREATE INDEX IF NOT EXISTS Topic on Topics (Topic);
32: CREATE TRIGGER IF NOT EXISTS Online_update_t AFTER UPDATE ON Online
1.1.2.5 misho 33: BEGIN
1.1.2.9 ! misho 34: UPDATE Online SET Stamp = strftime('%s', 'now') WHERE ConnID = old.ConnID;
1.1.2.5 misho 35: END;
1.1.2.6 misho 36: CREATE TRIGGER IF NOT EXISTS Online_insert_t AFTER INSERT ON Online
1.1.2.5 misho 37: BEGIN
1.1.2.9 ! misho 38: UPDATE Online SET Stamp = strftime('%s', 'now') WHERE ConnID = new.ConnID;
1.1.2.5 misho 39: END;
1.1.2.6 misho 40: CREATE TRIGGER IF NOT EXISTS Topics_update_t AFTER UPDATE ON Topics
1.1.2.5 misho 41: BEGIN
1.1.2.7 misho 42: UPDATE Topics SET Stamp = strftime('%s', 'now') WHERE id = old.id;
1.1.2.5 misho 43: END;
1.1.2.6 misho 44: CREATE TRIGGER IF NOT EXISTS Topics_insert_t AFTER INSERT ON Topics
1.1.2.5 misho 45: BEGIN
1.1.2.7 misho 46: UPDATE Topics SET Stamp = strftime('%s', 'now') WHERE id = new.id;
1.1.2.5 misho 47: END;
1.1.2.1 misho 48: COMMIT;
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>