What?

Glue is a strongly typed, bi-temporal database programming language.

Why?

Because using SQL to interact with a bi-temporal database can be cumbersome.

How?

Glue completely hides the fact that you are working on a bi-temporal database.

SELECT pencil.price;

> price
  --------
  1.99 USD

The above Glue statement selects the current pencil price from a bi-temporal database, i.e. Glue automatically selects the latest available information by default.

However, in case you need to access past information then you can do so with a time-operator.

SELECT pencil.price AT '1 Jun 2012';

> price
  --------
  1.79 USD

Time-operators have a low precedence, i.e. they are executed before most other operators. For example, the following statement calculates the difference in pencil prices between 2012 and today.

SELECT pencil.(price - price AT '1 Jun 2012');

> col_0
  --------
  0.20 USD

In the above example the first occurrence of "price" is not affected by the time-operator, i.e. here the current price is selected by default.

Like with any other operator you can use parenthesis to change the execution order. For example, the following statement calculates the difference between pencil and paper prices for a past date.

SELECT (pencil.price - paper.price) AT '1 Jun 2012';

> col_0
  --------
  2.90 USD

Time-operators are also allowed on the left-hand-side of an assignment. For example, the following statement sets the pencil price for a past date.

UPDATE pencil.price AT '1 Jun 2012' := 1.66 USD;

> Update OK

// just to show that the price really changed
SELECT pencil.price AT '1 Jun 2012';

> price
  --------
  1.66 USD

Personal note: If you ever had to write the corresponding SQL code to the above update statement on a bi-temporal database then you will probably appreciate the straight forward simplicity of Glue.

In the above example we changed the pencil price, however, as Glue is a bi-temporal database language the (seemingly) overwritten price is still accessible by using the ASOF operator.

SELECT pencil.price AT '1 Jun 2012' ASOF YESTERDAY;

> price
  --------
  1.79 USD

