/*-
 * Copyright (c) 2006 Fredrik Lindberg. <fli at shapeshifter dot se>
 * All rights reserved.
 *
 * Redistribution and use in source and binary forms, with or without
 * modification, are permitted provided that the following conditions
 * are met:
 * 1. Redistributions of source code must retain the above copyright
 *    notice, this list of conditions and the following disclaimer.
 * 2. Redistributions in binary form must reproduce the above copyright
 *    notice, this list of conditions and the following disclaimer in the
 *    documentation and/or other materials provided with the distribution.
 *
 * THIS SOFTWARE IS PROVIDED BY THE AUTHOR ``AS IS'' AND ANY EXPRESS OR
 * IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES
 * OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED.
 * IN NO EVENT SHALL THE AUTHOR BE LIABLE FOR ANY DIRECT, INDIRECT,
 * INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT
 * NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE,
 * DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY
 * THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
 * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF
 * THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
 *
 * $Id: mysql.c 38 2006-02-20 11:58:51Z fli $
 */

/*
 * MySQL backend to libbirdb
 */

#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <sys/types.h>
#include <sys/stat.h>
#include <sys/queue.h>
#include <db.h>
#include <fcntl.h>
#include <limits.h>
#include <errno.h>
#include <err.h>
#include <bioapi.h>
#include <mysql.h>

#include <birdb.h>

static void * mysqldb_open(const char *, int, char *[]);
static void mysqldb_close(void *);
static struct birdb_rec ** mysqldb_get(void *, struct birdb_rec *);
static void mysqldb_freegetres(void *, struct birdb_rec **);
static int mysqldb_ins(void *, struct birdb_rec *);
static int mysqldb_del(void *, struct birdb_rec *);
static struct birdb_rec * mysqldb_seq_getfirst(void *);
static struct birdb_rec * mysqldb_seq_getnext(void *, struct birdb_rec *);
static void mysqldb_seq_free(void *handle, struct birdb_rec *);

static BioAPI_BIR * buf2bir(char *, size_t);

/*
 * Default database "birdb", default table "bir"
 */
#define DEF_DB	"bioapi:bir"

/* Backend module glue */
BIRDB_BACKEND_PARAMS = {
	"mysql",
	"MySQL backend",
	mysqldb_open,
	mysqldb_close,
	mysqldb_get,
	mysqldb_freegetres,
	mysqldb_ins,
	mysqldb_del,
	mysqldb_seq_getfirst,
	mysqldb_seq_getnext,
	mysqldb_seq_free,
};

/*
 * mysql handle
 */
struct mysqldb_handle {
	MYSQL mysql;
	char *bspid;
	char *bspid_esc;
	struct {
		char *host;
		char *port;
		char *user;
		char *pwd;
		char *name;
		char *table;
	} db;
};

/*
 * Open and initialization routine
 * Arguments
 *   bspid - BSP uuid
 *	 argc - Number of arguments
 *	 argv - Argument array
 * 
 * Requires three arguments
 *  host:port user:pwd db:table
 * where the part to the right of the : might be left out for default
 * or blank values. 
 * 
 * Returns a mysqldb handle on success, otherwise NULL.
 */
static void *
mysqldb_open(const char *bspid, int argc, char *argv[])
{
	struct mysqldb_handle *mh;
	MYSQL *mysql;

	if (bspid == NULL) {
		warnx("Missing BSP ID");
		return (NULL);
	}

	if (argc < 2) {
		warnx("Missing arguments");
		return (NULL);
	}

	mh = malloc(sizeof(struct mysqldb_handle));
	if (mh == NULL)
		return (NULL);

	mh->bspid = strdup(bspid);
	if (mh->bspid == NULL)
		goto error1;

	mh->db.host = mh->db.port = strdup(argv[0]);
	mh->db.host = strsep(&mh->db.port, ":");

	mh->db.user = mh->db.pwd = strdup(argv[1]);
	mh->db.user = strsep(&mh->db.pwd, ":");

	mh->db.name = mh->db.table =
	    (argc == 3) ? strdup(argv[2]) : strdup(DEF_DB);
	mh->db.name = strsep(&mh->db.table, ":");

	if (mh->db.table == NULL) {
		warnx("Missing database table"); 
		goto error2;
	}

	mysql_init(&mh->mysql);
	mysql = mysql_real_connect(&mh->mysql, mh->db.host,
	    mh->db.user, mh->db.pwd, mh->db.name,
	    mh->db.port != NULL ? atoi(mh->db.port) : 0,
	    NULL, 0);
	if (mysql == NULL) {
		warnx("Unable to connect to MySQL database ``%s'' at host %s\n",
		    mh->db.host, mysql_error(&mh->mysql));
		goto error2;
	}

	mh->bspid_esc = malloc((strlen(bspid) * 2) + 1);
	mysql_real_escape_string(mysql, mh->bspid_esc, bspid, strlen(bspid));

	return (mh);
error2:
	free(mh->bspid);
error1:
	free(mh);
	return (NULL);
}

