io.crate.action.sql.SQLActionException Java Examples

@Test
public void testAlterTableAddObjectColumnToExistingObject() {
    execute("create table t (o object as (x string)) " +
            "clustered into 1 shards " +
            "with (number_of_replicas=0)");
    ensureYellow();
    execute("alter table t add o['y'] int");
    try {
        execute("alter table t add o object as (z string)");
        fail("did not fail for existing column o");
    } catch (SQLActionException e) {
        // column o exists already
        assertThat(e.getMessage(), containsString("The table doc.t already has a column named o"));
    }
    execute("select column_name from information_schema.columns where " +
            "table_name = 't' and table_schema='doc'" +
            "order by column_name asc");
    assertThat(response.rowCount(), is(3L));

    List<String> fqColumnNames = new ArrayList<>();
    for (Object[] row : response.rows()) {
        fqColumnNames.add((String) row[0]);
    }
    assertThat(fqColumnNames, Matchers.contains("o", "o['x']", "o['y']"));
}
 

@Test
public void testMultipleWritesWhenOnePartitionIsReadOnly() {
    execute("create table my_table (par int, content string) " +
            "clustered into 5 shards " +
            "partitioned by (par)");
    execute("insert into my_table (par, content) values " +
            "(1, 'content2'), " +
            "(2, 'content3')");

    ensureGreen();
    execute("alter table my_table partition (par=1) set (\"blocks.write\"=true)");
    try {
        execute("insert into my_table (par, content) values (2, 'content42'), " +
                "(2, 'content42'), " +
                "(1, 'content2'), " +
                "(3, 'content6')");
        fail("expected to throw an \"blocked\" exception");
    } catch (SQLActionException e) {
        assertThat(e.getMessage(), containsString("blocked by: [FORBIDDEN/8/index write (api)];"));
    }
    refresh();
    execute("select * from my_table");
    assertThat(response.rowCount(), Matchers.is(both(greaterThanOrEqualTo(2L)).and(lessThanOrEqualTo(5L))));
    //cleaning up
    execute("alter table my_table partition (par=1) set (\"blocks.write\"=false)");
}
 

@Test
public void testInsertNewColumnTableStrictColumnPolicy() throws Exception {
    execute("create table strict_table (" +
            "  id integer primary key, " +
            "  name string" +
            ") with (column_policy='strict', number_of_replicas=0)");
    ensureYellow();
    execute("insert into strict_table (id, name) values (1, 'Ford')");
    execute("refresh table strict_table");

    execute("select * from strict_table");
    assertThat(response.rowCount(), is(1L));
    assertThat(response.cols(), is(arrayContaining("id", "name")));
    assertThat(response.rows()[0], is(Matchers.<Object>arrayContaining(1, "Ford")));

    expectedException.expect(SQLActionException.class);
    expectedException.expectMessage("Column boo unknown");
    execute("insert into strict_table (id, name, boo) values (2, 'Trillian', true)");
}
 

@Test
public void testInsertPartitionedTablePrimaryKeysDuplicate() {
    execute("create table parted (" +
            "  id int, " +
            "  name string, " +
            "  date timestamp with time zone," +
            "  primary key (id, name)" +
            ") partitioned by (id, name)");
    ensureYellow();
    Long dateValue = System.currentTimeMillis();
    execute("insert into parted (id, name, date) values (?, ?, ?)",
        new Object[]{42, "Zaphod", dateValue});
    assertThat(response.rowCount(), is(1L));
    ensureYellow();
    refresh();

    expectedException.expect(SQLActionException.class);
    expectedException.expectMessage("A document with the same primary key exists already");

    execute("insert into parted (id, name, date) values (?, ?, ?)",
        new Object[]{42, "Zaphod", 0L});
}
 

@Test
public void testAlterTableAddDotExpression() {
    execute("create table t (id int) " +
            "clustered into 1 shards " +
            "with (number_of_replicas=0)");
    ensureYellow();
    expectedException.expect(SQLActionException.class);
    expectedException.expectMessage("\"o.x\" contains a dot");
    execute("alter table t add \"o.x\" int");
}
 

