upd: move revocation info to own table

[gigi.git] / src / club / wpia / gigi / database / tableStructure.sql

diff --git a/src/club/wpia/gigi/database/tableStructure.sql b/src/club/wpia/gigi/database/tableStructure.sql
index 304d85d995b3f71cf5959b1a74c3a5e452389859..0662bf18d24228575818c4a806de06619ba5cadc 100644 (file)
--- a/src/club/wpia/gigi/database/tableStructure.sql
+++ b/src/club/wpia/gigi/database/tableStructure.sql
@@ -139,7 +139,7 @@ CREATE TABLE "user_agreements" (
 DROP TABLE IF EXISTS "certs";
 
 DROP TYPE IF EXISTS "mdType";
 DROP TABLE IF EXISTS "certs";
 
 DROP TYPE IF EXISTS "mdType";

-CREATE TYPE "mdType" AS ENUM('md5','sha1','sha256','sha512');
+CREATE TYPE "mdType" AS ENUM('md5','sha1','sha256','sha384','sha512');

 
 DROP TYPE IF EXISTS "csrType";
 CREATE TYPE "csrType" AS ENUM ('CSR', 'SPKAC');
 
 DROP TYPE IF EXISTS "csrType";
 CREATE TYPE "csrType" AS ENUM ('CSR', 'SPKAC');


@@ -159,7 +159,6 @@ CREATE TABLE "certs" (
   "crt_name" varchar(255) NOT NULL DEFAULT '',
   "created" timestamp NULL DEFAULT NULL,
   "modified" timestamp NULL DEFAULT NULL,
   "crt_name" varchar(255) NOT NULL DEFAULT '',
   "created" timestamp NULL DEFAULT NULL,
   "modified" timestamp NULL DEFAULT NULL,

-  "revoked" timestamp NULL DEFAULT NULL,

   "expire" timestamp NULL DEFAULT NULL,
   "renewed" boolean NOT NULL DEFAULT 'false',
   "pkhash" char(40) DEFAULT NULL,
   "expire" timestamp NULL DEFAULT NULL,
   "renewed" boolean NOT NULL DEFAULT 'false',
   "pkhash" char(40) DEFAULT NULL,


@@ -168,13 +167,23 @@ CREATE TABLE "certs" (
   PRIMARY KEY ("id")
 );
 CREATE INDEX ON "certs" ("pkhash");
   PRIMARY KEY ("id")
 );
 CREATE INDEX ON "certs" ("pkhash");

-CREATE INDEX ON "certs" ("revoked");

 CREATE INDEX ON "certs" ("created");
 CREATE INDEX ON "certs" ("memid");
 CREATE INDEX ON "certs" ("serial");
 CREATE INDEX ON "certs" ("expire");
 CREATE INDEX ON "certs" ("crt_name");
 
 CREATE INDEX ON "certs" ("created");
 CREATE INDEX ON "certs" ("memid");
 CREATE INDEX ON "certs" ("serial");
 CREATE INDEX ON "certs" ("expire");
 CREATE INDEX ON "certs" ("crt_name");
 

+DROP TABLE IF EXISTS "certsRevoked";
+DROP TYPE IF EXISTS "revocationType";
+CREATE TYPE "revocationType" AS ENUM('user', 'support', 'ping_timeout');
+CREATE TABLE "certsRevoked" (
+  "id" int NOT NULL,
+  -- the time when the certificate was revoked by cassiopeia (and that is stored in the CRL)
+  -- NULL indicated the revocation is pending
+  "revoked" timestamp NULL,
+  "type" "revocationType" NOT NULL,
+  PRIMARY KEY ("id")
+);

 
 
 DROP TABLE IF EXISTS "certAvas";
 
 
 DROP TABLE IF EXISTS "certAvas";


@@ -328,7 +337,7 @@ CREATE TABLE IF NOT EXISTS "arbitrations" (
 DROP TABLE IF EXISTS "user_groups";
 
 DROP TYPE IF EXISTS "userGroup";
 DROP TABLE IF EXISTS "user_groups";
 
 DROP TYPE IF EXISTS "userGroup";

-CREATE TYPE "userGroup" AS enum('supporter','arbitrator','blockedassuree','blockedassurer','blockedlogin','ttp-assurer','ttp-applicant', 'codesigning', 'orgassurer', 'blockedcert', 'nucleus-assurer', 'locate-agent', 'verify-notification');
+CREATE TYPE "userGroup" AS enum('supporter','blocked-applicant','blocked-agent','blocked-login','ttp-agent','ttp-applicant', 'codesigning', 'org-agent', 'blocked-cert', 'nucleus-agent', 'locate-agent', 'verify-notification');

 
 CREATE TABLE IF NOT EXISTS "user_groups" (
   "id" serial NOT NULL,
 
 CREATE TABLE IF NOT EXISTS "user_groups" (
   "id" serial NOT NULL,


@@ -375,7 +384,7 @@ CREATE TABLE "schemeVersion" (
   "version" smallint NOT NULL,
   PRIMARY KEY ("version")
 );
   "version" smallint NOT NULL,
   PRIMARY KEY ("version")
 );

-INSERT INTO "schemeVersion" (version)  VALUES(26);
+INSERT INTO "schemeVersion" (version)  VALUES(29);

 
 DROP TABLE IF EXISTS `passwordResetTickets`;
 CREATE TABLE `passwordResetTickets` (
 
 DROP TABLE IF EXISTS `passwordResetTickets`;
 CREATE TABLE `passwordResetTickets` (