Mapeamento de tabelas para o Entity Framework com banco MySQL

mapeamento_entityframework_mysql.sql

-- Por Ycaro Afonso
-- 2016-03-05
-- Entity Framework mapping generator for MySQL
DROP TABLE IF EXISTS gis.fw_vw_framework_column;
DROP TABLE IF EXISTS gis.fw_vw_framework_constraint;
DROP TABLE IF EXISTS gis.fw_vw_framework_custom_constraint_item_unico; 
DROP TABLE IF EXISTS gis.fw_vw_framework_mapeamento_entity_model_builder;
DROP TABLE IF EXISTS gis.fw_vw_framework_mapeamento_entity_propriedades;
DROP TABLE IF EXISTS gis.fw_vw_framework_mapeamento_entity_propriedades_com_filhos;
DROP TABLE IF EXISTS gis.fw_vw_framework_table;


CREATE TABLE IF NOT EXISTS FW_VW_FRAMEWORK_TABLE AS (SELECT
    T.TABLE_CATALOG,
    T.TABLE_TYPE,
    T.TABLE_SCHEMA,
    T.TABLE_NAME
  FROM INFORMATION_SCHEMA.TABLES T
  WHERE T.TABLE_SCHEMA = DATABASE());


CREATE TABLE IF NOT EXISTS FW_VW_FRAMEWORK_COLUMN AS (SELECT
    *
  FROM (SELECT
      T.TABLE_SCHEMA,
      T.TABLE_NAME,
      C.COLUMN_NAME,
      C.ORDINAL_POSITION,
      C.COLUMN_DEFAULT,
      C.IS_NULLABLE,
      C.DATA_TYPE,
      IFNULL(C.CHARACTER_MAXIMUM_LENGTH, C.NUMERIC_PRECISION) TAMANHO,
      C.NUMERIC_SCALE,
      CASE WHEN C.EXTRA LIKE '%auto_increment%' THEN 1 ELSE 0 END IS_IDENTITY
    FROM INFORMATION_SCHEMA.TABLES T
      JOIN INFORMATION_SCHEMA.COLUMNS C
        ON T.TABLE_NAME = C.TABLE_NAME
        AND T.TABLE_SCHEMA = C.TABLE_SCHEMA
    WHERE T.TABLE_SCHEMA = DATABASE()) AS XX);


CREATE TABLE IF NOT EXISTS FW_VW_FRAMEWORK_CONSTRAINT
AS (SELECT
    *
  FROM (SELECT
  TC.TABLE_SCHEMA,
  TC.TABLE_NAME,
  KCU.COLUMN_NAME,
  TC.CONSTRAINT_TYPE,
  KCU.CONSTRAINT_NAME,
  KCU.REFERENCED_TABLE_SCHEMA TABLE_SCHEMA_FOREIGN_KEY,
  KCU.REFERENCED_TABLE_NAME TABLE_NAME_FOREIGN_KEY,
  KCU.REFERENCED_COLUMN_NAME COLUMN_NAME_FOREIGN_KEY
FROM information_schema.TABLE_CONSTRAINTS TC
  JOIN information_schema.KEY_COLUMN_USAGE KCU
    ON KCU.CONSTRAINT_SCHEMA = TC.CONSTRAINT_SCHEMA
    AND KCU.CONSTRAINT_NAME = TC.CONSTRAINT_NAME
    AND TC.TABLE_SCHEMA = KCU.TABLE_SCHEMA
    AND TC.TABLE_NAME = KCU.TABLE_NAME
WHERE TC.TABLE_SCHEMA = DATABASE()) AS XX);


CREATE TABLE IF NOT EXISTS FW_VW_FRAMEWORK_CUSTOM_CONSTRAINT_ITEM_UNICO
AS (SELECT
    *
  FROM (SELECT
      CS.TABLE_SCHEMA,
      CS.TABLE_NAME,
      CS.COLUMN_NAME,
      CASE SUM(CASE WHEN CS.CONSTRAINT_TYPE = 'PRIMARY KEY' THEN 1 ELSE 0 END) WHEN 0 THEN 0 ELSE 1 END IS_PRIMARY_KEY,
      CASE SUM(CASE WHEN CS.CONSTRAINT_TYPE = 'PRIMARY KEY' THEN C.IS_IDENTITY ELSE 0 END) WHEN 0 THEN 0 ELSE 1 END IS_IDENTITY,
      MIN(CS.TABLE_SCHEMA_FOREIGN_KEY) TABLE_SCHEMA_FOREIGN_KEY,
      MIN(CS.TABLE_NAME_FOREIGN_KEY) TABLE_NAME_FOREIGN_KEY,
      MIN(COLUMN_NAME_FOREIGN_KEY) COLUMN_NAME_FOREIGN_KEY
    FROM FW_VW_FRAMEWORK_CONSTRAINT CS
      JOIN FW_VW_FRAMEWORK_COLUMN C
        ON C.TABLE_SCHEMA = CS.TABLE_SCHEMA
        AND C.TABLE_NAME = CS.TABLE_NAME
		AND CS.COLUMN_NAME=C.COLUMN_NAME
    GROUP BY CS.TABLE_SCHEMA,
             CS.TABLE_NAME,
             CS.COLUMN_NAME) AS XX);


