Navigation :

SQL generation

This service generates SQL statements for any supported database.

Instantiation

If the connection to the database is available, just call the new SQLGenerator(toolbox) method. If you don’t have any connection to the database, you can create a generator with the following snippet:

ISQLDialect dialect = switch (type) {
        case SNOWFLAKE -> new SnowflakeSQLDialect();
        case MSSQL -> new MSSQLDialect();
        case CLICKHOUSE -> new ClickHouseSQLDialect();
    };
var naming = (new DirectQueryNameMapper()).convertToRemote();
// databaseName is the name of the target database
// schemaName is the name of the target schema or null if the target database doesn't handle the schema feature
// All parameters except dialect could be setup later
var generator = new SQLGenerator(naming, dialect, "databaseName", "schemaName");

SQL Generator Parameters

The generation behavior can be tuned by changing the SQLGeneratorParameters parameters. This object is available by calling its getter: generator.getParameters().

Parameter	Meaning	Sample On	Sample Off
generateDefaultValues	If true, generates the default value statement if provided and compatible.	`"DOUBLE_KEY2" DOUBLE NOT NULL DEFAULT 0.0`	`"DOUBLE_KEY2" DOUBLE NOT NULL`
generateNotNullConstraints	If true, generates the non-null constraint on the fields if required.	`"DOUBLE_KEY2" DOUBLE NOT NULL DEFAULT 0.0`	`"DOUBLE_KEY2" DOUBLE DEFAULT 0.0`
generatePrimaryKeys	If true the primary key constraint will be generated.	`PRIMARY KEY ("STRING_KEY1", "DOUBLE_KEY2")`	Not generated
generateForeignKeys	If true the joins or the references will create a foreign key constraint.	foreign key statement	Not generated
naming	The naming convention applied to the fields and table, also provided on the constructor.	`"DOUBLE_KEY2" DOUBLE NOT NULL DEFAULT 0.0`	`"DoubleKey2" DOUBLE NOT NULL DEFAULT 0.0`
database	The name of the database that can be used on the statements.
schemaName	The name of the schema, if the feature is available, that can be used on the statements.

Features

Table generation

From an IDataTable or an IStoreDescription object, the SQL generator is able to generate a CREATE TABLE statement matching the provided table. The table name and the table fields are converted according to the provided naming convention. The nullable constraint, the default value and the primary key may be generated according to the SQL parameters.

note

The SQL generator disregards whether the target database has vector support. Even if not supported, it will generate a valid vector field if requested.

For instance:

    private static final IStoreDescription TABLE = StartBuilding.store().withStoreName("GeneratorSqlStoreName")
            .withField("StringKey1").asKeyField()
            .withField("DoubleKey2", ILiteralType.DOUBLE).asKeyField()
            .withNullableField("IntValue", ILiteralType.INT).dictionarized()
            .withField("LongValue", ILiteralType.LONG)
            .withField("LocalDateValue", ILiteralType.LOCAL_DATE)
            .withField("DateValue", ILiteralType.DATE)
            .withField("BooleanValue", ILiteralType.BOOLEAN)
            .withField("FloatValue", ILiteralType.FLOAT)
            .withField("DateTimeValue", ILiteralType.ZONED_DATE_TIME)
            .withField("Blob", ILiteralType.OBJECT)
            .withVectorField("StringVector", ILiteralType.STRING)
            .withVectorField("DoubleVector", ILiteralType.DOUBLE).withDefaultValue(new double[] {0.0, 1.0}) // Note that this field is not handled on MS SQL DirectQuery implementation
            .withField("StringValue")
            .build();

will generate:

CREATE TABLE TEST_SCHEMA.GENERATOR_SQL_STORE_NAME(
  [STRING_KEY1] NVARCHAR(4000) NOT NULL DEFAULT 'N/A',
  [DOUBLE_KEY2] FLOAT NOT NULL DEFAULT 0.0,
  [INT_VALUE] INT,
  [LONG_VALUE] BIGINT NOT NULL DEFAULT 0,
  [LOCAL_DATE_VALUE] DATE NOT NULL,
  [DATE_VALUE] DATE NOT NULL,
  [BOOLEAN_VALUE] BIT NOT NULL DEFAULT 0,
  [FLOAT_VALUE] REAL NOT NULL DEFAULT 0.0,
  [DATE_TIME_VALUE] DATETIME2 NOT NULL,
  [BLOB] NVARCHAR(MAX) NOT NULL DEFAULT 'N/A',
  [STRING_VECTOR] NVARCHAR(MAX),
  [DOUBLE_VECTOR] NVARCHAR(MAX) NOT NULL DEFAULT '[0.0, 1.0]',
  [STRING_VALUE] NVARCHAR(4000) NOT NULL DEFAULT 'N/A',
  PRIMARY KEY ([STRING_KEY1], [DOUBLE_KEY2])
);

