/generator/lib/reverse/pgsql/PgsqlSchemaParser.php

<?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
 */

require_once dirname(__FILE__) . '/../BaseSchemaParser.php';

/**
 * Postgresql database schema parser.
 *
 * @author     Hans Lellelid <hans@xmpl.org>
 * @version    $Revision$
 * @package    propel.generator.reverse.pgsql
 */
class PgsqlSchemaParser extends BaseSchemaParser
{

	/**
	 * 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, Task $task = null)
	{
		$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)
		if ($task) $task->log("Reverse Engineering Tables", Project::MSG_VERBOSE);
		while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
			$name = $row['relname'];
			$namespacename = $row['nspname'];
			if ($name == $this->getMigrationTable()) {
				continue;
			}
			if ($task) $task->log("  Adding table '" . $name . "' in schema '" . $namespacename . "'", Project::MSG_VERBOSE);
			$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.
		if ($task) $task->log("Reverse Engineering Columns", Project::MSG_VERBOSE);
		foreach ($tableWraps as $wrap) {
			if ($task) $task->log("  Adding columns for table '" . $wrap->table->getName() . "'", Project::MSG_VERBOSE);
			$this->addColumns($wrap->table, $wrap->oid, $version);
		}

		// Now add indexes and constraints.
		if ($task) $task->log("Reverse Engineering Indices And Constraints", Project::MSG_VERBOSE);
		foreach ($tableWraps as $wrap) {
			if ($task) $task->log("  Adding indices and constraints for table '" . $wrap->table->getName() . "'", Project::MSG_VERBOSE);
			$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 (((int) $row['isarray']) === 1) {
				throw new EngineException (sprintf ("Array datatypes are not currently supported [%s.%s]", $this->name, $row['attname']));
			} // if (((int) $row['isarray']) === 1)

			$name = $row['attname'];

			// If they type is a domain, Process it
			if (strtolower ($row['typtype']) == 'd') {
				$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'];
				$is_nullable = (strlen (trim ($row['attnotnull'])) > 0) ? $row['attnotnull'] : $arrDomain['notnull'];
				$is_nullable = (($is_nullable == 't') ? 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'];
				$is_nullable = (($row['attnotnull'] == 't') ? false : true);
			} // else (strtolower ($row['typtype']) == 'd')

			$autoincrement = null;

			// if column has a default
			if (($boolHasDefault == 't') && (strlen (trim ($default)) > 0)) {
				if (!preg_match('/^nextval\(/', $default)) {
					$strDefault= preg_replace('/::[\W\D]*/', '', $default);
					$default = preg_replace('/(\'?)\'/', '${1}', $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 ($default !== null) {
				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(!$is_nullable);

			$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 ($intTypmod == -1)
		{
			return $arrRetVal;
		} // if ($intTypmod == -1)

		// 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;
		} // if ($strName == $this->getMappedNativeType(PropelTypes::NUMERIC))
		elseif ($strName == $this->getMappedNativeType(PropelTypes::TIME) || $strName == 'timetz'
			|| $strName == $this->getMappedNativeType(PropelTypes::TIMESTAMP) || $strName == 'timestamptz'
			|| $strName == 'interval' || $strName == 'bit')
		{
			$arrRetVal['length'] = sprintf ("%ld", $intTypmod);
		} // elseif (TIME, TIMESTAMP, INTERVAL, BIT)
		else
		{
			$arrRetVal['length'] = sprintf ("%ld", ($intTypmod - 4));
		} // else
		return $arrRetVal;
	} // private function processLengthScale ($intTypmod, $strName)

	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'];
			$local_table = $row['fktab'];
			$local_columns = explode(',', trim($row['fkcols'], '{}'));
			$foreign_table = $row['reftab'];
			$foreign_columns = 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($foreign_table);
			$localTable   = $database->getTable($local_table);

			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;
			}

			for ($i = 0; $i < count($local_columns); $i++) {
				$foreignKeys[$name]->addReference(
					$localTable->getColumn($local_columns[$i]),
					$foreignTable->getColumn($foreign_columns[$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 = ($row["indisunique"] == 't') ? 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']));

			} // foreach ($arrColumns as $intColNum)

		}

	}

	/**
	 * 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);

			} // foreach ($arrColumns as $intColNum)
		}

	}

	/**
	 * 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;
		}
		*/
	}

}