X-Git-Url: https://code.wpia.club/?p=gigi.git;a=blobdiff_plain;f=src%2Forg%2Fcacert%2Fgigi%2Fdatabase%2FtableStructure.sql;h=a8d92f57dca3879a8c39aa2ddd5bb51c5b956598;hp=8cd29110b0bc08ed284714082ca2a099b2ecd13e;hb=b59c37e88149d0463ab4b802cacb6f0ea883acfb;hpb=aa5723dbb64ec8efa63909d39ff72364f0a5ee96

diff --git a/src/org/cacert/gigi/database/tableStructure.sql b/src/org/cacert/gigi/database/tableStructure.sql
index 8cd29110..a8d92f57 100644
--- a/src/org/cacert/gigi/database/tableStructure.sql
+++ b/src/org/cacert/gigi/database/tableStructure.sql
@@ -12,23 +12,12 @@ CREATE TABLE "users" (
   "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',
-  "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 '',
   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");
 
@@ -38,8 +27,8 @@ 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,
+  "province" varchar(128) NOT NULL,
+  "city" varchar(128) NOT NULL,
   "contactEmail" varchar(100) NOT NULL,
   "creator" int NOT NULL,
   "optional_name" text,
@@ -171,7 +160,6 @@ CREATE TABLE "certs" (
   "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 '',
@@ -196,10 +184,9 @@ CREATE TABLE "certAvas" (
   PRIMARY KEY ("certId", "name")
 );
 
-DROP TABLE IF EXISTS "clientcerts";
-CREATE TABLE "clientcerts" (
+DROP TABLE IF EXISTS "logincerts";
+CREATE TABLE "logincerts" (
   "id" int NOT NULL,
-  "disablelogin" boolean NOT NULL DEFAULT 'false',
 
   PRIMARY KEY ("id")
 );
@@ -272,18 +259,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
-# 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 '',
-# 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',
-# date when assurance was deleted (or 0)
+# date when verification was deleted (or 0)
   "deleted" timestamp NULL DEFAULT NULL,
+  "country" varchar(2) NULL,
   PRIMARY KEY ("id")
 );
 
@@ -319,6 +307,15 @@ CREATE TABLE "cats_type" (
   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,
@@ -329,7 +326,7 @@ CREATE TABLE IF NOT EXISTS "arbitrations" (
 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');
 
 CREATE TABLE IF NOT EXISTS "user_groups" (
   "id" serial NOT NULL,
@@ -376,7 +373,7 @@ CREATE TABLE "schemeVersion" (
   "version" smallint NOT NULL,
   PRIMARY KEY ("version")
 );
-INSERT INTO "schemeVersion" (version)  VALUES(15);
+INSERT INTO "schemeVersion" (version)  VALUES(22);
 
 DROP TABLE IF EXISTS `passwordResetTickets`;
 CREATE TABLE `passwordResetTickets` (
@@ -654,3 +651,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);
 
+
+
+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
+);