# HG changeset patch
# User jbe
# Date 1307472170 -7200
# Node ID d8e11e6cfa1d8d6e6e143ee48d7aedad4795e71d
# Parent  b8426100fd2dcfc11bc853cbbac116156681d4c5
Added final "rank" column to table "initiative", which contains a unique order of all admitted initiatives

diff -r b8426100fd2d -r d8e11e6cfa1d core.sql
--- a/core.sql	Tue Jun 07 20:41:14 2011 +0200
+++ b/core.sql	Tue Jun 07 20:42:50 2011 +0200
@@ -571,6 +571,7 @@
         "multistage_majority"   BOOLEAN,
         "eligible"              BOOLEAN,
         "winner"                BOOLEAN,
+        "rank"                  INT4,
         "text_search_data"      TSVECTOR,
         CONSTRAINT "all_or_none_of_revoked_and_revoked_by_member_id_must_be_null"
           CHECK ("revoked" NOTNULL = "revoked_by_member_id" NOTNULL),
@@ -582,11 +583,12 @@
           ( "admitted" NOTNULL AND "admitted" = TRUE ) OR
           ( "positive_votes" ISNULL AND "negative_votes" ISNULL AND
             "direct_majority" ISNULL AND "indirect_majority" ISNULL AND
+            "schulze_rank" ISNULL AND
             "better_than_status_quo" ISNULL AND "worse_than_status_quo" ISNULL AND
-            "schulze_rank" ISNULL AND
             "reverse_beat_path" ISNULL AND "multistage_majority" ISNULL AND
-            "eligible" ISNULL AND "winner" ISNULL ) ),
-        CONSTRAINT "better_excludes_worse" CHECK (NOT ("better_than_status_quo" AND "worse_than_status_quo")) );
+            "eligible" ISNULL AND "winner" ISNULL AND "rank" ISNULL ) ),
+        CONSTRAINT "better_excludes_worse" CHECK (NOT ("better_than_status_quo" AND "worse_than_status_quo")),
+        CONSTRAINT "unique_rank_per_issue" UNIQUE ("issue_id", "rank") );
 CREATE INDEX "initiative_created_idx" ON "initiative" ("created");
 CREATE INDEX "initiative_revoked_idx" ON "initiative" ("revoked");
 CREATE INDEX "initiative_text_search_data_idx" ON "initiative" USING gin ("text_search_data");
@@ -3797,6 +3799,18 @@
           LIMIT 1
         ) AS "subquery"
         WHERE "id" = "subquery"."initiative_id";
+      -- write (final) ranks:
+      "rank_v" := 1;
+      FOR "initiative_id_v" IN
+        SELECT "id"
+        FROM "initiative"
+        WHERE "issue_id" = "issue_id_p" AND "admitted"
+        ORDER BY "winner" DESC, "schulze_rank", "id"
+      LOOP
+        UPDATE "initiative" SET "rank" = "rank_v"
+          WHERE "id" = "initiative_id_v";
+        "rank_v" := "rank_v" + 1;
+      END LOOP;
       -- set schulze rank of status quo and mark issue as finished:
       UPDATE "issue" SET
         "status_quo_schulze_rank" = "rank_ary"["dimension_v"],