Examples with MemoryMetadataStorageManager herddb.mem.MemoryMetadataStorageManager used on opensource projects

Example 31 with MemoryMetadataStorageManager

use of herddb.mem.MemoryMetadataStorageManager 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 32 with MemoryMetadataStorageManager

use of herddb.mem.MemoryMetadataStorageManager 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 33 with MemoryMetadataStorageManager

use of herddb.mem.MemoryMetadataStorageManager 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());
    }
}

Example 34 with MemoryMetadataStorageManager

use of herddb.mem.MemoryMetadataStorageManager in project herddb by diennea.

the class TruncateTableSQLTest method truncateTableNoTransaction.

@Test
public void truncateTableNoTransaction() 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.tsql (k1 string primary key,n1 int,s1 string)", Collections.emptyList());
        execute(manager, "CREATE BRIN INDEX test1 ON tblspace1.tsql (k1)", Collections.emptyList());
        execute(manager, "CREATE HASH INDEX test2 ON tblspace1.tsql (k1)", Collections.emptyList());
        execute(manager, "INSERT INTO tblspace1.tsql (k1) values('a')", Collections.emptyList(), TransactionContext.NO_TRANSACTION);
        try (DataScanner scan = scan(manager, "SELECT * FROM tblspace1.tsql ", Collections.emptyList())) {
            assertEquals(1, scan.consume().size());
        } catch (TableDoesNotExistException ok) {
        }
        execute(manager, "TRUNCATE TABLE tblspace1.tsql", Collections.emptyList());
        try (DataScanner scan = scan(manager, "SELECT * FROM tblspace1.tsql ", Collections.emptyList())) {
            assertEquals(0, scan.consume().size());
        } catch (TableDoesNotExistException ok) {
        }
    }
}

Example 35 with MemoryMetadataStorageManager

use of herddb.mem.MemoryMetadataStorageManager in project herddb by diennea.

the class UpdateTest method updateWithJDBC.

@Test
public void updateWithJDBC() 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(), NO_TRANSACTION);
        manager.waitForTablespace("tblspace1", 10000);
        execute(manager, "CREATE TABLE tblspace1.tsql (K1 string ,s1 string,n1 int, primary key(k1,s1))", Collections.emptyList());
        assertEquals(1, executeUpdate(manager, "INSERT INTO tblspace1.tsql(k1,s1,n1) values(?,?,?)", Arrays.asList("mykey", "a", Integer.valueOf(1234))).getUpdateCount());
        assertEquals(1, executeUpdate(manager, "INSERT INTO tblspace1.tsql(k1,s1,n1) values(?,?,?)", Arrays.asList("mykey2", "a", Integer.valueOf(1234))).getUpdateCount());
        assertEquals(1, executeUpdate(manager, "INSERT INTO tblspace1.tsql(k1,s1,n1) values(?,?,?)", Arrays.asList("mykey3", "a", Integer.valueOf(1234))).getUpdateCount());
        assertEquals(1, executeUpdate(manager, "INSERT INTO tblspace1.tsql(k1,s1,n1) values(?,?,?)", Arrays.asList("mykey4", "a", Integer.valueOf(1234))).getUpdateCount());
        assertEquals(1, executeUpdate(manager, "UPDATE tblspace1.tsql SET n1 = n1 + ? WHERE k1 = ?", Arrays.asList(Integer.valueOf(1234), "mykey")).getUpdateCount());
        assertEquals(1, executeUpdate(manager, "UPDATE tblspace1.tsql SET n1 = n1 - ? WHERE k1 = ?", Arrays.asList(Integer.valueOf(1234), "mykey")).getUpdateCount());
        assertEquals(1, executeUpdate(manager, "UPDATE tblspace1.tsql SET n1 = ? + n1 WHERE k1 = ?", Arrays.asList(Integer.valueOf(1234), "mykey")).getUpdateCount());
        assertEquals(1, executeUpdate(manager, "UPDATE tblspace1.tsql SET n1 = ? + n1 WHERE k1 = ?", Arrays.asList(Integer.valueOf(1234), "mykey")).getUpdateCount());
        assertEquals(1, executeUpdate(manager, "UPDATE tblspace1.tsql SET n1 = ? + n1 WHERE k1 = ? AND s1 = ?", Arrays.asList(Integer.valueOf(1234), "mykey", "a")).getUpdateCount());
    }
}