@Test
public void testInsertGeneratedPrimaryKeyValueGiven() throws Exception {
    execute("create table test(col1 integer primary key, col2 as col1 + 3 primary key)");
    ensureYellow();
    execute("insert into test(col1, col2) values (1, 4)");
    refresh();
    execute("select col2 from test");
    assertThat(response.rows()[0][0], is(4));

    // wrong value
    expectedException.expect(SQLActionException.class);
    expectedException.expectMessage("Given value 0 for generated column col2 does not match calculation (col1 + 3) = 4");
    execute("insert into test(col1, col2) values (1, 0)");
}
 

@Test
public void testInsertFromSubQueryMissingPrimaryKeyValues() throws Exception {
    execute("create table source(col1 integer)");
    execute("create table target(col1 integer primary key, col2 integer primary key)");
    ensureYellow();
    execute("insert into source (col1) values (1)");
    refresh();
    expectedException.expect(SQLActionException.class);
    expectedException.expectMessage("Column \"col2\" is required but is missing from the insert statement");
    execute("insert into target (col1) (select col1 from source)");
}
 

@Test
public void testGeneratedColumnAsPrimaryKeyValueEvaluateToNull() throws Exception {
    execute("CREATE TABLE test (col1 TEXT, col2 AS try_cast(col1 AS INT) PRIMARY KEY)");
    expectedException.expect(SQLActionException.class);
    expectedException.expectMessage("Primary key value must not be NULL");
    execute("insert into test (col1) values ('a')");
}
 

@Test
public void testCopyToForbidden() throws Exception {
    execute("create table t1 (id integer) with (number_of_replicas = 0, \"blocks.read\" = true)");

    expectedException.expect(SQLActionException.class);
    expectedException.expectMessage(String.format(Locale.ENGLISH,
        "The relation \"%s.t1\" doesn't support or allow COPY TO operations.", sqlExecutor.getCurrentSchema()));
    execute("copy t1 to DIRECTORY '/tmp/'");
}
 

@Test
public void testDropForbidden() throws Exception {
    execute("create table t1 (id integer) with (number_of_replicas = 0, \"blocks.metadata\" = true)");
    ensureYellow();

    expectedException.expect(SQLActionException.class);
    expectedException.expectMessage(String.format(Locale.ENGLISH,
        "The relation \"%s.t1\" doesn't support or allow DROP operations.", sqlExecutor.getCurrentSchema()));
    execute("drop table t1");
}
 

@Test
public void testOptimizeEmptyPartitionedTable() {
    execute(
        "create table parted (" +
        "   id integer," +
        "   name string," +
        "   date timestamp with time zone" +
        ") partitioned by (date) with (refresh_interval=0)");
    ensureYellow();

    expectedException.expect(SQLActionException.class);
    expectedException.expectMessage(String.format("No partition for table '%s' with ident '04130' exists", getFqn("parted")));
    execute("optimize table parted partition(date=0)");
}
 

@Test
public void testClosePreventsAlter() throws Exception {
    expectedException.expect(SQLActionException.class);
    expectedException.expectMessage(String.format("The relation \"%s\" doesn't support or allow ALTER operations, " +
                                    "as it is currently closed", getFqn("t")));
    execute("alter table t add column x string");
}
 

@Test
public void testUpdateForbidden() throws Exception {
    execute("create table t1 (id integer) with (number_of_replicas = 0, \"blocks.write\" = true, \"blocks.read\" = true)");
    ensureYellow();

    expectedException.expect(SQLActionException.class);
    expectedException.expectMessage(String.format(Locale.ENGLISH,
        "The relation \"%s.t1\" doesn't support or allow UPDATE operations.", sqlExecutor.getCurrentSchema()));
    execute("update t1 set id = 2");
}
 

@Test
public void testInsertObjectField() throws Exception {
    expectedException.expect(SQLActionException.class);

    setUpObjectTable();
    execute("insert into test12 (object_field['size']) values (127)");

}
 

@Test
public void selectWhereNonExistingColumnMatchFunction() throws Exception {
    nonExistingColumnSetup();

    expectedException.expect(SQLActionException.class);
    expectedException.expectMessage("Can only use MATCH on columns of type STRING or GEO_SHAPE, not on 'undefined'");

    execute("select * from quotes where match(o['something'], 'bla')");
}
 

