Add support for generated columns in SQLite3 adapter by fractaledmind · Pull Request #49346 · rails/rails

fractaledmind · 2023-09-21T14:01:38Z

Motivation / Background

SQLite is a feature-rich database engine, and production usage is only growing. We need Rails' support to offer developers the range and scope of its features.

Both the MySQL and PostgreSQL adapters support virtual columns, and SQLite itself has support generated columns since 2020.

Detail

PR #41856 introduced virtual columns to the PostgreSQLAdapter. This is effectively a clone of that feature, with some necessary tweaks.

create_table :users do |t|
  t.string :name
  t.virtual :name_upper, type: :string, as: 'UPPER(name)'
  t.virtual :name_lower, type: :string, as: 'LOWER(name)', stored: true
end

Firstly, SQLite supported both STORED and VIRTUAL generated columns, while PostgreSQL only supports VIRTUAL. Secondly, SQLite doesn't support ALTER TABLE commands with generated columns.

In this PR, I am adding full support for the SQLite3Adapter by implementing:

ActiveRecord::ConnectionAdapters::SQLite3::Column#virtual?
ActiveRecord::ConnectionAdapters::SQLite3Adapter#supports_virtual_columns?

and altering:

ActiveRecord::ConnectionAdapters::SQLite3::SchemaCreation#add_column_options
ActiveRecord::ConnectionAdapters::SQLite3::SchemaDefinitions#new_column_definition
ActiveRecord::ConnectionAdapters::SQLite3::SchemaDumper#prepare_column_options
ActiveRecord::ConnectionAdapters::SQLite3::SchemaStatements#new_column_from_field
ActiveRecord::ConnectionAdapters::SQLite3Adapter#table_structure
ActiveRecord::ConnectionAdapters::SQLite3Adapter#invalid_alter_table_type?
ActiveRecord::ConnectionAdapters::SQLite3Adapter#table_structure_with_collation

Checklist

Before submitting the PR make sure the following are checked:

This Pull Request is related to one change. Changes that are unrelated should be opened in separate PRs.
Commit message has a detailed description of what changed and why. If this PR fixes a related issue include it in the commit message. Ex: [Fix #issue-number]
Tests are added or updated if you fix a bug or add a feature.
CHANGELOG files are updated for the changed libraries if there is a behavior change or additional feature. Minor bug fixes and documentation changes should not be included.

skipkayhil

Small comment, but generally looks good

activerecord/lib/active_record/connection_adapters/sqlite3/column.rb

nvasilevski

👍

activerecord/lib/active_record/connection_adapters/sqlite3/schema_statements.rb

fractaledmind · 2023-09-26T20:27:59Z

@skipkayhil Addressed your one comment. Everything should be good to go now

activerecord/test/cases/adapters/sqlite3/sqlite3_adapter_test.rb

byroot

Two minor nitpick. I also rebased your branch to replace the merge commit.

Given how small the changes are I'll apply them myself.

byroot · 2023-12-14T17:03:11Z

activerecord/lib/active_record/connection_adapters/sqlite3/column.rb

        end

+        def virtual?
+          @generated_type.present?


Suggested change

@generated_type.present?

@generated_type

.present? isn't a "to_boolean" cast, it has fairly peculiar rules, and is rarely what you want.

Here we can just return @generated_type, or if you are adamant about returning a boolean, you can use !!@generated_type or !@generated_type.nil?

byroot · 2023-12-14T17:08:16Z

activerecord/lib/active_record/connection_adapters/sqlite3_adapter.rb

          type.to_sym == :primary_key || options[:primary_key] ||
-            options[:null] == false && options[:default].nil?
+            options[:null] == false && options[:default].nil? ||
+            (type.to_sym == :virtual && options[:stored])


We should do the to_sym casting in add_column

Generated columns (both stored and dynamic) are supported since version 3.31.0 of SQLite. This adds support for those to the SQLite3 adapter. ```ruby create_table :users do |t| t.string :name t.virtual :name_upper, type: :string, as: 'UPPER(name)' t.virtual :name_lower, type: :string, as: 'LOWER(name)', stored: true end ```

…hancedsqlite3-adapter Most of the enhancements provided by activerecord-enhancedsqlite3-adapter have been upstreamed into Rails, including: * Add support for generated columns in SQLite3 adapter rails/rails#49346 * Add SQLite3 support for supports_insert_returning? rails/rails#49290 * Allow overriding SQLite defaults from database.yml rails/rails#50460 * Performance tune the SQLite3 adapter connection configuration rails/rails#49349 * Allow SQLite3 busy_handler to be configured with simple max number of retries rails/rails#49352 One enhancement Joy still needs is a way to load SQLite3 extensions. There is a sqlpkg gem that would provide this, which includes generating an initializer that overrides the Rails SQLite3 adapter. Instead, I‘ve just adapted the enhancedsqlite3-adapter code and droppped into the lib/ directory. The code here also knows how to load an extension by gem name which is makes it useful for the sqlite_ulid gem currently in use.

rails-bot bot added the activerecord label Sep 21, 2023

abaldwin88 mentioned this pull request Sep 21, 2023

Add ActiveRecord.reload_attributes #48434

Open

$@fractaledmind$ fractaledmind force-pushed the ar-sqlite-virtual-columns branch from ef9ab69 to 6b61b49 Compare September 24, 2023 21:15

skipkayhil reviewed Sep 25, 2023

View reviewed changes

activerecord/lib/active_record/connection_adapters/sqlite3/column.rb Outdated Show resolved Hide resolved

$@fractaledmind$ fractaledmind force-pushed the ar-sqlite-virtual-columns branch from 6b61b49 to 941f83b Compare September 25, 2023 08:04

nvasilevski approved these changes Sep 25, 2023

View reviewed changes

activerecord/lib/active_record/connection_adapters/sqlite3/schema_statements.rb Outdated Show resolved Hide resolved

$@fractaledmind$ fractaledmind force-pushed the ar-sqlite-virtual-columns branch from 941f83b to 5f68fa4 Compare September 25, 2023 08:45

$@fractaledmind$ fractaledmind mentioned this pull request Sep 26, 2023

[skip ci] Add 7.1 release notes for SQLite3 adapter improvements #49387

Merged

zzak added this to the 7.1.0 milestone Sep 27, 2023

rafaelfranca removed this from the 7.1.0 milestone Sep 27, 2023

$@fractaledmind$ fractaledmind force-pushed the ar-sqlite-virtual-columns branch from 5f68fa4 to cf882dc Compare September 27, 2023 05:41

yahonda reviewed Oct 3, 2023

View reviewed changes

activerecord/test/cases/adapters/sqlite3/sqlite3_adapter_test.rb Outdated Show resolved Hide resolved

$@fractaledmind$ fractaledmind force-pushed the ar-sqlite-virtual-columns branch from cf882dc to 14fe38b Compare October 9, 2023 19:24

$@fractaledmind$ fractaledmind force-pushed the ar-sqlite-virtual-columns branch 3 times, most recently from d2803e3 to 09b1be4 Compare November 2, 2023 08:58

flavorjones added ready PRs ready to merge and removed ready PRs ready to merge labels Dec 14, 2023

byroot force-pushed the ar-sqlite-virtual-columns branch from 3610017 to a9c77e4 Compare December 14, 2023 17:00

byroot approved these changes Dec 14, 2023

View reviewed changes

byroot force-pushed the ar-sqlite-virtual-columns branch from a9c77e4 to e3ad3e8 Compare December 14, 2023 17:10

byroot force-pushed the ar-sqlite-virtual-columns branch from e3ad3e8 to 4e7bdcf Compare December 14, 2023 17:19

byroot merged commit 9064735 into rails:main Dec 14, 2023

$@fractaledmind$ fractaledmind deleted the ar-sqlite-virtual-columns branch December 16, 2023 00:13

$@fractaledmind$ fractaledmind mentioned this pull request Jan 22, 2024

Fix SQLite table definition parsing bug to handle commas in default function definitions #50838

Merged

4 tasks

rossta mentioned this pull request Oct 3, 2024

Upgrade rails 8 beta joyofrails/joyofrails.com#233

Merged

aidanharan mentioned this pull request Oct 8, 2025

Added support for computed columns rails-sqlserver/activerecord-sqlserver-adapter#1367

Merged

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Comments

Add support for generated columns in SQLite3 adapter#49346

Add support for generated columns in SQLite3 adapter#49346
byroot merged 1 commit intorails:mainfrom
fractaledmind:ar-sqlite-virtual-columns

$@fractaledmind$ fractaledmind commented Sep 21, 2023

Uh oh!

skipkayhil left a comment

Uh oh!

Uh oh!

nvasilevski left a comment

Uh oh!

Uh oh!

fractaledmind commented Sep 26, 2023

Uh oh!

Uh oh!

byroot left a comment

Uh oh!

byroot Dec 14, 2023

Uh oh!

byroot Dec 14, 2023

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

8 participants

Comments

Conversation

fractaledmind commented Sep 21, 2023

Motivation / Background

Detail

Checklist

Uh oh!

skipkayhil left a comment

Choose a reason for hiding this comment

Uh oh!

Uh oh!

nvasilevski left a comment

Choose a reason for hiding this comment

Uh oh!

Uh oh!

fractaledmind commented Sep 26, 2023

Uh oh!

Uh oh!

byroot left a comment

Choose a reason for hiding this comment

Uh oh!

byroot Dec 14, 2023

Choose a reason for hiding this comment

Uh oh!

byroot Dec 14, 2023

Choose a reason for hiding this comment

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

8 participants

$@fractaledmind$ fractaledmind commented Sep 21, 2023