all: updated FSF address in GPL text
[sip-router] / modules / db_oracle / res.c
1 /*
2  * $Id$
3  *
4  * Oracle module result related functions
5  *
6  * Copyright (C) 2007,2008 TRUNK MOBILE
7  *
8  * This file is part of Kamailio, a free SIP server.
9  *
10  * Kamailio is free software; you can redistribute it and/or modify
11  * it under the terms of the GNU General Public License as published by
12  * the Free Software Foundation; either version 2 of the License, or
13  * (at your option) any later version
14  *
15  * Kamailio is distributed in the hope that it will be useful,
16  * but WITHOUT ANY WARRANTY; without even the implied warranty of
17  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
18  * GNU General Public License for more details.
19  *
20  * You should have received a copy of the GNU General Public License
21  * along with this program; if not, write to the Free Software
22  * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301  USA
23  */
24
25
26 #include <string.h>
27 #include <time.h>
28 #include <oci.h>
29 #include "../../lib/srdb1/db_res.h"
30 #include "../../lib/srdb1/db_row.h"
31 #include "../../mem/mem.h"
32 #include "../../dprint.h"
33 #include "ora_con.h"
34 #include "dbase.h"
35 #include "asynch.h"
36 #include "res.h"
37
38
39 #define MAX_DEF_HANDLES 64
40
41 struct dmap {
42     OCIDefine* defh[MAX_DEF_HANDLES];
43     union {
44         dvoid* v;
45         double* f;
46         int* i;
47         char* c;
48         OCIDate* o;
49     }pv[MAX_DEF_HANDLES];
50     dvoid* pval[MAX_DEF_HANDLES];
51     ub2 ilen[MAX_DEF_HANDLES];
52     sb2 ind[MAX_DEF_HANDLES];
53     ub2 len[MAX_DEF_HANDLES];
54 };
55 typedef struct dmap dmap_t;
56
57
58 /*
59  * Get and convert columns from a result. Define handlers and buffers
60  */
61 static int get_columns(ora_con_t* con, db1_res_t* _r, OCIStmt* _c, dmap_t* _d)
62 {
63         OCIParam *param;
64         size_t tsz;
65         ub4 i, n;
66         sword status;
67
68         status = OCIAttrGet(_c, OCI_HTYPE_STMT, &n, NULL, OCI_ATTR_PARAM_COUNT,
69                 con->errhp);
70
71         if (status != OCI_SUCCESS) {
72                 LM_ERR("driver: %s\n", db_oracle_error(con, status));
73                 return -1;
74         }
75
76         if (!n) {
77                 LM_ERR("no columns\n");
78                 return -2;
79         }
80
81         if (n >= MAX_DEF_HANDLES) {
82                 LM_ERR("too many res. Rebuild with MAX_DEF_HANDLES >= %u\n", n);
83                 return -3;
84         }
85
86         if (db_allocate_columns(_r, n) != 0) {
87                 LM_ERR("could not allocate columns");
88                 return -4;
89         }
90         memset(RES_NAMES(_r), 0, sizeof(db_key_t) * n);
91
92         RES_COL_N(_r) = n;
93
94         tsz = 0;
95         memset(_d->defh, 0, sizeof(_d->defh[0]) * n);
96         for (i = 0; i < n; i++) {
97                 ub4 len;
98                 ub2 dtype;
99
100                 status = OCIParamGet(_c, OCI_HTYPE_STMT, con->errhp,
101                         (dvoid**)(dvoid*)&param, i+1);
102                 if (status != OCI_SUCCESS) goto ora_err;
103
104                 {
105                         text* name;
106                         str* sname;
107                         status = OCIAttrGet(param, OCI_DTYPE_PARAM,
108                                 (dvoid**)(dvoid*)&name, &len, OCI_ATTR_NAME,
109                                 con->errhp);
110                         if (status != OCI_SUCCESS) goto ora_err;
111                         sname = (str*)pkg_malloc(sizeof(str)+len+1);
112                         if (!sname) {
113                                 db_free_columns(_r);
114                                 LM_ERR("no private memory left\n");
115                                 return -5;
116                         }
117                         sname->len = len;
118                         sname->s = (char*)sname + sizeof(str);
119                         memcpy(sname->s, name, len);
120                         sname->s[len] = '\0';
121                         RES_NAMES(_r)[i] = sname;
122                 }
123
124                 status = OCIAttrGet(param, OCI_DTYPE_PARAM,
125                         (dvoid**)(dvoid*)&dtype, NULL, OCI_ATTR_DATA_TYPE,
126                         con->errhp);
127                 if (status != OCI_SUCCESS) goto ora_err;
128
129                 switch (dtype) {
130                 case SQLT_UIN:          /* unsigned integer */
131 set_bitmap:
132                         LM_DBG("use DB1_BITMAP type");
133                         RES_TYPES(_r)[i] = DB1_BITMAP;
134                         len = sizeof(VAL_BITMAP((db_val_t*)NULL));
135                         break;
136
137                 case SQLT_INT:          /* (ORANET TYPE) integer */
138 set_int:
139                         LM_DBG("use DB1_INT result type");
140                         RES_TYPES(_r)[i] = DB1_INT;
141                         len = sizeof(VAL_INT((db_val_t*)NULL));
142                         break;
143
144 //              case SQLT_LNG:          /* long */
145                 case SQLT_VNU:          /* NUM with preceding length byte */
146                 case SQLT_NUM:          /* (ORANET TYPE) oracle numeric */
147                         len = 0; /* PRECISION is ub1 */
148                         status = OCIAttrGet(param, OCI_DTYPE_PARAM,
149                                 (dvoid**)(dvoid*)&len, NULL, OCI_ATTR_PRECISION,
150                                 con->errhp);
151                         if (status != OCI_SUCCESS) goto ora_err;
152                         if (len <= 11) {
153                                 sb1 sc;
154                                 status = OCIAttrGet(param, OCI_DTYPE_PARAM,
155                                         (dvoid**)(dvoid*)&sc, NULL,
156                                         OCI_ATTR_SCALE, con->errhp);
157                                 if (status != OCI_SUCCESS) goto ora_err;
158                                 if (!sc) {
159                                         dtype = SQLT_INT;
160                                         if (len != 11) goto set_int;
161                                         dtype = SQLT_UIN;
162                                         goto set_bitmap;
163                                 }
164                         }
165                 case SQLT_FLT:          /* (ORANET TYPE) Floating point number */
166                 case SQLT_BFLOAT:       /* Native Binary float*/
167                 case SQLT_BDOUBLE:      /* NAtive binary double */
168                 case SQLT_IBFLOAT:      /* binary float canonical */
169                 case SQLT_IBDOUBLE:     /* binary double canonical */
170                 case SQLT_PDN:          /* (ORANET TYPE) Packed Decimal Numeric */
171                         LM_DBG("use DB1_DOUBLE result type");
172                         RES_TYPES(_r)[i] = DB1_DOUBLE;
173                         len = sizeof(VAL_DOUBLE((db_val_t*)NULL));
174                         dtype = SQLT_FLT;
175                         break;
176
177 //              case SQLT_TIME:         /* TIME */
178 //              case SQLT_TIME_TZ:      /* TIME WITH TIME ZONE */
179                 case SQLT_DATE:         /* ANSI Date */
180                 case SQLT_DAT:          /* date in oracle format */
181                 case SQLT_ODT:          /* OCIDate type */
182                 case SQLT_TIMESTAMP:    /* TIMESTAMP */
183                 case SQLT_TIMESTAMP_TZ: /* TIMESTAMP WITH TIME ZONE */
184                 case SQLT_TIMESTAMP_LTZ:/* TIMESTAMP WITH LOCAL TZ */
185 //              case SQLT_INTERVAL_YM:  /* INTERVAL YEAR TO MONTH */
186 //              case SQLT_INTERVAL_DS:  /* INTERVAL DAY TO SECOND */
187                         LM_DBG("use DB1_DATETIME result type");
188                         RES_TYPES(_r)[i] = DB1_DATETIME;
189                         len = sizeof(OCIDate);
190                         dtype = SQLT_ODT;
191                         break;
192
193                 case SQLT_CLOB:         /* character lob */
194                 case SQLT_BLOB:         /* binary lob */
195 //              case SQLT_BFILEE:       /* binary file lob */
196 //              case SQLT_CFILEE:       /* character file lob */
197 //              case SQLT_BIN:          /* binary data(DTYBIN) */
198 //              case SQLT_LBI:          /* long binary */
199                         LM_DBG("use DB1_BLOB result type");
200                         RES_TYPES(_r)[i] = DB1_BLOB;
201                         goto dyn_str;
202
203                 case SQLT_CHR:          /* (ORANET TYPE) character string */
204                 case SQLT_STR:          /* zero terminated string */
205                 case SQLT_VST:          /* OCIString type */
206                 case SQLT_VCS:          /* Variable character string */
207                 case SQLT_AFC:          /* Ansi fixed char */
208                 case SQLT_AVC:          /* Ansi Var char */
209 //              case SQLT_RID:          /* rowid */
210                         LM_DBG("use DB1_STR result type");
211                         RES_TYPES(_r)[i] = DB1_STR;
212 dyn_str:
213                         dtype = SQLT_CHR;
214                         len = 0; /* DATA_SIZE is ub2 */
215                         status = OCIAttrGet(param, OCI_DTYPE_PARAM,
216                                 (dvoid**)(dvoid*)&len, NULL, OCI_ATTR_DATA_SIZE,
217                                 con->errhp);
218                         if (status != OCI_SUCCESS) goto ora_err;
219                         if (len >= 4000) {
220                                 LM_DBG("use DB1_BLOB result type");
221                                 RES_TYPES(_r)[i] = DB1_BLOB;
222                         }
223                         ++len;
224                         break;
225
226                 default:
227                         LM_ERR("unsupported datatype %d\n", dtype);
228                         goto stop_load;
229                 }
230                 _d->ilen[i] = (ub2)len;
231                 _d->pv[i].v = st_buf + tsz;
232                 tsz += len;
233                 status = OCIDefineByPos(_c, &_d->defh[i], con->errhp, i+1,
234                         _d->pv[i].v, len, dtype, &_d->ind[i],
235                         &_d->len[i], NULL, OCI_DEFAULT);
236                 if (status != OCI_SUCCESS) goto ora_err;
237         }
238
239 #if STATIC_BUF_LEN < 65536
240 #error
241 #endif
242         if (tsz > 65536) {
243                 LM_ERR("Row size exceed 65K. IOB's are not supported");
244                 goto stop_load;
245         }
246         return 0;
247
248 ora_err:
249         LM_ERR("driver: %s\n", db_oracle_error(con, status));
250 stop_load:
251         db_free_columns(_r);
252         return -6;
253 }
254
255
256 /*
257  * Convert data fron db format to internal format
258  */
259 static int convert_row(db1_res_t* _res, db_row_t* _r, dmap_t* _d)
260 {
261         unsigned i, n = RES_COL_N(_res);
262
263         ROW_N(_r) = n;
264         ROW_VALUES(_r) = (db_val_t*)pkg_malloc(sizeof(db_val_t) * n);
265         if (!ROW_VALUES(_r)) {
266 nomem:
267                 LM_ERR("no private memory left\n");
268                 return -1;
269         }
270         memset(ROW_VALUES(_r), 0, sizeof(db_val_t) * n);
271
272         for (i = 0; i < n; i++) {
273                 static const str dummy_string = {"", 0};
274
275                 db_val_t* v = &ROW_VALUES(_r)[i];
276                 db_type_t t = RES_TYPES(_res)[i];
277
278                 if (_d->ind[i] == -1) {
279                         /* Initialize the string pointers to a dummy empty
280                          * string so that we do not crash when the NULL flag
281                          * is set but the module does not check it properly
282                          */
283                         VAL_STRING(v) = dummy_string.s;
284                         VAL_STR(v) = dummy_string;
285                         VAL_BLOB(v) = dummy_string;
286                         VAL_TYPE(v) = t;
287                         VAL_NULL(v) = 1;
288                         continue;
289                 }
290
291                 if (_d->ind[i])
292                         LM_WARN("truncated value in DB\n");
293
294                 VAL_TYPE(v) = t;
295                 switch (t) {
296                 case DB1_INT:
297                         VAL_INT(v) = *_d->pv[i].i;
298                         break;
299
300                 case DB1_BIGINT:
301                         LM_ERR("BIGINT not supported");
302                         return -1;
303
304                 case DB1_BITMAP:
305                         VAL_BITMAP(v) = *_d->pv[i].i;
306                         break;
307
308                 case DB1_DOUBLE:
309                         VAL_DOUBLE(v) = *_d->pv[i].f;
310                         break;
311
312                 case DB1_DATETIME:
313                         {
314                                 struct tm tm;
315                                 memset(&tm, 0, sizeof(tm));
316                                 OCIDateGetTime(_d->pv[i].o, &tm.tm_hour,
317                                         &tm.tm_min, &tm.tm_sec);
318                                 OCIDateGetDate(_d->pv[i].o, &tm.tm_year,
319                                         &tm.tm_mon, &tm.tm_mday);
320                                 if (tm.tm_mon)
321                                         --tm.tm_mon;
322                                 if (tm.tm_year >= 1900)
323                                         tm.tm_year -= 1900;
324                                 VAL_TIME(v) = mktime(&tm);
325                         }
326                         break;
327
328                 case DB1_STR:
329                 case DB1_BLOB:
330                 case DB1_STRING:
331                         {
332                                 size_t len = _d->len[i];
333                                 char *pstr = pkg_malloc(len+1);
334                                 if (!pstr) goto nomem;
335                                 memcpy(pstr, _d->pv[i].c, len);
336                                 pstr[len] = '\0';
337                                 VAL_FREE(v) = 1;
338                                 if (t == DB1_STR) {
339                                         VAL_STR(v).s = pstr;
340                                         VAL_STR(v).len = len;
341                                 } else if (t == DB1_BLOB) {
342                                         VAL_BLOB(v).s = pstr;
343                                         VAL_BLOB(v).len = len;
344                                 } else {
345                                         VAL_STRING(v) = pstr;
346                                 }
347                         }
348                         break;
349
350                 default:
351                         LM_ERR("unknown type mapping (%u)\n", t);
352                         return -2;
353                 }
354         }
355
356         return 0;
357 }
358
359
360 /*
361  * Get rows and convert it from oracle to db API representation
362  */
363 static int get_rows(ora_con_t* con, db1_res_t* _r, OCIStmt* _c, dmap_t* _d)
364 {
365         ub4 rcnt;
366         sword status;
367         unsigned n = RES_COL_N(_r);
368
369         memcpy(_d->len, _d->ilen, sizeof(_d->len[0]) * n);
370
371         // timelimited operation
372         status = begin_timelimit(con, 0);
373         if (status != OCI_SUCCESS) goto ora_err;
374         do status = OCIStmtFetch2(_c, con->errhp, 1, OCI_FETCH_LAST, 0,
375                 OCI_DEFAULT);
376         while (wait_timelimit(con, status));
377         if (done_timelimit(con, status)) goto stop_load;
378         if (status != OCI_SUCCESS) {
379                 if (status != OCI_NO_DATA)
380                         goto ora_err;
381
382                 RES_ROW_N(_r) = 0;
383                 RES_ROWS(_r) = NULL;
384                 return 0;
385         }
386
387         status = OCIAttrGet(_c, OCI_HTYPE_STMT, &rcnt, NULL,
388                 OCI_ATTR_CURRENT_POSITION, con->errhp);
389         if (status != OCI_SUCCESS) goto ora_err;
390         if (!rcnt) {
391                 LM_ERR("lastpos==0\n");
392                 goto stop_load;
393         }
394
395         RES_ROW_N(_r) = rcnt;
396         RES_ROWS(_r) = (db_row_t*)pkg_malloc(sizeof(db_row_t) * rcnt);
397         if (!RES_ROWS(_r)) {
398                 LM_ERR("no private memory left\n");
399                 return -1;
400         }
401         memset(RES_ROWS(_r), 0, sizeof(db_row_t) * rcnt);
402
403         while ( 1 ) {
404                 if (convert_row(_r, &RES_ROWS(_r)[--rcnt], _d) < 0) {
405                         LM_ERR("erroc convert row\n");
406                         goto stop_load;
407                 }
408
409                 if (!rcnt)
410                         return 0;
411
412                 memcpy(_d->len, _d->ilen, sizeof(_d->len[0]) * n);
413                 // timelimited operation
414                 status = begin_timelimit(con, 0);
415                 if (status != OCI_SUCCESS) goto ora_err;
416                 do status = OCIStmtFetch2(_c, con->errhp, 1, OCI_FETCH_PRIOR, 0,
417                         OCI_DEFAULT);
418                 while (wait_timelimit(con, status));
419                 if (done_timelimit(con, status)) goto stop_load;
420                 if (status != OCI_SUCCESS) break;
421         }
422 ora_err:
423         LM_ERR("driver: %s\n", db_oracle_error(con, status));
424 stop_load:
425         db_free_rows(_r);
426         RES_ROW_N(_r) = 0;      /* TODO: skipped in db_res.c :) */
427         return -3;
428 }
429
430
431 /*
432  * Read database answer and fill the structure
433  */
434 int db_oracle_store_result(const db1_con_t* _h, db1_res_t** _r)
435 {
436         dmap_t dmap;
437         int rc;
438         db1_res_t* r;
439         ora_con_t* con;
440         OCIStmt* hs;
441
442         if (!_h || !_r) {
443 badparam:
444                 LM_ERR("invalid parameter\n");
445                 return -1;
446         }
447
448         con = CON_ORA(_h);
449         {
450             query_data_t *pcb = con->pqdata;
451             
452
453             if (!pcb || !pcb->_rs)
454                     goto badparam;
455                     
456             hs = *pcb->_rs;
457             pcb->_rs = NULL; /* paranoid for next call */
458         }           
459         
460         rc = -1;
461         if (_r) *_r = NULL;     /* unification for all errors */
462
463         r = db_new_result();
464         if (!r) {
465                 LM_ERR("no memory left\n");
466                 goto done;
467         }
468
469         if (get_columns(con, r, hs, &dmap) < 0) {
470                 LM_ERR("error while getting column names\n");
471                 goto done;
472         }
473
474         if (get_rows(con, r, hs, &dmap) < 0) {
475                 LM_ERR("error while converting rows\n");
476                 db_free_columns(r);
477                 goto done;
478         }
479
480         rc = 0;
481         *_r = r;
482 done:
483         OCIHandleFree(hs, OCI_HTYPE_STMT);
484         return rc;
485 }