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 4b5194f77c1ac67d9f274dd324ee3531cf20fcf6..0662bf18d24228575818c4a806de06619ba5cadc 100644 (file)
--- a/src/club/wpia/gigi/database/tableStructure.sql
+++ b/src/club/wpia/gigi/database/tableStructure.sql
@@ -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";


@@ -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(28);
+INSERT INTO "schemeVersion" (version)  VALUES(29);

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