Skip to content

Database

Jump to bottom
Zane van Iperen edited this page Jul 2, 2018 · 14 revisions

Database

Developer Notes

Naming Conventions

Tables

^ Convention            ^ Description                                             ^
| ''nimrod_*''          | All Nimrod Tables.                                      |
| ''nimrod_resource_*'' | Tables used for resources and their related properties. |

=== Stored Procedures ===

^ Convention       ^ Description                          ^
| ''_*''           | Helper procedure.                    |
| ''_acr_*''       | Helpers for ''add_compiled_run()''.  |
| ''_msg_*''       | Helpers for messaging functionality. |
| ''_msg_build_*'' | Message payload helpers.             |
| ''_msg_t_*''     | Message trigger procedure.           |
| ''resolve_*''    | Resolve the entity via its path.     |
| ''_res_*''       | Resource helpers.                    |
| ''_res_t_*''     | Resource trigger procedure.          |
| ''_exp_t_*''     | Experiment trigger procedure.        |
| ''_gt_*''        | Helpers for ''export_task()''.       |

Triggers

^ Convention  ^ Description        ^
| ''t_*''     | Trigger            |
| ''t_exp_*'' | Experiment trigger |
| ''t_msg_*'' | Message trigger    |
| ''t_res_*'' | Resource trigger   |

Views

  • nimrod_full_jobs - Same as the nimrod_jobs table except with three additional columns containing:
    • status, the derived job status.
    • var_names, an array of the variable names.
    • var_values, an array of the variable values.
  • nimrod_full_runs - Same as the nimrod_runs table except with two additional columns, vars_json and tasks_json, containing a JSON array of the variable names (without implicit variables), and a JSON blob (export_tasks) of the tasks respectively.
  • nimrod_resource_storage_root - Same as the nimrod_resource_storage table except it only contains root resources (parent IS NULL) and contains the additional columns:
    • type_name, type_class, amqp_uri, amqp_cert_path, amqp_no_verify_peer, amqp_no_verify_host
  • nimrod_mapped_agents - A combination of the nimrod_agent_definitions and nimrod_agent_posix_mappings tables.

Reference

Database Stored Procedure reference. Most of these map directly to the NimrodAPI, and thus are undocumented.

update_config

Insert/Update configuration. Any field set to NULL will use the old value.

Only the row with id = 1 is updated. It is created if it doesn't exist.

FUNCTION update_config(_work_dir TEXT, _amqp_uri nimrod_uri, _amqp_routing_key TEXT, _tx_uri nimrod_uri) RETURNS nimrod_config;

get_copnfig

Get the global configuration. This will throw if setup hasn't been run.

FUNCTION get_config() RETURNS nimrod_config;

get_property

Get a property. Returns NULL if it doesn't exist.

FUNCTION get_property(_key nimrod_config_key) RETURNS TEXT;

set_property

Set a property. If _value is NULL or empty, delete it.

FUNCTION set_property(_key nimrod_config_key, _value TEXT) RETURNS TEXT;

get_properties

FUNCTION get_properties() RETURNS SETOF nimrod_config;

add_experiment

Add an experiment and return its row.

FUNCTION add_experiment(_name TEXT, _work_dir TEXT) RETURNS nimrod_experiments;

get_experiment

Get an experiment row by either its id or name.

FUNCTION get_experiment(_exp_id BIGINT) RETURNS SETOF nimrod_experiments;
FUNCTION get_experiment(_name TEXT) RETURNS SETOF nimrod_experiments

delete_experiment

Delete an experiment by either its id or name.

FUNCTION delete_experiment(_exp_id BIGINT) RETURNS SETOF VOID;
FUNCTION delete_experiment(_name TEXT) RETURNS SETOF VOID;

add_compiled_run

Add a compiled run JSON dump, returning its row.

FUNCTION add_compiled_run(_exp_id BIGINT, _name TEXT, _work_dir TEXT, _file_token TEXT, _run JSONB) RETURNS nimrod_full_runs;
  • This function is huge, it's in ''ddl-add-compiledrun.sql''.
  • Set _file_token to NULL to auto-generate a token.

get_run

Lookup a run by its id or name, and the id of its parent experiment.

FUNCTION get_run(_exp_id BIGINT, _run_id BIGINT) RETURNS SETOF nimrod_full_runs;
FUNCTION get_run(_exp_id BIGINT, _name TEXT) RETURNS SETOF nimrod_full_runs;

flag_run

FUNCTION flag_run(_run_id BIGINT, _flag nimrod_run_flag);

get_experiment_runs

Get all the runs for an experiment.

FUNCTION get_experiment_runs(_exp_id BIGINT) RETURNS SETOF nimrod_full_runs;

add_multiple_jobs

Add a batch of jobs to the given run, such for each _values[i], a job will be added such that vars[j] has the value _values[i][j].

FUNCTION add_multiple_jobs(_run_id BIGINT, _vars TEXT[], _values TEXT[][]) RETURNS SETOF nimrod_full_jobs
  • Any NULL values will be coalesced to ''.

get_job_status

FUNCTION get_job_status(_job_id BIGINT) RETURNS nimrod_job_status;

create_job_attempt

Create a job attempt.

FUNCTION create_job_attempt(_job_id BIGINT, _uuid UUID, _work_dir TEXT, _agent_uuid UUID) RETURNS SETOF nimrod_job_attempts;

start_job_attempt

FUNCTION start_job_attempt(_att_id BIGINT, _agent_uuid UUID) RETURNS SETOF nimrod_job_attempts;

finish_job_attempt

FUNCTION finish_job_attempt(_att_id BIGINT, _failed BOOLEAN) RETURNS SETOF nimrod_job_attempts;

get_job_attempts

FUNCTION get_job_attempts(_job_id BIGINT) RETURNS SETOF nimrod_job_attempts;

get_job_attempt

FUNCTION get_job_attempt(_att_id BIGINT) RETURNS SETOF nimrod_job_attempts;

filter_jobs

FUNCTION filter_jobs(_run_id BIGINT, _status nimrod_job_status[], _start BIGINT, _limit BIGINT) RETURNS SETOF nimrod_full_jobs;

get_resource_type_info

FUNCTION get_resource_type_info() RETURNS SETOF nimrod_resource_types;
FUNCTION get_resource_type_info(_type TEXT) RETURNS SETOF nimrod_resource_types;

add_root_resource

Add a root resource with the given configuration.

This will throw if _typename is not a valid type.

FUNCTION add_root_resource(_name TEXT, _typename TEXT, _config JSONB, _amqp_uri nimrod_uri, _tx_uri nimrod_uri) RETURNS nimrod_resource_storage_root;

add_child_resource

Add a child resource with the given configuration.

FUNCTION add_child_resource(_name TEXT, _parent BIGINT, _config JSONB, _tx_uri nimrod_uri) RETURNS nimrod_resource_storage;

resolve_resource

FUNCTION resolve_resource(_parent BIGINT, _path nimrod_path) RETURNS SETOF nimrod_resource_storage;
FUNCTION resolve_resource(_path nimrod_path) RETURNS SETOF nimrod_resource_storage;

get_root_resource

FUNCTION get_root_resource(_name TEXT) RETURNS SETOF nimrod_resource_storage_root;

get_child_resources

FUNCTION get_child_resources(_parent BIGINT) RETURNS SETOF nimrod_resource_storage;

get_parent_resource

FUNCTION get_parent_resource(_id BIGINT) RETURNS SETOF nimrod_resource_storage;

get_root_resource

FUNCTION get_root_resource(_id BIGINT) RETURNS SETOF nimrod_resource_storage_root;

walk_resource_to_root

FUNCTION walk_resource_to_root(_id BIGINT) RETURNS SETOF nimrod_resource_storage;

