Introduction

This installation manual guides you through the installation of the Write Back Extreme extension on Linux.

Skill level

To follow along, knowledge of installing a webserver on Linux is required.

Operating System

This manual is written for Linux Ubuntu LTS 20.04.

Scope

The following topics will be outside the scope of this manual:

Configuring the firewall
- Webserver: 80/443
- MySQL: 3306
- MSSQL: 1433

1. Preparation

For the purpose of this manual, you should use the bash terminal with sudo privileges:

Copy to Clipboard
sudo -i

Create the installation folder for the extension:

Copy to Clipboard
mkdir -p /var/www/<write-back.company.com>/

Make sure that we use the correct OS (Ubuntu or other LTS system):

Copy to Clipboard
lsb_release -a

Update dependency packages:

Copy to Clipboard
apt update

2. Installing a webserver

In order to use Write Back Extreme, you need to run a webserver. Our recommendation for a webserver is NGINX, due to its lightweight and static file performance. Alternatively, you can install Apache2 or others depending on the operating system.

Select your preferred webserver below and follow te installation steps:

NGINX
Apache2

NGINX

Install NGINX

This page will guide you through the installation of NGINX on Linux. This webserver is one of the options you have for using Write Back Extreme.

Remove Apache2 if installed and not needed:

Copy to Clipboard
apt remove apache2

Install NGINX via apt-get:

Copy to Clipboard
# install NGINX and MySQL server 5.7 or 8 apt -y install nginx # check if running systemctl status nginx

Follow the steps for configuration and further installation (a more detailed description can be found here):

Complete configuration file (domain name; SSL certificate/key)
Download config and follow instructions to place files in /etc/nginx/
Install SSL certificate in the right place
SSL init:

Copy to Clipboard
openssl dhparam -out /etc/nginx/dhparam.pem 2048

Restart NGINX

Copy to Clipboard
nginx -t && systemctl reload nginx

Apache2

Install Apache2/PHP Apache2

For installation of Apache2, please follow the guides provided at:

Note: installation fo different versions of Ubuntu may vary. Choose the version that’s right for your environment.

3. Installing PHP

To use Write Back Extreme you need to setup PHP correctly. We’d recommend installing PHP-FPM because it’s a standalone PHP instance. Other PHP instances require and run-only with Apache2.

Check what version of PHP is currently installed:

Copy to Clipboard
# Checking version of PHP, it should be 7.4 php -v

Install PHP

Copy to Clipboard
# Add PHP 7.4 repo dependencies apt -y install software-properties-common add-apt-repository ppa:ondrej/nginx-mainline # Update the repositories with latest apt-get update apt -y install php7.4-fpm unixodbc # Install PHP extensions apt -y install php7.4-{bcmath,bz2,intl,gd,mbstring,mysql,zip,xml,odbc} # Install the PHP ODBC extension via PECL pecl install PDO_ODBC # check if php is running systemctl status php7.4-fpm

Add the following settings to php.ini to be able to upload extension-upgrade files:

Copy to Clipboard
# /etc/php/7.4/fpm/php.ini post_max_size = 20M upload_max_filesize = 20M

Restart PHP

Copy to Clipboard
systemctl reload php7.4-fpm

4. Installing a database

The next step in installing Write Back Extreme is to install a database. As of this moment, we fully support 4 databases for system tables and schema creation and 1 database that supports schema-only (Snowflake).

Supported databases are listed below. Follow the appropriate guide for the preferred system database. Afterwards you can install any of the database drivers for schema creation.

MySQL/MariaDB

Install MySQL/MariaDB

This guide will explain how to install either MySQL or MariaDB. Because installation for both is similar, these two database drivers are both described on this page. Please follow along using the parts of this guide that apply to the driver of your choice.

Install MySQL

Check which version is currently installed in your environment:

Copy to Clipboard
# check version (should be 5.7 or above) mysql --version

Install MySQL 8:

Copy to Clipboard
# back to home cd # get latest apt-config information for MySQL 8 wget -c https://repo.mysql.com//mysql-apt-config_0.8.16-1_all.deb; dpkg -i mysql-apt-config_0.8.16-1_all.deb ### SELECT: MySQL Server & Cluster ### SELECT: mysql-8.0 ### SELECT: Ok # install MySQL server 8 apt update apt -y install mysql-server ### CREATE: root password ### SELECT: Use Strong Password Encryption

