Factory Fabric DB Validation Command Line Tool

Challenge Objectives
The purpose of this challenge is to build a generic command line tool that will read test cases from a configuration database, execute them and validate the execution results.
 
You’ll be expected to work with Oracle 11g R2 database and build the command line tool using Java 1.8, Spring and Apache JEXL.
 
Project Background
The goal of the project is to create a generic tool to automate the complete process of SQL Query execution, verify the output, feed outputs to next steps, determine success of test-case and save the results back to a result table.
 
Tool Execution Flow
The execution flow of the tool looks like this:

1. Configuration for all test cases is loaded into the CT_DEFINITION table manually
2. The tool will read all test cases from the CT_DEFINITION table for a given set of test suites or test cases (passed in through command line parameters), and decide how to run each of the test cases, to do that the tool need to:
   1. Group the rows selected from the CT_DEFINITION table by TEST_CASE_NAME, rows with the same TEST_CASE_NAME should belong to the same test case
   2. Within each test case group, rows should be ordered by STEPS, i.e. test case execution should start from step 1
   3. If any step has IS_ACTIVE_FLAG set to 0 the entire test case will be skipped. No need to execute the other steps in the test cases.
   4. For each test case, SRC_QRY column is the sql query to be executed, OUTPUT_AND_PARAM indicates the output and parameters and VALIDATION defines the rules to validate the step
3. For each test case, the tool will execute each step like this:
   1. Feed parameter name from previous step (if any) and execute the sql query
   2. Capture output in specified parameter name
   3. Verify the output with the validation rules
   4. If verification fails or there is error executing the step, then the whole test case is marked as failed and there’s no need to continue with the rest of the steps
   5. If all steps pass verification then the test case is considered successful
4. The tool writes test case result into CT_RESULT table
   1. RESULT_ID should be an auto-increase id
   2. EXECUTION_PHASE and ITERATION_ID should be configurable values in tool’s config file
   3. Status will be either “Success” or “Failed”
   4. EXECUTION_DATE should be the test case execution timestamp
   5. For other columns just copy values from the CT_DEFINITION table, ignore any other columns that cannot be found in CT_DEFINITION table.

 
We have provided sample database setup and some test cases for you to better understand this flow, please check base_load.sqlfor details, both test cases in this setup should be success.
 
Challenge Scope
This challenge will implement a tool that works as described above, more specifically:

1. The tool must be implemented as a standalone command line tool using Java 1.8, Spring and Apache JEXL
2. The tool must support configuration of the following for the user.
   1. The tool should support configuration for up to 4 different databases:
      1. Configuration database: this is the one that contains CT_DEFINITION and CT_RESULT tables
      2. Source database: in one of the samples, this is the database that contains table FTC_AGREEMENT
      3. Target database: in one of the samples, this is the database that contains table T_AGREEMENTS
      4. Pan database: in one of the samples, this is the database that contains table AGREEMENTS
   2. Configuration for all source / target / pan database tables so the tool knows which table belongs to which database
   3. EXECUTION_PHASE and ITERATION_ID fields
3. The tool must accept command line parameters that will specify one or more test suite names or test case names
4. The tool must be generic enough to work with any test cases the users may configure in the CT_DEFINITION table, you can assume that:
   1. Test cases are always fully defined in the CT_DEFINITION table, i.e. you don’t need anything else to get the test cases that you can run
   2. Test case results will always be written into CT_RESULT table
   3. They key columns of a test case step are SRC_QRY, OUTPUT_AND_PARAM and VALIDATION. Your implementation must be able to run the query, capture the output and save the values into the specified parameters and keep them for usage in subsequent steps (you’ll need to use Apache JEXL for this). And finally validate if output meets the validation rules. Please note parameters are only valid within a test case.
5. Make sure your code is properly documented
6. You should create more test data with different steps and validation rules to verify your submission, and include such test data as part of your submission
7. Use Maven as build system, your submission should not require any IDE to run.

 
Technology Stack

• Java 1.8
• Spring
• Apache JEXL
• SQL
• Oracle 11g R2

Final Submission Guidelines

Final Deliverables

• All original source code including maven build scripts.
• A readme in markdown format explaining how to configure, build and run your submission.
• A verification guide in markdown format explaining how to test and verify your submission. Submitting a video is NOT acceptable.