/** * Return information schema query * * @param string|null $table * @return string */ protected function getQuery($table = null) { $qSchema = $this->adapter->quoteValue($this->schema); if ($table !== null) { $qTable = $this->adapter->quoteValue($table); $table_clause = "and (t.TABLE_NAME = {$qTable} or (kcu.referenced_table_name = {$qTable} and kcu.constraint_name = 'FOREIGN KEY'))"; $table_join_condition = "(t.table_name = kcu.table_name or kcu.referenced_table_name = t.table_name)"; } else { $table_join_condition = "t.table_name = kcu.table_name"; $table_clause = ''; } $query = "\n\n SELECT\n t.table_name,\n c.column_name,\n c.data_type,\n c.column_type,\n\n c.extra,\n\n tc.constraint_type,\n kcu.constraint_name,\n kcu.referenced_table_name,\n kcu.referenced_column_name,\n\n c.column_default,\n c.is_nullable,\n c.numeric_precision,\n c.numeric_scale,\n c.character_octet_length,\n c.character_maximum_length,\n c.ordinal_position,\n\n c.column_key, -- UNI/MUL/PRI\n c.character_set_name,\n\n\n c.collation_name,\n\n c.column_comment,\n\n t.table_type,\n t.engine,\n t.table_comment,\n t.table_collation\n\n FROM `INFORMATION_SCHEMA`.`COLUMNS` c\n INNER JOIN `INFORMATION_SCHEMA`.`TABLES` t on c.TABLE_NAME = t.TABLE_NAME\n LEFT JOIN `INFORMATION_SCHEMA`.`KEY_COLUMN_USAGE` kcu\n on (\n {$table_join_condition}\n and kcu.table_schema = t.table_schema\n and kcu.column_name = c.column_name\n )\n LEFT JOIN\n `INFORMATION_SCHEMA`.`TABLE_CONSTRAINTS` tc\n on (\n t.table_name = tc.table_name\n and tc.table_schema = t.table_schema\n and tc.constraint_name = kcu.constraint_name\n )\n\n\n where c.TABLE_SCHEMA = {$qSchema}\n and t.TABLE_SCHEMA = {$qSchema}\n {$table_clause}\n and (kcu.table_schema = {$qSchema} or kcu.table_schema is null)\n\n and (kcu.column_name = c.column_name or kcu.column_name is null)\n order by t.table_name, c.ordinal_position\n "; return $query; }