Return to mqtt_pub.sql CVS log

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

Annotation of mqtt/etc/mqtt_pub.sql, revision 1.2.2.3

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,
1.2.2.2   misho      15: ConnID varchar(23) not null, 
1.2       misho      16: MsgID smallint unsigned not null, 
                     17: QoS char not null,
                     18: Retain char not null,
                     19: Topic varchar(255) not null,
1.2.2.3 ! misho      20: Value blob,
1.2       misho      21: PubUser varchar(64) not null,
                     22: PubDate datetime not null,
                     23: PubHost varchar(64) not null,
                     24: Stamp timestamp);
1.2.2.1   misho      25: CREATE TABLE IF NOT EXISTS Subscribes (
                     26: id integer not null primary key,
1.2.2.2   misho      27: ConnID varchar(23) not null, 
1.2.2.1   misho      28: MsgID smallint unsigned not null, 
                     29: QoS char not null,
                     30: Topic varchar(255) not null,
                     31: PubUser varchar(64) not null,
                     32: PubDate datetime not null,
                     33: PubHost varchar(64) not null,
                     34: Stamp timestamp);
                     35: CREATE INDEX IF NOT EXISTS online_RemoteHost on Online (RemoteHost);
                     36: CREATE INDEX IF NOT EXISTS online_Username on Online (Username);
                     37: CREATE INDEX IF NOT EXISTS topics_PubDate on Topics (PubDate);
                     38: CREATE INDEX IF NOT EXISTS topics_PubHost on Topics (PubHost);
                     39: CREATE INDEX IF NOT EXISTS topics_PubUser on Topics (PubUser);
                     40: CREATE INDEX IF NOT EXISTS topics_MsgID on Topics (MsgID);
                     41: CREATE INDEX IF NOT EXISTS topics_Topic on Topics (Topic);
1.2.2.2   misho      42: CREATE INDEX IF NOT EXISTS topics_ConnID on Topics (ConnID);
1.2.2.1   misho      43: CREATE INDEX IF NOT EXISTS subscribes_PubDate on Subscribes (PubDate);
                     44: CREATE INDEX IF NOT EXISTS subscribes_PubHost on Subscribes (PubHost);
                     45: CREATE INDEX IF NOT EXISTS subscribes_PubUser on Subscribes (PubUser);
                     46: CREATE INDEX IF NOT EXISTS subscribes_MsgID on Subscribes (MsgID);
                     47: CREATE INDEX IF NOT EXISTS subscribes_Topic on Subscribes (Topic);
1.2.2.2   misho      48: CREATE INDEX IF NOT EXISTS subscribes_ConnID on Subscribes (ConnID);
1.2       misho      49: CREATE TRIGGER IF NOT EXISTS Online_update_t AFTER UPDATE ON Online
                     50: BEGIN
                     51:        UPDATE Online SET Stamp = strftime('%s', 'now') WHERE ConnID = old.ConnID;
                     52: END;
                     53: CREATE TRIGGER IF NOT EXISTS Online_insert_t AFTER INSERT ON Online
                     54: BEGIN
                     55:        UPDATE Online SET Stamp = strftime('%s', 'now') WHERE ConnID = new.ConnID;
                     56: END;
                     57: CREATE TRIGGER IF NOT EXISTS Topics_update_t AFTER UPDATE ON Topics
                     58: BEGIN
                     59:        UPDATE Topics SET Stamp = strftime('%s', 'now') WHERE id = old.id;
                     60: END;
                     61: CREATE TRIGGER IF NOT EXISTS Topics_insert_t AFTER INSERT ON Topics
                     62: BEGIN
                     63:        UPDATE Topics SET Stamp = strftime('%s', 'now') WHERE id = new.id;
                     64: END;
1.2.2.1   misho      65: CREATE TRIGGER IF NOT EXISTS Subscribes_update_t AFTER UPDATE ON Subscribes
                     66: BEGIN
                     67:        UPDATE Subscribes SET Stamp = strftime('%s', 'now') WHERE id = old.id;
                     68: END;
                     69: CREATE TRIGGER IF NOT EXISTS Subscribes_insert_t AFTER INSERT ON Subscribes
                     70: BEGIN
                     71:        UPDATE Subscribes SET Stamp = strftime('%s', 'now') WHERE id = new.id;
                     72: END;
1.2       misho      73: COMMIT;