Examples with DBManager herddb.core.DBManager used on opensource projects

Example 11 with DBManager

use of herddb.core.DBManager in project herddb by diennea.

the class RawSQLTest method selectWithParameters.

@Test
public void selectWithParameters() throws Exception {
    String nodeId = "localhost";
    try (DBManager manager = new DBManager("localhost", new MemoryMetadataStorageManager(), new MemoryDataStorageManager(), new MemoryCommitLogManager(), null, null)) {
        manager.start();
        assumeTrue(manager.getPlanner() instanceof JSQLParserPlanner);
        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());
        assertEquals(1, executeUpdate(manager, "INSERT INTO tblspace1.tsql(k1,n1) values(?,?)", Arrays.asList("mykey", Integer.valueOf(1234))).getUpdateCount());
        try (DataScanner scan = scan(manager, "SELECT ? as foo, k1, n1 FROM tblspace1.tsql", Arrays.asList("test"))) {
            List<DataAccessor> all = scan.consume();
            assertEquals(1, all.size());
            assertEquals(RawString.of("test"), all.get(0).get("foo"));
            assertEquals(RawString.of("mykey"), all.get(0).get("k1"));
            assertEquals(Integer.valueOf(1234), all.get(0).get("n1"));
        }
        Timestamp timestamp = new java.sql.Timestamp(System.currentTimeMillis());
        try (DataScanner scan = scan(manager, "SELECT ? as foo, ? as bar  FROM tblspace1.tsql", Arrays.asList(Long.valueOf(1), timestamp))) {
            List<DataAccessor> all = scan.consume();
            assertEquals(1, all.size());
            assertEquals(Long.valueOf(1), all.get(0).get("foo"));
            assertEquals(timestamp, all.get(0).get("bar"));
        }
        if (manager.isFullSQLSupportEnabled()) {
            try (DataScanner scan = scan(manager, "SELECT MAX(?) as foo, MIN(?) as bar  FROM tblspace1.tsql", Arrays.asList(Long.valueOf(1), timestamp))) {
                List<DataAccessor> all = scan.consume();
                assertEquals(1, all.size());
                assertEquals(Long.valueOf(1), all.get(0).get("foo"));
                assertEquals(timestamp, all.get(0).get("bar"));
            }
        }
        executeUpdate(manager, "DELETE FROM tblspace1.tsql", Collections.emptyList());
        try (DataScanner scan = scan(manager, "SELECT ? as foo, ? as bar  FROM tblspace1.tsql", Arrays.asList(Long.valueOf(1), timestamp))) {
            List<DataAccessor> all = scan.consume();
            assertEquals(0, all.size());
        }
        if (manager.isFullSQLSupportEnabled()) {
            try (DataScanner scan = scan(manager, "SELECT MAX(?) as foo, MIN(?) as bar  FROM tblspace1.tsql", Arrays.asList(Long.valueOf(1), timestamp))) {
                List<DataAccessor> all = scan.consume();
                assertEquals(1, all.size());
                assertNull(all.get(0).get("foo"));
                assertNull(all.get(0).get("bar"));
            }
        }
    }
}

Example 12 with DBManager

use of herddb.core.DBManager in project herddb by diennea.

the class RawSQLTest method basicTest.

