Search in sources :

Example 91 with MemoryMetadataStorageManager

use of herddb.mem.MemoryMetadataStorageManager 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());
        }
    }
}
Also used : CreateTableSpaceStatement(herddb.model.commands.CreateTableSpaceStatement) DBManager(herddb.core.DBManager) DataScanner(herddb.model.DataScanner) MemoryDataStorageManager(herddb.mem.MemoryDataStorageManager) MemoryCommitLogManager(herddb.mem.MemoryCommitLogManager) RawString(herddb.utils.RawString) MemoryMetadataStorageManager(herddb.mem.MemoryMetadataStorageManager) Test(org.junit.Test)

Example 92 with MemoryMetadataStorageManager

use of herddb.mem.MemoryMetadataStorageManager 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));
        }
    }
}
Also used : CreateTableSpaceStatement(herddb.model.commands.CreateTableSpaceStatement) DBManager(herddb.core.DBManager) DataScanner(herddb.model.DataScanner) MemoryDataStorageManager(herddb.mem.MemoryDataStorageManager) RuntimeProjectedDataAccessor(herddb.model.planner.ProjectOp.ZeroCopyProjection.RuntimeProjectedDataAccessor) DataAccessor(herddb.utils.DataAccessor) MemoryCommitLogManager(herddb.mem.MemoryCommitLogManager) RawString(herddb.utils.RawString) MemoryMetadataStorageManager(herddb.mem.MemoryMetadataStorageManager) Test(org.junit.Test)

Example 93 with MemoryMetadataStorageManager

use of herddb.mem.MemoryMetadataStorageManager 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());
    }
}
Also used : CreateTableSpaceStatement(herddb.model.commands.CreateTableSpaceStatement) DBManager(herddb.core.DBManager) MemoryDataStorageManager(herddb.mem.MemoryDataStorageManager) MemoryCommitLogManager(herddb.mem.MemoryCommitLogManager) Timestamp(java.sql.Timestamp) Calendar(java.util.Calendar) RawString(herddb.utils.RawString) Timestamp(java.sql.Timestamp) MemoryMetadataStorageManager(herddb.mem.MemoryMetadataStorageManager) Test(org.junit.Test)

Example 94 with MemoryMetadataStorageManager

use of herddb.mem.MemoryMetadataStorageManager 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());
            }
        }
    }
}
Also used : CreateTableSpaceStatement(herddb.model.commands.CreateTableSpaceStatement) DBManager(herddb.core.DBManager) DataScanner(herddb.model.DataScanner) MemoryDataStorageManager(herddb.mem.MemoryDataStorageManager) MemoryCommitLogManager(herddb.mem.MemoryCommitLogManager) List(java.util.List) RawString(herddb.utils.RawString) MemoryMetadataStorageManager(herddb.mem.MemoryMetadataStorageManager) Test(org.junit.Test)

Example 95 with MemoryMetadataStorageManager

use of herddb.mem.MemoryMetadataStorageManager 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"));
        }
    }
}
Also used : MemoryDataStorageManager(herddb.mem.MemoryDataStorageManager) RuntimeProjectedDataAccessor(herddb.model.planner.ProjectOp.ZeroCopyProjection.RuntimeProjectedDataAccessor) DataAccessor(herddb.utils.DataAccessor) Timestamp(java.sql.Timestamp) RawString(herddb.utils.RawString) Timestamp(java.sql.Timestamp) CreateTableSpaceStatement(herddb.model.commands.CreateTableSpaceStatement) DBManager(herddb.core.DBManager) DataScanner(herddb.model.DataScanner) DMLStatementExecutionResult(herddb.model.DMLStatementExecutionResult) TransactionContext(herddb.model.TransactionContext) MemoryCommitLogManager(herddb.mem.MemoryCommitLogManager) MemoryMetadataStorageManager(herddb.mem.MemoryMetadataStorageManager) Test(org.junit.Test)

Aggregations

MemoryMetadataStorageManager (herddb.mem.MemoryMetadataStorageManager)257 MemoryCommitLogManager (herddb.mem.MemoryCommitLogManager)256 MemoryDataStorageManager (herddb.mem.MemoryDataStorageManager)252 Test (org.junit.Test)247 CreateTableSpaceStatement (herddb.model.commands.CreateTableSpaceStatement)240 DataScanner (herddb.model.DataScanner)156 RawString (herddb.utils.RawString)138 DBManager (herddb.core.DBManager)133 DataAccessor (herddb.utils.DataAccessor)83 Table (herddb.model.Table)80 ScanStatement (herddb.model.commands.ScanStatement)65 CreateTableStatement (herddb.model.commands.CreateTableStatement)57 TranslatedQuery (herddb.sql.TranslatedQuery)55 List (java.util.List)54 Index (herddb.model.Index)40 TransactionContext (herddb.model.TransactionContext)39 CreateIndexStatement (herddb.model.commands.CreateIndexStatement)37 SecondaryIndexSeek (herddb.index.SecondaryIndexSeek)30 RuntimeProjectedDataAccessor (herddb.model.planner.ProjectOp.ZeroCopyProjection.RuntimeProjectedDataAccessor)28 StatementExecutionException (herddb.model.StatementExecutionException)26