SQL server data

Several times a day, data import from the customer's SQL-server is launched.

The following SQL servers are supported:

• MySQL;

• PostgreSQL;

• MS SQL Server;

• Oracle.

There may be the following tables/representations on the server:

• Customers;

• Receipts;

• Receipts lines;

• Events;

• Products (nomenclature directory);

• Product categories (product classifier);

• Bonus balances (if they are not transmitted inside the Customer entity);

• Promo codes.

The tables must contain a field that the Platform can use to determine which data has changed/added since the last data exchange session.

There are two options of data batching:

• By date (serial or timestamp);

• By identifier.

With batching by date the Platform stores the maximum date value in the specified column and during next connection it queries all records with a date more recent than the specified one.

When batching by identifier, the Platform stores the maximum numeric identifier value in the specified column and during the next connection it queries all records with identifier greater than the specified one.

The data is queried in packs of specified size, for example, 1000 or 10000 records each.

When importing data with batching by date, the number of records with the same batching attribute value should not exceed 20000.

Configuration is supported when several users' data are stored in the same tables in the same database. In this case, the tables must contain a column with the user identifier. When importing data, the selection is made by this column.

Example of a real query executed by the platform on a remote SQL server:

SELECT 
    customer_id as local_id, 
    CONCAT(f_name, " ", m_name, " ", l_name) as full_name, 
    f_name as first_name, 
    m_name as middle_name, 
    l_name as last_name, 
    email as email, 
    phone as phone, 
    gender as sex, 
    b_date as birth_date, 
    country as country, 
    area as area, 
    city as city, 
    post_index as zip, 
    reg_date as register_date
FROM customers 
WHERE customer_id > {$last_id} AND user_id = 'acme-corp' 
LIMIT 10000;

In this example:

• customers are extracted from the customers table;

• the customer_id field is used for batching;

• several users live in the same database, the user selection is done by the user_id field;

• The size of the batch is 10,000.