-
Notifications
You must be signed in to change notification settings - Fork 7
Home
ScriptRunner is a Java-based command line utility for promoting source code and metadata to a database in a sequential and audited manner. It can be executed either standalone or as part of a continuous integration workflow.
The typical workflow for using ScriptRunner to deploy code is as follows:
- A release target is defined for a project, usually represented by a version label (e.g. "MYPROJECTr1.2.1").
- Developers check work in to their VCS and assign it to this version label. This work may include DDL in the form of PatchScripts, which need to run in a predictable order.
- A release manager will use ScriptRunner to build a deployment archive - a zip file containing all the files in the version label. The archive also contains a manifest defining the order of the promote. This is the ScriptRunner builder phase and may be automated by continuous integration software.
- The deployment archive is copied to the relevant environment (a machine with access to the target database). The ScriptRunner runner component is then used to extract the archive, promote the files in the correct order and log the result.
ScriptRunner is invoked from the command line as a Java application. This requires the Java executable to be in the PATH.
java -jar ScriptRunner.jar [args]
The following arguments are used to control which ScriptRunner function is executed. Exactly one must be specified. Additional arguments may be specified depending on which function is in use; see the sections describing those functions for details:
| Argument | Description |
|---|---|
| -build path/to/directory | Invokes the ScriptRunner builder against the given directory. |
| -run path/to/file | Invokes the ScriptRunner runner for the given archive or directory. |
| -install | Installs the ScriptRunner user and log tables on a target database. |
| -update | Updates the ScriptRunner installation to the latest version on a target database. |
| -parse path/to/script1 path/to/script2 ... | Parses one or more PatchScripts at the given location(s) and prints the parser output to standard out. |
Most ScriptRunner functions require a database connection, which may be specified using the arguments below. If ScriptRunner requires a database connection and some database details are not provided, the user will be prompted to enter them on the command line.
| Argument | Description |
|---|---|
| -jdbc "jdbc:oracle:thin:@dbhostname:1521:dbsid" | Specifies the full JDBC Connection String for the database. ScriptRunner gives this argument precedence over the other connection arguments. |
| -host dbhostname | Specifies the database hostname. |
| -port 1521 | Specifies the database port. |
| -sid dbsid | Specifies the database SID. |
| -service dbservice | Specifies the database service name. Only one of SID or service name may be specified. |
| -user username | Optionally overrides the default user to connect as (typically PROMOTEMGR). |
| -password password | Specify the database password for the promotion user. Be aware of the security implications of entering this in plaintext on the command line - if you do not specify this argument here, you will be asked for it when ScriptRunner connects to the database with a "secure" password prompt. |
| -sysdba | If specified, forces the connection to be made as SYSDBA. This should only be used if ScriptRunner encounters a problem when connected normally. |
The following arguments can be used to control how ScriptRunner logs its output.
| Argument | Description |
|---|---|
| -logdir path/to/directory | Overrides the default directory where ScriptRunner creates a log file (the directory it is being executed from). |
| -logstdout | If specified, log output will be printed to standard out in addition to the log file. |
| -logdebug | If specified, verbose debug output will be printed in the log file. |
-build path/to/directory -label VersionLabel [-outfile OutputArchiveName.zip -props path/to/file.txt]
The builder is responsible for creating a deployment archive from the directory specified. The directory should contain only files you wish to deploy for your version label, plus any configuration files - these should be selected by the VCS client.
A configuration file is used to specify which files in the directory should be promoted and what order they should be promoted in - this information is generated and stored in a manifest file within the deployment archive.
When the -build function is invoked, ScriptRunner searches the specified directory for the following files (paths are relative to the root of the directory).
- ScriptRunner/builder*.cfg - (required) the configuration file which the builder will use to search for files to deploy.
- ScriptRunner/manifest-override*.mf - (optional) an manifest override file for overriding the default behaviour of the builder.
- ScriptRunner/Loaders/*.sql - (optional) a set of 0 or more SQL files which can be used to load database metadata.
When invoking the builder, you must specify a version label using the -label argument. The version label is used to uniquely identify a promotion.
The builder will generate a manifest file in the target directory and then zip all implicated files into a single archive. By default this will be named "[version-label].zip" and placed in the current directory. To override this behaviour, use the -outfile argument to specify a destination for the archive.
If there are files in the target directory which are not targeted by the configuration file, a warning message will be printed in the ScriptRunner log (this indicates an omission in the config file).
The configuration file should contain linebreak-separated entries in the following format:
Files: /DatabasePatches/CorePatches/*.sql
Loader: Patch
StartOffset: 10000
FileOffset: 10
Properties: {additional_property="example"}<br/>
Files: /FoxModules/CoreModules/**
Loader: FoxModule
StartOffset: 5000
FileOffset: 10
Properties: {additional_property="example2"}
The instruction order is semi-important - the "Files:" instruction should always be specified first for a group of files. Only the "Properties:" instruction is optional.
The instructions control the build process as follows:
-
Files: specifies a wildcard glob for selecting a set of files which are processed according to the subsequent instructions. The following glob syntax is supported:
- ? - single character wildcard
- * - zero or more characters wilcard, e.g. /directory/*/*.sql
- ** - recursive wildcard, e.g. /**.sql
- Loader: specifies the name of the loader which will be used to load the selected files on to the database. This can be a built-in loader or a loader specified in the ScriptRunner/Loaders directory. The name of the loader corresponds to the name of a loader file with its extension removed, e.g. "FoxModule" will look up a file called "ScriptRunner/Loaders/FoxModule.sql". See #Built-in Loaders for a list of provided loaders.
- StartOffset: controls where the manifest numbering begins for the selected files. The offset is relative to the starting position of the preceding set of files. See #Manifest File for more details.
- FileOffset: specifies the size of the manifest numbering gap between the selected files.
- Properties: optionally specifies additional properties to be assigned to the manifest entries for the selected files.
The following built-in loaders are avaiable:
- Patch - for running PatchScripts
- DatabaseSource - for loading database source, for instance packages, triggers, views etc.
- ScriptRunnerUtil - a special loader for running ScriptRunner utilities, like a "compile all" script. A file passed to this loader will be parsed for bind and substitution variables (passed in from the manifest properties) and then run as a SQL script. Utils are not logged and can be run multiple times - for this reason they may also be included in the manifest multiple times. A util file should NOT make permanent changes to the database.
- Ignore - for excluding files from the builder - see #Ignoring Files
Once it has selected files using the builder configuration rules, ScriptRunner will generate a manifest file for controlling the order of the promote. The format of a manifest file is as follows:
010010: Patch DatabasePatches/CorePatches/PATCHCORE17680 (test 1).sql {file_hash="67a97c11476d31a873d4207c157d8c75", file_version="v1"}
010020: Patch DatabasePatches/CorePatches/PATCHCORE17690 (test 2).sql {file_hash="3268429226dd17e58c0d624646459997", file_version="v1.1.1"}
From left to right, each line specifies for each file:
- The position of the file in the promotion sequence
- The loader name to use to load the file
- The relative file path
- A property map containing properties for each file which can be bound in to a loader. Some properties such as the file hash are automatically generated by ScriptRunner and cannot be overriden.
The position numbering is controlled by the StartOffset and FileOffset instructions in the configuration file. This is designed to leave sufficient "gaps" between files in case an ordering override needs to be specified (see below). The numbering for a set of files starts at the start value for the previous set of files, plus the StartOffset. For instance in the configuration file shown above, Patches start at position 10000 and FoxModules start at position 10000 + 5000 = 15000. These starting positions will be constant for every manifest built with the configuration file, even if a different number of files are selected each time.
The numbering gap between files in the same set is controlled by the FileOffset value. A set of files could potentially "overflow" its allocated space; if this happens an error is raised and the configuration file should be adjusted to prevent it from happening.
You should not need to directly edit the manifest file after it is generated. To make changes, you should use the manifest override and additional properties mechanisms, described below.
If you need to override the way the builder constructs the manifest file for a certain version label, you can create a manifest override file. This allows you to:
- Define additional properties for a file
- Change the loader used to load a file
- Force a file to be promoted more than once
- Change the promotion sequence position of a file
The manifest override file should be added to version label at ScriptRunner/manifest-override*.mf, where * represents a 0 or more character description of the override (usually a version label, e.g. manifest-override-rel1.2.3.mf).
Note: entries in the manifest override must be paths to specific files and cannot contain wildcard characters. If you need to apply properties to files in bulk, you should modify the builder config file.
The syntax for augmentation entries is as follows. To change the loader used:
~ NewLoaderName /path/to/file.xml
To add properties or modify existing properties:
~ /path/to/file.xml {new_property="value", overridden_property="value"}
To do both:
~ NewLoaderName /path/to/file.xml {new_property="value", overridden_property="value"}
To force a file to be promoted more than once, you must specify the position you wish it to be placed in and prefix its filename with the "+" character. E.g.:
50050: LoaderName +/path/to/file.xml {new_property="value"}
The loader name must be specified and can be overridden if required. Additional properties are optional - they can be specified if required in the same way they can for augmentation entries. These overrides will not affect the file definition in the original position (they are only applied to the forced duplicate entry).
These are defined in the same way as forced duplicate entries without the "+" prefix on the file path, e.g.
50055: LoaderName /path/to/file.xml {new_property="value"}
Will move the file from its original position to position 50055. Again, the loader and properties may be overriden here too. The properties are optional.
Care should be taken when overriding positions, especially as more files could be inserted if the version label is rebuilt for whatever reason. ScriptRunner will only raise an error if an overriden position conflicts exactly with the position of another file.
You may wish to supply additional properties for files which will subsequently be passed into loaders. A common use case would be to get the VCS revision number for each file. If this is required the -props argument can be used to specify the path to a file, which could have been generated by the CI environment or VCS client. The additional properties file should reflect the syntax of "augmentation" entries in the following format:
~ /path/to/file.xml {file_version="r1.1", new_property="value"}
Loaders cannot be specified in the additional properties file.
File properties are defined as a comma seperated list of name-value pairs enclosed in braces. Quotation marks should be used to delimit values containing special characters (e.g. "=" and ","). These properties are bound into loaders either as either substitution variables or bind variables; see #Loaders for more details.
As properties can be specified in multiple locations, an order of precedence is defined which controls how they are cascaded. From lowest to highest precedence this order is:
- Default properties from the configuration file
- Properties from the additional properties file
- Properties from an augmentation entry in the manifest override file
- Properties from a position override entry or a forced duplicate entry in the manifest override file
The following property names are treated specially by ScriptRunner:
- file_hash - this must be always be specified in the manifest. It is generated by the builder and is a line break agnostic hash of the contents of a file. It is used by ScriptRunner to verify that files have not been corrupted before promotion, and is also logged on the database.
- file_version - this is optional and is used for logging purposes on the database.
You may have files in your VCS version label which you do not wish to be promoted to the database. To exclude files from the manifest, use the special Ignore loader in the builder config or manifest override. This is defined like a normal loader, but files selected by the loader are "ignored" by the builder and not placed in the manifest file.
At build time ScriptRunner will verify that all files located in the source directory are implicated in the manifest. If files are found in the source directory that are not implicated in the manifest a warning will be displayed. The warning can be promoted to a error (with a non-zero exit code) by passing the command line option `-nounimplicatedfiles`.
-install
Before ScriptRunner can promote files to a database it needs to be "installed" on the database. This involves creating a promotion user (by default PROMOTEMGR) and its associated logging tables. The SQL files for doing this are built in to ScriptRunner.
Important: you must ensure the db_create_file_dest Oracle system parameter is set before performing a ScriptRunner installation. This is because ScriptRunner creates tablespaces in the database's default location.
ScriptRunner must be installed using an existing database user with SYSDBA privileges. By default this will be the SYS user, but you may specify an alternative using the -user argument if you prefer.
Part of the installation process involves migrating PatchScript data from a legacy patch_control_table on the DBAMGR schema. This prevents PatchScripts which have already been run by a previous version of ScriptRunner from running again. If you do not have this legacy table on your target database, this part of the installation process is skipped.
For a description of the log tables, see #Logging.
-run path/to/file [-noexec -noversioncheck -nohashcheck]
The -run function is used to promote files from a promotion archive or directory to a target database. The path provided can resolve to either a zip archive or a directory, which is assumed to contain the same directory structure as the archive would.
ScriptRunner attempts to perform as much validation as possible before attempting to promote any files. However, it is not possible in Oracle to assert that the syntax of a DDL statement is valid without running it, so syntax errors in loaders or PatchScripts will not be detected until the promotion is started.
By default, ScriptRunner will check that the version of ScriptRunner used to create the manifest is the same version currently executing it. To suppress this check, specify the -noversioncheck argument. Furthermore, ScriptRunner will assert that the file hashes correspond to the hashes in their manifest entries. To suppress this check, specify the -nohashcheck argument.
When promoting a version label, ScriptRunner differentiates between loading database source code and metadata, which is promoted using loaders, and patch scripts, which are run in a different way.
PatchScripts and loaders ("scripts") share a common syntax for seperating SQL statements and controlling the state of the database connection.
Statements are delimited as in SQL*Plus by a single "/" character on a new line. A semicolon is NOT a valid delimiter and should not be used unless it forms part of standard Oracle syntax (e.g. BEGIN... END;). See the example below:
CONNECT bpmmgr /<br/> INSERT INTO business_process_definitions VALUES (:file, :clob) /<br/> COMMIT /<br/> DISCONNECT /
Each PatchScript execution or loader invocation will begin with the database connection connected as the promotion user, by default PROMOTEMGR. PROMOTEMGR is a privileged user which is able to perform almost any DML or DDL action required.
Note that all scripts must ensure all object references, etc, are fully schema qualified. PROMOTEMGR has a before-DDL trigger which is used to prevent any objects being constructed on its schema. This trigger will cause the promotion to fail if a schema qualification is omitted from a DDL statement. DML statements will fail as the unqualified objects will not exist on PROMOTMGR (unless they are public synonyms).
There may be some circumstances where you require a statement or set of statements to be executed as a different user. For instance, when submitting a job using dbms_job, the owner of the job is only ever the user executing the statement. If this is the case you may use the CONNECT and DISCONNECT syntax to switch the connection to a different user.
CONNECT username /
The CONNECT statement switches the connection to the specified username. This uses the Oracle proxy connection functionality, so the password for the user is not required (instead it uses the known credentials for PROMOTEMGR).
Any existing connection is implicitly disconnected when a CONNECT statement is executed. This disconnection is only allowed if there is no outstanding transaction on the connection. If there is an outstanding transaction an error is raised. To prevent this from happening you should use the COMMIT statement before performing a CONNECT.
Some statements may require the connection to be connected as the SYS user - for instance, grants to SYS objects such as the dbms_ packages. This can be done in ScriptRunner using the special username "SYSDBA". Special care should be taken when connected as SYSDBA as the DDL trigger described above is not able to prevent accidental DDL on the SYS schema.
You may also use the CONNECT syntax to execute a PL/SQL anonymous block as a certain user. The syntax below will CONNECT as schemaname, run the block and then DISCONNECT implicitly at the end. The same transaction rules apply here: you should make sure the PL/SQL block ends any transaction it starts. Subsequent statements executed after this one in the script will execute as the standard user.
CONNECT schemaname BEGIN INSERT INTO t1 VALUES(1); COMMIT; END; /
DISONNECT /
The DISCONNECT statement switches the connection back to the standard promotion user (PROMOTEMGR). There is no concept of a stack of connections in ScriptRunner so you will always be switched back to the promotion user regardless of how many CONNECTs have been issued before. As described above, you cannot DISCONNECT if a transaction is outstanding - this causes an error to be raised.
There is no need to DISCONNECT at the end of the script as this is done implicitly.
COMMIT /
The COMMIT statement commits any outstanding transaction, just as it would in SQL*PLus or in a PL/SQL block.
A loader is a statement or set of statements which is used to promote an individual file. Each loader script is executed once per file. File metadata, including properties from the manifest, can be bound in to the loader, as well as the file's contents as binary (BLOB) or character (CLOB) data. Properties can also be used as substitution variables, allowing the loader to dynamically change based on the file it is promoting.
An example loader statement is shown below. Note the syntax for a substitution variable:
INSERT INTO $${COMPONENTS_TABLE}$$
VALUES (:name, :clob, :file_version);
/<br/>
COMMIT
/
Any property value from the manifest can be referenced as a bind variable. The following special binds are also available:
- :name - name of the file
- :clob - character contents of the file as a CLOB
- :blob - binary contents of the file as a BLOB
A PatchScript is used for performing arbitrary DDL and DML operations on a database. A PatchScript should only ever be run once due to its potential for making permanent changes to the database structure. ScriptRunner will not run a PatchScript that has already been run, even if it is included in different version labels (see #Logging for more details). Because of this, your VCS should ensure that PatchScripts become immutable after they have been promoted to an environment. However, it is possible to force a PatchScript to run more than once if an unexpected error occurs - see #Error Handling.
A PatchScript's filename is important and is composed of three parts:
PATCHLABEL000010 (description).sql
- PATCHLABEL specifies a "group" for the patch, typically a system name.
- 000010 represents an arbitrarily long 0-padded number which denotes the sequence of the patch. Patches within a patch label are always executed in this sequence. The sequence in which patch labels are executed is determined by the manifest. Note: all patch numbers in a patch label should have the same 0-padding ength, so the manifest builder does not have to be specially manipulated to order the patches correctly.
- The description in parentheses should be a concise overview of what the patch is for. This is logged in the PROMOTEMGR log tables.
ScriptRunner logs every promotion action it performs in one or more log tables on PROMOTEMGR. The following tables are used:
| Table Name | Description |
|---|---|
| promotion_labels | Top level master record for every individual version label which has been promoted to the database. |
| promotion_runs | Log table for every individual execution of a version label promotion (i.e. a usage of the ScriptRunner -run function). |
| promotion_files | Detail table logging every file promoted during a promotion run (excluding PatchScripts). |
| patches | Top level master record for every PatchScript which has ever been run on the database. |
| patch_runs | Log table for every individual execution of a PatchScript. |
| patch_run_statements | Detail table logging every statement executed during an individual execution of a PatchScript. |
After a version label has been promoted, ScriptRunner will refuse to promote the same version label again unless the ignore_flag has been set on its row in the promotion_runs table, described below.
A version label should be promoted from start to end without any failures. However, this may not always be possible due to differences between live and test environments. If ScriptRunner encounters any error during a promotion it will immediately cease execution, mark all relevant log rows in the log tables as FAILED and exit with an error code. A full stack trace is printed to the log, and logged on the database, and the error description is printed to standard out. If it was not possible to log the error on the database then this will be reported as a message in the log file.
Promotions are designed to be 'resumable' from the point of failure. Depending on the exact nature of the error this may require the promotion archive to be re-built (for instance if a PatchScript needed to be modified). Once the cause of the problem is assessed and remedied, the promotion can be resumed. Be careful when resuming promotions: you should always check the resume will behave as you expect by using the -noexec flag, described below.
Resuming a promotion involves setting "ignore flags" on one or more log table entries. You must ensure you set all the required ignore flags, otherwise the promotion may resume but not promote all the files you were expecting. When you have set the flags you should invoke the ScriptRunner -run function as you normally would. It will check the log tables and start the promotion from the correct point, accounting for any ignore_flags.
If an error occurred which does not require any rectifying (i.e. you are ignoring the erroneous file/PatchScript), simply set the ignore_flag column on promotion_runs to "Y", on the row corresponding to your version label. I.e.:
UPDATE promotemgr.promotion_runs SET ignore_flag = 'Y' WHERE version_label = :label AND status = 'FAILED' /
Warning: Always check the -noexec report before resuming a promotion.
If a file (not a PatchScript) failed to be promoted, and you have rectified the problem and require the promotion to re-promote the offending file (or potentially files before it), you should set the ignore_flag on the relevant rows of the promotion_files table. Example:
UPDATE promotemgr.promotion_files SET ignore_flag = 'Y' WHERE promotion_run_id = :1 AND status = 'FAILED' /
Note that the "file index" of a file is used when deciding whether to promote it. This refers to the index of a file amongst other files of the same name within a promotion, when using forced duplicates (i.e. if the file is promoted more than once, the first occurrence will have index 1, the second index 2, etc). When setting an ignore flag, ensure you are setting it on the promotion file with the correct index.
PatchScripts are executed and logged in a different way to other files. Each individual statement in a PatchScript is hashed and each statement execution is seperately logged. To resume a PatchScript you need to set the ignore_flag on the patch_runs table. This will cause the PatchScript to resume execution from the first unexecuted statement.
UPDATE promotemgr.patch_runs SET ignore_flag = 'Y' WHERE status = 'FAILED' AND patch_label = :label AND patch_number = :number /
If you wish to re-execute certain statements within the PatchScript, you need to set the ignore_flag on the relevant entries in the patch_run_statements table.
UPDATE promotemgr.patch_run_statements SET ignore_flag = 'Y' WHERE status = 'FAILED' AND patch_label = :label AND patch_number = :number AND patch_run_id = :id /
If you are editing PatchScripts before resuming a promotion, be aware that you may be altering the hash of a statement in the PatchScript. If this happens ScriptRunner will treat the statement as a new statement and execute it. This may be desirable behaviour (for instance if you need a new statement to be executed) but you should use the -noexec flag to verify the promotion before resuming.
The -noexec flag executes ScriptRunner but prevents files from being promoted to the database. Instead, a report is generated and printed to the log file which describes which files will be promoted and which will be skipped due to a previous promotion. For PatchScripts, if the PatchScript is to be resumed, each statement from it is printed to show which statements will be executed and which will be skipped.
It is suggested that the -noexec flag is used in advance of every promotion, to ensure all the expected files will be promoted, and in the correct order.
Sample output:
Sequence Description Result Details ---------------------------------------------------------------------- 00010010 PATCHCORE 10 SKIP Already run 00010180 PATCHCORE 20 PROMOTE Full run 00010190 PATCHCORE 30 SKIP Already run 00010200 PATCHCORE 40 PROMOTE Partial re-run; see below 00000001 Statement 1 SKIP CREATE TABLE t1 (n NUMBER) 00000002 Statement 2 SKIP CREATE TABLE t2 (n2 NUMBER) 00000003 Statement 3 PROMOTE CREATE TABLE t3 (n3 NUMBER)
-update
Before running a promotion, ScriptRunner checks that its metadata tables on the target database are in the state it expects them to be. If they are not, the program exits with an error reporting the problem. The ScriptRunner JAR contains the patch scripts required to update the metadata tables to the correct state. The -update command can be used to execute these scripts. After a successful update, ScriptRunner can be used to do promotions again.
Note: if the version of the metadata tables on the database is greater than the version of ScriptRunner in use, it will not be possible for ScriptRunner to run any promotions on that database. You will need to use the version of ScriptRunner corresponding to the version of the metadata tables.
-parse /path/to/path.sql /path/to/patch2.sql ...
The -parse function is used to parse PatchScripts in advance of a promotion, so developers can assert that what they have written will be executed as expected. The command takes an arbitrary number of paths as arguments which should resolve to valid PatchScripts. The PatchScripts are then parsed and the split file is output to standard out.
If the parsing of any PatchScript results in an error, this will be printed to standard out and ScriptRunner will return an error code.