Revize 364a6830

[Merge conflict] - reverted controller version from #8702

src/controllers/certificates_controller.py

@@ -1 @@
-import time
-from sqlite3 import Connection, Error, DatabaseError, IntegrityError, ProgrammingError, OperationalError, \
-    NotSupportedError
-from typing import Dict, List, Set
+import json
+from datetime import datetime
+from itertools import chain
+from json import JSONDecodeError
 
-from src.exceptions.database_exception import DatabaseException
+from flask import request, Response
 from injector import inject
-from src.constants import *
-from src.model.certificate import Certificate
+
+from src.constants import CA_ID, \
+    SSL_ID, SIGNATURE_ID, AUTHENTICATION_ID, \
+    DATETIME_FORMAT, ROOT_CA_ID, INTERMEDIATE_CA_ID, CERTIFICATE_ID  # TODO DATABASE_FILE - not the Controller's
+from src.controllers.return_codes import *
+from src.exceptions.database_exception import DatabaseException
+from src.exceptions.unknown_exception import UnknownException
+from src.model.subject import Subject
+from src.services.certificate_service import CertificateService, RevocationReasonInvalidException, \
+    CertificateStatusInvalidException, CertificateNotFoundException, CertificateAlreadyRevokedException, \
+    CertificateCannotBeSetToValid
+#  responsibility.
+from src.services.cryptography import CryptographyException
+from src.services.key_service import KeyService
 from src.utils.logger import Logger
+from src.utils.util import dict_to_string
+
+EXTENSIONS = "extensions"
+
+TREE_NODE_TYPE_COUNT = 3
+
+FILTERING = "filtering"
+PAGE = "page"
+PER_PAGE = "per_page"
+ISSUER = "issuer"
+US = "usage"
+NOT_AFTER = "notAfter"
+NOT_BEFORE = "notBefore"
+COMMON_NAME = "CN"
+ID = "id"
+CA = "CA"
+USAGE = "usage"
+SUBJECT = "subject"
+VALIDITY_DAYS = "validityDays"
+TYPE = "type"
+ISSUED_BY = "issuedby"
+STATUS = "status"
+REASON = "reason"
+REASON_UNDEFINED = "unspecified"
+NAME = "name"
+PASSWORD = "password"
+
+E_NO_ISSUER_FOUND = {"success": False, "data": "No certificate authority with such unique ID exists."}
+E_NO_CERTIFICATES_FOUND = {"success": False, "data": "No such certificate found."}
+E_NO_CERTIFICATE_ALREADY_REVOKED = {"success": False, "data": "Certificate is already revoked."}
+E_NO_CERT_PRIVATE_KEY_FOUND = {"success": False,
+                               "data": "Internal server error (certificate's private key cannot be found)."}
+E_NOT_JSON_FORMAT = {"success": False, "data": "The request must be JSON-formatted."}
+E_CORRUPTED_DATABASE = {"success": False, "data": "Internal server error (corrupted database)."}
+E_GENERAL_ERROR = {"success": False, "data": "Internal server error (unknown origin)."}
+E_MISSING_PARAMETERS = {"success": False, "data": "Invalid request, missing parameters."}
+E_WRONG_PARAMETERS = {"success": False, "data": "Invalid request, wrong parameters."}
+E_IDENTITY_NAME_NOT_SPECIFIED = {"success": False, "data": "Invalid request, missing identity name."}
+E_IDENTITY_PASSWORD_NOT_SPECIFIED = {"success": False, "data": "Invalid request, missing identity password."}
+
+
+class CertController:
+    USAGE_KEY_MAP = {'CA': CA_ID, 'SSL': SSL_ID, 'digitalSignature': SIGNATURE_ID, 'authentication': AUTHENTICATION_ID}
+    INVERSE_USAGE_KEY_MAP = {k: v for v, k in USAGE_KEY_MAP.items()}
+    FILTERING_TYPE_KEY_MAP = {'root': ROOT_CA_ID, 'inter': INTERMEDIATE_CA_ID, 'end': CERTIFICATE_ID}
+    # INVERSE_FILTERING_TYPE_KEY_MAP = {k: v for v, k in FILTERING_TYPE_KEY_MAP.items()}
+
 
-INTEGRITY_ERROR_MSG = "Database relational integrity corrupted."
-PROGRAMMING_ERROR_MSG = "Exception raised for programming errors (etc. SQL statement)."
-OPERATIONAL_ERROR_MSG = "Exception raised for errors that are related to the database’s operation."
-NOT_SUPPORTED_ERROR_MSG = "Method or database API was used which is not supported by the database"
-DATABASE_ERROR_MSG = "Unknown exception that are related to the database."
-ERROR_MSG = "Unknown exception."
+    @inject
+    def __init__(self, certificate_service: CertificateService, key_service: KeyService):
+        self.certificate_service = certificate_service
+        self.key_service = key_service
 
+    def create_certificate(self):
+        """create new certificate
 
-class CertificateRepository:
+        Create a new certificate based on given information
 
-    @inject
-    def __init__(self, connection: Connection):
-        """
-        Constructor of the CertificateRepository object
+        :param body: Certificate data to be created
+        :type body: dict | bytes
 
-        :param connection: Instance of the Connection object
+        :rtype: CreatedResponse
         """
-        self.connection = connection
-        self.cursor = connection.cursor()
 
-    def create(self, certificate: Certificate):
+        Logger.info(f"\n\t{request.referrer}"
+                    f"\n\t{request.method}   {request.path}   {request.scheme}")
+
+        required_keys = {SUBJECT, USAGE, VALIDITY_DAYS}                             # required fields of the POST req
+
+        if request.is_json:                                                         # accept JSON only
+            body = request.get_json()
+
+            Logger.info(f"\n\tRequest body:"
+                        f"\n{dict_to_string(body)}")
+
+            if not all(k in body for k in required_keys):                           # verify that all keys are present
+                Logger.error(f"Invalid request, missing parameters")
+                return E_MISSING_PARAMETERS, C_BAD_REQUEST
+
+            if not isinstance(body[VALIDITY_DAYS], int):                            # type checking
+                Logger.error(f"Invalid request, wrong parameter '{VALIDITY_DAYS}'.")
+                return E_WRONG_PARAMETERS, C_BAD_REQUEST
+
+            subject = Subject.from_dict(body[SUBJECT])                              # generate Subject from passed dict
+
+            if subject is None:                                                     # if the format is incorrect
+                Logger.error(f"Invalid request, wrong parameter '{SUBJECT}'.")
+                return E_WRONG_PARAMETERS, C_BAD_REQUEST
+
+            usages_dict = {}
+
+            if not isinstance(body[USAGE], dict):                                   # type checking
+                Logger.error(f"Invalid request, wrong parameter '{USAGE}'.")
+                return E_WRONG_PARAMETERS, C_BAD_REQUEST
+
+            for k, v in body[USAGE].items():                                        # for each usage
+                if k not in CertController.USAGE_KEY_MAP:                                 # check that it is a valid usage
+                    Logger.error(f"Invalid request, wrong parameter '{USAGE}'[{k}].")
+                    return E_WRONG_PARAMETERS, C_BAD_REQUEST                        # and throw if it is not
+                usages_dict[CertController.USAGE_KEY_MAP[k]] = v                          # otherwise translate key and set
+
+            key = self.key_service.create_new_key()                                 # TODO pass key
+
+            extensions = ""
+            if EXTENSIONS in body:
+                extensions = body[EXTENSIONS]
+
+            try:
+                if CA not in body or body[CA] is None:                              # if issuer omitted (legal) or none
+                    cert = self.certificate_service.create_root_ca(                 # create a root CA
+                        key,
+                        subject,
+                        usages=usages_dict,                                         # TODO ignoring usages -> discussion
+                        days=body[VALIDITY_DAYS],
+                        extensions=extensions
+                    )
+                else:
+                    issuer = self.certificate_service.get_certificate(body[CA])     # get base issuer info
+
+                    if issuer is None:                                              # if such issuer does not exist
+                        Logger.error(f"No certificate authority with such unique ID exists 'ID = {key.private_key_id}'.")
+                        self.key_service.delete_key(key.private_key_id)             # free
+                        return E_NO_ISSUER_FOUND, C_BAD_REQUEST                     # and throw
+
+                    issuer_key = self.key_service.get_key(issuer.private_key_id)    # get issuer's key, which must exist
+
+                    if issuer_key is None:                                          # if it does not
+                        Logger.error(f"Internal server error (corrupted database).")
+                        self.key_service.delete_key(key.private_key_id)             # free
+                        return E_CORRUPTED_DATABASE, C_INTERNAL_SERVER_ERROR        # and throw
+
+                    f = self.certificate_service.create_ca if CA_ID in usages_dict and usages_dict[CA_ID] else \
+                        self.certificate_service.create_end_cert
+
+                    # noinspection PyArgumentList
+                    cert = f(                                                       # create inter CA or end cert
+                        key,                                                        # according to whether 'CA' is among
+                        subject,                                                    # the usages' fields
+                        issuer,
+                        issuer_key,
+                        usages=usages_dict,
+                        days=body[VALIDITY_DAYS],
+                        extensions=extensions
+                    )
+            except CryptographyException as e:
+                return E_WRONG_PARAMETERS, C_BAD_REQUEST
+
+            if cert is not None:
+                return {"success": True,
+                        "data": cert.certificate_id}, C_CREATED_SUCCESSFULLY
+            else:                                                                   # if this fails, then
+                Logger.error(f"Internal error: The certificate could not have been created.")
+                self.key_service.delete_key(key.private_key_id)                          # free
+                return {"success": False,                                           # and wonder what the cause is,
+                        "data": "Internal error: The certificate could not have been created."}, C_BAD_REQUEST
+                                                                                    # as obj/None carries only one bit
+                                                                                    # of error information
+        else:
+            Logger.error(f"The request must be JSON-formatted.")
+            return E_NOT_JSON_FORMAT, C_BAD_REQUEST                                 # throw in case of non-JSON format
+
+    def get_certificate_by_id(self, id):
+        """get certificate by ID
+
+        Get certificate in PEM format by ID
+
+        :param id: ID of a certificate to be queried
+        :type id: dict | bytes
+
+        :rtype: PemResponse
         """
-        Creates a certificate.
-        For root certificate (CA) the parent certificate id is modified to the same id (id == parent_id).
 
-        :param certificate: Instance of the Certificate object
+        Logger.info(f"\n\t{request.referrer}"
+                    f"\n\t{request.method}   {request.path}   {request.scheme}"
+                    f"\n\tCertificate ID = {id}")
+        try:
+            v = int(id)
+        except ValueError:
+            Logger.error(f"Invalid request, wrong parameters 'id'[{id}].")
+            return E_WRONG_PARAMETERS, C_BAD_REQUEST
+
+        cert = self.certificate_service.get_certificate(v)
 
-        :return: the result of whether the creation was successful
-        """
+        if cert is None:
+            Logger.error(f"No such certificate found 'ID = {v}'.")
+            return E_NO_CERTIFICATES_FOUND, C_NO_DATA
+        else:
+            return {"success": True, "data": cert.pem_data}, C_SUCCESS
 
