se/SE/se-lib/Core/Storage/Pgsql.php

<?php

Lib::loadClass('Core_StorageBase');
Lib::loadClass('Core_StorageFactory');

class Core_Storage_Pgsql extends Core_StorageBase {

	protected $_databaseName = '';
	protected $_zasob_id = 0;
	protected $_pdo = null;

	public function __construct($pdo) {
		$this->_pdo = $pdo;
		$this->_databaseName = $pdo->getDatabaseName();
		$this->_zasob_id = $pdo->getZasobId();
	}

	public function getTableStruct($tblName) {
		throw new Exception("TODO: F." . __FUNCTION__);

		$sql = "
			-- show fields from {$tblName}
			select cols.COLUMN_NAME as name
				, cols.DATA_TYPE as type
				, if('YES' = cols.IS_NULLABLE, 1, 0) as is_nullable
				, cols.COLUMN_DEFAULT as default_value
				, if(cols.COLUMN_DEFAULT is null, 1, 0) as default_is_null
				, cols.CHARACTER_MAXIMUM_LENGTH as max_length
				, cols.NUMERIC_PRECISION as num_precision
				, cols.NUMERIC_SCALE as num_scale
				, cols.CHARACTER_SET_NAME as char_encoding -- latin2
				, cols.COLLATION_NAME as char_collation -- latin2_general_ci
				, cols.EXTRA as extra
				, cols.COLUMN_TYPE as raw_storage_type
		--		, cols.*
			from INFORMATION_SCHEMA.COLUMNS cols
			where cols.TABLE_SCHEMA like :db_name
				and cols.TABLE_NAME like :tbl_name
		";
		$sth = $this->_pdo->prepare($sql);
		$sth->bindValue(':db_name', $this->_pdo->getDatabaseName(), PDO::PARAM_STR);
		$sth->bindValue(':tbl_name', $tblName, PDO::PARAM_STR);
		$sth->execute();
		$structRaw = $sth->fetchAll();
		if (empty($structRaw)) throw new Exception("Empty struct for table '{$tblName}'");
		foreach ($structRaw as $field) {
			$struct[$field['name']] = $field;
		}
		return $struct;
	}

	protected function _getTableStruct($tblName) {// ?
		$tblStructRaw = $this->getTableStructRaw($tblName);
		$tblStruct = array();
		foreach ($tblStructRaw as $fieldStruct) {
			$fldName = $fieldStruct['column_name'];
			$storageType = '';
			if ('integer' == $fieldStruct['data_type']
					|| 'bigint' == $fieldStruct['data_type']
					|| 'smallint' == $fieldStruct['data_type']
				 ) {
				$storageType = Core_StorageFactory::getType('Integer');
			}
			else if ('character varying' == $fieldStruct['data_type']
								|| 'character' == $fieldStruct['data_type']
								) {
				$storageType = Core_StorageFactory::getType('String');
				$storageType->setMaxLength($fieldStruct['character_maximum_length']);
			}
			else if ('numeric' == $fieldStruct['data_type']
							 || 'decimal' == $fieldStruct['data_type']
							) {
				$storageType = Core_StorageFactory::getType('Decimal');
			}
			else if ('real' == $fieldStruct['data_type']
							 || 'double precision' == $fieldStruct['data_type']
							) {
				$storageType = Core_StorageFactory::getType('Double');
			}
			else if ('text' == $fieldStruct['data_type']) {
				$storageType = Core_StorageFactory::getType('Text');
			}
			else if ('"char"' == $fieldStruct['data_type']) {
				$storageType = Core_StorageFactory::getType('String');
				$storageType->setMaxLength(1);
			}
			else if ('date' == $fieldStruct['data_type']) {
				$storageType = Core_StorageFactory::getType('Date');
			}
			else if ('timestamp without time zone' == $fieldStruct['data_type']) {
				$storageType = Core_StorageFactory::getType('DateTime');
			}
			else if ('timestamp with time zone' == $fieldStruct['data_type']) {
				$storageType = Core_StorageFactory::getType('DateTime');
			}
			else if ('USER-DEFINED' == $fieldStruct['data_type']) {
				if ('geometry' == $fieldStruct['udt_name']) {
					$storageType = Core_StorageFactory::getType('Geometry');
					$rawStructure = $this->_getStructureRaw('public', $tblName, $fldName);
					if (!empty($rawStructure[0]['type'])) {
						$spatialType = $rawStructure[0]['type'];
						$storageType->setSpatialType($spatialType);
					}
				} else {
					$storageType = Core_StorageFactory::getType('Unknown');
					$storageType->setRawType(array('data_type'=>$fieldStruct['data_type'], 'udt_name'=>$fieldStruct['udt_name']));
				}
			}
			else {
				$storageType = Core_StorageFactory::getType('Unknown');
				$storageType->setRawType(array('data_type'=>$fieldStruct['data_type'], 'udt_name'=>$fieldStruct['udt_name']));
			}

			if (!$storageType) {
				echo "<div class=\"alert alert-warning\">Storage type '{$fieldStruct['Type']}' not recognized</div>";
				// TODO: throw new Exception("Storage type '{$fieldStruct['Type']}' not recognized");
			}

			if ($fieldStruct['is_primary_key'] > 0) {
				$storageType->setPrimaryKey(true);
			}

			$vFldNillable = false;
			if ('YES' == $fieldStruct['is_nullable']) {
				$vFldNillable = true;
			} else if ('NO' == $fieldStruct['is_nullable']
								 && $fieldStruct['is_primary_key'] > 0
								 && 'nextval(' == substr($fieldStruct['column_default'], 0, 8)
								) {
				$vFldNillable = true;
			}
			$storageType->setNillable($vFldNillable);

			$vFldDefault = $fieldStruct['column_default'];
			if (!empty($vFldDefault)) {
				if ('::"char"' == substr($vFldDefault, -8)) {
					$vFldDefault = substr($vFldDefault, 1, -9);
					$storageType->setDefault($vFldDefault);
				} else if ('::"text"' == substr($vFldDefault, -8)) {
					$vFldDefault = substr($vFldDefault, 1, -9);
					$storageType->setDefault($vFldDefault);
				} else if ('::bpchar' == substr($vFldDefault, -8)) {
					$vFldDefault = substr($vFldDefault, 1, -9);
					$storageType->setDefault($vFldDefault);
				} else if ('::character varying' == substr($vFldDefault, -19)) {
					$vFldDefault = substr($vFldDefault, 1, -20);
					$storageType->setDefault($vFldDefault);
				} else if (is_numeric($vFldDefault)) {
					$storageType->setDefault($vFldDefault);
				} else if ('now()' == $vFldDefault) {
					// TODO: default = now()
				}
			} else if ('0' === $vFldDefault) {
				$storageType->setDefault('0');
			}

			$tblStruct[$fldName] = $storageType;
		}
		return $tblStruct;
	}

