-- Dump File -- -- Database is ported from MS Access ---------------------------------------------------------- -- Program Version 3.0.148 CREATE DATABASE "cvs-archive-2012-12-04"; -- NOTICE: At this place you need to connect to the new database and run the rest of the statements. -- -- Table structure for table 'address' -- DROP TABLE IF EXISTS "address"; CREATE TABLE "address" ( "ADDRESS_ID" SERIAL NOT NULL, "party_ID" INTEGER NOT NULL, "organization_ID" INTEGER, "orgPosition" VARCHAR(50), "email" VARCHAR(100), "deliveryPoint" VARCHAR(200), "city" VARCHAR(50), "administrativeArea" VARCHAR(50), "postalCode" VARCHAR(10), "country" VARCHAR(50), "currentFlag" /*BOOLEAN*/INTEGER DEFAULT E'0', "addressStartDate" TIMESTAMP, "dba_src_ID" INTEGER, PRIMARY KEY ("ADDRESS_ID") ); -- -- Table structure for table 'archiveLog' -- DROP TABLE IF EXISTS "archiveLog"; CREATE TABLE "archiveLog" ( "archiveLog_ID" SERIAL NOT NULL, "archiveLogUser" VARCHAR(50), "archiveLogComment" TEXT, "commentDate" TIMESTAMP DEFAULT E'=Now()', PRIMARY KEY ("archiveLog_ID") ); -- -- Table structure for table 'aux_Role' -- DROP TABLE IF EXISTS "aux_Role"; CREATE TABLE "aux_Role" ( "ROLE_ID" SERIAL NOT NULL, "roleCode" VARCHAR(30) NOT NULL, "roleDescription" VARCHAR(200), "accessionCode" VARCHAR(100), "roleProject" INTEGER, "roleObservation" INTEGER, "roleTaxonInt" INTEGER, "roleClassInt" INTEGER, "dba_src_ID" INTEGER, PRIMARY KEY ("ROLE_ID") ); -- -- Table structure for table 'classContributor' -- DROP TABLE IF EXISTS "classContributor"; CREATE TABLE "classContributor" ( "CLASSCONTRIBUTOR_ID" SERIAL NOT NULL, "COMMCLASS_ID" INTEGER NOT NULL, "PARTY_ID" INTEGER NOT NULL, "ROLE_ID" INTEGER, "dba_src_ID" INTEGER, PRIMARY KEY ("CLASSCONTRIBUTOR_ID") ); -- -- Table structure for table 'commClass' -- DROP TABLE IF EXISTS "commClass"; CREATE TABLE "commClass" ( "COMMCLASS_ID" SERIAL NOT NULL, "OBSERVATION_ID" INTEGER NOT NULL, "classStartDate" TIMESTAMP, "classStopDate" TIMESTAMP, "inspection" /*BOOLEAN*/INTEGER DEFAULT E'0', "tableAnalysis" /*BOOLEAN*/INTEGER DEFAULT E'0', "multivariateAnalysis" /*BOOLEAN*/INTEGER DEFAULT E'0', "expertSystem" TEXT, "classPublication_ID" INTEGER, "classNotes" TEXT, "commName" VARCHAR(200), "commCode" VARCHAR(200), "commFramework" VARCHAR(200), "commLevel" VARCHAR(200), "accessionCode" VARCHAR(100), "dba_src_ID" INTEGER, "dba_dataSource" VARCHAR(255), "classStopReason" VARCHAR(255), PRIMARY KEY ("COMMCLASS_ID") ); -- -- Table structure for table 'commConcept' -- DROP TABLE IF EXISTS "commConcept"; CREATE TABLE "commConcept" ( "COMMCONCEPT_ID" SERIAL NOT NULL, "COMMNAME_ID" INTEGER NOT NULL, "reference_ID" INTEGER, "commDescription" TEXT, "accessionCode" VARCHAR(100), "dba_src_ID" INTEGER, "commName" VARCHAR(255), "referenceText" VARCHAR(255), "scientific" VARCHAR(255), "translated" VARCHAR(255), "code" VARCHAR(255), "originalAuthorCode" VARCHAR(255), "common" VARCHAR(255), "commLevel" VARCHAR(80), "alliance" VARCHAR(255), "includeInPicklistNow" /*BOOLEAN*/INTEGER DEFAULT E'-1', "PhysGroup" VARCHAR(255), "FormGroup" VARCHAR(255), "EcolGroup" VARCHAR(255), "4thApproxName" VARCHAR(255), "NatureServeUID" VARCHAR(255), "classificationSystem" VARCHAR(255), PRIMARY KEY ("COMMCONCEPT_ID"), UNIQUE ("classificationSystem", "code") ); -- -- Table structure for table 'commCorrelation' -- DROP TABLE IF EXISTS "commCorrelation"; CREATE TABLE "commCorrelation" ( "COMMCORRELATION_ID" SERIAL NOT NULL, "COMMSTATUS_ID" INTEGER NOT NULL, "COMMCONCEPT_ID" INTEGER NOT NULL, "commConvergence" VARCHAR(20) NOT NULL, "correlationStart" TIMESTAMP NOT NULL, "correlationStop" TIMESTAMP, "dba_src_ID" INTEGER, PRIMARY KEY ("COMMCORRELATION_ID") ); -- -- Table structure for table 'commInterpretation' -- DROP TABLE IF EXISTS "commInterpretation"; CREATE TABLE "commInterpretation" ( "COMMINTERPRETATION_ID" SERIAL NOT NULL, "COMMCLASS_ID" INTEGER NOT NULL, "COMMCONCEPT_ID" INTEGER, "commcode" VARCHAR(34), "commname" VARCHAR(200), "classFit" VARCHAR(50), "classConfidence" VARCHAR(15), "commAuthority_ID" INTEGER, "notes" TEXT, "type" /*BOOLEAN*/INTEGER DEFAULT E'0', "nomenclaturalType" /*BOOLEAN*/INTEGER DEFAULT E'0', "dba_src_ID" INTEGER, "framework_id" INTEGER, "primaryDetermination" /*BOOLEAN*/INTEGER DEFAULT E'0', "primaryDeterminationStatus" INTEGER DEFAULT 0, "primaryComments" TEXT, "primaryStartDate" TIMESTAMP, "primarySTOPDate" TIMESTAMP, "dba_interpretationStatus" VARCHAR(255), "previousFit" VARCHAR(50), "orderDeterminationForEvent" INTEGER, "duplicateDetermination" /*BOOLEAN*/INTEGER DEFAULT E'0', PRIMARY KEY ("COMMINTERPRETATION_ID") ); -- -- Table structure for table 'commLineage' -- DROP TABLE IF EXISTS "commLineage"; CREATE TABLE "commLineage" ( "COMMLINEAGE_ID" SERIAL NOT NULL, "parentCommStatus_ID" INTEGER NOT NULL, "childCommStatus_ID" INTEGER NOT NULL, "dba_src_ID" INTEGER, PRIMARY KEY ("COMMLINEAGE_ID") ); -- -- Table structure for table 'commName' -- DROP TABLE IF EXISTS "commName"; CREATE TABLE "commName" ( "COMMNAME_ID" SERIAL NOT NULL, "commName" TEXT NOT NULL, "reference_ID" INTEGER, "dateEntered" TIMESTAMP, "dba_src_ID" INTEGER, PRIMARY KEY ("COMMNAME_ID") ); -- -- Table structure for table 'commStatus' -- DROP TABLE IF EXISTS "commStatus"; CREATE TABLE "commStatus" ( "COMMSTATUS_ID" SERIAL NOT NULL, "COMMCONCEPT_ID" INTEGER NOT NULL, "reference_ID" INTEGER, "commConceptStatus" VARCHAR(20) NOT NULL, "commParent_ID" INTEGER, "commLevel" VARCHAR(80), "startDate" TIMESTAMP NOT NULL, "stopDate" TIMESTAMP, "commPartyComments" TEXT, "PARTY_ID" INTEGER NOT NULL, "dba_src_ID" INTEGER, "accessionCode" VARCHAR(100), PRIMARY KEY ("COMMSTATUS_ID") ); -- -- Table structure for table 'commUsage' -- DROP TABLE IF EXISTS "commUsage"; CREATE TABLE "commUsage" ( "COMMUSAGE_ID" SERIAL NOT NULL, "COMMNAME_ID" INTEGER NOT NULL, "commName" TEXT, "COMMCONCEPT_ID" INTEGER, "usageStart" TIMESTAMP, "usageStop" TIMESTAMP, "commNameStatus" VARCHAR(20), "classSystem" VARCHAR(50), "PARTY_ID" INTEGER, "COMMSTATUS_ID" INTEGER, "dba_src_ID" INTEGER, PRIMARY KEY ("COMMUSAGE_ID") ); -- -- Table structure for table 'coverIndex' -- DROP TABLE IF EXISTS "coverIndex"; CREATE TABLE "coverIndex" ( "COVERINDEX_ID" SERIAL NOT NULL, "COVERMETHOD_ID" INTEGER NOT NULL, "coverCode" VARCHAR(10) NOT NULL, "upperLimit" DOUBLE PRECISION, "lowerLimit" DOUBLE PRECISION, "coverPercent" DOUBLE PRECISION NOT NULL, "indexDescription" TEXT, "dba_src_ID" INTEGER, PRIMARY KEY ("COVERINDEX_ID") ); -- -- Table structure for table 'coverMethod' -- DROP TABLE IF EXISTS "coverMethod"; CREATE TABLE "coverMethod" ( "COVERMETHOD_ID" SERIAL NOT NULL, "reference_ID" INTEGER, "coverType" VARCHAR(30) NOT NULL, "coverEstimationMethod" VARCHAR(80), "accessionCode" VARCHAR(100), "dba_src_ID" INTEGER, PRIMARY KEY ("COVERMETHOD_ID") ); -- -- Table structure for table 'cust_cvs_SoilDepth' -- DROP TABLE IF EXISTS "cust_cvs_SoilDepth"; CREATE TABLE "cust_cvs_SoilDepth" ( "DepthID" SERIAL NOT NULL, "observation_ID" INTEGER, "module" VARCHAR(4), "corner" VARCHAR(4), "depth" DOUBLE PRECISION, PRIMARY KEY ("DepthID") ); -- -- Table structure for table 'cvs_taxonAlt_translate' -- DROP TABLE IF EXISTS "cvs_taxonAlt_translate"; CREATE TABLE "cvs_taxonAlt_translate" ( "CVS_taxonAlt_ID" SERIAL NOT NULL, "SppID" INTEGER, "sciNAmeComplex" VARCHAR(200), "sciNAme" VARCHAR(200), "plantAccessionCode" VARCHAR(80), PRIMARY KEY ("CVS_taxonAlt_ID") ); -- -- Table structure for table 'definedValue' -- DROP TABLE IF EXISTS "definedValue"; CREATE TABLE "definedValue" ( "DEFINEDVALUE_ID" SERIAL NOT NULL, "USERDEFINED_ID" INTEGER NOT NULL, "tableRecord_ID" INTEGER NOT NULL, "definedValue" TEXT NOT NULL, "dba_src_ID" INTEGER, PRIMARY KEY ("DEFINEDVALUE_ID") ); -- -- Table structure for table 'disturbanceObs' -- DROP TABLE IF EXISTS "disturbanceObs"; CREATE TABLE "disturbanceObs" ( "disturbanceObs_ID" SERIAL NOT NULL, "OBSERVATION_ID" INTEGER NOT NULL, "disturbanceType" VARCHAR(30) NOT NULL, "disturbanceIntensity" VARCHAR(30), "disturbanceAge" DOUBLE PRECISION, "disturbanceExtent" DOUBLE PRECISION, "disturbanceComment" TEXT, "dba_src_ID" INTEGER, PRIMARY KEY ("disturbanceObs_ID") ); -- -- Table structure for table 'embargo' -- DROP TABLE IF EXISTS "embargo"; CREATE TABLE "embargo" ( "embargo_ID" SERIAL NOT NULL, "plot_ID" INTEGER NOT NULL, "embargoReason" TEXT NOT NULL, "embargoStart" TIMESTAMP NOT NULL, "embargoStop" TIMESTAMP NOT NULL, "defaultStatus" INTEGER NOT NULL, "dba_src_ID" INTEGER, PRIMARY KEY ("embargo_ID") ); -- -- Table structure for table 'entry_cover' -- DROP TABLE IF EXISTS "entry_cover"; CREATE TABLE "entry_cover" ( "HerbLineID" SERIAL NOT NULL, "AlternateOrder" DOUBLE PRECISION, "plotID" INTEGER NOT NULL, "NC_Code" INTEGER, "altReference_ID" INTEGER, "unknownSp" VARCHAR(50), "CBS_1" VARCHAR(1), "CBS_2" VARCHAR(1), "CBS_3" VARCHAR(1), "CBS_4" VARCHAR(1), "CBS_5" VARCHAR(1), "CBS_6" VARCHAR(1), "CBS_7" VARCHAR(1), "CBS_8" VARCHAR(1), "CBS_9" VARCHAR(1), "CBS_10" VARCHAR(1), "CBS_11" VARCHAR(1), "CBS_12" VARCHAR(1), "col1" VARCHAR(1), "col2" VARCHAR(1), "col3" VARCHAR(1), "col4" VARCHAR(1), "col5" VARCHAR(1), "col6" VARCHAR(1), "col7" VARCHAR(1), "col8" VARCHAR(1), "col9" VARCHAR(1), "col10" VARCHAR(1), "col11" VARCHAR(1), "col12" VARCHAR(1), "col13" VARCHAR(1), "col14" VARCHAR(1), "col15" VARCHAR(1), "col16" VARCHAR(1), "col17" VARCHAR(1), "col18" VARCHAR(1), "col19" VARCHAR(1), "col20" VARCHAR(1), "avcov" VARCHAR(1), "notes" VARCHAR(255), "errors" TEXT, "NC_Code_History" TEXT, "duplicate" INTEGER DEFAULT 0, "TreeFillin" VARCHAR(50), "userDefined1" VARCHAR(255), "userDefined2" VARCHAR(255), "userDefined3" VARCHAR(255), "userDefined4" VARCHAR(255), "userDefined5" VARCHAR(255), "userDefined6" VARCHAR(255), "userDefined7" VARCHAR(255), "userDefined8" VARCHAR(255), "userDefined9" VARCHAR(255), "userDefined10" VARCHAR(255), "userDefined11" VARCHAR(255), "userDefined12" VARCHAR(255), "userDefined13" VARCHAR(255), "userDefined14" VARCHAR(255), "userDefined15" VARCHAR(255), "userDefined16" VARCHAR(255), "log_createDate" TIMESTAMP DEFAULT E'Now()', "log_createUser" VARCHAR(50), "log_modifiedDate" TIMESTAMP DEFAULT E'Now()', "log_modifiedUser" VARCHAR(50), "collectionFlag" VARCHAR(1), PRIMARY KEY ("HerbLineID") ); -- -- Table structure for table 'entry_plots_cover' -- DROP TABLE IF EXISTS "entry_plots_cover"; CREATE TABLE "entry_plots_cover" ( "plotID" SERIAL NOT NULL, "project" INTEGER, "team" VARCHAR(6), "plot" VARCHAR(12), "cvs_level" INTEGER, "date" TIMESTAMP, "depth" INTEGER, "Ares_H" DOUBLE PRECISION, "notesPlot" VARCHAR(255), "str1" VARCHAR(10) DEFAULT E'H', "str2" VARCHAR(10) DEFAULT E'S', "str3" VARCHAR(10) DEFAULT E'U', "str4" VARCHAR(10) DEFAULT E'C', "str5" VARCHAR(10) DEFAULT E'E', "str6" VARCHAR(10), "str7" VARCHAR(10), "str8" VARCHAR(10), "str9" VARCHAR(10), "str10" VARCHAR(10), "str11" VARCHAR(10), "str12" VARCHAR(10), "head1" VARCHAR(1), "head2" VARCHAR(1), "head3" VARCHAR(1), "head4" VARCHAR(1), "head5" VARCHAR(1), "head6" VARCHAR(1), "head7" VARCHAR(1), "head8" VARCHAR(1), "head9" VARCHAR(1), "head10" VARCHAR(1), "head11" VARCHAR(1), "head12" VARCHAR(1), "head13" VARCHAR(1), "head14" VARCHAR(1), "head15" VARCHAR(1), "head16" VARCHAR(1), "head17" VARCHAR(1), "head18" VARCHAR(1), "head19" VARCHAR(1), "head20" VARCHAR(1), "headerrors" TEXT, "plotSrc" VARCHAR(50), "log_pcreateDate" TIMESTAMP DEFAULT E'Now()', "log_pcreateUser" VARCHAR(50), "log_pmodifiedDate" TIMESTAMP DEFAULT E'Now()', "log_pmodifiedUser" VARCHAR(50), "vmd_year" INTEGER DEFAULT 0, "cp_entry_plotDone" /*BOOLEAN*/INTEGER DEFAULT E'0', "cp_yearNum" INTEGER, "cp_authorObsCode" VARCHAR(255), PRIMARY KEY ("plotID") ); -- -- Table structure for table 'entry_plots_stems' -- DROP TABLE IF EXISTS "entry_plots_stems"; CREATE TABLE "entry_plots_stems" ( "plotID" SERIAL NOT NULL, "project" INTEGER, "team" VARCHAR(6), "plot" VARCHAR(12), "cvs_level" INTEGER, "date" TIMESTAMP, "Ares" DOUBLE PRECISION, "PlotSubSeedling" INTEGER, "PlotSubSapling" INTEGER, "PlotSubTree" INTEGER, "SubSamplingNarrative" TEXT, "notesPlot" VARCHAR(255), "notesPlot_indiv" VARCHAR(255), "headerrors" TEXT, "minimumDBH" DOUBLE PRECISION, "minimumdgh" DOUBLE PRECISION, "minimumHt" DOUBLE PRECISION, "AllStemsPlanted" /*BOOLEAN*/INTEGER DEFAULT E'0', "log_pcreateDate" TIMESTAMP DEFAULT E'Now()', "log_pcreateUser" VARCHAR(50), "log_pmodifiedDate" TIMESTAMP DEFAULT E'Now()', "log_pmodifiedUser" VARCHAR(50), "vmd_year" INTEGER DEFAULT 0, "cp_entry_plotDone" /*BOOLEAN*/INTEGER DEFAULT E'0', "cp_yearNum" INTEGER, "cp_authorObsCode" VARCHAR(255), "plotXYReversedOrientation" /*BOOLEAN*/INTEGER DEFAULT E'0', PRIMARY KEY ("plotID") ); -- -- Table structure for table 'entry_stems' -- DROP TABLE IF EXISTS "entry_stems"; CREATE TABLE "entry_stems" ( "TreeLineID" SERIAL NOT NULL, "AlternateOrder" DOUBLE PRECISION, "plotID" INTEGER NOT NULL, "NC_Code" INTEGER, "altReference_ID" INTEGER, "unknownSp" VARCHAR(50), "Module" VARCHAR(2), "isPlanted" VARCHAR(2), "Subseedling" INTEGER, "CHt_0_50" INTEGER DEFAULT 0, "CHt_50_100" INTEGER DEFAULT 0, "CHt_100_137" INTEGER DEFAULT 0, "SubSapling" INTEGER, "C0_1" INTEGER NOT NULL DEFAULT 0, "C1_2" INTEGER NOT NULL DEFAULT 0, "SubTree" INTEGER, "C2Plus" INTEGER NOT NULL DEFAULT 0, "C5Plus" INTEGER NOT NULL DEFAULT 0, "C10Plus" INTEGER NOT NULL DEFAULT 0, "C15Plus" INTEGER NOT NULL DEFAULT 0, "C20Plus" INTEGER NOT NULL DEFAULT 0, "C25Plus" INTEGER NOT NULL DEFAULT 0, "C30Plus" INTEGER NOT NULL DEFAULT 0, "C35Plus" INTEGER NOT NULL DEFAULT 0, "BigStem1" INTEGER, "BigStem2" INTEGER, "BigStem3" INTEGER, "BigStem4" INTEGER, "BigStem5" INTEGER, "BigStem6" INTEGER, "BigStem7" INTEGER, "BigStem8" INTEGER, "BigStem9" INTEGER, "BigStem10" INTEGER, "BigStem11" INTEGER, "BigStem12" INTEGER, "Notes" VARCHAR(255), "errors" TEXT, "NC_Code_History" TEXT, "duplicate" INTEGER DEFAULT 0, "TreeSummarySamplingCase" DOUBLE PRECISION, "IsIndividualRecord" /*BOOLEAN*/INTEGER DEFAULT E'0', "stemIDCode" VARCHAR(20), "stemSource" VARCHAR(10), "stemHealth" VARCHAR(50), "stemComment" VARCHAR(200), "stemX" DOUBLE PRECISION, "stemY" DOUBLE PRECISION, "dgh" DOUBLE PRECISION, "Height" DOUBLE PRECISION, "DBH" DOUBLE PRECISION, "resprout" /*BOOLEAN*/INTEGER DEFAULT E'0', "userDefined1" VARCHAR(255), "userDefined2" VARCHAR(255), "userDefined3" VARCHAR(255), "userDefined4" VARCHAR(255), "userDefined5" VARCHAR(255), "userDefined6" VARCHAR(255), "userDefined7" VARCHAR(255), "userDefined8" VARCHAR(255), "userDefined9" VARCHAR(255), "userDefined10" VARCHAR(255), "userDefined11" VARCHAR(255), "userDefined12" VARCHAR(255), "userDefined13" VARCHAR(255), "userDefined14" VARCHAR(255), "userDefined15" VARCHAR(255), "userDefined16" VARCHAR(255), "record_src" VARCHAR(50), "log_createDate" TIMESTAMP DEFAULT E'Now()', "log_createUser" VARCHAR(50), "log_modifiedDate" TIMESTAMP DEFAULT E'Now()', "log_modifiedUser" VARCHAR(50), "vmdMapSequence" INTEGER, "vmd_recoverySortOrder" TIMESTAMP, PRIMARY KEY ("TreeLineID") ); -- -- Table structure for table 'fieldList' -- DROP TABLE IF EXISTS "fieldList"; CREATE TABLE "fieldList" ( "fieldList_ID" SERIAL NOT NULL, "tableName" VARCHAR(50), "fieldName" VARCHAR(50), "listValue" VARCHAR(255), "valueDesc" TEXT, "sortOrd" DOUBLE PRECISION, "entry_displayValue" VARCHAR(255), "entry_description" TEXT, "entry_hideValue" INTEGER, "entry_sortingOrder" DOUBLE PRECISION, "entry_newRecord" /*BOOLEAN*/INTEGER DEFAULT E'0', "entry_obsoleteValue" /*BOOLEAN*/INTEGER DEFAULT E'0', "entry_obsoleteUseInstead" VARCHAR(255), PRIMARY KEY ("fieldList_ID") ); -- -- Table structure for table 'graphic' -- DROP TABLE IF EXISTS "graphic"; CREATE TABLE "graphic" ( "GRAPHIC_ID" SERIAL NOT NULL, "OBSERVATION_ID" INTEGER NOT NULL, "graphicName" VARCHAR(30), "graphicLocation" TEXT, "graphicDescription" TEXT, "graphicType" VARCHAR(20), "graphicDate" TIMESTAMP, "graphicData" TEXT, "dba_src_ID" INTEGER, "accessionCode" VARCHAR(100), PRIMARY KEY ("GRAPHIC_ID") ); -- -- Table structure for table 'migrationEntity' -- DROP TABLE IF EXISTS "migrationEntity"; CREATE TABLE "migrationEntity" ( "migrationEntity_ID" SERIAL NOT NULL, "migrationPkg_ID" INTEGER, "tableName" VARCHAR(100), "record_ID" INTEGER, "sourceRecord_ID" INTEGER, "entryDBPath" TEXT, "migrationDBPath" TEXT, PRIMARY KEY ("migrationEntity_ID") ); -- -- Table structure for table 'migrationPackage' -- DROP TABLE IF EXISTS "migrationPackage"; CREATE TABLE "migrationPackage" ( "migrationPkg_ID" SERIAL NOT NULL, "migrationGUID" VARCHAR(38), "migrationName" VARCHAR(100), "migrationDescription" TEXT, "userName" VARCHAR(50), "archiveFullPath" TEXT, "entryFullPath" TEXT, "migrationDBFullPath" TEXT, "migrationDateStart" TIMESTAMP, "migrationDateEnd" VARCHAR(50), "totalRecordsAdded" INTEGER, "plotsAdded" INTEGER, "observationsAdded" INTEGER, PRIMARY KEY ("migrationPkg_ID") ); -- -- Table structure for table 'namedPlace' -- DROP TABLE IF EXISTS "namedPlace"; CREATE TABLE "namedPlace" ( "NAMEDPLACE_ID" SERIAL NOT NULL, "placeSystem" VARCHAR(50), "placeName" VARCHAR(100) NOT NULL, "placeDescription" TEXT, "placeCode" VARCHAR(15), "owner" VARCHAR(100), "reference_ID" INTEGER, "accessionCode" VARCHAR(100), "dba_src_ID" INTEGER, PRIMARY KEY ("NAMEDPLACE_ID") ); -- -- Table structure for table 'namedPlaceCorrelation' -- DROP TABLE IF EXISTS "namedPlaceCorrelation"; CREATE TABLE "namedPlaceCorrelation" ( "NAMEDPLACECORRELATION_ID" SERIAL NOT NULL, "PARENTPLACE_ID" INTEGER NOT NULL, "CHILDPLACE_ID" INTEGER NOT NULL, "placeConvergence" VARCHAR(20) NOT NULL, PRIMARY KEY ("NAMEDPLACECORRELATION_ID") ); -- -- Table structure for table 'note' -- DROP TABLE IF EXISTS "note"; CREATE TABLE "note" ( "NOTE_ID" SERIAL NOT NULL, "NOTELINK_ID" INTEGER NOT NULL, "PARTY_ID" INTEGER, "ROLE_ID" INTEGER, "noteDate" TIMESTAMP, "noteType" VARCHAR(20) NOT NULL, "noteText" TEXT NOT NULL, "dba_src_ID" INTEGER, "accessionCode" VARCHAR(100), PRIMARY KEY ("NOTE_ID") ); -- -- Table structure for table 'noteLink' -- DROP TABLE IF EXISTS "noteLink"; CREATE TABLE "noteLink" ( "NOTELINK_ID" SERIAL NOT NULL, "tableName" VARCHAR(50) NOT NULL, "attributeName" VARCHAR(50), "tableRecord" INTEGER NOT NULL, "dba_src_ID" INTEGER, PRIMARY KEY ("NOTELINK_ID") ); -- -- Table structure for table 'observation' -- DROP TABLE IF EXISTS "observation"; CREATE TABLE "observation" ( "OBSERVATION_ID" SERIAL NOT NULL, "PREVIOUSOBS_ID" INTEGER, "PLOT_ID" INTEGER NOT NULL, "PROJECT_ID" INTEGER, "authorObsCode" VARCHAR(255), "obsStartDate" TIMESTAMP, "obsEndDate" TIMESTAMP, "dateAccuracy" VARCHAR(30), "dateEntered" TIMESTAMP DEFAULT E'=Now()', "COVERMETHOD_ID" INTEGER, "coverDispersion" VARCHAR(30), "autoTaxonCover" /*BOOLEAN*/INTEGER DEFAULT E'0', "STRATUMMETHOD_ID" INTEGER, "methodNarrative" TEXT, "taxonObservationArea" DOUBLE PRECISION, "stemSizeLimit" DOUBLE PRECISION, "stemObservationArea" DOUBLE PRECISION, "stemSampleMethod" VARCHAR(30), "originalData" TEXT, "effortLevel" VARCHAR(30), "plotValidationLevel" INTEGER, "floristicQuality" VARCHAR(30), "bryophyteQuality" VARCHAR(30), "lichenQuality" VARCHAR(30), "observationNarrative" TEXT, "landscapeNarrative" TEXT, "homogeneity" VARCHAR(255), "phenologicAspect" VARCHAR(255), "representativeness" VARCHAR(255), "standMaturity" VARCHAR(255), "successionalStatus" TEXT, "basalArea" DOUBLE PRECISION, "hydrologicRegime" VARCHAR(255), "soilMoistureRegime" VARCHAR(255), "soilDrainage" VARCHAR(255), "waterSalinity" VARCHAR(255), "waterDepth" DOUBLE PRECISION, "shoreDistance" DOUBLE PRECISION, "soilDepth" DOUBLE PRECISION, "organicDepth" DOUBLE PRECISION, "SOILTAXON_ID" INTEGER, "soilTaxonSrc" VARCHAR(200), "soilTaxonComplex" VARCHAR(255), "soilMapUnit" VARCHAR(255), "percentBedRock" DOUBLE PRECISION, "percentRockGravel" DOUBLE PRECISION, "percentWood" DOUBLE PRECISION, "percentLitter" DOUBLE PRECISION, "percentBareSoil" DOUBLE PRECISION, "percentWater" DOUBLE PRECISION, "percentOther" DOUBLE PRECISION, "nameOther" VARCHAR(30), "treeHt" DOUBLE PRECISION, "shrubHt" DOUBLE PRECISION, "fieldHt" DOUBLE PRECISION, "nonvascularHt" DOUBLE PRECISION, "submergedHt" DOUBLE PRECISION, "treeCover" DOUBLE PRECISION, "shrubCover" DOUBLE PRECISION, "fieldCover" DOUBLE PRECISION, "nonvascularCover" DOUBLE PRECISION, "floatingCover" DOUBLE PRECISION, "submergedCover" DOUBLE PRECISION, "dominantStratum" VARCHAR(40), "growthform1Type" VARCHAR(40), "growthform2Type" VARCHAR(40), "growthform3Type" VARCHAR(40), "growthform1Cover" DOUBLE PRECISION, "growthform2Cover" DOUBLE PRECISION, "growthform3Cover" DOUBLE PRECISION, "totalCover" DOUBLE PRECISION, "accessionCode" VARCHAR(100), "notesPublic" /*BOOLEAN*/INTEGER DEFAULT E'0', "notesMgt" /*BOOLEAN*/INTEGER DEFAULT E'0', "revisions" /*BOOLEAN*/INTEGER DEFAULT E'0', "cust_cvs_CowardinSystem" VARCHAR(100), "cust_cvs_mcnab_LFI_asp" DOUBLE PRECISION, "cust_cvs_mcnab_LFI_45" DOUBLE PRECISION, "cust_cvs_mcnab_LFI_90" DOUBLE PRECISION, "cust_cvs_mcnab_LFI_135" DOUBLE PRECISION, "cust_cvs_mcnab_LFI_180" DOUBLE PRECISION, "cust_cvs_mcnab_LFI_225" DOUBLE PRECISION, "cust_cvs_mcnab_LFI_270" DOUBLE PRECISION, "cust_cvs_mcnab_LFI_315" DOUBLE PRECISION, "cust_cvs_mcnab_TSI_asp" DOUBLE PRECISION, "cust_cvs_mcnab_TSI_45" DOUBLE PRECISION, "cust_cvs_mcnab_TSI_90" DOUBLE PRECISION, "cust_cvs_mcnab_TSI_135" DOUBLE PRECISION, "cust_cvs_mcnab_TSI_180" DOUBLE PRECISION, "cust_cvs_mcnab_TSI_225" DOUBLE PRECISION, "cust_cvs_mcnab_TSI_270" DOUBLE PRECISION, "cust_cvs_mcnab_TSI_315" DOUBLE PRECISION, "cust_cvs_mcnab_LFI" DOUBLE PRECISION, "cust_cvs_mcnab_TSI" DOUBLE PRECISION, "cust_cvs_LeafType" VARCHAR(100), "cust_cvs_LeafPhenology" VARCHAR(100), "cust_cvs_CanopyHeight" DOUBLE PRECISION, "cust_cvs_compassType" VARCHAR(10), "cust_cvs_LocationSource" VARCHAR(50), "cust_cvs_GPSLocationX" DOUBLE PRECISION, "cust_cvs_GPSLocationY" DOUBLE PRECISION, "cust_cvs_Ownership" VARCHAR(100), "cust_cvs_taxaRichness" INTEGER, "cust_cvs_lengthSoilProbe" DOUBLE PRECISION, "cust_cvs_percentHistosol" DOUBLE PRECISION, "cust_cvs_percentBedrock" DOUBLE PRECISION, "cust_cvs_percentBoulder" DOUBLE PRECISION, "cust_cvs_percentGravelCobble" DOUBLE PRECISION, "cust_cvs_percentMineralSoil" DOUBLE PRECISION, "cust_cvs_percentCWD" DOUBLE PRECISION, "cust_cvs_percentFWD" DOUBLE PRECISION, "cust_cvs_percentDuff" DOUBLE PRECISION, "cust_cvs_percentLitter" DOUBLE PRECISION, "cust_cvs_percentNonVasc" DOUBLE PRECISION, "cust_cvs_percentWater" DOUBLE PRECISION, "cust_cvs_percentOther" DOUBLE PRECISION, "cust_cvs_percentOtherName" VARCHAR(80), "cust_cvs_samplingLevel" INTEGER, "cust_cvs_postDsgPoly" VARCHAR(255), "cust_cvs_stemsSampled" INTEGER, "cust_cvs_stemSamplingNarrative" TEXT, "cust_eep_plotLastPlantedDate" TIMESTAMP, "cust_eep_plotNotSampled" /*BOOLEAN*/INTEGER DEFAULT E'0', "cust_eep_plotNotSampledReason" TEXT, "entry_ares_herbs" DOUBLE PRECISION, "entry_ares_trees" DOUBLE PRECISION, "entry_currentLandUse" TEXT, "entry_depth" INTEGER, "entry_elevationUnits" INTEGER, "entry_formerLandUse" TEXT, "entry_identificationNarrative" TEXT, "entry_IntensiveModuleList" VARCHAR(10), "entry_moduleSize_Overall" DOUBLE PRECISION, "entry_moduleSizeX" DOUBLE PRECISION, "entry_moduleSizeY" DOUBLE PRECISION, "entry_photosTaken" VARCHAR(255), "entry_physClass" VARCHAR(100), "entry_plot_X" INTEGER, "entry_plot_Y" INTEGER, "entry_soilModulesSampled" VARCHAR(100), "entry_subsequentObsNum" INTEGER, "entry_taxonomicAuthority_ID" INTEGER, "entry_taxonomicAuthorityDate" TIMESTAMP, "entry_team" VARCHAR(6), "entry_plot" VARCHAR(12), "entry_numberOfModules" INTEGER, "errors" TEXT, "dba_src_ID" INTEGER, "dba_notes" TEXT, "numberOfTaxa" INTEGER, UNIQUE ("authorObsCode"), PRIMARY KEY ("OBSERVATION_ID") ); -- -- Table structure for table 'observationContributor' -- DROP TABLE IF EXISTS "observationContributor"; CREATE TABLE "observationContributor" ( "OBSERVATIONCONTRIBUTOR_ID" SERIAL NOT NULL, "OBSERVATION_ID" INTEGER NOT NULL, "PARTY_ID" INTEGER NOT NULL, "ROLE_ID" INTEGER NOT NULL, "contributionDate" TIMESTAMP, "dba_src_ID" INTEGER, "unsure" /*BOOLEAN*/INTEGER DEFAULT E'0', PRIMARY KEY ("OBSERVATIONCONTRIBUTOR_ID") ); -- -- Table structure for table 'observationSynonym' -- DROP TABLE IF EXISTS "observationSynonym"; CREATE TABLE "observationSynonym" ( "OBSERVATIONSYNONYM_ID" SERIAL NOT NULL, "synonymObservation_ID" INTEGER NOT NULL, "primaryObservation_ID" INTEGER NOT NULL, "PARTY_ID" INTEGER NOT NULL, "ROLE_ID" INTEGER NOT NULL, "classStartDate" TIMESTAMP NOT NULL, "classStopDate" TIMESTAMP, "synonymComment" TEXT, "dba_src_ID" INTEGER, "accessionCode" VARCHAR(100), PRIMARY KEY ("OBSERVATIONSYNONYM_ID") ); -- -- Table structure for table 'party' -- DROP TABLE IF EXISTS "party"; CREATE TABLE "party" ( "PARTY_ID" SERIAL NOT NULL, "salutation" VARCHAR(20), "givenName" VARCHAR(50), "middleName" VARCHAR(50), "surName" VARCHAR(50), "suffix" VARCHAR(50), "entry_userInitials" VARCHAR(50), "organizationName" VARCHAR(100), "currentName_ID" INTEGER, "contactInstructions" TEXT, "email" VARCHAR(120), "accessionCode" VARCHAR(100), "partyType" VARCHAR(40), "dba_src_ID" INTEGER, "dba_unsure" VARCHAR(255), PRIMARY KEY ("PARTY_ID") ); -- -- Table structure for table 'partyMember' -- DROP TABLE IF EXISTS "partyMember"; CREATE TABLE "partyMember" ( "partyMember_ID" SERIAL NOT NULL, "parentParty_ID" INTEGER NOT NULL, "childParty_ID" INTEGER NOT NULL, "role_ID" INTEGER, "memberStart" TIMESTAMP NOT NULL, "memberStop" TIMESTAMP, "dba_src_ID" INTEGER, PRIMARY KEY ("partyMember_ID") ); -- -- Table structure for table 'place' -- DROP TABLE IF EXISTS "place"; CREATE TABLE "place" ( "PLOTPLACE_ID" SERIAL NOT NULL, "PLOT_ID" INTEGER NOT NULL, "calculated" /*BOOLEAN*/INTEGER DEFAULT E'0', "NAMEDPLACE_ID" INTEGER NOT NULL, "dba_src_ID" INTEGER, PRIMARY KEY ("PLOTPLACE_ID") ); -- -- Table structure for table 'plantConcept' -- DROP TABLE IF EXISTS "plantConcept"; CREATE TABLE "plantConcept" ( "PLANTCONCEPT_ID" SERIAL NOT NULL, "PLANTNAME_ID" INTEGER NOT NULL, "reference_ID" INTEGER NOT NULL, "plantname" VARCHAR(255), "plantNameWithAuthority" VARCHAR(255), "plantCode" VARCHAR(23), "plantDescription" TEXT, "accessionCode" VARCHAR(255), "dba_src_ID" INTEGER, "entry_sppID" INTEGER, "USDAPlantsCode" VARCHAR(255), "VascularPlant" VARCHAR(255), "denorm_kingdom" VARCHAR(255), "denorm_division" VARCHAR(255), "denorm_category" VARCHAR(255), "denorm_family" VARCHAR(255), "denorm_genus" VARCHAR(255), "denorm_species" VARCHAR(255), "denorm_triAbbr" VARCHAR(50), "denorm_triName" VARCHAR(255), "denorm_quadAbbr" VARCHAR(50), "denorm_quadName" VARCHAR(255), "denorm_hybridX" VARCHAR(50), "taxonLevel" VARCHAR(255), "taxonRank" DOUBLE PRECISION, "lowestParentName" VARCHAR(255), "lowestParentConcept_ID" INTEGER, "lowestChildRank" DOUBLE PRECISION, "commonName" VARCHAR(255), "growthForm_short" VARCHAR(255), "woody" VARCHAR(255), "exotic" VARCHAR(255), "usda_code" VARCHAR(255), "usda_growthForm" VARCHAR(255), PRIMARY KEY ("PLANTCONCEPT_ID") ); -- -- Table structure for table 'plantCorrelation' -- DROP TABLE IF EXISTS "plantCorrelation"; CREATE TABLE "plantCorrelation" ( "PLANTCORRELATION_ID" SERIAL NOT NULL, "PLANTSTATUS_ID" INTEGER NOT NULL, "PLANTCONCEPT_ID" INTEGER NOT NULL, "plantConvergence" VARCHAR(20) NOT NULL, "correlationStart" TIMESTAMP NOT NULL, "correlationStop" TIMESTAMP, "dba_src_ID" INTEGER, PRIMARY KEY ("PLANTCORRELATION_ID") ); -- -- Table structure for table 'plantLineage' -- DROP TABLE IF EXISTS "plantLineage"; CREATE TABLE "plantLineage" ( "PLANTLINEAGE_ID" SERIAL NOT NULL, "childPlantStatus_ID" INTEGER NOT NULL, "parentPlantStatus_ID" INTEGER NOT NULL, "dba_src_ID" INTEGER, PRIMARY KEY ("PLANTLINEAGE_ID") ); -- -- Table structure for table 'plantName' -- DROP TABLE IF EXISTS "plantName"; CREATE TABLE "plantName" ( "PLANTNAME_ID" SERIAL NOT NULL, "plantName" VARCHAR(255) NOT NULL, "reference_ID" INTEGER, "dateEntered" TIMESTAMP DEFAULT E'=Now()', "dba_src_ID" INTEGER, PRIMARY KEY ("PLANTNAME_ID") ); -- -- Table structure for table 'plantStatus' -- DROP TABLE IF EXISTS "plantStatus"; CREATE TABLE "plantStatus" ( "PLANTSTATUS_ID" SERIAL NOT NULL, "PLANTCONCEPT_ID" INTEGER NOT NULL, "reference_ID" INTEGER, "plantConceptStatus" VARCHAR(20) NOT NULL, "startDate" TIMESTAMP NOT NULL, "stopDate" TIMESTAMP, "plantPartyComments" TEXT, "plantParentName" VARCHAR(200), "plantParentConcept_id" INTEGER, "plantParent_ID" INTEGER, "plantLevel" VARCHAR(80), "PARTY_ID" INTEGER NOT NULL, "dba_src_ID" INTEGER, "denorm_FamilyName" VARCHAR(80), "accessionCode" VARCHAR(100), PRIMARY KEY ("PLANTSTATUS_ID") ); -- -- Table structure for table 'plantUsage' -- DROP TABLE IF EXISTS "plantUsage"; CREATE TABLE "plantUsage" ( "PLANTUSAGE_ID" SERIAL NOT NULL, "PLANTNAME_ID" INTEGER NOT NULL, "PLANTCONCEPT_ID" INTEGER NOT NULL, "usageStart" TIMESTAMP, "usageStop" TIMESTAMP, "plantNameStatus" VARCHAR(20), "plantName" VARCHAR(220), "classSystem" VARCHAR(50), "acceptedSynonym" VARCHAR(220), "PARTY_ID" INTEGER NOT NULL, "PLANTSTATUS_ID" INTEGER, "dba_src_ID" INTEGER, PRIMARY KEY ("PLANTUSAGE_ID") ); -- -- Table structure for table 'plot' -- DROP TABLE IF EXISTS "plot"; CREATE TABLE "plot" ( "PLOT_ID" SERIAL NOT NULL, "authorPlotCode" VARCHAR(255) NOT NULL, "reference_ID" INTEGER, "PARENT_ID" INTEGER, "plotObsolete" /*BOOLEAN*/INTEGER DEFAULT E'0', "REPLACEMENTPLOT_ID" INTEGER, "realLatitude" DOUBLE PRECISION, "realLongitude" DOUBLE PRECISION, "locationAccuracy" DOUBLE PRECISION, "confidentialityStatus" INTEGER, "confidentialityFieldsBlocked" INTEGER, "confidentialityReason" VARCHAR(200), "latitude" DOUBLE PRECISION, "longitude" DOUBLE PRECISION, "authorCoordinateType" VARCHAR(100), "authorCoordinateUnits" VARCHAR(100), "authorE" VARCHAR(20), "authorN" VARCHAR(20), "authorZone" VARCHAR(20), "authorDatum" VARCHAR(20), "authorLocation" VARCHAR(200), "locationNarrative" TEXT, "plotRationaleNarrative" TEXT, "azimuth" DOUBLE PRECISION, "dsgpoly" TEXT, "shape" VARCHAR(50), "area" DOUBLE PRECISION, "standSize" VARCHAR(50), "placementMethod" VARCHAR(50), "permanence" /*BOOLEAN*/INTEGER DEFAULT E'0', "layoutNarrative" TEXT, "elevation" DOUBLE PRECISION, "elevationAccuracy" DOUBLE PRECISION, "elevationRange" DOUBLE PRECISION, "slopeAspect" DOUBLE PRECISION, "minSlopeAspect" DOUBLE PRECISION, "maxSlopeAspect" DOUBLE PRECISION, "slopeGradient" DOUBLE PRECISION, "minSlopeGradient" DOUBLE PRECISION, "maxSlopeGradient" DOUBLE PRECISION, "topoPosition" VARCHAR(255), "landform" VARCHAR(255), "surficialDeposits" VARCHAR(255), "rockType" VARCHAR(255), "county" VARCHAR(255), "cust_cvs_province" VARCHAR(255), "stateProvince" VARCHAR(55), "COUNTRY" VARCHAR(100), "dateentered" TIMESTAMP, "submitter_surname" VARCHAR(100), "submitter_givenname" VARCHAR(100), "submitter_email" VARCHAR(100), "accessionCode" VARCHAR(100), "notesPublic" /*BOOLEAN*/INTEGER DEFAULT E'0', "notesMgt" /*BOOLEAN*/INTEGER DEFAULT E'0', "revisions" /*BOOLEAN*/INTEGER DEFAULT E'0', "cust_eep_reach" VARCHAR(255), "dba_src_ID" INTEGER, UNIQUE ("authorPlotCode"), PRIMARY KEY ("PLOT_ID") ); -- -- Table structure for table 'project' -- DROP TABLE IF EXISTS "project"; CREATE TABLE "project" ( "PROJECT_ID" SERIAL NOT NULL, "projectName" VARCHAR(150) NOT NULL, "projectDescription" TEXT, "startDate" TIMESTAMP, "stopDate" TIMESTAMP, "accessionCode" VARCHAR(100), "dba_src_ID" INTEGER, "entry_projectCode" VARCHAR(10), "entry_projectCodeSource" VARCHAR(50), "entry_contactDetails" TEXT, "entry_eep_length" DOUBLE PRECISION, "entry_eep_width" DOUBLE PRECISION, "entry_eep_area" DOUBLE PRECISION, "entry_eep_requiredPlots" INTEGER, "entry_eep_projectType" INTEGER DEFAULT 1, "entry_riverBasin_np_ID" INTEGER, UNIQUE ("accessionCode"), PRIMARY KEY ("PROJECT_ID"), UNIQUE ("entry_projectCodeSource", "entry_projectCode") ); -- -- Table structure for table 'projectContributor' -- DROP TABLE IF EXISTS "projectContributor"; CREATE TABLE "projectContributor" ( "PROJECTCONTRIBUTOR_ID" SERIAL NOT NULL, "PROJECT_ID" INTEGER NOT NULL, "PARTY_ID" INTEGER NOT NULL, "ROLE_ID" INTEGER, "surname" VARCHAR(50), "cheatRole" VARCHAR(50), "dba_src_ID" INTEGER, PRIMARY KEY ("PROJECTCONTRIBUTOR_ID") ); -- -- Table structure for table 'reference' -- DROP TABLE IF EXISTS "reference"; CREATE TABLE "reference" ( "reference_ID" SERIAL NOT NULL, "shortName" VARCHAR(250), "fulltext" TEXT, "referenceType" VARCHAR(250), "title" VARCHAR(250), "titleSuperior" VARCHAR(250), "pubDate" TIMESTAMP, "pubYear" INTEGER, "pubMonth" INTEGER, "pubDay" INTEGER, "accessDate" TIMESTAMP, "conferenceDate" TIMESTAMP, "referenceJournal_ID" INTEGER, "volume" VARCHAR(250), "issue" VARCHAR(250), "pageRange" VARCHAR(250), "totalPages" INTEGER, "publisher" VARCHAR(250), "publicationPlace" VARCHAR(250), "isbn" VARCHAR(250), "edition" VARCHAR(250), "numberOfVolumes" INTEGER, "chapterNumber" INTEGER, "reportNumber" INTEGER, "communicationType" VARCHAR(250), "degree" VARCHAR(250), "url" TEXT, "doi" TEXT, "additionalInfo" TEXT, "accessionCode" VARCHAR(100), "dba_src_ID" INTEGER, "cust_cvs_IsTaxonomicManual" /*BOOLEAN*/INTEGER DEFAULT E'0', "cust_cvs_unparsedAuthors" TEXT, "cust_cvs_referenceJournalName" VARCHAR(200), PRIMARY KEY ("reference_ID") ); -- -- Table structure for table 'referenceAltIdent' -- DROP TABLE IF EXISTS "referenceAltIdent"; CREATE TABLE "referenceAltIdent" ( "referenceAltIdent_ID" SERIAL NOT NULL, "reference_ID" INTEGER NOT NULL, "system" VARCHAR(250), "identifier" VARCHAR(250) NOT NULL, "dba_src_ID" INTEGER, PRIMARY KEY ("referenceAltIdent_ID") ); -- -- Table structure for table 'referenceContributor' -- DROP TABLE IF EXISTS "referenceContributor"; CREATE TABLE "referenceContributor" ( "referenceContributor_ID" SERIAL NOT NULL, "reference_ID" INTEGER NOT NULL, "referenceParty_ID" INTEGER NOT NULL, "roleType" VARCHAR(250), "position" INTEGER, "dba_src_ID" INTEGER, PRIMARY KEY ("referenceContributor_ID") ); -- -- Table structure for table 'referenceJournal' -- DROP TABLE IF EXISTS "referenceJournal"; CREATE TABLE "referenceJournal" ( "referenceJournal_ID" SERIAL NOT NULL, "journal" VARCHAR(250) NOT NULL, "issn" VARCHAR(250), "abbreviation" VARCHAR(250), "accessionCode" VARCHAR(100), "dba_src_ID" INTEGER, PRIMARY KEY ("referenceJournal_ID") ); -- -- Table structure for table 'referenceParty' -- DROP TABLE IF EXISTS "referenceParty"; CREATE TABLE "referenceParty" ( "referenceParty_ID" SERIAL NOT NULL, "type" VARCHAR(250), "positionName" VARCHAR(250), "salutation" VARCHAR(250), "givenName" VARCHAR(250), "surname" VARCHAR(250), "suffix" VARCHAR(250), "organizationName" VARCHAR(250), "currentParty_ID" INTEGER, "accessionCode" VARCHAR(100), "dba_src_ID" INTEGER, PRIMARY KEY ("referenceParty_ID") ); -- -- Table structure for table 'soilObs' -- DROP TABLE IF EXISTS "soilObs"; CREATE TABLE "soilObs" ( "SOILOBS_ID" SERIAL NOT NULL, "OBSERVATION_ID" INTEGER NOT NULL, "soilHorizon" VARCHAR(15) NOT NULL, "soilDepthTop" DOUBLE PRECISION, "soilDepthBottom" DOUBLE PRECISION, "soilColor" VARCHAR(30), "soilOrganic" DOUBLE PRECISION, "soilTexture" VARCHAR(50), "soilSand" DOUBLE PRECISION, "soilSilt" DOUBLE PRECISION, "soilClay" DOUBLE PRECISION, "soilCoarse" DOUBLE PRECISION, "soilPH" DOUBLE PRECISION, "exchangeCapacity" DOUBLE PRECISION, "baseSaturation" DOUBLE PRECISION, "soilDescription" TEXT, "cust_cvs_collectionName" VARCHAR(50), "cust_cvs_Lab" VARCHAR(10), "cust_cvs_N" DOUBLE PRECISION, "cust_cvs_S" DOUBLE PRECISION, "cust_cvs_P" DOUBLE PRECISION, "cust_cvs_Ca_ppm" DOUBLE PRECISION, "cust_cvs_Mg_ppm" DOUBLE PRECISION, "cust_cvs_K_ppm" DOUBLE PRECISION, "cust_cvs_Na_ppm" DOUBLE PRECISION, "cust_cvs_percent_Ca" DOUBLE PRECISION, "cust_cvs_percent_Mg" DOUBLE PRECISION, "cust_cvs_percent_K" DOUBLE PRECISION, "cust_cvs_percent_Na" DOUBLE PRECISION, "cust_cvs_percent_Othr" DOUBLE PRECISION, "cust_cvs_percent_H" DOUBLE PRECISION, "cust_cvs_B_ppm" DOUBLE PRECISION, "cust_cvs_Fe_ppm" DOUBLE PRECISION, "cust_cvs_Mn_ppm" DOUBLE PRECISION, "cust_cvs_Cu_ppm" DOUBLE PRECISION, "cust_cvs_Zn_ppm" DOUBLE PRECISION, "cust_cvs_Al_ppm" DOUBLE PRECISION, "cust_cvs_Density" DOUBLE PRECISION, "cust_cvs_Ca_over_Mg_ppm" DOUBLE PRECISION, "dba_src_ID" INTEGER, PRIMARY KEY ("SOILOBS_ID") ); -- -- Table structure for table 'soilTaxon' -- DROP TABLE IF EXISTS "soilTaxon"; CREATE TABLE "soilTaxon" ( "SOILTAXON_ID" SERIAL NOT NULL, "soilCode" VARCHAR(15), "soilName" VARCHAR(100), "soilLevel" INTEGER, "SOILPARENT_ID" INTEGER, "soilFramework" VARCHAR(33), "accessionCode" VARCHAR(100), "dba_src_ID" INTEGER, PRIMARY KEY ("SOILTAXON_ID") ); -- -- Table structure for table 'stemCount' -- DROP TABLE IF EXISTS "stemCount"; CREATE TABLE "stemCount" ( "STEMCOUNT_ID" SERIAL NOT NULL, "TAXONIMPORTANCE_ID" INTEGER NOT NULL, "stemDiameter" DOUBLE PRECISION, "stemDiameterAccuracy" DOUBLE PRECISION, "stemHeight" DOUBLE PRECISION, "stemHeightAccuracy" DOUBLE PRECISION, "stemCount" INTEGER NOT NULL, "stemTaxonArea" DOUBLE PRECISION, "dba_src_ID" INTEGER, "cust_cvs_ddh" DOUBLE PRECISION, "cust_cvs_ddhAccuracy" DOUBLE PRECISION, "cust_cvs_isPlanted" VARCHAR(2), "cust_cvs_subsamplingFactor" DOUBLE PRECISION, "entry_herbLineID" INTEGER, "entry_treeLineID" INTEGER, "entry_recordCount" INTEGER, "entry_sortOrder" DOUBLE PRECISION, PRIMARY KEY ("STEMCOUNT_ID") ); -- -- Table structure for table 'stemLocation' -- DROP TABLE IF EXISTS "stemLocation"; CREATE TABLE "stemLocation" ( "STEMLOCATION_ID" SERIAL NOT NULL, "STEMCOUNT_ID" INTEGER NOT NULL, "stemCode" VARCHAR(20), "stemXPosition" DOUBLE PRECISION, "stemYPosition" DOUBLE PRECISION, "stemHealth" VARCHAR(50), "dba_src_ID" INTEGER, "cust_cvs_stemSource" VARCHAR(50), "cust_cvs_stemComment" TEXT, "cust_cvs_resprout" /*BOOLEAN*/INTEGER DEFAULT E'0', "entry_sortOrder" DOUBLE PRECISION, PRIMARY KEY ("STEMLOCATION_ID") ); -- -- Table structure for table 'stratum' -- DROP TABLE IF EXISTS "stratum"; CREATE TABLE "stratum" ( "STRATUM_ID" SERIAL NOT NULL, "OBSERVATION_ID" INTEGER NOT NULL, "STRATUMTYPE_ID" INTEGER NOT NULL, "STRATUMMETHOD_ID" INTEGER, "stratumName" VARCHAR(30), "stratumHeight" DOUBLE PRECISION, "stratumBase" DOUBLE PRECISION, "stratumCover" DOUBLE PRECISION, "stratumDescription" VARCHAR(200), "dba_src_ID" INTEGER, "stratumArea" DOUBLE PRECISION, "stratumLabel" VARCHAR(50), PRIMARY KEY ("STRATUM_ID") ); -- -- Table structure for table 'stratumMethod' -- DROP TABLE IF EXISTS "stratumMethod"; CREATE TABLE "stratumMethod" ( "STRATUMMETHOD_ID" SERIAL NOT NULL, "reference_ID" INTEGER, "stratumMethodName" VARCHAR(30) NOT NULL, "stratumMethodDescription" TEXT, "stratumAssignment" VARCHAR(50), "accessionCode" VARCHAR(100), "dba_src_ID" INTEGER, PRIMARY KEY ("STRATUMMETHOD_ID") ); -- -- Table structure for table 'stratumType' -- DROP TABLE IF EXISTS "stratumType"; CREATE TABLE "stratumType" ( "STRATUMTYPE_ID" SERIAL NOT NULL, "STRATUMMETHOD_ID" INTEGER NOT NULL, "stratumIndex" VARCHAR(10), "stratumName" VARCHAR(30), "stratumDescription" TEXT, "dba_src_ID" INTEGER, "stratumOrder" DOUBLE PRECISION, PRIMARY KEY ("STRATUMTYPE_ID") ); -- -- Table structure for table 'taxonAlt' -- DROP TABLE IF EXISTS "taxonAlt"; CREATE TABLE "taxonAlt" ( "taxonAlt_ID" SERIAL NOT NULL, "taxonInterpretation_ID" INTEGER NOT NULL, "plantConcept_ID" INTEGER NOT NULL, "taxonAltFit" VARCHAR(50), "taxonAltConfidence" VARCHAR(50), "taxonAltNotes" TEXT, "dba_src_ID" INTEGER, PRIMARY KEY ("taxonAlt_ID") ); -- -- Table structure for table 'taxonImportance' -- DROP TABLE IF EXISTS "taxonImportance"; CREATE TABLE "taxonImportance" ( "taxonImportance_ID" SERIAL NOT NULL, "taxonObservation_ID" INTEGER NOT NULL, "stratum_ID" INTEGER, "cover" DOUBLE PRECISION, "basalArea" DOUBLE PRECISION, "biomass" DOUBLE PRECISION, "inferenceArea" DOUBLE PRECISION, "cust_cvs_StemFrequency" INTEGER, "dba_src_ID" INTEGER, "entry_herbLineID" INTEGER, "entry_treeLineID" INTEGER, "cust_cvs_corner1Presence" INTEGER, "cust_cvs_corner2Presence" INTEGER, "cust_cvs_corner3Presence" INTEGER, "cust_cvs_corner4Presence" INTEGER, "cust_cvs_corner5Presence" INTEGER, "cust_cvs_originalCoverCode" VARCHAR(10), PRIMARY KEY ("taxonImportance_ID") ); -- -- Table structure for table 'taxonInterpretation' -- DROP TABLE IF EXISTS "taxonInterpretation"; CREATE TABLE "taxonInterpretation" ( "TAXONINTERPRETATION_ID" SERIAL NOT NULL, "TAXONOBSERVATION_ID" INTEGER NOT NULL, "stemLocation_ID" INTEGER, "PLANTCONCEPT_ID" INTEGER NOT NULL, "interpretationDate" TIMESTAMP NOT NULL, "PLANTNAME_ID" INTEGER, "PARTY_ID" INTEGER, "ROLE_ID" INTEGER, "interpretationType" VARCHAR(30), "reference_ID" INTEGER, "originalInterpretation" /*BOOLEAN*/INTEGER NOT NULL DEFAULT E'0', "currentInterpretation" /*BOOLEAN*/INTEGER NOT NULL DEFAULT E'0', "taxonFit" VARCHAR(50), "taxonConfidence" VARCHAR(50), "collector_ID" INTEGER, "collectionNumber" VARCHAR(100), "collectionDate" TIMESTAMP, "museum_ID" INTEGER, "museumAccessionNumber" VARCHAR(100), "groupType" VARCHAR(20), "notes" TEXT, "notesPublic" /*BOOLEAN*/INTEGER DEFAULT E'0', "notesMgt" /*BOOLEAN*/INTEGER DEFAULT E'0', "revisions" /*BOOLEAN*/INTEGER DEFAULT E'0', "dba_src_ID" INTEGER, "accessionCode" VARCHAR(100), PRIMARY KEY ("TAXONINTERPRETATION_ID") ); -- -- Table structure for table 'taxonObservation' -- DROP TABLE IF EXISTS "taxonObservation"; CREATE TABLE "taxonObservation" ( "TAXONOBSERVATION_ID" SERIAL NOT NULL, "OBSERVATION_ID" INTEGER NOT NULL, "authorPlantName" VARCHAR(255), "reference_ID" INTEGER, "currentTaxonInterpPlantConcept_ID" INTEGER, "taxonInferenceArea" DOUBLE PRECISION, "accessionCode" VARCHAR(100), "dba_src_ID" INTEGER, "cust_cvs_corner1Presence" INTEGER, "cust_cvs_corner2Presence" INTEGER, "cust_cvs_corner3Presence" INTEGER, "cust_cvs_corner4Presence" INTEGER, "cust_cvs_corner5Presence" INTEGER, "entry_herbLineID" INTEGER, "entry_treeLineID" INTEGER, "entry_sppID" INTEGER, "entry_unknownSp" VARCHAR(50), "entry_sortOrder" DOUBLE PRECISION, "cust_cvs_collectionFlag" VARCHAR(50), "taxonObservationNotes" TEXT, "temp_taxonUpToDate" /*BOOLEAN*/INTEGER DEFAULT E'0', PRIMARY KEY ("TAXONOBSERVATION_ID") ); -- -- Table structure for table 'telephone' -- DROP TABLE IF EXISTS "telephone"; CREATE TABLE "telephone" ( "TELEPHONE_ID" SERIAL NOT NULL, "PARTY_ID" INTEGER NOT NULL, "phoneNumber" VARCHAR(30) NOT NULL, "phoneType" VARCHAR(20) NOT NULL, "dba_src_ID" INTEGER, PRIMARY KEY ("TELEPHONE_ID") ); -- -- Table structure for table 'userDataset' -- DROP TABLE IF EXISTS "userDataset"; CREATE TABLE "userDataset" ( "userDataset_ID" SERIAL NOT NULL, "usr_ID" INTEGER NOT NULL, "datasetStart" TIMESTAMP, "datasetStop" TIMESTAMP, "accessionCode" VARCHAR(100), "datasetName" VARCHAR(100) NOT NULL, "datasetDescription" TEXT, "datasetType" VARCHAR(50), "datasetSharing" VARCHAR(30), "datasetPassword" VARCHAR(50), PRIMARY KEY ("userDataset_ID") ); -- -- Table structure for table 'userDatasetItem' -- DROP TABLE IF EXISTS "userDatasetItem"; CREATE TABLE "userDatasetItem" ( "userDatasetItem_ID" SERIAL NOT NULL, "userDataset_ID" INTEGER NOT NULL, "itemAccessionCode" VARCHAR(100) NOT NULL, "itemType" VARCHAR(50), "itemStart" TIMESTAMP NOT NULL, "itemStop" TIMESTAMP, "notes" TEXT, PRIMARY KEY ("userDatasetItem_ID") ); -- -- Table structure for table 'userDefined' -- DROP TABLE IF EXISTS "userDefined"; CREATE TABLE "userDefined" ( "USERDEFINED_ID" SERIAL NOT NULL, "userDefinedName" VARCHAR(50), "userDefinedMetadata" TEXT, "userDefinedCategory" VARCHAR(30), "userDefinedType" VARCHAR(20) NOT NULL, "tableName" VARCHAR(50) NOT NULL, "accessionCode" VARCHAR(100), "dba_src_ID" INTEGER, PRIMARY KEY ("USERDEFINED_ID") ); -- -- Table structure for table 'usr' -- DROP TABLE IF EXISTS "usr"; CREATE TABLE "usr" ( "usr_ID" SERIAL NOT NULL, "party_ID" INTEGER NOT NULL, "password" VARCHAR(50) NOT NULL, "permission_type" INTEGER NOT NULL, "begin_time" TIMESTAMP, "last_connect" TIMESTAMP, "ticket_count" INTEGER, "email_address" VARCHAR(100) NOT NULL, "preferred_name" VARCHAR(100), "remote_address" VARCHAR(100), PRIMARY KEY ("usr_ID") ); -- -- Table structure for table 'Z_backend_TableDescription' -- DROP TABLE IF EXISTS "Z_backend_TableDescription"; CREATE TABLE "Z_backend_TableDescription" ( "Z_backend_tbl_descID" SERIAL, "tableName" VARCHAR(50), "tableModule" VARCHAR(50), "actualTableName" VARCHAR(50) NOT NULL, "entryTableName" VARCHAR(50), "importOrder" DOUBLE PRECISION, "Client" /*BOOLEAN*/INTEGER DEFAULT E'0', "Backend" /*BOOLEAN*/INTEGER DEFAULT E'0', "Temp" /*BOOLEAN*/INTEGER DEFAULT E'0', "Archive" /*BOOLEAN*/INTEGER DEFAULT E'0', "countRecords" INTEGER, "countNewRecords" INTEGER, PRIMARY KEY ("actualTableName"), UNIQUE ("Z_backend_tbl_descID") ); -- -- Table structure for table 'Z_FieldDescription' -- DROP TABLE IF EXISTS "Z_FieldDescription"; CREATE TABLE "Z_FieldDescription" ( "Z_field_ID" SERIAL NOT NULL, "TableName" VARCHAR(80) DEFAULT E'observation', "FieldName" VARCHAR(80), "FieldLabel" VARCHAR(255), "key" VARCHAR(50), "references" VARCHAR(100), "FieldDefinition" TEXT, "altHelpDefinition" TEXT, "nonNullRecs" INTEGER, "dataEntryImpl" VARCHAR(50), "fieldType" VARCHAR(50), "fieldLength" INTEGER, "fieldModule" VARCHAR(50), "archiveField" /*BOOLEAN*/INTEGER DEFAULT E'0', "countNonNulls" INTEGER, PRIMARY KEY ("Z_field_ID"), UNIQUE ("TableName", "FieldName") ); -- -- Table structure for table 'zvegbank_version' -- DROP TABLE IF EXISTS "zvegbank_version"; CREATE TABLE "zvegbank_version" ( "verID" SERIAL NOT NULL, "versionNumber" VARCHAR(100), "versionDate" TIMESTAMP, "versionDescription" TEXT, "databaseType" VARCHAR(100), "databaseDomain" VARCHAR(255), PRIMARY KEY ("verID") );