[Serdev] Nested MySQL queries
Jan Janak
jan at iptel.org
Tue Sep 20 12:56:27 UTC 2005
Thanks for your effort. Feel free to commit (in HEAD only).
Jan.
On 19-09-2005 23:33, Andreas Granig wrote:
> Jan Janak wrote:
> > OK, could you check if this is supported by mysqlclient library ? I'll
> > try to check the documentation -- i recall that there might be the
> > limit of one MYSQL_RES per mysql connection, but I am not completely
> > sure about that (maybe this was only true in some old versions of
> > mysql).
>
> I can't find anything in the docs, and a quick look into the source
> shows that only the number of affected rows is overwritten in subsequent
> calls to mysql_store_result().
>
> Attached you can find a patch against rel_0_9_0. Seems to work pretty
> well, stress-testing it with sipsak in usrloc-mode (3 x 20.000 different
> aors) shows the expected results (I've tested it with the cacheless
> usrloc module, which does quite a lot of DB queries).
>
> Andy
> Index: db/db_res.h
> ===================================================================
> RCS file: /cvsroot/ser/sip_router/db/db_res.h,v
> retrieving revision 1.5
> diff -u -r1.5 db_res.h
> --- db/db_res.h 24 Aug 2004 08:45:12 -0000 1.5
> +++ db/db_res.h 19 Sep 2005 21:20:32 -0000
> @@ -41,8 +41,9 @@
> db_type_t* types; /* Column types */
> int n; /* Number of columns */
> } col;
> - struct db_row* rows; /* Rows */
> - int n; /* Number of rows */
> + struct db_row* rows; /* Rows */
> + int n; /* Number of rows */
> + void* data; /* Auxiliary data */
> } db_res_t;
>
>
> Index: modules/mysql/dbase.c
> ===================================================================
> RCS file: /cvsroot/ser/sip_router/modules/mysql/dbase.c,v
> retrieving revision 1.38
> diff -u -r1.38 dbase.c
> --- modules/mysql/dbase.c 6 Dec 2004 08:51:44 -0000 1.38
> +++ modules/mysql/dbase.c 19 Sep 2005 21:20:32 -0000
> @@ -301,8 +301,8 @@
> return -2;
> }
>
> - CON_RESULT(_h) = mysql_store_result(CON_CONNECTION(_h));
> - if (!CON_RESULT(_h)) {
> + MYRES_RESULT(*_r) = mysql_store_result(CON_CONNECTION(_h));
> + if (!MYRES_RESULT(*_r)) {
> if (mysql_field_count(CON_CONNECTION(_h)) == 0) {
> (*_r)->col.n = 0;
> (*_r)->n = 0;
> @@ -315,18 +315,19 @@
> }
> }
>
> - if (convert_result(_h, *_r) < 0) {
> + if (convert_result(_h, *_r) < 0) {
> LOG(L_ERR, "store_result(): Error while converting result\n");
> + pkg_free((*_r)->data);
> pkg_free(*_r);
>
> - /* This cannot be used because if convert_result fails,
> - * free_result will try to free rows and columns too
> - * and free will be called two times
> - */
> - /* free_result(*_r); */
> + /* This cannot be used because if convert_result fails,
> + * free_result will try to free rows and columns too
> + * and free will be called two times
> + */
> + /* free_result(*_r); */
> return -4;
> }
> -
> +
> return 0;
> }
>
> @@ -345,9 +346,8 @@
> LOG(L_ERR, "db_free_result(): Unable to free result structure\n");
> return -1;
> }
> - mysql_free_result(CON_RESULT(_h));
> - CON_RESULT(_h) = 0;
> - return 0;
> +
> + return 0;
> }
>
>
> Index: modules/mysql/my_con.c
> ===================================================================
> RCS file: /cvsroot/ser/sip_router/modules/mysql/my_con.c,v
> retrieving revision 1.2
> diff -u -r1.2 my_con.c
> --- modules/mysql/my_con.c 3 Aug 2004 17:22:06 -0000 1.2
> +++ modules/mysql/my_con.c 19 Sep 2005 21:20:32 -0000
> @@ -88,7 +88,6 @@
> void free_connection(struct my_con* con)
> {
> if (!con) return;
> - if (con->res) mysql_free_result(con->res);
> if (con->id) free_my_id(con->id);
> if (con->con) {
> mysql_close(con->con);
> Index: modules/mysql/my_con.h
> ===================================================================
> RCS file: /cvsroot/ser/sip_router/modules/mysql/my_con.h,v
> retrieving revision 1.3
> diff -u -r1.3 my_con.h
> --- modules/mysql/my_con.h 24 Aug 2004 08:58:31 -0000 1.3
> +++ modules/mysql/my_con.h 19 Sep 2005 21:20:32 -0000
> @@ -36,9 +36,7 @@
> struct my_con {
> struct my_id* id; /* Connection identifier */
> int ref; /* Reference count */
> - MYSQL_RES* res; /* Actual result */
> MYSQL* con; /* Connection representation */
> - MYSQL_ROW row; /* Actual row in the result */
> time_t timestamp; /* Timestamp of last query */
> struct my_con* next; /* Next connection in the pool */
> };
> @@ -47,9 +45,7 @@
> /*
> * Some convenience wrappers
> */
> -#define CON_RESULT(db_con) (((struct my_con*)((db_con)->tail))->res)
> #define CON_CONNECTION(db_con) (((struct my_con*)((db_con)->tail))->con)
> -#define CON_ROW(db_con) (((struct my_con*)((db_con)->tail))->row)
> #define CON_TIMESTAMP(db_con) (((struct my_con*)((db_con)->tail))->timestamp)
>
>
> Index: modules/mysql/res.c
> ===================================================================
> RCS file: /cvsroot/ser/sip_router/modules/mysql/res.c,v
> retrieving revision 1.4
> diff -u -r1.4 res.c
> --- modules/mysql/res.c 24 Aug 2004 08:58:31 -0000 1.4
> +++ modules/mysql/res.c 19 Sep 2005 21:20:32 -0000
> @@ -70,7 +70,7 @@
>
> RES_COL_N(_r) = n;
>
> - fields = mysql_fetch_fields(CON_RESULT(_h));
> + fields = mysql_fetch_fields(MYRES_RESULT(_r));
> for(i = 0; i < n; i++) {
> RES_NAMES(_r)[i] = fields[i].name;
> switch(fields[i].type) {
> @@ -143,7 +143,7 @@
> return -1;
> }
>
> - n = mysql_num_rows(CON_RESULT(_h));
> + n = mysql_num_rows(MYRES_RESULT(_r));
> RES_ROW_N(_r) = n;
> if (!n) {
> RES_ROWS(_r) = 0;
> @@ -156,8 +156,8 @@
> }
>
> for(i = 0; i < n; i++) {
> - CON_ROW(_h) = mysql_fetch_row(CON_RESULT(_h));
> - if (!CON_ROW(_h)) {
> + MYRES_ROW(_r) = mysql_fetch_row(MYRES_RESULT(_r));
> + if (!MYRES_ROW(_r)) {
> LOG(L_ERR, "convert_rows(): %s\n", mysql_error(CON_CONNECTION(_h)));
> RES_ROW_N(_r) = i;
> free_rows(_r);
> @@ -201,6 +201,16 @@
> LOG(L_ERR, "new_result(): No memory left\n");
> return 0;
> }
> +
> + r->data = pkg_malloc(sizeof(struct my_res));
> + if(!r->data) {
> + pkg_free(r);
> + LOG(L_ERR, "store_result(): No memory left 2\n");
> + return 0;
> + }
> +
> + MYRES_RESULT(r) = 0;
> + MYRES_ROW(r) = 0;
> RES_NAMES(r) = 0;
> RES_TYPES(r) = 0;
> RES_COL_N(r) = 0;
> @@ -246,6 +256,8 @@
>
> free_columns(_r);
> free_rows(_r);
> + mysql_free_result(MYRES_RESULT(_r));
> + pkg_free(_r->data);
> pkg_free(_r);
> return 0;
> }
> Index: modules/mysql/res.h
> ===================================================================
> RCS file: /cvsroot/ser/sip_router/modules/mysql/res.h,v
> retrieving revision 1.3
> diff -u -r1.3 res.h
> --- modules/mysql/res.h 24 Aug 2004 08:58:31 -0000 1.3
> +++ modules/mysql/res.h 19 Sep 2005 21:20:32 -0000
> @@ -33,6 +33,13 @@
> #include "../../db/db_res.h"
> #include "../../db/db_con.h"
>
> +struct my_res {
> + MYSQL_RES* res; /* the mysql result */
> + MYSQL_ROW row; /* the current row */
> +};
> +
> +#define MYRES_RESULT(db_res) (((struct my_res*)(db_res)->data)->res)
> +#define MYRES_ROW(db_res) (((struct my_res*)(db_res)->data)->row)
>
> /*
> * Create a new result structure and initialize it
> Index: modules/mysql/row.c
> ===================================================================
> RCS file: /cvsroot/ser/sip_router/modules/mysql/row.c,v
> retrieving revision 1.4
> diff -u -r1.4 row.c
> --- modules/mysql/row.c 24 Aug 2004 08:58:31 -0000 1.4
> +++ modules/mysql/row.c 19 Sep 2005 21:20:32 -0000
> @@ -33,6 +33,7 @@
> #include <mysql/mysql.h>
> #include "val.h"
> #include "my_con.h"
> +#include "res.h"
> #include "row.h"
>
>
> @@ -56,11 +57,11 @@
> return -1;
> }
>
> - lengths = mysql_fetch_lengths(CON_RESULT(_h));
> + lengths = mysql_fetch_lengths(MYRES_RESULT(_res));
>
> for(i = 0; i < RES_COL_N(_res); i++) {
> if (str2val(RES_TYPES(_res)[i], &(ROW_VALUES(_r)[i]),
> - ((MYSQL_ROW)CON_ROW(_h))[i], lengths[i]) < 0) {
> + (MYRES_ROW(_res))[i], lengths[i]) < 0) {
> LOG(L_ERR, "convert_row(): Error while converting value\n");
> free_row(_r);
> return -3;
More information about the Serdev
mailing list