· 6 years ago · Nov 29, 2019, 11:26 PM
1<?php
2/**
3 * @brief Database Class
4 * @author <a href='https://www.invisioncommunity.com'>Invision Power Services, Inc.</a>
5 * @copyright (c) Invision Power Services, Inc.
6 * @license https://www.invisioncommunity.com/legal/standards/
7 * @package Invision Community
8 * @since 18 Feb 2013
9 */
10
11namespace IPSUtf8;
12
13/**
14 * @brief Database Class
15 * @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.
16 */
17class Db extends \mysqli
18{
19 /**
20 * @brief Datatypes
21 */
22 public static $dataTypes = array(
23 'database_column_type_numeric' => array(
24 'TINYINT' => 'TINYINT [Ā±127 ā» 255] [1B]',
25 'SMALLINT' => 'SMALLINT [Ā±3.3e4 ā» 6.6e4] [2B]',
26 'MEDIUMINT' => 'MEDIUMINT [Ā±8.4e6 ā» 1.7e7] [3B]',
27 'INT' => 'INT [Ā±2.1e9 ā» 4.3e9] [4B]',
28 'BIGINT' => 'BIGINT [Ā±9.2e18 ā» 1.8e19] [8B]',
29 'DECIMAL' => 'DECIMAL',
30 'FLOAT' => 'FLOAT',
31 'BIT' => 'BIT',
32
33 ),
34 'database_column_type_datetime' => array(
35 'DATE' => 'DATE',
36 'DATETIME' => 'DATETIME',
37 'TIMESTAMP' => 'TIMESTAMP',
38 'TIME' => 'TIME',
39 'YEAR' => 'YEAR',
40 ),
41 'database_column_type_string' => array(
42 'CHAR' => 'CHAR [Mā‰¤6.6e4] [(M*w)B]',
43 'VARCHAR' => 'VARCHAR [Mā‰¤6.6e4] [(L+(1āØ2))B]',
44 'TINYTEXT' => 'TINYTEXT [256B] [(L+1)B]',
45 'TEXT' => 'TEXT [64kB] [(L+2)B]',
46 'MEDIUMTEXT'=> 'MEDIUMTEXT [16MB] [(L+3)B]',
47 'LONGTEXT' => 'LONGTEXT [4GB] [(L+4)B]',
48 'BINARY' => 'BINARY [Mā‰¤6.6e4] [(M)B]',
49 'VARBINARY' => 'VARBINARY [Mā‰¤6.6e4] [(L+(1āØ2))B]',
50 'TINYBLOB' => 'TINYBLOB [256B] [(L+1)B]',
51 'BLOB' => 'BLOB [64kB] [(L+2)B]',
52 'MEDIUMBLOB'=> 'MEDIUMBLOB [16MB] [(L+3)B]',
53 'BIGBLOB' => 'BIGBLOB [4GB] [(L+4)B]',
54 'ENUM' => 'ENUM [6.6e4] [(1āØ2)B]',
55 'SET' => 'SET [64] [(1āØ2āØ3āØ4āØ8)B]',
56 )
57 );
58
59 /**
60 * @brief Multiton Store
61 */
62 protected static $multitons;
63
64 /**
65 * @brief Return Query
66 */
67 public $returnQuery = FALSE;
68
69 /**
70 * Get instance
71 *
72 * @param mixed $identifier Identifier
73 * @param array $connectionSettings Connection settings (use when initiating a new connection)
74 * @return IPSUtf8\Db
75 */
76 public static function i( $identifier=NULL, $connectionSettings=array() )
77 {
78 /* Did we pass a null value? */
79 $identifier = ( $identifier === NULL ) ? '__MAIN' : $identifier;
80
81 /* Don't have an instance? */
82 if( !isset( self::$multitons[ $identifier ] ) )
83 {
84 /* Load the default settings if necessary */
85 if ( $identifier === '__MAIN' OR $identifier == 'utf8' )
86 {
87 require( ROOT_PATH . '/conf_global.php' );
88 $connectionSettings = $INFO;
89 }
90
91 if ( isset( $connectionSettings['sql_tbl_prefix'] ) )
92 {
93 $connectionSettings['sql_tbl_prefix'] = str_replace( 'x_utf_', '', $connectionSettings['sql_tbl_prefix'] );
94 }
95
96 /* UTF8 Connection settings */
97 if ( mb_substr( $identifier, 0, 4 ) == 'utf8' )
98 {
99 $connectionSettings['sql_tbl_prefix'] = 'x_utf_' . $connectionSettings['sql_tbl_prefix'];
100 }
101
102 /* Connect */
103 $classname = \get_called_class();
104 self::$multitons[ $identifier ] = @new $classname(
105 $connectionSettings['sql_host'],
106 $connectionSettings['sql_user'],
107 $connectionSettings['sql_pass'],
108 $connectionSettings['sql_database'],
109 ( isset( $connectionSettings['sql_port'] ) and $connectionSettings['sql_port']) ? $connectionSettings['sql_port'] : NULL,
110 ( isset( $connectionSettings['sql_socket'] ) and $connectionSettings['sql_socket'] ) ? $connectionSettings['sql_socket'] : NULL
111 );
112
113 /* If the connection failed, throw an exception */
114 if( $error = mysqli_connect_error() )
115 {
116 throw new \IPSUtf8\Db\Exception( $error, self::$multitons[ $identifier ]->connect_errno );
117 }
118
119 /* If we succeeded, set the charset */
120 if ( mb_substr( $identifier, 0, 4 ) == 'utf8' )
121 {
122 /* UTF8MB4? */
123 if ( isset( $connectionSettings['sql_utf8mb4'] ) and $connectionSettings['sql_utf8mb4'] )
124 {
125 self::$multitons[ $identifier ]->charset = 'utf8mb4';
126 self::$multitons[ $identifier ]->collation = 'utf8mb4_unicode_ci';
127 self::$multitons[ $identifier ]->binaryCollation = 'utf8mb4_bin';
128 }
129
130 /* If we succeeded, set the charset */
131 if ( !self::$multitons[ $identifier ]->set_charset( self::$multitons[ $identifier ]->charset ) )
132 {
133 /* Fallback to UTF8 */
134 self::$multitons[ $identifier ]->charset = 'utf8';
135 self::$multitons[ $identifier ]->collation = 'utf8_unicode_ci';
136 self::$multitons[ $identifier ]->binaryCollation = 'utf8_bin';
137
138 self::$multitons[ $identifier ]->set_charset( 'utf8' );
139 }
140 }
141
142 /* Set the prefix */
143 if ( isset( $connectionSettings['sql_tbl_prefix'] ) )
144 {
145 self::$multitons[ $identifier ]->prefix = $connectionSettings['sql_tbl_prefix'];
146 }
147 }
148
149 /* Return */
150 return self::$multitons[ $identifier ];
151 }
152
153 /**
154 * @brief Charset
155 */
156 protected $charset = 'utf8';
157
158 /**
159 * @brief Collation
160 */
161 protected $collation = 'utf8_unicode_ci';
162
163 /**
164 * @brief Binary Collation
165 */
166 protected $binaryCollation = 'utf8_bin';
167
168 /**
169 * @brief Default MySQL Engine
170 */
171 protected $defaultEngine = NULL;
172
173 /**
174 * @brief Table Prefix
175 */
176 public $prefix = '';
177
178 /**
179 * Run a query
180 *
181 * @param string $query The query
182 * @return mixed
183 * @see <a href="http://uk1.php.net/manual/en/mysqli.query.php">mysqli::query</a>
184 * @throws \IPS\Db\Exception
185 */
186 public function query( $query )
187 {
188 /* Should we return the query instead of executing it? */
189 if( $this->returnQuery === TRUE )
190 {
191 $this->returnQuery = FALSE;
192 return $query;
193 }
194
195 $return = parent::query( $query );
196 if ( $return === FALSE )
197 {
198 throw new \IPSUtf8\Db\Exception( $this->error, $this->errno );
199 }
200 return $return;
201 }
202
203 /**
204 * Apparently, get_charset can be unavailable
205 *
206 * @return string
207 */
208 public function getCharset()
209 {
210 if ( method_exists( $this, 'get_charset' ) )
211 {
212 return static::get_charset()->charset;
213 }
214 else
215 {
216 return static::character_set_name();
217 }
218 }
219
220 /**
221 * Overload this method so we can change the charset if required
222 *
223 * @param string Charset
224 */
225 public function set_charset( $charset )
226 {
227 if ( mb_substr( $charset, 0, 4 ) == 'utf8' )
228 {
229 if ( $charset === 'utf8mb4' )
230 {
231 $this->charset = 'utf8mb4';
232 $this->collation = 'utf8mb4_unicode_ci';
233 $this->binaryCollation = 'utf8mb4_bin';
234 }
235 else
236 {
237 $this->charset = 'utf8';
238 $this->collation = 'utf8_unicode_ci';
239 $this->binaryCollation = 'utf8_bin';
240 }
241 }
242
243 return parent::set_charset( $charset );
244 }
245
246 /**
247 * Run Prepared SQL Statement
248 *
249 * @param string $query SQL Statement
250 * @param array $_binds Variables to bind
251 * @return \mysqli_stmt
252 */
253 public function preparedQuery( $query, array $_binds )
254 {
255 /* Init Bind object */
256 $bind = new Db\Bind();
257
258 /* Sort out subqueries */
259 $binds = array();
260 $i = 0;
261 foreach ( $_binds as $bindVal )
262 {
263 $i++;
264 if ( $bindVal instanceof \IPSUtf8\Db\Select )
265 {
266 $pos = 0;
267 for ( $j=0; $j<$i; $j++ )
268 {
269 $pos = mb_strpos( $query, '?', $pos ) + 1;
270 }
271 $query = mb_substr( $query, 0, $pos - 1 ) . $bindVal->query . mb_substr( $query, $pos );
272 $i--;
273
274 foreach ( $bindVal->binds as $v )
275 {
276 $binds[] = $v;
277 }
278 }
279 else
280 {
281 $binds[] = $bindVal;
282 }
283 }
284
285 /* Loop values to bind */
286 $i = 0;
287 foreach ( $binds as $bindVal )
288 {
289 $i++;
290 switch ( \gettype( $bindVal ) )
291 {
292 case 'boolean':
293 case 'integer':
294 $bind->add( 'i', $bindVal );
295 break;
296
297 case 'double':
298 $bind->add( 'd', $bindVal );
299 break;
300
301 case 'string':
302 $bind->add( 's', $bindVal );
303 break;
304
305 case 'object':
306 if( method_exists( $bindVal, '__toString' ) )
307 {
308 $bind->add( 's', (string) $bindVal );
309 break;
310 }
311 // Deliberately no break
312
313 case 'NULL':
314 case 'array':
315 case 'resource':
316 case 'unknown type':
317 default:
318 /* For NULL values, you can't bind, so we adjust the query to actually pass a NULL value */
319 $pos = 0;
320 for ( $j=0; $j<$i; $j++ )
321 {
322 $pos = mb_strpos( $query, '?', $pos ) + 1;
323 }
324 $query = mb_substr( $query, 0, $pos - 1 ) . 'NULL' . mb_substr( $query, $pos );
325 $i--;
326 break;
327 }
328 }
329
330 /* Add a backtrace to the query so we know where it came from if it causes issues */
331 $comment = '??';
332 $line = '?';
333 foreach( debug_backtrace( FALSE ) as $b )
334 {
335 if ( isset( $b['line'] ) )
336 {
337 $line = $b['line'];
338 }
339
340 if( isset( $b['class'] ) and $b['class'] !== 'IPSUtf8\_Db' )
341 {
342 $comment = "{$b['class']}::{$b['function']}:{$line}";
343 break;
344 }
345 }
346 $_query = $query;
347 $query = "/*{$comment}*/ {$query}";
348
349 /* Prepare */
350 $stmt = parent::prepare( $query );
351 if( $stmt === FALSE )
352 {
353 throw new \IPSUtf8\Db\Exception( $this->error, $this->errno, NULL, $_query, $binds );
354 }
355
356 /* Bind values */
357 if( $bind->haveBinds() === TRUE )
358 {
359 $stmt->bind_param( ...$bind->get() );
360 }
361
362 /* Execute */
363 $stmt->execute();
364 if ( $stmt->error )
365 {
366 throw new \IPSUtf8\Db\Exception( $stmt->error, $stmt->errno, NULL, $_query, $binds );
367 }
368 $stmt->store_result();
369
370 /* Return a Statement object */
371 return $stmt;
372 }
373
374 const SELECT_DISTINCT = 1;
375 const SELECT_SQL_CALC_FOUND_ROWS = 2;
376
377 /**
378 * Build SELECT statement
379 *
380 * @param array|string $columns The columns (as an array) to select or an expression
381 * @param array|string $table The table to select from. Either (string) table_name or (array) ( name, alias )
382 * @param array|string|NULL $where WHERE clause (see example)
383 * @param string|NULL $order ORDER BY clause
384 * @param array|int $limit Rows to fetch or array( offset, limit )
385 * @param string|NULL $group Column to GROUP BY
386 * @param array|string|NULL $having HAVING clause (same format as WHERE clause)
387 * @param int $flags Bitwise flags
388 * @li \IPSUtf8\Db::SELECT_DISTINCT Will use SELECT DISTINCT
389 * @li \IPSUtf8\DB::SELECT_SQL_CALC_FOUND_ROWS Will add SQL_CALC_FOUND_ROWS
390 * @return \IPSUtf8\Db\Select
391 *
392 */
393 public function select( $columns=NULL, $table, $where=NULL, $order=NULL, $limit=NULL, $group=NULL, $having=NULL, $flags=0 )
394 {
395 $binds = array();
396 $query = 'SELECT ';
397
398 /* Flags */
399 if ( $flags & static::SELECT_DISTINCT )
400 {
401 $query .= 'DISTINCT ';
402 }
403 if ( $flags & static::SELECT_SQL_CALC_FOUND_ROWS )
404 {
405 $query .= 'SQL_CALC_FOUND_ROWS ';
406 }
407
408 /* Columns */
409 if ( \is_string( $columns ) )
410 {
411 $query .= $columns;
412 }
413 else
414 {
415 $query .= implode( ', ', array_map( function( $col )
416 {
417 return '`' . $col . '`';
418 }, $columns ) );
419 }
420
421 /* Tables */
422 if ( \is_array( $table ) )
423 {
424 $query .= " FROM `{$this->prefix}{$table[0]}` AS `{$table[1]}`";
425 }
426 else
427 {
428 $query .= " FROM `{$this->prefix}{$table}` AS `{$table}`";
429 }
430
431 /* WHERE */
432 if ( $where )
433 {
434 $where = $this->compileWhereClause( $where );
435 $query .= ' WHERE ' . $where['clause'];
436 $binds = $where['binds'];
437 }
438
439 /* Group? */
440 if( $group )
441 {
442 $query .= " GROUP BY `{$group}`";
443 }
444
445 /* Having? */
446 if( $having )
447 {
448 $having = $this->compileWhereClause( $having );
449 $query .= ' HAVING ' . $having['clause'];
450 $binds = array_merge( $binds, $having['binds'] );
451 }
452
453 /* Order? */
454 if( $order )
455 {
456 $query .= ' ORDER BY ' . $order;
457 }
458
459 /* Limit */
460 if( $limit )
461 {
462 $query .= $this->compileLimitClause( $limit );
463 }
464
465 /* Return */
466 return new \IPSUtf8\Db\Select( $query, $binds, $this );
467 }
468
469 /**
470 * Build UNION statement
471 *
472 * @param array $selects Array of \IPSUtf8\Db\Select objects
473 * @param string|NULL $order ORDER BY clause
474 * @param array|int $limit Rows to fetch or array( offset, limit )
475 * @return \IPSUtf8\Db|Select
476 */
477 public function union( $selects, $order, $limit )
478 {
479 /* Combine selects */
480 $query = array();
481 $binds = array();
482 foreach ( $selects as $s )
483 {
484 $query[] = '( ' . $s->query . ' )';
485 $binds = array_merge( $binds, $s->binds );
486 }
487 $query = implode( ' UNION ', $query );
488
489 /* Order? */
490 if( $order )
491 {
492 $query .= ' ORDER BY ' . $order;
493 }
494
495 /* Limit */
496 if( $limit )
497 {
498 $query .= $this->compileLimitClause( $limit );
499 }
500
501 /* Return */
502 return new \IPSUtf8\Db\Select( $query, $binds, $this );
503 }
504
505 /**
506 * Run INSERT statement and return insert ID
507 *
508 * @see <a href='http://dev.mysql.com/doc/refman/5.1/en/insert.html'>INSERT Syntax</a>
509 * @param string $table Table name
510 * @param array|\IPSUtf8\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
511 * @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.
512 * @see \IPSUtf8\Db::replace()
513 * @param bool $bulkInsertNoPrep Turns off prepared statements for bulk inserts
514 * @return int
515 * @throws \IPSUtf8\Db\Exception
516 */
517 public function insert( $table, $set, $odkUpdate=FALSE, $bulkInsertNoPrep=FALSE, $tableDefinition=NULL )
518 {
519 /* Is a statement? */
520 if ( $set instanceof \IPSUtf8\Db\Select )
521 {
522 $query = "INSERT " . ( $odkUpdate ? " IGNORE " : '' ) . " INTO `{$this->prefix}{$table}` " . $set->query;
523 $binds = $set->binds;
524 $odkUpdate = false;
525 }
526
527 /* Nope, normal array */
528 else
529 {
530 /* Is this just one row? */
531 foreach ( $set as $k => $v )
532 {
533 if ( !\is_array( $v ) )
534 {
535 $set = array( $set );
536 }
537 break;
538 }
539
540 /* Compile */
541 $columns = NULL;
542 $values = array();
543 $binds = array();
544 foreach ( $set as $row )
545 {
546 if ( $columns === NULL )
547 {
548 $columns = array_map( function( $val ){ return "`{$val}`"; }, array_keys( $row ) );
549 }
550
551 if ( \count( $set ) > 1 AND $bulkInsertNoPrep )
552 {
553 if ( $tableDefinition === NULL )
554 {
555 $tableDefinition = $this->getTableDefinition( $table );
556 }
557
558 foreach( $row as $k => $v )
559 {
560 if ( isset( $tableDefinition['definition']['columns'][ $k ] ) )
561 {
562 $isInt = \in_array( \mb_strtolower( $tableDefinition['definition']['columns'][ $k ]['type'] ), array( 'integer', 'int', 'smallint', 'tinyint', 'mediumint', 'bigint', 'decimal', 'numeric', 'float', 'double' ) );
563
564 if ( $isInt )
565 {
566 if ( empty( $v ) )
567 {
568
569 if ( ! empty( $tableDefinition['definition']['columns'][ $k ]['allow_null'] ) AND $v !== 0 )
570 {
571 $row[ $k ] = 'null';
572 }
573 else
574 {
575 $row[ $k ] = 0;
576 }
577 }
578 }
579 else
580 {
581 if ( empty( $v ) )
582 {
583 if ( ! empty( $tableDefinition['definition']['columns'][ $k ]['allow_null'] ) AND $v !== '' )
584 {
585 $row[ $k ] = 'null';
586 }
587 else
588 {
589 $row[ $k ] = "''";
590 }
591 }
592 else
593 {
594 $row[ $k ] = "'" . $this->real_escape_string( $v ) . "'";
595 }
596 }
597 }
598 else
599 {
600 if ( ! ctype_digit( (string) $v ) )
601 {
602 if ( empty( $v ) )
603 {
604 $row[ $k ] = 'null';
605 }
606 else
607 {
608 $row[ $k ] = "'" . $this->real_escape_string( $v ) . "'";
609 }
610 }
611 }
612 }
613
614 $values[] = '(' . implode( ', ', $row ) . ')';
615 }
616 else
617 {
618 $binds = array_merge( $binds, array_values( $row ) );
619 $values[] = '( ' . implode( ', ', array_fill( 0, \count( $columns ), '?' ) ) . ' )';
620 }
621 }
622
623 /* Construct query */
624 $query = "INSERT INTO `{$this->prefix}{$table}` ( " . implode( ', ', $columns ) . ' ) VALUES ' . implode( ', ', $values );
625 }
626
627 /* Add "ON DUPLICATE KEY UPDATE" */
628 if( $odkUpdate )
629 {
630 $query .= " ON DUPLICATE KEY UPDATE " . implode( ', ', array_map( function( $val ){ return "{$val}=VALUES({$val})"; }, $columns ) );
631 }
632
633 /* Run */
634 if ( \count( $set ) > 1 AND $bulkInsertNoPrep )
635 {
636 $stmt = $this->query( $query );
637 return $this->insert_id;
638 }
639 else
640 {
641 $stmt = $this->preparedQuery( $query, $binds );
642 return $stmt->insert_id;
643 }
644 }
645
646 /**
647 * Run REPLACE statament and return number of affected rows
648 *
649 * @see <a href='http://dev.mysql.com/doc/refman/5.1/en/replace.html'>REPLACE Syntax</a>
650 * @param string $table Table name
651 * @param array $set Values to insert
652 * @return \IPSUtf8\Db\Statement
653 * @throws \IPSUtf8\Db\Exception
654 */
655 public function replace( $table, $set )
656 {
657 $columns = implode( ', ', array_map( function( $val ){ return "`{$val}`"; }, array_keys( $set ) ) );
658 $query = "REPLACE INTO `{$this->prefix}{$table}` ( " . $columns . " ) VALUES ( " . implode( ', ', array_fill( 0, \count( $set ), '?' ) ) . " )";
659
660 $stmt = $this->preparedQuery( $query, array_values( $set ) );
661 return $stmt->affected_rows;
662 }
663
664 /**
665 * Run UPDATE statement and return number of affected rows
666 *
667 * @see \IPSUtf8\Db::build
668 * @see <a href='http://dev.mysql.com/doc/refman/5.1/en/update.html'>UPDATE Syntax</a>
669 * @param string|array $table Table Name, or array( Table Name => Identifier )
670 * @param string|array $set Values to set (keys should be the table columns) or pre-formatted SET clause
671 * @param mixed $where WHERE clause (see \IPSUtf8\Db::build for details)
672 * @param array $joins Tables to join (see \IPSUtf8\Db::build for details)
673 * @return int
674 * @throws \IPSUtf8\Db\Exception
675 */
676 public function update( $table, $set, $where='', $joins=array() )
677 {
678 $binds = array();
679
680 /* Work out table */
681 $table = \is_array( $table ) ? "`{$this->prefix}{$table[0]}` {$this->prefix}{$table[1]}" : "`{$this->prefix}{$table}`";
682
683 /* Work out joins */
684 foreach ( $joins as $join )
685 {
686 $type = ( isset( $join['type'] ) and \in_array( strtoupper( $join['type'] ), array( 'LEFT', 'INNER', 'RIGHT' ) ) ) ? strtoupper( $join['type'] ) : 'LEFT';
687 $_table = \is_array( $join['from'] ) ? "`{$this->prefix}{$join['from'][0]}` {$this->prefix}{$join['from'][1]}" : $join['from'];
688
689 $on = $this->compileWhereClause( $join['where'] );
690 $binds = array_merge( $binds, $on['binds'] );
691
692 $joins[] = "{$type} JOIN {$_table} ON {$on['clause']}";
693 }
694 $joins = empty( $joins ) ? '' : ( ' ' . implode( "\n", $joins ) );
695
696 /* Work out SET clause */
697 if ( \is_array( $set ) )
698 {
699 $_set = array();
700 foreach ( $set as $k => $v )
701 {
702 $_set[] = "`{$k}`=?";
703 $binds[] = $v;
704 }
705 $set = implode( ',', $_set );
706 }
707
708 /* Compile where clause */
709 if ( $where !== '' )
710 {
711 $_where = $this->compileWhereClause( $where );
712 $where = 'WHERE ' . $_where['clause'];
713 $binds = array_merge( $binds, $_where['binds'] );
714 }
715
716 /* Run it */
717 $stmt = $this->preparedQuery( "UPDATE {$table} {$joins} SET {$set} {$where}", $binds );
718 return $stmt->affected_rows;
719 }
720
721 /**
722 * Run DELETE statement and return number of affected rows
723 *
724 * @see \IPSUtf8\Db::build
725 * @see <a href='http://dev.mysql.com/doc/refman/5.1/en/delete.html'>DELETE Syntax</a>
726 * @param string $table Table Name
727 * @param string|array|\IPSUtf8\Db\Statement|null $where WHERE clause (see \IPSUtf8\Db::build for details)
728 * @param string|null $order ORDER BY clause (see \IPSUtf8\Db::build for details)
729 * @param int|array|null $limit LIMIT clause (see \IPSUtf8\Db::build for details)
730 * @param string|null $statementColumn If \IPSUtf8\Db\Statement is passed, this is the name of the column that results are being loaded from
731 * @return \IPSUtf8\Db\Statement
732 * @throws \IPSUtf8\Db\Exception
733 */
734 public function delete( $table, $where=NULL, $order=NULL, $limit=NULL, $statementColumn=NULL )
735 {
736 /* Basic query */
737 $query = "DELETE FROM `{$this->prefix}{$table}`";
738
739 /* Is a statement? */
740 if ( $where instanceof \IPSUtf8\Db\Statement )
741 {
742 $query .= ' WHERE ' . $statementColumn . ' IN(' . $where->query . ')';
743 $binds = $where->binds;
744 }
745
746 /* Add where clause */
747 else
748 {
749 $binds = array();
750 if ( $where !== NULL )
751 {
752 $_where = $this->compileWhereClause( $where );
753 $query .= ' WHERE ' . $_where['clause'];
754 $binds = $_where['binds'];
755 }
756 }
757
758 /* Order? */
759 if( $order !== NULL )
760 {
761 $query .= ' ORDER BY ' . $order;
762 }
763
764 /* Limit */
765 if( $limit !== NULL )
766 {
767 $query .= $this->compileLimitClause( $limit );
768 }
769
770 /* Run it */
771 $stmt = $this->preparedQuery( $query, $binds );
772 return $stmt->affected_rows;
773 }
774
775 /**
776 * Compile WHERE clause
777 *
778 * @param string|array $data See \IPSUtf8\Db::build for details
779 * @return array Array containing the WHERE clause and the values to be bound - array( 'clause' => '1=1', 'binds' => array() )
780 */
781 public function compileWhereClause( $data )
782 {
783 $return = array( 'clause' => '1=1', 'binds' => array() );
784
785 if( \is_string( $data ) )
786 {
787 $return['clause'] = $data;
788 }
789 elseif ( \is_array( $data ) and ! empty( $data ) )
790 {
791 if ( \is_string( $data[0] ) )
792 {
793 $data = array( $data );
794 }
795
796 $clauses = array();
797 foreach ( $data as $bit )
798 {
799 if( !\is_array( $bit ) )
800 {
801 $clauses[] = $bit;
802 }
803 else
804 {
805 $clauses[] = array_shift( $bit );
806 $return['binds'] = array_merge( $return['binds'], $bit );
807 }
808 }
809
810 $return['clause'] = implode( ' AND ', $clauses );
811 }
812
813 return $return;
814 }
815
816 /**
817 * Compile LIMIT clause
818 *
819 * @param int|array $data See \IPSUtf8\Db::build for details
820 * @return string
821 */
822 protected function compileLimitClause( $data )
823 {
824 $limit = NULL;
825 if( \is_array( $data ) )
826 {
827 $offset = \intval( $data[0] );
828 $limit = \intval( $data[1] );
829 }
830 else
831 {
832 $offset = \intval( $data );
833 }
834
835 if( $limit !== NULL )
836 {
837 return " LIMIT {$offset},{$limit}";
838 }
839 else
840 {
841 return " LIMIT {$offset}";
842 }
843 }
844
845 /**
846 * Compile column definition
847 *
848 * @code
849 \IPSUtf8\Db::i()->compileColumnDefinition( array(
850 'name' => 'column_name', // Column name
851 'type' => 'VARCHAR', // Data type (do not specify length, etc. here)
852 'length' => 255, // Length. May be required or optional depending on data type.
853 'decimals' => 2, // Decimals. May be required or optional depending on data type.
854 'values' => array( 0, 1 ), // Acceptable values. Required for ENUM and SET data types.
855 'null' => FALSE, // (Optional) Specifies whether or not NULL vavlues are allowed. Defaults to TRUE.
856 'default' => 'Default Value', // (Optional) Default value
857 'comment' => 'Column Comment', // (Optional) Column comment
858 'unsigned' => TRUE, // (Optional) Will specify UNSIGNED for numeric types. Defaults to FALSE.
859 'zerofill' => TRUE, // (Optional) Will specify ZEROFILL for numeric types. Defaults to FALSE.
860 'auto_increment'=> TRUE, // (Optional) Will specify auto_increment. Defaults to FALSE.
861 'binary' => TRUE, // (Optional) Will specify BINARY for TEXT types. Defaults to FALSE.
862 'primary' => TRUE, // (Optional) Will specify PRIMARY KEY. Defaults to FALSE.
863 'unqiue' => TRUE, // (Optional) Will specify UNIQUE. Defaults to FALSE.
864 'key' => TRUE, // (Optional) Will specify KEY. Defaults to FALSE.
865 ) );
866 * @endcode
867 * @see <a href='http://dev.mysql.com/doc/refman/5.1/en/create-table.html'>MySQL CREATE TABLE syntax</a>
868 * @param array $data Column Data (see \IPSUtf8\Db::createTable for details)
869 * @return string
870 */
871 public function compileColumnDefinition( $data )
872 {
873 /* Specify name and type */
874 $definition = "`{$data['name']}` {$data['type']} ";
875
876 /* Some types specify length */
877 if(
878 \in_array( $data['type'], array( 'VARCHAR', 'VARBINARY' ) )
879 or
880 (
881 isset( $data['length'] ) and $data['length']
882 and
883 \in_array( $data['type'], array( 'BIT', 'TINYINT', 'SMALLINT', 'MEDIUMINT', 'INT', 'INTEGER', 'BIGINT', 'REAL', 'DOUBLE', 'FLOAT', 'DECIMAL', 'NUMERIC', 'CHAR', 'BINARY' ) )
884 )
885 ) {
886 $definition .= "({$data['length']}";
887
888 /* And some of those specify decimals (which may or may not be optional) */
889 if( \in_array( $data['type'], array( 'REAL', 'DOUBLE', 'FLOAT' ) ) or ( \in_array( $data['type'], array( 'DECIMAL', 'NUMERIC' ) ) and isset( $data['decimals'] ) ) )
890 {
891 $definition .= ',' . $data['decimals'];
892 }
893
894 $definition .= ') ';
895 }
896
897 /* Numeric types can be UNSIGNED and ZEROFILL */
898 if( \in_array( $data['type'], array( 'TINYINT', 'SMALLINT', 'MEDIUMINT', 'INT', 'INTEGER', 'BIGINT', 'REAL', 'DOUBLE', 'FLOAT', 'DECIMAL', 'NUMERIC' ) ) )
899 {
900 if( isset( $data['unsigned'] ) and $data['unsigned'] === TRUE )
901 {
902 $definition .= 'UNSIGNED ';
903 }
904 if( isset( $data['zerofill'] ) and $data['zerofill'] === TRUE )
905 {
906 $definition .= 'ZEROFILL ';
907 }
908 }
909
910 /* ENUM and SETs have values */
911 if( \in_array( $data['type'], array( 'ENUM', 'SET' ) ) )
912 {
913 $values = array();
914 foreach ( $data['values'] as $v )
915 {
916 $values[] = "'{$this->escape_string( $v )}'";
917 }
918
919 $definition .= '(' . implode( ',', $values ) . ') ';
920 }
921
922 /* Some types can be binary or not */
923 if( isset( $data['binary'] ) and $data['binary'] === TRUE and \in_array( $data['type'], array( 'CHAR', 'VARCHAR', 'TINYTEXT', 'TEXT', 'MEDIUMTEXT', 'LONGTEXT' ) ) )
924 {
925 $definition .= 'BINARY ';
926 }
927
928 /* Text types specify a character set and collation */
929 if( \in_array( $data['type'], array( 'CHAR', 'VARCHAR', 'TINYTEXT', 'TEXT', 'MEDIUMTEXT', 'LONGTEXT', 'ENUM', 'SET' ) ) )
930 {
931 $definition .= "CHARACTER SET {$this->charset} COLLATE {$this->collation} ";
932 }
933
934 /* NULL? */
935 if( isset( $data['allow_null'] ) and $data['allow_null'] === FALSE )
936 {
937 $definition .= 'NOT NULL ';
938 }
939 else
940 {
941 $definition .= 'NULL ';
942 }
943
944 /* Default value */
945 if( isset( $data['default'] ) and !\in_array( $data['type'], array( 'TINYTEXT', 'TEXT', 'MEDIUMTEXT', 'LONGTEXT', 'BLOB', 'MEDIUMBLOB', 'BIGBLOB' ) ) )
946 {
947 $defaultValue = \in_array( $data['type'], array( 'TINYINT', 'SMALLINT', 'MEDIUMINT', 'INT', 'INTEGER', 'BIGINT', 'REAL', 'DOUBLE', 'FLOAT', 'DECIMAL', 'NUMERIC', 'BIT' ) ) ? \floatval( $data['default'] ) : ( ! \in_array( $data['default'], array( 'CURRENT_TIMESTAMP' ) ) ? '\'' . $this->escape_string( $data['default'] ) . '\'' : $data['default'] );
948
949 /* Strict Mode isn't nice */
950 $toTest = trim( $defaultValue, "'" );
951 if ( \in_array( $data['type'], array( 'DATETIME' ) ) AND empty( $toTest ) ) # array here in case we need to expand later
952 {
953 $defaultValue = '\'0000-00-00 00:00:00\'';
954 }
955
956 $definition .= "DEFAULT {$defaultValue} ";
957 }
958
959 /* auto_increment? */
960 if( isset( $data['auto_increment'] ) and $data['auto_increment'] === TRUE )
961 {
962 $definition .= 'AUTO_INCREMENT ';
963 }
964
965 /* Index? */
966 if( isset( $data['primary'] ) )
967 {
968 $definition .= 'PRIMARY KEY ';
969 }
970 elseif( isset( $data['unique'] ) )
971 {
972 $definition .= 'UNIQUE ';
973 }
974 if( isset( $data['key'] ) )
975 {
976 $definition .= 'KEY ';
977 }
978
979 /* Comment */
980 if( isset( $data['comment'] ) )
981 {
982 $definition .= "COMMENT '{$this->escape_string( $data['comment'] )}'";
983 }
984
985 /* Return */
986 return $definition;
987 }
988
989 /**
990 * Compile index definition
991 *
992 * @code
993 \IPSUtf8\Db::i()->compileIndexDefinition( array(
994 'type' => 'key', // "primary", "unique", "fulltext" or "key"
995 'name' => 'index_name', // Index name. Not required if type is "primary"
996 'length' => 200, // Index length (used when taking part of a text field, for example)
997 'columns => array( 'column' ) // Columns to be in the index
998 ) );
999 * @endcode
1000 * @see <a href='http://dev.mysql.com/doc/refman/5.1/en/create-index.html'>MySQL CREATE INDEX syntax</a>
1001 * @see \IPSUtf8\Db::createTable
1002 * @param array $data Index Data (see \IPSUtf8\Db::createTable for details)
1003 * @return string
1004 */
1005 public function compileIndexDefinition( $data )
1006 {
1007 $definition = '';
1008
1009 /* Specify type */
1010 switch ( $data['type'] )
1011 {
1012 case 'primary':
1013 $definition .= 'PRIMARY KEY ';
1014 break;
1015
1016 case 'unique':
1017 $definition .= "UNIQUE KEY `{$data['name']}` ";
1018 break;
1019
1020 case 'fulltext':
1021 $definition .= "FULLTEXT KEY `{$data['name']}` ";
1022 break;
1023
1024 default:
1025 $definition .= "KEY `{$data['name']}` ";
1026 break;
1027 }
1028
1029 /* Specify columns */
1030 $definition .= '(' . implode( ',', array_map( function ( $val, $len )
1031 {
1032 return ( ! empty( $len ) ) ? "`{$val}`({$len})" : "`{$val}`";
1033 }, $data['columns'], ( ( isset( $data['length'] ) AND \is_array( $data['length'] ) ) ? $data['length'] : array_fill( 0, \count( $data['columns'] ), null ) ) ) ) . ')';
1034
1035 /* Return */
1036 return $definition;
1037 }
1038
1039 /**
1040 * Does table exist?
1041 *
1042 * @param string $name Table Name
1043 * @return bool
1044 */
1045 public function checkForTable( $name )
1046 {
1047 return ( $this->query( "SHOW TABLES LIKE '". $this->escape_string( "{$this->prefix}{$name}" ) . "'" )->num_rows > 0 );
1048 }
1049
1050 /**
1051 * Does index exist?
1052 *
1053 * @param string $name Table Name
1054 * @param string $index Index Name
1055 * @return bool
1056 */
1057 public function checkForIndex( $name, $index )
1058 {
1059 return ( $this->query( "SHOW INDEXES FROM ". $this->escape_string( "{$this->prefix}{$name}" ) . " WHERE Key_name LIKE '". $this->escape_string( $index ) . "'" )->num_rows > 0 );
1060 }
1061
1062 /**
1063 * Create Table
1064 *
1065 * @code
1066 \IPSUtf8\Db::createTable( array(
1067 'name' => 'table_name', // Table name
1068 'columns' => array( ... ), // Column data - see \IPSUtf8\Db::compileColumnDefinition for details
1069 'indexes' => array( ... ), // (Optional) Index data - see \IPSUtf8\Db::compileIndexDefinition for details
1070 'comment' => '...', // (Optional) Table comment
1071 'engine' => 'MEMORY', // (Optional) Engine to use - will default to not specifying one, unless a FULLTEXT index is specified, in which case MyISAM is forced
1072 'temporary' => TRUE, // (Optional) Will sepcify CREATE TEMPORARY TABLE - defaults to FALSE
1073 'if_not_exists' => TRUE, // (Optional) Will sepcify CREATE TABLE name IF NOT EXISTS - defaults to FALSE
1074 ) );
1075 * @endcode
1076 * @see <a href='http://dev.mysql.com/doc/refman/5.1/en/create-table.html'>MySQL CREATE TABLE syntax</a>
1077 * @see \IPSUtf8\Db::compileColumnDefinition
1078 * @see \IPSUtf8\Db::compileIndexDefinition
1079 * @param array $data Table Definition (see code sample for details)
1080 * @throws \IPSUtf8\Db\Exception
1081 * @return void
1082 */
1083 public function createTable( $data )
1084 {
1085 /* Start with a basic CREATE TABLE */
1086 $query = 'CREATE ';
1087 if( isset( $data['temporary'] ) and $data['temporary'] === TRUE )
1088 {
1089 $query.= 'TEMPORARY ';
1090 }
1091 $query .= 'TABLE ';
1092 if( isset( $data['if_not_exists'] ) and $data['if_not_exists'] === TRUE )
1093 {
1094 $query.= 'IF NOT EXISTS ';
1095 }
1096
1097 /* Add in our create definition */
1098 $query .= "`{$this->prefix}{$data['name']}` (\n\t";
1099 $createDefinitons = array();
1100 foreach ( $data['columns'] as $field )
1101 {
1102 $createDefinitons[] = $this->compileColumnDefinition( $field );
1103 }
1104 if( isset( $data['indexes'] ) )
1105 {
1106 foreach ( $data['indexes'] as $index )
1107 {
1108 if( $index['type'] === 'fulltext' )
1109 {
1110 $data['engine'] = 'MYISAM';
1111 }
1112 $createDefinitons[] = $this->compileIndexDefinition( $index );
1113 }
1114 }
1115 $query .= implode( ",\n\t", $createDefinitons );
1116 $query .= "\n)\n";
1117
1118 /* Specifying a particular engine? */
1119 if( isset( $data['engine'] ) and $data['engine'] )
1120 {
1121 $query .= "ENGINE {$data['engine']} ";
1122 }
1123
1124 /* Specify UTF8 */
1125 $query .= "CHARACTER SET {$this->charset} COLLATE {$this->collation} ";
1126
1127 /* Add comment */
1128 if( isset( $data['comment'] ) )
1129 {
1130 $query .= "COMMENT '{$this->escape_string( $data['comment'] )}'";
1131 }
1132
1133 /* Do it */
1134 return $this->query( $query );
1135 }
1136
1137 /**
1138 * Rename table
1139 *
1140 * @see <a href='http://dev.mysql.com/doc/refman/5.1/en/rename-table.html'>
1141 * @param string $oldName The current table name
1142 * @param string $newName The new name
1143 * @return void
1144 */
1145 public function renameTable( $oldName, $newName )
1146 {
1147 return $this->query( "RENAME TABLE `{$this->prefix}{$this->escape_string( $oldName )}` TO `{$this->prefix}{$this->escape_string( $newName )}`" );
1148 }
1149
1150 /**
1151 * Alter Table
1152 * Can only update the comment and engine
1153 *
1154 * @param string $table Table name
1155 * @param string|null $comment Table comment. NULL to not change
1156 * @param string|null $engine Engine to use. NULL to not change
1157 * @return void
1158 */
1159 public function alterTable( $table, $comment=NULL, $engine=NULL )
1160 {
1161 if ( $comment === NULL and $engine === NULL )
1162 {
1163 return;
1164 }
1165
1166 $query = "ALTER TABLE `{$this->prefix}{$this->escape_string( $table )}` ";
1167 if ( $comment !== NULL )
1168 {
1169 $query .= "COMMENT='{$this->escape_string( $comment )}' ";
1170 }
1171 if ( $engine !== NULL )
1172 {
1173 $query .= "ENGINE={$engine}";
1174 }
1175
1176 return $this->query( $query );
1177 }
1178
1179 /**
1180 * Drop table
1181 *
1182 * @see <a href='http://dev.mysql.com/doc/refman/5.1/en/drop-table.html'>DROP TABLE Syntax</a>
1183 * @param string|array $table Table Name(s)
1184 * @param bool $ifExists Adds an "IF EXISTS" clause to the query
1185 * @param bool $temporary Table is temporary?
1186 * @return void
1187 */
1188 public function dropTable( $table, $ifExists=FALSE, $temporary=FALSE )
1189 {
1190 $prefix = $this->prefix;
1191
1192 return $this->query(
1193 'DROP '
1194 . ( $temporary ? 'TEMPORARY ' : '' )
1195 . 'TABLE '
1196 . ( $ifExists ? 'IF EXISTS ' :'' )
1197 . implode( ', ', array_map(
1198 function( $val ) use ( $prefix )
1199 {
1200 return '`' . $prefix . $val . '`';
1201 },
1202 ( \is_array( $table ) ? $table : array( $table ) )
1203 ) )
1204 );
1205 }
1206
1207 /**
1208 * Get the table definition for an existing table
1209 *
1210 * @see \IPSUtf8\Db::createTable
1211 * @param string $table Table Name
1212 * @return array Table definition - see IPSUtf8\Db::createTable for details
1213 * @throws \OutOfRangeException
1214 * @throws \IPSUtf8\Db\Exception
1215 */
1216 public function getTableDefinition( $table )
1217 {
1218 /* Set name */
1219 $definition = array(
1220 'name' => $table,
1221 );
1222
1223 /* Fetch columns */
1224 $query = $this->query( "SHOW FULL COLUMNS FROM `{$this->prefix}" . $this->escape_string( $table ) . '`' );
1225
1226 if ( $query->num_rows === 0 )
1227 {
1228 throw new \OutOfRangeException;
1229 }
1230 while ( $row = $query->fetch_assoc() )
1231 {
1232 /* Set basic information */
1233 $columnDefinition = array(
1234 'name' => $row['Field'],
1235 'type' => '',
1236 'length' => 0,
1237 'decimals' => NULL,
1238 'values' => array()
1239 );
1240
1241 /* Parse the type */
1242 if( mb_strpos( $row['Type'], '(' ) !== FALSE )
1243 {
1244 /* First, we need to protect the enum options as they may have spaces before splitting */
1245 preg_match( '/(.+?)\((.+?)\)/', $row['Type'], $matches );
1246 $options = $matches[2];
1247 $type = preg_replace( '/(.+?)\((.+?)\)/', "$1(___TEMP___)", $row['Type'] );
1248 $typeInfo = explode( ' ', $type );
1249 $typeInfo[0] = str_replace( "___TEMP___", $options, $typeInfo[0] );
1250
1251 /* Now we match out the options */
1252 preg_match( '/(.+?)\((.+?)\)/', $typeInfo[0], $matches );
1253 $columnDefinition['type'] = mb_strtoupper( $matches[1] );
1254
1255 if( $columnDefinition['type'] === 'ENUM' or $columnDefinition['type'] === 'SET' )
1256 {
1257 preg_match_all( "/'(.+?)'/", $matches[2], $enum );
1258 $columnDefinition['values'] = $enum[1];
1259 }
1260 else
1261 {
1262 $lengthInfo = explode( ',', $matches[2] );
1263 $columnDefinition['length'] = \intval( $lengthInfo[0] );
1264 if( isset( $lengthInfo[1] ) )
1265 {
1266 $columnDefinition['decimals'] = \intval( $lengthInfo[1] );
1267 }
1268 }
1269 }
1270 else
1271 {
1272 $typeInfo = explode( ' ', $row['Type'] );
1273
1274 $columnDefinition['type'] = mb_strtoupper( $typeInfo[0] );
1275 $columnDefinition['length'] = 0;
1276 }
1277
1278 /* unsigned? */
1279 $columnDefinition['unsigned'] = \in_array( 'unsigned', $typeInfo );
1280
1281 /* zerofill? */
1282 $columnDefinition['zerofill'] = \in_array( 'zerofill', $typeInfo );
1283
1284 /* binary? */
1285 $columnDefinition['binary'] = ( $row['Collation'] === $this->binaryCollation );
1286
1287 /* Allow NULL? */
1288 $columnDefinition['allow_null'] = ( $row['Null'] === 'YES' );
1289
1290 /* Default value */
1291 $columnDefinition['default'] = $row['Default'];
1292 //if ( $columnDefinition['default'] === NULL and $columnDefinition['type'] != 'DATETIME' and !$columnDefinition['allow_null'] and mb_strpos( $row['Extra'], 'auto_increment' ) === FALSE )
1293 //{
1294 // $columnDefinition['default'] = '';
1295 //}
1296
1297 /* auto_increment */
1298 $columnDefinition['auto_increment'] = mb_strpos( $row['Extra'], 'auto_increment' ) !== FALSE;
1299
1300 /* Comment */
1301 $columnDefinition['comment'] = $row['Comment'] ?: '';
1302
1303 /* Collation */
1304 $columnDefinition['collation'] = $row['Collation'] ?: NULL;
1305
1306 /* Add it in the definition */
1307 ksort( $columnDefinition );
1308 $definition['columns'][ $columnDefinition['name'] ] = $columnDefinition;
1309 }
1310
1311 /* Fetch indexes */
1312 $indexes = array();
1313 $query = $this->query( "SHOW INDEXES FROM `{$this->prefix}{$table}`" );
1314 while ( $row = $query->fetch_assoc() )
1315 {
1316 $length = ( isset( $row['Sub_part'] ) AND ! empty( $row['Sub_part'] ) ) ? \intval( $row['Sub_part'] ) : null;
1317
1318 if( isset( $indexes[ $row['Key_name'] ] ) )
1319 {
1320 $indexes[ $row['Key_name'] ]['length'][] = $length;
1321 $indexes[ $row['Key_name'] ]['columns'][] = $row['Column_name'];
1322 }
1323 else
1324 {
1325 $type = 'key';
1326 if( $row['Key_name'] === 'PRIMARY' )
1327 {
1328 $type = 'primary';
1329 }
1330 elseif( $row['Index_type'] === 'FULLTEXT' )
1331 {
1332 $definition['engine'] = 'MYISAM';
1333 $type = 'fulltext';
1334 }
1335 elseif( !$row['Non_unique'] )
1336 {
1337 $type = 'unique';
1338 }
1339
1340 $indexes[ $row['Key_name'] ] = array(
1341 'type' => $type,
1342 'name' => $row['Key_name'],
1343 'length' => array( $length ),
1344 'columns' => array( $row['Column_name'] )
1345 );
1346 }
1347 }
1348 $definition['indexes'] = $indexes;
1349
1350 /* Finally, get the table comment */
1351 $row = $this->query( "SHOW TABLE STATUS LIKE '{$table}'" )->fetch_assoc();
1352
1353 $definition['comment'] = $row['Comment'];
1354 $definition['collation'] = $row['Collation'];
1355 $definition['engine'] = $row['Engine'];
1356
1357 if ( ! isset( $definition['engine'] ) )
1358 {
1359 $definition['engine'] = $this->defaultEngine();
1360 }
1361
1362 /* Return */
1363 return $definition;
1364 }
1365
1366 /**
1367 * Fetches the default engine
1368 *
1369 * @return string
1370 */
1371 public function defaultEngine()
1372 {
1373 if ( $this->defaultEngine === NULL )
1374 {
1375 /* If this is an IPB, we should use what is defined in conf_global.php */
1376 if ( file_exists( ROOT_PATH . '/conf_global.php' ) )
1377 {
1378 require( ROOT_PATH . '/conf_global.php' );
1379 if ( isset( $INFO['mysql_tbl_type'] ) )
1380 {
1381 if ( mb_strtolower( $INFO['mysql_tbl_type'] ) == 'myisam' )
1382 {
1383 $this->defaultEngine = 'MyISAM';
1384 }
1385 else
1386 {
1387 $this->defaultEngine = 'InnoDB';
1388 }
1389
1390 return $this->defaultEngine;
1391 }
1392 }
1393
1394 $query = $this->query( "SHOW ENGINES" );
1395
1396 while ( $row = $query->fetch_assoc() )
1397 {
1398 if ( \mb_strtolower( $row['Support'] ) === 'default' )
1399 {
1400 $this->defaultEngine = \mb_strtolower( $row['Engine'] );
1401 break;
1402 }
1403 }
1404 }
1405
1406 return $this->defaultEngine;
1407 }
1408
1409 /**
1410 * Add column to table in database
1411 *
1412 * @see \IPSUtf8\Db::compileColumnDefinition
1413 * @param string $table Table name
1414 * @param array $definition Column Definition (see \IPSUtf8\Db::compileColumnDefinition for details)
1415 * @return void
1416 */
1417 public function addColumn( $table, $definition )
1418 {
1419 return $this->query( "ALTER TABLE `{$this->prefix}{$this->escape_string( $table )}` ADD COLUMN {$this->compileColumnDefinition( $definition )}" );
1420 }
1421
1422 /**
1423 * Modify an existing column
1424 *
1425 * @see \IPSUtf8\Db::compileColumnDefinition
1426 * @param string $table Table name
1427 * @param string $column Column name
1428 * @param array $definition New column definition (see \IPSUtf8\Db::compileColumnDefinition for details)
1429 * @return void
1430 */
1431 public function changeColumn( $table, $column, $definition )
1432 {
1433 return $this->query( "ALTER TABLE `{$this->prefix}{$this->escape_string( $table )}` CHANGE COLUMN `{$this->escape_string( $column )}` {$this->compileColumnDefinition( $definition )}" );
1434 }
1435
1436 /**
1437 * Drop a column
1438 *
1439 * @param string $table Table name
1440 * @param string $column Column name
1441 * @return void
1442 */
1443 public function dropColumn( $table, $column )
1444 {
1445 return $this->query( "ALTER TABLE `{$this->prefix}{$this->escape_string( $table )}` DROP COLUMN `{$this->escape_string( $column )}`;" );
1446 }
1447
1448 /**
1449 * Add index to table in database
1450 *
1451 * @see \IPSUtf8\Db::compileIndexDefinition
1452 * @param string $table Table name
1453 * @param array $definition Index Definition (see \IPSUtf8\Db::compileIndexDefinition for details)
1454 * @return void
1455 */
1456 public function addIndex( $table, $definition )
1457 {
1458 return $this->query( "ALTER TABLE `{$this->prefix}{$this->escape_string( $table )}` ADD {$this->compileIndexDefinition( $definition )}" );
1459 }
1460
1461 /**
1462 * Modify an existing index
1463 *
1464 * @see \IPSUtf8\Db::compileIndexDefinition
1465 * @param string $table Table name
1466 * @param string $index Index name
1467 * @param array $definition New index definition (see \IPSUtf8\Db::compileIndexDefinition for details)
1468 * @return void
1469 */
1470 public function changeIndex( $table, $index, $definition )
1471 {
1472 $this->dropIndex( $table, $index );
1473 $this->addIndex( $table, $definition );
1474 }
1475
1476 /**
1477 * Drop an index
1478 *
1479 * @param string $table Table name
1480 * @param string $index Column name
1481 * @return void
1482 */
1483 public function dropIndex( $table, $index )
1484 {
1485 return $this->query( "ALTER TABLE `{$this->prefix}{$this->escape_string( $table )}` DROP INDEX `{$this->escape_string( $index )}`;" );
1486 }
1487
1488 /**
1489 * Find In Set
1490 *
1491 * @param string $column Column name
1492 * @param array $values Acceptable values
1493 * @return string Where clause
1494 */
1495 public function in( $column, $values )
1496 {
1497 $where = array();
1498 $in = array();
1499
1500 foreach( $values as $i )
1501 {
1502 if ( $i and \is_numeric( $i ) )
1503 {
1504 $where[] = "FIND_IN_SET(" . $i . "," . $column . ")";
1505 }
1506 else if ( $i and \is_string( $i ) )
1507 {
1508 $in[] = "'" . $this->real_escape_string( $i ) . "'";
1509 }
1510 }
1511
1512 $return = array();
1513
1514 if ( ! empty( $where ) )
1515 {
1516 $return[] = '( ' . implode( " OR ", $where ) . ' )';
1517 }
1518
1519 if ( ! empty( $in ) )
1520 {
1521 $return[] = $column . ' IN(' . implode( ',', $in ) . ')';
1522 }
1523
1524 if ( \count( $return ) )
1525 {
1526 return '(' . implode( ' OR ', $return ) . ')';
1527 }
1528 else
1529 {
1530 return '1=1';
1531 }
1532 }
1533
1534}