private static string GetIndexMetadata(DatabaseContext dbContext, string tableName)
{
const string caption = "Индекси";
string[] headers = { "№", "Наименование", "Тип", "Колони" };
string[] properties = { "Ordinal", "Name", "Type", "ColumnNames" };
var items = dbContext.Indexes
.FromSql(
$@"SELECT
ROW_NUMBER() OVER (ORDER BY I.name) AS 'ORDINAL_POSITION',
T.name AS 'TABLE_NAME',
I.name AS 'INDEX_NAME',
I.type_desc AS 'TYPE',
AC.name AS 'COLUMN_NAME'
FROM sys.tables AS T
INNER JOIN sys.indexes I
ON T.object_id = I.object_id
INNER JOIN sys.index_columns IC
ON I.object_id = IC.object_id
INNER JOIN sys.all_columns AC
ON T.object_id = AC.object_id AND IC.column_id = AC.column_id
WHERE T.name = {tableName} AND T.is_ms_shipped = 0 AND I.type_desc != 'HEAP'" )
.ToArray();
if (items.Length > 0)
{
return(HTMLFormatter.GenerateTable(caption, headers, properties, items));
}
return(string.Empty);
}
private static string GetReferenceMetadata(DatabaseContext dbContext, string tableName)
{
const string caption = "Референтни връзки";
string[] headers = { "№", "Наименование", "Колона", "Реферирана таблица", "Реферирана колона" };
string[] properties = { "Ordinal", "Name", "ColumnName", "ForeignTableName", "ForeignColumnName" };
var items = dbContext.References
.FromSql(
$@"SELECT
ROW_NUMBER() OVER (ORDER BY KCU1.COLUMN_NAME) AS 'ORDINAL_POSITION',
KCU1.CONSTRAINT_NAME AS 'CONSTRAINT_NAME',
KCU1.TABLE_NAME AS 'TABLE_NAME',
KCU1.COLUMN_NAME AS 'COLUMN_NAME',
KCU2.TABLE_NAME AS 'FOREIGN_TABLE_NAME',
KCU2.COLUMN_NAME AS 'FOREIGN_COLUMN_NAME'
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU1
ON KCU1.CONSTRAINT_NAME = RC.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU2
ON KCU2.CONSTRAINT_NAME = RC.UNIQUE_CONSTRAINT_NAME
WHERE KCU1.CONSTRAINT_SCHEMA = 'dbo' AND KCU1.TABLE_NAME = {tableName}" )
.ToArray();
if (items.Length > 0)
{
return(HTMLFormatter.GenerateTable(caption, headers, properties, items));
}
return(string.Empty);
}
private static string GetIndexMetadata(DatabaseContext dbContext, string tableName)
{
const string caption = "Индекси";
string[] headers = { "№", "Наименование", "Тип", "Колони" };
string[] properties = { "Ordinal", "Name", "Type", "ColumnNames" };
var items = dbContext.Indexes
.FromSql(
$@"select
row_number() OVER (order by indexname) as ordinal_position,
tablename,
indexname,
indexdef
from pg_indexes
where schemaname = 'public' and tablename = {tableName}" )
.ToArray();
if (items.Length > 0)
{
for (var i = 0; i < items.Length; i++)
{
var usingInfo = items[i].Definition.Split(" USING ")[1];
var usingFragments = usingInfo.Split(" (");
items[i].Type = usingFragments[0];
items[i].ColumnNames = usingFragments[1].Split(')')[0];
}
return(HTMLFormatter.GenerateTable(caption, headers, properties, items));
}
return(string.Empty);
}
private static string GetReferenceMetadata(DatabaseContext dbContext, string tableName)
{
const string caption = "Референтни връзки";
string[] headers = { "№", "Наименование", "Колона", "Реферирана таблица", "Реферирана колона" };
string[] properties = { "Ordinal", "Name", "ColumnName", "ForeignTableName", "ForeignColumnName" };
var items = dbContext.References
.FromSql(
$@"select
row_number() over(order by kcu1.column_name) as ordinal_position,
kcu1.constraint_name as constraint_name,
kcu1.table_name as table_name,
kcu1.column_name as column_name,
kcu2.table_name as foreign_table_name,
kcu2.column_name as foreign_column_name
from information_schema.referential_constraints rc
inner join information_schema.key_column_usage kcu1
on kcu1.constraint_name = rc.constraint_name
inner join information_schema.key_column_usage kcu2
on kcu2.constraint_name = rc.unique_constraint_name
where kcu1.constraint_schema = 'public' and kcu1.table_name = {tableName}" )
.ToArray();
if (items.Length > 0)
{
return(HTMLFormatter.GenerateTable(caption, headers, properties, items));
}
return(string.Empty);
}
private static string GetColumnMetadata(DatabaseContext dbContext, string tableName)
{
const string caption = "Колони";
string[] headers = { "№", "Наименование", "Тип на данните", "Mаксимална дължина", "Nullable", "Стойност по подразбиране" };
string[] properties = { "Ordinal", "Name", "Type", "MaxLength", "Nullable", "Default" };
var items = dbContext.Columns
.Where(e => e.TableSchema == "dbo" && e.TableName == tableName)
.OrderBy(e => e.Ordinal)
.ToArray();
for (var i = 0; i < items.Length; i++)
{
if (items[i].Default?.Contains("newid(") == true)
{
items[i].Default = null;
}
}
return(HTMLFormatter.GenerateTable(caption, headers, properties, items));
}
