Grails Datasource, legacy database, and you

Any one out there trying to work with Groovy, Grails, and legacy databases?

Well, I am and I thought I'd share with you my final dataSource configuration. I'm using a legacy database that drove an Apache + mod_perl web site that had a MySQL 3.x database. The Groovy/Grails project I'm working on will interact with that data (right now as a read-only service) and here's the final dataSource definition I ended up using for our first beta.

import org.codehaus.groovy.grails.orm.hibernate.cfg.GrailsAnnotationConfiguration
/**
 * This project is driven off of Legacy data.
 * Use update to prevent wiping old tables...
 * much of the data is accessed using JPA classes generated by
 * the Hibernate3 reverse engineer tool.
 ********************************************************************/
 dataSource {
  configClass = GrailsAnnotationConfiguration.class
  dbCreate = "update"
  dialect= org.hibernate.dialect.MySQLMyISAMDialect // or perhaps MySQL5Dialect
  pooled = false
 }
 environments {
  development {
   dataSource {
    driverClassName = "com.mysql.jdbc.Driver"
    url = "jdbc:mysql://localhost:3306/legacy?zeroDateTimeBehavior=convertToNull"
    username = "legacy"
    password = "legacy"
   }
  }
  test {
   dataSource {
    driverClassName = "com.mysql.jdbc.Driver"
    url = "jdbc:mysql://localhost:3306/legacy?zeroDateTimeBehavior=convertToNull"
    username = "legacy"
    password = "legacy"
   }
  }
  production {
   dataSource {
    jndiName = "java:/LegacyDS"
   }
  }
}

The LAMP stack was upgraded to use MySQL 5 recently so we can set up our new projects using the setting dialect= org.hibernate.dialect.MySQLInnoDBDialect which will give us the ability to use actual foreign key relationships. That's a big deal if you are a LAMP developer. I suppose that in short order I will be a Grails, Linux, Apache, MySQL developer... a GLAM developer... ah... no... lets not call it that.

BTW: I deploy to a JBoss Application server in production which manages the DataSources in a legacy-ds.xml file under /usr/local/jboss/server/default/deploy that's why my production dataSource uses the jndi lookup.

Alpha Geeks: say w00t!

2 Types of programmers

... what many would call "alpha" programmers — the leaders, trailblazers, trendsetters, the kind of folks that places like Google and Fog Creek software are obsessed with hiring. These folks were the first ones to install Linux at home in the 90's; the people who write lisp compilers and learn Haskell on weekends "just for fun"; they actively participate in open source projects; they're always aware of the latest, coolest new trends in programming and tools.

Yet, we work for and produce software for use by casual programmers and non-programmers. And as Sussman's post points out we had best not forget it. That means you. If you are reading my blog chances are an alpha geek (or just horribly lost).

What's the point of all the pretty shiny objects if we can't get people to use the pretty shiny objects?

Interoperability is the key to success

I just read the We have lost control post by Reg Braithwaite that's been making the rounds of the blog-o-sphere. Basically, modern IT departments compete directly with the internet. We have to. It's the 21st century. That's because...

Our users are being exposed to applications we don’t control. And it messes things up.

It means that our end users have used the internet and if our programs don't measure up they know it. You can't hide it! They know! Braithwaite tells us this lovely story about working on a CRM for his mum:

For example, my Mother uses Skype to talk to her friends. She thinks it’s normal to see all of your voice mail messages in a list on the screen. If I tried to give her a CRM application for managing contacts, the very first question she would ask would be, “Why can’t I listen to all of the voice mails from that contact in the application?”

Do you think she would have patience for my explanation that the company’s phone systems are complex and proprietary and that we can’t install Asterix just for her? She would grab me by the ear and drag me to my desk to get cracking on it!

In other words if you ship a CRM today that can't interoperate with a phone system... or your phone system can't interoperate with a CRM... your in house CRM will not measure up to the internet based competition... Skype.

And is the user wrong to expect a Skype-like experience? Should the IT department tell them to go suck an egg? I don't think that will fly.

Long story short modern IT departments will have to provide in-house mash-ups of services to be able to provide an end user experience that can compete with the wild and wooly internet.

BTW: I now believe that the Grails Searchable Plugin will change my life. Even though I haven't used it yet. Because I can see how full text search wins and how that plugin can single handedly enable search for all my projects. Yes... the computing world is about to change... again. Hold on for the barrel roll.

helloLDAP.groovy

