www.aleph1.co.uk Git - yaffs-website/blob - vendor/gabordemooij/redbean/RedBeanPHP/QueryWriter/MySQL.php

   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 }