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"

Impressum / About Us