public DbTableDef GetOrAddTable(string tableName, DbTableType tt) { // add a table. No dupes. if (string.IsNullOrWhiteSpace(tableName)) { return(null); } DbTableDef table; if (Tables.TryGetValue(tableName, out table)) { // its ok to change DbTableType ? return(table); } table = new DbTableDef(tableName, tt); AddTable(table); return(table); }
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); }
public DbTableDef(string tableName, DbTableType tt) { TableName = tableName; Name = GetEntityName(tableName); Type = tt; }
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 a.""owner"" || a.tabname as id, trim(a.""owner"") as owner, trim(a.tabname) as name, trim(b.comments) as comment, a.tabtype as type from systables a left join syscomments b on b.tabname = a.tabname where a.tabtype in ('T', 'V')" + (tbname == null ? "" : $" and {(ignoreCase ? "upper(trim(a.tabname))" : "trim(a.tabname)")} = {_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 "V": 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; } loc1.Add(loc2[table_id]); } if (loc6_1000.Count > 0) { loc6.Add(loc6_1000.ToArray()); } if (loc66_1000.Count > 0) { loc66.Add(loc66_1000.ToArray()); } //todo: ... return(loc1); }