Exemple #1
0
        public List <VerificationMessage> ApplyRule(Package package, Models.Manifest manifest)
        {
            var r = new List <VerificationMessage>();

            //Trace.WriteLine("SQL Scanner - started", "Information");

            try
            {
                if (manifest.HasSqlScripts)
                {
                    Stopwatch sw = Stopwatch.StartNew();

                    DotNetNukeVersionInfo highestAzureCompatableVersion = null;
                    var majorVersion = DotNetNukeMajorVersions.DotNetNukeVersionList();

                    foreach (var dotNetNukeVersionInfo in majorVersion)
                    {
                        if (DotNetNukeVersions.ConvertNameToRank(package.MinDotNetNukeVersion) <=
                            DotNetNukeVersions.ConvertNameToRank(dotNetNukeVersionInfo.Name))
                        {
                            var databaseName = Convert.ToBase64String(Guid.NewGuid().ToByteArray())
                                               .Substring(0, 22)
                                               .Replace("/", "_")
                                               .Replace("+", "-");

                            try
                            {
                                //create database
                                //Trace.WriteLine("SQL Scanner - creating database: " + databaseName, "Information");
                                Database.CreateDatabase(databaseName);

                                //create user for the database
                                //Trace.WriteLine("SQL Scanner - creating database user.", "Information");
                                User.CreateDatabaseAdmin(databaseName);

                                //install the base version script
                                //Trace.WriteLine("SQL Scanner - install base DNN db objects.", "Information");
                                DNNBase.InstallBaseDNNScripts(databaseName, dotNetNukeVersionInfo);

                                //list and record all DB objects
                                //Trace.WriteLine("SQL Scanner - build list of base db objects.", "Information");
                                var objectListPre = Objects.GetDatabaseObjects(databaseName);

                                //install extension scripts
                                //Trace.WriteLine("SQL Scanner - install extension.", "Information");
                                foreach (var sqlScriptFile in manifest.InstallScripts)
                                {
                                    if (!File.Exists(sqlScriptFile.TempFilePath) || sqlScriptFile.IsUnInstall)
                                    {
                                        continue;
                                    }

                                    try
                                    {
                                        Script.Execute(databaseName, File.ReadAllText(sqlScriptFile.TempFilePath));
                                    }
                                    catch (Exception ex)
                                    {
                                        Trace.TraceError("SQL Scanner - There was a problem installing: " + sqlScriptFile.Name);

                                        var error = ex.InnerException.Message;
                                        r.Add(new VerificationMessage
                                        {
                                            Message =
                                                "While testing against " + dotNetNukeVersionInfo.Name + " " +
                                                sqlScriptFile.Name + " returned an error: " +
                                                Common.CleanError(error),
                                            MessageType = MessageTypes.Error,
                                            MessageId   = new Guid("b25d95e3-06d0-4241-9729-96f85cfddcbf"),
                                            Rule        = GetType().ToString()
                                        });
                                    }
                                }

                                //scan and record all objects created or updated after we installed the extension.
                                //Trace.WriteLine("SQL Scanner - Get new list of db objects post install.", "Information");
                                var objectListPost = Objects.GetDatabaseObjects(databaseName);

                                //check to see if any core objects were modified.
                                //Trace.WriteLine("SQL Scanner - finding objects that might have been updated.", "Information");
                                foreach (var o1 in objectListPre)
                                {
                                    foreach (var o2 in objectListPost)
                                    {
                                        if (o1.ObjectId == o2.ObjectId && o1.ModifyDate != o2.ModifyDate)
                                        {
                                            r.Add(new VerificationMessage()
                                            {
                                                Message =
                                                    "While testing against " + dotNetNukeVersionInfo.Name +
                                                    " it was detected that a DotNetNuke core SQL object (" + o1.Name +
                                                    ") was modified by this extension.",
                                                MessageType = MessageTypes.Warning,
                                                MessageId   = new Guid("50072188-f3c3-4ae0-aaed-4f3d22162e9b"),
                                                Rule        = GetType().ToString()
                                            });
                                        }
                                    }
                                }

                                var newObjectList = new List <DatabaseObject>();

                                foreach (var o1 in objectListPost)
                                {
                                    var foundMatch = false;

                                    foreach (var o2 in objectListPre)
                                    {
                                        if (o1.ObjectId == o2.ObjectId)
                                        {
                                            foundMatch = true;
                                        }
                                    }

                                    if (!foundMatch)
                                    {
                                        newObjectList.Add(o1);
                                    }
                                }

                                //check for objects not correctly using {databaseOwner} & {objectQualifier}
                                //Trace.WriteLine("SQL Scanner - Scanning for objects that might not correctly be using dbo & object qualifier tokens.", "Information");
                                foreach (var databaseObject in newObjectList)
                                {
                                    if (databaseObject.SchemaId != 5 && databaseObject.Type.Trim() != "IT")
                                    {
                                        r.Add(new VerificationMessage()
                                        {
                                            Message =
                                                "While testing against " + dotNetNukeVersionInfo.Name +
                                                " It appears the extension has created a database object using the incorrect schema: " +
                                                databaseObject.Name,
                                            MessageType = MessageTypes.Error,
                                            MessageId   = new Guid("bd4ca33b-aa33-4d5f-9468-8d8f7491bb9a"),
                                            Rule        = GetType().ToString()
                                        });
                                    }

                                    switch (databaseObject.Type.Trim())
                                    {
                                    case "UQ":
                                        if (!databaseObject.Name.StartsWith("IX_TestQualifier_") && !databaseObject.Name.StartsWith("IX__TestQual"))
                                        {
                                            if (databaseObject.Name.Contains("TestQual"))
                                            {
                                                r.Add(new VerificationMessage()
                                                {
                                                    Message =
                                                        "While testing against " + dotNetNukeVersionInfo.Name +
                                                        " It appears the extension has created a unique constraint using an non-traditional naming convention it should be IX_{objectQualifier}ObjectName: " +
                                                        databaseObject.Name,
                                                    MessageType = MessageTypes.Warning,
                                                    MessageId   = new Guid("fc6b8fa6-2863-4bdf-a55c-a84e8165a57a"),
                                                    Rule        = GetType().ToString()
                                                });
                                            }
                                            else
                                            {
                                                r.Add(new VerificationMessage()
                                                {
                                                    Message =
                                                        "While testing against " + dotNetNukeVersionInfo.Name +
                                                        " It appears the extension has created a unique constraint which is missing an {objectQualifier} token: " +
                                                        databaseObject.Name,
                                                    MessageType = MessageTypes.Warning,
                                                    MessageId   = new Guid("91b12382-7556-42d0-8772-fb91873232a5"),
                                                    Rule        = GetType().ToString()
                                                });
                                            }
                                        }
                                        break;

                                    case "PK":
                                        if (!databaseObject.Name.StartsWith("PK_TestQualifier_") && !databaseObject.Name.StartsWith("PK__TestQual"))
                                        {
                                            if (databaseObject.Name.Contains("TestQual"))
                                            {
                                                r.Add(new VerificationMessage()
                                                {
                                                    Message =
                                                        "While testing against " + dotNetNukeVersionInfo.Name +
                                                        " It appears the extension has created a primary key using an non-traditional naming convention it should be PK_{objectQualifier}ObjectName: " +
                                                        databaseObject.Name,
                                                    MessageType = MessageTypes.Warning,
                                                    MessageId   = new Guid("e0bda944-44ab-4338-abb3-8a7bc37c42ee"),
                                                    Rule        = GetType().ToString()
                                                });
                                            }
                                            else
                                            {
                                                r.Add(new VerificationMessage()
                                                {
                                                    Message =
                                                        "While testing against " + dotNetNukeVersionInfo.Name +
                                                        " It appears the extension has created a primary key which is missing an {objectQualifier} token: " +
                                                        databaseObject.Name,
                                                    MessageType = MessageTypes.Warning,
                                                    MessageId   = new Guid("e25340cb-0e36-4217-a79a-b6ea7faac273"),
                                                    Rule        = GetType().ToString()
                                                });
                                            }
                                        }
                                        break;

                                    case "F":
                                        if (!databaseObject.Name.StartsWith("FK_TestQualifier_") && !databaseObject.Name.StartsWith("FK__TestQual"))
                                        {
                                            if (databaseObject.Name.Contains("TestQual"))
                                            {
                                                r.Add(new VerificationMessage()
                                                {
                                                    Message =
                                                        "While testing against " + dotNetNukeVersionInfo.Name +
                                                        " It appears the extension has created a foreign key using an non-traditional naming convention it should be FK_{objectQualifier}ObjectName: " +
                                                        databaseObject.Name,
                                                    MessageType = MessageTypes.Warning,
                                                    MessageId   = new Guid("15c557e1-8a94-4aba-aba3-a952de46f9ba"),
                                                    Rule        = GetType().ToString()
                                                });
                                            }
                                            else
                                            {
                                                r.Add(new VerificationMessage()
                                                {
                                                    Message =
                                                        "While testing against " + dotNetNukeVersionInfo.Name +
                                                        " It appears the extension has created a foreign key which is missing an {objectQualifier} token: " +
                                                        databaseObject.Name,
                                                    MessageType = MessageTypes.Warning,
                                                    MessageId   = new Guid("a5620d04-16cd-467b-8d86-2f400cd04376"),
                                                    Rule        = GetType().ToString()
                                                });
                                            }
                                        }
                                        break;

                                    case "D":
                                        if (!databaseObject.Name.StartsWith("DF_TestQualifier_") && !databaseObject.Name.StartsWith("DF__TestQual"))
                                        {
                                            if (databaseObject.Name.Contains("TestQual"))
                                            {
                                                r.Add(new VerificationMessage()
                                                {
                                                    Message =
                                                        "While testing against " + dotNetNukeVersionInfo.Name +
                                                        " It appears the extension has created a default constraint using an non-traditional naming convention it should be DF_{objectQualifier}ObjectName: " +
                                                        databaseObject.Name,
                                                    MessageType = MessageTypes.Warning,
                                                    MessageId   = new Guid("15c557e1-8a94-4aba-aba3-a952de46f9ba"),
                                                    Rule        = GetType().ToString()
                                                });
                                            }
                                            else
                                            {
                                                r.Add(new VerificationMessage()
                                                {
                                                    Message =
                                                        "While testing against " + dotNetNukeVersionInfo.Name +
                                                        " It appears the extension has created a default constraint which is missing an {objectQualifier} token: " +
                                                        databaseObject.Name,
                                                    MessageType = MessageTypes.Warning,
                                                    MessageId   = new Guid("a44d52d4-0e7f-438c-9ae1-539f7251f756"),
                                                    Rule        = GetType().ToString()
                                                });
                                            }
                                        }
                                        break;

                                    case "IT":
                                        break;

                                    default:
                                        if (!databaseObject.Name.StartsWith("TestQualifier_"))
                                        {
                                            if (databaseObject.Name.Contains("TestQual"))
                                            {
                                                r.Add(new VerificationMessage()
                                                {
                                                    Message =
                                                        "While testing against " + dotNetNukeVersionInfo.Name +
                                                        " It appears the extension has created a database object using an non-traditional naming convention it should be {objectQualifier}ObjectName: " +
                                                        databaseObject.Name,
                                                    MessageType = MessageTypes.Warning,
                                                    MessageId   = new Guid("15c557e1-8a94-4aba-aba3-a952de46f9ba"),
                                                    Rule        = GetType().ToString()
                                                });
                                            }
                                            else
                                            {
                                                r.Add(new VerificationMessage()
                                                {
                                                    Message =
                                                        "While testing against " + dotNetNukeVersionInfo.Name +
                                                        " It appears the extension has created a database object which is missing an {objectQualifier} token: " +
                                                        databaseObject.Name,
                                                    MessageType = MessageTypes.Warning,
                                                    MessageId   = new Guid("ba2855ee-73a5-4787-911b-426b7c0f24ef"),
                                                    Rule        = GetType().ToString()
                                                });
                                            }
                                        }
                                        break;
                                    }
                                }

                                //run sp_refreshsqlmodule on all newly created stored procedures to check for errors.
                                //Trace.WriteLine("SQL Scanner - Running sp_refreshsqlmodule on newly created objects.", "Information");
                                var hasInvalidObjects = false;

                                foreach (var databaseObject in newObjectList)
                                {
                                    if (databaseObject.TypeDesc == "SQL_STORED_PROCEDURE" ||
                                        databaseObject.TypeDesc == "SQL_TRIGGER" ||
                                        databaseObject.TypeDesc == "SQL_SCALAR_FUNCTION" ||
                                        databaseObject.TypeDesc == "SQL_TABLE_VALUED_FUNCTION" ||
                                        //databaseObject.TypeDesc == "SQL_INLINE_TABLE_VALUED_FUNCTION" ||
                                        databaseObject.TypeDesc == "VIEW")
                                    {
                                        var result = Objects.CheckDatabaseObject(databaseName, databaseObject);

                                        foreach (var error in result)
                                        {
                                            hasInvalidObjects = true;
                                            r.Add(new VerificationMessage()
                                            {
                                                Message =
                                                    "While testing against " + dotNetNukeVersionInfo.Name +
                                                    " It appears the extension has created a database object: " +
                                                    databaseObject.Name + " with the following error: " +
                                                    Common.CleanError(error),
                                                MessageType = MessageTypes.Error,
                                                MessageId   = new Guid("297ccdd3-884f-4cbb-85f0-24c343a9ff2a"),
                                                Rule        = GetType().ToString()
                                            });
                                        }
                                    }
                                }

                                //if DNN version > 6.0 and sq_refreshsqlmodule didn't return any errors, run SQL migration wizard
                                //Trace.WriteLine("SQL Scanner - about to run the SQL migration wizard.", "Information");
                                if (!hasInvalidObjects &&
                                    DotNetNukeVersions.ConvertNameToRank(dotNetNukeVersionInfo.Name) >= 60000 &&
                                    newObjectList.Count > 0)
                                {
                                    //Trace.WriteLine("SQL Scanner - This extension is new enough and as passed all scans up till now so it's time to run the migration wizard.", "Information");
                                    var hasAzureError   = false;
                                    var databaseScanner = new DatabaseScanner();
                                    var output          =
                                        databaseScanner.GenerateScriptFromSourceServer(
                                            Common.BuildServerConnection(databaseName), newObjectList);

                                    foreach (var error in output)
                                    {
                                        hasAzureError = true;
                                        r.Add(new VerificationMessage()
                                        {
                                            Message =
                                                "While testing against " + dotNetNukeVersionInfo.Name +
                                                " It appears the extension has an Azure SQL compatibility issue: " +
                                                Common.CleanError(error),
                                            MessageType = MessageTypes.Error,
                                            MessageId   = new Guid("98e44676-fde3-419b-bbf8-bcf785cbbf77"),
                                            Rule        = GetType().ToString()
                                        });
                                    }

                                    if (!hasAzureError)
                                    {
                                        highestAzureCompatableVersion = dotNetNukeVersionInfo;
                                    }
                                }
                            }
                            catch (Exception ex)
                            {
                                //Trace.WriteLine("SQL Scanner - There was a problem with the migration wizard.", "Information");

                                var error = ex.Message;
                                r.Add(new VerificationMessage()
                                {
                                    Message =
                                        "While testing against " + dotNetNukeVersionInfo.Name +
                                        " the following error was returned: " + Common.CleanError(error),
                                    MessageType = MessageTypes.SystemError,
                                    MessageId   = new Guid("2b0143ec-1bb6-43b1-9bae-1b114ec24e41"),
                                    Rule        = GetType().ToString()
                                });
                            }
                            finally
                            {
                                //drop database
                                Database.DropDatabase(databaseName);

                                User.DropDatabaseAdmin(databaseName);
                            }
                        }
                    }


                    //Trace.WriteLine("SQL Scanner - should we run the Azure install/backup/restore process?", "Information");

                    if (highestAzureCompatableVersion != null)
                    {
                        //Trace.WriteLine("SQL Scanner - Yes, we should!", "Information");
                        Stopwatch sw1 = Stopwatch.StartNew();
                        //if SQL migration wizard passes, do the following:
                        //      clear the azure install database
                        try
                        {
                            //Trace.WriteLine("SQL Scanner - clear the Azure database.", "Information");
                            Azure.ClearInstallAndRestoreDatabases();
                        }
                        catch (Exception ex)
                        {
                            var error = ex.InnerException.Message;
                            r.Add(new VerificationMessage()
                            {
                                Message =
                                    "While testing against SQL Azure database version " +
                                    highestAzureCompatableVersion.Name + " the cleanup failed and returned an error: " +
                                    Common.CleanError(error),
                                MessageType = MessageTypes.SystemError,
                                MessageId   = new Guid("fdde5e65-8a39-41c5-8a9e-9d208a8df609"),
                                Rule        = GetType().ToString()
                            });
                        }

                        sw1.Stop();

                        r.Add(new VerificationMessage()
                        {
                            Message =
                                "Processed Azure initial clean up in " + (sw1.ElapsedMilliseconds / 1000) + " seconds",
                            MessageType = MessageTypes.Info,
                            MessageId   = new Guid("1634b15a-a18b-422c-97f4-12f058c5e03b"),
                            Rule        = GetType().ToString()
                        });

                        Stopwatch sw2 = Stopwatch.StartNew();

                        //install the base version scripts
                        try
                        {
                            //Trace.WriteLine("SQL Scanner - set up the base DNN tables in Azure.", "Information");
                            Azure.SetupBaseDNNDatabase(highestAzureCompatableVersion);
                        }
                        catch (Exception ex)
                        {
                            var error = ex.InnerException.Message;
                            r.Add(new VerificationMessage()
                            {
                                Message =
                                    "While testing against SQL Azure database version " +
                                    highestAzureCompatableVersion.Name +
                                    " the base DNN table setup failed and returned an error: " +
                                    Common.CleanError(error),
                                MessageType = MessageTypes.SystemError,
                                MessageId   = new Guid("898526c6-c8df-4d64-bb35-a04e5bc01044"),
                                Rule        = GetType().ToString()
                            });
                        }

                        sw2.Stop();

                        r.Add(new VerificationMessage()
                        {
                            Message     = "Processed Azure initial setup in " + (sw2.ElapsedMilliseconds / 1000) + " seconds",
                            MessageType = MessageTypes.Info,
                            MessageId   = new Guid("f7eeb9b8-6a09-453f-814f-a6a5c126c431"),
                            Rule        = GetType().ToString()
                        });

                        Stopwatch sw3 = Stopwatch.StartNew();

                        //install extension scripts
                        foreach (var sqlScriptFile in manifest.InstallScripts)
                        {
                            if (!File.Exists(sqlScriptFile.TempFilePath) || sqlScriptFile.IsUnInstall)
                            {
                                continue;
                            }

                            try
                            {
                                //Trace.WriteLine("SQL Scanner - Install the extensions script files in Azure.", "Information");
                                Azure.InstallScripts(File.ReadAllText(sqlScriptFile.TempFilePath));
                            }
                            catch (Exception ex)
                            {
                                var error = ex.InnerException.Message;
                                r.Add(new VerificationMessage()
                                {
                                    Message =
                                        "While testing against SQL Azure database version " +
                                        highestAzureCompatableVersion.Name + " " + sqlScriptFile.Name +
                                        " returned an error: " + Common.CleanError(error),
                                    MessageType = MessageTypes.Error,
                                    MessageId   = new Guid("84d55196-a64e-4dbe-b848-2c06ba016869"),
                                    Rule        = GetType().ToString()
                                });
                            }
                        }

                        sw3.Stop();

                        r.Add(new VerificationMessage()
                        {
                            Message     = "Processed Azure module setup in " + (sw3.ElapsedMilliseconds / 1000) + " seconds",
                            MessageType = MessageTypes.Info,
                            MessageId   = new Guid("845b161f-b3c4-4382-8cf5-c105918a891b"),
                            Rule        = GetType().ToString()
                        });

                        Stopwatch sw4 = Stopwatch.StartNew();

                        //back up database
                        try
                        {
                            //Trace.WriteLine("SQL Scanner - Create a backup of the Azure database.", "Information");
                            Azure.CreateDatabaseBackup();
                        }
                        catch (Exception ex)
                        {
                            Trace.TraceError("Error while backing up the azure database.");
                            Trace.TraceError(ex.Message);

                            if (ex.InnerException != null)
                            {
                                Trace.TraceError(ex.InnerException.Message);
                            }

                            var error = ex.InnerException.Message;
                            r.Add(new VerificationMessage()
                            {
                                Message =
                                    "While testing against SQL Azure database version " +
                                    highestAzureCompatableVersion.Name +
                                    " an attempt to backup the database resulted in the following error: " +
                                    Common.CleanError(error),
                                MessageType = MessageTypes.Error,
                                MessageId   = new Guid("03d76ba9-e2cc-4531-a322-3147d7cd1358"),
                                Rule        = GetType().ToString()
                            });
                        }

                        sw4.Stop();

                        r.Add(new VerificationMessage()
                        {
                            Message     = "Processed Azure back up in " + (sw4.ElapsedMilliseconds / 1000) + " seconds",
                            MessageType = MessageTypes.Info,
                            MessageId   = new Guid("382d9b43-a8d0-4a69-8afe-f5f48a82d7dd"),
                            Rule        = GetType().ToString()
                        });

                        Stopwatch sw5 = Stopwatch.StartNew();

                        //restore database
                        try
                        {
                            //Trace.WriteLine("SQL Scanner - Restore the Azure database.", "Information");
                            Azure.RestoreDatabaseBackup();
                        }
                        catch (Exception ex)
                        {
                            var error = ex.InnerException.Message;
                            r.Add(new VerificationMessage()
                            {
                                Message =
                                    "While testing against SQL Azure database version " +
                                    highestAzureCompatableVersion.Name +
                                    " an attempt to restore the database resulted in the following error: " +
                                    Common.CleanError(error),
                                MessageType = MessageTypes.Error,
                                MessageId   = new Guid("50274d0e-bc59-4b50-8e28-d35183a29428"),
                                Rule        = GetType().ToString()
                            });
                        }

                        sw5.Stop();

                        r.Add(new VerificationMessage()
                        {
                            Message     = "Processed Azure restore in " + (sw5.ElapsedMilliseconds / 1000) + " seconds",
                            MessageType = MessageTypes.Info,
                            MessageId   = new Guid("6bbbe53d-4537-4c09-b1b5-c4b4b6fdbdf8"),
                            Rule        = GetType().ToString()
                        });

                        Stopwatch sw6 = Stopwatch.StartNew();

                        //Clean Up
                        try
                        {
                            //Trace.WriteLine("SQL Scanner - perform the final clean up of the Azure databases and backups.", "Information");
                            Azure.ClearInstallAndRestoreDatabases();
                        }
                        catch (Exception ex)
                        {
                            var error = ex.InnerException.Message;
                            r.Add(new VerificationMessage()
                            {
                                Message =
                                    "While testing against SQL Azure database version " +
                                    highestAzureCompatableVersion.Name + " the cleanup failed and returned an error: " +
                                    Common.CleanError(error),
                                MessageType = MessageTypes.SystemError,
                                MessageId   = new Guid("fdde5e65-8a39-41c5-8a9e-9d208a8df609"),
                                Rule        = GetType().ToString()
                            });
                        }

                        sw6.Stop();

                        r.Add(new VerificationMessage()
                        {
                            Message     = "Processed Azure final clean up in " + (sw6.ElapsedMilliseconds / 1000) + " seconds",
                            MessageType = MessageTypes.Info,
                            MessageId   = new Guid("e7da9c33-3ef4-430e-b8be-44188f09d5c7"),
                            Rule        = GetType().ToString()
                        });
                    }

                    sw.Stop();

                    r.Add(new VerificationMessage()
                    {
                        Message     = "Processed SQL in " + (sw.ElapsedMilliseconds / 1000) + " seconds",
                        MessageType = MessageTypes.Info,
                        MessageId   = new Guid("ad338269-f31c-4fe2-b564-4e5b44ac1b6b"),
                        Rule        = GetType().ToString()
                    });
                }
            }
            catch (Exception ex)
            {
                if (ex.InnerException != null)
                {
                    var error = ex.InnerException.Message;
                    r.Add(new VerificationMessage()
                    {
                        Message     = "While testing the SQL scripts, the following error was returned: " + Common.CleanError(error),
                        MessageType = MessageTypes.SystemError,
                        MessageId   = new Guid("97b6a0e6-1508-438e-a988-319e531c411c"),
                        Rule        = GetType().ToString()
                    });
                }
                else
                {
                    var error = ex.Message;
                    r.Add(new VerificationMessage()
                    {
                        Message     = "While testing the SQL scripts, the following error was returned: " + Common.CleanError(error),
                        MessageType = MessageTypes.SystemError,
                        MessageId   = new Guid("5b2ee4ba-ebc0-4230-8504-aa3365acfb0c"),
                        Rule        = GetType().ToString()
                    });
                }
            }

            return(r);
        }