Table Facet :: Foxglove

Overview

Inspired by selector of Instancing, {floxglove-link} provides several ways to specify the mapping between columns and generators for TableFacet.

JdbcTableFacet.Builder is the entry point to define value generators on columns of a table.

Packages:

guru.mikelue.foxglove.jdbc - The main package providing JdbcDataGenerator and JdbcTableFacet.

Code convention:

gen() - an instance of InstancioGenApi.

Versatile example of builder

var randomNumber = gen().ints().range(1000, 9999);

var setting = new DataSetting()
    // Generates past date-time or JDBCType of "TIMESTAMP_WITH_TIMEZONE
    .givenType(JDBCType.TIMESTAMP_WITH_TIMEZONE)
        .useSupplier(
            gen().temporal().zonedDateTime()
                .past()
        )
    // Customizes text pattern for any column with "model" in its name
    .columnMatcher(
        columnMeta -> columnMeta.name().contains("model")
    )
        .useSupplier(gen().text().pattern("Model-#C#C#d#d"));

var facet = JdbcTableFacet.builder(TABLE_CAR)
    // Uses the setting on this table
    .withSetting(setting)
    .numberOfRows(RANDOM_SIZE)
    // Round robin among several brands
    .column("cr_brand")
        .roundRobin("Toyota", "Honda", "Ford", "BMW", "Audi")
    // Generates year between 2015 and 2025
    .column("cr_year")
        .useSpec(() -> gen().shorts().range((short)2015, (short)2025))
    // Random choice for number of seats
    .column("cr_seats")
        .useSpec(() -> gen().oneOf(2, 4, 5, 7))
    // Color with nullable property get nullable values
    .<String>column("cr_color")
        .decideSupplier(columnMeta -> {
            var colorGenerator = gen().oneOf("Red", "Blue", "Green");

            if (columnMeta.properties().contains(Property.NULLABLE)) {
                return colorGenerator.nullable();
            }

            return colorGenerator;
        })
    // Fixed value for status column
    .column("cr_status")
        .fixed(null)
    // Includes these two columns by auto-generating their values
    .includeColumns("cr_license_plate", "cr_daily_rate", "cr_created_at", "cr_model")
    // Alter the values for "cr_license_plate" and "cr_daily_rate"
    .onTupleGenerated(tuple -> {
        tuple.setValue(
            "cr_license_plate",
            tuple.getValue("cr_brand") + "-" + randomNumber.get()
        );

        var dailyRate = switch (tuple.<String>getValue("cr_brand")) {
            case "Toyota", "Honda" -> 0.01;
            case "Ford" -> 0.02;
            default -> 0.03;
        };
        tuple.setValue("cr_daily_rate", dailyRate);
    })
    .build();

Value generators

The way to choose which columns to be applied:

Use Builder.column(String) to specify a column by name and set up value generator.
By DataSetting, you can use java.sql.JDBCType, type name, or ColumnMatcher(a Predicate for ColumnMeta) for choosing columns.

Note	You could use `DataGenerator.withSetting(DataSetting)` that change the way to choose Supplier automatically.

See Settings for more details about DataSetting.

A value generator can be:

An instance of Supplier - by useSupplier(Supplier<T>)
A Function - turns a ColumnMeta to a {Supplier} - by decideSupplier(SupplierDecider<T>)
A Supplier of ValueSpec - by useValueSpec(Supplier<ValueSpce<T>>)
A RowIndexToValue(as Function) - turns row indexes to values - by forRow(RowIndexToValue<T>)

Note	You can use InstancioGenApi (providing bunch of ValueSpecs) to create Supplier for any kind of random data.

By predicate of column

Use Builder.withSetting(DataSetting) to specify a generator by java.sql.JDBCType, type name, or ColumnMatcher(a Predicate for ColumnMeta).

Predicate example

