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

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;