1.1 --- a/core.sql	Thu Jul 08 20:58:22 2010 +0200
     1.2 +++ b/core.sql	Wed Jul 14 13:28:28 2010 +0200
     1.3 @@ -6,7 +6,7 @@
     1.4  BEGIN;
     1.5  
     1.6  CREATE VIEW "liquid_feedback_version" AS
     1.7 -  SELECT * FROM (VALUES ('1.1.0', 1, 1, 0))
     1.8 +  SELECT * FROM (VALUES ('1.2.0', 1, 2, 0))
     1.9    AS "subquery"("string", "major", "minor", "revision");
    1.10  
    1.11  
    1.12 @@ -120,15 +120,14 @@
    1.13          "id"                    SERIAL8         PRIMARY KEY,
    1.14          "member_id"             INT4            NOT NULL REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
    1.15          "until"                 TIMESTAMPTZ     NOT NULL DEFAULT now(),
    1.16 -        "login"                 TEXT,
    1.17          "active"                BOOLEAN         NOT NULL,
    1.18          "name"                  TEXT            NOT NULL );
    1.19  CREATE INDEX "member_history_member_id_idx" ON "member_history" ("member_id");
    1.20  
    1.21 -COMMENT ON TABLE "member_history" IS 'Filled by trigger; keeps information about old names, login names and active flag of members';
    1.22 +COMMENT ON TABLE "member_history" IS 'Filled by trigger; keeps information about old names and active flag of members';
    1.23  
    1.24  COMMENT ON COLUMN "member_history"."id"    IS 'Primary key, which can be used to sort entries correctly (and time warp resistant)';
    1.25 -COMMENT ON COLUMN "member_history"."until" IS 'Timestamp until the name and login had been valid';
    1.26 +COMMENT ON COLUMN "member_history"."until" IS 'Timestamp until the data was valid';
    1.27  
    1.28  
    1.29  CREATE TABLE "invite_code" (
    1.30 @@ -796,16 +795,12 @@
    1.31    LANGUAGE 'plpgsql' VOLATILE AS $$
    1.32      BEGIN
    1.33        IF
    1.34 -        ( NEW."login" NOTNULL AND OLD."login" NOTNULL AND
    1.35 -          NEW."login" != OLD."login" ) OR
    1.36 -        ( NEW."login" NOTNULL AND OLD."login" ISNULL ) OR
    1.37 -        ( NEW."login" ISNULL AND OLD."login" NOTNULL ) OR
    1.38          NEW."active" != OLD."active" OR
    1.39          NEW."name"   != OLD."name"
    1.40        THEN
    1.41          INSERT INTO "member_history"
    1.42 -          ("member_id", "login", "active", "name")
    1.43 -          VALUES (NEW."id", OLD."login", OLD."active", OLD."name");
    1.44 +          ("member_id", "active", "name")
    1.45 +          VALUES (NEW."id", OLD."active", OLD."name");
    1.46        END IF;
    1.47        RETURN NULL;
    1.48      END;
    1.49 @@ -816,7 +811,7 @@
    1.50    "write_member_history_trigger"();
    1.51  
    1.52  COMMENT ON FUNCTION "write_member_history_trigger"()  IS 'Implementation of trigger "write_member_history" on table "member"';
    1.53 -COMMENT ON TRIGGER "write_member_history" ON "member" IS 'When changing name or login of a member, create a history entry in "member_history" table';
    1.54 +COMMENT ON TRIGGER "write_member_history" ON "member" IS 'When changing certain fields of a member, create a history entry in "member_history" table';
    1.55  
    1.56  
    1.57  
    1.58 @@ -3184,6 +3179,7 @@
    1.59    LANGUAGE 'plpgsql' VOLATILE AS $$
    1.60      BEGIN
    1.61        UPDATE "member" SET
    1.62 +        "last_login"                   = NULL,
    1.63          "login"                        = NULL,
    1.64          "password"                     = NULL,
    1.65          "active"                       = FALSE,
    1.66 @@ -3191,6 +3187,7 @@
    1.67          "notify_email_unconfirmed"     = NULL,
    1.68          "notify_email_secret"          = NULL,
    1.69          "notify_email_secret_expiry"   = NULL,
    1.70 +        "notify_email_lock_expiry"     = NULL,
    1.71          "password_reset_secret"        = NULL,
    1.72          "password_reset_secret_expiry" = NULL,
    1.73          "organizational_unit"          = NULL,
    1.74 @@ -3209,8 +3206,6 @@
    1.75          "statement"                    = NULL
    1.76          WHERE "id" = "member_id_p";
    1.77        -- "text_search_data" is updated by triggers
    1.78 -      UPDATE "member_history" SET "login" = NULL
    1.79 -        WHERE "member_id" = "member_id_p";
    1.80        DELETE FROM "setting"            WHERE "member_id" = "member_id_p";
    1.81        DELETE FROM "setting_map"        WHERE "member_id" = "member_id_p";
    1.82        DELETE FROM "member_relation_setting" WHERE "member_id" = "member_id_p";
    1.83 @@ -3222,11 +3217,15 @@
    1.84        DELETE FROM "suggestion_setting" WHERE "member_id" = "member_id_p";
    1.85        DELETE FROM "membership"         WHERE "member_id" = "member_id_p";
    1.86        DELETE FROM "delegation"         WHERE "truster_id" = "member_id_p";
    1.87 +      DELETE FROM "direct_voter" USING "issue"
    1.88 +        WHERE "direct_voter"."issue_id" = "issue"."id"
    1.89 +        AND "issue"."closed" ISNULL
    1.90 +        AND "member_id" = "member_id_p";
    1.91        RETURN;
    1.92      END;
    1.93    $$;
    1.94  
    1.95 -COMMENT ON FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE) IS 'Clear certain settings and data of a particular member (data protection)';
    1.96 +COMMENT ON FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE) IS 'Deactivate member and clear certain settings and data of this member (data protection)';
    1.97  
    1.98  
    1.99  CREATE FUNCTION "delete_private_data"()
   1.100 @@ -3234,12 +3233,14 @@
   1.101    LANGUAGE 'plpgsql' VOLATILE AS $$
   1.102      BEGIN
   1.103        UPDATE "member" SET
   1.104 +        "last_login"                   = NULL,
   1.105          "login"                        = NULL,
   1.106          "password"                     = NULL,
   1.107          "notify_email"                 = NULL,
   1.108          "notify_email_unconfirmed"     = NULL,
   1.109          "notify_email_secret"          = NULL,
   1.110          "notify_email_secret_expiry"   = NULL,
   1.111 +        "notify_email_lock_expiry"     = NULL,
   1.112          "password_reset_secret"        = NULL,
   1.113          "password_reset_secret_expiry" = NULL,
   1.114          "organizational_unit"          = NULL,
   1.115 @@ -3257,7 +3258,6 @@
   1.116          "external_posts"               = NULL,
   1.117          "statement"                    = NULL;
   1.118        -- "text_search_data" is updated by triggers
   1.119 -      UPDATE "member_history" SET "login" = NULL;
   1.120        DELETE FROM "invite_code";
   1.121        DELETE FROM "setting";
   1.122        DELETE FROM "setting_map";