6 files changed, 109 insertions, 61 deletions

diff --git a/src/datacache/Makefile.am b/src/datacache/Makefile.am
index 2d112b4ca..14778e0a3 100644
--- a/src/datacache/Makefile.am
+++ b/src/datacache/Makefile.am
@@ -202,4 +202,5 @@ EXTRA_DIST = \
  test_datacache_data_heap.conf \
  perf_datacache_data_heap.conf \
  test_datacache_data_postgres.conf \
- perf_datacache_data_postgres.conf
+ perf_datacache_data_postgres.conf \
+ $(sql_DATA)
diff --git a/src/namecache/Makefile.am b/src/namecache/Makefile.am
index 272f16b77..f283d3f64 100644
--- a/src/namecache/Makefile.am
+++ b/src/namecache/Makefile.am
@@ -10,6 +10,13 @@ libexecdir= $(pkglibdir)/libexec/
 pkgcfg_DATA = \
    namecache.conf
 
+sqldir = $(prefix)/share/gnunet/sql/
+
+sql_DATA = \
+  namecache-0001.sql \
+  namecache-drop.sql
+
+
 if USE_COVERAGE
   AM_CFLAGS = --coverage -O0
   XLIBS = -lgcov
@@ -168,4 +175,5 @@ EXTRA_DIST = \
   test_namecache_api.conf \
   test_plugin_namecache_sqlite.conf \
   test_plugin_namecache_postgres.conf \
-        test_plugin_namecache_flat.conf
+  test_plugin_namecache_flat.conf \
+  $(sql_DATA)
diff --git a/src/namecache/namecache-0001.sql b/src/namecache/namecache-0001.sql
new file mode 100644
index 000000000..8509b078f
--- /dev/null
+++ b/src/namecache/namecache-0001.sql
@@ -0,0 +1,42 @@
+--
+-- 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 <http://www.gnu.org/licenses/>
+--
+
+-- Everything in one big transaction
+BEGIN;
+
+-- Check patch versioning is in place.
+SELECT _v.register_patch('namecache-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 ns096blocks (
+  query BYTEA NOT NULL DEFAULT '',
+  block BYTEA NOT NULL DEFAULT '',
+  expiration_time BIGINT NOT NULL DEFAULT 0);
+
+CREATE INDEX ir_query_hash
+  ON ns096blocks (query,expiration_time);
+
+CREATE INDEX ir_block_expiration
+  ON ns096blocks (expiration_time);
+
+
+COMMIT;
diff --git a/src/namecache/namecache-drop.sql b/src/namecache/namecache-drop.sql
new file mode 100644
index 000000000..197ee78c1
--- /dev/null
+++ b/src/namecache/namecache-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 <http://www.gnu.org/licenses/>
+--
+
+-- Everything in one big transaction
+BEGIN;
+
+
+SELECT _v.unregister_patch('namecache-0001');
+
+DROP SCHEMA namecache CASCADE;
+
+COMMIT;
diff --git a/src/namecache/plugin_namecache_postgres.c b/src/namecache/plugin_namecache_postgres.c
index d6d1730ce..cdbe248b6 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, 2017 GNUnet e.V.
+ * Copyright (C) 2009-2013, 2016, 2017, 2022 GNUnet e.V.
  *
  * GNUnet is free software: you can redistribute it and/or modify it
  * under the terms of the GNU Affero General Public License as published
@@ -56,63 +56,34 @@ struct Plugin
  * @param plugin the plugin context (state for this module)
  * @return #GNUNET_OK on success
  */
