/*
 * Nomencurator Database Schema for PostgreSQL 7.2
 *
 * $Id: makedbschema.sql,v 1.10 2002/08/26 09:22:50 t.okada Exp $
 * $Log: makedbschema.sql,v $
 * Revision 1.10  2002/08/26 09:22:50  t.okada
 * add publisher and place to publication
 *
 * Revision 1.9  2002/08/22 13:29:02  t.okada
 * It corrects for a locale colum addition.
 *
 * Revision 1.8  2002/08/20 13:20:42  t.okada
 * add lines column and notes column
 *
 * Revision 1.7  2002/08/15 05:17:35  ryo
 * change type of year column of Publication table into 'text'
 *
 * Revision 1.6  2002/07/29 04:58:45  ryo
 * change column name of NameUsage table from 'note' to 'notes'
 *
 * Revision 1.5  2002/07/24 12:15:05  t.okada
 * add note
 *
 * Revision 1.4  2002/07/08 07:39:27  ryo
 * remove unused table
 *
 * Revision 1.3  2002/07/03 13:35:06  ryo
 * modify specification
 *
 * Revision 1.2  2002/04/16 03:53:38  nozomi
 * migration to NameUsage from NameRecord
 *
 * Revision 1.1  2002/02/14 10:21:29  ryo
 * initial import into CVS.
 *
 */

/*
drop table Affiliation;
drop table Annotation;
drop table Appearance;
drop table Author;
drop table Institution;
drop table NameUsage;
drop table Publication;
drop table NRnode;
drop table ScientificName;
drop table DepositedData;
drop table ParsedData;
drop table ObjectTable;
drop table LT_Annotation_annotators;
drop table LT_Annotation_annotatants;
drop table LT_Appearance_annotations;
drop table LT_Appearance_nameusages;
drop table LT_Author_affiliations;
drop table LT_NameUsage_lowerTaxa;
drop table LT_NameUsage_authorList;
drop table LT_Publication_authorList;
drop table LT_Publication_contents;
drop table LT_Publication_appearances;
drop table LT_NRnode_linkFrom;
drop table LT_NRnode_linkTo;
drop table LT_NRnode_scientificNames;
drop table LT_NRnode_lower;
drop table LT_ScientificName_authority;
drop table LT_ScientificName_usages;
drop table LT_DepositedData_contents;
drop table LT_ParsedData_parsedObjects;
drop table LinkTable;
*/


/*
 * Object Table
 */
CREATE TABLE ObjectTable (
  id text -- object id
);

/*
 * Affiliation
 */
CREATE TABLE Affiliation (
  institute text,      -- (OID) pointer to Institution record
  dateFrom  timestamp, -- date (when a author) moved to the institution
  dateTo    timestamp  -- date (when a author) moved from the institution
) INHERITS (ObjectTable);

/*
 * Annotation
 */
CREATE TABLE Annotation (
  linkType   text, -- link type between two NameUsages, e.g. synonymy
  appearance text  -- (OID) pointer to Appearance object which holds "taxonomically encoded" text
  -- annotators -> LT_Anotation_annotators
                   -- (OID list) list of pointers to NameUsages which used
                   -- in the Publication referred via the Appearance
  -- annotatants -> LT_Anotation_annotatants
                   -- (OID list) list of pointers to NameUsages which are annotated
                   -- by the Publication referred via the Appearace
) INHERITS (ObjectTable);


/*
 * Appearance
 */
CREATE TABLE Appearance (
  page       text, -- precise page where the name appeared (with taxonomic action, if available)
  lines      text, -- precise lines where the name appeared (with taxonomic action, if available)
  appearance text,  -- precise expression of taxonomic actions such as naming and taxonomic decision
  publication text
  -- annotations -> LT_Appearance_annotations
                   -- (OID list) pointers to Annotation decoded from the appearance
  -- NameUsages -> LT_Appearance_nameusages
                   -- (OID list) pointers to NameUsage decoded from the appearance
) INHERITS (ObjectTable);


