liquid_feedback_core

annotate update/core-update.v1.3.0-v1.3.1.sql @ 532:5855ff9e5c8f

find changesets by author, revision, files, or words in the commit message
Several changes/additions for upcoming major release

- OAuth 2.0 support
- storing profiles as JSON document
- removed subject area membership
- revised snapshot system
- additional issue limiter (dynamic quorum in subject area)
- extended event logging in "event" table
author jbe
date Thu Mar 30 19:42:38 2017 +0200 (2017-03-30)
parents 53ae1d37e5de
children
rev   line source
jbe@102 1 BEGIN;
jbe@102 2
jbe@102 3 CREATE OR REPLACE VIEW "liquid_feedback_version" AS
jbe@102 4 SELECT * FROM (VALUES ('1.3.1', 1, 3, 1))
jbe@102 5 AS "subquery"("string", "major", "minor", "revision");
jbe@102 6
jbe@104 7 CREATE TABLE "system_setting" (
jbe@104 8 "member_ttl" INTERVAL );
jbe@104 9 CREATE UNIQUE INDEX "system_setting_singleton_idx" ON "system_setting" ((1));
jbe@104 10
jbe@104 11 COMMENT ON TABLE "system_setting" IS 'This table contains only one row with different settings in each column.';
jbe@104 12 COMMENT ON INDEX "system_setting_singleton_idx" IS 'This index ensures that "system_setting" only contains one row maximum.';
jbe@104 13 COMMENT ON COLUMN "system_setting"."member_ttl" IS 'Time after members get their "active" flag set to FALSE, if they do not login anymore.';
jbe@104 14
jbe@103 15 ALTER TABLE "member" ADD COLUMN "last_login_public" DATE;
jbe@102 16 ALTER TABLE "member" ADD COLUMN "locked" BOOLEAN NOT NULL DEFAULT FALSE;
jbe@102 17
jbe@107 18 UPDATE "member" SET "locked" = TRUE WHERE "active" = FALSE;
jbe@107 19
jbe@103 20 COMMENT ON COLUMN "member"."last_login" IS 'Timestamp of last login';
jbe@103 21 COMMENT ON COLUMN "member"."last_login_public" IS 'Date of last login (time stripped for privacy reasons, updated only after day change)';
jbe@103 22 COMMENT ON COLUMN "member"."locked" IS 'Locked members can not log in.';
jbe@103 23 COMMENT ON COLUMN "member"."active" IS 'Memberships, support and votes are taken into account when corresponding members are marked as active. When the user does not log in for an extended period of time, this flag may be set to FALSE. If the user is not locked, he/she may reset the active flag by logging in.';
jbe@103 24
jbe@104 25 CREATE FUNCTION "check_last_login"()
jbe@103 26 RETURNS VOID
jbe@103 27 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@104 28 DECLARE
jbe@104 29 "system_setting_row" "system_setting"%ROWTYPE;
jbe@103 30 BEGIN
jbe@104 31 SELECT * INTO "system_setting_row" FROM "system_setting";
jbe@103 32 LOCK TABLE "member" IN SHARE ROW EXCLUSIVE MODE;
jbe@103 33 UPDATE "member" SET "last_login_public" = "last_login"::date
jbe@107 34 FROM (
jbe@107 35 SELECT DISTINCT "member"."id"
jbe@107 36 FROM "member" LEFT JOIN "member_history"
jbe@107 37 ON "member"."id" = "member_history"."member_id"
jbe@107 38 WHERE "member"."last_login"::date < 'today' OR (
jbe@107 39 "member_history"."until"::date >= 'today' AND
jbe@107 40 "member_history"."active" = FALSE AND "member"."active" = TRUE
jbe@107 41 )
jbe@107 42 ) AS "subquery"
jbe@107 43 WHERE "member"."id" = "subquery"."id";
jbe@104 44 IF "system_setting_row"."member_ttl" NOTNULL THEN
jbe@104 45 UPDATE "member" SET "active" = FALSE
jbe@104 46 WHERE "active" = TRUE
jbe@107 47 AND "last_login"::date < 'today'
jbe@104 48 AND "last_login_public" <
jbe@104 49 (now() - "system_setting_row"."member_ttl")::date;
jbe@104 50 END IF;
jbe@103 51 RETURN;
jbe@103 52 END;
jbe@103 53 $$;
jbe@103 54
jbe@107 55 COMMENT ON FUNCTION "check_last_login"() IS 'Updates "last_login_public" field, which contains the date but not the time of the last login, and deactivates members who do not login for the time specified in "system_setting"."member_ttl". For privacy reasons this function does not update "last_login_public", if the last login of a member has been today (except when member was reactivated today).';
jbe@102 56
jbe@102 57 CREATE OR REPLACE FUNCTION "create_interest_snapshot"
jbe@102 58 ( "issue_id_p" "issue"."id"%TYPE )
jbe@102 59 RETURNS VOID
jbe@102 60 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@102 61 DECLARE
jbe@102 62 "member_id_v" "member"."id"%TYPE;
jbe@102 63 BEGIN
jbe@102 64 DELETE FROM "direct_interest_snapshot"
jbe@102 65 WHERE "issue_id" = "issue_id_p"
jbe@102 66 AND "event" = 'periodic';
jbe@102 67 DELETE FROM "delegating_interest_snapshot"
jbe@102 68 WHERE "issue_id" = "issue_id_p"
jbe@102 69 AND "event" = 'periodic';
jbe@102 70 DELETE FROM "direct_supporter_snapshot"
jbe@102 71 WHERE "issue_id" = "issue_id_p"
jbe@102 72 AND "event" = 'periodic';
jbe@102 73 INSERT INTO "direct_interest_snapshot"
jbe@102 74 ("issue_id", "event", "member_id", "voting_requested")
jbe@102 75 SELECT
jbe@102 76 "issue_id_p" AS "issue_id",
jbe@102 77 'periodic' AS "event",
jbe@102 78 "member"."id" AS "member_id",
jbe@102 79 "interest"."voting_requested"
jbe@102 80 FROM "interest" JOIN "member"
jbe@102 81 ON "interest"."member_id" = "member"."id"
jbe@102 82 WHERE "interest"."issue_id" = "issue_id_p"
jbe@102 83 AND "member"."active";
jbe@102 84 FOR "member_id_v" IN
jbe@102 85 SELECT "member_id" FROM "direct_interest_snapshot"
jbe@102 86 WHERE "issue_id" = "issue_id_p"
jbe@102 87 AND "event" = 'periodic'
jbe@102 88 LOOP
jbe@102 89 UPDATE "direct_interest_snapshot" SET
jbe@102 90 "weight" = 1 +
jbe@102 91 "weight_of_added_delegations_for_interest_snapshot"(
jbe@102 92 "issue_id_p",
jbe@102 93 "member_id_v",
jbe@102 94 '{}'
jbe@102 95 )
jbe@102 96 WHERE "issue_id" = "issue_id_p"
jbe@102 97 AND "event" = 'periodic'
jbe@102 98 AND "member_id" = "member_id_v";
jbe@102 99 END LOOP;
jbe@102 100 INSERT INTO "direct_supporter_snapshot"
jbe@102 101 ( "issue_id", "initiative_id", "event", "member_id",
jbe@102 102 "informed", "satisfied" )
jbe@102 103 SELECT
jbe@102 104 "issue_id_p" AS "issue_id",
jbe@102 105 "initiative"."id" AS "initiative_id",
jbe@102 106 'periodic' AS "event",
jbe@102 107 "supporter"."member_id" AS "member_id",
jbe@102 108 "supporter"."draft_id" = "current_draft"."id" AS "informed",
jbe@102 109 NOT EXISTS (
jbe@102 110 SELECT NULL FROM "critical_opinion"
jbe@102 111 WHERE "initiative_id" = "initiative"."id"
jbe@102 112 AND "member_id" = "supporter"."member_id"
jbe@102 113 ) AS "satisfied"
jbe@102 114 FROM "initiative"
jbe@102 115 JOIN "supporter"
jbe@102 116 ON "supporter"."initiative_id" = "initiative"."id"
jbe@102 117 JOIN "current_draft"
jbe@102 118 ON "initiative"."id" = "current_draft"."initiative_id"
jbe@102 119 JOIN "direct_interest_snapshot"
jbe@102 120 ON "supporter"."member_id" = "direct_interest_snapshot"."member_id"
jbe@102 121 AND "initiative"."issue_id" = "direct_interest_snapshot"."issue_id"
jbe@102 122 AND "event" = 'periodic'
jbe@102 123 WHERE "initiative"."issue_id" = "issue_id_p";
jbe@102 124 RETURN;
jbe@102 125 END;
jbe@102 126 $$;
jbe@102 127
jbe@103 128 CREATE OR REPLACE FUNCTION "check_everything"()
jbe@103 129 RETURNS VOID
jbe@103 130 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@103 131 DECLARE
jbe@103 132 "issue_id_v" "issue"."id"%TYPE;
jbe@103 133 BEGIN
jbe@103 134 DELETE FROM "expired_session";
jbe@104 135 PERFORM "check_last_login"();
jbe@103 136 PERFORM "calculate_member_counts"();
jbe@103 137 FOR "issue_id_v" IN SELECT "id" FROM "open_issue" LOOP
jbe@103 138 PERFORM "check_issue"("issue_id_v");
jbe@103 139 END LOOP;
jbe@103 140 FOR "issue_id_v" IN SELECT "id" FROM "issue_with_ranks_missing" LOOP
jbe@103 141 PERFORM "calculate_ranks"("issue_id_v");
jbe@103 142 END LOOP;
jbe@103 143 RETURN;
jbe@103 144 END;
jbe@103 145 $$;
jbe@103 146
jbe@103 147 COMMENT ON FUNCTION "check_everything"() IS 'Amongst other regular tasks this function performs "check_issue" for every open issue, and if possible, automatically calculates ranks. Use this function only for development and debugging purposes, as long transactions with exclusive locking may result. In productive environments you should call the lf_update program instead.';
jbe@103 148
jbe@103 149 CREATE OR REPLACE FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE)
jbe@103 150 RETURNS VOID
jbe@103 151 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@103 152 BEGIN
jbe@103 153 UPDATE "member" SET
jbe@103 154 "last_login" = NULL,
jbe@103 155 "last_login_public" = NULL,
jbe@103 156 "login" = NULL,
jbe@103 157 "password" = NULL,
jbe@103 158 "locked" = TRUE,
jbe@103 159 "active" = FALSE,
jbe@103 160 "notify_email" = NULL,
jbe@103 161 "notify_email_unconfirmed" = NULL,
jbe@103 162 "notify_email_secret" = NULL,
jbe@103 163 "notify_email_secret_expiry" = NULL,
jbe@103 164 "notify_email_lock_expiry" = NULL,
jbe@103 165 "password_reset_secret" = NULL,
jbe@103 166 "password_reset_secret_expiry" = NULL,
jbe@103 167 "organizational_unit" = NULL,
jbe@103 168 "internal_posts" = NULL,
jbe@103 169 "realname" = NULL,
jbe@103 170 "birthday" = NULL,
jbe@103 171 "address" = NULL,
jbe@103 172 "email" = NULL,
jbe@103 173 "xmpp_address" = NULL,
jbe@103 174 "website" = NULL,
jbe@103 175 "phone" = NULL,
jbe@103 176 "mobile_phone" = NULL,
jbe@103 177 "profession" = NULL,
jbe@103 178 "external_memberships" = NULL,
jbe@103 179 "external_posts" = NULL,
jbe@103 180 "statement" = NULL
jbe@103 181 WHERE "id" = "member_id_p";
jbe@103 182 -- "text_search_data" is updated by triggers
jbe@103 183 DELETE FROM "setting" WHERE "member_id" = "member_id_p";
jbe@103 184 DELETE FROM "setting_map" WHERE "member_id" = "member_id_p";
jbe@103 185 DELETE FROM "member_relation_setting" WHERE "member_id" = "member_id_p";
jbe@103 186 DELETE FROM "member_image" WHERE "member_id" = "member_id_p";
jbe@103 187 DELETE FROM "contact" WHERE "member_id" = "member_id_p";
jbe@103 188 DELETE FROM "area_setting" WHERE "member_id" = "member_id_p";
jbe@103 189 DELETE FROM "issue_setting" WHERE "member_id" = "member_id_p";
jbe@103 190 DELETE FROM "initiative_setting" WHERE "member_id" = "member_id_p";
jbe@103 191 DELETE FROM "suggestion_setting" WHERE "member_id" = "member_id_p";
jbe@103 192 DELETE FROM "membership" WHERE "member_id" = "member_id_p";
jbe@103 193 DELETE FROM "ignored_issue" WHERE "member_id" = "member_id_p";
jbe@103 194 DELETE FROM "delegation" WHERE "truster_id" = "member_id_p";
jbe@103 195 DELETE FROM "direct_voter" USING "issue"
jbe@103 196 WHERE "direct_voter"."issue_id" = "issue"."id"
jbe@103 197 AND "issue"."closed" ISNULL
jbe@103 198 AND "member_id" = "member_id_p";
jbe@103 199 RETURN;
jbe@103 200 END;
jbe@103 201 $$;
jbe@103 202
jbe@102 203 COMMIT;

Impressum / About Us