CREATE TABLE IF NOT EXISTS FW_VW_FRAMEWORK_MAPEAMENTO_ENTITY_PROPRIEDADES AS (SELECT
  T.TABLE_TYPE,
  C.TABLE_SCHEMA,
  C.TABLE_NAME,
  C.COLUMN_NAME,
  CONCAT((CASE C.DATA_TYPE WHEN 'varchar' THEN 'string' WHEN 'datetime' THEN 'DateTime' WHEN 'date' THEN 'DateTime' WHEN 'char' THEN 'string' WHEN 'bit' THEN 'bool' WHEN 'image' THEN 'byte[]' WHEN 'numeric' THEN 'decimal' ELSE C.DATA_TYPE END), (CASE C.DATA_TYPE WHEN 'varchar' THEN '' WHEN 'image' THEN '' ELSE CASE C.IS_NULLABLE WHEN 'YES' THEN '?' ELSE '' END END)) tipo,
  -- Annotations
  CONCAT(CASE CCIU.IS_PRIMARY_KEY WHEN 1 THEN '[Key]' ELSE '' END, CASE WHEN C.DATA_TYPE = 'varchar' AND
      C.IS_NULLABLE = 'NO' THEN '[Required]' ELSE '' END, CASE WHEN QTDE_CHAVE_PRIMARIA > 1 AND
      CCIU.IS_PRIMARY_KEY = 1 THEN CONCAT('[Column(Order = ', '1', ')]') ELSE '' END, CASE WHEN C.DATA_TYPE = 'varchar' THEN CONCAT('[StringLength(', CAST(C.tamanho AS char(20)), ')]') ELSE '' END, CASE WHEN CCIU.IS_PRIMARY_KEY = 1 AND
      CCIU.IS_IDENTITY = 0 THEN '[DatabaseGenerated(DatabaseGeneratedOption.None)]' ELSE '' END) Annotation,
  --
  IFNULL(CCIU.IS_PRIMARY_KEY, 0) IS_PRIMARY_KEY,
  C.IS_NULLABLE IS_NULLABLE_COLUMN_INTERNA,
  CCIU.TABLE_SCHEMA_FOREIGN_KEY TABLE_SCHEMA_REFERENCIA,
  CCIU.TABLE_NAME_FOREIGN_KEY TABLE_NAME_REFERENCIA,
  CCIU.COLUMN_NAME_FOREIGN_KEY COLUMN_NAME_REFERENCIA_EXTERNA,
  CCIU.IS_PRIMARY_KEY IS_PRIMARY_KEY_EXTERNA,
  CONCAT(CCIU.TABLE_NAME_FOREIGN_KEY, (CASE WHEN (SELECT
          COUNT(1)
        FROM FW_VW_FRAMEWORK_CONSTRAINT Cs
        WHERE Cs.TABLE_SCHEMA = C.TABLE_SCHEMA
        AND Cs.TABLE_NAME = C.TABLE_NAME
        AND Cs.TABLE_SCHEMA_FOREIGN_KEY = CCIU.TABLE_SCHEMA_FOREIGN_KEY
        AND Cs.TABLE_NAME_FOREIGN_KEY = CCIU.TABLE_NAME_FOREIGN_KEY) > 1 OR
      C.TABLE_NAME = CCIU.TABLE_NAME_FOREIGN_KEY
      #OR CCIU.COLUMN_NAME <> CCIU.COLUMN_NAME_FOREIGN_KEY -- 2015-9-23
	  OR CCIU.TABLE_NAME_FOREIGN_KEY = 'dominio'
      THEN REPLACE(CCIU.COLUMN_NAME,
        CCIU.COLUMN_NAME_FOREIGN_KEY, '') ELSE '' END)) NomePropriedadeReferenciaInterna,
  CONCAT(CCIU.TABLE_NAME, (CASE WHEN (SELECT
          COUNT(1)
        FROM FW_VW_FRAMEWORK_CONSTRAINT Cs
        WHERE TABLE_SCHEMA = C.TABLE_SCHEMA
        AND Cs.TABLE_NAME = C.TABLE_NAME
        AND Cs.TABLE_SCHEMA_FOREIGN_KEY = CCIU.TABLE_SCHEMA_FOREIGN_KEY
        AND Cs.TABLE_NAME_FOREIGN_KEY = CCIU.TABLE_NAME_FOREIGN_KEY) > 1 THEN REPLACE(CCIU.COLUMN_NAME, CCIU.COLUMN_NAME_FOREIGN_KEY,
        '') ELSE '' END), (CASE WHEN C.TABLE_NAME = CCIU.TABLE_NAME_FOREIGN_KEY THEN 'Filhos' ELSE '' END)) NomePropriedadeReferenciaExterna
FROM (SELECT
    NULL TABLE_TYPE, -- Retorna apenas tabelas que possuí chave primária
    CT.TABLE_SCHEMA,
    CT.TABLE_NAME,
    COUNT(1) QTDE_CHAVE_PRIMARIA
  FROM gis.fw_vw_framework_constraint CT
  WHERE CONSTRAINT_TYPE = 'PRIMARY KEY'
  GROUP BY CT.TABLE_SCHEMA,
           CT.TABLE_NAME) T
  JOIN FW_VW_FRAMEWORK_COLUMN C
    ON T.TABLE_SCHEMA = C.TABLE_SCHEMA
    AND T.TABLE_NAME = C.TABLE_NAME
  LEFT JOIN FW_VW_FRAMEWORK_CUSTOM_CONSTRAINT_ITEM_UNICO CCIU
    ON C.TABLE_SCHEMA = CCIU.TABLE_SCHEMA
    AND C.TABLE_NAME = CCIU.TABLE_NAME
    AND C.COLUMN_NAME = CCIU.COLUMN_NAME
  #LEFT JOIN FW_VW_FRAMEWORK_COLUMN C2
    #ON C2.TABLE_SCHEMA = CCIU.TABLE_SCHEMA_FOREIGN_KEY
    #AND C2.TABLE_NAME = CCIU.TABLE_NAME_FOREIGN_KEY
    #AND C2.COLUMN_NAME = CCIU.COLUMN_NAME_FOREIGN_KEY
	);


	  
	  
	  
	  
	  
	  
	  
	  
	  
	  
	  
	  
	  
