X-Git-Url: https://code.wpia.club/?p=gigi.git;a=blobdiff_plain;f=src%2Forg%2Fcacert%2Fgigi%2Fdatabase%2FtableStructure.sql;h=883adf769870fbf1d30085454c5b3ea25e0d3d02;hp=cc2aebd01f5f01f7fa5b09c24295b357f8af9003;hb=9def69bd08ea69eb27786d5b34f00e154e09e9f3;hpb=f3854f902e8c2c14bcc3c16baf82134ca5147494

diff --git a/src/org/cacert/gigi/database/tableStructure.sql b/src/org/cacert/gigi/database/tableStructure.sql
index cc2aebd0..883adf76 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");
 
@@ -338,7 +327,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,
@@ -385,7 +374,7 @@ CREATE TABLE "schemeVersion" (
   "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` (
@@ -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);
 
+
+
+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
+);