
package demos.columnImpact.test;

import gudusoft.gsqlparser.EDbVendor;
import junit.framework.Assert;
import junit.framework.TestCase;
import demos.columnImpact.ColumnImpact;

public class ColumnImpactTest extends TestCase
{

	public void testCrashSQL( )
	{
		String sqltext = "select job,\r\n"
				+ "max( decode( deptno, 10, cnt, null ) ) dept_10,\r\n"
				+ "max( decode( deptno, 20, cnt, null ) ) dept_20,\r\n"
				+ "max( decode( deptno, 30, cnt, null ) ) dept_30,\r\n"
				+ "max( decode( deptno, 40, cnt, null ) ) dept_40\r\n"
				+ "from ( select job, deptno, count(*) cnt\r\n"
				+ "from emp\r\n"
				+ "group by job, deptno )\r\n"
				+ "group by job;";
		ColumnImpact impact = new ColumnImpact( sqltext,
				EDbVendor.dbvmssql,
				true,
				false );
		System.out.println( impact.getImpactResult( ) );
		Assert.assertTrue( impact.getImpactResult( )
				.equals( "job depends on: emp.job, emp.deptno\r\n"
						+ "dept_10 depends on: emp.job, emp.deptno, emp\r\n"
						+ "dept_20 depends on: emp.job, emp.deptno, emp\r\n"
						+ "dept_30 depends on: emp.job, emp.deptno, emp\r\n"
						+ "dept_40 depends on: emp.job, emp.deptno, emp\r\n" ) );

	}

	public void testWithCTESQL( )
	{
		String sqltext = "WITH dept_count AS (\r\n"
				+ "SELECT deptno, COUNT(*) AS dept_count\r\n"
				+ "FROM   emp\r\n"
				+ "GROUP BY deptno)\r\n"
				+ "SELECT e.ename AS employee_name,\r\n"
				+ "dc.dept_count AS emp_dept_count\r\n"
				+ "FROM   emp e,\r\n"
				+ "dept_count dc\r\n"
				+ "WHERE  e.deptno = dc.deptno;";
		ColumnImpact impact = new ColumnImpact( sqltext,
				EDbVendor.dbvmssql,
				true,
				false );
		System.out.println( impact.getImpactResult( ) );
		Assert.assertTrue( impact.getImpactResult( )
				.equals( "employee_name depends on: emp.deptno, emp.ename\r\nemp_dept_count depends on: emp.deptno, emp\r\n" ) );

	}

	public void testUnionSQL( )
	{
		String sqltext = "SELECT SAL FROM\r\n"
				+ "(SELECT SAL FROM Store_Information\r\n"
				+ "Union\r\n"
				+ "SELECT SAL FROM Internet_Sales);";
		ColumnImpact impact = new ColumnImpact( sqltext,
				EDbVendor.dbvmssql,
				true,
				false );
		System.out.println( impact.getImpactResult( ) );
		Assert.assertTrue( impact.getImpactResult( )
				.equals( "SAL depends on: store_information.sal, internet_sales.sal\r\n" ) );

	}

	public void testCTEAndWhereSQL( )
	{
		String sqltext = "WITH dept_costs\r\n"
				+ "AS (SELECT SUM(sal) dept_total, dname,\r\n"
				+ "FROM   emp e,\r\n"
				+ "dept d\r\n"
				+ "WHERE  e.deptno = d.deptno\r\n"
				+ "GROUP  BY dname),\r\n"
				+ "avg_cost\r\n"
				+ "AS (SELECT SUM(dept_total) / COUNT(*) AVG\r\n"
				+ "FROM   dept_costs)\r\n"
				+ "SELECT *\r\n"
				+ "FROM   dept_costs\r\n"
				+ "WHERE  dept_total > (SELECT AVG\r\n"
				+ "FROM   avg_cost)\r\n"
				+ "ORDER  BY dname; \r\n";
		ColumnImpact impact = new ColumnImpact( sqltext,
				EDbVendor.dbvmssql,
				true,
				false );
		System.out.println( impact.getImpactResult( ) );
		Assert.assertTrue( impact.getImpactResult( )
				.equals( "* depends on: emp.sal, dept.deptno, dept.dname, emp.dname, emp.deptno, dept.sal\r\n" ) );

	}

