Export JSON/XML/YAML/CSV/MYSQL/PSQL/SQLITE #186
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
| name: Export JSON/XML/YAML/CSV/PSQL | |
| on: | |
| push: | |
| branches: | |
| - master | |
| paths-ignore: | |
| - "**" | |
| - "!scripts/export**" | |
| workflow_dispatch: | |
| inputs: | |
| pass: | |
| description: "Passcode" | |
| required: true | |
| jobs: | |
| export_json_xml: | |
| name: JSON/XML/YAML/CSV/PSQL | |
| runs-on: ubuntu-latest | |
| strategy: | |
| fail-fast: false | |
| steps: | |
| - name: Checkout | |
| uses: actions/checkout@v2 | |
| with: | |
| ref: ${{ github.head_ref }} | |
| - name: Setup PHP | |
| uses: shivammathur/setup-php@v2 | |
| with: | |
| php-version: "8.1" | |
| extensions: intl #optional | |
| ini-values: "post_max_size=256M" #optional | |
| - name: Setup tools | |
| run: | | |
| sudo sh -c 'echo "deb https://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list' | |
| wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add - | |
| sudo apt-get update | |
| sudo apt-get install -y pgloader | |
| sudo apt install postgresql-16 -y | |
| - name: Setup MySQL | |
| uses: shogo82148/actions-setup-mysql@v1 | |
| with: | |
| mysql-version: "5.7" | |
| root-password: root | |
| - name: Setup PostgreSQL | |
| uses: harmon758/postgresql-action@v1 | |
| with: | |
| postgresql version: "16" | |
| postgresql db: world | |
| postgresql user: root | |
| postgresql password: root | |
| - name: Add clean commands to world.sql | |
| run: | | |
| grep -v "DROP TABLE" sql/world.sql > tmpfile && mv tmpfile sql/world.sql | |
| echo -e "DROP TABLE IF EXISTS \`regions\`;\n\n$(cat sql/world.sql)" > sql/world.sql | |
| echo -e "DROP TABLE IF EXISTS \`subregions\`;\n$(cat sql/world.sql)" > sql/world.sql | |
| echo -e "DROP TABLE IF EXISTS \`countries\`;\n$(cat sql/world.sql)" > sql/world.sql | |
| echo -e "DROP TABLE IF EXISTS \`states\`;\n$(cat sql/world.sql)" > sql/world.sql | |
| echo -e "DROP TABLE IF EXISTS \`cities\`;\n$(cat sql/world.sql)" > sql/world.sql | |
| - name: Import data to MySQL and migrate to PostgreSQL | |
| run: | | |
| mysql -uroot -proot -e "CREATE DATABASE world;" | |
| mysql -uroot -proot --default-character-set=utf8mb4 world < sql/world.sql | |
| pgloader --version | |
| pgloader .github/my.load | |
| - name: Setup variables | |
| run: | | |
| echo "region_count=$(mysql -uroot -proot -e 'SELECT COUNT(*) FROM world.regions;' -s)" >> $GITHUB_ENV | |
| echo "subregion_count=$(mysql -uroot -proot -e 'SELECT COUNT(*) FROM world.subregions;' -s)" >> $GITHUB_ENV | |
| echo "country_count=$(mysql -uroot -proot -e 'SELECT COUNT(*) FROM world.countries;' -s)" >> $GITHUB_ENV | |
| echo "state_count=$(mysql -uroot -proot -e 'SELECT COUNT(*) FROM world.states;' -s)" >> $GITHUB_ENV | |
| echo "city_count=$(mysql -uroot -proot -e 'SELECT COUNT(*) FROM world.cities;' -s)" >> $GITHUB_ENV | |
| echo "current_date=$(date +'%dth %b %Y')" >> $GITHUB_ENV | |
| - name: Composer Dependencies | |
| run: | | |
| cd scripts/vendor | |
| composer install | |
| - name: Export JSON/XML/YAML/CSV/PSQL | |
| run: | | |
| php scripts/export.php | |
| php scripts/export_xml.php | |
| php scripts/export_yaml.php | |
| php scripts/export_csv.php | |
| mysqldump -uroot -proot --add-drop-table --disable-keys --set-charset --skip-add-locks world regions > sql/regions.sql | |
| mysqldump -uroot -proot --add-drop-table --disable-keys --set-charset --skip-add-locks world subregions > sql/subregions.sql | |
| mysqldump -uroot -proot --add-drop-table --disable-keys --set-charset --skip-add-locks world countries > sql/countries.sql | |
| mysqldump -uroot -proot --add-drop-table --disable-keys --set-charset --skip-add-locks world states > sql/states.sql | |
| mysqldump -uroot -proot --add-drop-table --disable-keys --set-charset --skip-add-locks world cities > sql/cities.sql | |
| pg_dump --dbname=postgresql://root:root@localhost/world -Fp --clean --rows-per-insert $region_count -t public.regions > psql/regions.sql | |
| pg_dump --dbname=postgresql://root:root@localhost/world -Fp --clean --rows-per-insert $subregion_count -t public.subregions > psql/subregions.sql | |
| pg_dump --dbname=postgresql://root:root@localhost/world -Fp --clean --rows-per-insert $country_count -t public.countries > psql/countries.sql | |
| pg_dump --dbname=postgresql://root:root@localhost/world -Fp --clean --rows-per-insert $state_count -t public.states > psql/states.sql | |
| pg_dump --dbname=postgresql://root:root@localhost/world -Fp --clean --rows-per-insert $city_count -t public.cities > psql/cities.sql | |
| pg_dump --dbname=postgresql://root:root@localhost/world -Fp --clean --rows-per-insert 10000 -t public.regions -t public.subregions -t public.countries -t public.states -t public.cities > psql/world.sql | |
| - name: Update README.md | |
| run: | | |
| sed -i "s/Total Regions : [0-9]* <br>/Total Regions : $region_count <br>/" README.md | |
| sed -i "s/Total Sub Regions : [0-9]* <br>/Total Sub Regions : $subregion_count <br>/" README.md | |
| sed -i "s/Total Countries : [0-9]* <br>/Total Countries : $country_count <br>/" README.md | |
| sed -i "s/Total States\/Regions\/Municipalities : [0-9]* <br>/Total States\/Regions\/Municipalities : $state_count <br>/" README.md | |
| sed -i "s/Total Cities\/Towns\/Districts : [0-9]* <br>/Total Cities\/Towns\/Districts : $city_count <br>/" README.md | |
| sed -i "s/Last Updated On : .*$/Last Updated On : $current_date/" README.md | |
| - name: Create Pull Request | |
| uses: peter-evans/create-pull-request@v3 | |
| with: | |
| commit-message: Add exported JSON, CSV, XML & YAML & PSQL files | |
| committer: Darshan Gada <[email protected]> | |
| author: ${{ github.actor }} <${{ github.actor }}@users.noreply.github.com> | |
| signoff: true | |
| branch: export/Files | |
| delete-branch: true | |
| title: "(chore): Export JSON, CSV, XML & YAML & PSQL files" | |
| labels: | | |
| exports | |
| automated | |
| reviewers: dr5hn |