Aug
10
2021
--

PostgreSQL PL/Java – A How-To, Part 2: How to Manipulate and Return Tuples

PostgreSQL PL:Java Manipulate Return Tuples

We discussed how to install and create a simple class in the first part of this series where we ran a SELECT and returned one row with one column with a formatted text. Now it’s time to expand and see how to return multiple tuples.
A little disclaimer here; I’m not going to comment much on the Java code because this is not intended to be a Java tutorial. The examples here are just for educational purposes, not intended to be of high performance or used in production!

Returning a Table Structure

This first example will show how we can select and return a table from a PL/Java function. We’ll keep using the table “customer” here and the probing SQL will be:
SELECT * FROM customer LIMIT 10;

I will create a new Java class here called “CustomerResultSet” and the initial code is:
package com.percona.blog.pljava;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.postgresql.pljava.ResultSetHandle;

public class CustomerResultSet implements ResultSetHandle {
	private Connection conn;
	private PreparedStatement stmt;
	private final String m_url = "jdbc:default:connection";
	private final String sql = "select * FROM customer LIMIT 10";

	public CustomerResultSet() throws SQLException {
		conn = DriverManager.getConnection(m_url);
		stmt = conn.prepareStatement(sql);
	}

	@Override
	public void close() throws SQLException {
		stmt.close();
		conn.close();
	}

	@Override
	public ResultSet getResultSet() throws SQLException {
		return stmt.executeQuery();
	}

	public static ResultSetHandle getCustomerPayments() throws SQLException {
		return new CustomerResultSet();
	}
}

Note that we are implementing the org.postgresql.pljava.ResultSetHandle interface provided by PL/Java. We need it because we are returning a complex object and the ResultSetHandle interface is appropriated when we don’t need to manipulate the returned tuples.

Now that we are using PL/Java objects we need to tell the compiler where to find those references and for this first example here we need the pljava-api jar, which in my case happens to be pljava-api-1.6.2.jar. If you remember from the first post I’ve compiled, the PL/Java I’m using here and my JAR file is located at “~/pljava-1_6_2/pljava-api/target/pljava-api-1.6.2.jar” and the compilation command will be:

javac -cp "~/pljava-1_6_2/pljava-api/target/pljava-api-1.6.2.jar" com/percona/blog/pljava/CustomerResultSet.java
jar -c -f /app/pg12/lib/pljavaPart2.jar com/percona/blog/pljava/CustomerResultSet.class

With my new JAR file created, I can then install it into Postgres and create the function “getCustomerLimit10()“:

SELECT sqlj.install_jar( 'file:///app/pg12/lib/pljavaPart2.jar', 'pljavaPart2', true );
SELECT sqlj.set_classpath( 'public', 'pljavaPart2' );
CREATE OR REPLACE FUNCTION getCustomerLimit10() RETURNS SETOF customer AS 'com.percona.blog.pljava.CustomerResultSet.getCustomerLimit10' LANGUAGE java;

The result of the function call is:
test=# SELECT * FROM getCustomerLimit10();
 customer_id | store_id | first_name | last_name |                email                | address_id | activebool | create_date |     last_update     | active 
-------------+----------+------------+-----------+-------------------------------------+------------+------------+-------------+---------------------+--------
           1 |        1 | MARY       | SMITH     | MARY.SMITH@sakilacustomer.org       |          5 | t          | 2006-02-14  | 2006-02-15 09:57:20 |      1
           2 |        1 | PATRICIA   | JOHNSON   | PATRICIA.JOHNSON@sakilacustomer.org |          6 | t          | 2006-02-14  | 2006-02-15 09:57:20 |      1
           3 |        1 | LINDA      | WILLIAMS  | LINDA.WILLIAMS@sakilacustomer.org   |          7 | t          | 2006-02-14  | 2006-02-15 09:57:20 |      1
           4 |        2 | BARBARA    | JONES     | BARBARA.JONES@sakilacustomer.org    |          8 | t          | 2006-02-14  | 2006-02-15 09:57:20 |      1
           5 |        1 | ELIZABETH  | BROWN     | ELIZABETH.BROWN@sakilacustomer.org  |          9 | t          | 2006-02-14  | 2006-02-15 09:57:20 |      1
           6 |        2 | JENNIFER   | DAVIS     | JENNIFER.DAVIS@sakilacustomer.org   |         10 | t          | 2006-02-14  | 2006-02-15 09:57:20 |      1
           7 |        1 | MARIA      | MILLER    | MARIA.MILLER@sakilacustomer.org     |         11 | t          | 2006-02-14  | 2006-02-15 09:57:20 |      1
           8 |        2 | SUSAN      | WILSON    | SUSAN.WILSON@sakilacustomer.org     |         12 | t          | 2006-02-14  | 2006-02-15 09:57:20 |      1
           9 |        2 | MARGARET   | MOORE     | MARGARET.MOORE@sakilacustomer.org   |         13 | t          | 2006-02-14  | 2006-02-15 09:57:20 |      1
          10 |        1 | DOROTHY    | TAYLOR    | DOROTHY.TAYLOR@sakilacustomer.org   |         14 | t          | 2006-02-14  | 2006-02-15 09:57:20 |      1
(10 rows)

test=#

Manipulating the Result Before Returning

Returning the result of a plain SQL has its usage like visibility/permissioning control, but we usually need to manipulate the results of a query before returning, and to do this we can implement the interface “org.postgresql.pljava.ResultSetProvider“.
I will implement a simple method to anonymize sensitive data with a hash function in the following example. I’ll also create a helper class to deal with the hash and cryptographic functions to keep the CustomerResultSet class clean:
/**
 * Crypto helper class that will contain all hashing and cryptographic functions
 */
package com.percona.blog.pljava;

import java.nio.charset.StandardCharsets;
import java.security.MessageDigest;
import java.security.NoSuchAlgorithmException;

public class Crypto {
	MessageDigest digest;
	
	public Crypto() throws NoSuchAlgorithmException {
		digest = MessageDigest.getInstance("SHA-256");
	}
	
	public String bytesToHex(byte[] hash) {
		StringBuilder hexString = new StringBuilder(2 * hash.length);
		for (int i = 0; i < hash.length; i++) {
			String hex = Integer.toHexString(0xff & hash[i]);
			if (hex.length() == 1) {
				hexString.append('0');
			}
			hexString.append(hex);
		}
		return hexString.toString();
	}
	
	public String encode(String data, int min, int max) {
		double salt = Math.random();
		int sbstring = (int) ((Math.random() * ((max - min) + 1)) + min);

		return bytesToHex(digest.digest((data + salt).getBytes(StandardCharsets.UTF_8))).substring(0, sbstring);
	}
}

/**
 * CustomerHash class
 */
package com.percona.blog.pljava;

import java.security.NoSuchAlgorithmException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.logging.Level;
import java.util.logging.Logger;

import org.postgresql.pljava.ResultSetProvider;

public class CustomerHash implements ResultSetProvider {
	private final Connection conn;
	private final PreparedStatement stmt;
	private final ResultSet rs;
	private final Crypto crypto;
	
	private final String m_url = "jdbc:default:connection";

	public CustomerHash(int id) throws SQLException, NoSuchAlgorithmException {
		String query;
		
		crypto = new Crypto();
		query = "SELECT * FROM customer WHERE customer_id = ?";
		conn = DriverManager.getConnection(m_url);
		stmt = conn.prepareStatement(query);
		stmt.setInt(1, id);
		rs = stmt.executeQuery();
	}
	
	@Override
	public void close() throws SQLException {
		stmt.close();
		conn.close();
	}
	
	@Override
	public boolean assignRowValues(ResultSet receiver, int currentRow) throws SQLException {		
		if (!rs.next())
			return false;
		
		try {
			receiver.updateInt(1, rs.getInt("customer_id"));
			receiver.updateInt(2, rs.getInt("store_id"));
			receiver.updateString(3, crypto.encode(rs.getString("first_name"), 5, 45));
			receiver.updateString(4, crypto.encode(rs.getString("last_name"), 5, 45));
			receiver.updateString(5, crypto.encode(rs.getString("email"), 5, 41) + "@mail.com");
			receiver.updateInt(6, rs.getInt("address_id"));
			receiver.updateBoolean(7, rs.getBoolean("activebool"));
			receiver.updateDate(8, rs.getDate("create_date"));
			receiver.updateTimestamp(9, rs.getTimestamp("last_update"));
			receiver.updateInt(10, rs.getInt("active"));
			
		} catch (Exception e) {
			Logger.getAnonymousLogger().log(Level.parse("SEVERE"), e.getMessage());
		}
		return true;
	}
	
