--- /dev/null
+<?php
+
+namespace RedBeanPHP\QueryWriter;
+
+use RedBeanPHP\QueryWriter\AQueryWriter as AQueryWriter;
+use RedBeanPHP\QueryWriter as QueryWriter;
+use RedBeanPHP\Adapter\DBAdapter as DBAdapter;
+use RedBeanPHP\Adapter as Adapter;
+use RedBeanPHP\RedException\SQL as SQLException;
+
+/**
+ * RedBeanPHP PostgreSQL Query Writer.
+ * This is a QueryWriter class for RedBeanPHP.
+ * This QueryWriter provides support for the PostgreSQL database platform.
+ *
+ * @file RedBeanPHP/QueryWriter/PostgreSQL.php
+ * @author Gabor de Mooij and the RedBeanPHP Community
+ * @license BSD/GPLv2
+ *
+ * @copyright
+ * (c) copyright G.J.G.T. (Gabor) de Mooij and the RedBeanPHP Community.
+ * This source file is subject to the BSD/GPLv2 License that is bundled
+ * with this source code in the file license.txt.
+ */
+class PostgreSQL extends AQueryWriter implements QueryWriter
+{
+ /**
+ * Data types
+ */
+ const C_DATATYPE_INTEGER = 0;
+ const C_DATATYPE_DOUBLE = 1;
+ const C_DATATYPE_TEXT = 3;
+ const C_DATATYPE_SPECIAL_DATE = 80;
+ const C_DATATYPE_SPECIAL_DATETIME = 81;
+ const C_DATATYPE_SPECIAL_POINT = 90;
+ const C_DATATYPE_SPECIAL_LSEG = 91;
+ const C_DATATYPE_SPECIAL_CIRCLE = 92;
+ const C_DATATYPE_SPECIAL_MONEY = 93;
+ const C_DATATYPE_SPECIAL_POLYGON = 94;
+ const C_DATATYPE_SPECIAL_MONEY2 = 95; //Numbers only money, i.e. fixed point numeric
+ const C_DATATYPE_SPECIAL_JSON = 96; //JSON support (only manual)
+ const C_DATATYPE_SPECIFIED = 99;
+
+ /**
+ * @var DBAdapter
+ */
+ protected $adapter;
+
+ /**
+ * @var string
+ */
+ protected $quoteCharacter = '"';
+
+ /**
+ * @var string
+ */
+ protected $defaultValue = 'DEFAULT';
+
+ /**
+ * Returns the insert suffix SQL Snippet
+ *
+ * @param string $table table
+ *
+ * @return string $sql SQL Snippet
+ */
+ protected function getInsertSuffix( $table )
+ {
+ return 'RETURNING id ';
+ }
+
+ /**
+ * @see AQueryWriter::getKeyMapForType
+ */
+ protected function getKeyMapForType( $type )
+ {
+ $table = $this->esc( $type, TRUE );
+ $keys = $this->adapter->get( '
+ SELECT
+ information_schema.key_column_usage.constraint_name AS "name",
+ information_schema.key_column_usage.column_name AS "from",
+ information_schema.constraint_table_usage.table_name AS "table",
+ information_schema.constraint_column_usage.column_name AS "to",
+ information_schema.referential_constraints.update_rule AS "on_update",
+ information_schema.referential_constraints.delete_rule AS "on_delete"
+ FROM information_schema.key_column_usage
+ INNER JOIN information_schema.constraint_table_usage
+ ON (
+ information_schema.key_column_usage.constraint_name = information_schema.constraint_table_usage.constraint_name
+ AND information_schema.key_column_usage.constraint_schema = information_schema.constraint_table_usage.constraint_schema
+ AND information_schema.key_column_usage.constraint_catalog = information_schema.constraint_table_usage.constraint_catalog
+ )
+ INNER JOIN information_schema.constraint_column_usage
+ ON (
+ information_schema.key_column_usage.constraint_name = information_schema.constraint_column_usage.constraint_name
+ AND information_schema.key_column_usage.constraint_schema = information_schema.constraint_column_usage.constraint_schema
+ AND information_schema.key_column_usage.constraint_catalog = information_schema.constraint_column_usage.constraint_catalog
+ )
+ INNER JOIN information_schema.referential_constraints
+ ON (
+ information_schema.key_column_usage.constraint_name = information_schema.referential_constraints.constraint_name
+ AND information_schema.key_column_usage.constraint_schema = information_schema.referential_constraints.constraint_schema
+ AND information_schema.key_column_usage.constraint_catalog = information_schema.referential_constraints.constraint_catalog
+ )
+ WHERE
+ information_schema.key_column_usage.table_catalog = current_database()
+ AND information_schema.key_column_usage.table_schema = ANY( current_schemas( FALSE ) )
+ AND information_schema.key_column_usage.table_name = ?
+ ', array( $type ) );
+ $keyInfoList = array();
+ foreach ( $keys as $k ) {
+ $label = $this->makeFKLabel( $k['from'], $k['table'], $k['to'] );
+ $keyInfoList[$label] = array(
+ 'name' => $k['name'],
+ 'from' => $k['from'],
+ 'table' => $k['table'],
+ 'to' => $k['to'],
+ 'on_update' => $k['on_update'],
+ 'on_delete' => $k['on_delete']
+ );
+ }
+ return $keyInfoList;
+ }
+
+ /**
+ * Constructor
+ *
+ * @param Adapter $adapter Database Adapter
+ */
+ public function __construct( Adapter $adapter )
+ {
+ $this->typeno_sqltype = array(
+ self::C_DATATYPE_INTEGER => ' integer ',
+ self::C_DATATYPE_DOUBLE => ' double precision ',
+ self::C_DATATYPE_TEXT => ' text ',
+ self::C_DATATYPE_SPECIAL_DATE => ' date ',
+ self::C_DATATYPE_SPECIAL_DATETIME => ' timestamp without time zone ',
+ self::C_DATATYPE_SPECIAL_POINT => ' point ',
+ self::C_DATATYPE_SPECIAL_LSEG => ' lseg ',
+ self::C_DATATYPE_SPECIAL_CIRCLE => ' circle ',
+ self::C_DATATYPE_SPECIAL_MONEY => ' money ',
+ self::C_DATATYPE_SPECIAL_MONEY2 => ' numeric(10,2) ',
+ self::C_DATATYPE_SPECIAL_POLYGON => ' polygon ',
+ self::C_DATATYPE_SPECIAL_JSON => ' json ',
+ );
+
+ $this->sqltype_typeno = array();
+
+ foreach ( $this->typeno_sqltype as $k => $v ) {
+ $this->sqltype_typeno[trim( strtolower( $v ) )] = $k;
+ }
+
+ $this->adapter = $adapter;
+ }
+
+ /**
+ * This method returns the datatype to be used for primary key IDS and
+ * foreign keys. Returns one if the data type constants.
+ *
+ * @return integer
+ */
+ public function getTypeForID()
+ {
+ return self::C_DATATYPE_INTEGER;
+ }
+
+ /**
+ * @see QueryWriter::getTables
+ */
+ public function getTables()
+ {
+ return $this->adapter->getCol( 'SELECT table_name FROM information_schema.tables WHERE table_schema = ANY( current_schemas( FALSE ) )' );
+ }
+
+ /**
+ * @see QueryWriter::createTable
+ */
+ public function createTable( $table )
+ {
+ $table = $this->esc( $table );
+
+ $this->adapter->exec( " CREATE TABLE $table (id SERIAL PRIMARY KEY); " );
+ }
+
+ /**
+ * @see QueryWriter::getColumns
+ */
+ public function getColumns( $table )
+ {
+ $table = $this->esc( $table, TRUE );
+
+ $columnsRaw = $this->adapter->get( "SELECT column_name, data_type FROM information_schema.columns WHERE table_name='$table' AND table_schema = ANY( current_schemas( FALSE ) )" );
+
+ $columns = array();
+ foreach ( $columnsRaw as $r ) {
+ $columns[$r['column_name']] = $r['data_type'];
+ }
+
+ return $columns;
+ }
+
+ /**
+ * @see QueryWriter::scanType
+ */
+ public function scanType( $value, $flagSpecial = FALSE )
+ {
+ $this->svalue = $value;
+
+ if ( $value === INF ) return self::C_DATATYPE_TEXT;
+
+ if ( $flagSpecial && $value ) {
+ if ( preg_match( '/^\d{4}\-\d\d-\d\d$/', $value ) ) {
+ return PostgreSQL::C_DATATYPE_SPECIAL_DATE;
+ }
+
+ if ( preg_match( '/^\d{4}\-\d\d-\d\d\s\d\d:\d\d:\d\d(\.\d{1,6})?$/', $value ) ) {
+ return PostgreSQL::C_DATATYPE_SPECIAL_DATETIME;
+ }
+
+ if ( preg_match( '/^\([\d\.]+,[\d\.]+\)$/', $value ) ) {
+ return PostgreSQL::C_DATATYPE_SPECIAL_POINT;
+ }
+
+ if ( preg_match( '/^\[\([\d\.]+,[\d\.]+\),\([\d\.]+,[\d\.]+\)\]$/', $value ) ) {
+ return PostgreSQL::C_DATATYPE_SPECIAL_LSEG;
+ }
+
+ if ( preg_match( '/^\<\([\d\.]+,[\d\.]+\),[\d\.]+\>$/', $value ) ) {
+ return PostgreSQL::C_DATATYPE_SPECIAL_CIRCLE;
+ }
+
+ if ( preg_match( '/^\((\([\d\.]+,[\d\.]+\),?)+\)$/', $value ) ) {
+ return PostgreSQL::C_DATATYPE_SPECIAL_POLYGON;
+ }
+
+ if ( preg_match( '/^\-?(\$|€|¥|£)[\d,\.]+$/', $value ) ) {
+ return PostgreSQL::C_DATATYPE_SPECIAL_MONEY;
+ }
+
+ if ( preg_match( '/^-?\d+\.\d{2}$/', $value ) ) {
+ return PostgreSQL::C_DATATYPE_SPECIAL_MONEY2;
+ }
+ }
+
+ if ( is_float( $value ) ) return self::C_DATATYPE_DOUBLE;
+
+ if ( $this->startsWithZeros( $value ) ) return self::C_DATATYPE_TEXT;
+
+ if ( $value === FALSE || $value === TRUE || $value === NULL || ( is_numeric( $value )
+ && AQueryWriter::canBeTreatedAsInt( $value )
+ && $value < 2147483648
+ && $value > -2147483648 )
+ ) {
+ return self::C_DATATYPE_INTEGER;
+ } elseif ( is_numeric( $value ) ) {
+ return self::C_DATATYPE_DOUBLE;
+ } else {
+ return self::C_DATATYPE_TEXT;
+ }
+ }
+
+ /**
+ * @see QueryWriter::code
+ */
+ public function code( $typedescription, $includeSpecials = FALSE )
+ {
+ $r = ( isset( $this->sqltype_typeno[$typedescription] ) ) ? $this->sqltype_typeno[$typedescription] : 99;
+
+ if ( $includeSpecials ) return $r;
+
+ if ( $r >= QueryWriter::C_DATATYPE_RANGE_SPECIAL ) {
+ return self::C_DATATYPE_SPECIFIED;
+ }
+
+ return $r;
+ }
+
+ /**
+ * @see QueryWriter::widenColumn
+ */
+ public function widenColumn( $type, $column, $datatype )
+ {
+ $table = $type;
+ $type = $datatype;
+
+ $table = $this->esc( $table );
+ $column = $this->esc( $column );
+
+ $newtype = $this->typeno_sqltype[$type];
+
+ $this->adapter->exec( "ALTER TABLE $table \n\t ALTER COLUMN $column TYPE $newtype " );
+ }
+
+ /**
+ * @see QueryWriter::addUniqueIndex
+ */
+ public function addUniqueConstraint( $type, $properties )
+ {
+ $tableNoQ = $this->esc( $type, TRUE );
+ $columns = array();
+ foreach( $properties as $key => $column ) $columns[$key] = $this->esc( $column );
+ $table = $this->esc( $type );
+ sort( $columns ); //else we get multiple indexes due to order-effects
+ $name = "UQ_" . sha1( $table . implode( ',', $columns ) );
+ $sql = "ALTER TABLE {$table}
+ ADD CONSTRAINT $name UNIQUE (" . implode( ',', $columns ) . ")";
+ try {
+ $this->adapter->exec( $sql );
+ } catch( SQLException $e ) {
+ return FALSE;
+ }
+ return TRUE;
+ }
+
+ /**
+ * @see QueryWriter::sqlStateIn
+ */
+ public function sqlStateIn( $state, $list )
+ {
+ $stateMap = array(
+ '42P01' => QueryWriter::C_SQLSTATE_NO_SUCH_TABLE,
+ '42703' => QueryWriter::C_SQLSTATE_NO_SUCH_COLUMN,
+ '23505' => QueryWriter::C_SQLSTATE_INTEGRITY_CONSTRAINT_VIOLATION
+ );
+
+ return in_array( ( isset( $stateMap[$state] ) ? $stateMap[$state] : '0' ), $list );
+ }
+
+ /**
+ * @see QueryWriter::addIndex
+ */
+ public function addIndex( $type, $name, $property )
+ {
+ $table = $this->esc( $type );
+ $name = preg_replace( '/\W/', '', $name );
+ $column = $this->esc( $property );
+
+ try {
+ $this->adapter->exec( "CREATE INDEX {$name} ON $table ({$column}) " );
+ return TRUE;
+ } catch ( SQLException $e ) {
+ return FALSE;
+ }
+ }
+
+ /**
+ * @see QueryWriter::addFK
+ */
+ public function addFK( $type, $targetType, $property, $targetProperty, $isDep = FALSE )
+ {
+ $table = $this->esc( $type );
+ $targetTable = $this->esc( $targetType );
+ $field = $this->esc( $property );
+ $targetField = $this->esc( $targetProperty );
+ $tableNoQ = $this->esc( $type, TRUE );
+ $fieldNoQ = $this->esc( $property, TRUE );
+ if ( !is_null( $this->getForeignKeyForTypeProperty( $tableNoQ, $fieldNoQ ) ) ) return FALSE;
+ try{
+ $delRule = ( $isDep ? 'CASCADE' : 'SET NULL' );
+ $this->adapter->exec( "ALTER TABLE {$table}
+ ADD FOREIGN KEY ( {$field} ) REFERENCES {$targetTable}
+ ({$targetField}) ON DELETE {$delRule} ON UPDATE {$delRule} DEFERRABLE ;" );
+ return TRUE;
+ } catch ( SQLException $e ) {
+ return FALSE;
+ }
+ }
+
+ /**
+ * @see QueryWriter::wipeAll
+ */
+ public function wipeAll()
+ {
+ $this->adapter->exec( 'SET CONSTRAINTS ALL DEFERRED' );
+
+ foreach ( $this->getTables() as $t ) {
+ $t = $this->esc( $t );
+ //Some plugins (PostGIS have unremovable tables/views), avoid exceptions.
+ try { $this->adapter->exec( "DROP TABLE IF EXISTS $t CASCADE " ); }catch( \Exception $e ) {}
+ }
+
+ $this->adapter->exec( 'SET CONSTRAINTS ALL IMMEDIATE' );
+ }
+}
projects / yaffs-website / blobdiff