/**
* Returns metadata for all foreign keys in a table.
* @param string
* @return array
*/
public function getForeignKeys($table)
{
$res = $this->driver->query("\n\t\t\tSELECT f.name AS foreign_key,\n\t\t\tOBJECT_NAME(f.parent_object_id) AS table_name,\n\t\t\tCOL_NAME(fc.parent_object_id,\n\t\t\tfc.parent_column_id) AS column_name,\n\t\t\tOBJECT_NAME (f.referenced_object_id) AS reference_table_name,\n\t\t\tCOL_NAME(fc.referenced_object_id,\n\t\t\tfc.referenced_column_id) AS reference_column_name,\n\t\t\tfc.*\n\t\t\tFROM sys.foreign_keys AS f\n\t\t\tINNER JOIN sys.foreign_key_columns AS fc\n\t\t\tON f.OBJECT_ID = fc.constraint_object_id\n\t\t\tWHERE OBJECT_NAME(f.parent_object_id) = {$this->driver->escape($table, dibi::TEXT)}\n\t\t");
$keys = array();
while ($row = $res->fetch(TRUE)) {
$key_name = $row['foreign_key'];
if (!isset($keys[$key_name])) {
$keys[$key_name]['name'] = $row['foreign_key'];
// foreign key name
$keys[$key_name]['local'] = array($row['column_name']);
// local columns
$keys[$key_name]['table'] = $row['reference_table_name'];
// referenced table
$keys[$key_name]['foreign'] = array($row['reference_column_name']);
// referenced columns
$keys[$key_name]['onDelete'] = FALSE;
$keys[$key_name]['onUpdate'] = FALSE;
} else {
$keys[$key_name]['local'][] = $row['column_name'];
// local columns
$keys[$key_name]['foreign'][] = $row['reference_column_name'];
// referenced columns
}
}
return array_values($keys);
}
/**
* Returns metadata for all foreign keys in a table.
* @param string
* @return array
*/
public function getForeignKeys($table)
{
if (!$this->driver instanceof DibiSqlite3Driver) {
// throw new NotSupportedException; // @see http://www.sqlite.org/foreignkeys.html
}
$res = $this->driver->query("PRAGMA foreign_key_list([{$table}])");
$keys = array();
while ($row = $res->fetch(TRUE)) {
$keys[$row['id']]['name'] = $row['id'];
// foreign key name
$keys[$row['id']]['local'][$row['seq']] = $row['from'];
// local columns
$keys[$row['id']]['table'] = $row['table'];
// referenced table
$keys[$row['id']]['foreign'][$row['seq']] = $row['to'];
// referenced columns
$keys[$row['id']]['onDelete'] = $row['on_delete'];
$keys[$row['id']]['onUpdate'] = $row['on_update'];
if ($keys[$row['id']]['foreign'][0] == NULL) {
$keys[$row['id']]['foreign'] = NULL;
}
}
$res->free();
return array_values($keys);
}
/**
* Apply substitutions to indentifier and delimites it.
* @param string indentifier
* @return string
*/
private function delimite($value)
{
$parts = explode('.', self::substitute($value));
foreach ($parts as &$value) {
$value = $value === '*' ? '*' : $this->driver->escape($value, dibi::IDENTIFIER);
}
return implode('.', $parts);
}
/**
* Returns metadata for all indexes in a table.
* @param string
* @return array
*/
public function getIndexes($table)
{
$keyUsagesRes = $this->driver->query("SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME = {$this->driver->escape($table, dibi::TEXT)}");
$keyUsages = array();
while ($row = $keyUsagesRes->fetch(TRUE)) {
$keyUsages[$row['CONSTRAINT_NAME']][(int) $row['ORDINAL_POSITION'] - 1] = $row['COLUMN_NAME'];
}
$res = $this->driver->query("SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_NAME = {$this->driver->escape($table, dibi::TEXT)}");
$indexes = array();
while ($row = $res->fetch(TRUE)) {
$indexes[$row['CONSTRAINT_NAME']]['name'] = $row['CONSTRAINT_NAME'];
$indexes[$row['CONSTRAINT_NAME']]['unique'] = $row['CONSTRAINT_TYPE'] === 'UNIQUE';
$indexes[$row['CONSTRAINT_NAME']]['primary'] = $row['CONSTRAINT_TYPE'] === 'PRIMARY KEY';
$indexes[$row['CONSTRAINT_NAME']]['columns'] = isset($keyUsages[$row['CONSTRAINT_NAME']]) ? $keyUsages[$row['CONSTRAINT_NAME']] : array();
}
return array_values($indexes);
}
/**
* Returns metadata for all indexes in a table.
* @param string
* @return array
*/
public function getIndexes($table)
{
/*$table = $this->escape($table, dibi::TEXT);
$this->query("
SELECT *
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE TABLE_NAME = $table AND TABLE_SCHEMA = DATABASE()
AND REFERENCED_COLUMN_NAME IS NULL
");*/
$res = $this->driver->query("SHOW INDEX FROM {$this->driver->escape($table, dibi::IDENTIFIER)}");
$indexes = array();
while ($row = $res->fetch(TRUE)) {
$indexes[$row['Key_name']]['name'] = $row['Key_name'];
$indexes[$row['Key_name']]['unique'] = !$row['Non_unique'];
$indexes[$row['Key_name']]['primary'] = $row['Key_name'] === 'PRIMARY';
$indexes[$row['Key_name']]['columns'][$row['Seq_in_index'] - 1] = $row['Column_name'];
}
return array_values($indexes);
}
/**
* Apply substitutions to indentifier and delimites it.
* @param string indentifier
* @return string
*/
private function delimite($value)
{
if ($value === '*') {
return '*';
} elseif (strpos($value, ':') !== FALSE) {
// provide substitution
$value = preg_replace_callback('#:(.*):#U', array(__CLASS__, 'subCb'), $value);
}
return $this->driver->escape($value, dibi::IDENTIFIER);
}
/**
* Returns metadata for all foreign keys in a table.
* @param string
* @return array
* @throws DibiNotSupportedException
*/
public function getForeignKeys($table)
{
$data = $this->driver->query("SELECT `ENGINE` FROM information_schema.TABLES WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = {$this->driver->escape($table, dibi::TEXT)}")->fetch(TRUE);
if ($data['ENGINE'] !== 'InnoDB') {
throw new DibiNotSupportedException("Foreign keys are not supported in {$data['ENGINE']} tables.");
}
$res = $this->driver->query("\n\t\t\tSELECT rc.CONSTRAINT_NAME, rc.UPDATE_RULE, rc.DELETE_RULE, kcu.REFERENCED_TABLE_NAME,\n\t\t\t\tGROUP_CONCAT(kcu.REFERENCED_COLUMN_NAME ORDER BY kcu.ORDINAL_POSITION) AS REFERENCED_COLUMNS,\n\t\t\t\tGROUP_CONCAT(kcu.COLUMN_NAME ORDER BY kcu.ORDINAL_POSITION) AS COLUMNS\n\t\t\tFROM information_schema.REFERENTIAL_CONSTRAINTS rc\n\t\t\tINNER JOIN information_schema.KEY_COLUMN_USAGE kcu ON\n\t\t\t\tkcu.CONSTRAINT_NAME = rc.CONSTRAINT_NAME\n\t\t\t\tAND kcu.CONSTRAINT_SCHEMA = rc.CONSTRAINT_SCHEMA\n\t\t\tWHERE rc.CONSTRAINT_SCHEMA = DATABASE()\n\t\t\t\tAND rc.TABLE_NAME = {$this->driver->escape($table, dibi::TEXT)}\n\t\t\tGROUP BY rc.CONSTRAINT_NAME\n\t\t");
$foreignKeys = array();
while ($row = $res->fetch(TRUE)) {
$keyName = $row['CONSTRAINT_NAME'];
$foreignKeys[$keyName]['name'] = $keyName;
$foreignKeys[$keyName]['local'] = explode(',', $row['COLUMNS']);
$foreignKeys[$keyName]['table'] = $row['REFERENCED_TABLE_NAME'];
$foreignKeys[$keyName]['foreign'] = explode(',', $row['REFERENCED_COLUMNS']);
$foreignKeys[$keyName]['onDelete'] = $row['DELETE_RULE'];
$foreignKeys[$keyName]['onUpdate'] = $row['UPDATE_RULE'];
}
return array_values($foreignKeys);
}
/**
* Apply substitutions to indentifier and delimites it.
* @param string indentifier
* @return string
* @internal
*/
public function delimite($value)
{
$value = self::substitute($value);
$parts = explode('.', $value);
foreach ($parts as &$v) {
if ($v !== '*') {
$v = $this->driver->escape($v, dibi::IDENTIFIER);
}
}
return implode('.', $parts);
}
/**
* @return void
*/
protected function initColumns()
{
if ($this->columns === NULL) {
$this->columns = array();
foreach ($this->driver->getColumnsMeta() as $info) {
$this->columns[] = $this->names[$info['name']] = new DibiColumnInfo($this->driver, $info);
}
}
}
/**
* Apply substitutions to indentifier and delimites it.
*
* @param string indentifier
* @return string
*/
private function delimite($value)
{
return $this->driver->escape(dibi::substitute($value), dibi::IDENTIFIER);
}
/**
* @return void
*/
protected function initIndexes()
{
if ($this->indexes === NULL) {
$this->initColumns();
$this->indexes = array();
foreach ($this->driver->getIndexes($this->name) as $info) {
foreach ($info['columns'] as $key => $name) {
$info['columns'][$key] = $this->columns[strtolower($name)];
}
$this->indexes[strtolower($info['name'])] = new DibiIndexInfo($info);
if (!empty($info['primary'])) {
$this->primaryKey = $this->indexes[strtolower($info['name'])];
}
}
}
}
/**
* Gets a information about the current database.
* @return DibiDatabaseInfo
*/
public function getDatabaseInfo()
{
$this->connected || $this->connect();
return new DibiDatabaseInfo($this->driver->getReflector(), isset($this->config['database']) ? $this->config['database'] : NULL);
}
