SQLite 3.31 Released With Support For Generated Columns

[phoronix]

Phoronix: SQLite 3.31 Released With Support For Generated Columns

The widely-used SQLite embedded database library saw its first major release of 2020 this week...

http://www.phoronix.com/scan.php?pag...-3.31-Released

[George99]

Nice. This update breaks Firefox and Thunderbird on debian...

[archkde]

What is the difference between generated colums and something like "SELECT (Column1 + Column2) FROM <table>" (just an example for a simple computation)?

[Hans Micheelsen]

What is the difference between generated colums and something like "SELECT (Column1 + Column2) FROM <table>" (just an example for a simple computation)?

I found this description: https://www.sqlite.org/gencol.html

[flower]

What is the difference between generated colums and something like "SELECT (Column1 + Column2) FROM <table>" (just an example for a simple computation)?

It happens to another time.
With select its computed on retrieve. Computed columns are usually computed on update and are indexable.

I don't know about this sqllite implementation though

[uid313]

Generated columns are also known as virtual columns and part of the SQL:2003 standard.

What are these useful for? In what use cases are they suitable? Any examples?
I can only think of a have virtual "Name" column that consists of the FirstName and LastName columns. Any other?

Also, why even do this on the database side, wouldn't be easier, more flexible and better to do it on the client-side?

[archkde]

It happens to another time.
With select its computed on retrieve. Computed columns are usually computed on update and are indexable.

I don't know about this sqllite implementation though

That makes sense. Thank you.

[tangent]

What are these useful for? In what use cases are they suitable? Any examples?

You have a Users table with a “contact” column, a generic string column where the user puts some way for the sysadmin to contact them. It isn’t strictly-typed because it’s a rarely-used feature, used only as a fallback when contact through the software fails. Some users put in an email address, some a mobile phone number, some a Twitter handle. You don’t want to run that text field through a series of regexes every time you need to use it, so you create three computed columns: is_email, is_phone, is_twitter, and you set the DB schema up to run custom SQL functions to recompile those every time Users.contact changes.

SQLite’s particular implementation then lets you mark those values as stored in the DB file, so that you only have to recompute them once.

SQLite also makes it easy to define those custom SQL functions using C code, which is almost always easier to write in than SQL but also, before this feature was added to SQLite, you probably already had a C equivalent of is_phone() and such, so now you just need to refactor it: the guts of the function are exported to SQLite as a custom function, and the outer shell then becomes a mere accessor for the cached Users.is_phone field.

If you reject my example as spurious — why would anyone have such loosely-typed data?! — then consider that RFC821 email addresses encompass a much wider scope than common Internet email. I could just have well have cast the example as Users.is_internet_email_address, is_mci_mail_address, is_compuserv_address, etc.

[Hugh]

Nice. This update breaks Firefox and Thunderbird on debian...

Interesting. This seems to be hitting us on Ubuntu. Any more info?

[tangent]

Interesting. This seems to be hitting us on Ubuntu. Any more info?

I suspect the fix is 3.31.1, specifically this checkin.