Hydra database schema¶
The database schema can be found here.
Hydra data¶
Network definition¶
These tables are referred to in the schema as ‘resources’. A resource is something to which data can be assigned, through attributes.
tProject¶
A project is a high level container for networks. A project can contain multiple networks.
project_id: Unique identifier for the project
project_name: The name of the project
project_description: A non-mandatory description
status: A character, which can be A (active) or X (deleted)
cr_date: Creation date
created_by: The user_id of the user who created the project
tNetwork¶
A network contains links and scenarios, not no nodes directly. The topology of a network is defined through its links – a link connects two nodes. A network also contains scenarios, which are containers for network data.
project_id: The project in which this network resides.
network_id: Unique identifier
network_name: The name of the network. This is UNIQUE within a project.
network_description: A non-mandatory description
network_layout: Layout parameters for the network.
status: A character, which can be A (active) or X (deleted)
projection: A string describing the map projection of the coordinates in the network.
cr_date: Creation date
tNode¶
Along with the standard id, name, description and status, a Node has an X, Y coordinate.
id: Unique identifier
network_id: The network in which this link resides.
name: Node name. This is UNIQUE within a network.
description: non-mandatory description
status: A character, which can be A (active) or X (deleted)
x: The node’s X-coordinate on a standard plane
y: The node’s Y-coordinate on a standard plane
layout: A string describing layout parameters.
type: The name of the template to which this node belongs. For example, the ‘reservoir’ type refers to the ‘reservoir’ templates which may reside in multiple groups.
cr_date: Creation date
tLink¶
Links belong inside a network and link two nodes. Links define the topology of the network. Along with the standard id, name and status, a link has two node ids and a network_id.
id: Unique identifier
network_id: The network in which this link resides.
status: A character, which can be A (active) or X (deleted)
node_1_id: Link from node 1…
node_2_id: …to node 2
name: Name of the link. This is UNIQUE for links between the same 2 nodes.
description: Description of the link..
layout: A string describing layout parameters. The layout includes intermediate points.
cr_date: Creation date
Attributes¶
tAttr¶
A basic attribute definition, with just a name and dimension. For example: A reservoir might have this attribute: Name: ‘Capacity’ Dimension ‘Volume’
attr_id: Unique identifier
attr_name: Name (Capacity, Flow)
attr_dimen: Dimension of the value that will be stored against this attribute.
cr_date: Creation date
tType¶
A resource type defines a grouping for attributes. This allows a ‘type’ of resource to be defined. For example, a simple reservoir template would contain two attributes: Flow and Capacity (each of which were defined in tattr)
type_id: Unique identifier
type_name: Template Name (‘Reservoir’ for example)
alias: This is a non-functional string used for display purposes.
layout: Default display parameters for a type – colour, shape etc.
template_id: The template to which this type belongs (For example: “EBSD Nodes”)
tTemplate¶
A grouping for resource types. Used to categorise resource types into a single group – for example, the ‘GAMS Nodes’ Group might contain two resource templates: ‘Reservoir’ and ‘Refinery’. This grouping should define what is required by a GAMS plugin.
template_id: Unique Identifier
template_name: Name
layout: Default display parameters for a template – colour, shape etc.
tTypeattr¶
This links attributes to their template. An attribute can be in several templates. Both attr_id and template_id make up the PK.
attr_id: The attribute
type_id: The type that this attribute is in.
default_dataset_id: Id of a dataset which can be used as a default.
attr_is_var: Flag to indicate whether, in this type, the attribute is a variable
data_type: The expected data type for the attribute in this type
data_dimension: The expeted dimension of the data
data_restriction: A python dictionary, which looks something like:{‘NUMPLACES’: ‘1’, ‘LESSTHAN’: ‘10’}
tResourceAttr¶
A ‘resource’ can be a Project, Network, Node, Link or Scenario. A resource attribute is an attribute associated with a specific resource. For example, given an attribute (attr_id = 1) a node (node_id = 100), the resource attribute states that node 100 has attribute 1. It is through this table that data can be associated with a resource.
resource_attr_id: Unique identifier
attr_id: The attribute being assigned to this resource
ref_key: The type of resource. Can be one of: (‘NODE’, ‘LINK’, ‘NETWORK’, ‘PROJECT’, ‘SCENARIO’)
network_id: The identifer for the network (can only be not-null if ref_key is ‘NETWORK’).
project_id: The identifer for the project (can only be not-null if ref_key is ‘PROJECT’).
node_id: The identifer for the node. (can only be not-null if ref_key is ‘NODE’)
link_id: The identifer for the link. (can only be not-null if ref_key is ‘LINK’)
group_id: The identifer for the resource group. (can only be not-null if ref_key is ‘GROUP’)
attr_is_var: Either ‘Y’ or ‘N’ – This flag indicates whether data should be assigned to the resource attribute. If not, it is assumed this will be done by an app.
tAttrMap¶
This maps two attributes, meaning they are equivalent. For example, ‘Capacity’ in one app might be the same as and ‘Size’ in another.
attr_id_a: Attribute a is the same as …
attr_id_b: … attribute b.
Scenarios¶
tScenario¶
A scenario is a set of data associated with a network. Let’s say there is a network with some node and links, all of which have been assigned some resource attributes. A scenario is what contains the data for those resource attributes. Several scenarios can be created per network, meaning multiple different datasets can be used on the same network.
scenario_id: Unique identifier
network_id: The network to which this scenario applies
scenario_name: The name of this scenario
scenario_description: Non-mandatory description
scenario_layout: Used to store layout information for the UI
start_time: Scenario start time (required for some models)
end_time: Scenario end time
time_step: Scenario time step
locked: Flag to indicate whether the scenario is editable
status: A character, which can be A (active) or X (deleted)
cr_date: Creation date
tResourceScenario¶
This connects a piece of data, a scenario and a resource attribute. The data itself is not accessed directly from this table, but through tDataset, which stores what type the data its, its units and other information.
dataset_id: A reference to the scenario data table.
scenario_id: A reference to the scenario
resource_attr_id: A reference to the resource attribute.
source: An varchar describing which app this dataset came from.
Datasets¶
tDataset¶
Links a scenario to a single piece of data. This table references the data in the appropriate data table using data_id. It knows which table to access using the data_type column. Ex: data_id = 1 and data_type = ‘descriptor’ means look in tDescriptor for data_id 1.
id: Unique identifier
type: Defines which data table to look in. Must be one of: (‘descriptor’, ‘timeseries’, ‘eqtimeseries’, ‘scalar’, ‘array’)
unit: What is this data type measured in?
name: A name for this data
hash: The hash of the datum. This hash is generated using python’s hash() function, as used in hash tables. Allows for easy comparison of data.
hidden: Flag to indicate whether this dataset has been hidden by its owner.
value: Contains the actual value. This will usually be a single value or a JSON string.
cr_date: Creation date
tDatasetCollection¶
Collections datasets into named sets for easy & convenient categorisation.
name: The human-readable name of the collection or category
id : Unique identifier for the collection. PK.
tDatasetCollectionItem¶
Keeps track of which piece of data is in which collection.
dataset_id : refers to the piece of data in tDataset that is in the collection
collection_id : refers to the collection_id in tDatasetCollection.
tTimeSeriesData¶
Time series data, stored as multiple time - value pairs, all associated with a single data_id, which is contained in tTimeSeries.
dataset_id: Reference to data_id in tTimeSeries
ts_time: Timestamp
ts_value: a multi-dimensional array, stored as a blob. Can also just be a single value.
tMetaData¶
Auxiliary information about the data, in name / value pairs.
dataset_id: Reference to the data about which this info is stored.
metadata_name: Name of the auxiliary piece of data
metadata_val: Value
User and permission management¶
These tables are not connected to the ones containing network information.
tUser¶
Save access credentials for each user
id: unique identifier
username: Username
password: Password
cr_date: Creation date
tRole¶
Define roles
id: Unique identifier
name: Role name
code: Role code. Unique. Used for easier identification
cr_date: Creation date
tPerm¶
Define particular permissions
id: Unique identifier
name: Permission Name
code: Permission code. Unique. Used for easier identification
cr_date: Creation date
tRolePerm¶
Assign particular permissions to a role
perm_id: Reference to permission
role_id: Reference to role
Ownership¶
These tables define what belongs to which users.
tProjectOwner¶
Ownership of a resource
user_id : User’s ID
project_id : Reference to the project
view : Flag to indicate read permissions (read is a reserved word, hence ‘view).
edit : Flag to indicate write permissions (write is a reserved word, hence ‘edit’).
share : Flag to indicate share permissions
cr_date : creation date
tNetworkOwner¶
Ownership of a resource
user_id : User’s ID
network_id : Reference to the Network.
view : Flag to indicate read permissions (read is a reserved word, hence ‘view).
edit : Flag to indicate write permissions (write is a reserved word, hence ‘edit’).
share : Flag to indicate share permissions
cr_date : creation date
tDatasetOwner¶
Ownership of a resource
user_id : User’s ID
dataset_id : Reference to the dataset
view : Flag to indicate read permissions (read is a reserved word, hence ‘view).
edit : Flag to indicate write permissions (write is a reserved word, hence ‘edit’).
share : Flag to indicate share permissions
cr_date : creation date
