From 5dd459d650db66a368da85a074a9ecf07d0d039e Mon Sep 17 00:00:00 2001 From: =?utf8?q?Franti=C5=A1ek=20Dvo=C5=99=C3=A1k?= Date: Fri, 21 Jul 2006 11:43:06 +0000 Subject: [PATCH] Implemented result buffer lenght check and fetch, result length aren't limited in prepared queries now. Starts with 256 bytes per column. Fix blob in results (was truncated on \000). Fix prepared commands without results (INSERT, ...). Rewrite the example test. --- org.glite.lb-utils.db/Makefile | 21 ++- org.glite.lb-utils.db/examples/db_test.c | 189 ++++++++++++++------- .../project/configure.properties.xml | 5 +- org.glite.lb-utils.db/src/db.c | 92 ++++++---- 4 files changed, 210 insertions(+), 97 deletions(-) diff --git a/org.glite.lb-utils.db/Makefile b/org.glite.lb-utils.db/Makefile index 2f96b36..43320d3 100644 --- a/org.glite.lb-utils.db/Makefile +++ b/org.glite.lb-utils.db/Makefile @@ -8,6 +8,7 @@ lbutilsprefix=lb-utils package=glite-lb-utils-db version=0.2.0 PREFIX=/opt/glite +flavour=gcc32thr glite_location=/opt/glite mysql_prefix=/opt/mysql @@ -43,6 +44,8 @@ endif TEST_LIBS:=-L${cppunit_prefix}/lib -lcppunit TEST_INC:=-I${cppunit_prefix}/include +LDFLAGS:=-L${stagedir}/lib ${COVERAGE_FLAGS} + COMPILE:=libtool --mode=compile ${CC} ${CFLAGS} LINK:=libtool --mode=link ${CC} -rpath ${stagedir}/lib ${LDFLAGS} INSTALL:=libtool --mode=install install @@ -55,16 +58,24 @@ ifneq (${mysql_prefix},/usr) endif endif -EXT_LIBS:=${mysqlib} -lmysqlclient +EXT_LIBS:=${mysqlib} -lmysqlclient -lglite_lb_common_${flavour} OBJS:=db.o +TESTOBJS:=dbtest.o HDRS:=db.h LOBJS:=${OBJS:.o=.lo} +LTESTOBJS:=${TESTOBJS:.o=.lo} libglite_lbutils_db.la: ${LOBJS} ${LINK} -o $@ $< ${EXT_LIBS} -db_test: db_test.lo - ${LINK} -o $@ $< -lglite_lbutils_db +libglite_lbutils_dbtest.la: ${LTESTOBJS} + ${LINK} -o $@ $< ${EXT_LIBS} + +dbtest.lo dbtest.o: db.c db.h + ${COMPILE} -DGLITE_LBU_DEFAULT_RESULT_BUFFER_LENGTH=10 -c $< -o $@ + +db_test: db_test.lo libglite_lbutils_dbtest.la + ${LINK} -o $@ $+ ${EXT_LIBS} default all: compile @@ -111,7 +122,7 @@ 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 +db.lo: db.c db.h +db_test.lo: libglite_lbutils_dbtest.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 index 50bc344..725fbf7 100644 --- a/org.glite.lb-utils.db/examples/db_test.c +++ b/org.glite.lb-utils.db/examples/db_test.c @@ -1,23 +1,80 @@ +/* + * Example (and quick test) of this DB module. + * + * Requires existing database with appropriate access: + * + * mysqladmin -u root -p create test + * mysql -u root -p -e 'GRANT ALL on test.* to testuset@localhost' + * + * Use CS environment variable when using different user/pwd@machine:dbname. + */ + #include #include #include #include "db.h" +#define CS "testuser/@localhost:test" +#define CREATE_CMD "CREATE TABLE data (\n\ + id INT NOT NULL,\n\ + user VARCHAR(32) NOT NULL,\n\ + info BLOB,\n\ + PRIMARY KEY (id),\n\ + INDEX(user)\n\ +) engine=innodb" +#define DROP_CMD "DROP TABLE data" +#define INSERT_TRIO_CMD "INSERT INTO data (id, user, info) VALUES (%d, %s, %s)" +#define SELECT_TRIO_CMD "SELECT id, user, info FROM data WHERE user = '%s'" +#define INSERT_CMD "INSERT INTO data (id, user, info) VALUES (?, ?, ?)" +#define SELECT_CMD "SELECT id, user, info FROM data WHERE user = ?" #define dprintf(ARGS) { printf("%s: ", name); printf ARGS; } +static void print_blob(unsigned long len, char *blob) { + int i; + for (i = 0; i < len; i++) printf("%02X ", blob[i]); + printf("(='"); + for (i = 0; i < len; i++) printf("%c", blob[i]); + printf("')"); +} + + +static void print_free_result(const char *name, unsigned long *lens, char **res) { + dprintf((" id='%s'=%d\n", res[0], atoi(res[0]))); + + dprintf((" user='%s'\n", res[1])); + + dprintf((" blob=")); + if (res[2] && lens) print_blob(lens[2], res[2]); + else printf("null"); + printf("\n"); + + free(res[0]); + free(res[1]); + free(res[2]); +} + + int main(int argn, char *argv[]) { - char *name; + char *name, *cmd; const char *cs; glite_lbu_DBContext ctx; glite_lbu_Statement stmt; int caps; + char blob1[] = "Guess: blob or \000string?"; + char blob2[] = {0, 1, 2, 3, 4, 5}; + + int nr; + char *res[3]; + unsigned long lens[3]; + if ((name = strrchr(argv[0], '/')) != NULL) name++; else name = argv[0]; - if ((cs = getenv("DB")) == NULL) cs = "jpis/@localhost:jpis1"; + if ((cs = getenv("CS")) == NULL) cs = CS; + cmd = NULL; // init dprintf(("connecting to %s...\n", cs)); @@ -27,83 +84,90 @@ int main(int argn, char *argv[]) { dprintf(("can't do prepared commands, exiting.")); goto failctx; } + // caps glite_lbu_DBSetCaps(ctx, caps); dprintf(("capabilities: %d\n", caps)); + // create all needed tables and data + dprintf(("creating tables...\n")); + glite_lbu_ExecSQL(ctx, DROP_CMD, NULL); + if (glite_lbu_ExecSQL(ctx, CREATE_CMD, NULL) == -1) goto failctx; + // trio-insert + dprintf(("trio-insert...\n")); + asprintf(&cmd, INSERT_TRIO_CMD, 1, "'hyperochus'", "NULL"); + if (glite_lbu_ExecSQL(ctx, cmd, NULL) != 1) goto failctx; + free(cmd); cmd = NULL; + // prepared-insert + dprintf(("prepare-insert...\n")); + if (glite_lbu_PrepareStmt(ctx, INSERT_CMD, &stmt) != 0) goto failctx; + dprintf(("execute 1. insert...\n")); + if (glite_lbu_ExecStmt(stmt, 3, + GLITE_LBU_DB_TYPE_INT, 2, + GLITE_LBU_DB_TYPE_VARCHAR, "cicomexocitl.civ", + GLITE_LBU_DB_TYPE_BLOB, blob1, sizeof(blob1) - 1) != 1) goto failstmt; + dprintf(("execute 2. insert...\n")); + if (glite_lbu_ExecStmt(stmt, 3, + GLITE_LBU_DB_TYPE_INT, 3, + GLITE_LBU_DB_TYPE_VARCHAR, "tartarus", + GLITE_LBU_DB_TYPE_NULL) != 1) goto failstmt; + dprintf(("execute 3. insert...\n")); + if (glite_lbu_ExecStmt(stmt, 3, + GLITE_LBU_DB_TYPE_INT, 4, + GLITE_LBU_DB_TYPE_VARCHAR, "harpia", + GLITE_LBU_DB_TYPE_BLOB, blob2, sizeof(blob2)) != 1) goto failstmt; + glite_lbu_FreeStmt(stmt); dprintf(("\n")); - // "trio" queries + // trio-query { - 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) { + const char *user; + + user = "harpia"; + dprintf(("selecting '%s'...\n", user)); + asprintf(&cmd, SELECT_TRIO_CMD, user); + if (glite_lbu_ExecSQL(ctx, cmd, &stmt) == -1) goto failctx; + free(cmd); cmd = NULL; + dprintf(("fetching '%s'...\n", user)); + while ((nr = glite_lbu_FetchRow(stmt, 3, lens, 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++;} - } + print_free_result(name, lens, res); } - free(res); + if (nr < 0) dprintf(("fetch '%s' failed\n", user)); dprintf(("closing stmt...\n")); + glite_lbu_FreeStmt(stmt); dprintf(("\n")); + + user = "nobody"; + dprintf(("selecting '%s'...\n", user)); + asprintf(&cmd, SELECT_TRIO_CMD, user); + if (glite_lbu_ExecSQL(ctx, cmd, &stmt) == -1) goto failctx; + free(cmd); cmd = NULL; + dprintf(("fetching '%s'...\n", user)); + while ((nr = glite_lbu_FetchRow(stmt, 3, lens, res)) > 0) { + dprintf(("Result: n=%d, res=%p\n", nr, res)); + print_free_result(name, lens, res); + } + if (nr < 0) dprintf(("fetch '%s' failed\n", user)); + dprintf(("closing stmt...\n")); glite_lbu_FreeStmt(stmt); + dprintf(("\n")); } // "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")); + const char *user; + + dprintf(("preparing...\n", user)); + if ((glite_lbu_PrepareStmt(ctx, SELECT_CMD, &stmt)) != 0) goto failctx; - 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) { + user = "cicomexocitl.civ"; + dprintf(("executing '%s'...\n", user)); + if (glite_lbu_ExecStmt(stmt, 1, GLITE_LBU_DB_TYPE_VARCHAR, user) == -1) goto failstmt; + dprintf(("fetching '%s'...\n", user)); + while ((nr = glite_lbu_FetchRow(stmt, 3, lens, 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++;} - } + print_free_result(name, lens, res); } - free(res); + if (nr < 0) dprintf(("fetch '%s' failed\n", user)); dprintf(("\n")); dprintf(("closing stmt...\n")); @@ -122,6 +186,7 @@ failctx: dprintf(("closing...\n")); glite_lbu_DBClose(ctx); fail: + free(cmd); dprintf(("failed\n")); return 1; } diff --git a/org.glite.lb-utils.db/project/configure.properties.xml b/org.glite.lb-utils.db/project/configure.properties.xml index af94b53..276b497 100644 --- a/org.glite.lb-utils.db/project/configure.properties.xml +++ b/org.glite.lb-utils.db/project/configure.properties.xml @@ -20,6 +20,9 @@ Revision history: $Log$ + Revision 1.2 2006/07/20 13:42:51 valtri + Join properly with build system (it uses lb.common yet), it compiles here now. + Revision 1.1.1.1 2006/07/14 06:59:03 akrenek module created @@ -49,8 +52,8 @@ distdir=${dist.dir} globalprefix=${global.prefix} lbutilsprefix=${subsystem.prefix} package=${module.package.name} -version=${module.version} PREFIX=${install.dir} +flavour=${with.globus.thr.flavor} glite_location=${with.glite.location} mysql_prefix=${with.mysql.prefix} diff --git a/org.glite.lb-utils.db/src/db.c b/org.glite.lb-utils.db/src/db.c index 829c9e0..f65a004 100644 --- a/org.glite.lb-utils.db/src/db.c +++ b/org.glite.lb-utils.db/src/db.c @@ -22,7 +22,9 @@ #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 +#ifndef GLITE_LBU_DEFAULT_RESULT_BUFFER_LENGTH +#define GLITE_LBU_DEFAULT_RESULT_BUFFER_LENGTH 256 +#endif #define CLR_ERR(CTX) lbu_clrerr((CTX)) @@ -357,13 +359,12 @@ int glite_lbu_PrepareStmt(glite_lbu_DBContext ctx, const char *sql, glite_lbu_St } 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); + // number of fields (0 for no results) + if ((meta = mysql_stmt_result_metadata((*stmt)->stmt)) != NULL) { + (*stmt)->nrfields = mysql_num_fields(meta); + mysql_free_result(meta); + } else + (*stmt)->nrfields = 0; return CLR_ERR(ctx); @@ -794,7 +795,12 @@ static int FetchRowSimple(glite_lbu_DBContext ctx, MYSQL_RES *result, unsigned l len = mysql_fetch_lengths(result); for (i=0; inrfields) { ERR(ctx, EINVAL, "bad number of result fields"); return -1; } - // bind results + // space for results if (n) binds = calloc(n, sizeof(MYSQL_BIND)); + if (!lengths) { + lens = calloc(n, sizeof(unsigned long)); + lengths = lens; + } 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); + binds[i].buffer_length = GLITE_LBU_DEFAULT_RESULT_BUFFER_LENGTH - 1; + binds[i].length = &lengths[i]; + binds[i].buffer = results[i] = calloc(1, GLITE_LBU_DEFAULT_RESULT_BUFFER_LENGTH); } if (mysql_stmt_bind_result(stmt->stmt, binds) != 0) goto failedstmt; - // fetch data + // fetch data, all can be truncated 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; + case MYSQL_DATA_TRUNCATED: #endif - case MYSQL_NO_DATA: goto failed; /* it's OK */ + case 0: + ret = 1; break; + case 1: ret = MY_ISOKSTMT(stmt, &retry); break; + case MYSQL_NO_DATA: ret = 0; goto quit; /* it's OK */ default: ERR(ctx, EIO, "other fetch error"); goto failed; } } while (ret == 0); - if (ret == -1) { - free(binds); - return STATUS(ctx); + if (ret == -1) goto failed; + + // check if all fileds had enough buffer space + for (i = 0; i < n; i++) { + // fetch the rest if needed + if (lengths[i] > binds[i].buffer_length) { + unsigned int fetched; + + fetched = binds[i].buffer_length; + if ((results[i] = realloc(results[i], lengths[i] + 1)) == NULL) { + ERR(ctx, ENOMEM, "insufficient memory for field data"); + goto failed; + } + results[i][lengths[i]] = '\000'; + binds[i].buffer = results[i] + fetched; + binds[i].buffer_length = lengths[i] - fetched; + + retry = 1; + do { + switch (mysql_stmt_fetch_column(stmt->stmt, binds + i, i, fetched)) { + case 0: ret = 1; break; + case 1: ret = MY_ISOKSTMT(stmt, &retry); break; + case MYSQL_NO_DATA: ret = 0; goto quit; /* it's OK */ + default: ERR(ctx, EIO, "other fetch error"); goto failed; + } + } while (ret == 0); + if (ret == -1) goto failed; + } } 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. -*/ - + free(lens); return n; failedstmt: MY_ERRSTMT(stmt); failed: + ret = -1; +quit: free(binds); + free(lens); for (i = 0; i < n; i++) { free(results[i]); results[i] = NULL; } - return -1; + return ret; } -- 1.8.2.3