/commons/src/test/java/com/navercorp/pinpoint/common/util/DefaultSqlParserTest.java

/*
 * Copyright 2014 NAVER Corp.
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *     http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */

package com.navercorp.pinpoint.common.util;

import org.junit.Assert;

import org.junit.Test;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.util.List;

/**
 * @author emeroad
 */
public class DefaultSqlParserTest {

    private final Logger logger = LoggerFactory.getLogger(this.getClass());

    private SqlParser sqlParser = new DefaultSqlParser();
    private OutputParameterParser outputParameterParser = new OutputParameterParser();

    @Test
    public void normalizedSql() {

        NormalizedSql parsingResult = sqlParser.normalizedSql("select * from table a = 1 and b=50 and c=? and d='11'");
        String s = parsingResult.getNormalizedSql();

        logger.debug(s);
        logger.debug(parsingResult.getParseParameter());

        NormalizedSql parsingResult2 = sqlParser.normalizedSql(" ");
        String s2 = parsingResult2.getNormalizedSql();
        logger.debug(s2);

        logger.debug("{}", (char) -1);
        String str = "s";
        logger.debug("{}", str.codePointAt(0));
        logger.debug("{}", (int) str.charAt(0));
        logger.debug("high:{}", Character.MAX_HIGH_SURROGATE);
        logger.debug("low:{}", Character.MIN_LOW_SURROGATE);
        logger.debug("{}", (int) Character.MIN_LOW_SURROGATE);
        logger.debug("{}", (int) Character.MAX_HIGH_SURROGATE);

        NormalizedSql parsingResult3 = sqlParser.normalizedSql("''");
        String s3 = parsingResult3.getNormalizedSql();
        logger.debug("s3:{}", s3);
        logger.debug("sb3:{}", parsingResult3.getParseParameter());
    }

    @Test
    public void nullCheck() {
        sqlParser.normalizedSql(null);
    }

    @Test
    public void complex() {

        assertEqual("select * from table a = 1 and b=50 and c=? and d='11'",
                "select * from table a = 0# and b=1# and c=? and d='2$'", "1,50,11");

        assertEqual("select * from table a = -1 and b=-50 and c=? and d='-11'",
                "select * from table a = -0# and b=-1# and c=? and d='2$'", "1,50,-11");

        assertEqual("select * from table a = +1 and b=+50 and c=? and d='+11'",
                "select * from table a = +0# and b=+1# and c=? and d='2$'", "1,50,+11");

        assertEqual("select * from table a = 1/*test*/ and b=50/*test*/ and c=? and d='11'",
                "select * from table a = 0#/*test*/ and b=1#/*test*/ and c=? and d='2$'", "1,50,11");

        assertEqual("select ZIPCODE,CITY from ZIPCODE");
        assertEqual("select a.ZIPCODE,a.CITY from ZIPCODE as a");
        assertEqual("select ZIPCODE,123 from ZIPCODE",
                "select ZIPCODE,0# from ZIPCODE", "123");

        assertEqual("SELECT * from table a=123 and b='abc' and c=1-3",
                "SELECT * from table a=0# and b='1$' and c=2#-3#", "123,abc,1,3");

        assertEqual("SYSTEM_RANGE(1, 10)",
                "SYSTEM_RANGE(0#, 1#)", "1,10");

    }

    @Test
    public void etcState() {

        assertEqual("test.abc", "test.abc", "");
        assertEqual("test.abc123", "test.abc123", "");
        assertEqual("test.123", "test.123", "");

    }

    @Test
    public void objectEquals() {

        assertEqualObject("test.abc");
        assertEqualObject("test.abc123");
        assertEqualObject("test.123");

    }


    @Test
    public void numberState() {
        assertEqual("123", "0#", "123");
        // just converting numbers as it is too much work to find out if '-' represents a negative number, or is part of the SQL expression
        assertEqual("-123", "-0#", "123");
        assertEqual("+123", "+0#", "123");
        assertEqual("1.23", "0#", "1.23");
        assertEqual("1.23.34", "0#", "1.23.34");
        assertEqual("123 456", "0# 1#", "123,456");
        assertEqual("1.23 4.56", "0# 1#", "1.23,4.56");
        assertEqual("1.23-4.56", "0#-1#", "1.23,4.56");

        assertEqual("1<2", "0#<1#", "1,2");
        assertEqual("1< 2", "0#< 1#", "1,2");
        assertEqual("(1< 2)", "(0#< 1#)", "1,2");

        assertEqual("-- 1.23", "-- 1.23", "");
        assertEqual("- -1.23", "- -0#", "1.23");
        assertEqual("--1.23", "--1.23", "");
        assertEqual("/* 1.23 */", "/* 1.23 */", "");
        assertEqual("/*1.23*/", "/*1.23*/", "");
        assertEqual("/* 1.23 \n*/", "/* 1.23 \n*/", "");

        assertEqual("test123", "test123", "");
        assertEqual("test_123", "test_123", "");
        assertEqual("test_ 123", "test_ 0#", "123");

        // this is effectively an impossible token
        assertEqual("123tst", "0#tst", "123");
    }

