1: <?php /* vim: set expandtab : */
2: /**
3: * Mysql abstraction which only uses mysql_* functions, but can use
4: * Mysqli also.
5: *
6: * For information on binding placeholders, @see AMysql_Statement::execute()
7: *
8: * @todo Maybe remove automatic dot detection for identifier escaping.
9: *
10: * Visit https://github.com/amcsi/amysql
11: * @author Szerémi Attila
12: * @license MIT License; http://www.opensource.org/licenses/mit-license.php
13: **/
14: abstract class AMysql_Abstract
15: {
16:
17: public $insertId; // last insert id
18: public $lastStatement; // last AMysql_Statement
19: public $link = null; // mysql link
20: public $isMysqli; // mysql or mysqli
21: public $error; // last error message
22: public $errno; // last error number
23: public $result; // last mysql result
24: public $query; // last used query string
25: public $affectedRows; // last affected rows count
26: /**
27: * What value to reset autocommit to after a rollback or commit when
28: * using Mysqli.
29: *
30: * @var boolean
31: * @access public
32: */
33: public $defaultAutoCommit = true;
34:
35: /**
36: * Contains the number of total affected rows by the last multiple statement deploying
37: * method, such as updateMultipleByData and updateMultipleByKey
38: *
39: * @var int
40: */
41: public $multipleAffectedRows;
42:
43: /**
44: * Whether to throw AMysql_Exceptions on mysql errors. If false, trigger_error is called instead.
45: * It is recommended to leave this on TRUE.
46: *
47: * @var boolean
48: * @access public
49: */
50: public $throwExceptions = true;
51:
52: /**
53: * Whether AMysql_Exceptions should trigger errors by default on construction.
54: * This is for legacy behavior (before v1.1.0). It is recommended to keep
55: * is at FALSE.
56: *
57: * @var int
58: * @access public
59: */
60: public $triggerErrorOnException = false;
61:
62: /**
63: * Whether backtraces should be added to each array for getQueriesData(). If true,
64: * backtraces will be found under the 'backtrace' key.
65: *
66: * @var boolean
67: */
68: public $includeBacktrace = false;
69:
70: /**
71: * Amount of seconds needed to pass by to automatically call mysql_ping before
72: * any query. This helps prevent "2006: Server has gone away" errors that may
73: * be caused by mysql queries being performed after other long, blocking requests.
74: *
75: * Mysql pinging isn't guaranteed to automatically reconnect if the connection
76: * actually gets lost, so it is recommended to set the connection details with
77: * setConnDetails even if you are using a preexisting mysql(i) resource.
78: *
79: * Can be set in the connection details array or $this->setAutoPingSeconds()
80: *
81: * @var int|FALSE
82: * @access protected
83: */
84: protected $autoPingSeconds = false;
85:
86: /**
87: * This is set automatically by the script. Do not change this value manually!
88: *
89: * @var mixed
90: * @access public
91: */
92: public $autoPing;
93:
94: /**
95: * If a "2006: Server has gone away" error would occur, attempt to reconnect
96: * once, resending the same query before giving up.
97: *
98: * Mysql pinging isn't guaranteed to automatically reconnect if the connection
99: * actually gets lost, so it is recommended to set the connection details with
100: * setConnDetails even if you are using a preexisting mysql(i) resource.
101: *
102: * Can be set in the config with the "autoReconnect" key, or by using the
103: * $this->setAutoReconnect() method.
104: *
105: * @var false
106: * @access protected
107: */
108: protected $autoReconnect = false;
109:
110: /**
111: * Last time a query has been executed or a mysql connection has been made.
112: * No need to modify externally.
113: *
114: * @var int
115: * @access public
116: */
117: public $lastPingTime;
118:
119: /**
120: * Let AMysql_Statement::bindParam() and AMysql_Statement::bindValue()
121: * use indexes starting from 1 instead of 0 in case of unnamed placeholders.
122: * The same way PDO does it. The factory default is false.
123: **/
124: public $pdoIndexedBinding = false;
125: /**
126: * The fetch mode. Can be changed here or set at runtime with
127: * setFetchMode.
128: *
129: * @var string
130: * @access protected
131: */
132: protected $_fetchMode = self::FETCH_ASSOC;
133:
134: protected $connDetails = array();
135: protected $profiler;
136: protected $inTransaction = false;
137:
138: protected $isAnsi = false;
139: protected $identifierQuoteChar = '`';
140:
141: /**
142: * Whether Mysqli is considered able to use.
143: * Do not change; it is automatically set.
144: * To force a specific mysql driver to be used, use
145: * $this->setConnDetails()
146: * @var boolean
147: */
148: public static $useMysqli;
149:
150: const FETCH_ASSOC = 'assoc';
151: const FETCH_OBJECT = 'object';
152: const FETCH_ARRAY = 'array';
153: const FETCH_ROW = 'row';
154:
155: /**
156: * @constructor
157: * @param resource|mysqli|array|string $resOrArrayOrHost (Optional) Either a valid mysql or mysqli connection
158: * resource/object, or a connection details array
159: * as according to setConnDetails() (doesn't auto connect),
160: * or parameters you would normally pass to the mysql_connect()
161: * function. (auto connects)
162: * (NOTE that this last construction method is
163: * discouraged and may be deprecated and removed in later versions)
164: * (Also note that I mention the arguments mysql_connect(), but only
165: * the arguments. This library will still connect to mysqli if it
166: * is available)
167: *
168: * @see $this->setConnDetails()
169: * @see $this->setConnDetails()
170: *
171: **/
172: public function __construct(
173: $resOrArrayOrHost = null,
174: $username = null,
175: $password = null,
176: $newLink = false,
177: $clientFlags = 0
178: ) {
179: if (!class_exists('AMysql_Statement')) {
180: // Assume no autoloader, and load everything manually
181: $dir = dirname(realpath(__FILE__));
182: require_once $dir . '/Exception.php';
183: require_once $dir . '/Expr.php';
184: require_once $dir . '/Statement.php';
185: require_once $dir . '/Iterator.php';
186: require_once $dir . '/Select.php';
187: require_once $dir . '/Profiler.php';
188: }
189:
190: $this->setAutoPingSeconds($this->autoPingSeconds);
191:
192: // Use mysqli by default if available and PHP is at least of version 5.3.0 (required).
193: // Can be overridden by connection details.
194: self::$useMysqli = class_exists('Mysqli', false) && function_exists('mysqli_stmt_get_result');
195:
196: if (is_resource($resOrArrayOrHost) &&
197: 0 === strpos(get_resource_type($resOrArrayOrHost), 'mysql link')
198: ) {
199: $this->link = $resOrArrayOrHost;
200: $this->isMysqli = false;
201: } elseif ($resOrArrayOrHost instanceof Mysqli) {
202: $this->link = $resOrArrayOrHost;
203: $this->isMysqli = true;
204: } elseif (is_array($resOrArrayOrHost)) {
205: $this->setConnDetails($resOrArrayOrHost);
206: } elseif (is_string($resOrArrayOrHost)) {
207: $this->oldSetConnDetails($resOrArrayOrHost, $username, $password, $newLink, $clientFlags);
208: $this->connect();
209: }
210: }
211:
212: /**
213: * Sets the connection details
214: *
215: * @param array $connDetails An array of details:
216: * host - hostname or ip
217: * username - username
218: * password - password
219: * db - db to auto connect to
220: * port - port
221: * driver - force 'mysql' or 'mysqli'
222: * socket - socket
223: * defaultAutoCommit - @see $this->defaultAutoCommit
224: * autoPingSeconds - @see $this->autoPingSeconds
225: * autoReconnect - @see $this->autoReconnect
226: *
227: *
228: * @access public
229: * @return AMysql_Abstract (chainable)
230: */
231: public function setConnDetails(array $cd)
232: {
233: $defaults = array (
234: 'socket' => ini_get('mysqli.default_socket'),
235: 'db' => null,
236: 'newLink' => false,
237: 'clientFlags' => 0,
238: );
239: $this->connDetails = array_merge($defaults, $cd);
240: if (array_key_exists('autoPingSeconds', $cd)) {
241: $this->setAutoPingSeconds($cd['autoPingSeconds']);
242: }
243: if (array_key_exists('autoReconnect', $cd)) {
244: $this->setAutoReconnect($cd['autoReconnect']);
245: }
246: if (array_key_exists('defaultAutoCommit', $cd)) {
247: $this->setDefaultAutoCommit($cd['defaultAutoCommit']);
248: }
249: return $this;
250: }
251:
252: /**
253: * @see mysql_connect()
254: *
255: * @param string $host (optional)
256: * @param string $username (optional)
257: * @param string $password (optional)
258: * @param boolean $newLink (optional)
259: * @param int $clientFlags (optional)
260: * @access public
261: * @return AMysql_Abstract (chainable)
262: */
263: public function oldSetConnDetails(
264: $host = null,
265: $username = null,
266: $password = null,
267: $newLink = false,
268: $clientFlags = 0
269: ) {
270: $port = null;
271: $cd = array ();
272: if ($host && false !== strpos($host, ':')) {
273: list ($host, $port) = explode(':', $host, 2);
274: }
275: $cd['host'] = $host;
276: $cd['port'] = $port;
277: $cd['username'] = $username;
278: $cd['password'] = $password;
279: $cd['newLink'] = $newLink;
280: $cd['clientFlags'] = $clientFlags;
281: $this->setConnDetails($cd);
282: return $this;
283: }
284:
285: /**
286: * Connects to the database with the configured settings.
287: * Sets $this->link and $this->isMysqli
288: *
289: * @access public
290: * @return AMysql_Abstract (chainable)
291: */
292: public function connect()
293: {
294: $this->error = null;
295: $this->errno = null;
296:
297: $cd = $this->connDetails;
298: if (!$cd) {
299: throw new LogicException("No connection details set. Could not connect.");
300: }
301: if (isset($cd['driver'])) {
302: switch ($cd['driver']) {
303: case 'mysqli':
304: $isMysqli = true;
305: break;
306: case 'mysql':
307: $isMysqli = false;
308: break;
309: default:
310: throw new LogicException ("Unknown driver: `$cd[driver]`");
311: break;
312: }
313: } else {
314: $isMysqli = self::$useMysqli;
315: }
316: $this->isMysqli = $isMysqli;
317: $newLink = !empty($cd['newLink']);
318: $res = null;
319: if (isset($cd['host'], $cd['username'], $cd['password'])) {
320: if ($isMysqli) {
321: $port = isset($cd['port']) ? $cd['port'] : ini_get('mysqli.default_port');
322: $res = mysqli_connect(
323: $cd['host'],
324: $cd['username'],
325: $cd['password'],
326: $cd['db'],
327: $port,
328: $cd['socket']
329: );
330: } else {
331: $host = isset($cd['port']) ? "$cd[host]:$cd[port]" : $cd['host'];
332: $res = mysql_connect(
333: $host,
334: $cd['username'],
335: $cd['password'],
336: $newLink,
337: $cd['clientFlags']
338: );
339: }
340: }
341: if ($res) {
342: $this->lastPingTime = time(); // otherwise can cause infinite recursion.
343: $this->link = $res;
344:
345: if (!$isMysqli && !empty($cd['db'])) {
346: $this->selectDb($cd['db']);
347: }
348: } else {
349: if ($this->isMysqli) {
350: $this->handleError(
351: mysqli_connect_error(),
352: mysqli_connect_errno(),
353: '(connection to mysql)'
354: );
355: } else {
356: $this->handleError(
357: mysql_error(),
358: mysql_errno(),
359: '(connection to mysql)'
360: );
361: }
362: }
363: return $this;
364: }
365:
366: /**
367: * Closes the connection and unsets its link.
368: *
369: * @access public
370: * @return AMysql_Abstract (chainable)
371: */
372: public function close()
373: {
374: if ($this->link instanceof Mysqli) {
375: $this->link->close();
376: }
377: else if (is_resource($this->link)) {
378: mysql_close($this->link);
379: }
380: $this->link = null;
381: return $this;
382: }
383:
384: /**
385: * Returns the DB connection link. Connects to the DB if not connected.
386: * Does not check if the connection has since been broken.
387: *
388: * @access public
389: * @return resource|mysqli Connection resource or object
390: */
391: public function autoConnect()
392: {
393: if (!$this->link) {
394: $this->connect();
395: }
396: return $this->link;
397: }
398:
399: /**
400: * Reconnects to the database with the last saved connection details.
401: *
402: * @access public
403: * @return void
404: */
405: public function forceReconnect()
406: {
407: $oldConnDetails = (array) $this->connDetails;
408: $this->connDetails['newLink'] = true;
409: $this->connect();
410: $this->connDetails = $oldConnDetails;
411: }
412:
413: /**
414: * Sets the amount of seconds needed to pass - since last communicating
415: * with the database - before communicating again, to ping the
416: * database server beforehand and also reconnect if the connection has
417: * been since lost.
418: *
419: * @param int|FALSE $autoPingSeconds The amount of seconds, or
420: * FALSE if it should be disabled
421: * @access public
422: * @return AMysql_Statement (chainable)
423: */
424: public function setAutoPingSeconds($autoPingSeconds)
425: {
426: $this->autoPingSeconds = $autoPingSeconds;
427: $this->autoPing = is_numeric($this->autoPingSeconds);
428: return $this;
429: }
430:
431: /**
432: * @see $this->autoReconnect
433: *
434: * @param boolean
435: * @access public
436: * @return AMysql_Abstract (chainable)
437: */
438: public function setAutoReconnect($autoReconnect)
439: {
440: $this->autoReconnect = $autoReconnect == true;
441: return $this;
442: }
443:
444: /**
445: * getAutoReconnect
446: *
447: * @access public
448: * @return boolean
449: */
450: public function getAutoReconnect()
451: {
452: return $this->autoReconnect;
453: }
454:
455: /**
456: * @see $this->defaultAutoCommit
457: *
458: * @param boolean
459: * @access public
460: * @return AMysql_Abstract (chainable)
461: */
462: public function setDefaultAutoCommit($defaultAutoCommit)
463: {
464: $this->defaultAutoCommit = $defaultAutoCommit == true;
465: return $this;
466: }
467:
468: /**
469: * Returns the default fetch mode. See the FETCH_ class constants.
470: *
471: * @access public
472: * @return string
473: */
474: public function getFetchMode()
475: {
476: return $this->_fetchMode;
477: }
478:
479: /**
480: * Selects the given database.
481: *
482: * @param string $db
483: * @return AMysql_Abstract (chainable)
484: */
485: public function selectDb($db)
486: {
487: $this->connDetails['db'] = $db; // for reconnecting later
488: $isMysqli = $this->isMysqli;
489: $link = $this->autoPingConnect();
490: $result = $isMysqli ? $link->select_db($db) : mysql_select_db($db, $link);
491: if (!$result) {
492: $error = $isMysqli ? $link->error : mysql_error($link);
493: $errno = $isMysqli ? $link->errno : mysql_errno($link);
494: $this->handleError($error, $errno, 'USE ' . $db);
495: }
496: return $this;
497: }
498:
499: /**
500: * Takes care of setting everything to utf-8
501: *
502: * @access public
503: * @return AMysql_Abstract (chainable)
504: */
505: public function setUtf8()
506: {
507: return $this->setCharset('utf8')->setNames('utf8');
508: }
509:
510: /**
511: * Changes the character set of the connection.
512: *
513: * @param string $charset Example: utf8
514: * @return AMysql_Abstract (chainable)
515: */
516: public function setCharset($charset)
517: {
518: $isMysqli = $this->isMysqli;
519: $this->autoPing();
520: if ($isMysqli) {
521: $result = $this->link->set_charset($charset);
522: } else {
523: if (!function_exists('mysql_set_charset')) {
524: function mysql_set_charset($charset, $link = null)
525: {
526: return mysql_query("SET CHARACTER SET '$charset'", $link);
527: }
528: }
529: $result = mysql_set_charset($charset, $this->link);
530: }
531: if (!$result) {
532: $error = $isMysqli ? $this->link->error : mysql_error($this->link);
533: $errno = $isMysqli ? $this->link->errno : mysql_errno($this->link);
534: $this->handleError($error, $errno, "(setting charset)");
535: }
536: return $this;
537: }
538:
539: /**
540: * Performs SET NAMES <charset> to change the character set. It may be
541: * enough to use $this->setCharset().
542: *
543: * @param string $names Example: utf8
544: * @return AMysql_Abstract (chainable)
545: */
546: public function setNames($names)
547: {
548: $stmt = $this->query("SET NAMES $names");
549: return $this;
550: }
551:
552: /**
553: * Sets whether ANSI mode is on. Does not change the database's mode,
554: * you just have to call this method when you know ANSI mode is being
555: * used in the mysql connection, so that escaping is done according
556: * to the correct mode.
557: * ANSI mode is off my default on most mysql installations.
558: *
559: * @param boolean $isAnsi
560: * @access public
561: * @return AMysql_Abstract (chainable)
562: */
563: public function setAnsi($isAnsi)
564: {
565: $this->isAnsi = $isAnsi;
566: $this->identifierQuoteChar = $isAnsi ? '"' : '`';
567: return $this;
568: }
569:
570: /**
571: * Returns whether ANSI mode is being used by this instance (not the database)
572: *
573: * @return boolean
574: **/
575: public function isAnsi()
576: {
577: return $this->isAnsi;
578: }
579:
580: /**
581: * Does a simple identifier escape. It should be fail proof for that literal identifier.
582: *
583: * @param string $identifier The identifier
584: * @param string $qc The quote character. Default: `
585: *
586: * @return string The escaped identifier.
587: **/
588: public static function escapeIdentifierSimple($identifier, $qc = '`')
589: {
590: return $qc . addcslashes($identifier, "$qc\\") . $qc;
591: }
592:
593: /**
594: * Escapes an identifier. If there's a dot in it, it is split
595: * into two identifiers, each escaped, and joined with a dot.
596: *
597: * @param string $identifier The identifier
598: * @param string $qc The quote character. Default: `
599: *
600: * @return string The escaped identifier.
601: **/
602: protected static function _escapeIdentifier($identifier, $qc)
603: {
604: $exploded = explode('.', $identifier);
605: $count = count($exploded);
606: $identifier = $exploded[$count-1] == '*' ?
607: '*' :
608: self::escapeIdentifierSimple($exploded[$count-1], $qc);
609: if (1 < $count) {
610: $identifier = "$qc$exploded[0]$qc.$identifier";
611: }
612: $ret = $identifier;
613: return $ret;
614: }
615:
616: /**
617: * Escapes an identifier, such as a column or table name.
618: * Includes functionality for making an AS syntax.
619: *
620: * @deprecated Do not rely on this static method. Its public visibility or name may be changed
621: * in the future. Use the non-static escapeTable() method instead.
622: *
623: * @param string $identifierName The identifier name. If it has a dot in
624: * it, it'll automatically split the
625: * identifier name into the
626: * `tableName`.`columnName` syntax.
627: *
628: * @param string $as (Optional) adds an AS syntax, but only
629: * if it's a string. The value is the alias
630: * the identifier should have for the query.
631: *
632: * @param $qc The quote character. Default: `
633: *
634: * @todo Possibly change the functionality to remove the automatic dot
635: * detection,
636: * and ask for an array instead?
637: *
638: * e.g.
639: * echo $amysql->escapeIdentifier('table.order', 'ot');
640: * // `table`.`order` AS ot
641: **/
642: public static function escapeIdentifier($identifierName, $as = null, $qc = '`')
643: {
644: $asString = '';
645: $escapeIdentifierName = true;
646: if ($as and !is_numeric($as)) {
647: $asString = ' AS ' . $as;
648: } elseif (is_string($identifierName) and (false !==
649: strpos($identifierName, ' AS '))
650: ) {
651: $exploded = explode(' AS ', $identifierName);
652: $identifierName = $exploded[0];
653: $asString = ' AS ' . $exploded[1];
654: }
655: if ($identifierName instanceof AMysql_Expr) {
656: $ret = $identifierName->__toString() . $asString;
657: } else {
658: $ret = self::_escapeIdentifier($identifierName, $qc) . $asString;
659: }
660: return $ret;
661: }
662:
663: /**
664: * Escapes a table name.
665: *
666: * @param string $identifierName The identifier name. If it has a dot in
667: * it, it'll automatically split the
668: * identifier name into the
669: * `tableName`.`columnName` syntax.
670: *
671: * @param string $as (Optional) adds an AS syntax, but only
672: * if it's a string. The value is the alias
673: * the identifier should have for the query.
674: *
675: * @return string
676: */
677: public function escapeTable($tableName, $as = null)
678: {
679: return self::escapeIdentifier($tableName, $as, $this->identifierQuoteChar);
680: }
681:
682: /**
683: * Escapes a column name.
684: *
685: * @param string $identifierName The identifier name. If it has a dot in
686: * it, it'll automatically split the
687: * identifier name into the
688: * `tableName`.`columnName` syntax.
689: *
690: * @param string $as (Optional) adds an AS syntax, but only
691: * if it's a string. The value is the alias
692: * the identifier should have for the query.
693: *
694: * @return string
695: */
696: public function escapeColumn($columnName, $as = null)
697: {
698: return $this->escapeTable($columnName, $as, $this->identifierQuoteChar);
699: }
700:
701: /**
702: * Returns whether a MySQL TRANSACTION is in progress,
703: * based off method calls.
704: *
705: * @access public
706: * @return boolean
707: */
708: public function inTransaction()
709: {
710: return $this->inTransaction;
711: }
712:
713: /**
714: * Performs a mysql ROLLBACK.
715: *
716: * @todo checks
717: * @return mixed Do not rely on this return value; it may change in
718: * the future.
719: **/
720: public function startTransaction()
721: {
722: $link = $this->autoConnect();
723: if ($link instanceof Mysqli) {
724: $link->autocommit(false);
725: $ret = true;
726: } else {
727: $ret = $this->query('START TRANSACTION');
728: }
729: $this->inTransaction = true;
730: return $ret;
731: }
732:
733: /**
734: * Performs a mysql COMMIT.
735: *
736: * @todo checks
737: * @return mixed Do not rely on this return value; it may change in
738: * the future.
739: **/
740: public function commit()
741: {
742: $link = $this->autoConnect();
743: $ret = $this->query('COMMIT');
744: if ($link instanceof Mysqli) {
745: $link->autocommit($this->defaultAutoCommit);
746: }
747:
748: $this->inTransaction = false;
749: return $ret;
750: }
751:
752: /**
753: * Performs a mysql ROLLBACK.
754: *
755: * @todo checks
756: * @return mixed Do not rely on this return value; it may change in
757: * the future.
758: **/
759: public function rollback()
760: {
761: $link = $this->autoConnect();
762: $ret = $this->query('ROLLBACK');
763: if ($link instanceof Mysqli) {
764: $link->autocommit($this->defaultAutoCommit);
765: }
766:
767: $this->inTransaction = false;
768: return $ret;
769: }
770:
771: /**
772: * Executes a query by an sql string and binds.
773: *
774: * @param string $sql The SQL string.
775: * @param mixed $binds The binds or a single bind.
776: *
777: * @return AMysql_Statement
778: **/
779: public function query($sql, $binds = array ())
780: {
781: $stmt = new AMysql_Statement($this);
782: $result = $stmt->query($sql, $binds);
783: return $stmt;
784: }
785:
786: /**
787: * Executes a query, and returns the first found row's first column's value.
788: * Throws a warning if no rows were found.
789: *
790: * @param string $sql The SQL string.
791: * @param mixed $binds The binds or a single bind.
792: *
793: * @return string|int
794: **/
795: public function getOne($sql, $binds = array ())
796: {
797: $stmt = new AMysql_Statement($this);
798: $stmt->query($sql, $binds);
799: return $stmt->result(0, 0);
800: }
801:
802: /**
803: * Like $this->getOne(), except returns a null when no result is found,
804: * without throwing an error.
805: *
806: * @param string $sql The SQL string.
807: * @param mixed $binds The binds or a single bind.
808: *
809: * @see AMysql_Abstract::getOne()
810: *
811: * @return string|int|NULL
812: **/
813: public function getOneNull($sql, $binds = array ())
814: {
815: $stmt = new AMysql_Statement($this);
816: $stmt->query($sql, $binds);
817: return $stmt->resultNull(0, 0);
818: }
819:
820: /**
821: * Like $this->getOne(), but casts the result to an int. No exception is
822: * thrown when there is no result.
823: *
824: * @param string $sql The SQL string.
825: * @param mixed $binds The binds or a single bind.
826: *
827: * @see AMysql_Abstract::getOne()
828: *
829: * @return int
830: **/
831: public function getOneInt($sql, $binds = array ())
832: {
833: $stmt = new AMysql_Statement($this);
834: $stmt->query($sql, $binds);
835: return $stmt->resultInt(0, 0);
836: }
837:
838: /**
839: * Prepares a mysql statement. It is to be executed.
840: *
841: * @param string $sql The unbound SQL string.
842: *
843: * @return AMysql_Statement A new statement instance.
844: **/
845: public function prepare($sql)
846: {
847: $stmt = new AMysql_Statement($this);
848: $stmt->prepare($sql);
849: return $stmt;
850: }
851:
852: /**
853: * Returns a new instance of AMysql_Select.
854: * By passing parameters, you can now have $select->column() invoked
855: * straight away (e.g. $amysql->select('*')->...)
856: *
857: * @param mixed $columns (Optional)
858: *
859: * @see AMysql_Select::column()
860: *
861: * @return AMysql_Select A new instance of this class
862: **/
863: public function select($columns = null)
864: {
865: $select = new AMysql_Select($this);
866: if ($columns) {
867: $select->column($columns);
868: }
869: return $select;
870: }
871:
872: /**
873: * Creates a new AMysql_Statement instance and returns it.
874: *
875: * @return AMysql_Statement
876: **/
877: public function newStatement()
878: {
879: return new AMysql_Statement($this);
880: }
881:
882: /**
883: * Performs an instant UPDATE returning the statement.
884: *
885: * @param string $tableName The table name.
886: * @param array $data The array of data changes. A
887: * one-dimensional array
888: * with keys as column names and values
889: * as their values.
890: * @param string $where An SQL substring of the WHERE clause.
891: * @param mixed $binds (Optional) The binds or a single bind for the WHERE clause.
892: *
893: * @return AMysql_Statement
894: **/
895: public function upd($tableName, array $data, $where, $binds = array())
896: {
897: $stmt = new AMysql_Statement($this);
898: $stmt->update($tableName, $data, $where);
899: $stmt->execute($binds);
900: return $stmt;
901: }
902:
903: /**
904: * Performs an instant UPDATE returning its success.
905: *
906: * @param string $tableName The table name.
907: * @param array $data The array of data changes. A
908: * one-dimensional array
909: * with keys as column names and values
910: * as their values.
911: * @param string $where An SQL substring of the WHERE clause.
912: * @param mixed $binds (Optional) The binds or a single bind for the WHERE clause.
913: *
914: * @return boolean Whether the update was successful.
915: **/
916: public function update($tableName, array $data, $where, $binds = array())
917: {
918: return $this->upd($tableName, $data, $where, $binds)->result;
919: }
920:
921: /**
922: * Updates multiple rows.
923: * The number of total affected rows can be found in
924: * $this->multipleAffectedRows.
925: *
926: * @param string $tableName The table name.
927: * @param array[] $data The array of data changes. An array of
928: * an array of column-value pairs to update
929: * for that row.
930: * One of the column keys should be the one
931: * with the value to search for for updating
932: * (typically the primary key)
933: * e.g.
934: * [
935: * [
936: * 'id' => 1,
937: * 'col1' => 'newStringValue',
938: * 'col2' => 'newStringValue2'
939: * ],
940: * [
941: * 'id' => 2,
942: * 'col1' => 'anotherNewStringValue',
943: * 'col2' => 'anotherNewStringValue2'
944: * ],
945: * ...
946: * ]
947: * @param string $column (Options) the name of the column to search
948: * for, and key to use among the data.
949: * The default is 'id'.
950: * @return boolean Whether there was an equal amount of
951: * successful updates (whether a row was
952: * affected or not) as the size of the
953: * inputted data array.
954: **/
955: public function updateMultipleByData(
956: $tableName,
957: array $data,
958: $column = 'id'
959: ) {
960: $successesNeeded = count($data);
961: $where = self::escapeIdentifier($column) . " = ?";
962: $affectedRows = 0;
963: foreach ($data as $row) {
964: $by = $row[$column];
965: unset($row[$column]);
966: $stmt = new AMysql_Statement($this);
967: $stmt->update($tableName, $row, $where)->execute(array ($by));
968: $affectedRows += $stmt->affectedRows;
969: if ($stmt->result) {
970: $successesNeeded--;
971: }
972: }
973: $this->multipleAffectedRows = $affectedRows;
974: return 0 === $successesNeeded;
975: }
976:
977: /**
978: * Updates multiple rows. The values for the column to search for is the
979: * key of each row.
980: * The number of total affected rows can be found in
981: * $this->multipleAffectedRows.
982: *
983: * @param string $tableName The table name.
984: * @param array[] $data The array of data changes. An array
985: * indexed by the value of the column to
986: * apply the update to (typically the primary)
987: * key containing
988: * an array of column-value pairs to update
989: * for that row.
990: * e.g.
991: * [
992: * 1 => [
993: * 'col1' => 'newStringValue',
994: * 'col2' => 'newStringValue2'
995: * ],
996: * 2 => [
997: * 'id' => 2,
998: * 'col1' => 'anotherNewStringValue',
999: * 'col2' => 'anotherNewStringValue2'
1000: * ],
1001: * ...
1002: * ]
1003: * @param string $column (Options) the name of the column and
1004: * key to search for. The default is
1005: * 'id'.
1006: * @param boolean $updateSameColumn (Optional) If the column being searched
1007: * for is within the a data row,
1008: * if this is false, that key should
1009: * be removed before updating the data.
1010: * This is the default.
1011: *
1012: * @return boolean
1013: **/
1014: public function updateMultipleByKey(
1015: $tableName,
1016: array $data,
1017: $column = 'id',
1018: $updateSameColumn = false
1019: ) {
1020: $successesNeeded = count($data);
1021: $where = $this->escapeColumn($column) . " = ?";
1022: $affectedRows = 0;
1023: foreach ($data as $by => $row) {
1024: if (!$updateSameColumn) {
1025: unset($row[$column]);
1026: }
1027: $stmt = new AMysql_Statement($this);
1028: $stmt->update($tableName, $row, $where)->execute(array ($by));
1029: $affectedRows += $stmt->affectedRows;
1030: if ($stmt->result) {
1031: $successesNeeded--;
1032: }
1033: }
1034: $this->multipleAffectedRows = $affectedRows;
1035: return 0 === $successesNeeded;
1036: }
1037:
1038: /**
1039: * Performs an instant INSERT, returning the statement.
1040: *
1041: * @param string $tableName The table name.
1042: * @param array $data A one or two-dimensional array.
1043: * 1D:
1044: * an associative array of keys as column names and values
1045: * as their values. This inserts one row.
1046: * 2D numeric:
1047: * A numeric array where each value is an associative array
1048: * with column-value pairs. Each outer, numeric value represents
1049: * a row of data.
1050: * 2D associative:
1051: * An associative array where the keys are the columns, the
1052: * values are numerical arrays, where each value represents the
1053: * value for the new row of that key.
1054: *
1055: * @return AMysql_Statement
1056: **/
1057: public function ins($tableName, array $data)
1058: {
1059: $stmt = new AMysql_Statement($this);
1060: $stmt->insert($tableName, $data);
1061: $stmt->execute();
1062: return $stmt;
1063: }
1064:
1065: /**
1066: * Performs an instant INSERT, but tries to return the last insert
1067: * id straight away.
1068: *
1069: * @param string $tableName The table name.
1070: * @param array $data A one or two-dimensional array.
1071: * 1D:
1072: * an associative array of keys as column names and values
1073: * as their values. This inserts one row.
1074: * 2D numeric:
1075: * A numeric array where each value is an associative array
1076: * with column-value pairs. Each outer, numeric value represents
1077: * a row of data.
1078: * 2D associative:
1079: * An associative array where the keys are the columns, the
1080: * values are numerical arrays, where each value represents the
1081: * value for the new row of that key.
1082: * @access public
1083: *
1084: * @return int|boolean The mysql_insert_id(), if the query
1085: * succeeded and there exists a primary key.
1086: * Otherwise the boolean of whether the insert
1087: * was successful.
1088: */
1089: public function insert($tableName, array $data)
1090: {
1091: $stmt = $this->ins($tableName, $data);
1092: $success = $stmt->result;
1093: if ($success) {
1094: return $stmt->insertId ? $stmt->insertId : true;
1095: } else {
1096: return false;
1097: }
1098: }
1099:
1100: /**
1101: * Performs an instant REPLACE, returning the statement.
1102: *
1103: * @param string $tableName The table name.
1104: * @param array $data A one or two-dimensional array.
1105: * 1D:
1106: * an associative array of keys as column names and values
1107: * as their values. This inserts one row.
1108: * 2D numeric:
1109: * A numeric array where each value is an associative array
1110: * with column-value pairs. Each outer, numeric value represents
1111: * a row of data.
1112: * 2D associative:
1113: * An associative array where the keys are the columns, the
1114: * values are numerical arrays, where each value represents the
1115: * value for the new row of that key.
1116: *
1117: * @return AMysql_Statement
1118: **/
1119: public function rep($tableName, array $data)
1120: {
1121: $stmt = new AMysql_Statement($this);
1122: $stmt->replace($tableName, $data);
1123: $stmt->execute();
1124: return $stmt;
1125: }
1126:
1127: /**
1128: * Performs an instant REPLACE, returning its success.
1129: *
1130: * @param string $tableName The table name.
1131: * @param array $data A one or two-dimensional array.
1132: * 1D:
1133: * an associative array of keys as column
1134: * names and values
1135: * as their values. This inserts one row.
1136: * 2D numeric:
1137: * A numeric array where each value is an
1138: * associative array
1139: * with column-value pairs. Each outer,
1140: * numeric value represents
1141: * a row of data.
1142: * 2D associative:
1143: * An associative array where the keys are the
1144: * columns, the
1145: * values are numerical arrays, where each
1146: * value represents the
1147: * value for the new row of that key.
1148: *
1149: * @return boolean Success.
1150: **/
1151: public function replace($tableName, array $data)
1152: {
1153: $success = $this->rep($tableName, $data)->result;
1154: return $success;
1155: }
1156:
1157: /**
1158: * Performs an INSERT or an UPDATE; if the $value parameter is not falsy,
1159: * an UPDATE is performed with the given column name and value, otherwise
1160: * an insert. It is recommended that this is used for tables with a primary
1161: * key, and use the primary key as the column to look at. Also, this would
1162: * keep the return value consistent.
1163: *
1164: * @param mixed $tableName The table name to INSERT or UPDATE to
1165: * @param mixed $data The data to change
1166: * @param mixed $columnName The column to search by. It should be
1167: * a primary key.
1168: * @param mixed $value (Optional) The value to look for in
1169: * case you want
1170: * to UPDATE. Keep this at null, 0,
1171: * or anything else falsy for INSERT.
1172: *
1173: * @return int If the $value is not falsy, it returns
1174: * $value after UPDATING. Otherwise the
1175: * mysql_insert_id() of the newly
1176: * INSERTED row.
1177: */
1178: public function save($tableName, $data, $columnName, $value = null)
1179: {
1180: if ($value) {
1181: $where = AMysql_Abstract::escapeIdentifier($columnName) . ' = ?';
1182: $this->update($tableName, $data, $where, array ($value));
1183: return $value;
1184: } else {
1185: $id = $this->insert($tableName, $data);
1186: return $id;
1187: }
1188: }
1189:
1190: /**
1191: * Performs an instant DELETE, returning the statement.
1192: *
1193: * @param string $tableName The table name.
1194: * @param string $where An SQL substring of the WHERE clause.
1195: * @param mixed $binds (Optional) The binds or a single bind for
1196: * the WHERE clause.
1197: *
1198: * @return AMysql_Statement
1199: **/
1200: public function del($tableName, $where, $binds = array ())
1201: {
1202: $stmt = new AMysql_Statement($this);
1203: $stmt->delete($tableName, $where);
1204: $stmt->execute($binds);
1205: return $stmt;
1206: }
1207:
1208: /**
1209: * Performs an instant DELETE, returning whether it succeeded.
1210: *
1211: * @param string $tableName The table name.
1212: * @param string $where An SQL substring of the WHERE clause.
1213: * @param mixed $binds (Optional) The binds or a single bind for
1214: * the WHERE clause.
1215: *
1216: * @return resource|FALSE The mysql resource if the delete was
1217: * successful, otherwise false.
1218: **/
1219: public function delete($tableName, $where, $binds = array ())
1220: {
1221: return $this->del($tableName, $where, $binds)->result;
1222: }
1223:
1224: /**
1225: * If the last mysql query was a SELECT with the SQL_CALC_FOUND_ROWS
1226: * options, this returns the number of found rows from that last
1227: * query with LIMIT and OFFSET ignored.
1228: *
1229: * @access public
1230: * @return int Number of found rows.
1231: */
1232: public function foundRows()
1233: {
1234: $stmt = new AMysql_Statement($this);
1235: $sql = 'SELECT FOUND_ROWS()';
1236: $stmt->query($sql);
1237: return $stmt->resultInt();
1238: }
1239:
1240: /**
1241: * Returns an AMysql_Expr for using in prepared statements as values.
1242: * See the AMysql_Expr class for details.
1243: * To bind a literal value without apostrophes, here is an example of
1244: * how you can execute a prepared statement with the help of placeholders:
1245: * $amysql->prepare('SELECT ? AS time')->execute(array (
1246: * $amysql->expr('CURRENT_TIMESTAMP')
1247: * ))
1248: *
1249: * @see AMysql_Expr
1250: * @param ...$variadic see AMysql_Expr
1251: *
1252: * @return AMysql_Expr
1253: **/
1254: public function expr(/* args */)
1255: {
1256: $args = func_get_args();
1257: $expr = new AMysql_Expr($this);
1258: call_user_func_array(array ($expr, 'set'), $args);
1259: return $expr;
1260: }
1261:
1262: /**
1263: * Escapes LIKE. The after the LIKE <string> syntax, you must place
1264: * an ESCAPE statement with '=' or whatever you pass here as
1265: * $escapeStr
1266: * It is not recommended to call this static method externally;
1267: * please use {@link AMysql_Expr} instead.
1268: *
1269: * @param string $s The string to LIKE escape
1270: * @param string $escapeChar (Optional) The escape character
1271: **/
1272: public static function escapeLike($s, $escapeStr = '=')
1273: {
1274: return str_replace(
1275: array($escapeStr, '_', '%'),
1276: array($escapeStr.$escapeStr, $escapeStr.'_', $escapeStr.'%'),
1277: $s
1278: );
1279: }
1280:
1281: /**
1282: * Escapes a value. The method depends on the passed value's type, but
1283: * unless the passed type is an AMysql_Expr, the safety is almost
1284: * guaranteed. Do not put apostrophes around bind marks! Those are handled
1285: * by this escaping method.
1286: *
1287: * @param mixed The value to escape
1288: *
1289: * @return string|int The value safe to put into a query,
1290: * including surrounding apostrophes if
1291: * the value is string-like, not including
1292: * apostrophes if it's an int or an
1293: * {@link AMysql_Expr} or
1294: * {@link AMysql_Select}
1295: * (for selectception)
1296: *
1297: **/
1298: public function escape($value)
1299: {
1300: $res = $this->autoConnect();
1301: $isValidLink =
1302: $res instanceof Mysqli ||
1303: 0 === strpos(get_resource_type($res), 'mysql link');
1304: if (!$isValidLink) {
1305: throw new RuntimeException('Resource is not a mysql resource.', 0);
1306: }
1307: $isMysqli = $this->isMysqli;
1308: // If it's an int, place it there literally
1309: if (is_int($value)) {
1310: return $value;
1311: }
1312: // If it's a NULL, use the literal string, NULL
1313: if (null === $value) {
1314: return 'NULL';
1315: }
1316: // Literal TRUE or FALSE in case of a boolean
1317: if (is_bool($value)) {
1318: return $value ? 'TRUE' : 'FALSE';
1319: }
1320: // for selectception ;)
1321: if ($value instanceof AMysql_Statement) {
1322: return $value->getSql();
1323: }
1324: // In case of an AMysql_Expr, use its __toString() methods return value.
1325: if ($value instanceof AMysql_Expr) {
1326: return $value->__toString();
1327: }
1328: // In the case of a string or anything else, let's escape it and
1329: // put it between apostrophes.
1330: return
1331: "'" .
1332: ($isMysqli ?
1333: $this->link->real_escape_string($value) :
1334: mysql_real_escape_string($value, $res))
1335: .
1336: "'"
1337: ;
1338: }
1339:
1340: /**
1341: * Transposes a 2 dimensional array.
1342: * Every inner array must contain the same keys as the other inner arrays,
1343: * otherwise unexpected results may occur.
1344: *
1345: * Example:
1346: * $input = array (
1347: * 3 => array (
1348: * 'col1' => 'bla',
1349: * 'col2' => 'yo'
1350: * ),
1351: * 9 => array (
1352: * 'col1' => 'ney',
1353: * 'col2' => 'lol'
1354: * )
1355: * );
1356: * $output = $amysql->transpose($input);
1357: *
1358: * $output: array (
1359: * 'col1' => array (
1360: * 3 => 'bla',
1361: * 9 => 'ney'
1362: * ),
1363: * 'col2' => array (
1364: * 3 => 'yo',
1365: * 9 => 'lol'
1366: * )
1367: * );
1368: *
1369: * @param array $array The 2 dimensional array to transpose
1370: * @return array
1371: */
1372: public static function transpose(array $array)
1373: {
1374: $ret = array ();
1375: if (!$array) {
1376: return $ret;
1377: }
1378: foreach ($array as $key1 => $arraySub) {
1379: if (!$ret) {
1380: foreach ($arraySub as $key2 => $value) {
1381: $ret[$key2] = array ($key1 => $value);
1382: }
1383: } else {
1384: foreach ($arraySub as $key2 => $value) {
1385: $ret[$key2][$key1] = $value;
1386: }
1387: }
1388: }
1389: return $ret;
1390: }
1391:
1392: /**
1393: * Adds a query and a profile for it to the list of queries.
1394: * Used by AMysql_Statement. Do not call externally!
1395: *
1396: * @param string $query The SQL query.
1397: * @param float $queryTime The time the query took.
1398: * @access public
1399: * @return AMysql_Abstract (chainable)
1400: */
1401: public function addQuery($query, $queryTime)
1402: {
1403: $this->getProfiler()->addQuery($query, $queryTime);
1404: return $this;
1405: }
1406:
1407: /**
1408: * Pings the mysql server to see if it's still alive;
1409: * attempts to reconnect otherwise.
1410: *
1411: * @param boolean $handleError (Optional) If TRUE, on failure, this will
1412: * be handled as any other AMysql error.
1413: * Defaults to FALSE (no errors or
1414: * exceptions).
1415: * @access public
1416: * @return boolean TRUE if the connection is still there
1417: * or reconnection was successful.
1418: * FALSE if reconnection wasn't successful.
1419: */
1420: public function pingReconnect()
1421: {
1422: $isMysqli = $this->isMysqli;
1423: if ($isMysqli) {
1424: $success = @mysqli_ping($this->link);
1425: } else {
1426: $success = @mysql_ping($this->link);
1427: }
1428: if (!$success) {
1429: try {
1430: $this->forceReconnect();
1431: if (!$this->error) {
1432: $success = true;
1433: }
1434: } catch (Exception $e) {
1435: // do nothing
1436: }
1437: }
1438: return $success;
1439: }
1440:
1441: /**
1442: * Pings if the set amount of auto ping time has passed.
1443: *
1444: * @access public
1445: * @return AMysql_Abstract (chainable)
1446: */
1447: public function autoPing()
1448: {
1449: if ($this->autoPing) {
1450: if (!$this->link) {
1451: $this->connect();
1452: } elseif (
1453: !$this->lastPingTime ||
1454: $this->autoPingSeconds <= (time() - $this->lastPingTime)
1455: ) {
1456: $this->pingReconnect();
1457: $this->lastPingTime = time();
1458: }
1459: }
1460: return $this;
1461: }
1462:
1463: /**
1464: * Auto connects and auto pings (if on), returning the mysql link.
1465: *
1466: * @access public
1467: * @return Mysqli|resource
1468: */
1469: public function autoPingConnect()
1470: {
1471: $link = $this->autoConnect();
1472: $this->autoPing();
1473: return $link;
1474: }
1475:
1476: /**
1477: * Gets the list of SQL queries performed so far by AMysql_Statement
1478: * objects connected by this object.
1479: *
1480: * @access public
1481: * @return array
1482: */
1483: public function getQueries()
1484: {
1485: return $this->getProfiler()->getQueries();
1486: }
1487:
1488: /**
1489: * Returns an arrays of profiled query data. Each value is an array that
1490: * consists of:
1491: * - query - The SQL query performed
1492: * - time - The amount of seconds the query took (float)
1493: *
1494: * If profileQueries wss off at any query, its time value will be null.
1495: *
1496: * @return array[]
1497: */
1498: public function getQueriesData()
1499: {
1500: return $this->getProfiler()->getQueriesData();
1501: }
1502:
1503: /**
1504: * Returns the profiler object. Pass it to your templates
1505: * to retrieve the profiler results.
1506: *
1507: * @access public
1508: * @return AMysql_Profiler
1509: */
1510: public function getProfiler()
1511: {
1512: if (!$this->profiler) {
1513: $this->profiler = new AMysql_Profiler($this);
1514: }
1515: return $this->profiler;
1516: }
1517:
1518: /**
1519: * Force using a new profiler.
1520: *
1521: * @access public
1522: * @return AMysql_Abstract (chainable)
1523: */
1524: public function useNewProfiler()
1525: {
1526: $this->profiler = new AMysql_Profiler($this);
1527: return $this;
1528: }
1529:
1530: /**
1531: * For internal use.
1532: *
1533: * @param mixed $msg
1534: * @param mixed $code
1535: * @param mixed $query
1536: * @access public
1537: * @throws AMysql_Exception
1538: * @return void
1539: */
1540: public function handleError($msg, $code, $query)
1541: {
1542: $this->error = $msg;
1543: $this->errno = $code;
1544: $ex = new AMysql_Exception($msg, $code, $query);
1545: if ($this->triggerErrorOnException) {
1546: $ex->triggerErrorOnce();
1547: }
1548: if ($this->throwExceptions) {
1549: throw $ex;
1550: }
1551: $ex->triggerErrorOnce();
1552: }
1553:
1554: /**
1555: * For internal use.
1556: *
1557: * @param mixed $msg
1558: * @param mixed $code
1559: * @param mixed $query
1560: * @access public
1561: * @throws AMysql_Exception
1562: * @return void
1563: */
1564: public function handleException(AMysql_Exception $ex)
1565: {
1566: $this->error = $ex->getMessage();
1567: $this->errno = $ex->getCode();
1568: if ($this->triggerErrorOnException) {
1569: $ex->triggerErrorOnce();
1570: }
1571: if ($this->throwExceptions) {
1572: throw $ex;
1573: }
1574: $ex->triggerErrorOnce();
1575: }
1576:
1577: public function __get($name)
1578: {
1579: switch ($name) {
1580: case 'totalTime':
1581: $profiler = $this->getProfiler();
1582: return $profiler->totalTime;
1583: }
1584: trigger_error("Undefined property: `$name`");
1585: }
1586: }
1587: