Generated columns

[sqddnt]

Hello,

I was wondering if generated columns were supported, or how should i make it work?

In my sqlite DB i added the following column:
ALTER TABLE Cyclist
ADD average_skill INTEGER GENERATED ALWAYS AS ((flat + mountain + downhill + cobblestones + timetrial + prologue + sprint + acceleration + stamina + resistance + recovery + hill + attack) / 13) VIRTUAL;

In my class i have:

public int average_skill { get; set; }

When I try to use it then im getting the following error:

SQLiteException: malformed database schema (Cyclist) - near "AS": syntax error
SimpleSQL.SQLite3.Prepare3 (System.IntPtr db, System.String query) (at <9d351781d88446ec91fb4a6de237d175>:0)
SimpleSQL.SQLiteCommand.Prepare3 () (at <9d351781d88446ec91fb4a6de237d175>:0)
SimpleSQL.SQLiteCommand.ExecuteQuery[T] (SimpleSQL.TableMapping map) (at <9d351781d88446ec91fb4a6de237d175>:0)

Thanks,

[echo17]

Based on that error, it looks like the sqlite library included with the plugin doesn't support generated columns. However, since the plugin is ORM based you can actually get more power by using calculated properties instead. Something like:



public int average_skill
{
   get
   {
       return (flat + mountain + downhill + cobblestones + timetrial + prologue + sprint + acceleration + stamina + resistance + recovery + hill + attack) / 13;
   }
}



Then you could use it something like:



var newCyclist = new Cyclist()
{
   flat = 5,
   mountain = 20,
   downhill = 1,
   cobblestones = 3,
   timetrial = 8,
   prologue = 0,
   sprint = 22,
   acceleration = 3,
   stamina = 2,
   resistance = 9,
   recovery = 1,
   hill = 8,
   attack = 9
};

var sql = "INSERT INTO Cyclist (flat, mountain, downhill, cobblestones, timetrial, prologue, sprint, acceleration, stamina, resistance, recovery, hill, attack, average_skill) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

dbManager.Execute(sql, newCyclist.flat, newCyclist.mountain, newCyclist.downhill, newCyclist.cobblestones, newCyclist.timetrial, newCyclist.prologue, newCyclist.sprint, newCyclist.acceleration, newCyclist.stamina, newCyclist.resistance, newCyclist.recovery, newCyclist.hill, newCyclist.attack, newCyclist.average_skill);



Note that you do not have to set the value of average_skill in the newCyclist object since the property is calculated in the class.

This method gives you a ton of flexibility, where you can even calculate your properties using complex functions not available inside sqlite.

[sqddnt]

Thanks, it is working. Will there any plan to support generated columns?

[echo17]

I can add it to my list of items to consider.