fixing datastore schema for future change for improved performance

3 files changed, 32 insertions, 16 deletions

diff --git a/src/datastore/plugin_datastore_mysql.c b/src/datastore/plugin_datastore_mysql.c
index ec263a035..db547cb30 100644
--- a/src/datastore/plugin_datastore_mysql.c
+++ b/src/datastore/plugin_datastore_mysql.c
@@ -194,7 +194,7 @@ struct Plugin
   /**
    * Prepared statements.
    */
-#define INSERT_ENTRY "INSERT INTO gn090 (repl,type,prio,anonLevel,expire,hash,vhash,value) VALUES (?,?,?,?,?,?,?,?)"
+#define INSERT_ENTRY "INSERT INTO gn090 (repl,type,prio,anonLevel,expire,rvalue,hash,vhash,value) VALUES (?,?,?,?,?,?,?,?,?)"
   struct GNUNET_MysqlStatementHandle *insert_entry;
   
 #define DELETE_ENTRY_BY_UID "DELETE FROM gn090 WHERE uid=?"
@@ -860,6 +860,7 @@ mysql_plugin_put (void *cls,
   unsigned long hashSize;
   unsigned long hashSize2;
   unsigned long lsize;
+  unsigned long rvalue;
   GNUNET_HashCode vhash;
 
   if (size > MAX_DATUM_SIZE)
@@ -871,6 +872,7 @@ mysql_plugin_put (void *cls,
   hashSize2 = sizeof (GNUNET_HashCode);
   lsize = size;
   GNUNET_CRYPTO_hash (data, size, &vhash);
+  rvalue = (unsigned long) GNUNET_CRYPTO_random_u64 (GNUNET_CRYPTO_QUALITY_WEAK, UINT64_MAX);
   if (GNUNET_OK !=
       prepared_statement_run (plugin,
                               plugin->insert_entry,
@@ -880,6 +882,7 @@ mysql_plugin_put (void *cls,
                               MYSQL_TYPE_LONG, &ipriority, GNUNET_YES,
                               MYSQL_TYPE_LONG, &ianonymity, GNUNET_YES,
                               MYSQL_TYPE_LONGLONG, &lexpiration, GNUNET_YES,
+                              MYSQL_TYPE_LONGLONG, &rvalue, GNUNET_YES,
                               MYSQL_TYPE_BLOB, key, hashSize, &hashSize,
                               MYSQL_TYPE_BLOB, &vhash, hashSize2, &hashSize2,
                               MYSQL_TYPE_BLOB, data, lsize, &lsize, 
@@ -1441,6 +1444,7 @@ libgnunet_plugin_datastore_mysql_init (void *cls)
              " prio INT(11) UNSIGNED NOT NULL DEFAULT 0,"
              " anonLevel INT(11) UNSIGNED NOT NULL DEFAULT 0,"
              " expire BIGINT UNSIGNED NOT NULL DEFAULT 0,"
+             " rvalue BIGINT UNSIGNED NOT NULL,"
              " hash BINARY(64) NOT NULL DEFAULT '',"
              " vhash BINARY(64) NOT NULL DEFAULT '',"
              " value BLOB NOT NULL DEFAULT '',"
@@ -1451,7 +1455,7 @@ libgnunet_plugin_datastore_mysql_init (void *cls)
              " INDEX idx_hash_vhash (hash(64),vhash(64)),"
              " INDEX idx_hash_type_uid (hash(64),type,uid),"
              " INDEX idx_prio (prio),"
-             " INDEX idx_repl (repl),"
+             " INDEX idx_repl_rvalue (repl,rvalue),"
              " INDEX idx_expire_prio (expire,prio),"
              " INDEX idx_anonLevel_uid (anonLevel,uid)"
              ") ENGINE=InnoDB") ||
diff --git a/src/datastore/plugin_datastore_postgres.c b/src/datastore/plugin_datastore_postgres.c
index 30a6c20da..9d60100cf 100644
--- a/src/datastore/plugin_datastore_postgres.c
+++ b/src/datastore/plugin_datastore_postgres.c
@@ -190,6 +190,7 @@ init_connection (struct Plugin *plugin)
                 "  prio INTEGER NOT NULL DEFAULT 0,"
                 "  anonLevel INTEGER NOT NULL DEFAULT 0,"
                 "  expire BIGINT NOT NULL DEFAULT 0,"
+                "  rvalue BIGINT NOT NULL DEFAULT 0,"
                 "  hash BYTEA NOT NULL DEFAULT '',"
                 "  vhash BYTEA NOT NULL DEFAULT '',"
                 "  value BYTEA NOT NULL DEFAULT '')" "WITH OIDS");
@@ -218,14 +219,18 @@ init_connection (struct Plugin *plugin)
           || (GNUNET_OK !=
               pq_exec (plugin, "CREATE INDEX idx_expire ON gn090 (expire)", __LINE__))
           || (GNUNET_OK !=
-              pq_exec (plugin, "CREATE INDEX idx_comb3 ON gn090 (prio,anonLevel)",
+              pq_exec (plugin, "CREATE INDEX idx_prio_anon ON gn090 (prio,anonLevel)",
                        __LINE__))
           || (GNUNET_OK !=
               pq_exec
-              (plugin, "CREATE INDEX idx_comb4 ON gn090 (prio,hash,anonLevel)",
+              (plugin, "CREATE INDEX idx_prio_hash_anon ON gn090 (prio,hash,anonLevel)",
                __LINE__))
           || (GNUNET_OK !=
-              pq_exec (plugin, "CREATE INDEX idx_comb7 ON gn090 (expire,hash)",
+              pq_exec
+              (plugin, "CREATE INDEX idx_repl_rvalue ON gn090 (repl,rvalue)",
+               __LINE__))
+          || (GNUNET_OK !=
+              pq_exec (plugin, "CREATE INDEX idx_expire_hash ON gn090 (expire,hash)",
                        __LINE__)))
         {
           PQclear (ret);
@@ -305,9 +310,9 @@ init_connection (struct Plugin *plugin)
       (GNUNET_OK !=
        pq_prepare (plugin,
                    "put",
-                   "INSERT INTO gn090 (repl, type, prio, anonLevel, expire, hash, vhash, value) "
-                   "VALUES ($1, $2, $3, $4, $5, $6, $7, $8)",
-                   8,
+                   "INSERT INTO gn090 (repl, type, prio, anonLevel, expire, rvalue, hash, vhash, value) "
+                   "VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9)",
+                   9,
                    __LINE__)) ||
       (GNUNET_OK !=
        pq_prepare (plugin,
@@ -472,12 +477,14 @@ postgres_plugin_put (void *cls,
   uint32_t banon = htonl (anonymity);
   uint32_t brepl = htonl (replication);
   uint64_t bexpi = GNUNET_TIME_absolute_hton (expiration).abs_value__;
+  uint64_t rvalue = GNUNET_CRYPTO_random_u64 (GNUNET_CRYPTO_QUALITY_WEAK, UINT64_MAX);
   const char *paramValues[] = {
     (const char *) &brepl,
     (const char *) &btype,
     (const char *) &bprio,
     (const char *) &banon,
     (const char *) &bexpi,
+    (const char *) &rvalue,
     (const char *) key,
     (const char *) &vhash,
     (const char *) data
@@ -488,15 +495,16 @@ postgres_plugin_put (void *cls,
     sizeof (bprio),
     sizeof (banon),
     sizeof (bexpi),
+    sizeof (rvalue),
     sizeof (GNUNET_HashCode),
     sizeof (GNUNET_HashCode),
     size
   };
-  const int paramFormats[] = { 1, 1, 1, 1, 1, 1, 1, 1 };
+  const int paramFormats[] = { 1, 1, 1, 1, 1, 1, 1, 1, 1 };
 
   GNUNET_CRYPTO_hash (data, size, &vhash);
   ret = PQexecPrepared (plugin->dbh,
-                        "put", 8, paramValues, paramLengths, paramFormats, 1);
+                        "put", 9, paramValues, paramLengths, paramFormats, 1);
   if (GNUNET_OK != check_result (plugin, ret,
                                  PGRES_COMMAND_OK,
                                  "PQexecPrepared", "put", __LINE__))
diff --git a/src/datastore/plugin_datastore_sqlite.c b/src/datastore/plugin_datastore_sqlite.c
index 3267869d5..613b6a1a7 100644
--- a/src/datastore/plugin_datastore_sqlite.c
+++ b/src/datastore/plugin_datastore_sqlite.c
@@ -178,7 +178,7 @@ create_indices (sqlite3 * dbh)
                 NULL, NULL, NULL);
   sqlite3_exec (dbh, "CREATE INDEX idx_expire ON gn090 (expire)",
                 NULL, NULL, NULL);
-  sqlite3_exec (dbh, "CREATE INDEX idx_repl ON gn090 (repl)",
+  sqlite3_exec (dbh, "CREATE INDEX idx_repl_rvalue ON gn090 (repl,rvalue)",
                 NULL, NULL, NULL);
 }
 
@@ -292,6 +292,7 @@ database_setup (const struct GNUNET_CONFIGURATION_Handle *cfg,
                       "  prio INT4 NOT NULL DEFAULT 0,"
                       "  anonLevel INT4 NOT NULL DEFAULT 0,"
                       "  expire INT8 NOT NULL DEFAULT 0,"
+                      "  rvalue INT8 NOT NULL,"
                       "  hash TEXT NOT NULL DEFAULT '',"
                       "  vhash TEXT NOT NULL DEFAULT '',"
                       "  value BLOB NOT NULL DEFAULT '')", NULL, NULL,
@@ -328,8 +329,8 @@ database_setup (const struct GNUNET_CONFIGURATION_Handle *cfg,
                    &plugin->selZeroAnon) != SQLITE_OK) ||
       (sq_prepare (plugin->dbh,
                    "INSERT INTO gn090 (repl, type, prio, "
-                   "anonLevel, expire, hash, vhash, value) "
-                   "VALUES (?, ?, ?, ?, ?, ?, ?, ?)",
+                   "anonLevel, expire, rvalue, hash, vhash, value) "
+                   "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)",
                    &plugin->insertContent) != SQLITE_OK) ||
       (sq_prepare (plugin->dbh,
                    "DELETE FROM gn090 WHERE _ROWID_ = ?",
@@ -469,6 +470,7 @@ sqlite_plugin_put (void *cls,
   int ret;
   sqlite3_stmt *stmt;
   GNUNET_HashCode vhash;
+  uint64_t rvalue;
 
   if (size > MAX_ITEM_SIZE)
     return GNUNET_SYSERR;
@@ -484,19 +486,21 @@ sqlite_plugin_put (void *cls,
 #endif
   GNUNET_CRYPTO_hash (data, size, &vhash);
   stmt = plugin->insertContent;
+  rvalue = GNUNET_CRYPTO_random_u64 (GNUNET_CRYPTO_QUALITY_WEAK, UINT64_MAX);
   if ((SQLITE_OK != sqlite3_bind_int (stmt, 1, replication)) ||
       (SQLITE_OK != sqlite3_bind_int (stmt, 2, type)) ||
       (SQLITE_OK != sqlite3_bind_int (stmt, 3, priority)) ||
       (SQLITE_OK != sqlite3_bind_int (stmt, 4, anonymity)) ||
       (SQLITE_OK != sqlite3_bind_int64 (stmt, 5, expiration.abs_value)) ||
+      (SQLITE_OK != sqlite3_bind_int64 (stmt, 6, rvalue)) ||
       (SQLITE_OK !=
-       sqlite3_bind_blob (stmt, 6, key, sizeof (GNUNET_HashCode),
+       sqlite3_bind_blob (stmt, 7, key, sizeof (GNUNET_HashCode),
                           SQLITE_TRANSIENT)) ||
       (SQLITE_OK !=
-       sqlite3_bind_blob (stmt, 7, &vhash, sizeof (GNUNET_HashCode),
+       sqlite3_bind_blob (stmt, 8, &vhash, sizeof (GNUNET_HashCode),
                           SQLITE_TRANSIENT))
       || (SQLITE_OK !=
-          sqlite3_bind_blob (stmt, 8, data, size,
+          sqlite3_bind_blob (stmt, 9, data, size,
                              SQLITE_TRANSIENT)))
     {
       LOG_SQLITE (plugin,