Exprodo DB Installation Guide

If your Exprodo DB is to be hosted on exprodo.com, then you will not need to read these instructions as it will be installed for you. You only need to read this installation guide if you are going to install Exprodo DB on your own server.

Hardware and Software Requirements

Operating system

Windows

MacOS

Linux (preferred)

Server Software

Java 8 or Java 11

MySQL 5.6 or later or MariaDB 10.1.2 or later

Tomcat 7 minimum

Apache HTTP Server (tested on 2.2 and 2.4, although any version should work)

Server Hardware

RAM 4GB minimum, 8GB recommended

Hard Disk 10GB minimum, but requirements depend on usage, particularly if you configure the system to store file uploads.

Recent & fast CPU recommended

Both 32 bit and 64 bit supported

You can use a virtualised server if required

Client Software

Chrome - any version, but recent versions preferred

FireFox - any version, but recent versions preferred

Safari - any version, but recent versions preferred

Microsoft Edge

Internet Explorer IE 9 or later, but IE11 is much better than IE9. (IE8 mostly works but is extremely slow)

Requirements

You should have received a file archive that contains the following files:

 

INSTALL/apache/http-redirect-www.yoursite.com.conf

INSTALL/apache/https-www.yoursite.com.conf

INSTALL/apache/http-testonly-www.yoursite.com.conf

INSTALL/apache_with_mod_jk/http-redirect-www.yoursite.com.conf

INSTALL/apache_with_mod_jk/mod_jk.conf

INSTALL/apache_with_mod_jk/https-www.yoursite.com.conf

INSTALL/apache_with_mod_jk/http-testonly-www.yoursite.com.conf

INSTALL/apache_with_mod_jk/README.txt

INSTALL/apache_with_mod_jk/workers.properties

INSTALL/apache_with_webauth/http-redirect-www.yoursite.com.conf

INSTALL/apache_with_webauth/https-www.yoursite.com.conf

INSTALL/pGina/dotNet45/pGina.Plugin.Exprodo.dotNet45.dll

INSTALL/pGina/dotNet45/Newtonsoft.Json.dll

INSTALL/pGina/dotNet40/pGina.Plugin.Exprodo.dotNet40.dll

INSTALL/pGina/dotNet40/Newtonsoft.Json.dll

INSTALL/user_tracker/...

INSTALL/README.txt

INSTALL/tomcat6/conf/server.xml

INSTALL/tomcat6/conf/tomcat-users.xml

 

The DB directory contains the program itself. The other files are examples for configuring Apache Tomcat 7 and Apache web server, described further below.

Installation Overview

You will need:

A database server, MySQL or MariaDB. Please email support@exprodo.com if you would like to use a different database server.

A servlet engine, eg Tomcat 7, Jetty etc

Java JDK 8 or JDK 11 (or possibly just a JRE, depending on the version of your servlet engine)

An Apache HTTP server may be required, or else you can serve directly from the servlet engine. The config files are use Apache HTTP server as a front end to Tomcat.

Once those things are in place, you need to:

Copy the DB directory into the servlet engine's webapps directory

Change a configuration file so it knows how to connect to the database

Configure the servlet engine and Apache

Start the servlet engine and Apache

Connect to the server from a browser to make sure it is accepting connections.

Install MySQL

MySQL 5.6 or later or MariaDB 10.1.2.

Create The Database And A Mysql User

Open a connection to MySQL with:

mysql -u root -p

 

or (depending on your operating system) and configuration:

sudo mysql

 

and then at the mysql prompt:

mysql> create database db;

mysql> grant all privileges on db.* to exprodo@localhost 

    -> identified by 'somepassword';

mysql> exit;

 

You may choose a different name for the database and/or user if you wish. Later on, you will configure Exprodo DB so it can locate the database and log in - you will just put the connection information into a URL (in file hibernate.cfg.xml).

 

It is not necessary to load any tables into this new database. When Exprodo DB boots for the first time, it will see the tables are missing and will automatically create the required tables and populate some of them with default data.

Install A Java JDK

Follow the installation instructions for the JDK for your platform. This needs to be at least JDK 8.  Note that the JDK is required, not just the JRE (this is a requirement for Apache Tomcat, the servlet server, rather than Exprodo DB itself).

Install A Java Servlet Server

This should be Apache Tomcat, or Jetty or some other server that supports the Servlet 2.5 specification. Exprodo DB has only been tested with Tomcat 7, 8 and 9, and so this is the preferred server. A future Exprodo DB release is likely to use Jetty instead (and also include a much easier installation and upgrade mechanism). The rest of this section assumes you are using Tomcat version 7.

 

You should read and follow the standard documentation for Tomcat to install it. Once installed, you will need to edit two files in Tomcat's conf directory, server.xml and tomcat-users.xml. You should find samples of each of these with the Exprodo DB distribution. The important parts of these are:

 

tomcat-users.xml

This needs to contain a <user...> line so that somebody can connect to Tomcat's web management pages. The user name and password can be anything and will only ever be used by a human. You may want more than one entry here, depending on who will do the administration.

server.xml

The sample provided defines a <Connector ...> entry on port 8080 that allows users to connect directly to Tomcat. A standard Tomcat server.xml would contain this entry, although the sample has the necessary config so that it supports compression. If all access is to be made through Apache, then this connector on port 8080 could potentially be turned off. Leave it on for now so you can test the installation before we bring Apache into the equation.

 

There's also a <Connector...> on port 8009 with protocol AJP/1.3. This is the means by which Apache talks to Tomcat.

Once done, check that you can connect to Tomcat and access its management pages. Go to the web page:

http://servername:8080/

where servername is the name of the server you installed Tomcat on. There should be a link on there to the management pages so you can ask Tomcat to start Exprodo DB (after Exprodo DB is installed).

Install Exprodo DB

Exprodo DB is delivered as a directory in war file format. However, there's a file inside the directory (WEB-INF/classes/hibernate.cfg.xml) that you will need to modify to tell Exprodo DB how to connect to the database.

 

The root directory in the archive is called DB. You should put this directory into Tomcat's webapps directory. Then edit the file

webapps/DB/WEB-INF/classes/hibernate.cfg.xml

 

The entries you need to configure are:

<property name="hibernate.connection.username">

exprodo

</property>

<property name="hibernate.connection.password">

somepassword

</property>

<property name="hibernate.connection.url">

jdbc:mysql://localhost/db

</property>

<property name="hibernate.connection.driver_class">

org.mariadb.jdbc.Driver

</property>

<property name="hibernate.dialect">

com.springsolutions.exprodo.core.server.persistence.ExprodoMySQL5InnoDBDialect

</property>

 

You need to change the username, password and url to match the user and database name you created earlier.

 