/*
 * Author
 */
CREATE TABLE Author (
  surname    text, -- surname of the author
  firstName  text, -- first name of the author
  middleName text, -- middle name of the author
  faudality text, -- faudality part of author's name
  title      text, -- title, such as Sir, if available
  epithet    text, -- epithet, e.g. Jr., Sen, if available
  birth      timestamp, -- date of birth, if available
  death      timestamp, -- date of death, if available
  -- publications -> LT_Publication_authorList
  -- (OID list) list of pointers to Publications by this Author
  -- affiliations -> LT_Author_affiliations
  -- (OID list) list of Affiliations of this Author
  firstPublication text,
  lastPublication text
) INHERITS (ObjectTable);


/*
 * Institution
 */
CREATE TABLE Institution (
  name       text, -- name of the institution
  address    text, -- address of the institution
  dateFrom   timestamp, -- date (a author) moved to the institution
  dateTo     timestamp, -- date (a author) moved from the institution
  previous   text, -- (OID) pointer to previous Institution record if name or address was changed
  next       text, -- (OID) pointer to next Institution record if name or address was changed
  divisionOf text  -- (OID) pointer to the Institution record to which this institution belongs to
  --  divisions -> LT_Institution_divisions
  -- (OID list) list of pointers to Insttiution which is division of this institution
) INHERITS (ObjectTable);


/*
 * NameUsage
 */
CREATE TABLE NameUsage (
  rank      text, -- the rank given in the Appearance of the Publication
  name      text, -- name used for the taxon concept in the Publication
  authority text, -- (OID) pointer to an Appearance in the authority Publication of the name
  appearance  text, -- (OID) pointer to an Appearance of the Publication in which the name was used
  higherTaxon    text, -- (OID) pointer to the higher rank NameUsage of this NameUsage, given in the publication
  -- lower -> LT_NameUsage_lowerTaxa
  -- (OID list) a list of pointers to lower rank NameUsage of this NameUsage, given in the publication
  type      text, -- (OID) pointer to the type NameUsage of this NameUsage, given in the publication
  typeOf    text,  -- (OID) pointer to the NameUsage of which type is this NameUsage, as given in the publication
  -- annotations -> LT_annotation_annotators
  -- (OID list) a list of pointers to Annotations stated with this NameUsage in relation to NameUsages of older usage of the name
  year    int2,
  notes   text,
  locale  text,
  incertaeSeds    text
  -- authorList -> LT_NameUsage_authorList
) INHERITS (ObjectTable);


/*
 * Publication
 */
CREATE TABLE Publication (
  authors      text, -- authors as text appeared in the publication
  affiliations text, -- affiliations as text appeared in the publication
  -- authorList -> LT_Publication_authorList
  -- (OID list) list of pointers to Author of this publication
  citationtitle     text,
  contentstitle    text, 
  ISXN      text, -- ISSN/ISBN code of the publication
  year      text, -- year of the publication
  volume    text, -- volume of the publication
  issue    text, -- number of the publication
  firstPage text, -- first page of the publication
  lastPage  text, -- last page of the publication
  publisher text, -- first page of the publication
  place     text, -- last page of the publication
  notes     text, -- notes of the publication
  recieved  timestamp, -- date when the manuscript was received
  reviced   timestamp, -- date when the reviced manuscript was received
  accepted  timestamp, -- date when the manuscript was accepted
  partOf    text  -- (OID) pointer to the Publication containing this publication, if available
                  -- (e.g. book contining this publication as its chapter)
  -- parts -> LT_Publication_contents
  -- (OID list) list of pointers to Publication contained by this publication, if available
  -- (e.g. chapters in this publication, if it is a book)
  -- appearances -> LT_Publication_appearances
  -- (OID list) list of pointers to Annotation abstracted from the publication
) INHERITS (ObjectTable);


/*
 * NRnode
 */
