IN5800 – Views, Triggers, Rules and Lore

Leif Harald Karlsen

Motivating example: Knowledge representation of family relations

CREATE TABLE person(
    pid int PRIMARY KEY,
    firstname text,
    lastname text,
    bdate date
);

CREATE TABLE mother(
    pid int REFERENCES person(pid),
    mother int REFERENCES person(pid)
);

CREATE TABLE father(
    pid int REFERENCES person(pid),
    father int REFERENCES person(pid)
);
-- and so on...
CREATE VIEW parent (pid, parent) AS
SELECT pid, mother FROM mother
UNION ALL
SELECT pid, father FROM father;

CREATE VIEW grandmother(pid, grandmother) AS
SELECT p.pid, m.mother
FROM parent AS p JOIN mother AS m ON (p.parent = m.pid);

-- and so on...
-- pid of grandmothers of 'Ole':

SELECT gm.mother
FROM person AS p
     JOIN mother AS m USING (pid)
     JOIN mother AS gm ON (m.mother = gm.pid)
WHERE p.firstname = 'Ole'
UNION ALL
SELECT gm.mother
FROM person AS p
     JOIN father AS f JOIN mother;
     JOIN mother AS gm ON (f.father = gm.pid)
WHERE p.firstname = 'Ole'

-- can be simplified to:

