SqlFactoryTest.java

package cn.org.rapid_framework.generator.provider.db.sql;

import java.sql.SQLException;

import cn.org.rapid_framework.generator.GeneratorConstants;
import cn.org.rapid_framework.generator.GeneratorProperties;
import cn.org.rapid_framework.generator.GeneratorTestCase;
import cn.org.rapid_framework.generator.provider.db.sql.SqlFactory.SqlParametersParser;
import cn.org.rapid_framework.generator.provider.db.sql.model.Sql;


public class SqlFactoryTest extends GeneratorTestCase {
	SqlFactory parser = new SqlFactory();

	public void test_isMatchListParam() {
	    String sql = "length(#username#) and in \n (#pwd#) and not \n in \n (#user#) and blog = #blog[]# and sex = #sex[].value#";
        assertFalse(new SqlParametersParser().isMatchListParam(sql, "username"));
        assertFalse(new SqlParametersParser().isMatchListParam(sql, "notexist"));
        assertFalse(new SqlParametersParser().isMatchListParam(sql, "in"));
        assertFalse(new SqlParametersParser().isMatchListParam(sql, "not in"));
        assertTrue(new SqlParametersParser().isMatchListParam(sql, "pwd"));
        assertTrue(new SqlParametersParser().isMatchListParam(sql, "user"));
        assertTrue(new SqlParametersParser().isMatchListParam(sql, "blog"));
        assertTrue(new SqlParametersParser().isMatchListParam(sql, "sex"));
	}
	
	public void test_union() throws SQLException, Exception {
		String query = "select * from user_info where user_id = ? and age = ? and password = ? and username like ? or (sex >= ?) ";
		String orderByQuery = "select * from user_info where user_id = ? and age = ? and password = ? and username like ? or (sex >= ?) order by :orderby ";
		Sql sql = parser.parseSql(query+" union " + query);
		verifyParameters(sql,"userId","username","password","age","sex");
		
		sql = parser.parseSql(orderByQuery+" union " + orderByQuery);
		verifyParameters(sql,"userId","username","password","age","sex");
	}
	
	public void test_order_by() throws SQLException, Exception {
		Sql sql = parser.parseSql("select * from user_info where user_id = ? and age = ? and password = ? and username like ? or (sex >= ?) order by :orderby :asc_desc");
		verifyParameters(sql,"userId","username","password","age","sex");
	}
	public void test_unscaped_xml() throws SQLException, Exception {
		try {
			Sql sql = parser.parseSql("select * from user_info where user_id < :user_id");
			fail();
		}catch(RuntimeException e) {
		}
	}

	public void test_ListParam() throws SQLException, Exception {
		Sql sql = parser.parseSql("select * from user_info where user_id = #nameList[]# and password = #{pwdList[index]} and age = ${ageList[${index}]} ");
		verifyParameters(sql,"nameList","pwdList","ageList");
		assertTrue(sql.getParam("nameList").isListParam());
		assertTrue(sql.getParam("pwdList").isListParam());
		assertTrue(sql.getParam("ageList").isListParam());
	}
	
	public void test_select() throws SQLException, Exception {
		Sql sql = parser.parseSql("select * from user_info where user_id = ? and age = ? and password = ? and username like ? or (sex >= ?)");
		verifyParameters(sql,"userId","username","password","age","sex");
		verifyColumns(sql,"user_id","username","password","BIRTH_DATE","age","sex");
	}
	
	public void test_聚合函数() throws SQLException, Exception {
		Sql sql = parser.parseSql("select DISTINCT(count(*)) DISTINCT_count,count(username) cnt_username,sum(age) sum_age,avg(sex) avg_sex from user_info where user_id = ? and age = ? and password = ? and username like ? or (sex >= ?)");
		verifyParameters(sql,"userId","username","password","age","sex");
		verifyColumns(sql,"DISTINCT_count","cnt_username","sum_age","avg_sex");
	}

	public void test_聚合函数2() throws SQLException, Exception {
		Sql sql = parser.parseSql("select DISTINCT username from user_info where user_id = ? and age = ? and password = ? and username like ? or (sex >= ?)");
		verifyParameters(sql,"userId","username","password","age","sex");
		verifyColumns(sql,"username");
	}

	public void test_group_by() throws SQLException, Exception {
		Sql sql = parser.parseSql("select DISTINCT username from user_info where user_id = ? and age = ? and password = ? and username like ? or (sex >= ?) group by username");
		verifyParameters(sql,"userId","username","password","age","sex");
		verifyColumns(sql,"username");
	}