/*
 * Close a mysql handle
 */
static void
mysqldb_close(void *handle)
{
	struct mysqldb_handle *mh = handle;

	mysql_close(&mh->mysql);
	free(mh->db.host);
	free(mh->db.user);
	free(mh->db.name);
	free(mh->bspid);
	free(mh);
}

/*
 * Get a record from the database
 * Arguments
 *     handle - mysql handle
 *     br - BIRdb record (only br_key is used) 
 *
 * Returns an array of birdb records that matches the given key.
 * This array must be freed with freegetres()
 */
static struct birdb_rec **
mysqldb_get(void *handle, struct birdb_rec *br)
{
	struct mysqldb_handle *mh = handle;
	struct birdb_rec **recarray = NULL;
	struct birdb_rec *rec;
	int error, sz, idx;
	MYSQL *mysql = &mh->mysql;
	MYSQL_ROW row;
	MYSQL_RES *queryres;
	char *query, *user;

	user = malloc((strlen(br->br_key) * 2) + 1);
	mysql_real_escape_string(mysql, user, br->br_key, strlen(br->br_key));

	asprintf(&query, "select unix_timestamp(ctime), bir, bir_len" 
	    " from %s where bsp = \"%s\" and user = \"%s\"",
	    mh->db.table, mh->bspid_esc, user);
	if (query == NULL)
		return (NULL);

	error = mysql_query(mysql, query);
	free(query);
	if (error != 0)
		return (NULL);

	queryres = mysql_store_result(mysql);
	if (queryres == NULL)
		return (NULL);

	sz = idx = 0;
	for (row = mysql_fetch_row(queryres);
	    row != NULL;
	    row = mysql_fetch_row(queryres)) {

		sz += sizeof(struct birdb_rec *);
		recarray = realloc(recarray, sz);
		if (recarray == NULL)
			goto fail;

		recarray[idx] = rec = malloc(sizeof(struct birdb_rec));
		rec->br_key = strdup(br->br_key); 
		if (rec->br_key == NULL)
			goto fail;

		rec->br_ctime = atoi(row[0]);
		rec->br_bir = buf2bir(row[1], atoi(row[2]));
		idx++;
	}
	mysql_free_result(queryres);

	/*
	 * Add a final NULL entry in the array to indicate the end
	 */
	if (recarray != NULL) {
		sz += sizeof(struct birdb_rec *);
		recarray = realloc(recarray, sz);
		if (recarray == NULL)
			goto fail;
		recarray[idx] = NULL;	
	}

	return (recarray);
fail:
	mysqldb_freegetres(handle, recarray);
	return (NULL);
}

/*
 * Free results returned by fdb_get()
 * Arguments
 *     handle - filedb handle 
 *     recarray - result array returned by fdb_get 
 */
static void
mysqldb_freegetres(void *handle, struct birdb_rec **recarray)
{
	int idx = 0;

	if (recarray == NULL)
		return;

	while (recarray[idx] != NULL) {
		free(recarray[idx++]);	
	}
	free(recarray);
}

/*
 * Insert a record into the database
 * Arguments
 *     handle - mysql handle
 *     br - the birdb record to be inserted 
 *
 * Returns 0 on success, non-zero on failure.
 */