@Test
public void basicTest() 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());
        assertEquals(1, executeUpdate(manager, "INSERT INTO tblspace1.tsql(k1,n1) values(?,?)", Arrays.asList("mykey", Integer.valueOf(1234))).getUpdateCount());
        {
            GetResult result = manager.get(new GetStatement("tblspace1", "tsql", Bytes.from_string("mykey"), null, false), StatementEvaluationContext.DEFAULT_EVALUATION_CONTEXT(), TransactionContext.NO_TRANSACTION);
            assertTrue(result.found());
            assertEquals(result.getRecord().key, Bytes.from_string("mykey"));
            Map<String, Object> finalRecord = result.getRecord().toBean(manager.getTableSpaceManager("tblspace1").getTableManager("tsql").getTable());
            assertEquals(RawString.of("mykey"), finalRecord.get("k1"));
            assertEquals(Integer.valueOf(1234), finalRecord.get("n1"));
        }
        {
            assertEquals(1, executeUpdate(manager, "UPDATE tblspace1.tsql set n1=? where k1 = ?", Arrays.asList(Integer.valueOf(999), "mykey")).getUpdateCount());
        }
        {
            assertEquals(1, executeUpdate(manager, "UPDATE tblspace1.tsql set n1=? where k1 = ? and n1 = ?", Arrays.asList(Integer.valueOf(100), "mykey", Integer.valueOf(999))).getUpdateCount());
        }
        {
            assertEquals(1, executeUpdate(manager, "UPDATE tblspace1.tsql set n1=? where k1 = ? and (n1 = ? or n1 <> ?)", Arrays.asList(Integer.valueOf(999), "mykey", Integer.valueOf(100), Integer.valueOf(1000))).getUpdateCount());
        }
        {
            assertEquals(1, executeUpdate(manager, "UPDATE tblspace1.tsql set n1=? where k1 = ? and (n1 <> ?)", Arrays.asList(Integer.valueOf(34), "mykey", Integer.valueOf(15))).getUpdateCount());
        }
        {
            assertEquals(1, executeUpdate(manager, "UPDATE tblspace1.tsql set n1=? where k1 = ? and not (n1 <> ?)", Arrays.asList(Integer.valueOf(999), "mykey", Integer.valueOf(34))).getUpdateCount());
        }
        {
            GetResult result = manager.get(new GetStatement("tblspace1", "tsql", Bytes.from_string("mykey"), null, false), StatementEvaluationContext.DEFAULT_EVALUATION_CONTEXT(), TransactionContext.NO_TRANSACTION);
            assertTrue(result.found());
            assertEquals(result.getRecord().key, Bytes.from_string("mykey"));
            Map<String, Object> finalRecord = result.getRecord().toBean(manager.getTableSpaceManager("tblspace1").getTableManager("tsql").getTable());
            assertEquals(RawString.of("mykey"), finalRecord.get("k1"));
            assertEquals(Integer.valueOf(999), finalRecord.get("n1"));
        }
        {
            TranslatedQuery translate1 = manager.getPlanner().translate(TableSpace.DEFAULT, "SELECT * FROM tblspace1.tsql where k1 = ?", Arrays.asList("mykey"), false, true, false, -1);
            GetStatement st_get = (GetStatement) translate1.plan.mainStatement;
            GetResult result = manager.get(st_get, translate1.context, TransactionContext.NO_TRANSACTION);
            assertTrue(result.found());
            assertEquals(result.getRecord().key, Bytes.from_string("mykey"));
            Map<String, Object> finalRecord = result.getRecord().toBean(manager.getTableSpaceManager("tblspace1").getTableManager("tsql").getTable());
            assertEquals(RawString.of("mykey"), finalRecord.get("k1"));
            assertEquals(Integer.valueOf(999), finalRecord.get("n1"));
        }
        {
            TranslatedQuery translate1 = manager.getPlanner().translate(TableSpace.DEFAULT, "SELECT * FROM tblspace1.tsql where k1 = ? and n1=?", Arrays.asList("mykey", 999), false, true, false, -1);
            GetStatement st_get_with_condition = (GetStatement) translate1.plan.mainStatement;
            GetResult result = manager.get(st_get_with_condition, translate1.context, TransactionContext.NO_TRANSACTION);
            assertTrue(result.found());
            assertEquals(result.getRecord().key, Bytes.from_string("mykey"));
            Map<String, Object> finalRecord = result.getRecord().toBean(manager.getTableSpaceManager("tblspace1").getTableManager("tsql").getTable());
            assertEquals(RawString.of("mykey"), finalRecord.get("k1"));
            assertEquals(Integer.valueOf(999), finalRecord.get("n1"));
        }
        {
            TranslatedQuery translate1 = manager.getPlanner().translate(TableSpace.DEFAULT, "SELECT * FROM tblspace1.tsql where k1 = ? and n1=?", Arrays.asList("mykey", 9992), false, true, false, -1);
            GetStatement st_get_with_wrong_condition = (GetStatement) translate1.plan.mainStatement;
            GetResult result = manager.get(st_get_with_wrong_condition, translate1.context, TransactionContext.NO_TRANSACTION);
            assertFalse(result.found());
        }
        {
            assertEquals(0, executeUpdate(manager, "DELETE FROM tblspace1.tsql where k1 = ? and n1 = ?", Arrays.asList("mykey", 123)).getUpdateCount());
        }
        {
            assertEquals(1, executeUpdate(manager, "DELETE FROM tblspace1.tsql where k1 = ?", Arrays.asList("mykey")).getUpdateCount());
        }
        {
            GetResult result = manager.get(new GetStatement("tblspace1", "tsql", Bytes.from_string("mykey"), null, false), StatementEvaluationContext.DEFAULT_EVALUATION_CONTEXT(), TransactionContext.NO_TRANSACTION);
            assertFalse(result.found());
        }
        {
            assertEquals(1, executeUpdate(manager, "INSERT INTO tblspace1.tsql(k1,n1) values(?,?)", Arrays.asList("mykey", Integer.valueOf(1234))).getUpdateCount());
        }
        {
            GetResult result = manager.get(new GetStatement("tblspace1", "tsql", Bytes.from_string("mykey"), null, false), StatementEvaluationContext.DEFAULT_EVALUATION_CONTEXT(), TransactionContext.NO_TRANSACTION);
            assertTrue(result.found());
        }
        {
            assertEquals(1, executeUpdate(manager, "DELETE FROM tblspace1.tsql where k1 = ? and n1=?", Arrays.asList("mykey", 1234)).getUpdateCount());
        }
        {
            GetResult result = manager.get(new GetStatement("tblspace1", "tsql", Bytes.from_string("mykey"), null, false), StatementEvaluationContext.DEFAULT_EVALUATION_CONTEXT(), TransactionContext.NO_TRANSACTION);
            assertFalse(result.found());
        }
        {
            assertEquals(1, executeUpdate(manager, "INSERT INTO tblspace1.tsql(k1,n1) values('mykey2',1234)", Collections.emptyList()).getUpdateCount());
        }
        {
            assertEquals(1, executeUpdate(manager, "UPDATE tblspace1.tsql set n1=2135 where k1 = 'mykey2'", Collections.emptyList()).getUpdateCount());
        }
        {
            assertEquals(1, executeUpdate(manager, "UPDATE tblspace1.tsql set n1=2138,s1='foo' where k1 = 'mykey2' and s1 is null", Collections.emptyList()).getUpdateCount());
        }
        {
            TranslatedQuery translate1 = manager.getPlanner().translate(TableSpace.DEFAULT, "SELECT * FROM tblspace1.tsql where k1 ='mykey2'", Collections.emptyList(), true, true, false, -1);
            ScanStatement scan = translate1.plan.mainStatement.unwrap(ScanStatement.class);
            try (DataScanner scan1 = manager.scan(scan, translate1.context, TransactionContext.NO_TRANSACTION)) {
                assertEquals(1, scan1.consume().size());
            }
        }
        {
            TranslatedQuery translate1 = manager.getPlanner().translate(TableSpace.DEFAULT, "SELECT k1 FROM tblspace1.tsql where k1 ='mykey2'", Collections.emptyList(), true, true, false, -1);
            try (DataScanner scan1 = ((ScanResult) manager.executePlan(translate1.plan, translate1.context, TransactionContext.NO_TRANSACTION)).dataScanner) {
                List<DataAccessor> records = scan1.consume();
                assertEquals(1, records.size());
                System.out.println("records:" + records);
                assertEquals(1, records.get(0).getFieldNames().length);
                assertEquals(1, records.get(0).toMap().size());
                assertEquals("k1", records.get(0).getFieldNames()[0]);
                assertEquals(RawString.of("mykey2"), records.get(0).get(0));
            }
        }
        {
            TranslatedQuery translate1 = manager.getPlanner().translate(TableSpace.DEFAULT, "SELECT k1 theKey FROM tblspace1.tsql where k1 ='mykey2'", Collections.emptyList(), true, true, false, -1);
            try (DataScanner scan1 = ((ScanResult) manager.executePlan(translate1.plan, translate1.context, TransactionContext.NO_TRANSACTION)).dataScanner) {
                List<DataAccessor> records = scan1.consume();
                assertEquals(1, records.size());
                assertEquals(1, records.get(0).getFieldNames().length);
                assertEquals(1, records.get(0).toMap().size());
                assertEquals("thekey", records.get(0).getFieldNames()[0].toLowerCase());
                System.out.println("type: " + records.get(0).getClass());
                assertEquals(RawString.of("mykey2"), records.get(0).get(0));
            }
        }
        {
            TranslatedQuery translate1 = manager.getPlanner().translate(TableSpace.DEFAULT, "SELECT k1 as theKey,'one' as theStringConstant,3  LongConstant FROM tblspace1.tsql where k1 ='mykey2'", Collections.emptyList(), true, true, false, -1);
            try (DataScanner scan1 = ((ScanResult) manager.executePlan(translate1.plan, translate1.context, TransactionContext.NO_TRANSACTION)).dataScanner) {
                List<DataAccessor> records = scan1.consume();
                assertEquals(1, records.size());
                assertEquals(3, records.get(0).getFieldNames().length);
                assertEquals(3, records.get(0).toMap().size());
                assertEquals("thekey", records.get(0).getFieldNames()[0].toLowerCase());
                assertEquals(RawString.of("mykey2"), records.get(0).get(0));
                assertEquals("thestringconstant", records.get(0).getFieldNames()[1].toLowerCase());
                assertEquals(RawString.of("one"), records.get(0).get(1));
                assertEquals("longconstant", records.get(0).getFieldNames()[2].toLowerCase());
                assertEquals(3, ((Number) records.get(0).get(2)).longValue());
            }
        }
        {
            TranslatedQuery translate1 = manager.getPlanner().translate(TableSpace.DEFAULT, "SELECT * FROM tblspace1.tsql where k1 ='mykey2' and s1 is not null", Collections.emptyList(), true, true, false, -1);
            ScanStatement scan = translate1.plan.mainStatement.unwrap(ScanStatement.class);
            try (DataScanner scan1 = manager.scan(scan, translate1.context, TransactionContext.NO_TRANSACTION)) {
                assertEquals(1, scan1.consume().size());
            }
        }
        {
            assertEquals(1, executeUpdate(manager, "UPDATE tblspace1.tsql set n1=2138,s1='bar' where k1 = 'mykey2' and s1 is not null", Collections.emptyList()).getUpdateCount());
        }
        {
            assertEquals(0, executeUpdate(manager, "UPDATE tblspace1.tsql set n1=2138,s1='bar' where k1 = 'mykey2' and s1 is null", Collections.emptyList()).getUpdateCount());
        }
        {
            assertEquals(0, executeUpdate(manager, "UPDATE tblspace1.tsql set n1=2138,s1='bar' where k1 = 'mykey2' and not (s1 is not null)", Collections.emptyList()).getUpdateCount());
        }
        {
            assertEquals(1, executeUpdate(manager, "UPDATE tblspace1.tsql set n1=2138,s1='bar' where k1 = 'mykey2' and not (s1 is null)", Collections.emptyList()).getUpdateCount());
        }
        {
            assertEquals(1, executeUpdate(manager, "DELETE FROM  tblspace1.tsql where k1 = 'mykey2' and s1 is not null", Collections.emptyList()).getUpdateCount());
            assertEquals(0, executeUpdate(manager, "DELETE FROM  tblspace1.tsql where k1 = 'mykey2' and s1 is not null", Collections.emptyList()).getUpdateCount());
        }
        {
            GetResult result = manager.get(new GetStatement("tblspace1", "tsql", Bytes.from_string("mykey2"), null, false), StatementEvaluationContext.DEFAULT_EVALUATION_CONTEXT(), TransactionContext.NO_TRANSACTION);
            assertFalse(result.found());
        }
        {
            TransactionResult result = (TransactionResult) execute(manager, "EXECUTE BEGINTRANSACTION 'tblspace1'", Collections.emptyList());
            long tx = result.getTransactionId();
            execute(manager, "EXECUTE COMMITTRANSACTION 'tblspace1'," + tx, Collections.emptyList());
        }
        {
            TransactionResult result = (TransactionResult) execute(manager, "EXECUTE BEGINTRANSACTION 'tblspace1'", Collections.emptyList());
            long tx = result.getTransactionId();
            execute(manager, "EXECUTE ROLLBACKTRANSACTION 'tblspace1'," + tx, Collections.emptyList());
        }
    }
}