	public static ResultSetProvider getCustomerAnonymized(int id) throws SQLException, NoSuchAlgorithmException {
		return new CustomerHash(id);
	}

}

The number of classes is increasing, so instead of mentioning them one by one let’s just use the “.java” to build the classes and the “.class” to create the jar:
javac -cp "~/pljava-1_6_2/build/pljava-api-1.6.2.jar" com/percona/blog/pljava/*.java
jar -c -f /app/pg12/lib/pljavaPart2.jar com/percona/blog/pljava/*.class

Remember that every time we change our JAR file we need to also reload it into Postgres. Check the next example and you’ll see that I’m reloading the JAR file, creating and testing our new function/method:
test=# SELECT sqlj.replace_jar( 'file:///app/pg12/lib/pljavaPart2.jar', 'pljavaPart2', true );
 replace_jar 
-------------
 
(1 row)

test=# CREATE OR REPLACE FUNCTION getCustomerAnonymized(int) RETURNS SETOF customer AS 'com.percona.blog.pljava.CustomerHash.getCustomerAnonymized' LANGUAGE java;
CREATE FUNCTION

test=# SELECT * FROM getCustomerAnonymized(9);
 customer_id | store_id |     first_name      |              last_name              |                  email                  | address_id | activebool | create_date |     last_update     | ac
tive 
-------------+----------+---------------------+-------------------------------------+-----------------------------------------+------------+------------+-------------+---------------------+---
-----
           9 |        2 | 72e2616ef0075e81929 | 3559c00ee546ae0062460c8faa4f24960f1 | 24854ed40ed42b57f077cb1cfaf916@mail.com |         13 | t          | 2006-02-14  | 2006-02-15 09:57:20 |   
   1
(1 row)

test=#

Great! We now have a method to anonymize data!

Triggers

The last topic of this second part will be about “triggers”, and to make it a bit more interesting we will create a trigger to encrypt the sensitive data of our table. The anonymization using the hash function in the previous example is great, but what happens if we have unauthorized access to the database? The data is saved in plain text!
To make this example as small as possible I won’t bother with securing the keys, as we will do it in part three of this series when we’ll use Java to access external resources using Vault to secure our keys, so stay tuned!
Ok, the first thing we need to do is to create the pair of keys we need to encrypt/decrypt our data. I’ll use “OpenSSL” to create them and gonna store them into a table named “keys”!
openssl genrsa -out keypair.pem 2048
openssl pkcs8 -topk8 -nocrypt -in keypair.pem -outform PEM -out private.pem
openssl rsa -in keypair.pem -outform PEM -pubout -out public.pem

Now that we have the keys we need to sanitize the key files to remove the header and footer data from both the private and public keys, and also remove all break-lines, or else our Java code will complain:
echo -n "CREATE TABLE keys(id int primary key, priv varchar not null, pub varchar not null); INSERT INTO keys VALUES(1, '" > keys.sql
cat private.pem | sed '1d;$d' | sed ':a;N;$!ba;s/\n//g' | tr -d '\n' >> keys.sql
echo -n "', '" >> keys.sql
cat public.pem | sed '1d;$d' | sed ':a;N;$!ba;s/\n//g' | tr -d '\n' >> keys.sql
echo -n "');" >> keys.sql

psql test < keys.sql

It will look like this when sanitized:
CREATE TABLE keys(id int primary key, priv varchar not null, pub varchar not null); INSERT INTO keys VALUES(1, 'MIIEvAIBADANBgkqhkiG9w0BAQEFAASCBKYwggSiAgEAAoIBAQCiAA4BE64JZpXwIGfsUanyL//drIcFZ1cmiCW6zWOxc6nL8AQ33MPyQup8g/ociJFGn/eEEYOvRMV2pVNo3qB3VQU4WHRWkq22x7mRfuhHmAnAJA3dic5fiJ1aCQgo7tEqlGPc0WqL+jMUXh6Wmktq1kDZagUGJRorw0f5Iaj60PycbGtgKaKDc4VHepkN1jl0rhpJBzjBehuvB88LLXJ/cHsMOp3q569jLsHtqymCA2wP68ldtfKtOowPW9togIUmgWY0Z2lWlefrlzmT2g3L/oYbPUxCmptOAMFD8NajdA518ohZAC8SPfUsD4CwL89oPrMZlX4RkTuc5UvBHiKrAgMBAAECggEAcJl5ImZ7YS1cqjrcAPYCGcQjJAD3GFpryOx4zQ5VbNHoA0ggpnNb/tdkBIf3ID4MO/qUH8fMr9YtKfpfr1SOVGNT7YYN1t68v36zDN4YtSqIHHTy7jkKqHxcYmhEs67K072wa5tjY0fUmSOSPzufj/K7wGJge5TuS9y/+fnbafkdfW/yz3X2YXL6T/jfjqI4h+P7Nhh5hlpD1KZfEWTAY5B5tBoLc4xaTIB8FTLclVWw3CAW8h60EwUAkyxCSbrP2I1FCrWsV6hJGy8U+hUQJUpyDdum9ZC1oAVewRrCkSH0ZP1XaQifDZoRv/1N7cCbQqaLJaVk4rzVOEv0DoCEAQKBgQDOMPMm2ioEredCx0hfmHWWayGA5as7VPDSzv1QH3g4AdjZFf2YxctXGNJNMpfqVvFiQCWxp9NpjUPpODRbmR2J+7tYgx3B445zDeXdBH2JTKhUgNTHjL6DrM6FTI3yaSsSJ77L0mDcFQ42nfWtfqkZd5lYfwiVC0eL86bp408+YQKBgQDJIks6RqVlDbHerIIqT1ToN+CQ+BCjx/Z6sk4BFIKBB8sU8VyVkZlvQpFHvT06oE/1NbyiQ3nVufGrm0kwMqx7MXGiA670E1Q+Q/mQ12uRByUlgd+LW4mp1Y6tln1lpP5pVqUOC/jtnXYQmEReU4Ye24E4AZhFU23J+oYoh3XEiwKBgEJFaWFrbWXjnxjPhGt1TRXziOks6ERBoMWg0boW40TdEx1y+/dGW3y69ZzqTfl7yEmT5ImdL04VoWYsMmfeZqgayLRCMCZJRVeld+P5tX+Tq+a9Iaahjfo0aIxfdqAbPUSwkZphG9Cg09iqHHSO6TrOPfM7oT6GSZCp11QFQ0sBAoGAeABi+8D8mx8hmWY5Pv8X/HiiHjwyyVTbpPbO/Wv8NPmuW69per9k2PHRdgjdCCZvrjBCfFlfznljS+yZLQ1+xP2J+4zRDESgBYpO0vED94JY0lj7Q8z4hICq4Lyh0kwvki+kyI2yFirVLy950wFoSu7R2NVywSH2pgQ3mOTBCeMCgYBL5KIRf1qwsCYaCggPls4pWKMjfxxO915h26/aaniEYaTNnhXRSRwkVOWoGHoUKfrqQdrvj/y5lgezn7mZM0CvnB6ZkGwDXxpcIYUnhR1Lnp3HNSqfigg+WjQASVCKuq3YUri3p+KQkrpED/O3B4FJW2Q4IReEuREEsKNkeH96ew==', 'MIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEAogAOAROuCWaV8CBn7FGp8i//3ayHBWdXJoglus1jsXOpy/AEN9zD8kLqfIP6HIiRRp/3hBGDr0TFdqVTaN6gd1UFOFh0VpKttse5kX7oR5gJwCQN3YnOX4idWgkIKO7RKpRj3NFqi/ozFF4elppLatZA2WoFBiUaK8NH+SGo+tD8nGxrYCmig3OFR3qZDdY5dK4aSQc4wXobrwfPCy1yf3B7DDqd6uevYy7B7aspggNsD+vJXbXyrTqMD1vbaICFJoFmNGdpVpXn65c5k9oNy/6GGz1MQpqbTgDBQ/DWo3QOdfKIWQAvEj31LA+AsC/PaD6zGZV+EZE7nOVLwR4iqwIDAQAB');

After done with populating the table we should have a nice table with both private and public keys. Now is the time to create our Java classes. I’ll reuse the “Crypto” class for the cryptographic functions and create a new class to add our trigger functions. I’ll only add the relevant part of the Crypto class here, but you can find the code described here on my GitHub page here[1] including Part One (and Part Three) when released. Let’s get to the code:
/**
 * This is the relevant part of the Crypto class that will encrypt and decrypt our data using the certificates we generated above.
 */
	public PublicKey getPublicKey(String base64PublicKey) {
		PublicKey publicKey = null;
		try {
			X509EncodedKeySpec keySpec = new X509EncodedKeySpec(Base64.getDecoder().decode(base64PublicKey.getBytes()));
			KeyFactory keyFactory = KeyFactory.getInstance("RSA");
			publicKey = keyFactory.generatePublic(keySpec);
			return publicKey;
		} catch (NoSuchAlgorithmException e) {
			e.printStackTrace();
		} catch (InvalidKeySpecException e) {
			e.printStackTrace();
		}
		return publicKey;
	}

	public PrivateKey getPrivateKey(String base64PrivateKey) {
		PrivateKey privateKey = null;
		PKCS8EncodedKeySpec keySpec = new PKCS8EncodedKeySpec(Base64.getDecoder().decode(base64PrivateKey.getBytes()));
		KeyFactory keyFactory = null;
		try {
			keyFactory = KeyFactory.getInstance("RSA");
		} catch (NoSuchAlgorithmException e) {
			e.printStackTrace();
		}
		try {
			privateKey = keyFactory.generatePrivate(keySpec);
		} catch (InvalidKeySpecException e) {
			e.printStackTrace();
		}
		return privateKey;
	}

	public String encrypt(String data, PublicKey publicKey) throws BadPaddingException, IllegalBlockSizeException,
			InvalidKeyException, NoSuchPaddingException, NoSuchAlgorithmException {
		Cipher cipher = Cipher.getInstance("RSA/ECB/PKCS1Padding");
		cipher.init(Cipher.ENCRYPT_MODE, publicKey);
		return Base64.getEncoder().encodeToString(cipher.doFinal(data.getBytes()));
	}
	
	public String decrypt(String data, PrivateKey privateKey) throws NoSuchPaddingException,
			NoSuchAlgorithmException, InvalidKeyException, BadPaddingException, IllegalBlockSizeException {
		Cipher cipher = Cipher.getInstance("RSA/ECB/PKCS1Padding");
		cipher.init(Cipher.DECRYPT_MODE, privateKey);
		return new String(cipher.doFinal(Base64.getDecoder().decode(data)));
	}

