SelectSingleTableTest.java

/*
 * Copyright 1999-2015 dangdang.com.
 * <p>
 * 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.
 * </p>
 */

package com.dangdang.ddframe.rdb.sharding.router;

import com.dangdang.ddframe.rdb.sharding.exception.SQLParserException;
import com.dangdang.ddframe.rdb.sharding.parser.result.router.Condition;
import org.junit.Test;

import java.util.Arrays;
import java.util.Collections;
import java.util.List;

import static org.hamcrest.core.Is.is;
import static org.junit.Assert.assertThat;

public final class SelectSingleTableTest extends AbstractDynamicRouteSqlTest {

    @Test
    public void assertGroupBy() throws SQLParserException {
        assertSingleTarget("select sum(qty) from order where order_id = 1 group by tenant_id", "ds_1",
                "SELECT SUM(qty) FROM order_1 WHERE order_id = 1 GROUP BY tenant_id");
        assertMultipleTargets("select sum(qty) from order group by tenant_id", 4, Arrays.asList("ds_0", "ds_1"),
                Arrays.asList("SELECT SUM(qty), tenant_id AS sharding_gen_1 FROM order_0 GROUP BY tenant_id"));
    }
    
    @Test
    public void assertSingleSelect() throws SQLParserException {
        assertSingleTarget("select * from order where order_id = 1", "ds_1", "SELECT * FROM order_1 WHERE order_id = 1");
        assertSingleTarget("select * from order where order_id = ?", Collections.<Object>singletonList(2), "ds_0", "SELECT * FROM order_0 WHERE order_id = ?");
        assertSingleTarget(Collections.singletonList(new ShardingValuePair("order", 1)), "select * from order", "ds_1", "SELECT * FROM order_1");
        assertSingleTarget(Collections.singletonList(new ShardingValuePair("order", 2)), "select * from order", "ds_0", "SELECT * FROM order_0");
    }
    
    @Test
    public void assertSelectWithAlias() throws SQLParserException {
        assertSingleTarget("select * from order a where a.order_id = 2", "ds_0", "SELECT * FROM order_0 a WHERE a.order_id = 2");
        assertSingleTarget("select * from order A where a.order_id = 2", "ds_0", "SELECT * FROM order_0 A WHERE a.order_id = 2");
        assertSingleTarget("select * from order a where A.order_id = 2", "ds_0", "SELECT * FROM order_0 a WHERE A.order_id = 2");
        assertSingleTarget(Collections.singletonList(new ShardingValuePair("order", 2)), "select * from order a", "ds_0", "SELECT * FROM order_0 a");
        assertSingleTarget(Collections.singletonList(new ShardingValuePair("order", 2)), "select * from order A", "ds_0", "SELECT * FROM order_0 A");
        assertSingleTarget(Collections.singletonList(new ShardingValuePair("order", 2)), "select * from order a", "ds_0", "SELECT * FROM order_0 a");
    }
    
    @Test
    public void assertSelectWithTableNameAsAlias() throws SQLParserException {
        assertSingleTarget("select * from order where order.order_id = 10", "ds_0", "SELECT * FROM order_0 WHERE order_0.order_id = 10");
    }
    
    @Test
    public void assertSelectWithIn() throws SQLParserException {
        assertMultipleTargets("select * from order where order_id in (?,?,?)", Arrays.<Object>asList(1, 2, 100), 4, 
                Arrays.asList("ds_0", "ds_1"), Arrays.asList("SELECT * FROM order_0 WHERE order_id IN (?, ?, ?)", "SELECT * FROM order_1 WHERE order_id IN (?, ?, ?)"));
        assertMultipleTargets(Collections.singletonList(new ShardingValuePair("order", Condition.BinaryOperator.IN, 1, 2, 100)), "select * from order", 4,
                Arrays.asList("ds_0", "ds_1"), Arrays.asList("SELECT * FROM order_0", "SELECT * FROM order_1"));
    }
    
    @Test
    public void assertSelectWithInAndIntersection() throws SQLParserException {
        assertMultipleTargets("select * from order where order_id in (?,?) or order_id in (?,?)", Arrays.<Object>asList(1, 2, 100, 2), 4,
                Arrays.asList("ds_0", "ds_1"), 
                Arrays.asList("SELECT * FROM order_1 WHERE order_id IN (?, ?) OR order_id IN (?, ?)", "SELECT * FROM order_1 WHERE order_id IN (?, ?) OR order_id IN (?, ?)"));
    }
    
