column-meta.sql

use `bitch`;

drop view if exists `meta_table`;
create view `meta_table` as
  select
    `TABLES`.`TABLE_NAME` as `table`,
    `COLUMN_NAME` as `column`,
    `COLUMN_DEFAULT` as `default`,
    `IS_NULLABLE` = 'NO' as `require`,
    `DATA_TYPE` as `type`,
    `CHARACTER_MAXIMUM_LENGTH` as `length`,
    `COLUMN_KEY` = 'PRI' as `primary`
  from `INFORMATION_SCHEMA`.`COLUMNS`
    inner join `INFORMATION_SCHEMA`.`TABLES`
      on `COLUMNS`.`TABLE_NAME` = `TABLES`.`TABLE_NAME`
  where
    `COLUMNS`.`TABLE_SCHEMA` = 'bitch' and
    `TABLES`.`TABLE_TYPE` = 'BASE TABLE'
;

drop view if exists `meta_column`;
create view `meta_column` as
  select
    `KEY_COLUMN_USAGE`.`TABLE_NAME` as `table`,
    `KEY_COLUMN_USAGE`.`CONSTRAINT_NAME` as `constraint`,
    `CONSTRAINT_TYPE` as `type`,
    `COLUMN_NAME` as `column`,
    `KEY_COLUMN_USAGE`.`REFERENCED_TABLE_NAME` as `ref_table`,
    `KEY_COLUMN_USAGE`.`REFERENCED_COLUMN_NAME` as `ref_column`
  from `INFORMATION_SCHEMA`.`KEY_COLUMN_USAGE`
    inner join `INFORMATION_SCHEMA`.`TABLES`
      on `KEY_COLUMN_USAGE`.`TABLE_NAME` = `TABLES`.`TABLE_NAME`
    inner join `INFORMATION_SCHEMA`.`TABLE_CONSTRAINTS`
      on `KEY_COLUMN_USAGE`.`CONSTRAINT_NAME` = `TABLE_CONSTRAINTS`.`CONSTRAINT_NAME`
  where
    `KEY_COLUMN_USAGE`.`CONSTRAINT_SCHEMA` = 'bitch' and
    `TABLES`.`TABLE_TYPE` = 'BASE TABLE'
  order by `REFERENCED_TABLE_NAME`, `table`
;

drop view if exists `meta_foreign`;
create view `meta_foreign` as
  select `table`, `column`, `ref_table`, `ref_column`
  from `meta_column`
  where `type` = 'FOREIGN KEY'
;

drop view if exists `meta_unique`;
create view `meta_unique` as
  select `table`, `column`
  from `meta_column`
  where `type` = 'UNIQUE'
  group by `table`, `column`
;

drop view if exists `meta_table_column`;
create view `meta_table_column` as
  select meta_table.`table`, meta_table.`column`, `ref_table`, `ref_column`
  from `meta_table`
    left join `meta_foreign` on
      meta_table.`table` = meta_foreign.`table` and
      meta_table.column = meta_foreign.column
  order by `table`, `column`
;