Glue also provides access to the dates when information had been inserted or updated. For example, in the following statement we see the entire pencil price history together with the dates when each price became valid and also the dates at which the change had been made (note that the INSERT date in the third row is today's date as the change has been made by the above update statement today).

SELECT pencil.(price SINCE INCEPTION).(BEGIN, INSERT, VALUE);

> BEGIN         INSERT        VALUE
  ------------------------------------
  2018-07-20    2018-07-20    1.99 USD
  2014-06-30    2014-06-30    1.89 USD
  2012-06-01	2022-12-04    1.66 USD
  2012-01-01    2012-01-01    1.79 USD

For more technical details on time-operators please refer to U.S. patent No. 9,697,239 (e.g. Figs. 22 et seqq.)

What Else?

1. Intuitive Data Models

Glue data models are intuitively to understand because the language natively supports to-many relations and time-constant relations. This removes the need for artificial data model elements like link tables and validity timestamps that otherwise clutter a data model.

The above figure illustrates a very simple "shop-model" that can directly be used by Glue. For example, there is no need to translate the double-headed arrow between "Invoice" and "Article" to a relational link-table because Glue natively handles to-many relations.

The little lock symbols on the arrows indicate that the status of the referred to object is locked in. This means that, for example, changes to the price of an article are not reflected into an invoice (cf. section 3 below).

2. Object Tags

Object tags simplify the handling of objects. For example a new customer object for a customer with name "John Smith" can be tagged as "John".

INSERT Customer (
    first_name := John,
    last_name  := Smith,
    birthdate  := '21 Jun 1970',
    TAG        := John
 );

The customer can now be referred to by simply using the word "John". For example, an invoice for customer "John" can be created like this.

INSERT Invoice (
    date     := TODAY,
    receiver := John,
    items    := (pencil, paper)
);

3. Time-Constant Relations

Time-constant relations lock in the status of an object at the moment of assignment. For example, if we change the pencil price to 2 USD like this

UPDATE pencil.price := 2.00 USD;

> Update OK

then the pencil price in the invoice of customer "John" is still 1.99 USD because the pencil item has been assigned to the invoice before the price change.

SELECT John.invoices.items.(name, price);

> name      price
  ------------------
  pencil    1.99 USD
  paper     4.99 USD

It is noteworthy that today time-constant relations are typically implemented by manually copying the relevant objects which is a cumbersome and error prone process.

4. Units and Dimensions

Glue natively supports units and dimensions. For example, the price of an article is not just a plain number but a tuple of a number and a currency, e.g. "0.99 USD" or "1.99 EUR". Glue also supports automatic unit conversion.

SELECT 1 USD + 1 EUR;

> col_0
  ----------
  2.0555 USD

The conversion factor of currencies is time dependent, therefore the above statement returns a different result when executed for a different time.

SELECT (1 USD + 1 EUR) AT '20 Feb 2018';

> col_0
  ----------
  2.2338 USD

5. Convenience Aggregation

Glue defines a convenience aggregator (+) for all elements. For example, "1+2" is "3", "pencil + pencil" is "2 pencil", and "pencil + paper" is the list "(pencil, paper)". Convenience aggregation is also defined on paths. For example, the following statement aggregates the price over all items of all invoices.

SELECT +Invoice.items.price;

> price
  --------------
  104,325.17 USD

Moving down the plus operator in the path provides a simple and easy to use drill-down functionality.

SELECT Invoice.+items.price;

> price
  ----------
  532.00 USD
  332.50 USD
  898.10 USD
  (352 more)

// you can easily join related information, e.g.
SELECT Invoice.(receiver.last_name).+items.price;

> last_name   price
  ----------------------
  Cox         532.00 USD
  Codd        332.50 USD
  Ritchie     898.10 USD
  (352 more)

6. Derived Properties

Derived properties are properties that use program code to derive their value from other information. A very simple example is the derived property "age" that calculates the age of a customer based on its birthdate.

INSERT RELATION (
    TAG := age,
    SOURCE := Customer,
    TARGET := { 
        RETURN (TODAY - birthdate) IN Year; 
    }
);

A derived property can be used like a normal (read-only) property. For example the following statement selects the birthdate and age of customer "John".

SELECT John.(birthdate, age);

> birthdate     age
  ---------------------
  1970-06-21    52 Year

7. Conditional Types

A conditional type implicitly describes a set of objects. For example, a conditional type "Adult" can be defined as the set of all customers that are older than 18 years.

INSERT TYPE (
    TAG       := Adult,
    SOURCE    := Customer,
    CONDITION := { age >= 18 Year; }
 );

Conditional types allow concise statements (e.g. "if Peter is Adult") and can also be used for implicit input validation.

INSERT Adult (
    first_name := Tiny,
    last_name  := Tim,
    birthdate  := '1 Jun 2012'
);

> ERROR: Object does not satisfy condition of type 'Adult'.

8. Code Historicization (planned)

Code historicization allows to run old code on current data. This is useful when you want to see the effect that a code change has on your current data.

For example, if a government decides that beginning with 1 December 2022 a person is considered an adult from the moment it is 17 years old then we update the "Adult" definition like this.

UPDATE Adult.CONDITION SINCE '1 Dec 2022' := {
    age >= 17 Year; 
};

As 1 December 2022 lies in the past the new definition is used by default, however, the old logic is still accessible by using the "LOGIC AT" operator. For example, the following statement counts the number of adults using the different definitions.

SELECT (
    Adult.COUNT,
    Adult.COUNT LOGIC AT '30 Nov 2022'
);

> COUNT    COUNT_1
  ----------------
  24       23

It is noteworthy that the ASOF operator does not necessarily provides the same result.

SELECT (
    Adult.COUNT,
    Adult.COUNT ASOF '30 Nov 2022'
);

> COUNT    COUNT_1
  ----------------
  24       22

We get a different result because the ASOF operator mandates that all information that has been added after 30 November 2022 is invisible, i.e. any adult (customer) that has been added after that date is not included in COUNT_1 (while all customers until today are included in the first COUNT).

Imprint