Provenance API - Table and View Operations

This document describes how to create tables associated with DDCs. All execution methods use JSON-RPC v2.0.

Create table prov.new_ddc

The following is an example of a JSON-RPC request to create a table on provenance.

Example request

CREATE TABLE test (
  id int,
  name varchar,
  password varchar,
  phone varchar,
  email varchar,
  UNIQUE (id)
);

An example of a JSON-RPC request to create a table like the one above is shown below.

{
  "jsonrpc": "2.0",
  "method": "prov.new_ddc",
  "params": {
    "ddc_label": "ddc:test",
    "columns": [
      {"column_name":"id", "data_type":"int"},
      {"column_name":"name", "data_type":"varchar"},
      {"column_name":"password", "data_type":"varchar"},
      {"column_name":"phone", "data_type":"varchar"},
      {"column_name":"email", "data_type":"varchar"}],
    "key": "id"
  },
  "id": "provenance_jsonrpc_id"
}

If records is specified at the same time, initial data is registered.

{
  "jsonrpc": "2.0",
  "method": "prov.new_ddc",
  "params": {
    "ddc_label": "ddc:test",
    "columns": [
      {"column_name":"id", "data_type":"int", "nullable": false},
      {"column_name":"name", "data_type":"varchar", "need_crypto": true},
      {"column_name":"password", "data_type":"varchar"},
      {"column_name":"phone", "data_type":"varchar"},
      {"column_name":"email", "data_type":"varchar"}],
    "key": "id",
    "records":[
      {"id": 1, "name": "yamada tarou","password": "password_abcde", "phone": "00-0000-0000", "email": "foobar@example.com"},
      {"id": 1, "name": "yamada tarou2","password": "password_abcde2", "phone": "00-0000-0002", "email": "foobar2@example.com"}
    ]
  },
  "id": "provenance_jsonrpc_id"
}

Parameters

The following parameters can be specified for params.

parameter name	data type	content	default value
ddc_label	string	DDC名	必須
columns	[object]	カラム名を `column_name`,型を`data_type`で指定する。 `data_type`に指定できるのは`varchar`, `int`, `geometry`, `double precision`, `timestamp with time zone`, `timestamp` の何れかです。 `varhcar` を使う場合で暗号化したいときは、`need_crypto` を true とします。	Required (all columns and types)
key	string	column_name of the column specified in columns that you want to make unique.
records	[object]	Set of values keyed by the value of column_name specified in columns

Get column information prov.get_ddc_columns

The following is an example of a JSON-RPC request to get column information for a table that has already been created.

{
  "jsonrpc": "2.0",
  "method": "prov.get_ddc_columns",
  "params": {"ddc_label": "ddc:test"},
  "id": "provenance_jsonrpc_id"
}

The format of the response is as follows. If varchar is specified for data_type and furthermore encryption is specified by using true for need_crypto, the data_type in the response will be bytea.

{
  "result":[
  {"column_name": "id", "data_type": "integer", "nullable": false, "attnum": 1},
  {"column_name": "name", "data_type": "bytea", "nullable": false, "attnum": 2},
  {"column_name": "password", "data_type": "character varying", "nullable": false, "attnum": 3},
  {"column_name": "phone", "data_type": "character varying", "nullable": false, "attnum": 4},
  {"column_name": "email", "data_type": "character varying", "nullable": false, "attnum": 5}]
}

Parameters

The following parameters are specified in params.

parameter name	data type	content	default value
ddc_label	string	DDC name	required
alias	string	prefix string specified by AS

Count the number of records prov.count_ddc_records

The following is an example of a JSON-RPC request to get the number of records in a table that has already been created.

{
  "jsonrpc": "2.0",
  "method": "prov.count_ddc_records",
  "params": {"ddc_label": "ddc:test"},
  "id": "provenance_jsonrpc_id"
}

The format of the response is as follows

{
  "result": 0
}

Parameters

The following parameters are specified in params.

parameter name	data type	content	default value
ddc_label	string	DDC name	required
session_id	integer	Specify if the session ID is known

Partial selection of records/columns prov.set_ddc

The following is an example of a JSON-RPC request to partially select and retrieve a table that has already been created.

