Backend server uses SQL DB to store stream history. It is recommended to use Postgresql. Embedded H2 DB is allowed for debug and testing purposes, but it should not be used in production.
DB structure
Backend server SQL DB contains the following tables which are created automatically on first launch:
Field | Type | Note |
---|---|---|
ALARM | ||
id | bigint | PRIMARY KEY |
name | character varying(255) | UNIQUE CONSTRAINT |
time | bigint | |
type | bigint | |
value | bigint | |
metric_id | bigint | FOREIGN KEY REFERENCES metric(id) |
node_id | bigint | FOREIGN KEY REFERENCES node(id) |
METRIC | ||
id | bigint | PRIMARY KEY |
enum_name | character varying(255) | UNIQUE CONSTRAINT |
name | character varying(255) | UNIQUE CONSTRAINT |
note | character varying(255) | |
NODE | ||
id | bigint | PRIMARY KEY |
host | character varying(255) | UNIQUE CONSTRAINT |
NODE_PROFILE | ||
node_id | bigint | FOREIGN KEY REFERENCES node(id) |
profile_id | bigint | FOREIGN KEY REFERENCES profile(id) |
PROFILE | ||
id | bigint | PRIMARY KEY |
name | character varying(255) | UNIQUE CONSTRAINT |
rate | integer | |
PROFILE_METRICS | ||
profile_id | bigint | FOREIGN KEY REFERENCES profile(id) |
metrics_id | bigint | FOREIGN KEY REFERENCES metric(id) |
PROFILE_RULE | ||
profile_id | bigint | FOREIGN KEY REFERENCES profile(id) |
rule_id | bigint | FOREIGN KEY REFERENCES rule(id) |
RULE | ||
id | bigint | PRIMARY KEY |
name | character varying(255) | UNIQUE CONSTRAINT |
type | bigint | |
value | character varying(255) | |
STREAM | ||
id | bigint | PRIMARY KEY |
create_date | timestamp without time zone | |
duration | bigint | |
media_id | character varying(255) | |
name | character varying(255) | |
node_id | bigint | FOREIGN KEY REFERENCES node(id) |
publisher_id | bigint | FOREIGN KEY REFERENCES stream(id) |
STREAM_STATUS | ||
id | bigint | PRIMARY KEY |
status | character varying(255) | |
timestamp | timestamp without time zone | |
stream_id | bigint | FOREIGN KEY REFERENCES stream(id) |
Postgresql installation and setup
1. Install Postgresql (for example, on CentOS 7) with command
yum install postgresql-server
2. Initialize DB and start Postgresql
postgresql-setup initdb systemctl enable postgresql systemctl start postgresql
3. Enter Postgresql console
su - postgres psql
4. Create database and user, grant privileges to the user
CREATE USER wcsoam WITH PASSWORD 'wcsoam'; CREATE DATABASE wcsoam; \c wcsoam GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO "wcsoam";
5. Exit Postgresql console
\q exit