Sqlite GENERATE columns, but being used in INSERT

[yuphing-ong]

related to the fix for #297

I'm using 2.11.1 which now generates columns for Sqlite GENERATED columns.

Using a table with 3 columns and 8 other GENERATED columns, when I try to do an insert, I get an error like:

table test has 3 columns but 11 values were supplied

And looking at the model generated, when I print it out, it's easy to understand why:

{<nil> <nil> {"testjson:"12345"} <nil> <nil> <nil> <nil> <nil> <nil> <nil> <nil>}

I suspect this means sqlite doesn't like the nils for the 8 GENERATED columns?

[yuphing-ong]

some testing when I do this via sqlite CLI:

sqlite> insert into test values (null,null, '{"key":"value"}');
sqlite> select * from test ;
||{"key":"value"}||||||||
sqlite> insert into test values (null,null, '{"key":"value"}', null, null, null, null, null, null, null, null);
Error: in prepare, table test has 3 columns but 11 values were supplied (1)

[yuphing-ong]

An example table create:

CREATE TABLE test (
    created_at date DEFAULT CURRENT_TIMESTAMP,
    updated_at date,
    jvalues TEXT NOT NULL,
    j1 TEXT GENERATED ALWAYS as (JSON_EXTRACT(jvalues, "$.J1")) ,
    j2 TEXT GENERATED ALWAYS as (JSON_EXTRACT(jvalues, "$.J2")) ,
    j3 TEXT GENERATED ALWAYS as (JSON_EXTRACT(jvalues, "$.J3")) ,
    j4 TEXT GENERATED ALWAYS as (JSON_EXTRACT(jvalues, "$.J4")) ,
    j5 TEXT GENERATED ALWAYS as (JSON_EXTRACT(jvalues, "$.J5")) ,
    j6 TEXT GENERATED ALWAYS as (JSON_EXTRACT(jvalues, "$.J6")) ,
    j7 TEXT GENERATED ALWAYS as (JSON_EXTRACT(jvalues, "$.J7")) ,
    j8 TEXT GENERATED ALWAYS as (JSON_EXTRACT(jvalues, "$.J8")) 
);

[yuphing-ong]

to work around this, instead of:

// assume jvalues is json.Marshall'd 
r := model.Test{Jvalues: jvalues}
insert := Test.INSERT().MODEL(r)

I have to be explicit and do this instead:

insert := Test.INSERT(Test.Jvalues).VALUES(jvalues)

which I assume means I end up not being able to take advantage of the model's type checking

[edit] Ok I see, since the INSERT(Test.Jvalues) restricts the columns, I can still do this and take advantage of the type checking:

// assume jvalues is json.Marshall'd 
r := model.Test{Jvalues: jvalues}
insert := Test.INSERT(Test.Jvalues).MODEL(r)

[houten11]

You can also use Test.MutableColumns, so you don't have to specify each column.