/**
* Create a new database table object.
*
* @param \Tabulate\DB\Database $database The database to which this table belongs.
* @param string $name The name of the table.
*/
public function __construct($database, $name)
{
$this->database = $database;
$this->name = $name;
$this->columns = array();
$columns = $this->database->query("SHOW FULL COLUMNS FROM `{$name}`");
foreach ($columns as $column_info) {
$column = new Column($this->database, $this, $column_info);
$this->columns[$column->getName()] = $column;
}
$this->recordCounter = new RecordCounter($this);
}
/**
* Determine whether a given value is valid for a foreign key (i.e. is the title of a foreign row).
*
* @param \Tabulate\DB\Column $column The foreign key column
* @param string $value The value to check
* @return true If the value is valid
* @throws Exception If the value is not valid
*/
protected function validateForeignKey($column, $value)
{
$foreignTable = $column->getReferencedTable();
if (!$this->getRecordsByTitle($foreignTable, $value)) {
$link = '<a href="' . $foreignTable->getUrl() . '" title="Opens in a new tab or window" target="_blank" >' . $foreignTable->getTitle() . '</a>';
throw new \Exception("Value <code>{$value}</code> not found in {$link}");
}
return true;
}
/**
* For a given foreign key column, get an alias and join clause for selecting
* against that column's foreign values. If the column is not a foreign key,
* the alias will just be the qualified column name, and the join clause will
* be the empty string.
*
* @param \Tabulate\DB\Column $column The FK column
* @return array Array with 'join_clause' and 'column_alias' keys
*/
public function joinOn($column)
{
$joinClause = '';
$columnAlias = '`' . $this->getName() . '`.`' . $column->getName() . '`';
if ($column->isForeignKey()) {
$fk1Table = $column->getReferencedTable();
if (!$fk1Table) {
throw new \Exception("Unable to get referenced table of " . $column->getTable()->getName() . '.' . $column->getName());
}
$fk1TitleColumn = $fk1Table->getTitleColumn();
$joinClause .= ' LEFT OUTER JOIN `' . $fk1Table->getName() . '` AS f' . $this->aliasCount . ' ON (`' . $this->getName() . '`.`' . $column->getName() . '` ' . ' = `f' . $this->aliasCount . '`.`' . $fk1Table->getPkColumn()->getName() . '`)';
$columnAlias = "`f{$this->aliasCount}`.`" . $fk1TitleColumn->getName() . "`";
// FK is also an FK?
if ($fk1TitleColumn->isForeignKey()) {
$fk2_table = $fk1TitleColumn->getReferencedTable();
$fk2_title_column = $fk2_table->getTitleColumn();
$joinClause .= ' LEFT OUTER JOIN `' . $fk2_table->getName() . '` AS ff' . $this->aliasCount . ' ON (f' . $this->aliasCount . '.`' . $fk1TitleColumn->getName() . '` ' . ' = ff' . $this->aliasCount . '.`' . $fk1Table->getPkColumn()->getName() . '`)';
$columnAlias = "`ff{$this->aliasCount}`.`" . $fk2_title_column->getName() . "`";
}
$this->aliasCount++;
}
return array('join_clause' => $joinClause, 'column_alias' => $columnAlias);
}
