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
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
|
create database if not exists ves;
use ves;
create table if not exists ves_measurement (
-- our id
id INTEGER NOT NULL AUTO_INCREMENT,
-- common header
event_type TEXT,
version TEXT,
source_id TEXT,
reporting_entity_name TEXT,
start_epoch_microsec BIGINT,
event_id TEXT,
last_epoch_microsec BIGINT,
priority TEXT,
sequence INT,
source_name TEXT,
domain TEXT,
event_name TEXT,
reporting_entity_id TEXT,
nfc_naming_code TEXT,
nf_naming_code TEXT,
time_zone_offset TEXT,
rawdata TEXT NOT NULL,
CONSTRAINT ves_measurement_pk PRIMARY KEY(id)
);
create table if not exists ves_measurement_fields (
event_id INTEGER NOT NULL,
measurement_interval LONG,
measurement_fields_version VARCHAR(32),
CONSTRAINT ves_measurement_fields_pk PRIMARY KEY (event_id),
CONSTRAINT ves_measurement_fields_fk1 FOREIGN KEY (event_id) REFERENCES ves_measurement(id) ON UPDATE CASCADE ON DELETE CASCADE
);
create table if not exists additional_measurement (
event_id INTEGER NOT NULL,
am_name VARCHAR(128) NOT NULL,
ves_measurement_fields_key INTEGER,
CONSTRAINT additional_measurement_pk PRIMARY KEY(event_id, am_name),
CONSTRAINT additional_measurement_fk1 FOREIGN KEY(event_id) REFERENCES ves_measurement(id) ON UPDATE CASCADE ON DELETE CASCADE
);
create table if not exists additional_measurement_value (
event INTEGER NOT NULL,
am_name VARCHAR(128) NOT NULL,
additional_measurement_key INTEGER,
ves_measurement_fields_key INTEGER,
am_key TEXT NOT NULL,
am_value TEXT,
CONSTRAINT additional_measurement_value_fk1 FOREIGN KEY(event, am_name) REFERENCES additional_measurement(event_id, am_name) ON UPDATE CASCADE ON DELETE CASCADE
);
-- to store raw payload i.e. without any parsing
create table if not exists payload (
event_id INTEGER NOT NULL,
payload TEXT NOT NULL
);
|
