public void MakeSQLNew()
{
SQLScript.Clear();
string GenName = txtGenName.Text.Trim();
if (!string.IsNullOrEmpty(GenName))
{
int?NewValue = StaticFunctionsClass.ToIntDef(txtGenNewValue.Text.Trim(), null);
int?IncrementValue = StaticFunctionsClass.ToIntDef(txtIncrementValue.Text.Trim(), null);
var sb = new StringBuilder();
sb.Append($@"CREATE SEQUENCE {GenName};{Environment.NewLine}");
if (NewValue != null)
{
string cmd = ((IncrementValue != null) && (IncrementValue.Value > 0))
?$@"ALTER SEQUENCE {txtGenName.Text.Trim()} RESTART WITH {NewValue.Value} INCREMENT BY {IncrementValue.Value};{Environment.NewLine}"
:$@"ALTER SEQUENCE {txtGenName.Text.Trim()} RESTART WITH {NewValue.Value};{Environment.NewLine}";
sb.Append(cmd);
}
sb.Append($@"{SQLPatterns.Commit}{Environment.NewLine}{Environment.NewLine}");
sb.Append($@"COMMENT ON GENERATOR {GenName} IS '{fctGenDescription.Text}';{Environment.NewLine}");
sb.Append($@"{SQLPatterns.Commit}{Environment.NewLine}{Environment.NewLine}");
SQLScript.Add(sb.ToString());
}
else
{
SQLScript.Add("/* Generator name is not defined */");
}
SQLToUI();
}
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 MakeSQLNew()
{
SQLScript.Clear();
//ckPrimary.Visible = true;
StringBuilder sb = new StringBuilder();
/*
* CREATE UNIQUE INDEX TLAGERDEF_IDX1
* ON TLAGERDEF(TLAGER_ID, POSX, POSY, POSZ)
*/
if (ckPrimary.Checked)
{
sb.Append($@"ALTER TABLE {TableName} ADD PRIMARY KEY");
string fieldStr = GetFields();
if (rbSortAscending.Checked)
{
fieldStr += $@" {StaticVariablesClass.ASCStr}";
}
if (rbSortDescending.Checked)
{
fieldStr += $@" {StaticVariablesClass.DECStr}";
}
fieldStr += ";";
sb.Append(fieldStr);
}
else
{
sb.Append($@"{StaticVariablesClass.CREATEStr} ");
if (cbUnique.Checked)
{
sb.Append($@"{StaticVariablesClass.UNIQUEStr} ");
}
if (rbSortAscending.Checked)
{
sb.Append($@"{StaticVariablesClass.ASCStr} ");
}
else if (rbSortDescending.Checked)
{
sb.Append($@"{StaticVariablesClass.DECStr} ");
}
sb.Append("INDEX ");
sb.Append(txtIndexName.Text.Trim());
sb.Append(" ON ");
sb.Append(TableName);
string fieldStr = GetFields();
sb.Append(fieldStr);
}
SQLScript.Add(sb.ToString());
SQLScript.Add($@"{SQLPatterns.Commit}");
SQLToUI();
}
public void MakeNotNullAlter(bool isPrimary)
{
// ALTER TABLE TADRESSEN ALTER POSTFACH TO POSTFACHX
// COMMENT ON COLUMN TADRESSEN.POSTFACHX IS '123'
// ALTER TABLE Customer CHANGE Address Addr char(50); feldnamen ändern
// ALTER TABLE t1 ALTER c_temp TO c1;
// ALTER TABLE t1 ALTER c1 TYPE char(90);
fctSQL.BackColor = SystemColors.Info;
SQLScript.Clear();
gbCollate.Enabled = false;
gbNULLDefault.Enabled = true;
bool FieldNameChanged = (OrgFieldObject.Name != FieldObject.Name);
bool FieldTypeChanged = ((OrgFieldObject.Domain.FieldType != FieldObject.Domain.FieldType) ||
(OrgFieldObject.Domain.Precision != FieldObject.Domain.Precision) ||
(OrgFieldObject.Domain.Scale != FieldObject.Domain.Scale) ||
(OrgFieldObject.Domain.Length != FieldObject.Domain.Length) ||
(OrgFieldObject.Domain.CharSet != FieldObject.Domain.CharSet) ||
(OrgFieldObject.Domain.Collate != FieldObject.Domain.Collate));
bool PrimaryKeyChanged = (OrgFieldObject.IsPrimary != FieldObject.IsPrimary);
bool IsNUllChanged = (OrgFieldObject.Domain.NotNull != FieldObject.Domain.NotNull);
bool PositionChanged = ((OrgFieldObject.Position != FieldObject.Position) && (FieldObject.Position > 0));
bool DefaultChanged = (OrgFieldObject.Domain.DefaultValue != FieldObject.Domain.DefaultValue);
bool DescriptionChanged = (OrgFieldObject.Description != FieldObject.Description);
if (!string.IsNullOrEmpty(txtNULLDefault.Text))
{
SQLScript.Add(Environment.NewLine);
SQLScript.Add(SQLPatterns.UpdateFieldData.Replace(SQLPatterns.TableKey, TableObject.Name).Replace(SQLPatterns.ColumnKey, FieldObject.Name).Replace(SQLPatterns.ValueKey, txtNULLDefault.Text));
SQLScript.Add($@"{Environment.NewLine}{SQLPatterns.Commit}{Environment.NewLine}");
}
// if (IsNUllChanged && (tabControlFieldTypes.SelectedTab == tabPageFieldType)) // && (TableObject.IsNotNull(FieldObject.Name) != cbNotNull.Checked))
{
// alter table tartikel alter datum set not null;
SQLScript.Add(Environment.NewLine);
if (!isPrimary)
{
string cmd1 = SQLPatterns.AlterTableFieldSetNull.Replace(SQLPatterns.TableKey, TableObject.Name).Replace(SQLPatterns.ColumnKey, FieldObject.Name);
SQLScript.Add(cmd1);
SQLScript.Add($@"{Environment.NewLine}{SQLPatterns.Commit}{Environment.NewLine}");
}
string cmd2 = SQLPatterns.AlterTableFieldSetNotNull.Replace(SQLPatterns.TableKey, TableObject.Name).Replace(SQLPatterns.ColumnKey, FieldObject.Name);
SQLScript.Add(cmd2);
SQLScript.Add($@"{Environment.NewLine}{SQLPatterns.Commit}{Environment.NewLine}");
}
SQLToUI();
}
public void MakeSQLNew()
{
SQLScript.Clear();
//ckPrimary.Visible = true;
StringBuilder sb = new StringBuilder();
/*
* CREATE UNIQUE INDEX TLAGERDEF_IDX1
* ON TLAGERDEF(TLAGER_ID, POSX, POSY, POSZ)
*/
if (ckPrimary.Checked)
{
sb.Append($@"ALTER TABLE {_tableObject.Name} ADD PRIMARY KEY");
string fieldStr = GetFields();
if (((eSort)cbSorting.SelectedItem) != eSort.NONE)
{
fieldStr += $@" {EnumHelper.GetDescription((eSort)cbSorting.SelectedItem)}";
}
fieldStr += ";";
sb.Append(fieldStr);
}
else
{
sb.Append($@"{StaticVariablesClass.CREATEStr} ");
if (cbUnique.Checked)
{
sb.Append($@"{StaticVariablesClass.UNIQUEStr} ");
}
if (((eSort)cbSorting.SelectedItem) != eSort.NONE)
{
sb.Append($@"{EnumHelper.GetDescription((eSort)cbSorting.SelectedItem)} ");
}
sb.Append("INDEX ");
sb.Append(txtIndexName.Text.Trim());
sb.Append(" ON ");
sb.Append(_tableObject.Name);
string fieldStr = GetFields();
sb.Append(fieldStr);
sb.Append(";");
}
SQLScript.Add(sb.ToString());
SQLScript.Add($@"{SQLPatterns.Commit}");
SQLToUI();
}
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 bool MakeSQLNew()
{
string active = TriggerObject.Active ? "ACTIVE" : "DEACTIVE";
string triggerTypeStr = EnumHelper.GetDescription(TriggerObject.Type); // StaticVariablesClass.GetTriggerTypeStr(TriggerObject.Type);
SQLScript.Clear();
StringBuilder sb = new StringBuilder();
sb.Append($@"{SQLPatterns.SetTermStart}{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($@"^{Environment.NewLine}");
sb.Append($@"{SQLPatterns.SetTermEnd}{Environment.NewLine}");
SQLScript.Add(sb.ToString());
SQLScript.Add($@"{Environment.NewLine}{SQLPatterns.Commit}");
SQLToUI();
return(true);
}
public void MakeSQLNew(bool force = false)
{
DomainClass Domain = null;
if ((cbDOMAIN.SelectedItem != null) && (cbDOMAIN.Text.Length > 0))
{
Domain = cbDOMAIN.SelectedItem as DomainClass;
}
if (Domain == null)
{
tabControlFld.SelectedTab = tabPageField;
}
bool usefieldtype = tabControlFld.SelectedTab == tabPageField;
SQLScript.Clear();
var sb = new StringBuilder();
sb.Append($@"CREATE TABLE {_tableObject.Name}{Environment.NewLine}({Environment.NewLine} {txtFieldName.Text} ");
if (!usefieldtype)
{
string cmd = (Domain != null)
? $@"{Domain.Name}"
: $@"{cbDOMAIN.Text}";
sb.Append(cmd);
//Anlegen mit domain -> keine collation und charset...
if ((!usefieldtype) && (Domain.FieldType == "VARYING"))
{
sb.Append(" /* domain */ ");
}
if (cbPrimaryKey.Checked)
{
sb.Append(" PRIMARY KEY");
}
cmd = (Domain != null)
? $@"{Environment.NewLine} /* raw:{Domain.RawType} intern:[{Domain.FieldType}] */"
: $@"{Environment.NewLine} /* {GetTypeString(cbTypes.Text, txtLength.Text, txtPrecisionLength.Text, txtScale.Text)} */";
sb.Append(cmd);
}
else
{
//Keine Domain
//sb.Append(GetTypeString(cbTypes.Text, txtLength.Text, txtPrecisionLength.Text, txtScale.Text));
sb.Append(GetTypeString(cbTypes.Text, txtLength.Text, txtPrecisionLength.Text, txtScale.Text));
if (cbTypes.Text == "VARCHAR")
{
//Lege Column mit Datentyp an
if (cbCharSet.Text.Length > 0)
{
sb.Append($@" CHARACTER SET {cbCharSet.Text}");
}
if (cbCollate.Text.Length > 0)
{
sb.Append($@" COLLATE {cbCollate.Text}");
}
}
if (txtDefault.Text.Length > 0)
{
sb.Append($@" DEFAULT {txtDefault.Text.Trim()}");
}
if (cbPrimaryKey.Checked)
{
sb.Append(" PRIMARY KEY");
}
if (cbNotNull.Checked)
{
sb.Append(" NOT NULL");
}
}
sb.Append($@"{Environment.NewLine});");
SQLScript.Add(sb.ToString());
sb.Clear();
//COMMENT ON COLUMN TADRESSEN.TTT IS ' rrrrrrrrrrrrrrrr'
if (fctDescription.Text.Length > 0)
{
sb.Clear();
sb.Append($@"COMMENT ON COLUMN {_tableObject.Name}.{txtFieldName.Text.Trim()} IS '{fctDescription.Text}';");
SQLScript.Add(sb.ToString());
SQLScript.Add(SQLPatterns.Commit);
}
SQLToUI();
}
public void MakeSQLNew()
{
SQLScript.Clear();
//ckPrimary.Visible = true;
StringBuilder sb = new StringBuilder();
/*
* CREATE UNIQUE INDEX TLAGERDEF_IDX1
* ON TLAGERDEF(TLAGER_ID, POSX, POSY, POSZ)
*/
if (ckPrimary.Checked)
{
sb.Append($@"ALTER TABLE {TableName} ADD PRIMARY KEY ");
bool firstdone = false;
string fieldStr = string.Empty;
fieldStr += "(";
foreach (ListViewItem lvi in lvFields.Items)
{
if (firstdone)
{
fieldStr += ",";
}
string st = lvi.Text;
fieldStr += st;
firstdone = true;
}
fieldStr += ")";
fieldStr += ";";
sb.Append(fieldStr);
}
else
{
sb.Append("CREATE ");
if (cbUnique.Checked)
{
sb.Append("UNIQUE ");
}
sb.Append("INDEX ");
sb.Append(txtIndexName.Text.Trim());
sb.Append(" ON ");
sb.Append(TableName);
sb.Append("(");
bool firstdone = false;
foreach (ListViewItem lvi in lvFields.Items)
{
if (firstdone)
{
sb.Append(",");
}
string st = lvi.Text;
sb.Append(st);
firstdone = true;
}
sb.Append(");");
}
SQLScript.Add(sb.ToString());
SQLScript.Add("COMMIT;");
SQLToUI();
}
public void MakeSQOAlter()
{
// ALTER TABLE TADRESSEN ALTER POSTFACH TO POSTFACHX
// COMMENT ON COLUMN TADRESSEN.POSTFACHX IS '123'
// ALTER TABLE Customer CHANGE Address Addr char(50); feldnamen ändern
// ALTER TABLE t1 ALTER c_temp TO c1;
// ALTER TABLE t1 ALTER c1 TYPE char(90);
fctSQL.BackColor = SystemColors.Info;
SQLScript.Clear();
gbCollate.Enabled = false;
gbNULLDefault.Enabled = true;
bool FieldNameChanged = (OrgFieldObject.Name != FieldObject.Name);
bool FieldTypeChanged = ((OrgFieldObject.Domain.FieldType != FieldObject.Domain.FieldType) ||
(OrgFieldObject.Domain.Precision != FieldObject.Domain.Precision) ||
(OrgFieldObject.Domain.Scale != FieldObject.Domain.Scale) ||
(OrgFieldObject.Domain.Length != FieldObject.Domain.Length) ||
(OrgFieldObject.Domain.CharSet != FieldObject.Domain.CharSet) ||
(OrgFieldObject.Domain.Collate != FieldObject.Domain.Collate));
bool PrimaryKeyChanged = (OrgFieldObject.IsPrimary != FieldObject.IsPrimary);
bool IsNUllChanged = (OrgFieldObject.Domain.NotNull != FieldObject.Domain.NotNull);
bool PositionChanged = ((OrgFieldObject.Position != FieldObject.Position) && (FieldObject.Position > 0));
bool DefaultChanged = (OrgFieldObject.Domain.DefaultValue != FieldObject.Domain.DefaultValue);
bool DescriptionChanged = (OrgFieldObject.Description != FieldObject.Description);
if (FieldNameChanged)
{
SQLScript.Add(SQLPatterns.AlterTableRenameField.Replace(SQLPatterns.TableKey, TableObject.Name).Replace(SQLPatterns.ColumnKey, OrgFieldObject.Name).Replace(SQLPatterns.ColumnKey2, FieldObject.Name));
SQLScript.Add($@"{Environment.NewLine}{SQLPatterns.Commit}{Environment.NewLine}{Environment.NewLine}");
}
if (FieldTypeChanged)
{
var sb = new StringBuilder($@"ALTER TABLE {TableObject.Name} ALTER {FieldObject.Name}");
if (tabControlFieldTypes.SelectedTab == tabPageFieldType)
{
var FieldType = GetTypeString((DBDataTypes)cbFieldTypes.SelectedItem, txtLength.Text, txtPrecisionLength.Text, txtScale.Text);
sb.Append($@" TYPE {FieldType}");
//Fieldinfo for domain
Type tp = cbFieldTypes.SelectedItem.GetType();
if (tp == typeof(DBVarchar))
{
int len = StaticFunctionsClass.ToIntDef(txtLength.Text, 0);
string btstr = (len > 0) ? $@" CHARACTER SET {cbCharSet.Text}" : $@" COLLATE {cbCollate.Text}";
sb.Append(btstr);
}
else if (tp == typeof(DBBlob))
{
int len = StaticFunctionsClass.ToIntDef(txtBlobSize.Text, 0);
string btstr = (cbBlobType.Text == "TEXT") ? $@" SUB_TYPE 1" : $@" SUB_TYPE 0";
sb.Append(btstr);
if (len > 0)
{
sb.Append($@" SEGMENT SIZE {len}");
}
}
if (!sb.ToString().EndsWith(";"))
{
sb.Append(";");
}
SQLScript.Add(sb.ToString());
SQLScript.Add($@"{Environment.NewLine}{SQLPatterns.Commit}{Environment.NewLine}");
}
else
{
var Domain = ((cbDOMAIN.SelectedItem != null) && (cbDOMAIN.Text.Length > 0)) ? cbDOMAIN.SelectedItem as DomainClass : null;
if (Domain != null)
{
var FieldType = (Domain != null) ? Domain.Name : cbDOMAIN.Text;
string cmd = (Domain != null)
? $@" {FieldType} /* raw:{Domain.RawType} intern:[{Domain.FieldType}] */"
: $@" {FieldType} /* {GetTypeString((DBDataTypes) cbFieldTypes.SelectedItem, txtLength.Text, txtPrecisionLength.Text, txtScale.Text)} */";
sb.Append(cmd);
//Fieldinfo for domain
if (Domain.FieldType == "VARYING")
{
if ((Domain.CharSet.Length > 0) || (Domain.Collate.Length > 0))
{
sb.Append(" /*");
}
if (Domain.CharSet.Length > 0)
{
sb.Append($@" CHARACTER SET {Domain.CharSet}");
}
if (Domain.Collate.Length > 0)
{
sb.Append($@" COLLATE {Domain.Collate}");
}
if ((Domain.CharSet.Length > 0) || (Domain.Collate.Length > 0))
{
sb.Append(" */");
}
}
SQLScript.Add(sb.ToString());
SQLScript.Add($@"{Environment.NewLine}{SQLPatterns.Commit}{Environment.NewLine}");
}
}
}
if (!string.IsNullOrEmpty(txtNULLDefault.Text))
{
SQLScript.Add(Environment.NewLine);
SQLScript.Add(SQLPatterns.UpdateFieldData.Replace(SQLPatterns.TableKey, TableObject.Name).Replace(SQLPatterns.ColumnKey, FieldObject.Name).Replace(SQLPatterns.ValueKey, txtNULLDefault.Text));
SQLScript.Add($@"{Environment.NewLine}{SQLPatterns.Commit}{Environment.NewLine}");
}
if (IsNUllChanged && (tabControlFieldTypes.SelectedTab == tabPageFieldType)) // && (TableObject.IsNotNull(FieldObject.Name) != cbNotNull.Checked))
{
// alter table tartikel alter datum set not null;
SQLScript.Add(Environment.NewLine);
string cmd = (FieldObject.Domain.NotNull)
? SQLPatterns.AlterTableFieldSetNotNull.Replace(SQLPatterns.TableKey, TableObject.Name).Replace(SQLPatterns.ColumnKey, FieldObject.Name)
: SQLPatterns.AlterTableFieldSetNull.Replace(SQLPatterns.TableKey, TableObject.Name).Replace(SQLPatterns.ColumnKey, FieldObject.Name);
SQLScript.Add(cmd);
SQLScript.Add($@"{Environment.NewLine}{SQLPatterns.Commit}{Environment.NewLine}");
}
// ALTER TABLE EMPLOYEE ADD PRIMARY KEY(SSN)
if (PrimaryKeyChanged)
{
if (FieldObject.IsPrimary)
{
SQLScript.Add(Environment.NewLine);
string cmd = SQLPatterns.AlterTableAddPK.Replace(SQLPatterns.TableKey, TableObject.Name).Replace(SQLPatterns.ColumnKey, FieldObject.Name).Replace(SQLPatterns.PrimaryKey, FieldObject.PK_ConstraintName);
SQLScript.Add(cmd);
SQLScript.Add($@"{Environment.NewLine}{SQLPatterns.Commit}{Environment.NewLine}");
}
else if (!string.IsNullOrEmpty(FieldObject.PK_ConstraintName))
{
SQLScript.Add(Environment.NewLine);
string cmd = SQLPatterns.AlterTableDropPK.Replace(SQLPatterns.TableKey, TableObject.Name).Replace(SQLPatterns.PrimaryKey, FieldObject.PK_ConstraintName);
SQLScript.Add(cmd);
SQLScript.Add($@"{Environment.NewLine}{SQLPatterns.Commit}{Environment.NewLine}");
}
}
if (PositionChanged)
{
string sql = SQLPatterns.PositionSchablone.Replace(SQLPatterns.TableKey, TableObject.Name).Replace(SQLPatterns.ColumnKey, FieldObject.Name).Replace(SQLPatterns.PositionKey, $@"{FieldObject.Position}");
SQLScript.Add(Environment.NewLine);
SQLScript.Add(sql);
SQLScript.Add($@"{Environment.NewLine}{SQLPatterns.Commit}{Environment.NewLine}");
}
if (DefaultChanged)
{
// alter table tartikel alter id set default 1;
string cmd = (txtDefault.Text.Length > 0)
? $@"{SQLPatterns.AlterTableSetDefault.Replace(SQLPatterns.TableKey,TableObject.Name).Replace(SQLPatterns.ColumnKey,FieldObject.Name).Replace(SQLPatterns.DefaultKey,txtDefault.Text)}"
: $@"{SQLPatterns.AlterTableDropDefault.Replace(SQLPatterns.TableKey,TableObject.Name).Replace(SQLPatterns.ColumnKey,FieldObject.Name)}";
SQLScript.Add(Environment.NewLine);
SQLScript.Add(cmd);
SQLScript.Add($@"{Environment.NewLine}{SQLPatterns.Commit}{Environment.NewLine}");
}
if ((DescriptionChanged) && (fctDescription.Text != FieldObject.Description))
{
SQLScript.Add(Environment.NewLine);
SQLScript.Add($@"COMMENT ON COLUMN {TableObject.Name}.{txtFieldName.Text.Trim()} IS '{fctDescription.Text}';");
SQLScript.Add($@"{Environment.NewLine}{SQLPatterns.Commit}{Environment.NewLine}");
}
SQLToUI();
}
public void MakeSQLNew()
{
fctSQL.BackColor = Color.Bisque;
SQLScript.Clear();
gbCollate.Enabled = true;
gbNULLDefault.Enabled = false;
if (string.IsNullOrEmpty(txtFieldName.Text))
{
SQLScript.Add("/* FieldName is undefined */");
SQLToUI();
return;
}
bool FieldTypeChaned = (OrgFieldObject.Name != FieldObject.Name);
bool PrimaryKeyChanged = (OrgFieldObject.IsPrimary != FieldObject.IsPrimary);
bool IsNUllChanged = (OrgFieldObject.Domain.NotNull != FieldObject.Domain.NotNull);
bool PositionChanged = ((OrgFieldObject.Position != FieldObject.Position) && (FieldObject.Position > 0));
bool DefaultChanged = (OrgFieldObject.Domain.DefaultValue != FieldObject.Domain.DefaultValue);
bool DescriptionChanged = (OrgFieldObject.Description != FieldObject.Description);
var sb = new StringBuilder();
string FieldName = txtFieldName.Text;
sb.Append($@"ALTER TABLE {TableObject.Name} ADD {FieldName} ");
if (tabControlFieldTypes.SelectedTab == tabPageFieldType)
{
var FieldType = GetTypeString((DBDataTypes)cbFieldTypes.SelectedItem, txtLength.Text, txtPrecisionLength.Text, txtScale.Text);
sb.Append($@" {FieldType}");
Type tp = cbFieldTypes.SelectedItem.GetType();
if (tp == typeof(DBVarchar))
{
//Lege Column mit Datentyp an
if (cbCharSet.Text.Length > 0)
{
sb.Append($@" CHARACTER SET {cbCharSet.Text}");
}
if (cbCollate.Text.Length > 0)
{
sb.Append($@" COLLATE {cbCollate.Text}");
}
}
else if (cbFieldTypes.Text == "BLOB")
{
// BLOB SUB_TYPE 0 SEGMENT SIZE 80
int st = 0;
if (cbBlobType.Text == "TEXT")
{
st = 1;
}
if (cbBlobType.Text == "BINARY")
{
st = 0;
}
sb.Append($@" SUB_TYPE {st}");
int len = StaticFunctionsClass.ToIntDef(txtBlobSize.Text, 0);
if (len > 0)
{
sb.Append($@" SEGMENT SIZE {len}");
}
}
}
else //Domain
{
var Domain = ((cbDOMAIN.SelectedItem != null) && (cbDOMAIN.Text.Length > 0))
? cbDOMAIN.SelectedItem as DomainClass : null;
FieldName = (Domain != null) ? Domain.Name : cbDOMAIN.Text;
string cmd = (Domain != null)
? $@" {FieldName} /* raw:{Domain.RawType} intern:[{Domain.FieldType}] */"
: $@" {FieldName} /* {GetTypeString((DBDataTypes) cbFieldTypes.SelectedItem, txtLength.Text, txtPrecisionLength.Text, txtScale.Text)} */";
sb.Append(cmd);
if (Domain != null)
{
if (Domain.FieldType == "VARYING")
{
sb.Append(" /* domain */ ");
}
}
}
if (txtDefault.Text.Length > 0)
{
sb.Append($@" DEFAULT '{txtDefault.Text.Trim()}'");
}
if (cbNotNull.Checked)
{
sb.Append(" NOT NULL");
}
sb.Append(";");
SQLScript.Add(sb.ToString());
SQLScript.Add($@"{Environment.NewLine}{SQLPatterns.Commit}{Environment.NewLine}");
if (cbPrimaryKey.Checked)
{
SQLScript.Add($@"{Environment.NewLine}");
string cmd = SQLPatterns.AlterTableAddPK.Replace(SQLPatterns.TableKey, TableObject.Name).Replace(SQLPatterns.ColumnKey, FieldName).Replace(SQLPatterns.PrimaryKey, txtPK.Text);
//SQLScript.Add($@"ALTER TABLE INVOICE ADD CONSTRAINT {txtPK.Text} PRIMARY KEY ({FieldName})");
SQLScript.Add($@"{Environment.NewLine}{SQLPatterns.Commit}{Environment.NewLine}");
}
//COMMENT ON COLUMN TADRESSEN.TTT IS ' rrrrrrrrrrrrrrrr'
if (fctDescription.Text.Length > 0)
{
SQLScript.Add($@"{Environment.NewLine}");
SQLScript.Add(SQLPatterns.AlterTableColumnComment.Replace(SQLPatterns.TableKey, TableObject.Name).Replace(SQLPatterns.ColumnKey, FieldName).Replace(SQLPatterns.DescriptionKey, fctDescription.Text));
SQLScript.Add($@"{Environment.NewLine}{SQLPatterns.Commit}{Environment.NewLine}");
}
int pos = StaticFunctionsClass.ToIntDef(txtPosition.Text, 0);
if (pos > 0)
{
string sql = SQLPatterns.PositionSchablone.Replace(SQLPatterns.TableKey, TableObject.Name).Replace(SQLPatterns.ColumnKey, FieldName).Replace(SQLPatterns.PositionKey, $@"{pos-1}");
SQLScript.Add($@"{Environment.NewLine}");
SQLScript.Add(sql);
SQLScript.Add($@"{Environment.NewLine}{SQLPatterns.Commit}{Environment.NewLine}");
}
SQLToUI();
}