INFORMATION_SCHEMA Sistem Tablosu

Giriş

Açıklaması şöyle

It's a part of the SQL-92 standard, and it's implemented by most major database engines (with the notable exception of Oracle).

Database Discovery

Bir keresinde veri tabanını keşfedip Java nesneleri haline getiren bir kod ile çalışmıştım. Adımlar şöyleydi. Burada amaç primary key ve foreign key sütunları arasında çift yönlü ilişki kurmak

1. Select foreign keys

Foreign key kullanan tabloları buluyordu.

//Foreign key of all tables.
//Key : Table name
//Value : List of column names in other tables
Map<String, List<String>> fkMap = new HashMap<>();  

2. Select default values

Her sütun için varsa default değerleri buluyordu. 

//Default column value of all tables
//Key : Table name
//Value : Column name + Default value
Map<String, Map<String, String>> defaultsMap = new HashMap<>();

3. Select all views

Tüm view'lar dolaşılıyordu. Doldurulan veri şöyleydi.

Map<String, DbTable> viewsMap

4. Select all tables

Tüm tablolar dolaşılıyordu. Doldurulan veri şöyleydi

Map<String, DbTable> tableMap

DbTable şöyle

public class DbTable {

  //database name of the table
  private String name;

  //Map of tables that contains foreign key set on this table's Public Key
  private Map<String, IDbTable> subTables;

  // Map of fields this table contains
  private Map<String, IDbField> fields = new LinkedHashMap<String, IDbField>();

  ...
}

5. Select all columns + select all foreign keys

Tüm tablo ve view'ların sütunları için nesneler yaratılıyordu. Buradaki en önemli adım her sütun nesnesi için aynı zamanda foreign key ise atıfta bulunduğu tablo ve sütunlar da atanıyordu

DbField şöyle. Yani refTableName ve refColumnsNames dolduruluyor. 

Böylece foreign key <-> primary key çift yönlü ilişkisinin bir tarafı dolduruluyor

public class DbField {

  //database name of the field
  private String name;

  //Data type mapping info for this field.
  private DATATYPE dataType;

  private boolean isNullable;

  //Default value for the field
  private String defaultValue;

  //Flag indicating if this field is part of public key
  private boolean isPrimaryKey;

  //Flag indicating if this field is foreign key
  private boolean isForeignKey;

  //Name of the table which is referenced by the field (used for FK fields)
  private String refTableName;

  //Names of columns which are referenced by the field (used for FK fields)
  private List<String> refColumnsNames;

  //Name of the PK column in the referenced table (used for FK fields)
  private String refPkColumnName;

  //Indicates position of the column in table
  private int ordinal;

  //Map of tables that contains foreign key set on this table's Public Key
  private Map<String, IDbTable> subTables;
  ...
}

6. Select all columns + select all primary keys

Tüm constraint'ler üzerinde dolaşıp primary key olanlar primary key = true olarak işaretlenir. Ayrıca foreign key ise atıfta bulunulan sütunun tablolar listesine DBTable nesnesi eklenir. Yani subTables listesine ekleniyor. 

Böylece primary key <-> foreign key çift yönlü ilişkisinin bir tarafı dolduruluyor

COLUMNS

Tablolara ait sütunları verir

Örnek - Bir kullanıcıya ait tabloların tüm sütunları

Şöyle yaparız

MySQL
SELECT table_name, column_name FROM information_schema.columns WHERE table_schema = ?

Oracle
SELECT a.table_name, a.column_name, a.data_default FROM all_tab_cols a WHERE a.owner = ?   

KEY_COLUMN_USAGE

Tablolara ait Primary Key, Foreign Key gibi sütunları verir

Şöyle yaparız

MYSQL
SELECT column_name, referenced_table_name FROM information_schema.key_column_usage
  WHERE referenced_column_name IS NOT NULL AND table_name LIKE 'GSR%';


Oracle
SELECT a.column_name, a.table_name 
  FROM user_cons_columns a,user_constraints b
  WHERE a.constraint_name = b.constraint_name
  AND a.table_name = b.table_name
  AND a.table_name LIKE 'GSR_%'
  AND b.constraint_type = 'R'

TABLES

Tablo ve view isimlerini verir

Şöyle yaparız

MYSQL
SELECT table_name, FROM information_schema.tables
 WHERE table_name LIKE 'GSR%' AND table_type = 'VIEW'

SELECT table_name, FROM information_schema.tables
 WHERE table_name LIKE 'GSR%' AND table_type = 'BASE TABLE'