CREATE VIEW subtable AS
SELECT id, name, email
FROM test
WHERE id <= 100;

Here is an example of a request to obtain the above image.

{
  "jsonrpc": "2.0",
  "method": "prov.set_ddc",
  "params": {"ddc_label": "ddc:subtable","source": "ddc:test", "ddc_type":"view", "columns":["id", "name","email"], "conds":{"<=":["id", 100]}},
  "id": "provenance_jsonrpc_id"
}

Parameters

The following parameters are specified in params. | parameter name | data type | content | default value | |-----------|--------|---------------------------------------------------------|-----| | ddc_label | string | DDC name | required | | source | string | Specify the name of an existing table. If the schema is known, specify it in the form . if the schema is known. | required | | ddc_type | string | One of link, table, or view. | required | | columns | [string] | To create ddc using only some columns, specify column names as an array of strings | | | conds | object | Specify conditions as an array with <=, >=, <, >, = as keys | required | | no_exec | boolean | Any of true, false | required |

add records prov.put_ddc_records

The following is an example of a JSON-RPC request to add records to a table that has already been created.

INSERT INTO test (id, name, password, phone, email) VALUES
    (1,'石塚 洋次','WJI67hUi','080-4715-4302','WzxKRGR6J@sample.com'),
    (2,'近藤 恭之','pHSBKdvB','090-3132-9449','KefD6KU@test.net'),
    (3,'玉井 真帆','SieMcQZi','090-2613-4478','cTC6Gu7a@test.com');

Here is an example of a request to add the above image: The second request counts the rows added.

{
  "jsonrpc": "2.0",
  "method": "prov.put_ddc_records",
  "params": {"ddc_label": "ddc:test",
             "records":[[1,"石塚 洋次","WJI67hUi","080-4715-4302","WzxKRGR6J@sample.com"],
                        [2,"近藤 恭之","pHSBKdvB","090-3132-9449","KefD6KU@test.net"],
                        [3,"玉井 真帆","SieMcQZi","090-2613-4478","cTC6Gu7a@test.com"]],
             "on_conflict": "error"
            },
  "id": "provenance_jsonrpc_id"
}

{
  "jsonrpc": "2.0",
  "method": "prov.count_ddc_records",
  "params": {"ddc_label": "ddc:test"},
  "id": "provenance_jsonrpc_id"
}

An example result of count_ddc_records is shown below.

{
  "result": 3,
  "id": "provenance_jsonrpc_id",
  "jsonrpc": "2.0"
}

Parameters

The following parameters are specified in the params of prov.put_ddc_records.

parameter name	data type	content	default value
ddc_label	string	DDC name	required
records	[object]	set of values with the value of column_name specified in columns such as `new_ddc` as a key

on_conflict	string	One of `update`, `nothing`, `error`	required

The params of prov.count_ddc_records should have the following parameters

parameter name	data type	content	default value
ddc_label	string	DDC名

Add a record from another table to a table prov.merge_ddc

The following is an example of a JSON-RPC request to add a record of another table to a table that has already been created.

INSERT INTO xrain
SELECT * FROM xrain_recent;

Here is an example of a request to add a record from another table, as shown in the image above. the second request counts the rows added.

{
  "jsonrpc": "2.0",
  "method": "prov.merge_ddc",
  "params": {"source": "ddc:xrain_recent",
             "target": "ddc:xrain",
             "on_conflict": "nothing",
             "no_exec": false},
  "id": "provenance_jsonrpc_id"
}

Parameters

The following parameters are specified in params.

parameter name	data type	content	default value
target	string	DDC name	required
source	string	DDC name	required
no_exec	boolean	One of true, false	required
on_conflict	string	One of `update`, `nothing`, `error`	required

Get records prov.get_ddc_records

The following is an example of a JSON-RPC request to retrieve table records for a table that has already stored data.

SELECT * FROM test;

Here is an example of a request to retrieve a record with the image shown above.

{
  "jsonrpc": "2.0",
  "method": "prov.get_ddc_records",
  "params": {"ddc_label": "ddc:test"},
  "id": "provenance_jsonrpc_id"
}

