public List <TableInfo> GetTablesByDatabase(string database) { _client.Database = database; Logger.remotor.Info("GetTablesByDatabase: " + _client.Server + "," + _client.Username + "," + _client.Password + "," + _client.Database); List <TableInfo> loc1 = _tables = null; Dictionary <int, TableInfo> loc2 = new Dictionary <int, TableInfo>(); Dictionary <int, Dictionary <string, ColumnInfo> > loc3 = new Dictionary <int, Dictionary <string, ColumnInfo> >(); DataSet ds = this.GetDataSet(@" select a.Object_id ,b.name 'Owner' ,a.name 'Name' ,'T' type from sys.tables a inner join sys.schemas b on b.schema_id = a.schema_id where a.name <> 'nicpetshop_config_fk' union all select a.Object_id ,b.name 'Owner' ,a.name 'Name' ,'P' type from sys.procedures a inner join sys.schemas b on b.schema_id = a.schema_id where a.type = 'P' and charindex('$NPSP', a.name) = 0 and charindex('diagram', a.name) = 0 order by type desc, b.name, a.name "); if (ds == null) { return(loc1); } List <int> loc6 = new List <int>(); List <int> loc66 = new List <int>(); foreach (DataRow row in ds.Tables[0].Rows) { int object_id = int.Parse(string.Concat(row[0])); string owner = string.Concat(row[1]); string table = string.Concat(row[2]); string type = string.Concat(row[3]); loc2.Add(object_id, new TableInfo(object_id, owner, table, type)); loc3.Add(object_id, new Dictionary <string, ColumnInfo>()); switch (type) { case "T": loc6.Add(object_id); break; case "P": loc66.Add(object_id); break; } } if (loc6.Count == 0) { return(loc1); } string loc8 = string.Join(",", loc6.ConvertAll <string>(delegate(int item) { return(string.Concat(item)); }).ToArray()); string loc88 = string.Join(",", loc66.ConvertAll <string>(delegate(int item) { return(string.Concat(item)); }).ToArray()); string tsql_place = @" select a.Object_id ,a.name 'Column' ,b.name 'Type' ,case when b.name in ('Text', 'NText', 'Image') then -1 when b.name in ('NChar', 'NVarchar') then a.max_length / 2 else a.max_length end 'Length' ,b.name + case when b.name in ('Char', 'VarChar', 'NChar', 'NVarChar', 'Binary', 'VarBinary') then '(' + case when a.max_length = -1 then 'MAX' when b.name in ('NChar', 'NVarchar') then cast(a.max_length / 2 as varchar) else cast(a.max_length as varchar) end + ')' when b.name in ('Numeric', 'Decimal') then '(' + cast(a.precision as varchar) + ',' + cast(a.scale as varchar) + ')' else '' end as 'SqlType' {0} a inner join sys.types b on b.user_type_id = a.user_type_id where a.object_id in ({1}) "; string tsql = string.Format(tsql_place, @" ,a.is_nullable 'IsNullable' ,a.is_identity 'IsIdentity' from sys.columns", loc8); if (loc88.Length > 0) { tsql += "union all" + string.Format(tsql_place, @" ,cast(0 as bit) 'IsNullable' ,a.is_output 'IsIdentity' from sys.parameters", loc88); } ds = this.GetDataSet(tsql); if (ds == null) { return(loc1); } foreach (DataRow row in ds.Tables[0].Rows) { int object_id = int.Parse(string.Concat(row[0])); string column = string.Concat(row[1]); string type = string.Concat(row[2]); int max_length = int.Parse(string.Concat(row[3])); string sqlType = string.Concat(row[4]); bool is_nullable = bool.Parse(string.Concat(row[5])); bool is_identity = bool.Parse(string.Concat(row[6])); if (max_length == 0) { max_length = -1; } loc3[object_id].Add(column, new ColumnInfo( column, CodeBuild.GetDBType(type), max_length, sqlType, DataSort.NONE, is_nullable, is_identity, false, false)); } ds = this.GetDataSet(string.Format(@" select a.object_id 'Object_id' ,c.name 'Column' ,b.index_id 'Index_id' ,b.is_unique 'IsUnique' ,b.is_primary_key 'IsPrimaryKey' ,cast(case when b.type_desc = 'CLUSTERED' then 1 else 0 end as bit) 'IsClustered' ,case when a.is_descending_key = 1 then 2 when a.is_descending_key = 0 then 1 else 0 end 'IsDesc' from sys.index_columns a inner join sys.indexes b on b.object_id = a.object_id and b.index_id = a.index_id left join sys.columns c on c.object_id = a.object_id and c.column_id = a.column_id where a.object_id in ({0}) ", loc8)); if (ds == null) { return(loc1); } Dictionary <int, Dictionary <int, List <ColumnInfo> > > indexColumns = new Dictionary <int, Dictionary <int, List <ColumnInfo> > >(); Dictionary <int, Dictionary <int, List <ColumnInfo> > > uniqueColumns = new Dictionary <int, Dictionary <int, List <ColumnInfo> > >(); foreach (DataRow row in ds.Tables[0].Rows) { int object_id = int.Parse(string.Concat(row[0])); string column = string.Concat(row[1]); int index_id = int.Parse(string.Concat(row[2])); bool is_unique = bool.Parse(string.Concat(row[3])); bool is_primary_key = bool.Parse(string.Concat(row[4])); bool is_clustered = bool.Parse(string.Concat(row[5])); int is_desc = int.Parse(string.Concat(row[6])); ColumnInfo loc9 = loc3[object_id][column]; if (loc9.IsClustered == false && is_clustered) { loc9.IsClustered = is_clustered; } if (loc9.IsPrimaryKey == false && is_primary_key) { loc9.IsPrimaryKey = is_primary_key; } if (loc9.Orderby == DataSort.NONE) { loc9.Orderby = (DataSort)is_desc; } Dictionary <int, List <ColumnInfo> > loc10 = null; List <ColumnInfo> loc11 = null; if (!indexColumns.TryGetValue(object_id, out loc10)) { indexColumns.Add(object_id, loc10 = new Dictionary <int, List <ColumnInfo> >()); } if (!loc10.TryGetValue(index_id, out loc11)) { loc10.Add(index_id, loc11 = new List <ColumnInfo>()); } loc11.Add(loc9); if (is_unique) { if (!uniqueColumns.TryGetValue(object_id, out loc10)) { uniqueColumns.Add(object_id, loc10 = new Dictionary <int, List <ColumnInfo> >()); } if (!loc10.TryGetValue(index_id, out loc11)) { loc10.Add(index_id, loc11 = new List <ColumnInfo>()); } loc11.Add(loc9); } } foreach (int object_id in indexColumns.Keys) { foreach (List <ColumnInfo> columns in indexColumns[object_id].Values) { loc2[object_id].Indexes.Add(columns); } } foreach (int object_id in uniqueColumns.Keys) { foreach (List <ColumnInfo> columns in uniqueColumns[object_id].Values) { columns.Sort(delegate(ColumnInfo c1, ColumnInfo c2) { return(c1.Name.CompareTo(c2.Name)); }); loc2[object_id].Uniques.Add(columns); } } ds = this.GetDataSet(string.Format(@" select b.object_id 'Object_id' ,c.name 'Column' ,a.constraint_object_id 'FKId' ,referenced_object_id ,cast(1 as bit) 'IsForeignKey' ,d.name 'Referenced_Column' ,null 'Referenced_Sln' ,null 'Referenced_Table' from sys.foreign_key_columns a inner join sys.tables b on b.object_id = a.parent_object_id inner join sys.columns c on c.object_id = a.parent_object_id and c.column_id = a.parent_column_id inner join sys.columns d on d.object_id = a.referenced_object_id and d.column_id = a.referenced_column_id where b.object_id in ({0}) ", loc8)); if (ds == null) { return(loc1); } Dictionary <int, Dictionary <int, ForeignKeyInfo> > fkColumns = new Dictionary <int, Dictionary <int, ForeignKeyInfo> >(); foreach (DataRow row in ds.Tables[0].Rows) { int object_id, fk_id, referenced_object_id; int.TryParse(string.Concat(row[0]), out object_id); string column = string.Concat(row[1]); int.TryParse(string.Concat(row[2]), out fk_id); int.TryParse(string.Concat(row[3]), out referenced_object_id); bool is_foreign_key = bool.Parse(string.Concat(row[4])); string referenced_column = string.Concat(row[5]); string referenced_db = string.Concat(row[6]); string referenced_table = string.Concat(row[7]); ColumnInfo loc9 = loc3[object_id][column]; TableInfo loc10 = null; ColumnInfo loc11 = null; bool isThisSln = referenced_object_id != 0; if (isThisSln) { loc10 = loc2[referenced_object_id]; loc11 = loc3[referenced_object_id][referenced_column]; } else { } Dictionary <int, ForeignKeyInfo> loc12 = null; ForeignKeyInfo loc13 = null; if (!fkColumns.TryGetValue(object_id, out loc12)) { fkColumns.Add(object_id, loc12 = new Dictionary <int, ForeignKeyInfo>()); } if (!loc12.TryGetValue(fk_id, out loc13)) { if (isThisSln) { loc13 = new ForeignKeyInfo(loc2[object_id], loc10); } else { loc13 = new ForeignKeyInfo(referenced_db, referenced_table, is_foreign_key); } loc12.Add(fk_id, loc13); } loc13.Columns.Add(loc9); if (isThisSln) { loc13.ReferencedColumns.Add(loc11); } else { loc13.ReferencedColumnNames.Add(referenced_column); } } foreach (int object_id in fkColumns.Keys) { foreach (ForeignKeyInfo fk in fkColumns[object_id].Values) { loc2[object_id].ForeignKeys.Add(fk); } } foreach (int loc4 in loc3.Keys) { foreach (ColumnInfo loc5 in loc3[loc4].Values) { loc2[loc4].Columns.Add(loc5); if (loc5.IsIdentity) { loc2[loc4].Identitys.Add(loc5); } if (loc5.IsClustered) { loc2[loc4].Clustereds.Add(loc5); } if (loc5.IsPrimaryKey) { loc2[loc4].PrimaryKeys.Add(loc5); } } } loc1 = _tables = new List <TableInfo>(); foreach (TableInfo loc4 in loc2.Values) { if (loc4.PrimaryKeys.Count == 0 && loc4.Uniques.Count > 0) { foreach (ColumnInfo loc5 in loc4.Uniques[0]) { loc5.IsPrimaryKey = true; loc4.PrimaryKeys.Add(loc5); } } this.Sort(loc4); loc1.Add(loc4); } loc2.Clear(); loc3.Clear(); return(loc1); }
public List <TableInfo> GetTablesByDatabase(string database) { _client.Database = database; Logger.remotor.Info("GetTablesByDatabase: " + _client.Server + "," + _client.Username + "," + _client.Password + "," + _client.Database); List <TableInfo> loc1 = _tables = null; Dictionary <string, TableInfo> loc2 = new Dictionary <string, TableInfo>(); Dictionary <string, Dictionary <string, ColumnInfo> > loc3 = new Dictionary <string, Dictionary <string, ColumnInfo> >(); DataSet ds = this.GetDataSet(string.Format(@" select b.nspname || '.' || a.tablename, a.schemaname, a.tablename , 'T' from pg_tables a inner join pg_namespace b on b.nspname = a.schemaname where a.schemaname not in ('pg_catalog', 'information_schema') union all select b.nspname || '.' || a.relname, b.nspname, a.relname, 'V' from pg_class a inner join pg_namespace b on b.oid = a.relnamespace where b.nspname not in ('pg_catalog', 'information_schema') and a.relkind in ('m','v') ")); if (ds == null) { return(loc1); } List <string> loc6 = new List <string>(); List <string> loc66 = new List <string>(); foreach (DataRow row in ds.Tables[0].Rows) { string table_id = string.Concat(row[0]); string owner = string.Concat(row[1]); string table = string.Concat(row[2]); string type = string.Concat(row[3]); loc2.Add(table_id, new TableInfo(table_id, owner, table, type)); loc3.Add(table_id, new Dictionary <string, ColumnInfo>()); switch (type) { case "V": case "T": loc6.Add(table_id.Replace("'", "''")); break; case "P": loc66.Add(table_id.Replace("'", "''")); break; } } if (loc6.Count == 0) { return(loc1); } string loc8 = "'" + string.Join("','", loc6.ToArray()) + "'"; string loc88 = "'" + string.Join("','", loc66.ToArray()) + "'"; ds = this.GetDataSet(string.Format(@" select ns.nspname || '.' || c.relname as id, a.attname, t.typname, case when a.atttypmod > 0 and a.atttypmod < 32767 then a.atttypmod - 4 else a.attlen end len, case when t.typelem = 0 then t.typname else t2.typname end, case when a.attnotnull then 0 else 1 end as is_nullable, e.adsrc as is_identity, --case when e.adsrc = 'nextval(''' || case when ns.nspname = 'public' then '' else ns.nspname || '.' end || c.relname || '_' || a.attname || '_seq''::regclass)' then 1 else 0 end as is_identity, d.description as comment, a.attndims, case when t.typelem = 0 then t.typtype else t2.typtype end, ns2.nspname, a.attnum from pg_class c inner join pg_attribute a on a.attnum > 0 and a.attrelid = c.oid inner join pg_type t on t.oid = a.atttypid left join pg_type t2 on t2.oid = t.typelem left join pg_description d on d.objoid = a.attrelid and d.objsubid = a.attnum left join pg_attrdef e on e.adrelid = a.attrelid and e.adnum = a.attnum inner join pg_namespace ns on ns.oid = c.relnamespace inner join pg_namespace ns2 on ns2.oid = t.typnamespace where ns.nspname || '.' || c.relname in ({0}) ", loc8)); if (ds == null) { return(loc1); } foreach (DataRow row in ds.Tables[0].Rows) { string table_id = string.Concat(row[0]); string column = string.Concat(row[1]); string type = string.Concat(row[2]); long max_length = long.Parse(string.Concat(row[3])); string sqlType = string.Concat(row[4]); bool is_nullable = string.Concat(row[5]) == "1"; //bool is_identity = string.Concat(row[6]) == "1"; bool is_identity = string.Concat(row[6]).StartsWith(@"nextval('") && string.Concat(row[6]).EndsWith(@"_seq'::regclass)"); string comment = string.Concat(row[7]); if (string.IsNullOrEmpty(comment)) { comment = column; } int attndims = int.Parse(string.Concat(row[8])); string typtype = string.Concat(row[9]); string owner = string.Concat(row[10]); int attnum = int.Parse(string.Concat(row[11])); switch (sqlType) { case "bool": case "name": case "bit": case "varbit": case "bpchar": case "varchar": case "bytea": case "text": case "uuid": break; default: max_length *= 8; break; } if (max_length <= 0) { max_length = -1; } if (type.StartsWith("_")) { type = type.Substring(1); if (attndims == 0) { attndims++; } } if (sqlType.StartsWith("_")) { sqlType = sqlType.Substring(1); } NpgsqlDbType dbtype = CodeBuild.GetDBType(type, typtype); string csType2 = dbtype == NpgsqlDbType.Composite ? UFString((owner == "public" ? sqlType : (owner + "_" + sqlType)) + "TYPE") : dbtype == NpgsqlDbType.Enum ? UFString((owner == "public" ? sqlType : (owner + "_" + sqlType)) + "ENUM") : sqlType; loc3[table_id].Add(column, new ColumnInfo( column, dbtype, max_length, csType2, CodeBuild.GetCSType(dbtype, attndims, csType2), DataSort.ASC, is_nullable, is_identity, false, false, attndims, attnum)); if (!_column_coments.ContainsKey(table_id)) { _column_coments.Add(table_id, new Dictionary <string, string>()); } if (!_column_coments[table_id].ContainsKey(column)) { _column_coments[table_id].Add(column, comment); } else { _column_coments[table_id][column] = comment; } } ds = this.GetDataSet(string.Format(@" select ns.nspname || '.' || d.relname as table_id, c.attname, ns.nspname || '/' || d.relname || '/' || b.relname as index_id, case when a.indisunique then 1 else 0 end IsUnique, case when a.indisprimary then 1 else 0 end IsPrimary, case when a.indisclustered then 0 else 1 end IsClustered, 0 IsDesc, a.indkey, c.attnum from pg_index a inner join pg_class b on b.oid = a.indexrelid inner join pg_attribute c on c.attnum > 0 and c.attrelid = b.oid inner join pg_namespace ns on ns.oid = b.relnamespace inner join pg_class d on d.oid = a.indrelid where ns.nspname || '.' || d.relname in ({0}) ", loc8)); if (ds == null) { return(loc1); } Dictionary <string, Dictionary <string, List <ColumnInfo> > > indexColumns = new Dictionary <string, Dictionary <string, List <ColumnInfo> > >(); Dictionary <string, Dictionary <string, List <ColumnInfo> > > uniqueColumns = new Dictionary <string, Dictionary <string, List <ColumnInfo> > >(); foreach (DataRow row in ds.Tables[0].Rows) { string table_id = string.Concat(row[0]); string column = string.Concat(row[1]); string index_id = string.Concat(row[2]); bool is_unique = string.Concat(row[3]) == "1"; bool is_primary_key = string.Concat(row[4]) == "1"; bool is_clustered = string.Concat(row[5]) == "1"; int is_desc = int.Parse(string.Concat(row[6])); string[] inkey = string.Concat(row[7]).Split(' '); int attnum = int.Parse(string.Concat(row[8])); attnum = int.Parse(inkey[attnum - 1]); foreach (string tc in loc3[table_id].Keys) { if (loc3[table_id][tc].Attnum == attnum) { column = tc; break; } } ColumnInfo loc9 = loc3[table_id][column]; if (loc9.IsClustered == false && is_clustered) { loc9.IsClustered = is_clustered; } if (loc9.IsPrimaryKey == false && is_primary_key) { loc9.IsPrimaryKey = is_primary_key; } if (loc9.Orderby == DataSort.NONE) { loc9.Orderby = (DataSort)is_desc; } Dictionary <string, List <ColumnInfo> > loc10 = null; List <ColumnInfo> loc11 = null; if (!indexColumns.TryGetValue(table_id, out loc10)) { indexColumns.Add(table_id, loc10 = new Dictionary <string, List <ColumnInfo> >()); } if (!loc10.TryGetValue(index_id, out loc11)) { loc10.Add(index_id, loc11 = new List <ColumnInfo>()); } loc11.Add(loc9); if (is_unique) { if (!uniqueColumns.TryGetValue(table_id, out loc10)) { uniqueColumns.Add(table_id, loc10 = new Dictionary <string, List <ColumnInfo> >()); } if (!loc10.TryGetValue(index_id, out loc11)) { loc10.Add(index_id, loc11 = new List <ColumnInfo>()); } loc11.Add(loc9); } } foreach (string table_id in indexColumns.Keys) { foreach (List <ColumnInfo> columns in indexColumns[table_id].Values) { loc2[table_id].Indexes.Add(columns); } } foreach (string table_id in uniqueColumns.Keys) { foreach (List <ColumnInfo> columns in uniqueColumns[table_id].Values) { columns.Sort(delegate(ColumnInfo c1, ColumnInfo c2) { return(c1.Name.CompareTo(c2.Name)); }); loc2[table_id].Uniques.Add(columns); } } ds = this.GetDataSet(string.Format(@" select ns.nspname || '.' || b.relname as table_id, array(select attname from pg_attribute where attrelid = a.conrelid and attnum = any(a.conkey)) as column_name, a.connamespace || '/' || a.conname as FKId, ns2.nspname || '.' || c.relname as ref_table_id, 1 as IsForeignKey, array(select attname from pg_attribute where attrelid = a.confrelid and attnum = any(a.confkey)) as ref_column, null ref_sln, null ref_table from pg_constraint a inner join pg_class b on b.oid = a.conrelid inner join pg_class c on c.oid = a.confrelid inner join pg_namespace ns on ns.oid = b.relnamespace inner join pg_namespace ns2 on ns2.oid = c.relnamespace where ns.nspname || '.' || b.relname in ({0}) ", loc8)); if (ds == null) { return(loc1); } Dictionary <string, Dictionary <string, ForeignKeyInfo> > fkColumns = new Dictionary <string, Dictionary <string, ForeignKeyInfo> >(); foreach (DataRow row in ds.Tables[0].Rows) { string table_id = string.Concat(row[0]); string[] column = row[1] as string[]; string fk_id = string.Concat(row[2]); string ref_table_id = string.Concat(row[3]); bool is_foreign_key = string.Concat(row[4]) == "1"; string[] referenced_column = row[5] as string[]; string referenced_db = string.Concat(row[6]); string referenced_table = string.Concat(row[7]); TableInfo loc10 = null; ColumnInfo loc11 = null; bool isThisSln = !string.IsNullOrEmpty(ref_table_id); if (isThisSln) { if (loc2.ContainsKey(ref_table_id) == false) { continue; } loc10 = loc2[ref_table_id]; loc11 = loc3[ref_table_id][referenced_column[0]]; } else { } Dictionary <string, ForeignKeyInfo> loc12 = null; ForeignKeyInfo loc13 = null; if (!fkColumns.TryGetValue(table_id, out loc12)) { fkColumns.Add(table_id, loc12 = new Dictionary <string, ForeignKeyInfo>()); } if (!loc12.TryGetValue(fk_id, out loc13)) { if (isThisSln) { loc13 = new ForeignKeyInfo(loc2[table_id], loc10); } else { loc13 = new ForeignKeyInfo(referenced_db, referenced_table, is_foreign_key); } loc12.Add(fk_id, loc13); } for (int a = 0; a < column.Length; a++) { loc13.Columns.Add(loc3[table_id][column[a]]); if (isThisSln) { loc13.ReferencedColumns.Add(loc3[ref_table_id][referenced_column[a]]); } else { loc13.ReferencedColumnNames.Add(referenced_column[a]); } } } foreach (string table_id in fkColumns.Keys) { foreach (ForeignKeyInfo fk in fkColumns[table_id].Values) { loc2[table_id].ForeignKeys.Add(fk); } } foreach (string table_id in loc3.Keys) { foreach (ColumnInfo loc5 in loc3[table_id].Values) { loc2[table_id].Columns.Add(loc5); if (loc5.IsIdentity) { loc2[table_id].Identitys.Add(loc5); } if (loc5.IsClustered) { loc2[table_id].Clustereds.Add(loc5); } if (loc5.IsPrimaryKey) { loc2[table_id].PrimaryKeys.Add(loc5); } } } loc1 = _tables = new List <TableInfo>(); foreach (TableInfo loc4 in loc2.Values) { if (loc4.PrimaryKeys.Count == 0 && loc4.Uniques.Count > 0) { foreach (ColumnInfo loc5 in loc4.Uniques[0]) { loc5.IsPrimaryKey = true; loc4.PrimaryKeys.Add(loc5); } } this.Sort(loc4); loc1.Add(loc4); } loc1.Sort(delegate(TableInfo t1, TableInfo t2) { return(t1.FullName.CompareTo(t2.FullName)); }); loc2.Clear(); loc3.Clear(); return(loc1); }
public List <TableInfo> GetTablesByDatabase(string database) { _client.Database = database; Logger.remotor.Info("GetTablesByDatabase: " + _client.Server + "," + _client.Username + "," + _client.Password + "," + _client.Database); string[] dbs = database.Split(','); List <TableInfo> loc1 = _tables = null; Dictionary <string, TableInfo> loc2 = new Dictionary <string, TableInfo>(); Dictionary <string, Dictionary <string, ColumnInfo> > loc3 = new Dictionary <string, Dictionary <string, ColumnInfo> >(); object[][] ds = this.GetDataSet(string.Format(@" select concat(a.table_schema, '.', a.table_name) 'id', a.table_schema 'owner', a.table_name 'table', 'T' from information_schema.tables a where a.table_schema in ('{0}') ", database.Replace("'", "''").Replace(",", "','"))); if (ds == null) { return(loc1); } List <string> loc6 = new List <string>(); List <string> loc66 = new List <string>(); foreach (object[] row in ds) { string table_id = string.Concat(row[0]); string owner = string.Concat(row[1]); string table = string.Concat(row[2]); string type = string.Concat(row[3]); if (dbs.Length == 1) { table_id = table_id.Substring(table_id.IndexOf('.') + 1); owner = ""; } loc2.Add(table_id, new TableInfo(table_id, owner, table, type)); loc3.Add(table_id, new Dictionary <string, ColumnInfo>()); switch (type) { case "T": loc6.Add(table.Replace("'", "''")); break; case "P": loc66.Add(table.Replace("'", "''")); break; } } if (loc6.Count == 0) { return(loc1); } string loc8 = "'" + string.Join("','", loc6.ToArray()) + "'"; string loc88 = "'" + string.Join("','", loc66.ToArray()) + "'"; ds = this.GetDataSet(string.Format(@" SELECT concat(a.table_schema, '.', a.table_name), a.column_name, a.data_type, ifnull(a.character_maximum_length, 0) 'len', a.column_type, case when a.is_nullable then 1 else 0 end 'is_nullable', case when locate('auto_increment', a.extra) > 0 then 1 else 0 end 'is_identity', a.column_comment 'comment' from information_schema.columns a where a.table_schema in ('{1}') and a.table_name in ({0}) ", loc8, database.Replace("'", "''").Replace(",", "','"))); if (ds == null) { return(loc1); } foreach (object[] row in ds) { string table_id = string.Concat(row[0]); string column = string.Concat(row[1]); string type = string.Concat(row[2]); //long max_length = long.Parse(string.Concat(row[3])); string sqlType = string.Concat(row[4]); Match m_len = Regex.Match(sqlType, @"\w+\((\d+)"); long max_length = m_len.Success ? int.Parse(m_len.Groups[1].Value) : -1; bool is_nullable = string.Concat(row[5]) == "1"; bool is_identity = string.Concat(row[6]) == "1"; string comment = string.Concat(row[7]); if (string.IsNullOrEmpty(comment)) { comment = column; } if (max_length == 0) { max_length = -1; } if (dbs.Length == 1) { table_id = table_id.Substring(table_id.IndexOf('.') + 1); } loc3[table_id].Add(column, new ColumnInfo( column, CodeBuild.GetDBType(type, sqlType.EndsWith(" unsigned")), max_length, sqlType, DataSort.ASC, is_nullable, is_identity, false, false)); if (!_column_coments.ContainsKey(table_id)) { _column_coments.Add(table_id, new Dictionary <string, string>()); } if (!_column_coments[table_id].ContainsKey(column)) { _column_coments[table_id].Add(column, comment); } else { _column_coments[table_id][column] = comment; } } ds = this.GetDataSet(string.Format(@" select concat(a.constraint_schema, '.', a.table_name) 'table_id', a.column_name, concat(a.constraint_schema, '/', a.table_name, '/', a.constraint_name) 'index_id', 1 'IsUnique', case when constraint_name = 'PRIMARY' then 1 else 0 end 'IsPrimaryKey', 0 'IsClustered', 0 'IsDesc' from information_schema.key_column_usage a where a.constraint_schema in ('{1}') and a.table_name in ({0}) and isnull(position_in_unique_constraint) ", loc8, database.Replace("'", "''").Replace(",", "','"))); if (ds == null) { return(loc1); } Dictionary <string, Dictionary <string, List <ColumnInfo> > > indexColumns = new Dictionary <string, Dictionary <string, List <ColumnInfo> > >(); Dictionary <string, Dictionary <string, List <ColumnInfo> > > uniqueColumns = new Dictionary <string, Dictionary <string, List <ColumnInfo> > >(); foreach (object[] row in ds) { string table_id = string.Concat(row[0]); string column = string.Concat(row[1]); string index_id = string.Concat(row[2]); bool is_unique = string.Concat(row[3]) == "1"; bool is_primary_key = string.Concat(row[4]) == "1"; bool is_clustered = string.Concat(row[5]) == "1"; int is_desc = int.Parse(string.Concat(row[6])); if (dbs.Length == 1) { table_id = table_id.Substring(table_id.IndexOf('.') + 1); } if (loc3.ContainsKey(table_id) == false || loc3[table_id].ContainsKey(column) == false) { continue; } ColumnInfo loc9 = loc3[table_id][column]; if (loc9.IsClustered == false && is_clustered) { loc9.IsClustered = is_clustered; } if (loc9.IsPrimaryKey == false && is_primary_key) { loc9.IsPrimaryKey = is_primary_key; } if (loc9.Orderby == DataSort.NONE) { loc9.Orderby = (DataSort)is_desc; } Dictionary <string, List <ColumnInfo> > loc10 = null; List <ColumnInfo> loc11 = null; if (!indexColumns.TryGetValue(table_id, out loc10)) { indexColumns.Add(table_id, loc10 = new Dictionary <string, List <ColumnInfo> >()); } if (!loc10.TryGetValue(index_id, out loc11)) { loc10.Add(index_id, loc11 = new List <ColumnInfo>()); } loc11.Add(loc9); if (is_unique) { if (!uniqueColumns.TryGetValue(table_id, out loc10)) { uniqueColumns.Add(table_id, loc10 = new Dictionary <string, List <ColumnInfo> >()); } if (!loc10.TryGetValue(index_id, out loc11)) { loc10.Add(index_id, loc11 = new List <ColumnInfo>()); } loc11.Add(loc9); } } foreach (string table_id in indexColumns.Keys) { foreach (List <ColumnInfo> columns in indexColumns[table_id].Values) { loc2[table_id].Indexes.Add(columns); } } foreach (string table_id in uniqueColumns.Keys) { foreach (List <ColumnInfo> columns in uniqueColumns[table_id].Values) { columns.Sort(delegate(ColumnInfo c1, ColumnInfo c2) { return(c1.Name.CompareTo(c2.Name)); }); loc2[table_id].Uniques.Add(columns); } } ds = this.GetDataSet(string.Format(@" select concat(a.constraint_schema, '.', a.table_name) 'table_id', a.column_name, concat(a.constraint_schema, '/', a.constraint_name) 'FKId', concat(a.referenced_table_schema, '.', a.referenced_table_name) 'ref_table_id', 1 'IsForeignKey', a.referenced_column_name 'ref_column', null 'ref_sln', null 'ref_table' from information_schema.key_column_usage a where a.constraint_schema in ('{1}') and a.table_name in ({0}) and not isnull(position_in_unique_constraint) ", loc8, database.Replace("'", "''").Replace(",", "','"))); if (ds == null) { return(loc1); } Dictionary <string, Dictionary <string, ForeignKeyInfo> > fkColumns = new Dictionary <string, Dictionary <string, ForeignKeyInfo> >(); foreach (object[] row in ds) { string table_id = string.Concat(row[0]); string column = string.Concat(row[1]); string fk_id = string.Concat(row[2]); string ref_table_id = string.Concat(row[3]); bool is_foreign_key = string.Concat(row[4]) == "1"; string referenced_column = string.Concat(row[5]); string referenced_db = string.Concat(row[6]); string referenced_table = string.Concat(row[7]); if (dbs.Length == 1) { table_id = table_id.Substring(table_id.IndexOf('.') + 1); ref_table_id = ref_table_id.Substring(ref_table_id.IndexOf('.') + 1); } if (loc3.ContainsKey(table_id) == false || loc3[table_id].ContainsKey(column) == false) { continue; } ColumnInfo loc9 = loc3[table_id][column]; TableInfo loc10 = null; ColumnInfo loc11 = null; bool isThisSln = !string.IsNullOrEmpty(ref_table_id); if (isThisSln) { if (loc2.ContainsKey(ref_table_id) == false) { continue; } loc10 = loc2[ref_table_id]; loc11 = loc3[ref_table_id][referenced_column]; } else { } Dictionary <string, ForeignKeyInfo> loc12 = null; ForeignKeyInfo loc13 = null; if (!fkColumns.TryGetValue(table_id, out loc12)) { fkColumns.Add(table_id, loc12 = new Dictionary <string, ForeignKeyInfo>()); } if (!loc12.TryGetValue(fk_id, out loc13)) { if (isThisSln) { loc13 = new ForeignKeyInfo(loc2[table_id], loc10); } else { loc13 = new ForeignKeyInfo(referenced_db, referenced_table, is_foreign_key); } loc12.Add(fk_id, loc13); } loc13.Columns.Add(loc9); if (isThisSln) { loc13.ReferencedColumns.Add(loc11); } else { loc13.ReferencedColumnNames.Add(referenced_column); } } foreach (string table_id in fkColumns.Keys) { foreach (ForeignKeyInfo fk in fkColumns[table_id].Values) { loc2[table_id].ForeignKeys.Add(fk); } } foreach (string table_id in loc3.Keys) { foreach (ColumnInfo loc5 in loc3[table_id].Values) { loc2[table_id].Columns.Add(loc5); if (loc5.IsIdentity) { loc2[table_id].Identitys.Add(loc5); } if (loc5.IsClustered) { loc2[table_id].Clustereds.Add(loc5); } if (loc5.IsPrimaryKey) { loc2[table_id].PrimaryKeys.Add(loc5); } } } loc1 = _tables = new List <TableInfo>(); foreach (TableInfo loc4 in loc2.Values) { if (loc4.PrimaryKeys.Count == 0 && loc4.Uniques.Count > 0) { foreach (ColumnInfo loc5 in loc4.Uniques[0]) { loc5.IsPrimaryKey = true; loc4.PrimaryKeys.Add(loc5); } } this.Sort(loc4); loc1.Add(loc4); } loc1.Sort(delegate(TableInfo t1, TableInfo t2) { return(t1.FullName.CompareTo(t2.FullName)); }); loc2.Clear(); loc3.Clear(); return(loc1); }