# HG changeset patch # User jbe # Date 1287266195 -7200 # Node ID b0866d51b754e458fc8633bd04cd35224554f08e # Parent bb04e4d1c68cc756bd200af2a61d998e984558da Backed out changesets dfa8e6a1f1e7, ca13b2614d10, and partly 0758a2e02620 - auto_support feature shall be added but implemented in a different way - Delegating to non-active members shall be possible in database - Incoming delegations MUST NOT be deleted when calling "delete_member" - Index on "initiative"("issue_id") is not neccessary due to UNIQUE ("issue_id", "id") statement diff -r bb04e4d1c68c -r b0866d51b754 core.sql --- a/core.sql Sat Oct 16 23:39:24 2010 +0200 +++ b/core.sql Sat Oct 16 23:56:35 2010 +0200 @@ -446,7 +446,6 @@ CHECK (("agreed" NOTNULL AND "agreed" = TRUE) OR "rank" ISNULL) ); CREATE INDEX "initiative_created_idx" ON "initiative" ("created"); CREATE INDEX "initiative_revoked_idx" ON "initiative" ("revoked"); -CREATE INDEX "initiative_issue_id_idx" ON "initiative" ("issue_id"); CREATE INDEX "initiative_text_search_data_idx" ON "initiative" USING gin ("text_search_data"); CREATE TRIGGER "update_text_search_data" BEFORE INSERT OR UPDATE ON "initiative" @@ -615,32 +614,16 @@ "initiative_id" INT4, "member_id" INT4, "draft_id" INT8 NOT NULL, - "auto_support" BOOLEAN NOT NULL DEFAULT 'f', + "auto_support" BOOLEAN NOT NULL DEFAULT FALSE, FOREIGN KEY ("issue_id", "member_id") REFERENCES "interest" ("issue_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE, FOREIGN KEY ("initiative_id", "draft_id") REFERENCES "draft" ("initiative_id", "id") ON DELETE CASCADE ON UPDATE CASCADE ); CREATE INDEX "supporter_member_id_idx" ON "supporter" ("member_id"); COMMENT ON TABLE "supporter" IS 'Members who support an initiative (conditionally); Frontends must ensure that supporters are not added or removed from fully_frozen or closed initiatives.'; -COMMENT ON COLUMN "supporter"."draft_id" IS 'Latest seen draft, defaults to current draft of the initiative (implemented by trigger "default_for_draft_id")'; - -CREATE FUNCTION update_supporter_drafts() - RETURNS trigger - LANGUAGE 'plpgsql' VOLATILE AS $$ - BEGIN - UPDATE supporter SET draft_id = NEW.id - WHERE initiative_id = NEW.initiative_id AND - (auto_support = 't' OR member_id = NEW.author_id); - RETURN new; - END -$$; - -CREATE TRIGGER "update_draft_supporter" - AFTER INSERT ON "draft" - FOR EACH ROW EXECUTE PROCEDURE - update_supporter_drafts(); - -COMMENT ON FUNCTION "update_supporter_drafts"() IS 'Automaticly update the supported draft_id to the latest version when auto_support is enabled'; +COMMENT ON COLUMN "supporter"."draft_id" IS 'Latest seen draft, defaults to current draft of the initiative (implemented by trigger "default_for_draft_id")'; +COMMENT ON COLUMN "supporter"."auto_support" IS 'Supporting member does not want to confirm new drafts of the initiative'; + CREATE TABLE "opinion" ( "initiative_id" INT4 NOT NULL, @@ -687,26 +670,6 @@ COMMENT ON COLUMN "delegation"."area_id" IS 'Reference to area, if delegation is area-wide, otherwise NULL'; COMMENT ON COLUMN "delegation"."issue_id" IS 'Reference to issue, if delegation is issue-wide, otherwise NULL'; -CREATE FUNCTION "check_delegation"() - RETURNS TRIGGER - LANGUAGE 'plpgsql' VOLATILE AS $$ - BEGIN - IF EXISTS ( - SELECT NULL FROM "member" WHERE - "id" = NEW."trustee_id" AND active = 'n' - ) THEN - RAISE EXCEPTION 'Cannot delegate to an inactive member'; - END IF; - RETURN NEW; - END; -$$; - -CREATE TRIGGER "update_delegation" - BEFORE INSERT OR UPDATE ON "delegation" - FOR EACH ROW EXECUTE PROCEDURE - check_delegation(); - -COMMENT ON FUNCTION "check_delegation"() IS 'Sanity checks for new delegation. Dont allow delegations to inactive members'; CREATE TABLE "direct_population_snapshot" ( PRIMARY KEY ("issue_id", "event", "member_id"), @@ -3416,7 +3379,6 @@ DELETE FROM "suggestion_setting" WHERE "member_id" = "member_id_p"; DELETE FROM "membership" WHERE "member_id" = "member_id_p"; DELETE FROM "delegation" WHERE "truster_id" = "member_id_p"; - DELETE FROM "delegation" WHERE "trustee_id" = "member_id_p"; DELETE FROM "direct_voter" USING "issue" WHERE "direct_voter"."issue_id" = "issue"."id" AND "issue"."closed" ISNULL diff -r bb04e4d1c68c -r b0866d51b754 update/core-update.v1.2.8-v1.2.9.sql --- a/update/core-update.v1.2.8-v1.2.9.sql Sat Oct 16 23:39:24 2010 +0200 +++ b/update/core-update.v1.2.8-v1.2.9.sql Sat Oct 16 23:56:35 2010 +0200 @@ -4,100 +4,7 @@ SELECT * FROM (VALUES ('1.2.9', 1, 2, 9)) AS "subquery"("string", "major", "minor", "revision"); - -ALTER TABLE "supporter" ADD "auto_support" BOOLEAN NOT NULL DEFAULT 'f'; - -CREATE FUNCTION VOLATILE update_supporter_drafts() - RETURNS trigger - LANGUAGE 'plpgsql' AS $$ - BEGIN - UPDATE supporter SET draft_id = NEW.id - WHERE initiative_id = NEW.initiative_id AND - (auto_support = 't' OR member_id = NEW.author_id); - RETURN new; - END -$$; - -COMMENT ON FUNCTION "update_supporter_drafts"() IS 'Automaticly update the supported draft_id to the latest version when auto_support is enabled'; - -CREATE TRIGGER "update_draft_supporter" - AFTER INSERT ON "draft" - FOR EACH ROW EXECUTE PROCEDURE - update_supporter_drafts(); - -CREATE FUNCTION "check_delegation"() - RETURNS TRIGGER - LANGUAGE 'plpgsql' VOLATILE AS $$ - BEGIN - IF EXISTS ( - SELECT NULL FROM "member" WHERE - "id" = NEW."trustee_id" AND active = 'n' - ) THEN - RAISE EXCEPTION 'Cannot delegate to an inactive member'; - END IF; - RETURN NEW; - END; -$$; - -CREATE TRIGGER "update_delegation" - BEFORE INSERT OR UPDATE ON "delegation" - FOR EACH ROW EXECUTE PROCEDURE - check_delegation(); - -CREATE OR REPLACE FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE) - RETURNS VOID - LANGUAGE 'plpgsql' VOLATILE AS $$ - BEGIN - UPDATE "member" SET - "last_login" = NULL, - "login" = NULL, - "password" = NULL, - "active" = FALSE, - "notify_email" = NULL, - "notify_email_unconfirmed" = NULL, - "notify_email_secret" = NULL, - "notify_email_secret_expiry" = NULL, - "notify_email_lock_expiry" = NULL, - "password_reset_secret" = NULL, - "password_reset_secret_expiry" = NULL, - "organizational_unit" = NULL, - "internal_posts" = NULL, - "realname" = NULL, - "birthday" = NULL, - "address" = NULL, - "email" = NULL, - "xmpp_address" = NULL, - "website" = NULL, - "phone" = NULL, - "mobile_phone" = NULL, - "profession" = NULL, - "external_memberships" = NULL, - "external_posts" = NULL, - "statement" = NULL - WHERE "id" = "member_id_p"; - -- "text_search_data" is updated by triggers - DELETE FROM "setting" WHERE "member_id" = "member_id_p"; - DELETE FROM "setting_map" WHERE "member_id" = "member_id_p"; - DELETE FROM "member_relation_setting" WHERE "member_id" = "member_id_p"; - DELETE FROM "member_image" WHERE "member_id" = "member_id_p"; - DELETE FROM "contact" WHERE "member_id" = "member_id_p"; - DELETE FROM "area_setting" WHERE "member_id" = "member_id_p"; - DELETE FROM "issue_setting" WHERE "member_id" = "member_id_p"; - DELETE FROM "initiative_setting" WHERE "member_id" = "member_id_p"; - DELETE FROM "suggestion_setting" WHERE "member_id" = "member_id_p"; - DELETE FROM "membership" WHERE "member_id" = "member_id_p"; - DELETE FROM "delegation" WHERE "truster_id" = "member_id_p"; - DELETE FROM "delegation" WHERE "trustee_id" = "member_id_p"; - DELETE FROM "direct_voter" USING "issue" - WHERE "direct_voter"."issue_id" = "issue"."id" - AND "issue"."closed" ISNULL - AND "member_id" = "member_id_p"; - RETURN; - END; - $$; - -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)'; - -CREATE INDEX "initiative_issue_id_idx" ON "initiative" ("issue_id"); +ALTER TABLE "supporter" ADD COLUMN + "auto_support" BOOLEAN NOT NULL DEFAULT FALSE; COMMIT;