private void GetFields(TableInfo table)
{
string _sqltext = $@"SELECT
ORDINAL_POSITION
,COLUMN_NAME
,IS_NULLABLE
,COLUMN_TYPE
,(CASE WHEN COLUMN_TYPE IN ('tinyint(1)','char(36)') THEN COLUMN_TYPE ELSE DATA_TYPE END) AS DATA_TYPE
,COLUMN_COMMENT
,COALESCE(
CASE
WHEN DATA_TYPE IN ('tinyint','smallint','mediumint','int','bigint','bit','double','float','decimal') THEN NUMERIC_PRECISION
WHEN DATA_TYPE IN ('date','time','year','timestamp','datetime') THEN DATETIME_PRECISION
ELSE CHARACTER_MAXIMUM_LENGTH
END
,0) AS LENGTEH
,COALESCE(NUMERIC_SCALE,0) AS NUMERIC_SCALE
,(EXTRA='auto_increment') as auto_increment
from information_schema.`COLUMNS` where TABLE_SCHEMA='{table.Schema}' and TABLE_NAME='{table.Name}';";
_sqltext = string.Format(_sqltext, table.Schema, table.Name);
SQLContext.ExecuteDataReader(dr =>
{
DbFieldInfo fi = new DbFieldInfo
{
Oid = Convert.ToInt32(dr["ORDINAL_POSITION"]),
Name = dr["COLUMN_NAME"].ToString(),
Length = Convert.ToInt64(dr["LENGTEH"].ToString()),
NotNull = dr["IS_NULLABLE"].ToString() == "NO",
Comment = dr["COLUMN_COMMENT"].ToString(),
Numeric_scale = Convert.ToInt32(dr["NUMERIC_SCALE"].ToString()),
DbType = dr["DATA_TYPE"].ToString(),
AutoIncrement = Convert.ToBoolean(dr["auto_increment"])
};
fi.CsType = MysqlType.SwitchToCSharp(fi.DbType);
if (!fi.NotNull && fi.CsType != "string" && fi.CsType != "byte[]" && fi.CsType != "JsonElement")
{
fi.RelType = $"{fi.CsType}?";
}
else
{
fi.RelType = fi.CsType;
}
if ((fi.RelType == "string" && fi.Length != 0 && fi.Length != 255) || (fi.Numeric_scale > 0) || (MysqlType.ContrastType(fi.DbType) == null))
{
fi.DbTypeFull = dr["COLUMN_TYPE"].ToString();
}
table.Fields.Add(fi);
}, CommandType.Text, _sqltext);
if (table.Type == TableType.Table)
{
GetPrimarykey(table);
}
}
private void GetPrimarykey(TableInfo table)
{
string _sqltext = $@"SELECT COLUMN_NAME,CONSTRAINT_NAME FROM information_schema.KEY_COLUMN_USAGE where TABLE_SCHEMA = '{table.Schema}' and TABLE_NAME = '{table.Name}' AND CONSTRAINT_NAME = 'PRIMARY'";
SQLContext.ExecuteDataReader(dr =>
{
var constaint = new ConstraintInfo
{
Field = dr["COLUMN_NAME"].ToString(),
Name = dr["CONSTRAINT_NAME"].ToString(),
Type = ConstraintType.PK
};
table.Constraints.Add(constaint);
table.Fields.Where(f => f.Name == constaint.Field).First().PrimaryKey = true;
}, CommandType.Text, _sqltext);
}
public void Initialize(ProjectConfig config)
{
this.config = config;
Tables = new List <TableInfo>();
string schema = new MySqlConnection(config.ConnectionString).Database;
#region dir
CheckNotNull.NotEmpty(config.ContextName, nameof(config.ContextName));
if (config.Mode == GeneralInfo.Db)
{
CheckNotNull.NotEmpty(config.OutputDir, nameof(config.OutputDir));
Config = new GeneralConfig
{
OutputDir = config.OutputDir,
ProjectName = config.ContextName,
ModelPath = config.OutputDir
};
if (!Directory.Exists(Config.ModelPath))
{
Directory.CreateDirectory(Config.ModelPath);
}
}
#endregion
#region Tables
string _sqltext = $@"SELECT TABLE_SCHEMA,TABLE_NAME,(CASE WHEN TABLE_TYPE = 'BASE TABLE' THEN 'TABLE'ELSE TABLE_TYPE END ) AS TABLE_TYPE
FROM information_schema.`TABLES` WHERE TABLE_SCHEMA = '{schema}'";
SQLContext.ExecuteDataReader(dr =>
{
var table = new TableInfo()
{
Schema = dr["TABLE_SCHEMA"].ToString(),
Name = dr["TABLE_NAME"].ToString(),
Type = Enum.Parse <TableType>(dr["TABLE_TYPE"].ToString(), true)
};
GetFields(table);
Tables.Add(table);
}, CommandType.Text, _sqltext);
#endregion
}
public void Initialize(ProjectConfig config)
{
this.config = config;
Tables = new List <TableInfo>();
#region dir
CheckNotNull.NotEmpty(config.ContextName, nameof(config.ContextName));
if (config.Mode == GeneralInfo.Db)
{
CheckNotNull.NotEmpty(config.OutputDir, nameof(config.OutputDir));
Config = new GeneralConfig
{
OutputDir = config.OutputDir,
ProjectName = config.ContextName,
ModelPath = config.OutputDir
};
if (!Directory.Exists(Config.ModelPath))
{
Directory.CreateDirectory(Config.ModelPath);
}
}
#endregion
#region Schemas
string[] filters = new string[this.Filters.Count];
for (int i = 0; i < Filters.Count; i++)
{
filters[i] = $"'{Filters[i]}'";
}
string sql = $@"SELECT schema_name FROM information_schema.schemata WHERE SCHEMA_NAME NOT IN({string.Join(",", filters)}) ORDER BY SCHEMA_NAME; ";
List <string> schemas = new List <string>();
SQLContext.ExecuteDataReader(dr =>
{
schemas.Add(dr[0].ToString());
}, CommandType.Text, sql);
#endregion
#region Tables
foreach (var schema in schemas)
{
string _sqltext = $@"SELECT table_name,'table' as type FROM INFORMATION_SCHEMA.tables WHERE table_schema='{schema}' AND table_type='BASE TABLE'
UNION ALL
SELECT table_name,'view' as type FROM INFORMATION_SCHEMA.views WHERE table_schema = '{schema}'";
SQLContext.ExecuteDataReader(dr =>
{
var table = new TableInfo()
{
Schema = schema,
Name = dr["table_name"].ToString(),
Type = dr["type"].ToString() == "table" ? TableType.Table : TableType.View
};
GetFields(table);
Tables.Add(table);
}, CommandType.Text, _sqltext);
}
#endregion
}
