Introduction

Variable substitution feature allows for runtime substitution of values such as tablespace names or data types, script generations. These variables can also be either defaulted (by defining in the migration.xml) or be required to fill out before allowing a migration run.

Some common examples of why variable substitutions are useful include the ability to make portable sql scripts by defining types as variables instead of absolutes.

Example of Enhanced Portability

Lets take for instance a simple oracle script.

CREATE TABLE person (
 id NUMBER NOT NULL,
 name VARCHAR2(50),
 birth_dte TIMESTAMP,
 register_dte DATE DEFAULT sysdate
);

The script is perfectly valid for oracle systems. But what if you wanted to run this on mysql? It is only a few minor changes but it is still tedious to have to make such changes before running the sql scripts. With variables you could instead make the script look like

CREATE TABLE person (
 id #number() NOT NULL,
 name #string(50),
 birth_dte #datetime(),
 register_dte #date() DEFAULT #now()
);

and then you can default these variables in the migration.xml to keep it convinient for oracle users but still allow other systems like mysql to work.

Defaulting Variables Values

To default a variable, simply add to the migration.xml configuration the following section

  <properties>
    <string>VARCHAR2($1)</string>
    <number>DOUBLE</number>
    <date>TIMESTAMP</date>
    <datetime>TIMESTAMP</datetime>
    <now>sysdate</now>
  </properties>

and oracle users will still work fine while mysql users at runtime could change the values to match there environment. So at runtime a mysql user might change the properties to be

  • string=VARCHAR($1)
  • number=INTEGER
  • date=DATE
  • datetime=DATETIME
  • now=now()

Also note that parameters passed into the variable functions are accessed like $1, $2, $3, etc. They are comma separated when passed in a value. So something like #number(9, 4) might be defined as NUMBER($1, $2) as the value for some databases and as DECIMAL($1, $2) or other systems. And if a system decides they want that to be INTEGER($1) only that is fine also. The 2nd parameter is simply ignored in that case.

Mandating Variable Substitutions

Requiring a variable to be filled out is as easy as simply not listing it in the properties section of the migrations.xml configuration. The above example for instance defaulted them to an oracle schema. But if your scripts are intended to be used by multiple environments, you might leave some or all blank in order to require it be populated prior to migration execution.

Most enterprise systems however only have a fixed database so a more common example of where this might have meaning in an enterprise is with such things as db links, grants to users, synonyms, tablespace names, etc.