Examples with DataScanner herddb.model.DataScanner used on opensource projects

Example 91 with DataScanner

use of herddb.model.DataScanner in project herddb by diennea.

the class UpdateTest method simpleManualSequence.

@Test
public void simpleManualSequence() 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.myseq (k1 int, k2 int, current_value int, primary key(k1, k2))", Collections.emptyList());
        DMLStatementExecutionResult insertSequence1 = executeUpdate(manager, "INSERT INTO tblspace1.myseq(k1,k2,current_value) values(?,?,?)", Arrays.asList(Integer.valueOf(78), Integer.valueOf(0), Integer.valueOf(1)), TransactionContext.AUTOTRANSACTION_TRANSACTION);
        assertTrue(insertSequence1.transactionId > 0);
        try (DataScanner scan = scan(manager, "SELECT current_value from tblspace1.myseq where k1=?", Arrays.asList(78), new TransactionContext(insertSequence1.transactionId))) {
            List<DataAccessor> recordSet = scan.consumeAndClose();
            assertEquals(1, recordSet.size());
            assertEquals(1, recordSet.get(0).get(0));
        }
        // set current_value = 2 if current_value = 1
        DMLStatementExecutionResult updateSequence1 = executeUpdate(manager, "UPDATE tblspace1.myseq set current_value=? where k1=78 and k2=0 and current_value=?", Arrays.asList(Integer.valueOf(2), Integer.valueOf(1)), new TransactionContext(insertSequence1.transactionId));
        assertEquals(insertSequence1.transactionId, updateSequence1.transactionId);
        assertEquals(1, updateSequence1.getUpdateCount());
        try (DataScanner scan = scan(manager, "SELECT current_value from tblspace1.myseq where k1=?", Arrays.asList(78), new TransactionContext(updateSequence1.transactionId))) {
            List<DataAccessor> recordSet = scan.consumeAndClose();
            assertEquals(1, recordSet.size());
            assertEquals(2, recordSet.get(0).get(0));
        }
        DMLStatementExecutionResult updateSequence2 = executeUpdate(manager, "UPDATE tblspace1.myseq set current_value=? where k1=78 and k2=0 and current_value=?", Arrays.asList(Integer.valueOf(3), Integer.valueOf(2)), new TransactionContext(updateSequence1.transactionId));
        assertEquals(updateSequence2.transactionId, updateSequence1.transactionId);
        assertEquals(1, updateSequence2.getUpdateCount());
        try (DataScanner scan = scan(manager, "SELECT current_value from tblspace1.myseq where k1=?", Arrays.asList(78), new TransactionContext(updateSequence1.transactionId))) {
            List<DataAccessor> recordSet = scan.consumeAndClose();
            assertEquals(1, recordSet.size());
            assertEquals(3, recordSet.get(0).get(0));
        }
        DMLStatementExecutionResult deleteSequence2 = executeUpdate(manager, "DELETE FROM tblspace1.myseq where k1=78 and k2=0 ", Arrays.asList(), new TransactionContext(updateSequence1.transactionId));
        assertEquals(deleteSequence2.transactionId, updateSequence1.transactionId);
        assertEquals(1, deleteSequence2.getUpdateCount());
        try (DataScanner scan = scan(manager, "SELECT current_value from tblspace1.myseq where k1=?", Arrays.asList(78), new TransactionContext(updateSequence1.transactionId))) {
            List<DataAccessor> recordSet = scan.consumeAndClose();
            assertEquals(0, recordSet.size());
        }
    }
}

Example 92 with DataScanner

use of herddb.model.DataScanner in project herddb by diennea.

the class UpdateTest method upsertTest.

