CQL and micronaut-cassandra

[pollend]

I was mentioning to @jameskleeh about adding CQL support to micronaut-data. The CQL language is a subset of sql so I don't think it would be that far of a stretch to add an additional builder to the model. Datastax seems to also have something that works basically the same. Would it be worthwhile adding support or using the existing query builder from datastax. In that case it could be added to the micronaut-cassandra repository.

Are there any potential benefits with using adding it to micronaut-data?

https://docs.datastax.com/en/developer/java-driver/4.4/manual/query_builder/

[graemerocher]

Seems like a worthwhile effort to me although we don’t have the resources in the short term to dedicate to it. Contributions welcome however

[pollend]

I think I might try to implement this in micronaut-data and then try reconcile what I can. patterns and implementations are heavily different between the two. you don't have a single ID column but your key can be a composite of a couple columns. you don't do joins across tables but rather you can "embed" an table into another table and query by the sub instance. you can basically do a select on a containing type of a table where this would be a join in SQL. Not sure if this would be better separated out then be kept with micronaut-data even when CQL has a lot of shared features of SQL.

I think I might try looking more at the Datastax mapping since it would be a lot easier to implement.

I was also looking at what spring did and this seems like it resolves some of my issues with this. I think this also square better then the query builder that datastax has. I think I have enough of the elements to attempt this with some kind of basic prototype.

https://github.com/spring-projects/spring-data-cassandra

[graemerocher]

Sounds good. Let me know if you need any help

[pollend]

so I'm making a bit of progress: https://github.com/pollend/micronaut-data/tree/feature/cassandra-playground

One thing I'm trying to work out is how to handle keyspaces. so do we want to define that in the annotation or have it done through the interface or some combination of both.

@CqlRepository(keyspace='ks')
public interface CqlBookDtoRepository extends BookDtoRepository {
}

Stream<BookDto> findStream(@Keyspace String key,String title);

[graemerocher]

Using an annotation allows customization via configuration. For example you could define @CqlRepository(keyspace='${my.keyspace:ks}')

And then the keyspace becomes configurable via my.keyspace

[pollend]

do you think the same annotations could be used for some of these properties. Would it make more sense to define some custom annotations for these cases. OneToMany would just be an embedded type like this:

CREATE TYPE phone (
    country_code int,
    number text,
)

CREATE TYPE address (
    street text,
    city text,
    zip text,
    phones map<text, phone>
)

CREATE TABLE user (
    name text PRIMARY KEY,
    addresses map<text, frozen<address>>
)

depending on the data it might be better to separate it into another table. if the data is repeated a lot you might want to have a table of addresses where your key is composed of the street name, city and state. The implementation from before won't work on another table because those properties would be separate instances. depending on the size of the data, you probably don't want to store gb's of data in a single entry when at most you only need to query a small part of it.

for a join you would do something like this:

SELECT * FROM address['name'].number = 'XX'

[graemerocher]

One approach (which we will probably use for MongoDB) is to use meta annotations so the DSL is clearer. For example you can do:

@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.TYPE)
@Documented
@MappedEntity  // The @Table annotation is annotated with @MappedEntity
public @interface Table {
      // Every time `@Table(name="..") is used map to `@MappedEntity("..")`
      @AliasFor(annotation=MappedEntity.class, member="value")  
      String name() default "";
}

This approach is nice because then the common functionality can be represented by the common annotation (@MappedEntity) and custom behaviour by the meta annotation.

Same thing with custom annotations like @OneToMany which can simply be a meta annotation for @Relation