CREATE TABLE IF NOT EXISTS FW_VW_FRAMEWORK_MAPEAMENTO_ENTITY_PROPRIEDADES_COM_FILHOS
AS (SELECT
    *
  FROM (SELECT
      TABLE_TYPE,
      TABLE_SCHEMA,
      TABLE_NAME Classe,
      'public' Permissao,
      Tipo,
      COLUMN_NAME NomePropriedade,
      Annotation,
      --
      '' NomePropriedadeReferenciaExterna,
      --
      COLUMN_NAME COLUMN_NAME_REFERENCIA_INTERNA,
      IS_PRIMARY_KEY IS_PRIMARY_KEY_INTERNA,
      IS_NULLABLE_COLUMN_INTERNA,
      '' TABLE_NAME_REFERENCIA_EXTERNA,
      '' COLUMN_NAME_REFERENCIA_EXTERNA,
      '' IS_PRIMARY_KEY_EXTERNA,
      '' IS_NULLABLE_COLUMN_EXTERNA,
      1 Parte
    FROM FW_VW_FRAMEWORK_MAPEAMENTO_ENTITY_PROPRIEDADES UNION ALL SELECT
      TABLE_TYPE,
      TABLE_SCHEMA,
      TABLE_NAME Classe,
      'public virtual' Permissao,
      TABLE_NAME_REFERENCIA Tipo,
      NomePropriedadeReferenciaInterna NomePropriedade,
      CASE WHEN NomePropriedadeReferenciaInterna <> TABLE_NAME_REFERENCIA THEN CONCAT('[ForeignKey("', COLUMN_NAME, '")]') ELSE '' END Annotation,
      -- Annotation,
      --
      NomePropriedadeReferenciaExterna,
      --
      COLUMN_NAME COLUMN_NAME_REFERENCIA_INTERNA,
      '' IS_PRIMARY_KEY_INTERNA,
      IS_NULLABLE_COLUMN_INTERNA,
      TABLE_NAME_REFERENCIA TABLE_NAME_REFERENCIA_EXTERNA,
      COLUMN_NAME_REFERENCIA_EXTERNA,
      '' IS_PRIMARY_KEY_EXTERNA,
      '' IS_NULLABLE_COLUMN_EXTERNA,
      2 Parte
    FROM FW_VW_FRAMEWORK_MAPEAMENTO_ENTITY_PROPRIEDADES
    WHERE TABLE_NAME_REFERENCIA IS NOT NULL UNION ALL SELECT
      TABLE_TYPE,
      TABLE_SCHEMA_REFERENCIA,
      TABLE_NAME_REFERENCIA Classe,
      'public virtual' Permissao,
      CASE WHEN IS_PRIMARY_KEY = 1 AND
          ((SELECT
              COUNT(1) -- Verifica se a tabela filha tem mais de uma chave primaria, se tiver o Tipo é ICollection
            FROM FW_VW_FRAMEWORK_CUSTOM_CONSTRAINT_ITEM_UNICO
            WHERE TABLE_NAME = MEP.TABLE_NAME
            AND IS_PRIMARY_KEY = 1) = 1) THEN TABLE_NAME ELSE CONCAT('ICollection<', TABLE_NAME, '>') END Tipo,
      NomePropriedadeReferenciaExterna NomePropriedade,
      CASE WHEN NomePropriedadeReferenciaInterna <> COLUMN_NAME AND
          TABLE_NAME_REFERENCIA = TABLE_NAME THEN CONCAT('[ForeignKey("', COLUMN_NAME, '")]') ELSE '' END Annotation,
      --
      NomePropriedadeReferenciaInterna NomePropriedadeReferenciaExterna,
      --
      '' COLUMN_NAME_REFERENCIA_INTERNA,
      '' IS_PRIMARY_KEY_INTERNA,
      NULL IS_NULLABLE_COLUMN_INTERNA,
      TABLE_NAME TABLE_NAME_REFERENCIA_EXTERNA,
      COLUMN_NAME COLUMN_NAME_REFERENCIA_EXTERNA,
      IS_PRIMARY_KEY_EXTERNA,
      IS_NULLABLE_COLUMN_INTERNA IS_NULLABLE_COLUMN_EXTERNA,
      3 Parte
    FROM FW_VW_FRAMEWORK_MAPEAMENTO_ENTITY_PROPRIEDADES MEP
    WHERE TABLE_NAME_REFERENCIA IS NOT NULL) AS XXX);



