There are several unit testing frameworks available for unit testing Oracle PL/SQL code. Some of the populate unit testing frameworks are utPLSQL, ruby-plsql-spec and PL/Unit. Personally I would recommend using ruby-plsql-spec as it takes the Behavior Driven Development (BDD) unit testing approach. It also provides test execution and coverage reporting, which is really very useful.
- Install Ruby or JRuby if you come from a Java background.
- Install ruby-plsql-spec gem; Gems is a package manager for Ruby.
gem install ruby-plsql-spec
- In your project directory, setup the test directory. This will create sub-folder named spec in which all supporting files will be generated. Database details need to be configured in database.yml
- All unit tests should be created in the spec directory and name should end in ‘_spec.rb’.
- The following PLSQL function is to be tested, it parses a string and returns a number if it’s valid, else it returns null.
CREATE OR REPLACE FUNCTION PARSENUM( input IN VARCHAR2 )
RETURN NUMBER IS
WHEN OTHERS THEN
- Create a unit test parsenum_spec.rb as follows:
describe “parsenum test” do
it “should not get null for valid number” do
plsql.parsenum(‘100’).should_not == NULL
- To run the test, execute the following:
- On successful execution the following message will be displayed.
Running all specs from spec/
Finished in 0.033782 seconds
1 example, 0 failures
- To generate coverage reports, use the following command:
plsql-spec run --coverage
You can see more examples here:
In this post I’m going to explain how we used Flyway for database migrations in one of our projects. Flyway allows version control for databases, hence, easing the process of release maintenance & deployment.
To read more about Flyway:
Flyway creates a meta-data table for maintaining the migration versions.We used SQL migrations and ant-based execution style. In the migration directory, sub-directories for different database objects were created, for modularity.
migrations/table – all table scripts
migrations/sequence – all sequence scripts
migrations/trigger – all trigger scripts
The separation of database objects also helps in maintaining execution order, when you have dependent objects; for example a package depends on some tables. As the versioning in Flyway is custom, meaning, it is done on the basis of the script file-name, we introduced codes in the file-name for indicating the database object for which the migration was applied; allowing more clarity in the meta-data table.
10 -> code for table
One major customization we did was for reverts. Flyway does not provide any revert functionality for rollbacks.
Hence, we created a custom ant task for managing reverts. This needed a rollback folder, just like the migrations folder for applying migrations, and the same sub-directory structure.
The corresponding rollback file-name would be R1__10_Person.sql.
This custom ant task would apply all rollbacks in the order as well as clean-up the Flyway meta-data table for the migrations that were rolled-back.
To make this full agile, we created a single migration shell-script and then integrated it in our Jenkins build.
Flyway really helped us improve on the deployment & release process, and also enabled better collaboration in maintaining database changes.
Database migration is a term for evolving databases in an agile environment. It is basically version control for your database so that releases can be controlled and managed. It has nothing to do with migrating from one database to another. 🙂
Martin Fowler and Pramod Sadalge talk in detail about agile database development in this podcast:
There are many database migration tools available, the following comparison table might help you make a better choice: