I've set up D2RQ against an Oracle database, and for some queries I get good
results, but the query I actually need takes about 8 minutes and then produces
nothing. Investigation has lead me to believe that the problem may be how D2RQ
treats Oracle DATE values (which really are date+time).
Let's go through my reasoning. This query works fine, yielding instant results:
SELECT * WHERE { ?s a nav:Person } LIMIT 10
This query takes 8 minutes to do nothing:
SELECT * WHERE { ?s a nav:Person; nav:updated ?t } LIMIT 10
Checking the database I see that there are 2.8 million rows in the PERSON
table, and every single one has a value in the MOD_DATO column.
Looking at the console we find:
11:49:57 INFO SPARQL :: Query: SELECT * WHERE { ?s a <http://psi.
nav.no/2012/mod/Person>; <http://psi.nav.no/2012/mod/updated> ?t } limit 10
11:49:57 DEBUG QueryExecutionIterator :: SELECT DISTINCT "T1_PERSON"."PERSON_ID"
, "T2_PERSON"."MOD_DATO" FROM "PERSON" "T1_PERSON", "PERSON" "T2_PERSON" WHERE "
T1_PERSON"."PERSON_ID" = "T2_PERSON"."PERSON_ID"
So this is not mapped to optimal SQL, but it looks correct to me.
I tried simplifying the SPARQL query to just
SELECT * WHERE { ?s nav:updated ?t } LIMIT 10
This time we get:
11:12:17 INFO SPARQL :: Query: SELECT * WHERE { ?s <http://psi.na
v.no/2012/mod/updated> ?t } limit 10
11:12:17 DEBUG QueryExecutionIterator :: SELECT DISTINCT "PERSON"."MOD_DATO", "P
ERSON"."PERSON_ID" FROM "PERSON"
11:20:06 INFO SPARQL :: OK/select: SELECT * WHERE { ?s <http://ps
i.nav.no/2012/mod/updated> ?t } limit 10
Again the SQL looks slow but right, but I get no results. Running the SQL query
directly gives me results that look perfectly fine.
The relevant part of my mapping file is:
map:Person a d2rq:ClassMap;
d2rq:dataStorage _:Database1;
d2rq:class nav:Person;
d2rq:uriPattern
"http://psi.nav.no/2012/mod/arena/person/@@PERSON.PERSON_ID@@";
.
_:Oppdatert a d2rq:PropertyBridge;
d2rq:belongsToClassMap map:Person;
d2rq:property nav:updated;
d2rq:column "PERSON.MOD_DATO";
d2rq:datatype xsd:dateTime;
.
There's also a column ETTERNAVN mapped to foaf:familyName (xsd:string) in the
same way. If I query for that with
SELECT * WHERE { ?s a nav:Person>; foaf:familyName ?f } limit 10
I get correct results instantly.
This led me to suspect that the declaration of MOD_DATO as xsd:dateTime might
be the problem. I changed the mapping to xsd:string and tried querying for
nav:updated/MOD_DATO again, and now I get instant results. Sadly, however, they
are just dates represented as strings, and I really do need the full date and
time.
Investigation of the schema shows that MOD_DATO is declared as DATE, but SQL
queries on it return full date/time values like 2010-05-28 01:59:04.0. Further
investigation shows that Oracle DATE values actually do include the time:
http://docs.oracle.com/cd/B19306_01/server.102/b14220/datatype.htm#i1847
So... is this a bug in D2RQ, in the sense that you've mapped the Oracle DATE
type to xsd:date when it should in fact map to xsd:dateTime?
--Lars M.
http://www.garshol.priv.no/tmphoto/
http://www.garshol.priv.no/blog/
------------------------------------------------------------------------------
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
