"attributes": New generic table for misc. information polymorphic relations

[dlebauer]

Table name: miscelaneyiis please propose ...

Use cases:

• adding synonyms to variables
• adding additional metadata to cultivars (e.g. lineage, etc)
• PEcAn, add all parameters that we currently store in xml
• generally whereever we have notes that we use to store additional info
• (eventually) could deprecate many fields that are sparsely populated

Implementation (Option 3)

Fields

• type: table name
• type_id: id in table name
• value: json

unique type, type_id

Plus standard ruby stuff

• id: bigint
• updated_at
• created_at

---

Other options originally considered

Option 1
Four fields:

• type: table name (key unique)
• type_id: id in table name
• key: character
• value: charcater

unique type, type_id, key

Option 2

• type: table name
• type_id: id in table name
• key: character
• value_string:
• value_float:
• value_int:
• ...

unique type, type_id, key

Option 2.1

• type: table name
• type_id: id in table name
• key: character
• type: (char) data type
• value: (char)

Option 3

• type: table name
• type_id: id in table name
• value: json

unique type, type_id

Plus standard ruby stuff

• id: bigint
• updated_at
• created_at

[dlebauer]

From meeting today, it sounds like Option 3 is preferred:

• type: table name
• type_id: id in table name
• value: jsonb

unique type, type_id

Here is a description of the json data type https://www.postgresql.org/docs/9.3/static/datatype-json.html and a list of json operations: https://www.postgresql.org/docs/9.3/static/functions-json.html in the postgres documentation.

[robkooper]

New database will be called attributes

Make sure to use something following like this to upsert into the database:

INSERT INTO attributes(type, type_id, value) VALUES ('sites', '1', '{"set":{"some_prop": 88}}')
ON CONFLICT (rec_id) DO UPDATE jsonb_set(attributes.value, array['set','some_prop'], '88', true) WHERE type='sites' AND type_id=1;

[robkooper]

Table will be called attributes

[ashiklom]

A possible application for loading raster formats, per request of @serbinsh:

• Create a new format called raster_projection_x
• Describe the format in attributes table: INSERT INTO attributes (type, id, value) VALUES ("formats", XXXX, "{'gis' : {'epsg' : 12345, 'reference_lat' : 45, 'reference_lon' : -83}, 'comment' : 'I hate this format'''}");
• Add special case for format raster_projection_x to load_data. If it detects that as the format, it can extract EPSG with something like SELECT value::json#>'{"gis", "epsg"}' FROM attributes INNER JOIN formats ... WHERE format_name = "raster_projection_x".
• Then, do your custom processing...

(This is very pseudo-code-y, but hopefully you get the idea?)

[serbinsh]

@dlebauer @ashiklom this looks like a great option!!

[ashiklom]

For reference, here's a nice SO answer explaining how to update PostgreSQL JSON records (PostgreSQL version >= 9.5): https://stackoverflow.com/a/35349699/2477097

In a nutshell, use jsonb_set function:

UPDATE attributes SET value = jsonb_set(value, '{tag}', '5') WHERE ...