Merge pull request #13793 from senny/postgres/dynamic_range_oid

Dynamically define PostgreSQL Range OIDs

4 files changed, 147 insertions, 63 deletions

diff --git a/activerecord/CHANGELOG.md b/activerecord/CHANGELOG.md
index 7efd75a239..881a4d2d85 100644
--- a/activerecord/CHANGELOG.md
+++ b/activerecord/CHANGELOG.md
@@ -1,3 +1,18 @@
+*   Deprecate half-baked support for PostgreSQL range values with excluding beginnings.
+    We currently map PostgreSQL ranges to Ruby ranges. This conversion is not fully
+    possible because the Ruby range does not support excluded beginnings.
+
+    The current solution of incrementing the beginning is not correct and is now
+    deprecated. For subtypes where we don't know how to increment (e.g. `#succ`
+    is not defined) it will raise an ArgumentException for ranges with excluding
+    beginnings.
+
+    *Yves Senn*
+
+*   Support for user created range types in PostgreSQL.
+
+    *Yves Senn*
+
 *   Default scopes are no longer overriden by chained conditions.
 
     Before this change when you defined a `default_scope` in a model
diff --git a/activerecord/lib/active_record/connection_adapters/postgresql/oid.rb b/activerecord/lib/active_record/connection_adapters/postgresql/oid.rb
index fae260a921..6f9b60b6c4 100644
--- a/activerecord/lib/active_record/connection_adapters/postgresql/oid.rb
+++ b/activerecord/lib/active_record/connection_adapters/postgresql/oid.rb
@@ -6,6 +6,10 @@ module ActiveRecord
       module OID
         class Type
           def type; end
+
+          def infinity(options = {})
+            ::Float::INFINITY * (options[:negative] ? -1 : 1)
+          end
         end
 
         class Identity < Type
@@ -109,23 +113,19 @@ module ActiveRecord
           def extract_bounds(value)
             from, to = value[1..-2].split(',')
             {
-              from:          (value[1] == ',' || from == '-infinity') ? infinity(:negative => true) : from,
-              to:            (value[-2] == ',' || to == 'infinity') ? infinity : to,
+              from:          (value[1] == ',' || from == '-infinity') ? @subtype.infinity(negative: true) : from,
+              to:            (value[-2] == ',' || to == 'infinity') ? @subtype.infinity : to,
               exclude_start: (value[0] == '('),
               exclude_end:   (value[-1] == ')')
             }
           end
 
-          def infinity(options = {})
-            ::Float::INFINITY * (options[:negative] ? -1 : 1)
-          end
-
           def infinity?(value)
             value.respond_to?(:infinite?) && value.infinite?
           end
 
-          def to_integer(value)
-            infinity?(value) ? value : value.to_i
+          def type_cast_single(value)
+            infinity?(value) ? value : @subtype.type_cast(value)
           end
 
           def type_cast(value)
@@ -133,27 +133,20 @@ module ActiveRecord
             return value if value.is_a?(::Range)
 
             extracted = extract_bounds(value)
-
-            case @subtype
-            when :date
-              from  = ConnectionAdapters::Column.value_to_date(extracted[:from])
-              from -= 1.day if extracted[:exclude_start]
-              to    = ConnectionAdapters::Column.value_to_date(extracted[:to])
-            when :decimal
-              from  = BigDecimal.new(extracted[:from].to_s)
-              # FIXME: add exclude start for ::Range, same for timestamp ranges
-              to    = BigDecimal.new(extracted[:to].to_s)
-            when :time
-              from = ConnectionAdapters::Column.string_to_time(extracted[:from])
-              to   = ConnectionAdapters::Column.string_to_time(extracted[:to])
-            when :integer
-              from = to_integer(extracted[:from]) rescue value ? 1 : 0
-              from -= 1 if extracted[:exclude_start]
-              to   = to_integer(extracted[:to]) rescue value ? 1 : 0
-            else
-              return value
+            from = type_cast_single extracted[:from]
+            to = type_cast_single extracted[:to]
+
+            if !infinity?(from) && extracted[:exclude_start]
+              if from.respond_to?(:succ)
+                from = from.succ
+                ActiveSupport::Deprecation.warn <<-MESSAGE
+Excluding the beginning of a Range is only partialy supported through `#succ`.
+This is not reliable and will be removed in the future.
+                MESSAGE
+              else
+                raise ArgumentError, "The Ruby Range object does not support excluding the beginning of a Range. (unsupported value: '#{value}')"
+              end
             end
