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