/**
* 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
*/
static int
database_setup (struct Plugin *plugin)
{
PGresult *res;
plugin->dbh = GNUNET_POSTGRES_connect (plugin->cfg,
"namecache-postgres");
if (NULL == plugin->dbh)
return GNUNET_SYSERR;
if (GNUNET_YES ==
GNUNET_CONFIGURATION_get_value_yesno (plugin->cfg,
"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");
}
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");
}
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;
}
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;
}
return GNUNET_OK;
}
/**
* @brief Get a database handle
*
* @param plugin global context
* @return #GNUNET_OK on success, #GNUNET_SYSERR on error
*/
static int
init_connection (struct Plugin *plugin)
{
PGresult *ret;
plugin->dbh = GNUNET_POSTGRES_connect (plugin->env->cfg, "datastore-postgres");
if (NULL == plugin->dbh)
return GNUNET_SYSERR;
ret =
PQexec (plugin->dbh,
"CREATE TABLE gn090 ("
" repl INTEGER NOT NULL DEFAULT 0,"
" type INTEGER NOT NULL DEFAULT 0,"
" 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");
if ( (NULL == ret) ||
((PQresultStatus (ret) != PGRES_COMMAND_OK) &&
(0 != strcmp ("42P07", /* duplicate table */
PQresultErrorField
(ret,
PG_DIAG_SQLSTATE)))))
{
(void) GNUNET_POSTGRES_check_result (plugin->dbh,
ret,
PGRES_COMMAND_OK,
"CREATE TABLE",
"gn090");
PQfinish (plugin->dbh);
plugin->dbh = NULL;
return GNUNET_SYSERR;
}
if (PQresultStatus (ret) == PGRES_COMMAND_OK)
{
if ((GNUNET_OK !=
GNUNET_POSTGRES_exec (plugin->dbh, "CREATE INDEX idx_hash ON gn090 (hash)")) ||
(GNUNET_OK !=
GNUNET_POSTGRES_exec (plugin->dbh, "CREATE INDEX idx_hash_vhash ON gn090 (hash,vhash)")) ||
(GNUNET_OK !=
GNUNET_POSTGRES_exec (plugin->dbh, "CREATE INDEX idx_prio ON gn090 (prio)")) ||
(GNUNET_OK !=
GNUNET_POSTGRES_exec (plugin->dbh, "CREATE INDEX idx_expire ON gn090 (expire)")) ||
(GNUNET_OK !=
GNUNET_POSTGRES_exec (plugin->dbh,
"CREATE INDEX idx_prio_anon ON gn090 (prio,anonLevel)")) ||
(GNUNET_OK !=
GNUNET_POSTGRES_exec (plugin->dbh,
"CREATE INDEX idx_prio_hash_anon ON gn090 (prio,hash,anonLevel)")) ||
(GNUNET_OK !=
GNUNET_POSTGRES_exec (plugin->dbh, "CREATE INDEX idx_repl_rvalue ON gn090 (repl,rvalue)")) ||
(GNUNET_OK !=
GNUNET_POSTGRES_exec (plugin->dbh, "CREATE INDEX idx_expire_hash ON gn090 (expire,hash)")))
{
PQclear (ret);
PQfinish (plugin->dbh);
plugin->dbh = NULL;
return GNUNET_SYSERR;
}
}
PQclear (ret);
ret =
PQexec (plugin->dbh,
"ALTER TABLE gn090 ALTER value SET STORAGE EXTERNAL");
if (GNUNET_OK !=
GNUNET_POSTGRES_check_result (plugin->dbh, ret, PGRES_COMMAND_OK, "ALTER TABLE", "gn090"))
{
PQfinish (plugin->dbh);
plugin->dbh = NULL;
return GNUNET_SYSERR;
}
PQclear (ret);
ret = PQexec (plugin->dbh, "ALTER TABLE gn090 ALTER hash SET STORAGE PLAIN");
if (GNUNET_OK !=
GNUNET_POSTGRES_check_result (plugin->dbh, ret, PGRES_COMMAND_OK, "ALTER TABLE", "gn090"))
{
PQfinish (plugin->dbh);
plugin->dbh = NULL;
return GNUNET_SYSERR;
}
PQclear (ret);
ret = PQexec (plugin->dbh, "ALTER TABLE gn090 ALTER vhash SET STORAGE PLAIN");
if (GNUNET_OK !=
GNUNET_POSTGRES_check_result (plugin->dbh, ret, PGRES_COMMAND_OK, "ALTER TABLE", "gn090"))
{
PQfinish (plugin->dbh);
plugin->dbh = NULL;
return GNUNET_SYSERR;
}
PQclear (ret);
if ((GNUNET_OK !=
GNUNET_POSTGRES_prepare (plugin->dbh, "getvt",
"SELECT type, prio, anonLevel, expire, hash, value, oid FROM gn090 "
"WHERE hash=$1 AND vhash=$2 AND type=$3 "
"ORDER BY oid ASC LIMIT 1 OFFSET $4", 4)) ||
(GNUNET_OK !=
GNUNET_POSTGRES_prepare (plugin->dbh, "gett",
"SELECT type, prio, anonLevel, expire, hash, value, oid FROM gn090 "
"WHERE hash=$1 AND type=$2 "
"ORDER BY oid ASC LIMIT 1 OFFSET $3", 3)) ||
(GNUNET_OK !=
GNUNET_POSTGRES_prepare (plugin->dbh, "getv",
"SELECT type, prio, anonLevel, expire, hash, value, oid FROM gn090 "
"WHERE hash=$1 AND vhash=$2 "
"ORDER BY oid ASC LIMIT 1 OFFSET $3", 3)) ||
(GNUNET_OK !=
GNUNET_POSTGRES_prepare (plugin->dbh, "get",
"SELECT type, prio, anonLevel, expire, hash, value, oid FROM gn090 "
"WHERE hash=$1 " "ORDER BY oid ASC LIMIT 1 OFFSET $2", 2)) ||
(GNUNET_OK !=
GNUNET_POSTGRES_prepare (plugin->dbh, "count_getvt",
"SELECT count(*) FROM gn090 WHERE hash=$1 AND vhash=$2 AND type=$3", 3)) ||
(GNUNET_OK !=
GNUNET_POSTGRES_prepare (plugin->dbh, "count_gett",
"SELECT count(*) FROM gn090 WHERE hash=$1 AND type=$2", 2)) ||
(GNUNET_OK !=
GNUNET_POSTGRES_prepare (plugin->dbh, "count_getv",
"SELECT count(*) FROM gn090 WHERE hash=$1 AND vhash=$2", 2)) ||
(GNUNET_OK !=
GNUNET_POSTGRES_prepare (plugin->dbh, "count_get",
"SELECT count(*) FROM gn090 WHERE hash=$1", 1)) ||
(GNUNET_OK !=
GNUNET_POSTGRES_prepare (plugin->dbh, "put",
"INSERT INTO gn090 (repl, type, prio, anonLevel, expire, rvalue, hash, vhash, value) "
"VALUES ($1, $2, $3, $4, $5, RANDOM(), $6, $7, $8)", 9)) ||
(GNUNET_OK !=
GNUNET_POSTGRES_prepare (plugin->dbh, "update",
"UPDATE gn090 SET prio = prio + $1, expire = CASE WHEN expire < $2 THEN $2 ELSE expire END "
"WHERE oid = $3", 3)) ||
(GNUNET_OK !=
GNUNET_POSTGRES_prepare (plugin->dbh, "decrepl",
"UPDATE gn090 SET repl = GREATEST (repl - 1, 0) "
"WHERE oid = $1", 1)) ||
(GNUNET_OK !=
GNUNET_POSTGRES_prepare (plugin->dbh, "select_non_anonymous",
"SELECT type, prio, anonLevel, expire, hash, value, oid FROM gn090 "
"WHERE anonLevel = 0 AND type = $1 ORDER BY oid DESC LIMIT 1 OFFSET $2",
1)) ||
(GNUNET_OK !=
GNUNET_POSTGRES_prepare (plugin->dbh, "select_expiration_order",
"(SELECT type, prio, anonLevel, expire, hash, value, oid FROM gn090 "
"WHERE expire < $1 ORDER BY prio ASC LIMIT 1) " "UNION "
"(SELECT type, prio, anonLevel, expire, hash, value, oid FROM gn090 "
"ORDER BY prio ASC LIMIT 1) " "ORDER BY expire ASC LIMIT 1",
1)) ||
(GNUNET_OK !=
GNUNET_POSTGRES_prepare (plugin->dbh, "select_replication_order",
"SELECT type, prio, anonLevel, expire, hash, value, oid FROM gn090 "
"ORDER BY repl DESC,RANDOM() LIMIT 1", 0)) ||
(GNUNET_OK !=
GNUNET_POSTGRES_prepare (plugin->dbh, "delrow", "DELETE FROM gn090 " "WHERE oid=$1", 1)) ||
(GNUNET_OK !=
GNUNET_POSTGRES_prepare (plugin->dbh, "get_keys", "SELECT hash FROM gn090", 0)))
{
PQfinish (plugin->dbh);
plugin->dbh = NULL;
return GNUNET_SYSERR;
}
return GNUNET_OK;
}
/**
* @brief Get a database handle
*
* @param plugin global context
* @return #GNUNET_OK on success, #GNUNET_SYSERR on error
*/
static int
init_connection (struct Plugin *plugin)
{
PGresult *ret;
plugin->dbh = GNUNET_POSTGRES_connect (plugin->env->cfg,
"datacache-postgres");
if (NULL == plugin->dbh)
return GNUNET_SYSERR;
ret =
PQexec (plugin->dbh,
"CREATE TEMPORARY TABLE gn090dc ("
" type INTEGER NOT NULL DEFAULT 0,"
" discard_time BIGINT NOT NULL DEFAULT 0,"
" key BYTEA NOT NULL DEFAULT '',"
" value BYTEA NOT NULL DEFAULT '',"
" path BYTEA DEFAULT '')"
"WITH OIDS");
if ( (ret == NULL) ||
((PQresultStatus (ret) != PGRES_COMMAND_OK) &&
(0 != strcmp ("42P07", /* duplicate table */
PQresultErrorField
(ret,
PG_DIAG_SQLSTATE)))))
{
(void) GNUNET_POSTGRES_check_result (plugin->dbh, ret,
PGRES_COMMAND_OK,
"CREATE TABLE",
"gn090dc");
PQfinish (plugin->dbh);
plugin->dbh = NULL;
return GNUNET_SYSERR;
}
if (PQresultStatus (ret) == PGRES_COMMAND_OK)
{
if ((GNUNET_OK !=
GNUNET_POSTGRES_exec (plugin->dbh,
"CREATE INDEX idx_key ON gn090dc (key)")) ||
(GNUNET_OK !=
GNUNET_POSTGRES_exec (plugin->dbh,
"CREATE INDEX idx_dt ON gn090dc (discard_time)")))
{
PQclear (ret);
PQfinish (plugin->dbh);
plugin->dbh = NULL;
return GNUNET_SYSERR;
}
}
PQclear (ret);
ret =
PQexec (plugin->dbh,
"ALTER TABLE gn090dc ALTER value SET STORAGE EXTERNAL");
if (GNUNET_OK !=
GNUNET_POSTGRES_check_result (plugin->dbh,
ret,
PGRES_COMMAND_OK,
"ALTER TABLE",
"gn090dc"))
{
PQfinish (plugin->dbh);
plugin->dbh = NULL;
return GNUNET_SYSERR;
}
PQclear (ret);
ret = PQexec (plugin->dbh,
"ALTER TABLE gn090dc ALTER key SET STORAGE PLAIN");
if (GNUNET_OK !=
GNUNET_POSTGRES_check_result (plugin->dbh,
ret,
PGRES_COMMAND_OK,
"ALTER TABLE",
"gn090dc"))
{
PQfinish (plugin->dbh);
plugin->dbh = NULL;
return GNUNET_SYSERR;
}
PQclear (ret);
if ((GNUNET_OK !=
GNUNET_POSTGRES_prepare (plugin->dbh,
"getkt",
"SELECT discard_time,type,value,path FROM gn090dc "
"WHERE key=$1 AND type=$2 ", 2)) ||
(GNUNET_OK !=
GNUNET_POSTGRES_prepare (plugin->dbh,
"getk",
"SELECT discard_time,type,value,path FROM gn090dc "
"WHERE key=$1", 1)) ||
(GNUNET_OK !=
GNUNET_POSTGRES_prepare (plugin->dbh,
"getm",
"SELECT length(value),oid,key FROM gn090dc "
"ORDER BY discard_time ASC LIMIT 1", 0)) ||
(GNUNET_OK !=
GNUNET_POSTGRES_prepare (plugin->dbh,
"get_random",
"SELECT discard_time,type,value,path,key FROM gn090dc "
"ORDER BY key ASC LIMIT 1 OFFSET $1", 1)) ||
(GNUNET_OK !=
GNUNET_POSTGRES_prepare (plugin->dbh,
"get_closest",
"SELECT discard_time,type,value,path,key FROM gn090dc "
"WHERE key>=$1 ORDER BY key ASC LIMIT $2", 1)) ||
(GNUNET_OK !=
GNUNET_POSTGRES_prepare (plugin->dbh,
"delrow",
"DELETE FROM gn090dc WHERE oid=$1", 1)) ||
(GNUNET_OK !=
GNUNET_POSTGRES_prepare (plugin->dbh,
"put",
"INSERT INTO gn090dc (type, discard_time, key, value, path) "
"VALUES ($1, $2, $3, $4, $5)", 5)))
{
PQfinish (plugin->dbh);
plugin->dbh = NULL;
return GNUNET_SYSERR;
}
return GNUNET_OK;
}
