Difference between maximum and minimum timestamps

[Jonathan Burke]

Hello,

There are three things I am trying to do and any help would be
appreciated:
1. Take the difference between the fields:
workflowRun.endedOn : max(Option[Timestamp]) and
workflowRun.startedOn : min(timestamp).

2. If endedOn = None use currentTime

3. I am grouping workflowRuns and want a clause like:
having((max(workflowRun.endedOn.get) < someLimit) and
(min(workflowRun.startedOn) > someLesserLimit))

The query I am using is:
join(ZamboniSchema.workflows,
ZamboniSchema.workflowRuns.leftOuter) ((wf, wfRun) =>
groupBy(wf.name)
compute((max((wfRun.get.endedOn.get.getTime)) -
min(wfRun.get.startedOn.getTime)).asInstanceOf[BinaryAMSOp[Option[Double],Option[Double]]])
on(wf.id === wfRun.get.workflowId))

And it generates the Statement:

Select
Workflow1.id as g0,
(max(Workflow_Run2.ended_On) - min(Workflow_Run2.started_On))
as c0
From
Workflow Workflow1
left outer join Workflow_Run Workflow_Run2 on (Workflow1.id =
Workflow_Run2.workflow_Id)
Group By
Workflow1.id

But you may notice a few issues:
1. I tried a number of different ways to get the max(timestamp) terms
to work (using toString and getTime both work but getTime is needed
for the subtraction operation). Oracle returns a decimal value when
taking the difference of two dates but getTime yields a long so I cast
the expression as shown to yield the following query output type:
Query[GroupWithMeasures[Long, Option[Double]]. So this works for my
first goal but is there a "Correct" way to do this as I would imagine
this is very Oracle specific?
2. I have tried a few different having statements and I am wondering
if my syntax is wrong. I've tried:

join(ZamboniSchema.workflows,
ZamboniSchema.workflowRuns.leftOuter) ((wf, wfRun) =>
groupBy(wf.name)

having(min(wfRun.get.startedOn.getTime) > twoWeeksAgo) //**
Relevant call

compute((max((wfRun.get.endedOn.get.getTime)) -
min(wfRun.get.startedOn.getTime)).asInstanceOf[BinaryAMSOp[Option[Double],Option[Double]]])
on(wf.id === wfRun.get.workflowId))

And receive a compiler error of:
error: type mismatch;
[scalac] found :
org.squeryl.dsl.ast.BinaryOperatorNodeLogicalBoolean
[scalac] required:
org.squeryl.dsl.ast.TypedExpressionNode[org.squeryl.dsl.ast.LogicalBoolean]
[scalac] having(min(wfRun.get.startedOn.getTime) >
twoWeeksAgo)

And even if I did something like this unrelated to timestamps:

join(ZamboniSchema.workflows,
ZamboniSchema.workflowRuns.leftOuter) ((wf, wfRun) =>
groupBy(wf.name)

having(avg(wfRun.id) > 0) //** Relevant call, though
avg(wfRun.id) is kind of useless

compute((max((wfRun.get.endedOn.get.getTime)) -
min(wfRun.get.startedOn.getTime)).asInstanceOf[BinaryAMSOp[Option[Double],Option[Double]]])
on(wf.id === wfRun.get.workflowId))

It still gives me a similar type mismatch. Am I using having wrong?

Thanks for the help and thanks for reading this far.

Jonathan Burke

[Maxime Lévesque]

 Hi, the Having clause was simply broken, I pushed a fix in the master branch,

for date subtractions, they are not implemented in the DSL, it will be pretty

trivial to implement *if* uniform behavior can be generalized

accross most supported database. I.e. oracle returns a float, if all databases

do the same, it will be trivial. So the first step is to survey supported databases,

please open an assembla issue, and if you happen to know about another DB or two,

write the info.

ML

[Jonathan Burke]

Will do.  Thanks, Max.

2011/3/1 Maxime Lévesque <maxime....@gmail.com>

[David Whittaker]

I can tell you that postgres returns an interval type from the command line.  I'm not sure what that gets converted to by the JDBC driver.

2011/3/1 Maxime Lévesque <maxime....@gmail.com>

[Alec Wysoker]

Hi Max,

I pulled from the master a couple of hours ago in order to grab your
fix to HAVING for Jonathan. I'm having a couple of problems:

- Squeryl unit test suite appears to be broken:

[info] x return the correct results if a left outer join is used
[info] 'Some(org.squeryl.dsl.GroupWithMeasures@62ea423e)' is not
equal to 'Some(14)' (AggregateFuncInOuterJoin.scala:99)
[info] == org.squeryl.tests.LeftJoinTest ==
[info]
[info] == test-finish ==
[error] Failed: : Total 8, Failed 1, Errors 0, Passed 6, Skipped 1
[info] == test-finish ==
[info]
[info] == test-cleanup ==
[info] == test-cleanup ==
[error] Error running org.squeryl.tests.LeftJoinTest: Test FAILED

- I'm getting a SQL exception in my own unit test suite. It appears
that something has gone wrong with "IN" functionality. My query looks
like this:

from(ZamboniSchema.steps)(s =>
where(s.workflowId === workflowId and
s._status.in(desiredStatuses.map(_.toString)))
select(s))

And the SQL looks like the following. Note that there are only 2 ?s
in the query, but the array of JDBC parameters is longer.

Select
Step1.fan_Out as Step1_fan_Out,
Step1.name as Step1_name,
Step1.child_Workflow_Id as Step1_child_Workflow_Id,
Step1.step_Runner as Step1_step_Runner,
Step1.id as Step1_id,
Step1.max_Tries as Step1_max_Tries,
Step1.created_At as Step1_created_At,
Step1.terminal_Step_Ordinal as Step1_terminal_Step_Ordinal,
Step1.workflow_Id as Step1_workflow_Id,
Step1.status as Step1_status
From
Step Step1
Where
((Step1.workflow_Id = ?) and (Step1.status in (?)))
jdbcParams:[1,RUNNING]
2011-03-01 12:17:16 [INFO] [wf: NestedWorkflow 3-1]: Select
Step1.fan_Out as Step1_fan_Out,
Step1.name as Step1_name,
Step1.child_Workflow_Id as Step1_child_Workflow_Id,
Step1.step_Runner as Step1_step_Runner,
Step1.id as Step1_id,
Step1.max_Tries as Step1_max_Tries,
Step1.created_At as Step1_created_At,
Step1.terminal_Step_Ordinal as Step1_terminal_Step_Ordinal,
Step1.workflow_Id as Step1_workflow_Id,
Step1.status as Step1_status
From
Step Step1
Where
((Step1.workflow_Id = ?) and (Step1.status in (?)))
jdbcParams:[1,STOPPED,STEP_RUNNER_FAILED,INTERNAL_ERROR,FAILED,UNKNOWN]
Exception while executing statement : Invalid value "3" for parameter
"parameterIndex" [90008-127]
errorCode: 90008, sqlState: 90008

Thanks, Alec

2011/3/1 Maxime Lévesque <maxime....@gmail.com>:

[Maxime Lévesque]

Ok, just pushed a for the broken test,

for the IN issue, there has been some changes in that area, the implicit convs are different,

but should be equivalent.

There are some test cases for this, and I added one more to that uses a string seq, just to make sure,

and I can`t reproduce your problem.

The implicit conv that gets called in your case is :

  implicit def traversableOfString2ListString(l: Traversable[StringType]) =

    new RightHandSideOfIn[StringType](new ConstantExpressionNodeList[StringType](l))

defined here : 

https://github.com/max-l/Squeryl/blob/master/src/main/scala/org/squeryl/dsl/DslFactory.scala#L119

can you try this : 

   from(ZamboniSchema.steps)(s =>
     where(s.workflowId === workflowId and

             s._status.in {

               val ds = desiredStatuses.map(_.toString).toSeq

               println(ds)

               ds

              })
     select(s))

?

Thanks

[Alec Wysoker]

Hi Max,

It does work if I add the .toSeq:

from(ZamboniSchema.steps)(s =>
where(s.workflowId === workflowId and

s._status.in(desiredStatuses.map(_.toString).toSeq))
select(s))

results in the right number of question marks in the query:

Select
Step1.fan_Out as Step1_fan_Out,
Step1.name as Step1_name,
Step1.child_Workflow_Id as Step1_child_Workflow_Id,
Step1.step_Runner as Step1_step_Runner,
Step1.id as Step1_id,
Step1.max_Tries as Step1_max_Tries,
Step1.created_At as Step1_created_At,
Step1.terminal_Step_Ordinal as Step1_terminal_Step_Ordinal,
Step1.workflow_Id as Step1_workflow_Id,
Step1.status as Step1_status
From
Step Step1
Where

((Step1.workflow_Id = ?) and (Step1.status in (?,?,?,?,?)))
jdbcParams:[1,STOPPED,STEP_RUNNER_FAILED,INTERNAL_ERROR,FAILED,UNKNOWN]

I then undid my change and made the following change to
DslFactory.scala, and this also worked:

diff --git a/src/main/scala/org/squeryl/dsl/DslFactory.scala
b/src/main/scala/org/squeryl/dsl/DslFactory.scala
index a0f7699..b9ee26d 100644
--- a/src/main/scala/org/squeryl/dsl/DslFactory.scala
+++ b/src/main/scala/org/squeryl/dsl/DslFactory.scala
@@ -108,17 +108,17 @@ trait DslFactory
// column/field types, so they don't need to be overridable factory methods :

implicit def traversableOfNumercalExpressionList[A <%
NumericalExpression[_]](l: Traversable[A]) =
- new RightHandSideOfIn[NumericalExpression[A]](new
ConstantExpressionNodeList[Any](l))
+ new RightHandSideOfIn[NumericalExpression[A]](new
ConstantExpressionNodeList[Any](l.toSeq))

// TODO : find out why this generalized conv for NonNumericals won't
work (looks like a scalac bug...):
// implicit def traversableOfNonNumercalExpressionList[A <%
NonNumericalExpression[_]](l: Traversable[A]) =
// new RightHandSideOfIn[NonNumericalExpression[A]](new
ConstantExpressionNodeList[Any](l))

implicit def traversableOfString2ListString(l: Traversable[StringType]) =

- new RightHandSideOfIn[StringType](new
ConstantExpressionNodeList[StringType](l))
+ new RightHandSideOfIn[StringType](new
ConstantExpressionNodeList[StringType](l.toSeq))

implicit def traversableOfDate2ListDate(l: Traversable[DateType]) =
- new RightHandSideOfIn[DateType](new
ConstantExpressionNodeList[DateType](l))
+ new RightHandSideOfIn[DateType](new
ConstantExpressionNodeList[DateType](l.toSeq))

implicit def typedExpression2OrderByArg[E <%
TypedExpressionNode[_]](e: E) = new OrderByArg(e)

Let me know if you want me to push this change onto a branch. I don't
really understand the DSL very well, so it is quite possible that this
change is incorrect or incomplete.

[Max]

Given that datetime subtraction has return type that varies among DBs, the solution would be to 

have a "lowest common denominator" return type of a double, for DBs that return an interval type,

some SQL function would be applied on the expression to convert it to a floating point number.

so the query :

  from(zs)(z=> select(&(z.d1 - z.d2)))

would generate :

- oracle :

  select (d1 - d2) as x from T

- on postgres :

  

  select extractNumberOfDaysFromInterval(d1 - d2) as x from T

of course, the function extractNumberOfDaysFromInterval will be whatever DB specific expression is 

required for the given DB.

Here's a draft of how it can be implemented :

//create a class DateTimeInterval extending org.squeryl.dsl.ast.TypeConversion :

class DateTimeInterval(d1: DateExpression[_], d2: DateExpression[_]) 

extends TypeConversion(e) with NumericalExpression[Double] {

  override def doWrite(sw: StatementWriter) = 

    sw.databaseAdapter.extractNumberOfDaysFromInterval(sw, d1,d2)

}

// add a - method to DateExpression : 

trait DateExpression[A] extends NonNumericalExpression[A] {

  def ~ = this

  def -(e: DateExpression[A]) = new DateTimeInterval(this, e)

}

That should do it ....

but of course, the method : 

  databaseAdapter.extractNumberOfDaysFromInterval(d1,d2)

needs to be implemented, 

in oracle it would simply be : 

def extractNumberOfDaysFromInterval(sw: StatementWriter, d1: DateExpression[A], d2: DateExpression[A]) = {

  d1.write(sw)

  sw.write(" - ")

  d2.write(sw)

}

ML

[Alec Wysoker]

Hi Max,

I neglected to say that desiredStatuses in the example below is a Set,
not a Seq.

-Alec

2011/3/1 Maxime Lévesque <maxime....@gmail.com>:

[Maxime Lévesque]

 In your test case, were there repetitions in your set ?

If it is a Set[String] then there can't be repetitions ....

The trait of the in function is Traversable, which Set does extend, I'm a little puzzled as

to why this is happening, this is the code involved :

class ConstantExpressionNodeList[T](val value: Traversable[T]) extends ExpressionNode {

  def isEmpty =

    value == Nil

  def doWrite(sw: StatementWriter) =

    if(sw.isForDisplay)

      sw.write(this.value.map(e=>"'" +e+"'").mkString(","))

    else {

      sw.write(this.value.map(z => "?").mkString(","))

      this.value.foreach(z => sw.addParam(z.asInstanceOf[AnyRef]))

    }

}

in this case, 

  yourSet.map(z => "?").mkString(",")

  yourSet.toSeq.map(z => "?").mkString(",")

gives different results....