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

Example 1 with TranslatedQuery

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

the class ServerSideConnectionPeer method handleOpenScanner.

private void handleOpenScanner(Message message, Channel _channel) {
    String tableSpace = (String) message.parameters.get("tableSpace");
    Long tx = (Long) message.parameters.get("tx");
    long txId = tx != null ? tx : 0;
    String query = (String) message.parameters.get("query");
    String scannerId = (String) message.parameters.get("scannerId");
    int fetchSize = 10;
    if (message.parameters.containsKey("fetchSize")) {
        fetchSize = (Integer) message.parameters.get("fetchSize");
    }
    int maxRows = 0;
    if (message.parameters.containsKey("maxRows")) {
        maxRows = (Integer) message.parameters.get("maxRows");
    }
    List<Object> parameters = (List<Object>) message.parameters.get("params");
    if (LOGGER.isLoggable(Level.FINEST)) {
        LOGGER.log(Level.FINEST, "openScanner txId+" + txId + ", fetchSize " + fetchSize + ", maxRows " + maxRows + "," + query + " with " + parameters);
    }
    try {
        TranslatedQuery translatedQuery = server.getManager().getPlanner().translate(tableSpace, query, parameters, true, true, false, maxRows);
        if (LOGGER.isLoggable(Level.FINEST)) {
            LOGGER.log(Level.FINEST, query + " -> " + translatedQuery.plan.mainStatement);
        }
        TransactionContext transactionContext = new TransactionContext(txId);
        if (translatedQuery.plan.mainStatement instanceof SQLPlannedOperationStatement || translatedQuery.plan.mainStatement instanceof ScanStatement || translatedQuery.plan.joinStatements != null) {
            ScanResult scanResult = (ScanResult) server.getManager().executePlan(translatedQuery.plan, translatedQuery.context, transactionContext);
            DataScanner dataScanner = scanResult.dataScanner;
            ServerSideScannerPeer scanner = new ServerSideScannerPeer(dataScanner);
            String[] columns = dataScanner.getFieldNames();
            List<DataAccessor> records = dataScanner.consume(fetchSize);
            TuplesList tuplesList = new TuplesList(columns, records);
            boolean last = dataScanner.isFinished();
            LOGGER.log(Level.FINEST, "sending first {0} records to scanner {1} query {2}", new Object[] { records.size(), scannerId, query });
            if (!last) {
                scanners.put(scannerId, scanner);
            }
            _channel.sendReplyMessage(message, Message.RESULTSET_CHUNK(null, scannerId, tuplesList, last, dataScanner.transactionId));
        } else {
            _channel.sendReplyMessage(message, Message.ERROR(null, new Exception("unsupported query type for scan " + query + ": PLAN is " + translatedQuery.plan)));
        }
    } catch (DataScannerException | RuntimeException err) {
        LOGGER.log(Level.SEVERE, "error on scanner " + scannerId + ": " + err, err);
        scanners.remove(scannerId);
        Message error = Message.ERROR(null, err);
        if (err instanceof NotLeaderException) {
            error.setParameter("notLeader", "true");
        }
        _channel.sendReplyMessage(message, error);
    }
}

Example 2 with TranslatedQuery

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

the class RawSQLTest method aliasTest.

@Test
public void aliasTest() 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());
        {
            TranslatedQuery translate1 = manager.getPlanner().translate(TableSpace.DEFAULT, "SELECT k1 theKey FROM tblspace1.tsql where k1 ='mykey2'", Collections.emptyList(), true, true, false, -1);
            ScanStatement scan = translate1.plan.mainStatement.unwrap(ScanStatement.class);
            PlannerOp plannerOp = translate1.plan.mainStatement.unwrap(PlannerOp.class);
            System.out.println("plannerOp:" + plannerOp);
            try (DataScanner scan1 = manager.scan(scan, translate1.context, TransactionContext.NO_TRANSACTION)) {
                List<DataAccessor> records = scan1.consume();
                assertEquals(0, records.size());
                assertEquals(1, scan1.getFieldNames().length);
                assertEquals("thekey", scan1.getFieldNames()[0].toLowerCase());
            }
        }
    }
}

Example 3 with TranslatedQuery

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

the class RawSQLTest method indexSeek.

@Test
public void indexSeek() 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());
        {
            TranslatedQuery translated = manager.getPlanner().translate(TableSpace.DEFAULT, " SELECT k1 as theKey,'one' as theStringConstant,3  LongConstant" + " FROM tblspace1.tsql" + " where k1 ='mykey'", Collections.emptyList(), true, true, false, -1);
            ScanStatement scan = translated.plan.mainStatement.unwrap(ScanStatement.class);
            assertTrue(scan.getPredicate().getIndexOperation() instanceof PrimaryIndexSeek);
            try (DataScanner scan1 = ((ScanResult) manager.executePlan(translated.plan, translated.context, TransactionContext.NO_TRANSACTION)).dataScanner) {
                List<DataAccessor> records = scan1.consume();
                assertEquals(1, records.size());
                assertEquals(3, records.get(0).getFieldNames().length);
                assertEquals(3, records.get(0).toMap().size());
                assertEquals("thekey", records.get(0).getFieldNames()[0].toLowerCase());
                assertEquals(RawString.of("mykey"), records.get(0).get("theKey"));
                assertEquals("thestringconstant", records.get(0).getFieldNames()[1].toLowerCase());
                assertEquals(RawString.of("one"), records.get(0).get("theStringConstant"));
                assertEquals("longconstant", records.get(0).getFieldNames()[2].toLowerCase());
                assertEquals(3L, ((Number) records.get(0).get("LongConstant")).longValue());
            }
        }
        {
            TranslatedQuery translate1 = manager.getPlanner().translate(TableSpace.DEFAULT, "SELECT k1 as theKey,'one' as theStringConstant,3  LongConstant FROM tblspace1.tsql where k1 = 'mykey_no'", Collections.emptyList(), true, true, false, -1);
            ScanStatement scan = translate1.plan.mainStatement.unwrap(ScanStatement.class);
            assertTrue(scan.getPredicate().getIndexOperation() instanceof PrimaryIndexSeek);
            try (DataScanner scan1 = manager.scan(scan, translate1.context, TransactionContext.NO_TRANSACTION)) {
                assertTrue(scan1.consume().isEmpty());
            }
        }
        {
            TranslatedQuery translate1 = manager.getPlanner().translate(TableSpace.DEFAULT, "SELECT k1 as theKey,'one' as theStringConstant,3  LongConstant FROM tblspace1.tsql where k1 = 'mykey' and n1<>1234", Collections.emptyList(), true, true, false, -1);
            ScanStatement scan = translate1.plan.mainStatement.unwrap(ScanStatement.class);
            assertTrue(scan.getPredicate().getIndexOperation() instanceof PrimaryIndexSeek);
            try (DataScanner scan1 = manager.scan(scan, translate1.context, TransactionContext.NO_TRANSACTION)) {
                assertTrue(scan1.consume().isEmpty());
            }
        }
    }
}

Example 4 with TranslatedQuery

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

the class RawSQLTest method orderByTest.

@Test
public void orderByTest() 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(1))).getUpdateCount());
        assertEquals(1, executeUpdate(manager, "INSERT INTO tblspace1.tsql(k1,n1) values(?,?)", Arrays.asList("mykey2", Integer.valueOf(2))).getUpdateCount());
        assertEquals(1, executeUpdate(manager, "INSERT INTO tblspace1.tsql(k1,n1) values(?,?)", Arrays.asList("mykey3", Integer.valueOf(4))).getUpdateCount());
        assertEquals(1, executeUpdate(manager, "INSERT INTO tblspace1.tsql(k1) values(?)", Arrays.asList("mykey4")).getUpdateCount());
        {
            TranslatedQuery translate1 = manager.getPlanner().translate(TableSpace.DEFAULT, "SELECT k1 FROM tblspace1.tsql order by n1", Collections.emptyList(), true, true, false, -1);
            ScanStatement scan = translate1.plan.mainStatement.unwrap(ScanStatement.class);
            try (DataScanner scan1 = manager.scan(scan, translate1.context, TransactionContext.NO_TRANSACTION)) {
                List<DataAccessor> result = scan1.consume();
                assertEquals(4, result.size());
                assertEquals(RawString.of("mykey"), result.get(0).get(0));
                assertEquals(RawString.of("mykey2"), result.get(1).get(0));
                assertEquals(RawString.of("mykey3"), result.get(2).get(0));
                // NULLS LAST
                assertEquals(RawString.of("mykey4"), result.get(3).get(0));
            }
        }
        {
            TranslatedQuery translate1 = manager.getPlanner().translate(TableSpace.DEFAULT, "SELECT k1 FROM tblspace1.tsql order by n1 desc", Collections.emptyList(), true, true, false, -1);
            ScanStatement scan = translate1.plan.mainStatement.unwrap(ScanStatement.class);
            try (DataScanner scan1 = manager.scan(scan, translate1.context, TransactionContext.NO_TRANSACTION)) {
                List<DataAccessor> result = scan1.consume();
                assertEquals(4, result.size());
                assertEquals(RawString.of("mykey"), result.get(3).get(0));
                assertEquals(RawString.of("mykey2"), result.get(2).get(0));
                assertEquals(RawString.of("mykey3"), result.get(1).get(0));
                assertEquals(RawString.of("mykey4"), result.get(0).get(0));
            }
        }
        assertEquals(1, executeUpdate(manager, "INSERT INTO tblspace1.tsql(k1,n1) values(?,?)", Arrays.asList("mykey5", Integer.valueOf(3))).getUpdateCount());
        {
            TranslatedQuery translate1 = manager.getPlanner().translate(TableSpace.DEFAULT, "SELECT k1 FROM tblspace1.tsql order by n1, k1", Collections.emptyList(), true, true, false, -1);
            ScanStatement scan = translate1.plan.mainStatement.unwrap(ScanStatement.class);
            try (DataScanner scan1 = manager.scan(scan, translate1.context, TransactionContext.NO_TRANSACTION)) {
                List<DataAccessor> result = scan1.consume();
                assertEquals(5, result.size());
                assertEquals(RawString.of("mykey"), result.get(0).get(0));
                assertEquals(RawString.of("mykey2"), result.get(1).get(0));
                assertEquals(RawString.of("mykey5"), result.get(2).get(0));
                assertEquals(RawString.of("mykey3"), result.get(3).get(0));
                // NULLS LAST
                assertEquals(RawString.of("mykey4"), result.get(4).get(0));
            }
        }
        {
            TranslatedQuery translate1 = manager.getPlanner().translate(TableSpace.DEFAULT, "SELECT k1 FROM tblspace1.tsql order by n1, k1 desc", Collections.emptyList(), true, true, false, -1);
            ScanStatement scan = translate1.plan.mainStatement.unwrap(ScanStatement.class);
            try (DataScanner scan1 = manager.scan(scan, translate1.context, TransactionContext.NO_TRANSACTION)) {
                List<DataAccessor> result = scan1.consume();
                assertEquals(5, result.size());
                assertEquals(RawString.of("mykey"), result.get(0).get(0));
                assertEquals(RawString.of("mykey2"), result.get(1).get(0));
                assertEquals(RawString.of("mykey5"), result.get(2).get(0));
                assertEquals(RawString.of("mykey3"), result.get(3).get(0));
                // NULLS LAST
                assertEquals(RawString.of("mykey4"), result.get(4).get(0));
            }
        }
        {
            TranslatedQuery translate1 = manager.getPlanner().translate(TableSpace.DEFAULT, "SELECT k1 FROM tblspace1.tsql order by n1, k1 desc limit 2", Collections.emptyList(), true, true, false, -1);
            ScanStatement scan = translate1.plan.mainStatement.unwrap(ScanStatement.class);
            try (DataScanner scan1 = manager.scan(scan, translate1.context, TransactionContext.NO_TRANSACTION)) {
                List<DataAccessor> result = scan1.consume();
                assertEquals(2, result.size());
                assertEquals(RawString.of("mykey"), result.get(0).get(0));
                assertEquals(RawString.of("mykey2"), result.get(1).get(0));
            }
        }
        {
            TranslatedQuery translate1 = manager.getPlanner().translate(TableSpace.DEFAULT, "SELECT k1 FROM tblspace1.tsql order by n1 asc limit 2", Collections.emptyList(), true, true, false, -1);
            ScanStatement scan = translate1.plan.mainStatement.unwrap(ScanStatement.class);
            try (DataScanner scan1 = manager.scan(scan, translate1.context, TransactionContext.NO_TRANSACTION)) {
                List<DataAccessor> result = scan1.consume();
                assertEquals(2, result.size());
                assertEquals(RawString.of("mykey"), result.get(0).get(0));
                assertEquals(RawString.of("mykey2"), result.get(1).get(0));
            }
        }
        {
            TranslatedQuery translate1 = manager.getPlanner().translate(TableSpace.DEFAULT, "SELECT k1 FROM tblspace1.tsql order by n1 desc limit 3", Collections.emptyList(), true, true, false, -1);
            ScanStatement scan = translate1.plan.mainStatement.unwrap(ScanStatement.class);
            try (DataScanner scan1 = manager.scan(scan, translate1.context, TransactionContext.NO_TRANSACTION)) {
                List<DataAccessor> result = scan1.consume();
                assertEquals(3, result.size());
                assertEquals(RawString.of("mykey4"), result.get(0).get(0));
                assertEquals(RawString.of("mykey3"), result.get(1).get(0));
                assertEquals(RawString.of("mykey5"), result.get(2).get(0));
            }
        }
    }
}

Example 5 with TranslatedQuery

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

the class RestartTest 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 = new DBManager("localhost", new FileMetadataStorageManager(metadataPath), new FileDataStorageManager(dataPath), new FileCommitLogManager(logsPath, 64 * 1024 * 1024), 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.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 = new DBManager("localhost", new FileMetadataStorageManager(metadataPath), new FileDataStorageManager(dataPath), new FileCommitLogManager(logsPath, 64 * 1024 * 1024), tmoDir, null)) {
        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());
        }
    }
}