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

Example 96 with DataScanner

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

the class RawSQLTest method allowNullsInSingleColumnSecondaryIndexes.

@Test
public void allowNullsInSingleColumnSecondaryIndexes() 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());
        execute(manager, "CREATE INDEX `ix1` ON tblspace1.tsql(`n1`)", 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));
        try (DataScanner scan = scan(manager, "SELECT * FROM tblspace1.tsql WHERE n1=213", Arrays.asList())) {
            assertEquals(1, scan.consume().size());
        }
        executeUpdate(manager, "UPDATE tblspace1.tsql set n1=null where k1 = ?", Arrays.asList("mykey2"));
        try (DataScanner scan = scan(manager, "SELECT * FROM tblspace1.tsql WHERE n1=213", Arrays.asList())) {
            assertEquals(0, scan.consume().size());
        }
        try (DataScanner scan = scan(manager, "SELECT * FROM tblspace1.tsql WHERE n1 is null", Arrays.asList())) {
            assertEquals(2, scan.consume().size());
        }
    }
}

Example 97 with DataScanner

use of herddb.model.DataScanner 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 98 with DataScanner

use of herddb.model.DataScanner 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 99 with DataScanner

use of herddb.model.DataScanner 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 100 with DataScanner

use of herddb.model.DataScanner 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"));
        }
    }
}