Examples with CreateTableSpaceStatement herddb.model.commands.CreateTableSpaceStatement used on opensource projects

Example 36 with CreateTableSpaceStatement

use of herddb.model.commands.CreateTableSpaceStatement in project herddb by diennea.

the class SecondaryIndexAccessSuite method createIndexInTransaction1.

@Test
public void createIndexInTransaction1() throws Exception {
    String nodeId = "localhost";
    try (DBManager manager = new DBManager("localhost", new MemoryMetadataStorageManager(), new MemoryDataStorageManager(), new MemoryCommitLogManager(), null, null)) {
        manager.start();
        CreateTableSpaceStatement st1 = new CreateTableSpaceStatement("tblspace1", Collections.singleton(nodeId), nodeId, 1, 0, 0);
        manager.executeStatement(st1, StatementEvaluationContext.DEFAULT_EVALUATION_CONTEXT(), TransactionContext.NO_TRANSACTION);
        manager.waitForTablespace("tblspace1", 10000);
        Bytes key = Bytes.from_int(1234);
        Bytes value = Bytes.from_long(8888);
        Table transacted_table = Table.builder().tablespace("tblspace1").name("t1").column("id", ColumnTypes.STRING).column("name", ColumnTypes.STRING).primaryKey("id").build();
        long tx = TestUtils.beginTransaction(manager, "tblspace1");
        CreateTableStatement st_create = new CreateTableStatement(transacted_table);
        manager.executeStatement(st_create, StatementEvaluationContext.DEFAULT_EVALUATION_CONTEXT(), new TransactionContext(tx));
        TestUtils.executeUpdate(manager, "INSERT INTO tblspace1.t1(id,name) values(?,?)", Arrays.asList("a", "n1"), new TransactionContext(tx));
        TestUtils.executeUpdate(manager, "INSERT INTO tblspace1.t1(id,name) values(?,?)", Arrays.asList("b", "n1"), new TransactionContext(tx));
        TestUtils.executeUpdate(manager, "INSERT INTO tblspace1.t1(id,name) values(?,?)", Arrays.asList("c", "n2"), new TransactionContext(tx));
        Index index = Index.builder().onTable(transacted_table).type(indexType).column("name", ColumnTypes.STRING).build();
        CreateIndexStatement createIndex = new CreateIndexStatement(index);
        manager.executeStatement(createIndex, StatementEvaluationContext.DEFAULT_EVALUATION_CONTEXT(), new TransactionContext(tx));
        {
            TranslatedQuery translated = manager.getPlanner().translate(TableSpace.DEFAULT, "SELECT * FROM tblspace1.t1 WHERE name='n1'", Collections.emptyList(), true, true, false, -1);
            ScanStatement scan = translated.plan.mainStatement.unwrap(ScanStatement.class);
            // uncommitted indexes are not used
            assertFalse(scan.getPredicate().getIndexOperation() instanceof SecondaryIndexSeek);
            try (DataScanner scan1 = manager.scan(scan, translated.context, new TransactionContext(tx))) {
                assertEquals(2, scan1.consume().size());
            }
        }
        manager.executeStatement(new CommitTransactionStatement("tblspace1", tx), StatementEvaluationContext.DEFAULT_EVALUATION_CONTEXT(), TransactionContext.NO_TRANSACTION);
        {
            TranslatedQuery translated = manager.getPlanner().translate(TableSpace.DEFAULT, "SELECT * FROM tblspace1.t1 WHERE name='n1'", Collections.emptyList(), true, true, false, -1);
            ScanStatement scan = translated.plan.mainStatement.unwrap(ScanStatement.class);
            assertTrue(scan.getPredicate().getIndexOperation() instanceof SecondaryIndexSeek);
            try (DataScanner scan1 = manager.scan(scan, translated.context, TransactionContext.NO_TRANSACTION)) {
                assertEquals(2, scan1.consume().size());
            }
        }
    }
}

Example 37 with CreateTableSpaceStatement

use of herddb.model.commands.CreateTableSpaceStatement in project herddb by diennea.

the class SimpleJoinTest method testSimpleJoinNoWhere.

