www.aleph1.co.uk Git - yaffs-website/blob - vendor/gabordemooij/redbean/RedBeanPHP/QueryWriter/PostgreSQL.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 PostgreSQL Query Writer.
  13  * This is a QueryWriter class for RedBeanPHP.
  14  * This QueryWriter provides support for the PostgreSQL database platform.
  15  *
  16  * @file    RedBeanPHP/QueryWriter/PostgreSQL.php
  17  * @author  Gabor de Mooij and the RedBeanPHP Community
  18  * @license BSD/GPLv2
  19  *
  20  * @copyright
  21  * (c) copyright 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 PostgreSQL extends AQueryWriter implements QueryWriter
  26 {
  27         /**
  28          * Data types
  29          */
  30         const C_DATATYPE_INTEGER          = 0;
  31         const C_DATATYPE_DOUBLE           = 1;
  32         const C_DATATYPE_TEXT             = 3;
  33         const C_DATATYPE_SPECIAL_DATE     = 80;
  34         const C_DATATYPE_SPECIAL_DATETIME = 81;
  35         const C_DATATYPE_SPECIAL_POINT    = 90;
  36         const C_DATATYPE_SPECIAL_LSEG     = 91;
  37         const C_DATATYPE_SPECIAL_CIRCLE   = 92;
  38         const C_DATATYPE_SPECIAL_MONEY    = 93;
  39         const C_DATATYPE_SPECIAL_POLYGON  = 94;
  40         const C_DATATYPE_SPECIAL_MONEY2   = 95; //Numbers only money, i.e. fixed point numeric
  41         const C_DATATYPE_SPECIAL_JSON     = 96; //JSON support (only manual)
  42         const C_DATATYPE_SPECIFIED        = 99;
  43
  44         /**
  45          * @var DBAdapter
  46          */
  47         protected $adapter;
  48
  49         /**
  50          * @var string
  51          */
  52         protected $quoteCharacter = '"';
  53
  54         /**
  55          * @var string
  56          */
  57         protected $defaultValue = 'DEFAULT';
  58
  59         /**
  60          * Returns the insert suffix SQL Snippet
  61          *
  62          * @param string $table table
  63          *
  64          * @return  string $sql SQL Snippet
  65          */
  66         protected function getInsertSuffix( $table )
  67         {
  68                 return 'RETURNING id ';
  69         }
  70
  71         /**
  72          * @see AQueryWriter::getKeyMapForType
  73          */
  74         protected function getKeyMapForType( $type )
  75         {
  76                 $table = $this->esc( $type, TRUE );
  77                 $keys = $this->adapter->get( '
  78                         SELECT
  79                         information_schema.key_column_usage.constraint_name AS "name",
  80                         information_schema.key_column_usage.column_name AS "from",
  81                         information_schema.constraint_table_usage.table_name AS "table",
  82                         information_schema.constraint_column_usage.column_name AS "to",
  83                         information_schema.referential_constraints.update_rule AS "on_update",
  84                         information_schema.referential_constraints.delete_rule AS "on_delete"
  85                                 FROM information_schema.key_column_usage
  86                         INNER JOIN information_schema.constraint_table_usage
  87                                 ON (
  88                                         information_schema.key_column_usage.constraint_name = information_schema.constraint_table_usage.constraint_name
  89                                         AND information_schema.key_column_usage.constraint_schema = information_schema.constraint_table_usage.constraint_schema
  90                                         AND information_schema.key_column_usage.constraint_catalog = information_schema.constraint_table_usage.constraint_catalog
  91                                 )
  92                         INNER JOIN information_schema.constraint_column_usage
  93                                 ON (
  94                                         information_schema.key_column_usage.constraint_name = information_schema.constraint_column_usage.constraint_name
  95                                         AND information_schema.key_column_usage.constraint_schema = information_schema.constraint_column_usage.constraint_schema
  96                                         AND information_schema.key_column_usage.constraint_catalog = information_schema.constraint_column_usage.constraint_catalog
  97                                 )
  98                         INNER JOIN information_schema.referential_constraints
  99                                 ON (
 100                                         information_schema.key_column_usage.constraint_name = information_schema.referential_constraints.constraint_name
 101                                         AND information_schema.key_column_usage.constraint_schema = information_schema.referential_constraints.constraint_schema
 102                                         AND information_schema.key_column_usage.constraint_catalog = information_schema.referential_constraints.constraint_catalog
 103                                 )
 104                         WHERE
 105                                 information_schema.key_column_usage.table_catalog = current_database()
 106                                 AND information_schema.key_column_usage.table_schema = ANY( current_schemas( FALSE ) )
 107                                 AND information_schema.key_column_usage.table_name = ?
 108                 ', array( $type ) );
 109                 $keyInfoList = array();
 110                 foreach ( $keys as $k ) {
 111                         $label = $this->makeFKLabel( $k['from'], $k['table'], $k['to'] );
 112                         $keyInfoList[$label] = array(
 113                                 'name'          => $k['name'],
 114                                 'from'          => $k['from'],
 115                                 'table'         => $k['table'],
 116                                 'to'            => $k['to'],
 117                                 'on_update'     => $k['on_update'],
 118                                 'on_delete'     => $k['on_delete']
 119                         );
 120                 }
 121                 return $keyInfoList;
 122         }
 123
 124         /**
 125          * Constructor
 126          *
 127          * @param Adapter $adapter Database Adapter
 128          */
 129         public function __construct( Adapter $adapter )
 130         {
 131                 $this->typeno_sqltype = array(
 132                         self::C_DATATYPE_INTEGER          => ' integer ',
 133                         self::C_DATATYPE_DOUBLE           => ' double precision ',
 134                         self::C_DATATYPE_TEXT             => ' text ',
 135                         self::C_DATATYPE_SPECIAL_DATE     => ' date ',
 136                         self::C_DATATYPE_SPECIAL_DATETIME => ' timestamp without time zone ',
 137                         self::C_DATATYPE_SPECIAL_POINT    => ' point ',
 138                         self::C_DATATYPE_SPECIAL_LSEG     => ' lseg ',
 139                         self::C_DATATYPE_SPECIAL_CIRCLE   => ' circle ',
 140                         self::C_DATATYPE_SPECIAL_MONEY    => ' money ',
 141                         self::C_DATATYPE_SPECIAL_MONEY2   => ' numeric(10,2) ',
 142                         self::C_DATATYPE_SPECIAL_POLYGON  => ' polygon ',
 143                         self::C_DATATYPE_SPECIAL_JSON     => ' json ',
 144                 );
 145
 146                 $this->sqltype_typeno = array();
 147
 148                 foreach ( $this->typeno_sqltype as $k => $v ) {
 149                         $this->sqltype_typeno[trim( strtolower( $v ) )] = $k;
 150                 }
 151
 152                 $this->adapter = $adapter;
 153         }
 154
 155         /**
 156          * This method returns the datatype to be used for primary key IDS and
 157          * foreign keys. Returns one if the data type constants.
 158          *
 159          * @return integer
 160          */
 161         public function getTypeForID()
 162         {
 163                 return self::C_DATATYPE_INTEGER;
 164         }
 165
 166         /**
 167          * @see QueryWriter::getTables
 168          */
 169         public function getTables()
 170         {
 171                 return $this->adapter->getCol( 'SELECT table_name FROM information_schema.tables WHERE table_schema = ANY( current_schemas( FALSE ) )' );
 172         }
 173
 174         /**
 175          * @see QueryWriter::createTable
 176          */
 177         public function createTable( $table )
 178         {
 179                 $table = $this->esc( $table );
 180
 181                 $this->adapter->exec( " CREATE TABLE $table (id SERIAL PRIMARY KEY); " );
 182         }
 183
 184         /**
 185          * @see QueryWriter::getColumns
 186          */
 187         public function getColumns( $table )
 188         {
 189                 $table      = $this->esc( $table, TRUE );
 190
 191                 $columnsRaw = $this->adapter->get( "SELECT column_name, data_type FROM information_schema.columns WHERE table_name='$table' AND table_schema = ANY( current_schemas( FALSE ) )" );
 192
 193                 $columns = array();
 194                 foreach ( $columnsRaw as $r ) {
 195                         $columns[$r['column_name']] = $r['data_type'];
 196                 }
 197
 198                 return $columns;
 199         }
 200
 201         /**
 202          * @see QueryWriter::scanType
 203          */
 204         public function scanType( $value, $flagSpecial = FALSE )
 205         {
 206                 $this->svalue = $value;
 207
 208                 if ( $value === INF ) return self::C_DATATYPE_TEXT;
 209
 210                 if ( $flagSpecial && $value ) {
 211                         if ( preg_match( '/^\d{4}\-\d\d-\d\d$/', $value ) ) {
 212                                 return PostgreSQL::C_DATATYPE_SPECIAL_DATE;
 213                         }
 214
 215                         if ( preg_match( '/^\d{4}\-\d\d-\d\d\s\d\d:\d\d:\d\d(\.\d{1,6})?$/', $value ) ) {
 216                                 return PostgreSQL::C_DATATYPE_SPECIAL_DATETIME;
 217                         }
 218
 219                         if ( preg_match( '/^\([\d\.]+,[\d\.]+\)$/', $value ) ) {
 220                                 return PostgreSQL::C_DATATYPE_SPECIAL_POINT;
 221                         }
 222
 223                         if ( preg_match( '/^\[\([\d\.]+,[\d\.]+\),\([\d\.]+,[\d\.]+\)\]$/', $value ) ) {
 224                                 return PostgreSQL::C_DATATYPE_SPECIAL_LSEG;
 225                         }
 226
 227                         if ( preg_match( '/^\<\([\d\.]+,[\d\.]+\),[\d\.]+\>$/', $value ) ) {
 228                                 return PostgreSQL::C_DATATYPE_SPECIAL_CIRCLE;
 229                         }
 230
 231                         if ( preg_match( '/^\((\([\d\.]+,[\d\.]+\),?)+\)$/', $value ) ) {
 232                                 return PostgreSQL::C_DATATYPE_SPECIAL_POLYGON;
 233                         }
 234
 235                         if ( preg_match( '/^\-?(\$|€|¥|£)[\d,\.]+$/', $value ) ) {
 236                                 return PostgreSQL::C_DATATYPE_SPECIAL_MONEY;
 237                         }
 238
 239                         if ( preg_match( '/^-?\d+\.\d{2}$/', $value ) ) {
 240                                 return PostgreSQL::C_DATATYPE_SPECIAL_MONEY2;
 241                         }
 242                 }
 243
 244                 if ( is_float( $value ) ) return self::C_DATATYPE_DOUBLE;
 245
 246                 if ( $this->startsWithZeros( $value ) ) return self::C_DATATYPE_TEXT;
 247
 248                 if ( $value === FALSE || $value === TRUE || $value === NULL || ( is_numeric( $value )
 249                                 && AQueryWriter::canBeTreatedAsInt( $value )
 250                                 && $value < 2147483648
 251                                 && $value > -2147483648 )
 252                 ) {
 253                         return self::C_DATATYPE_INTEGER;
 254                 } elseif ( is_numeric( $value ) ) {
 255                         return self::C_DATATYPE_DOUBLE;
 256                 } else {
 257                         return self::C_DATATYPE_TEXT;
 258                 }
 259         }
 260
 261         /**
 262          * @see QueryWriter::code
 263          */
 264         public function code( $typedescription, $includeSpecials = FALSE )
 265         {
 266                 $r = ( isset( $this->sqltype_typeno[$typedescription] ) ) ? $this->sqltype_typeno[$typedescription] : 99;
 267
 268                 if ( $includeSpecials ) return $r;
 269
 270                 if ( $r >= QueryWriter::C_DATATYPE_RANGE_SPECIAL ) {
 271                         return self::C_DATATYPE_SPECIFIED;
 272                 }
 273
 274                 return $r;
 275         }
 276
 277         /**
 278          * @see QueryWriter::widenColumn
 279          */
 280         public function widenColumn( $type, $column, $datatype )
 281         {
 282                 $table   = $type;
 283                 $type    = $datatype;
 284
 285                 $table   = $this->esc( $table );
 286                 $column  = $this->esc( $column );
 287
 288                 $newtype = $this->typeno_sqltype[$type];
 289
 290                 $this->adapter->exec( "ALTER TABLE $table \n\t ALTER COLUMN $column TYPE $newtype " );
 291         }
 292
 293         /**
 294          * @see QueryWriter::addUniqueIndex
 295          */
 296         public function addUniqueConstraint( $type, $properties )
 297         {
 298                 $tableNoQ = $this->esc( $type, TRUE );
 299                 $columns = array();
 300                 foreach( $properties as $key => $column ) $columns[$key] = $this->esc( $column );
 301                 $table = $this->esc( $type );
 302                 sort( $columns ); //else we get multiple indexes due to order-effects
 303                 $name = "UQ_" . sha1( $table . implode( ',', $columns ) );
 304                 $sql = "ALTER TABLE {$table}
 305                 ADD CONSTRAINT $name UNIQUE (" . implode( ',', $columns ) . ")";
 306                 try {
 307                         $this->adapter->exec( $sql );
 308                 } catch( SQLException $e ) {
 309                         return FALSE;
 310                 }
 311                 return TRUE;
 312         }
 313
 314         /**
 315          * @see QueryWriter::sqlStateIn
 316          */
 317         public function sqlStateIn( $state, $list )
 318         {
 319                 $stateMap = array(
 320                         '42P01' => QueryWriter::C_SQLSTATE_NO_SUCH_TABLE,
 321                         '42703' => QueryWriter::C_SQLSTATE_NO_SUCH_COLUMN,
 322                         '23505' => QueryWriter::C_SQLSTATE_INTEGRITY_CONSTRAINT_VIOLATION
 323                 );
 324
 325                 return in_array( ( isset( $stateMap[$state] ) ? $stateMap[$state] : '0' ), $list );
 326         }
 327
 328         /**
 329          * @see QueryWriter::addIndex
 330          */
 331         public function addIndex( $type, $name, $property )
 332         {
 333                 $table  = $this->esc( $type );
 334                 $name   = preg_replace( '/\W/', '', $name );
 335                 $column = $this->esc( $property );
 336
 337                 try {
 338                         $this->adapter->exec( "CREATE INDEX {$name} ON $table ({$column}) " );
 339                         return TRUE;
 340                 } catch ( SQLException $e ) {
 341                         return FALSE;
 342                 }
 343         }
 344
 345         /**
 346          * @see QueryWriter::addFK
 347          */
 348         public function addFK( $type, $targetType, $property, $targetProperty, $isDep = FALSE )
 349         {
 350                 $table = $this->esc( $type );
 351                 $targetTable = $this->esc( $targetType );
 352                 $field = $this->esc( $property );
 353                 $targetField = $this->esc( $targetProperty );
 354                 $tableNoQ = $this->esc( $type, TRUE );
 355                 $fieldNoQ = $this->esc( $property, TRUE );
 356                 if ( !is_null( $this->getForeignKeyForTypeProperty( $tableNoQ, $fieldNoQ ) ) ) return FALSE;
 357                 try{
 358                         $delRule = ( $isDep ? 'CASCADE' : 'SET NULL' );
 359                         $this->adapter->exec( "ALTER TABLE {$table}
 360                                 ADD FOREIGN KEY ( {$field} ) REFERENCES  {$targetTable}
 361                                 ({$targetField}) ON DELETE {$delRule} ON UPDATE {$delRule} DEFERRABLE ;" );
 362                         return TRUE;
 363                 } catch ( SQLException $e ) {
 364                         return FALSE;
 365                 }
 366         }
 367
 368         /**
 369          * @see QueryWriter::wipeAll
 370          */
 371         public function wipeAll()
 372         {
 373                 $this->adapter->exec( 'SET CONSTRAINTS ALL DEFERRED' );
 374
 375                 foreach ( $this->getTables() as $t ) {
 376                         $t = $this->esc( $t );
 377                         //Some plugins (PostGIS have unremovable tables/views), avoid exceptions.
 378                         try { $this->adapter->exec( "DROP TABLE IF EXISTS $t CASCADE " ); }catch( \Exception $e ) {}
 379                 }
 380
 381                 $this->adapter->exec( 'SET CONSTRAINTS ALL IMMEDIATE' );
 382         }
 383 }