Examples with DataAccessor herddb.utils.DataAccessor used on opensource projects

Example 51 with DataAccessor

use of herddb.utils.DataAccessor 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()).consumeAndClose();
            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()).consumeAndClose();
            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)));
        }
        execute(manager, "INSERT INTO tblspace1.table2 (k2,n2,s2) values('a',1,'A')", Collections.emptyList());
        {
            List<DataAccessor> tuples = scan(manager, "SELECT * FROM tblspace1.table1 t1, tblspace1.table2 t2 WHERE t1.k1=? and t2.k2=? and t1.k1=t2.k2", Arrays.asList("a", "a")).consumeAndClose();
            for (DataAccessor t : tuples) {
                System.out.println("t:" + t);
                assertEquals(6, t.getFieldNames().length);
            }
            assertEquals(1, tuples.size());
        }
    }
}

Example 52 with DataAccessor

use of herddb.utils.DataAccessor 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 53 with DataAccessor

use of herddb.utils.DataAccessor 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 54 with DataAccessor

use of herddb.utils.DataAccessor 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);
        }
    }
}

Example 55 with DataAccessor

use of herddb.utils.DataAccessor in project herddb by diennea.

the class AlterTablespaceSQLTest method createAlterTableSpace.

@Test
public void createAlterTableSpace() throws Exception {
    String nodeId = "localhost";
    try (DBManager manager = new DBManager(nodeId, new MemoryMetadataStorageManager(), new MemoryDataStorageManager(), new MemoryCommitLogManager(), null, null)) {
        manager.start();
        assertTrue(manager.waitForTablespace(TableSpace.DEFAULT, 10000));
        execute(manager, "EXECUTE CREATETABLESPACE 'ttt'", Collections.emptyList());
        execute(manager, "CREATE TABLESPACE 'ttt2','leader:" + nodeId + "'", Collections.emptyList());
        try {
            execute(manager, "EXECUTE CREATETABLESPACE 'ttt2','leader:othernode'", Collections.emptyList());
            fail();
        } catch (TableSpaceAlreadyExistsException err) {
        }
        execute(manager, "EXECUTE CREATETABLESPACE 'ttt3','leader:othernode'", Collections.emptyList());
        execute(manager, "EXECUTE CREATETABLESPACE 'ttt4','leader:othernode','replica:" + nodeId + ",othernode'", Collections.emptyList());
        execute(manager, "EXECUTE ALTERTABLESPACE 'ttt3','replica:" + nodeId + ",othernode','expectedReplicaCount:2'", Collections.emptyList());
        execute(manager, "EXECUTE ALTERTABLESPACE 'ttt3','leader:othernode'", Collections.emptyList());
        execute(manager, "EXECUTE ALTERTABLESPACE 'ttt3','expectedReplicaCount:12'", Collections.emptyList());
        TableSpace ttt3 = manager.getMetadataStorageManager().describeTableSpace("ttt3");
        assertEquals("othernode", ttt3.leaderId);
        assertEquals(12, ttt3.expectedReplicaCount);
        assertTrue(ttt3.replicas.contains("othernode"));
        assertTrue(ttt3.replicas.contains(nodeId));
        try (DataScanner scan = scan(manager, "SELECT * FROM SYSTABLESPACES", Collections.emptyList())) {
            List<DataAccessor> tuples = scan.consume();
            assertEquals(5, tuples.size());
            for (DataAccessor t : tuples) {
                System.out.println("tablespace: " + t.toMap());
                assertNotNull(t.get("expectedreplicacount"));
                assertNotNull(t.get("tablespace_name"));
                assertNotNull(t.get("replica"));
                assertNotNull(t.get("leader"));
            }
        }
        try (DataScanner scan = scan(manager, "SELECT expectedreplicacount FROM SYSTABLESPACES where tablespace_name='ttt3'", Collections.emptyList())) {
            List<DataAccessor> tuples = scan.consume();
            assertEquals(1, tuples.size());
            for (DataAccessor t : tuples) {
                System.out.println("tablespace: " + t.toMap());
                assertEquals(12, t.get("expectedreplicacount"));
            }
        }
    }
}