How to use the latest PHPOffice/Spreadsheet with CakePHP 2.x

Preface

Using the latest PHPOfficePhpSpreadsheet in a CakePHP3 application is relatively easy, A simple composer command like composer require phpoffice/phpspreadsheet does the trick in a few seconds and then you ready to go.

All of as Cake uses end up with a PHPExcelComponent that we either borrow or devise ourselves, so we can make our users ... happy by giving them the beloved excel files.

The problem with using PHPOffice/PhpSpreadsheet inside a CakePHP2 application is that CakePHP2 does not support namespaces and so the App::import() is not able to load all the required classes correctly.

This simple howto lists all the steps that I followed in order to make things work, with the help of this question at stackoverflow and this blog post from Mark.

The Steps

1. Download the latest version from here.
2. Extract the zip file and rename the src folder (inside Phpspreadsheet-develop) to PhpOffice
3. Go to your Cake2 project and move/copy this new PhpOffice folder inside your APP/Vendors folder
4. While you are there, create another folder named Psr (inside your APP/Vendors) and an other folder named SimpleCache inside Psr. (You will know why in a minute)
5. PhpSpreadsheet refferences the Fig-Simple cache library that we also have to install manually. Go to the php-fig/simple-cache page and download the required zip file (name is simple-cache-master.zip).
6. Unzip that in a temprary directory and move all three files from the src folder to APP/Vendors/Psr/SimpleCache.
7. Open your APP/Config/bootstrap.php file and place the following code at the end.

       /**
        * Configure the autoloader
        */
       spl_autoload_register( function($class) {
           foreach(App::path('Vendor') as $base) {
               $path = $base . str_replace('\\', DS, $class) . '.php';
               if (file_exists($path)) 
                   return include $path;            
           }
       }, true);
   

8. That's it. If you want to use the Spreadsheet class, all you have to do is place a use PhpOffice\PhpSpreadsheet\Spreadsheet; statement at the top of your file and then any reference like _xls = new Spreadsheet(); will work just fine.

A few afterwords

What we actually accomplished here is that we instructed the php class loader to search for any class it does not know about inside a speciffic sub folder of our APP/Vendors. This subfolder structure, must confirm to the exact package name of the class. Hence we created the PhpOffice/PhpSpreadsheet and Psr/SimpleCache subfolders. Using this technique we may add any additional library into our CakePHP2 application provided that we keep this naming standard. My setup was a CentOS 6.10 web server with PHP 5.6.37 (from Remi).

Once again, CakePHP3 and composer can make this job extremely easier, so unless you really have to, do not go into all this fass ;)

JPA Calling a MySQL stored function

I have a stored function called getNextCode() that requires a string parameter and returns a string. I wish to call this from my JPA enabled application. T he following code fragment shows the use of the crateNativeQuery() method to achieve this.

    private String _getNextRollPackageCode()
    {
        String query = String.format(
                "SELECT getNextCode( '%s')",
                this.ROLL_PACKAGES_MODEL_NAME
        );

        return this.ROLL_PACKAGE_CODE_PREFIX + "-" +  (String) _entityManager.createNativeQuery(query)
                .getSingleResult();
    }

Ubuntu: Setting up an MQTT Server and connecting via SSL

Foreward

This has been a very frustrating story as I was asked to setup an MQTT server that would support -- every IT Manager's pride and joy -- SSL. The setup was quite smooth, thanks to the good people of Digital Ocean. Their post How to Install and Secure the Mosquitto MQTT Messaging Broker on Ubuntu 16.04, explains everything crystal clear.

I also wanted to setup the websockets protocol, so a Javascript client would continue to work. Last but not least be able to programmatically connect using programs written in Java, C# and Python 3. This blog post shows the exact setup we used to make things work, as well as minimal code samples about how to connect.

For the shake of this example, my server DNS name is supposed to be mysite.net, the MQTT server user is mqtt-user and the password we set, is my_Super_Secure_Password.

Server setup and testing

Starting with the setup, I followed Digital Oceans instructions and making a fiew slight changed ended up with this:

# MQTT Config File with websockets and SSL
# Created March - 26th -2018
#

# ----------------------------------------------------------------------------
# Log Setup
# ----------------------------------------------------------------------------
log_type error
log_type warning
log_type notice
log_type information
log_type websockets
websockets_log_level 255
log_timestamp true
# log dest is set in /etc/mosquitto/mosquitto.conf
# web sockets log level can be removed if you verify that websockets is working

