Ejemplo n.º 1
0
        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],
                    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]);
            }

            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);
        }
Ejemplo n.º 2
0
        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)), 0),
b.comments
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[8];
                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]);
                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]);
                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
                });
                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 = string.Format(@"
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 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);
        }