You should now ask Tomcat to start Exprodo DB (in case it isn't done automatically). Go to the web management page, linked from:

http://servername:8080/

and see if Exprodo DB has started, and if not, ask for it to be started.

 

Once this is done, then you should test to see whether Exprodo DB is working with Tomcat. Open a web browser at this url:

http://servername:8080/DB/com.springsolutions.exprodo.basic.Basic/program.html

 

All being well, you will see a Exprodo DB login screen. If you don't, then you'll need to check to see what errors are on screen or in the Tomcat logs.

 

If you do see the login screen, then try to log in with username root and a blank password.

Install Apache HTTP Server

Follow the Apache documentation as appropriate for your operating system in order to install the Apache HTTP server. Once installed, you should make sure your Apache configuration loads modules mod_proxy, mod_proxy_http, mod_rewrite, mod_deflate and mod_ssl. The location of your modules may vary from that shown below, so you may need to alter this sample configuration:

 

LoadModule proxy_module /usr/lib/apache2/modules/mod_proxy.so

LoadModule proxy_http_module /usr/lib/apache2/modules/mod_proxy_http.so

LoadModule rewrite_module /usr/lib/apache2/modules/mod_rewrite.so

LoadModule deflate_module /usr/lib/apache2/modules/mod_deflate.so

LoadModule ssl_module /usr/lib/apache2/modules/mod_ssl.so

 

For mod_deflate, you will need to add config like this:

<IfModule mod_deflate.c>

   AddOutputFilterByType DEFLATE text/html text/plain text/xml text/css text/javascript

</IfModule>

 

This allows Apache to gzip the files it serves. This has a marked impact on the size of Exprodo DB javascript files in particular.

 

You will now need to configure the Exprodo DB aspects of Apache. You will have your own requirements here, but here is the configuration you will need to provide access to Exprodo DB from a URL of "https://yourServer/DB".

 

RewriteEngine On 

RewriteRule ^/DB/Service(.*) /DB/com.springsolutions.exprodo.basic.Basic/Service$1 [PT,L]

RewriteRule ^/DB/program.html(.*) /DB/com.springsolutions.exprodo.basic.Basic/program.html$1 [PT,L]

RewriteRule ^/DB/$ /DB/com.springsolutions.exprodo.basic.Basic/program.html [PT,L]

RewriteRule ^/DB/(.*) /DB/com.springsolutions.exprodo.basic.Basic/$1 [PT,L]

ProxyRequests Off

<Proxy *>

    Order allow,deny

    Allow from all

</Proxy>

ProxyPass /DB http://localhost:8080/DB

ProxyPassReverse /DB http://localhost:8080/DB

 

The above configuration should be placed inside a VirtualHost declaration. If you want to provide both SSL and non-SSL access to Exprodo DB, then you should place the above configuration in both the SSL and non-SSL VirtualHost areas. At this point, accessing Exprodo DB from "https://yourServer/DB" should work, possibly unless you are using an operating system that uses SELinux, as described below.

 

Note that previous versions of this installation guide differed in two ways from the above:

Previously, it was suggested that you should use mod_jk to connect Apache HTTP server with your servlet container (such as Apache Tomcat). This is no longer recommended. In particular, mod_jk isn't available for recent versions of Red Hat Linux and CentOS. That is why the above recommended Apache configuration uses mod_proxy_http.

It used to be a requirement that http access was required, at least from localhost, even when you only wanted https access to be remotely accessible. This requirement no longer exists so you only need to configure the SSL VirtualHost with the above directives if you only want to provide SSL access.

SELinux Settings

If you are running a version of Linux that uses SELinux, then you may find that your Apache HTTP server is prevented from opening further http connections, thereby stopping the above from working. You can check whether you will need to do this by running this command:

getsebool httpd_can_network_connect

 

If this returns zero or off, then you will need to change this setting by running this command:

setsebool -P httpd_can_network_connect 1

 

You can read about the httpd_can_network_connect setting on the httpd_selinux man page. If your httpd_can_network_connect setting is zero or off, then the result will be an error in your Apache logs similar to this:

(13)Permission denied: proxy: HTTP: attempt to connect to 127.0.0.1:8080 (localhost) failed

Rebooting

Finally, you will need to arrange for MySQL, Tomcat and Apache to be started each time your operating system boots. You should follow the standard method for your platform. For Windows, this means they should all have been installed as services.

Advanced Apache Configuration

Sometimes, you may have an external-facing Apache HTTP server through which all of your traffic flows (eg on a firewall) and then internally you may have another Apache HTTP server that has the mod_jk module installed. In this situation, with two Apache HTTP servers and one Tomcat server, there is some extra configuration to do. Without any extra configuration, Tomcat will always be given the IP address of the firewall Apache HTTP server as the source of each request instead of that of the user's computer. The problem with such a situation is that the login attempts that Exprodo DB will record will all show the firewall as the source of every attempt. For defence against any attack, it would be better to change this.

 

You can set up forwarding of HTTP requests from the firewall to the inner Apache HTTP server in two ways: by configuring a reverse proxy with the ProxyPass directive, or with mod_rewrite and the [P] directive.

 

If you use ProxyPass, then you can make a configuration change on the firewall Apache HTTP server by adding the directive

ProxyPreserveHost On

to the appropriate part of the firewall's Apache HTTP server configuration.

 

This situation is described here:

http://tomcat.apache.org/connectors-doc/generic_howto/proxy.html

You may be able to achieve the desired effect with SetEnv or SetEnvIf directives instead of the above (but I have not tested this).

 

Once you have done this, you can check whether this is working as expected in one of two ways. First, you can look at entries that appear in the login_attempts table whenever somebody tries to login to Exprodo DB. Secondly, you can check from Exprodo DB by using either the Search page or the Data Explorer page.

Upgrading Exprodo DB

Upgrades follow the following path:

 

1.Stop Tomcat

2.Take a backup of your MySQL database, this should be done before every upgrade (and must be done before major upgrades) because Exprodo DB applies database updates automatically when a new version is first run.
 
mysqldump -u root --password=somepassword db > db.saved.sql

3.Move your existing DB directory out of tomcat's webapps directory to somewhere else.

4.Unpack the contents of the new tar.gz file, and move the DB directory from there into your tomcat's webapps directory.

5.Copy the hibernate.cfg.xml file from your old DB/WEB-INF/classes/ directory to the new directory

6.Make sure the old DB directory is not in tomcat's webapps directory. (If there are two DB directories in the webapps directory at the same time, then both will try to run. If they point to the same database, then things may go wrong. If they are different versions of Exprodo DB, then things will probably go wrong.)

7.Open a web browser to confirm that Exprodo DB is accepting connections

Rolling Back a Failed Database Upgrade

If you should want to roll back the database because an upgrade failed in some way, then you should not load you back-up over the top of the upgraded database. That's because there could be tables for the upgrade that remain in place, and so it might cause problems with doing the upgrade again (since it may do the wrong thing since it will detect the new tables or columns exist and assume it doesn't need to do an upgrade).

 

The recommended way to roll back the database after an upgrade is to drop the database, recreate it, and then load the backup.

Installing Files Required For PDF Generation

Exprodo DB supports two different means of generating PDF files. The older of these requires installation of groff and mom and relies on system calls outside Java to run it. We no longer recommend this, and instead much prefer to use XSL FO as the route towards generating PDF files. This means you do not need to install groff and should only consider using this method if you are very familiar with groff and mom.

 

Ubuntu

 

    $ sudo apt-get install groff

 

The groff package includes both the full set of 'mom' macros needed for creating documents and the extras for creating PDF files.

 

CentOS

 

  $ sudo yum install groff

  $ sudo yum install groff-perl

 

The groff package includes the full set of 'mom' macros needed for creating documents.

 

The groff-perl package includes the extras for creating PDF files.