Project

General

Profile

« Previous | Next » 

Revision 8065

inputs/SALVIAS/: Regenerated salvias_*.schema.sql from the MySQL version, to take advantage of my2pg improvements. The placeholder *_index columns which take the place of MySQL's inline index definitions have now been replaced by no-op CHECK constraints, so that there are no longer lots of dummy *_index columns in the map spreadsheets.

View differences:

inputs/SALVIAS/salvias_plots.schema.sql
90 90
  "plot_notes" text,
91 91
  "tmp_del" char(3) DEFAULT NULL,
92 92
  PRIMARY KEY ("PlotID"),
93
  "Country_index" boolean,
94
  "PrimOwnerID_index" boolean,
95
  "plot_administrator_index" boolean,
96
  "project_id_index" boolean,
97
  "tmp_del_index" boolean
93
  /*KEY "Country" ("Country")*/CHECK (true),
94
  /*KEY "PrimOwnerID" ("PrimOwnerID")*/CHECK (true),
95
  /*KEY "plot_administrator" ("plot_administrator")*/CHECK (true),
96
  /*KEY "project_id" ("project_id")*/CHECK (true),
97
  /*KEY "tmp_del" ("tmp_del")*/CHECK (true)
98 98
);
99 99
/*!40101 SET character_set_client = @saved_cs_client */;
100 100

  
......
170 170
  "temp_liandbh" double precision DEFAULT NULL,
171 171
  "tmp_del" char(3) DEFAULT NULL,
172 172
  PRIMARY KEY ("PlotObsID"),
173
  "PlotID_index" boolean,
174
  "tmp_del_index" boolean,
175
  "tmp_PlotObsID_index" boolean
173
  /*KEY "PlotID" ("PlotID")*/CHECK (true),
174
  /*KEY "tmp_del" ("tmp_del")*/CHECK (true),
175
  /*KEY "tmp_PlotObsID" ("tmp_PlotObsID")*/CHECK (true)
176 176
);
177 177
/*!40101 SET character_set_client = @saved_cs_client */;
178 178

  
......
191 191
  "plot_administrator" varchar(30) NOT NULL DEFAULT '',
192 192
  "project_id" integer NOT NULL DEFAULT '0',
193 193
  PRIMARY KEY ("EnteredBy","download_timestamp"),
194
  "plot_administrator_index" boolean
194
  /*KEY "plot_administrator" ("plot_administrator","project_id")*/CHECK (true)
195 195
);
196 196
/*!40101 SET character_set_client = @saved_cs_client */;
197 197

  
......
235 235
  "username" varchar(50) NOT NULL DEFAULT '',
236 236
  "access_level" integer NOT NULL DEFAULT '0',
237 237
  PRIMARY KEY ("plot_id","username"),
238
  "access_level_index" boolean
238
  /*KEY "access_level" ("access_level")*/CHECK (true)
239 239
);
240 240
/*!40101 SET character_set_client = @saved_cs_client */;
241 241

  
......
312 312
  "plot_notes" text,
313 313
  "tmp_del" char(3) DEFAULT NULL,
314 314
  PRIMARY KEY ("PlotID"),
315
  "Country_index" boolean,
316
  "PrimOwnerID_index" boolean,
317
  "plot_administrator_index" boolean,
318
  "project_id_index" boolean
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 319
);
320 320
/*!40101 SET character_set_client = @saved_cs_client */;
321 321

  
......
334 334
  "ipr_specific" text,
335 335
  "ipr_specific_updated" date DEFAULT NULL,
336 336
  PRIMARY KEY ("project_id"),
337
  "project_name_index" boolean,
338
  "on_project_list_index" boolean,
339
  "project_distinct_index" boolean
337
  /*KEY "project_name" ("project_name")*/CHECK (true),
338
  /*KEY "on_project_list" ("allow_download_all")*/CHECK (true),
339
  /*KEY "project_distinct" ("project_id","project_name","project_pi")*/CHECK (true)
340 340
);
341 341
/*!40101 SET character_set_client = @saved_cs_client */;
342 342

  
......
365 365
  "stem_liana_infestation" varchar(50) DEFAULT NULL,
366 366
  "tmp_del" char(3) DEFAULT NULL,
367 367
  PRIMARY KEY ("stem_id"),
368
  "plotobs_id_index" boolean
368
  /*KEY "plotobs_id" ("PlotObsID")*/CHECK (true)
