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