liquid_feedback_core
changeset 410:d301dc24b25c
Proportional Runoff for issues both based on single areas and single units
author | jbe |
---|---|
date | Mon Oct 14 19:36:33 2013 +0200 (2013-10-14) |
parents | 5f24058af0b8 |
children | 44a07d8f1bb4 |
files | core.sql lf_update_issue_order.c update/core-update.v2.2.5-v2.2.6.sql |
line diff
1.1 --- a/core.sql Mon Oct 14 00:58:38 2013 +0200 1.2 +++ b/core.sql Mon Oct 14 19:36:33 2013 +0200 1.3 @@ -606,16 +606,16 @@ 1.4 COMMENT ON COLUMN "issue"."status_quo_schulze_rank" IS 'Schulze rank of status quo, as calculated by "calculate_ranks" function'; 1.5 1.6 1.7 -CREATE TABLE "issue_order" ( 1.8 +CREATE TABLE "issue_order_in_admission_state" ( 1.9 "id" INT8 PRIMARY KEY, --REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 1.10 - "order_in_admission_state" INT4, 1.11 - "max_supporter_count" INT4 ); 1.12 - 1.13 -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.14 - 1.15 -COMMENT ON COLUMN "issue_order"."id" IS 'References "issue" ("id") but has no referential integrity trigger associated, due to performance/locking issues'; 1.16 -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"'; 1.17 -COMMENT ON COLUMN "issue_order"."max_supporter_count" IS 'Secondary sorting key when displaying issues in admission state from different areas'; 1.18 + "order_in_area" INT4, 1.19 + "order_in_unit" INT4 ); 1.20 + 1.21 +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"'; 1.22 + 1.23 +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'; 1.24 +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'; 1.25 +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'; 1.26 1.27 1.28 CREATE TABLE "issue_setting" ( 1.29 @@ -2075,11 +2075,13 @@ 1.30 1.31 CREATE VIEW "issue_supporter_in_admission_state" AS 1.32 SELECT DISTINCT 1.33 + "area"."unit_id", 1.34 "issue"."area_id", 1.35 "issue"."id" AS "issue_id", 1.36 "supporter"."member_id", 1.37 "direct_interest_snapshot"."weight" 1.38 FROM "issue" 1.39 + JOIN "area" ON "area"."id" = "issue"."area_id" 1.40 JOIN "supporter" ON "supporter"."issue_id" = "issue"."id" 1.41 JOIN "direct_interest_snapshot" 1.42 ON "direct_interest_snapshot"."issue_id" = "issue"."id"
2.1 --- a/lf_update_issue_order.c Mon Oct 14 00:58:38 2013 +0200 2.2 +++ b/lf_update_issue_order.c Mon Oct 14 19:36:33 2013 +0200 2.3 @@ -174,16 +174,11 @@ 2.4 } 2.5 2.6 // write results to database: 2.7 -static int write_ranks(PGconn *db, char *escaped_area_id) { 2.8 +static int write_ranks(PGconn *db, char *escaped_area_or_unit_id, char *mode) { 2.9 PGresult *res; 2.10 char *cmd; 2.11 int i; 2.12 - 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) { 2.13 - fprintf(stderr, "Could not prepare query string in memory.\n"); 2.14 - abort(); 2.15 - } 2.16 - res = PQexec(db, cmd); 2.17 - free(cmd); 2.18 + res = PQexec(db, "BEGIN"); 2.19 if (!res) { 2.20 fprintf(stderr, "Error in pqlib while sending SQL command to initiate issue order update.\n"); 2.21 return 1; 2.22 @@ -204,7 +199,7 @@ 2.23 fprintf(stderr, "Could not escape literal in memory.\n"); 2.24 abort(); 2.25 } 2.26 - 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) { 2.27 + 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) { 2.28 fprintf(stderr, "Could not prepare query string in memory.\n"); 2.29 abort(); 2.30 } 2.31 @@ -212,12 +207,12 @@ 2.32 res = PQexec(db, cmd); 2.33 free(cmd); 2.34 if (!res) { 2.35 - fprintf(stderr, "Error in pqlib while sending SQL command to insert issue order.\n"); 2.36 + fprintf(stderr, "Error in pqlib while sending SQL command to update issue order.\n"); 2.37 } else if ( 2.38 PQresultStatus(res) != PGRES_COMMAND_OK && 2.39 PQresultStatus(res) != PGRES_TUPLES_OK 2.40 ) { 2.41 - fprintf(stderr, "Error while executing SQL command to insert issue order:\n%s", PQresultErrorMessage(res)); 2.42 + fprintf(stderr, "Error while executing SQL command to update issue order:\n%s", PQresultErrorMessage(res)); 2.43 PQclear(res); 2.44 } else { 2.45 PQclear(res); 2.46 @@ -245,7 +240,7 @@ 2.47 } 2.48 2.49 // calculate ordering of issues in admission state for an area and call write_ranks() to write it to database: 2.50 -static int process_area(PGconn *db, PGresult *res, char *escaped_area_id) { 2.51 +static int process_area_or_unit(PGconn *db, PGresult *res, char *escaped_area_or_unit_id, char *mode) { 2.52 int err; // variable to store an error condition (0 = success) 2.53 int ballot_count = 1; // number of ballots, must be initiatized to 1, due to loop below 2.54 struct ballot *ballots; // data structure containing the ballots 2.55 @@ -265,7 +260,7 @@ 2.56 if (!tuple_count) { 2.57 // write results to database: 2.58 if (logging) printf("No supporters for any issue. Writing ranks to database.\n"); 2.59 - err = write_ranks(db, escaped_area_id); 2.60 + err = write_ranks(db, escaped_area_or_unit_id, mode); 2.61 if (logging) printf("Done.\n"); 2.62 return 0; 2.63 } 2.64 @@ -377,7 +372,7 @@ 2.65 2.66 // write results to database: 2.67 if (logging) printf("Writing ranks to database.\n"); 2.68 - err = write_ranks(db, escaped_area_id); 2.69 + err = write_ranks(db, escaped_area_or_unit_id, mode); 2.70 if (logging) printf("Done.\n"); 2.71 2.72 // free candidates[] array: 2.73 @@ -445,6 +440,23 @@ 2.74 return 1; 2.75 } 2.76 2.77 + // create missing "issue_order_in_admission_state" entries for issues 2.78 + 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"); 2.79 + if (!res) { 2.80 + fprintf(stderr, "Error in pqlib while sending SQL command creating new issue order entries.\n"); 2.81 + err = 1; 2.82 + } else if ( 2.83 + PQresultStatus(res) != PGRES_COMMAND_OK && 2.84 + PQresultStatus(res) != PGRES_TUPLES_OK 2.85 + ) { 2.86 + fprintf(stderr, "Error while executing SQL command creating new issue order entries:\n%s", PQresultErrorMessage(res)); 2.87 + err = 1; 2.88 + PQclear(res); 2.89 + } else { 2.90 + if (logging) printf("Created %s new issue order entries.\n", PQcmdTuples(res)); 2.91 + PQclear(res); 2.92 + } 2.93 + 2.94 // go through areas: 2.95 res = PQexec(db, "SELECT \"id\" FROM \"area\""); 2.96 if (!res) { 2.97 @@ -490,7 +502,7 @@ 2.98 err = 1; 2.99 PQclear(res2); 2.100 } else { 2.101 - if (process_area(db, res2, escaped_area_id)) err = 1; 2.102 + if (process_area_or_unit(db, res2, escaped_area_id, "area")) err = 1; 2.103 PQclear(res2); 2.104 } 2.105 freemem(escaped_area_id); 2.106 @@ -498,8 +510,61 @@ 2.107 PQclear(res); 2.108 } 2.109 2.110 + // go through units: 2.111 + res = PQexec(db, "SELECT \"id\" FROM \"unit\""); 2.112 + if (!res) { 2.113 + fprintf(stderr, "Error in pqlib while sending SQL command selecting units to process.\n"); 2.114 + err = 1; 2.115 + } else if (PQresultStatus(res) != PGRES_TUPLES_OK) { 2.116 + fprintf(stderr, "Error while executing SQL command selecting units to process:\n%s", PQresultErrorMessage(res)); 2.117 + err = 1; 2.118 + PQclear(res); 2.119 + } else if (PQnfields(res) < 1) { 2.120 + fprintf(stderr, "Too few columns returned by SQL command selecting units to process.\n"); 2.121 + err = 1; 2.122 + PQclear(res); 2.123 + } else { 2.124 + count = PQntuples(res); 2.125 + if (logging) printf("Number of units to process: %i\n", count); 2.126 + for (i=0; i<count; i++) { 2.127 + char *unit_id, *escaped_unit_id; 2.128 + char *cmd; 2.129 + PGresult *res2; 2.130 + unit_id = PQgetvalue(res, i, 0); 2.131 + if (logging) printf("Processing unit #%s:\n", unit_id); 2.132 + escaped_unit_id = escapeLiteral(db, unit_id, strlen(unit_id)); 2.133 + if (!escaped_unit_id) { 2.134 + fprintf(stderr, "Could not escape literal in memory.\n"); 2.135 + abort(); 2.136 + } 2.137 + if (asprintf(&cmd, "SELECT \"member_id\", \"weight\", \"issue_id\" FROM \"issue_supporter_in_admission_state\" WHERE \"unit_id\" = %s ORDER BY \"member_id\"", escaped_unit_id) < 0) { 2.138 + fprintf(stderr, "Could not prepare query string in memory.\n"); 2.139 + abort(); 2.140 + } 2.141 + res2 = PQexec(db, cmd); 2.142 + free(cmd); 2.143 + if (!res2) { 2.144 + fprintf(stderr, "Error in pqlib while sending SQL command selecting issue supporter in admission state.\n"); 2.145 + err = 1; 2.146 + } else if (PQresultStatus(res2) != PGRES_TUPLES_OK) { 2.147 + fprintf(stderr, "Error while executing SQL command selecting issue supporter in admission state:\n%s", PQresultErrorMessage(res)); 2.148 + err = 1; 2.149 + PQclear(res2); 2.150 + } else if (PQnfields(res2) < 3) { 2.151 + fprintf(stderr, "Too few columns returned by SQL command selecting issue supporter in admission state.\n"); 2.152 + err = 1; 2.153 + PQclear(res2); 2.154 + } else { 2.155 + if (process_area_or_unit(db, res2, escaped_unit_id, "unit")) err = 1; 2.156 + PQclear(res2); 2.157 + } 2.158 + freemem(escaped_unit_id); 2.159 + } 2.160 + PQclear(res); 2.161 + } 2.162 + 2.163 // clean-up entries of deleted issues 2.164 - res = PQexec(db, "DELETE FROM \"issue_order\" USING \"issue_order\" AS \"issue_order2\" NATURAL LEFT JOIN \"issue\" WHERE \"issue_order\".\"id\" = \"issue_order2\".\"id\" AND \"issue\".\"id\" ISNULL"); 2.165 + res = PQexec(db, "DELETE FROM \"issue_order_in_admission_state\" USING \"issue_order_in_admission_state\" AS \"self\" NATURAL LEFT JOIN \"issue\" WHERE \"issue_order_in_admission_state\".\"id\" = \"self\".\"id\" AND (\"issue\".\"id\" ISNULL OR \"issue\".\"state\" != 'admission'::\"issue_state\")"); 2.166 if (!res) { 2.167 fprintf(stderr, "Error in pqlib while sending SQL command deleting ordering data of deleted issues.\n"); 2.168 err = 1;
3.1 --- a/update/core-update.v2.2.5-v2.2.6.sql Mon Oct 14 00:58:38 2013 +0200 3.2 +++ b/update/core-update.v2.2.5-v2.2.6.sql Mon Oct 14 19:36:33 2013 +0200 3.3 @@ -4,24 +4,26 @@ 3.4 SELECT * FROM (VALUES ('2.2.6', 2, 2, 6)) 3.5 AS "subquery"("string", "major", "minor", "revision"); 3.6 3.7 -CREATE TABLE "issue_order" ( 3.8 +CREATE TABLE "issue_order_in_admission_state" ( 3.9 "id" INT8 PRIMARY KEY, --REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 3.10 - "order_in_admission_state" INT4, 3.11 - "max_supporter_count" INT4 ); 3.12 + "order_in_area" INT4, 3.13 + "order_in_unit" INT4 ); 3.14 3.15 -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'; 3.16 +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"'; 3.17 3.18 -COMMENT ON COLUMN "issue_order"."id" IS 'References "issue" ("id") but has no referential integrity trigger associated, due to performance/locking issues'; 3.19 -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"'; 3.20 -COMMENT ON COLUMN "issue_order"."max_supporter_count" IS 'Secondary sorting key when displaying issues in admission state from different areas'; 3.21 +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'; 3.22 +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'; 3.23 +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'; 3.24 3.25 CREATE VIEW "issue_supporter_in_admission_state" AS 3.26 SELECT DISTINCT 3.27 + "area"."unit_id", 3.28 "issue"."area_id", 3.29 "issue"."id" AS "issue_id", 3.30 "supporter"."member_id", 3.31 "direct_interest_snapshot"."weight" 3.32 FROM "issue" 3.33 + JOIN "area" ON "area"."id" = "issue"."area_id" 3.34 JOIN "supporter" ON "supporter"."issue_id" = "issue"."id" 3.35 JOIN "direct_interest_snapshot" 3.36 ON "direct_interest_snapshot"."issue_id" = "issue"."id"