-- This is the SQL schema which describes the structure of a Weft QDA
-- database. It allows saving and querying of Ruby objects which use the
-- QDA::Persistent module. The schema is designed for SQLite version 3,
-- although it includes references to some features that aren't fully
-- supported in SQLite, such as enforcement of foreign key constraints.

-- Below, the basic table structure is described first. After that, a
-- number of virtual tables (VIEWs) are set up, with INSTEAD OF triggers
-- to permit INSERT, UPDATE and DELETE operations. These VIEWs support
-- inheritance relationships between persistent classes (polymorphic
-- classes).

-- REAL TABLES

-- This table works around a problem with using triggers to insert in
-- steps in polymorphic classes, where LAST_INSERT_ID() doesn't work as
-- expected when called after the trigger actions have completed, but
-- the last id is needed in Ruby.
CREATE TABLE last_id (
  id INTEGER
);
INSERT INTO last_id VALUES (NULL);

-- Document polymorphic class base table, shared data for all documents.
CREATE TABLE document (
  doc_id INTEGER PRIMARY KEY NOT NULL,
  type CHAR(1),
  title VARCHAR(255) DEFAULT NULL,
  content_type VARCHAR(255),
  memo TEXT,
  created_date TIMESTAMP,
  modified_date TIMESTAMP 
);

CREATE INDEX document_idx
  ON document(title);

-- Text documents content
CREATE TABLE document_text (
  doc_id INTEGER PRIMARY KEY NOT NULL REFERENCES document(doc_id),
  text TEXT
);

-- All media documents shared table
CREATE TABLE document_filebased (
  doc_id INTEGER PRIMARY KEY NOT NULL REFERENCES document(doc_id),
  file TEXT DEFAULT NULL,
  content BLOB,
  fingerprint CHAR(32)
);

-- Image documents
CREATE TABLE document_image (
  doc_id INTEGER PRIMARY KEY NOT NULL REFERENCES document(doc_id),
  width INTEGER,
  height INTEGER
);

-- Audio & Video documents - timebased
CREATE TABLE document_media (
  doc_id INTEGER PRIMARY KEY NOT NULL REFERENCES document(doc_id),
  length INTEGER
);

-- Document attributes
CREATE TABLE document_attr (
  attr_id INTEGER PRIMARY KEY NOT NULL,
  attr_name VARCHAR(255),
  type CHAR(1) 
);

-- The values of document attributes
CREATE TABLE document_attr_value (
  attr_id INTEGER NOT NULL REFERENCES document_attr(attr_id),
  doc_id INTEGER,
  attr_value TEXT,
  PRIMARY KEY(doc_id, attr_id) ON CONFLICT REPLACE 
);

-- Categories; straightforward
CREATE TABLE category (
  category_id INTEGER PRIMARY KEY NOT NULL,
  name VARCHAR(255) DEFAULT NULL,
  memo TEXT,
  parent INTEGER,
  colour CHAR(6),
  created_date TIMESTAMP,
  modified_date TIMESTAMP 
);

-- Uniquely identified segment of a document, polymorphic base
-- table. Virtual tables created below for concrete types.
CREATE TABLE extent (
  extent_id INTEGER PRIMARY KEY NOT NULL,
  type CHAR(1),
  doc_id INTEGER REFERENCES document(doc_id) 
);

-- Extents (passages) within TextDocuments
CREATE TABLE extent_text (
  extent_id INTEGER REFERENCES extent(extent_id),
  offset INTEGER NOT NULL,  
  length INTEGER NOT NULL
);

-- Extents (rectangles) within an image
CREATE TABLE extent_image (
  extent_id INTEGER REFERENCES extent(extent_id),
  left INTEGER NOT NULL,  
  top INTEGER NOT NULL,
  right INTEGER NOT NULL,
  bottom INTEGER NOT NULL
);

-- Extents (time periods) within Audio and Video documents
CREATE TABLE extent_media (
  extent_id INTEGER REFERENCES extent(extent_id),
  position INTEGER NOT NULL,  
  duration INTEGER NOT NULL
);

-- Coding of an extent by a particular category; QDA::Code class is
-- represented by virtual table below
CREATE TABLE mark (
  extent_id INTEGER REFERENCES extent(extent_id),
  category_id INTEGER REFERENCES category(category_id)
);

-- The text content of a free-standing annotation applied to an
-- extent. QDA::Annotation class is in a virtual table below.
CREATE TABLE note_text (
  annotation_id INTEGER PRIMARY KEY NOT NULL,
  extent_id INTEGER REFERENCES extent(extent_id),
  note TEXT 
);

-- Inter-point link
CREATE TABLE link (
  from_ex INTEGER REFERENCES extent(extent_id),
  to_ex INTEGER REFERENCES extent(extent_id)
);

-- Per-project settings, values may be arbitrary ruby types
CREATE TABLE project_setting ( 
  key VARCHAR(255) NOT NULL PRIMARY KEY ON CONFLICT REPLACE,
  value_opaque BLOB 
);

-- Stored multi-criteria query
CREATE TABLE query ( 
  query_id INTEGER PRIMARY KEY NOT NULL,
  name VARCHAR(255) NOT NULL,
  memo TEXT,
  root_opaque BLOB );

-- Matrix comparison of coding
CREATE TABLE code_review (
  code_review_id INTEGER PRIMARY KEY NOT NULL,
  name VARCHAR(255) NOT NULL,
  memo TEXT,
  rows_opaque BLOB,
  cols_opaque BLOB 
);

-- Free text searches
CREATE TABLE search (
  search_id INTEGER PRIMARY KEY NOT NULL,
  term VARCHAR(255) NOT NULL,
  context INTEGER,
  case_sensitive BOOLEAN,
  regexp BOOLEAN 
);

-- Used by the search table engine
CREATE TABLE document_suffix (
  docid INTEGER,
  suffixes BLOB 
);

-- Custom ruby code
CREATE TABLE script (
  script_id INTEGER PRIMARY KEY NOT NULL,
  code TEXT,
  linked_file TEXT DEFAULT NULL,
  last_run_ok BOOLEAN,
  last_output TEXT,
  last_error TEXT
);

-- FROM HERE, ALL TRIGGERS TO CREATE VIRTUAL TABLES AND MANAGE DELETION

-- TextDocument virtual table 

CREATE VIEW text_document
AS SELECT document.doc_id AS doc_id, title, content_type, memo, 
          created_date, modified_date, text
   FROM document, document_text
   WHERE document.doc_id = document_text.doc_id;

CREATE TRIGGER insert_document_text
INSTEAD OF INSERT ON text_document
BEGIN
  INSERT INTO document(type, title, content_type, memo, 
                       created_date, modified_date)
    VALUES ('T', new.title, new.content_type, 
            new.memo, new.created_date, new.modified_date);
  UPDATE last_id SET id = LAST_INSERT_ROWID();
  INSERT INTO document_text(doc_id, text)
    VALUES (LAST_INSERT_ROWID(), new.text);
END;

CREATE TRIGGER update_document_text
INSTEAD OF UPDATE ON text_document
BEGIN
 UPDATE document
   SET title = new.title, content_type = new.content_type, memo = new.memo, 
       created_date = new.created_date, modified_date = new.modified_date
   WHERE doc_id = old.doc_id;
 UPDATE document_text
   SET text = new.text
   WHERE doc_id = old.doc_id;
END;

CREATE TRIGGER delete_document_text
INSTEAD OF DELETE ON text_document
BEGIN
  DELETE FROM document WHERE doc_id = old.doc_id;
  DELETE FROM document_text WHERE doc_id = old.doc_id;
END;

-- ImageDocument virtual table

CREATE VIEW image_document
AS SELECT document.doc_id AS doc_id, title, content_type, memo, 
          created_date, modified_date, file, content, fingerprint,
          width, height
   FROM document, document_filebased, document_image
   WHERE document.doc_id = document_filebased.doc_id
     AND document.doc_id = document_image.doc_id;