-        Logger.debug("Function launched.")
+    def get_certificate_details_by_id(self, id):
+        """get certificate's details by ID
 
-        try:
-            sql = (f"INSERT INTO {TAB_CERTIFICATES} "
-                   f"({COL_VALID_FROM},"
-                   f"{COL_VALID_TO},"
-                   f"{COL_PEM_DATA},"
-                   f"{COL_COMMON_NAME},"
-                   f"{COL_COUNTRY_CODE},"
-                   f"{COL_LOCALITY},"
-                   f"{COL_PROVINCE},"
-                   f"{COL_ORGANIZATION},"
-                   f"{COL_ORGANIZATIONAL_UNIT},"
-                   f"{COL_EMAIL_ADDRESS},"
-                   f"{COL_TYPE_ID},"
-                   f"{COL_PARENT_ID},"
-                   f"{COL_PRIVATE_KEY_ID}) "
-                   f"VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?)")
-            values = [certificate.valid_from,
-                      certificate.valid_to,
-                      certificate.pem_data,
-                      certificate.common_name,
-                      certificate.country_code,
-                      certificate.locality,
-                      certificate.province,
-                      certificate.organization,
-                      certificate.organizational_unit,
-                      certificate.email_address,
-                      certificate.type_id,
-                      certificate.parent_id,
-                      certificate.private_key_id]
-            self.cursor.execute(sql, values)
-            self.connection.commit()
-
-            last_id: int = self.cursor.lastrowid
-
-            if certificate.type_id == ROOT_CA_ID:
-                certificate.parent_id = last_id
-                self.update(last_id, certificate)
-            else:
-                for usage_id, usage_value in certificate.usages.items():
-                    if usage_value:
-                        sql = (f"INSERT INTO {TAB_CERTIFICATE_USAGES} "
-                               f"({COL_CERTIFICATE_ID},"
-                               f"{COL_USAGE_TYPE_ID}) "
-                               f"VALUES (?,?)")
-                        values = [last_id, usage_id]
-                        self.cursor.execute(sql, values)
-                        self.connection.commit()
-        except IntegrityError:
-            Logger.error(INTEGRITY_ERROR_MSG)
-            raise DatabaseException(INTEGRITY_ERROR_MSG)
-        except ProgrammingError:
-            Logger.error(PROGRAMMING_ERROR_MSG)
-            raise DatabaseException(PROGRAMMING_ERROR_MSG)
-        except OperationalError:
-            Logger.error(OPERATIONAL_ERROR_MSG)
-            raise DatabaseException(OPERATIONAL_ERROR_MSG)
-        except NotSupportedError:
-            Logger.error(NOT_SUPPORTED_ERROR_MSG)
-            raise DatabaseException(NOT_SUPPORTED_ERROR_MSG)
-        except DatabaseError:
-            Logger.error(DATABASE_ERROR_MSG)
-            raise DatabaseException(DATABASE_ERROR_MSG)
-        except Error:
-            Logger.error(ERROR_MSG)
-            raise DatabaseException(ERROR_MSG)
-
-        return last_id
-
-    def read(self, certificate_id: int):
-        """
-        Reads (selects) a certificate.
+        Get certificate details by ID
 
-        :param certificate_id: ID of specific certificate
+        :param id: ID of a certificate whose details are to be queried
+        :type id: dict | bytes
 
-        :return: instance of the Certificate object
+        :rtype: CertificateResponse
         """
 
-        Logger.debug("Function launched.")
+        Logger.info(f"\n\t{request.referrer}"
+                    f"\n\t{request.method}   {request.path}   {request.scheme}"
+                    f"\n\tCertificate ID = {id}")
 
         try:
-            sql = (f"SELECT * FROM {TAB_CERTIFICATES} "
-                   f"WHERE {COL_ID} = ? AND ({COL_DELETION_DATE} IS NULL OR {COL_DELETION_DATE} = '')")
-            values = [certificate_id]
-            self.cursor.execute(sql, values)
-            certificate_row = self.cursor.fetchone()
-
-            if certificate_row is None:
-                return None
-
-            sql = (f"SELECT * FROM {TAB_CERTIFICATE_USAGES} "
-                   f"WHERE {COL_CERTIFICATE_ID} = ?")
-            self.cursor.execute(sql, values)
-            usage_rows = self.cursor.fetchall()
-
-            usage_dict: Dict[int, bool] = {}
-            for usage_row in usage_rows:
-                usage_dict[usage_row[2]] = True
-
-            certificate: Certificate = Certificate(certificate_row[0],      # ID
-                                                   certificate_row[1],      # valid from
-                                                   certificate_row[2],      # valid to
-                                                   certificate_row[3],      # pem data
-                                                   certificate_row[14],     # type ID
-                                                   certificate_row[15],     # parent ID
-                                                   certificate_row[16],     # private key ID
-                                                   usage_dict,
-                                                   certificate_row[4],      # common name
-                                                   certificate_row[5],      # country code
-                                                   certificate_row[6],      # locality
-                                                   certificate_row[7],      # province
-                                                   certificate_row[8],      # organization
-                                                   certificate_row[9],      # organizational unit
-                                                   certificate_row[10],     # email address
-                                                   certificate_row[11],     # revocation date
-                                                   certificate_row[12])     # revocation reason
-
-        except IntegrityError:
-            Logger.error(INTEGRITY_ERROR_MSG)
-            raise DatabaseException(INTEGRITY_ERROR_MSG)
-        except ProgrammingError:
-            Logger.error(PROGRAMMING_ERROR_MSG)
-            raise DatabaseException(PROGRAMMING_ERROR_MSG)
-        except OperationalError:
-            Logger.error(OPERATIONAL_ERROR_MSG)
-            raise DatabaseException(OPERATIONAL_ERROR_MSG)
-        except NotSupportedError:
-            Logger.error(NOT_SUPPORTED_ERROR_MSG)
-            raise DatabaseException(NOT_SUPPORTED_ERROR_MSG)
-        except DatabaseError:
-            Logger.error(DATABASE_ERROR_MSG)
-            raise DatabaseException(DATABASE_ERROR_MSG)
-        except Error:
-            Logger.error(ERROR_MSG)
-            raise DatabaseException(ERROR_MSG)
-
-        return certificate
-
-    def read_all(self, filter_type: int = None):
-        """
-        Reads (selects) all certificates (with type).
+            v = int(id)
+        except ValueError:
+            Logger.error(f"Invalid request, wrong parameters 'id'[{id}].")
+            return E_WRONG_PARAMETERS, C_BAD_REQUEST
 
