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 +}