Configure MySQL

Some configuration is optional for security reasons:

Copy to Clipboard
# (MySQL 8 only) make sure the native password module is changed; # otherwise we cannot connect with username/password to database #### add to /etc/mysql/conf.d/mysql.cnf [mysqld] default-authentication-plugin=mysql_native_password # Allow only local access bind-address = 127.0.0.1 # Disable LOCAL INFILE local-infile=0 # restart mysql systemctl restart mysql.service # configure mysql mysql_secure_installation ### validate password plugin: yes ### remove anonymous users: yes ### disallow root login remotely: yes ### remote test database: yes ### reload privilege tables now: yes # Restart MySQL server for applying configuration and user. systemctl restart mysql.service

Install MariaDB

To install MariaDB, please follow the default installation guide.

Configure MariaDB

Some configuration is optional for security reasons:

Copy to Clipboard
# (MariaDB only) make sure the native password module is changed; # otherwise we cannot connect with username/password to database #### add to /etc/mysql/conf.d/mysql.cnf [mysqld] default-authentication-plugin=mysql_native_password # Allow only local access bind-address = 127.0.0.1 # Disable LOCAL INFILE local-infile=0 # restart mysql systemctl restart mysql.service # configure mysql mysql_secure_installation ### validate password plugin: yes ### remove anonymous users: yes ### disallow root login remotely: yes ### remote test database: yes ### reload privilege tables now: yes # Restart MySQL server for applying configuration and user. systemctl restart mysql.service

MySQL and MariaDB – Create database users

We’d highly recommend creating a read and write user for the database. The read-only user will be used for fetching and writing data into the table. The write/read user will be used by the system for managing the schemas and system tables in the database.

Copy to Clipboard
# log in to mysql mysql -u root -p > CREATE DATABASE writeback_db; > CREATE USER 'writeback_owner'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password'; > GRANT ALL ON writeback_db.* TO 'writeback_owner'@'localhost'; > CREATE USER 'writeback_reader'@'%' IDENTIFIED WITH mysql_native_password BY 'password'; > GRANT SELECT ON writeback_db.* TO 'writeback_reader'@'%'; > EXIT;

MS SQL Server

Install MS SQL

For other Linux installations, see Microsoft.com.

Install dev tools

Copy to Clipboard
apt -y install php-pear php7.4-dev php7.4-sybase

ODBC driver

Copy to Clipboard
curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add - # Ubuntu 20.04 (other versions > Ubuntu) curl https://packages.microsoft.com/config/ubuntu/20.04/prod.list > /etc/apt/sources.list.d/mssql-release.list apt update ACCEPT_EULA=Y apt -y install msodbcsql17 unixodbc-dev

PHP extension

Copy to Clipboard
pecl channel-update pecl.php.net # install driver pecl config-set php_ini /etc/php/7.4/fpm/php.ini pecl install sqlsrv pdo_sqlsrv printf "; priority=20\nextension=sqlsrv.so\n" > /etc/php/7.4/mods-available/sqlsrv.ini printf "; priority=30\nextension=pdo_sqlsrv.so\n" > /etc/php/7.4/mods-available/pdo_sqlsrv.ini phpenmod -v 7.4 sqlsrv pdo_sqlsrv

PostgreSQL

Install PostgreSQL

We are currently working on an installation guide for PostgreSQL. Until this page gets updated, please follow the links provided below for installation instructions:

SQLite

Install SQLite

We do not recommend using SQLite. However, it is supported by Write Back Extreme. For an installation guide, please follow the documentation.

Snowflake

Install Snowflake

Snowflake cannot be used for initial install. However, you can install it later to enable schemas and write back actions.

Currently, Write Back Extreme is only supported via ODBC. Make sure you have installed the PHP ODBC extension via PECL. You should have already done this in step 3: Installing PHP:

Copy to Clipboard
pecl install PDO_ODBC

Configure ODBC for Snowflake

Please follow this guide to configure the ODBC driver for Snowflake:

Installing and Configuring the Snowflake ODBC Driver for Linux

ODBC Setup

For Snowflake, we recommend you to setup the majority of the connection settings within the ODBC manager. The connection can be configured as system- or user DSN. There are some configurations required within the backend:

DSN name
Username
Password
Database

ODBC manager fields are leading and will overtake the “local” Write Back configuration.

