Exemplo n.º 1
0
        public IList <IList <string> > EvaluateColumnDataType(TableColumn column, DatabaseObject table)
        {
            IList <IList <string> > result = new List <IList <string> >();

            result.Add(new List <string>()
            {
                "Datatype:",
                column.TypeWithLength
            });
            if (column.IsUserType)
            {
                result.Add(new List <string>()
                {
                    "Suggestion:",
                    "It is a user defined type so optimal data type is not resolved"
                });
            }
            else if (column.DataTypeIsChar() || column.DataTypeIsBinary())
            {
                Tuple <long, long> minMax = _databaseOperations.GetMinMaxColumnValues(column, table);
                result.Add(new List <string>
                {
                    "Max length in values:",
                    minMax.Item2.ToString()
                });
                if (minMax.Item2 < column.Length)
                {
                    result.Add(new List <string>
                    {
                        "Max length which can be stored:",
                        column.Length.ToString(),
                    });
                    result.Add(new List <string>
                    {
                        "Suggestion:",
                        $"Bytes can be lowered from {column.Length} to {Math.Max(minMax.Item2, DataTypeConstants.MIN_LENGTH_IN_BYTES)} => {column.Type}({Math.Max(minMax.Item2, DataTypeConstants.MIN_LENGTH_IN_BYTES)})"
                    });
                }
                else if (column.Length == -1 && minMax.Item2 <= column.MaxLength)
                {
                    result.Add(new List <string>
                    {
                        "Suggestion:",
                        $"Bytes can be lowered from MAX to {minMax.Item2} => {column.Type}({minMax.Item2})"
                    });
                }
                else
                {
                    result.Add(new List <string>
                    {
                        "No suggestion"
                    });
                }
            }
            else if (column.DataTypeIsInteger())
            {
                Tuple <long, long> minMax = _databaseOperations.GetMinMaxColumnValues(column, table);
                Tuple <long, long> minMaxDataType;
                switch (column.Type)
                {
                case DataTypeConstants.TINYINT:
                    minMaxDataType = new Tuple <long, long>(DataTypeConstants.TINYINT_MIN_VALUE, DataTypeConstants.TINYINT_MAX_VALUE);
                    break;

                case DataTypeConstants.SMALLINT:
                    minMaxDataType = new Tuple <long, long>(DataTypeConstants.SMALLINT_MIN_VALUE, DataTypeConstants.SMALLINT_MAX_VALUE);
                    break;

                case DataTypeConstants.INT:
                    minMaxDataType = new Tuple <long, long>(DataTypeConstants.INT_MIN_VALUE, DataTypeConstants.INT_MAX_VALUE);
                    break;

                case DataTypeConstants.BIGINT:
                    minMaxDataType = new Tuple <long, long>(DataTypeConstants.BIGINT_MIN_VALUE, DataTypeConstants.BIGINT_MAX_VALUE);
                    break;

                default:
                    result.Add(new List <string> {
                        "Unknown integer value"
                    });
                    return(result);
                }
                result.Add(new List <string>
                {
                    "Min value in values:",
                    minMax.Item1.ToString()
                });
                result.Add(new List <string>
                {
                    "Max value in values:",
                    minMax.Item2.ToString()
                });
                if (minMax.Item1 >= DataTypeConstants.TINYINT_MIN_VALUE && minMax.Item2 <= DataTypeConstants.TINYINT_MAX_VALUE)
                {
                    result.Add(GenerateSuggestionForIntDataType(column, minMaxDataType, DataTypeConstants.TINYINT, DataTypeConstants.TINYINT_MIN_VALUE, DataTypeConstants.TINYINT_MAX_VALUE));
                }
                else if (minMax.Item1 >= DataTypeConstants.SMALLINT_MIN_VALUE && minMax.Item2 <= DataTypeConstants.SMALLINT_MAX_VALUE)
                {
                    result.Add(GenerateSuggestionForIntDataType(column, minMaxDataType, DataTypeConstants.SMALLINT, DataTypeConstants.SMALLINT_MIN_VALUE, DataTypeConstants.SMALLINT_MAX_VALUE));
                }
                else if (minMax.Item1 >= DataTypeConstants.INT_MIN_VALUE && minMax.Item2 <= DataTypeConstants.INT_MAX_VALUE)
                {
                    result.Add(GenerateSuggestionForIntDataType(column, minMaxDataType, DataTypeConstants.INT, DataTypeConstants.INT_MIN_VALUE, DataTypeConstants.INT_MAX_VALUE));
                }
                else // It must be in range of bigint, no need to check it and no suggestion (cannot be used smaller data type)
                {
                    result.Add(new List <string>
                    {
                        "No suggestion"
                    });
                }
            }
            else
            {
                result.Add(new List <string>
                {
                    "No suggestion for this data type"
                });
            }
            return(result);
        }
