---

create_db.sql
250 B
-- poweruser
SET SESSION ROLE db_mgr;
create DATABASE "xapidb";
-- sysadmin
SELECT dba.create_login(__login_name => 'xapiservice', __pass => 'xapiservice123' );
SELECT dba.create_login(__login_name => 'xapideveloper', __pass => 'xapideveloper123' );


CREATE OR REPLACE FUNCTION xapi_check_service_subscription(
    p_subscriber_name text,
    p_http_method text,
    p_method_url text,  -- было p_service_name
    p_headers jsonb
)
RETURNS jsonb AS $$
DECLARE
    v_subscriber_id integer;
    v_subscriber_name text;
    v_method_id integer;
    v_system_id integer;
    v_granted integer;
    v_required_parameters jsonb := '{}';
    v_missing_parameters jsonb := '{}';
    v_header_value text;   
    v_systems RECORD;
    v_param RECORD;
    v_received_value TEXT;
    v_value text;
    v_found BOOLEAN := FALSE;
    v_system_name text;
BEGIN
    -- Получаем ID подписчика
    SELECT subscriber_id, subscriber_name 
    INTO v_subscriber_id, v_subscriber_name
    FROM xapi_subscribers
    WHERE tech_user_name = p_subscriber_name;

    if v_subscriber_name = 'xapi' then
      RETURN jsonb_build_object('result','OK','message','Доступ к методу разрешен','system_name','xapi');
    end if;
   
    IF v_subscriber_id IS NULL then
      return jsonb_build_object('result','ERROR','message','Не найден подписчик ' || p_subscriber_name,'system_name','');
    END IF;

    -- Получаем ID системы
    -- по списку подписок
    for v_systems in 
      SELECT system_id, count(*) over (partition by subscriber_id) subscriber_systems_count
      FROM xapi_system_subscribers
      WHERE subscriber_id = v_subscriber_id
    loop
	  if v_systems.subscriber_systems_count = 1 then
	    v_system_id := v_systems.system_id;
	    exit;
	  end if;
	 
      -- плюс параметры
	  FOR v_param IN
        SELECT sp.parameter_id, p.parameter_name, sp.parameter_value
        FROM xapi_system_parameters sp
        JOIN xapi_parameters p ON sp.parameter_id = p.parameter_id
        WHERE sp.system_id = v_systems.system_id
      loop	    
        -- Извлекаем значение из переданных заголовков
        SELECT value INTO v_received_value
        FROM jsonb_each_text(p_headers)
        WHERE key = v_param.parameter_name;

        -- Если значение совпадает — отмечаем, что хотя бы один параметр прошёл
        IF v_received_value = v_param.parameter_value THEN
          v_found := TRUE;
          EXIT; -- Выходим из цикла, так как нашли подходящий параметр
        END IF;
      END LOOP;
     
      -- у системы не задан параметры или параметр найден
      if not found or v_found then 
  	    v_system_id := v_systems.system_id;
  	    exit;
      end if;
    end loop;
   
    IF v_system_id IS NULL THEN
      RETURN jsonb_build_object('result','ERROR','message','Не определена внешняя система для подписчика ' || p_subscriber_name,'system_name','');
    END IF;

    SELECT system_name
    INTO v_system_name
    FROM xapi_systems
    WHERE system_id = v_system_id;

    -- Получаем ID метода через URL и HTTP-метод
    SELECT m.method_id INTO v_method_id
    FROM xapi_methods m
    WHERE m.method_url = p_method_url
      AND m.http_method = p_http_method
      AND m.active = 'Y';

    IF v_method_id IS NULL THEN
      RETURN jsonb_build_object('result','ERROR','message','Метод ' || p_http_method || ' ' || p_method_url || ' отсутствует в xAPI или отключен','system_name',v_system_name);
    END IF;

    -- Проверка активности системы
    for v_systems in 
      SELECT active, system_name FROM xapi_systems WHERE system_id = v_system_id 
    loop
      IF v_systems.active = 'N' THEN
        RETURN jsonb_build_object('result','ERROR','message','Внешняя система ' || v_systems.system_name || ' деактивирована','system_name',v_system_name);
      END IF;
    end loop;

    -- Проверяем, есть ли разрешение у системы на этот метод
    SELECT count(*) INTO v_granted
    FROM xapi_method_grants
    WHERE method_id = v_method_id
      AND system_id = v_system_id
      and CURRENT_DATE between start_date and COALESCE(end_date, 'infinity'::date);

    IF v_granted = 0 THEN
      RETURN jsonb_build_object('result','ERROR','message','Доступ к методу ' || p_http_method || ' ' || p_method_url || ' запрещен','system_name',v_system_name);
    END IF;

    -- Возвращаем успешный результат с именем системы
    RETURN jsonb_build_object('result','OK','message','Доступ к методу разрешен','system_name',v_system_name);
