materialised_views.sql

-- DROP SCHEMA public CASCADE;
-- CREATE SCHEMA public;

-- the document table keeps track of the latest view for each document.
-- deleting the document will cascade through "document_view" and the different
-- view field columns below.

CREATE TABLE "document" (
	"document_id"	INTEGER PRIMARY KEY,
    "latest_view" INTEGER DEFAULT NULL
);

-- the document view table keeps a record of all document views we have 
-- materialised. 

CREATE TABLE "document_view" (
	"view_id"	INTEGER PRIMARY KEY,
	"document_id"	INTEGER,
    "schema" TEXT NOT NULL,
    FOREIGN KEY (document_id) REFERENCES document (document_id) ON DELETE CASCADE
);

ALTER TABLE document
    ADD CONSTRAINT fk_document_latest_view
    FOREIGN KEY (latest_view) REFERENCES document_view (view_id);

-- these "view_field_" tables keep the field values for all materialised views. 
-- there is one of these for every field type to represent the different values
-- and required additional data such as the item index for relation list fields.

CREATE TABLE "view_field_str" (
	"view_id"	INTEGER NOT NULL,
	"name"	TEXT NOT NULL,
	"value"	TEXT NOT NULL,
    FOREIGN KEY (view_id) REFERENCES document_view(view_id) ON DELETE CASCADE
);

-- this index lets us access fields by view_id and name
CREATE UNIQUE INDEX named_view_field_str ON view_field_str (name, view_id);

CREATE TABLE "view_field_int" (
	"view_id"	INTEGER NOT NULL,
	"name"	TEXT NOT NULL,
	"value"	INTEGER NOT NULL,
    FOREIGN KEY (view_id) REFERENCES document_view(view_id) ON DELETE CASCADE
);

CREATE UNIQUE INDEX named_view_field_int ON view_field_int (name, view_id);

CREATE TABLE "view_field_relation_list" (
	"view_id"	INTEGER NOT NULL,
	"name"	TEXT NOT NULL,
	"value"	INTEGER NOT NULL,
    "item_index" INTEGER NOT NULL,
    FOREIGN KEY (view_id) REFERENCES document_view (view_id) ON DELETE CASCADE
);

-- the index for list field types is not unique because there may be multiple
-- rows that represent the invidual list entries
CREATE INDEX named_view_field_relation_list ON view_field_relation_list (name, view_id);

-- insert data

-- this is creating two document views for the same document with three fields,
-- including a list field

INSERT INTO document ("document_id", "latest_view") VALUES (1, NULL);

INSERT INTO document_view ("document_id", "view_id", "schema") VALUES (1, 1, 'schema_1');
INSERT INTO view_field_int ("view_id", "name", "value") VALUES (1, 'age', 10);
INSERT INTO view_field_str ("view_id", "name", "value") VALUES (1, 'name', 'Bogundo');
INSERT INTO view_field_str ("view_id", "name", "value") VALUES (1, 'color', 'Orange');
INSERT INTO view_field_relation_list ("view_id", "name", "value", "item_index") VALUES (1, 'friends', 2, 0);
INSERT INTO view_field_relation_list ("view_id", "name", "value", "item_index") VALUES (1, 'friends', 3, 1);

INSERT INTO document_view ("document_id", "view_id", "schema") VALUES (1, 2, 'schema_1');
INSERT INTO view_field_int ("view_id", "name", "value") VALUES (2, 'age', 900);
INSERT INTO view_field_str ("view_id", "name", "value") VALUES (2, 'name', 'Acuato');
INSERT INTO view_field_str ("view_id", "name", "value") VALUES (2, 'color', 'Crimson');
INSERT INTO view_field_relation_list ("view_id", "name", "value", "item_index") VALUES (2, 'friends', 2, 0);
INSERT INTO view_field_relation_list ("view_id", "name", "value", "item_index") VALUES (2, 'friends', 3, 1);

-- set latest view after the view has been inserted
UPDATE document SET latest_view=2 WHERE document_id=1;


-- materialised view

-- schema_1_documents provides a view of the field values of all 'schema_1' 
-- documents' latest views

CREATE VIEW "schema_1_documents" AS
  SELECT 
    document.document_id,
    document_view.view_id,
    name_field.value as "name",
    color_field.value as "color",
    age_field.value as "age",
    friends_field.value as "friends",
    friends_field.item_index as "friends_index"
  FROM
    document
  LEFT JOIN document_view
    ON
      document_view.view_id = document.latest_view
  LEFT JOIN view_field_str name_field
    ON
      name_field.view_id = document_view.view_id
    AND 
      name_field.name = 'name'
  LEFT JOIN view_field_str color_field
    ON
      color_field.view_id = document_view.view_id
    AND 
      color_field.name = 'color'
  LEFT JOIN view_field_int age_field
    ON
      age_field.view_id = document_view.view_id
    AND 
      age_field.name = 'age'
  LEFT JOIN view_field_relation_list friends_field
    ON
      friends_field.view_id = document_view.view_id
    AND 
      friends_field.name = 'friends'
  WHERE document_view.schema = 'schema_1';
      
EXPLAIN ANALYZE SELECT * FROM schema_1_documents;

--  document_id | view_id |  name  |  color  | age | friends | friends_index 
-- -------------+---------+--------+---------+-----+---------+---------------
--            1 |       2 | Acuato | Crimson | 900 |       2 |             0
--            1 |       2 | Acuato | Crimson | 900 |       3 |             1

-- these results contain two rows to represent the different values of the 
-- `friends` field, all other columns are identical for all rows


-- schema_1_views provides a comprehensive view of all document views for all
-- documents of schema 'schema_1'

CREATE VIEW "schema_1_views" AS
  SELECT 
    document.document_id,
    document_view.view_id,
    (document.latest_view = document_view.view_id) as "is_latest",
    name_field.value as "name",
    color_field.value as "color",
    age_field.value as "age",
    friends_field.value as "friends"
  FROM
    document
  LEFT JOIN document_view
    ON
      document_view.document_id = document.document_id
  LEFT JOIN view_field_str name_field
    ON
      name_field.view_id = document_view.view_id
    AND 
      name_field.name = 'name'
  LEFT JOIN view_field_str color_field
    ON
      color_field.view_id = document_view.view_id
    AND 
      color_field.name = 'color'
  LEFT JOIN view_field_int age_field
    ON
      age_field.view_id = document_view.view_id
    AND 
      age_field.name = 'age'
  LEFT JOIN view_field_relation_list friends_field
    ON
      friends_field.view_id = document_view.view_id
    AND 
      friends_field.name = 'friends'
  WHERE document_view.schema = 'schema_1';
  
SELECT DISTINCT document_id, view_id, is_latest, age FROM schema_1_views;

--  document_id | view_id | is_latest | age 
-- -------------+---------+-----------+-----
--            1 |       1 | f         |  10
--            1 |       2 | t         | 900