@Test
public void testSimpleJoinNoWhere() throws Exception {
    String nodeId = "localhost";
    try (DBManager manager = new DBManager("localhost", new MemoryMetadataStorageManager(), new MemoryDataStorageManager(), new MemoryCommitLogManager(), null, null)) {
        manager.start();
        CreateTableSpaceStatement st1 = new CreateTableSpaceStatement("tblspace1", Collections.singleton(nodeId), nodeId, 1, 0, 0);
        manager.executeStatement(st1, StatementEvaluationContext.DEFAULT_EVALUATION_CONTEXT(), TransactionContext.NO_TRANSACTION);
        manager.waitForTablespace("tblspace1", 10000);
        execute(manager, "CREATE TABLE tblspace1.table1 (k1 string primary key,n1 int,s1 string)", Collections.emptyList());
        execute(manager, "CREATE TABLE tblspace1.table2 (k2 string primary key,n2 int,s2 string)", Collections.emptyList());
        execute(manager, "INSERT INTO tblspace1.table1 (k1,n1,s1) values('a',1,'A')", Collections.emptyList());
        execute(manager, "INSERT INTO tblspace1.table1 (k1,n1,s1) values('b',2,'B')", Collections.emptyList());
        execute(manager, "INSERT INTO tblspace1.table2 (k2,n2,s2) values('c',3,'A')", Collections.emptyList());
        execute(manager, "INSERT INTO tblspace1.table2 (k2,n2,s2) values('d',4,'A')", Collections.emptyList());
        {
            List<DataAccessor> tuples = scan(manager, "SELECT * FROM" + " tblspace1.table1 t1" + " NATURAL JOIN tblspace1.table2 t2" + " WHERE t1.n1 > 0" + "   and t2.n2 >= 1", Collections.emptyList()).consume();
            for (DataAccessor t : tuples) {
                System.out.println("t:" + t);
                assertEquals(6, t.getFieldNames().length);
                assertEquals("k1", t.getFieldNames()[0]);
                assertEquals("n1", t.getFieldNames()[1]);
                assertEquals("s1", t.getFieldNames()[2]);
                assertEquals("k2", t.getFieldNames()[3]);
                assertEquals("n2", t.getFieldNames()[4]);
                assertEquals("s2", t.getFieldNames()[5]);
            }
            assertEquals(4, tuples.size());
            assertTrue(tuples.stream().anyMatch(t -> t.toMap().equals(MapUtils.map("k1", "a", "n1", 1, "s1", "A", "k2", "c", "n2", 3, "s2", "A"))));
            assertTrue(tuples.stream().anyMatch(t -> t.toMap().equals(MapUtils.map("k1", "a", "n1", 1, "s1", "A", "k2", "d", "n2", 4, "s2", "A"))));
            assertTrue(tuples.stream().anyMatch(t -> t.toMap().equals(MapUtils.map("k1", "b", "n1", 2, "s1", "B", "k2", "c", "n2", 3, "s2", "A"))));
            assertTrue(tuples.stream().anyMatch(t -> t.toMap().equals(MapUtils.map("k1", "b", "n1", 2, "s1", "B", "k2", "d", "n2", 4, "s2", "A"))));
        }
        {
            List<DataAccessor> tuples = scan(manager, "SELECT t1.k1, t2.k2 FROM" + " tblspace1.table1 t1 " + " NATURAL JOIN tblspace1.table2 t2 " + " WHERE t1.n1 > 0" + "   and t2.n2 >= 1", Collections.emptyList()).consume();
            for (DataAccessor t : tuples) {
                assertEquals(2, t.getFieldNames().length);
                assertEquals("k1", t.getFieldNames()[0]);
                assertEquals("k2", t.getFieldNames()[1]);
            }
            assertEquals(4, tuples.size());
            assertTrue(tuples.stream().anyMatch(t -> t.toMap().equals(MapUtils.map("k1", "a", "k2", "c"))));
            assertTrue(tuples.stream().anyMatch(t -> t.toMap().equals(MapUtils.map("k1", "a", "k2", "d"))));
            assertTrue(tuples.stream().anyMatch(t -> t.toMap().equals(MapUtils.map("k1", "b", "k2", "c"))));
            assertTrue(tuples.stream().anyMatch(t -> t.toMap().equals(MapUtils.map("k1", "b", "k2", "d"))));
        }
        {
            List<DataAccessor> tuples = scan(manager, "SELECT t1.k1, t2.k2 FROM" + " tblspace1.table1 t1 " + " NATURAL JOIN tblspace1.table2 t2 " + " WHERE t1.n1 >= 2" + "   and t2.n2 >= 4", Collections.emptyList()).consume();
            for (DataAccessor t : tuples) {
                assertEquals(2, t.getFieldNames().length);
                assertEquals("k1", t.getFieldNames()[0]);
                assertEquals("k2", t.getFieldNames()[1]);
            }
            assertEquals(1, tuples.size());
            assertTrue(tuples.stream().anyMatch(t -> t.toMap().equals(MapUtils.map("k1", "b", "k2", "d"))));
        }
        {
            List<DataAccessor> tuples = scan(manager, "SELECT t1.*,t2.* FROM" + " tblspace1.table1 t1" + " NATURAL JOIN tblspace1.table2 t2" + " WHERE t1.n1 > 0" + "   and t2.n2 >= 1", Collections.emptyList()).consume();
            for (DataAccessor t : tuples) {
                assertEquals(6, t.getFieldNames().length);
                assertEquals("k1", t.getFieldNames()[0]);
                assertEquals("n1", t.getFieldNames()[1]);
                assertEquals("s1", t.getFieldNames()[2]);
                assertEquals("k2", t.getFieldNames()[3]);
                assertEquals("n2", t.getFieldNames()[4]);
                assertEquals("s2", t.getFieldNames()[5]);
            }
            assertEquals(4, tuples.size());
            assertTrue(tuples.stream().anyMatch(t -> t.toMap().equals(MapUtils.map("k1", "a", "n1", 1, "s1", "A", "k2", "c", "n2", 3, "s2", "A"))));
            assertTrue(tuples.stream().anyMatch(t -> t.toMap().equals(MapUtils.map("k1", "a", "n1", 1, "s1", "A", "k2", "d", "n2", 4, "s2", "A"))));
            assertTrue(tuples.stream().anyMatch(t -> t.toMap().equals(MapUtils.map("k1", "b", "n1", 2, "s1", "B", "k2", "c", "n2", 3, "s2", "A"))));
            assertTrue(tuples.stream().anyMatch(t -> t.toMap().equals(MapUtils.map("k1", "b", "n1", 2, "s1", "B", "k2", "d", "n2", 4, "s2", "A"))));
        }
        {
            List<DataAccessor> tuples = scan(manager, "SELECT t1.* FROM" + " tblspace1.table1 t1" + " NATURAL JOIN tblspace1.table2 t2" + " WHERE t1.n1 > 0" + "   and t2.n2 >= 1", Collections.emptyList()).consume();
            for (DataAccessor t : tuples) {
                assertEquals(3, t.getFieldNames().length);
                assertEquals("k1", t.getFieldNames()[0]);
                assertEquals("n1", t.getFieldNames()[1]);
                assertEquals("s1", t.getFieldNames()[2]);
            }
            assertEquals(4, tuples.size());
            assertTrue(tuples.stream().anyMatch(t -> t.toMap().equals(MapUtils.map("k1", "a", "n1", 1, "s1", "A"))));
            assertTrue(tuples.stream().anyMatch(t -> t.toMap().equals(MapUtils.map("k1", "a", "n1", 1, "s1", "A"))));
            assertTrue(tuples.stream().anyMatch(t -> t.toMap().equals(MapUtils.map("k1", "b", "n1", 2, "s1", "B"))));
            assertTrue(tuples.stream().anyMatch(t -> t.toMap().equals(MapUtils.map("k1", "b", "n1", 2, "s1", "B"))));
        }
        {
            List<DataAccessor> tuples = scan(manager, "SELECT t2.* FROM" + " tblspace1.table1 t1" + " NATURAL JOIN tblspace1.table2 t2" + " WHERE t1.n1 > 0" + "   and t2.n2 >= 1", Collections.emptyList()).consume();
            for (DataAccessor t : tuples) {
                assertEquals(3, t.getFieldNames().length);
                assertEquals("k2", t.getFieldNames()[0]);
                assertEquals("n2", t.getFieldNames()[1]);
                assertEquals("s2", t.getFieldNames()[2]);
            }
            assertEquals(4, tuples.size());
            assertTrue(tuples.stream().anyMatch(t -> t.toMap().equals(MapUtils.map("k2", "c", "n2", 3, "s2", "A"))));
            assertTrue(tuples.stream().anyMatch(t -> t.toMap().equals(MapUtils.map("k2", "d", "n2", 4, "s2", "A"))));
            assertTrue(tuples.stream().anyMatch(t -> t.toMap().equals(MapUtils.map("k2", "c", "n2", 3, "s2", "A"))));
            assertTrue(tuples.stream().anyMatch(t -> t.toMap().equals(MapUtils.map("k2", "d", "n2", 4, "s2", "A"))));
        }
        {
            List<DataAccessor> tuples = scan(manager, "SELECT t2.s2 FROM" + " tblspace1.table1 t1" + " NATURAL JOIN tblspace1.table2 t2" + " WHERE t1.n1 > 0" + "   and t2.n2 >= 1", Collections.emptyList()).consume();
            for (DataAccessor t : tuples) {
                assertEquals(1, t.getFieldNames().length);
                assertEquals("s2", t.getFieldNames()[0]);
            }
            assertEquals(4, tuples.size());
            assertTrue(tuples.stream().anyMatch(t -> t.toMap().equals(MapUtils.map("s2", "A"))));
            assertTrue(tuples.stream().anyMatch(t -> t.toMap().equals(MapUtils.map("s2", "A"))));
            assertTrue(tuples.stream().anyMatch(t -> t.toMap().equals(MapUtils.map("s2", "A"))));
            assertTrue(tuples.stream().anyMatch(t -> t.toMap().equals(MapUtils.map("s2", "A"))));
        }
        {
            List<DataAccessor> tuples = scan(manager, "SELECT * FROM" + " tblspace1.table1 t1" + " NATURAL JOIN tblspace1.table2 t2" + " WHERE t1.n1 > 0" + "   and t2.n2 >= 4", Collections.emptyList()).consume();
            for (DataAccessor t : tuples) {
                assertEquals(6, t.getFieldNames().length);
                assertEquals("k1", t.getFieldNames()[0]);
                assertEquals("n1", t.getFieldNames()[1]);
                assertEquals("s1", t.getFieldNames()[2]);
                assertEquals("k2", t.getFieldNames()[3]);
                assertEquals("n2", t.getFieldNames()[4]);
                assertEquals("s2", t.getFieldNames()[5]);
            }
            assertEquals(2, tuples.size());
            assertTrue(tuples.stream().anyMatch(t -> t.toMap().equals(MapUtils.map("k1", "a", "n1", 1, "s1", "A", "k2", "d", "n2", 4, "s2", "A"))));
            assertTrue(tuples.stream().anyMatch(t -> t.toMap().equals(MapUtils.map("k1", "b", "n1", 2, "s1", "B", "k2", "d", "n2", 4, "s2", "A"))));
        }
        {
            List<DataAccessor> tuples = scan(manager, "SELECT * FROM" + " tblspace1.table1 t1" + " NATURAL JOIN tblspace1.table2 t2" + " WHERE t1.n1 <= t2.n2", Collections.emptyList()).consume();
            for (DataAccessor t : tuples) {
                assertEquals(6, t.getFieldNames().length);
                assertEquals("k1", t.getFieldNames()[0]);
                assertEquals("n1", t.getFieldNames()[1]);
                assertEquals("s1", t.getFieldNames()[2]);
                assertEquals("k2", t.getFieldNames()[3]);
                assertEquals("n2", t.getFieldNames()[4]);
                assertEquals("s2", t.getFieldNames()[5]);
            }
            assertEquals(4, tuples.size());
            assertTrue(tuples.stream().anyMatch(t -> t.toMap().equals(MapUtils.map("k1", "a", "n1", 1, "s1", "A", "k2", "c", "n2", 3, "s2", "A"))));
            assertTrue(tuples.stream().anyMatch(t -> t.toMap().equals(MapUtils.map("k1", "a", "n1", 1, "s1", "A", "k2", "d", "n2", 4, "s2", "A"))));
            assertTrue(tuples.stream().anyMatch(t -> t.toMap().equals(MapUtils.map("k1", "b", "n1", 2, "s1", "B", "k2", "c", "n2", 3, "s2", "A"))));
            assertTrue(tuples.stream().anyMatch(t -> t.toMap().equals(MapUtils.map("k1", "b", "n1", 2, "s1", "B", "k2", "d", "n2", 4, "s2", "A"))));
        }
        {
            List<DataAccessor> tuples = scan(manager, "SELECT * FROM " + " tblspace1.table1 t1 " + " NATURAL JOIN tblspace1.table2 t2 " + " WHERE t1.n1 <= t2.n2 " + "and t2.n2 <= 3", Collections.emptyList()).consume();
            for (DataAccessor t : tuples) {
                assertEquals(6, t.getFieldNames().length);
                assertEquals("k1", t.getFieldNames()[0]);
                assertEquals("n1", t.getFieldNames()[1]);
                assertEquals("s1", t.getFieldNames()[2]);
                assertEquals("k2", t.getFieldNames()[3]);
                assertEquals("n2", t.getFieldNames()[4]);
                assertEquals("s2", t.getFieldNames()[5]);
            }
            assertEquals(2, tuples.size());
            assertTrue(tuples.stream().anyMatch(t -> t.toMap().equals(MapUtils.map("k1", "a", "n1", 1, "s1", "A", "k2", "c", "n2", 3, "s2", "A"))));
            assertTrue(tuples.stream().anyMatch(t -> t.toMap().equals(MapUtils.map("k1", "b", "n1", 2, "s1", "B", "k2", "c", "n2", 3, "s2", "A"))));
        }
        {
            List<DataAccessor> tuples = scan(manager, "SELECT * FROM " + " tblspace1.table1 t1 " + " JOIN tblspace1.table2 t2 ON t1.n1 <= t2.n2 " + " and t2.n2 <= 3", Collections.emptyList()).consume();
            for (DataAccessor t : tuples) {
                assertEquals(6, t.getFieldNames().length);
                assertEquals("k1", t.getFieldNames()[0]);
                assertEquals("n1", t.getFieldNames()[1]);
                assertEquals("s1", t.getFieldNames()[2]);
                assertEquals("k2", t.getFieldNames()[3]);
                assertEquals("n2", t.getFieldNames()[4]);
                assertEquals("s2", t.getFieldNames()[5]);
            }
            assertEquals(2, tuples.size());
            assertTrue(tuples.stream().anyMatch(t -> t.toMap().equals(MapUtils.map("k1", "a", "n1", 1, "s1", "A", "k2", "c", "n2", 3, "s2", "A"))));
            assertTrue(tuples.stream().anyMatch(t -> t.toMap().equals(MapUtils.map("k1", "b", "n1", 2, "s1", "B", "k2", "c", "n2", 3, "s2", "A"))));
        }
        {
            List<DataAccessor> tuples = scan(manager, "SELECT t1.k1, t2.k2 FROM" + " tblspace1.table1 t1 " + " NATURAL JOIN tblspace1.table2 t2 " + " WHERE t1.n1 + 3 = t2.n2", Collections.emptyList()).consume();
            for (DataAccessor t : tuples) {
                System.out.println("tuple -: " + t.toMap());
                assertEquals(2, t.getFieldNames().length);
                assertEquals("k1", t.getFieldNames()[0]);
                assertEquals("k2", t.getFieldNames()[1]);
            }
            assertEquals(1, tuples.size());
            assertTrue(tuples.stream().anyMatch(t -> t.toMap().equals(MapUtils.map("k1", "a", "k2", "d"))));
        }
        {
            List<DataAccessor> tuples = scan(manager, "SELECT t2.n2, t1.s1, t2.k2 FROM" + " tblspace1.table1 t1" + " NATURAL JOIN tblspace1.table2 t2" + " WHERE t1.n1 > 0" + "   and t2.n2 >= 1", Collections.emptyList()).consume();
            for (DataAccessor t : tuples) {
                assertEquals(3, t.getFieldNames().length);
                assertEquals("n2", t.getFieldNames()[0]);
                assertEquals("s1", t.getFieldNames()[1]);
                assertEquals("k2", t.getFieldNames()[2]);
            }
            assertEquals(4, tuples.size());
            assertTrue(tuples.stream().anyMatch(t -> t.toMap().equals(MapUtils.map("s1", "A", "k2", "c", "n2", 3))));
            assertTrue(tuples.stream().anyMatch(t -> t.toMap().equals(MapUtils.map("s1", "A", "k2", "d", "n2", 4))));
            assertTrue(tuples.stream().anyMatch(t -> t.toMap().equals(MapUtils.map("s1", "B", "k2", "c", "n2", 3))));
            assertTrue(tuples.stream().anyMatch(t -> t.toMap().equals(MapUtils.map("s1", "B", "k2", "d", "n2", 4))));
        }
        {
            List<DataAccessor> tuples = scan(manager, "SELECT t2.*, t1.* FROM" + " tblspace1.table1 t1" + " NATURAL JOIN tblspace1.table2 t2" + " WHERE t1.n1 > 0" + "   and t2.n2 >= 1", Collections.emptyList()).consume();
            for (DataAccessor t : tuples) {
                assertEquals(6, t.getFieldNames().length);
                assertEquals("k2", t.getFieldNames()[0]);
                assertEquals("n2", t.getFieldNames()[1]);
                assertEquals("s2", t.getFieldNames()[2]);
                assertEquals("k1", t.getFieldNames()[3]);
                assertEquals("n1", t.getFieldNames()[4]);
                assertEquals("s1", t.getFieldNames()[5]);
            }
            assertEquals(4, tuples.size());
            assertTrue(tuples.stream().anyMatch(t -> t.toMap().equals(MapUtils.map("k1", "a", "n1", 1, "s1", "A", "k2", "c", "n2", 3, "s2", "A"))));
            assertTrue(tuples.stream().anyMatch(t -> t.toMap().equals(MapUtils.map("k1", "a", "n1", 1, "s1", "A", "k2", "d", "n2", 4, "s2", "A"))));
            assertTrue(tuples.stream().anyMatch(t -> t.toMap().equals(MapUtils.map("k1", "b", "n1", 2, "s1", "B", "k2", "c", "n2", 3, "s2", "A"))));
            assertTrue(tuples.stream().anyMatch(t -> t.toMap().equals(MapUtils.map("k1", "b", "n1", 2, "s1", "B", "k2", "d", "n2", 4, "s2", "A"))));
        }
        {
            List<DataAccessor> tuples = scan(manager, "SELECT * FROM" + " tblspace1.table1 t1" + " NATURAL JOIN tblspace1.table2 t2 " + " ORDER BY n2,n1", Collections.emptyList()).consume();
            for (DataAccessor t : tuples) {
                assertEquals(6, t.getFieldNames().length);
                assertEquals("k1", t.getFieldNames()[0]);
                assertEquals("n1", t.getFieldNames()[1]);
                assertEquals("s1", t.getFieldNames()[2]);
                assertEquals("k2", t.getFieldNames()[3]);
                assertEquals("n2", t.getFieldNames()[4]);
                assertEquals("s2", t.getFieldNames()[5]);
            }
            assertEquals(4, tuples.size());
            int i = 0;
            assertTrue(tuples.get(i++).toMap().equals(MapUtils.map("k1", "a", "n1", 1, "s1", "A", "k2", "c", "n2", 3, "s2", "A")));
            assertTrue(tuples.get(i++).toMap().equals(MapUtils.map("k1", "b", "n1", 2, "s1", "B", "k2", "c", "n2", 3, "s2", "A")));
            assertTrue(tuples.get(i++).toMap().equals(MapUtils.map("k1", "a", "n1", 1, "s1", "A", "k2", "d", "n2", 4, "s2", "A")));
            assertTrue(tuples.get(i++).toMap().equals(MapUtils.map("k1", "b", "n1", 2, "s1", "B", "k2", "d", "n2", 4, "s2", "A")));
        }
        {
            List<DataAccessor> tuples = scan(manager, "SELECT * FROM" + " tblspace1.table1 t1" + " NATURAL JOIN tblspace1.table2 t2 " + " ORDER BY n2 desc,n1", Collections.emptyList()).consume();
            for (DataAccessor t : tuples) {
                assertEquals(6, t.getFieldNames().length);
                assertEquals("k1", t.getFieldNames()[0]);
                assertEquals("n1", t.getFieldNames()[1]);
                assertEquals("s1", t.getFieldNames()[2]);
                assertEquals("k2", t.getFieldNames()[3]);
                assertEquals("n2", t.getFieldNames()[4]);
                assertEquals("s2", t.getFieldNames()[5]);
            }
            assertEquals(4, tuples.size());
            int i = 0;
            assertTrue(tuples.get(i++).toMap().equals(MapUtils.map("k1", "a", "n1", 1, "s1", "A", "k2", "d", "n2", 4, "s2", "A")));
            assertTrue(tuples.get(i++).toMap().equals(MapUtils.map("k1", "b", "n1", 2, "s1", "B", "k2", "d", "n2", 4, "s2", "A")));
            assertTrue(tuples.get(i++).toMap().equals(MapUtils.map("k1", "a", "n1", 1, "s1", "A", "k2", "c", "n2", 3, "s2", "A")));
            assertTrue(tuples.get(i++).toMap().equals(MapUtils.map("k1", "b", "n1", 2, "s1", "B", "k2", "c", "n2", 3, "s2", "A")));
        }
        {
            List<DataAccessor> tuples = scan(manager, "SELECT t1.k1, t2.k2 FROM" + " tblspace1.table1 t1 " + " NATURAL JOIN tblspace1.table2 t2 " + " WHERE t1.n1 + 3 <= t2.n2", Collections.emptyList()).consume();
            for (DataAccessor t : tuples) {
                System.out.println("tuple -: " + t.toMap());
                assertEquals(2, t.getFieldNames().length);
                assertEquals("k1", t.getFieldNames()[0]);
                assertEquals("k2", t.getFieldNames()[1]);
            }
            assertEquals(1, tuples.size());
            assertTrue(tuples.stream().anyMatch(t -> t.toMap().equals(MapUtils.map("k1", "a", "k2", "d"))));
        }
        if (manager.getPlanner() instanceof CalcitePlanner) {
            List<DataAccessor> tuples = scan(manager, "SELECT t1.k1, t2.k2, t1.s1, t2.s2 FROM" + " tblspace1.table1 t1 " + " LEFT JOIN tblspace1.table2 t2 " + " ON t1.s1 = t2.s2" + " ", Collections.emptyList()).consume();
            for (DataAccessor t : tuples) {
                System.out.println("tuple -: " + t.toMap());
                assertEquals(4, t.getFieldNames().length);
                assertEquals("k1", t.getFieldNames()[0]);
                assertEquals("k2", t.getFieldNames()[1]);
                assertEquals("s1", t.getFieldNames()[2]);
                assertEquals("s2", t.getFieldNames()[3]);
            }
            assertEquals(3, tuples.size());
            assertTrue(tuples.stream().anyMatch(t -> t.toMap().equals(MapUtils.map("k1", "a", "k2", "c", "s1", "A", "s2", "A"))));
            assertTrue(tuples.stream().anyMatch(t -> t.toMap().equals(MapUtils.map("k1", "a", "k2", "d", "s1", "A", "s2", "A"))));
            assertTrue(tuples.stream().anyMatch(t -> t.toMap().equals(MapUtils.map("k1", "b", "k2", null, "s1", "B", "s2", null))));
        }
        if (manager.getPlanner() instanceof CalcitePlanner) {
            List<DataAccessor> tuples = scan(manager, "SELECT t1.k1, t2.k2, t1.s1, t2.s2 FROM" + " tblspace1.table1 t1 " + " RIGHT JOIN tblspace1.table2 t2 " + " ON t1.s1 = t2.s2" + " ", Collections.emptyList()).consume();
            for (DataAccessor t : tuples) {
                System.out.println("tuple -: " + t.toMap());
                assertEquals(4, t.getFieldNames().length);
                assertEquals("k1", t.getFieldNames()[0]);
                assertEquals("k2", t.getFieldNames()[1]);
                assertEquals("s1", t.getFieldNames()[2]);
                assertEquals("s2", t.getFieldNames()[3]);
            }
            assertEquals(2, tuples.size());
            assertTrue(tuples.stream().anyMatch(t -> t.toMap().equals(MapUtils.map("k1", "a", "k2", "c", "s1", "A", "s2", "A"))));
            assertTrue(tuples.stream().anyMatch(t -> t.toMap().equals(MapUtils.map("k1", "a", "k2", "d", "s1", "A", "s2", "A"))));
        }
    }
}

