image: ubuntu:22.04

stages:
  - test
  - build-image

workflow:
  rules:
    - if: $CI_MERGE_REQUEST_ID
      when: never
    - when: always

variables:
  POSTGRES_DB: dbname # Set database.
  POSTGRES_USER: test_user # Set username.
  POSTGRES_PASSWORD: ""
  POSTGRES_HOST_AUTH_METHOD: "trust"

test-general:
  stage: test
  before_script:
    - export DEBIAN_FRONTEND="noninteractive"
    - apt-get update
    - apt-get install -y jq curl wget git s3cmd sudo git make lsb-release gnupg gcc
    - wget --quiet https://golang.org/dl/go1.17.2.linux-amd64.tar.gz
    - sudo rm -rf /usr/local/go && sudo tar -C /usr/local -xzf go1.17.2.linux-amd64.tar.gz
    - echo "export PATH=\$PATH:/usr/local/go/bin" >> ~/.profile
    - source ~/.profile
    - cd ./pghrep && make main && cd ..
    - sudo .ci/prepare_cluster.sh "15"
    - ps ax | grep postgres
    - psql -U postgres -p 5432 -c 'show data_directory;'
    - psql -U postgres -p 5433 -c 'show data_directory;'
    - psql -U postgres -p 5434 -c 'show data_directory;'
    # Configure ssh keys
    - mkdir -p ~/.ssh && echo "${PRIVATE_KEY}" > ~/.ssh/id_rsa && echo "${PUBLIC_KEY}" > ~/.ssh/authorized_keys && echo "${PUBLIC_KEY}" > ~/.ssh/id_rsa.pub && chmod 644 ~/.ssh/authorized_keys && chmod 644 ~/.ssh/id_rsa.pub && chmod 600 ~/.ssh/id_rsa
    - echo "${PRIVATE_KEY}" > ~/.ssh/gitlab_key && chmod 600 ~/.ssh/gitlab_key
    - echo "Host gitlab.com" > ~/.ssh/config && echo "  Preferredauthentications publickey" >> ~/.ssh/config && echo "  IdentityFile ~/.ssh/gitlab_key" >> ~/.ssh/config
    - apt-get -y install openssh-server
    - service ssh restart

  script:
    - bash -version
    - psql -d dbname -U test_user -c "SELECT version();"
    - psql -p 5433 -d dbname -U test_user -c "SELECT version();"
    - psql -p 5434 -d dbname -U test_user -c "SELECT version();"
    - psql -d dbname -U test_user -f .ci/test_db_dump.sql
    - .ci/prepare_test_db.sh
    - vacuumdb -U test_user dbname --analyze
    - export ARTIFACTS_PATH=$(pwd)/artifacts && echo $ARTIFACTS_PATH
    # First run only for K00X reports
    - ./checkup collect -h postgres.test1.node --pg-port 5433 --username test_user --project test --dbname dbname -e 1 --file resources/checks/K000_query_analysis.sh > >(tee -a std.log) 2> >(tee -a err.log >&2)
    - ./checkup collect  -h postgres.test2.node --pg-port 5434 --username test_user --project test --dbname dbname -e 1 --file resources/checks/K000_query_analysis.sh > >(tee -a std.log) 2> >(tee -a err.log >&2)
    - ./checkup collect  -h postgres.test3.node --username test_user --project test --dbname dbname -e 1 --file resources/checks/K000_query_analysis.sh > >(tee -a std.log) 2> >(tee -a err.log >&2)
    # Last run of checkup
    - ./checkup collect  -h postgres.test1.node --pg-port 5433 --username test_user --project test --dbname dbname -e 1 > >(tee -a std.log) 2> >(tee -a err.log >&2)
    - ./checkup collect  -h postgres.test2.node --pg-port 5434 --username test_user --project test --dbname dbname -e 1 > >(tee -a std.log) 2> >(tee -a err.log >&2)
    - ./checkup collect  -h postgres.test3.node --username test_user --project test --dbname dbname -e 1 > >(tee -a std.log) 2> >(tee -a err.log >&2)
    - ./checkup process --project test
    # Check results
    - cat err.log
    - grep -v "To compare results, 2 runs are needed. Please run './checkup collect' once again for this epoch." err.log > err1.log || true
    - grep -v "this is not a real error. Just run check again." err1.log > err.log || true
    - grep -v "Failed to create bus connection" err.log > err1.log || true
    - cat err1.log
    - export TEST_RESULT=$(stat --format="%s" err1.log) && echo $TEST_RESULT
    - ([[ $TEST_RESULT -ne 0 ]]) && exit 1
    - (git config --global user.name "postgres-ai" && git config --global user.email "[email protected]" && git config --global push.default simple) || true
    - eval $(ssh-agent -s) || true
    - ssh-add ~/.ssh/gitlab_key || true
    - ssh-keyscan -t rsa gitlab.com >> ~/.ssh/known_hosts || true
    - ssh -T [email protected] || true
    - (cd ~/ && git clone [email protected]:postgres-ai-team/postgres-checkup-tests.git) || true
    - (mkdir -p ~/postgres-checkup-tests/$CI_COMMIT_REF_NAME && [[ "$CI_COMMIT_REF_NAME" != "master" ]] && rm -Rf ~/postgres-checkup-tests/$CI_COMMIT_REF_NAME/*) || true
    - (cp -Rf $ARTIFACTS_PATH/test/* ~/postgres-checkup-tests/$CI_COMMIT_REF_NAME/) || true
    - (cd ~/postgres-checkup-tests && git add ./$CI_COMMIT_REF_NAME/* && git commit -m "$CI_COMMIT_REF_NAME artifacts" && git push) || true
    - exit $TEST_RESULT

  artifacts:
    paths:
      - ./artifacts/test
      - ./std.log
      - ./err.log
    expire_in: 1 week

.prepare:
  stage: test
  before_script:
    - export DEBIAN_FRONTEND="noninteractive"
    - apt-get update
    - apt-get install -y jq curl wget git s3cmd sudo git make lsb-release gnupg gcc
    - echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list
    - wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | apt-key add -
    - apt-get update
    - apt-get -y upgrade
    - apt-get -y install postgresql-client-11
    - psql --version
    - wget --quiet https://golang.org/dl/go1.17.2.linux-amd64.tar.gz
    - sudo rm -rf /usr/local/go && sudo tar -C /usr/local -xzf go1.17.2.linux-amd64.tar.gz
    - echo "export PATH=\$PATH:/usr/local/go/bin" >> ~/.profile
    - source ~/.profile
    - cd ./pghrep && make main && cd ..

.test-check:
  stage: test
  extends: ".prepare"
  script:
    - psql -h postgres -d dbname -U test_user -c "SELECT version();"
    - echo "Test H003 Non indexed FKs"
    - psql -h postgres -d dbname -U test_user -f .ci/h003_step_1.sql
    - ./checkup collect -c .ci/test.yml --file ./resources/checks/H003_non_indexed_fks.sh
    - data_dir=$(cat ./artifacts/test/nodes.json | jq -r '.last_check | .dir') && result=$(cat ./artifacts/test/json_reports/$data_dir/H003_non_indexed_fks.json | jq '.results .postgres .data .indexes') && ([[ "$result" == "[]" ]] || [[ "$result" == "null" ]]) && exit 301
    - psql -h postgres -d dbname -U test_user -f .ci/h003_step_2.sql
    - rm -Rf ./artifacts/
    - ./checkup collect -c .ci/test.yml --file ./resources/checks/H003_non_indexed_fks.sh
    - data_dir=$(cat ./artifacts/test/nodes.json | jq -r '.last_check | .dir') && result=$(cat ./artifacts/test/json_reports/$data_dir/H003_non_indexed_fks.json | jq '.results .postgres .data .indexes') && echo "$result" && cat ./artifacts/test/json_reports/$data_dir/H003_non_indexed_fks.json && ([[ ! "$result" == "[]" ]] && [[ ! "$result" == "null" ]]) && exit 302
    - echo "H003 passed"
    - echo "Test H004 redundant indexes"
    - psql -h postgres -d dbname -U test_user -f .ci/test_db_dump.sql
    - ./checkup collect -h postgres --username test_user --project test --dbname dbname -e 1 --file ./resources/checks/H004_redundant_indexes.sh
    - data_dir=$(cat ./artifacts/test/nodes.json | jq -r '.last_check | .dir') && result=$(cat ./artifacts/test/json_reports/$data_dir/H004_redundant_indexes.json | jq '.results .postgres .data .redundant_indexes ."public.t_with_redundant_idx_id"') && ( [[ "$result" == "" ]] || [[ "$result" == "null" ]]) && exit 201
    - data_dir=$(cat ./artifacts/test/nodes.json | jq -r '.last_check | .dir') && result=$(cat ./artifacts/test/json_reports/$data_dir/H004_redundant_indexes.json | jq '.results .postgres .data .redundant_indexes ."public.t_with_redundant_idx_f1"') && ( [[ "$result" == "" ]] || [[ "$result" == "null" ]]) && exit 202
    - data_dir=$(cat ./artifacts/test/nodes.json | jq -r '.last_check | .dir') && result=$(cat ./artifacts/test/json_reports/$data_dir/H004_redundant_indexes.json | jq '.results .postgres .data .redundant_indexes ."public.t_with_redundant_idx_f1_uniq"') && ([[ ! "$result" == "[]" ]] && [[ ! "$result" == "null" ]]) && exit 203
    - data_dir=$(cat ./artifacts/test/nodes.json | jq -r '.last_check | .dir') && result=$(cat ./artifacts/test/json_reports/$data_dir/H004_redundant_indexes.json | jq '.results .postgres .data .redundant_indexes ."public.t_with_redundant_idx_pkey"') && ([[ ! "$result" == "[]" ]] && [[ ! "$result" == "null" ]]) && exit 204
    - data_dir=$(cat ./artifacts/test/nodes.json | jq -r '.last_check | .dir') && result=$(cat ./artifacts/test/json_reports/$data_dir/H004_redundant_indexes.json | jq '.results .postgres .data .redundant_indexes ."public.t_with_redundant_ref_idx_1"') && ([[ ! "$result" == "[]" ]] && [[ ! "$result" == "null" ]]) && exit 205
    - data_dir=$(cat ./artifacts/test/nodes.json | jq -r '.last_check | .dir') && result=$(cat ./artifacts/test/json_reports/$data_dir/H004_redundant_indexes.json | jq '.results .postgres .data .redundant_indexes ."public.t_with_redundant_ref_idx_2"') && ( [[ "$result" == "" ]] || [[ "$result" == "null" ]]) && exit 206
    - data_dir=$(cat ./artifacts/test/nodes.json | jq -r '.last_check | .dir') && result=$(cat ./artifacts/test/json_reports/$data_dir/H004_redundant_indexes.json | jq '.results .postgres .data .redundant_indexes ."exp_redundant.t_with_redundant_ref_idx_1"') && ([[ ! "$result" == "[]" ]] && [[ ! "$result" == "null" ]]) && exit 207
    - data_dir=$(cat ./artifacts/test/nodes.json | jq -r '.last_check | .dir') && result=$(cat ./artifacts/test/json_reports/$data_dir/H004_redundant_indexes.json | jq '.results .postgres .data .redundant_indexes ."exp_redundant.t_with_redundant_ref_idx_2"') && ( [[ "$result" == "" ]] || [[ "$result" == "null" ]]) && exit 208
    - data_dir=$(cat ./artifacts/test/nodes.json | jq -r '.last_check | .dir') && result=$(cat ./artifacts/test/json_reports/$data_dir/H004_redundant_indexes.json | jq '.results .postgres .data .redundant_indexes ."public.ctnr_idx01"') && ( [[ "$result" == "" ]] || [[ "$result" == "null" ]]) && exit 209
    - data_dir=$(cat ./artifacts/test/nodes.json | jq -r '.last_check | .dir') && result=$(cat "./artifacts/test/json_reports/$data_dir/H004_redundant_indexes.json" | jq '.results .postgres .data .redundant_indexes ."public.ctnr_idx04"') && ([[ ! "$result" == "[]" ]] && [[ ! "$result" == "null" ]]) && exit 210
    - echo "H004 passed"
    - ./checkup -c .ci/test.yml --file ./resources/checks/F005_index_bloat.sh
    - ./checkup -c .ci/test.yml --file ./resources/checks/L001_table_sizes.sh
    - data_dir=$(cat ./artifacts/test/nodes.json | jq -r '.last_check | .dir') && f005_indexes_size=$(cat ./artifacts/test/json_reports/$data_dir/F005_index_bloat.json | jq '.results .postgres .data .index_bloat_total .real_size_bytes_sum') && echo $f005_indexes_size
    - data_dir=$(cat ./artifacts/test/nodes.json | jq -r '.last_check | .dir') && l001_indexes_size=$(cat ./artifacts/test/json_reports/$data_dir/L001_table_sizes.json | jq '.results .postgres .data .tables_data_total .indexes_size_bytes_sum') && echo $l001_indexes_size
    - diff=$((f005_indexes_size - l001_indexes_size)) && diff=${diff#-} && echo $diff && ratio=$((diff * 100 / l001_indexes_size)) && echo $ratio && ([[ $ratio -gt "5" ]]) && exit 303
    - echo "F005/L001 Total indexes size passed"
    - ./checkup -c .ci/test.yml --file ./resources/checks/F004_heap_bloat.sh
    - data_dir=$(cat ./artifacts/test/nodes.json | jq -r '.last_check | .dir') && ([[ ! -f "./artifacts/test/json_reports/$data_dir/F004_heap_bloat.json" ]] || [[ ! -f "./artifacts/test/md_reports/$data_dir/F004.md" ]]) && exit 304
    - data_dir=$(cat ./artifacts/test/nodes.json | jq -r '.last_check | .dir') && msg=$(grep "can be reduced 0.00 times" ./artifacts/test/md_reports/$data_dir/F004.md) && [[ ! -z "$msg" ]] && exit 304
    - ([[ "$CI_COMMIT_REF_NAME" != "master" ]]) && exit 0
    # Check small indexes
    - .ci/prepare_test_db.sh postgres
    - ./checkup -c .ci/test.yml --file ./resources/checks/H002_unused_indexes.sh
    - ./checkup -c .ci/test.yml --file ./resources/checks/H004_redundant_indexes.sh
    ## unused
    - data_dir=$(cat ./artifacts/test/nodes.json | jq -r '.last_check | .dir') && result=$(cat "./artifacts/test/json_reports/$data_dir/H002_unused_indexes.json" | jq '.results .postgres .data .never_used_indexes ."public.i_u_12"') && ( [[ "$result" == "" ]] || [[ "$result" == "null" ]]) && exit 210
    ## redundant
    - data_dir=$(cat ./artifacts/test/nodes.json | jq -r '.last_check | .dir') && result=$(cat "./artifacts/test/json_reports/$data_dir/H004_redundant_indexes.json" | jq '.results .postgres .data .redundant_indexes ."public.i_r_12"') && ( [[ "$result" == "" ]] || [[ "$result" == "null" ]]) && exit 211
    - echo "Check small indexes in small db mode passed"
    - .ci/prepare_large_db.sh postgres
    - ./checkup -c .ci/test.yml --file ./resources/checks/H002_unused_indexes.sh
    - ./checkup -c .ci/test.yml --file ./resources/checks/H004_redundant_indexes.sh
    ## unused
    - data_dir=$(cat ./artifacts/test/nodes.json | jq -r '.last_check | .dir') && result=$(cat "./artifacts/test/json_reports/$data_dir/H002_unused_indexes.json" | jq '.results .postgres .data .never_used_indexes ."public.i_u_12"') && ([[ ! "$result" == "[]" ]] && [[ ! "$result" == "null" ]]) && exit 212
    ## redundant
    - data_dir=$(cat ./artifacts/test/nodes.json | jq -r '.last_check | .dir') && result=$(cat "./artifacts/test/json_reports/$data_dir/H004_redundant_indexes.json" | jq '.results .postgres .data .redundant_indexes ."public.i_r_12"') && ([[ ! "$result" == "[]" ]] && [[ ! "$result" == "null" ]]) && exit 213
    - echo "Check small indexes in large db mode passed"

test-pghrep:
  stage: test
  extends: ".prepare"
  script:
    - cd pghrep && make test

test-check-9.6:
  extends: ".test-check"
  stage: "test"
  services:
    - postgres:9.6

test-check-10:
  extends: ".test-check"
  stage: "test"
  services:
    - postgres:10

test-check-11:
  extends: ".test-check"
  stage: "test"
  services:
    - postgres:11

test-check-12:
  extends: ".test-check"
  stage: "test"
  services:
    - postgres:12

test-check-13:
  extends: ".test-check"
  stage: "test"
  services:
    - postgres:13

test-check-14:
  extends: ".test-check"
  stage: "test"
  services:
    - postgres:14

test-check-15:
  extends: ".test-check"
  stage: "test"
  services:
    - postgres:15

test-check-16:
  extends: ".test-check"
  stage: "test"
  services:
    - postgres:16

test-check-cli:
  services:
    - postgres:16
  stage: "test"
  extends: ".prepare"
  script: |
    errcount=0
    printTail="                                                                      "
    for f in tests/cli_*.sh; do
      printf "$f${printTail:0:-${#f}}"
      bash "$f" -H
      status=$?
      if [ "$status" -ne 0 ]; then
        errcount="$(($errcount+1))"
      fi
    done
    if [ "$errcount" -ne 0 ]; then
      >&2 echo "Oh no! $errcount tests failed"
      exit 1
    fi

.job_template: &build_image_definition
  image: docker:19
  stage: build-image
  artifacts:
    paths:
      - bin
  services:
    - docker:dind
  script:
    - apk update && apk upgrade && apk add --no-cache bash # TODO(anatoly): Remove dependency.
    - bash ./.ci/docker_build_push.sh

.only_var_template: &only_tag_release
  rules:
    - if: $CI_COMMIT_TAG =~ /^[0-9.]+$/

.only_var_template: &only_tag_rc
  rules:
    - if: $CI_COMMIT_TAG =~ /^[0-9.]+[\-_]*[a-zA-Z]+[a-zA-Z0-9.\-_]*[a-zA-Z0-9]+$/

.only_var_template: &only_master
  rules:
    - if: $CI_COMMIT_BRANCH == "master"

.only_var_template: &only_feature
  rules:
    - if: $CI_COMMIT_REF_SLUG && $CI_COMMIT_REF_SLUG != "master"

build-image-feature:
  <<: *build_image_definition
  <<: *only_feature
  variables:
    REGISTRY_USER: "${CI_REGISTRY_USER}"
    REGISTRY_PASSWORD: "${CI_REGISTRY_PASSWORD}"
    REGISTRY: "${CI_REGISTRY}"
    DOCKER_NAME: "registry.gitlab.com/postgres-ai/postgres-checkup"
    TAGS: "${DOCKER_NAME}:${CI_COMMIT_REF_SLUG}"

build-image-master:
  <<: *build_image_definition
  <<: *only_master
  variables:
    DOCKER_NAME: "registry.gitlab.com/postgres-ai/postgres-checkup"
    TAGS: "${DOCKER_NAME}:master,${DOCKER_NAME}:master-${CI_COMMIT_SHORT_SHA}"

build-image-latest:
  <<: *build_image_definition
  <<: *only_tag_release
  variables:
    REGISTRY_USER: "${DH_CI_REGISTRY_USER}"
    REGISTRY_PASSWORD: "${DH_CI_REGISTRY_PASSWORD}"
    REGISTRY: "${DH_CI_REGISTRY}"
    DOCKER_NAME: "postgresai/postgres-checkup"
    TAGS: "${DOCKER_NAME}:latest,${DOCKER_NAME}:${CI_COMMIT_TAG}"

build-image-rc:
  <<: *build_image_definition
  <<: *only_tag_rc
  variables:
    REGISTRY_USER: "${DH_CI_REGISTRY_USER}"
    REGISTRY_PASSWORD: "${DH_CI_REGISTRY_PASSWORD}"
    REGISTRY: "${DH_CI_REGISTRY}"
    DOCKER_NAME: "postgresai/postgres-checkup"
    TAGS: "${DOCKER_NAME}:${CI_COMMIT_TAG}"