feat: added gbfs database update #1
Workflow file for this run
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| # Mobility Database Update | ||
| # | ||
| # This GitHub Action manages the Mobility Database by handling both schema and content updates in different scenarios. | ||
| # It's designed to operate under the following conditions: | ||
| # | ||
| # 1. Database Schema Update (Job: db-schema-update): | ||
| # - Triggered by either a 'push' to the main branch or a 'workflow_dispatch' event. | ||
| # - Responsible for updating the database schema using Liquibase. | ||
| # | ||
| # 2. Database Content Update (Job: db-content-update): | ||
| # - Executed on 'repository_dispatch' or 'workflow_dispatch' events. | ||
| # - Focuses on updating the content of the database. | ||
| # - Dependent on the completion of the Database Schema Update job. | ||
| # - Utilizes scripts to install requirements, generate the database model, and populate the database with new content. | ||
| # | ||
| # 3. Update GCP Secrets (Job: update-gcp-secret): | ||
| # - Runs on 'repository_dispatch' or 'workflow_dispatch' events. | ||
| # - Loads secrets from OnePassword. | ||
| # - Dynamically updates GCP secrets based on the environment (dev, qa, prod). | ||
| name: Database Update | ||
| on: | ||
| workflow_call: | ||
| secrets: | ||
| GCP_MOBILITY_FEEDS_SA_KEY: | ||
| description: Service account key | ||
| required: true | ||
| DB_GCP_MOBILITY_FEEDS_SA_KEY: | ||
| description: Service account key where the DB in deployed | ||
| required: true | ||
| DB_USER_NAME: | ||
| description: PostgreSQL User Name | ||
| required: true | ||
| DB_USER_PASSWORD: | ||
| description: PostgreSQL User Password | ||
| required: true | ||
| DB_INSTANCE_NAME: | ||
| description: PostgreSQL Database Instance Name | ||
| required: true | ||
| OP_SERVICE_ACCOUNT_TOKEN: | ||
| description: OnePassword Service Account Token | ||
| required: true | ||
| POSTGRE_SQL_INSTANCE_NAME: | ||
| description: PostgreSQL Instance Name | ||
| required: true | ||
| inputs: | ||
| PROJECT_ID: | ||
| description: GCP Project ID | ||
| required: true | ||
| type: string | ||
| DB_NAME: | ||
| description: PostgreSQL Database Name | ||
| required: true | ||
| type: string | ||
| ENVIRONMENT: | ||
| description: GCP ENVIRONMENT | ||
| required: true | ||
| type: string | ||
| DB_ENVIRONMENT: | ||
| description: GCP ENVIRONMENT where DB is deployed. | ||
| required: true | ||
| type: string | ||
| REGION: | ||
| description: GCP region | ||
| required: true | ||
| type: string | ||
| env: | ||
| python_version: '3.11' | ||
| jobs: | ||
| db-schema-update: | ||
| name: 'Database Schema Update' | ||
| permissions: write-all | ||
| runs-on: ubuntu-latest | ||
| steps: | ||
| - name: Checkout code | ||
| uses: actions/checkout@v4 | ||
| - name: Authenticate to Google Cloud QA/PROD | ||
| uses: google-github-actions/auth@v2 | ||
| with: | ||
| credentials_json: ${{ secrets.DB_GCP_MOBILITY_FEEDS_SA_KEY }} | ||
| - name: Google Cloud Setup | ||
| uses: google-github-actions/setup-gcloud@v2 | ||
| - name: Load secrets from 1Password | ||
| uses: 1password/[email protected] | ||
| with: | ||
| export-env: true # Export loaded secrets as environment variables | ||
| env: | ||
| OP_SERVICE_ACCOUNT_TOKEN: ${{ secrets.OP_SERVICE_ACCOUNT_TOKEN }} | ||
| GCP_FEED_SSH_USER: "op://rbiv7rvkkrsdlpcrz3bmv7nmcu/GCP_FEED_SSH_USER/username" | ||
| GCP_FEED_BASTION_NAME: "op://rbiv7rvkkrsdlpcrz3bmv7nmcu/GCP_FEED_BASTION_NAME/username" | ||
| GCP_FEED_BASTION_SSH_KEY: "op://rbiv7rvkkrsdlpcrz3bmv7nmcu/GCP_FEED_BASTION_SSH_KEY/private key" | ||
| - name: Tunnel | ||
| run: | | ||
| mkdir -p ~/.ssh | ||
| echo "${{ env.GCP_FEED_BASTION_SSH_KEY }}" > ~/.ssh/id_rsa | ||
| chmod 600 ~/.ssh/id_rsa | ||
| ./scripts/tunnel-create.sh -project_id ${{ inputs.PROJECT_ID }} -zone ${{ inputs.REGION }}-a -instance ${{ env.GCP_FEED_BASTION_NAME }}-${{ inputs.DB_ENVIRONMENT}} -target_account ${{ env.GCP_FEED_SSH_USER }} -db_instance ${{ secrets.POSTGRE_SQL_INSTANCE_NAME }} | ||
| sleep 10 # Wait for the tunnel to establish | ||
| - name: Test Database Connection Through Tunnel | ||
| run: | | ||
| sudo apt-get update && sudo apt-get install -y postgresql-client | ||
| PGPASSWORD=${{ secrets.DB_USER_PASSWORD }} psql -h localhost -p 5432 -U ${{ secrets.DB_USER_NAME }} -d ${{ inputs.DB_NAME }} -c "SELECT version();" | ||
| - name: Run Liquibase | ||
| run: | | ||
| wget -O- https://repo.liquibase.com/liquibase.asc | gpg --dearmor > liquibase-keyring.gpg && \ | ||
| cat liquibase-keyring.gpg | sudo tee /usr/share/keyrings/liquibase-keyring.gpg > /dev/null && \ | ||
| echo 'deb [trusted=yes arch=amd64 signed-by=/usr/share/keyrings/liquibase-keyring.gpg] https://repo.liquibase.com stable main' | sudo tee /etc/apt/sources.list.d/liquibase.list | ||
| sudo apt-get update | ||
| sudo apt-get install liquibase=4.25.1 | ||
| export LIQUIBASE_CLASSPATH="liquibase" | ||
| export LIQUIBASE_COMMAND_CHANGELOG_FILE="changelog.xml" | ||
| export LIQUIBASE_COMMAND_URL=jdbc:postgresql://localhost:5432/${{ inputs.DB_NAME }} | ||
| export LIQUIBASE_COMMAND_USERNAME=${{ secrets.DB_USER_NAME }} | ||
| export LIQUIBASE_COMMAND_PASSWORD=${{ secrets.DB_USER_PASSWORD }} | ||
| export LIQUIBASE_LOG_LEVEL=FINE | ||
| liquibase update | ||
| db-content-update: | ||
| name: 'Database Content Update' | ||
| permissions: write-all | ||
| runs-on: ubuntu-latest | ||
| needs: db-schema-update | ||
| if: ${{ github.event_name == 'repository_dispatch' || github.event_name == 'workflow_dispatch' }} | ||
| steps: | ||
| - name: Checkout code | ||
| uses: actions/checkout@v4 | ||
| - name: Setup python | ||
| uses: actions/setup-python@v4 | ||
| with: | ||
| python-version: ${{ env.python_version }} | ||
| - name: Authenticate to Google Cloud QA/PROD | ||
| uses: google-github-actions/auth@v2 | ||
| with: | ||
| credentials_json: ${{ secrets.DB_GCP_MOBILITY_FEEDS_SA_KEY }} | ||
| - name: Google Cloud Setup | ||
| uses: google-github-actions/setup-gcloud@v2 | ||
| - name: Update .env file | ||
| run: | | ||
| echo "PGUSER=${{ secrets.DB_USER_NAME }}" > config/.env.local | ||
| echo "POSTGRES_USER=${{ secrets.DB_USER_NAME }}" >> config/.env.local | ||
| echo "POSTGRES_PASSWORD=${{ secrets.DB_USER_PASSWORD }}" >> config/.env.local | ||
| echo "POSTGRES_DB=${{ inputs.DB_NAME }}" >> config/.env.local | ||
| echo "FEEDS_DATABASE_URL=postgresql://${{ secrets.DB_USER_NAME }}:${{ secrets.DB_USER_PASSWORD }}@localhost:5432/${{ inputs.DB_NAME }}" >> config/.env.local | ||
| echo "POSTGRES_PORT=5432" >> config/.env.local | ||
| echo "POSTGRES_HOST=localhost" >> config/.env.local | ||
| echo "ENV=${{ inputs.ENVIRONMENT }}" >> config/.env.local | ||
| cat config/.env.local | ||
| - name: Load secrets from 1Password | ||
| uses: 1password/[email protected] | ||
| with: | ||
| export-env: true # Export loaded secrets as environment variables | ||
| env: | ||
| OP_SERVICE_ACCOUNT_TOKEN: ${{ secrets.OP_SERVICE_ACCOUNT_TOKEN }} | ||
| GCP_FEED_SSH_USER: "op://rbiv7rvkkrsdlpcrz3bmv7nmcu/GCP_FEED_SSH_USER/username" | ||
| GCP_FEED_BASTION_NAME: "op://rbiv7rvkkrsdlpcrz3bmv7nmcu/GCP_FEED_BASTION_NAME/username" | ||
| GCP_FEED_BASTION_SSH_KEY: "op://rbiv7rvkkrsdlpcrz3bmv7nmcu/GCP_FEED_BASTION_SSH_KEY/private key" | ||
| - name: Tunnel | ||
| run: | | ||
| mkdir -p ~/.ssh | ||
| echo "${{ env.GCP_FEED_BASTION_SSH_KEY }}" > ~/.ssh/id_rsa | ||
| chmod 600 ~/.ssh/id_rsa | ||
| ./scripts/tunnel-create.sh -project_id ${{ inputs.PROJECT_ID }} -zone ${{ inputs.REGION }}-a -instance ${{ env.GCP_FEED_BASTION_NAME }}-${{ inputs.DB_ENVIRONMENT}} -target_account ${{ env.GCP_FEED_SSH_USER }} -db_instance ${{ secrets.POSTGRE_SQL_INSTANCE_NAME }} | ||
| sleep 10 # Wait for the tunnel to establish | ||
| - name: Install requirements and generate db model | ||
| run: scripts/db-gen.sh | ||
| - name: Download csv version of the database | ||
| run: wget -O sources.csv https://bit.ly/catalogs-csv | ||
| - name: Get full path of sources.csv | ||
| id: getpath | ||
| run: echo "PATH=$(realpath sources.csv)" >> $GITHUB_OUTPUT | ||
| - name: Download systems.csv | ||
| run: wget -O systems.csv https://raw.githubusercontent.com/MobilityData/gbfs/master/systems.csv | ||
| - name: Get full path of systems.csv | ||
| id: getsyspath | ||
| run: echo "PATH=$(realpath systems.csv)" >> $GITHUB_OUTPUT | ||
| - name: [GTFS] Update Database Content | ||
| run: scripts/populate-db.sh ${{ steps.getpath.outputs.PATH }} > populate.log | ||
| - name: [GBFS] Update Database Content | ||
| run: scripts/populate-db.sh ${{ steps.getsyspath.outputs.PATH }} gbfs >> populate-gbfs.log | ||
| - name: [GTFS] Upload log file for verification | ||
| if: ${{ always() }} | ||
| uses: actions/upload-artifact@v4 | ||
| with: | ||
| name: populate-${{ inputs.ENVIRONMENT }}.log | ||
| path: populate.log | ||
| - name: [GBFS] Upload log file for verification | ||
| if: ${{ always() }} | ||
| uses: actions/upload-artifact@v4 | ||
| with: | ||
| name: populate-gbfs-${{ inputs.ENVIRONMENT }}.log | ||
| update-gcp-secret: | ||
| name: Update GCP Secrets | ||
| if: ${{ github.event_name == 'repository_dispatch' || github.event_name == 'workflow_dispatch' }} | ||
| runs-on: ubuntu-latest | ||
| steps: | ||
| - name: Authenticate to Google Cloud QA/PROD | ||
| uses: google-github-actions/auth@v2 | ||
| with: | ||
| credentials_json: ${{ secrets.GCP_MOBILITY_FEEDS_SA_KEY }} | ||
| - name: Google Cloud Setup | ||
| uses: google-github-actions/setup-gcloud@v2 | ||
| - name: Load secrets from 1Password | ||
| id: onepw_secrets | ||
| uses: 1password/[email protected] | ||
| with: | ||
| export-env: true # Export loaded secrets as environment variables | ||
| env: | ||
| OP_SERVICE_ACCOUNT_TOKEN: ${{ secrets.OP_SERVICE_ACCOUNT_TOKEN }} | ||
| CREDENTIALS: "op://rbiv7rvkkrsdlpcrz3bmv7nmcu/2mtq7eqbhxv3o25yerdbz4scse/credential" | ||
| - name: Create or Update Auth Secret | ||
| env: | ||
| PROJECT_ID: ${{ inputs.PROJECT_ID }} | ||
| ENVIRONMENT: ${{ inputs.ENVIRONMENT }} | ||
| SECRET_VALUE: ${{ env.CREDENTIALS }} | ||
| SECRET_NAME: FEEDS_CREDENTIALS | ||
| run: | | ||
| echo "Processing secret $SECRET_NAME in project $PROJECT_ID..." | ||
| if gcloud secrets describe $SECRET_NAME --project=$PROJECT_ID; then | ||
| echo "Secret $SECRET_NAME already exists in project $PROJECT_ID, updating..." | ||
| echo -n "$SECRET_VALUE" | gcloud secrets versions add $SECRET_NAME --data-file=- --project=$PROJECT_ID | ||
| else | ||
| echo "Secret $SECRET_NAME does not exist in project $PROJECT_ID, creating..." | ||
| echo -n "$SECRET_VALUE" | gcloud secrets create $SECRET_NAME --data-file=- --replication-policy="automatic" --project=$PROJECT_ID | ||
| fi | ||
