fastcgi++
mysql.cpp
Go to the documentation of this file.
1 
2 /***************************************************************************
3 * Copyright (C) 2007 Eddie Carle [eddie@erctech.org] *
4 * *
5 * This file is part of fastcgi++. *
6 * *
7 * fastcgi++ is free software: you can redistribute it and/or modify it *
8 * under the terms of the GNU Lesser General Public License as published *
9 * by the Free Software Foundation, either version 3 of the License, or (at *
10 * your option) any later version. *
11 * *
12 * fastcgi++ is distributed in the hope that it will be useful, but WITHOUT *
13 * ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or *
14 * FITNESS FOR A PARTICULAR PURPOSE. See the GNU Lesser General Public *
15 * License for more details. *
16 * *
17 * You should have received a copy of the GNU Lesser General Public License *
18 * along with fastcgi++. If not, see <http://www.gnu.org/licenses/>. *
19 ****************************************************************************/
20 
21 
22 #include <asql/mysql.hpp>
23 #include <utf8_codecvt.hpp>
24 #include <cstdlib>
25 
26 void ASql::MySQL::Connection::connect(const char* host, const char* user, const char* passwd, const char* db, unsigned int port, const char* unix_socket, unsigned long client_flag, const char* const charset)
27 {
28  if(m_initialized)
29  {
30  for(unsigned int i=0; i<threads(); ++i)
31  {
32  mysql_stmt_close(foundRowsStatement[i]);
33  mysql_close(&m_connection[i]);
34  }
35  m_initialized = false;
36  }
37 
38  for(unsigned int i=0; i<threads(); ++i)
39  {
40  if(!mysql_init(&m_connection[i]))
41  throw Error(&m_connection[i]);
42 
43  if(!mysql_real_connect(&m_connection[i], host, user, passwd, db, port, unix_socket, client_flag))
44  throw Error(&m_connection[i]);
45 
46  if(mysql_set_character_set(&m_connection[i], charset))
47  throw Error(&m_connection[i]);
48 
49  if(mysql_autocommit(&m_connection[i], 0))
50  throw Error(&m_connection[i]);
51 
52  if(!(foundRowsStatement[i] = mysql_stmt_init(&m_connection[i])))
53  throw Error(&m_connection[i]);
54 
55  if(mysql_stmt_prepare(foundRowsStatement[i], "SELECT FOUND_ROWS()", 19))
56  throw Error(foundRowsStatement[i]);
57 
58  std::memset(&foundRowsBinding[i], 0, sizeof(MYSQL_BIND));
59  foundRowsBinding[i].buffer_type = MYSQL_TYPE_LONGLONG;
60  foundRowsBinding[i].is_unsigned = 1;
61  }
62 
63  m_initialized = true;
64 }
65 
66 ASql::MySQL::Connection::~Connection()
67 {
68  if(m_initialized)
69  {
70  for(unsigned int i=0; i<threads(); ++i)
71  {
72  mysql_stmt_close(foundRowsStatement[i]);
73  mysql_close(&m_connection[i]);
74  }
75  }
76 }
77 
78 void ASql::MySQL::Connection::getFoundRows(unsigned long long* const& rows, const unsigned int thread)
79 {
80  if(mysql_stmt_bind_param(foundRowsStatement[thread], 0))
81  throw Error(foundRowsStatement[thread]);
82 
83  if(mysql_stmt_execute(foundRowsStatement[thread]))
84  throw Error(foundRowsStatement[thread]);
85 
86  foundRowsBinding[thread].buffer = rows;
87  if(mysql_stmt_bind_result(foundRowsStatement[thread], &foundRowsBinding[thread]))
88  throw Error(foundRowsStatement[thread]);
89 
90  if(mysql_stmt_fetch(foundRowsStatement[thread]))
91  throw Error(foundRowsStatement[thread]);
92  mysql_stmt_free_result(foundRowsStatement[thread]);
93  mysql_stmt_reset(foundRowsStatement[thread]);
94 
95 }
96 
97 void ASql::MySQL::Statement::init(const char* const& queryString, const size_t& queryLength, const Data::Set* const parameterSet, const Data::Set* const resultSet, bool customPlaceholders)
98 {
99  if(m_initialized)
100  {
101  for(unsigned int i=0; i<connection.threads(); ++i)
102  mysql_stmt_close(stmt[i]);
103  m_initialized = false;
104  }
105 
106  const char* realQueryString = queryString;
107  size_t realQueryLength = queryLength;
108  boost::scoped_array<char> buffer;
109 
110  if(customPlaceholders)
111  {
112  buffer.reset(new char[queryLength]);
113  std::memset(buffer.get(), 0, queryLength);
114  realQueryString = buffer.get();
115  realQueryLength = 0;
116 
117  char intBuffer[4];
118  size_t intBufferSize=0;
119  std::memset(intBuffer, 0, sizeof(intBuffer));
120  bool inPlaceholder = false;
121 
122  for(const char* it=queryString; it != queryString+queryLength; ++it)
123  {
124  if(inPlaceholder && '0' <= *it && *it <= '9' && intBufferSize < sizeof(intBuffer)-1)
125  {
126  intBuffer[intBufferSize++] = *it;
127  continue;
128  }
129 
130  if(inPlaceholder)
131  {
132  paramOrder.push_back(std::atoi(intBuffer));
133 
134  intBufferSize=0;
135  std::memset(intBuffer, 0, sizeof(intBuffer));
136  inPlaceholder = false;
137  }
138 
139  if(*it == '?') inPlaceholder = true;
140 
141  buffer[realQueryLength++] = *it;
142  }
143 
144  if(inPlaceholder) paramOrder.push_back(std::atoi(intBuffer));
145  }
146 
147  for(unsigned int i=0; i<connection.threads(); ++i)
148  {
149  m_stop[i]=&ConnectionPar<Statement>::s_false;
150  stmt[i]=mysql_stmt_init(&connection.connection(i));
151  if(!stmt)
152  throw Error(&connection.connection(i));
153 
154  if(mysql_stmt_prepare(stmt[i], realQueryString, realQueryLength))
155  throw Error(stmt[i]);
156 
157  if(parameterSet) buildBindings(stmt[i], *parameterSet, paramsConversions[i], paramsBindings[i], paramOrder.size()?&paramOrder:0);
158  if(resultSet) buildBindings(stmt[i], *resultSet, resultsConversions[i], resultsBindings[i]);
159  }
160 
161  m_initialized = true;
162 }
163 
164 void ASql::MySQL::Statement::executeParameters(const Data::Set* const& parameters, const unsigned int thread)
165 {
166  if(parameters)
167  {
168  bindBindings(*const_cast<Data::Set*>(parameters), paramsConversions[thread], paramsBindings[thread], paramOrder.size()?&paramOrder:0);
169  for(Data::Conversions::iterator it=paramsConversions[thread].begin(); it!=paramsConversions[thread].end(); ++it)
170  if(!(paramsBindings[thread][it->first].is_null && *paramsBindings[thread][it->first].is_null)) it->second->convertParam();
171  if(mysql_stmt_bind_param(stmt[thread], paramsBindings[thread].get())!=0) throw Error(stmt[thread]);
172  }
173 
174  if(mysql_stmt_execute(stmt[thread])!=0) throw Error(stmt[thread]);
175 }
176 
177 bool ASql::MySQL::Statement::executeResult(Data::Set& row, const unsigned int thread)
178 {
179  bindBindings(row, resultsConversions[thread], resultsBindings[thread]);
180  if(mysql_stmt_bind_result(stmt[thread], resultsBindings[thread].get())!=0) throw Error(stmt[thread]);
181  switch (mysql_stmt_fetch(stmt[thread]))
182  {
183  case 1:
184  throw Error(stmt[thread]);
185  case MYSQL_NO_DATA:
186  return false;
187  default:
188  for(Data::Conversions::iterator it=resultsConversions[thread].begin(); it!=resultsConversions[thread].end(); ++it)
189  if(!(resultsBindings[thread][it->first].is_null && *resultsBindings[thread][it->first].is_null)) it->second->convertResult();
190  return true;
191  };
192 }
193 
194 void ASql::MySQL::Statement::execute(const Data::Set* const parameters, Data::SetContainer* const results, unsigned long long int* const insertId, unsigned long long int* const rows, bool docommit, const unsigned int thread)
195 {
196  if(*m_stop[thread]) goto end;
197  executeParameters(parameters, thread);
198 
199  if(results)
200  {
201  Data::SetContainer& res=*results;
202 
203  while(1)
204  {{
205  Data::Set& row=res.manufacture();
206  bindBindings(row, resultsConversions[thread], resultsBindings[thread]);
207  if(!executeResult(row, thread))
208  {
209  res.trim();
210  break;
211  }
212  if(*m_stop[thread])
213  {
214  res.trim();
215  goto end;
216  }
217  }}
218 
219  if(*m_stop[thread]) goto end;
220  if(rows) connection.getFoundRows(rows, thread);
221  }
222  else
223  {
224  if(*m_stop[thread]) goto end;
225  if(rows) *rows = mysql_stmt_affected_rows(stmt[thread]);
226  if(*m_stop[thread]) goto end;
227  if(insertId) *insertId = mysql_stmt_insert_id(stmt[thread]);
228  }
229 
230 end:
231  if(*m_stop[thread])
232  connection.rollback(thread);
233  else if(docommit)
234  connection.commit(thread);
235  mysql_stmt_free_result(stmt[thread]);
236  mysql_stmt_reset(stmt[thread]);
237 }
238 
239 bool ASql::MySQL::Statement::execute(const Data::Set* const parameters, Data::Set& results, bool docommit, const unsigned int thread)
240 {
241  bool retval(false);
242  if(*m_stop[thread]) goto end;
243  executeParameters(parameters, thread);
244  if(*m_stop[thread]) goto end;
245  retval=executeResult(results, thread);
246 end:
247  if(*m_stop[thread])
248  connection.rollback(thread);
249  else if(docommit)
250  connection.commit(thread);
251  mysql_stmt_free_result(stmt[thread]);
252  mysql_stmt_reset(stmt[thread]);
253  return retval;
254 }
255 
256 void ASql::MySQL::Statement::execute(const Data::SetContainer& parameters, unsigned long long int* rows, bool docommit, const unsigned int thread)
257 {
258  if(rows) *rows = 0;
259 
260  parameters.init();
261  for(const Data::Set* set=parameters.pull(); set!=0; set=parameters.pull())
262  {
263  if(*m_stop[thread]) break;
264  executeParameters(set, thread);
265  if(*m_stop[thread]) break;
266  if(rows) *rows += mysql_stmt_affected_rows(stmt[thread]);
267  }
268  if(*m_stop[thread])
269  connection.rollback(thread);
270  else if(docommit)
271  connection.commit(thread);
272  mysql_stmt_free_result(stmt[thread]);
273  mysql_stmt_reset(stmt[thread]);
274 }
275 
276 void ASql::MySQL::Statement::buildBindings(MYSQL_STMT* const& stmt, const ASql::Data::Set& set, ASql::Data::Conversions& conversions, boost::scoped_array<MYSQL_BIND>& bindings, const std::deque<unsigned char>* order)
277 {
278  using namespace Data;
279 
280  conversions.clear();
281 
282  const int bindSize=order?order->size():set.numberOfSqlElements();
283  if(!bindSize) return;
284  bindings.reset(new MYSQL_BIND[bindSize]);
285 
286  std::memset(bindings.get(), 0, sizeof(MYSQL_BIND)*bindSize);
287 
288  for(int i=0; i<bindSize; ++i)
289  {
290  const unsigned char index = order?(*order)[i]:i;
291  Data::Index element = set.getSqlIndex(index);
292 
293  // Handle NULL
294  if(element.type>=U_TINY_N)
295  element.type=Type(element.type-U_TINY_N); // Make it the same type without the nullableness
296 
297  // Handle unsigned
298  if(element.type<=U_BIGINT)
299  {
300  bindings[i].is_unsigned=1;
301  element.type=Type(element.type+TINY);
302  }
303 
304  // Start decoding values
305  switch(element.type)
306  {
307  case TINY:
308  {
309  bindings[i].buffer_type=MYSQL_TYPE_TINY;
310  break;
311  }
312 
313  case SHORT:
314  {
315  bindings[i].buffer_type=MYSQL_TYPE_SHORT;
316  break;
317  }
318 
319  case INT:
320  {
321  bindings[i].buffer_type=MYSQL_TYPE_LONG;
322  break;
323  }
324 
325  case BIGINT:
326  {
327  bindings[i].buffer_type=MYSQL_TYPE_LONGLONG;
328  break;
329  }
330 
331  case FLOAT:
332  {
333  bindings[i].buffer_type=MYSQL_TYPE_FLOAT;
334  break;
335  }
336 
337  case DOUBLE:
338  {
339  bindings[i].buffer_type=MYSQL_TYPE_DOUBLE;
340  break;
341  }
342 
343  case DATE:
344  {
345  TypedConversion<Date>* conv = new TypedConversion<Date>;
346  bindings[i].buffer = &conv->internal;
347  bindings[i].buffer_type = MYSQL_TYPE_DATE;
348  conversions[i].reset(conv);
349  break;
350  }
351 
352  case DATETIME:
353  {
354  TypedConversion<Datetime>* conv = new TypedConversion<Datetime>;
355  bindings[i].buffer = &conv->internal;
356  bindings[i].buffer_type = MYSQL_TYPE_DATETIME;
357  conversions[i].reset(conv);
358  break;
359  }
360 
361  case TIME:
362  {
363  TypedConversion<Time>* conv = new TypedConversion<Time>;
364  bindings[i].buffer = &conv->internal;
365  bindings[i].buffer_type = MYSQL_TYPE_TIME;
366  conversions[i].reset(conv);
367  break;
368  }
369 
370  case BLOB:
371  {
372  TypedConversion<Blob>* conv = new TypedConversion<Blob>(i, stmt, MYSQL_TYPE_BLOB, bindings[i].buffer);
373  bindings[i].length = &conv->length;
374  bindings[i].buffer_type = conv->bufferType;
375  conversions[i].reset(conv);
376  break;
377  }
378 
379  case TEXT:
380  {
381  TypedConversion<Text>* conv = new TypedConversion<Text>(i, stmt, MYSQL_TYPE_STRING, bindings[i].buffer);
382  bindings[i].length = &conv->length;
383  bindings[i].buffer_type = conv->bufferType;
384  conversions[i].reset(conv);
385  break;
386  }
387 
388  case WTEXT:
389  {
390  TypedConversion<Wtext>* conv = new TypedConversion<Wtext>(i, stmt, bindings[i].buffer);
391 
392  bindings[i].length = &conv->length;
393  bindings[i].buffer_type = conv->bufferType;
394  conversions[i].reset(conv);
395  break;
396  }
397 
398  case CHAR:
399  case BINARY:
400  {
401  bindings[i].buffer_length = element.size;
402  bindings[i].buffer_type = element.type==CHAR?MYSQL_TYPE_STRING:MYSQL_TYPE_BLOB;
403  break;
404  }
405 
406  default:
407  {
408  // Invalid element type, this shouldn't happen
409  break;
410  }
411  }
412  }
413 }
414 
415 void ASql::MySQL::Statement::bindBindings(Data::Set& set, Data::Conversions& conversions, boost::scoped_array<MYSQL_BIND>& bindings, const std::deque<unsigned char>* order)
416 {
417  const int bindSize=order?order->size():set.numberOfSqlElements();
418  for(int i=0; i<bindSize; ++i)
419  {
420  const unsigned char index = order?(*order)[i]:i;
421  Data::Index element = set.getSqlIndex(index);
422 
423  if(element.type >= Data::U_TINY_N)
424  {
425  bindings[i].is_null = (my_bool*)&((Data::NullablePar*)element.data)->nullness;
426  element.data = ((Data::NullablePar*)element.data)->getVoid();
427  }
428 
429  Data::Conversions::iterator it=conversions.find(i);
430  if(it==conversions.end())
431  bindings[i].buffer=element.data;
432  else
433  {
434  it->second->external=element.data;
435  bindings[i].buffer=it->second->getPointer();
436  }
437  }
438 }
439 
440 void ASql::MySQL::TypedConversion<ASql::Data::Datetime>::convertResult()
441 {
442  try
443  {
444  *(boost::posix_time::ptime*)external=boost::posix_time::ptime(boost::gregorian::date(internal.year, internal.month, internal.day), boost::posix_time::time_duration(internal.hour, internal.minute, internal.second));
445  }
446  catch(...)
447  {
448  *(boost::posix_time::ptime*)external==boost::posix_time::not_a_date_time;
449  }
450 }
451 
452 void ASql::MySQL::TypedConversion<ASql::Data::Datetime>::convertParam()
453 {
454  std::memset(&internal, 0, sizeof(MYSQL_TIME));
455  internal.year = ((boost::posix_time::ptime*)external)->date().year();
456  internal.month = ((boost::posix_time::ptime*)external)->date().month();
457  internal.day = ((boost::posix_time::ptime*)external)->date().day();
458  internal.hour = ((boost::posix_time::ptime*)external)->time_of_day().hours();
459  internal.minute = ((boost::posix_time::ptime*)external)->time_of_day().minutes();
460  internal.second = ((boost::posix_time::ptime*)external)->time_of_day().seconds();
461 }
462 
463 void ASql::MySQL::TypedConversion<ASql::Data::Date>::convertResult()
464 {
465  try
466  {
467  *(boost::gregorian::date*)external=boost::gregorian::date(internal.year, internal.month, internal.day);
468  }
469  catch(...)
470  {
471  *(boost::gregorian::date*)external==boost::gregorian::date(boost::gregorian::not_a_date_time);
472  }
473 }
474 
475 void ASql::MySQL::TypedConversion<ASql::Data::Date>::convertParam()
476 {
477  std::memset(&internal, 0, sizeof(MYSQL_TIME));
478  internal.year = ((boost::gregorian::date*)external)->year();
479  internal.month = ((boost::gregorian::date*)external)->month();
480  internal.day = ((boost::gregorian::date*)external)->day();
481 }
482 
483 void ASql::MySQL::TypedConversion<ASql::Data::Time>::convertResult()
484 {
485  *(boost::posix_time::time_duration*)external = boost::posix_time::time_duration(internal.neg?internal.hour*-1:internal.hour, internal.minute, internal.second);
486 }
487 
488 void ASql::MySQL::TypedConversion<ASql::Data::Time>::convertParam()
489 {
490  std::memset(&internal, 0, sizeof(MYSQL_TIME));
491  internal.hour = std::abs(((boost::posix_time::time_duration*)external)->hours());
492  internal.minute = std::abs(((boost::posix_time::time_duration*)external)->minutes());
493  internal.second = std::abs(((boost::posix_time::time_duration*)external)->seconds());
494  internal.neg = ((boost::posix_time::time_duration*)external)->hours() < 0 ? 1:0;
495 }
496 
497 template void ASql::MySQL::TypedConversion<ASql::Data::Blob>::grabIt(ASql::Data::Blob& data);
498 template void ASql::MySQL::TypedConversion<ASql::Data::Text>::grabIt(ASql::Data::Text& data);
499 template<class T> void ASql::MySQL::TypedConversion<T>::grabIt(T& data)
500 {
501  if(data.size() != length) data.resize(length);
502 
503  if(length)
504  {
505  MYSQL_BIND bind;
506  std::memset(&bind, 0, sizeof(bind));
507  bind.buffer=&data[0];
508  bind.buffer_length=length;
509  bind.length=&length;
510  bind.buffer_type=bufferType;
511  if(mysql_stmt_fetch_column(statement, &bind, column, 0)!=0) throw Error(statement);
512  }
513 }
514 
515 template void ASql::MySQL::TypedConversion<ASql::Data::Blob>::convertParam();
516 template void ASql::MySQL::TypedConversion<ASql::Data::Text>::convertParam();
517 template<class T> void ASql::MySQL::TypedConversion<T>::convertParam()
518 {
519  T& data = *(T*)external;
520 
521  length = data.size();
522  if(length) buffer = &data[0];
523  else buffer=0;
524 }
525 
526 void ASql::MySQL::TypedConversion<ASql::Data::Wtext>::convertResult()
527 {
528  std::vector<char>& conversionBuffer = inputBuffer;
529  Data::VectorBlob blob(inputBuffer);
530  grabIt(blob);
531 
532  std::wstring& output = *(std::wstring*)external;
533  output.resize(conversionBuffer.size());
534 
535  if(conversionBuffer.size())
536  {
537  wchar_t* it;
538  const char* tmp;
539  mbstate_t conversionState = mbstate_t();
540  if(std::use_facet<std::codecvt<wchar_t, char, mbstate_t> >(std::locale(std::locale::classic(), new utf8CodeCvt::utf8_codecvt_facet)).in(conversionState, (const char*)&conversionBuffer.front(), (const char*)&conversionBuffer.front() + conversionBuffer.size(), tmp, &output[0], &output[0] + output.size(), it)!=std::codecvt_base::ok)
541  throw ASql::Error(CodeConversionErrorMsg, -1);
542  output.resize(it-&output[0]);
543  conversionBuffer.clear();
544  }
545 }
546 
547 void ASql::MySQL::TypedConversion<ASql::Data::Wtext>::convertParam()
548 {
549  using namespace std;
550 
551  wstring& data = *(wstring*)external;
552 
553  inputBuffer.resize(data.size()*sizeof(wchar_t));
554 
555  if(inputBuffer.size())
556  {
557  const wchar_t* tmp;
558  char* it;
559  mbstate_t conversionState = mbstate_t();
560  if(use_facet<codecvt<wchar_t, char, mbstate_t> >(locale(locale::classic(), new utf8CodeCvt::utf8_codecvt_facet)).out(conversionState, (const wchar_t*)&data[0], (const wchar_t*)&data[0] + data.size(), tmp, &inputBuffer.front(), &inputBuffer.front() + inputBuffer.size(), it)!=codecvt_base::ok) throw ASql::Error(CodeConversionErrorMsg, -1);
561  inputBuffer.resize(it-&inputBuffer[0]);
562  }
563 
564  buffer=&inputBuffer.front();
565  length = inputBuffer.size();
566 }
567 
568 
569 //Instance the ConnectionPar functions
570 template void ASql::ConnectionPar<ASql::MySQL::Statement>::start();
571 template void ASql::ConnectionPar<ASql::MySQL::Statement>::terminate();
572 template void ASql::ConnectionPar<ASql::MySQL::Statement>::intHandler(unsigned int id);
573 template void ASql::ConnectionPar<ASql::MySQL::Statement>::queue(ASql::MySQL::Statement* const& statement, QueryPar& query, int instance);
574 template void ASql::ConnectionPar<ASql::MySQL::Statement>::queue(Transaction<ASql::MySQL::Statement>& transaction, int instance);
575 template void ASql::Transaction<ASql::MySQL::Statement>::cancel();
576 
577 
578 ASql::MySQL::Error::Error(MYSQL* mysql): ASql::Error(mysql_error(mysql), mysql_errno(mysql)) { }
579 ASql::MySQL::Error::Error(MYSQL_STMT* stmt): ASql::Error(mysql_stmt_error(stmt), mysql_stmt_errno(stmt)) { }
580 
581 const char ASql::MySQL::CodeConversionErrorMsg[]="Error in code conversion to/from MySQL server.";
582 
583 ASql::MySQL::Statement::~Statement()
584 {
585  if(m_initialized)
586  {
587  for(unsigned int i=0; i<connection.threads(); ++i)
588  mysql_stmt_close(stmt[i]);
589  }
590 }
Generated on Fri Aug 24 2012 12:02:36 for fastcgi++ by   doxygen 1.8.1