Annotation of embedaddon/strongswan/src/libimcv/imv/tables-mysql.sql, revision 1.1.1.1
1.1 misho 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 `sessions_identities`;
103: CREATE TABLE `sessions_identities` (
104: `id` INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
105: `session_id` INTEGER NOT NULL REFERENCES `sessions`(`id`),
106: `identity_id` INTEGER NOT NULL REFERENCES `identities`(`id`),
107: UNIQUE (`session_id`, `identity_id`)
108: );
109:
110: DROP TABLE IF EXISTS `workitems`;
111: CREATE TABLE `workitems` (
112: `id` INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
113: `session` INTEGER NOT NULL REFERENCES `sessions`(`id`),
114: `enforcement` INTEGER NOT NULL REFERENCES `enforcements`(`id`),
115: `type` INTEGER NOT NULL,
116: `arg_str` VARCHAR(128),
117: `arg_int` INTEGER DEFAULT 0,
118: `rec_fail` INTEGER NOT NULL,
119: `rec_noresult` INTEGER NOT NULL,
120: `rec_final` INTEGER,
121: `result` VARCHAR(128)
122: );
123:
124: DROP TABLE IF EXISTS `results`;
125: CREATE TABLE `results` (
126: `id` INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
127: `session` INTEGER NOT NULL REFERENCES `measurements`(`id`),
128: `policy` INTEGER NOT NULL REFERENCES `policies`(`id`),
129: `rec` INTEGER NOT NULL,
130: `result` TEXT NOT NULL
131: );
132:
133: DROP TABLE IF EXISTS `components`;
134: CREATE TABLE `components` (
135: `id` INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
136: `vendor_id` INTEGER NOT NULL,
137: `name` INTEGER NOT NULL,
138: `qualifier` INTEGER DEFAULT 0
139: );
140:
141: DROP TABLE IF EXISTS `key_component`;
142: CREATE TABLE `key_component` (
143: `key` INTEGER NOT NULL,
144: `component` INTEGER NOT NULL,
145: `depth` INTEGER DEFAULT 0,
146: `seq_no` INTEGER DEFAULT 0,
147: PRIMARY KEY (`key`, `component`)
148: );
149:
150: DROP TABLE IF EXISTS `component_hashes`;
151: CREATE TABLE `component_hashes` (
152: `component` INTEGER NOT NULL,
153: `key` INTEGER NOT NULL,
154: `seq_no` INTEGER NOT NULL,
155: `pcr` INTEGER NOT NULL,
156: `algo` INTEGER NOT NULL,
157: `hash` VARBINARY(32) NOT NULL,
158: PRIMARY KEY(`component`, `key`, `seq_no`, `algo`)
159: );
160:
161: DROP TABLE IF EXISTS `packages`;
162: CREATE TABLE `packages` (
163: `id` INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
164: `name` VARCHAR(128) NOT NULL,
165: `blacklist` INTEGER DEFAULT 0
166: );
167:
168: DROP TABLE IF EXISTS versions;
169: CREATE TABLE versions (
170: `id` INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
171: `package` INTEGER NOT NULL REFERENCES packages(id),
172: `product` INTEGER NOT NULL REFERENCES products(id),
173: `release` VARCHAR(32) NOT NULL,
174: `security` INTEGER DEFAULT 0,
175: `blacklist` INTEGER DEFAULT 0,
176: `time` INTEGER DEFAULT 0
177: );
178:
179: DROP TABLE IF EXISTS `devices`;
180: CREATE TABLE `devices` (
181: `id` INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
182: `description` VARCHAR(100) DEFAULT "",
183: `value` VARCHAR(256) NOT NULL,
184: `product` INTEGER REFERENCES `products`(`id`),
185: `trusted` INTEGER DEFAULT 0,
186: `created` INTEGER,
187: `inactive` INTEGER DEFAULT 0
188: );
189:
190: DROP TABLE IF EXISTS `identities`;
191: CREATE TABLE `identities` (
192: `id` INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
193: `type` INTEGER NOT NULL,
194: `value` VARBINARY(128) NOT NULL,
195: UNIQUE (type, value)
196: );
197:
198: DROP TABLE IF EXISTS `regids`;
199: CREATE TABLE `regids` (
200: `id` INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
201: `name` TEXT NOT NULL
202: );
203:
204: DROP TABLE IF EXISTS `tags`;
205: CREATE TABLE `tags` (
206: `id` INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
207: `regid` INTEGER NOT NULL REFERENCES `regids`(`id`),
208: `unique_sw_id` VARCHAR(64) NOT NULL,
209: `value` VARCHAR(128)
210: );
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>