Exemplo n.º 2
0
        public bool ArchiveTable(DatabaseObject tableToArchive, IDictionary <string, string> convertorTablenames, bool createTable, string whereConditions, bool useInsertIntoSelect, bool archiveAllEntries, StreamWriter streamToSave)
        {
            bool exportIsNotEmpty = false;
            ISet <DependencyEdge>  cycleEdges;
            IList <DependencyNode> orderedTables = DependencyGraph.GetTopologicallySortedNodes(tableToArchive, true, out cycleEdges);

            if (createTable)
            {
                Server server = new Server(new ServerConnection(Database.Instance.Connection));
                Microsoft.SqlServer.Management.Smo.Database database = server.Databases[Database.Instance.DatabaseName];
                Transfer transfer = new Transfer(database)
                {
                    CopyAllObjects = false,
                    CopyAllUserDefinedDataTypes = true,
                    CopyAllSchemas = true,
                    CopyAllXmlSchemaCollections = true,
                    Options = new ScriptingOptions
                    {
                        FullTextCatalogs    = true,
                        FullTextStopLists   = true,
                        FullTextIndexes     = true,
                        DriAll              = true,
                        ExtendedProperties  = true,
                        Indexes             = true,
                        NonClusteredIndexes = true,
                        Triggers            = true,
                        IncludeIfNotExists  = true
                    }
                };
                foreach (DependencyNode table in orderedTables)
                {
                    transfer.ObjectList.Add(database.Tables[table.DbObject.Name, table.DbObject.Schema]);
                }
                StringBuilder archiveCreateTable = new StringBuilder();
                foreach (string line in transfer.ScriptTransfer())
                {
                    archiveCreateTable.AppendLine(line);
                    archiveCreateTable.AppendLine("GO");
                }
                foreach (DependencyNode table in orderedTables)
                {
                    archiveCreateTable.Replace(table.DbObject.NameWithSchemaBrackets, table.DbObject.NameWithSchema.Replace(table.DbObject.NameWithSchemaBrackets, $"[{convertorTablenames[table.DbObject.NameWithSchema.ToLower()].Replace(".", "].[")}]"));
                    archiveCreateTable.Replace($"TABLE {table.DbObject.NameWithSchema}", $"TABLE {convertorTablenames[table.DbObject.NameWithSchema.ToLower()]}");
                    archiveCreateTable.Replace($"ON {table.DbObject.NameWithSchema}", $"ON {convertorTablenames[table.DbObject.NameWithSchema.ToLower()]}");
                    string oldTablename = table.DbObject.Name;
                    string newTablename = convertorTablenames[table.DbObject.NameWithSchema.ToLower()];
                    newTablename = newTablename.Substring(Math.Min(newTablename.LastIndexOf(".") + 1, newTablename.Length - 1)).Replace("[", "").Replace("]", "");
                    archiveCreateTable.Replace($"_{oldTablename}_", $"_{newTablename}_");
                    archiveCreateTable.Replace($"name=N'{oldTablename}'", $"name=N'{newTablename}'");
                }
                streamToSave.Write(archiveCreateTable.ToString());
                exportIsNotEmpty = true;
                archiveCreateTable.Clear();
            }
            IDictionary <DependencyNode, string> tableSubSelects = new Dictionary <DependencyNode, string>(orderedTables.Count);

            if (!archiveAllEntries)
            {
                foreach (DependencyNode table in orderedTables.Reverse())
                {
                    if (table.DbObject.Equals(tableToArchive))
                    {
                        tableSubSelects.Add(table, $"FROM {table.DbObject.NameWithSchemaBrackets}{(whereConditions.Trim().Length > 0 ? $" WHERE {whereConditions}" : "")}");
                    }
                    foreach (DependencyEdge edge in table.ParentEdges)
                    {
                        DependencyNode parent = edge.Parent;
                        if (parent.DbObject.Equals(tableToArchive))
                        {
                            continue;
                        }
                        if (orderedTables.Contains(parent))
                        {
                            IList <string> parentColumnsConditions = new List <string>();
                            foreach (DependencyColumn column in edge.Columns)
                            {
                                parentColumnsConditions.Add($"{parent.DbObject.NameWithSchemaBrackets}.[{column.ParentColumn}] IN (SELECT DISTINCT {table.DbObject.NameWithSchemaBrackets}.[{column.ChildColumn}] {tableSubSelects[edge.Child]})");
                            }
                            if (!tableSubSelects.ContainsKey(parent))
                            {
                                tableSubSelects[parent] = $"FROM {parent.DbObject.NameWithSchemaBrackets} WHERE ({string.Join(" AND ", parentColumnsConditions)})";
                            }
                            else
                            {
                                tableSubSelects[parent] = tableSubSelects[parent] + $" OR ({string.Join(" AND ", parentColumnsConditions)})";
                            }
                        }
                    }
                }
            }
            else
            {
                foreach (DependencyNode table in orderedTables)
                {
                    if (table.DbObject.Equals(tableToArchive) && whereConditions.Trim().Length > 0)
                    {
                        tableSubSelects.Add(table, $"FROM {table.DbObject.NameWithSchemaBrackets} WHERE {whereConditions}");
                    }
                    else
                    {
                        tableSubSelects.Add(table, $"FROM {table.DbObject.NameWithSchemaBrackets}");
                    }
                }
            }
            IDictionary <DependencyNode, bool> valuesInTables = new Dictionary <DependencyNode, bool>(orderedTables.Count);
            bool valuesGenerated = false;

            foreach (DependencyNode table in orderedTables)
            {
                valuesInTables[table] = (Database.Instance.ExecuteScalarSelect($"SELECT COUNT(*) {tableSubSelects[table]}") ?? 0) > 0;
                if (valuesInTables[table])
                {
                    valuesGenerated = true;
                }
            }
            StringBuilder noCheckConstraints    = new StringBuilder();
            StringBuilder witchCheckConstraints = new StringBuilder();

            foreach (DependencyEdge edge in cycleEdges)
            {
                noCheckConstraints.AppendLine($"ALTER TABLE {edge.Child.DbObject.NameWithSchemaBrackets} NOCHECK CONSTRAINT {edge.Name};");
                witchCheckConstraints.AppendLine($"ALTER TABLE {edge.Child.DbObject.NameWithSchemaBrackets} WITH CHECK CHECK CONSTRAINT {edge.Name};");
            }
            if (valuesGenerated || useInsertIntoSelect)
            {
                streamToSave.Write(noCheckConstraints.ToString());
            }
            foreach (DependencyNode table in orderedTables)
            {
                string tableName = convertorTablenames[table.DbObject.NameWithSchema.ToLower()];
                if (!tableName.Contains("[") && !tableName.Contains("]"))
                {
                    tableName = "[" + tableName.Replace(".", "].[") + "]";
                }
                IList <string> columnsList = _databaseOperations.GetColumnNames(table.DbObject, false);
                string         columns     = "[" + string.Join("], [", columnsList) + "]";
                if (!useInsertIntoSelect || table.DbObject.NameWithSchema == convertorTablenames[table.DbObject.NameWithSchema.ToLower()])
                {
                    if (!valuesInTables[table])
                    {
                        continue;
                    }
                    if (_databaseOperations.HasTableIdentityColumn(table.DbObject))
                    {
                        streamToSave.WriteLine($"SET IDENTITY_INSERT {tableName} ON;");
                    }
                    int values = 0;
                    foreach (string insert in new DatabaseOperations.ValueInsertGetter($"SELECT {columns} {tableSubSelects[table]}"))
                    {
                        if (values % MAX_VALUES_IN_INSERT == 0)
                        {
                            if (values > 0)
                            {
                                streamToSave.WriteLine(";");
                                streamToSave.WriteLine("GO");
                            }
                            streamToSave.Write($"INSERT INTO {tableName} ({columns}) VALUES ");
                        }
                        else
                        {
                            streamToSave.Write(", ");
                        }
                        streamToSave.Write(insert);
                        values++;
                    }
                    if (values > 0)
                    {
                        streamToSave.WriteLine(";");
                        streamToSave.WriteLine("GO");
                    }
                    if (_databaseOperations.HasTableIdentityColumn(table.DbObject))
                    {
                        streamToSave.WriteLine($"SET IDENTITY_INSERT {tableName} OFF;");
                    }
                }
                else
                {
                    exportIsNotEmpty = true;
                    if (_databaseOperations.HasTableIdentityColumn(table.DbObject))
                    {
                        streamToSave.WriteLine($"SET IDENTITY_INSERT {tableName} ON;");
                    }
                    streamToSave.WriteLine($"INSERT INTO {tableName} ({columns}) SELECT * {tableSubSelects[table]};");
                }
            }
            if (valuesGenerated || useInsertIntoSelect)
            {
                exportIsNotEmpty = true;
                streamToSave.Write(witchCheckConstraints.ToString());
            }
            streamToSave.Flush();
            return(exportIsNotEmpty);
        }
