CODEWITHSUNDEEP
grailshqlgrails-ormlegacy-database
oligofren
oligofren

Reputation: 22993

Translating data mining sql-queries into Grails equivalents?

I would like to build a web interface to present logdata. This should be a nice opportunity to try to build a little web app using Grails, which I have been wanting to try out for quite a while, but I am having some trouble understanding how I can "translate" the manual sql queries of today into something usable for Grails.

I have not found too much info on retrofitting existing data tables onto domain classes in the book Grails In Action. So I am bringing it here for some clarification :)

This is basically the schema for the log table I am logging to today:

LOG(id,method,timestamp,millis_used,username,hostname,...etc)

I can see myself doing something like creating domain classes User and Host with mappings like hasMany = { logs: Log } and a Log class with belongsTo = { user: User }, but not how I can use this in a effective way to query my data, especially if dealing with hundred of thousands of log rows. I am usually doing queries on the data like "find the average time used for method='fooBar' and user='john_doe' the last 30 days" or "count the number of rows where method='fooBaz' and host='localhost' from May to December".

How would you go about to retrieve info like this? Would you just forget about mapping the log entries and just use some kind of direct SQL (HQL?) queries on the tables or can this (unknown to me) GORM beast be used for stuff like this?

Upvotes: 2

Views: 518

Answers (2)

Derek Slife
Derek Slife

Reputation: 22496

First, I agree with Ted's response. Check out Burt's presentation before you setup your GORM domains.

Second, I recommend you take a look at Criterias. The grails DSL for the Hibernate Criteria functionality makes for a very clean and maintainable codebase. Here are some examples:

Example Criteria:

def avg = Log.createCriteria().get {
    projections {
        avg 'duration'
    }
    user {
        eq 'userName', 'user1'
    }
}
println("Average = ${avg}")

Example Domain Objects:

class User {

    String userName
    String firstName
    String lastName

    static constraints = {
        userName nullable:false, maxSize:32
        firstName nullable:false, maxSize:50
        lastName nullable:false, maxSize:50
    }

}

class Host {

    String hostname

    static mapping = {
        version false
    }

    static constraints = {
        hostname nullable:false, maxSize:64
    }
}

class Log {

    Host host
    User user
    String method
    String logMessage
    Date dateCreated
    long duration

    static mapping = {
        autoTimestamp true  //Note: this will automatically update dateCreated and lastUpdate
        version false
    }

    static constraints = {
        host nullable:false
        user nullable:false
        method nullable:false, maxSize:50
        logMessage nullable:false, maxSize:255
        duration nullable:false
    }
}

Upvotes: 4

Ted Naleid
Ted Naleid

Reputation: 26811

With as many records as you're likely to have for logging per user or per host, I wouldn't use a hasMany relationship. Logging tends to be write heavy and there are costs to maintaining the set/list of logs per member that wouldn't be worth it.

HQL is probably your best bet. You can craft it to look a lot like native SQL.

Burt Beckwith has a great presentation that talks about some of the performance in grails with GORM that'd be worth your time to watch: http://www.infoq.com/presentations/GORM-Performance

Upvotes: 2

Related Questions