get_root_resources

FUNCTION get_root_resources() RETURNS SETOF nimrod_resource_storage_root;

get_assigned_resources

Get the resources assigned to the experiment with id _exp_id.

FUNCTION get_assigned_resources(_exp_id BIGINT) RETURNS SETOF nimrod_resource_storage;
  • Recall that assignments are stored on the leaves, so this will return said leaves.

assign_resource

Assign the resource with id _res_id to the experiment with id _exp_id, returning the newly-added assignment rows. Existing assignments will not be returned.

FUNCTION assign_resource(_res_id BIGINT, _exp_id BIGINT) RETURNS SETOF nimrod_resource_assignments;

unassign_resource

Unassign the resource with id _res_id from the experiment with id _exp_id, returning the deleted assignments.

FUNCTION unassign_resource(_res_id BIGINT, _exp_id BIGINT) RETURNS SETOF nimrod_resource_assignments;

get_resource_assignment_state

Get the state of the resource assignment.

Valid values are:

  • 0 -- The resource is not assigned.
  • 1 -- The resource is partially assigned.
  • 2 -- The resource is fully assigned.
FUNCTION get_resource_assignment_state(_res_id BIGINT, _exp_id BIGINT) RETURNS INTEGER;

get_resource_caps

Get the capabilities of a given resource.

Valid values are:

  • 0 -- The resource is incapable.
  • 1 -- The resource is partially capable.
  • 2 -- The resource is fully capable.
FUNCTION get_resource_caps(_res_id BIGINT, _run_id BIGINT) RETURNS INTEGER;

add_resource_caps

Add capabilities to a resource.

FUNCTION add_resource_caps(_res_id BIGINT, _run_id BIGINT) RETURNS VOID;

remove_resource_caps

Remove capabilities from a resource.

FUNCTION remove_resource_caps(_res_id BIGINT, _run_id BIGINT) RETURNS VOID;

resolve_resource_txuri

FUNCTION resolve_resource_txuri(_id BIGINT) RETURNS nimrod_uri;

resolve_resource_amqpuri

FUNCTION resolve_resource_amqpuri(_id BIGINT) RETURNS nimrod_uri;

get_agent_information

FUNCTION get_agent_information(_uuid UUID) RETURNS SETOF nimrod_resource_agents;

get_agent_resource

FUNCTION get_agent_resource(_uuid UUID) RETURNS SETOF nimrod_resource_storage;

get_agents_on_resource

FUNCTION get_agents_on_resource(_res_id BIGINT) RETURNS SETOF nimrod_resource_agents;

add_agent

FUNCTION add_agent(_state nimrod_agent_state, _queue TEXT, _uuid UUID, _shutdown_signal INTEGER, _shutdown_reason nimrod_agent_shutdown_reason, _location BIGINT) RETURNS nimrod_resource_agents;

update_agent

FUNCTION update_agent(_uuid UUID, _state nimrod_agent_state, _signal INTEGER, _reason nimrod_agent_shutdown_reason) RETURNS SETOF nimrod_resource_agents;

poll_master_messages

FUNCTION poll_master_messages() RETURNS SETOF nimrod_master_message_storage;

add_master_message

FOR INTERNAL USE ONLY

FUNCTION add_master_message(op nimrod_message_operation, class nimrod_master_message_class, payload JSONB);

add_command_result

CREATE OR REPLACE FUNCTION add_command_result(
	_attempt_id BIGINT,
	_status nimrod_command_result_status,
	_command_index BIGINT,
	_time REAL,
	_retval INT,
	_message TEXT,
	_error_code INT
) RETURNS nimrod_command_results AS $$
	INSERT INTO nimrod_command_results(attempt_id, status, command_index, time, retval, message, error_code)
	VALUES(_attempt_id, _status, _command_index, _time, _retval, _message, _error_code)
	RETURNING *;
$$ LANGUAGE SQL;

Clone this wiki locally