Example 38 with CreateTableSpaceStatement

use of herddb.model.commands.CreateTableSpaceStatement in project herddb by diennea.

the class SimpleJoinTest method testSubQueryInSelect.

@Test
public void testSubQueryInSelect() throws Exception {
    String nodeId = "localhost";
    try (DBManager manager = new DBManager("localhost", new MemoryMetadataStorageManager(), new MemoryDataStorageManager(), new MemoryCommitLogManager(), null, null)) {
        assumeThat(manager.getPlanner(), instanceOf(CalcitePlanner.class));
        manager.start();
        CreateTableSpaceStatement st1 = new CreateTableSpaceStatement("tblspace1", Collections.singleton(nodeId), nodeId, 1, 0, 0);
        manager.executeStatement(st1, StatementEvaluationContext.DEFAULT_EVALUATION_CONTEXT(), TransactionContext.NO_TRANSACTION);
        manager.waitForTablespace("tblspace1", 10000);
        execute(manager, "CREATE TABLE tblspace1.table1 (k1 string primary key,n1 int,s1 string)", Collections.emptyList());
        execute(manager, "CREATE TABLE tblspace1.table2 (k2 string primary key,n2 int,s2 string)", Collections.emptyList());
        execute(manager, "INSERT INTO tblspace1.table1 (k1,n1,s1) values('a',1,'A')", Collections.emptyList());
        execute(manager, "INSERT INTO tblspace1.table1 (k1,n1,s1) values('b',2,'B')", Collections.emptyList());
        execute(manager, "INSERT INTO tblspace1.table2 (k2,n2,s2) values('c',3,'A')", Collections.emptyList());
        execute(manager, "INSERT INTO tblspace1.table2 (k2,n2,s2) values('d',4,'A')", Collections.emptyList());
        {
            List<DataAccessor> tuples = scan(manager, "SELECT t1.k1, max(n1) as maxn1, max(select n2 from tblspace1.table2 t2 WHERE t1.s1=t2.s2) as maxn2 FROM " + " tblspace1.table1 t1 " + " group by k1", Collections.emptyList()).consume();
            for (DataAccessor t : tuples) {
                System.out.println("t:" + t);
                assertEquals(3, t.getFieldNames().length);
                assertEquals("k1", t.getFieldNames()[0]);
                assertEquals("maxn1", t.getFieldNames()[1]);
                assertEquals("maxn2", t.getFieldNames()[2]);
            }
            assertEquals(2, tuples.size());
            assertTrue(tuples.stream().anyMatch(t -> t.toMap().equals(MapUtils.map("k1", RawString.of("a"), "maxn1", 1, "maxn2", 4))));
            assertTrue(tuples.stream().anyMatch(t -> t.toMap().equals(MapUtils.map("k1", RawString.of("b"), "maxn1", 2, "maxn2", null))));
        }
        {
            List<DataAccessor> tuples = scan(manager, "SELECT t1.k1, max(n1) as maxn1, max(select n2 from tblspace1.table2 t2 WHERE t1.s1=t2.s2) as maxn2 FROM " + " tblspace1.table1 t1 " + " group by k1" + " order by max(select n2 from tblspace1.table2 t2 WHERE t1.s1=t2.s2) desc", Collections.emptyList()).consume();
            for (DataAccessor t : tuples) {
                System.out.println("t:" + t);
                assertEquals(3, t.getFieldNames().length);
                assertEquals("k1", t.getFieldNames()[0]);
                assertEquals("maxn1", t.getFieldNames()[1]);
                assertEquals("maxn2", t.getFieldNames()[2]);
            }
            assertEquals(2, tuples.size());
            assertTrue(tuples.get(0).toMap().equals(MapUtils.map("k1", RawString.of("b"), "maxn1", 2, "maxn2", null)));
            assertTrue(tuples.get(1).toMap().equals(MapUtils.map("k1", RawString.of("a"), "maxn1", 1, "maxn2", 4)));
        }
    }
}

