/**
	@class gatsDBpp::GATS_DB_mysql
	@brief Employs the GATS_DB_mysql class methods.

	@date $Date$
	@version  $Rev$

	@author
		- Lance Deaver

	@copyright
		(©) Copyright 2006 by GATS Inc.
		11864 Canon Blvd., Suite 101, Newport News, VA 23606

		All Rights Reserved. No part of this software or publication may be
		reproduced, stored in a retrieval system, or transmitted, in any form
		or by any means, electronic, mechanical, photocopying, recording, or
		otherwise without the prior written permission of GATS Inc.

	@see		GATS_DB_mysql.h
	@see		GATS_DB_Exception.h
	@bug		None known

	@warning The assignment operator and copy constructor are protected!!

<em> $Id$ </em>
*/


#include "GATS_DB_mysql.h"
#include "GATS_DB_Exception.h"


#include<cstring>

using namespace gatsDBpp;

/**
 * Connects to the MySQL database via the \a mysql_real_connect C API.  See
 * documentation on this function for further details of these parameters.
 *
 * @param[in] user	MySQL database username.
 * @param[in] passwd MySQL database user password.
 * @param[in] database	The database to initally connect into.
 * @param[in] host	The host name or IP address.
 * @param[in] port	The MySQL server port number.
 * @param[in] unix_socket   Specifies the socket or named pipe that should be used
 * @param[in] client_flag Additional options for the database connection. See \a mysql_real_connect
 * documentation for further details.
 *
 * @exception DBConnectionFailed The connection to the MySQL database was not successful.
 */
GATS_DB_mysql::GATS_DB_mysql(const char* user, const char* passwd,
		const char* database, const char* host,
		const unsigned int port, const char* unix_socket,
		unsigned long int client_flag )
{
	if( ! (_connection = ::mysql_init(0) )  )
	{
		THROW_GATS_EXCEPTION( DBConnectionFailed, ::mysql_error(_connection) ) ;
	}
	if( ! ::mysql_real_connect(_connection,host,
		user, passwd,
		database ,port , unix_socket , client_flag) )
	{
		THROW_GATS_EXCEPTION( DBConnectionFailed, ::mysql_error(_connection) ) ;
	}
}
/**
 * Disconnects from the MySQL database using the C API \a mysql_close.
 *
 */
GATS_DB_mysql::~GATS_DB_mysql()
{
	::mysql_close(_connection);
}
/**
 * Provides a C++ wrapper to the \a mysql_select_db C API.
 *
 * @param[in] db_name C type string containing the database name to connect into
 *
 * @exception BadQuery Requested database does not exist or other error occurred.
 */
void GATS_DB_mysql::SelectDB(const char *db_name)
{
	if( ::mysql_select_db(_connection,db_name) ) {
		THROW_GATS_EXCEPTION( BadQuery, ::mysql_error(_connection) ) ;
	}
}

/**
 * Provides a C++ wrapper to the \a mysql_query or \a mysql_real_query C functions.
 * If provided a non-zero length parameter this method executes \a mysql_real_query
 * if length is kept its default 0 this method will execute \a mysql_query.  You
 * will need to provide a non-zero byte length if your sqlstring contains any binary data
 * that could be interpreted as string terminating NULL.
 *
 * @retval GAT_DB_Results A vector<vector<ColData> > containing the rows and field values from
 * the query statement. There is a GATS_DB_Results container available for every Query  however some SQL statements
 * such as INSERT, UPDATE, CREATE, and DELETE  will return empty containers.  A SELECT will
 * return an empty container if it did not find any matching rows.
 *
 * @param[in]  sqlstring  A char* containing the verbatim SQL statement to send to the
 * database.
 * @param[in] length The byte length of the SQL string.  You need to provide this if your
 * SQL statement contains any binary data that could be interpreted as a string terminating
 * NULL.
 *
 * @exception BadQuery  An Error occured while executing this SQL statement.
 *
 */
