Archive for the 'database' Category

CF Groovy 1.0RC (With Hibernate!)

I've just released 1.0RC of CF Groovy, including Hibernate support.  You can download it, or view the demo app.  The download includes both the demo and the runtime engine.

The big new feature is Hibernate support, of course.  Here are a couple snippets from the demo app.  First, the entity class:

package com.barneyb

import javax.persistence.*

@Entity
class User extends AbstractEntity {

    String firstName
    String lastName
    String email

    @Column(unique = true)
    String username
}

Then the engine setup (in code here, ColdSpring would be better):

<cfset application.cfhibernate = createObject("component", "HibernatePlugin").init() />
<cfset application.cfhibernate.setEntityPath(
    getDirectoryFromPath(getCurrentTemplatePath()) & "entities"
) />
<cfset application.cfhibernate.setColdFusionDsn("cfgroovydemo") />
<cfset application.cfhibernate.setAnnotatedClasses(listToArray("com.barneyb.User")) />
<cfset application.cfgroovy = createObject("component", "cfgroovy").init() />
<cfset application.cfgroovy.addPlugin(application.cfhibernate) />

Finally, doing some persistence operations:

<cfset request.sess = application.cfhibernate.getSessionForRequest() />
<g:script>
import com.barneyb.*

variables.user = new User([
    firstName: attributes.firstName,
    lastName: attributes.lastName,
    username: attributes.username,
    email: attributes.email
])
request.sess.save(variables.user)
request.sess.flush()
</g:script>
<cfset userList = request.sess.createQuery(
    "from User order by lastName, firstName, username"
).list() />

As you can see, very minimal fuss to do some pretty powerful things.  This only scratches the surface of what Hibernate can do; the full power of the tool is available (custom annotations, the full XML mapping capabilities, caching, etc.).

Another significant change is that all non-inline Groovy is precompiled into Java classes and loaded dynamically.  This gives you compiler checks on your Groovy code and engine startup, more helpful message for code errors, and some performance improvements.

Finally, as illustrated above, the <g:setPath> tag has been superceded by an explicitly managed CF Groovy runtime.  This gives you complete control over the lifecycle of the engine, which lets you target performance in production and development speed in development.  You can still use <g:setPath> instead of managing the runtime yourself, but that should be considered a deprecated practice.

CF Groovy Takes a Nap

After close to two weeks of struggling, I finally managed to deploy pure source to a CFML runtime (Railo, in this case), and get Groovy entities in and out of the database with Hibernate.  No compliation, no IDE, no development-mode server, just my Groovy source along with a hacked up CF Groovy.  This is very exciting for me, because while Groovy is cool and all, CF's dynamic nature has become a must have.

I don't have source to download yet, just a horribly hacky proof of concept, but here's what the user of the "framework" does.  First, create your entity:

package com.barneyb
import javax.persistence.*

@Entity
class Person {

  @Id
  @GeneratedValue
  Long id
  Long version
  String name
  Date dob

  def getAgeInSeconds() {
    (new Date().getTime() - dob.getTime()) / 1000
  }

  def getAgeInDays() {
    ageInSeconds / 86400
  }

  def getAgeInYears() {
    ageInDays / 365.249
  }

  String toString() {
    "$name is $ageInYears years ($ageInDays days) old"
  }

}

That's the same class as my initial CF Groovy demo used, except with the of JPA annotations added.  Because I'm binding to Hibernate, you can use any Hibernate-supported annotations, but I opted to stick with vanilla JPA.  Now we need our hibernate.cfg.xml, which is totally stock:

<?xml version='1.0' encoding='utf-8'?>
<!DOCTYPE hibernate-configuration PUBLIC
  "-//Hibernate/Hibernate Configuration DTD//EN"
  "http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">
<hibernate-configuration>

  <session-factory>

    <property name="current_session_context_class">thread</property>
    <property name="cache.provider_class">org.hibernate.cache.NoCacheProvider</property>
    <property name="show_sql">true</property>
    <property name="hibernate.dialect">org.hibernate.dialect.MySQLDialect</property>
    <property name="hibernate.connection.driver_class">com.mysql.jdbc.Driver</property>
    <property name="hibernate.connection.url">jdbc:mysql://localhost:3306/dbname</property>
    <property name="hibernate.connection.username">username</property>
    <property name="hibernate.connection.password">password</property>
    <property name="hibernate.hbm2ddl.auto">update</property>

    <mapping class="com.barneyb.Person" />

  </session-factory>

</hibernate-configuration>

You could also map your classes manually with XML, if you wanted, but for the sake of simplicitly, I've done it all with annotations.  You'll also notice the database connection information embedded directly in the file. This is far from ideal.  I'm hoping to extract it from a CFML DSN, but that might only be possible on Adobe ColdFusion (via the Admin API).  The last thing is the test script:

<g:script>
  import java.text.SimpleDateFormat

  import org.hibernate.*
  import org.hibernate.cfg.*

  import com.barneyb.Person

  sdf = new java.text.SimpleDateFormat("yyy/MM/dd")
  sdf.lenient = true

  def sf = new AnnotationConfiguration()
      .configure().buildSessionFactory()
  def sess = sf.openSession()
  def tx  = sess.beginTransaction()
  sess.createQuery("delete Person").executeUpdate()
  sess.save(new Person(
    name: attributes.name ?: "Barney",
    dob: sdf.parse(attributes.dob ?: "1980/06/10")
  ))
  tx.commit()
  sess.close()
  sf.close()
</g:script>

No need to compile anything, no need to drop JARs all over your server, nothing.  Write, refresh, persist.  That includes adding new entities, new properties to existing entities, etc.

It's not ready for public consumption at the moment, but that's my top "free time" priority.  For the incredibly impatient, there's a branch in my SVN repository with the current source.  Don't expect anything pretty.  ;)

Just Say No to Materialized MySQL Views

I'm a big fan of MySQL, but I ran into an interesting performance issue this weekend regarding views. MySQL added view support in 5.0, and I've used it to great effect, but it's not all roses.

Within MySQL, there are several ways to categorize views, but the one I care about is materialized vs. rewritten/merged. Materialized views are views that, as you might guess, are materialized when they're needed. In simpler terms, the view definition is used to create a temporary table and then that temporary table is used for the actual query. Rewritten or merged views are combined with the actual query by the SQL engine and executed as a single query.

The performance problem I ran across was with the former view type. It seems that the internals of MySQL manage materializing in a very inefficient way. I can't provide any sort of hard data to support that, but what I experienced was definitely points to the conclusion.

I had a view that had some fairly intensive stuff in it (both structurally and computationally). I knew it was materialized and was fine with a little bit of a slowdown, but I was seeing times in the 15 second range for certain queries that used it on my personal server (which, admittedly, is a only 3GHz Celeron). Manually materializing the view (i.e. running the view statement to create my own table) and then running the queries in question took a few hundred milliseconds tops, counting both the generation of the temp table and the actual query time (with most of it being the former).

The only explanation I can come up with was that the materializing of the view was happening repeatedly. Very repeatedly. Recasting the "view" as a normal table that I repopulate as needed has eliminated all the performance issues, and the queries are now typically running in tens of milliseconds.

I should mention that I've never had any performance issues with rewritten/merged views. MySQL seems to do a fantastic job efficiency-wise with them, and I continue to use them.

Interestingly, I had a very similar problem with Oracle a few months ago. The main difference was that I wasn't using views, but rather subqueries in the FROM clause (i.e., inline views). In that app, creating separate views was both impossible (we didn't own the database), and unnecessary (only one query used the view), and we were able to get around it with "factored subqueries" (the WITH clause).