Parameters

The following parameters can be specified for params.

parameter name	data type	content	default value
ddc_label	string	DDC名	必須

Table join prov.join_ddc

The following is an example of a JSON-RPC request to create a joined table for two tables that have already stored data.

CREATE TABLE xrain_domingo AS
SELECT
  l.start_datetime, l.rain_map AS rast,
  r.zoomlevel, r.tile_x, r.tile_y, r.start_datetime
FROM xrain l
INNER JOIN domingo r ON l.start_datetime = r.start_datetime;

Here is an example of a request to join tables with the above image. The left alias is fixed at l and the right alias is fixed at r.

{
  "jsonrpc": "2.0",
  "method": "prov.join_ddc",
  "params": {"output_ddc": "ddc:xrain_domingo",
            "left_ddc":"ddc:xrain", "right_ddc":"ddc:domingo",
            "columns":["l.start_datetime", "l.rain_map as rast",
        "r.zoomlevel", "r.tile_x", "r.tile_y", "r.area_q"],
            "conds":{"=": ["l.start_datetime", "r.start_datetime"]},
            "join_type": "inner"},
  "id": "provenance_jsonrpc_id"
}

Parameters

The following parameters are specified in params.

parameter name	data type	content	default value
output_ddc	string	DDC name	required
left_ddc	string	DDC name	required
right_ddc	string	DDC name	required
columns	[string]	Specify the column name to be retrieved as alias `l` for the left table and `r` for the right table	required
conds	object	Specify conditions as an array with `<=`, `>=`, `<`, `>`, `=` as keys	required
join_type	string	One of `inner` or `outer`.	required

Delete table prov.delete_ddc

The following is an example of a JSON-RPC request to delete a table that has already been created.

DROP TABLE test;

Here is an example of a request to delete a table with the above image.

{
  "jsonrpc": "2.0",
  "method": "prov.delete_ddc",
  "params": {"ddc_label": "ddc:test"},
  "id": "provenance_jsonrpc_id"
}

Parameters

The following parameters are specified in params.

parameter name	data type	content	default value
ddc_label	string	DDC name	required

Transaction prov.begin_session, prov.resume_session, prov.begin_or_resume_session, prov.rollback, prov.commit

An example of a JSON-RPC request for controlling database transactions is shown below.

BEGIN TRANSACTION;
...
ROLLBACK;
...
COMMIT;

Here is an example of a request to control the above image.

{
  "jsonrpc": "2.0",
  "method": "prov.begin_session",
  "id": "provenance_jsonrpc_id"
}

{
  "jsonrpc": "2.0",
  "method": "prov.resume_session",
  "id": "provenance_jsonrpc_id"
}

{
  "jsonrpc": "2.0",
  "method": "prov.begin_or_resume_session",
  "id": "provenance_jsonrpc_id"
}

{
  "jsonrpc": "2.0",
  "method": "prov.rollback",
  "id": "provenance_jsonrpc_id"
}

{
  "jsonrpc": "2.0",
  "method": "prov.commit",
  "id": "provenance_jsonrpc_id"
}

Convert table name and ddc prov.set_ddc, prov.resolve_ddc

The following is an example of a JSON-RPC request to add ddc to an existing table.

{
  "jsonrpc": "2.0",
  "method": "prov.set_ddc",
  "params":{"ddc_label":"ddc:test","source":"public.test","ddc_type":"link", "no_exec": false},
  "id": "provenance_jsonrpc_id"
}

An example of a JSON-RPC request to retrieve the entity (table/view) pointed to by ddc is shown below.

{
  "jsonrpc": "2.0",
  "method": "prov.resolve_ddc",
  "params":{"ddc_label":"ddc:test"},
  "id": "provenance_jsonrpc_id"
}

An example result of resolve_ddc is shown below.

{
  "result": {"table": {"ddc_info":["realname"]}},
  "id": "provenance_jsonrpc_id",
  "jsonrpc": "2.0"
}

Parameters

Use the foregoing for the params of prov.set_ddc.

Use the following parameters for the params of prov.resolve_ddc.

parameter name	data type	content	default value
ddc_label	string	DDC name	required