Announcement

Collapse
No announcement yet.

SQLite 3.31 Released With Support For Generated Columns

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • SQLite 3.31 Released With Support For Generated Columns

    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...

    Phoronix, Linux Hardware Reviews, Linux hardware benchmarks, Linux server benchmarks, Linux benchmarking, Desktop Linux, Linux performance, Open Source graphics, Linux How To, Ubuntu benchmarks, Ubuntu hardware, Phoronix Test Suite

  • #2
    Nice. This update breaks Firefox and Thunderbird on debian...

    Comment


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

      Comment


      • #4
        Originally posted by archkde View Post
        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
        Last edited by tildearrow; 26 January 2020, 04:20 PM.

        Comment


        • #5
          Originally posted by archkde View Post
          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

          Comment


          • #6
            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?

            Comment


            • #7
              Originally posted by flower View Post

              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.

              Comment


              • #8
                Originally posted by uid313 View Post
                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.

                Comment


                • #9
                  Originally posted by George99 View Post
                  Nice. This update breaks Firefox and Thunderbird on debian...
                  Interesting. This seems to be hitting us on Ubuntu. Any more info?

                  Comment


                  • #10
                    Originally posted by Hugh View Post

                    Interesting. This seems to be hitting us on Ubuntu. Any more info?
                    I suspect the fix is 3.31.1, specifically this checkin.

                    Comment

                    Working...
                    X