liquid_feedback_core

changeset 352:98c14d8d07f1

Support for proportional ordering of suggestions in core.sql; Begin of work on "lf_update_suggestion_order" (a second background job for sorting suggestions based on a proportional preferential voting system)
author jbe
date Sat Mar 16 17:22:01 2013 +0100 (2013-03-16)
parents b836c8782dc4
children 31ce1877320b
files Makefile core.sql lf_update_suggestion_order.c
line diff
     1.1 --- a/Makefile	Sun Mar 10 19:50:54 2013 +0100
     1.2 +++ b/Makefile	Sat Mar 16 17:22:01 2013 +0100
     1.3 @@ -1,8 +1,16 @@
     1.4 +all:: lf_update lf_update_suggestion_order
     1.5 +
     1.6  lf_update: lf_update.c
     1.7  	cc	-Wall -g \
     1.8  		-I "`pg_config --includedir`" \
     1.9  		-L "`pg_config --libdir`" \
    1.10  		-o lf_update lf_update.c -lpq
    1.11  
    1.12 +lf_update_suggestion_order: lf_update_suggestion_order.c
    1.13 +	cc	-Wall -g \
    1.14 +		-I "`pg_config --includedir`" \
    1.15 +		-L "`pg_config --libdir`" \
    1.16 +		-o lf_update_suggestion_order lf_update_suggestion_order.c -lpq
    1.17 +
    1.18  clean::
    1.19  	rm -f lf_update
     2.1 --- a/core.sql	Sun Mar 10 19:50:54 2013 +0100
     2.2 +++ b/core.sql	Sat Mar 16 17:22:01 2013 +0100
     2.3 @@ -7,7 +7,7 @@
     2.4  BEGIN;
     2.5  
     2.6  CREATE VIEW "liquid_feedback_version" AS
     2.7 -  SELECT * FROM (VALUES ('2.2.0', 2, 2, 0))
     2.8 +  SELECT * FROM (VALUES ('2.2.1', 2, 2, 1))
     2.9    AS "subquery"("string", "major", "minor", "revision");
    2.10  
    2.11  
    2.12 @@ -610,6 +610,7 @@
    2.13          "satisfied_supporter_count"          INT4,
    2.14          "satisfied_informed_supporter_count" INT4,
    2.15          "harmonic_weight"       NUMERIC(12, 3),
    2.16 +        "final_suggestion_order_calculated" BOOLEAN NOT NULL DEFAULT FALSE,
    2.17          "positive_votes"        INT4,
    2.18          "negative_votes"        INT4,
    2.19          "direct_majority"       BOOLEAN,
    2.20 @@ -666,6 +667,7 @@
    2.21  COMMENT ON COLUMN "initiative"."satisfied_supporter_count"          IS 'Calculated from table "direct_supporter_snapshot"';
    2.22  COMMENT ON COLUMN "initiative"."satisfied_informed_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
    2.23  COMMENT ON COLUMN "initiative"."harmonic_weight"        IS 'Indicates the relevancy of the initiative, calculated from the potential supporters weighted with the harmonic series to avoid a large number of clones affecting other initiative''s sorting positions too much; shall be used as secondary sorting key after "admitted" as primary sorting key';
    2.24 +COMMENT ON COLUMN "initiative"."final_suggestion_order_calculated" IS 'Set to TRUE, when "proportional_order" of suggestions has been calculated the last time';
    2.25  COMMENT ON COLUMN "initiative"."positive_votes"         IS 'Calculated from table "direct_voter"';
    2.26  COMMENT ON COLUMN "initiative"."negative_votes"         IS 'Calculated from table "direct_voter"';
    2.27  COMMENT ON COLUMN "initiative"."direct_majority"        IS 'TRUE, if "positive_votes"/("positive_votes"+"negative_votes") is strictly greater or greater-equal than "direct_majority_num"/"direct_majority_den", and "positive_votes" is greater-equal than "direct_majority_positive", and ("positive_votes"+abstentions) is greater-equal than "direct_majority_non_negative"';
    2.28 @@ -768,7 +770,8 @@
    2.29          "plus1_unfulfilled_count"  INT4,
    2.30          "plus1_fulfilled_count"    INT4,
    2.31          "plus2_unfulfilled_count"  INT4,
    2.32 -        "plus2_fulfilled_count"    INT4 );
    2.33 +        "plus2_fulfilled_count"    INT4,
    2.34 +        "proportional_order"    INT4 );
    2.35  CREATE INDEX "suggestion_created_idx" ON "suggestion" ("created");
    2.36  CREATE INDEX "suggestion_author_id_created_idx" ON "suggestion" ("author_id", "created");
    2.37  CREATE INDEX "suggestion_text_search_data_idx" ON "suggestion" USING gin ("text_search_data");
    2.38 @@ -789,6 +792,7 @@
    2.39  COMMENT ON COLUMN "suggestion"."plus1_fulfilled_count"    IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
    2.40  COMMENT ON COLUMN "suggestion"."plus2_unfulfilled_count"  IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
    2.41  COMMENT ON COLUMN "suggestion"."plus2_fulfilled_count"    IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
    2.42 +COMMENT ON COLUMN "suggestion"."proportional_order"       IS 'To be used for sorting suggestions within an initiative; updated by "lf_update_suggestion_order"';
    2.43  
    2.44  
    2.45  CREATE TABLE "rendered_suggestion" (
    2.46 @@ -2028,6 +2032,51 @@
    2.47  COMMENT ON VIEW "critical_opinion" IS 'Opinions currently causing dissatisfaction';
    2.48  
    2.49  
    2.50 +CREATE VIEW "initiative_suggestion_order_calculation" AS
    2.51 +  SELECT
    2.52 +    "initiative"."id" AS "initiative_id",
    2.53 +    ("issue"."closed" NOTNULL OR "issue"."fully_frozen" NOTNULL) AS "final"
    2.54 +  FROM "initiative" JOIN "issue"
    2.55 +  ON "initiative"."issue_id" = "issue"."id"
    2.56 +  WHERE ("issue"."closed" ISNULL AND "issue"."fully_frozen" ISNULL)
    2.57 +  OR ("initiative"."final_suggestion_order_calculated" = FALSE);
    2.58 +
    2.59 +COMMENT ON VIEW "initiative_suggestion_order_calculation" IS 'Initiatives, where the "proportional_order" of its suggestions has to be calculated';
    2.60 +
    2.61 +COMMENT ON COLUMN "initiative_suggestion_order_calculation"."final" IS 'Set to TRUE, if the issue is fully frozen or closed, and the calculation has only be done for one last time';
    2.62 +
    2.63 +
    2.64 +CREATE VIEW "individual_suggestion_ranking" AS
    2.65 +  SELECT
    2.66 +    "opinion"."initiative_id",
    2.67 +    "opinion"."member_id",
    2.68 +    "direct_interest_snapshot"."weight",
    2.69 +    CASE WHEN
    2.70 +      ("opinion"."degree" = 2 AND "opinion"."fulfilled" = FALSE) OR
    2.71 +      ("opinion"."degree" = -2 AND "opinion"."fulfilled" = TRUE)
    2.72 +    THEN 1 ELSE
    2.73 +      CASE WHEN
    2.74 +        ("opinion"."degree" = 1 AND "opinion"."fulfilled" = FALSE) OR
    2.75 +        ("opinion"."degree" = -1 AND "opinion"."fulfilled" = TRUE)
    2.76 +      THEN 2 ELSE
    2.77 +        CASE WHEN
    2.78 +          ("opinion"."degree" = 2 AND "opinion"."fulfilled" = TRUE) OR
    2.79 +          ("opinion"."degree" = -2 AND "opinion"."fulfilled" = FALSE)
    2.80 +        THEN 3 ELSE 4 END
    2.81 +      END
    2.82 +    END AS "preference",
    2.83 +    "opinion"."suggestion_id"
    2.84 +  FROM "opinion"
    2.85 +  JOIN "initiative" ON "initiative"."id" = "opinion"."initiative_id"
    2.86 +  JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
    2.87 +  JOIN "direct_interest_snapshot"
    2.88 +    ON  "direct_interest_snapshot"."issue_id" = "issue"."id"
    2.89 +    AND "direct_interest_snapshot"."event" = "issue"."latest_snapshot_event"
    2.90 +    AND "direct_interest_snapshot"."member_id" = "opinion"."member_id";
    2.91 +
    2.92 +COMMENT ON VIEW "individual_suggestion_ranking" IS 'Helper view for "lf_update_suggestion_order" to allow a proportional ordering of suggestions within an initiative';
    2.93 +
    2.94 +
    2.95  CREATE VIEW "battle_participant" AS
    2.96      SELECT "initiative"."id", "initiative"."issue_id"
    2.97      FROM "issue" JOIN "initiative"
     3.1 --- /dev/null	Thu Jan 01 00:00:00 1970 +0000
     3.2 +++ b/lf_update_suggestion_order.c	Sat Mar 16 17:22:01 2013 +0100
     3.3 @@ -0,0 +1,272 @@
     3.4 +#include <stdlib.h>
     3.5 +#include <stdio.h>
     3.6 +#include <string.h>
     3.7 +#include <libpq-fe.h>
     3.8 +#include <search.h>
     3.9 +
    3.10 +static char *escapeLiteral(PGconn *conn, const char *str, size_t len) {
    3.11 +  // provides compatibility for PostgreSQL versions prior 9.0
    3.12 +  // in future: return PQescapeLiteral(conn, str, len);
    3.13 +  char *res;
    3.14 +  size_t res_len;
    3.15 +  res = malloc(2*len+3);
    3.16 +  res[0] = '\'';
    3.17 +  res_len = PQescapeStringConn(conn, res+1, str, len, NULL);
    3.18 +  res[res_len+1] = '\'';
    3.19 +  res[res_len+2] = 0;
    3.20 +  return res;
    3.21 +}
    3.22 +
    3.23 +static void freemem(void *ptr) {
    3.24 +  // to be used for "escapeLiteral" function
    3.25 +  // provides compatibility for PostgreSQL versions prior 9.0
    3.26 +  // in future: PQfreemem(ptr);
    3.27 +  free(ptr);
    3.28 +}
    3.29 +
    3.30 +#define COL_MEMBER_ID     0
    3.31 +#define COL_WEIGHT        1
    3.32 +#define COL_PREFERENCE    2
    3.33 +#define COL_SUGGESTION_ID 3
    3.34 +
    3.35 +static int candidate_count;
    3.36 +static char **candidates;
    3.37 +
    3.38 +static void register_candidate(char **candidate, VISIT visit, int level) {
    3.39 +  if (visit == postorder || visit == leaf) {
    3.40 +    candidates[candidate_count++] = *candidate;
    3.41 +  }
    3.42 +}
    3.43 +
    3.44 +static int ptrstrcmp(char **s1, char **s2) {
    3.45 +  return strcmp(*s1, *s2);
    3.46 +}
    3.47 +
    3.48 +static int candidate_number(char *candidate) {
    3.49 +  char **addr;
    3.50 +  addr = bsearch(&candidate, candidates, candidate_count, sizeof(char *), (void *)ptrstrcmp);
    3.51 +  if (!addr) {
    3.52 +    fprintf(stderr, "Candidate not found (should not happen)\n");
    3.53 +    abort();
    3.54 +  }
    3.55 +  return addr - candidates;
    3.56 +}
    3.57 +
    3.58 +struct ballot_section {
    3.59 +  int count;
    3.60 +  int *candidates;
    3.61 +};
    3.62 +
    3.63 +struct ballot {
    3.64 +  int weight;
    3.65 +  struct ballot_section sections[4];
    3.66 +};
    3.67 +
    3.68 +static void process_initiative(PGresult *res) {
    3.69 +  void *candidate_tree = NULL;
    3.70 +  int ballot_count = 0;
    3.71 +  int tuple_count, i;
    3.72 +  char *old_member_id = NULL;
    3.73 +  struct ballot *ballots, *ballot;
    3.74 +  int candidates_in_sections[4] = {0, };
    3.75 +  candidate_count = 0;
    3.76 +  tuple_count = PQntuples(res);
    3.77 +  for (i=0; i<=tuple_count; i++) {
    3.78 +    char *member_id, *suggestion_id;
    3.79 +    if (i<tuple_count) {
    3.80 +      member_id = PQgetvalue(res, i, COL_MEMBER_ID);
    3.81 +      suggestion_id = PQgetvalue(res, i, COL_SUGGESTION_ID);
    3.82 +      if (!candidate_tree || !tfind(suggestion_id, &candidate_tree, (void *)strcmp)) {
    3.83 +        candidate_count++;
    3.84 +        if (!tsearch(suggestion_id, &candidate_tree, (void *)strcmp)) {
    3.85 +          fprintf(stderr, "Insufficient memory\n");
    3.86 +          abort();
    3.87 +        }
    3.88 +      }
    3.89 +    }
    3.90 +    if (i==tuple_count || (old_member_id && strcmp(old_member_id, member_id))) {
    3.91 +      ballot_count++;
    3.92 +    }
    3.93 +    old_member_id = member_id;
    3.94 +  }
    3.95 +  printf("Candidate count: %i\n", candidate_count);
    3.96 +  candidates = malloc(candidate_count * sizeof(char *));
    3.97 +  if (!candidates) {
    3.98 +    fprintf(stderr, "Insufficient memory\n");
    3.99 +    abort();
   3.100 +  }
   3.101 +  candidate_count = 0;
   3.102 +  twalk(candidate_tree, (void *)register_candidate);
   3.103 +  while (candidate_tree) tdelete(*(void **)candidate_tree, &candidate_tree, (void *)strcmp);
   3.104 +  printf("Ballot count: %i\n", ballot_count);
   3.105 +  ballots = calloc(ballot_count, sizeof(struct ballot));
   3.106 +  if (!ballots) {
   3.107 +    fprintf(stderr, "Insufficient memory\n");
   3.108 +    abort();
   3.109 +  }
   3.110 +  ballot = ballots;
   3.111 +  for (i=0; i<=tuple_count; i++) {
   3.112 +    char *member_id, *suggestion_id;
   3.113 +    int weight, preference;
   3.114 +    if (i<tuple_count) {
   3.115 +      member_id = PQgetvalue(res, i, COL_MEMBER_ID);
   3.116 +      suggestion_id = PQgetvalue(res, i, COL_SUGGESTION_ID);
   3.117 +      weight = (int)strtol(PQgetvalue(res, i, COL_WEIGHT), (char **)NULL, 10);
   3.118 +      if (weight <= 0) {
   3.119 +        fprintf(stderr, "Unexpected weight value\n");
   3.120 +        abort();
   3.121 +      }
   3.122 +      preference = (int)strtol(PQgetvalue(res, i, COL_PREFERENCE), (char **)NULL, 10);
   3.123 +      if (preference < 1 || preference > 4) {
   3.124 +        fprintf(stderr, "Unexpected preference value\n");
   3.125 +        abort();
   3.126 +      }
   3.127 +      preference--;
   3.128 +      ballot->weight = weight;
   3.129 +      ballot->sections[preference].count++;
   3.130 +    }
   3.131 +    if (i==tuple_count || (old_member_id && strcmp(old_member_id, member_id))) {
   3.132 +      ballot++;
   3.133 +    }
   3.134 +    old_member_id = member_id;
   3.135 +  }
   3.136 +  for (i=0; i<ballot_count; i++) {
   3.137 +    int j;
   3.138 +    for (j=0; j<4; j++) {
   3.139 +      if (ballots[i].sections[j].count) {
   3.140 +        ballots[i].sections[j].candidates = malloc(ballots[i].sections[j].count * sizeof(int));
   3.141 +        if (!ballots[i].sections[j].candidates) {
   3.142 +          fprintf(stderr, "Insufficient memory\n");
   3.143 +          abort();
   3.144 +        }
   3.145 +      }
   3.146 +    }
   3.147 +  }
   3.148 +  ballot = ballots;
   3.149 +  for (i=0; i<=tuple_count; i++) {
   3.150 +    char *member_id, *suggestion_id;
   3.151 +    int preference;
   3.152 +    if (i<tuple_count) {
   3.153 +      member_id = PQgetvalue(res, i, COL_MEMBER_ID);
   3.154 +      suggestion_id = PQgetvalue(res, i, COL_SUGGESTION_ID);
   3.155 +      preference = (int)strtol(PQgetvalue(res, i, COL_PREFERENCE), (char **)NULL, 10);
   3.156 +      if (preference < 1 || preference > 4) {
   3.157 +        fprintf(stderr, "Unexpected preference value\n");
   3.158 +        abort();
   3.159 +      }
   3.160 +      preference--;
   3.161 +      ballot->sections[preference].candidates[candidates_in_sections[preference]++] = candidate_number(suggestion_id);
   3.162 +    }
   3.163 +    if (i==tuple_count || (old_member_id && strcmp(old_member_id, member_id))) {
   3.164 +      ballot++;
   3.165 +      candidates_in_sections[0] = 0;
   3.166 +      candidates_in_sections[1] = 0;
   3.167 +      candidates_in_sections[2] = 0;
   3.168 +      candidates_in_sections[3] = 0;
   3.169 +    }
   3.170 +    old_member_id = member_id;
   3.171 +  }
   3.172 +}
   3.173 +
   3.174 +int main(int argc, char **argv) {
   3.175 +
   3.176 +  // variable declarations:
   3.177 +  int err = 0;
   3.178 +  int i, count;
   3.179 +  char *conninfo;
   3.180 +  PGconn *db;
   3.181 +  PGresult *res;
   3.182 +
   3.183 +  // parse command line:
   3.184 +  if (argc == 0) return 1;
   3.185 +  if (argc == 1 || !strcmp(argv[1], "-h") || !strcmp(argv[1], "--help")) {
   3.186 +    FILE *out;
   3.187 +    out = argc == 1 ? stderr : stdout;
   3.188 +    fprintf(stdout, "\n");
   3.189 +    fprintf(stdout, "Usage: %s <conninfo>\n", argv[0]);
   3.190 +    fprintf(stdout, "\n");
   3.191 +    fprintf(stdout, "<conninfo> is specified by PostgreSQL's libpq,\n");
   3.192 +    fprintf(stdout, "see http://www.postgresql.org/docs/9.1/static/libpq-connect.html\n");
   3.193 +    fprintf(stdout, "\n");
   3.194 +    fprintf(stdout, "Example: %s dbname=liquid_feedback\n", argv[0]);
   3.195 +    fprintf(stdout, "\n");
   3.196 +    return argc == 1 ? 1 : 0;
   3.197 +  }
   3.198 +  {
   3.199 +    size_t len = 0;
   3.200 +    for (i=1; i<argc; i++) len += strlen(argv[i]) + 1;
   3.201 +    conninfo = malloc(len * sizeof(char));
   3.202 +    if (!conninfo) {
   3.203 +      fprintf(stderr, "Error: Could not allocate memory for conninfo string\n");
   3.204 +      return 1;
   3.205 +    }
   3.206 +    conninfo[0] = 0;
   3.207 +    for (i=1; i<argc; i++) {
   3.208 +      if (i>1) strcat(conninfo, " ");
   3.209 +      strcat(conninfo, argv[i]);
   3.210 +    }
   3.211 +  }
   3.212 +
   3.213 +  // connect to database:
   3.214 +  db = PQconnectdb(conninfo);
   3.215 +  if (!db) {
   3.216 +    fprintf(stderr, "Error: Could not create database handle\n");
   3.217 +    return 1;
   3.218 +  }
   3.219 +  if (PQstatus(db) != CONNECTION_OK) {
   3.220 +    fprintf(stderr, "Could not open connection:\n%s", PQerrorMessage(db));
   3.221 +    return 1;
   3.222 +  }
   3.223 +
   3.224 +  // check initiatives:
   3.225 +  res = PQexec(db, "SELECT \"initiative_id\", \"final\" FROM \"initiative_suggestion_order_calculation\"");
   3.226 +  if (!res) {
   3.227 +    fprintf(stderr, "Error in pqlib while sending SQL command selecting open issues\n");
   3.228 +    err = 1;
   3.229 +  } else if (PQresultStatus(res) != PGRES_TUPLES_OK) {
   3.230 +    fprintf(stderr, "Error while executing SQL command selecting open issues:\n%s", PQresultErrorMessage(res));
   3.231 +    err = 1;
   3.232 +    PQclear(res);
   3.233 +  } else {
   3.234 +    count = PQntuples(res);
   3.235 +    printf("Number of initiatives to process: %i\n", count);
   3.236 +    for (i=0; i<count; i++) {
   3.237 +      char *initiative_id, *escaped_initiative_id;
   3.238 +      char *cmd;
   3.239 +      PGresult *res2;
   3.240 +      initiative_id = PQgetvalue(res, i, 0);
   3.241 +      printf("Processing initiative_id: %s\n", initiative_id);
   3.242 +      escaped_initiative_id = escapeLiteral(db, initiative_id, strlen(initiative_id));
   3.243 +      if (asprintf(&cmd, "SELECT \"member_id\", \"weight\", \"preference\", \"suggestion_id\" FROM \"individual_suggestion_ranking\" WHERE \"initiative_id\" = %s ORDER BY \"member_id\", \"preference\"", escaped_initiative_id) < 0) {
   3.244 +        fprintf(stderr, "Could not prepare query string in memory.\n");
   3.245 +        err = 1;
   3.246 +        freemem(escaped_initiative_id);
   3.247 +        break;
   3.248 +      }
   3.249 +      res2 = PQexec(db, cmd);
   3.250 +      free(cmd);
   3.251 +      if (!res2) {
   3.252 +        fprintf(stderr, "Error in pqlib while sending SQL command selecting open issues\n");
   3.253 +        err = 1;
   3.254 +      } else if (PQresultStatus(res2) != PGRES_TUPLES_OK) {
   3.255 +        fprintf(stderr, "Error while executing SQL command selecting open issues:\n%s", PQresultErrorMessage(res));
   3.256 +        err = 1;
   3.257 +        PQclear(res2);
   3.258 +      } else {
   3.259 +        if (PQntuples(res2) == 0) {
   3.260 +          printf("Nothing to do.\n");
   3.261 +        } else {
   3.262 +          process_initiative(res2);
   3.263 +        }
   3.264 +        PQclear(res2);
   3.265 +      }
   3.266 +      freemem(escaped_initiative_id);
   3.267 +    }
   3.268 +    PQclear(res);
   3.269 +  }
   3.270 +
   3.271 +  // cleanup and exit
   3.272 +  PQfinish(db);
   3.273 +  return err;
   3.274 +
   3.275 +}

Impressum / About Us