1 DROP TABLE IF EXISTS `certOwners`;
2 CREATE TABLE `certOwners` (
3 `id` int(11) NOT NULL AUTO_INCREMENT,
4 `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
5 `modified` timestamp NULL DEFAULT NULL,
6 `deleted` timestamp NULL DEFAULT NULL,
8 ) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;
10 DROP TABLE IF EXISTS `users`;
11 CREATE TABLE `users` (
12 `id` int(11) NOT NULL,
13 `email` varchar(255) NOT NULL DEFAULT '',
14 `password` varchar(255) NOT NULL DEFAULT '',
15 `fname` varchar(255) NOT NULL DEFAULT '',
16 `mname` varchar(255) NOT NULL DEFAULT '',
17 `lname` varchar(255) NOT NULL DEFAULT '',
18 `suffix` varchar(50) NOT NULL DEFAULT '',
19 `dob` date NOT NULL DEFAULT '0000-00-00',
20 `verified` int(1) NOT NULL DEFAULT '0',
21 `ccid` int(3) NOT NULL DEFAULT '0',
22 `regid` int(5) NOT NULL DEFAULT '0',
23 `locid` int(7) NOT NULL DEFAULT '0',
24 `listme` int(1) NOT NULL DEFAULT '0',
25 `contactinfo` varchar(255) NOT NULL DEFAULT '',
26 `language` varchar(5) NOT NULL DEFAULT '',
29 KEY `regid` (`regid`),
30 KEY `locid` (`locid`),
31 KEY `email` (`email`),
32 KEY `stats_users_verified` (`verified`)
33 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
36 DROP TABLE IF EXISTS `organisations`;
37 CREATE TABLE IF NOT EXISTS `organisations` (
38 `id` int(11) NOT NULL,
39 `name` varchar(100) NOT NULL,
40 `state` varchar(2) NOT NULL,
41 `province` varchar(100) NOT NULL,
42 `city` varchar(100) NOT NULL,
43 `contactEmail` varchar(100) NOT NULL,
44 `creator` int(11) NOT NULL,
46 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
48 DROP TABLE IF EXISTS `domains`;
49 CREATE TABLE `domains` (
50 `id` int(11) NOT NULL AUTO_INCREMENT,
51 `memid` int(11) NOT NULL,
52 `domain` varchar(255) NOT NULL,
53 `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
54 `modified` datetime NULL DEFAULT NULL,
55 `deleted` datetime NULL DEFAULT NULL,
57 KEY `memid` (`memid`),
58 KEY `domain` (`domain`),
59 KEY `stats_domains_deleted` (`deleted`)
60 ) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;
62 DROP TABLE IF EXISTS `emails`;
63 CREATE TABLE `emails` (
64 `id` int(11) NOT NULL AUTO_INCREMENT,
65 `memid` int(11) NOT NULL DEFAULT '0',
66 `email` varchar(255) NOT NULL DEFAULT '',
67 `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
68 `modified` datetime NULL DEFAULT NULL,
69 `deleted` datetime NULL DEFAULT NULL,
70 `hash` varchar(50) NOT NULL DEFAULT '',
71 `attempts` int(1) NOT NULL DEFAULT '0',
73 KEY `memid` (`memid`),
74 KEY `stats_email_hash` (`hash`),
75 KEY `stats_email_deleted` (`deleted`),
77 ) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;
79 DROP TABLE IF EXISTS `emailPinglog`;
80 CREATE TABLE `emailPinglog` (
81 `when` datetime NOT NULL,
82 `uid` int(11) NOT NULL,
83 `email` varchar(255) NOT NULL,
84 `type` enum('fast', 'active') NOT NULL,
85 `status` enum('open', 'success', 'failed') NOT NULL,
86 `result` varchar(255) NOT NULL
87 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
89 DROP TABLE IF EXISTS `pingconfig`;
90 CREATE TABLE `pingconfig` (
91 `id` int(13) NOT NULL AUTO_INCREMENT,
92 `domainid` int(11) NOT NULL,
93 `type` enum('email', 'ssl', 'http', 'dns') NOT NULL,
94 `info` varchar(255) NOT NULL,
95 `reping` enum('y','n') NOT NULL DEFAULT 'n',
97 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
100 DROP TABLE IF EXISTS `domainPinglog`;
101 CREATE TABLE `domainPinglog` (
102 `when` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
103 `configId` int(13) NOT NULL,
104 `state` enum('open', 'success', 'failed') NOT NULL,
105 `challenge` varchar(16),
106 `result` varchar(255)
107 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
109 DROP TABLE IF EXISTS `baddomains`;
110 CREATE TABLE `baddomains` (
111 `domain` varchar(255) NOT NULL DEFAULT ''
112 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
115 DROP TABLE IF EXISTS `alerts`;
116 CREATE TABLE `alerts` (
117 `memid` int(11) NOT NULL DEFAULT '0',
118 `general` tinyint(1) NOT NULL DEFAULT '0',
119 `country` tinyint(1) NOT NULL DEFAULT '0',
120 `regional` tinyint(1) NOT NULL DEFAULT '0',
121 `radius` tinyint(1) NOT NULL DEFAULT '0',
122 PRIMARY KEY (`memid`)
123 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
125 DROP TABLE IF EXISTS `user_agreements`;
126 CREATE TABLE `user_agreements` (
127 `id` int(11) NOT NULL AUTO_INCREMENT,
128 `memid` int(11) NOT NULL,
129 `secmemid` int(11) DEFAULT NULL,
130 `document` varchar(50) DEFAULT NULL,
131 `date` datetime DEFAULT NULL,
132 `active` int(1) NOT NULL,
133 `method` varchar(100) NOT NULL,
134 `comment` varchar(100) DEFAULT NULL,
136 ) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;
138 DROP TABLE IF EXISTS `certs`;
139 CREATE TABLE `certs` (
140 `id` int(11) NOT NULL AUTO_INCREMENT,
141 `memid` int(11) NOT NULL DEFAULT '0',
142 `serial` varchar(50) NOT NULL DEFAULT '',
143 `keytype` char(2) NOT NULL DEFAULT 'NS',
144 `codesign` tinyint(1) NOT NULL DEFAULT '0',
145 `md` enum('md5','sha1','sha256','sha512') NOT NULL DEFAULT 'sha512',
146 `profile` int(3) NOT NULL,
147 `caid` int(3) NULL DEFAULT NULL,
149 `csr_name` varchar(255) NOT NULL DEFAULT '',
150 `csr_type` enum('CSR', 'SPKAC') NOT NULL,
151 `crt_name` varchar(255) NOT NULL DEFAULT '',
152 `created` timestamp NULL DEFAULT NULL,
153 `modified` datetime NULL DEFAULT NULL,
154 `revoked` datetime NULL DEFAULT NULL,
155 `expire` datetime NULL DEFAULT NULL,
156 `renewed` tinyint(1) NOT NULL DEFAULT '0',
157 `disablelogin` int(1) NOT NULL DEFAULT '0',
158 `pkhash` char(40) DEFAULT NULL,
159 `certhash` char(40) DEFAULT NULL,
160 `description` varchar(100) NOT NULL DEFAULT '',
162 KEY `emailcerts_pkhash` (`pkhash`),
163 KEY `revoked` (`revoked`),
164 KEY `created` (`created`),
165 KEY `memid` (`memid`),
166 KEY `serial` (`serial`),
167 KEY `stats_emailcerts_expire` (`expire`),
168 KEY `emailcrt` (`crt_name`)
169 ) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;
172 DROP TABLE IF EXISTS `certAvas`;
173 CREATE TABLE `certAvas` (
174 `certid` int(11) NOT NULL,
175 `name` varchar(20) NOT NULL,
176 `value` varchar(255) NOT NULL,
178 PRIMARY KEY (`certid`, `name`)
179 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
181 DROP TABLE IF EXISTS `clientcerts`;
182 CREATE TABLE `clientcerts` (
183 `id` int(11) NOT NULL,
184 `disablelogin` int(1) NOT NULL DEFAULT '0',
187 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
189 DROP TABLE IF EXISTS `profiles`;
190 CREATE TABLE `profiles` (
191 `id` int(3) NOT NULL AUTO_INCREMENT,
192 `keyname` varchar(60) NOT NULL,
193 `keyUsage` varchar(100) NOT NULL,
194 `extendedKeyUsage` varchar(100) NOT NULL,
195 `rootcert` int(2) NOT NULL DEFAULT '1',
196 `name` varchar(100) NOT NULL,
199 ) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;
201 INSERT INTO `profiles` SET rootcert=0, keyname='client', name='ssl-client (unassured)', keyUsage='digitalSignature, keyEncipherment, keyAgreement', extendedKeyUsage='clientAuth';
202 INSERT INTO `profiles` SET rootcert=0, keyname='mail', name='mail (unassured)', keyUsage='digitalSignature, keyEncipherment, keyAgreement', extendedKeyUsage='emailProtection';
203 INSERT INTO `profiles` SET rootcert=0, keyname='client-mail', name='ssl-client + mail (unassured)', keyUsage='digitalSignature, keyEncipherment, keyAgreement', extendedKeyUsage='clientAuth, emailProtection';
204 INSERT INTO `profiles` SET rootcert=0, keyname='server', name='ssl-server (unassured)', keyUsage='digitalSignature, keyEncipherment, keyAgreement', extendedKeyUsage='serverAuth';
206 INSERT INTO `profiles` SET rootcert=1, keyname='client-a', name='ssl-client (assured)', keyUsage='digitalSignature, keyEncipherment, keyAgreement', extendedKeyUsage='clientAuth';
207 INSERT INTO `profiles` SET rootcert=1, keyname='mail-a', name='mail (assured)', keyUsage='digitalSignature, keyEncipherment, keyAgreement', extendedKeyUsage='emailProtection';
208 INSERT INTO `profiles` SET rootcert=1, keyname='client-mail-a', name='ssl-client + mail(assured)', keyUsage='digitalSignature, keyEncipherment, keyAgreement', extendedKeyUsage='clientAuth, emailProtection';
209 INSERT INTO `profiles` SET rootcert=1, keyname='server-a', name='ssl-server (assured)', keyUsage='digitalSignature, keyEncipherment, keyAgreement', extendedKeyUsage='serverAuth';
210 INSERT INTO `profiles` SET rootcert=2, keyname='code-a', name='codesign (assured)', keyUsage='digitalSignature, keyEncipherment, keyAgreement', extendedKeyUsage='codeSigning, msCodeInd, msCodeCom';
212 INSERT INTO `profiles` SET rootcert=3, keyname='client-orga', name='ssl-client (orga)', keyUsage='digitalSignature, keyEncipherment, keyAgreement', extendedKeyUsage='clientAuth';
213 INSERT INTO `profiles` SET rootcert=3, keyname='mail-orga', name='mail (orga)', keyUsage='digitalSignature, keyEncipherment, keyAgreement', extendedKeyUsage='emailProtection';
214 INSERT INTO `profiles` SET rootcert=3, keyname='client-mail-orga', name='ssl-client + mail(orga)', keyUsage='digitalSignature, keyEncipherment, keyAgreement', extendedKeyUsage='clientAuth, emailProtection';
215 INSERT INTO `profiles` SET rootcert=3, keyname='server-orga', name='ssl-server (orga)', keyUsage='digitalSignature, keyEncipherment, keyAgreement', extendedKeyUsage='serverAuth';
216 INSERT INTO `profiles` SET rootcert=4, keyname='code-orga', name='codesign (orga)', keyUsage='digitalSignature, keyEncipherment, keyAgreement', extendedKeyUsage='codeSigning, msCodeInd, msCodeCom';
218 -- 0=unassured, 1=assured, 2=codesign, 3=orga, 4=orga-sign
219 DROP TABLE IF EXISTS `subjectAlternativeNames`;
220 CREATE TABLE `subjectAlternativeNames` (
221 `certId` int(11) NOT NULL,
222 `contents` varchar(50) NOT NULL,
223 `type` enum('email','DNS') NOT NULL
224 ) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;
226 DROP TABLE IF EXISTS `cacerts`;
227 CREATE TABLE `cacerts` (
228 `id` int(3) NOT NULL AUTO_INCREMENT,
229 `keyname` varchar(60) NOT NULL,
230 `subroot` int(2) NOT NULL,
231 `validFrom` datetime NULL DEFAULT NULL,
232 `validTo` datetime NULL DEFAULT NULL,
235 ) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;
238 DROP TABLE IF EXISTS `jobs`;
239 CREATE TABLE `jobs` (
240 `id` int(11) NOT NULL AUTO_INCREMENT,
241 `targetId` int(11) NOT NULL,
242 `task` enum('sign','revoke') NOT NULL,
243 `state` enum('open', 'done', 'error') NOT NULL DEFAULT 'open',
244 `warning` int(2) NOT NULL DEFAULT '0',
246 `executeTo` VARCHAR(11),
248 KEY `state` (`state`)
249 ) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;
252 DROP TABLE IF EXISTS `notary`;
253 CREATE TABLE `notary` (
254 `id` int(11) NOT NULL AUTO_INCREMENT,
255 `from` int(11) NOT NULL DEFAULT '0',
256 `to` int(11) NOT NULL DEFAULT '0',
257 # total points that have been entered
258 `points` int(3) NOT NULL DEFAULT '0',
259 # awarded and the "experience points" are calculated virtually
260 # Face to Face is default, TOPUP is for the remaining 30Points after two TTP
261 # TTP is default ttp assurance
262 `method` enum('Face to Face Meeting', 'TOPUP', 'TTP-Assisted') NOT NULL DEFAULT 'Face to Face Meeting',
263 `location` varchar(255) NOT NULL DEFAULT '',
264 `date` varchar(255) NOT NULL DEFAULT '',
265 # date when assurance was entered
266 `when` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
268 `expire` datetime NULL DEFAULT NULL,
270 `sponsor` int(11) NOT NULL DEFAULT '0',
271 # date when assurance was deleted (or 0)
272 `deleted` datetime NULL DEFAULT NULL,
276 KEY `stats_notary_when` (`when`),
277 KEY `stats_notary_method` (`method`)
278 ) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;
281 DROP TABLE IF EXISTS `cats_passed`;
282 CREATE TABLE `cats_passed` (
283 `id` int(11) NOT NULL AUTO_INCREMENT,
284 `user_id` int(11) NOT NULL,
285 `variant_id` int(11) NOT NULL,
286 `pass_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
288 UNIQUE KEY `test_passed` (`user_id`,`variant_id`,`pass_date`)
289 ) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;
291 # --------------------------------------------------------
294 # Table structure for table `cats_type`
297 DROP TABLE IF EXISTS `cats_type`;
298 CREATE TABLE `cats_type` (
299 `id` int(11) NOT NULL AUTO_INCREMENT,
300 `type_text` varchar(255) NOT NULL,
302 UNIQUE KEY `type_text` (`type_text`)
303 ) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;
305 DROP TABLE IF EXISTS `arbitrations`;
306 CREATE TABLE IF NOT EXISTS `arbitrations` (
307 `user` int(11) NOT NULL,
308 `arbitration` varchar(20) NOT NULL,
309 PRIMARY KEY (`user`,`arbitration`)
310 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
312 DROP TABLE IF EXISTS `user_groups`;
313 CREATE TABLE IF NOT EXISTS `user_groups` (
314 `id` int(11) NOT NULL AUTO_INCREMENT,
315 `user` int(11) NOT NULL,
316 `permission` enum('supporter','arbitrator','blockedassuree','blockedassurer','blockedlogin','ttp-assurer','ttp-applicant', 'codesigning', 'orgassurer') NOT NULL,
317 `granted` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
318 `deleted` timestamp NULL DEFAULT NULL,
319 `grantedby` int(11) NOT NULL,
320 `revokedby` int(11) DEFAULT NULL,
322 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
324 DROP TABLE IF EXISTS `org_admin`;
325 CREATE TABLE IF NOT EXISTS `org_admin` (
326 `orgid` int(11) NOT NULL,
327 `memid` int(11) NOT NULL,
328 `master` enum('y', 'n') NOT NULL,
329 `creator` int(11) NOT NULL,
330 `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
331 `deleter` int(11) NULL DEFAULT NULL,
332 `deleted` timestamp NULL DEFAULT NULL,
333 KEY (`orgid`, `memid`)
334 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
336 DROP TABLE IF EXISTS `adminLog`;
337 CREATE TABLE `adminLog` (
338 `when` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
339 `uid` int(11) unsigned NOT NULL,
340 `admin` int(11) unsigned NOT NULL,
341 `type` varchar(100) NOT NULL DEFAULT '',
342 `information` varchar(50) NOT NULL DEFAULT '',
344 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;