xdb_sql.c

jabber server jabber server jabber server jabber server

/* --------------------------------------------------------------------------
 *
 * License
 *
 * The contents of this file are subject to the Jabber Open Source License
 * Version 1.0 (the "JOSL").  You may not copy or use this file, in either
 * source code or executable form, except in compliance with the JOSL. You
 * may obtain a copy of the JOSL at http://www.jabber.org/ or at
 * http://www.opensource.org/.  
 *
 * Software distributed under the JOSL is distributed on an "AS IS" basis,
 * WITHOUT WARRANTY OF ANY KIND, either express or implied.  See the JOSL
 * for the specific language governing rights and limitations under the
 * JOSL.
 *
 * Copyrights
 * 
 * Portions created by or assigned to Jabber.com, Inc. are 
 * Copyright (c) 1999-2002 Jabber.com, Inc.  All Rights Reserved.  Contact
 * information for Jabber.com, Inc. is available at http://www.jabber.com/.
 *
 * Portions Copyright (c) 1998-1999 Jeremie Miller.
 * 
 * Acknowledgements
 * 
 * Special thanks to the Jabber Open Source Contributors for their
 * suggestions and support of Jabber.
 * 
 * Alternatively, the contents of this file may be used under the terms of the
 * GNU General Public License Version 2 or later (the "GPL"), in which case
 * the provisions of the GPL are applicable instead of those above.  If you
 * wish to allow use of your version of this file only under the terms of the
 * GPL and not to allow others to use your version of this file under the JOSL,
 * indicate your decision by deleting the provisions above and replace them
 * with the notice and other provisions required by the GPL.  If you do not
 * delete the provisions above, a recipient may use your version of this file
 * under either the JOSL or the GPL. 
 * 
 * --------------------------------------------------------------------------*/

#include <jabberd.h>

/** the namespace of variables in templates in the configuration */
#define NS_XDBSQL "http://jabberd.org/ns/xdbsql"

#ifdef HAVE_MYSQL
#  include <mysql/mysql.h>
#  include <mysql/errmsg.h>
#endif

#ifdef HAVE_POSTGRESQL
#  include <postgresql/libpq-fe.h>
#endif

/**
 * the maximum number of defined namespaces to handle, can be overridden with
 * the <maxns/> configuration setting
 */
#define XDBSQL_MAXNS_PRIME 101

/**
 * @file xdb_sql.c
 * @brief xdb module that handles the requests using a SQL database
 *
 * xdb_sql is an implementation of a xdb module for jabberd14, that handles
 * the xdb requests using an underlying SQL database. Currently only mysql
 * is supported.
 */

/**
 * structure that holds the data used by xdb_sql internally
 */
typedef struct xdbsql_struct {
    xht		namespace_defs;		/**< definitions of queries for the different namespaces */
#ifdef HAVE_MYSQL
    int		use_mysql;		/**< if we want to use the mysql driver */
    MYSQL	*mysql;			/**< our database handle */
    char	*mysql_user;		/**< username for mysql server */
    char	*mysql_password;	/**< password for mysql server */
    char	*mysql_host;		/**< hostname of the mysql server */
    char	*mysql_database;	/**< database on the mysql server */
    int		mysql_port;		/**< port of the mysql server */
    char	*mysql_socket;		/**< socket of the mysql server */
    unsigned long mysql_flag;		/**< flags for the connection to the mysql server */
#endif
#ifdef HAVE_POSTGRESQL
    int		use_postgresql;		/**< if we want to use the postgresql driver */
    PGconn	*postgresql;		/**< our postgresql connection handle */
    char	*postgresql_conninfo;	/**< settings used to connect to postgresql */
#endif
} *xdbsql, _xdbsql;

/**
 * structure that holds the information how to handle a namespace
 */