	// public void testConnectbyAndStartwithSQL( )
	// {
	// String sqltext =
	// "select * from persons.dept connect by prior paredeptid=deptid start with deptid=76\r\n";
	// ColumnImpact impact = new ColumnImpact( sqltext,
	// EDbVendor.dbvoracle,
	// true,
	// false );
	// System.out.println( impact.getImpactResult( ) );
	// Assert.assertTrue( impact.getImpactResult( )
	// .equals(
	// "* depends on: persons.dept, persons.dept.paredeptid, persons.dept.deptid\r\n"
	// ) );
	// }

	public void testWhereSQL( )
	{
		String sqltext = "select a.ca1, a.ca2, b.cb1\r\n"
				+ "from\r\n"
				+ "(select ca1,ca2, ca_id from ta where ca3 = 5) a,\r\n"
				+ "(select cb1, cb_id from tb where cb3 = 5) b\r\n"
				+ "where b.cb_id = a.ca_id; \r\n";
		ColumnImpact impact = new ColumnImpact( sqltext,
				EDbVendor.dbvmssql,
				true,
				false );
		System.out.println( impact.getImpactResult( ) );
		Assert.assertTrue( impact.getImpactResult( )
				.equals( "a.ca1 depends on: tb.cb_id, ta.ca3, ta.ca_id, tb.cb3, ta.ca1\r\na.ca2 depends on: tb.cb_id, ta.ca3, ta.ca_id, tb.cb3, ta.ca2\r\nb.cb1 depends on: tb.cb_id, ta.ca3, ta.ca_id, tb.cb3, tb.cb1\r\n" ) );

	}

	public void testCaseWhenSQL( )
	{
		String sqltext = "select product_id,product_type_id,\r\n"
				+ "case product_type_id\r\n"
				+ "when 1 then 'Book'\r\n"
				+ "when 2 then 'Video'\r\n"
				+ "when 3 then 'DVD'\r\n"
				+ "when 4 then 'CD'\r\n"
				+ "else 'Magazine'\r\n"
				+ "end as testcasewhen\r\n"
				+ "from products; \r\n";
		ColumnImpact impact = new ColumnImpact( sqltext,
				EDbVendor.dbvmssql,
				true,
				false );
		System.out.println( impact.getImpactResult( ) );
		Assert.assertTrue( impact.getImpactResult( )
				.equals( "product_id depends on: products.product_id\r\nproduct_type_id depends on: products.product_type_id\r\ntestcasewhen depends on: products.product_type_id\r\n" ) );
	}

	public void testOrderBySQL( )
	{
		String sqltext = "select * from mytb order by mycol nulls first; \r\n";
		ColumnImpact impact = new ColumnImpact( sqltext,
				EDbVendor.dbvmssql,
				true,
				false );
		System.out.println( impact.getImpactResult( ) );
		Assert.assertTrue( impact.getImpactResult( )
				.equals( "* depends on: mytb.mycol, mytb\r\n" ) );
	}

	public void testUpdateSQL( )
	{
		String sqltext = "UPDATE sds_tst_prdct \r\n"
				+ "SET base_cow_id = b.bsecowid \r\n"
				+ "FROM sds_tst_prdct a, \r\n"
				+ "bog_pr_jil b \r\n"
				+ "WHERE a.std_tst_id = b.tstid \r\n"
				+ "AND b.bsecowid > 1 \r\n";
		ColumnImpact impact = new ColumnImpact( sqltext,
				EDbVendor.dbvmssql,
				true,
				false );
		System.out.println( impact.getImpactResult( ) );
		Assert.assertTrue( impact.getImpactResult( )
				.equals( "base_cow_id depends on: bog_pr_jil.bsecowid, bog_pr_jil.tstid, sds_tst_prdct.std_tst_id\r\n" ) );

	}

