fix: greatly improve performance of often-executed ping-fetch-query

[gigi.git] / src / org / cacert / gigi / database / tableStructure.sql

diff --git a/src/org/cacert/gigi/database/tableStructure.sql b/src/org/cacert/gigi/database/tableStructure.sql
index 1adb2057e2b839940490b38406aa5ac9a61f2919..304d85d995b3f71cf5959b1a74c3a5e452389859 100644 (file)
--- a/src/org/cacert/gigi/database/tableStructure.sql
+++ b/src/org/cacert/gigi/database/tableStructure.sql
@@ -12,23 +12,13 @@ CREATE TABLE "users" (
   "id" int NOT NULL,
   "email" varchar(255) NOT NULL DEFAULT '',
   "password" varchar(255) NOT NULL DEFAULT '',
   "id" int NOT NULL,
   "email" varchar(255) NOT NULL DEFAULT '',
   "password" varchar(255) NOT NULL DEFAULT '',

-  "fname" varchar(255) NOT NULL DEFAULT '',
-  "mname" varchar(255) NOT NULL DEFAULT '',
-  "lname" varchar(255) NOT NULL DEFAULT '',
-  "suffix" varchar(50) NOT NULL DEFAULT '',
+  "preferredName" int NULL,

   "dob" date NOT NULL,
   "verified" boolean NOT NULL DEFAULT 'false',
   "dob" date NOT NULL,
   "verified" boolean NOT NULL DEFAULT 'false',

-  "ccid" int NOT NULL DEFAULT '0',
-  "regid" int NOT NULL DEFAULT '0',
-  "locid" int NOT NULL DEFAULT '0',
-  "listme" boolean NOT NULL DEFAULT 'false',
-  "contactinfo" varchar(255) NOT NULL DEFAULT '',

   "language" varchar(5) NOT NULL DEFAULT '',
   "language" varchar(5) NOT NULL DEFAULT '',

+  "country" varchar(2) NULL,

   PRIMARY KEY ("id")
 );
   PRIMARY KEY ("id")
 );

-CREATE INDEX ON "users" ("ccid");
-CREATE INDEX ON "users" ("regid");
-CREATE INDEX ON "users" ("locid");

 CREATE INDEX ON "users" ("email");
 CREATE INDEX ON "users" ("verified");
 
 CREATE INDEX ON "users" ("email");
 CREATE INDEX ON "users" ("verified");
 


@@ -37,9 +27,9 @@ DROP TABLE IF EXISTS "organisations";
 CREATE TABLE IF NOT EXISTS "organisations" (
   "id" int NOT NULL,
   "name" varchar(64) NOT NULL,
 CREATE TABLE IF NOT EXISTS "organisations" (
   "id" int NOT NULL,
   "name" varchar(64) NOT NULL,

-  "state" varchar(2) NOT NULL,
-  "province" varchar(100) NOT NULL,
-  "city" varchar(100) NOT NULL,
+  "country" varchar(2) NOT NULL,
+  "province" varchar(128) NOT NULL,
+  "city" varchar(128) NOT NULL,

   "contactEmail" varchar(100) NOT NULL,
   "creator" int NOT NULL,
   "optional_name" text,
   "contactEmail" varchar(100) NOT NULL,
   "creator" int NOT NULL,
   "optional_name" text,


@@ -115,6 +105,7 @@ CREATE TABLE "domainPinglog" (
   "challenge" varchar(16),
   "result" varchar(255)
 );
   "challenge" varchar(16),
   "result" varchar(255)
 );

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

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


@@ -171,7 +162,6 @@ CREATE TABLE "certs" (
   "revoked" timestamp NULL DEFAULT NULL,
   "expire" timestamp NULL DEFAULT NULL,
   "renewed" boolean NOT NULL DEFAULT 'false',
   "revoked" timestamp NULL DEFAULT NULL,
   "expire" timestamp NULL DEFAULT NULL,
   "renewed" boolean NOT NULL DEFAULT 'false',

-  "disablelogin" boolean NOT NULL DEFAULT 'false',

   "pkhash" char(40) DEFAULT NULL,
   "certhash" char(40) DEFAULT NULL,
   "description" varchar(100) NOT NULL DEFAULT '',
   "pkhash" char(40) DEFAULT NULL,
   "certhash" char(40) DEFAULT NULL,
   "description" varchar(100) NOT NULL DEFAULT '',


@@ -196,10 +186,9 @@ CREATE TABLE "certAvas" (
   PRIMARY KEY ("certId", "name")
 );
 
   PRIMARY KEY ("certId", "name")
 );
 

-DROP TABLE IF EXISTS "clientcerts";
-CREATE TABLE "clientcerts" (
+DROP TABLE IF EXISTS "logincerts";
+CREATE TABLE "logincerts" (

   "id" int NOT NULL,
   "id" int NOT NULL,

-  "disablelogin" boolean NOT NULL DEFAULT 'false',

 
   PRIMARY KEY ("id")
 );
 
   PRIMARY KEY ("id")
 );


@@ -272,18 +261,19 @@ CREATE TABLE "notary" (
   "points" int NOT NULL DEFAULT '0',
 # awarded and the "experience points" are calculated virtually
 # Face to Face is default, TOPUP is for the remaining 30Points after two TTP
   "points" int NOT NULL DEFAULT '0',
 # awarded and the "experience points" are calculated virtually
 # Face to Face is default, TOPUP is for the remaining 30Points after two TTP

-# TTP is default ttp assurance
+# TTP is default ttp verification

   "method" "notaryType" NOT NULL DEFAULT 'Face to Face Meeting',
   "location" varchar(255) NOT NULL DEFAULT '',
   "date" varchar(255) NOT NULL DEFAULT '',
   "method" "notaryType" NOT NULL DEFAULT 'Face to Face Meeting',
   "location" varchar(255) NOT NULL DEFAULT '',
   "date" varchar(255) NOT NULL DEFAULT '',

-# date when assurance was entered
+# date when verification was entered

   "when" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
 #?
   "expire" timestamp NULL DEFAULT NULL,
 #?????????????????
   "sponsor" int NOT NULL DEFAULT '0',
   "when" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
 #?
   "expire" timestamp NULL DEFAULT NULL,
 #?????????????????
   "sponsor" int NOT NULL DEFAULT '0',

-# date when assurance was deleted (or 0)
+# date when verification was deleted (or 0)

   "deleted" timestamp NULL DEFAULT NULL,
   "deleted" timestamp NULL DEFAULT NULL,

+  "country" varchar(2) NULL,

   PRIMARY KEY ("id")
 );
 
   PRIMARY KEY ("id")
 );
 


@@ -299,6 +289,8 @@ CREATE TABLE "cats_passed" (
   "user_id" int NOT NULL,
   "variant_id" int NOT NULL,
   "pass_date" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
   "user_id" int NOT NULL,
   "variant_id" int NOT NULL,
   "pass_date" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,

+  "language" varchar(5) NOT NULL DEFAULT '',
+  "version" varchar(10) NOT NULL DEFAULT '',

   PRIMARY KEY ("id"),
   UNIQUE ("user_id","variant_id","pass_date")
 );
   PRIMARY KEY ("id"),
   UNIQUE ("user_id","variant_id","pass_date")
 );


@@ -317,6 +309,15 @@ CREATE TABLE "cats_type" (
   UNIQUE ("type_text")
 );
 
   UNIQUE ("type_text")
 );
 

+# Add values to table "cats_type"
+INSERT INTO `cats_type` (`type_text`) VALUES ('Agent Qualifying Challenge');
+INSERT INTO `cats_type` (`type_text`) VALUES ('Organisation Agent Qualifying Challenge');
+INSERT INTO `cats_type` (`type_text`) VALUES ('TTP Agent Qualifying Challenge');
+INSERT INTO `cats_type` (`type_text`) VALUES ('TTP TOPUP Agent Qualifying Challenge');
+INSERT INTO `cats_type` (`type_text`) VALUES ('Code Signing Challenge');
+INSERT INTO `cats_type` (`type_text`) VALUES ('Organisation Administrator Data Protection Challenge');
+INSERT INTO `cats_type` (`type_text`) VALUES ('Support Data Protection Challenge');
+

 DROP TABLE IF EXISTS "arbitrations";
 CREATE TABLE IF NOT EXISTS "arbitrations" (
   "user" int NOT NULL,
 DROP TABLE IF EXISTS "arbitrations";
 CREATE TABLE IF NOT EXISTS "arbitrations" (
   "user" int NOT NULL,


@@ -327,7 +328,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');
+CREATE TYPE "userGroup" AS enum('supporter','arbitrator','blockedassuree','blockedassurer','blockedlogin','ttp-assurer','ttp-applicant', 'codesigning', 'orgassurer', 'blockedcert', 'nucleus-assurer', '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,


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

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

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


@@ -652,3 +653,30 @@ INSERT INTO `countryIsoCode`(english, code2, code3, obp_id) VALUES ('South Afric
 INSERT INTO `countryIsoCode`(english, code2, code3, obp_id) VALUES ('Zambia',  'ZM',  'ZMB',  894);
 INSERT INTO `countryIsoCode`(english, code2, code3, obp_id) VALUES ('Zimbabwe',  'ZW',  'ZWE',  716);
 
 INSERT INTO `countryIsoCode`(english, code2, code3, obp_id) VALUES ('Zambia',  'ZM',  'ZMB',  894);
 INSERT INTO `countryIsoCode`(english, code2, code3, obp_id) VALUES ('Zimbabwe',  'ZW',  'ZWE',  716);
 

+
+
+DROP TABLE IF EXISTS "names";
+DROP TYPE IF EXISTS "nameSchemaType";
+CREATE TYPE "nameSchemaType" AS ENUM ('single', 'western');
+
+CREATE TABLE "names" (
+  "id" serial NOT NULL,
+  "uid" int NOT NULL,
+  "type" "nameSchemaType" NOT NULL,
+  "created" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
+  "deleted" timestamp NULL DEFAULT NULL,
+  "deprecated" timestamp NULL DEFAULT NULL,
+  PRIMARY KEY ("id")
+);
+
+
+DROP TABLE IF EXISTS "nameParts";
+DROP TYPE IF EXISTS "namePartType";
+CREATE TYPE "namePartType" AS ENUM ('first-name', 'last-name', 'single-name', 'suffix');
+
+CREATE TABLE "nameParts" (
+  "id" int NOT NULL,
+  "position" int NOT NULL,
+  "type" "namePartType" NOT NULL,
+  "value" varchar(255) NOT NULL
+);