Examples with TranslatedQuery herddb.sql.TranslatedQuery used on opensource projects

Example 81 with TranslatedQuery

use of herddb.sql.TranslatedQuery in project herddb by diennea.

the class IndexScanRangeTest method secondaryIndexSeek.

private void secondaryIndexSeek(String indexType) 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);
        Table table = Table.builder().tablespace("tblspace1").name("t1").column("id", ColumnTypes.STRING).column("n1", ColumnTypes.INTEGER).column("n2", ColumnTypes.INTEGER).column("name", ColumnTypes.STRING).primaryKey("id").build();
        CreateTableStatement st2 = new CreateTableStatement(table);
        manager.executeStatement(st2, StatementEvaluationContext.DEFAULT_EVALUATION_CONTEXT(), TransactionContext.NO_TRANSACTION);
        Index index = Index.builder().onTable(table).type(indexType).column("n1", ColumnTypes.INTEGER).build();
        TestUtils.executeUpdate(manager, "INSERT INTO tblspace1.t1(id,n1,n2,name) values('a',1,5,'n1')", Collections.emptyList());
        TestUtils.executeUpdate(manager, "INSERT INTO tblspace1.t1(id,n1,n2,name) values('b',2,5,'n1')", Collections.emptyList());
        TestUtils.executeUpdate(manager, "INSERT INTO tblspace1.t1(id,n1,n2,name) values('c',2,5,'n2')", Collections.emptyList());
        TestUtils.executeUpdate(manager, "INSERT INTO tblspace1.t1(id,n1,n2,name) values('d',2,5,'n2')", Collections.emptyList());
        TestUtils.executeUpdate(manager, "INSERT INTO tblspace1.t1(id,n1,n2,name) values('e',3,5,'n2')", Collections.emptyList());
        // create index, it will be built using existing data
        CreateIndexStatement st3 = new CreateIndexStatement(index);
        manager.executeStatement(st3, StatementEvaluationContext.DEFAULT_EVALUATION_CONTEXT(), TransactionContext.NO_TRANSACTION);
        {
            TranslatedQuery translated = manager.getPlanner().translate(TableSpace.DEFAULT, "SELECT * FROM tblspace1.t1 WHERE n1=2", Collections.emptyList(), true, true, false, -1);
            ScanStatement scan = translated.plan.mainStatement.unwrap(ScanStatement.class);
            assertTrue(scan.getPredicate().getIndexOperation() instanceof SecondaryIndexSeek);
            try (DataScanner scan1 = manager.scan(scan, translated.context, TransactionContext.NO_TRANSACTION)) {
                assertEquals(3, scan1.consume().size());
            }
        }
    }
}

Example 82 with TranslatedQuery

use of herddb.sql.TranslatedQuery in project herddb by diennea.

the class SecondaryUniqueIndexAccessSuite method secondaryUniqueIndexPrefixScan.

@Test
public void secondaryUniqueIndexPrefixScan() throws Exception {
    String nodeId = "localhost";
    Path tmp = tmpDir.newFolder().toPath();
    ServerConfiguration serverConfiguration = newServerConfigurationWithAutoPort(tmp);
    serverConfiguration.set(ServerConfiguration.PROPERTY_READLOCK_TIMEOUT, 3);
    serverConfiguration.set(ServerConfiguration.PROPERTY_WRITELOCK_TIMEOUT, 3);
    try (DBManager manager = new DBManager("localhost", new MemoryMetadataStorageManager(), new MemoryDataStorageManager(), new MemoryCommitLogManager(), tmp, null, serverConfiguration, NullStatsLogger.INSTANCE)) {
        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);
        Table table = Table.builder().tablespace("tblspace1").name("t1").column("id", ColumnTypes.STRING).column("n1", ColumnTypes.INTEGER).column("name", ColumnTypes.STRING).primaryKey("id").build();
        CreateTableStatement st2 = new CreateTableStatement(table);
        manager.executeStatement(st2, StatementEvaluationContext.DEFAULT_EVALUATION_CONTEXT(), TransactionContext.NO_TRANSACTION);
        Index index = Index.builder().onTable(table).type(Index.TYPE_HASH).unique(true).column("n1", ColumnTypes.INTEGER).column("name", ColumnTypes.STRING).build();
        TestUtils.executeUpdate(manager, "INSERT INTO tblspace1.t1(id,n1,name) values('a',1,'n1')", Collections.emptyList());
        // create index, it will be built using existing data
        CreateIndexStatement st3 = new CreateIndexStatement(index);
        manager.executeStatement(st3, StatementEvaluationContext.DEFAULT_EVALUATION_CONTEXT(), TransactionContext.NO_TRANSACTION);
        // cannot insert another record with '1-n1'
        herddb.utils.TestUtils.assertThrows(UniqueIndexContraintViolationException.class, () -> {
            TestUtils.executeUpdate(manager, "INSERT INTO tblspace1.t1(id,n1,name) values('b',1,'n1')", Collections.emptyList());
        });
        // multiple values
        // it is not a transaction, the first INSERT will succeed
        herddb.utils.TestUtils.assertThrows(UniqueIndexContraintViolationException.class, () -> {
            TestUtils.executeUpdate(manager, "INSERT INTO tblspace1.t1(id,n1,name) values('b',8,'n1'),('c',1,'n1')", Collections.emptyList());
        });
        TestUtils.executeUpdate(manager, "INSERT INTO tblspace1.t1(id,n1,name) values('d',2,'n2')", Collections.emptyList());
        TestUtils.executeUpdate(manager, "INSERT INTO tblspace1.t1(id,n1,name) values('e',3,'n2')", Collections.emptyList());
        // single record UPDATE
        herddb.utils.TestUtils.assertThrows(UniqueIndexContraintViolationException.class, () -> {
            TestUtils.executeUpdate(manager, "UPDATE tblspace1.t1 set n1=1,name='n1' where id='d'", Collections.emptyList());
        });
        // multi record UPDATE
        herddb.utils.TestUtils.assertThrows(UniqueIndexContraintViolationException.class, () -> {
            TestUtils.executeUpdate(manager, "UPDATE tblspace1.t1 set n1=1,name='n1' where id='d'", Collections.emptyList());
        });
        {
            TranslatedQuery translated = manager.getPlanner().translate(TableSpace.DEFAULT, "SELECT * FROM tblspace1.t1 WHERE n1=1", Collections.emptyList(), true, true, false, -1);
            ScanStatement scan = translated.plan.mainStatement.unwrap(ScanStatement.class);
            assertTrue(scan.getPredicate().getIndexOperation() instanceof SecondaryIndexPrefixScan);
            try (DataScanner scan1 = manager.scan(scan, translated.context, TransactionContext.NO_TRANSACTION)) {
                assertEquals(1, scan1.consume().size());
            }
        }
        {
            TranslatedQuery translated = manager.getPlanner().translate(TableSpace.DEFAULT, "SELECT * FROM tblspace1.t1 WHERE n1=8", Collections.emptyList(), true, true, false, -1);
            ScanStatement scan = translated.plan.mainStatement.unwrap(ScanStatement.class);
            assertTrue(scan.getPredicate().getIndexOperation() instanceof SecondaryIndexPrefixScan);
            try (DataScanner scan1 = manager.scan(scan, translated.context, TransactionContext.NO_TRANSACTION)) {
                assertEquals(1, scan1.consume().size());
            }
        }
        {
            TranslatedQuery translated = manager.getPlanner().translate(TableSpace.DEFAULT, "SELECT * FROM tblspace1.t1 WHERE n1=2 and name='n2'", Collections.emptyList(), true, true, false, -1);
            ScanStatement scan = translated.plan.mainStatement.unwrap(ScanStatement.class);
            assertTrue(scan.getPredicate().getIndexOperation() instanceof SecondaryIndexSeek);
            try (DataScanner scan1 = manager.scan(scan, translated.context, TransactionContext.NO_TRANSACTION)) {
                assertEquals(1, scan1.consume().size());
            }
        }
        {
            TranslatedQuery translated = manager.getPlanner().translate(TableSpace.DEFAULT, "SELECT * FROM tblspace1.t1 WHERE n1>=1", Collections.emptyList(), true, true, false, -1);
            ScanStatement scan = translated.plan.mainStatement.unwrap(ScanStatement.class);
            assertNull(scan.getPredicate().getIndexOperation());
            try (DataScanner scan1 = manager.scan(scan, translated.context, TransactionContext.NO_TRANSACTION)) {
                assertEquals(4, scan1.consume().size());
            }
        }
        // update the index
        TestUtils.executeUpdate(manager, "UPDATE tblspace1.t1 set n1=10,name='n1' where id='a'", Collections.emptyList());
        TestUtils.executeUpdate(manager, "UPDATE tblspace1.t1 set n1=1,name='n1' where id='d'", Collections.emptyList());
        {
            TranslatedQuery translated = manager.getPlanner().translate(TableSpace.DEFAULT, "SELECT * FROM tblspace1.t1 WHERE n1=1", Collections.emptyList(), true, true, false, -1);
            ScanStatement scan = translated.plan.mainStatement.unwrap(ScanStatement.class);
            assertTrue(scan.getPredicate().getIndexOperation() instanceof SecondaryIndexPrefixScan);
            try (DataScanner scan1 = manager.scan(scan, translated.context, TransactionContext.NO_TRANSACTION)) {
                List<DataAccessor> consume = scan1.consume();
                assertEquals(1, consume.size());
                assertEquals(RawString.of("d"), consume.get(0).get("id"));
            }
        }
        // delete, update the index
        TestUtils.executeUpdate(manager, "DELETE FROM tblspace1.t1 where id='d'", Collections.emptyList());
        // enure another record can be stored on the same key
        TestUtils.executeUpdate(manager, "INSERT INTO tblspace1.t1(id,n1,name) values('o',1,'n1')", Collections.emptyList());
        // test transactions
        long tx = TestUtils.beginTransaction(manager, "tblspace1");
        // insert a new record, and a new index entry, but in transaction, the transacition will hold the index lock on "100-n100"
        TestUtils.executeUpdate(manager, "INSERT INTO tblspace1.t1(id,n1,name) values('t1',100,'n100')", Collections.emptyList(), new TransactionContext(tx));
        Transaction transaction = manager.getTableSpaceManager("tblspace1").getTransaction(tx);
        assertEquals(1, transaction.locks.get("t1_n1_name").size());
        // delete the same record, the index still hasn't been touched, but we are still holding a lock on "100-n100"
        TestUtils.executeUpdate(manager, "DELETE FROM  tblspace1.t1 where id='t1'", Collections.emptyList(), new TransactionContext(tx));
        // insert a new record again, with the same key in the index, we are still holding the lock on "100-n100"
        TestUtils.executeUpdate(manager, "INSERT INTO tblspace1.t1(id,n1,name) values('t2',100,'n100')", Collections.emptyList(), new TransactionContext(tx));
        // check that we are going to timeout on lock acquisition from another context (for instance no transaction)
        StatementExecutionException err = herddb.utils.TestUtils.expectThrows(StatementExecutionException.class, () -> {
            TestUtils.executeUpdate(manager, "INSERT INTO tblspace1.t1(id,n1,name) values('t3',100,'n100')", Collections.emptyList());
        });
        assertThat(err.getCause(), instanceOf(LockAcquireTimeoutException.class));
        TestUtils.commitTransaction(manager, "tblspace1", tx);
        // cannot insert another record with "100-n100"
        herddb.utils.TestUtils.assertThrows(UniqueIndexContraintViolationException.class, () -> {
            TestUtils.executeUpdate(manager, "INSERT INTO tblspace1.t1(id,n1,name) values('t3',100,'n100')", Collections.emptyList());
        });
        // again, with transactions, use 200-n200 as index key
        tx = TestUtils.beginTransaction(manager, "tblspace1");
        TestUtils.executeUpdate(manager, "INSERT INTO tblspace1.t1(id,n1,name) values('s1',200,'n200')", Collections.emptyList(), new TransactionContext(tx));
        // touching the same record, lock has been already held by the transaction
        TestUtils.executeUpdate(manager, "UPDATE tblspace1.t1 set n1=200 where n1=200", Collections.emptyList(), new TransactionContext(tx));
        TestUtils.commitTransaction(manager, "tblspace1", tx);
        // again, lock the 200-n200 key with a fake update
        tx = TestUtils.beginTransaction(manager, "tblspace1");
        assertEquals(1, TestUtils.executeUpdate(manager, "UPDATE tblspace1.t1 set n1=200 where n1=200", Collections.emptyList(), new TransactionContext(tx)).getUpdateCount());
        // timeout during an update, lock for 200-n200 is on the transaction, can't perform the same update from another context
        err = herddb.utils.TestUtils.expectThrows(StatementExecutionException.class, () -> {
            TestUtils.executeUpdate(manager, "UPDATE tblspace1.t1 set n1=200 where n1=200", Collections.emptyList());
        });
        assertThat(err.getCause(), instanceOf(LockAcquireTimeoutException.class));
        assertEquals(1, TestUtils.executeUpdate(manager, "DELETE FROM tblspace1.t1 where n1=200", Collections.emptyList(), new TransactionContext(tx)).getUpdateCount());
        TestUtils.commitTransaction(manager, "tblspace1", tx);
        TestUtils.executeUpdate(manager, "INSERT INTO tblspace1.t1(id,n1,name) values('s1',200,'n200')", Collections.emptyList());
        // select "FOR UPDATE" does not create locks for the index
        tx = TestUtils.beginTransaction(manager, "tblspace1");
        assertEquals(1, TestUtils.scan(manager, "SELECT * FROM tblspace1.t1 where n1=200 FOR UPDATE", Collections.emptyList(), new TransactionContext(tx)).consumeAndClose().size());
        transaction = manager.getTableSpaceManager("tblspace1").getTransaction(tx);
        assertNull(transaction.locks.get("t1_n1_name"));
        // but it creates a lock for the primary key
        assertEquals(1, transaction.locks.get("t1").size());
        assertEquals(1, TestUtils.executeUpdate(manager, "UPDATE tblspace1.t1 set n1=200 where n1=200", Collections.emptyList(), new TransactionContext(tx)).getUpdateCount());
        // now we have the lock on the unique index
        assertEquals(1, transaction.locks.get("t1_n1_name").size());
        // still holding the lock for the primary key
        assertEquals(1, transaction.locks.get("t1").size());
        // timeout during an update, lock for 200-n200 is on the transaction, can't perform the same update from another context
        err = herddb.utils.TestUtils.expectThrows(StatementExecutionException.class, () -> {
            TestUtils.executeUpdate(manager, "UPDATE tblspace1.t1 set n1=200, name='n200' where id='t2'", Collections.emptyList());
        });
        assertThat(err.getCause(), instanceOf(LockAcquireTimeoutException.class));
        TestUtils.commitTransaction(manager, "tblspace1", tx);
    }
}

Example 83 with TranslatedQuery

use of herddb.sql.TranslatedQuery in project herddb by diennea.

the class RestartTestBase method recoverTableAndIndexNoData.

/**
 * Evaluate that a recover work even on a table without any data inserted
 */
@Test
public void recoverTableAndIndexNoData() throws Exception {
    Path dataPath = folder.newFolder("data").toPath();
    Path logsPath = folder.newFolder("logs").toPath();
    Path metadataPath = folder.newFolder("metadata").toPath();
    Path tmoDir = folder.newFolder("tmoDir").toPath();
    String nodeId = "localhost";
    Table table;
    Index index;
    try (DBManager manager = buildDBManager(nodeId, metadataPath, dataPath, logsPath, tmoDir)) {
        manager.start();
        CreateTableSpaceStatement st1 = new CreateTableSpaceStatement("tblspace1", Collections.singleton(nodeId), nodeId, 1, 0, 0);
        manager.executeStatement(st1, StatementEvaluationContext.DEFAULT_EVALUATION_CONTEXT(), TransactionContext.NO_TRANSACTION);
        assertTrue(manager.waitForTablespace("tblspace1", 10000));
        table = Table.builder().tablespace("tblspace1").name("t1").column("id", ColumnTypes.INTEGER).column("name", ColumnTypes.STRING).primaryKey("id").build();
        index = Index.builder().onTable(table).column("name", ColumnTypes.STRING).type(Index.TYPE_BRIN).build();
        manager.executeStatement(new CreateTableStatement(table), StatementEvaluationContext.DEFAULT_EVALUATION_CONTEXT(), TransactionContext.NO_TRANSACTION);
        manager.executeStatement(new CreateIndexStatement(index), StatementEvaluationContext.DEFAULT_EVALUATION_CONTEXT(), TransactionContext.NO_TRANSACTION);
        GetResult result = manager.get(new GetStatement("tblspace1", table.name, Bytes.from_int(1), null, false), StatementEvaluationContext.DEFAULT_EVALUATION_CONTEXT(), TransactionContext.NO_TRANSACTION);
        assertFalse(result.found());
        /* Access through index  */
        TranslatedQuery translated = manager.getPlanner().translate(TableSpace.DEFAULT, "SELECT * FROM tblspace1.t1 WHERE name=\'uno\'", Collections.emptyList(), true, true, false, -1);
        ScanStatement scan = translated.plan.mainStatement.unwrap(ScanStatement.class);
        assertTrue(scan.getPredicate().getIndexOperation() instanceof SecondaryIndexSeek);
        try (DataScanner scan1 = manager.scan(scan, translated.context, TransactionContext.NO_TRANSACTION)) {
            assertEquals(0, scan1.consume().size());
        }
        manager.checkpoint();
    }
    try (DBManager manager = buildDBManager(nodeId, metadataPath, dataPath, logsPath, tmoDir)) {
        manager.start();
        assertTrue(manager.waitForBootOfLocalTablespaces(10000));
        /* Access through key to page */
        GetResult result = manager.get(new GetStatement("tblspace1", table.name, Bytes.from_int(1), null, false), StatementEvaluationContext.DEFAULT_EVALUATION_CONTEXT(), TransactionContext.NO_TRANSACTION);
        assertFalse(result.found());
        /* Access through index  */
        TranslatedQuery translated = manager.getPlanner().translate(TableSpace.DEFAULT, "SELECT * FROM tblspace1.t1 WHERE name=\'uno\'", Collections.emptyList(), true, true, false, -1);
        ScanStatement scan = translated.plan.mainStatement.unwrap(ScanStatement.class);
        assertTrue(scan.getPredicate().getIndexOperation() instanceof SecondaryIndexSeek);
        try (DataScanner scan1 = manager.scan(scan, translated.context, TransactionContext.NO_TRANSACTION)) {
            assertEquals(0, scan1.consume().size());
        }
    }
}

Example 84 with TranslatedQuery

use of herddb.sql.TranslatedQuery in project herddb by diennea.

the class SimpleBrinIndexRecoveryTest method createRecoveryIndex_withduoblecheckpoint.

@Test
public void createRecoveryIndex_withduoblecheckpoint() throws Exception {
    Path dataPath = folder.newFolder("data").toPath();
    Path logsPath = folder.newFolder("logs").toPath();
    Path metadataPath = folder.newFolder("metadata").toPath();
    Path tmoDir = folder.newFolder("tmoDir").toPath();
    String nodeId = "localhost";
    try (DBManager manager = new DBManager("localhost", new FileMetadataStorageManager(metadataPath), new FileDataStorageManager(dataPath), new FileCommitLogManager(logsPath), tmoDir, 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);
        assertTrue(manager.waitForTablespace("tblspace1", 10000));
        Table table = Table.builder().tablespace("tblspace1").name("t1").column("id", ColumnTypes.STRING).column("name", ColumnTypes.STRING).primaryKey("id").build();
        CreateTableStatement st2 = new CreateTableStatement(table);
        manager.executeStatement(st2, StatementEvaluationContext.DEFAULT_EVALUATION_CONTEXT(), TransactionContext.NO_TRANSACTION);
        Index index = Index.builder().onTable(table).type(Index.TYPE_BRIN).column("name", ColumnTypes.STRING).build();
        TestUtils.executeUpdate(manager, "INSERT INTO tblspace1.t1(id,name) values('a','n1')", Collections.emptyList());
        TestUtils.executeUpdate(manager, "INSERT INTO tblspace1.t1(id,name) values('b','n1')", Collections.emptyList());
        TestUtils.executeUpdate(manager, "INSERT INTO tblspace1.t1(id,name) values('c','n1')", Collections.emptyList());
        TestUtils.executeUpdate(manager, "INSERT INTO tblspace1.t1(id,name) values('d','n2')", Collections.emptyList());
        TestUtils.executeUpdate(manager, "INSERT INTO tblspace1.t1(id,name) values('e','n2')", Collections.emptyList());
        CreateIndexStatement st3 = new CreateIndexStatement(index);
        manager.executeStatement(st3, StatementEvaluationContext.DEFAULT_EVALUATION_CONTEXT(), TransactionContext.NO_TRANSACTION);
        TranslatedQuery translated = manager.getPlanner().translate(TableSpace.DEFAULT, "SELECT * FROM tblspace1.t1 WHERE name='n1'", Collections.emptyList(), true, true, false, -1);
        ScanStatement scan = translated.plan.mainStatement.unwrap(ScanStatement.class);
        assertTrue(scan.getPredicate().getIndexOperation() instanceof SecondaryIndexSeek);
        try (DataScanner scan1 = manager.scan(scan, translated.context, TransactionContext.NO_TRANSACTION)) {
            assertEquals(3, scan1.consume().size());
        }
        manager.checkpoint();
        manager.checkpoint();
    }
    try (DBManager manager = new DBManager("localhost", new FileMetadataStorageManager(metadataPath), new FileDataStorageManager(dataPath), new FileCommitLogManager(logsPath), tmoDir, null)) {
        manager.start();
        assertTrue(manager.waitForBootOfLocalTablespaces(10000));
        TranslatedQuery translated = manager.getPlanner().translate(TableSpace.DEFAULT, "SELECT * FROM tblspace1.t1 WHERE name='n1'", Collections.emptyList(), true, true, false, -1);
        ScanStatement scan = translated.plan.mainStatement.unwrap(ScanStatement.class);
        assertTrue(scan.getPredicate().getIndexOperation() instanceof SecondaryIndexSeek);
        try (DataScanner scan1 = manager.scan(scan, translated.context, TransactionContext.NO_TRANSACTION)) {
            assertEquals(3, scan1.consume().size());
        }
    }
}

Example 85 with TranslatedQuery

use of herddb.sql.TranslatedQuery in project herddb by diennea.

the class TestUtils method dump.

public static void dump(DBManager manager, String query, List<Object> parameters, TransactionContext transactionContext, Consumer<DataAccessor> sink) throws StatementExecutionException, DataScannerException {
    LOG.log(Level.INFO, "Dump {0} parameters: {1}", new Object[] { query, parameters });
    if (parameters == null) {
        parameters = Collections.emptyList();
    }
    TranslatedQuery translated = manager.getPlanner().translate(TableSpace.DEFAULT, query, parameters, true, true, false, -1);
    try (DataScanner scanner = ((ScanResult) manager.executePlan(translated.plan, translated.context, transactionContext)).dataScanner) {
        while (scanner.hasNext()) {
            sink.accept(scanner.next());
        }
    }
}