A comprehensive collection of tools to generate Oracle SQL scripts for world countries data. This project provides multiple implementations in different programming languages to transform geographical data into normalized Oracle database structures.
This repository contains three different solutions for generating Oracle SQL scripts that populate databases with comprehensive world countries data, including regions, subregions, and detailed country information. Each solution uses different data sources and programming languages to achieve the same goal.
All solutions generate a normalized Oracle database structure with three main tables:
- World regions (continents): Africa, Americas, Antarctica, Asia, Europe, Oceania
- Primary key:
region_id - Unique region names
- Geographic subregions within each continent (20-24 subregions)
- Foreign key relationship to regions
- Primary key:
subregion_id
- Comprehensive data for 246-250 countries and territories
- Foreign key relationships to regions and subregions
- Includes: ISO codes, names, capitals, coordinates, area, population, currencies, languages, and more
- Primary key:
country_id
Primary Solution - Most comprehensive and feature-rich
- Data Source: mledoze/countries repository
- Language: Python 3
- Features:
- Automated setup script with data download
- Comprehensive country data including membership fields
- Oracle-optimized SQL with proper indexing
- Master script for sequential execution
- Example queries for common use cases
- 250 countries with extensive metadata
Key Files:
generate_oracle_sql.py- Main generator scriptsetup.sh- Automated setup and executionadd_membership_fields.py- Enhanced version with membership datarequirements.txt- Python dependencies
Generated SQL Files:
00_master_script.sql- Master execution script01_create_tables.sql- DDL with tables, indexes, constraints02_insert_regions.sql- 6 world regions03_insert_subregions.sql- 24 subregions04_insert_countries.sql- 250 countries/territories05_example_queries.sql- Common queries and analysis
Alternative Solution - Clean and simple
- Data Source: world-countries npm package
- Language: Node.js/JavaScript
- Features:
- NPM-based data source
- Clean, readable code structure
- Separate SQL files for each table
- Oracle-specific data types and syntax
- 246 countries with core data
Key Files:
index.js- Main generator scriptpackage.json- Node.js dependenciesconvert.js- Data conversion utilitiestest.js- Test suite
Generated SQL Files:
01_create_tables.sql- Table definitions02_regions_data.sql- 5 regions03_subregions_data.sql- 22 subregions04_countries_data.sql- 246 countriesrun_all.sql- Execution script
Ruby Implementation - Using Countries gem
- Data Source: Countries gem
- Language: Ruby
- Features:
- Official Countries gem integration
- Oracle sequences and triggers
- Single or separate file generation
- Comprehensive country metadata
- 246 countries with detailed information
Key Files:
generate_countries_sql.rb- Main generator scriptGemfile- Ruby dependenciesimport.sh- Import script
Generated SQL Files:
schema.sql- Table definitionsregions.sql- 5 regionssubregions.sql- 22 subregionscountries.sql- 246 countries
cd sol_mdeloze
chmod +x setup.sh
./setup.shcd sol_js_world_countries
npm install
npm startcd sol_ruby
gem install countries
ruby generate_countries_sql.rb- Python 3.6+
- curl (for data download)
- Standard Python libraries only
- Node.js 12+
- npm
- Ruby 2.5+
- Countries gem
- Application Development: Reference data for applications requiring geographical information
- Data Analysis: Geopolitical analysis and reporting
- ETL Processes: Data warehousing and business intelligence
- Educational Projects: Learning database design and normalization
- API Development: Backend data for geographical APIs
| Solution | Countries | Regions | Subregions | Data Source |
|---|---|---|---|---|
| Python | 250 | 6 | 24 | mledoze/countries |
| JavaScript | 246 | 5 | 22 | world-countries npm |
| Ruby | 246 | 5 | 22 | Countries gem |
- Membership Fields: UN, EU, NATO, Commonwealth, and other organization memberships
- Comprehensive Metadata: Currencies, languages, timezones, calling codes
- Performance Optimization: Proper indexing and query optimization
- Data Validation: Automatic data cleaning and validation
- Incremental Updates: Support for data updates and migrations
- Oracle Compatibility: Proper Oracle SQL syntax and data types
- Foreign Key Relationships: Normalized database design
- Data Integrity: Constraints and validations
- Documentation: Comprehensive comments and documentation
- Example Queries: Common use case examples
Each solution generates organized SQL files:
- Schema/DDL Files: Table creation with constraints and indexes
- Data Files: INSERT statements for each table
- Master Scripts: Sequential execution scripts
- Example Queries: Common queries and analysis examples
Contributions are welcome! Areas for improvement:
- Additional data sources and validation
- Performance optimizations
- New programming language implementations
- Enhanced documentation
- Bug fixes and improvements
This project is open source. Data sources have their own licenses:
- mledoze/countries - Open Database License
- world-countries - Open Database License
- Countries gem - MIT License
- Data Sources:
- mledoze/countries - Comprehensive countries data
- world-countries - NPM package
- Countries gem - Ruby gem
- Contributors: All developers who have contributed to this project
- Community: Open source geographical data community
For questions, issues, or contributions:
- Create an issue in the repository
- Review existing documentation
- Check the example queries for common use cases
Choose the solution that best fits your technology stack and requirements. The Python solution is recommended for most use cases due to its comprehensive feature set and automated setup.