var setting = new DataSetting()
    // Excludes any column with JDBCType of OTHER
    .excludeWhen(columnMeta -> columnMeta.jdbcType() == JDBCType.OTHER)
    // For any column with type of "VARCHAR", using the supplier
    .givenType(JDBCType.VARCHAR)
        .useSupplier(() -> sampleText + suffixSupplier.get())
    // For not-nullable SMALLINT columns, generating values between 2010 and 2020
    .columnMatcher(
        columnMeta -> columnMeta.jdbcType() == JDBCType.SMALLINT &&
            !columnMeta.properties().contains(Property.NULLABLE)
    )
        .useSupplier(gen().shorts().range((short)2010, (short)2020)::get);

var facet = JdbcTableFacet.builder(TABLE_CAR)
    // Uses the setting on the whole table
    .withSetting(setting)
    .numberOfRows(RANDOM_SIZE)
    .build();

Include/Exclude columns

Except specifying columns one by one by Builder.column(String), following methods filter columns to be included/excluded for data generation automatically:

Builder.includeColumns(String…) - Only these columns will to be generated automatically(DataSetting), whatever the properties(e.g. Generated Columns(PostgreSQL)) they have.
- JdbcDataGenerator would throw error if no supplier can be found.
- If you would like to use default value defined by DDL, don’t put the column name in this list.
Builder.excludeColumns(String…) - columns must not to be generated automatically.
- Undefined columns are decided by DataSetting. This logic is differ from includeColumns().
- If you would like to use default value defined by DDL, put the column name in this list.

Above two methods are mutually exclusive.

Include columns

var tableFacet = JdbcTableFacet.builder(TABLE_CAR)
    .numberOfRows(RANDOM_SIZE)
    // Must put all of the columns which are not-nullable, no-default value.
    .includeColumns("cr_license_plate", "cr_brand", "cr_model", "cr_year")
    .build();

Exclude columns

var tableFacet = JdbcTableFacet.builder(TABLE_CAR)
    .numberOfRows(RANDOM_SIZE)
    // These columns would not be generated by Foxglove
    .excludeColumns("cr_seats", "cr_color", "cr_status", "cr_created_at", "cr_updated_at")
    .build();

The number of rows

You can only use one of the following ways to specify the number of rows for a table:

Use Builder.numberOfRows(int) to set a fixed number of rows.
Use Builder.keyOfInt(String) to set a key column, which must be integral type(INT, BIGINT, etc).
Use Builder.cartesianProduct(String) to set up domain for columns(at least one)
Use Builder.referencing(String) to set up referencing to another table’s column.

Note

If you would like to use domain of fixed values but the column is not integral type. There are ways to achieve that:

Define normal column

// Prepares list of colors
var colors = gen().oneOf("Red", "Blue", "Green")
    .list(RANDOM_SIZE);

var facet = JdbcTableFacet.builder(TABLE_CAR)
    // The number of rows is equal to the size of colors list
    .numberOfRows(colors.size())
    // Use round robin for these colors
    .column("cr_color").roundRobin(colors)
    .build();

Cartesian product on only one column

// Prepares list of colors
var colors = gen().oneOf("Red", "Blue", "Green")
    .list(RANDOM_SIZE);

var facet = JdbcTableFacet.builder(TABLE_CAR)
    // Use round robin for these colors
    .cartesianProduct("cr_color").domain(colors)
    .build();

Fix number of rows

Fixed number of rows

var facet = JdbcTableFacet.builder(TABLE_CAR)
    .numberOfRows(RANDOM_SIZE)
    .build();

Key range

When using keyOfInt(String), you can specify the range of key values by:

range(start, end) - With start(inclusive) number and end(exclusive) number.
limit(long, int) - With start(inclusive) number and fixed number of rows.

Key column

// The range of cr_id is between 1000 and 1000 + RANDOM_SIZE - 1
var facet = JdbcTableFacet.builder(TABLE_CAR)
    .keyOfInt("cr_id").limit(1000, RANDOM_SIZE)
    .build();

Note	You can only use one column as key column.

The keyOfInt(String) is not about uniqueness, is about defining the number of rows.
However, the database is likely having uniqueness constraint on the column.

Cartesian product of columns

