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

1.1.2.1   misho       1: PRAGMA foreign_keys=ON;
                      2: BEGIN TRANSACTION;
                      3: CREATE TABLE Online (
                      4: Username varchar(64) not null,
                      5: RemoteHost varchar(64) not null,
1.1.2.3   misho       6: RemotePort smallint unsigned not null,
1.1.2.1   misho       7: Stamp timestamp);
                      8: CREATE TABLE Topics (
                      9: id integer not null primary key,
1.1.2.2   misho      10: Retain char not null,
1.1.2.1   misho      11: Topic varchar(255) not null,
                     12: Value text,
                     13: PubUser varchar(64) not null,
                     14: PubDate datetime not null,
                     15: PubHost varchar(64) not null,
                     16: Stamp timestamp);
                     17: CREATE INDEX RemoteHost on Online (RemoteHost);
                     18: CREATE INDEX Username on Online (Username);
1.1.2.4   misho      19: CREATE UNIQUE INDEX User on Online (Username, RemoteHost);
1.1.2.1   misho      20: CREATE INDEX PubDate on Topics (PubDate);
                     21: CREATE INDEX PubHost on Topics (PubHost);
                     22: CREATE INDEX PubUser on Topics (PubUser);
                     23: CREATE INDEX Topic on Topics (Topic);
1.1.2.5 ! misho      24: CREATE TRIGGER Online_update_t AFTER UPDATE ON Online
        !            25: BEGIN
        !            26:        UPDATE Online SET Stamp = strftime("%s", "now") WHERE Username = old.Username;
        !            27: END;
        !            28: CREATE TRIGGER Online_insert_t AFTER INSERT ON Online
        !            29: BEGIN
        !            30:        UPDATE Online SET Stamp = strftime("%s", "now") WHERE Username = new.Username;
        !            31: END;
        !            32: CREATE TRIGGER Topics_update_t AFTER UPDATE ON Topics
        !            33: BEGIN
        !            34:        UPDATE Topics SET Stamp = strftime("%s", "now") WHERE id = old.id;
        !            35: END;
        !            36: CREATE TRIGGER Topics_insert_t AFTER INSERT ON Topics
        !            37: BEGIN
        !            38:        UPDATE Topics SET Stamp = strftime("%s", "now") WHERE id = new.id;
        !            39: END;
1.1.2.1   misho      40: COMMIT;