-static int
+static enum GNUNET_GenericReturnValue
 database_setup (struct Plugin *plugin)
 {
-  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"
-                            ")");
-  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"
-                            ")");
-  const struct GNUNET_PQ_ExecuteStatement *cr;
-
-  if (GNUNET_YES ==
-      GNUNET_CONFIGURATION_get_value_yesno (plugin->cfg,
+  struct GNUNET_PQ_PreparedStatement ps[] = {
+    GNUNET_PQ_make_prepare ("cache_block",
+                            "INSERT INTO namecache.ns096blocks"
+                            " (query, block, expiration_time)"
+                            " VALUES"
+                            " ($1, $2, $3)"),
+    GNUNET_PQ_make_prepare ("expire_blocks",
+                            "DELETE FROM namecache.ns096blocks"
+                            " WHERE expiration_time<$1"),
+    GNUNET_PQ_make_prepare ("delete_block",
+                            "DELETE FROM namecache.ns096blocks"
+                            " WHERE query=$1 AND expiration_time<=$2"),
+    GNUNET_PQ_make_prepare ("lookup_block",
+                            "SELECT block"
+                            " FROM namecache.ns096blocks"
+                            " WHERE query=$1"
+                            " ORDER BY expiration_time DESC LIMIT 1"),
+    GNUNET_PQ_PREPARED_STATEMENT_END
+  };
+
+  plugin->dbh = GNUNET_PQ_connect_with_cfg (plugin->cfg,
                                             "namecache-postgres",
-                                            "TEMPORARY_TABLE"))
-  {
-    cr = &es_temporary;
-  }
-  else
-  {
-    cr = &es_default;
-  }
-  {
-    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
-    };
-    struct GNUNET_PQ_PreparedStatement ps[] = {
-      GNUNET_PQ_make_prepare ("cache_block",
-                              "INSERT INTO ns096blocks (query, block, expiration_time) VALUES "
-                              "($1, $2, $3)"),
-      GNUNET_PQ_make_prepare ("expire_blocks",
-                              "DELETE FROM ns096blocks WHERE expiration_time<$1"),
-      GNUNET_PQ_make_prepare ("delete_block",
-                              "DELETE FROM ns096blocks WHERE query=$1 AND expiration_time<=$2"),
-      GNUNET_PQ_make_prepare ("lookup_block",
-                              "SELECT block FROM ns096blocks WHERE query=$1"
-                              " ORDER BY expiration_time DESC LIMIT 1"),
-      GNUNET_PQ_PREPARED_STATEMENT_END
-    };
-
-    plugin->dbh = GNUNET_PQ_connect_with_cfg (plugin->cfg,
-                                              "namecache-postgres",
-                                              NULL,
-                                              es,
-                                              ps);
-  }
+                                            "namecache-",
+                                            NULL,
+                                            ps);
   if (NULL == plugin->dbh)
     return GNUNET_SYSERR;
   return GNUNET_OK;
@@ -174,7 +145,7 @@ delete_old_block (struct Plugin *plugin,
  * @param block block to cache
  * @return #GNUNET_OK on success, else #GNUNET_SYSERR
  */
-static int
+static enum GNUNET_GenericReturnValue
 namecache_postgres_cache_block (void *cls,
                                 const struct GNUNET_GNSRECORD_Block *block)
 {
@@ -222,7 +193,7 @@ namecache_postgres_cache_block (void *cls,
  * @param iter_cls closure for @a iter
  * @return #GNUNET_OK on success, #GNUNET_NO if there were no results, #GNUNET_SYSERR on error
  */
-static int
+static enum GNUNET_GenericReturnValue
 namecache_postgres_lookup_block (void *cls,
                                  const struct GNUNET_HashCode *query,
                                  GNUNET_NAMECACHE_BlockCallback iter,
diff --git a/src/namestore/Makefile.am b/src/namestore/Makefile.am
index b4d2b0ff2..98ee228e1 100644
--- a/src/namestore/Makefile.am
+++ b/src/namestore/Makefile.am
@@ -538,5 +538,6 @@ EXTRA_DIST = \
   test_plugin_namestore_sqlite.conf \
   test_plugin_namestore_postgres.conf \
   test_hostkey \
-        example_zonefile \
-  $(check_SCRIPTS)
+  example_zonefile \
+  $(check_SCRIPTS) \
+  $(sql_DATA)