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

1.1.2.1   misho       1: PRAGMA foreign_keys=ON;
                      2: BEGIN TRANSACTION;
1.1.2.6 ! misho       3: CREATE TABLE IF NOT EXISTS Online (
1.1.2.1   misho       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);
1.1.2.6 ! misho       8: CREATE TABLE IF NOT EXISTS Topics (
1.1.2.1   misho       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);
1.1.2.6 ! misho      17: CREATE INDEX IF NOT EXISTS RemoteHost on Online (RemoteHost);
        !            18: CREATE INDEX IF NOT EXISTS Username on Online (Username);
        !            19: CREATE UNIQUE INDEX IF NOT EXISTS User on Online (Username, RemoteHost);
        !            20: CREATE INDEX IF NOT EXISTS PubDate on Topics (PubDate);
        !            21: CREATE INDEX IF NOT EXISTS PubHost on Topics (PubHost);
        !            22: CREATE INDEX IF NOT EXISTS PubUser on Topics (PubUser);
        !            23: CREATE INDEX IF NOT EXISTS Topic on Topics (Topic);
        !            24: CREATE TRIGGER IF NOT EXISTS Online_update_t AFTER UPDATE ON Online
1.1.2.5   misho      25: BEGIN
                     26:        UPDATE Online SET Stamp = strftime("%s", "now") WHERE Username = old.Username;
                     27: END;
1.1.2.6 ! misho      28: CREATE TRIGGER IF NOT EXISTS Online_insert_t AFTER INSERT ON Online
1.1.2.5   misho      29: BEGIN
                     30:        UPDATE Online SET Stamp = strftime("%s", "now") WHERE Username = new.Username;
                     31: END;
1.1.2.6 ! misho      32: CREATE TRIGGER IF NOT EXISTS Topics_update_t AFTER UPDATE ON Topics
1.1.2.5   misho      33: BEGIN
                     34:        UPDATE Topics SET Stamp = strftime("%s", "now") WHERE id = old.id;
                     35: END;
1.1.2.6 ! misho      36: CREATE TRIGGER IF NOT EXISTS Topics_insert_t AFTER INSERT ON Topics
1.1.2.5   misho      37: BEGIN
                     38:        UPDATE Topics SET Stamp = strftime("%s", "now") WHERE id = new.id;
                     39: END;
1.1.2.1   misho      40: COMMIT;