Project

General

Profile

« Previous | Next » 

Revision 1549

Added by Aaron Marcuse-Kubitza almost 13 years ago

inputs/CTFS: Added remaining non-data src files

View differences:

inputs/CTFS/src/CTFS_conversion_bci.php
1 
<html><body style="background-color:yellow;">
2 
<h1 style="text-align:center;"> CTFS to VegX conversion </h1>
3 

  		




  
  4
  
    
<?php


  		




  
  5
  
    
$dbhost = 'localhost';


  		




  
  6
  
    
$dbuser = 's_sramteke';


  		




  
  7
  
    
$dbpass = 'S6a52mxX';


  		




  
  8
  
    
echo date("m/d/y :H:i:s",time());


  		




  
  9
  
    
$conn = mysql_connect($dbhost,$dbuser,$dbpass) or die ('Error');


  		




  
  10
  
    

  		




  
  11
  
    
$dbname = 's_sramteke';


  		




  
  12
  
    
mysql_select_db($dbname);


  		




  
  13
  
    

  		




  
  14
  
    
//$sql = "SELECT * FROM `DFtemp`";


  		




  
  15
  
    
//$result = mysql_query($sql);


  		




  
  16
  
    

  		




  
  17
  
    
$xmlHandle = fopen("new_Xml_CTFS_row.xml","w");


  		




  
  18
  
    

  		




  
  19
  
    

  		




  
  20
  
    
$writer = new XMLWriter();


  		




  
  21
  
    
$writer->openMemory();


  		




  
  22
  
    
$writer->setIndent(true);


  		




  
  23
  
    
$writer->setIndentString("    ");


  		




  
  24
  
    
$writer->startDocument('1.0', 'UTF-8');


  		




  
  25
  
    

  		




  
  26
  
    
$writer->startElement('vegX');


  		




  
  27
  
    
$writer->writeAttribute("xsi:noNamespaceSchemaLocation", "C:/Development/Utilities/LCR.Utilities.DataImport/LCR.Utilities.MappingTool/Resources/VEGX15~1.2/veg.xsd");


  		




  
  28
  
    
$writer->writeAttribute("xmlns:acc", "eml://ecoinformatics.org/access-2.0.1");


  		




  
  29
  
    
$writer->writeAttribute("xmlns:cov", "eml://ecoinformatics.org/coverage-2.0.1");


  		




  
  30
  
    
$writer->writeAttribute("xmlns:doc", "eml://ecoinformatics.org/documentation-2.0.1");


  		




  
  31
  
    
$writer->writeAttribute("xmlns:lit", "eml://ecoinformatics.org/literature-2.0.1");


  		




  
  32
  
    
$writer->writeAttribute("xmlns:party","eml://ecoinformatics.org/party-2.0.1");


  		




  
  33
  
    
$writer->writeAttribute("xmlns:proj","eml://ecoinformatics.org/project-2.0.1");


  		




  
  34
  
    
$writer->writeAttribute("xmlns:res","eml://ecoinformatics.org/resource-2.0.1");


  		




  
  35
  
    
$writer->writeAttribute("xmlns:txt","eml://ecoinformatics.org/text-2.0.1");


  		




  
  36
  
    
$writer->writeAttribute("xmlns:dwe","http://rs.tdwg.org/dwc/dwelement/");


  		




  
  37
  
    
$writer->writeAttribute("xmlns:dwg","http://rs.tdwg.org/dwc/geospatial/");


  		




  
  38
  
    
$writer->writeAttribute("xmlns:misc","http://www.bfn.de/misc-1.0.1");


  		




  
  39
  
    
$writer->writeAttribute("xmlns:org","http://www.bfn.de/organismobservation-1.0.1");


  		




  
  40
  
    
$writer->writeAttribute("xmlns:plot","http://www.bfn.de/plot-1.0.1");


  		




  
  41
  
    
$writer->writeAttribute("xmlns:obs","http://www.bfn.de/plotobservation-1.0.1");


  		




  
  42
  
    
$writer->writeAttribute("xmlns:tcs","http://www.tdwg.org/schemas/tcs/1.01");


  		




  
  43
  
    
$writer->writeAttribute("xmlns:xsi","http://www.w3.org/2001/XMLSchema-instance");


  		




  
  44
  
    
$filename = "new_Xml_CTFS_row.xml";


  		




  
  45
  
    

  		




  
  46
  
    
$file = $writer->outputMemory();


  		




  
  47
  
    

  		




  
  48
  
    
file_put_contents($filename,$file);


  		




  
  49
  
    

  		




  
  50
  
    
$i=0;


  		




  
  51
  
    

  		




  
  52
  
    
/*while($row = mysql_fetch_row($result))


  		




  
  53
  
    
{


  		




  
  54
  
    
$writer->startElement('projects');


  		




  
  55
  
    

  		




  
  56
  
    
$writer->startElement('project');


  		




  
  57
  
    
$writer->writeElement('title','Project 1');


  		




  
  58
  
    
$writer->endElement();


  		




  
  59
  
    
$writer->endElement();


  		




  
  60
  
    

  		




  
  61
  
    

  		




  
  62
  
    
}*/


  		




  
  63
  
    

  		




  
  64
  
    
$sql = "SELECT distinct `PlotID`, `Plot`, `QuadratID`, `QuadratName`, `gx`, `gy` FROM `DFtemp` WHERE plot = 'bci'";


  		




  
  65
  
    

  		




  
  66
  
    
$result = mysql_query($sql);


  		




  
  67
  
    
$writer->startElement('plots');


  		




  
  68
  
    

  		




  
  69
  
    
while($row = mysql_fetch_row($result))


  		




  
  70
  
    
{


  		




  
  71
  
    

  		




  
  72
  
    
$writer->startElement('plot');


  		




  
  73
  
    
	$writer->writeElement('plotUniqueIdentifier',$row[0]);


  		




  
  74
  
    
	$writer->writeElement('plotName',$row[1]);


  		




  
  75
  
    

  		




  
  76
  
    
	$writer->startElement('geospatial');


  		




  
  77
  
    
		$writer->writeElement('dwg:VerbatimLatitude',$row[4]);


  		




  
  78
  
    
		$writer->writeElement('dwg:VerbatimLongitude',$row[5]);


  		




  
  79
  
    
	$writer->endElement();


  		




  
  80
  
    

  		




  
  81
  
    
$writer->endElement();


  		




  
  82
  
    

  		




  
  83
  
    
$writer->startElement('plot');


  		




  
  84
  
    
	$writer->writeElement('plotUniqueIdentifier',$row[2]);


  		




  
  85
  
    
	$writer->writeElement('plotName',$row[3]);


  		




  
  86
  
    

  		




  
  87
  
    
	$writer->startElement('relatedPlot');


  		




  
  88
  
    
		$writer->startElement('relatedPlot');


  		




  
  89
  
    
			$writer->writeElement('relatedPlotID',$row[0]);


  		




  
  90
  
    
			$writer->writeElement('plotRelationship',"Quadrant");


  		




  
  91
  
    
		$writer->endElement();


  		




  
  92
  
    
	$writer->endElement();


  		




  
  93
  
    
$writer->endElement();


  		




  
  94
  
    
$i++;


  		




  
  95
  
    
	


  		




  
  96
  
    
	if($i == 1000)


  		




  
  97
  
    
	{


  		




  
  98
  
    
		file_put_contents('new_Xml_CTFS_row.xml', $writer->flush(true), FILE_APPEND);


  		




  
  99
  
    
		$i = 0;


  		




  
  100
  
    
	}


  		




  
  101
  
    
}


  		




  
  102
  
    
$writer->endElement();


  		




  
  103
  
    

  		




  
  104
  
    
file_put_contents($filename, $writer->flush(true), FILE_APPEND);


  		




  
  105
  
    

  		




  
  106
  
    
$i=0;


  		




  
  107
  
    

  		




  
  108
  
    
$sql = "select MeasureID,CensusID,QuadratID, PlotCensusNumber, ExactDate,PLotID FROM `DFtemp` WHERE plot = 'bci'";


  		




  
  109
  
    
$result = mysql_query($sql);


  		




  
  110
  
    

  		




  
  111
  
    
$writer->startElement('plotObservations');


  		




  
  112
  
    
while($row = mysql_fetch_row($result))


  		




  
  113
  
    
{


  		




  
  114
  
    
$writer->startElement('plotObservation');


  		




  
  115
  
    
	$writer->writeAttribute("id",$row[0]);


  		




  
  116
  
    
	$writer->writeElement('plotUniqueIdentifierID',$row[5]);


  		




  
  117
  
    
	$writer->writeElement('obsStartDate',$row[4]);


  		




  
  118
  
    
	$writer->startElement('simpleUserdefined');


  		




  
  119
  
    
		$writer->writeElement('name',"CensusID");


  		




  
  120
  
    
		$writer->writeElement('value',$row[1]);


  		




  
  121
  
    
		$writer->writeElement('methodID',"CensusID");


  		




  
  122
  
    
	$writer->endElement();


  		




  
  123
  
    
$writer->endElement();


  		




  
  124
  
    

  		




  
  125
  
    
$writer->startElement('plotObservation');


  		




  
  126
  
    
	$writer->writeAttribute("id",$row[2]);


  		




  
  127
  
    
	$writer->writeElement('plotUniqueIdentifierID',$row[2]);


  		




  
  128
  
    
	$writer->writeElement('obsStartDate',$row[4]);


  		




  
  129
  
    
	$writer->startElement('simpleUserdefined');


  		




  
  130
  
    
		$writer->writeElement('name',"CensusID");


  		




  
  131
  
    
		$writer->writeElement('value',$row[1]);


  		




  
  132
  
    
		$writer->writeElement('methodID',"CensusID");


  		




  
  133
  
    
	$writer->endElement();


  		




  
  134
  
    
$writer->endElement();


  		




  
  135
  
    

  		




  
  136
  
    
$i++;


  		




  
  137
  
    
	


  		




  
  138
  
    
	if($i == 1000)


  		




  
  139
  
    
	{


  		




  
  140
  
    
		file_put_contents('new_Xml_CTFS_row.xml', $writer->flush(true), FILE_APPEND);


  		




  
  141
  
    
		$i = 0;


  		




  
  142
  
    
	}


  		




  
  143
  
    

  		




  
  144
  
    
}


  		




  
  145
  
    
$writer->endElement();


  		




  
  146
  
    

  		




  
  147
  
    
file_put_contents($filename, $writer->flush(true), FILE_APPEND);


  		




  
  148
  
    

  		




  
  149
  
    
$i=0;


  		




  
  150
  
    

  		




  
  151
  
    
$sql = " select Genus,Family,SpeciesName,GenusSpecies,SpeciesID,Mnemonic FROM `DFtemp` WHERE plot = 'bci'";


  		




  
  152
  
    

  		




  
  153
  
    
$result = mysql_query($sql);


  		




  
  154
  
    
$writer->startElement('taxonConcepts');


  		




  
  155
  
    
while($row = mysql_fetch_row($result))


  		




  
  156
  
    
{


  		




  
  157
  
    
$writer->startElement('taxonConcept');


  		




  
  158
  
    
	$writer->writeAttribute("id",$row[0]);


  		




  
  159
  
    
	$writer->writeElement('tcs:Name',$row[0]);


  		




  
  160
  
    
	$writer->writeAttribute("scientific","false");


  		




  
  161
  
    
	//$writer->endElement();


  		




  
  162
  
    
	$writer->writeElement('tcs:Rank',"Genus");


  		




  
  163
  
    
$writer->endElement();


  		




  
  164
  
    

  		




  
  165
  
    
$writer->startElement('taxonConcept');


  		




  
  166
  
    
	$writer->writeAttribute("id",$row[1]);


  		




  
  167
  
    
	$writer->writeElement('tcs:Name',$row[1]);


  		




  
  168
  
    
	$writer->writeAttribute("scientific","false");


  		




  
  169
  
    
	//$writer->endElement();


  		




  
  170
  
    
	$writer->writeElement('tcs:Rank',"Family");


  		




  
  171
  
    
	


  		




  
  172
  
    
	$writer->startElement('tcs:TaxonRelationships');


  		




  
  173
  
    
	$writer->startElement('tcs:TaxonRelationship');


  		




  
  174
  
    
		$writer->writeAttribute("type","is child taxon of");


  		




  
  175
  
    
		$writer->writeElement('tcs:ToTaxonConcept',$row[0]);


  		




  
  176
  
    
	$writer->endElement();


  		




  
  177
  
    
	$writer->endElement();


  		




  
  178
  
    
$writer->endElement();


  		




  
  179
  
    

  		




  
  180
  
    
$writer->startElement('taxonConcept');


  		




  
  181
  
    
	$writer->writeAttribute("id",$row[2]);


  		




  
  182
  
    
	$writer->startElement('tcs:Name');


  		




  
  183
  
    
		$writer->startAttribute("scientific");


  		




  
  184
  
    
			$writer->text("false");


  		




  
  185
  
    
		$writer->endAttribute();


  		




  
  186
  
    
		$writer->text($row[2]);


  		




  
  187
  
    
	$writer->endElement();


  		




  
  188
  
    
	$writer->writeElement('tcs:Rank',"Species");


  		




  
  189
  
    
	


  		




  
  190
  
    
	$writer->startElement('tcs:TaxonRelationships');


  		




  
  191
  
    
	$writer->startElement('tcs:TaxonRelationship');


  		




  
  192
  
    
		$writer->writeAttribute("type","is child taxon of");


  		




  
  193
  
    
		$writer->writeElement('tcs:ToTaxonConcept',$row[0]);


  		




  
  194
  
    
	$writer->endElement();


  		




  
  195
  
    
	$writer->startElement('tcs:TaxonRelationship');


  		




  
  196
  
    
		$writer->writeAttribute("type","is child taxon of");


  		




  
  197
  
    
		$writer->writeElement('tcs:ToTaxonConcept',$row[1]);


  		




  
  198
  
    
	$writer->endElement();	


  		




  
  199
  
    
	$writer->endElement();


  		




  
  200
  
    
$writer->endElement();


  		




  
  201
  
    

  		




  
  202
  
    
$i++;


  		




  
  203
  
    
	


  		




  
  204
  
    
	if($i == 1000)


  		




  
  205
  
    
	{


  		




  
  206
  
    
		file_put_contents('new_Xml_CTFS_row.xml', $writer->flush(true), FILE_APPEND);


  		




  
  207
  
    
		$i = 0;


  		




  
  208
  
    
	}


  		




  
  209
  
    
	


  		




  
  210
  
    
}


  		




  
  211
  
    
$writer->endElement();


  		




  
  212
  
    

  		




  
  213
  
    
$i=0;


  		




  
  214
  
    

  		




  
  215
  
    
file_put_contents('new_Xml_CTFS_row.xml', $writer->flush(true), FILE_APPEND);


  		




  
  216
  
    

  		




  
  217
  
    
$sql = "select SpeciesID,Mnemonic FROM `DFtemp` WHERE plot = 'bci'";


  		




  
  218
  
    

  		




  
  219
  
    
$result = mysql_query($sql);


  		




  
  220
  
    
$writer->startElement('taxonNameUsageConcepts');


  		




  
  221
  
    
while($row = mysql_fetch_row($result))


  		




  
  222
  
    
{


  		




  
  223
  
    
$writer->startElement('taxonNameUsageConcept');


  		




  
  224
  
    
	$writer->writeAttribute("id",$row[0]);


  		




  
  225
  
    
	$writer->writeElement('authorName');


  		




  
  226
  
    
	$writer->writeElement('authorCode',$row[1]);


  		




  
  227
  
    
$writer->endElement();


  		




  
  228
  
    

  		




  
  229
  
    
$i++;


  		




  
  230
  
    
	


  		




  
  231
  
    
	if($i == 1000)


  		




  
  232
  
    
	{


  		




  
  233
  
    
		file_put_contents('new_Xml_CTFS_row.xml', $writer->flush(true), FILE_APPEND);


  		




  
  234
  
    
		$i = 0;


  		




  
  235
  
    
	}


  		




  
  236
  
    

  		




  
  237
  
    
}


  		




  
  238
  
    
$writer->endElement();


  		




  
  239
  
    

  		




  
  240
  
    
$i=0;


  		




  
  241
  
    

  		




  
  242
  
    
file_put_contents('new_Xml_CTFS_row.xml', $writer->flush(true), FILE_APPEND);


  		




  
  243
  
    

  		




  
  244
  
    
$sql = "select MeasureID,PlotID,x,y,TreeID,Tag,StemId,StemTag,DBH,HOM,ListOfTSM,status FROM `DFtemp` WHERE plot = 'bci'";


  		




  
  245
  
    

  		




  
  246
  
    
$result = mysql_query($sql);


  		




  
  247
  
    
$writer->startElement('individualOrganismObservations');


  		




  
  248
  
    
while($row = mysql_fetch_row($result))


  		




  
  249
  
    
{


  		




  
  250
  
    
$writer->startElement('individualOrganismObservation');


  		




  
  251
  
    
	$writer->writeAttribute("id",$row[0]);


  		




  
  252
  
    
	$writer->writeElement('plotObservationID',$row[0]);


  		




  
  253
  
    
	$writer->writeElement('individualOrganismID',$row[4]);


  		




  
  254
  
    
	$writer->startElement('diameterBaseDistance');


  		




  
  255
  
    
	$writer->startElement('diameter');


  		




  
  256
  
    
	$writer->writeElement('value',$row[8]);


  		




  
  257
  
    
	$writer->writeElement('attributeID',"DBH");


  		




  
  258
  
    
	$writer->endElement();


  		




  
  259
  
    
	$writer->startElement('baseDistance');


  		




  
  260
  
    
	$writer->writeElement('value',$row[9]);


  		




  
  261
  
    
	$writer->writeElement('attributeID',"HOM");


  		




  
  262
  
    
	$writer->endElement();


  		




  
  263
  
    
	$writer->endElement();


  		




  
  264
  
    
	


  		




  
  265
  
    
	$writer->startElement("relatedIndividual");


  		




  
  266
  
    
	$writer->startElement("relatedItem");


  		




  
  267
  
    
	$writer->writeElement('relatedItemID',$row[6]);


  		




  
  268
  
    
	$writer->writeElement('itemRelationship',"Stem");


  		




  
  269
  
    
	$writer->endElement();


  		




  
  270
  
    
	$writer->endElement();


  		




  
  271
  
    
	


  		




  
  272
  
    
	$writer->startElement('relativePlotPosition');


  		




  
  273
  
    
	$writer->writeElement('relativeX',$row[2]);


  		




  
  274
  
    
	$writer->writeElement('relativeY',$row[3]);


  		




  
  275
  
    
	$writer->endElement();


  		




  
  276
  
    
	


  		




  
  277
  
    
	$str = $row[10];


  		




  
  278
  
    
	while($str != "")


  		




  
  279
  
    
	{


  		




  
  280
  
    
		$pos = strpos($str,',');


  		




  
  281
  
    
		if($pos === false)


  		




  
  282
  
    
		{


  		




  
  283
  
    
			$TSMcode = $str;


  		




  
  284
  
    
			$str = "";


  		




  
  285
  
    
		}


  		




  
  286
  
    
		else


  		




  
  287
  
    
		{


  		




  
  288
  
    
			$TSMcode=substr($str,0,$pos);


  		




  
  289
  
    
			$str = substr($str,$pos+1);			


  		




  
  290
  
    
		}		


  		




  
  291
  
    
		//echo $TSMcode;


  		




  
  292
  
    
		$sqlt = "select status from attributes where attr = '" . $TSMcode . "'";


  		




  
  293
  
    
		//echo $sqlt;


  		




  
  294
  
    
		$resultt = mysql_query($sqlt);


  		




  
  295
  
    
		$rowt = mysql_fetch_row($resultt);


  		




  
  296
  
    
		//echo $rowt[0];


  		




  
  297
  
    
		


  		




  
  298
  
    
		$writer->startElement('simpleUserdefined');


  		




  
  299
  
    
		$writer->writeElement('name',$rowt[0]);


  		




  
  300
  
    
		$writer->writeElement('value',$TSMcode);


  		




  
  301
  
    
		$writer->writeElement('methodID',$TSMcode);


  		




  
  302
  
    
		$writer->endElement();


  		




  
  303
  
    
	}


  		




  
  304
  
    
	


  		




  
  305
  
    
	$writer->writeElement('individualOrganismHealth',$row[11]);


  		




  
  306
  
    
	


  		




  
  307
  
    
$writer->endElement();


  		




  
  308
  
    

  		




  
  309
  
    
$i++;


  		




  
  310
  
    
	


  		




  
  311
  
    
	if($i == 1000)


  		




  
  312
  
    
	{


  		




  
  313
  
    
		file_put_contents('new_Xml_CTFS_row.xml', $writer->flush(true), FILE_APPEND);


  		




  
  314
  
    
		$i = 0;


  		




  
  315
  
    
	}


  		




  
  316
  
    
}


  		




  
  317
  
    
$writer->endElement();


  		




  
  318
  
    
file_put_contents('new_Xml_CTFS_row.xml', $writer->flush(true), FILE_APPEND);


  		




  
  319
  
    

  		




  
  320
  
    
$sql = "select SpeciesID,TreeID,Tag,StemId,StemTag FROM `DFtemp` WHERE plot = 'bci'";


  		




  
  321
  
    

  		




  
  322
  
    
$i = 0;


  		




  
  323
  
    

  		




  
  324
  
    
$result = mysql_query($sql);


  		




  
  325
  
    
$writer->startElement('individualOrganisms');


  		




  
  326
  
    
while($row = mysql_fetch_row($result))


  		




  
  327
  
    
{


  		




  
  328
  
    
	$writer->startElement('individualOrganism');


  		




  
  329
  
    
	$writer->writeAttribute("id",$row[1]);


  		




  
  330
  
    
	$writer->writeElement('taxonNameUsageConceptID',$row[0]);


  		




  
  331
  
    
	$writer->writeElement('identificationLabel',$row[2]);


  		




  
  332
  
    
	$writer->endElement();


  		




  
  333
  
    
	


  		




  
  334
  
    
	$writer->startElement('individualOrganism');


  		




  
  335
  
    
	$writer->writeAttribute("id",$row[3]);


  		




  
  336
  
    
	$writer->writeElement('taxonNameUsageConceptID',$row[0]);


  		




  
  337
  
    
	$writer->writeElement('identificationLabel',$row[4]);


  		




  
  338
  
    
	$writer->endElement();


  		




  
  339
  
    
	$i++;


  		




  
  340
  
    
	


  		




  
  341
  
    
	if($i == 1000)


  		




  
  342
  
    
	{


  		




  
  343
  
    
		file_put_contents('new_Xml_CTFS_row.xml', $writer->flush(true), FILE_APPEND);


  		




  
  344
  
    
		$i = 0;


  		




  
  345
  
    
	}


  		




  
  346
  
    
}


  		




  
  347
  
    
$writer->endElement();


  		




  
  348
  
    
file_put_contents('new_Xml_CTFS_row.xml', $writer->flush(true), FILE_APPEND);


  		




  
  349
  
    

  		




  
  350
  
    
$writer->startElement('attributes');


  		




  
  351
  
    
$writer->startElement('attribute');


  		




  
  352
  
    
	$writer->writeAttribute("id","DBH");


  		




  
  353
  
    
	$writer->startElement('quantitative');


  		




  
  354
  
    
		$writer->writeElement('methodID');


  		




  
  355
  
    
		$writer->writeElement('unit','m');


  		




  
  356
  
    
		$writer->writeElement('precision','.01');


  		




  
  357
  
    
		$writer->writeElement('upperBound',"10000");


  		




  
  358
  
    
		$writer->writeElement('lowerBound',"0");


  		




  
  359
  
    
	$writer->endElement();


  		




  
  360
  
    
$writer->endElement();


  		




  
  361
  
    
$writer->startElement('attribute');


  		




  
  362
  
    
	$writer->writeAttribute("id","HOM");


  		




  
  363
  
    
	$writer->startElement('quantitative');


  		




  
  364
  
    
		$writer->writeElement('methodID');


  		




  
  365
  
    
		$writer->writeElement('unit','m');


  		




  
  366
  
    
		$writer->writeElement('precision','.01');


  		




  
  367
  
    
		$writer->writeElement('upperBound',"10000");


  		




  
  368
  
    
		$writer->writeElement('lowerBound',"0");


  		




  
  369
  
    
	$writer->endElement();


  		




  
  370
  
    
$writer->endElement();


  		




  
  371
  
    
$writer->endElement();


  		




  
  372
  
    

  		




  
  373
  
    

  		




  
  374
  
    

  		




  
  375
  
    
$sql = "select attr,status from attributes";


  		




  
  376
  
    
$result = mysql_query($sql);


  		




  
  377
  
    
$writer->startElement('methods');


  		




  
  378
  
    
$writer->startElement('method');


  		




  
  379
  
    
	$writer->writeAttribute("id","CensusID");


  		




  
  380
  
    
	$writer->writeElement('description',"Census information");


  		




  
  381
  
    
	$writer->writeElement('name',"CensusID");


  		




  
  382
  
    
$writer->endElement();


  		




  
  383
  
    
