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

Example 31 with DBManager

use of herddb.core.DBManager 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 32 with DBManager

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

Example 33 with DBManager

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

the class UpdateTest method updateThenSelect.

/**
 * Check update then select (#320, not every row update really was run).
 *
 * @author diego.salvi
 */
@Test
public void updateThenSelect() throws Exception {
    int runs = 50;
    int inserts = 100;
    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);
        for (int run = 0; run < runs; ++run) {
            execute(manager, "CREATE TABLE tblspace1.tsql (k1 int, n1 int, primary key(k1))", Collections.emptyList());
            for (int i = 0; i < inserts; ++i) {
                assertEquals(1, executeUpdate(manager, "INSERT INTO tblspace1.tsql(k1,n1) values(?,?)", Arrays.asList(Integer.valueOf(i), Integer.valueOf(1))).getUpdateCount());
            }
            long tx = beginTransaction(manager, "tblspace1");
            TransactionContext ctx = new TransactionContext(tx);
            assertEquals(inserts, executeUpdate(manager, "UPDATE tblspace1.tsql set n1 = 100", Collections.emptyList(), ctx).getUpdateCount());
            for (int i = 0; i < inserts; ++i) {
                assertEquals(1, scan(manager, "SELECT k1, n1 FROM tblspace1.tsql WHERE k1 = ? AND n1 = 100", Arrays.asList(Integer.valueOf(i)), ctx).consumeAndClose().size());
            }
            for (int i = 0; i < inserts; ++i) {
                try {
                    assertEquals(1, scan(manager, "SELECT k1, n1 FROM tblspace1.tsql WHERE k1 = ? AND n1 = 100", Arrays.asList(Integer.valueOf(i)), ctx).consumeAndClose().size());
                } catch (AssertionError e) {
                    throw e;
                }
            }
            commitTransaction(manager, "tblspace1", tx);
            for (int i = 0; i < inserts; ++i) {
                assertEquals(1, scan(manager, "SELECT k1, n1 FROM tblspace1.tsql WHERE k1 = ? AND n1 = 100", Arrays.asList(Integer.valueOf(i), ctx)).consumeAndClose().size());
            }
            execute(manager, "DROP TABLE tblspace1.tsql", Collections.emptyList());
        }
    }
}

Example 34 with DBManager

use of herddb.core.DBManager 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 35 with DBManager

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

the class RawSQLTest method jdbcWrongParameterCountTest.

@Test
public void jdbcWrongParameterCountTest() 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 {
            scan(manager, "SELECT * FROM tblspace1.tsql where k1=?", Collections.emptyList());
            fail();
        } catch (MissingJDBCParameterException ok) {
            assertEquals(1, ok.getIndex());
        }
        try {
            scan(manager, "SELECT * FROM tblspace1.tsql where k1=1 and n1=?", Collections.emptyList());
            fail();
        } catch (MissingJDBCParameterException ok) {
            assertEquals(1, ok.getIndex());
        }
        try {
            scan(manager, "SELECT * FROM tblspace1.tsql where k1=1 or n1=?", Collections.emptyList());
            fail();
        } catch (MissingJDBCParameterException ok) {
            assertEquals(1, ok.getIndex());
        }
        try {
            scan(manager, "SELECT * FROM tblspace1.tsql order by k1 limit ?", Collections.emptyList());
            fail();
        } catch (MissingJDBCParameterException ok) {
            assertEquals(1, ok.getIndex());
        }
        // }
        if (manager.isFullSQLSupportEnabled()) {
            try {
                scan(manager, "SELECT * FROM tblspace1.tsql where k1 in (SELECT k1 FROM tblspace1.tsql where n1=?)", Collections.emptyList());
                fail();
            } catch (MissingJDBCParameterException ok) {
                assertEquals(1, ok.getIndex());
            }
        }
        if (manager.isFullSQLSupportEnabled()) {
            try {
                scan(manager, "SELECT * FROM tblspace1.tsql where n1=? and k1 in (SELECT k1 FROM tblspace1.tsql where n1=?)", Arrays.asList(1));
                fail();
            } catch (MissingJDBCParameterException ok) {
                assertEquals(2, ok.getIndex());
            }
        }
        try {
            scan(manager, "SELECT * FROM tblspace1.tsql where k1=1 and n1=? and n1=?", Arrays.asList(1));
            fail();
        } catch (MissingJDBCParameterException ok) {
            assertEquals(2, ok.getIndex());
        }
        try {
            scan(manager, "SELECT n1+? FROM tblspace1.tsql", Collections.emptyList());
        } catch (MissingJDBCParameterException ok) {
            assertEquals(1, ok.getIndex());
        }
    }
}