# Moorea Biocode Project
# Feb 2006 - 2009
# Collecting Event table


## caution! 
## any new fields must also be added to biocode_collecting_event_deleted as well as myschema.p
## see also ./update_specimens_from_collevent.sql

DROP TABLE biocode_collecting_event;

CREATE TABLE biocode_collecting_event (

EventID   		   serial,  # key for table biocode: coll_eventID
seq_num                    varchar(40),     # added 8/12/2008  JG, per request by JD for FMA
OtherEventID		   varchar(128),   # see OtherEventInst for whose this is
ProjectName		   varchar(128),   # "Fiji"
HoldingInstitution         varchar(255), 
DateFirstEntered           datetime,  
EnteredBy                  varchar(128), 
DateLastModified           datetime,
ModifiedBy                 varchar(128), 

Collector                  varchar(255),
Collector2                 varchar(128),
Collector3                 varchar(128),
Collector4                 varchar(128),
Collector5                 varchar(128),
Collector6                 varchar(128),
Collector7                 varchar(128),
Collector8                 varchar(128),
Collector_List             varchar(255),    ## Col1, Col2, Col3, Col4, Col5, Col6, Col7, Col8
YearCollected              mediumint unsigned,   #  Darwin Core= "Year"
MonthCollected             tinyint unsigned,     # Darwin Core= "Month"
DayCollected               tinyint unsigned,     # Darwin Core= "Day"
TimeofDay		   varchar(128),
YearCollected2             mediumint unsigned,
MonthCollected2            tinyint unsigned,
DayCollected2              tinyint unsigned,
TimeofDay2		   varchar(128),

ContinentOcean		   varchar(128),
IslandGroup		   varchar(128),
Island			   varchar(128),
Country			   varchar(128),
StateProvince		   varchar(128),
County			   varchar(128),
Locality		   varchar(255),
Loc_Num	                   varchar(64),   ## for future use

DecimalLongitude	   decimal (10,5),
DecimalLatitude		   decimal (10,5),
DecimalLongitude2	   decimal (10,5),
DecimalLatitude2	   decimal (10,5),
HorizontalDatum		   varchar(50),
MaxErrorInMeters	   decimal (10,4), 

MinElevationMeters	   decimal (10,4),
MaxElevationMeters	   decimal (10,4),
MinDepthMeters		   decimal (10,4),
MaxDepthMeters		   decimal (10,4),
DepthOfBottomMeters	   decimal (10,4),
DepthErrorMeters           decimal (10,4),

IndividualCount		   varchar(50),  
Habitat                    varchar(255),
MicroHabitat               varchar(255),
Collection_Method	   varchar(64),   # Malaise Trap
Landowner		   varchar(255),
Permit_Info		   text,
Remarks			   varchar(255),
OtherEventInst		   varchar(64),    # if OtherEventID, whose it is "Mandala"
OtherEventID2		   varchar(128),
OtherMiscID		   varchar(128),

VerbatimLongitude          varchar(255),
VerbatimLatitude           varchar(255),
VerbatimLongitude2         varchar(255),
VerbatimLatitude2          varchar(255),
Disposition		   varchar(255),   # where is this sample now?
TaxonNotes		   varchar(255),   # added 11/15/2005
Coll_EventID_collector     varchar(50),    # collector's field notes no. 02/13/06
pic                        varchar(255),
batch_id                   char(24)       # for batch upload  2008-05-16_12:22:28  GO

);