c962d433a83f3e2bf98f7ea4592c14e9d9a460cf
[strongswan.git] / src / libimcv / imv / tables.sql
1 /* IMV PTS SQLite database */
2
3 DROP TABLE IF EXISTS directories;
4 CREATE TABLE directories (
5 id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
6 path TEXT NOT NULL
7 );
8 DROP INDEX IF EXISTS directories_path;
9 CREATE INDEX directories_path ON directories (
10 path
11 );
12
13 DROP TABLE IF EXISTS files;
14 CREATE TABLE files (
15 id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
16 dir INTEGER DEFAULT 0 REFERENCES directories(id),
17 name TEXT NOT NULL
18 );
19 DROP INDEX IF EXISTS files_name;
20 CREATE INDEX files_name ON files (
21 name
22 );
23
24 DROP TABLE IF EXISTS products;
25 CREATE TABLE products (
26 id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
27 name TEXT NOT NULL
28 );
29 DROP INDEX IF EXISTS products_name;
30 CREATE INDEX products_name ON products (
31 name
32 );
33
34 DROP TABLE IF EXISTS product_file;
35 CREATE TABLE product_file (
36 product INTEGER NOT NULL REFERENCES products(id),
37 file INTEGER NOT NULL REFERENCES files(id),
38 measurement INTEGER DEFAULT 0,
39 metadata INTEGER DEFAULT 0,
40 PRIMARY KEY (product, file)
41 );
42
43 DROP TABLE IF EXISTS algorithms;
44 CREATE TABLE algorithms (
45 id INTEGER PRIMARY KEY,
46 name VARCHAR(20) not NULL
47 );
48
49 DROP TABLE IF EXISTS file_hashes;
50 CREATE TABLE file_hashes (
51 id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
52 file INTEGER NOT NULL REFERENCES files(id),
53 product INTEGER NOT NULL REFERENCES products(id),
54 device INTEGER DEFAULT 0,
55 algo INTEGER NOT NULL REFERENCES algorithms(id),
56 hash BLOB NOT NULL
57 );
58
59 DROP TABLE IF EXISTS sessions;
60 CREATE TABLE sessions (
61 id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
62 time INTEGER NOT NULL,
63 connection INTEGER NOT NULL,
64 identity INTEGER DEFAULT 0 REFERENCES identities(id),
65 device INTEGER DEFAULT 0 REFERENCES devices(id),
66 product INTEGER DEFAULT 0 REFERENCES products(id)
67 );
68
69 DROP TABLE IF EXISTS workitems;
70 CREATE TABLE workitems (
71 id integer NOT NULL PRIMARY KEY AUTOINCREMENT,
72 session integer NOT NULL REFERENCES sessions(id),
73 type integer DEFAULT 0,
74 argument text NOT NULL,
75 rec_fail integer DEFAULT 1,
76 rec_noresult integer DEFAULT 1,
77 rec_final integer DEFAULT 3,
78 result text
79 );
80 DROP INDEX IF EXISTS workitems_session;
81 CREATE INDEX workitems_sessions ON workitems (
82 session
83 );
84
85 DROP TABLE IF EXISTS components;
86 CREATE TABLE components (
87 id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
88 vendor_id INTEGER NOT NULL,
89 name INTEGER NOT NULL,
90 qualifier INTEGER DEFAULT 0
91 );
92
93
94 DROP TABLE IF EXISTS key_component;
95 CREATE TABLE key_component (
96 key INTEGER NOT NULL,
97 component INTEGER NOT NULL,
98 depth INTEGER DEFAULT 0,
99 seq_no INTEGER DEFAULT 0,
100 PRIMARY KEY (key, component)
101 );
102
103
104 DROP TABLE IF EXISTS component_hashes;
105 CREATE TABLE component_hashes (
106 component INTEGER NOT NULL,
107 key INTEGER NOT NULL,
108 seq_no INTEGER NOT NULL,
109 pcr INTEGER NOT NULL,
110 algo INTEGER NOT NULL,
111 hash BLOB NOT NULL,
112 PRIMARY KEY(component, key, seq_no, algo)
113 );
114
115 DROP TABLE IF EXISTS packages;
116 CREATE TABLE packages (
117 id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
118 name TEXT NOT NULL
119 );
120 DROP INDEX IF EXISTS packages_name;
121 CREATE INDEX packages_name ON packages (
122 name
123 );
124
125 DROP TABLE IF EXISTS versions;
126 CREATE TABLE versions (
127 id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
128 package INTEGER NOT NULL,
129 product INTEGER NOT NULL,
130 release TEXT NOT NULL,
131 security INTEGER DEFAULT 0,
132 time INTEGER DEFAULT 0
133 );
134 DROP INDEX IF EXISTS versions_release;
135 CREATE INDEX versions_release ON versions (
136 release
137 );
138 DROP INDEX IF EXISTS versions_package_product;
139 CREATE INDEX versions_package_product ON versions (
140 package, product
141 );
142
143 DROP TABLE IF EXISTS devices;
144 CREATE TABLE devices (
145 id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
146 value BLOB NOT NULL
147 );
148 DROP INDEX IF EXISTS devices_id;
149 CREATE INDEX devices_value ON devices (
150 value
151 );
152
153 DROP TABLE IF EXISTS device_infos;
154 CREATE TABLE device_infos (
155 id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
156 session INTEGER NOT NULL REFERENCES sessions(id),
157 count INTEGER DEFAULT 0,
158 count_update INTEGER DEFAULT 0,
159 count_blacklist INTEGER DEFAULT 0,
160 flags INTEGER DEFAULT 0
161 );
162
163 DROP TABLE IF EXISTS identities;
164 CREATE TABLE identities (
165 id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
166 type INTEGER NOT NULL,
167 value BLOB NOT NULL,
168 UNIQUE (type, value)
169 );