-        :param filter_type: ID of certificate type from CertificateTypes table
+        cert = self.certificate_service.get_certificate(v)
 
-        :return: list of certificates
-        """
+        if cert is None:
+            Logger.error(f"No such certificate found 'ID = {v}'.")
+            return E_NO_CERTIFICATES_FOUND, C_NOT_FOUND
 
-        Logger.debug("Function launched.")
+        data = self.cert_to_dict_full(cert)
+        if data is None:
+            return E_CORRUPTED_DATABASE, C_INTERNAL_SERVER_ERROR
 
         try:
-            sql_extension = ""
-            values = []
-            if filter_type is not None:
-                sql_extension = (f" AND {COL_TYPE_ID} = ("
-                                 f"SELECT {COL_ID} FROM {TAB_CERTIFICATE_TYPES} WHERE {COL_ID} = ?)")
-                values = [filter_type]
-
-            sql = (f"SELECT * FROM {TAB_CERTIFICATES} "
-                   f"WHERE ({COL_DELETION_DATE} IS NULL OR {COL_DELETION_DATE} = ''){sql_extension}")
-            self.cursor.execute(sql, values)
-            certificate_rows = self.cursor.fetchall()
-
-            certificates: List[Certificate] = []
-            for certificate_row in certificate_rows:
-                sql = (f"SELECT * FROM {TAB_CERTIFICATE_USAGES} "
-                       f"WHERE {COL_CERTIFICATE_ID} = ?")
-                values = [certificate_row[0]]
-                self.cursor.execute(sql, values)
-                usage_rows = self.cursor.fetchall()
-
-                usage_dict: Dict[int, bool] = {}
-                for usage_row in usage_rows:
-                    usage_dict[usage_row[2]] = True
-
-                certificates.append(Certificate(certificate_row[0],      # ID
-                                                certificate_row[1],      # valid from
-                                                certificate_row[2],      # valid to
-                                                certificate_row[3],      # pem data
-                                                certificate_row[14],     # type ID
-                                                certificate_row[15],     # parent ID
-                                                certificate_row[16],     # private key ID
-                                                usage_dict,
-                                                certificate_row[4],      # common name
-                                                certificate_row[5],      # country code
-                                                certificate_row[6],      # locality
-                                                certificate_row[7],      # province
-                                                certificate_row[8],      # organization
-                                                certificate_row[9],      # organizational unit
-                                                certificate_row[10],     # email address
-                                                certificate_row[11],     # revocation date
-                                                certificate_row[12]))    # revocation reason
-        except IntegrityError:
-            Logger.error(INTEGRITY_ERROR_MSG)
-            raise DatabaseException(INTEGRITY_ERROR_MSG)
-        except ProgrammingError:
-            Logger.error(PROGRAMMING_ERROR_MSG)
-            raise DatabaseException(PROGRAMMING_ERROR_MSG)
-        except OperationalError:
-            Logger.error(OPERATIONAL_ERROR_MSG)
-            raise DatabaseException(OPERATIONAL_ERROR_MSG)
-        except NotSupportedError:
-            Logger.error(NOT_SUPPORTED_ERROR_MSG)
-            raise DatabaseException(NOT_SUPPORTED_ERROR_MSG)
-        except DatabaseError:
-            Logger.error(DATABASE_ERROR_MSG)
-            raise DatabaseException(DATABASE_ERROR_MSG)
-        except Error:
-            Logger.error(ERROR_MSG)
-            raise DatabaseException(ERROR_MSG)
-
-        return certificates
-
-    def read_all_filter(self, target_types: Set[int], target_usages: Set[int], target_cn_substring: str, page: int,
-                        per_page: int):
-        """
-        Reads (selects) all certificates according to a specific filtering and pagination options.
-        :param target_types: certificate types (filter)
-        :param target_usages: certificate usages (filter)
-        :param target_cn_substring: certificate CN substring (filter)
-        :param page: target page
-        :param per_page: target page size
-        :return: list of certificates
-        """
+            state = self.certificate_service.get_certificate_state(v)
+            data["status"] = state
+        except CertificateNotFoundException:
+            Logger.error(f"No such certificate found 'ID = {id}'.")
 
-        Logger.debug("Function launched.")
+        return {"success": True, "data": data}, C_SUCCESS
 
-        try:
-            values = []
-            values += list(target_types)
-            values += list(target_usages)
-
-            sql = (
-                f"SELECT a.{COL_ID}, {COL_COMMON_NAME}, {COL_VALID_FROM}, {COL_VALID_TO}, {COL_PEM_DATA}, "
-                f"{COL_REVOCATION_DATE}, {COL_REVOCATION_REASON}, {COL_PRIVATE_KEY_ID}, {COL_TYPE_ID}, {COL_PARENT_ID} "
-                f"FROM {TAB_CERTIFICATES} a "
-                f"WHERE (a.{COL_DELETION_DATE} IS NULL OR a.{COL_DELETION_DATE} = '') "
-                f"AND a.{COL_TYPE_ID} IN ({','.join('?' * len(target_types))}) "
-                f"AND (SELECT COUNT(*) FROM {TAB_CERTIFICATE_USAGES} "
-                f"WHERE {COL_CERTIFICATE_ID} = a.{COL_ID} "
-                f"AND {COL_USAGE_TYPE_ID} IN ({','.join('?' * len(target_usages))}) "
-                f") > 0 "
-            )
+    def get_certificate_list(self):
+        """get list of certificates
+
+        Lists certificates based on provided filtering options
 
