ci-integration-tests-csae.yml

name: CI Integration Tests CSAE
on:
  workflow_dispatch:
  pull_request:
  push:
    branches:
      - 'main'

jobs:
  test:
    strategy:
      matrix:
        python: ['3.8', '3.9', '3.10', '3.11']
    runs-on: ubuntu-latest
    name: Functional test
    steps:
      - name: Check out repository code
        uses: actions/checkout@v4

      - name: Setup Python
        uses: actions/setup-python@v4
        with:
          python-version: ${{ matrix.python }}

      - name: Define CSAE env name
        id: define-environment-name
        run: |
          PYTHON_VERSION=$(echo -n "$PYTHON_VERSION" | tr -d '.') # remove dots from version
          echo "env-name=dbt-ci-$PYTHON_VERSION-$(date +%s%N)" >> $GITHUB_OUTPUT
        env:
          PYTHON_VERSION: ${{ matrix.python }}

      - name: Create CSAE environments
        id: create-csae-environments
        shell: bash
        run: |
          chmod 777 .github/workflows/scripts/createTestEnvironments.sh
          ./.github/workflows/scripts/createTestEnvironments.sh
        env:
          CSAE_TOKEN: ${{ secrets.CSAE_TOKEN_DEVTOOLS }}
          CSAE_ENV_PASSWORD: ${{ secrets.CSAE_ENV_PASSWORD }}
          CSAE_ENV_NAME: ${{ steps.define-environment-name.outputs.env-name}}

      - name: Install TTU
        shell: bash
        run: |
          aws s3 cp "$AWS_S3_PATH_OF_TTU_BUNDLE"/"$TTU_BUNDLE_FILE_NAME" .
          tar -xzf "$TTU_BUNDLE_FILE_NAME"
          cd TeradataToolsAndUtilitiesBase
          sudo ./setup.sh a
        env:
          AWS_ACCESS_KEY_ID: ${{ secrets.AWS_ACCESS_KEY_ID }}
          AWS_SECRET_ACCESS_KEY: ${{ secrets.AWS_SECRET_ACCESS_KEY }}
          AWS_DEFAULT_REGION: ${{ secrets.AWS_REGION }}
          AWS_S3_PATH_OF_TTU_BUNDLE: ${{ secrets.AWS_S3_PATH_OF_TTU_BUNDLE }}
          TTU_BUNDLE_FILE_NAME: ${{ secrets.TTU_BUNDLE_FILE_NAME }}

      - name: Install requirements
        run: |
          python -m pip install --upgrade pip
          pip install -r requirements_dev.txt

      - name: Prepare database
        shell: bash
        run: |
          export PATH=$PATH:"/opt/teradata/client/17.20/bin"

          cd md5_20080530/md5/src

          # prepare bteq script

          cat << EOF > /tmp/install_md5.bteq

          .SET EXITONDELAY ON MAXREQTIME 20
          .logon $DBT_TERADATA_SERVER_NAME/$DBT_TERADATA_USERNAME,$DBT_TERADATA_PASSWORD
          GRANT CREATE FUNCTION ON GLOBAL_FUNCTIONS TO $DBT_TERADATA_USERNAME;
          DATABASE GLOBAL_FUNCTIONS;
          .run file = hash_md5.btq
          GRANT EXECUTE FUNCTION ON GLOBAL_FUNCTIONS TO PUBLIC WITH GRANT OPTION;
          .logoff
          EOF

          bteq < /tmp/install_md5.bteq
          
          cd ../../../tests/functional/adapter
          
          cat << EOF > /tmp/create_procedure_in_tera.bteq
          .SET EXITONDELAY ON MAXREQTIME 20
          .logon $DBT_TERADATA_SERVER_NAME/$DBT_TERADATA_USERNAME,$DBT_TERADATA_PASSWORD
          create user dummy_test_tmode from dbc as permanent=100000000 BYTES,password=dummy_test_tmode;
          GRANT ALL ON dummy_test_tmode to dbc;
          create multiset table dummy_test_tmode.currency_table (amount  DECIMAL(18, 2), original_currency VARCHAR(3), converted_amount DECIMAL(18, 2));
          .COMPILE file = CurrencyConversionProcedureTERA.sql
          .logoff
          EOF
          
          bteq < /tmp/create_procedure_in_tera.bteq
          
          cat << EOF > /tmp/create_procedure_in_ansi.bteq
          .SET SESSION TRANSACTION ANSI;
          .SET EXITONDELAY ON MAXREQTIME 20
          .logon $DBT_TERADATA_SERVER_NAME/$DBT_TERADATA_USERNAME,$DBT_TERADATA_PASSWORD
          .COMPILE file = CurrencyConversionProcedureANSI.sql
          commit;
          .logoff
          EOF
          
          bteq < /tmp/create_procedure_in_ansi.bteq

        env:
          DBT_TERADATA_SERVER_NAME: ${{ steps.create-csae-environments.outputs.teradata-server-name }}
          DBT_TERADATA_USERNAME: dbc
          DBT_TERADATA_PASSWORD: ${{ secrets.CSAE_ENV_PASSWORD }}

      - name: Run pytest tests with ANSI mode
        run: |
          sed -i "s/localhost/$DBT_TERADATA_SERVER_NAME/g" tests/conftest.py
          sed -i "s/username='dbc'/username='$DBT_TERADATA_USERNAME'/g" tests/conftest.py
          sed -i "s/password='dbc'/password='$DBT_TERADATA_PASSWORD'/g" tests/conftest.py
          export DBT_TEST_USER_1=test_grants_user1
          export DBT_TEST_USER_2=test_grants_user2
          export DBT_TEST_USER_3=test_grants_user3
          pytest -v -k "not TestCurrentTimestamp and not TestProcedureANSIInTERA and not TestProcedureTERAInTERA and not Test_query_band" tests/functional
        env:
          DBT_TERADATA_SERVER_NAME: ${{ steps.create-csae-environments.outputs.teradata-server-name }}
          DBT_TERADATA_USERNAME: dbc
          DBT_TERADATA_PASSWORD: ${{ secrets.CSAE_ENV_PASSWORD }}

      - name: Run pytest tests with TERA mode
        run: |
          sed -i "s/localhost/$DBT_TERADATA_SERVER_NAME/g" tests/conftest.py
          sed -i "s/username='dbc'/username='$DBT_TERADATA_USERNAME'/g" tests/conftest.py
          sed -i "s/password='dbc'/password='$DBT_TERADATA_PASSWORD'/g" tests/conftest.py
          sed -i "s/tmode='ANSI'/tmode='$DBT_TERADATA_TMODE'/g" tests/conftest.py
          export DBT_TEST_USER_1=test_grants_user1
          export DBT_TEST_USER_2=test_grants_user2
          export DBT_TEST_USER_3=test_grants_user3
          pytest -v -k "not TestCurrentTimestamp and not TestProcedureTERAInANSI and not TestProcedureANSIInANSI" tests/functional
        env:
          DBT_TERADATA_SERVER_NAME: ${{ steps.create-csae-environments.outputs.teradata-server-name }}
          DBT_TERADATA_USERNAME: dbc
          DBT_TERADATA_PASSWORD: ${{ secrets.CSAE_ENV_PASSWORD }}
          DBT_TERADATA_TMODE: TERA

      - name: Setup environment with ANSI for performance and catalog tests
        run: |
          mkdir ~/.dbt
          cat << EOF > ~/.dbt/profiles.yml
          teradata:
            outputs:
              dbt_perf_test:
                type: teradata
                host: $DBT_TERADATA_SERVER_NAME
                user: $DBT_TERADATA_USERNAME
                password: $DBT_TERADATA_PASSWORD
                logmech: TD2
                schema: dbt_perf_test
                tmode: ANSI
                threads: 1
                timeout_seconds: 300
                priority: interactive
                retries: 1
              dbt_catalog_test:
                type: teradata
                host: $DBT_TERADATA_SERVER_NAME
                user: $DBT_TERADATA_USERNAME
                password: $DBT_TERADATA_PASSWORD
                logmech: TD2
                schema: dbt_catalog_test
                tmode: ANSI
                threads: 1
                timeout_seconds: 300
                priority: interactive
                retries: 1
              dbt_catalog_test_qvci:
                type: teradata
                host: $DBT_TERADATA_SERVER_NAME_QVCI
                user: $DBT_TERADATA_USERNAME_QVCI
                password: $DBT_TERADATA_PASSWORD_QVCI
                logmech: TD2
                schema: dbt_catalog_test
                tmode: ANSI
                threads: 1
                timeout_seconds: 300
                priority: interactive
                retries: 1
          EOF
        env:
          DBT_TERADATA_SERVER_NAME: ${{ steps.create-csae-environments.outputs.teradata-server-name }}
          DBT_TERADATA_USERNAME: dbc
          DBT_TERADATA_PASSWORD: ${{ secrets.CSAE_ENV_PASSWORD }}
          DBT_TERADATA_SERVER_NAME_QVCI: ${{ steps.create-csae-environments.outputs.teradata-server-name-qvci }}
          DBT_TERADATA_USERNAME_QVCI: dbc
          DBT_TERADATA_PASSWORD_QVCI: ${{ secrets.CSAE_ENV_PASSWORD }}

      - name: Run performance tests with ANSI
        run: |
          cd $GITHUB_WORKSPACE/test/performance
          ./run.sh

      - name: Run catalog tests with ANSI
        run: |
          # cd $GITHUB_WORKSPACE/test/catalog/with_qvci
          # ./run.sh
          cd $GITHUB_WORKSPACE/test/catalog/without_qvci
          ./run.sh

      - name: Setup environment with TERA for performance and catalog tests
        run: |
          rm ~/.dbt/profiles.yml
          cat << EOF > ~/.dbt/profiles.yml
          teradata:
            outputs:
              dbt_perf_test:
                type: teradata
                host: $DBT_TERADATA_SERVER_NAME
                user: $DBT_TERADATA_USERNAME
                password: $DBT_TERADATA_PASSWORD
                logmech: TD2
                schema: dbt_perf_test
                tmode: TERA
                threads: 1
                timeout_seconds: 300
                priority: interactive
                retries: 1
              dbt_catalog_test:
                type: teradata
                host: $DBT_TERADATA_SERVER_NAME
                user: $DBT_TERADATA_USERNAME
                password: $DBT_TERADATA_PASSWORD
                logmech: TD2
                schema: dbt_catalog_test
                tmode: TERA
                threads: 1
                timeout_seconds: 300
                priority: interactive
                retries: 1
              dbt_catalog_test_qvci:
                type: teradata
                host: $DBT_TERADATA_SERVER_NAME_QVCI
                user: $DBT_TERADATA_USERNAME_QVCI
                password: $DBT_TERADATA_PASSWORD_QVCI
                logmech: TD2
                schema: dbt_catalog_test
                tmode: TERA
                threads: 1
                timeout_seconds: 300
                priority: interactive
                retries: 1
          EOF
        env:
          DBT_TERADATA_SERVER_NAME: ${{ steps.create-csae-environments.outputs.teradata-server-name }}
          DBT_TERADATA_USERNAME: dbc
          DBT_TERADATA_PASSWORD: ${{ secrets.CSAE_ENV_PASSWORD }}
          DBT_TERADATA_SERVER_NAME_QVCI: ${{ steps.create-csae-environments.outputs.teradata-server-name-qvci }}
          DBT_TERADATA_USERNAME_QVCI: dbc
          DBT_TERADATA_PASSWORD_QVCI: ${{ secrets.CSAE_ENV_PASSWORD }}

      - name: Run performance tests with TERA
        run: |
          cd $GITHUB_WORKSPACE/test/performance
          ./run.sh

      - name: Run catalog tests with TERA
        run: |
          # cd $GITHUB_WORKSPACE/test/catalog/with_qvci
          # ./run.sh
          cd $GITHUB_WORKSPACE/test/catalog/without_qvci
          ./run.sh

      - name: Run nopi tests
        run: |
          sed -i "s/hostname=dbt.*/hostname='$DBT_TERADATA_SERVER_NAME'/g" tests/conftest.py
          sed -i "s/username=demo.*/username='$DBT_TERADATA_USERNAME'/g" tests/conftest.py
          sed -i "s/password=dbt.*/password='$DBT_TERADATA_PASSWORD'/g" tests/conftest.py
          pytest tests/functional/adapter/test_nopi.py
        env:
          DBT_TERADATA_SERVER_NAME: ${{ steps.create-csae-environments.outputs.teradata-server-name }}
          DBT_TERADATA_USERNAME: dbc
          DBT_TERADATA_PASSWORD: ${{ secrets.CSAE_ENV_PASSWORD }}

      - name: Delete CSAE environment
        if: always()
        run: |
          chmod 777 .github/workflows/scripts/deleteTestEnvironments.sh
          ./.github/workflows/scripts/deleteTestEnvironments.sh
        env:
          CSAE_TOKEN: '${{ secrets.CSAE_TOKEN_DEVTOOLS }}'
          CSAE_ENV_NAME: '${{ steps.define-environment-name.outputs.env-name}}'