static int
mysqldb_ins(void *handle, struct birdb_rec *br)
{
	struct mysqldb_handle *mh = handle;
	MYSQL *mysql = &mh->mysql;
	BioAPI_BIR *bir = br->br_bir;
	int nbytes, offset, error;
	char *query, *buf, *buf_blob;
	char *bsp, *user;

	nbytes = bir->Header.Length; 
	nbytes += (bir->Signature != NULL) ? bir->Signature->Length + 4 : 0;
	buf = malloc(nbytes);
	if (buf == NULL)
		return (-1);

	offset = 0; 
	/* Write header */
	memcpy(buf + offset, &bir->Header, sizeof(BioAPI_BIR_HEADER));
	offset += sizeof(BioAPI_BIR_HEADER);

	/* Write biometric data */
	memcpy(buf + offset, bir->BiometricData, 
		 (bir->Header.Length) - sizeof(BioAPI_BIR_HEADER));
	offset += (bir->Header.Length) - sizeof(BioAPI_BIR_HEADER);

	/* Store signature, if present */
	if (bir->Signature) {
		memcpy(buf + offset, &bir->Signature->Length, 4);
		offset += 4; 
		memcpy(buf + offset, bir->Signature->Data,
			bir->Signature->Length);
	}

	buf_blob = malloc((nbytes * 2) + 1);
	mysql_real_escape_string(mysql, buf_blob, buf, nbytes); 

	bsp = malloc((strlen(mh->bspid) * 2) + 1);
	mysql_real_escape_string(mysql, bsp, mh->bspid, strlen(mh->bspid));

	user = malloc((strlen(br->br_key) * 2) + 1);
	mysql_real_escape_string(mysql, user, br->br_key, strlen(br->br_key));

	asprintf(&query, "insert into %s (bsp, user, ctime, bir, bir_len)"
	    " values(\"%s\", \"%s\", from_unixtime(%d), \"%s\", %d)",
	    mh->db.table, bsp, user, br->br_ctime, buf_blob, nbytes);
	if (query == NULL)
		return (-1);

	free(buf);
	free(buf_blob);
	free(bsp);
	free(user);

	error = mysql_query(mysql, query);
	free(query);
	if (error != 0) {
		warn("Failed to insert record into mysql database");
		return (-1);
	}

	return (0);
}

/*
 * Remove a record from the database
 * Arguments
 *     handle - mysql handle
 *     br - the birdb record to be removed 
 *
 * Returns 0 on success, non-zero on failure.
 */
static int
mysqldb_del(void *handle, struct birdb_rec *br)
{
	struct mysqldb_handle *mh = handle;
	MYSQL *mysql = &mh->mysql;
	char *query, *user;
	int error;

	user = malloc((strlen(br->br_key) * 2) + 1);
	mysql_real_escape_string(mysql, user, br->br_key, strlen(br->br_key));
	asprintf(&query, "delete from %s where user = \"%s\""
	    " and ctime = from_unixtime(%d)",
	    mh->db.table, user, br->br_ctime);

	free(user);
	if (query == NULL)
		return (-1);

	error = mysql_query(mysql, query);
	if (error != 0)
		return (-1);

	return (0);
}

/*
 * Get the first record of the database for sequential traversal 
 * Arguments
 *     handle - mysql handle
 *     br - the birdb record to be removed 
 *
 * Returns the first record if any, NULL otherwise 
 */
static struct birdb_rec * 
mysqldb_seq_getfirst(void *handle)
{
	struct mysqldb_handle *mh = handle;
	struct birdb_rec *rec;
	MYSQL *mysql = &mh->mysql;
	MYSQL_RES *queryres;
	MYSQL_ROW row;
	int error;
	char *query;

	asprintf(&query, "lock tables %s write", mh->db.table);
	error = mysql_query(mysql, query);
	if (error != 0) {
		free(query);
		warn("Failed to lock table");
		return (NULL);
	}
	free(query);

	asprintf(&query, "select user, unix_timestamp(ctime), bir,"
	    " bir_len from %s where"
	    " bsp = \"%s\" order by user,ctime asc limit 1",
	    mh->db.table, mh->bspid_esc);

	error = mysql_query(mysql, query);
	if (error != 0) {
		free(query);
		return (NULL);
	}
	free(query);
	queryres = mysql_store_result(mysql);
	if (queryres == NULL)
		return (NULL);

	row = mysql_fetch_row(queryres);
	if (row == NULL) {
		mysql_free_result(queryres);
		return (NULL);
	}

	rec = malloc(sizeof(struct birdb_rec));
	if (rec == NULL)
		return (NULL);

	rec->br_key = strdup(row[0]);
	rec->br_ctime = atoi(row[1]); 
	rec->br_bir = buf2bir(row[2], atoi(row[3]));
	return (rec);
}

/*
 * Get the next logical record for use with sequential traversial 
 * Arguments
 *     handle - mysqldb handle
 *     prev - previous record from getnext or getfirst 
 *
 * Returns the next record if any, NULL otherwise 
 * Resources allocated for the previous record will be freed.
 */
