Data Operations¶

The ERMrest data operations manipulate tabular data structured according to the existing entity-relationship model already configured as schemata resources in the catalog.

In the following examples, we illustrate the use of specific data formats. However, content negotiation allows any of the supported tabular data formats to be used in any request or response involving tabular data.

Entity Resolution¶

The GET operation is used to resolve an externally conveyed RID column value, using an entity_rid resource data name of the form:

service /catalog/ cid [ @ revision ] /entity_rid/ rid

On success the response for an existing entity is:

HTTP/1.1 200 OK
Content-Type: application/json

{
  "schema_name": sname,
  "table_name": tname,
  "RID": rid
}

This result indicates the schema name _sname and table name tname where the given rid can be located. Given such a result, the client may attempt to retrieve the resolved entity content via the entity retrieval API using this form of resource name:

service /catalog/ cid [ @ revision ] /entity/ sname : tname /RID= rid

to address the resolved entity within the same catalog or catalog snapshot it was resolved in.

However, if resolution determines that the entity existed previously and no longer exists within the addressed catalog or catalog snapshot, a slightly different success response is generated:

HTTP/1.1 200 OK
Content-Type: application/json

{
  "schema_name": sname,
  "table_name": tname,
  "RID": rid,
  "deleted_at": _dtime_,
  "last_visible_at": _vtime_,
  "last_visible_snaptime": _vrevision_
}

This result indicates the sname and tname for the rid as previously; it additionally indicates a deletion timestamp dtime when the entity was removed, a last-visible timestamp vtime when the entity was visible with its last pre-deletion state, and the vrevision revision number of the catalog snapshot containing this last visible state. Given such a result, the client may attempt to retrieve the last-visible resolved entity content via the following resource name:

service /catalog/ cid @ vrevision /entity/ sname : tname /RID= rid

to address the resolved entity within the vrevision catalog snapshot containing its final visible state.

Typical error response codes include:

404 Not Found

Entity Creation¶

The POST operation is used to create new entity records in a table, using an entity resource data name of the form:

service /catalog/ cid /entity/ table name
service /catalog/ cid /entity/ schema name : table name

In this operation, complex entity paths with filter and linked entity elements are not allowed. The request input includes all columns of the table, thus supplying full entity records of data:

POST /ermrest/catalog/42/entity/schema_name:table_name HTTP/1.1
Host: www.example.com
Content-Type: text/csv
Accept: text/csv

column1,column2
1,foo
2,foo
3,bar
4,baz

The input data MUST observe the table definition including column names and types, uniqueness constraints for key columns, and validity of any foreign key references. It is an error for any existing key in the stored table to match any key in the input data, as this would denote the creation of multiple rows with the same keys.

For convenience, the ERMrest system columns (RID, RCT, RMT, RCB, RMB) are implicitly supplied with default values during entity creation.

On success, the response is:

HTTP/1.1 200 OK
Content-Type: text/csv

column1,column2
1,foo
2,foo
3,bar
4,baz

Typical error response codes include:

400 Bad Request
409 Conflict
403 Forbidden
401 Unauthorized

Entity Creation with Defaults¶

The POST operation is also used to create new entity records in a table where some values are assigned default values, using an entity resource data name of the form:

service /catalog/ cid /entity/ table name ?defaults= column name
service /catalog/ cid /entity/ schema name : table name ?defaults= column name , …
service /catalog/ cid /entity/ table name ?defaults= column name &nondefaults= column name , …

In this operation, complex entity paths with filter and linked entity elements are not allowed. The request input includes all columns of the table, thus supplying full entity records of data:

POST /ermrest/catalog/42/entity/schema_name:table_name?defaults=column1 HTTP/1.1
Host: www.example.com
Content-Type: text/csv
Accept: text/csv

column1,column2
1,foo
1,bar
1,baz
1,bof

The input data MUST observe the table definition including column names and types, uniqueness constraints for key columns, and validity of any foreign key references. If multiple columns are to be set to defaults, they are provided as a comma-separated list of column names on the right-hand-side of the defaults=... query parameter binding.

All columns should still be present in the input. However, the values for the column (or columns) named in the defaults query parameter will be ignored and server-assigned values generated instead. It is an error for any existing key in the stored table to match any key in the input data, as this would denote the creation of multiple rows with the same keys.

For convenience, columns lacking enumerate privilege and the ERMrest system columns (RID, RCT, RMT, RCB, RMB) are implicitly supplied with default values during entity creation, even if they are not listed in the defaults query parameter. The optional nondefaults query parameter can be used to suppress this implicit behavior, e.g. to allow a client to import or relocate table content from another catalog while preserving its originally assigned RID, RCT, and RCB values. NOTE: the system will refuse attempts by clients to write values for RMT and RMB regardless of circumstance. Also, the ability to write values RID, RCT, and RCB is limited to the catalog owner except that other users may claim RID values they have pre-allocated in the special public.ERMrest_RID_Lease table.

On success, the response is:

