From 7299f45d97d1dcc22d8155cfeb95939be6422bf4 Mon Sep 17 00:00:00 2001 From: Christian Grothoff Date: Tue, 22 Nov 2022 15:15:53 +0100 Subject: modernize datacache postgres implementation --- src/datacache/Makefile.am | 7 +++ src/datacache/datacache-0001.sql | 48 +++++++++++++++++ src/datacache/datacache-drop.sql | 25 +++++++++ src/datacache/datacache.conf | 4 ++ src/datacache/plugin_datacache_postgres.c | 85 ++++++++++++++----------------- 5 files changed, 121 insertions(+), 48 deletions(-) create mode 100644 src/datacache/datacache-0001.sql create mode 100644 src/datacache/datacache-drop.sql (limited to 'src/datacache') diff --git a/src/datacache/Makefile.am b/src/datacache/Makefile.am index 4789706ff..2d112b4ca 100644 --- a/src/datacache/Makefile.am +++ b/src/datacache/Makefile.am @@ -8,6 +8,13 @@ pkgcfgdir= $(pkgdatadir)/config.d/ dist_pkgcfg_DATA = \ datacache.conf +sqldir = $(prefix)/share/gnunet/sql/ + +sql_DATA = \ + datacache-0001.sql \ + datacache-drop.sql + + if USE_COVERAGE AM_CFLAGS = --coverage -O0 XLIBS = -lgcov diff --git a/src/datacache/datacache-0001.sql b/src/datacache/datacache-0001.sql new file mode 100644 index 000000000..6567de3c2 --- /dev/null +++ b/src/datacache/datacache-0001.sql @@ -0,0 +1,48 @@ +-- +-- This file is part of GNUnet +-- Copyright (C) 2014--2022 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, see +-- + +-- Everything in one big transaction +BEGIN; + +-- Check patch versioning is in place. +SELECT _v.register_patch('datacache-0001', NULL, NULL); + +-------------------- Schema ---------------------------- + +CREATE SCHEMA datacache; +COMMENT ON SCHEMA datacache IS 'gnunet-datacache data'; + +SET search_path TO datacache; + +CREATE TABLE IF NOT EXISTS gn180dc ( + oid BIGINT GENERATED BY DEFAULT AS IDENTITY, + type INT4 NOT NULL, + ro INT4 NOT NULL, + prox INT4 NOT NULL, + expiration_time INT8 NOT NULL, + key BYTEA NOT NULL CHECK(LENGTH(key)=64), + trunc BYTEA NOT NULL CHECK(LENGTH(trunc)=32), + value BYTEA NOT NULL, + path BYTEA DEFAULT NULL); + +CREATE INDEX IF NOT EXISTS idx_oid + ON gn180dc (oid); +CREATE INDEX IF NOT EXISTS idx_key + ON gn180dc (key); +CREATE INDEX IF NOT EXISTS idx_dt + ON gn180dc (expiration_time); + +COMMIT; diff --git a/src/datacache/datacache-drop.sql b/src/datacache/datacache-drop.sql new file mode 100644 index 000000000..2dd84bca8 --- /dev/null +++ b/src/datacache/datacache-drop.sql @@ -0,0 +1,25 @@ +-- +-- This file is part of GNUnet +-- Copyright (C) 2014--2022 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, see +-- + +-- Everything in one big transaction +BEGIN; + + +SELECT _v.unregister_patch('datacache-0001'); + +DROP SCHEMA datacache CASCADE; + +COMMIT; diff --git a/src/datacache/datacache.conf b/src/datacache/datacache.conf index f9c718eb0..ed5c3da06 100644 --- a/src/datacache/datacache.conf +++ b/src/datacache/datacache.conf @@ -1,2 +1,6 @@ [datacache-postgres] CONFIG = postgres:///gnunet + +# Where are the SQL files to setup our tables? +# Important: this MUST end with a "/"! +SQL_DIR = $DATADIR/sql/ \ No newline at end of file diff --git a/src/datacache/plugin_datacache_postgres.c b/src/datacache/plugin_datacache_postgres.c index df5fc4454..8bfd04aea 100644 --- a/src/datacache/plugin_datacache_postgres.c +++ b/src/datacache/plugin_datacache_postgres.c @@ -66,64 +66,45 @@ struct Plugin static enum GNUNET_GenericReturnValue init_connection (struct Plugin *plugin) { - struct GNUNET_PQ_ExecuteStatement es[] = { - GNUNET_PQ_make_try_execute ( - "CREATE TEMPORARY SEQUENCE IF NOT EXISTS gn180dc_oid_seq"), - GNUNET_PQ_make_execute ("CREATE TEMPORARY TABLE IF NOT EXISTS gn180dc (" - " oid OID NOT NULL DEFAULT nextval('gn180dc_oid_seq')," - " type INT4 NOT NULL," - " ro INT4 NOT NULL," - " prox INT4 NOT NULL," - " expiration_time INT8 NOT NULL," - " key BYTEA NOT NULL CHECK(LENGTH(key)=64)," - " trunc BYTEA NOT NULL CHECK(LENGTH(trunc)=32)," - " value BYTEA NOT NULL," - " path BYTEA DEFAULT NULL)"), - GNUNET_PQ_make_try_execute ( - "ALTER SEQUENCE gnu011dc_oid_seq OWNED BY gn180dc.oid"), - GNUNET_PQ_make_try_execute ( - "CREATE INDEX IF NOT EXISTS idx_oid ON gn180dc (oid)"), - GNUNET_PQ_make_try_execute ( - "CREATE INDEX IF NOT EXISTS idx_key ON gn180dc (key)"), - GNUNET_PQ_make_try_execute ( - "CREATE INDEX IF NOT EXISTS idx_dt ON gn180dc (expiration_time)"), - GNUNET_PQ_make_execute ( - "ALTER TABLE gn180dc ALTER value SET STORAGE EXTERNAL"), - GNUNET_PQ_make_execute ("ALTER TABLE gn180dc ALTER key SET STORAGE PLAIN"), - GNUNET_PQ_EXECUTE_STATEMENT_END - }; struct GNUNET_PQ_PreparedStatement ps[] = { GNUNET_PQ_make_prepare ("getkt", - "SELECT expiration_time,type,ro,value,trunc,path FROM gn180dc " - "WHERE key=$1 AND type=$2 AND expiration_time >= $3"), + "SELECT expiration_time,type,ro,value,trunc,path" + " FROM datacache.gn180dc" + " WHERE key=$1 AND type=$2 AND expiration_time >= $3"), GNUNET_PQ_make_prepare ("getk", - "SELECT expiration_time,type,ro,value,trunc,path FROM gn180dc " - "WHERE key=$1 AND expiration_time >= $2"), + "SELECT expiration_time,type,ro,value,trunc,path" + " FROM datacache.gn180dc" + " WHERE key=$1 AND expiration_time >= $2"), GNUNET_PQ_make_prepare ("getex", - "SELECT LENGTH(value) AS len,oid,key FROM gn180dc" + "SELECT LENGTH(value) AS len,oid,key" + " FROM datacache.gn180dc" " WHERE expiration_time < $1" " ORDER BY expiration_time ASC LIMIT 1"), GNUNET_PQ_make_prepare ("getm", - "SELECT LENGTH(value) AS len,oid,key FROM gn180dc" + "SELECT LENGTH(value) AS len,oid,key" + " FROM datacache.gn180dc" " ORDER BY prox ASC, expiration_time ASC LIMIT 1"), GNUNET_PQ_make_prepare ("get_closest", - "(SELECT expiration_time,type,ro,value,trunc,path,key FROM gn180dc" + "(SELECT expiration_time,type,ro,value,trunc,path,key" + " FROM datacache.gn180dc" " WHERE key >= $1" " AND expiration_time >= $2" " AND ( (type = $3) OR ( 0 = $3) )" " ORDER BY key ASC" " LIMIT $4)" " UNION " - "(SELECT expiration_time,type,ro,value,trunc,path,key FROM gn180dc" + "(SELECT expiration_time,type,ro,value,trunc,path,key" + " FROM datacache.gn180dc" " WHERE key <= $1" " AND expiration_time >= $2" " AND ( (type = $3) OR ( 0 = $3) )" " ORDER BY key DESC" " LIMIT $4)"), GNUNET_PQ_make_prepare ("delrow", - "DELETE FROM gn180dc WHERE oid=$1"), + "DELETE FROM datacache.gn180dc" + " WHERE oid=$1"), GNUNET_PQ_make_prepare ("put", - "INSERT INTO gn180dc" + "INSERT INTO datacache.gn180dc" " (type, ro, prox, expiration_time, key, value, trunc, path) " "VALUES ($1, $2, $3, $4, $5, $6, $7, $8)"), GNUNET_PQ_PREPARED_STATEMENT_END @@ -131,8 +112,8 @@ init_connection (struct Plugin *plugin) plugin->dbh = GNUNET_PQ_connect_with_cfg (plugin->env->cfg, "datacache-postgres", + "datacache-", NULL, - es, ps); if (NULL == plugin->dbh) return GNUNET_SYSERR; @@ -165,9 +146,12 @@ postgres_plugin_put (void *cls, GNUNET_PQ_query_param_fixed_size (block->data, block->data_size), GNUNET_PQ_query_param_auto_from_type (&block->trunc_peer), - GNUNET_PQ_query_param_fixed_size (block->put_path, - block->put_path_length - * sizeof(struct GNUNET_DHT_PathElement)), + (0 == block->put_path_length) + ? GNUNET_PQ_query_param_null () + : GNUNET_PQ_query_param_fixed_size ( + block->put_path, + block->put_path_length + * sizeof(struct GNUNET_DHT_PathElement)), GNUNET_PQ_query_param_end }; enum GNUNET_DB_QueryStatus ret; @@ -226,8 +210,8 @@ handle_results (void *cls, uint32_t bro32; void *data; struct GNUNET_DATACACHE_Block block; - void *path; - size_t path_size; + void *path = NULL; + size_t path_size = 0; struct GNUNET_PQ_ResultSpec rs[] = { GNUNET_PQ_result_spec_absolute_time ("expiration_time", &block.expiration_time), @@ -240,9 +224,11 @@ handle_results (void *cls, &block.data_size), GNUNET_PQ_result_spec_auto_from_type ("trunc", &block.trunc_peer), - GNUNET_PQ_result_spec_variable_size ("path", - &path, - &path_size), + GNUNET_PQ_result_spec_allow_null ( + GNUNET_PQ_result_spec_variable_size ("path", + &path, + &path_size), + NULL), GNUNET_PQ_result_spec_end }; @@ -351,12 +337,12 @@ postgres_plugin_del (void *cls) GNUNET_PQ_query_param_end }; uint32_t size; - uint32_t oid; + uint64_t oid; struct GNUNET_HashCode key; struct GNUNET_PQ_ResultSpec rs[] = { GNUNET_PQ_result_spec_uint32 ("len", &size), - GNUNET_PQ_result_spec_uint32 ("oid", + GNUNET_PQ_result_spec_uint64 ("oid", &oid), GNUNET_PQ_result_spec_auto_from_type ("key", &key), @@ -364,7 +350,7 @@ postgres_plugin_del (void *cls) }; enum GNUNET_DB_QueryStatus res; struct GNUNET_PQ_QueryParam dparam[] = { - GNUNET_PQ_query_param_uint32 (&oid), + GNUNET_PQ_query_param_uint64 (&oid), GNUNET_PQ_query_param_end }; struct GNUNET_TIME_Absolute now; @@ -617,6 +603,9 @@ libgnunet_plugin_datacache_postgres_done (void *cls) struct GNUNET_DATACACHE_PluginFunctions *api = cls; struct Plugin *plugin = api->cls; + GNUNET_break (GNUNET_OK == + GNUNET_PQ_exec_sql (plugin->dbh, + "datacache-drop")); GNUNET_PQ_disconnect (plugin->dbh); GNUNET_free (plugin); GNUNET_free (api); -- cgit v1.2.3