Some default configuration layout:

Copy to Clipboard
[ODBC Data Sources] SnowflakeDSII = Snowflake [ODBC] Trace = 0 TraceAutoStop = 0 TraceFile = TraceLibrary = [SnowflakeDSII] Driver = {…Path to the driver…} Description = Snowflake DSII Server = customer.snowflakecomputing.com Port = 443 UID = customer Schema = PUBLIC Warehouse = ANALYTICS_WH Locale = en-US Tracing = 0 Database = WRITEBACK_DEMO

DSN name to use in WriteBack: SnowflakeDSII

5. Installing and configuring the Write Back Extreme extension

If you’ve prepared your environment with a webserver, PHP and a database, you are ready to install and configure the Write Back Extreme extension.

5.1 Installation

First, unpack the zip file to /var/www/{write-back.company.com}/

If this zip file contains a writeback-extreme_x.gwe and writeback-extreme_x.md5 file please also unzip the writeback-extreme_x.gwe file. This writeback-extreme_x.gwe file has all the needed files inside.

Copy to Clipboard
## upload writeback.zip to server # check if unzip is installed apt -y install unzip # unpack unzip -o writeback.zip -d /var/www/{write-back.company.com}/ # change owner to executing user (www-data is the standard used by NGINX) chown -R www-data:www-data /var/www/{write-back.company.com}/

5.2 Configuration

Copy to Clipboard
# change directory cd /var/www/<write-back.company.com>/ # copy .env.example to .env cp .env.example .env

5.3 Change environment settings (.env)

You can change the database credentials by adjusting the .env file:

Copy to Clipboard
DB_CONNECTION=mysql-default DB_DRIVER=sqlsrv #DB_CONNECTION=sqlsrv-default DB_HOST=127.0.0.1 DB_PORT=3306 DB_DATABASE=writeback_db DB_USERNAME=writeback_owner DB_PASSWORD=password

5.4 Add a licence file

We create a LICENCE file within the root directory:

Copy to Clipboard
cd /var/www/{write-back.company.com}/ echo “license string” > LICENSE

5.5 Check if previous steps have been successful

Copy to Clipboard
php core app:status

5.6 Install Write Back into database

Copy to Clipboard
php core app:update

5.7 Configure application

Copy to Clipboard
cd /var/www/{write-back.company.com}/ php core installation-menu ### SELECT: Set default connections, in case of MSSQL see “Only for MSSQL”. ### SELECT: Create system user (Administrator = yes; for backend) ### SELECT: Exit

Alter the storage/connection.json. This part is deprecated in 1.1.0, so skip this and head over to 5.8 if you are using 1.1.0. If not, make sure the storage/connection.json contains:

Copy to Clipboard
{ "sqlsrv-default": { "key": "sqlsrv-default", "label": "SQL Server Default", "color": "blue", "driver": "sqlsrv", "url": "${DATABASE_URL}", "host": "${DB_HOST}", "port": "${DB_PORT}", "database": "${DB_DATABASE}", "username": "${DB_USERNAME}", "password": "${DB_PASSWORD}", "options": { "1002": true } } }

5.8 Make sure file permissions are correct

Copy to Clipboard
# change owner to executing user (www-data is the standard used by NGINX) chown -R www-data:www-data /var/www/{write-back.company.com}/ # first is make sure all folders have proper permissions find . -type d -exec chmod 0755 {} \; # make sure all files have proper permissions find . -type f -exec chmod 0644 {} \; # make sure the CORE has execute permissions chmod +x core

5.9 Access backend

The system user (administrator = yes) you created in the “Configure application” step is used to access the backend via:

Copy to Clipboard
https://{write-back.company.com}/backend/

5.10 Download the .trex file

The .trex file can be downloaded from the backend to share it with users. Click on the username (top right) and click on “Download .trex”.

WBE Installation Manual – Download .trex

What’s next?

Congratulations! You’ve successfully installed and configured the Write Back Extreme extension in your environment.

Now that you’re ready to go, check out the Write Back Extreme Product Guide for complete documentation on how to use the extension.

Quirks

This part of the guide will be updated when we get feedback from our customers about things that might go wrong or are not clear during the process of installation.

Upgrade guide

This part of the manual will be reserved for upgrade instructions. Stay tuned!

Write Back Extreme – Installation Manual (Linux)