feat: EXPOSED-416 Support adding special database-specific column definitions

[bog-walk]

Column definition in CREATE TABLE DDL currently covers the following: _column_name column_type column_default null_constraint keyconstraint

There are some very niched database-specific keywords or syntax that can be appended to a column definition, which are not available to the user unless introduced by feature requests. Some of these would just require hard-coding the additional feature in Column class for the specific database.

This proposal attempts to give user's the ability to customize column definitions that fit the following criteria:

1. It is very database-specific, but low priority.
2. It does not affect any further logic, like column insert/update values, prepared statement, etc.
3. It fits standard ordering rule shared by most databases, i.e. it follows _column_name column_type columndefault but comes before any constraints.

---

This will not solve all use cases, but gives user's access to the following example features:

MySQL: ON UPDATE CURRENT_TIMESTAMP Specifies default value when row is updated

object : Table("tester") {
    val created = timestamp("created")
        .defaultExpression(CurrentTimestamp)
        .withDefinition("ON UPDATE", CurrentTimestamp)
}

// generates SQL:
// CREATE TABLE IF NOT EXISTS tester (created DATETIME(6) DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6) NOT NULL)

---

Oracle: DEFAULT ON NULL (also H2) Specifies default value when row is inserted (or updated) with null value

object : Table("tester") {
    val item = varchar("item", 32).withDefinition("DEFAULT ON NULL", stringLiteral("Item A"))
}

// generates SQL:
// CREATE TABLE TESTER (ITEM VARCHAR2(32 CHAR) DEFAULT ON NULL 'Item A' NOT NULL)

---

SQL Server: MASKED WITH Masks data returned by SELECT statements for users without proper permissions

object : Table("tester") {
    val email = varchar("email", 128)
        .uniqueIndex()
        .withDefinition("MASKED WITH (FUNCTION = 'email()')")
}

// generates SQL:
// CREATE TABLE tester (email VARCHAR(128) MASKED WITH (FUNCTION = 'email()') NOT NULL)

---

MySQL: COMMENT (also H2, SQLite) Adds comment to column only

object : Table("tester") {
    val amount = integer("amount").withDefinition("COMMENT", stringLiteral("Amount of testers"))
}

// generates SQL:
// CREATE TABLE IF NOT EXISTS tester (amount INT COMMENT 'Amount of testers' NOT NULL)

---

MySQL: INVISIBLE (also H2, Oracle) Sets a column's visibility to hidden (so it's not available unless named)

object : Table("tester") {
    val active = bool("active").nullable().withDefinition("INVISIBLE")
}

// generates SQL:
// CREATE TABLE IF NOT EXISTS tester (active BOOLEAN INVISIBLE NULL)

---

PostgreSQL GENERATED ALWAYS AS (...) (supported by all DB actually) Creates a generated column on database-side based on provided computation

object : Table("tester") {
    val amount = integer("amount").nullable()
    val computedAmount = integer("computed_amount")
        .nullable()
        .databaseGenerated()
        .withDefinition("GENERATED ALWAYS AS (AMOUNT + 1) STORED")
}

// generates SQL:
// CREATE TABLE IF NOT EXISTS tester (
//     amount INT NULL,
//     computed_amount INT GENERATED ALWAYS AS (AMOUNT + 1) STORED NULL
// )

---

The ordering rule works if either a foreign key or check constraint is chained to the column, because these are implemented as generated SQL following the CONSTRAINT keyword, which is separate from the column definition clause itself.

[e5l]

As a workaround for CREATE TABLE, we can create postprocessing for the statement to replace invalid syntax