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 upper(tc.constraint_type) = 'PRIMARY KEY' and tc.table_name=@table_name and tc.table_schema=@table_schema"; MySqlParameter para0 = new MySqlParameter("@table_name", oracleTable.Name); MySqlParameter para1 = new MySqlParameter("@table_schema", this.DatabaseName); DbHelper helper = new DbHelper(this._connectionString); var table = helper.ListBySql(sql, para0, para1); Common.PrimaryKey key = new Common.PrimaryKey(); key.Columns = new ColumnCollection(); 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"; MySqlParameter para0 = new MySqlParameter("@table_name", oracleTable.Name); MySqlParameter para1 = new MySqlParameter("@table_schema", this.DatabaseName); DbHelper helper = new DbHelper(this._connectionString); var table = helper.ListBySql(sql, para0, para1); oracleTable.ForiegnKeys = new List <Common.ForeignKey>(); foreach (DataRow row in table.Rows) { Common.ForeignKey key = new Common.ForeignKey(); key.Columns = new ColumnCollection(); 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)); string foreignTable = row["referenced_table_name"] + string.Empty; if (ContainForeignTable && key.ForeignTable == null && !string.IsNullOrEmpty(foreignTable)) { string referenced_schema = row["referenced_table_schema"] + string.Empty; var fac = new MySQLDataFactory(this._connectionString); 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 upper(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"; MySqlParameter para0 = new MySqlParameter("@table_name", oracleTable.Name); MySqlParameter para1 = new MySqlParameter("@table_schema", this.DatabaseName); DbHelper helper = new DbHelper(this._connectionString); var table = helper.ListBySql(sql, para0, para1); 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 ColumnCollection(); 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 mysqlTable = (MySQLTableSchema)db.Tables.Find(it => it.Name.Equals(table_name)); SetColumns(mysqlTable); SetForeignKey(mysqlTable); SetUniqueKey(mysqlTable); SetPrimaryKey(mysqlTable); return(mysqlTable); }
public override DatabaseSchema GetDatabaseSchema() { DatabaseSchema db = new DatabaseSchema(); db.Tables = new List <ITableSchema>(); string sql = @"SELECT table_name,TABLE_COMMENT,IF(table_type='BASE TABLE','TABLE','VIEW') as object_type FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA=@TARGET_SCHEMA"; DbHelper helper = new DbHelper(this._connectionString); var data = helper.ListBySql(sql, new MySqlParameter("@TARGET_SCHEMA", this.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; table.ObjectType = row["OBJECT_TYPE"] + 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, case when extra='auto_increment' then 1 else 0 end as auto_increment from information_schema.columns where table_schema=@target_schema and table_name=@target_table order by ordinal_position"; DbHelper helper = new DbHelper(this._connectionString); var table = helper.ListBySql(sql, new MySqlParameter("@TARGET_SCHEMA", this.DatabaseName), new MySqlParameter("@TARGET_TABLE", oracleTable.Name)); oracleTable.Columns = new ColumnCollection(); 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), IsAutoIncrement = Convert.ToInt32(row["auto_increment"]) == 1, }; oracleTable.Columns.Add(column); } }