	public function getTableStructRaw($tblName) {// ?
		$tblNamePattern = '/^[a-zA-Z0-9_]+$/';
		if (!preg_match($tblNamePattern, $tblName)) {
			throw new Exception("Table name not allowed");
		}
		$sqlTblName = $tblName;
		$sql = "select * from information_schema.columns where table_name='{$sqlTblName}'";
		$sqlAllPrimaryKeys = "
			select tc.table_schema, tc.table_name, kc.column_name
			from
					information_schema.table_constraints tc,
					information_schema.key_column_usage kc
			where
					tc.constraint_type = 'PRIMARY KEY'
					and kc.table_name = tc.table_name
					and kc.table_schema = tc.table_schema
					and kc.constraint_name = tc.constraint_name
			order by 1, 2;
		";
		$sql = "select cols.*
				, (select count(kc.column_name)
					from information_schema.table_constraints tc
						join information_schema.key_column_usage kc on(kc.table_name = tc.table_name
							and kc.table_schema = tc.table_schema
							and kc.constraint_name = tc.constraint_name)
					where tc.constraint_type = 'PRIMARY KEY'
						and tc.table_name = cols.table_name
						and kc.column_name = cols.column_name
				) as is_primary_key
			from information_schema.columns cols
			where cols.table_name='{$sqlTblName}'
		";
		return $this->fetchAllAssoc($sql);
	}

	public function getTableListWithInfo() {
		return $this->_getPgsqlStructureRaw();
	}

	public function _getPgsqlStructureRaw($schemaName = 'public', $tableName = '', $fieldName = '') {
		$rawStructureList = array();
		$sqlWhereAndSchemaName = ($schemaName) ? " and n.nspname='{$schemaName}' " : '';
		$sqlWhereAndTableName  = ($tableName) ?  " and c.relname='{$tableName}' " : '';
		$sqlWhereAndFieldName  = ($fieldName) ?  " and f.attname='{$fieldName}' " : '';
		$sql = "
			select
				c.relname as table_name,
				f.attnum as number,
				f.attname as name,
				f.attnum,
				f.attnotnull as notnull,
				f.atttypid,
				f.atttypmod,
				pg_catalog.format_type(f.atttypid,f.atttypmod) as type,
				case when p.contype = 'p' then 1 else 0 end as primarykey,
				case when p.contype = 'u' then 1 else 0 end as uniquekey,
				case when p.contype = 'f' then g.relname end as foreignkey,
				case when p.contype = 'f' then p.confkey end as foreignkey_fieldnum,
				case when p.contype = 'f' then g.relname end as foreignkey,
				case when p.contype = 'f' then p.conkey end as foreignkey_connnum,
				case when f.atthasdef = 't' then d.adsrc end as default
			from pg_attribute f
				join pg_class c on c.oid = f.attrelid
				join pg_type t on t.oid = f.atttypid
				left join pg_attrdef d on d.adrelid = c.oid and d.adnum = f.attnum
				left join pg_namespace n on n.oid = c.relnamespace
				left join pg_constraint p on p.conrelid = c.oid and f.attnum = any (p.conkey)
				left join pg_class as g on p.confrelid = g.oid
			where c.relkind = 'r'::char
				{$sqlWhereAndSchemaName}
				{$sqlWhereAndTableName}
				{$sqlWhereAndFieldName}
				and f.attnum > 0
			order by c.relname, number
		";
		return $this->_pdo->fetchAll($sql);
	}

	public function getTableList() {
		return array_filter($this->getTableFullList(), function($row) {
			return ('BASE TABLE' == $row['table_type']);
		});
	}

	public function getViewList() {
		return array_filter($this->getTableFullList(), function($row) {
			return ('VIEW' == $row['table_type']);
		});
	}

	public function getTableFullList() {
		return array_map(function($row) {
			$row['table_name'] = strtolower($row['table_name']);
			return $row;
		}, $this->_pdo->fetchAll("
			select table_name, table_type
			from information_schema.tables
			where table_schema not in ('pg_catalog', 'information_schema')
		"));
	}

	public function fetchAllAssoc($sql) {// ?
		if (empty($sql)) {
			throw new Exception("Empty query");
		}
		$stmt = $this->_pdo->query($sql);
		return $stmt->fetchAll(PDO::FETCH_ASSOC);
	}

}