EAN's database script set will automatically configure and update a MariaDB 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.

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

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

The instructions for MariaDB and SQLite below both refer to script sets available from our database scripts GitHub repo. Download the file set for your desired engine from the repo before you begin.

MariaDB is recommended for production environments. SQLite is recommended only for desktop or development applications.


Creating a MariaDB database with EAN Database content

Follow these steps to create a database from the EAN downloadable content using MariaDB format as a target. You will get ALL available data from EAN using this solution, allowing you to create a central repository for a database server. These instructions are written for Linux-based servers.

First, you will need to make sure you are using the latest MariaDB database engine. You can download the latest version from https://mariadb.com.

If you already have MariaDB, check your engine version before proceeding. Log in via the mariadb (formerly mysql) command line utility and your server version will appear in the third line of the MariaDB welcome message. You can also send the command SELECT @@version; within MariaDB.

In this example case (a CENTOS 7 computer), we can see on the “Server version, that MariaDB version is 10.2.8 is running, so we are good to go!

There are 2 basic steps required to use MariaDB as your target database for imports:

  1. Create the empty structure of the database: You will need to run a .sql script from our repo to generate the database structure.
  2. Configure a refresh schedule: You will need to download and refresh your data every time you need the latest information exposed by EAN. Due to the volume of data and possible connection and machine limitations, transfers may take several hours – be sure to test performance when setting your schedules.

1. Create database structure

For any refresh project, you will need to create an empty database. This will create the storage area as well. The EAN database file collection is about 4 GB in size; once expanded, it consumes approximately 7-8GB.

Set up your databasa by using the mariadb command line utility and the MariaDB_create_eanprod.sql script from the repo.

The following command format will run the script and generate the database – simply replace the example MariaDB credentials below with your own:

>mariadb –u eanuser –p Passw@rd1 –e “source MariaDB_create_eanprod.sql”

For our example case, the command path would be as follows: /usr/local/mariadb/server/bin/mariadb –u eanuser –p Passw@rd1 –e “source MariaDB_create_eanprod.sql”

In just a few seconds, you should have the database created. If you need to cover other languages other than English, you should extend the eanprod script structure with the cooresponding tables.

For example: if you need Spanish and Brazilian Portuguese content, run the extend_eanprod.sql script twice, replacing the XX_xx placeholders in the table names with es_es and then with pt_br.

We can run a technical verification of the database creation by using the same mariadb utility with the show tables command to verify the tables.

We now have an empty structure ready to receive the data from the EAN DB servers. We first use the show databases command, then use eanprod; to use the show tables command. Remember to exit the mariadb utility with the exit; command to finish.

Initial Data Download

Now that we have an empty structure, we can pull the latest data by running the refresh script. Before we do so, we need to understand that the refresh script is created in the Linux platform under the bash shell.

The script is well documented and you can see all utilities that need to be presently installed for it to work (must Linux distributions have all commands already installed, but please double check). Change the script permissions to make it executable (we use the same eanuser user in this case) execute: EAN_MariaDB_refresh.sh

The script will first download the files from our site using the cURL command line utility. You will see output similar to this:

The script will tell you the download time and progress. You can also see if the file is different from the last time you downloaded. The uploading...” line will be the indicator that the script is sending data to your database. Actual time will be affected by network connection and database engine performance, but expect a few hours of work.

At the end of the script you will receive a list of all tables with the amount of records transferred, as well as the lines of records in each downloaded file.

2. Schedule the refresh job

To schedule the refresh job you should use your operating system’s services. In case of Unix/Linux, that is scheduling service is cron. To cron a job you just edit the crontab file. The documentation further states that the crontab command should be used to edit your crontab file, and you specifically edit the file with the crontab -e command.

Here is a crontab file for our example machine:

 In this case, we configure the system to run at 00:00 (midnight) every Sunday, you can see we are using the eanuser to schedule the task and run it inside that directory. For our specific Linux OS, we do not even need to stop/start the crond system as changes are picked up every minute by the engine. In our case, the crond system emails the root user when the job runs.

We officially recommend running this task at least once a week. By design, the refresh script can run while the database and tables are in use, but performance will likely be impacted due to concurrent I/O operations. We recommend so scheduling it to run when your system sees lighter loads.


Creating an SQLite Database with EAN Database Content

SQLite is a more lightweight option for creating an EAN content database in a desktop environment or even mobile devices. However, it may prove harder to manage and maintain for production environments than the MariaDB configuration detailed in the previous section. For the best performance and reliability, please use the MariaDB solution for production.

