Return to mqtt_pub.sql CVS log

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

add new sql field

PRAGMA foreign_keys=ON;
BEGIN TRANSACTION;
CREATE TABLE IF NOT EXISTS Online (
ConnID varchar(23) not null primary key, 
Username varchar(12) not null,
RemoteHost varchar(64) not null,
WillFlag tinyint not null,
WillTopic text,
WillMsg text,
WillQoS tinyint,
WillRetain tinyint,
Stamp timestamp);
CREATE TABLE IF NOT EXISTS Topics (
id integer not null primary key,
ConnID varchar(23) not null, 
MsgID smallint unsigned not null, 
QoS char not null,
Retain char not null,
Topic varchar(255) not null,
Value text,
PubUser varchar(64) not null,
PubDate datetime not null,
PubHost varchar(64) not null,
Stamp timestamp);
CREATE TABLE IF NOT EXISTS Subscribes (
id integer not null primary key,
ConnID varchar(23) not null, 
MsgID smallint unsigned not null, 
QoS char not null,
Topic varchar(255) not null,
PubUser varchar(64) not null,
PubDate datetime not null,
PubHost varchar(64) not null,
Stamp timestamp);
CREATE INDEX IF NOT EXISTS online_RemoteHost on Online (RemoteHost);
CREATE INDEX IF NOT EXISTS online_Username on Online (Username);
CREATE INDEX IF NOT EXISTS topics_PubDate on Topics (PubDate);
CREATE INDEX IF NOT EXISTS topics_PubHost on Topics (PubHost);
CREATE INDEX IF NOT EXISTS topics_PubUser on Topics (PubUser);
CREATE INDEX IF NOT EXISTS topics_MsgID on Topics (MsgID);
CREATE INDEX IF NOT EXISTS topics_Topic on Topics (Topic);
CREATE INDEX IF NOT EXISTS topics_ConnID on Topics (ConnID);
CREATE INDEX IF NOT EXISTS subscribes_PubDate on Subscribes (PubDate);
CREATE INDEX IF NOT EXISTS subscribes_PubHost on Subscribes (PubHost);
CREATE INDEX IF NOT EXISTS subscribes_PubUser on Subscribes (PubUser);
CREATE INDEX IF NOT EXISTS subscribes_MsgID on Subscribes (MsgID);
CREATE INDEX IF NOT EXISTS subscribes_Topic on Subscribes (Topic);
CREATE INDEX IF NOT EXISTS subscribes_ConnID on Subscribes (ConnID);
CREATE TRIGGER IF NOT EXISTS Online_update_t AFTER UPDATE ON Online
BEGIN
	UPDATE Online SET Stamp = strftime('%s', 'now') WHERE ConnID = old.ConnID;
END;
CREATE TRIGGER IF NOT EXISTS Online_insert_t AFTER INSERT ON Online
BEGIN
	UPDATE Online SET Stamp = strftime('%s', 'now') WHERE ConnID = new.ConnID;
END;
CREATE TRIGGER IF NOT EXISTS Topics_update_t AFTER UPDATE ON Topics
BEGIN
	UPDATE Topics SET Stamp = strftime('%s', 'now') WHERE id = old.id;
END;
CREATE TRIGGER IF NOT EXISTS Topics_insert_t AFTER INSERT ON Topics
BEGIN
	UPDATE Topics SET Stamp = strftime('%s', 'now') WHERE id = new.id;
END;
CREATE TRIGGER IF NOT EXISTS Subscribes_update_t AFTER UPDATE ON Subscribes
BEGIN
	UPDATE Subscribes SET Stamp = strftime('%s', 'now') WHERE id = old.id;
END;
CREATE TRIGGER IF NOT EXISTS Subscribes_insert_t AFTER INSERT ON Subscribes
BEGIN
	UPDATE Subscribes SET Stamp = strftime('%s', 'now') WHERE id = new.id;
END;
COMMIT;