/packages/Db/Db/Managers/MySqlQuery.class.php
PHP | 857 lines | 562 code | 100 blank | 195 comment | 152 complexity | 4d4e9a6d5286703d78a1a8e8e3b2aeb5 MD5 | raw file
1<?php
2
3class MySqlQuery extends Model {
4
5 protected $link = null;
6
7 protected $instanceName = MySqlDbManager::DEFAULT_INSTANCE_NAME;
8
9 protected $result = null;
10
11 ////////counter vars/////////////
12 protected $lastFetchType = null;
13 protected $lastRecordPosition = 0;
14 protected $lastFieldPosition = 0;
15 protected $isTransactionStarted = false;
16 ////////////////////////////////
17
18 protected $logger = null;
19 protected $log = false;
20 protected $nonExitentTables = array();
21
22 protected $isInstanceLocked = false;
23
24 const FETCH_TYPE_RECORD = 'record';
25 const FETCH_TYPE_FIELD = 'field';
26 const RECORD_TYPE_ARRAY = 0;
27 const RECORD_TYPE_ASSOC = 1;
28 const RECORD_TYPE_OBJECT = 2;
29 const LOGGER_NAME = 'MysqlQuery';
30
31 /**
32 * Class constructor
33 *
34 * @param Db_MySqlDatabase db
35 *
36 */
37 public function __construct($instanceName = null, Logger $logger = null) {
38 if($instanceName !== null){
39 $this->instanceName = $instanceName;
40 }
41
42 if ($logger === null) {
43 $this->setLogger(new SessionLogger());
44 }
45 else {
46 $this->setLogger($logger);
47 }
48
49 $this->link = MySqlDbManager::getDbObject($instanceName)->getLink();
50 }
51
52 /**
53 * Switch to RW endpoint and lock on it. This will disable switching
54 * on RO endpoints if SELECT is executed
55 */
56 public function lockEndpoint(){
57 $this->switchToRWEndpoint();
58 $this->isInstanceLocked = true;
59 }
60
61 /**
62 * Release endpoint lock enable switching to RO endpoints
63 */
64 public function unlockEndpoint(){
65 $this->isInstanceLocked = false;
66 }
67
68 protected function chooseDbEndpoint($query){
69 $type = (self::isSelect($query) ? MySqlDbManager::ENDPOINT_TYPE_RO : MySqlDbManager::ENDPOINT_TYPE_RW);
70 $this->link = MySqlDbManager::getDbObject($this->instanceName, $type)->getLink();
71 }
72
73 protected function switchToRWEndpoint(){
74 $this->link = MySqlDbManager::getDbObject($this->instanceName, MySqlDbManager::ENDPOINT_TYPE_RW)->getLink();
75 }
76
77 public static function isSelect($query) {
78 // Default trim()'s mask plus left parentheses
79 $ltrimMask = "( \t\n\r\0\x0B";
80
81 return 'SELECT' === strtoupper(
82 substr(
83 ltrim($query, $ltrimMask), 0, 6
84 )
85 );
86 }
87
88 public function getInstanceName(){
89 return $this->instanceName;
90 }
91 public function setInstanceName($instanceName){
92 $this->instanceName = $instanceName;
93 }
94
95 public function setLogger(Logger $logger) {
96 $this->logger = $logger;
97 }
98
99 public function setLogging($bool) {
100 if (!is_bool($bool)) {
101 return false;
102 }
103 $this->log = $bool;
104 }
105
106 public function getLogging() {
107 return $this->log;
108 }
109
110 /**
111 * Get current error messgae from database
112 *
113 * @return bool
114 */
115 public function errorMessage() {
116 return $this->link->error;
117 }
118
119 /**
120 * Get current error code from database
121 *
122 * @return unknown
123 */
124 public function errorCode() {
125 return $this->link->errno;
126 }
127
128 /**
129 * Execute SQL query
130 *
131 * @param string $sqlStatement
132 * @return MysqlQuery
133 */
134 public function exec($sqlStatement) {
135 if(!$this->isInstanceLocked && !$this->isTransactionStarted){
136 $this->chooseDbEndpoint($sqlStatement);
137 }
138
139 if (empty($sqlStatement)) {
140 throw new EmptyArgumentException();
141 }
142
143 if ($this->log) {
144 $this->logger->log(static::LOGGER_NAME, $sqlStatement);
145 }
146
147 if (($this->result = $this->link->query($sqlStatement)) !== false) {
148 $this->lastFetchType = null;
149 $this->lastFieldPosition = 0;
150 $this->lastRecordPosition = 0;
151 return $this;
152 }
153 else {
154 $errorCode = $this->errorCode();
155 $errorMessage = $this->errorMessage();
156 if ($errorCode == 1146) {
157 preg_match("/Table \'.*?\.(.+?)\' doesn\'t exist/", $errorMessage, $matches);
158
159 if (isset($matches[1])) {
160 $nonExistantTableName = $matches[1];
161
162 if (!in_array($nonExistantTableName, $this->nonExitentTables)) {
163
164 $sqlFiles = Tbl::getPluginSQLFilePathsByTableName($nonExistantTableName);
165 if ($sqlFiles !== false) {
166 $this->startTransaction();
167 foreach ($sqlFiles as $sqlFilePath) {
168 self::executeSQLFile($sqlFilePath, ';');
169 }
170
171 if ($this->commit()) {
172 array_push($this->nonExitentTables, $nonExistantTableName);
173 return $this->exec($sqlStatement);
174 }
175 else {
176 $this->rollBack();
177 }
178 }
179 }
180 }
181 }
182 throw new MySqlException("MySQL Error: $errorCode: $errorMessage in query `$sqlStatement`", $errorCode);
183 }
184 }
185
186 /**
187 * Rows affected by query
188 *
189 * @return bool
190 */
191 public function affected() {
192 return $this->link->affected_rows;
193 }
194
195 /**
196 * Get last insert id
197 *
198 * @return int $insert_id
199 */
200 public function getLastInsertId() {
201 return $this->link->insert_id;
202 }
203
204 /**
205 * Analog of mysql_num_rows()
206 *
207 * @return int $number
208 */
209 public function countRecords() {
210 if ($this->result) {
211 return $this->result->num_rows;
212 }
213 else {
214 return false;
215 }
216 }
217
218 /**
219 * Analog of mysql_num_fields()
220 *
221 * @return int $number
222 */
223 public function countFields() {
224 if ($this->result) {
225 return $this->result->field_count;
226 }
227 else {
228 return false;
229 }
230 }
231
232 /**
233 * Fetch one row and move cursor to nex row
234 *
235 * @param int $type (0-Normal Array, 1-Associative Array, 2-Object)
236 * @return array
237 */
238 public function fetchRecord($type = self::RECORD_TYPE_ASSOC) {
239 if ($this->countRecords() == 0) {
240 return false;
241 }
242
243 if (!$this->result) {
244 return array();
245 }
246
247 if ($this->lastFetchType != self::FETCH_TYPE_RECORD) {
248 $this->result->data_seek($this->lastRecordPosition);
249 $this->lastFetchType = self::FETCH_TYPE_RECORD;
250 }
251 else {
252 ++$this->lastRecordPosition;
253 }
254
255 switch ($type) {
256 case self::RECORD_TYPE_ARRAY:
257 return $this->result->fetch_row();
258 case self::RECORD_TYPE_ASSOC:
259 return $this->result->fetch_assoc();
260 case self::RECORD_TYPE_OBJECT:
261 return $this->result->fetch_object();
262 default:
263 return array();
264 }
265 }
266
267 /**
268 * Fetch one field from row
269 *
270 * @param string $fieldName
271 * @param int $isNumeric (false - $field_identifier is name of the field, true - $field_identifier is number of the field)
272 * @return string
273 */
274 public function fetchField($fieldName, $isNumeric = false) {
275 if ($this->countRecords() == 0) {
276 return false;
277 }
278
279 if (!$this->result) {
280 return false;
281 }
282
283 if ($this->lastFetchType != self::FETCH_TYPE_FIELD) {
284 $this->result->data_seek($this->lastFieldPosition);
285 $this->lastFetchType = self::FETCH_TYPE_FIELD;
286 }
287 else {
288 ++$this->lastFieldPosition;
289 }
290
291 if ($isNumeric) {
292 $record = $this->result->fetch_row();
293 }
294 else {
295 $record = $this->result->fetch_assoc();
296 }
297
298 if ($record) {
299 return $record[$fieldName];
300 }
301 else {
302 return false;
303 }
304 }
305
306 /**
307 * Get array of the query
308 *
309 * @param int $offset
310 * @param int $len
311 * @param int $fieldsType (0-Normal,1-Assoc, 2-Object)
312 * @param int $rowsType (0-Normal,1-Assoc, 2-Object)
313 * @param string $rowsTypeField (name of the field to become index for Assoc $rowsType)
314 * @return array
315 */
316 public function fetchRecords($offset = 0, $len = 0, $fieldsType = self::RECORD_TYPE_ASSOC, $rowsType = self::RECORD_TYPE_ARRAY, $rowsTypeField = null) {
317 $returnArray = array();
318 $counter = 0;
319 $numRecords = $this->countRecords();
320
321 if (abs($offset) > $numRecords || $numRecords == 0 || !$this->result) {
322 return array();
323 }
324
325 if ($this->lastFetchType == self::FETCH_TYPE_FIELD) {
326 $resultLastPosition = $this->lastFieldPosition;
327 }
328 elseif ($this->lastFetchType == self::FETCH_TYPE_RECORD) {
329 $resultLastPosition = $this->lastRecordPosition;
330 }
331 else {
332 $resultLastPosition = 0;
333 }
334
335 $flagToReverceArray = false;
336
337 if ($len < 0) {
338 $flagToReverceArray = true;
339 }
340
341 if ($len > 0) {
342 if ($offset < 0) {
343 $offset = $numRecords - abs($offset);
344 }
345 }
346 elseif ($len < 0) {
347 if ($offset > 0) {
348 if (abs($len) > abs($offset)) {
349 $len = abs($offset);
350 $offset = 0;
351 }
352 else {
353 $offset = $offset - abs($len) + 1;
354 $len = abs($len);
355 }
356 }
357 elseif ($offset < 0) {
358 if (abs($len) > abs($offset)) {
359 $len = $numRecords - abs($offset) + 1;
360 $offset = 0;
361 }
362 else {
363 $offset = $numRecords - abs($offset) - abs($len) + 1;
364 $len = abs($len);
365 }
366 }
367 }
368 elseif ($len == 0) {
369 $len = $numRecords;
370 }
371
372 $this->result->data_seek($offset);
373
374 if ($rowsType == self::RECORD_TYPE_ARRAY) {
375 if ($fieldsType == self::RECORD_TYPE_ARRAY) {
376 while ($currentResult = $this->result->fetch_row()) {
377 $returnArray[$counter] = $currentResult;
378 $counter++;
379 if ($counter == abs($len)) {
380 break;
381 }
382 }
383 }
384 elseif ($fieldsType == self::RECORD_TYPE_ASSOC) {
385 while ($currentResult = $this->result->fetch_assoc()) {
386 $returnArray[$counter] = $currentResult;
387 $counter++;
388 if ($counter == abs($len)) {
389 break;
390 }
391 }
392 }
393 elseif ($fieldsType == self::RECORD_TYPE_OBJECT) {
394 while ($currentResult = $this->result->fetch_object()) {
395 $returnArray[$counter] = $currentResult;
396 $counter++;
397 if ($counter == abs($len)) {
398 break;
399 }
400 }
401 }
402 else {
403 return array();
404 }
405 }
406 elseif ($rowsType == self::RECORD_TYPE_ASSOC and ! empty($rowsTypeField)) {
407 $numFieldType = 0;
408 if ($fieldsType == self::RECORD_TYPE_ARRAY) {
409 for ($i = 0; $i < $this->countFields(); $i++) {
410 if ($this->fieldName($i) == $rowsTypeField) {
411 $numFieldType = $i;
412 break;
413 }
414 }
415 while ($currentResult = $this->result->fetch_row()) {
416 $returnArray[$currentResult[$numFieldType]] = $currentResult;
417 $counter++;
418 if ($counter == abs($len)) {
419 break;
420 }
421 }
422 }
423 elseif ($fieldsType == self::RECORD_TYPE_ASSOC) {
424 while ($currentResult = $this->result->fetch_assoc()) {
425 $returnArray[$currentResult[$rowsTypeField]] = $currentResult;
426 $counter++;
427 if ($counter == abs($len)) {
428 break;
429 }
430 }
431 }
432 elseif ($fieldsType == self::RECORD_TYPE_OBJECT) {
433 while ($currentResult = $this->result->fetch_object()) {
434 $returnArray[$currentResult->$rowsTypeField] = $currentResult;
435 $counter++;
436 if ($counter == abs($len)) {
437 break;
438 }
439 }
440 }
441 }
442 else {
443 return array();
444 }
445 if ($resultLastPosition < $numRecords) {
446 $this->result->data_seek($resultLastPosition);
447 }
448 if ($flagToReverceArray) {
449 $returnArray = array_reverse($returnArray);
450 }
451
452 return $returnArray;
453 }
454
455 /**
456 * Get array of only one field
457 *
458 * @param string $fieldName
459 * @param bool $isNumeric (true-$field_identifier is numeric, false-$field_identifier is a name)
460 * @param int $offset
461 * @param int $len
462 * @return array
463 */
464 public function fetchFields($fieldName, $isNumeric = false, $offset = 0, $len = 0) {
465 $returnArray = array();
466 $counter = 0;
467 $numRecords = $this->countRecords();
468
469 if (abs($offset) > $numRecords || $numRecords == 0 || !$this->result) {
470 return array();
471 }
472
473 if ($this->lastFetchType == self::FETCH_TYPE_FIELD) {
474 $resultLastPosition = $this->lastFieldPosition;
475 }
476 elseif ($this->lastFetchType == self::FETCH_TYPE_RECORD) {
477 $resultLastPosition = $this->lastRecordPosition;
478 }
479 else {
480 $resultLastPosition = 0;
481 }
482
483 $flagToReverceArray = false;
484
485 if ($len < 0) {
486 $flagToReverceArray = true;
487 }
488
489 if ($len > 0) {
490 if ($offset < 0) {
491 $offset = $numRecords - abs($offset);
492 }
493 }
494 elseif ($len < 0) {
495 if ($offset > 0) {
496 if (abs($len) > abs($offset)) {
497 $len = abs($offset);
498 $offset = 0;
499 }
500 else {
501 $offset = $offset - abs($len) + 1;
502 $len = abs($len);
503 }
504 }
505 elseif ($offset < 0) {
506 if (abs($len) > abs($offset)) {
507 $len = $numRecords - abs($offset) + 1;
508 $offset = 0;
509 }
510 else {
511 $offset = $numRecords - abs($offset) - abs($len) + 1;
512 $len = abs($len);
513 }
514 }
515 }
516 elseif ($len == 0) {
517 $len = $numRecords;
518 }
519
520 $this->result->data_seek($offset);
521
522 if ($isNumeric == false) {
523 while ($currentResult = $this->result->fetch_assoc()) {
524 $returnArray[$counter] = $currentResult[$fieldName];
525 $counter++;
526 if ($counter == abs($len)) {
527 break;
528 }
529 }
530 }
531 elseif ($isNumeric == true) {
532 while ($currentResult = $this->result->fetch_row()) {
533 $returnArray[$counter] = $currentResult[$fieldName];
534 $counter++;
535 if ($counter == abs($len)) {
536 break;
537 }
538 }
539 }
540 else {
541 return array();
542 }
543
544
545 if ($resultLastPosition < $numRecords) {
546 $this->result->data_seek($resultLastPosition);
547 }
548 if ($flagToReverceArray) {
549 $returnArray = array_reverse($returnArray);
550 }
551
552 return $returnArray;
553 }
554
555 /**
556 * Get name of specified field
557 *
558 * @param int $offset
559 * @return string
560 */
561 public function fieldName($offset) {
562 return $this->fieldInfo($offset)->name;
563 }
564
565 /**
566 * Get type of specified field
567 *
568 * @param int $offset
569 * @return string
570 */
571 public function fieldType($offset) {
572 return $this->fieldInfo($offset)->type;
573 }
574
575 /**
576 * Get length of specified field
577 *
578 * @param int $offset
579 * @return int
580 */
581 public function fieldLen($offset) {
582 return $this->fieldInfo($offset)->length;
583 }
584
585
586 /**
587 * Get flags of specified field
588 *
589 * @param int $offset
590 * @return array
591 */
592 public function fieldFlags($offset) {
593 return $this->fieldInfo($offset)->flags;
594 }
595
596
597 /**
598 *
599 * @param int $offset
600 * @return mysqli_fetch_field_direct
601 */
602 public function fieldInfo($offset){
603 if ($this->result and $offset >= 0 and $offset < $this->countFields()) {
604 return $this->result->fetch_field_direct($offset);
605 }
606 return false;
607 }
608
609 /**
610 * Get found rows count from select query which
611 * uses SQL_CALC_FOUND_ROWS parameter
612 *
613 * @return integer
614 */
615 public function getFoundRowsCount() {
616 $this->exec("SELECT FOUND_ROWS() AS `cnt`");
617 return $this->fetchField('cnt');
618 }
619
620 public function escapeString($string){
621 return $this->link->real_escape_string($string);
622 }
623
624
625 /**
626 * Starts a new transaction
627 *
628 * @access public
629 * @throws DB_Exception
630 * @return boolean
631 */
632 public function startTransaction($withSnapshot = false, $name = NULL) {
633 if ($this->isTransactionStarted) {
634 return false;
635 }
636 $this->switchToRWEndpoint();
637
638 if($name !== null){
639 if($this->link->begin_transaction(($withSnapshot ? MYSQLI_TRANS_START_WITH_CONSISTENT_SNAPSHOT : NULL), $name)){
640 $this->isTransactionStarted = true;
641 return true;
642 }
643 }
644 else{
645 if($this->link->begin_transaction(($withSnapshot ? MYSQLI_TRANS_START_WITH_CONSISTENT_SNAPSHOT : NULL))){
646 $this->isTransactionStarted = true;
647 return true;
648 }
649 }
650
651
652 return false;
653 }
654
655 /**
656 * Commits a last started transaction
657 *
658 * @access public
659 * @return boolean
660 */
661 public function commit($name = NULL) {
662 if (!$this->isTransactionStarted) {
663 return false;
664 }
665
666 $result = $this->link->commit(NULL, $name);
667 $this->isTransactionStarted = false;
668 return $result;
669 }
670
671 /**
672 * Saves a last started transaction
673 *
674 * @param string $identifier savePoint identifier
675 *
676 * @access public
677 * @return boolean
678 */
679 public function savePoint($identifier) {
680 if (!$this->isTransactionStarted || empty($identifier)) {
681 return false;
682 }
683 return $this->link->savepoint($identifier);
684 }
685
686 /**
687 * Rolls back to last savePoint
688 *
689 * @param string $savepointIdentifier
690 *
691 * @access public
692 * @return boolean
693 */
694 public function rollBack($savepointIdentifier = NULL) {
695 if (!$this->isTransactionStarted) {
696 return false;
697 }
698
699 $result = $this->link->rollback(NULL, $savepointIdentifier);
700
701 $this->isTransactionStarted = false;
702 return $result;
703 }
704
705 /**
706 * Locks tables from given array
707 *
708 * @param array $tables
709 *
710 * @example $tables = Array("table_name_1" => "r", "table_name_2" => "w", "table_name_3" => "");
711 *
712 * or
713 *
714 * @param string $table
715 * @param string $type
716 *
717 * @example $tables = "table", $type = "r" (READ)
718 * @example $tables = "table", $type = "w" (WRITE)
719 *
720 * @access public
721 * @return boolean
722 */
723 public function lockTables($tables, $type = "r") {
724 $this->switchToRWEndpoint();
725
726 if (empty($tables)) {
727 return false;
728 }
729
730 $lockQuery = "LOCK TABLES ";
731 if (is_array($tables)) {
732 $lockQueriesArr = array();
733 foreach ($tables as $table_name => $current_type) {
734 $query .= $table_name . " ";
735 if ($current_type == "w") {
736 $query .= " WRITE";
737 }
738 else {
739 $query .= " READ";
740 }
741 array_push($lockQueriesArr, $query);
742 }
743 $lockQuery .= implode(", ", $lockQueriesArr);
744 }
745 elseif (is_string($tables)) {
746 $lockQuery .= $tables;
747 if ($type == "w") {
748 $lockQuery .= " WRITE";
749 }
750 else {
751 $lockQuery .= " READ";
752 }
753 }
754
755 return $this->link->query($lockQuery);
756 }
757
758 /**
759 * Unlocks tables that were locked by current thread
760 *
761 * @access public
762 * @return boolean
763 */
764 public function unlockTables() {
765 $this->switchToRWEndpoint();
766
767 return $this->link->query('UNLOCK TABLES');
768 }
769
770 /**
771 * Drops table or tables
772 *
773 * @param array $tableName
774 *
775 * or
776 *
777 * @param string $tableName
778 *
779 * @access public
780 * @return boolean
781 */
782 public function dropTables($tableName) {
783 if (empty($tableName)) {
784 return false;
785 }
786 $this->switchToRWEndpoint();
787
788 $dropQuery = "DROP TABLE ";
789 if (is_array($tableName)) {
790 $dropQuery .= implode(",", $tableName);
791 }
792 elseif (is_string($tableName)) {
793 $dropQuery .= $tableName;
794 }
795
796 return $this->link->query($dropQuery);
797 }
798
799 /**
800 * Renames table
801 *
802 * @param string $oldName
803 * @param string $newName
804 *
805 * @access public
806 * @return boolean
807 */
808 public function renameTable($oldName, $newName) {
809 $this->switchToRWEndpoint();
810
811 if (!empty($oldName) && !empty($newName)) {
812 return $this->link->query("RENAME TABLE $oldName TO $newName");
813 }
814 else {
815 return false;
816 }
817 }
818
819 public function executeSQLFile($file, $delimiter = ';') {
820 $this->switchToRWEndpoint();
821
822 $matches = array();
823 $otherDelimiter = false;
824 if (is_file($file) === true) {
825 $file = fopen($file, 'r');
826 if (is_resource($file) === true) {
827 $query = array();
828 while (feof($file) === false) {
829 $query[] = fgets($file);
830 if (preg_match('~' . preg_quote('delimiter', '~') . '\s*([^\s]+)$~iS', end($query), $matches) === 1) {
831 //DELIMITER DIRECTIVE DETECTED
832 array_pop($query); //WE DON'T NEED THIS LINE IN SQL QUERY
833 if ($otherDelimiter = ( $matches[1] != $delimiter )) {
834
835 }
836 else {
837 // THIS IS THE DEFAULT DELIMITER, DELETE THE LINE BEFORE THE LAST (THAT SHOULD BE THE NOT DEFAULT DELIMITER) AND WE SHOULD CLOSE THE STATEMENT
838 array_pop($query);
839 $query[] = $delimiter;
840 }
841 }
842 if (!$otherDelimiter && preg_match('~' . preg_quote($delimiter, '~') . '\s*$~iS', end($query)) === 1) {
843 $query = trim(implode('', $query));
844
845 $this->exec($query);
846 }
847 if (is_string($query) === true) {
848 $query = array();
849 }
850 }
851 return fclose($file);
852 }
853 }
854 return false;
855 }
856
857}