liquid_feedback_core
annotate lf_update.c @ 7:69d84040fb93
Version beta8
More attibutes in member table
Renamed column ident_number of member table to identification
Images of members are now stored in extra table member_image
Minor bugfix in init.sql: Added missing verification_time column
Full text index search support using PostgreSQL's TSVECTOR and TSQUERY datatypes
New function highlight(...), which helps to highlight matching words in search results
More attibutes in member table
Renamed column ident_number of member table to identification
Images of members are now stored in extra table member_image
Minor bugfix in init.sql: Added missing verification_time column
Full text index search support using PostgreSQL's TSVECTOR and TSQUERY datatypes
New function highlight(...), which helps to highlight matching words in search results
author | jbe |
---|---|
date | Mon Nov 23 12:00:00 2009 +0100 (2009-11-23) |
parents | 6133c0a62378 |
children | bdccc56fb705 |
rev | line source |
---|---|
jbe@0 | 1 #include <stdlib.h> |
jbe@0 | 2 #include <stdio.h> |
jbe@0 | 3 #include <string.h> |
jbe@0 | 4 #include <libpq-fe.h> |
jbe@0 | 5 |
jbe@0 | 6 int main(int argc, char **argv) { |
jbe@1 | 7 |
jbe@1 | 8 // variable declarations: |
jbe@0 | 9 int i, count; |
jbe@0 | 10 char *conninfo; |
jbe@0 | 11 PGconn *db; |
jbe@0 | 12 PGresult *list; |
jbe@0 | 13 PGresult *status; |
jbe@1 | 14 |
jbe@1 | 15 // parse command line: |
jbe@0 | 16 if (argc == 0) return 1; |
jbe@0 | 17 if (argc == 1 || !strcmp(argv[1], "-h") || !strcmp(argv[1], "--help")) { |
jbe@0 | 18 FILE *out; |
jbe@0 | 19 out = argc == 1 ? stderr : stdout; |
jbe@0 | 20 fprintf(stdout, "\n"); |
jbe@0 | 21 fprintf(stdout, "Usage: %s <conninfo>\n", argv[0]); |
jbe@0 | 22 fprintf(stdout, "\n"); |
jbe@0 | 23 fprintf(stdout, "<conninfo> is specified by PostgreSQL's libpq,\n"); |
jbe@0 | 24 fprintf(stdout, "see http://www.postgresql.org/docs/8.4/static/libpq-connect.html\n"); |
jbe@0 | 25 fprintf(stdout, "\n"); |
jbe@0 | 26 fprintf(stdout, "Example: %s dbname=liquid_feedback\n", argv[0]); |
jbe@0 | 27 fprintf(stdout, "\n"); |
jbe@0 | 28 return argc == 1 ? 1 : 0; |
jbe@0 | 29 } |
jbe@0 | 30 { |
jbe@0 | 31 size_t len = 0; |
jbe@0 | 32 for (i=1; i<argc; i++) len += strlen(argv[i]) + 1; |
jbe@0 | 33 conninfo = malloc(len * sizeof(char)); |
jbe@0 | 34 if (!conninfo) { |
jbe@0 | 35 fprintf(stderr, "Error: Could not allocate memory for conninfo string\n"); |
jbe@0 | 36 return 1; |
jbe@0 | 37 } |
jbe@0 | 38 conninfo[0] = 0; |
jbe@0 | 39 for (i=1; i<argc; i++) { |
jbe@0 | 40 if (i>1) strcat(conninfo, " "); |
jbe@0 | 41 strcat(conninfo, argv[i]); |
jbe@0 | 42 } |
jbe@0 | 43 } |
jbe@1 | 44 |
jbe@1 | 45 // connect to database: |
jbe@0 | 46 db = PQconnectdb(conninfo); |
jbe@0 | 47 if (!db) { |
jbe@0 | 48 fprintf(stderr, "Error: Could not create database handle\n"); |
jbe@0 | 49 return 1; |
jbe@0 | 50 } |
jbe@0 | 51 if (PQstatus(db) != CONNECTION_OK) { |
jbe@0 | 52 fprintf(stderr, "Could not open connection:\n%s", PQerrorMessage(db)); |
jbe@0 | 53 return 1; |
jbe@0 | 54 } |
jbe@1 | 55 |
jbe@1 | 56 // delete expired sessions: |
jbe@1 | 57 status = PQexec(db, "DELETE FROM \"expired_session\""); |
jbe@1 | 58 if (!status) { |
jbe@1 | 59 fprintf(stderr, "Error in pqlib while sending SQL command deleting expired sessions\n"); |
jbe@1 | 60 return 1; |
jbe@1 | 61 } |
jbe@1 | 62 if ( |
jbe@1 | 63 PQresultStatus(status) != PGRES_COMMAND_OK && |
jbe@1 | 64 PQresultStatus(status) != PGRES_TUPLES_OK |
jbe@1 | 65 ) { |
jbe@1 | 66 fprintf(stderr, "Error while executing SQL command deleting expired sessions:\n%s", PQresultErrorMessage(status)); |
jbe@1 | 67 return 1; |
jbe@1 | 68 } |
jbe@1 | 69 |
jbe@4 | 70 // calculate member counts: |
jbe@4 | 71 status = PQexec(db, "SELECT \"calculate_member_counts\"()"); |
jbe@4 | 72 if (!status) { |
jbe@4 | 73 fprintf(stderr, "Error in pqlib while sending SQL command calculating member counts\n"); |
jbe@4 | 74 return 1; |
jbe@4 | 75 } |
jbe@4 | 76 if ( |
jbe@4 | 77 PQresultStatus(status) != PGRES_COMMAND_OK && |
jbe@4 | 78 PQresultStatus(status) != PGRES_TUPLES_OK |
jbe@4 | 79 ) { |
jbe@4 | 80 fprintf(stderr, "Error while executing SQL command calculating member counts:\n%s", PQresultErrorMessage(status)); |
jbe@4 | 81 return 1; |
jbe@4 | 82 } |
jbe@4 | 83 |
jbe@1 | 84 // update open issues: |
jbe@0 | 85 list = PQexec(db, "SELECT \"id\" FROM \"open_issue\""); |
jbe@0 | 86 if (!list) { |
jbe@0 | 87 fprintf(stderr, "Error in pqlib while sending SQL command selecting open issues\n"); |
jbe@0 | 88 return 1; |
jbe@0 | 89 } |
jbe@0 | 90 if (PQresultStatus(list) != PGRES_TUPLES_OK) { |
jbe@0 | 91 fprintf(stderr, "Error while executing SQL command selecting open issues:\n%s", PQresultErrorMessage(list)); |
jbe@0 | 92 return 1; |
jbe@0 | 93 } |
jbe@0 | 94 count = PQntuples(list); |
jbe@0 | 95 for (i=0; i<count; i++) { |
jbe@0 | 96 const char *params[1]; |
jbe@0 | 97 params[0] = PQgetvalue(list, i, 0); |
jbe@0 | 98 status = PQexecParams( |
jbe@0 | 99 db, "SELECT \"check_issue\"($1)", 1, NULL, params, NULL, NULL, 0 |
jbe@0 | 100 ); |
jbe@1 | 101 if (!status) { |
jbe@1 | 102 fprintf(stderr, "Error in pqlib while sending SQL command to call function \"check_issue\"(...):\n"); |
jbe@1 | 103 return 1; |
jbe@1 | 104 } |
jbe@0 | 105 if ( |
jbe@0 | 106 PQresultStatus(status) != PGRES_COMMAND_OK && |
jbe@0 | 107 PQresultStatus(status) != PGRES_TUPLES_OK |
jbe@0 | 108 ) { |
jbe@0 | 109 fprintf(stderr, "Error while calling SQL function \"check_issue\"(...):\n%s", PQresultErrorMessage(status)); |
jbe@0 | 110 return 1; |
jbe@0 | 111 } |
jbe@0 | 112 PQclear(status); |
jbe@0 | 113 } |
jbe@0 | 114 PQclear(list); |
jbe@1 | 115 |
jbe@1 | 116 // calculate ranks after voting is finished: |
jbe@1 | 117 // (NOTE: This is a seperate process to avoid long transactions with locking) |
jbe@0 | 118 list = PQexec(db, "SELECT \"id\" FROM \"issue_with_ranks_missing\""); |
jbe@0 | 119 if (!list) { |
jbe@0 | 120 fprintf(stderr, "Error in pqlib while sending SQL command selecting issues where ranks are missing\n"); |
jbe@0 | 121 return 1; |
jbe@0 | 122 } |
jbe@0 | 123 if (PQresultStatus(list) != PGRES_TUPLES_OK) { |
jbe@0 | 124 fprintf(stderr, "Error while executing SQL command selecting issues where ranks are missing:\n%s", PQresultErrorMessage(list)); |
jbe@0 | 125 return 1; |
jbe@0 | 126 } |
jbe@0 | 127 count = PQntuples(list); |
jbe@0 | 128 for (i=0; i<count; i++) { |
jbe@0 | 129 const char *params[1]; |
jbe@0 | 130 params[0] = PQgetvalue(list, i, 0); |
jbe@0 | 131 status = PQexecParams( |
jbe@0 | 132 db, "SELECT \"calculate_ranks\"($1)", 1, NULL, params, NULL, NULL, 0 |
jbe@0 | 133 ); |
jbe@1 | 134 if (!status) { |
jbe@1 | 135 fprintf(stderr, "Error in pqlib while sending SQL command to call function \"calculate_ranks\"(...):\n"); |
jbe@1 | 136 return 1; |
jbe@1 | 137 } |
jbe@0 | 138 if ( |
jbe@0 | 139 PQresultStatus(status) != PGRES_COMMAND_OK && |
jbe@0 | 140 PQresultStatus(status) != PGRES_TUPLES_OK |
jbe@0 | 141 ) { |
jbe@0 | 142 fprintf(stderr, "Error while calling SQL function \"calculate_ranks\"(...):\n%s", PQresultErrorMessage(status)); |
jbe@0 | 143 return 1; |
jbe@0 | 144 } |
jbe@0 | 145 PQclear(status); |
jbe@0 | 146 } |
jbe@0 | 147 PQclear(list); |
jbe@1 | 148 |
jbe@1 | 149 // cleanup and exit |
jbe@0 | 150 PQfinish(db); |
jbe@0 | 151 return 0; |
jbe@1 | 152 |
jbe@0 | 153 } |