typedef struct xdbsql_ns_def_struct {
    char	**get_query;		/**< SQL query to handle get requests */
    xmlnode	get_result;		/**< template for results for get requests */
    char	**set;			/**< SQL query to handle set requests */
    char	**delete;		/**< SQL query to delete old values */
} *xdbsql_ns_def, _xdbsql_ns_def;

/**
 * connect to the mysql server
 *
 * @param i the instance we are running in
 * @param xq our internal instance data
 */
void xdb_sql_mysql_connect(instance i, xdbsql xq) {
#ifdef HAVE_MYSQL
    /* connect to the database */
    if (mysql_real_connect(xq->mysql, xq->mysql_host, xq->mysql_user, xq->mysql_password, xq->mysql_database, xq->mysql_port, xq->mysql_socket, xq->mysql_flag) == NULL) {
	log_error(i->id, "failed to connect to mysql server: %s", mysql_error(xq->mysql));
    }
#else
    log_debug2(ZONE, LOGT_STRANGE, "xdb_sql_mysql_connect called, but not compiled in.");
#endif
}

/**
 * add a string to a spool while escaping some characters
 *
 * @param destination the result spool
 * @param new_string what should be added (this gets destroyed!!!)
 */
void xdb_sql_spool_add_escaped(spool destination, char *new_string) {
    char *first_to_escape = NULL;
    char *ptr = NULL;
    char character_to_escape[2] = "\0\0";

    /* check for ' */
    first_to_escape = strchr(new_string, '\'');

    /* is there a " earlier? */
    ptr = strchr(new_string, '"');
    if (ptr != NULL && (ptr < first_to_escape || first_to_escape == NULL)) {
	first_to_escape = ptr;
    }

    /* is there a \ earlier? */
    ptr = strchr(new_string, '\\');
    if (ptr != NULL && (ptr < first_to_escape || first_to_escape == NULL)) {
	first_to_escape = ptr;
    }

    /* is there something to escape? */
    if (first_to_escape == NULL) {
	/* no */
	spool_add(destination, new_string);
	return;
    }

    /* add up to the character that is escaped and this character with escapeing ... */
    character_to_escape[0] = first_to_escape[0];
    first_to_escape[0] = 0;
    spooler(destination, new_string, "\\", character_to_escape, destination);
    
    /* and call recursive */
    xdb_sql_spool_add_escaped(destination, first_to_escape+1);
}

/**
 * use the template for a query to construct a real query
 *
 * @param template the template to construct the SQL query
 * @param xdb_query the xdb query
 * @return SQL query
 */
char *xdb_sql_construct_query(char **template, xmlnode xdb_query) {
    int index = 0;		/* token counter */
    spool result_spool = spool_new(xdb_query->p); /* where to store the result */

    /* sanity check */
    if (template == NULL || xdb_query == NULL) {
	return NULL;
    }

    /* debugging */
    log_debug2(ZONE, LOGT_STORAGE, "constructing query using xdb_query %s", xmlnode2str(xdb_query));

    /* construct the result */
    while (template[index] != NULL) {
	if (index % 2 == 0) {
	    /* copy token */
	    spool_add(result_spool, template[index]);
	} else {
	    /* substitute token */
	    char *subst = NULL;

	    /* XXX very hacky way to select attributes on the root element, xpath in xmlnode would be nice */
	    if (j_strncmp(template[index], "attribute::", 11) == 0) {
		subst = xmlnode_get_attrib(xdb_query, template[index]+11);
	    } else {
		char *ptr;
		/* text node? */
		ptr = strstr(template[index], "/text()");
		if (ptr == NULL || ptr-template[index] != strlen(template[index])-7) {
		    /* attribute value? */
		    ptr = strstr(template[index], "/attribute::");
		    if (ptr == NULL) {
			/* get the element */
			xmlnode subst_node = (xmlnode_get_tag(xdb_query, template[index]));
			if (subst_node != NULL)
			    subst = xmlnode2str(subst_node);
		    } else {
			/* attribute value */
			xmlnode temp_node = NULL;
			char *attribute = pstrdup(xdb_query->p, ptr+12);
			ptr = pstrdup(xdb_query->p, template[index]);
			strstr(ptr, "/attribute::")[0] = 0;
			temp_node = xmlnode_get_tag(xdb_query, ptr);
			subst = xmlnode_get_attrib(temp_node, attribute);
		    }
		} else {
		    /* get the text content of the element */
		    ptr = pstrdup(xdb_query->p, template[index]);
		    strstr(ptr, "/text()")[0] = 0;
		    subst = xmlnode_get_tag_data(xdb_query, ptr);
		}
	    }
	    xdb_sql_spool_add_escaped(result_spool, pstrdup(result_spool->p, subst!=NULL ? subst : ""));
	}

	/* next token */
	index++;
    }

    return spool_print(result_spool);
}

