From f3a744e60e9af7fad0efd26555da57e98d647feb Mon Sep 17 00:00:00 2001 From: Marco van Wieringen Date: Fri, 2 Apr 2010 09:23:10 +0200 Subject: [PATCH] Added limit_filter which rewrites queries which use the LIMIT functions into something Ingres can understand. Removed all static functions from the myingres.sh file as they are no longer an exported interface (we can always put that back when we have the need to export more functions). Some other rewrites of the code to make it somewhat cleaner. --- bacula/src/cats/cats.h | 2 + bacula/src/cats/ingres.c | 8 ++- bacula/src/cats/myingres.sc | 116 ++++++++++++++++++++++++------------ bacula/src/cats/myingres.sh | 37 +++++------- 4 files changed, 98 insertions(+), 65 deletions(-) diff --git a/bacula/src/cats/cats.h b/bacula/src/cats/cats.h index 674d57bb33..52ae5c6721 100644 --- a/bacula/src/cats/cats.h +++ b/bacula/src/cats/cats.h @@ -538,6 +538,7 @@ extern const char* my_pg_batch_fill_path_query; #ifdef HAVE_INGRES #include "myingres.h" +#include "lib/reg.h" #define BDB_VERSION 12 @@ -589,6 +590,7 @@ struct B_DB { POOLMEM *path; /* Path only */ POOLMEM *esc_name; /* Escaped file name */ POOLMEM *esc_path; /* Escaped path name */ + BREGEXP *limit_filter; /* Filter LIMIT function in queries into supported SQL */ int fnl; /* file name length */ int pnl; /* path name length */ }; diff --git a/bacula/src/cats/ingres.c b/bacula/src/cats/ingres.c index 75067b8dc6..11860066fe 100755 --- a/bacula/src/cats/ingres.c +++ b/bacula/src/cats/ingres.c @@ -126,6 +126,7 @@ db_init_database(JCR *jcr, const char *db_name, const char *db_user, const char mdb->esc_name = get_pool_memory(PM_FNAME); mdb->esc_path = get_pool_memory(PM_FNAME); mdb->allow_transactions = mult_db_connections; + mdb->limit_filter = new_bregexp("/LIMIT ([0-9]+)/FETCH FIRST $1 ROW ONLY/g"); qinsert(&db_list, &mdb->bq); /* put db in list */ V(mutex); return mdb; @@ -265,6 +266,7 @@ db_close_database(JCR *jcr, B_DB *mdb) free_pool_memory(mdb->path); free_pool_memory(mdb->esc_name); free_pool_memory(mdb->esc_path); + free_bregexp(mdb->limit_filter); if (mdb->db_name) { free(mdb->db_name); } @@ -542,13 +544,13 @@ int my_ingres_query(B_DB *mdb, const char *query) /* TODO: differentiate between SELECTs and other queries */ - if ((cols = INGgetCols(query)) <= 0) { + if ((cols = INGgetCols(mdb, query)) <= 0) { if (cols < 0 ) { Dmsg0(500,"my_ingres_query: neg.columns: no DML stmt!\n"); } Dmsg0(500,"my_ingres_query (non SELECT) starting...\n"); /* non SELECT */ - mdb->num_rows = INGexec(mdb->db, query); + mdb->num_rows = INGexec(mdb, mdb->db, query); if (INGcheck()) { Dmsg0(500,"my_ingres_query (non SELECT) went wrong\n"); mdb->status = 1; @@ -559,7 +561,7 @@ int my_ingres_query(B_DB *mdb, const char *query) } else { /* SELECT */ Dmsg0(500,"my_ingres_query (SELECT) starting...\n"); - mdb->result = INGquery(mdb->db, query); + mdb->result = INGquery(mdb, mdb->db, query); if ( mdb->result != NULL ) { Dmsg1(500, "we have a result\n", query); diff --git a/bacula/src/cats/myingres.sc b/bacula/src/cats/myingres.sc index e632d36999..5425730321 100644 --- a/bacula/src/cats/myingres.sc +++ b/bacula/src/cats/myingres.sc @@ -18,8 +18,9 @@ int INGcheck() return (sqlca.sqlcode < 0) ? sqlca.sqlcode : 0; } -short INGgetCols(const char *query) +short INGgetCols(B_DB *mdb, const char *query) { + bool stmt_free = false; EXEC SQL BEGIN DECLARE SECTION; char *stmt; EXEC SQL END DECLARE SECTION; @@ -32,13 +33,22 @@ short INGgetCols(const char *query) sqlda->sqln = number; - stmt = bstrdup(query); + /* + * See if we need to run this through the limit_filter. + */ + if (strstr(query, "LIMIT") != NULL) { + stmt = mdb->limit_filter->replace(query); + } else { + stmt = bstrdup(query); + stmt_free = true; + } EXEC SQL PREPARE s1 from :stmt; if (INGcheck() < 0) { number = -1; goto bail_out; } + EXEC SQL DESCRIBE s1 into :sqlda; if (INGcheck() < 0) { number = -1; @@ -48,13 +58,16 @@ short INGgetCols(const char *query) number = sqlda->sqld; bail_out: - free(stmt); + if (stmt_free) { + free(stmt); + } free(sqlda); return number; } -static inline IISQLDA *INGgetDescriptor(short numCols, const char *query) +static inline IISQLDA *INGgetDescriptor(B_DB *mdb, short numCols, const char *query) { + bool stmt_free = false; EXEC SQL BEGIN DECLARE SECTION; char *stmt; EXEC SQL END DECLARE SECTION; @@ -67,11 +80,21 @@ static inline IISQLDA *INGgetDescriptor(short numCols, const char *query) sqlda->sqln = numCols; - stmt = bstrdup(query); + /* + * See if we need to run this through the limit_filter. + */ + if (strstr(query, "LIMIT") != NULL) { + stmt = mdb->limit_filter->replace(query); + } else { + stmt = bstrdup(query); + stmt_free = true; + } EXEC SQL PREPARE s2 INTO :sqlda FROM :stmt; - free(stmt); + if (stmt_free) { + free(stmt); + } for (i = 0; i < sqlda->sqld; ++i) { /* @@ -111,12 +134,12 @@ static inline IISQLDA *INGgetDescriptor(short numCols, const char *query) static void INGfreeDescriptor(IISQLDA *sqlda) { + int i; + if (!sqlda) { return; } - int i; - for (i = 0; i < sqlda->sqld; ++i) { if (sqlda->sqlvar[i].sqldata) { free(sqlda->sqlvar[i].sqldata); @@ -153,13 +176,13 @@ static inline int INGgetTypeSize(IISQLVAR *ingvar) static inline INGresult *INGgetINGresult(IISQLDA *sqlda) { + int i; + INGresult *result = NULL; + if (!sqlda) { return NULL; } - int i; - INGresult *result = NULL; - result = (INGresult *)malloc(sizeof(INGresult)); memset(result, 0, sizeof(INGresult)); @@ -189,32 +212,29 @@ static inline INGresult *INGgetINGresult(IISQLDA *sqlda) static void INGfreeINGresult(INGresult *ing_res) { + int rows; + ING_ROW *rowtemp; + if (!ing_res) { return; } - int rows; - ING_ROW *rowtemp; - /* * Free all rows and fields, then res, not descriptor! + * + * Use of rows is a nasty workaround til I find the reason, + * why aggregates like max() don't work */ - if (ing_res != NULL) { - /* - * Use of rows is a nasty workaround til I find the reason, - * why aggregates like max() don't work - */ - rows = ing_res->num_rows; - ing_res->act_row = ing_res->first_row; - while (ing_res->act_row != NULL && rows > 0) { - rowtemp = ing_res->act_row->next; - INGfreeRowSpace(ing_res->act_row, ing_res->sqlda); - ing_res->act_row = rowtemp; - --rows; - } - if (ing_res->fields) { - free(ing_res->fields); - } + rows = ing_res->num_rows; + ing_res->act_row = ing_res->first_row; + while (ing_res->act_row != NULL && rows > 0) { + rowtemp = ing_res->act_row->next; + INGfreeRowSpace(ing_res->act_row, ing_res->sqlda); + ing_res->act_row = rowtemp; + --rows; + } + if (ing_res->fields) { + free(ing_res->fields); } free(ing_res); ing_res = NULL; @@ -379,6 +399,7 @@ static inline ING_STATUS INGresultStatus(INGresult *res) static void INGrowSeek(INGresult *res, int row_number) { ING_ROW *trow = NULL; + if (res->act_row->row_number == row_number) { return; } @@ -390,7 +411,7 @@ static void INGrowSeek(INGresult *res, int row_number) return; } - for (trow = res->first_row ; trow->row_number != row_number ; trow = trow->next ); + for (trow = res->first_row; trow->row_number != row_number; trow = trow->next) ; res->act_row = trow; /* * Note - can be null - if row_number not found, right? @@ -402,6 +423,7 @@ char *INGgetvalue(INGresult *res, int row_number, int column_number) if (row_number != res->act_row->row_number) { INGrowSeek(res, row_number); } + return res->act_row->sqlvar[column_number].sqldata; } @@ -410,6 +432,7 @@ int INGgetisnull(INGresult *res, int row_number, int column_number) if (row_number != res->act_row->row_number) { INGrowSeek(res, row_number); } + return (*res->act_row->sqlvar[column_number].sqlind == -1) 1 : 0; } @@ -437,19 +460,32 @@ short INGftype(const INGresult *res, int column_number) return res->fields[column_number].type; } -int INGexec(INGconn *conn, const char *query) +int INGexec(B_DB *mdb, INGconn *conn, const char *query) { + bool stmt_free = false; int check; EXEC SQL BEGIN DECLARE SECTION; int rowcount; char *stmt; EXEC SQL END DECLARE SECTION; - stmt = bstrdup(query); + /* + * See if we need to run this through the limit_filter. + */ + if (strstr(query, "LIMIT") != NULL) { + stmt = mdb->limit_filter->replace(query); + } else { + stmt = bstrdup(query); + stmt_free = true; + } rowcount = -1; EXEC SQL EXECUTE IMMEDIATE :stmt; - free(stmt); + + if (stmt_free) { + free(stmt); + } + if ((check = INGcheck()) < 0) { return check; } @@ -462,7 +498,7 @@ int INGexec(INGconn *conn, const char *query) return rowcount; } -INGresult *INGquery(INGconn *conn, const char *query) +INGresult *INGquery(B_DB *mdb, INGconn *conn, const char *query) { /* * TODO: error handling @@ -470,7 +506,7 @@ INGresult *INGquery(INGconn *conn, const char *query) IISQLDA *desc = NULL; INGresult *res = NULL; int rows = -1; - int cols = INGgetCols(query); + int cols = INGgetCols(mdb, query); desc = INGgetDescriptor(cols, query); if (!desc) { @@ -495,18 +531,20 @@ void INGclear(INGresult *res) if (res == NULL) { return; } - IISQLDA *desc = res->sqlda; + INGfreeINGresult(res); - INGfreeDescriptor(desc); + INGfreeDescriptor(res->sqlda); } INGconn *INGconnectDB(char *dbname, char *user, char *passwd) { + INGconn *dbconn; + if (dbname == NULL || strlen(dbname) == 0) { return NULL; } - INGconn *dbconn = (INGconn *)malloc(sizeof(INGconn)); + dbconn = (INGconn *)malloc(sizeof(INGconn)); memset(dbconn, 0, sizeof(INGconn)); EXEC SQL BEGIN DECLARE SECTION; diff --git a/bacula/src/cats/myingres.sh b/bacula/src/cats/myingres.sh index 641ee9ea5c..8cbf0415ec 100644 --- a/bacula/src/cats/myingres.sh +++ b/bacula/src/cats/myingres.sh @@ -56,29 +56,20 @@ typedef struct ing_conn { /* ---Prototypes--- */ -int INGcheck(); -ING_STATUS INGresultStatus(INGresult *res); -short INGgetCols(const char *stmt); -IISQLDA *INGgetDescriptor(short numCols, const char *stmt); -void INGfreeDescriptor(IISQLDA *sqlda); -int INGgetTypeSize(IISQLVAR *ingvar); -INGresult *INGgetINGresult(IISQLDA *sqlda); -void INGfreeINGresult(INGresult *ing_res); -ING_ROW *INGgetRowSpace(INGresult *ing_res); -void INGfreeRowSpace(ING_ROW *row, IISQLDA *sqlda); -int INGfetchAll(const char *stmt, INGresult *ing_res); -void INGrowSeek(INGresult *res, int row_number); -char *INGgetvalue(INGresult *res, int row_number, int column_number); -int INGgetisnull(INGresult *res, int row_number, int column_number); -int INGntuples(const INGresult *res); -int INGnfields(const INGresult *res); -char *INGfname(const INGresult *res, int column_number); -short INGftype(const INGresult *res, int column_number); -INGresult *INGexec(INGconn *db, const char *query); -void INGclear(INGresult *res); +int INGcheck(void); +short INGgetCols(B_DB *mdb, const char *stmt); +char *INGgetvalue(INGresult *res, int row_number, int column_number); +int INGgetisnull(INGresult *res, int row_number, int column_number); +int INGntuples(const INGresult *res); +int INGnfields(const INGresult *res); +char *INGfname(const INGresult *res, int column_number); +short INGftype(const INGresult *res, int column_number); +int INGexec(B_DB *mdb, INGconn *db, const char *query); +INGresult *INGquery(B_DB *mdb, INGconn *db, const char *query); +void INGclear(INGresult *res); INGconn *INGconnectDB(char *dbname, char *user, char *passwd); -void INGdisconnectDB(INGconn *dbconn); -char *INGerrorMessage(const INGconn *conn); -char *INGcmdTuples(INGresult *res); +void INGdisconnectDB(INGconn *dbconn); +char *INGerrorMessage(const INGconn *conn); +char *INGcmdTuples(INGresult *res); #endif /* _MYINGRES_SH */ -- 2.39.2