# HG changeset patch # User jbe # Date 1381772193 -7200 # Node ID d301dc24b25c32f07c64916807afa8bd9b77498a # Parent 5f24058af0b89a2f2951cca25310905d13413749 Proportional Runoff for issues both based on single areas and single units diff -r 5f24058af0b8 -r d301dc24b25c core.sql --- a/core.sql Mon Oct 14 00:58:38 2013 +0200 +++ b/core.sql Mon Oct 14 19:36:33 2013 +0200 @@ -606,16 +606,16 @@ COMMENT ON COLUMN "issue"."status_quo_schulze_rank" IS 'Schulze rank of status quo, as calculated by "calculate_ranks" function'; -CREATE TABLE "issue_order" ( +CREATE TABLE "issue_order_in_admission_state" ( "id" INT8 PRIMARY KEY, --REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE, - "order_in_admission_state" INT4, - "max_supporter_count" INT4 ); - -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'; - -COMMENT ON COLUMN "issue_order"."id" IS 'References "issue" ("id") but has no referential integrity trigger associated, due to performance/locking issues'; -COMMENT ON COLUMN "issue_order"."order_in_admission_state" IS 'To be used for sorting issues within an area, when showing issues in admission state; NULL values sort last; updated by "lf_update_issue_order"'; -COMMENT ON COLUMN "issue_order"."max_supporter_count" IS 'Secondary sorting key when displaying issues in admission state from different areas'; + "order_in_area" INT4, + "order_in_unit" INT4 ); + +COMMENT ON TABLE "issue_order_in_admission_state" IS 'Ordering information for issues that are not stored in the "issue" table to avoid locking of multiple issues at once; Filled/updated by "lf_update_issue_order"'; + +COMMENT ON COLUMN "issue_order_in_admission_state"."id" IS 'References "issue" ("id") but has no referential integrity trigger associated, due to performance/locking issues'; +COMMENT ON COLUMN "issue_order_in_admission_state"."order_in_area" IS 'Order of issues in admission state within a single area; NULL values sort last'; +COMMENT ON COLUMN "issue_order_in_admission_state"."order_in_unit" IS 'Order of issues in admission state within all areas of a unit; NULL values sort last'; CREATE TABLE "issue_setting" ( @@ -2075,11 +2075,13 @@ CREATE VIEW "issue_supporter_in_admission_state" AS SELECT DISTINCT + "area"."unit_id", "issue"."area_id", "issue"."id" AS "issue_id", "supporter"."member_id", "direct_interest_snapshot"."weight" FROM "issue" + JOIN "area" ON "area"."id" = "issue"."area_id" JOIN "supporter" ON "supporter"."issue_id" = "issue"."id" JOIN "direct_interest_snapshot" ON "direct_interest_snapshot"."issue_id" = "issue"."id" diff -r 5f24058af0b8 -r d301dc24b25c lf_update_issue_order.c --- a/lf_update_issue_order.c Mon Oct 14 00:58:38 2013 +0200 +++ b/lf_update_issue_order.c Mon Oct 14 19:36:33 2013 +0200 @@ -174,16 +174,11 @@ } // write results to database: -static int write_ranks(PGconn *db, char *escaped_area_id) { +static int write_ranks(PGconn *db, char *escaped_area_or_unit_id, char *mode) { PGresult *res; char *cmd; int i; - if (asprintf(&cmd, "BEGIN; DELETE FROM \"issue_order\" USING \"issue\" WHERE \"issue_order\".\"id\" = \"issue\".\"id\" AND \"issue\".\"area_id\" = %s", escaped_area_id) < 0) { - fprintf(stderr, "Could not prepare query string in memory.\n"); - abort(); - } - res = PQexec(db, cmd); - free(cmd); + res = PQexec(db, "BEGIN"); if (!res) { fprintf(stderr, "Error in pqlib while sending SQL command to initiate issue order update.\n"); return 1; @@ -204,7 +199,7 @@ fprintf(stderr, "Could not escape literal in memory.\n"); abort(); } - if (asprintf(&cmd, "INSERT INTO \"issue_order\" (\"id\", \"order_in_admission_state\", \"max_supporter_count\") SELECT %s, %i, max(\"supporter_count\") FROM \"initiative\" WHERE \"issue_id\" = %s", escaped_issue_id, candidates[i].seat, escaped_issue_id) < 0) { + if (asprintf(&cmd, "UPDATE \"issue_order_in_admission_state\" SET \"order_in_%s\" = %i WHERE \"id\" = %s", mode, candidates[i].seat, escaped_issue_id) < 0) { fprintf(stderr, "Could not prepare query string in memory.\n"); abort(); } @@ -212,12 +207,12 @@ res = PQexec(db, cmd); free(cmd); if (!res) { - fprintf(stderr, "Error in pqlib while sending SQL command to insert issue order.\n"); + fprintf(stderr, "Error in pqlib while sending SQL command to update issue order.\n"); } else if ( PQresultStatus(res) != PGRES_COMMAND_OK && PQresultStatus(res) != PGRES_TUPLES_OK ) { - fprintf(stderr, "Error while executing SQL command to insert issue order:\n%s", PQresultErrorMessage(res)); + fprintf(stderr, "Error while executing SQL command to update issue order:\n%s", PQresultErrorMessage(res)); PQclear(res); } else { PQclear(res); @@ -245,7 +240,7 @@ } // calculate ordering of issues in admission state for an area and call write_ranks() to write it to database: -static int process_area(PGconn *db, PGresult *res, char *escaped_area_id) { +static int process_area_or_unit(PGconn *db, PGresult *res, char *escaped_area_or_unit_id, char *mode) { int err; // variable to store an error condition (0 = success) int ballot_count = 1; // number of ballots, must be initiatized to 1, due to loop below struct ballot *ballots; // data structure containing the ballots @@ -265,7 +260,7 @@ if (!tuple_count) { // write results to database: if (logging) printf("No supporters for any issue. Writing ranks to database.\n"); - err = write_ranks(db, escaped_area_id); + err = write_ranks(db, escaped_area_or_unit_id, mode); if (logging) printf("Done.\n"); return 0; } @@ -377,7 +372,7 @@ // write results to database: if (logging) printf("Writing ranks to database.\n"); - err = write_ranks(db, escaped_area_id); + err = write_ranks(db, escaped_area_or_unit_id, mode); if (logging) printf("Done.\n"); // free candidates[] array: @@ -445,6 +440,23 @@ return 1; } + // create missing "issue_order_in_admission_state" entries for issues + res = PQexec(db, "INSERT INTO \"issue_order_in_admission_state\" (\"id\") SELECT \"issue\".\"id\" FROM \"issue\" NATURAL LEFT JOIN \"issue_order_in_admission_state\" WHERE \"issue\".\"state\" = 'admission'::\"issue_state\" AND \"issue_order_in_admission_state\".\"id\" ISNULL"); + if (!res) { + fprintf(stderr, "Error in pqlib while sending SQL command creating new issue order entries.\n"); + err = 1; + } else if ( + PQresultStatus(res) != PGRES_COMMAND_OK && + PQresultStatus(res) != PGRES_TUPLES_OK + ) { + fprintf(stderr, "Error while executing SQL command creating new issue order entries:\n%s", PQresultErrorMessage(res)); + err = 1; + PQclear(res); + } else { + if (logging) printf("Created %s new issue order entries.\n", PQcmdTuples(res)); + PQclear(res); + } + // go through areas: res = PQexec(db, "SELECT \"id\" FROM \"area\""); if (!res) { @@ -490,7 +502,7 @@ err = 1; PQclear(res2); } else { - if (process_area(db, res2, escaped_area_id)) err = 1; + if (process_area_or_unit(db, res2, escaped_area_id, "area")) err = 1; PQclear(res2); } freemem(escaped_area_id); @@ -498,8 +510,61 @@ PQclear(res); } + // go through units: + res = PQexec(db, "SELECT \"id\" FROM \"unit\""); + if (!res) { + fprintf(stderr, "Error in pqlib while sending SQL command selecting units to process.\n"); + err = 1; + } else if (PQresultStatus(res) != PGRES_TUPLES_OK) { + fprintf(stderr, "Error while executing SQL command selecting units to process:\n%s", PQresultErrorMessage(res)); + err = 1; + PQclear(res); + } else if (PQnfields(res) < 1) { + fprintf(stderr, "Too few columns returned by SQL command selecting units to process.\n"); + err = 1; + PQclear(res); + } else { + count = PQntuples(res); + if (logging) printf("Number of units to process: %i\n", count); + for (i=0; i