MasonSql 14_04 Installation on Ubuntu 14.04 LTS (Trusty) on Proxmox servers 2_01

Create the VM with at least 2GB RAM and 512M of disk space; Proceed with the upgrade of the operating system and the installation of the package.

Notes: In Ubuntu 14_04 you must install Apache 2.2 as per below steps not 2.4 because MasonSQL is not compatible with Apache 2.4

# Proceed with root privileges
sudo su -

# Add Ubuntu 12.04 Precise on your repository list

echo "deb http://archive.ubuntu.com/ubuntu precise main restricted universe
deb http://archive.ubuntu.com/ubuntu precise-updates main restricted universe
deb http://security.ubuntu.com/ubuntu precise-security main restricted universe multiverse
deb http://archive.canonical.com/ubuntu precise partner" > /etc/apt/sources.list.d/ubuntu1204.list

# Add Apache and Perl Pinning

echo "Package: apache*
Pin: release a=precise
Pin-Priority: 1000
 
Package: perl*
Pin: release a=precise
Pin-Priority: 1000
 
Package: libperl*
Pin: release a=precise
Pin-Priority: 1000" > /etc/apt/preferences.d/apache22

# Add postgresql

sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -cs)-pgdg main" >> /etc/apt/sources.list.d/pgdg.list'

apt-get install wget ca-certificates

# Import the repository signing key, and update the package lists
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

# Locale settings
echo "it_IT.UTF-8 UTF-8" > /var/lib/locales/supported.d/local
locale-gen

# Time zone
dpkg-reconfigure tzdata

# Update packages
apt-get update
apt-get upgrade
apt-get dist-upgrade

## Packages needed to use MasonSQL

# Apache2 & mod_perl
apt-get -t precise install apache2 apache2-mpm-prefork libapache2-mod-perl2 libapache2-request-perl libapache2-mod-apreq2 
a2enmod apreq perl ssl 

# Postgresql
echo "deb http://apt.postgresql.org/pub/repos/apt/ trusty-pgdg main" >/etc/apt/sources.list.d/pgdg.list
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo apt-get update
apt-get install postgresql-9.6 postgresql-client-9.6 postgresql-plperl-9.6 postgresql-client-common postgresql-common postgresql-contrib-9.6 postgresql-doc-9.6 

# Perl DBI, DBD::Pg
apt-get install build-essential libpq-dev
cpan DBD::Pg
apt-get install libdbi-perl libapache-dbi-perl

# Various packages
apt-get install -t precise libjson-perl libautobox-perl libxml-simple-perl libclone-perl liblog-log4perl-perl libipc-run-perl libspreadsheet-writeexcel-perl libmail-sender-perl libstring-diff-perl libarchive-tar-wrapper-perl libclass-load-perl libdbi-perl libsql-statement-perl libhtml-parser-perl libxml-parser-perl

# Mason
apt-get install libhtml-mason-perl

# Various packages available from CPAN
cpan install App::cpanminus
# new releases not work
cpanm Apache2::AuthCookie@3.23
cpan HOP::Lexer
cpan SQL::Interpolate
cpan Sys::RunAlone
cpan HTTP::BrowserDetect

You must then copy the /opt/masonsql/ replicating the folder from an already existing installation or by downloading from SVN and recovering dojo-toolkit 1.8 from the internet.

Proceed with the database installation using the forms in /opt/masonsql/utility/sql/ directory.

The create_database.sql creates the usernames used by the application. You have to change passwords which can then be shown in the configuration files. If necessary also modify the char-set declared during the the database creation.

cd /opt/masonsql/utility/sql
cat roles.sql | su -c psql\ postgres postgres
cat create_database.sql | su -c psql\ postgres postgres
cat pg_dump.masonsql.template.sql | su -c psql\ masonsql postgres

Change the database access permissions:

## file /etc/postgresql/9.6/main/postgresql.conf :
listen_addresses = 'localhost'
# Or if there is also an access from the local network:
listen_addresses = '*'

## file /etc/postgresql/9.6/main/pg_hba.conf
local masonsql all password
host  masonsql all        127.0.0.1    255.255.255.255 password
# If you access the LAN
host  masonsql all        192.168.1.0  255.255.255.0   md5

# Restart the server (after the listening socket has changed)
/etc/init.d/postgresql restart

Setting up your environment for the installation of an application that uses the framework.