SELECT gm.grandmother
FROM person AS p
     JOIN grandmother AS gm USING (pid=
WHERE p.firstname = 'Ole';

Problems with Views: Equivalences

  • Views are equivalences
  • I.e. complete description of a term
  • Need to handle full complexity of terms for every statement
    • E.g. not possible to state “mothers are parents” without also specifying everything else that is a parent
    • Complex domains: this makes knowledge representation difficult
  • Every term is completely defined once and in one place
    • Cannot have different sources/files contributing to the definition of the same term
    • In big organizations: many different branches with their own specializations of a more general term

Problems with Views: Stratified/Hierarchical

  • Definitions become layered
    • Views built on views/tables in layers
    • Tables at the bottom
  • Views are (generally) non-insertable
    • I.e. one cannot insert (data) statements into a view
    • What if I only know that “x has parent y”?
    • Need to add to definition of term/view
    • (e.g. ... UNION ALL VALUES (x, y))
  • Definitions have an ordering/direction
    • E.g. cannot also state that “brothers are male siblings”
    • Cannot “define” a table
    • Cannot have cycles in the definitions

Small side-step: SQL functions

  • Can define new functions in SQL
  • Similar to other programming languages
  • Body is a string (enclosed in $<identifier>$, e.g. $body$)
  • Can use different languages (e.g. SQL, PLPGSQL)
  • SQL: Single SQL-query/command in body
  • PLPGSQL: procedural extension to SQL
    • IF, FOR, variables, etc.
    • Multiple SQL-commands in body
    • Needs BEGIN and END
    • DELCARE for variables
    • RETURN-statement
CREATE FUNCTION inc(n int) RETURNS int AS
$body$
  SELECT n+1;
$body$ language SQL;

CREATE FUNCTION five() RETURNS int AS
$body$
  SELECT 5;
$body$ language SQL;

SELECT inc(five()); --> 6



CREATE FUNCTION pick_person() RETURNS int AS
$body$
  DECLARE
    picked_person int;
  BEGIN
    picked_person = ( -- pick a person not already picked
      SELECT pid FROM person
      WHERE pid NOT IN (SELECT pid FROM picked)
      LIMIT 1
    );
      
    INSERT INTO picked -- add person to picked
    VALUES (picked_person);
      
    RETURN picked_person; -- returned the picked person
  END;
$body$ language PLPGSQL;

Triggers

  • Triggers are general event driven actions
  • Calls a function on database events (INSERT, UPDATE, DELETE)
  • Can happen BEFORE, AFTER or INSTEAD OF action
  • Can be executed for EACH ROW or EACH STATEMENT
CREATE TABLE person(
    pid int PRIMARY KEY,
    pname text
);
CREATE TABLE person_log(at timestamp, msg text);

CREATE TRIGGER log_ins_person
AFTER INSERT ON person
FOR EACH ROW
EXECUTE PROCEDURE log_ins_person_fn();

Trigger functions are arbitrary functions that:

  • Returns trigger
  • Taks no arguments
  • But can use the special constants NEW and OLD
  • NEW denotes the inserted/updated row
  • OLD is the row that is deleted (or before update)
CREATE FUNCTION log_ins_person_fn()
RETURNS trigger AS
$body$
BEGIN
  INSERT INTO person_log(at, msg)
  VALUES (
    now(),
    format('Insert: pid=%s, name=%s',
           NEW.pid, NEW.pname)
  );
  RETURN NEW;
END;
$body$ LANGUAGE plpgsql;

Triggers in Practice

Triggers for Knowledge Representation

CREATE FUNCTION mothers_are_parents_fnc() RETURNS trigger AS
$body$
  BEGIN
    INSERT INTO parent VALUES (NEW.pid, NEW.mother);
    RETURN NEW;
  END;
$body$ LANGUAGE plpgsql;

CREATE TRIGGER mothers_are_parents
AFTER INSERT ON mother
FOR EACH ROW
EXECUTE PROCEDURE mothers_are_parents_fnc();

PostgreSQL Rules

  • Special type of triggers with simpler syntax
  • Captures simple and common use of triggers
  • “Merges” trigger and function definition
  • Allows an SQL-command (INSERT, DELETE, UPDATE, SELECT) to be executed
  • In adition to (DO ALSO) or instead of (DO INSTEAD) an action
  • The SQL-command has access to NEW and OLD
-- Equivalent to trigger above
CREATE RULE mothers_are_parents AS
ON INSERT TO mother DO ALSO
INSERT INTO parent              
VALUES (NEW.pid, NEW.mother);

Logical Rules

Forward Chaining Rules

Backward Chaining Rules

Rules in Practice

Lore

  • Lore (Logical relations) is a small extension to (Postgres)SQL
  • It introduces three new keywords
    • RELATION: Behaves as both VIEW (can be defined) and TABLE (can INSERT INTO)
    • IMPLICATION: Can use rules/implications to define relations
    • IMPORT: Can import scripts
  • Also introduces a simplified (Datalog-like) syntax for certain Lore/SQL-statements
  • Pure extention: Regular SQL-statements are kept as is

Relations

CREATE RELATION person(id int, pname text NOT NULL);

Relations to SQL

The statement

CREATE RELATION person(id int, pname text NOT NULL);

gets translated into the following SQL:

CREATE TABLE person_stored (id int, pname text NOT NULL, UNIQUE (id, pname));

CREATE VIEW person_virtual AS
SELECT *
FROM person_stored
LIMIT 0;

CREATE VIEW person AS
SELECT *
FROM person_stored
UNION ALL
SELECT *
FROM person_virtual;

CREATE RULE person_insert_rule AS
ON INSERT TO person DO INSTEAD
INSERT INTO person_stored (SELECT NEW.id, NEW.name)
ON CONFLICT (id, pname) DO NOTHING;

Relations (cont.)

Implications

Implications: Example

CREATE RELATION person(id int, pname text NOT NULL);

CREATE RELATION student(id int, sname text NOT NULL);
CREATE RELATION employee(id int, ename text NOT NULL);

CREATE IMPLICATION person AS
SELECT * FROM student;

CREATE IMPLICATION person AS
SELECT * FROM employee;

INSERT INTO person VALUES (1, 'Ole');
INSERT INTO student VALUES (2, 'Per');
INSERT INTO employee VALUES (3, 'Kari');

SELECT * FROM person; gives:

--  id | pname 
-- ----+-------
--   1 | Ole
--   2 | Per
--   3 | Kari
-- (3 rows)

Implications: Translation to SQL

CREATE IMPLICATION person AS
SELECT * FROM student;

CREATE IMPLICATION person AS
SELECT * FROM employee;

'      ||                   '
'      || translation       '
'      ||                   '
'      \/                   '
         
CREATE VIEW person_virtual (id, pname) AS
SELECT * FROM employee
UNION ALL
SELECT * FROM student;

Forward vs. Backward Implication

Forward Implications: Example

CREATE FORWARD IMPLICATION person AS
SELECT * FROM student AS s;

CREATE FORWARD IMPLICATION person AS
SELECT * FROM employee AS e;

'      ||                   '
'      || translation       '
'      ||                   '
'      \/                   '

CREATE FUNCTION student_to_person_insert_trigger_fnc()
RETURNS trigger AS $body$
BEGIN
  INSERT INTO person
  SELECT * FROM (SELECT NEW.id, NEW.name) AS s;
  RETURN NEW;
END;
$body$ LANGUAGE plpgsql;

CREATE TRIGGER student_to_person_insert_trigger
AFTER INSERT ON student_stored
FOR EACH ROW
EXECUTE PROCEDURE student_to_person_insert_trigger_fnc();

INSERT INTO person
SELECT * FROM student AS s;


CREATE FUNCTION employee_to_person_insert_trigger_fnc()
RETURNS trigger AS $body$
BEGIN
  INSERT INTO person
  SELECT * FROM (SELECT NEW.id, NEW.name) AS e;
  RETURN NEW;
END;
$body$ LANGUAGE plpgsql;

CREATE TRIGGER employee_to_person_insert_trigger
AFTER INSERT ON employee_stored
FOR EACH ROW
EXECUTE PROCEDURE employee_to_person_insert_trigger_fnc();

INSERT INTO person
SELECT * FROM employee AS e;

Recursive Implications

CREATE IMPLICATION ancestor AS
SELECT * FROM parent;

CREATE IMPLICATION ancestor AS
SELECT p.pid, a.ancestor
FROM parent AS p JOIN ancestor AS a ON (p.parent = a.pid);

'      ||                   '
'      || translation       '
'      ||                   '
'      \/                   '

CREATE VIEW ancestor_virtual (pid, ancestor) AS
WITH RECURSIVE
tmp_subquery(pid, ancestor) AS (
    SELECT * FROM parent
    UNION ALL
    SELECT p.pid, a.ancestor_stored
    FROM parent AS p                                     -- Also include all explicit 
         JOIN ancestor_stored AS a ON (p.parent = a.pid) -- ancestors in recursion
    UNION ALL
    SELECT p.pid, a.tmp_subquery
    FROM parent AS p
         JOIN tmp_subquery AS a ON (p.parent = a.pid) -- Recursive call
  )
SELECT * FROM tmp_subquery;

Drop Relation

Recap

Simpler Syntax: Implications

person(id, sname) <- student(id, sname); -- backward-chaining rule
person(x, y) <- employee(x, y);

mother(x, y) -> parent(x, y);            -- forward-chaining rule

ancestor(x, y), ancestor(y, z) -> ancestor(x, z);

student(sid, sname), takes_course(sid, code, cname) : code >= 4000 AND code <= 6000 -> master_student(sid, sname);

ifi_student(sid, sname) <- student(sid, sname), takes_course(sid, code, cname) : cname LIKE 'IN%';

'      ||                   '
'      || translation       '
'      ||                   '
'      \/                   '

CREATE IMPLICATION person AS
SELECT id, sname FROM student AS t1(id, sname);

CREATE IMPLICATION person AS
SELECT x, y FROM employee AS t1(x, y);

CREATE FORWARD IMPLICATION parent AS
SELECT x, y FROM mother AS t1(x, y);

CREATE FORWARD IMPLICATION ancestor AS
SELECT x, z
FROM ancestor AS t1(x, y)
     NATURAL JOIN ancestor AS t2(y, z);

CREATE FORWARD IMPLICATION master_student AS
SELECT sid, sname
FROM student AS t1(sid, sname)
     NATURAL JOIN takes_course AS t2(sid, code, cname)
WHERE code >= 4000 AND code <= 6000;

CREATE IMPLICATION ifi_student AS
SELECT sid, sname
FROM student AS t1(sid, sname)
     NATURAL JOIN takes_course AS t2(sid, code, cname)
WHERE cname LIKE 'IN%';

Simpler Syntax: Inserts

student(1, 'Kari');
student(2, 'Per');

'      ||                   '
'      || translation       '
'      ||                   '
'      \/                   '

INSERT INTO student VALUES (1, 'Kari');
INSERT INTO student VALUES (2, 'Per');

Simpler Syntax: Queries

<- student(id, sname), ancestor(id, anc) : sname = 'Per' OR sname = 'Kari' ;

'      ||                   '
'      || translation       '
'      ||                   '
'      \/                   '

SELECT *
FROM student AS t1(id, sname) NATURAL JOIN ancestor AS t2(id, anc)
WHERE sname = 'Per' OR sname = 'Kari';

Imports

IMPORT 'file:~/code/person.lore';

IMPORT 'http://leifhka.org/lore/person.lore';

Lore Meta-data

Lore Program

java -jar lore.jar <flags> script.lore
java -jar lore.jar -h dbpg-ifi-kurs03 -U in5800_leifhka_user -d in5800_leifhka -P pwd123 script.lore

Issues and Things to Note

Lore and Knowledge Representation