use of herddb.model.commands.CreateIndexStatement in project herddb by diennea.
the class SecondaryNonUniqueIndexAccessSuite method createIndexOnTableWithData.
@Test
public void createIndexOnTableWithData() 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("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("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());
// 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 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());
}
}
}
use of herddb.model.commands.CreateIndexStatement in project herddb by diennea.
the class SecondaryNonUniqueIndexAccessSuite method createIndexOnEmptyTable.
@Test
public void createIndexOnEmptyTable() 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("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("name", ColumnTypes.STRING).build();
// create index, initially it will be empty
CreateIndexStatement st3 = new CreateIndexStatement(index);
manager.executeStatement(st3, StatementEvaluationContext.DEFAULT_EVALUATION_CONTEXT(), TransactionContext.NO_TRANSACTION);
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());
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 = ((ScanResult) manager.executePlan(translated.plan, translated.context, TransactionContext.NO_TRANSACTION)).dataScanner) {
assertEquals(3, scan1.consume().size());
}
}
}
use of herddb.model.commands.CreateIndexStatement in project herddb by diennea.
the class IndexScanRangeTest method secondaryIndexRangeScan.
private void secondaryIndexRangeScan(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>=1", Collections.emptyList(), true, true, false, -1);
ScanStatement scan = translated.plan.mainStatement.unwrap(ScanStatement.class);
System.out.println("indexOperation:" + scan.getPredicate().getIndexOperation());
assertTrue(scan.getPredicate().getIndexOperation() instanceof SecondaryIndexRangeScan);
try (DataScanner scan1 = manager.scan(scan, translated.context, TransactionContext.NO_TRANSACTION)) {
assertEquals(5, 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);
System.out.println("indexOperation:" + scan.getPredicate().getIndexOperation());
assertTrue(scan.getPredicate().getIndexOperation() instanceof SecondaryIndexRangeScan);
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", Collections.emptyList(), true, true, false, -1);
ScanStatement scan = translated.plan.mainStatement.unwrap(ScanStatement.class);
System.out.println("indexOperation:" + scan.getPredicate().getIndexOperation());
assertTrue(scan.getPredicate().getIndexOperation() instanceof SecondaryIndexRangeScan);
try (DataScanner scan1 = manager.scan(scan, translated.context, TransactionContext.NO_TRANSACTION)) {
assertEquals(4, scan1.consume().size());
}
}
{
TranslatedQuery translated = manager.getPlanner().translate(TableSpace.DEFAULT, "SELECT * FROM tblspace1.t1 WHERE n1>= 1 and n1<=2", Collections.emptyList(), true, true, false, -1);
ScanStatement scan = translated.plan.mainStatement.unwrap(ScanStatement.class);
System.out.println("indexOperation:" + scan.getPredicate().getIndexOperation());
assertTrue(scan.getPredicate().getIndexOperation() instanceof SecondaryIndexRangeScan);
try (DataScanner scan1 = manager.scan(scan, translated.context, TransactionContext.NO_TRANSACTION)) {
assertEquals(4, scan1.consume().size());
}
}
{
TranslatedQuery translated = manager.getPlanner().translate(TableSpace.DEFAULT, "SELECT * FROM tblspace1.t1 WHERE n1<= -10 and n1<=2", Collections.emptyList(), true, true, false, -1);
ScanStatement scan = translated.plan.mainStatement.unwrap(ScanStatement.class);
System.out.println("indexOperation:" + scan.getPredicate().getIndexOperation());
assertTrue(scan.getPredicate().getIndexOperation() instanceof SecondaryIndexRangeScan);
try (DataScanner scan1 = manager.scan(scan, translated.context, TransactionContext.NO_TRANSACTION)) {
assertEquals(0, 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);
System.out.println("indexOperation:" + scan.getPredicate().getIndexOperation());
assertTrue(scan.getPredicate().getIndexOperation() instanceof SecondaryIndexRangeScan);
try (DataScanner scan1 = manager.scan(scan, translated.context, TransactionContext.NO_TRANSACTION)) {
assertEquals(4, scan1.consume().size());
}
}
{
TranslatedQuery translated = manager.getPlanner().translate(TableSpace.DEFAULT, "SELECT * FROM tblspace1.t1 WHERE n1<3", Collections.emptyList(), true, true, false, -1);
ScanStatement scan = translated.plan.mainStatement.unwrap(ScanStatement.class);
System.out.println("indexOperation:" + scan.getPredicate().getIndexOperation());
assertTrue(scan.getPredicate().getIndexOperation() instanceof SecondaryIndexRangeScan);
try (DataScanner scan1 = manager.scan(scan, translated.context, TransactionContext.NO_TRANSACTION)) {
assertEquals(4, 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);
System.out.println("indexOperation:" + scan.getPredicate().getIndexOperation());
assertTrue(scan.getPredicate().getIndexOperation() instanceof SecondaryIndexRangeScan);
try (DataScanner scan1 = manager.scan(scan, translated.context, TransactionContext.NO_TRANSACTION)) {
assertEquals(0, scan1.consume().size());
}
}
{
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);
System.out.println("indexOperation:" + scan.getPredicate().getIndexOperation());
assertTrue(scan.getPredicate().getIndexOperation() instanceof SecondaryIndexRangeScan);
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 and n1<3", Collections.emptyList(), true, true, false, -1);
ScanStatement scan = translated.plan.mainStatement.unwrap(ScanStatement.class);
System.out.println("indexOperation:" + scan.getPredicate().getIndexOperation());
assertTrue(scan.getPredicate().getIndexOperation() instanceof SecondaryIndexRangeScan);
try (DataScanner scan1 = manager.scan(scan, translated.context, TransactionContext.NO_TRANSACTION)) {
assertEquals(3, scan1.consume().size());
}
}
{
TranslatedQuery translated = manager.getPlanner().translate(TableSpace.DEFAULT, "SELECT * FROM tblspace1.t1 WHERE name='n1' and n1>=1 and n1<=2", Collections.emptyList(), true, true, false, -1);
ScanStatement scan = translated.plan.mainStatement.unwrap(ScanStatement.class);
System.out.println("indexOperation:" + scan.getPredicate().getIndexOperation());
assertTrue(scan.getPredicate().getIndexOperation() instanceof SecondaryIndexRangeScan);
try (DataScanner scan1 = manager.scan(scan, translated.context, TransactionContext.NO_TRANSACTION)) {
assertEquals(2, scan1.consume().size());
}
}
}
}
use of herddb.model.commands.CreateIndexStatement in project herddb by diennea.
the class TableSpaceManager method executeStatement.
public StatementExecutionResult executeStatement(Statement statement, StatementEvaluationContext context, TransactionContext transactionContext) throws StatementExecutionException {
boolean rollbackOnError = false;
/* Do not autostart transaction on alter table statements */
if (transactionContext.transactionId == TransactionContext.AUTOTRANSACTION_ID && statement.supportsTransactionAutoCreate()) {
StatementExecutionResult newTransaction = beginTransaction();
transactionContext = new TransactionContext(newTransaction.transactionId);
rollbackOnError = true;
}
Transaction transaction = transactions.get(transactionContext.transactionId);
if (transaction != null && !transaction.tableSpace.equals(tableSpaceName)) {
throw new StatementExecutionException("transaction " + transaction.transactionId + " is for tablespace " + transaction.tableSpace + ", not for " + tableSpaceName);
}
if (transactionContext.transactionId > 0 && transaction == null) {
throw new StatementExecutionException("transaction " + transactionContext.transactionId + " not found on tablespace " + tableSpaceName);
}
try {
if (statement instanceof TableAwareStatement) {
return executeTableAwareStatement(statement, transaction, context);
}
if (statement instanceof SQLPlannedOperationStatement) {
return executePlannedOperationStatement(statement, transactionContext, context);
}
if (statement instanceof BeginTransactionStatement) {
if (transaction != null) {
throw new IllegalArgumentException("transaction already started");
}
return beginTransaction();
}
if (statement instanceof RollbackTransactionStatement) {
return rollbackTransaction((RollbackTransactionStatement) statement);
}
if (statement instanceof CommitTransactionStatement) {
return commitTransaction((CommitTransactionStatement) statement);
}
if (statement instanceof CreateTableStatement) {
return createTable((CreateTableStatement) statement, transaction);
}
if (statement instanceof CreateIndexStatement) {
return createIndex((CreateIndexStatement) statement, transaction);
}
if (statement instanceof DropTableStatement) {
return dropTable((DropTableStatement) statement, transaction);
}
if (statement instanceof DropIndexStatement) {
return dropIndex((DropIndexStatement) statement, transaction);
}
if (statement instanceof AlterTableStatement) {
return alterTable((AlterTableStatement) statement, transactionContext);
}
throw new StatementExecutionException("unsupported statement " + statement);
} catch (StatementExecutionException error) {
if (rollbackOnError) {
rollbackTransaction(new RollbackTransactionStatement(tableSpaceName, transactionContext.transactionId));
}
throw error;
}
}
use of herddb.model.commands.CreateIndexStatement in project herddb by diennea.
the class SQLPlanner method buildCreateIndexStatement.
private Statement buildCreateIndexStatement(String defaultTableSpace, CreateIndex s) throws StatementExecutionException {
try {
String tableSpace = s.getTable().getSchemaName();
if (tableSpace == null) {
tableSpace = defaultTableSpace;
}
String tableName = s.getTable().getName();
String indexName = s.getIndex().getName().toLowerCase();
String indexType = convertIndexType(s.getIndex().getType());
herddb.model.Index.Builder builder = herddb.model.Index.builder().name(indexName).uuid(UUID.randomUUID().toString()).type(indexType).table(tableName).tablespace(tableSpace);
AbstractTableManager tableDefinition = manager.getTableSpaceManager(tableSpace).getTableManager(tableName);
if (tableDefinition == null) {
throw new TableDoesNotExistException("no such table " + tableName + " in tablespace " + tableSpace);
}
for (String columnName : s.getIndex().getColumnsNames()) {
columnName = columnName.toLowerCase();
Column column = tableDefinition.getTable().getColumn(columnName);
if (column == null) {
throw new StatementExecutionException("no such column " + columnName + " on table " + tableName + " in tablespace " + tableSpace);
}
builder.column(column.name, column.type);
}
CreateIndexStatement statement = new CreateIndexStatement(builder.build());
return statement;
} catch (IllegalArgumentException err) {
throw new StatementExecutionException("bad index definition: " + err.getMessage(), err);
}
}
Aggregations