add: handling of RA Agent Contract

[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 57825338bdf2180fd67ec1d0a287f1c42b2e076f..ef7547cedcd2907fd3f47929be290d5384568b53 100644 (file)
--- a/src/club/wpia/gigi/database/tableStructure.sql
+++ b/src/club/wpia/gigi/database/tableStructure.sql
@@ -103,9 +103,11 @@ CREATE TABLE "domainPinglog" (
   "configId" int NOT NULL,
   "state" "pingState" NOT NULL,
   "challenge" varchar(16),
   "configId" int NOT NULL,
   "state" "pingState" NOT NULL,
   "challenge" varchar(16),

-  "result" varchar(255)
+  "result" varchar(255),
+  "needsAction" boolean DEFAULT false

 );
 CREATE INDEX ON "domainPinglog" ("configId","when");
 );
 CREATE INDEX ON "domainPinglog" ("configId","when");

+CREATE INDEX ON "domainPinglog" ("when", "needsAction");

 
 DROP TABLE IF EXISTS "baddomains";
 CREATE TABLE "baddomains" (
 
 DROP TABLE IF EXISTS "baddomains";
 CREATE TABLE "baddomains" (


@@ -113,16 +115,6 @@ CREATE TABLE "baddomains" (
 );
 
 
 );
 
 

-DROP TABLE IF EXISTS "alerts";
-CREATE TABLE "alerts" (
-  "memid" int NOT NULL DEFAULT '0',
-  "general" boolean NOT NULL DEFAULT 'false',
-  "country" boolean NOT NULL DEFAULT 'false',
-  "regional" boolean NOT NULL DEFAULT 'false',
-  "radius" boolean NOT NULL DEFAULT 'false',
-  PRIMARY KEY ("memid")
-);
-

 DROP TABLE IF EXISTS "user_agreements";
 CREATE TABLE "user_agreements" (
   "id" serial NOT NULL,
 DROP TABLE IF EXISTS "user_agreements";
 CREATE TABLE "user_agreements" (
   "id" serial NOT NULL,


@@ -137,9 +129,11 @@ CREATE TABLE "user_agreements" (
 );
 
 DROP TABLE IF EXISTS "certs";
 );
 
 DROP TABLE IF EXISTS "certs";

+DROP TYPE IF EXISTS "revocationType";
+CREATE TYPE "revocationType" AS ENUM('user', 'support', 'ping_timeout', 'key_compromise');

 
 DROP TYPE IF EXISTS "mdType";
 
 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');


@@ -154,17 +148,22 @@ CREATE TABLE "certs" (
   "profile" int NOT NULL,
   "caid" int NULL DEFAULT NULL,
 
   "profile" int NOT NULL,
   "caid" int NULL DEFAULT NULL,
 

-  "csr_name" varchar(255) NOT NULL DEFAULT '',

   "csr_type" "csrType" NOT NULL,
   "csr_type" "csrType" NOT NULL,

-  "crt_name" varchar(255) NOT NULL DEFAULT '',

   "created" timestamp NULL DEFAULT NULL,
   "modified" timestamp NULL DEFAULT NULL,
   "created" timestamp NULL DEFAULT NULL,
   "modified" timestamp NULL DEFAULT NULL,

-  "revoked" timestamp NULL DEFAULT NULL,
+
+  "revoked" timestamp NULL,
+  "revocationType" "revocationType" NULL,
+  "revocationChallenge" varchar(32) NULL DEFAULT NULL,
+  "revocationSignature" text NULL DEFAULT NULL,
+  "revocationMessage" text NULL DEFAULT NULL,
+

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

+  "actorid" int NOT NULL,

   PRIMARY KEY ("id")
 );
 CREATE INDEX ON "certs" ("pkhash");
   PRIMARY KEY ("id")
 );
 CREATE INDEX ON "certs" ("pkhash");


@@ -173,9 +172,7 @@ CREATE INDEX ON "certs" ("created");
 CREATE INDEX ON "certs" ("memid");
 CREATE INDEX ON "certs" ("serial");
 CREATE INDEX ON "certs" ("expire");
 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" ("actorid");

 
 DROP TABLE IF EXISTS "certAvas";
 CREATE TABLE "certAvas" (
 
 DROP TABLE IF EXISTS "certAvas";
 CREATE TABLE "certAvas" (


@@ -240,7 +237,7 @@ CREATE TABLE "jobs" (
   "targetId" int NOT NULL,
   "task" "jobType" NOT NULL,
   "state" "jobState" NOT NULL DEFAULT 'open',
   "targetId" int NOT NULL,
   "task" "jobType" NOT NULL,
   "state" "jobState" NOT NULL DEFAULT 'open',

-  "warning" smallint NOT NULL DEFAULT '0',
+  "attempt" smallint NOT NULL DEFAULT '0',

   "executeFrom" DATE,
   "executeTo" VARCHAR(11),
   PRIMARY KEY ("id")
   "executeFrom" DATE,
   "executeTo" VARCHAR(11),
   PRIMARY KEY ("id")


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

-INSERT INTO "schemeVersion" (version)  VALUES(27);
+INSERT INTO "schemeVersion" (version)  VALUES(38);

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


@@ -680,3 +677,43 @@ CREATE TABLE "nameParts" (
   "type" "namePartType" NOT NULL,
   "value" varchar(255) NOT NULL
 );
   "type" "namePartType" NOT NULL,
   "value" varchar(255) NOT NULL
 );

+
+
+DROP TABLE IF EXISTS "certificateAttachment";
+DROP TYPE IF EXISTS "certificateAttachmentType";
+CREATE TYPE "certificateAttachmentType" AS ENUM ('CSR','CRT');
+
+CREATE TABLE "certificateAttachment" (
+  "certid" int NOT NULL,
+  "type" "certificateAttachmentType" NOT NULL,
+  "content" text NOT NULL,
+  PRIMARY KEY ("certid", "type")
+);
+
+DROP TABLE IF EXISTS "jobLog";
+CREATE TABLE "jobLog" (
+  "jobid" int NOT NULL,
+  "attempt" smallint NOT NULL,
+  "content" text NOT NULL,
+  PRIMARY KEY ("jobid", "attempt")
+);
+CREATE INDEX ON "jobLog" ("jobid");
+
+DROP TABLE IF EXISTS "user_contracts";
+DROP TYPE IF EXISTS "contractType";
+CREATE TYPE "contractType" AS ENUM ('RA Agent Contract', 'Org RA Agent Contract');
+
+CREATE TABLE "user_contracts" (
+  "id" serial NOT NULL,
+  "token" varchar(32) NOT NULL,
+  "memid" int NOT NULL,
+  "document" "contractType" NOT NULL,
+  "agentname" varchar(255) NOT NULL,
+  "datesigned" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
+  "daterevoked" timestamp DEFAULT NULL,
+  PRIMARY KEY ("id")
+);
+CREATE INDEX ON "user_contracts" ("memid");
+CREATE INDEX ON "user_contracts" ("document");
+CREATE INDEX ON "user_contracts" ("datesigned");
+CREATE INDEX ON "user_contracts" ("daterevoked");