From 49649b2ac771f8e01c3439a8d9d1567b1a5e4fc8 Mon Sep 17 00:00:00 2001 From: Christian Grothoff Date: Fri, 10 Jun 2011 15:39:28 +0000 Subject: [PATCH] fixing datastore schema for future change for improved performance --- src/datastore/plugin_datastore_mysql.c | 8 ++++++-- src/datastore/plugin_datastore_postgres.c | 24 +++++++++++++++-------- src/datastore/plugin_datastore_sqlite.c | 16 +++++++++------ 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, -- 2.25.1