File:
[ELWIX - Embedded LightWeight unIX -] /
mqtt /
etc /
mqtt_pub.sql
Revision
1.3:
download - view:
text,
annotated -
select for diffs -
revision graph
Tue Jul 3 09:02:49 2012 UTC (12 years, 1 month ago) by
misho
Branches:
MAIN
CVS tags:
mqtt2_1,
mqtt2_0,
mqtt1_3,
mqtt1_2,
MQTT2_0,
MQTT1_3,
MQTT1_2,
MQTT1_1,
HEAD
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;
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>