    @Test
    public void assertSelectWithBetween() throws SQLParserException {
        assertMultipleTargets("select * from order where order_id between ? and ?", Arrays.<Object>asList(1, 100), 4, 
                Arrays.asList("ds_0", "ds_1"), Arrays.asList("SELECT * FROM order_0 WHERE order_id BETWEEN ? AND ?", "SELECT * FROM order_1 WHERE order_id BETWEEN ? AND ?"));
        assertMultipleTargets(Collections.singletonList(new ShardingValuePair("order", Condition.BinaryOperator.BETWEEN, 1, 100)), "select * from order", 4,
                Arrays.asList("ds_0", "ds_1"), Arrays.asList("SELECT * FROM order_0", "SELECT * FROM order_1"));
    }
    
    @Test
    public void assertSelectWithBetweenAndIntersection() throws SQLParserException {
        assertMultipleTargets("select * from order where order_id between ? and ? or order_id between ? and ? ", Arrays.<Object>asList(1, 50, 29, 100), 4,
                Arrays.asList("ds_0", "ds_1"), 
                Arrays.asList("SELECT * FROM order_0 WHERE order_id BETWEEN ? AND ? OR order_id BETWEEN ? AND ?", 
                        "SELECT * FROM order_1 WHERE order_id BETWEEN ? AND ? OR order_id BETWEEN ? AND ?"));
    }
    
    @Test
    public void assertSingleSelectLimit() throws SQLParserException {
        assertSingleTarget("select * from order where order_id = 1 limit 5", "ds_1", "SELECT * FROM order_1 WHERE order_id = 1 LIMIT 5");
        assertSingleTarget("select * from order where order_id = 1 limit 2,5", "ds_1", "SELECT * FROM order_1 WHERE order_id = 1 LIMIT 2, 5");
        assertSingleTarget("select * from order where order_id = 1 limit 5 offset 2", "ds_1", "SELECT * FROM order_1 WHERE order_id = 1 LIMIT 2, 5");
    
        List<Object> parameters = Arrays.<Object>asList(2, 4, 5);
        assertSingleTarget("select * from order where order_id = ? limit ?,?", parameters, "ds_0", "SELECT * FROM order_0 WHERE order_id = ? LIMIT ?, ?");
        assertThat(parameters, is(Arrays.<Object>asList(2, 4, 5)));
    
        parameters = Arrays.<Object>asList(2, 4, 5);
        assertSingleTarget("select * from order where order_id = ? limit ? offset ?", parameters, "ds_0", "SELECT * FROM order_0 WHERE order_id = ? LIMIT ?, ?");
        assertThat(parameters, is(Arrays.<Object>asList(2, 5, 4)));
    
        parameters = Arrays.<Object>asList(2, 5);
        assertSingleTarget("select * from order where order_id = ? limit ?", parameters, "ds_0", "SELECT * FROM order_0 WHERE order_id = ? LIMIT ?");
        assertThat(parameters, is(Arrays.<Object>asList(2, 5)));
    
        parameters = Arrays.<Object>asList(2, 5);
        assertSingleTarget("select * from order where order_id = ? limit ?,10", parameters, "ds_0", "SELECT * FROM order_0 WHERE order_id = ? LIMIT ?, 10");
        assertThat(parameters, is(Arrays.<Object>asList(2, 5)));
    
        parameters = Arrays.<Object>asList(2, 5);
        assertSingleTarget("select * from order where order_id = ? limit 10,?", parameters, "ds_0", "SELECT * FROM order_0 WHERE order_id = ? LIMIT 10, ?");
        assertThat(parameters, is(Arrays.<Object>asList(2, 5)));
    }
    
