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