Exemplo n.º 3
0
        public string DeleteEntriesFromTable(DatabaseObject tableWhereDelete, string whereConditions, IDictionary <DB.ForeignKey, DB.ForeignKey.DeleteActions> foreignKeyActions)
        {
            StringBuilder          delete        = new StringBuilder();
            DependencyNode         startTable    = DependencyGraph[tableWhereDelete];
            IList <DependencyNode> orderedTables = new List <DependencyNode>();
            IDictionary <DependencyNode, IList <DependencyEdge> > childEdges = new Dictionary <DependencyNode, IList <DependencyEdge> >();

            childEdges[startTable] = new List <DependencyEdge>();
            foreach (DependencyNode table in DependencyGraph.GetDescendants(tableWhereDelete))
            {
                childEdges[table] = new List <DependencyEdge>();
            }
            ISet <DependencyNode> processing = new HashSet <DependencyNode>();
            ISet <DependencyNode> processed  = new HashSet <DependencyNode>();

            ProccessDeleteSubhierarchy(startTable, null, orderedTables, childEdges, foreignKeyActions, processing, processed);
            IList <string> queries = new List <string>();
            IDictionary <DependencyNode, string> tableSubSelects = new Dictionary <DependencyNode, string>(orderedTables.Count);

            foreach (DependencyNode table in orderedTables.Reverse())
            {
                if (table.DbObject.Equals(tableWhereDelete))
                {
                    tableSubSelects.Add(table, $"{(whereConditions.Trim().Length > 0 ? $" WHERE {whereConditions}" : "")}");
                    queries.Add($"DELETE FROM {tableWhereDelete.NameWithSchemaBrackets} {tableSubSelects[table]}");
                }
                foreach (DependencyEdge edge in childEdges[table])
                {
                    DependencyNode child = edge.Child;
                    IList <string> childColumnsConditions = new List <string>();
                    foreach (DependencyColumn column in edge.Columns)
                    {
                        childColumnsConditions.Add($"{child.DbObject.NameWithSchemaBrackets}.[{column.ChildColumn}] IN (SELECT DISTINCT {table.DbObject.NameWithSchemaBrackets}.[{column.ParentColumn}] FROM {edge.Parent.DbObject.NameWithSchemaBrackets} {tableSubSelects[edge.Parent]})");
                    }
                    if (!tableSubSelects.ContainsKey(child))
                    {
                        tableSubSelects[child] = $"WHERE ({string.Join(" OR ", childColumnsConditions)})";
                    }
                    else if (!child.DbObject.Equals(tableWhereDelete))
                    {
                        tableSubSelects[child] = tableSubSelects[child] + $" OR ({string.Join(" OR ", childColumnsConditions)})";
                    }
                    if (foreignKeyActions[edge.ForeignKey] == DB.ForeignKey.DeleteActions.Cascade)
                    {
                        queries.Add($"DELETE FROM {child.DbObject.NameWithSchemaBrackets} {tableSubSelects[child]}");
                    }
                    else if (foreignKeyActions[edge.ForeignKey] == DB.ForeignKey.DeleteActions.SetNull)
                    {
                        IList <string> setColumns = new List <string>();
                        foreach (DependencyColumn column in edge.Columns)
                        {
                            setColumns.Add($"{child.DbObject.NameWithSchemaBrackets}.[{column.ChildColumn}] = NULL");
                        }
                        queries.Add($"UPDATE {child.DbObject.NameWithSchemaBrackets} SET {string.Join(", ", setColumns)} {tableSubSelects[child]}");
                    }
                    else if (foreignKeyActions[edge.ForeignKey] == DB.ForeignKey.DeleteActions.SetDefault)
                    {
                        IList <string> setColumns = new List <string>();
                        foreach (DependencyColumn column in edge.Columns)
                        {
                            string defaultValue = column.ChildColumn.DefaultValue;
                            if (defaultValue == null)
                            {
                                defaultValue = "NULL";
                            }
                            setColumns.Add($"{child.DbObject.NameWithSchemaBrackets}.[{column.ChildColumn}] = {defaultValue}");
                        }
                        queries.Add($"UPDATE {child.DbObject.NameWithSchemaBrackets} SET {string.Join(", ", setColumns)} {tableSubSelects[child]}");
                    }
                }
            }
            delete.AppendLine("BEGIN TRY").AppendLine("BEGIN TRANSACTION");
            foreach (string query in queries.Reverse())
            {
                delete.Append(query).AppendLine(";");
            }
            delete.AppendLine("COMMIT").AppendLine("END TRY").AppendLine("BEGIN CATCH");
            delete.AppendLine("IF @@TRANCOUNT > 0").AppendLine("ROLLBACK TRAN");
            delete.AppendLine("DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE()");
            delete.AppendLine("DECLARE @ErrorSeverity INT = ERROR_SEVERITY()");
            delete.AppendLine("DECLARE @ErrorState INT = ERROR_STATE()");
            delete.AppendLine("RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState); ");
            delete.AppendLine("END CATCH");
            return(delete.ToString());
        }