Added add_segment() method to IETF attributes
[strongswan.git] / src / libimcv / imv / tables-mysql.sql
1
2 DROP TABLE IF EXISTS `directories`;
3 CREATE TABLE `directories` (
4 `id` INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
5 `path` VARCHAR(2048) NOT NULL
6 );
7
8 DROP TABLE IF EXISTS `files`;
9 CREATE TABLE `files` (
10 `id` INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
11 `dir` INTEGER DEFAULT 0 REFERENCES `directories`(`id`),
12 `name` VARCHAR(512) NOT NULL
13 );
14
15 DROP TABLE IF EXISTS `products`;
16 CREATE TABLE `products` (
17 `id` INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
18 `name` VARCHAR(128) NOT NULL
19 );
20
21 DROP TABLE IF EXISTS `algorithms`;
22 CREATE TABLE `algorithms` (
23 `id` INTEGER PRIMARY KEY,
24 `name` VARCHAR(20) NOT NULL
25 );
26
27 DROP TABLE IF EXISTS `file_hashes`;
28 CREATE TABLE `file_hashes` (
29 `id` INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
30 `file` INTEGER NOT NULL REFERENCES `files`(`id`),
31 `product` INTEGER NOT NULL REFERENCES `products`(`id`),
32 `device` INTEGER DEFAULT 0,
33 `key` INTEGER DEFAULT 0 REFERENCES `keys`(id),
34 `algo` INTEGER NOT NULL REFERENCES `algorithms`(`id`),
35 `hash` VARBINARY(64) NOT NULL
36 );
37
38 DROP TABLE IF EXISTS `keys`;
39 CREATE TABLE `keys` (
40 `id` INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
41 `keyid` VARBINARY(128) NOT NULL,
42 `owner` VARCHAR(128) NOT NULL
43 );
44
45 DROP TABLE IF EXISTS `groups`;
46 CREATE TABLE `groups` (
47 `id` INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
48 `name` VARCHAR(50) NOT NULL UNIQUE,
49 `parent` INTEGER
50 );
51
52 DROP TABLE IF EXISTS `groups_members`;
53 CREATE TABLE `groups_members` (
54 `id` INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
55 `group_id` INTEGER NOT NULL REFERENCES `groups`(`id`),
56 `device_id` INTEGER NOT NULL REFERENCES `devices`(`id`),
57 UNIQUE (`group_id`, `device_id`)
58 );
59
60 DROP TABLE IF EXISTS `groups_product_defaults`;
61 CREATE TABLE `groups_product_defaults` (
62 `id` INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
63 `group_id` INTEGER NOT NULL REFERENCES `groups`(`id`),
64 `product_id` INTEGER NOT NULL REFERENCES `products`(`id`),
65 UNIQUE (`group_id`, `product_id`)
66 );
67
68 DROP TABLE IF EXISTS `policies`;
69 CREATE TABLE `policies` (
70 `id` INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
71 `type` INTEGER NOT NULL,
72 `name` VARCHAR(100) NOT NULL UNIQUE,
73 `argument` VARCHAR(100) DEFAULT '' NOT NULL,
74 `rec_fail` INTEGER NOT NULL,
75 `rec_noresult` INTEGER NOT NULL,
76 `file` INTEGER DEFAULT 0 REFERENCES `files`(`id`),
77 `dir` INTEGER DEFAULT 0 REFERENCES `directories`(`id`)
78 );
79
80 DROP TABLE IF EXISTS `enforcements`;
81 CREATE TABLE `enforcements` (
82 `id` INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
83 `policy` INTEGER NOT NULL REFERENCES `policies`(`id`),
84 `group_id` INTEGER NOT NULL REFERENCES `groups`(`id`),
85 `rec_fail` INTEGER,
86 `rec_noresult` INTEGER,
87 `max_age` INTEGER NOT NULL,
88 UNIQUE (`policy`, `group_id`)
89 );
90
91 DROP TABLE IF EXISTS `sessions`;
92 CREATE TABLE `sessions` (
93 `id` INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
94 `time` INTEGER NOT NULL,
95 `connection` INTEGER NOT NULL,
96 `identity` INTEGER DEFAULT 0 REFERENCES `identities`(`id`),
97 `device` INTEGER DEFAULT 0 REFERENCES `devices`(`id`),
98 `product` INTEGER DEFAULT 0 REFERENCES `products`(`id`),
99 `rec` INTEGER DEFAULT 3
100 );
101
102 DROP TABLE IF EXISTS `workitems`;
103 CREATE TABLE `workitems` (
104 `id` INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
105 `session` INTEGER NOT NULL REFERENCES `sessions`(`id`),
106 `enforcement` INTEGER NOT NULL REFERENCES `enforcements`(`id`),
107 `type` INTEGER NOT NULL,
108 `arg_str` VARCHAR(128),
109 `arg_int` INTEGER DEFAULT 0,
110 `rec_fail` INTEGER NOT NULL,
111 `rec_noresult` INTEGER NOT NULL,
112 `rec_final` INTEGER,
113 `result` VARCHAR(128)
114 );
115
116 DROP TABLE IF EXISTS `results`;
117 CREATE TABLE `results` (
118 `id` INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
119 `session` INTEGER NOT NULL REFERENCES `measurements`(`id`),
120 `policy` INTEGER NOT NULL REFERENCES `policies`(`id`),
121 `rec` INTEGER NOT NULL,
122 `result` TEXT NOT NULL
123 );
124
125 DROP TABLE IF EXISTS `components`;
126 CREATE TABLE `components` (
127 `id` INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
128 `vendor_id` INTEGER NOT NULL,
129 `name` INTEGER NOT NULL,
130 `qualifier` INTEGER DEFAULT 0
131 );
132
133 DROP TABLE IF EXISTS `key_component`;
134 CREATE TABLE `key_component` (
135 `key` INTEGER NOT NULL,
136 `component` INTEGER NOT NULL,
137 `depth` INTEGER DEFAULT 0,
138 `seq_no` INTEGER DEFAULT 0,
139 PRIMARY KEY (`key`, `component`)
140 );
141
142 DROP TABLE IF EXISTS `component_hashes`;
143 CREATE TABLE `component_hashes` (
144 `component` INTEGER NOT NULL,
145 `key` INTEGER NOT NULL,
146 `seq_no` INTEGER NOT NULL,
147 `pcr` INTEGER NOT NULL,
148 `algo` INTEGER NOT NULL,
149 `hash` VARBINARY(32) NOT NULL,
150 PRIMARY KEY(`component`, `key`, `seq_no`, `algo`)
151 );
152
153 DROP TABLE IF EXISTS `packages`;
154 CREATE TABLE `packages` (
155 `id` INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
156 `name` VARCHAR(128) NOT NULL,
157 `blacklist` INTEGER DEFAULT 0
158 );
159
160 DROP TABLE IF EXISTS versions;
161 CREATE TABLE versions (
162 `id` INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
163 `package` INTEGER NOT NULL REFERENCES packages(id),
164 `product` INTEGER NOT NULL REFERENCES products(id),
165 `release` VARCHAR(32) NOT NULL,
166 `security` INTEGER DEFAULT 0,
167 `blacklist` INTEGER DEFAULT 0,
168 `time` INTEGER DEFAULT 0
169 );
170
171 DROP TABLE IF EXISTS `devices`;
172 CREATE TABLE `devices` (
173 `id` INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
174 `description` VARCHAR(100) DEFAULT "",
175 `value` VARCHAR(256) NOT NULL,
176 `product` INTEGER REFERENCES `products`(`id`),
177 `created` INTEGER
178 );
179
180 DROP TABLE IF EXISTS `identities`;
181 CREATE TABLE `identities` (
182 `id` INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
183 `type` INTEGER NOT NULL,
184 `value` VARBINARY(128) NOT NULL,
185 UNIQUE (type, value)
186 );
187
188 DROP TABLE IF EXISTS `regids`;
189 CREATE TABLE `regids` (
190 `id` INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
191 `name` TEXT NOT NULL
192 );
193
194 DROP TABLE IF EXISTS `tags`;
195 CREATE TABLE `tags` (
196 `id` INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
197 `regid` INTEGER NOT NULL REFERENCES `regids`(`id`),
198 `unique_sw_id` VARCHAR(64) NOT NULL,
199 `value` VARCHAR(128)
200 );