Lesson 4.4: Updating data
Building update queries
To update the values of attributes, we use chain Delete and Insert clauses. In effect, these queries delete an existing attribute of an entity (or relation) and then inserts a new one in its place. Consider the following two queries.
match
$order-38 isa order, has id "o0038";
$dispatched isa status "dispatched";
delete
has $dispatched of $order-38;→ Run but don’t commit
match
$order-38 isa order, has id "o0038";
insert
$order-38 has status "delivered";→ Run but close the transaction!
When run one after the other, they replace the existing order status "dispatched" with "delivered". We can do this more efficiently using a simple 3-stage query pipeline (just another name for a query that contains multiple clauses):
match
$order-38 isa order, has id "o0038";
$dispatched isa status "dispatched";
delete
has $dispatched of $order-38;
insert
$order-38 has status "delivered";→ Commit
In addition to being more efficient and easier for readers to interpret, these queries are sometimes a necessity if we’re replacing the attribute we’re matching on. In the following query, we match every book that has the genre "art", and replace it with the genre "art and design". Functionally, this represents a rename of the genre without changing the books in it. If we were to try this with a Delete query followed by an Insert query, then the Insert query would have nothing to match on!
#!test[schema]
#{{
#
# 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 agreed to in writing,
# software distributed under the License is distributed on an
# "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
# KIND, either express or implied. See the License for the
# specific language governing permissions and limitations
# under the License.
#
define
entity book @abstract,
owns isbn @card(0..2),
owns isbn-13 @key,
owns isbn-10 @unique,
owns title,
owns page-count,
owns genre @card(0..),
owns price,
plays contribution:work,
plays publishing:published,
plays promotion-inclusion:item,
plays order-line:item,
plays rating:rated,
plays recommendation:recommended;
entity hardback sub book,
owns stock;
entity paperback sub book,
owns stock;
entity ebook sub book;
entity contributor,
owns name,
plays contribution:contributor,
plays authoring:author,
plays editing:editor,
plays illustrating:illustrator;
entity company @abstract,
owns name;
entity publisher sub company,
plays publishing:publisher;
entity courier sub company,
plays delivery:deliverer;
entity publication,
owns year,
plays publishing:publication,
plays locating:located;
entity user,
owns id @key,
owns name,
owns birth-date,
plays action-execution:executor,
plays locating:located,
plays recommendation:recipient;
entity order,
owns id @key,
owns status,
plays order-line:order,
plays action-execution:action,
plays delivery:delivered;
entity promotion,
owns code @key,
owns name,
owns start-timestamp,
owns end-timestamp,
plays promotion-inclusion:promotion;
entity review,
owns id @key,
owns score,
owns verified,
plays rating:review,
plays action-execution:action;
entity login,
owns success,
plays action-execution:action;
entity address,
owns street,
plays delivery:destination,
plays locating:located;
entity place @abstract,
owns name,
plays locating:located,
plays locating:location;
entity city sub place;
entity state sub place;
entity country sub place;
relation contribution,
relates contributor,
relates work;
relation authoring sub contribution,
relates author as contributor;
relation editing sub contribution,
relates editor as contributor;
relation illustrating sub contribution,
relates illustrator as contributor;
relation publishing,
relates publisher,
relates published,
relates publication;
relation promotion-inclusion,
relates promotion,
relates item,
owns discount;
relation order-line,
relates order,
relates item,
owns quantity,
owns price;
relation rating,
relates review,
relates rated;
relation action-execution,
relates action,
relates executor,
owns timestamp;
relation delivery,
relates deliverer,
relates delivered,
relates destination;
relation locating,
relates located,
relates location;
relation recommendation,
relates recommended,
relates recipient;
attribute isbn @abstract, value string;
attribute isbn-13 sub isbn;
attribute isbn-10 sub isbn;
attribute title, value string;
attribute page-count, value integer;
attribute genre, value string;
attribute stock, value integer;
attribute price, value double;
attribute discount, value double;
attribute id, value string;
attribute code, value string;
attribute name, value string;
attribute birth-date, value datetime;
attribute street, value string;
attribute year, value integer;
attribute quantity, value integer;
attribute score, value integer;
attribute verified, value boolean;
attribute timestamp, value datetime;
attribute start-timestamp, value datetime;
attribute end-timestamp, value datetime;
attribute status, value string @regex("^(paid|dispatched|delivered|returned|canceled)$");
attribute success, value boolean;
# TODO: Change to check
fun is_review_verified_by_purchase($review: review) -> { order }:
match
($review, $product) isa rating;
($order, $product) isa order-line;
($user, $review) isa action-execution, has timestamp $review-time;
($user, $order) isa action-execution, has timestamp $order-time;
$review-time > $order-time;
return { $order };
fun book_recommendations_for($user: user) -> {book}:
match
$new-book isa book;
{
let $new-book in book_recommendations_by_author($user);
} or {
let $new-book in book_recommendations_by_genre($user);
};
return { $new-book };
fun book_recommendations_by_genre($user: user) -> { book }:
match
$user isa user;
$liked-book isa book;
{
($user, $order-for-liked) isa action-execution;
($order-for-liked, $liked-book) isa order-line;
} or {
($user, $review-for-liked) isa action-execution;
($review-for-liked, $liked-book) isa rating;
$review-for-liked has score >= 7;
};
$new-book isa book;
not { {
($user, $order-for-new) isa action-execution;
($order-for-new, $new-book) isa order-line;
} or {
($user, $review-for-new) isa action-execution;
($review-for-new, $new-book) isa rating;
}; };
$liked-book has genre $shared-genre;
$new-book has genre $shared-genre;
not { {
$shared-genre == "fiction";
} or {
$shared-genre == "nonfiction";
}; };
return { $new-book };
fun book_recommendations_by_author($user: user) -> { book }:
match
$user isa user;
$liked-book isa book;
{
($user, $order-for-liked) isa action-execution;
($order-for-liked, $liked-book) isa order-line;
} or {
($user, $review-for-liked) isa action-execution;
($review-for-liked, $liked-book) isa rating;
$review-for-liked has score >= 7;
};
$new-book isa book;
not { {
($user, $order-for-new) isa action-execution;
($order-for-new, $new-book) isa order-line;
} or {
($user, $review-for-new) isa action-execution;
($review-for-new, $new-book) isa rating;
}; };
($liked-book, $shared-author) isa authoring;
($new-book, $shared-author) isa authoring;
return { $new-book };
fun order_line_best_price($line: order-line) -> { double }:
match
($order) isa action-execution, has timestamp $order-time;
$line isa order-line, links ($order, $item);
$item has price $retail-price;
let $time_value = $order-time;
let $best-discount = best_discount_for_item($item, $time_value);
let $discounted-price = round(100 * $retail-price * (1 - $best-discount)) / 100;
$line has quantity $quantity;
let $line-total = $quantity * $discounted-price;
return { $line-total };
fun best_discount_for_item($item: book, $order-time: datetime) -> double:
match
{
$inclusion isa promotion-inclusion,
links ($promotion, $item),
has discount $discount-attr;
$promotion has start-timestamp <= $order-time,
has end-timestamp >= $order-time;
let $discount = $discount-attr;
} or {
let $discount = 0.0; # default
};
return max($discount);
fun transitive_places($place: place) -> { place }:
match
{
locating (located: $place, location: $parent);
} or {
locating (located: $place, location: $middle);
let $parent in transitive_places($middle);
};
return { $parent };
#}}
#!test[write]
match
$book isa book, has $art;
$art isa genre "art";
delete
has $art of $book;
insert
$book has genre "art and design";→ Commit!
Update shorthand
When updating an attribute ownership (or indeed, a role player in an existing relation) that exists exactly 0 or 1 times, you can use the update clause shorthand:
match
$book isa paperback, has isbn-10 "0060929790";
update $book has stock 20;The update clause will, for each statement written, set the new attribute to be the only attribute owned by the owner. In this example, for the specific paperback book, it sets the stock to 25, replacing any existing stock value.
Exercise
Use a match-delete-insert query pipeline to decrease the stock attribute of paperback Pride and Prejudice by one. In order to do so, you will need to first retrieve its current value.
Hint
To get the correct ISBN and current stock, you can use the following query.
match
$pride-prejudice isa paperback, has title "Pride and Prejudice";
fetch {
"isbn": [ $pride-prejudice.isbn ],
"stock": $pride-prejudice.stock,
};Sample solution
match
$pride-prejudice isa paperback, has isbn "9780553212150";
$stock isa stock 15;
delete
has $stock of $pride-prejudice;
insert
$pride-prejudice has stock 14;→ Commit
Of course, having to retrieve the current stock value before updating it is a bit inconvenient. We’ll learn how to use arithmetic operations to automatically decrement it in a single query in Lesson 7.5.