Add support for "generated always" columns

[vlsi]

Databases support "GENERATED ALWAYS" columns which are columns that can compute values at the query time.
Users can include the columns in indices, and so on.

It would be nice if there was a possibility to declare a column that contains an expression.

For instance:

object Cities : Table() {
    val id = integer("id") // Column<Int>
    val name = varchar("name", 50) // Column<String>

    val lower_name = varchar("lower_name", 50) generatedAs {
        name.lowerCase() // it is Expression<T>.lowerCase(): LowerCase<T>
    }
    // OR
    val lower_name = varchar("lower_name", 50) generatedAs {
        // It looks like this syntax would allow generating the expressions from the composing parts
        it[name].lowerCase()
    }

    override val primaryKey = PrimaryKey(lower_name, name = "PK_Cities")
}

Then there should be a way to:
a) Include the virtual column into a primary key. Then update statement builder should be able to build queries like

update cities set ... where lower(name)=lower(?)

b) include the virtual column into index definition (e.g. to create index when creating table via Exposed)

c) use the virtual expression in the search somehow. For instance, if there's an index on lower(name), then it would be interesting to build search conditions like where lower(name)=lower(?) when user passes input ingredients (e.g. an expression for name)

See

• https://www.postgresql.org/docs/14/ddl-generated-columns.html
• https://oracle-base.com/articles/11g/virtual-columns-11gr1

[jasondashwang]

I would also like a form of virtual columns on defining table schema.

My example use case would be

object Object : Table() {
    val id = integer("id") // Column<Int>
    val endDate = date("end_date") // Column<LocalDate>

    val endTime: LocalDateTime = generatedAs {
        append("(", this.endDate, " + TIME '23:59:59:9999') AT TIME ZONE 'America/Los_Angeles'")
    }
}

[darmstrong1]

Related to this issue, I would like to know if it is possible to tell Exposed to ignore all columns not included in an insert statement.
I created a table in postgresql that looks like this:

CREATE TABLE usr
(
    id                    SERIAL           PRIMARY KEY,
    nm                    text             NOT NULL,
    age                   int              NOT NULL,
    _hash                 bigint GENERATED ALWAYS AS (hash_record_extended((nm, age), 0)) STORED
);

My Exposed table looks like this:

object UsrTable: IntIdTable("usr", "id") {
	val nm = text("nm")
	val age = integer("age")
	val hash = long("_hash").nullable()
}

When I run this:

        val usr = Usr(nm ="jill", age = 22)
        val saved = UsrTable.insert {
            it[nm] = usr.nm
            it[age] = usr.age
        }.resultedValues!!.map {
            Usr(
                id = it[UsrTable.id].value,
                nm = it[UsrTable.nm],
                age = it[UsrTable.age],
                hash = it[UsrTable.hash]
            )
        }

I get the following error:
Exception in thread "main" org.jetbrains.exposed.exceptions.ExposedSQLException: org.postgresql.util.PSQLException: ERROR: cannot insert a non-DEFAULT value into column "_hash" Detail: Column "_hash" is a generated column.
I was hoping Exposed would ignore the _hash column because I did not set it in my insert parameters.

Since the database sets the value of _hash, I don't see the need to specify the default expression in the Exposed code, but I tried that just in case it would get me past the error. I set the hash column to this:

object UsrTable: IntIdTable("usr", "id") {
	val nm = text("nm")
	val age = integer("age")
	val hash = long("_hash").defaultExpression(CustomFunction("hash_record_extended", LongColumnType(), nm, age)).nullable()
}

This is the query it produces and the error message I see in the log:

13:10:05.068 [main] DEBUG Exposed - INSERT INTO usr (_hash, age, nm) VALUES ((hash_record_extended(usr.nm, usr.age)), 22, 'jill')
SQL: INSERT INTO usr (_hash, age, nm) VALUES ((hash_record_extended(usr.nm, usr.age)), 22, 'jill')
13:10:05.068 [main] WARN Exposed - Transaction attempt #0 failed: org.postgresql.util.PSQLException: ERROR: invalid reference to FROM-clause entry for table "usr"
  Hint: There is an entry for table "usr", but it cannot be referenced from this part of the query.
  Position: 64. Statement(s): INSERT INTO usr (_hash, age, nm) VALUES ((hash_record_extended(usr.nm, usr.age)), ?, ?)
org.jetbrains.exposed.exceptions.ExposedSQLException: org.postgresql.util.PSQLException: ERROR: invalid reference to FROM-clause entry for table "usr"
  Hint: There is an entry for table "usr", but it cannot be referenced from this part of the query.

I'm not so concerned with the second error I got because I don't see the need to set a default expression for the column since the database will do that for me.

I would like to know if there is a way to tell Exposed to not include a column in an insert statement if I do not list it in my insert statement.
If there is not a way to do that, would it be possible to add a generated flag to columns, similar to the nullable flag?
If the generated flag is set, then Exposed would never send a value for that column in insert statements.

[bog-walk]

@darmstrong1 Since version 0.44.0, a column can be flagged as generated by the database using databaseGenerated():

val hash = long("_hash").nullable().databaseGenerated()

The column definition of the Exposed table object could also be adjusted if needed using withDefinition() (available in upcoming version 0.52.0):

object UsrTable : IntIdTable("usr", "id") {
    val nm = text("nm")
    val age = integer("age")
    val hash = long("_hash")
        .nullable()
        .databaseGenerated()
        .withDefinition("GENERATED ALWAYS AS (hash_record_extended((nm, age), 0)) STORED")
}

[adAAM91]

@bog-walk your suggestion works, but this definition sets a db default to the column. Currently SchemaUtils cant handle this, it always recognize it as "IncorrectDefault" and try to drop it with DROP DEFAULT which fails (only DROP EXPRESSION works with generated columns). SchemaUtils should handle generated column's "default" separately.

[klaidoshka]

Schema

object Users : IntIdTable("users") {
    val name = varchar(
        "name",
        32
    )
        .uniqueIndex()

    val nameUpper = varchar(
        "nameUpper",
        32
    )
        .databaseGenerated()
        .withDefinition("GENERATED ALWAYS AS (UPPER(`name`)) PERSISTENT")
        .uniqueIndex()     

Generated SQL
Using MariaDB dialect

CREATE TABLE IF NOT EXISTS users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(32) NOT NULL,
  nameUpper VARCHAR(32) GENERATED ALWAYS AS (UPPER(`name`)) PERSISTENT NOT NULL
)

MariaDB does not support nullability in its GENERATED ALWAYS AS column. However, it seems that Exposed enforces nullability part. That makes generated SQL incorrect. Could do it in another way, via defaultExpression by uppercasing name, however it isn't really generated then.