while($row = mysql_fetch_row($result))


  		




  
  384
  
    
{


  		




  
  385
  
    
$writer->startElement('method');


  		




  
  386
  
    
	$writer->writeAttribute("id",$row[0]);


  		




  
  387
  
    
	$writer->writeElement('description',$row[1]);


  		




  
  388
  
    
	$writer->writeElement('name',$row[0]);


  		




  
  389
  
    
$writer->endElement();


  		




  
  390
  
    
}


  		




  
  391
  
    
$writer->endElement();


  		




  
  392
  
    

  		




  
  393
  
    
$writer->endElement();


  		




  
  394
  
    

  		




  
  395
  
    
$writer->endDocument();


  		




  
  396
  
    

  		




  
  397
  
    
//header('Content-type: text/xml');


  		




  
  398
  
    

  		




  
  399
  
    
//echo $writer->outputMemory();


  		




  
  400
  
    

  		




  
  401
  
    
$filename = "new_Xml_CTFS_row.xml";


  		




  
  402
  
    

  		




  
  403
  
    
//$file = $writer->outputMemory();


  		




  
  404
  
    
file_put_contents($filename, $writer->flush(true), FILE_APPEND);


  		




  
  405
  
    
//file_put_contents($filename,$file);


  		




  
  406
  
    

  		




  
  407
  
    
echo "and Veg-X file for the uploaded csv file was created";


  		




  
  408
  
    
echo date("m/d/y :H:i:s",time());


  		




  
  409
  
    
?>


  		




  
  410
  
    
<FORM METHOD="LINK" ACTION="new_Xml_CTFS_row.xml">


  		




  
  411
  
    
<br/>


  		




  
  412
  
    
<br/>


  		




  
  413
  
    
<br/>


  		




  
  414
  
    
<INPUT TYPE="submit" VALUE="Click to See the VegX file">


  		




  
  415
  
    
</FORM>


  		




  
  416
  
    
</body></html>


  		




  
  417
  
    

  		




  
  418
  
    

  		




  
  419
  
    

  		




  
  420
  
    

  		




  
  421
  
    

  		




  
  422
  
    

  		




  
  423
  
    

  		




  
  424
  
    

  		




  
  425
  
    

  		




  
  426
  
    

  		




  
  427
  
    

  		












  

    
      inputs/CTFS/src/scripts_to_drop_extra_tables/create.sql
    
  





  
  1
  
    
/* ***************** 2011 STRI DB 'CREATE' MySQL Script *****************


  		




  
  2
  
    
 *									*


  		




  
  3
  
    
 *		Most of the 2011 db is based off of the 2007 db.	*


  		




  
  4
  
    
 *		This script creates the new tables of the 2011 db	*


  		




  
  5
  
    
 *		and creates the new attributes of existing tables	*


  		




  
  6
  
    
 *		Nothing is deleted in this script. The drop.sql		*


  		




  
  7
  
    
 *		script will remove excess data and schema.		*


  		




  
  8
  
    
 *									*


  		




  
  9
  
    
 ***********************************************************************/


  		




  
  10
  
    
 


  		




  
  11
  
    
/* ***************************** Developers *****************************


  		




  
  12
  
    
 *									*


  		




  
  13
  
    
 *		Bradley University, Computer Science Dept.		*


  		




  
  14
  
    
 *		Smithsonian Tropical Research Institute			*


  		




  
  15
  
    
 *									*


  		




  
  16
  
    
 *			2011 Capstone Project				*


  		




  
  17
  
    
 *		Mario Campos, Michael Koeber, Zach Caschetta 		*


  		




  
  18
  
    
 *									*	


  		




  
  19
  
    
 ***********************************************************************/


  		




  
  20
  
    

  		




  
  21
  
    
-- Quadrat TABLE


  		




  
  22
  
    
ALTER TABLE Tree DROP FOREIGN KEY `Tree_ibfk_1`;


  		




  
  23
  
    
ALTER TABLE CensusQuadrat DROP FOREIGN KEY `CensusQuadrat_ibfk_2`;


  		




  
  24
  
    
ALTER TABLE Quadrat DROP PRIMARY KEY;


  		




  
  25
  
    
ALTER TABLE Quadrat CHANGE COLUMN QuadratID QuadratID2 INT UNSIGNED;


  		




  
  26
  
    
ALTER TABLE Quadrat ADD COLUMN (Area FLOAT UNSIGNED, IsStandardShape ENUM('Y','N') NOT NULL);


  		




  
  27
  
    
ALTER TABLE Quadrat ADD COLUMN QuadratID INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY;


  		




  
  28
  
    

  		




  
  29
  
    
-- CensusQuadrat TABLE


  		




  
  30
  
    
ALTER TABLE DataEntry DROP FOREIGN KEY `DataEntry_ibfk_1`;


  		




  
  31
  
    
ALTER TABLE DataCollection DROP FOREIGN KEY `DataCollection_ibfk_1`;


  		




  
  32
  
    
ALTER TABLE CensusQuadrat CHANGE COLUMN QuadratID QuadratID2 INT UNSIGNED;


  		




  
  33
  
    
ALTER TABLE CensusQuadrat ADD COLUMN QuadratID INT UNSIGNED NOT NULL;


  		




  
  34
  
    
UPDATE CensusQuadrat CQ, Quadrat Q SET CQ.QuadratID=Q.QuadratID WHERE CQ.QuadratID2=Q.QuadratID2 AND CQ.PlotID=Q.PlotID;


  		




  
  35
  
    
ALTER TABLE CensusQuadrat DROP PRIMARY KEY;


  		




  
  36
  
    
ALTER TABLE CensusQuadrat ADD COLUMN CensusQuadratID INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY;


  		




  
  37
  
    
ALTER TABLE CensusQuadrat ADD FOREIGN KEY (`QuadratID`) REFERENCES `Quadrat` (`QuadratID`);


  		




  
  38
  
    

  		




  
  39
  
    
-- DataCollection TABLE


  		




  
  40
  
    
ALTER TABLE DataCollection DROP PRIMARY KEY;


  		




  
  41
  
    
ALTER TABLE DataCollection ADD COLUMN DataCollectionID INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY;


  		




  
  42
  
    
ALTER TABLE DataCollection ADD COLUMN PersonnelRoleID INT UNSIGNED NOT NULL;


  		




  
  43
  
    
ALTER TABLE DataCollection CHANGE COLUMN QuadratID QuadratID2 INT UNSIGNED;


  		




  
  44
  
    
ALTER TABLE DataCollection ADD COLUMN QuadratID INT UNSIGNED NOT NULL;


  		




  
  45
  
    
UPDATE DataCollection D, Quadrat Q SET D.QuadratID=Q.QuadratID WHERE D.QuadratID2=Q.QuadratID2;


  		




  
  46
  
    
ALTER TABLE DataCollection ADD FOREIGN KEY (`QuadratID`) REFERENCES `Quadrat` (`QuadratID`);


  		




  
  47
  
    
ALTER TABLE DataCollection ADD FOREIGN KEY (`CensusID`)  REFERENCES `Census` (`CensusID`);


  		




  
  48
  
    

  		




  
  49
  
    
-- PersonnelRole TABLE


  		




  
  50
  
    
CREATE TABLE PersonnelRole (PersonnelRoleID INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,


  		




  
  51
  
    
       	     		    PersonnelID SMALLINT UNSIGNED NOT NULL,


  		




  
  52
  
    
			    RoleID SMALLINT UNSIGNED NOT NULL


  		




  
  53
  
    
			   )ENGINE=INNODB;


  		




  
  54
  
    
INSERT INTO PersonnelRole (PersonnelID,RoleID) SELECT PersonnelID, RoleID FROM DataCollection;


  		




  
  55
  
    
ALTER TABLE PersonnelRole ADD FOREIGN KEY (RoleID) REFERENCES RoleReference(RoleID);


  		




  
  56
  
    
ALTER TABLE PersonnelRole ADD FOREIGN KEY (PersonnelID) REFERENCES Personnel(PersonnelID);


  		




  
  57
  
    
UPDATE DataCollection D, PersonnelRole P SET D.PersonnelRoleID=P.PersonnelRoleID WHERE D.PersonnelID=P.PersonnelID AND D.RoleID=P.RoleID;


  		




  
  58
  
    
ALTER TABLE DataCollection DROP FOREIGN KEY `DataCollection_ibfk_2`;


  		




  
  59
  
    
ALTER TABLE DataCollection DROP FOREIGN KEY `DataCollection_ibfk_3`;


  		




  
  60
  
    
ALTER TABLE DataCollection ADD FOREIGN KEY (`PersonnelRoleID`) REFERENCES `PersonnelRole` (`PersonnelRoleID`);


  		




  
  61
  
    

  		




  
  62
  
    
-- Tree TABLE


  		




  
  63
  
    
ALTER TABLE Tree CHANGE COLUMN QuadratID QuadratID2 INT UNSIGNED;


  		




  
  64
  
    
ALTER TABLE Tree ADD COLUMN QuadratID INT UNSIGNED;


  		




  
  65
  
    
UPDATE Tree T, Quadrat Q SET T.QuadratID=Q.QuadratID WHERE T.QuadratID2=Q.QuadratID2;


  		




  
  66
  
    

  		




  
  67
  
    
-- Stem TABLE


  		




  
  68
  
    
ALTER TABLE Measurement DROP FOREIGN KEY `Measurement_ibfk_2`;


  		




  
  69
  
    
-- ALTER TABLE Remeasurement DROP FOREIGN KEY to stem --FINISH THIS LINE


  		




  
  70
  
    
ALTER TABLE Stem DROP PRIMARY KEY;


  		




  
  71
  
    
ALTER TABLE Stem ADD COLUMN (QuadratID INT UNSIGNED NOT NULL,


  		




  
  72
  
    
      	    	     	     StemNumber INT UNSIGNED NOT NULL,


  		




  
  73
  
    
			     Moved ENUM('Y','N') NOT NULL,


  		




  
  74
  
    
			     GX FLOAT,


  		




  
  75
  
    
			     GY FLOAT,


  		




  
  76
  
    
			     GZ FLOAT,


  		




  
  77
  
    
			     PX FLOAT,


  		




  
  78
  
    
			     PY FLOAT,


  		




  
  79
  
    
			     PZ FLOAT,


  		




  
  80
  
    
			     QX FLOAT,


  		




  
  81
  
    
			     QY FLOAT,


  		




  
  82
  
    
			     QZ FLOAT


  		




  
  83
  
    
			    );


  		




  
  84
  
    
UPDATE Stem S, Tree T SET S.QuadratID=T.QuadratID WHERE S.TreeID=T.TreeID;


  		




  
  85
  
    
UPDATE Stem S1, Stem S2 SET S1.StemNumber=S2.StemID WHERE S1.StemID=S2.StemID AND S1.TreeID=S2.TreeID;


  		




  
  86
  
    
UPDATE Stem S, Tree T SET S.QX=T.x, S.QY=T.y WHERE S.TreeID=T.TreeID;


  		




  
  87
  
    
ALTER TABLE Stem DROP COLUMN StemID;


  		




  
  88
  
    
ALTER TABLE Stem ADD COLUMN StemID INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;


  		




  
  89
  
    

  		




  
  90
  
    
-- Measurement(DBH) TABLE


  		




  
  91
  
    
ALTER TABLE LogMeasurementHistory DROP FOREIGN KEY `LogMeasurementHistory_ibfk_1`;


  		




  
  92
  
    
ALTER TABLE MeasurementAttributes DROP FOREIGN KEY `MeasurementAttributes_ibfk_1`;


  		




  
  93
  
    
ALTER TABLE Measurement DROP PRIMARY KEY;


  		




  
  94
  
    
ALTER TABLE Measurement ADD COLUMN DBHID INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY;


  		




  
  95
  
    
ALTER TABLE Measurement ADD COLUMN Comments VARCHAR(128);


  		




  
  96
  
    
ALTER TABLE Measurement MODIFY COLUMN HOM CHAR(16);


  		




  
  97
  
    
UPDATE Measurement M, Stem S SET M.StemID=S.StemID WHERE M.StemID=S.StemNumber AND M.TreeID=S.TreeID;


  		




  
  98
  
    
ALTER TABLE Measurement ADD FOREIGN KEY (`StemID`) REFERENCES `Stem` (`StemID`);


  		




  
  99
  
    

  		




  
  100
  
    
-- MeasurementAttributes TABLE


  		




  
  101
  
    
ALTER TABLE MeasurementAttributes ADD COLUMN DBHID INT UNSIGNED;


  		




  
  102
  
    
UPDATE MeasurementAttributes MA, Measurement M SET MA.DBHID=M.DBHID WHERE MA.MeasureID=M.MeasureID;


  		




  
  103
  
    