HTTP/1.1 200 OK
Content-Type: text/csv

column1,column2
4,foo
5,bar
6,baz
7,bof

In this example, a presumed serial4 type used for column1 would lead to a sequence of serial numbers being issued for the default column.

Typical error response codes include:

400 Bad Request
409 Conflict
403 Forbidden
401 Unauthorized

Idempotent Entity Creation¶

The POST operation is also used to create new entity records unless they already exist, using an entity resource data name of the form:

service /catalog/ cid /entity/ table name ?onconflict=skip
service /catalog/ cid /entity/ schema name : table name ?onconflict=skip
service /catalog/ cid /entity/ table name ?defaults= column name &onconflict=skip

In this request variant, input rows with keys matching existing data are presumed to be redundant and are discarded. No attempt is made to compare or preserve non-key material from the discarded inputs. This onconflict=skip behavior may be mixed with the other defaults and nondefaults query parameters described previously.

The input and output are identical to previous descriptions, except that discarded input rows will not be represented in the response.

Entity Update¶

The PUT operation is used to update entity records in a table, using an entity resource data name of the form:

service /catalog/ cid /entity/ table name
service /catalog/ cid /entity/ schema name : table name

In this operation, complex entity paths with filter and linked entity elements are not allowed. The request input includes all columns of the table, thus supplying full entity records of data:

PUT /ermrest/catalog/42/entity/schema_name:table_name HTTP/1.1
Host: www.example.com
Content-Type: text/csv
Accept: text/csv

column1,column2
1,foo
2,foo
3,bar
4,baz

The input data MUST observe the table definition including column names and types, uniqueness constraints for key columns, and validity of any foreign key references. Any input row with keys matching an existing stored row will cause an update of non-key columns to match the input row. Any input row with keys not matching an existing stored row will cause creation of a new row.

On success, the response is:

HTTP/1.1 200 OK
Content-Type: text/csv

column1,column2
1,foo
2,foo
3,bar
4,baz

Typical error response codes include:

400 Bad Request
409 Conflict
403 Forbidden
401 Unauthorized

Entity Retrieval¶

The GET operation is used to retrieve entity records, using an entity resource data name of the form:

service /catalog/ cid [ @ revision ] /entity/ path

In this operation, complex entity paths with filter and linked entity elements are allowed, for example:

GET /ermrest/catalog/42/entity/table1/column1=value1/table2/column2=value2 HTTP/1.1
Host: www.example.com
Accept: text/csv

On success, the response is:

HTTP/1.1 200 OK
Content-Type: text/csv

column1,column2
1,foo
2,foo
3,bar
4,baz

Each result row will correspond to an entity in the entity set denoted by path. This will be a filtered subset of entities from the table instance context of path considering all filtering and joining criteria.

Typical error response codes include:

409 Conflict
403 Forbidden
401 Unauthorized

Entity Deletion¶

The DELETE operation is used to delete entity records, using an entity resource data name of the form:

service /catalog/ cid /entity/ path

In this operation, complex entity paths with filter and linked entity elements are allowed.

DELETE /ermrest/catalog/42/entity/table1/column1=value1/table2/column2=value2 HTTP/1.1
Host: www.example.com

On success, the response is:

HTTP/1.1 204 No Content

The result of the operation is that each of the entity records denoted by path are deleted from the catalog. This operation only (directly) affects the right-most table instance context of path. Additional joined entity context may be used to filter the set of affected rows, but none of the contextual table instances are targeted by deletion. However, due to constraints configured in the model, it is possible for a deletion to cause side-effects in another table, e.g. deletion of entities with key values causing foreign key references to those entities to also be processed by a cascading delete or update.

Typical error response codes include:

409 Conflict
403 Forbidden
401 Unauthorized

Attribute Retrieval¶

The GET operation is used to retrieve projected attribute records, using an attribute resource data name of the form:

service /catalog/ cid [ @ revision ] /attribute/ path / projection , …

In this operation, complex entity paths with filter and linked entity elements are allowed and projection can draw values from any entity element of path, for example:

GET /ermrest/catalog/42/attribute/A:=table1/column1=value1/table2/column2=value2/x:=A:column1,y:=column3 HTTP/1.1
Host: www.example.com
Accept: text/csv

where output column x is drawn from column column1 of the table1 table instance aliased with A, while output column y is drawn from column column3 of the table2 table instance context of path.

On success, the response is:

HTTP/1.1 200 OK
Content-Type: text/csv

x,y
1,foo
2,foo
3,bar
4,baz

Each result row will correspond to an entity in the entity set denoted by path but the result row will be populated with the specified projection values rather than the denoted entity fields. This will be a filtered subset of entities from the table instance context of path considering all filtering and joining criteria.

Typical error response codes include:

409 Conflict
403 Forbidden
401 Unauthorized

Attribute Deletion¶

The DELETE operation is used to clear attributes to their default value (usually NULL), using an attribute resource data name of the form:

service /catalog/ cid /attribute/ path / target , …

