Return to mqtt_db.sql CVS log

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

add sqlite3 database for mqtt

PRAGMA foreign_keys=ON;
BEGIN TRANSACTION;
CREATE TABLE Users (
uid integer primary key AUTOINCREMENT,
Username varchar(64) not null,
Password varchar(64) not null,
Access integer not null,
Stamp timestamp);
CREATE TABLE Online (
Username varchar(64) not null,
RemoteHost varchar(64) not null,
RepotePort smallint not null,
Stamp timestamp,
foreign key (Username) references Users (Username));
CREATE TABLE Topics (
id integer not null primary key,
Topic varchar(255) not null,
Value text,
PubUser varchar(64) not null,
PubDate datetime not null,
PubHost varchar(64) not null,
Stamp timestamp,
foreign key (PubUser) references Users (Username));
CREATE UNIQUE INDEX User on Users (UserName);
CREATE INDEX RemoteHost on Online (RemoteHost);
CREATE INDEX Username on Online (Username);
CREATE INDEX PubDate on Topics (PubDate);
CREATE INDEX PubHost on Topics (PubHost);
CREATE INDEX PubUser on Topics (PubUser);
CREATE INDEX Topic on Topics (Topic);
COMMIT;