private TableInfo CreateTable(string tableName)
{
TableInfo table = new TableInfo()
{
Name = tableName
};
string sql = $@"select
ordinal_position as Colorder,
column_name as ColumnName,
data_type as TypeName,
coalesce(character_maximum_length,numeric_precision,-1) as Length,
numeric_scale as Scale,
case is_nullable when 'NO' then 0 else 1 end as CanNull,
column_default as DefaultVal,
case when position('nextval' in column_default)>0 then 1 else 0 end as IsIdentity,
case when b.pk_name is null then 0 else 1 end as IsPK,
c.DeText as colcomment
from information_schema.columns
left join (
select pg_attr.attname as colname,pg_constraint.conname as pk_name from pg_constraint
inner join pg_class on pg_constraint.conrelid = pg_class.oid
inner join pg_attribute pg_attr on pg_attr.attrelid = pg_class.oid and pg_attr.attnum = pg_constraint.conkey[1]
inner join pg_type on pg_type.oid = pg_attr.atttypid
where pg_class.relname = '{tableName}' and pg_constraint.contype='p'
) b on b.colname = information_schema.columns.column_name
left join (
select attname,description as DeText from pg_class
left join pg_attribute pg_attr on pg_attr.attrelid= pg_class.oid
left join pg_description pg_desc on pg_desc.objoid = pg_attr.attrelid and pg_desc.objsubid=pg_attr.attnum
where pg_attr.attnum>0 and pg_attr.attrelid=pg_class.oid and pg_class.relname='{tableName}'
)c on c.attname = information_schema.columns.column_name
where table_schema='public' and table_name='{tableName}' order by ordinal_position asc;";
DataTable columnTable = helper.GetDataTable(sql);
for (int c = 0; c < columnTable.Rows.Count; ++c)
{
ColumnInfo column = CreateColumn(columnTable.Rows[c]);
table.ColumnCollection.AddColumn(column);
table.CommentCollection.AddComment(CreateComment(column, table));
}
sql = $"select * from pg_indexes where tablename='{tableName.ToLower()}'";
DataTable indexTable = helper.GetDataTable(sql);
for (int i = 0; i < indexTable.Rows.Count; ++i)
{
table.IndexCollection.AddIndex(CreateIndex(indexTable.Rows[i], table));
}
return(table);
}
public DatabaseInfo CreateDatabaseInfo()
{
DatabaseInfo database = new DatabaseInfo();
using (helper = new PostgreSqlHelper(Connect))
{
string sql = @"SELECT tablename FROM pg_tables
WHERE tablename NOT LIKE 'pg%'
AND tablename NOT LIKE 'sql_%'
ORDER BY tablename;";
DataTable nameTable = helper.GetDataTable(sql);
for (int i = 0; i < nameTable.Rows.Count; ++i)
{
database.AddTable(CreateTable(nameTable.Rows[i][0].ToString()));
}
}
return(database);
}