Document Research Database Installation Instructions (traditional)

There are two ways of installing the Document Research Database (DRDB):

• Option 1: Installation in a Docker container (very simple, recommended)

• Option 2: Installation on a server with manual configuration of Apache, Mysql, certificates, database creation, etc. etc.

This page describes the manual approach.

Option 2: Manual Installation

The DRDB uses Apache, PHP and MySQL on the backend and several Javascript libraries for the front end. Instead of Apache, other web servers can probably used as well but you are on your own of how to configure them.

The following steps are needed to get the DRDB up and running. In the text below the DBRD is simply referred to as the project. The following procedures were tested on Ubuntu 18.04.

Install Apache and MySQL

System upgrade and Apache server package installation

sudo apt update && sudo apt upgrade
sudo apt install unzip apache2 php libapache2-mod-php php-gd php-zip

#snakeoil ssl certificates
sudo apt install ssl-cert

Apache modules required by DRDB

sudo apt install mysql-server php-mysql php-mysqlnd

Enable Apache modules

sudo a2enmod rewrite headers
sudo a2enmod php7.2

# RESTART the system after this!

---

Base directory change

• In '/etc/apache2/sites-enabled', change 000... directory from /var/www/html to /var/www

• Do the same in ssl site

Enable encryption, disable unencrypted access

sudo a2enmod ssl
sudo a2ensite default-ssl.conf

• Disable listen to port 80 in ports.conf

• Remove unencrypted virtualhost:

sudo a2dissite 000-default.conf
systemctl reload apache2

SSL configuration

• In case the server should listen to a port other than 443:

• Change SSL port in ports.conf and in in the configuration files in *sites-enabled as well!!!

• Put SSL certificates into place. This can also be done later if you are happy to run with the generic snakeoil certificates

Change php session lifetime

• It is recommended to change session.gc_maxlifetime from 1440 seconds (24 minutes) to 172800 seconds (2 days) to make sure drdb document edits that take longer will succeed.

sudo nano /etc/php/7.2/apache2/php.ini

change:
   session.gc_maxlifetime = 14400
 to
   session.gc_maxlifetime = 172800 

sudo service apache2 restart

---

MYSQL user and database configuration

• The MySQL database and the user for the project need to be configured in mysql by hand, this can't be done from the DRDB web GUI later. The values given here need to be configured in DRDB configuration file in the web directory later.

Create the MYSQL database for the project

sudo mysql -u root

create database YOUR_DATABASE_NAME;

• In the statement replace YOUR_DATABASE_NAME with your real database name. To set up an example database, use 'drdb_example' as database name.

Create the MYSQL user for the project

• In the statement replace YOUR_USERNAME with your real database name. To set up an example user, use 'drdb_user'

• Note: The password given in the statement is the hash value of the plaintext password given in the DRDB data file. The password used in the example configuration file is 'F8932jvasdASF'. The hash value of this plain text password is generated with the following mysql command: SELECT PASSWORD('F8932jvasdASF'); and results in *E6410A45E982DC5A85CA6AE4A14CF2AAE169A37E.

# Give the user only access to this particular database in MySQL:

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON YOUR_DATABASE_NAME.* TO 'YOUR_USERNAME'@'localhost' IDENTIFIED BY PASSWORD '*E6410A45E982DC5A85CA6AE4A14CF2AAE169A37E';

exit;

• In the command replace YOUR_DATABASE_NAME and YOUR_USERNAME with the values used above!

---

Web server application and library installation

Unpack Javascript GUI libraries

• Extract the Javascript GUI libraries (bootstrap, etc.) in the root web directory (e.g. /var/www)

cd /var/www
tar xvzf 01-drdb-js-libraries.tar.gz
chown -R www-data:www-data *

Create project directory

• Select a directory in which to put the php code for DRDB. (e.g. /var/www/drdb) and extract the code:

cd /var/www
sudo mkdir drdb

Deny Directory Listings

• Make sure directories can't be listed as this might allow downloading files. Add the following in apache2.conf

# The '-Indexes' is the important part
<Directory /var/www/>
        Options -Indexes +FollowSymLinks
        AllowOverride None
        Require all granted
</Directory>

Configure HTTP Digest username/password

• DRDB requires the use of the web server's HTTP Digest username/password authentication scheme. It can, for example, be configured in the .htaccess files of the project. However, this is problematic when updating the code later on. Therefore configure this in /etc/apache2/apache2.cfg. Here's an example:

<Directory /var/www/drdb>
AuthType Basic
AuthName "drdb"
AuthBasicProvider file
AuthUserFile /drdb-pwd-dir/pwd.txt
Require user john
Require user jane
AllowOverride All
</Directory>

• The pwd.txt MUST be located in /drdb-pwd-dir, as this path is used in the code to add/change/delete username/passwords in the file. Create the directory as follows:

cd /
sudo mkdir /drdb-pwd-dir
sudo chown -R www-data:www-data /drdb-pwd-dir

The usernames/passwords in the file are generated as follows:

#FIRST TIME (ADD -c = create new file)
#sudo htpasswd /drdb-pwd-dir/pwd.txt john
  New password: mypassword
  Re-type new password: mypassword
  Adding password for user john 

#subsequent (-b = batch, password as part of the command:
sudo htpasswd -b /drdb-pwd-dir/pwd.txt jane SecretPassword

• Note: Without username/password protection of the project directories, the database can NOT fucntion, therefore configuration is not optional and missing protection generates a GUI error message.

• Restart apache with sudo service apache2 restart

• Then test that the drdb web directory (still empty) is password protected by going to https://localhost/drdb (replace localhost with IP address or domain name if not working locally). The browser should then ask for a username and password before proceeding to go to the directory. If it doesn't something is no yet setup correctly. Fix this before proceeding!

Allow .htaccess files to override Apache configuration

• A number of directories in the project should not be accessible via the web browser. To ensure this, .htaccess files are used in those directories. This is why the 'AllowOveride All' instruction is necessary in the 'Directory' directive above! Do not forget to add it!

Install the php code

cd drdb
tar -xvf ../drdb-php-code.tar 
cd ..
sudo chown -R www-data:www-data drdb

Configure the project

cd /var/www/drdb/config

The config directory contains 4 configuration files that need to be adapted before the MySQL database tables can be initialized.

config.php

• Modify the trusted_domains array by putting all domain names and IP addresses in that will be used in the web browser to access the database. If the name or IP address is not present, writing to the database will be blocked to prevent cross site vulnerability exploitation.

• Adapt all mysql variables except for mysqlHostName if the MySQL database is on the local machine.

• Change the value of 'salt'. The variable is used as input parameter to create a security token to prevent CSRF attacks.

• doc-xxx, title and footer are text variables used in the GUI. Adapt to your liking.

• Leave fix_french_punctuation = false, unless you are working with French text and non-native keyboards and want things like French quotation marks, non-breakable spaces and other French specialties to be automatically changed/inserted.

• Leave protect_against_rss = true. For details have a look at the project security description.

• Leave default_increment_new_record at 100. This is the default number of ids between two records and allows easy insertion of new records between existing records later. Note: renumbering occurs if there is no space between two records. Leaving the value at 100 will make automatic renumbering unnecessary in most cases.

database-structure.php

• This configuration file contains an array that holds information about which fields each database records will have. A detailed description of how this array is constructed can be found in the configuration file.

• For the demo configuration, leave the file as it is.

• DATABASE FIELD NAME CONVENTIONS - VERY IMPORTANT - READ! The field names (first element in the sub-array) are the names of the fields in the SQL database. They MUST NOT start with a digit, they HAVE TO start with a character. Otherwise the database is created just fine but all add/modify actions to records later on fail with an indescript error message.

id-structure.php

• This file contains the structure of the database id field which has a number of ranges that represent different pieces of data. For details have a look at the configuration file.

• For the demo configuration, leave the file as it is.

user-permissions-config.php

• Add an entry in config/user-permissions-config.php for the user created above and set all access rights. See the config file for details. Without this entry the new user only has very limited access rights.

---

Project database initialization

• Before any pages are shown when typing in the URL in the browser, the database needs to be initialized first. This is done by copy/pasting the following URL to the browser (replace ip-address to that of your server):

https://192.168.1.42/drdb/index.php?content=bs_first_use_init

• If everything has worked correctly the result web page should show a number of green bands that report that the mysql tables have been created successfully.

• If the page remains blank, check the log file for error messages.

tail -n 500 -f /var/www/drdb/log/drdb-logfile.txt 

First document record creation

• After having created the database tables, click on the image at the top of the web page to go to the main screen. There, click on the "Add new document" button to create a new database entry.

Bulk Import of Existing Data

• Pre-existing data from Libreoffice Calc / Microsoft Excel can be imported into a DRDB database at any time. For details see the data import page. A JSON formated demo Calc export file (json-export-data.txt) can be used to import demo data into the database.