-            if target_cn_substring is not None:
-                sql += f" AND a.{COL_COMMON_NAME} LIKE '%' || ? || '%'"
-
-                values += [target_cn_substring]
-
-            if page is not None and per_page is not None:
-                sql += f" LIMIT {per_page} OFFSET {page * per_page}"
-
-            self.cursor.execute(sql, values)
-            certificate_rows = self.cursor.fetchall()
-
-            certificates: List[Certificate] = []
-            for certificate_row in certificate_rows:
-                sql = (f"SELECT {COL_USAGE_TYPE_ID} FROM {TAB_CERTIFICATE_USAGES} "
-                       f"WHERE {COL_CERTIFICATE_ID} = ?")
-                types = [certificate_row[0]]
-                self.cursor.execute(sql, types)
-                usage_rows = self.cursor.fetchall()
-
-                usage_dict: Dict[int, bool] = {}
-                for usage_row in usage_rows:
-                    usage_dict[usage_row[0]] = True
-
-                certificates.append(Certificate(certificate_row[0],
-                                                certificate_row[1],
-                                                certificate_row[2],
-                                                certificate_row[3],
-                                                certificate_row[4],
-                                                certificate_row[7],
-                                                certificate_row[8],
-                                                certificate_row[9],
-                                                usage_dict,
-                                                certificate_row[5],
-                                                certificate_row[6]))
-        except IntegrityError:
-            Logger.error(INTEGRITY_ERROR_MSG)
-            raise DatabaseException(INTEGRITY_ERROR_MSG)
-        except ProgrammingError:
-            Logger.error(PROGRAMMING_ERROR_MSG)
-            raise DatabaseException(PROGRAMMING_ERROR_MSG)
-        except OperationalError:
-            Logger.error(OPERATIONAL_ERROR_MSG)
-            raise DatabaseException(OPERATIONAL_ERROR_MSG)
-        except NotSupportedError:
-            Logger.error(NOT_SUPPORTED_ERROR_MSG)
-            raise DatabaseException(NOT_SUPPORTED_ERROR_MSG)
-        except DatabaseError:
-            Logger.error(DATABASE_ERROR_MSG)
-            raise DatabaseException(DATABASE_ERROR_MSG)
-        except Error:
-            Logger.error(ERROR_MSG)
-            raise DatabaseException(ERROR_MSG)
-
-        return certificates
-
-    def update(self, certificate_id: int, certificate: Certificate):
+        :param filtering: Filter certificate type to be queried
+        :type filtering: dict | bytes
+
+        :rtype: CertificateListResponse
         """
-        Updates a certificate.
-        If the parameter of certificate (Certificate object) is not to be changed,
-        the same value must be specified.
 
-        :param certificate_id: ID of specific certificate
-        :param certificate: Instance of the Certificate object
+        Logger.info(f"\n\t{request.referrer}"
+                    f"\n\t{request.method}   {request.path}   {request.scheme}")
+
+
+        # the filtering parameter can be read as URL argument or as a request body
+        if request.is_json:
+            data = request.get_json()
+        else:
+            data = {}
+
+        if FILTERING in request.args.keys():
+            try:
+                data[FILTERING] = json.loads(request.args[FILTERING])
+            except JSONDecodeError:
+                Logger.error(f"The request must be JSON-formatted.")
+                return E_NOT_JSON_FORMAT, C_BAD_REQUEST
+
+        if PAGE in request.args.keys():
+            try:
+                data[PAGE] = json.loads(request.args[PAGE])
+            except JSONDecodeError:
+                Logger.error(f"The request must be JSON-formatted.")
+                return E_NOT_JSON_FORMAT, C_BAD_REQUEST
+
+        if PER_PAGE in request.args.keys():
+            try:
+                data[PER_PAGE] = json.loads(request.args[PER_PAGE])
+            except JSONDecodeError:
+                Logger.error(f"The request must be JSON-formatted.")
+                return E_NOT_JSON_FORMAT, C_BAD_REQUEST
+
+        Logger.info(f"\n\tRequest body:"
+                    f"\n{dict_to_string(data)}")
+
+        target_types = {ROOT_CA_ID, INTERMEDIATE_CA_ID, CERTIFICATE_ID}
+        target_usages = {v for v in CertController.INVERSE_USAGE_KEY_MAP.keys()}
+        target_cn_substring = None
+        issuer_id = -1
+
+        unfiltered = True
+
+        if PER_PAGE in data:
+            unfiltered = False
+            page = data.get(PAGE, 0)
+            per_page = data[PER_PAGE]
+        else:
+            page = None
+            per_page = None
+
+        if FILTERING in data:                                                   # if the 'filtering' field exists
+            unfiltered = False
+            if isinstance(data[FILTERING], dict):                               # and it is also a 'dict'
+
+                # noinspection DuplicatedCode
+                if TYPE in data[FILTERING]:                                     # containing 'type'
+                    if isinstance(data[FILTERING][TYPE], list):                 # which is a 'list',
+                                                                                # map every field to id
+                        try:
+                            target_types = {CertController.FILTERING_TYPE_KEY_MAP[v] for v in data[FILTERING][TYPE]}
+                        except KeyError as e:
+                            Logger.error(f"Invalid request, wrong parameters '{FILTERING}.{TYPE}' - '{e}'.")
+                            return E_WRONG_PARAMETERS, C_BAD_REQUEST
+                    else:
+                        Logger.error(f"Invalid request, wrong parameters '{FILTERING}.{TYPE}'.")
+                        return E_WRONG_PARAMETERS, C_BAD_REQUEST
+
+                # noinspection DuplicatedCode
+                if USAGE in data[FILTERING]:                                    # containing 'usage'
+                    if isinstance(data[FILTERING][USAGE], list):                # which is a 'list',
+                                                                                # map every field to id
+                        try:
+                            target_usages = {CertController.USAGE_KEY_MAP[v] for v in data[FILTERING][USAGE]}
+                        except KeyError as e:
+                            Logger.error(f"Invalid request, wrong parameters '{FILTERING}.{USAGE}' - '{e}'.")
+                            return E_WRONG_PARAMETERS, C_BAD_REQUEST
+                    else:
+                        Logger.error(f"Invalid request, wrong parameters '{FILTERING}.{USAGE}'.")
+                        return E_WRONG_PARAMETERS, C_BAD_REQUEST
+
+                if COMMON_NAME in data[FILTERING]:                              # containing 'CN'
+                    if isinstance(data[FILTERING][COMMON_NAME], str):           # which is a 'str'
+                        target_cn_substring = data[FILTERING][COMMON_NAME]
+                    else:
+                        Logger.error(f"Invalid request, wrong parameters '{FILTERING}.{COMMON_NAME}'.")
+                        return E_WRONG_PARAMETERS, C_BAD_REQUEST
+
+                if ISSUED_BY in data[FILTERING]:                                # containing 'issuedby'
+                    if isinstance(data[FILTERING][ISSUED_BY], int):             # which is an 'int'
+                        issuer_id = data[FILTERING][ISSUED_BY]                  # then get its children only
+
+            else:
+                Logger.error(f"Invalid request, wrong parameters '{FILTERING}'.")
+                return E_WRONG_PARAMETERS, C_BAD_REQUEST
+
+        if unfiltered:                                                      # if not filtering
+            certs = self.certificate_service.get_certificates()
+        elif issuer_id >= 0:                                                # if filtering by an issuer
+            try:
+                                                                            # get his children, filtered
+                certs = self.certificate_service.get_certificates_issued_by_filter(
+                    issuer_id=issuer_id,
+                    target_types=target_types,
+                    target_usages=target_usages,
+                    target_cn_substring=target_cn_substring,
+                    page=page,
+                    per_page=per_page
+                )
+            except CertificateNotFoundException:                            # if id does not exist
+                Logger.error(f"No such certificate found 'ID = {issuer_id}'.")
+                return E_NO_CERTIFICATES_FOUND, C_NOT_FOUND                 # throw
+        else:
+            certs = self.certificate_service.get_certificates_filter(
+                target_types=target_types,
+                target_usages=target_usages,
+                target_cn_substring=target_cn_substring,
+                page=page,
+                per_page=per_page
+            )
 
-        :return: the result of whether the updation was successful
+        if certs is None:
+            Logger.error(f"Internal server error (unknown origin).")
+            return E_GENERAL_ERROR, C_INTERNAL_SERVER_ERROR
+        elif len(certs) == 0:
+            # TODO check log level
+            Logger.warning(f"No such certificate found (empty list).")
+            return {"success": True, "data": []}, C_SUCCESS
+        else:
+            ret = []
+            for c in certs:
+                data = self.cert_to_dict_partial(c)
+                if data is None:
+                    Logger.error(f"Internal server error (corrupted database).")
+                    return E_CORRUPTED_DATABASE, C_INTERNAL_SERVER_ERROR
+                ret.append(
+                    data
+                )
+            return {"success": True, "data": ret}, C_SUCCESS
+
+    def get_certificate_root_by_id(self, id):
+        """get certificate's root of trust chain by ID
+
+        Get certificate's root of trust chain in PEM format by ID
+
+        :param id: ID of a child certificate whose root is to be queried
+        :type id: dict | bytes
+
+        :rtype: PemResponse
         """
 
-        Logger.debug("Function launched.")
+        Logger.info(f"\n\t{request.referrer}"
+                    f"\n\t{request.method}   {request.path}   {request.scheme}"
+                    f"\n\tCertificate ID = {id}")
 
         try:
-            sql = (f"UPDATE {TAB_CERTIFICATES} "
-                   f"SET {COL_VALID_FROM} = ?, "
-                   f"{COL_VALID_TO} = ?, "
-                   f"{COL_PEM_DATA} = ?, "
-                   f"{COL_COMMON_NAME} = ?, "
-                   f"{COL_COUNTRY_CODE} = ?, "
-                   f"{COL_LOCALITY} = ?, "
-                   f"{COL_PROVINCE} = ?, "
-                   f"{COL_ORGANIZATION} = ?, "
-                   f"{COL_ORGANIZATIONAL_UNIT} = ?, "
-                   f"{COL_EMAIL_ADDRESS} = ?, "
-                   f"{COL_TYPE_ID} = ?, "
-                   f"{COL_PARENT_ID} = ?, "
-                   f"{COL_PRIVATE_KEY_ID} = ? "
-                   f"WHERE {COL_ID} = ?")
-            values = [certificate.valid_from,
-                      certificate.valid_to,
-                      certificate.pem_data,
-                      certificate.common_name,
-                      certificate.country_code,
-                      certificate.locality,
-                      certificate.province,
-                      certificate.organization,
-                      certificate.organizational_unit,
-                      certificate.email_address,
-                      certificate.type_id,
-                      certificate.parent_id,
-                      certificate.private_key_id,
-                      certificate_id]
-
-            self.cursor.execute(sql, values)
-            self.connection.commit()
-
-            sql = (f"DELETE FROM {TAB_CERTIFICATE_USAGES} "
-                   f"WHERE {COL_CERTIFICATE_ID} = ?")
-            values = [certificate_id]
-            self.cursor.execute(sql, values)
-            self.connection.commit()
-
-            check_updated = self.cursor.rowcount > 0
-
-            # iterate over usage pairs
-            for usage_id, usage_value in certificate.usages.items():
-                if usage_value:
-                    sql = (f"INSERT INTO {TAB_CERTIFICATE_USAGES} "
-                           f"({COL_CERTIFICATE_ID},"
-                           f"{COL_USAGE_TYPE_ID}) "
-                           f"VALUES (?,?)")
-                    values = [certificate_id, usage_id]
-                    self.cursor.execute(sql, values)
-                    self.connection.commit()
-        except IntegrityError:
-            Logger.error(INTEGRITY_ERROR_MSG)
-            raise DatabaseException(INTEGRITY_ERROR_MSG)
-        except ProgrammingError:
-            Logger.error(PROGRAMMING_ERROR_MSG)
-            raise DatabaseException(PROGRAMMING_ERROR_MSG)
-        except OperationalError:
-            Logger.error(OPERATIONAL_ERROR_MSG)
-            raise DatabaseException(OPERATIONAL_ERROR_MSG)
-        except NotSupportedError:
-            Logger.error(NOT_SUPPORTED_ERROR_MSG)
-            raise DatabaseException(NOT_SUPPORTED_ERROR_MSG)
-        except DatabaseError:
-            Logger.error(DATABASE_ERROR_MSG)
-            raise DatabaseException(DATABASE_ERROR_MSG)
-        except Error:
-            Logger.error(ERROR_MSG)
-            raise DatabaseException(ERROR_MSG)
-
-        return check_updated
-
-    def delete(self, certificate_id: int):
-        """
-        Deletes a certificate
+            v = int(id)
+        except ValueError:
+            Logger.error(f"Invalid request, wrong parameters 'id'[{id}].")
+            return E_WRONG_PARAMETERS, C_BAD_REQUEST
 
-        :param certificate_id: ID of specific certificate
+        cert = self.certificate_service.get_certificate(v)
 
-        :return: the result of whether the deletion was successful
-        """
+        if cert is None:
+            Logger.error(f"No such certificate found 'ID = {v}'.")
+            return E_NO_CERTIFICATES_FOUND, C_NO_DATA
 
-        Logger.debug("Function launched.")
+        trust_chain = self.certificate_service.get_chain_of_trust(cert.parent_id, exclude_root=False)
+        if trust_chain is None or len(trust_chain) == 0:
+            Logger.error(f"No such certificate found (empty list).")
+            return E_NO_CERTIFICATES_FOUND, C_NO_DATA
 
-        try:
-            sql = (f"UPDATE {TAB_CERTIFICATES} "
-                   f"SET {COL_DELETION_DATE} = ? "
-                   f"WHERE {COL_ID} = ? AND ({COL_DELETION_DATE} IS NULL OR {COL_DELETION_DATE} = '')")
-            values = [int(time.time()),
-                      certificate_id]
-            self.cursor.execute(sql, values)
-            self.connection.commit()
-        except IntegrityError:
-            Logger.error(INTEGRITY_ERROR_MSG)
-            raise DatabaseException(INTEGRITY_ERROR_MSG)
-        except ProgrammingError:
-            Logger.error(PROGRAMMING_ERROR_MSG)
-            raise DatabaseException(PROGRAMMING_ERROR_MSG)
-        except OperationalError:
-            Logger.error(OPERATIONAL_ERROR_MSG)
-            raise DatabaseException(OPERATIONAL_ERROR_MSG)
-        except NotSupportedError:
-            Logger.error(NOT_SUPPORTED_ERROR_MSG)
-            raise DatabaseException(NOT_SUPPORTED_ERROR_MSG)
-        except DatabaseError:
-            Logger.error(DATABASE_ERROR_MSG)
-            raise DatabaseException(DATABASE_ERROR_MSG)
-        except Error:
-            Logger.error(ERROR_MSG)
-            raise DatabaseException(ERROR_MSG)
-
-        return self.cursor.rowcount > 0
-
-    def set_certificate_revoked(
-            self, certificate_id: int, revocation_date: str, revocation_reason: str = REV_REASON_UNSPECIFIED):
-        """
-        Revoke a certificate
+        return {"success": True, "data": trust_chain[-1].pem_data}, C_SUCCESS
 
-        :param certificate_id: ID of specific certificate
-        :param revocation_date: Date, when the certificate is revoked
-        :param revocation_reason: Reason of the revocation
+    def get_certificate_trust_chain_by_id(self, id):
+        """get certificate's trust chain by ID (including root certificate)
 
-        :return:
-            the result of whether the revocation was successful OR
-            sqlite3.Error if an exception is thrown
+        Get certificate trust chain in PEM format by ID
+
+        :param id: ID of a child certificate whose chain is to be queried
+        :type id: dict | bytes
+
+        :rtype: PemResponse
         """
 
-        Logger.debug("Function launched.")
+        Logger.info(f"\n\t{request.referrer}"
+                    f"\n\t{request.method}   {request.path}   {request.scheme}"
+                    f"\n\tCertificate ID = {id}")
 
         try:
-            if revocation_date != "" and revocation_reason == "":
-                revocation_reason = REV_REASON_UNSPECIFIED
-            elif revocation_date == "":
-                return False
-
-            sql = (f"UPDATE {TAB_CERTIFICATES} "
-                   f"SET {COL_REVOCATION_DATE} = ?, "
-                   f"{COL_REVOCATION_REASON} = ? "
-                   f"WHERE {COL_ID} = ? AND ({COL_REVOCATION_DATE} IS NULL OR {COL_REVOCATION_DATE} = '')")
-            values = [revocation_date,
-                      revocation_reason,
-                      certificate_id]
-            self.cursor.execute(sql, values)
-            self.connection.commit()
-        except IntegrityError:
-            Logger.error(INTEGRITY_ERROR_MSG)
-            raise DatabaseException(INTEGRITY_ERROR_MSG)
-        except ProgrammingError:
-            Logger.error(PROGRAMMING_ERROR_MSG)
-            raise DatabaseException(PROGRAMMING_ERROR_MSG)
-        except OperationalError:
-            Logger.error(OPERATIONAL_ERROR_MSG)
-            raise DatabaseException(OPERATIONAL_ERROR_MSG)
-        except NotSupportedError:
-            Logger.error(NOT_SUPPORTED_ERROR_MSG)
-            raise DatabaseException(NOT_SUPPORTED_ERROR_MSG)
-        except DatabaseError:
-            Logger.error(DATABASE_ERROR_MSG)
-            raise DatabaseException(DATABASE_ERROR_MSG)
-        except Error:
-            Logger.error(ERROR_MSG)
-            raise DatabaseException(ERROR_MSG)
-
-        return self.cursor.rowcount > 0
-
-    def clear_certificate_revocation(self, certificate_id: int):
-        """
-        Clear revocation of a certificate
+            v = int(id)
+        except ValueError:
+            Logger.error(f"Invalid request, wrong parameters 'id'[{id}].")
+            return E_WRONG_PARAMETERS, C_BAD_REQUEST
 
-        :param certificate_id: ID of specific certificate
+        cert = self.certificate_service.get_certificate(v)
 
-        :return:
-            the result of whether the clear revocation was successful OR
-            sqlite3.Error if an exception is thrown
-        """
+        if cert is None:
+            Logger.error(f"No such certificate found 'ID = {v}'.")
+            return E_NO_CERTIFICATES_FOUND, C_NO_DATA
 