# ----------------------------------------------------------------------------
# User Security Setup
# ----------------------------------------------------------------------------
allow_anonymous false
password_file /etc/mosquitto/passwd

# Do not allow non-secure connection from anywhere but the localhost
bind_address    localhost
port            1883

# ----------------------------------------------------------------------------
# This is the SSL Setup
# ----------------------------------------------------------------------------
listener        8883 
protocol        mqtt      

certfile        /etc/letsencrypt/live/mysite.net/cert.pem
cafile          /etc/letsencrypt/live/mysite.net/chain.pem
keyfile         /etc/letsencrypt/live/mysite.net/privkey.pem
require_certificate false

# ----------------------------------------------------------------------------
# Here is our SSL enabled web sockets config
# ----------------------------------------------------------------------------
listener        9993 
protocol        websockets
http_dir        /var/www/mysite.net/mqtt

certfile        /etc/letsencrypt/live/mysite.net/cert.pem
cafile          /etc/letsencrypt/live/mysite.net/chain.pem
keyfile         /etc/letsencrypt/live/mysite.net/privkey.pem
require_certificate false

This is the contents of the /etc/mosquitto/conf.d/default.conf file. To test I switched to my local console and issued the following command:

 mosquitto_sub -h mysite.net -p 8883 -t test -u mqtt_user -P my_Super_Secure_Password --capath /etc/ssl/certs

then opened a second terminal, and each time I would type

mosquitto_pub -h mysite.net -p 8883 -t test -m "Hello Sailor"  -u mqtt_user -P my_Super_Secure_Password --capath /etc/ssl/certs

I would see a Hello Sailor line on the first console screen. That more or less did it

Connecting with Code

This is the part where we completely lost it. All examples in the web show how to perform an SSL connection using your own private key pair but in our case we wanted something much simpler. Our requirement was that the connection be SSL encrypted but we would use the mqtt generated user and password pair. In the Java world, we really got mixed up with keystores, trust manager factories and the like while all it took to post a simple message was :

package com.kfator.mqttclient;

import static java.nio.charset.StandardCharsets.UTF_8;
import java.security.SecureRandom;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.net.ssl.SSLContext;
import org.eclipse.paho.client.mqttv3.MqttClient;
import org.eclipse.paho.client.mqttv3.MqttConnectOptions;
import org.eclipse.paho.client.mqttv3.persist.MemoryPersistence;

public class Main {
    public static final String MQTT_URL = "ssl://mysite.net:8883";
    public static final String MQTT_USER_NAME = "mqtt_user";
    public static final String MQTT_USER_PASSWORD = "my_Super_Secure_Password";

    public static void main(String args[])
    {
        try {
            MqttClient client = new MqttClient(
                    MQTT_URL,
                    MqttClient.generateClientId(),  // ClientId
                    new MemoryPersistence());       // Persistence

            SSLContext sslContext = SSLContext.getInstance("SSL");
            sslContext.init(null, null, new SecureRandom());

            MqttConnectOptions options = new MqttConnectOptions();
            options.setSocketFactory(sslContext.getSocketFactory());
            options.setUserName(MQTT_USER_NAME);
            options.setPassword(MQTT_USER_PASSWORD.toCharArray());

            client.connect(options);
            client.publish(
                "test", // topic
                "Hello from Java".getBytes(UTF_8), // payload
                2, // QoS
                false); // retained?
            client.disconnect();

            System.out.println("Connection established!");
        } catch (Exception ex) {
            Logger.getLogger(Main.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
}

The code runs from a Netbenas 8.2 Maven project. To include the paho-mqtt libraries we (sort of) followed the instructions at MQTT Client Library Encyclopedia – Eclipse Paho Java page. the actual steps were to create an empty Mevn Projet, then right click on the dependencies Node, select the add dependency menu item and fill in the form details as show at the begining of the Paho-Java page.

The C# code using the Mqtt-Paho library for .NET goes like this:

    _clientMonitor = new MqttClient("mysite.net", 8883, true, new X509Certificate(), null, MqttSslProtocols.TLSv1_0);

... and finally, python 3 only requires the use of client.tls_set() before calling connect(). Here is the related fragment from out demo program.

client = mqtt.Client()
client.on_connect = on_connect
client.on_message = on_message
client.username_pw_set(MQTT_USER_NAME, MQTT_PASSWORD)

client.tls_set()
client.connect(HOST, PORT)

And the best part was, that all these worked ...