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.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;