public string GetAllTableConstraintsByTypeSystemTables(eDBVersion version, eConstraintType ContraintsType)
{
var sb = new StringBuilder();
sb.Append($@"select ");
sb.Append($@"rc.rdb$constraint_name,");
sb.Append($@"rc.rdb$constraint_type,");
sb.Append($@"rc.rdb$relation_name,");
sb.Append($@"rc.rdb$deferrable,");
sb.Append($@"rc.rdb$initially_deferred,");
sb.Append($@"rc.rdb$index_name,");
sb.Append($@"cc.rdb$trigger_name,");
sb.Append($@"rfc.rdb$const_name_uq,");
sb.Append($@"rfc.rdb$match_option,");
sb.Append($@"rfc.rdb$update_rule,");
sb.Append($@"rfc.rdb$delete_rule,");
sb.Append($@"inx.rdb$field_name,inx.rdb$field_position ");
sb.Append($@"from rdb$relation_constraints rc ");
sb.Append($@"left join rdb$check_constraints cc on cc.rdb$constraint_name = rc.rdb$constraint_name ");
sb.Append($@"left join rdb$ref_constraints rfc on rfc.rdb$constraint_name = rc.rdb$constraint_name ");
sb.Append($@"left join rdb$index_segments inx on inx.rdb$index_name = rc.rdb$index_name ");
sb.Append($@"where rc.rdb$relation_name like '%$%' AND rc.rdb$constraint_type = '{EnumHelper.GetDescription(ContraintsType)}' ");
sb.Append($@"order by rc.rdb$constraint_name,rc.rdb$relation_name, inx.rdb$field_name,inx.rdb$field_position;");
return(sb.ToString());
}
public string GetTableIndicies(eDBVersion version, string tableName)
{
StringBuilder sb = new StringBuilder();
sb.Append("SELECT RDB$INDICES.RDB$INDEX_NAME AS Index_Name ,RDB$INDICES.rdb$unique_flag AS Unique_Flag,RDB$INDICES.rdb$index_inactive AS Inactive_Flag ");
sb.Append("FROM RDB$INDEX_SEGMENTS");
sb.Append("JOIN RDB$INDICES ON RDB$INDICES.RDB$INDEX_NAME = RDB$INDEX_SEGMENTS.RDB$INDEX_NAME ");
sb.Append("LEFT JOIN RDB$RELATION_CONSTRAINTS ON RDB$RELATION_CONSTRAINTS.RDB$INDEX_NAME = RDB$INDEX_SEGMENTS.RDB$INDEX_NAME ");
sb.Append($@"WHERE UPPER(RDB$INDICES.RDB$RELATION_NAME) = '{tableName}' ");
sb.Append("GROUP BY RDB$INDICES.RDB$INDEX_NAME,RDB$INDICES.rdb$index_type,RDB$INDICES.rdb$unique_flag,RDB$INDICES.rdb$index_inactive;");
return(sb.ToString());
}
public string GetTableCheckConstraints(eDBVersion version, string TableName)
{
var sb = new StringBuilder();
sb.Append($@"select rc.rdb$constraint_name, rc.rdb$constraint_type, rc.rdb$relation_name, rc.rdb$deferrable, rc.rdb$initially_deferred, rc.rdb$index_name,");
sb.Append($@"tr.rdb$trigger_name,tr.rdb$trigger_source,rfc.rdb$const_name_uq, rfc.rdb$match_option, rfc.rdb$update_rule, rfc.rdb$delete_rule ");
sb.Append($@"from rdb$relation_constraints rc " + "");
sb.Append($@"left join rdb$check_constraints cc on cc.rdb$constraint_name = rc.rdb$constraint_name ");
sb.Append($@"left join rdb$triggers tr on tr.rdb$trigger_name = cc.rdb$trigger_name ");
sb.Append($@"left join rdb$ref_constraints rfc on rfc.rdb$constraint_name = rc.rdb$constraint_name ");
sb.Append($@"where rc.rdb$relation_name = '{TableName}' and rc.rdb$constraint_name not like '%$%' AND rc.rdb$constraint_type = 'CHECK';");
return(sb.ToString());
}
public string GetIndiciesByName(eDBVersion version, string indexName)
{
StringBuilder sb = new StringBuilder();
sb.Append("SELECT RDB$INDICES.RDB$RELATION_NAME,RDB$INDICES.RDB$INDEX_NAME AS Index_Name,RDB$INDEX_SEGMENTS.RDB$FIELD_NAME AS Field_Name,");
sb.Append("RDB$INDICES.rdb$unique_flag AS Unique_Flag,RDB$INDICES.rdb$index_inactive AS Inactive_Flag,RDB$INDICES.RDB$INDEX_TYPE ");
sb.Append("FROM RDB$INDEX_SEGMENTS ");
sb.Append("JOIN RDB$INDICES ON RDB$INDICES.RDB$INDEX_NAME = RDB$INDEX_SEGMENTS.RDB$INDEX_NAME ");
sb.Append("LEFT JOIN RDB$RELATION_FIELDS ON RDB$RELATION_FIELDS.rdb$field_position = (RDB$INDEX_SEGMENTS.RDB$FIELD_POSITION + 1) AND RDB$INDEX_SEGMENTS.RDB$FIELD_NAME = RDB$RELATION_FIELDS.rdb$field_name ");
sb.Append("LEFT JOIN RDB$RELATION_CONSTRAINTS ON RDB$RELATION_CONSTRAINTS.RDB$INDEX_NAME = RDB$INDEX_SEGMENTS.RDB$INDEX_NAME ");
sb.Append($@"WHERE UPPER(RDB$INDICES.RDB$INDEX_NAME) = '{indexName}';");
return(sb.ToString());
}
public string RefreshNonSystemDomains(eDBVersion version)
{
string cmd = string.Empty;
string cmd0 = "SELECT RDB$FIELDS.RDB$FIELD_NAME,RDB$FIELDS.RDB$CHARACTER_LENGTH,RDB$FIELDS.RDB$FIELD_TYPE,RDB$TYPES.rdb$type_name,RDB$CHARACTER_SETS.RDB$CHARACTER_SET_NAME,RDB$COLLATIONS.RDB$COLLATION_NAME,RDB$FIELDS.RDB$DEFAULT_SOURCE,RDB$FIELDS.RDB$DESCRIPTION FROM RDB$FIELDS";
string cmd1 = "LEFT JOIN RDB$TYPES ON RDB$TYPES.RDB$TYPE = RDB$FIELDS.RDB$FIELD_TYPE";
string cmd7 = "LEFT JOIN RDB$CHARACTER_SETS ON RDB$FIELDS.RDB$CHARACTER_SET_ID = RDB$CHARACTER_SETS.RDB$CHARACTER_SET_ID";
string cmd8 = "LEFT JOIN RDB$COLLATIONS ON RDB$FIELDS.RDB$COLLATION_ID = RDB$COLLATIONS.RDB$COLLATION_ID AND RDB$CHARACTER_SETS.RDB$CHARACTER_SET_ID = RDB$COLLATIONS.RDB$CHARACTER_SET_ID";
string wherestr = "WHERE RDB$TYPES.RDB$FIELD_NAME = 'RDB$FIELD_TYPE' AND RDB$FIELDS.RDB$FIELD_NAME NOT LIKE '%$%'";
cmd = cmd0 + " " + cmd1 + " " + cmd7 + " " + cmd8 + " " + wherestr + ";";
return(cmd);
}
public string GetAllIndicies(eDBVersion version, eTableType tableType)
{
var sb = new StringBuilder();
sb.Append("SELECT RDB$INDICES.RDB$RELATION_NAME, RDB$INDICES.RDB$INDEX_NAME,RDB$INDEX_SEGMENTS.RDB$FIELD_NAME,RDB$INDICES.rdb$unique_flag,RDB$INDICES.rdb$index_inactive,RDB$INDICES.rdb$index_type ");
sb.Append("FROM RDB$INDEX_SEGMENTS JOIN RDB$INDICES ON RDB$INDICES.RDB$INDEX_NAME = RDB$INDEX_SEGMENTS.RDB$INDEX_NAME ");
sb.Append("LEFT JOIN RDB$RELATION_FIELDS ON RDB$RELATION_FIELDS.rdb$field_position = (RDB$INDEX_SEGMENTS.RDB$FIELD_POSITION + 1) AND RDB$INDEX_SEGMENTS.RDB$FIELD_NAME = RDB$RELATION_FIELDS.rdb$field_name ");
sb.Append("LEFT JOIN RDB$RELATION_CONSTRAINTS ON RDB$RELATION_CONSTRAINTS.RDB$INDEX_NAME = RDB$INDEX_SEGMENTS.RDB$INDEX_NAME ");
string str = (tableType == eTableType.system)
? "WHERE RDB$INDICES.RDB$SYSTEM_FLAG > 0 AND RDB$INDICES.RDB$FOREIGN_KEY IS NULL "
: "WHERE RDB$INDICES.RDB$SYSTEM_FLAG = 0 AND RDB$INDICES.RDB$FOREIGN_KEY IS NULL ";
sb.Append(str);
sb.Append("GROUP BY RDB$INDICES.RDB$RELATION_NAME,RDB$INDICES.RDB$INDEX_NAME,RDB$INDEX_SEGMENTS.RDB$FIELD_NAME,RDB$INDICES.rdb$index_type,RDB$INDICES.rdb$unique_flag,RDB$INDICES.rdb$index_inactive,RDB$INDICES.rdb$index_type ");
sb.Append("ORDER BY RDB$INDICES.RDB$RELATION_NAME, RDB$INDICES.RDB$INDEX_NAME;");
return(sb.ToString());
}
public string GetTableConstraintsByType(eDBVersion version, string ContraintsType, string TableName)
{
var sb = new StringBuilder();
/*
* sb.Append($@"select ");
* sb.Append($@"rc.rdb$constraint_name,");
* sb.Append($@"rc.rdb$constraint_type,");
* sb.Append($@"rc.rdb$relation_name,");
* sb.Append($@"rc.rdb$deferrable,");
* sb.Append($@"rc.rdb$initially_deferred,");
* sb.Append($@"rc.rdb$index_name,");
* sb.Append($@"cc.rdb$trigger_name,");
* sb.Append($@"rfc.rdb$const_name_uq,");
* sb.Append($@"rfc.rdb$match_option,");
* sb.Append($@"rfc.rdb$update_rule,");
* sb.Append($@"rfc.rdb$delete_rule ");
* sb.Append($@"from rdb$relation_constraints rc ");
* sb.Append($@"LEFT JOIN rdb$check_constraints cc ON cc.rdb$constraint_name = rc.rdb$constraint_name ");
* sb.Append($@"LEFT JOIN rdb$ref_constraints rfc ON rfc.rdb$constraint_name = rc.rdb$constraint_name ");
* sb.Append($@"where rc.rdb$relation_name = '{TableName}' and rc.rdb$constraint_name NOT LIKE '%$%' AND rc.rdb$constraint_type = '{ContraintsType}';");
*/
sb.Append($@"select ");
sb.Append($@"rc.rdb$constraint_name,");
sb.Append($@"rc.rdb$constraint_type,");
sb.Append($@"rc.rdb$relation_name,");
sb.Append($@"rc.rdb$deferrable,");
sb.Append($@"rc.rdb$initially_deferred,");
sb.Append($@"rc.rdb$index_name,");
sb.Append($@"cc.rdb$trigger_name,");
sb.Append($@"rfc.rdb$const_name_uq, rfc.rdb$match_option, rfc.rdb$update_rule, rfc.rdb$delete_rule,");
sb.Append($@"inx.rdb$field_name,inx.rdb$field_position ");
sb.Append($@"from rdb$relation_constraints rc ");
sb.Append($@"LEFT JOIN rdb$check_constraints cc ON cc.rdb$constraint_name = rc.rdb$constraint_name ");
sb.Append($@"LEFT JOIN rdb$ref_constraints rfc ON rfc.rdb$constraint_name = rc.rdb$constraint_name ");
sb.Append($@"LEFT JOIN rdb$index_segments inx ON inx.rdb$index_name = rc.rdb$index_name ");
sb.Append($@"where rc.rdb$relation_name = '{TableName}' and rc.rdb$constraint_name NOT LIKE '%$%' AND rc.rdb$constraint_type = '{ContraintsType}' ");
sb.Append($@"order by rc.rdb$constraint_name,rc.rdb$relation_name, inx.rdb$field_name,inx.rdb$field_position;");
return(sb.ToString());
}
public string GetConstraintsByTypeNotNull(eDBVersion version)
{
var sb = new StringBuilder();
sb.Append($@"select ");
sb.Append($@"rc.rdb$constraint_name,");
sb.Append($@"rc.rdb$constraint_type,");
sb.Append($@"rc.rdb$relation_name,");
sb.Append($@"rc.rdb$deferrable,");
sb.Append($@"rc.rdb$initially_deferred,");
sb.Append($@"rc.rdb$index_name,");
sb.Append($@"cc.rdb$trigger_name,");
sb.Append($@"rfc.rdb$const_name_uq,");
sb.Append($@"rfc.rdb$match_option,");
sb.Append($@"rfc.rdb$update_rule,");
sb.Append($@"rfc.rdb$delete_rule ");
sb.Append($@"from rdb$relation_constraints rc ");
sb.Append($@"left join rdb$check_constraints cc on cc.rdb$constraint_name = rc.rdb$constraint_name ");
sb.Append($@"left join rdb$ref_constraints rfc on rfc.rdb$constraint_name = rc.rdb$constraint_name ");
sb.Append($@"where rc.rdb$constraint_name not like '%$%' AND rc.rdb$constraint_type = 'NOT NULL';");
return(sb.ToString());
}
public string GetConstraintsByTypePrimaryKey(eDBVersion version)
{
var sb = new StringBuilder();
sb.Append($@"select ");
sb.Append($@"rc.rdb$constraint_name,");
sb.Append($@"rc.rdb$constraint_type,");
sb.Append($@"rc.rdb$relation_name,");
sb.Append($@"rc.rdb$deferrable,");
sb.Append($@"rc.rdb$initially_deferred,");
sb.Append($@"rc.rdb$index_name,");
sb.Append($@"ins.rdb$field_name,");
sb.Append($@"rfc.rdb$const_name_uq,");
sb.Append($@"rfc.rdb$match_option,");
sb.Append($@"rfc.rdb$update_rule,");
sb.Append($@"rfc.rdb$delete_rule ");
sb.Append($@"from rdb$relation_constraints rc ");
sb.Append($@"left join rdb$check_constraints cc on cc.rdb$constraint_name = rc.rdb$constraint_name ");
sb.Append($@"left join rdb$ref_constraints rfc on rfc.rdb$constraint_name = rc.rdb$constraint_name ");
sb.Append($@"left join rdb$index_segments ins on ins.rdb$index_name = rc.rdb$index_name ");
sb.Append($@"where rc.rdb$constraint_name not like '%$%' AND rc.rdb$constraint_type = 'PRIMARY KEY';");
return(sb.ToString());
}
public string GetConstraintsByTypeUnique(eDBVersion version)
{
var sb = new StringBuilder();
sb.Append($@"select ");
sb.Append($@"rc.rdb$constraint_name,");
sb.Append($@"rc.rdb$constraint_type,");
sb.Append($@"rc.rdb$relation_name,");
sb.Append($@"rc.rdb$deferrable,");
sb.Append($@"rc.rdb$initially_deferred,");
sb.Append($@"rc.rdb$index_name,");
sb.Append($@"ins.rdb$field_name,");
sb.Append($@"rfc.rdb$const_name_uq,");
sb.Append($@"rfc.rdb$match_option,");
sb.Append($@"rfc.rdb$update_rule,");
sb.Append($@"rfc.rdb$delete_rule ");
sb.Append($@"from rdb$relation_constraints rc ");
sb.Append($@"LEFT JOIN rdb$check_constraints cc ON cc.rdb$constraint_name = rc.rdb$constraint_name ");
sb.Append($@"LEFT JOIN rdb$ref_constraints rfc ON rfc.rdb$constraint_name = rc.rdb$constraint_name ");
sb.Append($@"LEFT JOIN rdb$index_segments ins ON ins.rdb$index_name = rc.rdb$index_name ");
sb.Append($@"where rc.rdb$constraint_name NOT LIKE '%$%' AND rc.rdb$constraint_type = 'UNIQUE';");
return(sb.ToString());
}
public string GetAllTableCheckConstraintsSystem(eDBVersion version)
{
var sb = new StringBuilder();
sb.Append($@"select ");
sb.Append($@"rc.rdb$constraint_name,");
sb.Append($@"rc.rdb$constraint_type,");
sb.Append($@"rc.rdb$relation_name,");
sb.Append($@"rc.rdb$deferrable,");
sb.Append($@"rc.rdb$initially_deferred,");
sb.Append($@"rc.rdb$index_name,");
sb.Append($@"tr.rdb$trigger_name,");
sb.Append($@"tr.rdb$trigger_source,");
sb.Append($@"rfc.rdb$const_name_uq,");
sb.Append($@"rfc.rdb$match_option,");
sb.Append($@"rfc.rdb$update_rule,");
sb.Append($@"rfc.rdb$delete_rule ");
sb.Append($@"from rdb$relation_constraints rc ");
sb.Append($@"LEFT JOIN rdb$check_constraints cc ON cc.rdb$constraint_name = rc.rdb$constraint_name ");
sb.Append($@"LEFT JOIN rdb$triggers tr ON tr.rdb$trigger_name = cc.rdb$trigger_name ");
sb.Append($@"LEFT JOIN rdb$ref_constraints rfc ON rfc.rdb$constraint_name = rc.rdb$constraint_name ");
sb.Append($@"where rc.rdb$relation_name like '%$%' AND rc.rdb$constraint_type = 'CHECK';");
return(sb.ToString());
}