	public void test_join() throws SQLException, Exception {
		Sql sql = parser.parseSql("select DISTINCT t1.username from user_info t1 inner join user_info t2 on t1.user_id=t2.user_id where t1.user_id = ? and t1.age = ? and t1.password = ? and t1.username like ? or (t1.sex >= ?) group by t1.username");
		verifyParameters(sql,"userId","username","password","age","sex");
		verifyColumns(sql,"username");
	}

	public void test_select_with_table_alias() throws SQLException, Exception {
		Sql sql = parser.parseSql("select * from user_info t where t.user_id = ? and t.age = ? and t.password = ? and t.username like ? or (t.sex >= ?)");
		verifyParameters(sql,"userId","username","password","age","sex");
		verifyColumns(sql,"userId","username","password","BIRTH_DATE","age","sex");
	}
	
	public void test_group_by_having() throws SQLException, Exception {
		Sql sql = parser.parseSql("select sum(age) sum_age from user_info where user_id = ? and age = ? and password = ? and username like ? or (sex >= ?) group by username having sum(age) = :havingUsername");
		verifyParameters(sql,"userId","username","password","age","sex","havingUsername");
		verifyColumns(sql,"sum_age");
	}

    public void test_select_for_mybatis_foreach() throws SQLException, Exception {
        Sql sql = parser.parseSql("select * from user_info where user_id = ? and username = #{usernamesByIndex[index]}");
        verifyParameters(sql,"userId","usernamesByIndex");
        
        sql = parser.parseSql("select * from user_info where user_id = ? and username = #{usernamesByIndex[${index}]}");
        verifyParameters(sql,"userId","usernamesByIndex");
        
        sql = parser.parseSql("select * from user_info where user_id = ? and username = #{item}");
        verifyParameters(sql,"userId","item");
    }

   public void test_select_for_ibatis_foreach() throws SQLException, Exception {
       Sql sql = parser.parseSql("select * from user_info where user_id = ? and username = #usernamesByIndex[]#");
       verifyParameters(sql,"userId","usernamesByIndex");
   }
   
	public void test_select_willcard() throws SQLException, Exception {
		Sql sql = parser.parseSql("select * from user_info where user_id = ? and username = :username");
		verifyParameters(sql,"userId","username");
		String expected = "select USER_ID,USERNAME,PASSWORD,BIRTH_DATE,SEX,AGE from user_info where user_id = #userId# and username = :username";
		assertStringEquals(expected,sql.getIbatisSql());
	}

	public void test_select_willcard_multi_table() throws SQLException, Exception {
		Sql sql = parser.parseSql("select t1.*,t2.* from user_info t1 inner join role t2 on t1.username=t2.role_name where t1.user_id = ? and t2.role_name = :role_name");
		verifyParameters(sql,"userId","role_name");
		String expected = "select t1.USER_ID,t1.USERNAME,t1.PASSWORD,t1.BIRTH_DATE,t1.SEX,t1.AGE,t2.USER_ID,t2.USERNAME,t2.PASSWORD,t2.BIRTH_DATE,t2.SEX,t2.AGE from user_info where user_id = #userId# and username = :username";
//		assertStringEquals(expected,sql.getSql());
		//FIXME select t1.* t2.* 应该返回不同的表前缀
	}
	
	public void test_escaped() throws SQLException, Exception {
		Sql sql = parser.parseSql("select * from user_info where user_id > ? and username < :username");
		verifyParameters(sql,"userId","username");
		assertStringEquals("select USER_ID,USERNAME,PASSWORD,BIRTH_DATE,SEX,AGE from user_info where user_id > #userId# and username < :username",sql.getIbatisSql());
		assertStringEquals("select USER_ID,USERNAME,PASSWORD,BIRTH_DATE,SEX,AGE from user_info where user_id > #{userId} and username < :username",sql.getMybatisSql());
	}

	public void test_sql_function() throws SQLException, Exception {
		Sql sql = parser.parseSql("select * from user_info where username = lower(#userId#) and username != :username");
		verifyParameters(sql,"userId","username");
		
		sql = parser.parseSql("select * from user_info where username = lower(?) and username != :username");
		verifyNoParameters(sql,"userId");
		verifyParameters(sql,"username");
	}
	
	public void test_insert() throws SQLException, Exception {
		Sql sql = parser.parseSql("insert into user_info(user_id,username,password) values (?,?,?)");
		verifyParameters(sql,"userId","username","password");
	}
	
