From 1defd30dfeb1867c2756b3fe6a437f695951d0c9 Mon Sep 17 00:00:00 2001 From: Christian Grothoff Date: Thu, 1 Jun 2017 21:48:19 +0200 Subject: adding more good helpers to libgnunetpq --- src/datacache/plugin_datacache_postgres.c | 4 +- src/datastore/plugin_datastore_postgres.c | 3 +- src/include/gnunet_pq_lib.h | 293 ++++++++++++++++++++++++++++++ src/namecache/plugin_namecache_postgres.c | 152 +++++++--------- src/namestore/plugin_namestore_postgres.c | 200 ++++++++++---------- src/postgres/postgres.c | 42 ----- src/pq/Makefile.am | 4 + src/pq/pq_connect.c | 127 +++++++++++++ src/pq/pq_eval.c | 249 +++++++++++++++++++++++++ src/pq/pq_exec.c | 106 +++++++++++ src/pq/pq_prepare.c | 92 ++++++++++ src/psycstore/plugin_psycstore_postgres.c | 219 +++++++++++----------- 12 files changed, 1144 insertions(+), 347 deletions(-) create mode 100644 src/pq/pq_connect.c create mode 100644 src/pq/pq_eval.c create mode 100644 src/pq/pq_exec.c create mode 100644 src/pq/pq_prepare.c diff --git a/src/datacache/plugin_datacache_postgres.c b/src/datacache/plugin_datacache_postgres.c index 13c2c26a2..8f5cdbde1 100644 --- a/src/datacache/plugin_datacache_postgres.c +++ b/src/datacache/plugin_datacache_postgres.c @@ -68,8 +68,8 @@ init_connection (struct Plugin *plugin) { PGresult *ret; - plugin->dbh = GNUNET_POSTGRES_connect (plugin->env->cfg, - "datacache-postgres"); + plugin->dbh = GNUNET_PQ_connect_with_cfg (plugin->env->cfg, + "datacache-postgres"); if (NULL == plugin->dbh) return GNUNET_SYSERR; ret = diff --git a/src/datastore/plugin_datastore_postgres.c b/src/datastore/plugin_datastore_postgres.c index b6aeb0be6..1c9ded4f4 100644 --- a/src/datastore/plugin_datastore_postgres.c +++ b/src/datastore/plugin_datastore_postgres.c @@ -72,7 +72,8 @@ init_connection (struct Plugin *plugin) { PGresult *ret; - plugin->dbh = GNUNET_POSTGRES_connect (plugin->env->cfg, "datastore-postgres"); + plugin->dbh = GNUNET_PQ_connect_with_cfg (plugin->env->cfg, + "datastore-postgres"); if (NULL == plugin->dbh) return GNUNET_SYSERR; diff --git a/src/include/gnunet_pq_lib.h b/src/include/gnunet_pq_lib.h index 756370b74..5e54813e3 100644 --- a/src/include/gnunet_pq_lib.h +++ b/src/include/gnunet_pq_lib.h @@ -25,6 +25,9 @@ #include "gnunet_util_lib.h" +/* ************************* pq_query_helper.c functions ************************ */ + + /** * Function called to convert input argument into SQL parameters. * @@ -188,6 +191,9 @@ struct GNUNET_PQ_QueryParam GNUNET_PQ_query_param_uint64 (const uint64_t *x); +/* ************************* pq_result_helper.c functions ************************ */ + + /** * Extract data from a Postgres database @a result at row @a row. * @@ -412,6 +418,8 @@ GNUNET_PQ_result_spec_uint64 (const char *name, uint64_t *u64); +/* ************************* pq.c functions ************************ */ + /** * Execute a prepared statement. * @@ -419,6 +427,7 @@ GNUNET_PQ_result_spec_uint64 (const char *name, * @param name name of the prepared statement * @param params parameters to the statement * @return postgres result + * @deprecated (should become an internal API) */ PGresult * GNUNET_PQ_exec_prepared (PGconn *db_conn, @@ -435,6 +444,7 @@ GNUNET_PQ_exec_prepared (PGconn *db_conn, * @return * #GNUNET_YES if all results could be extracted * #GNUNET_SYSERR if a result was invalid (non-existing field) + * @deprecated (should become an internal API) */ int GNUNET_PQ_extract_result (PGresult *result, @@ -452,6 +462,289 @@ void GNUNET_PQ_cleanup_result (struct GNUNET_PQ_ResultSpec *rs); +/* ******************** pq_eval.c functions ************** */ + + +/** + * Status code returned from functions running PQ commands. + * Can be combined with a function that returns the number + * of results, so non-negative values indicate success. + */ +enum GNUNET_PQ_QueryStatus +{ + /** + * A hard error occurred, retrying will not help. + */ + GNUNET_PQ_STATUS_HARD_ERROR = -2, + + /** + * A soft error occurred, retrying the transaction may succeed. + */ + GNUNET_PQ_STATUS_SOFT_ERROR = -1, + + /** + * The transaction succeeded, but yielded zero results. + */ + GNUNET_PQ_STATUS_SUCCESS_NO_RESULTS = 0, + + /** + * The transaction succeeded, and yielded one result. + */ + GNUNET_PQ_STATUS_SUCCESS_ONE_RESULT = 1 + +}; + + +/** + * Check the @a result's error code to see what happened. + * Also logs errors. + * + * @param connection connection to execute the statement in + * @param statement_name name of the statement that created @a result + * @param result result to check + * @return status code from the result, mapping PQ status + * codes to `enum GNUNET_PQ_QueryStatus`. Never + * returns positive values as this function does + * not look at the result set. + * @deprecated (low level, let's see if we can do with just the high-level functions) + */ +enum GNUNET_PQ_QueryStatus +GNUNET_PQ_eval_result (PGconn *connection, + const char *statement_name, + PGresult *result); + + +/** + * Execute a named prepared @a statement that is NOT a SELECT + * statement in @a connnection using the given @a params. Returns the + * resulting session state. + * + * @param connection connection to execute the statement in + * @param statement_name name of the statement + * @param params parameters to give to the statement (#GNUNET_PQ_query_param_end-terminated) + * @return status code from the result, mapping PQ status + * codes to `enum GNUNET_PQ_QueryStatus`. Never + * returns positive values as this function does + * not look at the result set. + */ +enum GNUNET_PQ_QueryStatus +GNUNET_PQ_eval_prepared_non_select (PGconn *connection, + const char *statement_name, + const struct GNUNET_PQ_QueryParam *params); + + +/** + * Function to be called with the results of a SELECT statement + * that has returned @a num_results results. + * + * @param cls closure + * @param result the postgres result + * @param num_result the number of results in @a result + */ +typedef void +(*GNUNET_PQ_PostgresResultHandler)(void *cls, + PGresult *result, + unsigned int num_results); + + +/** + * Execute a named prepared @a statement that is a SELECT statement + * which may return multiple results in @a connection using the given + * @a params. Call @a rh with the results. Returns the query + * status including the number of results given to @a rh (possibly zero). + * @a rh will not have been called if the return value is negative. + * + * @param connection connection to execute the statement in + * @param statement_name name of the statement + * @param params parameters to give to the statement (#GNUNET_PQ_query_param_end-terminated) + * @param rh function to call with the result set, NULL to ignore + * @param rh_cls closure to pass to @a rh + * @return status code from the result, mapping PQ status + * codes to `enum GNUNET_PQ_QueryStatus`. + */ +enum GNUNET_PQ_QueryStatus +GNUNET_PQ_eval_prepared_multi_select (PGconn *connection, + const char *statement_name, + const struct GNUNET_PQ_QueryParam *params, + GNUNET_PQ_PostgresResultHandler rh, + void *rh_cls); + + +/** + * Execute a named prepared @a statement that is a SELECT statement + * which must return a single result in @a connection using the given + * @a params. Stores the result (if any) in @a rs, which the caller + * must then clean up using #GNUNET_PQ_cleanup_result() if the return + * value was #GNUNET_PQ_STATUS_SUCCESS_ONE_RESULT. Returns the + * resulting session status. + * + * @param connection connection to execute the statement in + * @param statement_name name of the statement + * @param params parameters to give to the statement (#GNUNET_PQ_query_param_end-terminated) + * @param[in,out] rs result specification to use for storing the result of the query + * @return status code from the result, mapping PQ status + * codes to `enum GNUNET_PQ_QueryStatus`. + */ +enum GNUNET_PQ_QueryStatus +GNUNET_PQ_eval_prepared_singleton_select (PGconn *connection, + const char *statement_name, + const struct GNUNET_PQ_QueryParam *params, + struct GNUNET_PQ_ResultSpec *rs); + + +/* ******************** pq_prepare.c functions ************** */ + + +/** + * Information needed to prepare a list of SQL statements using + * #GNUNET_PQ_prepare_statements(). + */ +struct GNUNET_PQ_PreparedStatement { + + /** + * Name of the statement. + */ + const char *name; + + /** + * Actual SQL statement. + */ + const char *sql; + + /** + * Number of arguments included in @e sql. + */ + unsigned int num_arguments; + +}; + + +/** + * Terminator for prepared statement list. + */ +#define GNUNET_PQ_PREPARED_STATEMENT_END { NULL, NULL, 0 } + + +/** + * Create a `struct GNUNET_PQ_PreparedStatement`. + * + * @param name name of the statement + * @param sql actual SQL statement + * @param num_args number of arguments in the statement + * @return initialized struct + */ +struct GNUNET_PQ_PreparedStatement +GNUNET_PQ_make_prepare (const char *name, + const char *sql, + unsigned int num_args); + + +/** + * Request creation of prepared statements @a ps from Postgres. + * + * @param connection connection to prepare the statements for + * @param ps #GNUNET_PQ_PREPARED_STATEMENT_END-terminated array of prepared + * statements. + * @return #GNUNET_OK on success, + * #GNUNET_SYSERR on error + */ +int +GNUNET_PQ_prepare_statements (PGconn *connection, + const struct GNUNET_PQ_PreparedStatement *ps); + + +/* ******************** pq_exec.c functions ************** */ + + +/** + * Information needed to run a list of SQL statements using + * #GNUNET_PQ_exec_statements(). + */ +struct GNUNET_PQ_ExecuteStatement { + + /** + * Actual SQL statement. + */ + const char *sql; + + /** + * Should we ignore errors? + */ + int ignore_errors; + +}; + + +/** + * Terminator for executable statement list. + */ +#define GNUNET_PQ_EXECUTE_STATEMENT_END { NULL, GNUNET_SYSERR } + + +/** + * Create a `struct GNUNET_PQ_ExecuteStatement` where errors are fatal. + * + * @param sql actual SQL statement + * @return initialized struct + */ +struct GNUNET_PQ_ExecuteStatement +GNUNET_PQ_make_execute (const char *sql); + + +/** + * Create a `struct GNUNET_PQ_ExecuteStatement` where errors should + * be tolerated. + * + * @param sql actual SQL statement + * @return initialized struct + */ +struct GNUNET_PQ_ExecuteStatement +GNUNET_PQ_make_try_execute (const char *sql); + + +/** + * Request execution of an array of statements @a es from Postgres. + * + * @param connection connection to execute the statements over + * @param es #GNUNET_PQ_PREPARED_STATEMENT_END-terminated array of prepared + * statements. + * @return #GNUNET_OK on success (modulo statements where errors can be ignored) + * #GNUNET_SYSERR on error + */ +int +GNUNET_PQ_exec_statements (PGconn *connection, + const struct GNUNET_PQ_ExecuteStatement *es); + + +/* ******************** pq_connect.c functions ************** */ + + +/** + * Create a connection to the Postgres database using @a config_str + * for the configuration. Initialize logging via GNUnet's log + * routines and disable Postgres's logger. + * + * @param config_str configuration to use + * @return NULL on error + */ +PGconn * +GNUNET_PQ_connect (const char *config_str); + + +/** + * Connect to a postgres database using the configuration + * option "CONFIG" in @a section. + * + * @param cfg configuration + * @param section configuration section to use to get Postgres configuration options + * @return the postgres handle, NULL on error + */ +PGconn * +GNUNET_PQ_connect_with_cfg (const struct GNUNET_CONFIGURATION_Handle *cfg, + const char *section); + + + #endif /* GNUNET_PQ_LIB_H_ */ /* end of include/gnunet_pq_lib.h */ diff --git a/src/namecache/plugin_namecache_postgres.c b/src/namecache/plugin_namecache_postgres.c index bec8bffd2..9c85f4470 100644 --- a/src/namecache/plugin_namecache_postgres.c +++ b/src/namecache/plugin_namecache_postgres.c @@ -1,6 +1,6 @@ /* * This file is part of GNUnet - * Copyright (C) 2009-2013, 2016 GNUnet e.V. + * Copyright (C) 2009-2013, 2016, 2017 GNUnet e.V. * * GNUnet is free software; you can redistribute it and/or modify * it under the terms of the GNU General Public License as published @@ -71,41 +71,35 @@ struct Plugin }; -/** - * Create our database indices. - * - * @param dbh handle to the database - */ -static void -create_indices (PGconn * dbh) -{ - /* create indices */ - if ( (GNUNET_OK != - GNUNET_POSTGRES_exec (dbh, - "CREATE INDEX ir_query_hash ON ns096blocks (query,expiration_time)")) || - (GNUNET_OK != - GNUNET_POSTGRES_exec (dbh, - "CREATE INDEX ir_block_expiration ON ns096blocks (expiration_time)")) ) - LOG (GNUNET_ERROR_TYPE_ERROR, - _("Failed to create indices\n")); -} - - /** * Initialize the database connections and associated * data structures (create tables and indices * as needed as well). * * @param plugin the plugin context (state for this module) - * @return GNUNET_OK on success + * @return #GNUNET_OK on success */ static int database_setup (struct Plugin *plugin) { - PGresult *res; - - plugin->dbh = GNUNET_POSTGRES_connect (plugin->cfg, - "namecache-postgres"); + struct GNUNET_PQ_ExecuteStatement es_temporary = + GNUNET_PQ_make_execute ("CREATE TEMPORARY TABLE IF NOT EXISTS ns096blocks (" + " query BYTEA NOT NULL DEFAULT ''," + " block BYTEA NOT NULL DEFAULT ''," + " expiration_time BIGINT NOT NULL DEFAULT 0" + ")" + "WITH OIDS"); + struct GNUNET_PQ_ExecuteStatement es_default = + GNUNET_PQ_make_execute ("CREATE TABLE IF NOT EXISTS ns096blocks (" + " query BYTEA NOT NULL DEFAULT ''," + " block BYTEA NOT NULL DEFAULT ''," + " expiration_time BIGINT NOT NULL DEFAULT 0" + ")" + "WITH OIDS"); + const struct GNUNET_PQ_ExecuteStatement *cr; + + plugin->dbh = GNUNET_PQ_connect_with_cfg (plugin->cfg, + "namecache-postgres"); if (NULL == plugin->dbh) return GNUNET_SYSERR; if (GNUNET_YES == @@ -113,65 +107,56 @@ database_setup (struct Plugin *plugin) "namecache-postgres", "TEMPORARY_TABLE")) { - res = - PQexec (plugin->dbh, - "CREATE TEMPORARY TABLE ns096blocks (" - " query BYTEA NOT NULL DEFAULT ''," - " block BYTEA NOT NULL DEFAULT ''," - " expiration_time BIGINT NOT NULL DEFAULT 0" - ")" "WITH OIDS"); + cr = &es_temporary; } else { - res = - PQexec (plugin->dbh, - "CREATE TABLE ns096blocks (" - " query BYTEA NOT NULL DEFAULT ''," - " block BYTEA NOT NULL DEFAULT ''," - " expiration_time BIGINT NOT NULL DEFAULT 0" - ")" "WITH OIDS"); + cr = &es_default; } - if ( (NULL == res) || - ((PQresultStatus (res) != PGRES_COMMAND_OK) && - (0 != strcmp ("42P07", /* duplicate table */ - PQresultErrorField - (res, - PG_DIAG_SQLSTATE))))) + { - (void) GNUNET_POSTGRES_check_result (plugin->dbh, res, - PGRES_COMMAND_OK, "CREATE TABLE", - "ns096blocks"); - PQfinish (plugin->dbh); - plugin->dbh = NULL; - return GNUNET_SYSERR; + struct GNUNET_PQ_ExecuteStatement es[] = { + *cr, + GNUNET_PQ_make_try_execute ("CREATE INDEX ir_query_hash ON ns096blocks (query,expiration_time)"), + GNUNET_PQ_make_try_execute ("CREATE INDEX ir_block_expiration ON ns096blocks (expiration_time)"), + GNUNET_PQ_EXECUTE_STATEMENT_END + }; + + if (GNUNET_OK != + GNUNET_PQ_exec_statements (plugin->dbh, + es)) + { + PQfinish (plugin->dbh); + plugin->dbh = NULL; + return GNUNET_SYSERR; + } } - if (PQresultStatus (res) == PGRES_COMMAND_OK) - create_indices (plugin->dbh); - PQclear (res); - if ((GNUNET_OK != - GNUNET_POSTGRES_prepare (plugin->dbh, - "cache_block", - "INSERT INTO ns096blocks (query, block, expiration_time) VALUES " - "($1, $2, $3)", 3)) || - (GNUNET_OK != - GNUNET_POSTGRES_prepare (plugin->dbh, - "expire_blocks", - "DELETE FROM ns096blocks WHERE expiration_time<$1", 1)) || - (GNUNET_OK != - GNUNET_POSTGRES_prepare (plugin->dbh, - "delete_block", - "DELETE FROM ns096blocks WHERE query=$1 AND expiration_time<=$2", 2)) || - (GNUNET_OK != - GNUNET_POSTGRES_prepare (plugin->dbh, - "lookup_block", - "SELECT block FROM ns096blocks WHERE query=$1" - " ORDER BY expiration_time DESC LIMIT 1", 1))) { - PQfinish (plugin->dbh); - plugin->dbh = NULL; - return GNUNET_SYSERR; + struct GNUNET_PQ_PreparedStatement ps[] = { + GNUNET_PQ_make_prepare ("cache_block", + "INSERT INTO ns096blocks (query, block, expiration_time) VALUES " + "($1, $2, $3)", 3), + GNUNET_PQ_make_prepare ("expire_blocks", + "DELETE FROM ns096blocks WHERE expiration_time<$1", 1), + GNUNET_PQ_make_prepare ("delete_block", + "DELETE FROM ns096blocks WHERE query=$1 AND expiration_time<=$2", 2), + GNUNET_PQ_make_prepare ("lookup_block", + "SELECT block FROM ns096blocks WHERE query=$1" + " ORDER BY expiration_time DESC LIMIT 1", 1), + GNUNET_PQ_PREPARED_STATEMENT_END + }; + + if (GNUNET_OK != + GNUNET_PQ_prepare_statements (plugin->dbh, + ps)) + { + PQfinish (plugin->dbh); + plugin->dbh = NULL; + return GNUNET_SYSERR; + } } + return GNUNET_OK; } @@ -185,7 +170,7 @@ static void namecache_postgres_expire_blocks (struct Plugin *plugin) { struct GNUNET_TIME_Absolute now = GNUNET_TIME_absolute_get (); - struct GNUNET_PQ_QueryParam params[] = { + struct GNUNET_PQ_QueryParam params[] = { GNUNET_PQ_query_param_absolute_time (&now), GNUNET_PQ_query_param_end }; @@ -217,7 +202,7 @@ delete_old_block (struct Plugin *plugin, const struct GNUNET_HashCode *query, struct GNUNET_TIME_AbsoluteNBO expiration_time) { - struct GNUNET_PQ_QueryParam params[] = { + struct GNUNET_PQ_QueryParam params[] = { GNUNET_PQ_query_param_auto_from_type (query), GNUNET_PQ_query_param_absolute_time_nbo (&expiration_time), GNUNET_PQ_query_param_end @@ -254,7 +239,7 @@ namecache_postgres_cache_block (void *cls, size_t block_size = ntohl (block->purpose.size) + sizeof (struct GNUNET_CRYPTO_EcdsaPublicKey) + sizeof (struct GNUNET_CRYPTO_EcdsaSignature); - struct GNUNET_PQ_QueryParam params[] = { + struct GNUNET_PQ_QueryParam params[] = { GNUNET_PQ_query_param_auto_from_type (&query), GNUNET_PQ_query_param_fixed_size (block, block_size), GNUNET_PQ_query_param_absolute_time_nbo (&block->expiration_time), @@ -271,7 +256,9 @@ namecache_postgres_cache_block (void *cls, GNUNET_break (0); return GNUNET_SYSERR; } - delete_old_block (plugin, &query, block->expiration_time); + delete_old_block (plugin, + &query, + block->expiration_time); res = GNUNET_PQ_exec_prepared (plugin->dbh, "cache_block", @@ -301,10 +288,11 @@ namecache_postgres_cache_block (void *cls, static int namecache_postgres_lookup_block (void *cls, const struct GNUNET_HashCode *query, - GNUNET_NAMECACHE_BlockCallback iter, void *iter_cls) + GNUNET_NAMECACHE_BlockCallback iter, + void *iter_cls) { struct Plugin *plugin = cls; - struct GNUNET_PQ_QueryParam params[] = { + struct GNUNET_PQ_QueryParam params[] = { GNUNET_PQ_query_param_auto_from_type (query), GNUNET_PQ_query_param_end }; diff --git a/src/namestore/plugin_namestore_postgres.c b/src/namestore/plugin_namestore_postgres.c index 01dbf9e61..4bf931c93 100644 --- a/src/namestore/plugin_namestore_postgres.c +++ b/src/namestore/plugin_namestore_postgres.c @@ -1,6 +1,6 @@ /* * This file is part of GNUnet - * Copyright (C) 2009-2013, 2016 GNUnet e.V. + * Copyright (C) 2009-2013, 2016, 2017 GNUnet e.V. * * GNUnet is free software; you can redistribute it and/or modify * it under the terms of the GNU General Public License as published @@ -74,30 +74,6 @@ struct Plugin }; -/** - * Create our database indices. - * - * @param dbh handle to the database - */ -static void -create_indices (PGconn * dbh) -{ - /* create indices */ - if ( (GNUNET_OK != - GNUNET_POSTGRES_exec (dbh, - "CREATE INDEX IF NOT EXISTS ir_pkey_reverse ON ns097records (zone_private_key,pkey)")) || - (GNUNET_OK != - GNUNET_POSTGRES_exec (dbh, - "CREATE INDEX IF NOT EXISTS ir_pkey_iter ON ns097records (zone_private_key,rvalue)")) || - (GNUNET_OK != - GNUNET_POSTGRES_exec (dbh, "CREATE INDEX IF NOT EXISTS it_iter ON ns097records (rvalue)")) || - (GNUNET_OK != - GNUNET_POSTGRES_exec (dbh, "CREATE INDEX IF NOT EXISTS ir_label ON ns097records (label)")) ) - LOG (GNUNET_ERROR_TYPE_ERROR, - _("Failed to create indices\n")); -} - - /** * Initialize the database connections and associated * data structures (create tables and indices @@ -109,10 +85,30 @@ create_indices (PGconn * dbh) static int database_setup (struct Plugin *plugin) { - PGresult *res; - - plugin->dbh = GNUNET_POSTGRES_connect (plugin->cfg, - "namestore-postgres"); + struct GNUNET_PQ_ExecuteStatement es_temporary = + GNUNET_PQ_make_execute ("CREATE TEMPORARY TABLE IF NOT EXISTS ns097records (" + " zone_private_key BYTEA NOT NULL DEFAULT ''," + " pkey BYTEA DEFAULT ''," + " rvalue BYTEA NOT NULL DEFAULT ''," + " record_count INTEGER NOT NULL DEFAULT 0," + " record_data BYTEA NOT NULL DEFAULT ''," + " label TEXT NOT NULL DEFAULT ''" + ")" + "WITH OIDS"); + struct GNUNET_PQ_ExecuteStatement es_default = + GNUNET_PQ_make_execute ("CREATE TABLE IF NOT EXISTS ns097records (" + " zone_private_key BYTEA NOT NULL DEFAULT ''," + " pkey BYTEA DEFAULT ''," + " rvalue BYTEA NOT NULL DEFAULT ''," + " record_count INTEGER NOT NULL DEFAULT 0," + " record_data BYTEA NOT NULL DEFAULT ''," + " label TEXT NOT NULL DEFAULT ''" + ")" + "WITH OIDS"); + const struct GNUNET_PQ_ExecuteStatement *cr; + + plugin->dbh = GNUNET_PQ_connect_with_cfg (plugin->cfg, + "namestore-postgres"); if (NULL == plugin->dbh) return GNUNET_SYSERR; if (GNUNET_YES == @@ -120,80 +116,70 @@ database_setup (struct Plugin *plugin) "namestore-postgres", "TEMPORARY_TABLE")) { - res = - PQexec (plugin->dbh, - "CREATE TEMPORARY TABLE IF NOT EXISTS ns097records (" - " zone_private_key BYTEA NOT NULL DEFAULT ''," - " pkey BYTEA DEFAULT ''," - " rvalue BYTEA NOT NULL DEFAULT ''," - " record_count INTEGER NOT NULL DEFAULT 0," - " record_data BYTEA NOT NULL DEFAULT ''," - " label TEXT NOT NULL DEFAULT ''" - ")" "WITH OIDS"); + cr = &es_temporary; } else { - res = - PQexec (plugin->dbh, - "CREATE TABLE IF NOT EXISTS ns097records (" - " zone_private_key BYTEA NOT NULL DEFAULT ''," - " pkey BYTEA DEFAULT ''," - " rvalue BYTEA NOT NULL DEFAULT ''," - " record_count INTEGER NOT NULL DEFAULT 0," - " record_data BYTEA NOT NULL DEFAULT ''," - " label TEXT NOT NULL DEFAULT ''" - ")" "WITH OIDS"); + cr = &es_default; } - if ( (NULL == res) || - ((PQresultStatus (res) != PGRES_COMMAND_OK) && - (0 != strcmp ("42P07", /* duplicate table */ - PQresultErrorField - (res, - PG_DIAG_SQLSTATE))))) + { - (void) GNUNET_POSTGRES_check_result (plugin->dbh, res, - PGRES_COMMAND_OK, "CREATE TABLE", - "ns097records"); - PQfinish (plugin->dbh); - plugin->dbh = NULL; - return GNUNET_SYSERR; + struct GNUNET_PQ_ExecuteStatement es[] = { + *cr, + GNUNET_PQ_make_try_execute ("CREATE INDEX IF NOT EXISTS ir_pkey_reverse " + "ON ns097records (zone_private_key,pkey)"), + GNUNET_PQ_make_try_execute ("CREATE INDEX IF NOT EXISTS ir_pkey_iter " + "ON ns097records (zone_private_key,rvalue)"), + GNUNET_PQ_make_try_execute ("CREATE INDEX IF NOT EXISTS it_iter " + "ON ns097records (rvalue)"), + GNUNET_PQ_make_try_execute ("CREATE INDEX IF NOT EXISTS ir_label " + "ON ns097records (label)"), + GNUNET_PQ_EXECUTE_STATEMENT_END + }; + + if (GNUNET_OK != + GNUNET_PQ_exec_statements (plugin->dbh, + es)) + { + PQfinish (plugin->dbh); + plugin->dbh = NULL; + return GNUNET_SYSERR; + } } - create_indices (plugin->dbh); - - if ((GNUNET_OK != - GNUNET_POSTGRES_prepare (plugin->dbh, - "store_records", - "INSERT INTO ns097records (zone_private_key, pkey, rvalue, record_count, record_data, label) VALUES " - "($1, $2, $3, $4, $5, $6)", 6)) || - (GNUNET_OK != - GNUNET_POSTGRES_prepare (plugin->dbh, - "delete_records", - "DELETE FROM ns097records WHERE zone_private_key=$1 AND label=$2", 2)) || - (GNUNET_OK != - GNUNET_POSTGRES_prepare (plugin->dbh, - "zone_to_name", - "SELECT record_count,record_data,label FROM ns097records" - " WHERE zone_private_key=$1 AND pkey=$2", 2)) || - (GNUNET_OK != - GNUNET_POSTGRES_prepare (plugin->dbh, - "iterate_zone", - "SELECT record_count,record_data,label FROM ns097records" - " WHERE zone_private_key=$1 ORDER BY rvalue LIMIT 1 OFFSET $2", 2)) || - (GNUNET_OK != - GNUNET_POSTGRES_prepare (plugin->dbh, - "iterate_all_zones", - "SELECT record_count,record_data,label,zone_private_key" - " FROM ns097records ORDER BY rvalue LIMIT 1 OFFSET $1", 1)) || - (GNUNET_OK != - GNUNET_POSTGRES_prepare (plugin->dbh, - "lookup_label", - "SELECT record_count,record_data,label" - " FROM ns097records WHERE zone_private_key=$1 AND label=$2", 2))) + { - PQfinish (plugin->dbh); - plugin->dbh = NULL; - return GNUNET_SYSERR; + struct GNUNET_PQ_PreparedStatement ps[] = { + GNUNET_PQ_make_prepare ("store_records", + "INSERT INTO ns097records (zone_private_key, pkey, rvalue, record_count, record_data, label) VALUES " + "($1, $2, $3, $4, $5, $6)", 6), + GNUNET_PQ_make_prepare ("delete_records", + "DELETE FROM ns097records " + "WHERE zone_private_key=$1 AND label=$2", 2), + GNUNET_PQ_make_prepare ("zone_to_name", + "SELECT record_count,record_data,label FROM ns097records" + " WHERE zone_private_key=$1 AND pkey=$2", 2), + GNUNET_PQ_make_prepare ("iterate_zone", + "SELECT record_count,record_data,label FROM ns097records " + "WHERE zone_private_key=$1 ORDER BY rvalue LIMIT 1 OFFSET $2", 2), + GNUNET_PQ_make_prepare ("iterate_all_zones", + "SELECT record_count,record_data,label,zone_private_key" + " FROM ns097records ORDER BY rvalue LIMIT 1 OFFSET $1", 1), + GNUNET_PQ_make_prepare ("lookup_label", + "SELECT record_count,record_data,label " + "FROM ns097records WHERE zone_private_key=$1 AND label=$2", 2), + GNUNET_PQ_PREPARED_STATEMENT_END + }; + + if (GNUNET_OK != + GNUNET_PQ_prepare_statements (plugin->dbh, + ps)) + { + PQfinish (plugin->dbh); + plugin->dbh = NULL; + return GNUNET_SYSERR; + } } + return GNUNET_OK; } @@ -221,19 +207,19 @@ namestore_postgres_store_records (void *cls, uint64_t rvalue; uint32_t rd_count_nbo = htonl ((uint32_t) rd_count); size_t data_size; - unsigned int i; memset (&pkey, 0, sizeof (pkey)); - for (i=0;i 64 * 65536) { @@ -262,9 +248,10 @@ namestore_postgres_store_records (void *cls, const int paramFormats[] = { 1, 1, 1, 1, 1, 1 }; PGresult *res; - if (data_size != GNUNET_GNSRECORD_records_serialize (rd_count, rd, - data_size, data)) - { + if (data_size != + GNUNET_GNSRECORD_records_serialize (rd_count, rd, + data_size, data)) + { GNUNET_break (0); return GNUNET_SYSERR; } @@ -301,7 +288,8 @@ static int get_record_and_call_iterator (struct Plugin *plugin, PGresult *res, const struct GNUNET_CRYPTO_EcdsaPrivateKey *zone_key, - GNUNET_NAMESTORE_RecordIterator iter, void *iter_cls) + GNUNET_NAMESTORE_RecordIterator iter, + void *iter_cls) { const char *data; size_t data_size; @@ -311,7 +299,9 @@ get_record_and_call_iterator (struct Plugin *plugin, unsigned int cnt; if (GNUNET_OK != - GNUNET_POSTGRES_check_result (plugin->dbh, res, PGRES_TUPLES_OK, + GNUNET_POSTGRES_check_result (plugin->dbh, + res, + PGRES_TUPLES_OK, "PQexecPrepared", "iteration")) { diff --git a/src/postgres/postgres.c b/src/postgres/postgres.c index 14095c5a4..828842d9d 100644 --- a/src/postgres/postgres.c +++ b/src/postgres/postgres.c @@ -160,48 +160,6 @@ GNUNET_POSTGRES_prepare_ (PGconn *dbh, } -/** - * Connect to a postgres database - * - * @param cfg configuration - * @param section configuration section to use to get Postgres configuration options - * @return the postgres handle - */ -PGconn * -GNUNET_POSTGRES_connect (const struct GNUNET_CONFIGURATION_Handle * cfg, - const char *section) -{ - PGconn *dbh; - char *conninfo; - - /* Open database and precompile statements */ - if (GNUNET_OK != - GNUNET_CONFIGURATION_get_value_string (cfg, - section, - "CONFIG", - &conninfo)) - conninfo = NULL; - dbh = PQconnectdb (conninfo == NULL ? "" : conninfo); - - if (NULL != dbh) - { - if (PQstatus (dbh) != CONNECTION_OK) - { - GNUNET_log_from (GNUNET_ERROR_TYPE_ERROR, - "postgres", - _("Unable to connect to Postgres database '%s': %s\n"), - conninfo, - PQerrorMessage (dbh)); - PQfinish (dbh); - dbh = NULL; - } - } - // FIXME: warn about out-of-memory when dbh is NULL? - GNUNET_free_non_null (conninfo); - return dbh; -} - - /** * Delete the row identified by the given rowid (qid * in postgres). diff --git a/src/pq/Makefile.am b/src/pq/Makefile.am index 8bb0a0132..d0c71703b 100644 --- a/src/pq/Makefile.am +++ b/src/pq/Makefile.am @@ -15,6 +15,10 @@ endif libgnunetpq_la_SOURCES = \ pq.c \ + pq_connect.c \ + pq_eval.c \ + pq_exec.c \ + pq_prepare.c \ pq_query_helper.c \ pq_result_helper.c libgnunetpq_la_LIBADD = -lpq \ diff --git a/src/pq/pq_connect.c b/src/pq/pq_connect.c new file mode 100644 index 000000000..99ad06485 --- /dev/null +++ b/src/pq/pq_connect.c @@ -0,0 +1,127 @@ +/* + This file is part of GNUnet + Copyright (C) 2017 GNUnet e.V. + + GNUnet is free software; you can redistribute it and/or modify it under the + terms of the GNU General Public License as published by the Free Software + Foundation; either version 3, or (at your option) any later version. + + GNUnet is distributed in the hope that it will be useful, but WITHOUT ANY + WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR + A PARTICULAR PURPOSE. See the GNU General Public License for more details. + + You should have received a copy of the GNU General Public License along with + GNUnet; see the file COPYING. If not, If not, see +*/ +/** + * @file pq/pq_connect.c + * @brief functions to connect to libpq (PostGres) + * @author Christian Grothoff + */ +#include "platform.h" +#include "gnunet_util_lib.h" +#include "gnunet_pq_lib.h" + + +/** + * Function called by libpq whenever it wants to log something. + * We already log whenever we care, so this function does nothing + * and merely exists to silence the libpq logging. + * + * @param arg the SQL connection that was used + * @param res information about some libpq event + */ +static void +pq_notice_receiver_cb (void *arg, + const PGresult *res) +{ + /* do nothing, intentionally */ +} + + +/** + * Function called by libpq whenever it wants to log something. + * We log those using the Taler logger. + * + * @param arg the SQL connection that was used + * @param message information about some libpq event + */ +static void +pq_notice_processor_cb (void *arg, + const char *message) +{ + GNUNET_log_from (GNUNET_ERROR_TYPE_INFO, + "pq", + "%s", + message); +} + + +/** + * Create a connection to the Postgres database using @a config_str + * for the configuration. Initialize logging via GNUnet's log + * routines and disable Postgres's logger. + * + * @param config_str configuration to use + * @return NULL on error + */ +PGconn * +GNUNET_PQ_connect (const char *config_str) +{ + PGconn *conn; + + conn = PQconnectdb (config_str); + if ( (NULL == conn) || + (CONNECTION_OK != + PQstatus (conn)) ) + { + GNUNET_log_from (GNUNET_ERROR_TYPE_ERROR, + "pq", + "Database connection to '%s' failed: %s\n", + config_str, + (NULL != conn) ? + PQerrorMessage (conn) + : "PQconnectdb returned NULL"); + if (NULL != conn) + PQfinish (conn); + return NULL; + } + PQsetNoticeReceiver (conn, + &pq_notice_receiver_cb, + conn); + PQsetNoticeProcessor (conn, + &pq_notice_processor_cb, + conn); + return conn; +} + + +/** + * Connect to a postgres database using the configuration + * option "CONFIG" in @a section. + * + * @param cfg configuration + * @param section configuration section to use to get Postgres configuration options + * @return the postgres handle, NULL on error + */ +PGconn * +GNUNET_PQ_connect_with_cfg (const struct GNUNET_CONFIGURATION_Handle * cfg, + const char *section) +{ + PGconn *dbh; + char *conninfo; + + /* Open database and precompile statements */ + if (GNUNET_OK != + GNUNET_CONFIGURATION_get_value_string (cfg, + section, + "CONFIG", + &conninfo)) + conninfo = NULL; + dbh = GNUNET_PQ_connect (conninfo == NULL ? "" : conninfo); + GNUNET_free_non_null (conninfo); + return dbh; +} + + +/* end of pq/pq_connect.c */ diff --git a/src/pq/pq_eval.c b/src/pq/pq_eval.c new file mode 100644 index 000000000..39a7a2c98 --- /dev/null +++ b/src/pq/pq_eval.c @@ -0,0 +1,249 @@ +/* + This file is part of GNUnet + Copyright (C) 2017 GNUnet e.V. + + GNUnet is free software; you can redistribute it and/or modify it under the + terms of the GNU General Public License as published by the Free Software + Foundation; either version 3, or (at your option) any later version. + + GNUnet is distributed in the hope that it will be useful, but WITHOUT ANY + WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR + A PARTICULAR PURPOSE. See the GNU General Public License for more details. + + You should have received a copy of the GNU General Public License along with + GNUnet; see the file COPYING. If not, If not, see +*/ +/** + * @file pq/pq_eval.c + * @brief functions to execute SQL statements with arguments and/or results (PostGres) + * @author Christian Grothoff + */ +#include "platform.h" +#include "gnunet_util_lib.h" +#include "gnunet_pq_lib.h" + + +/** + * Error code returned by Postgres for deadlock. + */ +#define PQ_DIAG_SQLSTATE_DEADLOCK "40P01" + +/** + * Error code returned by Postgres for uniqueness violation. + */ +#define PQ_DIAG_SQLSTATE_UNIQUE_VIOLATION "23505" + +/** + * Error code returned by Postgres on serialization failure. + */ +#define PQ_DIAG_SQLSTATE_SERIALIZATION_FAILURE "40001" + + +/** + * Check the @a result's error code to see what happened. + * Also logs errors. + * + * @param connection connection to execute the statement in + * @param statement_name name of the statement that created @a result + * @param result result to check + * @return status code from the result, mapping PQ status + * codes to `enum GNUNET_PQ_QueryStatus`. Never + * returns positive values as this function does + * not look at the result set. + * @deprecated (low level, let's see if we can do with just the high-level functions) + */ +enum GNUNET_PQ_QueryStatus +GNUNET_PQ_eval_result (PGconn *connection, + const char *statement_name, + PGresult *result) +{ + if (PGRES_COMMAND_OK != + PQresultStatus (result)) + { + const char *sqlstate; + + sqlstate = PQresultErrorField (result, + PG_DIAG_SQLSTATE); + if (NULL == sqlstate) + { + /* very unexpected... */ + GNUNET_break (0); + return GNUNET_PQ_STATUS_HARD_ERROR; + } + if ( (0 == strcmp (sqlstate, + PQ_DIAG_SQLSTATE_DEADLOCK)) || + (0 == strcmp (sqlstate, + PQ_DIAG_SQLSTATE_SERIALIZATION_FAILURE)) ) + { + /* These two can be retried and have a fair chance of working + the next time */ + GNUNET_log_from (GNUNET_ERROR_TYPE_INFO, + "pq", + "Query `%s' failed with result: %s/%s/%s/%s/%s\n", + statement_name, + PQresultErrorField (result, + PG_DIAG_MESSAGE_PRIMARY), + PQresultErrorField (result, + PG_DIAG_MESSAGE_DETAIL), + PQresultErrorMessage (result), + PQresStatus (PQresultStatus (result)), + PQerrorMessage (connection)); + return GNUNET_PQ_STATUS_SOFT_ERROR; + } + GNUNET_log_from (GNUNET_ERROR_TYPE_ERROR, + "pq", + "Query `%s' failed with result: %s/%s/%s/%s/%s\n", + statement_name, + PQresultErrorField (result, + PG_DIAG_MESSAGE_PRIMARY), + PQresultErrorField (result, + PG_DIAG_MESSAGE_DETAIL), + PQresultErrorMessage (result), + PQresStatus (PQresultStatus (result)), + PQerrorMessage (connection)); + return GNUNET_PQ_STATUS_HARD_ERROR; + } + return GNUNET_PQ_STATUS_SUCCESS_NO_RESULTS; +} + + +/** + * Execute a named prepared @a statement that is NOT a SELECT + * statement in @a connnection using the given @a params. Returns the + * resulting session state. + * + * @param connection connection to execute the statement in + * @param statement_name name of the statement + * @param params parameters to give to the statement (#GNUNET_PQ_query_param_end-terminated) + * @return status code from the result, mapping PQ status + * codes to `enum GNUNET_PQ_QueryStatus`. Never + * returns positive values as this function does + * not look at the result set. + */ +enum GNUNET_PQ_QueryStatus +GNUNET_PQ_eval_prepared_non_select (PGconn *connection, + const char *statement_name, + const struct GNUNET_PQ_QueryParam *params) +{ + PGresult *result; + enum GNUNET_PQ_QueryStatus qs; + + result = GNUNET_PQ_exec_prepared (connection, + statement_name, + params); + qs = GNUNET_PQ_eval_result (connection, + statement_name, + result); + PQclear (result); + return qs; +} + + +/** + * Execute a named prepared @a statement that is a SELECT statement + * which may return multiple results in @a connection using the given + * @a params. Call @a rh with the results. Returns the query + * status including the number of results given to @a rh (possibly zero). + * @a rh will not have been called if the return value is negative. + * + * @param connection connection to execute the statement in + * @param statement_name name of the statement + * @param params parameters to give to the statement (#GNUNET_PQ_query_param_end-terminated) + * @param rh function to call with the result set, NULL to ignore + * @param rh_cls closure to pass to @a rh + * @return status code from the result, mapping PQ status + * codes to `enum GNUNET_PQ_QueryStatus`. + */ +enum GNUNET_PQ_QueryStatus +GNUNET_PQ_eval_prepared_multi_select (PGconn *connection, + const char *statement_name, + const struct GNUNET_PQ_QueryParam *params, + GNUNET_PQ_PostgresResultHandler rh, + void *rh_cls) +{ + PGresult *result; + enum GNUNET_PQ_QueryStatus qs; + unsigned int ret; + + result = GNUNET_PQ_exec_prepared (connection, + statement_name, + params); + qs = GNUNET_PQ_eval_result (connection, + statement_name, + result); + if (qs < 0) + { + PQclear (result); + return qs; + } + ret = PQntuples (result); + if (NULL != rh) + rh (rh_cls, + result, + ret); + PQclear (result); + return ret; +} + + +/** + * Execute a named prepared @a statement that is a SELECT statement + * which must return a single result in @a connection using the given + * @a params. Stores the result (if any) in @a rs, which the caller + * must then clean up using #GNUNET_PQ_cleanup_result() if the return + * value was #GNUNET_PQ_STATUS_SUCCESS_ONE_RESULT. Returns the + * resulting session status. + * + * @param connection connection to execute the statement in + * @param statement_name name of the statement + * @param params parameters to give to the statement (#GNUNET_PQ_query_param_end-terminated) + * @param[in,out] rs result specification to use for storing the result of the query + * @return status code from the result, mapping PQ status + * codes to `enum GNUNET_PQ_QueryStatus`. + */ +enum GNUNET_PQ_QueryStatus +GNUNET_PQ_eval_prepared_singleton_select (PGconn *connection, + const char *statement_name, + const struct GNUNET_PQ_QueryParam *params, + struct GNUNET_PQ_ResultSpec *rs) +{ + PGresult *result; + enum GNUNET_PQ_QueryStatus qs; + + result = GNUNET_PQ_exec_prepared (connection, + statement_name, + params); + qs = GNUNET_PQ_eval_result (connection, + statement_name, + result); + if (qs < 0) + { + PQclear (result); + return qs; + } + if (0 == PQntuples (result)) + { + PQclear (result); + return GNUNET_PQ_STATUS_SUCCESS_NO_RESULTS; + } + if (1 != PQntuples (result)) + { + /* more than one result, but there must be at most one */ + GNUNET_break (0); + PQclear (result); + return GNUNET_PQ_STATUS_HARD_ERROR; + } + if (GNUNET_OK != + GNUNET_PQ_extract_result (result, + rs, + 0)) + { + PQclear (result); + return GNUNET_PQ_STATUS_HARD_ERROR; + } + PQclear (result); + return GNUNET_PQ_STATUS_SUCCESS_ONE_RESULT; +} + + +/* end of pq/pq_eval.c */ diff --git a/src/pq/pq_exec.c b/src/pq/pq_exec.c new file mode 100644 index 000000000..1e5e4eb76 --- /dev/null +++ b/src/pq/pq_exec.c @@ -0,0 +1,106 @@ +/* + This file is part of GNUnet + Copyright (C) 2017 GNUnet e.V. + + GNUnet is free software; you can redistribute it and/or modify it under the + terms of the GNU General Public License as published by the Free Software + Foundation; either version 3, or (at your option) any later version. + + GNUnet is distributed in the hope that it will be useful, but WITHOUT ANY + WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR + A PARTICULAR PURPOSE. See the GNU General Public License for more details. + + You should have received a copy of the GNU General Public License along with + GNUnet; see the file COPYING. If not, If not, see +*/ +/** + * @file pq/pq_exec.c + * @brief functions to execute plain SQL statements (PostGres) + * @author Christian Grothoff + */ +#include "platform.h" +#include "gnunet_util_lib.h" +#include "gnunet_pq_lib.h" + + +/** + * Create a `struct GNUNET_PQ_ExecuteStatement` where errors are fatal. + * + * @param sql actual SQL statement + * @return initialized struct + */ +struct GNUNET_PQ_ExecuteStatement +GNUNET_PQ_make_execute (const char *sql) +{ + struct GNUNET_PQ_ExecuteStatement es = { + .sql = sql, + .ignore_errors = GNUNET_NO + }; + + return es; +} + + +/** + * Create a `struct GNUNET_PQ_ExecuteStatement` where errors should + * be tolerated. + * + * @param sql actual SQL statement + * @return initialized struct + */ +struct GNUNET_PQ_ExecuteStatement +GNUNET_PQ_make_try_execute (const char *sql) +{ + struct GNUNET_PQ_ExecuteStatement es = { + .sql = sql, + .ignore_errors = GNUNET_YES + }; + + return es; +} + + +/** + * Request execution of an array of statements @a es from Postgres. + * + * @param connection connection to execute the statements over + * @param es #GNUNET_PQ_PREPARED_STATEMENT_END-terminated array of prepared + * statements. + * @return #GNUNET_OK on success (modulo statements where errors can be ignored) + * #GNUNET_SYSERR on error + */ +int +GNUNET_PQ_exec_statements (PGconn *connection, + const struct GNUNET_PQ_ExecuteStatement *es) +{ + for (unsigned int i=0; NULL != es[i].sql; i++) + { + PGresult *result; + + result = PQexec (connection, + es[i].sql); + + if ( (GNUNET_NO == es[i].ignore_errors) && + (PGRES_COMMAND_OK != PQresultStatus (result)) ) + { + GNUNET_log_from (GNUNET_ERROR_TYPE_ERROR, + "pq", + "Failed to execute `%s': %s/%s/%s/%s/%s", + es[i].sql, + PQresultErrorField (result, + PG_DIAG_MESSAGE_PRIMARY), + PQresultErrorField (result, + PG_DIAG_MESSAGE_DETAIL), + PQresultErrorMessage (result), + PQresStatus (PQresultStatus (result)), + PQerrorMessage (connection)); + PQclear (result); + return GNUNET_SYSERR; + } + PQclear (result); + } + return GNUNET_OK; +} + + +/* end of pq/pq_exec.c */ diff --git a/src/pq/pq_prepare.c b/src/pq/pq_prepare.c new file mode 100644 index 000000000..f533cb564 --- /dev/null +++ b/src/pq/pq_prepare.c @@ -0,0 +1,92 @@ +/* + This file is part of GNUnet + Copyright (C) 2017 GNUnet e.V. + + GNUnet is free software; you can redistribute it and/or modify it under the + terms of the GNU General Public License as published by the Free Software + Foundation; either version 3, or (at your option) any later version. + + GNUnet is distributed in the hope that it will be useful, but WITHOUT ANY + WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR + A PARTICULAR PURPOSE. See the GNU General Public License for more details. + + You should have received a copy of the GNU General Public License along with + GNUnet; see the file COPYING. If not, If not, see +*/ +/** + * @file pq/pq_prepare.c + * @brief functions to connect to libpq (PostGres) + * @author Christian Grothoff + */ +#include "platform.h" +#include "gnunet_util_lib.h" +#include "gnunet_pq_lib.h" + + +/** + * Create a `struct GNUNET_PQ_PreparedStatement`. + * + * @param name name of the statement + * @param sql actual SQL statement + * @param num_args number of arguments in the statement + * @return initialized struct + */ +struct GNUNET_PQ_PreparedStatement +GNUNET_PQ_make_prepare (const char *name, + const char *sql, + unsigned int num_args) +{ + struct GNUNET_PQ_PreparedStatement ps = { + .name = name, + .sql = sql, + .num_arguments = num_args + }; + + return ps; +} + + +/** + * Request creation of prepared statements @a ps from Postgres. + * + * @param connection connection to prepare the statements for + * @param ps #GNUNET_PQ_PREPARED_STATEMENT_END-terminated array of prepared + * statements. + * @return #GNUNET_OK on success, + * #GNUNET_SYSERR on error + */ +int +GNUNET_PQ_prepare_statements (PGconn *connection, + const struct GNUNET_PQ_PreparedStatement *ps) +{ + for (unsigned int i=0;NULL != ps[i].name;i++) + { + PGresult *ret; + + GNUNET_log_from (GNUNET_ERROR_TYPE_DEBUG, + "pq", + "Preparing SQL statement `%s' as `%s'\n", + ps[i].sql, + ps[i].name); + ret = PQprepare (connection, + ps[i].name, + ps[i].sql, + ps[i].num_arguments, + NULL); + if (PGRES_COMMAND_OK != PQresultStatus (ret)) + { + GNUNET_log_from (GNUNET_ERROR_TYPE_ERROR | GNUNET_ERROR_TYPE_BULK, + "pq", + _("PQprepare (`%s' as `%s') failed with error: %s\n"), + ps[i].sql, + ps[i].name, + PQerrorMessage (connection)); + PQclear (ret); + return GNUNET_SYSERR; + } + } + return GNUNET_OK; +} + + +/* end of pq/pq_prepare.c */ diff --git a/src/psycstore/plugin_psycstore_postgres.c b/src/psycstore/plugin_psycstore_postgres.c index 273ab4e80..f410e2737 100644 --- a/src/psycstore/plugin_psycstore_postgres.c +++ b/src/psycstore/plugin_psycstore_postgres.c @@ -84,117 +84,96 @@ struct Plugin * as needed as well). * * @param plugin the plugin context (state for this module) - * @return GNUNET_OK on success + * @return #GNUNET_OK on success */ static int database_setup (struct Plugin *plugin) { + struct GNUNET_PQ_ExecuteStatement es[] = { + GNUNET_PQ_make_execute ("CREATE TABLE IF NOT EXISTS channels (\n" + " id SERIAL,\n" + " pub_key BYTEA NOT NULL CHECK (LENGTH(pub_key)=32),\n" + " max_state_message_id BIGINT,\n" + " state_hash_message_id BIGINT,\n" + " PRIMARY KEY(id)\n" + ")" + "WITH OIDS"), + GNUNET_PQ_make_execute ("CREATE UNIQUE INDEX IF NOT EXISTS channel_pub_key_idx \n" + " ON channels (pub_key)"), + GNUNET_PQ_make_execute ("CREATE OR REPLACE FUNCTION get_chan_id(BYTEA) RETURNS INTEGER AS \n" + " 'SELECT id FROM channels WHERE pub_key=$1;' LANGUAGE SQL STABLE \n" + "RETURNS NULL ON NULL INPUT"), + GNUNET_PQ_make_execute ("CREATE TABLE IF NOT EXISTS slaves (\n" + " id SERIAL,\n" + " pub_key BYTEA NOT NULL CHECK (LENGTH(pub_key)=32),\n" + " PRIMARY KEY(id)\n" + ")" + "WITH OIDS"), + GNUNET_PQ_make_execute ("CREATE UNIQUE INDEX IF NOT EXISTS slaves_pub_key_idx \n" + " ON slaves (pub_key)"), + GNUNET_PQ_make_execute ("CREATE OR REPLACE FUNCTION get_slave_id(BYTEA) RETURNS INTEGER AS \n" + " 'SELECT id FROM slaves WHERE pub_key=$1;' LANGUAGE SQL STABLE \n" + "RETURNS NULL ON NULL INPUT"), + GNUNET_PQ_make_execute ("CREATE TABLE IF NOT EXISTS membership (\n" + " channel_id BIGINT NOT NULL REFERENCES channels(id),\n" + " slave_id BIGINT NOT NULL REFERENCES slaves(id),\n" + " did_join INT NOT NULL,\n" + " announced_at BIGINT NOT NULL,\n" + " effective_since BIGINT NOT NULL,\n" + " group_generation BIGINT NOT NULL\n" + ")" + "WITH OIDS"), + GNUNET_PQ_make_execute ("CREATE INDEX IF NOT EXISTS idx_membership_channel_id_slave_id " + "ON membership (channel_id, slave_id)"), + /** @todo messages table: add method_name column */ + GNUNET_PQ_make_execute ("CREATE TABLE IF NOT EXISTS messages (\n" + " channel_id BIGINT NOT NULL REFERENCES channels(id),\n" + " hop_counter INT NOT NULL,\n" + " signature BYTEA CHECK (LENGTH(signature)=64),\n" + " purpose BYTEA CHECK (LENGTH(purpose)=8),\n" + " fragment_id BIGINT NOT NULL,\n" + " fragment_offset BIGINT NOT NULL,\n" + " message_id BIGINT NOT NULL,\n" + " group_generation BIGINT NOT NULL,\n" + " multicast_flags INT NOT NULL,\n" + " psycstore_flags INT NOT NULL,\n" + " data BYTEA,\n" + " PRIMARY KEY (channel_id, fragment_id),\n" + " UNIQUE (channel_id, message_id, fragment_offset)\n" + ")" + "WITH OIDS"), + GNUNET_PQ_make_execute ("CREATE TABLE IF NOT EXISTS state (\n" + " channel_id BIGINT NOT NULL REFERENCES channels(id),\n" + " name TEXT NOT NULL,\n" + " value_current BYTEA,\n" + " value_signed BYTEA,\n" + " PRIMARY KEY (channel_id, name)\n" + ")" + "WITH OIDS"), + GNUNET_PQ_make_execute ("CREATE TABLE IF NOT EXISTS state_sync (\n" + " channel_id BIGINT NOT NULL REFERENCES channels(id),\n" + " name TEXT NOT NULL,\n" + " value BYTEA,\n" + " PRIMARY KEY (channel_id, name)\n" + ")" + "WITH OIDS"), + GNUNET_PQ_EXECUTE_STATEMENT_END + }; + /* Open database and precompile statements */ - plugin->dbh = GNUNET_POSTGRES_connect (plugin->cfg, - "psycstore-postgres"); + plugin->dbh = GNUNET_PQ_connect_with_cfg (plugin->cfg, + "psycstore-postgres"); if (NULL == plugin->dbh) return GNUNET_SYSERR; - - /* Create tables */ - if ((GNUNET_OK != - GNUNET_POSTGRES_exec(plugin->dbh, - "CREATE TABLE IF NOT EXISTS channels (\n" - " id SERIAL,\n" - " pub_key BYTEA NOT NULL CHECK (LENGTH(pub_key)=32),\n" - " max_state_message_id BIGINT,\n" - " state_hash_message_id BIGINT,\n" - " PRIMARY KEY(id)\n" - ")" "WITH OIDS")) || - - (GNUNET_OK != - GNUNET_POSTGRES_exec(plugin->dbh, - "CREATE UNIQUE INDEX IF NOT EXISTS channel_pub_key_idx \n" - " ON channels (pub_key)")) || - - (GNUNET_OK != - GNUNET_POSTGRES_exec(plugin->dbh, - "CREATE OR REPLACE FUNCTION get_chan_id(BYTEA) RETURNS INTEGER AS \n" - " 'SELECT id FROM channels WHERE pub_key=$1;' LANGUAGE SQL STABLE \n" - "RETURNS NULL ON NULL INPUT")) || - - (GNUNET_OK != - GNUNET_POSTGRES_exec(plugin->dbh, - "CREATE TABLE IF NOT EXISTS slaves (\n" - " id SERIAL,\n" - " pub_key BYTEA NOT NULL CHECK (LENGTH(pub_key)=32),\n" - " PRIMARY KEY(id)\n" - ")" "WITH OIDS")) || - - (GNUNET_OK != - GNUNET_POSTGRES_exec(plugin->dbh, - "CREATE UNIQUE INDEX IF NOT EXISTS slaves_pub_key_idx \n" - " ON slaves (pub_key)")) || - - (GNUNET_OK != - GNUNET_POSTGRES_exec(plugin->dbh, - "CREATE OR REPLACE FUNCTION get_slave_id(BYTEA) RETURNS INTEGER AS \n" - " 'SELECT id FROM slaves WHERE pub_key=$1;' LANGUAGE SQL STABLE \n" - "RETURNS NULL ON NULL INPUT")) || - - (GNUNET_OK != - GNUNET_POSTGRES_exec(plugin->dbh, - "CREATE TABLE IF NOT EXISTS membership (\n" - " channel_id BIGINT NOT NULL REFERENCES channels(id),\n" - " slave_id BIGINT NOT NULL REFERENCES slaves(id),\n" - " did_join INT NOT NULL,\n" - " announced_at BIGINT NOT NULL,\n" - " effective_since BIGINT NOT NULL,\n" - " group_generation BIGINT NOT NULL\n" - ")" "WITH OIDS")) || - - (GNUNET_OK != - GNUNET_POSTGRES_exec(plugin->dbh, - "CREATE INDEX IF NOT EXISTS idx_membership_channel_id_slave_id " - "ON membership (channel_id, slave_id)")) || - - /** @todo messages table: add method_name column */ - (GNUNET_OK != - GNUNET_POSTGRES_exec(plugin->dbh, - "CREATE TABLE IF NOT EXISTS messages (\n" - " channel_id BIGINT NOT NULL REFERENCES channels(id),\n" - " hop_counter INT NOT NULL,\n" - " signature BYTEA CHECK (LENGTH(signature)=64),\n" - " purpose BYTEA CHECK (LENGTH(purpose)=8),\n" - " fragment_id BIGINT NOT NULL,\n" - " fragment_offset BIGINT NOT NULL,\n" - " message_id BIGINT NOT NULL,\n" - " group_generation BIGINT NOT NULL,\n" - " multicast_flags INT NOT NULL,\n" - " psycstore_flags INT NOT NULL,\n" - " data BYTEA,\n" - " PRIMARY KEY (channel_id, fragment_id),\n" - " UNIQUE (channel_id, message_id, fragment_offset)\n" - ")" "WITH OIDS")) || - - (GNUNET_OK != - GNUNET_POSTGRES_exec(plugin->dbh, - "CREATE TABLE IF NOT EXISTS state (\n" - " channel_id BIGINT NOT NULL REFERENCES channels(id),\n" - " name TEXT NOT NULL,\n" - " value_current BYTEA,\n" - " value_signed BYTEA,\n" - " PRIMARY KEY (channel_id, name)\n" - ")" "WITH OIDS")) || - (GNUNET_OK != - GNUNET_POSTGRES_exec(plugin->dbh, - "CREATE TABLE IF NOT EXISTS state_sync (\n" - " channel_id BIGINT NOT NULL REFERENCES channels(id),\n" - " name TEXT NOT NULL,\n" - " value BYTEA,\n" - " PRIMARY KEY (channel_id, name)\n" - ")" "WITH OIDS"))) + if (GNUNET_OK != + GNUNET_PQ_exec_statements (plugin->dbh, + es)) { PQfinish (plugin->dbh); plugin->dbh = NULL; return GNUNET_SYSERR; } - /* Prepare statements */ if ((GNUNET_OK != GNUNET_POSTGRES_prepare (plugin->dbh, "transaction_begin", @@ -842,7 +821,6 @@ fragment_row (struct Plugin *plugin, void *purpose = NULL; size_t signature_size; size_t purpose_size; - uint64_t fragment_id; uint64_t fragment_offset; uint64_t message_id; @@ -852,9 +830,7 @@ fragment_row (struct Plugin *plugin, size_t buf_size; int ret = GNUNET_SYSERR; struct GNUNET_MULTICAST_MessageHeader *mp; - uint32_t msg_flags; - struct GNUNET_PQ_ResultSpec results[] = { GNUNET_PQ_result_spec_uint32 ("hop_counter", &hop_counter), GNUNET_PQ_result_spec_variable_size ("signature", &signature, &signature_size), @@ -964,8 +940,6 @@ fragment_get (void *cls, void *cb_cls) { struct Plugin *plugin = cls; - *returned_fragments = 0; - struct GNUNET_PQ_QueryParam params_select[] = { GNUNET_PQ_query_param_auto_from_type (channel_key), GNUNET_PQ_query_param_uint64 (&first_fragment_id), @@ -973,7 +947,12 @@ fragment_get (void *cls, GNUNET_PQ_query_param_end }; - return fragment_select (plugin, "select_fragments", params_select, returned_fragments, cb, cb_cls); + *returned_fragments = 0; + return fragment_select (plugin, + "select_fragments", + params_select, + returned_fragments, + cb, cb_cls); } @@ -1002,7 +981,11 @@ fragment_get_latest (void *cls, GNUNET_PQ_query_param_end }; - return fragment_select (plugin, "select_latest_fragments", params_select, returned_fragments, cb, cb_cls); + return fragment_select (plugin, + "select_latest_fragments", + params_select, + returned_fragments, + cb, cb_cls); } @@ -1024,11 +1007,6 @@ message_get (void *cls, void *cb_cls) { struct Plugin *plugin = cls; - *returned_fragments = 0; - - if (0 == fragment_limit) - fragment_limit = INT64_MAX; - struct GNUNET_PQ_QueryParam params_select[] = { GNUNET_PQ_query_param_auto_from_type (channel_key), GNUNET_PQ_query_param_uint64 (&first_message_id), @@ -1037,7 +1015,14 @@ message_get (void *cls, GNUNET_PQ_query_param_end }; - return fragment_select (plugin, "select_messages", params_select, returned_fragments, cb, cb_cls); + if (0 == fragment_limit) + fragment_limit = INT64_MAX; + *returned_fragments = 0; + return fragment_select (plugin, + "select_messages", + params_select, + returned_fragments, + cb, cb_cls); } @@ -1057,8 +1042,6 @@ message_get_latest (void *cls, void *cb_cls) { struct Plugin *plugin = cls; - *returned_fragments = 0; - struct GNUNET_PQ_QueryParam params_select[] = { GNUNET_PQ_query_param_auto_from_type (channel_key), GNUNET_PQ_query_param_auto_from_type (channel_key), @@ -1066,7 +1049,12 @@ message_get_latest (void *cls, GNUNET_PQ_query_param_end }; - return fragment_select (plugin, "select_latest_messages", params_select, returned_fragments, cb, cb_cls); + *returned_fragments = 0; + return fragment_select (plugin, + "select_latest_messages", + params_select, + returned_fragments, + cb, cb_cls); } @@ -1255,7 +1243,8 @@ state_assign (struct Plugin *plugin, const char *stmt, static int -update_message_id (struct Plugin *plugin, const char *stmt, +update_message_id (struct Plugin *plugin, + const char *stmt, const struct GNUNET_CRYPTO_EddsaPublicKey *channel_key, uint64_t message_id) { -- cgit v1.2.3