CREATE TRIGGER insert_document_image
INSTEAD OF INSERT ON image_document
BEGIN
  INSERT INTO document(type, title, content_type, memo, 
                       created_date, modified_date)
    VALUES ('I', new.title, new.content_type, 
            new.memo, new.created_date, new.modified_date);
  UPDATE last_id SET id = LAST_INSERT_ROWID();
  INSERT INTO document_filebased(doc_id, file, content, fingerprint)
    VALUES (LAST_INSERT_ROWID(), new.file, new.content, new.fingerprint);
  INSERT INTO document_image(doc_id, width, height)
    VALUES (LAST_INSERT_ROWID(), new.width, new.height);
END;

CREATE TRIGGER update_document_image
INSTEAD OF UPDATE ON image_document
BEGIN
 UPDATE document
   SET title = new.title, content_type = new.content_type, memo = new.memo, 
       created_date = new.created_date, modified_date = new.modified_date
   WHERE doc_id = old.doc_id;
 UPDATE document_filebased
   SET file = new.file, content = new.content, fingerprint = new.fingerprint
   WHERE doc_id = old.doc_id;
 UPDATE document_image
   SET width = new.width, height = new.height
   WHERE doc_id = old.doc_id;
END;

CREATE TRIGGER delete_document_image
INSTEAD OF DELETE ON image_document
BEGIN
  DELETE FROM document WHERE doc_id = old.doc_id;
  DELETE FROM document_filebased WHERE doc_id = old.doc_id;
  DELETE FROM document_image WHERE doc_id = old.doc_id;
END;

-- AudioDocument virtual table

CREATE VIEW audio_document
AS SELECT document.doc_id AS doc_id, title, content_type, memo, 
          created_date, modified_date, file, content, fingerprint, length
   FROM document, document_filebased, document_media
   WHERE document.doc_id = document_filebased.doc_id
     AND document.doc_id = document_media.doc_id;

CREATE TRIGGER insert_document_audio
INSTEAD OF INSERT ON audio_document
BEGIN
  INSERT INTO document(type, title, content_type, memo, 
                       created_date, modified_date)
    VALUES ('A', new.title, new.content_type, 
            new.memo, new.created_date, new.modified_date);
  UPDATE last_id SET id = LAST_INSERT_ROWID();
  INSERT INTO document_filebased(doc_id, file, content, fingerprint)
    VALUES (LAST_INSERT_ROWID(), new.file, new.content, new.fingerprint);
  INSERT INTO document_media(doc_id, length)
    VALUES (LAST_INSERT_ROWID(), new.length);
END;

CREATE TRIGGER update_document_audio
INSTEAD OF UPDATE ON audio_document
BEGIN
 UPDATE document
   SET title = new.title, content_type = new.content_type, memo = new.memo, 
       created_date = new.created_date, modified_date = new.modified_date
   WHERE doc_id = old.doc_id;
 UPDATE document_filebased
   SET file = new.file, content = new.content, fingerprint = new.fingerprint
   WHERE doc_id = old.doc_id;
 UPDATE document_media
   SET length = new.length
   WHERE doc_id = old.doc_id;
END;

CREATE TRIGGER delete_document_audio
INSTEAD OF DELETE ON audio_document
BEGIN
  DELETE FROM document WHERE doc_id = old.doc_id;
  DELETE FROM document_filebased WHERE doc_id = old.doc_id;
  DELETE FROM document_media WHERE doc_id = old.doc_id;
END;

-- VideoDocument virtual table

CREATE VIEW video_document
AS SELECT document.doc_id AS doc_id, title, content_type, memo, 
          created_date, modified_date, file, content, fingerprint, length
   FROM document, document_filebased, document_media
   WHERE document.doc_id = document_filebased.doc_id
     AND document.doc_id = document_media.doc_id;

CREATE TRIGGER insert_document_video
INSTEAD OF INSERT ON video_document
BEGIN
  INSERT INTO document(type, title, content_type, memo, 
                       created_date, modified_date)
    VALUES ('V', new.title, new.content_type, 
            new.memo, new.created_date, new.modified_date);
  UPDATE last_id SET id = LAST_INSERT_ROWID();
  INSERT INTO document_filebased(doc_id, file, content, fingerprint)
    VALUES (LAST_INSERT_ROWID(), new.file, new.content, new.fingerprint);
  INSERT INTO document_media(doc_id, length)
    VALUES (LAST_INSERT_ROWID(), new.length);
END;

CREATE TRIGGER update_document_video
INSTEAD OF UPDATE ON video_document
BEGIN
 UPDATE document
   SET title = new.title, content_type = new.content_type, memo = new.memo, 
       created_date = new.created_date, modified_date = new.modified_date
   WHERE doc_id = old.doc_id;
 UPDATE document_filebased
   SET file = new.file, content = new.content, fingerprint = new.fingerprint
   WHERE doc_id = old.doc_id;
 UPDATE document_media
   SET length = new.length
   WHERE doc_id = old.doc_id;
END;

CREATE TRIGGER delete_document_video
INSTEAD OF DELETE ON video_document
BEGIN
  DELETE FROM document WHERE doc_id = old.doc_id;
  DELETE FROM document_filebased WHERE doc_id = old.doc_id;
  DELETE FROM document_media WHERE doc_id = old.doc_id;
END;

-- Aggregation of all Documents

CREATE VIEW all_document
AS SELECT * FROM document
LEFT OUTER JOIN document_text
  ON document.doc_id = document_text.doc_id 
LEFT OUTER JOIN document_filebased
  ON document.doc_id = document_filebased.doc_id 
LEFT OUTER JOIN document_image
  ON document.doc_id = document_image.doc_id 
LEFT OUTER JOIN document_media
  ON document.doc_id = document_media.doc_id;

-- Cascading deletion of documents by id, and of associated data

CREATE TRIGGER delete_document
AFTER DELETE ON document
BEGIN
  DELETE FROM document_attr_value WHERE doc_id = old.doc_id;
  DELETE FROM code WHERE doc_id = old.doc_id;
  DELETE FROM annotation WHERE doc_id = old.doc_id;

  DELETE FROM document_text WHERE doc_id = old.doc_id;
  DELETE FROM document_filebased WHERE doc_id = old.doc_id;
  DELETE FROM document_image WHERE doc_id = old.doc_id;
  DELETE FROM document_media WHERE doc_id = old.doc_id;
END;

-- General triggers
CREATE TRIGGER delete_category
  AFTER DELETE ON category
  BEGIN
    DELETE FROM code WHERE category_id = old.category_id;
END;

CREATE VIEW text_extent
AS SELECT extent.extent_id AS extent_id, doc_id, offset, length
   FROM extent, extent_text
   WHERE extent.extent_id = extent_text.extent_id;

CREATE TRIGGER insert_extent_text
INSTEAD OF INSERT ON text_extent
BEGIN
INSERT INTO extent(type, doc_id)
 VALUES ('T', new.doc_id);
UPDATE last_id SET id = LAST_INSERT_ROWID();
INSERT INTO extent_text(extent_id, offset, length)
 VALUES (LAST_INSERT_ROWID(), new.offset, new.length);
END;
 
CREATE TRIGGER update_extent_text
 INSTEAD OF UPDATE ON text_extent
BEGIN
 UPDATE extent
   SET doc_id = new.doc_id
   WHERE extent_id = old.extent_id;
 UPDATE extent_text
   SET offset = new.offset, length = new.length
   WHERE extent_id = old.extent_id;
END;

CREATE TRIGGER delete_extent_text
INSTEAD OF DELETE ON text_extent
BEGIN
DELETE FROM extent WHERE extent_id = old.extent_id;
DELETE FROM extent_text WHERE extent_id = old.extent_id;
END;

-- ImageExtent virtual table

CREATE VIEW image_extent
AS SELECT extent.extent_id AS extent_id, doc_id, 
          left, top, right, bottom
   FROM extent, extent_image
   WHERE extent.extent_id = extent_image.extent_id;

CREATE TRIGGER insert_extent_image
INSTEAD OF INSERT ON image_extent
BEGIN
INSERT INTO extent(type, doc_id)
 VALUES ('I', new.doc_id);
UPDATE last_id SET id = LAST_INSERT_ROWID();
INSERT INTO extent_image(extent_id, left, top, right, bottom)
 VALUES (LAST_INSERT_ROWID(), new.left, new.top, new.right, new.bottom);
END;
 
CREATE TRIGGER update_extent_image
 INSTEAD OF UPDATE ON image_extent
BEGIN
 UPDATE extent
   SET doc_id = new.doc_id
   WHERE extent_id = old.extent_id;
 UPDATE extent_image
   SET left = new.left, top = new.top, right = new.right, bottom = new.bottom
   WHERE extent_id = old.extent_id;
END;

CREATE TRIGGER delete_extent_image
INSTEAD OF DELETE ON image_extent
BEGIN
DELETE FROM extent WHERE extent_id = old.extent_id;
DELETE FROM extent_image WHERE extent_id = old.extent_id;
END;

CREATE VIEW media_extent
AS SELECT extent.extent_id AS extent_id, doc_id, position, duration
   FROM extent, extent_media
   WHERE extent.extent_id = extent_media.extent_id;

CREATE TRIGGER insert_extent_media
INSTEAD OF INSERT ON media_extent
BEGIN
INSERT INTO extent(type, doc_id)
 VALUES ('T', new.doc_id);
UPDATE last_id SET id = LAST_INSERT_ROWID();
INSERT INTO extent_media(extent_id, position, duration)
 VALUES (LAST_INSERT_ROWID(), new.position, new.duration);
END;
 
CREATE TRIGGER update_extent_media
 INSTEAD OF UPDATE ON media_extent
BEGIN
 UPDATE extent
   SET doc_id = new.doc_id
   WHERE extent_id = old.extent_id;
 UPDATE extent_media
   SET position = new.position, duration = new.duration
   WHERE extent_id = old.extent_id;
END;

CREATE TRIGGER delete_extent_media
INSTEAD OF DELETE ON media_extent
BEGIN
  DELETE FROM extent WHERE extent_id = old.extent_id;
  DELETE FROM extent_media WHERE media_id = old.extent_id;
END;

-- Aggregation of all extents
CREATE VIEW all_extent
AS SELECT * FROM extent
LEFT OUTER JOIN extent_text
  ON extent.extent_id = extent_text.extent_id 
LEFT OUTER JOIN extent_image
  ON extent.extent_id = extent_image.extent_id 
LEFT OUTER JOIN extent_media
  ON extent.extent_id = extent_media.extent_id;

-- Cascading deletion of extents by id
CREATE TRIGGER delete_extent
AFTER DELETE ON extent
BEGIN
  DELETE FROM extent_text WHERE extent_id = old.extent_id;
  DELETE FROM extent_image WHERE extent_id = old.extent_id;
  DELETE FROM extent_media WHERE extent_id = old.extent_id;
END;

-- QDA::Code class; application of a mark to an extent
CREATE VIEW code 
AS SELECT type, mark.extent_id AS extent_id, category_id, 
          doc_id, offset, length,
          left, top, right, bottom,
          position, duration
   FROM mark, all_extent
   WHERE all_extent.extent_id = mark.extent_id;

CREATE TRIGGER insert_code
INSTEAD OF INSERT ON code
BEGIN
  INSERT INTO mark(category_id, extent_id)
  VALUES (new.category_id, new.extent_id);
END;
 
CREATE TRIGGER delete_code
INSTEAD OF DELETE ON code
BEGIN
DELETE FROM mark WHERE extent_id = old.extent_id;
DELETE FROM extent WHERE extent_id = old.extent_id;
END;


-- QDA::Annotation class: application of a note to an extent
CREATE VIEW annotation 
AS SELECT type, note_text.extent_id AS extent_id, annotation_id, note,
          doc_id, offset, length,
          left, top, right, bottom,
          position, duration
   FROM note_text, all_extent
   WHERE all_extent.extent_id = note_text.extent_id;

CREATE TRIGGER insert_annotation
INSTEAD OF INSERT ON annotation
BEGIN
  INSERT INTO note_text(note, extent_id)
  VALUES (new.note, new.extent_id);
END;
 
CREATE TRIGGER update_annotation
INSTEAD OF UPDATE ON annotation
BEGIN
 UPDATE note_text
   SET note = new.note
   WHERE annotation_id = old.annotation_id;
END;

CREATE TRIGGER delete_annotation
INSTEAD OF DELETE ON annotation
BEGIN
DELETE FROM note_text WHERE annotation_id = old.annotation_id;
DELETE FROM extent WHERE extent_id = old.extent_id;
END;