src/org/cacert/gigi/database/tableStructure.sql

   1 DROP TABLE IF EXISTS "certOwners";
   2 CREATE TABLE "certOwners" (
   3   "id" serial NOT NULL,
   4   "created" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
   5   "modified" timestamp NULL DEFAULT NULL,
   6   "deleted" timestamp NULL DEFAULT NULL,
   7   PRIMARY KEY ("id")
   8 );
   9
  10 DROP TABLE IF EXISTS "users";
  11 CREATE TABLE "users" (
  12   "id" int 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,
  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 '',
  27   PRIMARY KEY ("id")
  28 );
  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");
  34
  35
  36 DROP TABLE IF EXISTS "organisations";
  37 CREATE TABLE IF NOT EXISTS "organisations" (
  38   "id" int 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 NOT NULL,
  45   PRIMARY KEY ("id")
  46 );
  47
  48 DROP TABLE IF EXISTS "domains";
  49 CREATE TABLE "domains" (
  50   "id" serial NOT NULL,
  51   "memid" int NOT NULL,
  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,
  56   PRIMARY KEY ("id")
  57 );
  58 CREATE INDEX ON "domains" ("memid");
  59 CREATE INDEX ON "domains" ("domain");
  60 CREATE INDEX ON "domains" ("deleted");
  61
  62 DROP TABLE IF EXISTS "emails";
  63 CREATE TABLE "emails" (
  64   "id" serial NOT NULL,
  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',
  72   PRIMARY KEY ("id")
  73 );
  74 CREATE INDEX ON "emails" ("memid");
  75 CREATE INDEX ON "emails" ("hash");
  76 CREATE INDEX ON "emails" ("deleted");
  77 CREATE INDEX ON "emails" ("email");
  78
  79 DROP TABLE IF EXISTS "emailPinglog";
  80 DROP TABLE IF EXISTS "domainPinglog";
  81
  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');
  86
  87 CREATE TABLE "emailPinglog" (
  88   "when" timestamp NOT NULL,
  89   "uid" int NOT NULL,
  90   "email" varchar(255) NOT NULL,
  91   "type" "emailPingType" NOT NULL,
  92   "status" "pingState" NOT NULL,
  93   "result" varchar(255) NOT NULL
  94 );
  95
  96 DROP TABLE IF EXISTS "pingconfig";
  97
  98 DROP TYPE IF EXISTS "pingType";
  99 CREATE TYPE "pingType" AS ENUM ('email', 'ssl', 'http', 'dns');
 100
 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,
 106   "deleted" timestamp NULL DEFAULT NULL,
 107   PRIMARY KEY ("id")
 108 );
 109
 110
 111 CREATE TABLE "domainPinglog" (
 112   "when" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
 113   "configId" int NOT NULL,
 114   "state" "pingState" NOT NULL,
 115   "challenge" varchar(16),
 116   "result" varchar(255)
 117 );
 118
 119 DROP TABLE IF EXISTS "baddomains";
 120 CREATE TABLE "baddomains" (
 121   "domain" varchar(255) NOT NULL DEFAULT ''
 122 );
 123
 124
 125 DROP TABLE IF EXISTS "alerts";
 126 CREATE TABLE "alerts" (
 127   "memid" int NOT NULL DEFAULT '0',
 128   "general" boolean NOT NULL DEFAULT 'false',
 129   "country" boolean NOT NULL DEFAULT 'false',
 130   "regional" boolean NOT NULL DEFAULT 'false',
 131   "radius" boolean NOT NULL DEFAULT 'false',
 132   PRIMARY KEY ("memid")
 133 );
 134
 135 DROP TABLE IF EXISTS "user_agreements";
 136 CREATE TABLE "user_agreements" (
 137   "id" serial NOT NULL,
 138   "memid" int NOT NULL,
 139   "secmemid" int DEFAULT NULL,
 140   "document" varchar(50) DEFAULT NULL,
 141   "date" timestamp DEFAULT NULL,
 142   "active" boolean NOT NULL,
 143   "method" varchar(100) NOT NULL,
 144   "comment" varchar(100) DEFAULT NULL,
 145   PRIMARY KEY ("id")
 146 );
 147
 148 DROP TABLE IF EXISTS "certs";
 149
 150 DROP TYPE IF EXISTS "mdType";
 151 CREATE TYPE "mdType" AS ENUM('md5','sha1','sha256','sha512');
 152
 153 DROP TYPE IF EXISTS "csrType";
 154 CREATE TYPE "csrType" AS ENUM ('CSR', 'SPKAC');
 155
 156 CREATE TABLE "certs" (
 157   "id" serial NOT NULL,
 158   "memid" int NOT NULL DEFAULT '0',
 159   "serial" varchar(50) NOT NULL DEFAULT '',
 160   "keytype" char(2) NOT NULL DEFAULT 'NS',
 161   "codesign" boolean NOT NULL DEFAULT 'false',
 162   "md" "mdType" NOT NULL DEFAULT 'sha512',
 163   "profile" int NOT NULL,
 164   "caid" int NULL DEFAULT NULL,
 165
 166   "csr_name" varchar(255) NOT NULL DEFAULT '',
 167   "csr_type" "csrType" NOT NULL,
 168   "crt_name" varchar(255) NOT NULL DEFAULT '',
 169   "created" timestamp NULL DEFAULT NULL,
 170   "modified" timestamp NULL DEFAULT NULL,
 171   "revoked" timestamp NULL DEFAULT NULL,
 172   "expire" timestamp NULL DEFAULT NULL,
 173   "renewed" boolean NOT NULL DEFAULT 'false',
 174   "disablelogin" boolean NOT NULL DEFAULT 'false',
 175   "pkhash" char(40) DEFAULT NULL,
 176   "certhash" char(40) DEFAULT NULL,
 177   "description" varchar(100) NOT NULL DEFAULT '',
 178   PRIMARY KEY ("id")
 179 );
 180 CREATE INDEX ON "certs" ("pkhash");
 181 CREATE INDEX ON "certs" ("revoked");
 182 CREATE INDEX ON "certs" ("created");
 183 CREATE INDEX ON "certs" ("memid");
 184 CREATE INDEX ON "certs" ("serial");
 185 CREATE INDEX ON "certs" ("expire");
 186 CREATE INDEX ON "certs" ("crt_name");
 187
 188
 189
 190 DROP TABLE IF EXISTS "certAvas";
 191 CREATE TABLE "certAvas" (
 192   "certId" int NOT NULL,
 193   "name" varchar(20) NOT NULL,
 194   "value" varchar(255) NOT NULL,
 195
 196   PRIMARY KEY ("certId", "name")
 197 );
 198
 199 DROP TABLE IF EXISTS "clientcerts";
 200 CREATE TABLE "clientcerts" (
 201   "id" int NOT NULL,
 202   "disablelogin" boolean NOT NULL DEFAULT 'false',
 203
 204   PRIMARY KEY ("id")
 205 );
 206
 207 DROP TABLE IF EXISTS "profiles";
 208 CREATE TABLE "profiles" (
 209   "id" serial NOT NULL,
 210   "keyname" varchar(60) NOT NULL,
 211   "include" varchar(200) NOT NULL,
 212   "requires" varchar(200) NOT NULL,
 213   "name" varchar(100) NOT NULL,
 214   PRIMARY KEY ("id"),
 215   UNIQUE ("keyname")
 216 );
 217
 218 DROP TABLE IF EXISTS "subjectAlternativeNames";
 219
 220 DROP TYPE IF EXISTS "SANType";
 221 CREATE TYPE "SANType" AS ENUM ('email', 'DNS');
 222
 223 CREATE TABLE "subjectAlternativeNames" (
 224   "certId" int NOT NULL,
 225   "contents" varchar(50) NOT NULL,
 226   "type" "SANType" NOT NULL
 227 );
 228
 229 DROP TABLE IF EXISTS "cacerts";
 230 CREATE TABLE "cacerts" (
 231   "id" serial NOT NULL,
 232   "keyname" varchar(60) NOT NULL,
 233   "link" varchar(160) NOT NULL,
 234   "parentRoot" int NOT NULL,
 235   "validFrom" timestamp NULL DEFAULT NULL,
 236   "validTo" timestamp NULL DEFAULT NULL,
 237   PRIMARY KEY ("id"),
 238   UNIQUE ("keyname")
 239 );
 240
 241 DROP TABLE IF EXISTS "jobs";
 242
 243 DROP TYPE IF EXISTS "jobType";
 244 CREATE TYPE "jobType" AS ENUM ('sign', 'revoke');
 245 DROP TYPE IF EXISTS "jobState";
 246 CREATE TYPE "jobState" AS ENUM ('open', 'done', 'error');
 247
 248
 249 CREATE TABLE "jobs" (
 250   "id" serial NOT NULL,
 251   "targetId" int NOT NULL,
 252   "task" "jobType" NOT NULL,
 253   "state" "jobState" NOT NULL DEFAULT 'open',
 254   "warning" smallint NOT NULL DEFAULT '0',
 255   "executeFrom" DATE,
 256   "executeTo" VARCHAR(11),
 257   PRIMARY KEY ("id")
 258 );
 259
 260 CREATE INDEX ON "jobs" ("state");
 261
 262 DROP TABLE IF EXISTS "notary";
 263
 264 DROP TYPE IF EXISTS "notaryType";
 265 CREATE TYPE "notaryType" AS enum('Face to Face Meeting', 'TOPUP', 'TTP-Assisted');
 266
 267 CREATE TABLE "notary" (
 268   "id" serial NOT NULL,
 269   "from" int NOT NULL DEFAULT '0',
 270   "to" int NOT NULL DEFAULT '0',
 271 # total points that have been entered
 272   "points" int NOT NULL DEFAULT '0',
 273 # awarded and the "experience points" are calculated virtually
 274 # Face to Face is default, TOPUP is for the remaining 30Points after two TTP
 275 # TTP is default ttp assurance
 276   "method" "notaryType" NOT NULL DEFAULT 'Face to Face Meeting',
 277   "location" varchar(255) NOT NULL DEFAULT '',
 278   "date" varchar(255) NOT NULL DEFAULT '',
 279 # date when assurance was entered
 280   "when" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
 281 #?
 282   "expire" timestamp NULL DEFAULT NULL,
 283 #?????????????????
 284   "sponsor" int NOT NULL DEFAULT '0',
 285 # date when assurance was deleted (or 0)
 286   "deleted" timestamp NULL DEFAULT NULL,
 287   PRIMARY KEY ("id")
 288 );
 289
 290 CREATE INDEX ON "notary"("from");
 291 CREATE INDEX ON "notary"("to");
 292 CREATE INDEX ON "notary"("when");
 293 CREATE INDEX ON "notary"("method");
 294
 295
 296 DROP TABLE IF EXISTS "cats_passed";
 297 CREATE TABLE "cats_passed" (
 298   "id" serial NOT NULL,
 299   "user_id" int NOT NULL,
 300   "variant_id" int NOT NULL,
 301   "pass_date" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
 302   PRIMARY KEY ("id"),
 303   UNIQUE ("user_id","variant_id","pass_date")
 304 );
 305
 306 # --------------------------------------------------------
 307
 308 #
 309 # Table structure for table "cats_type"
 310 #
 311
 312 DROP TABLE IF EXISTS "cats_type";
 313 CREATE TABLE "cats_type" (
 314   "id" serial NOT NULL,
 315   "type_text" varchar(255) NOT NULL,
 316   PRIMARY KEY ("id"),
 317   UNIQUE ("type_text")
 318 );
 319
 320 DROP TABLE IF EXISTS "arbitrations";
 321 CREATE TABLE IF NOT EXISTS "arbitrations" (
 322   "user" int NOT NULL,
 323   "arbitration" varchar(20) NOT NULL,
 324   PRIMARY KEY ("user","arbitration")
 325 );
 326
 327 DROP TABLE IF EXISTS "user_groups";
 328
 329 DROP TYPE IF EXISTS "userGroup";
 330 CREATE TYPE "userGroup" AS enum('supporter','arbitrator','blockedassuree','blockedassurer','blockedlogin','ttp-assurer','ttp-applicant', 'codesigning', 'orgassurer', 'blockedcert');
 331
 332 CREATE TABLE IF NOT EXISTS "user_groups" (
 333   "id" serial NOT NULL,
 334   "user" int NOT NULL,
 335   "permission" "userGroup" NOT NULL,
 336   "granted" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
 337   "deleted" timestamp NULL DEFAULT NULL,
 338   "grantedby" int NOT NULL,
 339   "revokedby" int DEFAULT NULL,
 340   PRIMARY KEY ("id")
 341 );
 342
 343 DROP TABLE IF EXISTS "org_admin";
 344
 345 DROP TYPE IF EXISTS "yesno";
 346 CREATE TYPE "yesno" AS enum('y', 'n');
 347
 348
 349 CREATE TABLE IF NOT EXISTS "org_admin" (
 350   "orgid" int NOT NULL,
 351   "memid" int NOT NULL,
 352   "master" "yesno" NOT NULL,
 353   "creator" int NOT NULL,
 354   "created" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
 355   "deleter" int NULL DEFAULT NULL,
 356   "deleted" timestamp NULL DEFAULT NULL
 357 );
 358 CREATE INDEX ON "org_admin"("orgid", "memid");
 359
 360
 361 DROP TABLE IF EXISTS "adminLog";
 362 CREATE TABLE "adminLog" (
 363   "when" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
 364   "uid" int NOT NULL,
 365   "admin" int NOT NULL,
 366   "type" varchar(100) NOT NULL DEFAULT '',
 367   "information" varchar(50) NOT NULL DEFAULT ''
 368 );
 369 CREATE INDEX ON "adminLog"("when");
 370
 371
 372 DROP TABLE IF EXISTS "schemeVersion";
 373 CREATE TABLE "schemeVersion" (
 374   "version" smallint NOT NULL,
 375   PRIMARY KEY ("version")
 376 );
 377 INSERT INTO "schemeVersion" (version)  VALUES(8);
 378
 379 DROP TABLE IF EXISTS `passwordResetTickets`;
 380 CREATE TABLE `passwordResetTickets` (
 381   `id` serial NOT NULL,
 382   `memid` int NOT NULL,
 383   `creator` int NOT NULL,
 384   `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
 385   `used` timestamp NULL DEFAULT NULL,
 386   `token` varchar(32) NOT NULL,
 387   `private_token` varchar(255) NOT NULL,
 388   PRIMARY KEY (`id`)
 389 );