# HG changeset patch
# User jbe
# Date 1363450921 -3600
# Node ID 98c14d8d07f13edb02199f0396567b8edb36c826
# Parent  b836c8782dc4589c19e4f2d1266729ca397c4409
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)

diff -r b836c8782dc4 -r 98c14d8d07f1 Makefile
--- a/Makefile	Sun Mar 10 19:50:54 2013 +0100
+++ b/Makefile	Sat Mar 16 17:22:01 2013 +0100
@@ -1,8 +1,16 @@
+all:: lf_update lf_update_suggestion_order
+
 lf_update: lf_update.c
 	cc	-Wall -g \
 		-I "`pg_config --includedir`" \
 		-L "`pg_config --libdir`" \
 		-o lf_update lf_update.c -lpq
 
+lf_update_suggestion_order: lf_update_suggestion_order.c
+	cc	-Wall -g \
+		-I "`pg_config --includedir`" \
+		-L "`pg_config --libdir`" \
+		-o lf_update_suggestion_order lf_update_suggestion_order.c -lpq
+
 clean::
 	rm -f lf_update
diff -r b836c8782dc4 -r 98c14d8d07f1 core.sql
--- a/core.sql	Sun Mar 10 19:50:54 2013 +0100
+++ b/core.sql	Sat Mar 16 17:22:01 2013 +0100
@@ -7,7 +7,7 @@
 BEGIN;
 
 CREATE VIEW "liquid_feedback_version" AS
-  SELECT * FROM (VALUES ('2.2.0', 2, 2, 0))
+  SELECT * FROM (VALUES ('2.2.1', 2, 2, 1))
   AS "subquery"("string", "major", "minor", "revision");
 
 
@@ -610,6 +610,7 @@
         "satisfied_supporter_count"          INT4,
         "satisfied_informed_supporter_count" INT4,
         "harmonic_weight"       NUMERIC(12, 3),
+        "final_suggestion_order_calculated" BOOLEAN NOT NULL DEFAULT FALSE,
         "positive_votes"        INT4,
         "negative_votes"        INT4,
         "direct_majority"       BOOLEAN,
@@ -666,6 +667,7 @@
 COMMENT ON COLUMN "initiative"."satisfied_supporter_count"          IS 'Calculated from table "direct_supporter_snapshot"';
 COMMENT ON COLUMN "initiative"."satisfied_informed_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
 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';
+COMMENT ON COLUMN "initiative"."final_suggestion_order_calculated" IS 'Set to TRUE, when "proportional_order" of suggestions has been calculated the last time';
 COMMENT ON COLUMN "initiative"."positive_votes"         IS 'Calculated from table "direct_voter"';
 COMMENT ON COLUMN "initiative"."negative_votes"         IS 'Calculated from table "direct_voter"';
 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"';
@@ -768,7 +770,8 @@
         "plus1_unfulfilled_count"  INT4,
         "plus1_fulfilled_count"    INT4,
         "plus2_unfulfilled_count"  INT4,
-        "plus2_fulfilled_count"    INT4 );
+        "plus2_fulfilled_count"    INT4,
+        "proportional_order"    INT4 );
 CREATE INDEX "suggestion_created_idx" ON "suggestion" ("created");
 CREATE INDEX "suggestion_author_id_created_idx" ON "suggestion" ("author_id", "created");
 CREATE INDEX "suggestion_text_search_data_idx" ON "suggestion" USING gin ("text_search_data");
@@ -789,6 +792,7 @@
 COMMENT ON COLUMN "suggestion"."plus1_fulfilled_count"    IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
 COMMENT ON COLUMN "suggestion"."plus2_unfulfilled_count"  IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
 COMMENT ON COLUMN "suggestion"."plus2_fulfilled_count"    IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
