schema doesn't show generated columns in Sqlite

[shaicoleman]

Complete Description of Issue

When calling the schema method, it doesn't return generated columns.

Also, SQLite supports generated columns, however the documentation doesn't mention it https://www.sqlite.org/gencol.html https://sequel.jeremyevans.net/rdoc/classes/Sequel/Schema/CreateTableGenerator.html

Simplest Possible Self-Contained Example Showing the Bug

require 'sequel'
require 'logger'
DB = Sequel.sqlite
DB.loggers << Logger.new($stdout)
DB.sql_log_level = :debug
DB.create_table!(:artists) do
  primary_key :id
  String :name
end
DB.add_column(:artists, :uppername, :text, generated_always_as: Sequel.lit('upper(name)'))
DB[:artists].insert(name: 'The Beatles')
puts DB[:artists].first[:uppername]
puts DB.schema(:artists) # uppername field isn't returned

SQL Log (if any)

SELECT sqlite_version()
DROP TABLE IF EXISTS `artists`
CREATE TABLE `artists` (`id` integer NOT NULL PRIMARY KEY AUTOINCREMENT, `name` varchar(255))
PRAGMA foreign_keys = 0
PRAGMA legacy_alter_table = 1
BEGIN
ALTER TABLE `artists` ADD COLUMN `uppername` text GENERATED ALWAYS AS (upper(name)) 
COMMIT
PRAGMA foreign_keys = 1
PRAGMA legacy_alter_table = 0
INSERT INTO `artists` (`name`) VALUES ('The Beatles')
SELECT * FROM `artists` LIMIT 1
PRAGMA table_xinfo('artists')

Ruby Version

3.2.2

Sequel Version

5.73.0

[jeremyevans]

It looks like the information was previously included, and a change to SQLite's table_xinfo PRAGMA to remove the GENERATED ALWAYS from the type broke it. It is trivial to reinclude the generated columns in the schema output:

diff --git a/lib/sequel/adapters/shared/sqlite.rb b/lib/sequel/adapters/shared/sqlite.rb
index 355ab6c32..d1479612d 100644
--- a/lib/sequel/adapters/shared/sqlite.rb
+++ b/lib/sequel/adapters/shared/sqlite.rb
@@ -504,7 +504,6 @@ module Sequel
             # table_xinfo PRAGMA used, remove hidden columns
             # that are not generated columns
             if row[:generated] = (row.delete(:hidden) != 0)
-              next unless row[:type].end_with?(' GENERATED ALWAYS')
               row[:type] = row[:type].sub(' GENERATED ALWAYS', '')
             end
           end

However, SQLite does not appear to provide an API to get the GENERATED AS VALUE (upper(name) in your example). It looks like the information is only available via accessing the sqlite_schema/sqlite_master system table, and you would have to parse it out of the CREATE TABLE SQL. One of Sequel's core tenets is that it never attempts to parse SQL, because it is generally impossible to do correctly in all cases.

So I will apply the patch so generated columns turn up in schema, but note that doing so doesn't provide enough information to rebuild the column. It's probably fine if you just plan to use Sequel::Model, but if you are trying to use the schema_dumper extension, it will not do what you want.