replication-test.yml

name: Docker Replica Mode Test

on:
  push:
    branches: [ "main" ]

jobs:
  test-replication:
    runs-on: ubuntu-latest
    strategy:
      matrix:
        source: ['postgres', 'mysql', 'mariadb', 'dolt']
    steps:
      - uses: actions/checkout@v4

      - name: Install dependencies
        run: |
          # Only install DuckDB for data comparison
          curl -LJO https://github.com/duckdb/duckdb/releases/latest/download/duckdb_cli-linux-amd64.zip
          unzip duckdb_cli-linux-amd64.zip
          chmod +x duckdb
          sudo mv duckdb /usr/local/bin

      - name: Start source ${{ matrix.source }} database
        run: |
          if [ "${{ matrix.source }}" = "mysql" ]; then
            docker run -d --name source-db -p 3306:3306 \
              -e MYSQL_ROOT_PASSWORD=root \
              -e MYSQL_DATABASE=test \
              mysql:lts

          elif [ "${{ matrix.source }}" = "mariadb" ]; then
            docker run -d --name source-db -p 3306:3306 \
              -e MARIADB_ROOT_PASSWORD=root \
              -e MARIADB_DATABASE=test \
              mariadb:latest \
              --gtid-strict-mode=1 \
              --log-bin=mybinlog \
              --binlog-format=ROW

          elif [ "${{ matrix.source }}" = "dolt" ]; then
            # Create Dolt config
            mkdir -p doltcfg
            cat <<EOF > doltcfg/config.json
          {
            "sqlserver.global.enforce_gtid_consistency": "ON",
            "sqlserver.global.gtid_mode": "ON",
            "sqlserver.global.log_bin": "1"
          }
          EOF

            docker run -d --name source-db -p 3306:3306 \
              -v "$(pwd)/doltcfg":/etc/dolt/doltcfg.d/ \
              dolthub/dolt-sql-server:latest \
              -u root -p root

          elif [ "${{ matrix.source }}" = "postgres" ]; then
            docker run -d --name source-db -p 5432:5432 \
              -e POSTGRES_PASSWORD=postgres \
              -e POSTGRES_DB=test \
              postgres:latest \
              -c wal_level=logical
          fi

          # Wait for database to be ready
          if [ "${{ matrix.source }}" = "postgres" ]; then
            until docker exec source-db pg_isready; do
              sleep 1
            done
          elif [ "${{ matrix.source }}" = "dolt" ]; then
            until docker exec source-db dolt sql -q "SELECT 1"; do
              sleep 1
            done
          elif [ "${{ matrix.source }}" = "mariadb" ]; then
            until docker exec source-db mariadb -uroot -proot -e "SELECT 1"; do
              sleep 1
            done
          else
            until docker exec source-db mysql -uroot -proot -e "SELECT 1"; do
              sleep 1
            done
          fi

          # Create test data
          if [ "${{ matrix.source }}" = "postgres" ]; then
            docker exec source-db psql -U postgres test -c "
              CREATE TABLE items (id INT PRIMARY KEY, name VARCHAR(50));
              INSERT INTO items VALUES (1, 'test1'), (2, 'test2');"
          elif [ "${{ matrix.source }}" = "dolt" ]; then
            docker exec source-db dolt sql -q "
              CREATE DATABASE test;
              CREATE TABLE test.items (id INT PRIMARY KEY, name VARCHAR(50));
              INSERT INTO test.items VALUES (1, 'test1'), (2, 'test2');
              CREATE TABLE test.skip (id INT PRIMARY KEY, name VARCHAR(50));
              INSERT INTO test.skip VALUES (1, 'abc'), (2, 'def');"
          elif [ "${{ matrix.source }}" = "mariadb" ]; then
            docker exec source-db mariadb -uroot -proot test -e "
              CREATE TABLE items (id INT PRIMARY KEY, name VARCHAR(50));
              INSERT INTO items VALUES (1, 'test1'), (2, 'test2');
              CREATE TABLE skip (id INT PRIMARY KEY, name VARCHAR(50));
              INSERT INTO skip VALUES (1, 'abc'), (2, 'def');"
          else
            docker exec source-db mysql -uroot -proot test -e "
              CREATE TABLE items (id INT PRIMARY KEY, name VARCHAR(50));
              INSERT INTO items VALUES (1, 'test1'), (2, 'test2');
              CREATE TABLE skip (id INT PRIMARY KEY, name VARCHAR(50));
              INSERT INTO skip VALUES (1, 'abc'), (2, 'def');"
          fi

      - name: Start MyDuck Server in replica mode
        run: |
          if [ "${{ matrix.source }}" = "postgres" ]; then
            SOURCE_DSN="postgres://postgres:postgres@host.docker.internal:5432/test"
          else
            SOURCE_DSN="mysql://root:root@host.docker.internal:3306/test?skip-tables=test.skip"
          fi

          docker run -d --name myduck \
            --add-host=host.docker.internal:host-gateway \
            -p 13306:3306 \
            -p 15432:5432 \
            --env=SETUP_MODE=REPLICA \
            --env=SOURCE_DSN="$SOURCE_DSN" \
            apecloud/myduckserver:latest

          # Wait and check container status
          for i in {1..15}; do
            if ! docker ps | grep -q myduck; then
              echo "MyDuck container exited unexpectedly"
              docker logs myduck
              exit 1
            fi
            sleep 1
          done

      - name: Verify initial replication
        run: |
          # Query source data
          SCHEMA=""
          if [ "${{ matrix.source }}" = "postgres" ]; then
            docker exec source-db psql -U postgres -h 127.0.0.1 test \
              -c "\COPY (SELECT * FROM items ORDER BY id) TO STDOUT WITH CSV;" | tee source_data.csv
            SCHEMA=public
          elif [ "${{ matrix.source }}" = "dolt" ]; then
            docker exec source-db dolt sql --result-format csv -q "SELECT * FROM test.items ORDER BY id" | tee source_data.csv
            SCHEMA=test
          elif [ "${{ matrix.source }}" = "mariadb" ]; then
            docker exec source-db mariadb -uroot -proot test \
              -e "SELECT * FROM items ORDER BY id;" | tee source_data.csv
            SCHEMA=test
          else
            docker exec source-db mysql -uroot -proot test \
              -e "SELECT * FROM items ORDER BY id;" | tee source_data.csv
            SCHEMA=test
          fi

          # Query MyDuck data
          docker exec myduck psql -U postgres -h 127.0.0.1 \
            -c "\COPY (SELECT * FROM ${SCHEMA}.items ORDER BY id) TO STDOUT WITH CSV;" | tee myduck_data.csv

          # Compare data using DuckDB
          duckdb --csv -c "
            CREATE TABLE source AS FROM 'source_data.csv';
            CREATE TABLE myduck AS FROM 'myduck_data.csv';
            SELECT COUNT(*) FROM (
              SELECT * FROM source EXCEPT SELECT * FROM myduck
            ) diff;" | tail -n 1 | tee diff_count.txt

          # Verify no differences
          if grep -q '^0$' diff_count.txt; then
            echo 'Initial replication verification successful'
          else
            echo 'Initial replication verification failed'
            exit 1
          fi

      - name: Test replication of new data
        run: |
          # Insert new data in source
          SCHEMA=""
          if [ "${{ matrix.source }}" = "postgres" ]; then
            docker exec source-db psql -U postgres test \
              -c "INSERT INTO items VALUES (3, 'test3');"
            SCHEMA=public
          elif [ "${{ matrix.source }}" = "dolt" ]; then
            docker exec source-db dolt sql -q "INSERT INTO test.items VALUES (3, 'test3');"
            SCHEMA=test
          elif [ "${{ matrix.source }}" = "mariadb" ]; then
            docker exec source-db mariadb -uroot -proot test \
              -e "INSERT INTO items VALUES (3, 'test3');"
            SCHEMA=test
          else
            docker exec source-db mysql -uroot -proot test \
              -e "INSERT INTO items VALUES (3, 'test3');"
            SCHEMA=test
          fi

          # Wait for replication
          sleep 10

          # Verify new data was replicated
          docker exec myduck psql -t -U postgres -h 127.0.0.1 -c \
            "SELECT COUNT(*) FROM ${SCHEMA}.items WHERE id = 3;" | tr -d ' ' | tee count.txt

          if grep -q '^1$' count.txt; then
            echo 'Replication of new data verified successfully'
          else
            echo 'Replication of new data verification failed'
            exit 1
          fi

          # Print the logs
          docker logs myduck

      - name: Verify skip tables
        run: |
          # Verify skipped table is empty (for MySQL-compatible databases only)
          if [ "${{ matrix.source }}" != "postgres" ]; then
            # Check if skip table exists and has any rows
            TABLE_EXISTS=$(docker exec myduck psql -t -U postgres -h 127.0.0.1 -c \
              "SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = '${SCHEMA}' AND table_name = 'skip';" | tr -d ' ')

            if [ "$TABLE_EXISTS" -ne "0" ]; then
              COUNT=$(docker exec myduck psql -t -U postgres -h 127.0.0.1 -c \
                "SELECT COUNT(*) FROM ${SCHEMA}.skip;" | tr -d ' ')
              if [ "$COUNT" -eq "0" ]; then
                echo "Successfully verified that skipped table exists but is empty"
              else
                echo "Error: Skipped table 'skip' contains $COUNT rows when it should be empty"
                exit 1
              fi
            else
              echo "Successfully verified that skipped table does not exist in destination"
            fi
          fi

      - name: Cleanup
        if: always()
        run: |
          docker rm -f source-db myduck || true