Cartesian product

// Generates 3 (brands) * 3 (years) = 9 rows
var facet = JdbcTableFacet.builder(TABLE_CAR)
    .cartesianProduct("cr_brand")
        .domain("Toyota", "Honda", "Ford")
    .cartesianProduct("cr_year")
        .domain(2020, 2021, 2022)
    .build();

Cartesian product by referencing

// Any way to generate data for these two tables
var carFacet = JdbcTableFacet.builder(TABLE_CAR)
    .keyOfInt("cr_id").limit(1000, 3)
    .build();
var memberFacet = JdbcTableFacet.builder(TABLE_MEMBER)
    .keyOfInt("mb_id").limit(2000, 5)
    .build();
// :~)

var rentFacet = JdbcTableFacet.builder(TABLE_RENT)
    // References to ids of cars
    .cartesianProduct("rt_cr_id")
        .referencing(carFacet, "cr_id")
    // References to ids of members
    .cartesianProduct("rt_mb_id")
        .referencing(memberFacet, "mb_id")
    .build();

Referencing to another table

Only one column can be used for referencing.

Referencing column

// Prepares parent facet
var carFacet = JdbcTableFacet.builder(TABLE_CAR)
    .numberOfRows(RANDOM_SIZE)
    .build();

// Generates rows referencing to the parent facet.
// The number of features is equal to "RANDOM_SIZE".
var featureFacet = JdbcTableFacet.builder(TABLE_CAR_FEATURE)
    .referencing("cf_cr_id").parent(carFacet, "cr_id")
        // Every car is referenced by one feature.
        .cardinality(1)
    .column("cf_feature_name")
        .fixed("Sunroof")
    .build();

You can use .column(String).from(JdbcTableFacet, String) to uses values of another table’s column as domain.

See Other generators section for using values of another table’s column as domain values.

Sequence number

You can use Int4SequenceSupplier or Int8SequenceSupplier to define sequence number on a column.

Sequence number generator

// Generates license plate like CAR-01, CAR-02, ...
var sequence = new Int4SequenceSupplier(2, 2);
Supplier<String> plateSupplier = () -> String.format(
    "CAR-%02d",
    sequence.getAsInt()
);
var facet = JdbcTableFacet.builder(TABLE_CAR)
    .numberOfRows(RANDOM_SIZE)
    .column("cr_license_plate").useSupplier(plateSupplier)
    .build();

Other kinds of value generator

fix(value) - Use a fixed value on the column.
roundRobin(…) - Round robin provided value on the column.
from(JdbcTableFacet, String) - Copy values from another table’s column.
- You can combine with other generator(e.g. roundRobin(), random()) to control how to pick value from source column.

From values of column of another table

// Prepares parent facet
var carFacet = JdbcTableFacet.builder(TABLE_CAR)
    .numberOfRows(RANDOM_SIZE)
    .column("cr_color")
        .roundRobin("Red", "Blue", "Green")
    .build();

var carArchivedFacet = JdbcTableFacet.builder(TABLE_CAR_ARCHIVED)
    .numberOfRows(10)
    // Has no effect on the number of rows
    .column("ca_color")
        .from(carFacet, "cr_color")
        // Chooses the values of cars' colors randomly
        .random()
    .build();

Change row values

After a row get generated, which is type of TupleAccessor object, you can change the row by Builder.onTupleGenerated(Consumer<RowAccessor>).

Note	Only values of included columns can be changed.

Modifying row

var facet = JdbcTableFacet.builder(TABLE_CAR)
    .numberOfRows(RANDOM_SIZE)
    .column("cr_brand")
        .roundRobin("Toyota", "Honda", "Ford", "BMW", "Audi")
    // Sets the value of "cr_license_plate" by combination of "cr_brand" and a random number
    .onTupleGenerated(tuple -> tuple.setValue(
        "cr_license_plate",
        tuple.getValue("cr_brand") + "-" + randomNumber.get()
    ))
    .build();

References:

Settings - for more details about DataSetting
Default Generators - for default generators