Export JSON/XML/YAML/CSV/MYSQL/PSQL/SQLITE/SQLSERVER/MONGODB #319
This file contains hidden or 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/SQLSERVER/MONGODB | |
| on: | |
| push: | |
| branches: | |
| - master | |
| paths-ignore: | |
| - "**" | |
| - "!bin/Commands/Export**" | |
| workflow_dispatch: | |
| inputs: | |
| pass: | |
| description: "Passcode" | |
| required: true | |
| jobs: | |
| export: | |
| name: JSON/XML/YAML/CSV/MYSQL/PSQL/SQLITE/SQLSERVER/MONGODB | |
| runs-on: ubuntu-24.04 | |
| strategy: | |
| matrix: | |
| php-version: [8.2] | |
| node-version: [20.x] | |
| fail-fast: false | |
| steps: | |
| - name: Checkout | |
| uses: actions/checkout@v4 | |
| with: | |
| fetch-depth: 1 | |
| submodules: true | |
| ref: ${{ github.head_ref }} | |
| persist-credentials: true | |
| lfs: false | |
| - 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, memory_limit=512M" #optional | |
| - name: Use Node.js ${{ matrix.node-version }} | |
| uses: actions/setup-node@v4 | |
| with: | |
| node-version: ${{ matrix.node-version }} | |
| cache: 'npm' | |
| cache-dependency-path: nmig/package-lock.json | |
| - name: Cache Composer dependencies | |
| uses: actions/cache@v4 | |
| with: | |
| path: bin/vendor | |
| key: ${{ runner.os }}-composer-${{ hashFiles('bin/composer.lock') }} | |
| restore-keys: ${{ runner.os }}-composer- | |
| - name: Start MySQL service | |
| run: | | |
| sudo systemctl start mysql.service | |
| mysql -V | |
| # Wait for MySQL to be ready | |
| while ! mysqladmin ping -h"127.0.0.1" --silent; do | |
| echo "Waiting for MySQL..." | |
| sleep 1 | |
| done | |
| - 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: Setup MongoDB | |
| uses: supercharge/mongodb-github-action@1.10.0 | |
| with: | |
| mongodb-version: '6.0' | |
| mongodb-replica-set: rs0 | |
| - name: Install MongoDB Database Tools | |
| run: | | |
| # Download MongoDB Database Tools directly | |
| wget -q https://fastdl.mongodb.org/tools/db/mongodb-database-tools-ubuntu2204-x86_64-100.7.3.deb | |
| sudo dpkg -i mongodb-database-tools-ubuntu2204-x86_64-100.7.3.deb | |
| rm -rf mongodb-database-tools-ubuntu2204-x86_64-100.7.3.deb | |
| # Verify installation | |
| mongoimport --version | |
| - name: Setup MySQL DB | |
| run: | | |
| echo "🗄️ Creating MySQL database..." | |
| mysql -uroot -proot -e "CREATE DATABASE world CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;" | |
| mysql -uroot -proot -e "SHOW DATABASES;" | |
| echo "✅ Database created" | |
| - name: Initialize Database Schema | |
| run: | | |
| echo "📋 Creating database tables..." | |
| mysql -uroot -proot world < bin/db/schema.sql | |
| echo "✅ Schema initialized - verifying tables..." | |
| mysql -uroot -proot -e "USE world; SHOW TABLES;" | |
| mysql -uroot -proot -e "USE world; SHOW CREATE TABLE regions\G" | head -20 | |
| echo "✅ All tables created successfully" | |
| - name: Setup Python Dependencies | |
| run: | | |
| python -m pip install --upgrade pip | |
| pip install mysql-connector-python | |
| echo "✅ Python dependencies installed" | |
| - name: Import JSON to MySQL | |
| run: | | |
| echo "📥 Importing contributions to MySQL..." | |
| python3 bin/scripts/sync/import_json_to_mysql.py --host localhost --user root --password root --database world | |
| echo "✅ Import complete - IDs auto-assigned by MySQL" | |
| - name: Export MySQL to JSON (Validation) | |
| run: | | |
| echo "🔄 Syncing MySQL back to contributions JSON for validation..." | |
| python3 bin/scripts/sync/sync_mysql_to_json.py --host localhost --user root --password root --database world | |
| echo "✅ MySQL → JSON sync complete" | |
| echo "📊 Validating round-trip consistency..." | |
| # Show what changed (if anything) | |
| git diff --stat contributions/ || echo "No changes detected - perfect round-trip!" | |
| - 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 | |
| cd .. | |
| - name: Setup MySQLtoSQLite | |
| run: | | |
| python -m pip install --upgrade pip | |
| pip install "mysql-to-sqlite3" "sqlglot<30" | |
| 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 +'%B %d, %Y')" >> $GITHUB_ENV | |
| - name: Composer Dependencies | |
| working-directory: ./bin | |
| run: | | |
| composer install | |
| php console list | |
| - name: Export JSON | |
| working-directory: ./bin | |
| run: php console export:json | |
| - name: Convert JSON to GeoJSON | |
| working-directory: ./bin | |
| run: | | |
| python3 scripts/export/json_to_geojson.py | |
| - name: Convert JSON to Toon Format | |
| working-directory: ./bin | |
| run: | | |
| python3 scripts/export/json_to_toon.py | |
| - name: Export XML | |
| working-directory: ./bin | |
| run: php console export:xml | |
| - name: Export YAML | |
| working-directory: ./bin | |
| run: php console export:yaml | |
| - name: Export CSV | |
| working-directory: ./bin | |
| run: php console export:csv | |
| - name: Export MySQL SQL | |
| run: | | |
| mkdir -p sql | |
| mysqldump -uroot -proot --single-transaction --add-drop-table --disable-keys --set-charset --skip-add-locks world regions > sql/regions.sql | |
| mysqldump -uroot -proot --single-transaction --add-drop-table --disable-keys --set-charset --skip-add-locks world subregions > sql/subregions.sql | |
| mysqldump -uroot -proot --single-transaction --add-drop-table --disable-keys --set-charset --skip-add-locks world countries > sql/countries.sql | |
| mysqldump -uroot -proot --single-transaction --add-drop-table --disable-keys --set-charset --skip-add-locks world states > sql/states.sql | |
| mysqldump -uroot -proot --single-transaction --add-drop-table --disable-keys --set-charset --skip-add-locks world cities > sql/cities.sql | |
| # Export complete world database | |
| mysqldump -uroot -proot --single-transaction --add-drop-table --disable-keys --set-charset --skip-add-locks world > sql/world.sql | |
| - name: Add DROP TABLE commands to world.sql | |
| run: | | |
| echo "🧹 Adding DROP TABLE commands in correct order..." | |
| # Remove any existing DROP TABLE commands | |
| grep -v "DROP TABLE" sql/world.sql > sql/world_temp.sql | |
| # Create new file with DROP TABLE commands in correct order (reverse of foreign key dependencies) | |
| cat > sql/world.sql << 'EOF' | |
| -- ------------------------------------------------------------- | |
| -- Generated from MySQL database | |
| -- Combined world database | |
| -- ------------------------------------------------------------- | |
| DROP TABLE IF EXISTS `cities`; | |
| DROP TABLE IF EXISTS `states`; | |
| DROP TABLE IF EXISTS `countries`; | |
| DROP TABLE IF EXISTS `subregions`; | |
| DROP TABLE IF EXISTS `regions`; | |
| EOF | |
| # Append the dump without DROP TABLE commands | |
| cat sql/world_temp.sql >> sql/world.sql | |
| rm sql/world_temp.sql | |
| echo "✅ DROP TABLE commands added" | |
| - name: Generate Schema Files | |
| run: | | |
| echo "📋 Generating schema files..." | |
| # Export MySQL schema only (no data) | |
| mysqldump -uroot -proot --no-data --single-transaction --add-drop-table world > sql/schema.sql | |
| echo "✅ Schema files generated" | |
| - name: Export PostgreSQL SQL | |
| env: | |
| PGPASSWORD: postgres | |
| run: | | |
| mkdir -p psql | |
| # Export PostgreSQL schema only (no data) | |
| pg_dump --dbname=postgresql://postgres:postgres@localhost/world -Fp --schema-only --clean --if-exists --no-owner --no-acl > psql/schema.sql | |
| # Export individual tables with data | |
| pg_dump --dbname=postgresql://postgres:postgres@localhost/world -Fp --inserts --clean --if-exists --no-owner --no-acl -t regions > psql/regions.sql | |
| pg_dump --dbname=postgresql://postgres:postgres@localhost/world -Fp --inserts --clean --if-exists --no-owner --no-acl -t subregions > psql/subregions.sql | |
| pg_dump --dbname=postgresql://postgres:postgres@localhost/world -Fp --inserts --clean --if-exists --no-owner --no-acl -t countries > psql/countries.sql | |
| pg_dump --dbname=postgresql://postgres:postgres@localhost/world -Fp --inserts --clean --if-exists --no-owner --no-acl -t states > psql/states.sql | |
| pg_dump --dbname=postgresql://postgres:postgres@localhost/world -Fp --inserts --clean --if-exists --no-owner --no-acl -t cities > psql/cities.sql | |
| pg_dump --dbname=postgresql://postgres:postgres@localhost/world -Fp --inserts --clean --if-exists --no-owner --no-acl > psql/world.sql | |
| # Remove \restrict and \unrestrict commands from all psql files | |
| for f in psql/*.sql; do | |
| grep -v '^\\\(un\)\?restrict ' "$f" > "$f.tmp" && mv "$f.tmp" "$f" | |
| done | |
| - name: Export SQLite | |
| run: | | |
| # Clean up any existing SQLite files first | |
| rm -rf sqlite/ | |
| mkdir -p sqlite | |
| 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 --mysql-password root -u root -f sqlite/world.sqlite3 | |
| - name: Export SQL Server | |
| working-directory: ./bin | |
| run: php console export:sql-server | |
| - name: Export MongoDB | |
| working-directory: ./bin | |
| run: | | |
| php console export:mongodb | |
| ls -la ../mongodb | |
| - name: Import MongoDB | |
| working-directory: ./mongodb | |
| run: | | |
| # Wait for MongoDB to be ready | |
| sleep 5 | |
| echo "Importing collections..." | |
| mongoimport --host localhost:27017 --db world --collection regions --file regions.json --jsonArray | |
| mongoimport --host localhost:27017 --db world --collection subregions --file subregions.json --jsonArray | |
| mongoimport --host localhost:27017 --db world --collection countries --file countries.json --jsonArray | |
| mongoimport --host localhost:27017 --db world --collection states --file states.json --jsonArray | |
| mongoimport --host localhost:27017 --db world --collection cities --file cities.json --jsonArray | |
| echo "Import completed" | |
| # Create a MongoDB dump | |
| mongodump --host localhost:27017 --db world --out mongodb-dump | |
| # Compress the dump | |
| tar -czvf world-mongodb-dump.tar.gz mongodb-dump | |
| echo "MongoDB dump created at mongodb/world-mongodb-dump.tar.gz" | |
| rm -rf mongodb-dump regions.json subregions.json countries.json states.json cities.json | |
| - name: Compress All Large Files | |
| run: | | |
| echo "🗜️ Compressing all large files..." | |
| echo "================================" | |
| # JSON Files | |
| echo "📄 Compressing JSON files..." | |
| if [ -f json/countries+states+cities.json ]; then | |
| gzip -9 -k -f json/countries+states+cities.json | |
| echo " ✓ json/countries+states+cities.json.gz" | |
| fi | |
| if [ -f json/cities.json ]; then | |
| gzip -9 -k -f json/cities.json | |
| echo " ✓ json/cities.json.gz" | |
| fi | |
| # TOON Files | |
| echo "📄 Compressing TOON files..." | |
| if [ -f toon/cities.toon ]; then | |
| gzip -9 -k -f toon/cities.toon | |
| echo " ✓ toon/cities.toon.gz" | |
| fi | |
| # GEOJSON Files | |
| echo "📄 Compressing GEOJSON files..." | |
| if [ -f geojson/cities.geojson ]; then | |
| gzip -9 -k -f geojson/cities.geojson | |
| echo " ✓ geojson/cities.geojson.gz" | |
| fi | |
| # XML Files | |
| echo "📄 Compressing XML files..." | |
| if [ -f xml/cities.xml ]; then | |
| gzip -9 -k -f xml/cities.xml | |
| echo " ✓ xml/cities.xml.gz" | |
| fi | |
| # YAML Files | |
| echo "📄 Compressing YAML files..." | |
| if [ -f yml/cities.yml ]; then | |
| gzip -9 -k -f yml/cities.yml | |
| echo " ✓ yml/cities.yml.gz" | |
| fi | |
| # CSV Files | |
| echo "📄 Compressing CSV files..." | |
| if [ -f csv/cities.csv ]; then | |
| gzip -9 -k -f csv/cities.csv | |
| echo " ✓ csv/cities.csv.gz" | |
| fi | |
| if [ -f csv/translations.csv ]; then | |
| gzip -9 -k -f csv/translations.csv | |
| echo " ✓ csv/translations.csv.gz" | |
| fi | |
| # MySQL SQL Files | |
| echo "📄 Compressing MySQL SQL files..." | |
| gzip -9 -k -f sql/world.sql | |
| gzip -9 -k -f sql/cities.sql | |
| echo " ✓ sql/world.sql.gz" | |
| echo " ✓ sql/cities.sql.gz" | |
| # PostgreSQL SQL Files | |
| echo "📄 Compressing PostgreSQL SQL files..." | |
| gzip -9 -k -f psql/world.sql | |
| gzip -9 -k -f psql/cities.sql | |
| echo " ✓ psql/world.sql.gz" | |
| echo " ✓ psql/cities.sql.gz" | |
| # SQLite Files | |
| echo "📄 Compressing SQLite files..." | |
| gzip -9 -k -f sqlite/world.sqlite3 | |
| gzip -9 -k -f sqlite/cities.sqlite3 | |
| echo " ✓ sqlite/world.sqlite3.gz" | |
| echo " ✓ sqlite/cities.sqlite3.gz" | |
| # SQL Server Files | |
| echo "📄 Compressing SQL Server files..." | |
| if [ -f sqlserver/world.sql ]; then | |
| gzip -9 -k -f sqlserver/world.sql | |
| echo " ✓ sqlserver/world.sql.gz" | |
| fi | |
| if [ -f sqlserver/cities.sql ]; then | |
| gzip -9 -k -f sqlserver/cities.sql | |
| echo " ✓ sqlserver/cities.sql.gz" | |
| fi | |
| echo "================================" | |
| echo "✅ All compression complete!" | |
| echo "" | |
| echo "📊 Compressed file sizes:" | |
| ls -lh json/*.gz toon/*.gz geojson/*.gz xml/*.gz yml/*.gz csv/*.gz sql/*.gz psql/*.gz sqlite/*.gz sqlserver/*.gz 2>/dev/null || true | |
| - 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 GitHub Release with exports | |
| uses: actions/github-script@v7 | |
| env: | |
| CURRENT_DATE: ${{ env.current_date }} | |
| REGION_COUNT: ${{ env.region_count }} | |
| SUBREGION_COUNT: ${{ env.subregion_count }} | |
| COUNTRY_COUNT: ${{ env.country_count }} | |
| STATE_COUNT: ${{ env.state_count }} | |
| CITY_COUNT: ${{ env.city_count }} | |
| with: | |
| script: | | |
| const fs = require('fs'); | |
| const path = require('path'); | |
| // Find latest feature release (non-prerelease) for base version, | |
| // and count existing export prereleases to determine next export number | |
| let baseVersion = '0.0'; | |
| let exportNum = 0; | |
| try { | |
| const { data: releases } = await github.rest.repos.listReleases({ | |
| owner: context.repo.owner, | |
| repo: context.repo.repo, | |
| per_page: 50, | |
| }); | |
| // Find highest non-prerelease version (manual feature releases) | |
| let bestMajor = 0, bestMinor = 0; | |
| for (const rel of releases) { | |
| if (rel.prerelease) continue; | |
| const match = rel.tag_name.match(/^v?(\d+)\.(\d+)/); | |
| if (match) { | |
| const m = parseInt(match[1]), n = parseInt(match[2]); | |
| if (m > bestMajor || (m === bestMajor && n > bestMinor)) { | |
| bestMajor = m; | |
| bestMinor = n; | |
| } | |
| } | |
| } | |
| baseVersion = `${bestMajor}.${bestMinor}`; | |
| // Count existing export releases for this base version | |
| for (const rel of releases) { | |
| const match = rel.tag_name.match(new RegExp(`^v?${bestMajor}\\.${bestMinor}-export\\.(\\d+)$`)); | |
| if (match) { | |
| const num = parseInt(match[1]); | |
| if (num >= exportNum) exportNum = num; | |
| } | |
| } | |
| } catch { | |
| // No existing releases | |
| } | |
| exportNum++; | |
| const tag = `v${baseVersion}-export.${exportNum}`; | |
| core.info(`Base version: v${baseVersion}, creating export release ${tag}...`); | |
| // Create export release as prerelease (doesn't override latest feature release) | |
| const { data: release } = await github.rest.repos.createRelease({ | |
| owner: context.repo.owner, | |
| repo: context.repo.repo, | |
| tag_name: tag, | |
| name: tag, | |
| body: [ | |
| `## ${tag} - ${process.env.CURRENT_DATE}`, | |
| '', | |
| `Data export build for v${baseVersion}.`, | |
| '', | |
| '### Data Statistics', | |
| `- **Regions**: ${process.env.REGION_COUNT}`, | |
| `- **Subregions**: ${process.env.SUBREGION_COUNT}`, | |
| `- **Countries**: ${process.env.COUNTRY_COUNT}`, | |
| `- **States**: ${process.env.STATE_COUNT}`, | |
| `- **Cities**: ${process.env.CITY_COUNT}`, | |
| '', | |
| '### Formats', | |
| 'JSON, MySQL, PostgreSQL, SQLite, SQL Server, XML, YAML, CSV, GeoJSON, Toon, MongoDB', | |
| '', | |
| '---', | |
| '*Auto-generated by GitHub Actions export workflow*', | |
| ].join('\n'), | |
| draft: false, | |
| prerelease: false, | |
| }); | |
| core.info(`Release created: ${release.html_url}`); | |
| // Upload all .gz files and mongodb dump | |
| const uploadPatterns = [ | |
| 'json/*.gz', | |
| 'sql/*.gz', | |
| 'psql/*.gz', | |
| 'sqlite/*.gz', | |
| 'sqlserver/*.gz', | |
| 'xml/*.gz', | |
| 'yml/*.gz', | |
| 'csv/*.gz', | |
| 'toon/*.gz', | |
| 'geojson/*.gz', | |
| 'mongodb/world-mongodb-dump.tar.gz', | |
| ]; | |
| for (const pattern of uploadPatterns) { | |
| const globber = await glob.create(pattern); | |
| for await (const filePath of globber.globGenerator()) { | |
| // Prefix with directory to avoid name collisions | |
| // e.g. sql/cities.sql.gz → sql-cities.sql.gz, psql/cities.sql.gz → psql-cities.sql.gz | |
| const dir = path.basename(path.dirname(filePath)); | |
| const base = path.basename(filePath); | |
| const assetName = `${dir}-${base}`; | |
| const fileSize = fs.statSync(filePath).size; | |
| core.info(`Uploading ${assetName} (${(fileSize / 1024 / 1024).toFixed(1)} MB)...`); | |
| const data = fs.readFileSync(filePath); | |
| await github.rest.repos.uploadReleaseAsset({ | |
| owner: context.repo.owner, | |
| repo: context.repo.repo, | |
| release_id: release.id, | |
| name: assetName, | |
| data, | |
| }); | |
| } | |
| } | |
| core.info('All export assets uploaded to release.'); | |
| - name: Create Pull Request | |
| uses: peter-evans/create-pull-request@v7 | |
| with: | |
| commit-message: | | |
| Export database formats - ${{ env.current_date }} | |
| Total records: ${{ env.country_count }} countries, ${{ env.state_count }} states, ${{ env.city_count }} cities | |
| Large files (.gz) uploaded to GitHub Releases instead of git. | |
| committer: Darshan Gada <gadadarshan@gmail.com> | |
| signoff: true | |
| branch: export/Files | |
| delete-branch: true | |
| title: "Database Export - ${{ env.current_date }}" | |
| body: | | |
| ## Database Export | |
| ### Data Statistics | |
| - **Regions**: ${{ env.region_count }} | |
| - **Subregions**: ${{ env.subregion_count }} | |
| - **Countries**: ${{ env.country_count }} | |
| - **States**: ${{ env.state_count }} | |
| - **Cities**: ${{ env.city_count }} | |
| ### What's in this PR | |
| Small export files (individual table JSON, CSV, SQL schema, etc.) that are useful in the repo. | |
| ### What's in the Release | |
| All large compressed exports (.gz) are attached to the [latest release](https://github.com/${{ github.repository }}/releases) instead of committed to git. | |
| --- | |
| *Generated by GitHub Actions* | |
| labels: | | |
| exports | |
| automated | |
| reviewers: dr5hn |