Java and MySQL

Each time I set up a new machine for development, I keep forgetting that JDBC doesn’t come bundled with the drivers for MySQL.

Download available from http://dev.mysql.com/downloads/connector/j/, however you need an oracle username and password, thanks Oracle, yet another one lying around. Not a login I use a lot, it offers an option for a reminder of your username but mine turned out to be my email address, all captured in Evernote for the future.

Reset the password and logged in to find a form that needed filling before it would let me download! http://dev.mysql.com/downloads/mirror.php?id=412178 may help in the future, we’ll see.

Put the downloaded jar file in jre/lib/ext, on a Mac that’s in /System/Library/Java/JavaVirtualMachines/1.6.0.jdk/Contents/Home/lib/ext

 

 

 

Advertisements

It just works

Wavemaker rocks. Not sure there’s any easier way to say it. Thought I’d throw a new challenge at it, and like all the previous posts, the thing just does what it says on the tin.

I have a VPS out there running a linux variant, on which I run some analysis code. That code write updates and results into a local MySQL database. That database is permissioned for localhost access only.

Fed up with various locations not letting me ssh into the box to check the content of the database, and figuring port 80 is available just about anywhere, the challenge was to describe the schema to Wavemaker and have it deploy to my linux box.

OK so it doesn’t deploy straight to the box, it deploys to tomcat, so a quick apt-get install tomcat6, followed by some tomcat user creation we have a target deployment location.

Guess what. It just works, pointed the application at the port the tomcat server was listening on, tweaked the database connection string to include the database name and 5 minutes later, it’s all working.

Only challenge remaining is the out-of-the-box widgets have a limit on the number of rows they show, need to explore a better way to scroll through the tables. One for the future.

What, where, when, why?

The one regular reader of this blog (yes, that’s you) will remember vividly that we’re building a data driven application which will make it easy to schedule expertise against incoming requests.

To do this, at some point we’re going to need to ask the database who is available between certain times. i.e. who has not already been booked on a gig between certain times. With the current data model that means querying the list of individuals to see if they have the right level of skills to match the skills requested and then check to see if any booking row for that individual starts or ends between the target times, and if it does, then that individual is not a hit. It took me a while to write that in English so currently I have no clue how to code it in SQL to return a list of people! I’m sure it’s possible, but it sounds complex (it appears to traverse every table and relationship in the model) and we should only do complexity when necessary, right? Figuring that it is possible is one thing, getting someone to understand it, support it and maintain afterwards is painful.

Options

So what’s the first thing we do? Trawl the net looking for pointers, this is a problem that has been solved before, every email calendaring software deals with this, and I have no desire to reinvent wheels, just make this app a useful piece of Wavemaker. RFC 5545 Internet Calendaring looked like a good place to start but it defines how to communicate free/busy info, not a good way for arranging data in a model such that free and busy can easily be derived or stored.

Reading through the search results, there’s nothing really popping up which says “here’s a way to do this”. What I can see is that free/busy info in many cases is generated and is treated as a cache – meaning it can be recreated. This triggers some thinking about the model. If we track bookings, i.e. a period when a person is busy, can we assume that any non-booked time is free? We could do, and say to folk create a booking for things like time off on holiday, weekends etc, or, as I was thinking, we ask people to create free time records for the coming month or two, so they effectively publish their availability for gigs. One working principal I had in my head from earlier debate was that everything an individual does is a “work package” and therefore there should be a booking for it.

This would mean for internal projects, the nominated PM would create a request for a piece of work the same way as any outside requestor – this consistency appeals to me. Which means, free time is therefore defined as any time for which there isn’t a booking. Tackling that query complexity is starting to sound like the right thing to do!

Another thought which supports solving the difficult query needs is that what happens when a booking is cancelled, and that person then becomes free again at that time? In the case where we had free time records we’d need to figure out how to coalesce adjacent free time records into one record. Let’s avoid any thoughts about batch housekeeping sorting these things out, let’s avoid the problem if we can design it out.

The Query