CREATE TABLE IF NOT EXISTS FW_VW_FRAMEWORK_MAPEAMENTO_ENTITY_MODEL_BUILDER
AS (SELECT
    Parte,
    TipoClasse,
    Classe,
    CONCAT('modelBuilder.Entity<', Classe, '>().', Mapeamento) Mapeamento
  FROM (SELECT
      1 Parte,
      MEPF.TABLE_TYPE TipoClasse,
      Classe,
      CASE WHEN MEPF.Tipo = 'string' THEN CONCAT('Property(e => e.', MEPF.NomePropriedade, ').IsUnicode(false);') WHEN MEPF.Tipo = 'decimal' OR
          MEPF.Tipo = 'decimal?' THEN CONCAT('Property(e => e.', MEPF.NomePropriedade, ').HasPrecision(', CAST(C.TAMANHO AS char(10)), ', ', CAST(C.NUMERIC_SCALE AS char(10)), ');') END Mapeamento
    FROM FW_VW_FRAMEWORK_MAPEAMENTO_ENTITY_PROPRIEDADES_COM_FILHOS MEPF
      JOIN FW_VW_FRAMEWORK_COLUMN C
        ON C.COLUMN_NAME = MEPF.NomePropriedade
        AND C.TABLE_NAME = MEPF.Classe
    WHERE Permissao = 'public'
    AND MEPF.Tipo IN ('string', 'decimal', 'decimal?') UNION ALL SELECT
      2 Parte,
      MEPF.TABLE_TYPE TipoClasse,
      MEPF.Classe,
      CONCAT('HasMany(e => e.', MEPF.NomePropriedade, ').', (
      CASE WHEN MEPF.IS_NULLABLE_COLUMN_INTERNA = 'NO' OR
          MEPF.IS_NULLABLE_COLUMN_EXTERNA = 'NO' THEN 'WithRequired' ELSE 'WithOptional' END), '(e => e.', MEPF.NomePropriedadeReferenciaExterna, ').HasForeignKey(e => e.', MEPF.COLUMN_NAME_REFERENCIA_EXTERNA, ').WillCascadeOnDelete(false);')
    FROM FW_VW_FRAMEWORK_MAPEAMENTO_ENTITY_PROPRIEDADES_COM_FILHOS MEPF
    WHERE MEPF.Tipo LIKE 'ICollection%'
    AND (
    (
    IFNULL(MEPF.IS_NULLABLE_COLUMN_INTERNA, 'NO') = 'NO'
    OR MEPF.IS_NULLABLE_COLUMN_EXTERNA = 'NO'
    )
    OR (MEPF.Classe = MEPF.TABLE_NAME_REFERENCIA_EXTERNA)
    )

    -- 1p1
    UNION ALL SELECT
      3 Parte,
      MEPF.TABLE_TYPE TipoClasse,
      Classe,
      CONCAT('HasOptional(e => e.', MEPF.NomePropriedade, ').WithRequired(e => e.', MEPF.NomePropriedadeReferenciaExterna, ');') Mapeamento
    FROM FW_VW_FRAMEWORK_MAPEAMENTO_ENTITY_PROPRIEDADES_COM_FILHOS MEPF
    WHERE IS_PRIMARY_KEY_EXTERNA = 1
    AND (SELECT
        COUNT(1)
      FROM FW_VW_FRAMEWORK_CUSTOM_CONSTRAINT_ITEM_UNICO
      WHERE TABLE_NAME = MEPF.TABLE_NAME_REFERENCIA_EXTERNA
      AND IS_PRIMARY_KEY = 1) = 1) AS XX);



DROP TABLE IF EXISTS gis.FW_VW_MAPEAMENTO;
	  
CREATE TABLE IF NOT EXISTS FW_VW_MAPEAMENTO
AS (SELECT
    *
  FROM (SELECT
      Classe,
      CONCAT(Annotation, ' ', Permissao, ' ', Tipo, ' ', NomePropriedade, ' { get; set; }') Propriedade,
      Parte
    FROM gis.fw_vw_framework_mapeamento_entity_propriedades_com_filhos UNION ALL SELECT
      Classe,
      Mapeamento,
      '' Tipo
    FROM gis.fw_vw_framework_mapeamento_entity_model_builder) AS XX);



DROP TABLE IF EXISTS gis.fw_vw_framework_column;
DROP TABLE IF EXISTS gis.fw_vw_framework_constraint;
DROP TABLE IF EXISTS gis.fw_vw_framework_custom_constraint_item_unico;
DROP TABLE IF EXISTS gis.fw_vw_framework_mapeamento_entity_model_builder;
DROP TABLE IF EXISTS gis.fw_vw_framework_mapeamento_entity_propriedades;
DROP TABLE IF EXISTS gis.fw_vw_framework_mapeamento_entity_propriedades_com_filhos;
DROP TABLE IF EXISTS gis.fw_vw_framework_table;