Now we can implement the class with both functions – the trigger function to encrypt and a function to decrypt when we need to SELECT the data:
package com.percona.blog.pljava;

import java.security.InvalidKeyException;
import java.security.NoSuchAlgorithmException;
import java.security.PrivateKey;
import java.security.PublicKey;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.logging.Level;
import java.util.logging.Logger;

import javax.crypto.BadPaddingException;
import javax.crypto.IllegalBlockSizeException;
import javax.crypto.NoSuchPaddingException;

import org.postgresql.pljava.ResultSetProvider;
import org.postgresql.pljava.TriggerData;

public class CustomerCrypto implements ResultSetProvider {
	private final String m_url = "jdbc:default:connection";
	private final Connection conn;
	private PreparedStatement stmt;
	private ResultSet rs;

	//
	private PrivateKey privateKey;
	private PublicKey publicKey;

	public CustomerCrypto() throws SQLException, NoSuchAlgorithmException {
		String query;

		query = "SELECT * FROM keys WHERE id = 1";
		conn = DriverManager.getConnection(m_url);
		stmt = conn.prepareStatement(query);
		rs = stmt.executeQuery();
		if (!rs.next())
			throw new SQLException("Keys not found!");

		privateKey = Crypto.getPrivateKey(rs.getString("priv"));
		publicKey = Crypto.getPublicKey(rs.getString("pub"));
	}
	
	public void processQuery(int id) throws SQLException, NoSuchAlgorithmException {
		String query;
		query = "SELECT * FROM customer WHERE customer_id = ?";
		stmt = conn.prepareStatement(query);
		stmt.setInt(1, id);
		rs = stmt.executeQuery();
	}

	@Override
	public void close() throws SQLException {
		stmt.close();
		conn.close();
	}

	public static int getLineNumber() {
		return Thread.currentThread().getStackTrace()[2].getLineNumber();
	}

	@Override
	public boolean assignRowValues(ResultSet receiver, int currentRow) throws SQLException {
		if (!rs.next())
			return false;

		try {
			receiver.updateInt(1, rs.getInt("customer_id"));
			receiver.updateInt(2, rs.getInt("store_id"));
			receiver.updateString(3, Crypto.decrypt(rs.getString("first_name"), this.privateKey));
			receiver.updateString(4, Crypto.decrypt(rs.getString("last_name"), this.privateKey));
			receiver.updateString(5, Crypto.decrypt(rs.getString("email"), this.privateKey));
			receiver.updateInt(6, rs.getInt("address_id"));
			receiver.updateBoolean(7, rs.getBoolean("activebool"));
			receiver.updateDate(8, rs.getDate("create_date"));
			receiver.updateTimestamp(9, rs.getTimestamp("last_update"));
			receiver.updateInt(10, rs.getInt("active"));

		} catch (Exception e) {
			Logger.getAnonymousLogger().log(Level.parse("SEVERE"), e.getMessage());
		}
		return true;
	}
	
	private void encryptData(TriggerData td) throws InvalidKeyException, BadPaddingException, IllegalBlockSizeException, NoSuchPaddingException, NoSuchAlgorithmException, SQLException {
		ResultSet _new = td.getNew();
		
		_new.updateString("first_name", Crypto.encrypt(_new.getString("first_name"), this.publicKey));
		_new.updateString("last_name", Crypto.encrypt(_new.getString("last_name"), this.publicKey));
		_new.updateString("email", Crypto.encrypt(_new.getString("email"), this.publicKey));
	}
	
	public static void customerBeforeInsertUpdate(TriggerData td) throws SQLException, InvalidKeyException, BadPaddingException, IllegalBlockSizeException, NoSuchPaddingException, NoSuchAlgorithmException {
		CustomerCrypto ret = new CustomerCrypto();
		ret.encryptData(td);
	}

	public static ResultSetProvider getCustomerCrypto(int id) throws SQLException, NoSuchAlgorithmException {
		CustomerCrypto ret = new CustomerCrypto();
		ret.processQuery(id);
		
		return ret;
	}

}

