# 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 +#include +#include +#include +#include + +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 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 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 \n", argv[0]); + fprintf(stdout, "\n"); + fprintf(stdout, " 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; i1) 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