Support for subqueries

[Glamdring]

I would be cool if migration scripts supported some sort of subqueries. For cases where you don't know the ID(s) of some records, but you want to perform an update. That update would be slow, but it can be on tables with a limited number of records and so the penalty would not be that big.

I implemented something to support subqueries by extending your classes, but since DbMigration is package-private, I had to use reflection so it's a bit ugly. A query in my implementation would look like this:

 UPDATE some_table SET some_column=1000 WHERE id=${{SELECT id FROM some_table WHERE code='non-primary-key-value' ALLOW FILTERING}};

Then I have something like that:

private static final String SUBSTITUTE_REGEX = "\\$\\{\\{(.+)\\}\\}";
private static final Pattern SUBSTITUTE_PATTERN = Pattern.compile(SUBSTITUTE_REGEX);
void transformMigrations(List migrations) {
    try {
        // DbMigration is not public, so we use type-unsafe list + reflection
        Field field = ReflectionUtils.findField(Class.forName("org.cognitor.cassandra.migration.DbMigration"), "migrationScript");
        ReflectionUtils.makeAccessible(field);
        for (Object migration : migrations) {
            String script = (String) field.get(migration);
            if (!script.contains("${{")) {
                continue;
            }
            String[] lines = script.split(";");
            StringBuilder sb = new StringBuilder();
            for (String line : lines) {
                Matcher matcher = SUBSTITUTE_PATTERN.matcher(line);
                if (matcher.find()) {
                    logger.info("Transforming query {}", line);
                    String subquery = matcher.group(1);
                    List<Map<String, Object>> result = cqlOperations.query(subquery, new ColumnMapRowMapper());
                    // assuming a single column select
                    for (Map<String, Object> row : result) {
                        sb.append(line.replaceAll(SUBSTITUTE_REGEX, row.values().iterator().next().toString())).append(";");
                    }
                } else {
                    sb.append(line).append(";");
                }
            }
            String transformedMigrationScript = sb.toString();
            logger.info("Transformed migration script {}", transformedMigrationScript);
            ReflectionUtils.setField(field, migration, transformedMigrationScript);
        }
    } catch (Exception ex) {
        throw new RuntimeException(ex);
    }
}

[patka]

Hi,

this looks interesting indeed. This would also be a good usecase to implement the Java based migrations. I will have a look as soon as possible. I had other things to do in the last weeks but I will start to work on the issues again in the next couple of days and then have a closer look on your changes. Thanks a lot for putting the effort!

Cheers Patrick