In this operation, complex entity paths with filter and linked entity elements are allowed but only attributes from the path entity context can be specified as the target for deletion, rather than the generalized projection possible with retrieval.

DELETE /ermrest/catalog/42/attribute/table1/column1=value1/table2/column2=value2/column3 HTTP/1.1
Host: www.example.com

On success, the response is:

HTTP/1.1 204 No Content

The result of the operation is that each of the entity records denoted by path are modified in the catalog, changing their target columns to default value (usually NULL or whatever default value is configured for that column in the model). This operation only (directly) affects the right-most table instance context of path. Additional joined entity context may be used to filter the set of affected rows, but none of the contextual table instances are targeted by deletion. However, due to constraints configured in the model, it is possible for a deletion to cause side-effects in another table, e.g. modification of key values causing foreign key references to those entities to also be processed by a cascading update.

Typical error response codes include:

409 Conflict
403 Forbidden
401 Unauthorized

Attribute Group Retrieval¶

The GET operation is used to retrieve projected attribute group records, using an attributegroup resource data name of the form:

service /catalog/ cid [ @ revision ] /attributegroup/ path / group key , …
service /catalog/ cid [ @ revision ] /attributegroup/ path / group key , … ; projection , …

In this operation, complex entity paths with filter and linked entity elements are allowed, for example:

GET /ermrest/catalog/42/attributegroup/A:=table1/column1=value1/table2/column2=value2/y:=column3;x:=A:cnt(column1),z:=A:column1 HTTP/1.1
Host: www.example.com
Accept: text/csv

On success, the response is:

HTTP/1.1 200 OK
Content-Type: text/csv

y,x,z
foo,2,1
bar,1,3
baz,1,4

Each result row will correspond to a distinct group key tuple existing in the joined records denoted by path and the result row will be populated with the group key tuple and additional projection values. Unlike the entity and attribute resource spaces which have outputs corresponding to entities in the context of path, the attributegroup resource space changes the semantics of path to denote a permutation set of joined rows based on all the entity-relationship linkages between the elements of path; this permutation set is further sub-setted by any filters in path.

Typical error response codes include:

409 Conflict
403 Forbidden
401 Unauthorized

Attribute Group Update¶

The PUT operation is used to update attributes in a table, using an attributegroup resource data name of the form:

service /catalog/ cid /attributegroup/ path / group key , … ; target , …

In this operation, complex entity paths with filter and linked entity elements are not allowed:

PUT /ermrest/catalog/42/attributegroup/table/column1;column2 HTTP/1.1
Host: www.example.com
Content-Type: text/csv
Accept: text/csv

column1,column2
1,foo
2,foo
3,bar
4,baz

The input data MUST NOT have more than one row with the same group key tuple of values. Any input row with group key columns matching an existing stored row will cause an update of target columns to match the input row. Any input row with group key columns not matching an existing stored row will cause an error.

On success, the response contains updated row information:

HTTP/1.1 200 OK
Content-Type: text/csv

column1,column2
1,foo
2,foo
3,bar
4,baz

TODO: clarify the meaning of this result content.

Typical error response codes include:

400 Bad Request
409 Conflict
403 Forbidden
401 Unauthorized

Attribute Group Update with Renaming¶

As with retrieval of attribute groups, update supports renaming of stored columns within the external representation, so that it is even possible to rewrite the key columns as in this example:

PUT /ermrest/catalog/42/attributegroup/table1/original:=column1;replacement:=column1 HTTP/1.1
Host: www.example.com
Content-Type: text/csv
Accept: text/csv

original,replacement
foo,foo-prime
bar,bar-prime
baz,baz-prime

Here, the stored rows with column1 matching values in original of the input will have column1 rewritten to the corresponding value in replacement.

On success, the response is:

HTTP/1.1 200 OK
Content-Type: text/csv

original,replacement
foo,foo-prime
bar,bar-prime
baz,baz-prime

Aggregate Retrieval¶

The GET operation is used to retrieve projected aggregates, using an aggregate resource data name of the form:

service /catalog/ cid [ @ revision ] /aggregate/ path / projection , …

In this operation, complex entity paths with filter and linked entity elements are allowed, for example:

GET /ermrest/catalog/42/aggregate/A:=table1/column1=value1/table2/column2=value2/y:=cnt_d(column2),x:=A:cnt(column1),z:=A:column1 HTTP/1.1
Host: www.example.com
Accept: text/csv

On success, the response is:

HTTP/1.1 200 OK
Content-Type: text/csv

y,x,z
3,4,1

A single result row will summarize the joined records denoted by path and the result row will be populated with projection values. Like the attributegroup resource space, the aggregate resource space changes the semantics of path to denote a permutation set of joined rows based on all the entity-relationship linkages between the elements of path; this permutation set is further sub-setted by any filters in path and then reduced to a single aggregate summary value by projection values using an aggregate function or by choosing an arbitrary example value for projection values referencing a bare column.

Typical error response codes include:

409 Conflict
403 Forbidden
401 Unauthorized