action.yml

name: 'Check DB Migration'
description: 'Check the migration files if there is any potential lock to the tables on MySQL DB.'
author: 'Mahmoud Almasri'
inputs:
  FILE_LOCATION:
    required: false
    default: "src/migrations/"
    description: "The location of migration files"
  GUIDE_DOC:
    required: false
    default: "https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-operations.html"
    description: "Any documentation that you need to reference here."
  LANGUAGE:
    required: false
    default: "NODEJS"
    description: "The programing language, we support now PHP and Nodejs"
  GITHUB_TOKEN:
    description: "Your Github token"
    required: true
outputs:
  NEW_MIGRATIONS:
    description: "The list of the new migration files"
    value: ${{steps.list_migrations.outputs.new_migrations}}

runs:
  using: "composite"
  steps:
    - name: Checkout code
      uses: actions/checkout@v4

    - name: List new migration files using GitHub API
      id: list_migrations
      uses: actions/github-script@v6
      with:
        github-token: ${{ inputs.GITHUB_TOKEN }}
        script: |
          const prNumber = context.payload.pull_request.number;
          const { data: files } = await github.rest.pulls.listFiles({
            owner: context.repo.owner,
            repo: context.repo.repo,
            pull_number: prNumber,
          });

          const newMigrations = files
            .filter(file => file.status === 'added' && file.filename.startsWith('${{ inputs.FILE_LOCATION }}'))
            .map(file => file.filename)
            .join('\n');

          core.setOutput("new_migrations", newMigrations);

    - name: Save migration file list
      run: echo "${{ steps.list_migrations.outputs.new_migrations }}" > new_migrations.txt
      shell: bash

    - name: Display migration file list
      run: cat new_migrations.txt
      shell: bash

    - name: Extract SQL from migration files
      run: |
        touch extracted_sql.txt
        echo "LANGUAGE is: ${{ inputs.LANGUAGE }}"
        # Loop through each new migration file and extract lines with SQL statements
        if [[ "${{ inputs.LANGUAGE }}" == "NODEJS" ]]; then
          echo "Processing NODEJS files"
          while IFS= read -r file; do
            echo "Processing file: $file"
            if [ -f "$file" ]; then
              awk '/queryRunner\.query/,/);/' "$file" | sed -E 's/.*queryRunner\.query\((.*)\);.*/\1/' >> extracted_sql.txt || echo "Failed to process $file"
            else
              echo "File not found: $file"
            fi
          done < new_migrations.txt
        else
          echo "Processing Non-NODEJS files"
          while IFS= read -r file; do
            echo "Processing file: $file"
            if [ -f "$file" ]; then
              awk '/->addSql/,/);/' "$file" | sed 's/.*->addSql(\"\(.*\)\".*/\1/' >> extracted_sql.txt || echo "Failed to process $file"
            else
              echo "File not found: $file"
            fi
          done < new_migrations.txt
        fi
      shell: bash

    - name: Check for potential table locks
      run: |
        touch potential_locks.txt
        echo "Checking for potential table locks"
        echo "Extracted SQL statements:"
        cat extracted_sql.txt
        echo "Starting to check each SQL statement:"
        # Check each SQL statement for potential table locks
        while IFS= read -r sql; do
          echo "Checking SQL: $sql"
          if echo "$sql" | grep -iqE 'update|insert|delete|alter|create|drop|truncate'; then
            echo "Potential lock detected: $sql"
            echo "$sql" >> potential_locks.txt
          fi
        done < extracted_sql.txt
        echo "Extracted SQL statements:"
        cat extracted_sql.txt
        echo "Potential locks detected:"
        cat potential_locks.txt
        found_locks=false
        if [ -s potential_locks.txt ]; then
          echo "Potential table locks detected:"
          cat potential_locks.txt
          found_locks=true
        else
          echo "No potential table locks detected."
        fi
        echo "found_locks=$found_locks" >> $GITHUB_ENV
      shell: bash


    - name: Display Potential Locks
      run: |
        found_locks=false
        if [ -s potential_locks.txt ]; then
          echo "Potential table locks detected:"
          cat potential_locks.txt
          found_locks=true
        else
          echo "No potential table locks detected."
        fi
        echo "found_locks=$found_locks" >> $GITHUB_ENV
      shell: bash

    - name: Add "DB-Lock" label if potential table locks are found
      if: env.found_locks == 'true'
      uses: actions/github-script@v6
      with:
        github-token: ${{ inputs.GITHUB_TOKEN }}
        script: |
          const prNumber = context.payload.pull_request.number;
          await github.rest.issues.addLabels({
            owner: context.repo.owner,
            repo: context.repo.repo,
            issue_number: prNumber,
            labels: ['DB-Lock']
          });

    - name: Comment on PR with potential table locks
      if: env.found_locks == 'true'
      uses: actions/github-script@v6
      with:
        github-token: ${{ inputs.GITHUB_TOKEN }}
        script: |
          const prNumber = context.payload.pull_request.number;
          const fs = require('fs');
          const potentialLocks = fs.readFileSync('potential_locks.txt', 'utf8');
          const commentIdentifier = "<!-- db-lock-comment -->";
          const commentBody = `${commentIdentifier}\n### Potential Table Locks Detected\nThe following SQL statements in the migration files might lock the tables:\n\`\`\`\n${potentialLocks}\n\`\`\`\nFor more information on how to avoid DB locks, please refer to [this guide](${{ inputs.GUIDE_DOC }}).`;
          const { data: comments } = await github.rest.issues.listComments({
            owner: context.repo.owner,
            repo: context.repo.repo,
            issue_number: prNumber,
          });
          const existingComment = comments.find(comment => comment.body.includes(commentIdentifier));

          if (existingComment) {
            await github.rest.issues.updateComment({
              owner: context.repo.owner,
              repo: context.repo.repo,
              comment_id: existingComment.id,
              body: commentBody,
            });
          } else {
            await github.rest.issues.createComment({
              owner: context.repo.owner,
              repo: context.repo.repo,
              issue_number: prNumber,
              body: commentBody,
            });
          }

    - name: Make the check status fail
      if: env.found_locks == 'true'
      run: |
        exit 1
      shell: bash

branding:
  icon: 'database'
  color: 'purple'