-
Notifications
You must be signed in to change notification settings - Fork 0
/
initialize-database.sql
executable file
·37 lines (34 loc) · 1.34 KB
/
initialize-database.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
CREATE TABLE IF NOT EXISTS streams (
id VARCHAR(500) NOT NULL PRIMARY KEY,
description TEXT,
peerCount INTEGER UNSIGNED NOT NULL,
messagesPerSecond DECIMAL(8,2) UNSIGNED NOT NULL,
bytesPerSecond DECIMAL(16,2) UNSIGNED NOT NULL,
publisherCount INTEGER UNSIGNED, -- NULL if stream has public publish permission
subscriberCount INTEGER UNSIGNED, -- NULL if stream has public subscribe permission
crawlTimestamp DATETIME NOT NULL,
INDEX streams_peerCount (peerCount),
INDEX streams_description (description(100)),
INDEX streams_messagesPerSecond (messagesPerSecond),
INDEX streams_bytesPerSecond (bytesPerSecond),
INDEX streams_publisherCount (publisherCount),
INDEX streams_subscriberCount (subscriberCount)
);
CREATE TABLE IF NOT EXISTS sample_messages (
streamId VARCHAR(500) NOT NULL PRIMARY KEY,
content MEDIUMBLOB NOT NULL,
contentType VARCHAR(50) NOT NULL
);
CREATE TABLE IF NOT EXISTS nodes (
id CHAR(40) NOT NULL PRIMARY KEY,
ipAddress VARCHAR(15)
);
CREATE TABLE IF NOT EXISTS neighbors (
streamPartId VARCHAR(500) NOT NULL,
nodeId1 CHAR(40) NOT NULL,
nodeId2 CHAR(40) NOT NULL,
PRIMARY KEY (streamPartId, nodeId1, nodeId2),
FOREIGN KEY (nodeId1) REFERENCES nodes(id),
FOREIGN KEY (nodeId2) REFERENCES nodes(id),
INDEX neighbors_streamPartId (streamPartId)
);