add: Allow multiple names, name-schemes, multi-name-assurance, etc.

[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 cc2aebd01f5f01f7fa5b09c24295b357f8af9003..883adf769870fbf1d30085454c5b3ea25e0d3d02 100644 (file)
--- 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 '',
   "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 '',
   PRIMARY KEY ("id")
 );
   "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");
 
 CREATE INDEX ON "users" ("email");
 CREATE INDEX ON "users" ("verified");
 


@@ -338,7 +327,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');

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


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

-INSERT INTO "schemeVersion" (version)  VALUES(17);
+INSERT INTO "schemeVersion" (version)  VALUES(20);

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


@@ -663,3 +652,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
+);