Running SQL Code Analysis during Flyway Migrations
A set of PowerShell automation script tasks for running database build and migrations tasks. This article describes the SQL code analysis task, which will check the syntax of the SQL code in your databases and your migration scripts for 'code smells'.
Flyway is the database build component in a development and deployment system, but it is only one component among many. We need a way to support the other tools that are needed as part of an automated Flyway migration or build. I’ll show how to integrate Flyway with SQL (or Oracle) code analysis, two of the tools in Flyway Enterprise, to check a Flyway-managed database for code issues. The demo in this article uses the Code Analysis for SQL Server command line component, but Oracle Code Analysis, part of Flyway Enterprise, works in the same way.
When this check is run automatically, where building a new version of the database, it will help you detect and correct issues early in the development cycle and allow the Governance and Operations team visibility into production readiness of the code.
Creating PowerShell build tasks with Script blocks
I’ll use a slightly unconventional approach to scripting. With PowerShell, I tend to like to reuse scripting components and assemble them like Lego. I use some tools that are part of Flyway Enterprise, and some that aren’t, but by sticking to certain conventions, I can then click them all together quickly.
The method I use takes advantage of a feature of .NET and PowerShell that often trips people up. This is the fact that hash tables and other large objects are generally passed by reference. You don’t copy the whole object. If you actually want to copy a hash table, you need to use the .Clone() method. Unless you want to get confused, you generally need to pretend to yourself that a hash table is read-only. However, in our case, we will use this feature of “pass-by-reference objects” to pass information down a chain of portable, anonymous functions that are just script blocks. This requires that you have a standard for the keys that you use as parameters and return values. The reason I use this method is that I can use these same PowerShell Components in Flyway Teams using a file, representing a hash table, as a parameter, and as a way to return values to the system after the script is executed. Basically, one can avoid using placeholders as parameters in Flyway callback scripts by using a JSON or XML file to pass information between build tasks.
Build tasks for Flyway migrations
These build tasks are usually done before or after a migration. In my previous article, Creating Database Build Artifacts when Running Flyway Migrations, I demonstrated build tasks, implemented as PowerShell script blocks, to generate a build script, and an object level source folder.
They generally need to know where the database is, the type of database, how to get there, and what credentials are needed. They usually need also to know the version of the database. You need to store all the problems (job-stoppers) and warnings that come up in the chain.
In this article, we’ll add in a simple build task that runs a code analysis check on a database, or the migration scripts for it We can use it to report any issues with the code within the database, each time we run Flyway to deliver a new version.
How the build task components work
The following diagram shows how this code analysis task, and various other build tasks, can be used, and what they would be able to do, in the context of a Flyway migration.
The ‘Scripted Flyway migration process’ is a PowerShell script that, when executed, uses Flyway to migrate the database to a specified version. The script then executes script blocks for each of the required build tasks, such as to generate the build script for the new version, the Source directory with its subdirectories, and a Reports folder containing a code analysis report (as indicated by the green arrow ‘Build scripts and reports’). Once the Source directory for a version is created, there is another build task that can use it to check that an existing copy of the database, purportedly at that version, hasn’t subsequently drifted. It will also provide the input for an SQL Change Automation-based script.
These tasks must be run in the right order and any problems must halt the process. Often, there are a few other utility tasks that must be run before the required task can take place. For example, before it can run a migration task, Flyway must check user credentials and then access the database, using the supplied details and credentials. There is also a task that checks the current version of the database, either before or after a migration, and this check is often required before proceeding with any other tasks such a generating a build script or checking the database for any issues with the code.
Here’s the current list of available tasks for running deployments with Flyway and other Enterprise edition tools, though I’ll be adding more over time. In this article we’ll be running the code analysis checks, either on the database ($CheckCodeInDatabase), or the set of migration scripts ($CheckCodeInMigrationFiles), but to do that we’ll need a few of the preliminary tasks too.
$FetchAnyRequiredPasswordsThis checks the hash table to see if there is a username without a password. If so, the task asks for it in a query window and stores it, encrypted, in the user area it. If the user already has the password stored for this username and database, it uses it.$GetCurrentVersionThis contacts the database and determines its current version by interrogating theflyway_schema_historydata table in the database. It merely finds the highest version number recorded as being successfully used. If it is an empty database, or there is just no Flyway data, then it returns a version of 0.0.0.$FetchOrSaveDetailsOfParameterSet: This allows you to save and load the shared parameters for all these script blocks, under a name you give it. You’d choose a different name for each database within a project but make them unique across projects If the parameters include the name, but the vital information is missing, it fills it in from the last remembered version. If it has the name and the vital information, then it assumes you want to save it. If there is no name, then it ignores the hash table. It does not save problems and warnings.$CheckCodeInDatabaseThis runs SQL code analysis on the scripted objects within the database such as procedure, functions and views and saves the report and reports back any issues in the$DatabaseDetailshash table. It saves the reports in the designated project directory, in a Reports folder, as a subfolder for the supplied version (for example, in Pubs\1.1.5\Reports), so it needs$GetCurrentVersionto have been run beforehand in the chain of tasks.$CheckCodeInMigrationFilesThis, where necessary, runs SQL code analysis on all the migration files in the folder and saves the report and again it saves the reports in the designated project directory, each one in a Reports folder, as a subfolder for the supplied version.$IsDatabaseIdenticalToSource: This uses SQL Compare to check that a version of a database is correct and hasn’t been changed. To do this, the$CreateScriptFoldersIfNecessarytask must have been run first. It compares the database to the associated source folder, for that version, and returns, in the hash table, the comparison equal to true if it was the same, or false if there has been drift, with a list of objects that have changed. If the comparison returns$null, then it means there has been an error. To access the right source folder for this database version, it needs$GetCurrentVersionto have been run beforehand in the chain of tasks$CreateScriptFoldersIfNecessary: this task checks to see if a Source folder already exists for this version of the database and, if not, it will create one and fill it with subdirectories for each type of object. A tables folder will, for example, have a file for every table each containing a build script to create that object. When this exists, it allows SQL Compare to do comparisons and check that a version has not drifted. It saves the Source folder as a subfolder for the supplied version, so it needs$GetCurrentVersionto have been run beforehand in the chain of tasks.$CreateBuildScriptIfNecessary: produces a build script from the database, using SQL Compare. It saves the build script in the Scripts folder, as a subfolder for the supplied version, so it needs$GetCurrentVersionto have been run beforehand in the chain of tasks.$ExecuteTableSmellReport:This script block executes SQL that produces a report in XML or JSON from the database that alerts you to tables that may have issues
All the build tasks listed in 4-9 will run the task only once per version. They start by checking to see if the output, at the required version, has been done already. If so, then it is safe to assume that it doesn’t need to be redone as in normal circumstances old migration scripts can’t be changed, so a database at a particular version can’t change.
If the Flyway migration sequence is altered, or any of the migration files are cleared, then all the existing files in the Scripts directory must be cleared too. If a file of any type, such as a generated build script, must be redone, then the old one must be deleted beforehand, because the routines check for the existence of the file first and will only script out a version if the script no longer exists in that directory. If any file in a Scripts or Source folder must be refreshed, then the entire directory must be deleted. This will need to be part of a flyway 'clean' action if you are scripting at the same time.
The code analysis build task
This task is implemented using the Code Analysis for SQL Server command line component (a.k.a. SQL Code Guard) in Flyway Enterprise. It is very easy to run SQL code guard. You tell it the task, either to check the source database or the scripts for code smells, you give it the details such as the database or the location of the scripts folder and let it generate an XML file of the results that you can then prettify to taste.
Running SQL Prompt’s Code Analysis Rules
You can also define the SQL code analysis rules that your team considers important using SQL Prompt and them run them automatically from a PowerShell script, using SQL code Guard, which I demonstrate in SQL Code Analysis from a PowerShell Deployment Script. This article also explains how to obtain and install code guard.
Here is the script block that I use for the $CheckCodeInDatabase task. It looks rather more complicated than it sounds, because it must make various checks and cooperates with other Lego-style tasks. It also reports back, in the $DatabaseDetails hash table, any issues it finds (note that this is applicable to SQL Server only, at the moment).
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 |
<#This scriptblock checks the code in the database for any issues, using SQL Code Guard to do all the work. This runs SQL Codeguard and saves the report in a subdirectory the version directory of your project artefacts. It also reports back in the $DatabaseDetails Hashtable. It checks the current database, not the scripts $DatabaseDetails=@{ 'server'='MyServer'; 'Database'='MyDatabase'; 'version'='1.1.15'; 'project'='MyProjectName'; 'uid'='MyUID'; #if necessary! 'pwd'='MyPassword'#if necessary! 'warnings'=@{};'problems'=@{};} $CheckCodeInDatabase.Invoke($OurDetails) $DatabaseDetails.warnings.CheckCodeInDatabase #> $CheckCodeInDatabase = { Param ($param1) # $CheckCodeInDatabase - (Don't delete this) #you must set this value correctly before starting. Set-Alias CodeGuard "${env:ProgramFiles(x86)}\SQLCodeGuard\SqlCodeGuard30.Cmd.exe" -Scope local $Problems = @(); #our local problem counter #is that alias correct? if (!(test-path ((Get-alias -Name codeguard).definition) -PathType Leaf)) { $Problems += 'The alias for Codeguard is not set correctly yet' } if ($param1.Escapedserver -eq $null) #double-check that escapedValues are in place { #we need this to wotk out the location for the report $EscapedValues = $param1.GetEnumerator() | where { $_.Name -in ('server', 'Database', 'Project') } | foreach{ @{ "Escaped$($_.Name)" = ( $_.Value.Split([IO.Path]::GetInvalidFileNameChars()) -join '_') } } $EscapedValues | foreach{ $param1 += $_ } } #check that all the values we need are in the hashtable @('server', 'Database', 'version', 'EscapedProject') | foreach{ if ($param1.$_ -eq $null) { $Problems += "no value for '$($_)'" } } #now we create the parameters for CodeGuard. $MyDatabasePath = "$($env:USERPROFILE)\Documents\GitHub\$( $param1.EscapedProject)\$($param1.Version)\Reports" $Arguments = @{ server = $($param1.server) #The server name to connect Database = $($param1.database) #The database name to analyze outfile = "$MyDatabasePath\codeAnalysis.xml" <# The file name in which to store the analysis xml report#> #exclude='BP007;DEP004;ST001' #A semicolon separated list of rule codes to exclude include = 'all' #A semicolon separated list of rule codes to include } #add the arguments for credentials where necessary if (!([string]::IsNullOrEmpty($param1.uid))) { $Arguments += @{ User = $($param1.uid) Password = $($param1.pwd) } } # we need to make sure tha path is there if (-not (Test-Path -PathType Container $MyDatabasePath)) { # does the path to the reports directory exist? # not there, so we create the directory $null = New-Item -ItemType Directory -Force $MyDatabasePath; } <# we only do the analysis if it hasn't already been done for this version, and we've hit no problems #> if (($problems.Count -eq 0) -and (-not ( Test-Path -PathType leaf "$MyDatabasePath\codeAnalysis.xml"))) { $result = codeguard @Arguments; #execute the command-line Codeguard. if ($? -or $LASTEXITCODE -eq 1) { "Written Code analysis for $($param1.Project) $($param1.Version ) to $MyDatabasePath\codeAnalysis.xml" } else { <#report a problem and send back the args for diagnosis (hint, only for script development) #> $Args = ''; $Args += $Arguments | foreach{ "$($_.Name)=$($_.Value)" } $problems += "CodeGuard responded '$result' with error code $LASTEXITCODE when used with parameters $Args." } $Problems += $result | where { $_ -like '*error*' } } if ($problems.Count -gt 0) { Write-warning "Problem '$problems' with CheckCodeInDatabase! "; $Param1.Problems.'CheckCodeInDatabase' += $problems; } if ($problems.Count -eq 0) { $Param1.Locations.'CheckCodeInDatabase' = "$MyDatabasePath\codeAnalysis.xml"; } } |
Running a quick code analysis check on the database
Probably the easiest way of using this code is to paste it into a script and then add something like this.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
$OurDetails=@{ 'server'='MyServer'; 'Database'='MyDatabase'; 'version'='MyVersion'; 'project'='MyProjectName'; <a id="post-4038606-_Hlk68107520"></a>'uid'='MyuserID'; #if necessary! 'pwd'='MyCunningPassword'; #if necessary! 'warnings'=@{};'problems'=@{}; 'Locations'=@{} } $CheckCodeInDatabase.Invoke($OurDetails) <# now we print out all the problems with the code reported by Codeguard because it is in XML format in the file #> if ($OurDetails.Problems.Count -eq 0) { [xml]$XmlDocument = Get-Content -Path $OurDetails.Locations.CheckCodeInDatabase $warnings = @(); $warnings += $XmlDocument.root.GetEnumerator() | foreach{ $name = $_.name.ToString(); $_.issue } | select-object @{ Name = "Object"; Expression = { $name } }, code, line, text $warnings } |
This should give you a list of issues in the code (this is truncated output):
Object code line text ------ ---- ---- ---- dbo.byroyalty PE009 4 No SET NOCOUNT ON before DML dbo.PublishersByPublicationType DEP021 5 String literals as column aliases are deprecated dbo.PublishersByPublicationType DEP021 6 String literals as column aliases are deprecated dbo.PublishersByPublicationType DEP021 7 String literals as column aliases are deprecated dbo.PublishersByPublicationType DEP021 8 String literals as column aliases are deprecated dbo.PublishersByPublicationType DEP021 9 String literals as column aliases are deprecated dbo.PublishersByPublicationType DEP021 10 String literals as column aliases are deprecated dbo.PublishersByPublicationType DEP021 11 String literals as column aliases are deprecated dbo.PublishersByPublicationType ST010 13 Use alias for all table sources dbo.PublishersByPublicationType ST010 14 Use alias for all table sources dbo.PublishersByPublicationType ST010 16 Use alias for all table sources dbo.PublishersByPublicationType PE002 16 Schema name for table or view is not specified dbo.PublishersByPublicationType ST010 17 Use alias for all table sources dbo.reptq1 PE009 4 No SET NOCOUNT ON before DML dbo.reptq1 MI003 6 Unqualified column name dbo.reptq1 ST010 7 Use alias for all table sources dbo.reptq1 ST010 8 Use alias for all table sources dbo.reptq1 ST010 10 Use alias for all table sources dbo.reptq1 PE002 10 Schema name for table or view is not specified ..<etc…>
Chaining build tasks
Our simple example so far isn’t fit for real use for several reasons. Firstly, you really don’t want passwords in PowerShell scripts. Also, you need to know the version of the database so that it gets saved in the correct directory. Anyway, it is probably going to be easier for anything but the simplest jobs to use separate tasks in a chain for jobs each of which performs a single action such as finding out the current version. After all, you don’t want to execute any other tasks if one of the tasks fail.
You can get around this by chaining tasks. As described earlier, there is a handy task in this project that will save and retrieve your password from a secure file in your user area, and one that will find out what version the database is at, by reading the Flyway schema history table. To access these script block tasks, you need to first execute the file they are in. This PowerShell file, called file, called DatabaseBuildAndMigrateTasks.ps1, provides a cmdlet and a whole lot of script blocks.
Once you have this, you can pull in this file at the start of each job. This can be done in several ways, but I generally just save the calling script (JustCheckCodeInDatabase.ps1) and the file containing the script block tasks in the same directory as my Flyway scripts. This should work whatever way you call the script, maybe in the IDE, as a command-line script, or from a deployment or release tool. If you just paste in this code and run it, you’ll have to make the folder hosting your script blocks tasks file the working directory first.
Here’s the code for the JustCheckCodeInDatabase.ps1 job, which after calling the preliminary tasks to get the password and check the database version, just runs the $CheckCodeInDatabase task. You can get the code from my GitHub:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 |
<# this script checks out the code of the current version of the database and all the migration file. It lists all the problems in the current database. Then it runs Flyway Info task on the database #> $pushVerbosity=$VerbosePreference $VerbosePreference= 'continue' #create an alias for the commandline Flyway, #Set-Alias Flyway 'MyPathTo\flyway.cmd' -Scope local #create an alias for the commandline Flyway, Set-Alias Flyway 'C:\ProgramData\chocolatey\lib\flyway.commandline\tools\flyway-7.3.1\flyway.cmd' -Scope local if (!(test-path ((Get-alias -Name Flyway).definition) -PathType Leaf)) { Write-error "Sorry, but you need a path to the Flyway Commandline" } $MyProject = 'pubs' #Must fill in the name of the project. This determines where #script artefacts are kept <# The tasks are in a separate script. It is placed in the same directory as this script. First, find out where we were executed from. Each environment has a different way of doing it. It all depends how you execute it. If you just past this in, you'll have to make this the working directory #> try { $executablepath = [System.IO.Path]::GetDirectoryName($myInvocation.MyCommand.Definition) } catch { $executablepath = '' } #didn't like that so remove it if ($executablepath -eq '') { $executablepath = "$(If ($psISE) # null if at the commandline { Split-Path -Path $psISE.CurrentFile.FullPath } Else { $global:PSScriptRoot })" } if ([string]::IsNullOrEmpty($ExecutablePath)) { $ExecutablePath = $pwd } .("$executablepath\DatabaseBuildAndMigrateTasks.ps1") $pushVerbosity=$VerbosePreference# we will run this in verbose mode to try it out $VerbosePreference= 'continue' $Invocations = @( $FetchOrSaveDetailsOfParameterSet, #save parameters so you can recall them later. $FetchAnyRequiredPasswords, #passwords are kept in an encrypte4d file in the user area $GetCurrentVersion, #get the current version so you can save the code report in the right place $CheckCodeInDatabase #now look at all the code in the modules (Procs, functions and so on) ) $OurDetails =@{ 'server'='MyServer'; 'Database'='MyDatabase'; 'version'='MyVersion'; 'project'='MyProjectName'; 'uid'='MyuserID'; #if necessary! 'warnings'=@{};'problems'=@{}; 'Locations'=@{} } Process-FlywayTasks $OurDetails $Invocations <# now we print out all the problems with the code reported by Codeguard because it is in XML format in the file #> if ($OurDetails.Problems.Count -eq 0) { [xml]$XmlDocument = Get-Content -Path $OurDetails.Locations.CheckCodeInDatabase $warnings = @(); $warnings += $XmlDocument.root.GetEnumerator() | foreach{ $name = $_.name.ToString(); $_.issue } | select-object @{ Name = "Object"; Expression = { $name } }, code, line, text $warnings } #return to our previous verbosity $VerbosePreference=$pushVerbosity |
Giving …
Object code line text ------ ---- ---- ---- dbo.byroyalty PE009 4 No SET NOCOUNT ON before DML dbo.byroyalty ST003 2 Procedure body not enclosed with BEGIN...END dbo.byroyalty PE002 4 Schema name for table or view is not specified dbo.employee_insupd EI020 27 ROLLBACK TRANSACTION without BEGIN TRANSACTION dbo.employee_insupd PE009 8 No SET NOCOUNT ON before DML dbo.employee_insupd MI003 8 Unqualified column name dbo.employee_insupd MI003 8 Unqualified column name dbo.employee_insupd ST001 10 Old-style join is used (...from table1,table2...) dbo.employee_insupd PE002 10 Schema name for table or view is not specified dbo.employee_insupd PE002 10 Schema name for table or view is not specified dbo.reptq1 PE009 4 No SET NOCOUNT ON before DML dbo.reptq1 ST003 2 Procedure body not enclosed with BEGIN...END dbo.reptq1 PE002 6 Schema name for table or view is not specified dbo.reptq2 PE009 4 No SET NOCOUNT ON before DML dbo.reptq2 ST003 2 Procedure body not enclosed with BEGIN...END dbo.reptq2 PE002 7 Schema name for table or view is not specified dbo.reptq3 PE009 4 No SET NOCOUNT ON before DML dbo.reptq3 ST003 2 Procedure body not enclosed with BEGIN...END dbo.reptq3 PE002 7 Schema name for table or view is not specified dbo.titleview MI003 4 Unqualified column name dbo.titleview MI003 4 Unqualified column name dbo.titleview MI003 4 Unqualified column name dbo.titleview MI003 4 Unqualified column name dbo.titleview MI003 4 Unqualified column name dbo.titleview MI003 4 Unqualified column name dbo.titleview ST001 5 Old-style join is used (...from table1,table2...) dbo.titleview ST010 5 Use alias for all table sources dbo.titleview PE002 5 Schema name for table or view is not specified dbo.titleview ST010 5 Use alias for all table sources dbo.titleview PE002 5 Schema name for table or view is not specified dbo.titleview ST010 5 Use alias for all table sources dbo.titleview PE002 5 Schema name for table or view is not specified
Running SQL Code Analysis on a Flyway-managed database
So, let’s run some code analysis checks from a database that is being managed by Flyway. This time we’ll check both the database, with $CheckCodeInDatabase, and the migration files, with $CheckCodeInMigrationFiles. If you just check the database, you only check the code inside the modules such as views, procedures and functions. You miss out on checking the syntax in the migration files.
After the checks are done both on the database and all the current migration files, the script just calls Flyway with the Info task, but you can see how it could easily be your chosen set of Flyway tasks. You can choose to set it to run before a migration, or after it is done. As with the earlier version, it also uses the $FetchAnyRequiredPasswords script block task to handle passwords securely, and the $GetCurrentVersion task to check the version of the database before running the static code checks. Only if it gets a valid version can it then run the checks. If the database is empty, or hasn’t got a flyway version number, then $GetCurrentVersion assumes a version of 0.0.0. The process needs a version number to save a report of the code issues in the right place. In a subsequent article I’ll be showing how to do drift checks, and other post-migration tricks.
Saving the database details
This time, we will also use the $FetchOrSaveDetailsOfParameterSet task to save the database details and other information to a hashtable, which we’ll store on disk in the user area. All you need to do to get this to work is run the code once, providing all the database details in long form and giving the parameter set a name, like this:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
<$DatabaseDetails = @{ 'name' ='TheNameToGiveThisDatabaseAndProject'; 'ProjectFolder' = 'MyPathToTheFlywayFolder\PubsFlywaySecondMigration'; 'ProjectDescription'='However you describe your project'; 'pwd' = ''; #Always leave blank 'uid' = 'MyUserName'; #leave blank unless you use credentials 'Database' = 'MyDatabase'; # fill this in please 'server' = 'MyServer'; # We need to know the server! 'port' = $null; #Not normally needed with SQL Server. add if required #set to $null or leave it out if you want to let jdbc detect it 'Project' = $MyProject; # the name of the project-needed for saving files 'Version' = ''; # current version of database - # leave blank unless you know } |
Happily, you don’t have to call it ‘TheNameToGiveThisDatabaseAndProject‘: just something memorable that is unique within the project. Normally, you’d probably use the name of the database you’re working on, but you can call it after your pet cat or one of the Irish saints, if you prefer. If you provide a database as well as a name and project, the $FetchOrSaveDetailsOfParameterSet task will save the details to disk. Of course, if you don’t want your full flyway data saved every time you run the code, don’t fill in the name.
Once they are saved, you can simply retrieve those details whenever you want to use this database on this development project, rather than entering them all over again, like this…
|
1 2 3 4 |
$OurDetails = @{ 'name' = 'TheNameToGiveThisDatabaseAndProject'; 'Project' = $MyProject; } |
If you use this short form, providing a name, and project, but no server or database, then the task looks on disk to find if there is a previously saved hashtable under that name. If so, it loads it. If something goes wrong, you can access the data on env:USERPROFILE\Documents\Deploy\<name of project>, and either alter it by hand or delete it. Once you have things running for your project, you won’t have to leave or change details of servers in your script. If you wish to change what is stored, you run it long-form with the name again.
Running the build tasks
As in the previous example, all the build tasks are in a separate PowerShell script file, called DatabaseBuildAndMigrateTasks.ps1, in the same directory as the script below. The Flyway migrate Scripts folder is, in my case, UserName>\Documents\GitHub\PubsAndFlyway\PubsFlywaySecondMigration, and the output of all build tasks goes to different project folder, called Pubs (<UserName>\Documents\GitHub\Pubs), which is laid out with a directory for each version.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 |
<# this script checks out the code of the current version of the database and all the migration file. It lists all the problems in the current database. Then it runs Flyway Info task on the database #> $pushVerbosity=$VerbosePreference $VerbosePreference= 'continue' #create an alias for the commandline Flyway, #Set-Alias Flyway 'MyPathTo\flyway.cmd' -Scope local #create an alias for the commandline Flyway, Set-Alias Flyway 'C:\ProgramData\chocolatey\lib\flyway.commandline\tools\flyway-7.3.1\flyway.cmd' -Scope local if (!(test-path ((Get-alias -Name Flyway).definition) -PathType Leaf)) { Write-error "Sorry, but you need a path to the Flyway Commandline" } $MyProject = 'pubs' #Must fill in the name of the project. This determines where #script artefacts are kept <# The tasks are in a separate script. It is placed in the same directory as this script. First, find out where we were executed from. Each environment has a different way of doing it. It all depends how you execute it. If you just past this in, you'll have to make this the working directory #> try { $executablepath = [System.IO.Path]::GetDirectoryName($myInvocation.MyCommand.Definition) } catch { $executablepath = '' } #didn't like that so remove it if ($executablepath -eq '') { $executablepath = "$(If ($psISE) # null if at the commandline { Split-Path -Path $psISE.CurrentFile.FullPath } Else { $global:PSScriptRoot })" } if ([string]::IsNullOrEmpty($ExecutablePath)) { $ExecutablePath = $pwd } .("$executablepath\DatabaseBuildAndMigrateTasks.ps1") <# $DatabaseDetails = @{ 'name' ='TheNameToGiveThisDatabaseAndProject'; 'ProjectFolder' = 'MyPathToTheFlywayFolder\PubsFlywaySecondMigration'; 'ProjectDescription'='However you describe your project'; 'pwd' = ''; #Always leave blank 'uid' = 'MyUserName'; #leave blank unless you use credentials 'Database' = 'MyDatabase'; # fill this in please 'server' = 'MyServer'; # We need to know the server! 'port' = $null; #Not normally needed with SQL Server. add if required #set to $null or leave it out if you want to let jdbc detect it 'Project' = $MyProject; # the name of the project-needed for saving files 'Version' = ''; # current version of database - # leave blank unless you know } #> $pushVerbosity=$VerbosePreference $VerbosePreference= 'continue' $DatabaseDetails = @{ 'name' = 'MyDatabase'; 'Project' = $MyProject; } $Invocations = @( $FetchOrSaveDetailsOfParameterSet, #save parameters so you can recall them later. $FetchAnyRequiredPasswords, #passwords are kept in an encrypte4d file in the user area $GetCurrentVersion, #get the current version so you can save the code report in the right place $CheckCodeInDatabase, #now look at all the code in the modues (Procs, functions and so on) $CheckCodeInMigrationFiles, #make sure we've done a code analysis on the files too $FormatTheBasicFlywayParameters #so we can use flyway ) $DatabaseDetails.problems=@{} Process-FlywayTasks $DatabaseDetails $Invocations if ($DatabaseDetails.Problems.Count -eq 0) { [xml]$XmlDocument = Get-Content -Path $DatabaseDetails.Locations.CheckCodeInDatabase $warnings = @(); $warnings += $XmlDocument.root.GetEnumerator() | foreach{ $name = $_.name.ToString(); $_.issue } | select-object @{ Name = "Object"; Expression = { $name } }, code, line, text $warnings } if ($DatabaseDetails.Problems.Count -eq 0) { Flyway info $DatabaseDetails.FlyWayArgs } $VerbosePreference=$pushVerbosity |
Here’s the resulting output:
Object code line text ------ ---- ---- ---- dbo.byroyalty PE009 4 No SET NOCOUNT ON before DML dbo.PublishersByPublicationType DEP021 5 String literals as column aliases are deprecated dbo.PublishersByPublicationType DEP021 6 String literals as column aliases are deprecated dbo.PublishersByPublicationType DEP021 7 String literals as column aliases are deprecated dbo.PublishersByPublicationType DEP021 8 String literals as column aliases are deprecated dbo.PublishersByPublicationType DEP021 9 String literals as column aliases are deprecated dbo.PublishersByPublicationType DEP021 10 String literals as column aliases are deprecated dbo.PublishersByPublicationType DEP021 11 String literals as column aliases are deprecated dbo.PublishersByPublicationType ST010 13 Use alias for all table sources dbo.PublishersByPublicationType ST010 14 Use alias for all table sources dbo.PublishersByPublicationType ST010 16 Use alias for all table sources dbo.PublishersByPublicationType PE002 16 Schema name for table or view is not specified dbo.PublishersByPublicationType ST010 17 Use alias for all table sources dbo.reptq1 PE009 4 No SET NOCOUNT ON before DML dbo.reptq1 MI003 6 Unqualified column name ---etc --- dbo.titleview PE002 4 Schema name for table or view is not specified dbo.titleview ST010 4 Use alias for all table sources dbo.titleview PE002 4 Schema name for table or view is not specified dbo.titleview ST010 4 Use alias for all table sources dbo.titleview PE002 4 Schema name for table or view is not specified ---Flyway stuff deleted --- (Microsoft SQL Server 13.0) Schema version: 1.1.8 +-----------+---------+-----------------------------+------+---------------------+---------+ | Category | Version | Description | Type | Installed On | State | +-----------+---------+-----------------------------+------+---------------------+---------+ | Versioned | 1.1.1 | Initial Build | SQL | 2021-03-24 15:03:14 | Success | | Versioned | 1.1.2 | Pubs Original Data | SQL | 2021-03-24 15:03:34 | Success | | Versioned | 1.1.3 | UseNVarcharetc | SQL | 2021-03-24 15:03:45 | Success | | Versioned | 1.1.4 | RenameConstraintsAdd tables | SQL | 2021-03-24 15:03:59 | Success | | Versioned | 1.1.5 | Add New Data | SQL | 2021-03-24 15:04:34 | Success | | Versioned | 1.1.6 | Add Tags | SQL | 2021-03-24 15:04:51 | Success | | Versioned | 1.1.7 | Add Indexes | SQL | 2021-03-24 15:05:04 | Success | | Versioned | 1.1.8 | AddconditionalVersion | SQL | 2021-03-24 15:05:16 | Success | +-----------+---------+-----------------------------+------+---------------------+---------+
For more elaborate ways of viewing and prettifying the codeAnalysis.xml report, see my previous article, SQL Code Analysis from a PowerShell Deployment Script.
Conclusions
I’ve shown what looks at first glance to be a rather complicated approach to scripting. Whatever your approach to scripting, the principles are very similar, and the Script block approach is surprisingly simple but takes some care to make sure that obvious mistakes in the way they are used are easily revealed to the user. It allows Flyway to participate in a deployment in a cooperative manner with other database development and deployment tools such as Redgate’s SQL Change Automation and SQL Compare. It is devised to allow development teams to provide a robust system without becoming overwhelmed by the complexity of the processes.
Tools in this post
You may also like
-
Webinar
Getting more control over database deployments with Flyway
Developers are always looking for ways to automate the continuous delivery of all database changes to guarantee everything happens efficiently and reliably, like they do for application deployments. Join Flyway expert Carlos Robles in this session to explore and discuss the new features of Flyway v7 which make it one of best database version control and migration tools in the market.
-
Article
The Flyway Info Command Explained Simply
If you need the current version of your Flyway database, and a history of the changes that were applied to build that version, then the info command is the place to go. It allows you to review applied and pending migrations, track migration status, and troubleshoot any issues that may have occurred during the migration process.
-
Article
Implementing a Database Versioning System
Database versioning brings more control to team-based database development and helps avoid many of the errors that often creep into database delivery. This article explains all the requirements of a semantic versioning system for databases, from specifying the format of the version numbers, to deciding where to store them and how to process and compare them.
-
Article
The Flyway Desktop CLI: Automate your Database Development Process
We'll explore the Flyway Desktop CLI, also known as "flyway-dev", its capabilities, and how we might use it to automate process of capturing the schema changes made to a development database and then generating a migration script to use in subsequent deployments of those changes.
-
Article
Working with Flyway And Entity Framework Code First: An Overview
This article presents an approach to database development and deployment that combines the strengths of Entry Framework Code First for .NET-driven development with the control and database versioning provided by Flyway's SQL migrations. It allows every database change to be reviewed and tested for integrity, performance, and stability in the same way as any application change. It should make a Database CI process much easier to sustain.
-
Article
Debugging Flyway Callbacks and Migrations that Use Placeholders
Placeholders come in very handy in Flyway, but troubleshooting the SQL migration and callback scripts that use them can be tricky. This articles demonstrates how to develop, test and debug these scripts in a tool designed for these tasks, such as SSMS, leaving Flyway to do what it's designed for, which is running the scripts.
