text: --
  -- Licensed to the Apache Software Foundation (ASF) under one
  -- or more contributor license agreements.  See the NOTICE file
  -- distributed with this work for additional information
  -- regarding copyright ownership.  The ASF licenses this file
  -- to you under the Apache License, Version 2.0 (the
  -- "License"); you may not use this file except in compliance
  -- with the License.  You may obtain a copy of the License at
  --
  --   http://www.apache.org/licenses/LICENSE-2.0
  --
  -- Unless required by applicable law or agreed to in writing,
  -- software distributed under the License is distributed on an
  -- "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
  -- KIND, either express or implied.  See the License for the
  -- specific language governing permissions and limitations
  -- under the License.
  --
  
  -- We assume the metadata about documents (patient id, date) come from
  -- some table in your database.  modify this view to join with that table
  -- and get this info
  create view v_document as
  select analysis_batch, document_id, doc_text, instance_id, null patient_id, null doc_date, null doc_title, null document_type_name
  from document;
  
  
  create view v_annotation
  AS
  SELECT anno.*, ur.uima_type_name, substr(doc.doc_text, anno.span_begin+1, anno.span_end-anno.span_begin)  anno_text, doc.analysis_batch
  FROM anno_base AS anno 
  INNER JOIN document AS doc ON doc.document_id = anno.document_id
  INNER JOIN ref_uima_type AS ur on ur.uima_type_id = anno.uima_type_id
  ;
  
  
  -- this view gives the document info, cui info, and sentence info in which a cui is found.
  create view v_document_cui_sent
  as
  SELECT
    da.anno_base_id,
    d.analysis_batch,
    da.document_id,
    ne.polarity,
    o.code,
    o.cui,
    substr(d.doc_text, da.span_begin+1, da.span_end-da.span_begin) cui_text,
    substr(d.doc_text, s.span_begin+1, s.span_end-s.span_begin) sentence_text,
    o.disambiguated,
    null patient_id,
    null doc_date,
    null doc_title,
    null document_type_name
  FROM anno_base da
  INNER JOIN anno_named_entity  ne ON da.anno_base_id = ne.anno_base_id
  INNER JOIN anno_ontology_concept  o ON o.anno_base_id = ne.anno_base_id
  inner join anno_contain ac on da.anno_base_id = ac.child_anno_base_id
  INNER join anno_base s
    on ac.parent_anno_base_id = s.anno_base_id
    and s.uima_type_id in (select uima_type_id from ref_uima_type where uima_type_name = 'org.apache.ctakes.typesystem.type.textspan.Sentence')
  INNER JOIN document d on da.document_id = d.document_id
  ;
  
  CREATE VIEW v_document_ontoanno
  AS
  SELECT d.document_id, da.span_begin, da.span_end, ne.polarity, o.code, o.cui, d.analysis_batch, o.disambiguated
  FROM document AS d INNER JOIN
  anno_base AS da ON d.document_id = da.document_id INNER JOIN
  anno_named_entity AS ne ON da.anno_base_id = ne.anno_base_id INNER JOIN
  anno_ontology_concept AS o ON o.anno_base_id = ne.anno_base_id
  ;
  