Exemplo n.º 1
0
        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();
        }
Exemplo n.º 2
0
        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();
        }
Exemplo n.º 3
0
        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();
        }
Exemplo n.º 4
0
        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();
        }
Exemplo n.º 5
0
        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();
        }
Exemplo n.º 6
0
        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();
        }
Exemplo n.º 7
0
        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);
        }
Exemplo n.º 8
0
        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();
        }
Exemplo n.º 9
0
        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();
        }
Exemplo n.º 10
0
        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();
        }
Exemplo n.º 11
0
        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();
        }