public InteractiveSqlApplicationTests()
 {
     var sqlExecutor = new SqlExecutor(SqlServerConfiguration.ReadAppSettings());
     var hintGenerator = new HintGenerator();
     m_Application = new InteractiveSqlApplication(m_Logger, sqlExecutor, hintGenerator, CreateCourse());
     m_Application.Start();
 }

        public int RunScriptsToUpdate(Server disposableDbServer, string scriptsFolderPath, string dbname)
        {
            SqlExecutor = new SqlExecutor(disposableDbServer);
            if (!scriptsFolderPath.TrimEnd('/').TrimEnd('\\').EndsWith(FolderName))
            {
                var type = GetType();
                var suffixPos = type.Name.IndexOf("Manager");
                if (suffixPos < 1) {
                    throw new InvalidOperationException("Derived classes of UpdatableManager should have the -Manager suffix as well");
                }
                var scriptType = type.Name.Substring(0, suffixPos);
                throw new ArgumentException(String.Format("scriptsFolderPath must contain {0}s", scriptType),
                    "scriptsFolderPath");
            }

            int count = 0;
            IEnumerable<string> scriptPaths = Directory.GetFileSystemEntries(scriptsFolderPath, "*.sql");
            foreach (var scriptPath in scriptPaths)
            {
                var scriptFilename = Path.GetFileName(scriptPath);

                var scriptContents = File.ReadAllText(scriptPath);

                var scriptContentsWithAlter = ReplaceCreateWithAlter(scriptContents);

                try{
                    Run(scriptContentsWithAlter, dbname, scriptFilename);
                } catch {
                    //maybe ALTER failed because the element is still not there...
                    Run(scriptContents, dbname, scriptFilename);
                }
                count++;
            }
            return count;
        }

 public DisposableDbManager(string dbCreationPath, Server disposableDbServer, string dbNameInVcs)
 {
     _dbCreationPath = dbCreationPath;
     _disposableDbServer = disposableDbServer;
     _sqlExecutor = new SqlExecutor(_disposableDbServer);
     _dbNameInVcs = dbNameInVcs;
 }

        public override void ExecuteCommand()
        {
            var dbServer = ConnectionString.DataSource;
            var masterConnectionString = Util.GetMasterConnectionString(ConnectionString.ConnectionString);

            Log.Info("Listing backups for server '{0}':", dbServer);
            
            using (var sqlConnection = new SqlConnection(masterConnectionString))
            using (var dbExecutor = new SqlExecutor(sqlConnection))
            {
                sqlConnection.Open();

                var dbs = dbExecutor.Query<Db>(
                    "SELECT name FROM sys.databases WHERE name LIKE 'Backup_%' AND state = @state",
                    new { state = Util.OnlineState });

                foreach(var db in dbs)
                {
                    var timestamp = Util.GetDatabaseNameTimestamp(db);
                    var date = Util.GetDateTimeFromTimestamp(timestamp);

                    Log.Info("{0} ({1})", timestamp, date);
                }
            }
        }

 public static bool BackupIsInProgress(SqlExecutor dbExecutor)
 {
     return dbExecutor.Query<Database>(
         "SELECT name, state FROM sys.databases WHERE name LIKE 'Backup_%' AND state = @state",
         new { state = CopyingState })
         .Any();
 }

        public override void ExecuteCommand()
        {
            var dbServer = ConnectionString.DataSource;
            var masterConnectionString = Util.GetMasterConnectionString(ConnectionString.ConnectionString);

            Log.Trace("Deleting old warehouse backups for server '{0}':", dbServer);

            using (var sqlConnection = new SqlConnection(masterConnectionString))
            {
                sqlConnection.Open();

                using (var dbExecutor = new SqlExecutor(sqlConnection))
                {
                    var dbs = dbExecutor.Query<Db>(
                        "SELECT name FROM sys.databases WHERE name LIKE 'WarehouseBackup_%' AND state = @state",
                        new { state = Util.OnlineState });

                    foreach (var db in dbs)
                    {
                        var timestamp = Util.GetDatabaseNameTimestamp(db);
                        var date = Util.GetDateTimeFromTimestamp(timestamp);
                        if (DateTime.UtcNow.Subtract(TimeSpan.FromDays(7)) > date)
                            DeleteDatabaseBackup(db, dbExecutor);
                    }
                }
            }
        }

        private void CreateReportForVSTask()
        {
            string[] VsQuery = new JavaScriptSerializer().Deserialize<string[]>(ReportHelpers.Load(StorageAccount, "VsVersion.json", ContainerName));
            using (var sqlConnection = new SqlConnection(ConnectionString.ConnectionString))
            {
                using (var dbExecutor = new SqlExecutor(sqlConnection))
                {
                    sqlConnection.Open();
                    DateTime date = DateTime.UtcNow.AddDays(-LastNDays);
                    List<VsRequest> requests = new List<VsRequest>();
                    foreach (string each in VsQuery)
                    {
                        try
                        {
                            var request = dbExecutor.Query<Int32>(string.Format(sqlQueryForVS, date.ToString("yyyy-MM-dd"), each)).SingleOrDefault();
                            requests.Add(new VsRequest("VS" + each, request.ToString()));
                        }

                        catch
                        {
                            requests.Add(new VsRequest("VS" + each, "0"));
                        }

                    }
                    var json = new JavaScriptSerializer().Serialize(requests);
                    ReportHelpers.CreateBlob(StorageAccount, "VsTrend" + LastNDays.ToString() + "Day.json", ContainerName, "application/json", ReportHelpers.ToStream(json));



                }
            }
        }

        public override void ExecuteCommand()
        {
            // Verify the name
            if (!Force && !AllowedPrefixes.Any(p => ConnectionString.InitialCatalog.StartsWith(p, StringComparison.OrdinalIgnoreCase)))
            {
                Log.Error("Cannot sanitize database named '{0}' without -Force argument", ConnectionString.InitialCatalog);
                return;
            }
            Log.Info("Ready to sanitize {0} on {1}", ConnectionString.InitialCatalog, Util.GetDatabaseServerName(ConnectionString));

            // All we need to sanitize is the user table. Package data is public (EVEN unlisted ones) and not PII
            if (WhatIf)
            {
                Log.Trace("Would execute the following SQL:");
                Log.Trace(SanitizeUsersQuery);
                Log.Trace("With @emailDomain = " + EmailDomain);
            }
            else
            {
                using (SqlConnection connection = new SqlConnection(ConnectionString.ConnectionString))
                using (SqlExecutor dbExecutor = new SqlExecutor(connection))
                {
                    connection.Open();
                    try
                    {
                        var count = dbExecutor.Execute(SanitizeUsersQuery, new { emailDomain = EmailDomain });
                        Log.Info("Sanitization complete. {0} Users affected", count);
                    }
                    catch (Exception ex)
                    {
                        Log.Error(ex.ToString());
                    }
                }
            }
        }

        public override void ExecuteCommand()
        {
            using (var masterDbConnection = new SqlConnection(Util.GetMasterConnectionString(ConnectionString)))
            using (var masterDbExecutor = new SqlExecutor(masterDbConnection))
            {
                masterDbConnection.Open();

                var restoreDbName = CopyDatabaseForRestore(
                    masterDbExecutor);

                using (var restoreDbConnection = new SqlConnection(Util.GetConnectionString(ConnectionString, restoreDbName)))
                using (var restoreDbExecutor = new SqlExecutor(restoreDbConnection))
                {
                    restoreDbConnection.Open();

                    PrepareDataForRestore(
                        restoreDbExecutor);

                    RenameLiveDatabase(
                        masterDbExecutor);

                    RenameDatabaseBackup(
                        masterDbExecutor,
                        restoreDbName);
                }
            }
        }

        private void CreateWeeklyStatReportFor(string connectionString,string sqlQuery,string reportName)
        {
            startingTime = new DateTime(Year, UnixTimeStampUtility.GetMonthNumber(Month), 01); //initialize to day 01 of the given month.
            DateTime monthEndTime = new DateTime(Year, UnixTimeStampUtility.GetMonthNumber(Month), UnixTimeStampUtility.GetDaysInMonth(Month));
            List<Tuple<string, string>> uploadsDataPoints = new List<Tuple<string, string>>();
            int week = 1;
            using (var sqlConnection = new SqlConnection(connectionString))
            {
                using (var dbExecutor = new SqlExecutor(sqlConnection))
                {
                    sqlConnection.Open();

                    while (startingTime <= monthEndTime)
                    {
                        DateTime endTime = startingTime.AddDays(7);
                        if (endTime > monthEndTime) endTime = monthEndTime;
                        try
                        {
                            var count = dbExecutor.Query<Int32>(string.Format(sqlQuery, startingTime.ToString("yyyy-MM-dd"), endTime.ToString("yyyy-MM-dd"))).SingleOrDefault();
                            uploadsDataPoints.Add(new Tuple<string, string>("Week" + week++, count.ToString()));
                        }
                        catch (NullReferenceException)
                        {
                            uploadsDataPoints.Add(new Tuple<string, string>("Week" + week++, "0"));
                        }
                        
                        startingTime = startingTime.AddDays(7);
                    }
                }
            }
            JArray reportObject = ReportHelpers.GetJson(uploadsDataPoints);
            ReportHelpers.CreateBlob(ReportStorage, reportName + Month + "MonthlyReport.json", "dashboard", "application/json", ReportHelpers.ToStream(reportObject));
        }

 private void DeleteDatabaseBackup(Database db, SqlExecutor dbExecutor)
 {
     if (!WhatIf)
     {
         dbExecutor.Execute(string.Format("DROP DATABASE {0}", db.Name));
     }
     Log.Info("Deleted database {0}.", db.Name);
 }

 private void DeleteDatabaseBackup(Db db, SqlExecutor dbExecutor)
 {
     if (!WhatIf)
     {
         dbExecutor.Execute($"DROP DATABASE {db.Name}");
     }
     Log.Info("Deleted database {0}.", db.Name);
 }

 public static bool BackupIsInProgress(SqlExecutor dbExecutor, string backupPrefix)
 {
     return dbExecutor.Query<Db>(
         // Not worried about SQL Injection here :). This is an admin tool.
         "SELECT name, state FROM sys.databases WHERE name LIKE '" + backupPrefix + "%' AND state = @state",
         new { state = CopyingState })
         .Any();
 }

 public LessonModule(InteractiveSqlApplication application, SqlExecutor sqlExecutor)
 {
     m_Application = application;
     m_SqlExecutor = sqlExecutor;
     var firstLesson = m_Application.Course.Lessons.First();
     Get["/"] = parameters => Response.AsRedirect(firstLesson.Url());
     Get["/lesson/{lessonName}"] = parameters => RenderLesson(parameters.lessonName);
 }

 public void EmptyQueriesCauseFailedResult()
 {
     var application = new SqlExecutor(m_SqlServerConfiguration);
     application.SetUpDatabase(" ");
     var result = application.ExecuteQuery("");
     
     Assert.Equal("Query is empty", ExpectError(result));
 }

        public void BadlyFormedQueriesCauseFailedResult()
        {
            var application = new SqlExecutor(m_SqlServerConfiguration);
            application.SetUpDatabase(" ");
            var result = application.ExecuteQuery("SELECTEROO");

            Assert.Equal("Could not find stored procedure 'SELECTEROO'.", ExpectError(result));
        }

        public override void ExecuteCommand()
        {
            var cstr = Util.GetMasterConnectionString(ConnectionString.ConnectionString);
            using (var connection = new SqlConnection(cstr))
            using (var db = new SqlExecutor(connection))
            {
                connection.Open();

                // Snap the current date just in case we are running right on the cusp
                var today = DateTime.UtcNow;

                // Get the list of database backups
                var backups = db.Query<Db>(
                    "SELECT name, state FROM sys.databases WHERE name LIKE 'Backup_%'")
                    .Select(d => new OnlineDatabaseBackup(Util.GetDatabaseServerName(ConnectionString), d.Name, d.State))
                    .OrderByDescending(b => b.Timestamp)
                    .ToList();

                // Grab end-of-day backups from days before today
                var dailyBackups = backups
                    .GroupBy(b => b.Timestamp.Value.Date)
                    .Where(g => g.Key < today.Date)
                    .Select(g => g.OrderByDescending(b => b.Timestamp.Value).Last())
                    .ToList();
                Log.Info("Found {0} daily backups to export", dailyBackups.Count);

                // Start exporting them
                foreach (var dailyBackup in dailyBackups)
                {
                    if (dailyBackup.State != Util.OnlineState)
                    {
                        Log.Info("Skipping '{0}', it is still being copied", dailyBackup.DatabaseName);
                    }
                    else
                    {
                        if (dailyBackup.Timestamp.Value.TimeOfDay < new TimeSpan(23, 30, 00))
                        {
                            Log.Warn("Somehow, '{0}' is the only backup from {1}. Exporting it to be paranoid",
                                dailyBackup.DatabaseName,
                                dailyBackup.Timestamp.Value.Date.ToShortDateString());
                        }
                        Log.Info("Exporting '{0}'...", dailyBackup.DatabaseName);
                        (new ExportDatabaseTask()
                        {
                            ConnectionString = new SqlConnectionStringBuilder(ConnectionString.ConnectionString)
                            {
                                InitialCatalog = dailyBackup.DatabaseName
                            },
                            DestinationStorage = StorageAccount,
                            DestinationContainer = "database-backups",
                            SqlDacEndpoint = SqlDacEndpoint,
                            WhatIf = WhatIf
                        }).Execute();
                    }
                }
            }
        }

        public static DateTime GetLastBackupTime(SqlExecutor dbExecutor, string backupNamePrefix)
        {
            var lastBackup = GetLastBackup(dbExecutor, backupNamePrefix);

            if (lastBackup == null)
                return DateTime.MinValue;

            var timestamp = lastBackup.Name.Substring(backupNamePrefix.Length);

            return GetDateTimeFromTimestamp(timestamp);
        }

        public override void ExecuteCommand()
        {
            using (var sqlConnection = new SqlConnection(ConnectionString.ConnectionString))
            using (var dbExecutor = new SqlExecutor(sqlConnection))
            {
                sqlConnection.Open();

                var externalPackages = dbExecutor.Query<Package>(@"
                    SELECT pr.Id, p.Version, p.ExternalPackageUrl
                    FROM Packages p 
                        JOIN PackageRegistrations pr ON pr.[Key] = p.PackageRegistrationKey
                    WHERE p.ExternalPackageUrl IS NOT NULL
                    ORDER BY Id, Version");

                foreach (Package pkg in externalPackages)
                {
                    Console.WriteLine();
                    HttpClient client = new HttpClient();
                    var responseTask = client.GetAsync(pkg.ExternalPackageUrl);
                    var response = responseTask.Result;
                    if (!response.IsSuccessStatusCode)
                    {
                        Console.WriteLine("Found broken package: " + response.StatusCode + "  " + pkg.ExternalPackageUrl);
                        Console.WriteLine("You should ask the package owner to unlist the package " + pkg.Id + " " + pkg.Version);
                    }

                    var bytesTask = response.Content.ReadAsByteArrayAsync();
                    byte[] bytes = bytesTask.Result;
                    var blobClient = CreateBlobClient();
                    var packagesBlobContainer = Util.GetPackagesBlobContainer(blobClient);
                    var packageFileBlob = Util.GetPackageFileBlob(
                        packagesBlobContainer,
                        pkg.Id,
                        pkg.Version);
                    var fileName = Util.GetPackageFileName(
                        pkg.Id,
                        pkg.Version);
                    if (packageFileBlob.Exists())
                    {
                        Console.WriteLine("SKIPPED! Package file blob " + fileName + " already exists");
                    }
                    else
                    {
                        Console.WriteLine("Saving the package file " + pkg.ExternalPackageUrl + " to blob storage as " + fileName);
                        if (!WhatIf)
                        {
                            packageFileBlob.UploadFromStream(
                                new MemoryStream(bytes),
                                AccessCondition.GenerateIfNoneMatchCondition("*"));
                        }
                    }
                }
            }
        }

        private static void RunScriptsInForDatabase(Server server, DbParams dbParams, IEnumerable<string> sqlFiles)
        {
            var executor = new SqlExecutor(server);

            var sqlFilesSortedAlphabetically = Order(sqlFiles);

            foreach(var sqlFile in sqlFilesSortedAlphabetically) {
                Console.WriteLine("Going to run " + sqlFile);
                executor.ExecuteNonQuery(File.ReadAllText(sqlFile), dbParams.DbName);
            }
        }