Example 39 with CreateTableSpaceStatement

use of herddb.model.commands.CreateTableSpaceStatement in project herddb by diennea.

the class SimpleSubqueryTest method tableAliasTest.

@Test
public void tableAliasTest() throws Exception {
    String nodeId = "localhost";
    try (DBManager manager = new DBManager("localhost", new MemoryMetadataStorageManager(), new MemoryDataStorageManager(), new MemoryCommitLogManager(), null, null)) {
        manager.start();
        CreateTableSpaceStatement st1 = new CreateTableSpaceStatement("tblspace1", Collections.singleton(nodeId), nodeId, 1, 0, 0);
        manager.executeStatement(st1, StatementEvaluationContext.DEFAULT_EVALUATION_CONTEXT(), TransactionContext.NO_TRANSACTION);
        manager.waitForTablespace("tblspace1", 10000);
        execute(manager, "CREATE TABLE tblspace1.table1 (k1 string primary key,n1 int)", Collections.emptyList());
        assertEquals(1, executeUpdate(manager, "INSERT INTO tblspace1.table1(k1,n1) values(?,?)", Arrays.asList("mykey", Integer.valueOf(1234))).getUpdateCount());
        assertEquals(1, executeUpdate(manager, "INSERT INTO tblspace1.table1(k1,n1) values(?,?)", Arrays.asList("mykey2", Integer.valueOf(1234))).getUpdateCount());
        assertEquals(1, executeUpdate(manager, "INSERT INTO tblspace1.table1(k1,n1) values(?,?)", Arrays.asList("mykey3", Integer.valueOf(1234))).getUpdateCount());
        assertEquals(1, executeUpdate(manager, "INSERT INTO tblspace1.table1(k1,n1) values(?,?)", Arrays.asList("mykey4", Integer.valueOf(1234))).getUpdateCount());
        assertEquals(1, scan(manager, "SELECT * " + "FROM tblspace1.table1 t1 " + "WHERE t1.k1='mykey2'" + "", Collections.emptyList()).consume().size());
        assertEquals(1, scan(manager, "SELECT t1.k1 " + "FROM tblspace1.table1 t1 " + "WHERE t1.k1='mykey2'" + "", Collections.emptyList()).consume().size());
        try {
            scan(manager, "SELECT t2.k1 " + "FROM tblspace1.table1 t1", Collections.emptyList());
            fail("query must not work");
        } catch (Exception ok) {
            assertTrue("invalid column name k1 invalid table name t2, expecting t1".equals(ok.getMessage()) || ok.getMessage().contains("Table 'T2' not found"));
        }
        try {
            scan(manager, "SELECT k1 " + "FROM tblspace1.table1 t1 " + "WHERE t2.n1=123", Collections.emptyList());
            fail("query must not work");
        } catch (Exception ok) {
            assertTrue("invalid column name n1 invalid table name t2, expecting t1".equals(ok.getMessage()) || ok.getMessage().contains("Table 'T2' not found"));
        }
        try {
            scan(manager, "SELECT k1 " + "FROM tblspace1.table1 t1 " + "WHERE t2.k1='aaa'", Collections.emptyList());
            fail("query must not work");
        } catch (Exception ok) {
            assertTrue("invalid column name k1 invalid table name t2, expecting t1".equals(ok.getMessage()) || ok.getMessage().contains("Table 'T2' not found"));
        }
        try {
            scan(manager, "SELECT * " + "FROM tblspace1.table1 t2 " + "WHERE t1.k1='mykey2'", Collections.emptyList());
            fail("query must not work");
        } catch (Exception ok) {
            assertTrue("invalid column name k1 invalid table name t1, expecting t2".equals(ok.getMessage()) || ok.getMessage().contains("Table 'T1' not found"));
        }
        try {
            scan(manager, "SELECT * " + "FROM tblspace1.table1 t2 " + "ORDER BY t1.n1", Collections.emptyList());
            fail("query must not work");
        } catch (Exception ok) {
            assertTrue("invalid column name n1 invalid table name t1, expecting t2".equals(ok.getMessage()) || ok.getMessage().contains("Table 'T1' not found"));
        }
    }
}

