예제 #1
0
        public void Execute(ICollection <DatabaseStoredProcedure> baseProcedures, ICollection <DatabaseStoredProcedure> compareProcedures)
        {
            bool first = false;

            //find new sprocs (in compare, but not in base)
            foreach (var procedure in compareProcedures)
            {
                var name   = procedure.Name;
                var schema = procedure.SchemaOwner;
                var match  = baseProcedures.FirstOrDefault(t => t.Name == name && t.SchemaOwner == schema);
                if (match != null)
                {
                    continue;
                }
                var script = string.Empty;
                if (!first)
                {
                    first = true;
                    //CREATE PROCEDURE cannot be combined with other statements in a batch,
                    //so be preceeded by and terminate with a "GO" (sqlServer) or "/" (Oracle)
                    if (_results.Count > 0)
                    {
                        script += _writer.RunStatements() + Environment.NewLine;
                    }
                }
                script += "-- NEW STORED PROCEDURE " + procedure.Name + Environment.NewLine +
                          _writer.AddProcedure(procedure);
                CreateResult(ResultType.Add, procedure, script);
            }

            //find dropped and existing sprocs
            foreach (var procedure in baseProcedures)
            {
                var name   = procedure.Name;
                var schema = procedure.SchemaOwner;
                var match  = compareProcedures.FirstOrDefault(t => t.Name == name && t.SchemaOwner == schema);
                if (match == null)
                {
                    CreateResult(ResultType.Delete, procedure,
                                 "-- DROP STORED PROCEDURE " + procedure.Name + Environment.NewLine +
                                 _writer.DropProcedure(procedure));
                    continue;
                }
                //sproc may or may not have been changed

                if (procedure.Sql == match.Sql)
                {
                    continue;                             //the same
                }
                //a sanitized comparison
                if (_writer.CompareProcedure(procedure.Sql, match.Sql))
                {
                    continue;
                }

                //in Oracle could be a CREATE OR REPLACE
                CreateResult(ResultType.Change, procedure,
                             "-- ALTER STORED PROCEDURE " + procedure.Name + Environment.NewLine +
                             _writer.DropProcedure(procedure) + Environment.NewLine +
                             _writer.AddProcedure(match));
            }
        }