CREATE VIEW seqfeature_key_v
 AS SELECT f.*, t.name AS key_name 
    FROM seqfeature f, term t 
    WHERE f.type_term_id = t.term_id;

CREATE    VIEW gff
 AS SELECT e.accession       AS fref, 
           fl.start_pos      AS fstart, 
           fl.end_pos        AS fend,
           k.name            AS type, 
           NULL       AS fscore,
           fl.strand         AS fstrand, 
           NULL    AS fphase,
           f.seqfeature_id   AS gid
    FROM seqfeature f, 
         term k, 
         location fl,
         bioentry e
    WHERE f.type_term_id = k.term_id  AND
          fl.seqfeature_id = f.seqfeature_id        AND
          f.bioentry_id    = e.bioentry_id;

--- basic fasta view; we may have others where we
--- cram more into header
CREATE VIEW fasta
  AS SELECT '>'                        ||
            e.name                     ||
            ' '                        ||
            e.description              AS header,
            s.seq                      AS residues
  FROM bioentry e,
       biosequence s
  WHERE e.bioentry_id = s.bioentry_id;

--- fasta with length of seq appended
CREATE VIEW fasta_withlen
  AS SELECT fasta.header               || 
            ' length:'                 ||
            length(residues)           AS header,
            residues
  FROM fasta;

---
CREATE VIEW embl_FT
 AS SELECT fl.start_pos      ||
           '..'              ||
           fl.end_pos        AS floc,
           k.name            AS ftype, 
           qt.name           AS qterm,
           q.value AS qval
    FROM seqfeature f, 
         term k, 
         term qt, 
         location fl,
         bioentry e,
         seqfeature_qualifier_value q
    WHERE k.term_id = f.type_term_id  AND
          qt.term_id = q.term_id AND
          fl.seqfeature_id = f.seqfeature_id        AND
          f.bioentry_id    = e.bioentry_id;


--- this should probably be autogenerated by
--- an ontology of mappings
CREATE VIEW embl
  AS SELECT e.name                     AS ID,
            e.accession                AS AC,
            e.accession || 
            ' '         || 
            e.version                  AS SV
  FROM bioentry e;
  
  
  
  --~ 
  --~ CREATE    VIEW gffseq
 --~ AS SELECT e.accession       AS fref, 
           --~ fl.start_pos      AS fstart, 
           --~ fl.end_pos        AS fend,
           --~ k.name            AS type, 
           --~ NULL     AS fscore,
           --~ fl.strand         AS fstrand, 
           --~ NULL    AS fphase,
           --~ f.seqfeature_id   AS gid,
           --~ get_subseq(s.seq,
                      --~ fl.start_pos,
                      --~ fl.end_pos,
                      --~ fl.strand)
                             --~ AS subseq
    --~ FROM seqfeature f, 
         --~ term k, 
         --~ location fl,
         --~ bioentry e,
         --~ biosequence s
    --~ WHERE f.type_term_id = k.term_id AND
          --~ fl.seqfeature_id    = f.seqfeature_id        AND
          --~ f.bioentry_id       = e.bioentry_id          AND
          --~ f.bioentry_id       = s.bioentry_id;

--- these are autogenerated:

CREATE VIEW f_source AS
  SELECT * from seqfeature_key_v WHERE key_name = 'source';
CREATE VIEW f_misc_feature AS
  SELECT * from seqfeature_key_v WHERE key_name = 'misc_feature';
CREATE VIEW f_sig_peptide AS
  SELECT * from seqfeature_key_v WHERE key_name = 'sig_peptide';
CREATE VIEW f_CDS AS
  SELECT * from seqfeature_key_v WHERE key_name = 'CDS';
CREATE VIEW f_gene AS
  SELECT * from seqfeature_key_v WHERE key_name = 'gene';
CREATE VIEW f_mat_peptide AS
  SELECT * from seqfeature_key_v WHERE key_name = 'mat_peptide';
CREATE VIEW f_variation AS
  SELECT * from seqfeature_key_v WHERE key_name = 'variation';
CREATE VIEW f_exon AS
  SELECT * from seqfeature_key_v WHERE key_name = 'exon';
CREATE VIEW f_intron AS
  SELECT * from seqfeature_key_v WHERE key_name = 'intron';


