/**
* createFromParams()
* Constructor for the THINKER_Object_Relationship Class that uses parameters for creation
*
* @author Cory Gehr
* @access public
* @static
* @param $relationName: Name of the Relationship
* @param $sourceSchema: Source Schema
* @param $sourceTable: Source Table
* @param $sourceTableName: Source Table Friendly Name
* @param $sourceColumn: Source Column
* @param $sourceColumnName: Source Column Friendly Name
* @param $refSchema: Referenced Schema
* @param $refTable: Referenced Table
* @param $refTableName: Referenced Table Friendly Name
* @param $refColumn: Referenced Column
* @param $refColumnName: Referenced Column Friendly Name
* @return THINKER_Object_Relationship Object
*/
public static function createFromParams($relationName, $sourceSchema, $sourceTable, $sourceTableName, $sourceColumn, $sourceColumnName, $refSchema, $refTable, $refTableName, $refColumn, $refColumnName)
{
$Object = new THINKER_Object_Relationship();
// Set values
$Object->setRelationshipName($relationName);
$Object->setSourceSchema($sourceSchema);
$Object->setSourceTable($sourceTable);
$Object->setSourceTableName($sourceTableName);
$Object->setSourceColumn($sourceColumn);
$Object->setSourceColumnName($sourceColumnName);
$Object->setReferencedSchema($refSchema);
$Object->setReferencedTable($refTable);
$Object->setReferencedTableName($refTableName);
$Object->setReferencedColumn($refColumn);
$Object->setReferencedColumnName($refColumnName);
return $Object;
}
/**
* discoverRelationships()
* Returns the information about each Foreign Key relationship with other tables
*
* @access public
* @return Array of THINKER_Object_Relationship Objects
*/
public function discoverRelationships()
{
global $_DB;
$query = "SELECT KCU.CONSTRAINT_NAME, KCU.COLUMN_NAME, C.COLUMN_COMMENT, KCU.REFERENCED_TABLE_SCHEMA, KCU.REFERENCED_TABLE_NAME, \n\t\t\t\t T.TABLE_COMMENT, KCU.REFERENCED_COLUMN_NAME, C1.COLUMN_COMMENT\n\t\t\t\t FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU \n\t\t\t\t JOIN INFORMATION_SCHEMA.COLUMNS C \n\t\t\t\t \tON C.TABLE_SCHEMA = KCU.TABLE_SCHEMA AND C.TABLE_NAME = KCU.TABLE_NAME AND C.COLUMN_NAME = KCU.COLUMN_NAME \n\t\t\t\t JOIN INFORMATION_SCHEMA.COLUMNS C1\n\t\t\t\t ON C1.TABLE_SCHEMA = KCU.REFERENCED_TABLE_SCHEMA AND C1.TABLE_NAME = KCU.REFERENCED_TABLE_NAME AND C1.COLUMN_NAME = KCU.REFERENCED_COLUMN_NAME \n\t\t\t\t JOIN INFORMATION_SCHEMA.TABLES T\n\t\t\t\t \tON T.TABLE_SCHEMA = KCU.REFERENCED_TABLE_SCHEMA AND T.TABLE_NAME = KCU.REFERENCED_TABLE_NAME \n\t\t\t\t WHERE KCU.TABLE_SCHEMA = :schemaName \n\t\t\t\t AND KCU.TABLE_NAME = :tableName \n\t\t\t\t AND KCU.REFERENCED_TABLE_NAME IS NOT NULL \n\t\t\t\t ORDER BY C.ORDINAL_POSITION, KCU.COLUMN_NAME";
$params = array(':schemaName' => $this->getTableSchema(), ':tableName' => $this->getTableName());
$statement = $_DB['thinker']->prepare($query);
$statement->execute($params);
$results = $statement->fetchAll(PDO::FETCH_NUM);
$output = array();
if ($results) {
foreach ($results as $t) {
list($relationName, $columnName, $columnComment, $refSchema, $refTable, $refTableComment, $refColumnName, $refColumnComment) = $t;
$output[] = THINKER_Object_Relationship::createFromParams($relationName, $this->getTableSchema(), $this->getTableName(), $this->getTableFriendlyName(), $columnName, $columnComment, $refSchema, $refTable, $refTableComment, $refColumnName, $refColumnComment);
}
}
return $output;
}
