ci-integration-tests.yml

name: CI Integration Tests
on:
  workflow_dispatch:

jobs:
  test:
    strategy:
      matrix:
        python: ['3.8', '3.9', '3.10', '3.11']
    runs-on: macos-12
    name: Functional test
    steps:
      - name: Setup Python
        uses: actions/setup-python@v2
        with:
          python-version: ${{ matrix.python }}
      - name: Check out repository code
        uses: actions/checkout@v2

      - name: Download Vantage Express
        shell: bash
        run: |
          brew install hudochenkov/sshpass/sshpass
          echo "my IP address is: " $(ipconfig getifaddr en0)
          mkdir /tmp/downloads
          cd /tmp/downloads
          curl -L "https://d289lrf5tw1zls.cloudfront.net/database/teradata-express/VantageExpress17.20_Sles12_20220819081111.7z?Expires=1735689600&Signature=$VE_URL_SIGNATURE&Key-Pair-Id=APKAJ3SWQUPWKYVMO2WQ" \
            --compressed -o ve.7z
        env:
          VE_URL_SIGNATURE: ${{ secrets.VE_URL_SIGNATURE }}

      - name: Unzip Vantage Express
        shell: bash
        run: |
          cd /tmp/downloads
          7z x ve.7z

      - name: Install Vantage Express
        shell: bash
        run: |
          export VM_IMAGE_DIR="/tmp/downloads/VantageExpress17.20_Sles12"
          DEFAULT_VM_NAME="vantage-express"
          VM_NAME="${VM_NAME:-$DEFAULT_VM_NAME}"
          vboxmanage createvm --name "$VM_NAME" --register --ostype openSUSE_64
          vboxmanage modifyvm "$VM_NAME" --ioapic on --memory 6000 --vram 128 --nic1 nat --cpus 3
          vboxmanage storagectl "$VM_NAME" --name "SATA Controller" --add sata --controller IntelAhci
          vboxmanage storageattach "$VM_NAME" --storagectl "SATA Controller" --port 0 --device 0 --type hdd --medium  "$(find $VM_IMAGE_DIR -name '*disk1*')"
          vboxmanage storageattach "$VM_NAME" --storagectl "SATA Controller" --port 1 --device 0 --type hdd --medium  "$(find $VM_IMAGE_DIR -name '*disk2*')"
          vboxmanage storageattach "$VM_NAME" --storagectl "SATA Controller" --port 2 --device 0 --type hdd --medium  "$(find $VM_IMAGE_DIR -name '*disk3*')"
          vboxmanage modifyvm "$VM_NAME" --natpf1 "tdssh,tcp,,4422,,22"
          vboxmanage modifyvm "$VM_NAME" --natpf1 "tddb,tcp,,1025,,1025"
          vboxmanage startvm "$VM_NAME" --type headless

      - name: Install TTU
        shell: bash
        run: |
          curl -L "https://d289lrf5tw1zls.cloudfront.net/tools/ttu/TeradataToolsAndUtilitiesBase__macosx_x86_64.17.20.08.00.tar.gz?Expires=1735689600&Signature=$TTU_URL_SIGNATURE&Key-Pair-Id=APKAJ3SWQUPWKYVMO2WQ" \
            --compressed -o ttu.tar.gz
          tar -xzf ttu.tar.gz

          installer -pkg ./TeradataToolsAndUtilitiesBase/*.pkg -target CurrentUserHomeDirectory
        env:
          TTU_URL_SIGNATURE: ${{ secrets.TTU_URL_SIGNATURE }}

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

          # install timeout command
          export HOMEBREW_NO_INSTALLED_DEPENDENTS_CHECK=yes
          export HOMEBREW_NO_INSTALL_CLEANUP=yes
          export HOMEBREW_NO_INSTALL_UPGRADE=yes
          brew install coreutils jq

      - name: Verify Vantage Express is running
        shell: bash
        run: ./.github/workflows/scripts/verifyVantageIsRunning.sh

      - name: Prepare database
        shell: bash
        run: |          
          export PATH=$PATH:"/Users/runner/Library/Application Support/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 127.0.0.1/dbc,dbc
          CREATE DATABASE GLOBAL_FUNCTIONS AS PERMANENT = 60e6, SPOOL = 120e6;
          GRANT CREATE FUNCTION ON GLOBAL_FUNCTIONS TO dbc;
          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 127.0.0.1/dbc,dbc
          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 127.0.0.1/dbc,dbc
          .COMPILE file = CurrencyConversionProcedureANSI.sql
          commit;
          .logoff
          EOF
          
          bteq < /tmp/create_procedure_in_ansi.bteq

      - name: Run pytest tests with ANSI mode
        run: |
          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" tests/functional
          

      - name: Run pytest tests with TERA mode
        run: |
          export DBT_TEST_USER_1=test_grants_user1
          export DBT_TEST_USER_2=test_grants_user2
          export DBT_TEST_USER_3=test_grants_user3
          sed -i'' -e "s/tmode='ANSI'/tmode='TERA'/g" tests/conftest.py
          pytest -v -k "not TestCurrentTimestamp and not TestProcedureTERAInANSI and not TestProcedureANSIInANSI" tests/functional

#      - name: Run pytest tests with existing database
#        run: |
#          export DBT_TEST_USER_1=test_grants_user1
#          export DBT_TEST_USER_2=test_grants_user2
#          export DBT_TEST_USER_3=test_grants_user3
#          cat << EOF > /tmp/pytestdatabase.bteq
#          .SET EXITONDELAY ON MAXREQTIME 20
#          .logon 127.0.0.1/dbc,dbc
#          CREATE DATABASE DBT_TEST
#            AS PERMANENT = 60e6, SPOOL = 120e6;
#          .logoff
#          EOF
#          # strip the random suffix from the pytest config
#          sed -i'.original' -e "s/_{prefix}//" tests/conftest.py
#
#          pytest tests/functional
      - name: Run performance tests for fastload In ANSI
        run: |
          mkdir ~/.dbt
          cat << EOF > ~/.dbt/profiles.yml
          teradata:
            outputs:
              dbt_perf_test:
                type: teradata
                host: localhost
                user: dbc
                password: dbc
                logmech: TD2
                schema: dbt_perf_test
                tmode: ANSI
                threads: 1
                timeout_seconds: 300
                priority: interactive
                retries: 1
              dbt_catalog_test:
                type: teradata
                host: localhost
                user: dbc
                password: dbc
                logmech: TD2
                schema: dbt_catalog_test
                tmode: ANSI
                threads: 1
                timeout_seconds: 300
                priority: interactive
                retries: 1
              dbt_catalog_test_qvci:
                type: teradata
                host: localhost
                user: dbc
                password: dbc
                logmech: TD2
                schema: dbt_catalog_test
                tmode: ANSI
                threads: 1
                timeout_seconds: 300
                priority: interactive
                retries: 1
          EOF
          cd $GITHUB_WORKSPACE/test/performance
          ./run.sh
      - name: Run catalog tests in ANSI
        run: |
          # set DisableQVCI to false - this will give us data type info for views in DBC.ColumnsJQV
          sshpass -p root ssh -o StrictHostKeyChecking=no -p 4422 root@localhost  "dbscontrol << EOF
          M internal 551=false
          W
          EOF

          tpareset -y Enable QVCI
          "

          $GITHUB_WORKSPACE/.github/workflows/scripts/verifyVantageIsRunning.sh

          cd $GITHUB_WORKSPACE/test/catalog/with_qvci
          ./run.sh
          cd $GITHUB_WORKSPACE/test/catalog/without_qvci
          ./run.sh

      - name: Run performance tests for fastload In TERA
        run: |
          cat << EOF > ~/.dbt/profiles.yml
          teradata:
            outputs:
              dbt_perf_test:
                type: teradata
                host: localhost
                user: dbc
                password: dbc
                logmech: TD2
                schema: dbt_perf_test
                tmode: TERA
                threads: 1
                timeout_seconds: 300
                priority: interactive
                retries: 1
              dbt_catalog_test:
                type: teradata
                host: localhost
                user: dbc
                password: dbc
                logmech: TD2
                schema: dbt_catalog_test
                tmode: TERA
                threads: 1
                timeout_seconds: 300
                priority: interactive
                retries: 1
              dbt_catalog_test_qvci:
                type: teradata
                host: localhost
                user: dbc
                password: dbc
                logmech: TD2
                schema: dbt_catalog_test
                tmode: TERA
                threads: 1
                timeout_seconds: 300
                priority: interactive
                retries: 1
          EOF
          cd $GITHUB_WORKSPACE/test/performance
          ./run.sh
      - name: Run catalog tests in TERA
        run: |
          # set DisableQVCI to false - this will give us data type info for views in DBC.ColumnsJQV
          sshpass -p root ssh -o StrictHostKeyChecking=no -p 4422 root@localhost  "dbscontrol << EOF
          M internal 551=false
          W
          EOF

          tpareset -y Enable QVCI
          "

          $GITHUB_WORKSPACE/.github/workflows/scripts/verifyVantageIsRunning.sh

          cd $GITHUB_WORKSPACE/test/catalog/with_qvci
          ./run.sh
          cd $GITHUB_WORKSPACE/test/catalog/without_qvci
          ./run.sh

      - name: Run nopi tests
        run: |
          sshpass -p root ssh -o StrictHostKeyChecking=no -p 4422 root@localhost  "dbscontrol << EOF
          M general 53=N
          W
          EOF

          tpareset -y Disable PrimaryIndexDefault
          "

          $GITHUB_WORKSPACE/.github/workflows/scripts/verifyVantageIsRunning.sh

          pytest tests/functional/adapter/test_nopi.py
      - uses: actions/upload-artifact@v2
        if: ${{ failure() ||  cancelled() }}
        with:
          name: virtualbox-logs
          path: /Users/runner/VirtualBox*