Menu
▾
▴
gbif-cvs — GBIF mailing list for CVS commit messages
You can subscribe to this list here.
| 2004 |
Jan
|
Feb
|
Mar
|
Apr
|
May
|
Jun
|
Jul
|
Aug
|
Sep
(76) |
Oct
(8) |
Nov
(13) |
Dec
(7) |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 2005 |
Jan
(41) |
Feb
(2) |
Mar
(68) |
Apr
(4) |
May
(12) |
Jun
(54) |
Jul
(56) |
Aug
(95) |
Sep
(21) |
Oct
(45) |
Nov
(33) |
Dec
(265) |
| 2006 |
Jan
(306) |
Feb
(158) |
Mar
(152) |
Apr
(498) |
May
(427) |
Jun
(594) |
Jul
(1378) |
Aug
(1374) |
Sep
(991) |
Oct
(713) |
Nov
(1129) |
Dec
(629) |
| 2007 |
Jan
(706) |
Feb
(662) |
Mar
(816) |
Apr
(814) |
May
(664) |
Jun
(914) |
Jul
(372) |
Aug
(356) |
Sep
(258) |
Oct
(360) |
Nov
(128) |
Dec
(304) |
| 2008 |
Jan
(190) |
Feb
(204) |
Mar
(551) |
Apr
(378) |
May
(89) |
Jun
(66) |
Jul
(84) |
Aug
(15) |
Sep
(34) |
Oct
(4) |
Nov
(2) |
Dec
(12) |
| 2009 |
Jan
(3) |
Feb
(77) |
Mar
(20) |
Apr
(17) |
May
(70) |
Jun
(5) |
Jul
(11) |
Aug
(25) |
Sep
(1) |
Oct
|
Nov
|
Dec
|
|
From: Tim <tim...@us...> - 2009-05-28 09:01:30
|
Update of /cvsroot/gbif/portal-core/src/main/java/org/gbif/portal/util/log In directory ddv4jf1.ch3.sourceforge.com:/tmp/cvs-serv5136/src/main/java/org/gbif/portal/util/log Modified Files: LogEvent.java Log Message: Fixed the erroneous type on the log event Index: LogEvent.java =================================================================== RCS file: /cvsroot/gbif/portal-core/src/main/java/org/gbif/portal/util/log/LogEvent.java,v retrieving revision 1.15 retrieving revision 1.16 diff -C2 -d -r1.15 -r1.16 *** LogEvent.java 18 Oct 2007 14:44:05 -0000 1.15 --- LogEvent.java 28 May 2009 09:01:20 -0000 1.16 *************** *** 69,72 **** --- 69,73 ---- public static final LogEvent EXTRACT_MISSINGACCEPTEDTAXON = new LogEvent("extractMissingAcceptedTaxon", EXTRACT_RANGE_START + 10); public static final LogEvent EXTRACT_AMBIGUOUSSCIENTIFICNAME = new LogEvent("extractAmbiguousScientificName", EXTRACT_RANGE_START + 11); + public static final LogEvent EXTRACT_TEMPORALISSUE = new LogEvent("extractTemporalParseIssue", EXTRACT_RANGE_START + 12); // 2001-2999 - User messages |
|
From: Tim <tim...@us...> - 2009-05-28 09:01:29
|
Update of /cvsroot/gbif/portal-index/src/main/java/org/gbif/portal/harvest/workflow/activity/occurrence In directory ddv4jf1.ch3.sourceforge.com:/tmp/cvs-serv5053/src/main/java/org/gbif/portal/harvest/workflow/activity/occurrence Modified Files: OccurrenceRecordSynchroniserActivity.java Log Message: Fixed the erroneous type on the log event Index: OccurrenceRecordSynchroniserActivity.java =================================================================== RCS file: /cvsroot/gbif/portal-index/src/main/java/org/gbif/portal/harvest/workflow/activity/occurrence/OccurrenceRecordSynchroniserActivity.java,v retrieving revision 1.52 retrieving revision 1.53 diff -C2 -d -r1.52 -r1.53 *** OccurrenceRecordSynchroniserActivity.java 29 Jul 2008 10:46:08 -0000 1.52 --- OccurrenceRecordSynchroniserActivity.java 28 May 2009 09:01:11 -0000 1.53 *************** *** 1093,1097 **** if(invalidDate){ ! GbifLogMessage rangeMessage = gbifLogUtils.createGbifLogMessage(context, LogEvent.EXTRACT_GEOSPATIALISSUE, "Invalid or unparsable date"); rangeMessage.setCountOnly(true); --- 1093,1098 ---- if(invalidDate){ ! ! GbifLogMessage rangeMessage = gbifLogUtils.createGbifLogMessage(context, LogEvent.EXTRACT_TEMPORALISSUE, "Invalid or unparsable date"); rangeMessage.setCountOnly(true); |
|
From: Jose C. <jos...@us...> - 2009-05-28 08:09:43
|
Update of /cvsroot/gbif/portal-core/db In directory ddv4jf1.ch3.sourceforge.com:/tmp/cvs-serv31173/db Modified Files: process.sql Log Message: added ; (semicolon) Index: process.sql =================================================================== RCS file: /cvsroot/gbif/portal-core/db/process.sql,v retrieving revision 1.1 retrieving revision 1.2 diff -C2 -d -r1.1 -r1.2 *** process.sql 26 May 2009 13:39:41 -0000 1.1 --- process.sql 28 May 2009 08:09:35 -0000 1.2 *************** *** 613,617 **** inner join taxon_concept p on c.parent_concept_id=p.id set c.parent_concept_id=null ! where p.rank>=c.rank select concat('Rollover complete: ', now()) as debug; --- 613,617 ---- inner join taxon_concept p on c.parent_concept_id=p.id set c.parent_concept_id=null ! where p.rank>=c.rank; select concat('Rollover complete: ', now()) as debug; |
|
From: Jose C. <jos...@us...> - 2009-05-27 09:40:19
|
Update of /cvsroot/gbif/portal-web/src/main/webapp/WEB-INF In directory ddv4jf1.ch3.sourceforge.com:/tmp/cvs-serv8952/src/main/webapp/WEB-INF Modified Files: tiles-def.xml occurrenceDispatcher-servlet.xml Log Message: Bug 16 Fix: In each individual species page, in the "Explore Occurrences" link, numbers of expected occurrence records has been specified Index: tiles-def.xml =================================================================== RCS file: /cvsroot/gbif/portal-web/src/main/webapp/WEB-INF/tiles-def.xml,v retrieving revision 1.146 retrieving revision 1.147 diff -C2 -d -r1.146 -r1.147 *** tiles-def.xml 15 May 2009 10:29:34 -0000 1.146 --- tiles-def.xml 27 May 2009 09:40:05 -0000 1.147 *************** *** 167,170 **** --- 167,175 ---- <put name="content" value="/WEB-INF/jsp/occurrence/counts/totalCount.jsp"/> </definition> + + <!-- Occurrence Searching - total count retrieved via ajax --> + <definition name="actionsOccurrenceCountView" extends="ajaxHtmlLayout"> + <put name="content" value="/WEB-INF/jsp/occurrence/counts/actionsOccurrenceCount.jsp"/> + </definition> <!-- Occurrence Points AJAX tile for retrieving groups of points via AJAX--> *************** *** 472,475 **** --- 477,488 ---- </definition> + <!-- Settings Tiles --> + <definition name="bugs" extends="mainLayout"> + <put name="subtitle" value="/WEB-INF/jsp/user/subtitle.jsp"/> + <put name="headcontent" value="/WEB-INF/jsp/user/headcontent.jsp"/> + <put name="content" value="/WEB-INF/jsp/bugs.jsp"/> + <put name="breadcrumbs" value=""/> + </definition> + <!-- Images Tile --> <definition name="thumbnails" extends="ajaxHtmlLayout"> Index: occurrenceDispatcher-servlet.xml =================================================================== RCS file: /cvsroot/gbif/portal-web/src/main/webapp/WEB-INF/occurrenceDispatcher-servlet.xml,v retrieving revision 1.100 retrieving revision 1.101 diff -C2 -d -r1.100 -r1.101 *** occurrenceDispatcher-servlet.xml 24 Feb 2009 15:38:52 -0000 1.100 --- occurrenceDispatcher-servlet.xml 27 May 2009 09:40:05 -0000 1.101 *************** *** 32,35 **** --- 32,36 ---- <prop key="/addOrUpdateFilters">filterComponentsController</prop> <prop key="/occurrenceCount">occurrenceFilterController</prop> + <prop key="/actionsOccurrenceCount">occurrenceFilterController</prop> <prop key="/search*.htm">occurrenceFilterController</prop> <prop key="/*points*.htm">occurrenceFilterController</prop> |
|
From: Jose C. <jos...@us...> - 2009-05-27 09:40:14
|
Update of /cvsroot/gbif/portal-web/src/main/webapp/WEB-INF/jsp/taxonomy/drilldown In directory ddv4jf1.ch3.sourceforge.com:/tmp/cvs-serv8952/src/main/webapp/WEB-INF/jsp/taxonomy/drilldown Modified Files: actions.jsp Log Message: Bug 16 Fix: In each individual species page, in the "Explore Occurrences" link, numbers of expected occurrence records has been specified Index: actions.jsp =================================================================== RCS file: /cvsroot/gbif/portal-web/src/main/webapp/WEB-INF/jsp/taxonomy/drilldown/actions.jsp,v retrieving revision 1.23 retrieving revision 1.24 diff -C2 -d -r1.23 -r1.24 *** actions.jsp 13 Mar 2008 08:18:45 -0000 1.23 --- actions.jsp 27 May 2009 09:40:05 -0000 1.24 *************** *** 1,3 **** --- 1,14 ---- <%@ include file="/common/taglibs.jsp"%> + <script type="text/javascript"> + <% //retrieve the count %> + var countCallback = { + success:function(o){document.getElementById("searchResultCount").innerHTML=o.responseText;}, + failure: function(o){} + } + YAHOO.util.Connect.asyncRequest('GET', + "<string:trim>${pageContext.request.contextPath}/occurrences/actionsOccurrenceCount?<gbif:criteria criteria="${occurrenceCriteria}"/><gbiftag:occurrenceFilterOptions/></string:trim>", + countCallback, + null); + </script> <div id="furtherActions"> <h4><spring:message code='actions.for'/> <gbif:taxonPrint concept="${taxonConcept}"/></h4> *************** *** 8,14 **** <td> <ul class="actionsListInline"> ! <gbif:isMajorRank concept="${taxonConcept}"> <li> ! <a href="${pageContext.request.contextPath}/occurrences/search.htm?<gbif:criteria criteria="${occurrenceCriteria}"/>"><spring:message code="explore.occurrences"/></a> </li> </gbif:isMajorRank> --- 19,25 ---- <td> <ul class="actionsListInline"> ! <gbif:isMajorRank concept="${taxonConcept}"> <li> ! <a href="${pageContext.request.contextPath}/occurrences/search.htm?<gbif:criteria criteria="${occurrenceCriteria}"/>"><spring:message code="explore.occurrences"/> <span id="searchResultCount"></span></a> </li> </gbif:isMajorRank> |
|
From: Jose C. <jos...@us...> - 2009-05-27 09:40:13
|
Update of /cvsroot/gbif/portal-web/src/main/java/org/gbif/portal/web/controller/occurrence In directory ddv4jf1.ch3.sourceforge.com:/tmp/cvs-serv8952/src/main/java/org/gbif/portal/web/controller/occurrence Modified Files: OccurrenceFilterController.java Log Message: Bug 16 Fix: In each individual species page, in the "Explore Occurrences" link, numbers of expected occurrence records has been specified Index: OccurrenceFilterController.java =================================================================== RCS file: /cvsroot/gbif/portal-web/src/main/java/org/gbif/portal/web/controller/occurrence/OccurrenceFilterController.java,v retrieving revision 1.66 retrieving revision 1.67 diff -C2 -d -r1.66 -r1.67 *** OccurrenceFilterController.java 14 May 2009 12:06:23 -0000 1.66 --- OccurrenceFilterController.java 27 May 2009 09:40:05 -0000 1.67 *************** *** 396,399 **** --- 396,424 ---- /** + * Determines the view based on the map provided, and if none found defaults to the + * "occurrenceFilter" view. + * @param request To get the view from + * @param response To write to + * @return The model and view for the filter params provided + */ + public ModelAndView actionsOccurrenceCount(HttpServletRequest request, HttpServletResponse response) throws Exception { + //retrieve the criteria from the request + CriteriaDTO criteria = CriteriaUtil.getCriteriaAndPopulate(request,occurrenceFilters.getFilters()); + ModelAndView mav = new ModelAndView("actionsOccurrenceCountView"); + //convert to triplets + List<PropertyStoreTripletDTO> triplets = queryHelper.getTriplets(occurrenceFilters.getFilters(), criteria, request, response); + triplets.add(new PropertyStoreTripletDTO(queryHelper.getQueryNamespace(), selectFieldSubject, returnPredicateSubject, "SERVICE.OCCURRENCE.QUERY.RETURNFIELDS.ID")); + //do the query + SearchResultsDTO searchResults = countsQueryManager.doTripletQuery(triplets, true, new SearchConstraints(0, maxCount+1)); + Integer recordCount = (Integer) searchResults.getResults().size(); + //construct the model + mav.addObject(criteriaRequestKey, criteria); + mav.addObject(filtersRequestKey, occurrenceFilters.getFilters()); + mav.addObject("maxCount", maxCount); + mav.addObject("recordCount", recordCount); + return mav; + } + + /** * Prototype full screen bounding box wizard. * |
|
From: Jose C. <jos...@us...> - 2009-05-27 09:40:12
|
Update of /cvsroot/gbif/portal-web/src/main/webapp/WEB-INF/classes In directory ddv4jf1.ch3.sourceforge.com:/tmp/cvs-serv8952/src/main/webapp/WEB-INF/classes Modified Files: messages.properties applicationContext-occurrence-filters.xml Log Message: Bug 16 Fix: In each individual species page, in the "Explore Occurrences" link, numbers of expected occurrence records has been specified Index: applicationContext-occurrence-filters.xml =================================================================== RCS file: /cvsroot/gbif/portal-web/src/main/webapp/WEB-INF/classes/applicationContext-occurrence-filters.xml,v retrieving revision 1.18 retrieving revision 1.19 diff -C2 -d -r1.18 -r1.19 *** applicationContext-occurrence-filters.xml 15 May 2009 10:29:34 -0000 1.18 --- applicationContext-occurrence-filters.xml 27 May 2009 09:40:05 -0000 1.19 *************** *** 699,703 **** <map> <entry key="0" value="imageurl.with.url"/> - <entry key="1" value="imageurl.without.url"/> </map> </property> --- 699,702 ---- Index: messages.properties =================================================================== RCS file: /cvsroot/gbif/portal-web/src/main/webapp/WEB-INF/classes/messages.properties,v retrieving revision 1.265 retrieving revision 1.266 diff -C2 -d -r1.265 -r1.266 *** messages.properties 25 May 2009 14:42:07 -0000 1.265 --- messages.properties 27 May 2009 09:40:05 -0000 1.266 *************** *** 295,298 **** --- 295,303 ---- occurrence.search.filter.matched.many=This search matches {0} occurrence records. + occurrence.search.actions.retrievingcount=(Retrieving count....) + occurrence.search.actions.matched.over=(Over {0} records) + occurrence.search.actions.matched.one=(1 record) + occurrence.search.actions.matched.many=({0} records) + # Occurrence Search Results Table occurrence.search.results=Occurrence search results |
|
From: Jose C. <jos...@us...> - 2009-05-27 09:40:12
|
Update of /cvsroot/gbif/portal-web/src/main/webapp/WEB-INF/jsp/occurrence/counts In directory ddv4jf1.ch3.sourceforge.com:/tmp/cvs-serv8952/src/main/webapp/WEB-INF/jsp/occurrence/counts Added Files: actionsOccurrenceCount.jsp Log Message: Bug 16 Fix: In each individual species page, in the "Explore Occurrences" link, numbers of expected occurrence records has been specified --- NEW FILE: actionsOccurrenceCount.jsp --- <%@ include file="/common/taglibs.jsp"%><string:trim> <c:choose> <c:when test="${recordCount>maxCount}"> <spring:message code="occurrence.search.actions.matched.over" arguments="${maxCount}"/> </c:when> <c:when test="${recordCount==1}"> <spring:message code="occurrence.search.actions.matched.one"/> </c:when> <c:otherwise> <spring:message code="occurrence.search.actions.matched.many" arguments="${recordCount}"/> </c:otherwise> </c:choose> </string:trim> |
|
From: Tim <tim...@us...> - 2009-05-26 14:17:01
|
Update of /cvsroot/gbif/portal-core/db/deprecated In directory ddv4jf1.ch3.sourceforge.com:/tmp/cvs-serv13450/db/deprecated Log Message: Directory /cvsroot/gbif/portal-core/db/deprecated added to the repository |
|
From: Tim <tim...@us...> - 2009-05-26 14:17:00
|
Update of /cvsroot/gbif/portal-index/db In directory ddv4jf1.ch3.sourceforge.com:/tmp/cvs-serv13463/db Removed Files: process.sql Log Message: Created a proper DDL from the LIVE portal and appropriate inoput data to get setup. Run a portal.ddl, initPortal.sql, process.sql to get set up properly --- process.sql DELETED --- |
|
From: Tim <tim...@us...> - 2009-05-26 14:16:55
|
Update of /cvsroot/gbif/portal-core/db/deprecated In directory ddv4jf1.ch3.sourceforge.com:/tmp/cvs-serv13471/db/deprecated Added Files: portal.sql altitude_depth.sql accepted_concept_fix.sql deprecated-portal.sql init-portal.sql portal-simple.clay occurrence_ns.sql port.sql Log Message: Created a proper DDL from the LIVE portal and appropriate inoput data to get setup. Run a portal.ddl, initPortal.sql, process.sql to get set up properly --- NEW FILE: init-portal.sql --- /** * This is a script that allocates indexes to predefined buffers in the server. * To run as on startup add the --init-file=/etc/mysql/init-portal.sql * E.g.: * mysqld_safe --init-file=/etc/mysql/init-portal.sql & * * To use this, one must have something like the following in the my.cnf file: # Create some custom key buffers that will hold specific indexes # These are huge caches designed to get the entire index into memory taxon_concept_cache.key_buffer_size=2000M # caches for the occurrence_record occurrence_record_cache_1.key_buffer_size=3990M occurrence_record_cache_2.key_buffer_size=3990M occurrence_record_cache_3.key_buffer_size=3990M occurrence_record_cache_4.key_buffer_size=3990M occurrence_record_cache_5.key_buffer_size=3990M occurrence_record_cache_6.key_buffer_size=3990M occurrence_record_cache_7.key_buffer_size=3990M occurrence_record_cache_8.key_buffer_size=3990M */ /** * Cache the entire TC index in the buffer */ cache index taxon_concept in taxon_concept_cache; load index into cache taxon_concept; /** * This makes an educated guess at splitting up the indexes on the occurrence record into sensible chunks */ cache index occurrence_record index(ix_or_k_cell_mod_cell,ix_or_data_resource_id,ix_or_occurrence_date) in occurrence_record_cache_1; cache index occurrence_record index(ix_or_p_cell_mod_cell,ix_or_institution_code,ix_or_month) in occurrence_record_cache_2; cache index occurrence_record index(ix_or_c_cell_mod_cell,ix_or_collection_code,ix_or_data_provider_id) in occurrence_record_cache_3; cache index occurrence_record index(ix_or_o_cell_mod_cell,ix_or_catalogue_number) in occurrence_record_cache_4; cache index occurrence_record index(ix_or_f_cell_mod_cell,ix_or_taxon_concept_id) in occurrence_record_cache_5; cache index occurrence_record index(ix_or_g_cell_mod_cell,ix_or_taxon_name_id) in occurrence_record_cache_6; cache index occurrence_record index(ix_or_s_cell_mod_cell,ix_or_iso_country_code,ix_or_year) in occurrence_record_cache_7; cache index occurrence_record index(ix_or_cell_mod_cell_nub, ix_or_nub_country) in occurrence_record_cache_8; load index into cache occurrence_record; --- NEW FILE: portal.sql --- -- occurrence record create table occurrence_record ( id int unsigned not null , data_provider_id smallint unsigned not null , data_resource_id smallint unsigned not null , institution_code_id smallint unsigned not null , collection_code_id mediumint unsigned not null , catalogue_number_id int unsigned not null , taxon_concept_id int unsigned not null , taxon_name_id mediumint unsigned not null , kingdom_concept_id int unsigned , phylum_concept_id int unsigned , class_concept_id int unsigned , order_concept_id int unsigned , family_concept_id int unsigned , genus_concept_id int unsigned , species_concept_id int unsigned , nub_concept_id int unsigned , iso_country_code char(2) [...1064 lines suppressed...] insert into entity_type (id, entity_type) values (7, 'occurrence'); -- country tags insert into tag (id, name, entity_type, tag_table, description) values (6000, 'host_country_country_kingdom_basis', 6, 'quad_relation_tag','A breakdown of hosts by country, kingom and basis of record'); -- data resource tags insert into tag (id, name, entity_type, tag_table, description) values (4000, 'data_resource_keywords', 4, 'string_tag','Keywords associated with this dataset'); insert into tag (id, name, entity_type, tag_table, description) values (4100, 'data_resource_occurrences_country', 4, 'bi_relation_tag', 'This data resource has this many occurrences in this country'); insert into tag (id, name, entity_type, tag_table, description) values (4101, 'data_resource_occurrences_bounding_box', 4, 'geographic_coverage_tag', 'This data resource has occurrence data within this bounding box'); insert into tag (id, name, entity_type, tag_table, description) values (4102, 'data_resource_occurrences_wkt_polygon', 4, 'string_tag', 'A WKT Polygon describing the occurrences for this data resource'); insert into tag (id, name, entity_type, tag_table, description) values (4120, 'data_resource_occurrences_date_range', 4, 'temporal_coverage_tag', 'The temporal coverage of a data resource '); insert into tag (id, name, entity_type, tag_table, description) values (4121, 'data_resource_occurrences_month', 4, 'number_tag', 'The months this data resource has data for'); insert into tag (id, name, entity_type, tag_table, description) values (4140, 'data_resource_occurrences_species', 4, 'bi_relation_tag', 'This data resource has this many occurrences for this species'); insert into tag (id, name, entity_type, tag_table, description) values (4141, 'data_resource_occurrences_genus', 4, 'bi_relation_tag', 'This data resource has this many occurrences for this genus'); insert into tag (id, name, entity_type, tag_table, description) values (4142, 'data_resource_occurrences_family', 4, 'bi_relation_tag','This data resource has this many occurrences for this family'); insert into tag (id, name, entity_type, tag_table, description) values (4150, 'data_resource_taxonomic_scope', 4, 'bi_relation_tag','The taxonomic scope for this resource. Points to a nub concept'); insert into tag (id, name, entity_type, tag_table, description) values (4151, 'data_resource_associated_kingdom', 4, 'bi_relation_tag','This kingdom this data resource contains data for'); insert into tag (id, name, entity_type, tag_table, description) values (4152, 'data_resource_common_names', 4, 'bi_relation_tag','Species common names associated with this resource'); insert into tag (id, name, entity_type, tag_table, description) values (4160, 'data_resource_contains_type_specimens', 4, 'number_tag','Indicates whether a data resource contains type records'); insert into tag (id, name, entity_type, tag_table, description) values (4161, 'data_resource_collector', 4, 'string_tag','Names of collectors who have collected for this dataset'); --- NEW FILE: accepted_concept_fix.sql --- update taxon_concept tc inner join relationship_assertion ra ON ra.from_concept_id=tc.id SET is_accepted=false; update taxon_concept tc set tc.is_accepted=true where tc.is_accepted is NULL; --- NEW FILE: port.sql --- -- after the original data is loaded in -- clean up the temp tables drop table col_vernacular_name; drop table distribution; drop table duplicated_taxon_names; drop table extract; drop table nbn_tab; drop table publication; drop table range_stats; drop table temp_MOBOT; drop table temp_fishbase; drop table temp_nbn; drop table temp_to_remove; drop table temp_to_remove2; drop view view_indexing; drop view view_nubbed; drop view classification; -- ROR can be modified alter table raw_occurrence_record modify column data_provider_id smallint, modify column data_resource_id smallint, modify column resource_access_point_id smallint, modify column created timestamp null default null, modify column modified timestamp null default null, modify column deleted timestamp null default null, drop index IX_raw_occurrence_record_4, drop index IX_raw_occurrence_record_5, drop index IX_raw_occurrence_record_6, drop index IX_raw_occurrence_record_7, drop index ix_scientific_name, drop index ix_data_provider_id, drop index IX_RAP_MOD_ID, drop index IX_DR_INST_COLL_CAT, add index (created, modified), add index (data_resource_id, catalogue_number), add index (resource_access_point_id, id); -- cell densities will be recreated, so let's just recreate tables drop table cell_density; drop table centi_cell_density; create table cell_density ( type smallint unsigned not null , entity_id int unsigned not null , cell_id smallint unsigned not null , count int unsigned , primary key (type, entity_id, cell_id) ) engine=myisam; create table centi_cell_density ( type smallint unsigned not null , entity_id int unsigned not null , cell_id smallint unsigned not null , centi_cell_id tinyint unsigned not null , count int unsigned , primary key (type, entity_id, cell_id, centi_cell_id) ) engine=myisam; -- taxon name rename table taxon_name to old_taxon_name; create table taxon_name ( id mediumint unsigned not null auto_increment , canonical varchar(255) not null , supra_generic varchar(255) , generic varchar(255) , infrageneric varchar(255) , specific_epithet varchar(255) , infraspecific varchar(255) , infraspecific_marker varchar(255) , is_hybrid bool , rank smallint unsigned not null , author varchar(255) , searchable_canonical varchar(255) , primary key (id) ) engine=myisam; create index ix_taxon_name_canonical_rank on taxon_name (canonical, rank); create index ix_taxon_name_searchable_canonical_rank on taxon_name (searchable_canonical, rank); insert into taxon_name select * from old_taxon_name; -- taxon concept rename table taxon_concept to old_taxon_concept; create table taxon_concept ( id int unsigned not null auto_increment , rank smallint unsigned not null , taxon_name_id mediumint unsigned not null , data_provider_id smallint unsigned not null , data_resource_id smallint unsigned not null , parent_concept_id int unsigned , kingdom_concept_id int unsigned , phylum_concept_id int unsigned , class_concept_id int unsigned , order_concept_id int unsigned , family_concept_id int unsigned , genus_concept_id int unsigned , species_concept_id int unsigned , is_accepted bool default true , is_nub_concept bool default false , partner_concept_id int unsigned , priority smallint not null default 100 , is_secondary bool default false , created timestamp null default null , modified timestamp null default null , deleted timestamp null default null , primary key (id) ) engine=myisam; create index ix_taxon_concept_dr_rank on taxon_concept(data_resource_id, rank); create index ix_taxon_concept_partner_dr on taxon_concept(partner_concept_id,data_resource_id); create index ix_taxon_concept_tn_dr on taxon_concept(taxon_name_id, data_resource_id); create index ix_taxon_concept_parent on taxon_concept(parent_concept_id); create index ix_taxon_concept_k on taxon_concept(kingdom_concept_id); create index ix_taxon_concept_p on taxon_concept(phylum_concept_id); create index ix_taxon_concept_c on taxon_concept(class_concept_id); create index ix_taxon_concept_o on taxon_concept(order_concept_id); create index ix_taxon_concept_f on taxon_concept(family_concept_id); create index ix_taxon_concept_g on taxon_concept(genus_concept_id); create index ix_taxon_concept_s on taxon_concept(species_concept_id); insert into taxon_concept select id,rank,taxon_name_id,data_provider_id,data_resource_id,parent_concept_id,kingdom_concept_id,phylum_concept_id,class_concept_id,order_concept_id,family_concept_id,genus_concept_id,species_concept_id,is_accepted,is_nub_concept,partner_concept_id,priority,is_secondary,created,modified,deleted from old_taxon_concept; -- remote_concept rename table remote_concept to old_remote_concept; create table remote_concept ( id int not null auto_increment , taxon_concept_id int not null , remote_id varchar(255) , id_type smallint unsigned not null , modified timestamp , primary key (id) ) engine=myisam; create index ix_remote_concept_taxon_concept_type_remote_id on remote_concept (taxon_concept_id, id_type, remote_id); insert into remote_concept select * from old_remote_concept; -- gbif log message rename table gbif_log_message to old_gbif_log_message; create table gbif_log_message ( id int unsigned not null auto_increment , portal_instance_id tinyint unsigned , log_group_id int unsigned , event_id mediumint unsigned default 0 , level smallint unsigned , data_provider_id smallint unsigned , data_resource_id smallint unsigned , occurrence_record_id int unsigned , taxon_concept_id int unsigned , user_id smallint unsigned , message text , restricted tinyint(1) unsigned , count int unsigned , timestamp timestamp not null , primary key (id) ) engine=myisam; create index ix_gbif_log_message_log_group on gbif_log_message (log_group_id asc); create index ix_gbif_log_message_instance on gbif_log_message (portal_instance_id asc); create index ix_gbif_log_message_level on gbif_log_message (level asc); create index ix_gbif_log_message_event on gbif_log_message (event_id asc); create index ix_gbif_log_message_date on gbif_log_message (timestamp asc); create index ix_gbif_log_message_user on gbif_log_message (user_id asc); create index ix_gbif_log_message_instance_log_group on gbif_log_message (portal_instance_id asc, log_group_id asc); create index ix_gbif_log_message_resource_event_timestamp on gbif_log_message (data_resource_id, event_id, timestamp); create index ix_gbif_log_message_provider_event_timestamp on gbif_log_message (data_provider_id, event_id, timestamp); create index ix_gbif_log_message_occurrence_event_timestamp on gbif_log_message (occurrence_record_id, event_id, timestamp); insert into gbif_log_message select * from old_gbif_log_message; -- institution code create table institution_code( id smallint unsigned not null auto_increment, code varchar(255) not null, primary key (id) ) engine=myisam; insert ignore into institution_code(code) select distinct institution_code from occurrence_record; create index ix_ic_institution_code on institution_code (code); -- collection code create table collection_code( id mediumint unsigned not null auto_increment, code varchar(255) not null, primary key (id) ) engine=myisam; insert ignore into collection_code(code) select distinct collection_code from occurrence_record; create index ix_cc_collection_code on collection_code (code); -- catalogue number create table catalogue_number( id int unsigned not null auto_increment, code varchar(255) not null, primary key (id) ) engine=myisam; insert ignore into catalogue_number(code) select distinct catalogue_number from occurrence_record; create index ix_cn_catalogue_number on catalogue_number (code); -- This get modified schemas so let's move them out the way then create and populate later rename table occurrence_record to old_occurrence_record; update old_occurrence_record set month=null where month<1 or month>12; update old_occurrence_record set year=null where year<1700 or year>2008; create table occurrence_record ( id int unsigned not null , data_provider_id smallint unsigned not null , data_resource_id smallint unsigned not null , institution_code_id smallint unsigned not null , collection_code_id mediumint unsigned not null , catalogue_number_id int unsigned not null , taxon_concept_id int unsigned not null , taxon_name_id mediumint unsigned not null , kingdom_concept_id int unsigned , phylum_concept_id int unsigned , class_concept_id int unsigned , order_concept_id int unsigned , family_concept_id int unsigned , genus_concept_id int unsigned , species_concept_id int unsigned , nub_concept_id int unsigned , iso_country_code char(2) , latitude float , longitude float , cell_id smallint unsigned , centi_cell_id tinyint unsigned , mod360_cell_id smallint unsigned , year smallint unsigned , month tinyint unsigned , occurrence_date date , basis_of_record tinyint unsigned not null default 0 , taxonomic_issue tinyint unsigned not null default 0 , geospatial_issue tinyint unsigned not null default 0 , other_issue tinyint unsigned not null default 0 , deleted timestamp null default null , primary key (id) ) engine=myisam; insert ignore into occurrence_record(id,data_provider_id,data_resource_id,institution_code_id,collection_code_id,catalogue_number_id,taxon_concept_id,taxon_name_id, nub_concept_id,iso_country_code,latitude,longitude,cell_id,mod360_cell_id,centi_cell_id,year,month,occurrence_date,basis_of_record,taxonomic_issue,geospatial_issue,other_issue,deleted) select straight_join o.raw_occurrence_record_id, o.data_provider_id, o.data_resource_id, i.id, c.id, n.id, o.taxon_concept_id, o.taxon_name_id, o.nub_concept_id, o.iso_country_code, o.latitude, o.longitude, o.cell_id, mod(o.cell_id, 360), o.centi_cell_id, o.year, o.month, o.occurrence_date, o.basis_of_record, o.taxonomic_issue, o.geospatial_issue, o.other_issue, o.deleted from old_occurrence_record o inner join catalogue_number n on n.code = o.catalogue_number inner join institution_code i on i.code = o.institution_code inner join collection_code c on c.code = o.collection_code; show warnings; alter table occurrence_record add index ix_or_data_provider_id (data_provider_id), add index ix_or_data_resource_id (data_resource_id), add index ix_or_institution_code (institution_code_id), add index ix_or_collection_code (collection_code_id), add index ix_or_catalogue_number (catalogue_number_id), add index ix_or_taxon_concept_id (taxon_concept_id), add index ix_or_taxon_name_id (taxon_name_id), add index ix_or_iso_country_code (iso_country_code), add index ix_or_occurrence_date (occurrence_date), add index ix_or_month (month), add index ix_or_year (year), add index ix_or_k_cell_mod_cell (kingdom_concept_id, cell_id, mod360_cell_id), add index ix_or_p_cell_mod_cell (phylum_concept_id, cell_id, mod360_cell_id), add index ix_or_c_cell_mod_cell (class_concept_id, cell_id, mod360_cell_id), add index ix_or_o_cell_mod_cell (order_concept_id, cell_id, mod360_cell_id), add index ix_or_f_cell_mod_cell (family_concept_id, cell_id, mod360_cell_id), add index ix_or_g_cell_mod_cell (genus_concept_id, cell_id, mod360_cell_id), add index ix_or_s_cell_mod_cell (species_concept_id, cell_id, mod360_cell_id), add index ix_or_cell_mod_cell_nub (cell_id, mod360_cell_id, nub_concept_id), add index ix_or_nub_country (nub_concept_id, iso_country_code); -- image record rename table image_record to old_image_record; create table image_record ( id int unsigned not null auto_increment , data_resource_id smallint unsigned not null , occurrence_id int unsigned , taxon_concept_id int unsigned , raw_image_type varchar(128) , image_type tinyint unsigned not null default 0 , url varchar(255) not null , description text , rights text , html_for_display text , primary key (id) ) engine=myisam; create index ix_image_record_occurrence on image_record (occurrence_id asc); create index ix_image_record_resource_taxon_concept on image_record (data_resource_id, taxon_concept_id); insert into image_record select id,data_resource_id,raw_occurrence_record_id,taxon_concept_id,raw_image_type,image_type,url,description,rights,html_for_display from old_image_record; -- identifier record rename table identifier_record to old_identifier_record; create table identifier_record ( id int unsigned not null auto_increment , data_resource_id smallint unsigned not null , occurrence_id int unsigned , identifier_type smallint unsigned not null default 0 , identifier varchar(255) not null , primary key (id) ) engine=myisam; create index ix_identifier_record_occurrence on identifier_record (occurrence_id asc); insert into identifier_record select id,data_resource_id,raw_occurrence_record_id,identifier_type,identifier from old_identifier_record; -- link record rename table link_record to old_link_record; create table link_record ( id int unsigned not null auto_increment , data_resource_id smallint unsigned not null , occurrence_id int unsigned , taxon_concept_id int unsigned , raw_link_type varchar(128) , link_type smallint unsigned not null default 0 , url varchar(255) not null , description text , primary key (id) ) engine=myisam; create index ix_link_record_occurrence on link_record (occurrence_id asc); create index ix_link_record_taxon_concept on link_record (taxon_concept_id asc); insert into link_record select id,data_resource_id,raw_occurrence_record_id,taxon_concept_id,raw_link_type,link_type,url,description from old_link_record; -- typification record rename table typification_record to old_typification_record; create table typification_record ( id int unsigned not null auto_increment , data_resource_id smallint unsigned not null , occurrence_id int unsigned , taxon_name_id mediumint unsigned , scientific_name varchar(255) , publication varchar(255) , type_status varchar(255) not null , notes text , primary key (id) ) engine=myisam; create index ix_typification_record_occurrence on typification_record (occurrence_id asc); create index ix_typification_record_name on typification_record (taxon_name_id asc); create index ix_typification_record_resource on typification_record (data_resource_id asc); insert into typification_record select id,data_resource_id,raw_occurrence_record_id,taxon_name_id,scientific_name,publication,type_status,notes from old_typification_record; -- deduplicate the taxon_names create table taxon_name_unique ( id mediumint unsigned not null auto_increment , canonical varchar(255) not null , supra_generic varchar(255) , generic varchar(255) , infrageneric varchar(255) , specific_epithet varchar(255) , infraspecific varchar(255) , infraspecific_marker varchar(255) , is_hybrid bool , rank smallint unsigned not null , author varchar(255) , searchable_canonical varchar(255) , primary key (id) , unique key (canonical(200), author(100), rank) ) engine=myisam; insert ignore into taxon_name_unique select * from taxon_name; create index ix_taxon_name_unique_canonical_rank on taxon_name_unique (canonical, rank); create table tn_duplicate ( unique_id mediumint unsigned, non_unique_id mediumint unsigned ) engine=myisam; -- put in the ones that are not meant to be there insert into tn_duplicate(non_unique_id) select tn.id from taxon_name tn left join taxon_name_unique tnu on tn.id=tnu.id where tnu.id is null; -- add an index create index ix_tn_dup_non_unique on tn_duplicate (non_unique_id); -- now update what they should be update taxon_name tn inner join tn_duplicate tnd on tnd.non_unique_id=tn.id inner join taxon_name_unique tnu on tn.canonical=tnu.canonical and tn.rank=tnu.rank and tn.author=tnu.author set tnd.unique_id=tnu.id; -- add an index to check for error create index ix_tn_dup_unique on tn_duplicate (unique_id); -- now do some updates to correct the misused TN IDs update typification_record tr inner join tn_duplicate tnd on tr.taxon_name_id = tnd.non_unique_id set tr.taxon_name_id=tnd.unique_id; update taxon_concept tc inner join tn_duplicate tnd on tc.taxon_name_id = tnd.non_unique_id set tc.taxon_name_id=tnd.unique_id; update occurrence_record o inner join tn_duplicate tnd on o.taxon_name_id = tnd.non_unique_id set o.taxon_name_id=tnd.unique_id; delete tn.* from taxon_name tn inner join tn_duplicate tnd on tnd.non_unique_id=tn.id; --- NEW FILE: occurrence_ns.sql --- -- occurrence record create table occurrence_record_ns ( id int unsigned not null , data_provider_id smallint unsigned not null , data_resource_id smallint unsigned not null , institution_code_id smallint unsigned not null , collection_code_id mediumint unsigned not null , catalogue_number_id int unsigned not null , taxon_concept_id int unsigned not null , taxon_name_id int unsigned not null , nub_concept_id int unsigned , left_concept_id mediumint unsigned , iso_country_code char(2) , latitude float , longitude float , cell_id smallint unsigned , centi_cell_id tinyint unsigned , mod360_cell_id smallint unsigned , altitude_metres smallint signed , depth_centimetres mediumint unsigned , year smallint unsigned , month tinyint unsigned , occurrence_date date , basis_of_record tinyint unsigned not null default 0 , taxonomic_issue tinyint unsigned not null default 0 , geospatial_issue mediumint unsigned not null default 0 , other_issue tinyint unsigned not null default 0 , modified timestamp null default null , deleted timestamp null default null , primary key (id) ) engine=myisam; alter table occurrence_record_ns add index ix_or_data_provider_id (data_provider_id), add index ix_or_data_resource_id (data_resource_id), add index ix_or_institution_code (institution_code_id), add index ix_or_collection_code (collection_code_id), add index ix_or_catalogue_number (catalogue_number_id), add index ix_or_taxon_name_id (taxon_name_id), add index ix_or_taxon_concept_id (taxon_concept_id), add index ix_or_iso_country_code (iso_country_code), add index ix_or_occurrence_date (occurrence_date), add index ix_or_month (month), add index ix_or_year (year), add index ix_or_altitude_metres (altitude_metres), add index ix_or_depth_centimetres (depth_centimetres), add index ix_or_left_concept_id_cell_mod_cell (left_concept_id, cell_id, mod360_cell_id), add index ix_or_cell_mod_cell (cell_id, mod360_cell_id), add index ix_or_nub_country (nub_concept_id, iso_country_code); insert into occurrence_record_ns select id , data_provider_id , data_resource_id , institution_code_id , collection_code_id , catalogue_number_id , taxon_concept_id , taxon_name_id , nub_concept_id , NULL , iso_country_code , latitude , longitude , cell_id , centi_cell_id , mod360_cell_id , NULL , NULL , year , month , occurrence_date , basis_of_record , taxonomic_issue , geospatial_issue , other_issue , NULL , deleted from occurrence_record; --- NEW FILE: portal-simple.clay --- <?xml version="1.0" encoding="UTF-8" standalone="yes"?> <clay-model clay-version="1.4.0"> <database-model alias="" author="" begin-script="" end-script="" name="portal-simple" remarks="" sql-dialect-id="jp.azzurri.clay.dialect.MySQL_5_0_Dialect" uid="23825c:11958d5c71e:-7e37" version="1.0"> <database-model-description></database-model-description> <schema-list> <schema alias="" name="DEFAULT_SCHEMA" remarks="" uid="23825c:11958d5c71e:-7e35"> <schema-description></schema-description> <domain-list/> <table-list> <table alias="" name="common_name" remarks="" uid="23825c:11958d5c71e:-7e34"> <table-description></table-description> <table-figure-bounds height="88" width="118" x="888" y="-434"/> <column-list> <column alias="" auto-increment="true" column-size="10" decimal-digits="0" default-value="" mandatory="true" name="id" remarks="" uid="23825c:11958d5c71e:-7e32"> <column-description></column-description> <data-type jdbc-type="4" name="INT" selected-variant-pattern="INT(%n)"> <variant precision-max="10" precision-min="0" precision-variable="%n" type-name-pattern="INT(%n)"/> <variant type-name-pattern="INT"/> <variant precision-max="10" precision-min="0" precision-variable="%n" type-name-pattern="INT(%n) UNSIGNED"/> [...3374 lines suppressed...] <variant precision-max="10" precision-min="0" precision-variable="%n" type-name-pattern="INT(%n) UNSIGNED ZEROFILL"/> <variant precision-max="10" precision-min="0" precision-variable="%n" type-name-pattern="INT(%n) ZEROFILL"/> <variant type-name-pattern="INT UNSIGNED"/> <variant type-name-pattern="INT UNSIGNED ZEROFILL"/> <variant type-name-pattern="INT ZEROFILL"/> </data-type> </column> </column-list> <primary-key alias="" name="PK_CENTI_CELL_DENSITY" remarks="" uid="bbd19b:11970b2512f:-797a"> <primary-key-description></primary-key-description> </primary-key> <unique-key-list/> <foreign-key-list/> <index-list/> </table> </table-list> </schema> </schema-list> </database-model> </clay-model> --- NEW FILE: altitude_depth.sql --- alter table occurrence_record add altitude_metres smallint signed, add depth_centimetres mediumint unsigned, add index ix_or_altitude_metres (altitude_metres), add index ix_or_depth_centimetres (depth_centimetres); --- NEW FILE: deprecated-portal.sql --- /** * This is the real database script */ CREATE TABLE data_provider ( id INT NOT NULL AUTO_INCREMENT , name VARCHAR(255) , description TEXT , address VARCHAR(255) , website_url VARCHAR(255) , logo_url VARCHAR(255) , email VARCHAR(255) , telephone VARCHAR(255) , uuid CHAR(50) , concept_count INT DEFAULT 0 , higher_concept_count INT DEFAULT 0 , species_count INT DEFAULT 0 , occurrence_count INT DEFAULT 0 , occurrence_coordinate_count INT DEFAULT 0 , created DATETIME , modified DATETIME , deleted DATETIME , iso_country_code CHAR(2) , stated_count_served INT , gbif_approver VARCHAR(150) , lock_description TINYINT(1) NOT NULL DEFAULT 0 , lock_iso_country_code TINYINT(1) NOT NULL DEFAULT 0 , data_resource_count INT DEFAULT 0 , PRIMARY KEY (id) ) ENGINE=MYISAM DATA DIRECTORY='/var/lib/mysql/portal/extra_d' INDEX DIRECTORY='/var/lib/mysql/portal/extra_i'; CREATE INDEX IX_data_provider_name ON data_provider (name ASC); CREATE INDEX IX_data_provider_country ON data_provider (iso_country_code ASC); CREATE TABLE raw_occurrence_record ( id INT NOT NULL AUTO_INCREMENT , data_provider_id INT NOT NULL , data_resource_id INT NOT NULL , resource_access_point_id INT NOT NULL , institution_code VARCHAR(255) , collection_code VARCHAR(255) , catalogue_number VARCHAR(255) , scientific_name VARCHAR(255) , author VARCHAR(255) , rank VARCHAR(50) , kingdom VARCHAR(150) , phylum VARCHAR(150) , class VARCHAR(250) , order_rank VARCHAR(50) , family VARCHAR(250) , genus VARCHAR(150) , species VARCHAR(150) , subspecies VARCHAR(150) , latitude VARCHAR(50) , longitude VARCHAR(50) , lat_long_precision VARCHAR(50) , max_altitude VARCHAR(50) , min_altitude VARCHAR(50) , altitude_precision VARCHAR(50) , min_depth VARCHAR(50) , max_depth VARCHAR(50) , depth_precision VARCHAR(50) , continent_ocean VARCHAR(100) , country VARCHAR(100) , state_province VARCHAR(100) , county VARCHAR(100) , collector_name VARCHAR(255) , locality TEXT , year VARCHAR(50) , month VARCHAR(50) , day VARCHAR(50) , basis_of_record VARCHAR(100) , identifier_name VARCHAR(255) , identification_date DATETIME , unit_qualifier VARCHAR(255) , created DATETIME , modified DATETIME , deleted DATETIME , PRIMARY KEY (id) ) ENGINE=MYISAM DATA DIRECTORY='/var/lib/mysql/portal/extra_d' INDEX DIRECTORY='/var/lib/mysql/portal/extra_i'; CREATE INDEX IX_raw_occurrence_record_4 ON raw_occurrence_record (data_resource_id ASC); CREATE INDEX IX_raw_occurrence_record_5 ON raw_occurrence_record (resource_access_point_id ASC); CREATE INDEX IX_raw_occurrence_record_6 ON raw_occurrence_record (created ASC); CREATE INDEX IX_raw_occurrence_record_7 ON raw_occurrence_record (modified ASC); CREATE INDEX IX_DR_INST_COLL_CAT_UNIT ON raw_occurrence_record (data_resource_id,institution_code(10),collection_code(10),catalogue_number(25),unit_qualifier(10)); CREATE INDEX IX_RAP_MOD_ID ON raw_occurrence_record (resource_access_point_id,modified,id); CREATE TABLE occurrence_record ( id INT NOT NULL AUTO_INCREMENT , raw_occurrence_record_id INT NOT NULL , data_provider_id INT , data_resource_id INT , provider_iso_country_code CHAR(2) , institution_code VARCHAR(255) , collection_code VARCHAR(255) , catalogue_number VARCHAR(255) , taxon_concept_id INT NOT NULL , taxon_name_id INT NOT NULL , nub_concept_id INT , nub_name_id INT , iso_country_code CHAR(2) , latitude FLOAT , longitude FLOAT , cell_id INT , centi_cell_id SMALLINT , lat_long_precision INT , altitude_meters FLOAT , altitude_precision INT , depth_meters FLOAT , depth_precision INT , year INT , month INT , day INT , occurrence_date DATETIME , basis_of_record INT NOT NULL , collector_name VARCHAR(255) , locality VARCHAR(255) , taxonomic_issue TINYINT NOT NULL DEFAULT 0 , geospatial_issue TINYINT NOT NULL DEFAULT 0 , other_issue TINYINT NOT NULL DEFAULT 0 , created DATETIME , modified DATETIME , deleted DATETIME , PRIMARY KEY (id) ) ENGINE=MYISAM DATA DIRECTORY='/var/lib/mysql/portal/extra_d' INDEX DIRECTORY='/var/lib/mysql/portal/extra_i'; CREATE INDEX IX_occurrence_record_raw_occurrence_record_id ON occurrence_record (raw_occurrence_record_id ASC); CREATE INDEX IX_occurrence_record_data_provider_id ON occurrence_record (data_provider_id ASC); CREATE INDEX IX_occurrence_record_data_resource_id ON occurrence_record (data_resource_id ASC); CREATE INDEX IX_occurrence_record_institution_code ON occurrence_record (institution_code ASC); CREATE INDEX IX_occurrence_record_collection_code ON occurrence_record (collection_code ASC); CREATE INDEX IX_occurrence_record_catalogue_number ON occurrence_record (catalogue_number ASC); CREATE INDEX IX_occurrence_record_taxon_concept_id ON occurrence_record (taxon_concept_id ASC); CREATE INDEX IX_occurrence_record_taxon_name_id ON occurrence_record (taxon_name_id ASC); CREATE INDEX IX_occurrence_record_nub_concept_id ON occurrence_record (nub_concept_id ASC); CREATE INDEX IX_occurrence_record_latitude ON occurrence_record (latitude ASC); CREATE INDEX IX_occurrence_record_longitude ON occurrence_record (longitude ASC); CREATE INDEX IX_occurrence_record_cell_id ON occurrence_record (cell_id ASC); CREATE INDEX IX_occurrence_record_centi_cell_id ON occurrence_record (centi_cell_id ASC); CREATE INDEX IX_occurrence_record_iso_country_code ON occurrence_record (iso_country_code ASC); CREATE INDEX IX_occurrence_record_occurrence_date ON occurrence_record (occurrence_date ASC); CREATE INDEX IX_occurrence_record_month ON occurrence_record (month ASC); CREATE INDEX IX_occurrence_record_year ON occurrence_record (year ASC); CREATE INDEX IX_occurrence_record_geospatial_issue ON occurrence_record (geospatial_issue ASC); CREATE INDEX IX_occurrence_record_basis_of_record ON occurrence_record (basis_of_record ASC); CREATE INDEX IX_occurrence_record_modified ON occurrence_record (modified ASC); CREATE INDEX IX_LAT_LONG ON occurrence_record (latitude,longitude); CREATE INDEX IX_NUB_CELL ON occurrence_record (cell_id, nub_concept_id); CREATE INDEX IX_NUB_COUNTRY ON occurrence_record (iso_country_code, nub_concept_id); CREATE TABLE publication ( id INT NOT NULL AUTO_INCREMENT , title VARCHAR(255) NOT NULL , year VARCHAR(4) , PRIMARY KEY (id) ) ENGINE=MYISAM DATA DIRECTORY='/var/lib/mysql/portal/extra_d' INDEX DIRECTORY='/var/lib/mysql/portal/extra_i'; CREATE TABLE property_store_namespace ( id INT NOT NULL AUTO_INCREMENT , namespace VARCHAR(255) , PRIMARY KEY (id) ) ENGINE=MYISAM DATA DIRECTORY='/var/lib/mysql/portal/extra_d' INDEX DIRECTORY='/var/lib/mysql/portal/extra_i'; CREATE TABLE agent ( id INT NOT NULL AUTO_INCREMENT , name VARCHAR(255) NOT NULL , address VARCHAR(255) , email VARCHAR(255) , telephone VARCHAR(255) , created DATETIME , modified DATETIME , deleted DATETIME , PRIMARY KEY (id) ) ENGINE=MYISAM DATA DIRECTORY='/var/lib/mysql/portal/extra_d' INDEX DIRECTORY='/var/lib/mysql/portal/extra_i'; CREATE TABLE country ( id INT NOT NULL AUTO_INCREMENT , iso_country_code CHAR(2) , concept_count INT DEFAULT 0 , species_count INT DEFAULT 0 , occurrence_count INT DEFAULT 0 , occurrence_coordinate_count INT DEFAULT 0 , continent_code ENUM('AF', 'AS', 'EU', 'NA', 'OC', 'SA', 'AN') , region char(3) , min_latitude FLOAT , max_latitude FLOAT , min_longitude FLOAT , max_longitude FLOAT , PRIMARY KEY (id) ) ENGINE=MYISAM DATA DIRECTORY='/var/lib/mysql/portal/extra_d' INDEX DIRECTORY='/var/lib/mysql/portal/extra_i'; CREATE INDEX IX_country_iso_country_code ON country (iso_country_code ASC); CREATE INDEX IX_country_continent_code ON country (continent_code ASC); CREATE INDEX IX_country_region ON country (region ASC); CREATE TABLE ip_country ( id INT NOT NULL AUTO_INCREMENT , start CHAR(15) NOT NULL , end CHAR(15) NOT NULL , start_long BIGINT UNSIGNED , end_long BIGINT UNSIGNED , iso_country_code CHAR(12) , PRIMARY KEY (id) ) ENGINE=MYISAM DATA DIRECTORY='/var/lib/mysql/portal/extra_d' INDEX DIRECTORY='/var/lib/mysql/portal/extra_i'; CREATE INDEX IX_ip_country_range ON ip_country (start_long ASC, end_long ASC); CREATE TABLE cell_density ( type SMALLINT NOT NULL , concept_id INT NOT NULL , cell_id INT NOT NULL , count INT , PRIMARY KEY (type, concept_id, cell_id) ) ENGINE=MYISAM DATA DIRECTORY='/var/lib/mysql/portal/extra_d' INDEX DIRECTORY='/var/lib/mysql/portal/extra_i'; CREATE TABLE centi_cell_density ( type SMALLINT NOT NULL , concept_id INT NOT NULL , cell_id INT NOT NULL , centi_cell_id INT NOT NULL , count INT , PRIMARY KEY (type, concept_id, cell_id, centi_cell_id) ) ENGINE=MYISAM DATA DIRECTORY='/var/lib/mysql/portal/extra_d' INDEX DIRECTORY='/var/lib/mysql/portal/extra_i'; CREATE TABLE cell_country ( cell_id INT NOT NULL , iso_country_code CHAR(2) NOT NULL , PRIMARY KEY (cell_id, iso_country_code) ) ENGINE=MYISAM DATA DIRECTORY='/var/lib/mysql/portal/extra_d' INDEX DIRECTORY='/var/lib/mysql/portal/extra_i'; CREATE INDEX IX_cell_ids ON cell_country (cell_id ASC); CREATE INDEX IX_iso_country_codes ON cell_country (iso_country_code ASC); CREATE TABLE year_density ( type SMALLINT NOT NULL , entity_id INT NOT NULL , year INT NOT NULL , count INT , PRIMARY KEY (type, entity_id, year) ) ENGINE=MYISAM DATA DIRECTORY='/var/lib/mysql/portal/extra_d' INDEX DIRECTORY='/var/lib/mysql/portal/extra_i'; CREATE TABLE month_density ( type SMALLINT NOT NULL , entity_id INT NOT NULL , month INT NOT NULL , count INT , PRIMARY KEY (type, entity_id, month) ) ENGINE=MYISAM DATA DIRECTORY='/var/lib/mysql/portal/extra_d' INDEX DIRECTORY='/var/lib/mysql/portal/extra_i'; CREATE TABLE resource_country ( data_resource_id INT NOT NULL , iso_country_code CHAR(2) NOT NULL , count INT , PRIMARY KEY (data_resource_id, iso_country_code) ) ENGINE=MYISAM DATA DIRECTORY='/var/lib/mysql/portal/extra_d' INDEX DIRECTORY='/var/lib/mysql/portal/extra_i'; CREATE INDEX IX_resource_country_1 ON resource_country (data_resource_id ASC); CREATE INDEX IX_resource_country_2 ON resource_country (iso_country_code ASC); CREATE TABLE taxon_country ( taxon_concept_id INT NOT NULL , iso_country_code CHAR(2) NOT NULL , count INT , PRIMARY KEY (taxon_concept_id, iso_country_code) ) ENGINE=MYISAM DATA DIRECTORY='/var/lib/mysql/portal/extra_d' INDEX DIRECTORY='/var/lib/mysql/portal/extra_i'; CREATE INDEX IX_taxon_country_1 ON taxon_country (taxon_concept_id ASC); CREATE INDEX IX_taxon_country_2 ON taxon_country (iso_country_code ASC); CREATE TABLE resource_network ( id INT NOT NULL AUTO_INCREMENT , name VARCHAR(255) , code VARCHAR(50) , description TEXT , address VARCHAR(255) , website_url VARCHAR(255) , logo_url VARCHAR(255) , email VARCHAR(255) , telephone VARCHAR(255) , concept_count INT DEFAULT 0 , species_count INT DEFAULT 0 , occurrence_count INT DEFAULT 0 , occurrence_coordinate_count INT DEFAULT 0 , created DATETIME , modified DATETIME , deleted DATETIME , data_resource_count INT DEFAULT 0 , PRIMARY KEY (id) ) ENGINE=MYISAM DATA DIRECTORY='/var/lib/mysql/portal/extra_d' INDEX DIRECTORY='/var/lib/mysql/portal/extra_i'; CREATE INDEX IX_resource_network_name ON resource_network (name ASC); CREATE TABLE index_data ( id INT NOT NULL AUTO_INCREMENT , resource_access_point_id INT NOT NULL , type INT , lower_value VARCHAR(255) , upper_value VARCHAR(255) , started DATETIME , finished DATETIME , PRIMARY KEY (id) ) ENGINE=MYISAM DATA DIRECTORY='/var/lib/mysql/portal/extra_d' INDEX DIRECTORY='/var/lib/mysql/portal/extra_i'; CREATE INDEX IX_indexing_data_1 ON index_data (resource_access_point_id ASC); CREATE INDEX IX_indexing_data_2 ON index_data (started ASC); CREATE TABLE image_record ( id INT NOT NULL AUTO_INCREMENT , data_provider_id INT NOT NULL , data_resource_id INT NOT NULL , raw_occurrence_record_id INT , occurrence_record_id INT , taxon_concept_id INT , raw_image_type VARCHAR(128) , image_type INT NOT NULL DEFAULT 0 , url VARCHAR(255) NOT NULL , description TEXT , rights TEXT , html_for_display TEXT , PRIMARY KEY (id) ) ENGINE=MYISAM DATA DIRECTORY='/var/lib/mysql/portal/extra_d' INDEX DIRECTORY='/var/lib/mysql/portal/extra_i'; CREATE INDEX IX_image_record_1 ON image_record (occurrence_record_id ASC); CREATE INDEX IX_image_record_2 ON image_record (data_resource_id ASC); CREATE INDEX IX_image_record_3 ON image_record (image_type ASC); CREATE INDEX IX_image_record_4 ON image_record (taxon_concept_id ASC); CREATE INDEX IX_image_record_5 ON image_record (raw_occurrence_record_id ASC); CREATE TABLE identifier_record ( id INT NOT NULL AUTO_INCREMENT , data_provider_id INT NOT NULL , data_resource_id INT NOT NULL , raw_occurrence_record_id INT , occurrence_record_id INT , identifier_type INT NOT NULL DEFAULT 0 , identifier VARCHAR(255) NOT NULL , PRIMARY KEY (id) ) ENGINE=MYISAM DATA DIRECTORY='/var/lib/mysql/portal/extra_d' INDEX DIRECTORY='/var/lib/mysql/portal/extra_i'; CREATE INDEX IX_identifier_record_1 ON identifier_record (occurrence_record_id ASC); CREATE INDEX IX_identifier_record_2 ON identifier_record (data_resource_id ASC); CREATE INDEX IX_identifier_record_3 ON identifier_record (identifier_type ASC); CREATE INDEX IX_identifier_record_4 ON identifier_record (identifier ASC); CREATE INDEX IX_identifier_record_5 ON identifier_record (raw_occurrence_record_id ASC); CREATE TABLE link_record ( id INT NOT NULL AUTO_INCREMENT , data_provider_id INT NOT NULL , data_resource_id INT NOT NULL , raw_occurrence_record_id INT , occurrence_record_id INT , taxon_concept_id INT , raw_link_type VARCHAR(128) , link_type INT NOT NULL DEFAULT 0 , url VARCHAR(255) NOT NULL , description TEXT , PRIMARY KEY (id) ) ENGINE=MYISAM DATA DIRECTORY='/var/lib/mysql/portal/extra_d' INDEX DIRECTORY='/var/lib/mysql/portal/extra_i'; CREATE INDEX IX_link_record_1 ON link_record (occurrence_record_id ASC); CREATE INDEX IX_link_record_2 ON link_record (data_resource_id ASC); CREATE INDEX IX_link_record_3 ON link_record (taxon_concept_id ASC); CREATE INDEX IX_link_record_4 ON link_record (link_type ASC); CREATE INDEX IX_link_record_5 ON link_record (raw_occurrence_record_id ASC); CREATE TABLE gbif_log_message ( id INT NOT NULL AUTO_INCREMENT , portal_instance_id INT , log_group_id INT , event_id INT DEFAULT 0 , level INT , data_provider_id INT , data_resource_id INT , occurrence_record_id INT , taxon_concept_id INT , user_id INT , message TEXT , restricted TINYINT(1) , count INT , timestamp TIMESTAMP NOT NULL , PRIMARY KEY (id) ) ENGINE=MYISAM DATA DIRECTORY='/var/lib/mysql/portal/extra_d' INDEX DIRECTORY='/var/lib/mysql/portal/extra_i'; CREATE INDEX IX_gbif_log_message_log_group ON gbif_log_message (log_group_id ASC); CREATE INDEX IX_gbif_log_message_instance ON gbif_log_message (portal_instance_id ASC); CREATE INDEX IX_gbif_log_message_provider ON gbif_log_message (data_provider_id ASC); CREATE INDEX IX_gbif_log_message_resource ON gbif_log_message (data_resource_id ASC); CREATE INDEX IX_gbif_log_message_level ON gbif_log_message (level ASC); CREATE INDEX IX_gbif_log_message_event ON gbif_log_message (event_id ASC); CREATE INDEX IX_gbif_log_message_date ON gbif_log_message (timestamp ASC); CREATE INDEX IX_gbif_log_message_user ON gbif_log_message (user_id ASC); CREATE INDEX IX_gbif_log_message_instance_log_group ON gbif_log_message (portal_instance_id ASC, log_group_id ASC); CREATE INDEX IX_gbif_log_message_instance_event_id_data_resource_id ON gbif_log_message (event_id ASC, data_resource_id ASC); CREATE INDEX IX_gbif_log_message_instance_event_id_data_provider_id ON gbif_log_message (event_id ASC, data_provider_id ASC); CREATE TABLE typification_record ( id INT NOT NULL AUTO_INCREMENT , data_provider_id INT NOT NULL , data_resource_id INT NOT NULL , raw_occurrence_record_id INT , occurrence_record_id INT , taxon_name_id INT NOT NULL DEFAULT 0 , scientific_name VARCHAR(255) , publication VARCHAR(255) , type_status VARCHAR(255) NOT NULL , notes TEXT , PRIMARY KEY (id) ) ENGINE=MYISAM DATA DIRECTORY='/var/lib/mysql/portal/extra_d' INDEX DIRECTORY='/var/lib/mysql/portal/extra_i'; CREATE INDEX IX_typification_record_1 ON typification_record (occurrence_record_id ASC); CREATE INDEX IX_typification_record_2 ON typification_record (data_resource_id ASC); CREATE INDEX IX_typification_record_3 ON typification_record (taxon_name_id ASC); CREATE INDEX IX_typification_record_4 ON typification_record (raw_occurrence_record_id ASC); CREATE TABLE resource_rank ( id INT NOT NULL , resource_type INT , entity_id INT , entity_type INT , rank INT , PRIMARY KEY (id) ) ENGINE=MYISAM DATA DIRECTORY='/var/lib/mysql/portal/extra_d' INDEX DIRECTORY='/var/lib/mysql/portal/extra_i'; CREATE INDEX IX_resource_rank_type ON resource_rank (resource_type ASC); CREATE INDEX IX_resource_rank_entity_id ON resource_rank (entity_id ASC); CREATE INDEX IX_resource_rank_entity_type ON resource_rank (entity_type ASC); CREATE INDEX IX_resource_rank_rank ON resource_rank (rank ASC); CREATE TABLE rank ( id INT NOT NULL , name CHAR(50) , PRIMARY KEY (id) ) ENGINE=MYISAM DATA DIRECTORY='/var/lib/mysql/portal/extra_d' INDEX DIRECTORY='/var/lib/mysql/portal/extra_i'; CREATE INDEX IX_rank_1 ON rank (name ASC); CREATE TABLE taxon_name ( id INT NOT NULL AUTO_INCREMENT , canonical VARCHAR(255) NOT NULL , supra_generic VARCHAR(255) , generic VARCHAR(255) , infrageneric VARCHAR(255) , specific_epithet VARCHAR(255) , infraspecific VARCHAR(255) , infraspecific_marker VARCHAR(255) , is_hybrid BOOL , rank INT , author VARCHAR(255) , searchable_canonical VARCHAR(255) , PRIMARY KEY (id) ) ENGINE=MYISAM DATA DIRECTORY='/var/lib/mysql/portal/extra_d' INDEX DIRECTORY='/var/lib/mysql/portal/extra_i'; CREATE INDEX IX_taxon_name_canonical ON taxon_name (canonical ASC); CREATE INDEX IX_taxon_name_specific_epithet ON taxon_name (specific_epithet ASC); CREATE INDEX IX_taxon_name_author ON taxon_name (author ASC); CREATE INDEX IX_taxon_name_rank ON taxon_name (rank ASC); CREATE INDEX IX_taxon_name_searchable_canonical ON taxon_name (searchable_canonical ASC); CREATE INDEX IX_can_auth_rank ON taxon_name (canonical(10), author(10), rank); CREATE TABLE data_resource ( id INT NOT NULL AUTO_INCREMENT , data_provider_id INT NOT NULL , name VARCHAR(255) , display_name VARCHAR(255) , description TEXT , rights TEXT , citation TEXT , logo_url VARCHAR(255) , shared_taxonomy BOOL DEFAULT false , concept_count INT DEFAULT 0 , higher_concept_count INT DEFAULT 0 , species_count INT DEFAULT 0 , occurrence_count INT DEFAULT 0 , occurrence_coordinate_count INT DEFAULT 0 , basis_of_record INT NOT NULL DEFAULT 0 , created DATETIME , modified DATETIME , deleted DATETIME , citable_agent VARCHAR(255) , root_taxon_rank INT , root_taxon_name VARCHAR(150) , scope_continent_code CHAR(2) , scope_country_code CHAR(2) , provider_record_count INT , taxonomic_priority INT NOT NULL DEFAULT 100 , website_url VARCHAR(255) , occurrence_clean_geospatial_count INT DEFAULT 0 , lock_display_name TINYINT(1) NOT NULL DEFAULT 0 , lock_citable_agent TINYINT(1) NOT NULL DEFAULT 0 , lock_basis_of_record TINYINT(1) NOT NULL DEFAULT 0 , PRIMARY KEY (id) , INDEX (data_provider_id) ) ENGINE=MYISAM DATA DIRECTORY='/var/lib/mysql/portal/extra_d' INDEX DIRECTORY='/var/lib/mysql/portal/extra_i'; CREATE INDEX IX_data_resource_name ON data_resource (name ASC); CREATE INDEX IX_data_resource_display_name ON data_resource (display_name ASC); CREATE INDEX IX_data_resource_shared_taxonomy ON data_resource (shared_taxonomy ASC); CREATE TABLE taxon_concept ( id INT NOT NULL AUTO_INCREMENT , rank INT NOT NULL , taxon_name_id INT NOT NULL , data_provider_id INT NOT NULL , data_resource_id INT NOT NULL , publication_id INT , parent_concept_id INT , kingdom_concept_id INT , phylum_concept_id INT , class_concept_id INT , order_concept_id INT , family_concept_id INT , genus_concept_id INT , species_concept_id INT , nub_concept_id INT , is_accepted BOOL DEFAULT true , is_nub_concept BOOL DEFAULT false , partner_concept_id INT , modified DATETIME , created DATETIME , deleted DATETIME , priority INT NOT NULL DEFAULT 100 , is_secondary BOOL DEFAULT false , PRIMARY KEY (id) , INDEX (taxon_name_id) , INDEX (data_provider_id) , INDEX (data_resource_id) , INDEX (parent_concept_id) , INDEX (kingdom_concept_id) , INDEX (phylum_concept_id) , INDEX (class_concept_id) , INDEX (order_concept_id) , INDEX (family_concept_id) , INDEX (genus_concept_id) , INDEX (species_concept_id) ) ENGINE=MYISAM DATA DIRECTORY='/var/lib/mysql/portal/extra_d' INDEX DIRECTORY='/var/lib/mysql/portal/extra_i'; CREATE INDEX IX_taxon_concept_rank ON taxon_concept (rank ASC); CREATE INDEX IX_taxon_concept_2 ON taxon_concept (is_nub_concept ASC); CREATE INDEX IX_taxon_concept_3 ON taxon_concept (partner_concept_id ASC); CREATE INDEX IX_taxon_concept_4 ON taxon_concept (priority ASC); CREATE TABLE resource_access_point ( id INT NOT NULL AUTO_INCREMENT , data_provider_id INT , data_resource_id INT , url VARCHAR(255) , remote_id_at_url VARCHAR(255) , uuid CHAR(50) , created DATETIME , modified DATETIME , deleted DATETIME , last_harvest_start datetime , last_extract_start datetime , supports_date_last_modified boolean not null default false , interval_metadata_days int , interval_harvest_days int , PRIMARY KEY (id) , INDEX (data_provider_id) ) ENGINE=MYISAM DATA DIRECTORY='/var/lib/mysql/portal/extra_d' INDEX DIRECTORY='/var/lib/mysql/portal/extra_i'; CREATE TABLE distribution ( id INT NOT NULL AUTO_INCREMENT , taxon_concept_id INT , text VARCHAR(255) NOT NULL , iso_language_code CHAR(2) NOT NULL , language VARCHAR(255) , PRIMARY KEY (id) , INDEX (taxon_concept_id) ) ENGINE=MYISAM DATA DIRECTORY='/var/lib/mysql/portal/extra_d' INDEX DIRECTORY='/var/lib/mysql/portal/extra_i'; CREATE INDEX IX_distribution_text ON distribution (text ASC); CREATE INDEX IX_distribution_iso_language_code ON distribution (iso_language_code ASC); CREATE INDEX IX_distribution_language ON distribution (language ASC); CREATE TABLE namespace_mapping ( resource_access_point_id INT NOT NULL , property_store_namespace_id INT NOT NULL , priority INT NOT NULL , PRIMARY KEY (property_store_namespace_id, resource_access_point_id) , INDEX (property_store_namespace_id) , INDEX (resource_access_point_id) ) ENGINE=MYISAM DATA DIRECTORY='/var/lib/mysql/portal/extra_d' INDEX DIRECTORY='/var/lib/mysql/portal/extra_i'; CREATE TABLE data_resource_agent ( id INT NOT NULL AUTO_INCREMENT , data_resource_id INT NOT NULL , agent_id INT NOT NULL , agent_type INT NOT NULL , PRIMARY KEY (id) , INDEX (data_resource_id) , INDEX (agent_id) ) ENGINE=MYISAM DATA DIRECTORY='/var/lib/mysql/portal/extra_d' INDEX DIRECTORY='/var/lib/mysql/portal/extra_i'; CREATE TABLE data_provider_agent ( id INT NOT NULL AUTO_INCREMENT , data_provider_id INT NOT NULL , agent_id INT NOT NULL , agent_type INT NOT NULL , PRIMARY KEY (id) , INDEX (data_provider_id) , INDEX (agent_id) ) ENGINE=MYISAM DATA DIRECTORY='/var/lib/mysql/portal/extra_d' INDEX DIRECTORY='/var/lib/mysql/portal/extra_i'; CREATE TABLE relationship_assertion ( from_concept_id INT NOT NULL , to_concept_id INT NOT NULL , relationship_type INT NOT NULL , PRIMARY KEY (from_concept_id, to_concept_id, relationship_type) , INDEX (from_concept_id) , INDEX (to_concept_id) ) ENGINE=MYISAM DATA DIRECTORY='/var/lib/mysql/portal/extra_d' INDEX DIRECTORY='/var/lib/mysql/portal/extra_i'; CREATE INDEX IX_relationship_assertion_1 ON relationship_assertion (relationship_type ASC); CREATE TABLE remote_concept ( id INT NOT NULL AUTO_INCREMENT , taxon_concept_id INT NOT NULL , id_type INT NOT NULL , remote_id VARCHAR(255) , modified TIMESTAMP , PRIMARY KEY (id) , INDEX (taxon_concept_id) ) ENGINE=MYISAM DATA DIRECTORY='/var/lib/mysql/portal/extra_d' INDEX DIRECTORY='/var/lib/mysql/portal/extra_i'; CREATE INDEX IX_remote_concept_remote_id ON remote_concept (remote_id ASC); CREATE INDEX IX_remote_concept_id_type ON remote_concept (id_type ASC); CREATE INDEX IX_remote_concept_modified ON remote_concept (modified ASC); CREATE TABLE country_name ( id INT NOT NULL AUTO_INCREMENT , country_id INT , name VARCHAR(255) , searchable_name VARCHAR(255) , iso_country_code CHAR(2) , locale CHAR(2) , PRIMARY KEY (id) , INDEX (country_id) ) ENGINE=MYISAM DATA DIRECTORY='/var/lib/mysql/portal/extra_d' INDEX DIRECTORY='/var/lib/mysql/portal/extra_i'; CREATE INDEX IX_country_name_name ON country_name (name ASC); CREATE INDEX IX_country_name_searchable_name ON country_name (searchable_name ASC); CREATE INDEX IX_country_name_iso_country_code ON country_name (iso_country_code ASC); CREATE INDEX IX_country_name_locale ON country_name (locale ASC); CREATE TABLE network_membership ( id INT NOT NULL AUTO_INCREMENT , data_resource_id INT NOT NULL , resource_network_id INT NOT NULL , PRIMARY KEY (id) , INDEX (data_resource_id) , INDEX (resource_network_id) ) ENGINE=MYISAM DATA DIRECTORY='/var/lib/mysql/portal/extra_d' INDEX DIRECTORY='/var/lib/mysql/portal/extra_i'; CREATE INDEX IX_network_membership_id ON network_membership (id ASC); CREATE TABLE common_name ( id INT NOT NULL AUTO_INCREMENT , taxon_concept_id INT , name VARCHAR(255) NOT NULL , iso_language_code CHAR(2) NOT NULL , language VARCHAR(255) , PRIMARY KEY (id) , INDEX (taxon_concept_id) ) ENGINE=MYISAM DATA DIRECTORY='/var/lib/mysql/portal/extra_d' INDEX DIRECTORY='/var/lib/mysql/portal/extra_i'; CREATE INDEX IX_common_name_1 ON common_name (name ASC); CREATE INDEX IX_common_name_2 ON common_name (iso_language_code ASC); CREATE INDEX IX_common_name_3 ON common_name (language ASC); CREATE TABLE gbif_user ( id INT NOT NULL AUTO_INCREMENT , portal_instance_id INT NOT NULL , name VARCHAR(255) NOT NULL , email VARCHAR(255) NOT NULL , verified BOOL NOT NULL DEFAULT false , PRIMARY KEY (id) ) ENGINE=MYISAM DATA DIRECTORY='/var/lib/mysql/portal/extra_d' INDEX DIRECTORY='/var/lib/mysql/portal/extra_i'; CREATE INDEX IX_gbif_user_unique ON gbif_user (portal_instance_id ASC, name(50) ASC, email(50) ASC); CREATE INDEX IX_gbif_user_verified ON gbif_user (verified ASC); CREATE TABLE registration_login ( id int(11) NOT NULL auto_increment, login_id varchar(255) default NULL, business_key varchar(255) default NULL, PRIMARY KEY (id) ) ENGINE=MYISAM DATA DIRECTORY='/var/lib/mysql/portal/extra_d' INDEX DIRECTORY='/var/lib/mysql/portal/extra_i'; CREATE INDEX IX_registration_login_login_id ON registration_login (login_id ASC); CREATE INDEX IX_registration_login_business_key ON registration_login (business_key ASC); insert into property_store_namespace(namespace) values('http://www.biocase.org/schemas/protocol/1.3'); insert into property_store_namespace(namespace) values('http://www.tdwg.org/schemas/abcd/1.2'); insert into property_store_namespace(namespace) values('http://www.tdwg.org/schemas/abcd/2.05'); insert into property_store_namespace(namespace) values('http://www.tdwg.org/schemas/abcd/2.06'); insert into property_store_namespace(namespace) values('http://digir.net/schema/protocol/2003/1.0'); insert into property_store_namespace(namespace) values('http://digir.net/schema/conceptual/darwin/2003/1.0'); insert into property_store_namespace(namespace) values('http://manis.digir.net/schema/protocol/2003/1.0'); insert into property_store_namespace(namespace) values('http://manis.digir.net/schema/conceptual/darwin/2003/1.0'); insert into property_store_namespace(namespace) values('http://rs.tdwg.org/tapir/1.0'); insert into property_store_namespace(namespace) values('http://rs.tdwg.org/dwc/dwcore'); insert into property_store_namespace(namespace) values('http://rs.tdwg.org/dwc/geospatial'); insert into property_store_namespace(namespace) values('http://rs.tdwg.org/dwc/curatorial'); insert into property_store_namespace(namespace) values('http://digir.net/schema/conceptual/darwin/manis/1.21'); DROP TABLE IF EXISTS QRTZ_JOB_LISTENERS; DROP TABLE IF EXISTS QRTZ_TRIGGER_LISTENERS; DROP TABLE IF EXISTS QRTZ_FIRED_TRIGGERS; DROP TABLE IF EXISTS QRTZ_PAUSED_TRIGGER_GRPS; DROP TABLE IF EXISTS QRTZ_SCHEDULER_STATE; DROP TABLE IF EXISTS QRTZ_LOCKS; DROP TABLE IF EXISTS QRTZ_SIMPLE_TRIGGERS; DROP TABLE IF EXISTS QRTZ_CRON_TRIGGERS; DROP TABLE IF EXISTS QRTZ_BLOB_TRIGGERS; DROP TABLE IF EXISTS QRTZ_TRIGGERS; DROP TABLE IF EXISTS QRTZ_JOB_DETAILS; DROP TABLE IF EXISTS QRTZ_CALENDARS; CREATE TABLE QRTZ_JOB_DETAILS ( JOB_NAME VARCHAR(100) NOT NULL, JOB_GROUP VARCHAR(100) NOT NULL, DESCRIPTION VARCHAR(255) NULL, JOB_CLASS_NAME VARCHAR(128) NOT NULL, IS_DURABLE VARCHAR(1) NOT NULL, IS_VOLATILE VARCHAR(1) NOT NULL, IS_STATEFUL VARCHAR(1) NOT NULL, REQUESTS_RECOVERY VARCHAR(1) NOT NULL, JOB_DATA BLOB NULL, PRIMARY KEY (JOB_NAME,JOB_GROUP) ) ENGINE=MYISAM DATA DIRECTORY='/var/lib/mysql/portal/extra_d' INDEX DIRECTORY='/var/lib/mysql/portal/extra_i'; CREATE TABLE QRTZ_JOB_LISTENERS ( JOB_NAME VARCHAR(100) NOT NULL, JOB_GROUP VARCHAR(100) NOT NULL, JOB_LISTENER VARCHAR(100) NOT NULL, PRIMARY KEY (JOB_NAME,JOB_GROUP,JOB_LISTENER), FOREIGN KEY (JOB_NAME,JOB_GROUP) REFERENCES QRTZ_JOB_DETAILS(JOB_NAME,JOB_GROUP) ) ENGINE=MYISAM DATA DIRECTORY='/var/lib/mysql/portal/extra_d' INDEX DIRECTORY='/var/lib/mysql/portal/extra_i'; CREATE TABLE QRTZ_TRIGGERS ( TRIGGER_NAME VARCHAR(100) NOT NULL, TRIGGER_GROUP VARCHAR(100) NOT NULL, JOB_NAME VARCHAR(100) NOT NULL, JOB_GROUP VARCHAR(100) NOT NULL, IS_VOLATILE VARCHAR(1) NOT NULL, DESCRIPTION VARCHAR(255) NULL, NEXT_FIRE_TIME BIGINT(13) NULL, PREV_FIRE_TIME BIGINT(13) NULL, PRIORITY INTEGER NULL, TRIGGER_STATE VARCHAR(16) NOT NULL, TRIGGER_TYPE VARCHAR(8) NOT NULL, START_TIME BIGINT(13) NOT NULL, END_TIME BIGINT(13) NULL, CALENDAR_NAME VARCHAR(255) NULL, MISFIRE_INSTR SMALLINT(2) NULL, JOB_DATA BLOB NULL,... [truncated message content] |
|
From: Jose C. <jos...@us...> - 2009-05-26 10:21:11
|
Update of /cvsroot/gbif/portal-service/src/main/java/org/gbif/portal/dao/geospatial/impl/hibernate In directory ddv4jf1.ch3.sourceforge.com:/tmp/cvs-serv24402/src/main/java/org/gbif/portal/dao/geospatial/impl/hibernate Modified Files: CountryDAOImpl.java Log Message: small fix on getCountryCountsForCountry. Erroneous query. Index: CountryDAOImpl.java =================================================================== RCS file: /cvsroot/gbif/portal-service/src/main/java/org/gbif/portal/dao/geospatial/impl/hibernate/CountryDAOImpl.java,v retrieving revision 1.23 retrieving revision 1.24 diff -C2 -d -r1.23 -r1.24 *** CountryDAOImpl.java 25 May 2009 14:42:10 -0000 1.23 --- CountryDAOImpl.java 26 May 2009 10:20:59 -0000 1.24 *************** *** 241,251 **** } if(isoCountryCode!=null){ ! sb.append("rc.iso_country_code=:isoCountryCode and cn.locale=:locale and"); ! } ! sb.append(" dr.deleted is null"); if(geoRefOnly){ sb.append(" rc.occurrence_coordinate_count>0"); } sb.append(" group by cn_name"); SQLQuery query = session.createSQLQuery(sb.toString()); --- 241,253 ---- } if(isoCountryCode!=null){ ! sb.append("rc.iso_country_code=:isoCountryCode and cn.locale=:locale"); ! } if(geoRefOnly){ sb.append(" rc.occurrence_coordinate_count>0"); } + + sb.append(" and dr.deleted is null"); + sb.append(" group by cn_name"); SQLQuery query = session.createSQLQuery(sb.toString()); |
|
From: Jose C. <jos...@us...> - 2009-05-26 10:20:27
|
Update of /cvsroot/gbif/portal-web/src/main/java/org/gbif/portal/web/tag In directory ddv4jf1.ch3.sourceforge.com:/tmp/cvs-serv24308/src/main/java/org/gbif/portal/web/tag Added Files: FormatTextTag.java Log Message: Bug 169 Enhancement: Formatted URLs as links in the metadata fields (rights, description) in Data Provider and dataset pages. --- NEW FILE: FormatTextTag.java --- /*************************************************************************** * Copyright (C) 2006 Global Biodiversity Information Facility Secretariat. * All Rights Reserved. * * The contents of this file are subject to the Mozilla Public * License Version 1.1 (the "License"); you may not use this file * except in compliance with the License. You may obtain a copy of * the License at http://www.mozilla.org/MPL/ * * Software distributed under the License is distributed on an "AS * IS" basis, WITHOUT WARRANTY OF ANY KIND, either express or * implied. See the License for the specific language governing * rights and limitations under the License. ***************************************************************************/ package org.gbif.portal.web.tag; import java.io.IOException; import javax.servlet.jsp.JspException; import javax.servlet.jsp.tagext.TagSupport; import org.apache.commons.logging.Log; import org.apache.commons.logging.LogFactory; /** * This tag formats different elements inside a text string: * - Links: by appending the '<a href="'prefix and '">someName</a>' suffix * * @author josecuadra */ public class FormatTextTag extends TagSupport{ private static final long serialVersionUID = 3161689558535927050L; /** Logger*/ private static Log logger = LogFactory.getLog(FormatTextTag.class); /**The scientific name from the record**/ protected String content; //Elements for formatting an URL protected String urlPrefix = "<a href=\""; protected String urlSuffix = "\">"; protected String urlEndTag = "</a>"; protected String urlCompletePattern = "http://"; protected String urlSimplePattern = "www."; protected char[] urlDelimiters = new char[]{' ', '(',')', '[', ']', '{', '}'}; /** * @see javax.servlet.jsp.tagext.BodyTagSupport#doEndTag() */ public int doStartTag() throws JspException { String output = ""; output = formatLink(); try { pageContext.getOut().print(output.toString()); } catch (IOException e) { logger.error(e.getMessage(), e); throw new JspException(e); } return SKIP_BODY; } public String formatLink() { int currentPos=0; int startPos=0; int endPos=0; String tempUrl; String tempLink; StringBuffer output=new StringBuffer(); while( (startPos = content.indexOf(urlCompletePattern, currentPos)) != -1 ) { endPos = delimitedString(content, startPos); tempUrl = content.substring(startPos, endPos); tempLink = urlPrefix+tempUrl+urlSuffix+tempUrl+urlEndTag; //append the text before the link ref output.append(content.substring(currentPos, startPos)); //append the formatted link output.append(tempLink); currentPos = endPos; } output.append(content.substring(endPos, content.length())); return output.toString(); } public int delimitedString(String tempUrl, int startPos) { int firstDelimiter=tempUrl.length(); //index of the appearance of the first delimiter for(char delimiter: urlDelimiters) { if(tempUrl.indexOf(delimiter,startPos)!=-1) { int indexof = tempUrl.indexOf(delimiter,startPos); if(indexof<firstDelimiter) firstDelimiter=indexof; } } return firstDelimiter; } /** * @return the content */ public String getContent() { return content; } /** * @param content the content to set */ public void setContent(String content) { this.content = content; } } |
|
From: Jose C. <jos...@us...> - 2009-05-26 10:20:27
|
Update of /cvsroot/gbif/portal-web/src/main/webapp/WEB-INF/jsp/dataset/drilldown/resource In directory ddv4jf1.ch3.sourceforge.com:/tmp/cvs-serv24308/src/main/webapp/WEB-INF/jsp/dataset/drilldown/resource Modified Files: information.jsp Log Message: Bug 169 Enhancement: Formatted URLs as links in the metadata fields (rights, description) in Data Provider and dataset pages. Index: information.jsp =================================================================== RCS file: /cvsroot/gbif/portal-web/src/main/webapp/WEB-INF/jsp/dataset/drilldown/resource/information.jsp,v retrieving revision 1.25 retrieving revision 1.26 diff -C2 -d -r1.25 -r1.26 *** information.jsp 6 May 2009 12:48:41 -0000 1.25 --- information.jsp 26 May 2009 10:20:16 -0000 1.26 *************** *** 3,8 **** <c:if test="${not empty dataResource.name}"><p><label><spring:message code="name"/>:</label>${dataResource.name}</p></c:if> <c:if test="${not empty dataResource.websiteUrl}"><p><label><spring:message code="website"/>:</label><a href="<c:if test="${fn:length(ataResource.websiteUrl)>6 && !fn:startsWith(dataResource.websiteUrl, 'http://')}">http://</c:if>${dataResource.websiteUrl}">${dataResource.websiteUrl}</a></p></c:if> ! <c:if test="${not empty dataResource.description}"><p><label><spring:message code="description"/>:</label>${dataResource.description}</p></c:if> ! <c:if test="${not empty dataResource.rights}"><p><label><spring:message code="rights"/>:</label>${dataResource.rights}</p></c:if> <%-- --- 3,8 ---- <c:if test="${not empty dataResource.name}"><p><label><spring:message code="name"/>:</label>${dataResource.name}</p></c:if> <c:if test="${not empty dataResource.websiteUrl}"><p><label><spring:message code="website"/>:</label><a href="<c:if test="${fn:length(ataResource.websiteUrl)>6 && !fn:startsWith(dataResource.websiteUrl, 'http://')}">http://</c:if>${dataResource.websiteUrl}">${dataResource.websiteUrl}</a></p></c:if> ! <c:if test="${not empty dataResource.description}"><p><label><spring:message code="description"/>:</label><gbif:formatText content="${dataResource.description}"/></p></c:if> ! <c:if test="${not empty dataResource.rights}"><p><label><spring:message code="rights"/>:</label><gbif:formatText content="${dataResource.rights}"/></p></c:if> <%-- |
|
From: Jose C. <jos...@us...> - 2009-05-26 10:20:27
|
Update of /cvsroot/gbif/portal-web/src/main/webapp/WEB-INF/jsp/dataset/drilldown/provider In directory ddv4jf1.ch3.sourceforge.com:/tmp/cvs-serv24308/src/main/webapp/WEB-INF/jsp/dataset/drilldown/provider Modified Files: information.jsp Log Message: Bug 169 Enhancement: Formatted URLs as links in the metadata fields (rights, description) in Data Provider and dataset pages. Index: information.jsp =================================================================== RCS file: /cvsroot/gbif/portal-web/src/main/webapp/WEB-INF/jsp/dataset/drilldown/provider/information.jsp,v retrieving revision 1.22 retrieving revision 1.23 diff -C2 -d -r1.22 -r1.23 *** information.jsp 8 Jan 2008 09:53:24 -0000 1.22 --- information.jsp 26 May 2009 10:20:16 -0000 1.23 *************** *** 5,9 **** <c:if test="${not empty dataProvider.websiteUrl}"><p><label><spring:message code="website"/>:</label><a href="${dataProvider.websiteUrl}">${dataProvider.websiteUrl}</a></p></c:if> <c:if test="${not empty dataProvider.gbifApprover}"><p><label><spring:message code="gbif.participant"/>:</label>${dataProvider.gbifApprover}</p></c:if> ! <c:if test="${not empty dataProvider.description}"><p><label><spring:message code="description"/>:</label>${dataProvider.description}</p></c:if> <c:if test="${not empty dataProvider.address}"><p><label><spring:message code="address"/>:</label>${dataProvider.address}</p></c:if> <c:if test="${not empty dataProvider.isoCountryCode}"><p><label><spring:message code="country" text=""/>:</label><spring:message code="country.${dataProvider.isoCountryCode}" text=""/></p></c:if> --- 5,9 ---- <c:if test="${not empty dataProvider.websiteUrl}"><p><label><spring:message code="website"/>:</label><a href="${dataProvider.websiteUrl}">${dataProvider.websiteUrl}</a></p></c:if> <c:if test="${not empty dataProvider.gbifApprover}"><p><label><spring:message code="gbif.participant"/>:</label>${dataProvider.gbifApprover}</p></c:if> ! <c:if test="${not empty dataProvider.description}"><p><label><spring:message code="description"/>:</label><gbif:formatText content="${dataProvider.description}"/></p></c:if> <c:if test="${not empty dataProvider.address}"><p><label><spring:message code="address"/>:</label>${dataProvider.address}</p></c:if> <c:if test="${not empty dataProvider.isoCountryCode}"><p><label><spring:message code="country" text=""/>:</label><spring:message code="country.${dataProvider.isoCountryCode}" text=""/></p></c:if> |
|
From: Jose C. <jos...@us...> - 2009-05-26 10:20:27
|
Update of /cvsroot/gbif/portal-web/src/main/webapp/tld In directory ddv4jf1.ch3.sourceforge.com:/tmp/cvs-serv24308/src/main/webapp/tld Modified Files: gbif.tld Log Message: Bug 169 Enhancement: Formatted URLs as links in the metadata fields (rights, description) in Data Provider and dataset pages. Index: gbif.tld =================================================================== RCS file: /cvsroot/gbif/portal-web/src/main/webapp/tld/gbif.tld,v retrieving revision 1.38 retrieving revision 1.39 diff -C2 -d -r1.38 -r1.39 *** gbif.tld 5 May 2009 16:48:40 -0000 1.38 --- gbif.tld 26 May 2009 10:20:16 -0000 1.39 *************** *** 524,527 **** --- 524,540 ---- <tag> + <description>Tag for different components of a text string</description> + <name>formatText</name> + <tag-class>org.gbif.portal.web.tag.FormatTextTag</tag-class> + <body-content>JSP</body-content> + <attribute> + <description>The text string</description> + <name>content</name> + <required>true</required> + <rtexprvalue>true</rtexprvalue> + </attribute> + </tag> + + <tag> <description>Returns the current date</description> <name>currentDate</name> |
|
From: Jose C. <jos...@us...> - 2009-05-26 09:39:43
|
Update of /cvsroot/gbif/portal-index/db In directory ddv4jf1.ch3.sourceforge.com:/tmp/cvs-serv15447/db Modified Files: process.sql Log Message: added update statement to eliminate infinite loops in the taxonomy (taxon concept having a parent with a higher or same rank) Index: process.sql =================================================================== RCS file: /cvsroot/gbif/portal-index/db/process.sql,v retrieving revision 1.23 retrieving revision 1.24 diff -C2 -d -r1.23 -r1.24 *** process.sql 7 May 2009 14:29:45 -0000 1.23 --- process.sql 26 May 2009 09:39:31 -0000 1.24 *************** *** 609,611 **** --- 609,617 ---- group by 2; + -- to eliminate possiblity of infinites loops in the taxonomy + update taxon_concept c + inner join taxon_concept p on c.parent_concept_id=p.id + set c.parent_concept_id=null + where p.rank>=c.rank + select concat('Rollover complete: ', now()) as debug; |
|
From: Jose C. <jos...@us...> - 2009-05-25 16:02:53
|
Update of /cvsroot/gbif/portal-web/src/main/webapp/javascript In directory ddv4jf1.ch3.sourceforge.com:/tmp/cvs-serv3233/src/main/webapp/javascript Modified Files: dateSelector.js Log Message: Bug 341 Fix: Occurrence Date filter in Occurrence search page was not updating correctly the search string. Seems javascript doesn't support method overloading correctly. Index: dateSelector.js =================================================================== RCS file: /cvsroot/gbif/portal-web/src/main/webapp/javascript/dateSelector.js,v retrieving revision 1.3 retrieving revision 1.4 diff -C2 -d -r1.3 -r1.4 *** dateSelector.js 5 Feb 2008 16:24:41 -0000 1.3 --- dateSelector.js 25 May 2009 16:02:44 -0000 1.4 *************** *** 2,6 **** var february = 2; ! function validateDayListFromMonth(dataSelectorDiv){ var daySelect = dataSelectorDiv.getElementsByTagName("SELECT")[0]; --- 2,6 ---- var february = 2; ! function validateDayListFromMonthSelector(dataSelectorDiv){ var daySelect = dataSelectorDiv.getElementsByTagName("SELECT")[0]; |
|
From: Jose C. <jos...@us...> - 2009-05-25 16:02:51
|
Update of /cvsroot/gbif/portal-web/src/main/webapp/WEB-INF/tags In directory ddv4jf1.ch3.sourceforge.com:/tmp/cvs-serv3233/src/main/webapp/WEB-INF/tags Modified Files: dateSelect.tag Log Message: Bug 341 Fix: Occurrence Date filter in Occurrence search page was not updating correctly the search string. Seems javascript doesn't support method overloading correctly. Index: dateSelect.tag =================================================================== RCS file: /cvsroot/gbif/portal-web/src/main/webapp/WEB-INF/tags/dateSelect.tag,v retrieving revision 1.7 retrieving revision 1.8 diff -C2 -d -r1.7 -r1.8 *** dateSelect.tag 10 Oct 2007 13:09:04 -0000 1.7 --- dateSelect.tag 25 May 2009 16:02:44 -0000 1.8 *************** *** 30,34 **** <c:if test="${hideLabels}"><spring:message code="month"/>:</c:if> ! <select id="<c:if test="${not empty name}">${name}_</c:if>month" name="<c:if test="${not empty name}">${name}_</c:if>month" class="monthSelect" onchange="javascript:validateDayListFromMonth(this.parentNode);<c:if test="${not empty callback}">${callback}</c:if>"> <option value="01"<c:if test="${selectedMonth==01}"> selected="true"</c:if>><spring:message code="month.jan"/></option> <option value="02"<c:if test="${selectedMonth==02}"> selected="true"</c:if>><spring:message code="month.feb"/></option> --- 30,34 ---- <c:if test="${hideLabels}"><spring:message code="month"/>:</c:if> ! <select id="<c:if test="${not empty name}">${name}_</c:if>month" name="<c:if test="${not empty name}">${name}_</c:if>month" class="monthSelect" onchange="javascript:validateDayListFromMonthSelector(this.parentNode);<c:if test="${not empty callback}">${callback}</c:if>"> <option value="01"<c:if test="${selectedMonth==01}"> selected="true"</c:if>><spring:message code="month.jan"/></option> <option value="02"<c:if test="${selectedMonth==02}"> selected="true"</c:if>><spring:message code="month.feb"/></option> *************** *** 50,54 **** name="<c:if test="${not empty name}">${name}_</c:if>year" class="yearInput" ! onchange="javascript:validateDayListFromMonth(this.parentNode);<c:if test="${not empty callback}">${callback}</c:if>" value="${selectedYear}" maxlength="4" --- 50,54 ---- name="<c:if test="${not empty name}">${name}_</c:if>year" class="yearInput" ! onKeyUp="javascript:validateDayListFromMonthSelector(this.parentNode);<c:if test="${not empty callback}">${callback}</c:if>" value="${selectedYear}" maxlength="4" |
|
From: Jose C. <jos...@us...> - 2009-05-25 14:42:24
|
Update of /cvsroot/gbif/portal-service/src/main/java/org/gbif/portal/dao/geospatial In directory ddv4jf1.ch3.sourceforge.com:/tmp/cvs-serv23610/src/main/java/org/gbif/portal/dao/geospatial Modified Files: CountryDAO.java Log Message: Bug 209 Enhancement: Included a table "Countries providing data for Map" on each Country page. Index: CountryDAO.java =================================================================== RCS file: /cvsroot/gbif/portal-service/src/main/java/org/gbif/portal/dao/geospatial/CountryDAO.java,v retrieving revision 1.13 retrieving revision 1.14 diff -C2 -d -r1.13 -r1.14 *** CountryDAO.java 24 Feb 2009 15:39:05 -0000 1.13 --- CountryDAO.java 25 May 2009 14:42:10 -0000 1.14 *************** *** 111,114 **** --- 111,123 ---- /** + * Retrieve counts for countries providing data for the country + * + * @param isoCountryCode + * @param geoRefOnly + * @return + */ + public List<Object[]> getCountryCountsForCountry(String isoCountryCode, boolean geoRefOnly, Locale locale); + + /** * Use the taxon_country table to retrieve the countries with data for this taxon. * |
|
From: Jose C. <jos...@us...> - 2009-05-25 14:42:20
|
Update of /cvsroot/gbif/portal-web/src/main/webapp/WEB-INF/jsp/geography/drilldown In directory ddv4jf1.ch3.sourceforge.com:/tmp/cvs-serv23573/src/main/webapp/WEB-INF/jsp/geography/drilldown Modified Files: view.jsp Added Files: countryCounts.jsp Log Message: Bug 209 Enhancement: Included a table "Countries providing data for Map" on each Country page. Index: view.jsp =================================================================== RCS file: /cvsroot/gbif/portal-web/src/main/webapp/WEB-INF/jsp/geography/drilldown/view.jsp,v retrieving revision 1.14 retrieving revision 1.15 diff -C2 -d -r1.14 -r1.15 *** view.jsp 19 Mar 2008 17:41:43 -0000 1.14 --- view.jsp 25 May 2009 14:42:07 -0000 1.15 *************** *** 14,17 **** --- 14,18 ---- <c:set var="occurrenceSearchValue" value="${country.isoCountryCode}" scope="request"/> <tiles:insert page="dataRecord.jsp"/> + <tiles:insert page="countryCounts.jsp"/> </div> </div> \ No newline at end of file --- NEW FILE: countryCounts.jsp --- <%@ include file="/common/taglibs.jsp"%> <script type="text/javascript"> function toggleTables(firstTable, secondTable, visibleClass){ var t1 = document.getElementById(firstTable); var t2 = document.getElementById(secondTable); if(t1.className=='hidden'){ t1.className = visibleClass; t2.className = 'hidden'; } else { t2.className = visibleClass; t1.className = 'hidden'; } } </script> <c:if test="${not empty countryCounts}"> <h5><spring:message code="geography.drilldown.mapped.countries" text="Countries providing data for Map"/></h5> <c:if test="${fn:length(countryCounts)>4}"> <a href="javascript:toggleTables('sampleCountryTable', 'countryCount', 'results');" title="Show all countries"><spring:message code="show"/>/<spring:message code="hide"/></a> </c:if> <table id="sampleCountryTable" class="results" style="width: 720px;"> <thead> <th><spring:message code="country"/></th> <th class="lastColumn"><spring:message code="log.console.count"/></th> </thead> <tbody> <c:forEach items="${countryCounts}" var="countryCount" begin="0" end="3"> <tr> <td style="width:650px;"> <c:set var="countryName"><gbif:capitalize>${countryCount.name}</gbif:capitalize></c:set> <img src="${pageContext.request.contextPath}/images/flags/<string:lowerCase>${countryCount.key}</string:lowerCase>.gif"/> <a href="${pageContext.request.contextPath}/countries/${countryCount.key}">${countryName}</a> <p class="resultsDetails"> ${countryCount.properties[0]} </p> </td> <td class="lastColumn" style="width:70px;"> <a href="${pageContext.request.contextPath}/occurrences/search.htm?<gbif:criterion subject="32" predicate="0" value="${countryCount.key}" index="0"/>&<gbif:criterion subject="${occurrenceSearchSubject}" predicate="0" value="${occurrenceSearchValue}" index="1"/>&<gbif:criterion subject="28" predicate="0" value="0" index="2"/>"><fmt:formatNumber value="${countryCount.count}" pattern="###,###"/></a> </td> </tr> </c:forEach> <c:if test="${fn:length(countryCounts)>4}"> <tr> <td colspan="2"> <p class="showFullTable"> <a href="javascript:toggleTables('sampleCountryTable', 'countryCount', 'results');">View ${fn:length(countryCounts)-4 } more countries... </a> </p> </td> </tr> </c:if> </tbody> </table> <c:if test="${fn:length(countryCounts)>4}"> <display-el:table name="countryCounts" class="hidden" id="fullTable" uid="countryCount" style="width: 720px;" requestURI="${pageContext.request.contextPath}/countries/${countryCount.key}/"> <display-el:column titleKey="country" style="width:650px;"> <c:set var="countryName"><gbif:capitalize>${countryCount.name}</gbif:capitalize></c:set> <img src="${pageContext.request.contextPath}/images/flags/<string:lowerCase>${countryCount.key}</string:lowerCase>.gif"/> <a href="${pageContext.request.contextPath}/countries/${countryCount.key}">${countryName}</a> <p class="resultsDetails"> ${countryCount.properties[0]} </display-el:column> <display-el:column class="lastColumn" titleKey="log.console.count" style="width:70px;"> <a href="${pageContext.request.contextPath}/occurrences/search.htm?<gbif:criterion subject="32" predicate="0" value="${countryCount.key}" index="0"/>&<gbif:criterion subject="${occurrenceSearchSubject}" predicate="0" value="${occurrenceSearchValue}" index="1"/>&<gbif:criterion subject="28" predicate="0" value="0" index="2"/>"><fmt:formatNumber value="${countryCount.count}" pattern="###,###"/></a> </display-el:column> </display-el:table> </c:if> </c:if> |
|
From: Jose C. <jos...@us...> - 2009-05-25 14:42:19
|
Update of /cvsroot/gbif/portal-service/src/main/java/org/gbif/portal/service In directory ddv4jf1.ch3.sourceforge.com:/tmp/cvs-serv23610/src/main/java/org/gbif/portal/service Modified Files: GeospatialManager.java Log Message: Bug 209 Enhancement: Included a table "Countries providing data for Map" on each Country page. Index: GeospatialManager.java =================================================================== RCS file: /cvsroot/gbif/portal-service/src/main/java/org/gbif/portal/service/GeospatialManager.java,v retrieving revision 1.32 retrieving revision 1.33 diff -C2 -d -r1.32 -r1.33 *** GeospatialManager.java 24 Feb 2009 15:39:05 -0000 1.32 --- GeospatialManager.java 25 May 2009 14:42:10 -0000 1.33 *************** *** 280,283 **** --- 280,295 ---- */ public List<CountDTO> getDataResourceCountsForCountry(String isoCountryCode, boolean georeferencedOnly) throws ServiceException; + + /** + * Returns a list of occurrence record counts for countries providing data for an country + * (e.g.: Denmark hosts occurrences that were collected in Australia) + * + * @param isoCountryCode The country key + * @param geoRefOnly Whether to only count georeferenced points + * @param locale current locale of the webapps + * @return List<CountDTO> containing iso country code, country name and count + * @throws ServiceException indicate a failure to retrieve the data due to a network/database connection + */ + public List<CountDTO> getCountryCountsForCountry(String isoCountryCode, boolean geoRefOnly, Locale locale) throws ServiceException; /** |
|
From: Jose C. <jos...@us...> - 2009-05-25 14:42:17
|
Update of /cvsroot/gbif/portal-service/src/main/java/org/gbif/portal/service/impl In directory ddv4jf1.ch3.sourceforge.com:/tmp/cvs-serv23610/src/main/java/org/gbif/portal/service/impl Modified Files: GeospatialManagerImpl.java Log Message: Bug 209 Enhancement: Included a table "Countries providing data for Map" on each Country page. Index: GeospatialManagerImpl.java =================================================================== RCS file: /cvsroot/gbif/portal-service/src/main/java/org/gbif/portal/service/impl/GeospatialManagerImpl.java,v retrieving revision 1.47 retrieving revision 1.48 diff -C2 -d -r1.47 -r1.48 *** GeospatialManagerImpl.java 24 Feb 2009 15:39:05 -0000 1.47 --- GeospatialManagerImpl.java 25 May 2009 14:42:10 -0000 1.48 *************** *** 335,338 **** --- 335,346 ---- } + + public List<CountDTO> getCountryCountsForCountry(String isoCountryCode, boolean geoRefOnly, Locale locale) throws ServiceException { + List<Object[]> counts = countryDAO.getCountryCountsForCountry(isoCountryCode, geoRefOnly, locale); + + + return countDTOFactory.createDTOList(counts); + } + /** * @see org.gbif.portal.service.GeospatialManager#getDataProviderCountsForCountry(java.lang.String) |
|
From: Jose C. <jos...@us...> - 2009-05-25 14:42:16
|
Update of /cvsroot/gbif/portal-web/src/main/java/org/gbif/portal/web/controller/geography In directory ddv4jf1.ch3.sourceforge.com:/tmp/cvs-serv23573/src/main/java/org/gbif/portal/web/controller/geography Modified Files: GeographyController.java Log Message: Bug 209 Enhancement: Included a table "Countries providing data for Map" on each Country page. Index: GeographyController.java =================================================================== RCS file: /cvsroot/gbif/portal-web/src/main/java/org/gbif/portal/web/controller/geography/GeographyController.java,v retrieving revision 1.19 retrieving revision 1.20 diff -C2 -d -r1.19 -r1.20 *** GeographyController.java 19 Mar 2008 20:19:40 -0000 1.19 --- GeographyController.java 25 May 2009 14:42:06 -0000 1.20 *************** *** 54,57 **** --- 54,58 ---- protected String countryModelKey = "country"; protected String resourceCountsModelKey = "resourceCounts"; + protected String countryCountsModelKey = "countryCounts"; protected String hostedModelKey = "hosted"; protected boolean sortResourcesByCount = false; *************** *** 78,84 **** if(country!=null){ - //sort counts into descending order List<CountDTO> resourceCounts = geospatialManager.getDataResourceCountsForCountry(country.getIsoCountryCode(), true); if(sortResourcesByCount){ Collections.sort(resourceCounts, new Comparator<CountDTO>(){ --- 79,85 ---- if(country!=null){ //sort counts into descending order List<CountDTO> resourceCounts = geospatialManager.getDataResourceCountsForCountry(country.getIsoCountryCode(), true); + List<CountDTO> countryCounts = geospatialManager.getCountryCountsForCountry(country.getIsoCountryCode(), true, locale); if(sortResourcesByCount){ Collections.sort(resourceCounts, new Comparator<CountDTO>(){ *************** *** 102,105 **** --- 103,107 ---- mav.addObject(countryModelKey, country); mav.addObject(resourceCountsModelKey, resourceCounts); + mav.addObject(countryCountsModelKey, countryCounts); if(logger.isDebugEnabled()) |
|
From: Jose C. <jos...@us...> - 2009-05-25 14:42:16
|
Update of /cvsroot/gbif/portal-service/src/main/java/org/gbif/portal/dao/geospatial/impl/hibernate In directory ddv4jf1.ch3.sourceforge.com:/tmp/cvs-serv23610/src/main/java/org/gbif/portal/dao/geospatial/impl/hibernate Modified Files: CountryDAOImpl.java Log Message: Bug 209 Enhancement: Included a table "Countries providing data for Map" on each Country page. Index: CountryDAOImpl.java =================================================================== RCS file: /cvsroot/gbif/portal-service/src/main/java/org/gbif/portal/dao/geospatial/impl/hibernate/CountryDAOImpl.java,v retrieving revision 1.22 retrieving revision 1.23 diff -C2 -d -r1.22 -r1.23 *** CountryDAOImpl.java 24 Feb 2009 15:39:05 -0000 1.22 --- CountryDAOImpl.java 25 May 2009 14:42:10 -0000 1.23 *************** *** 217,220 **** --- 217,266 ---- }); } + + /** + * @see org.gbif.portal.dao.resources.DataResourceDAO#getCountryCountsForCountry(java.lang.Long) + */ + @SuppressWarnings("unchecked") + public List<Object[]> getCountryCountsForCountry(final String isoCountryCode, final boolean geoRefOnly, final Locale locale) { + HibernateTemplate template = getHibernateTemplate(); + return (List) template.execute(new HibernateCallback() { + public Object doInHibernate(Session session) { + StringBuffer sb = new StringBuffer("select dp.iso_country_code as the_iso_country_code, cn.name as cn_name,"); + if(geoRefOnly){ + sb.append(" sum(rc.occurrence_coordinate_count) as the_count from resource_country rc" + + " inner join data_resource dr on rc.data_resource_id=dr.id " + + " inner join data_provider dp on dr.data_provider_id=dp.id " + + " inner join country_name cn on dp.iso_country_code=cn.iso_country_code" + + " where "); + } else { + sb.append(" sum(rc.count) as the_count from resource_country rc" + + " inner join data_resource dr on rc.data_resource_id=dr.id " + + " inner join data_provider dp on dr.data_provider_id=dp.id " + + " inner join country_name cn on dp.iso_country_code=cn.iso_country_code" + + " where "); + } + if(isoCountryCode!=null){ + sb.append("rc.iso_country_code=:isoCountryCode and cn.locale=:locale and"); + } + sb.append(" dr.deleted is null"); + + if(geoRefOnly){ + sb.append(" rc.occurrence_coordinate_count>0"); + } + sb.append(" group by cn_name"); + SQLQuery query = session.createSQLQuery(sb.toString()); + if(isoCountryCode!=null) { + query.setParameter("isoCountryCode", isoCountryCode); + query.setParameter("locale", getLocaleForQuery(locale)); + query.addScalar("the_iso_country_code", Hibernate.STRING); + query.addScalar("cn_name", Hibernate.STRING); + query.addScalar("the_count", Hibernate.INTEGER); + } + query.setCacheable(true); + logger.debug("query is: " + sb.toString()); + return query.list(); + } + }); + } /** |