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: }
384: