[isis] 01/02: ISIS-1859: adds a hint-n-tip doc

[da...@apache.org]

This is an automated email from the ASF dual-hosted git repository.

danhaywood pushed a commit to branch maint-1.16.1
in repository https://gitbox.apache.org/repos/asf/isis.git

commit a47243ff9fb6aac28d9a4b80b807fb0693216532
Author: Dan Haywood <da...@haywood-associates.co.uk>
AuthorDate: Tue Feb 13 23:03:22 2018 +0000

    ISIS-1859: adds a hint-n-tip doc
---
 .../guides/ugodn/_ugodn_hints-and-tips.adoc        |  1 +
 ..._ugodn_hints-and-tips_jdoql-and-timestamps.adoc | 76 ++++++++++++++++++++++
 2 files changed, 77 insertions(+)

diff --git a/adocs/documentation/src/main/asciidoc/guides/ugodn/_ugodn_hints-and-tips.adoc b/adocs/documentation/src/main/asciidoc/guides/ugodn/_ugodn_hints-and-tips.adoc
index a85c7e5..17bf778 100644
--- a/adocs/documentation/src/main/asciidoc/guides/ugodn/_ugodn_hints-and-tips.adoc
+++ b/adocs/documentation/src/main/asciidoc/guides/ugodn/_ugodn_hints-and-tips.adoc
@@ -32,5 +32,6 @@ include::_ugodn_hints-and-tips_java8.adoc[leveloffset=+1,lines=3..-1]
 
 include::_ugodn_hints-and-tips_diagnosing-n-plus-1.adoc[leveloffset=+1,lines=3..-1]
 include::_ugodn_hints-and-tips_typesafe-queries-and-fetchgroups.adoc[leveloffset=+1,lines=3..-1]
+include::_ugodn_hints-and-tips_jdoql-and-timestamps.adoc[leveloffset=+1,lines=3..-1]
 
 // end::inclusions[]
diff --git a/adocs/documentation/src/main/asciidoc/guides/ugodn/_ugodn_hints-and-tips_jdoql-and-timestamps.adoc b/adocs/documentation/src/main/asciidoc/guides/ugodn/_ugodn_hints-and-tips_jdoql-and-timestamps.adoc
new file mode 100644
index 0000000..dad1ca5
--- /dev/null
+++ b/adocs/documentation/src/main/asciidoc/guides/ugodn/_ugodn_hints-and-tips_jdoql-and-timestamps.adoc
@@ -0,0 +1,76 @@
+:_basedir: ../../
+:_imagesdir: images/
+[[_ugodn_hints-and-tips_jdoql-and-timestamps]]
+= JDOQL and Timestamps
+:Notice: Licensed to the Apache Software Foundation (ASF) under one or more contributor license agreements. See the NOTICE file distributed with this work for additional information regarding copyright ownership. The ASF licenses this file to you under the Apache License, Version 2.0 (the "License"); you may not use this file except in compliance with the License. You may obtain a copy of the License at. http://www.apache.org/licenses/LICENSE-2.0 . Unless required by applicable law or ag [...]
+
+
+Beware of entities with a property called "timestamp": you run the risk of "timestamp" being treated as a keyword in certain contexts, probably not as you intended.
+
+By way of example, the (non-ASF) link:http://platform.incode.org[Incode Platform]'s command module has an entity called `CommandJdo`.
+This has a property called "timestamp", of type `java.sql.Timestamp`.
+
+This defines a query using JDOQL:
+
+[source,java]
+----
+SELECT
+FROM org.isisaddons.module.command.dom.CommandJdo
+WHERE executeIn == 'FOREGROUND'
+   && timestamp > :timestamp
+   && startedAt != null
+   && completedAt != null
+ORDER BY timestamp ASC
+----
+
+This is declared using a JDO `@Query`; no errors are thrown at any stage.
+
+However, running this query against SQL Server 2016 produced a different result first time it was run compared to subsequent times.
+
+Running SQL Profiler showed the underlying SQL as:
+
+[source,sql]
+----
+exec sp_prepexec @p1 output,N'@P0 datetime2',
+N'SELECT ''org.isisaddons.module.command.dom.CommandJdo'' AS NUCLEUS_TYPE,
+  A0.arguments,
+  ...,
+  A0.target,
+  A0."timestamp",
+  A0.transactionId,
+  A0."user",
+  ''2018-01-24 17:29:18.3'' AS NUCORDER0    // <1>
+FROM isiscommand.Command A0
+WHERE A0.executeIn = ''FOREGROUND''
+  AND A0."timestamp" > @P0
+  AND A0.startedAt IS NOT NULL
+  AND A0.completedAt IS NOT NULL
+  ORDER BY NUCORDER0
+  OFFSET 0 ROWS FETCH NEXT 2 ROWS ONLY ',   // <2>
+'2018-01-24 17:29:18.3000000'               // <3>
+----
+<1> discussed below ... this is the issue
+<2> because the query is submitted with max rows programmatically set to 2.
+<3> argument for @P0 (the timestamp parametr)
+
+
+To unpick this, the key issue is the `NUCORDER0` column, which is then used in the `ORDER BY`.
+However, because this is a literal value, the effect is no defined ordering.
+
+The problem therefore is that in the JDOQL the "ORDER BY timestamp ASC", the "timestamp" is being evaluated as the current time - a built-in function.
+
+My fix was to change the JDOQL to be:
+
+[source,sql]
+----
+SELECT
+FROM org.isisaddons.module.command.dom.CommandJdo
+WHERE executeIn == 'FOREGROUND'
+   && timestamp > :timestamp
+   && startedAt != null
+   && completedAt != null
+ORDER BY this.timestamp ASC             // <1>
+----
+<1> Use "this." to qualify the timestamp
+
+It wasn't necessary to qualify the other occurances of "timestamp" (though it would be no harm to do so, either).

-- 
To stop receiving notification emails like this one, please contact
danhaywood@apache.org.