CREATE TABLE NRnode (
  NameUsage text -- (OID) pointer to the NameUsage proxied by this data
  -- from -> LT_NRnode_LinkFrom
  -- (OID) 
  -- to -> LT_NRnode_LinkTo
  -- (OID) 
  -- scientificNames -> LT_NRnode_scientificNames
  -- (OID) 
) INHERITS (ObjectTable);


/*
 * ScientificName
 */
CREATE TABLE ScientificName (
  authority text -- (OID)
) INHERITS (ObjectTable);

/*
 * DepositedData
 */
CREATE TABLE DepositedData (
  contents    text, -- (OID)
  depositor   text,
  depositedAt timestamp
) INHERITS (ObjectTable);

/*
 * ParsedData
 */
CREATE TABLE ParsedData (
  contents text -- (OID)
) INHERITS (ObjectTable);


/*
 * LinkTable
 */
CREATE TABLE LinkTable (
  linkFrom  text,
--  tableName text,
  linkTo    text,
  seq     int2 -- link order
);
CREATE TABLE LT_Annotation_annotators () INHERITS (LinkTable);
CREATE TABLE LT_Annotation_annotatants () INHERITS (LinkTable);
CREATE TABLE LT_Appearance_annotations () INHERITS (LinkTable);
CREATE TABLE LT_Appearance_NameUsages () INHERITS (LinkTable);
CREATE TABLE LT_Author_affiliations () INHERITS (LinkTable);
CREATE TABLE LT_NameUsage_lowerTaxa () INHERITS (LinkTable);
CREATE TABLE LT_NameUsage_authorList () INHERITS (LinkTable);
CREATE TABLE LT_Publication_authorList () INHERITS (LinkTable);
CREATE TABLE LT_Publication_contents () INHERITS (LinkTable);
CREATE TABLE LT_Publication_appearances () INHERITS (LinkTable);
CREATE TABLE LT_NRnode_linkFrom () INHERITS (LinkTable);
CREATE TABLE LT_NRnode_linkTo () INHERITS (LinkTable);
CREATE TABLE LT_NRnode_scientificNames () INHERITS (LinkTable);
CREATE TABLE LT_NRnode_lower () INHERITS (LinkTable);
CREATE TABLE LT_ScientificName_authority () INHERITS (LinkTable);
CREATE TABLE LT_ScientificName_usages () INHERITS (LinkTable);
CREATE TABLE LT_DepositedData_contents () INHERITS (LinkTable);
CREATE TABLE LT_ParsedData_parsedObjects () INHERITS (LinkTable);


/*
 * grant to nobody user.
 */
grant all on Affiliation to nobody;
grant all on Annotation to nobody;
grant all on Appearance to nobody;
grant all on Author to nobody;
grant all on Institution to nobody;
grant all on NameUsage to nobody;
grant all on Publication to nobody;
grant all on NRnode to nobody;
grant all on ScientificName to nobody;
grant all on DepositedData to nobody;
grant all on ParsedData to nobody;
grant all on ObjectTable to nobody;
grant all on LT_Annotation_annotators to nobody;
grant all on LT_Annotation_annotatants to nobody;
grant all on LT_Appearance_annotations to nobody;
grant all on LT_Appearance_NameUsages to nobody;
grant all on LT_Author_affiliations to nobody;
grant all on LT_NameUsage_lowerTaxa to nobody;
grant all on LT_NameUsage_authorList to nobody;
grant all on LT_Publication_authorList to nobody;
grant all on LT_Publication_contents to nobody;
grant all on LT_Publication_appearances to nobody;
grant all on LT_NRnode_linkFrom to nobody;
grant all on LT_NRnode_linkTo to nobody;
grant all on LT_NRnode_scientificNames to nobody;
grant all on LT_NRnode_lower to nobody;
grant all on LT_ScientificName_authority to nobody;
grant all on LT_ScientificName_usages to nobody;
grant all on LT_DepositedData_contents to nobody;
grant all on LT_ParsedData_parsedObjects to nobody;
