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   PRIMARY KEY ("id")
 107 );
 108
 109
 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)
 116 );
 117
 118 DROP TABLE IF EXISTS "baddomains";
 119 CREATE TABLE "baddomains" (
 120   "domain" varchar(255) NOT NULL DEFAULT ''
 121 );
 122
 123
 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")
 132 );
 133
 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,
 144   PRIMARY KEY ("id")
 145 );
 146
 147 DROP TABLE IF EXISTS "certs";
 148
 149 DROP TYPE IF EXISTS "mdType";
 150 CREATE TYPE "mdType" AS ENUM('md5','sha1','sha256','sha512');
 151
 152 DROP TYPE IF EXISTS "csrType";
 153 CREATE TYPE "csrType" AS ENUM ('CSR', 'SPKAC');
 154
 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,
 164
 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 '',
 177   PRIMARY KEY ("id")
 178 );
 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");
 186
 187
 188
 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,
 194
 195   PRIMARY KEY ("certId", "name")
 196 );
 197
 198 DROP TABLE IF EXISTS "clientcerts";
 199 CREATE TABLE "clientcerts" (
 200   "id" int NOT NULL,
 201   "disablelogin" boolean NOT NULL DEFAULT 'false',
 202
 203   PRIMARY KEY ("id")
 204 );
 205
 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,
 213   PRIMARY KEY ("id"),
 214   UNIQUE ("keyname")
 215 );
 216
 217 DROP TABLE IF EXISTS "subjectAlternativeNames";
 218
 219 DROP TYPE IF EXISTS "SANType";
 220 CREATE TYPE "SANType" AS ENUM ('email', 'DNS');
 221
 222 CREATE TABLE "subjectAlternativeNames" (
 223   "certId" int NOT NULL,
 224   "contents" varchar(50) NOT NULL,
 225   "type" "SANType" NOT NULL
 226 );
 227
 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,
 236   PRIMARY KEY ("id"),
 237   UNIQUE ("keyname")
 238 );
 239
 240 DROP TABLE IF EXISTS "jobs";
 241
 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');
 246
 247
 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',
 254   "executeFrom" DATE,
 255   "executeTo" VARCHAR(11),
 256   PRIMARY KEY ("id")
 257 );
 258
 259 CREATE INDEX ON "jobs" ("state");
 260
 261 DROP TABLE IF EXISTS "notary";
 262
 263 DROP TYPE IF EXISTS "notaryType";
 264 CREATE TYPE "notaryType" AS enum('Face to Face Meeting', 'TOPUP', 'TTP-Assisted');
 265
 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,
 280 #?
 281   "expire" timestamp NULL DEFAULT NULL,
 282 #?????????????????
 283   "sponsor" int NOT NULL DEFAULT '0',
 284 # date when assurance was deleted (or 0)
 285   "deleted" timestamp NULL DEFAULT NULL,
 286   PRIMARY KEY ("id")
 287 );
 288
 289 CREATE INDEX ON "notary"("from");
 290 CREATE INDEX ON "notary"("to");
 291 CREATE INDEX ON "notary"("when");
 292 CREATE INDEX ON "notary"("method");
 293
 294
 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,
 301   PRIMARY KEY ("id"),
 302   UNIQUE ("user_id","variant_id","pass_date")
 303 );
 304
 305 # --------------------------------------------------------
 306
 307 #
 308 # Table structure for table "cats_type"
 309 #
 310
 311 DROP TABLE IF EXISTS "cats_type";
 312 CREATE TABLE "cats_type" (
 313   "id" serial NOT NULL,
 314   "type_text" varchar(255) NOT NULL,
 315   PRIMARY KEY ("id"),
 316   UNIQUE ("type_text")
 317 );
 318
 319 DROP TABLE IF EXISTS "arbitrations";
 320 CREATE TABLE IF NOT EXISTS "arbitrations" (
 321   "user" int NOT NULL,
 322   "arbitration" varchar(20) NOT NULL,
 323   PRIMARY KEY ("user","arbitration")
 324 );
 325
 326 DROP TABLE IF EXISTS "user_groups";
 327
 328 DROP TYPE IF EXISTS "userGroup";
 329 CREATE TYPE "userGroup" AS enum('supporter','arbitrator','blockedassuree','blockedassurer','blockedlogin','ttp-assurer','ttp-applicant', 'codesigning', 'orgassurer');
 330
 331 CREATE TABLE IF NOT EXISTS "user_groups" (
 332   "id" serial NOT NULL,
 333   "user" int 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,
 339   PRIMARY KEY ("id")
 340 );
 341
 342 DROP TABLE IF EXISTS "org_admin";
 343
 344 DROP TYPE IF EXISTS "yesno";
 345 CREATE TYPE "yesno" AS enum('y', 'n');
 346
 347
 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
 356 );
 357 CREATE INDEX ON "org_admin"("orgid", "memid");
 358
 359
 360 DROP TABLE IF EXISTS "adminLog";
 361 CREATE TABLE "adminLog" (
 362   "when" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
 363   "uid" int NOT NULL,
 364   "admin" int NOT NULL,
 365   "type" varchar(100) NOT NULL DEFAULT '',
 366   "information" varchar(50) NOT NULL DEFAULT ''
 367 );
 368 CREATE INDEX ON "adminLog"("when");
 369
 370
 371 DROP TABLE IF EXISTS "schemeVersion";
 372 CREATE TABLE "schemeVersion" (
 373   "version" smallint NOT NULL,
 374   PRIMARY KEY ("version")
 375 );
 376 INSERT INTO "schemeVersion" (version)  VALUES(5);