1 /* FreeTDS - Library of routines accessing Sybase and Microsoft databases
2 * Copyright (C) 2011 James K. Lowden
3 *
4 * This library is free software; you can redistribute it and/or
5 * modify it under the terms of the GNU Library General Public
6 * License as published by the Free Software Foundation; either
7 * version 2 of the License, or (at your option) any later version.
8 *
9 * This library is distributed in the hope that it will be useful,
10 * but WITHOUT ANY WARRANTY; without even the implied warranty of
11 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
12 * Library General Public License for more details.
13 *
14 * You should have received a copy of the GNU Library General Public
15 * License along with this library; if not, write to the
16 * Free Software Foundation, Inc., 59 Temple Place - Suite 330,
17 * Boston, MA 02111-1307, USA.
18 */
19
20 #include <config.h>
21
22 #include <stdarg.h>
23
24 #include <freetds/time.h>
25
26 #include <assert.h>
27 #include <stdio.h>
28
29 #if HAVE_STDLIB_H
30 #include <stdlib.h>
31 #endif /* HAVE_STDLIB_H */
32
33 #if HAVE_STRING_H
34 #include <string.h>
35 #endif /* HAVE_STRING_H */
36
37 #if HAVE_UNISTD_H
38 #include <unistd.h>
39 #endif /* HAVE_UNISTD_H */
40
41 #if HAVE_ERRNO_H
42 # include <errno.h>
43 #endif /* HAVE_ERRNO_H */
44
45
46 #include <freetds/tds.h>
47 #include <freetds/thread.h>
48 #include <freetds/convert.h>
49 #include <freetds/utils/string.h>
50 #include <freetds/replacements.h>
51 #include <sybfront.h>
52 #include <sybdb.h>
53 #include <syberror.h>
54 #include <dblib.h>
55
56 #define TDS_FIND(k,b,c) tds_find(k, b, TDS_VECTOR_SIZE(b), sizeof(b[0]), c)
57
58 typedef bool (*compare_func)(const void *, const void *);
59
60 static void *
tds_find(const void * key,const void * base,size_t nelem,size_t width,compare_func compar)61 tds_find(const void *key, const void *base, size_t nelem, size_t width,
62 compare_func compar)
63 {
64 size_t n;
65 char *p = (char *) base;
66
67 for (n = nelem; n != 0; --n) {
68 if (compar(key, p))
69 return p;
70 p += width;
71 }
72 return NULL;
73 }
74
75
76 struct col_t
77 {
78 size_t len;
79 TDS_SERVER_TYPE type;
80 int null_indicator;
81 char *s;
82 union {
83 DBTINYINT ti;
84 DBSMALLINT si;
85 DBINT i;
86 DBREAL r;
87 DBFLT8 f;
88 } data;
89 };
90
91 static TDS_SERVER_TYPE infer_col_type(int sybtype);
92
93 static struct col_t *
col_init(struct col_t * pcol,int sybtype,int collen)94 col_init(struct col_t *pcol, int sybtype, int collen)
95 {
96 assert(pcol);
97
98 pcol->type = infer_col_type(sybtype);
99 if (pcol->type == TDS_INVALID_TYPE)
100 return NULL;
101 pcol->len = collen;
102
103 switch (sybtype) {
104 case 0:
105 pcol->len = 0;
106 return NULL;
107 case SYBDATETIME:
108 case SYBDATETIME4:
109 case SYBDATETIMN:
110 collen = 30;
111 /* fall through */
112 case SYBCHAR:
113 case SYBVARCHAR:
114 case SYBTEXT:
115 case SYBNTEXT:
116 pcol->len = collen;
117 if ((pcol->s = tds_new(char, 1+collen)) == NULL) {
118 return NULL;
119 }
120 break;
121 }
122 return pcol;
123 }
124
125 static void
col_free(struct col_t * p)126 col_free(struct col_t *p)
127 {
128 free(p->s);
129 memset(p, 0, sizeof(*p));
130 }
131
132 static bool
col_equal(const struct col_t * pc1,const struct col_t * pc2)133 col_equal(const struct col_t *pc1, const struct col_t *pc2)
134 {
135 assert( pc1 && pc2 );
136 assert( pc1->type == pc2->type );
137
138 switch (pc1->type) {
139
140 case SYBCHAR:
141 case SYBVARCHAR:
142 if( pc1->len != pc2->len)
143 return false;
144 return strncmp(pc1->s, pc2->s, pc1->len) == 0;
145 case SYBINT1:
146 return pc1->data.ti == pc2->data.ti;
147 case SYBINT2:
148 return pc1->data.si == pc2->data.si;
149 case SYBINT4:
150 return pc1->data.i == pc2->data.i;
151 case SYBFLT8:
152 return pc1->data.f == pc2->data.f;
153 case SYBREAL:
154 return pc1->data.r == pc2->data.r;
155
156 case SYBINTN:
157 case SYBDATETIME:
158 case SYBBIT:
159 case SYBTEXT:
160 case SYBNTEXT:
161 case SYBIMAGE:
162 case SYBMONEY4:
163 case SYBMONEY:
164 case SYBDATETIME4:
165 case SYBBINARY:
166 case SYBVOID:
167 case SYBVARBINARY:
168 case SYBBITN:
169 case SYBNUMERIC:
170 case SYBDECIMAL:
171 case SYBFLTN:
172 case SYBMONEYN:
173 case SYBDATETIMN:
174 assert( false && pc1->type );
175 break;
176 }
177 return false;
178 }
179
180 static void *
col_buffer(struct col_t * pcol)181 col_buffer(struct col_t *pcol)
182 {
183 switch (pcol->type) {
184
185 case SYBCHAR:
186 case SYBVARCHAR:
187 return pcol->s;
188 case SYBINT1:
189 return &pcol->data.ti;
190 case SYBINT2:
191 return &pcol->data.si;
192 case SYBINT4:
193 return &pcol->data.i;
194 case SYBFLT8:
195 return &pcol->data.f;
196 case SYBREAL:
197 return &pcol->data.r;
198
199 case SYBINTN:
200 case SYBDATETIME:
201 case SYBBIT:
202 case SYBTEXT:
203 case SYBNTEXT:
204 case SYBIMAGE:
205 case SYBMONEY4:
206 case SYBMONEY:
207 case SYBDATETIME4:
208 case SYBBINARY:
209 case SYBVOID:
210 case SYBVARBINARY:
211 case SYBBITN:
212 case SYBNUMERIC:
213 case SYBDECIMAL:
214 case SYBFLTN:
215 case SYBMONEYN:
216 case SYBDATETIMN:
217 assert( false && pcol->type );
218 break;
219 }
220 return NULL;
221
222 }
223
224 #if 0
225 static int
226 col_print(FILE* out, const struct col_t *pcol)
227 {
228 char *fmt;
229
230 switch (pcol->type) {
231
232 case SYBCHAR:
233 case SYBVARCHAR:
234 return (int) fwrite(pcol->s, pcol->len, 1, out);
235 case SYBINT1:
236 return fprintf(out, "%d", (int)pcol->ti);
237 case SYBINT2:
238 return fprintf(out, "%d", (int)pcol->si);
239 case SYBINT4:
240 return fprintf(out, "%d", (int)pcol->i);
241 case SYBFLT8:
242 return fprintf(out, "%f", pcol->f);
243 case SYBREAL:
244 return fprintf(out, "%f", (double)pcol->r);
245
246 case SYBINTN:
247 case SYBDATETIME:
248 case SYBBIT:
249 case SYBTEXT:
250 case SYBNTEXT:
251 case SYBIMAGE:
252 case SYBMONEY4:
253 case SYBMONEY:
254 case SYBDATETIME4:
255 case SYBBINARY:
256 case SYBVOID:
257 case SYBVARBINARY:
258 case SYBBITN:
259 case SYBNUMERIC:
260 case SYBDECIMAL:
261 case SYBFLTN:
262 case SYBMONEYN:
263 case SYBDATETIMN:
264 assert( false && pcol->type );
265 break;
266 }
267 return false;
268 }
269 #endif
270 static struct col_t *
col_cpy(struct col_t * pdest,const struct col_t * psrc)271 col_cpy(struct col_t *pdest, const struct col_t *psrc)
272 {
273 assert( pdest && psrc );
274 assert( psrc->len > 0 || psrc->null_indicator == -1);
275
276 memcpy(pdest, psrc, sizeof(*pdest));
277
278 if (psrc->s) {
279 assert(psrc->len >= 0);
280 if ((pdest->s = tds_new(char, psrc->len)) == NULL)
281 return NULL;
282 memcpy(pdest->s, psrc->s, psrc->len);
283 }
284
285 assert( pdest->len > 0 || pdest->null_indicator == -1);
286 return pdest;
287 }
288
289 static bool
col_null(const struct col_t * pcol)290 col_null( const struct col_t *pcol )
291 {
292 assert(pcol);
293 return pcol->null_indicator == -1;
294 }
295
296 static char *
string_value(const struct col_t * pcol)297 string_value(const struct col_t *pcol)
298 {
299 char *output = NULL;
300 int len = -1;
301
302 switch (pcol->type) {
303 case SYBCHAR:
304 case SYBVARCHAR:
305 if ((output = tds_new0(char, 1 + pcol->len)) == NULL)
306 return NULL;
307 strncpy(output, pcol->s, pcol->len);
308 return output;
309 break;
310 case SYBINT1:
311 len = asprintf(&output, "%d", (int)pcol->data.ti);
312 break;
313 case SYBINT2:
314 len = asprintf(&output, "%d", (int)pcol->data.si);
315 break;
316 case SYBINT4:
317 len = asprintf(&output, "%d", (int)pcol->data.i);
318 break;
319 case SYBFLT8:
320 len = asprintf(&output, "%f", pcol->data.f);
321 break;
322 case SYBREAL:
323 len = asprintf(&output, "%f", (double)pcol->data.r);
324 break;
325
326 default:
327 case SYBINTN:
328 case SYBDATETIME:
329 case SYBBIT:
330 case SYBTEXT:
331 case SYBNTEXT:
332 case SYBIMAGE:
333 case SYBMONEY4:
334 case SYBMONEY:
335 case SYBDATETIME4:
336 case SYBBINARY:
337 case SYBVOID:
338 case SYBVARBINARY:
339 case SYBBITN:
340 case SYBNUMERIC:
341 case SYBDECIMAL:
342 case SYBFLTN:
343 case SYBMONEYN:
344 case SYBDATETIMN:
345 assert( false && pcol->type );
346 return NULL;
347 break;
348 }
349
350 return len >= 0? output : NULL;
351 }
352
353 static char *
join(int argc,char * argv[],const char sep[])354 join(int argc, char *argv[], const char sep[])
355 {
356 size_t len = 0;
357 char **p, *output;
358
359 for (p=argv; p < argv + argc; p++) {
360 len += strlen(*p);
361 }
362
363 len += 1 + argc * strlen(sep); /* allows one too many */
364
365 output = tds_new0(char, len);
366 if (!output)
367 return NULL;
368
369 for (p=argv; p < argv + argc; p++) {
370 if (p != argv)
371 strcat(output, sep);
372 strcat(output, *p);
373 }
374 return output;
375 }
376
377 static TDS_SERVER_TYPE
infer_col_type(int sybtype)378 infer_col_type(int sybtype)
379 {
380 switch (sybtype) {
381 case SYBCHAR:
382 case SYBVARCHAR:
383 case SYBTEXT:
384 case SYBNTEXT:
385 return SYBCHAR;
386 case SYBDATETIME:
387 case SYBDATETIME4:
388 case SYBDATETIMN:
389 return SYBCHAR;
390 case SYBINT1:
391 case SYBBIT:
392 case SYBBITN:
393 return SYBINT1;
394 case SYBINT2:
395 return SYBINT2;
396 case SYBINT4:
397 case SYBINTN:
398 return SYBINT4;
399 case SYBFLT8:
400 case SYBMONEY4:
401 case SYBMONEY:
402 case SYBFLTN:
403 case SYBMONEYN:
404 case SYBNUMERIC:
405 case SYBDECIMAL:
406 return SYBFLT8;
407 case SYBREAL:
408 return SYBREAL;
409
410 case SYBIMAGE:
411 case SYBBINARY:
412 case SYBVOID:
413 case SYBVARBINARY:
414 assert( false && sybtype );
415 break;
416 }
417 return TDS_INVALID_TYPE;
418 }
419
420 static int
bind_type(int sybtype)421 bind_type(int sybtype)
422 {
423 switch (sybtype) {
424 case SYBCHAR:
425 case SYBVARCHAR:
426 case SYBTEXT:
427 case SYBNTEXT:
428 case SYBDATETIME:
429 case SYBDATETIME4:
430 case SYBDATETIMN:
431 return NTBSTRINGBIND;
432 case SYBINT1:
433 case SYBBIT:
434 case SYBBITN:
435 return TINYBIND;
436 case SYBINT2:
437 return SMALLBIND;
438 case SYBINT4:
439 case SYBINTN:
440 return INTBIND;
441 case SYBFLT8:
442 case SYBMONEY4:
443 case SYBMONEY:
444 case SYBFLTN:
445 case SYBMONEYN:
446 case SYBNUMERIC:
447 case SYBDECIMAL:
448 return FLT8BIND;
449 case SYBREAL:
450 return REALBIND;
451
452 case SYBIMAGE:
453 case SYBBINARY:
454 case SYBVOID:
455 case SYBVARBINARY:
456 assert( false && sybtype );
457 break;
458 }
459 return 0;
460 }
461
462 typedef struct KEY_T
463 {
464 int nkeys;
465 struct col_t *keys;
466 } KEY_T;
467
468 static bool
key_equal(const KEY_T * a,const KEY_T * b)469 key_equal(const KEY_T *a, const KEY_T *b)
470 {
471 int i;
472
473 assert(a && b);
474 assert(a->keys && b->keys);
475 assert(a->nkeys == b->nkeys);
476
477 for (i=0; i < a->nkeys; i++) {
478 if (! col_equal(a->keys+i, b->keys+i))
479 return false;
480 }
481 return true;
482 }
483
484
485 static void
key_free(KEY_T * p)486 key_free(KEY_T *p)
487 {
488 col_free(p->keys);
489 free(p->keys);
490 memset(p, 0, sizeof(*p));
491 }
492
493 static KEY_T *
key_cpy(KEY_T * pdest,const KEY_T * psrc)494 key_cpy(KEY_T *pdest, const KEY_T *psrc)
495 {
496 int i;
497
498 assert( pdest && psrc );
499
500 if ((pdest->keys = tds_new0(struct col_t, psrc->nkeys)) == NULL)
501 return NULL;
502
503 pdest->nkeys = psrc->nkeys;
504
505 for( i=0; i < psrc->nkeys; i++) {
506 if (NULL == col_cpy(pdest->keys+i, psrc->keys+i))
507 return NULL;
508 }
509
510 return pdest;
511 }
512
513
514 static char *
make_col_name(DBPROCESS * dbproc,const KEY_T * k)515 make_col_name(DBPROCESS *dbproc, const KEY_T *k)
516 {
517 const struct col_t *pc;
518 char **names, **s, *output;
519
520 assert(k);
521 assert(k->nkeys);
522 assert(k->keys);
523
524 s = names = tds_new0(char *, k->nkeys);
525 if (!s) {
526 dbperror(dbproc, SYBEMEM, errno);
527 return NULL;
528 }
529 for(pc=k->keys; pc < k->keys + k->nkeys; pc++) {
530 *s++ = strdup(string_value(pc));
531 }
532
533 output = join(k->nkeys, names, "/");
534
535 for(s=names; s < names + k->nkeys; s++) {
536 free(*s);
537 }
538 free(names);
539
540 return output;
541 }
542
543
544 typedef struct agg_t
545 {
546 KEY_T row_key, col_key;
547 struct col_t value;
548 } AGG_T;
549
550 #if 0
551 static bool
552 agg_key_equal(const void *a, const void *b)
553 {
554 int i;
555 const AGG_T *p1 = a, *p2 = b;
556
557 assert(p1 && p2);
558 assert(p1->row_key.keys && p2->row_key.keys);
559 assert(p1->row_key.nkeys == p2->row_key.nkeys);
560
561 for( i=0; i < p1->row_key.nkeys; i++ ) {
562 if (! col_equal(p1->row_key.keys+i, p2->row_key.keys+i))
563 return false;
564 }
565
566 return true;
567 }
568 #endif
569
570 static bool
agg_next(const AGG_T * p1,const AGG_T * p2)571 agg_next(const AGG_T *p1, const AGG_T *p2)
572 {
573 int i;
574
575 assert(p1 && p2);
576
577 if (p1->row_key.keys == NULL || p2->row_key.keys == NULL)
578 return false;
579
580 assert(p1->row_key.keys && p2->row_key.keys);
581 assert(p1->row_key.nkeys == p2->row_key.nkeys);
582
583 assert(p1->col_key.keys && p2->col_key.keys);
584 assert(p1->col_key.nkeys == p2->col_key.nkeys);
585
586 for( i=0; i < p1->row_key.nkeys; i++ ) {
587 assert(p1->row_key.keys[i].type);
588 assert(p2->row_key.keys[i].type);
589 if (p1->row_key.keys[i].type != p2->row_key.keys[i].type)
590 return false;
591 }
592
593 for( i=0; i < p1->row_key.nkeys; i++ ) {
594 if (! col_equal(p1->row_key.keys+i, p2->row_key.keys+i))
595 return false;
596 }
597
598 for( i=0; i < p1->col_key.nkeys; i++ ) {
599 if (p1->col_key.keys[i].type != p2->col_key.keys[i].type)
600 return false;
601 }
602
603 for( i=0; i < p1->col_key.nkeys; i++ ) {
604 if (! col_equal(p1->col_key.keys+i, p2->col_key.keys+i))
605 return false;
606 }
607
608 return true;
609 }
610
611 static void
agg_free(AGG_T * p)612 agg_free(AGG_T *p)
613 {
614 key_free(&p->row_key);
615 key_free(&p->col_key);
616 col_free(&p->value);
617 }
618
619 static bool
agg_equal(const AGG_T * p1,const AGG_T * p2)620 agg_equal(const AGG_T *p1, const AGG_T *p2)
621 {
622 int i;
623
624 assert(p1 && p2);
625 assert(p1->row_key.keys && p1->col_key.keys);
626 assert(p2->row_key.keys && p2->col_key.keys);
627
628 assert(p1->row_key.nkeys == p2->row_key.nkeys);
629 assert(p1->col_key.nkeys == p2->col_key.nkeys);
630
631 /* todo: use key_equal */
632 for( i=0; i < p1->row_key.nkeys; i++ ) {
633 if (! col_equal(p1->row_key.keys+i, p2->row_key.keys+i))
634 return false;
635 }
636 for( i=0; i < p1->col_key.nkeys; i++ ) {
637 if (! col_equal(p1->col_key.keys+i, p2->col_key.keys+i))
638 return false;
639 }
640 return true;
641 }
642
643 #undef TEST_MALLOC
644 #define TEST_MALLOC(dest,type) \
645 {if (!(dest = (type*)calloc(1, sizeof(type)))) goto Cleanup;}
646
647 #undef TEST_CALLOC
648 #define TEST_CALLOC(dest,type,n) \
649 {if (!(dest = (type*)calloc((n), sizeof(type)))) goto Cleanup;}
650
651 #define tds_alloc_column() ((TDSCOLUMN*) calloc(1, sizeof(TDSCOLUMN)))
652
653 static TDSRESULTINFO *
alloc_results(size_t num_cols)654 alloc_results(size_t num_cols)
655 {
656 TDSRESULTINFO *res_info;
657 TDSCOLUMN **ppcol;
658
659 TEST_MALLOC(res_info, TDSRESULTINFO);
660 res_info->ref_count = 1;
661 TEST_CALLOC(res_info->columns, TDSCOLUMN *, num_cols);
662
663 for (ppcol = res_info->columns; ppcol < res_info->columns + num_cols; ppcol++)
664 if ((*ppcol = tds_alloc_column()) == NULL)
665 goto Cleanup;
666 res_info->num_cols = num_cols;
667 res_info->row_size = 0;
668 return res_info;
669
670 Cleanup:
671 tds_free_results(res_info);
672 return NULL;
673 }
674
675 static TDSRET
set_result_column(TDSSOCKET * tds,TDSCOLUMN * curcol,const char name[],const struct col_t * pvalue)676 set_result_column(TDSSOCKET * tds, TDSCOLUMN * curcol, const char name[], const struct col_t *pvalue)
677 {
678 assert(curcol && pvalue);
679 assert(name);
680
681 curcol->column_usertype = pvalue->type;
682 curcol->column_nullable = true;
683 curcol->column_writeable = false;
684 curcol->column_identity = false;
685
686 tds_set_column_type(tds->conn, curcol, pvalue->type); /* sets "cardinal" type */
687
688 curcol->column_timestamp = (curcol->column_type == SYBBINARY && curcol->column_usertype == TDS_UT_TIMESTAMP);
689
690 #if 0
691 curcol->funcs->get_info(tds, curcol);
692 #endif
693 curcol->on_server.column_size = curcol->column_size;
694
695 if (!tds_dstr_copy(&curcol->column_name, name))
696 return TDS_FAIL;
697
698 tdsdump_log(TDS_DBG_INFO1, "tds7_get_data_info: \n"
699 "\tcolname = %s\n"
700 "\ttype = %d (%s)\n"
701 "\tserver's type = %d (%s)\n"
702 "\tcolumn_varint_size = %d\n"
703 "\tcolumn_size = %d (%d on server)\n",
704 tds_dstr_cstr(&curcol->column_name),
705 curcol->column_type, tds_prtype(curcol->column_type),
706 curcol->on_server.column_type, tds_prtype(curcol->on_server.column_type),
707 curcol->column_varint_size,
708 curcol->column_size, curcol->on_server.column_size);
709
710 return TDS_SUCCESS;
711 }
712
713 struct metadata_t { KEY_T *pacross; char *name; struct col_t col; };
714
715
716 static bool
reinit_results(TDSSOCKET * tds,size_t num_cols,const struct metadata_t meta[])717 reinit_results(TDSSOCKET * tds, size_t num_cols, const struct metadata_t meta[])
718 {
719 TDSRESULTINFO *info;
720 int i;
721
722 assert(tds);
723 assert(num_cols);
724 assert(meta);
725
726 tds_free_all_results(tds);
727 tds->rows_affected = TDS_NO_COUNT;
728
729 if ((info = alloc_results(num_cols)) == NULL)
730 return false;
731
732 tds_set_current_results(tds, info);
733 if (tds->cur_cursor) {
734 tds_free_results(tds->cur_cursor->res_info);
735 tds->cur_cursor->res_info = info;
736 tdsdump_log(TDS_DBG_INFO1, "set current_results to cursor->res_info\n");
737 } else {
738 tds->res_info = info;
739 tdsdump_log(TDS_DBG_INFO1, "set current_results (%u column%s) to tds->res_info\n", (unsigned) num_cols, (num_cols==1? "":"s"));
740 }
741
742 tdsdump_log(TDS_DBG_INFO1, "setting up %u columns\n", (unsigned) num_cols);
743
744 for (i = 0; i < num_cols; i++) {
745 set_result_column(tds, info->columns[i], meta[i].name, &meta[i].col);
746 info->columns[i]->bcp_terminator = (char*) meta[i].pacross; /* overload available pointer */
747 }
748
749 if (num_cols > 0) {
750 static const char dashes[31] = "------------------------------";
751 tdsdump_log(TDS_DBG_INFO1, " %-20s %-15s %-15s %-7s\n", "name", "size/wsize", "type/wtype", "utype");
752 tdsdump_log(TDS_DBG_INFO1, " %-20s %15s %15s %7s\n", dashes+10, dashes+30-15, dashes+30-15, dashes+30-7);
753 }
754 for (i = 0; i < num_cols; i++) {
755 TDSCOLUMN *curcol = info->columns[i];
756
757 tdsdump_log(TDS_DBG_INFO1, " %-20s %7d/%-7d %7d/%-7d %7d\n",
758 tds_dstr_cstr(&curcol->column_name),
759 curcol->column_size, curcol->on_server.column_size,
760 curcol->column_type, curcol->on_server.column_type,
761 curcol->column_usertype);
762 }
763
764 #if 1
765 /* all done now allocate a row for tds_process_row to use */
766 if (TDS_FAILED(tds_alloc_row(info))) return false;
767 #endif
768 return true;
769 }
770
771 typedef struct pivot_t
772 {
773 DBPROCESS *dbproc;
774 STATUS status;
775 DB_RESULT_STATE dbresults_state;
776
777 AGG_T *output;
778 KEY_T *across;
779 size_t nout, nacross;
780 } PIVOT_T;
781
782 static bool
pivot_key_equal(const PIVOT_T * a,const PIVOT_T * b)783 pivot_key_equal(const PIVOT_T *a, const PIVOT_T *b)
784 {
785 assert(a && b);
786
787 return a->dbproc == b->dbproc;
788 }
789
790 static PIVOT_T *pivots = NULL;
791 static size_t npivots = 0;
792
793 PIVOT_T *
dbrows_pivoted(DBPROCESS * dbproc)794 dbrows_pivoted(DBPROCESS *dbproc)
795 {
796 PIVOT_T P;
797
798 assert(dbproc);
799 P.dbproc = dbproc;
800
801 return (PIVOT_T *) tds_find(&P, pivots, npivots, sizeof(*pivots), (compare_func) pivot_key_equal);
802 }
803
804 STATUS
dbnextrow_pivoted(DBPROCESS * dbproc,PIVOT_T * pp)805 dbnextrow_pivoted(DBPROCESS *dbproc, PIVOT_T *pp)
806 {
807 int i;
808 AGG_T candidate, *pout;
809
810 assert(pp);
811 assert(dbproc && dbproc->tds_socket);
812 assert(dbproc->tds_socket->res_info);
813 assert(dbproc->tds_socket->res_info->columns || 0 == dbproc->tds_socket->res_info->num_cols);
814
815 for (pout = pp->output; pout < pp->output + pp->nout; pout++) {
816 if (pout->row_key.keys != NULL)
817 break;
818 }
819
820 if (pout == pp->output + pp->nout) {
821 dbproc->dbresults_state = _DB_RES_NEXT_RESULT;
822 return NO_MORE_ROWS;
823 }
824
825 memset(&candidate, 0, sizeof(candidate));
826 key_cpy(&candidate.row_key, &pout->row_key);
827
828 /* "buffer_transfer_bound_data" */
829 for (i = 0; i < dbproc->tds_socket->res_info->num_cols; i++) {
830 struct col_t *pval = NULL;
831 TDSCOLUMN *pcol = dbproc->tds_socket->res_info->columns[i];
832 assert(pcol);
833
834 if (pcol->column_nullbind) {
835 if (pcol->column_cur_size < 0) {
836 *(DBINT *)(pcol->column_nullbind) = -1;
837 } else {
838 *(DBINT *)(pcol->column_nullbind) = 0;
839 }
840 }
841 if (!pcol->column_varaddr) {
842 fprintf(stderr, "no pcol->column_varaddr in col %d\n", i);
843 continue;
844 }
845
846 /* find column in output */
847 if (pcol->bcp_terminator == NULL) { /* not a cross-tab column */
848 pval = &candidate.row_key.keys[i];
849 } else {
850 AGG_T *pcan;
851 key_cpy(&candidate.col_key, (KEY_T *) pcol->bcp_terminator);
852 if ((pcan = tds_find(&candidate, pout, pp->output + pp->nout - pout,
853 sizeof(*pp->output), (compare_func) agg_next)) != NULL) {
854 /* flag this output as used */
855 pout->row_key.keys = NULL;
856 pval = &pcan->value;
857 }
858 }
859
860 if (!pval || col_null(pval)) { /* nothing in output for this x,y location */
861 dbgetnull(dbproc, pcol->column_bindtype, pcol->column_bindlen, (BYTE *) pcol->column_varaddr);
862 continue;
863 }
864
865 assert(pval);
866
867 #if 0
868 printf("\ncopying col %d, type %d/%d, len %d to %p ", i, pval->type, pcol->column_type, pval->len, pcol->column_varaddr);
869 switch (pval->type) {
870 case 48:
871 printf("value %d", (int)pval->ti); break;
872 case 56:
873 printf("value %d", (int)pval->si); break;
874 }
875 printf("\n");
876 #endif
877 pcol->column_size = pval->len;
878 pcol->column_data = col_buffer(pval);
879
880 copy_data_to_host_var( dbproc,
881 pval->type,
882 col_buffer(pval),
883 pval->len,
884 (BYTE *) pcol->column_varaddr,
885 pcol->column_bindlen,
886 pcol->column_bindtype,
887 (DBINT*) pcol->column_nullbind
888 );
889 }
890
891 return REG_ROW;
892 }
893
894 /**
895 * Pivot the rows, creating a new resultset
896 *
897 * Call dbpivot() immediately after dbresults(). It calls dbnextrow() as long as
898 * it returns REG_ROW, transforming the results into a cross-tab report.
899 * dbpivot() modifies the metadata such that DB-Library can be used tranparently:
900 * retrieve the rows as usual with dbnumcols(), dbnextrow(), etc.
901 *
902 * @dbproc, our old friend
903 * @nkeys the number of left-edge columns to group by
904 * @keys an array of left-edge columns to group by
905 * @ncols the number of top-edge columns to group by
906 * @cols an array of top-edge columns to group by
907 * @func the aggregation function to use
908 * @val the number of the column to which @func is applied
909 *
910 * @returns the return code from the final call to dbnextrow().
911 * Success is normally indicated by NO_MORE_ROWS.
912 */
913 RETCODE
dbpivot(DBPROCESS * dbproc,int nkeys,int * keys,int ncols,int * cols,DBPIVOT_FUNC func,int val)914 dbpivot(DBPROCESS *dbproc, int nkeys, int *keys, int ncols, int *cols, DBPIVOT_FUNC func, int val)
915 {
916 enum { logalot = 1 };
917 PIVOT_T P, *pp;
918 AGG_T input, *pout = NULL;
919 struct metadata_t *metadata, *pmeta;
920 size_t i, nmeta = 0;
921
922 tdsdump_log(TDS_DBG_FUNC, "dbpivot(%p, %d,%p, %d,%p, %p, %d)\n", dbproc, nkeys, keys, ncols, cols, func, val);
923 if (logalot) {
924 char buffer[1024] = {'\0'}, *s = buffer;
925 const static char *const names[2] = { "\tkeys (down)", "\n\tcols (across)" };
926 int *p = keys, *pend = p + nkeys;
927
928 for (i=0; i < 2; i++) {
929 const char *sep = "";
930 s += sprintf(s, "%s: ", names[i]);
931 for ( ; p < pend; p++) {
932 s += sprintf(s, "%s%d", sep, *p);
933 sep = ", ";
934 }
935 p = cols;
936 pend = p + ncols;
937 assert(s < buffer + sizeof(buffer));
938 }
939 tdsdump_log(TDS_DBG_FUNC, "%s\n", buffer);
940 }
941
942 memset(&input, 0, sizeof(input));
943
944 P.dbproc = dbproc;
945 if ((pp = tds_find(&P, pivots, npivots, sizeof(*pivots), (compare_func) pivot_key_equal)) == NULL ) {
946 pp = TDS_RESIZE(pivots, 1 + npivots);
947 if (!pp)
948 return FAIL;
949 pp += npivots++;
950 } else {
951 agg_free(pp->output);
952 key_free(pp->across);
953 }
954 memset(pp, 0, sizeof(*pp));
955
956 if ((input.row_key.keys = tds_new0(struct col_t, nkeys)) == NULL)
957 return FAIL;
958 input.row_key.nkeys = nkeys;
959 for (i=0; i < nkeys; i++) {
960 int type = dbcoltype(dbproc, keys[i]);
961 int len = dbcollen(dbproc, keys[i]);
962 assert(type && len);
963
964 if (!col_init(input.row_key.keys+i, type, len))
965 return FAIL;
966 if (FAIL == dbbind(dbproc, keys[i], bind_type(type), input.row_key.keys[i].len, col_buffer(input.row_key.keys+i)))
967 return FAIL;
968 if (FAIL == dbnullbind(dbproc, keys[i], &input.row_key.keys[i].null_indicator))
969 return FAIL;
970 }
971
972 if ((input.col_key.keys = tds_new0(struct col_t, ncols)) == NULL)
973 return FAIL;
974 input.col_key.nkeys = ncols;
975 for (i=0; i < ncols; i++) {
976 int type = dbcoltype(dbproc, cols[i]);
977 int len = dbcollen(dbproc, cols[i]);
978 assert(type && len);
979
980 if (!col_init(input.col_key.keys+i, type, len))
981 return FAIL;
982 if (FAIL == dbbind(dbproc, cols[i], bind_type(type), input.col_key.keys[i].len, col_buffer(input.col_key.keys+i)))
983 return FAIL;
984 if (FAIL == dbnullbind(dbproc, cols[i], &input.col_key.keys[i].null_indicator))
985 return FAIL;
986 }
987
988 /* value */ {
989 int type = dbcoltype(dbproc, val);
990 int len = dbcollen(dbproc, val);
991 assert(type && len);
992
993 if (!col_init(&input.value, type, len))
994 return FAIL;
995 if (FAIL == dbbind(dbproc, val, bind_type(type), input.value.len, col_buffer(&input.value)))
996 return FAIL;
997 if (FAIL == dbnullbind(dbproc, val, &input.value.null_indicator))
998 return FAIL;
999 }
1000
1001 while ((pp->status = dbnextrow(dbproc)) == REG_ROW) {
1002 /* add to unique list of crosstab columns */
1003 if (tds_find(&input.col_key, pp->across, pp->nacross, sizeof(*pp->across), (compare_func) key_equal) == NULL) {
1004 if (!TDS_RESIZE(pp->across, 1 + pp->nacross))
1005 return FAIL;
1006 key_cpy(pp->across + pp->nacross, &input.col_key);
1007 }
1008 assert(pp->across);
1009
1010 if ((pout = tds_find(&input, pp->output, pp->nout, sizeof(*pp->output), (compare_func) agg_equal)) == NULL ) {
1011 if (!TDS_RESIZE(pp->output, 1 + pp->nout))
1012 return FAIL;
1013 pout = pp->output + pp->nout++;
1014
1015
1016 if ((pout->row_key.keys = tds_new0(struct col_t, input.row_key.nkeys)) == NULL)
1017 return FAIL;
1018 key_cpy(&pout->row_key, &input.row_key);
1019
1020 if ((pout->col_key.keys = tds_new0(struct col_t, input.col_key.nkeys)) == NULL)
1021 return FAIL;
1022 key_cpy(&pout->col_key, &input.col_key);
1023
1024 if (!col_init(&pout->value, input.value.type, input.value.len))
1025 return FAIL;
1026 }
1027
1028 func(&pout->value, &input.value);
1029
1030 }
1031
1032 /* Mark this proc as pivoted, so that dbnextrow() sees it when the application calls it */
1033 pp->dbproc = dbproc;
1034 pp->dbresults_state = dbproc->dbresults_state;
1035 dbproc->dbresults_state = pp->output < pout? _DB_RES_RESULTSET_ROWS : _DB_RES_RESULTSET_EMPTY;
1036
1037 /*
1038 * Initialize new metadata
1039 */
1040 nmeta = input.row_key.nkeys + pp->nacross;
1041 metadata = tds_new0(struct metadata_t, nmeta);
1042 if (!metadata) {
1043 dbperror(dbproc, SYBEMEM, errno);
1044 return FAIL;
1045 }
1046 assert(pp->across || pp->nacross == 0);
1047
1048 /* key columns are passed through as-is, verbatim */
1049 for (i=0; i < input.row_key.nkeys; i++) {
1050 assert(i < nkeys);
1051 metadata[i].name = strdup(dbcolname(dbproc, keys[i]));
1052 metadata[i].pacross = NULL;
1053 col_cpy(&metadata[i].col, input.row_key.keys+i);
1054 }
1055
1056 /* pivoted columms are found in the "across" data */
1057 for (i=0, pmeta = metadata + input.row_key.nkeys; i < pp->nacross; i++) {
1058 struct col_t col;
1059 if (!col_init(&col, SYBFLT8, sizeof(double)))
1060 return FAIL;
1061 assert(pmeta + i < metadata + nmeta);
1062 pmeta[i].name = make_col_name(dbproc, pp->across+i);
1063 if (!pmeta[i].name)
1064 return FAIL;
1065 assert(pp->across);
1066 pmeta[i].pacross = pp->across + i;
1067 col_cpy(&pmeta[i].col, pp->nout? &pp->output[0].value : &col);
1068 }
1069
1070 if (!reinit_results(dbproc->tds_socket, nmeta, metadata)) {
1071 return FAIL;
1072 }
1073
1074 return SUCCEED;
1075
1076 #if 0
1077 for (pp->pout=pp->output; pp->pout < pp->output + pp->nout; pp->pout++) {
1078 char name[256] = {0};
1079
1080 assert(pp->pout->col_key.keys[0].len < sizeof(name));
1081 memset(name, '\0', sizeof(name));
1082 memcpy(name, pp->pout->col_key.keys[0].s, pp->pout->col_key.keys[0].len),
1083 printf("%5d %-30s %5d\n", pp->pout->row_key.keys[0].i,
1084 name,
1085 pp->pout->value.i );
1086 }
1087 exit(1);
1088 #endif
1089 }
1090
1091 /*
1092 * Aggregation functions
1093 */
1094
1095 void
dbpivot_count(struct col_t * tgt,const struct col_t * src)1096 dbpivot_count (struct col_t *tgt, const struct col_t *src)
1097 {
1098 assert( tgt && src);
1099 assert (src->type);
1100
1101 tgt->type = SYBINT4;
1102
1103 if (! col_null(src))
1104 tgt->data.i++;
1105 }
1106
1107 void
dbpivot_sum(struct col_t * tgt,const struct col_t * src)1108 dbpivot_sum (struct col_t *tgt, const struct col_t *src)
1109 {
1110 assert( tgt && src);
1111 assert (src->type);
1112
1113 tgt->type = src->type;
1114
1115 if (col_null(src))
1116 return;
1117
1118 switch (src->type) {
1119 case SYBINT1:
1120 tgt->data.ti += src->data.ti;
1121 break;
1122 case SYBINT2:
1123 tgt->data.si += src->data.si;
1124 break;
1125 case SYBINT4:
1126 tgt->data.i += src->data.i;
1127 break;
1128 case SYBFLT8:
1129 tgt->data.f += src->data.f;
1130 break;
1131 case SYBREAL:
1132 tgt->data.r += src->data.r;
1133 break;
1134
1135 case SYBCHAR:
1136 case SYBVARCHAR:
1137 case SYBINTN:
1138 case SYBDATETIME:
1139 case SYBBIT:
1140 case SYBTEXT:
1141 case SYBNTEXT:
1142 case SYBIMAGE:
1143 case SYBMONEY4:
1144 case SYBMONEY:
1145 case SYBDATETIME4:
1146 case SYBBINARY:
1147 case SYBVOID:
1148 case SYBVARBINARY:
1149 case SYBBITN:
1150 case SYBNUMERIC:
1151 case SYBDECIMAL:
1152 case SYBFLTN:
1153 case SYBMONEYN:
1154 case SYBDATETIMN:
1155 default:
1156 tdsdump_log(TDS_DBG_INFO1, "dbpivot_sum(): invalid operand %d\n", src->type);
1157 tgt->type = SYBINT4;
1158 tgt->data.i = 0;
1159 break;
1160 }
1161 }
1162
1163 void
dbpivot_min(struct col_t * tgt,const struct col_t * src)1164 dbpivot_min (struct col_t *tgt, const struct col_t *src)
1165 {
1166 assert( tgt && src);
1167 assert (src->type);
1168
1169 tgt->type = src->type;
1170
1171 if (col_null(src))
1172 return;
1173
1174 switch (src->type) {
1175 case SYBINT1:
1176 tgt->data.ti = tgt->data.ti < src->data.ti? tgt->data.ti : src->data.ti;
1177 break;
1178 case SYBINT2:
1179 tgt->data.si = tgt->data.si < src->data.si? tgt->data.si : src->data.si;
1180 break;
1181 case SYBINT4:
1182 tgt->data.i = tgt->data.i < src->data.i? tgt->data.i : src->data.i;
1183 break;
1184 case SYBFLT8:
1185 tgt->data.f = tgt->data.f < src->data.f? tgt->data.f : src->data.f;
1186 break;
1187 case SYBREAL:
1188 tgt->data.r = tgt->data.r < src->data.r? tgt->data.r : src->data.r;
1189 break;
1190
1191 case SYBCHAR:
1192 case SYBVARCHAR:
1193 case SYBINTN:
1194 case SYBDATETIME:
1195 case SYBBIT:
1196 case SYBTEXT:
1197 case SYBNTEXT:
1198 case SYBIMAGE:
1199 case SYBMONEY4:
1200 case SYBMONEY:
1201 case SYBDATETIME4:
1202 case SYBBINARY:
1203 case SYBVOID:
1204 case SYBVARBINARY:
1205 case SYBBITN:
1206 case SYBNUMERIC:
1207 case SYBDECIMAL:
1208 case SYBFLTN:
1209 case SYBMONEYN:
1210 case SYBDATETIMN:
1211 default:
1212 tdsdump_log(TDS_DBG_INFO1, "dbpivot_sum(): invalid operand %d\n", src->type);
1213 tgt->type = SYBINT4;
1214 tgt->data.i = 0;
1215 break;
1216 }
1217 }
1218
1219 void
dbpivot_max(struct col_t * tgt,const struct col_t * src)1220 dbpivot_max (struct col_t *tgt, const struct col_t *src)
1221 {
1222 assert( tgt && src);
1223 assert (src->type);
1224
1225 tgt->type = src->type;
1226
1227 if (col_null(src))
1228 return;
1229
1230 switch (src->type) {
1231 case SYBINT1:
1232 tgt->data.ti = tgt->data.ti > src->data.ti? tgt->data.ti : src->data.ti;
1233 break;
1234 case SYBINT2:
1235 tgt->data.si = tgt->data.si > src->data.si? tgt->data.si : src->data.si;
1236 break;
1237 case SYBINT4:
1238 tgt->data.i = tgt->data.i > src->data.i? tgt->data.i : src->data.i;
1239 break;
1240 case SYBFLT8:
1241 tgt->data.f = tgt->data.f > src->data.f? tgt->data.f : src->data.f;
1242 break;
1243 case SYBREAL:
1244 tgt->data.r = tgt->data.r > src->data.r? tgt->data.r : src->data.r;
1245 break;
1246
1247 case SYBCHAR:
1248 case SYBVARCHAR:
1249 case SYBINTN:
1250 case SYBDATETIME:
1251 case SYBBIT:
1252 case SYBTEXT:
1253 case SYBNTEXT:
1254 case SYBIMAGE:
1255 case SYBMONEY4:
1256 case SYBMONEY:
1257 case SYBDATETIME4:
1258 case SYBBINARY:
1259 case SYBVOID:
1260 case SYBVARBINARY:
1261 case SYBBITN:
1262 case SYBNUMERIC:
1263 case SYBDECIMAL:
1264 case SYBFLTN:
1265 case SYBMONEYN:
1266 case SYBDATETIMN:
1267 default:
1268 tdsdump_log(TDS_DBG_INFO1, "dbpivot_sum(): invalid operand %d\n", src->type);
1269 tgt->type = SYBINT4;
1270 tgt->data.i = 0;
1271 break;
1272 }
1273 }
1274
1275 static const struct name_t {
1276 char name[14];
1277 DBPIVOT_FUNC func;
1278 } names[] =
1279 { { "count", dbpivot_count }
1280 , { "sum", dbpivot_sum }
1281 , { "min", dbpivot_min }
1282 , { "max", dbpivot_max }
1283 };
1284
1285 static bool
name_equal(const struct name_t * n1,const struct name_t * n2)1286 name_equal( const struct name_t *n1, const struct name_t *n2 )
1287 {
1288 assert(n1 && n2);
1289 return strcmp(n1->name, n2->name) == 0;
1290 }
1291
1292 DBPIVOT_FUNC
dbpivot_lookup_name(const char name[])1293 dbpivot_lookup_name( const char name[] )
1294 {
1295 struct name_t *n = TDS_FIND(name, names, (compare_func) name_equal);
1296
1297 return n ? n->func : NULL;
1298 }
1299