Stream history storage in SQL DB¶

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¶

Install Postgresql (for example, on CentOS) with command
```
yum install postgresql-server
```

Initialize DB and start Postgresql

postgresql-setup initdb
systemctl enable postgresql
systemctl start postgresql

Enter Postgresql console
```
su - postgres
psql
```

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";

Exit Postgresql console
```
\q
exit
```

DB queries optimizing¶

In builds before 1.163, to speed up Postgresql and to decrease CPU load, the following indexes should be created after successfull launch of WCS OAM:

CREATE INDEX ON stream(name, media_id, node_id);
CREATE INDEX ON stream(node_id, name, status);
CREATE INDEX ON stream(publisher_id);

Data structure of stream table in wcsoam database should look as follows: