Generate a script to create a table from the entity definition

Question

Is there a way to generate the statement CREATE TABLE from an entity definition? I know it is possible using Achilles but I want to use the regular Cassandra entity.

The target is getting the following script from the entity class below.

Statement

CREATE TABLE user (userId uuid PRIMARY KEY, name text);

Entity

@Table(keyspace = "ks", name = "users",
       readConsistency = "QUORUM",
       writeConsistency = "QUORUM",
       caseSensitiveKeyspace = false,
       caseSensitiveTable = false)
public static class User {
    @PartitionKey
    private UUID userId;
    private String name;
    // ... constructors / getters / setters
}

Answer 1

Create a class named Utility with package name com.datastax.driver.mapping to access some utils method from that package.

package com.datastax.driver.mapping;

import com.datastax.driver.core.*;
import com.datastax.driver.core.utils.UUIDs;
import com.datastax.driver.mapping.annotations.ClusteringColumn;
import com.datastax.driver.mapping.annotations.Column;
import com.datastax.driver.mapping.annotations.PartitionKey;
import com.datastax.driver.mapping.annotations.Table;

import java.net.InetAddress;
import java.nio.ByteBuffer;
import java.util.*;

/**
 * Created by Ashraful Islam
 */
public class Utility {

    private static final Map<Class, DataType.Name> BUILT_IN_CODECS_MAP = new HashMap<>();

    static {
        BUILT_IN_CODECS_MAP.put(Long.class, DataType.Name.BIGINT);
        BUILT_IN_CODECS_MAP.put(Boolean.class, DataType.Name.BOOLEAN);
        BUILT_IN_CODECS_MAP.put(Double.class, DataType.Name.DOUBLE);
        BUILT_IN_CODECS_MAP.put(Float.class, DataType.Name.FLOAT);
        BUILT_IN_CODECS_MAP.put(Integer.class, DataType.Name.INT);
        BUILT_IN_CODECS_MAP.put(Short.class, DataType.Name.SMALLINT);
        BUILT_IN_CODECS_MAP.put(Byte.class, DataType.Name.TINYINT);
        BUILT_IN_CODECS_MAP.put(long.class, DataType.Name.BIGINT);
        BUILT_IN_CODECS_MAP.put(boolean.class, DataType.Name.BOOLEAN);
        BUILT_IN_CODECS_MAP.put(double.class, DataType.Name.DOUBLE);
        BUILT_IN_CODECS_MAP.put(float.class, DataType.Name.FLOAT);
        BUILT_IN_CODECS_MAP.put(int.class, DataType.Name.INT);
        BUILT_IN_CODECS_MAP.put(short.class, DataType.Name.SMALLINT);
        BUILT_IN_CODECS_MAP.put(byte.class, DataType.Name.TINYINT);
        BUILT_IN_CODECS_MAP.put(ByteBuffer.class, DataType.Name.BLOB);
        BUILT_IN_CODECS_MAP.put(InetAddress.class, DataType.Name.INET);
        BUILT_IN_CODECS_MAP.put(String.class, DataType.Name.TEXT);
        BUILT_IN_CODECS_MAP.put(Date.class, DataType.Name.TIMESTAMP);
        BUILT_IN_CODECS_MAP.put(UUID.class, DataType.Name.UUID);
        BUILT_IN_CODECS_MAP.put(LocalDate.class, DataType.Name.DATE);
        BUILT_IN_CODECS_MAP.put(Duration.class, DataType.Name.DURATION);
    }

    private static final Comparator<MappedProperty<?>> POSITION_COMPARATOR = new Comparator<MappedProperty<?>>() {
        @Override
        public int compare(MappedProperty<?> o1, MappedProperty<?> o2) {
            return o1.getPosition() - o2.getPosition();
        }
    };