Here's a short script that I wrote just to test working with an ActiveDirectory server. It's a short hack of a script that you can use to just test settings and the like. Thanks to this script I know I've found the right settings to work with our Microsoft ActiveDirectory server through Java. I could even use this groovy code to set up a Spring Bean service.

#!/usr/bin/env groovy

import java.util.Hashtable
import javax.naming.*
import javax.naming.event.*
import javax.naming.directory.*

def ou = " System and Service Accounts"

// here, we're just reading in some parameters...
def stdin = new BufferedReader(
        new InputStreamReader(System.in)
    )
print "username: " // your username to access LDAP... not a real user
String username = stdin.readLine()
print "password: " // the password for that account...
String passwd = stdin.readLine()

// next we set up an environment for LDAP
Hashtable env = new Hashtable()
env.put(
    Context.INITIAL_CONTEXT_FACTORY,
    "com.sun.jndi.ldap.LdapCtxFactory"
    )
env.put(
    Context.SECURITY_AUTHENTICATION, 
    "EXTERNAL"
    )
/* // if only I had SSL working... *sigh* oh well...
env.put(
    Context.SECURITY_PROTOCOL,
    "ssl"
    ) //*/

// You'll want to put your server's name or IP here:
env.put(
    Context.PROVIDER_URL, 
    "ldap://activedirectoryhost.mycompany.com:636/"
    )

// Here, you'll want to set up your company's parameters for OU and DC...
env.put(
    Context.SECURITY_PRINCIPAL,
    "CN=" + username + ",OU=" + ou + ",DC=mycompany,DC=com"
    ) // username
env.put(
    Context.SECURITY_CREDENTIALS,
    passwd
    ) // and password

// Now we'll try and connect...
try {
    def ctx = new InitialDirContext(env)
    def attr = ctx.getAttributes("")
    def srchInfo = new SearchControls()
    // you'll want to use your own search base
    def searchBase= "OU=company,DC=mycompany,DC=com" 
    // this is the magic search string sauce:
    def searchFilter = "(&(objectClass=person))"
    // we strong type the next var because it is 
    // used in a Java API that needs String[]
    String[] objAttribs=[
        "givenName",
        "cn",
        "sn",
        "mail",
        "mailNickname",
        "userPrincipalName",
        "displayName",
        "manager",
        "department",
        "memberOf"
    ]
    srchInfo.setSearchScope(SearchControls.SUBTREE_SCOPE)
    srchInfo.setReturningAttributes(objAttribs)
    
    // Now we get the results and loop through them...
    NamingEnumeration dirObjects = ctx.search(searchBase,searchFilter,srchInfo)
    def nodirObjects = 0
    while (dirObjects != null && dirObjects.hasMoreElements()) {
        def dirObject = dirObjects.next()
        println("'" + dirObject.getName() + "':")
        def attrs = dirObject.getAttributes()
        for(name in objAttribs) {
            println "\t * " + attrs.get(name)
        }
        nodirObjects++ 
    }
    ctx.close()
    println("Number of entries identified: " + nodirObjects)
} catch (Exception e) {
    println "Exception: " + e
}

Grails Searchable Plugin... wow.

Words fail me Searchable plugin for Grails... wow. I'm speechless... I'm... wow. I just don't know what to say... wow.

Is anybody using this yet? I'm going to have to plug this into my very next project... wow.

Magical MySQL URL parameters Save my Groovy Code

I've been working with legacy databases in MySQL and basically forbidden from using hibernate by a bug I had with all zero dates of the format '0000-00-00' which is perfectly legal in MySQL but completely disallowed in JDBC.
Stack trace for this problem contains:

SQL Exception: Value '0000-00-00' can not be represented as java.sql.Date 

The answer? Apparently there is a hidden setting to use...

dataSource {
  driverClassName = "com.mysql.jdbc.Driver"
  dialect= org.hibernate.dialect.MySQLMyISAMDialect
  url = "jdbc:mysql://localhost:3306/legacy?zeroDateTimeBehavior=convertToNull"
  username = "legacyUser"
  password = "legacyPassword"
 }

That little flag on the URL is absolutely magical and now I can map my Grails objects onto a legacy database. Isn't life grand?

Groovy Class tricks

jwagon sent me these mind bending groovy class manipulations in a chat today:

#!/usr/bin/env groovy

class AA { 
        def msg
        AA(something){ msg = something } 
    }

def c = { a, b -> 
 def  x =a.newInstance(b)
 println x.msg
}

c(AA, "foo")

Or how about....

#!/usr/bin/env groovy
def m = java.lang.String
def n =  m.newInstance("foo")
println n