2 * Copyright (C) 2005-2013 Team XBMC
5 * This Program is free software; you can redistribute it and/or modify
6 * it under the terms of the GNU General Public License as published by
7 * the Free Software Foundation; either version 2, or (at your option)
10 * This Program is distributed in the hope that it will be useful,
11 * but WITHOUT ANY WARRANTY; without even the implied warranty of
12 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
13 * GNU General Public License for more details.
15 * You should have received a copy of the GNU General Public License
16 * along with XBMC; see the file COPYING. If not, see
17 * <http://www.gnu.org/licenses/>.
25 #include "utils/log.h"
26 #include "system.h" // for GetLastError()
27 #include "network/WakeOnAccess.h"
30 #include "mysqldataset.h"
31 #include "mysql/errmsg.h"
33 #pragma comment(lib, "mysqlclient.lib")
38 #define ER_BAD_DB_ERROR 1049
44 //************* MysqlDatabase implementation ***************
46 MysqlDatabase::MysqlDatabase() {
49 _in_transaction = false; // for transaction
51 error = "Unknown database error";//S_NO_CONNECTION;
61 MysqlDatabase::~MysqlDatabase() {
65 Dataset* MysqlDatabase::CreateDataset() const {
66 return new MysqlDataset((MysqlDatabase*)this);
69 int MysqlDatabase::status(void) {
70 if (active == false) return DB_CONNECTION_NONE;
71 return DB_CONNECTION_OK;
74 int MysqlDatabase::setErr(int err_code, const char * qry) {
78 error = "Successful result";
80 case CR_COMMANDS_OUT_OF_SYNC:
81 error = "Commands were executed in an improper order";
83 case CR_SERVER_GONE_ERROR:
84 error = "The MySQL server has gone away";
87 error = "The connection to the server was lost during this query";
89 case CR_UNKNOWN_ERROR:
90 error = "An unknown error occurred";
92 case 1146: /* ER_NO_SUCH_TABLE */
93 error = "The table does not exist";
97 snprintf(err, 256, "Undefined MySQL error: Code (%d)", err_code);
100 error += "\nQuery: ";
106 const char *MysqlDatabase::getErrorMsg() {
107 return error.c_str();
110 int MysqlDatabase::connect(bool create_new) {
111 if (host.empty() || db.empty())
112 return DB_CONNECTION_NONE;
114 //CLog::Log(LOGDEBUG, "Connecting to mysql:%s:%s", host.c_str(), db.c_str());
121 conn = mysql_init(conn);
124 key.empty() ? NULL : key.c_str(),
125 cert.empty() ? NULL : cert.c_str(),
126 ca.empty() ? NULL : ca.c_str(),
127 capath.empty() ? NULL : capath.c_str(),
128 ciphers.empty() ? NULL : ciphers.c_str());
131 if (!CWakeOnAccess::Get().WakeUpHost(host, "MySQL : " + db))
132 return DB_CONNECTION_NONE;
134 // establish connection with just user credentials
135 if (mysql_real_connect(conn, host.c_str(),login.c_str(),passwd.c_str(), NULL, atoi(port.c_str()),NULL,0) != NULL)
137 // disable mysql autocommit since we handle it
138 //mysql_autocommit(conn, false);
140 // enforce utf8 charset usage
141 default_charset = mysql_character_set_name(conn);
142 if(mysql_set_character_set(conn, "utf8")) // returns 0 on success
144 CLog::Log(LOGERROR, "Unable to set utf8 charset: %s [%d](%s)",
145 db.c_str(), mysql_errno(conn), mysql_error(conn));
151 // nothing to see here
158 sprintf(sqlcmd, "CREATE DATABASE `%s` CHARACTER SET utf8 COLLATE utf8_general_ci", db.c_str());
159 if ( (ret=query_with_reconnect(sqlcmd)) != MYSQL_OK )
161 throw DbErrors("Can't create new database: '%s' (%d)", db.c_str(), ret);
165 if (mysql_select_db(conn, db.c_str()) == 0)
168 return DB_CONNECTION_OK;
172 // if we failed above, either credentials were incorrect or the database didn't exist
173 if (mysql_errno(conn) == ER_BAD_DB_ERROR && create_new)
176 if (create() == MYSQL_OK)
180 return DB_CONNECTION_OK;
184 CLog::Log(LOGERROR, "Unable to open database: %s [%d](%s)",
185 db.c_str(), mysql_errno(conn), mysql_error(conn));
187 return DB_CONNECTION_NONE;
191 CLog::Log(LOGERROR, "Unable to open database: %s (%u)",
192 db.c_str(), GetLastError());
194 return DB_CONNECTION_NONE;
197 void MysqlDatabase::disconnect(void) {
207 int MysqlDatabase::create() {
208 return connect(true);
211 int MysqlDatabase::drop() {
213 throw DbErrors("Can't drop database: no active connection...");
216 sprintf(sqlcmd,"DROP DATABASE `%s`", db.c_str());
217 if ( (ret=query_with_reconnect(sqlcmd)) != MYSQL_OK )
219 throw DbErrors("Can't drop database: '%s' (%d)", db.c_str(), ret);
222 return DB_COMMAND_OK;
225 int MysqlDatabase::copy(const char *backup_name) {
226 if ( !active || conn == NULL)
227 throw DbErrors("Can't copy database: no active connection...");
232 // ensure we're connected to the db we are about to copy
233 if ( (ret=mysql_select_db(conn, db.c_str())) != MYSQL_OK )
234 throw DbErrors("Can't connect to source database: '%s'",db.c_str());
236 // grab a list of base tables only (no views)
237 sprintf(sql, "SHOW FULL TABLES WHERE Table_type = 'BASE TABLE'");
238 if ( (ret=query_with_reconnect(sql)) != MYSQL_OK )
239 throw DbErrors("Can't determine base tables for copy.");
241 // get list of all tables from old DB
242 MYSQL_RES* res = mysql_store_result(conn);
246 if (mysql_num_rows(res) == 0)
248 mysql_free_result(res);
249 throw DbErrors("The source database was unexpectedly empty.");
252 // create the new database
253 sprintf(sql, "CREATE DATABASE `%s` CHARACTER SET utf8 COLLATE utf8_general_ci", backup_name);
254 if ( (ret=query_with_reconnect(sql)) != MYSQL_OK )
256 mysql_free_result(res);
257 throw DbErrors("Can't create database for copy: '%s' (%d)", db.c_str(), ret);
262 // duplicate each table from old db to new db
263 while ( (row=mysql_fetch_row(res)) != NULL )
265 // copy the table definition
266 sprintf(sql, "CREATE TABLE %s.%s LIKE %s",
267 backup_name, row[0], row[0]);
269 if ( (ret=query_with_reconnect(sql)) != MYSQL_OK )
271 mysql_free_result(res);
272 throw DbErrors("Can't copy schema for table '%s'\nError: %s", db.c_str(), ret);
275 // copy the table data
276 sprintf(sql, "INSERT INTO %s.%s SELECT * FROM %s",
277 backup_name, row[0], row[0]);
279 if ( (ret=query_with_reconnect(sql)) != MYSQL_OK )
281 mysql_free_result(res);
282 throw DbErrors("Can't copy data for table '%s'\nError: %d", row[0], ret);
285 mysql_free_result(res);
287 // we don't recreate views, indicies, or triggers on copy
288 // as we'll be dropping and recreating them anyway
294 int MysqlDatabase::drop_analytics(void) {
295 if ( !active || conn == NULL)
296 throw DbErrors("Can't clean database: no active connection...");
301 // ensure we're connected to the db we are about to clean from stuff
302 if ( (ret=mysql_select_db(conn, db.c_str())) != MYSQL_OK )
303 throw DbErrors("Can't connect to database: '%s'",db.c_str());
305 // getting a list of indexes in the database
306 sprintf(sql, "SELECT DISTINCT table_name, index_name"
307 " FROM information_schema.statistics"
308 " WHERE index_name != 'PRIMARY' AND"
309 " table_schema = '%s'", db.c_str());
310 if ( (ret=query_with_reconnect(sql)) != MYSQL_OK )
311 throw DbErrors("Can't determine list of indexes to drop.");
313 // we will acquire lists here
314 MYSQL_RES* res = mysql_store_result(conn);
319 while ( (row=mysql_fetch_row(res)) != NULL )
321 sprintf(sql, "ALTER TABLE %s.%s DROP INDEX %s", db.c_str(), row[0], row[1]);
323 if ( (ret=query_with_reconnect(sql)) != MYSQL_OK )
325 mysql_free_result(res);
326 throw DbErrors("Can't drop index '%s'\nError: %d", row[0], ret);
329 mysql_free_result(res);
332 // next topic is a views list
333 sprintf(sql, "SELECT table_name"
334 " FROM information_schema.views"
335 " WHERE table_schema = '%s'", db.c_str());
336 if ( (ret=query_with_reconnect(sql)) != MYSQL_OK )
337 throw DbErrors("Can't determine list of views to drop.");
339 res = mysql_store_result(conn);
343 while ( (row=mysql_fetch_row(res)) != NULL )
345 /* we do not need IF EXISTS because these views are exist */
346 sprintf(sql, "DROP VIEW %s.%s", db.c_str(), row[0]);
348 if ( (ret=query_with_reconnect(sql)) != MYSQL_OK )
350 mysql_free_result(res);
351 throw DbErrors("Can't drop view '%s'\nError: %d", row[0], ret);
354 mysql_free_result(res);
358 sprintf(sql, "SELECT trigger_name"
359 " FROM information_schema.triggers"
360 " WHERE event_object_schema = '%s'", db.c_str());
361 if ( (ret=query_with_reconnect(sql)) != MYSQL_OK )
362 throw DbErrors("Can't determine list of triggers to drop.");
364 res = mysql_store_result(conn);
368 while ( (row=mysql_fetch_row(res)) != NULL )
370 sprintf(sql, "DROP TRIGGER %s.%s", db.c_str(), row[0]);
372 if ( (ret=query_with_reconnect(sql)) != MYSQL_OK )
374 mysql_free_result(res);
375 throw DbErrors("Can't create trigger '%s'\nError: %s", row[0], ret);
378 mysql_free_result(res);
384 int MysqlDatabase::query_with_reconnect(const char* query) {
388 // try to reconnect if server is gone
389 while ( ((result = mysql_real_query(conn, query, strlen(query))) != MYSQL_OK) &&
390 ((result = mysql_errno(conn)) == CR_SERVER_GONE_ERROR || result == CR_SERVER_LOST) &&
393 CLog::Log(LOGINFO,"MYSQL server has gone. Will try %d more attempt(s) to reconnect.", attempts);
401 long MysqlDatabase::nextid(const char* sname) {
402 CLog::Log(LOGDEBUG,"MysqlDatabase::nextid for %s",sname);
403 if (!active) return DB_UNEXPECTED_RESULT;
404 const char* seq_table = "sys_seq";
405 int id;/*,nrow,ncol;*/
408 sprintf(sqlcmd,"select nextid from %s where seq_name = '%s'",seq_table, sname);
409 CLog::Log(LOGDEBUG,"MysqlDatabase::nextid will request");
410 if ((last_err = query_with_reconnect(sqlcmd)) != 0)
412 return DB_UNEXPECTED_RESULT;
414 res = mysql_store_result(conn);
415 if (mysql_num_rows(res) == 0)
418 sprintf(sqlcmd,"insert into %s (nextid,seq_name) values (%d,'%s')",seq_table,id,sname);
419 mysql_free_result(res);
420 if ((last_err = query_with_reconnect(sqlcmd)) != 0) return DB_UNEXPECTED_RESULT;
425 MYSQL_ROW row = mysql_fetch_row(res);
428 unsigned long *lengths;
429 lengths = mysql_fetch_lengths(res);
430 CLog::Log(LOGINFO,"Next id is [%.*s] ", (int) lengths[0], row[0]);
431 sprintf(sqlcmd,"update %s set nextid=%d where seq_name = '%s'",seq_table,id,sname);
432 mysql_free_result(res);
433 if ((last_err = query_with_reconnect(sqlcmd) != 0)) return DB_UNEXPECTED_RESULT;
436 return DB_UNEXPECTED_RESULT;
439 // methods for transactions
440 // ---------------------------------------------
441 void MysqlDatabase::start_transaction() {
444 CLog::Log(LOGDEBUG,"Mysql Start transaction");
445 _in_transaction = true;
449 void MysqlDatabase::commit_transaction() {
453 CLog::Log(LOGDEBUG,"Mysql commit transaction");
454 _in_transaction = false;
458 void MysqlDatabase::rollback_transaction() {
461 mysql_rollback(conn);
462 CLog::Log(LOGDEBUG,"Mysql rollback transaction");
463 _in_transaction = false;
467 bool MysqlDatabase::exists(void) {
470 if ( conn == NULL || mysql_ping(conn) )
472 CLog::Log(LOGERROR, "Not connected to database, test of existence is not possible.");
476 MYSQL_RES* result = mysql_list_dbs(conn, db.c_str());
479 CLog::Log(LOGERROR,"Database is not present, does the user has CREATE DATABASE permission");
483 ret = (mysql_num_rows(result) > 0);
484 mysql_free_result(result);
486 // Check if there is some tables ( to permit user with no create database rights
489 result = mysql_list_tables(conn, NULL);
491 ret = (mysql_num_rows(result) > 0);
493 mysql_free_result(result);
499 // methods for formatting
500 // ---------------------------------------------
501 string MysqlDatabase::vprepare(const char *format, va_list args)
503 string strFormat = format;
504 string strResult = "";
508 // %q is the sqlite format string for %s.
509 // Any bad character, like "'", will be replaced with a proper one
511 while ( (pos = strFormat.find("%s", pos)) != string::npos )
512 strFormat.replace(pos++, 2, "%q");
514 p = mysql_vmprintf(strFormat.c_str(), args);
520 // RAND() is the mysql form of RANDOM()
522 while ( (pos = strResult.find("RANDOM()", pos)) != string::npos )
524 strResult.replace(pos++, 8, "RAND()");
532 /* vsprintf() functionality is based on sqlite3.c functions */
535 ** Conversion types fall into various categories as defined by the
536 ** following enumeration.
538 #define etRADIX 1 /* Integer types. %d, %x, %o, and so forth */
539 #define etFLOAT 2 /* Floating point. %f */
540 #define etEXP 3 /* Exponentional notation. %e and %E */
541 #define etGENERIC 4 /* Floating or exponential, depending on exponent. %g */
542 #define etSIZE 5 /* Return number of characters processed so far. %n */
543 #define etSTRING 6 /* Strings. %s */
544 #define etDYNSTRING 7 /* Dynamically allocated strings. %z */
545 #define etPERCENT 8 /* Percent symbol. %% */
546 #define etCHARX 9 /* Characters. %c */
547 /* The rest are extensions, not normally found in printf() */
548 #define etSQLESCAPE 10 /* Strings with '\'' doubled. Stings with '\\' escaped. %q */
549 #define etSQLESCAPE2 11 /* Strings with '\'' doubled and enclosed in '',
550 NULL pointers replaced by SQL NULL. %Q */
551 #define etPOINTER 14 /* The %p conversion */
552 #define etSQLESCAPE3 15 /* %w -> Strings with '\"' doubled */
554 #define etINVALID 0 /* Any unrecognized conversion type */
556 #define ARRAY_SIZE(X) ((int)(sizeof(X)/sizeof(X[0])))
559 ** An "etByte" is an 8-bit unsigned value.
561 typedef unsigned char etByte;
564 ** Each builtin conversion character (ex: the 'd' in "%d") is described
565 ** by an instance of the following structure
567 typedef struct et_info { /* Information about each format field */
568 char fmttype; /* The format field code letter */
569 etByte base; /* The base for radix conversion */
570 etByte flags; /* One or more of FLAG_ constants below */
571 etByte type; /* Conversion paradigm */
572 etByte charset; /* Offset into aDigits[] of the digits string */
573 etByte prefix; /* Offset into aPrefix[] of the prefix string */
577 ** An objected used to accumulate the text of a string where we
578 ** do not necessarily know how big the string will be in the end.
581 char *zBase; /* A base allocation. Not from malloc. */
582 char *zText; /* The string collected so far */
583 int nChar; /* Length of the string so far */
584 int nAlloc; /* Amount of space allocated in zText */
585 int mxAlloc; /* Maximum allowed string length */
586 bool mallocFailed; /* Becomes true if any memory allocation fails */
587 bool tooBig; /* Becomes true if string size exceeds limits */
591 ** Allowed values for et_info.flags
593 #define FLAG_SIGNED 1 /* True if the value to convert is signed */
594 #define FLAG_INTERN 2 /* True if for internal use only */
595 #define FLAG_STRING 4 /* Allow infinity precision */
599 ** The following table is searched linearly, so it is good to put the
600 ** most frequently used conversion types first.
602 static const char aDigits[] = "0123456789ABCDEF0123456789abcdef";
603 static const char aPrefix[] = "-x0\000X0";
604 static const et_info fmtinfo[] = {
605 { 'd', 10, 1, etRADIX, 0, 0 },
606 { 's', 0, 4, etSTRING, 0, 0 },
607 { 'g', 0, 1, etGENERIC, 30, 0 },
608 { 'z', 0, 4, etDYNSTRING, 0, 0 },
609 { 'q', 0, 4, etSQLESCAPE, 0, 0 },
610 { 'Q', 0, 4, etSQLESCAPE2, 0, 0 },
611 { 'w', 0, 4, etSQLESCAPE3, 0, 0 },
612 { 'c', 0, 0, etCHARX, 0, 0 },
613 { 'o', 8, 0, etRADIX, 0, 2 },
614 { 'u', 10, 0, etRADIX, 0, 0 },
615 { 'x', 16, 0, etRADIX, 16, 1 },
616 { 'X', 16, 0, etRADIX, 0, 4 },
617 { 'f', 0, 1, etFLOAT, 0, 0 },
618 { 'e', 0, 1, etEXP, 30, 0 },
619 { 'E', 0, 1, etEXP, 14, 0 },
620 { 'G', 0, 1, etGENERIC, 14, 0 },
621 { 'i', 10, 1, etRADIX, 0, 0 },
622 { 'n', 0, 0, etSIZE, 0, 0 },
623 { '%', 0, 0, etPERCENT, 0, 0 },
624 { 'p', 16, 0, etPOINTER, 0, 1 },
628 ** "*val" is a double such that 0.1 <= *val < 10.0
629 ** Return the ascii code for the leading digit of *val, then
630 ** multiply "*val" by 10.0 to renormalize.
633 ** input: *val = 3.14159
634 ** output: *val = 1.4159 function return = '3'
636 ** The counter *cnt is incremented each time. After counter exceeds
637 ** 16 (the number of significant digits in a 64-bit float) '0' is
640 char MysqlDatabase::et_getdigit(double *val, int *cnt) {
643 if( (*cnt)++ >= 16 ) return '0';
647 *val = (*val - d)*10.0;
652 ** Append N space characters to the given string buffer.
654 void MysqlDatabase::appendSpace(StrAccum *pAccum, int N) {
655 static const char zSpaces[] = " ";
656 while( N>=(int)sizeof(zSpaces)-1 ) {
657 mysqlStrAccumAppend(pAccum, zSpaces, sizeof(zSpaces)-1);
658 N -= sizeof(zSpaces)-1;
661 mysqlStrAccumAppend(pAccum, zSpaces, N);
665 #ifndef MYSQL_PRINT_BUF_SIZE
666 # define MYSQL_PRINT_BUF_SIZE 350
669 #define etBUFSIZE MYSQL_PRINT_BUF_SIZE /* Size of the output buffer */
674 ** The maximum length of a TEXT or BLOB in bytes. This also
675 ** limits the size of a row in a table or index.
677 ** The hard limit is the ability of a 32-bit signed integer
678 ** to count the size: 2^31-1 or 2147483647.
680 #ifndef MYSQL_MAX_LENGTH
681 # define MYSQL_MAX_LENGTH 1000000000
685 ** The root program. All variations call this core.
688 ** func This is a pointer to a function taking three arguments
689 ** 1. A pointer to anything. Same as the "arg" parameter.
690 ** 2. A pointer to the list of characters to be output
691 ** (Note, this list is NOT null terminated.)
692 ** 3. An integer number of characters to be output.
693 ** (Note: This number might be zero.)
695 ** arg This is the pointer to anything which will be passed as the
696 ** first argument to "func". Use it for whatever you like.
698 ** fmt This is the format string, as in the usual print.
700 ** ap This is a pointer to a list of arguments. Same as in
704 ** The return value is the total number of characters sent to
705 ** the function "func". Returns -1 on a error.
707 ** Note that the order in which automatic variables are declared below
708 ** seems to make a big difference in determining how fast this beast
711 void MysqlDatabase::mysqlVXPrintf(
712 StrAccum *pAccum, /* Accumulate results here */
713 int useExtended, /* Allow extended %-conversions */
714 const char *fmt, /* Format string */
715 va_list ap /* arguments */
717 int c; /* Next character in the format string */
718 char *bufpt; /* Pointer to the conversion buffer */
719 int precision; /* Precision of the current field */
720 int length; /* Length of the field */
721 int idx; /* A general purpose loop counter */
722 int width; /* Width of the current field */
723 etByte flag_leftjustify; /* True if "-" flag is present */
724 etByte flag_plussign; /* True if "+" flag is present */
725 etByte flag_blanksign; /* True if " " flag is present */
726 etByte flag_alternateform; /* True if "#" flag is present */
727 etByte flag_altform2; /* True if "!" flag is present */
728 etByte flag_zeropad; /* True if field width constant starts with zero */
729 etByte flag_long; /* True if "l" flag is present */
730 etByte flag_longlong; /* True if the "ll" flag is present */
731 etByte done; /* Loop termination flag */
732 uint64_t longvalue; /* Value for integer types */
733 double realvalue; /* Value for real types */
734 const et_info *infop; /* Pointer to the appropriate info structure */
735 char buf[etBUFSIZE]; /* Conversion buffer */
736 char prefix; /* Prefix character. "+" or "-" or " " or '\0'. */
737 etByte xtype = 0; /* Conversion paradigm */
738 char *zExtra; /* Extra memory used for etTCLESCAPE conversions */
739 int exp, e2; /* exponent of real numbers */
740 double rounder; /* Used for rounding floating point values */
741 etByte flag_dp; /* True if decimal point should be shown */
742 etByte flag_rtz; /* True if trailing zeros should be removed */
743 etByte flag_exp; /* True to force display of the exponent */
744 int nsd; /* Number of significant digits returned */
748 for(; (c=(*fmt))!=0; ++fmt){
753 while( (c=(*++fmt))!='%' && c!=0 ) amt++;
754 mysqlStrAccumAppend(pAccum, bufpt, amt);
757 if( (c=(*++fmt))==0 ){
758 mysqlStrAccumAppend(pAccum, "%", 1);
761 /* Find out what flags are present */
762 flag_leftjustify = flag_plussign = flag_blanksign = flag_alternateform = flag_altform2 = flag_zeropad = 0;
768 case '-': flag_leftjustify = 1; break;
769 case '+': flag_plussign = 1; break;
770 case ' ': flag_blanksign = 1; break;
771 case '#': flag_alternateform = 1; break;
772 case '!': flag_altform2 = 1; break;
773 case '0': flag_zeropad = 1; break;
774 default: done = 1; break;
776 } while( !done && (c=(*++fmt))!=0 );
777 /* Get the field width */
780 width = va_arg(ap,int);
782 flag_leftjustify = 1;
787 while( c>='0' && c<='9' ){
788 width = width*10 + c - '0';
792 if( width > etBUFSIZE-10 ){
793 width = etBUFSIZE-10;
795 /* Get the precision */
800 precision = va_arg(ap,int);
801 if( precision<0 ) precision = -precision;
804 while( c>='0' && c<='9' ){
805 precision = precision*10 + c - '0';
812 /* Get the conversion type modifier */
823 flag_long = flag_longlong = 0;
825 /* Fetch the info entry for the field */
828 for(idx=0; idx<ARRAY_SIZE(fmtinfo); idx++){
829 if( c==fmtinfo[idx].fmttype ){
830 infop = &fmtinfo[idx];
831 if( useExtended || (infop->flags & FLAG_INTERN)==0 ){
842 /* Limit the precision to prevent overflowing buf[] during conversion */
843 if( precision>etBUFSIZE-40 && (infop->flags & FLAG_STRING)==0 ){
844 precision = etBUFSIZE-40;
848 ** At this point, variables are initialized as follows:
850 ** flag_alternateform TRUE if a '#' is present.
851 ** flag_altform2 TRUE if a '!' is present.
852 ** flag_plussign TRUE if a '+' is present.
853 ** flag_leftjustify TRUE if a '-' is present or if the
854 ** field width was negative.
855 ** flag_zeropad TRUE if the width began with 0.
856 ** flag_long TRUE if the letter 'l' (ell) prefixed
857 ** the conversion character.
858 ** flag_longlong TRUE if the letter 'll' (ell ell) prefixed
859 ** the conversion character.
860 ** flag_blanksign TRUE if a ' ' is present.
861 ** width The specified field width. This is
862 ** always non-negative. Zero is the default.
863 ** precision The specified precision. The default
865 ** xtype The class of the conversion.
866 ** infop Pointer to the appropriate info struct.
870 flag_longlong = sizeof(char*)==sizeof(int64_t);
871 flag_long = sizeof(char*)==sizeof(long int);
872 /* Fall through into the next case */
874 if( infop->flags & FLAG_SIGNED ){
877 v = va_arg(ap,int64_t);
878 }else if( flag_long ){
879 v = va_arg(ap,long int);
888 if( flag_plussign ) prefix = '+';
889 else if( flag_blanksign ) prefix = ' ';
894 longvalue = va_arg(ap,uint64_t);
895 }else if( flag_long ){
896 longvalue = va_arg(ap,unsigned long int);
898 longvalue = va_arg(ap,unsigned int);
902 if( longvalue==0 ) flag_alternateform = 0;
903 if( flag_zeropad && precision<width-(prefix!=0) ){
904 precision = width-(prefix!=0);
906 bufpt = &buf[etBUFSIZE-1];
908 register const char *cset; /* Use registers for speed */
910 cset = &aDigits[infop->charset];
912 do{ /* Convert to ascii */
913 *(--bufpt) = cset[longvalue%base];
914 longvalue = longvalue/base;
915 }while( longvalue>0 );
917 length = (int)(&buf[etBUFSIZE-1]-bufpt);
918 for(idx=precision-length; idx>0; idx--){
919 *(--bufpt) = '0'; /* Zero pad */
921 if( prefix ) *(--bufpt) = prefix; /* Add sign */
922 if( flag_alternateform && infop->prefix ){ /* Add "0" or "0x" */
925 pre = &aPrefix[infop->prefix];
926 for(; (x=(*pre))!=0; pre++) *(--bufpt) = x;
928 length = (int)(&buf[etBUFSIZE-1]-bufpt);
934 realvalue = va_arg(ap,double);
935 if( precision<0 ) precision = 6; /* Set default precision */
936 if( precision>etBUFSIZE/2-10 ) precision = etBUFSIZE/2-10;
938 realvalue = -realvalue;
941 if( flag_plussign ) prefix = '+';
942 else if( flag_blanksign ) prefix = ' ';
945 if( xtype==etGENERIC && precision>0 ) precision--;
946 /* It makes more sense to use 0.5 */
947 for(idx=precision, rounder=0.5; idx>0; idx--, rounder*=0.1){}
948 if( xtype==etFLOAT ) realvalue += rounder;
949 /* Normalize realvalue to within 10.0 > realvalue >= 1.0 */
952 if( mysqlIsNaN((double)realvalue) ){
959 while( realvalue>=1e32 && exp<=350 ){ realvalue *= 1e-32; exp+=32; }
960 while( realvalue>=1e8 && exp<=350 ){ realvalue *= 1e-8; exp+=8; }
961 while( realvalue>=10.0 && exp<=350 ){ realvalue *= 0.1; exp++; }
962 while( realvalue<1e-8 ){ realvalue *= 1e8; exp-=8; }
963 while( realvalue<1.0 ){ realvalue *= 10.0; exp--; }
966 bufpt = (char *)"-Inf";
967 }else if( prefix=='+' ){
968 bufpt = (char *)"+Inf";
970 bufpt = (char *)"Inf";
972 length = strlen(bufpt);
978 ** If the field type is etGENERIC, then convert to either etEXP
979 ** or etFLOAT, as appropriate.
981 flag_exp = xtype==etEXP;
982 if( xtype!=etFLOAT ){
983 realvalue += rounder;
984 if( realvalue>=10.0 ){ realvalue *= 0.1; exp++; }
986 if( xtype==etGENERIC ){
987 flag_rtz = !flag_alternateform;
988 if( exp<-4 || exp>precision ){
991 precision = precision - exp;
1003 flag_dp = (precision>0 ?1:0) | flag_alternateform | flag_altform2;
1004 /* The sign in front of the number */
1006 *(bufpt++) = prefix;
1008 /* Digits prior to the decimal point */
1013 *(bufpt++) = et_getdigit(&realvalue,&nsd);
1016 /* The decimal point */
1020 /* "0" digits after the decimal point but before the first
1021 ** significant digit of the number */
1022 for(e2++; e2<0; precision--, e2++){
1023 //ASSERT( precision>0 );
1026 /* Significant digits after the decimal point */
1027 while( (precision--)>0 ){
1028 *(bufpt++) = et_getdigit(&realvalue,&nsd);
1030 /* Remove trailing zeros and the "." if no digits follow the "." */
1031 if( flag_rtz && flag_dp ){
1032 while( bufpt[-1]=='0' ) *(--bufpt) = 0;
1033 //ASSERT( bufpt>buf );
1034 if( bufpt[-1]=='.' ){
1035 if( flag_altform2 ){
1042 /* Add the "eNNN" suffix */
1043 if( flag_exp || xtype==etEXP ){
1044 *(bufpt++) = aDigits[infop->charset];
1046 *(bufpt++) = '-'; exp = -exp;
1051 *(bufpt++) = (char)((exp/100)+'0'); /* 100's digit */
1054 *(bufpt++) = (char)(exp/10+'0'); /* 10's digit */
1055 *(bufpt++) = (char)(exp%10+'0'); /* 1's digit */
1059 /* The converted number is in buf[] and zero terminated. Output it.
1060 ** Note that the number is in the usual order, not reversed as with
1061 ** integer conversions. */
1062 length = (int)(bufpt-buf);
1065 /* Special case: Add leading zeros if the flag_zeropad flag is
1066 ** set and we are not left justified */
1067 if( flag_zeropad && !flag_leftjustify && length < width){
1069 int nPad = width - length;
1070 for(i=width; i>=nPad; i--){
1071 bufpt[i] = bufpt[i-nPad];
1074 while( nPad-- ) bufpt[i++] = '0';
1079 *(va_arg(ap,int*)) = pAccum->nChar;
1091 for(idx=1; idx<precision; idx++) buf[idx] = (char)c;
1100 bufpt = va_arg(ap,char*);
1103 }else if( xtype==etDYNSTRING ){
1107 for(length=0; length<precision && bufpt[length]; length++){}
1109 length = strlen(bufpt);
1114 case etSQLESCAPE3: {
1115 int i, j, k, n, isnull;
1118 char q = ((xtype==etSQLESCAPE3)?'"':'\''); /* Quote character */
1119 const char *escarg = va_arg(ap, char*);
1121 if( isnull ) escarg = (xtype==etSQLESCAPE2 ? "NULL" : "(NULL)");
1123 for(i=0; k!=0 && (ch=escarg[i])!=0; i++, k--);
1124 needQuote = !isnull && xtype==etSQLESCAPE2;
1125 n = i*2 + 1 + needQuote*2;
1127 bufpt = zExtra = (char *)malloc(n);
1129 pAccum->mallocFailed = 1;
1136 if( needQuote ) bufpt[j++] = q;
1138 j += mysql_real_escape_string(conn, bufpt, escarg, k);
1139 if( needQuote ) bufpt[j++] = q;
1142 /* The precision in %q and %Q means how many input characters to
1143 ** consume, not the length of the output...
1144 ** if( precision>=0 && precision<length ) length = precision; */
1150 }/* End switch over the format type */
1152 ** The text of the conversion is pointed to by "bufpt" and is
1153 ** "length" characters long. The field width is "width". Do
1156 if( !flag_leftjustify ){
1157 register int nspace;
1158 nspace = width-length;
1160 appendSpace(pAccum, nspace);
1164 mysqlStrAccumAppend(pAccum, bufpt, length);
1166 if( flag_leftjustify ){
1167 register int nspace;
1168 nspace = width-length;
1170 appendSpace(pAccum, nspace);
1176 }/* End for loop over the format string */
1177 } /* End of function */
1180 ** Append N bytes of text from z to the StrAccum object.
1182 void MysqlDatabase::mysqlStrAccumAppend(StrAccum *p, const char *z, int N) {
1183 if( p->tooBig | p->mallocFailed ){
1192 if( p->nChar+N >= p->nAlloc ){
1194 int szNew = p->nChar;
1196 if( szNew > p->mxAlloc ){
1197 mysqlStrAccumReset(p);
1203 zNew = (char *)malloc(p->nAlloc);
1205 memcpy(zNew, p->zText, p->nChar);
1206 mysqlStrAccumReset(p);
1209 p->mallocFailed = 1;
1210 mysqlStrAccumReset(p);
1214 memcpy(&p->zText[p->nChar], z, N);
1219 ** Finish off a string by making sure it is zero-terminated.
1220 ** Return a pointer to the resulting string. Return a NULL
1221 ** pointer if any kind of error was encountered.
1223 char * MysqlDatabase::mysqlStrAccumFinish(StrAccum *p){
1225 p->zText[p->nChar] = 0;
1226 if( p->zText==p->zBase ){
1227 p->zText = (char *)malloc(p->nChar+1);
1229 memcpy(p->zText, p->zBase, p->nChar+1);
1231 p->mallocFailed = 1;
1239 ** Reset an StrAccum string. Reclaim all malloced memory.
1241 void MysqlDatabase::mysqlStrAccumReset(StrAccum *p){
1242 if( p->zText!=p->zBase ){
1249 ** Initialize a string accumulator
1251 void MysqlDatabase::mysqlStrAccumInit(StrAccum *p, char *zBase, int n, int mx){
1252 p->zText = p->zBase = zBase;
1257 p->mallocFailed = 0;
1261 ** Print into memory obtained from mysql_malloc(). Omit the internal
1262 ** %-conversion extensions.
1264 char *MysqlDatabase::mysql_vmprintf(const char *zFormat, va_list ap) {
1266 char zBase[MYSQL_PRINT_BUF_SIZE];
1269 mysqlStrAccumInit(&acc, zBase, sizeof(zBase), MYSQL_MAX_LENGTH);
1270 mysqlVXPrintf(&acc, 0, zFormat, ap);
1271 z = mysqlStrAccumFinish(&acc);
1276 //************* MysqlDataset implementation ***************
1278 MysqlDataset::MysqlDataset():Dataset() {
1282 autorefresh = false;
1286 MysqlDataset::MysqlDataset(MysqlDatabase *newDb):Dataset(newDb) {
1290 autorefresh = false;
1293 MysqlDataset::~MysqlDataset() {
1294 if (errmsg) free(errmsg);
1298 void MysqlDataset::set_autorefresh(bool val) {
1304 //--------- protected functions implementation -----------------//
1306 MYSQL* MysqlDataset::handle(){
1309 return static_cast<MysqlDatabase*>(db)->getHandle();
1315 void MysqlDataset::make_query(StringList &_sql) {
1318 if (db == NULL) throw DbErrors("No Database Connection");
1321 if (autocommit) db->start_transaction();
1323 for (list<string>::iterator i =_sql.begin(); i!=_sql.end(); i++)
1326 Dataset::parse_sql(query);
1327 if ((result = static_cast<MysqlDatabase *>(db)->query_with_reconnect(query.c_str())) != MYSQL_OK)
1329 throw DbErrors(db->getErrorMsg());
1333 if (db->in_transaction() && autocommit) db->commit_transaction();
1336 ds_state = dsSelect;
1342 if (db->in_transaction()) db->rollback_transaction();
1348 void MysqlDataset::make_insert() {
1349 make_query(insert_sql);
1353 void MysqlDataset::make_edit() {
1354 make_query(update_sql);
1358 void MysqlDataset::make_deletion() {
1359 make_query(delete_sql);
1362 void MysqlDataset::fill_fields() {
1363 if ((db == NULL) || (result.record_header.size() == 0) || (result.records.size() < (unsigned int)frecno)) return;
1365 if (fields_object->size() == 0) // Filling columns name
1367 const unsigned int ncols = result.record_header.size();
1368 fields_object->resize(ncols);
1369 for (unsigned int i = 0; i < ncols; i++)
1370 (*fields_object)[i].props = result.record_header[i];
1374 if (result.records.size() != 0)
1376 const sql_record *row = result.records[frecno];
1379 const unsigned int ncols = row->size();
1380 fields_object->resize(ncols);
1381 for (unsigned int i = 0; i < ncols; i++)
1382 (*fields_object)[i].val = row->at(i);
1386 const unsigned int ncols = result.record_header.size();
1387 fields_object->resize(ncols);
1388 for (unsigned int i = 0; i < ncols; i++)
1389 (*fields_object)[i].val = "";
1393 //------------- public functions implementation -----------------//
1394 bool MysqlDataset::dropIndex(const char *table, const char *index)
1397 string sql_prepared;
1399 sql = "SELECT * FROM information_schema.statistics WHERE TABLE_SCHEMA=DATABASE() AND table_name='%s' AND index_name='%s'";
1400 sql_prepared = static_cast<MysqlDatabase*>(db)->prepare(sql.c_str(), table, index);
1402 if (!query(sql_prepared))
1407 sql = "ALTER TABLE %s DROP INDEX %s";
1408 sql_prepared = static_cast<MysqlDatabase*>(db)->prepare(sql.c_str(), table, index);
1410 if (exec(sql_prepared) != MYSQL_OK)
1417 static bool ci_test(char l, char r)
1419 return tolower(l) == tolower(r);
1422 static size_t ci_find(const string& where, const string& what)
1424 std::string::const_iterator loc = std::search(where.begin(), where.end(), what.begin(), what.end(), ci_test);
1425 if (loc == where.end())
1426 return string::npos;
1428 return loc - where.begin();
1431 int MysqlDataset::exec(const string &sql) {
1432 if (!handle()) throw DbErrors("No Database Connection");
1437 // enforce the "auto_increment" keyword to be appended to "integer primary key"
1440 if ( (loc=ci_find(qry, "integer primary key")) != string::npos)
1442 qry = qry.insert(loc + 19, " auto_increment ");
1445 // force the charset and collation to UTF-8
1446 if ( ci_find(qry, "CREATE TABLE") != string::npos
1447 || ci_find(qry, "CREATE TEMPORARY TABLE") != string::npos )
1449 qry += " CHARACTER SET utf8 COLLATE utf8_general_ci";
1452 CLog::Log(LOGDEBUG,"Mysql execute: %s", qry.c_str());
1454 if (db->setErr( static_cast<MysqlDatabase *>(db)->query_with_reconnect(qry.c_str()), qry.c_str()) != MYSQL_OK)
1456 throw DbErrors(db->getErrorMsg());
1460 // TODO: collect results and store in exec_res
1465 int MysqlDataset::exec() {
1469 const void* MysqlDataset::getExecRes() {
1474 bool MysqlDataset::query(const char *query) {
1475 if(!handle()) throw DbErrors("No Database Connection");
1476 std::string qry = query;
1477 int fs = qry.find("select");
1478 int fS = qry.find("SELECT");
1479 if (!( fs >= 0 || fS >=0))
1480 throw DbErrors("MUST be select SQL!");
1486 // mysql doesn't understand CAST(foo as integer) => change to CAST(foo as signed integer)
1487 while ((loc = ci_find(qry, "as integer)")) != string::npos)
1488 qry = qry.insert(loc + 3, "signed ");
1490 MYSQL_RES *stmt = NULL;
1492 if ( static_cast<MysqlDatabase*>(db)->setErr(static_cast<MysqlDatabase*>(db)->query_with_reconnect(qry.c_str()), qry.c_str()) != MYSQL_OK )
1493 throw DbErrors(db->getErrorMsg());
1495 MYSQL* conn = handle();
1496 stmt = mysql_store_result(conn);
1499 const unsigned int numColumns = mysql_num_fields(stmt);
1500 MYSQL_FIELD *fields = mysql_fetch_fields(stmt);
1502 result.record_header.resize(numColumns);
1503 for (unsigned int i = 0; i < numColumns; i++)
1504 result.record_header[i].name = fields[i].name;
1507 while ((row = mysql_fetch_row(stmt)))
1508 { // have a row of data
1509 sql_record *res = new sql_record;
1510 res->resize(numColumns);
1511 for (unsigned int i = 0; i < numColumns; i++)
1513 field_value &v = res->at(i);
1514 switch (fields[i].type)
1516 case MYSQL_TYPE_LONGLONG:
1517 case MYSQL_TYPE_DECIMAL:
1518 case MYSQL_TYPE_NEWDECIMAL:
1519 case MYSQL_TYPE_TINY:
1520 case MYSQL_TYPE_SHORT:
1521 case MYSQL_TYPE_INT24:
1522 case MYSQL_TYPE_LONG:
1525 v.set_asInt(atoi(row[i]));
1532 case MYSQL_TYPE_FLOAT:
1533 case MYSQL_TYPE_DOUBLE:
1536 v.set_asDouble(atof(row[i]));
1543 case MYSQL_TYPE_STRING:
1544 case MYSQL_TYPE_VAR_STRING:
1545 case MYSQL_TYPE_VARCHAR:
1546 if (row[i] != NULL) v.set_asString((const char *)row[i] );
1548 case MYSQL_TYPE_TINY_BLOB:
1549 case MYSQL_TYPE_MEDIUM_BLOB:
1550 case MYSQL_TYPE_LONG_BLOB:
1551 case MYSQL_TYPE_BLOB:
1552 if (row[i] != NULL) v.set_asString((const char *)row[i]);
1554 case MYSQL_TYPE_NULL:
1556 CLog::Log(LOGDEBUG,"MYSQL: Unknown field type: %u", fields[i].type);
1562 result.records.push_back(res);
1564 mysql_free_result(stmt);
1566 ds_state = dsSelect;
1571 bool MysqlDataset::query(const string &q) {
1572 return query(q.c_str());
1575 void MysqlDataset::open(const string &sql) {
1576 set_select_sql(sql);
1580 void MysqlDataset::open() {
1581 if (select_sql.size())
1583 query(select_sql.c_str());
1587 ds_state = dsInactive;
1591 void MysqlDataset::close() {
1594 edit_object->clear();
1595 fields_object->clear();
1596 ds_state = dsInactive;
1601 void MysqlDataset::cancel() {
1602 if ((ds_state == dsInsert) || (ds_state==dsEdit))
1604 if (result.record_header.size())
1605 ds_state = dsSelect;
1607 ds_state = dsInactive;
1612 int MysqlDataset::num_rows() {
1613 return result.records.size();
1617 bool MysqlDataset::eof() {
1622 bool MysqlDataset::bof() {
1627 void MysqlDataset::first() {
1629 this->fill_fields();
1632 void MysqlDataset::last() {
1637 void MysqlDataset::prev(void) {
1642 void MysqlDataset::next(void) {
1648 void MysqlDataset::free_row(void)
1650 if (frecno < 0 || (unsigned int)frecno >= result.records.size())
1653 sql_record *row = result.records[frecno];
1657 result.records[frecno] = NULL;
1661 bool MysqlDataset::seek(int pos) {
1662 if (ds_state == dsSelect)
1672 int64_t MysqlDataset::lastinsertid() {
1673 if (!handle()) DbErrors("No Database Connection");
1674 return mysql_insert_id(handle());
1677 long MysqlDataset::nextid(const char *seq_name) {
1679 return db->nextid(seq_name);
1681 return DB_UNEXPECTED_RESULT;
1684 void MysqlDataset::interrupt() {