static struct birdb_rec * 
mysqldb_seq_getnext(void *handle, struct birdb_rec *prev)
{
	struct mysqldb_handle *mh = handle;
	struct birdb_rec *rec;
	MYSQL *mysql = &mh->mysql;
	MYSQL_RES *queryres;
	MYSQL_ROW row;
	int error;
	char *query, *user;

	/* Check if the previous record was the last logical record */
	asprintf(&query, "select user, unix_timestamp(ctime) from %s "
	    "order by user desc, ctime desc limit 1", mh->db.table);
	error = mysql_query(mysql, query);
	free(query);

	queryres = mysql_store_result(mysql);
	if (queryres == NULL)
		return (NULL);
	row = mysql_fetch_row(queryres);
	if (row == NULL) {
		mysql_free_result(queryres);
		return (NULL);
	}

	if (strcmp(prev->br_key, row[0]) == 0 &&
	    prev->br_ctime == atoi(row[1])) {
		rec = NULL;
		goto out;
	}
	mysql_free_result(queryres);

	user = malloc((strlen(prev->br_key) * 2) + 1);
	mysql_real_escape_string(mysql, user, prev->br_key,
	    strlen(prev->br_key));
	asprintf(&query,
	    "select user, unix_timestamp(ctime), bir, bir_len from %s where"
	    " bsp = \"%s\" and ((unix_timestamp(ctime) > %d) or"
	    " (unix_timestamp(ctime) >= %d and user != \"%s\"))"
	    " order by user,ctime asc limit 1",
	    mh->db.table, mh->bspid_esc, prev->br_ctime, prev->br_ctime,
	    prev->br_key);

	birdb_freerec(prev);
	free(user);

	error = mysql_query(mysql, query);
	if (error != 0) {
		free(query);
		return (NULL);
	}
	free(query);
	queryres = mysql_store_result(mysql);
	if (queryres == NULL)
		return (NULL);

	row = mysql_fetch_row(queryres);
	if (row == NULL) {
		mysql_free_result(queryres);
		return (NULL);
	}

	rec = malloc(sizeof(struct birdb_rec));
	if (rec == NULL)
		return (NULL);
	rec->br_key = strdup(row[0]);
	rec->br_ctime = atoi(row[1]); 
	rec->br_bir = buf2bir(row[2], atoi(row[3]));

out:
	mysql_free_result(queryres);
	return (rec);
}

/*
 * Free the remains of an sequential traversial
 * Arguments
 *     handle - mysql handle
 *     last - previous record from getnext or getfirst 
 * 
 * This is not needed if getnext returns NULL
 */
static void
mysqldb_seq_free(void *handle, struct birdb_rec *last)
{
	struct mysqldb_handle *mh = handle;
	MYSQL *mysql = &mh->mysql;
	int error;

	error = mysql_query(mysql, "unlock tables");
	if (error != 0)
		warnx("failed to unlock table");
	birdb_freerec(last);
}

/*
 * Creates a BioAPI bir from a byte buffer
 */
static BioAPI_BIR *
buf2bir(char *buf, size_t len)
{
	BioAPI_BIR *bir;
	int offset, nbytes;

	bir = malloc(sizeof(BioAPI_BIR));
	if (bir == NULL)
		return (NULL);

	offset = 0;
	memcpy(&bir->Header, buf, sizeof(BioAPI_BIR_HEADER));
	offset += sizeof(BioAPI_BIR_HEADER);

	nbytes = (bir->Header.Length) - sizeof(BioAPI_BIR_HEADER);
	bir->BiometricData = malloc(nbytes);
	if (bir->BiometricData == NULL) {
		free(bir);
		return (NULL);
	}

	memcpy(bir->BiometricData, buf + offset, nbytes);
	offset += nbytes;

	if (len > offset) {
		bir->Signature = malloc(sizeof (BioAPI_DATA));
		if (bir->Signature == NULL) {
			free(bir->BiometricData);
			free(bir);
			return (NULL);
		}
		memcpy(&bir->Signature->Length, buf + offset, 4);
		offset += 4;
		bir->Signature->Data = malloc(bir->Signature->Length);
		if (bir->Signature->Data == NULL) {
			free(bir->Signature);
			free(bir->BiometricData);
			free(bir);
			return (NULL);
		}
		memcpy(bir->Signature->Data, buf + offset,
			bir->Signature->Length);
	}
	else {
		bir->Signature = NULL;
	}
	return (bir);
}