示例#1
0
        async Task ExecuteSqlScriptAsync(string scriptName, DatabaseLock dbLock)
        {
            if (dbLock == null)
            {
                throw new ArgumentNullException(nameof(dbLock));
            }

            if (!dbLock.IsHeld)
            {
                throw new ArgumentException("This database lock has already been released!", nameof(dbLock));
            }

            string scriptText = await GetScriptTextAsync(scriptName);

            // Split script into distinct executeable commands
            IEnumerable <string> scriptCommands = Regex.Split(scriptText, @"^\s*GO\s*$", RegexOptions.Multiline | RegexOptions.IgnoreCase)
                                                  .Where(x => x.Trim().Length > 0);

            Stopwatch latencyStopwatch = Stopwatch.StartNew();

            try
            {
                foreach (string commandText in scriptCommands)
                {
                    using SqlCommand command = dbLock.CreateCommand();
                    command.CommandText      = commandText;

                    await command.ExecuteNonQueryAsync();
                }
            }
            finally
            {
                latencyStopwatch.Stop();
                this.traceHelper.ExecutedSqlScript(scriptName, latencyStopwatch);
            }
        }
示例#2
0
        public async Task CreateOrUpgradeSchemaAsync(bool recreateIfExists)
        {
            // Prevent other create or delete operations from executing at the same time.
            await using DatabaseLock dbLock = await this.AcquireDatabaseLockAsync(this.settings.CreateDatabaseIfNotExists);

            var currentSchemaVersion = new SemanticVersion(0, 0, 0);

            if (recreateIfExists)
            {
                await this.DropSchemaAsync(dbLock);
            }
            else
            {
                // If the database already has the latest schema, then skip
                using SqlCommand command = dbLock.CreateCommand();
                command.CommandText      = "dt._GetVersions";
                command.CommandType      = CommandType.StoredProcedure;

                try
                {
                    using DbDataReader reader = await SqlUtils.ExecuteReaderAsync(command, this.traceHelper);

                    if (await reader.ReadAsync())
                    {
                        // The first result contains the latest version
                        currentSchemaVersion = SqlUtils.GetSemanticVersion(reader);
                        if (currentSchemaVersion >= DTUtils.ExtensionVersion)
                        {
                            // The schema is already up-to-date.
                            return;
                        }
                    }
                }
                catch (SqlException e) when(e.Number == 2812 /* Could not find stored procedure */)
                {
                    // Ignore - this is expected for new databases
                }
            }

            // SQL schema setup scripts are embedded resources in the assembly, making them immutable post-build.
            Assembly             assembly          = typeof(SqlOrchestrationService).Assembly;
            IEnumerable <string> createSchemaFiles = assembly.GetManifestResourceNames()
                                                     .Where(name => name.Contains(".schema-") && name.EndsWith(".sql"));

            var versionedFiles = new Dictionary <SemanticVersion, string>();

            foreach (string name in createSchemaFiles)
            {
                // Attempt to parse the semver-like string from the resource name.
                // This version number tells us whether to execute the script for this extension version.
                const string RegexExpression = @"schema-(\d+.\d+.\d+(?:-\w+)?).sql$";
                Match        match           = Regex.Match(name, RegexExpression);
                if (!match.Success || match.Groups.Count < 2)
                {
                    throw new InvalidOperationException($"Failed to find version information in resource name '{name}'. The resource name must match the regex expression '{RegexExpression}'.");
                }

                SemanticVersion version = SemanticVersion.Parse(match.Groups[1].Value);
                if (!versionedFiles.TryAdd(version, match.Value))
                {
                    throw new InvalidOperationException($"There must not be more than one script resource with the same version number! Found {version} multiple times.");
                }
            }

            // Sort by the version numbers to ensure that we run them in the correct order
            foreach ((SemanticVersion version, string name) in versionedFiles.OrderBy(pair => pair.Key))
            {
                // Skip past versions that are already present in the database
                if (version > currentSchemaVersion)
                {
                    await this.ExecuteSqlScriptAsync(name, dbLock);

                    currentSchemaVersion = version;
                }
            }

            // Add or update stored procedures, functions, and views
            await this.ExecuteSqlScriptAsync("logic.sql", dbLock);

            // Configure security roles, permissions, etc.
            await this.ExecuteSqlScriptAsync("permissions.sql", dbLock);

            // Insert the current extension version number into the database and commit the transaction.
            // The extension version is used instead of the schema version to more accurately track whether
            // we need to update the sprocs or views.
            using (SqlCommand command = dbLock.CreateCommand())
            {
                command.CommandText = "dt._UpdateVersion";
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Add("@SemanticVersion", SqlDbType.NVarChar, 100).Value = DTUtils.ExtensionVersion.ToString();

                await SqlUtils.ExecuteNonQueryAsync(command, this.traceHelper);
            }

            await dbLock.CommitAsync();
        }