-        Logger.debug("Function launched.")
+        if cert.parent_id is None:
+            Logger.error(f"Parent ID is empty in certificate 'ID = {v}'.")
+            return E_NO_CERTIFICATES_FOUND, C_NO_DATA
 
-        try:
-            sql = (f"UPDATE {TAB_CERTIFICATES} "
-                   f"SET {COL_REVOCATION_DATE} = NULL, "
-                   f"{COL_REVOCATION_REASON} = NULL "
-                   f"WHERE {COL_ID} = ?")
-            values = [certificate_id]
-            self.cursor.execute(sql, values)
-            self.connection.commit()
-        except IntegrityError:
-            Logger.error(INTEGRITY_ERROR_MSG)
-            raise DatabaseException(INTEGRITY_ERROR_MSG)
-        except ProgrammingError:
-            Logger.error(PROGRAMMING_ERROR_MSG)
-            raise DatabaseException(PROGRAMMING_ERROR_MSG)
-        except OperationalError:
-            Logger.error(OPERATIONAL_ERROR_MSG)
-            raise DatabaseException(OPERATIONAL_ERROR_MSG)
-        except NotSupportedError:
-            Logger.error(NOT_SUPPORTED_ERROR_MSG)
-            raise DatabaseException(NOT_SUPPORTED_ERROR_MSG)
-        except DatabaseError:
-            Logger.error(DATABASE_ERROR_MSG)
-            raise DatabaseException(DATABASE_ERROR_MSG)
-        except Error:
-            Logger.error(ERROR_MSG)
-            raise DatabaseException(ERROR_MSG)
-
-        return self.cursor.rowcount > 0
-
-    def get_all_revoked_by(self, certificate_id: int):
+        trust_chain = self.certificate_service.get_chain_of_trust(cert.parent_id, exclude_root=False)
+
+        ret = []
+        for intermediate in trust_chain:
+            ret.append(intermediate.pem_data)
+
+        return {"success": True, "data": "".join(ret)}, C_SUCCESS
+
+    def set_certificate_status(self, id):
+        """
+        Revoke a certificate given by ID
+            - revocation request may contain revocation reason
+            - revocation reason is verified based on the possible predefined values
+            - if revocation reason is not specified 'undefined' value is used
+        :param id: Identifier of the certificate to be revoked
+        :type id: int
+
+        :rtype: SuccessResponse | ErrorResponse (see OpenAPI definition)
         """
-        Get list of the revoked certificates that are direct descendants of the certificate with the ID
 
-        :param certificate_id: ID of specific certificate
+        Logger.info(f"\n\t{request.referrer}"
+                    f"\n\t{request.method}   {request.path}   {request.scheme}"
+                    f"\n\tCertificate ID = {id}")
+
+        required_keys = {STATUS}  # required keys
+
+        # check if the request contains a JSON body
+        if request.is_json:
+            request_body = request.get_json()
+
+            Logger.info(f"\n\tRequest body:"
+                        f"\n{dict_to_string(request_body)}")
+
+            # try to parse certificate identifier -> if it is not int return error 400
+            try:
+                identifier = int(id)
+            except ValueError:
+                Logger.error(f"Invalid request, wrong parameters 'id'[{id}].")
+                return E_WRONG_PARAMETERS, C_BAD_REQUEST
+
+            # verify that all required keys are present
+            if not all(k in request_body for k in required_keys):
+                Logger.error(f"Invalid request, missing parameters.")
+                return E_MISSING_PARAMETERS, C_BAD_REQUEST
+
+            # get status and reason from the request
+            status = request_body[STATUS]
+            reason = request_body.get(REASON, REASON_UNDEFINED)
+            try:
+                # set certificate status using certificate_service
+                self.certificate_service.set_certificate_revocation_status(identifier, status, reason)
+            except (RevocationReasonInvalidException, CertificateStatusInvalidException):
+                # these exceptions are thrown in case invalid status or revocation reason is passed to the controller
+                Logger.error(f"Invalid request, wrong parameters.")
+                return E_WRONG_PARAMETERS, C_BAD_REQUEST
+            except CertificateAlreadyRevokedException:
+                Logger.error(f"Certificate is already revoked 'ID = {identifier}'.")
+                return E_NO_CERTIFICATE_ALREADY_REVOKED, C_BAD_REQUEST
+            except CertificateNotFoundException:
+                Logger.error(f"No such certificate found 'ID = {identifier}'.")
+                return E_NO_CERTIFICATES_FOUND, C_NOT_FOUND
+            except CertificateCannotBeSetToValid as e:
+                return {"success": False, "data": str(e)}, C_BAD_REQUEST
+            return {"success": True,
+                    "data": "Certificate status updated successfully."}, C_SUCCESS
+        # throw an error in case the request does not contain a json body
+        else:
+            Logger.error(f"The request must be JSON-formatted.")
+            return E_NOT_JSON_FORMAT, C_BAD_REQUEST
+
+    def cert_to_dict_partial(self, c):
+        """
+        Dictionarizes a certificate directly fetched from the database. Contains partial information.
+        :param c: target cert
+        :return: certificate dict (compliant with some parts of the REST API)
+        """
 
-        :return:
-            list of the certificates OR
-            None if the list is empty OR
-            sqlite3.Error if an exception is thrown
+        # TODO check log
+        Logger.debug(f"Function launched.")
+
+        c_issuer = self.certificate_service.get_certificate(c.parent_id)
+        if c_issuer is None:
+            return None
+
+        return {
+            ID: c.certificate_id,
+            COMMON_NAME: c.common_name,
+            NOT_BEFORE: datetime.strptime(c.valid_from, DATETIME_FORMAT).date(),
+            NOT_AFTER: datetime.strptime(c.valid_to, DATETIME_FORMAT).date(),
+            USAGE: {CertController.INVERSE_USAGE_KEY_MAP[k]: v for k, v in c.usages.items()},
+            ISSUER: {
+                ID: c_issuer.certificate_id,
+                COMMON_NAME: c_issuer.common_name
+            }
+        }
+
+    def cert_to_dict_full(self, c):
+        """
+        Dictionarizes a certificate directly fetched from the database, but adds subject info.
+        Contains full information.
+        :param c: target cert
+        :return: certificate dict (compliant with some parts of the REST API)
         """
 
-        Logger.debug("Function launched.")
+        Logger.info(f"Function launched.")
 
