db_check.yml

# this runner include the check only, only runs for pull_request
name: vmangos Development DB Check

on:
  pull_request:
    paths:
      - '**.sql'
      - '.github/workflows/db_check.yml'
env:
  REPO_DIR : ${{github.workspace}}
  WORLD_DB : "world_full_14_june_2021"
jobs:
  build:
    runs-on: ubuntu-latest # 18.04 has mysql5.7 and latest(20.04) has mysql8

    # ... some other config ...

    steps:

    - name: Shutdown Ubuntu MySQL (SUDO)
      run: sudo service mysql stop # Shutdown the Default MySQL, "sudo" is necessary, please not remove it

    - name: Start docker mysql
      run: |
        sudo docker pull mysql
        sudo docker run --name mysqldb -p 3306:3306 -e MYSQL_ROOT_PASSWORD=root -d mysql:5.6 --max_allowed_packet=128M
        sudo docker start mysqldb
        until nc -z localhost 3306 ; do sleep 5 ; echo "Waiting for database availability..." ; done


    - name: Checkout update sql
      uses: actions/checkout@v4

    - name: Checkout base sql
      run: |
        cd ${{github.workspace}}
        git clone https://github.com/brotalnia/database
        pwd
        7z e -y ${{github.workspace}}/database/${{env.WORLD_DB}}.7z
        ls
    - name: Create tables
      run: |
        docker exec mysqldb sh -c 'exec mysql -u root -proot -e "CREATE DATABASE IF NOT EXISTS realmd DEFAULT CHARSET utf8 COLLATE utf8_general_ci;"'
        docker exec mysqldb sh -c 'exec mysql -u root -proot -e "CREATE DATABASE IF NOT EXISTS characters DEFAULT CHARSET utf8 COLLATE utf8_general_ci;"'
        docker exec mysqldb sh -c 'exec mysql -u root -proot -e "CREATE DATABASE IF NOT EXISTS mangos DEFAULT CHARSET utf8 COLLATE utf8_general_ci;"'
        docker exec mysqldb sh -c 'exec mysql -u root -proot -e "CREATE DATABASE IF NOT EXISTS logs DEFAULT CHARSET utf8 COLLATE utf8_general_ci;"'
    - name: Import data
      run: |
        docker exec -i mysqldb sh -c 'exec mysql -u root -proot realmd' < ${{github.workspace}}/sql/logon.sql
        docker exec -i mysqldb sh -c 'exec mysql -u root -proot logs' < ${{github.workspace}}/sql/logs.sql
        docker exec -i mysqldb sh -c 'exec mysql -u root -proot mangos' < ${{github.workspace}}/${{env.WORLD_DB}}.sql
        docker exec -i mysqldb sh -c 'exec mysql -u root -proot characters' < ${{github.workspace}}/sql/characters.sql

    - name: Apply Migration
      run: |
        cd ${{github.workspace}}/sql/migrations/ && chmod +x merge.sh && ./merge.sh
        [ -f "${{github.workspace}}/sql/migrations/world_db_updates.sql" ] && docker exec -i mysqldb sh -c 'exec mysql -u root -proot mangos' < ${{github.workspace}}/sql/migrations/world_db_updates.sql
        [ -f "${{github.workspace}}/sql/migrations/characters_db_updates.sql" ] && docker exec -i mysqldb sh -c 'exec mysql -u root -proot characters' < ${{github.workspace}}/sql/migrations/characters_db_updates.sql
        [ -f "${{github.workspace}}/sql/migrations/logon_db_updates.sql" ] && docker exec -i mysqldb sh -c 'exec mysql -u root -proot realmd' < ${{github.workspace}}/sql/migrations/logon_db_updates.sql
        [ -f "${{github.workspace}}/sql/migrations/logs_db_updates.sql" ] && docker exec -i mysqldb sh -c 'exec mysql -u root -proot logs' < ${{github.workspace}}/sql/migrations/logs_db_updates.sql
    - name: Export Dump
      run: |
        mkdir dbexport
        cd ${{github.workspace}}/dbexport
        mkdir db_dump
        docker exec mysqldb sh -c 'exec mysqldump -u root -proot mangos' > ${{github.workspace}}/dbexport/db_dump/mangos.sql
        docker exec mysqldb sh -c 'exec mysqldump -u root -proot realmd' > ${{github.workspace}}/dbexport/db_dump/logon.sql
        docker exec mysqldb sh -c 'exec mysqldump -u root -proot logs' > ${{github.workspace}}/dbexport/db_dump/logs.sql
        docker exec mysqldb sh -c 'exec mysqldump -u root -proot characters' > ${{github.workspace}}/dbexport/db_dump/characters.sql


        # cd db_dump
        # mkdir update_check_only_do_not_import
        # cp ${{github.workspace}}/sql/migrations/world_db_updates.sql ${{github.workspace}}/dbexport/db_dump/update_check_only_do_not_import/world_db_updates.sql
        # cp ${{github.workspace}}/sql/migrations/logon_db_updates.sql ${{github.workspace}}/dbexport/db_dump/update_check_only_do_not_import/logon_db_updates.sql
        # cp ${{github.workspace}}/sql/migrations/logs_db_updates.sql ${{github.workspace}}/dbexport/db_dump/update_check_only_do_not_import/logs_db_updates.sql
        # cp ${{github.workspace}}/sql/migrations/characters_db_updates.sql ${{github.workspace}}/dbexport/db_dump/update_check_only_do_not_import/characters_db_updates.sql

    - name: Create New tables
      run: |
        docker exec mysqldb sh -c 'exec mysql -u root -proot -e "CREATE DATABASE IF NOT EXISTS realmd2 DEFAULT CHARSET utf8 COLLATE utf8_general_ci;"'
        docker exec mysqldb sh -c 'exec mysql -u root -proot -e "CREATE DATABASE IF NOT EXISTS characters2 DEFAULT CHARSET utf8 COLLATE utf8_general_ci;"'
        docker exec mysqldb sh -c 'exec mysql -u root -proot -e "CREATE DATABASE IF NOT EXISTS mangos2 DEFAULT CHARSET utf8 COLLATE utf8_general_ci;"'
        docker exec mysqldb sh -c 'exec mysql -u root -proot -e "CREATE DATABASE IF NOT EXISTS logs2 DEFAULT CHARSET utf8 COLLATE utf8_general_ci;"'

    - name: Import data back in to verify the dump data
      run: |
        docker exec -i mysqldb sh -c 'exec mysql -u root -proot realmd2' < ${{github.workspace}}/dbexport/db_dump/logon.sql
        docker exec -i mysqldb sh -c 'exec mysql -u root -proot logs2' < ${{github.workspace}}/dbexport/db_dump/logs.sql
        docker exec -i mysqldb sh -c 'exec mysql -u root -proot mangos2' < ${{github.workspace}}/dbexport/db_dump/mangos.sql
        docker exec -i mysqldb sh -c 'exec mysql -u root -proot characters2' < ${{github.workspace}}/dbexport/db_dump/characters.sql