use of herddb.mem.MemoryCommitLogManager 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)));
}
}
}
use of herddb.mem.MemoryCommitLogManager 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"));
}
}
}
use of herddb.mem.MemoryCommitLogManager 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());
}
}
use of herddb.mem.MemoryCommitLogManager 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) {
}
}
}
use of herddb.mem.MemoryCommitLogManager 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());
}
}
Aggregations