369 369
);
370 370
/*!40101 SET character_set_client = @saved_cs_client */;
371 371
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
inputs/SALVIAS/salvias_users.schema.sql
28 28
  "affiliation_id" integer NOT NULL,
29 29
  "description" varchar(200) NOT NULL DEFAULT '',
30 30
  PRIMARY KEY ("affiliation_id"),
31
  "description_index" boolean
31
  /*CONSTRAINT "description" */UNIQUE ("description")
32 32
);
33 33
/*!40101 SET character_set_client = @saved_cs_client */;
34 34

  
......
46 46
  "admin_email" varchar(100) DEFAULT NULL,
47 47
  "http_location" varchar(255) DEFAULT NULL,
48 48
  PRIMARY KEY ("app_id"),
49
  "name_index" boolean
49
  /*CONSTRAINT "name" */UNIQUE ("name")
50 50
);
51 51
/*!40101 SET character_set_client = @saved_cs_client */;
52 52

  
......
152 152
  "group_id" integer NOT NULL DEFAULT '0',
153 153
  "app_id" integer NOT NULL DEFAULT '0',
154 154
  PRIMARY KEY ("page","group_id"),
155
  "app_id_index" boolean,
156
  "page_index" boolean
155
  /*KEY "app_id" ("app_id")*/CHECK (true),
156
  /*FULLTEXT KEY "page" ("page")*/CHECK (true)
157 157
);
158 158
/*!40101 SET character_set_client = @saved_cs_client */;
159 159

  
......
172 172
  "allow_request" integer NOT NULL DEFAULT '0',
173 173
  "show_description" integer NOT NULL DEFAULT '0',
174 174
  PRIMARY KEY ("page","app_id"),
175
  "allow_request_index" boolean,
176
  "show_description_index" boolean
175
  /*KEY "allow_request" ("allow_request")*/CHECK (true),
176
  /*KEY "show_description" ("show_description")*/CHECK (true)
177 177
);
178 178
/*!40101 SET character_set_client = @saved_cs_client */;
179 179

  
......
205 205
  "ip" varchar(16) NOT NULL DEFAULT '',
206 206
  "app_id" integer DEFAULT NULL,
207 207
  PRIMARY KEY ("ip"),
208
  "app_id_index" boolean
208
  /*KEY "app_id" ("app_id")*/CHECK (true)
209 209
);
210 210
/*!40101 SET character_set_client = @saved_cs_client */;
211 211

  
......
277 277
  "group_admin_email" varchar(80) NOT NULL DEFAULT '',
278 278
  "allow_request" integer NOT NULL DEFAULT '0',
279 279
  PRIMARY KEY ("gid"),
280
  "group_admin_email_index" boolean,
281
  "allow_request_index" boolean
280
  /*KEY "group_admin_email" ("group_admin_email")*/CHECK (true),
281
  /*KEY "allow_request" ("allow_request")*/CHECK (true)
282 282
);
283 283
/*!40101 SET character_set_client = @saved_cs_client */;
284 284

  
......
298 298
  "action" varchar(40) DEFAULT NULL,
299 299
  "app_id" integer DEFAULT NULL,
300 300
  PRIMARY KEY ("log_id"),
301
  "timestamp_index" boolean,
302
  "app_id_index" boolean
301
  /*CONSTRAINT "timestamp" */UNIQUE ("timestamp","ip"),
302
  /*KEY "app_id" ("app_id")*/CHECK (true)
303 303
);
304 304
/*!40101 SET character_set_client = @saved_cs_client */;
305 305

  
......
330 330
  "Department" varchar(50) DEFAULT NULL,
331 331
  "spec_dq_priority" integer NOT NULL DEFAULT '0',
332 332
  PRIMARY KEY ("id"),
333
  "login_index" boolean
333
  /*CONSTRAINT "login" */UNIQUE ("username")
334 334
);
335 335
/*!40101 SET character_set_client = @saved_cs_client */;
336 336

  
......
349 349
  "tries" integer DEFAULT NULL,
350 350
  "nextlogin" integer DEFAULT NULL,
351 351
  PRIMARY KEY ("username","app_id"),
352
  "ip_index" boolean
352
  /*KEY "ip" ("ip")*/CHECK (true)
