1 DROP TABLE IF EXISTS "certOwners";
2 CREATE TABLE "certOwners" (
4 "created" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
5 "modified" timestamp NULL DEFAULT NULL,
6 "deleted" timestamp NULL DEFAULT NULL,
10 DROP TABLE IF EXISTS "users";
11 CREATE TABLE "users" (
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 '',
20 "verified" boolean NOT NULL DEFAULT 'false',
21 "ccid" int NOT NULL DEFAULT '0',
22 "regid" int NOT NULL DEFAULT '0',
23 "locid" int NOT NULL DEFAULT '0',
24 "listme" boolean NOT NULL DEFAULT 'false',
25 "contactinfo" varchar(255) NOT NULL DEFAULT '',
26 "language" varchar(5) NOT NULL DEFAULT '',
29 CREATE INDEX ON "users" ("ccid");
30 CREATE INDEX ON "users" ("regid");
31 CREATE INDEX ON "users" ("locid");
32 CREATE INDEX ON "users" ("email");
33 CREATE INDEX ON "users" ("verified");
36 DROP TABLE IF EXISTS "organisations";
37 CREATE TABLE IF NOT EXISTS "organisations" (
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 NOT NULL,
48 DROP TABLE IF EXISTS "domains";
49 CREATE TABLE "domains" (
52 "domain" varchar(255) NOT NULL,
53 "created" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
54 "modified" timestamp NULL DEFAULT NULL,
55 "deleted" timestamp NULL DEFAULT NULL,
58 CREATE INDEX ON "domains" ("memid");
59 CREATE INDEX ON "domains" ("domain");
60 CREATE INDEX ON "domains" ("deleted");
62 DROP TABLE IF EXISTS "emails";
63 CREATE TABLE "emails" (
65 "memid" int NOT NULL DEFAULT '0',
66 "email" varchar(255) NOT NULL DEFAULT '',
67 "created" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
68 "modified" timestamp NULL DEFAULT NULL,
69 "deleted" timestamp NULL DEFAULT NULL,
70 "hash" varchar(50) NOT NULL DEFAULT '',
71 "attempts" smallint NOT NULL DEFAULT '0',
74 CREATE INDEX ON "emails" ("memid");
75 CREATE INDEX ON "emails" ("hash");
76 CREATE INDEX ON "emails" ("deleted");
77 CREATE INDEX ON "emails" ("email");
79 DROP TABLE IF EXISTS "emailPinglog";
80 DROP TABLE IF EXISTS "domainPinglog";
82 DROP TYPE IF EXISTS "emailPingType";
83 CREATE TYPE "emailPingType" AS ENUM ('fast', 'active');
84 DROP TYPE IF EXISTS "pingState";
85 CREATE TYPE "pingState" AS ENUM ('open', 'success', 'failed');
87 CREATE TABLE "emailPinglog" (
88 "when" timestamp NOT NULL,
90 "email" varchar(255) NOT NULL,
91 "type" "emailPingType" NOT NULL,
92 "status" "pingState" NOT NULL,
93 "result" varchar(255) NOT NULL
96 DROP TABLE IF EXISTS "pingconfig";
98 DROP TYPE IF EXISTS "pingType";
99 CREATE TYPE "pingType" AS ENUM ('email', 'ssl', 'http', 'dns');
101 CREATE TABLE "pingconfig" (
102 "id" serial NOT NULL,
103 "domainid" int NOT NULL,
104 "type" "pingType" NOT NULL,
105 "info" varchar(255) NOT NULL,
110 CREATE TABLE "domainPinglog" (
111 "when" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
112 "configId" int NOT NULL,
113 "state" "pingState" NOT NULL,
114 "challenge" varchar(16),
115 "result" varchar(255)
118 DROP TABLE IF EXISTS "baddomains";
119 CREATE TABLE "baddomains" (
120 "domain" varchar(255) NOT NULL DEFAULT ''
124 DROP TABLE IF EXISTS "alerts";
125 CREATE TABLE "alerts" (
126 "memid" int NOT NULL DEFAULT '0',
127 "general" boolean NOT NULL DEFAULT 'false',
128 "country" boolean NOT NULL DEFAULT 'false',
129 "regional" boolean NOT NULL DEFAULT 'false',
130 "radius" boolean NOT NULL DEFAULT 'false',
131 PRIMARY KEY ("memid")
134 DROP TABLE IF EXISTS "user_agreements";
135 CREATE TABLE "user_agreements" (
136 "id" serial NOT NULL,
137 "memid" int NOT NULL,
138 "secmemid" int DEFAULT NULL,
139 "document" varchar(50) DEFAULT NULL,
140 "date" timestamp DEFAULT NULL,
141 "active" boolean NOT NULL,
142 "method" varchar(100) NOT NULL,
143 "comment" varchar(100) DEFAULT NULL,
147 DROP TABLE IF EXISTS "certs";
149 DROP TYPE IF EXISTS "mdType";
150 CREATE TYPE "mdType" AS ENUM('md5','sha1','sha256','sha512');
152 DROP TYPE IF EXISTS "csrType";
153 CREATE TYPE "csrType" AS ENUM ('CSR', 'SPKAC');
155 CREATE TABLE "certs" (
156 "id" serial NOT NULL,
157 "memid" int NOT NULL DEFAULT '0',
158 "serial" varchar(50) NOT NULL DEFAULT '',
159 "keytype" char(2) NOT NULL DEFAULT 'NS',
160 "codesign" boolean NOT NULL DEFAULT 'false',
161 "md" "mdType" NOT NULL DEFAULT 'sha512',
162 "profile" int NOT NULL,
163 "caid" int NULL DEFAULT NULL,
165 "csr_name" varchar(255) NOT NULL DEFAULT '',
166 "csr_type" "csrType" NOT NULL,
167 "crt_name" varchar(255) NOT NULL DEFAULT '',
168 "created" timestamp NULL DEFAULT NULL,
169 "modified" timestamp NULL DEFAULT NULL,
170 "revoked" timestamp NULL DEFAULT NULL,
171 "expire" timestamp NULL DEFAULT NULL,
172 "renewed" boolean NOT NULL DEFAULT 'false',
173 "disablelogin" boolean NOT NULL DEFAULT 'false',
174 "pkhash" char(40) DEFAULT NULL,
175 "certhash" char(40) DEFAULT NULL,
176 "description" varchar(100) NOT NULL DEFAULT '',
179 CREATE INDEX ON "certs" ("pkhash");
180 CREATE INDEX ON "certs" ("revoked");
181 CREATE INDEX ON "certs" ("created");
182 CREATE INDEX ON "certs" ("memid");
183 CREATE INDEX ON "certs" ("serial");
184 CREATE INDEX ON "certs" ("expire");
185 CREATE INDEX ON "certs" ("crt_name");
189 DROP TABLE IF EXISTS "certAvas";
190 CREATE TABLE "certAvas" (
191 "certid" int NOT NULL,
192 "name" varchar(20) NOT NULL,
193 "value" varchar(255) NOT NULL,
195 PRIMARY KEY ("certid", "name")
198 DROP TABLE IF EXISTS "clientcerts";
199 CREATE TABLE "clientcerts" (
201 "disablelogin" boolean NOT NULL DEFAULT 'false',
206 DROP TABLE IF EXISTS "profiles";
207 CREATE TABLE "profiles" (
208 "id" serial NOT NULL,
209 "keyname" varchar(60) NOT NULL,
210 "include" varchar(200) NOT NULL,
211 "requires" varchar(200) NOT NULL,
212 "name" varchar(100) NOT NULL,
217 DROP TABLE IF EXISTS "subjectAlternativeNames";
219 DROP TYPE IF EXISTS "SANType";
220 CREATE TYPE "SANType" AS ENUM ('email', 'DNS');
222 CREATE TABLE "subjectAlternativeNames" (
223 "certId" int NOT NULL,
224 "contents" varchar(50) NOT NULL,
225 "type" "SANType" NOT NULL
228 DROP TABLE IF EXISTS "cacerts";
229 CREATE TABLE "cacerts" (
230 "id" serial NOT NULL,
231 "keyname" varchar(60) NOT NULL,
232 "link" varchar(160) NOT NULL,
233 "parentRoot" int NOT NULL,
234 "validFrom" timestamp NULL DEFAULT NULL,
235 "validTo" timestamp NULL DEFAULT NULL,
240 DROP TABLE IF EXISTS "jobs";
242 DROP TYPE IF EXISTS "jobType";
243 CREATE TYPE "jobType" AS ENUM ('sign', 'revoke');
244 DROP TYPE IF EXISTS "jobState";
245 CREATE TYPE "jobState" AS ENUM ('open', 'done', 'error');
248 CREATE TABLE "jobs" (
249 "id" serial NOT NULL,
250 "targetId" int NOT NULL,
251 "task" "jobType" NOT NULL,
252 "state" "jobState" NOT NULL DEFAULT 'open',
253 "warning" smallint NOT NULL DEFAULT '0',
255 "executeTo" VARCHAR(11),
259 CREATE INDEX ON "jobs" ("state");
261 DROP TABLE IF EXISTS "notary";
263 DROP TYPE IF EXISTS "notaryType";
264 CREATE TYPE "notaryType" AS enum('Face to Face Meeting', 'TOPUP', 'TTP-Assisted');
266 CREATE TABLE "notary" (
267 "id" serial NOT NULL,
268 "from" int NOT NULL DEFAULT '0',
269 "to" int NOT NULL DEFAULT '0',
270 # total points that have been entered
271 "points" int NOT NULL DEFAULT '0',
272 # awarded and the "experience points" are calculated virtually
273 # Face to Face is default, TOPUP is for the remaining 30Points after two TTP
274 # TTP is default ttp assurance
275 "method" "notaryType" NOT NULL DEFAULT 'Face to Face Meeting',
276 "location" varchar(255) NOT NULL DEFAULT '',
277 "date" varchar(255) NOT NULL DEFAULT '',
278 # date when assurance was entered
279 "when" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
281 "expire" timestamp NULL DEFAULT NULL,
283 "sponsor" int NOT NULL DEFAULT '0',
284 # date when assurance was deleted (or 0)
285 "deleted" timestamp NULL DEFAULT NULL,
289 CREATE INDEX ON "notary"("from");
290 CREATE INDEX ON "notary"("to");
291 CREATE INDEX ON "notary"("when");
292 CREATE INDEX ON "notary"("method");
295 DROP TABLE IF EXISTS "cats_passed";
296 CREATE TABLE "cats_passed" (
297 "id" serial NOT NULL,
298 "user_id" int NOT NULL,
299 "variant_id" int NOT NULL,
300 "pass_date" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
302 UNIQUE ("user_id","variant_id","pass_date")
305 # --------------------------------------------------------
308 # Table structure for table "cats_type"
311 DROP TABLE IF EXISTS "cats_type";
312 CREATE TABLE "cats_type" (
313 "id" serial NOT NULL,
314 "type_text" varchar(255) NOT NULL,
319 DROP TABLE IF EXISTS "arbitrations";
320 CREATE TABLE IF NOT EXISTS "arbitrations" (
322 "arbitration" varchar(20) NOT NULL,
323 PRIMARY KEY ("user","arbitration")
326 DROP TABLE IF EXISTS "user_groups";
328 DROP TYPE IF EXISTS "userGroup";
329 CREATE TYPE "userGroup" AS enum('supporter','arbitrator','blockedassuree','blockedassurer','blockedlogin','ttp-assurer','ttp-applicant', 'codesigning', 'orgassurer');
331 CREATE TABLE IF NOT EXISTS "user_groups" (
332 "id" serial NOT NULL,
334 "permission" "userGroup" NOT NULL,
335 "granted" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
336 "deleted" timestamp NULL DEFAULT NULL,
337 "grantedby" int NOT NULL,
338 "revokedby" int DEFAULT NULL,
342 DROP TABLE IF EXISTS "org_admin";
344 DROP TYPE IF EXISTS "yesno";
345 CREATE TYPE "yesno" AS enum('y', 'n');
348 CREATE TABLE IF NOT EXISTS "org_admin" (
349 "orgid" int NOT NULL,
350 "memid" int NOT NULL,
351 "master" "yesno" NOT NULL,
352 "creator" int NOT NULL,
353 "created" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
354 "deleter" int NULL DEFAULT NULL,
355 "deleted" timestamp NULL DEFAULT NULL
357 CREATE INDEX ON "org_admin"("orgid", "memid");
360 DROP TABLE IF EXISTS "adminLog";
361 CREATE TABLE "adminLog" (
362 "when" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
364 "admin" int NOT NULL,
365 "type" varchar(100) NOT NULL DEFAULT '',
366 "information" varchar(50) NOT NULL DEFAULT ''
368 CREATE INDEX ON "adminLog"("when");
371 DROP TABLE IF EXISTS "schemeVersion";
372 CREATE TABLE "schemeVersion" (
373 "version" smallint NOT NULL,
374 PRIMARY KEY ("version")
376 INSERT INTO "schemeVersion" (version) VALUES(4);