END;
$$ LANGUAGE plpgsql;

grant execute on function xapi_check_service_subscription TO PUBLIC;
grant ALL on function xapi_check_service_subscription TO xapideveloper;


variables:
  PROJECT_NAME: vendors
  TEAM_NAME: nfs
  IMAGE_REGISTRY: harbor.vimpelcom.ru
  IMAGE: "xapi_vendors"
  TAG: "1.0.1"
  SERVICE_PORT: "8111"
stages:
  - build_app
  - build_docker
  - deploy

build_app:
  image: harbor.vimpelcom.ru/nfs/ubuntu-builder@sha256:8a194582d2316a1b3658b7441f267c3809d98de5f6cdacdc8ac25d221b5bb941
  stage: build_app
  artifacts:
    paths:
      - target/*.jar
    expire_in: 1 day
  script: 
    #push to harbor
    - java -version
    - mvn clean package -e
  tags:
    - yd-m6-ktrun34-build-deploy

.build_docker_template:
  image: harbor.vimpelcom.ru/dockerhub/library/docker@sha256:6f4ccd1fda9a901a738e925982c81e9ba960c11bb0c9136fc2bace7233e8e9ce
  stage: build_docker
  services:
    - harbor.vimpelcom.ru/dockerhub/library/docker@sha256:6f4ccd1fda9a901a738e925982c81e9ba960c11bb0c9136fc2bace7233e8e9ce
  variables:
    DOCKER_HOST: tcp://localhost:2375
    DOCKER_TLS_CERTDIR: ""
  dependencies:
    - build_app
  before_script:
    - cp ./.gitlab-ci/certs/VimpelcomCAG2.pem /root/my-root-ca.crt
    - cp ./.gitlab-ci/certs/VimpelcomCAG2.pem /usr/local/share/ca-certificates/VimpelcomCAG2.crt
    - cat /root/my-root-ca.crt >> /etc/ssl/certs/ca-certificates.crt
    - update-ca-certificates
    - sleep 60
  script:
    - docker build -t ${IMAGE_REGISTRY}/${TEAM_NAME}/${PROJECT_NAME}_${ENV} -f ./.gitlab-ci/Dockerfile . 
    - docker image ls
    - docker push ${IMAGE_REGISTRY}/${TEAM_NAME}/${PROJECT_NAME}_${ENV}
  tags:
    - k8s

.deploy_template:
  #image: harbor.vimpelcom.ru/common/ci-tools:latest
  image: harbor.vimpelcom.ru/nfs/ubuntu-builder:2.0
  stage: deploy
  script:
    - echo "${SERVER_KEY}" >> ssh_key
    - chmod 600 ssh_key
    - mkdir -p ~/.ssh
    - ssh-keyscan ${HOST} >> ~/.ssh/known_hosts
    - chmod 644 ~/.ssh/known_hosts
    - ssh -i ssh_key dorootless@${HOST} "mkdir -p ~/app"
    - scp -i ssh_key ./.gitlab-ci/docker-compose.yml dorootless@${HOST}:~/app/docker-compose.yml
    # удалять старый контейнер
    - ssh -i ssh_key dorootless@${HOST} "docker rm -f ${PROJECT_NAME}_${ENV}"
    - ssh -i ssh_key dorootless@${HOST} "docker image rm ${IMAGE_REGISTRY}/${TEAM_NAME}/${PROJECT_NAME}_${ENV}"
    # - scp -i ssh_key ./.gitlab-ci/envs/envs-test.env dorootless@camel-test.xapi-test.cloud.vimpelcom.ru:~/app/variables.env
    - ssh -i ssh_key dorootless@${HOST} "echo "NFS_URL=${TEST_NFS_URL}">~/app/variables.env"
    - ssh -i ssh_key dorootless@${HOST} "echo "NFS_USER=${TEST_NFS_USER}">>~/app/variables.env"
    - ssh -i ssh_key dorootless@${HOST} "echo "NFS_PASSWORD=${TEST_NFS_PASSWORD}">>~/app/variables.env"
    # - scp -i ssh_key ./.gitlab-ci/nginx/nginx.conf dorootless@${HOST}:~/conf/nginx.conf
    # - ssh -i ssh_key "sed -i -e 's/replace_hostname/${HOST}/g' ~/conf/nginx.conf"
    #пулим и запускаем новый контейнер
    - ssh -i ssh_key dorootless@${HOST} "docker pull ${IMAGE_REGISTRY}/${TEAM_NAME}/${PROJECT_NAME}_${ENV}"
    - ssh -i ssh_key dorootless@${HOST} "docker run -d -p ${SERVICE_PORT}:${SERVICE_PORT} --name ${PROJECT_NAME}_${ENV} --env-file=/export/home/dorootless/app/variables.env ${IMAGE_REGISTRY}/${TEAM_NAME}/${PROJECT_NAME}_${ENV}"
  tags:
    - k8s

# build_app_dev:
#   extends: .build_app_template
#   when: manual
#   variables: 
#     ENV: dev
#   only:
#     refs:
#       - dev

# build_app_test:
#   extends: .build_app_template
#   when: manual
#   variables:
#     ENV: test
#   only:
#     refs:
#       - test

# build_app_uat:
#   extends: .build_app_template
#   when: manual
#   variables:
#     ENV: uat
#   only:
#     refs:
#       - uat

# build_app_prod:
#   extends: .build_app_template
#   when: manual
#   variables:
#     ENV: prod
#   only:
#     refs:
#       - main

build_docker_dev:
  extends: .build_docker_template
  when: manual
  variables: 
    ENV: dev
  only:
    refs:
      - dev

build_docker_test:
  extends: .build_docker_template
  when: manual
  variables:
    ENV: test
  only:
    refs:
      - main

build_docker_uat:
  extends: .build_docker_template
  when: manual
  variables:
    ENV: uat
  only:
    refs:
      - uat

build_docker_prod:
  extends: .build_docker_template
  when: manual
  variables:
    ENV: prod
  only:
    refs:
      - test

deploy_dev:
  extends: .deploy_template
  when: manual
  variables: 
    ENV: dev
    SERVER_KEY: ${DEV_KEY}
    HOST: camel-karavan-dev.xapi-dev.cloud.vimpelcom.ru
    NFS_USER: ${DEV_NFS_USER}
    NFS_PASSWORD: ${DEV_NFS_PASSWORD}
    NFS_HOST: ${DEV_NFS_HOST}
  only:
    refs:
      - dev

deploy_test:
  extends: .deploy_template
  when: manual
  variables:
    ENV: test
    SERVER_KEY: ${TEST_KEY}
    HOST: camel-test.xapi-test.cloud.vimpelcom.ru
    NFS_USER: ${TEST_NFS_USER}
    NFS_PASSWORD: ${TEST_NFS_PASSWORD}
    NFS_HOST: ${TEST_NFS_HOST}
  only:
    refs:
      - main

deploy_uat:
  extends: .deploy_template
  when: manual
  variables:
    ENV: uat
    SERVER_KEY: ${UAT_KEY}
    HOST: camel-plrnd.xapi-prodlike.cloud.vimpelcom.ru
    NFS_USER: ${UAT_NFS_USER}
    NFS_PASSWORD: ${UAT_NFS_PASSWORD}
    NFS_HOST: ${UAT_NFS_HOST}
  only:
    refs:
      - uat

deploy_prod:
  extends: .deploy_template
  when: manual
  variables:
    ENV: prod
    SERVER_KEY: ${PROD_KEY}
    HOST: camel-prod.xapi-prod.cloud.vimpelcom.ru
    NFS_USER: ${PROD_NFS_USER}
    NFS_PASSWORD: ${PROD_NFS_PASSWORD}
    NFS_HOST: ${PROD_NFS_HOST}
  only:
    refs:
      - test


<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <parent>
        <groupId>org.apache.camel.springboot</groupId>
        <artifactId>spring-boot</artifactId>
        <version>4.10.2</version>
    </parent>

    <artifactId>xapi_vendors</artifactId>
    <name>XAPI Vendors service</name>
    <description>An example showing Camel REST DSL and OpenApi with Spring Boot</description>
    <version>1.0.1</version>

    <properties>
        <camel-version>4.10.2</camel-version>
        <skip.starting.camel.context>false</skip.starting.camel.context>
        <jkube-maven-plugin-version>1.18.1</jkube-maven-plugin-version>
        <jkube.generator.from>registry.access.redhat.com/ubi9/openjdk-17:latest</jkube.generator.from>
        <jkube.build.switchToDeployment>true</jkube.build.switchToDeployment>
        <kafka-avro-serializer-version>7.1.1</kafka-avro-serializer-version>
        <reactor-version>3.7.0</reactor-version>
        <testcontainers-version>1.20.4</testcontainers-version>
        <hapi-structures-v24-version>2.5.1</hapi-structures-v24-version>
        <artemis-jakarta-version>2.38.0</artemis-jakarta-version>
        <category>Rest</category>
    </properties>

	<repositories>

	    <repository>
			<id>central</id>
			<url>file://${user.home}/.m2/repository</url>
	        <releases>
	            <enabled>false</enabled>
	        </releases>
	        <snapshots>
	            <enabled>false</enabled>
	        </snapshots>
	    </repository>
    
    	<repository>
			<id>xapi__mvn__camel</id>
			<!--suppress UnresolvedMavenProperty -->
            <url>https://${env.NEXUS_USER}:${env.NEXUS_PASSWORD}@nexus.vimpelcom.ru/repository/xapi__mvn__camel/</url>
			<name>xapi__mvn__camel</name>
			<snapshots>
				<enabled>true</enabled>
			</snapshots>
			<releases>
				<enabled>true</enabled>
			</releases>
		</repository>

    	<repository>
			<id>maven-central</id>
			<url>https://nexus.vimpelcom.ru/repository/maven-central/</url>
			<name>maven-central</name>
			<snapshots>
				<enabled>true</enabled>
			</snapshots>
			<releases>
				<enabled>true</enabled>
			</releases>
		</repository>
    
	</repositories>
    <pluginRepositories>


        <pluginRepository>
            <id>central</id>
            <url>file://${user.home}/.m2/repository</url>
            <releases>
                <enabled>false</enabled>
            </releases>
            <snapshots>
                <enabled>false</enabled>
            </snapshots>
        </pluginRepository>

        <pluginRepository>
            <id>xapi__mvn__camel</id>
            <url>https://${env.NEXUS_USER}:${env.NEXUS_PASSWORD}@nexus.vimpelcom.ru/repository/xapi__mvn__camel/</url>
            <name>xapi__mvn__camel</name>
            <snapshots>
                <enabled>true</enabled>
            </snapshots>
            <releases>
                <enabled>true</enabled>
            </releases>
        </pluginRepository>

        <pluginRepository>
            <id>maven-central</id>
            <url>https://nexus.vimpelcom.ru/repository/maven-central/</url>
            <name>maven-central</name>
            <snapshots>
                <enabled>true</enabled>
            </snapshots>
            <releases>
                <enabled>true</enabled>
            </releases>
        </pluginRepository>

    </pluginRepositories>


    <!-- Spring-Boot and Camel BOM -->
    <dependencyManagement>
        <dependencies>
            <dependency>
                <groupId>org.apache.camel.springboot</groupId>
                <artifactId>camel-spring-boot-bom</artifactId>
                <version>${camel-version}</version>
                <type>pom</type>
                <scope>import</scope>
            </dependency>
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-dependencies</artifactId>
                <version>${spring-boot-version}</version>
                <type>pom</type>
                <scope>import</scope>
            </dependency>
        </dependencies>
    </dependencyManagement>


    <dependencies>
        <!-- Spring Boot -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-actuator</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>

        <!-- Camel -->
        <dependency>
            <groupId>org.apache.camel.springboot</groupId>
            <artifactId>camel-spring-boot-starter</artifactId>
        </dependency>
        <dependency>
            <groupId>org.apache.camel.springboot</groupId>
            <artifactId>camel-openapi-java-starter</artifactId>
        </dependency>
        <dependency>
            <groupId>org.apache.camel.springboot</groupId>
            <artifactId>camel-servlet-starter</artifactId>
        </dependency>
        <dependency>
            <groupId>org.apache.camel.springboot</groupId>
            <artifactId>camel-platform-http-starter</artifactId>
        </dependency>
        <dependency>
            <groupId>org.apache.camel.springboot</groupId>
            <artifactId>camel-spring-jdbc-starter</artifactId>
        </dependency>
        <dependency>
            <groupId>org.apache.camel.springboot</groupId>
            <artifactId>camel-sql-starter</artifactId>
        </dependency>
        <dependency>
            <groupId>org.apache.camel.springboot</groupId>
            <artifactId>camel-jackson-starter</artifactId>
        </dependency>
        <dependency>
            <groupId>org.apache.camel.springboot</groupId>
            <artifactId>camel-direct-starter</artifactId>
        </dependency>
        <dependency>
            <groupId>org.apache.commons</groupId>
            <artifactId>commons-lang3</artifactId>
        </dependency>

        <!-- custom -->
        <dependency>
            <groupId>org.apache.camel</groupId>
            <artifactId>camel-attachments</artifactId>
            <version>4.10.2</version>
        </dependency>
        <dependency>
            <groupId>org.apache.camel</groupId>
            <artifactId>camel-core-model</artifactId>
            <version>4.10.2</version>
        </dependency>
        <dependency>
            <groupId>org.apache.camel</groupId>
            <artifactId>camel-core-processor</artifactId>
            <version>4.10.2</version>
        </dependency>
        <dependency>
            <groupId>org.apache.camel</groupId>
            <artifactId>camel-core-reifier</artifactId>
            <version>4.10.2</version>
        </dependency>
        <dependency>
            <groupId>org.apache.camel</groupId>
            <artifactId>camel-api</artifactId>
            <version>4.10.2</version>
        </dependency>
        <dependency>
            <groupId>org.apache.camel</groupId>
            <artifactId>camel-main</artifactId>
            <version>4.10.2</version>
        </dependency>
        <dependency>
            <groupId>org.apache.camel</groupId>
            <artifactId>camel-jdbc</artifactId>
            <version>4.10.2</version>
        </dependency>
        <dependency>
            <groupId>org.apache.camel</groupId>
            <artifactId>camel-base-engine</artifactId>
            <version>4.10.2</version>
        </dependency>
        <dependency>
            <groupId>org.apache.camel</groupId>
            <artifactId>camel-core-languages</artifactId>
            <version>4.10.2</version>
        </dependency>
        <dependency>
            <groupId>org.apache.camel</groupId>
            <artifactId>camel-http-base</artifactId>
            <version>4.10.2</version>
        </dependency>
        <dependency>
            <groupId>org.apache.camel</groupId>
            <artifactId>camel-management-api</artifactId>
            <version>4.10.2</version>
        </dependency>
        <dependency>
            <groupId>org.apache.camel</groupId>
            <artifactId>camel-tooling-model</artifactId>
            <version>4.10.2</version>
        </dependency>
        <dependency>
            <groupId>org.apache.camel</groupId>
            <artifactId>camel-tooling-util</artifactId>
            <version>4.10.2</version>
        </dependency>
        <dependency>
            <groupId>org.apache.camel</groupId>
            <artifactId>camel-base</artifactId>
            <version>4.10.2</version>
        </dependency>
        <dependency>
            <groupId>org.apache.camel</groupId>
            <artifactId>camel-support</artifactId>
            <version>4.10.2</version>
        </dependency>
        <dependency>
            <groupId>org.apache.camel</groupId>
            <artifactId>camel-util</artifactId>
            <version>4.10.2</version>
        </dependency>
        <dependency>
            <groupId>org.apache.camel</groupId>
            <artifactId>camel-xml-io</artifactId>
            <version>4.10.2</version>
        </dependency>
        <dependency>
            <groupId>org.apache.camel</groupId>
            <artifactId>camel-xml-io-util</artifactId>
            <version>4.10.2</version>
        </dependency>
        <dependency>
            <groupId>org.apache.camel</groupId>
            <artifactId>camel-xml-jaxp-util</artifactId>
            <version>4.10.2</version>
        </dependency>
		<dependency>
	    	<groupId>jakarta.xml.bind</groupId>
		    <artifactId>jakarta.xml.bind-api</artifactId>
		    <version>4.0.2</version>
		</dependency>
		<dependency>
		    <groupId>org.springframework</groupId>
		    <artifactId>spring-jcl</artifactId>
		    <version>6.2.3</version>
		</dependency>

<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-tx</artifactId>
    <version>6.2.3</version>
</dependency>

<dependency>
    <groupId>io.swagger.core.v3</groupId>
    <artifactId>swagger-annotations-jakarta</artifactId>
    <version>2.2.23</version>
</dependency>
<dependency>
    <groupId>io.swagger.core.v3</groupId>
    <artifactId>swagger-core-jakarta</artifactId>
    <version>2.2.23</version>
</dependency>
<dependency>
    <groupId>io.swagger.core.v3</groupId>
    <artifactId>swagger-models-jakarta</artifactId>
    <version>2.2.23</version>
</dependency>
<dependency>
    <groupId>io.swagger.parser.v3</groupId>
    <artifactId>swagger-parser</artifactId>
    <version>2.1.25</version>
</dependency>


        <!-- db driver -->
        <dependency>
            <groupId>com.oracle.database.jdbc</groupId>
            <artifactId>ojdbc11</artifactId>
            <version>23.6.0.24.10</version>
        </dependency>

        <!-- metrics and expose for prometheus -->
        <dependency>
            <groupId>org.apache.camel.springboot</groupId>
            <artifactId>camel-micrometer-starter</artifactId>
        </dependency>
        <dependency>
            <groupId>io.micrometer</groupId>
            <artifactId>micrometer-registry-prometheus</artifactId>
        </dependency>

        <!-- Test -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.apache.camel</groupId>
            <artifactId>camel-test-junit5</artifactId>
            <version>${camel-version}</version>
            <scope>test</scope>
        </dependency>

    </dependencies>


    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
                <version>3.4.3</version>
    	        <dependencies>
	                <dependency>
                	    <groupId>commons-logging</groupId>
            	        <artifactId>commons-logging</artifactId>
        	            <version>1.2</version>
    	            </dependency>
	            </dependencies>
                <executions>
                    <execution>
                        <goals>
                            <goal>repackage</goal>
                        </goals>
                    </execution>
                </executions>
            </plugin>
        </plugins>
    </build>

</project>