# HG changeset patch
# User jbe
# Date 1330426759 -3600
# Node ID 18e1cd384528cb3230dd0d98ded87a1a76f0233b
# Parent  06b9c347fd611ae558b79e89d3ae1cf1a469c4fd
No generic NOT NULL constraints on columns "name" and "notify_level" in table "member"

diff -r 06b9c347fd61 -r 18e1cd384528 core.sql
--- a/core.sql	Sat Feb 25 16:37:18 2012 +0100
+++ b/core.sql	Tue Feb 28 11:59:19 2012 +0100
@@ -100,10 +100,10 @@
         "notify_email_secret"          TEXT     UNIQUE,
         "notify_email_secret_expiry"   TIMESTAMPTZ,
         "notify_email_lock_expiry"     TIMESTAMPTZ,
-        "notify_level"          "notify_level"  NOT NULL DEFAULT 'none',
+        "notify_level"          "notify_level",
         "password_reset_secret"        TEXT     UNIQUE,
         "password_reset_secret_expiry" TIMESTAMPTZ,
-        "name"                  TEXT            NOT NULL UNIQUE,
+        "name"                  TEXT            UNIQUE,
         "identification"        TEXT            UNIQUE,
         "authentication"        TEXT,
         "organizational_unit"   TEXT,
@@ -123,7 +123,9 @@
         "statement"             TEXT,
         "text_search_data"      TSVECTOR,
         CONSTRAINT "active_requires_activated_and_last_activity"
-          CHECK ("active" = FALSE OR ("activated" NOTNULL AND "last_activity" NOTNULL)) );
+          CHECK ("active" = FALSE OR ("activated" NOTNULL AND "last_activity" NOTNULL)),
+        CONSTRAINT "name_not_null_if_activated"
+          CHECK ("activated" ISNULL OR "name" NOTNULL) );
 CREATE INDEX "member_active_idx" ON "member" ("active");
 CREATE INDEX "member_text_search_data_idx" ON "member" USING gin ("text_search_data");
 CREATE TRIGGER "update_text_search_data"
@@ -152,8 +154,8 @@
 COMMENT ON COLUMN "member"."notify_email_secret"        IS 'Secret sent to the address in "notify_email_unconformed"';
 COMMENT ON COLUMN "member"."notify_email_secret_expiry" IS 'Expiry date/time for "notify_email_secret"';
 COMMENT ON COLUMN "member"."notify_email_lock_expiry"   IS 'Date/time until no further email confirmation mails may be sent (abuse protection)';
-COMMENT ON COLUMN "member"."notify_level"         IS 'Selects which event notifications are to be sent to the "notify_email" mail address';
-COMMENT ON COLUMN "member"."name"                 IS 'Distinct name of the member';
+COMMENT ON COLUMN "member"."notify_level"         IS 'Selects which event notifications are to be sent to the "notify_email" mail address, may be NULL if member did not make any selection yet';
+COMMENT ON COLUMN "member"."name"                 IS 'Distinct name of the member, may be NULL if account has not been activated yet';
 COMMENT ON COLUMN "member"."identification"       IS 'Optional identification number or code of the member';
 COMMENT ON COLUMN "member"."authentication"       IS 'Information about how this member was authenticated';
 COMMENT ON COLUMN "member"."organizational_unit"  IS 'Branch or division of the organization the member belongs to';
diff -r 06b9c347fd61 -r 18e1cd384528 update/core-update.v2.0.3-v2.0.4.sql
--- a/update/core-update.v2.0.3-v2.0.4.sql	Sat Feb 25 16:37:18 2012 +0100
+++ b/update/core-update.v2.0.3-v2.0.4.sql	Tue Feb 28 11:59:19 2012 +0100
@@ -5,6 +5,14 @@
   SELECT * FROM (VALUES ('2.0.4', 2, 0, 4))
   AS "subquery"("string", "major", "minor", "revision");
 
+-- drop NOT NULL constraints on columns "name" and "notify_level"
+-- in table "member", and add new constraint for "name":
+ALTER TABLE "member" ALTER COLUMN "notify_level" DROP NOT NULL;
+ALTER TABLE "member" ALTER COLUMN "name" DROP NOT NULL;
+ALTER TABLE "member" ADD CONSTRAINT "name_not_null_if_activated" CHECK ("activated" ISNULL OR "name" NOTNULL);
+COMMENT ON COLUMN "member"."notify_level" IS 'Selects which event notifications are to be sent to the "notify_email" mail address, may be NULL if member did not make any selection yet';
+COMMENT ON COLUMN "member"."name"         IS 'Distinct name of the member, may be NULL if account has not been activated yet';
+
 -- add table "session":
 CREATE TABLE "session" (
         "ident"                 TEXT            PRIMARY KEY,