From 3b516b5beb79f7e8c1fdd123e7d5a03c00349cdf Mon Sep 17 00:00:00 2001 From: "Dickson S. Guedes" Date: Wed, 5 Sep 2012 14:01:21 -0300 Subject: ActiveRecord support to PostgreSQL 9.2 JSON type This implements the support to encode/decode JSON data to/from database and creating columns of type JSON using a native type [1] supported by PostgreSQL from version 9.2. [1] http://www.postgresql.org/docs/9.2/static/datatype-json.html --- activerecord/CHANGELOG.md | 5 ++ .../active_record/connection_adapters/column.rb | 1 + .../connection_adapters/postgresql/cast.rb | 16 +++++ .../connection_adapters/postgresql/oid.rb | 9 +++ .../connection_adapters/postgresql/quoting.rb | 8 ++- .../connection_adapters/postgresql_adapter.rb | 13 +++- .../test/cases/adapters/postgresql/json_test.rb | 69 ++++++++++++++++++++++ activerecord/test/cases/schema_dumper_test.rb | 7 +++ .../test/schema/postgresql_specific_schema.rb | 11 +++- 9 files changed, 135 insertions(+), 4 deletions(-) create mode 100644 activerecord/test/cases/adapters/postgresql/json_test.rb (limited to 'activerecord') diff --git a/activerecord/CHANGELOG.md b/activerecord/CHANGELOG.md index a4859bb3ca..3cb021cf1b 100644 --- a/activerecord/CHANGELOG.md +++ b/activerecord/CHANGELOG.md @@ -14,6 +14,11 @@ *Ian Lesperance* +* Allow JSON columns to be created in PostgreSQL and properly encoded/decoded + to/from database. + + *Dickson S. Guedes* + * Fix time column type casting for invalid time string values to correctly return nil. *Adam Meehan* diff --git a/activerecord/lib/active_record/connection_adapters/column.rb b/activerecord/lib/active_record/connection_adapters/column.rb index d0237848c7..0390168461 100644 --- a/activerecord/lib/active_record/connection_adapters/column.rb +++ b/activerecord/lib/active_record/connection_adapters/column.rb @@ -124,6 +124,7 @@ module ActiveRecord when :boolean then "#{klass}.value_to_boolean(#{var_name})" when :hstore then "#{klass}.string_to_hstore(#{var_name})" when :inet, :cidr then "#{klass}.string_to_cidr(#{var_name})" + when :json then "#{klass}.string_to_json(#{var_name})" else var_name end end diff --git a/activerecord/lib/active_record/connection_adapters/postgresql/cast.rb b/activerecord/lib/active_record/connection_adapters/postgresql/cast.rb index 20e482dcc2..b59195f98a 100644 --- a/activerecord/lib/active_record/connection_adapters/postgresql/cast.rb +++ b/activerecord/lib/active_record/connection_adapters/postgresql/cast.rb @@ -37,6 +37,22 @@ module ActiveRecord end end + def json_to_string(object) + if Hash === object + ActiveSupport::JSON.encode(object) + else + object + end + end + + def string_to_json(string) + if String === string + ActiveSupport::JSON.decode(string) + else + string + end + end + def string_to_cidr(string) if string.nil? nil diff --git a/activerecord/lib/active_record/connection_adapters/postgresql/oid.rb b/activerecord/lib/active_record/connection_adapters/postgresql/oid.rb index 6657491c06..b8e7687b21 100644 --- a/activerecord/lib/active_record/connection_adapters/postgresql/oid.rb +++ b/activerecord/lib/active_record/connection_adapters/postgresql/oid.rb @@ -145,6 +145,14 @@ module ActiveRecord end end + class Json < Type + def type_cast(value) + return if value.nil? + + ConnectionAdapters::PostgreSQLColumn.string_to_json value + end + end + class TypeMap def initialize @mapping = {} @@ -244,6 +252,7 @@ module ActiveRecord register_type 'polygon', OID::Identity.new register_type 'circle', OID::Identity.new register_type 'hstore', OID::Hstore.new + register_type 'json', OID::Json.new register_type 'cidr', OID::Cidr.new alias_type 'inet', 'cidr' diff --git a/activerecord/lib/active_record/connection_adapters/postgresql/quoting.rb b/activerecord/lib/active_record/connection_adapters/postgresql/quoting.rb index 1ae7443cde..152258a2f4 100644 --- a/activerecord/lib/active_record/connection_adapters/postgresql/quoting.rb +++ b/activerecord/lib/active_record/connection_adapters/postgresql/quoting.rb @@ -22,6 +22,7 @@ module ActiveRecord when Hash case column.sql_type when 'hstore' then super(PostgreSQLColumn.hstore_to_string(value), column) + when 'json' then super(PostgreSQLColumn.json_to_string(value), column) else super end when IPAddr @@ -66,8 +67,11 @@ module ActiveRecord return super unless 'bytea' == column.sql_type { :value => value, :format => 1 } when Hash - return super unless 'hstore' == column.sql_type - PostgreSQLColumn.hstore_to_string(value) + case column.sql_type + when 'hstore' then PostgreSQLColumn.hstore_to_string(value) + when 'json' then PostgreSQLColumn.json_to_string(value) + else super + end when IPAddr return super unless ['inet','cidr'].includes? column.sql_type PostgreSQLColumn.cidr_to_string(value) diff --git a/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb b/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb index 956e83bfd8..c508afb33e 100644 --- a/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb +++ b/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb @@ -106,6 +106,9 @@ module ActiveRecord # Hstore when /\A'(.*)'::hstore\z/ $1 + # JSON + when /\A'(.*)'::json\z/ + $1 # Object identifier types when /\A-?\d+\z/ $1 @@ -201,6 +204,9 @@ module ActiveRecord # UUID type when 'uuid' :uuid + # JSON type + when 'json' + :json # Small and big integer types when /^(?:small|big)int$/ :integer @@ -267,6 +273,10 @@ module ActiveRecord def uuid(name, options = {}) column(name, 'uuid', options) end + + def json(name, options = {}) + column(name, 'json', options) + end end ADAPTER_NAME = 'PostgreSQL' @@ -290,7 +300,8 @@ module ActiveRecord inet: { name: "inet" }, cidr: { name: "cidr" }, macaddr: { name: "macaddr" }, - uuid: { name: "uuid" } + uuid: { name: "uuid" }, + json: { name: "json" } } include Quoting diff --git a/activerecord/test/cases/adapters/postgresql/json_test.rb b/activerecord/test/cases/adapters/postgresql/json_test.rb new file mode 100644 index 0000000000..7907b93479 --- /dev/null +++ b/activerecord/test/cases/adapters/postgresql/json_test.rb @@ -0,0 +1,69 @@ +# encoding: utf-8 + +require "cases/helper" +require 'active_record/base' +require 'active_record/connection_adapters/postgresql_adapter' + +class PostgresqlJSONTest < ActiveRecord::TestCase + class JsonDataType < ActiveRecord::Base + self.table_name = 'json_data_type' + end + + def setup + @connection = ActiveRecord::Base.connection + begin + @connection.create_table('json_data_type') do |t| + t.json 'payload', :default => {} + end + rescue ActiveRecord::StatementInvalid + return skip "do not test on PG without json" + end + @column = JsonDataType.columns.find { |c| c.name == 'payload' } + end + + def teardown + @connection.execute 'drop table if exists json_data_type' + end + + def test_column + assert_equal :json, @column.type + end + + def test_type_cast_json + assert @column + + data = "{\"a_key\":\"a_value\"}" + hash = @column.class.string_to_json data + assert_equal({'a_key' => 'a_value'}, hash) + assert_equal({'a_key' => 'a_value'}, @column.type_cast(data)) + + assert_equal({}, @column.type_cast("{}")) + assert_equal({'key'=>nil}, @column.type_cast('{"key": null}')) + assert_equal({'c'=>'}','"a"'=>'b "a b'}, @column.type_cast(%q({"c":"}", "\"a\"":"b \"a b"}))) + end + + def test_rewrite + @connection.execute "insert into json_data_type (payload) VALUES ('{\"k\":\"v\"}')" + x = JsonDataType.first + x.payload = { '"a\'' => 'b' } + assert x.save! + end + + def test_select + @connection.execute "insert into json_data_type (payload) VALUES ('{\"k\":\"v\"}')" + x = JsonDataType.first + assert_equal({'k' => 'v'}, x.payload) + end + + def test_select_multikey + @connection.execute %q|insert into json_data_type (payload) VALUES ('{"k1":"v1", "k2":"v2", "k3":[1,2,3]}')| + x = JsonDataType.first + assert_equal({'k1' => 'v1', 'k2' => 'v2', 'k3' => [1,2,3]}, x.payload) + end + + def test_null_json + @connection.execute %q|insert into json_data_type (payload) VALUES(null)| + x = JsonDataType.first + assert_equal(nil, x.payload) + end +end diff --git a/activerecord/test/cases/schema_dumper_test.rb b/activerecord/test/cases/schema_dumper_test.rb index 92084d3eb6..68e88372f3 100644 --- a/activerecord/test/cases/schema_dumper_test.rb +++ b/activerecord/test/cases/schema_dumper_test.rb @@ -236,6 +236,13 @@ class SchemaDumperTest < ActiveRecord::TestCase end end + def test_schema_dump_includes_json_shorthand_definition + output = standard_dump + if %r{create_table "postgresql_json_data_type"} =~ output + assert_match %r|t.json "json_data", :default => {}|, output + end + end + def test_schema_dump_includes_inet_shorthand_definition output = standard_dump if %r{create_table "postgresql_network_address"} =~ output diff --git a/activerecord/test/schema/postgresql_specific_schema.rb b/activerecord/test/schema/postgresql_specific_schema.rb index 5f01f1fc50..2d8c0aa69a 100644 --- a/activerecord/test/schema/postgresql_specific_schema.rb +++ b/activerecord/test/schema/postgresql_specific_schema.rb @@ -1,7 +1,7 @@ ActiveRecord::Schema.define do %w(postgresql_tsvectors postgresql_hstores postgresql_arrays postgresql_moneys postgresql_numbers postgresql_times postgresql_network_addresses postgresql_bit_strings postgresql_uuids - postgresql_oids postgresql_xml_data_type defaults geometrics postgresql_timestamp_with_zones postgresql_partitioned_table postgresql_partitioned_table_parent).each do |table_name| + postgresql_oids postgresql_xml_data_type defaults geometrics postgresql_timestamp_with_zones postgresql_partitioned_table postgresql_partitioned_table_parent postgresql_json_data_type).each do |table_name| execute "DROP TABLE IF EXISTS #{quote_table_name table_name}" end @@ -82,6 +82,15 @@ _SQL _SQL end + if 't' == select_value("select 'json'=ANY(select typname from pg_type)") + execute <<_SQL + CREATE TABLE postgresql_json_data_type ( + id SERIAL PRIMARY KEY, + json_data json default '{}'::json + ); +_SQL + end + execute <<_SQL CREATE TABLE postgresql_moneys ( id SERIAL PRIMARY KEY, -- cgit v1.2.3