@Test
public void upsertTest() 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());
        try (DataScanner scan = scan(manager, "SELECT n1 from tblspace1.tsql where k1=?", Arrays.asList("mykey"))) {
            List<DataAccessor> recordSet = scan.consumeAndClose();
            assertEquals(1, recordSet.size());
            assertEquals(1234, recordSet.get(0).get(0));
        }
        assertEquals(1, executeUpdate(manager, "UPSERT INTO tblspace1.tsql(k1,n1) values(?,?)", Arrays.asList("mykey", Integer.valueOf(1235))).getUpdateCount());
        try (DataScanner scan = scan(manager, "SELECT n1 from tblspace1.tsql where k1=?", Arrays.asList("mykey"))) {
            List<DataAccessor> recordSet = scan.consumeAndClose();
            assertEquals(1, recordSet.size());
            assertEquals(1235, recordSet.get(0).get(0));
        }
        assertEquals(4, executeUpdate(manager, "UPSERT INTO tblspace1.tsql(k1,n1)" + "values(?,?),(?,?),(?,?),(?,?)", Arrays.asList("mykey", Integer.valueOf(1235), "mykey", Integer.valueOf(1236), "mykey", Integer.valueOf(1237), "mykey", Integer.valueOf(1238))).getUpdateCount());
        try (DataScanner scan = scan(manager, "SELECT n1 from tblspace1.tsql where k1=?", Arrays.asList("mykey"))) {
            List<DataAccessor> recordSet = scan.consumeAndClose();
            assertEquals(1, recordSet.size());
            assertEquals(1238, recordSet.get(0).get(0));
        }
        assertEquals(1, executeUpdate(manager, "DELETE FROM tblspace1.tsql", Collections.emptyList()).getUpdateCount());
        execute(manager, "ALTER TABLE tblspace1.tsql MODIFY s1 string not null", Collections.emptyList());
        // assert that UPSERT fails
        StatementExecutionException error = herddb.utils.TestUtils.expectThrows(StatementExecutionException.class, () -> {
            executeUpdate(manager, "UPSERT INTO tblspace1.tsql(k1,n1) values(?,?)", Arrays.asList("mykey", Integer.valueOf(1235)));
        });
        assertThat(error.getMessage(), containsString("Column 's1' has no default value and does not allow NULLs"));
        // insert a value, n1 has a value
        assertEquals(1, executeUpdate(manager, "UPSERT INTO tblspace1.tsql(k1,n1,s1) values(?,?,'non-empty')", Arrays.asList("mykey", Integer.valueOf(1235))).getUpdateCount());
        try (DataScanner scan = scan(manager, "SELECT n1 from tblspace1.tsql where k1=?", Arrays.asList("mykey"))) {
            List<DataAccessor> recordSet = scan.consumeAndClose();
            assertEquals(1, recordSet.size());
            assertEquals(1235, recordSet.get(0).get(0));
        }
        // upsert, making n1 null now, because it has not been named in the INSERT clause
        // use non uppercase casing in UPSERT keyword
        assertEquals(1, executeUpdate(manager, "UPsert INTO tblspace1.tsql(k1,s1) values(?,'non-empty')", Arrays.asList("mykey")).getUpdateCount());
        try (DataScanner scan = scan(manager, "SELECT n1 from tblspace1.tsql where k1=?", Arrays.asList("mykey"))) {
            List<DataAccessor> recordSet = scan.consumeAndClose();
            assertEquals(1, recordSet.size());
            assertNull(recordSet.get(0).get(0));
        }
    }
}

Example 93 with DataScanner

use of herddb.model.DataScanner in project herddb by diennea.

the class UpdateTest method updatePrimaryKeyTest.

@Test
public void updatePrimaryKeyTest() throws Exception {
    String nodeId = "localhost";
    try (DBManager manager = new DBManager("localhost", new MemoryMetadataStorageManager(), new MemoryDataStorageManager(), new MemoryCommitLogManager(), null, null)) {
        assumeTrue(manager.isFullSQLSupportEnabled());
        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 int not null primary key, k2 int)", Collections.emptyList());
        executeUpdate(manager, "INSERT INTO tblspace1.tsql(k1,k2) values(?,?)", Arrays.asList(Integer.valueOf(78), Integer.valueOf(99)), TransactionContext.NO_TRANSACTION);
        try (DataScanner scan = scan(manager, "SELECT * from tblspace1.tsql where k1=?", Arrays.asList(78), TransactionContext.NO_TRANSACTION)) {
            List<DataAccessor> recordSet = scan.consume();
            assertEquals(1, recordSet.size());
        }
        executeUpdate(manager, "UPDATE tblspace1.tsql set k1=? where k1=?", Arrays.asList(Integer.valueOf(72), Integer.valueOf(78)), TransactionContext.NO_TRANSACTION);
        try (DataScanner scan = scan(manager, "SELECT * from tblspace1.tsql where k1=?", Arrays.asList(78), TransactionContext.NO_TRANSACTION)) {
            List<DataAccessor> recordSet = scan.consume();
            assertEquals(0, recordSet.size());
        }
        try (DataScanner scan = scan(manager, "SELECT * from tblspace1.tsql where k1=?", Arrays.asList(72), TransactionContext.NO_TRANSACTION)) {
            List<DataAccessor> recordSet = scan.consume();
            assertEquals(1, recordSet.size());
        }
        try (DataScanner scan = scan(manager, "SELECT * from tblspace1.tsql where k1=? and k2=?", Arrays.asList(72, 99), TransactionContext.NO_TRANSACTION)) {
            List<DataAccessor> recordSet = scan.consume();
            assertEquals(1, recordSet.size());
        }
        dump(manager, "SELECT * from tblspace1.tsql", null, TransactionContext.NO_TRANSACTION);
        // complicate the problem, use the old value to build the new one
        executeUpdate(manager, "UPDATE tblspace1.tsql set k1=k1 * 2 + k2 where k2 >= ?", Arrays.asList(Integer.valueOf(99)), TransactionContext.NO_TRANSACTION);
        dump(manager, "SELECT * from tblspace1.tsql", null, TransactionContext.NO_TRANSACTION);
        try (DataScanner scan = scan(manager, "SELECT * from tblspace1.tsql where k1=? and k2=?", Arrays.asList(243, 99), TransactionContext.NO_TRANSACTION)) {
            List<DataAccessor> recordSet = scan.consume();
            assertEquals(1, recordSet.size());
        }
        // update other values not in pk, using old pk value
        executeUpdate(manager, "UPDATE tblspace1.tsql set k1=k1 - 10, k2=k1 * 2", Collections.emptyList(), TransactionContext.NO_TRANSACTION);
        dump(manager, "SELECT * from tblspace1.tsql", null, TransactionContext.NO_TRANSACTION);
        try (DataScanner scan = scan(manager, "SELECT * from tblspace1.tsql where k1=? and k2=?", Arrays.asList(233, 486), TransactionContext.NO_TRANSACTION)) {
            List<DataAccessor> recordSet = scan.consume();
            assertEquals(1, recordSet.size());
        }
    }
}

