This repository contains the library that manages the Envrio MySql and InfluxDB databases. It consists of four main modules and four auxilliary. Three of the main modules handle the connection and CRUD operations to the MySql database and one to the InfluxDB. An in depth descriptiion of the main modules functionalities is given below.
Version: 1.2.0
Creates the engine that connects an app to the MySql database, using the class scoped_sessions above class sessionmaker.
Version: 1.4.3
Creates the envrio_core MySql database with the tables-columns shown in the following table:
| Table Name | Column Name | Data Type | Short Description |
|---|---|---|---|
| user_tables | id | int | User unique indentification number |
| name | varchar(500) | User cognito name | |
| varchar(500) | User email | ||
| account_type | ENUM | User type. Can be one of academic, commercial, beta, alpha | |
| subscription_expires_in | float | User's subscription expiration timestamp | |
| stations | id | int | Station unique identification number |
| brand | varchar(50) | Station brand | |
| model | varchar(200) | Station specific model | |
| code | varchar(100) | Station id as defined in the data providers API | |
| date_created | float | The timestamp that station was installed on the field and started to transmit data | |
| last_communication | float | The timestamp of the station last data update | |
| status | ENUM | The station's status. Can be online or offline | |
| longitude | decimal(10,8) | The lognitude of the station's installation point | |
| latitude | decimal(10,8) | The latitude of the station's installation point | |
| elevation | int | The elevation of the station's installation point | |
| access | json | A json object that contains an array with the user_ids that have an active subscription for the station e.g. {"users": [1,2,3]} | |
| name | json | A json object with the name that is give to the station is various languages e.g., {"el":"Αθήνα","en":"Athens"}. Usually the name of the station corresponds to the location that it is installed | |
| icon_type | varchar(10) | The type of the station. It can be one of 'meteo', 'coastal' or 'hydro' | |
| gateways | id | int | Gatewasy unique identification number |
| brand | varchar(50) | Gateway brand | |
| model | varchar(200) | Gateway specific model | |
| code | varchar(100) | Gateway id as defined in the data providers API | |
| name | varchar(500) | Gateway name as defined in the data provider API | |
| station_id | int | The id of the station that the gateways is connected to | |
| repeater_units | id | int | Repeater unique identification number |
| brand | varchar(50) | Repeater brand | |
| model | varchar(200) | Repeater specific model | |
| code | varchar(100) | Repeater id as defined in the data providers API | |
| date_created | float | The timestamp that repeater was installed on the field and started to transmit data | |
| last_communication | float | The timestamp of the repeater unit's last data update | |
| status | varchar(10) | The repeater unit's status. Can be online or offline | |
| longitude | decimal(10,8) | The lognitude of the repeater's installation point | |
| latitude | decimal(10,8) | The latitude of the repeater's installation point | |
| elevation | int | The elevation of the repeater's installation point | |
| name | json | A json object with the name that is give to the repeater unit is various languages e.g., {"el":"Μονάδα Αναμετάδοσης 1","en":"Repeater Unit 1"} | |
| station_id | int | The id of the station that the repeater unit is paired with | remote_terminal_units | id | int | remote terminal unit unique identification number |
| brand | varchar(50) | remote terminal unit brand | |
| model | varchar(200) | remote terminal unit specific model | |
| code | varchar(100) | remote terminal unit id as defined in the data providers API | |
| date_created | float | The timestamp that the terminal unit was installed on the field and started to transmit data | |
| last_communication | float | The timestamp of the remote terminal unit's last data update | |
| status | varchar(10) | The remote terminal unit's status. Can be online or offline | |
| longitude | decimal(10,8) | The longitude of the remote terminal unit's installation point | |
| latitude | decimal(10,8) | The latitude of the remote terminal unit's installation point | |
| elevation | int | The elevation of the remote terminal unit's installation point | |
| name | json | A json object with the name that is give to the remote terminal unit is various languages e.g., {"el":"Τερματική Μονάδα 1","en":"Terminal Unit 1"} | |
| station_id | int | The id of the station that the remote terminal unit is paired with | |
| monitored_parameters | id | int | Parameter unique identification number |
| device_type | ENUM | Depending on the type of device that monitores the parameter. It's 'sensor', 'meter', or 'calculated' if the parameter is monitored by a sensor, a meter or is a formula result, respectively | |
| measurement | varchar(100) | The name of the parameter that is measured. The nomenclature is mainly based on the CF Standard Names | |
| unit | varchar(20) | The unit that the parameter is reported | |
| last_communication | float | The timestamp of the monitored parameter's last data update | |
| status | varchar(10) | The monitored parameter's status. Can be online or offline | |
| device_height | float | The distance of the device, that monitors the parameter, from a reference surface (land or sea) in meters. If the device is above the reference surface the device height takes positive values, while in the case of installations within soil or below the sea surface negative | |
| name | varchar(100) | Paremeter name as defined by the user | |
| code | varchar(100) | Parameter id in the data provider API | |
| station_id | int | The id of the station that the parameter is measured by | |
| rtu_id | int | The id of the remote terminal unit that the parameter is measured by. Default value is None | |
| davis_api_credentials | id | int | A key unique identification number |
| station_id | int | The station id that the key corresponds | |
| key_id | str | The Key Management Service key id | |
| secret_name | str | The name for the Secrets Manager | |
| metrica_api_credentials | id | int | A key unique identification number |
| station_id | int | The station id that the key corresponds | |
| key_id | str | The Key Management Service key id | |
| secret_name | str | The name for the Secrets Manager | |
| adcon_api_credentials | id | int | A key unique identification number |
| station_id | int | The station id that the key corresponds | |
| key_id | str | The Key Management Service key id | |
| secret_name | str | The name for the Secrets Manager | |
| farms_registry | id | int | A farm unique identification number |
| user_id | int | The user id that the farm belong's to | |
| longitude | decimal(10,8) | The longitude of the farm location | |
| latitude | decimal(10,8) | The latitude of the farm location | |
| fields_registry | id | int | A field unique identification number |
| farm_id | int | The farm id that the farm is assigned to | |
| boundaries | json | A GeoJSON with the field boundaries | |
| soil_properties | json | A json object that includes the key layers that refers to the field distict soil layers and the key data that it's an array with the hydraulic properties (sat=saturation point,fc=field capacit,pwp=permanent wilting poin,ksat=saturated hydraulic conductivity) for each layer e.g., {"layers":2,"data":[{"depth":5,"sat":40,"fc":32,"pwp":14,"ksat":100},{"depth":25,"sat":42,"fc":35,"pwp":15,"ksat":100}]} | |
| applications_registry | id | int | An application unique identification number |
| field_id | int | The field id that the application was implemented | |
| type | varchar(10) | Defines the type of application and could be one of 'irrigation' or 'fertilization' | |
| suggested_amount | json | A json object with the keys type and amount. The key type take values 'water' or '19-6-15 (+7) +2MgO +0,5B'(fertilizer composition) in the case of irrigation and fertilization respectively. Similarly, the key amount is an integer and refers to the amount of water in qubic meters or the fertilizer amount in kg that is suggester to be applied to the field by the model. For example in the case of an irrigation advice {"type":"water","amount":300} | |
| applied_amount | json | The amount of water or fertilizer that is actually applied on the field e.g. {"type":"water","amount":390} | |
| applied_in | float | The timestamp that the application occurs | |
| advices_registry | id | int | An advice unique identification number |
| field_id | int | The field id that the advice was asked for | |
| type | varchar(10) | Defines the type of the advice and could be one of 'irrigation' or 'fertilization' | |
| status | varchar(10) | Defines the status of an irrigation advice. It can be one of 'ready', 'in_progress', 'cancelled', 'configuration_required' | |
| date_registered | float | The timestampt that the user asked for the advice | |
| date_created | float | The timestampt that the advice status switched to 'ready' | |
| measurement_translations | measurement | varchar(100) | The name of the parameter that is measured. The nomenclature is mainly based on the CF Standard Names |
| el | varchar(800) | A name of a measured parameter expresed in a manner that the end user can understand in Greek language | |
| en | varchar(800) | A name of a measured parameter expresed in a manner that the end user can understand in English language |
Version: 1.3.2
Validates the data type before they are used by CRUD module methods by creating classes inheriting from pydantic basemodel class.
Version: 1.3.4
Defines all the methods that Create, Read, Update and Delete tables, table rows, etc. from MySql database. It includes the folloing classes-methods:
| Class Name | Method | Short Description |
|---|---|---|
| User | add | Adds a new user to MySql database Arguments: name(str): the cognito user id; email(str): the user email; subscription_expires_in(timestamp): user's subscription expiration timestamp |
| get_by_name | Returns a named tuple object with the named fields
user id,
name,
email and
subscription_expires_in info Arguments name(str): the cognito user id |
|
| get_by_id | Returns a named tuple object with the named fields
user id,
name,
email and
subscription_expires_in info Arguments user_id(int): the user id |
|
| get_by_email | Returns a named tuple object with the named fields
user id,
name,
email and
subscription_expires_in info Arguments email(str): the user email |
|
| Stations | add | Adds a new station to MySql database Arguments brand(str): station's brand model(str): station's specific model code(str): station's code in provider's API date_created(int): station's installation timestamp last_communication(float): station's last communication timestamp status(str): station's status, online or offline longitude(decimal): station's longitude latitude(decimal): station's latitude elevation(int): station's elevation access(int): the user ids that have an active subscription name(json): A dictionary with station's name in different languages icon_type(str): one of 'hydro', 'meteo', 'coastal' depending on station's type |
| get_by_id | Returns a named tuple object with the named fields
station id,
brand,
model,
code,
date_created,
last_communication,
status,
longitude,
latitude,
elevation,
access,
name
and icon_type Arguments id(int): the station's id in the MySql database |
|
| get_by_code | Returns a named tuple object with the named fields
station id,
brand,
model,
code,
date_created,
last_communication,
status,
longitude,
latitude,
elevation,
access,
name
and icon_type Arguments code(str): the station's id in provider's API |
|
| get_by_brand | Returns a list of named tuples. The list length is equal to the stations of the given brand. Each named tuple has the name fields
station id,
brand,
model,
code,
date_created,
last_communication,
status,
longitude,
latitude,
elevation,
access,
name
and icon_type Arguments brand(str): a station brand |
|
| get_by_access | Returns a list of named tuples. The list length is equal to the stations that a user has an active subscription for. Each named tuple has the name fields
station id,
brand,
model,
code,
date_created,
last_communication,
status,
longitude,
latitude,
elevation,
access,
name
and icon_type Arguments user_id(int): the user id |
|
| update_date_created | Updates the date_created column value Arguments station_id(int): a station id new_datetime(ing): the new date time timestamp |
|
| update_last_communication | The date-time of the latest data update Arguments station_id(int): a station id new_datetime(int): the new date time timestamp |
|
| update_status | updating the station status Arguments station_id(int): a station id current_status(str): the new station status, online or offline |
|
| add_user_to_station | adds the user to station acceess list Arguments station_id(int): a station id user_id(int): the user that will be added to the station access list |
|
| add_user_to_station | adds the user to station acceess list Arguments station_id(int): a station id user_id(int): the user that will be added to the station access list |
|
| delete_user_from_station | deletes the user from sthe station access list Arguments station_id(int): a station id user_id(int): the user that will be added to the station access list |
|
| Gateways | add | Adds a new gateway Arguments brand(str): gateway's brand model(str): gateway's specific model code(str): gateway's id in provider's API name(str): gateway's user defined name station_id(int): the station id that gateway is connected to |
| get by code | Returns a named tuple object with the named fields
gateway id,
brand,
model,
code,
name and
station_id Arguments code(str): gateways's id is provider's API |
|
| RepeaterUnits | add | Adds a new repeater unit in MySql database Arguments brand(str): repeater unit's brand model(str): repeater unit's specific model code(str): repeater unit's id in provider's API date_created(int): repeater unit's installation timestamp last_communication(float): repeater unit's last communication timestamp status(str): repeater unit's status, online or offline longitude(decimal): repeater unit's longitude latitude(decimal): repeater unit's longitude evelation(int): repeater unit's elevation name(json): a dictionaly with a name assigned by the user to the repeater uni in different languages station_id(int): the station id that the repeater unit is paired with |
| get_by_id | Returns a named tuple object with the named fields
id,
brand,
model,
code,
date_created,
last_communication,
status,
longitude,
latitude,
elevation,
name and
station_id Arguments id(int): the repeater unit's id in the MySql database |
|
| get_by_code | Returns a named tuple object with the named fields
id,
brand,
model,
code,
date_created,
last_communication,
status,
longitude,
latitude,
elevation,
name and
station_id Arguments code(str): the remote terminal unit's code in providers API |
|
| get_by_station_id | Returns a named tuple object with the named fields
id,
brand,
model,
code,
date_created,
last_communication,
status,
longitude,
latitude,
elevation,
name and
station_id Arguments station_id(int): the station id that remote terminal unit is paired with |
|
| update_date_created | Updates the date_created column value Arguments station_id(int): a station id new_datetime(ing): the new date time timestamp |
|
| update_last_communication | The date-time of the latest data update Arguments station_id(int): a station id new_datetime(int): the new date time timestamp |
|
| update_status | updating the repeater unit status Arguments repeater_id(int): a repeater unit id current_status(str): the new repeater unit's status, online or offline |
|
| delete_by_code | It deletes a repeater unit entry for a provided repeater unit code Arguments code(str): the repeater unit's id as defined in provider's API: |
|
| RemoteTerminalUnits | add | Adds a new remote terminal unit in MySql database Arguments brand(str): remote terminal unit's brand model(str): remote terminal unit's specific model code(str): remote terminal unit's id in provider's API date_created(int): remote terminal unit's installation timestamp last_communication(float): remote terminal unit's last communication timestamp status(str): remote terminal unit's status, online or offline longitude(decimal): remote terminal unit's longitude latitude(decimal): remote terminal unit's longitude evelation(int): remote terminal unit's elevation name(json): a dictionaly with a name assigned by the user to the remote terminal uni in different languages station_id(int): the station id that the remote terminal unit is paired with |
| get_by_id | Returns a named tuple object with the named fields
id,
brand,
model,
code,
date_created,
last_communication,
status,
longitude,
latitude,
elevation,
name and
station_id Arguments id(int): the remote terminal unit's id in the MySql database |
|
| get_by_code | Returns a named tuple object with the named fields
id,
brand,
model,
code,
date_created,
last_communication,
status,
longitude,
latitude,
elevation,
name and
station_id Arguments code(str): the remote terminal unit's code in providers API |
|
| get_by_station_id | Returns a named tuple object with the named fields
id,
brand,
model,
code,
date_created,
last_communication,
status,
longitude,
latitude,
elevation,
name and
station_id Arguments station_id(int): the station id that remote terminal unit is paired with |
|
| get_by_repeater_id | Returns a named tuple object with the named fields
id,
brand,
model,
code,
date_created,
last_communication,
status,
longitude,
latitude,
elevation,
name and
station_id Arguments repeater_id(int): the repeater id that remote terminal unit is paired with |
|
| update_date_created | Updates the date_created column value Arguments station_id(int): a station id new_datetime(ing): the new date time timestamp |
|
| update_last_communication | The date-time of the latest data update Arguments rtu_id(int): a remote terminal unit id new_datetime(int): the new date time timestamp |
|
| update_status | updating the remote_terminal_unit status Arguments rtu_id(int): a station id current_status(str): the new remote terminal unit's status, online or offline |
|
| delete_by_code | It deletes a remote terminal unit entry for a provided remote terminal unit code Arguments code(str): the remote terminal unit's id as defined in provider's API: |
|
| MonitoredParameters | add | Adds a new sensor or meter to MySql database Arguments device_type(str): can be one of sensor, meter or calculated measurement(str): monitored parameter's name unit(str): monitored parameter's unit date_created(int): monitored parameter's installation timestamp last_communication(float): monitored parameter's last communication timestamp status(str): monitored parameter's status, online or offline device_height(float): the device distance below (negative) or above (positive) the reference surface (soil or sea) in meters name(str): a name assigned by the user to the parameter code(str): the parameter id in provider's API station_id(int): the station id that the parameter is monitored by rtu_id: the remote terminal unit id that the parameter is monitored by. Default value is None |
| get_by_station_id | Returns a list of named tuples with the parameters that are monitored by the given station id. Each named tuple object includes the named fields
id,
device_type,
measurement,
unit,
date_created,
last_communication,
status,
device_height,
name,
code,
station_id and
rtu_id Arguments station_id(int): the station id that the parameter is monitored by |
|
| get_by_repeater_id | Returns a list of named tuples with the parameters that are monitored by the given repeater id. Each named tuple object includes the named fields
id,
device_type,
measurement,
unit,
date_created,
last_communication,
status,
device_height,
name,
code,
station_id and
rtu_id Arguments repeater_id(int): the remote terminal unit id that is parent to the rtu that the parameter is monitored by |
|
| get_by_id | Returns a named tuple object with the named fields
id,
device_type,
measurement,
unit,
date_created,
last_communication,
status,
device_height,
name,
code,
station_id and
rtu_id Arguments rtu_id(int): the remote terminal unit id that the parameter is monitored by |
|
| get_by_rtu_id | Returns a list of named tuples with the parameters that are monitored by the given rtu id. Each named tuple object includes the named fields
id,
device_type,
measurement,
unit,
date_created,
last_communication,
status,
device_height,
name,
code,
station_id and
rtu_id Arguments rtu_id(int): the remote terminal unit id that the parameter is monitored by |
|
| update_last_communication | The date-time of the latest data update Arguments monitored_parameter_id(int): a monitored parameter's id new_datetime(int): the new date time timestamp |
|
| update_status | updating the monitored parameter's status Arguments monitored_parameter_id(int): a monitored parameter's id current_status(str): the new monitored parameter's status, online or offline |
|
| delete_by_code | It deletes a monitored parameter entry for a provided monitored parameter code Arguments code(str): the monitored parameter's id as defined in provider's API: |
|
| DavisApiCredentials | add | Adds a new key id and secret name Arguments station_id(int): the station id that key belong's to key_id(str): the Key Management Service key id secret_name(str): the Secrets Manager secret name |
| get_by_station_id | Returns a key id and a secret name Arguments station_id(int): the station id that key belong's to |
|
| update_key_id_by_station_id | Updates the key id Arguments station_id(int): the station id that key belong's to new_key_id(str): the new Key Management Service key id |
|
| update_secret_name_by_station_id | Updates the secret name Arguments station_id(int): the station id that key belong's to new_secret_name(str): the new Secrets Management new secret name |
|
| delete_by_station_id | Deletes the key id and secret name Arguments station_id(int): the station id that key belong's to |
|
| MetricaApiCredentials | add | Adds a new key id and secret name Arguments station_id(int): the station id that key belong's to key_id(str): the Key Management Service key id secret_name(str): the Secrets Manager secret name |
| get_by_station_id | Returns a key id and a secret name Arguments station_id(int): the station id that key belong's to |
|
| update_key_id_by_station_id | Updates the key id Arguments station_id(int): the station id that key belong's to new_key_id(str): the new Key Management Service key id |
|
| update_secret_name_by_station_id | Updates the secret name Arguments station_id(int): the station id that key belong's to new_secret_name(str): the new Secrets Management new secret name |
|
| delete_by_station_id | Deletes the key id and secret name Arguments station_id(int): the station id that key belong's to |
|
| ADCONApiCredentials | add | Adds a new key id and secret name Arguments station_id(int): the station id that key belong's to key_id(str): the Key Management Service key id secret_name(str): the Secrets Manager secret name |
| get_by_station_id | Returns a key id and a secret name Arguments station_id(int): the station id that key belong's to |
|
| update_key_id_by_station_id | Updates the key id Arguments station_id(int): the station id that key belong's to new_key_id(str): the new Key Management Service key id |
|
| update_secret_name_by_station_id | Updates the secret name Arguments station_id(int): the station id that key belong's to new_secret_name(str): the new Secrets Management new secret name |
|
| delete_by_station_id | Deletes the key id and secret name Arguments station_id(int): the station id that key belong's to |
version: 1.1.2
Connects to the InfluxDB database and defines all the required methods for data management and bucket configuration:
| Class Name | Method | Short Description |
|---|---|---|
| InfluxConnector | __init__ | Initializes an influx instance Arguments bucket_name(str): the desired influx bucket influx_conf(dict): a dictionary with the influx host, token and organization name |
| DataManagement | write_point | Inserts data points to the database Arguments measurement(str): The name of the parameter that is measured sensor_id(int): the sensor id in MySql MeasuredParameters table unit(str): the unit that the parameter is expressed data(dict): a dictionary with keys date_time and values that contains the measured data and their timestamps |
| delete_rows | Deletes data points from the database for a given time range Arguments measurement(str): The name of the parameter from which data will be deleted start(str): A date-time in ISO 8601 format. The beginning of the time period that data points will be deleted stop(str): A date-time in ISO 8601 format. The end of the time period that data points will be deleted tag(str): A measured parameter id as defined in MySql MeasuredParemeters table |
|
| query_data | Returns a dataframe with the queried data Arguments measurement(str): The name of the parameter that data should be retrieved sensor_id(int): a parameter id from MySql MeasuredParemeters table unit(srt): the unit that the parameter is expressed start(str): A date-time in ISO 8601 format. The beginning of the time period that data points will be retrieved stop(str): A date-time in ISO 8601 format. The end of the time period that data points will be retrieved |
|
| BucketConfiguration | list_buckets | Returns a list with the existing Bucket class objects |
| update_bucket | Updates a Bucket retention rules Arguments type(str): a string that represents Bucket type e.g., expire data_duration(int): the data duration in seconds. Default is 0, corresponding to infinite shard_group_duration(int): the shard group duration in seconds. Default value is set to 630,720,000 secs (roughly 20 years) description(str): a short descrption related with the update purpose |