-
             ::Range.new(from, to, extracted[:exclude_end])
           end
         end
@@ -222,6 +215,10 @@ module ActiveRecord
 
             ConnectionAdapters::Column.value_to_decimal value
           end
+
+          def infinity(options = {})
+            BigDecimal.new("Infinity") * (options[:negative] ? -1 : 1)
+          end
         end
 
         class Hstore < Type
@@ -331,13 +328,6 @@ module ActiveRecord
         alias_type    'int8', 'int2'
         alias_type    'oid',  'int2'
 
-        register_type 'daterange', OID::Range.new(:date)
-        register_type 'numrange', OID::Range.new(:decimal)
-        register_type 'tsrange', OID::Range.new(:time)
-        register_type 'int4range', OID::Range.new(:integer)
-        alias_type    'tstzrange', 'tsrange'
-        alias_type    'int8range', 'int4range'
-
         register_type 'numeric', OID::Decimal.new
         register_type 'text', OID::Identity.new
         alias_type 'varchar', 'text'
diff --git a/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb b/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb
index 36c7462419..d1fb132b60 100644
--- a/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb
+++ b/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb
@@ -785,18 +785,29 @@ module ActiveRecord
         end
 
         def initialize_type_map(type_map)
-          result = execute('SELECT oid, typname, typelem, typdelim, typinput FROM pg_type', 'SCHEMA')
-          leaves, nodes = result.partition { |row| row['typelem'] == '0' }
+          if supports_ranges?
+            result = execute(<<-SQL, 'SCHEMA')
+              SELECT t.oid, t.typname, t.typelem, t.typdelim, t.typinput, r.rngsubtype
+              FROM pg_type as t
+              LEFT JOIN pg_range as r ON oid = rngtypid
+            SQL
+          else
+            result = execute(<<-SQL, 'SCHEMA')
+              SELECT t.oid, t.typname, t.typelem, t.typdelim, t.typinput
+              FROM pg_type as t
+            SQL
+          end
+          ranges, nodes = result.partition { |row| row['typinput'] == 'range_in' }
+          leaves, nodes = nodes.partition { |row| row['typelem'] == '0' }
+          arrays, nodes = nodes.partition { |row| row['typinput'] == 'array_in' }
 
-          # populate the leaf nodes
+          # populate the base types
           leaves.find_all { |row| OID.registered_type? row['typname'] }.each do |row|
             type_map[row['oid'].to_i] = OID::NAMES[row['typname']]
           end
 
           records_by_oid = result.group_by { |row| row['oid'] }
 
-          arrays, nodes = nodes.partition { |row| row['typinput'] == 'array_in' }
-
           # populate composite types
           nodes.each do |row|
             add_oid row, records_by_oid, type_map
@@ -807,6 +818,13 @@ module ActiveRecord
             array = OID::Array.new  type_map[row['typelem'].to_i]
             type_map[row['oid'].to_i] = array
           end
+
+          # populate range types
+          ranges.find_all { |row| type_map.key? row['rngsubtype'].to_i }.each do |row|
+            subtype = type_map[row['rngsubtype'].to_i]
+            range = OID::Range.new type_map[row['rngsubtype'].to_i]
+            type_map[row['oid'].to_i] = range
+          end
         end
 
         FEATURE_NOT_SUPPORTED = "0A000" #:nodoc:
diff --git a/activerecord/test/cases/adapters/postgresql/range_test.rb b/activerecord/test/cases/adapters/postgresql/range_test.rb
index 5c2d8e1c1d..306bb09a0d 100644
--- a/activerecord/test/cases/adapters/postgresql/range_test.rb
+++ b/activerecord/test/cases/adapters/postgresql/range_test.rb
@@ -10,12 +10,22 @@ if ActiveRecord::Base.connection.supports_ranges?
   class PostgresqlRangeTest < ActiveRecord::TestCase
     def teardown
       @connection.execute 'DROP TABLE IF EXISTS postgresql_ranges'
+      @connection.execute 'DROP TYPE IF EXISTS floatrange'
     end
 
     def setup
-      @connection = ActiveRecord::Base.connection
+      @connection = PostgresqlRange.connection
       begin
         @connection.transaction do
+          @connection.execute 'DROP TABLE IF EXISTS postgresql_ranges'
+          @connection.execute 'DROP TYPE IF EXISTS floatrange'
+          @connection.execute <<_SQL
+            CREATE TYPE floatrange AS RANGE (
+                subtype = float8,
+                subtype_diff = float8mi
+            );
+_SQL
+
           @connection.create_table('postgresql_ranges') do |t|
             t.daterange :date_range
             t.numrange :num_range
@@ -24,7 +34,11 @@ if ActiveRecord::Base.connection.supports_ranges?
             t.int4range :int4_range
             t.int8range :int8_range
           end
+
+          @connection.add_column 'postgresql_ranges', 'float_range', 'floatrange'
         end
+        @connection.send :reload_type_map
+        PostgresqlRange.reset_column_information
       rescue ActiveRecord::StatementInvalid
         skip "do not test on PG without range"
       end
@@ -35,23 +49,26 @@ if ActiveRecord::Base.connection.supports_ranges?
                    ts_range: "[''2010-01-01 14:30'', ''2011-01-01 14:30'']",
                    tstz_range: "[''2010-01-01 14:30:00+05'', ''2011-01-01 14:30:00-03'']",
                    int4_range: "[1, 10]",
-                   int8_range: "[10, 100]")
+                   int8_range: "[10, 100]",
+                   float_range: "[0.5, 0.7]")
 
       insert_range(id: 102,
-                   date_range: "(''2012-01-02'', ''2012-01-04'')",
+                   date_range: "[''2012-01-02'', ''2012-01-04'')",
                    num_range: "[0.1, 0.2)",
                    ts_range: "[''2010-01-01 14:30'', ''2011-01-01 14:30'')",
                    tstz_range: "[''2010-01-01 14:30:00+05'', ''2011-01-01 14:30:00-03'')",
-                   int4_range: "(1, 10)",
-                   int8_range: "(10, 100)")
+                   int4_range: "[1, 10)",
+                   int8_range: "[10, 100)",
+                   float_range: "[0.5, 0.7)")
 
       insert_range(id: 103,
-                   date_range: "(''2012-01-02'',]",
+                   date_range: "[''2012-01-02'',]",
                    num_range: "[0.1,]",
                    ts_range: "[''2010-01-01 14:30'',]",
                    tstz_range: "[''2010-01-01 14:30:00+05'',]",
-                   int4_range: "(1,]",
-                   int8_range: "(10,]")
+                   int4_range: "[1,]",
+                   int8_range: "[10,]",
+                   float_range: "[0.5,]")
 
       insert_range(id: 104,
                    date_range: "[,]",
@@ -59,15 +76,17 @@ if ActiveRecord::Base.connection.supports_ranges?
                    ts_range: "[,]",
                    tstz_range: "[,]",
                    int4_range: "[,]",
-                   int8_range: "[,]")
+                   int8_range: "[,]",
+                   float_range: "[,]")
 
       insert_range(id: 105,
-                   date_range: "(''2012-01-02'', ''2012-01-02'')",
-                   num_range: "(0.1, 0.1)",
-                   ts_range: "(''2010-01-01 14:30'', ''2010-01-01 14:30'')",
-                   tstz_range: "(''2010-01-01 14:30:00+05'', ''2010-01-01 06:30:00-03'')",
-                   int4_range: "(1, 1)",
-                   int8_range: "(10, 10)")
+                   date_range: "[''2012-01-02'', ''2012-01-02'')",
+                   num_range: "[0.1, 0.1)",
+                   ts_range: "[''2010-01-01 14:30'', ''2010-01-01 14:30'')",
+                   tstz_range: "[''2010-01-01 14:30:00+05'', ''2010-01-01 06:30:00-03'')",
+                   int4_range: "[1, 1)",
+                   int8_range: "[10, 10)",
+                   float_range: "[0.5, 0.5)")
 
       @new_range = PostgresqlRange.new
       @first_range = PostgresqlRange.find(101)
@@ -88,24 +107,24 @@ if ActiveRecord::Base.connection.supports_ranges?
 
     def test_int4range_values
       assert_equal 1...11, @first_range.int4_range
-      assert_equal 2...10, @second_range.int4_range
-      assert_equal 2...Float::INFINITY, @third_range.int4_range
+      assert_equal 1...10, @second_range.int4_range
+      assert_equal 1...Float::INFINITY, @third_range.int4_range
       assert_equal(-Float::INFINITY...Float::INFINITY, @fourth_range.int4_range)
       assert_nil @empty_range.int4_range
     end
 
     def test_int8range_values
       assert_equal 10...101, @first_range.int8_range