GATS_DB_Results GATS_DB_mysql::Query(const char* sqlstring, const unsigned long int length)
{
	std::vector< std::vector<ColData> > x;
	if (length == 0) {
		if( ::mysql_query(_connection, sqlstring ) != 0) {
			THROW_GATS_EXCEPTION( BadQuery, ::mysql_error(_connection) ) ;
		}
	} else {
		if( ::mysql_real_query(_connection, sqlstring, length ) != 0) {
			THROW_GATS_EXCEPTION( BadQuery, ::mysql_error(_connection) ) ;
		}
	}
	MYSQL_RES* result= ::mysql_use_result(_connection) ;
	const unsigned int num_fields = ::mysql_field_count(_connection);
	if( ! result ) {
		if( num_fields != 0 )  {
			THROW_GATS_EXCEPTION( BadQuery, ::mysql_error(_connection) ) ;
		}
	} else if(num_fields > 0) {
		MYSQL_ROW row;
		while( (row = ::mysql_fetch_row(result))  ) {
			unsigned long *field_lengths = ::mysql_fetch_lengths(result);
			std::vector<ColData> Cols;
			for(unsigned int iField=0; iField < num_fields; ++iField) {
				Cols.push_back(ColData(row[iField], field_lengths[iField] ) );
			}
			x.push_back(Cols);
		}
	}
	::mysql_free_result(result);
	return x;
}
/**
 * Retrieve a listing of table names from the currently connected database that match the wildcard.  This
 * is similar to performing the following command   SHOW TABLES [LIKE wild];
 * This method is a C++ wrapper to the \a mysql_list_tables C API.
 *
 * @param[in] wild  A char* containing a regular expression what may contain the '\%' or '_' wildcard characters.
 * If NULL the result will be all the tables in the current database.
 *
 * @retval vector<std::string> A vector of strings that contain the matching tablenames.
 *
 * @exception BadQuery A server error occurred.
 */
 std::vector<std::string> GATS_DB_mysql::TableNames(const char *wild)
 {
 	std::vector<std::string> tablenames;
 	MYSQL_RES* result= ::mysql_list_tables(_connection, wild) ;
	const unsigned int num_fields = ::mysql_field_count(_connection);
	if( ! result ) {
		if( num_fields != 0 )  {
			THROW_GATS_EXCEPTION( BadQuery, ::mysql_error(_connection) ) ;
		}
	} else if(num_fields > 0) {
 		MYSQL_ROW row;
		while( (row = ::mysql_fetch_row(result))  ) {
			tablenames.push_back(std::string(row[0]) );
		}
	}
	::mysql_free_result(result);
	return tablenames;

 }
/**
 * Retrieve a listing of database names that matching the wildcard.  This
 * is similar to performing the following command   SHOW DATABASES [LIKE wild];
 * This method is a C++ wrapper to the \a mysql_list_dbs C API.
 *
 * @param[in] wild  A char* containing a regular expression what may contain the '\%' or '_' wildcard characters.
 * If NULL the result will be all the database names on the server.
 *
 * @retval vector<std::string> A vector of strings that contain the matching database names.
 *
 * @exception BadQuery A server error occurred.
 */
std::vector<std::string> GATS_DB_mysql::DBNames(const char *wild)
{
	std::vector<std::string> dbnames;
 	MYSQL_RES* result= ::mysql_list_dbs(_connection, wild) ;
	const unsigned int num_fields = ::mysql_field_count(_connection);
	if( ! result ) {
		if( num_fields != 0 )  {
			THROW_GATS_EXCEPTION( BadQuery, ::mysql_error(_connection) ) ;
		}
	} else if(num_fields > 0) {
 		MYSQL_ROW row;
		while( (row = ::mysql_fetch_row(result))  ) {
			dbnames.push_back(std::string(row[0]) );
		}
	}
	::mysql_free_result(result);
	return dbnames;
}
/**
 * Retrieve a listing of field names in the provided table that match the wildcard.  This
 * is similar to performing the following command   SHOW COLUMNS FROM tbl_name [LIKE wild];
 * This method is a C++ wrapper to the \a mysql_list_fields C API.
 *
 * @param[in] table A char* containing the table name for which you want it field names
 * @param[in] wild  A char* containing a regular expression what may contain the '\%' or '_' wildcard characters.
 * If NULL the result will be all the field names for the provided table name.
 *
 * @retval vector<std::string> A vector of strings that contain the matching field names.
 *
 * @exception BadQuery A server error occurred.
 */

std::vector<std::string> GATS_DB_mysql::FieldNames(const char* table, const char *wild)
{
	std::vector<std::string> fieldnames;
 	MYSQL_RES* result= ::mysql_list_fields(_connection, table,  wild) ;
	if( ! result ) {
		THROW_GATS_EXCEPTION( BadQuery, ::mysql_error(_connection) ) ;
	}
	MYSQL_FIELD* field;
	while( (field = ::mysql_fetch_field(result))  ) {
		fieldnames.push_back(std::string(field->name) );
	}
	::mysql_free_result(result);
	return fieldnames;
}
/**
 * This method will assemble an INSERT statement and send the results to the MySQL server.
 * The user provides a table name and a std::vector containing a std::pair.  The first item in the
 * pair is the fieldname, the second item is a ColData that contains the data you which to place in that
 * field.  If the ColData object notifies this method if the data needs quoted and escaped and this
 * method will perform that task as it assembles the SQL statement.  The insert statement will
 * have the following form.
 * @n
 * @n
 *   INSERT INTO table ( field1, field2, field3) VALUES (value1, value2, value3)
 * @n
 * @n
 *   Since INSERT does not return a result set, the return type of this method is void.
 *
 * @param[in] table The table name to INSERT data.
 * @param[in] fields A vector of string and colData pairs that contain the field name and the data to insert into
 * the field in the form of a ColData object.
 *
 * @exception BadQuery A server error occurred.
 */


