Skip to content

HTTPS clone URL

Subversion checkout URL

You can clone with
or
.
Download ZIP
tree: 99e125ebc6
Fetching contributors…

Cannot retrieve contributors at this time

573 lines (507 sloc) 21.508 kb
<?php
/**
* This file is part of the Propel package.
* For the full copyright and license information, please view the LICENSE
* file that was distributed with this source code.
*
* @license MIT License
*/
namespace Propel\Generator\Reverse;
use Propel\Generator\Exception\EngineException;
use Propel\Generator\Model\Column;
use Propel\Generator\Model\ColumnDefaultValue;
use Propel\Generator\Model\Database;
use Propel\Generator\Model\ForeignKey;
use Propel\Generator\Model\Index;
use Propel\Generator\Model\PropelTypes;
use Propel\Generator\Model\Table;
use Propel\Generator\Model\Unique;
use Propel\Generator\Reverse\AbstractSchemaParser;
/**
* Postgresql database schema parser.
*
* @author Hans Lellelid <hans@xmpl.org>
*/
class PgsqlSchemaParser extends AbstractSchemaParser
{
/**
* Map PostgreSQL native types to Propel types.
* @var array
*/
/** Map MySQL native types to Propel (JDBC) types. */
private static $pgsqlTypeMap = array(
'bool' => PropelTypes::BOOLEAN,
'boolean' => PropelTypes::BOOLEAN,
'tinyint' => PropelTypes::TINYINT,
'smallint' => PropelTypes::SMALLINT,
'mediumint' => PropelTypes::SMALLINT,
'int2' => PropelTypes::SMALLINT,
'int' => PropelTypes::INTEGER,
'int4' => PropelTypes::INTEGER,
'serial4' => PropelTypes::INTEGER,
'integer' => PropelTypes::INTEGER,
'int8' => PropelTypes::BIGINT,
'bigint' => PropelTypes::BIGINT,
'bigserial' => PropelTypes::BIGINT,
'serial8' => PropelTypes::BIGINT,
'int24' => PropelTypes::BIGINT,
'real' => PropelTypes::REAL,
'float' => PropelTypes::FLOAT,
'float4' => PropelTypes::FLOAT,
'decimal' => PropelTypes::DECIMAL,
'numeric' => PropelTypes::DECIMAL,
'double' => PropelTypes::DOUBLE,
'float8' => PropelTypes::DOUBLE,
'char' => PropelTypes::CHAR,
'character' => PropelTypes::CHAR,
'varchar' => PropelTypes::VARCHAR,
'date' => PropelTypes::DATE,
'time' => PropelTypes::TIME,
'timetz' => PropelTypes::TIME,
//'year' => PropelTypes::YEAR, PropelTypes::YEAR does not exist... does this need to be mapped to a different propel type?
'datetime' => PropelTypes::TIMESTAMP,
'timestamp' => PropelTypes::TIMESTAMP,
'timestamptz' => PropelTypes::TIMESTAMP,
'bytea' => PropelTypes::BLOB,
'text' => PropelTypes::LONGVARCHAR,
);
/**
* Gets a type mapping from native types to Propel types
*
* @return array
*/
protected function getTypeMapping()
{
return self::$pgsqlTypeMap;
}
/**
*
*/
public function parse(Database $database)
{
$stmt = $this->dbh->query('SELECT version() as ver');
$nativeVersion = $stmt->fetchColumn();
if (!$nativeVersion) {
throw new EngineException('Failed to get database version');
}
$arrVersion = sscanf($nativeVersion, '%*s %d.%d');
$version = sprintf('%d.%d', $arrVersion[0], $arrVersion[1]);
// Clean up
$stmt = null;
$stmt = $this->dbh->query("SELECT c.oid,
c.relname, n.nspname
FROM pg_class c join pg_namespace n on (c.relnamespace=n.oid)
WHERE c.relkind = 'r'
AND n.nspname NOT IN ('information_schema','pg_catalog')
AND n.nspname NOT LIKE 'pg_temp%'
AND n.nspname NOT LIKE 'pg_toast%'
ORDER BY relname"
);
$tableWraps = array();
// First load the tables (important that this happen before filling out details of tables)
while ($row = $stmt->fetch(\PDO::FETCH_ASSOC)) {
$name = $row['relname'];
$namespacename = $row['nspname'];
if ($name == $this->getMigrationTable()) {
continue;
}
$oid = $row['oid'];
$table = new Table($name);
if ($namespacename != 'public') {
$table->setSchema($namespacename);
}
$table->setIdMethod($database->getDefaultIdMethod());
$database->addTable($table);
// Create a wrapper to hold these tables and their associated OID
$wrap = new \stdClass;
$wrap->table = $table;
$wrap->oid = $oid;
$tableWraps[] = $wrap;
}
// Now populate only columns.
foreach ($tableWraps as $wrap) {
$this->addColumns($wrap->table, $wrap->oid, $version);
}
// Now add indexes and constraints.
foreach ($tableWraps as $wrap) {
$this->addForeignKeys($wrap->table, $wrap->oid, $version);
$this->addIndexes($wrap->table, $wrap->oid, $version);
$this->addPrimaryKey($wrap->table, $wrap->oid, $version);
}
// @TODO - Handle Sequences ...
return count($tableWraps);
}
/**
* Adds Columns to the specified table.
*
* @param Table $table The Table model class to add columns to.
* @param int $oid The table OID
* @param string $version The database version.
*/
protected function addColumns(Table $table, $oid, $version)
{
// Get the columns, types, etc.
// Based on code from pgAdmin3 (http://www.pgadmin.org/)
$stmt = $this->dbh->prepare("SELECT
att.attname,
att.atttypmod,
att.atthasdef,
att.attnotnull,
def.adsrc,
CASE WHEN att.attndims > 0 THEN 1 ELSE 0 END AS isarray,
CASE
WHEN ty.typname = 'bpchar'
THEN 'char'
WHEN ty.typname = '_bpchar'
THEN '_char'
ELSE
ty.typname
END AS typname,
ty.typtype
FROM pg_attribute att
JOIN pg_type ty ON ty.oid=att.atttypid
LEFT OUTER JOIN pg_attrdef def ON adrelid=att.attrelid AND adnum=att.attnum
WHERE att.attrelid = ? AND att.attnum > 0
AND att.attisdropped IS FALSE
ORDER BY att.attnum");
$stmt->bindValue(1, $oid, \PDO::PARAM_INT);
$stmt->execute();
while ($row = $stmt->fetch(\PDO::FETCH_ASSOC)) {
$size = null;
$precision = null;
$scale = null;
// Check to ensure that this column isn't an array data type
if (1 === ((int) $row['isarray'])) {
throw new EngineException(sprintf('Array datatypes are not currently supported [%s.%s]', $this->name, $row['attname']));
}
$name = $row['attname'];
// If they type is a domain, Process it
if ('d' === strtolower($row['typtype'])) {
$arrDomain = $this->processDomain($row['typname']);
$type = $arrDomain['type'];
$size = $arrDomain['length'];
$precision = $size;
$scale = $arrDomain['scale'];
$boolHasDefault = (strlen(trim($row['atthasdef'])) > 0) ? $row['atthasdef'] : $arrDomain['hasdefault'];
$default = (strlen(trim($row['adsrc'])) > 0) ? $row['adsrc'] : $arrDomain['default'];
$isNullable = (strlen(trim($row['attnotnull'])) > 0) ? $row['attnotnull'] : $arrDomain['notnull'];
$isNullable = ('t' === $isNullable ? false : true);
} else {
$type = $row['typname'];
$arrLengthPrecision = $this->processLengthScale($row['atttypmod'], $type);
$size = $arrLengthPrecision['length'];
$precision = $size;
$scale = $arrLengthPrecision['scale'];
$boolHasDefault = $row['atthasdef'];
$default = $row['adsrc'];
$isNullable = ('t' === $row['attnotnull'] ? false : true);
}
$autoincrement = null;
// if column has a default
if ('t' === $boolHasDefault && (strlen(trim($default)) > 0)) {
if (!preg_match('/^nextval\(/', $default)) {
$strDefault= preg_replace('/::[\W\D]*/', '', $default);
$default = str_replace("'", '', $strDefault);
} else {
$autoincrement = true;
$default = null;
}
} else {
$default = null;
}
$propelType = $this->getMappedPropelType($type);
if (!$propelType) {
$propelType = Column::DEFAULT_TYPE;
$this->warn('Column [' . $table->getName() . '.' . $name. '] has a column type ('.$type.') that Propel does not support.');
}
$column = new Column($name);
$column->setTable($table);
$column->setDomainForType($propelType);
// We may want to provide an option to include this:
// $column->getDomain()->replaceSqlType($type);
$column->getDomain()->replaceSize($size);
$column->getDomain()->replaceScale($scale);
if (null !== $default) {
if (in_array($default, array('now()'))) {
$type = ColumnDefaultValue::TYPE_EXPR;
} else {
$type = ColumnDefaultValue::TYPE_VALUE;
}
$column->getDomain()->setDefaultValue(new ColumnDefaultValue($default, $type));
}
$column->setAutoIncrement($autoincrement);
$column->setNotNull(!$isNullable);
$table->addColumn($column);
}
} // addColumn()
private function processLengthScale($intTypmod, $strName)
{
// Define the return array
$arrRetVal = array('length' => null, 'scale' => null);
// Some datatypes don't have a Typmod
if (-1 === $intTypmod) {
return $arrRetVal;
}
// Numeric Datatype?
if ($strName == $this->getMappedNativeType(PropelTypes::NUMERIC)) {
$intLen = ($intTypmod - 4) >> 16;
$intPrec = ($intTypmod - 4) & 0xffff;
$intLen = sprintf('%ld', $intLen);
if ($intPrec) {
$intPrec = sprintf('%ld', $intPrec);
} // if ($intPrec)
$arrRetVal['length'] = $intLen;
$arrRetVal['scale'] = $intPrec;
} elseif (
$strName == $this->getMappedNativeType(PropelTypes::TIME)
|| 'timetz' === $strName
|| $strName == $this->getMappedNativeType(PropelTypes::TIMESTAMP)
|| 'timestamptz' === $strName
|| 'interval' === $strName
|| 'bit' === $strName
) {
$arrRetVal['length'] = sprintf('%ld', $intTypmod);
} else {
$arrRetVal['length'] = sprintf('%ld', ($intTypmod - 4));
}
return $arrRetVal;
}
private function processDomain($strDomain)
{
if (strlen(trim($strDomain)) < 1) {
throw new EngineException('Invalid domain name [' . $strDomain . ']');
}
$stmt = $this->dbh->prepare("SELECT
d.typname as domname,
b.typname as basetype,
d.typlen,
d.typtypmod,
d.typnotnull,
d.typdefault
FROM pg_type d
INNER JOIN pg_type b ON b.oid = CASE WHEN d.typndims > 0 then d.typelem ELSE d.typbasetype END
WHERE
d.typtype = 'd'
AND d.typname = ?
ORDER BY d.typname");
$stmt->bindValue(1, $strDomain);
$stmt->execute();
$row = $stmt->fetch(\PDO::FETCH_ASSOC);
if (!$row) {
throw new EngineException('Domain [' . $strDomain . '] not found.');
}
$arrDomain = array ();
$arrDomain['type'] = $row['basetype'];
$arrLengthPrecision = $this->processLengthScale($row['typtypmod'], $row['basetype']);
$arrDomain['length'] = $arrLengthPrecision['length'];
$arrDomain['scale'] = $arrLengthPrecision['scale'];
$arrDomain['notnull'] = $row['typnotnull'];
$arrDomain['default'] = $row['typdefault'];
$arrDomain['hasdefault'] = (strlen(trim($row['typdefault'])) > 0) ? 't' : 'f';
$stmt = null; // cleanup
return $arrDomain;
} // private function processDomain($strDomain)
/**
* Load foreign keys for this table.
*/
protected function addForeignKeys(Table $table, $oid, $version)
{
$database = $table->getDatabase();
$stmt = $this->dbh->prepare("SELECT
conname,
confupdtype,
confdeltype,
CASE nl.nspname WHEN 'public' THEN cl.relname ELSE nl.nspname||'.'||cl.relname END as fktab,
array_agg(DISTINCT a2.attname) AS fkcols,
CASE nr.nspname WHEN 'public' THEN cr.relname ELSE nr.nspname||'.'||cr.relname END as reftab,
array_agg(DISTINCT a1.attname) AS refcols
FROM pg_constraint ct
JOIN pg_class cl ON cl.oid=conrelid
JOIN pg_class cr ON cr.oid=confrelid
JOIN pg_namespace nl ON nl.oid = cl.relnamespace
JOIN pg_namespace nr ON nr.oid = cr.relnamespace
LEFT JOIN pg_catalog.pg_attribute a1 ON a1.attrelid = ct.confrelid
LEFT JOIN pg_catalog.pg_attribute a2 ON a2.attrelid = ct.conrelid
WHERE
contype='f'
AND conrelid = ?
AND a2.attnum = ANY (ct.conkey)
AND a1.attnum = ANY (ct.confkey)
GROUP BY conname, confupdtype, confdeltype, fktab, reftab
ORDER BY conname");
$stmt->bindValue(1, $oid);
$stmt->execute();
$foreignKeys = array(); // local store to avoid duplicates
while ($row = $stmt->fetch(\PDO::FETCH_ASSOC)) {
$name = $row['conname'];
$localTable = $row['fktab'];
$localColumns = explode(',', trim($row['fkcols'], '{}'));
$foreignTable = $row['reftab'];
$foreignColumns = explode(',', trim($row['refcols'], '{}'));
// On Update
switch ($row['confupdtype']) {
case 'c':
$onupdate = ForeignKey::CASCADE;
break;
case 'd':
$onupdate = ForeignKey::SETDEFAULT;
break;
case 'n':
$onupdate = ForeignKey::SETNULL;
break;
case 'r':
$onupdate = ForeignKey::RESTRICT;
break;
default:
case 'a':
// NOACTION is the postgresql default
$onupdate = ForeignKey::NONE;
break;
}
// On Delete
switch ($row['confdeltype']) {
case 'c':
$ondelete = ForeignKey::CASCADE;
break;
case 'd':
$ondelete = ForeignKey::SETDEFAULT;
break;
case 'n':
$ondelete = ForeignKey::SETNULL;
break;
case 'r':
$ondelete = ForeignKey::RESTRICT;
break;
default:
case 'a':
//NOACTION is the postgresql default
$ondelete = ForeignKey::NONE;
break;
}
$foreignTable = $database->getTable($foreignTable);
$localTable = $database->getTable($localTable);
if (!isset($foreignKeys[$name])) {
$fk = new ForeignKey($name);
$fk->setForeignTableCommonName($foreignTable->getCommonName());
$fk->setForeignSchemaName($foreignTable->getSchema());
$fk->setOnDelete($ondelete);
$fk->setOnUpdate($onupdate);
$table->addForeignKey($fk);
$foreignKeys[$name] = $fk;
}
$max = count($localColumns);
for ($i = 0; $i < $max; $i++) {
$foreignKeys[$name]->addReference(
$localTable->getColumn($localColumns[$i]),
$foreignTable->getColumn($foreignColumns[$i])
);
}
}
}
/**
* Load indexes for this table
*/
protected function addIndexes(Table $table, $oid, $version)
{
$stmt = $this->dbh->prepare("SELECT
DISTINCT ON(cls.relname)
cls.relname as idxname,
indkey,
indisunique
FROM pg_index idx
JOIN pg_class cls ON cls.oid=indexrelid
WHERE indrelid = ? AND NOT indisprimary
ORDER BY cls.relname");
$stmt->bindValue(1, $oid);
$stmt->execute();
$stmt2 = $this->dbh->prepare("SELECT a.attname
FROM pg_catalog.pg_class c JOIN pg_catalog.pg_attribute a ON a.attrelid = c.oid
WHERE c.oid = ? AND a.attnum = ? AND NOT a.attisdropped
ORDER BY a.attnum");
$indexes = array();
while ($row = $stmt->fetch(\PDO::FETCH_ASSOC)) {
$name = $row['idxname'];
$unique = ('t' === $row['indisunique'] ? true : false);
if (!isset($indexes[$name])) {
if ($unique) {
$indexes[$name] = new Unique($name);
} else {
$indexes[$name] = new Index($name);
}
$table->addIndex($indexes[$name]);
}
$arrColumns = explode(' ', $row['indkey']);
foreach ($arrColumns as $intColNum) {
$stmt2->bindValue(1, $oid);
$stmt2->bindValue(2, $intColNum);
$stmt2->execute();
$row2 = $stmt2->fetch(\PDO::FETCH_ASSOC);
$indexes[$name]->addColumn($table->getColumn($row2['attname']));
}
}
}
/**
* Loads the primary key for this table.
*/
protected function addPrimaryKey(Table $table, $oid, $version)
{
$stmt = $this->dbh->prepare("SELECT
DISTINCT ON(cls.relname)
cls.relname as idxname,
indkey,
indisunique
FROM pg_index idx
JOIN pg_class cls ON cls.oid=indexrelid
WHERE indrelid = ? AND indisprimary
ORDER BY cls.relname");
$stmt->bindValue(1, $oid);
$stmt->execute();
// Loop through the returned results, grouping the same key_name together
// adding each column for that key.
while ($row = $stmt->fetch(\PDO::FETCH_ASSOC)) {
$arrColumns = explode(' ', $row['indkey']);
foreach ($arrColumns as $intColNum) {
$stmt2 = $this->dbh->prepare("SELECT a.attname
FROM pg_catalog.pg_class c JOIN pg_catalog.pg_attribute a ON a.attrelid = c.oid
WHERE c.oid = ? AND a.attnum = ? AND NOT a.attisdropped
ORDER BY a.attnum");
$stmt2->bindValue(1, $oid);
$stmt2->bindValue(2, $intColNum);
$stmt2->execute();
$row2 = $stmt2->fetch(\PDO::FETCH_ASSOC);
$table->getColumn($row2['attname'])->setPrimaryKey(true);
}
}
}
/**
* Adds the sequences for this database.
*
* @return void
* @throws SQLException
*/
protected function addSequences(Database $database)
{
/*
-- WE DON'T HAVE ANY USE FOR THESE YET IN REVERSE ENGINEERING ...
$this->sequences = array();
$result = pg_query($this->conn->getResource(), "SELECT c.oid,
case when n.nspname='public' then c.relname else n.nspname||'.'||c.relname end as relname
FROM pg_class c join pg_namespace n on (c.relnamespace=n.oid)
WHERE c.relkind = 'S'
AND n.nspname NOT IN ('information_schema','pg_catalog')
AND n.nspname NOT LIKE 'pg_temp%'
AND n.nspname NOT LIKE 'pg_toast%'
ORDER BY relname");
if (!$result) {
throw new SQLException("Could not list sequences", pg_last_error($this->dblink));
}
while ($row = pg_fetch_assoc($result)) {
// FIXME -- decide what info we need for sequences & then create a SequenceInfo object (if needed)
$obj = new stdClass;
$obj->name = $row['relname'];
$obj->oid = $row['oid'];
$this->sequences[strtoupper($row['relname'])] = $obj;
}
*/
}
}
Jump to Line
Something went wrong with that request. Please try again.