/**
 * find any node in a xmlnode tree that matches the search
 *
 * @todo something like this should become a part of xmlnode
 *
 * @param root the root of the tree we search in
 * @param name which element to search
 * @return the found element, or NULL if no such element
 */
xmlnode xdb_sql_find_node_recursive(xmlnode root, const char *name) {
    xmlnode ptr = NULL;

    /* is it already this node? */
    if (j_strcmp(xmlnode_get_name(root), name) == 0) {
	/* we found it */
	return root;
    }

    /* check the child nodes */
    for (ptr = xmlnode_get_firstchild(root); ptr != NULL; ptr = xmlnode_get_nextsibling(ptr)) {
	xmlnode result = xdb_sql_find_node_recursive(ptr, name);
	if (result != NULL) {
	    return result;
	}
    }

    /* found nothing */
    return NULL;
}

/**
 * execute a sql query using mysql
 *
 * @param i the instance we are running in
 * @param xq instance internal data
 * @param query the SQL query to execute
 * @param template template to construct the result
 * @param result where to add the results
 * @return 0 on success, non zero on failure
 */
int xdb_sql_execute_mysql(instance i, xdbsql xq, char *query, xmlnode template, xmlnode result) {
#ifdef HAVE_MYSQL
    int ret = 0;
    MYSQL_RES *res = NULL;
    MYSQL_ROW row = NULL;
    
    /* try to execute the query */
    ret = mysql_query(xq->mysql, query);

    /* failed and we need to reconnect? */
    if (ret == CR_SERVER_LOST || ret == CR_SERVER_GONE_ERROR) {
	xdb_sql_mysql_connect(i, xq);

	ret = mysql_query(xq->mysql, query);
    }

    /* still an error? log and return */
    if (ret != 0) {
	log_error(i->id, "mysql query (%s) failed: %s", query, mysql_error(xq->mysql));
	return 1;
    }

    /* the mysql query succeded: fetch results */
    while (res = mysql_store_result(xq->mysql)) {
	/* how many fields are in the rows */
	unsigned int num_fields = mysql_num_fields(res);

	/* fetch rows of the result */
	while (row = mysql_fetch_row(res)) {
	    xmlnode variable = NULL;
	    xmlnode new_instance = NULL;

	    log_debug2(ZONE, LOGT_STORAGE, "we got a result row with %u fields", num_fields);
	    
	    /* instantiate a copy of the template */
	    new_instance = xmlnode_dup_pool(result->p, template);

	    /* find variables in the template and replace them with values */
	    while (variable = xdb_sql_find_node_recursive(new_instance, "value")) {
		xmlnode parent = xmlnode_get_parent(variable);
		int value = j_atoi(xmlnode_get_attrib(variable, "value"), 0);
		int parsed = j_strcmp(xmlnode_get_attrib(variable, "parsed"), "parsed") == 0;

		/* hide the template variable */
		xmlnode_hide(variable);

		/* insert the value */
		if (value > 0 && value <= num_fields) {
		    if (parsed) {
			xmlnode fieldvalue = xmlnode_str(row[value-1], j_strlen(row[value-1]));
			xmlnode fieldcopy = xmlnode_dup_pool(result->p, fieldvalue);
			xmlnode_free(fieldvalue);
			xmlnode_insert_tag_node(parent, fieldcopy);
		    } else {
			xmlnode_insert_cdata(parent, row[value-1], -1);
		    }
		}
	    }

	    /* insert the result */
	    log_debug2(ZONE, LOGT_STORAGE, "the row results in: %s", xmlnode2str(new_instance));
	    xmlnode_insert_node(result, xmlnode_get_firstchild(new_instance));
	}

	/* free the result again */
	mysql_free_result(res);
    }

    return 0;
#else
    log_debug2(ZONE, LOGT_STRANGE, "xdb_sql_execute_mysql called, but not compiled in.");
    return 1;
#endif
}

