liquid_feedback_core

changeset 225:18e1cd384528

No generic NOT NULL constraints on columns "name" and "notify_level" in table "member"
author jbe
date Tue Feb 28 11:59:19 2012 +0100 (2012-02-28)
parents 06b9c347fd61
children 10a231cfd585
files core.sql update/core-update.v2.0.3-v2.0.4.sql
line diff
     1.1 --- a/core.sql	Sat Feb 25 16:37:18 2012 +0100
     1.2 +++ b/core.sql	Tue Feb 28 11:59:19 2012 +0100
     1.3 @@ -100,10 +100,10 @@
     1.4          "notify_email_secret"          TEXT     UNIQUE,
     1.5          "notify_email_secret_expiry"   TIMESTAMPTZ,
     1.6          "notify_email_lock_expiry"     TIMESTAMPTZ,
     1.7 -        "notify_level"          "notify_level"  NOT NULL DEFAULT 'none',
     1.8 +        "notify_level"          "notify_level",
     1.9          "password_reset_secret"        TEXT     UNIQUE,
    1.10          "password_reset_secret_expiry" TIMESTAMPTZ,
    1.11 -        "name"                  TEXT            NOT NULL UNIQUE,
    1.12 +        "name"                  TEXT            UNIQUE,
    1.13          "identification"        TEXT            UNIQUE,
    1.14          "authentication"        TEXT,
    1.15          "organizational_unit"   TEXT,
    1.16 @@ -123,7 +123,9 @@
    1.17          "statement"             TEXT,
    1.18          "text_search_data"      TSVECTOR,
    1.19          CONSTRAINT "active_requires_activated_and_last_activity"
    1.20 -          CHECK ("active" = FALSE OR ("activated" NOTNULL AND "last_activity" NOTNULL)) );
    1.21 +          CHECK ("active" = FALSE OR ("activated" NOTNULL AND "last_activity" NOTNULL)),
    1.22 +        CONSTRAINT "name_not_null_if_activated"
    1.23 +          CHECK ("activated" ISNULL OR "name" NOTNULL) );
    1.24  CREATE INDEX "member_active_idx" ON "member" ("active");
    1.25  CREATE INDEX "member_text_search_data_idx" ON "member" USING gin ("text_search_data");
    1.26  CREATE TRIGGER "update_text_search_data"
    1.27 @@ -152,8 +154,8 @@
    1.28  COMMENT ON COLUMN "member"."notify_email_secret"        IS 'Secret sent to the address in "notify_email_unconformed"';
    1.29  COMMENT ON COLUMN "member"."notify_email_secret_expiry" IS 'Expiry date/time for "notify_email_secret"';
    1.30  COMMENT ON COLUMN "member"."notify_email_lock_expiry"   IS 'Date/time until no further email confirmation mails may be sent (abuse protection)';
    1.31 -COMMENT ON COLUMN "member"."notify_level"         IS 'Selects which event notifications are to be sent to the "notify_email" mail address';
    1.32 -COMMENT ON COLUMN "member"."name"                 IS 'Distinct name of the member';
    1.33 +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';
    1.34 +COMMENT ON COLUMN "member"."name"                 IS 'Distinct name of the member, may be NULL if account has not been activated yet';
    1.35  COMMENT ON COLUMN "member"."identification"       IS 'Optional identification number or code of the member';
    1.36  COMMENT ON COLUMN "member"."authentication"       IS 'Information about how this member was authenticated';
    1.37  COMMENT ON COLUMN "member"."organizational_unit"  IS 'Branch or division of the organization the member belongs to';
     2.1 --- a/update/core-update.v2.0.3-v2.0.4.sql	Sat Feb 25 16:37:18 2012 +0100
     2.2 +++ b/update/core-update.v2.0.3-v2.0.4.sql	Tue Feb 28 11:59:19 2012 +0100
     2.3 @@ -5,6 +5,14 @@
     2.4    SELECT * FROM (VALUES ('2.0.4', 2, 0, 4))
     2.5    AS "subquery"("string", "major", "minor", "revision");
     2.6  
     2.7 +-- drop NOT NULL constraints on columns "name" and "notify_level"
     2.8 +-- in table "member", and add new constraint for "name":
     2.9 +ALTER TABLE "member" ALTER COLUMN "notify_level" DROP NOT NULL;
    2.10 +ALTER TABLE "member" ALTER COLUMN "name" DROP NOT NULL;
    2.11 +ALTER TABLE "member" ADD CONSTRAINT "name_not_null_if_activated" CHECK ("activated" ISNULL OR "name" NOTNULL);
    2.12 +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';
    2.13 +COMMENT ON COLUMN "member"."name"         IS 'Distinct name of the member, may be NULL if account has not been activated yet';
    2.14 +
    2.15  -- add table "session":
    2.16  CREATE TABLE "session" (
    2.17          "ident"                 TEXT            PRIMARY KEY,

Impressum / About Us