New Guide: Understanding EAN Geographic Regions

With this guide, you'll learn how EAN maps geographic regions and how our various regions and subclasses relate to each other within our database files. We also provide our recommended methods for searching via different region types with our script set's stored procedures, including latitude/longitude coordinates, multi-city vincinity searches, airport-based searches and more.

Get the guide from the EAN Database Scripts GitHub repo.

EAN's database script set will automatically configure and update a MySQL database to store local copies of EAN's static database catalog. The tables' creation, initial file retrieval and subsequent updates are handled entirely by the scripts – only minimal user configuration is required.

This particular guide and script set is tailored for Linux environments. For partners who need international languages, the translated versions of our database files are fully supported.

Microsoft SQL is also supported for all English-language files; note that columns marked as TEXT in our database documentation are set as NVARCHAR(MAX) for compatibility with MS SQL. Extra functions provided by the eanextras file in the MySQL script set are not yet available.

Change Tracking

You can track changes for any field in the ActivePropertyList file with every update using the log_activeproperty_changes table created by the latest script set.

Hotel name or address updates, chain code changes, new or deleted properties and more can be tracked on a hotel-by-hotel basis. You can directly edit the stored procedures used to create this table to tailor the tracking table to best suit your needs.

Base Requirements

  • MySQL Server
  • Text editor
  • 9GB of free space (database + space for update downloads)

Step One: Initial MySQL Database Configuration

To ensure any international characters within the database files are handled properly, your MySQL database must be configured to use UTF-8. The scripts are preset to configure databases in UTF-8, but you must ensure the rest of your database and server environment will support this encoding.

The database should be created in a new, clean environment - do not attempt to append any existing databases. Also the refresh process assume an specific fieldnames and field orders, we recommend that if you need to create extensions, do so in another database.

Developers and Administrators:
We suggest you make the following changes to your my.cnf configuration file of MySQL:

[client]
default-character-set    = utf8

[mysqld]
character-set-server     = utf8
collation-server     = utf8_unicode_ci
init-connect        = 'SET NAMES utf8'
init-connect        = 'SET collation_connection = utf8_unicode_ci'
skip-character-set-client-handshake


Step Two: Configure the Database Creation Script

The eanprod creation script will automatically configure the core EAN database with the required table structure, it will also create the database user: eanuser. For international support, the script extend_eanprod must also be used to create the additional structures needed. Both scripts are clearly commented to make configuration easy.

Please note that the core English structure created by eanprod is required for all languages – it must be created even if you do not plan on using English at all. 

After configuration, simply run the script via the MySQL command line utility or a server management program like MySQL Workbench to generate the tables.

Developers and Administrators:
For security, first change the user permission section at the top of the script to a unique username and password.

Both the eanprod and extend_eanprod scripts must be run for international language support.

extend_eanprod must be edited to use the desired language code in the table names it will create. Identify your desired languages in the file list by appending the appropriate language code to the file names, following the filename_xx_XX format. All supported language codes are provided in the comment area at the top of the script.


Step Three: Configure the Database Refresh Script

The database refresh script automates updates the core database catalog and the extras database. The files specified will be downloaded and will automatically replace the existing records in the eanprod database that you configured with the database creation script in step two. The script checks the timestamp fields created in the local database to prevent multiple updates within a day.

If extend_eanprod was used to create tables for additional languages, those languages must be specified in the refresh script'sFILES list. Simply append the set of standard filenames with the appropriate extension for each desired language, such as _ja_JP for Japanese. All supported extensions are provided in a comment at the top of the extend_eanprod script.

Updates to each row will automatically be timestamped as they are inserted. Note that these timestamps only track changes to your local database in order to update and erase records as needed – they do not correspond to when the source files were updated on EAN's servers. 

Before running the refresh script initially or scheduling it via cron or another service, review the following parameters in the script to ensure they are correct for your configuration:
  • MYSQL_DIR must equal the path to MySQL on the machine where the script will run. You can confirm this with which mysql
  • MYSQL_USER and MYSQL_PASS must equal the credentials set on your MySQL server.
  • HOME_DIR should equal the home directory of the account on the OS that will run the script.
  • FILES_DIR should equal the directory you want to download the database files to prior to uploading them to the database.
  • Ensure the FILES list includes the file names for any extra languages you configured with the extend_eanprod script. Copy the original file list provided in the script and add the appropriate language code, ensuring the resulting file names match the table names created by extend_eanprod.
  • Lastly, ensure that the wget command is available on the machine that will run this script. Type which wget in a new terminal window to check - if a directory returns, wget is installed.
    If nothing returns, you'll need to install wget before you can proceed. You can download the package directly from the GNU Wget page.

If you are using MySQL 5.6.6 or newer, you cannot store the username and password in the script as provided. Instead, you must use mysql_config_editor to store your credentials in a protected file. Use the following command format:

mysql_config_editor set --login-path=local --host=localhost --user=localuser –password

You can verify your stored credentials by issuing this command:

mysql_config_editor print --all
You should see the following information returned:
[local]
user = localuser
password = *****
host = localhost
          

The [local] section you just created can be renamed as you wish. Test your login using mysql:

mysql --login-path=local

If all previous steps were successful, you should be presented with the standard MySQL/Oracle introduction and a mysql> prompt.
 
Lastly, you will need to edit the script itself to use the new credentials file. Locate the following line in the script:

CMD_MYSQL="${MYSQL_DIR}mysql  --local-infile=1 --default-character-set=utf8 --protocol=${MYSQL_PROTOCOL} --port=${MYSQL_PORT} --user=${MYSQL_USER} --pass=${MYSQL_PASS} --host=${MYSQL_HOST} --database=${MYSQL_DB}"
and change it to:
CMD_MYSQL="${MYSQL_DIR}mysql --login-path=${MYSQL_LOGINPATH} --local-infile=1 --default-character-set=utf8 --protocol=${MYSQL_PROTOCOL} --port=${MYSQL_PORT} --database=${MYSQL_DB}"
We have this same command commented out in the current script so you can swap to it easily. If you choose to further modify the command, login-path must remain the first parameter for it to work properly.

Step Four: Execute and Schedule

Once you're satisfied with its configuration, execute the refresh script.

The data transfer will take some time - the total file set is in excess of 4GB. You should also allow ample time for the tables to be populated - some of the relational property tables take a particularly long time to fill. Subsequent updates will take less than an hour or so for most machines and connections.

Once the script has finished populating the database, it will verify the file contents against the contents of the database automatically. After a successful initial run, you can schedule weekly updates via cron or the scheduling tool of your choice. If you don't want to or can't automate scheduled runs of the script, the refresh script can be executed manually as needed - just set a reminder on your calendar. We recommend a refresh frequency of at least once per week.

Scheduling

Be careful to schedule the script to run after 12:00 midnight in your time zone, as it uses a "< than today()" expression to erase files. If the script is running as midnight passes, records from the previous day may not be properly erased.

This script is supplied as-is, and we cannot directly support your specific configuration or problem. It is published as an extra help to our partners. If you have any problem or suggestions with this script you can send us an email at support@ean.com.


Support

This script is supplied as-is. We cannot directly support any specific configuration issues or problems you encounter.

If you have any problem or suggestions with the basic nature of the script set, you can send us an email at support@ean.com