/**
 * execute a sql query using postgresql
 *
 * @param i the instance we are running in
 * @param xq instance internal data
 * @param query the SQL query to execute
 * @param template template to construct the result
 * @param result where to add the results
 * @return 0 on success, non zero on failure
 */
int xdb_sql_execute_postgresql(instance i, xdbsql xq, char *query, xmlnode template, xmlnode result) {
#ifdef HAVE_POSTGRESQL
    PGresult *res = NULL;
    ExecStatusType status = 0;
    int row = 0;
    int fields = 0;

    /* are we still connected? */
    if (PQstatus(xq->postgresql) != CONNECTION_OK) {
	log_warn(i->id, "resetting connection to the PostgreSQL server");
	
	/* reset the connection */
	PQreset(xq->postgresql);

	/* are we now connected? */
	if (PQstatus(xq->postgresql) != CONNECTION_OK) {
	    log_error(i->id, "cannot reset connection: %s", PQerrorMessage(xq->postgresql));
	    return 1;
	}
    }

    /* try to execute the query */
    res = PQexec(xq->postgresql, query);
    if (res == NULL) {
	log_error(i->id, "cannot execute PostgreSQL query: %s", PQerrorMessage(xq->postgresql));
	return 1;
    }

    /* get the status of the execution */
    status = PQresultStatus(res);
    switch (status) {
	case PGRES_EMPTY_QUERY:
	case PGRES_BAD_RESPONSE:
	case PGRES_FATAL_ERROR:
	    log_warn(i->id, "%s: %s", PQresStatus(status), PQresultErrorMessage(res));
	    PQclear(res);
	    return 1;
	case PGRES_COMMAND_OK:
	case PGRES_COPY_OUT:
	case PGRES_COPY_IN:
	    PQclear(res);
	    return 0;
    }

    /* the postgresql query succeded: fetch results */
    fields = PQnfields(res);
    for (row = 0; row < PQntuples(res); row++) {
	xmlnode variable = NULL;
	xmlnode new_instance = NULL;

	/* instantiate a copy of the template */
	new_instance = xmlnode_dup_pool(result->p, template);

	/* find variables in the template and replace them with values */
	while (variable = xdb_sql_find_node_recursive(new_instance, "value")) {
	    xmlnode parent = xmlnode_get_parent(variable);
	    int value = j_atoi(xmlnode_get_attrib(variable, "value"), 0);
	    int parsed = j_strcmp(xmlnode_get_attrib(variable, "parsed"), "parsed") == 0;

	    /* hide the template variable */
	    xmlnode_hide(variable);

	    /* insert the value */
	    if (value > 0 && value <= fields) {
		if (parsed) {
		    xmlnode fieldvalue = xmlnode_str(PQgetvalue(res, row, value-1), PQgetlength(res, row, value-1));
		    xmlnode fieldcopy = xmlnode_dup_pool(result->p, fieldvalue);
		    xmlnode_free(fieldvalue);
		    xmlnode_insert_tag_node(parent, fieldcopy);
		} else {
		    xmlnode_insert_cdata(parent, PQgetvalue(res, row, value-1), PQgetlength(res, row, value-1));
		}
	    }