Ivy User Details Released

So over the Christmas period we had some time to catch up on the latest features of the Sparkl plugin from webdetails. One particular feature that interested us was the new persistent database connectivity. If you develop Sparkl plugins, there are times where you might want to save data into a REAL database. In the past we had two options for saving data. We could either save data into text files like csv or json on the file system or we could create our own database using something like MySQL and save to that. Both of these options have drawbacks. If we are writing to text files then we cannot have multiple users accessing the same text files at the same time. Also text files are simple and there are no cool SQL features you might get from a RDBMS. The other option is to install our own RDBMs and use that. The problem here is that you are having to install your own database server and set it up. This means that when you hand out your plugin to other users they would also have to install a RDBMS.

So how does Sparkl overcome these issues? Well, Sparkl now has the ability to connect to the native Pentaho Hypersonic (H2) database! We get all the advantages of a real RDBMS as well as not having to install our own server.

What does all this have to do with the new Ivy User Details plugin?

Well, we wanted to do two things with this plugin. We wanted to create a plugin that allowed Pentaho users logged into the BA Server to store more details about themselves. The user can save their first name, last name, gender, telephone numbers, email and so on. Once these details have been saved they can be used in other Pentaho solutions. For example, you could add a custom welcome string to your existing CDE dashboards. Something like “Welcome to the Dashboard Harris Ward”

ivyud

The other thing this plugin serves to do is give Sparkl plugin developers a sample that will let them understand how to get started with persistent Sparkl plugins.

So how does it work?

There are a couple of moving parts to this application. We have a Connection string to the Hypersonic database and we have a SQL create statement in a ddl file. Lets take a look at the Hypersonic connection first.

jdbc:h2:${cpk.plugin.dir}/resources/database/ivyud;DB_CLOSE_DELAY=-1;AUTO_SERVER=TRUE;INIT=RUNSCRIPT FROM ‘${cpk.plugin.dir}/resources/ddl/ivyud.ddl

You can see that we are using some Sparkl variables in this connection string.

The first chunk of text in bold is the location of where our database will be saved. You can see in this example we are using the cpk.plugin.dir variable. This means that in the IvyUD our database will be stored in pentaho-solutions/system/IvyUD/resources/database folder with the name ivyud. The second chunk of text in bold is the create statement to execute when the connection is made. You can see that this ddl file is saved in the pentaho-solutions/system/IvyUD/resources/ddl directory. This ddl file contains the CREATE IF NOT EXISTS sql that is needed to execute when the connection is opened.

You can see the IvyUD DDL below.


CREATE TABLE IF NOT EXISTS USERS
(
username VARCHAR(128),
title VARCHAR(128),
firstname VARCHAR(128),
lastname VARCHAR(128),
dob VARCHAR(256),
gender VARCHAR(256),
phone VARCHAR(256),
mobile VARCHAR(256),
email VARCHAR(256)
)

Simple right? Thats all there is to it. Make the connection to Hypersonic and execute your own DDL SQL and start saving your plugin data to the database. In the case of the IvyUD we are saving the extra user details to a table that in linked to the regular Pentaho user via their username. Open it up in Sparkl and Pentaho Data integration to take a look.

Until next time!

About the author

Harris Ward is the Managing Director of Ivy Information Systems. He has been working in Open Source Business Intelligence for a number of clients developing solutions and providing training spanning 10 years

Leave a Reply