-        try:
-            sql = (f"SELECT * FROM {TAB_CERTIFICATES} "
-                   f"WHERE {COL_PARENT_ID} = ? AND {COL_REVOCATION_DATE} IS NOT NULL AND {COL_REVOCATION_DATE} != ''")
-            values = [certificate_id]
-            self.cursor.execute(sql, values)
-            certificate_rows = self.cursor.fetchall()
-
-            certificates: List[Certificate] = []
-            for certificate_row in certificate_rows:
-                sql = (f"SELECT * FROM {TAB_CERTIFICATE_USAGES} "
-                       f"WHERE {COL_CERTIFICATE_ID} = ?")
-                values = [certificate_row[0]]
-                self.cursor.execute(sql, values)
-                usage_rows = self.cursor.fetchall()
-
-                usage_dict: Dict[int, bool] = {}
-                for usage_row in usage_rows:
-                    usage_dict[usage_row[2]] = True
-
-                certificates.append(Certificate(certificate_row[0],      # ID
-                                                certificate_row[1],      # valid from
-                                                certificate_row[2],      # valid to
-                                                certificate_row[3],      # pem data
-                                                certificate_row[14],     # type ID
-                                                certificate_row[15],     # parent ID
-                                                certificate_row[16],     # private key ID
-                                                usage_dict,
-                                                certificate_row[4],      # common name
-                                                certificate_row[5],      # country code
-                                                certificate_row[6],      # locality
-                                                certificate_row[7],      # province
-                                                certificate_row[8],      # organization
-                                                certificate_row[9],      # organizational unit
-                                                certificate_row[10],     # email address
-                                                certificate_row[11],     # revocation date
-                                                certificate_row[12]))    # revocation reason
-        except IntegrityError:
-            Logger.error(INTEGRITY_ERROR_MSG)
-            raise DatabaseException(INTEGRITY_ERROR_MSG)
-        except ProgrammingError:
-            Logger.error(PROGRAMMING_ERROR_MSG)
-            raise DatabaseException(PROGRAMMING_ERROR_MSG)
-        except OperationalError:
-            Logger.error(OPERATIONAL_ERROR_MSG)
-            raise DatabaseException(OPERATIONAL_ERROR_MSG)
-        except NotSupportedError:
-            Logger.error(NOT_SUPPORTED_ERROR_MSG)
-            raise DatabaseException(NOT_SUPPORTED_ERROR_MSG)
-        except DatabaseError:
-            Logger.error(DATABASE_ERROR_MSG)
-            raise DatabaseException(DATABASE_ERROR_MSG)
-        except Error:
-            Logger.error(ERROR_MSG)
-            raise DatabaseException(ERROR_MSG)
-
-        return certificates
-
-    def get_all_issued_by(self, certificate_id: int):
+        subj = self.certificate_service.get_subject_from_certificate(c)
+        c_issuer = self.certificate_service.get_certificate(c.parent_id)
+        if c_issuer is None:
+            return None
+
+        return {
+            SUBJECT: subj.to_dict(),
+            NOT_BEFORE: datetime.strptime(c.valid_from, DATETIME_FORMAT).date(),
+            NOT_AFTER: datetime.strptime(c.valid_to, DATETIME_FORMAT).date(),
+            USAGE: {CertController.INVERSE_USAGE_KEY_MAP[k]: v for k, v in c.usages.items()},
+            CA: c_issuer.certificate_id
+        }
+
+    def get_private_key_of_a_certificate(self, id):
         """
-        Get list of the certificates that are direct descendants of the certificate with the ID
+        Get a private key used to sign a certificate in PEM format specified by certificate's ID
 
-        :param certificate_id: ID of specific certificate
+        :param id: ID of a certificate whose private key is to be queried
+        :type id: dict | bytes
 
-        :return:
-            list of the certificates OR
-            None if the list is empty OR
-            sqlite3.Error if an exception is thrown
+        :rtype: PemResponse
         """
 
-        Logger.debug("Function launched.")
+        Logger.info(f"\n\t{request.referrer}"
+                    f"\n\t{request.method}   {request.path}   {request.scheme}"
+                    f"\n\tCertificate ID = {id}")
 
+        # try to parse the supplied ID
         try:
-            sql = (f"SELECT * FROM {TAB_CERTIFICATES} "
-                   f"WHERE {COL_PARENT_ID} = ? AND {COL_ID} != ? AND "
-                   f"({COL_DELETION_DATE} IS NULL OR {COL_DELETION_DATE} = '')")
-            values = [certificate_id, certificate_id]
-            self.cursor.execute(sql, values)
-            certificate_rows = self.cursor.fetchall()
-
-            certificates: List[Certificate] = []
-            for certificate_row in certificate_rows:
-                sql = (f"SELECT * FROM {TAB_CERTIFICATE_USAGES} "
-                       f"WHERE {COL_CERTIFICATE_ID} = ?")
-                values = [certificate_row[0]]
-                self.cursor.execute(sql, values)
-                usage_rows = self.cursor.fetchall()
-
-                usage_dict: Dict[int, bool] = {}
-                for usage_row in usage_rows:
-                    usage_dict[usage_row[2]] = True
-
-                certificates.append(Certificate(certificate_row[0],      # ID
-                                                certificate_row[1],      # valid from
-                                                certificate_row[2],      # valid to
-                                                certificate_row[3],      # pem data
-                                                certificate_row[14],     # type ID
-                                                certificate_row[15],     # parent ID
-                                                certificate_row[16],     # private key ID
-                                                usage_dict,
-                                                certificate_row[4],      # common name
-                                                certificate_row[5],      # country code
-                                                certificate_row[6],      # locality
-                                                certificate_row[7],      # province
-                                                certificate_row[8],      # organization
-                                                certificate_row[9],      # organizational unit
-                                                certificate_row[10],     # email address
-                                                certificate_row[11],     # revocation date
-                                                certificate_row[12]))    # revocation reason
-        except IntegrityError:
-            Logger.error(INTEGRITY_ERROR_MSG)
-            raise DatabaseException(INTEGRITY_ERROR_MSG)
-        except ProgrammingError:
-            Logger.error(PROGRAMMING_ERROR_MSG)
-            raise DatabaseException(PROGRAMMING_ERROR_MSG)
-        except OperationalError:
-            Logger.error(OPERATIONAL_ERROR_MSG)
-            raise DatabaseException(OPERATIONAL_ERROR_MSG)
-        except NotSupportedError:
-            Logger.error(NOT_SUPPORTED_ERROR_MSG)
-            raise DatabaseException(NOT_SUPPORTED_ERROR_MSG)
-        except DatabaseError:
-            Logger.error(DATABASE_ERROR_MSG)
-            raise DatabaseException(DATABASE_ERROR_MSG)
-        except Error:
-            Logger.error(ERROR_MSG)
-            raise DatabaseException(ERROR_MSG)
-
-        return certificates
-
-    def get_all_issued_by_filter(self, issuer_id, target_types, target_usages, target_cn_substring, page, per_page):
+            v = int(id)
+        except ValueError:
+            Logger.error(f"Invalid request, wrong parameters 'id'[{id}].")
+            return E_WRONG_PARAMETERS, C_BAD_REQUEST
+
+        # find a certificate using the given ID
+        cert = self.certificate_service.get_certificate(v)
+
+        if cert is None:
+            Logger.error(f"No such certificate found 'ID = {v}'.")
+            return E_NO_CERTIFICATES_FOUND, C_NOT_FOUND
+        else:
+            # certificate exists, fetch it's private key
+            private_key = self.key_service.get_key(cert.private_key_id)
+            if cert is None:
+                Logger.error(f"Internal server error (certificate's private key cannot be found).")
+                return E_NO_CERT_PRIVATE_KEY_FOUND, C_INTERNAL_SERVER_ERROR
+            else:
+                return {"success": True, "data": private_key.private_key}, C_SUCCESS
+
+    def get_public_key_of_a_certificate(self, id):
         """
-        Get list of the certificates that are direct descendants of the certificate with the ID
-
-        :param target_types: certificate types (filter)
-        :param target_usages: certificate usages (filter)
-        :param target_cn_substring: certificate CN substring (filter)
-        :param page: target page
-        :param per_page: target page size
-        :param issuer_id: ID of specific certificate
-
-        :return:
-            list of the certificates OR
-            None if the list is empty
+        Get a public key of a certificate in PEM format specified by certificate's ID
+
+        :param id: ID of a certificate whose public key is to be queried
+        :type id: dict | bytes
+
+        :rtype: PemResponse
         """
 
-        Logger.debug("Function launched.")
+        Logger.info(f"\n\t{request.referrer}"
+                    f"\n\t{request.method}   {request.path}   {request.scheme}"
+                    f"\n\tCertificate ID = {id}")
 
+        # try to parse the supplied ID
         try:
