public List <DbTableInfo> GetTablesByDatabase(params string[] database) { var olddatabase = ""; using (var conn = _orm.Ado.MasterPool.Get(TimeSpan.FromSeconds(5))) { olddatabase = conn.Value.Database; } var dbs = database == null || database.Any() == false ? new[] { olddatabase } : database; var tables = new List <DbTableInfo>(); foreach (var db in dbs) { if (string.IsNullOrEmpty(db)) { continue; } var loc1 = new List <DbTableInfo>(); var loc2 = new Dictionary <int, DbTableInfo>(); var loc3 = new Dictionary <int, Dictionary <string, DbColumnInfo> >(); var sql = $@" use [{db}]; select a.Object_id ,b.name 'Owner' ,a.name 'Name' ,(select value from sys.extended_properties where major_id = a.object_id AND minor_id = 0 AND name = 'MS_Description') 'Comment' ,'TABLE' type from sys.tables a inner join sys.schemas b on b.schema_id = a.schema_id where not(b.name = 'dbo' and a.name = 'sysdiagrams') union all select a.Object_id ,b.name 'Owner' ,a.name 'Name' ,(select value from sys.extended_properties where major_id = a.object_id AND minor_id = 0 AND name = 'MS_Description') 'Comment' ,'VIEW' type from sys.views a inner join sys.schemas b on b.schema_id = a.schema_id union all select a.Object_id ,b.name 'Owner' ,a.name 'Name' ,(select value from sys.extended_properties where major_id = a.object_id AND minor_id = 0 AND name = 'MS_Description') 'Comment' ,'StoreProcedure' type from sys.procedures a inner join sys.schemas b on b.schema_id = a.schema_id where a.type = 'P' and charindex('diagram', a.name) = 0 order by type desc, b.name, a.name ; use [{olddatabase}]; "; var ds = _orm.Ado.ExecuteArray(CommandType.Text, sql); if (ds == null) { return(loc1); } var loc6 = new List <int[]>(); var loc66 = new List <int[]>(); var loc6_1000 = new List <int>(); var loc66_1000 = new List <int>(); foreach (object[] row in ds) { int object_id = int.Parse(string.Concat(row[0])); var owner = string.Concat(row[1]); var table = string.Concat(row[2]); var comment = string.Concat(row[3]); Enum.TryParse <DbTableType>(string.Concat(row[4]), out var type); loc2.Add(object_id, new DbTableInfo { Id = object_id.ToString(), Schema = owner, Name = table, Comment = comment, Type = type }); loc3.Add(object_id, new Dictionary <string, DbColumnInfo>()); switch (type) { case DbTableType.VIEW: case DbTableType.TABLE: loc6_1000.Add(object_id); if (loc6_1000.Count >= 500) { loc6.Add(loc6_1000.ToArray()); loc6_1000.Clear(); } break; case DbTableType.StoreProcedure: loc66_1000.Add(object_id); if (loc66_1000.Count >= 500) { loc66.Add(loc66_1000.ToArray()); loc66_1000.Clear(); } break; } } if (loc6_1000.Count > 0) { loc6.Add(loc6_1000.ToArray()); } if (loc66_1000.Count > 0) { loc66.Add(loc66_1000.ToArray()); } if (loc6.Count == 0) { return(loc1); } Func <List <int[]>, StringBuilder> getloc8Sb = loclist => { if (loclist.Count == 0) { return(new StringBuilder()); } var loc8sb = new StringBuilder().Append("("); for (var loc8sbidx = 0; loc8sbidx < loclist.Count; loc8sbidx++) { if (loc8sbidx > 0) { loc8sb.Append(" OR "); } loc8sb.Append("a.table_name in ("); for (var loc8sbidx2 = 0; loc8sbidx2 < loclist[loc8sbidx].Length; loc8sbidx2++) { if (loc8sbidx2 > 0) { loc8sb.Append(","); } loc8sb.Append(loclist[loc8sbidx][loc8sbidx2]); } loc8sb.Append(")"); } loc8sb.Append(")"); return(loc8sb); }; var loc8 = getloc8Sb(loc6); var loc88 = getloc8Sb(loc66); var tsql_place = @" select isnull(e.name,'') + '.' + isnull(d.name,'') ,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' ,( select value from sys.extended_properties where major_id = a.object_id AND minor_id = a.column_id AND name = 'MS_Description') 'Comment' {0} a inner join sys.types b on b.user_type_id = a.user_type_id left join sys.tables d on d.object_id = a.object_id left join sys.schemas e on e.schema_id = d.schema_id{2} where {1} "; sql = string.Format(tsql_place, @" ,a.is_nullable 'IsNullable' ,a.is_identity 'IsIdentity' ,f.text as 'DefaultValue' from sys.columns", loc8.ToString().Replace("a.table_name", "a.object_id"), @" left join syscomments f on f.id = a.default_object_id "); if (loc88.Length > 0) { sql += "union all" + string.Format(tsql_place.Replace( " select value from sys.extended_properties where major_id = a.object_id AND minor_id = a.column_id", " select value from sys.extended_properties where major_id = a.object_id AND minor_id = a.parameter_id"), @" ,cast(0 as bit) 'IsNullable' ,a.is_output 'IsIdentity' ,'' as 'DefaultValue' from sys.parameters", loc88.ToString().Replace("a.table_name", "a.object_id"), ""); } sql = $"use [{db}];{sql};use [{olddatabase}]; "; ds = _orm.Ado.ExecuteArray(CommandType.Text, sql); if (ds == null) { return(loc1); } foreach (object[] row in ds) { var table_id = string.Concat(row[0]); var object_id = int.Parse(string.Concat(row[1])); var column = string.Concat(row[2]); var type = string.Concat(row[3]); var max_length = int.Parse(string.Concat(row[4])); var sqlType = string.Concat(row[5]); var comment = string.Concat(row[6]); var is_nullable = bool.Parse(string.Concat(row[7])); var is_identity = bool.Parse(string.Concat(row[8])); var defaultValue = string.Concat(row[9]); if (max_length == 0) { max_length = -1; } loc3[object_id].Add(column, new DbColumnInfo { Name = column, MaxLength = max_length, IsIdentity = is_identity, IsNullable = is_nullable, IsPrimary = false, DbTypeText = type, DbTypeTextFull = sqlType, Table = loc2[object_id], Coment = comment, DefaultValue = defaultValue }); loc3[object_id][column].DbType = this.GetDbType(loc3[object_id][column]); loc3[object_id][column].CsType = this.GetCsTypeInfo(loc3[object_id][column]); } sql = $@" use [{db}]; select a.object_id 'Object_id' ,c.name 'Column' ,b.name '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 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 {loc8.ToString().Replace("a.table_name", "a.object_id")} ; use [{olddatabase}]; "; ds = _orm.Ado.ExecuteArray(CommandType.Text, sql); if (ds == null) { return(loc1); } var indexColumns = new Dictionary <int, Dictionary <string, DbIndexInfo> >(); var uniqueColumns = new Dictionary <int, Dictionary <string, DbIndexInfo> >(); foreach (object[] row in ds) { int object_id = int.Parse(string.Concat(row[0])); string column = string.Concat(row[1]); string index_id = 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])); bool is_desc = string.Concat(row[6]) == "1"; if (loc3.ContainsKey(object_id) == false || loc3[object_id].ContainsKey(column) == false) { continue; } DbColumnInfo loc9 = loc3[object_id][column]; if (loc9.IsPrimary == false && is_primary_key) { loc9.IsPrimary = is_primary_key; } Dictionary <string, DbIndexInfo> loc10 = null; DbIndexInfo loc11 = null; if (!indexColumns.TryGetValue(object_id, out loc10)) { indexColumns.Add(object_id, loc10 = new Dictionary <string, DbIndexInfo>()); } if (!loc10.TryGetValue(index_id, out loc11)) { loc10.Add(index_id, loc11 = new DbIndexInfo()); } loc11.Columns.Add(new DbIndexColumnInfo { Column = loc9, IsDesc = is_desc }); if (is_unique && !is_primary_key) { if (!uniqueColumns.TryGetValue(object_id, out loc10)) { uniqueColumns.Add(object_id, loc10 = new Dictionary <string, DbIndexInfo>()); } if (!loc10.TryGetValue(index_id, out loc11)) { loc10.Add(index_id, loc11 = new DbIndexInfo()); } loc11.Columns.Add(new DbIndexColumnInfo { Column = loc9, IsDesc = is_desc }); } } foreach (var object_id in indexColumns.Keys) { foreach (var column in indexColumns[object_id]) { loc2[object_id].IndexesDict.Add(column.Key, column.Value); } } foreach (var object_id in uniqueColumns.Keys) { foreach (var column in uniqueColumns[object_id]) { column.Value.Columns.Sort((c1, c2) => c1.Column.Name.CompareTo(c2.Column.Name)); loc2[object_id].UniquesDict.Add(column.Key, column.Value); } } sql = $@" use [{db}]; select b.object_id 'Object_id' ,c.name 'Column' ,e.name 'FKId' ,a.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 left join sys.foreign_keys e on e.object_id = a.constraint_object_id where {loc8.ToString().Replace("a.table_name", "b.object_id")} ; use [{olddatabase}]; "; ds = _orm.Ado.ExecuteArray(CommandType.Text, sql); if (ds == null) { return(loc1); } var fkColumns = new Dictionary <int, Dictionary <string, DbForeignInfo> >(); foreach (object[] row in ds) { int object_id, referenced_object_id; int.TryParse(string.Concat(row[0]), out object_id); var column = string.Concat(row[1]); string fk_id = string.Concat(row[2]); int.TryParse(string.Concat(row[3]), out referenced_object_id); var is_foreign_key = bool.Parse(string.Concat(row[4])); var referenced_column = string.Concat(row[5]); var referenced_db = string.Concat(row[6]); var referenced_table = string.Concat(row[7]); DbColumnInfo loc9 = loc3[object_id][column]; DbTableInfo loc10 = null; DbColumnInfo loc11 = null; bool isThisSln = referenced_object_id != 0; if (isThisSln) { loc10 = loc2[referenced_object_id]; loc11 = loc3[referenced_object_id][referenced_column]; } else { } Dictionary <string, DbForeignInfo> loc12 = null; DbForeignInfo loc13 = null; if (!fkColumns.TryGetValue(object_id, out loc12)) { fkColumns.Add(object_id, loc12 = new Dictionary <string, DbForeignInfo>()); } if (!loc12.TryGetValue(fk_id, out loc13)) { loc12.Add(fk_id, loc13 = new DbForeignInfo { Table = loc2[object_id], ReferencedTable = loc10 }); } loc13.Columns.Add(loc9); loc13.ReferencedColumns.Add(loc11); } foreach (var table_id in fkColumns.Keys) { foreach (var fk in fkColumns[table_id]) { loc2[table_id].ForeignsDict.Add(fk.Key, fk.Value); } } foreach (var table_id in loc3.Keys) { foreach (var loc5 in loc3[table_id].Values) { loc2[table_id].Columns.Add(loc5); if (loc5.IsIdentity) { loc2[table_id].Identitys.Add(loc5); } if (loc5.IsPrimary) { loc2[table_id].Primarys.Add(loc5); } } } foreach (var loc4 in loc2.Values) { //if (loc4.Primarys.Count == 0 && loc4.UniquesDict.Count > 0) //{ // foreach (var loc5 in loc4.UniquesDict.First().Value.Columns) // { // loc5.Column.IsPrimary = true; // loc4.Primarys.Add(loc5.Column); // } //} loc4.Primarys.Sort((c1, c2) => c1.Name.CompareTo(c2.Name)); loc4.Columns.Sort((c1, c2) => { int compare = c2.IsPrimary.CompareTo(c1.IsPrimary); if (compare == 0) { bool b1 = loc4.ForeignsDict.Values.Where(fk => fk.Columns.Where(c3 => c3.Name == c1.Name).Any()).Any(); bool b2 = loc4.ForeignsDict.Values.Where(fk => fk.Columns.Where(c3 => c3.Name == c2.Name).Any()).Any(); compare = b2.CompareTo(b1); } if (compare == 0) { compare = c1.Name.CompareTo(c2.Name); } return(compare); }); loc1.Add(loc4); } loc1.Sort((t1, t2) => { var ret = t1.Schema.CompareTo(t2.Schema); if (ret == 0) { ret = t1.Name.CompareTo(t2.Name); } return(ret); }); loc2.Clear(); loc3.Clear(); tables.AddRange(loc1); } return(tables); }
public List <DbTableInfo> GetTables(string[] database, string tablename, bool ignoreCase) { var loc1 = new List <DbTableInfo>(); var loc2 = new Dictionary <string, DbTableInfo>(); var loc3 = new Dictionary <string, Dictionary <string, DbColumnInfo> >(); string[] tbname = null; if (string.IsNullOrEmpty(tablename) == false) { tbname = _commonUtils.SplitTableName(tablename); if (tbname?.Length == 1) { var userUsers = _orm.Ado.ExecuteScalar(" select username from user_users")?.ToString(); if (string.IsNullOrEmpty(userUsers)) { return(loc1); } tbname = new[] { userUsers, tbname[0] }; } if (ignoreCase) { tbname = tbname.Select(a => a.ToLower()).ToArray(); } database = new[] { tbname[0] }; } else if (database == null || database.Any() == false) { var userUsers = _orm.Ado.ExecuteScalar(" select username from user_users")?.ToString(); if (string.IsNullOrEmpty(userUsers)) { return(loc1); } database = new[] { userUsers }; } var databaseIn = string.Join(",", database.Select(a => _commonUtils.FormatSql("{0}", a))); var sql = $@" select a.owner || '.' || a.table_name AS tbname, a.owner, a.table_name, b.comments, 'TABLE' AS tp from all_tables a left join all_tab_comments b on b.owner = a.owner and b.table_name = a.table_name and b.table_type = 'TABLE' where {(ignoreCase ? "lower(a.owner)" : "a.owner")} in ({databaseIn}){(tbname == null ? "" : $" and {(ignoreCase ? "lower(a.table_name)" : "a.table_name")}={_commonUtils.FormatSql("{0}", tbname[1])}")} UNION ALL select a.owner || '.' || a.view_name, a.owner, a.view_name, b.comments, 'VIEW' AS tp from all_views a left join all_tab_comments b on b.owner = a.owner and b.table_name = a.view_name and b.table_type = 'VIEW' where {(ignoreCase ? "lower(a.owner)" : "a.owner")} in ({databaseIn}){(tbname == null ? "" : $" and {(ignoreCase ? "lower(a.view_name)" : "a.view_name")}={_commonUtils.FormatSql("{0}", tbname[1])}")} "; var ds = _orm.Ado.ExecuteArray(CommandType.Text, sql); if (ds == null) { return(loc1); } var loc6 = new List <string[]>(); var loc66 = new List <string[]>(); var loc6_1000 = new List <string>(); var loc66_1000 = new List <string>(); foreach (var row in ds) { var table_id = string.Concat(row[0]); var schema = string.Concat(row[1]); var table = string.Concat(row[2]); var comment = string.Concat(row[3]); var type = string.Concat(row[4]) == "VIEW" ? DbTableType.VIEW : DbTableType.TABLE; if (database.Length == 1) { table_id = table_id.Substring(table_id.IndexOf('.') + 1); schema = ""; } loc2.Add(table_id, new DbTableInfo { Id = table_id, Schema = schema, Name = table, Comment = comment, Type = type }); loc3.Add(table_id, new Dictionary <string, DbColumnInfo>()); switch (type) { case DbTableType.TABLE: case DbTableType.VIEW: loc6_1000.Add(table.Replace("'", "''")); if (loc6_1000.Count >= 999) { loc6.Add(loc6_1000.ToArray()); loc6_1000.Clear(); } break; case DbTableType.StoreProcedure: loc66_1000.Add(table.Replace("'", "''")); if (loc66_1000.Count >= 999) { loc66.Add(loc66_1000.ToArray()); loc66_1000.Clear(); } break; } } if (loc6_1000.Count > 0) { loc6.Add(loc6_1000.ToArray()); } if (loc66_1000.Count > 0) { loc66.Add(loc66_1000.ToArray()); } if (loc6.Count == 0) { return(loc1); } var loc8 = new StringBuilder().Append("("); for (var loc8idx = 0; loc8idx < loc6.Count; loc8idx++) { if (loc8idx > 0) { loc8.Append(" OR "); } loc8.Append("a.table_name in ("); for (var loc8idx2 = 0; loc8idx2 < loc6[loc8idx].Length; loc8idx2++) { if (loc8idx2 > 0) { loc8.Append(","); } loc8.Append($"'{loc6[loc8idx][loc8idx2]}'"); } loc8.Append(")"); } loc8.Append(")"); _orm.Ado.ExecuteNonQuery(CommandType.Text, @" CREATE OR REPLACE FUNCTION FREESQL_LONG_TO_CHAR_DEFAULT ( TABLE_NAME VARCHAR, COLUMN VARCHAR2 ) RETURN VARCHAR AS TEXT_C1 VARCHAR2(32767); SQL_CUR VARCHAR2(2000); BEGIN DBMS_OUTPUT.ENABLE(BUFFER_SIZE => NULL); SQL_CUR := 'SELECT T.DATA_DEFAULT FROM USER_TAB_COLUMNS T WHERE T.TABLE_NAME = '''||TABLE_NAME||''' AND T.COLUMN_NAME='''||COLUMN||''''; DBMS_OUTPUT.PUT_LINE(SQL_CUR); EXECUTE IMMEDIATE SQL_CUR INTO TEXT_C1; TEXT_C1 := SUBSTR(TEXT_C1, 1, 4000); RETURN TEXT_C1; END;"); sql = $@" select a.owner || '.' || a.table_name, a.column_name, a.data_type, a.data_length, a.data_precision, a.data_scale, a.char_used, case when a.nullable = 'N' then 0 else 1 end, nvl((select 1 from user_sequences where upper(sequence_name)=upper(a.table_name||'_seq_'||a.column_name) and rownum < 2), 0), to_char(b.comments), nvl(FREESQL_LONG_TO_CHAR_DEFAULT(a.table_name, a.column_name),'') from all_tab_cols a left join all_col_comments b on b.owner = a.owner and b.table_name = a.table_name and b.column_name = a.column_name where {(ignoreCase ? "lower(a.owner)" : "a.owner")} in ({databaseIn}) and {loc8} and a.column_id is not null "; ds = _orm.Ado.ExecuteArray(CommandType.Text, sql); if (ds == null) { return(loc1); } var ds2 = new List <object[]>(); foreach (var row in ds) { var ds2item = new object[9]; ds2item[0] = row[0]; ds2item[1] = row[1]; ds2item[2] = Regex.Replace(string.Concat(row[2]), @"\(\d+\)", ""); ds2item[4] = OdbcOracleCodeFirst.GetOracleSqlTypeFullName(new object[] { row[1], row[2], row[3], row[4], row[5], row[6] }); ds2item[5] = string.Concat(row[7]); ds2item[6] = string.Concat(row[8]); ds2item[7] = string.Concat(row[9]); ds2item[8] = string.Concat(row[10]); ds2.Add(ds2item); } var position = 0; foreach (var row in ds2) { 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]); var m_len = Regex.Match(sqlType, @"\w+\((\d+)"); int 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]); string defaultValue = string.Concat(row[8]); if (max_length == 0) { max_length = -1; } if (database.Length == 1) { table_id = table_id.Substring(table_id.IndexOf('.') + 1); } loc3[table_id].Add(column, new DbColumnInfo { Name = column, MaxLength = max_length, IsIdentity = is_identity, IsNullable = is_nullable, IsPrimary = false, DbTypeText = type, DbTypeTextFull = sqlType, Table = loc2[table_id], Comment = comment, DefaultValue = defaultValue, Position = ++position }); loc3[table_id][column].DbType = this.GetDbType(loc3[table_id][column]); loc3[table_id][column].CsType = this.GetCsTypeInfo(loc3[table_id][column]); } OdbcOracleCodeFirst.CreateOracleFunction(_orm); sql = $@" select a.table_owner || '.' || a.table_name, nvl(freesql_long_2_varchar(a.index_name, c.table_name, c.column_position), c.column_name), c.index_name, case when a.uniqueness = 'UNIQUE' then 1 else 0 end, case when exists(select 1 from all_constraints where constraint_name = a.index_name and constraint_type = 'P') then 1 else 0 end, 0, case when c.descend = 'DESC' then 1 else 0 end, c.column_position from all_indexes a, all_ind_columns c where a.index_name = c.index_name and a.table_owner = c.table_owner and a.table_name = c.table_name and {(ignoreCase ? "lower(a.table_owner)" : "a.table_owner")} in ({databaseIn}) and {loc8} "; ds = _orm.Ado.ExecuteArray(CommandType.Text, sql); if (ds == null) { return(loc1); } var indexColumns = new Dictionary <string, Dictionary <string, DbIndexInfo> >(); var uniqueColumns = new Dictionary <string, Dictionary <string, DbIndexInfo> >(); foreach (var row in ds) { string table_id = string.Concat(row[0]); string column = string.Concat(row[1]).Trim('"'); 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"; bool is_desc = string.Concat(row[6]) == "1"; if (database.Length == 1) { table_id = table_id.Substring(table_id.IndexOf('.') + 1); } if (loc3.ContainsKey(table_id) == false || loc3[table_id].ContainsKey(column) == false) { continue; } var loc9 = loc3[table_id][column]; if (loc9.IsPrimary == false && is_primary_key) { loc9.IsPrimary = is_primary_key; } if (is_primary_key) { continue; } Dictionary <string, DbIndexInfo> loc10 = null; DbIndexInfo loc11 = null; if (!indexColumns.TryGetValue(table_id, out loc10)) { indexColumns.Add(table_id, loc10 = new Dictionary <string, DbIndexInfo>()); } if (!loc10.TryGetValue(index_id, out loc11)) { loc10.Add(index_id, loc11 = new DbIndexInfo()); } loc11.Columns.Add(new DbIndexColumnInfo { Column = loc9, IsDesc = is_desc }); if (is_unique && !is_primary_key) { if (!uniqueColumns.TryGetValue(table_id, out loc10)) { uniqueColumns.Add(table_id, loc10 = new Dictionary <string, DbIndexInfo>()); } if (!loc10.TryGetValue(index_id, out loc11)) { loc10.Add(index_id, loc11 = new DbIndexInfo()); } loc11.Columns.Add(new DbIndexColumnInfo { Column = loc9, IsDesc = is_desc }); } } foreach (string table_id in indexColumns.Keys) { foreach (var column in indexColumns[table_id]) { loc2[table_id].IndexesDict.Add(column.Key, column.Value); } } foreach (string table_id in uniqueColumns.Keys) { foreach (var column in uniqueColumns[table_id]) { column.Value.Columns.Sort((c1, c2) => c1.Column.Name.CompareTo(c2.Column.Name)); loc2[table_id].UniquesDict.Add(column.Key, column.Value); } } if (tbname == null) { sql = $@" select a.owner || '.' || a.table_name, c.column_name, c.constraint_name, b.owner || '.' || b.table_name, 1, d.column_name -- a.owner 外键拥有者, -- a.table_name 外键表, -- c.column_name 外键列, -- b.owner 主键拥有者, -- b.table_name 主键表, -- d.column_name 主键列, -- c.constraint_name 外键名, -- d.constraint_name 主键名 from all_constraints a, all_constraints b, all_cons_columns c, --外键表 all_cons_columns d --主键表 where a.r_constraint_name = b.constraint_name and a.constraint_type = 'R' and b.constraint_type = 'P' and a.r_owner = b.owner and a.constraint_name = c.constraint_name and b.constraint_name = d.constraint_name and a.owner = c.owner and a.table_name = c.table_name and b.owner = d.owner and b.table_name = d.table_name and {(ignoreCase ? "lower(a.owner)" : "a.owner")} in ({databaseIn}) and {loc8} "; ds = _orm.Ado.ExecuteArray(CommandType.Text, sql); if (ds == null) { return(loc1); } var fkColumns = new Dictionary <string, Dictionary <string, DbForeignInfo> >(); foreach (var 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]); if (database.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; } var loc9 = loc3[table_id][column]; if (loc2.ContainsKey(ref_table_id) == false) { continue; } var loc10 = loc2[ref_table_id]; var loc11 = loc3[ref_table_id][referenced_column]; Dictionary <string, DbForeignInfo> loc12 = null; DbForeignInfo loc13 = null; if (!fkColumns.TryGetValue(table_id, out loc12)) { fkColumns.Add(table_id, loc12 = new Dictionary <string, DbForeignInfo>()); } if (!loc12.TryGetValue(fk_id, out loc13)) { loc12.Add(fk_id, loc13 = new DbForeignInfo { Table = loc2[table_id], ReferencedTable = loc10 }); } loc13.Columns.Add(loc9); loc13.ReferencedColumns.Add(loc11); } foreach (var table_id in fkColumns.Keys) { foreach (var fk in fkColumns[table_id]) { loc2[table_id].ForeignsDict.Add(fk.Key, fk.Value); } } } foreach (var table_id in loc3.Keys) { foreach (var loc5 in loc3[table_id].Values) { loc2[table_id].Columns.Add(loc5); if (loc5.IsIdentity) { loc2[table_id].Identitys.Add(loc5); } if (loc5.IsPrimary) { loc2[table_id].Primarys.Add(loc5); } } } foreach (var loc4 in loc2.Values) { //if (loc4.Primarys.Count == 0 && loc4.UniquesDict.Count > 0) //{ // foreach (var loc5 in loc4.UniquesDict.First().Value.Columns) // { // loc5.Column.IsPrimary = true; // loc4.Primarys.Add(loc5.Column); // } //} loc4.Primarys.Sort((c1, c2) => c1.Name.CompareTo(c2.Name)); loc4.Columns.Sort((c1, c2) => { int compare = c2.IsPrimary.CompareTo(c1.IsPrimary); if (compare == 0) { bool b1 = loc4.ForeignsDict.Values.Where(fk => fk.Columns.Where(c3 => c3.Name == c1.Name).Any()).Any(); bool b2 = loc4.ForeignsDict.Values.Where(fk => fk.Columns.Where(c3 => c3.Name == c2.Name).Any()).Any(); compare = b2.CompareTo(b1); } if (compare == 0) { compare = c1.Name.CompareTo(c2.Name); } return(compare); }); loc1.Add(loc4); } loc1.Sort((t1, t2) => { var ret = t1.Schema.CompareTo(t2.Schema); if (ret == 0) { ret = t1.Name.CompareTo(t2.Name); } return(ret); }); loc2.Clear(); loc3.Clear(); return(loc1); }
public List <DbTableInfo> GetTablesByDatabase(params string[] database2) { var loc1 = new List <DbTableInfo>(); var loc2 = new Dictionary <string, DbTableInfo>(); var loc3 = new Dictionary <string, Dictionary <string, DbColumnInfo> >(); var database = database2?.ToArray(); if (database == null || database.Any() == false) { var userUsers = _orm.Ado.ExecuteScalar(" select username from user_users")?.ToString(); if (string.IsNullOrEmpty(userUsers)) { return(loc1); } database = new[] { userUsers }; } var databaseIn = string.Join(",", database.Select(a => _commonUtils.FormatSql("{0}", a))); var sql = string.Format(@" select a.owner || '.' || a.table_name, a.owner, a.table_name, b.comments, 'TABLE' from all_tables a left join all_tab_comments b on b.owner = a.owner and b.table_name = a.table_name and b.table_type = 'TABLE' where a.owner in ({0})", databaseIn); var ds = _orm.Ado.ExecuteArray(CommandType.Text, sql); if (ds == null) { return(loc1); } var loc6 = new List <string[]>(); var loc66 = new List <string[]>(); var loc6_1000 = new List <string>(); var loc66_1000 = new List <string>(); foreach (var row in ds) { var table_id = string.Concat(row[0]); var schema = string.Concat(row[1]); var table = string.Concat(row[2]); var comment = string.Concat(row[3]); var type = string.Concat(row[4]) == "VIEW" ? DbTableType.VIEW : DbTableType.TABLE; if (database.Length == 1) { table_id = table_id.Substring(table_id.IndexOf('.') + 1); schema = ""; } loc2.Add(table_id, new DbTableInfo { Id = table_id, Schema = schema, Name = table, Comment = comment, Type = type }); loc3.Add(table_id, new Dictionary <string, DbColumnInfo>()); switch (type) { case DbTableType.TABLE: case DbTableType.VIEW: loc6_1000.Add(table.Replace("'", "''")); if (loc6_1000.Count >= 999) { loc6.Add(loc6_1000.ToArray()); loc6_1000.Clear(); } break; case DbTableType.StoreProcedure: loc66_1000.Add(table.Replace("'", "''")); if (loc66_1000.Count >= 999) { loc66.Add(loc66_1000.ToArray()); loc66_1000.Clear(); } break; } } if (loc6_1000.Count > 0) { loc6.Add(loc6_1000.ToArray()); } if (loc66_1000.Count > 0) { loc66.Add(loc66_1000.ToArray()); } if (loc6.Count == 0) { return(loc1); } var loc8 = new StringBuilder().Append("("); for (var loc8idx = 0; loc8idx < loc6.Count; loc8idx++) { if (loc8idx > 0) { loc8.Append(" OR "); } loc8.Append("a.table_name in ("); for (var loc8idx2 = 0; loc8idx2 < loc6[loc8idx].Length; loc8idx2++) { if (loc8idx2 > 0) { loc8.Append(","); } loc8.Append($"'{loc6[loc8idx][loc8idx2]}'"); } loc8.Append(")"); } loc8.Append(")"); sql = string.Format(@" select a.owner || '.' || a.table_name, a.column_name, a.data_type, a.data_length, a.data_precision, a.data_scale, a.char_used, case when a.nullable = 'N' then 0 else 1 end, nvl((select 1 from user_sequences where upper(sequence_name)=upper(a.table_name||'_seq_'||a.column_name) and rownum < 2), 0), b.comments, a.data_default from all_tab_cols a left join all_col_comments b on b.owner = a.owner and b.table_name = a.table_name and b.column_name = a.column_name where a.owner in ({1}) and {0} ", loc8, databaseIn); ds = _orm.Ado.ExecuteArray(CommandType.Text, sql); if (ds == null) { return(loc1); } var ds2 = new List <object[]>(); foreach (var row in ds) { var ds2item = new object[9]; ds2item[0] = row[0]; ds2item[1] = row[1]; ds2item[2] = Regex.Replace(string.Concat(row[2]), @"\(\d+\)", ""); ds2item[4] = DamengCodeFirst.GetDamengSqlTypeFullName(new object[] { row[1], row[2], row[3], row[4], row[5], row[6] }); ds2item[5] = string.Concat(row[7]) == "1"; ds2item[6] = string.Concat(row[8]) == "1"; ds2item[7] = string.Concat(row[9]); ds2item[8] = string.Concat(row[10]); ds2.Add(ds2item); } foreach (var row in ds2) { 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]); var m_len = Regex.Match(sqlType, @"\w+\((\d+)"); int 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]); string defaultValue = string.Concat(row[8]); if (max_length == 0) { max_length = -1; } if (database.Length == 1) { table_id = table_id.Substring(table_id.IndexOf('.') + 1); } loc3[table_id].Add(column, new DbColumnInfo { Name = column, MaxLength = max_length, IsIdentity = is_identity, IsNullable = is_nullable, IsPrimary = false, DbTypeText = type, DbTypeTextFull = sqlType, Table = loc2[table_id], Coment = comment, DefaultValue = defaultValue }); loc3[table_id][column].DbType = this.GetDbType(loc3[table_id][column]); loc3[table_id][column].CsType = this.GetCsTypeInfo(loc3[table_id][column]); } sql = string.Format(@" select a.table_owner || '.' || a.table_name, c.column_name, c.index_name, case when a.uniqueness = 'UNIQUE' then 1 else 0 end, case when exists(select 1 from all_constraints where constraint_name = a.index_name and constraint_type = 'P') then 1 else 0 end, 0, case when c.descend = 'DESC' then 1 else 0 end, c.column_position from all_indexes a, all_ind_columns c where a.index_name = c.index_name and a.table_owner = c.table_owner and a.table_name = c.table_name and a.table_owner in ({1}) and {0} ", loc8, databaseIn); ds = _orm.Ado.ExecuteArray(CommandType.Text, sql); if (ds == null) { return(loc1); } var indexColumns = new Dictionary <string, Dictionary <string, DbIndexInfo> >(); var uniqueColumns = new Dictionary <string, Dictionary <string, DbIndexInfo> >(); foreach (var row in ds) { string table_id = string.Concat(row[0]); string column = string.Concat(row[1]).Trim('"'); 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"; bool is_desc = string.Concat(row[6]) == "1"; if (database.Length == 1) { table_id = table_id.Substring(table_id.IndexOf('.') + 1); } if (loc3.ContainsKey(table_id) == false || loc3[table_id].ContainsKey(column) == false) { continue; } var loc9 = loc3[table_id][column]; if (loc9.IsPrimary == false && is_primary_key) { loc9.IsPrimary = is_primary_key; } if (is_primary_key) { continue; } Dictionary <string, DbIndexInfo> loc10 = null; DbIndexInfo loc11 = null; if (!indexColumns.TryGetValue(table_id, out loc10)) { indexColumns.Add(table_id, loc10 = new Dictionary <string, DbIndexInfo>()); } if (!loc10.TryGetValue(index_id, out loc11)) { loc10.Add(index_id, loc11 = new DbIndexInfo()); } loc11.Columns.Add(new DbIndexColumnInfo { Column = loc9, IsDesc = is_desc }); if (is_unique && !is_primary_key) { if (!uniqueColumns.TryGetValue(table_id, out loc10)) { uniqueColumns.Add(table_id, loc10 = new Dictionary <string, DbIndexInfo>()); } if (!loc10.TryGetValue(index_id, out loc11)) { loc10.Add(index_id, loc11 = new DbIndexInfo()); } loc11.Columns.Add(new DbIndexColumnInfo { Column = loc9, IsDesc = is_desc }); } } foreach (string table_id in indexColumns.Keys) { foreach (var column in indexColumns[table_id]) { loc2[table_id].IndexesDict.Add(column.Key, column.Value); } } foreach (string table_id in uniqueColumns.Keys) { foreach (var column in uniqueColumns[table_id]) { column.Value.Columns.Sort((c1, c2) => c1.Column.Name.CompareTo(c2.Column.Name)); loc2[table_id].UniquesDict.Add(column.Key, column.Value); } } sql = string.Format(@" select a.owner || '.' || a.table_name, c.column_name, c.constraint_name, b.owner || '.' || b.table_name, 1, d.column_name -- a.owner 外键拥有者, -- a.table_name 外键表, -- c.column_name 外键列, -- b.owner 主键拥有者, -- b.table_name 主键表, -- d.column_name 主键列, -- c.constraint_name 外键名, -- d.constraint_name 主键名 from all_constraints a, all_constraints b, all_cons_columns c, --外键表 all_cons_columns d --主键表 where a.r_constraint_name = b.constraint_name and a.constraint_type = 'R' and b.constraint_type = 'P' and a.r_owner = b.owner and a.constraint_name = c.constraint_name and b.constraint_name = d.constraint_name and a.owner = c.owner and a.table_name = c.table_name and b.owner = d.owner and b.table_name = d.table_name and a.owner in ({1}) and {0} ", loc8, databaseIn); ds = _orm.Ado.ExecuteArray(CommandType.Text, sql); if (ds == null) { return(loc1); } var fkColumns = new Dictionary <string, Dictionary <string, DbForeignInfo> >(); foreach (var 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]); if (database.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; } var loc9 = loc3[table_id][column]; if (loc2.ContainsKey(ref_table_id) == false) { continue; } var loc10 = loc2[ref_table_id]; var loc11 = loc3[ref_table_id][referenced_column]; Dictionary <string, DbForeignInfo> loc12 = null; DbForeignInfo loc13 = null; if (!fkColumns.TryGetValue(table_id, out loc12)) { fkColumns.Add(table_id, loc12 = new Dictionary <string, DbForeignInfo>()); } if (!loc12.TryGetValue(fk_id, out loc13)) { loc12.Add(fk_id, loc13 = new DbForeignInfo { Table = loc2[table_id], ReferencedTable = loc10 }); } loc13.Columns.Add(loc9); loc13.ReferencedColumns.Add(loc11); } foreach (var table_id in fkColumns.Keys) { foreach (var fk in fkColumns[table_id]) { loc2[table_id].ForeignsDict.Add(fk.Key, fk.Value); } } foreach (var table_id in loc3.Keys) { foreach (var loc5 in loc3[table_id].Values) { loc2[table_id].Columns.Add(loc5); if (loc5.IsIdentity) { loc2[table_id].Identitys.Add(loc5); } if (loc5.IsPrimary) { loc2[table_id].Primarys.Add(loc5); } } } foreach (var loc4 in loc2.Values) { //if (loc4.Primarys.Count == 0 && loc4.UniquesDict.Count > 0) //{ // foreach (var loc5 in loc4.UniquesDict.First().Value.Columns) // { // loc5.Column.IsPrimary = true; // loc4.Primarys.Add(loc5.Column); // } //} loc4.Primarys.Sort((c1, c2) => c1.Name.CompareTo(c2.Name)); loc4.Columns.Sort((c1, c2) => { int compare = c2.IsPrimary.CompareTo(c1.IsPrimary); if (compare == 0) { bool b1 = loc4.ForeignsDict.Values.Where(fk => fk.Columns.Where(c3 => c3.Name == c1.Name).Any()).Any(); bool b2 = loc4.ForeignsDict.Values.Where(fk => fk.Columns.Where(c3 => c3.Name == c2.Name).Any()).Any(); compare = b2.CompareTo(b1); } if (compare == 0) { compare = c1.Name.CompareTo(c2.Name); } return(compare); }); loc1.Add(loc4); } loc1.Sort((t1, t2) => { var ret = t1.Schema.CompareTo(t2.Schema); if (ret == 0) { ret = t1.Name.CompareTo(t2.Name); } return(ret); }); loc2.Clear(); loc3.Clear(); return(loc1); }
public List <DbTableInfo> GetTablesByDatabase(params string[] database) { var olddatabase = ""; var is96 = true; using (var conn = _orm.Ado.MasterPool.Get(TimeSpan.FromSeconds(5))) { olddatabase = conn.Value.Database; is96 = PgVersionIs96(conn.Value.ServerVersion); } var dbs = database == null || database.Any() == false ? new[] { olddatabase } : database; var tables = new List <DbTableInfo>(); foreach (var db in dbs) { if (string.IsNullOrEmpty(db) || string.Compare(db, olddatabase, true) != 0) { continue; } var loc1 = new List <DbTableInfo>(); var loc2 = new Dictionary <string, DbTableInfo>(); var loc3 = new Dictionary <string, Dictionary <string, DbColumnInfo> >(); var sql = $@" select b.nspname || '.' || a.tablename, a.schemaname, a.tablename , d.description, 'TABLE' from pg_tables a inner join pg_namespace b on b.nspname = a.schemaname inner join pg_class c on c.relnamespace = b.oid and c.relname = a.tablename left join pg_description d on d.objoid = c.oid and objsubid = 0 where a.schemaname not in ('pg_catalog', 'information_schema', 'topology') and b.nspname || '.' || a.tablename not in ('public.spatial_ref_sys') union all select b.nspname || '.' || a.relname, b.nspname, a.relname, d.description, 'VIEW' from pg_class a inner join pg_namespace b on b.oid = a.relnamespace left join pg_description d on d.objoid = a.oid and objsubid = 0 where b.nspname not in ('pg_catalog', 'information_schema') and a.relkind in ('m','v') and b.nspname || '.' || a.relname not in ('public.geography_columns','public.geometry_columns','public.raster_columns','public.raster_overviews') "; var ds = _orm.Ado.ExecuteArray(CommandType.Text, sql); if (ds == null) { return(loc1); } var loc6 = new List <string[]>(); var loc66 = new List <string[]>(); var loc6_1000 = new List <string>(); var loc66_1000 = new List <string>(); foreach (object[] row in ds) { var object_id = string.Concat(row[0]); var owner = string.Concat(row[1]); var table = string.Concat(row[2]); var comment = string.Concat(row[3]); Enum.TryParse <DbTableType>(string.Concat(row[4]), out var type); loc2.Add(object_id, new DbTableInfo { Id = object_id.ToString(), Schema = owner, Name = table, Comment = comment, Type = type }); loc3.Add(object_id, new Dictionary <string, DbColumnInfo>()); switch (type) { case DbTableType.VIEW: case DbTableType.TABLE: loc6_1000.Add(object_id); if (loc6_1000.Count >= 500) { loc6.Add(loc6_1000.ToArray()); loc6_1000.Clear(); } break; case DbTableType.StoreProcedure: loc66_1000.Add(object_id); if (loc66_1000.Count >= 500) { loc66.Add(loc66_1000.ToArray()); loc66_1000.Clear(); } break; } } if (loc6_1000.Count > 0) { loc6.Add(loc6_1000.ToArray()); } if (loc66_1000.Count > 0) { loc66.Add(loc66_1000.ToArray()); } if (loc6.Count == 0) { return(loc1); } var loc8 = new StringBuilder().Append("("); for (var loc8idx = 0; loc8idx < loc6.Count; loc8idx++) { if (loc8idx > 0) { loc8.Append(" OR "); } loc8.Append("a.table_name in ("); for (var loc8idx2 = 0; loc8idx2 < loc6[loc8idx].Length; loc8idx2++) { if (loc8idx2 > 0) { loc8.Append(","); } loc8.Append($"'{loc6[loc8idx][loc8idx2]}'"); } loc8.Append(")"); } loc8.Append(")"); sql = $@" 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, pg12以下 (select pg_get_expr(adbin, adrelid) from pg_attrdef where adrelid = e.adrelid limit 1) 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 {loc8.ToString().Replace("a.table_name", "ns.nspname || '.' || c.relname")}"; ds = _orm.Ado.ExecuteArray(CommandType.Text, sql); if (ds == null) { return(loc1); } var position = 0; foreach (object[] row in ds) { var object_id = string.Concat(row[0]); var column = string.Concat(row[1]); var type = string.Concat(row[2]); var max_length = int.Parse(string.Concat(row[3])); var sqlType = string.Concat(row[4]); var is_nullable = string.Concat(row[5]) == "1"; var is_identity = string.Concat(row[6]).StartsWith(@"nextval('") && string.Concat(row[6]).EndsWith(@"'::regclass)"); var comment = string.Concat(row[7]); var defaultValue = string.Concat(row[6]); 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.ToLower()) { 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); } if (max_length > 0) { switch (sqlType.ToLower()) { //case "numeric": sqlType += $"({max_length})"; break; case "bpchar": case "varchar": case "bytea": case "bit": case "varbit": sqlType += $"({max_length})"; break; } } loc3[object_id].Add(column, new DbColumnInfo { Name = column, MaxLength = max_length, IsIdentity = is_identity, IsNullable = is_nullable, IsPrimary = false, DbTypeText = type, DbTypeTextFull = sqlType, Table = loc2[object_id], Coment = comment, DefaultValue = defaultValue, Position = ++position }); loc3[object_id][column].DbType = this.GetDbType(loc3[object_id][column]); loc3[object_id][column].CsType = this.GetCsTypeInfo(loc3[object_id][column]); } sql = $@" select ns.nspname || '.' || d.relname as table_id, c.attname, 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, {(is96 ? "case when pg_index_column_has_property(b.oid, c.attnum, 'desc') = 't' then 1 else 0 end" : "0")} IsDesc, a.indkey::text, 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 {loc8.ToString().Replace("a.table_name", "ns.nspname || '.' || d.relname")} "; ds = _orm.Ado.ExecuteArray(CommandType.Text, sql); if (ds == null) { return(loc1); } var indexColumns = new Dictionary <string, Dictionary <string, DbIndexInfo> >(); var uniqueColumns = new Dictionary <string, Dictionary <string, DbIndexInfo> >(); foreach (object[] row in ds) { var object_id = string.Concat(row[0]); var column = string.Concat(row[1]); var index_id = string.Concat(row[2]); var is_unique = string.Concat(row[3]) == "1"; var is_primary_key = string.Concat(row[4]) == "1"; var is_clustered = string.Concat(row[5]) == "1"; var is_desc = string.Concat(row[6]) == "1"; var inkey = string.Concat(row[7]).Split(' '); var attnum = int.Parse(string.Concat(row[8])); attnum = int.Parse(inkey[attnum - 1]); foreach (string tc in loc3[object_id].Keys) { if (loc3[object_id][tc].DbTypeText.EndsWith("[]")) { column = tc; break; } } if (loc3.ContainsKey(object_id) == false || loc3[object_id].ContainsKey(column) == false) { continue; } var loc9 = loc3[object_id][column]; if (loc9.IsPrimary == false && is_primary_key) { loc9.IsPrimary = is_primary_key; } Dictionary <string, DbIndexInfo> loc10 = null; DbIndexInfo loc11 = null; if (!indexColumns.TryGetValue(object_id, out loc10)) { indexColumns.Add(object_id, loc10 = new Dictionary <string, DbIndexInfo>()); } if (!loc10.TryGetValue(index_id, out loc11)) { loc10.Add(index_id, loc11 = new DbIndexInfo()); } loc11.Columns.Add(new DbIndexColumnInfo { Column = loc9, IsDesc = is_desc }); if (is_unique && !is_primary_key) { if (!uniqueColumns.TryGetValue(object_id, out loc10)) { uniqueColumns.Add(object_id, loc10 = new Dictionary <string, DbIndexInfo>()); } if (!loc10.TryGetValue(index_id, out loc11)) { loc10.Add(index_id, loc11 = new DbIndexInfo()); } loc11.Columns.Add(new DbIndexColumnInfo { Column = loc9, IsDesc = is_desc }); } } foreach (var object_id in indexColumns.Keys) { foreach (var column in indexColumns[object_id]) { loc2[object_id].IndexesDict.Add(column.Key, column.Value); } } foreach (var object_id in uniqueColumns.Keys) { foreach (var column in uniqueColumns[object_id]) { column.Value.Columns.Sort((c1, c2) => c1.Column.Name.CompareTo(c2.Column.Name)); loc2[object_id].UniquesDict.Add(column.Key, column.Value); } } sql = $@" 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.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 {loc8.ToString().Replace("a.table_name", "ns.nspname || '.' || b.relname")} "; ds = _orm.Ado.ExecuteArray(CommandType.Text, sql); if (ds == null) { return(loc1); } var fkColumns = new Dictionary <string, Dictionary <string, DbForeignInfo> >(); foreach (object[] row in ds) { var table_id = string.Concat(row[0]); var column = row[1] as string[]; var fk_id = string.Concat(row[2]); var ref_table_id = string.Concat(row[3]); var is_foreign_key = string.Concat(row[4]) == "1"; var referenced_column = row[5] as string[]; var referenced_db = string.Concat(row[6]); var referenced_table = string.Concat(row[7]); if (loc2.ContainsKey(ref_table_id) == false) { continue; } Dictionary <string, DbForeignInfo> loc12 = null; DbForeignInfo loc13 = null; if (!fkColumns.TryGetValue(table_id, out loc12)) { fkColumns.Add(table_id, loc12 = new Dictionary <string, DbForeignInfo>()); } if (!loc12.TryGetValue(fk_id, out loc13)) { loc12.Add(fk_id, loc13 = new DbForeignInfo { Table = loc2[table_id], ReferencedTable = loc2[ref_table_id] }); } for (int a = 0; a < column.Length; a++) { loc13.Columns.Add(loc3[table_id][column[a]]); loc13.ReferencedColumns.Add(loc3[ref_table_id][referenced_column[a]]); } } foreach (var table_id in fkColumns.Keys) { foreach (var fk in fkColumns[table_id]) { loc2[table_id].ForeignsDict.Add(fk.Key, fk.Value); } } foreach (var table_id in loc3.Keys) { foreach (var loc5 in loc3[table_id].Values) { loc2[table_id].Columns.Add(loc5); if (loc5.IsIdentity) { loc2[table_id].Identitys.Add(loc5); } if (loc5.IsPrimary) { loc2[table_id].Primarys.Add(loc5); } } } foreach (var loc4 in loc2.Values) { //if (loc4.Primarys.Count == 0 && loc4.UniquesDict.Count > 0) //{ // foreach (var loc5 in loc4.UniquesDict.First().Value.Columns) // { // loc5.Column.IsPrimary = true; // loc4.Primarys.Add(loc5.Column); // } //} loc4.Primarys.Sort((c1, c2) => c1.Name.CompareTo(c2.Name)); loc4.Columns.Sort((c1, c2) => { int compare = c2.IsPrimary.CompareTo(c1.IsPrimary); if (compare == 0) { bool b1 = loc4.ForeignsDict.Values.Where(fk => fk.Columns.Where(c3 => c3.Name == c1.Name).Any()).Any(); bool b2 = loc4.ForeignsDict.Values.Where(fk => fk.Columns.Where(c3 => c3.Name == c2.Name).Any()).Any(); compare = b2.CompareTo(b1); } if (compare == 0) { compare = c1.Name.CompareTo(c2.Name); } return(compare); }); loc1.Add(loc4); } loc1.Sort((t1, t2) => { var ret = t1.Schema.CompareTo(t2.Schema); if (ret == 0) { ret = t1.Name.CompareTo(t2.Name); } return(ret); }); loc2.Clear(); loc3.Clear(); tables.AddRange(loc1); } return(tables); }
public List <DbTableInfo> GetTablesByDatabase(params string[] database2) { var loc1 = new List <DbTableInfo>(); var loc2 = new Dictionary <string, DbTableInfo>(); var loc3 = new Dictionary <string, Dictionary <string, DbColumnInfo> >(); var database = database2?.ToArray(); if (database == null || database.Any() == false) { using (var conn = _orm.Ado.MasterPool.Get()) { if (string.IsNullOrEmpty(conn.Value.Database)) { return(loc1); } database = new[] { conn.Value.Database }; } } var databaseIn = string.Join(",", database.Select(a => _commonUtils.FormatSql("{0}", a))); var sql = string.Format(@" select concat(a.table_schema, '.', a.table_name) 'id', a.table_schema 'schema', a.table_name 'table', a.table_comment, a.table_type 'type' from information_schema.tables a where a.table_schema in ({0})", databaseIn); var ds = _orm.Ado.ExecuteArray(CommandType.Text, sql); if (ds == null) { return(loc1); } var loc6 = new List <string[]>(); var loc66 = new List <string[]>(); var loc6_1000 = new List <string>(); var loc66_1000 = new List <string>(); foreach (var row in ds) { var table_id = string.Concat(row[0]); var schema = string.Concat(row[1]); var table = string.Concat(row[2]); var comment = string.Concat(row[3]); var type = string.Concat(row[4]) == "VIEW" ? DbTableType.VIEW : DbTableType.TABLE; if (database.Length == 1) { table_id = table_id.Substring(table_id.IndexOf('.') + 1); schema = ""; } loc2.Add(table_id, new DbTableInfo { Id = table_id, Schema = schema, Name = table, Comment = comment, Type = type }); loc3.Add(table_id, new Dictionary <string, DbColumnInfo>()); switch (type) { case DbTableType.TABLE: case DbTableType.VIEW: loc6_1000.Add(table.Replace("'", "''")); if (loc6_1000.Count >= 500) { loc6.Add(loc6_1000.ToArray()); loc6_1000.Clear(); } break; case DbTableType.StoreProcedure: loc66_1000.Add(table.Replace("'", "''")); if (loc66_1000.Count >= 500) { loc66.Add(loc66_1000.ToArray()); loc66_1000.Clear(); } break; } } if (loc6_1000.Count > 0) { loc6.Add(loc6_1000.ToArray()); } if (loc66_1000.Count > 0) { loc66.Add(loc66_1000.ToArray()); } if (loc6.Count == 0) { return(loc1); } var loc8 = new StringBuilder().Append("("); for (var loc8idx = 0; loc8idx < loc6.Count; loc8idx++) { if (loc8idx > 0) { loc8.Append(" OR "); } loc8.Append("a.table_name in ("); for (var loc8idx2 = 0; loc8idx2 < loc6[loc8idx].Length; loc8idx2++) { if (loc8idx2 > 0) { loc8.Append(","); } loc8.Append($"'{loc6[loc8idx][loc8idx2]}'"); } loc8.Append(")"); } loc8.Append(")"); sql = 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 = 'YES' 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', a.column_default 'default_value' from information_schema.columns a where a.table_schema in ({1}) and {0} ", loc8, databaseIn); ds = _orm.Ado.ExecuteArray(CommandType.Text, sql); if (ds == null) { return(loc1); } var position = 0; foreach (var 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]); var m_len = Regex.Match(sqlType, @"\w+\((\d+)"); int 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]); string defaultValue = string.Concat(row[8]); if (max_length == 0) { max_length = -1; } if (database.Length == 1) { table_id = table_id.Substring(table_id.IndexOf('.') + 1); } loc3[table_id].Add(column, new DbColumnInfo { Name = column, MaxLength = max_length, IsIdentity = is_identity, IsNullable = is_nullable, IsPrimary = false, DbTypeText = type, DbTypeTextFull = sqlType, Table = loc2[table_id], Coment = comment, DefaultValue = defaultValue, Position = ++position }); loc3[table_id][column].DbType = this.GetDbType(loc3[table_id][column]); loc3[table_id][column].CsType = this.GetCsTypeInfo(loc3[table_id][column]); } sql = string.Format(@" select concat(a.table_schema, '.', a.table_name) 'table_id', a.column_name, a.index_name 'index_id', case when a.non_unique = 0 then 1 else 0 end 'IsUnique', case when a.index_name = 'PRIMARY' then 1 else 0 end 'IsPrimaryKey', 0 'IsClustered', 0 'IsDesc' from information_schema.statistics a where a.table_schema in ({1}) and {0} ", loc8, databaseIn); ds = _orm.Ado.ExecuteArray(CommandType.Text, sql); if (ds == null) { return(loc1); } var indexColumns = new Dictionary <string, Dictionary <string, DbIndexInfo> >(); var uniqueColumns = new Dictionary <string, Dictionary <string, DbIndexInfo> >(); foreach (var 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"; bool is_desc = string.Concat(row[6]) == "1"; if (database.Length == 1) { table_id = table_id.Substring(table_id.IndexOf('.') + 1); } if (loc3.ContainsKey(table_id) == false || loc3[table_id].ContainsKey(column) == false) { continue; } var loc9 = loc3[table_id][column]; if (loc9.IsPrimary == false && is_primary_key) { loc9.IsPrimary = is_primary_key; } Dictionary <string, DbIndexInfo> loc10 = null; DbIndexInfo loc11 = null; if (!indexColumns.TryGetValue(table_id, out loc10)) { indexColumns.Add(table_id, loc10 = new Dictionary <string, DbIndexInfo>()); } if (!loc10.TryGetValue(index_id, out loc11)) { loc10.Add(index_id, loc11 = new DbIndexInfo()); } loc11.Columns.Add(new DbIndexColumnInfo { Column = loc9, IsDesc = is_desc }); if (is_unique && !is_primary_key) { if (!uniqueColumns.TryGetValue(table_id, out loc10)) { uniqueColumns.Add(table_id, loc10 = new Dictionary <string, DbIndexInfo>()); } if (!loc10.TryGetValue(index_id, out loc11)) { loc10.Add(index_id, loc11 = new DbIndexInfo()); } loc11.Columns.Add(new DbIndexColumnInfo { Column = loc9, IsDesc = is_desc }); } } foreach (string table_id in indexColumns.Keys) { foreach (var column in indexColumns[table_id]) { loc2[table_id].IndexesDict.Add(column.Key, column.Value); } } foreach (string table_id in uniqueColumns.Keys) { foreach (var column in uniqueColumns[table_id]) { column.Value.Columns.Sort((c1, c2) => c1.Column.Name.CompareTo(c2.Column.Name)); loc2[table_id].UniquesDict.Add(column.Key, column.Value); } } sql = string.Format(@" select concat(a.constraint_schema, '.', a.table_name) 'table_id', a.column_name, a.constraint_name 'FKId', concat(a.referenced_table_schema, '.', a.referenced_table_name) 'ref_table_id', 1 'IsForeignKey', a.referenced_column_name 'ref_column' from information_schema.key_column_usage a where a.constraint_schema in ({1}) and {0} and not isnull(position_in_unique_constraint) ", loc8, databaseIn); ds = _orm.Ado.ExecuteArray(CommandType.Text, sql); if (ds == null) { return(loc1); } var fkColumns = new Dictionary <string, Dictionary <string, DbForeignInfo> >(); foreach (var 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]); if (database.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; } var loc9 = loc3[table_id][column]; if (loc2.ContainsKey(ref_table_id) == false) { continue; } var loc10 = loc2[ref_table_id]; var loc11 = loc3[ref_table_id][referenced_column]; Dictionary <string, DbForeignInfo> loc12 = null; DbForeignInfo loc13 = null; if (!fkColumns.TryGetValue(table_id, out loc12)) { fkColumns.Add(table_id, loc12 = new Dictionary <string, DbForeignInfo>()); } if (!loc12.TryGetValue(fk_id, out loc13)) { loc12.Add(fk_id, loc13 = new DbForeignInfo { Table = loc2[table_id], ReferencedTable = loc10 }); } loc13.Columns.Add(loc9); loc13.ReferencedColumns.Add(loc11); } foreach (var table_id in fkColumns.Keys) { foreach (var fk in fkColumns[table_id]) { loc2[table_id].ForeignsDict.Add(fk.Key, fk.Value); } } foreach (var table_id in loc3.Keys) { foreach (var loc5 in loc3[table_id].Values) { loc2[table_id].Columns.Add(loc5); if (loc5.IsIdentity) { loc2[table_id].Identitys.Add(loc5); } if (loc5.IsPrimary) { loc2[table_id].Primarys.Add(loc5); } } } foreach (var loc4 in loc2.Values) { //if (loc4.Primarys.Count == 0 && loc4.UniquesDict.Count > 0) //{ // foreach (var loc5 in loc4.UniquesDict.First().Value.Columns) // { // loc5.Column.IsPrimary = true; // loc4.Primarys.Add(loc5.Column); // } //} loc4.Primarys.Sort((c1, c2) => c1.Name.CompareTo(c2.Name)); loc4.Columns.Sort((c1, c2) => { int compare = c2.IsPrimary.CompareTo(c1.IsPrimary); if (compare == 0) { bool b1 = loc4.ForeignsDict.Values.Where(fk => fk.Columns.Where(c3 => c3.Name == c1.Name).Any()).Any(); bool b2 = loc4.ForeignsDict.Values.Where(fk => fk.Columns.Where(c3 => c3.Name == c2.Name).Any()).Any(); compare = b2.CompareTo(b1); } if (compare == 0) { compare = c1.Name.CompareTo(c2.Name); } return(compare); }); loc1.Add(loc4); } loc1.Sort((t1, t2) => { var ret = t1.Schema.CompareTo(t2.Schema); if (ret == 0) { ret = t1.Name.CompareTo(t2.Name); } return(ret); }); loc2.Clear(); loc3.Clear(); return(loc1); }
public List <DbTableInfo> GetTables(string[] database, string tablename, bool ignoreCase) { var loc1 = new List <DbTableInfo>(); var loc2 = new Dictionary <string, DbTableInfo>(); var loc3 = new Dictionary <string, Dictionary <string, DbColumnInfo> >(); string[] tbname = null; if (string.IsNullOrEmpty(tablename) == false) { tbname = _commonUtils.SplitTableName(tablename); if (tbname?.Length == 1) { tbname = new[] { "main", tbname[0] } } ; if (ignoreCase) { tbname = tbname.Select(a => a.ToLower()).ToArray(); } database = new[] { tbname[0] }; } else if (database == null || database.Any() == false) { database = GetDatabases().ToArray(); } if (database.Any() == false) { return(loc1); } Action <object[], int> addColumn = (row, position) => { 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]); var m_len = Regex.Match(sqlType, @"\w+\((\d+)"); int 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"; bool is_primary = string.Concat(row[7]) == "1"; string comment = string.Concat(row[8]); string defaultValue = string.Concat(row[9]); if (max_length == 0) { max_length = -1; } loc3[table_id].Add(column, new DbColumnInfo { Name = column, MaxLength = max_length, IsIdentity = is_identity, IsNullable = is_nullable, IsPrimary = is_primary, DbTypeText = type, DbTypeTextFull = sqlType, Table = loc2[table_id], Coment = comment, DefaultValue = defaultValue, Position = position }); loc3[table_id][column].DbType = this.GetDbType(loc3[table_id][column]); loc3[table_id][column].CsType = this.GetCsTypeInfo(loc3[table_id][column]); }; foreach (var db in database) { var sql = $@" select '{db}.' || tbl_name, '{db}', tbl_name, '' Comment, 'TABLE', sql from {db}.sqlite_master where type='table'{(tbname == null ? "" : $" and {(ignoreCase ? "lower(tbl_name)" : "tbl_name")}={_commonUtils.FormatSql("{0}", tbname[1])}")}"; var ds = _orm.Ado.ExecuteArray(CommandType.Text, sql); if (ds == null) { continue; } var loc6 = new List <string[]>(); var loc66 = new List <string[]>(); var loc6_1000 = new List <string>(); var loc66_1000 = new List <string>(); foreach (var row in ds) { var table_id = string.Concat(row[0]); var schema = string.Concat(row[1]); var table = string.Concat(row[2]); var comment = string.Concat(row[3]); var type = string.Concat(row[4]) == "VIEW" ? DbTableType.VIEW : DbTableType.TABLE; if (database.Length == 1) { table_id = table_id.Substring(table_id.IndexOf('.') + 1); schema = ""; } loc2.Add(table_id, new DbTableInfo { Id = table_id, Schema = schema, Name = table, Comment = comment, Type = type }); loc3.Add(table_id, new Dictionary <string, DbColumnInfo>()); switch (type) { case DbTableType.TABLE: case DbTableType.VIEW: loc6_1000.Add(table.Replace("'", "''")); if (loc6_1000.Count >= 999) { loc6.Add(loc6_1000.ToArray()); loc6_1000.Clear(); } break; case DbTableType.StoreProcedure: loc66_1000.Add(table.Replace("'", "''")); if (loc66_1000.Count >= 999) { loc66.Add(loc66_1000.ToArray()); loc66_1000.Clear(); } break; } if (type == DbTableType.TABLE && table != "sqlite_sequence") { var dsql = string.Concat(row[5]); var cols = _orm.Ado.ExecuteArray(CommandType.Text, $"PRAGMA \"{db}\".table_info(\"{table}\")"); var position = 0; foreach (var col in cols) { var col_name = string.Concat(col[1]); var is_identity = false; var dsqlIdx = dsql?.IndexOf($"\"{col_name}\" "); if (dsqlIdx > 0) { var dsqlLastIdx = dsql.IndexOf('\n', dsqlIdx.Value); if (dsqlLastIdx > 0) { is_identity = dsql.Substring(dsqlIdx.Value, dsqlLastIdx - dsqlIdx.Value).Contains("AUTOINCREMENT"); } } var ds2item = new object[10]; ds2item[0] = table_id; ds2item[1] = col_name; ds2item[2] = Regex.Replace(string.Concat(col[2]), @"\(\d+(\b*,\b*\d+)?\)", "").ToUpper(); ds2item[4] = string.Concat(col[2]).ToUpper(); ds2item[5] = string.Concat(col[5]) == "0" && string.Concat(col[3]) == "0" ? 1 : 0; ds2item[6] = is_identity; ds2item[7] = string.Concat(col[5]) == "1" ? 1 : 0; ds2item[8] = ""; ds2item[9] = string.Concat(col[4]); addColumn(ds2item, ++position); } if (tbname == null) { var fks = _orm.Ado.ExecuteArray(CommandType.Text, $"PRAGMA \"{db}\".foreign_key_list(\"{table}\")"); if (fks != null && fks.Length > 0) { var fkColumns = new Dictionary <string, Dictionary <string, DbForeignInfo> >(); foreach (var fk in fks) { string column = string.Concat(fk[3]); string fk_id = $"{db}.{table}.{fk[0]}"; string ref_table_id = database.Length == 1 ? string.Concat(fk[2]) : $"{db}.{fk[2]}"; string referenced_column = string.Concat(fk[4]); if (loc3.ContainsKey(table_id) == false || loc3[table_id].ContainsKey(column) == false) { continue; } var loc9 = loc3[table_id][column]; if (loc2.ContainsKey(ref_table_id) == false) { continue; } var loc10 = loc2[ref_table_id]; var loc11 = loc3[ref_table_id][referenced_column]; Dictionary <string, DbForeignInfo> loc12 = null; DbForeignInfo loc13 = null; if (!fkColumns.TryGetValue(table_id, out loc12)) { fkColumns.Add(table_id, loc12 = new Dictionary <string, DbForeignInfo>()); } if (!loc12.TryGetValue(fk_id, out loc13)) { loc12.Add(fk_id, loc13 = new DbForeignInfo { Table = loc2[table_id], ReferencedTable = loc10 }); } loc13.Columns.Add(loc9); loc13.ReferencedColumns.Add(loc11); } foreach (var table_id2 in fkColumns.Keys) { foreach (var fk in fkColumns[table_id2]) { loc2[table_id2].ForeignsDict.Add(fk.Key, fk.Value); } } } } } } if (loc6_1000.Count > 0) { loc6.Add(loc6_1000.ToArray()); } if (loc66_1000.Count > 0) { loc66.Add(loc66_1000.ToArray()); } if (loc6.Count == 0) { continue; } } foreach (var table_id in loc3.Keys) { foreach (var loc5 in loc3[table_id].Values) { loc2[table_id].Columns.Add(loc5); if (loc5.IsIdentity) { loc2[table_id].Identitys.Add(loc5); } if (loc5.IsPrimary) { loc2[table_id].Primarys.Add(loc5); } } } foreach (var loc4 in loc2.Values) { //if (loc4.Primarys.Count == 0 && loc4.UniquesDict.Count > 0) //{ // foreach (var loc5 in loc4.UniquesDict.First().Value.Columns) // { // loc5.Column.IsPrimary = true; // loc4.Primarys.Add(loc5.Column); // } //} loc4.Primarys.Sort((c1, c2) => c1.Name.CompareTo(c2.Name)); loc4.Columns.Sort((c1, c2) => { int compare = c2.IsPrimary.CompareTo(c1.IsPrimary); if (compare == 0) { bool b1 = loc4.ForeignsDict.Values.Where(fk => fk.Columns.Where(c3 => c3.Name == c1.Name).Any()).Any(); bool b2 = loc4.ForeignsDict.Values.Where(fk => fk.Columns.Where(c3 => c3.Name == c2.Name).Any()).Any(); compare = b2.CompareTo(b1); } //if (compare == 0) compare = c1.Name.CompareTo(c2.Name); return(compare); }); loc1.Add(loc4); } loc1.Sort((t1, t2) => { var ret = t1.Schema.CompareTo(t2.Schema); if (ret == 0) { ret = t1.Name.CompareTo(t2.Name); } return(ret); }); loc2.Clear(); loc3.Clear(); return(loc1); }
public List <DbTableInfo> GetTables(string[] database, string tablename, bool ignoreCase) { var olddatabase = ""; using (var conn = _orm.Ado.MasterPool.Get(TimeSpan.FromSeconds(5))) { olddatabase = conn.Value.Database; } string[] tbname = null; string[] dbs = database == null || database.Any() == false ? new[] { olddatabase } : database; if (string.IsNullOrEmpty(tablename) == false) { tbname = _commonUtils.SplitTableName(tablename); if (tbname?.Length == 1) { tbname = new[] { "PUBLIC", tbname[0] } } ; if (ignoreCase) { tbname = tbname.Select(a => a.ToLower()).ToArray(); } dbs = new[] { olddatabase }; } var tables = new List <DbTableInfo>(); foreach (var db in dbs) { if (string.IsNullOrEmpty(db) || string.Compare(db, olddatabase, true) != 0) { continue; } var loc1 = new List <DbTableInfo>(); var loc2 = new Dictionary <string, DbTableInfo>(); var loc3 = new Dictionary <string, Dictionary <string, DbColumnInfo> >(); var sql = $@" {(tbname == null ? "" : $"select * from (")}select b.nspname || '.' || a.relname, b.nspname, a.relname, d.description, 'TABLE' from sys_class a inner join sys_namespace b on b.oid = a.relnamespace left join sys_description d on d.objoid = a.oid and objsubid = 0 where b.nspname not in ('DIRECTORIES', 'INFO_SCHEM', 'REPLICATION', 'STAGENT', 'SYSAUDIT', 'SYSDBA', 'SYSFTSDBA', 'SYSSECURE', 'SYS_GLOBAL_TEMP', 'WMSYS') and a.relkind in ('r') and b.nspname || '.' || a.relname not in ('PUBLIC.SPATIAL_REF_SYS') union all select b.nspname || '.' || a.relname, b.nspname, a.relname, d.description, 'VIEW' from sys_class a inner join sys_namespace b on b.oid = a.relnamespace left join sys_description d on d.objoid = a.oid and objsubid = 0 where b.nspname not in ('DIRECTORIES', 'INFO_SCHEM', 'REPLICATION', 'STAGENT', 'SYSAUDIT', 'SYSDBA', 'SYSFTSDBA', 'SYSSECURE', 'SYS_GLOBAL_TEMP', 'WMSYS') and a.relkind in ('m','v') and b.nspname || '.' || a.relname not in ('PUBLIC.GEOGRAPHY_COLUMNS','PUBLIC.GEOMETRY_COLUMNS','PUBLIC.RASTER_COLUMNS','PUBLIC.RASTER_OVERVIEWS','PUBLIC.DBA_JOBS') {(tbname == null ? "" : $") ft_dbf where {(ignoreCase ? "lower(nspname)" : "nspname")}={_commonUtils.FormatSql("{0}", tbname[0])} and {(ignoreCase ? "lower(relname)" : "relname")}={_commonUtils.FormatSql("{0}", tbname[1])}")}"; var ds = _orm.Ado.ExecuteArray(CommandType.Text, sql); if (ds == null) { return(loc1); } var loc6 = new List <string[]>(); var loc66 = new List <string[]>(); var loc6_1000 = new List <string>(); var loc66_1000 = new List <string>(); foreach (object[] row in ds) { var object_id = string.Concat(row[0]); var owner = string.Concat(row[1]); var table = string.Concat(row[2]); var comment = string.Concat(row[3]); Enum.TryParse <DbTableType>(string.Concat(row[4]), out var type); loc2.Add(object_id, new DbTableInfo { Id = object_id.ToString(), Schema = owner, Name = table, Comment = comment, Type = type }); loc3.Add(object_id, new Dictionary <string, DbColumnInfo>()); switch (type) { case DbTableType.VIEW: case DbTableType.TABLE: loc6_1000.Add(object_id); if (loc6_1000.Count >= 500) { loc6.Add(loc6_1000.ToArray()); loc6_1000.Clear(); } break; case DbTableType.StoreProcedure: loc66_1000.Add(object_id); if (loc66_1000.Count >= 500) { loc66.Add(loc66_1000.ToArray()); loc66_1000.Clear(); } break; } } if (loc6_1000.Count > 0) { loc6.Add(loc6_1000.ToArray()); } if (loc66_1000.Count > 0) { loc66.Add(loc66_1000.ToArray()); } if (loc6.Count == 0) { return(loc1); } var loc8 = new StringBuilder().Append("("); for (var loc8idx = 0; loc8idx < loc6.Count; loc8idx++) { if (loc8idx > 0) { loc8.Append(" OR "); } loc8.Append("a.table_name in ("); for (var loc8idx2 = 0; loc8idx2 < loc6[loc8idx].Length; loc8idx2++) { if (loc8idx2 > 0) { loc8.Append(","); } loc8.Append($"'{loc6[loc8idx][loc8idx2]}'"); } loc8.Append(")"); } loc8.Append(")"); sql = $@" 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, pg12以下 (select sys_get_expr(adbin, adrelid) from sys_attrdef where adrelid = e.adrelid and adnum = e.adnum limit 1) 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 sys_class c inner join sys_attribute a on a.attnum > 0 and a.attrelid = c.oid inner join sys_type t on t.oid = a.atttypid left join sys_type t2 on t2.oid = t.typelem left join sys_description d on d.objoid = a.attrelid and d.objsubid = a.attnum left join sys_attrdef e on e.adrelid = a.attrelid and e.adnum = a.attnum inner join sys_namespace ns on ns.oid = c.relnamespace inner join sys_namespace ns2 on ns2.oid = t.typnamespace where {loc8.ToString().Replace("a.table_name", "ns.nspname || '.' || c.relname")}"; ds = _orm.Ado.ExecuteArray(CommandType.Text, sql); if (ds == null) { return(loc1); } var position = 0; foreach (object[] row in ds) { var object_id = string.Concat(row[0]); var column = string.Concat(row[1]); var type = string.Concat(row[2]); var max_length = int.Parse(string.Concat(row[3])); var sqlType = string.Concat(row[4]); var is_nullable = string.Concat(row[5]) == "1"; var is_identity = string.Concat(row[6]).StartsWith(@"NEXTVAL('") && (string.Concat(row[6]).EndsWith(@"'::text)") || string.Concat(row[6]).EndsWith(@"')")); var comment = string.Concat(row[7]); var defaultValue = string.Concat(row[6]); 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.ToLower()) { 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); } if (max_length > 0) { switch (sqlType.ToLower()) { //case "numeric": sqlType += $"({max_length})"; break; case "bpchar": case "varchar": case "bytea": case "bit": case "varbit": sqlType += $"({max_length})"; break; } } if (attndims > 0) { type += "[]"; } loc3[object_id].Add(column, new DbColumnInfo { Name = column, MaxLength = max_length, IsIdentity = is_identity, IsNullable = is_nullable, IsPrimary = false, DbTypeText = type, DbTypeTextFull = sqlType, Table = loc2[object_id], Comment = comment, DefaultValue = defaultValue, Position = ++position }); loc3[object_id][column].DbType = this.GetDbType(loc3[object_id][column]); loc3[object_id][column].CsType = this.GetCsTypeInfo(loc3[object_id][column]); } sql = $@" select ns.nspname || '.' || d.relname as table_id, c.attname, 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, --case when sys_index_column_has_property(b.oid, c.attnum, 'desc') = 't' then 1 else 0 end IsDesc, 0, a.indkey, c.attnum from sys_index a inner join sys_class b on b.oid = a.indexrelid inner join sys_attribute c on c.attnum > 0 and c.attrelid = b.oid inner join sys_namespace ns on ns.oid = b.relnamespace inner join sys_class d on d.oid = a.indrelid where {loc8.ToString().Replace("a.table_name", "ns.nspname || '.' || d.relname")} "; ds = _orm.Ado.ExecuteArray(CommandType.Text, sql); if (ds == null) { return(loc1); } var indexColumns = new Dictionary <string, Dictionary <string, DbIndexInfo> >(); var uniqueColumns = new Dictionary <string, Dictionary <string, DbIndexInfo> >(); foreach (object[] row in ds) { var object_id = string.Concat(row[0]); var column = string.Concat(row[1]); var index_id = string.Concat(row[2]); var is_unique = string.Concat(row[3]) == "1"; var is_primary_key = string.Concat(row[4]) == "1"; var is_clustered = string.Concat(row[5]) == "1"; var is_desc = string.Concat(row[6]) == "1"; var inkey = string.Concat(row[7]).Split(' '); var attnum = int.Parse(string.Concat(row[8])); attnum = int.Parse(inkey[attnum - 1]); foreach (string tc in loc3[object_id].Keys) { if (loc3[object_id][tc].DbTypeText.EndsWith("[]")) { column = tc; break; } } if (loc3.ContainsKey(object_id) == false || loc3[object_id].ContainsKey(column) == false) { continue; } var loc9 = loc3[object_id][column]; if (loc9.IsPrimary == false && is_primary_key) { loc9.IsPrimary = is_primary_key; } Dictionary <string, DbIndexInfo> loc10 = null; DbIndexInfo loc11 = null; if (!indexColumns.TryGetValue(object_id, out loc10)) { indexColumns.Add(object_id, loc10 = new Dictionary <string, DbIndexInfo>()); } if (!loc10.TryGetValue(index_id, out loc11)) { loc10.Add(index_id, loc11 = new DbIndexInfo()); } loc11.Columns.Add(new DbIndexColumnInfo { Column = loc9, IsDesc = is_desc }); if (is_unique && !is_primary_key) { if (!uniqueColumns.TryGetValue(object_id, out loc10)) { uniqueColumns.Add(object_id, loc10 = new Dictionary <string, DbIndexInfo>()); } if (!loc10.TryGetValue(index_id, out loc11)) { loc10.Add(index_id, loc11 = new DbIndexInfo()); } loc11.Columns.Add(new DbIndexColumnInfo { Column = loc9, IsDesc = is_desc }); } } foreach (var object_id in indexColumns.Keys) { foreach (var column in indexColumns[object_id]) { loc2[object_id].IndexesDict.Add(column.Key, column.Value); } } foreach (var object_id in uniqueColumns.Keys) { foreach (var column in uniqueColumns[object_id]) { column.Value.Columns.Sort((c1, c2) => c1.Column.Name.CompareTo(c2.Column.Name)); loc2[object_id].UniquesDict.Add(column.Key, column.Value); } } if (tbname == null) { sql = $@" select a.pktable_schem || '.' || a.pktable_name, a.pkcolumn_name, a.fk_name, a.fktable_schem || '.' || a.fktable_name, 1, a.fkcolumn_name from v_sys_foreign_keys a where {loc8.ToString().Replace("a.table_name", "a.pktable_schem || '.' || a.pktable_name")} "; ds = _orm.Ado.ExecuteArray(CommandType.Text, sql); if (ds == null) { return(loc1); } var fkColumns = new Dictionary <string, Dictionary <string, DbForeignInfo> >(); foreach (var 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]); if (loc3.ContainsKey(table_id) == false || loc3[table_id].ContainsKey(column) == false) { continue; } var loc9 = loc3[table_id][column]; if (loc2.ContainsKey(ref_table_id) == false) { continue; } var loc10 = loc2[ref_table_id]; var loc11 = loc3[ref_table_id][referenced_column]; Dictionary <string, DbForeignInfo> loc12 = null; DbForeignInfo loc13 = null; if (!fkColumns.TryGetValue(table_id, out loc12)) { fkColumns.Add(table_id, loc12 = new Dictionary <string, DbForeignInfo>()); } if (!loc12.TryGetValue(fk_id, out loc13)) { loc12.Add(fk_id, loc13 = new DbForeignInfo { Table = loc2[table_id], ReferencedTable = loc10 }); } loc13.Columns.Add(loc9); loc13.ReferencedColumns.Add(loc11); } foreach (var table_id in fkColumns.Keys) { foreach (var fk in fkColumns[table_id]) { loc2[table_id].ForeignsDict.Add(fk.Key, fk.Value); } } } foreach (var table_id in loc3.Keys) { foreach (var loc5 in loc3[table_id].Values) { loc2[table_id].Columns.Add(loc5); if (loc5.IsIdentity) { loc2[table_id].Identitys.Add(loc5); } if (loc5.IsPrimary) { loc2[table_id].Primarys.Add(loc5); } } } foreach (var loc4 in loc2.Values) { //if (loc4.Primarys.Count == 0 && loc4.UniquesDict.Count > 0) //{ // foreach (var loc5 in loc4.UniquesDict.First().Value.Columns) // { // loc5.Column.IsPrimary = true; // loc4.Primarys.Add(loc5.Column); // } //} loc4.Primarys.Sort((c1, c2) => c1.Name.CompareTo(c2.Name)); loc4.Columns.Sort((c1, c2) => { int compare = c2.IsPrimary.CompareTo(c1.IsPrimary); if (compare == 0) { bool b1 = loc4.ForeignsDict.Values.Where(fk => fk.Columns.Where(c3 => c3.Name == c1.Name).Any()).Any(); bool b2 = loc4.ForeignsDict.Values.Where(fk => fk.Columns.Where(c3 => c3.Name == c2.Name).Any()).Any(); compare = b2.CompareTo(b1); } if (compare == 0) { compare = c1.Name.CompareTo(c2.Name); } return(compare); }); loc1.Add(loc4); } loc1.Sort((t1, t2) => { var ret = t1.Schema.CompareTo(t2.Schema); if (ret == 0) { ret = t1.Name.CompareTo(t2.Name); } return(ret); }); loc2.Clear(); loc3.Clear(); tables.AddRange(loc1); } return(tables); }