    @Test
    public void numberState2() {
        assertEqual("1.23e", "0#", "1.23e");
        assertEqual("1.23E", "0#", "1.23E");
        // just converting numbers as it is too much work to find out if '-' represents a negative number, or is part of the SQL expression
        assertEqual("1.4e-10", "0#-1#", "1.4e,10");

    }


    @Test
    public void singleLineCommentState() {
        assertEqual("--", "--", "");
        assertEqual("//", "//", "");
        assertEqual("--123", "--123", "");
        assertEqual("//123", "//123", "");
        assertEqual("--test", "--test");
        assertEqual("//test", "//test");
        assertEqual("--test\ntest", "--test\ntest", "");
        assertEqual("--test\t\n", "--test\t\n", "");
        assertEqual("--test\n123 test", "--test\n0# test", "123");
    }


    @Test
    public void multiLineCommentState() {
        assertEqual("/**/", "/**/", "");
        assertEqual("/* */", "/* */", "");
        assertEqual("/* */abc", "/* */abc", "");
        assertEqual("/* * */", "/* * */", "");
        assertEqual("/* * */", "/* * */", "");

        assertEqual("/* abc", "/* abc", "");

        assertEqual("select * from table", "select * from table", "");
    }

    @Test
    public void symbolState() {
        assertEqual("''", "''", "");
        assertEqual("'abc'", "'0$'", "abc");
        assertEqual("'a''bc'", "'0$'", "a''bc");
        assertEqual("'a' 'bc'", "'0$' '1$'", "a,bc");

        assertEqual("'a''bc' 'a''bc'", "'0$' '1$'", "a''bc,a''bc");


        assertEqual("select * from table where a='a'", "select * from table where a='0$'", "a");
    }

    //    @Test
    public void charout() {
        for (int i = 11; i < 67; i++) {
            logger.debug("{}", (char) i);
        }
    }

    @Test
    public void commentAndSymbolCombine() {
        assertEqual("/* 'test' */", "/* 'test' */", "");
        assertEqual("/* 'test'' */", "/* 'test'' */", "");
        assertEqual("/* '' */", "/* '' */");

        assertEqual("/*  */ 123 */", "/*  */ 0# */", "123");

        assertEqual("' /* */'", "'0$'", " /* */");

    }

    @Test
    public void separatorTest() {

        assertEqual("1234 456,7", "0# 1#,2#", "1234,456,7");

        assertEqual("'1234 456,7'", "'0$'", "1234 456,,7");

        assertEqual("'1234''456,7'", "'0$'", "1234''456,,7");
        NormalizedSql parsingResult2 = this.sqlParser.normalizedSql("'1234''456,7'");
        logger.debug("{}", parsingResult2);

        // for string token
        assertEqual("'1234' '456,7'", "'0$' '1$'", "1234,456,,7");
    }


    @Test
    public void combineTest() {
        assertCombine("123 345", "0# 1#", "123,345");
        assertCombine("123 345 'test'", "0# 1# '2$'", "123,345,test");
        assertCombine("1 2 3 4 5 6 7 8 9 10 11", "0# 1# 2# 3# 4# 5# 6# 7# 8# 9# 10#", "1,2,3,4,5,6,7,8,9,10,11");
    }

    @Test
    public void combineErrorTest() {
        assertCombineErrorCase("123 10#", "0# 10#", "123,345");

        assertCombineErrorCase("1 3 10#", "0# 2# 10#", "1,2,3");

        assertCombineErrorCase("1 2 3", "0# 2 3", "1,2,3");
        assertCombineErrorCase("1 2 10", "0# 2 10", "1,2,3");
        assertCombineErrorCase("1 2 201", "0# 2 201", "1,2,3");

        assertCombineErrorCase("1 2 11", "0# 2 10#", "1,2,3,4,5,6,7,8,9,10,11");

    }

