示例#1
0
        private static void Process()
        {
            IMigrationOutput output  = new ConsoleMigrationOutput(_OutputResultsFile, !_NoConsoleLogging);
            SMOScriptOptions options = SMOScriptOptions.CreateFromConfig();
            var migrator             = new TsqlFileMigrator(_FileToProcess, output);

            migrator.ParseFile(options, true);
        }
示例#2
0
        public List <string> GenerateScriptFromSourceServer(ServerConnection sc, List <DatabaseObject> objectList)
        {
            var dtStart = DateTime.Now;
            AsyncUpdateStatus updateStatus = GenScriptAsyncUpdateStatusHandler;
            var          args   = new AsyncNotificationEventArgs(NotificationEventFunctionCode.GenerateScriptFromSQLServer, 0, "", CommonFunc.FormatString("Process started at {0} -- UTC -> {1} ... ", dtStart.ToString(CultureInfo.CurrentUICulture), dtStart.ToUniversalTime().ToString(CultureInfo.CurrentUICulture)) + Environment.NewLine, Color.DarkBlue);
            StreamWriter swTSQL = null;

            SqlSmoObject[] smoTriggers   = null;
            Object         sender        = System.Threading.Thread.CurrentThread;
            var            smoScriptOpts = SMOScriptOptions.CreateFromConfig();

            updateStatus(args);

            sc.Connect();

            var ss  = new Server(sc);
            var db  = ss.Databases[sc.DatabaseName];
            var sdb = new ScriptDatabase();

            sdb.Initialize(ss, db, updateStatus, smoScriptOpts, swTSQL);

            args.DisplayText     = "";
            args.StatusMsg       = "Sorting objects by dependency ...";
            args.PercentComplete = 1;
            updateStatus(args);

            var sorted             = GetSortedObjects(db, objectList);
            var sp                 = new SourceProcessor();
            var tempPathForBCPData = Path.Combine(Path.GetTempPath(), "/BCPData");

            Directory.CreateDirectory(tempPathForBCPData);
            sp.Initialize(sdb, smoScriptOpts, updateStatus, args, tempPathForBCPData);

            //NOTE: This is what does the magic!
            if (sp.Process(sorted, 30))
            {
                return(output);
            }

            if (!Regex.IsMatch(smoScriptOpts.ScriptTableAndOrData, smoScriptOpts.GetLocalizedStringValue("ScriptOptionsTableData"), RegexOptions.IgnoreCase))
            {
                if (sp.Process(DatabaseObjectsTypes.Triggers, smoTriggers, 95))
                {
                    return(output);
                }
            }

            var dtEnd      = DateTime.Now;
            var tsDuration = dtEnd.Subtract(dtStart);
            var sHour      = tsDuration.Minutes == 1 ? " hour, " : " hours, ";
            var sMin       = tsDuration.Minutes == 1 ? " minute and " : " minutes and ";
            var sSecs      = tsDuration.Seconds == 1 ? " second" : " seconds";

            args.StatusMsg    = "Done!";
            args.DisplayColor = Color.DarkCyan;

            if (smoScriptOpts.CheckCompatibility() == 1)
            {
                args.DisplayText = CommonFunc.FormatString(@"No analysis done on script.
Processing finished at {0} -- UTC -> {1}
Total processing time: {2}", dtEnd.ToString(CultureInfo.CurrentUICulture), dtEnd.ToUniversalTime().ToString(CultureInfo.CurrentUICulture), tsDuration.Hours + sHour + tsDuration.Minutes.ToString(CultureInfo.CurrentUICulture) + sMin + tsDuration.Seconds.ToString(CultureInfo.CurrentUICulture) + sSecs);
            }
            else
            {
                args.DisplayText = CommonFunc.FormatString(@"Analysis completed at {0} -- UTC -> {1}
Any issues discovered will be reported above.
Total processing time: {2}", dtEnd.ToString(CultureInfo.CurrentUICulture), dtEnd.ToUniversalTime().ToString(CultureInfo.CurrentUICulture), tsDuration.Hours + sHour + tsDuration.Minutes.ToString(CultureInfo.CurrentUICulture) + sMin + tsDuration.Seconds.ToString(CultureInfo.CurrentUICulture) + sSecs);
            }
            args.PercentComplete = 100;
            updateStatus(args);
            return(output);
        }
示例#3
0
        public void GenerateScriptFromSourceServer(Server sourceServer, Database sourceDatabase, StreamWriter swTSQL, AsyncUpdateStatus updateStatus, string outputDir, ObjectSelector objectFilter)
        {
            _updateStatus   = updateStatus;
            _smoScriptOpts  = SMOScriptOptions.CreateFromConfig();
            _ObjectSelector = objectFilter;

            //if (_smoScriptOpts.TargetServer == _smoScriptOpts.GetLocalizedStringValue("ServerType_AzureSQLDatabase"))
            //{
            //    _ObjectSelector.Assemblies.Script = false;
            //    _ObjectSelector.SchemaCollections.Script = false;
            //}

            DateTime dtStart = DateTime.Now;
            AsyncNotificationEventArgs eventArgs = new AsyncNotificationEventArgs(NotificationEventFunctionCode.GenerateScriptFromSQLServer, 0, "", CommonFunc.FormatString(Properties.Resources.MessageProcessStarted, dtStart.ToString(CultureInfo.CurrentUICulture), dtStart.ToUniversalTime().ToString(CultureInfo.CurrentUICulture)) + Environment.NewLine, Color.DarkBlue);

            _updateStatus(eventArgs);

            ScriptDatabase sdb = new ScriptDatabase();

            sdb.Initialize(sourceServer, sourceDatabase, _updateStatus, _smoScriptOpts, swTSQL, true);

            eventArgs.DisplayText     = "";
            eventArgs.StatusMsg       = Properties.Resources.MessageSorting;
            eventArgs.PercentComplete = 1;
            _updateStatus(eventArgs);

            // Tables, Views, Stored Procedures, and Triggers can all have dependencies.  GetSortedObjects returns
            // these objects in dependency order.

            SqlSmoObject[] smoAssemblies         = GetAssemblies(sourceDatabase);
            SqlSmoObject[] smoPartitionFunctions = GetPartitionFunctions(sourceDatabase);
            SqlSmoObject[] smoPartitionSchemes   = GetPartitionSchemes(sourceDatabase);
            SqlSmoObject[] smoTriggers           = GetTriggers(sourceServer, sourceDatabase);
            SqlSmoObject[] smoRoles      = GetRoles(sourceDatabase);
            SqlSmoObject[] smoSchemas    = GetSchemas(sourceDatabase);
            SqlSmoObject[] smoSchemaCols = GetSchemaCollections(sourceServer, sourceDatabase);
            SqlSmoObject[] smoUDTs       = GetUDTs(sourceDatabase);
            SqlSmoObject[] smoUDTTs      = GetUDTTs(sourceDatabase);
            SqlSmoObject[] smoSynonyms   = GetSynonyms(sourceDatabase);
            SqlSmoObject[] sorted        = GetSortedObjects(sourceDatabase);

            if (Regex.IsMatch(_smoScriptOpts.ScriptDropCreate, _smoScriptOpts.GetLocalizedStringValue("SOSDrop"), RegexOptions.IgnoreCase) ||
                Regex.IsMatch(_smoScriptOpts.ScriptDropCreate, _smoScriptOpts.GetLocalizedStringValue("SOSDropCreate"), RegexOptions.IgnoreCase))
            {
                eventArgs.StatusMsg       = Properties.Resources.MessageCreatingDropScripts;
                eventArgs.PercentComplete = 2;
                _updateStatus(eventArgs);

                ScriptDrops(sorted, smoRoles, smoSchemas, smoSchemaCols, smoUDTs, smoUDTTs, smoAssemblies, smoSynonyms, smoPartitionFunctions, smoPartitionSchemes, sdb);
            }

            if (Regex.IsMatch(_smoScriptOpts.ScriptDropCreate, _smoScriptOpts.GetLocalizedStringValue("SOSCreate"), RegexOptions.IgnoreCase) ||
                Regex.IsMatch(_smoScriptOpts.ScriptDropCreate, _smoScriptOpts.GetLocalizedStringValue("SOSDropCreate"), RegexOptions.IgnoreCase))
            {
                SourceProcessor sp = new SourceProcessor();
                sp.Initialize(sdb, _smoScriptOpts, _updateStatus, eventArgs, outputDir);

                // Roles, Schemas, XML Schema Collections and UDT have no dependencies.  Thus we process one at a time.

                if (!Regex.IsMatch(_smoScriptOpts.ScriptTableAndOrData, _smoScriptOpts.GetLocalizedStringValue("ScriptOptionsTableData"), RegexOptions.IgnoreCase))
                {
                    if (sp.Process(DatabaseObjectsTypes.Assemblies, smoAssemblies, 3))
                    {
                        return;
                    }
                    if (sp.Process(DatabaseObjectsTypes.PartitionFunctions, smoPartitionFunctions, 6))
                    {
                        return;
                    }
                    if (sp.Process(DatabaseObjectsTypes.PartitionSchemes, smoPartitionSchemes, 9))
                    {
                        return;
                    }
                    if (sp.Process(DatabaseObjectsTypes.Roles, smoRoles, 12))
                    {
                        return;
                    }
                    if (sp.Process(DatabaseObjectsTypes.Schemas, smoSchemas, 15))
                    {
                        return;
                    }
                    if (sp.Process(DatabaseObjectsTypes.XMLSchemaCollections, smoSchemaCols, 20))
                    {
                        return;
                    }
                    if (sp.Process(DatabaseObjectsTypes.UserDefinedDataTypes, smoUDTs, 25))
                    {
                        return;
                    }
                    if (sp.Process(DatabaseObjectsTypes.UserDefinedTableTypes, smoUDTTs, 30))
                    {
                        return;
                    }
                    if (sp.Process(DatabaseObjectsTypes.Synonyms, smoSynonyms, 35))
                    {
                        return;
                    }
                }
                if (sp.Process(sorted, 40))
                {
                    return;
                }

                if (!Regex.IsMatch(_smoScriptOpts.ScriptTableAndOrData, _smoScriptOpts.GetLocalizedStringValue("ScriptOptionsTableData"), RegexOptions.IgnoreCase))
                {
                    if (sp.Process(DatabaseObjectsTypes.Triggers, smoTriggers, 95))
                    {
                        return;
                    }
                }
            }

            if (swTSQL != null)
            {
                swTSQL.Flush();
                swTSQL.Close();
            }

            DateTime dtEnd      = DateTime.Now;
            TimeSpan tsDuration = dtEnd.Subtract(dtStart);
            string   sHour      = tsDuration.Minutes == 1 ? Properties.Resources.MessageHour : Properties.Resources.MessageHours;
            string   sMin       = tsDuration.Minutes == 1 ? Properties.Resources.MessageMinute : Properties.Resources.MessageMinutes;
            string   sSecs      = tsDuration.Seconds == 1 ? Properties.Resources.MessageSecond : Properties.Resources.MessageSeconds;

            eventArgs.StatusMsg    = Properties.Resources.Done;
            eventArgs.DisplayColor = Color.DarkCyan;

            if (_smoScriptOpts.CheckCompatibility() == 1)
            {
                eventArgs.DisplayText = CommonFunc.FormatString(Properties.Resources.MessageFinishedNoAnalysis, dtEnd.ToString(CultureInfo.CurrentUICulture), dtEnd.ToUniversalTime().ToString(CultureInfo.CurrentUICulture), tsDuration.Hours + sHour + tsDuration.Minutes.ToString(CultureInfo.CurrentUICulture) + sMin + tsDuration.Seconds.ToString(CultureInfo.CurrentUICulture) + sSecs);
            }
            else
            {
                eventArgs.DisplayText = CommonFunc.FormatString(Properties.Resources.MessageFinishedWithAnalysis, dtEnd.ToString(CultureInfo.CurrentUICulture), dtEnd.ToUniversalTime().ToString(CultureInfo.CurrentUICulture), tsDuration.Hours + sHour + tsDuration.Minutes.ToString(CultureInfo.CurrentUICulture) + sMin + tsDuration.Seconds.ToString(CultureInfo.CurrentUICulture) + sSecs);
            }
            eventArgs.PercentComplete = 100;
            _updateStatus(eventArgs);
        }
示例#4
0
        private List <Azure> ParseFile(string fileToProcess, bool parseFile)
        {
            var startTime   = DateTime.Now;
            var messageList = new List <Azure> {
                new Azure(fileToProcess, MessageTypes.Info, "Beginning Azure Check.")
            };

            var sdb     = new ScriptDatabase();
            var options = SMOScriptOptions.CreateFromConfig();

            sdb.Initialize(_output.StatusUpdateHandler, options, false);

            /****************************************************************/

            var  sqlText = CommonFunc.GetTextFromFile(fileToProcess);
            var  cah     = new CommentAreaHelper();
            long totalCharacterOffset = 0;
            var  bCommentedLine       = false;

            var sqlCmds = new List <string>();

            if (parseFile)
            {
                var sb = new StringBuilder();
                cah.FindCommentAreas(sqlText);
                foreach (var line in cah.Lines)
                {
                    if (line.Equals("GO", StringComparison.OrdinalIgnoreCase))
                    {
                        if (!cah.IsIndexInComments(totalCharacterOffset))
                        {
                            sqlCmds.Add(sb.ToString());
                            sb.Length = 0;
                        }
                        else
                        {
                            sb.Append(line + Environment.NewLine);
                        }
                    }
                    else
                    {
                        sb.Append(line + Environment.NewLine);
                    }
                    totalCharacterOffset += line.Length + cah.CrLf;
                }

                //first we will remove the comments.
                var r         = new Regex(@"(/\*([^*]|[\r\n]|(\*+([^*/]|[\r\n])))*\*+/)|(--.*)");
                var remainder = r.Replace(sb.ToString(), string.Empty).Trim();

                if (remainder.Length > 8)
                {
                    messageList.Add(new Azure(fileToProcess, MessageTypes.Warning, "Final Section of T-SQL didn't finish with a 'GO' command."));
                    sb.Append("GO" + Environment.NewLine);
                    sqlCmds.Add(sb.ToString());
                }
                else //this will re-add the comments and such so that the output files will better match the input files.
                {
                    sqlCmds.Add(sb.ToString());
                }
            }
            else
            {
                sqlCmds.Add(sqlText);
            }

            var numCmds = sqlCmds.Count;

            if (numCmds == 0)
            {
                messageList.Add(new Azure(fileToProcess, MessageTypes.Info, "No data to process"));

                return(messageList);
            }

            var loopCtr = 0;

            messageList.Add(new Azure(fileToProcess, MessageTypes.Info, string.Format("Processing {0} out of {1}", loopCtr, numCmds)));

            totalCharacterOffset = 0;

            foreach (var cmd in sqlCmds)
            {
                ++loopCtr;

                if (cmd.Length == 0 || cmd.Equals(Environment.NewLine))
                {
                    continue;
                }

                foreach (CommentArea ca in cah.CommentAreas)
                {
                    if (ca.Start == totalCharacterOffset && ca.End == totalCharacterOffset + cmd.Length - cah.CrLf - 1) // note that the -1 is to put you at zero based counting
                    {
                        bCommentedLine = true;
                        break;
                    }
                    bCommentedLine = false;
                }
                totalCharacterOffset += cmd.Length + cah.CrLf;

                if (parseFile && !bCommentedLine && !(cmd.StartsWith("/*~") || cmd.StartsWith("~*/")))
                {
                    if (Regex.IsMatch(cmd, "(CREATE|ALTER)\\sPROCEDURE", RegexOptions.IgnoreCase))
                    {
                        sdb.ParseFileTSQLGo(cmd);
                    }
                    else if (Regex.IsMatch(cmd, "(CREATE|ALTER)\\sTABLE", RegexOptions.IgnoreCase))
                    {
                        sdb.ParseFileTable(cmd);
                    }
                    else if (Regex.IsMatch(cmd, "CREATE\\sXML\\sSCHEMA\\sCOLLECTION", RegexOptions.IgnoreCase))
                    {
                        sdb.ParseFileXMLSchemaCollections(cmd);
                    }
                    else if (Regex.IsMatch(cmd, "CREATE\\sTYPE", RegexOptions.IgnoreCase))
                    {
                        sdb.ParseFileUDT(cmd);
                    }
                    else if (Regex.IsMatch(cmd, "CREATE\\s[a-z\\s]*\\sINDEX", RegexOptions.IgnoreCase))
                    {
                        sdb.ParseFileIndex(cmd);
                    }
                    else if (Regex.IsMatch(cmd, "CREATE ROLE", RegexOptions.IgnoreCase))
                    {
                        sdb.ParseFileRole(cmd);
                    }
                    else
                    {
                        sdb.ParseFileTSQLGo(cmd);
                    }
                }
                else
                {
                    sdb.OutputSQLString(cmd, Color.Black);
                }



                if (loopCtr % 20 == 0)
                {
                    messageList.Add(new Azure(fileToProcess, MessageTypes.Info, string.Format("Processing {0} out of {1} - %{2} Complete", loopCtr, numCmds, (int)((loopCtr / (float)numCmds) * 100.0))));
                }
            }

            var endTime = DateTime.Now;

            messageList.Add(new Azure(fileToProcess, MessageTypes.Info, string.Format(
                                          "{1}Total processing time --> {0}",
                                          endTime.Subtract(startTime),
                                          "--~")));

            return(messageList);
        }