The relevant parts of the code above are the “customerBeforeInsertUpdate” and “encryptData” methods, the former being the static method the database will access. The PL/Java on Postgres expects to find a static method with “void (TriggerData)” signature. It will call the “encryptData” method of the “CustomerCrypto” object to do the job. The “encryptData” method will recover the resultset from the “NEW” pointer that is passed through the “TriggerData” object and then change the value to crypt the data. We need to call the trigger in the “BEFORE” event because we need to crypt it before it is persisted.
Another important method is the “getCustomerCrypto“. We need to be able to get the data decrypted and this method will help us. Here, we use the same technique we used in the previous example where we implemented the “ResultSetProvider” interface and manipulated the data before returning the resultset. Take a closer look at the “assignRowValues” method and you’ll see that we are decrypting the data there with “Crypto.decrypt” method!
Ok, time to compile the code and check if it really works:
javac -cp "/v01/proj/percona/blog/pljava/pljava-1_6_2/build/pljava-api-1.6.2.jar" com/percona/blog/pljava/*.java
jar -c -f /app/pg12/lib/pljavaPart2.jar com/percona/blog/pljava/*.class

And create the database objects:
SELECT sqlj.replace_jar( 'file:///app/pg12/lib/pljavaPart2.jar', 'pljavaPart2', true );

CREATE FUNCTION customerBeforeInsertUpdate()
			RETURNS trigger
			AS 'com.percona.blog.pljava.CustomerCrypto.customerBeforeInsertUpdate'
			LANGUAGE java;

CREATE TRIGGER tg_customerBeforeInsertUpdate
			BEFORE INSERT ON customer
			FOR EACH ROW
			EXECUTE PROCEDURE customerBeforeInsertUpdate();

At this point, our data isn’t encrypted yet but we can do it with a noop update and the trigger will do its magic:
test=# SELECT * FROM customer LIMIT 3;
 customer_id | store_id | first_name | last_name |                email                | address_id | activebool | create_date |     last_update     | active 
-------------+----------+------------+-----------+-------------------------------------+------------+------------+-------------+---------------------+--------
           1 |        1 | MARY       | SMITH     | MARY.SMITH@sakilacustomer.org       |          5 | t          | 2006-02-14  | 2006-02-15 09:57:20 |      1
           2 |        1 | PATRICIA   | JOHNSON   | PATRICIA.JOHNSON@sakilacustomer.org |          6 | t          | 2006-02-14  | 2006-02-15 09:57:20 |      1
           3 |        1 | LINDA      | WILLIAMS  | LINDA.WILLIAMS@sakilacustomer.org   |          7 | t          | 2006-02-14  | 2006-02-15 09:57:20 |      1
(3 rows)

test=# UPDATE customer SET first_name = first_name, last_name = last_name, email = email;
UPDATE 599

test=# SELECT * FROM customer LIMIT 3;
 customer_id | store_id |                                                                                                                                                                       
 first_name                                                                                                                                                                        |            
                                                                                                                                                            last_name                           
                                                                                                                                              |                                                 
                                                                                                                         email                                                                  
                                                                                                         | address_id | activebool | create_date |        last_update         | active 
-------------+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------+------------+------------+-------------+----------------------------+--------
           3 |        1 | DT8oXb0VQvtFSIOv61zZfmUrpjWWGoeyl8D8tQl7naCLT31WlJn3U+uILYqUedSOdDSO17QdQKwChWG+DrcvYEih9RYyEPR2ja9deN4cn+vwHt/v09HDfmwrsJUt0UgP/fp78hCxkJDAV50KkMUsA23aeH5HRn9nCHOH0P
AcuId+7acCgwvU9YP8Sx2KVeVnLaBrzpeRLrsmAczQLUAXilXfdFC8uT2APBfwx1So2eCe+kSOsjcu1yTwlsa95Dnfu/N++Zm1D4knKUrAuNm5svTHjIz+B4HKXFMPz/Yk7KYF6ThB6OshIomyRvSEtKu5torfdwAvT3tsgP2DLWiKgQ== | H0YRoi10z36
tnNSXpBs/oYfMQRbAhfUYLIcE885Dhxmy2mbuhecCCqPcye5/++MhUwmEQG2pBgfsqWHLOnAgbqjaG3O0reipVysYK7cMysX1w5RVINsyD5H3vCqgnHESfdRuhW3b00InkR2qCtBYX1QJ1tKJZz89D2AOjoTq5jTum00vcLT06h6ZxVh1RKLNAuGpY9qN57m
/9a4JZuff9poYjw2PPQ6kTxhtbFl3bw+B3sJUcLFuFMYUoAAHsVETQRAerH1ncG9Uxi+xQjUtTVBqZdjvED+eydetH7vsnjBuYDtXD9XAn14qmALx5NfvwpU5jfpMOPOM4xP1BRVA2Q== | DpWBfhhii4LRPxZ9XJy8xoNne+qm051wD5Gd9AMHc+oIhx/B
ln6H+lcAM3625rKN1Vw/lG6VkQo2EnoZz/bhFtULvAOAUiBxerBDbYe0lYWqI50NxnFJbkexMkjDSiuoglh3ilRBn6Z+WGLc7FfEprOd1+tULW2gcwLI68uoEhfSY7INQZuGXfOUMAM4roB2fWvEfylL1ShbiGTRjX7KGXQbXLJtm7xel8J2VhdCecXzxzY2
Mtnu3EXGNpFy9atTXxE/fI0C5AX/u2FDZiOHz9xV7sB3atcqAeXwJB0smpBnPbwI3BN+ptzsWnhyFNNS+ol4QayMDgFhi/tp2+lCAQ== |          7 | t          | 2006-02-14  | 2021-08-08 19:10:29.337653 |      1
           4 |        2 | jo3zKr6lJ5zMN5f3/BPgENhs9CdzDu7F/uFxAf6uO9MAKc7X+++ipk/OBbvvA8vpaJ7DTgph9LshRvpYIqPMwS6IubkScDOSRDoLBNW9z2oMF3dB46R86LK0pTEVVrGaddjnPzaAEh7Uwzy3LncC1y7pJqGKW1b3RGUE8n
4SgstMo/4tRNUe/AUcPn9FXkCoc5jFvn8gPwVoTRKwhYu0oTco2gNKZs1rmFdmkmobGaMjZuhWyWG2PO1oXIVPkpgILmD42yAMDxWkS4DVvgJChXJRukjBzfOitsQwHapjqPqf/q3mfBaQzNUwROcSfGBe6KlI5yfjWU309KRKaCYWNQ== | MMhPovG/N3k
Xjou6kS9V7QtEvlA5NS8/n62KVRVVGEnsh5bhwEhBZxlK72AQv8e4niATWPEiJJU6i7Z08NkU5FWNIvuWwlGTdEEW+kK7XQXib6cNAdnmo4RH72SWhDxEp3tMwwoZif2932H8WDEbNdP6bCP69ekBA7Z+nGtXaeh+H9BAaLf1e6XunBj2YN7zs4sFWB2Kxs2
IugLWd9g9677BWzUeJIzpJfVLro4HYlzASh9AMKb8wPRU0LlEpxtqUdejj7IY5M1hVhDTCCLSQjSqJscqzG1pYQ04W7KNdGwWxJPMjvyPC2K4H+HQuW0IWVjvFpyYd/5q1eIQX+vjdw== | oF4nyIjtVtWuydg6QiSg3BDadWe48nhbBEZSLrR5GVigA768
E3n1npN6sdstzG7bRVnphtfwIZwQ3MUFURWCbUCe0VqioNgVXFhiRvr3DAw2AH64ss/h65B2U5whAygnq4kiy5JvPD0z0omtfs9913QeoO+ilvPVLEc0q3n0jD9ZQlkNVfHSytx1NY86gWnESquTVhkVQ55QDV8GY70YLX9V6nU7ldu+zpNLmf2+rfpxqbRC
i16jnHGDcTT7CKeq+AxbiJDeaaAmSPpxTZsrX4sXFW4rpNtSmOyuyHZziy8rkN8xSpyhvrmxjC7EYe4bn6L/+hay108Wn0BSFYe2ow== |          8 | t          | 2006-02-14  | 2021-08-08 19:10:29.337653 |      1
<...>
(3 rows)

test=#

Awesome, we get our data encrypted! What about the “decrypt” part of the class? Let’s check it out:
test=# CREATE OR REPLACE FUNCTION getCustomerCrypto(int) RETURNS SETOF customer AS 'com.percona.blog.pljava.CustomerCrypto.getCustomerCrypto' LANGUAGE java;
CREATE FUNCTION

test=# SELECT * FROM getCustomerCrypto(10);
 customer_id | store_id | first_name | last_name |               email               | address_id | activebool | create_date |     last_update     | active 
-------------+----------+------------+-----------+-----------------------------------+------------+------------+-------------+---------------------+--------
          10 |        1 | DOROTHY    | TAYLOR    | DOROTHY.TAYLOR@sakilacustomer.org |         14 | t          | 2006-02-14  | 2006-02-15 09:57:20 |      1
(1 row)

test=#

Worked like a charm! Here we finish part two and at this point, we are able to query and manipulate objects inside of our database. The next and last article of this series will cover external calls, and we’ll see how to use external resources from PL/Java. Don’t miss it!

[1] https://github.com/elchinoo/blogs/tree/main/pljava

Percona Distribution for PostgreSQL provides the best and most critical enterprise components from the open-source community, in a single distribution, designed and tested to work together.

Download Percona Distribution for PostgreSQL Today!

Jul
29
2021
--

PostgreSQL PL/Java – A How-To, Part 1

PostgreSQL PL:Java

We’ve recently received some questions regarding PL/Java and I found it hard to get clear instructions searching on the internet. It’s not that there is no good information out there, but most of it is either incomplete, outdated, or confusing and I decided to create this short “how-to” and show how to install it and how to get it running with few examples.

Installation

I will show here how to install it from sources, first because my platform doesn’t have the compiled binaries, and second because if your platform has the binaries from the package manager you can just install it from there, for example using YUM or APT. Also, note that I’m using PL/Java without the designation “TRUSTED” and a Postgres database superuser for simplicity. I would recommend reading the documentation about users and privileges here[1].

The versions of the software I’m using here are:

  • PostgreSQL 12.7
  • PL/Java 1.6.2
  • OpenJDK 11
  • Apache Maven 3.6.3

I downloaded the sources from “https://github.com/tada/pljava/releases“, unpackaged and compiled with maven:

wget https://github.com/tada/pljava/archive/refs/tags/V1_6_2.tar.gz
tar -xf V1_6_2.tar.gz
cd pljava-1_6_2
mvn clean install
java -jar pljava-packaging/target/pljava-pg12.jar

I’ll assume here that you know maven enough and won’t go through the “mvn” command. The “java -jar pljava-packaging/target/pljava-pg12.jar” will copy/install the needed files and packages into Postgres folders. Note that maven used my Postgres version and created the jar file with the version: “pljava-pg12.jar“, so pay attention to the version you have there as the jar file will change if you have a different Postgres version!

I can now create the extension into the database I will use it. I’m using the database “demo” in this blog:

$ psql demo
psql (12.7)
Type "help" for help.

demo=# CREATE EXTENSION pljava;
WARNING: Java virtual machine not yet loaded
DETAIL: libjvm: cannot open shared object file: No such file or directory
HINT: SET pljava.libjvm_location TO the correct path to the jvm library (libjvm.so or jvm.dll, etc.)
ERROR: cannot use PL/Java before successfully completing its setup
HINT: Check the log for messages closely preceding this one, detailing what step of setup failed and what will be needed, probably setting one of the "pljava." configuration variables, to complete the setup. If there is not enough help in the log, try again with different settings for "log_min_messages" or "log_error_verbosity".

Not exactly what I was expecting but I got a good hint: “HINT: SET pljava.libjvm_location TO the correct path to the jvm library (libjvm.so or jvm.dll, etc.)“. Ok, I had to find the libjvm my system is using to configure Postgres. I used the SET command to do it online:

demo=# SET pljava.libjvm_location TO '/usr/lib/jvm/java-11-openjdk-11.0.11.0.9-5.fc34.x86_64/lib/server/libjvm.so';
NOTICE: PL/Java loaded
DETAIL: versions:
PL/Java native code (1.6.2)
PL/Java common code (1.6.2)
Built for (PostgreSQL 12.7 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 11.1.1 20210531 (Red Hat 11.1.1-3), 64-bit)
Loaded in (PostgreSQL 12.7 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 11.1.1 20210531 (Red Hat 11.1.1-3), 64-bit)
OpenJDK Runtime Environment (11.0.11+9)
OpenJDK 64-Bit Server VM (11.0.11+9, mixed mode, sharing)
NOTICE: PL/Java successfully started after adjusting settings
HINT: The settings that worked should be saved (using ALTER DATABASE demo SET ... FROM CURRENT or in the "/v01/data/db/pg12/postgresql.conf" file). For a reminder of what has been set, try: SELECT name, setting FROM pg_settings WHERE name LIKE 'pljava.%' AND source = 'session'
NOTICE: PL/Java load successful after failed CREATE EXTENSION
DETAIL: PL/Java is now installed, but not as an extension.
HINT: To correct that, either COMMIT or ROLLBACK, make sure the working settings are saved, exit this session, and in a new session, either: 1. if committed, run "CREATE EXTENSION pljava FROM unpackaged", or 2. if rolled back, simply "CREATE EXTENSION pljava" again.
SET

Also used the “ALTER SYSTEM” to make it persistent across all my databases as it writes the given parameter setting to the “postgresql.auto.conf” file, which is read in addition to “postgresql.conf“:

demo=# ALTER SYSTEM SET pljava.libjvm_location TO '/usr/lib/jvm/java-11-openjdk-11.0.11.0.9-5.fc34.x86_64/lib/server/libjvm.so';
ALTER SYSTEM

Now we have it installed we can check the system catalog if it is indeed there:

demo=# SELECT * FROM pg_language WHERE lanname LIKE 'java%';
oid    | lanname | lanowner | lanispl | lanpltrusted | lanplcallfoid | laninline | lanvalidator | lanacl
-------+---------+----------+---------+--------------+---------------+-----------+--------------+-------------------
16428  | java    | 10       | t       | t            | 16424         | 0         | 16427        | {charly=U/charly}
16429  | javau   | 10       | t       | f            | 16425         | 0         | 16426        |
(2 rows)

And test if it is working:

demo=# CREATE FUNCTION getProperty(VARCHAR)
RETURNS VARCHAR
AS 'java.lang.System.getProperty'
LANGUAGE java;
CREATE FUNCTION
demo=# SELECT getProperty('java.version');
getproperty
-------------
11.0.11
(1 row)

It’s working! Time to try something useful.

Accessing Database Objects with PL/Java

The majority of the examples I found showed how to do a “hello world” from a Java class or how to calculate a Fibonacci sequence but nothing how to access database objects. Well, nothing wrong with those examples but I suppose that one who installs PL/Java in his database would like to access database objects from inside of a Java function and this is what we gonna do here.

I will use the sample database “pagila” that can be found here[2] for our tests in this post.

For this first example, I will create a simple class with a static method that will be accessed outside like any Postgres function. The function will receive an integer argument and use it to search the table “customer”, column “customer_id” and will print the customer’s id, full name, email,  and address:

package com.percona.blog.pljava;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class Customers {
	private static String m_url = "jdbc:default:connection";

	public static String getCustomerInfo(Integer id) throws SQLException {
		Connection conn = DriverManager.getConnection(m_url);
		String query = "SELECT c.customer_id, c.last_name ||', '|| c.first_name as full_name, "
				+ " c.email, a.address, ci.city, a.district "
				+ " FROM customer c"
				+ "	 JOIN address a on a.address_id = c.address_id "
				+ "	JOIN city ci on ci.city_id = a.city_id "
				+ " WHERE customer_id = ?";

		PreparedStatement stmt = conn.prepareStatement(query);
		stmt.setInt(1, id);
		ResultSet rs = stmt.executeQuery();
		rs.next();
		String ret; 
		ret = "- ID: " + rs.getString("customer_id") ;
		ret += "\n- Name: " + rs.getString("full_name");
		ret += "\n- Email: " + rs.getString("email");
		ret += "\n- Address: " + rs.getString("address");
		ret += "\n- City: " + rs.getString("city");
		ret += "\n- District: " + rs.getString("district");
		ret += "\n--------------------------------------------------------------------------------";

		stmt.close();
		conn.close();

		return (ret);
	}
}

I’ve compiled and created the “jar” file manually with the below commands:

javac com/percona/blog/pljava/Customers.java
jar -c -f /app/pg12/lib/demo.jar com/percona/blog/pljava/Customers.class

Note that I’ve created the jar file inside the folder “/app/pg12/lib”, keep notes because we’ll use this information in the next step, loading the jar file inside Postgres:

demo=# SELECT sqlj.install_jar( 'file:///app/pg12/lib/demo.jar', 'demo', true );
 install_jar 
-------------
(1 row)

demo=# SELECT sqlj.set_classpath( 'public', 'demo' );
 set_classpath 
---------------
(1 row)

The install_jar function has the signature “install_jar(<jar_url>, <jar_name>, <deploy>)” and it loads a jar file from a location appointed by an URL into the SQLJ jar repository. It is an error if a jar with the given name already exists in the repository or if the jar doesn’t exist in the URL or the database isn’t able to read it:

demo=# SELECT sqlj.install_jar( 'file:///app/pg12/lib/demo2.jar', 'demo', true );
ERROR:  java.sql.SQLException: I/O exception reading jar file: /app/pg12/lib/demo2.jar (No such file or directory)
demo=# SELECT sqlj.install_jar( 'file:///app/pg12/lib/demo.jar', 'demo', true );
 install_jar 
------------- 
(1 row)

demo=# SELECT sqlj.install_jar( 'file:///app/pg12/lib/demo.jar', 'demo', true );
ERROR:  java.sql.SQLNonTransientException: A jar named 'demo' already exists

The function set_classpath defines a classpath for the given schema, in this example the schema “public”. A classpath consists of a colon-separated list of jar names or class names. It’s an error if the given schema does not exist or if one or more jar names references non-existent jars.

The next step is to create the Postgres functions:

demo=# CREATE FUNCTION getCustomerInfo( INT ) RETURNS CHAR AS 
    'com.percona.blog.pljava.Customers.getCustomerInfo( java.lang.Integer )'
LANGUAGE java;
CREATE FUNCTION

We can now use it:

demo=# SELECT getCustomerInfo(100);
                                 getcustomerinfo                                  
----------------------------------------------------------------------------------
 - ID: 100                                                                       +
 - Name: HAYES, ROBIN                                                            +
 - Email: ROBIN.HAYES@sakilacustomer.org                                         +
 - Address: 1913 Kamakura Place                                                  +
 - City: Jelets                                                                  +
 - District: Lipetsk                                                             +
 --------------------------------------------------------------------------------
(1 row)

Sweet, we have our first Java function inside our Postgres demo database.

Now, in our last example here I will add another method to this class, now to list all the payments from a given customer and calculate its total:

package com.percona.blog.pljava;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class Customers {
	private static String m_url = "jdbc:default:connection";

	public static String getCustomerInfo(Integer id) throws SQLException {
		Connection conn = DriverManager.getConnection(m_url);
		String query = "SELECT c.customer_id, c.last_name ||', '|| c.first_name as full_name, "
				+ " c.email, a.address, ci.city, a.district "
				+ " FROM customer c"
				+ "	 JOIN address a on a.address_id = c.address_id "
				+ "	JOIN city ci on ci.city_id = a.city_id "
				+ " WHERE customer_id = ?";

		PreparedStatement stmt = conn.prepareStatement(query);
		stmt.setInt(1, id);
		ResultSet rs = stmt.executeQuery();
		rs.next();
		String ret; 
		ret = "- ID: " + rs.getString("customer_id") ;
		ret += "\n- Name: " + rs.getString("full_name");
		ret += "\n- Email: " + rs.getString("email");
		ret += "\n- Address: " + rs.getString("address");
		ret += "\n- City: " + rs.getString("city");
		ret += "\n- District: " + rs.getString("district");
		ret += "\n--------------------------------------------------------------------------------";

		stmt.close();
		conn.close();

		return (ret);
	}

	public static String getCustomerTotal(Integer id) throws SQLException {
		Connection conn;
		PreparedStatement stmt;
		ResultSet rs;
		String result;
		double total;

		conn = DriverManager.getConnection(m_url);
		stmt = conn.prepareStatement(
				"SELECT c.customer_id, c.first_name, c.last_name FROM customer c WHERE c.customer_id = ?");
		stmt.setInt(1, id);
		rs = stmt.executeQuery();
		if (rs.next()) {
			result = "Customer ID  : " + rs.getInt("customer_id");
			result += "\nCustomer Name: " + rs.getString("last_name") + ", " + rs.getString("first_name");
			result += "\n--------------------------------------------------------------------------------------------------------";
		} else {
			return null;
		}

		stmt = conn.prepareStatement("SELECT p.payment_date, p.amount FROM payment p WHERE p.customer_id = ? ORDER BY 1");
		stmt.setInt(1, id);
		rs = stmt.executeQuery();
		total = 0;

		while (rs.next()) {
			result += "\nPayment date: " + rs.getString("payment_date") + ",    Value: " + rs.getString("amount");
			total += rs.getFloat("amount");
		}
		result += "\n--------------------------------------------------------------------------------------------------------";
		result += "\nTotal: " +String.format("%1$,.2f",  total);
		
		stmt.close();
		conn.close();
		return (result);
	}
}

Same instructions to compile:

javac com/percona/blog/pljava/Customers.java 
jar -c -f /app/pg12/lib/demo.jar com/percona/blog/pljava/Customers.class

Then we need to replace the loaded jar file for the newly created and create the function inside Postgres:

demo=# SELECT sqlj.replace_jar( 'file:///app/pg12/lib/demo.jar', 'demo', true );
 replace_jar 
-------------
(1 row)

demo=# CREATE FUNCTION getCustomerTotal( INT ) RETURNS CHAR AS 
    'com.percona.blog.pljava.Customers.getCustomerTotal( java.lang.Integer )'
LANGUAGE java;
CREATE FUNCTION

And the result is:

demo=# SELECT getCustomerInfo(100);
                                 getcustomerinfo                                  
----------------------------------------------------------------------------------
 - ID: 100                                                                       +
 - Name: HAYES, ROBIN                                                            +
 - Email: ROBIN.HAYES@sakilacustomer.org                                         +
 - Address: 1913 Kamakura Place                                                  +
 - City: Jelets                                                                  +
 - District: Lipetsk                                                             +
 --------------------------------------------------------------------------------
(1 row)

We finish this part here and with this last example. At this point, we are able to access objects, loop through a resultset, and return the result back as a single object like a TEXT. I will discuss how to return an array/resultset, how to use PL/Java functions within triggers, and how to use external resources in part two and part three of this article, stay tuned!

[1] https://tada.github.io/pljava/use/policy.html
[2] https://www.postgresql.org/ftp/projects/pgFoundry/dbsamples/pagila/

Apr
06
2021
--

Okta launches a new free developer plan

At its Oktane21 conference, Okta, the popular authentication and identity platform, today announced a new — and free — developer edition that features fewer limitations and support for significantly more monthly active users than its current free plan.

The new ‘Okta Starter Developer Edition,’ as it’s called, allows developers to scale up to 15,000 monthly active users — up from only 1,000 on its existing free plan. In addition, the company is also launching enhanced documentation, a set of sample apps and new SDKs, which now cover languages and frameworks like Go, Java, JavaScript, Python, Vue.js, React Native and Spring Boot.

“Our overall philosophy isn’t, ‘we want to just provide […] a set of authentication and authorization services.’ The way we’re looking at this is, ‘hey, app developer, how do we provide you the foundation you need to get up and running quickly with authorization and authentication as one part of it,’ ” Diya Jolly, Okta’s chief product officer, told me. And she believes that Okta is in a unique position to do so, because it doesn’t only offer tools to manage authorization and access, but also systems for securing microservices and providing applications with access to privileged resources.

Image Credits: Okta

It’s also worth noting that, while the deal hasn’t closed yet, Okta’s intent to acquire Auth0 significantly extends its developer strategy, given Auth0’s developer-first approach.

As for the expanded free account, Jolly noted that the company found that developers wanted to be able to access more of the service’s features during their prototyping phases. That means the new free Developer Edition comes with support for multi-factor authentication, machine-to-machine tokens and B2B integrations, for example, in addition to expanded support for integrations into toolchains. As is so often the case with enterprise tools, the free edition doesn’t come with the usual enterprise support options and has lower rate limits than the paid plans.

Still, and Jolly acknowledged this, a small to medium-sized business may be able to build applications and take them into production based on this new free plan.

“15K [monthly active users] is is a lot, but if you look at our customer base, it’s about the right amount for the smaller business applications, the real SMBs, and that was the goal. In a developer motion, you want people to try out things and then upgrade. I think that’s the key. No developer is going to come and build with you if you don’t have a free offering that they can tinker around and play with.”

Image Credits: Okta

She noted that the company has spent a lot of time thinking about how to support developers through the application development lifecycle overall. That includes better CLI tools for developers who would rather bypass Okta’s web-based console, for example, and additional integrations with tools like Terraform, Kong and Heroku. “Today, [developers] have to stitch together identity and Okta into those experiences — or they use some other identity — we’ve pre-stitched all of this for them,” Jolly said.

The new Okta Starter Developer Edition, as well as the new documentation, sample applications and integrations, are now available at developer.okta.com.

May
18
2020
--

GO1, an enterprise learning platform, picks up $40M from Microsoft, Salesforce and more

With a large proportion of knowledge workers doing now doing their jobs from home, the need for tools to help them feel connected to their profession can be as important as tools to, more practically, keep them connected. Today, a company that helps do precisely that is announcing a growth round of funding after seeing engagement on its platform triple in the last month.

GO1.com, an online learning platform focused specifically on professional training courses (both those to enhance a worker’s skills as well as those needed for company compliance training), is today announcing that it has raised $40 million in funding, a Series C that it plans to use to continue expanding its business. The startup was founded in Brisbane, Australia and now has operations also based out of San Francisco — it was part of a Y Combinator cohort back in 2015 — and more specifically, it wants to continue growth in North America, and to continue expanding its partner network.

GO1 not disclosing its valuation but we are asking. It’s worth pointing out that not only has it seen engagement triple in the last month as companies turn to online learning to keep users connected to their professional lives even as they work among children and house pets, noisy neighbours, dirty laundry, sourdough starters, and the rest (and that’s before you count the harrowing health news we are hit with on a regular basis). But even beyond that, longer term GO1 has shown some strong signs that speak of its traction.

It counts the likes of the University of Oxford, Suzuki, Asahi and Thrifty among its 3,000+ customers, with more than 1.5 million users overall able to access over 170,000 courses and other resources provided by some 100 vetted content partners. Overall usage has grown five-fold over the last 12 months. (GO1 works both with in-house learning management systems or provides its own.)

“GO1’s growth over the last couple of months has been unprecedented and the use of online tools for training is now undergoing a structural shift,” said Andrew Barnes, CEO of GO1, in a statement. “It is gratifying to fill an important void right now as workers embrace online solutions. We are inspired about the future that we are building as we expand our platform with new mediums that reach millions of people every day with the content they need.”

The funding is coming from a very strong list of backers: it’s being co-led by Madrona Venture Group and SEEK — the online recruitment and course directory company that has backed a number of edtech startups, including FutureLearn and Coursera — with participation also from Microsoft’s venture arm M12; new backer Salesforce Ventures, the investing arm of the CRM giant; and another previous backer, Our Innovation Fund.

Microsoft is a strategic backer: GO1 integrated with Teams, so now users can access GO1 content directly via Microsoft’s enterprise-facing video and messaging platform.

“GO1 has been critical for business continuity as organizations navigate the remote realities of COVID-19,” said Nagraj Kashyap, Microsoft Corporate Vice President and Global Head of M12, in a statement. “The GO1 integration with Microsoft Teams offers a seamless learning experience at a time when 75 million people are using the application daily. We’re proud to invest in a solution helping keep employees learning and businesses growing through this time.”

Similarly, Salesforce is also coming in as a strategic, integrating this into its own online personal development products and initiatives.

“We are excited about partnering with GO1 as it looks to scale its online content hub globally. While the majority of corporate learning is done in person today, we believe the new digital imperative will see an acceleration in the shift to online learning tools. We believe GO1 fits well into the Trailhead ecosystem and our vision of creating the life-long learner journey,” said Rob Keith, Head of Australia, Salesforce Ventures, in a statement.

Working remotely has raised a whole new set of challenges for organizations, especially those whose employees typically have never before worked for days, weeks and months outside of the office.

Some of these have been challenges of a more basic IT nature: getting secure access to systems on the right kinds of machines and making sure people can communicate in the ways that they need to to get work done.

But others are more nuanced and long-term but actually just as important, such as making sure people remain in a healthy state of mind about work. Education is one way of getting them on the right track: professional development is not only useful for the person to do her or his job better, but it’s a way to motivate people, to focus their minds, and take a rest from their routines, but in a way that still remains relevant to work.

GO1 is absolutely not the only company pursuing this opportunity. Others include Udemy and Coursera, which have both come to enterprise after initially focusing more on traditional education plays. And LinkedIn Learning (which used to be known as Lynda, before LinkedIn acquired it and shifted the branding) was a trailblazer in this space.

For these, enterprise training sits in a different strategic place to GO1, which started out with compliance training and onboarding of employees before gravitating into a much wider set of topics that range from photography and design, through to Java, accounting, and even yoga and mindfulness training and everything in between.

It’s perhaps the directional approach, alongside its success, that have set GO1 apart from the competition and that has attracted the investment, which seems to have come ahead even of the current boost in usage.

“We met GO1 many months before COVID-19 was on the tip of everyone’s tongue and were impressed then with the growth of the platform and the ability of the team to expand their corporate training offering significantly in North America and Europe,” commented S. Somasegar, managing director, Madrona Venture Group, in a statement. “The global pandemic has only increased the need to both provide training and retraining – and also to do it remotely. GO1 is an important link in the chain of recovery.” As part of the funding Somasegar will join the GO1 board of directors.

Notably, GO1 is currently making all COVID-19 related learning resources available for free “to help teams continue to perform and feel supported during this time of disruption and change,” the company said.

Aug
19
2019
--

Microsoft acquires jClarity, a Java performance tuning tool

Microsoft announced this morning that it was acquiring jClarity, a tool designed to tune the performance of Java applications. It will be doing that on Azure from now on. In addition, the company has been offering a flavor of Java called AdoptOpenJDK, which they bill as a free alternative to Oracle Java. The companies did not discuss the terms of the deal.

As Microsoft pointed out in a blog post announcing the acquisition, they are seeing increasing use of large-scale Java installations on Azure, both internally with platforms like Minecraft and externally with large customers, including Daimler and Adobe.

The company believes that by adding the jClarity team and its toolset, it can help service these Java customers better. “The team, formed by Java champions and data scientists with proven expertise in data driven Java Virtual Machine (JVM) optimizations, will help teams at Microsoft to leverage advancements in the Java platform,” the company wrote in the blog.

Microsoft has actually been part of the AdoptOpenJDK project, along with a Who’s Who of other enterprise companies, including Amazon, IBM, Pivotal, Red Hat and SAP.

Co-founder and CEO Martijn Verburg, writing in a company blog post announcing the deal, unsurprisingly spoke in glowing terms about the company he was about to become a part of. “Microsoft leads the world in backing developers and their communities, and after speaking to their engineering and programme leadership, it was a no brainer to enter formal discussions. With the passion and deep expertise of Microsoft’s people, we’ll be able to support the Java ecosystem better than ever before,” he wrote.

Verburg also took the time to thank the employees, customers and community that have supported the open-source project on top of which his company was built. Verburg’s new title at Microsoft will be Principal Engineering Group Manager (Java) at Microsoft.

It is unclear how the community will react to another flavor of Java being absorbed by another large vendor, or how the other big vendors involved in the project will feel about it, but regardless, jClarity’s flavor of Java and its performance tools are part of Microsoft now.

Note: This article originally stated that all of jClarity’s products are open source. Its performance tools are paid services.

Jul
19
2019
--

Upcoming Webinar 7/23: 10 Common Mistakes Java Developers Make when Writing SQL

Mistakes Java Developers Make when Writing SQL

Please join Percona’s Senior Support Engineer Charly Batista as he presents “10 Common Mistakes (Java) Developers Make when Writing SQL” on Tuesday, July 23rd, 2019 at 8:00 AM EDT (UTC-4).

Register Now

It’s easy for Java developers (and users of other OO languages) to mix object-oriented thinking and imperative thinking. But when it comes to writing SQL the nightmare begins! Firstly, SQL is a declarative language and it has nothing to do with either OO or imperative thinking. It is relatively easy to express a condition in SQL but it is not so easy to express it optimally – and even worse to translate it to the OO paradigm. Secondly, they need to think in terms of set and relational algebra, even if unconsciously!

In this talk, we’ll see the most common mistakes that developers make in OO, especially Java, when writing SQL code, and how we can avoid them.

If you can’t attend, sign up anyways we’ll send you the slides and recording afterward.

Speakers:
Charly Batista

Charly Batista
Senior support engineer

Charly worked as Java Architect for many years and using many different database technologies. He helped to design some of the features of the system used in the Brazilian Postal Service, the largest Java project in Latin America in that time. He also helped to design the database of the Brazilian REDESIM project, the system that is responsible for the municipalities taxation in Brazil. He now lives in China and works as Senior Engineer at Percona.

Jun
05
2019
--

Aion Network introduces first blockchain virtual machine for Java developers

Aion Network, a nonprofit dedicated to creating tools to promote blockchain technologies, announced a new virtual machine today that’s built on top of the popular Java Virtual Machine. Its ultimate goal is increasing the popularity of blockchain with developers.

Aion CEO Matthew Spoke says one of the barriers to more widespread blockchain adoption has been a lack of tooling for developers in a common language like Java. The company believed if they could build a virtual machine specifically for blockchain on top of the Java Virtual Machine (JVM), which has been in use for years, it could help promote more extensive use of blockchain.

Today, it’s announcing the Aion Virtual Machine (AVM), a virtual machine that sits on top of the JVM. AVM makes it possible for developers to use their familiar toolset while building in the blockchain bits like smart contracts in the AVM without having to alter the JVM at all.

“We didn’t want to modify the JVM. We wanted to build some sort of supplementary software layer that can interact with the JVM. Blockchains have a set of unique criteria. They need to be deterministic; the computing needs to happen across the distributed network of nodes; and the JVM was never designed with this in mind,” Spoke explained.

Aion set out to build a virtual machine for blockchain without reinventing the wheel. It recognized that Java remains one of the most popular programming languages around, and it didn’t want to mess with that. In fact, it wanted to take advantage of the popularity by building a kind of blockchain interpreter that would sit on top of the JVM without getting in the way of it.

“Rather than trying to convince people of the merits of a new system, can we just get the system they’re already familiar with on top of the blockchain? So we started engineering towards that solution. And we’ve been working on that since for about a year at this point, leading up to our release this week to prove that we can solve that problem,” Spoke told TechCrunch.

Up to this point, Aion has been focusing on the crypto community, but the company felt to really push the blockchain beyond the realm of the true believers, it needed to come up with a way for developers who weren’t immersed in this to take advantage of it.

“Our big focus now is how do we take this message of building blockchain apps and take it into a more traditional software industry audience. Instead of trying to compete for the attention of crypto developers, we want the blockchain to become almost a micro service layer to what normal software developers are solving on a day-to-day basis,” he said.

The company is hoping that by providing this way to access blockchain services, it can help popularize blockchain concepts with developers who might not otherwise have been familiar with them. It’s but one attempt to bring blockchain to more business-oriented use cases, but the company has given this a lot of thought and believes it will help them evangelize this approach with a wider audience of developers moving forward.

Apr
18
2019
--

CloudBees acquires Electric Cloud to build out its software delivery management platform

CloudBees, the enterprise continuous integration and delivery service (and the biggest contributor to the Jenkins open-source automation server), today announced that it has acquired Electric Cloud, a continuous delivery and automation platform that first launched all the way back in 2002.

The two companies did not disclose the price of the acquisition, but CloudBees has raised a total of $113.2 million while Electric Cloud raised $64.6 million from the likes of Rembrandt Venture Partners, U.S. Venture Partners, RRE Ventures and Next47.

CloudBees plans to integrate Electric Cloud’s application release automation platform into its offerings. Electric Flow’s 110 employees will join CloudBees.

“As of today, we provide customers with best-of-breed CI/CD software from a single vendor, establishing CloudBees as a continuous delivery powerhouse,” said Sacha Labourey, the CEO and co-founder of CloudBees, in today’s announcement. “By combining the strength of CloudBees, Electric Cloud, Jenkins and Jenkins X, CloudBees offers the best CI/CD solution for any application, from classic to Kubernetes, on-premise to cloud, self-managed to self-service.”

Electric Cloud offers its users a number of tools for automating their release pipelines and managing the application life cycle afterward.

“We are looking forward to joining CloudBees and executing on our shared goal of helping customers build software that matters,” said Carmine Napolitano, CEO, Electric Cloud. “The combination of CloudBees’ industry-leading continuous integration and continuous delivery platform, along with Electric Cloud’s industry-leading application release orchestration solution, gives our customers the best foundation for releasing apps at any speed the business demands.”

As CloudBees CPO Christina Noren noted during her keynote at CloudBees’ developer conference today, the company’s customers are getting more sophisticated in their DevOps platforms, but they are starting to run into new problems now that they’ve reached this point.

“What we’re seeing is that these customers have disconnected and fragmented islands of information,” she said. “There’s the view that each development team has […] and there’s not a common language, there’s not a common data model, and there’s not an end-to-end process that unites from left to right, top to bottom.” This kind of integrated system is what CloudBees is building toward (and that competitors like GitLab would argue they already offer). Today’s announcement marks a first step into this direction toward building a full software delivery management platform, though others are likely to follow.

During his company’s developer conference, Labourey also today noted that CloudBees will profit from Electric Cloud’s longstanding expertise in continuous delivery and that the acquisition will turn CloudBees into a “DevOps powerhouse.”

Today’s announcement follows CloudBees’ acquisition of CI/CD tool CodeShip last year. As of now, CodeShip remains a standalone product in the company’s lineup. It’ll be interesting to see how CloudBees will integrate Electric Cloud’s products to build a more integrated system.

Feb
11
2019
--

Google Docs gets an API for task automation

Google today announced the general availability of a new API for Google Docs that will allow developers to automate many of the tasks that users typically do manually in the company’s online office suite. The API has been in developer preview since last April’s Google Cloud Next 2018 and is now available to all developers.

As Google notes, the REST API was designed to help developers build workflow automation services for their users, build content management services and create documents in bulk. Using the API, developers can also set up processes that manipulate documents after the fact to update them, and the API also features the ability to insert, delete, move, merge and format text, insert inline images and work with lists, among other things.

The canonical use case here is invoicing, where you need to regularly create similar documents with ever-changing order numbers and line items based on information from third-party systems (or maybe even just a Google Sheet). Google also notes that the API’s import/export abilities allow you to use Docs for internal content management systems.

Some of the companies that built solutions based on the new API during the preview period include Zapier, Netflix, Mailchimp and Final Draft. Zapier integrated the Docs API into its own workflow automation tool to help its users create offer letters based on a template, for example, while Netflix used it to build an internal tool that helps its engineers gather data and automate its documentation workflow.

 

 

May
09
2018
--

Does the Version Number Matter?

ProxySQL

ProxySQLYes, it does! In this blog post, I am going to share my recent experiences with ProxySQL and how important the database software version number can be.

Migration

I was working on a migration to Percona XtraDB Cluster (PXC) with ProxySQL, fortunately on a staging environment first so we could catch any issues (like this one).

We installed Percona XtraDB Cluster and ProxySQL on the staging environment and repointed the staging application to ProxySQL. At first, everything looked great. We were able to do some application tests and everything looked good. I advised the customer to do more testing to make sure everything works well.

Something is wrong, but what?

A few days later the customer noticed that their application was not working properly.

We started investigating. Everything seemed well-configured, and the only thing we could see in the application log was the following:

2018-04-20 11:28:31,169 [ default-threads - 42] ERROR Error in lifecycle management : org.hibernate.StaleStateException : Batch update returned unexpected row count from update [0]; actual row count: 0; expected: 1 {it.tasgroup.monetica.gt.lifecycle.LifeCycle:line 103} (method: error)
org.hibernate.StaleStateException: Batch update returned unexpected row count from update [0]; actual row count: 0; expected: 1
at org.hibernate.jdbc.Expectations$BasicExpectation.checkBatched(Expectations.java:85)
at org.hibernate.jdbc.Expectations$BasicExpectation.verifyOutcome(Expectations.java:70)

Based on this error I still did not know what is wrong. Were some of the queries failing because of PXC, ProxySQL or some other settings?

We redirected the application to one of the nodes from PXC, and everything worked fine. We tried HAproxy as well, and everything worked again. We knew something was happening around ProxySQL which is causing the problem. But we still could not find the problem. Every query went through ProxySQL without any issue.

Debug log is our savior

The customer finally enabled the application debug logging so we could see which query was failing:

delete from TABLENAME where ID='11' and Timestamp ='2018-04-20 16:15:03';

I was confused at first: this is a kind of simple query, what could be wrong? Let’s investigate it on the cluster. When I tried to select the data on the cluster, it gave me back zero results. That’s OK, maybe the row was already deleted?

For this investigation, the slow query logging was enabled and long_query_time set to 0 to log all the queries. I checked the slow query log looking for queries like this. What I found helped me realize what the problem was:

delete from TABLENAME where ID=10 and Timestamp ='2018-04-20 11:17:22.35';
delete from TABLENAME where ID=24 and Timestamp ='2018-04-20 11:17:31.602';
delete from TABLENAME where ID=43 and Timestamp ='2018-04-20 11:18:13.2';
delete from TABLENAME where ID=22 and Timestamp ='2018-04-20 11:11:02.854';
delete from TABLENAME where ID=11 and Timestamp ='2018-04-20 11:21:57';
delete from TABLENAME where ID=64 and Timestamp ='2018-04-20 11:18:34';
delete from TABLENAME where ID=47 and Timestamp ='2018-04-20 10:38:35';
delete from TABLENAME where ID=23 and Timestamp ='2018-04-20 11:30:03';

I hope you see the difference! The first four lines have fractional seconds! At that time, the application was pointed to the cluster directly. So ProxySQL cut off the fractional seconds? That would be a nasty bug.

I checked the application log again with the debug information, and I could see the application does not even use the fractional seconds in the queries when it points to ProxySQL. This is why the query was failing (does not delete any rows), because in the table all the rows had fractional seconds but the queries were not using them.

So why does the application not use fractional seconds with ProxySQL?

First of all, fractional seconds were introduced in MySQL 5.6.4. The application is a Java-based application with Jboss and Hibernate. I knew ProxySQL reports MySQL 5.5. Maybe the application/connector reads the version number and makes decisions based on that?

It was quite easy to test this theory by just changing the version number in ProxySQL like this:

update global_variables set variable_value="5.7.21" where variable_name='mysql-server_version';
load mysql variables to run;save mysql variables to disk;

The application had to be restarted (probably it was caching the previous settings) but after that everything was working as expected.

But be careful, now it will report 5.7.21 for all the hostgroups. What if you have multiple hostgroups with different MySQL versions? It would be nice if you could define this for every hostgroup.

Conclusion

The solution was very easy, but finding the source of the problem took a long time. If you are planning to use ProxySQL, I would always recommend changing the mysql-server_version to match to the underlying MySQL server version number because who knows which connector or application checks the version and makes a decision based on that.

There is another example here where Marco Tusa had a very similar problem with a Java connector.

The post Does the Version Number Matter? appeared first on Percona Database Performance Blog.

Powered by WordPress | Theme: Aeros 2.0 by TheBuckmaker.com