Return to mqtt_pub.sql CVS log

Up to [ELWIX - Embedded LightWeight unIX -] / mqtt / etc

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;