    @Test
    public void assertSelectInLimit() throws SQLParserException {
        assertMultipleTargets("select * from order where order_id in (?,?,?) limit 5", Arrays.<Object>asList(1, 2, 100), 4,
                Arrays.asList("ds_0", "ds_1"), Arrays.asList("SELECT * FROM order_0 WHERE order_id IN (?, ?, ?) LIMIT 5", "SELECT * FROM order_1 WHERE order_id IN (?, ?, ?) LIMIT 5"));
        assertMultipleTargets("select * from order where order_id in (?,?,?) limit 2,5", Arrays.<Object>asList(1, 2, 100), 4,
                Arrays.asList("ds_0", "ds_1"), Arrays.asList("SELECT * FROM order_0 WHERE order_id IN (?, ?, ?) LIMIT 0, 7", "SELECT * FROM order_1 WHERE order_id IN (?, ?, ?) LIMIT 0, 7"));
        assertMultipleTargets("select * from order where order_id in (?,?,?) limit 5 offset 2", Arrays.<Object>asList(1, 2, 100), 4,
                Arrays.asList("ds_0", "ds_1"), Arrays.asList("SELECT * FROM order_0 WHERE order_id IN (?, ?, ?) LIMIT 0, 7", "SELECT * FROM order_1 WHERE order_id IN (?, ?, ?) LIMIT 0, 7"));
    
        List<Object> parameters = Arrays.<Object>asList(1, 2, 100, 5);
        assertMultipleTargets("select * from order where order_id in (?,?,?) limit ?", parameters, 4,
                Arrays.asList("ds_0", "ds_1"), Arrays.asList("SELECT * FROM order_0 WHERE order_id IN (?, ?, ?) LIMIT ?", "SELECT * FROM order_1 WHERE order_id IN (?, ?, ?) LIMIT ?"));
        assertThat(parameters, is(Arrays.<Object>asList(1, 2, 100, 5)));
    
        parameters = Arrays.<Object>asList(1, 2, 100, 2, 5);
        assertMultipleTargets("select * from order where order_id in (?,?,?) limit ?,?", parameters, 4,
                Arrays.asList("ds_0", "ds_1"), Arrays.asList("SELECT * FROM order_0 WHERE order_id IN (?, ?, ?) LIMIT ?, ?", "SELECT * FROM order_1 WHERE order_id IN (?, ?, ?) LIMIT ?, ?"));
        assertThat(parameters, is(Arrays.<Object>asList(1, 2, 100, 0, 7)));
    
        parameters = Arrays.<Object>asList(1, 2, 100, 5, 2);
        assertMultipleTargets("select * from order where order_id in (?,?,?) limit ? offset ?", parameters, 4,
                Arrays.asList("ds_0", "ds_1"), Arrays.asList("SELECT * FROM order_0 WHERE order_id IN (?, ?, ?) LIMIT ?, ?", "SELECT * FROM order_1 WHERE order_id IN (?, ?, ?) LIMIT ?, ?"));
        assertThat(parameters, is(Arrays.<Object>asList(1, 2, 100, 0, 7)));
    
        parameters = Arrays.<Object>asList(1, 2, 100, 5);
        assertMultipleTargets("select * from order where order_id in (?,?,?) limit 2,?", parameters, 4,
                Arrays.asList("ds_0", "ds_1"), Arrays.asList("SELECT * FROM order_0 WHERE order_id IN (?, ?, ?) LIMIT 0, ?", "SELECT * FROM order_1 WHERE order_id IN (?, ?, ?) LIMIT 0, ?"));
        assertThat(parameters, is(Arrays.<Object>asList(1, 2, 100, 7)));
    
        parameters = Arrays.<Object>asList(1, 2, 100, 2);
        assertMultipleTargets("select * from order where order_id in (?,?,?) limit ?,5", parameters, 4,
                Arrays.asList("ds_0", "ds_1"), Arrays.asList("SELECT * FROM order_0 WHERE order_id IN (?, ?, ?) LIMIT ?, 7", "SELECT * FROM order_1 WHERE order_id IN (?, ?, ?) LIMIT ?, 7"));
        assertThat(parameters, is(Arrays.<Object>asList(1, 2, 100, 0)));
    }
    