@Test
public void testNestedReadForbidden() throws Exception {
    execute("create table t1 (id integer) with (number_of_replicas = 0, \"blocks.read\" = true)");
    execute("create table t2 (id integer) with (number_of_replicas = 0)");
    ensureYellow();
    execute("insert into t1 (id) values (1)");

    expectedException.expect(SQLActionException.class);
    expectedException.expectMessage(String.format(Locale.ENGLISH,
        "The relation \"%s.t1\" doesn't support or allow READ operations.", sqlExecutor.getCurrentSchema()));
    execute("insert into t2 (id) (select id from t1)");
}
 

@Test
public void testCreateTableAlreadyExistsException() throws Exception {
    execute("create table test (col1 integer primary key, col2 string)");
    ensureYellow();

    expectedException.expect(SQLActionException.class);
    expectedException.expectMessage("Relation 'doc.test' already exists.");
    execute("create table test (col1 integer primary key, col2 string)");
}
 

@Test
public void testInsertNullTargetForNotNullGeneratedColumn() {
    execute("create table generated_column (" +
            " id int primary key," +
            " ts timestamp with time zone," +
            " gen_col as extract(year from ts) not null" +
            ") with (number_of_replicas=0)");
    ensureYellow();

    expectedException.expect(SQLActionException.class);
    expectedException.expectMessage("\"gen_col\" must not be null");
    execute("insert into generated_column (id, gen_col) values (1, null)");
}
 

@Test
public void testDropSnapshotUnknownRepository() throws Exception {
    String repository = "unknown_repo";
    String snapshot = "unknown_snap";
    expectedException.expect(SQLActionException.class);
    expectedException.expectMessage(String.format(Locale.ENGLISH, "Repository '%s' unknown", repository));
    execute("drop snapshot " + repository + "." + snapshot);
}
 

@Test
public void testInsertWithClusteredByWithoutValue() throws Exception {
    execute("create table quotes (id integer, quote string) clustered by(id) " +
            "with (number_of_replicas=0)");
    ensureYellow();

    expectedException.expect(SQLActionException.class);
    expectedException.expectMessage("Clustered by value is required but is missing from the insert statement");
    execute("insert into quotes (quote) values(?)",
        new Object[]{"I'd far rather be happy than right any day."});
}
 

@Test
public void testAlterTableAddDotExpressionInSubscript() {
    execute("create table t (id int) " +
            "clustered into 1 shards " +
            "with (number_of_replicas=0)");
    ensureYellow();
    expectedException.expect(SQLActionException.class);
    expectedException.expectMessage("\"o['x.y']\" contains a dot");

    execute("alter table t add \"o['x.y']\" int");
}
 

@Test
public void testInsertWithPKMissingOnInsert() throws Exception {
    this.setup.createTestTableWithPrimaryKey();

    Object[] args = new Object[]{
        "In the beginning the Universe was created.\n" +
        "This has made a lot of people very angry and been widely regarded as a bad move."
    };

    expectedException.expect(SQLActionException.class);
    expectedException.expectMessage("Column \"pk_col\" is required but is missing from the insert statement");

    execute("insert into test (message) values (?)", args);
}
 

@Test
public void testScalarEvaluatesInErrorOnSysShards() throws Exception {
    // we need at least 1 shard, otherwise the table is empty and no evaluation occurs
    execute("create table t1 (id integer) clustered into 1 shards with (number_of_replicas=0)");
    ensureYellow();

    expectedException.expect(SQLActionException.class);
    expectedException.expectMessage(" / by zero");
    execute("select 1/0 from sys.shards");
}
 

@Test
public void testCreateSnapshotConcreteTableForbidden() throws Exception {
    execute("create table t1 (id integer) with (number_of_replicas = 0, \"blocks.read\" = true)");
    ensureYellow();
    execute("CREATE REPOSITORY repo TYPE \"fs\" with (location=?, compress=True)",
        new Object[]{TEMPORARY_FOLDER.newFolder().getAbsolutePath()});

    expectedException.expect(SQLActionException.class);
    expectedException.expectMessage(String.format(Locale.ENGLISH,
        "The relation \"%s.t1\" doesn't support or allow CREATE SNAPSHOT operations.", sqlExecutor.getCurrentSchema()));
    execute("CREATE SNAPSHOT repo.snap TABLE t1 WITH (wait_for_completion=true)");
}
 

@Test
public void testStrictPartitionedTableInsert() throws Exception {
    execute("create table numbers (" +
            "  num int, " +
            "  odd boolean," +
            "  prime boolean" +
            ") partitioned by (odd) with (column_policy='strict', number_of_replicas=0)");
    ensureYellow();

    GetIndexTemplatesResponse response = client().admin().indices()
        .prepareGetTemplates(PartitionName.templateName(sqlExecutor.getCurrentSchema(), "numbers"))
        .execute().actionGet();
    assertThat(response.getIndexTemplates().size(), is(1));
    IndexTemplateMetaData template = response.getIndexTemplates().get(0);
    CompressedXContent mappingStr = template.mappings().get(Constants.DEFAULT_MAPPING_TYPE);
    assertThat(mappingStr, is(notNullValue()));
    Tuple<XContentType, Map<String, Object>> typeAndMap =
        XContentHelper.convertToMap(mappingStr.compressedReference(), false, XContentType.JSON);
    @SuppressWarnings("unchecked")
    Map<String, Object> mapping = (Map<String, Object>) typeAndMap.v2().get(Constants.DEFAULT_MAPPING_TYPE);
    assertThat(decodeMappingValue(mapping.get("dynamic")), is(ColumnPolicy.STRICT));

    execute("insert into numbers (num, odd, prime) values (?, ?, ?)",
        new Object[]{6, true, false});
    execute("refresh table numbers");

    Map<String, Object> sourceMap = getSourceMap(
        new PartitionName(new RelationName("doc", "numbers"), Arrays.asList("true")).asIndexName());
    assertThat(decodeMappingValue(sourceMap.get("dynamic")), is(ColumnPolicy.STRICT));

    expectedException.expect(SQLActionException.class);
    expectedException.expectMessage("Column perfect unknown");
    execute("insert into numbers (num, odd, prime, perfect) values (?, ?, ?, ?)",
        new Object[]{28, true, false, true});
}
 

@Test
public void testInsertBadIPAddress() throws Exception {
    execute("create table t (i ip) with (number_of_replicas=0)");
    ensureYellow();
    expectedException.expect(SQLActionException.class);
    expectedException.expectMessage("Cannot cast `'192.168.1.500'` of type `text` to type `ip`");
    execute("insert into t (i) values ('192.168.1.2'), ('192.168.1.3'),('192.168.1.500')");
}
 

@Test
public void updateToStrictObject() throws Exception {
    expectedException.expect(SQLActionException.class);
    expectedException.expectMessage("Column author['name']['middle_name'] unknown");

    execute("update ot set author['name']['middle_name']='Noel' " +
            "where author['name']['first_name']='Douglas' and author['name']['last_name']='Adams'");
}
 

@Test
public void testAddColumnToStrictObject() throws Exception {
    Map<String, Object> authorMap = Map.of(
        "name", Map.of(
            "first_name", "Douglas",
            "middle_name", "Noel",
            "last_name", "Adams"),
        "age", 49);
    expectedException.expect(SQLActionException.class);
    expectedException.expectMessage(
        containsString("dynamic introduction of [middle_name] within [author.name] is not allowed"));
    execute(
        "insert into ot (title, author) values (?, ?)",
        new Object[]{"Life, the Universe and Everything", authorMap});
}
 

@Override
public <T> T execute(CrateAction action, CrateActionResponseHandler<T> handler) throws DataAccessException {
   	
   	notNull(action, "An implementation of CrateAction is required");
   	notNull(handler, "An implementation of CrateActionResponseHandler<T> is required");
   	
   	try {
   		SQLRequest request = action.getSQLRequest();
   		logger.debug(SQL_STATEMENT, request.stmt(), Arrays.toString(request.args()));
   		return (T)handler.handle(client.sql(request).actionGet());
   	}catch(SQLActionException e) {
   		throw tryConvertingRuntimeException(e);
	}
   }
 

@Test
public void testAlterClusterRerouteRetryFailedPrivileges() {
    executeAsSuperuser("alter cluster reroute retry failed");
    assertThat(response.rowCount(), is (0L));

    expectedException.expect(SQLActionException.class);
    expectedException.expectMessage(containsString("UnauthorizedException: User \"normal\" is not authorized to execute the statement"));
    executeAsNormalUser("alter cluster reroute retry failed");
}