Example 94 with DataScanner

use of herddb.model.DataScanner in project herddb by diennea.

the class RawSQLTest method simpleComparatorTest.

@Test
public void simpleComparatorTest() 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,s1) values(?,?,?)", Arrays.asList("mykey", Integer.valueOf(1), "a")).getUpdateCount());
        assertEquals(1, executeUpdate(manager, "INSERT INTO tblspace1.tsql(k1,n1,s1) values(?,?,?)", Arrays.asList("mykey2", Integer.valueOf(2), "a")).getUpdateCount());
        assertEquals(1, executeUpdate(manager, "INSERT INTO tblspace1.tsql(k1,n1,s1) values(?,?,?)", Arrays.asList("mykey3", Integer.valueOf(3), "a")).getUpdateCount());
        assertEquals(1, executeUpdate(manager, "INSERT INTO tblspace1.tsql(k1,n1,s1) values(?,?,?)", Arrays.asList("mykey4", Integer.valueOf(-1), "a")).getUpdateCount());
        try (DataScanner scan1 = scan(manager, "SELECT * FROM tblspace1.tsql WHERE n1=1", Collections.emptyList())) {
            assertEquals(1, scan1.consume().size());
        }
        try (DataScanner scan1 = scan(manager, "SELECT * FROM tblspace1.tsql WHERE n1>1", Collections.emptyList())) {
            assertEquals(2, scan1.consume().size());
        }
        try (DataScanner scan1 = scan(manager, "SELECT * FROM tblspace1.tsql WHERE n1>=1", Collections.emptyList())) {
            assertEquals(3, scan1.consume().size());
        }
        try (DataScanner scan1 = scan(manager, "SELECT * FROM tblspace1.tsql WHERE n1<1", Collections.emptyList())) {
            assertEquals(1, scan1.consume().size());
        }
        try (DataScanner scan1 = scan(manager, "SELECT * FROM tblspace1.tsql WHERE n1<-7", Collections.emptyList())) {
            assertEquals(0, scan1.consume().size());
        }
        try (DataScanner scan1 = scan(manager, "SELECT * FROM tblspace1.tsql WHERE n1<=-1", Collections.emptyList())) {
            assertEquals(1, scan1.consume().size());
        }
        try (DataScanner scan1 = scan(manager, "SELECT * FROM tblspace1.tsql WHERE n1<>1", Collections.emptyList())) {
            assertEquals(3, scan1.consume().size());
        }
    }
}

Example 95 with DataScanner

use of herddb.model.DataScanner in project herddb by diennea.

the class RawSQLTest method orderByAlias2Test.

@Test
public void orderByAlias2Test() 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(1))).getUpdateCount());
        assertEquals(1, executeUpdate(manager, "INSERT INTO tblspace1.tsql(k1,n1) values(?,?)", Arrays.asList("mykey2", Integer.valueOf(2))).getUpdateCount());
        assertEquals(1, executeUpdate(manager, "INSERT INTO tblspace1.tsql(k1,n1) values(?,?)", Arrays.asList("mykey3", Integer.valueOf(3))).getUpdateCount());
        try (DataScanner scan1 = scan(manager, "SELECT * FROM tblspace1.tsql as tt ORDER BY tt.n1", Collections.emptyList())) {
            List<DataAccessor> result = scan1.consume();
            assertEquals(3, result.size());
            assertEquals(RawString.of("mykey"), result.get(0).get("k1"));
            assertEquals(RawString.of("mykey2"), result.get(1).get("k1"));
            assertEquals(RawString.of("mykey3"), result.get(2).get("k1"));
        }
        try (DataScanner scan1 = scan(manager, "SELECT * FROM tblspace1.tsql as tt ORDER BY tt.n1 desc", Collections.emptyList())) {
            List<DataAccessor> result = scan1.consume();
            assertEquals(3, result.size());
            assertEquals(RawString.of("mykey3"), result.get(0).get("k1"));
            assertEquals(RawString.of("mykey2"), result.get(1).get("k1"));
            assertEquals(RawString.of("mykey"), result.get(2).get("k1"));
        }
    }
}