liquid_feedback_core

changeset 173:d8e11e6cfa1d

Added final "rank" column to table "initiative", which contains a unique order of all admitted initiatives
author jbe
date Tue Jun 07 20:42:50 2011 +0200 (2011-06-07)
parents b8426100fd2d
children 368fe2299d07
files core.sql
line diff
     1.1 --- a/core.sql	Tue Jun 07 20:41:14 2011 +0200
     1.2 +++ b/core.sql	Tue Jun 07 20:42:50 2011 +0200
     1.3 @@ -571,6 +571,7 @@
     1.4          "multistage_majority"   BOOLEAN,
     1.5          "eligible"              BOOLEAN,
     1.6          "winner"                BOOLEAN,
     1.7 +        "rank"                  INT4,
     1.8          "text_search_data"      TSVECTOR,
     1.9          CONSTRAINT "all_or_none_of_revoked_and_revoked_by_member_id_must_be_null"
    1.10            CHECK ("revoked" NOTNULL = "revoked_by_member_id" NOTNULL),
    1.11 @@ -582,11 +583,12 @@
    1.12            ( "admitted" NOTNULL AND "admitted" = TRUE ) OR
    1.13            ( "positive_votes" ISNULL AND "negative_votes" ISNULL AND
    1.14              "direct_majority" ISNULL AND "indirect_majority" ISNULL AND
    1.15 +            "schulze_rank" ISNULL AND
    1.16              "better_than_status_quo" ISNULL AND "worse_than_status_quo" ISNULL AND
    1.17 -            "schulze_rank" ISNULL AND
    1.18              "reverse_beat_path" ISNULL AND "multistage_majority" ISNULL AND
    1.19 -            "eligible" ISNULL AND "winner" ISNULL ) ),
    1.20 -        CONSTRAINT "better_excludes_worse" CHECK (NOT ("better_than_status_quo" AND "worse_than_status_quo")) );
    1.21 +            "eligible" ISNULL AND "winner" ISNULL AND "rank" ISNULL ) ),
    1.22 +        CONSTRAINT "better_excludes_worse" CHECK (NOT ("better_than_status_quo" AND "worse_than_status_quo")),
    1.23 +        CONSTRAINT "unique_rank_per_issue" UNIQUE ("issue_id", "rank") );
    1.24  CREATE INDEX "initiative_created_idx" ON "initiative" ("created");
    1.25  CREATE INDEX "initiative_revoked_idx" ON "initiative" ("revoked");
    1.26  CREATE INDEX "initiative_text_search_data_idx" ON "initiative" USING gin ("text_search_data");
    1.27 @@ -3797,6 +3799,18 @@
    1.28            LIMIT 1
    1.29          ) AS "subquery"
    1.30          WHERE "id" = "subquery"."initiative_id";
    1.31 +      -- write (final) ranks:
    1.32 +      "rank_v" := 1;
    1.33 +      FOR "initiative_id_v" IN
    1.34 +        SELECT "id"
    1.35 +        FROM "initiative"
    1.36 +        WHERE "issue_id" = "issue_id_p" AND "admitted"
    1.37 +        ORDER BY "winner" DESC, "schulze_rank", "id"
    1.38 +      LOOP
    1.39 +        UPDATE "initiative" SET "rank" = "rank_v"
    1.40 +          WHERE "id" = "initiative_id_v";
    1.41 +        "rank_v" := "rank_v" + 1;
    1.42 +      END LOOP;
    1.43        -- set schulze rank of status quo and mark issue as finished:
    1.44        UPDATE "issue" SET
    1.45          "status_quo_schulze_rank" = "rank_ary"["dimension_v"],

Impressum / About Us