OneScript

OneScript is used on software development projects to automate the creation of a single SQL release script file from individual SQL change script files that are managed in source control.

Process Overview

Developers create individual SQL change script files each with the same name as the database object that it creates or alters. These are checked into source control folders named after the database object's type. For example, "Tables", "Views", "Stored Procedures".



As an example, adding a column to the customer table might involve the following change script files:

/Tables/Customers.sql
/Stored Procedures/GetCustomer.sql
/Stored Procedures/SaveCustomer.sql

The original content in the "/tables/Customers.sql" file was the CREATE TABLE command. Since this has been deployed to production and will never be used again we overwrite it with our new ALTER TABLE command to add the new column. This may seem counterintuitive but it results in the history of the script files in source control containing the history of changes to the database objects and it also reduces the number of active scripts that need to be managed.

Developers use this same approach (and tools) when scripting out changes for database objects, database code, and static data. These change scripts can be for SQL Server, Oracle, MySQL or any database that is changeable with scripts. There isn't the extra step of translating changes into XML format.

The next step is to use OneScript to pull all the newly added or modified change script files from source control, sort them based on the folders that they are in ("Tables", "Views", "Stored Procedures", etc.), and then combine them into a single release script file that can be applied to a database to do a deployment. OneScript allows for exceptions to the default file filters and default sort orders.

The fact that only scripts committed to source control can be included in a release script file leads to better control and a complete audit trail. This may also be required to meet regulatory or compliance requirements.

After OneScript generates a release script file it should be applied to a recent copy of the production database and then the changes should be tested. Any failures or changes to the change scripts should restart the process.

Eventually the same exact release script file that was applied to all of the pre-production environments and tested is used to deploy the changes to the production database. This makes a successful release much more likely and ensures that you are releasing what has been tested.

Example

We blogged (here) about how to use OneScript with PowerScript to automatically do the following:

  1. When code is checked in to Subversion run (using a hook) OneScript's command-line to create a release script file containing all of the changes scripts files created or edited since the branch was created.
  2. Restore a resent backup of the production database to the Deployment Test SQL Server instance.
  3. Apply the release script file that OneScript just generated to the test database.
  4. Determine if applying the release script file to the database had any errors.
  5. Send an email to the developers with “Success” or “Failed” in the subject line. Have the "process log" as the body of the email and have the results of applying the SQL release script file as an attachment.

If the apply was successful than the database can be used to run automated unit tests.
You can read the blog posts here and download all of the PowerShell code here.

OneScript Features

Command-Line

A release script file can be generated from the OneScript command-line. You normally define a filter like "include all files that have changed since the branch was created" or "include all files changed since a certain date/time". The OneScript Windows application can be used to make selections that build the command-line options that you want.




The fact that a release script file can be generated from a command-line means that OneScript can be used as a part of an automated build process and will work with different build tools such as TeamCity, MS Build, Jenkins or Hudson.


OneScript Windows Application

Everything that you can do from the command-line you can also do from the easy to use OneScript Windows application. Also on the rare occasions that you need to, you can easily include or exclude specific change script files or override a change script file's default sort order. OneScript filters and displays data directly from your source control as you are editing these overrides.






Works with Different Types of Source Control

OneScript works with:
  • Subversion (SVN) - local or remote
  • Team Foundation Server (TFS) - on-premise or off-premise
  • Files on a disk or shared drive (for GIT, etc.)

Automatically Inject SQL into the Release Script File during a Build

For each of your projects you can define SQL that gets written into the generated release script file along with the individual change scripts. You can define SQL to be written at the start/end of the generated release script file and SQL to be include before/after each included change script file.





OneScript has a lot of substitution placeholders that can be used in the injected SQL. Below is an example of a SQL template that will be valued and inserted before each included change script file.



The placeholders will be replaced with values from source control. So the %f will be replaced by each included change script file's filename right before the file's content is copied into the generated release script file.

This example injects SQL that prints the source control details for each included change script file right before that file's changes are applied to the database. It is used to immediately identify any change script file that cause an error during deployment.



The ability to inject SQL with placeholders opens up many possibilities for auditing and control.

Licensing

A license is required for each client computer that will run OneScript.
Each license includes:
  • Ability to run OneScript Windows application
  • Ability to run OneScript command-line to generate release script files
  • Unlimited OneScript projects
  • Unlimited source control connections
  • Unlimited OneScript database server connections