void GATS_DB_mysql::InsertIntoTable(const char* table, const std::vector<std::pair<std::string, ColData> >& fields   )
{
	std::string sql("INSERT INTO ");
	sql += std::string(table);
	sql += std::string(" (");
	std::vector< std::pair< unsigned long int, boost::shared_array<char> > > values;
	if( ! fields.empty() ) {
		std::vector<std::pair<std::string, ColData> >::const_iterator fieldIter = fields.begin();
		for( ; fieldIter != fields.end() ; ++fieldIter) {
			sql += (*fieldIter).first;
			if( fieldIter < fields.end() -1) {
				sql += std::string(",");
			}
			ColData C = (*fieldIter).second;
			if( C.isNull() ) {
				boost::shared_array<char> t(new char[5]);
				strcpy(t.get(), "NULL");
				values.push_back(std::pair<unsigned long int, boost::shared_array<char> >(strlen(t.get()),t) );
			} else if( C.isEmpty() ) {
				boost::shared_array<char> t(new char[3]);
				strcpy(t.get(), "''");
				values.push_back(std::pair<unsigned long int, boost::shared_array<char> >(strlen(t.get()),t) );
			} else if( C.needsQuote() )  {
				const unsigned long int dlen = C.Length();
				boost::shared_array<char> tempval(new char[2*dlen + 1]);
				unsigned long int newlen = 2 + // room for quotations surrounding the C string
					::mysql_real_escape_string(_connection, tempval.get(), C.asC_str(), dlen);
				boost::shared_array<char> t(new char[newlen]);
				t[0] = '\'';
				memcpy(t.get()+1, tempval.get() , newlen-2 );
				t[newlen-1] = '\'';
				values.push_back(std::pair<unsigned long int, boost::shared_array<char> >(newlen,t) );
			} else {
				const unsigned long int dlen = C.Length();
				boost::shared_array<char> t(new char[dlen]);
				memcpy(t.get(), C.asC_str() , dlen );
				values.push_back(std::pair<unsigned long int, boost::shared_array<char> >(dlen,t) );
			}
		}  //for
	}
	sql += std::string(") VALUES (");
	unsigned long int length = sql.size() + 1;  //leave room for closing parenthesis
	boost::shared_array<char> SQLstatement;
	if(! values.empty() ) {
		length += (values.size() -1);  // for commas delimiting the values
		for(unsigned long i=0; i< values.size(); ++i) {  //determine how much array to allocate
			length += values[i].first;
		}
		SQLstatement.reset( new char[length+1]  );  //leave room for terminating NULL
		strcpy(SQLstatement.get()+length-1, ")" );
		char* end = SQLstatement.get();
		memcpy(end, sql.c_str() , sql.size()  );
		end += sql.size();
		for(unsigned long int i=0; i< values.size(); ++i) {
			memcpy(end, (values[i].second).get(), values[i].first);
			end += values[i].first;
			if(i < values.size() -1) {
				*end = ',';
				++end;
			}
		}
	} else {
		sql += std::string(")");
		SQLstatement.reset(new char[sql.size()+1] );  //leave room for terminating NULL
		strcpy(SQLstatement.get(), sql.c_str() );
	}
//	std::cout << SQLstatement.get() << std::endl;
	Query(SQLstatement.get(), length);
}
/**
 * This method will assemble an UPDATE statement and send the results to the MySQL server.
 * The user provides a table name and a std::vector containing a std::pair.  The first item in the
 * pair is the fieldname, the second item is a ColData that contains the data you which to place in that
 * field.  If the ColData object notifies this method if the data needs quoted and escaped and this
 * method will perform that task as it assembles the SQL statement.  The where clause is appended on the
 * end of the sql statement.  If the where clause was set to "WHERE field1 = 32' then the final sql statement
 * generated by this method would have the form.
 * @n
 * @n
 *   UPDATE table SET field1=value1, field2=value2, field3=value3 WHERE field1 = 32
 * @n
 * @n
 *   Since UPDATE does not return a result set, the return type of this method is void.
 *
 * @param[in] table The table name to UPDATE.
 * @param[in] fields A vector of string and colData pairs that contain the field name and the data to insert into
 * the field in the form of a ColData object.
 * @param[in] whereclause A C string containing the portion of the SQL statement beginning with "WHERE  blah ".  This string
 * is appended to the end of the UPDATE sql statement in an unaltered form.
 * @param[in] wherelen This is the byte length of the where string.  If non-zero the method will use the first wherelen
 * bytes of the where string you would need to provide this length of your where string contained binary data which
 * perhaps contained NULL characters in the middle of the string.  If kept the default or set to zero the method will
 * append up to the where terminating NULL.
 *
 *
 * @exception BadQuery A server error occurred.
 */