    public static String convertEntityToSchema(Class<?> entityClass) {
        Table table = AnnotationChecks.getTypeAnnotation(Table.class, entityClass);

        String ksName = table.caseSensitiveKeyspace() ? Metadata.quote(table.keyspace()) : table.keyspace().toLowerCase();
        String tableName = table.caseSensitiveTable() ? Metadata.quote(table.name()) : table.name().toLowerCase();

        List<MappedProperty<?>> pks = new ArrayList<>();
        List<MappedProperty<?>> ccs = new ArrayList<>();
        List<MappedProperty<?>> rgs = new ArrayList<>();

        Set<? extends MappedProperty<?>> properties = MappingConfiguration.builder().build().getPropertyMapper().mapTable(entityClass);

        for (MappedProperty<?> mappedProperty : properties) {
            if (mappedProperty.isComputed())
                continue; //Skip Computed
            if (mappedProperty.isPartitionKey())
                pks.add(mappedProperty);
            else if (mappedProperty.isClusteringColumn())
                ccs.add(mappedProperty);
            else
                rgs.add(mappedProperty);
        }

        if (pks.isEmpty()) {
            throw new IllegalArgumentException("No Partition Key define");
        }

        Collections.sort(pks, POSITION_COMPARATOR);
        Collections.sort(ccs, POSITION_COMPARATOR);

        StringBuilder query = new StringBuilder("CREATE TABLE ");
        if (!ksName.isEmpty()) {
            query.append(ksName).append('.');
        }
        query.append(tableName).append('(').append(toSchema(pks));
        if (!ccs.isEmpty()) {
            query.append(',').append(toSchema(ccs));
        }
        if (!rgs.isEmpty()) {
            query.append(',').append(toSchema(rgs));
        }
        query.append(',').append("PRIMARY KEY(");
        query.append('(').append(join(pks, ",")).append(')');
        if (!ccs.isEmpty()) {
            query.append(',').append(join(ccs, ","));
        }
        query.append(')').append(");");
        return query.toString();
    }

    private static String toSchema(List<MappedProperty<?>> list) {
        StringBuilder sb = new StringBuilder();
        if (!list.isEmpty()) {
            MappedProperty<?> first = list.get(0);
            sb.append(first.getMappedName()).append(' ').append(BUILT_IN_CODECS_MAP.get(first.getPropertyType().getRawType()));
            for (int i = 1; i < list.size(); i++) {
                MappedProperty<?> field = list.get(i);
                sb.append(',').append(field.getMappedName()).append(' ').append(BUILT_IN_CODECS_MAP.get(field.getPropertyType().getRawType()));
            }
        }
        return sb.toString();
    }

    private static String join(List<MappedProperty<?>> list, String separator) {
        StringBuilder sb = new StringBuilder();
        if (!list.isEmpty()) {
            sb.append(list.get(0).getMappedName());
            for (int i = 1; i < list.size(); i++) {
                sb.append(separator).append(list.get(i).getMappedName());
            }
        }
        return sb.toString();
    }
}

How to use it ?

System.out.println(convertEntityToSchema(User.class));

Output :

CREATE TABLE ks.users(userid uuid,name text,PRIMARY KEY((userid)));

Limitation :

• UDT, collection not supported
• Only support and distinguish these data type long,boolean,double,float,int,short,byte,ByteBuffer,InetAddress,String,Date,UUID,LocalDate,Duration

Answer 2

From the answer of Ashraful Islam, I have made a functional version in case someone is interested (@Ashraful Islam please feel free to add it to your answer if you prefer). I also have added the support to ZonedDateTime following the recommendations of Datastax to use a type tuple<timestamp,varchar> (see their documentation).

import com.datastax.driver.core.*;
import com.datastax.driver.mapping.MappedProperty;
import com.datastax.driver.mapping.MappingConfiguration;
import com.datastax.driver.mapping.annotations.Table;
import com.google.common.collect.ImmutableMap;

import java.net.InetAddress;
import java.nio.ByteBuffer;
import java.time.ZonedDateTime;
import java.util.*;
import java.util.function.Predicate;
import java.util.stream.Collectors;

/**
 * Inspired by Ashraful Islam
 * https://stackoverflow.com/questions/44950245/generate-a-script-to-create-a-table-from-the-entity-definition/45039182#45039182
 */
public class CassandraScriptGeneratorFromEntities {

    private static final Map<Class, DataType> BUILT_IN_CODECS_MAP = ImmutableMap.<Class, DataType>builder()
        .put(Long.class, DataType.bigint())
        .put(Boolean.class, DataType.cboolean())
        .put(Double.class, DataType.cdouble())
        .put(Float.class, DataType.cfloat())
        .put(Integer.class, DataType.cint())
        .put(Short.class, DataType.smallint())
        .put(Byte.class, DataType.tinyint())
        .put(long.class, DataType.bigint())
        .put(boolean.class, DataType.cboolean())
        .put(double.class, DataType.cdouble())
        .put(float.class, DataType.cfloat())
        .put(int.class, DataType.cint())
        .put(short.class, DataType.smallint())
        .put(byte.class, DataType.tinyint())
        .put(ByteBuffer.class, DataType.blob())
        .put(InetAddress.class, DataType.inet())
        .put(String.class, DataType.text())
        .put(Date.class, DataType.timestamp())
        .put(UUID.class, DataType.uuid())
        .put(LocalDate.class, DataType.date())
        .put(Duration.class, DataType.duration())
        .put(ZonedDateTime.class, TupleType.of(ProtocolVersion.NEWEST_SUPPORTED, CodecRegistry.DEFAULT_INSTANCE, DataType.timestamp(), DataType.text()))
        .build();
    private static final Predicate<List<?>> IS_NOT_EMPTY = ((Predicate<List<?>>) List::isEmpty).negate();


    public static StringBuilder convertEntityToSchema(final Class<?> entityClass, final String defaultKeyspace, final long ttl) {
    final Table table = Objects.requireNonNull(entityClass.getAnnotation(Table.class), () -> "The given entity " + entityClass + " is not annotated with @Table");
    final String keyspace = Optional.of(table.keyspace())
            .filter(((Predicate<String>) String::isEmpty).negate())
            .orElse(defaultKeyspace);
    final String ksName = table.caseSensitiveKeyspace() ? Metadata.quote(keyspace) : keyspace.toLowerCase(Locale.ROOT);
    final String tableName = table.caseSensitiveTable() ? Metadata.quote(table.name()) : table.name().toLowerCase(Locale.ROOT);

    final Set<? extends MappedProperty<?>> properties = MappingConfiguration.builder().build().getPropertyMapper().mapTable(entityClass);

    final List<? extends MappedProperty<?>> partitionKeys = Optional.of(
            properties.stream()
                    .filter(((Predicate<MappedProperty<?>>) MappedProperty::isComputed).negate())
                    .filter(MappedProperty::isPartitionKey)
                    .sorted(Comparator.comparingInt(MappedProperty::getPosition))
                    .collect(Collectors.toList())
    ).filter(IS_NOT_EMPTY).orElseThrow(() -> new IllegalArgumentException("No Partition Key define in the given entity"));

    final List<MappedProperty<?>> clusteringColumns = properties.stream()
            .filter(((Predicate<MappedProperty<?>>) MappedProperty::isComputed).negate())
            .filter(MappedProperty::isClusteringColumn)
            .sorted(Comparator.comparingInt(MappedProperty::getPosition))
            .collect(Collectors.toList());

    final List<MappedProperty<?>> otherColumns = properties.stream()
            .filter(((Predicate<MappedProperty<?>>) MappedProperty::isComputed).negate())
            .filter(((Predicate<MappedProperty<?>>) MappedProperty::isPartitionKey).negate())
            .filter(((Predicate<MappedProperty<?>>) MappedProperty::isClusteringColumn).negate())
            .sorted(Comparator.comparing(MappedProperty::getPropertyName))
            .collect(Collectors.toList());

    final StringBuilder query = new StringBuilder("CREATE TABLE IF NOT EXISTS ");

    Optional.of(ksName).filter(((Predicate<String>) String::isEmpty).negate()).ifPresent(ks -> query.append(ks).append('.'));

    query.append(tableName).append("(\n").append(toSchema(partitionKeys));

    Optional.of(clusteringColumns).filter(IS_NOT_EMPTY).ifPresent(list -> query.append(",\n").append(toSchema(list)));
    Optional.of(otherColumns).filter(IS_NOT_EMPTY).ifPresent(list -> query.append(",\n").append(toSchema(list)));

    query.append(',').append("\nPRIMARY KEY(");
    query.append('(').append(join(partitionKeys)).append(')');

    Optional.of(clusteringColumns).filter(IS_NOT_EMPTY).ifPresent(list -> query.append(", ").append(join(list)));
    query.append(')').append(") with default_time_to_live = ").append(ttl);

    return query;
}

    private static String toSchema(final List<? extends MappedProperty<?>> list) {
    return list.stream()
            .map(property -> property.getMappedName() + ' ' + BUILT_IN_CODECS_MAP.getOrDefault(property.getPropertyType().getRawType(), DataType.text()))
            .collect(Collectors.joining(",\n"));
  }

private static String join(final List<? extends MappedProperty<?>> list) {
    return list.stream().map(MappedProperty::getMappedName).collect(Collectors.joining(", "));
  }