SQL Compare Launcher (sql_compare.jar)

Configuration

Requirements

• If necessary, connect to a remote host using plink with specific credentials (in the configuration) and execute the command specified (as well in the configuration)
• Extablish 2 separate connections to the databases specified in the confiiguration
• Execute the sql script <sqlScriptRootPath>/<testPath>/<testName>_<testcaseIndex>_a.sql on the first database
• Execute the sql script <sqlScriptRootPath>/<testPath>/<testName>_<testcaseIndex>_b.sql on the second one
• Compare both the resultsets

Tutorial: Creating and executing SQL Compare tests

Prerequisites

CREATE TABLE table_stock_1 (
  article_id INTEGER,
  article_name TEXT,
  article_color TEXT,
  article_nb INTEGER,
  PRIMARY KEY(article_id)
);

INSERT INTO table_stock_1 VALUES
(1, 'Hat', 'Red', 10),
(2, 'Hat', 'Blue', 4),
(3, 'Hat', 'Green', 17),
(4, 'Sweat-shirt', 'Red', 5);

CREATE TABLE table_stock_2 (
  article_id INTEGER,
  article_name TEXT,
  article_color TEXT,
  article_nb INTEGER,
  PRIMARY KEY(article_id)
);

INSERT INTO table_stock_2 VALUES
(1, 'Hat', 'Red', 10),
(2, 'Hat', 'Blue', 4),
(3, 'Hat', 'Green', 17),
(4, 'Sweat-shirt', 'Red', 5);

CREATE TABLE table_stock_3 (
  article_id INTEGER,
  article_name TEXT,
  article_color TEXT,
  article_nb INTEGER,
  PRIMARY KEY(article_id)
);

INSERT INTO table_stock_3 VALUES
(1, 'Hat', 'Red', 10),
(2, 'Hat', 'Blue', 3),
(4, 'Sweat-shirt', 'Red', 6);

• Create the text file C:\test_repository\tests\sql_compare\folder1\test1_1_a.sql and write in it:
  SELECT * FROM table_stock_1;
• Create the text file C:\test_repository\tests\sql_compare\folder1\test1_1_b.sql and write in it:
  SELECT * FROM table_stock_2;

• Create the text file C:\test_repository\tests\sql_compare\folder1\test1_2_a.sql and write in it:
  SELECT * FROM table_stock_1;
• Create the text file C:\test_repository\tests\sql_compare\folder1\test1_2_b.sql and write in it:
  SELECT * FROM table_stock_3;

Create a dedicated category for SQL Compare tests and create a test

• create a category SQL Compare associated to the launcher sql_compare.jar
• under this category, create (somewhere in the tree) a test with name test1 and with a canonical path set to folder1.
• create 2 test cases under this test with the name you prefer but necessarily with index 1 and 2 (this is what is used to find the right script on disk).

Creating a test campaign

• create a campaign including only the test tabledemo
• create a campaign session specifying in the configuration:
  • sql scripts root path: C:\test_repository\tests\sql_compare
  • the same driver, classpath and credential to connect to the test database (connection 1 and 2)

Run a campaign session