Example 13 with DBManager

use of herddb.core.DBManager in project herddb by diennea.

the class SimpleJoinTest method testExists.

@Test
public void testExists() throws Exception {
    String nodeId = "localhost";
    Path dataPath = folder.newFolder("data").toPath();
    Path logsPath = folder.newFolder("logs").toPath();
    Path metadataPath = folder.newFolder("metadata").toPath();
    Path tmoDir = folder.newFolder("tmoDir").toPath();
    // reproduces an error due to the order or elements returned during the scan
    try (DBManager manager = new DBManager(nodeId, new FileMetadataStorageManager(metadataPath), new FileDataStorageManager(dataPath), new FileCommitLogManager(logsPath), tmoDir, null)) {
        assumeThat(manager.getPlanner(), instanceOf(CalcitePlanner.class));
        manager.start();
        manager.waitForTablespace(TableSpace.DEFAULT, 10000);
        execute(manager, "CREATE TABLE a (k1 int primary key, v1 int)", Collections.emptyList());
        execute(manager, "CREATE TABLE b (k2 int primary key, v2 int)", Collections.emptyList());
        execute(manager, "INSERT INTO a (k1,v1) values(1,1)", Collections.emptyList());
        execute(manager, "INSERT INTO b (k2,v2) values(1,1)", Collections.emptyList());
        {
            try (DataScanner scan1 = scan(manager, " select k1 as kk, v1 as vv from a where 1=1 and ( NOT EXISTS (SELECT NULL FROM b WHERE b.v2=a.k1 AND  1=1  AND b.k2 = 2 ))", Collections.emptyList())) {
                List<DataAccessor> consume = scan1.consume();
                System.out.println("NUM " + consume.size());
                for (DataAccessor r : consume) {
                    System.out.println("RECORD " + r.toMap());
                }
                assertEquals(1, consume.size());
            }
        }
        {
            try (DataScanner scan1 = scan(manager, " select k1 as kk, v1 as vv from a where 1=1 and ( NOT EXISTS (SELECT NULL FROM b WHERE b.v2=a.k1 AND  1=1  AND b.k2 = 1 ))", Collections.emptyList())) {
                List<DataAccessor> consume = scan1.consume();
                System.out.println("NUM " + consume.size());
                for (DataAccessor r : consume) {
                    System.out.println("RECORD " + r.toMap());
                }
                assertEquals(0, consume.size());
            }
        }
        // joins during transactions must release transaction resources properly
        {
            long tx = TestUtils.beginTransaction(manager, TableSpace.DEFAULT);
            try (DataScanner scan1 = scan(manager, " select k1 as kk, v1 as vv from a where 1=1 and ( NOT EXISTS (SELECT NULL FROM b WHERE b.v2=a.k1 AND  1=1  AND b.k2 = 2 ))", Collections.emptyList(), new TransactionContext(tx))) {
                List<DataAccessor> consume = scan1.consume();
                System.out.println("NUM " + consume.size());
                for (DataAccessor r : consume) {
                    System.out.println("RECORD " + r.toMap());
                }
                assertEquals(1, consume.size());
            }
            TestUtils.commitTransaction(manager, TableSpace.DEFAULT, tx);
        }
        {
            long tx = TestUtils.beginTransaction(manager, TableSpace.DEFAULT);
            try (DataScanner scan1 = scan(manager, " select k1 as kk, v1 as vv from a where 1=1 and ( EXISTS (SELECT 1 FROM b WHERE b.v2=a.k1 AND b.k2 = 1 ))", Collections.emptyList(), new TransactionContext(tx))) {
                List<DataAccessor> consume = scan1.consume();
                System.out.println("NUM " + consume.size());
                for (DataAccessor r : consume) {
                    System.out.println("RECORD " + r.toMap());
                }
                assertEquals(1, consume.size());
            }
            TestUtils.commitTransaction(manager, TableSpace.DEFAULT, tx);
        }
    }
}

Example 14 with DBManager

use of herddb.core.DBManager in project herddb by diennea.

the class SimpleJoinTest method testStartTransactionInJoinBranch.

@Test
public void testStartTransactionInJoinBranch() 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());
        {
            TranslatedQuery translated = manager.getPlanner().translate(TableSpace.DEFAULT, "SELECT * FROM" + " tblspace1.table1 t1" + " JOIN tblspace1.table2 t2" + " ON t1.n1 > 0" + "   and t2.n2 >= 1", Collections.emptyList(), true, true, false, -1);
            translated.context.setForceRetainReadLock(true);
            if (manager.getPlanner() instanceof CalcitePlanner) {
                assertThat(translated.plan.originalRoot, instanceOf(NestedLoopJoinOp.class));
                NestedLoopJoinOp join = (NestedLoopJoinOp) translated.plan.originalRoot;
                assertThat(join.getLeft(), instanceOf(SimpleScanOp.class));
                assertThat(join.getRight(), instanceOf(SimpleScanOp.class));
            }
            // we want that the left branch of the join starts the transactoion
            ScanResult scanResult = ((ScanResult) manager.executePlan(translated.plan, translated.context, TransactionContext.AUTOTRANSACTION_TRANSACTION));
            List<DataAccessor> tuples = scanResult.dataScanner.consumeAndClose();
            assertTrue(scanResult.transactionId > 0);
            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());
            TestUtils.commitTransaction(manager, "tblspace1", scanResult.transactionId);
        }
        {
            TranslatedQuery translated = manager.getPlanner().translate(TableSpace.DEFAULT, "SELECT * FROM" + " tblspace1.table1 t1" + " JOIN tblspace1.table2 t2" + " ON t1.n1 > 0" + "   and t2.n2 >= 1" + "   order by t1.n1, t2.n2", Collections.emptyList(), true, true, false, // with order by
            -1);
            translated.context.setForceRetainReadLock(true);
            assertThat(translated.plan.originalRoot, instanceOf(SortOp.class));
            if (manager.getPlanner() instanceof CalcitePlanner) {
                NestedLoopJoinOp join = (NestedLoopJoinOp) ((SortOp) translated.plan.originalRoot).getInput();
                assertThat(join.getLeft(), instanceOf(SimpleScanOp.class));
                assertThat(join.getRight(), instanceOf(SimpleScanOp.class));
            } else {
                JoinOp join = (JoinOp) ((SortOp) translated.plan.originalRoot).getInput();
                assertThat(join.getLeft(), instanceOf(SimpleScanOp.class));
                assertThat(join.getRight(), instanceOf(SimpleScanOp.class));
            }
            // we want that the left branch of the join starts the transactoion
            ScanResult scanResult = ((ScanResult) manager.executePlan(translated.plan, translated.context, TransactionContext.AUTOTRANSACTION_TRANSACTION));
            List<DataAccessor> tuples = scanResult.dataScanner.consumeAndClose();
            assertTrue(scanResult.transactionId > 0);
            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());
            TestUtils.commitTransaction(manager, "tblspace1", scanResult.transactionId);
        }
        {
            TranslatedQuery translated = manager.getPlanner().translate(TableSpace.DEFAULT, "SELECT t1.s1 FROM" + " tblspace1.table1 t1" + " JOIN tblspace1.table2 t2" + " ON t1.n1 > 0" + "   and t2.n2 >= 1" + "   order by t1.n1, t2.n2", Collections.emptyList(), true, true, false, // with order by on non selected fields
            -1);
            translated.context.setForceRetainReadLock(true);
            assertThat(translated.plan.originalRoot, instanceOf(ProjectOp.class));
            SortOp sort = (SortOp) ((ProjectOp) translated.plan.originalRoot).getInput();
            assertThat(((ProjectOp) translated.plan.originalRoot).getProjection(), instanceOf(ProjectOp.ZeroCopyProjection.class));
            if (manager.getPlanner() instanceof CalcitePlanner) {
                NestedLoopJoinOp join = (NestedLoopJoinOp) sort.getInput();
                assertThat(join.getLeft(), instanceOf(SimpleScanOp.class));
                assertThat(join.getRight(), instanceOf(SimpleScanOp.class));
            } else {
                JoinOp join = (JoinOp) sort.getInput();
                assertThat(join.getLeft(), instanceOf(SimpleScanOp.class));
                assertThat(join.getRight(), instanceOf(SimpleScanOp.class));
            }
            // we want that the left branch of the join starts the transactoion
            ScanResult scanResult = ((ScanResult) manager.executePlan(translated.plan, translated.context, TransactionContext.AUTOTRANSACTION_TRANSACTION));
            List<DataAccessor> tuples = scanResult.dataScanner.consumeAndClose();
            assertTrue(scanResult.transactionId > 0);
            for (DataAccessor t : tuples) {
                System.out.println("t:" + t);
                assertEquals(1, t.getFieldNames().length);
                assertEquals("s1", t.getFieldNames()[0]);
            }
            assertEquals(4, tuples.size());
            TestUtils.commitTransaction(manager, "tblspace1", scanResult.transactionId);
        }
    }
}