ALTER TABLE MeasurementAttributes ADD FOREIGN KEY (`DBHID`) REFERENCES `Measurement` (`DBHID`);


  		




  
  104
  
    
ALTER TABLE LogMAttrHistory DROP FOREIGN KEY `LogMAttrHistory_ibfk_1`;


  		




  
  105
  
    
ALTER TABLE MeasurementAttributes DROP PRIMARY KEY;


  		




  
  106
  
    
ALTER TABLE MeasurementAttributes ADD COLUMN DBHAttID INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY;


  		




  
  107
  
    

  		




  
  108
  
    
-- Remeasurement TABLE


  		




  
  109
  
    
ALTER TABLE RemeasAttribs DROP FOREIGN KEY `RemeasAttribs_ibfk_1`;


  		




  
  110
  
    
ALTER TABLE Remeasurement DROP PRIMARY KEY;


  		




  
  111
  
    
ALTER TABLE Remeasurement CHANGE COLUMN RemeasureID RemeasureID2 INT UNSIGNED;


  		




  
  112
  
    
ALTER TABLE Remeasurement ADD COLUMN RemeasureID INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY;


  		




  
  113
  
    
UPDATE Remeasurement R, Stem S SET R.StemID=S.StemID WHERE R.StemID=S.StemNumber AND R.TreeID=S.TreeID;


  		




  
  114
  
    
ALTER TABLE Remeasurement ADD FOREIGN KEY (`StemID`) REFERENCES `Stem` (`StemID`);


  		




  
  115
  
    

  		




  
  116
  
    
-- RemeasAttribs TABLE


  		




  
  117
  
    
ALTER TABLE RemeasAttribs CHANGE COLUMN RemeasureID RemeasureID2 INT UNSIGNED;


  		




  
  118
  
    
ALTER TABLE RemeasAttribs ADD COLUMN RemeasureID INT UNSIGNED NOT NULL;


  		




  
  119
  
    
UPDATE RemeasAttribs RA, Remeasurement R SET RA.RemeasureID=R.RemeasureID WHERE RA.RemeasureID2=R.RemeasureID2;


  		




  
  120
  
    
ALTER TABLE RemeasAttribs DROP PRIMARY KEY;


  		




  
  121
  
    
ALTER TABLE RemeasAttribs ADD COLUMN RmAttID INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY;


  		




  
  122
  
    
ALTER TABLE RemeasAttribs ADD FOREIGN KEY (`RemeasureID`) REFERENCES `Remeasurement` (`RemeasureID`);


  		




  
  123
  
    

  		




  
  124
  
    
-- TreeAttributes TABLE


  		




  
  125
  
    
ALTER TABLE LogTreeAttrHistory DROP FOREIGN KEY `LogTreeAttrHistory_ibfk_1`;


  		




  
  126
  
    
ALTER TABLE TreeAttributes DROP PRIMARY KEY;


  		




  
  127
  
    
ALTER TABLE TreeAttributes ADD COLUMN TAttID INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY;


  		




  
  128
  
    

  		




  
  129
  
    
-- rename tables


  		




  
  130
  
    
ALTER TABLE Measurement RENAME TO DBH;


  		




  
  131
  
    
ALTER TABLE MeasurementAttributes RENAME TO DBHAttributes;


  		




  
  132
  
    

  		




  
  133
  
    
-- add new tables


  		




  
  134
  
    
CREATE TABLE IF NOT EXISTS FeatureTypes (


  		




  
  135
  
    
					   FeatureTypeID INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,


  		




  
  136
  
    
					   Type VARCHAR(32) NOT NULL


  		




  
  137
  
    
					)  ENGINE=INNODB;


  		




  
  138
  
    

  		




  
  139
  
    
CREATE TABLE IF NOT EXISTS Features (


  		




  
  140
  
    
					FeatureID INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,


  		




  
  141
  
    
					FeatureTypeID INT UNSIGNED NOT NULL,


  		




  
  142
  
    
					Name VARCHAR(32) NOT NULL,


  		




  
  143
  
    
					ShortDescrip VARCHAR(32),


  		




  
  144
  
    
					LongDescrip VARCHAR(128),


  		




  
  145
  
    
					FOREIGN KEY (`FeatureTypeID`) REFERENCES `FeatureTypes` (`FeatureTypeID`)


  		




  
  146
  
    
				    )	ENGINE=INNODB;


  		




  
  147
  
    

  		




  
  148
  
    
CREATE TABLE IF NOT EXISTS Coordinates (


  		




  
  149
  
    
					  CoorID INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,


  		




  
  150
  
    
					  FeatureID INT UNSIGNED,


  		




  
  151
  
    
					  PlotID INT UNSIGNED,


  		




  
  152
  
    
					  QuadratID INT UNSIGNED,


  		




  
  153
  
    
					  GX FLOAT(8),


  		




  
  154
  
    
					  GY FLOAT(8),


  		




  
  155
  
    
					  GZ FLOAT(8),


  		




  
  156
  
    
					  PX FLOAT(8),


  		




  
  157
  
    
					  PY FLOAT(8),


  		




  
  158
  
    
					  PZ FLOAT(8),


  		




  
  159
  
    
					  QX FLOAT(8),


  		




  
  160
  
    
					  QY FLOAT(8),


  		




  
  161
  
    
					  QZ FLOAT(8),


  		




  
  162
  
    
					  CoordinateNo INT UNSIGNED,


  		




  
  163
  
    
					  FOREIGN KEY (`FeatureID`) REFERENCES `Features` (`FeatureID`),


  		




  
  164
  
    
					  FOREIGN KEY (`PlotID`) REFERENCES `Site` (`PlotID`),


  		




  
  165
  
    
					  FOREIGN KEY (`QuadratID`) REFERENCES `Quadrat` (`QuadratID`)


  		




  
  166
  
    
				       )  ENGINE=INNODB;


  		




  
  167
  
    

  		




  
  168
  
    
CREATE TABLE IF NOT EXISTS MeasurementType (


  		




  
  169
  
    
						MeasurementTypeID INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,


  		




  
  170
  
    
						UOM VARCHAR(32) NOT NULL,


  		




  
  171
  
    
						Type VARCHAR(256)


  		




  
  172
  
    
					   )	ENGINE=INNODB;


  		




  
  173
  
    

  		




  
  174
  
    
CREATE TABLE IF NOT EXISTS Measurement (


  		




  
  175
  
    
					MeasureID INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,


  		




  
  176
  
    
					CensusID INT UNSIGNED NOT NULL,


  		




  
  177
  
    
					TreeID INT UNSIGNED NOT NULL,


  		




  
  178
  
    
					StemID INT UNSIGNED NOT NULL,


  		




  
  179
  
    
					MeasurementTypeID INT UNSIGNED NOT NULL,


  		




  
  180
  
    
					Measure VARCHAR(256) NOT NULL,


  		




  
  181
  
    
					ExactDate DATE NOT NULL,


  		




  
  182
  
    
					Comments VARCHAR(128),


  		




  
  183
  
    
					FOREIGN KEY (`CensusID`) REFERENCES `Census` (`CensusID`),


  		




  
  184
  
    
					FOREIGN KEY (`TreeID`) REFERENCES `Tree` (`TreeID`),


  		




  
  185
  
    
					FOREIGN KEY (`StemID`) REFERENCES `Stem` (`StemID`),


  		




  
  186
  
    
					FOREIGN KEY (`MeasurementTypeID`) REFERENCES `MeasurementType` (`MeasurementTypeID`)


  		




  
  187
  
    
				       )ENGINE=INNODB;


  		




  
  188
  
    

  		




  
  189
  
    
CREATE TABLE IF NOT EXISTS MeasurementAttributes (


  		




  
  190
  
    
       	     	    	   			  MAttID INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,


  		




  
  191
  
    
						  MeasureID INT UNSIGNED NOT NULL,


  		




  
  192
  
    
						  CensusID INT UNSIGNED NOT NULL,


  		




  
  193
  
    
						  TSMID INT UNSIGNED NOT NULL,


  		




  
  194
  
    
						  FOREIGN KEY (`MeasureID`) REFERENCES `Measurement` (`MeasureID`),


  		




  
  195
  
    
						  FOREIGN KEY (`CensusID`) REFERENCES `Census` (`CensusID`),


  		




  
  196
  
    
						  FOREIGN KEY (`TSMID`) REFERENCES `TSMAttributes` (`TSMID`)


  		




  
  197
  
    
						 )ENGINE=INNODB;


  		




  
  198
  
    

  		




  
  199
  
    
CREATE TABLE IF NOT EXISTS Log (


  		




  
  200
  
    
				LogID BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,


  		




  
  201
  
    
				PersonnelID SMALLINT UNSIGNED,


  		




  
  202
  
    
				ChangedTable VARCHAR(32) NOT NULL,


  		




  
  203
  
    
				PrimaryKey VARCHAR(32) NOT NULL,


  		




  
  204
  
    
				ChangedColumn VARCHAR(32) NOT NULL,


  		




  
  205
  
    
				ChangeDate DATE,


  		




  
  206
  
    
				ChangeTime TIMESTAMP,


  		




  
  207
  
    
				Description VARCHAR(256),


  		




  
  208
  
    
				Action ENUM('I','D','U') NOT NULL,


  		




  
  209
  
    
				Old VARCHAR(512) NOT NULL,


  		




  
  210
  
    
				New VARCHAR(512) NOT NULL,


  		




  
  211
  
    
				FOREIGN KEY (`PersonnelID`) REFERENCES `Personnel` (`PersonnelID`)


  		




  
  212
  
    
			       )ENGINE=INNODB;


  		




  
  213
  
    

  		




  
  214
  
    
-- modify existing tables to meet new schema's specs


  		




  
  215
  
    
ALTER TABLE Site ADD COLUMN (  Area INT UNSIGNED NOT NULL,


  		




  
  216
  
    
      	    	     	       QDimX INT UNSIGNED NOT NULL,


  		




  
  217
  
    
			       QDimY INT UNSIGNED NOT NULL,


  		




  
  218
  
    
			       GUOM VARCHAR(32) NOT NULL,


  		




  
  219
  
    
			       GZUOM VARCHAR(32) NOT NULL,


  		




  
  220
  
    
			       PUOM VARCHAR(32) NOT NULL,


  		




  
  221
  
    
			       QUOM VARCHAR(32) NOT NULL,


  		




  
  222
  
    
			       GCoorCollected VARCHAR(32),


  		




  
  223
  
    
			       PCoorCollected VARCHAR(32),


  		




  
  224
  
    
			       QCoorCollected VARCHAR(32)


  		




  
  225
  
    
			    ); 


  		




  
  226
  
    

  		




  
  227
  
    
ALTER TABLE Stem ADD COLUMN (	GX FLOAT(8),


  		




  
  228
  
    
				GY FLOAT(8),


  		




  
  229
  
    
				GZ FLOAT(8),


  		




  
  230
  
    
				PX FLOAT(8),


  		




  
  231
  
    
				PY FLOAT(8),


  		




  
  232
  
    
				PZ FLOAT(8),


  		




  
  233
  
    
				QX FLOAT(8),


  		




  
  234
  
    
				QY FLOAT(8),


  		




  
  235
  
    
				QZ FLOAT(8),


  		




  
  236
  
    
				Moved ENUM('Y','N') NOT NULL


  		




  
  237
  
    
			    );


  		




  
  238
  
    

  		




  
  239
  
    
ALTER TABLE Census MODIFY COLUMN PlotCensusNumber CHAR(16);


  		












  

    
      inputs/CTFS/src/scripts_to_drop_extra_tables/drop.sql
    
  





  
  1
  
    
/* ************ MySQL Database and Schema Converting Script *************


  		




  
  2
  
    
 *									*


  		




  
  3
  
    
 *		This script first replicates the old databases		*


  		




  
  4
  
    
 *		schema into a new database. The new database's		*


  		




  
  5
  
    
 *		schema is then modified to meet the new schema		*


  		




  
  6
  
    
 *		specifications. Finally, the the data of the 		*


  		




  
  7
  
    
 *		old database is copied into the new database		*


  		




  
  8
  
    
 *		and converted, if necessary.				*


  		




  
  9
  
    
 *									*


  		




  
  10
  
    
 ***********************************************************************/


  		




  
  11
  
    
 


  		




  
  12
  
    
/* ***************************** Developers *****************************


  		




  
  13
  
    
 *									*


  		




  
  14
  
    
 *		Bradley University, Computer Science Dept.		*


  		




  
  15
  
    
 *		Smithsonian Tropical Research Institute			*


  		




  
  16
  
    
 *									*


  		




  
  17
  
    
 *			2011 Capstone Project				*


  		




  
  18
  
    
 *		Mario Campos, Michael Koeber, Zach Caschetta 		*


  		




  
  19
  
    
 *									*	


  		




  
  20
  
    
 ***********************************************************************/


  		




  
  21
  
    

  		




  
  22
  
    
-- drop unnecessary columns


  		




  
  23
  
    
ALTER TABLE Site DROP COLUMN Latitude;


  		




  
  24
  
    
ALTER TABLE Site DROP COLUMN Longitude;


  		




  
  25
  
    
ALTER TABLE Site DROP COLUMN Elevation;


  		




  
  26
  
    
ALTER TABLE Site DROP COLUMN ReferenceX;


  		




  
  27
  
    
ALTER TABLE Site DROP COLUMN ReferenceY;


  		




  
  28
  
    
ALTER TABLE Quadrat DROP COLUMN DimX;


  		




  
  29
  
    
ALTER TABLE Quadrat DROP COLUMN DimY;


  		




  
  30
  
    
ALTER TABLE Quadrat DROP COLUMN StartX;


  		




  
  31
  
    
ALTER TABLE Quadrat DROP COLUMN StartY;


  		




  
  32
  
    
ALTER TABLE Quadrat DROP COLUMN QuadratID2;


  		




  
  33
  
    
ALTER TABLE CensusQuadrat DROP COLUMN QuadratID2;


  		




  
  34
  
    
ALTER TABLE CensusQuadrat DROP COLUMN PlotID;


  		




  
  35
  
    
ALTER TABLE DataCollection DROP COLUMN PlotID;


  		




  
  36
  
    
ALTER TABLE DataCollection DROP COLUMN PersonnelID;


  		




  
  37
  
    
ALTER TABLE DataCollection DROP COLUMN RoleID;


  		




  
  38
  
    
ALTER TABLE DataCollection DROP COLUMN QuadratID2;


  		




  
  39
  
    
ALTER TABLE Tree DROP COLUMN QuadratID2;


  		




  
  40
  
    
ALTER TABLE Tree DROP COLUMN QuadratID;


  		




  
  41
  
    
ALTER TABLE Tree DROP COLUMN PlotID;


  		




  
  42
  
    
ALTER TABLE Tree DROP COLUMN x;


  		




  
  43
  
    
ALTER TABLE Tree DROP COLUMN y;


  		




  
  44
  
    
ALTER TABLE DBH DROP COLUMN TreeID;


  		




  
  45
  
    
ALTER TABLE DBH DROP COLUMN MeasureID;


  		




  
  46
  
    
ALTER TABLE DBHAttributes DROP COLUMN MeasureID;


  		




  
  47
  
    
ALTER TABLE Remeasurement DROP FOREIGN KEY `Remeasurement_ibfk_2`;


  		




  
  48
  
    
ALTER TABLE Remeasurement DROP COLUMN TreeID;


  		




  
  49
  
    
ALTER TABLE Remeasurement DROP COLUMN RemeasureID2;


  		




  
  50
  
    
ALTER TABLE RemeasAttribs DROP COLUMN RemeasureID2;


  		




  
  51
  
    

  		




  
  52
  
    
-- drop unnecessary tables


  		




  
  53
  
    
DROP TABLE IF EXISTS DFTemp,


  		




  
  54
  
    
     	      	     LogMAttrHistory,


  		




  
  55
  
    
		     LogTreeHistory,


  		




  
  56
  
    
		     DataEntry,


  		




  
  57
  
    
		     LogStemHistory,


  		




  
  58
  
    
		     LogMeasurementHistory,


  		




  
  59
  
    
		     StageTargetNew,


  		




  
  60
  
    
		     StageTarget,


  		




  
  61
  
    
		     StageSource,


  		




  
  62
  
    
		     StageTaxonomicChanges,


  		




  
  63
  
    
		     LogTreeAttrHistory;


  		












  

    
      inputs/CTFS/src/scripts_to_drop_extra_tables/migrate.pl
    
  





  
  1
  
    
#!/usr/bin/perl -w -s


  		




  
  2
  
    

  		




  
  3
  
    
############################# migrate.pl ###############################


  		




  
  4
  
    
#								       #


  		




  
  5
  
    
#	This perl script creates the new 2011 db and migrates          #


  		




  
  6
  
    
#       from the old db by...                                          #


  		




  
  7
  
    
#	1. creates the new database using create.sql                   #


  		




  
  8
  
    
#	2. maps the data from the old db to the new db                 #


  		




  
  9
  
    
#	3. drops the excess data and schema                            #


  		




  
  10
  
    
#								       #


  		




  
  11
  
    
#       Usage: migrate.pl [options]                                    #


  		




  
  12
  
    
#       options:                                                       #


  		




  
  13
  
    
#         -olddb='<DATABASENAME>'    Name of the old STRI Database     #


  		




  
  14
  
    
#         -newdb='<DATABASENAME>'    Name of the new STRI Database     #


  		




  
  15
  
    
#         -user='<USERNAME>'         MySQL username                    #


  		




  
  16
  
    
#         -passwd='<PASSWORD>'       Password of the MySQL user        #


  		




  
  17
  
    
#         -cfile='<FILEPATH>'        Absolute path to create.sql       #


  		




  
  18
  
    
#         -dfile='<FILEPATH>'        Absolute path to drop.sql         #


  		




  
  19
  
    
#         -mysql='<FILEPATH>'        Absolute path to mysql            #


  		




  
  20
  
    
#         -mysqldump='<FILEPATH>'    Absolute path to mysqldump        #


  		




  
  21
  
    
#         -quiet                     Ignores errors                    #


  		




  
  22
  
    
#                                                                      #


  		




  
  23
  
    
########################################################################


  		




  
  24
  
    
 


  		




  
  25
  
    
############################## Developers ##############################


  		




  
  26
  
    
#								       #


  		




  
  27
  
    
#		Bradley University, Computer Science Dept.	       #


  		




  
  28
  
    
#		Smithsonian Tropical Research Institute		       #


  		




  
  29
  
    
#								       #


  		




  
  30
  
    
#			2011 Capstone Project			       #


  		




  
  31
  
    
#		Mario Campos, Michael Koeber, Zach Caschetta 	       #


  		




  
  32
  
    
#								       #


  		




  
  33
  
    
########################################################################


  		




  
  34
  
    

  		




  
  35
  
    
use strict;


  		




  
  36
  
    
use DBI;


  		




  
  37
  
    
use Fcntl;


  		




  
  38
  
    

  		




  
  39
  
    
our($olddb,$newdb,$user,$passwd,$cfile,$dfile,$mysql,$mysqldump,$quiet);


  		




  
  40
  
    
BEGIN {


  		




  
  41
  
    
    $olddb or print 'Enter the old database name:' and chomp($olddb=<>);


  		




  
  42
  
    
    $newdb or print 'Enter the new database name:' and chomp($newdb=<>);


  		




  
  43
  
    
    $user or print 'Enter the username:' and chomp($user=<>);


  		




  
  44
  
    
    $passwd or print 'Enter the password:' and chomp($passwd=<>);


  		




  
  45
  
    
    $cfile or print 'Enter the path of the sql create file:' and chomp($cfile=<>);


  		




  
  46
  
    
    $dfile or print 'Enter the path of the sql drop file:' and chomp($dfile=<>);


  		




  
  47
  
    
    $mysql or $mysql='mysql';


  		




  
  48
  
    
    $mysqldump or $mysqldump='mysqldump';


  		




  
  49
  
    
    sysopen FILE,'migrate.log',O_CREAT|O_EXCL|O_WRONLY,0755 or die $!;


  		




  
  50
  
    
}


  		




  
  51
  
    

  		




  
  52
  
    
# create new db


  		




  
  53
  
    
print "Creating `$newdb`...";


  		




  
  54
  
    
system "$mysql -u $user -p$passwd -e 'create database $newdb;'";


  		




  
  55
  
    
print "done!\n";


  		




  
  56
  
    

  		




  
  57
  
    
# dump old db


  		




  
  58
  
    
print "Dumping `$olddb`...";


  		




  
  59
  
    
system "$mysqldump --opt -u $user -p$passwd $olddb > $olddb";


  		




  
  60
  
    
print "done!\n";


  		




  
  61
  
    

  		




  
  62
  
    
# inserting data into new db


  		




  
  63
  
    
print "Undumping into `$newdb`...";


  		




  
  64
  
    
system "$mysql -u $user -p$passwd $newdb < $olddb";


  		




  
  65
  
    
print "done!\n";


  		




  
  66
  
    

  		




  
  67
  
    
# running create.sql


  		




  
  68
  
    
system "$mysql -v -u $user -p$passwd $newdb < $cfile";


  		




  
  69
  
    

  		




  
  70
  
    

  		




  
  71
  
    
# connect to MySQL


  		




  
  72
  
    
my $dbh = DBI->connect("DBI:mysql:database=$newdb;host=localhost",$user,$passwd);


  		




  
  73
  
    

  		




  
  74
  
    
#   create coordinates of plot points


  		




  
  75
  
    
my $sql = 'SELECT PlotID, Latitude, Longitude, Elevation FROM Site';


  		




  
  76
  
    
my $sth = $dbh->prepare("$sql");


  		




  
  77
  
    
$sth->execute;


  		




  
  78
  
    
my $tbl_ref = $sth->fetchall_hashref('PlotID');


  		




  
  79
  
    
foreach my $key (keys %$tbl_ref) {


  		




  
  80
  
    
    $sql = 'INSERT INTO Coordinates (PlotID,GX,GY';


  		




  
  81
  
    
    defined $tbl_ref->{$key}->{'Elevation'} and $sql.=',GZ';


  		




  
  82
  
    
    $sql.=") VALUES ($key,$tbl_ref->{$key}->{Longitude},$tbl_ref->{$key}->{Latitude}";


  		




  
  83
  
    
    defined $tbl_ref->{$key}->{'Elevation'} and $sql.=",$tbl_ref->{$key}->{'Elevation'}";


  		




  
  84
  
    
    $sql.=')';


  		




  
  85
  
    
    if($dbh->do($sql)) {


  		




  
  86
  
    
	print "$sql\n";


  		




  
  87
  
    
    } else {


  		




  
  88
  
    
	die "Error executing: $sql\n" unless defined $quiet;


  		




  
  89
  
    
	print FILE "Error executing: $sql\n";


  		




  
  90
  
    
    }


  		




  
  91
  
    
}


  		




  
  92
  
    

  		




  
  93
  
    
#   create coordinates of quadrat points


  		




  
  94
  
    
$sql = 'SELECT QuadratID, PlotID, StartX, StartY FROM Quadrat';


  		




  
  95
  
    
$sth = $dbh->prepare("$sql");


  		




  
  96
  
    
$sth->execute;


  		




  
  97
  
    
$tbl_ref = $sth->fetchall_hashref('QuadratID');


  		




  
  98
  
    
foreach my $key (keys %$tbl_ref) {


  		




  
  99
  
    
    $sql = "INSERT INTO Coordinates (QuadratID,PlotID,PX,PY) VALUES ($key,$tbl_ref->{$key}->{PlotID},$tbl_ref->{$key}->{StartX},$tbl_ref->{$key}->{StartY})";


  		




  
  100
  
    
    if($dbh->do($sql)) {


  		




  
  101
  
    
        print "$sql\n";


  		




  
  102
  
    
    } else {


  		




  
  103
  
    
        die "Error executing: $sql\n" unless defined $quiet;


  		




  
  104
  
    
        print FILE "Error executing: $sql\n";


  		




  
  105
  
    
    }


  		




  
  106
  
    
}


  		




  
  107
  
    

  		




  
  108
  
    
$sql = 'SELECT DISTINCT DimX, DimY, PlotID FROM Quadrat';


  		




  
  109
  
    
$sth = $dbh->prepare("$sql");


  		




  
  110
  
    
$sth->execute;


  		




  
  111
  
    
$tbl_ref = $sth->fetchall_hashref('PlotID');


  		




  
  112
  
    
foreach my $key (keys %$tbl_ref) {


  		




  
  113
  
    
    if(defined $tbl_ref->{$key}->{'DimX'} && defined $tbl_ref->{$key}->{'DimY'}) {


  		




  
  114
  
    
	$sql = "UPDATE Site SET QDimX=$tbl_ref->{$key}->{DimX}, QDimY=$tbl_ref->{$key}->{DimY} WHERE PlotID=$key";


  		




  
  115
  
    
	if($dbh->do($sql)) {


  		




  
  116
  
    
	    print "$sql\n";


  		




  
  117
  
    
	} else {


  		




  
  118
  
    
	    die "Error executing: $sql\n" unless defined $quiet;


  		




  
  119
  
    
	    print FILE "Error executing: $sql\n";


  		




  
  120
  
    
	}


  		




  
  121
  
    
    }


  		




  
  122
  
    
}


  		




  
  123
  
    

  		




  
  124
  
    
# LogTreeHistory


  		




  
  125
  
    
$sql = 'SELECT TreeID,ChangeDate,ChangeDescription,ChangeCodeID,QuadratID,PlotID,Tag,X,Y,SpeciesID,SubSpeciesID FROM LogTreeHistory ORDER BY ChangeDate';


  		




  
  126
  
    
$sth = $dbh->prepare("$sql");


  		




  
  127
  
    
$sth->execute;


  		




  
  128
  
    
my @row = ();


  		




  
  129
  
    
while( @row = $sth->fetchrow_array ) {


  		




  
  130
  
    
    $sql = "INSERT INTO Log (ChangedTable,PrimaryKey,";


  		




  
  131
  
    
    defined $row[1] and $sql .= 'ChangeDate,';


  		




  
  132
  
    
    defined $row[2] and $sql .= 'Description,';


  		




  
  133
  
    
    $sql .= "New) VALUES ('Tree',$row[0],";


  		




  
  134
  
    
    defined $row[1] and $sql .= "'$row[1]',";


  		




  
  135
  
    
    defined $row[2] and $sql .= "'$row[2]',";


  		




  
  136
  
    
    $sql .= "'ChangeCodeID=$row[3],QuadratID=$row[4],PlotID=$row[5],Tag=$row[6],X=$row[7],Y=$row[8],SpeciesID=$row[9],SubspeciesID=$row[10]')";


  		




  
  137
  
    
    if($dbh->do($sql)) {


  		




  
  138
  
    
	print "$sql\n";


  		




  
  139
  
    
    } else {


  		




  
  140
  
    
	die "Error executing: $sql\n" unless defined $quiet;


  		




  
  141
  
    
	print FILE "Error executing: $sql\n";


  		




  
  142
  
    
    }


  		




  
  143
  
    
}


  		




  
  144
  
    

  		




  
  145
  
    
# LogMeasurementHistory


  		




  
  146
  
    
$sql = 'SELECT MeasureID,CensusID,DateOfChange,DescriptionOfChange,StemID,TreeID,DBH,HOM,ExactDate FROM LogMeasurementHistory ORDER BY DateOfChange';


  		




  
  147
  
    
$sth = $dbh->prepare("$sql");


  		




  
  148
  
    
$sth->execute;


  		




  
  149
  
    
while( @row = $sth->fetchrow_array ) {


  		




  
  150
  
    
    $sql = "INSERT INTO Log (ChangedTable,PrimaryKey,";


  		




  
  151
  
    
    defined $row[2] and $sql .= 'ChangeDate,';


  		




  
  152
  
    
    defined $row[3] and $sql .= 'Description,';


  		




  
  153
  
    
    $sql .= "New) VALUES ('Measurement','MeasureID=$row[0],CensusID=$row[1]',";


  		




  
  154
  
    
    defined $row[2] and $sql .= "'$row[2]',";


  		




  
  155
  
    
    defined $row[3] and $sql .= "'$row[3]',";


  		




  
  156
  
    
    $sql .= "'StemID=$row[4],TreeID=$row[5],DBH=$row[6],HOM=$row[7],ExactDate=$row[8]')";


  		




  
  157
  
    

  		




  
  158
  
    
    if($dbh->do($sql)) {


  		




  
  159
  
    
	print "$sql\n";


  		




  
  160
  
    
    } else {


  		




  
  161
  
    
        die "Error executing: $sql\n" unless defined $quiet;


  		




  
  162
  
    
        print FILE "Error executing: $sql\n";


  		




  
  163
  
    
    }


  		




  
  164
  
    
}


  		




  
  165
  
    

  		




  
  166
  
    
# LogMAttrHistory


  		




  
  167
  
    
$sql = 'SELECT MeasureID,CensusID,TSMID,DateOfChange,DescriptionOfChange FROM LogMAttrHistory ORDER BY DateOfChange';


  		




  
  168
  
    
$sth = $dbh->prepare($sql);


  		




  
  169
  
    
$sth->execute;


  		




  
  170
  
    
while( @row = $sth->fetchrow_array ) {


  		




  
  171
  
    
    $sql = "INSERT INTO Log (ChangedTable,PrimaryKey,";


  		




  
  172
  
    
    defined $row[3] and $sql .= 'ChangeDate,';


  		




  
  173
  
    
    defined $row[4] and $sql .= 'Description,';


  		




  
  174
  
    
    $sql .= "New) VALUES ('MeasurementAttributes','MeasureID=$row[0],CensusID=$row[1],TSMID=$row[2]',";


  		




  
  175
  
    
    defined $row[3] and $sql .= "'$row[3]',";


  		




  
  176
  
    
    defined $row[4] and $sql .= "'$row[4]',";


  		




  
  177
  
    
    $sql .= "'MeasureID=$row[0],CensusID=$row[1],TSMID=$row[2]')";


  		




  
  178
  
    

  		




  
  179
  
    
    if($dbh->do($sql)) {


  		




  
  180
  
    
        print "$sql\n";


  		




  
  181
  
    
    } else {


  		




  
  182
  
    
        die "Error executing: $sql\n" unless defined $quiet;


  		




  
  183
  
    
        print FILE "Error executing: $sql\n";


  		




  
  184
  
    
    }


  		




  
  185
  
    
}


  		




  
  186
  
    

  		




  
  187
  
    
# LogTreeAttrHistory


  		




  
  188
  
    
$sql = 'SELECT CensusID,TreeID,TSMID,DateOfChange,DescriptionOfChange FROM LogTreeAttrHistory ORDER BY DateOfChange';


  		




  
  189
  
    
$sth = $dbh->prepare("$sql");


  		




  
  190
  
    
$sth->execute;


  		




  
  191
  
    
while( @row = $sth->fetchrow_array ) {


  		




  
  192
  
    
    $sql = "INSERT INTO Log (ChangedTable,PrimaryKey,";


  		




  
  193
  
    
    defined $row[3] and $sql .= 'ChangeDate,';


  		




  
  194
  
    
    defined $row[4] and $sql .= 'Description,';


  		




  
  195
  
    
    $sql .= "New) VALUES ('TreeAttributes','CensusID=$row[0],TreeID=$row[1],TSMID=$row[2]',";


  		




  
  196
  
    
    defined $row[3] and $sql .= "'$row[3]',";


  		




  
  197
  
    
    defined $row[4] and $sql .= "'$row[4]',";


  		




  
  198
  
    
    $sql .= "'CensusID=$row[0],TreeID=$row[1],TSMID=$row[2]')";


  		




  
  199
  
    

  		




  
  200
  
    
    if($dbh->do($sql)) {


  		




  
  201
  
    
        print "$sql\n";


  		




  
  202
  
    
    } else {


  		




  
  203
  
    
        die "Error executing: $sql\n" unless defined $quiet;


  		




  
  204
  
    
        print FILE "Error executing: $sql\n";


  		




  
  205
  
    
    }


  		




  
  206
  
    
}


  		




  
  207
  
    

  		




  
  208
  
    
# execute drop.sql


  		




  
  209
  
    
system "$mysql -v -u $user -p$passwd $newdb < $dfile" or die $!;


  		




  
  210
  
    

  		




  
  211
  
    
close FILE or die $!;


  		












  

    
      inputs/CTFS/src/README.TXT
    
  





  
  1
  
    
E-mail from Shash on 2012-1-19:


  		




  
  2
  
    

  		




  
  3
  
    
query for data analysis


  		




  
  4
  
    
 


  		




  
  5
  
    
select * from DFtemp where SpeciesID = 465 and StemID = 10 and StemTag is not null


  		












  

    
      inputs/CTFS/src/DBv5.txt
    
  





  
  1
  
    
--


  		




  
  2
  
    
-- ER/Studio Data Architect 9.0 SQL Code Generation


  		




  
  3
  
    
-- Company :      STRI


  		




  
  4
  
    
-- Project :      Physical-09-1-08mod_ERStudio_comp.DM1


  		




  
  5
  
    
-- Author :       Mario Campos, Zach Caschetta, Michael Koeber, BUCS, STRI


  		




  
  6
  
    
--


  		




  
  7
  
    
-- Date Created : Monday, November 14, 2011 17:39:33


  		




  
  8
  
    
-- Target DBMS : MySQL 5.x


  		




  
  9
  
    
--


  		




  
  10
  
    

  		




  
  11
  
    
-- 


  		




  
  12
  
    
-- TABLE: Census 


  		




  
  13
  
    
--


  		




  
  14
  
    

  		




  
  15
  
    
CREATE TABLE Census(


  		




  
  16
  
    
    CensusID            INT UNSIGNED    AUTO_INCREMENT,


  		




  
  17
  
    
    PlotID              INT UNSIGNED    NOT NULL,


  		




  
  18
  
    
    PlotCensusNumber    CHAR(16),


  		




  
  19
  
    
    StartDate           DATE,


  		




  
  20
  
    
    EndDate             DATE,


  		




  
  21
  
    
    Description         VARCHAR(128),


  		




  
  22
  
    
    PRIMARY KEY (CensusID)


  		




  
  23
  
    
)ENGINE=INNODB


  		




  
  24
  
    
;


  		




  
  25
  
    

  		




  
  26
  
    

  		




  
  27
  
    

  		




  
  28
  
    
-- 


  		




  
  29
  
    
-- TABLE: CensusQuadrat 


  		




  
  30
  
    
--


  		




  
  31
  
    

  		




  
  32
  
    
CREATE TABLE CensusQuadrat(


  		




  
  33
  
    
    CensusQuadratID    INT UNSIGNED  AUTO_INCREMENT,


  		




  
  34
  
    
    CensusID           INT UNSIGNED  NOT NULL,


  		




  
  35
  
    
    QuadratID          INT UNSIGNED  NOT NULL,


  		




  
  36
  
    
    PRIMARY KEY (CensusQuadratID)


  		




  
  37
  
    
)ENGINE=INNODB


  		




  
  38
  
    
;


  		




  
  39
  
    

  		




  
  40
  
    

  		




  
  41
  
    

  		




  
  42
  
    
-- 


  		




  
  43
  
    
-- TABLE: Coordinates 


  		




  
  44
  
    
--


  		




  
  45
  
    

  		




  
  46
  
    
CREATE TABLE Coordinates(


  		




  
  47
  
    
    CoorID          INT UNSIGNED     AUTO_INCREMENT,


  		




  
  48
  
    
    FeatureID       INT UNSIGNED     NOT NULL,


  		




  
  49
  
    
    QuadratID       INT UNSIGNED     NOT NULL,


  		




  
  50
  
    
    PlotID          INT UNSIGNED     NOT NULL,


  		




  
  51
  
    
    GX              DECIMAL(0, 0),


  		




  
  52
  
    
    GY              DECIMAL(0, 0),


  		




  
  53
  
    
    GZ              DECIMAL(0, 0),


  		




  
  54
  
    
    PX              DECIMAL(0, 0),


  		




  
  55
  
    
    PY              DECIMAL(0, 0),


  		




  
  56
  
    
    PZ              DECIMAL(0, 0),


  		




  
  57
  
    
    QX              DECIMAL(0, 0),


  		




  
  58
  
    
    QY              DECIMAL(0, 0),


  		




  
  59
  
    
    QZ              DECIMAL(0, 0),


  		




  
  60
  
    
    CoordinateNo    INT,


  		




  
  61
  
    
    PRIMARY KEY (CoorID)


  		




  
  62
  
    
)ENGINE=INNODB


  		




  
  63
  
    
;


  		




  
  64
  
    

  		




  
  65
  
    

  		




  
  66
  
    

  		




  
  67
  
    
-- 


  		




  
  68
  
    
-- TABLE: Country 


  		




  
  69
  
    
--


  		




  
  70
  
    

  		




  
  71
  
    
CREATE TABLE Country(


  		




  
  72
  
    
    CountryID      INT UNSIGNED   AUTO_INCREMENT,


  		




  
  73
  
    
    CountryName    VARCHAR(64),


  		




  
  74
  
    
    PRIMARY KEY (CountryID)


  		




  
  75
  
    
)ENGINE=INNODB


  		




  
  76
  
    
;


  		




  
  77
  
    

  		




  
  78
  
    

  		




  
  79
  
    

  		




  
  80
  
    
-- 


  		




  
  81
  
    
-- TABLE: CurrentObsolete 


  		




  
  82
  
    
--


  		




  
  83
  
    

  		




  
  84
  
    
CREATE TABLE CurrentObsolete(


  		




  
  85
  
    
    SpeciesID            INT UNSIGNED    NOT NULL,


  		




  
  86
  
    
    ObsoleteSpeciesID    INT UNSIGNED    NOT NULL,


  		




  
  87
  
    
    ChangeDate           DATETIME        NOT NULL,


  		




  
  88
  
    
    ChangeCodeID         INT UNSIGNED    NOT NULL,


  		




  
  89
  
    
    ChangeNote           VARCHAR(128),


  		




  
  90
  
    
    PRIMARY KEY (SpeciesID, ObsoleteSpeciesID, ChangeDate)


  		




  
  91
  
    
)ENGINE=INNODB


  		




  
  92
  
    
;


  		




  
  93
  
    

  		




  
  94
  
    

  		




  
  95
  
    

  		




  
  96
  
    
-- 


  		




  
  97
  
    
-- TABLE: DataCollection 


  		




  
  98
  
    
--


  		




  
  99
  
    

  		




  
  100
  
    
CREATE TABLE DataCollection(


  		




  
  101
  
    
    DataCollectionID    INT UNSIGNED  AUTO_INCREMENT,


  		




  
  102
  
    
    CensusID            INT UNSIGNED  NOT NULL,


  		




  
  103
  
    
    QuadratID           INT UNSIGNED  NOT NULL,


  		




  
  104
  
    
    PersonnelRoleID     INT UNSIGNED  NOT NULL,


  		




  
  105
  
    
    StartDate           DATE,


  		




  
  106
  
    
    EndDate             DATE,


  		




  
  107
  
    
    PRIMARY KEY (DataCollectionID)


  		




  
  108
  
    
)ENGINE=INNODB


  		




  
  109
  
    
;


  		




  
  110
  
    

  		




  
  111
  
    

  		




  
  112
  
    

  		




  
  113
  
    
-- 


  		




  
  114
  
    
-- TABLE: DBH 


  		




  
  115
  
    
--


  		




  
  116
  
    

  		




  
  117
  
    
CREATE TABLE DBH(


  		




  
  118
  
    
    DBHID          INT UNSIGNED    AUTO_INCREMENT,


  		




  
  119
  
    
    CensusID       INT UNSIGNED    NOT NULL,


  		




  
  120
  
    
    StemID         INT UNSIGNED    NOT NULL,


  		




  
  121
  
    
    DBH            FLOAT(7),


  		




  
  122
  
    
    HOM            CHAR(16),


  		




  
  123
  
    
    PrimaryStem    VARCHAR(20),


  		




  
  124
  
    
    ExactDate      DATE,


  		




  
  125
  
    
    Comments       VARCHAR(128),


  		




  
  126
  
    
    PRIMARY KEY (DBHID)


  		




  
  127
  
    
)ENGINE=INNODB


  		




  
  128
  
    
;


  		




  
  129
  
    

  		




  
  130
  
    

  		




  
  131
  
    

  		




  
  132
  
    
-- 


  		




  
  133
  
    
-- TABLE: DBHAttributes 


  		




  
  134
  
    
--


  		




  
  135
  
    

  		




  
  136
  
    
CREATE TABLE DBHAttributes(


  		




  
  137
  
    
    DBHAttID    INT UNSIGNED  AUTO_INCREMENT,


  		




  
  138
  
    
    DBHID       INT UNSIGNED  NOT NULL,


  		




  
  139
  
    
    CensusID    INT UNSIGNED  NOT NULL,


  		




  
  140
  
    
    TSMID       INT UNSIGNED  NOT NULL,


  		




  
  141
  
    
    PRIMARY KEY (DBHAttID)


  		




  
  142
  
    
)ENGINE=INNODB


  		




  
  143
  
    
;


  		




  
  144
  
    

  		




  
  145
  
    

  		




  
  146
  
    

  		




  
  147
  
    
-- 


  		




  
  148
  
    
-- TABLE: Family 


  		




  
  149
  
    
--


  		




  
  150
  
    

  		




  
  151
  
    
CREATE TABLE Family(


  		




  
  152
  
    
    FamilyID       INT UNSIGNED  AUTO_INCREMENT,


  		




  
  153
  
    
    Family         CHAR(32),


  		




  
  154
  
    
    ReferenceID    INT UNSIGNED  NOT NULL,


  		




  
  155
  
    
    PRIMARY KEY (FamilyID)


  		




  
  156
  
    
)ENGINE=INNODB


  		




  
  157
  
    
;


  		




  
  158
  
    

  		




  
  159
  
    

  		




  
  160
  
    

  		




  
  161
  
    
-- 


  		




  
  162
  
    
-- TABLE: Features 


  		




  
  163
  
    
--


  		




  
  164
  
    

  		




  
  165
  
    
CREATE TABLE Features(


  		




  
  166
  
    
    FeatureID           INT UNSIGNED    AUTO_INCREMENT,


  		




  
  167
  
    
    FeatureTypeID       INT UNSIGNED    NOT NULL,


  		




  
  168
  
    
    Name                VARCHAR(32),


  		




  
  169
  
    
    ShortDescription    VARCHAR(32),


  		




  
  170
  
    
    LongDescription     VARCHAR(128),


  		




  
  171
  
    
    PRIMARY KEY (FeatureID)


  		




  
  172
  
    
)ENGINE=INNODB


  		




  
  173
  
    
;


  		




  
  174
  
    

  		




  
  175
  
    

  		




  
  176
  
    

  		




  
  177
  
    
-- 


  		




  
  178
  
    
-- TABLE: FeatureTypes 


  		




  
  179
  
    
--


  		




  
  180
  
    

  		




  
  181
  
    
CREATE TABLE FeatureTypes(


  		




  
  182
  
    
    FeatureTypeID    INT UNSIGNED   AUTO_INCREMENT,


  		




  
  183
  
    
    Type             VARCHAR(32)    NOT NULL,


  		




  
  184
  
    
    PRIMARY KEY (FeatureTypeID)


  		




  
  185
  
    
)ENGINE=INNODB


  		




  
  186
  
    
;


  		




  
  187
  
    

  		




  
  188
  
    

  		




  
  189
  
    

  		




  
  190
  
    
-- 


  		




  
  191
  
    
-- TABLE: Genus 


  		




  
  192
  
    
--


  		




  
  193
  
    

  		




  
  194
  
    
CREATE TABLE Genus(


  		




  
  195
  
    
    GenusID        INT UNSIGNED  AUTO_INCREMENT,


  		




  
  196
  
    
    Genus          CHAR(32),


  		




  
  197
  
    
    ReferenceID    INT UNSIGNED  NOT NULL,


  		




  
  198
  
    
    Authority      CHAR(32),


  		




  
  199
  
    
    FamilyID       INT UNSIGNED  NOT NULL,


  		




  
  200
  
    
    PRIMARY KEY (GenusID)


  		




  
  201
  
    
)ENGINE=INNODB


  		




  
  202
  
    
;


  		




  
  203
  
    

  		




  
  204
  
    

  		




  
  205
  
    

  		




  
  206
  
    
-- 


  		




  
  207
  
    
-- TABLE: Log 


  		




  
  208
  
    
--


  		




  
  209
  
    

  		




  
  210
  
    
CREATE TABLE Log(


  		




  
  211
  
    
    LogID            INT UNSIGNED    AUTO_INCREMENT,


  		




  
  212
  
    
    PersonnelID      INT UNSIGNED    NOT NULL,


  		




  
  213
  
    
    ChangedTable     VARCHAR(32)     NOT NULL,


  		




  
  214
  
    
    ChangedColumn    VARCHAR(32)     NOT NULL,


  		




  
  215
  
    
    ChangeDate       DATE            NOT NULL,


  		




  
  216
  
    
    ChangeTime       TIMESTAMP,


  		




  
  217
  
    
    PrimaryKey       VARCHAR(32),


  		




  
  218
  
    
    Old              VARCHAR(128),


  		




  
  219
  
    
    New              VARCHAR(128),


  		




  
  220
  
    
    Action           CHAR(1)         NOT NULL,


  		




  
  221
  
    
    Description      VARCHAR(256),


  		




  
  222
  
    
    PRIMARY KEY (LogID)


  		




  
  223
  
    
)ENGINE=INNODB


  		




  
  224
  
    
;


  		




  
  225
  
    

  		




  
  226
  
    

  		




  
  227
  
    

  		




  
  228
  
    
-- 


  		




  
  229
  
    
-- TABLE: Measurement 


  		




  
  230
  
    
--


  		




  
  231
  
    

  		




  
  232
  
    
CREATE TABLE Measurement(


  		




  
  233
  
    
    MeasureID            INT UNSIGNED    AUTO_INCREMENT,


  		




  
  234
  
    
    CensusID             INT UNSIGNED,


  		




  
  235
  
    
    StemID               INT UNSIGNED    NOT NULL,


  		




  
  236
  
    
    MeasurementTypeID    INT UNSIGNED    NOT NULL,


  		




  
  237
  
    
    Measure              VARCHAR(32)     NOT NULL,


  		




  
  238
  
    
    ExactDate            DATE            NOT NULL,


  		




  
  239
  
    
    Comments             VARCHAR(128),


  		




  
  240
  
    
    PRIMARY KEY (MeasureID)


  		




  
  241
  
    
)ENGINE=INNODB


  		




  
  242
  
    
;


  		




  
  243
  
    

  		




  
  244
  
    

  		




  
  245
  
    

  		




  
  246
  
    
-- 


  		




  
  247
  
    
-- TABLE: MeasurementAttributes 


  		




  
  248
  
    
--


  		




  
  249
  
    

  		




  
  250
  
    
CREATE TABLE MeasurementAttributes(


  		




  
  251
  
    
    MAttID       INT UNSIGNED  AUTO_INCREMENT,


  		




  
  252
  
    
    MeasureID    INT UNSIGNED  NOT NULL,


  		




  
  253
  
    
    CensusID     INT UNSIGNED  NOT NULL,


  		




  
  254
  
    
    TSMID        INT UNSIGNED  NOT NULL,


  		




  
  255
  
    
    PRIMARY KEY (MAttID)


  		




  
  256
  
    
)ENGINE=INNODB


  		




  
  257
  
    
;


  		




  
  258
  
    

  		




  
  259
  
    

  		




  
  260
  
    

  		




  
  261
  
    
-- 


  		




  
  262
  
    
-- TABLE: MeasurementType 


  		




  
  263
  
    
--


  		




  
  264
  
    

  		




  
  265
  
    
CREATE TABLE MeasurementType(


  		




  
  266
  
    
    MeasurementTypeID    INT UNSIGNED   AUTO_INCREMENT,


  		




  
  267
  
    
    Type                 CHAR(32)       NOT NULL,


  		




  
  268
  
    
    UOM                  VARCHAR(32),


  		




  
  269
  
    
    PRIMARY KEY (MeasurementTypeID)


  		




  
  270
  
    
)ENGINE=INNODB


  		




  
  271
  
    
;


  		




  
  272
  
    

  		




  
  273
  
    

  		




  
  274
  
    

  		




  
  275
  
    
-- 


  		




  
  276
  
    
-- TABLE: Personnel 


  		




  
  277
  
    
--


  		




  
  278
  
    

  		




  
  279
  
    
CREATE TABLE Personnel(


  		




  
  280
  
    
    PersonnelID    INT UNSIGNED   AUTO_INCREMENT,


  		




  
  281
  
    
    FirstName      VARCHAR(32),


  		




  
  282
  
    
    LastName       VARCHAR(32)    NOT NULL,


  		




  
  283
  
    
    PRIMARY KEY (PersonnelID)


  		




  
  284
  
    
)ENGINE=INNODB


  		




  
  285
  
    
;


  		




  
  286
  
    

  		




  
  287
  
    

  		




  
  288
  
    

  		




  
  289
  
    
-- 


  		




  
  290
  
    
-- TABLE: PersonnelRole 


  		




  
  291
  
    
--


  		




  
  292
  
    

  		




  
  293
  
    
CREATE TABLE PersonnelRole(


  		




  
  294
  
    
    PersonnelRoleID    INT UNSIGNED  AUTO_INCREMENT,


  		




  
  295
  
    
    PersonnelID        INT UNSIGNED  NOT NULL,


  		




  
  296
  
    
    RoleID             INT UNSIGNED  NOT NULL,


  		




  
  297
  
    
    PRIMARY KEY (PersonnelRoleID)


  		




  
  298
  
    
)ENGINE=INNODB


  		




  
  299
  
    
;


  		




  
  300
  
    

  		




  
  301
  
    

  		




  
  302
  
    

  		




  
  303
  
    
-- 


  		




  
  304
  
    
-- TABLE: Quadrat 


  		




  
  305
  
    
