Project

General

Profile

Bug #3786 » MySQL.C

Drus Kor, 02/14/2015 12:59 PM

 
1
/*
2
 * Copyright (C) 2008 Emweb bvba, Kessel-Lo, Belgium.
3
 *
4
 * See the LICENSE file for terms of use.
5
 *
6
 * Contributed by: Paul Harrison
7
 */
8
#include "Wt/Dbo/backend/MySQL"
9
#include "Wt/Dbo/Exception"
10

    
11
#include <boost/lexical_cast.hpp>
12
#include <iostream>
13
#include <vector>
14
#include <sstream>
15

    
16
#include <boost/date_time/posix_time/posix_time.hpp>
17

    
18
#ifdef WT_WIN32
19
#define snprintf _snprintf
20
#include <winsock2.h>
21
#endif
22
#include <mysql.h>
23
#include <mysql/errmsg.h>
24

    
25
#define BYTEAOID 17
26

    
27
#define DEBUG(x)
28
//#define DEBUG(x) x
29

    
30
namespace Wt {
31
  namespace Dbo {
32
    namespace backend {
33

    
34
        template< typename Callback, typename Param, typename Ptr, typename Fn  >
35
        int callWithReconnect(Callback cb, const Param rq, Ptr ptr, Fn fn ){
36
            int tryCount = 3;
37
            int retCode  = 0;
38
            do
39
            {
40
                retCode =  cb(rq);
41
                std::cerr << "callWithReconnect code: " << retCode << std::endl;
42
                if(retCode != 0) break;
43
                (ptr->*fn)();
44
            } while(retCode && --tryCount);
45
            return retCode;
46
        }
47

    
48
        template< typename Callback, typename Conn, typename Param, typename Ptr, typename Fn >
49
        int callWithReconnect(Callback cb, Conn conn_, const Param rq, Ptr ptr, Fn fn ){
50
            int tryCount = 3;
51
            int retCode  = 0;
52
            do
53
            {
54
                retCode =  cb(conn_,rq);
55
                std::cerr << "callWithReconnect code: " << retCode << std::endl;
56
                if(retCode != CR_SERVER_LOST) break;
57
                (ptr->*fn)();
58
            } while(retCode && --tryCount);
59
            return retCode;
60
        }
61

    
62
class MySQLException : public Exception
63
{
64
  public:
65
  MySQLException(const std::string& msg)
66
  : Exception(msg)
67
  { }
68
};
69

    
70
class MySQL_impl{
71
public:
72
  MYSQL *mysql;
73

    
74
  MySQL_impl():
75
    mysql(NULL)
76
  {}
77

    
78
  MySQL_impl(MYSQL *newmysql):
79
    mysql(newmysql)
80
  {}
81

    
82
  MYSQL *ptr()
83
  {
84
    return mysql;
85
  }
86

    
87
};
88

    
89
namespace {
90
  struct LibraryInitializer {
91
    LibraryInitializer() {
92
#if !defined(MYSQL_NO_LIBRARY_INIT)
93
      mysql_library_init(0, NULL, NULL);
94
#endif
95
    }
96
  } libraryInitializer;
97
}
98

    
99
/*!
100
 * \brief MySQL prepared statement.
101
 * @todo should the getResult requests all be type checked...
102
 */
103
class MySQLStatement : public SqlStatement
104
{
105
  public:
106
    MySQLStatement(MySQL& conn, const std::string& sql)
107
    : conn_(conn),
108
      sql_(sql)
109
    {
110
      lastId_ = -1;
111
      row_ = affectedRows_ = 0;
112
      result_ = 0;
113
      out_pars_ = 0;
114
      errors_ = 0;
115
      lastOutCount_ = 0;
116

    
117
      stmt_ =  mysql_stmt_init(conn_.connection()->mysql);
118
      mysql_stmt_attr_set(stmt_, STMT_ATTR_UPDATE_MAX_LENGTH, &mysqltrue_);
119
      if(mysql_stmt_prepare(stmt_, sql_.c_str(), sql_.length()) != 0) {
120
        throw MySQLException("error creating prepared statement: '"
121
			      + sql + "': " + mysql_stmt_error(stmt_));
122
      }
123
      if(mysql_stmt_param_count(stmt_)> 0)
124
      {
125
          in_pars_ = (MYSQL_BIND *)malloc(
126
                sizeof(struct st_mysql_bind) * mysql_stmt_param_count(stmt_));
127
          memset(in_pars_, 0,
128
                 sizeof(struct st_mysql_bind)*mysql_stmt_param_count(stmt_));
129
      }
130
      else{
131
        in_pars_ = 0;
132
      }
133
//      snprintf(name_, 64, "SQL%p%08X", this, rand());
134

    
135

    
136
      DEBUG(std::cerr <<  " new SQLStatement for: " << sql_ << std::endl);
137

    
138
      state_ = Done;
139
    }
140

    
141
    virtual ~MySQLStatement()
142
    {
143
      DEBUG(std::cerr << "closing prepared stmt " << sql_ << std::endl);
144
      for(unsigned int i = 0;   i < mysql_stmt_param_count(stmt_) ; ++i)
145
          freeColumn(i);
146
      if (in_pars_) free(in_pars_);
147

    
148
      if(out_pars_) free_outpars();
149

    
150
      if (errors_) delete[] errors_;
151

    
152
      if(result_) {
153
        mysql_free_result(result_);
154
      }
155

    
156
      mysql_stmt_close(stmt_);
157
      stmt_ = 0;
158
    }
159

    
160
    virtual void reset()
161
    {
162
      state_ = Done;
163
      has_truncation_ = false;
164
    }
165

    
166
    virtual void bind(int column, const std::string& value)
167
    {
168
      if (in_pars_ == 0)
169
        throw MySQLException(std::string("Try to bind too much?"));
170

    
171
      DEBUG(std::cerr << this << " bind " << column << " "
172
            << value << std::endl);
173

    
174
      unsigned long * len = (unsigned long *)malloc(sizeof(unsigned long));
175

    
176
      char * data;
177
      //memset(&in_pars_[column], 0, sizeofin_pars_[column]);// Check
178
      in_pars_[column].buffer_type = MYSQL_TYPE_STRING;
179

    
180
      unsigned long bufLen = value.length() + 1;
181
      *len = value.length();
182
      data = (char *)malloc(bufLen);
183
      memcpy(data, value.c_str(), value.length());
184
      freeColumn(column);
185
      in_pars_[column].buffer = data;
186
      in_pars_[column].buffer_length = bufLen;
187
      in_pars_[column].length = len;
188
      in_pars_[column].is_null = 0;
189
    }
190

    
191
    virtual void bind(int column, short value)
192
    {
193
      if (in_pars_ == 0)
194
        throw MySQLException(std::string("Try to bind too much?"));
195

    
196
      DEBUG(std::cerr << this << " bind " << column << " "
197
            << value << std::endl);
198
      short * data = (short *)malloc(sizeof(short));
199
      *data = value;
200
      freeColumn(column);
201
      in_pars_[column].buffer_type = MYSQL_TYPE_SHORT;
202
      in_pars_[column].buffer = data;
203
      in_pars_[column].length = 0;
204
      in_pars_[column].is_null = 0;
205

    
206
    }
207

    
208
    virtual void bind(int column, int value)
209
    {
210
      if (in_pars_ == 0)
211
        throw MySQLException(std::string("Try to bind too much?"));
212

    
213
      DEBUG(std::cerr << this << " bind " << column << " "
214
            << value << std::endl);
215
      int * data = (int *)malloc(sizeof(int));
216
      *data = value;
217
      freeColumn(column);
218
      in_pars_[column].buffer_type = MYSQL_TYPE_LONG;
219
      in_pars_[column].buffer = data;
220
      in_pars_[column].length = 0;
221
      in_pars_[column].is_null = 0;
222
    }
223

    
224
    virtual void bind(int column, long long value)
225
    {
226
      if (in_pars_ == 0)
227
        throw MySQLException(std::string("Try to bind too much?"));
228

    
229
      DEBUG(std::cerr << this << " bind " << column << " "
230
            << value << std::endl);
231
      long long * data = (long long *)malloc(sizeof(long long));
232
      *data = value;
233
      freeColumn(column);
234
      in_pars_[column].buffer_type = MYSQL_TYPE_LONGLONG;
235
      in_pars_[column].buffer = data;
236
      in_pars_[column].length = 0;
237
      in_pars_[column].is_null = 0;
238
    }
239

    
240
    virtual void bind(int column, float value)
241
    {
242
      DEBUG(std::cerr << this << " bind " << column << " "
243
            << value << std::endl);
244
      float * data = (float *) malloc(sizeof(float));
245
      *data = value;
246
      freeColumn(column);
247
      in_pars_[column].buffer_type = MYSQL_TYPE_FLOAT;
248
      in_pars_[column].buffer = data;
249
      in_pars_[column].length = 0;
250
      in_pars_[column].is_null = 0;
251
   }
252

    
253
    virtual void bind(int column, double value)
254
    {
255
      if (in_pars_ == 0)
256
        throw MySQLException(std::string("Try to bind too much?"));
257

    
258
      DEBUG(std::cerr << this << " bind " << column << " "
259
            << value << std::endl);
260
      double * data = (double *)malloc(sizeof(double));
261
      *data = value;
262
      freeColumn(column);
263
      in_pars_[column].buffer_type = MYSQL_TYPE_DOUBLE;
264
      in_pars_[column].buffer = data;
265
      in_pars_[column].length = 0;
266
      in_pars_[column].is_null = 0;
267
    }
268

    
269
    virtual void bind(int column, const boost::posix_time::ptime& value,
270
                      SqlDateTimeType type)
271
    {
272
      if (in_pars_ == 0)
273
        throw MySQLException(std::string("Try to bind too much?"));
274

    
275
      DEBUG(std::cerr << this << " bind " << column << " "
276
            << boost::posix_time::to_simple_string(value) << std::endl);
277

    
278
      MYSQL_TIME*  ts = (MYSQL_TIME*)malloc(sizeof(struct st_mysql_time));
279

    
280
      boost::posix_time::ptime::time_duration_type  tim = value.time_of_day();
281
      boost::posix_time::ptime::date_type dd = value.date();
282
      ts->year = dd.year();
283
      ts->month = dd.month();
284
      ts->day = dd.day();
285
      ts->neg = 0;
286

    
287
      if (type == SqlDate){
288
        in_pars_[column].buffer_type = MYSQL_TYPE_DATE;
289
           ts->hour = 0;
290
           ts->minute = 0;
291
           ts->second = 0;
292
           ts->second_part = 0;
293

    
294
      }
295
      else {
296
        in_pars_[column].buffer_type = MYSQL_TYPE_DATETIME;
297
        ts->hour = tim.hours();
298
        ts->minute = tim.minutes();
299
        ts->second = tim.seconds();
300
        if (conn_.getFractionalSecondsPart() > 0)
301
          ts->second_part = (unsigned long)tim.fractional_seconds();
302
        else
303
          ts->second_part = 0;
304
      }
305
      freeColumn(column);
306
      in_pars_[column].buffer = ts;
307
      in_pars_[column].length = 0;
308
      in_pars_[column].is_null = 0;
309

    
310
     }
311

    
312
    virtual void bind(int column, const boost::posix_time::time_duration& value)
313
    {
314
      if (in_pars_ == 0)
315
        throw MySQLException(std::string("Try to bind too much?"));
316

    
317
      DEBUG(std::cerr << this << " bind " << column << " "
318
            << boost::posix_time::to_simple_string(value) << std::endl);
319

    
320
      MYSQL_TIME* ts  = (MYSQL_TIME *)malloc(sizeof(struct st_mysql_time));
321

    
322
      //IMPL note that there is not really a "duration" type in mysql...
323
      //mapping to a datetime
324
      in_pars_[column].buffer_type = MYSQL_TYPE_TIME;//MYSQL_TYPE_DATETIME;
325

    
326
      ts->year = 0;
327
      ts->month = 0;
328
      ts->day = 0;
329
      ts->neg = 0;
330
      ts->hour = value.hours();
331
      ts->minute = value.minutes();
332
      ts->second = value.seconds();
333
      if(conn_.getFractionalSecondsPart() > 0)
334
        ts->second_part = (unsigned long)value.fractional_seconds();
335
      else
336
        ts->second_part = 0;
337
      freeColumn(column);
338
      in_pars_[column].buffer = ts;
339
      in_pars_[column].length = 0;
340
      in_pars_[column].is_null = 0;
341
    }
342

    
343
    virtual void bind(int column, const std::vector<unsigned char>& value)
344
    {
345
      if (in_pars_ == 0)
346
        throw MySQLException(std::string("Try to bind too much?"));
347

    
348
      DEBUG(std::cerr << this << " bind " << column << " (blob, size=" <<
349
            value.size() << ")" << std::endl);
350

    
351
      unsigned long * len = (unsigned long *)malloc(sizeof(unsigned long));
352

    
353
      char * data;
354
      in_pars_[column].buffer_type = MYSQL_TYPE_BLOB;
355

    
356
      *len = value.size();
357
      data = (char *)malloc(*len);
358
      if (value.size() > 0) // must not dereference begin() for empty vectors
359
	memcpy(data, &(*value.begin()), *len);
360

    
361
      freeColumn(column);
362
      in_pars_[column].buffer = data;
363
      in_pars_[column].buffer_length = *len;
364
      in_pars_[column].length = len;
365
      in_pars_[column].is_null = 0;
366

    
367
      // FIXME if first null was bound, check here and invalidate the prepared
368
      // statement if necessary because the type changes
369
    }
370

    
371
    virtual void bindNull(int column)
372
    {
373
      if (in_pars_ == 0)
374
        throw MySQLException(std::string("Try to bind too much?"));
375

    
376
      DEBUG(std::cerr << this << " bind " << column << " null" << std::endl);
377

    
378
      freeColumn(column);
379
      in_pars_[column].buffer_type = MYSQL_TYPE_NULL;
380
      in_pars_[column].is_null = const_cast<my_bool*>(&mysqltrue_);
381
      unsigned long * len = (unsigned long *)malloc(sizeof(unsigned long));
382
      in_pars_[column].buffer = 0;
383
      in_pars_[column].buffer_length = 0;
384
      in_pars_[column].length = len;
385
    }
386

    
387
    virtual void execute()
388
    {
389
      if (conn_.showQueries())
390
        std::cerr << sql_ << std::endl;
391

    
392

    
393
      if(mysql_stmt_bind_param(stmt_, &in_pars_[0]) == 0){
394
        if (callWithReconnect( &mysql_stmt_execute, stmt_, &conn_, &MySQL::reconnect)  == 0 ) {
395
          if(mysql_stmt_field_count(stmt_) == 0) { // assume not select
396
            affectedRows_ = mysql_stmt_affected_rows(stmt_);
397
           state_ = NoFirstRow;
398
            if (affectedRows_ == 1 ) {
399
              lastId_ = mysql_stmt_insert_id(stmt_);
400
            }
401
          }
402
          else {
403
            row_ = 0;
404

    
405
            if(result_){
406
              mysql_free_result(result_);
407
            }
408

    
409
            result_ = mysql_stmt_result_metadata(stmt_);
410
            mysql_stmt_store_result(stmt_); //possibly not efficient,
411
            //but suffer from "commands out of sync" errors with the usage
412
            //patterns that Wt::Dbo uses if not called.
413
            if( result_ ) {
414
              if(mysql_num_fields(result_) > 0){
415
                state_ = NextRow;
416
              }
417
              else {
418
                state_ = NoFirstRow; // not sure how/if this can happen
419
              }
420
            }
421
            else {
422
              throw MySQLException(std::string("error getting result metadata ")
423
                                   + mysql_stmt_error(stmt_));
424
            }
425
          }
426
        }
427
        else {
428
          throw MySQLException(
429
                std::string("error executing prepared statement ")+
430
                mysql_stmt_error(stmt_));
431
        }
432
      }
433
      else {
434
        throw MySQLException(std::string("error binding parameters")+
435
                             mysql_stmt_error(stmt_));
436
      }
437
    }
438

    
439
    virtual long long insertedId()
440
    {
441
      return lastId_;
442
    }
443

    
444
    virtual int affectedRowCount()
445
    {
446
      return (int)affectedRows_;
447
    }
448

    
449
    virtual bool nextRow()
450
    {
451
      int status = 0;
452
      switch (state_) {
453
        case NoFirstRow:
454
          state_ = Done;
455
          return false;
456
        case NextRow:
457
          //bind the output..
458
          bind_output();
459
          if ((status = mysql_stmt_fetch(stmt_)) == 0 ||
460
	      status == MYSQL_DATA_TRUNCATED) {
461
	    if (status == MYSQL_DATA_TRUNCATED)
462
	      has_truncation_ = true;
463
	    else
464
	      has_truncation_ = false;
465
            row_++;
466
            return true;
467
          } else {
468
            if(status == MYSQL_NO_DATA ) {
469
              lastOutCount_ = mysql_num_fields(result_);
470
              mysql_free_result(result_);
471
              mysql_stmt_free_result(stmt_);
472
              result_ = 0;
473
              state_ = Done;
474
              return false;
475
            } else {
476
              throw MySQLException(std::string("MySQL: row fetch failure: ") +
477
                                   mysql_stmt_error(stmt_));
478
            }
479
          }
480
        break;
481
      case Done:
482
        throw MySQLException("MySQL: nextRow(): statement already "
483
                             "finished");
484
      }
485

    
486
      return false;
487
    }
488

    
489
    virtual bool getResult(int column, std::string *value, int size)
490
    {
491
      if (*(out_pars_[column].is_null) == 1)
492
        return false;
493

    
494
      if(*(out_pars_[column].length) > 0){
495
        char * str;
496
	if (*(out_pars_[column].length) + 1 > out_pars_[column].buffer_length) {
497
	  free(out_pars_[column].buffer);
498
	  out_pars_[column].buffer = malloc(*(out_pars_[column].length)+1);
499
	  out_pars_[column].buffer_length = *(out_pars_[column].length)+1;
500
	}
501
        mysql_stmt_fetch_column(stmt_,  &out_pars_[column], column, 0);
502

    
503
        if (has_truncation_ && *out_pars_[column].error)
504
	  throw MySQLException("MySQL: getResult(): truncated result for column "
505
	    + boost::lexical_cast<int>(column));
506

    
507

    
508
	str = static_cast<char*>( out_pars_[column].buffer);
509
        *value = std::string(str, *out_pars_[column].length);
510

    
511
        DEBUG(std::cerr << this
512
              << " result string " << column << " " << *value << std::endl);
513

    
514
        return true;
515
      }
516
      else
517
        return false;
518
    }
519

    
520
    virtual bool getResult(int column, short *value)
521
    {
522
      if (has_truncation_ && *out_pars_[column].error)
523
	throw MySQLException("MySQL: getResult(): truncated result for column "
524
	  + boost::lexical_cast<int>(column));
525

    
526
      if (*(out_pars_[column].is_null) == 1)
527
         return false;
528

    
529
      *value = *static_cast<short*>(out_pars_[column].buffer);
530

    
531
      return true;
532
    }
533

    
534
    virtual bool getResult(int column, int *value)
535
    {
536

    
537
      if (*(out_pars_[column].is_null) == 1)
538
        return false;
539
      switch (out_pars_[column].buffer_type ){
540
      case MYSQL_TYPE_TINY:
541
        if (has_truncation_ && *out_pars_[column].error)
542
	  throw MySQLException("MySQL: getResult(): truncated result for column "
543
	    + boost::lexical_cast<int>(column));
544
        *value = (int)*static_cast<bool*>(out_pars_[column].buffer);
545
        break;
546
      case MYSQL_TYPE_LONG:
547
        if (has_truncation_ && *out_pars_[column].error)
548
	  throw MySQLException("MySQL: getResult(): truncated result for column "
549
	    + boost::lexical_cast<int>(column));
550
        *value = *static_cast<int*>(out_pars_[column].buffer);
551
        break;
552

    
553
      case MYSQL_TYPE_LONGLONG:
554
        if (has_truncation_ && *out_pars_[column].error)
555
	  throw MySQLException("MySQL: getResult(): truncated result for column "
556
	    + boost::lexical_cast<int>(column));
557
        *value = (int)*static_cast<long long*>(out_pars_[column].buffer);
558
        break;
559

    
560
      case MYSQL_TYPE_NEWDECIMAL:
561
	{
562
	  std::string strValue;
563
	  if (!getResult(column, &strValue, 0))
564
	    return false;
565

    
566
	  try{
567
	    *value = boost::lexical_cast<int>(strValue);
568
	  } catch( boost::bad_lexical_cast const& ) {
569
	    try{
570
	      *value = (int)boost::lexical_cast<double>(strValue);
571
	    } catch( boost::bad_lexical_cast const& ) {
572
	      std::cout << "Error: MYSQL_TYPE_NEWDECIMAL " << strValue
573
		<< "could not be casted to int" << std::endl;
574
	      return false;
575
	    }
576
	  }
577
	}
578
        break;
579
      default:
580
	return false;
581
      }
582

    
583
      DEBUG(std::cerr << this
584
            << " result  int " << column << " " << *value << std::endl);
585

    
586
      return true;
587
    }
588

    
589
    virtual bool getResult(int column, long long *value)
590
    {
591
      if (has_truncation_ && *out_pars_[column].error)
592
	throw MySQLException("MySQL: getResult(): truncated result for column "
593
	  + boost::lexical_cast<int>(column));
594

    
595
      if (*(out_pars_[column].is_null) == 1)
596
        return false;
597
      switch (out_pars_[column].buffer_type ){
598
        case MYSQL_TYPE_LONG:
599

    
600
          *value = *static_cast<int*>(out_pars_[column].buffer);
601
          break;
602

    
603
        case MYSQL_TYPE_LONGLONG:
604

    
605
          *value = *static_cast<long long*>(out_pars_[column].buffer);
606
          break;
607

    
608
        default:
609

    
610
	  throw MySQLException("MySQL: getResult(long long): unknown type: "
611
			       + boost::lexical_cast<int>
612
			       (out_pars_[column].buffer_type ));
613
	  break;
614
      }
615

    
616
      DEBUG(std::cerr << this
617
            << " result long long " << column << " " << *value << std::endl);
618

    
619
      return true;
620
    }
621

    
622
    virtual bool getResult(int column, float *value)
623
    {
624
      if (has_truncation_ && *out_pars_[column].error)
625
	throw MySQLException("MySQL: getResult(): truncated result for column "
626
	  + boost::lexical_cast<int>(column));
627

    
628
      if (*(out_pars_[column].is_null) == 1)
629
         return false;
630

    
631
       *value = *static_cast<float*>(out_pars_[column].buffer);
632

    
633
      DEBUG(std::cerr << this
634
            << " result float " << column << " " << *value << std::endl);
635

    
636
      return true;
637
    }
638

    
639
    virtual bool getResult(int column, double *value)
640
    {
641

    
642
      if (*(out_pars_[column].is_null) == 1)
643
         return false;
644
      switch (out_pars_[column].buffer_type ){
645
      case MYSQL_TYPE_DOUBLE:
646
        if (has_truncation_ && *out_pars_[column].error)
647
	  throw MySQLException("MySQL: getResult(): truncated result for column "
648
	    + boost::lexical_cast<int>(column));
649
        *value = *static_cast<double*>(out_pars_[column].buffer);
650
        break;
651
      case MYSQL_TYPE_FLOAT:
652
        if (has_truncation_ && *out_pars_[column].error)
653
	  throw MySQLException("MySQL: getResult(): truncated result for column "
654
	    + boost::lexical_cast<int>(column));
655
        *value = *static_cast<float*>(out_pars_[column].buffer);
656
        break;
657
      case MYSQL_TYPE_NEWDECIMAL:
658
	{
659
	  std::string strValue;
660
	  if (!getResult(column, &strValue, 0))
661
	    return false;
662

    
663
	  try {
664
	    *value = boost::lexical_cast<double>(strValue);
665
	  } catch( boost::bad_lexical_cast const& ) {
666
	    std::cout << "Error: MYSQL_TYPE_NEWDECIMAL " << strValue
667
	      << "could not be casted to double" << std::endl;
668
	    return false;
669
	  }
670
	}
671
        break;
672
      default:
673
	return false;
674
      }
675

    
676
      DEBUG(std::cerr << this
677
            << " result double " << column << " " << *value << std::endl);
678

    
679
      return true;
680
    }
681

    
682
    virtual bool getResult(int column, boost::posix_time::ptime *value,
683
                           SqlDateTimeType type)
684
    {
685
      if (has_truncation_ && *out_pars_[column].error)
686
	throw MySQLException("MySQL: getResult(): truncated result for column "
687
	  + boost::lexical_cast<int>(column));
688

    
689
      if (*(out_pars_[column].is_null) == 1)
690
         return false;
691

    
692
      MYSQL_TIME* ts = static_cast<MYSQL_TIME*>(out_pars_[column].buffer);
693

    
694
      if (type == SqlDate){
695
        *value = boost::posix_time::ptime(
696
              boost::gregorian::date(ts->year, ts->month, ts->day),
697
                                          boost::posix_time::hours(0));
698
      }
699
      else
700
        *value = boost::posix_time::ptime(
701
            boost::gregorian::date(ts->year, ts->month, ts->day),
702
            boost::posix_time::time_duration(ts->hour, ts->minute, ts->second)
703
            + boost::posix_time::microseconds(ts->second_part));
704

    
705
      DEBUG(std::cerr << this
706
            << " result time " << column << " " << *value << std::endl);
707

    
708
      return true;
709
    }
710

    
711
    virtual bool getResult(int column, boost::posix_time::time_duration* value)
712
    {
713
      if (has_truncation_ && *out_pars_[column].error)
714
	throw MySQLException("MySQL: getResult(): truncated result for column "
715
	  + boost::lexical_cast<int>(column));
716

    
717
      if (*(out_pars_[column].is_null) == 1)
718
         return false;
719

    
720
       MYSQL_TIME* ts = static_cast<MYSQL_TIME*>(out_pars_[column].buffer);
721
       *value = boost::posix_time::time_duration(
722
             ts->hour, ts->minute, ts->second, ts->second_part);
723

    
724
       DEBUG(std::cerr << this
725
             << " result time " << column << " " << *value << std::endl);
726

    
727
       return true;
728
    }
729

    
730
    virtual bool getResult(int column, std::vector<unsigned char> *value,
731
                           int size)
732
    {
733
      if (*(out_pars_[column].is_null) == 1)
734
        return false;
735

    
736
      if(*(out_pars_[column].length) > 0){
737
	if (*(out_pars_[column].length) > out_pars_[column].buffer_length) {
738
	  free(out_pars_[column].buffer);
739
	  out_pars_[column].buffer = malloc(*(out_pars_[column].length));
740
	  out_pars_[column].buffer_length = *(out_pars_[column].length);
741
	}
742
        mysql_stmt_fetch_column(stmt_,  &out_pars_[column], column, 0);
743

    
744
      if (*out_pars_[column].error)
745
	throw MySQLException("MySQL: getResult(): truncated result for column "
746
	  + boost::lexical_cast<int>(column));
747

    
748

    
749
	std::size_t vlength = *(out_pars_[column].length);
750
        unsigned char *v =
751
            static_cast<unsigned char*>(out_pars_[column].buffer);
752

    
753
        value->resize(vlength);
754
        std::copy(v, v + vlength, value->begin());
755

    
756
        DEBUG(std::cerr << this
757
              << " result blob " << column << " (blob, size = "
758
              << vlength << ")"<< std::endl);
759

    
760
        return true;
761
      }
762
      else
763
        return false;
764
    }
765

    
766
    virtual std::string sql() const {
767
      return sql_;
768
    }
769

    
770
  private:
771

    
772
    MySQL& conn_;
773
    std::string sql_;
774
    char name_[64];
775
    bool has_truncation_;
776
    MYSQL_RES *result_;
777
    MYSQL_STMT* stmt_;
778
    MYSQL_BIND* in_pars_;
779
    MYSQL_BIND* out_pars_;
780
    my_bool* errors_;
781
    unsigned int lastOutCount_;
782
    // true value to use because mysql specifies that pointer to the boolean
783
    // is passed in many cases....
784
    static const my_bool mysqltrue_;
785
    enum { NoFirstRow, NextRow, Done } state_;
786
    long long lastId_, row_, affectedRows_;
787

    
788
    void bind_output() {
789
      if (!out_pars_) {
790
	out_pars_ =(MYSQL_BIND *)malloc(
791
	      mysql_num_fields(result_) * sizeof(struct st_mysql_bind));
792
	memset(out_pars_, 0,
793
		mysql_num_fields(result_) * sizeof(struct st_mysql_bind));
794
	errors_ = new my_bool[mysql_num_fields(result_)];
795
	for(unsigned int i = 0; i < mysql_num_fields(result_); ++i){
796
	  MYSQL_FIELD* field = mysql_fetch_field_direct(result_, i);
797
	  out_pars_[i].buffer_type = field->type;
798
	  out_pars_[i].error = &errors_[i];
799
	  switch(field->type){
800
	  case MYSQL_TYPE_TINY:
801
	    out_pars_[i].buffer = malloc(1);
802
	    out_pars_[i].buffer_length = 1;
803
	    break;
804

    
805
	  case MYSQL_TYPE_SHORT:
806
	    out_pars_[i].buffer = malloc(sizeof(short));
807
	    out_pars_[i].buffer_length = sizeof(short);
808
	    break;
809

    
810
	  case MYSQL_TYPE_LONG:
811
	    out_pars_[i].buffer = malloc(sizeof(int));
812
	    out_pars_[i].buffer_length = sizeof(int);
813
	    break;
814
	  case MYSQL_TYPE_FLOAT:
815
	    out_pars_[i].buffer = malloc(sizeof(float));
816
	    out_pars_[i].buffer_length = sizeof(float);
817
	    break;
818

    
819
	  case MYSQL_TYPE_LONGLONG:
820
	    out_pars_[i].buffer = malloc(sizeof(long long));
821
	    out_pars_[i].buffer_length = sizeof(long long);
822
	    break;
823
	  case MYSQL_TYPE_DOUBLE:
824
	    out_pars_[i].buffer = malloc(sizeof(double));
825
	    out_pars_[i].buffer_length = sizeof(double);
826
	    break;
827

    
828
	  case MYSQL_TYPE_TIME:
829
	  case MYSQL_TYPE_DATE:
830
	  case MYSQL_TYPE_DATETIME:
831
	  case MYSQL_TYPE_TIMESTAMP:
832
	    out_pars_[i].buffer = malloc(sizeof(struct st_mysql_time));
833
	    out_pars_[i].buffer_length = sizeof(struct st_mysql_time);
834
	    break;
835

    
836
	  case MYSQL_TYPE_NEWDECIMAL: // newdecimal is stored as string.
837
	  case MYSQL_TYPE_STRING:
838
	  case MYSQL_TYPE_VAR_STRING:
839
	  case MYSQL_TYPE_BLOB:
840
	    out_pars_[i].buffer = malloc(256);
841
	    out_pars_[i].buffer_length = 256; // Reserve 256 bytes, if the content is longer, it will be reallocated later
842
	    //http://dev.mysql.com/doc/refman/5.0/en/mysql-stmt-fetch.html
843
	    break;
844
	  default:
845
	    std::cerr << "MySQL Backend Programming Error: unknown type "
846
		      << field->type << std::endl;
847
	  }
848
	  out_pars_[i].buffer_type = field->type;
849
	  out_pars_[i].is_null = (my_bool *)malloc(sizeof(char));
850
	  out_pars_[i].length =
851
	      (unsigned long *) malloc(sizeof(unsigned long));
852
	  out_pars_[i].error = (my_bool *)malloc(sizeof(char));
853
	}
854
      }
855
      mysql_stmt_bind_result(stmt_, out_pars_);
856
    }
857

    
858
    void freeColumn(int column)
859
    {
860
      if(in_pars_[column].length != 0 ) {
861
        free(in_pars_[column].length);
862
        in_pars_[column].length = 0;
863
      }
864

    
865
      if(in_pars_[column].buffer != 0 ) {
866
        free(in_pars_[column].buffer);
867
        in_pars_[column].buffer = 0;
868
      }
869
    }
870

    
871
    void free_outpars(){
872

    
873
      unsigned int count;
874
      if(!result_){
875
          count = lastOutCount_;
876
      }else
877
        count = mysql_num_fields(result_);
878

    
879
      for (unsigned int i = 0; i < count; ++i){
880
       if(out_pars_[i].buffer != 0)free(out_pars_[i].buffer);
881
       if(out_pars_[i].is_null != 0)free(out_pars_[i].is_null) ;
882
       if(out_pars_[i].length != 0)free(out_pars_[i].length);
883
       if(out_pars_[i].error != 0)free(out_pars_[i].error);
884

    
885
      }
886
      free(out_pars_);
887
      out_pars_ = 0;
888
    }
889

    
890
};
891

    
892
const my_bool MySQLStatement::mysqltrue_ = 1;
893

    
894
MySQL::MySQL(const std::string &db,  const std::string &dbuser,
895
             const std::string &dbpasswd, const std::string dbhost,
896
             unsigned int dbport, const std::string &dbsocket,
897
             int fractionalSecondsPart)
898
: impl_(new MySQL_impl())
899
{
900
  setFractionalSecondsPart(fractionalSecondsPart);
901

    
902
  try {
903
    connect(db, dbuser, dbpasswd, dbhost, dbport, dbsocket);
904
  } catch (...) {
905
    delete impl_;
906
    throw;
907
  }
908
}
909

    
910
MySQL::MySQL(const MySQL& other)
911
  : SqlConnection(other),
912
    impl_(new MySQL_impl())
913
{
914
  setFractionalSecondsPart(other.fractionalSecondsPart_);
915

    
916
  try {
917
    if (!other.dbname_.empty())
918
      connect(other.dbname_, other.dbuser_, other.dbpasswd_, other.dbhost_, other.dbport_, other.dbsocket_);
919
  } catch (...) {
920
    delete impl_;
921
    throw;
922
  }
923
}
924

    
925
MySQL::~MySQL()
926
{
927
  clearStatementCache();
928
  if (impl_ && impl_->mysql)
929
    mysql_close(impl_->mysql);
930

    
931
  if (impl_){
932
    delete impl_;
933
    impl_ = 0;
934
  }
935
}
936

    
937
MySQL *MySQL::clone() const
938
{
939
  return new MySQL(*this);
940
}
941

    
942
bool MySQL::connect(const std::string &db,  const std::string &dbuser,
943
             const std::string &dbpasswd, const std::string &dbhost,
944
             unsigned int dbport, const std::string &dbsocket)
945
{
946
  if (impl_->mysql)
947
    throw MySQLException("MySQL : Already connected, disconnect first");
948

    
949
  if((impl_->mysql = mysql_init(NULL))){
950
    my_bool reconnect = true;
951
    if(!mysql_options(impl_->mysql, MYSQL_OPT_RECONNECT, &reconnect)) {
952
      if(mysql_real_connect(impl_->mysql, dbhost.c_str(), dbuser.c_str(),
953
	dbpasswd.empty() ? 0 : dbpasswd.c_str(),
954
	db.c_str(), dbport,
955
	dbsocket.c_str(),
956
	CLIENT_FOUND_ROWS) != impl_->mysql) {
957
	  std::string errtext = mysql_error(impl_->mysql);
958
	  mysql_close(impl_->mysql);
959
	  impl_->mysql = 0;
960
	  throw MySQLException(
961
	    std::string("MySQL : Failed to connect to database server: ")
962
	    + errtext);
963
      } else {
964
	// success!
965
	dbname_ = db;
966
	dbuser_ = dbuser;
967
	dbpasswd_ = dbpasswd;
968
	dbhost_ = dbhost;
969
	dbsocket_ = dbsocket;
970
	dbport_ = dbport;
971
      }
972
    } else {
973
      mysql_close(impl_->mysql);
974
      impl_->mysql = 0;
975
      throw MySQLException("MySQL : Failed to set the reconnect option");
976
    }
977
  } else {
978
    throw MySQLException(
979
      std::string("MySQL : Failed to initialize database: ") + dbname_);
980
  }
981

    
982
  init();
983

    
984
  return true;
985
}
986

    
987
void MySQL::init()
988
{
989
  executeSql("SET sql_mode='ANSI_QUOTES,REAL_AS_FLOAT'");
990
  executeSql("SET storage_engine=INNODB;");
991
  executeSql("SET NAMES 'utf8';");
992
}
993

    
994
SqlStatement *MySQL::prepareStatement(const std::string& sql)
995
{
996
  return new MySQLStatement(*this, sql);
997
}
998

    
999
void MySQL::executeSql(const std::string &sql)
1000
{
1001
  if (showQueries())
1002
    std::cerr << sql << std::endl;
1003

    
1004
  if( callWithReconnect(&mysql_query, impl_->mysql, sql.c_str(), this, &MySQL::reconnect ) != 0 ){
1005
    throw MySQLException("MySQL error performing query: '" +
1006
			 sql + "': " + mysql_error(impl_->mysql));
1007
  }
1008
  //use any results up
1009
  MYSQL_RES* res = mysql_store_result(impl_->mysql);
1010
  if(res) mysql_free_result(res);
1011

    
1012
}
1013

    
1014
std::string MySQL::autoincrementType() const
1015
{
1016
  return "BIGINT";
1017
}
1018

    
1019
std::string MySQL::autoincrementSql() const
1020
{
1021
  return "AUTO_INCREMENT";
1022
}
1023

    
1024
std::string MySQL::autoincrementInsertSuffix(const std::string &id) const
1025
{
1026
  return std::string();
1027
}
1028

    
1029
std::vector<std::string>
1030
MySQL::autoincrementCreateSequenceSql(const std::string &table,
1031
                                      const std::string &id) const{
1032
  return std::vector<std::string>();
1033
}
1034

    
1035
std::vector<std::string>
1036
MySQL::autoincrementDropSequenceSql(const std::string &table,
1037
                             const std::string &id) const{
1038

    
1039
  return std::vector<std::string>();
1040
}
1041

    
1042
const char *MySQL::dateTimeType(SqlDateTimeType type) const
1043
{
1044
  switch (type) {
1045
  case SqlDate:
1046
    return "date";
1047
  case SqlDateTime:
1048
    return dateType_.c_str();
1049
  case SqlTime:
1050
    return timeType_.c_str();
1051
  }
1052
  std::stringstream ss;
1053
  ss << __FILE__ << ":" << __LINE__ << ": implementation error";
1054
  throw MySQLException(ss.str());
1055
}
1056

    
1057
const char *MySQL::blobType() const
1058
{
1059
  return "blob";
1060
}
1061

    
1062
bool MySQL::supportAlterTable() const
1063
{
1064
  return true;
1065
}
1066

    
1067
const char *MySQL::alterTableConstraintString() const
1068
{
1069
  return "foreign key";
1070
}
1071

    
1072
int MySQL::getFractionalSecondsPart() const
1073
{
1074
  return fractionalSecondsPart_;
1075
}
1076

    
1077
void MySQL::setFractionalSecondsPart(int fractionalSecondsPart)
1078
{
1079
  fractionalSecondsPart_ = fractionalSecondsPart;
1080

    
1081
  if (fractionalSecondsPart_ != -1) {
1082
    dateType_ = "datetime(";
1083
    dateType_ += boost::lexical_cast<std::string>(fractionalSecondsPart_);
1084
    dateType_ += ")";
1085
  } else
1086
    dateType_ = "datetime";
1087

    
1088

    
1089
  //IMPL note that there is not really a "duration" type in mysql...
1090
  if (fractionalSecondsPart_ != -1) {
1091
    timeType_ = "time(";
1092
    timeType_ += boost::lexical_cast<std::string>(fractionalSecondsPart_);
1093
    timeType_ += ")";
1094
  } else
1095
    timeType_ = "time";
1096
}
1097

    
1098

    
1099
void MySQL::reconnect()
1100
{
1101
  std::cerr << "reconnecting ... " << std::endl;
1102
  this->clearStatementCache();
1103
  this->init();
1104
}
1105

    
1106
void MySQL::startTransaction()
1107
{
1108
  if (showQueries())
1109
     std::cerr << "start transaction" << std::endl;
1110
  if( mysql_query(impl_->mysql, "start transaction") != 0 ){
1111
    throw MySQLException(std::string("MySQL error starting transaction: ") +
1112
                         mysql_error(impl_->mysql));
1113
  }
1114
  //use any results up
1115
  MYSQL_RES* res = mysql_store_result(impl_->mysql);
1116
  if(res) mysql_free_result(res);
1117
}
1118

    
1119
void MySQL::commitTransaction()
1120
{
1121
  my_bool status;
1122
  if (showQueries())
1123
     std::cerr << "commit transaction" << std::endl;
1124
  if( (status = mysql_commit(impl_->mysql)) != 0 ){
1125
    std::cerr << "error committing transaction: "
1126
              << mysql_error(impl_->mysql) << std::endl;
1127
    throw MySQLException(std::string("MySQL error committing transaction: ") +
1128
                         mysql_error(impl_->mysql));
1129
  }
1130
  //use any results up
1131
  MYSQL_RES* res = mysql_store_result(impl_->mysql);
1132
  if(res) mysql_free_result(res);
1133
}
1134

    
1135
void MySQL::rollbackTransaction()
1136
{
1137
  my_bool status;
1138
  if (showQueries())
1139
     std::cerr << "rollback" << std::endl;
1140
  if((status =  mysql_rollback(impl_->mysql)) != 0 ){
1141
    throw MySQLException(std::string("MySQL error rolling back transaction: ") +
1142
                         mysql_error(impl_->mysql));
1143
  }
1144
  //use any results up
1145
  MYSQL_RES* res = mysql_store_result(impl_->mysql);
1146
  if(res) mysql_free_result(res);
1147
}
1148

    
1149
}
1150
}
1151
}
(1-1/2)