Return to mqtt_pub.sql CVS log

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

version 1.1

    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: ConnID varchar(23) not null, 
   16: MsgID smallint unsigned not null, 
   17: QoS char not null,
   18: Retain char not null,
   19: Topic varchar(255) not null,
   20: Value blob,
   21: PubUser varchar(64) not null,
   22: PubDate datetime not null,
   23: PubHost varchar(64) not null,
   24: Stamp timestamp);
   25: CREATE TABLE IF NOT EXISTS Subscribes (
   26: id integer not null primary key,
   27: ConnID varchar(23) not null, 
   28: MsgID smallint unsigned not null, 
   29: QoS char not null,
   30: Topic varchar(255) not null,
   31: PubUser varchar(64) not null,
   32: PubDate datetime not null,
   33: PubHost varchar(64) not null,
   34: Stamp timestamp);
   35: CREATE INDEX IF NOT EXISTS online_RemoteHost on Online (RemoteHost);
   36: CREATE INDEX IF NOT EXISTS online_Username on Online (Username);
   37: CREATE INDEX IF NOT EXISTS topics_PubDate on Topics (PubDate);
   38: CREATE INDEX IF NOT EXISTS topics_PubHost on Topics (PubHost);
   39: CREATE INDEX IF NOT EXISTS topics_PubUser on Topics (PubUser);
   40: CREATE INDEX IF NOT EXISTS topics_MsgID on Topics (MsgID);
   41: CREATE INDEX IF NOT EXISTS topics_Topic on Topics (Topic);
   42: CREATE INDEX IF NOT EXISTS topics_ConnID on Topics (ConnID);
   43: CREATE INDEX IF NOT EXISTS subscribes_PubDate on Subscribes (PubDate);
   44: CREATE INDEX IF NOT EXISTS subscribes_PubHost on Subscribes (PubHost);
   45: CREATE INDEX IF NOT EXISTS subscribes_PubUser on Subscribes (PubUser);
   46: CREATE INDEX IF NOT EXISTS subscribes_MsgID on Subscribes (MsgID);
   47: CREATE INDEX IF NOT EXISTS subscribes_Topic on Subscribes (Topic);
   48: CREATE INDEX IF NOT EXISTS subscribes_ConnID on Subscribes (ConnID);
   49: CREATE TRIGGER IF NOT EXISTS Online_update_t AFTER UPDATE ON Online
   50: BEGIN
   51: 	UPDATE Online SET Stamp = strftime('%s', 'now') WHERE ConnID = old.ConnID;
   52: END;
   53: CREATE TRIGGER IF NOT EXISTS Online_insert_t AFTER INSERT ON Online
   54: BEGIN
   55: 	UPDATE Online SET Stamp = strftime('%s', 'now') WHERE ConnID = new.ConnID;
   56: END;
   57: CREATE TRIGGER IF NOT EXISTS Topics_update_t AFTER UPDATE ON Topics
   58: BEGIN
   59: 	UPDATE Topics SET Stamp = strftime('%s', 'now') WHERE id = old.id;
   60: END;
   61: CREATE TRIGGER IF NOT EXISTS Topics_insert_t AFTER INSERT ON Topics
   62: BEGIN
   63: 	UPDATE Topics SET Stamp = strftime('%s', 'now') WHERE id = new.id;
   64: END;
   65: CREATE TRIGGER IF NOT EXISTS Subscribes_update_t AFTER UPDATE ON Subscribes
   66: BEGIN
   67: 	UPDATE Subscribes SET Stamp = strftime('%s', 'now') WHERE id = old.id;
   68: END;
   69: CREATE TRIGGER IF NOT EXISTS Subscribes_insert_t AFTER INSERT ON Subscribes
   70: BEGIN
   71: 	UPDATE Subscribes SET Stamp = strftime('%s', 'now') WHERE id = new.id;
   72: END;
   73: COMMIT;