Return to mqtt_pub.sql CVS log

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

Annotation of mqtt/etc/mqtt_pub.sql, revision 1.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.3     ! 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.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.3     ! misho      25: CREATE TABLE IF NOT EXISTS Subscribes (
        !            26: id integer not null primary key,
        !            27: ConnID varchar(23) not null, 
        !            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);
        !            42: CREATE INDEX IF NOT EXISTS topics_ConnID on Topics (ConnID);
        !            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);
        !            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.3     ! 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;