# By: Riasat Ullah # This file contains queries for services. A service represents # a technical component or unit of an organization. from dbqueries import db_organizations from exceptions.user_exceptions import DependencyFound, InvalidRequest from objects.service import Service from psycopg2 import errorcodes from utils import constants, errors, helpers, key_manager, permissions, times, var_names from uuid import UUID from validations import component_validator, string_validator import configuration as configs import datetime import json import psycopg2 import pytz import uuid def create_service(conn, timestamp, organization_id, service_name, description, for_policy_ref_id, re_trigger_minutes=None, support_days=None, support_start=None, support_end=None, support_timezone=None, de_prioritize=None, re_prioritize=None, allow_grouping=None): ''' Create entries in the database for a new service. Services that represent a real technical component and is created by a user should be created using this function. User services will be created automatically. :param conn: db connection :param timestamp: timestamp when this request is being made on :param organization_id: ID of the organization :param service_name: name of the service :param description: brief description of what the service is for :param for_policy_ref_id: reference ID of the policy that incidents triggered on this service should be forwarded to :param re_trigger_minutes: number of minutes after which incidents should be re-triggered in ack state :param support_days: (list of int) of days the service is active :param support_start: (str) time the service is active from in the format HH:MM :param support_end: (str) time the service is active till in the format HH:MM :param support_timezone: timezone the service is in; this will determine the UTC support hours :param de_prioritize: (boolean) to de-prioritize incidents during off hours or not :param re_prioritize: (boolean) to re-prioritize incidents that were de-prioritized when support hours start or not :param allow_grouping: (boolean) to allow incidents to be grouped on this service or not :return: unmasked reference key of the service :errors: AssertionError, DatabaseError, LookupError, ValueError ''' component_validator.validate_service_data(timestamp, organization_id, service_name, description, re_trigger_minutes, support_days, support_start, support_end, support_timezone, de_prioritize, re_prioritize, allow_grouping) unmasked_pol_ref_id = key_manager.unmask_reference_key(for_policy_ref_id) if support_days is not None: support_start_time = times.get_time_from_string(support_start) support_end_time = times.get_time_from_string(support_end) else: support_start_time = None support_end_time = None support_timezone = None de_prioritize = None re_prioritize = None serv_ref_key = key_manager.generate_reference_key() query = ''' select create_service( %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s ); ''' query_params = (timestamp, constants.end_timestamp, organization_id, serv_ref_key, service_name, description, unmasked_pol_ref_id, re_trigger_minutes, support_days, support_start_time, support_end_time, support_timezone, de_prioritize, re_prioritize, allow_grouping,) try: conn.execute(query, query_params) return serv_ref_key except psycopg2.IntegrityError as e: if e.pgcode == errorcodes.CHECK_VIOLATION: raise InvalidRequest(errors.err_invalid_request) else: raise except psycopg2.DatabaseError: raise def edit_service(conn, timestamp, service_ref_id, organization_id, user_id, service_name, description, for_policy_ref_id, re_trigger_minutes, support_days, support_start, support_end, support_timezone, de_prioritize=None, re_prioritize=None, allow_grouping=None, check_adv_perm=False, has_comp_perm=False, has_team_perm=False): ''' Edit the details of a service in the database. :param conn: db connection :param timestamp: timestamp when this request is being made on :param service_ref_id: reference ID of the service that is being edited :param organization_id: ID of the organization :param user_id: user_id of the user creating the service :param service_name: name of the service :param description: brief description of what the service is for :param for_policy_ref_id: reference ID of the policy that incidents triggered on this service should be forwarded to :param re_trigger_minutes: number of minutes after which incidents should be re-triggered in ack state :param support_days: (list of int) of days the service is active :param support_start: (str) time the service is active from in the format HH:MM :param support_end: (str) time the service is active till in the format HH:MM :param support_timezone: timezone the service is in; this will determine the UTC support hours :param de_prioritize: (boolean) to de-prioritize incidents during off hours or not :param re_prioritize: (boolean) to re-prioritize incidents that were de-prioritized when support hours start or not :param check_adv_perm: (boolean) should advanced permissions be checked :param has_comp_perm: (boolean) does the organization have advanced permissions :param has_team_perm: (boolean) does the organization have team permissions :param allow_grouping: (boolean) to allow incidents to be grouped on this service or not :return: (int) service ID :errors: AssertionError, DatabaseError, LookupError, PermissionError, ValueError ''' assert isinstance(user_id, int) component_validator.validate_service_data(timestamp, organization_id, service_name, description, re_trigger_minutes, support_days, support_start, support_end, support_timezone, de_prioritize, re_prioritize, allow_grouping) unmasked_serv_ref_id = key_manager.unmask_reference_key(service_ref_id) unmasked_pol_ref_id = key_manager.unmask_reference_key(for_policy_ref_id) support_start_time = None if support_start is None else times.get_time_from_string(support_start) support_end_time = None if support_end is None else times.get_time_from_string(support_end) adv_perm_type = permissions.COMPONENT_ADVANCED_EDIT_PERMISSION query = ''' select edit_service( %s, %s, %s, %s::smallint, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s ); ''' query_params = (check_adv_perm, has_comp_perm, has_team_perm, configs.service_component_type_id, adv_perm_type, timestamp, constants.end_timestamp, organization_id, unmasked_serv_ref_id, user_id, service_name, description, unmasked_pol_ref_id, re_trigger_minutes, support_days, support_start_time, support_end_time, support_timezone, de_prioritize, re_prioritize, allow_grouping,) try: result = conn.fetch(query, query_params) return result[0][0] except psycopg2.IntegrityError as e: if e.pgcode == errorcodes.CHECK_VIOLATION: raise LookupError(errors.err_unknown_resource) elif e.pgcode == errorcodes.RESTRICT_VIOLATION: raise PermissionError(errors.err_user_rights) else: raise except psycopg2.DatabaseError: raise def delete_service(conn, timestamp, organization_id, user_id, service_ref_id, check_adv_perm=False, has_comp_perm=False, has_team_perm=False): ''' Delete a service. :param conn: db connection :param timestamp: timestamp when this request is being made on :param service_ref_id: reference ID of the service that is to be deleted :param organization_id: ID of the organization the service belongs to :param user_id: ID of the user deleting the service :param check_adv_perm: (boolean) should advanced permissions be checked :param has_comp_perm: (boolean) does the organization have advanced permissions :param has_team_perm: (boolean) does the organization have team permissions :return: (int) the service ID :errors: AssertionError, DatabaseError, LookupError ''' assert isinstance(timestamp, datetime.datetime) assert isinstance(organization_id, int) assert isinstance(user_id, int) unmasked_serv_ref_id = key_manager.unmask_reference_key(service_ref_id) adv_perm_type = permissions.COMPONENT_ADVANCED_EDIT_PERMISSION query = ''' select delete_service( %s, %s, %s, %s::smallint, %s, %s, %s, %s, %s ); ''' query_params = (check_adv_perm, has_comp_perm, has_team_perm, configs.service_component_type_id, adv_perm_type, timestamp, organization_id, unmasked_serv_ref_id, user_id,) try: result = conn.fetch(query, query_params) return result[0][0] except psycopg2.IntegrityError as e: if e.pgcode == errorcodes.CHECK_VIOLATION: raise LookupError(errors.err_unknown_resource) else: raise except psycopg2.DatabaseError: raise def enable_service(conn, timestamp, service_ref_id, organization_id, user_id, to_enable, check_adv_perm=False, has_comp_perm=False, has_team_perm=False): ''' Enables or disables a service. :param conn: db connection :param timestamp: timestamp when this request is being made on :param service_ref_id: reference ID of the service that is being edited :param organization_id: ID of the organization :param user_id: user_id of the user creating the service :param to_enable: (boolean) True if the service should be enabled; False otherwise :param check_adv_perm: (boolean) should advanced permissions be checked :param has_comp_perm: (boolean) does the organization have advanced permissions :param has_team_perm: (boolean) does the organization have team permissions :return: (int) service ID :errors: AssertionError, DatabaseError, LookupError, PermissionError, ValueError ''' assert isinstance(organization_id, int) assert isinstance(user_id, int) assert isinstance(to_enable, bool) unmasked_serv_ref_id = key_manager.unmask_reference_key(service_ref_id) adv_perm_type = permissions.COMPONENT_ADVANCED_EDIT_PERMISSION query = ''' select enable_service( %s, %s, %s, %s::smallint, %s, %s, %s, %s, %s, %s, %s ); ''' query_params = (check_adv_perm, has_comp_perm, has_team_perm, configs.service_component_type_id, adv_perm_type, timestamp, constants.end_timestamp, organization_id, unmasked_serv_ref_id, user_id, to_enable,) try: result = conn.fetch(query, query_params) return result[0][0] except psycopg2.IntegrityError as e: if e.pgcode == errorcodes.CHECK_VIOLATION: raise LookupError(errors.err_unknown_resource) elif e.pgcode == errorcodes.RESTRICT_VIOLATION: raise PermissionError(errors.err_user_rights) else: raise except psycopg2.DatabaseError: raise def add_service_maintenance(conn, timestamp, service_ref_id, organization_id, user_id, maintenance_start, maintenance_end, maintenance_timezone, reason=None, check_adv_perm=False, has_comp_perm=False, has_team_perm=False): ''' Add a service maintenance schedule. :param conn: db connection :param timestamp: timestamp when this request is being made :param service_ref_id: reference ID of the service the maintenance is for :param organization_id: ID of the organization the service belongs to :param user_id: user_id of the user adding the maintenance :param maintenance_start: (datetime.datetime) when the maintenance should start :param maintenance_end: (datetime.datetime) when the maintenance should end :param maintenance_timezone: timezone the maintenance time is in :param reason: (str) reason for this maintenance :param check_adv_perm: (boolean) should advanced permissions be checked :param has_comp_perm: (boolean) does the organization have advanced permissions :param has_team_perm: (boolean) does the organization have team permissions :errors: AssertionError, DatabaseError, PermissionError ''' assert isinstance(timestamp, datetime.datetime) assert isinstance(organization_id, int) assert isinstance(user_id, int) assert isinstance(maintenance_start, datetime.datetime) assert isinstance(maintenance_end, datetime.datetime) assert maintenance_timezone in pytz.all_timezones unmasked_serv_ref_id = key_manager.unmask_reference_key(service_ref_id) utc_end = times.region_to_utc_time(maintenance_end, maintenance_timezone) adv_perm_type = permissions.COMPONENT_ADVANCED_EDIT_PERMISSION query = ''' select add_service_maintenance( %s, %s, %s, %s::smallint, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s ); ''' query_params = (check_adv_perm, has_comp_perm, has_team_perm, configs.service_component_type_id, adv_perm_type, organization_id, unmasked_serv_ref_id, timestamp, utc_end, maintenance_start, maintenance_end, maintenance_timezone, reason, user_id,) try: result = conn.fetch(query, query_params) return result[0][0] except psycopg2.IntegrityError as e: if e.pgcode == errorcodes.RESTRICT_VIOLATION: raise PermissionError(errors.err_user_rights) else: raise except psycopg2.DatabaseError: raise def delete_service_maintenance(conn, timestamp, organization_id, user_id, service_ref_id, maintenance_id, check_adv_perm=False, has_comp_perm=False, has_team_perm=False): ''' Deletes a scheduled service maintenance that is yet to expire. :param conn: db connection :param timestamp: timestamp when this request is being made :param organization_id: ID of the organization the service belongs to :param user_id: user_id of the user deleting the maintenance :param service_ref_id: reference ID of the service the maintenance is for :param maintenance_id: the unique maintenance ID :param check_adv_perm: (boolean) should advanced permissions be checked :param has_comp_perm: (boolean) does the organization have advanced permissions :param has_team_perm: (boolean) does the organization have team permissions :errors: AssertionError, DatabaseError, LookupError, PermissionError ''' assert isinstance(timestamp, datetime.datetime) assert isinstance(organization_id, int) assert isinstance(user_id, int) assert isinstance(maintenance_id, int) unmasked_serv_ref_id = key_manager.unmask_reference_key(service_ref_id) adv_perm_type = permissions.COMPONENT_ADVANCED_EDIT_PERMISSION query = ''' select delete_service_maintenance( %s, %s, %s, %s::smallint, %s, %s, %s, %s, %s, %s ); ''' query_params = (check_adv_perm, has_comp_perm, has_team_perm, configs.service_component_type_id, adv_perm_type, timestamp, organization_id, unmasked_serv_ref_id, maintenance_id, user_id,) try: conn.execute(query, query_params) except psycopg2.IntegrityError as e: if e.pgcode == errorcodes.RESTRICT_VIOLATION: raise PermissionError(errors.err_user_rights) else: raise except psycopg2.DatabaseError: raise def add_service_integration(conn, timestamp, organization_id, service_ref_id, integ_key, integ_type, integ_name, integ_email=None, integ_url=None, access_token=None, secret_token=None, vendor_endpoint=None, vendor_endpoint_name=None, vendor_account_name=None, incoming_events=None, outgoing_events=None, conditions_map=None, payload_map=None, public_access=None, additional_info=None, external_id=None, external_info=None, updated_by=None, check_adv_perm=False, has_comp_perm=False, has_team_perm=False): ''' Adds an internal endpoint integration to a service. Internal endpoint integrations are those that are done by allocating a separate internal endpoint to receive data from the external vendor. :param conn: db connection :param timestamp: timestamp when this request is being made :param organization_id: ID of the organization :param service_ref_id: reference ID of the service :param integ_key: UUID that uniquely identifies this integration :param integ_type: (str) the type of the integration :param integ_name: user given name of the integration :param integ_email: first part of the email address of the integration if this is an email integration :param integ_url: url issued by TaskCall to receive integration messages into :param access_token: access token needed to exchange information for this integration :param secret_token: secret token needed to exchange information for this integration :param vendor_endpoint: url issued by the vendor for TaskCall to send messages to the vendor :param vendor_endpoint_name: name of the vendor's endpoint :param vendor_account_name: name of the organization's account at the vendor's :param incoming_events: (list) of incoming events that can be accepted through this integration :param outgoing_events: (list) of outgoing events that can be sent out through this integration :param conditions_map: (dict) of conditions that must be met before accepting messages :param payload_map: (dict) that maps the vendor fields and constants to TaskCall's internal variables :param public_access: (bool) that states if this integration can be connected to publicly :param additional_info: (dict) any extra information :param external_id: (str) ID of the organization on the vendor's side :param external_info: (dict) of external details of the organization :param updated_by: user_id of the user who created/updated the details :param check_adv_perm: (boolean) should advanced permissions be checked :param has_comp_perm: (boolean) does the organization have advanced permissions :param has_team_perm: (boolean) does the organization have team permissions :return: (tuple) -> (policy ID, service ID) :errors: AssertionError, DatabaseError ''' assert isinstance(timestamp, datetime.datetime) assert isinstance(organization_id, int) unmasked_serv_ref_id = key_manager.unmask_reference_key(service_ref_id) if integ_email is not None: subdomain = db_organizations.get_organization_subdomain(conn, timestamp, organization_id) integ_email = helpers.construct_taskcall_email_address(integ_email, subdomain) component_validator.validate_integration_details( integ_type, integ_name, integ_email, integ_url, access_token, secret_token, vendor_endpoint, vendor_endpoint_name, vendor_account_name, incoming_events, outgoing_events, conditions_map, payload_map, public_access, additional_info, external_id, external_info, updated_by ) if integ_email is not None: assert taskcall_email_is_unique(conn, timestamp, integ_email) if external_id is not None or external_info is not None: curr_org_integ_type_details = get_organization_integration_type_details(conn, timestamp, organization_id, integration_type=integ_type) if external_info is not None and (len(curr_org_integ_type_details) == 0 or ( external_id is not None and external_id not in [item[var_names.external_id] for item in curr_org_integ_type_details]) ): external_info = json.dumps(external_info) else: external_id = None external_info = None if conditions_map is not None: conditions_map = json.dumps(conditions_map) if payload_map is not None: payload_map = json.dumps(payload_map) if additional_info is not None: additional_info = json.dumps(additional_info) adv_perm_type = permissions.COMPONENT_ADVANCED_EDIT_PERMISSION query = ''' select create_internal_endpoint_integration( %s, %s, %s, %s::smallint, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s ); ''' query_params = (check_adv_perm, has_comp_perm, has_team_perm, configs.service_component_type_id, adv_perm_type, timestamp, constants.end_timestamp, integ_type, unmasked_serv_ref_id, organization_id, integ_key, integ_name, integ_email, integ_url, access_token, secret_token, vendor_endpoint, vendor_endpoint_name, vendor_account_name, incoming_events, outgoing_events, conditions_map, payload_map, public_access, additional_info, external_id, external_info, updated_by, constants.root_destination,) try: result = conn.fetch(query, query_params) return result[0][0] except psycopg2.IntegrityError as e: if e.pgcode == errorcodes.CHECK_VIOLATION: raise InvalidRequest(errors.err_invalid_request) elif e.pgcode == errorcodes.RESTRICT_VIOLATION: raise PermissionError(errors.err_user_rights) except psycopg2.DatabaseError: raise def edit_service_integration(conn, timestamp, organization_id, updated_by, integ_key, integ_name, additional_info=None, check_adv_perm=False, has_comp_perm=False, has_team_perm=False): ''' Edits the name of the integration. :param conn: db connection :param timestamp: timestamp when this request is being made :param organization_id: ID of the organization :param updated_by: user_id of the user who created/updated the details :param integ_key: UUID -> the integration key that uniquely identifies this integration :param integ_name: user given name of the integration :param additional_info: (dict) of additional info :param check_adv_perm: (boolean) should advanced permissions be checked :param has_comp_perm: (boolean) does the organization have advanced permissions :param has_team_perm: (boolean) does the organization have team permissions :errors: AssertionError, DatabaseError ''' assert isinstance(timestamp, datetime.datetime) assert isinstance(organization_id, int) assert isinstance(updated_by, int) assert string_validator.is_standard_name(integ_name) unmasked_integ_key = key_manager.unmask_reference_key(integ_key) adv_perm_type = permissions.COMPONENT_ADVANCED_EDIT_PERMISSION if additional_info is not None: additional_info = json.dumps(additional_info) query = ''' select edit_service_integration( %s, %s, %s, %s::smallint, %s, %s, %s, %s, %s, %s, %s, %s ); ''' query_params = (check_adv_perm, has_comp_perm, has_team_perm, configs.service_component_type_id, adv_perm_type, timestamp, constants.end_timestamp, organization_id, unmasked_integ_key, integ_name, additional_info, updated_by,) try: conn.execute(query, query_params) except psycopg2.DatabaseError: raise def edit_service_integration_vendor_details(conn, timestamp, organization_id, integ_key, vendor_endpoint=None, vendor_account=None): ''' Edits the details of the vendor. This is only to be used for internal use. :param conn: db connection :param timestamp: timestamp when this request is being made :param organization_id: ID of the organization :param integ_key: UUID -> the integration key that uniquely identifies this integration (unmasked) :param vendor_endpoint: vendor endpoint URL :param vendor_account: organization's account name at the vendor's :errors: AssertionError, DatabaseError ''' assert isinstance(timestamp, datetime.datetime) assert isinstance(organization_id, int) if vendor_endpoint is None and vendor_account is None: raise InvalidRequest(errors.err_invalid_request) query = ''' do $body$ begin update service_integrations set end_timestamp = %(timestamp)s where start_timestamp <= %(timestamp)s and end_timestamp > %(timestamp)s and organization_id = %(org_id)s and integration_key = %(integ_key)s; insert into service_integrations( select integration_id, %(timestamp)s, %(end_time)s, integration_type_id, serviceid, organization_id, integration_key, integration_name, integration_email, integration_url, access_token, secret_token, %(ven_ep)s, vendor_endpoint_name, %(ven_acc)s, incoming_events, outgoing_events, conditions_map, payload_map, public_access, additional_info from service_integrations where start_timestamp <= %(timestamp)s and end_timestamp = %(timestamp)s and organization_id = %(org_id)s and integration_key = %(integ_key)s ); end; $body$ ''' query_params = {'timestamp': timestamp, 'end_time': constants.end_timestamp, 'org_id': organization_id, 'integ_key': integ_key, 'ven_ep': vendor_endpoint, 'ven_acc': vendor_account} try: conn.execute(query, query_params) except psycopg2.DatabaseError: raise def transfer_service_integration(conn, timestamp, organization_id, updated_by, integ_key, new_serv_ref_id, check_adv_perm=False, has_comp_perm=False, has_team_perm=False): ''' Transfers an integration from one service to another. :param conn: db connection :param timestamp: timestamp when this request is being made :param organization_id: ID of the organization :param updated_by: user_id of the user who created/updated the details :param integ_key: UUID -> the integration key that uniquely identifies this integration :param new_serv_ref_id: the reference ID of the new service the integration is being transferred to :param check_adv_perm: (boolean) should advanced permissions be checked :param has_comp_perm: (boolean) does the organization have advanced permissions :param has_team_perm: (boolean) does the organization have team permissions :return: (dict) -> {integration_id: , integration_email: , service_id: , for_policy_id: } :errors: AssertionError, DatabaseError ''' assert isinstance(timestamp, datetime.datetime) assert isinstance(organization_id, int) assert isinstance(updated_by, int) unmasked_integ_key = key_manager.unmask_reference_key(integ_key) unmasked_serv_ref_id = key_manager.unmask_reference_key(new_serv_ref_id) adv_perm_type = permissions.COMPONENT_ADVANCED_EDIT_PERMISSION query = ''' select transfer_service_integration( %s, %s, %s, %s::smallint, %s, %s, %s, %s, %s, %s, %s ); ''' query_params = (check_adv_perm, has_comp_perm, has_team_perm, configs.service_component_type_id, adv_perm_type, timestamp, constants.end_timestamp, organization_id, unmasked_integ_key, unmasked_serv_ref_id, updated_by,) try: result = conn.fetch(query, query_params) return result[0][0] except psycopg2.IntegrityError as e: if e.pgcode == errorcodes.CHECK_VIOLATION: raise InvalidRequest(errors.err_invalid_request) elif e.pgcode == errorcodes.RESTRICT_VIOLATION: raise PermissionError(errors.err_user_rights) else: raise except psycopg2.DatabaseError: raise def delete_service_integration(conn, timestamp, organization_id, user_id, integ_key, check_adv_perm=False, has_comp_perm=False, has_team_perm=False): ''' Deletes a service integration. :param conn: db connection :param timestamp: timestamp when this request is being made :param organization_id: ID of the organization :param user_id: ID of the user deleting the integration :param integ_key: UUID -> the integration key that uniquely identifies this integration :param check_adv_perm: (boolean) should advanced permissions be checked :param has_comp_perm: (boolean) does the organization have advanced permissions :param has_team_perm: (boolean) does the organization have team permissions :errors: AssertionError, DatabaseError ''' assert isinstance(timestamp, datetime.datetime) assert isinstance(organization_id, int) if check_adv_perm: assert isinstance(user_id, int) unmasked_integ_key = key_manager.unmask_reference_key(integ_key) adv_perm_type = permissions.COMPONENT_ADVANCED_EDIT_PERMISSION query = ''' select delete_service_integration( %s, %s, %s, %s::smallint, %s, %s, %s, %s, %s ); ''' query_params = (check_adv_perm, has_comp_perm, has_team_perm, configs.service_component_type_id, adv_perm_type, timestamp, organization_id, unmasked_integ_key, user_id,) try: result = conn.fetch(query, query_params) return result[0][0] except psycopg2.IntegrityError as e: if e.pgcode == errorcodes.RESTRICT_VIOLATION: raise PermissionError(errors.err_user_rights) else: raise except psycopg2.DatabaseError: raise def taskcall_email_is_unique(conn, timestamp, email_addr): ''' Checks if a TaskCall issued email address is unique or not. :param conn: db connection :param timestamp: timestamp when this request is being made on :param email_addr: the TaskCall email address :return: (boolean) True if it is unique; False otherwise ''' assert isinstance(timestamp, datetime.datetime) assert string_validator.is_email_address(email_addr) query = "select taskcall_email_is_unique(%s, %s);" query_params = (timestamp, email_addr,) try: result = conn.fetch(query, query_params) return result[0][0] except psycopg2.DatabaseError: raise def get_email_integrated_service_basic_info(conn, timestamp, organization_id): ''' Get the basic info of services that have an email integration. :param conn: db connection :param timestamp: timestamp this request is being made on :param organization_id: ID of the organization :return: (list of tuple) -> [ (service id, for policy id, integration id, integration email), ... ] :errors: AssertionError, DatabaseError ''' assert isinstance(timestamp, datetime.datetime) assert isinstance(organization_id, int) query = ''' select se.serviceid, se.for_policyid, integration_id, integration_email from services as se join service_integrations as sint using(serviceid) where se.organization_id = %(org_id)s and se.start_timestamp <= %(timestamp)s and se.end_timestamp > %(timestamp)s and sint.start_timestamp <= %(timestamp)s and sint.end_timestamp > %(timestamp)s and sint.integration_email is not null; ''' query_params = {'timestamp': timestamp, 'org_id': organization_id} try: result = conn.fetch(query, query_params) return result except psycopg2.DatabaseError: raise def service_associated_task_titles(conn, timestamp, service_ref_id): ''' Get the titles of tasks that are associated to a service at a given point in time. :param conn: db connection :param timestamp: timestamp to check on :param service_ref_id: concealed service reference ID :return: (list) of task titles ''' assert isinstance(timestamp, datetime.datetime) unmasked_ref_id = key_manager.unmask_reference_key(service_ref_id) query = ''' select array_agg(title) from tasks where start_timestamp <= %(timestamp)s and end_timestamp > %(timestamp)s and serviceid is not null and serviceid in ( select serviceid from services where start_timestamp <= %(timestamp)s and end_timestamp > %(timestamp)s and service_ref_id = %(ref_id)s ); ''' query_params = {'timestamp': timestamp, 'ref_id': unmasked_ref_id} try: result = conn.fetch(query, query_params) if len(result) == 0 or result[0][0] is None: return [] else: return result[0][0] except psycopg2.DatabaseError: raise def get_service_id_and_timezone(conn, timestamp, organization_id, service_ref_id): ''' Get the timezone of the support hours of the service. :param conn: db connection :param timestamp: timestamp the request is being made on :param organization_id: ID of the organization the service belongs to :param service_ref_id: (UUID) reference ID of the service :return: (tuple) -> (service id, support timezone) ''' assert isinstance(timestamp, datetime.datetime) query = ''' select serviceid, support_timezone from services where start_timestamp <= %s and end_timestamp > %s and organization_id = %s and service_ref_id = %s; ''' query_params = (timestamp, timestamp, organization_id, service_ref_id,) try: result = conn.fetch(query, query_params) if len(result) == 1: return result[0][0], result[0][1] elif len(result) == 0: raise ValueError(errors.err_unknown_resource) else: raise SystemError(errors.err_internal_multiple_entries_found) except psycopg2.DatabaseError: raise def list_services(conn, timestamp, organization_id, user_id, check_adv_perm=False): ''' Gets the list of services given certain parameters. :param conn: db connection :param timestamp: timestamp this request is being made on :param organization_id: ID of the organization to filter by :param user_id: ID of the user_id making the request :param check_adv_perm: (boolean) True if advanced team permissions should be checked :return: (list) of dict of service details :errors: AssertionError, DatabaseError ''' assert isinstance(timestamp, datetime.datetime) assert isinstance(organization_id, int) assert isinstance(user_id, int) check_start = timestamp - datetime.timedelta(days=30) query_params = {'timestamp': timestamp, 'org_id': organization_id, 'usr_id': user_id, 'comp_type_id': configs.service_component_type_id, 'open_state': constants.open_state, 'ack_state': constants.acknowledged_state, 'check_start': check_start} serv_conds = [] if check_adv_perm: serv_conds.append(''' serviceid not in ( select component_id from components_user_cannot_view( %(timestamp)s, %(org_id)s, %(usr_id)s, %(comp_type_id)s::smallint ) ) ''') query = ''' with t1 as ( select serviceid, service_ref_id, organization_id, service_name, description, for_policyid, is_enabled from services where organization_id = %(org_id)s and start_timestamp <= %(timestamp)s and end_timestamp > %(timestamp)s {0} ) , t2 as ( select component_id as serviceid, json_agg(json_build_object( 'team_ref_id', team_ref_id, 'team_name', team_name )) as service_teams from team_components as tco join teams using (team_id) where tco.start_timestamp <= %(timestamp)s and tco.end_timestamp > %(timestamp)s and tco.component_type_id = %(comp_type_id)s and teams.organization_id = %(org_id)s and teams.start_timestamp <= %(timestamp)s and teams.end_timestamp > %(timestamp)s group by serviceid ) , t3 as ( select supporting_serviceid as serviceid, json_agg(json_build_object( 'business_service_ref_id', bs.business_service_ref_id, 'service_name', bs.service_name )) as service_bus from business_service_dependencies as bssd join business_services as bs using(business_serviceid) where bs.start_timestamp <= %(timestamp)s and bs.end_timestamp > %(timestamp)s and bssd.start_timestamp <= %(timestamp)s and bssd.end_timestamp > %(timestamp)s and bssd.is_technical = true and bssd.supporting_serviceid in (select serviceid from t1) group by bssd.supporting_serviceid ) , t4 as ( select t1.*, t2.service_teams, t3.service_bus from t1 left join t2 using(serviceid) left join t3 using(serviceid) ) , t5 as ( select serviceid, max(ti.instance_timestamp) as last_instance_time, sum(case when is_open = true and status = %(open_state)s then 1 else 0 end) as open_count, sum(case when is_open = true and status = %(ack_state)s then 1 else 0 end) as ack_count from tasks join task_instances as ti on ti.taskid = tasks.taskid where tasks.organization_id = %(org_id)s and ti.organization_id = %(org_id)s and tasks.serviceid in (select serviceid from t4) group by serviceid ) , t6 as ( select serviceid, json_object_agg( timezone(org.organization_timezone, timezone('UTC', instance_timestamp))::date, instance_count ) as periodic_health from ( select ti.organization_id, serviceid, instance_timestamp, count(instanceid) as instance_count from task_instances as ti join tasks on ti.taskid = tasks.taskid and ti.organization_id = tasks.organization_id where tasks.organization_id = %(org_id)s and tasks.serviceid in (select serviceid from t4) and instance_timestamp >= %(check_start)s and instance_timestamp <= %(timestamp)s group by ti.organization_id, serviceid, instance_timestamp ) as sub_table join organizations as org using(organization_id) group by serviceid ) select service_ref_id, t4.organization_id, service_name, description, for_policyid, is_enabled, service_teams, service_bus, t5.last_instance_time, case when t5.open_count is null then 0 else t5.open_count end, case when t5.ack_count is null then 0 else t5.ack_count end, t6.periodic_health from t4 left join t5 using(serviceid) left join t6 using(serviceid) order by open_count desc, ack_count desc, service_name asc; '''.format(' and ' + ' and '.join(serv_conds) if len(serv_conds) > 0 else '') try: result = conn.fetch(query, query_params) data = [] for ref_id, org_id, serv_name, desc, pol_id, is_enbl, serv_teams, serv_bus, last_inst_time,\ open_count, ack_count, periodic_health in result: periodic_health = dict() if periodic_health is None else periodic_health dt_range = [check_start + datetime.timedelta(days=i) for i in range(0, (timestamp - check_start).days + 1)] formatted_ph = [] for dt_ in dt_range: dt_str_ = datetime.datetime.strftime(dt_, constants.date_hyphen_format) count = periodic_health[dt_str_] if dt_str_ in periodic_health else 0 formatted_ph.append({var_names.period: dt_, var_names.count: count}) data.append({ var_names.service_ref_id: key_manager.conceal_reference_key(ref_id), var_names.service_name: serv_name, var_names.description: desc, var_names.teams: [[item[var_names.team_name], key_manager.conceal_reference_key(UUID(item[var_names.team_ref_id]))] for item in serv_teams] if serv_teams is not None else [], var_names.business_services: [ [item[var_names.service_name], key_manager.conceal_reference_key(UUID(item[var_names.business_service_ref_id]))] for item in serv_bus] if serv_bus is not None else [], var_names.policy_id: pol_id, var_names.last_instance_timestamp: last_inst_time, var_names.open_incident_count: [open_count if open_count is not None else 0, ack_count if ack_count is not None else 0], var_names.last_30_days: formatted_ph }) return data except psycopg2.DatabaseError: raise def get_service_details(conn, timestamp, organization_id, user_id, service_ref_id, check_adv_perm=False): ''' Get the details of a service. :param conn: db connection :param timestamp: timestamp when this request is being made :param organization_id: ID of the organization the service belongs to :param user_id: user_id of the user making the request :param service_ref_id: reference ID of the service being checked :param check_adv_perm: (boolean) True if advanced team permissions should be checked :return: (dict) of service details ''' assert isinstance(timestamp, datetime.datetime) assert isinstance(organization_id, int) assert isinstance(user_id, int) unmasked_serv_ref_id = key_manager.unmask_reference_key(service_ref_id) query = " select * from get_service_details(%s, %s::smallint, %s, %s, %s, %s); " query_params = (check_adv_perm, configs.service_component_type_id, timestamp, organization_id, unmasked_serv_ref_id, user_id,) try: result = conn.fetch(query, query_params) data = dict() for serv_ref_id, serv_name, desc, is_enabled, pol_ref_id, trig_min, supp_days, supp_start, supp_end, supp_tz,\ de_prioritize, re_prioritize, allow_grp, mnt_, bus_servs, srv_tags in result: mnt_items = [[item[var_names.maintenance_id], times.get_timestamp_from_string(item[var_names.maintenance_start]), times.get_timestamp_from_string(item[var_names.maintenance_end]), item[var_names.timezone], item[var_names.reason]] for item in mnt_]\ if mnt_ is not None else None serv_status = constants.enabled if not is_enabled: serv_status = constants.disabled elif is_enabled and mnt_items is not None: # this is only to check if the service is in maintenance or not serv = Service(unmasked_serv_ref_id, organization_id, serv_name, desc, is_enabled, pol_ref_id, support_timezone=supp_tz, current_maintenances=[[item[1], item[2], item[3]] for item in mnt_items]) if serv.in_maintenance(timestamp): serv_status = constants.maintenance data = { var_names.service_ref_id: key_manager.conceal_reference_key(serv_ref_id), var_names.service_name: serv_name, var_names.description: desc, var_names.is_enabled: is_enabled, var_names.status: serv_status, var_names.policy_ref_id: key_manager.conceal_reference_key(pol_ref_id), var_names.re_trigger_minutes: trig_min, var_names.support_days: supp_days, var_names.support_start: supp_start, var_names.support_end: supp_end, var_names.timezone: supp_tz, var_names.de_prioritize: de_prioritize, var_names.re_prioritize: re_prioritize, var_names.allow_grouping: allow_grp, var_names.maintenances: mnt_items, var_names.business_services: [ [item[var_names.service_name], key_manager.conceal_reference_key(UUID(item[var_names.business_service_ref_id]))] for item in bus_servs] if bus_servs is not None else None, var_names.tags: srv_tags } return data except psycopg2.IntegrityError as e: if e.pgcode == errorcodes.RESTRICT_VIOLATION: raise PermissionError(errors.err_user_rights) else: raise except psycopg2.DatabaseError: raise def get_service(conn, timestamp, service_id=None, service_ref_id=None, organization_id=None): ''' Get the details of a service. For internal use only :param conn: db connection :param timestamp: timestamp when this request is being made :param service_id: (int or list) ID of the service(s) being checked :param service_ref_id: reference ID of the service :param organization_id: ID of the organization the service belongs to :return: (dict) of Service objects ''' assert isinstance(timestamp, datetime.datetime) assert service_id is not None or service_ref_id is not None or organization_id is not None query_params = {'timestamp': timestamp} conditions = [] if service_id is not None: conditions.append(" sd.serviceid = any(%(serv_id)s) ") query_params['serv_id'] = helpers.get_int_list(service_id) if service_ref_id is not None: conditions.append(" sd.service_ref_id = %(serv_ref_id)s ") query_params['serv_ref_id'] = service_ref_id if isinstance(service_ref_id, UUID)\ else key_manager.unmask_reference_key(service_ref_id) if organization_id is not None: assert isinstance(organization_id, int) conditions.append(" sd.organization_id = %(org_id)s ") query_params['org_id'] = organization_id query = ''' with t1 as ( select sd.serviceid, sd.organization_id, service_ref_id, service_name, sd.description, is_enabled, for_policyid, retrigger_minutes, support_days, support_start_time, support_end_time, support_timezone, off_hours_deprioritize, on_hours_reprioritize, allow_grouping from services as sd where sd.start_timestamp <= %(timestamp)s and sd.end_timestamp > %(timestamp)s {0} ) , t2 as ( select serviceid, json_agg(json_build_object( 'maintenance_start', maintenance_start, 'maintenance_end', maintenance_end, 'timezone', maintenance_timezone )) as curr_mnt from service_maintenances where start_timestamp <= %(timestamp)s and end_timestamp > %(timestamp)s and serviceid in (select serviceid from t1) group by serviceid ) , t3 as ( select serviceid, json_agg(json_build_object( 'integration_id', integration_id, 'integration_type_id', integration_type_id, 'integration_type', integration_type, 'is_incoming', incoming, 'is_outgoing', outgoing, 'integration_name', integration_name, 'email', integration_email, 'integration_key', integration_key, 'integration_url', integration_url, 'access_token', access_token, 'secret_token', secret_token, 'vendor_endpoint', vendor_endpoint, 'vendor_endpoint_name', vendor_endpoint_name, 'vendor_account_name', vendor_account_name, 'incoming_events', incoming_events, 'outgoing_events', outgoing_events, 'conditions_map', conditions_map, 'payload_map', payload_map, 'is_public', public_access, 'additional_info', additional_info )) as integ_det from service_integrations as si join integration_types as it using(integration_type_id) where it.start_date <= %(timestamp)s and it.end_date > %(timestamp)s and si.start_timestamp <= %(timestamp)s and si.end_timestamp > %(timestamp)s and si.serviceid in (select serviceid from t1) group by serviceid ) , t4 as ( select supporting_serviceid as serviceid, json_agg(json_build_object( 'business_service_id', bs.business_serviceid, 'service_name', bs.service_name, 'min_urgency', bs.min_urgency )) as service_bus from business_service_dependencies as bssd join business_services as bs using(business_serviceid) where bs.start_timestamp <= %(timestamp)s and bs.end_timestamp > %(timestamp)s and bssd.start_timestamp <= %(timestamp)s and bssd.end_timestamp > %(timestamp)s and bssd.is_technical = true and bssd.supporting_serviceid in (select serviceid from t1) group by bssd.supporting_serviceid ) select t1.*, t2.curr_mnt, t3.integ_det, t4.service_bus from t1 left join t2 using(serviceid) left join t3 using(serviceid) left join t4 using(serviceid); '''.format('' if len(conditions) == 0 else ' and ' + ' and '.join(conditions)) try: result = conn.fetch(query, query_params) data = dict() for serv_id, org_id, ref_id, serv_name, desc, is_enabled, p_id, trig_min, supp_days, supp_start, supp_end,\ supp_tz, deprioritize, reprioritize, allow_grp, curr_mnt, integs, bus_servs in result: mnt_items = [[times.get_timestamp_from_string(item[var_names.maintenance_start]), times.get_timestamp_from_string(item[var_names.maintenance_end]), item[var_names.timezone]] for item in curr_mnt] \ if curr_mnt is not None else None data[serv_id] = Service(serv_id, org_id, serv_name, desc, is_enabled, p_id, re_trigger_minutes=trig_min, support_days=supp_days, support_start=supp_start, support_end=supp_end, support_timezone=supp_tz, off_hours_deprioritize=deprioritize, on_hours_reprioritize=reprioritize, allow_grouping=allow_grp, service_ref_id=ref_id, current_maintenances=mnt_items, service_integrations=integs, business_services=bus_servs) return data except psycopg2.DatabaseError: raise def get_service_policy_id(conn, timestamp, organization_id, service_ref_id): ''' Get the policy id that a given service is associated to :param conn: db connection :param timestamp: timestamp when this request is being made :param service_ref_id: reference ID of the service being checked :param organization_id: ID of the organization the service belongs to :return: tuple -> (service ID, policy ID) ''' assert isinstance(timestamp, datetime.datetime) assert isinstance(organization_id, int) unmasked_serv_ref_id = key_manager.unmask_reference_key(service_ref_id) query = ''' select serviceid, for_policyid from services where start_timestamp <= %s and end_timestamp > %s and organization_id = %s and service_ref_id = %s; ''' query_params = (timestamp, timestamp, organization_id, unmasked_serv_ref_id,) try: result = conn.fetch(query, query_params) if len(result) == 1: return result[0][0], result[0][1] elif len(result) == 0: raise LookupError(errors.err_unknown_resource) else: raise SystemError(errors.err_internal_multiple_entries_found) except psycopg2.DatabaseError: raise def get_service_integrations(conn, timestamp, organization_id, service_ref_id, user_id=None, check_adv_perm=False): ''' Get the list of all integrations of a service. This does not do an adv check. :param conn: db connection :param timestamp: timestamp when this request is being made :param organization_id: ID of the organization the service belongs to :param service_ref_id: reference ID of the service :param user_id: user_id of the user making the request :param check_adv_perm: (boolean) True if advanced team permissions should be checked :return: (list of dict) -> [ { integration details }, ... ] ''' assert isinstance(timestamp, datetime.datetime) unmasked_serv_ref_id = key_manager.unmask_reference_key(service_ref_id) query_params = {'timestamp': timestamp, 'ref_id': unmasked_serv_ref_id, 'org_id': organization_id} serv_cond = '' if check_adv_perm: assert user_id is not None and isinstance(user_id, int) serv_cond = ''' and serviceid not in ( select component_id from components_user_cannot_view( %(timestamp)s, %(org_id)s, %(usr_id)s, %(comp_type_id)s::smallint ) ) ''' query_params['usr_id'] = user_id query_params['comp_type_id'] = configs.service_component_type_id query = ''' select integration_key, integration_name, integration_email, integration_url, it.integration_type, it.description, it.icon_url, si.additional_info from service_integrations as si join integration_types as it using(integration_type_id) where si.start_timestamp <= %(timestamp)s and si.end_timestamp > %(timestamp)s and it.start_date <= %(timestamp)s and it.end_date > %(timestamp)s and si.serviceid in ( select serviceid from services where start_timestamp <= %(timestamp)s and end_timestamp > %(timestamp)s and service_ref_id = %(ref_id)s {0} ) order by integration_name; '''.format(serv_cond) try: result = conn.fetch(query, query_params) data = [] for key_, name_, email_, url_, type_, desc_, icon_, info_ in result: data.append({ var_names.integration_key: key_manager.conceal_reference_key(key_), var_names.integration_name: name_, var_names.email: email_, var_names.integration_url: url_, var_names.integration_type: type_, var_names.description: desc_, var_names.icon_url: icon_, var_names.additional_info: info_ }) return data except psycopg2.DatabaseError: raise def get_service_integrations_for_internal_use(conn, timestamp, organization_id=None, account_wide=False): ''' Get the details of a service. For internal use only :param conn: db connection :param timestamp: timestamp when this request is being made :param organization_id: ID of the organization the service belongs to :param account_wide: if only account wide integrations are needed :return: (list) of integration details ''' assert isinstance(timestamp, datetime.datetime) assert organization_id is not None query_params = {'timestamp': timestamp} conditions = [] if organization_id is not None: assert isinstance(organization_id, int) conditions.append(" si.organization_id = %(org_id)s ") query_params['org_id'] = organization_id if account_wide: conditions.append(" it.integration_type = any(%(int_typ_list)s) ") query_params['int_typ_list'] = configs.allowed_account_wide_integrations query = ''' select json_build_object( 'integration_id', integration_id, 'integration_type_id', integration_type_id, 'integration_type', integration_type, 'is_incoming', incoming, 'is_outgoing', outgoing, 'integration_name', integration_name, 'email', integration_email, 'integration_key', integration_key, 'integration_url', integration_url, 'access_token', access_token, 'secret_token', secret_token, 'vendor_endpoint', vendor_endpoint, 'vendor_endpoint_name', vendor_endpoint_name, 'vendor_account_name', vendor_account_name, 'incoming_events', incoming_events, 'outgoing_events', outgoing_events, 'conditions_map', conditions_map, 'payload_map', payload_map, 'is_public', public_access, 'additional_info', additional_info ) as integ_det from service_integrations as si join integration_types as it using(integration_type_id) where it.start_date <= %(timestamp)s and it.end_date > %(timestamp)s and si.start_timestamp <= %(timestamp)s and si.end_timestamp > %(timestamp)s {0}; '''.format('' if len(conditions) == 0 else ' and ' + ' and '.join(conditions)) try: result = conn.fetch(query, query_params) data = [] for item in result: data.append(item[0]) return data except psycopg2.DatabaseError: raise def get_service_integration_key_details(conn, timestamp, integration_key, with_type_id=False): ''' Get the basic details of an integration given the integration key. :param conn: db connection :param timestamp: timestamp when this request is being made :param integration_key: (UUID) the integration key :param with_type_id: if True then the integration type ID is also returned as the fourth item in the tuple :return: (list) -> [integration ID, service ID, organization ID]; None if integration key does not match :errors: AssertionError, DatabaseError ''' assert isinstance(integration_key, UUID) query = ''' select integration_id, serviceid, organization_id, integration_type_id from service_integrations where start_timestamp <= %s and end_timestamp > %s and integration_key = %s; ''' query_params = (timestamp, timestamp, integration_key,) try: result = conn.fetch(query, query_params) if len(result) == 0: return None else: if with_type_id: return [result[0][0], result[0][1], result[0][2], result[0][3]] else: return [result[0][0], result[0][1], result[0][2]] except psycopg2.DatabaseError: raise def get_all_service_maintenances(conn, timestamp, organization_id, user_id, check_adv_perm=False): ''' Get the list of maintenances that are scheduled across all services. :param conn: db connection :param timestamp: timestamp when this request is being made :param organization_id: ID of the organization the service belongs to :param user_id: user_id of the user making the request :param check_adv_perm: (boolean) True if advanced team permissions should be checked :return: (list of dict) -> [ { integration details }, ... ] ''' assert isinstance(timestamp, datetime.datetime) query_params = {'timestamp': timestamp, 'org_id': organization_id, 'user_name': user_id, 'comp_type_id': configs.service_component_type_id} serv_cond = '' if check_adv_perm: serv_cond = ''' and serviceid not in ( select component_id from components_user_cannot_view( %(timestamp)s, %(org_id)s, %(user_name)s, %(comp_type_id)s::smallint ) ) ''' query = ''' select sv.service_ref_id, sv.service_name, json_agg(json_build_object( 'maintenance_id', maintenance_id, 'maintenance_start', maintenance_start, 'maintenance_end', maintenance_end, 'timezone', maintenance_timezone, 'reason', reason )) as mnts from service_maintenances as sm join services as sv using(serviceid) where sm.start_timestamp <= %(timestamp)s and sm.end_timestamp > %(timestamp)s and sv.start_timestamp <= %(timestamp)s and sv.end_timestamp > %(timestamp)s {0} group by sv.service_ref_id, sv.service_name; '''.format(serv_cond) try: result = conn.fetch(query, query_params) data = [] for ref_, name_, mnt_ in result: for item in mnt_: data.append({ var_names.service_ref_id: key_manager.conceal_reference_key(ref_), var_names.service_name: name_, var_names.maintenances: [item[var_names.maintenance_id], item[var_names.maintenance_start], item[var_names.maintenance_end], item[var_names.timezone], item[var_names.reason]] }) return data except psycopg2.DatabaseError: raise def get_basic_services_list(conn, timestamp, organization_id, user_id=None): ''' Get the basic services list. :param conn: db connection :param timestamp: timestamp this request is being made on :param organization_id: ID of the organization :param user_id: ID of the user to check team permissions for :return: (list of list) -> [ [service name, service ref id], ... ] :errors: AssertionError, DatabaseError ''' assert isinstance(timestamp, datetime.datetime) assert isinstance(organization_id, int) query_params = {'timestamp': timestamp, 'org_id': organization_id} conditions = [] if user_id is not None: assert isinstance(user_id, int) conditions.append(''' serviceid not in ( select component_id from components_user_cannot_view( %(timestamp)s, %(org_id)s, %(usr_id)s, %(comp_type_id)s::smallint ) ) ''') query_params['usr_id'] = user_id query_params['comp_type_id'] = configs.service_component_type_id query = ''' select service_name, service_ref_id from services where start_timestamp <= %(timestamp)s and end_timestamp > %(timestamp)s and organization_id = %(org_id)s {0} order by service_name; '''.format(' and ' + ' and '.join(conditions) if len(conditions) > 0 else '') try: result = conn.fetch(query, query_params) data = [] for name_, key_ in result: data.append([name_, key_manager.conceal_reference_key(key_)]) return data except psycopg2.DatabaseError: raise def internalize_service_ref_ids_in_routing_rules_and_actions(conn, timestamp, organization_id, rules: list, actions: dict): ''' Convert service ref IDs provided in conditional routing "rules" to service ID. :param conn: db connection :param timestamp: timestamp when this request is being made :param organization_id: ID of the organization :param rules: (list of dict) of rules :param actions: (dict) of actions to take for the conditional routing :return: (tuple) -> ((list of dict) of rules with internalized service IDs, dict of actions with internalized service IDs) :errors: AssertionError, DatabaseError, LookupError ''' assert isinstance(timestamp, datetime.datetime) assert isinstance(organization_id, int) assert isinstance(rules, list) assert isinstance(actions, dict) received_serv_refs = [] cond_types = [item[var_names.rule_type] for item in rules] if var_names.service in cond_types: received_serv_refs = [item[var_names.field_value] for item in rules if item[var_names.rule_type] == var_names.service] if var_names.route_to_service in actions: received_serv_refs.append(actions[var_names.route_to_service]) if len(received_serv_refs) > 0: serv_dict = list_service_ids_from_ref_ids(conn, timestamp, organization_id, received_serv_refs, unmasked=False, as_dict=True) for item in rules: if item[var_names.rule_type] == var_names.service: unmasked_provided_ref = key_manager.unmask_reference_key(item[var_names.field_value]) if unmasked_provided_ref in serv_dict: item[var_names.field_value] = serv_dict[unmasked_provided_ref] else: raise LookupError(errors.err_unknown_resource) if var_names.route_to_service in actions: unmasked_route_to_srv_ref = key_manager.unmask_reference_key(actions[var_names.route_to_service]) actions[var_names.route_to_service] = serv_dict[unmasked_route_to_srv_ref] return rules, actions def list_service_ids_from_ref_ids(conn, timestamp, organization_id, service_ref_ids=None, unmasked=True, as_dict=False): ''' Get the IDs of services as a list given their reference ids. :param conn: db connection :param timestamp: timestamp when this request is being made :param organization_id: organization id the policies belong to :param service_ref_ids: (list) of service reference ids :param unmasked: True if the given reference IDs are unmasked; False otherwise :param as_dict: True if the data should be returned as dict :return: (list) -> [service ID 1, service ID 2, ...] | (dict) -> {ref id: serv id, ...} ''' assert isinstance(timestamp, datetime.datetime) conditions = [] query_params = {'timestamp': timestamp, 'org_id': organization_id} if service_ref_ids is not None: assert isinstance(service_ref_ids, list) if not unmasked: service_ref_ids = [key_manager.unmask_reference_key(x) for x in service_ref_ids] conditions.append('service_ref_id = any(%(srv_list)s)') query_params['srv_list'] = service_ref_ids query = ''' select serviceid, service_ref_id from services where start_timestamp <= %(timestamp)s and end_timestamp > %(timestamp)s and organization_id = %(org_id)s {0}; '''.format(' and ' + ' and '.join(conditions) if len(conditions) > 0 else '') try: result = conn.fetch(query, query_params) if as_dict: data = dict() for id_, ref_ in result: data[ref_] = id_ return data else: data = [] for id_, ref_ in result: data.append(id_) return data except psycopg2.DatabaseError: raise def get_organization_integration_type_details(conn, timestamp, organization_id=None, integration_type_id=None, integration_type=None, external_id=None): ''' Get the external vendor details of an organization for a specific type of integration. :param conn: db connection :param timestamp: (datetime.datetime) timestamp the request is being made :param organization_id: (int) ID of the organization :param integration_type_id: (int) the integration type ID :param integration_type: (str) the name of the type of integration :param external_id: (str) ID of the organization on the external vendor's side :return: (list) of dict :errors: AssertionError, DatabaseError ''' assert isinstance(timestamp, datetime.datetime) query_params = {'timestamp': timestamp} conditions = [] if organization_id is not None: assert isinstance(organization_id, int) conditions.append(" organization_id = %(org_id)s ") query_params['org_id'] = organization_id if integration_type_id is not None: assert isinstance(integration_type_id, int) conditions.append(" integration_type_id = %(integ_type_id)s ") query_params['integ_type_id'] = integration_type_id if integration_type is not None: assert integration_type in configs.allowed_integration_types conditions.append(''' integration_type_id in ( select integration_type_id from integration_types where start_date <= %(timestamp)s and end_date > %(timestamp)s and integration_type = %(integ_type_name)s ) ''') query_params['integ_type_name'] = integration_type if external_id is not None: assert isinstance(external_id, str) conditions.append(" external_id = %(ext_id)s ") query_params['ext_id'] = external_id query = ''' select organization_id, integration_type_id, external_id, details from organization_integration_type_details where start_timestamp <= %(timestamp)s and end_timestamp > %(timestamp)s {0}; '''.format(' and ' + ' and '.join(conditions) if len(conditions) > 0 else '') try: result = conn.fetch(query, query_params) data = [] for og_id, integ_typ_id, ext_id, ext_info in result: data.append({var_names.organization_id: og_id, var_names.integration_type_id: integ_typ_id, var_names.external_id: ext_id, var_names.external_info: ext_info}) return data except psycopg2.DatabaseError: raise def update_organization_integration_type_details(conn, timestamp, organization_id, integration_type, new_details, external_id=None): ''' Update the external vendor details of a specific type of integration of an organization. :param conn: db connection :param timestamp: (datetime.datetime) timestamp when this request is being made :param organization_id: (int) ID of the organization :param integration_type: (str) name of the type of integration :param new_details: (dict) of new details :param external_id: (str) vendor provided ID :errors: AssertionError, DatabaseError ''' assert isinstance(timestamp, datetime.datetime) assert isinstance(organization_id, int) assert integration_type in configs.allowed_integration_types if new_details is not None: assert isinstance(new_details, dict) new_details = json.dumps(new_details) query_params = {'timestamp': timestamp, 'org_id': organization_id, 'integ_type': integration_type, 'new_det': new_details} conditions = [] if external_id is not None: assert isinstance(external_id, str) conditions.append(" external_id = %(ext_id)s ") query_params['ext_id'] = external_id query = ''' update organization_integration_type_details set details = %(new_det)s where start_timestamp <= %(timestamp)s and end_timestamp > %(timestamp)s and organization_id = %(org_id)s and integration_type_id in ( select integration_type_id from integration_types where start_date <= %(timestamp)s and end_date > %(timestamp)s and integration_type = %(integ_type)s ) {0}; '''.format(' and ' + ' and '.join(conditions) if len(conditions) > 0 else '') try: conn.execute(query, query_params) except psycopg2.DatabaseError: raise def get_service_name_ref_keyed_on_id(conn, timestamp, organization_id, service_ids=None): ''' Get the name and ref id of services keyed on their ID. This is primarily used in conditional routing to externalize data when it is being prepared to be sent to the user. :param conn: db connection :param timestamp: timestamp when this request is being made :param organization_id: ID of the organization :param service_ids: (list of int) service IDs :return: (dict of list) -> { serv ID: [serv name, serv ref], ... } ''' assert isinstance(timestamp, datetime.datetime) assert isinstance(organization_id, int) query_params = {'timestamp': timestamp, 'org_id': organization_id} conditions = [] if service_ids is not None: assert isinstance(service_ids, list) conditions.append(" serviceid = any(%(srv_ids)s)") query_params['srv_ids'] = service_ids query = ''' select serviceid, service_name, service_ref_id from services where start_timestamp <= %(timestamp)s and end_timestamp > %(timestamp)s and organization_id = %(org_id)s {0}; '''.format(' and ' + ' and '.join(conditions) if len(conditions) > 0 else '') try: result = conn.fetch(query, query_params) data = dict() for id_, name_, ref_ in result: data[id_] = [name_, key_manager.conceal_reference_key(ref_)] return data except psycopg2.DatabaseError: raise def tech_dependencies_for_internal_use(conn, timestamp, org_id): ''' :FOR INTERNAL USE ONLY: Gets the data needed to evaluate circular dependency of technical services. :param conn: db connection :param timestamp: timestamp when this request was made :param org_id: the organization ID :return: (dict) -> {service id: [supp serv ID, ...], ...} :error: AssertionError, DatabaseError ''' assert isinstance(timestamp, datetime.datetime) assert isinstance(org_id, int) query = ''' select srv.serviceid, srv.service_name, json_agg(supporting_serviceid) as supp from services as srv left join service_dependencies as dep using(serviceid) where srv.organization_id = %(org_id)s and srv.start_timestamp <= %(timestamp)s and srv.end_timestamp > %(timestamp)s and dep.start_timestamp <= %(timestamp)s and dep.end_timestamp > %(timestamp)s group by srv.serviceid, srv.service_name; ''' query_params = {'timestamp': timestamp, 'org_id': org_id} try: result = conn.fetch(query, query_params) data = dict() for id_, name_, supp in result: supp = supp if supp is not None else [] data[id_] = { var_names.name: name_, var_names.dependencies: supp } return data except psycopg2.DatabaseError: raise def business_dependencies_for_internal_use(conn, timestamp, org_id): ''' :FOR INTERNAL USE ONLY: Gets the data needed to evaluate circular dependency of business services. :param conn: db connection :param timestamp: timestamp when this request was made :param org_id: the organization ID :return: (dict) -> {business service id: [supp business serv ID, ...], ...} :error: AssertionError, DatabaseError ''' assert isinstance(timestamp, datetime.datetime) assert isinstance(org_id, int) query = ''' select bus.business_serviceid, bus.service_name, json_agg(supporting_serviceid) as supp from business_services as bus left join business_service_dependencies as dep using(business_serviceid) where bus.organization_id = %(org_id)s and bus.start_timestamp <= %(timestamp)s and bus.end_timestamp > %(timestamp)s and dep.start_timestamp <= %(timestamp)s and dep.end_timestamp > %(timestamp)s and dep.is_technical = false group by bus.business_serviceid, bus.service_name; ''' query_params = {'timestamp': timestamp, 'org_id': org_id} try: result = conn.fetch(query, query_params) data = dict() for id_, name_, supp in result: supp = supp if supp is not None else [] data[id_] = { var_names.name: name_, var_names.dependencies: supp } return data except psycopg2.DatabaseError: raise def get_dependency_nodes_from_ref_ids(conn, timestamp, organization_id, from_node, to_node): ''' Get the IDs of services as a list given their reference ids. :param conn: db connection :param timestamp: timestamp when this request is being made :param organization_id: organization id the policies belong to :param from_node: (list of length 2) -> [concealed ref id, is_technical] :param to_node: (list of length 2) -> [concealed ref id, is_technical] :return: (tuple) -> (from id, is_tech), (to id, is_tech) :error: AssertionError, LookupError ''' assert isinstance(timestamp, datetime.datetime) assert isinstance(organization_id, int) assert isinstance(from_node, list) and len(from_node) == 2 assert isinstance(to_node, list) and len(to_node) == 2 from_unmasked, is_from_tech = key_manager.unmask_reference_key(from_node[0]), from_node[1] to_unmasked, is_to_tech = key_manager.unmask_reference_key(to_node[0]), to_node[1] query = ''' select serviceid, service_ref_id, true from services where start_timestamp <= %(timestamp)s and end_timestamp > %(timestamp)s and organization_id = %(org_id)s union select business_serviceid, business_service_ref_id, false from business_services where start_timestamp <= %(timestamp)s and end_timestamp > %(timestamp)s and organization_id = %(org_id)s; ''' query_params = {'timestamp': timestamp, 'org_id': organization_id} try: result = conn.fetch(query, query_params) data = dict() for id_, ref_, is_tech in result: data[(ref_, is_tech)] = id_ new_from_node, new_to_node = None, None if (from_unmasked, is_from_tech) in data: new_from_node = (data[(from_unmasked, is_from_tech)], is_from_tech) if (to_unmasked, is_to_tech) in data: new_to_node = (data[(to_unmasked, is_to_tech)], is_to_tech) if new_from_node is None or new_to_node is None: raise LookupError(errors.err_unknown_resource) else: return new_from_node, new_to_node except psycopg2.DatabaseError: raise def get_system_dependencies(conn, timestamp, org_id): ''' Get details of all technical and business service dependencies that will be needed for the service graph. "WARNING": This query returns the actual policy id and does not include the on-call. The policy ref id and the on-call must be added in the view that calls the function. :param conn: db connection :param timestamp: timestamp when this request was made :param org_id: the organization ID :return: (dict of dict) of service dependency related details :errors: AssertionError, DatabaseError ''' assert isinstance(timestamp, datetime.datetime) assert isinstance(org_id, int) query = ''' with t1 as ( select srv.serviceid, srv.service_name, srv.service_ref_id, srv.description, for_policyid, is_enabled from services as srv where start_timestamp <= %(timestamp)s and end_timestamp > %(timestamp)s and organization_id = %(org_id)s ) , t2 as ( select dep.serviceid, json_agg(t1.service_ref_id) as supp_tech from service_dependencies as dep join t1 on t1.serviceid = dep.supporting_serviceid where dep.start_timestamp <= %(timestamp)s and dep.end_timestamp > %(timestamp)s group by dep.serviceid ) , t3 as ( select component_id as serviceid, json_agg(json_build_object( 'team_ref_id', team_ref_id, 'team_name', team_name )) as service_teams from team_components as tco join teams using (team_id) where tco.start_timestamp <= %(timestamp)s and tco.end_timestamp > %(timestamp)s and tco.component_type_id = %(srv_comp_type_id)s and teams.organization_id = %(org_id)s and teams.start_timestamp <= %(timestamp)s and teams.end_timestamp > %(timestamp)s group by serviceid ) , t4 as ( select tasks.serviceid, json_agg(json_build_object( 'organization_instance_id', organization_instanceid, 'task_title', tasks.title )) as service_insts from task_instances as inst join tasks using(taskid, organization_id) where inst.is_open = true and inst.organization_id = %(org_id)s and tasks.organization_id = %(org_id)s and tasks.start_timestamp <= %(timestamp)s and tasks.end_timestamp > %(timestamp)s and tasks.serviceid is not null group by tasks.serviceid ) , t5 as ( select bus.business_serviceid, bus.service_name, bus.business_service_ref_id, bus.description from business_services as bus where start_timestamp <= %(timestamp)s and end_timestamp > %(timestamp)s and organization_id = %(org_id)s ) , t6 as ( select dep.business_serviceid, json_agg(services.service_ref_id) as supp_tech from business_service_dependencies as dep join services on services.serviceid = dep.supporting_serviceid where dep.start_timestamp <= %(timestamp)s and dep.end_timestamp > %(timestamp)s and is_technical = true and services.start_timestamp <= %(timestamp)s and services.end_timestamp > %(timestamp)s and services.organization_id = %(org_id)s group by dep.business_serviceid ) , t7 as ( select dep.business_serviceid, json_agg(t5.business_service_ref_id) as bus_tech from business_service_dependencies as dep join t5 on t5.business_serviceid = dep.supporting_serviceid where dep.start_timestamp <= %(timestamp)s and dep.end_timestamp > %(timestamp)s and is_technical = false group by dep.business_serviceid ) , t8 as ( select component_id as business_serviceid, json_agg(json_build_object( 'team_ref_id', team_ref_id, 'team_name', team_name )) as bus_teams from team_components as tco join teams using (team_id) where tco.start_timestamp <= %(timestamp)s and tco.end_timestamp > %(timestamp)s and tco.component_type_id = %(bus_comp_type_id)s and teams.organization_id = %(org_id)s and teams.start_timestamp <= %(timestamp)s and teams.end_timestamp > %(timestamp)s group by business_serviceid ) , t9 as ( select iibs.business_serviceid, json_agg(json_build_object( 'organization_instance_id', organization_instanceid, 'task_title', tasks.title )) as bus_insts from task_instances as inst join tasks using(taskid, organization_id) join instance_impacted_business_services as iibs using(instanceid) where inst.is_open = true and inst.organization_id = %(org_id)s and tasks.organization_id = %(org_id)s and tasks.start_timestamp <= %(timestamp)s and tasks.end_timestamp > %(timestamp)s and iibs.start_timestamp <= %(timestamp)s and iibs.end_timestamp > %(timestamp)s group by iibs.business_serviceid ) select t1.service_ref_id, t1.service_name, t1.description, true, t1.for_policyid, t1.is_enabled, t2.supp_tech, null as bus_tech, t3.service_teams, t4.service_insts from t1 left join t2 using(serviceid) left join t3 using(serviceid) left join t4 using(serviceid) union all select t5.business_service_ref_id, t5.service_name, t5.description, false, null, true, t6.supp_tech, t7.bus_tech, t8.bus_teams as service_teams, t9.bus_insts as service_insts from t5 left join t6 using(business_serviceid) left join t7 using(business_serviceid) left join t8 using(business_serviceid) left join t9 using(business_serviceid); ''' query_params = {'timestamp': timestamp, 'org_id': org_id, 'srv_comp_type_id': configs.service_component_type_id, 'bus_comp_type_id': configs.business_service_component_type_id} try: result = conn.fetch(query, query_params) data = dict() for srv_ref, srv_name, desc_, is_tech, srv_pol, is_enbl, supp_tech, bus_tech, srv_teams, srv_insts in result: concealed_key = key_manager.attach_service_type_to_ref_id( key_manager.conceal_reference_key(srv_ref), is_tech) serv_dep = [key_manager.attach_service_type_to_ref_id(key_manager.conceal_reference_key( uuid.UUID(item)), True) for item in supp_tech] if supp_tech is not None else [] serv_dep += [key_manager.attach_service_type_to_ref_id(key_manager.conceal_reference_key( uuid.UUID(item)), False) for item in bus_tech] if bus_tech is not None else [] data[concealed_key] = { var_names.service_ref_id: concealed_key, var_names.service_name: srv_name, var_names.description: desc_, var_names.is_technical: is_tech, var_names.policy_id: srv_pol, var_names.is_enabled: is_enbl, var_names.dependencies: serv_dep, var_names.teams: [[item[var_names.team_name], key_manager.conceal_reference_key(uuid.UUID(item[var_names.team_ref_id]))] for item in srv_teams] if srv_teams is not None else [], var_names.incidents: srv_insts if srv_insts is not None else [] } return data except psycopg2.DatabaseError: raise def get_service_technical_dependencies(conn, timestamp, organization_id, service_ref_id=None, org_instance_id=None): ''' Get all the technical dependencies of a service. :param conn: db connection :param timestamp: timestamp when this request is being made :param organization_id: ID of the organization :param service_ref_id: concealed reference ID of the service to filter by :param org_instance_id: organization instance ID to filter by :return: (dict) -> {dependencies: [{...}, ...], used by: [{...}, ...]} :errors: AssertionError, DatabaseError ''' assert isinstance(timestamp, datetime.datetime) assert isinstance(organization_id, int) assert service_ref_id is not None or org_instance_id is not None query_params = {'timestamp': timestamp, 'org_id': organization_id} serv_filter = '()' if service_ref_id is not None: serv_filter = ''' ( select serviceid from services where service_ref_id = %(srv_ref)s and start_timestamp <= %(timestamp)s and end_timestamp > %(timestamp)s and organization_id = %(org_id)s ) ''' query_params['srv_ref'] = key_manager.unmask_reference_key(service_ref_id) elif org_instance_id is not None: assert isinstance(org_instance_id, int) serv_filter = ''' ( select serviceid from tasks join task_instances using(taskid) where start_timestamp <= %(timestamp)s and end_timestamp > %(timestamp)s and organization_id = %(org_id)s and organization_instanceid = %(org_inst_id)s ) ''' query_params['org_inst_id'] = org_instance_id query = ''' with t1 as ( select serviceid, service_name, service_ref_id from services where start_timestamp <= %(timestamp)s and end_timestamp > %(timestamp)s and organization_id = %(org_id)s ) select cmbt.service_name, cmbt.service_ref_id, cmbt.depends_on, count(inst.instanceid) from ( select supporting_serviceid as rel_serv, t1.service_name, t1.service_ref_id, true as depends_on from service_dependencies as dep join t1 on t1.serviceid = dep.supporting_serviceid where dep.serviceid in {0} and dep.start_timestamp <= %(timestamp)s and dep.end_timestamp > %(timestamp)s union select t1.serviceid as rel_serv, t1.service_name, t1.service_ref_id, false as depends_on from service_dependencies as dep join t1 on t1.serviceid = dep.serviceid where dep.supporting_serviceid in {0} and dep.start_timestamp <= %(timestamp)s and dep.end_timestamp > %(timestamp)s ) as cmbt left join tasks on tasks.serviceid is not null and tasks.serviceid = cmbt.rel_serv and tasks.start_timestamp <= %(timestamp)s and tasks.end_timestamp > %(timestamp)s and tasks.organization_id = %(org_id)s left join task_instances as inst on tasks.taskid = inst.taskid and inst.is_open = true group by cmbt.service_name, cmbt.service_ref_id, cmbt.depends_on; '''.format(serv_filter) try: result = conn.fetch(query, query_params) data = {var_names.dependencies: [], var_names.used_by: []} for name_, ref_, is_dep_, cnt_ in result: item = { var_names.service_name: name_, var_names.service_ref_id: key_manager.conceal_reference_key(ref_), var_names.incidents: cnt_ } if is_dep_: data[var_names.dependencies].append(item) else: data[var_names.used_by].append(item) return data except psycopg2.DatabaseError: raise def add_system_dependency(conn, timestamp, organization_id, user_id, from_service, to_service, check_adv_perm=False, has_comp_perm=False, has_team_perm=False): ''' Add a service dependency. :param conn: db connection :param timestamp: timestamp when this request was made :param organization_id: the organization ID :param user_id: ID of the user who is making the request :param from_service: (list of length 2) -> [concealed reference ID, is technical] :param to_service: (list of length 2) -> [concealed reference ID, is technical] :param check_adv_perm: (boolean) should advanced permissions be checked :param has_comp_perm: (boolean) does the organization have advanced permissions :param has_team_perm: (boolean) does the organization have team permissions :errors: AssertionError, DatabaseError, DependencyError, LookupError, PermissionError, TypeError ''' assert isinstance(timestamp, datetime.datetime) assert isinstance(organization_id, int) assert isinstance(user_id, int) assert isinstance(from_service, list) and len(from_service) == 2 assert isinstance(to_service, list) and len(to_service) == 2 from_srv, from_is_tech = key_manager.unmask_reference_key(from_service[0]), from_service[1] to_srv, to_is_tech = key_manager.unmask_reference_key(to_service[0]), to_service[1] if to_is_tech and not from_is_tech: raise TypeError(errors.err_service_business_dependency) from_node, to_node = get_dependency_nodes_from_ref_ids(conn, timestamp, organization_id, from_service, to_service) if from_is_tech and to_is_tech: dependencies = tech_dependencies_for_internal_use(conn, timestamp, organization_id) if to_node[0] in dependencies: dependencies[to_node[0]][var_names.dependencies].append(from_node[0]) circular_dep = component_validator.service_circular_dependency(from_node[0], from_node[0], dependencies) if circular_dep is not None: raise DependencyFound(errors.err_service_circular_dependency, circular_dep[0]) elif not from_is_tech and not to_is_tech: dependencies = business_dependencies_for_internal_use(conn, timestamp, organization_id) if to_node[0] in dependencies: dependencies[to_node[0]][var_names.dependencies].append(from_node[0]) circular_dep = component_validator.service_circular_dependency(from_node[0], from_node[0], dependencies) if circular_dep is not None: raise DependencyFound(errors.err_service_circular_dependency, circular_dep[0]) adv_perm_type = permissions.COMPONENT_ADVANCED_EDIT_PERMISSION from_comp_type = configs.service_component_type_id if from_is_tech else configs.business_service_component_type_id to_comp_type = configs.service_component_type_id if to_is_tech else configs.business_service_component_type_id query = ''' select add_system_dependency ( %s, %s, %s, %s, %s, %s, %s, %s, %s::smallint, %s, %s::smallint, %s ); ''' query_params = (check_adv_perm, has_comp_perm, has_team_perm, timestamp, constants.end_timestamp, organization_id, user_id, adv_perm_type, from_comp_type, from_node[0], to_comp_type, to_node[0],) try: conn.execute(query, query_params) except psycopg2.IntegrityError as e: if e.pgcode == errorcodes.CHECK_VIOLATION: raise LookupError(errors.err_unknown_resource) elif e.pgcode == errorcodes.RESTRICT_VIOLATION: raise PermissionError(errors.err_user_rights) else: raise except psycopg2.DatabaseError: raise def delete_system_dependency(conn, timestamp, organization_id, user_id, from_service, to_service, check_adv_perm=False, has_comp_perm=False, has_team_perm=False): ''' Remove a service dependency. :param conn: db connection :param timestamp: timestamp when this request was made :param organization_id: the organization ID :param user_id: ID of the user who is making the request :param from_service: (list of length 2) -> [concealed reference ID, is technical] :param to_service: (list of length 2) -> [concealed reference ID, is technical] :param check_adv_perm: (boolean) should advanced permissions be checked :param has_comp_perm: (boolean) does the organization have advanced permissions :param has_team_perm: (boolean) does the organization have team permissions :errors: AssertionError, DatabaseError, PermissionError ''' assert isinstance(timestamp, datetime.datetime) assert isinstance(organization_id, int) assert isinstance(user_id, int) assert isinstance(from_service, list) and len(from_service) == 2 assert isinstance(to_service, list) and len(to_service) == 2 from_srv, from_is_tech = key_manager.unmask_reference_key(from_service[0]), from_service[1] to_srv, to_is_tech = key_manager.unmask_reference_key(to_service[0]), to_service[1] from_node, to_node = get_dependency_nodes_from_ref_ids(conn, timestamp, organization_id, from_service, to_service) from_comp_type = configs.service_component_type_id if from_is_tech else configs.business_service_component_type_id to_comp_type = configs.service_component_type_id if to_is_tech else configs.business_service_component_type_id adv_perm_type = permissions.COMPONENT_ADVANCED_EDIT_PERMISSION query = ''' select remove_system_dependency ( %s, %s, %s, %s, %s, %s, %s, %s::smallint, %s, %s::smallint, %s ); ''' query_params = (check_adv_perm, has_comp_perm, has_team_perm, timestamp, organization_id, user_id, adv_perm_type, from_comp_type, from_node[0], to_comp_type, to_node[0],) try: conn.execute(query, query_params) except psycopg2.IntegrityError as e: if e.pgcode == errorcodes.RESTRICT_VIOLATION: raise PermissionError(errors.err_user_rights) else: raise except psycopg2.DatabaseError: raise def list_system_component_state(conn, timestamp, org_id): ''' Get details of all technical and business service dependencies that will be needed for the service graph. "WARNING": This query returns the actual policy id and does not include the on-call. The policy ref id and the on-call must be added in the view that calls the function. :param conn: db connection :param timestamp: timestamp when this request was made :param org_id: the organization ID :return: (dict of dict) of service dependency related details :errors: AssertionError, DatabaseError ''' assert isinstance(timestamp, datetime.datetime) assert isinstance(org_id, int) check_start = timestamp - datetime.timedelta(days=30) query = ''' with t1 as ( select srv.serviceid, srv.service_name, srv.service_ref_id, srv.description, for_policyid, is_enabled from services as srv where start_timestamp <= %(timestamp)s and end_timestamp > %(timestamp)s and organization_id = %(org_id)s ) , t2 as ( select dep.serviceid, json_agg(t1.service_ref_id) as supp_tech from service_dependencies as dep join t1 on t1.serviceid = dep.supporting_serviceid where dep.start_timestamp <= %(timestamp)s and dep.end_timestamp > %(timestamp)s group by dep.serviceid ) , t3 as ( select component_id as serviceid, json_agg(json_build_object( 'team_ref_id', team_ref_id, 'team_name', team_name )) as service_teams from team_components as tco join teams using (team_id) where tco.start_timestamp <= %(timestamp)s and tco.end_timestamp > %(timestamp)s and tco.component_type_id = %(srv_comp_type_id)s and teams.organization_id = %(org_id)s and teams.start_timestamp <= %(timestamp)s and teams.end_timestamp > %(timestamp)s group by serviceid ) , t4 as ( select tasks.serviceid, json_agg(json_build_object( 'organization_instance_id', organization_instanceid, 'task_title', tasks.title )) as service_insts from task_instances as inst join tasks using(taskid, organization_id) where inst.is_open = true and inst.organization_id = %(org_id)s and tasks.organization_id = %(org_id)s and tasks.start_timestamp <= %(timestamp)s and tasks.end_timestamp > %(timestamp)s and tasks.serviceid is not null group by tasks.serviceid ) , t5 as ( select serviceid, json_object_agg( timezone(org.organization_timezone, timezone('UTC', instance_timestamp))::date, instance_count ) as periodic_health from ( select ti.organization_id, serviceid, instance_timestamp, count(instanceid) as instance_count from task_instances as ti join tasks on ti.taskid = tasks.taskid and ti.organization_id = tasks.organization_id where tasks.organization_id = %(org_id)s and tasks.serviceid in (select serviceid from t1) and instance_timestamp >= %(check_start)s and instance_timestamp <= %(timestamp)s group by ti.organization_id, serviceid, instance_timestamp ) as sub_table join organizations as org using(organization_id) group by serviceid ) , t6 as ( select bus.business_serviceid, bus.service_name, bus.business_service_ref_id, bus.description from business_services as bus where start_timestamp <= %(timestamp)s and end_timestamp > %(timestamp)s and organization_id = %(org_id)s ) , t7 as ( select dep.business_serviceid, json_agg(services.service_ref_id) as supp_tech from business_service_dependencies as dep join services on services.serviceid = dep.supporting_serviceid where dep.start_timestamp <= %(timestamp)s and dep.end_timestamp > %(timestamp)s and is_technical = true and services.start_timestamp <= %(timestamp)s and services.end_timestamp > %(timestamp)s and services.organization_id = %(org_id)s group by dep.business_serviceid ) , t8 as ( select dep.business_serviceid, json_agg(t6.business_service_ref_id) as bus_tech from business_service_dependencies as dep join t6 on t6.business_serviceid = dep.supporting_serviceid where dep.start_timestamp <= %(timestamp)s and dep.end_timestamp > %(timestamp)s and is_technical = false group by dep.business_serviceid ) , t9 as ( select component_id as business_serviceid, json_agg(json_build_object( 'team_ref_id', team_ref_id, 'team_name', team_name )) as bus_teams from team_components as tco join teams using (team_id) where tco.start_timestamp <= %(timestamp)s and tco.end_timestamp > %(timestamp)s and tco.component_type_id = %(bus_comp_type_id)s and teams.organization_id = %(org_id)s and teams.start_timestamp <= %(timestamp)s and teams.end_timestamp > %(timestamp)s group by business_serviceid ) , t10 as ( select iibs.business_serviceid, json_agg(json_build_object( 'organization_instance_id', organization_instanceid, 'task_title', tasks.title )) as bus_insts from task_instances as inst join tasks using(taskid, organization_id) join instance_impacted_business_services as iibs using(instanceid) where inst.is_open = true and inst.organization_id = %(org_id)s and tasks.organization_id = %(org_id)s and tasks.start_timestamp <= %(timestamp)s and tasks.end_timestamp > %(timestamp)s and iibs.start_timestamp <= %(timestamp)s and iibs.end_timestamp > %(timestamp)s group by iibs.business_serviceid ) select t1.service_ref_id, t1.service_name, t1.description, true, t1.for_policyid, t1.is_enabled, t2.supp_tech, null as bus_tech, t3.service_teams, t4.service_insts, t5.periodic_health from t1 left join t2 using(serviceid) left join t3 using(serviceid) left join t4 using(serviceid) left join t5 using(serviceid) union all select t6.business_service_ref_id, t6.service_name, t6.description, false, null, true, t7.supp_tech, t8.bus_tech, t9.bus_teams as service_teams, t10.bus_insts as service_insts, null from t6 left join t7 using(business_serviceid) left join t8 using(business_serviceid) left join t9 using(business_serviceid) left join t10 using(business_serviceid) order by service_name; ''' query_params = {'timestamp': timestamp, 'org_id': org_id, 'srv_comp_type_id': configs.service_component_type_id, 'bus_comp_type_id': configs.business_service_component_type_id, 'check_start': check_start} try: result = conn.fetch(query, query_params) data = dict() for srv_ref, srv_name, desc_, is_tech, srv_pol, is_enbl, supp_tech, bus_tech, srv_teams,\ srv_insts, periodic_health in result: concealed_key = key_manager.conceal_reference_key(srv_ref) supp_dep = [key_manager.conceal_reference_key(uuid.UUID(item)) for item in supp_tech]\ if supp_tech is not None else [] bus_dep = [key_manager.conceal_reference_key(uuid.UUID(item)) for item in bus_tech]\ if bus_tech is not None else [] periodic_health = dict() if periodic_health is None else periodic_health dt_range = [check_start + datetime.timedelta(days=i) for i in range(0, (timestamp - check_start).days + 1)] formatted_ph = [] if is_tech: for dt_ in dt_range: dt_str_ = datetime.datetime.strftime(dt_, constants.date_hyphen_format) count = periodic_health[dt_str_] if dt_str_ in periodic_health else 0 formatted_ph.append({var_names.period: dt_, var_names.count: count}) data[(concealed_key, is_tech)] = { var_names.service_ref_id: concealed_key, var_names.service_name: srv_name, var_names.description: desc_, var_names.is_technical: is_tech, var_names.policy_id: srv_pol, var_names.is_enabled: is_enbl, var_names.services: supp_dep, var_names.business_services: bus_dep, var_names.teams: [[item[var_names.team_name], key_manager.conceal_reference_key(uuid.UUID(item[var_names.team_ref_id]))] for item in srv_teams] if srv_teams is not None else [], var_names.incidents: srv_insts if srv_insts is not None else [], var_names.last_30_days: formatted_ph } new_data = dict() for fi_key in data: new_data[fi_key] = data[fi_key] fi_ref, fi_tech = fi_key srv_dep, srv_use = [], [] bus_dep, bus_use = [], [] for se_key in data: if fi_key != se_key: se_ref, se_tech = se_key se_item = [data[se_key][var_names.service_name], data[se_key][var_names.service_ref_id], len(data[se_key][var_names.incidents])] if fi_tech: if se_tech and se_ref in data[fi_key][var_names.services]: srv_dep.append(se_item) elif se_tech and fi_ref in data[se_key][var_names.services]: srv_use.append(se_item) elif not se_tech and fi_ref in data[se_key][var_names.services]: bus_use.append(se_item) else: if se_tech and se_ref in data[fi_key][var_names.services]: srv_dep.append(se_item) elif not se_tech and se_ref in data[fi_key][var_names.business_services]: bus_dep.append(se_item) elif not se_tech and fi_ref in data[se_key][var_names.business_services]: bus_use.append(se_item) new_data[fi_key][var_names.services] = {var_names.dependencies: srv_dep, var_names.used_by: srv_use} new_data[fi_key][var_names.business_services] = {var_names.dependencies: bus_dep, var_names.used_by: bus_use} new_data_list = helpers.sorted_list_of_dict(list(new_data.values()), var_names.service_name) return new_data_list except psycopg2.DatabaseError: raise