public List<DbTableInfo> GetTables(string[] database, string tablename, bool ignoreCase)
{
var loc1 = new List<DbTableInfo>();
var loc2 = new Dictionary<string, DbTableInfo>();
var loc3 = new Dictionary<string, Dictionary<string, DbColumnInfo>>();
string[] tbname = null;
if (string.IsNullOrEmpty(tablename) == false)
{
tbname = _commonUtils.SplitTableName(tablename);
if (tbname?.Length == 1) tbname = new[] { "main", tbname[0] };
if (ignoreCase) tbname = tbname.Select(a => a.ToLower()).ToArray();
database = new[] { tbname[0] };
}
else if (database == null || database.Any() == false || string.IsNullOrEmpty(database.FirstOrDefault()))
database = GetDatabases().ToArray();
if (database.Any() == false) return loc1;
Action<object[], int> addColumn = (row, position) =>
{
string table_id = string.Concat(row[0]);
string column = string.Concat(row[1]);
string type = string.Concat(row[2]);
//long max_length = long.Parse(string.Concat(row[3]));
string sqlType = string.Concat(row[4]);
var m_len = Regex.Match(sqlType, @"\w+\((\d+)");
int max_length = m_len.Success ? int.Parse(m_len.Groups[1].Value) : -1;
bool is_nullable = string.Concat(row[5]) == "1";
bool is_identity = string.Concat(row[6]) == "1";
bool is_primary = string.Concat(row[7]) == "1";
string comment = string.Concat(row[8]);
string defaultValue = string.Concat(row[9]);
if (max_length == 0) max_length = -1;
loc3[table_id].Add(column, new DbColumnInfo
{
Name = column,
MaxLength = max_length,
IsIdentity = is_identity,
IsNullable = is_nullable,
IsPrimary = is_primary,
DbTypeText = type,
DbTypeTextFull = sqlType,
Table = loc2[table_id],
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]);
};
foreach (var db in database)
{
// 修复：读取真实type字段，不再写死'TABLE'
var sql = $@"
select
'{db}.' || tbl_name,
'{db}',
tbl_name,
'' Comment,
type,
sql
from {db}.sqlite_master where (type='table' or type='view') {(tbname == null ? "" : $" and {(ignoreCase ? "lower(tbl_name)" : "tbl_name")}={_commonUtils.FormatSql("{0}", tbname[1])}")}";
var ds = _orm.Ado.ExecuteArray(CommandType.Text, sql);
if (ds == null) continue;
var loc6 = new List<string[]>();
var loc66 = new List<string[]>();
var loc6_1000 = new List<string>();
var loc66_1000 = new List<string>();
foreach (var row in ds)
{
var table_id = string.Concat(row[0]);
var schema = string.Concat(row[1]);
var table = string.Concat(row[2]);
var comment = string.Concat(row[3]);
// 现在取数据库真实type值，VIEW/Table正常识别
string objType = string.Concat(row[4]);
var tableType = objType.Equals("VIEW", StringComparison.OrdinalIgnoreCase)
? 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 = tableType
});
loc3.Add(table_id, new Dictionary<string, DbColumnInfo>());
switch (tableType)
{
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 ((tableType == DbTableType.TABLE || tableType == DbTableType.VIEW) && table != "sqlite_sequence")
{
var dsql = string.Concat(row[5]);
var cols = _orm.Ado.ExecuteArray(CommandType.Text, $"PRAGMA \"{db}\".table_info(\"{table}\")");
var position = 0;
foreach (var col in cols)
{
var col_name = string.Concat(col[1]);
var is_identity = false;
// 视图不解析AUTOINCREMENT，只有表才解析
if (tableType == DbTableType.TABLE)
{
var dsqlIdx = dsql?.IndexOf($"\"{col_name}\" ");
if (dsqlIdx > 0)
{
var dsqlLastIdx = dsql.IndexOf('\n', dsqlIdx.Value);
if (dsqlLastIdx > 0)
is_identity = dsql.Substring(dsqlIdx.Value, dsqlLastIdx - dsqlIdx.Value).Contains("AUTOINCREMENT");
}
}
var ds2item = new object[10];
ds2item[0] = table_id;
ds2item[1] = col_name;
ds2item[2] = Regex.Replace(string.Concat(col[2]), @"\(\d+(\b*,\b*\d+)?\)", "").ToUpper();
ds2item[4] = string.Concat(col[2]).ToUpper();
ds2item[5] = string.Concat(col[5]) == "0" && string.Concat(col[3]) == "0" ? 1 : 0;
ds2item[6] = is_identity ? 1 : 0;
ds2item[7] = string.Concat(col[5]) == "1" ? 1 : 0;
ds2item[8] = "";
ds2item[9] = string.Concat(col[4]);
addColumn(ds2item, ++position);
}
// ========== 索引、唯一键、外键仅TABLE执行，VIEW跳过 ==========
if (tableType == DbTableType.TABLE)
{
Dictionary<string, DbIndexInfo> indexes = new Dictionary<string, DbIndexInfo>();
var dbIndexes = _orm.Ado.ExecuteArray(CommandType.Text, $"PRAGMA \"{db}\".INDEX_LIST(\"{table}\")");
foreach (var dbIndex in dbIndexes)
{
if (string.Concat(dbIndex[3]) == "pk") continue;
var dbIndexesColumns = _orm.Ado.ExecuteArray(CommandType.Text, $"PRAGMA \"{db}\".INDEX_INFO(\"{dbIndex[1]}\")");
var dbIndexesSql = string.Concat(_orm.Ado.ExecuteScalar(CommandType.Text, $" SELECT sql FROM \"{db}\".sqlite_master WHERE name = '{dbIndex[1]}'"));
foreach (var dbcolumn in dbIndexesColumns)
{
var column = string.Concat(dbcolumn[2]);
var indexName = string.Concat(dbIndex[1]);
var isDesc = dbIndexesSql.IndexOf($@"{column}"" DESC", StringComparison.CurrentCultureIgnoreCase) == -1 ? "0" : "1";
var isUnique = string.Concat(dbIndex[2]);
if (loc3.ContainsKey(table_id) == false || loc3[table_id].ContainsKey(column) == false) continue;
var loc9 = loc3[table_id][column];
if (indexes.TryGetValue(indexName, out var indexInfo) == false)
indexes.Add(indexName, indexInfo = new DbIndexInfo
{
IsUnique = isUnique == "1",
Name = indexName
});
if (indexInfo.Columns.Any(a => a.Column.Name == column) == false)
indexInfo.Columns.Add(new DbIndexColumnInfo
{
Column = loc9,
IsDesc = isDesc == "1"
});
}
}
foreach (var indexItem in indexes)
{
if (indexItem.Value.IsUnique)
loc2[table_id].UniquesDict.Add(indexItem.Key, indexItem.Value);
else
loc2[table_id].IndexesDict.Add(indexItem.Key, indexItem.Value);
}
if (tbname == null)
{
var fks = _orm.Ado.ExecuteArray(CommandType.Text, $"PRAGMA \"{db}\".foreign_key_list(\"{table}\")");
if (fks != null && fks.Length > 0)
{
var fkColumns = new Dictionary<string, Dictionary<string, DbForeignInfo>>();
foreach (var fk in fks)
{
string column = string.Concat(fk[3]);
string fk_id = $"{db}.{table}.{fk[0]}";
string ref_table_id = database.Length == 1 ? string.Concat(fk[2]) : $"{db}.{fk[2]}";
string referenced_column = string.Concat(fk[4]);
if (loc3.ContainsKey(table_id) == false || loc3[table_id].ContainsKey(column) == false) continue;
var loc9 = loc3[table_id][column];
if (loc2.ContainsKey(ref_table_id) == false) continue;
var loc10 = loc2[ref_table_id];
var loc11 = loc3[ref_table_id][referenced_column];
Dictionary<string, DbForeignInfo> loc12 = null;
DbForeignInfo loc13 = null;
if (!fkColumns.TryGetValue(table_id, out loc12))
fkColumns.Add(table_id, loc12 = new Dictionary<string, DbForeignInfo>());
if (!loc12.TryGetValue(fk_id, out loc13))
loc12.Add(fk_id, loc13 = new DbForeignInfo { Table = loc2[table_id], ReferencedTable = loc10 });
loc13.Columns.Add(loc9);
loc13.ReferencedColumns.Add(loc11);
}
foreach (var table_id2 in fkColumns.Keys)
foreach (var fk in fkColumns[table_id2])
loc2[table_id2].ForeignsDict.Add(fk.Key, fk.Value);
}
}
}
}
}
if (loc6_1000.Count > 0) loc6.Add(loc6_1000.ToArray());
if (loc66_1000.Count > 0) loc66.Add(loc66_1000.ToArray());
if (loc6.Count == 0) continue;
}
foreach (var table_id in loc3.Keys)
{
foreach (var loc5 in loc3[table_id].Values)
{
loc2[table_id].Columns.Add(loc5);
if (loc5.IsIdentity) loc2[table_id].Identitys.Add(loc5);
if (loc5.IsPrimary) loc2[table_id].Primarys.Add(loc5);
}
}
foreach (var loc4 in loc2.Values)
{
//if (loc4.Primarys.Count == 0 && loc4.UniquesDict.Count > 0)
//{
// foreach (var loc5 in loc4.UniquesDict.First().Value.Columns)
// {
// loc5.Column.IsPrimary = true;
// loc4.Primarys.Add(loc5.Column);
// }
//}
loc4.Primarys.Sort((c1, c2) => c1.Name.CompareTo(c2.Name));
loc4.Columns.Sort((c1, c2) =>
{
int compare = c2.IsPrimary.CompareTo(c1.IsPrimary);
if (compare == 0)
{
bool b1 = loc4.ForeignsDict.Values.Where(fk => fk.Columns.Where(c3 => c3.Name == c1.Name).Any()).Any();
bool b2 = loc4.ForeignsDict.Values.Where(fk => fk.Columns.Where(c3 => c3.Name == c2.Name).Any()).Any();
compare = b2.CompareTo(b1);
}
//if (compare == 0) compare = c1.Name.CompareTo(c2.Name);
return compare;
});
loc1.Add(loc4);
}
loc1.Sort((t1, t2) =>
{
var ret = t1.Schema.CompareTo(t2.Schema);
if (ret == 0) ret = t1.Name.CompareTo(t2.Name);
return ret;
});
loc2.Clear();
loc3.Clear();
return loc1;
}
// c# code
目前使用的 FreeSql.Generator版本QLite中识别不了视图的bug，生成实现类也是只有table 。
看了一下代码，并没完整的对视图识别强写了TABLE
把FreeSql.Provider.Sqlite\SqliteDbFirst.cs 里的GetTables 方法修改如下
没详细研究，目前也能在模板中识别：gen.table.Type == FreeSql.DatabaseModel.DbTableType.VIEW
估计在引用：FreeSql.Provider.Sqlite\SqliteDbFirst.cs 的程序也存在相同的问题。