Annotation of mqtt/etc/mqtt_pub.sql, revision 1.2.2.1
1.2 misho 1: PRAGMA foreign_keys=ON;
2: BEGIN TRANSACTION;
3: CREATE TABLE IF NOT EXISTS Online (
4: ConnID varchar(23) not null primary key,
5: Username varchar(12) not null,
6: RemoteHost varchar(64) not null,
7: WillFlag tinyint not null,
8: WillTopic text,
9: WillMsg text,
10: WillQoS tinyint,
11: WillRetain tinyint,
12: Stamp timestamp);
13: CREATE TABLE IF NOT EXISTS Topics (
14: id integer not null primary key,
15: MsgID smallint unsigned not null,
16: QoS char not null,
17: Retain char not null,
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.2.2.1 ! misho 24: CREATE TABLE IF NOT EXISTS Subscribes (
! 25: id integer not null primary key,
! 26: MsgID smallint unsigned not null,
! 27: QoS char not null,
! 28: Topic varchar(255) not null,
! 29: PubUser varchar(64) not null,
! 30: PubDate datetime not null,
! 31: PubHost varchar(64) not null,
! 32: Stamp timestamp);
! 33: CREATE INDEX IF NOT EXISTS online_RemoteHost on Online (RemoteHost);
! 34: CREATE INDEX IF NOT EXISTS online_Username on Online (Username);
! 35: CREATE INDEX IF NOT EXISTS topics_PubDate on Topics (PubDate);
! 36: CREATE INDEX IF NOT EXISTS topics_PubHost on Topics (PubHost);
! 37: CREATE INDEX IF NOT EXISTS topics_PubUser on Topics (PubUser);
! 38: CREATE INDEX IF NOT EXISTS topics_MsgID on Topics (MsgID);
! 39: CREATE INDEX IF NOT EXISTS topics_Topic on Topics (Topic);
! 40: CREATE INDEX IF NOT EXISTS subscribes_PubDate on Subscribes (PubDate);
! 41: CREATE INDEX IF NOT EXISTS subscribes_PubHost on Subscribes (PubHost);
! 42: CREATE INDEX IF NOT EXISTS subscribes_PubUser on Subscribes (PubUser);
! 43: CREATE INDEX IF NOT EXISTS subscribes_MsgID on Subscribes (MsgID);
! 44: CREATE INDEX IF NOT EXISTS subscribes_Topic on Subscribes (Topic);
1.2 misho 45: CREATE TRIGGER IF NOT EXISTS Online_update_t AFTER UPDATE ON Online
46: BEGIN
47: UPDATE Online SET Stamp = strftime('%s', 'now') WHERE ConnID = old.ConnID;
48: END;
49: CREATE TRIGGER IF NOT EXISTS Online_insert_t AFTER INSERT ON Online
50: BEGIN
51: UPDATE Online SET Stamp = strftime('%s', 'now') WHERE ConnID = new.ConnID;
52: END;
53: CREATE TRIGGER IF NOT EXISTS Topics_update_t AFTER UPDATE ON Topics
54: BEGIN
55: UPDATE Topics SET Stamp = strftime('%s', 'now') WHERE id = old.id;
56: END;
57: CREATE TRIGGER IF NOT EXISTS Topics_insert_t AFTER INSERT ON Topics
58: BEGIN
59: UPDATE Topics SET Stamp = strftime('%s', 'now') WHERE id = new.id;
60: END;
1.2.2.1 ! misho 61: CREATE TRIGGER IF NOT EXISTS Subscribes_update_t AFTER UPDATE ON Subscribes
! 62: BEGIN
! 63: UPDATE Subscribes SET Stamp = strftime('%s', 'now') WHERE id = old.id;
! 64: END;
! 65: CREATE TRIGGER IF NOT EXISTS Subscribes_insert_t AFTER INSERT ON Subscribes
! 66: BEGIN
! 67: UPDATE Subscribes SET Stamp = strftime('%s', 'now') WHERE id = new.id;
! 68: END;
1.2 misho 69: COMMIT;
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>