Return to mqtt_pub.sql CVS log

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

added new files

    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;