materialised_views2.sql

-- 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" (
	"view_id"	INTEGER NOT NULL,
	"name"	TEXT NOT NULL,
  -- postgres doesn't support `BLOB` fields, so this is `TEXT`
	"value"	TEXT NOT NULL,
    "item_index" INTEGER,
    FOREIGN KEY (view_id) REFERENCES document_view(view_id) ON DELETE CASCADE
);

-- this index lets us access fields by view_id and name
CREATE INDEX named_view_field ON view_field (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 ("view_id", "name", "value") VALUES (1, 'age', '10');
INSERT INTO view_field ("view_id", "name", "value") VALUES (1, 'name', 'Bogundo');
INSERT INTO view_field ("view_id", "name", "value") VALUES (1, 'color', 'Orange');
INSERT INTO view_field ("view_id", "name", "value", "item_index") VALUES (1, 'friends', '2', 0);
INSERT INTO view_field ("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 ("view_id", "name", "value") VALUES (2, 'age', '900');
INSERT INTO view_field ("view_id", "name", "value") VALUES (2, 'name', 'Acuato');
INSERT INTO view_field ("view_id", "name", "value") VALUES (2, 'color', 'Crimson');
INSERT INTO view_field ("view_id", "name", "value", "item_index") VALUES (2, 'friends', '2', 0);
INSERT INTO view_field ("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 name_field
    ON
      name_field.view_id = document_view.view_id
    AND 
      name_field.name = 'name'
  LEFT JOIN view_field color_field
    ON
      color_field.view_id = document_view.view_id
    AND 
      color_field.name = 'color'
  LEFT JOIN view_field age_field
    ON
      age_field.view_id = document_view.view_id
    AND 
      age_field.name = 'age'
  LEFT JOIN view_field 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 
-- -------------+---------+--------+---------+-----+---------
--            1 |       2 | Acuato | Crimson | 900 |       2
--            1 |       2 | Acuato | Crimson | 900 |       3

-- 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 name_field
    ON
      name_field.view_id = document_view.view_id
    AND 
      name_field.name = 'name'
  LEFT JOIN view_field color_field
    ON
      color_field.view_id = document_view.view_id
    AND 
      color_field.name = 'color'
  LEFT JOIN view_field age_field
    ON
      age_field.view_id = document_view.view_id
    AND 
      age_field.name = 'age'
  LEFT JOIN view_field 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, age FROM schema_1_views;

--  document_id | view_id | age 
-- -------------+---------+-----
--            1 |       1 |  10
--            1 |       2 | 900