· 10 years ago · Sep 22, 2016, 01:04 AM
1<?php
2/**
3 * @brief Database Class
4 * @author <a href='http://www.invisionpower.com'>Invision Power Services, Inc.</a>
5 * @copyright (c) 2001 - 2016 Invision Power Services, Inc.
6 * @license http://www.invisionpower.com/legal/standards/
7 * @package IPS Community Suite
8 * @since 18 Feb 2013
9 * @version SVN_VERSION_NUMBER
10 */
11
12namespace IPS;
13
14/* To prevent PHP errors (extending class does not exist) revealing path */
15if ( !defined( '\IPS\SUITE_UNIQUE_KEY' ) )
16{
17 header( ( isset( $_SERVER['SERVER_PROTOCOL'] ) ? $_SERVER['SERVER_PROTOCOL'] : 'HTTP/1.0' ) . ' 403 Forbidden' );
18 exit;
19}
20
21/**
22 * @brief Database Class
23 * @note All functionality MUST be supported by MySQL 5.1.3 and higher. All references to the MySQL manual are therefore the 5.1 version.
24 */
25class _Db extends \mysqli
26{
27 /**
28 * SELECT flags
29 */
30 const SELECT_DISTINCT = 1;
31 const SELECT_SQL_CALC_FOUND_ROWS = 2;
32 const SELECT_MULTIDIMENSIONAL_JOINS = 4;
33
34 /**
35 * INSERT/UPDATE flags
36 */
37 const LOW_PRIORITY = 1;
38 const IGNORE = 2;
39
40 /**
41 * @brief Datatypes
42 */
43 public static $dataTypes = array(
44 'database_column_type_numeric' => array(
45 'TINYINT' => 'TINYINT [±127 ⊻ 255] [1B]',
46 'SMALLINT' => 'SMALLINT [±3.3e4 ⊻ 6.6e4] [2B]',
47 'MEDIUMINT' => 'MEDIUMINT [±8.4e6 ⊻ 1.7e7] [3B]',
48 'INT' => 'INT [±2.1e9 ⊻ 4.3e9] [4B]',
49 'BIGINT' => 'BIGINT [±9.2e18 ⊻ 1.8e19] [8B]',
50 'DECIMAL' => 'DECIMAL',
51 'FLOAT' => 'FLOAT',
52 'BIT' => 'BIT',
53
54 ),
55 'database_column_type_datetime' => array(
56 'DATE' => 'DATE',
57 'DATETIME' => 'DATETIME',
58 'TIMESTAMP' => 'TIMESTAMP',
59 'TIME' => 'TIME',
60 'YEAR' => 'YEAR',
61 ),
62 'database_column_type_string' => array(
63 'CHAR' => 'CHAR [M≤6.6e4] [(M*w)B]',
64 'VARCHAR' => 'VARCHAR [M≤6.6e4] [(L+(1∨2))B]',
65 'TINYTEXT' => 'TINYTEXT [256B] [(L+1)B]',
66 'TEXT' => 'TEXT [64kB] [(L+2)B]',
67 'MEDIUMTEXT'=> 'MEDIUMTEXT [16MB] [(L+3)B]',
68 'LONGTEXT' => 'LONGTEXT [4GB] [(L+4)B]',
69 'BINARY' => 'BINARY [M≤6.6e4] [(M)B]',
70 'VARBINARY' => 'VARBINARY [M≤6.6e4] [(L+(1∨2))B]',
71 'TINYBLOB' => 'TINYBLOB [256B] [(L+1)B]',
72 'BLOB' => 'BLOB [64kB] [(L+2)B]',
73 'MEDIUMBLOB'=> 'MEDIUMBLOB [16MB] [(L+3)B]',
74 'BIGBLOB' => 'BIGBLOB [4GB] [(L+4)B]',
75 'ENUM' => 'ENUM [6.6e4] [(1∨2)B]',
76 'SET' => 'SET [64] [(1∨2∨3∨4∨8)B]',
77 )
78 );
79
80 /**
81 * @brief Multiton Store
82 */
83 protected static $multitons;
84
85 /**
86 * Get instance
87 *
88 * @param mixed $identifier Identifier
89 * @param array $connectionSettings Connection settings (use when initiating a new connection)
90 * @return \IPS\Db
91 */
92 public static function i( $identifier=NULL, $connectionSettings=array() )
93 {
94 /* Did we pass a null value? */
95 $identifier = ( $identifier === NULL ) ? '__MAIN' : $identifier;
96
97 /* Don't have an instance? */
98 if( !isset( static::$multitons[ $identifier ] ) )
99 {
100 /* Load the default settings if necessary */
101 if( $identifier === '__MAIN' )
102 {
103 require( \IPS\ROOT_PATH . '/conf_global.php' );
104 $connectionSettings = isset( $INFO ) ? $INFO : array();
105 }
106
107 /* Connect */
108 $classname = get_called_class();
109 static::$multitons[ $identifier ] = @new $classname(
110 $connectionSettings['sql_host'],
111 $connectionSettings['sql_user'],
112 $connectionSettings['sql_pass'],
113 $connectionSettings['sql_database'],
114 ( isset( $connectionSettings['sql_port'] ) and $connectionSettings['sql_port']) ? $connectionSettings['sql_port'] : NULL,
115 ( isset( $connectionSettings['sql_socket'] ) and $connectionSettings['sql_socket'] ) ? $connectionSettings['sql_socket'] : NULL
116 );
117
118 /* If the connection failed, throw an exception */
119 if( $error = mysqli_connect_error() )
120 {
121 throw new \IPS\Db\Exception( $error, static::$multitons[ $identifier ]->connect_errno );
122 }
123
124 /* UTF8MB4? */
125 if ( isset( $connectionSettings['sql_utf8mb4'] ) and $connectionSettings['sql_utf8mb4'] )
126 {
127 static::$multitons[ $identifier ]->charset = 'utf8mb4';
128 static::$multitons[ $identifier ]->collation = 'utf8mb4_unicode_ci';
129 static::$multitons[ $identifier ]->binaryCollation = 'utf8mb4_bin';
130 }
131
132 /* If we succeeded, set the charset */
133 if( !static::$multitons[ $identifier ]->set_charset( static::$multitons[ $identifier ]->charset ) )
134 {
135 /* But if setting that failed, fall back to UTF8 */
136 static::$multitons[ $identifier ]->charset = 'utf8';
137 static::$multitons[ $identifier ]->collation = 'utf8_unicode_ci';
138 static::$multitons[ $identifier ]->binaryCollation = 'utf8_bin';
139
140 static::$multitons[ $identifier ]->set_charset( static::$multitons[ $identifier ]->charset );
141 }
142
143 if ( \IPS\IN_DEV )
144 {
145 static::$multitons[ $identifier ]->query( "SET sql_mode='STRICT_ALL_TABLES,ONLY_FULL_GROUP_BY'" );
146 }
147
148 /* Set the prefix */
149 if ( isset( $connectionSettings['sql_tbl_prefix'] ) )
150 {
151 static::$multitons[ $identifier ]->prefix = $connectionSettings['sql_tbl_prefix'];
152 }
153 }
154
155 /* Return */
156 return static::$multitons[ $identifier ];
157 }
158
159 /**
160 * @brief Charset
161 */
162 public $charset = 'utf8';
163
164 /**
165 * @brief Collation
166 */
167 public $collation = 'utf8_unicode_ci';
168
169 /**
170 * @brief Binary Collation
171 */
172 public $binaryCollation = 'utf8_bin';
173
174 /**
175 * @brief Table Prefix
176 */
177 public $prefix = '';
178
179 /**
180 * @brief Query log
181 */
182 public $log = array();
183
184 /**
185 * @brief Return the query instead of executing it
186 * @note Only designed to work with methods that call query() vs prepared statements
187 */
188 public $returnQuery = FALSE;
189
190 /**
191 * Run a query
192 *
193 * @param string $query The query
194 * @param bool $log Should be logged?
195 * @return mixed
196 * @see <a href="http://uk1.php.net/manual/en/mysqli.query.php">mysqli::query</a>
197 * @throws \IPS\Db\Exception
198 */
199 public function query( $query, $log = TRUE )
200 {
201 /* Should we return the query instead of executing it? */
202 if( $this->returnQuery === TRUE )
203 {
204 $this->returnQuery = FALSE;
205 return $query;
206 }
207
208 /* Log */
209 if ( \IPS\QUERY_LOG and $log )
210 {
211 $this->log( $query );
212 }
213
214 /* Run */
215 $return = parent::query( $query );
216 if ( $return === FALSE )
217 {
218 throw new \IPS\Db\Exception( $this->error, $this->errno );
219 }
220 return $return;
221 }
222
223 /**
224 * Force a query to run regardless of $this->returnQuery
225 *
226 * @param string $query The query
227 * @param bool $log Should be logged?
228 * @return mixed
229 * @see <a href="http://uk1.php.net/manual/en/mysqli.query.php">mysqli::query</a>
230 * @throws \IPS\Db\Exception
231 */
232 public function forceQuery( $query, $log = TRUE )
233 {
234 $return = $this->returnQuery;
235 $this->returnQuery = false;
236
237 $result = $this->query( $query, $log );
238
239 $this->returnQuery = $return;
240
241 return $result;
242 }
243
244 /**
245 * Run Prepared SQL Statement
246 *
247 * @param string $query SQL Statement
248 * @param array $_binds Variables to bind
249 * @return \mysqli_stmt
250 */
251 public function preparedQuery( $query, array $_binds )
252 {
253 /* Init Bind object */
254 $bind = new Db\Bind();
255
256 /* Sort out subqueries */
257 $binds = array();
258 $i = 0;
259 for ( $j = 0; $j < \strlen( $query ); $j++ )
260 {
261 if ( $query[ $j ] == '?' )
262 {
263 if ( array_key_exists( $i, $_binds ) )
264 {
265 if ( $_binds[ $i ] instanceof \IPS\Db\Select )
266 {
267 $query = \substr( $query, 0, $j ) . $_binds[ $i ]->query . \substr( $query, $j + 1);
268 $j += \strlen( $_binds[ $i ]->query );
269
270 foreach ( $_binds[ $i ]->binds as $_bind )
271 {
272 $binds[] = $_bind;
273 }
274 }
275 else
276 {
277 $binds[] = $_binds[ $i ];
278 }
279
280 $i++;
281 }
282 }
283 }
284
285 /* Loop values to bind */
286 $i = 0;
287 $longThreshold = 1048576;
288 $sendAsLong = array();
289 foreach ( $binds as $bindVal )
290 {
291 if( ( is_object( $bindVal ) OR is_string( $bindVal ) ) AND \strlen( (string) $bindVal ) > $longThreshold )
292 {
293 $sendAsLong[ $i ] = (string) $bindVal;
294 }
295
296 $i++;
297 switch ( gettype( $bindVal ) )
298 {
299 case 'boolean':
300 case 'integer':
301 $bind->add( 'i', $bindVal );
302 break;
303
304 case 'double':
305 $bind->add( 'd', $bindVal );
306 break;
307
308 case 'string':
309 if( \strlen( $bindVal ) > $longThreshold )
310 {
311 $bind->add( 'b', NULL );
312 }
313 else
314 {
315 $bind->add( 's', $bindVal );
316 }
317 break;
318
319 case 'object':
320 if( method_exists( $bindVal, '__toString' ) )
321 {
322 if( \strlen( $bindVal ) > $longThreshold )
323 {
324 $bind->add( 'b', NULL );
325 }
326 else
327 {
328 $bind->add( 's', (string) $bindVal );
329 }
330 break;
331 }
332 // Deliberately no break
333
334 case 'NULL':
335 case 'array':
336 case 'resource':
337 case 'unknown type':
338 default:
339 /* For NULL values, you can't bind, so we adjust the query to actually pass a NULL value */
340 $pos = 0;
341 for ( $j=0; $j<$i; $j++ )
342 {
343 $pos = mb_strpos( $query, '?', $pos ) + 1;
344 }
345 $query = mb_substr( $query, 0, $pos - 1 ) . 'NULL' . mb_substr( $query, $pos );
346 $i--;
347 break;
348 }
349 }
350
351 /* Log */
352 if ( \IPS\QUERY_LOG )
353 {
354 /* Log */
355 $this->log( static::_replaceBinds( $query, $binds ) );
356 }
357
358 /* Return full query */
359 if( $this->returnQuery === TRUE )
360 {
361 $this->returnQuery = FALSE;
362 return static::_replaceBinds( $query, $binds );
363 }
364
365 /* Add a backtrace to the query so we know where it came from if it causes issues */
366 $comment = '??';
367 $line = '?';
368 foreach( debug_backtrace( FALSE ) as $b )
369 {
370 if ( isset( $b['line'] ) )
371 {
372 $line = $b['line'];
373 }
374
375 if( isset( $b['class'] ) and !in_array( $b['class'], array( 'IPS\_Db', 'IPS\Db\_Select', 'IPS\Patterns\_ActiveRecord', 'IPS\Patterns\_ActiveRecordIterator' ) ) )
376 {
377 $comment = "{$b['class']}::{$b['function']}:{$line}";
378 break;
379 }
380 }
381 $_query = $query;
382 $query = "/*{$comment}*/ {$query}";
383
384 /* Prepare */
385 $stmt = parent::prepare( $query );
386 if( $stmt === FALSE )
387 {
388 throw new \IPS\Db\Exception( $this->error, $this->errno, NULL, $_query, $binds );
389 }
390
391 /* Bind values */
392 if( $bind->haveBinds() === TRUE )
393 {
394 call_user_func_array( array( $stmt, 'bind_param' ), $bind->get() );
395
396 if( count( $sendAsLong ) )
397 {
398 foreach( $sendAsLong as $index => $data )
399 {
400 $chunks = str_split( $data, $longThreshold - 1 );
401
402 foreach( $chunks as $chunk )
403 {
404 $stmt->send_long_data( $index, $chunk );
405 }
406 }
407 }
408 }
409
410 /* Execute */
411 $stmt->execute();
412
413 /* Handle errors */
414 $count = 1;
415 while ( $stmt->error )
416 {
417 /* If we hit a deadlock, try again once */
418 if ( $stmt->errno === 1213 and $count < 2 )
419 {
420 $stmt->execute();
421 $count++;
422 }
423
424 /* Throw error */
425 else
426 {
427 throw new \IPS\Db\Exception( $stmt->error, $stmt->errno, NULL, $_query, $binds );
428 }
429 }
430
431 /* Store result */
432 $stmt->store_result();
433
434 /* Return a Statement object */
435 return $stmt;
436 }
437
438 /**
439 * Log
440 *
441 * @param string $logQuery Query to log
442 * @return void
443 */
444 protected function log( $logQuery )
445 {
446 $this->log[] = array(
447 'query' => $logQuery,
448 'backtrace' => var_export( debug_backtrace( DEBUG_BACKTRACE_IGNORE_ARGS ), TRUE ),
449 'extra' => NULL
450 );
451 }
452
453 /**
454 * Build SELECT statement
455 *
456 * @param array|string $columns The columns (as an array) to select or an expression
457 * @param array|string $table The table to select from. Either (string) table_name or (array) ( name, alias ) or \IPS\Db\Select object
458 * @param array|string|NULL $where WHERE clause - see \IPS\Db::compileWhereClause() for details
459 * @param string|NULL $order ORDER BY clause
460 * @param array|int $limit Rows to fetch or array( offset, limit )
461 * @param string|NULL|array $group Column(s) to GROUP BY
462 * @param array|string|NULL $having HAVING clause (same format as WHERE clause)
463 * @param int $flags Bitwise flags
464 * @li \IPS\Db::SELECT_DISTINCT Will use SELECT DISTINCT
465 * @li \IPS\Db::SELECT_SQL_CALC_FOUND_ROWS Will add SQL_CALC_FOUND_ROWS
466 * @li \IPS\Db::SELECT_MULTIDIMENSIONAL_JOINS Will return the result as a multidimensional array, with each joined table separately
467 * @return \IPS\Db\Select
468 *
469 */
470 public function select( $columns=NULL, $table, $where=NULL, $order=NULL, $limit=NULL, $group=NULL, $having=NULL, $flags=0 )
471 {
472 $binds = array();
473 $query = 'SELECT ';
474
475 /* Flags */
476 if ( $flags & static::SELECT_DISTINCT )
477 {
478 $query .= 'DISTINCT ';
479 }
480 if ( $flags & static::SELECT_SQL_CALC_FOUND_ROWS )
481 {
482 $query .= 'SQL_CALC_FOUND_ROWS ';
483 }
484
485 /* Columns */
486 if ( is_string( $columns ) )
487 {
488 $query .= $columns;
489 }
490 else
491 {
492 $query .= implode( ', ', array_map( function( $col )
493 {
494 return ( mb_strpos( $col, '`' ) === FALSE ) ? ( '`' . $col . '`' ) : $col;
495 }, $columns ) );
496 }
497
498 /* Tables */
499 if ( $table instanceof \IPS\Db\Select )
500 {
501 $tableQuery = $table->query;
502 $binds = $table->binds;
503 preg_match( '/FROM `(.+?)`( AS `(.+?)`)?/', $tableQuery, $matches );
504 $query .= isset( $matches[3] ) ? " FROM ( {$tableQuery} ) AS `{$matches[3]}`" : ( " FROM ( {$tableQuery} ) AS `" . md5(uniqid()) . '`' );
505 }
506 elseif ( is_array( $table ) )
507 {
508 if ( is_array( $table[0] ) and count( $table[0] ) )
509 {
510 $tables = array();
511 foreach( $table as $item )
512 {
513 $tables[] = " `{$this->prefix}{$item[0]}` AS `{$item[1]}`";
514 }
515
516 $query .= " FROM " . implode( ', ', $tables );
517 }
518 else
519 {
520 $tableName = ( $table[0] instanceof \IPS\Db\Select ) ? '(' . $table[0] . ')' : '`' . $this->prefix . $table[0] . '`';
521 $query .= " FROM {$tableName} AS `{$table[1]}`";
522 }
523 }
524 else
525 {
526 $query .= $this->prefix ? " FROM `{$this->prefix}{$table}` AS `{$table}`" : " FROM `{$table}`";
527 }
528
529 /* WHERE */
530 if ( $where )
531 {
532 $where = $this->compileWhereClause( $where );
533 $query .= ' WHERE ' . $where['clause'];
534 $binds = $where['binds'];
535 }
536
537 /* Group? */
538 if( $group )
539 {
540 if ( is_array( $group ) )
541 {
542 $query .= " GROUP BY " . implode( ',', array_map( function( $val )
543 {
544 if( mb_strpos( $val, '.' ) !== FALSE )
545 {
546 $pieces = explode( '.', $val );
547
548 foreach( $pieces as $k => $piece )
549 {
550 $pieces[ $k ] = '`' . $piece . '`';
551 }
552
553 return implode( '.', $pieces );
554 }
555
556 return "`{$val}`";
557 }, $group ) );
558 }
559 else
560 {
561 if( mb_strpos( $group, '.' ) !== FALSE )
562 {
563 $pieces = explode( '.', $group );
564
565 foreach( $pieces as $k => $piece )
566 {
567 $pieces[ $k ] = '`' . $piece . '`';
568 }
569
570 $group = implode( '.', $pieces );
571 }
572 else
573 {
574 $group = "`{$group}`";
575 }
576
577 $query .= " GROUP BY {$group}";
578 }
579 }
580
581 /* Having? */
582 if( $having )
583 {
584 $having = $this->compileWhereClause( $having );
585 $query .= ' HAVING ' . $having['clause'];
586 $binds = array_merge( $binds, $having['binds'] );
587 }
588
589 /* Order? */
590 if( $order )
591 {
592 $query .= ' ORDER BY ' . $order;
593 }
594
595 /* Limit */
596 if( $limit )
597 {
598 $query .= $this->compileLimitClause( $limit );
599 }
600
601 /* Return */
602 return new \IPS\Db\Select( $query, $binds, $this, $flags & static::SELECT_MULTIDIMENSIONAL_JOINS );
603 }
604
605 /**
606 * Build UNION statement
607 *
608 * @param array $selects Array of \IPS\Db\Select objects
609 * @param string|NULL $order ORDER BY clause
610 * @param array|int $limit Rows to fetch or array( offset, limit )
611 * @param string|null $group Group by clause
612 * @param bool $unionAll TRUE to perform a UNION ALL, FALSE (default) to perform a regular UNION
613 * @param int $flags Bitwise flags
614 * @param array|string|NULL $where WHERE clause (see example)
615 * @param string $querySelect Custom select for the outer query
616 * @li \IPS\Db::SELECT_SQL_CALC_FOUND_ROWS Will add SQL_CALC_FOUND_ROWS
617 * @return \IPS\Db|Select
618 */
619 public function union( $selects, $order, $limit, $group=NULL, $unionAll=FALSE, $flags=0, $where=NULL, $querySelect='*' )
620 {
621 /* Combine selects */
622 $query = array();
623 $binds = array();
624 foreach ( $selects as $s )
625 {
626 $query[] = '( ' . $s->query . ' )';
627 $binds = array_merge( $binds, $s->binds );
628 }
629
630 $union = $unionAll ? "UNION ALL" : "UNION";
631
632 $query = "SELECT " . ( $flags & static::SELECT_SQL_CALC_FOUND_ROWS ? "SQL_CALC_FOUND_ROWS " : "" ) . $querySelect . " FROM( " . implode( ' ' . $union . ' ', $query ) . ") derivedTable ";
633
634 /* WHERE */
635 if ( $where )
636 {
637 $where = $this->compileWhereClause( $where );
638 $query .= ' WHERE ' . $where['clause'];
639 $binds = array_merge( $binds, $where['binds'] );
640 }
641
642 /* Group */
643 if( $group )
644 {
645 $query.= " GROUP BY " . $group;
646 }
647
648 /* Order? */
649 if( $order )
650 {
651 $query .= ' ORDER BY ' . $order;
652 }
653
654 /* Limit */
655 if( $limit )
656 {
657 $query .= $this->compileLimitClause( $limit );
658 }
659
660 /* Return */
661 return new \IPS\Db\Select( $query, $binds, $this );
662 }
663
664 /**
665 * Run INSERT statement and return insert ID
666 *
667 * @see <a href='http://dev.mysql.com/doc/refman/5.1/en/insert.html'>INSERT Syntax</a>
668 * @param string $table Table name
669 * @param array|\IPS\Db\Select $set Values to insert or array of values to set for multiple rows (NB, if providing multiple rows, they MUST all contain the same columns) or a statement to do INSERT INTO SELECT FROM
670 * @param bool $odkUpdate Append an ON DUPLICATE KEY UPDATE clause to the query. Similar to the replace() method but updates if a record is found, instead of delete and reinsert.
671 * @param bool $ignoreErrors Ignore errors?
672 * @see \IPS\Db::replace()
673 * @return int
674 * @throws \IPS\Db\Exception
675 */
676 public function insert( $table, $set, $odkUpdate=FALSE, $ignoreErrors=FALSE )
677 {
678 /* Build */
679 $query = $this->_buildInsertQuery( ( $ignoreErrors ? 'INSERT IGNORE' : 'INSERT' ), $table, $set );
680
681 /* Add "ON DUPLICATE KEY UPDATE" */
682 if( $odkUpdate )
683 {
684 $query[0] .= " ON DUPLICATE KEY UPDATE " . implode( ', ', array_map( function( $val ){ return "{$val}=VALUES({$val})"; }, $query[2] ) );
685 }
686
687 /* Run */
688 $return = $this->returnQuery;
689
690 $stmt = $this->preparedQuery( $query[0], $query[1] );
691
692 if( $return === TRUE )
693 {
694 return $stmt;
695 }
696
697 return $stmt->insert_id;
698 }
699
700 /**
701 * Run REPLACE statament and return number of affected rows OR inserted ID
702 *
703 * @see <a href='http://dev.mysql.com/doc/refman/5.1/en/replace.html'>REPLACE Syntax</a>
704 * @param string $table Table name
705 * @param array $set Values to insert
706 * @param bool $getInsertId If TRUE, returns the insert ID rather than the number of affected rows
707 * @return int
708 * @throws \IPS\Db\Exception
709 */
710 public function replace( $table, $set, $getInsertId=false )
711 {
712 /* Build */
713 $query = $this->_buildInsertQuery( 'REPLACE', $table, $set );
714
715 $return = $this->returnQuery;
716
717 $stmt = $this->preparedQuery( $query[0], $query[1] );
718
719 if( $return === TRUE )
720 {
721 return $stmt;
722 }
723
724 return $getInsertId ? $stmt->insert_id : $stmt->affected_rows;
725 }
726
727 /**
728 * Build the replace or insert into query
729 *
730 * @param string $type INSERT|REPLACE
731 * @param string $table Table name
732 * @param array|\IPS\Db\Select $set Values to insert or array of values to set for multiple rows (NB, if providing multiple rows, they MUST all contain the same columns) or a statement to do INSERT INTO SELECT FROM
733 * @return array 0 => query, 1 => binds, 2 => columns
734 */
735 protected function _buildInsertQuery( $type, $table, $set )
736 {
737 $columns = NULL;
738
739 /* Is a statement? */
740 if ( $set instanceof \IPS\Db\Select )
741 {
742 $query = "{$type} INTO `{$this->prefix}{$table}` " . $set->query;
743 $binds = $set->binds;
744 }
745 else
746 {
747 /* Is this just one row? */
748 foreach ( $set as $k => $v )
749 {
750 if ( !is_array( $v ) )
751 {
752 $set = array( $set );
753 }
754 break;
755 }
756
757 /* Compile */
758 $columns = NULL;
759 $values = array();
760 $binds = array();
761 if ( count( $set ) )
762 {
763 foreach ( $set as $row )
764 {
765 if ( $columns === NULL )
766 {
767 $columns = array_map( function( $val ){ return "`{$val}`"; }, array_keys( $row ) );
768 }
769
770 $binds = array_merge( $binds, array_values( $row ) );
771 $values[] = '( ' . implode( ', ', array_fill( 0, count( $columns ), '?' ) ) . ' )';
772 }
773 }
774 else
775 {
776 $columns = array();
777 $values = array( '()' );
778 }
779
780 /* Construct query */
781 $query = "{$type} INTO `{$this->prefix}{$table}` ( " . implode( ', ', $columns ) . ' ) VALUES ' . implode( ', ', $values );
782 }
783
784 return array( 0 => $query, 1 => $binds, 2 => $columns );
785 }
786
787 /**
788 * Run UPDATE statement and return number of affected rows
789 *
790 * @see <a href='http://dev.mysql.com/doc/refman/5.1/en/update.html'>UPDATE Syntax</a>
791 * @param string|array $table Table Name, or array( Table Name => Identifier )
792 * @param string|array $set Values to set (keys should be the table columns) or pre-formatted SET clause or \IPS\Db\Select object
793 * @param mixed $where WHERE clause (see \IPS\Db::compileWhereClause for details)
794 * @param array $joins Tables to join
795 * @param int|array|null $limit LIMIT clause (see \IPS\Db::select for details)
796 * @param int $flags Bitwise flags
797 * @li \IPS\Db::LOW_PRIORITY Will use LOW_PRIORITY
798 * @li \IPS\Db::IGNORE Will use IGNORE
799 * @return int
800 * @throws \IPS\Db\Exception
801 */
802 public function update( $table, $set, $where='', $joins=array(), $limit=NULL, $flags=0 )
803 {
804 $binds = array();
805
806 /* Work out table */
807 $table = is_array( $table ) ? "`{$this->prefix}{$table[0]}` {$this->prefix}{$table[1]}" : "`{$this->prefix}{$table}` {$table}";
808
809 /* Work out joins */
810 $_joins = array();
811
812 foreach ( $joins as $join )
813 {
814 $type = ( isset( $join['type'] ) and in_array( \strtoupper( $join['type'] ), array( 'LEFT', 'INNER', 'RIGHT' ) ) ) ? \strtoupper( $join['type'] ) : 'LEFT';
815 $_table = is_array( $join['from'] ) ? "`{$this->prefix}{$join['from'][0]}` {$this->prefix}{$join['from'][1]}" : "`{$this->prefix}{$join['from']}` {$join['from']}";
816
817 $on = $this->compileWhereClause( $join['where'] );
818 $binds = array_merge( $binds, $on['binds'] );
819
820 $_joins[] = "{$type} JOIN {$_table} ON {$on['clause']}";
821 }
822 $joins = empty( $_joins ) ? '' : ( ' ' . implode( "\n", $_joins ) );
823
824 /* Work out SET clause */
825 if ( is_array( $set ) )
826 {
827 $_set = array();
828 foreach ( $set as $k => $v )
829 {
830 $_set[] = "`{$k}`=" . ( is_object( $v ) ? '(?)' : '?' );
831 $binds[] = $v;
832 }
833 $set = implode( ',', $_set );
834 }
835
836 /* Compile where clause */
837 if ( $where !== '' )
838 {
839 $_where = $this->compileWhereClause( $where );
840 $where = 'WHERE ' . $_where['clause'];
841 $binds = array_merge( $binds, $_where['binds'] );
842 }
843
844 /* Build query */
845 $query = 'UPDATE ';
846 if ( $flags & static::LOW_PRIORITY )
847 {
848 $query .= 'LOW_PRIORITY ';
849 }
850 if ( $flags & static::IGNORE )
851 {
852 $query .= 'IGNORE ';
853 }
854 $query .= "{$table} {$joins} SET {$set} {$where} ";
855
856 /* Limit */
857 if( $limit !== NULL )
858 {
859 $query .= $this->compileLimitClause( $limit );
860 }
861
862 /* Run it */
863 $return = $this->returnQuery;
864
865 $stmt = $this->preparedQuery( $query, $binds );
866
867 if( $return === TRUE )
868 {
869 return $stmt;
870 }
871
872 return $stmt->affected_rows;
873 }
874
875 /**
876 * Run DELETE statement and return number of affected rows
877 *
878 * @see <a href='http://dev.mysql.com/doc/refman/5.1/en/delete.html'>DELETE Syntax</a>
879 * @param string $table Table Name
880 * @param string|array|\IPS\Db\Statement|null $where WHERE clause (see \IPS\Db::compileWhereClause for details)
881 * @param string|null $order ORDER BY clause
882 * @param int|array|null $limit LIMIT clause (see \IPS\Db::select for details)
883 * @param string|null $statementColumn If \IPS\Db\Statement is passed, this is the name of the column that results are being loaded from
884 * @return \IPS\Db\Statement
885 * @throws \IPS\Db\Exception
886 */
887 public function delete( $table, $where=NULL, $order=NULL, $limit=NULL, $statementColumn=NULL )
888 {
889 /* TRUNCATE is faster, so use that if appropriate */
890 if ( $where === NULL and $limit === NULL )
891 {
892 $return = $this->returnQuery;
893
894 $stmt = $this->preparedQuery( "TRUNCATE `{$this->prefix}{$table}`", array() );
895
896 if( $return === TRUE )
897 {
898 return $stmt;
899 }
900
901 return $stmt->affected_rows;
902 }
903
904 /* Basic query */
905 $query = "DELETE FROM `{$this->prefix}{$table}`";
906
907 /* Is a statement? */
908 if ( $where instanceof \IPS\Db\Statement )
909 {
910 $query .= ' WHERE ' . $statementColumn . ' IN(' . $where->query . ')';
911 $binds = $where->binds;
912 }
913
914 /* Add where clause */
915 else
916 {
917 $binds = array();
918 if ( $where !== NULL )
919 {
920 $_where = $this->compileWhereClause( $where );
921 $query .= ' WHERE ' . $_where['clause'];
922 $binds = $_where['binds'];
923 }
924 }
925
926 /* Order? */
927 if( $order !== NULL )
928 {
929 $query .= ' ORDER BY ' . $order;
930 }
931
932 /* Limit */
933 if( $limit !== NULL )
934 {
935 $query .= $this->compileLimitClause( $limit );
936 }
937
938 /* Run it */
939 $return = $this->returnQuery;
940
941 $stmt = $this->preparedQuery( $query, $binds );
942
943 if( $return === TRUE )
944 {
945 return $stmt;
946 }
947
948 return $stmt->affected_rows;
949 }
950
951 /**
952 * Compile WHERE clause
953 *
954 * @code
955 // Single clause
956 "foo IS NOT NULL"
957 // Single clause with bound values (always bind values to ensure they are properly escaped)
958 array( 'foo=?', 'fooValue' )
959 array( 'foo=? OR bar=?', 'fooValue', 'barValue' )
960 // Multiple clauses (will be joined with AND) with bound values
961 array( array( 'foo=?, 'fooValue' ), array( 'bar=?', 'barValue' ) )
962 * @endcode
963 * @param string|array $data See examples
964 * @return array Array containing the WHERE clause and the values to be bound - array( 'clause' => '1=1', 'binds' => array() )
965 */
966 public function compileWhereClause( $data )
967 {
968 $return = array( 'clause' => '1=1', 'binds' => array() );
969
970 if( is_string( $data ) )
971 {
972 $return['clause'] = $data;
973 }
974 elseif ( is_array( $data ) and ! empty( $data ) )
975 {
976 if ( is_string( $data[0] ) )
977 {
978 $data = array( $data );
979 }
980
981 $clauses = array();
982 foreach ( $data as $bit )
983 {
984 if( !is_array( $bit ) )
985 {
986 $clauses[] = $bit;
987 }
988 else
989 {
990 $clause = array_shift( $bit );
991
992 $binds = $bit;
993 $i = 0;
994 foreach ( $binds as $k => $v )
995 {
996 $i++;
997 if ( $v === NULL )
998 {
999 $pos = 0;
1000 for ( $j=0; $j<$i; $j++ )
1001 {
1002 $pos = mb_strpos( $clause, '?', $pos ) + 1;
1003 }
1004
1005 if( mb_substr( $clause, $pos - 3, 3 ) == '!=?' )
1006 {
1007 $clause = mb_substr( $clause, 0, $pos - 3 ) . ' IS NOT NULL' . mb_substr( $clause, $pos );
1008 }
1009 else
1010 {
1011 $clause = mb_substr( $clause, 0, $pos - 2 ) . ' IS NULL' . mb_substr( $clause, $pos );
1012 }
1013
1014 $i--;
1015 unset( $binds[$k] );
1016 }
1017
1018 }
1019
1020 $clauses[] = $clause;
1021 $return['binds'] = array_merge( $return['binds'], $binds );
1022 }
1023 }
1024
1025 $return['clause'] = implode( ' AND ', $clauses );
1026 }
1027
1028 return $return;
1029 }
1030
1031 /**
1032 * Compile LIMIT clause
1033 *
1034 * @param int|array $data Rows to fetch or array( offset, limit )
1035 * @return string
1036 */
1037 public function compileLimitClause( $data )
1038 {
1039 $limit = NULL;
1040 if( is_array( $data ) )
1041 {
1042 $offset = intval( $data[0] );
1043 $limit = intval( $data[1] );
1044 }
1045 else
1046 {
1047 $offset = intval( $data );
1048 }
1049
1050 if( $limit !== NULL )
1051 {
1052 return " LIMIT {$offset},{$limit}";
1053 }
1054 else
1055 {
1056 return " LIMIT {$offset}";
1057 }
1058 }
1059
1060 /**
1061 * Compile column definition
1062 *
1063 * @code
1064 \IPS\Db::i()->compileColumnDefinition( array(
1065 'name' => 'column_name', // Column name
1066 'type' => 'VARCHAR', // Data type (do not specify length, etc. here)
1067 'length' => 255, // Length. May be required or optional depending on data type.
1068 'decimals' => 2, // Decimals. May be required or optional depending on data type.
1069 'values' => array( 0, 1 ), // Acceptable values. Required for ENUM and SET data types.
1070 'allow_null' => FALSE, // (Optional) Specifies whether or not NULL vavlues are allowed. Defaults to TRUE.
1071 'default' => 'Default Value', // (Optional) Default value
1072 'comment' => 'Column Comment', // (Optional) Column comment
1073 'unsigned' => TRUE, // (Optional) Will specify UNSIGNED for numeric types. Defaults to FALSE.
1074 'zerofill' => TRUE, // (Optional) Will specify ZEROFILL for numeric types. Defaults to FALSE.
1075 'auto_increment'=> TRUE, // (Optional) Will specify auto_increment. Defaults to FALSE.
1076 'binary' => TRUE, // (Optional) Will specify BINARY for TEXT types. Defaults to FALSE.
1077 'primary' => TRUE, // (Optional) Will specify PRIMARY KEY. Defaults to FALSE.
1078 'unqiue' => TRUE, // (Optional) Will specify UNIQUE. Defaults to FALSE.
1079 'key' => TRUE, // (Optional) Will specify KEY. Defaults to FALSE.
1080 ) );
1081 * @endcode
1082 * @see <a href='http://dev.mysql.com/doc/refman/5.1/en/create-table.html'>MySQL CREATE TABLE syntax</a>
1083 * @param array $data Column Data (see \IPS\Db::createTable for details)
1084 * @return string
1085 */
1086 public function compileColumnDefinition( $data )
1087 {
1088 /* Specify name and type */
1089 $definition = "`{$data['name']}` " . \strtoupper( $data['type'] ) . ' ';
1090
1091 /* Some types specify length */
1092 if(
1093 in_array( \strtoupper( $data['type'] ), array( 'VARCHAR', 'VARBINARY' ) )
1094 or
1095 (
1096 isset( $data['length'] ) and $data['length']
1097 and
1098 in_array( \strtoupper( $data['type'] ), array( 'BIT', 'TINYINT', 'SMALLINT', 'MEDIUMINT', 'INT', 'INTEGER', 'BIGINT', 'REAL', 'DOUBLE', 'FLOAT', 'DECIMAL', 'NUMERIC', 'CHAR', 'BINARY' ) )
1099 )
1100 ) {
1101 $definition .= "({$data['length']}";
1102
1103 /* And some of those specify decimals (which may or may not be optional) */
1104 if( in_array( \strtoupper( $data['type'] ), array( 'REAL', 'DOUBLE', 'FLOAT' ) ) or ( in_array( \strtoupper( $data['type'] ), array( 'DECIMAL', 'NUMERIC' ) ) and isset( $data['decimals'] ) ) )
1105 {
1106 $definition .= ',' . $data['decimals'];
1107 }
1108
1109 $definition .= ') ';
1110 }
1111
1112 /* Numeric types can be UNSIGNED and ZEROFILL */
1113 if( in_array( \strtoupper( $data['type'] ), array( 'TINYINT', 'SMALLINT', 'MEDIUMINT', 'INT', 'INTEGER', 'BIGINT', 'REAL', 'DOUBLE', 'FLOAT', 'DECIMAL', 'NUMERIC' ) ) )
1114 {
1115 if( isset( $data['unsigned'] ) and $data['unsigned'] === TRUE )
1116 {
1117 $definition .= 'UNSIGNED ';
1118 }
1119 if( isset( $data['zerofill'] ) and $data['zerofill'] === TRUE )
1120 {
1121 $definition .= 'ZEROFILL ';
1122 }
1123 }
1124
1125 /* ENUM and SETs have values */
1126 if( in_array( \strtoupper( $data['type'] ), array( 'ENUM', 'SET' ) ) )
1127 {
1128 $values = array();
1129 foreach ( $data['values'] as $v )
1130 {
1131 $values[] = "'{$this->escape_string( $v )}'";
1132 }
1133
1134 $definition .= '(' . implode( ',', $values ) . ') ';
1135 }
1136
1137 /* Some types can be binary or not */
1138 if( isset( $data['binary'] ) and $data['binary'] === TRUE and in_array( \strtoupper( $data['type'] ), array( 'CHAR', 'VARCHAR', 'TINYTEXT', 'TEXT', 'MEDIUMTEXT', 'LONGTEXT' ) ) )
1139 {
1140 $definition .= 'BINARY ';
1141 }
1142
1143 /* Text types specify a character set and collation */
1144 if( in_array( \strtoupper( $data['type'] ), array( 'CHAR', 'VARCHAR', 'TINYTEXT', 'TEXT', 'MEDIUMTEXT', 'LONGTEXT', 'ENUM', 'SET' ) ) )
1145 {
1146 $definition .= "CHARACTER SET {$this->charset} COLLATE {$this->collation} ";
1147 }
1148
1149 /* NULL? */
1150 if( isset( $data['allow_null'] ) and $data['allow_null'] === FALSE )
1151 {
1152 $definition .= 'NOT NULL ';
1153 }
1154 else
1155 {
1156 $definition .= 'NULL ';
1157 }
1158
1159 /* auto_increment? */
1160 if( isset( $data['auto_increment'] ) and $data['auto_increment'] === TRUE )
1161 {
1162 $definition .= 'AUTO_INCREMENT ';
1163 }
1164 else
1165 {
1166 /* Default value */
1167 if( isset( $data['default'] ) and !in_array( \strtoupper( $data['type'] ), array( 'TINYTEXT', 'TEXT', 'MEDIUMTEXT', 'LONGTEXT', 'BLOB', 'MEDIUMBLOB', 'BIGBLOB', 'LONGBLOB' ) ) )
1168 {
1169 if( $data['type'] == 'BIT' )
1170 {
1171 $definition .= "DEFAULT {$data['default']} ";
1172 }
1173 else
1174 {
1175 $defaultValue = in_array( \strtoupper( $data['type'] ), array( 'TINYINT', 'SMALLINT', 'MEDIUMINT', 'INT', 'INTEGER', 'BIGINT', 'REAL', 'DOUBLE', 'FLOAT', 'DECIMAL', 'NUMERIC' ) ) ? floatval( $data['default'] ) : ( ! in_array( $data['default'], array( 'CURRENT_TIMESTAMP', 'BIT' ) ) ? '\'' . $this->escape_string( $data['default'] ) . '\'' : $data['default'] );
1176 $definition .= "DEFAULT {$defaultValue} ";
1177 }
1178 }
1179 }
1180
1181 /* Index? */
1182 if( isset( $data['primary'] ) )
1183 {
1184 $definition .= 'PRIMARY KEY ';
1185 }
1186 elseif( isset( $data['unique'] ) )
1187 {
1188 $definition .= 'UNIQUE ';
1189 }
1190 if( isset( $data['key'] ) )
1191 {
1192 $definition .= 'KEY ';
1193 }
1194
1195 /* Comment */
1196 if( isset( $data['comment'] ) and ! empty( $data['comment'] ) )
1197 {
1198 $definition .= "COMMENT '{$this->escape_string( $data['comment'] )}'";
1199 }
1200
1201 /* Return */
1202 return $definition;
1203 }
1204
1205 /**
1206 * Compile index definition
1207 *
1208 * @code
1209 \IPS\Db::i()->compileIndexDefinition( array(
1210 'type' => 'key', // "primary", "unique", "fulltext" or "key"
1211 'name' => 'index_name', // Index name. Not required if type is "primary"
1212 'length' => 200, // Index length (used when taking part of a text field, for example)
1213 'columns' => array( 'column' ) // Columns to be in the index
1214 ) );
1215 * @endcode
1216 * @see <a href='http://dev.mysql.com/doc/refman/5.1/en/create-index.html'>MySQL CREATE INDEX syntax</a>
1217 * @see \IPS\Db::createTable
1218 * @param array $data Index Data (see \IPS\Db::createTable for details)
1219 * @return string
1220 */
1221 public function compileIndexDefinition( $data )
1222 {
1223 $definition = '';
1224
1225 /* Specify type */
1226 switch ( \strtolower( $data['type'] ) )
1227 {
1228 case 'primary':
1229 $definition .= 'PRIMARY KEY ';
1230 break;
1231
1232 case 'unique':
1233 $definition .= "UNIQUE KEY `{$data['name']}` ";
1234 break;
1235
1236 case 'fulltext':
1237 $definition .= "FULLTEXT KEY `{$data['name']}` ";
1238 break;
1239
1240 default:
1241 $definition .= "KEY `{$data['name']}` ";
1242 break;
1243 }
1244
1245 /* Specify columns */
1246 $definition .= '(' . implode( ',', array_map( function ( $val, $len )
1247 {
1248 return ( ! empty( $len ) ) ? "`{$val}`({$len})" : "`{$val}`";
1249 }, $data['columns'], ( ( isset( $data['length'] ) AND is_array( $data['length'] ) ) ? $data['length'] : array_fill( 0, count( $data['columns'] ), null ) ) ) ) . ')';
1250
1251 /* Return */
1252 return $definition;
1253 }
1254
1255 /**
1256 * Does table exist?
1257 *
1258 * @param string $name Table Name
1259 * @return bool
1260 */
1261 public function checkForTable( $name )
1262 {
1263 return ( $this->forceQuery( "SHOW TABLES LIKE '". $this->escape_string( "{$this->prefix}{$name}" ) . "'" )->num_rows > 0 );
1264 }
1265
1266 /**
1267 * Does column exist?
1268 *
1269 * @param string $name Table Name
1270 * @param string $column Column Name
1271 * @return bool
1272 */
1273 public function checkForColumn( $name, $column )
1274 {
1275 return ( $this->forceQuery( "SHOW COLUMNS FROM `". $this->escape_string( "{$this->prefix}{$name}" ) . "` LIKE '". $this->escape_string( $column ) . "'" )->num_rows > 0 );
1276 }
1277
1278 /**
1279 * Does index exist?
1280 *
1281 * @param string $name Table Name
1282 * @param string $index Index Name
1283 * @return bool
1284 */
1285 public function checkForIndex( $name, $index )
1286 {
1287 return ( $this->forceQuery( "SHOW INDEXES FROM `". $this->escape_string( "{$this->prefix}{$name}" ) . "` WHERE Key_name LIKE '". $this->escape_string( $index ) . "'" )->num_rows > 0 );
1288 }
1289
1290 /**
1291 * Create Table
1292 *
1293 * @code
1294 \IPS\Db::createTable( array(
1295 'name' => 'table_name', // Table name
1296 'columns' => array( ... ), // Column data - see \IPS\Db::compileColumnDefinition for details
1297 'indexes' => array( ... ), // (Optional) Index data - see \IPS\Db::compileIndexDefinition for details
1298 'comment' => '...', // (Optional) Table comment
1299 'engine' => 'MEMORY', // (Optional) Engine to use - will default to not specifying one, unless a FULLTEXT index is specified, in which case MyISAM is forced
1300 'temporary' => TRUE, // (Optional) Will sepcify CREATE TEMPORARY TABLE - defaults to FALSE
1301 'if_not_exists' => TRUE, // (Optional) Will sepcify CREATE TABLE name IF NOT EXISTS - defaults to FALSE
1302 ) );
1303 * @endcode
1304 * @param array $data Table Definition (see code sample for details)
1305 * @throws \IPS\Db\Exception
1306 * @return void|string
1307 */
1308 public function createTable( $data )
1309 {
1310 return $this->query( $this->_createTableQuery( $data ) );
1311 }
1312
1313 /**
1314 * Create copy of table structure
1315 *
1316 * @param string $table The table name
1317 * @param string $newTableName Name of table to create
1318 * @throws \IPS\Db\Exception
1319 * @return void|string
1320 */
1321 public function duplicateTableStructure( $table, $newTableName )
1322 {
1323 return $this->query( "CREATE TABLE `{$this->prefix}{$newTableName}` LIKE `{$this->prefix}{$table}`" );
1324 }
1325
1326 /**
1327 * Create Table Query
1328 *
1329 * @see <a href='http://dev.mysql.com/doc/refman/5.1/en/create-table.html'>MySQL CREATE TABLE syntax</a>
1330 * @see \IPS\Db::compileColumnDefinition
1331 * @see \IPS\Db::compileIndexDefinition
1332 * @param array $data Table Definition (see code sample for details)
1333 * @return string
1334 */
1335 public function _createTableQuery( $data )
1336 {
1337 $data = $this->updateDefinitionIndexLengths( $data );
1338 $mysqlVersion = \IPS\Db::i()->server_info;
1339
1340 /* Start with a basic CREATE TABLE */
1341 $query = 'CREATE ';
1342 if( isset( $data['temporary'] ) and $data['temporary'] === TRUE )
1343 {
1344 $query.= 'TEMPORARY ';
1345 }
1346 $query .= 'TABLE ';
1347 if( isset( $data['if_not_exists'] ) and $data['if_not_exists'] === TRUE )
1348 {
1349 $query.= 'IF NOT EXISTS ';
1350 }
1351
1352 /* Add in our create definition */
1353 $query .= "`{$this->prefix}{$data['name']}` (\n\t";
1354 $createDefinitons = array();
1355 foreach ( $data['columns'] as $field )
1356 {
1357 $createDefinitons[] = $this->compileColumnDefinition( $field );
1358 }
1359 if( isset( $data['indexes'] ) )
1360 {
1361 foreach ( $data['indexes'] as $index )
1362 {
1363 if( $index['type'] === 'fulltext' )
1364 {
1365 /* If this is a fulltext index, set engine to myisam but only if engine is something besides innodb or myisam OR the mysql version is less than 5.6 - in
1366 this case we assume the default engine is most likely either myisam or innodb */
1367 if( !$this->_innoDbSupportsFulltextIndexes() OR ( isset( $data['engine'] ) AND !in_array( \strtoupper( $data['engine'] ), array( 'INNODB', 'MYISAM' ) ) ) )
1368 {
1369 $data['engine'] = 'MYISAM';
1370 }
1371 }
1372
1373 $createDefinitons[] = $this->compileIndexDefinition( $index );
1374 }
1375 }
1376 $query .= implode( ",\n\t", $createDefinitons );
1377 $query .= "\n)\n";
1378
1379 /* Specifying a particular engine? */
1380 if( isset( $data['engine'] ) and $data['engine'] )
1381 {
1382 $query .= "ENGINE {$data['engine']} ";
1383 }
1384
1385 /* Specify UTF8 */
1386 $query .= "CHARACTER SET {$this->charset} COLLATE {$this->collation} ";
1387
1388 /* Add comment */
1389 if( isset( $data['comment'] ) )
1390 {
1391 $query .= "COMMENT '{$this->escape_string( $data['comment'] )}'";
1392 }
1393
1394 /* Return */
1395 return $query;
1396 }
1397
1398 /**
1399 * Rename table
1400 *
1401 * @see <a href='http://dev.mysql.com/doc/refman/5.1/en/rename-table.html'>Rename Table</a>
1402 * @param string $oldName The current table name
1403 * @param string $newName The new name
1404 * @return void
1405 * @see <a href='http://stackoverflow.com/questions/12856783/best-practice-with-mysql-innodb-to-rename-huge-table-when-table-with-same-name-a'>Renaming huge InnoDB tables</a>
1406 * @see <a href='http://www.percona.com/blog/2011/02/03/performance-problem-with-innodb-and-drop-table/'>Performance problem dropping huge InnoDB tables</a>
1407 * @note A race condition can occur sometimes with InnoDB + innodb_file_per_table so we can't drop then rename...see above links
1408 */
1409 public function renameTable( $oldName, $newName )
1410 {
1411 /* Find out if the table we are renaming *to* already exists */
1412 $cleanUp = FALSE;
1413 $query = "`{$this->prefix}{$this->escape_string( $oldName )}` TO `{$this->prefix}{$this->escape_string( $newName )}`";
1414
1415 if( $this->checkForTable( $newName ) )
1416 {
1417 $query = "`{$this->prefix}{$this->escape_string( $newName )}` TO `{$this->prefix}{$this->escape_string( $newName )}_DROP`, " . $query;
1418 $cleanUp = TRUE;
1419 }
1420
1421 $result = $this->query( "RENAME TABLE " . $query );
1422
1423 if( $cleanUp )
1424 {
1425 $this->dropTable( $newName . '_DROP', TRUE );
1426 }
1427
1428 return $result;
1429 }
1430
1431 /**
1432 * Alter Table
1433 * Can only update the comment and engine
1434 * @note This will not examine key lengths and adjust.
1435 *
1436 * @param string $table Table name
1437 * @param string|null $comment Table comment. NULL to not change
1438 * @param string|null $engine Engine to use. NULL to not change
1439 * @return void
1440 */
1441 public function alterTable( $table, $comment=NULL, $engine=NULL )
1442 {
1443 if ( $comment === NULL and $engine === NULL )
1444 {
1445 return;
1446 }
1447
1448 $query = "ALTER TABLE `{$this->prefix}{$this->escape_string( $table )}` ";
1449 if ( $comment !== NULL )
1450 {
1451 $query .= "COMMENT='{$this->escape_string( $comment )}' ";
1452 }
1453 if ( $engine !== NULL )
1454 {
1455 $query .= "ENGINE={$engine}";
1456 }
1457
1458 return $this->query( $query );
1459 }
1460
1461 /**
1462 * Find out the default storage engine
1463 *
1464 * @return string
1465 */
1466 public function defaultEngine()
1467 {
1468 $result = $this->forceQuery( "SHOW ENGINES" );
1469
1470 while( $engine = $result->fetch_assoc() )
1471 {
1472 if( \strtoupper( $engine['Support'] ) == 'DEFAULT' )
1473 {
1474 return $engine['Engine'];
1475 }
1476 }
1477
1478 return $this->_innoDbSupportsFulltextIndexes() ? 'InnoDB' : 'MyISAM';
1479 }
1480
1481 /**
1482 * Is InnoDB supported for fulltext indexes?
1483 *
1484 * @return bool
1485 */
1486 protected function _innoDbSupportsFulltextIndexes()
1487 {
1488 /* MariaDB supports fulltext for InnoDB on versions higher than 10.0.5 */
1489 if ( preg_match( '/^(\d*\.\d*(\.\d*)-)?(\d*\.\d*(\.\d*))-MariaDB/', $this->server_info, $matches ) )
1490 {
1491 $mariaVersion = $matches[3];
1492 return version_compare( $mariaVersion, '10.0.5', '>=' );
1493 }
1494
1495 /* Normal MySQL supports fulltext for InnoDB on versions higher than 5.6 */
1496 else
1497 {
1498 return $this->server_version >= 50600;
1499 }
1500 }
1501
1502 /**
1503 * Drop table
1504 *
1505 * @see <a href='http://dev.mysql.com/doc/refman/5.1/en/drop-table.html'>DROP TABLE Syntax</a>
1506 * @param string|array $table Table Name(s)
1507 * @param bool $ifExists Adds an "IF EXISTS" clause to the query
1508 * @param bool $temporary Table is temporary?
1509 * @return void
1510 */
1511 public function dropTable( $table, $ifExists=FALSE, $temporary=FALSE )
1512 {
1513 $prefix = $this->prefix;
1514
1515 return $this->query(
1516 'DROP '
1517 . ( $temporary ? 'TEMPORARY ' : '' )
1518 . 'TABLE '
1519 . ( $ifExists ? 'IF EXISTS ' :'' )
1520 . implode( ', ', array_map(
1521 function( $val ) use ( $prefix )
1522 {
1523 return '`' . $prefix . $val . '`';
1524 },
1525 ( is_array( $table ) ? $table : array( $table ) )
1526 ) )
1527 );
1528 }
1529
1530 /**
1531 * Get the table definition for an existing table
1532 *
1533 * @see \IPS\Db::createTable
1534 * @param string $table Table Name
1535 * @param boolean $columnsOnly Fetch columns only
1536 * @param boolean $getCollation Get column collations
1537 * @return array Table definition - see IPS\Db::createTable for details
1538 * @throws \OutOfRangeException
1539 * @throws \IPS\Db\Exception
1540 */
1541 public function getTableDefinition( $table, $columnsOnly=FALSE, $getCollation=FALSE )
1542 {
1543 /* Set name */
1544 $definition = array(
1545 'name' => $table,
1546 );
1547
1548 /* Fetch columns */
1549 if( !$this->checkForTable( $table ) )
1550 {
1551 throw new \OutOfRangeException;
1552 }
1553 $query = $this->forceQuery( "SHOW FULL COLUMNS FROM `{$this->prefix}" . $this->escape_string( $table ) . '`' );
1554 if ( $query->num_rows === 0 )
1555 {
1556 throw new \OutOfRangeException;
1557 }
1558 while ( $row = $query->fetch_assoc() )
1559 {
1560 /* Set basic information */
1561 $columnDefinition = array(
1562 'name' => $row['Field'],
1563 'type' => '',
1564 'length' => 0,
1565 'decimals' => NULL,
1566 'values' => array()
1567 );
1568
1569 if ( $getCollation and isset( $row['Collation'] ) )
1570 {
1571 $columnDefinition['collation'] = $row['Collation'];
1572 }
1573
1574 /* Parse the type */
1575 if( mb_strpos( $row['Type'], '(' ) !== FALSE )
1576 {
1577 /* First, we need to protect the enum options as they may have spaces before splitting */
1578 preg_match( '/(.+?)\((.+?)\)/', $row['Type'], $matches );
1579 $options = $matches[2];
1580 $type = preg_replace( '/(.+?)\((.+?)\)/', "$1(___TEMP___)", $row['Type'] );
1581 $typeInfo = explode( ' ', $type );
1582 $typeInfo[0] = str_replace( "___TEMP___", $options, $typeInfo[0] );
1583
1584 /* Now we match out the options */
1585 preg_match( '/(.+?)\((.+?)\)/', $typeInfo[0], $matches );
1586 $columnDefinition['type'] = mb_strtoupper( $matches[1] );
1587
1588 if( $columnDefinition['type'] === 'ENUM' or $columnDefinition['type'] === 'SET' )
1589 {
1590 preg_match_all( "/'(.*?)'/", $matches[2], $enum );
1591 $columnDefinition['values'] = $enum[1];
1592 }
1593 else
1594 {
1595 $lengthInfo = explode( ',', $matches[2] );
1596 $columnDefinition['length'] = intval( $lengthInfo[0] );
1597 if( isset( $lengthInfo[1] ) )
1598 {
1599 $columnDefinition['decimals'] = intval( $lengthInfo[1] );
1600 }
1601 }
1602 }
1603 else
1604 {
1605 $typeInfo = explode( ' ', $row['Type'] );
1606
1607 $columnDefinition['type'] = mb_strtoupper( $typeInfo[0] );
1608 $columnDefinition['length'] = 0;
1609 }
1610
1611 /* unsigned? */
1612 $columnDefinition['unsigned'] = in_array( 'unsigned', $typeInfo );
1613
1614 /* zerofill? */
1615 $columnDefinition['zerofill'] = in_array( 'zerofill', $typeInfo );
1616
1617 /* binary? */
1618 $columnDefinition['binary'] = ( $row['Collation'] === $this->binaryCollation );
1619
1620 /* Allow NULL? */
1621 $columnDefinition['allow_null'] = ( $row['Null'] === 'YES' );
1622
1623 /* Default value */
1624 $columnDefinition['default'] = $row['Default'];
1625 if ( $columnDefinition['default'] === NULL and !$columnDefinition['allow_null'] and mb_strpos( $row['Extra'], 'auto_increment' ) === FALSE )
1626 {
1627 $columnDefinition['default'] = '';
1628 }
1629
1630 /* auto_increment */
1631 $columnDefinition['auto_increment'] = mb_strpos( $row['Extra'], 'auto_increment' ) !== FALSE;
1632
1633 /* Comment */
1634 $columnDefinition['comment'] = $row['Comment'] ?: '';
1635
1636 /* Add it in the defintion */
1637 ksort( $columnDefinition );
1638 $definition['columns'][ $columnDefinition['name'] ] = $columnDefinition;
1639 }
1640
1641 if( !$columnsOnly )
1642 {
1643 /* Fetch indexes */
1644 $indexes = array();
1645 $query = $this->forceQuery( "SHOW INDEXES FROM `{$this->prefix}{$table}`" );
1646 while ( $row = $query->fetch_assoc() )
1647 {
1648 $length = ( isset( $row['Sub_part'] ) AND ! empty( $row['Sub_part'] ) ) ? intval( $row['Sub_part'] ) : null;
1649
1650 if( isset( $indexes[ $row['Key_name'] ] ) )
1651 {
1652 $indexes[ $row['Key_name'] ]['length'][] = $length;
1653 $indexes[ $row['Key_name'] ]['columns'][] = $row['Column_name'];
1654 }
1655 else
1656 {
1657 $type = 'key';
1658 if( $row['Key_name'] === 'PRIMARY' )
1659 {
1660 $type = 'primary';
1661 }
1662 elseif( $row['Index_type'] === 'FULLTEXT' )
1663 {
1664 $type = 'fulltext';
1665 }
1666 elseif( !$row['Non_unique'] )
1667 {
1668 $type = 'unique';
1669 }
1670
1671 $indexes[ $row['Key_name'] ] = array(
1672 'type' => $type,
1673 'name' => $row['Key_name'],
1674 'length' => array( $length ),
1675 'columns' => array( $row['Column_name'] )
1676 );
1677 }
1678 }
1679 $definition['indexes'] = $indexes;
1680
1681 /* Finally, get the table comment and engine */
1682 $row = $this->forceQuery( "SHOW TABLE STATUS LIKE '{$this->prefix}" . $this->escape_string( $table ) . "'" )->fetch_assoc();
1683
1684 if( $row['Comment'] )
1685 {
1686 $definition['comment'] = $row['Comment'];
1687 }
1688
1689 if( $row['Collation'] )
1690 {
1691 $definition['collation'] = $row['Collation'];
1692 }
1693
1694 if( $row['Engine'] )
1695 {
1696 $definition['engine'] = $row['Engine'];
1697 }
1698
1699 }
1700
1701 /* Return */
1702 return $definition;
1703 }
1704
1705 /**
1706 * Add column to table in database
1707 *
1708 * @see \IPS\Db::compileColumnDefinition
1709 * @param string $table Table name
1710 * @param array $definition Column Definition (see \IPS\Db::compileColumnDefinition for details)
1711 * @return void
1712 */
1713 public function addColumn( $table, $definition )
1714 {
1715 return $this->query( "ALTER TABLE `{$this->prefix}{$this->escape_string( $table )}` ADD COLUMN {$this->compileColumnDefinition( $definition )}" );
1716 }
1717
1718 /**
1719 * Modify an existing column
1720 *
1721 * @see \IPS\Db::compileColumnDefinition
1722 * @param string $table Table name
1723 * @param string $column Column name
1724 * @param array $definition New column definition (see \IPS\Db::compileColumnDefinition for details)
1725 * @return void
1726 */
1727 public function changeColumn( $table, $column, $definition )
1728 {
1729 return $this->query( "ALTER TABLE `{$this->prefix}{$this->escape_string( $table )}` CHANGE COLUMN `{$this->escape_string( $column )}` {$this->compileColumnDefinition( $definition )}" );
1730 }
1731
1732 /**
1733 * Drop a column
1734 *
1735 * @param string $table Table name
1736 * @param string|array $column Column name
1737 * @return void
1738 */
1739 public function dropColumn( $table, $column )
1740 {
1741 if( is_array( $column ) )
1742 {
1743 $drops = array();
1744
1745 foreach( $column as $_column )
1746 {
1747 $drops[] = "DROP COLUMN `{$this->escape_string( $_column )}`";
1748 }
1749
1750 $statement = implode( ", ", $drops );
1751 }
1752 else
1753 {
1754 $statement = "DROP COLUMN `{$this->escape_string( $column )}`";
1755 }
1756
1757 return $this->query( "ALTER TABLE `{$this->prefix}{$this->escape_string( $table )}` {$statement};" );
1758 }
1759
1760 /**
1761 * Add index to table in database
1762 *
1763 * @see \IPS\Db::compileIndexDefinition
1764 * @param string $table Table name
1765 * @param array $definition Index Definition (see \IPS\Db::compileIndexDefinition for details)
1766 * @param bool $discardDuplicates If adding a unique index, should duplicates be discarded? (If FALSE and there are any, an exception will be thrown)
1767 * @return void
1768 */
1769 public function addIndex( $table, $definition, $discardDuplicates=TRUE )
1770 {
1771 /* If it's a unique index, make sure there won't be any duplicates */
1772 if ( $discardDuplicates and in_array( $definition['type'], array( 'primary', 'unique' ) ) AND $this->returnQuery === FALSE )
1773 {
1774 $this->duplicateTableStructure( $table, "{$table}_temp" );
1775 $this->addIndex( "{$table}_temp", $definition, FALSE );
1776 $this->insert( "{$table}_temp", \IPS\Db::i()->select( '*', $table ), FALSE, TRUE );
1777 $this->dropTable( $table );
1778 $this->renameTable( "{$table}_temp", $table );
1779 }
1780 /* Otherwise just do it normally */
1781 else
1782 {
1783 return $this->query( "ALTER TABLE `{$this->prefix}{$this->escape_string( $table )}` {$this->buildIndex( $table, $definition )}" );
1784 }
1785 }
1786
1787 /**
1788 * Modify an existing index
1789 *
1790 * @see \IPS\Db::compileIndexDefinition
1791 * @param string $table Table name
1792 * @param string $index Index name
1793 * @param array $definition New index definition (see \IPS\Db::compileIndexDefinition for details)
1794 * @return void
1795 */
1796 public function changeIndex( $table, $index, $definition )
1797 {
1798 $returnQuery = $this->returnQuery;
1799 $return = NULL;
1800
1801 if( $this->checkForIndex( $table, $index ) )
1802 {
1803 $query = $this->dropIndex( $table, $index );
1804
1805 if( $returnQuery === TRUE )
1806 {
1807 $return = $query;
1808 }
1809 }
1810
1811 if ( $returnQuery )
1812 {
1813 $this->returnQuery = TRUE;
1814 }
1815
1816 $query = $this->addIndex( $table, $definition );
1817
1818 if( $returnQuery === TRUE )
1819 {
1820 $this->returnQuery = FALSE;
1821 $return .= $query;
1822
1823 return $return;
1824 }
1825
1826 return $query;
1827 }
1828
1829 /**
1830 * Build an index query for add/change
1831 *
1832 * @see \IPS\Db::compileIndexDefinition
1833 * @param string $table Table name
1834 * @param array $definition New index definition (see \IPS\Db::compileIndexDefinition for details)
1835 * @return void
1836 */
1837 public function buildIndex( $table, $definition )
1838 {
1839 $indexName = $definition['name'];
1840 $data = $this->getTableDefinition( $table, FALSE, TRUE );
1841 $engine = mb_strtolower( $data['engine'] );
1842
1843 /* Add the index to the table definition */
1844 $data['indexes'][ $indexName ] = $definition;
1845
1846 /* Reduce sub_part if required */
1847 $data = $this->updateDefinitionIndexLengths( $data );
1848 $return = '';
1849
1850 /* Do we need to adjust the engine because it's a fulltext index? */
1851 if( $engine !== mb_strtolower( $data['engine'] ) )
1852 {
1853 $return = "ENGINE={$data['engine']}, ";
1854 }
1855
1856 /* Extract the key we want to add */
1857 $definition = $data['indexes'][ $indexName ];
1858
1859 return $return . "ADD {$this->compileIndexDefinition( $definition )}";
1860 }
1861
1862 /**
1863 * Drop an index
1864 *
1865 * @param string $table Table name
1866 * @param string|array $index Column name
1867 * @return mixed
1868 */
1869 public function dropIndex( $table, $index )
1870 {
1871 $index = ( is_array( $index ) ) ? $index : array( $index );
1872
1873 $indexes = array();
1874
1875 if( \IPS\Db::i()->returnQuery )
1876 {
1877 foreach( $index as $key => $col )
1878 {
1879 if ( !$this->checkForIndex( $table, $col ) )
1880 {
1881 unset( $index[$key] );
1882 }
1883 }
1884 }
1885
1886 foreach( $index as $col )
1887 {
1888 $indexes[] = ( $col == 'PRIMARY KEY' ) ? "DROP " . $col : "DROP INDEX `" . $this->escape_string( $col ) . "`";
1889 }
1890
1891 $_index = implode( ', ', $indexes );
1892
1893
1894 try
1895 {
1896 $return = '';
1897
1898 if ( $_index )
1899 {
1900 $return = $this->query( "ALTER TABLE `{$this->prefix}{$this->escape_string( $table )}` {$_index};" );
1901 }
1902 else
1903 {
1904 /* Even if we do not run a query here, we need to reset this */
1905 \IPS\Db::i()->returnQuery = FALSE;
1906 }
1907
1908 return $return;
1909 }
1910 catch( \IPS\Db\Exception $e )
1911 {
1912 /* No need to stop here if index doesn't exist */
1913 if ( $e->getCode() !== 1091 )
1914 {
1915 throw $e;
1916 }
1917
1918 return 0;
1919 }
1920 }
1921
1922 /**
1923 * FIND_IN_SET
1924 * Generates a WHERE clause to determine if any value from a column containing a comma-delimined list matches any value from an array
1925 *
1926 * @param string $column Column name (which contains a comma-delimited list)
1927 * @param array $values Acceptable values
1928 * @param bool $reverse If true, will match cases where NO values from $column match any from $values
1929 * @return string Where clause
1930 * @see \IPS\Db::in() More efficient equivilant for columns that do not contain comma-delimited lists
1931 */
1932 public function findInSet( $column, $values, $reverse=FALSE )
1933 {
1934 $where = array();
1935
1936 foreach( $values as $i )
1937 {
1938 if ( $i != NULL and is_numeric( $i ) )
1939 {
1940 $where[] = ( $reverse ? 'NOT ' : '' ) . "FIND_IN_SET(" . $i . "," . $column . ")";
1941 }
1942 else if ( $i != NULL and is_string( $i ) )
1943 {
1944 $where[] = ( $reverse ? 'NOT ' : '' ) . "FIND_IN_SET('" . $this->real_escape_string( $i ) . "'," . $column . ")";
1945 }
1946 }
1947
1948 $statement = $reverse ? 'AND' : 'OR';
1949
1950 if ( ! empty( $where ) )
1951 {
1952 return '( ' . implode( " {$statement} ", $where ) . ' )';
1953 }
1954 else
1955 {
1956 return $reverse ? '1=1' : '1=0';
1957 }
1958 }
1959
1960 /**
1961 * IN
1962 * Generates a WHERE clause to determine if the value of a column matches any value from an array
1963 *
1964 * @param string $column Column name
1965 * @param array $values Acceptable values
1966 * @param bool $reverse If true, will match cases where $column does NOT match $values
1967 * @return string Where clause
1968 * @see \IPS\Db::findInSet() For columns that contain comma-delimited lists
1969 */
1970 public function in( $column, $values, $reverse=FALSE )
1971 {
1972 $in = array();
1973
1974 if( !is_array( $values ) )
1975 {
1976 $values = array( $values );
1977 }
1978
1979 foreach( $values as $i )
1980 {
1981 /* We must use the !== comparison so that 0 is not treated the same as NULL */
1982 if ( $i !== NULL and is_numeric( $i ) and ( is_int( $i ) or is_float( $i ) ) )
1983 {
1984 $in[] = $i;
1985 }
1986 else if ( $i != NULL and is_string( $i ) )
1987 {
1988 $in[] = "'" . $this->real_escape_string( $i ) . "'";
1989 }
1990 }
1991
1992 $return = array();
1993
1994 if ( ! empty( $in ) )
1995 {
1996 $return[] = $column . ( $reverse ? ' NOT' : '' ) . ' IN(' . implode( ',', $in ) . ')';
1997 }
1998
1999 if ( count( $return ) )
2000 {
2001 return '( ' . implode( ' OR ', $return ) . ' )';
2002 }
2003 else
2004 {
2005 return $reverse ? '1=1' : '1=0';
2006 }
2007 }
2008
2009 /**
2010 * Bitwise WHERE clause
2011 *
2012 * @param array $definition Bitwise keys as defined by the class
2013 * @param string $key The key to check for
2014 * @param bool $value Value to check for
2015 * @return string
2016 * @throws \InvalidArgumentException
2017 */
2018 public function bitwiseWhere( $definition, $key, $value=TRUE )
2019 {
2020 $operator = $value ? '& ' : '& ~';
2021 foreach ( $definition as $column => $keys )
2022 {
2023 if ( isset( $keys[ $key ] ) )
2024 {
2025 return "(`{$column}` {$operator}{$keys[ $key ]} ) != 0";
2026 }
2027 }
2028
2029 throw new \InvalidArgumentException;
2030 }
2031
2032 /**
2033 * Strip index lengths in the schema definitions - useful for a better comparison of the definitions
2034 * since different engines and charsets require different storage. Also, strip engine and collation.
2035 *
2036 * @param array|string $data Table definition (array) or table name (string)
2037 * @return array
2038 */
2039 public function normalizeDefinition( $data )
2040 {
2041 $definition = ( is_array( $data ) ) ? $data : $this->getTableDefinition( $data, FALSE, TRUE );
2042
2043 if ( isset( $definition['indexes'] ) )
2044 {
2045 foreach( $definition['indexes'] as $key => &$index )
2046 {
2047 /* Make sure the keys are in the correct order otherwise normal variances trigger differences just because 'columns' can come before 'length', etc */
2048 ksort( $index );
2049
2050 if( isset( $index['length'] ) )
2051 {
2052 foreach( $index['length'] as $_key => $length )
2053 {
2054 $definition['indexes'][ $key ]['length'][ $_key ] = null;
2055 }
2056 }
2057 }
2058 }
2059
2060 if( isset( $definition['collation'] ) )
2061 {
2062 unset( $definition['collation'] );
2063 }
2064
2065 if( isset( $definition['engine'] ) )
2066 {
2067 unset( $definition['engine'] );
2068 }
2069
2070 /* Prevent conflicts when schema says DEFAULT '0' but it is DEFAULT 0 and an INT type column as this is always set as a 0 anyway */
2071 foreach( $definition['columns'] as $name => $data )
2072 {
2073 if ( in_array( \strtoupper( $data['type'] ), array_keys( static::$dataTypes['database_column_type_numeric'] ) ) and ( ! in_array( \strtoupper( $data['type'] ), array( 'DECIMAL', 'FLOAT', 'BIT' ) ) ) and is_numeric( $data['default'] ) )
2074 {
2075 $definition['columns'][ $name ]['default'] = intval( $data['default'] );
2076 }
2077 }
2078
2079 return $definition;
2080 }
2081
2082 /**
2083 * Attempt to fix issues with keys longer than maximum allowed by DB engine
2084 * which is 1000 bytes for MyISAM and 767 for InnoDB taking into consideration the
2085 * multiplier (4 bytes per character for utf8mb4 and 3 bytes per character for UTF8)
2086 *
2087 * @param array|string $data Table definition (array) or table name (string)
2088 * @return array
2089 */
2090 public function updateDefinitionIndexLengths( $data )
2091 {
2092 $definition = ( is_array( $data ) ) ? $data : $this->getTableDefinition( $data, FALSE, TRUE );
2093 $length = 0;
2094 $multiplier = ( $this->charset === 'utf8mb4' ) ? 4 : 3;
2095 $needsFixing = array();
2096 $maxLen = 1000;
2097
2098 if ( ( ! isset( $definition['engine'] ) OR mb_strtolower( $definition['engine'] ) == 'innodb' ) and isset( $definition['indexes'] ) )
2099 {
2100 $definition['engine'] = $this->defaultEngine();
2101
2102 /* Any FULLTEXT fields? */
2103 foreach( $definition['indexes'] as $key => $data )
2104 {
2105 if ( $data['type'] === 'fulltext' )
2106 {
2107 /* If this is a fulltext index, set engine to myisam but only if engine is something besides innodb or myisam OR the mysql version is less than 5.6 - in
2108 this case we assume the default engine is most likely either myisam or innodb */
2109 if( !$this->_innoDbSupportsFulltextIndexes() OR ( isset( $definition['engine'] ) AND !in_array( \strtoupper( $definition['engine'] ), array( 'INNODB', 'MYISAM' ) ) ) )
2110 {
2111 $definition['engine'] = 'myisam';
2112 }
2113 }
2114 }
2115 }
2116
2117 if ( \mb_strtolower( $definition['engine'] ) === 'innodb' )
2118 {
2119 $maxLen = 767;
2120 }
2121
2122 if ( isset( $definition['indexes'] ) )
2123 {
2124 foreach( $definition['indexes'] as $key => $index )
2125 {
2126 $thisLength = null;
2127 $hasText = false;
2128
2129 foreach( $index['columns'] as $i => $column )
2130 {
2131 $thisLength = ( isset( $index['length'][ $i ] ) ) ? $index['length'][ $i ] : ( ( (int) $definition['columns'][ $column ]['length'] or empty( $definition['columns'][ $column ]['length'] ) ) ? $definition['columns'][ $column ]['length'] : 250 );
2132
2133 $isText = in_array( mb_strtolower( $definition['columns'][ $column ]['type'] ), array( 'mediumtext', 'text' ) );
2134
2135 if ( $hasText === false and $isText === true )
2136 {
2137 $hasText = true;
2138 }
2139
2140 if ( isset( $definition['columns'][ $column ] ) and ( ( ! empty( $thisLength ) or $isText ) ) )
2141 {
2142 $length += $thisLength;
2143 }
2144
2145 /* Is this an MB4 column */
2146 if ( $multiplier === 3 AND isset( $definition['columns'][ $column ]['collation'] ) )
2147 {
2148 if ( mb_substr( $definition['columns'][ $column ]['collation'], 0, 7 ) === 'utf8mb4' )
2149 {
2150 $multiplier = 4;
2151 }
2152 }
2153 }
2154
2155 if ( ( $length * $multiplier > $maxLen ) or $hasText )
2156 {
2157 foreach( $index['columns'] as $i => $column )
2158 {
2159 $thisLength = ( isset( $index['length'][ $i ] ) ) ? $index['length'][ $i ] : ( (int) $definition['columns'][ $column ]['length'] ? $definition['columns'][ $column ]['length'] : 250 );
2160
2161 if ( isset( $definition['columns'][ $column ] ) and ( ( ! empty( $thisLength ) or in_array( mb_strtolower( $definition['columns'][ $column ]['type'] ), array( 'mediumtext', 'text' ) ) ) ) )
2162 {
2163 /* Column name, column length, column type */
2164 $needsFixing[ $key ][ $i ] = array( $column, $thisLength, $definition['columns'][ $column ]['type'] );
2165 }
2166 }
2167 }
2168
2169 $length = 0;
2170 }
2171 }
2172
2173 if ( count( $needsFixing ) )
2174 {
2175 foreach( $needsFixing as $key => $i )
2176 {
2177 $totalLength = 0;
2178 $maxChars = $maxLen / $multiplier;
2179
2180 foreach( $i as $vals )
2181 {
2182 $totalLength += $vals[1];
2183 }
2184
2185 if ( $totalLength > $maxChars )
2186 {
2187 /* Check each column can be reduced by the amount we need reducing */
2188 $debt = 0;
2189
2190 $reduceEachBy = ( ( 100 / $totalLength ) * $maxChars) / 100;
2191
2192 /* Apply debt if we have any. We do not reduce integers */
2193 foreach( $i as $x => $vals )
2194 {
2195 if ( in_array( mb_strtoupper( $vals[2] ), array_keys( static::$dataTypes['database_column_type_numeric'] ) ) )
2196 {
2197 $debt += $vals[1];
2198 }
2199 }
2200
2201 /* Recalculate value to multiply index sub lengths with (subtracting debt) */
2202 if ( $debt < $totalLength )
2203 {
2204 $reduceEachBy = ( ( 100 / ($totalLength - $debt) ) * ( $maxChars - $debt ) ) / 100;
2205 }
2206
2207 foreach( $i as $x => $vals )
2208 {
2209 /* No length? */
2210 if ( empty( $vals[1] ) )
2211 {
2212 $vals[1] = 250;
2213 }
2214
2215 if ( in_array( mb_strtoupper( $vals[2] ), array_keys( static::$dataTypes['database_column_type_numeric'] ) ) )
2216 {
2217 /* Preserve col len where possible but if the column length is greater than subpart allowed, NULL the length
2218 otherwise MySQL will complain as you cannot use subpart on non-string column. */
2219 if ( $vals[1] > floor( $maxLen / $multiplier ) )
2220 {
2221 $vals[1] = NULL;
2222 $i[ $x ] = $vals;
2223 }
2224
2225 continue;
2226 }
2227
2228 $vals[1] = floor( $vals[1] * $reduceEachBy );
2229 $i[ $x ] = $vals;
2230 }
2231 }
2232
2233 foreach( $i as $x => $vals )
2234 {
2235 if ( $definition['columns'][ $definition['indexes'][ $key ]['columns'][ $x ] ]['length'] != $vals[1] )
2236 {
2237 $definition['indexes'][ $key ]['length'][ $x ] = intval( $vals[1] );
2238 }
2239 else
2240 {
2241 $definition['indexes'][ $key ]['length'][ $x ] = NULL;
2242 }
2243 }
2244 }
2245 }
2246
2247 return $definition;
2248 }
2249
2250 /**
2251 * Create database
2252 *
2253 * @param string $name Database Name
2254 * @return bool
2255 */
2256 public function createDatabase( $name )
2257 {
2258 return ( $this->query( "CREATE DATABASE ". $this->escape_string( "{$name}" ) ) );
2259 }
2260
2261 /**
2262 * Strip comments from a .sql file
2263 *
2264 * @param string $contents Contents from SQL file
2265 * @return string
2266 */
2267 public static function stripComments( $contents )
2268 {
2269 $contents = preg_replace( '/\/\*.+?\*\//', '', $contents );
2270 $contents = preg_replace( '/#.*/', '', $contents );
2271 $contents = preg_replace( '/--.*/', '', $contents );
2272 $contents = trim( $contents );
2273
2274 return $contents;
2275 }
2276
2277 /**
2278 * Replace binds in a prepared query to get the "full" query
2279 *
2280 * @param string $query Query
2281 * @param array $binds Any binds in the query
2282 * @return string
2283 */
2284 public static function _replaceBinds( $query, $binds )
2285 {
2286 /* Replace ?s with the actual values */
2287 if( is_array( $binds ) AND count( $binds ) )
2288 {
2289 foreach ( $binds as $b )
2290 {
2291 $query = preg_replace( '/\?/', var_export( $b, TRUE ), $query, 1 );
2292 }
2293 }
2294
2295 return $query;
2296 }
2297}