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