3 namespace RedBeanPHP\QueryWriter;
5 use RedBeanPHP\QueryWriter\AQueryWriter as AQueryWriter;
6 use RedBeanPHP\QueryWriter as QueryWriter;
7 use RedBeanPHP\Adapter\DBAdapter as DBAdapter;
8 use RedBeanPHP\Adapter as Adapter;
9 use RedBeanPHP\RedException\SQL as SQLException;
12 * RedBeanPHP MySQLWriter.
13 * This is a QueryWriter class for RedBeanPHP.
14 * This QueryWriter provides support for the MySQL/MariaDB database platform.
16 * @file RedBeanPHP/QueryWriter/MySQL.php
17 * @author Gabor de Mooij and the RedBeanPHP Community
21 * (c) G.J.G.T. (Gabor) de Mooij and the RedBeanPHP Community.
22 * This source file is subject to the BSD/GPLv2 License that is bundled
23 * with this source code in the file license.txt.
25 class MySQL extends AQueryWriter implements QueryWriter
30 const C_DATATYPE_BOOL = 0;
31 const C_DATATYPE_UINT32 = 2;
32 const C_DATATYPE_DOUBLE = 3;
33 const C_DATATYPE_TEXT7 = 4; //InnoDB cant index varchar(255) utf8mb4 - so keep 191 as long as possible
34 const C_DATATYPE_TEXT8 = 5;
35 const C_DATATYPE_TEXT16 = 6;
36 const C_DATATYPE_TEXT32 = 7;
37 const C_DATATYPE_SPECIAL_DATE = 80;
38 const C_DATATYPE_SPECIAL_DATETIME = 81;
39 const C_DATATYPE_SPECIAL_POINT = 90;
40 const C_DATATYPE_SPECIAL_LINESTRING = 91;
41 const C_DATATYPE_SPECIAL_POLYGON = 92;
42 const C_DATATYPE_SPECIAL_MONEY = 93;
44 const C_DATATYPE_SPECIFIED = 99;
54 protected $quoteCharacter = '`';
57 * @see AQueryWriter::getKeyMapForType
59 protected function getKeyMapForType( $type )
61 $databaseName = $this->adapter->getCell('SELECT DATABASE()');
62 $table = $this->esc( $type, TRUE );
63 $keys = $this->adapter->get('
65 information_schema.key_column_usage.constraint_name AS `name`,
66 information_schema.key_column_usage.referenced_table_name AS `table`,
67 information_schema.key_column_usage.column_name AS `from`,
68 information_schema.key_column_usage.referenced_column_name AS `to`,
69 information_schema.referential_constraints.update_rule AS `on_update`,
70 information_schema.referential_constraints.delete_rule AS `on_delete`
71 FROM information_schema.key_column_usage
72 INNER JOIN information_schema.referential_constraints
73 ON information_schema.referential_constraints.constraint_name = information_schema.key_column_usage.constraint_name
75 information_schema.key_column_usage.table_schema = :database
76 AND information_schema.referential_constraints.constraint_schema = :database
77 AND information_schema.key_column_usage.constraint_schema = :database
78 AND information_schema.key_column_usage.table_name = :table
79 AND information_schema.key_column_usage.constraint_name != \'PRIMARY\'
80 AND information_schema.key_column_usage.referenced_table_name IS NOT NULL
81 ', array( ':database' => $databaseName, ':table' => $table ) );
82 $keyInfoList = array();
83 foreach ( $keys as $k ) {
84 $label = $this->makeFKLabel( $k['from'], $k['table'], $k['to'] );
85 $keyInfoList[$label] = array(
88 'table' => $k['table'],
90 'on_update' => $k['on_update'],
91 'on_delete' => $k['on_delete']
100 * @param Adapter $adapter Database Adapter
102 public function __construct( Adapter $adapter )
104 $this->typeno_sqltype = array(
105 MySQL::C_DATATYPE_BOOL => ' TINYINT(1) UNSIGNED ',
106 MySQL::C_DATATYPE_UINT32 => ' INT(11) UNSIGNED ',
107 MySQL::C_DATATYPE_DOUBLE => ' DOUBLE ',
108 MySQL::C_DATATYPE_TEXT7 => ' VARCHAR(191) ',
109 MYSQL::C_DATATYPE_TEXT8 => ' VARCHAR(255) ',
110 MySQL::C_DATATYPE_TEXT16 => ' TEXT ',
111 MySQL::C_DATATYPE_TEXT32 => ' LONGTEXT ',
112 MySQL::C_DATATYPE_SPECIAL_DATE => ' DATE ',
113 MySQL::C_DATATYPE_SPECIAL_DATETIME => ' DATETIME ',
114 MySQL::C_DATATYPE_SPECIAL_POINT => ' POINT ',
115 MySQL::C_DATATYPE_SPECIAL_LINESTRING => ' LINESTRING ',
116 MySQL::C_DATATYPE_SPECIAL_POLYGON => ' POLYGON ',
117 MySQL::C_DATATYPE_SPECIAL_MONEY => ' DECIMAL(10,2) '
120 $this->sqltype_typeno = array();
122 foreach ( $this->typeno_sqltype as $k => $v ) {
123 $this->sqltype_typeno[trim( strtolower( $v ) )] = $k;
126 $this->adapter = $adapter;
128 $this->encoding = $this->adapter->getDatabase()->getMysqlEncoding();
132 * This method returns the datatype to be used for primary key IDS and
133 * foreign keys. Returns one if the data type constants.
137 public function getTypeForID()
139 return self::C_DATATYPE_UINT32;
143 * @see QueryWriter::getTables
145 public function getTables()
147 return $this->adapter->getCol( 'show tables' );
151 * @see QueryWriter::createTable
153 public function createTable( $table )
155 $table = $this->esc( $table );
157 $encoding = $this->adapter->getDatabase()->getMysqlEncoding();
158 $sql = "CREATE TABLE $table (id INT( 11 ) UNSIGNED NOT NULL AUTO_INCREMENT, PRIMARY KEY ( id )) ENGINE = InnoDB DEFAULT CHARSET={$encoding} COLLATE={$encoding}_unicode_ci ";
160 $this->adapter->exec( $sql );
164 * @see QueryWriter::getColumns
166 public function getColumns( $table )
168 $columnsRaw = $this->adapter->get( "DESCRIBE " . $this->esc( $table ) );
171 foreach ( $columnsRaw as $r ) {
172 $columns[$r['Field']] = $r['Type'];
179 * @see QueryWriter::scanType
181 public function scanType( $value, $flagSpecial = FALSE )
183 $this->svalue = $value;
185 if ( is_null( $value ) ) return MySQL::C_DATATYPE_BOOL;
186 if ( $value === INF ) return MySQL::C_DATATYPE_TEXT7;
188 if ( $flagSpecial ) {
189 if ( preg_match( '/^-?\d+\.\d{2}$/', $value ) ) {
190 return MySQL::C_DATATYPE_SPECIAL_MONEY;
192 if ( preg_match( '/^\d{4}\-\d\d-\d\d$/', $value ) ) {
193 return MySQL::C_DATATYPE_SPECIAL_DATE;
195 if ( preg_match( '/^\d{4}\-\d\d-\d\d\s\d\d:\d\d:\d\d$/', $value ) ) {
196 return MySQL::C_DATATYPE_SPECIAL_DATETIME;
198 if ( preg_match( '/^POINT\(/', $value ) ) {
199 return MySQL::C_DATATYPE_SPECIAL_POINT;
201 if ( preg_match( '/^LINESTRING\(/', $value ) ) {
202 return MySQL::C_DATATYPE_SPECIAL_LINESTRING;
204 if ( preg_match( '/^POLYGON\(/', $value ) ) {
205 return MySQL::C_DATATYPE_SPECIAL_POLYGON;
209 //setter turns TRUE FALSE into 0 and 1 because database has no real bools (TRUE and FALSE only for test?).
210 if ( $value === FALSE || $value === TRUE || $value === '0' || $value === '1' ) {
211 return MySQL::C_DATATYPE_BOOL;
214 if ( is_float( $value ) ) return self::C_DATATYPE_DOUBLE;
216 if ( !$this->startsWithZeros( $value ) ) {
218 if ( is_numeric( $value ) && ( floor( $value ) == $value ) && $value >= 0 && $value <= 4294967295 ) {
219 return MySQL::C_DATATYPE_UINT32;
222 if ( is_numeric( $value ) ) {
223 return MySQL::C_DATATYPE_DOUBLE;
227 if ( mb_strlen( $value, 'UTF-8' ) <= 191 ) {
228 return MySQL::C_DATATYPE_TEXT7;
231 if ( mb_strlen( $value, 'UTF-8' ) <= 255 ) {
232 return MySQL::C_DATATYPE_TEXT8;
235 if ( mb_strlen( $value, 'UTF-8' ) <= 65535 ) {
236 return MySQL::C_DATATYPE_TEXT16;
239 return MySQL::C_DATATYPE_TEXT32;
243 * @see QueryWriter::code
245 public function code( $typedescription, $includeSpecials = FALSE )
247 if ( isset( $this->sqltype_typeno[$typedescription] ) ) {
248 $r = $this->sqltype_typeno[$typedescription];
250 $r = self::C_DATATYPE_SPECIFIED;
253 if ( $includeSpecials ) {
257 if ( $r >= QueryWriter::C_DATATYPE_RANGE_SPECIAL ) {
258 return self::C_DATATYPE_SPECIFIED;
265 * @see QueryWriter::addUniqueIndex
267 public function addUniqueConstraint( $type, $properties )
269 $tableNoQ = $this->esc( $type, TRUE );
271 foreach( $properties as $key => $column ) $columns[$key] = $this->esc( $column );
272 $table = $this->esc( $type );
273 sort( $columns ); // Else we get multiple indexes due to order-effects
274 $name = 'UQ_' . sha1( implode( ',', $columns ) );
276 $sql = "ALTER TABLE $table
277 ADD UNIQUE INDEX $name (" . implode( ',', $columns ) . ")";
278 $this->adapter->exec( $sql );
279 } catch ( SQLException $e ) {
280 //do nothing, dont use alter table ignore, this will delete duplicate records in 3-ways!
287 * @see QueryWriter::addIndex
289 public function addIndex( $type, $name, $property )
292 $table = $this->esc( $type );
293 $name = preg_replace( '/\W/', '', $name );
294 $column = $this->esc( $property );
295 $this->adapter->exec( "CREATE INDEX $name ON $table ($column) " );
297 } catch ( SQLException $e ) {
303 * @see QueryWriter::addFK
306 public function addFK( $type, $targetType, $property, $targetProperty, $isDependent = FALSE )
308 $table = $this->esc( $type );
309 $targetTable = $this->esc( $targetType );
310 $targetTableNoQ = $this->esc( $targetType, TRUE );
311 $field = $this->esc( $property );
312 $fieldNoQ = $this->esc( $property, TRUE );
313 $targetField = $this->esc( $targetProperty );
314 $targetFieldNoQ = $this->esc( $targetProperty, TRUE );
315 $tableNoQ = $this->esc( $type, TRUE );
316 $fieldNoQ = $this->esc( $property, TRUE );
317 if ( !is_null( $this->getForeignKeyForTypeProperty( $tableNoQ, $fieldNoQ ) ) ) return FALSE;
319 //Widen the column if it's incapable of representing a foreign key (at least INT).
320 $columns = $this->getColumns( $tableNoQ );
321 $idType = $this->getTypeForID();
322 if ( $this->code( $columns[$fieldNoQ] ) !== $idType ) {
323 $this->widenColumn( $type, $property, $idType );
326 $fkName = 'fk_'.($tableNoQ.'_'.$fieldNoQ);
327 $cName = 'c_'.$fkName;
329 $this->adapter->exec( "
331 ADD CONSTRAINT $cName
332 FOREIGN KEY $fkName ( `{$fieldNoQ}` ) REFERENCES `{$targetTableNoQ}`
333 (`{$targetFieldNoQ}`) ON DELETE " . ( $isDependent ? 'CASCADE' : 'SET NULL' ) . ' ON UPDATE '.( $isDependent ? 'CASCADE' : 'SET NULL' ).';');
334 } catch ( SQLException $e ) {
335 // Failure of fk-constraints is not a problem
341 * @see QueryWriter::sqlStateIn
343 public function sqlStateIn( $state, $list )
346 '42S02' => QueryWriter::C_SQLSTATE_NO_SUCH_TABLE,
347 '42S22' => QueryWriter::C_SQLSTATE_NO_SUCH_COLUMN,
348 '23000' => QueryWriter::C_SQLSTATE_INTEGRITY_CONSTRAINT_VIOLATION
351 return in_array( ( isset( $stateMap[$state] ) ? $stateMap[$state] : '0' ), $list );
355 * @see QueryWriter::wipeAll
357 public function wipeAll()
359 $this->adapter->exec( 'SET FOREIGN_KEY_CHECKS = 0;' );
361 foreach ( $this->getTables() as $t ) {
363 $this->adapter->exec( "DROP TABLE IF EXISTS `$t`" );
364 } catch ( SQLException $e ) {
368 $this->adapter->exec( "DROP VIEW IF EXISTS `$t`" );
369 } catch ( SQLException $e ) {
373 $this->adapter->exec( 'SET FOREIGN_KEY_CHECKS = 1;' );