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