First, you will need to make sure you are using SQLite version 3 or greater. You can download the latest version from https://sqlite.org.

If you already have sqlite3, check your engine version before proceeding. Log in via the sqlite3 command line utility and your server version will appear in the first line of the welcome message:
 

In this case (a Windows 10 computer) we can see the SQLite version is 3.20.1, so we are good to go!

There are 2 basic steps required to use SQLite as a target database for imports:

  1. Create the empty structure of the database: You will need to run the .sql script from our repo to generate the database structure. The script will generate an empty eanprod.db file that will hold all EAN downloadable content.
  2. Download and refresh your data every time you need the latest information exposed by EAN. Due to the volume of data and possible connection and machine limitations, transfers may take several hours – be sure to test performance when setting your schedules.

1. Create database structure

For any refresh project, you will need to create an empty database. This will create the storage area as well. The EAN database file collection is about 4 GB in size; once expanded, it consumes approximately 7-8GB. This step is easy to accomplish by using SQLite’s command line utility. Optionally, you can use an SQLite graphical browser like DB Browser for SQLite.

Set up your database by using the sqlite3 command line utility and the SQLite_create_eanprod.sql script from the repo.

The following command format will run the script and generate the database in your directory of choice. Your database must be named eanprod.db for the script to work correctly.
For our example case, the subdirectory
/sqlite_data/ was created for the new database – simply replace the example paths below with your own.

>sqlite3 .\sqlite_data\eanprod.db < .\scripts\SQLite\SQLite_create_eanprod.sql

In just a few seconds you should have the file eanprod.db created. You can list your directory to verify its creation:

You can also run a technical verification of the database creation by using the same sqlite3 utility with the .tables command to verify the tables:

1.     Connect to the new database:

>sqlite3 .\sqlite_data\eanprod.db

2.     Run the command:
>sqlite3 .tables

You now have an empty structure ready to receive the data from the EAN DB servers. Remember to exit the SQLite3 utility with the .q command.

Refreshing the Data

Now that we have an empty structure, we can pull the latest data by running the refresh script. Before we do so, we need to understand that the refresh script is created in the Linux platform under the bash shell and that it uses a Python script, so we will need to have the proper runtime installed. The refresh script is EAN_SQLite_refresh.sh.

If you do not have Python installed, visit https://www.python.org/ to download:

In our case the latest Python 2 version is Python 2.7.14. If you are new to Python, you should know that there are two concurrent versions: 2 & 3. They are NOT compatible; this script was design to work with Python 2 only, so please use the latest of that version.

To verify your Python version, use the command python --version

Once we verify it is the correct version, we should move into running the refresh process so we can populate our SQLite database.

Once Python 2 is installed and/or verified, the first step to running the refresh process is to install the Unix compatibility layer so we can launch the script.

This step only applies for Windows machines; if you are running this script in Mac OS or Linux you just need to apply executable permissions via chmod 777 /filepath/EAN_SQLite_refresh.sh

For Windows, use the Cygwin project to install the compatibility layer. Go to the project offical site to download: https://cygwin.com/install.html

Run the default install, ensuring the package md5deep is installed, as it is required to generate the downloadable content signatures and compare them.

You will also need cURL and Unzip. These utilities are required for the refresh script to operate. Download the utilities and place them in a directory within your system path if you do not already have them installed:

Utility

Source site

Executable

cURL - command line tool and library for transferring data with URLs

https://curl.haxx.se/

curl.exe

Unzip – zip file format extractor

http://www.stahlworks.com/dev/unzip.exe

unzip.exe

Before running EAN_SQLite_refresh.sh, open the script and ensure that all variables & file paths are configured correctly for your machine. The script is annotated and you can quickly confirm all utilities that need to be installed for it to work.

To begin refreshing the data, launch the Cygwin64 terminal:

Navigate to the directory containing EAN_SQLite_refresh.sh and execute the script:

You will see the process running as it downloads content and uploads it to the eanprod.db SQLite database.

The script will tell you the download time and progress, you can also see if the file is different from the last time you downloaded. The "uploading..." line will be the indicator that the script is sending data to the database. Actual time will be affected by network connection and database engine performance but expect a few hours of work.

At the end of the script, you will get a list of all tables with the amount of records, as well as the lines of records in each downloaded file.

Schedule the refresh job

To schedule the refresh job, you should use your operating system’s default sub-system. In the case of Windows 10 this is schtasks.exe. You only need to do this step if you will like it to automatically refresh the database; if not, you may run it on-demand as needed. You can find documentation here:  https://msdn.microsoft.com/en-us/library/windows/desktop/bb736357(v=vs.85).aspx

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