You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@jena.apache.org by Rob Walpole <ro...@gmail.com> on 2013/06/13 17:56:48 UTC
Conditional INSERT statements
Hi there,
Hoping for some help with a SPARQL challenge I am facing...
We have an instance of Jena Fuseki to which we POST SPARQL update queries
to update the status of a thing called a transferAsset. A transferAsset is
actually a bNode which is referenced by a thing called a transfer. These
transferAssets have a subject, which is another resource, a status and a
modified date.
While there is a specific order in which status updates should be applied
(e.g SENDING, SENT, COMPLETED) there is no guarantee of the order in which
the updates will be received by Fuseki. In other words a COMPLETED status
may inadvertently be received before a SENDING status. To check that we
only apply the most recent status we check the modified date of the
transferAsset. If the new modified date is later than the existitng
modified date we delete the transferAsset at the same time as inserting a
new one.
The problem however is that although the deletion is conditional on there
being a transferAsset older than the new transferAsset, the insert is not,
and I am struggling to see how to make the insert conditional. This means
that if the updates are received out of order we end up inserting another
transferAsset with the same subject, i.e without deleting the old
transferAsset - not what we want.
The SPARQL code is shown below...
DELETE {
?transfer <http://nationalarchives.gov.uk/terms/dri#transferAsset>
?transferAsset .
}
INSERT {
?transfer <http://nationalarchives.gov.uk/terms/dri#transferAsset> _:b0 .
_:b0 <http://purl.org/dc/terms/subject> ?subject .
_:b0 <http://nationalarchives.gov.uk/terms/dri#transferAssetStatus> <
http://nationalarchives.gov.uk/dri/catalogue/transferAssetStatus#SENT> .
_:b0 <http://purl.org/dc/terms/modified> "2013-06-13T11:58:23.468Z"^^<
http://www.w3.org/2001/XMLSchema#dateTime> .
}
WHERE
{ ?transfer <http://purl.org/dc/terms/identifier> "201305241200"^^<
http://www.w3.org/2001/XMLSchema#string> .
?subject <http://purl.org/dc/terms/identifier>
"dff82497-f161-4afd-8e38-f31a8b475b43"^^<
http://www.w3.org/2001/XMLSchema#string>
OPTIONAL
{ ?transfer <http://nationalarchives.gov.uk/terms/dri#transferAsset>
?transferAsset .
?transferAsset <http://purl.org/dc/terms/subject> ?subject .
?transferAsset <http://purl.org/dc/terms/modified>
?transferAssetModified
FILTER ( ?transferAssetModified < "2013-06-13T11:58:23.468Z"^^<
http://www.w3.org/2001/XMLSchema#dateTime> )
}
}
And a sample transfer showing the duplication problem...
<http://nationalarchives.gov.uk/dri/catalogue/transfer/201305241200>
rdf:type dri:Transfer ;
dri:transferAsset
[ dri:transferAssetStatus
<
http://nationalarchives.gov.uk/dri/catalogue/transferAssetStatus#SENDING> ;
dcterms:modified
"2013-06-13T11:58:23.463Z"^^xsd:dateTime ;
dcterms:subject <
http://nationalarchives.gov.uk/dri/catalogue/item/dff82497-f161-4afd-8e38-f31a8b475b43
>
] ;
dri:transferAsset
[ dri:transferAssetStatus
<
http://nationalarchives.gov.uk/dri/catalogue/transferAssetStatus#SENT> ;
dcterms:modified
"2013-06-13T11:58:23.468Z"^^xsd:dateTime ;
dcterms:subject <
http://nationalarchives.gov.uk/dri/catalogue/item/dff82497-f161-4afd-8e38-f31a8b475b43
>
] ;
dri:transferStatus <
http://nationalarchives.gov.uk/dri/catalogue/transferStatus#COMPLETED> ;
dcterms:identifier "201305241200"^^xsd:string ;
dcterms:modified "2013-06-13T11:58:27.999Z"^^xsd:dateTime .
Many thanks
--
Rob Walpole
Email robkwalpole@gmail.com
Tel. +44 (0)7969 869881
Skype: RobertWalpolehttp://www.linkedin.com/in/robwalpole
Re: Conditional INSERT statements
Posted by Rob Walpole <ro...@gmail.com>.
Hi Andy,
Just to complete the thread and for anyone else that needs to do something
similar, we now have this working nicely using two operations, as you
suggested. Here is the code...
DELETE
{
?transfer dri:transferAsset ?transferAsset .
?transferAsset ?transferAssetProperty ?transferAssetValue .
}
WHERE
{
?transfer dcterms:identifier "20130628134601"^^xsd:string .
?subject dcterms:identifier
"9a10d7cf-c7e8-4e06-91c7-1d46aaa6e52e"^^xsd:string .
OPTIONAL
{
?transfer dri:transferAsset ?transferAsset .
?transferAsset dcterms:subject ?subject ;
dcterms:modified ?transferAssetModified ;
?transferAssetProperty ?transferAssetValue .
FILTER(?transferAssetModified <
"2013-06-28T15:21:00Z"^^xsd:dateTime)
}
};
INSERT
{
?transfer dri:transferAsset [
dri:transferAssetStatus <
http://nationalarchives.gov.uk/dri/catalogue/transferAssetStatus#COMPLETED>
;
dcterms:modified "2013-06-28T15:21:00Z"^^xsd:dateTime ;
dcterms:subject ?subject
]
}
WHERE
{
# where the transfer asset doesn't exist - i.e it is new or has not been
deleted
?transfer dcterms:identifier "20130628134601"^^xsd:string .
?subject dcterms:identifier
"9a10d7cf-c7e8-4e06-91c7-1d46aaa6e52e"^^xsd:string .
FILTER(NOT EXISTS { ?transfer dri:transferAsset [ dcterms:subject
?subject ] . })
}
So basically the delete is conditional, as before, but now the insert is
conditional on the delete having occurred. Easy when you know how :-)
Thanks for your help!
Rob
On Sat, Jun 15, 2013 at 2:02 PM, Andy Seaborne <an...@apache.org> wrote:
> PREFIX dri: <http://nationalarchives.gov.**uk/terms/dri#<http://nationalarchives.gov.uk/terms/dri#>
> >
> PREFIX rdfs: <http://www.w3.org/2000/01/**rdf-schema#<http://www.w3.org/2000/01/rdf-schema#>
> >
> PREFIX status: <http://nationalarchives.gov.**uk/dri/catalogue/**
> transferAssetStatus#<http://nationalarchives.gov.uk/dri/catalogue/transferAssetStatus#>
> >
> PREFIX dct: <http://purl.org/dc/terms/>
> PREFIX xsd: <http://www.w3.org/2001/**XMLSchema#<http://www.w3.org/2001/XMLSchema#>
> >
> PREFIX rdf: <http://www.w3.org/1999/02/22-**rdf-syntax-ns#<http://www.w3.org/1999/02/22-rdf-syntax-ns#>
> >
>
>
> DELETE {
> ?transfer dri:transferAsset ?transferAsset .
> }
> INSERT {
> ?transfer dri:transferAsset _:b0 .
> _:b0 dct:subject ?subject .
> _:b0 dri:transferAssetStatus status:SENT .
> _:b0 dct:modified "2013-06-13T11:58:23.468Z"^^**xsd:dateTime .
> }
> WHERE
> { ?transfer dct:identifier "201305241200"^^xsd:string .
> ?subject dct:identifier "dff82497-f161-4afd-8e38-**
> f31a8b475b43"^^xsd:string
>
> OPTIONAL
> { ?transfer dri:transferAsset ?transferAsset .
> ?transferAsset dct:subject ?subject .
> ?transferAsset dct:modified ?transferAssetModified
> FILTER ( ?transferAssetModified < "2013-06-13T11:58:23.468Z"^^**xsd:dateTime
> )
> }
> }
>
>
>
> Rob,
>
> (which version of the software?)
>
> The example data does not look like the DELETE was applied - there is
> still a dri:transferAsset link to the old state. I would have expected the
> bnode still to be there but the triple connecting it should have gone.
>
> If so, then the OPTIONAL is not matching -- it sets ?transferAsset.
>
> In your example, the
>
> ?subject dct:identifier ...
>
> does not match either but an INSERT does seem to have happened.
>
> Could you delete all ?transferAsset completely? The new state only
> depends on the new status if it's a legal state transition for the status.
>
> To cope with the fact that COMPLETED can come before SENDING, test the
> status.
>
>
>
> DELETE {
> ?transfer dri:transferAsset ?transferAsset .
> ?transferAsset ?p ?o .
> }
> INSERT {
> ?transfer dri:transferAsset _:b0 .
> _:b0 dct:subject ?subject .
> _:b0 dri:transferAssetStatus status:SENT .
> _:b0 dct:modified "2013-06-13T11:58:23.468Z"^^**xsd:dateTime .
> }
> WHERE {
> ?transfer dct:identifier "201305241200"^^xsd:string ;
> dri:transferAssetStatus ?status ;
> dri:transferAsset ?transferAsset .
> FILTER (?status != status:COMPLETED)
> ?transferAsset ?p ?o .
> } ;
>
>
> SPARQL Updates can be several operations in one request. It may be easier
> to have two operations
>
> DELETE { ... } WHERE { ... } ;
> INSERT { ... } WHERE { ... }
>
> Andy
>
--
Rob Walpole
Email robkwalpole@gmail.com
Tel. +44 (0)7969 869881
Skype: RobertWalpolehttp://www.linkedin.com/in/robwalpole
Re: Conditional INSERT statements
Posted by Andy Seaborne <an...@apache.org>.
PREFIX dri: <http://nationalarchives.gov.uk/terms/dri#>
PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>
PREFIX status:
<http://nationalarchives.gov.uk/dri/catalogue/transferAssetStatus#>
PREFIX dct: <http://purl.org/dc/terms/>
PREFIX xsd: <http://www.w3.org/2001/XMLSchema#>
PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#>
DELETE {
?transfer dri:transferAsset ?transferAsset .
}
INSERT {
?transfer dri:transferAsset _:b0 .
_:b0 dct:subject ?subject .
_:b0 dri:transferAssetStatus status:SENT .
_:b0 dct:modified "2013-06-13T11:58:23.468Z"^^xsd:dateTime .
}
WHERE
{ ?transfer dct:identifier "201305241200"^^xsd:string .
?subject dct:identifier
"dff82497-f161-4afd-8e38-f31a8b475b43"^^xsd:string
OPTIONAL
{ ?transfer dri:transferAsset ?transferAsset .
?transferAsset dct:subject ?subject .
?transferAsset dct:modified ?transferAssetModified
FILTER ( ?transferAssetModified <
"2013-06-13T11:58:23.468Z"^^xsd:dateTime )
}
}
Rob,
(which version of the software?)
The example data does not look like the DELETE was applied - there is
still a dri:transferAsset link to the old state. I would have expected
the bnode still to be there but the triple connecting it should have gone.
If so, then the OPTIONAL is not matching -- it sets ?transferAsset.
In your example, the
?subject dct:identifier ...
does not match either but an INSERT does seem to have happened.
Could you delete all ?transferAsset completely? The new state only
depends on the new status if it's a legal state transition for the status.
To cope with the fact that COMPLETED can come before SENDING, test the
status.
DELETE {
?transfer dri:transferAsset ?transferAsset .
?transferAsset ?p ?o .
}
INSERT {
?transfer dri:transferAsset _:b0 .
_:b0 dct:subject ?subject .
_:b0 dri:transferAssetStatus status:SENT .
_:b0 dct:modified "2013-06-13T11:58:23.468Z"^^xsd:dateTime .
}
WHERE {
?transfer dct:identifier "201305241200"^^xsd:string ;
dri:transferAssetStatus ?status ;
dri:transferAsset ?transferAsset .
FILTER (?status != status:COMPLETED)
?transferAsset ?p ?o .
} ;
SPARQL Updates can be several operations in one request. It may be
easier to have two operations
DELETE { ... } WHERE { ... } ;
INSERT { ... } WHERE { ... }
Andy
Re: Conditional INSERT statements
Posted by Rob Walpole <ro...@gmail.com>.
Hi Andy,
Thanks for your reply...
On Thu, Jun 13, 2013 at 11:26 PM, Andy Seaborne <an...@apache.org> wrote:
> Immediate comments:
>
> 1/
>
>
>
> DELETE {
> ?transfer <http://nationalarchives.gov.**uk/terms/dri#transferAsset<http://nationalarchives.gov.uk/terms/dri#transferAsset>
> >
> ?transferAsset .
> }
> INSERT {
> ?transfer <http://nationalarchives.gov.**uk/terms/dri#transferAsset<http://nationalarchives.gov.uk/terms/dri#transferAsset>>
> _:b0 .
> various triples with _:b0 as subject
>
>
> so the DELETE leaves the structure from the bNode in place even if
> unlinked?
>
>
Ah, so are you saying we need to do something like:
DELETE
{
?transfer dri:transferAsset ?transferAsset .
?transferAsset ?property ?value .
}
INSERT
{
....new transferAsset...
}
WHERE
{
?transfer dcterms:identifier "201305241200"^^xsd:string .
?subject dcterms:identifier"dff82497-f161-4afd-8e38-**f31a8
b475b43"^^xsd:string
OPTIONAL {
?transfer dri:transferAsset ?transferAsset .
?transferAsset dcterms:subject ?subject
?transferAsset dcterms:modified ?transferAssetModified ;
?property ?value .
FILTER (?transferAssetModified
"2013-06-13T11:58:23.468Z"^^xsd:dateTime)
}
}
Otherwise the bNode gets orphaned? I can see this may be wrong but I'm not
sure this would cause our problem which I believe is to do with the INSERT
not being conditional on the filter statement.
> 2/ In the data: I see:
>
> .../transferAssetStatus#SENT
>
> but
>
> .../transferStatus#COMPLETED
>
> Whta's
>
> dri:transferAsset
> [ dri:transferAssetStatus ..
> vs
>
> dri:transferStatus
>
>
Yes, I've probably given you too much information there. The transfer item
also has status (as well as it's transferAssets) but this is unrelated the
the problem.
Thanks
Rob
>
Re: Conditional INSERT statements
Posted by Andy Seaborne <an...@apache.org>.
Immediate comments:
1/
DELETE {
?transfer <http://nationalarchives.gov.uk/terms/dri#transferAsset>
?transferAsset .
}
INSERT {
?transfer <http://nationalarchives.gov.uk/terms/dri#transferAsset> _:b0 .
various triples with _:b0 as subject
so the DELETE leaves the structure from the bNode in place even if unlinked?
2/ In the data: I see:
.../transferAssetStatus#SENT
but
.../transferStatus#COMPLETED
Whta's
dri:transferAsset
[ dri:transferAssetStatus ..
vs
dri:transferStatus
Andy
On 13/06/13 16:56, Rob Walpole wrote:
> Hi there,
>
> Hoping for some help with a SPARQL challenge I am facing...
>
> We have an instance of Jena Fuseki to which we POST SPARQL update queries
> to update the status of a thing called a transferAsset. A transferAsset is
> actually a bNode which is referenced by a thing called a transfer. These
> transferAssets have a subject, which is another resource, a status and a
> modified date.
>
> While there is a specific order in which status updates should be applied
> (e.g SENDING, SENT, COMPLETED) there is no guarantee of the order in which
> the updates will be received by Fuseki. In other words a COMPLETED status
> may inadvertently be received before a SENDING status. To check that we
> only apply the most recent status we check the modified date of the
> transferAsset. If the new modified date is later than the existitng
> modified date we delete the transferAsset at the same time as inserting a
> new one.
>
> The problem however is that although the deletion is conditional on there
> being a transferAsset older than the new transferAsset, the insert is not,
> and I am struggling to see how to make the insert conditional. This means
> that if the updates are received out of order we end up inserting another
> transferAsset with the same subject, i.e without deleting the old
> transferAsset - not what we want.
>
> The SPARQL code is shown below...
>
> DELETE {
> ?transfer <http://nationalarchives.gov.uk/terms/dri#transferAsset>
> ?transferAsset .
> }
> INSERT {
> ?transfer <http://nationalarchives.gov.uk/terms/dri#transferAsset> _:b0 .
> _:b0 <http://purl.org/dc/terms/subject> ?subject .
> _:b0 <http://nationalarchives.gov.uk/terms/dri#transferAssetStatus> <
> http://nationalarchives.gov.uk/dri/catalogue/transferAssetStatus#SENT> .
> _:b0 <http://purl.org/dc/terms/modified> "2013-06-13T11:58:23.468Z"^^<
> http://www.w3.org/2001/XMLSchema#dateTime> .
> }
> WHERE
> { ?transfer <http://purl.org/dc/terms/identifier> "201305241200"^^<
> http://www.w3.org/2001/XMLSchema#string> .
> ?subject <http://purl.org/dc/terms/identifier>
> "dff82497-f161-4afd-8e38-f31a8b475b43"^^<
> http://www.w3.org/2001/XMLSchema#string>
> OPTIONAL
> { ?transfer <http://nationalarchives.gov.uk/terms/dri#transferAsset>
> ?transferAsset .
> ?transferAsset <http://purl.org/dc/terms/subject> ?subject .
> ?transferAsset <http://purl.org/dc/terms/modified>
> ?transferAssetModified
> FILTER ( ?transferAssetModified < "2013-06-13T11:58:23.468Z"^^<
> http://www.w3.org/2001/XMLSchema#dateTime> )
> }
> }
>
> And a sample transfer showing the duplication problem...
>
> <http://nationalarchives.gov.uk/dri/catalogue/transfer/201305241200>
> rdf:type dri:Transfer ;
> dri:transferAsset
> [ dri:transferAssetStatus
> <
> http://nationalarchives.gov.uk/dri/catalogue/transferAssetStatus#SENDING> ;
> dcterms:modified
> "2013-06-13T11:58:23.463Z"^^xsd:dateTime ;
> dcterms:subject <
> http://nationalarchives.gov.uk/dri/catalogue/item/dff82497-f161-4afd-8e38-f31a8b475b43
>>
> ] ;
> dri:transferAsset
> [ dri:transferAssetStatus
> <
> http://nationalarchives.gov.uk/dri/catalogue/transferAssetStatus#SENT> ;
> dcterms:modified
> "2013-06-13T11:58:23.468Z"^^xsd:dateTime ;
> dcterms:subject <
> http://nationalarchives.gov.uk/dri/catalogue/item/dff82497-f161-4afd-8e38-f31a8b475b43
>>
> ] ;
> dri:transferStatus <
> http://nationalarchives.gov.uk/dri/catalogue/transferStatus#COMPLETED> ;
> dcterms:identifier "201305241200"^^xsd:string ;
> dcterms:modified "2013-06-13T11:58:27.999Z"^^xsd:dateTime .
>
> Many thanks
>