What I see in Open Source Software in 2019

This could be a short rant in a Twitter post. But this will let me start writing here again.

It seems that OSS developers took a decision about two years ago and the industry too. Applications stopped being cool, communities languished, forums reduced its activity, in part for the consolidation of solutions, the decision of promoting the enterprise versions instead of the community ones and also in part of the allure of cloud, AI and cybersecurity technology market. It seems that OSS developers took a decision about two years ago and the industry too.

For instance PDI/Kettle leader and founder moved to other company and the desktop application has components (email or excel output) that had not been updated for newer versions of the software they are interfacing with. The other one is Sentrifugo a Human resource Web App, where there are no updates or activity from it’s founder in years. And lastly the BOSSIE’s awards has dropped the App category since 2017.

So, consider maintainers of those apps heroes, contribute as you can. Don’t let those products become those abandoned buildings in Instagram, they don’t deserve that.

Pentaho Server 8.1 with MariaDB (MySql)

[Edited on:
– December 2018 to correct config drivers for MariaDB.
– June 13th 2018, to add the solution note to Sql parameters.]

Migrate your Database

The official way to change the database is at pentaho-wiki and pentaho-doc. These are notes to my 2011 post that modifies the DEMO to use MariaDB instead of the HSQLDB that comes with it.

Important: I verified this on a Ubuntu 18.04 and MariaDB 10.1, keeping the  jcr (Jackrabbit repository) in thefilesystem and have a problem with JINDI SQL Queries that I have to substitute with JDBC SQL Queries (see the final note).

I. DATABASE DRIVER

Download the driver [Connector/J mariadb-java-client-2.3.0.jar] to ~/Pentaho8/pentaho-server/tomcat/lib.

If you have the desktop tools, copy the driver also to ~/Pentaho8/report-designer/lib, ~/Pentaho8/data-integration/lib, ~/Pentaho8/shema-workbench/lib.

II. DATABASE CONTENT

Create content in Database

Start the MariaDB engine and run the suplied scripts in ~/Pentaho8/pentaho-server/data/mysql5, to create required databases:

cd ~/Pentaho8/pentaho-server/data/mysql5
 mysql -u root-user -p root-password < create_quartz_mysql.sql
 mysql -u root-user -p root-password < create_repository_mysql.sql

Download the Sample Database (here), unzip and upload it:

mysql -u root-user -p root-password < SampleData_2018_mysql.sql

III. TOMCAT CONFIG

Hibernte Settings

  1. Goto to ~/Pentaho8/server/pentaho-server/pentaho-solutions/system/hibernate
  2. Open hibernate-settings.xml.
  3. Find the  tag config-file and change it to
    system/hibernate/mysql5.hibernate.cfg.xml
  4. Open mysql5.hibernate.cfg.xml change the class drivers
    <property name="connection.driver_class">org.mariadb.jdbc.Driver</property>
    <property name="connection.url">jdbc:mariadb://localhost:3306/hibernate</property>

System connections

  1. Go to ~/Pentaho8/pentaho-server/tomcat/webapps/pentaho/META-INF/
  2. Open context.xml
  3. Change classname, url and SQL query to:
    <?xml version="1.0" encoding="UTF-8"?>
    <Context path="/pentaho" docbase="webapps/pentaho/">
    	<Resource name="jdbc/Hibernate" auth="Container" type="javax.sql.DataSource"
    		factory="org.apache.tomcat.jdbc.pool.DataSourceFactory" maxActive="20" minIdle="0" maxIdle="5" initialSize="0"
    		maxWait="10000" username="hibuser" password="password"
    		driverClassName="org.mariadb.jdbc.Driver" url="jdbc:mariadb://localhost:3306/hibernate"
    		validationQuery="select 1" />
    
    	<Resource name="jdbc/Quartz" auth="Container" type="javax.sql.DataSource"
    		factory="org.apache.tomcat.jdbc.pool.DataSourceFactory" maxActive="20" minIdle="0" maxIdle="5" initialSize="0"
    		maxWait="10000" username="pentaho_user" password="password"
    		driverClassName="org.mariadb.jdbc.Driver" url="jdbc:mariadb://localhost:3306/quartz"
    		validationQuery="select 1"/>
    
    </Context>

Spring config

  1. Go to ~/Pentaho8/pentaho-server/Pentaho-solutions/system/
  2. Open applicationContext-spring-security-hibernate.properties
  3. Change driver, url and dialect to
    jdbc.driver=org.mariadb.jdbc.Driver
    jdbc.url=jdbc:mariadb://localhost:3306/hibernate
    jdbc.username=hibuser
    jdbc.password=password
    hibernate.dialect=org.hibernate.dialect.MySQL5Dialect

Add datasources to jndi list

  1. Go to ~/Pentaho8/pentaho-server/pentaho-solutions/system/simple-jndi
  2. Open jdbc.properties
    1. Change the driver definitions to mysql
      SampleData/type=javax.sql.DataSource
      SampleData/driver=org.mariadb.jdbc.Driver
      SampleData/url=jdbc:mariadb://localhost:3306/sampledata
      SampleData/user=pentaho_user
      SampleData/password=password
      Hibernate/type=javax.sql.DataSource
      Hibernate/driver=org.mariadb.jdbc.Driver
      Hibernate/url=jdbc:mariadb://localhost:3306/hibernate
      Hibernate/user=hibuser
      Hibernate/password=password
      Quartz/type=javax.sql.DataSource
      Quartz/driver=org.mariadb.jdbc.Driver
      Quartz/url=jdbc:mariadb://localhost:3306/quartz
      Quartz/user=pentaho_user
      Quartz/password=password
      SampleDataAdmin/type=javax.sql.DataSource
      SampleDataAdmin/driver=org.mariadb.jdbc.Driver
      SampleDataAdmin/url=jdbc:mariadb://localhost:3306/sampledata
      SampleDataAdmin/user=pentaho_admin
      SampleDataAdmin/password=password

Stop HSQLDB from starting

  1. Go to ~/Pentaho8/pentaho-server/tomcat/webapps/pentaho/WEB-INF/
  2. Open web.xml
  3. Find the word HSQLDB. There are to points to edit. You will delete “–>” on the first line and “<–” on the last, to make it into a comment.In version 8.1 go to lines 25 and 167. They should look like this:
    <!-- [BEGIN HSQLDB DATABASES]
     <context-param>
     <param-name>hsqldb-databases</param-name>
     <param-value>sampledata@../../data/hsqldb/sampledata,hibernate@../../data/hsqldb/hibernate,quartz@../../data/hsqldb/quartz</param-value>
     </context-param>
     [END HSQLDB DATABASES] -->

    and

    <!-- [BEGIN HSQLDB STARTER]
     	<listener>
     	<listener-class>org.pentaho.platform.web.http.context.HsqldbStartupListener</listener-class>
     </listener>
     [END HSQLDB STARTER] -->

IV. Change Web Server Datasources

  1. Start your BI server and log-in  (localhost:8080/pentaho) as Admin.
  2. Click at the left top menu: Manage Data Sources
  3. You need to select SampleDataJDBC data-source and click on the gear icon to open the config window.
  4. Change the database type to MariaDb. Then change the Database Name to sampledata (the database name is in lowercase), port to 3306, type again the password (password). Click on TEST. Click OK if its succesfull.

Here, you can define a connection to your databases or you can return later.

That’s it, you should have a working Server with MariaDB. To test it by using the samples: Browse: Samples → Public → Steel Wheels.

V. Modify your SQL Queries that uses Parameters

But there is a problem if you want to use a query wit SQL with parameters. Maybe there is a missconfiguration in CDA or the repository, or a problem with the MySql driver for a MariaDB engine, as the parameters in the JNDI configuration report an error and do not work.

To get a result I use only SQL JDBC, by supplying these config parameters on each query

driver:    org.mariadb.jdbc.Driver
 url=      jdbc:mariadb://localhost:3306/sampledata?generateSimpleParameterMetadata=true
 user:     pentaho_admin
 password: password

Troubleshoot Log

If you have problems check the log file is ~/Pentaho8/pentaho-server/tomcat/logs/catalina.out its very verbose so look for ERROR lines