353 353
);
354 354
/*!40101 SET character_set_client = @saved_cs_client */;
355 355
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
inputs/SALVIAS/projects/header.csv
1
project_id,project_name,project_pi,allow_download_all,ipr_specific,ipr_specific_updated,project_name_index,on_project_list_index,project_distinct_index
1
project_id,project_name,project_pi,allow_download_all,ipr_specific,ipr_specific_updated
inputs/SALVIAS/projects/map.csv
5 5
allow_download_all,*allow_download_all,,
6 6
ipr_specific,*ipr_specific,,
7 7
ipr_specific_updated,*ipr_specific_updated,,
8
project_name_index,*project_name_index,,
9
on_project_list_index,*on_project_list_index,,
10
project_distinct_index,*project_distinct_index,,
inputs/SALVIAS/projects/VegBIEN.csv
4 4
allow_download_all,,** No join mapping for *allow_download_all ** 
5 5
ipr_specific,,** No join mapping for *ipr_specific ** 
6 6
ipr_specific_updated,,** No join mapping for *ipr_specific_updated ** 
7
on_project_list_index,,** No join mapping for *on_project_list_index ** 
8
project_distinct_index,,** No join mapping for *project_distinct_index ** 
9
project_name_index,,** No join mapping for *project_name_index ** 
10 7
project_pi,,** No join mapping for *project_pi ** 
inputs/SALVIAS/projects/unmapped_terms.csv
2 2
*allow_download_all
3 3
*ipr_specific
4 4
*ipr_specific_updated
5
*project_name_index
6
*on_project_list_index
7
*project_distinct_index
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,plotobs_id_index
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
inputs/SALVIAS/stems/map.csv
15 15
stem_canopy_position,canopyPosition,,
16 16
stem_liana_infestation,lianaInfestation,,
17 17
tmp_del,*tmp_del,,
18
plotobs_id_index,OMIT,,Placeholder column created by MySQL to PostgreSQL translation
inputs/SALVIAS/stems/VegBIEN.csv
21 21
PlotObsID,"/location/locationevent/taxonoccurrence/iscultivated/_alt/1/_if[@name=""if TaxonOccurrence""]/cond/_exists",
22 22
PlotObsID,"/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
plotobs_id_index,,** No non-empty join mapping for OMIT ** Placeholder column created by MySQL to PostgreSQL translation
25 24
tmp_del,,** No join mapping for *tmp_del ** 
inputs/SALVIAS/stems/new_terms.csv
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."
5 5
stem_height_first_branch_m,heightFirstBranch_m,,"Brad: Same as for ht_first_branch_m, but applies to individuals stems, not trees. Rare."
6
plotobs_id_index,OMIT,,Placeholder column created by MySQL to PostgreSQL translation
inputs/SALVIAS/plotObservations/header.csv
1
PlotObsID,PlotID,PlotCode,census_no,census_date,OrigRecordID,Line,Ind,ind_id,tag1,tag2,x_position,y_position,dist,perp_dist,SourceVoucher,coll_number,collector_code,coll_inits,coll_lastname,coll_firstname,DetType,det_by,fam_status,gen_status,name_status,SpAuthStatus,Family,Genus,GenAuth,Species,auth,infra_rank_1,infra_ep_1,infra_auth_1,common_name,morphoname,species_code,Habit,height_class,height_m,height_m_commercial,ht_first_branch_m,NoInd,cover_percent,intercept_cm,cfaff,other_annotations,morphocf,IsMorpho,OrigFamily,OrigGenus,OrigSpecies,OrigAuth,phenology,canopy_position,canopy_form,liana_infestation,Notes,tmp_PlotObsID,temp_dbh,temp_liandbh,tmp_del,PlotID_index,tmp_del_index,tmp_PlotObsID_index
1
PlotObsID,PlotID,PlotCode,census_no,census_date,OrigRecordID,Line,Ind,ind_id,tag1,tag2,x_position,y_position,dist,perp_dist,SourceVoucher,coll_number,collector_code,coll_inits,coll_lastname,coll_firstname,DetType,det_by,fam_status,gen_status,name_status,SpAuthStatus,Family,Genus,GenAuth,Species,auth,infra_rank_1,infra_ep_1,infra_auth_1,common_name,morphoname,species_code,Habit,height_class,height_m,height_m_commercial,ht_first_branch_m,NoInd,cover_percent,intercept_cm,cfaff,other_annotations,morphocf,IsMorpho,OrigFamily,OrigGenus,OrigSpecies,OrigAuth,phenology,canopy_position,canopy_form,liana_infestation,Notes,tmp_PlotObsID,temp_dbh,temp_liandbh,tmp_del
inputs/SALVIAS/plotObservations/map.csv
69 69
temp_dbh,diameterBreastHeight_cm,,"Assuming units based on the units for intercept_cm, which measures the same dimension"
70 70
temp_liandbh,*temp_liandbh,,
71 71
tmp_del,*tmp_del,,
72
PlotID_index,OMIT,,Placeholder column created by MySQL to PostgreSQL translation
73
tmp_del_index,OMIT,,Placeholder column created by MySQL to PostgreSQL translation
74
tmp_PlotObsID_index,OMIT,,Placeholder column created by MySQL to PostgreSQL translation
inputs/SALVIAS/plotObservations/VegBIEN.csv
132 132
IsMorpho,,** No join mapping for *IsMorpho ** 
133 133
OrigAuth,,** No join mapping for *OrigAuth ** 
134 134
OrigRecordID,,** No join mapping for *OrigRecordID ** 
135
PlotID_index,,** No non-empty join mapping for OMIT ** Placeholder column created by MySQL to PostgreSQL translation
136 135
SpAuthStatus,,** No join mapping for *SpAuthStatus ** 
137 136
coll_inits,,** No join mapping for *coll_inits ** 
138 137
collector_code,,** No join mapping for *collector_code ** Brad: OMIT
......
151 150
species_code,,** No join mapping for *species_code ** 
152 151
temp_liandbh,,** No join mapping for *temp_liandbh ** 
153 152
tmp_PlotObsID,,** No join mapping for *tmp_PlotObsID ** 
154
tmp_PlotObsID_index,,** No non-empty join mapping for OMIT ** Placeholder column created by MySQL to PostgreSQL translation
155 153
tmp_del,,** No join mapping for *tmp_del ** 
156
tmp_del_index,,** No non-empty join mapping for OMIT ** Placeholder column created by MySQL to PostgreSQL translation
inputs/SALVIAS/plotObservations/new_terms.csv
29 29
OrigGenus,originalGenus,,
30 30
OrigSpecies,originalSpecificEpithet,,Brad: OMIT
31 31
temp_dbh,diameterBreastHeight_cm,,"Assuming units based on the units for intercept_cm, which measures the same dimension"
32
PlotID_index,OMIT,,Placeholder column created by MySQL to PostgreSQL translation
33
tmp_del_index,OMIT,,Placeholder column created by MySQL to PostgreSQL translation
34
tmp_PlotObsID_index,OMIT,,Placeholder column created by MySQL to PostgreSQL translation
inputs/SALVIAS/plotMetadata_/header.csv
1
MethodCode,PlotID,orig_filename,AccessCode,project_id,PrimOwnerID,SiteCode,SiteName,new_world,MajorGeo,Country,PolDiv1,pol1_type,pol2,pol2_type,lat_string,long_string,LatDec,LongDec,lat_long_accuracy,Elev,elev_max_m,elev_min_m,Precip,ElevSource,Temp,PrecipSource,TempSource,bearing,slope_aspect,slope_gradient,clay_percent,silt_percent,sand_percent,pH,soil_N,soil_P,soil_C,soil_K,soil_Mg,soil_Ca,soil_Na,soil_acidity,soil_base,soil_cation_cap,soil_conductivity,organic_percent,soil_texture,RevisionComments,Locality_Description,topography_desc,vegetation_1,vegetation_2,Habitat,life_zone_code,life_zone,PlotMethod,plot_area_ha,recensused,date_start,date_finish,plot_administrator,plot_notes,tmp_del,Country_index,PrimOwnerID_index,plot_administrator_index,project_id_index,tmp_del_index,lookup_MethodCode_Description
1
MethodCode,PlotID,orig_filename,AccessCode,project_id,PrimOwnerID,SiteCode,SiteName,new_world,MajorGeo,Country,PolDiv1,pol1_type,pol2,pol2_type,lat_string,long_string,LatDec,LongDec,lat_long_accuracy,Elev,elev_max_m,elev_min_m,Precip,ElevSource,Temp,PrecipSource,TempSource,bearing,slope_aspect,slope_gradient,clay_percent,silt_percent,sand_percent,pH,soil_N,soil_P,soil_C,soil_K,soil_Mg,soil_Ca,soil_Na,soil_acidity,soil_base,soil_cation_cap,soil_conductivity,organic_percent,soil_texture,RevisionComments,Locality_Description,topography_desc,vegetation_1,vegetation_2,Habitat,life_zone_code,life_zone,PlotMethod,plot_area_ha,recensused,date_start,date_finish,plot_administrator,plot_notes,tmp_del,lookup_MethodCode_Description
inputs/SALVIAS/plotMetadata_/map.csv
63 63
plot_administrator,*plot_administrator,,
64 64
plot_notes,locationRemarks,,
65 65
tmp_del,*tmp_del,,"3 plots contain ""del"" in this field and 3 in AccessCode"
66
Country_index,OMIT,,Placeholder column created by MySQL to PostgreSQL translation
67
PrimOwnerID_index,OMIT,,Placeholder column created by MySQL to PostgreSQL translation
68
plot_administrator_index,OMIT,,Placeholder column created by MySQL to PostgreSQL translation
69
project_id_index,OMIT,,Placeholder column created by MySQL to PostgreSQL translation
70
tmp_del_index,OMIT,,Placeholder column created by MySQL to PostgreSQL translation
71 66
lookup_MethodCode_Description,observationMeasure,,
inputs/SALVIAS/plotMetadata_/VegBIEN.csv
68 68
slope_gradient,/location/slopegradient_fraction/_alt/1,
69 69
PlotID,"/location/sourceaccessioncode/_if[@name=""if locationID""]/cond/_exists","Brad: This is artificial internal database ID; a unique identifier within SALVIAS DB to each plot, within the table plotMetadata."
70 70
PlotID,"/location/sourceaccessioncode/_if[@name=""if locationID""]/then/_if[@name=""if subplot""]/then/_join/1","Brad: This is artificial internal database ID; a unique identifier within SALVIAS DB to each plot, within the table plotMetadata."
71
Country_index,,** No non-empty join mapping for OMIT ** Placeholder column created by MySQL to PostgreSQL translation
72 71
ElevSource,,** No join mapping for *ElevSource ** 
73 72
MethodCode,,** No non-empty join mapping for OMIT ** Not globally unique after lookup_MethodCode_Description is combined with PlotMethod
74 73
PrecipSource,,** No join mapping for *PrecipSource ** 
75 74
PrimOwnerID,,** No join mapping for *PrimOwnerID ** 
76
PrimOwnerID_index,,** No non-empty join mapping for OMIT ** Placeholder column created by MySQL to PostgreSQL translation
77 75
RevisionComments,,** No join mapping for *RevisionComments ** 
78 76
SiteName,,** No join mapping for *SiteName ** 
79 77
TempSource,,** No join mapping for *TempSource ** 
......
82 80
new_world,,** No join mapping for *new_world ** 
83 81
orig_filename,,** No join mapping for *orig_filename ** 
84 82
plot_administrator,,** No join mapping for *plot_administrator ** 
85
plot_administrator_index,,** No non-empty join mapping for OMIT ** Placeholder column created by MySQL to PostgreSQL translation
86 83
pol1_type,,** No join mapping for *pol1_type ** 
87 84
pol2_type,,** No join mapping for *pol2_type ** 
88
project_id_index,,** No non-empty join mapping for OMIT ** Placeholder column created by MySQL to PostgreSQL translation
89 85
recensused,,"** No join mapping for *recensused ** Brad: This is a 0/1 value, internal to SALVIAS. 1 indicates that a  plot has >1 set of values, from different census events.; Aaron: Different censuses are distinguished in organisms data by different census_no values"
90 86
tmp_del,,"** No join mapping for *tmp_del ** 3 plots contain ""del"" in this field and 3 in AccessCode"
91
tmp_del_index,,** No non-empty join mapping for OMIT ** Placeholder column created by MySQL to PostgreSQL translation
inputs/SALVIAS/plotMetadata_/new_terms.csv
27 27
PlotMethod,samplingProtocol,,
28 28
date_start,startDate,,
29 29
date_finish,endDate,,
30
Country_index,OMIT,,Placeholder column created by MySQL to PostgreSQL translation
31
PrimOwnerID_index,OMIT,,Placeholder column created by MySQL to PostgreSQL translation
32
plot_administrator_index,OMIT,,Placeholder column created by MySQL to PostgreSQL translation
33
project_id_index,OMIT,,Placeholder column created by MySQL to PostgreSQL translation
34
tmp_del_index,OMIT,,Placeholder column created by MySQL to PostgreSQL translation
35 30
lookup_MethodCode_Description,observationMeasure,,

Also available in: Unified diff