private void SetPrimaryKey(MySQLTableSchema oracleTable)
{
string sql = @"select tc.table_name,tc.constraint_name,kc.column_name,tb.auto_increment
from information_schema.table_constraints tc
left join information_schema.key_column_usage kc on tc.constraint_name = kc.constraint_name
and tc.table_schema = kc.table_schema and tc.table_name = kc.table_name
left join information_schema.tables tb on tb.table_name = tc.table_name and tb.table_schema = tc.table_schema
where tc.constraint_type = 'primary key'
and tc.table_name=@table_name and tc.table_schema=@table_schema";
var table = Db.Ado.GetDataTable(sql, new SugarParameter("@table_name", oracleTable.Name), new SugarParameter("@table_schema", DatabaseName));
Common.PrimaryKey key = new Common.PrimaryKey();
key.Columns = new List <IColumn>();
foreach (DataRow row in table.Rows)
{
string column_name = row["COLUMN_NAME"] + string.Empty;
string constraint_name = row["CONSTRAINT_NAME"] + string.Empty;
key.ConstraintName = constraint_name;
IColumn pkCol = oracleTable.Columns.Find(it => it.Name == column_name);
pkCol.IsAutoIncrement = !string.IsNullOrEmpty(row["AUTO_INCREMENT"] + string.Empty);
key.Columns.Add(pkCol);
}
oracleTable.PrimaryKey = key;
}
private void SetForeignKey(MySQLTableSchema oracleTable)
{
string sql = @"select tc.table_name, tc.constraint_name,kc.column_name,kc.referenced_table_name,kc.referenced_table_schema
from information_schema.table_constraints tc,information_schema.key_column_usage kc
where tc.constraint_type='FOREIGN KEY' and tc.constraint_name=kc.constraint_name
and tc.table_schema=kc.table_schema and tc.table_name=kc.table_name
and tc.table_name=@table_name and tc.table_schema=@table_schema";
var table = Db.Ado.GetDataTable(sql, new SugarParameter("@table_name", oracleTable.Name), new SugarParameter("@table_schema", DatabaseName));
oracleTable.ForiegnKeys = new List <Common.ForeignKey>();
foreach (DataRow row in table.Rows)
{
Common.ForeignKey key = new Common.ForeignKey();
key.Columns = new List <IColumn>();
string column_name = row["COLUMN_NAME"] + string.Empty;
string constraint_name = row["CONSTRAINT_NAME"] + string.Empty;
key.ConstraintName = constraint_name;
key.Columns.Add(oracleTable.Columns.Find(it => it.Name == column_name));
if (ContainForeignTable && key.ForeignTable == null)
{
string foreignTable = row["referenced_table_name"] + string.Empty;
string referenced_schema = row["referenced_table_schema"] + string.Empty;
var fac = new MySQLDbOperation(this.Db);
fac.ContainForeignTable = false;
fac._db_name = referenced_schema;
key.ForeignTable = fac.GetTableSchema(foreignTable);
}
oracleTable.ForiegnKeys.Add(key);
}
}
private void SetUniqueKey(MySQLTableSchema oracleTable)
{
string sql = @"select tc.table_name, tc.constraint_name,kc.column_name
from information_schema.table_constraints tc,information_schema.key_column_usage kc
where tc.constraint_type='UNIQUE' and tc.constraint_name=kc.constraint_name and tc.table_schema=kc.table_schema and tc.table_name=kc.table_name and tc.table_name=@table_name and tc.table_schema=@table_schema";
var table = Db.Ado.GetDataTable(sql, new SugarParameter("@table_name", oracleTable.Name), new SugarParameter("@table_schema", DatabaseName));
oracleTable.UniqueKeys = new List <Common.UniqueKey>();
foreach (DataRow row in table.Rows)
{
string column_name = row["COLUMN_NAME"] + string.Empty;
string constraint_name = row["CONSTRAINT_NAME"] + string.Empty;
Common.UniqueKey key = oracleTable.UniqueKeys.Find(it => it.ConstraintName == constraint_name);
if (key == null)
{
key = new Common.UniqueKey();
key.Columns = new List <IColumn>();
key.ConstraintName = constraint_name;
oracleTable.UniqueKeys.Add(key);
}
key.Columns.Add(oracleTable.Columns.Find(it => it.Name == column_name));
}
}
public override ITableSchema GetTableSchema(string table_name)
{
var db = GetDatabaseSchema();
MySQLTableSchema oracleTable = (MySQLTableSchema)db.Tables.Find(it => it.Name.Equals(table_name));
SetColumns(oracleTable);
SetForeignKey(oracleTable);
SetUniqueKey(oracleTable);
SetPrimaryKey(oracleTable);
return(oracleTable);
}
public override DatabaseSchema GetDatabaseSchema()
{
DatabaseSchema db = new DatabaseSchema();
db.Tables = new List <ITableSchema>();
string sql = @"SELECT TABLE_NAME,TABLE_COMMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA=@TARGET_SCHEMA";
var data = Db.Ado.GetDataTable(sql, new SugarParameter("@TARGET_SCHEMA", DatabaseName));
foreach (DataRow row in data.Rows)
{
MySQLTableSchema table = new MySQLTableSchema();
table.Name = row["TABLE_NAME"] + string.Empty;
table.Comment = row["TABLE_COMMENT"] + string.Empty;
db.Tables.Add(table);
}
return(db);
}
private void SetColumns(MySQLTableSchema oracleTable)
{
string sql = @"select column_name,data_type,
character_maximum_length as data_length,
numeric_precision as data_precision,
numeric_scale as data_scale,
is_nullable as nullable,
column_default as data_default,
column_comment as comments
from information_schema.columns where table_schema=@target_schema and table_name=@target_table";
var table = Db.Ado.GetDataTable(sql,
new SugarParameter("@table_name", oracleTable.Name),
new SugarParameter("@table_schema", DatabaseName));
oracleTable.Columns = new List <IColumn>();
foreach (DataRow row in table.Rows)
{
int scale = row.GetInt("DATA_SCALE");
string data_type = row["DATA_TYPE"] + string.Empty;
MySQLColumn column = new MySQLColumn
{
Name = row["COLUMN_NAME"] + string.Empty,
Comment = row["COMMENTS"] + string.Empty,
CsharpType = MySQLUtils.TransformDatabaseType(data_type, scale),
DbType = data_type,
DefaultValue = row["DATA_DEFAULT"] + string.Empty,
IsNullable = (row["NULLABLE"] + string.Empty) != "NO",
Length = row.GetInt("DATA_LENGTH"),
Scale = scale,
Table = oracleTable,
IsNumeric = MySQLUtils.IsNumeric(data_type)
};
oracleTable.Columns.Add(column);
}
}
