Generated(computed) Columns

[kevinresol]

MySQL/Postgres: (Postgres does not support VIRTUAL)

column_name data_type [GENERATED ALWAYS] AS (expression)
   [VIRTUAL | STORED] [UNIQUE [KEY]]

SQL server:

column_name AS expression [PERSISTED]

Proposed Haxe syntax:

@:generated // computed on the fly
@:generated(Stored) or @:generated(true) // store computed value in table
var foo:Int = bar * 2;

[kevinresol]

A few notes on implementation:

1. Generated field should not appear in a insert query
2. But it should appear in the result of a select query
3. I think it shouldn't be too hard to convert the expression into CREATE/ALTER TABLE query since we already have the AST at compile time? @benmerckx
4. How to implement function calls? (e.g. as illustrated in the age-from-dob example here)

[benmerckx]

Formatting/printing SQL has always been the easy part to tink_sql changes :) What's the added benefit over making it part of the select (and thereby also supporting databases without computed columns)?

[kevinresol]

Maybe my description was confusing, I meant this:

1. $type(db.Table.insertOne) => {nonComputed:String}->Promise<Int>
2. $type(db.Table.first()) => Promise<{nonComputed:String, computed:String}>
3. db.Table.select($type({...})) => {?nonComputed, ?computed}

So what I mean is simply: computed values should be supported as readonly.

[back2dos]

I can see why this would be useful. OTOH I wonder if it's not easier and more portable to have the computation in application code rather than the database?

[kevinresol]

Computed columns can be used for (speeding up) aggregation. So no, I don't think they are equivalent.