DBPatch is an Ant task that brings the state of the target database in sync with the local patches. It is usually run locally as a part of the build process to update a local development database or it can be run before deployment to update a test or production database. Read more about patch creation strategies and including the Antlib here.
DBPatch uses a special database table to track which patches were already applied. Such table is created automatically if it is missing, so it requires no preliminary setup. However if the database user id used to run DB patches does not have enough permissions to create tables in the target table space, a user must take care of setting it up. The default table structure is the following (table and column names can be customized, see parameter definitions for details):
CREATE TABLE patch_tracking( ID INTEGER NOT NULL PRIMARY KEY, PATCH_NAME VARCHAR(100), PATCH_RELEASE_ID VARCHAR(20), APPLIED_TIMESTAMP TIMESTAMP, PATCH_UTILITY_VERSION VARCHAR(20) )
| Attribute | Description | Required |
| patchindex | A text file that contains the names of the patches to run in the order they should be executed, one file per line. Empty lines and lines starting with # or // are skipped. The rest are treated as file names in the directory relative to the patch index file directory. | Yes |
| patchreleaseid | Defines a namespace for patch file names. All patch files must have unique names for a given patch release id. | No - default is empty |
| patchtablename | An attribute that defines the name of the table where patch tracking information is stored. Can contain schema name. E.g. "myschema.mypatchtable". | No - default is 'patch_tracking' |
| driver | Class name of the jdbc driver | Yes |
| url | Database connection url | Yes |
| userid | Database user name | Yes |
| password | Database password | Yes |
| encoding | Encoding of the SQL patch files | No - defaults to default JVM encoding |
| delimiter | String that separates SQL statements | No - default ";" |
| autocommit | Auto commit flag for database connection | No - default "false" |
| showheaders | Print headers for result sets from the statements | No, default "true" |
| showtrailers | Print trailer for number of rows affected | No, default "true" |
| classpath | Classpath used to load driver | No (use system classpath) |
| classpathref | The classpath to use, given as a reference to a path defined elsewhere. | No (use system classpath) |
| delimitertype | Control whether the delimiter will only be recognized on a line by itself. Can be "normal" -anywhere on the line, or "row", meaning it must be on a line by itself | No (default:normal) |
| keepformat | Control whether the format of the sql will be preserved. Useful when loading packages and procedures. | No (default=false) |
| escapeprocessing | Control whether the Java statement
object will perform escape substitution. See Statement's API docs for details. Since Ant 1.6. | No (default=true) |
Running dbpatches listed in "patches/index.txt":
<dbpatch
patchIndex="patches/index.txt"
patchReleaseId="${version}"
driver="com.mysql.jdbc.Driver"
url="${db.url}"
userid="${db.userid}"
password="${db.password}"
classpathref="my.drivers.ref"/>