Join generation

From an ITableJoin or an IReferenceDescription object, the SQL generator is able to generate a FOREIGN KEY statement matching the provided join.

note

Atoti objects don’t ensure the order of the join fields, but the databases generally require that the order exactly matches the target table primary key. Therefore, you must also provide the primary key order or the table description.

For instance:

    private static final IReferenceDescription TABLE_JOIN = StartBuilding.reference()
        .fromStore("GeneratorSqlStoreName")
        .toStore("StoreName2")
        .withName("ReferenceName")
        .withMapping("StringKey1", "StringKey")
        .withMapping("DoubleKey2", "DoubleKey")
        .build();

will generate:

ALTER TABLE TEST_SCHEMA.GENERATOR_SQL_STORE_NAME
    ADD CONSTRAINT REFERENCE_NAME
        FOREIGN KEY ([STRING_KEY1], [DOUBLE_KEY2])
            REFERENCES TEST_SCHEMA.STORE_NAME2([STRING_KEY], [DOUBLE_KEY]);

Full schema generation

You can also directly generate a whole schema from an IDatabaseSchema or an IDatastoreSchemaDescription object. For instance:

USE TEST_DATABASE;
GO

DROP SCHEMA IF EXISTS TEST_SCHEMA;
GO
CREATE SCHEMA TEST_SCHEMA;
GO

CREATE TABLE TEST_SCHEMA.GENERATOR_SQL_STORE_NAME(
  [STRING_KEY1] NVARCHAR(4000) NOT NULL DEFAULT 'N/A',
  [DOUBLE_KEY2] FLOAT NOT NULL DEFAULT 0.0,
  [INT_VALUE] INT,
  PRIMARY KEY ([STRING_KEY1], [DOUBLE_KEY2])
);

CREATE TABLE TEST_SCHEMA.STORE_NAME2(
  [STRING_KEY] NVARCHAR(4000) NOT NULL DEFAULT 'N/A',
  [DOUBLE_KEY] FLOAT NOT NULL DEFAULT 0.0,
  [DOUBLE_VALUE] FLOAT,
  PRIMARY KEY ([STRING_KEY], [DOUBLE_KEY])
);

ALTER TABLE TEST_SCHEMA.GENERATOR_SQL_STORE_NAME
ADD CONSTRAINT REFERENCE_NAME
FOREIGN KEY ([STRING_KEY1], [DOUBLE_KEY2])
REFERENCES TEST_SCHEMA.STORE_NAME2([STRING_KEY], [DOUBLE_KEY]);

GO

Insert statement

A collection of strong-typed tuples can be converted into SQL insert statements. You must provide the target table name.

note

These should only be used for testing.

They are not intended to be used in production nor to generate bulk load.

For instance:

    private static final Collection<Object[]> DATA = List.of(
            new Object[] {
                    "String",
                    1.0,
                    4,
                    8L,
                    LocalDate.of(2023,4,5),
                    new Date(123, Calendar.MAY, 5),
                    true,
                    2.0F,
                    ZonedDateTime.of(LocalDate.of(2023,4,5), LocalTime.of(4, 25), ZoneId.of("GMT")),
                    MathFunctions.Metric.SUM,
                    new String[] {"A", "B", "C"},
                    new double[] { 6.0, 7.0, 8.0},
                    "A"
            },
            new Object[] {
                    "O' String",
                    5.0,
                    null,
                    12L,
                    LocalDate.of(2023,4,5),
                    new Date(123, Calendar.MAY, 5),
                    true,
                    1.0F,
                    ZonedDateTime.of(LocalDate.of(2023,4,5), LocalTime.of(4, 25), ZoneId.of("CET")),
                    MathFunctions.Metric.SUM,
                    new String[] {"A", "B", "C"},
                    new double[] { 6.0, 7.0, 8.0},
                    "B"
            });

will generate:

INSERT INTO
  TEST_SCHEMA.GENERATOR_SQL_STORE_NAME
VALUES
  ('String', 1.0, 4, 8, '2023-04-05', '2023-05-05', 1, 2.0, '2023-04-05 04:25:00', 'SUM', '[''A'', ''B'', ''C'']', '[6.0, 7.0, 8.0]', 'A'),
  ('O'' String', 5.0, null, 12, '2023-04-05', '2023-05-05', 1, 1.0, '2023-04-05 04:25:00', 'SUM', '[''A'', ''B'', ''C'']', '[6.0, 7.0, 8.0]', 'B');

Other features

Drop table
Insert statement (for debugging purposes only)
Use database statement
Create schema statement