-            values = [issuer_id, issuer_id]
-            values += list(target_types)
-            values += list(target_usages)
-
-            sql = (
-                f"SELECT a.{COL_ID}, {COL_COMMON_NAME}, {COL_VALID_FROM}, {COL_VALID_TO}, {COL_PEM_DATA}, "
-                f"{COL_REVOCATION_DATE}, {COL_REVOCATION_REASON}, {COL_PRIVATE_KEY_ID}, {COL_TYPE_ID}, {COL_PARENT_ID} "
-                f"FROM {TAB_CERTIFICATES} a "
-                f"WHERE (a.{COL_DELETION_DATE} IS NULL OR a.{COL_DELETION_DATE} = '') "
-                f"AND a.{COL_PARENT_ID} = ? AND a.{COL_ID} != ? "
-                f"AND a.{COL_TYPE_ID} IN ({','.join('?' * len(target_types))}) "
-                f"AND (SELECT COUNT(*) FROM {TAB_CERTIFICATE_USAGES} "
-                f"WHERE {COL_CERTIFICATE_ID} = a.{COL_ID} "
-                f"AND {COL_USAGE_TYPE_ID} IN ({','.join('?' * len(target_usages))}) "
-                f") > 0 "
-            )
+            v = int(id)
+        except ValueError:
+            Logger.error(f"Invalid request, wrong parameters 'id'[{id}].")
+            return E_WRONG_PARAMETERS, C_BAD_REQUEST
+
+        # find a certificate using the given ID
+        cert = self.certificate_service.get_certificate(v)
+
+        if cert is None:
+            Logger.error(f"No such certificate found 'ID = {v}'.")
+            return E_NO_CERTIFICATES_FOUND, C_NOT_FOUND
+        else:
+            return {"success": True, "data": self.certificate_service.get_public_key_from_certificate(cert)}, C_SUCCESS
 
-            if target_cn_substring is not None:
-                sql += f" AND a.{COL_COMMON_NAME} LIKE '%' || ? || '%'"
-
-                values += [target_cn_substring]
-
-            if page is not None and per_page is not None:
-                sql += f" LIMIT {per_page} OFFSET {page * per_page}"
-
-            self.cursor.execute(sql, values)
-            certificate_rows = self.cursor.fetchall()
-
-            certificates: List[Certificate] = []
-            for certificate_row in certificate_rows:
-                sql = (f"SELECT * FROM {TAB_CERTIFICATE_USAGES} "
-                       f"WHERE {COL_CERTIFICATE_ID} = ?")
-                values = [certificate_row[0]]
-                self.cursor.execute(sql, values)
-                usage_rows = self.cursor.fetchall()
-
-                usage_dict: Dict[int, bool] = {}
-                for usage_row in usage_rows:
-                    usage_dict[usage_row[2]] = True
-
-                certificates.append(Certificate(certificate_row[0],
-                                                certificate_row[1],
-                                                certificate_row[2],
-                                                certificate_row[3],
-                                                certificate_row[4],
-                                                certificate_row[7],
-                                                certificate_row[8],
-                                                certificate_row[9],
-                                                usage_dict,
-                                                certificate_row[5],
-                                                certificate_row[6]))
-        except IntegrityError:
-            Logger.error(INTEGRITY_ERROR_MSG)
-            raise DatabaseException(INTEGRITY_ERROR_MSG)
-        except ProgrammingError:
-            Logger.error(PROGRAMMING_ERROR_MSG)
-            raise DatabaseException(PROGRAMMING_ERROR_MSG)
-        except OperationalError:
-            Logger.error(OPERATIONAL_ERROR_MSG)
-            raise DatabaseException(OPERATIONAL_ERROR_MSG)
-        except NotSupportedError:
-            Logger.error(NOT_SUPPORTED_ERROR_MSG)
-            raise DatabaseException(NOT_SUPPORTED_ERROR_MSG)
-        except DatabaseError:
-            Logger.error(DATABASE_ERROR_MSG)
-            raise DatabaseException(DATABASE_ERROR_MSG)
-        except Error:
-            Logger.error(ERROR_MSG)
-            raise DatabaseException(ERROR_MSG)
-
-        return certificates
-
-    def get_all_descendants_of(self, certificate_id: int):
+    def delete_certificate(self, id):
         """
-        Get a list of all certificates C such that the certificate identified by "certificate_id" belongs to a trust chain
-        between C and its root certificate authority (i.e. is an ancestor of C).
-        :param certificate_id: target certificate ID
-        :return: list of all descendants
+        Deletes a certificate identified by ID, including its corresponding subtree (all descendants).
+        :param id: target certificate ID
+        :rtype: DeleteResponse
         """
 
-        Logger.debug("Function launched.")
+        Logger.info(f"\n\t{request.referrer}"
+                    f"\n\t{request.method}   {request.path}   {request.scheme}"
+                    f"\n\tCertificate ID = {id}")
 
         try:
-            def dfs(children_of, this, collection: list):
-                for child in children_of(this.certificate_id):
-                    dfs(children_of, child, collection)
-                collection.append(this)
-
-            subtree_root = self.read(certificate_id)
-            if subtree_root is None:
-                return None
-
-            all_certs = []
-            dfs(self.get_all_issued_by, subtree_root, all_certs)
-        except IntegrityError:
-            Logger.error(INTEGRITY_ERROR_MSG)
-            raise DatabaseException(INTEGRITY_ERROR_MSG)
-        except ProgrammingError:
-            Logger.error(PROGRAMMING_ERROR_MSG)
-            raise DatabaseException(PROGRAMMING_ERROR_MSG)
-        except OperationalError:
-            Logger.error(OPERATIONAL_ERROR_MSG)
-            raise DatabaseException(OPERATIONAL_ERROR_MSG)
-        except NotSupportedError:
-            Logger.error(NOT_SUPPORTED_ERROR_MSG)
-            raise DatabaseException(NOT_SUPPORTED_ERROR_MSG)
-        except DatabaseError:
-            Logger.error(DATABASE_ERROR_MSG)
-            raise DatabaseException(DATABASE_ERROR_MSG)
-        except Error:
-            Logger.error(ERROR_MSG)
-            raise DatabaseException(ERROR_MSG)
-
-        return all_certs
-
-    def get_next_id(self) -> int:
+            v = int(id)
+        except ValueError:
+            Logger.error(f"Invalid request, wrong parameters 'id'[{id}].")
+            return E_WRONG_PARAMETERS, C_BAD_REQUEST
+
+        try:
+            self.certificate_service.delete_certificate(v)
+        except CertificateNotFoundException:
+            Logger.error(f"No such certificate found 'ID = {v}'.")
+            return E_NO_CERTIFICATES_FOUND, C_NOT_FOUND
+        except DatabaseException:
+            Logger.error(f"Internal server error (corrupted database).")
+            return E_CORRUPTED_DATABASE, C_INTERNAL_SERVER_ERROR
+        except CertificateStatusInvalidException or RevocationReasonInvalidException or UnknownException:
+            Logger.error(f"Internal server error (unknown origin).")
+            return E_GENERAL_ERROR, C_INTERNAL_SERVER_ERROR
+
+        return {"success": True, "data": "The certificate and its descendants have been successfully deleted."}
+
+    def generate_certificate_pkcs_identity(self, id):
         """
-        Get identifier of the next certificate that will be inserted into the database
-        :return: identifier of the next certificate that will be added into the database
+        Generates a PKCS12 identity (including the chain of trust) of the certificate given by the specified ID.
+        Response is of application/x-pkcs12 type.
+
+        :param id: ID of a certificate whose PKCS12 identity should be generated
+        :type id: int
+
+        :rtype: Response
         """
 
-        Logger.debug("Function launched.")
+        Logger.info(f"\n\t{request.referrer}"
+                    f"\n\t{request.method}   {request.path}   {request.scheme}"
+                    f"\n\tCertificate ID = {id}")
 
+        # try to parse the supplied ID
         try:
-            # get next IDs of all tables
-            self.cursor.execute("SELECT * FROM SQLITE_SEQUENCE")
-            results = self.cursor.fetchall()
-
-            # search for next ID in Certificates table and return it
-            for result in results:
-                if result[0] == TAB_CERTIFICATES:
-                    return result[1] + 1  # current last id + 1
-            # if certificates table is not present in the query results, return 1
-        except IntegrityError:
-            Logger.error(INTEGRITY_ERROR_MSG)
-            raise DatabaseException(INTEGRITY_ERROR_MSG)
-        except ProgrammingError:
-            Logger.error(PROGRAMMING_ERROR_MSG)
-            raise DatabaseException(PROGRAMMING_ERROR_MSG)
-        except OperationalError:
-            Logger.error(OPERATIONAL_ERROR_MSG)
-            raise DatabaseException(OPERATIONAL_ERROR_MSG)
-        except NotSupportedError:
-            Logger.error(NOT_SUPPORTED_ERROR_MSG)
-            raise DatabaseException(NOT_SUPPORTED_ERROR_MSG)
-        except DatabaseError:
-            Logger.error(DATABASE_ERROR_MSG)
-            raise DatabaseException(DATABASE_ERROR_MSG)
-        except Error: