public List <TableShowEntity> GetTableShowList(string connStr, string databaseName)
{
const string tablesql = "select name from sqlite_master where type='table' and name<>'sqlite_sequence' and name<>'oviki_comment' ORDER BY name ";
const string viewsql = "select name from sqlite_master where type='view' ORDER BY name ";
//const string viewsql = @"SELECT name FROM sqlite_master WHERE type = 'view'";
List <TableShowEntity> list = new List <TableShowEntity>();
var helper = SqlHelperFactory.OpenDatabase(connStr, _provider.GetProviderFactory(), SqlType.SQLite);
var dt = helper.ExecuteDataTable(tablesql);
foreach (DataRow item in dt.Rows)
{
var dt2 = helper.ExecuteDataTable($"PRAGMA table_info({item["name"].ToString()})");
foreach (DataRow row in dt2.Rows)
{
var entity = new TableShowEntity()
{
TableType = "t",
Name = item["name"].ToString(),
ColumnName = row["name"].ToString(),
Type = row["type"].ToString(),
IsNullAble = row["notnull"].ToString() == "0",
DefaultValue = row["dflt_value"]?.ToString(),
IsPrimaryKey = row["pk"].ToString() == "1",
};
list.Add(entity);
}
}
dt = helper.ExecuteDataTable(viewsql);
helper.Dispose();
foreach (DataRow item in dt.Rows)
{
var dt2 = helper.ExecuteDataTable($"PRAGMA table_info({item["name"].ToString()})");
foreach (DataRow row in dt2.Rows)
{
list.Add(new TableShowEntity()
{
TableType = "v",
Name = item["name"].ToString(),
ColumnName = row["name"].ToString(),
Type = row["type"].ToString(),
IsNullAble = row["notnull"].ToString() == "0",
DefaultValue = row["dflt_value"]?.ToString(),
IsPrimaryKey = row["pk"].ToString() == "1",
});
}
}
return(list);
}
public StructureItemModel(TableShowEntity columnEntity)
{
ColumnName = columnEntity.ColumnName;
Nullable = columnEntity.IsNullAble ? "?" : null;
if (string.IsNullOrEmpty(columnEntity.DefaultValue) == false)
{
DefaultValue = columnEntity.DefaultValue;
}
if (string.IsNullOrEmpty(columnEntity.ColumnComment) == false)
{
Comment = columnEntity.ColumnComment;
}
if (columnEntity.IsPrimaryKey && columnEntity.IsIdentity)
{
KeyType = "fka";
}
else if (columnEntity.IsPrimaryKey)
{
KeyType = "fk";
}
else if (columnEntity.IsIdentity)
{
KeyType = "fa";
}
else
{
KeyType = "f";
}
ColumnType = columnEntity.Type.ToLower();
if (ColumnType == "varchar" || ColumnType == "nvarchar" || ColumnType == "char" || ColumnType == "nchar")
{
ColumnType = ColumnType + "(" + columnEntity.Length + ")";
}
else if (ColumnType == "decimal" || ColumnType == "numeric")
{
ColumnType = ColumnType + "(" + columnEntity.Precision + "," + columnEntity.Scale + ")";
}
}
public List <TableShowEntity> GetTableShowList(string connStr, string databaseName)
{
var sql = @"SELECT col.table_schema AS SchemaName, col.table_name TableName,
col.ordinal_position AS Index, col.column_name ColumnName, col.character_maximum_length Length,
case when col.is_nullable='YES' then 'true' else 'false' end IsNullAble,
col.numeric_precision AS Precision, col.numeric_scale Scale,
col_description(c.oid, col.ordinal_position) AS Comment,
col.column_default AS DefaultValue,
case when col.is_identity='YES' then 'true' else 'false' end IsIdentity,
col.data_type AS col_type,
bt.typname AS elem_name,c.relkind TableType,
(case when(select count(*) from pg_constraint where conrelid = b.attrelid and conkey[1] = b.attnum and contype = 'p') > 0 then 'true' else 'false' end) IsPrimaryKey
FROM information_schema.columns AS col
LEFT JOIN pg_namespace ns ON ns.nspname = col.table_schema
LEFT JOIN pg_class c ON col.table_name = c.relname AND c.relnamespace = ns.oid
LEFT JOIN pg_attrdef a ON c.oid = a.adrelid AND col.ordinal_position = a.adnum
LEFT JOIN pg_attribute b ON b.attrelid = c.oid AND b.attname = col.column_name
LEFT JOIN pg_type et ON et.oid = b.atttypid
LEFT JOIN pg_collation coll ON coll.oid = b.attcollation
LEFT JOIN pg_namespace colnsp ON coll.collnamespace = colnsp.oid
LEFT JOIN pg_type bt ON et.typelem = bt.oid LEFT JOIN pg_namespace nbt ON bt.typnamespace = nbt.oid
WHERE c.relkind in ('r','v') and ns.nspname not in ('pg_catalog', 'pg_toast', 'information_schema')
ORDER BY col.table_schema,c.relkind, col.table_name, col.ordinal_position";
const string sql_table = @"select ns.nspname SchemaName, cls.relname as TableName ,(select description from pg_description where objoid=cls.oid and objsubid=0) as Comment
from pg_class cls
join pg_catalog.pg_namespace as ns on ns.oid = cls.relnamespace
where cls.relkind in ('r','v') and ns.nspname not in ('pg_catalog', 'pg_toast', 'information_schema')
order by TableName;";
Dictionary <string, string> comments = new Dictionary <string, string>();
var helper = SqlHelperFactory.OpenDatabase(connStr, _provider.GetProviderFactory(), SqlType.PostgreSQL);
helper.ChangeDatabase(databaseName);
var entities = helper.Select <TableEntity>(sql_table);
foreach (var entity in entities)
{
comments[entity.SchemaName + "." + entity.TableName] = entity.Comment;
}
var entities2 = helper.Select <TableColumnTemp>(sql);
helper.Dispose();
List <TableShowEntity> result = new List <TableShowEntity>();
foreach (var temp in entities2)
{
TableShowEntity entity = new TableShowEntity()
{
SchemaName = temp.SchemaName,
Name = temp.TableName,
Index = temp.Index,
ColumnName = temp.ColumnName,
ColumnComment = temp.Comment,
DefaultValue = temp.DefaultValue,
Length = temp.Length,
Precision = temp.Precision,
Scale = temp.Scale,
IsPrimaryKey = temp.IsPrimaryKey,
IsIdentity = temp.IsIdentity,
IsNullAble = temp.IsNullAble,
Type = temp.col_type,
TableType = temp.TableType,
};
if (entity.Type == "ARRAY")
{
entity.Type = temp.elem_name + "[]";
}
if (entity.TableType == "r")
{
entity.TableType = "t";
}
if (comments.TryGetValue(temp.SchemaName + "." + temp.TableName, out string comment))
{
entity.Comment = comment;
}
result.Add(entity);
}
return(result);
// select
//(select relname || '--' || (select description from pg_description where objoid = oid and objsubid = 0) as comment from pg_class where oid = a.attrelid) as table_name,
//a.attname as column_name,
//format_type(a.atttypid, a.atttypmod) as data_type,
//(case when atttypmod-4 > 0 then atttypmod-4 else 0 end)data_length,
//(case when(select count(*) from pg_constraint where conrelid = a.attrelid and conkey[1] = attnum and contype = 'p') > 0 then 'Y' else 'N' end) as 主键约束,
//(case when(select count(*) from pg_constraint where conrelid = a.attrelid and conkey[1] = attnum and contype = 'u') > 0 then 'Y' else 'N' end) as 唯一约束,
//(case when(select count(*) from pg_constraint where conrelid = a.attrelid and conkey[1] = attnum and contype = 'f') > 0 then 'Y' else 'N' end) as 外键约束,
//(case when a.attnotnull = true then 'Y' else 'N' end) as nullable,
//col_description(a.attrelid, a.attnum) as comment
//from pg_attribute a
//where attstattarget = -1 and attrelid in (select oid from pg_class where relname in(select relname from pg_class where relkind = 'r' and relname is not null ))
//order by table_name,a.attnum;
}