void GATS_DB_mysql::UpdateTable(const char* table,  const std::vector<std::pair<std::string, ColData> >& fields,
 	const char* whereclause, const unsigned long wherelen)
{
	if(fields.empty() ) {
		THROW_GATS_EXCEPTION( BadQuery, "UPDATE statement must contain at least one col_name = expression" ) ;
	}
	std::string sql("UPDATE ");
	sql += std::string(table);
	sql += std::string(" SET ");
	unsigned long int length = sql.size() ;
	std::vector< std::pair< unsigned long int, boost::shared_array<char> > > values;

	std::vector<std::pair<std::string, ColData> >::const_iterator fieldIter = fields.begin();
	for( ; fieldIter != fields.end() ; ++fieldIter) {
		length += ((*fieldIter).first).size()+1;   // Leave room for equal sign
		if( fieldIter < fields.end() -1) {
			++length;  // for the comma
		}
		ColData C = (*fieldIter).second;
		if( C.isNull() ) {
			boost::shared_array<char> t(new char[5]);
			strcpy(t.get(), "NULL");
			values.push_back(std::pair<unsigned long int, boost::shared_array<char> >(strlen(t.get()),t) );
		} else if( C.isEmpty() ) {
			boost::shared_array<char> t(new char[3]);
			strcpy(t.get(), "''");
			values.push_back(std::pair<unsigned long int, boost::shared_array<char> >(strlen(t.get()),t) );
		} else if( C.needsQuote() )  {
			const unsigned long int dlen = C.Length();
			boost::shared_array<char> tempval(new char[2*dlen + 1]);
			unsigned long int newlen = 2 + // room for quotations surrounding the C string
				::mysql_real_escape_string(_connection, tempval.get(), C.asC_str(), dlen);
			boost::shared_array<char> t(new char[newlen]);
			t[0] = '\'';
			memcpy(t.get()+1, tempval.get() , newlen-2 );
			t[newlen-1] = '\'';
			values.push_back(std::pair<unsigned long int, boost::shared_array<char> >(newlen,t) );
		} else {
			const unsigned long int dlen = C.Length();
			boost::shared_array<char> t(new char[dlen]);
			memcpy(t.get(), C.asC_str() , dlen );
			values.push_back(std::pair<unsigned long int, boost::shared_array<char> >(dlen,t) );
		}
		length += (values.back()).first;
	}  //for

	if(whereclause) {
		if(wherelen == 0) {
			length += strlen(whereclause);
		} else {
			length += wherelen;
		}
		++length;  //include a space before whereclause
	}

	boost::shared_array<char> SQLstatement(new char[length+1]); //include a terminating NULL
	char* end = SQLstatement.get()  ;
	memcpy(end, sql.c_str() , sql.size()  );
	end += sql.size();
	for(unsigned long int i=0; i< values.size(); ++i) {
		memcpy(end, (fields[i].first).c_str(), (fields[i].first).size() );
		end += (fields[i].first).size();
		*end ='=';
		++end;
		memcpy(end, (values[i].second).get(), values[i].first);
		end += values[i].first;
		if(i < values.size() -1) {
			*end = ',';
			++end;
		}
	}

	if(whereclause) {
		*end = ' ';
		++end;
		unsigned long l = (wherelen==0) ? strlen(whereclause) : wherelen;
		memcpy(end, whereclause, l);
		end += l;
	}
	*end = '\0';  //finally a terminating null;
	Query(SQLstatement.get(), length);
}

/**
 * This method returns the number of rows that were affected by the last sql statement sent to the server.
 * If the statement had been a DELETE, UPDATE, INSERT, REPLACE  the results from this method would be
 * the number of rows that were modified.
 * @warning This method may not return the expect value following a SELECT statement.  Intuitively this routine
 * should return the number of rows that were selected from a SELECT statement but in reality this routine
 * probably will not so you should alway check the size of the GATS_DB_Results containter for the number of rows returned.
 * This method provides a wrapper around the \a mysql_affected_rows C API.
 */
unsigned long long int GATS_DB_mysql::AffectedRows()
{
	return static_cast<unsigned long long int>(::mysql_affected_rows(_connection) );
}