static MString*
psql_make_sql_insert (DbTable* table)
{
int n = 0;
int max = table->schema->nfields;
if (max <= 0) {
logerror ("psql: Trying to insert 0 values into table '%s'\n", table->schema->name);
goto fail_exit;
}
MString* mstr = mstring_create ();
if (mstr == NULL) {
logerror("psql: Failed to create managed string for preparing SQL INSERT statement\n");
goto fail_exit;
}
/* Build SQL "INSERT INTO" statement */
n += mstring_set (mstr, "INSERT INTO \"");
n += mstring_cat (mstr, table->schema->name);
n += mstring_cat (mstr, "\" VALUES ($1, $2, $3, $4"); /* metadata columns */
while (max-- > 0)
mstring_sprintf (mstr, ", $%d", 4 + table->schema->nfields - max);
mstring_cat (mstr, ");");
if (n != 0) goto fail_exit;
return mstr;
fail_exit:
if (mstr) mstring_delete (mstr);
return NULL;
}
/** Create a new out stream for sending over the network
* \param transport string representing the protocol used to establish the connection (oml_strndup()'d locally)
* \param hostname string representing the host to connect to (oml_strndup()'d locally)
* \param service symbolic name or port number of the service to connect to (oml_strndup()'d locally)
* \return a new OmlOutStream instance
*
* \see oml_strndup
*/
OmlOutStream*
net_stream_new(const char *transport, const char *hostname, const char *service)
{
MString *dest;
assert(transport != NULL && hostname != NULL && service != NULL);
OmlNetOutStream* self = (OmlNetOutStream *)oml_malloc(sizeof(OmlNetOutStream));
memset(self, 0, sizeof(OmlNetOutStream));
dest = mstring_create();
mstring_sprintf(dest, "%s://%s:%s", transport, hostname, service);
self->dest = (char*)oml_strndup (mstring_buf(dest), mstring_len(dest));
mstring_delete(dest);
self->protocol = (char*)oml_strndup (transport, strlen (transport));
self->host = (char*)oml_strndup (hostname, strlen (hostname));
self->service = (char*)oml_strndup (service, strlen (service));
logdebug("%s: Created OmlNetOutStream\n", self->dest);
socket_set_non_blocking_mode(0);
/* // Now see if we can connect to server */
/* if (! open_socket(self)) { */
/* free(self); */
/* return NULL; */
/* } */
self->write = net_stream_write;
self->close = net_stream_close;
return (OmlOutStream*)self;
}
/** Create a new out stream for writing into a local file.
*
* \param file destination file (oml_strndup()'d locally)
* \return a new OmlOutStream instance
*
* \see oml_strndup
*/
OmlOutStream*
file_stream_new(const char *file)
{
MString *dest;
OmlFileOutStream* self = (OmlFileOutStream *)oml_malloc(sizeof(OmlFileOutStream));
memset(self, 0, sizeof(OmlFileOutStream));
loginfo ("File_stream: opening local storage file '%s'\n", file);
if (strcmp(file, "stdout") == 0 || strcmp(file, "-") == 0) {
self->f = stdout;
} else {
if ((self->f = fopen(file, "a+")) == NULL) {
logerror ("Can't open local storage file '%s'\n", file);
return 0;
}
}
dest = mstring_create();
mstring_sprintf(dest, "file:%s", file);
self->dest = (char*)oml_strndup (mstring_buf(dest), mstring_len(dest));
mstring_delete(dest);
self->write = file_stream_write;
self->close = file_stream_close;
return (OmlOutStream*)self;
}
static int
psql_set_sender_id (Database *database, const char *name, int id)
{
MString *mstr = mstring_create();
mstring_sprintf (mstr, "%d", id);
int ret = psql_set_key_value (database, "_senders", "name", "id", name, mstring_buf (mstr));
mstring_delete (mstr);
return ret;
}
/** Prepare the conninfo string to connect to the Postgresql server.
*
* \param host server hostname
* \param port server port or service name
* \param user username
* \param pass password
* \param extra_conninfo additional connection parameters
* \return a dynamically allocated MString containing the connection information; the caller must take care of freeing it
*
* \see mstring_delete
*/
static MString *psql_prepare_conninfo(const char *database, const char *host, const char *port, const char *user, const char *pass, const char *extra_conninfo)
{
MString *conninfo;
int portnum;
portnum = resolve_service(port, 5432);
conninfo = mstring_create();
mstring_sprintf (conninfo, "host='%s' port='%d' user='******' password='******' dbname='%s' %s",
host, portnum, user, pass, database, extra_conninfo);
return conninfo;
}
int
psql_set_key_value (Database *database, const char *table,
const char *key_column, const char *value_column,
const char *key, const char *value)
{
PsqlDB *psqldb = (PsqlDB*) database->handle;
MString *stmt = mstring_create ();
char *check_value = psql_get_key_value (database, table, key_column, value_column, key);
if (check_value == NULL)
mstring_sprintf (stmt, "INSERT INTO \"%s\" (\"%s\", \"%s\") VALUES ('%s', '%s');",
table, key_column, value_column, key, value);
else
mstring_sprintf (stmt, "UPDATE \"%s\" SET \"%s\"='%s' WHERE \"%s\"='%s';",
table, value_column, value, key_column, key);
if (sql_stmt (psqldb, mstring_buf (stmt))) {
logwarn("psql:%s: Key-value update failed for %s='%s' in %s(%s, %s) (database error)\n",
database->name, key, value, table, key_column, value_column);
return -1;
}
return 0;
}
/**
* @brief Do a key-value style select on a database table.
*
* This function does a key lookup on a database table that is set up
* in key-value style. The table can have more than two columns, but
* this function SELECT's two of them and returns the value of the
* value column. It checks to make sure that the key returned is the
* one requested, then returns its corresponding value.
*
* This function makes a lot of assumptions about the database and
* the table:
*
* #- the database exists and is open
* #- the table exists in the database
* #- there is a column named key_column in the table
* #- there is a column named value_column in the table
*
* The function does not check for any of these conditions, but just
* assumes they are true. Be advised.
*
* @param database the database to look up in.
* @param table the name of the table to look up in.
* @param key_column the name of the column holding the key strings.
* @param value_column the name of the column holding the value strings.
* @param key the key string to look up (i.e. WHERE key_column='key').
*
* @return the string value corresponding to the given key, or NULL
* if an error occurred of if the key was not present in the table.
*/
char*
psql_get_key_value (Database *database, const char *table,
const char *key_column, const char *value_column,
const char *key)
{
if (database == NULL || table == NULL || key_column == NULL ||
value_column == NULL || key == NULL)
return NULL;
PGresult *res;
PsqlDB *psqldb = (PsqlDB*) database->handle;
MString *stmt = mstring_create();
mstring_sprintf (stmt, "SELECT %s FROM %s WHERE %s='%s';",
value_column, table, key_column, key);
res = PQexec (psqldb->conn, mstring_buf (stmt));
if (PQresultStatus(res) != PGRES_TUPLES_OK) {
logerror("psql:%s: Error trying to get %s[%s]; (%s).\n",
database->name, table, key, PQerrorMessage(psqldb->conn));
goto fail_exit;
}
if (PQntuples (res) == 0)
goto fail_exit;
if (PQnfields (res) < 1)
goto fail_exit;
if (PQntuples (res) > 1)
logwarn("psql:%s: Key-value lookup for key '%s' in %s(%s, %s) returned more than one possible key.\n",
database->name, key, table, key_column, value_column);
char *value = NULL;
value = PQgetvalue (res, 0, 0);
if (value != NULL)
value = xstrndup (value, strlen (value));
PQclear (res);
mstring_delete (stmt);
return value;
fail_exit:
PQclear (res);
mstring_delete (stmt);
return NULL;
}
/** Setup the PostgreSQL backend.
*
* \return 0 on success, -1 otherwise
*/
int
psql_backend_setup ()
{
MString *str, *conninfo;
loginfo ("psql: Sending experiment data to PostgreSQL server %s:%s as user '%s'\n",
pg_host, pg_port, pg_user);
conninfo = psql_prepare_conninfo("postgres", pg_host, pg_port, pg_user, pg_pass, pg_conninfo);
PGconn *conn = PQconnectdb (mstring_buf (conninfo));
if (PQstatus (conn) != CONNECTION_OK) {
logerror ("psql: Could not connect to PostgreSQL database (conninfo \"%s\"): %s\n",
mstring_buf(conninfo), PQerrorMessage (conn));
mstring_delete(conninfo);
return -1;
}
/* oml2-server must be able to create new databases, so check that
our user has the required role attributes */
str = mstring_create();
mstring_sprintf (str, "SELECT rolcreatedb FROM pg_roles WHERE rolname='%s'", pg_user);
PGresult *res = PQexec (conn, mstring_buf (str));
mstring_delete(str);
if (PQresultStatus (res) != PGRES_TUPLES_OK) {
logerror ("psql: Failed to determine role privileges for role '%s': %s\n",
pg_user, PQerrorMessage (conn));
return -1;
}
char *has_create = PQgetvalue (res, 0, 0);
if (strcmp (has_create, "t") == 0)
logdebug ("psql: User '%s' has CREATE DATABASE privileges\n", pg_user);
else {
logerror ("psql: User '%s' does not have required role CREATE DATABASE\n", pg_user);
return -1;
}
mstring_delete(conninfo);
PQclear (res);
PQfinish (conn);
return 0;
}
/**
* \brief Called when a node connects via TCP
* \param
* \return
*/
void
on_connect (Socket* client_sock, void* handle)
{
(void)handle; // This parameter is unused
MString *mstr = mstring_create ();
mstring_sprintf (mstr,"%s.%d", resultfile_name, session->client_count);
logdebug("New client (index %d) connected\n", session->client_count);
session->client_count++;
Client* client = client_new(client_sock, page_size, mstring_buf (mstr),
downstream_port, downstream_address);
mstring_delete (mstr);
session_add_client (session, client);
client->session = session;
client->recv_event = eventloop_on_read_in_channel(client_sock, client_callback,
status_callback, (void*)client);
pthread_create (&client->thread, NULL, client_send_thread, (void*)client);
}
/** Helper function to build and send a database-related event to the server hook.
*
* \param self Database on which the event happened
* \param event event to report
*
* \return 0 on success (even if the hook is disabled), -1 otherwise
*
* \see HOOK_CMD_DBCREATED, HOOK_CMD_DBOPENED, HOOK_CMD_DBCLOSED
*/
static int
database_hook_send_event(Database *self, const char* event){
char dburi[PATH_MAX+1];
MString *hook_command = mstring_create();
if(hook_enabled()) {
if(!self->get_uri(self, dburi, sizeof(dburi))) {
logwarn("%s: Unable to get full URI to database for hook\n", self->name);
} else if (mstring_sprintf(hook_command, "%s %s\n",
event, dburi) == -1) {
logwarn("%s: Failed to construct command string for event hook\n", self->name);
mstring_delete(hook_command);
return -1;
} else if(hook_write(mstring_buf(hook_command), mstring_len(hook_command)) < (int)mstring_len(hook_command)) {
logwarn("%s: Failed to send command string to event hook: %s\n", self->name, strerror(errno));
mstring_delete(hook_command);
return -1;
}
}
return 0;
}
/** Create or open an PostgreSQL database
* \param db the databse to associate with the sqlite3 database
* \return 0 if successful, -1 otherwise
*/
int
psql_create_database(Database* db)
{
MString *conninfo;
MString *str;
PGconn *conn;
PGresult *res = NULL;
int ret = -1;
loginfo ("psql:%s: Accessing database\n", db->name);
/*
* Make a connection to the database server -- check if the
* requested database exists or not by connecting to the 'postgres'
* database and querying that.
*/
conninfo = psql_prepare_conninfo("postgres", pg_host, pg_port, pg_user, pg_pass, pg_conninfo);
conn = PQconnectdb(mstring_buf (conninfo));
/* Check to see that the backend connection was successfully made */
if (PQstatus(conn) != CONNECTION_OK) {
logerror ("psql: Could not connect to PostgreSQL database (conninfo \"%s\"): %s\n",
mstring_buf(conninfo), PQerrorMessage (conn));
goto cleanup_exit;
}
PQsetNoticeReceiver(conn, psql_receive_notice, "postgres");
str = mstring_create();
mstring_sprintf (str, "SELECT datname from pg_database where datname='%s';", db->name);
res = PQexec (conn, mstring_buf (str));
if (PQresultStatus (res) != PGRES_TUPLES_OK) {
logerror ("psql: Could not get list of existing databases\n");
goto cleanup_exit;
}
/* No result rows means database doesn't exist, so create it instead */
if (PQntuples (res) == 0) {
loginfo ("psql:%s: Database does not exist, creating it\n", db->name);
mstring_set (str, "");
mstring_sprintf (str, "CREATE DATABASE \"%s\";", db->name);
res = PQexec (conn, mstring_buf (str));
if (PQresultStatus (res) != PGRES_COMMAND_OK) {
logerror ("psql:%s: Could not create database: %s\n", db->name, PQerrorMessage (conn));
goto cleanup_exit;
}
}
PQfinish (conn);
mstring_delete(conninfo);
/* Now that the database should exist, make a connection to the it for real */
conninfo = psql_prepare_conninfo(db->name, pg_host, pg_port, pg_user, pg_pass, pg_conninfo);
conn = PQconnectdb(mstring_buf (conninfo));
/* Check to see that the backend connection was successfully made */
if (PQstatus(conn) != CONNECTION_OK) {
logerror ("psql:%s: Could not connect to PostgreSQL database (conninfo \"%s\"): %s\n",
db->name, mstring_buf(conninfo), PQerrorMessage (conn));
goto cleanup_exit;
}
PQsetNoticeReceiver(conn, psql_receive_notice, db->name);
PsqlDB* self = (PsqlDB*)xmalloc(sizeof(PsqlDB));
self->conn = conn;
self->last_commit = time (NULL);
db->create = psql_create_database;
db->release = psql_release;
db->table_create = psql_table_create;
db->table_create_meta = psql_table_create_meta;
db->table_free = psql_table_free;
db->insert = psql_insert;
db->add_sender_id = psql_add_sender_id;
db->get_metadata = psql_get_metadata;
db->set_metadata = psql_set_metadata;
db->get_uri = psql_get_uri;
db->get_table_list = psql_get_table_list;
db->handle = self;
begin_transaction (self);
/* Everything was successufl, prepare for cleanup */
ret = 0;
cleanup_exit:
if (res) PQclear (res);
if (ret) PQfinish (conn); /* If return !=0, cleanup connection */
mstring_delete (str);
mstring_delete (conninfo);
return ret;
}
TableDescr*
psql_get_table_list (Database *database, int *num_tables)
{
PsqlDB *self = database->handle;
const char *stmt_tablename =
"SELECT tablename FROM pg_tables WHERE tablename NOT LIKE 'pg%' AND tablename NOT LIKE 'sql%';";
PGresult *res;
TableDescr *tables = NULL;
int rows, cols, i;
*num_tables = -1;
res = PQexec (self->conn, stmt_tablename);
if (PQresultStatus (res) != PGRES_TUPLES_OK) {
logerror("psql:%s: Couldn't get list of tables: %s\n",
database->name, PQerrorMessage (self->conn));
PQclear (res);
return NULL;
}
rows = PQntuples (res);
cols = PQnfields (res);
if (cols < 1)
return NULL;
int have_meta = 0;
for (i = 0; i < rows && !have_meta; i++)
if (strcmp (PQgetvalue (res, i, 0), "_experiment_metadata") == 0)
have_meta = 1;
if(!have_meta)
logdebug("psql:%s: No metadata found\n", database->name);
*num_tables = 0;
for (i = 0; i < rows; i++) {
char *val = PQgetvalue (res, i, 0);
logdebug("psql:%s: Found table '%s'", database->name, val);
MString *str = mstring_create ();
TableDescr *t = NULL;
if (have_meta) {
mstring_sprintf (str, "SELECT value FROM _experiment_metadata WHERE key='table_%s';", val);
PGresult *schema_res = PQexec (self->conn, mstring_buf (str));
if (PQresultStatus (schema_res) != PGRES_TUPLES_OK) {
logdebug("psql:%s: Couldn't get schema for table '%s': %s; skipping\n",
database->name, val, PQerrorMessage (self->conn));
mstring_delete (str);
continue;
}
int rows = PQntuples (schema_res);
if (rows == 0) {
logdebug("psql:%s: Metadata for table '%s' found but empty\n", database->name, val);
t = table_descr_new (val, NULL); // Don't know the schema for this table
} else {
logdebug("psql:%s: Stored schema for table '%s': %s\n", database->name, val, PQgetvalue (schema_res, 0, 0));
struct schema *schema = schema_from_meta (PQgetvalue (schema_res, 0, 0));
t = table_descr_new (val, schema);
}
PQclear (schema_res);
mstring_delete (str);
} else {
t = table_descr_new (val, NULL);
}
if (t) {
t->next = tables;
tables = t;
(*num_tables)++;
}
}
return tables;
}
/**
* @brief Create a PostgreSQL table
* @param db the database that contains the sqlite3 db
* @param table the table to associate in sqlite3 database
* @return 0 if successful, -1 otherwise
*/
static int
table_create (Database* db, DbTable* table, int backend_create)
{
if (db == NULL) {
logerror("psql: Tried to create a table in a NULL database\n");
return -1;
}
if (table == NULL) {
logerror("psql:%s: Tried to create a table from a NULL definition\n", db->name);
return -1;
}
if (table->schema == NULL) {
logerror("psql:%s: No schema defined for table, cannot create\n", db->name);
return -1;
}
MString *insert = NULL, *create = NULL;
PsqlDB* psqldb = (PsqlDB*)db->handle;
PGresult *res;
if (backend_create) {
int sindex = table->schema->index;
table->schema->index = -1;
MString *mstr = mstring_create ();
mstring_sprintf (mstr, "table_%s", table->schema->name);
const char *meta = schema_to_meta (table->schema);
table->schema->index = sindex;
logdebug("psql:%s: SET META: %s\n", db->name, meta);
psql_set_metadata (db, mstring_buf (mstr), meta);
create = schema_to_sql (table->schema, oml_to_postgresql_type);
if (!create) {
logerror("psql:%s: Failed to build SQL CREATE TABLE statement string for table '%s'\n",
db->name, table->schema->name);
goto fail_exit;
}
if (sql_stmt (psqldb, mstring_buf (create))) {
logerror("psql:%s: Could not create table '%s': %s\n",
db->name, table->schema->name,
PQerrorMessage (psqldb->conn));
goto fail_exit;
}
}
insert = psql_make_sql_insert (table);
if (!insert) {
logerror("psql:%s: Failed to build SQL INSERT INTO statement for table '%s'\n",
db->name, table->schema->name);
goto fail_exit;
}
/* Prepare the insert statement and update statement */
PsqlTable* psqltable = (PsqlTable*)xmalloc(sizeof(PsqlTable));
table->handle = psqltable;
MString *insert_name = mstring_create();
mstring_set (insert_name, "OMLInsert-");
mstring_cat (insert_name, table->schema->name);
res = PQprepare(psqldb->conn,
mstring_buf (insert_name),
mstring_buf (insert),
table->schema->nfields + 4, // FIXME: magic number of metadata cols
NULL);
if (PQresultStatus(res) != PGRES_COMMAND_OK) {
logerror("psql:%s: Could not prepare statement: %s\n",
db->name, PQerrorMessage(psqldb->conn));
PQclear(res);
goto fail_exit;
return -1;
}
PQclear(res);
psqltable->insert_stmt = insert_name;
if (create) mstring_delete (create);
if (insert) mstring_delete (insert);
return 0;
fail_exit:
if (create) mstring_delete (create);
if (insert) mstring_delete (insert);
return -1;
}