We’ve eliminated redesigning the data model so let’s go figure out how to solve that query. The question we need to solve is who (person.id) has skill x at level y (assume we have the id for the skill, so personskill.skill_id and personskill.level >;= y, and has no booking record starting or finishing between request.startdatetime and request.enddatetime.

Let’s just say, with all the joins between tables needed to access the data to answer that question that a fair amount of time has been spent reading stuff and trying to code it up. Here’s what I came up with:

select
    person.id as PersonID, booking.id as BookingID
from
    person
        inner join
    personskill ON person.id = personskill.person_id
        left join
    booking ON person.id = booking.person_id
        left join
    request ON booking.request_id = request.id
where
    skills_id = 1
and
    level >;= 1
and
    (
       booking.person_id is null
    or
       not (request.startdate <;= '2012-5-23 16:00' and request.enddate >;= '2012-5-23 14:00')
    )
;

So once we have the MySQL (i.e. almost normal SQL) required to support answering the question we then have to convert this to HQL as that’s all Wavemaker understands. Why didn’t I try to develop the query in HQL in the first place? The Wavemaker IDE isn’t as functional as a dedicated tool like MySQL Workbench when it comes to iterative development – for this very reason Wavemaker best practise is to use database specific tools to develop the schema then import in to the project.

The Application Interface

Before we convert the query, let’s examine how it’s going to be used and where those parameters it needs come from. We’re dedicating a page within the application which presents a list of Requests, and once one is selected, a button labelled “suggest candidates” is enabled. The user presses this button and a table with the list of suitable candidates is presented. From there the user can select one user, which enables a button labelled “book person”, that creates a Booking record for that Person against the Request.

The page is accessed from the Request menu, from a menu item labelled Book Person, i.e. the flow is Request ->; Book Person.

Futures

The thinking races ahead while researching this, we wouldn’t really want to try and replicate peoples calendars, but wouldn’t it be good to be able to send out a calendar invite email for a booking so people can see it in their personal calendars too? Would save whoever is administering the system some time too, as without this feature they would have to manually send the calendar reservation. So we need an email capability within the app, and to know how to structure an invite email. More fun lies ahead!

Creating the database

Harking back to the initial getting started post, the Turnkey MySQL server appliance from the VMware Solutions Exchange was downloaded. The starting point for the database schema being used was created with MySQL Workbench, which is also available as a free download. That “free” thing is going well so far!

This post is about 2 things: getting our MySQL Appliance to use the same IP address every time VMware Fusion starts it and then secondly using MySQL Workbench to create our database.

Turnkey MySQL Appliance IP Address

The Turnkey MySQL Appliance installed fine and the setup was easy, but it was just set up with a dynamic IP address allocated by VMware Fusion on the Mac. It’s going to be a little painful on the development setup to be switching the database targets IP address every time it starts up so a static address is needed.

As can be seen in the screenshot the starting point is a suspended Turnkey MySQL Appliance, configured to grab an IP address from one of Fusions subnets. Which one though? 172.16.253.0 it seems, according to the running VM. So how do we set a static IP on that subnet? Can we allocate a range for static allocation?

The shipped help files with Fusion mention it’s inbuilt DHCP server only once, when describing what the NAT option is on a network connection for a VM. Even googling for some help didn’t immediately reveal how to control the range of addresses that can be used for static allocation.

Having a poke around the installed files didn’t help either, the vmnet-dhcpd binary was there. However a quick “ps –ef | grep dhcp” revealed that Fusion runs an instance of the binary for each subnet it’s running, and has it’s config file as a runtime argument.

On my system, Fusion is running 2 subnets, vmnet1 and vmnet8. The Turnkey MySQL appliance is using the 172.16.253/24 subnet. This is the vmnet8 subnet and the config file is /Library/Preferences/VMware Fusion/vmnet8/dhcpd.conf. The contents of that file reveals, that from .128 through .254 is the range the dhcpd binary uses for dynamic addresses, and .1 and .2 addresses are already used.

Using the Turnkey MySQL Appliance vm console, it’s pretty easy and intuitive to switch to static addressing – I chose .120 on my system.

Uploading the database schema

Now that we have a MySQL server that will have the same address every time we use it, it’s time to load up the defined schema.

Using MySQL Workbench, it’s a simple matter of configuring the connection parameters to out Turnkey MySQL Appliance. On the Workbench application menu, selecting Database -> Manage Connections reveals the dialog to plug in our IP address and credentials.

After this it’s once again surprisingly simple to select Database -> Forward Engineer … to upload our schema and create the database we need for our app. Sorry, but I don’t want to hear a passionate justification as to why it’s called “forward engineering” a database, it’s very definitely applying and uploading the schema to the database.

First Steps

So that’s it. Over 25 years into a career in technology and I’ve started my first tech blog. Wish me luck, be gentle with me but do let me know where I stray over the borders of accepted blog etiquette so this can end up a useful resource.

The about page explains the background to this project, so here go – first steps. There’s been many advances in app development and hosting technologies I looked at when they were first emerging. SaaS is the future, imho, so for me it’s time to get back up to speed on the business end of IT, i.e. automating and assisting business logic through an application. Infrastructure’s been fun but my attention keeps getting drawn back to my technology roots.

Wavemaker

On first glance through the marketing blurb, Wavemaker ticks all the right boxes, point and click application creation, with the ability to drop down to code when you push the edges of it’s capability. Going here first, the idea of clip together applications, with comprehensive IDE, and automated deployment options is appealing. Can it deliver usable applications, rapidly? I’m hoping the slowest part is me getting the grey matter tuned in to the approach.

First the environment, running on a Macbook Pro, downloaded Wavemaker 6.4.5GA and a MySQL appliance to run under Vmware Fusion, figuring best to start out with an external database just in case it’s possible to write wm apps that won’t port between databases easily – nothing in the blurb to suggest that’s the case, so call me cautious.

Registered on the vmware solutions exchange site https://solutionexchange.vmware.com/store and found the Turnkey MySQL Appliance, running MySQL 5.1. There’s another from Jumpbox there as well but the only thing it claimed to have free is a trial, implying cost, although I gave up after 30 seconds of trying to figure out if there actually is a cost for it.

Turnkey installed OK, looks like there’s paying options for backup and DNS integration if needed. Ignoring for now as this is dev/playpen time.

Wavemaker was a regular Mac OS X dmg file, and the usual drag and drop to a link to the Applications folder. It downloaded some open source dependencies, fairly rapidly on first run. Time to run through the tutorials.

CloudFoundry

So just to see if I can really complicate things, let’s throw a little CloundFoundry into the mix. Erm, no problem it seems – created an account, had to wait for an email approving it. Followed the links in it, watched the getting started video, changed my password. Downloaded the micro cloud foundry, set up my domain there, whiteware.cloudfoundry.me, which I think at this point gives a deployment target for the app – let’s see if that assumption works as we go through this learning experience.