liquid_feedback_core

changeset 8:e6faf5ff83af beta9

Version beta9

Added missing indicies on TSVECTOR fields

New field latest_snapshot_event in table issue specifying the type of the latest snapshot taken

Added weight field also for (intermediate) delegating members in snapshot and voter tables

Possibility for an initiative to specify a URL where an external discussion takes place (discussion_url)

Export concept for creating a database dump, without some non-public information (e.g. private contact data), including:
- Shell script lf_export
- Database function delete_private_data()

Field in member table to be used by a frontend to store information about hints being hidden by the user

Minor cleanup / New comments
author jbe
date Mon Nov 30 12:00:00 2009 +0100 (2009-11-30)
parents 69d84040fb93
children 4af4df1415f9
files README core.sql lf_export
line diff
     1.1 --- a/README	Mon Nov 23 12:00:00 2009 +0100
     1.2 +++ b/README	Mon Nov 30 12:00:00 2009 +0100
     1.3 @@ -18,6 +18,10 @@
     1.4        within the same transaction, e.g. issues can't exist without
     1.5        an initiative and vice versa.
     1.6  
     1.7 +To create an export file, which is containing all but private data,
     1.8 +you may use the lf_export shell-script:
     1.9 +$ lf_export liquid_feedback export.sql.gz
    1.10 +
    1.11  To uninstall the software, delete the lf_update binary
    1.12  and drop the database by entering the following command:
    1.13  $ dropdb liquid_feedback
     2.1 --- a/core.sql	Mon Nov 23 12:00:00 2009 +0100
     2.2 +++ b/core.sql	Mon Nov 30 12:00:00 2009 +0100
     2.3 @@ -6,7 +6,7 @@
     2.4  BEGIN;
     2.5  
     2.6  CREATE VIEW "liquid_feedback_version" AS
     2.7 -  SELECT * FROM (VALUES ('beta8', NULL, NULL, NULL))
     2.8 +  SELECT * FROM (VALUES ('beta9', NULL, NULL, NULL))
     2.9    AS "subquery"("string", "major", "minor", "revision");
    2.10  
    2.11  
    2.12 @@ -35,7 +35,7 @@
    2.13      BEGIN
    2.14        RETURN ts_headline(
    2.15          'pg_catalog.simple',
    2.16 -        replace(replace("body_p", '\\', '\\\\'), '*', '\\*'),
    2.17 +        replace(replace("body_p", e'\\', e'\\\\'), '*', e'\\*'),
    2.18          "text_search_query"("query_text_p"),
    2.19          'StartSel=* StopSel=* HighlightAll=TRUE' );
    2.20      END;
    2.21 @@ -52,12 +52,14 @@
    2.22  -- Tables and indicies --
    2.23  -------------------------
    2.24  
    2.25 +
    2.26  CREATE TABLE "member" (
    2.27          "id"                    SERIAL4         PRIMARY KEY,
    2.28          "login"                 TEXT            NOT NULL UNIQUE,
    2.29          "password"              TEXT,
    2.30          "active"                BOOLEAN         NOT NULL DEFAULT TRUE,
    2.31          "admin"                 BOOLEAN         NOT NULL DEFAULT FALSE,
    2.32 +        "hidden_hints"          TEXT[],
    2.33          "notify_email"          TEXT,
    2.34          "notify_email_confirmed" BOOLEAN,
    2.35          "name"                  TEXT            NOT NULL UNIQUE,
    2.36 @@ -80,6 +82,7 @@
    2.37          CONSTRAINT "notify_email_null_check"
    2.38            CHECK ("notify_email" NOTNULL = "notify_email_confirmed" NOTNULL) );
    2.39  CREATE INDEX "member_active_idx" ON "member" ("active");
    2.40 +CREATE INDEX "member_text_search_data_idx" ON "member" USING gin ("text_search_data");
    2.41  CREATE TRIGGER "update_text_search_data"
    2.42    BEFORE INSERT OR UPDATE ON "member"
    2.43    FOR EACH ROW EXECUTE PROCEDURE
    2.44 @@ -93,6 +96,7 @@
    2.45  COMMENT ON COLUMN "member"."password"               IS 'Password (preferably as crypto-hash, depending on the frontend or access layer)';
    2.46  COMMENT ON COLUMN "member"."active"                 IS 'Inactive members can not login and their supports/votes are not counted by the system.';
    2.47  COMMENT ON COLUMN "member"."admin"                  IS 'TRUE for admins, which can administrate other users and setup policies and areas';
    2.48 +COMMENT ON COLUMN "member"."hidden_hints"           IS 'This field may be used by a frontend to store identification strings for introductory hints, which the user wants to hide.';
    2.49  COMMENT ON COLUMN "member"."notify_email"           IS 'Email address where notifications of the system are sent to';
    2.50  COMMENT ON COLUMN "member"."notify_email_confirmed" IS 'TRUE, if "notify_email" has been confirmed';
    2.51  COMMENT ON COLUMN "member"."name"                   IS 'Distinct name of the member';
    2.52 @@ -202,6 +206,7 @@
    2.53          "autoreject_weight"     INT4,
    2.54          "text_search_data"      TSVECTOR );
    2.55  CREATE INDEX "area_active_idx" ON "area" ("active");
    2.56 +CREATE INDEX "area_text_search_data_idx" ON "area" USING gin ("text_search_data");
    2.57  CREATE TRIGGER "update_text_search_data"
    2.58    BEFORE INSERT OR UPDATE ON "area"
    2.59    FOR EACH ROW EXECUTE PROCEDURE
    2.60 @@ -216,6 +221,11 @@
    2.61  COMMENT ON COLUMN "area"."autoreject_weight"   IS 'Sum of weight of members using the autoreject feature';
    2.62  
    2.63  
    2.64 +CREATE TYPE "snapshot_event" AS ENUM ('periodic', 'end_of_admission', 'start_of_voting');
    2.65 +
    2.66 +COMMENT ON TYPE "snapshot_event" IS 'Reason for snapshots: ''periodic'' = due to periodic recalculation, ''end_of_admission'' = saved state at end of admission period, ''start_of_voting'' = saved state at end of verification period';
    2.67 +
    2.68 +
    2.69  CREATE TABLE "issue" (
    2.70          "id"                    SERIAL4         PRIMARY KEY,
    2.71          "area_id"               INT4            NOT NULL REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
    2.72 @@ -227,6 +237,7 @@
    2.73          "closed"                TIMESTAMPTZ,
    2.74          "ranks_available"       BOOLEAN         NOT NULL DEFAULT FALSE,
    2.75          "snapshot"              TIMESTAMPTZ,
    2.76 +        "latest_snapshot_event" "snapshot_event",
    2.77          "population"            INT4,
    2.78          "vote_now"              INT4,
    2.79          "vote_later"            INT4,
    2.80 @@ -245,23 +256,25 @@
    2.81            "half_frozen" <= "fully_frozen" AND
    2.82            "fully_frozen" <= "closed" ),
    2.83          CONSTRAINT "last_snapshot_on_full_freeze" CHECK ("snapshot" = "fully_frozen"),  -- NOTE: snapshot can be set, while frozen is NULL yet
    2.84 -        CONSTRAINT "freeze_requires_snapshot" CHECK ("fully_frozen" ISNULL OR "snapshot" NOTNULL) );
    2.85 +        CONSTRAINT "freeze_requires_snapshot" CHECK ("fully_frozen" ISNULL OR "snapshot" NOTNULL),
    2.86 +        CONSTRAINT "set_both_or_none_of_snapshot_and_latest_snapshot_event" CHECK ("snapshot" NOTNULL = "latest_snapshot_event" NOTNULL) );
    2.87  CREATE INDEX "issue_area_id_idx" ON "issue" ("area_id");
    2.88  CREATE INDEX "issue_policy_id_idx" ON "issue" ("policy_id");
    2.89  CREATE INDEX "issue_created_idx_open" ON "issue" ("created") WHERE "closed" ISNULL;
    2.90  
    2.91  COMMENT ON TABLE "issue" IS 'Groups of initiatives';
    2.92  
    2.93 -COMMENT ON COLUMN "issue"."accepted"        IS 'Point in time, when one initiative of issue reached the "issue_quorum"';
    2.94 -COMMENT ON COLUMN "issue"."half_frozen"     IS 'Point in time, when "discussion_time" has elapsed, or members voted for voting';
    2.95 -COMMENT ON COLUMN "issue"."fully_frozen"    IS 'Point in time, when "verification_time" has elapsed';
    2.96 -COMMENT ON COLUMN "issue"."closed"          IS 'Point in time, when "admission_time" or "voting_time" have elapsed, and issue is no longer active';
    2.97 -COMMENT ON COLUMN "issue"."ranks_available" IS 'TRUE = ranks have been calculated';
    2.98 -COMMENT ON COLUMN "issue"."snapshot"        IS 'Point in time, when snapshot tables have been updated and "population", "vote_now", "vote_later" and *_count values were precalculated';
    2.99 -COMMENT ON COLUMN "issue"."population"      IS 'Sum of "weight" column in table "direct_population_snapshot"';
   2.100 -COMMENT ON COLUMN "issue"."vote_now"        IS 'Number of votes in favor of voting now, as calculated from table "direct_interest_snapshot"';
   2.101 -COMMENT ON COLUMN "issue"."vote_later"      IS 'Number of votes against voting now, as calculated from table "direct_interest_snapshot"';
   2.102 -COMMENT ON COLUMN "issue"."voter_count"     IS 'Total number of direct and delegating voters; This value is related to the final voting, while "population" is related to snapshots before the final voting';
   2.103 +COMMENT ON COLUMN "issue"."accepted"              IS 'Point in time, when one initiative of issue reached the "issue_quorum"';
   2.104 +COMMENT ON COLUMN "issue"."half_frozen"           IS 'Point in time, when "discussion_time" has elapsed, or members voted for voting';
   2.105 +COMMENT ON COLUMN "issue"."fully_frozen"          IS 'Point in time, when "verification_time" has elapsed';
   2.106 +COMMENT ON COLUMN "issue"."closed"                IS 'Point in time, when "admission_time" or "voting_time" have elapsed, and issue is no longer active';
   2.107 +COMMENT ON COLUMN "issue"."ranks_available"       IS 'TRUE = ranks have been calculated';
   2.108 +COMMENT ON COLUMN "issue"."snapshot"              IS 'Point in time, when snapshot tables have been updated and "population", "vote_now", "vote_later" and *_count values were precalculated';
   2.109 +COMMENT ON COLUMN "issue"."latest_snapshot_event" IS 'Event type of latest snapshot for issue; Can be used to select the latest snapshot data in the snapshot tables';
   2.110 +COMMENT ON COLUMN "issue"."population"            IS 'Sum of "weight" column in table "direct_population_snapshot"';
   2.111 +COMMENT ON COLUMN "issue"."vote_now"              IS 'Number of votes in favor of voting now, as calculated from table "direct_interest_snapshot"';
   2.112 +COMMENT ON COLUMN "issue"."vote_later"            IS 'Number of votes against voting now, as calculated from table "direct_interest_snapshot"';
   2.113 +COMMENT ON COLUMN "issue"."voter_count"           IS 'Total number of direct and delegating voters; This value is related to the final voting, while "population" is related to snapshots before the final voting';
   2.114  
   2.115  
   2.116  CREATE TABLE "initiative" (
   2.117 @@ -269,6 +282,7 @@
   2.118          "issue_id"              INT4            NOT NULL REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
   2.119          "id"                    SERIAL4         PRIMARY KEY,
   2.120          "name"                  TEXT            NOT NULL,
   2.121 +        "discussion_url"        TEXT,
   2.122          "created"               TIMESTAMPTZ     NOT NULL DEFAULT now(),
   2.123          "revoked"               TIMESTAMPTZ,
   2.124          "admitted"              BOOLEAN,
   2.125 @@ -284,13 +298,16 @@
   2.126            CHECK ("revoked" ISNULL OR "admitted" ISNULL),
   2.127          CONSTRAINT "non_admitted_initiatives_cant_contain_voting_results"
   2.128            CHECK ("admitted" = TRUE OR ("positive_votes" ISNULL AND "negative_votes" ISNULL AND "rank" ISNULL)) );
   2.129 +CREATE INDEX "initiative_text_search_data_idx" ON "initiative" USING gin ("text_search_data");
   2.130  CREATE TRIGGER "update_text_search_data"
   2.131    BEFORE INSERT OR UPDATE ON "initiative"
   2.132    FOR EACH ROW EXECUTE PROCEDURE
   2.133 -  tsvector_update_trigger('text_search_data', 'pg_catalog.simple', "name");
   2.134 +  tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
   2.135 +    "name", "discussion_url");
   2.136  
   2.137  COMMENT ON TABLE "initiative" IS 'Group of members publishing drafts for resolutions to be passed';
   2.138  
   2.139 +COMMENT ON COLUMN "initiative"."discussion_url" IS 'URL pointing to a discussion platform for this initiative';
   2.140  COMMENT ON COLUMN "initiative"."revoked"        IS 'Point in time, when one initiator decided to revoke the initiative';
   2.141  COMMENT ON COLUMN "initiative"."admitted"       IS 'True, if initiative reaches the "initiative_quorum" when freezing the issue';
   2.142  COMMENT ON COLUMN "initiative"."supporter_count"                    IS 'Calculated from table "direct_supporter_snapshot"';
   2.143 @@ -310,6 +327,7 @@
   2.144          "author_id"             INT4            NOT NULL REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
   2.145          "content"               TEXT            NOT NULL,
   2.146          "text_search_data"      TSVECTOR );
   2.147 +CREATE INDEX "draft_text_search_data_idx" ON "draft" USING gin ("text_search_data");
   2.148  CREATE TRIGGER "update_text_search_data"
   2.149    BEFORE INSERT OR UPDATE ON "draft"
   2.150    FOR EACH ROW EXECUTE PROCEDURE
   2.151 @@ -335,6 +353,7 @@
   2.152          "plus1_fulfilled_count"    INT4,
   2.153          "plus2_unfulfilled_count"  INT4,
   2.154          "plus2_fulfilled_count"    INT4 );
   2.155 +CREATE INDEX "suggestion_text_search_data_idx" ON "suggestion" USING gin ("text_search_data");
   2.156  CREATE TRIGGER "update_text_search_data"
   2.157    BEFORE INSERT OR UPDATE ON "suggestion"
   2.158    FOR EACH ROW EXECUTE PROCEDURE
   2.159 @@ -444,11 +463,6 @@
   2.160  COMMENT ON COLUMN "delegation"."issue_id" IS 'Reference to issue, if delegation is issue-wide, otherwise NULL';
   2.161  
   2.162  
   2.163 -CREATE TYPE "snapshot_event" AS ENUM ('periodic', 'end_of_admission', 'start_of_voting');
   2.164 -
   2.165 -COMMENT ON TYPE "snapshot_event" IS 'Reason for snapshots: ''periodic'' = due to periodic recalculation, ''end_of_admission'' = saved state at end of admission period, ''start_of_voting'' = saved state at end of verification period';
   2.166 -
   2.167 -
   2.168  CREATE TABLE "direct_population_snapshot" (
   2.169          PRIMARY KEY ("issue_id", "event", "member_id"),
   2.170          "issue_id"              INT4            REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
   2.171 @@ -470,6 +484,7 @@
   2.172          "issue_id"              INT4            REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
   2.173          "event"                "snapshot_event",
   2.174          "member_id"             INT4            REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
   2.175 +        "weight"                INT4,
   2.176          "delegate_member_ids"   INT4[]          NOT NULL );
   2.177  CREATE INDEX "delegating_population_snapshot_member_id_idx" ON "delegating_population_snapshot" ("member_id");
   2.178  
   2.179 @@ -477,6 +492,7 @@
   2.180  
   2.181  COMMENT ON COLUMN "delegating_population_snapshot"."event"               IS 'Reason for snapshot, see "snapshot_event" type for details';
   2.182  COMMENT ON COLUMN "delegating_population_snapshot"."member_id"           IS 'Delegating member';
   2.183 +COMMENT ON COLUMN "delegating_population_snapshot"."weight"              IS 'Intermediate weight';
   2.184  COMMENT ON COLUMN "delegating_population_snapshot"."delegate_member_ids" IS 'Chain of members who act as delegates; last entry referes to "member_id" column of table "direct_population_snapshot"';
   2.185  
   2.186  
   2.187 @@ -501,6 +517,7 @@
   2.188          "issue_id"         INT4                 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
   2.189          "event"                "snapshot_event",
   2.190          "member_id"             INT4            REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
   2.191 +        "weight"                INT4,
   2.192          "delegate_member_ids"   INT4[]          NOT NULL );
   2.193  CREATE INDEX "delegating_interest_snapshot_member_id_idx" ON "delegating_interest_snapshot" ("member_id");
   2.194  
   2.195 @@ -508,6 +525,7 @@
   2.196  
   2.197  COMMENT ON COLUMN "delegating_interest_snapshot"."event"               IS 'Reason for snapshot, see "snapshot_event" type for details';
   2.198  COMMENT ON COLUMN "delegating_interest_snapshot"."member_id"           IS 'Delegating member';
   2.199 +COMMENT ON COLUMN "delegating_interest_snapshot"."weight"              IS 'Intermediate weight';
   2.200  COMMENT ON COLUMN "delegating_interest_snapshot"."delegate_member_ids" IS 'Chain of members who act as delegates; last entry referes to "member_id" column of table "direct_interest_snapshot"';
   2.201  
   2.202  
   2.203 @@ -523,7 +541,7 @@
   2.204          FOREIGN KEY ("issue_id", "event", "member_id") REFERENCES "direct_interest_snapshot" ("issue_id", "event", "member_id") ON DELETE CASCADE ON UPDATE CASCADE );
   2.205  CREATE INDEX "direct_supporter_snapshot_member_id_idx" ON "direct_supporter_snapshot" ("member_id");
   2.206  
   2.207 -COMMENT ON TABLE "direct_supporter_snapshot" IS 'Snapshot of supporters of initiatives (weight is stored in "direct_interest_snapshot)';
   2.208 +COMMENT ON TABLE "direct_supporter_snapshot" IS 'Snapshot of supporters of initiatives (weight is stored in "direct_interest_snapshot")';
   2.209  
   2.210  COMMENT ON COLUMN "direct_supporter_snapshot"."event"     IS 'Reason for snapshot, see "snapshot_event" type for details';
   2.211  COMMENT ON COLUMN "direct_supporter_snapshot"."informed"  IS 'Supporter has seen the latest draft of the initiative';
   2.212 @@ -548,12 +566,14 @@
   2.213          PRIMARY KEY ("issue_id", "member_id"),
   2.214          "issue_id"              INT4            REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
   2.215          "member_id"             INT4            REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
   2.216 +        "weight"                INT4,
   2.217          "delegate_member_ids"   INT4[]          NOT NULL );
   2.218  CREATE INDEX "delegating_voter_member_id_idx" ON "direct_voter" ("member_id");
   2.219  
   2.220  COMMENT ON TABLE "delegating_voter" IS 'Delegations increasing the weight of entries in the "direct_voter" table';
   2.221  
   2.222  COMMENT ON COLUMN "delegating_voter"."member_id"           IS 'Delegating member';
   2.223 +COMMENT ON COLUMN "delegating_voter"."weight"              IS 'Intermediate weight';
   2.224  COMMENT ON COLUMN "delegating_voter"."delegate_member_ids" IS 'Chain of members who act as delegates; last entry referes to "member_id" column of table "direct_voter"';
   2.225  
   2.226  
   2.227 @@ -1021,6 +1041,13 @@
   2.228      END;
   2.229    $$;
   2.230  
   2.231 +COMMENT ON FUNCTION "membership_weight_with_skipping"
   2.232 +  ( "area"."id"%TYPE,
   2.233 +    "member"."id"%TYPE,
   2.234 +    INT4[] )
   2.235 +  IS 'Helper function for "membership_weight" function';
   2.236 +
   2.237 +
   2.238  CREATE FUNCTION "membership_weight"
   2.239    ( "area_id_p"         "area"."id"%TYPE,
   2.240      "member_id_p"       "member"."id"%TYPE )  -- "member"."id"%TYPE[]
   2.241 @@ -1035,6 +1062,11 @@
   2.242      END;
   2.243    $$;
   2.244  
   2.245 +COMMENT ON FUNCTION "membership_weight"
   2.246 +  ( "area"."id"%TYPE,
   2.247 +    "member"."id"%TYPE )
   2.248 +  IS 'Calculates the potential voting weight of a member in a given area';
   2.249 +
   2.250  
   2.251  CREATE VIEW "member_count_view" AS
   2.252    SELECT count(1) AS "total_count" FROM "member" WHERE "active";
   2.253 @@ -1067,7 +1099,6 @@
   2.254    ON "membership"."member_id" = "member"."id"
   2.255    AND "member"."active"
   2.256    GROUP BY "area"."id";
   2.257 --- TODO: count delegations
   2.258  
   2.259  COMMENT ON VIEW "area_member_count" IS 'View used to update "member_count" column of table "area"';
   2.260  
   2.261 @@ -1481,6 +1512,7 @@
   2.262        "issue_delegation_row"  "issue_delegation"%ROWTYPE;
   2.263        "delegate_member_ids_v" "delegating_population_snapshot"."delegate_member_ids"%TYPE;
   2.264        "weight_v"              INT4;
   2.265 +      "sub_weight_v"          INT4;
   2.266      BEGIN
   2.267        "weight_v" := 0;
   2.268        FOR "issue_delegation_row" IN
   2.269 @@ -1509,12 +1541,18 @@
   2.270                "issue_delegation_row"."truster_id",
   2.271                "delegate_member_ids_v"
   2.272              );
   2.273 -          "weight_v" := "weight_v" + 1 +
   2.274 +          "sub_weight_v" := 1 +
   2.275              "weight_of_added_delegations_for_population_snapshot"(
   2.276                "issue_id_p",
   2.277                "issue_delegation_row"."truster_id",
   2.278                "delegate_member_ids_v"
   2.279              );
   2.280 +          UPDATE "delegating_population_snapshot"
   2.281 +            SET "weight" = "sub_weight_v"
   2.282 +            WHERE "issue_id" = "issue_id_p"
   2.283 +            AND "event" = 'periodic'
   2.284 +            AND "member_id" = "issue_delegation_row"."truster_id";
   2.285 +          "weight_v" := "weight_v" + "sub_weight_v";
   2.286          END IF;
   2.287        END LOOP;
   2.288        RETURN "weight_v";
   2.289 @@ -1606,6 +1644,7 @@
   2.290        "issue_delegation_row"  "issue_delegation"%ROWTYPE;
   2.291        "delegate_member_ids_v" "delegating_interest_snapshot"."delegate_member_ids"%TYPE;
   2.292        "weight_v"              INT4;
   2.293 +      "sub_weight_v"          INT4;
   2.294      BEGIN
   2.295        "weight_v" := 0;
   2.296        FOR "issue_delegation_row" IN
   2.297 @@ -1634,12 +1673,18 @@
   2.298                "issue_delegation_row"."truster_id",
   2.299                "delegate_member_ids_v"
   2.300              );
   2.301 -          "weight_v" := "weight_v" + 1 +
   2.302 +          "sub_weight_v" := 1 +
   2.303              "weight_of_added_delegations_for_interest_snapshot"(
   2.304                "issue_id_p",
   2.305                "issue_delegation_row"."truster_id",
   2.306                "delegate_member_ids_v"
   2.307              );
   2.308 +          UPDATE "delegating_interest_snapshot"
   2.309 +            SET "weight" = "sub_weight_v"
   2.310 +            WHERE "issue_id" = "issue_id_p"
   2.311 +            AND "event" = 'periodic'
   2.312 +            AND "member_id" = "issue_delegation_row"."truster_id";
   2.313 +          "weight_v" := "weight_v" + "sub_weight_v";
   2.314          END IF;
   2.315        END LOOP;
   2.316        RETURN "weight_v";
   2.317 @@ -1744,14 +1789,15 @@
   2.318        PERFORM "create_population_snapshot"("issue_id_p");
   2.319        PERFORM "create_interest_snapshot"("issue_id_p");
   2.320        UPDATE "issue" SET
   2.321 -        "snapshot"   = now(),
   2.322 +        "snapshot" = now(),
   2.323 +        "latest_snapshot_event" = 'periodic',
   2.324          "population" = (
   2.325            SELECT coalesce(sum("weight"), 0)
   2.326            FROM "direct_population_snapshot"
   2.327            WHERE "issue_id" = "issue_id_p"
   2.328            AND "event" = 'periodic'
   2.329          ),
   2.330 -        "vote_now"   = (
   2.331 +        "vote_now" = (
   2.332            SELECT coalesce(sum("weight"), 0)
   2.333            FROM "direct_interest_snapshot"
   2.334            WHERE "issue_id" = "issue_id_p"
   2.335 @@ -1910,6 +1956,8 @@
   2.336    RETURNS VOID
   2.337    LANGUAGE 'plpgsql' VOLATILE AS $$
   2.338      BEGIN
   2.339 +      UPDATE "issue" SET "latest_snapshot_event" = "event_p"
   2.340 +        WHERE "id" = "issue_id_p";
   2.341        UPDATE "direct_population_snapshot" SET "event" = "event_p"
   2.342          WHERE "issue_id" = "issue_id_p" AND "event" = 'periodic';
   2.343        UPDATE "delegating_population_snapshot" SET "event" = "event_p"
   2.344 @@ -2011,6 +2059,7 @@
   2.345        "issue_delegation_row"  "issue_delegation"%ROWTYPE;
   2.346        "delegate_member_ids_v" "delegating_voter"."delegate_member_ids"%TYPE;
   2.347        "weight_v"              INT4;
   2.348 +      "sub_weight_v"          INT4;
   2.349      BEGIN
   2.350        "weight_v" := 0;
   2.351        FOR "issue_delegation_row" IN
   2.352 @@ -2030,18 +2079,23 @@
   2.353            "delegate_member_ids_v" :=
   2.354              "member_id_p" || "delegate_member_ids_p";
   2.355            INSERT INTO "delegating_voter"
   2.356 -            ("member_id", "issue_id", "delegate_member_ids")
   2.357 +            ("issue_id", "member_id", "delegate_member_ids")
   2.358              VALUES (
   2.359 -              "issue_delegation_row"."truster_id",
   2.360 -              "issue_id_p",
   2.361 -              "delegate_member_ids_v"
   2.362 -            );
   2.363 -          "weight_v" := "weight_v" +
   2.364 -            1 + "weight_of_added_vote_delegations"(
   2.365                "issue_id_p",
   2.366                "issue_delegation_row"."truster_id",
   2.367                "delegate_member_ids_v"
   2.368              );
   2.369 +          "sub_weight_v" := 1 +
   2.370 +            "weight_of_added_vote_delegations"(
   2.371 +              "issue_id_p",
   2.372 +              "issue_delegation_row"."truster_id",
   2.373 +              "delegate_member_ids_v"
   2.374 +            );
   2.375 +          UPDATE "delegating_voter"
   2.376 +            SET "weight" = "sub_weight_v"
   2.377 +            WHERE "issue_id" = "issue_id_p"
   2.378 +            AND "member_id" = "issue_delegation_row"."truster_id";
   2.379 +          "weight_v" := "weight_v" + "sub_weight_v";
   2.380          END IF;
   2.381        END LOOP;
   2.382        RETURN "weight_v";
   2.383 @@ -2549,4 +2603,28 @@
   2.384  
   2.385  
   2.386  
   2.387 +------------------------------
   2.388 +-- Deletion of private data --
   2.389 +------------------------------
   2.390 +
   2.391 +
   2.392 +CREATE FUNCTION "delete_private_data"()
   2.393 +  RETURNS VOID
   2.394 +  LANGUAGE 'plpgsql' VOLATILE AS $$
   2.395 +    DECLARE
   2.396 +      "issue_id_v" "issue"."id"%TYPE;
   2.397 +    BEGIN
   2.398 +      DELETE FROM "session";
   2.399 +      DELETE FROM "contact" WHERE NOT "public";
   2.400 +      DELETE FROM "direct_voter" USING "issue"
   2.401 +        WHERE "direct_voter"."issue_id" = "issue"."id"
   2.402 +        AND "issue"."closed" ISNULL;
   2.403 +      RETURN;
   2.404 +    END;
   2.405 +  $$;
   2.406 +
   2.407 +COMMENT ON FUNCTION "delete_private_data"() IS 'DO NOT USE on productive database, but only on a copy! This function deletes all data which should not be publicly available, and can be used to create a database dump for publication.';
   2.408 +
   2.409 +
   2.410 +
   2.411  COMMIT;
     3.1 --- /dev/null	Thu Jan 01 00:00:00 1970 +0000
     3.2 +++ b/lf_export	Mon Nov 30 12:00:00 2009 +0100
     3.3 @@ -0,0 +1,34 @@
     3.4 +#!/bin/sh
     3.5 +
     3.6 +if [ -z "$1" -o -z "$2" ]; then
     3.7 +  echo "Usage: $0 <dbname> <filename>.sql.gz"
     3.8 +  exit 1
     3.9 +fi
    3.10 +
    3.11 +EXPORT_DBNAME=liquid_feedback_autoexport
    3.12 +retval=0
    3.13 +
    3.14 +echo "Dropping database \"$EXPORT_DBNAME\" if existent..."
    3.15 +dropdb "$EXPORT_DBNAME" 2> /dev/null
    3.16 +echo "Copying database \"$1\" to new database \"$EXPORT_DBNAME\"..."
    3.17 +if createdb -T "$1" "$EXPORT_DBNAME"
    3.18 +then
    3.19 +  echo "Deleting private data in copied database..."
    3.20 +  if psql liquid_feedback -c 'SELECT delete_private_data()' > /dev/null
    3.21 +  then
    3.22 +    echo "Dumping and compressing copied database to \"$2\"..."
    3.23 +    if pg_dump --no-owner --no-privileges "$EXPORT_DBNAME" | gzip -9 > "$2"
    3.24 +    then
    3.25 +    else
    3.26 +      retval=4
    3.27 +    fi
    3.28 +  else
    3.29 +    retval=3
    3.30 +  fi
    3.31 +else
    3.32 +  retval=2
    3.33 +fi
    3.34 +echo "Dropping database \"$EXPORT_DBNAME\"..."
    3.35 +dropdb "$EXPORT_DBNAME"
    3.36 +echo "DONE."
    3.37 +exit $retval

Impressum / About Us