From 3a36cb08687aa337efca4dff12d20901d4d8029e Mon Sep 17 00:00:00 2001 From: gkemmey Date: Thu, 13 Sep 2018 16:00:51 -0400 Subject: SQLite3 adapter supports expression indexes --- activerecord/CHANGELOG.md | 12 ++++++++ .../sqlite3/schema_statements.rb | 6 +++- .../connection_adapters/sqlite3_adapter.rb | 4 +++ .../cases/adapters/sqlite3/sqlite3_adapter_test.rb | 35 ++++++++++++++++++++++ activerecord/test/cases/schema_dumper_test.rb | 19 ++++++++---- 5 files changed, 70 insertions(+), 6 deletions(-) diff --git a/activerecord/CHANGELOG.md b/activerecord/CHANGELOG.md index bfdc978005..fa230df993 100644 --- a/activerecord/CHANGELOG.md +++ b/activerecord/CHANGELOG.md @@ -1,3 +1,15 @@ +* SQLite3 adapter supports expression indexes + + ``` + create_table :users do |t| + t.string :email + end + + add_index :users, 'lower(email)', name: 'index_users_on_email', unique: true + ``` + + *Gray Kemmey* + * Allow subclasses to redefine autosave callbacks for associated records. Fixes #33305. diff --git a/activerecord/lib/active_record/connection_adapters/sqlite3/schema_statements.rb b/activerecord/lib/active_record/connection_adapters/sqlite3/schema_statements.rb index 24e7bc65fa..0e254455b6 100644 --- a/activerecord/lib/active_record/connection_adapters/sqlite3/schema_statements.rb +++ b/activerecord/lib/active_record/connection_adapters/sqlite3/schema_statements.rb @@ -21,12 +21,16 @@ module ActiveRecord WHERE name = #{quote(row['name'])} AND type = 'index' SQL - /\sWHERE\s+(?.+)$/i =~ index_sql + /\sON\s+"(\w+?)"\s+\((?.+?)\)(\sWHERE\s+(?.+))?$/i =~ index_sql columns = exec_query("PRAGMA index_info(#{quote(row['name'])})", "SCHEMA").map do |col| col["name"] end + if columns.any?(&:nil?) # index created with an expression + columns = expressions.split(", ").map { |e| e.gsub(/^\"|\"?$/, "") } + end + # Add info on sort order for columns (only desc order is explicitly specified, asc is # the default) orders = {} diff --git a/activerecord/lib/active_record/connection_adapters/sqlite3_adapter.rb b/activerecord/lib/active_record/connection_adapters/sqlite3_adapter.rb index efe454fa7f..51f4944808 100644 --- a/activerecord/lib/active_record/connection_adapters/sqlite3_adapter.rb +++ b/activerecord/lib/active_record/connection_adapters/sqlite3_adapter.rb @@ -125,6 +125,10 @@ module ActiveRecord true end + def supports_expression_index? + sqlite_version >= "3.9.0" + end + def requires_reloading? true end diff --git a/activerecord/test/cases/adapters/sqlite3/sqlite3_adapter_test.rb b/activerecord/test/cases/adapters/sqlite3/sqlite3_adapter_test.rb index d1d4d545a3..320f6efd56 100644 --- a/activerecord/test/cases/adapters/sqlite3/sqlite3_adapter_test.rb +++ b/activerecord/test/cases/adapters/sqlite3/sqlite3_adapter_test.rb @@ -345,6 +345,41 @@ module ActiveRecord end end + if ActiveRecord::Base.connection.supports_expression_index? + def test_expression_index + with_example_table do + @conn.add_index "ex", "abs(number)", name: "expression" + index = @conn.indexes("ex").find { |idx| idx.name == "expression" } + assert_equal %w{ abs(number) }, index.columns + end + end + + def test_expression_index_with_where + with_example_table do + @conn.add_index "ex", "id % 10, abs(number)", name: "expression", where: "id > 1000" + index = @conn.indexes("ex").find { |idx| idx.name == "expression" } + assert_equal ["id % 10", "abs(number)"], index.columns + assert_equal "id > 1000", index.where + end + end + + def test_complicated_expression + with_example_table do + @conn.add_index "ex", "id % 10, (CASE WHEN number > 0 THEN abs(number) END)", name: "expression" + index = @conn.indexes("ex").find { |idx| idx.name == "expression" } + assert_equal ["id % 10", "(CASE WHEN number > 0 THEN abs(number) END)"], index.columns + end + end + + def test_not_everything_an_expression + with_example_table do + @conn.add_index "ex", %w{ id abs(number) }, name: "expression" + index = @conn.indexes("ex").find { |idx| idx.name == "expression" } + assert_equal %w{ id abs(number) }.sort, index.columns.sort + end + end + end + def test_primary_key with_example_table do assert_equal "id", @conn.primary_key("ex") diff --git a/activerecord/test/cases/schema_dumper_test.rb b/activerecord/test/cases/schema_dumper_test.rb index 75b68b521c..db13f20a39 100644 --- a/activerecord/test/cases/schema_dumper_test.rb +++ b/activerecord/test/cases/schema_dumper_test.rb @@ -226,6 +226,20 @@ class SchemaDumperTest < ActiveRecord::TestCase assert_match %r{t\.float\s+"temperature"$}, output end + if ActiveRecord::Base.connection.supports_expression_index? + def test_schema_dump_expression_indices + index_definition = dump_table_schema("companies").split(/\n/).grep(/t\.index.*company_expression_index/).first.strip + + if current_adapter?(:PostgreSQLAdapter) + assert_match %r{CASE.+lower\(\(name\)::text\)}i, index_definition + elsif current_adapter?(:SQLite3Adapter) + assert_match %r{CASE.+lower\(name\)}i, index_definition + else + assert false + end + end + end + if current_adapter?(:Mysql2Adapter) def test_schema_dump_includes_length_for_mysql_binary_fields output = standard_dump @@ -278,11 +292,6 @@ class SchemaDumperTest < ActiveRecord::TestCase assert_match %r{t\.decimal\s+"decimal_array_default",\s+default: \["1.23", "3.45"\],\s+array: true}, output end - def test_schema_dump_expression_indices - index_definition = dump_table_schema("companies").split(/\n/).grep(/t\.index.*company_expression_index/).first.strip - assert_match %r{CASE.+lower\(\(name\)::text\)}i, index_definition - end - def test_schema_dump_interval_type output = dump_table_schema "postgresql_times" assert_match %r{t\.interval\s+"time_interval"$}, output -- cgit v1.2.3