1: 2: 3: 4: 5: 6: 7: 8: 9: 10: 11: 12: 13: 14: 15: 16: 17: 18: 19: 20: 21: 22: 23: 24: 25: 26: 27: 28: 29: 30: 31: 32: 33: 34: 35: 36: 37: 38: 39: 40: 41: 42: 43: 44: 45: 46: 47: 48: 49: 50: 51: 52: 53: 54: 55: 56: 57: 58: 59: 60: 61: 62: 63: 64: 65: 66: 67: 68: 69: 70: 71: 72: 73: 74: 75: 76: 77: 78: 79: 80: 81: 82: 83: 84: 85: 86: 87: 88: 89: 90: 91: 92: 93: 94: 95: 96: 97: 98: 99: 100: 101: 102: 103: 104: 105: 106: 107: 108: 109: 110: 111: 112: 113: 114: 115: 116: 117: 118: 119: 120: 121: 122: 123: 124: 125: 126: 127: 128: 129: 130: 131: 132: 133: 134: 135: 136: 137: 138: 139: 140: 141: 142: 143: 144: 145: 146: 147: 148: 149: 150: 151: 152: 153: 154: 155: 156: 157: 158: 159: 160: 161: 162: 163: 164: 165: 166: 167: 168: 169: 170: 171: 172: 173: 174: 175: 176: 177: 178: 179: 180: 181: 182: 183: 184: 185: 186: 187: 188: 189: 190: 191: 192: 193: 194: 195: 196: 197: 198: 199: 200: 201: 202: 203: 204: 205: 206: 207: 208: 209: 210: 211: 212: 213: 214: 215: 216: 217: 218: 219: 220: 221: 222: 223: 224: 225: 226: 227: 228: 229: 230: 231: 232: 233: 234: 235: 236: 237: 238: 239: 240: 241: 242: 243: 244: 245: 246: 247: 248: 249: 250: 251: 252: 253: 254: 255: 256: 257: 258: 259: 260: 261: 262: 263: 264: 265:
<?php
namespace Inlm\SchemaGenerator\Extractors;
use CzProject\SqlSchema;
use Inlm\SchemaGenerator\IExtractor;
use Inlm\SchemaGenerator\Utils\Generator;
use Inlm\SchemaGenerator\Utils\DataTypeParser;
use Nette;
class DibiMysqlExtractor implements IExtractor
{
private $connection;
private $ignoredTables;
private $tableMetas;
public function __construct($connection, array $ignoredTables = array())
{
if (!($connection instanceof \Dibi\Connection || $connection instanceof \DibiConnection)) {
throw new \Inlm\SchemaGenerator\InvalidArgumentException('Connection must be instance of Dibi\Connection or DibiConnection.');
}
$this->connection = $connection;
$this->ignoredTables = $ignoredTables;
}
public function generateSchema(array $options = array(), array $customTypes = array())
{
$schema = new SqlSchema\Schema;
foreach ($this->getTables() as $tableName) {
if (in_array($tableName, $this->ignoredTables, TRUE)) {
continue;
}
$schema->addTable($this->createTable($tableName));
}
return $schema;
}
private function getTables()
{
$rows = $this->connection->fetchAll('SHOW FULL TABLES');
$tables = array();
foreach ($rows as $row) {
$data = $row->toArray();
$tables[] = array_shift($data);
}
$this->tableMetas = $this->connection->query('SHOW TABLE STATUS')
->fetchAssoc('Name,=');
$rows = $this->connection->fetchAll('SELECT
T.table_name,
CCSA.character_set_name
FROM
INFORMATION_SCHEMA.`TABLES` AS T,
INFORMATION_SCHEMA.`COLLATION_CHARACTER_SET_APPLICABILITY` AS CCSA
WHERE
CCSA.collation_name = T.table_collation
AND T.table_schema = DATABASE();
');
foreach ($rows as $row) {
$this->tableMetas[$row['table_name']]['CHARACTER SET'] = $row['character_set_name'];
}
return $tables;
}
private function createTable($name)
{
$table = new SqlSchema\Table($name);
$this->assignTableMetaData($table);
$rows = $this->connection->fetchAll('SHOW FULL COLUMNS FROM %n', $name);
$meta = isset($this->tableMetas[$name]) ? $this->tableMetas[$name] : array();
$columnsMeta = $this->getColumnsMeta($name);
foreach ($rows as $row) {
$rowMeta = isset($columnsMeta[$row['Field']]) ? $columnsMeta[$row['Field']] : array();
$datatype = DataTypeParser::parse($row['Type']);
$options = $datatype->getOptions();
if ($row['Collation'] !== NULL) {
if (!isset($meta['Collation']) || $meta['Collation'] !== $row['Collation']) {
$options['COLLATE'] = $row['Collation'];
}
}
if ($rowMeta['CHARACTER SET'] !== NULL) {
if (!isset($meta['CHARACTER SET']) || $meta['CHARACTER SET'] !== $rowMeta['CHARACTER SET']) {
$options['CHARACTER SET'] = $rowMeta['CHARACTER SET'];
}
}
$column = $table->addColumn(
$row['Field'],
$datatype->getType(),
$datatype->getParameters(),
$options
);
$column->setNullable($row['Null'] === 'YES');
$column->setDefaultValue($row['Default']);
$column->setAutoIncrement($row['Extra'] === 'auto_increment');
if ($row['Comment'] !== '') {
$column->setComment($row['Comment']);
}
}
$this->createTableIndexes($table);
$this->createTableForeignKeys($table);
return $table;
}
private function assignTableMetaData(SqlSchema\Table $table)
{
$name = $table->getName();
if (!isset($this->tableMetas[$name])) {
return;
}
$meta = $this->tableMetas[$name];
$options = array(
'Engine' => 'ENGINE',
'CHARACTER SET' => 'CHARACTER SET',
'Collation' => 'COLLATE',
);
foreach ($options as $key => $option) {
if (isset($meta[$key])) {
$table->setOption($option, $meta[$key]);
}
}
if (isset($meta['Comment']) && $meta['Comment'] !== '') {
$table->setComment($meta['Comment']);
}
}
private function getColumnsMeta($tableName)
{
$rows = $this->connection->fetchAll('SELECT
column_name,
character_set_name
FROM INFORMATION_SCHEMA.`COLUMNS`
WHERE table_schema = DATABASE()
AND table_name = %s', $tableName
);
$meta = array();
foreach ($rows as $row) {
$meta[$row['column_name']]['CHARACTER SET'] = $row['character_set_name'];
}
return $meta;
}
private function createTableIndexes(SqlSchema\Table $table)
{
$rows = $this->connection->fetchAll('SHOW INDEXES FROM %n', $table->getName());
$indexes = array();
foreach ($rows as $row) {
$name = $row['Key_name'];
if ($name === 'PRIMARY') {
$name = NULL;
}
if (!isset($indexes[$name])) {
$type = SqlSchema\Index::TYPE_INDEX;
if ($name === NULL) {
$type = SqlSchema\Index::TYPE_PRIMARY;
} elseif (!$row['Non_unique']) {
$type = SqlSchema\Index::TYPE_UNIQUE;
} elseif ($row['Index_type'] === 'FULLTEXT') {
$type = SqlSchema\Index::TYPE_FULLTEXT;
}
$indexes[$name] = $table->addIndex($name, $type);
}
$index = $indexes[$name];
$index->addColumn($row['Column_name'])
->setOrder($row['Collation'] === 'A' ? SqlSchema\IndexColumn::ASC : SqlSchema\IndexColumn::DESC)
->setLength($row['Sub_part']);
}
}
private function createTableForeignKeys(SqlSchema\Table $table)
{
$rows = $this->connection->fetchAll('SELECT
KEY_COLUMN_USAGE.TABLE_NAME,
KEY_COLUMN_USAGE.COLUMN_NAME,
KEY_COLUMN_USAGE.CONSTRAINT_NAME,
KEY_COLUMN_USAGE.REFERENCED_TABLE_NAME,
KEY_COLUMN_USAGE.REFERENCED_COLUMN_NAME,
REFERENTIAL_CONSTRAINTS.UPDATE_RULE,
REFERENTIAL_CONSTRAINTS.DELETE_RULE
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS REFERENTIAL_CONSTRAINTS
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KEY_COLUMN_USAGE
ON KEY_COLUMN_USAGE.CONSTRAINT_NAME = REFERENTIAL_CONSTRAINTS.CONSTRAINT_NAME
AND KEY_COLUMN_USAGE.TABLE_SCHEMA = REFERENTIAL_CONSTRAINTS.CONSTRAINT_SCHEMA
AND KEY_COLUMN_USAGE.TABLE_NAME = REFERENTIAL_CONSTRAINTS.TABLE_NAME
WHERE
KEY_COLUMN_USAGE.TABLE_SCHEMA = DATABASE()
AND
KEY_COLUMN_USAGE.TABLE_NAME = %s', $table->getName()
);
$fks = array();
foreach ($rows as $row) {
$name = $row['CONSTRAINT_NAME'];
if (!isset($fks[$name])) {
$fks[$name] = $table->addForeignKey($name, array(), $row['REFERENCED_TABLE_NAME'], array())
->setOnUpdateAction($row['UPDATE_RULE'])
->setOnDeleteAction($row['DELETE_RULE']);
}
$fk = $fks[$name];
$fk->addColumn($row['COLUMN_NAME']);
$fk->addTargetColumn($row['REFERENCED_COLUMN_NAME']);
}
}
}