1.1 --- a/core.sql	Fri Oct 11 13:42:16 2013 +0200
     1.2 +++ b/core.sql	Fri Oct 11 20:07:41 2013 +0200
     1.3 @@ -539,8 +539,6 @@
     1.4          "population"            INT4,
     1.5          "voter_count"           INT4,
     1.6          "status_quo_schulze_rank" INT4,
     1.7 -        "order_in_admission_state" INT4,
     1.8 -        "order_in_open_states"  INT4,
     1.9          CONSTRAINT "admission_time_not_null_unless_instantly_accepted" CHECK (
    1.10            "admission_time" NOTNULL OR ("accepted" NOTNULL AND "accepted" = "created") ),
    1.11          CONSTRAINT "valid_state" CHECK (
    1.12 @@ -590,24 +588,33 @@
    1.13  
    1.14  COMMENT ON TABLE "issue" IS 'Groups of initiatives';
    1.15  
    1.16 -COMMENT ON COLUMN "issue"."admin_notice"             IS 'Public notice by admin to explain manual interventions, or to announce corrections';
    1.17 -COMMENT ON COLUMN "issue"."phase_finished"           IS 'Set to a value NOTNULL, if the current phase has finished, but calculations are pending; No changes in this issue shall be made by the frontend or API when this value is set';
    1.18 -COMMENT ON COLUMN "issue"."accepted"                 IS 'Point in time, when one initiative of issue reached the "issue_quorum"';
    1.19 -COMMENT ON COLUMN "issue"."half_frozen"              IS 'Point in time, when "discussion_time" has elapsed; Frontends must ensure that for half_frozen issues a) initiatives are not revoked, b) no new drafts are created, c) no initiators are added or removed.';
    1.20 -COMMENT ON COLUMN "issue"."fully_frozen"             IS 'Point in time, when "verification_time" has elapsed and voting has started; Frontends must ensure that for fully_frozen issues additionally to the restrictions for half_frozen issues a) initiatives are not created, b) no interest is created or removed, c) no supporters are added or removed, d) no opinions are created, changed or deleted.';
    1.21 -COMMENT ON COLUMN "issue"."closed"                   IS 'Point in time, when "admission_time" or "voting_time" have elapsed, and issue is no longer active; Frontends must ensure that for closed issues additionally to the restrictions for half_frozen and fully_frozen issues a) no voter is added or removed to/from the direct_voter table, b) no votes are added, modified or removed.';
    1.22 -COMMENT ON COLUMN "issue"."cleaned"                  IS 'Point in time, when discussion data and votes had been deleted';
    1.23 -COMMENT ON COLUMN "issue"."admission_time"           IS 'Copied from "policy" table at creation of issue';
    1.24 -COMMENT ON COLUMN "issue"."discussion_time"          IS 'Copied from "policy" table at creation of issue';
    1.25 -COMMENT ON COLUMN "issue"."verification_time"        IS 'Copied from "policy" table at creation of issue';
    1.26 -COMMENT ON COLUMN "issue"."voting_time"              IS 'Copied from "policy" table at creation of issue';
    1.27 -COMMENT ON COLUMN "issue"."snapshot"                 IS 'Point in time, when snapshot tables have been updated and "population" and *_count values were precalculated';
    1.28 -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';
    1.29 -COMMENT ON COLUMN "issue"."population"               IS 'Sum of "weight" column in table "direct_population_snapshot"';
    1.30 -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';
    1.31 -COMMENT ON COLUMN "issue"."status_quo_schulze_rank"  IS 'Schulze rank of status quo, as calculated by "calculate_ranks" function';
    1.32 -COMMENT ON COLUMN "issue"."order_in_admission_state" IS 'To be used for sorting issues within an area, when showing only issues in admission state; NULL values sort last; updated by "lf_update_issue_order"';
    1.33 -COMMENT ON COLUMN "issue"."order_in_open_states"     IS 'To be used for sorting issues within an area, when showing all open issues; NULL values sort last; updated by "lf_update_issue_order"';
    1.34 +COMMENT ON COLUMN "issue"."admin_notice"            IS 'Public notice by admin to explain manual interventions, or to announce corrections';
    1.35 +COMMENT ON COLUMN "issue"."phase_finished"          IS 'Set to a value NOTNULL, if the current phase has finished, but calculations are pending; No changes in this issue shall be made by the frontend or API when this value is set';
    1.36 +COMMENT ON COLUMN "issue"."accepted"                IS 'Point in time, when one initiative of issue reached the "issue_quorum"';
    1.37 +COMMENT ON COLUMN "issue"."half_frozen"             IS 'Point in time, when "discussion_time" has elapsed; Frontends must ensure that for half_frozen issues a) initiatives are not revoked, b) no new drafts are created, c) no initiators are added or removed.';
    1.38 +COMMENT ON COLUMN "issue"."fully_frozen"            IS 'Point in time, when "verification_time" has elapsed and voting has started; Frontends must ensure that for fully_frozen issues additionally to the restrictions for half_frozen issues a) initiatives are not created, b) no interest is created or removed, c) no supporters are added or removed, d) no opinions are created, changed or deleted.';
    1.39 +COMMENT ON COLUMN "issue"."closed"                  IS 'Point in time, when "admission_time" or "voting_time" have elapsed, and issue is no longer active; Frontends must ensure that for closed issues additionally to the restrictions for half_frozen and fully_frozen issues a) no voter is added or removed to/from the direct_voter table, b) no votes are added, modified or removed.';
    1.40 +COMMENT ON COLUMN "issue"."cleaned"                 IS 'Point in time, when discussion data and votes had been deleted';
    1.41 +COMMENT ON COLUMN "issue"."admission_time"          IS 'Copied from "policy" table at creation of issue';
    1.42 +COMMENT ON COLUMN "issue"."discussion_time"         IS 'Copied from "policy" table at creation of issue';
    1.43 +COMMENT ON COLUMN "issue"."verification_time"       IS 'Copied from "policy" table at creation of issue';
    1.44 +COMMENT ON COLUMN "issue"."voting_time"             IS 'Copied from "policy" table at creation of issue';
    1.45 +COMMENT ON COLUMN "issue"."snapshot"                IS 'Point in time, when snapshot tables have been updated and "population" and *_count values were precalculated';
    1.46 +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';
    1.47 +COMMENT ON COLUMN "issue"."population"              IS 'Sum of "weight" column in table "direct_population_snapshot"';
    1.48 +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';
    1.49 +COMMENT ON COLUMN "issue"."status_quo_schulze_rank" IS 'Schulze rank of status quo, as calculated by "calculate_ranks" function';
    1.50 +
    1.51 +
    1.52 +CREATE TABLE "issue_order" (
    1.53 +        "id"                    INT8            PRIMARY KEY REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
    1.54 +        "order_in_admission_state" INT4,
    1.55 +        "order_in_open_states"  INT4 );
    1.56 +
    1.57 +COMMENT ON TABLE "issue_order" IS 'Ordering information for issues that are not stored in the "issue" table to avoid locking of multiple issues at once';
    1.58 +
    1.59 +COMMENT ON COLUMN "issue_order"."order_in_admission_state" IS 'To be used for sorting issues within an area, when showing only issues in admission state; NULL values sort last; updated by "lf_update_issue_order"';
    1.60 +COMMENT ON COLUMN "issue_order"."order_in_open_states"     IS 'To be used for sorting issues within an area, when showing all open issues; NULL values sort last; updated by "lf_update_issue_order"';
    1.61  
    1.62  
    1.63  CREATE TABLE "issue_setting" (
    1.64 @@ -2087,7 +2094,7 @@
    1.65      "area_id",
    1.66      "id" AS "issue_id",
    1.67      "order_in_admission_state" * 2 - 1 AS "minimum_position"
    1.68 -  FROM "issue"
    1.69 +  FROM "issue" NATURAL LEFT JOIN "issue_order"
    1.70    WHERE "closed" ISNULL
    1.71    ORDER BY
    1.72      coalesce(