mkdir /opt/demo_masonsql /opt/demo_masonsql/etc /opt/demo_masonsql/htdocs /opt/demo_masonsql/lib
chmod -R 750 /opt/demo_masonsql

mkdir /opt/demo_masonsql/archive /opt/demo_masonsql/tmp
chmod 770 /opt/demo_masonsql/archive /opt/demo_masonsql/tmp

mkdir /var/log/apache2/demo_masonsql
chmod 755 /var/log/apache2/
chmod 770 /var/log/apache2/demo_masonsql
chown root.www-data /var/log/apache2/demo_masonsql

# All files belong to the root user and www-data group
chown -R root.www-data /opt/demo_masonsql

cp -a /opt/masonsql/utility/example.apache2.demo_masonsql.conf /opt/demo_masonsql/etc/apache2.demo_masonsql.conf

# Edit the configuration file by adjusting the parameters: VirtualHost, SSLCertificateFile, SSLCertificateKeyFile
# ServerAdmin, ServerName, MailFrom, Auth_AC_Domain, DBIconnect, etc.

# Link to the Apache2 configuration folder
ln -s /opt/demo_masonsql/etc/apache2.demo_masonsql.conf /etc/apache2/conf.d/demo_masonsql.conf

# Insert the /etc/ crontab command to periodically empty the temporary files folder
echo "# svuoto file temporanei di Demo MasonSql
12 6  * * *     root find /opt/demo_masonsql/tmp -maxdepth 1 -mindepth 1 -mtime +7 -exec rm -r '{}' ';'" >> /etc/crontab

Access the application using the username admin and password installa

ODT Reportman Installation

---

These instructions for installation were checked on Ubuntu Precise (12.04) and Ubuntu Trusty (14.04) with downgrade to Apache 2.2

Packages

These packages are prerequisite for the ODT Report Manager:

apt-get -y install libxml-simple-perl
cpan XML::Simple::Sorted
cpan IPC::Run::SafeHandles
cpan Text::Balanced

Perlbrew

Because of ODF::lpOD incompatibility with the Perl version 5.14, used by MasonSQL in some deployments, the Perlbrew must be installed to use a newer version of Perl.
See the MasonSQL Configuration Matrix.

To install Perlbrew, with all the necessary libraries, run following perlbrew-deploy.sh script as a root user:

sudo /opt/masonsql/utility/perlbrew-deploy.sh

Libreoffice

# Proceed with root privileges.
sudo su -

# Install latest System.LibreOffice (5.0 or later).

# Ubuntu Precise (12.04)
apt-get install python-software-properties
# Ubuntu Trusty (14.04)
# apt-get install software-properties-common

sudo add-apt-repository ppa:libreoffice/ppa
sudo apt-get update
apt-get install openjdk-9-jre libreoffice-writer
# Se si utilizzano anche i file ODS
apt-get install libreoffice-calc
# Install pdftk.
apt-get install pdftk

Unoconv Daemon

Unoconv can be used by ODT Report Manager to convert .odt files to .pdf format.
The OdtConvEngine environment variable must be configured with "OdtConvEngine" as an alternative to "libreoffice".

# Proceed with root privileges.
sudo su -
# Install latest Unoconv.
cd /tmp
apt-get install git
git clone https://github.com/dagwieers/unoconv
cd unoconv/
make install
cd ../
rm -rf unoconv/

# Ubuntu Trusty (14.04) - You must use python3
sed -i~ 's/^#!\/usr\/bin\/env python$/#!\/usr\/bin\/env python3/' /usr/bin/unoconv

# Install and start Unoconv daemon.
ln -s /opt/masonsql/etc/unoconvd /etc/init.d/unoconvd
update-rc.d  unoconvd defaults
service unoconvd start

Tables

The queries for creating the ODT tables are in the /opt/masonsql/utility/sql/odt_report_tables.mql file.

Before applying the queries the owner name postgres must be changed to the new owner name my_db_owner_name.

Configuration

ODT Report Manager uses a couple of Apache configuration variables.
Following is an example configuration in the /opt/my_app_name/etc/apache2.my_app_name-devel.my_domain_name.conf file:

# Path of this apache configuration file
PerlSetVar ConfigPath /opt/my_app_name/etc/apache2.my_app_name-devel.my_domain_name.conf

# Temporary directory
PerlSetVar TmpDir   /opt/my_app_name/tmp

# Default report engine configuration: 
# rep = old Report Manager
# odt = new ODT Report printed in odt format
# pdf = new ODT Report printed in pdf format
PerlSetVar DefaultReportEngine "pdf"

# ODT Reportman's access to DB
PerlSetVar ReportDBIconnect "dbi:Pg:dbname=my_db_name;user=report;password=MyReportPassword"

# Archive for templates
PerlSetVar InputFilesArchive "/opt/my_app_name/archive"

The InputFilesArchive is used by Files Component. The component stores its files in the sub-directories which must be created:

cd /opt/my_app_name/archive
mkdir public
chown www-data:www-data public
mkdir odt_reports
chown root:www-data odt_reports
chmod 775 odt_reports

Without correct access permissions for the application's Apache config file the ODT Report Manager returns an error. Example of correctly set permissions:

-rw-r----- 1 root www-data 10182 Aug 19 12:13  /opt/my_app_name/etc/apache2.labs.leader.it.conf

In order to apply changes, Apache configuration file must be reloaded:

/etc/init.d/apache2 reload

Functions and Permissions

Example setup of the ODT Reportman's permissions:

Reportman Installation

Reportman is a report manager developed with Delphi and Kylix, available for Windows and Linux.

Unfortunately in the Linux version (Kylix) UTF-8 encoding can not be used, so it was necessary to abandon the use of Kylix version in favor of the Delphi version, which fortunately turns Wine environment.

# Installing Wine
apt-get install wine xserver-common
PDF filter for optimization and to protect the document
apt-get install qpdf

# If you are connecting remotely with 'ssh -CX ...'
echo -e "\n# Necessario per usare X da remoto\nX11UseLocalhost yes\n"  >> /etc/ssh/sshd_config
/etc/init.d/ssh restart

# Package not necessary
apt-get purge gnome-exe-thumbnailer cups-bsd cups-client cups-common libcupsimage2 libcups2

Warning!: If you use already prepared and copied image (/opt/masonsql/reports/) from a previous installation, the next steps are not necessary!
Otherwise continue installation from a server with an available console X. I suggest to use Xrdp:

apt-get install xrdp xterm

# If you also use the Report Manager Designer you must also install a Desktop Manager,
# Otherwise you can not resize windows; for example:

apt-get install blackbox

# Wine Home is using the www-data user
HW="/opt/masonsql/report/www-data_home_dir"
[ -d $HW ] || cp -a /etc/skel $HW
chmod 700 $HW
chown -R www-data.www-data $HW
/etc/init.d/apache2 stop
usermod -d $HW  -s /bin/bash www-data
/etc/init.d/apache2 start

To connect remotely:

rdesktop -g1280x1024 -u www-data SERVER

Connect to the server with the user using Reportman to continue with the Wine installation and Reportman:

# Make sure that wine is working by launching the Wine configuration:
winecfg

# Microsoft Font
winetricks corefonts


# ADOBE Installation - Microsoft License EULA !!!

#    http://msdn.microsoft.com/en-us/library/windows/desktop/ms693148%28v=vs.85%29.aspx
winetricks mdac28

# Installing Postgresql ODBC driver
wget http://ftp.postgresql.org/pub/odbc/versions/msi/psqlodbc_09_01_0200.zip
unzip psqlodbc_09_01_0200.zip
wine msiexec /i psqlodbc.msi
# Verification or configuring the ODBC connections
wine odbcad32.exe

# Download Windows version of the Reportman (complete package)
wget http://sourceforge.net/projects/reportman/files/Report%20Manager%20Designer/Designer%202.9/reportman2_9b.exe


# Reportman Installation
# Install only when needed: Command Line Tool and optionally Report Manager Designer (if you develop new reports),

wine reportman2_9b.exe

# Remove no longer needed files
rm psqlodbc_09_01_0200.zip  psqlodbc.msi  README.txt  reportman2_9b.exe  upgrade.bat

# look for the installation folder
REP="/opt/masonsql/report/www-data_home_dir/.wine/drive_c"
if [ -d "$REP/Program Files (x86)" ]
then
  REP="$REP/Program Files (x86)/Report Manager"
else
  REP="$REP/Program Files/Report Manager"
fi

# Configuration of an empty Report Manager connections
touch "$REP/dbxdrivers.ini"
chown www-data.www-data "$REP/dbxdrivers.ini"
touch "$REP/dbxconnections.ini"
chown www-data.www-data "$REP/dbxconnections.ini"

# If you installed Report Manager Designer to activate it run:
wine 'C:\Program Files (x86)\Report Manager\repmandxp.exe'