public bool MakeSQLAlter()
{
SQLScript.Clear();
var sb = new StringBuilder();
string active = OrgTriggerObject.Active ? "ACTIVE" : "DEACTIVE";
string triggerTypeStr = StaticVariablesClass.GetTriggerTypeStr(OrgTriggerObject.Type);
SQLScript.Add($@"DROP TRIGGER {OrgTriggerObject.Name};");
SQLScript.Add($@"{SQLPatterns.Commit}{Environment.NewLine}");
sb.Append($@"SET TERM ^ ;{Environment.NewLine}");
sb.Append($@"CREATE TRIGGER {TriggerObject.Name} ");
sb.Append($@"FOR {TriggerObject.RelationName}");
sb.Append($@"{Environment.NewLine}{active} ");
sb.Append($@"{triggerTypeStr} ");
sb.Append($@"POSITION {TriggerObject.Sequence}");
sb.Append($@"{Environment.NewLine}{TriggerObject.Source}{Environment.NewLine}");
sb.Append($@"{SQLPatterns.ScriptTerminator}{Environment.NewLine}");
sb.Append($@"{SQLPatterns.SetTermEnd}{Environment.NewLine}");
SQLScript.Add(sb.ToString());
SQLScript.Add($@"{SQLPatterns.Commit}");
SQLToUI();
return(true);
}
public void MakeSQOAlter()
{
SQLScript.Clear();
cbCollate.Enabled = false;
var sb = new StringBuilder();
if (DomainObject.Name != txtName.Text)
{
sb.Append($@"ALTER DOMAIN {DomainObject.Name} TO {txtName.Text};");
sb.Append($@"{SQLPatterns.Commit}{Environment.NewLine}");
}
sb.Append($@"ALTER DOMAIN {txtName.Text} TYPE {StaticVariablesClass.CompleteRawType(cbTypes.Text.Trim(), StaticFunctionsClass.ToIntDef(txtLength.Text.Trim(),0))}");
if (cbCharSet.Enabled)
{
if (cbCharSet.Text != "NONE")
{
sb.Append($@" CHARACTER SET {cbCharSet.Text}");
}
}
sb.AppendLine($@";");
sb.AppendLine($@"{SQLPatterns.Commit}{Environment.NewLine}");
if (txtDefault.Text != DomainObject._defaultValue)
{
string defstr = (cbTypes.Text.IndexOf("CHAR") >= 0) ? $@"'{txtDefault.Text}'" : txtDefault.Text;
string cmd = string.IsNullOrEmpty(txtDefault.Text) ? $@"ALTER DOMAIN {txtName.Text} SET DEFAULT NULL;" : $@"ALTER DOMAIN {txtName.Text} SET DEFAULT {defstr};";
sb.AppendLine(cmd);
sb.AppendLine($@"{SQLPatterns.Commit}{Environment.NewLine}");
}
if (txtCheck.Text.Length > 0)
{
sb.AppendLine($@"ALTER DOMAIN {txtName.Text} DROP CONSTRAINT; /* drop check constraint */");
if (ckCheck.Checked)
{
sb.AppendLine($@"ALTER DOMAIN {txtName.Text} ADD CONSTRAINT CHECK ({txtCheck.Text}); /* adds not null flag to domain */");
}
sb.AppendLine($@"{SQLPatterns.Commit}{Environment.NewLine}");
}
else if (ckCheck.Checked != DomainObject.NotNull)
{
sb.AppendLine($@"ALTER DOMAIN {txtName.Text} DROP CONSTRAINT; /* drop check constraint */");
if (ckCheck.Checked)
{
sb.AppendLine($@"ALTER DOMAIN {txtName.Text} ADD CONSTRAINT CHECK (VALUE IS NOT NULL); /* adds not null flag to domain */");
}
sb.AppendLine($@"{SQLPatterns.Commit}{Environment.NewLine}");
}
if (fctDescription.Text != DomainObject.Description)
{
sb.AppendLine($@"COMMENT ON DOMAIN {txtName.Text} IS '{fctDescription.Text}';");
sb.AppendLine($@"{SQLPatterns.Commit}{Environment.NewLine}");
}
SQLScript.Add(sb.ToString());
SQLToUI();
}
public void SearchType()
{
string rt = StaticVariablesClass.ConvertRawTypeToRawName(DomainObject.FieldType);
int inx = cbTypes.FindStringExact(rt);
cbTypes.SelectedIndex = inx;
gbLength.Enabled = StaticVariablesClass.HasRawLength(cbTypes.Text.Trim());
}
void DomainDataToEdit(DomainClass Domain)
{
txtLength.Text = Domain.Length.ToString();
txtScale.Text = Domain.Scale.ToString();
txtPrecisionLength.Text = Domain.Precision.ToString();
cbDOMAIN.Text = Domain.Name;
cbTypes.Text = StaticVariablesClass.ConvertRawTypeToRawName(Domain.FieldType);
cbCharSet.Text = (Domain.CharSet == null) ? "NONE" : Domain.CharSet;
cbCollate.Text = (Domain.Collate == null) ? "NONE" : Domain.Collate;
}
public void MakeSQLNew()
{
SQLScript.Clear();
cbCollate.Enabled = true;
var sb = new StringBuilder();
sb.Append($@"CREATE DOMAIN {txtName.Text} AS ");
sb.Append($@"{StaticVariablesClass.CompleteRawType(cbTypes.Text.Trim(), StaticFunctionsClass.ToIntDef(txtLength.Text.Trim(), 0))}");
if (cbCharSet.Enabled)
{
if (cbCharSet.Text != "NONE")
{
sb.Append($@" CHARACTER SET {cbCharSet.Text}");
}
}
if (cbCollate.Enabled)
{
if (cbCollate.Text != "NONE")
{
sb.Append($@" COLLATE {cbCollate.Text}");
}
}
if (!string.IsNullOrEmpty(txtDefault.Text))
{
sb.Append($@" DEFAULT '{txtDefault.Text}'");
}
sb.AppendLine($@";");
sb.Append($@"{SQLPatterns.Commit}{Environment.NewLine}");
if (cbNotNull.Checked)
{
sb.AppendLine($@"ALTER DOMAIN {txtName.Text} ADD CONSTRAINT CHECK (VALUE IS NOT NULL); /* adds not null flag to domain */");
sb.AppendLine($@"{SQLPatterns.Commit}{Environment.NewLine}");
}
if (!string.IsNullOrEmpty(fctDescription.Text))
{
sb.AppendLine($@"COMMENT ON DOMAIN {txtName.Text} IS '{fctDescription.Text}';");
sb.Append($@"{SQLPatterns.Commit}{Environment.NewLine}");
}
SQLScript.Add(sb.ToString());
SQLToUI();
}
public void RefreshDomain()
{
string cmd0 = "SELECT RDB$FIELDS.RDB$FIELD_NAME,RDB$FIELDS.RDB$CHARACTER_LENGTH,RDB$FIELDS.RDB$FIELD_SCALE,RDB$FIELD_PRECISION,RDB$FIELDS.RDB$FIELD_TYPE,RDB$FIELDS.RDB$DESCRIPTION,RDB$TYPES.RDB$TYPE_NAME FROM RDB$FIELDS";
string cmd1 = "LEFT JOIN RDB$TYPES ON RDB$TYPES.RDB$TYPE = RDB$FIELDS.RDB$FIELD_TYPE";
string where = "WHERE RDB$FIELDS.RDB$FIELD_NAME = '" + FieldObject.Domain + "' AND RDB$TYPES.RDB$FIELD_NAME = 'RDB$FIELD_TYPE'";
string cmd = cmd0 + " " + cmd1 + " " + where + ";";
ConnectionClass cc = ConnectionPoolClass.Instance().GetConnection("C1");
cc.ShowExceptionMode = MessageLibrary.ShowError.no;
DbDataReader dr = cc.ExecuteQuery(cmd, false);
if (dr != null)
{
if (cc.HasRows())
{
int n = 0;
while (cc.Read())
{
DomainObject = DataClassFactory.GetDataClass(StaticVariablesClass.DomainsKeyStr) as DomainClass;
DomainObject.Name = cc.GetValue(0).ToString().Trim();
DomainObject.Length = StaticFunctionsClass.ToIntDef(cc.GetValue(1).ToString().Trim(), 0);
DomainObject.Scale = StaticFunctionsClass.ToIntDef(cc.GetValue(2).ToString().Trim(), 0);
DomainObject.Precision = StaticFunctionsClass.ToIntDef(cc.GetValue(3).ToString().Trim(), 0);
DomainObject.TypeNumber = StaticFunctionsClass.ToIntDef(cc.GetValue(4).ToString().Trim(), 0);
DomainObject.FieldType = cc.GetValue(6).ToString().Trim();
DomainObject.RawType = StaticVariablesClass.ConvertINTERNALType_TO_SQLType(DomainObject.FieldType, DomainObject.Length);
object ob = cc.GetValue(5);
DomainObject.Description = cc.GetValue(5).ToString().Trim();
/*
* if (!string.IsNullOrEmpty(ob.ToString()))
* {
* byte[] bytetext = (byte[])cc.DBProvider.DataReader[3];
* DomainObject.Description = System.Text.Encoding.UTF8.GetString(bytetext);
* }
*/
n++;
}
}
cc.CloseConnection();
}
}
public void ObjectToEdit(TableFieldClass FieldObject)
{
txtFieldName.Text = FieldObject.Name;
fctDescription.Text = FieldObject.Description;
cbPrimaryKey.Checked = FieldObject.IsPrimary;
txtDefault.Text = FieldObject.Domain.DefaultValue;
cbNotNull.Checked = FieldObject.Domain.NotNull;
txtLength.Text = FieldObject.Domain.Length.ToString();
txtScale.Text = FieldObject.Domain.Scale.ToString();
txtPrecisionLength.Text = FieldObject.Domain.Precision.ToString();
txtPK.Text = string.IsNullOrEmpty(FieldObject.PK_ConstraintName) ? TableObject.primary_constraint?.Name : FieldObject.PK_ConstraintName;
cbDOMAIN.Text = FieldObject.Domain.Name;
cbFieldTypes.Text = StaticVariablesClass.ConvertRawTypeToRawName(FieldObject.Domain.FieldType);
fctDescription.Text = FieldObject.Description;
cbCharSet.Text = (FieldObject.Domain.CharSet == null) ? "NONE" : FieldObject.Domain.CharSet;
cbCollate.Text = (FieldObject.Domain.Collate == null) ? "NONE" : FieldObject.Domain.Collate;
txtPosition.Text = FieldObject.Position.ToString();
}
public void EditToObject()
{
FieldObject = new TableFieldClass();
FieldObject.Name = txtFieldName.Text.Trim();
FieldObject.Position = StaticFunctionsClass.ToIntDef(txtPosition.Text, OrgFieldObject.Position);
//FieldObject.CharSet = cbCharSet.Text;
//FieldObject.Collate = cbCollate.Text;
FieldObject.Domain.Precision = StaticFunctionsClass.ToIntDef(txtPrecisionLength.Text, OrgFieldObject.Domain.Precision);
FieldObject.Domain.Scale = StaticFunctionsClass.ToIntDef(txtScale.Text, OrgFieldObject.Domain.Scale);
FieldObject.Description = fctDescription.Text;
FieldObject.IsPrimary = cbPrimaryKey.Checked;
FieldObject.PK_ConstraintName = txtPK.Text;
FieldObject.Domain.FieldType = StaticVariablesClass.ConvertRawNameToRawType(cbFieldTypes.Text);
FieldObject.Domain.CharSet = cbCharSet.Text.Trim();
FieldObject.Domain.NotNull = cbNotNull.Checked;
FieldObject.Domain.Length = StaticFunctionsClass.ToIntDef(txtLength.Text, OrgFieldObject.Domain.Length);
FieldObject.Domain.DefaultValue = txtDefault.Text;
FieldObject.Domain.Collate = cbCollate.Text;
}
public static string CreateTabelDLL(TableClass tableObject, eCreateMode cmode)
{
//
// CREATE TABLE TACT_TANT(
// ID TABLE_ID NOT NULL /* TABLE_ID = VARCHAR(64) NOT NULL */,
// BEZ VAR10 /* VAR10 = VARCHAR(10) */,
// TANTWORT_ID REF_ID /* REF_ID = VARCHAR(64) */,
// TACTION_ID REF_ID /* REF_ID = VARCHAR(64) */,
// GUELTIG "SMALLINT" DEFAULT 1 NOT NULL /* "SMALLINT" = SMALLINT */,
// STATUS "SMALLINT" DEFAULT 0 NOT NULL /* "SMALLINT" = SMALLINT */,
// TUSERID REF_ID /* REF_ID = VARCHAR(64) */,
// STAMP "TIMESTAMP" DEFAULT CURRENT_TIMESTAMP /* "TIMESTAMP" = TIMESTAMP */
// );
var sb = new StringBuilder();
if (cmode == eCreateMode.recreate)
{
sb.AppendLine($@"RECREATE TABLE {tableObject.Name}");
}
else
{
sb.AppendLine($@"CREATE TABLE {tableObject.Name}");
}
sb.AppendLine("(");
int fc = 0;
foreach (TableFieldClass tfc in tableObject.Fields.Values)
{
fc++;
bool systemdomain = false;
if (tfc.Domain.Name.IndexOf("$") >= 0)
{
systemdomain = true;
}
if (systemdomain)
{
string FType = (tfc.Domain.Length > 0) ? $@"{StaticVariablesClass.ConvertRawTypeToRawName(tfc.Domain.FieldType)}({tfc.Domain.Length})" : $@"{StaticVariablesClass.ConvertRawTypeToRawName(tfc.Domain.FieldType)}";
sb.Append($@" {tfc.Name} {FType} ");
}
else
{
sb.Append($@" {tfc.Name} {tfc.Domain.Name} ");
}
if ((systemdomain) || ((tfc.Domain.FieldType != "VARYING") && ((!tableObject.IsPrimary(tfc.Name)) /*tfc.IS_PRIMARY*/)))
{
if (tfc.Domain.FieldType == "VARYING")
{
if ((tfc.Domain.CharSet != "NONE") && (tfc.Domain.Collate.Length > 0))
{
sb.Append($@" CHARACTER SET {tfc.Domain.CharSet}");
}
//Not NULL
if (tfc.Domain.NotNull)
{
sb.Append(" NOT NULL");
}
if ((tfc.Domain.Collate != "NONE") && (tfc.Domain.Collate.Length > 0))
{
sb.Append($@" COLLATE {tfc.Domain.CharSet}");
}
}
if (tableObject.IsPrimary(tfc.Name))
{
sb.Append(" PRIMARY KEY");
}
}
if (fc < tableObject.Fields.Count)
{
sb.Append(",");
}
//
if (systemdomain)
{
sb.Append($@" /* intern:[{tfc.Domain.FieldType}] domain:{tfc.Domain.Name} */");
}
else
{
sb.Append($@" /* raw:{tfc.Domain.RawType} intern:[{tfc.Domain.FieldType}] */");
}
if ((!systemdomain) && ((tfc.Domain.FieldType == "VARYING") || ((tableObject.IsPrimary(tfc.Name)))))
{
sb.Append(" /*");
if (tfc.Domain.FieldType == "VARYING")
{
if ((tfc.Domain.CharSet != "NONE") && (tfc.Domain.Collate.Length > 0))
{
sb.Append($@" CHARACTER SET {tfc.Domain.CharSet}");
}
if (tfc.Domain.NotNull)
{
sb.Append(" NOT NULL");
}
if ((tfc.Domain.Collate != "NONE") && (tfc.Domain.Collate.Length > 0))
{
sb.Append($@" COLLATE {tfc.Domain.CharSet}");
}
}
sb.Append(" */");
}
sb.Append(Environment.NewLine);
}
sb.AppendLine("");
sb.AppendLine(");");
return(sb.ToString());
}
public void SetControlsEnabled()
{
gbLength.Enabled = StaticVariablesClass.HasRawLength(cbTypes.Text.Trim());
cbCharSet.Enabled = cbTypes.Text.IndexOf("CHAR") >= 0;
cbCollate.Enabled = cbTypes.Text.IndexOf("CHAR") >= 0;
}