    @Test
    public void assertSelectOrLimit() throws SQLParserException {
        assertMultipleTargets("select * from order where order_id = ? or order_id = ? or order_id = ? limit 5", Arrays.<Object>asList(1, 2, 100), 2,
                Arrays.asList("ds_0", "ds_1"), 
                Arrays.asList("SELECT * FROM order_0 WHERE order_id = ? OR order_id = ? OR order_id = ? LIMIT 5", "SELECT * FROM order_1 WHERE order_id = ? OR order_id = ? OR order_id = ? LIMIT 5"));
        assertMultipleTargets("select * from order where order_id = ? or order_id = ? or order_id = ? limit 2,5", Arrays.<Object>asList(1, 2, 100), 2,
                Arrays.asList("ds_0", "ds_1"), Arrays.asList(
                        "SELECT * FROM order_0 WHERE order_id = ? OR order_id = ? OR order_id = ? LIMIT 0, 7", "SELECT * FROM order_1 WHERE order_id = ? OR order_id = ? OR order_id = ? LIMIT 0, 7"));
        assertMultipleTargets("select * from order where order_id = ? or order_id = ? or order_id = ? limit 5 offset 2", Arrays.<Object>asList(1, 2, 100), 2,
                Arrays.asList("ds_0", "ds_1"), Arrays.asList(
                        "SELECT * FROM order_0 WHERE order_id = ? OR order_id = ? OR order_id = ? LIMIT 0, 7", "SELECT * FROM order_1 WHERE order_id = ? OR order_id = ? OR order_id = ? LIMIT 0, 7"));
    
        List<Object> parameters = Arrays.<Object>asList(1, 2, 100, 5);
        assertMultipleTargets("select * from order where order_id = ? or order_id = ? or order_id = ? limit ?", parameters, 2, Arrays.asList("ds_0", "ds_1"), 
                Arrays.asList("SELECT * FROM order_0 WHERE order_id = ? OR order_id = ? OR order_id = ? LIMIT ?", "SELECT * FROM order_1 WHERE order_id = ? OR order_id = ? OR order_id = ? LIMIT ?"));
        assertThat(parameters, is(Arrays.<Object>asList(1, 2, 100, 5)));
    
        parameters = Arrays.<Object>asList(1, 2, 100, 2, 5);
        assertMultipleTargets("select * from order where order_id = ? or order_id = ? or order_id = ? limit ?,?", parameters, 2, Arrays.asList("ds_0", "ds_1"), Arrays.asList(
                "SELECT * FROM order_0 WHERE order_id = ? OR order_id = ? OR order_id = ? LIMIT ?, ?", "SELECT * FROM order_1 WHERE order_id = ? OR order_id = ? OR order_id = ? LIMIT ?, ?"));
        assertThat(parameters, is(Arrays.<Object>asList(1, 2, 100, 0, 7)));
    
        parameters = Arrays.<Object>asList(1, 2, 100, 5, 2);
        assertMultipleTargets("select * from order where order_id = ? or order_id = ? or order_id = ? limit ? offset ?", parameters, 2, Arrays.asList("ds_0", "ds_1"), Arrays.asList(
                "SELECT * FROM order_0 WHERE order_id = ? OR order_id = ? OR order_id = ? LIMIT ?, ?", "SELECT * FROM order_1 WHERE order_id = ? OR order_id = ? OR order_id = ? LIMIT ?, ?"));
        assertThat(parameters, is(Arrays.<Object>asList(1, 2, 100, 0, 7)));
    
        parameters = Arrays.<Object>asList(1, 2, 100, 5);
        assertMultipleTargets("select * from order where order_id = ? or order_id = ? or order_id = ? limit 2,?", parameters, 2, Arrays.asList("ds_0", "ds_1"), Arrays.asList(
                "SELECT * FROM order_0 WHERE order_id = ? OR order_id = ? OR order_id = ? LIMIT 0, ?", "SELECT * FROM order_1 WHERE order_id = ? OR order_id = ? OR order_id = ? LIMIT 0, ?"));
        assertThat(parameters, is(Arrays.<Object>asList(1, 2, 100, 7)));
    
        parameters = Arrays.<Object>asList(1, 2, 100, 2);
        assertMultipleTargets("select * from order where order_id = ? or order_id = ? or order_id = ? limit ?,5", parameters, 2, Arrays.asList("ds_0", "ds_1"), Arrays.asList(
                "SELECT * FROM order_0 WHERE order_id = ? OR order_id = ? OR order_id = ? LIMIT ?, 7", "SELECT * FROM order_1 WHERE order_id = ? OR order_id = ? OR order_id = ? LIMIT ?, 7"));
        assertThat(parameters, is(Arrays.<Object>asList(1, 2, 100, 0)));
    }
}