src.dualinventive.com/mtinfo/secureserver/legacy/lib/include/di-util/database.h

/*
************************************************************************
**
**	Copyright (c) 2009..2010 by
**		Core|Vision B.V.
**		Cereslaan 10b
**		5384 VT  Heesch
**		The Netherlands
**
**	All Rights Reserved
**
************************************************************************
*/
/*
************************************************************************
**
**	Project name:   Dual Inventive: Utility Library
**	Filename:       database.h
**	Author:			Jack Weeland
**	Date:			October 27, 2009
**	File version:	1.00	of October 27, 2009
**
************************************************************************
*/
/*
************************************************************************
**
**	Database functions - definitions
**
**	When you use the database functions defined in this file, you must
**	link your executable with '-lmysql'.
**
************************************************************************
*/

#ifndef __DATABASE_H
#define	__DATABASE_H

#include <stddef.h>

/*
**	Data structures
*/

// handle for the database connection (as returned by 'db_init()')
typedef struct DATABASE *db_t;

// to determine the type of a field in the result set
typedef enum DATABASE_FIELD_TYPE
{
	result_type_uint,		// unsigned int
	result_type_uint64,		// unsigned long long _or_ my_ulonglong
	result_type_int,		// int
	result_type_float,		// float
	result_type_string,		// char*
	result_type_binary,		// unsigned char*
	result_type_datetime	// char*
} db_fieldtype_t;

// structure to store the results of a query
typedef struct DATABASE_RESULT {
	// result data from the database
	struct DATABASE_RESULT_DATA *data;
	
	// linked list
	struct DATABASE_RESULT *next;
} *db_result_t;

/*
**	Initialisation
*/

// initialise a 'db_t' structure, set the database name (direct,
//	using a string of the format "database:user:password@host:port"
//	or "database:user:password@socket", or using a configu-
//	ration file) and connect to it
// the database name can be set later, with 'db_set_database()',
//	_but_ then 'db_connect()' must be called too; if such scenario,
//	the argument for 'db_name' must be NULL
db_t db_init(const char *db_name);
void db_deinit(db_t db);
// set database name (direct or from a file) - only to be used when
//	'db' was initialized, but 'db_init()', with a NULL database
int db_set_database(db_t db, const char *db_name);
// connect to the database - see above
int db_connect(db_t db);

/*
**	Run a query and process its results
**
**	General operation:
**
**		db_result_t results;
**
**		db_query(db_handle, "SELECT string FROM whatever", &results);
**		do
**		{
**			int i;
**			
**			for( i = 0; i < db_num_rows(results); i++) {
**				printf("%d: %s\n", i, db_get_str(results, i, 0));
**			}
**		} while( db_next_result(&results) != NULL );
**
**	or
**
**		db_result_t results;
**		int i;
**
**		db_query(db_handle, "SELECT string FROM whatever", &results);
**			
**		for( i = 0; i < db_num_rows(results); i++) {
**			printf("%d: %s\n", i, db_get_str(results, i, 0));
**		}
**		db_destroy_result(&results);
**
**	and for simple queries that return one row
**
**		db_result_t results;
**
**		db_query(db_handle, "SELECT string FROM whatever WHERE id=1234", &results);
**			
**		printf("%s\n", db_get_str(results, 0, 0));
**		
**		db_destroy_result(&results);
**
*/

// run a query or a number of queries, separated by semicolons, and consume
//	the result data from the database server; returns the number of items
//	(should be equal to the number of actual queries) in the result set, or
//	'-1' if an error was detected - the result set may still be valid!
// the result set must deleted with 'db_destroy_result()' or by calling
//	'db_next_result()' repeatedly until it returns NULL
int db_query(db_t, const char *query, db_result_t *resultset);

// value of the last insert item with an auto incremented identifier
unsigned long long db_last_id(db_t);

/*
**	Operations on a result set of a query
*/

// get the next result from the result set; the current node is destroyed
//	the next result set is returned _and_ the pointer 'resultset' is
//	modified as well;
// returns NULL when there are no more results
db_result_t db_next_result(db_result_t *resultset);

// for simple queries, the result set can be destroyed with this function
int db_destroy_result(db_result_t *resultset);

// number of data rows in the result set
int db_num_rows(db_result_t resultset);

// number of fields per row
int db_num_fields(db_result_t resultset);

// get the index of a field by name
int db_get_field(db_result_t resultset, const char *field_name);

// get the name, type, length (size) and precision of a field
const char *db_field_name(db_result_t resultset, int field);
db_fieldtype_t db_field_type(db_result_t resultset, int field);
int db_field_length(db_result_t resultset, int field);
int db_field_precision(db_result_t resultset, int field);

// does a field have a NULL value?
int db_is_null(db_result_t resultset, int row, int field);

// get a field; the buffer must be large enough and it can be a pointer
//	to an integer ('bufsz' = sizeof(int)), string (char* or unsigned char*,
//	'bufsz' is the length of the buffer, floating point or 64-bit integer
//	(unsigned long long or my_ulonglong); the type of the field in the
//	database determines the type of 'buffer'
// generic function, returns the number of bytes returned in 'buffer'
// to enquire the buffer size needed, specify 'buffer' as NULL and 'bufsz'
//	as '0' and the funtion returns the number of bytes needed to store
//	the result.
int db_get_data(db_result_t resultset, int row, int field, void *buffer, size_t bufsz);
// functions to get an (unsigned) integer and a string; the returned
//	string is no longer valid after a call to 'db_next_result()'
int db_get_int(db_result_t resultset, int row, int field);
unsigned int db_get_uint(db_result_t resultset, int row, int field);
unsigned long long db_get_uint64(db_result_t resultset, int row, int field);
double db_get_float(db_result_t resultset, int row, int field);
const char *db_get_str(db_result_t resultset, int row, int field);
// get a date/time (time_t) from the result set
time_t db_get_date(db_result_t resultset, int row, int field);

// compare 'str' with the value in the database, minding the type of the field
// returns a negative number if the database field is less than 'str',
//	zero if they are equal and a positive number otherwise, like strcmp()
//	resp. strncmp() and memcmp()
// returns -1, 0 or +1 when the database data is an unsigned integer or a
//	floating point number to avoid overflow issues
// returns -1 on error
int db_cmp(db_result_t resultset, int row, int field, const char *str);
int db_ncmp(db_result_t resultset, int row, int field, const char *str, size_t len);

/*
**	Miscellaneous functions
*/

// errors from the database
int db_errno(db_t db);
const char *db_error(db_t db);
int db_error_str(db_t db, char *buffer, size_t bufsz);

// escape quotes (and other characters) in a string or a block of binary data
//	- the variant with a user supplied buffer returns the length of 'escaped'
//	- when using the variant with a static, please note that this buffer is 4096
//	  bytes in size (i.e. the length of 'str' must be less than 2048); supply
//	  a user buffer when this is not large enough
// the output buffer 'escaped' should be "2 * strlen(str) + 1"
// returns the number of characters in 'escaped' or '-1' on error (buffer too
//	small or invalid parameters)
// NOTE: the output buffer _must_ be large enough, or these functions will fail!
const char *db_escape(db_t db, const char *str);
int db_escape_str(db_t db, const char *str, char *escaped, size_t escaped_sz);
int db_escape_bin(db_t db, const void *data, size_t data_sz, char *escaped, size_t escaped_sz);

#endif /* __DATABASE_H */