Table and View Operations

This document has been machine translated.

This document describes how to create a table associated with a DDC.

All execution methods use JSON-RPC v2.0.

Create the table prov.new_ddc

An example of a JSON-RPC request to create a table on provenance is shown below.

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": "occurrence_jsonrpc_id"
}

If records is specified at the same time, the initial data will be 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": "occurrence_jsonrpc_id"
}

Parameters

The following parameters can be specified in params.

parameter name	data type	content	default value
ddc_label	string	DDC name	Required
columns	[object]	Specify the column name with `column_name` and the type with `data_type`. The `data_type` can be one of varchar, int, geometry, double precision, timestamp with time zone, or timestamp. If you use varhcar and want to encrypt the data, set `need_crypto` to true.	Required (all columns and types)
key	string	The column_name of the column specified in columns that you want to make unique.
records	[object]	A set of values using the column_name value specified in columns as a key.

Obtaining column information prov.get_ddc_columns

The following is an example of a JSON-RPC request to retrieve column information of 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 you specify varchar for data_type and also specify encryption using true for need_crypto, the data_type in the response will be bytea.

If you use

{
  "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 can be specified in params.

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

Count the number of records prov.count_ddc_records

An example of a JSON-RPC request to get the number of records in a table that has already been created is shown below.

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

The format of the response is as follows

{
  "result": 0
}

Parameters

The following parameters can be specified in params.

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

Partial selection of records and 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;

This is an example of a request to retrieve an image like the one above.

{
  "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": "occurrence_jsonrpc_id"
}

Parameters

The following parameters can be 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 you know the schema, use the format . If the schema is known, specify it in the format .	required
ddc_type	string	one of `link`, `table`, `view`	required
columns	[string]	if you want to create a ddc using only some columns, specify column names as an array of strings
conds	string	one of true or false	required
no_exec	string	one of true or false	required

If you want to create a ddc using only some columns, specify column names as an array of strings. | If you want to create a ddc using only some columns, specify column names as an array of strings.

Add record prov.put_ddc_records

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

INSERT INTO test (id, name, password, phone, email) VALUES
    (1,'Yohji Ishizuka','WJI67hUi','080-4715-4302','WzxKRGR6J@sample.com'),
    (2,'Yasuyuki Kondo','pHSBKdvB','090-3132-9449','KefD6KU@test.net'),
    (3,'Maho Tamai','SieMcQZi','090-2613-4478','cTC6Gu7a@test.com');

Here's an example request to add an image like the one above, with the second request counting the rows added.

{
  "jsonrpc": "2.0",
  "method": "prov.put_ddc_records",
  "params": {"ddc_label": "ddc:test",
             "records":[[1, "Yoji Ishizuka", "WJI67hUi", "080-4715-4302", "WzxKRGR6J@sample.com"],
                        [2, "Yasuyuki Kondo", "pHSBKdvB", "090-3132-9449", "KefD6KU@test.net"],
                        [3, "Maho Tamai", "SieMcQZi", "090-2613-4478", "cTC6Gu7a@test.com"],
             "on_conflict": "error"
            },
  "id": "occurrence_jsonrpc_id"
}

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

An example result of count_ddc_records is shown below.

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

Parameters.

The following parameters can be specified for params of prov.put_ddc_records.

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

The following parameters can be specified for params of prov.count_ddc_records.

parameter name	data type	content	default value
ddc_label	string	DDC name

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;

This is an example of a request to add records from another table, as shown in the image above, with the second request counting 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": "occurrence_jsonrpc_id"
}

Parameters

The following parameters can be specified in params.

parameter name	data type	content	default value
target	string	DDC name	required
source	string	DDC name	required
target	string	DDC name	required
on_conflict	string	one of `update`, `nothing`, or `error`	required

get records prov.get_ddc_records

The following is an example of a JSON-RPC request for a table with stored data to retrieve the table's records.

SELECT * FROM test;

The following is an example of a JSON-RPC request to retrieve records for a table with the above image.

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

Parameters

The following parameters can be specified in params.

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

Table join

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;
```.

This is an example of a request to join tables with an image like the one above. The alias on the left is fixed at `l`, and the alias on the right at `r`.

```json
{
  "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": "occurrence_jsonrpc_id"
}

Parameters

The following parameters can be specified in params.

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

delete table prov.delete_ddc

An example of a JSON-RPC request to delete a table that has already been created is shown below.

DROP TABLE test;

This 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 can be 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

The following is an example of a JSON-RPC request to control a database transaction.

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

This is an example of a request to control an image like the one above.

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

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

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

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

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

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

Here is an example of a JSON-RPC request to give 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": "occurrence_jsonrpc_id"
}

The following is an example of a JSON-RPC request to retrieve the entity (table/view) pointed to by ddc.

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

An example result of resolve_ddc is shown below.

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

Parameters.

Use the above for params in prov.set_ddc.

The following parameters should be used for params of prov.resolve_ddc.

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