Example 40 with CreateTableSpaceStatement

use of herddb.model.commands.CreateTableSpaceStatement in project herddb by diennea.

the class SimpleSubqueryTest method subQueryOnWhereTest.

@Test
public void subQueryOnWhereTest() throws Exception {
    String nodeId = "localhost";
    try (DBManager manager = new DBManager("localhost", new MemoryMetadataStorageManager(), new MemoryDataStorageManager(), new MemoryCommitLogManager(), null, null)) {
        manager.start();
        CreateTableSpaceStatement st1 = new CreateTableSpaceStatement("tblspace1", Collections.singleton(nodeId), nodeId, 1, 0, 0);
        manager.executeStatement(st1, StatementEvaluationContext.DEFAULT_EVALUATION_CONTEXT(), TransactionContext.NO_TRANSACTION);
        manager.waitForTablespace("tblspace1", 10000);
        execute(manager, "CREATE TABLE tblspace1.table1 (k1 string primary key,n1 int)", Collections.emptyList());
        execute(manager, "CREATE TABLE tblspace1.table2 (k2 string primary key,fk string)", Collections.emptyList());
        assertEquals(1, executeUpdate(manager, "INSERT INTO tblspace1.table1(k1,n1) values(?,?)", Arrays.asList("mykey", Integer.valueOf(1234))).getUpdateCount());
        assertEquals(1, executeUpdate(manager, "INSERT INTO tblspace1.table1(k1,n1) values(?,?)", Arrays.asList("mykey2", Integer.valueOf(1234))).getUpdateCount());
        assertEquals(1, executeUpdate(manager, "INSERT INTO tblspace1.table1(k1,n1) values(?,?)", Arrays.asList("mykey3", Integer.valueOf(1234))).getUpdateCount());
        assertEquals(1, executeUpdate(manager, "INSERT INTO tblspace1.table1(k1,n1) values(?,?)", Arrays.asList("mykey4", Integer.valueOf(1234))).getUpdateCount());
        assertEquals(1, executeUpdate(manager, "INSERT INTO tblspace1.table2(k2,fk) values(?,?)", Arrays.asList("subkey1", "mykey2")).getUpdateCount());
        assertEquals(1, executeUpdate(manager, "INSERT INTO tblspace1.table2(k2,fk) values(?,?)", Arrays.asList("subkey2", "mykey2")).getUpdateCount());
        assertEquals(1, executeUpdate(manager, "INSERT INTO tblspace1.table2(k2,fk) values(?,?)", Arrays.asList("subkey3", "mykey3")).getUpdateCount());
        assertEquals(1, executeUpdate(manager, "INSERT INTO tblspace1.table2(k2,fk) values(?,?)", Arrays.asList("subkey4", "mykey4")).getUpdateCount());
        assertEquals(2, scan(manager, "SELECT * " + "FROM tblspace1.table1 t1 " + "WHERE t1.k1 in ('mykey','mykey3')" + "", Collections.emptyList()).consume().size());
        assertEquals(1, scan(manager, "SELECT * " + "FROM tblspace1.table1 t1 " + "WHERE t1.k1 in (SELECT fk FROM tblspace1.table2 WHERE k2='subkey4')" + "", Collections.emptyList()).consume().size());
        assertEquals(1, scan(manager, "SELECT * " + "FROM tblspace1.table1 t1 " + "WHERE t1.n1 = ? and t1.k1 in (SELECT fk FROM tblspace1.table2 WHERE k2=?)" + "", Arrays.asList(1234, "subkey4")).consume().size());
        assertEquals(0, scan(manager, "SELECT * " + "FROM tblspace1.table1 t1 " + "WHERE t1.n1 = ? and t1.k1 in (SELECT fk FROM tblspace1.table2 WHERE k2=?)" + "", Arrays.asList(1234, "subkey5")).consume().size());
    }
}