Saturday, April 11, 2015

CI with SQL*Developer, Jenkins, and GNU make

In a previous post (Integrating Jenkins and SQL*Developer's Unit Test), I showed how JUnit ouptut can be created from SQL*Developer and captured by Jenkins. In this post, I will show how to use GNU "make" as the build engine with Jenkins and SQL*Developer.

(UPDATE 17-Apr-2015: Fixed formatting errors)

GNU make

GNU "make" is included with the GNU software that is part of Linux.  I found GNU "make" an easy to use and reliable build engine.  I originally wrote my build scripts in "bash".  Switching to GNU "make" greatly simplified the build scripting and provided much more functionality than my "bash" scripts.  More information about GNU "make" can be found at

What does "make" do?  It reads the "Makefile" to determine what recipes must be used to update targets that may have dependencies.  Specifically, the "Makefile" in the ZIP file does this:
  1. Create/Update all the needed database objects and data in a database.  In this example, the database object are created from an SQL*Developer Cart created for this purpose.
  2. Create/Update all the needed tests.  In this example, the tests are created from an SQL*Developer Unit Test Suite export created for this purpose.
  3. Execute the Unit Test Suite and create a JUnit output for Jenkins.  In this example, the JUnit output is created from an SQL*Developer Report created for this purpose.
  4. Only execute the necessary pieces when something changes.  In this example, dependency/target relationships are handled with the built-in dependency capability of "make" using the "Makefile" created for this purpose.
Supporting Scripts

All of the files/scripts used for this example are in this file.  The list of files in the ZIP file is described in the README.txt that is also in the ZIP.

Some things to consider:

In order to accurately update database objects, the supporting scripts will delete database objects so they can be re-created.  In particular, the "drop_all.sql" script will drop all objects in a schema.  Since this is run on a newly created schema, it should not be a problem to remove all objects.

Also, the "delete_utr.sql" script will remove all data associated with a Test Suite.  This includes the results from previous runs of the Test Suite.  On a build server, this would not be a problem, but may be unexpected on a workstation.  Again, the installation procedure creates a new Unit Test Repository for this example, so it should not be a problem to remove all data.

Additional Prerequisites:

  • GNU utilities like make, sed, grep  (I am using Cygwin on Windows7)
  • Oracle database (I am using Oracle11g Express Edition)
  • SQL*Developer  (I am using 4.0.3)
  • Jenkins (I am using "java -jar jenkins.war --httpPort=8081" so it doesn't interfere with Oracle APEX on port 8080)

Before Running Jenkins:

It is important to get the build working before using it with Jenkins.  For Cygwin on Windows7, the "make" command should be run in a "Command Prompt" window, not a "Cygwin Terminal".  The "make" utility and accompanying scripts should properly isolate the differences between Windows and POSIX (Linux).
  1. Unzip the ZIP file into any directory
  2. Review the README.txt file
  3. Follow the "Installation" instructions
  4. Follow the "Testing" instructions
Running Jenkins:

1) Open the local Jenkins website (My URL is HTTP:// and create a new job.

2) Select Freestyle and Enter a name.

3) We won't be using Source Code Management in this example

4) Add a Build Step

5) Select "Execute Windows batch command" (Note: I am using Cygwin on Windows7.  For Linux, use "Execute shell")

6) Enter "cd" to confirm where the blog1 workspace is located. (Note: I am using Cygwin on Windows7.  For Linux, use "pwd -P")

7) "Save" the project, click "Build Now", click the down arrow next to "#1", then click "Console Output"

8) The Console Output reveals the location of the job workspace (C:\Users\Duane\.jenkins\jobs\blog1\workspace)

9) Copy the working set of unzipped files into the blog1 workspace.  Remove the "*.done" files (blog1_cart.done, import_utr.done, and runtest.done) to reset the build process.  This step would normally be accomplished automatically by Source Code Management like Subversion.  The "*.done" files would not be included in the SCM Repository.

10) In the browser, click "Back to Project", click "Workspace" and confirm the files are in place.  (All these files are not necessary, as this is an example listing.)

11) Click "Configure" and change "cd" to "make all".

Note: When running in Linux, environment settings for Java and Oracle XE may be needed.  I did not need these for Cygwin on Windows7.
export JAVA_HOME=/usr/lib/jvm/java-7-oracle export PATH=$JAVA_HOME/bin:$PATH export ORACLE_BASE=/u01/app/oracle export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/xe export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH export PATH=$ORACLE_HOME/bin:$PATH export NLS_LANG=`$ORACLE_HOME/bin/` export ORACLE_SID=XE

12) Click "Add post-build action"

13) Select "Publish JUnit test result report"

14) Add "junit_report*.xml" for "Test report XMLs".  This will properly capture the "junit_report_FIT_blog1.xml" output from our testing.

15) Save these changes.  The, as in step 7, click "Build Now", then "Console Output".  The test should end with "Recording test results" and "Finished: FAILURE".  As indicated in the README.txt, there is an error included in the testing.  So, the test will show as failed.

16) To see if the test results were properly captured, click the down arrow next to build number like in Step 7.  The menu should have an extra item "Test Result" that was not in Step 7.  Click it.

17) Test Results from that build are displayed:

Under "All Failed Tests", the "Test Name" that failed can be broken down to the following into the following components:
  • PL/SQL Package being tested: EMP
  • PL/SQL Procedure begin tested: REORG
  • SQL*Developer Unit Test Name: Reorganization Test
  • Unit Test Implementation Name: Test Implementation 1
  • Implementation Sequence: 2_RUNTEST
Implementation Sequence "2_RUNTEST" is 2 of 4 Implementation Sequences:
  • 3_VALIDATE01 - Where 01 is the Validation Sequence

NOTE: Under "All Tests", the package "SQL*Developer_Test_Suite" does not actual exist as a PL/SQL package.  This package name is used to collect the "Test_Suite_STARTUP" and "Test_Suite_TEARDOWN" results that are run at the Test Suite level.  The "Class" for this package is the name of the Test Suite.  Otherwise, as a general rule, "Class" is the name of the procedure/function being tested.

18) Click on the failed test "EMP.REORG.Reorganization Test:Test Implementation 1:2_RUNTEST"

The "Stacktrace" information is particularly useful here.  The error indicates a foreign key constraint violation on "BLOG1.BOSS".  In fact, the "I_BOSS" value of 22 is incorrect.  To fix this test, change that value to any "EMPNO" value from the "EMPLOYEE" table (like 7654).

Hopefully, this was a successful start to Continuous Integration with SQL*Developer, Jenkins, and GNU "make".  Thank you for reading,

1 comment: