Project

General

Profile

« Previous | Next » 

Revision 8067

Added by Aaron Marcuse-Kubitza over 11 years ago

Refreshed SALVIAS

View differences:

inputs/SALVIAS/salvias_plots.schema.sql
1 1 
SET standard_conforming_strings = off;
2 2 
SET escape_string_warning = off;
3 
-- MySQL dump 10.13  Distrib 5.5.28, for debian-linux-gnu (x86_64)
3 
-- MySQL dump 10.13  Distrib 5.5.29, for debian-linux-gnu (x86_64)
4 4 
--
5 5 
-- Host: localhost    Database: salvias_plots
6 6 
-- ------------------------------------------------------
7 
-- Server version	5.5.28-0ubuntu0.12.04.2
7 
-- Server version	5.5.29-0ubuntu0.12.04.2
8 8 

  		




  9
  9
  
    
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;


  		




  10
  10
  
    
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;


  		




  ......




  177
  177
  
    
/*!40101 SET character_set_client = @saved_cs_client */;


  		




  178
  178
  
    

  		




  179
  179
  
    
--


  		




  180
  
  
    
-- Table structure for table "log_plot_download"


  		




  181
  
  
    
--


  		




  182
  
  
    

  		




  183
  
  
    
DROP TABLE IF EXISTS "log_plot_download";


  		




  184
  
  
    
/*!40101 SET @saved_cs_client     = @@character_set_client */;


  		




  185
  
  
    
/*!40101 SET character_set_client = utf8 */;


  		




  186
  
  
    
CREATE TABLE "log_plot_download" (


  		




  187
  
  
    
  "EnteredBy" varchar(14) NOT NULL DEFAULT '',


  		




  188
  
  
    
  "download_timestamp" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,


  		




  189
  
  
    
  "ip" varchar(21) NOT NULL DEFAULT '',


  		




  190
  
  
    
  "plot_id" integer DEFAULT NULL,


  		




  191
  
  
    
  "plot_administrator" varchar(30) NOT NULL DEFAULT '',


  		




  192
  
  
    
  "project_id" integer NOT NULL DEFAULT '0',


  		




  193
  
  
    
  PRIMARY KEY ("EnteredBy","download_timestamp"),


  		




  194
  
  
    
  /*KEY "plot_administrator" ("plot_administrator","project_id")*/CHECK (true)


  		




  195
  
  
    
);


  		




  196
  
  
    
/*!40101 SET character_set_client = @saved_cs_client */;


  		




  197
  
  
    

  		




  198
  
  
    
--


  		




  199
  180
  
    
-- Table structure for table "lookup_MethodCode"


  		




  200
  181
  
    
--


  		




  201
  182
  
    

  		




  ......




  224
  205
  
    
/*!40101 SET character_set_client = @saved_cs_client */;


  		




  225
  206
  
    

  		




  226
  207
  
    
--


  		




  227
  
  
    
-- Table structure for table "permissions_plot_user"


  		




  228
  
  
    
--


  		




  229
  
  
    

  		




  230
  
  
    
DROP TABLE IF EXISTS "permissions_plot_user";


  		




  231
  
  
    
/*!40101 SET @saved_cs_client     = @@character_set_client */;


  		




  232
  
  
    
/*!40101 SET character_set_client = utf8 */;


  		




  233
  
  
    
CREATE TABLE "permissions_plot_user" (


  		




  234
  
  
    
  "plot_id" integer NOT NULL DEFAULT '0',


  		




  235
  
  
    
  "username" varchar(50) NOT NULL DEFAULT '',


  		




  236
  
  
    
  "access_level" integer NOT NULL DEFAULT '0',


  		




  237
  
  
    
  PRIMARY KEY ("plot_id","username"),


  		




  238
  
  
    
  /*KEY "access_level" ("access_level")*/CHECK (true)


  		




  239
  
  
    
);


  		




  240
  
  
    
/*!40101 SET character_set_client = @saved_cs_client */;


  		




  241
  
  
    

  		




  242
  
  
    
--


  		




  243
  
  
    
-- Table structure for table "plotMetadata"


  		




  244
  
  
    
--


  		




  245
  
  
    

  		




  246
  
  
    
DROP TABLE IF EXISTS "plotMetadata";


  		




  247
  
  
    
/*!40101 SET @saved_cs_client     = @@character_set_client */;


  		




  248
  
  
    
/*!40101 SET character_set_client = utf8 */;


  		




  249
  
  
    
CREATE TABLE "plotMetadata" (


  		




  250
  
  
    
  "PlotID" integer NOT NULL,


  		




  251
  
  
    
  "orig_filename" text,


  		




  252
  
  
    
  "AccessCode" integer DEFAULT NULL,


  		




  253
  
  
    
  "project_id" integer DEFAULT NULL,


  		




  254
  
  
    
  "PrimOwnerID" varchar(30) DEFAULT NULL,


  		




  255
  
  
    
  "SiteCode" varchar(100) DEFAULT NULL,


  		




  256
  
  
    
  "SiteName" varchar(150) DEFAULT NULL,


  		




  257
  
  
    
  "new_world" char(3) DEFAULT NULL,


  		




  258
  
  
    
  "MajorGeo" varchar(255) DEFAULT NULL,


  		




  259
  
  
    
  "Country" varchar(80) DEFAULT NULL,


  		




  260
  
  
    
  "PolDiv1" varchar(50) DEFAULT NULL,


  		




  261
  
  
    
  "pol1_type" varchar(25) DEFAULT NULL,


  		




  262
  
  
    
  "pol2" varchar(50) DEFAULT NULL,


  		




  263
  
  
    
  "pol2_type" varchar(25) DEFAULT NULL,


  		




  264
  
  
    
  "lat_string" varchar(20) DEFAULT NULL,


  		




  265
  
  
    
  "long_string" varchar(20) DEFAULT NULL,


  		




  266
  
  
    
  "LatDec" float DEFAULT NULL,


  		




  267
  
  
    
  "LongDec" float DEFAULT NULL,


  		




  268
  
  
    
  "lat_long_accuracy" varchar(255) DEFAULT NULL,


  		




  269
  
  
    
  "Elev" float DEFAULT NULL,


  		




  270
  
  
    
  "elev_max_m" float DEFAULT NULL,


  		




  271
  
  
    
  "elev_min_m" float DEFAULT NULL,


  		




  272
  
  
    
  "Precip" float DEFAULT NULL,


  		




  273
  
  
    
  "ElevSource" float DEFAULT NULL,


  		




  274
  
  
    
  "Temp" float DEFAULT NULL,


  		




  275
  
  
    
  "PrecipSource" float DEFAULT NULL,


  		




  276
  
  
    
  "TempSource" varchar(255) DEFAULT NULL,


  		




  277
  
  
    
  "bearing" double precision DEFAULT NULL,


  		




  278
  
  
    
  "slope_aspect" varchar(10) DEFAULT NULL,


  		




  279
  
  
    
  "slope_gradient" integer DEFAULT NULL,


  		




  280
  
  
    
  "clay_percent" float DEFAULT NULL,


  		




  281
  
  
    
  "silt_percent" float DEFAULT NULL,


  		




  282
  
  
    
  "sand_percent" float DEFAULT NULL,


  		




  283
  
  
    
  "pH" float DEFAULT NULL,


  		




  284
  
  
    
  "soil_N" varchar(20) DEFAULT NULL,


  		




  285
  
  
    
  "soil_P" varchar(20) DEFAULT NULL,


  		




  286
  
  
    
  "soil_C" varchar(20) DEFAULT NULL,


  		




  287
  
  
    
  "soil_K" varchar(20) DEFAULT NULL,


  		




  288
  
  
    
  "soil_Mg" varchar(20) DEFAULT NULL,


  		




  289
  
  
    
  "soil_Ca" varchar(20) DEFAULT NULL,


  		




  290
  
  
    
  "soil_Na" varchar(20) DEFAULT NULL,


  		




  291
  
  
    
  "soil_acidity" varchar(20) DEFAULT NULL,


  		




  292
  
  
    
  "soil_base" varchar(20) DEFAULT NULL,


  		




  293
  
  
    
  "soil_cation_cap" varchar(20) DEFAULT NULL,


  		




  294
  
  
    
  "soil_conductivity" varchar(20) DEFAULT NULL,


  		




  295
  
  
    
  "organic_percent" float DEFAULT NULL,


  		




  296
  
  
    
  "soil_texture" varchar(20) DEFAULT NULL,


  		




  297
  
  
    
  "RevisionComments" varchar(255) DEFAULT NULL,


  		




  298
  
  
    
  "Locality_Description" varchar(250) DEFAULT NULL,


  		




  299
  
  
    
  "topography_desc" varchar(250) DEFAULT NULL,


  		




  300
  
  
    
  "vegetation_1" varchar(255) DEFAULT NULL,


  		




  301
  
  
    
  "vegetation_2" varchar(255) DEFAULT NULL,


  		




  302
  
  
    
  "Habitat" varchar(250) DEFAULT NULL,


  		




  303
  
  
    
  "life_zone_code" varchar(10) DEFAULT NULL,


  		




  304
  
  
    
  "life_zone" varchar(150) DEFAULT NULL,


  		




  305
  
  
    
  "PlotMethod" varchar(250) DEFAULT NULL,


  		




  306
  
  
    
  "MethodCode" integer DEFAULT NULL,


  		




  307
  
  
    
  "plot_area_ha" float DEFAULT NULL,


  		




  308
  
  
    
  "recensused" char(3) DEFAULT NULL,


  		




  309
  
  
    
  "date_start" varchar(20) DEFAULT NULL,


  		




  310
  
  
    
  "date_finish" varchar(20) DEFAULT NULL,


  		




  311
  
  
    
  "plot_administrator" varchar(30) DEFAULT NULL,


  		




  312
  
  
    
  "plot_notes" text,


  		




  313
  
  
    
  "tmp_del" char(3) DEFAULT NULL,


  		




  314
  
  
    
  PRIMARY KEY ("PlotID"),


  		




  315
  
  
    
  /*KEY "Country" ("Country")*/CHECK (true),


  		




  316
  
  
    
  /*KEY "PrimOwnerID" ("PrimOwnerID")*/CHECK (true),


  		




  317
  
  
    
  /*KEY "plot_administrator" ("plot_administrator")*/CHECK (true),


  		




  318
  
  
    
  /*KEY "project_id" ("project_id")*/CHECK (true)


  		




  319
  
  
    
);


  		




  320
  
  
    
/*!40101 SET character_set_client = @saved_cs_client */;


  		




  321
  
  
    

  		




  322
  
  
    
--


  		




  323
  208
  
    
-- Table structure for table "projects"


  		




  324
  209
  
    
--


  		




  325
  210
  
    

  		




  ......




  350
  235
  
    
CREATE TABLE "stems" (


  		




  351
  236
  
    
  "stem_id" integer NOT NULL,


  		




  352
  237
  
    
  "origrecord_id_stems" integer DEFAULT NULL,


  		




  353
  
  
    
  "PlotObsID" integer DEFAULT NULL,


  		




  
  238
  
    
  "plotobs_id" integer DEFAULT NULL,


  		




  354
  239
  
    
  "NoInd" integer DEFAULT '0',


  		




  355
  240
  
    
  "basal_diam" double precision DEFAULT NULL,


  		




  356
  241
  
    
  "stem_dbh" double precision DEFAULT NULL,


  		




  ......




  365
  250
  
    
  "stem_liana_infestation" varchar(50) DEFAULT NULL,


  		




  366
  251
  
    
  "tmp_del" char(3) DEFAULT NULL,


  		




  367
  252
  
    
  PRIMARY KEY ("stem_id"),


  		




  368
  
  
    
  /*KEY "plotobs_id" ("PlotObsID")*/CHECK (true)


  		




  
  253
  
    
  /*KEY "plotobs_id" ("plotobs_id")*/CHECK (true),


  		




  
  254
  
    
  /*KEY "tmp_del" ("tmp_del")*/CHECK (true)


  		




  369
  255
  
    
);


  		




  370
  256
  
    
/*!40101 SET character_set_client = @saved_cs_client */;


  		




  371
  257
  
    
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;


  		




  ......




  378
  264
  
    
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;


  		




  379
  265
  
    
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;


  		




  380
  266
  
    

  		




  381
  
  
    
-- Dump completed on 2012-11-14 15:48:54


  		




  
  267
  
    
-- Dump completed on 2013-03-15  8:43:39


  		












  

    
      inputs/SALVIAS/salvias_users.schema.sql
    
  





  1
  1
  
    
SET standard_conforming_strings = off;


  		




  2
  2
  
    
SET escape_string_warning = off;


  		




  3
  
  
    
-- MySQL dump 10.13  Distrib 5.5.28, for debian-linux-gnu (x86_64)


  		




  
  3
  
    
-- MySQL dump 10.13  Distrib 5.5.29, for debian-linux-gnu (x86_64)


  		




  4
  4
  
    
--


  		




  5
  5
  
    
-- Host: localhost    Database: salvias_users


  		




  6
  6
  
    
-- ------------------------------------------------------


  		




  7
  
  
    
-- Server version	5.5.28-0ubuntu0.12.04.3


  		




  
  7
  
    
-- Server version	5.5.29-0ubuntu0.12.04.2


  		




  8
  8
  
    

  		




  9
  9
  
    
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;


  		




  10
  10
  
    
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;


  		




  ......




  362
  362
  
    
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;


  		




  363
  363
  
    
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;


  		




  364
  364
  
    

  		




  365
  
  
    
-- Dump completed on 2012-12-11  9:53:05


  		




  
  365
  
    
-- Dump completed on 2013-03-15  8:43:45


  		












  

    
      inputs/SALVIAS/salvias_plots.~.clean_up.sql
    
  





  1
  1
  
    
-- Restore original table names


  		




  2
  
  
    
DROP TABLE "plotMetadata"; -- name conflicts with PlotMetadata and has less data


  		




  3
  2
  
    
ALTER TABLE "PlotMetadata" RENAME TO "plotMetadata";


  		




  4
  3
  
    
ALTER TABLE "PlotObservations" RENAME TO "plotObservations";


  		




  5
  4
  
    

  		




  6
  5
  
    
-- Delete rows that do not satisfy foreign key constraints


  		




  7
  
  
    
DELETE FROM stems WHERE "PlotObsID" IN (


  		




  8
  
  
    
    SELECT stems."PlotObsID"


  		




  
  6
  
    
DELETE FROM stems WHERE "plotobs_id" IN (


  		




  
  7
  
    
    SELECT stems."plotobs_id"


  		




  9
  8
  
    
    FROM stems


  		




  10
  
  
    
    LEFT JOIN "plotObservations" ON "plotObservations"."PlotObsID" = stems."PlotObsID"


  		




  
  9
  
    
    LEFT JOIN "plotObservations" ON "plotObservations"."PlotObsID" = stems."plotobs_id"


  		




  11
  10
  
    
    WHERE "plotObservations"."PlotObsID" IS NULL


  		




  12
  11
  
    
);


  		




  13
  12
  
    

  		




  ......




  19
  18
  
    
CREATE INDEX ON "plotObservations" ("PlotID");


  		




  20
  19
  
    
ALTER TABLE "plotObservations" ADD FOREIGN KEY ("PlotID") REFERENCES "plotMetadata"("PlotID") ON UPDATE CASCADE ON DELETE CASCADE;


  		




  21
  20
  
    

  		




  22
  
  
    
CREATE INDEX ON stems ("PlotObsID");


  		




  23
  
  
    
ALTER TABLE stems ADD FOREIGN KEY ("PlotObsID") REFERENCES "plotObservations"("PlotObsID") ON UPDATE CASCADE ON DELETE CASCADE;


  		




  
  21
  
    
CREATE INDEX ON stems ("plotobs_id");


  		




  
  22
  
    
ALTER TABLE stems ADD FOREIGN KEY ("plotobs_id") REFERENCES "plotObservations"("PlotObsID") ON UPDATE CASCADE ON DELETE CASCADE;


  		




  24
  23
  
    

  		




  25
  24
  
    
-- Remove private data that should not be publicly visible


  		




  26
  25
  
    
DELETE FROM "plotMetadata" WHERE "AccessCode" = 1;


  		












  

    
      inputs/SALVIAS/stems/header.csv
    
  





  1
  
  
    
stem_id,origrecord_id_stems,PlotObsID,NoInd,basal_diam,stem_dbh,gentry_dbh,stem_notes,stem_tag1,stem_tag2,stem_height_m,stem_height_first_branch_m,stem_canopy_form,stem_canopy_position,stem_liana_infestation,tmp_del


  		




  
  1
  
    
stem_id,origrecord_id_stems,plotobs_id,NoInd,basal_diam,stem_dbh,gentry_dbh,stem_notes,stem_tag1,stem_tag2,stem_height_m,stem_height_first_branch_m,stem_canopy_form,stem_canopy_position,stem_liana_infestation,tmp_del


  		












  

    
      inputs/SALVIAS/stems/map.csv
    
  





  1
  1
  
    
SALVIAS,VegCore,Filter,Comments


  		




  2
  2
  
    
stem_id,stemID,,


  		




  3
  3
  
    
origrecord_id_stems,*origrecord_id_stems,,


  		




  4
  
  
    
PlotObsID,individualID,,


  		




  
  4
  
    
plotobs_id,individualID,,


  		




  5
  5
  
    
NoInd,stemCount,,


  		




  6
  6
  
    
basal_diam,basalDiameter_cm,,Assuming units are the same as for stem_dbh because the values are similar and they measure the same dimension


  		




  7
  7
  
    
stem_dbh,diameterBreastHeight_cm,,"Assuming units based on the units for plotObservations.intercept_cm, which measures the same dimension"


  		












  

    
      inputs/SALVIAS/stems/VegBIEN.csv
    
  





  1
  1
  
    
SALVIAS,VegBIEN:/_setDefault:[source_id/source/shortname/_env:[name=source]]/path/_simplifyPath:[next=parent_id]/path,Comments


  		




  2
  
  
    
PlotObsID,"/location/iscultivated/_or/1/_if[@name=""if TaxonOccurrence""]/cond/_exists",


  		




  3
  
  
    
PlotObsID,/location/locationevent/taxonoccurrence/aggregateoccurrence/plantobservation/sourceaccessioncode/_first/2,


  		




  
  2
  
    
plotobs_id,"/location/iscultivated/_or/1/_if[@name=""if TaxonOccurrence""]/cond/_exists",


  		




  
  3
  
    
plotobs_id,/location/locationevent/taxonoccurrence/aggregateoccurrence/plantobservation/sourceaccessioncode/_first/2,


  		




  4
  4
  
    
NoInd,/location/locationevent/taxonoccurrence/aggregateoccurrence/plantobservation/stemcount,


  		




  5
  5
  
    
basal_diam,/location/locationevent/taxonoccurrence/aggregateoccurrence/plantobservation/stemobservation/basaldiameter_m/_cm_to_m/value,Assuming units are the same as for stem_dbh because the values are similar and they measure the same dimension


  		




  6
  6
  
    
stem_canopy_form,"/location/locationevent/taxonoccurrence/aggregateoccurrence/plantobservation/stemobservation/definedvalue[*_id/userdefined[tablename=stemobservation,userdefinedname=canopyForm]]:[@fkey=tablerecord_id]/definedvalue",


  		




  ......




  14
  14
  
    
stem_id,/location/locationevent/taxonoccurrence/aggregateoccurrence/plantobservation/stemobservation/sourceaccessioncode,


  		




  15
  15
  
    
stem_tag2,/location/locationevent/taxonoccurrence/aggregateoccurrence/plantobservation/stemobservation/tag/_alt/1,"The second tag supercedes the first. Brad: See commend for tag1. Your mapping for tag2 looks correct. Probably both values would go here, only nested, with one superceding the other."


  		




  16
  16
  
    
stem_tag1,/location/locationevent/taxonoccurrence/aggregateoccurrence/plantobservation/stemobservation/tag/_alt/2,"The second tag supercedes the first. Brad: Another type of code, typically a number, used by the original data provider to indicate an individual tree. These are numbers on physical tags attached to the tree. Tag2 Is the same thing, only used if the first tag was lost. Obviously not a good system as it's possible a tree tag could be lost and changed more than once."


  		




  17
  
  
    
PlotObsID,"/location/locationevent/taxonoccurrence/authortaxoncode/_if[@name=""if in plot""]/then/_first/1/_if[@name=""if individual""]/cond/_exists",


  		




  
  17
  
    
plotobs_id,"/location/locationevent/taxonoccurrence/authortaxoncode/_if[@name=""if in plot""]/then/_first/1/_if[@name=""if individual""]/cond/_exists",


  		




  18
  18
  
    
stem_id,"/location/locationevent/taxonoccurrence/authortaxoncode/_if[@name=""if in plot""]/then/_first/1/_if[@name=""if individual""]/cond/_exists",


  		




  19
  19
  
    
stem_tag2,"/location/locationevent/taxonoccurrence/authortaxoncode/_if[@name=""if in plot""]/then/_first/2/_first/2/_alt/1","The second tag supercedes the first. Brad: See commend for tag1. Your mapping for tag2 looks correct. Probably both values would go here, only nested, with one superceding the other."


  		




  20
  20
  
    
stem_tag1,"/location/locationevent/taxonoccurrence/authortaxoncode/_if[@name=""if in plot""]/then/_first/2/_first/2/_alt/2","The second tag supercedes the first. Brad: Another type of code, typically a number, used by the original data provider to indicate an individual tree. These are numbers on physical tags attached to the tree. Tag2 Is the same thing, only used if the first tag was lost. Obviously not a good system as it's possible a tree tag could be lost and changed more than once."


  		




  21
  
  
    
PlotObsID,"/location/locationevent/taxonoccurrence/iscultivated/_alt/1/_if[@name=""if TaxonOccurrence""]/cond/_exists",


  		




  22
  
  
    
PlotObsID,"/location/locationevent/taxonoccurrence/sourceaccessioncode/_first/2/_if[@name=""if organism in AggregateOccurrence""]/else/_first/2",


  		




  
  21
  
    
plotobs_id,"/location/locationevent/taxonoccurrence/iscultivated/_alt/1/_if[@name=""if TaxonOccurrence""]/cond/_exists",


  		




  
  22
  
    
plotobs_id,"/location/locationevent/taxonoccurrence/sourceaccessioncode/_first/2/_if[@name=""if organism in AggregateOccurrence""]/else/_first/2",


  		




  23
  23
  
    
origrecord_id_stems,,** No join mapping for *origrecord_id_stems ** 


  		




  24
  24
  
    
tmp_del,,** No join mapping for *tmp_del ** 


  		












  

    
      inputs/SALVIAS/stems/test.xml.ref
    
  





  11
  11
  
    
                            <taxonoccurrence>


  		




  12
  12
  
    
                                <aggregateoccurrence>


  		




  13
  13
  
    
                                    <plantobservation>


  		




  14
  
  
    
                                        <sourceaccessioncode>$PlotObsID</sourceaccessioncode>


  		




  
  14
  
    
                                        <sourceaccessioncode>$plotobs_id</sourceaccessioncode>


  		




  15
  15
  
    
                                        <stemcount>$NoInd</stemcount>


  		




  16
  16
  
    
                                        <stemobservation>


  		




  17
  17
  
    
                                            <basaldiameter_m><_cm_to_m><value>$basal_diam</value></_cm_to_m></basaldiameter_m>


  		




  ......




  73
  73
  
    
                                        </stemobservation>


  		




  74
  74
  
    
                                    </plantobservation>


  		




  75
  75
  
    
                                </aggregateoccurrence>


  		




  76
  
  
    
                                <sourceaccessioncode>$PlotObsID</sourceaccessioncode>


  		




  
  76
  
    
                                <sourceaccessioncode>$plotobs_id</sourceaccessioncode>


  		




  77
  77
  
    
                            </taxonoccurrence>


  		




  78
  78
  
    
                        </locationevent>


  		




  79
  79
  
    
                    </location>


  		












  

    
      inputs/SALVIAS/stems/new_terms.csv
    
  





  1
  
  
    
PlotObsID,individualID,,


  		




  
  1
  
    
plotobs_id,individualID,,


  		




  2
  2
  
    
NoInd,stemCount,,


  		




  3
  3
  
    
stem_tag1,tag,/_alt/2,"The second tag supercedes the first. Brad: Another type of code, typically a number, used by the original data provider to indicate an individual tree. These are numbers on physical tags attached to the tree. Tag2 Is the same thing, only used if the first tag was lost. Obviously not a good system as it's possible a tree tag could be lost and changed more than once."


  		




  4
  4
  
    
stem_height_m,height_m,,"""Total height of this stem only, in meters"" (stem_height_m definition at < http://salvias.net/Documents/salvias_data_dictionary.html#Plot+data>). Brad: Same as for height, but applies to individuals stems, not trees. Rare."


  		












  

    
      inputs/SALVIAS/_MySQL/salvias_plots.schema.sql
    
  





  1
  
  
    
-- MySQL dump 10.13  Distrib 5.5.28, for debian-linux-gnu (x86_64)


  		




  
  1
  
    
-- MySQL dump 10.13  Distrib 5.5.29, for debian-linux-gnu (x86_64)


  		




  2
  2
  
    
--


  		




  3
  3
  
    
-- Host: localhost    Database: salvias_plots


  		




  4
  4
  
    
-- ------------------------------------------------------


  		




  5
  
  
    
-- Server version	5.5.28-0ubuntu0.12.04.2


  		




  
  5
  
    
-- Server version	5.5.29-0ubuntu0.12.04.2


  		




  6
  6
  
    

  		




  7
  7
  
    
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;


  		




  8
  8
  
    
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;


  		




  ......




  175
  175
  
    
/*!40101 SET character_set_client = @saved_cs_client */;


  		




  176
  176
  
    

  		




  177
  177
  
    
--


  		




  178
  
  
    
-- Table structure for table "log_plot_download"


  		




  179
  
  
    
--


  		




  180
  
  
    

  		




  181
  
  
    
DROP TABLE IF EXISTS "log_plot_download";


  		




  182
  
  
    
/*!40101 SET @saved_cs_client     = @@character_set_client */;


  		




  183
  
  
    
/*!40101 SET character_set_client = utf8 */;


  		




  184
  
  
    
CREATE TABLE "log_plot_download" (


  		




  185
  
  
    
  "EnteredBy" varchar(14) NOT NULL DEFAULT '',


  		




  186
  
  
    
  "download_timestamp" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,


  		




  187
  
  
    
  "ip" varchar(21) NOT NULL DEFAULT '',


  		




  188
  
  
    
  "plot_id" int(11) DEFAULT NULL,


  		




  189
  
  
    
  "plot_administrator" varchar(30) NOT NULL DEFAULT '',


  		




  190
  
  
    
  "project_id" int(11) NOT NULL DEFAULT '0',


  		




  191
  
  
    
  PRIMARY KEY ("EnteredBy","download_timestamp"),


  		




  192
  
  
    
  KEY "plot_administrator" ("plot_administrator","project_id")


  		




  193
  
  
    
);


  		




  194
  
  
    
/*!40101 SET character_set_client = @saved_cs_client */;


  		




  195
  
  
    

  		




  196
  
  
    
--


  		




  197
  178
  
    
-- Table structure for table "lookup_MethodCode"


  		




  198
  179
  
    
--


  		




  199
  180
  
    

  		




  ......




  222
  203
  
    
/*!40101 SET character_set_client = @saved_cs_client */;


  		




  223
  204
  
    

  		




  224
  205
  
    
--


  		




  225
  
  
    
-- Table structure for table "permissions_plot_user"


  		




  226
  
  
    
--


  		




  227
  
  
    

  		




  228
  
  
    
DROP TABLE IF EXISTS "permissions_plot_user";


  		




  229
  
  
    
/*!40101 SET @saved_cs_client     = @@character_set_client */;


  		




  230
  
  
    
/*!40101 SET character_set_client = utf8 */;


  		




  231
  
  
    
CREATE TABLE "permissions_plot_user" (


  		




  232
  
  
    
  "plot_id" int(11) NOT NULL DEFAULT '0',


  		




  233
  
  
    
  "username" varchar(50) NOT NULL DEFAULT '',


  		




  234
  
  
    
  "access_level" mediumint(9) NOT NULL DEFAULT '0',


  		




  235
  
  
    
  PRIMARY KEY ("plot_id","username"),


  		




  236
  
  
    
  KEY "access_level" ("access_level")


  		




  237
  
  
    
);


  		




  238
  
  
    
/*!40101 SET character_set_client = @saved_cs_client */;


  		




  239
  
  
    

  		




  240
  
  
    
--


  		




  241
  
  
    
-- Table structure for table "plotMetadata"


  		




  242
  
  
    
--


  		




  243
  
  
    

  		




  244
  
  
    
DROP TABLE IF EXISTS "plotMetadata";


  		




  245
  
  
    
/*!40101 SET @saved_cs_client     = @@character_set_client */;


  		




  246
  
  
    
/*!40101 SET character_set_client = utf8 */;


  		




  247
  
  
    
CREATE TABLE "plotMetadata" (


  		




  248
  
  
    
  "PlotID" int(11) NOT NULL,


  		




  249
  
  
    
  "orig_filename" longtext,


  		




  250
  
  
    
  "AccessCode" smallint(6) DEFAULT NULL,


  		




  251
  
  
    
  "project_id" int(11) DEFAULT NULL,


  		




  252
  
  
    
  "PrimOwnerID" varchar(30) DEFAULT NULL,


  		




  253
  
  
    
  "SiteCode" varchar(100) DEFAULT NULL,


  		




  254
  
  
    
  "SiteName" varchar(150) DEFAULT NULL,


  		




  255
  
  
    
  "new_world" char(3) DEFAULT NULL,


  		




  256
  
  
    
  "MajorGeo" varchar(255) DEFAULT NULL,


  		




  257
  
  
    
  "Country" varchar(80) DEFAULT NULL,


  		




  258
  
  
    
  "PolDiv1" varchar(50) DEFAULT NULL,


  		




  259
  
  
    
  "pol1_type" varchar(25) DEFAULT NULL,


  		




  260
  
  
    
  "pol2" varchar(50) DEFAULT NULL,


  		




  261
  
  
    
  "pol2_type" varchar(25) DEFAULT NULL,


  		




  262
  
  
    
  "lat_string" varchar(20) DEFAULT NULL,


  		




  263
  
  
    
  "long_string" varchar(20) DEFAULT NULL,


  		




  264
  
  
    
  "LatDec" float DEFAULT NULL,


  		




  265
  
  
    
  "LongDec" float DEFAULT NULL,


  		




  266
  
  
    
  "lat_long_accuracy" varchar(255) DEFAULT NULL,


  		




  267
  
  
    
  "Elev" float DEFAULT NULL,


  		




  268
  
  
    
  "elev_max_m" float DEFAULT NULL,


  		




  269
  
  
    
  "elev_min_m" float DEFAULT NULL,


  		




  270
  
  
    
  "Precip" float DEFAULT NULL,


  		




  271
  
  
    
  "ElevSource" float DEFAULT NULL,


  		




  272
  
  
    
  "Temp" float DEFAULT NULL,


  		




  273
  
  
    
  "PrecipSource" float DEFAULT NULL,


  		




  274
  
  
    
  "TempSource" varchar(255) DEFAULT NULL,


  		




  275
  
  
    
  "bearing" double unsigned DEFAULT NULL,


  		




  276
  
  
    
  "slope_aspect" varchar(10) DEFAULT NULL,


  		




  277
  
  
    
  "slope_gradient" smallint(6) DEFAULT NULL,


  		




  278
  
  
    
  "clay_percent" float unsigned DEFAULT NULL,


  		




  279
  
  
    
  "silt_percent" float unsigned DEFAULT NULL,


  		




  280
  
  
    
  "sand_percent" float unsigned DEFAULT NULL,


  		




  281
  
  
    
  "pH" float unsigned DEFAULT NULL,


  		




  282
  
  
    
  "soil_N" varchar(20) DEFAULT NULL,


  		




  283
  
  
    
  "soil_P" varchar(20) DEFAULT NULL,


  		




  284
  
  
    
  "soil_C" varchar(20) DEFAULT NULL,


  		




  285
  
  
    
  "soil_K" varchar(20) DEFAULT NULL,


  		




  286
  
  
    
  "soil_Mg" varchar(20) DEFAULT NULL,


  		




  287
  
  
    
  "soil_Ca" varchar(20) DEFAULT NULL,


  		




  288
  
  
    
  "soil_Na" varchar(20) DEFAULT NULL,


  		




  289
  
  
    
  "soil_acidity" varchar(20) DEFAULT NULL,


  		




  290
  
  
    
  "soil_base" varchar(20) DEFAULT NULL,


  		




  291
  
  
    
  "soil_cation_cap" varchar(20) DEFAULT NULL,


  		




  292
  
  
    
  "soil_conductivity" varchar(20) DEFAULT NULL,


  		




  293
  
  
    
  "organic_percent" float unsigned DEFAULT NULL,


  		




  294
  
  
    
  "soil_texture" varchar(20) DEFAULT NULL,


  		




  295
  
  
    
  "RevisionComments" varchar(255) DEFAULT NULL,


  		




  296
  
  
    
  "Locality_Description" varchar(250) DEFAULT NULL,


  		




  297
  
  
    
  "topography_desc" varchar(250) DEFAULT NULL,


  		




  298
  
  
    
  "vegetation_1" varchar(255) DEFAULT NULL,


  		




  299
  
  
    
  "vegetation_2" varchar(255) DEFAULT NULL,


  		




  300
  
  
    
  "Habitat" varchar(250) DEFAULT NULL,


  		




  301
  
  
    
  "life_zone_code" varchar(10) DEFAULT NULL,


  		




  302
  
  
    
  "life_zone" varchar(150) DEFAULT NULL,


  		




  303
  
  
    
  "PlotMethod" varchar(250) DEFAULT NULL,


  		




  304
  
  
    
  "MethodCode" smallint(6) DEFAULT NULL,


  		




  305
  
  
    
  "plot_area_ha" float DEFAULT NULL,


  		




  306
  
  
    
  "recensused" char(3) DEFAULT NULL,


  		




  307
  
  
    
  "date_start" varchar(20) DEFAULT NULL,


  		




  308
  
  
    
  "date_finish" varchar(20) DEFAULT NULL,


  		




  309
  
  
    
  "plot_administrator" varchar(30) DEFAULT NULL,


  		




  310
  
  
    
  "plot_notes" longtext,


  		




  311
  
  
    
  "tmp_del" char(3) DEFAULT NULL,


  		




  312
  
  
    
  PRIMARY KEY ("PlotID"),


  		




  313
  
  
    
  KEY "Country" ("Country"),


  		




  314
  
  
    
  KEY "PrimOwnerID" ("PrimOwnerID"),


  		




  315
  
  
    
  KEY "plot_administrator" ("plot_administrator"),


  		




  316
  
  
    
  KEY "project_id" ("project_id")


  		




  317
  
  
    
);


  		




  318
  
  
    
/*!40101 SET character_set_client = @saved_cs_client */;


  		




  319
  
  
    

  		




  320
  
  
    
--


  		




  321
  206
  
    
-- Table structure for table "projects"


  		




  322
  207
  
    
--


  		




  323
  208
  
    

  		




  ......




  348
  233
  
    
CREATE TABLE "stems" (


  		




  349
  234
  
    
  "stem_id" int(11) unsigned NOT NULL,


  		




  350
  235
  
    
  "origrecord_id_stems" int(11) unsigned DEFAULT NULL,


  		




  351
  
  
    
  "PlotObsID" int(11) DEFAULT NULL,


  		




  
  236
  
    
  "plotobs_id" int(11) DEFAULT NULL,


  		




  352
  237
  
    
  "NoInd" int(11) DEFAULT '0',


  		




  353
  238
  
    
  "basal_diam" double DEFAULT NULL,


  		




  354
  239
  
    
  "stem_dbh" double DEFAULT NULL,


  		




  ......




  363
  248
  
    
  "stem_liana_infestation" varchar(50) DEFAULT NULL,


  		




  364
  249
  
    
  "tmp_del" char(3) DEFAULT NULL,


  		




  365
  250
  
    
  PRIMARY KEY ("stem_id"),


  		




  366
  
  
    
  KEY "plotobs_id" ("PlotObsID")


  		




  
  251
  
    
  KEY "plotobs_id" ("plotobs_id"),


  		




  
  252
  
    
  KEY "tmp_del" ("tmp_del")


  		




  367
  253
  
    
);


  		




  368
  254
  
    
/*!40101 SET character_set_client = @saved_cs_client */;


  		




  369
  255
  
    
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;


  		




  ......




  376
  262
  
    
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;


  		




  377
  263
  
    
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;


  		




  378
  264
  
    

  		




  379
  
  
    
-- Dump completed on 2012-11-14 15:48:54


  		




  
  265
  
    
-- Dump completed on 2013-03-15  8:43:39


  		












  

    
      inputs/SALVIAS/_MySQL/salvias_users.schema.sql
    
  





  1
  
  
    
-- MySQL dump 10.13  Distrib 5.5.28, for debian-linux-gnu (x86_64)


  		




  
  1
  
    
-- MySQL dump 10.13  Distrib 5.5.29, for debian-linux-gnu (x86_64)


  		




  2
  2
  
    
--


  		




  3
  3
  
    
-- Host: localhost    Database: salvias_users


  		




  4
  4
  
    
-- ------------------------------------------------------


  		




  5
  
  
    
-- Server version	5.5.28-0ubuntu0.12.04.3


  		




  
  5
  
    
-- Server version	5.5.29-0ubuntu0.12.04.2


  		




  6
  6
  
    

  		




  7
  7
  
    
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;


  		




  8
  8
  
    
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;


  		




  ......




  360
  360
  
    
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;


  		




  361
  361
  
    
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;


  		




  362
  362
  
    

  		




  363
  
  
    
-- Dump completed on 2012-12-11  9:53:05


  		




  
  363
  
    
-- Dump completed on 2013-03-15  8:43:45


  		












Also available in:  Unified diff