I'm trying to turn things over:
WHERE ?docDateY - ?docDateX < 3
is translated to MySQL literally, so I just have to figure out what would
be the number for three days.
--
diogo patrão
On Fri, May 25, 2012 at 3:47 PM, Richard Cyganiak <[email protected]>wrote:
> You're out of luck then. You can write the date arithmetic in SQL inside
> the mapping, or write it as an ARQ extension (but then it's evaluated in
> memory as post-processing after SQL query evaluation), or do the maths
> before generating the SQL query. No other option exists at this time.
>
> Best,
> Richard
>
>
>
> On 25 May 2012, at 19:14, Diogo FC Patrao wrote:
>
> > That's a good idea, Richard, However, I'm benchmarking database
> integration architectures, and should keep the mapping as generic as
> possible. I presented a "30 days earlier" query, but other should use 29,
> 28, 31 days... and I can't have that many mappings =)
> >
> > I was toying with LET:
> >
> > SELECT DISTINCT ?patient
> > {
> > ?patient a :Patient;
> > :patientHasDocument ?docX;
> > :patientHasDocument ?docY.
> >
> > ?docX :documentType ?docTypeX;
> > :documentDateOfCreation ?docDateX;
> > .
> > ?docY :documentType ?docTypeY;
> > :documentDateOfCreation ?docDateY;
> > .
> >
> > LET( ?dateYminus3days := ?docDateY + "-P3D"^^xsd:duration )
> > FILTER(
> > ?docTypeX = "PRESCRIPTION"^^xsd:string &&
> > ?docTypeY = "PROGRESS NOTE"^^xsd:string &&
> > ?docDateX < ?docDateY &&
> > ?docDateX > ?dateYminus3days && #***
> > ?docDateX > "2012-02-21"^^xsd:date
> > )
> > }
> >
> > but surprisingly, no date condition is in the generated SQL ! removing
> the LET and the #*** condition, all conditions are translated.
> >
> > I also tried:
> >
> > ...
> > ?docDateX > ?docDateY - "P3D"^^xsd:duration
> > ...
> >
> > (which is accepted by ARQ now) D2R translates it to
> >
> > ... AND PRESCR_1.DOC_DATE > PRESCR_2.DOC_DATE - "P3D"
> >
> >
> >
> >
> >
> > --
> > diogo patrão
> >
> >
> >
> >
> >
> > On Fri, May 25, 2012 at 3:02 PM, Richard Cyganiak <[email protected]>
> wrote:
> > If you can express the conditions in SQL, then you can add some
> additional property bridges to the mapping that compute the condition using
> a sqlExpression. For example, the second condition can be expressed like
> this, using an additional property :documentDateOfCreationPlus30D
> >
> > map:Plus30Bridge a d2rq:PropertyBridge;
> > d2rq:classMap map:DocumentMap
> > d2rq:property :documentDateOfCreationPlus30D;
> > d2rq:sqlExpression "DATE_ADD(DOCUMENT.CREATION_DATE, "30D")";
> > d2rq:datatype xsd:date.
> >
> > (You'll have to substitute whatever is the correct SQL date manipulation
> syntax for your DB.)
> >
> > Then you can just query:
> >
> > ?docX :documentDateOfCreationPlus30D ?docDateX2.
> > FILTER (?docDateX2 > "2012-03-22"^^xsd:date)
> >
> > And I believe this would be translated to SQL correctly in 0.8.
> >
> > A similar approach could be used for the first condition although that's
> more tricky because it would require a bunch of extra joins in the mapping.
> >
> > Best,
> > Richard
> >
> >
> > On 25 May 2012, at 18:44, Diogo FC Patrao wrote:
> >
> > > Hello
> > >
> > > I was wondering if there's any way to translate complex date
> conditions to SQL in d2rq-0.8.
> > >
> > > Expanding a bit: I have this query
> > >
> > > SELECT DISTINCT ?patient
> > > {
> > > ?patient a :Patient;
> > > :patientHasDocument ?docX;
> > > :patientHasDocument ?docY.
> > >
> > > ?docX :documentType ?docTypeX;
> > > :documentDateOfCreation ?docDateX;
> > > .
> > > ?docY :documentType ?docTypeY;
> > > :documentDateOfCreation ?docDateY;
> > > .
> > >
> > > FILTER(
> > > ?docTypeX = "PRESCRIPTION"^^xsd:string &&
> > > ?docTypeY = "PROGRESS NOTE"^^xsd:string &&
> > > ?docDateX < ?docDateY &&
> > > ?docDateX > mylib:DateaddLibForARQ( ?docDateY, "-P3D" ) &&
> > > ?docDateX > mylib:DateaddLibForARQ( "2012-03-22", "-P30D" )
> > > )
> > > }
> > >
> > >
> > > The problem are the last two sentences in FILTER. I'm using custom
> java code to perform date operations such as "?docDateY minus three days"
> or "30 days before 22-mar-2012" (I know, I could substitute the second
> condition for a fixed one, but that's not my point - and it wouldn't solve
> the issue either, I just tried that).
> > >
> > > Problem is, those conditions are not translated to MySQL, so it runs a
> SQL query with the remaining conditions, mount results on memory and then
> filter. Also, the generated SQL is inneficient (--fast is on), it is
> running for 48 hours and counting!
> > >
> > > When I wrote this piece of SPARQL with the custom functions, I wasn't
> aware of any way of writing a timeframe condition that would be translated
> into SQL code. So I ask now,
> > >
> > > Is there a way?
> > >
> > > Thanks!
> > >
> > > --
> > > diogo patrão
> > >
> > >
> > >
> > >
> ------------------------------------------------------------------------------
> > > Live Security Virtual Conference
> > > Exclusive live event will cover all the ways today's security and
> > > threat landscape has changed and how IT managers can respond.
> Discussions
> > > will include endpoint security, mobile security and the latest in
> malware
> > > threats.
> http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/_______________________________________________
> > > d2rq-map-devel mailing list
> > > [email protected]
> > > https://lists.sourceforge.net/lists/listinfo/d2rq-map-devel
> >
> >
>
>
------------------------------------------------------------------------------
Live Security Virtual Conference
Exclusive live event will cover all the ways today's security and
threat landscape has changed and how IT managers can respond. Discussions
will include endpoint security, mobile security and the latest in malware
threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/
_______________________________________________
d2rq-map-devel mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/d2rq-map-devel
