Lesson 4.1: Inserting simple data

The Insert query

To insert new data into a database, we use an Insert clause. The following query inserts a new user.

insert
$evelin isa user,
    has id "u0011",
    has name "Evelin Griffith",
    has birth-date 1994-08-03;

This Insert query consists of a single insert clause, which inserts a single entity of type user and assigns it three attributes: one each of types id, name, and birth-date.

Using a write transaction, try running. After running it, make sure to commit the transaction. The new user should now be in the database! You can check by switching to a new read transaction and then running the following query, which lists the names of users in the database.

match
$user isa user;
fetch { "name": $user.name };

If you have correctly executed the Insert query, the new user Evelin should appear in the results.

Exercise

Insert a new promotion entity with the following attributes:

  • A code attribute with value "SPR24".

  • A name with value "Spring Sale 2024".

  • A start-timestamp with value 2024-03-15T00:00:00.

  • An end-timestamp with value 2024-03-31T23:59:59.

Sample solution 
insert
$spring-sale isa promotion,
    has code "SPR24",
    has name "Spring Sale 2024",
    has start-timestamp 2024-03-15T00:00:00,
    has end-timestamp 2024-03-31T23:59:59;

Don’t forget to commit!

TypeDB 3 supports date, datetime and datetime-tz (datetime with timezone)

These generally look like this:

  • yyyy-mm-dd

  • yyyy-mm-ddThh:mm

  • yyyy-mm-ddThh:mm:ss

  • yyyy-mm-ddThh:mm:ss.fffffffff

  • yyyy-mm-ddThh:mm:ss.fffffffff <IANA timezone TZ identifier>

  • yyyy-mm-ddThh:mm:ss.fffffffff±HHMM (with ISO 8601 timezone specifier)

All datetimes are stored at nanosecond precision, with missing digits filled by zeroes.

Inserting multiple objects

An Insert query can insert any number of entities and relations. In the following query, we insert four new entities: a paperback, a contributor, a publisher, and a publication event, along with two new relations between them: an authoring relation and a publishing relation.

insert
$frankenstein isa paperback,
    has isbn-13 "9780486282114",
    has title "Frankenstein",
    has page-count 166,
    has genre "fiction",
    has genre "horror",
    has price 5.79,
    has isbn-10 "0486282112";
$shelley isa contributor,
    has name "Shelley, Mary";
$dover isa publisher,
    has name "Dover Publications";
$publication isa publication,
    has year 1994;
authoring (work: $frankenstein, author: $shelley);
publishing (published: $frankenstein, publisher: $dover, publication: $publication);

Reminder: commit your changes!

Notice that we have given the book two genre attributes! This is perfectly permissible since your schema permits it with @card(0..). All attributes in TypeDB may multivalued with this setting, with the default being 0 or 1 attributes with @card(0..1). Cardinality configuration is an important part of constraining your data into the shapes and semantics you desire - such as allowing an entity or relation to own any number of attributes of the same type, or allowing exactly 1, or any other range of quantities of them.

Exercise

Insert two new users. The first user should have:

  • The user ID "u0012".

  • The name Fiona Carlson.

  • The birth date 10th July 1951.

The second user should have:

  • The user ID "u0013".

  • The name Jordan Duarte.

  • The birth date 31st October 1963.

Sample solution 
insert
$fiona isa user,
    has id "u0012",
    has name "Fiona Carlson",
    has birth-date 1951-07-10;
$jordan isa user,
    has id "u0013",
    has name "Jordan Duarte",
    has birth-date 1963-10-31;

Commit reminder!

Inserting references to existing objects

So far, the Insert queries we’ve seen have only created entirely new entities, relations between them, and their attributes. We can also write Insert queries that create relations between existing entities, or add new attributes to existing entities or relations. To do so, we use an Insert query with two clauses: a match clause followed by an insert clause. In the following example, we record the stock of the newly added edition of Frankenstein.

match
$book isa book, has isbn-13 "9780486282114";
insert
$book has stock 20;

→ Commit

This query matches the book we inserted previously by its ISBN, and then gives it a new stock attribute. In a Fetch query, the match clause identifies matching data within the database, and the fetch clause specifies what to retrieve from each match. In an Insert query, the match clause serves the same function, but then we insert new data for each match rather than retrieving existing data.

In order to insert a new attribute, we have to match the attribute’s existing owner, represented in the last query by the $book variable. Similarly, in the next query, we insert a new relation by matching the relation’s existing role players, represented by the $city and $publication variables. It records the fact that the publication of this edition of Frankenstein by Dover Publications took place in NYC.

match
$book isa paperback, has isbn-13 "9780486282114";
$publisher isa publisher, has name "Dover Publications";
$publication isa publication, has year 1994;
publishing (published: $book, publisher: $publisher, publication: $publication);
$city isa city, has name "New York City";
insert
locating (location: $city, located: $publication);

→ Commit

An Insert query without a match clause always inserts the data described in the insert clause once. However, an Insert query with a match clause will insert the data described in the insert clause multiple times, once for each match found. The following query would assign a stock attribute to every paperback in the database, even if it already had one!

match
$book isa paperback;
insert
$book has stock 20;

→ DON’T COMMIT YET

Likewise, the following query would create a locating relation for every publication entity with a year attribute equal to 1994.

match
$publication isa publication, has year 1994;
$city isa city, has name "New York City";
insert
locating (location: $city, located: $publication) isa locating;

→ Close your transaction without committing

Since we don’t want these changes to be permanent (they’re just for demonstration), simply close your transaction to throw away the changes. Before committing, your changes will live as a set of changes in the transactional snapshot of your database. If you did commit, those changes are merged back into the database. If you don’t, they are simply discarded. Any changes before commit are invisible to other transactions.

It is often useful to structure the match clause around the values of one or more key attributes, which can only be matched once. We have done this above using the key attribute isbn-13, as the value "9780486282114" uniquely identifies a single book. We will see how to define key attributes in Lesson 5.1.

Putting data

TypeDB 3.0 ships with a new alternative to insert: the put clauses. Put clauses check if the pattern provided exists in its entirety, and if it does not, it inserts the entire pattern:

put $book isa book, has isbn "0195153448";

A put is equivalent to doing:

match not { <pattern> };
insert <pattern>;

It’s highly recommended use put with a key-ed attributes. This will prevent duplicates from emerging during concurrent transactions, as one will be forced to fail.

Exercise

Insert a new login entity with a success attribute with value true, link that entity to the existing user Fiona Carlson (created in the last exercise) using an action-execution relation, and give that relation a timestamp attribute with value 2024-03-04T07:32:03.287.

Hint 1

You can get the labels of the roles in the action-execution relation with the following Fetch query.

#!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[read]
match
action-execution relates $role;
fetch {
  "role": $role,
};
Hint 2

Make sure to identify Fiona by her unique user ID "u0010" so as not to match other users with the same name!

Sample solution 
match
$user isa user, has id "u0012";
insert
$login isa login, has success true;
$rel isa action-execution,
  links (action: $login, executor: $user),
  has timestamp 2024-03-04T07:32:03.287;

→ Commit!