--


  		




  
  306
  
    

  		




  
  307
  
    
CREATE TABLE Quadrat(


  		




  
  308
  
    
    QuadratID          INT UNSIGNED     AUTO_INCREMENT,


  		




  
  309
  
    
    PlotID             INT UNSIGNED     NOT NULL,


  		




  
  310
  
    
    QuadratName        CHAR(8),


  		




  
  311
  
    
    IsStandardShape    CHAR(1)          NOT NULL,


  		




  
  312
  
    
    Area               DECIMAL(0, 0),


  		




  
  313
  
    
    PRIMARY KEY (QuadratID)


  		




  
  314
  
    
)ENGINE=INNODB


  		




  
  315
  
    
;


  		




  
  316
  
    

  		




  
  317
  
    

  		




  
  318
  
    

  		




  
  319
  
    
-- 


  		




  
  320
  
    
-- TABLE: Reference 


  		




  
  321
  
    
--


  		




  
  322
  
    

  		




  
  323
  
    
CREATE TABLE Reference(


  		




  
  324
  
    
    ReferenceID          INT UNSIGNED    AUTO_INCREMENT,


  		




  
  325
  
    
    PublicationTitle     VARCHAR(64),


  		




  
  326
  
    
    FullReference        VARCHAR(256),


  		




  
  327
  
    
    DateofPublication    DATE,


  		




  
  328
  
    
    PRIMARY KEY (ReferenceID)


  		




  
  329
  
    
)ENGINE=INNODB


  		




  
  330
  
    
;


  		




  
  331
  
    

  		




  
  332
  
    

  		




  
  333
  
    

  		




  
  334
  
    
-- 


  		




  
  335
  
    
-- TABLE: RemeasAttribs 


  		




  
  336
  
    
--


  		




  
  337
  
    

  		




  
  338
  
    
CREATE TABLE RemeasAttribs(


  		




  
  339
  
    
    RmAttID        INT UNSIGNED  AUTO_INCREMENT,


  		




  
  340
  
    
    RemeasureID    INT UNSIGNED  NOT NULL,


  		




  
  341
  
    
    CensusID       INT UNSIGNED  NOT NULL,


  		




  
  342
  
    
    TSMID          INT UNSIGNED  NOT NULL,


  		




  
  343
  
    
    PRIMARY KEY (RmAttID)


  		




  
  344
  
    
)ENGINE=INNODB


  		




  
  345
  
    
;


  		




  
  346
  
    

  		




  
  347
  
    

  		




  
  348
  
    

  		




  
  349
  
    
-- 


  		




  
  350
  
    
-- TABLE: Remeasurement 


  		




  
  351
  
    
--


  		




  
  352
  
    

  		




  
  353
  
    
CREATE TABLE Remeasurement(


  		




  
  354
  
    
    RemeasureID    INT UNSIGNED  AUTO_INCREMENT,


  		




  
  355
  
    
    CensusID       INT UNSIGNED  NOT NULL,


  		




  
  356
  
    
    StemID         INT UNSIGNED  NOT NULL,


  		




  
  357
  
    
    DBH            FLOAT(7),


  		




  
  358
  
    
    HOM            FLOAT(7),


  		




  
  359
  
    
    ExactDate      DATE,


  		




  
  360
  
    
    PRIMARY KEY (RemeasureID)


  		




  
  361
  
    
)ENGINE=INNODB


  		




  
  362
  
    
;


  		




  
  363
  
    

  		




  
  364
  
    

  		




  
  365
  
    

  		




  
  366
  
    
-- 


  		




  
  367
  
    
-- TABLE: RoleReference 


  		




  
  368
  
    
--


  		




  
  369
  
    

  		




  
  370
  
    
CREATE TABLE RoleReference(


  		




  
  371
  
    
    RoleID         INT UNSIGNED    AUTO_INCREMENT,


  		




  
  372
  
    
    Description    VARCHAR(128),


  		




  
  373
  
    
    PRIMARY KEY (RoleID)


  		




  
  374
  
    
)ENGINE=INNODB


  		




  
  375
  
    
;


  		




  
  376
  
    

  		




  
  377
  
    

  		




  
  378
  
    

  		




  
  379
  
    
-- 


  		




  
  380
  
    
-- TABLE: Site 


  		




  
  381
  
    
--


  		




  
  382
  
    

  		




  
  383
  
    
CREATE TABLE Site(


  		




  
  384
  
    
    PlotID               INT UNSIGNED     AUTO_INCREMENT,


  		




  
  385
  
    
    CountryID            INT UNSIGNED     NOT NULL,


  		




  
  386
  
    
    Area                 DECIMAL(0, 0)    NOT NULL,


  		




  
  387
  
    
    PlotName             CHAR(64),


  		




  
  388
  
    
    LocationName         VARCHAR(128),


  		




  
  389
  
    
    QDimX                DECIMAL(0, 0)    NOT NULL,


  		




  
  390
  
    
    QDimY                DECIMAL(0, 0)    NOT NULL,


  		




  
  391
  
    
    SizeOfSite           CHAR(32),


  		




  
  392
  
    
    ShapeOfSite          CHAR(32),


  		




  
  393
  
    
    DescriptionOfSite    VARCHAR(128),


  		




  
  394
  
    
    GUnit                VARCHAR(32),


  		




  
  395
  
    
    GZUOM                VARCHAR(32),


  		




  
  396
  
    
    PUnit                VARCHAR(32),


  		




  
  397
  
    
    QUnit                VARCHAR(32),


  		




  
  398
  
    
    GCoorCollected       VARCHAR(32),


  		




  
  399
  
    
    PCoorCollected       VARCHAR(32),


  		




  
  400
  
    
    QCoorCollected       VARCHAR(32),


  		




  
  401
  
    
    PRIMARY KEY (PlotID)


  		




  
  402
  
    
)ENGINE=INNODB


  		




  
  403
  
    
;


  		




  
  404
  
    

  		




  
  405
  
    

  		




  
  406
  
    

  		




  
  407
  
    
-- 


  		




  
  408
  
    
-- TABLE: Species 


  		




  
  409
  
    
--


  		




  
  410
  
    

  		




  
  411
  
    
CREATE TABLE Species(


  		




  
  412
  
    
    SpeciesID            INT UNSIGNED     AUTO_INCREMENT,


  		




  
  413
  
    
    CurrentTaxonFlag     DECIMAL(0, 0),


  		




  
  414
  
    
    ObsoleteTaxonFlag    DECIMAL(0, 0),


  		




  
  415
  
    
    GenusID              INT UNSIGNED     NOT NULL,


  		




  
  416
  
    
    ReferenceID          INT UNSIGNED     NOT NULL,


  		




  
  417
  
    
    SpeciesName          CHAR(64),


  		




  
  418
  
    
    Mnemonic             CHAR(10),


  		




  
  419
  
    
    Authority            VARCHAR(128),


  		




  
  420
  
    
    IDLevel              CHAR(8),


  		




  
  421
  
    
    FieldFamily          CHAR(32),


  		




  
  422
  
    
    Description          VARCHAR(128),


  		




  
  423
  
    
    PRIMARY KEY (SpeciesID)


  		




  
  424
  
    
)ENGINE=INNODB


  		




  
  425
  
    
;


  		




  
  426
  
    

  		




  
  427
  
    

  		




  
  428
  
    

  		




  
  429
  
    
-- 


  		




  
  430
  
    
-- TABLE: SpeciesInventory 


  		




  
  431
  
    
--


  		




  
  432
  
    

  		




  
  433
  
    
CREATE TABLE SpeciesInventory(


  		




  
  434
  
    
    SpeciesInvID    INT UNSIGNED  AUTO_INCREMENT,


  		




  
  435
  
    
    CensusID        INT UNSIGNED  NOT NULL,


  		




  
  436
  
    
    PlotID          INT UNSIGNED  NOT NULL,


  		




  
  437
  
    
    SpeciesID       INT UNSIGNED  NOT NULL,


  		




  
  438
  
    
    SubSpeciesID    INT UNSIGNED  NOT NULL,


  		




  
  439
  
    
    PRIMARY KEY (SpeciesInvID)


  		




  
  440
  
    
)ENGINE=INNODB


  		




  
  441
  
    
;


  		




  
  442
  
    

  		




  
  443
  
    

  		




  
  444
  
    

  		




  
  445
  
    
-- 


  		




  
  446
  
    
-- TABLE: Specimen 


  		




  
  447
  
    
--


  		




  
  448
  
    

  		




  
  449
  
    
CREATE TABLE Specimen(


  		




  
  450
  
    
    SpecimenID        INT UNSIGNED     AUTO_INCREMENT,


  		




  
  451
  
    
    TreeID            INT UNSIGNED     NOT NULL,


  		




  
  452
  
    
    Collector         CHAR(64),


  		




  
  453
  
    
    SpecimenNumber    DECIMAL(0, 0),


  		




  
  454
  
    
    SpeciesID         INT UNSIGNED     NOT NULL,


  		




  
  455
  
    
    SubSpeciesID      INT UNSIGNED     NOT NULL,


  		




  
  456
  
    
    Herbarium         CHAR(32),


  		




  
  457
  
    
    Voucher           DECIMAL(0, 0),


  		




  
  458
  
    
    CollectionDate    DATE,


  		




  
  459
  
    
    DeterminedBy      CHAR(64),


  		




  
  460
  
    
    Description       VARCHAR(128),


  		




  
  461
  
    
    PRIMARY KEY (SpecimenID)


  		




  
  462
  
    
)ENGINE=INNODB


  		




  
  463
  
    
;


  		




  
  464
  
    

  		




  
  465
  
    

  		




  
  466
  
    

  		




  
  467
  
    
-- 


  		




  
  468
  
    
-- TABLE: Stem 


  		




  
  469
  
    
--


  		




  
  470
  
    

  		




  
  471
  
    
CREATE TABLE Stem(


  		




  
  472
  
    
    StemID             INT UNSIGNED     AUTO_INCREMENT,


  		




  
  473
  
    
    TreeID             INT UNSIGNED     NOT NULL,


  		




  
  474
  
    
    QuadratID          INT UNSIGNED     NOT NULL,


  		




  
  475
  
    
    StemTag            VARCHAR(32),


  		




  
  476
  
    
    StemDescription    VARCHAR(128),


  		




  
  477
  
    
    StemNumber         DECIMAL(0, 0)    NOT NULL,


  		




  
  478
  
    
    GX                 DECIMAL(0, 0),


  		




  
  479
  
    
    GY                 DECIMAL(0, 0),


  		




  
  480
  
    
    GZ                 DECIMAL(0, 0),


  		




  
  481
  
    
    PX                 DECIMAL(0, 0),


  		




  
  482
  
    
    PY                 CHAR(18),


  		




  
  483
  
    
    PZ                 CHAR(18),


  		




  
  484
  
    
    QX                 DECIMAL(0, 0),


  		




  
  485
  
    
    QY                 DECIMAL(0, 0),


  		




  
  486
  
    
    QZ                 DECIMAL(0, 0),


  		




  
  487
  
    
    Moved              CHAR(1)          NOT NULL,


  		




  
  488
  
    
    PRIMARY KEY (StemID)


  		




  
  489
  
    
)ENGINE=INNODB


  		




  
  490
  
    
;


  		




  
  491
  
    

  		




  
  492
  
    

  		




  
  493
  
    

  		




  
  494
  
    
-- 


  		




  
  495
  
    
-- TABLE: SubSpecies 


  		




  
  496
  
    
--


  		




  
  497
  
    

  		




  
  498
  
    
CREATE TABLE SubSpecies(


  		




  
  499
  
    
    SubSpeciesID          INT UNSIGNED     AUTO_INCREMENT,


  		




  
  500
  
    
    SpeciesID             INT UNSIGNED     NOT NULL,


  		




  
  501
  
    
    CurrentTaxonFlag      DECIMAL(0, 0),


  		




  
  502
  
    
    ObsoleteTaxonFlag     DECIMAL(0, 0),


  		




  
  503
  
    
    SubSpeciesName        CHAR(64),


  		




  
  504
  
    
    Mnemonic              CHAR(10),


  		




  
  505
  
    
    Authority             VARCHAR(128),


  		




  
  506
  
    
    InfraSpecificLevel    CHAR(32),


  		




  
  507
  
    
    PRIMARY KEY (SubSpeciesID)


  		




  
  508
  
    
)ENGINE=INNODB


  		




  
  509
  
    
;


  		




  
  510
  
    

  		




  
  511
  
    

  		




  
  512
  
    

  		




  
  513
  
    
-- 


  		




  
  514
  
    
-- TABLE: Tree 


  		








... This diff was truncated because it exceeds the maximum size that can be displayed.



Also available in:  Unified diff