	public void test_insert_with_userId_not_null() throws SQLException, Exception {
		try {
		Sql sql = parser.parseSql("insert into user_info(username,password) values (?,?)");
		fail("user_id must be not null");
		}catch(Exception e) {
			assertTrue(true);
		}
	}
	
	public void test_delete() throws SQLException, Exception {
		Sql sql = parser.parseSql("delete from user_info where username = ? and password = ? and age = ? or (sex >= ?)");
		verifyParameters(sql,"username","password","age","sex");
		assertEquals("delete from user_info where username = #username# and password = #password# and age = #age# or (sex >= #sex#)",sql.getIbatisSql());
		assertEquals("delete from user_info where username = #{username} and password = #{password} and age = #{age} or (sex >= #{sex})",sql.getMybatisSql());
		assertEquals("delete from user_info where username = :username and password = :password and age = :age or (sex >= :sex)",sql.getSpringJdbcSql());
		assertEquals("delete from user_info where username = :username and password = :password and age = :age or (sex >= :sex)",sql.getHql());
	}
	public void test_update() throws SQLException, Exception {
		Sql sql = parser.parseSql("update user_info set username = ? , password = ? , age = ? , sex = ?");
		verifyParameters(sql,"username","password","age","sex");
		assertEquals("update user_info set username = #username# , password = #password# , age = #age# , sex = #sex#",sql.getIbatisSql());
	}

   public void test_select_as_alias_h2() {
        
        Sql sql = parser.parseSql("SELECT username user,password pwd from user_info");
        System.out.println("columns:"+sql.getColumns());
        verifyColumns(sql, "user","pwd");
    }
	   
	public void test_select_as_alias() {
	    GeneratorProperties.setProperty(GeneratorConstants.JDBC_DRIVER, "com.mysql.jdbc.Driver");
	    GeneratorProperties.setProperty(GeneratorConstants.JDBC_USERNAME, "srvctl");
	    GeneratorProperties.setProperty(GeneratorConstants.JDBC_PASSWORD, "ali88");
	    GeneratorProperties.setProperty(GeneratorConstants.JDBC_URL, "jdbc:mysql://mypay1.devdb.alipay.net:3306/srvctldb?useUnicode=true&characterEncoding=gbk");
	    GeneratorProperties.setProperty(GeneratorConstants.JDBC_SCHEMA, "srvctldb");
	    
	    Sql sql = parser.parseSql("SELECT t1.TABLE_ID,t1.TABLE_SIGN,t2.HOST_NAME,t1.TB_VERSION,t2.VERSION,t1.GMT_MODIFIED as SERVER_DATE,t2.GMT_MODIFIED as CLIENT_DATE,t3.CHECK_TIME,t4.CONFIG_TITLE FROM bc_config_table t1,bc_config_client t2,bc_config_monitor t3,bc_config_schema_view t4 WHERE t1.TABLE_ID=t2.TABLE_ID AND t1.TABLE_ID=t3.TABLE_ID AND t1.TABLE_ID=t4.TABLE_ID AND t1.STATUS='1' AND t1.APP_NAME=?");
	    System.out.println("columns:"+sql.getColumns());
	    verifyColumns(sql, "TABLE_ID","TABLE_SIGN","HOST_NAME","TB_VERSION","VERSION","SERVER_DATE","CLIENT_DATE","CHECK_TIME","CONFIG_TITLE");
	}
	
	private void assertStringEquals(String expected, String str) {
		if(expected.toLowerCase().replaceAll("\\s", "").equals(str.toLowerCase().replaceAll("\\s", ""))) {
		}else {
			assertEquals(expected,str);
		}
	}
	private void verifyParameters(Sql sql, String... expectedParameters) {
		for(String param : expectedParameters) {
			assertNotNull("not found param:"+param+" on sql:"+sql.getSourceSql()+" actual params:"+sql.getParams(),sql.getParam(param));
		}
	}
	private void verifyNoParameters(Sql sql, String... expectedParameters) {
		for(String param : expectedParameters) {
			assertNull("not found param:"+param+" on sql:"+sql.getSourceSql()+"\n real params:"+sql.getParams(),sql.getParam(param));
		}
	}
	private void verifyColumns(Sql sql, String... expectedColumns) {
		for(String name : expectedColumns) {
			assertNotNull("not found column:"+name+" on sql:"+sql.getSourceSql()+"\n real columns:"+sql.getColumns(),sql.getColumnByName(name));
		}
	}
}