Example 15 with DBManager

use of herddb.core.DBManager in project herddb by diennea.

the class SimpleJoinTest method testJoinUnsortedKey.

@Test
public void testJoinUnsortedKey() throws Exception {
    String nodeId = "localhost";
    Path dataPath = folder.newFolder("data").toPath();
    Path logsPath = folder.newFolder("logs").toPath();
    Path metadataPath = folder.newFolder("metadata").toPath();
    Path tmoDir = folder.newFolder("tmoDir").toPath();
    // reproduces an error due to the order or elements returned during the scan
    try (DBManager manager = new DBManager(nodeId, new FileMetadataStorageManager(metadataPath), new FileDataStorageManager(dataPath), new FileCommitLogManager(logsPath), tmoDir, null)) {
        manager.start();
        manager.waitForTablespace(TableSpace.DEFAULT, 10000);
        execute(manager, "CREATE TABLE customer (customer_id long primary key,contact_email string,contact_person string, creation long, deleted boolean, modification long, name string, vetting boolean)", Collections.emptyList());
        execute(manager, "CREATE TABLE license (license_id long primary key,application string, creation long,data string, deleted boolean, modification long,signature string, customer_id long , license_key_id string)", Collections.emptyList());
        execute(manager, "INSERT INTO license (license_id,customer_id) values(1,1)", Collections.emptyList());
        execute(manager, "INSERT INTO license (license_id,customer_id) values(2,1)", Collections.emptyList());
        execute(manager, "INSERT INTO license (license_id,customer_id) values(3,3)", Collections.emptyList());
        execute(manager, "INSERT INTO license (license_id,customer_id) values(4,4)", Collections.emptyList());
        execute(manager, "INSERT INTO license (license_id,customer_id) values(5,3)", Collections.emptyList());
        execute(manager, "INSERT INTO license (license_id,customer_id) values(6,3)", Collections.emptyList());
        execute(manager, "INSERT INTO license (license_id,customer_id) values(7,3)", Collections.emptyList());
        execute(manager, "INSERT INTO license (license_id,customer_id) values(8,3)", Collections.emptyList());
        execute(manager, "INSERT INTO license (license_id,customer_id) values(9,3)", Collections.emptyList());
        execute(manager, "INSERT INTO license (license_id,customer_id) values(10,3)", Collections.emptyList());
        execute(manager, "INSERT INTO license (license_id,customer_id) values(11,3)", Collections.emptyList());
        execute(manager, "INSERT INTO license (license_id,customer_id) values(12,5)", Collections.emptyList());
        execute(manager, "INSERT INTO license (license_id,customer_id) values(13,6)", Collections.emptyList());
        execute(manager, "INSERT INTO license (license_id,customer_id) values(14,7)", Collections.emptyList());
        execute(manager, "INSERT INTO license (license_id,customer_id) values(15,3)", Collections.emptyList());
        execute(manager, "INSERT INTO customer (customer_id) values(1)", Collections.emptyList());
        execute(manager, "INSERT INTO customer (customer_id) values(2)", Collections.emptyList());
        execute(manager, "INSERT INTO customer (customer_id) values(3)", Collections.emptyList());
        execute(manager, "INSERT INTO customer (customer_id) values(4)", Collections.emptyList());
        execute(manager, "INSERT INTO customer (customer_id) values(5)", Collections.emptyList());
        execute(manager, "INSERT INTO customer (customer_id) values(6)", Collections.emptyList());
        execute(manager, "INSERT INTO customer (customer_id) values(7)", Collections.emptyList());
        {
            try (DataScanner scan1 = scan(manager, "SELECT t0.license_id,c.customer_id FROM license t0, customer c WHERE c.customer_id = 3 AND t0.customer_id = 3 AND c.customer_id = t0.customer_id", Collections.emptyList())) {
                List<DataAccessor> consume = scan1.consumeAndClose();
                System.out.println("NUM " + consume.size());
                assertEquals(9, consume.size());
                for (DataAccessor r : consume) {
                    System.out.println("RECORD " + r.toMap());
                }
            }
        }
        // joins during transactions must release transaction resources properly
        {
            long tx = TestUtils.beginTransaction(manager, TableSpace.DEFAULT);
            try (DataScanner scan1 = scan(manager, "SELECT t0.license_id,c.customer_id FROM license t0, customer c WHERE c.customer_id = 3 AND t0.customer_id = 3 AND c.customer_id = t0.customer_id", Collections.emptyList(), new TransactionContext(tx))) {
                List<DataAccessor> consume = scan1.consume();
                System.out.println("NUM " + consume.size());
                assertEquals(9, consume.size());
                for (DataAccessor r : consume) {
                    System.out.println("RECORD " + r.toMap());
                }
            }
            TestUtils.commitTransaction(manager, TableSpace.DEFAULT, tx);
        }
    }
}