    @Test
    public void combineBindValue() {
        OutputParameterParser parameterParser = new OutputParameterParser();

        String sql = "select * from table a = 1 and b=50 and c=? and d='11'";
        String expected = "select * from table a = 1 and b=50 and c='foo' and d='11'";
        List<String> bindValues = parameterParser.parseOutputParameter("foo");
        String result = sqlParser.combineBindValues(sql, bindValues);
        Assert.assertEquals(expected, result);

        sql = "select * from table a = ? and b=? and c=? and d=?";
        expected = "select * from table a = '1' and b='50' and c='foo' and d='11'";
        bindValues = parameterParser.parseOutputParameter("1, 50, foo, 11");
        result = sqlParser.combineBindValues(sql, bindValues);
        Assert.assertEquals(expected, result);

        sql = "select * from table id = \"foo ? bar\" and number=?";
        expected = "select * from table id = \"foo ? bar\" and number='99'";
        bindValues = parameterParser.parseOutputParameter("99");
        result = sqlParser.combineBindValues(sql, bindValues);
        Assert.assertEquals(expected, result);

        sql = "select * from table id = 'hi ? name''s foo' and number=?";
        expected = "select * from table id = 'hi ? name's foo' and number='99'";
        bindValues = parameterParser.parseOutputParameter("99");
        result = sqlParser.combineBindValues(sql, bindValues);
        Assert.assertEquals(expected, result);

        sql = "/** comment ? */ select * from table id = ?";
        expected = "/** comment ? */ select * from table id = 'foo,bar'";
        bindValues = parameterParser.parseOutputParameter("foo,,bar");
        result = sqlParser.combineBindValues(sql, bindValues);
        Assert.assertEquals(expected, result);

        sql = "select /*! STRAIGHT_JOIN ? */ * from table id = ?";
        expected = "select /*! STRAIGHT_JOIN ? */ * from table id = 'foo,bar'";
        bindValues = parameterParser.parseOutputParameter("foo,,bar");
        result = sqlParser.combineBindValues(sql, bindValues);
        Assert.assertEquals(expected, result);

        sql = "select * from table id = ?; -- This ? comment";
        expected = "select * from table id = 'foo'; -- This ? comment";
        bindValues = parameterParser.parseOutputParameter("foo");
        result = sqlParser.combineBindValues(sql, bindValues);
        Assert.assertEquals(expected, result);
    }

    private void assertCombine(String result, String sql, String outputParams) {
        List<String> output = this.outputParameterParser.parseOutputParameter(outputParams);

        NormalizedSql parsingResult = this.sqlParser.normalizedSql(result);
        Assert.assertEquals("sql", parsingResult.getNormalizedSql(), sql);
        String combine = this.sqlParser.combineOutputParams(sql, output);
        Assert.assertEquals("combine", result, combine);
    }

    private void assertCombineErrorCase(String expectedError, String sql, String outputParams) {
        List<String> output = this.outputParameterParser.parseOutputParameter(outputParams);
//        ParsingResult parsingResult = this.sqlParser.normalizedSql(result);
        String combine = this.sqlParser.combineOutputParams(sql, output);
        Assert.assertEquals("combine", expectedError, combine);
    }


    private void assertEqual(String expected) {
        NormalizedSql parsingResult = sqlParser.normalizedSql(expected);
        String normalizedSql = parsingResult.getNormalizedSql();
        try {
            Assert.assertEquals(expected, normalizedSql);
        } catch (AssertionError e) {
            logger.warn("Original :{}", expected);
            throw e;
        }
    }

    private void assertEqual(String expected, String actual) {
        NormalizedSql parsingResult = sqlParser.normalizedSql(expected);
        String normalizedSql = parsingResult.getNormalizedSql();
        try {
            Assert.assertEquals(actual, normalizedSql);
        } catch (AssertionError e) {
            logger.warn("Original :{}", expected);
            throw e;
        }
    }

    private void assertEqual(String expected, String actual, String outputExpected) {
        NormalizedSql parsingResult = sqlParser.normalizedSql(expected);
        String normalizedSql = parsingResult.getNormalizedSql();
        String output = parsingResult.getParseParameter();
        List<String> outputParams = outputParameterParser.parseOutputParameter(output);
        String s = sqlParser.combineOutputParams(normalizedSql, outputParams);
        logger.debug("combine:" + s);
        try {
            Assert.assertEquals("normalizedSql check", actual, normalizedSql);
        } catch (AssertionError e) {
            logger.warn("Original :{}", expected);
            throw e;
        }

        Assert.assertEquals("outputParam check", outputExpected, parsingResult.getParseParameter());
    }

    private void assertEqualObject(String expected) {
        NormalizedSql parsingResult = sqlParser.normalizedSql(expected);
        String normalizedSql = parsingResult.getNormalizedSql();
        try {
            Assert.assertEquals("normalizedSql check", expected, normalizedSql);
            Assert.assertSame(expected, normalizedSql);
        } catch (AssertionError e) {
            logger.warn("Original :{}", expected);
            throw e;
        }

    }
}