1: <?php
  2: 
  3: namespace RedBeanPHP\QueryWriter;
  4: 
  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;
 10: 
 11: /**
 12:  * RedBeanPHP MySQLWriter.
 13:  * This is a QueryWriter class for RedBeanPHP.
 14:  * This QueryWriter provides support for the MySQL/MariaDB database platform.
 15:  *
 16:  * @file    RedBeanPHP/QueryWriter/MySQL.php
 17:  * @author  Gabor de Mooij and the RedBeanPHP Community
 18:  * @license BSD/GPLv2
 19:  *
 20:  * @copyright
 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.
 24:  */
 25: class MySQL extends AQueryWriter implements QueryWriter
 26: {
 27:     /**
 28:      * Data types
 29:      */
 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;
 43: 
 44:     const C_DATATYPE_SPECIFIED        = 99;
 45: 
 46:     /**
 47:      * @var DBAdapter
 48:      */
 49:     protected $adapter;
 50: 
 51:     /**
 52:      * @var string
 53:      */
 54:     protected $quoteCharacter = '`';
 55: 
 56:     /**
 57:      * @see AQueryWriter::getKeyMapForType
 58:      */
 59:     protected function getKeyMapForType( $type )
 60:     {
 61:         $databaseName = $this->adapter->getCell('SELECT DATABASE()');
 62:         $table = $this->esc( $type, TRUE );
 63:         $keys = $this->adapter->get('
 64:             SELECT
 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
 74:             WHERE
 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(
 86:                 'name'          => $k['name'],
 87:                 'from'          => $k['from'],
 88:                 'table'         => $k['table'],
 89:                 'to'            => $k['to'],
 90:                 'on_update'     => $k['on_update'],
 91:                 'on_delete'     => $k['on_delete']
 92:             );
 93:         }
 94:         return $keyInfoList;
 95:     }
 96: 
 97:     /**
 98:      * Constructor
 99:      *
100:      * @param Adapter $adapter Database Adapter
101:      */
102:     public function __construct( Adapter $adapter )
103:     {
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) '
118:         );
119: 
120:         $this->sqltype_typeno = array();
121: 
122:         foreach ( $this->typeno_sqltype as $k => $v ) {
123:             $this->sqltype_typeno[trim( strtolower( $v ) )] = $k;
124:         }
125: 
126:         $this->adapter = $adapter;
127: 
128:         $this->encoding = $this->adapter->getDatabase()->getMysqlEncoding();
129:     }
130: 
131:     /**
132:      * This method returns the datatype to be used for primary key IDS and
133:      * foreign keys. Returns one if the data type constants.
134:      *
135:      * @return integer
136:      */
137:     public function getTypeForID()
138:     {
139:         return self::C_DATATYPE_UINT32;
140:     }
141: 
142:     /**
143:      * @see QueryWriter::getTables
144:      */
145:     public function getTables()
146:     {
147:         return $this->adapter->getCol( 'show tables' );
148:     }
149: 
150:     /**
151:      * @see QueryWriter::createTable
152:      */
153:     public function createTable( $table )
154:     {
155:         $table = $this->esc( $table );
156: 
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 ";
159: 
160:         $this->adapter->exec( $sql );
161:     }
162: 
163:     /**
164:      * @see QueryWriter::getColumns
165:      */
166:     public function getColumns( $table )
167:     {
168:         $columnsRaw = $this->adapter->get( "DESCRIBE " . $this->esc( $table ) );
169: 
170:         $columns = array();
171:         foreach ( $columnsRaw as $r ) {
172:             $columns[$r['Field']] = $r['Type'];
173:         }
174: 
175:         return $columns;
176:     }
177: 
178:     /**
179:      * @see QueryWriter::scanType
180:      */
181:     public function scanType( $value, $flagSpecial = FALSE )
182:     {
183:         $this->svalue = $value;
184: 
185:         if ( is_null( $value ) ) return MySQL::C_DATATYPE_BOOL;
186:         if ( $value === INF ) return MySQL::C_DATATYPE_TEXT7;
187: 
188:         if ( $flagSpecial ) {
189:             if ( preg_match( '/^-?\d+\.\d{2}$/', $value ) ) {
190:                 return MySQL::C_DATATYPE_SPECIAL_MONEY;
191:             }
192:             if ( preg_match( '/^\d{4}\-\d\d-\d\d$/', $value ) ) {
193:                 return MySQL::C_DATATYPE_SPECIAL_DATE;
194:             }
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;
197:             }
198:             if ( preg_match( '/^POINT\(/', $value ) ) {
199:                 return MySQL::C_DATATYPE_SPECIAL_POINT;
200:             }
201:             if ( preg_match( '/^LINESTRING\(/', $value ) ) {
202:                 return MySQL::C_DATATYPE_SPECIAL_LINESTRING;
203:             }
204:             if ( preg_match( '/^POLYGON\(/', $value ) ) {
205:                 return MySQL::C_DATATYPE_SPECIAL_POLYGON;
206:             }
207:         }
208: 
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;
212:         }
213: 
214:         if ( is_float( $value ) ) return self::C_DATATYPE_DOUBLE;
215: 
216:         if ( !$this->startsWithZeros( $value ) ) {
217: 
218:             if ( is_numeric( $value ) && ( floor( $value ) == $value ) && $value >= 0 && $value <= 4294967295 ) {
219:                 return MySQL::C_DATATYPE_UINT32;
220:             }
221: 
222:             if ( is_numeric( $value ) ) {
223:                 return MySQL::C_DATATYPE_DOUBLE;
224:             }
225:         }
226: 
227:         if ( mb_strlen( $value, 'UTF-8' ) <= 191 ) {
228:             return MySQL::C_DATATYPE_TEXT7;
229:         }
230: 
231:         if ( mb_strlen( $value, 'UTF-8' ) <= 255 ) {
232:             return MySQL::C_DATATYPE_TEXT8;
233:         }
234: 
235:         if ( mb_strlen( $value, 'UTF-8' ) <= 65535 ) {
236:             return MySQL::C_DATATYPE_TEXT16;
237:         }
238: 
239:         return MySQL::C_DATATYPE_TEXT32;
240:     }
241: 
242:     /**
243:      * @see QueryWriter::code
244:      */
245:     public function code( $typedescription, $includeSpecials = FALSE )
246:     {
247:         if ( isset( $this->sqltype_typeno[$typedescription] ) ) {
248:             $r = $this->sqltype_typeno[$typedescription];
249:         } else {
250:             $r = self::C_DATATYPE_SPECIFIED;
251:         }
252: 
253:         if ( $includeSpecials ) {
254:             return $r;
255:         }
256: 
257:         if ( $r >= QueryWriter::C_DATATYPE_RANGE_SPECIAL ) {
258:             return self::C_DATATYPE_SPECIFIED;
259:         }
260: 
261:         return $r;
262:     }
263: 
264:     /**
265:      * @see QueryWriter::addUniqueIndex
266:      */
267:     public function addUniqueConstraint( $type, $properties )
268:     {
269:         $tableNoQ = $this->esc( $type, TRUE );
270:         $columns = array();
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 ) );
275:         try {
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!
281:             return FALSE;
282:         }
283:         return TRUE;
284:     }
285: 
286:     /**
287:      * @see QueryWriter::addIndex
288:      */
289:     public function addIndex( $type, $name, $property )
290:     {
291:         try {
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) " );
296:             return TRUE;
297:         } catch ( SQLException $e ) {
298:             return FALSE;
299:         }
300:     }
301: 
302:     /**
303:      * @see QueryWriter::addFK
304:      * @return bool
305:      */
306:     public function addFK( $type, $targetType, $property, $targetProperty, $isDependent = FALSE )
307:     {
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;
318: 
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 );
324:         }
325: 
326:         $fkName = 'fk_'.($tableNoQ.'_'.$fieldNoQ);
327:         $cName = 'c_'.$fkName;
328:         try {
329:             $this->adapter->exec( "
330:                 ALTER TABLE {$table}
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
336:         }
337:         return true;
338:     }
339: 
340:     /**
341:      * @see QueryWriter::sqlStateIn
342:      */
343:     public function sqlStateIn( $state, $list )
344:     {
345:         $stateMap = array(
346:             '42S02' => QueryWriter::C_SQLSTATE_NO_SUCH_TABLE,
347:             '42S22' => QueryWriter::C_SQLSTATE_NO_SUCH_COLUMN,
348:             '23000' => QueryWriter::C_SQLSTATE_INTEGRITY_CONSTRAINT_VIOLATION
349:         );
350: 
351:         return in_array( ( isset( $stateMap[$state] ) ? $stateMap[$state] : '0' ), $list );
352:     }
353: 
354:     /**
355:      * @see QueryWriter::wipeAll
356:      */
357:     public function wipeAll()
358:     {
359:         $this->adapter->exec( 'SET FOREIGN_KEY_CHECKS = 0;' );
360: 
361:         foreach ( $this->getTables() as $t ) {
362:             try {
363:                 $this->adapter->exec( "DROP TABLE IF EXISTS `$t`" );
364:             } catch ( SQLException $e ) {
365:             }
366: 
367:             try {
368:                 $this->adapter->exec( "DROP VIEW IF EXISTS `$t`" );
369:             } catch ( SQLException $e ) {
370:             }
371:         }
372: 
373:         $this->adapter->exec( 'SET FOREIGN_KEY_CHECKS = 1;' );
374:     }
375: }
376: