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

Example 41 with DBManager

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

the class RawSQLTest method compareToNull.

@Test
public void compareToNull() 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());
        executeUpdate(manager, "INSERT INTO tblspace1.tsql(k1,n1) values(?,?)", Arrays.asList("mykey", null));
        executeUpdate(manager, "INSERT INTO tblspace1.tsql(k1,n1) values(?,?)", Arrays.asList("mykey2", 213));
        executeUpdate(manager, "INSERT INTO tblspace1.tsql(k1,n1) values(?,?)", Arrays.asList("mykey3", 214));
        // comparing with NULLs return NULL, not "false" or "true"
        try (DataScanner scan = scan(manager, "SELECT * FROM tblspace1.tsql WHERE n1=213", Arrays.asList())) {
            assertEquals(1, scan.consume().size());
        }
        try (DataScanner scan = scan(manager, "SELECT * FROM tblspace1.tsql WHERE n1 = NULL", Arrays.asList())) {
            assertEquals(0, scan.consume().size());
        }
        try (DataScanner scan = scan(manager, "SELECT * FROM tblspace1.tsql WHERE n1 IS NULL", Arrays.asList())) {
            assertEquals(1, scan.consume().size());
        }
        try (DataScanner scan = scan(manager, "SELECT * FROM tblspace1.tsql WHERE n1 IS NOT NULL", Arrays.asList())) {
            assertEquals(2, scan.consume().size());
        }
        try (DataScanner scan = scan(manager, "SELECT * FROM tblspace1.tsql WHERE NOT n1 IS NULL", Arrays.asList())) {
            assertEquals(2, scan.consume().size());
        }
        try (DataScanner scan = scan(manager, "SELECT * FROM tblspace1.tsql WHERE NOT n1 <= 213", Arrays.asList())) {
            assertEquals(1, scan.consume().size());
        }
        try (DataScanner scan = scan(manager, "SELECT * FROM tblspace1.tsql WHERE n1 <= 214", Arrays.asList())) {
            assertEquals(2, scan.consume().size());
        }
        try (DataScanner scan = scan(manager, "SELECT * FROM tblspace1.tsql WHERE n1 > 213", Arrays.asList())) {
            assertEquals(1, scan.consume().size());
        }
        try (DataScanner scan = scan(manager, "SELECT * FROM tblspace1.tsql WHERE n1 >= 213", Arrays.asList())) {
            assertEquals(2, scan.consume().size());
        }
        // In sorts NULL is greater than other values
        try (DataScanner scan = scan(manager, "SELECT k1 FROM tblspace1.tsql ORDER BY n1 ASC NULLS LAST", Arrays.asList())) {
            List<DataAccessor> consume = scan.consume();
            assertEquals(3, consume.size());
            int i = 0;
            assertEquals(RawString.of("mykey2"), consume.get(i++).get(0));
            assertEquals(RawString.of("mykey3"), consume.get(i++).get(0));
            assertEquals(RawString.of("mykey"), consume.get(i++).get(0));
        }
        try (DataScanner scan = scan(manager, "SELECT k1 FROM tblspace1.tsql ORDER BY n1 DESC NULLS LAST", Arrays.asList())) {
            List<DataAccessor> consume = scan.consume();
            assertEquals(3, consume.size());
            int i = 0;
            assertEquals(RawString.of("mykey"), consume.get(i++).get(0));
            assertEquals(RawString.of("mykey3"), consume.get(i++).get(0));
            assertEquals(RawString.of("mykey2"), consume.get(i++).get(0));
        }
        try (DataScanner scan = scan(manager, "SELECT k1 FROM tblspace1.tsql ORDER BY n1 ASC", Arrays.asList())) {
            List<DataAccessor> consume = scan.consume();
            assertEquals(3, consume.size());
            int i = 0;
            assertEquals(RawString.of("mykey2"), consume.get(i++).get(0));
            assertEquals(RawString.of("mykey3"), consume.get(i++).get(0));
            assertEquals(RawString.of("mykey"), consume.get(i++).get(0));
        }
        try (DataScanner scan = scan(manager, "SELECT k1 FROM tblspace1.tsql ORDER BY n1 DESC", Arrays.asList())) {
            List<DataAccessor> consume = scan.consume();
            assertEquals(3, consume.size());
            int i = 0;
            assertEquals(RawString.of("mykey"), consume.get(i++).get(0));
            assertEquals(RawString.of("mykey3"), consume.get(i++).get(0));
            assertEquals(RawString.of("mykey2"), consume.get(i++).get(0));
        }
        try (DataScanner scan = scan(manager, "SELECT k1 FROM tblspace1.tsql ORDER BY n1 ASC NULLS FIRST", Arrays.asList())) {
            List<DataAccessor> consume = scan.consume();
            assertEquals(3, consume.size());
            int i = 0;
            assertEquals(RawString.of("mykey"), consume.get(i++).get(0));
            assertEquals(RawString.of("mykey2"), consume.get(i++).get(0));
            assertEquals(RawString.of("mykey3"), consume.get(i++).get(0));
        }
    }
}

Example 42 with DBManager

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

the class RawSQLTest method timestampFunctionsTest.

@Test
public void timestampFunctionsTest() 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 string primary key,d1 timestamp)", Collections.emptyList());
        java.sql.Timestamp now = new java.sql.Timestamp(System.currentTimeMillis());
        java.sql.Timestamp nowPlusOneDayInMillis = new java.sql.Timestamp(now.getTime() + 1000 * 60 * 60 * 24);
        assertEquals(1, executeUpdate(manager, "INSERT INTO tblspace1.tsql(k1,d1) values(?,?)", Arrays.asList("mykey", now)).getUpdateCount());
        assertEquals(1, scan(manager, "SELECT * FROM tblspace1.tsql where d1=?", Arrays.asList(now)).consumeAndClose().size());
        assertEquals(1, scan(manager, "SELECT * FROM tblspace1.tsql where timestampdiff(SECOND, d1, CAST(? AS TIMESTAMP)) = 60 * 60 * 24", Arrays.asList(nowPlusOneDayInMillis)).consumeAndClose().size());
        Calendar cal = Calendar.getInstance();
        cal.setTimeInMillis(now.getTime());
        assertEquals(1, scan(manager, "SELECT * FROM tblspace1.tsql where YEAR(d1) = ?", Arrays.asList(cal.get(Calendar.YEAR))).consumeAndClose().size());
        assertEquals(1, scan(manager, "SELECT * FROM tblspace1.tsql where MONTH(d1)= ?", Arrays.asList(cal.get(Calendar.MONTH) + 1)).consumeAndClose().size());
        assertEquals(1, scan(manager, "SELECT * FROM tblspace1.tsql where DAYOFMONTH(d1)= ?", Arrays.asList(cal.get(Calendar.DAY_OF_MONTH))).consumeAndClose().size());
        assertEquals(1, scan(manager, "SELECT * FROM tblspace1.tsql where HOUR(d1)= ?", Arrays.asList(cal.get(Calendar.HOUR_OF_DAY))).consumeAndClose().size());
        assertEquals(1, scan(manager, "SELECT * FROM tblspace1.tsql where MINUTE(d1)= ?", Arrays.asList(cal.get(Calendar.MINUTE))).consumeAndClose().size());
        assertEquals(1, scan(manager, "SELECT * FROM tblspace1.tsql where SECOND(d1)= ?", Arrays.asList(cal.get(Calendar.SECOND))).consumeAndClose().size());
        cal.set(Calendar.MILLISECOND, 0);
        cal.set(Calendar.SECOND, 0);
        cal.set(Calendar.MINUTE, 0);
        cal.set(Calendar.HOUR_OF_DAY, 0);
        java.sql.Timestamp midnight = new java.sql.Timestamp(cal.getTimeInMillis());
        int convertedDayOfWeek = cal.get(Calendar.DAY_OF_WEEK) - 1;
        if (convertedDayOfWeek == 0) {
            convertedDayOfWeek = 7;
        }
        assertEquals(1, scan(manager, "SELECT * FROM tblspace1.tsql where DAYOFWEEK(d1)= ?", Arrays.asList(convertedDayOfWeek)).consumeAndClose().size());
        assertEquals(1, scan(manager, "SELECT * FROM tblspace1.tsql where FLOOR(d1 TO DAY) = ?", Arrays.asList(midnight)).consumeAndClose().size());
        cal.add(Calendar.DATE, 1);
        java.sql.Timestamp midnightOfTomorrow = new java.sql.Timestamp(cal.getTimeInMillis());
        assertEquals(1, scan(manager, "SELECT * FROM tblspace1.tsql" + " where TIMESTAMPADD(DAY, 1, CAST(? AS TIMESTAMP)) = CAST(? AS TIMESTAMP)", Arrays.asList(midnight, midnightOfTomorrow)).consumeAndClose().size());
    }
}

Example 43 with DBManager

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

the class RawSQLTest method simpleEqualsTest.

@Test
public void simpleEqualsTest() 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(5), "b")).getUpdateCount());
        assertEquals(1, executeUpdate(manager, "INSERT INTO tblspace1.tsql(k1) values(?)", Arrays.asList("mykey4")).getUpdateCount());
        {
            try (DataScanner scan1 = scan(manager, "SELECT * FROM tblspace1.tsql where k1=?", Arrays.asList("mykey"))) {
                List<DataAccessor> result = scan1.consume();
                assertEquals(1, result.size());
            }
        }
        {
            try (DataScanner scan1 = scan(manager, "SELECT * FROM tblspace1.tsql where k1=?", Arrays.asList((Object) null))) {
                List<DataAccessor> result = scan1.consume();
                assertEquals(0, result.size());
            }
        }
    }
}

Example 44 with DBManager

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

the class RawSQLTest method multiInsert.

@Test
public void multiInsert() 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,t1 timestamp)", Collections.emptyList());
        java.sql.Timestamp tt1 = new java.sql.Timestamp(System.currentTimeMillis());
        java.sql.Timestamp tt2 = new java.sql.Timestamp(System.currentTimeMillis() + 60000);
        assertEquals(2, executeUpdate(manager, "INSERT INTO tblspace1.tsql(k1,n1,t1) values(?,?,?),(?,?,?)", Arrays.asList("mykey", Integer.valueOf(1234), tt1, "mykey2", Integer.valueOf(1235), tt2)).getUpdateCount());
        try (DataScanner scan = scan(manager, "SELECT k1,n1,t1 FROM tblspace1.tsql ORDER BY n1 desc", Collections.emptyList())) {
            List<DataAccessor> res = scan.consume();
            assertEquals(RawString.of("mykey2"), res.get(0).get("k1"));
            assertEquals(RawString.of("mykey"), res.get(1).get("k1"));
            assertEquals(Integer.valueOf(1235), res.get(0).get("n1"));
            assertEquals(Integer.valueOf(1234), res.get(1).get("n1"));
            assertEquals(tt2, res.get(0).get("t1"));
            assertEquals(tt1, res.get(1).get("t1"));
        }
        execute(manager, "CREATE TABLE tblspace1.tsql2 (a1 integer auto_increment primary key, k1 string ,n1 int,s1 string,t1 timestamp)", Collections.emptyList());
        assertEquals(2, executeUpdate(manager, "INSERT INTO tblspace1.tsql2(k1,n1,t1) values(?,?,?),(?,?,?)", Arrays.asList("mykey", Integer.valueOf(1234), tt1, "mykey2", Integer.valueOf(1235), tt2)).getUpdateCount());
        try (DataScanner scan = scan(manager, "SELECT a1,k1,n1,t1 FROM tblspace1.tsql2 ORDER BY n1 desc", Collections.emptyList())) {
            List<DataAccessor> res = scan.consume();
            assertEquals(RawString.of("mykey2"), res.get(0).get("k1"));
            assertEquals(RawString.of("mykey"), res.get(1).get("k1"));
            assertEquals(Integer.valueOf(1235), res.get(0).get("n1"));
            assertEquals(Integer.valueOf(1234), res.get(1).get("n1"));
            assertEquals(tt2, res.get(0).get("t1"));
            assertEquals(tt1, res.get(1).get("t1"));
            assertEquals(2, res.get(0).get("a1"));
            assertEquals(1, res.get(1).get("a1"));
        }
        // auto-transaction
        execute(manager, "CREATE TABLE tblspace1.tsql3 (a1 integer auto_increment primary key, k1 string ,n1 int,s1 string,t1 timestamp)", Collections.emptyList());
        DMLStatementExecutionResult resInsert = executeUpdate(manager, "INSERT INTO tblspace1.tsql3(k1,n1,t1) values(?,?,?),(?,?,?)", Arrays.asList("mykey", Integer.valueOf(1234), tt1, "mykey2", Integer.valueOf(1235), tt2), TransactionContext.AUTOTRANSACTION_TRANSACTION);
        assertEquals(2, resInsert.getUpdateCount());
        assertTrue(resInsert.transactionId > 0);
        try (DataScanner scan = scan(manager, "SELECT a1,k1,n1,t1 FROM tblspace1.tsql3 ORDER BY n1 desc", Collections.emptyList(), 0, new TransactionContext(resInsert.transactionId))) {
            List<DataAccessor> res = scan.consume();
            assertEquals(RawString.of("mykey2"), res.get(0).get("k1"));
            assertEquals(RawString.of("mykey"), res.get(1).get("k1"));
            assertEquals(Integer.valueOf(1235), res.get(0).get("n1"));
            assertEquals(Integer.valueOf(1234), res.get(1).get("n1"));
            assertEquals(tt2, res.get(0).get("t1"));
            assertEquals(tt1, res.get(1).get("t1"));
            assertEquals(2, res.get(0).get("a1"));
            assertEquals(1, res.get(1).get("a1"));
        }
    }
}

Example 45 with DBManager

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

the class RawSQLTest method selectFromDualTest.

@Test
public void selectFromDualTest() 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);
        try (DataScanner scan = TestUtils.scan(manager, "SELECT 'one' as theStringConstant,3 LongConstant FROM DUAL", Collections.emptyList())) {
            List<DataAccessor> records = scan.consume();
            assertEquals(1, records.size());
            assertEquals("one", records.get(0).get("theStringConstant").toString());
            assertEquals(3L, records.get(0).get("LongConstant"));
        }
        try (DataScanner scan = TestUtils.scan(manager, "SELECT 'one' as theStringConstant,3  LongConstant FROM tblspace1.Dual", Collections.emptyList())) {
            List<DataAccessor> records = scan.consume();
            assertEquals(1, records.size());
            assertEquals("one", records.get(0).get("theStringConstant").toString());
            assertEquals(3L, records.get(0).get("LongConstant"));
        }
    }
}