From 75b324ae579f7ff3476980d367611f65a82796f8 Mon Sep 17 00:00:00 2001 From: =?utf8?q?Franti=C5=A1ek=20Dvo=C5=99=C3=A1k?= Date: Thu, 20 Jul 2006 13:02:33 +0000 Subject: [PATCH] Database module joined from LB and JP parts. Already tested, interface can change yet. --- org.glite.lb-utils.db/.cvsignore | 3 +- org.glite.lb-utils.db/Makefile | 117 ++++ org.glite.lb-utils.db/examples/db_test.c | 127 +++++ org.glite.lb-utils.db/interface/db.h | 311 +++++++++++ org.glite.lb-utils.db/src/db.c | 893 +++++++++++++++++++++++++++++++ 5 files changed, 1450 insertions(+), 1 deletion(-) create mode 100644 org.glite.lb-utils.db/Makefile create mode 100644 org.glite.lb-utils.db/examples/db_test.c create mode 100644 org.glite.lb-utils.db/interface/db.h create mode 100644 org.glite.lb-utils.db/src/db.c diff --git a/org.glite.lb-utils.db/.cvsignore b/org.glite.lb-utils.db/.cvsignore index 1df717b..69dd069 100755 --- a/org.glite.lb-utils.db/.cvsignore +++ b/org.glite.lb-utils.db/.cvsignore @@ -1,2 +1,3 @@ .project -.cdtproject \ No newline at end of file +.cdtproject +build diff --git a/org.glite.lb-utils.db/Makefile b/org.glite.lb-utils.db/Makefile new file mode 100644 index 0000000..5a19948 --- /dev/null +++ b/org.glite.lb-utils.db/Makefile @@ -0,0 +1,117 @@ +# defaults +top_srcdir=. +builddir=build +stagedir=. +distdir=. +globalprefix=glite +lbutilsprefix=lb-utils +package=glite-lb-utils-db +version=0.2.0 +PREFIX=/opt/glite + +glite_location=/opt/glite +mysql_prefix=/opt/mysql +mysql_version=4.1.11 +cppunit_prefix=/opt/cppunit +thrflavour=gcc32pthr +nothrflavour=gcc32 + +-include Makefile.inc +-include ../project/version.properties + +version=${module.version} + +CC=gcc + +VPATH=${top_srcdir}/interface:${top_srcdir}/src:${top_srcdir}/examples + +DEBUG:=-g -O0 -Wall + +CFLAGS:= \ + ${DEBUG} \ + -DVERSION=\"${version}\" \ + -I${stagedir}/include -I${top_srcdir}/src -I. \ + -I${top_srcdir}/interface \ + ${COVERAGE_FLAGS} \ + -I${mysql_prefix}/include -I${mysql_prefix}/include/mysql \ + -D_GNU_SOURCE + +ifdef LBS_DB_PROFILE + CFLAGS:=${CFLAGS} -DLBS_DB_PROFILE +endif + +TEST_LIBS:=-L${cppunit_prefix}/lib -lcppunit +TEST_INC:=-I${cppunit_prefix}/include + +COMPILE:=libtool --mode=compile ${CC} ${CFLAGS} +LINK:=libtool --mode=link ${CC} -rpath ${stagedir}/lib ${LDFLAGS} +INSTALL:=libtool --mode=install install + +ifneq (${mysql_prefix},/usr) + ifeq ($(shell echo ${mysql_version} | cut -d. -f1,2),4.1) + mysqlib := -L${mysql_prefix}/lib/mysql + else + mysqlib := -L${mysql_prefix}/lib + endif +endif + +EXT_LIBS:=${mysqlib} -lmysqlclient +OBJS:=db.o +HDRS:=db.h +LOBJS:=${OBJS:.o=.lo} + +libglite_lbutils_db.la: ${LOBJS} + ${LINK} -o $@ $< ${EXT_LIBS} + +db_test: db_test.lo + ${LINK} -o $@ $< -lglite_lbutils_db + +default all: compile + +compile: libglite_lbutils_db.la + +check: + -echo No checks here yet. + +test_coverage: + -mkdir coverage + cd coverage && $(MAKE) -f ../Makefile top_srcdir=../../ COVERAGE_FLAGS="-fprofile-arcs -ftest-coverage" check + cd coverage && for i in `echo ${OBJS} | tr ' ' '\012' | sort -u`; do gcov $$i ; done + +examples: db_test + +doc: + doxygen C.dox + +stage: compile + $(MAKE) install PREFIX=${stagedir} DOSTAGE=yes + +dist: distsrc distbin + +distsrc: + mkdir -p ${top_srcdir}/${package}-${version} + cd ${top_srcdir} && GLOBIGNORE="${package}-${version}" && cp -Rf * ${package}-${version} + cd ${top_srcdir} && tar -czf ${distdir}/${package}-${version}_src.tar.gz --exclude-from=project/tar_exclude ${package}-${version} + rm -rf ${top_srcdir}/${package}-${version} + +distbin: + $(MAKE) install PREFIX=`pwd`/tmpbuilddir${stagedir} + save_dir=`pwd`; cd tmpbuilddir${stagedir} && tar -czf $$save_dir/${top_srcdir}/${distdir}/${package}-${version}_bin.tar.gz *; cd $$save_dir + rm -rf tmpbuilddir + +install: + -mkdir -p ${PREFIX}/lib + -mkdir -p ${PREFIX}/include/${globalprefix}/${lbutilsprefix} +# ${INSTALL} -m 644 ${top_srcdir}/LICENSE ${PREFIX}/share/doc/${package}-${version} + ${INSTALL} -m 755 "libglite_lbutils_db.la" "${PREFIX}/lib/glite-lbutils-db.la"; \ + ${INSTALL} -m 644 ${HDRS} ${PREFIX}/include/${globalprefix}/${lbutilsprefix} + +clean: + +%.o %.lo: %.c + ${COMPILE} -c $< + +db.o db.lo: db.c db.h +db_test: libglite_lbutils_db.la db.h db_test.c + +.PHONY: default all compile check examples doc stage dist distsrc distbin install clean test_coverage diff --git a/org.glite.lb-utils.db/examples/db_test.c b/org.glite.lb-utils.db/examples/db_test.c new file mode 100644 index 0000000..50bc344 --- /dev/null +++ b/org.glite.lb-utils.db/examples/db_test.c @@ -0,0 +1,127 @@ +#include +#include +#include + +#include "db.h" + + +#define dprintf(ARGS) { printf("%s: ", name); printf ARGS; } + + +int main(int argn, char *argv[]) { + char *name; + const char *cs; + glite_lbu_DBContext ctx; + glite_lbu_Statement stmt; + int caps; + + if ((name = strrchr(argv[0], '/')) != NULL) name++; + else name = argv[0]; + if ((cs = getenv("DB")) == NULL) cs = "jpis/@localhost:jpis1"; + + // init + dprintf(("connecting to %s...\n", cs)); + if (glite_lbu_DBConnect(&ctx, cs, 0) != 0) goto fail; + if ((caps = glite_lbu_DBQueryCaps(ctx)) == -1) goto failctx; + if ((caps & GLITE_LBU_DB_CAP_PREPARED) == 0) { + dprintf(("can't do prepared commands, exiting.")); + goto failctx; + } + glite_lbu_DBSetCaps(ctx, caps); + dprintf(("capabilities: %d\n", caps)); + dprintf(("\n")); + + // "trio" queries +{ + int nr, i; + char **res; + + dprintf(("selecting...\n")); + if ((glite_lbu_ExecSQL(ctx, "SELECT uniqueid, feedid, state, source, condition FROM feeds", &stmt)) == -1) goto failctx; + + dprintf(("fetching...\n")); + res = calloc(6, sizeof(char *)); + while ((nr = glite_lbu_FetchRow(stmt, 5, NULL, res)) > 0) { + dprintf(("Result: n=%d, res=%p\n", nr, res)); + i = 0; + if (res) { + dprintf((" uniqueid = %s\n", res[0])); + dprintf((" feedid = %s\n", res[1])); + dprintf((" state = %s\n", res[2])); + dprintf((" source = %s\n", res[3])); + dprintf((" condition = %s\n", res[4])); + while(i < nr) {free(res[i]);i++;} + } + } + free(res); + dprintf(("closing stmt...\n")); + dprintf(("\n")); + glite_lbu_FreeStmt(stmt); +} + + // "param" queries +{ + int nr, i; + char **res; + long int param_state; + + dprintf(("preparing...\n")); + if ((glite_lbu_PrepareStmt(ctx, "SELECT feedid, state, source, condition FROM feeds WHERE state = ?", &stmt)) != 0) goto failctx; + + param_state = 1; + dprintf(("executing state %ld...\n", param_state)); + if (glite_lbu_ExecStmt(stmt, 1, GLITE_LBU_DB_TYPE_INT, param_state) == -1) goto failstmt; + dprintf(("fetching...\n")); + res = calloc(5, sizeof(char *)); + while ((nr = glite_lbu_FetchRow(stmt, 4, NULL, res)) > 0) { + dprintf(("Result: n=%d, res=%p\n", nr, res)); + i = 0; + if (res) { + dprintf((" feedid=%s\n", res[0])); + dprintf((" state=%s\n", res[1])); + dprintf((" source=%s\n", res[2])); + dprintf((" condition=%s\n", res[3])); + while(i < nr) {free(res[i]);i++;} + } + } + free(res); + dprintf(("\n")); + + param_state = 3; + dprintf(("executing state %ld...\n", param_state)); + if (glite_lbu_ExecStmt(stmt, 1, GLITE_LBU_DB_TYPE_INT, param_state) == -1) goto failstmt; + dprintf(("fetching...\n")); + res = calloc(5, sizeof(char *)); + while ((nr = glite_lbu_FetchRow(stmt, 4, NULL, res)) > 0) { + dprintf(("Result: n=%d, res=%p\n", nr, res)); + i = 0; + if (res) { + dprintf((" feedid=%s\n", res[0])); + dprintf((" state=%s\n", res[1])); + dprintf((" source=%s\n", res[2])); + dprintf((" condition=%s\n", res[3])); + while(i < nr) {free(res[i]);i++;} + } + } + free(res); + dprintf(("\n")); + + dprintf(("closing stmt...\n")); + glite_lbu_FreeStmt(stmt); + dprintf(("\n")); +} + + dprintf(("closing...\n")); + glite_lbu_DBClose(ctx); + return 0; + +failstmt: + printf("closing stmt...\n"); + glite_lbu_FreeStmt(stmt); +failctx: + dprintf(("closing...\n")); + glite_lbu_DBClose(ctx); +fail: + dprintf(("failed\n")); + return 1; +} diff --git a/org.glite.lb-utils.db/interface/db.h b/org.glite.lb-utils.db/interface/db.h new file mode 100644 index 0000000..3640dc9 --- /dev/null +++ b/org.glite.lb-utils.db/interface/db.h @@ -0,0 +1,311 @@ +#ifndef GLITE_LBU_DB_H +#define GLITE_LBU_DB_H + +#ident "$Header$" + +#ifdef __cplusplus +extern "C" { +#endif + + +/** + * \file db.h + * \defgroup database Database module + * + * Database modul module API (LB & JP Utils). + * + * There are two ways to access DB here: + * - simple: + * + * SQL commands as single string. All values are incorporated in the SQL command strings. Proper escaping is required. + * - enhanced: + * + * Prepared SQL commands with separated parameters, functions PrepareStmt() and ExecStmt(). All values are delivered in separated buffers. Its faster for multiple using and more secure. + * @{ + */ + + +/** + * Enable transaction support if available. + * + * With disabled transaction can be used transaction functions, they are just ignored. + */ +#define GLITE_LBU_DB_CAP_TRANSACTIONS 1 + +/** + * Check prepared parameters support. + */ +#define GLITE_LBU_DB_CAP_PREPARED 2 + +/** + * Check for getting indexes support. + * + * Needed for QueryIndices call. + */ +#define GLITE_LBU_DB_CAP_INDEX 4 + + +/** + * Database connection context. + */ +typedef struct glite_lbu_DBContext_s *glite_lbu_DBContext; + + +/** + * Prepared statement, used for SQL statement with parameters. + */ +typedef struct glite_lbu_Statement_s *glite_lbu_Statement; + + +/** + * Structure holds date for multi-rows insert. + */ +typedef struct glite_lbu_bufInsert_s glite_lbu_bufInsert; + + + +/** + * All types of parameteres, they match to the SQL types. + */ +typedef enum { + GLITE_LBU_DB_TYPE_NULL = 0, + GLITE_LBU_DB_TYPE_TINYINT = 1, + GLITE_LBU_DB_TYPE_INT = 2, + GLITE_LBU_DB_TYPE_TINYBLOB = 3, + GLITE_LBU_DB_TYPE_TINYTEXT = 4, + GLITE_LBU_DB_TYPE_BLOB = 5, + GLITE_LBU_DB_TYPE_TEXT = 6, + GLITE_LBU_DB_TYPE_MEDIUMBLOB = 7, + GLITE_LBU_DB_TYPE_MEDIUMTEXT = 8, + GLITE_LBU_DB_TYPE_LONGBLOB = 9, + GLITE_LBU_DB_TYPE_LONGTEXT = 10, + GLITE_LBU_DB_TYPE_VARCHAR = 11, + GLITE_LBU_DB_TYPE_CHAR = 12, + GLITE_LBU_DB_TYPE_DATE = 13, + GLITE_LBU_DB_TYPE_TIME = 14, + GLITE_LBU_DB_TYPE_DATETIME = 15, + GLITE_LBU_DB_TYPE_TIMESTAMP = 16, + GLITE_LBU_DB_TYPE_LAST = 17 +} glite_lbu_DBType; + + + +/** + * Create the context and connect to the database. + * + * \param[out] ctx context to work with + * \param[in] cs connect string user/password\@host:database + * \param[in] caps capabilities to use, should be found out by QueryCaps(), + * 0 for initial connect + * + * \return error code, 0 = OK + */ +int glite_lbu_DBConnect(glite_lbu_DBContext *ctx, const char *cs, int caps); + + +/** + * Close the connection and free the context. + * + * \param[in,out] ctx context to work with + */ +void glite_lbu_DBClose(glite_lbu_DBContext ctx); + + +/** + * Check database version and capabilities. + * + * \param[in,out] ctx context to work with + * + * \return capabilities + * \retval -1 error occured + */ +int glite_lbu_DBQueryCaps(glite_lbu_DBContext ctx); + + +/** + * Set the database capabilities on already initialized context. + * + * It should be find out by DBQueryCaps() first. + * + * \param[in,out] ctx context to work with + * \param[in] caps capabilities to use, should be found out by QueryCaps() + */ +void glite_lbu_DBSetCaps(glite_lbu_DBContext ctx, int caps); + + +/** + * Start transaction. + */ +int glite_lbu_Transaction(glite_lbu_DBContext ctx); + + +/** + * Commit (end) transaction. + */ +int glite_lbu_Commit(glite_lbu_DBContext ctx); + + +/** + * Cancel transaction. + */ +int glite_lbu_Rollback(glite_lbu_DBContext ctx); + + +/** + * \param[in,out] stmt executed SQL statement + * \param[in] n number of items for sure there is enough space in lengths and results + * \param[out] lengths array with lengths (good for data blobs), may be NULL + * \param[out] results array with results, all items are allocated + * + * \retval >0 number of fields of the retrieved row + * \retval 0 no more rows + * \retval -1 error + */ +int glite_lbu_FetchRow(glite_lbu_Statement stmt, unsigned int n, unsigned long *lengths, char **results); + + +/** + * Free the statement structure and destroy its parameters. + * + * \param[in,out] stmt statement + */ +void glite_lbu_FreeStmt(glite_lbu_Statement stmt); + + +/** + * Parse and execute one simple SQL statement. + * All values are incorporated int the SQL command string. + * + * \param[in,out] ctx context to work with + * \param[in] cmd SQL command + * \param[out] stmt statement handle with results (makes sense for selects only) + * + * \return number of rows selected, created or affected by update, -1 on error + */ +int glite_lbu_ExecSQL(glite_lbu_DBContext ctx, const char *cmd, glite_lbu_Statement *stmt); + + +/** + * Query for column names of the statement. + * + * It work only for simple API, so only after ExecSQL(). + * + * \param[in,out] stmt the statement handle + * \param[out] cols result array of names + * + * \return error code + */ +int glite_lbu_QueryColumns(glite_lbu_Statement stmt, char **cols); + + +/** + * Retrieve column names of a query simple SQL statement. + * + * Not implemented for now. + * + * \param[in,out] ctx context to work with + * \param[in] table table name + * \param[out] names result set column names. Expects allocated array. + * + * \return 0 if OK, nonzero on error + */ +int glite_lbu_QueryIndices(glite_lbu_DBContext ctx, const char *table, char ***names); + + +/** + * Convert time_t into database-specific time string. + * + * The result string can be used directly in SQL commands. + * + * \param[in] t the converted time + * \param[out] str result allocated string + */ +void glite_lbu_TimeToDB(time_t t, char **str); + + +/** + * Convert database-specific time string to time_t. + * + * String is expected in database for (ISO format). + * + * \param[in] str the converted string + * \return result time + */ +time_t glite_lbu_DBToTime(const char *str); + + +/** + * Init data structure for buffered insert + * + * takes table_name and columns string for future multirow insert + * when insert string oversize size_limit or number of rows to be inserted + * overcome record_limit, the real insert is triggered + */ +int glite_lbu_bufferedInsertInit(glite_lbu_DBContext ctx, glite_lbu_bufInsert *bi, void *mysql, const char *table_name, long size_limit, long record_limit, const char * columns); + + +/** + * adds row of n values into n columns into an insert buffer + * if num. of rows or size of data oversteps the limits, real + * multi-row insert is done + */ +int glite_lbu_bufferedInsert(glite_lbu_bufInsert *bi, const char *row); + + +/** + * Flush buffered data and free bi structure. + */ +int glite_lbu_bufferedInsertClose(glite_lbu_bufInsert *bi); + + +/** + * Prepare the SQL statement. Use glite_lbu_FreeStmt() to free it. + * + * \param[in,out] ctx context to work with + * \param[in] sql SQL command + * \param[out] stmt returned SQL statement + * + * \return error code + */ +int glite_lbu_PrepareStmt(glite_lbu_DBContext ctx, const char *sql, glite_lbu_Statement *stmt); + + +/** + * Execute prepared SQL statement. + * + * \param[in,out] stmt SQL statement + * \param[in] n number of items + * + * Variable parameters (n-times): + * + * always: + * + * \param type DB item type + * + * then one of them: + * + * \param GLITE_LBU_DB_TYPE_TINYINT int c + * \param GLITE_LBU_DB_TYPE_INT long int i + * \param GLITE_LBU_DB_TYPE_...BLOB/TEXT void *b, unsigned long len + * \param GLITE_LBU_DB_TYPE_[VAR]CHAR char *str + * \param GLITE_LBU_DB_TYPE_DATE/TIME/DATETIME time_t t + * \param GLITE_LBU_DB_TYPE_TIMESTAMP time_t t + * \param GLITE_LBU_DB_TYPE_NULL - + * + * \return number of affected rows, -1 on error + */ +int glite_lbu_ExecStmt(glite_lbu_Statement stmt, int n, ...); + + +/** + * @} database group + */ + + +#ifdef __cplusplus +} +#endif + +#else +#error Already included +#endif diff --git a/org.glite.lb-utils.db/src/db.c b/org.glite.lb-utils.db/src/db.c new file mode 100644 index 0000000..829c9e0 --- /dev/null +++ b/org.glite.lb-utils.db/src/db.c @@ -0,0 +1,893 @@ +#ident "$Header$" + +#include +#include +#include +#include +#include +#include +#include +#include +#include + +#include +#include +#include + +#warning FIXME: change to lb-utils +#include "glite/lb/trio.h" +#include "db.h" + + +#define GLITE_LBU_MYSQL_INDEX_VERSION 40001 +#define GLITE_LBU_MYSQL_PREPARED_VERSION 40102 +#define BUF_INSERT_ROW_ALLOC_BLOCK 1000 +#define GLITE_LBU_DEFAULT_RESULT_BUFFER_LENGTH 1024 + + +#define CLR_ERR(CTX) lbu_clrerr((CTX)) +#define ERR(CTX, CODE, DESC) lbu_err((CTX), (CODE), (DESC), __FUNCTION__, __LINE__) +#define STATUS(CTX) ((CTX)->code) +#define MY_ERR(CTX) myerr((CTX), __FUNCTION__, __LINE__) +#define MY_ERRSTMT(STMT) myerrstmt((STMT), __FUNCTION__, __LINE__) +#define MY_ISOKSTMT(STMT, RETRY) myisokstmt((STMT), __FUNCTION__, __LINE__, (RETRY)) + +#define USE_TRANS(CTX) ((CTX->caps & GLITE_LBU_DB_CAP_TRANSACTIONS) != 0) + + + +struct glite_lbu_DBContext_s { + MYSQL *mysql; + const char *cs; + int caps; + int code; + char *text; +}; + + +struct glite_lbu_Statement_s { + glite_lbu_DBContext ctx; + + /* for simple commands */ + MYSQL_RES *result; + + /* for prepared commands */ + MYSQL_STMT *stmt; + unsigned long nrfields; +}; + + +struct glite_lbu_bufInsert_s { + glite_lbu_DBContext ctx; + char *table_name; + char *columns; /* names of columns to be inserted into + * (values separated with commas) */ + char **rows; /* each row hold string of one row to be inserted + * (values separated with commas) */ + long rec_num, /* actual number of rows in structure */ + rec_size; /* approx. size of a real insert string */ + long size_limit, /* size and # of records limit which trigger */ + record_limit; /* real insert; zero means unlimitted */ +}; + + +/* + * mapping glite DB types to mysql types + */ +int glite_type_to_mysql[] = { + MYSQL_TYPE_NULL, + MYSQL_TYPE_TINY, + MYSQL_TYPE_LONG, + MYSQL_TYPE_TINY_BLOB, + MYSQL_TYPE_TINY_BLOB, + MYSQL_TYPE_BLOB, + MYSQL_TYPE_BLOB, + MYSQL_TYPE_MEDIUM_BLOB, + MYSQL_TYPE_MEDIUM_BLOB, + MYSQL_TYPE_LONG_BLOB, + MYSQL_TYPE_LONG_BLOB, + MYSQL_TYPE_VAR_STRING, + MYSQL_TYPE_STRING, + MYSQL_TYPE_DATE, + MYSQL_TYPE_TIME, + MYSQL_TYPE_DATETIME, + MYSQL_TYPE_TIMESTAMP, +}; + + + +static int lbu_clrerr(glite_lbu_DBContext ctx); +static int lbu_err(glite_lbu_DBContext ctx, int code, const char *text, const char *func, int line); +static int myerr(glite_lbu_DBContext ctx, const char *source, int line); +static int myerrstmt(glite_lbu_Statement stmt, const char *source, int line); +static int myisokstmt(glite_lbu_Statement stmt, const char *source, int line, int *retry); +static int db_connect(glite_lbu_DBContext ctx, const char *cs, int caps, MYSQL **mysql); +static void db_close(MYSQL *mysql); +static int transaction_test(glite_lbu_DBContext ctx, MYSQL *m2, int *have_transactions); +static int FetchRowSimple(glite_lbu_DBContext ctx, MYSQL_RES *result, unsigned long *lengths, char **results); +static int FetchRowPrepared(glite_lbu_DBContext ctx, glite_lbu_Statement stmt, unsigned int n, unsigned long *lengths, char **results); +void set_time(MYSQL_TIME *mtime, const time_t time); +time_t get_time(const MYSQL_TIME *mtime); + + +/* ---- common ---- */ + + +int glite_lbu_DBConnect(glite_lbu_DBContext *ctx, const char *cs, int caps) { + int err; + + *ctx = calloc(1, sizeof **ctx); + if (db_connect(*ctx, cs, caps, &(*ctx)->mysql) != 0) { + err = STATUS(*ctx); + glite_lbu_DBClose(*ctx); + *ctx = NULL; + return err; + } + return 0; +} + + +void glite_lbu_DBClose(glite_lbu_DBContext ctx) { + db_close(ctx->mysql); + free(ctx->text); + free(ctx); +} + + +int glite_lbu_DBQueryCaps(glite_lbu_DBContext ctx) { + MYSQL *m = ctx->mysql; + MYSQL *m2; + int major,minor,sub,version,caps,have_transactions=0; + const char *ver_s; + + caps = 0; + + ver_s = mysql_get_server_info(m); + if (!ver_s || 3 != sscanf(ver_s,"%d.%d.%d",&major,&minor,&sub)) + return ERR(ctx, EINVAL, "problem retreiving MySQL version"); + version = 10000*major + 100*minor + sub; + + if (version >= GLITE_LBU_MYSQL_INDEX_VERSION) caps |= GLITE_LBU_DB_CAP_INDEX; + if (version >= GLITE_LBU_MYSQL_PREPARED_VERSION) caps |= GLITE_LBU_DB_CAP_PREPARED; + + CLR_ERR(ctx); + + if (db_connect(ctx, ctx->cs, 0, &m2) == 0) { + transaction_test(ctx, m2, &have_transactions); + db_close(m2); + } + if (have_transactions) caps |= GLITE_LBU_DB_CAP_TRANSACTIONS; + + if (STATUS(ctx) == 0) return caps; + else return -1; +} + + +void glite_lbu_DBSetCaps(glite_lbu_DBContext ctx, int caps) { + ctx->caps = caps; +} + + +int glite_lbu_Transaction(glite_lbu_DBContext ctx) { + if (USE_TRANS(ctx)) { + if (glite_lbu_ExecSQL(ctx, "SET AUTOCOMMIT=0", NULL) < 0) goto err; + if (glite_lbu_ExecSQL(ctx, "BEGIN", NULL) < 0) goto err; + } +err: + return STATUS(ctx); +} + + +int glite_lbu_Commit(glite_lbu_DBContext ctx) { + if (USE_TRANS(ctx)) { + if (glite_lbu_ExecSQL(ctx, "COMMIT", NULL) < 0) goto err; + if (glite_lbu_ExecSQL(ctx, "SET AUTOCOMMIT=1", NULL) < 0) goto err; + } +err: + return STATUS(ctx); +} + + +int glite_lbu_Rollback(glite_lbu_DBContext ctx) { + if (USE_TRANS(ctx)) { + if (glite_lbu_ExecSQL(ctx, "ROLLBACK", NULL) < 0) goto err; + if (glite_lbu_ExecSQL(ctx, "SET AUTOCOMMIT=1", NULL) < 0) goto err; + } +err: + return STATUS(ctx); +} + + +int glite_lbu_FetchRow(glite_lbu_Statement stmt, unsigned int n, unsigned long *lengths, char **results) { + if (stmt->result) return FetchRowSimple(stmt->ctx, stmt->result, lengths, results); + else return FetchRowPrepared(stmt->ctx, stmt, n, lengths, results); +} + + +void glite_lbu_FreeStmt(glite_lbu_Statement stmt) { + if (stmt) { + if (stmt->result) mysql_free_result(stmt->result); + if (stmt->stmt) mysql_stmt_close(stmt->stmt); + free(stmt); + } +} + + +#warning TODO: glite_lbu_QueryIndices not implemented +/*int glite_lbu_QueryIndices(glite_lbu_DBContext ctx, const char *table, char **names) { + return 0; +}*/ + + +/* ---- simple ---- */ + +int glite_lbu_ExecSQL(glite_lbu_DBContext ctx, const char *cmd, glite_lbu_Statement *stmt) { + int merr; + int retry_nr = 0; + int do_reconnect = 0; +#ifdef LBS_DB_PROFILE + struct timeval start,end; + int pid; + + static struct timeval sum = { + tv_sec: 0, + tv_usec: 0 + }; +#endif + + CLR_ERR(ctx); + + if (stmt) *stmt = NULL; + +#ifdef LBS_DB_PROFILE + gettimeofday(&start,NULL); +#endif + + while (retry_nr == 0 || do_reconnect) { + do_reconnect = 0; + if (mysql_query(ctx->mysql, cmd)) { + /* error occured */ + switch (merr = mysql_errno(ctx->mysql)) { + case 0: + break; + case ER_DUP_ENTRY: + ERR(ctx, EEXIST, mysql_error(ctx->mysql)); + return -1; + break; + case CR_SERVER_LOST: + if (retry_nr <= 0) + do_reconnect = 1; + break; + default: + MY_ERR(ctx); + return -1; + break; + } + } + retry_nr++; + } + + if (stmt) { + *stmt = calloc(1, sizeof(**stmt)); + if (!*stmt) { + ERR(ctx, ENOMEM, NULL); + return -1; + } + (**stmt).ctx = ctx; + (**stmt).result = mysql_store_result(ctx->mysql); + if (!(**stmt).result) { + if (mysql_errno(ctx->mysql)) { + MY_ERR(ctx); + return -1; + } + } + } else { + MYSQL_RES *r = mysql_store_result(ctx->mysql); + mysql_free_result(r); + } +#ifdef LBS_DB_PROFILE + pid = getpid(); + gettimeofday(&end,NULL); + end.tv_usec -= start.tv_usec; + end.tv_sec -= start.tv_sec; + if (end.tv_usec < 0) { end.tv_sec--; end.tv_usec += 1000000; } + + sum.tv_usec += end.tv_usec; + sum.tv_sec += end.tv_sec + sum.tv_usec / 1000000; + sum.tv_usec -= 1000000 * (sum.tv_usec / 1000000); + fprintf(stderr,"[%d] %s\n[%d] %3ld.%06ld (sum: %3ld.%06ld)\n",pid,txt,pid,end.tv_sec,end.tv_usec,sum.tv_sec,sum.tv_usec); +#endif + + return mysql_affected_rows(ctx->mysql); +} + + +int glite_lbu_QueryColumns(glite_lbu_Statement stmt, char **cols) +{ + int i = 0; + MYSQL_FIELD *f; + + if (!stmt->result) return ERR(stmt->ctx, EINVAL, "QueryColums work only in simple API"); + while ((f = mysql_fetch_field(stmt->result))) cols[i++] = f->name; + return i == 0; +} + + +void glite_lbu_TimeToDB(time_t t, char **str) { + struct tm *tm = gmtime(&t); + + asprintf(str,"'%4d-%02d-%02d %02d:%02d:%02d'",tm->tm_year+1900,tm->tm_mon+1, + tm->tm_mday,tm->tm_hour,tm->tm_min,tm->tm_sec); +} + + +time_t glite_lbu_DBToTime(const char *str) { + struct tm tm; + + memset(&tm,0,sizeof(tm)); + setenv("TZ","UTC",1); tzset(); + sscanf(str,"%4d-%02d-%02d %02d:%02d:%02d", + &tm.tm_year,&tm.tm_mon,&tm.tm_mday, + &tm.tm_hour,&tm.tm_min,&tm.tm_sec); + tm.tm_year -= 1900; + tm.tm_mon--; + + return mktime(&tm); +} + +/* ---- prepared --- */ + +int glite_lbu_PrepareStmt(glite_lbu_DBContext ctx, const char *sql, glite_lbu_Statement *stmt) { + int ret, retry; + MYSQL_RES *meta; + + // init + *stmt = calloc(1, sizeof(**stmt)); + (*stmt)->ctx = ctx; + + // create the SQL command + if (((*stmt)->stmt = mysql_stmt_init(ctx->mysql)) == NULL) + return MY_ERRSTMT(*stmt); + + // prepare the SQL command + retry = 1; + do { + mysql_stmt_prepare((*stmt)->stmt, sql, strlen(sql)); + ret = MY_ISOKSTMT(*stmt, &retry); + } while (ret == 0); + if (ret == -1) goto failed; + + // number of fields + if ((meta = mysql_stmt_result_metadata((*stmt)->stmt)) == NULL) { + MY_ERRSTMT(*stmt); + goto failed; + } + (*stmt)->nrfields = mysql_num_fields(meta); + mysql_free_result(meta); + + return CLR_ERR(ctx); + +failed: + glite_lbu_FreeStmt(*stmt); + return STATUS(ctx); +} + + +int glite_lbu_ExecStmt(glite_lbu_Statement stmt, int n, ...) { + int i; + va_list ap; + glite_lbu_DBType type; + char *pchar; + long int *plint; + MYSQL_TIME *ptime; + glite_lbu_DBContext ctx; + int ret, retry; + MYSQL_BIND *binds = NULL; + void **data = NULL; + unsigned long *lens; + + // gather parameters + if (n) { + binds = calloc(n, sizeof(MYSQL_BIND)); + data = calloc(n, sizeof(void *)); + lens = calloc(n, sizeof(unsigned long *)); + } + va_start(ap, n); + for (i = 0; i < n; i++) { + type = va_arg(ap, glite_lbu_DBType); + switch (type) { + case GLITE_LBU_DB_TYPE_TINYINT: + pchar = binds[i].buffer = data[i] = malloc(sizeof(char)); + *pchar = va_arg(ap, int); + break; + + case GLITE_LBU_DB_TYPE_INT: + plint = binds[i].buffer = data[i] = malloc(sizeof(long int)); + *plint = va_arg(ap, long int); + break; + + case GLITE_LBU_DB_TYPE_TINYBLOB: + case GLITE_LBU_DB_TYPE_TINYTEXT: + case GLITE_LBU_DB_TYPE_BLOB: + case GLITE_LBU_DB_TYPE_TEXT: + case GLITE_LBU_DB_TYPE_MEDIUMBLOB: + case GLITE_LBU_DB_TYPE_MEDIUMTEXT: + case GLITE_LBU_DB_TYPE_LONGBLOB: + case GLITE_LBU_DB_TYPE_LONGTEXT: + binds[i].buffer = va_arg(ap, void *); + binds[i].length = &lens[i]; + lens[i] = va_arg(ap, unsigned long); + break; + + case GLITE_LBU_DB_TYPE_VARCHAR: + case GLITE_LBU_DB_TYPE_CHAR: + binds[i].buffer = va_arg(ap, char *); + binds[i].length = &lens[i]; + lens[i] = binds[i].buffer ? strlen((char *)binds[i].buffer) : 0; + break; + + case GLITE_LBU_DB_TYPE_DATE: + case GLITE_LBU_DB_TYPE_TIME: + case GLITE_LBU_DB_TYPE_DATETIME: + case GLITE_LBU_DB_TYPE_TIMESTAMP: + ptime = binds[i].buffer = data[i] = malloc(sizeof(MYSQL_TIME)); + set_time(ptime, va_arg(ap, time_t)); + break; + + case GLITE_LBU_DB_TYPE_NULL: + break; + + default: + assert("unimplemented parameter assign" == NULL); + break; + } + binds[i].buffer_type = glite_type_to_mysql[type]; + } + va_end(ap); + + // bind parameters + if (mysql_stmt_bind_param(stmt->stmt, binds) != 0) { + MY_ERRSTMT(stmt); + goto failed; + } + + // run + ctx = stmt->ctx; + retry = 1; + do { + mysql_stmt_execute(stmt->stmt); + ret = MY_ISOKSTMT(stmt, &retry); + } while (ret == 0); + if (ret == -1) goto failed; + + // result + retry = 1; + do { + mysql_stmt_store_result(stmt->stmt); + ret = MY_ISOKSTMT(stmt, &retry); + } while (ret == 0); + if (ret == -1) goto failed; + + // free params + for (i = 0; i < n; i++) free(data[i]); + free(data); + free(binds); + free(lens); + CLR_ERR(ctx); + return mysql_stmt_affected_rows(stmt->stmt); + +failed: + for (i = 0; i < n; i++) free(data[i]); + free(data); + free(binds); + free(lens); + return -1; +} + + +int glite_lbu_bufferedInsertInit(glite_lbu_DBContext ctx, glite_lbu_bufInsert *bi, void *mysql, const char *table_name, long size_limit, long record_limit, const char *columns) +{ + bi->ctx = ctx; + bi->table_name = strdup(table_name); + bi->columns = strdup(columns); + bi->rec_num = 0; + bi->rec_size = 0; + bi->rows = calloc(record_limit, sizeof(*(bi->rows)) ); + bi->size_limit = size_limit; + bi->record_limit = record_limit; + + return STATUS(bi->ctx); +} + + +static int flush_bufferd_insert(glite_lbu_bufInsert *bi) +{ + char *stmt, *vals, *temp; + long i; + + + if (!bi->rec_num) + return STATUS(bi->ctx); + + asprintf(&vals,"(%s)", bi->rows[0]); + for (i=1; i < bi->rec_num; i++) { + // XXX: use string add (preallocated memory) + asprintf(&temp,"%s,(%s)", vals, bi->rows[i]); + free(vals); vals = temp; temp = NULL; + free(bi->rows[i]); + bi->rows[i] = NULL; + } + + trio_asprintf(&stmt, "insert into %|Ss(%|Ss) values %s;", + bi->table_name, bi->columns, vals); + + if (glite_lbu_ExecSQL(bi->ctx,stmt,NULL) < 0) { + if (STATUS(bi->ctx) == EEXIST) + CLR_ERR(bi->ctx); + } + + /* reset bi counters */ + bi->rec_size = 0; + bi->rec_num = 0; + + free(vals); + free(stmt); + + return STATUS(bi->ctx); +} + + +int glite_lbu_bufferedInsert(glite_lbu_bufInsert *bi, const char *row) +{ + bi->rows[bi->rec_num++] = strdup(row); + bi->rec_size += strlen(row); + + if ((bi->size_limit && bi->rec_size >= bi->size_limit) || + (bi->record_limit && bi->rec_num >= bi->record_limit)) + { + if (flush_bufferd_insert(bi)) + return STATUS(bi->ctx); + } + + return CLR_ERR(bi->ctx); +} + + +static void free_buffered_insert(glite_lbu_bufInsert *bi) { + long i; + + free(bi->table_name); + free(bi->columns); + for (i=0; i < bi->rec_num; i++) { + free(bi->rows[i]); + } + free(bi->rows); +} + + +int glite_lbu_bufferedInsertClose(glite_lbu_bufInsert *bi) +{ + if (flush_bufferd_insert(bi)) + return STATUS(bi->ctx); + free_buffered_insert(bi); + + return CLR_ERR(bi->ctx); +} + + +/* + * helping compatibility function: clear error from the context + */ +static int lbu_clrerr(glite_lbu_DBContext ctx) { + ctx->code = 0; + if (ctx->text) { + free(ctx->text); + ctx->text = NULL; + } + return 0; +} + + +/* + * helping compatibility function: sets error on the context + */ +static int lbu_err(glite_lbu_DBContext ctx, int code, const char *text, const char *func, int line) { + if (code) { + ctx->code = code; + free(ctx->text); + ctx->text = text ? strdup(text) : NULL; + fprintf(stderr, "[db] %s:%d %s\n", func, line, text); + return code; + } else + return ctx->code; +} + + +/* + * helping function: find oud mysql error and sets on the context + */ +static int myerr(glite_lbu_DBContext ctx, const char *source, int line) { + return lbu_err(ctx, EIO, mysql_error(ctx->mysql), source, line); +} + + +/* + * helping function: find oud mysql stmt error and sets on the context + */ +static int myerrstmt(glite_lbu_Statement stmt, const char *source, int line) { + return lbu_err(stmt->ctx, EIO, mysql_stmt_error(stmt->stmt), source, line); +} + + +/* + * Ehelping function: error handle + * + * \return -1 failed + * \return 0 retry + * \return 1 OK + */ +static int myisokstmt(glite_lbu_Statement stmt, const char *source, int line, int *retry) { + switch (mysql_stmt_errno(stmt->stmt)) { + case 0: + return 1; + break; + case ER_DUP_ENTRY: + lbu_err(stmt->ctx, EEXIST, mysql_stmt_error(stmt->stmt), source, line); + return -1; + break; + case CR_SERVER_LOST: + if (*retry > 0) { + (*retry)--; + return 0; + } else + return -1; + break; + default: + myerrstmt(stmt, source, line); + return -1; + break; + } +} + + +/* + * mysql connect + */ +static int db_connect(glite_lbu_DBContext ctx, const char *cs, int caps, MYSQL **mysql) { + char *buf = NULL; + char *host,*user,*pw,*db; + char *slash,*at,*colon; + int ret; + + // needed for SQL result parameters + assert(sizeof(int) >= sizeof(my_bool)); + + if (!cs) return ERR(ctx, EINVAL, "connect string not specified"); + + if (!(*mysql = mysql_init(NULL))) return ERR(ctx, ENOMEM, NULL); + + mysql_options(*mysql, MYSQL_READ_DEFAULT_FILE, "my"); + + host = user = pw = db = NULL; + + buf = strdup(cs); + slash = strchr(buf,'/'); + at = strrchr(buf,'@'); + colon = strrchr(buf,':'); + + if (!slash || !at || !colon) { + free(buf); + db_close(*mysql); + *mysql = NULL; + return ERR(ctx, EINVAL, "Invalid DB connect string"); + } + + *slash = *at = *colon = 0; + host = at+1; + user = buf; + pw = slash+1; + db = colon+1; + + /* ljocha: CLIENT_FOUND_ROWS added to make authorization check + * working in update_notif(). + * Hope it does not break anything else */ + if (!mysql_real_connect(*mysql,host,user,pw,db,0,NULL,CLIENT_FOUND_ROWS)) { + free(buf); + ret = MY_ERR(ctx); + glite_lbu_DBClose(ctx); + return ret; + } + free(buf); + + ctx->cs = cs; + ctx->caps = caps; + return 0; +} + + +/* + * mysql close + */ +static void db_close(MYSQL *mysql) { + if (mysql) mysql_close(mysql); +} + + +/* + * test transactions capability: + * + * 1) with connection 1 create testing table test + * 2) with connection 1 insert a value + * 3) with connection 2 look for a value, transactions are for no error and + * no items found + * 4) with connection 1 commit and drop the table + */ +static int transaction_test(glite_lbu_DBContext ctx, MYSQL *m2, int *have_transactions) { + MYSQL *m1; + char *desc, *cmd_create, *cmd_insert, *cmd_select, *cmd_drop; + int retval; + int err; + pid_t pid; + + ctx->caps |= GLITE_LBU_DB_CAP_TRANSACTIONS; + pid = getpid(); + *have_transactions = 0; + + asprintf(&cmd_create, "CREATE TABLE test%d (item INT)", pid); + asprintf(&cmd_insert, "INSERT INTO test%d (item) VALUES (1)", pid); + asprintf(&cmd_select, "SELECT item FROM test%d", pid); + asprintf(&cmd_drop, "DROP TABLE test%d", pid); + + m1 = ctx->mysql; + glite_lbu_ExecSQL(ctx, cmd_drop, NULL); + if (glite_lbu_ExecSQL(ctx, cmd_create, NULL) != 0) goto err1; + if (glite_lbu_Transaction(ctx) != 0) goto err2; + if (glite_lbu_ExecSQL(ctx, cmd_insert, NULL) != 1) goto err2; + + ctx->mysql = m2; + if ((retval = glite_lbu_ExecSQL(ctx, cmd_select, NULL)) == -1) goto err2; + + ctx->mysql = m1; + if (glite_lbu_Commit(ctx) != 0) goto err2; + if (glite_lbu_ExecSQL(ctx, cmd_drop, NULL) != 0) goto err1; + +#ifdef LBS_DB_PROFILE + fprintf(stderr, "[%d] use_transactions = %d\n", getpid(), USE_TRANS(ctx)); +#endif + + *have_transactions = retval == 0; + goto ok; +err2: + err = ctx->code; + desc = ctx->text; + glite_lbu_ExecSQL(ctx, cmd_drop, NULL); + ctx->code = err; + ctx->text = desc; +err1: +ok: + free(cmd_create); + free(cmd_insert); + free(cmd_select); + free(cmd_drop); + return STATUS(ctx); +} + + +/* + * simple version of the fetch + */ +static int FetchRowSimple(glite_lbu_DBContext ctx, MYSQL_RES *result, unsigned long *lengths, char **results) { + MYSQL_ROW row; + int nr, i; + unsigned long *len; + + CLR_ERR(ctx); + + if (!(row = mysql_fetch_row(result))) { + if (mysql_errno((MYSQL *) ctx->mysql)) { + MY_ERR(ctx); + return -1; + } else return 0; + } + + nr = mysql_num_fields(result); + len = mysql_fetch_lengths(result); + for (i=0; inrfields) { + ERR(ctx, EINVAL, "bad number of result fields"); + return -1; + } + + // bind results + if (n) binds = calloc(n, sizeof(MYSQL_BIND)); + for (i = 0; i < n; i++) { + binds[i].buffer_type = MYSQL_TYPE_VAR_STRING; + binds[i].buffer_length = GLITE_LBU_DEFAULT_RESULT_BUFFER_LENGTH; + if (lengths) binds[i].length = &lengths[i]; + binds[i].buffer = results[i] = calloc(1, binds[i].buffer_length); + } + if (mysql_stmt_bind_result(stmt->stmt, binds) != 0) goto failedstmt; + + // fetch data + retry = 1; + do { + switch(mysql_stmt_fetch(stmt->stmt)) { + case 0: ret = 1; break; + case 1: ret = MY_ISOKSTMT(stmt, &retry); break; +#ifdef MYSQL_DATA_TRUNCATED + case MYSQL_DATA_TRUNCATED: goto failedstmt; +#endif + case MYSQL_NO_DATA: goto failed; /* it's OK */ + default: ERR(ctx, EIO, "other fetch error"); goto failed; + } + } while (ret == 0); + if (ret == -1) { + free(binds); + return STATUS(ctx); + } + + CLR_ERR(ctx); + free(binds); + +#warning FIXME: check for lenght > buffer_length, implement using fetch_column, check it +/* +You can check for data truncation by comparing the length and buffer_length members of each MYSQL_BIND structure after a fetch. If the data is truncated (length > buffer_length), you can resize the buffer and call mysql_stmt_fetch_column() for the corresponding column. +Do not forget to recall mysql_stmt_bind_result() if you are going to use the new buffers for another fetch, since mysql makes a copy of your BIND structures and therefore does not see the updated buffer. +*/ + + return n; + +failedstmt: + MY_ERRSTMT(stmt); +failed: + free(binds); + for (i = 0; i < n; i++) { + free(results[i]); + results[i] = NULL; + } + return -1; +} + + +void set_time(MYSQL_TIME *mtime, const time_t time) { + struct tm tm; + + gmtime_r(&time, &tm); + mtime->year = tm.tm_year + 1900; + mtime->month = tm.tm_mon + 1; + mtime->day = tm.tm_mday; + mtime->hour = tm.tm_hour; + mtime->minute = tm.tm_min; + mtime->second = tm.tm_sec; +} + + +time_t get_time(const MYSQL_TIME *mtime) { + struct tm tm; + + memset(&tm, 0, sizeof(tm)); + setenv("TZ","UTC",1); tzset(); + tm.tm_year = mtime->year - 1900; + tm.tm_mon = mtime->month - 1; + tm.tm_mday = mtime->day; + tm.tm_hour = mtime->hour; + tm.tm_min = mtime->minute; + tm.tm_sec = mtime->second; + + return mktime(&tm); +} -- 1.8.2.3