+COMMENT ON COLUMN "suggestion"."proportional_order"       IS 'To be used for sorting suggestions within an initiative; updated by "lf_update_suggestion_order"';
 
 
 CREATE TABLE "rendered_suggestion" (
@@ -2028,6 +2032,51 @@
 COMMENT ON VIEW "critical_opinion" IS 'Opinions currently causing dissatisfaction';
 
 
+CREATE VIEW "initiative_suggestion_order_calculation" AS
+  SELECT
+    "initiative"."id" AS "initiative_id",
+    ("issue"."closed" NOTNULL OR "issue"."fully_frozen" NOTNULL) AS "final"
+  FROM "initiative" JOIN "issue"
+  ON "initiative"."issue_id" = "issue"."id"
+  WHERE ("issue"."closed" ISNULL AND "issue"."fully_frozen" ISNULL)
+  OR ("initiative"."final_suggestion_order_calculated" = FALSE);
+
+COMMENT ON VIEW "initiative_suggestion_order_calculation" IS 'Initiatives, where the "proportional_order" of its suggestions has to be calculated';
+
+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';
+
+
+CREATE VIEW "individual_suggestion_ranking" AS
+  SELECT
+    "opinion"."initiative_id",
+    "opinion"."member_id",
+    "direct_interest_snapshot"."weight",
+    CASE WHEN
+      ("opinion"."degree" = 2 AND "opinion"."fulfilled" = FALSE) OR
+      ("opinion"."degree" = -2 AND "opinion"."fulfilled" = TRUE)
+    THEN 1 ELSE
+      CASE WHEN
+        ("opinion"."degree" = 1 AND "opinion"."fulfilled" = FALSE) OR
+        ("opinion"."degree" = -1 AND "opinion"."fulfilled" = TRUE)
+      THEN 2 ELSE
+        CASE WHEN
+          ("opinion"."degree" = 2 AND "opinion"."fulfilled" = TRUE) OR
+          ("opinion"."degree" = -2 AND "opinion"."fulfilled" = FALSE)
+        THEN 3 ELSE 4 END
+      END
+    END AS "preference",
+    "opinion"."suggestion_id"
+  FROM "opinion"
+  JOIN "initiative" ON "initiative"."id" = "opinion"."initiative_id"
+  JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
+  JOIN "direct_interest_snapshot"
+    ON  "direct_interest_snapshot"."issue_id" = "issue"."id"
+    AND "direct_interest_snapshot"."event" = "issue"."latest_snapshot_event"
+    AND "direct_interest_snapshot"."member_id" = "opinion"."member_id";
+
+COMMENT ON VIEW "individual_suggestion_ranking" IS 'Helper view for "lf_update_suggestion_order" to allow a proportional ordering of suggestions within an initiative';
+
+
 CREATE VIEW "battle_participant" AS
     SELECT "initiative"."id", "initiative"."issue_id"
     FROM "issue" JOIN "initiative"
diff -r b836c8782dc4 -r 98c14d8d07f1 lf_update_suggestion_order.c
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/lf_update_suggestion_order.c	Sat Mar 16 17:22:01 2013 +0100
@@ -0,0 +1,272 @@
+#include <stdlib.h>
+#include <stdio.h>
+#include <string.h>
+#include <libpq-fe.h>
+#include <search.h>
+
+static char *escapeLiteral(PGconn *conn, const char *str, size_t len) {
+  // provides compatibility for PostgreSQL versions prior 9.0
+  // in future: return PQescapeLiteral(conn, str, len);
+  char *res;
+  size_t res_len;
+  res = malloc(2*len+3);
+  res[0] = '\'';
+  res_len = PQescapeStringConn(conn, res+1, str, len, NULL);
+  res[res_len+1] = '\'';
+  res[res_len+2] = 0;
+  return res;
+}
+
+static void freemem(void *ptr) {
+  // to be used for "escapeLiteral" function
+  // provides compatibility for PostgreSQL versions prior 9.0
+  // in future: PQfreemem(ptr);
+  free(ptr);
+}
+
+#define COL_MEMBER_ID     0
+#define COL_WEIGHT        1
+#define COL_PREFERENCE    2
+#define COL_SUGGESTION_ID 3
+
+static int candidate_count;
+static char **candidates;
+
+static void register_candidate(char **candidate, VISIT visit, int level) {
+  if (visit == postorder || visit == leaf) {
+    candidates[candidate_count++] = *candidate;
+  }
+}
+
+static int ptrstrcmp(char **s1, char **s2) {
+  return strcmp(*s1, *s2);
+}
+
+static int candidate_number(char *candidate) {
+  char **addr;
+  addr = bsearch(&candidate, candidates, candidate_count, sizeof(char *), (void *)ptrstrcmp);
+  if (!addr) {
+    fprintf(stderr, "Candidate not found (should not happen)\n");
+    abort();
+  }
+  return addr - candidates;
+}
+
+struct ballot_section {
+  int count;
+  int *candidates;
+};
+
+struct ballot {
+  int weight;
+  struct ballot_section sections[4];
+};
+
+static void process_initiative(PGresult *res) {
+  void *candidate_tree = NULL;
+  int ballot_count = 0;
+  int tuple_count, i;
+  char *old_member_id = NULL;
+  struct ballot *ballots, *ballot;
+  int candidates_in_sections[4] = {0, };
+  candidate_count = 0;
+  tuple_count = PQntuples(res);
+  for (i=0; i<=tuple_count; i++) {
+    char *member_id, *suggestion_id;
+    if (i<tuple_count) {
+      member_id = PQgetvalue(res, i, COL_MEMBER_ID);
+      suggestion_id = PQgetvalue(res, i, COL_SUGGESTION_ID);
+      if (!candidate_tree || !tfind(suggestion_id, &candidate_tree, (void *)strcmp)) {
+        candidate_count++;
+        if (!tsearch(suggestion_id, &candidate_tree, (void *)strcmp)) {
+          fprintf(stderr, "Insufficient memory\n");
+          abort();
+        }
+      }
+    }
+    if (i==tuple_count || (old_member_id && strcmp(old_member_id, member_id))) {
+      ballot_count++;
+    }
+    old_member_id = member_id;
+  }
+  printf("Candidate count: %i\n", candidate_count);
+  candidates = malloc(candidate_count * sizeof(char *));
+  if (!candidates) {
+    fprintf(stderr, "Insufficient memory\n");
+    abort();
+  }
+  candidate_count = 0;
+  twalk(candidate_tree, (void *)register_candidate);
+  while (candidate_tree) tdelete(*(void **)candidate_tree, &candidate_tree, (void *)strcmp);
+  printf("Ballot count: %i\n", ballot_count);
+  ballots = calloc(ballot_count, sizeof(struct ballot));
+  if (!ballots) {
+    fprintf(stderr, "Insufficient memory\n");
+    abort();
+  }
+  ballot = ballots;
+  for (i=0; i<=tuple_count; i++) {
+    char *member_id, *suggestion_id;
+    int weight, preference;
+    if (i<tuple_count) {
+      member_id = PQgetvalue(res, i, COL_MEMBER_ID);
+      suggestion_id = PQgetvalue(res, i, COL_SUGGESTION_ID);
+      weight = (int)strtol(PQgetvalue(res, i, COL_WEIGHT), (char **)NULL, 10);
+      if (weight <= 0) {
+        fprintf(stderr, "Unexpected weight value\n");
+        abort();
+      }
+      preference = (int)strtol(PQgetvalue(res, i, COL_PREFERENCE), (char **)NULL, 10);
+      if (preference < 1 || preference > 4) {
+        fprintf(stderr, "Unexpected preference value\n");
+        abort();
+      }
+      preference--;
+      ballot->weight = weight;
+      ballot->sections[preference].count++;
+    }
+    if (i==tuple_count || (old_member_id && strcmp(old_member_id, member_id))) {
+      ballot++;
+    }
+    old_member_id = member_id;
+  }
+  for (i=0; i<ballot_count; i++) {
+    int j;
+    for (j=0; j<4; j++) {
+      if (ballots[i].sections[j].count) {
+        ballots[i].sections[j].candidates = malloc(ballots[i].sections[j].count * sizeof(int));
+        if (!ballots[i].sections[j].candidates) {
+          fprintf(stderr, "Insufficient memory\n");
+          abort();
+        }
+      }
+    }
+  }
+  ballot = ballots;
+  for (i=0; i<=tuple_count; i++) {
+    char *member_id, *suggestion_id;
+    int preference;
+    if (i<tuple_count) {
+      member_id = PQgetvalue(res, i, COL_MEMBER_ID);
+      suggestion_id = PQgetvalue(res, i, COL_SUGGESTION_ID);
+      preference = (int)strtol(PQgetvalue(res, i, COL_PREFERENCE), (char **)NULL, 10);
+      if (preference < 1 || preference > 4) {
+        fprintf(stderr, "Unexpected preference value\n");
+        abort();
+      }
+      preference--;
+      ballot->sections[preference].candidates[candidates_in_sections[preference]++] = candidate_number(suggestion_id);
+    }
+    if (i==tuple_count || (old_member_id && strcmp(old_member_id, member_id))) {
+      ballot++;
+      candidates_in_sections[0] = 0;
+      candidates_in_sections[1] = 0;
+      candidates_in_sections[2] = 0;
+      candidates_in_sections[3] = 0;
+    }
+    old_member_id = member_id;
+  }
+}
+
+int main(int argc, char **argv) {
+
+  // variable declarations:
+  int err = 0;
+  int i, count;
+  char *conninfo;
+  PGconn *db;
+  PGresult *res;
+
+  // parse command line:
+  if (argc == 0) return 1;
+  if (argc == 1 || !strcmp(argv[1], "-h") || !strcmp(argv[1], "--help")) {
+    FILE *out;
+    out = argc == 1 ? stderr : stdout;
+    fprintf(stdout, "\n");
+    fprintf(stdout, "Usage: %s <conninfo>\n", argv[0]);
+    fprintf(stdout, "\n");
+    fprintf(stdout, "<conninfo> is specified by PostgreSQL's libpq,\n");
+    fprintf(stdout, "see http://www.postgresql.org/docs/9.1/static/libpq-connect.html\n");
+    fprintf(stdout, "\n");
+    fprintf(stdout, "Example: %s dbname=liquid_feedback\n", argv[0]);
+    fprintf(stdout, "\n");
+    return argc == 1 ? 1 : 0;
+  }
+  {
+    size_t len = 0;
+    for (i=1; i<argc; i++) len += strlen(argv[i]) + 1;
+    conninfo = malloc(len * sizeof(char));
+    if (!conninfo) {
+      fprintf(stderr, "Error: Could not allocate memory for conninfo string\n");
+      return 1;
+    }
+    conninfo[0] = 0;
+    for (i=1; i<argc; i++) {
+      if (i>1) strcat(conninfo, " ");
+      strcat(conninfo, argv[i]);
+    }
+  }
+
+  // connect to database:
+  db = PQconnectdb(conninfo);
+  if (!db) {
+    fprintf(stderr, "Error: Could not create database handle\n");
+    return 1;
+  }
+  if (PQstatus(db) != CONNECTION_OK) {
+    fprintf(stderr, "Could not open connection:\n%s", PQerrorMessage(db));
+    return 1;
+  }
+
+  // check initiatives:
+  res = PQexec(db, "SELECT \"initiative_id\", \"final\" FROM \"initiative_suggestion_order_calculation\"");
+  if (!res) {
+    fprintf(stderr, "Error in pqlib while sending SQL command selecting open issues\n");
+    err = 1;
+  } else if (PQresultStatus(res) != PGRES_TUPLES_OK) {
+    fprintf(stderr, "Error while executing SQL command selecting open issues:\n%s", PQresultErrorMessage(res));
+    err = 1;
+    PQclear(res);
+  } else {
+    count = PQntuples(res);
+    printf("Number of initiatives to process: %i\n", count);
+    for (i=0; i<count; i++) {
+      char *initiative_id, *escaped_initiative_id;
+      char *cmd;
+      PGresult *res2;
+      initiative_id = PQgetvalue(res, i, 0);
+      printf("Processing initiative_id: %s\n", initiative_id);
+      escaped_initiative_id = escapeLiteral(db, initiative_id, strlen(initiative_id));
+      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) {
+        fprintf(stderr, "Could not prepare query string in memory.\n");
+        err = 1;
+        freemem(escaped_initiative_id);
+        break;
+      }
+      res2 = PQexec(db, cmd);
+      free(cmd);
+      if (!res2) {
+        fprintf(stderr, "Error in pqlib while sending SQL command selecting open issues\n");
+        err = 1;
+      } else if (PQresultStatus(res2) != PGRES_TUPLES_OK) {
+        fprintf(stderr, "Error while executing SQL command selecting open issues:\n%s", PQresultErrorMessage(res));
+        err = 1;
+        PQclear(res2);
+      } else {
+        if (PQntuples(res2) == 0) {
+          printf("Nothing to do.\n");
+        } else {
+          process_initiative(res2);
+        }
+        PQclear(res2);
+      }
+      freemem(escaped_initiative_id);
+    }
+    PQclear(res);
+  }
+
+  // cleanup and exit
+  PQfinish(db);
+  return err;
+
+}