-      assert_equal 11...100, @second_range.int8_range
-      assert_equal 11...Float::INFINITY, @third_range.int8_range
+      assert_equal 10...100, @second_range.int8_range
+      assert_equal 10...Float::INFINITY, @third_range.int8_range
       assert_equal(-Float::INFINITY...Float::INFINITY, @fourth_range.int8_range)
       assert_nil @empty_range.int8_range
     end
 
     def test_daterange_values
       assert_equal Date.new(2012, 1, 2)...Date.new(2012, 1, 5), @first_range.date_range
-      assert_equal Date.new(2012, 1, 3)...Date.new(2012, 1, 4), @second_range.date_range
-      assert_equal Date.new(2012, 1, 3)...Float::INFINITY, @third_range.date_range
+      assert_equal Date.new(2012, 1, 2)...Date.new(2012, 1, 4), @second_range.date_range
+      assert_equal Date.new(2012, 1, 2)...Float::INFINITY, @third_range.date_range
       assert_equal(-Float::INFINITY...Float::INFINITY, @fourth_range.date_range)
       assert_nil @empty_range.date_range
     end
@@ -133,6 +152,14 @@ if ActiveRecord::Base.connection.supports_ranges?
       assert_nil @empty_range.tstz_range
     end
 
+    def test_custom_range_values
+      assert_equal 0.5..0.7, @first_range.float_range
+      assert_equal 0.5...0.7, @second_range.float_range
+      assert_equal 0.5...Float::INFINITY, @third_range.float_range
+      assert_equal -Float::INFINITY...Float::INFINITY, @fourth_range.float_range
+      assert_nil @empty_range.float_range
+    end
+
     def test_create_tstzrange
       tstzrange = Time.parse('2010-01-01 14:30:00 +0100')...Time.parse('2011-02-02 14:30:00 CDT')
       round_trip(@new_range, :tstz_range, tstzrange)
@@ -203,6 +230,38 @@ if ActiveRecord::Base.connection.supports_ranges?
       assert_nil_round_trip(@first_range, :int8_range, 39999...39999)
     end
 
+    def test_exclude_beginning_for_subtypes_with_succ_method_is_deprecated
+      tz = ::ActiveRecord::Base.default_timezone
+
+      silence_warnings {
+        assert_deprecated {
+          range = PostgresqlRange.create!(date_range: "(''2012-01-02'', ''2012-01-04'']")
+          assert_equal Date.new(2012, 1, 3)..Date.new(2012, 1, 4), range.date_range
+        }
+        assert_deprecated {
+          range = PostgresqlRange.create!(ts_range: "(''2010-01-01 14:30'', ''2011-01-01 14:30'']")
+          assert_equal Time.send(tz, 2010, 1, 1, 14, 30, 1)..Time.send(tz, 2011, 1, 1, 14, 30, 0), range.ts_range
+        }
+        assert_deprecated {
+          range = PostgresqlRange.create!(tstz_range: "(''2010-01-01 14:30:00+05'', ''2011-01-01 14:30:00-03'']")
+          assert_equal Time.parse('2010-01-01 09:30:01 UTC')..Time.parse('2011-01-01 17:30:00 UTC'), range.tstz_range
+        }
+        assert_deprecated {
+          range = PostgresqlRange.create!(int4_range: "(1, 10]")
+          assert_equal 2..10, range.int4_range
+        }
+        assert_deprecated {
+          range = PostgresqlRange.create!(int8_range: "(10, 100]")
+          assert_equal 11..100, range.int8_range
+        }
+      }
+    end
+
+    def test_exclude_beginning_for_subtypes_without_succ_method_is_not_supported
+      assert_raises(ArgumentError) { PostgresqlRange.create!(num_range: "(0.1, 0.2]") }
+      assert_raises(ArgumentError) { PostgresqlRange.create!(float_range: "(0.5, 0.7]") }
+    end
+
     private
       def assert_equal_round_trip(range, attribute, value)
         round_trip(range, attribute, value)
@@ -229,7 +288,8 @@ if ActiveRecord::Base.connection.supports_ranges?
             ts_range,
             tstz_range,
             int4_range,
-            int8_range
+            int8_range,
+            float_range
           ) VALUES (
             #{values[:id]},
             '#{values[:date_range]}',
@@ -237,7 +297,8 @@ if ActiveRecord::Base.connection.supports_ranges?
             '#{values[:ts_range]}',
             '#{values[:tstz_range]}',
             '#{values[:int4_range]}',
-            '#{values[:int8_range]}'
+            '#{values[:int8_range]}',
+            '#{values[:float_range]}'
           )
         SQL
       end