	public void testInnerJoinSQL( )
	{
		String sqltext = "Select a.key,a.info as a_info,b.info as b_info \r\n"
				+ "From \r\n"
				+ "( select key,max(info)as info from table1 ) a \r\n"
				+ "INNER JOIN \r\n"
				+ "( select key,max(info) as info from table2 ) b \r\n"
				+ "ON a.key = b.key\r\n";
		ColumnImpact impact = new ColumnImpact( sqltext,
				EDbVendor.dbvmssql,
				true,
				true );
		System.out.println( impact.getImpactResult( ) );
		Assert.assertTrue( impact.getImpactResult( )
				.equals( "<?xml version=\"1.0\" encoding=\"UTF-8\" standalone=\"no\"?>"
						+ "<columnImpactResult>"
						+ "<targetColumn name=\"a.key\">"
						+ "<sourceColumn clause=\"select\" name=\"key\" tableName=\"table1\"/>"
						+ "<sourceColumn clause=\"join\" name=\"key\" tableName=\"table1\"/>"
						+ "<sourceColumn clause=\"join\" name=\"key\" tableName=\"table2\"/>"
						+ "</targetColumn>"
						+ "<targetColumn alias=\"a_info\" name=\"a.info\">"
						+ "<sourceColumn clause=\"join\" name=\"key\" tableName=\"table1\"/>"
						+ "<sourceColumn clause=\"join\" name=\"key\" tableName=\"table2\"/>"
						+ "<sourceColumn clause=\"select\" name=\"info\" tableName=\"table1\"/>"
						+ "</targetColumn>"
						+ "<targetColumn alias=\"b_info\" name=\"b.info\">"
						+ "<sourceColumn clause=\"join\" name=\"key\" tableName=\"table1\"/>"
						+ "<sourceColumn clause=\"select\" name=\"info\" tableName=\"table2\"/>"
						+ "<sourceColumn clause=\"join\" name=\"key\" tableName=\"table2\"/>"
						+ "</targetColumn>"
						+ "</columnImpactResult>" ) );
	}

	public void testGroupbySQL( )
	{
		String sqltext = "SELECT a.deptno \"Department\",  \r\n"
				+ "a.num_emp/b.total_count \"Employees\",  \r\n"
				+ "a.sal_sum/b.total_sal \"Salary\" \r\n"
				+ "FROM \r\n"
				+ "(SELECT deptno, COUNT() num_emp, SUM(SAL) sal_sum \r\n"
				+ "FROM scott.emp \r\n"
				+ "GROUP BY deptno1) a, \r\n"
				+ "(SELECT COUNT() total_count, SUM(sal) total_sal \r\n"
				+ "FROM scott.emp) b\r\n";
		ColumnImpact impact = new ColumnImpact( sqltext,
				EDbVendor.dbvmssql,
				true,
				true );
		System.out.println( impact.getImpactResult( ) );
		Assert.assertTrue( impact.getImpactResult( )
				.equals( "<?xml version=\"1.0\" encoding=\"UTF-8\" standalone=\"no\"?>"
						+ "<columnImpactResult>"
						+ "<targetColumn alias=\"&quot;Department&quot;\" name=\"a.deptno\">"
						+ "<sourceColumn clause=\"select\" name=\"deptno\" tableName=\"emp\" tableOwner=\"scott\"/>"
						+ "<sourceColumn clause=\"group by\" name=\"deptno1\" tableName=\"emp\" tableOwner=\"scott\"/>"
						+ "</targetColumn>"
						+ "<targetColumn alias=\"&quot;Employees&quot;\" name=\"a.num_emp/b.total_count\">"
						+ "<sourceColumn clause=\"group by\" name=\"deptno1\" tableName=\"emp\" tableOwner=\"scott\"/>"
						+ "<sourceColumn clause=\"select\" tableName=\"emp\" tableOwner=\"scott\"/>"
						+ "</targetColumn>"
						+ "<targetColumn alias=\"&quot;Salary&quot;\" name=\"a.sal_sum/b.total_sal\">"
						+ "<sourceColumn clause=\"select\" name=\"SAL\" tableName=\"emp\" tableOwner=\"scott\"/>"
						+ "<sourceColumn clause=\"group by\" name=\"deptno1\" tableName=\"emp\" tableOwner=\"scott\"/>"
						+ "</targetColumn>"
						+ "</columnImpactResult>" ) );
	}
}
