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 PostgreSQL Query Writer.
13 * This is a QueryWriter class for RedBeanPHP.
14 * This QueryWriter provides support for the PostgreSQL database platform.
16 * @file RedBeanPHP/QueryWriter/PostgreSQL.php
17 * @author Gabor de Mooij and the RedBeanPHP Community
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.
25 class PostgreSQL extends AQueryWriter implements QueryWriter
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;
52 protected $quoteCharacter = '"';
57 protected $defaultValue = 'DEFAULT';
60 * Returns the insert suffix SQL Snippet
62 * @param string $table table
64 * @return string $sql SQL Snippet
66 protected function getInsertSuffix( $table )
68 return 'RETURNING id ';
72 * @see AQueryWriter::getKeyMapForType
74 protected function getKeyMapForType( $type )
76 $table = $this->esc( $type, TRUE );
77 $keys = $this->adapter->get( '
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
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
92 INNER JOIN information_schema.constraint_column_usage
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
98 INNER JOIN information_schema.referential_constraints
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
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 = ?
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'],
117 'on_update' => $k['on_update'],
118 'on_delete' => $k['on_delete']
127 * @param Adapter $adapter Database Adapter
129 public function __construct( Adapter $adapter )
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 ',
146 $this->sqltype_typeno = array();
148 foreach ( $this->typeno_sqltype as $k => $v ) {
149 $this->sqltype_typeno[trim( strtolower( $v ) )] = $k;
152 $this->adapter = $adapter;
156 * This method returns the datatype to be used for primary key IDS and
157 * foreign keys. Returns one if the data type constants.
161 public function getTypeForID()
163 return self::C_DATATYPE_INTEGER;
167 * @see QueryWriter::getTables
169 public function getTables()
171 return $this->adapter->getCol( 'SELECT table_name FROM information_schema.tables WHERE table_schema = ANY( current_schemas( FALSE ) )' );
175 * @see QueryWriter::createTable
177 public function createTable( $table )
179 $table = $this->esc( $table );
181 $this->adapter->exec( " CREATE TABLE $table (id SERIAL PRIMARY KEY); " );
185 * @see QueryWriter::getColumns
187 public function getColumns( $table )
189 $table = $this->esc( $table, TRUE );
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 ) )" );
194 foreach ( $columnsRaw as $r ) {
195 $columns[$r['column_name']] = $r['data_type'];
202 * @see QueryWriter::scanType
204 public function scanType( $value, $flagSpecial = FALSE )
206 $this->svalue = $value;
208 if ( $value === INF ) return self::C_DATATYPE_TEXT;
210 if ( $flagSpecial && $value ) {
211 if ( preg_match( '/^\d{4}\-\d\d-\d\d$/', $value ) ) {
212 return PostgreSQL::C_DATATYPE_SPECIAL_DATE;
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;
219 if ( preg_match( '/^\([\d\.]+,[\d\.]+\)$/', $value ) ) {
220 return PostgreSQL::C_DATATYPE_SPECIAL_POINT;
223 if ( preg_match( '/^\[\([\d\.]+,[\d\.]+\),\([\d\.]+,[\d\.]+\)\]$/', $value ) ) {
224 return PostgreSQL::C_DATATYPE_SPECIAL_LSEG;
227 if ( preg_match( '/^\<\([\d\.]+,[\d\.]+\),[\d\.]+\>$/', $value ) ) {
228 return PostgreSQL::C_DATATYPE_SPECIAL_CIRCLE;
231 if ( preg_match( '/^\((\([\d\.]+,[\d\.]+\),?)+\)$/', $value ) ) {
232 return PostgreSQL::C_DATATYPE_SPECIAL_POLYGON;
235 if ( preg_match( '/^\-?(\$|€|¥|£)[\d,\.]+$/', $value ) ) {
236 return PostgreSQL::C_DATATYPE_SPECIAL_MONEY;
239 if ( preg_match( '/^-?\d+\.\d{2}$/', $value ) ) {
240 return PostgreSQL::C_DATATYPE_SPECIAL_MONEY2;
244 if ( is_float( $value ) ) return self::C_DATATYPE_DOUBLE;
246 if ( $this->startsWithZeros( $value ) ) return self::C_DATATYPE_TEXT;
248 if ( $value === FALSE || $value === TRUE || $value === NULL || ( is_numeric( $value )
249 && AQueryWriter::canBeTreatedAsInt( $value )
250 && $value < 2147483648
251 && $value > -2147483648 )
253 return self::C_DATATYPE_INTEGER;
254 } elseif ( is_numeric( $value ) ) {
255 return self::C_DATATYPE_DOUBLE;
257 return self::C_DATATYPE_TEXT;
262 * @see QueryWriter::code
264 public function code( $typedescription, $includeSpecials = FALSE )
266 $r = ( isset( $this->sqltype_typeno[$typedescription] ) ) ? $this->sqltype_typeno[$typedescription] : 99;
268 if ( $includeSpecials ) return $r;
270 if ( $r >= QueryWriter::C_DATATYPE_RANGE_SPECIAL ) {
271 return self::C_DATATYPE_SPECIFIED;
278 * @see QueryWriter::widenColumn
280 public function widenColumn( $type, $column, $datatype )
285 $table = $this->esc( $table );
286 $column = $this->esc( $column );
288 $newtype = $this->typeno_sqltype[$type];
290 $this->adapter->exec( "ALTER TABLE $table \n\t ALTER COLUMN $column TYPE $newtype " );
294 * @see QueryWriter::addUniqueIndex
296 public function addUniqueConstraint( $type, $properties )
298 $tableNoQ = $this->esc( $type, TRUE );
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 ) . ")";
307 $this->adapter->exec( $sql );
308 } catch( SQLException $e ) {
315 * @see QueryWriter::sqlStateIn
317 public function sqlStateIn( $state, $list )
320 '42P01' => QueryWriter::C_SQLSTATE_NO_SUCH_TABLE,
321 '42703' => QueryWriter::C_SQLSTATE_NO_SUCH_COLUMN,
322 '23505' => QueryWriter::C_SQLSTATE_INTEGRITY_CONSTRAINT_VIOLATION
325 return in_array( ( isset( $stateMap[$state] ) ? $stateMap[$state] : '0' ), $list );
329 * @see QueryWriter::addIndex
331 public function addIndex( $type, $name, $property )
333 $table = $this->esc( $type );
334 $name = preg_replace( '/\W/', '', $name );
335 $column = $this->esc( $property );
338 $this->adapter->exec( "CREATE INDEX {$name} ON $table ({$column}) " );
340 } catch ( SQLException $e ) {
346 * @see QueryWriter::addFK
348 public function addFK( $type, $targetType, $property, $targetProperty, $isDep = FALSE )
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;
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 ;" );
363 } catch ( SQLException $e ) {
369 * @see QueryWriter::wipeAll
371 public function wipeAll()
373 $this->adapter->exec( 'SET CONSTRAINTS ALL DEFERRED' );
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 ) {}
381 $this->adapter->exec( 'SET CONSTRAINTS ALL IMMEDIATE' );