private static FkDicts GetForeignKeysFromBase()
{
var fkDicts = new FkDicts();
fkDicts.keyDict = new Dictionary <string, Key>();
fkDicts.relationDict = new Dictionary <string, List <Relation> >();
fkDicts.columnDict = new Dictionary <string, List <string> >();
string query = @"SELECT
(detail_relation_constraints.rdb$relation_name) AS table_name,
(detail_index_segments.rdb$field_name) AS field_name,
(master_relation_constraints.rdb$relation_name) AS reference_table,
(master_index_segments.rdb$field_name) AS fk_field
FROM
rdb$relation_constraints detail_relation_constraints
JOIN rdb$index_segments detail_index_segments ON detail_relation_constraints.rdb$index_name = detail_index_segments.rdb$index_name
JOIN rdb$ref_constraints ON detail_relation_constraints.rdb$constraint_name = rdb$ref_constraints.rdb$constraint_name -- Master indeksas
JOIN rdb$relation_constraints master_relation_constraints ON rdb$ref_constraints.rdb$const_name_uq = master_relation_constraints.rdb$constraint_name
JOIN rdb$index_segments master_index_segments ON master_relation_constraints.rdb$index_name = master_index_segments.rdb$index_name
WHERE
detail_relation_constraints.rdb$constraint_type = 'FOREIGN KEY'";
var fkTable = DataBaseConnector.GetTableByQuery(query);
foreach (DataRow row in fkTable.Rows)
{
string table = row[0].ToString().Replace(" ", "");
string column = row[1].ToString().Replace(" ", "");
string refTable = row[2].ToString().Replace(" ", "");
string refColumn = row[3].ToString().Replace(" ", "");
AddValuesToFkDics(table, column, refTable, refColumn, fkDicts);
}
return(fkDicts);
}
static Dictionary <string, List <string> > LoadTableColumnsKeyDict(string query)
{
var dict = new Dictionary <string, List <string> >();
DataTable table = DataBaseConnector.GetTableByQuery(query);
foreach (DataRow row in table.Rows)
{
string tableName = row[0].ToString().Replace(" ", "");
string columnName = row[1].ToString().Replace(" ", "");
AddValueToDict(tableName, columnName, dict);
}
return(dict);
}
public static DataTable GetTableFromBase(string name, string orderCol = null)
{
if (string.IsNullOrWhiteSpace(name) == false)
{
string query = string.Format("select * from {0}", name);
if (orderCol != null)
{
query += string.Format(" order by {0} asc", orderCol);
}
var result = DataBaseConnector.GetTableByQuery(query);
result.TableName = name;
return(result);
}
return(new DataTable());
}
private static Dictionary <string, Dictionary <string, object> > GetCommentsFromBase()
{
string query = @"select
rf.rdb$relation_name,
rf.rdb$field_name,
rf.rdb$description
from rdb$relation_fields rf
where (coalesce(rf.rdb$system_flag, 0) = 0) and rf.rdb$description is not null";
var dict = new Dictionary <string, Dictionary <string, object> >();
DataTable table = DataBaseConnector.GetTableByQuery(query);
foreach (DataRow row in table.Rows)
{
var tableName = row[0].ToString().Replace(" ", "");
var columnName = row[1].ToString().Replace(" ", "");
var comment = row[2].ToString();
AddValueToDict(tableName, columnName, comment, dict);
}
return(dict);
}
private static Dictionary <string, Dictionary <string, object> > GetMaxLendthFromBase()
{
string query = @"select
rf.rdb$relation_name,
rf.rdb$field_name,
case f.rdb$field_type
when 8 then 9
else f.rdb$field_length / 4
end
from rdb$relation_fields rf
join rdb$fields f on (f.rdb$field_name = rf.rdb$field_source)
where (coalesce(rf.rdb$system_flag, 0) = 0)";
var dict = new Dictionary <string, Dictionary <string, object> >();
DataTable table = DataBaseConnector.GetTableByQuery(query);
foreach (DataRow row in table.Rows)
{
var tableName = row[0].ToString().Replace(" ", "");
var columnName = row[1].ToString().Replace(" ", "");
var length = Convert.ToInt32(row[2]);
AddValueToDict(tableName, columnName, length, dict);
}
return(dict);
}
