/** * 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); }
/** * 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); }
/** * Returns metadata for all foreign keys in a table. * @param string * @return array */ public function getForeignKeys($table) { if (!$this->driver instanceof DibiSqlite3Driver) { // throw new DibiNotSupportedException; // @see http://www.sqlite.org/foreignkeys.html } $res = $this->driver->query("PRAGMA foreign_key_list({$this->driver->escape($table, dibi::IDENTIFIER)})"); $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; } } return array_values($keys); }
/** * 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); }