Export JSON/XML/YAML/CSV/MYSQL/PSQL/SQLITE #209
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/MYSQL/PSQL/SQLITE | |
| 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/MYSQL/PSQL/SQLITE | |
| runs-on: ubuntu-latest | |
| strategy: | |
| matrix: | |
| php-version: [8.1] | |
| node-version: [20.x] | |
| fail-fast: false | |
| steps: | |
| - name: Checkout | |
| uses: actions/checkout@v4 | |
| with: | |
| submodules: true | |
| ref: ${{ github.head_ref }} | |
| - name: Setup PHP | |
| uses: shivammathur/setup-php@v2 | |
| with: | |
| php-version: ${{ matrix.php-version }} | |
| extensions: intl #optional | |
| coverage: none | |
| ini-values: "post_max_size=256M" #optional | |
| - name: Use Node.js ${{ matrix.node-version }} | |
| uses: actions/setup-node@v4 | |
| with: | |
| node-version: ${{ matrix.node-version }} | |
| - name: Start MySQL service | |
| run: | | |
| sudo systemctl start mysql.service | |
| mysql -V | |
| - name: Start PostgreSQL service | |
| run: | | |
| sudo systemctl start postgresql.service | |
| pg_isready | |
| pg_lsclusters | |
| sudo -u postgres psql -c "CREATE DATABASE world;" | |
| sudo -u postgres psql -c "ALTER USER postgres PASSWORD 'postgres';" | |
| sudo -u postgres psql -c "\l" | |
| - 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: Setup MySQL DB | |
| run: | | |
| ls -R | |
| mysql -uroot -proot -e "CREATE DATABASE world;" | |
| mysql -uroot -proot -e "SHOW DATABASES;" | |
| mysql -uroot -proot --default-character-set=utf8mb4 world < sql/world.sql | |
| - name: Setup & Run NMIG (MySQL to PostgreSQL) | |
| run: | | |
| cp nmig.config.json nmig/config/config.json | |
| cd nmig | |
| npm install | |
| npm run build | |
| npm start | |
| - name: Setup MySQLtoSQLite | |
| run: | | |
| python -m pip install --upgrade pip | |
| pip install mysql-to-sqlite3 | |
| mysql2sqlite --version | |
| - 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 | |
| run: | | |
| php scripts/export.php | |
| - name: Export XML | |
| run: | | |
| php scripts/export_xml.php | |
| - name: Export YAML | |
| run: | | |
| php scripts/export_yaml.php | |
| - name: Export CSV | |
| run: | | |
| php scripts/export_csv.php | |
| - name: Export MySQL SQL | |
| run: | | |
| 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 | |
| - name: Export PostgreSQL SQL | |
| env: | |
| PGPASSWORD: postgres | |
| run: | | |
| pg_dump --dbname=postgresql://postgres:postgres@localhost/world -Fp --inserts --clean -t regions > psql/regions.sql | |
| pg_dump --dbname=postgresql://postgres:postgres@localhost/world -Fp --inserts --clean -t subregions > psql/subregions.sql | |
| pg_dump --dbname=postgresql://postgres:postgres@localhost/world -Fp --inserts --clean -t countries > psql/countries.sql | |
| pg_dump --dbname=postgresql://postgres:postgres@localhost/world -Fp --inserts --clean -t states > psql/states.sql | |
| pg_dump --dbname=postgresql://postgres:postgres@localhost/world -Fp --inserts --clean -t cities > psql/cities.sql | |
| pg_dump --dbname=postgresql://postgres:postgres@localhost/world -Fp --inserts --clean > psql/world.sql | |
| - name: Export SQLite | |
| run: | | |
| mysql2sqlite -d world -t regions --mysql-password root -u root -f sqlite/regions.sqlite3 | |
| mysql2sqlite -d world -t subregions --mysql-password root -u root -f sqlite/subregions.sqlite3 | |
| mysql2sqlite -d world -t countries --mysql-password root -u root -f sqlite/countries.sqlite3 | |
| mysql2sqlite -d world -t states --mysql-password root -u root -f sqlite/states.sqlite3 | |
| mysql2sqlite -d world -t cities --mysql-password root -u root -f sqlite/cities.sqlite3 | |
| mysql2sqlite -d world -t countries states --mysql-password root -u root -f sqlite/countries+states.sqlite3 | |
| mysql2sqlite -d world -t countries cities --mysql-password root -u root -f sqlite/countries+cities.sqlite3 | |
| mysql2sqlite -d world -t states cities --mysql-password root -u root -f sqlite/states+cities.sqlite3 | |
| mysql2sqlite -d world --mysql-password root -u root -f sqlite/world.sqlite3 | |
| - 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@v6 | |
| with: | |
| commit-message: Add exported JSON, CSV, XML, YAML, MYSQL, PSQL & SQLITE files | |
| committer: Darshan Gada <gadadarshan@gmail.com> | |
| signoff: true | |
| branch: export/Files | |
| delete-branch: true | |
| title: "(chore): Export JSON, CSV, XML, YAML, MYSQL, PSQL & SQLITE files" | |
| labels: | | |
| exports | |
| automated | |
| reviewers: dr5hn |