14 Replies Latest reply on Nov 20, 2007 5:51 AM by camunda

    [sim] problems with hibernate queries and dates

    camunda

      Hi.

      I stuck in two problems with hibernate queries today, I hope somebody has an answer to them!

      I want to get the average (and standard derivation) of time between two process start events. For this I want to query the ProcessInstanceCreateLog.

      First problem: I need to calculate the time in between. The "clean" way would be like this I think:

       select
       (select min(pl2.date) - pl.date
       from org.jbpm.graph.log.ProcessInstanceCreateLog pl2
       where pl2.date > pl.date)
       from org.jbpm.graph.log.ProcessInstanceCreateLog pl
       where pl.token.processInstance.processDefinition = :processDefinition
       and exists (select pl2.date
       from org.jbpm.graph.log.ProcessInstanceCreateLog pl2
       where pl2.date > pl.date)
      


      Big problem here: How to make the date difference calculation db independant?

      1.) The way with "-" seems not to work correct with mySQL in all cases. I got some wrong figures:
       2007-11-13 17:21:00.0
      - 2007-11-13 17:20:59.0
      = 41
      


      2.) TIMESTAMPDIFFERENCE(SECOND, time1, time2) results in a hibernate exception (even if it should be supported):
      Exception in thread "main" java.lang.NullPointerException
       at org.hibernate.dialect.Dialect$2.getReturnType(Dialect.java:85)
       at org.hibernate.hql.ast.util.SessionFactoryHelper.findFunctionReturnType(SessionFactoryHelper.java:382)
       at org.hibernate.hql.ast.tree.AggregateNode.getDataType(AggregateNode.java:21)
       at org.hibernate.hql.ast.tree.SelectClause.initializeExplicitSelectClause(SelectClause.java:143)
       at org.hibernate.hql.ast.HqlSqlWalker.useSelectClause(HqlSqlWalker.java:705)
       at org.hibernate.hql.ast.HqlSqlWalker.processQuery(HqlSqlWalker.java:529)
       at org.hibernate.hql.antlr.HqlSqlBaseWalker.query(HqlSqlBaseWalker.java:645)
       at org.hibernate.hql.antlr.HqlSqlBaseWalker.selectStatement(HqlSqlBaseWalker.java:281)
       at org.hibernate.hql.antlr.HqlSqlBaseWalker.statement(HqlSqlBaseWalker.java:229)
       at org.hibernate.hql.ast.QueryTranslatorImpl.analyze(QueryTranslatorImpl.java:228)
       at org.hibernate.hql.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:160)
       at org.hibernate.hql.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:111)
      


      3.) TIMEDIFF(time1, time2) results in a SQL exception when using MySQL:
      java.sql.SQLException: Value '00:00:00 can not be represented as java.sql.Time


      So how to do that in a database independant way?

      The second problem is, that the figures I need are not calculated from the database. This can not be nested in the above statement (or I haven't figured it out how). So I thought about the following workaround, should work in real life, because process instance id's are ordered in order of creation date:

       select
       count(pl),
       avg(pl2.date - pl.date),
       min(pl2.date - pl.date),
       max(pl2.date - pl.date),
       stddev(pl2.date - pl.date)
       from org.jbpm.graph.log.ProcessInstanceCreateLog pl,
       org.jbpm.graph.log.ProcessInstanceCreateLog pl2
       where pl.token.processInstance.processDefinition = :processDefinition
       and pl2.token.processInstance.id = pl.token.processInstance.id + 1
       group by (pl.token.processInstance.processDefinition)
      


      Does anybody sees upcoming problems with that query?

      The last version is what I use for the moment, but it is still not really correct.

      Any ideas to the date stuff?

      Or do I have to go back to native queries? This would be a pain in the ass, because I have to provide SQL's for a bunch of databases... Hibernate seems to be a bit week at the point of date calculation... By the way, I found a Hibernate JIRA issue for it, but it seems not to be assigned :-(
      http://opensource.atlassian.com/projects/hibernate/browse/HHH-2434


        • 1. Re: [sim] problems with hibernate queries and dates

          This is a pure hibernate issue, please use the hibernate forums

          • 2. Re: [sim] problems with hibernate queries and dates
            camunda

            2 things:

            1.) The question "if somebody sees problems with the query refereing to the process instance id order" is related to the application domain, so I think it is right in this forum.

            2.) I can not post any question to the hibernate forum, because I don't have credits there. So I can only hope somebody has an idea here, otherwise I think I can only leave it as open issue for the moment :-/

            • 3. Re: [sim] problems with hibernate queries and dates
              camunda
              • 4. Re: [sim] problems with hibernate queries and dates
                kukeltje

                Bernd,

                There is a problem with the stddev in the query. It does not work in HSQLDB see <a href=http://jira.jboss.com/jira/browse/JBPM-1081>http://jira.jboss.com/jira/browse/JBPM-1081

                • 5. Re: [sim] problems with hibernate queries and dates
                  camunda

                  Hi Ronald.

                  I know, but thanks for creating an issue for it. I commented a possible fix there.

                  Compatibility seems to be a big problem when it comes down to realy advanced queries :-/

                  • 6. Re: [sim] problems with hibernate queries and dates
                    camunda

                    I had some more problems today. I now think of skipping hibernate and using native queries here, because some of the stuff would really be easier and better. And I get rid of some problems I have at the moment...

                    I could provide mySQL and Oracle for the beginning, also HSQL shouldn't be hard. But f somebody needs more, he has to translate the queries himself.

                    Any thoughts?

                    • 7. Re: [sim] problems with hibernate queries and dates
                      camunda

                      Okay, seems you guys don't care much about native queries ;-) So I switch to native queries and start with mySQL.

                      • 8. Re: [sim] problems with hibernate queries and dates
                        kukeltje

                        I certainly don't mind ;-) But hey, who am I?

                        • 9. Re: [sim] problems with hibernate queries and dates
                          camunda

                          Your agreement means much to me ;-)

                          • 10. Re: [sim] problems with hibernate queries and dates
                            tom.baeyens

                             

                            "camunda" wrote:
                            I had some more problems today. I now think of skipping hibernate and using native queries here, because some of the stuff would really be easier and better. And I get rid of some problems I have at the moment...

                            I could provide mySQL and Oracle for the beginning, also HSQL shouldn't be hard. But f somebody needs more, he has to translate the queries himself.

                            Any thoughts?


                            sorry for the late reply.

                            up to now, we didn't use anything outside hibernate to get the best possible portability. i didn't see a strategy here to get the same level of portability in what you want to do.

                            here are my first questions

                            do you intend to build all the queries for all the dbs ? do you intend to maintain them ? for different versions of the dbs ? do you know that you can't rely on the community for that ?

                            that is why we accepted the downside of staying within the hibernate boundaries. but feel free to challenge that.

                            • 11. Re: [sim] problems with hibernate queries and dates
                              camunda

                              I see this problem too. But the problem is, that HQL is really too limited for some of the stuff. And at least TIME related queries are somehow database dependent, even with HQL.

                              What we could do is to provide a "base compatability implementation", which does the same job with "normal" HQL queries and Java-Code. This will perform much much slower I guess, but it could work portable.

                              And the user can just configure to use the "base" version or the optimized version, if the database is supported (my plans are mySQL 5 and Oracle 10).

                              • 12. Re: [sim] problems with hibernate queries and dates
                                tom.baeyens

                                portability wins against speed if you need to make the tradeoff.

                                indeed, a db compatibility layer could handle that. try to look into how people can customize the queries. can they customize the queries in the simulation queries hbm file ? meaning; is the API that we have to use to execute those queries the same for HQL as for native SQL queries ?

                                if we can work out a solution like that, we can provide portable configuration file with HQL queries (perhaps slow, but portable)

                                then people should be able to configure for their database a faster native SQL. but then their on their own. we could devote a wiki page where people could exchange their experiences.

                                but i expect that the hibernate API will not be exactly the same. so that needs to be figured out.

                                • 13. Re: [sim] problems with hibernate queries and dates
                                  camunda

                                  The Hibernate API is almost the same, but we need another command anyway. If we go for compatibility the Command has to calculate the standard derivation for itself for instance (means: get all log objects from database!), the speed optimized version can delegate this calculation to the database.

                                  So I think we can do the following:
                                  1.) Provide 2 Commands, one using the basic HQL's, one using the native queries
                                  2.) Provide 1 basic query.hbm.xml and 2 natives (mySQL & Oracle).

                                  So in default, you add the basic query.hbm.xml and use the basic command. Speed optimization means using a different command and configure the right query.hbm.xml. Or maybe write an own one for the database you need.

                                  • 14. Re: [sim] problems with hibernate queries and dates
                                    camunda

                                    Another thought: Maybe we shouldn't care too much about compatibility at this point for now, because the upcoming BAM tables should answer the questions much easier, and so even maybe more portable.

                                    Or the other way round: what I do here should be the responsibility of BAM, so we can maybe get a better solution from this area...