OneScript

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

OneScript automates this process which is usually time consuming, tedious, and error prone. Your developers will be more productive.

A database release script can be generated from OneScript's easy to use Window’s application or from its command-line program. The ability to run it from the command-line allows it to be a part of an automated or continuous integration process.

The SQL change scripts can be for SQL Server, Oracle, MySQL or any database that is changeable with scripts.


Source Control Centric

During development, developers make individual SQL change scripts and commit them into source control. They commit them into folders based on the type of database object that the script changes. All table scripts are in the same folder, and all stored procedure scripts are in a different folder, etc.




To generate a release script OneScript reads these scripts from source control, filters them, sorts them, and then combines them together into a single release script. The fact that only scripts committed to source control can be included in a release leads to better control and a complete audit trail.

Developers use the same approach and tools to script out changes for database objects, database code, and static data. They do not have the extra step of translating changes into XML format.


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.)

Build Command

A release script can be generated by using the easy to use Windows application or from the command-line. You normally define a filter like "include all files that have changed since a branch was created" or "include all files changed since a certain date/time".

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




Sort Order

You assign a sort order to each source control folder name (Tables, Data, Stored Procs, etc.). At build time, OneScript defaults to sorting individual change script files based on their folder’s sort order and then alphabetically by its file name.




Override Defaults

On the rare occasions that you need it, you can easily include or exclude specific files or override a file's default sort order.


Inject SQL into the Release Script

For each of your projects you can define SQL that gets written into the generated file along with the change scripts. You can define SQL to be written at the start/end of the generated release script and SQL to be include before/after each included 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 injected before each included file.



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

This example injects SQL that is used to immediately identify the included change script file that caused an error during deployment.




Recommended Release Process

When a OneScript release file is generated it should be applied to a recent copy of your 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 your production database. This makes a successful release much more likely and ensures that you are releasing what has been tested.