private void btn_AttrAdd_Click(object sender, EventArgs e)
{
List <string> existIndexName = new List <string>();
for (int i = 0; i < this.lb_AttrIndex.Items.Count; i++)
{
existIndexName.Add(this.lb_AttrIndex.Items[i].ToString());
}
CreateDBIndexDlg myDlg = new CreateDBIndexDlg(fcFields, existIndexName);
if (myDlg.ShowDialog() == DialogResult.OK)
{
IDbIndexInfo myNewDBIndex = new DbIndexInfo();
myNewDBIndex.Name = myDlg.sDBIndexName;
foreach (string sField in myDlg.listDBIndexFields)
{
myNewDBIndex.AppendFieldDefine(sField, true);
}
int iAddIndex = this.lb_AttrIndex.Items.Add(new myAttrIndex(myNewDBIndex, true));
this.lb_AttrIndex.SelectedIndex = iAddIndex;
}
}
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);
}
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);
}
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
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] = OracleCodeFirst.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]);
}
OracleCodeFirst.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);
}
public List <DbTableInfo> GetTables(string[] database, string tablename, bool ignoreCase)
{
var olddatabase = "";
var is96 = true;
using (var conn = _orm.Ado.MasterPool.Get(TimeSpan.FromSeconds(5)))
{
olddatabase = conn.Value.Database;
is96 = PgVersionIs96(conn.Value.ServerVersion);
}
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.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')
{(tbname == null ? "" : $") ft_dbf where {(ignoreCase ? "lower(schemaname)" : "schemaname")}={_commonUtils.FormatSql("{0}", tbname[0])} and {(ignoreCase ? "lower(tablename)" : "tablename")}={_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 pg_get_expr(adbin, adrelid) from pg_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{(IsPg10 ? ", a.attidentity" : "")}
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)") || string.Concat(row[6]).EndsWith(@"')")) ||
IsPg10 && new[] { "a", "d" }.Contains(string.Concat(row[12])); //pg10 GENERATED { BY DEFAULT | AWAYS } AS IDENTITY
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,
{(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);
}
}
if (tbname == null)
{
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> 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[] { olddatabase, "dbo", tbname[0] }
}
;
if (tbname?.Length == 2)
{
tbname = new[] { olddatabase, tbname[0], tbname[1] }
}
;
tbname = tbname.Select(a => a.ToLower()).ToArray();
dbs = new[] { tbname[0] };
}
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 * from (
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
) ft_dbf{(tbname == null ? "" : _commonUtils.FormatSql(" where lower([owner])={0} and lower([name])={1}", new[] { tbname[1], tbname[2] }))}
order by type desc, [owner], [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' and class=1) '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);
}
var position = 0;
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],
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 = $@"
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);
}
}
if (tbname == null)
{
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>(StringComparer.CurrentCultureIgnoreCase);
var loc3 = new Dictionary <string, Dictionary <string, DbColumnInfo> >(StringComparer.CurrentCultureIgnoreCase);
string[] tbname = null;
if (string.IsNullOrEmpty(tablename) == false)
{
tbname = _commonUtils.SplitTableName(tablename);
if (tbname?.Length == 1)
{
using (var conn = _orm.Ado.MasterPool.Get(TimeSpan.FromSeconds(5)))
{
if (string.IsNullOrEmpty(conn.Value.Database))
{
return(loc1);
}
tbname = new[] { conn.Value.Database, tbname[0] };
}
}
if (ignoreCase)
{
tbname = tbname.Select(a => a.ToLower()).ToArray();
}
database = new[] { tbname[0] };
}
else 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 = $@"
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 {(ignoreCase ? "lower(a.table_schema)" : "a.table_schema")} in ({databaseIn}){(tbname == null ? "" : $" and {(ignoreCase ? "lower(a.table_name)" : "a.table_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>(StringComparer.CurrentCultureIgnoreCase));
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 = $@"
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 {(ignoreCase ? "lower(a.table_schema)" : "a.table_schema")} in ({databaseIn}) and {loc8}
";
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],
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]);
}
sql = $@"
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 {(ignoreCase ? "lower(a.table_schema)" : "a.table_schema")} in ({databaseIn}) and {loc8}
";
ds = _orm.Ado.ExecuteArray(CommandType.Text, sql);
if (ds == null)
{
return(loc1);
}
var indexColumns = new Dictionary <string, Dictionary <string, DbIndexInfo> >(StringComparer.CurrentCultureIgnoreCase);
var uniqueColumns = new Dictionary <string, Dictionary <string, DbIndexInfo> >(StringComparer.CurrentCultureIgnoreCase);
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>(StringComparer.CurrentCultureIgnoreCase));
}
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>(StringComparer.CurrentCultureIgnoreCase));
}
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
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 {(ignoreCase ? "lower(a.constraint_schema)" : "a.constraint_schema")} in ({databaseIn}) and {loc8} and not isnull(position_in_unique_constraint)
";
ds = _orm.Ado.ExecuteArray(CommandType.Text, sql);
if (ds == null)
{
return(loc1);
}
var fkColumns = new Dictionary <string, Dictionary <string, DbForeignInfo> >(StringComparer.CurrentCultureIgnoreCase);
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>(StringComparer.CurrentCultureIgnoreCase));
}
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)
{
string[] tbname = null;
if (string.IsNullOrEmpty(tablename) == false)
{
tbname = _commonUtils.SplitTableName(tablename);
if (ignoreCase)
{
tbname = tbname.Select(a => a.ToUpper()).ToArray();
}
}
var loc1 = new List <DbTableInfo>();
var loc2 = new Dictionary <string, DbTableInfo>();
var loc3 = new Dictionary <string, Dictionary <string, DbColumnInfo> >();
var sql = @"
select
trim(rdb$relation_name) as id,
trim(rdb$owner_name) as owner,
trim(rdb$relation_name) as name,
trim(rdb$external_description) as comment,
rdb$relation_type as type
from rdb$relations
where rdb$system_flag=0" + (tbname == null ? "" : $" and {(ignoreCase ? "upper(trim(rdb$relation_name))" : "trim(rdb$relation_name)")} = {_commonUtils.FormatSql("{0}", tbname.Last())}");
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]);
DbTableType type = DbTableType.TABLE;
switch (string.Concat(row[4]))
{
case "1": type = DbTableType.VIEW; break;
}
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("trim(d.rdb$relation_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
trim(d.rdb$relation_name),
trim(a.rdb$field_name),
case
when b.rdb$field_sub_type = 2 then 'DECIMAL'
when b.rdb$field_type = 14 then 'CHAR'
when b.rdb$field_type = 37 then 'VARCHAR'
when b.rdb$field_type = 8 then 'INTEGER'
when b.rdb$field_type = 16 then 'BIGINT'
when b.rdb$field_type = 27 then 'DOUBLE PRECISION'
when b.rdb$field_type = 7 then 'SMALLINT'
else
(select trim(rdb$type_name) from rdb$types where rdb$type = b.rdb$field_type and rdb$field_name = 'RDB$FIELD_TYPE' rows 1) ||
coalesce((select ' SUB_TYPE ' || rdb$type from rdb$types where b.rdb$field_type = 261 and rdb$type = b.rdb$field_sub_type and rdb$field_name = 'RDB$FIELD_SUB_TYPE' rows 1),'')
end || trim(case when b.rdb$dimensions = 1 then '[]' else '' end),
b.rdb$character_length,
case
when b.rdb$field_sub_type = 2 then (select 'DECIMAL(' || rdb$field_precision || ',' || abs(rdb$field_scale) || ')' from rdb$types where b.rdb$field_sub_type = 2 and rdb$type = b.rdb$field_sub_type and rdb$field_name = 'RDB$FIELD_SUB_TYPE' rows 1)
when b.rdb$field_type = 14 then 'CHAR(' || b.rdb$character_length || ')'
when b.rdb$field_type = 37 then 'VARCHAR(' || b.rdb$character_length || ')'
when b.rdb$field_type = 8 then 'INTEGER'
when b.rdb$field_type = 16 then 'BIGINT'
when b.rdb$field_type = 27 then 'DOUBLE PRECISION'
when b.rdb$field_type = 7 then 'SMALLINT'
else
(select trim(rdb$type_name) from rdb$types where rdb$type = b.rdb$field_type and rdb$field_name = 'RDB$FIELD_TYPE' rows 1) ||
coalesce((select ' SUB_TYPE ' || rdb$type from rdb$types where b.rdb$field_type = 261 and rdb$type = b.rdb$field_sub_type and rdb$field_name = 'RDB$FIELD_SUB_TYPE' rows 1),'')
end || trim(case when b.rdb$dimensions = 1 then '[]' else '' end),
case when a.rdb$null_flag = 1 then 0 else 1 end,
{((_orm.Ado as FirebirdAdo)?.IsFirebird2_5 == true ? "0" : "case when a.rdb$identity_type = 1 then 1 else 0 end")},
a.rdb$description,
a.rdb$default_value
from rdb$relation_fields a
inner join rdb$fields b on b.rdb$field_name = a.rdb$field_source
inner join rdb$relations d on d.rdb$relation_name = a.rdb$relation_name
where a.rdb$system_flag = 0 and {loc8}
order by a.rdb$relation_name, a.rdb$field_position
";
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]).Trim();
//long max_length = long.Parse(string.Concat(row[3]));
string sqlType = string.Concat(row[4]).Trim();
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]);
}
sql = string.Format(@"
select
trim(d.rdb$relation_name),
trim(c.rdb$field_name),
trim(d.rdb$index_name),
case when d.rdb$unique_flag = 1 then 1 else 0 end,
case when exists(select first 1 1 from rdb$relation_constraints where rdb$index_name = d.rdb$index_name and rdb$constraint_type = 'PRIMARY KEY') then 1 else 0 end,
0,
0
from rdb$indices d
inner join rdb$index_segments c on c.rdb$index_name = d.rdb$index_name
where {0}", 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]);
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);
}
}
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);
}
