February 2014 - Echobase-commits - mailman.cloud.codelutin.com

r967 - trunk/src/site/en/rst
by tchemit＠users.forge.codelutin.com 28 Feb '14

28 Feb '14

Author: tchemit Date: 2014-02-28 19:55:22 +0100 (Fri, 28 Feb 2014) New Revision: 967 Url: http://codelutin.com/projects/echobase/repository/revisions/967 Log: >From scmwebeditor -- ... test commit in https Modified: trunk/src/site/en/rst/model.rst Modified: trunk/src/site/en/rst/model.rst =================================================================== --- trunk/src/site/en/rst/model.rst 2014-02-28 18:54:38 UTC (rev 966) +++ trunk/src/site/en/rst/model.rst 2014-02-28 18:55:22 UTC (rev 967) @@ -36,7 +36,7 @@ Présentation ------------ -*TODO Translate me* +*TODO DOme Dome Translate me* On donne ici des explications sur le modèle.

1 0

r966 - trunk/src/site/en/rst
by tchemit＠users.forge.codelutin.com 28 Feb '14

28 Feb '14

Author: tchemit Date: 2014-02-28 19:54:38 +0100 (Fri, 28 Feb 2014) New Revision: 966 Url: http://codelutin.com/projects/echobase/repository/revisions/966 Log: >From scmwebeditor -- Translate me Modified: trunk/src/site/en/rst/model.rst Modified: trunk/src/site/en/rst/model.rst =================================================================== --- trunk/src/site/en/rst/model.rst 2014-02-28 18:52:24 UTC (rev 965) +++ trunk/src/site/en/rst/model.rst 2014-02-28 18:54:38 UTC (rev 966) @@ -43,9 +43,9 @@ Téléchargement ============== -- `Télécharger le modèle au format argoUML`_. +- `Download model (argoUML format)`_. -- `Télécharger le modèle au format freeMind`_. +- `Download model (freeMind format)`_. Modèle du référentiel --------------------- @@ -81,9 +81,8 @@ - `Sous modèle pêcherie`_ -.. _Télécharger le modèle au format argoUML: http://svn.codelutin.com/echobase/trunk/echobase-domain/src/main/xmi/echoba… -.. _Télécharger le modèle au format freeMind: http://svn.codelutin.com/echobase/trunk/src/doc/model/EchoBase_structure_V5… - +.. _Download model (argoUML format): http://svn.codelutin.com/echobase/trunk/echobase-domain/src/main/xmi/echoba… +.. _Download model (freeMind format): http://svn.codelutin.com/echobase/trunk/src/doc/model/EchoBase_structure_V5… .. _Vue générale du référentiel: model/referenceAll.png .. _Référentiel AcousticInstrument: model/referenceAcousticInstrument.png .. _Référentiel AreaOfOperation: model/referenceAreaOfOperation.png

1 0

r965 - trunk/src/site/en/rst
by tchemit＠users.forge.codelutin.com 28 Feb '14

28 Feb '14

Author: tchemit Date: 2014-02-28 19:52:24 +0100 (Fri, 28 Feb 2014) New Revision: 965 Url: http://codelutin.com/projects/echobase/repository/revisions/965 Log: >From scmwebeditor -- Modified: trunk/src/site/en/rst/model.rst Modified: trunk/src/site/en/rst/model.rst =================================================================== --- trunk/src/site/en/rst/model.rst 2014-02-28 17:45:12 UTC (rev 964) +++ trunk/src/site/en/rst/model.rst 2014-02-28 18:52:24 UTC (rev 965) @@ -22,9 +22,9 @@ .. * #L% .. - -=============== -Modèle Echobase -=============== +============== +Echobase model +============== .. contents:: Table des matières :depth: 2

1 0

r964 - in trunk: . echobase-services/src/main/java/fr/ifremer/echobase/services/service/spatial echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/dbeditor echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/embeddedApplication echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/exportDb echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/exportQuery echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/impor
by tchemit＠users.forge.codelutin.com 28 Feb '14

28 Feb '14

Author: tchemit Date: 2014-02-28 18:45:12 +0100 (Fri, 28 Feb 2014) New Revision: 964 Url: http://codelutin.com/projects/echobase/repository/revisions/964 Log: refs #4194: Visualisation des donn?\195?\169es spatiales Added: trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/spatial/RefreshSpatialViews.java trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/spatial/Show.java trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/spatial/ShowMap.java trunk/echobase-ui/src/main/webapp/WEB-INF/jsp/spatial/show.jsp trunk/echobase-ui/src/main/webapp/WEB-INF/jsp/spatial/showMap.jsp Removed: trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/spatial/DisplaySpatialData.java trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/spatial/GetVoyageDataMetadata.java trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/spatial/Save.java trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/spatial/ShowSpatialData.java trunk/echobase-ui/src/main/webapp/WEB-INF/jsp/spatial/showData.jsp trunk/echobase-ui/src/main/webapp/WEB-INF/jsp/spatial/showDataMap.jsp Modified: trunk/echobase-services/src/main/java/fr/ifremer/echobase/services/service/spatial/SpatialService.java trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/Home.java trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/dbeditor/AbstractLoadPage.java trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/dbeditor/ImportTable.java trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/dbeditor/SaveEntity.java trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/embeddedApplication/Build.java trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/embeddedApplication/Configure.java trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/embeddedApplication/Download.java trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/exportDb/Download.java trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/exportQuery/AbstractEditExportQuery.java trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/exportQuery/CloneExportQuery.java trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/exportQuery/CreateNewExportQuery.java trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/exportQuery/DeleteExportQuery.java trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/exportQuery/EditExportQuery.java trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/exportQuery/SaveExportQuery.java trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/exportQuery/ShowExportQuery.java trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/importData/AbstractLaunchImport.java trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/importData/CreateMission.java trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/importDb/Import.java trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/removeData/ConfirmDelete.java trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/removeData/Delete.java trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/user/Create.java trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/user/Delete.java trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/user/Update.java trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/workingDb/AbstractWorkingDbAction.java trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/workingDb/AddSpatial.java trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/workingDb/Connect.java trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/workingDb/Create.java trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/workingDb/CreatePostgresDb.java trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/workingDb/Delete.java trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/workingDb/Disconnect.java trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/workingDb/ShowList.java trunk/echobase-ui/src/main/resources/config/struts-spatial.xml trunk/echobase-ui/src/main/resources/i18n/echobase-ui_en_GB.properties trunk/echobase-ui/src/main/resources/i18n/echobase-ui_fr_FR.properties trunk/echobase-ui/src/main/webapp/WEB-INF/decorators.xml trunk/echobase-ui/src/main/webapp/WEB-INF/includes/header.jsp trunk/echobase-ui/src/main/webapp/js/gridHelper.js trunk/pom.xml Modified: trunk/echobase-services/src/main/java/fr/ifremer/echobase/services/service/spatial/SpatialService.java =================================================================== --- trunk/echobase-services/src/main/java/fr/ifremer/echobase/services/service/spatial/SpatialService.java 2014-02-28 14:08:22 UTC (rev 963) +++ trunk/echobase-services/src/main/java/fr/ifremer/echobase/services/service/spatial/SpatialService.java 2014-02-28 17:45:12 UTC (rev 964) @@ -121,4 +121,28 @@ } } } + + /** + * To update all posgis materialized views.. + * + * @since 2.6 + */ + public void updatePostgisViews() { + + if (isSpatialAware()) { + + // try the update only for postgresql + try { + if (log.isInfoEnabled()) { + log.info("Will try to refresh all spatial views..."); + } + persistenceService.executeSQL("SELECT echobase_refresh_views();"); + persistenceService.commit(); + } catch (Exception e) { + if (log.isErrorEnabled()) { + log.error("Could not refresh spatial views", e); + } + } + } + } } Modified: trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/Home.java =================================================================== --- trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/Home.java 2014-02-28 14:08:22 UTC (rev 963) +++ trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/Home.java 2014-02-28 17:45:12 UTC (rev 964) @@ -37,7 +37,7 @@ public String execute() throws Exception { if (!getEchoBaseSession().isWorkingDbSelected()) { - addFlashMessage(_("echobase.info.no.workingDb.selected")); + addFlashMessage(t("echobase.info.no.workingDb.selected")); } return SUCCESS; } Modified: trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/dbeditor/AbstractLoadPage.java =================================================================== --- trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/dbeditor/AbstractLoadPage.java 2014-02-28 14:08:22 UTC (rev 963) +++ trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/dbeditor/AbstractLoadPage.java 2014-02-28 17:45:12 UTC (rev 964) @@ -85,7 +85,7 @@ if (entityType == null) { // no table selected - addFlashMessage(_("echobase.info.no.table.selected")); + addFlashMessage(t("echobase.info.no.table.selected")); } else { // load table metas Modified: trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/dbeditor/ImportTable.java =================================================================== --- trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/dbeditor/ImportTable.java 2014-02-28 14:08:22 UTC (rev 963) +++ trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/dbeditor/ImportTable.java 2014-02-28 17:45:12 UTC (rev 964) @@ -143,7 +143,7 @@ false); error = eee; - addFlashError(_("echobase.info.import.failed")); + addFlashError(t("echobase.info.import.failed")); if (log.isErrorEnabled()) { log.error("Error while import ", eee); Modified: trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/dbeditor/SaveEntity.java =================================================================== --- trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/dbeditor/SaveEntity.java 2014-02-28 14:08:22 UTC (rev 963) +++ trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/dbeditor/SaveEntity.java 2014-02-28 17:45:12 UTC (rev 964) @@ -26,6 +26,7 @@ import com.google.common.collect.Maps; import fr.ifremer.echobase.entities.EchoBaseUserEntityEnum; import fr.ifremer.echobase.services.service.DbEditorService; +import fr.ifremer.echobase.services.service.spatial.SpatialService; import fr.ifremer.echobase.ui.actions.EchoBaseActionSupport; import org.apache.struts2.interceptor.ParameterAware; import org.nuiton.topia.persistence.TopiaEntity; @@ -92,6 +93,10 @@ properties, getEchoBaseSession().getUser()); + if (spatialService.isSpatialAware()) { + addFlashMessage(t("echobase.info.reload.spatialData")); + } + return SUCCESS; } @@ -108,5 +113,8 @@ @Inject protected transient DbEditorService dbEditorService; + + @Inject + protected transient SpatialService spatialService; } Modified: trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/embeddedApplication/Build.java =================================================================== --- trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/embeddedApplication/Build.java 2014-02-28 14:08:22 UTC (rev 963) +++ trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/embeddedApplication/Build.java 2014-02-28 17:45:12 UTC (rev 964) @@ -49,24 +49,24 @@ @Override protected String getSuccesMessage() { - return _("echobase.info.createEmbedded.succeded"); + return t("echobase.info.createEmbedded.succeded"); } @Override protected String getErrorMessage() { - return _("echobase.info.createEmbedded.failed"); + return t("echobase.info.createEmbedded.failed"); } @Override protected String getResultMessage(EmbeddedApplicationConfiguration model) { - String result = _("echobase.message.createEmbedded.result", + String result = t("echobase.message.createEmbedded.result", model.getActionTime()); return result; } @Override public String getActionResumeTitle() { - return _("echobase.legend.createEmbedded.resume"); + return t("echobase.legend.createEmbedded.resume"); } @Override Modified: trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/embeddedApplication/Configure.java =================================================================== --- trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/embeddedApplication/Configure.java 2014-02-28 14:08:22 UTC (rev 963) +++ trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/embeddedApplication/Configure.java 2014-02-28 17:45:12 UTC (rev 964) @@ -80,7 +80,7 @@ // gets war location File warLocation = configuration.getWarLocation(); if (!warLocation.exists()) { - addFlashError(_("echobase.error.warlocation.notFound", + addFlashError(t("echobase.error.warlocation.notFound", warLocation)); } model.setWarLocation(warLocation); @@ -88,7 +88,7 @@ voyages = userDbPersistenceService.loadSortAndDecorate(Voyage.class); if (MapUtils.isEmpty(voyages)) { - addFlashMessage(_("echobase.info.no.voyagee.found")); + addFlashMessage(t("echobase.info.no.voyagee.found")); } } Modified: trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/embeddedApplication/Download.java =================================================================== --- trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/embeddedApplication/Download.java 2014-02-28 14:08:22 UTC (rev 963) +++ trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/embeddedApplication/Download.java 2014-02-28 17:45:12 UTC (rev 964) @@ -80,13 +80,13 @@ getEchoBaseSession().getActionConfiguration(EmbeddedApplicationConfiguration.class); if (model == null) { - addFlashError(_("echobase.error.no.embeddedApplication.configurationFound")); + addFlashError(t("echobase.error.no.embeddedApplication.configurationFound")); return ERROR; } File file = model.getEmbeddedApplicationFile(); if (file == null) { - addFlashError(_("echobase.error.no.embeddedApplication.exportFileFound")); + addFlashError(t("echobase.error.no.embeddedApplication.exportFileFound")); return ERROR; } Modified: trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/exportDb/Download.java =================================================================== --- trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/exportDb/Download.java 2014-02-28 14:08:22 UTC (rev 963) +++ trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/exportDb/Download.java 2014-02-28 17:45:12 UTC (rev 964) @@ -76,13 +76,13 @@ getEchoBaseSession().getActionConfiguration(ExportDbConfiguration.class); if (model == null) { - addFlashError(_("echobase.error.no.exportDb.configurationFound")); + addFlashError(t("echobase.error.no.exportDb.configurationFound")); return ERROR; } File exportFile = model.getExportFile(); if (exportFile == null) { - addFlashError(_("echobase.error.no.exportDb.exportFileFound")); + addFlashError(t("echobase.error.no.exportDb.exportFileFound")); return ERROR; } Modified: trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/exportQuery/AbstractEditExportQuery.java =================================================================== --- trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/exportQuery/AbstractEditExportQuery.java 2014-02-28 14:08:22 UTC (rev 963) +++ trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/exportQuery/AbstractEditExportQuery.java 2014-02-28 17:45:12 UTC (rev 964) @@ -98,7 +98,7 @@ if (isNewQuery()) { // new query in progress - addFlashMessage(_("echobase.info.new.sqlQuery.inprogress")); + addFlashMessage(t("echobase.info.new.sqlQuery.inprogress")); } return INPUT; } @@ -118,10 +118,10 @@ if (!hasFieldErrors()) { if (!exportQueryService.isQueryNameValid(getQuery())) { addFieldError("query.name", - _("echobase.error.query.invalid.name")); + t("echobase.error.query.invalid.name")); } else if (!exportQueryService.isQueryNameAvailable(getQuery())) { addFieldError("query.name", - _("echobase.error.query.name.already.exists")); + t("echobase.error.query.name.already.exists")); } else { try { exportQueryService.testSql(getQuery().getSqlQuery()); @@ -131,7 +131,7 @@ log.warn("Invalid sql ", cause); } addFieldError("query.sqlQuery", - _("echobase.error.invalid.sql", cause.getMessage())); + t("echobase.error.invalid.sql", cause.getMessage())); } } } Modified: trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/exportQuery/CloneExportQuery.java =================================================================== --- trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/exportQuery/CloneExportQuery.java 2014-02-28 14:08:22 UTC (rev 963) +++ trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/exportQuery/CloneExportQuery.java 2014-02-28 17:45:12 UTC (rev 964) @@ -64,7 +64,7 @@ } String result = super.execute(); - addFlashMessage(_("echobase.info.query.cloned")); + addFlashMessage(t("echobase.info.query.cloned")); return result; } Modified: trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/exportQuery/CreateNewExportQuery.java =================================================================== --- trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/exportQuery/CreateNewExportQuery.java 2014-02-28 14:08:22 UTC (rev 963) +++ trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/exportQuery/CreateNewExportQuery.java 2014-02-28 17:45:12 UTC (rev 964) @@ -85,7 +85,7 @@ queries = exportQueryService.loadSortAndDecorate(ExportQuery.class); // new query in progress - addFlashMessage(_("echobase.info.new.sqlQuery.inprogress")); + addFlashMessage(t("echobase.info.new.sqlQuery.inprogress")); return SUCCESS; } Modified: trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/exportQuery/DeleteExportQuery.java =================================================================== --- trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/exportQuery/DeleteExportQuery.java 2014-02-28 14:08:22 UTC (rev 963) +++ trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/exportQuery/DeleteExportQuery.java 2014-02-28 17:45:12 UTC (rev 964) @@ -65,7 +65,7 @@ } exportQueryService.delete(getQuery().getTopiaId()); query = null; - addFlashMessage(_("echobase.info.query.removed")); + addFlashMessage(t("echobase.info.query.removed")); return SUCCESS; } Modified: trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/exportQuery/EditExportQuery.java =================================================================== --- trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/exportQuery/EditExportQuery.java 2014-02-28 14:08:22 UTC (rev 963) +++ trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/exportQuery/EditExportQuery.java 2014-02-28 17:45:12 UTC (rev 964) @@ -100,7 +100,7 @@ // this user can not update selected query addFlashMessage( - _("echobase.info.sqlQuery.not.modifiable")); + t("echobase.info.sqlQuery.not.modifiable")); } // test query @@ -121,7 +121,7 @@ log.warn("Invalid sql ", cause); } addFieldError("query.sqlQuery", - _("echobase.error.invalid.sql", cause.getMessage())); + t("echobase.error.invalid.sql", cause.getMessage())); result = false; } return result; Modified: trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/exportQuery/SaveExportQuery.java =================================================================== --- trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/exportQuery/SaveExportQuery.java 2014-02-28 14:08:22 UTC (rev 963) +++ trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/exportQuery/SaveExportQuery.java 2014-02-28 17:45:12 UTC (rev 964) @@ -58,9 +58,9 @@ String result = super.execute(); if (newQuery) { - addFlashMessage(_("echobase.info.query.created")); + addFlashMessage(t("echobase.info.query.created")); } else { - addFlashMessage(_("echobase.info.query.saved")); + addFlashMessage(t("echobase.info.query.saved")); } return result; } Modified: trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/exportQuery/ShowExportQuery.java =================================================================== --- trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/exportQuery/ShowExportQuery.java 2014-02-28 14:08:22 UTC (rev 963) +++ trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/exportQuery/ShowExportQuery.java 2014-02-28 17:45:12 UTC (rev 964) @@ -88,7 +88,7 @@ if (queries.isEmpty()) { // no query saved - addFlashMessage(_("echobase.info.no.sqlQuery.saved")); + addFlashMessage(t("echobase.info.no.sqlQuery.saved")); } return SUCCESS; } Modified: trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/importData/AbstractLaunchImport.java =================================================================== --- trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/importData/AbstractLaunchImport.java 2014-02-28 14:08:22 UTC (rev 963) +++ trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/importData/AbstractLaunchImport.java 2014-02-28 17:45:12 UTC (rev 964) @@ -25,10 +25,14 @@ import fr.ifremer.echobase.services.service.importdata.AbstractImportConfiguration; import fr.ifremer.echobase.services.service.importdata.AbstractImportDataService; +import fr.ifremer.echobase.services.service.importdb.ImportDbMode; +import fr.ifremer.echobase.services.service.spatial.SpatialService; import fr.ifremer.echobase.ui.actions.AbstractWaitAndExecAction; import org.apache.commons.logging.Log; import org.apache.commons.logging.LogFactory; +import javax.inject.Inject; + /** * Abstract action to launch an data import. * @@ -42,23 +46,26 @@ /** Logger. */ private static final Log log = LogFactory.getLog(AbstractLaunchImport.class); + @Inject + protected transient SpatialService spatialService; + protected AbstractLaunchImport(Class<M> modelType, Class<S> serviceType) { super(modelType, serviceType); } @Override protected String getSuccesMessage() { - return _("echobase.info.importData.succeded"); + return t("echobase.info.importData.succeded"); } @Override protected String getErrorMessage() { - return _("echobase.info.importData.failed"); + return t("echobase.info.importData.failed"); } @Override protected String getResultMessage(M model) { - String message = _("echobase.message.importData.result", + String message = t("echobase.message.importData.result", model.getActionTime(), model.getResultMessage()); if (log.isInfoEnabled()) { log.info("Result: " + message); @@ -68,7 +75,7 @@ @Override public String getActionResumeTitle() { - return _("echobase.legend.importData.result.resume"); + return t("echobase.legend.importData.result.resume"); } @Override @@ -84,6 +91,9 @@ @Override protected void closeAction(M model) throws Exception { destroyModel(model); + if (spatialService.isSpatialAware()) { + addFlashMessage(t("echobase.info.reload.spatialData")); + } } } Modified: trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/importData/CreateMission.java =================================================================== --- trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/importData/CreateMission.java 2014-02-28 14:08:22 UTC (rev 963) +++ trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/importData/CreateMission.java 2014-02-28 17:45:12 UTC (rev 964) @@ -71,11 +71,11 @@ log.info("Created mission : " + missionSaved.getTopiaId()); } addFlashMessage( - _("echobase.information.mission.created", missionSaved.getName())); + t("echobase.information.mission.created", missionSaved.getName())); result = SUCCESS; } catch (MissionNameAlreadyExistException e) { addFieldError("mission.name", - _("echobase.error.mission.name.already.exist")); + t("echobase.error.mission.name.already.exist")); } return result; } Modified: trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/importDb/Import.java =================================================================== --- trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/importDb/Import.java 2014-02-28 14:08:22 UTC (rev 963) +++ trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/importDb/Import.java 2014-02-28 17:45:12 UTC (rev 964) @@ -25,6 +25,7 @@ import fr.ifremer.echobase.entities.EchoBaseUser; import fr.ifremer.echobase.services.service.importdb.ImportDbConfiguration; +import fr.ifremer.echobase.services.service.importdb.ImportDbMode; import fr.ifremer.echobase.services.service.importdb.ImportDbService; import fr.ifremer.echobase.ui.actions.AbstractWaitAndExecAction; import org.apache.commons.logging.Log; @@ -49,12 +50,12 @@ @Override protected String getSuccesMessage() { - return _("echobase.info.importDb.succeded"); + return t("echobase.info.importDb.succeded"); } @Override protected String getErrorMessage() { - return _("echobase.info.importDb.failed"); + return t("echobase.info.importDb.failed"); } @Override @@ -62,12 +63,12 @@ String message; switch (model.getImportDbMode()) { case REFERENTIAL: - message = _("echobase.importDb.referentialResult", + message = t("echobase.importDb.referentialResult", model.getActionTime()); break; case FREE: - message = _("echobase.importDb.freeResult", + message = t("echobase.importDb.freeResult", model.getActionTime()); break; default: @@ -81,7 +82,7 @@ @Override public String getActionResumeTitle() { - return _("echobase.legend.importDb.resume"); + return t("echobase.legend.importDb.resume"); } @Override Modified: trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/removeData/ConfirmDelete.java =================================================================== --- trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/removeData/ConfirmDelete.java 2014-02-28 14:08:22 UTC (rev 963) +++ trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/removeData/ConfirmDelete.java 2014-02-28 17:45:12 UTC (rev 964) @@ -92,7 +92,7 @@ String voyage = decorator.toString(voyageEntity); String importType = importTypes.get(importLog.getImportType().name()); - String result = _("echobase.common.importLogToDelete", + String result = t("echobase.common.importLogToDelete", voyage, importType, importLog.getImportDate()); Modified: trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/removeData/Delete.java =================================================================== --- trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/removeData/Delete.java 2014-02-28 14:08:22 UTC (rev 963) +++ trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/removeData/Delete.java 2014-02-28 17:45:12 UTC (rev 964) @@ -25,10 +25,13 @@ import fr.ifremer.echobase.services.service.removedata.RemoveDataConfiguration; import fr.ifremer.echobase.services.service.removedata.RemoveDataService; +import fr.ifremer.echobase.services.service.spatial.SpatialService; import fr.ifremer.echobase.ui.actions.AbstractWaitAndExecAction; import org.apache.commons.logging.Log; import org.apache.commons.logging.LogFactory; +import javax.inject.Inject; + /** * Delete a selected import Log. * @@ -42,23 +45,26 @@ /** Logger. */ private static final Log log = LogFactory.getLog(Delete.class); + @Inject + protected transient SpatialService spatialService; + public Delete() { super(RemoveDataConfiguration.class, RemoveDataService.class); } @Override protected String getSuccesMessage() { - return _("echobase.info.removeData.succeded"); + return t("echobase.info.removeData.succeded"); } @Override protected String getErrorMessage() { - return _("echobase.info.removeData.failed"); + return t("echobase.info.removeData.failed"); } @Override protected String getResultMessage(RemoveDataConfiguration model) { - String message = _("echobase.message.removeData.result", + String message = t("echobase.message.removeData.result", model.getActionTime(), model.getResultMessage()); if (log.isInfoEnabled()) { log.info("Result: " + message); @@ -68,7 +74,7 @@ @Override public String getActionResumeTitle() { - return _("echobase.legend.removeData.resume"); + return t("echobase.legend.removeData.resume"); } @Override @@ -83,5 +89,9 @@ @Override protected void closeAction(RemoveDataConfiguration model) throws Exception { destroyModel(model); + + if (spatialService.isSpatialAware()) { + addFlashMessage(t("echobase.info.reload.spatialData")); + } } } Deleted: trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/spatial/DisplaySpatialData.java =================================================================== --- trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/spatial/DisplaySpatialData.java 2014-02-28 14:08:22 UTC (rev 963) +++ trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/spatial/DisplaySpatialData.java 2014-02-28 17:45:12 UTC (rev 964) @@ -1,52 +0,0 @@ -package fr.ifremer.echobase.ui.actions.spatial; - -/* - * #%L - * EchoBase :: UI - * $Id$ - * $HeadURL$ - * %% - * Copyright (C) 2011 - 2013 Ifremer, Codelutin - * %% - * This program is free software: you can redistribute it and/or modify - * it under the terms of the GNU Affero General Public License as published by - * the Free Software Foundation, either version 3 of the License, or - * (at your option) any later version. - * - * This program is distributed in the hope that it will be useful, - * but WITHOUT ANY WARRANTY; without even the implied warranty of - * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the - * GNU General Public License for more details. - * - * You should have received a copy of the GNU Affero General Public License - * along with this program. If not, see <http://www.gnu.org/licenses/>. - * #L% - */ - -import fr.ifremer.echobase.ui.actions.EchoBaseActionSupport; - -/** - * To display only the spatial data map. - * <p/> - * This action will be reused to raster spatial data maps. - * - * @author tchemit <chemit(a)codelutin.com> - * @since 2.2 - */ -public class DisplaySpatialData extends EchoBaseActionSupport { - - private static final long serialVersionUID = 1L; - - protected final ShowSpatialModel model = new ShowSpatialModel(); - - public ShowSpatialModel getModel() { - return model; - } - - @Override - public String execute() throws Exception { - - - return SUCCESS; - } -} Deleted: trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/spatial/GetVoyageDataMetadata.java =================================================================== --- trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/spatial/GetVoyageDataMetadata.java 2014-02-28 14:08:22 UTC (rev 963) +++ trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/spatial/GetVoyageDataMetadata.java 2014-02-28 17:45:12 UTC (rev 964) @@ -1,91 +0,0 @@ -package fr.ifremer.echobase.ui.actions.spatial; - -/* - * #%L - * EchoBase :: UI - * $Id$ - * $HeadURL$ - * %% - * Copyright (C) 2011 - 2013 Ifremer, Codelutin - * %% - * This program is free software: you can redistribute it and/or modify - * it under the terms of the GNU Affero General Public License as published by - * the Free Software Foundation, either version 3 of the License, or - * (at your option) any later version. - * - * This program is distributed in the hope that it will be useful, - * but WITHOUT ANY WARRANTY; without even the implied warranty of - * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the - * GNU General Public License for more details. - * - * You should have received a copy of the GNU Affero General Public License - * along with this program. If not, see <http://www.gnu.org/licenses/>. - * #L% - */ - -import com.google.common.base.Preconditions; -import com.google.common.collect.Maps; -import fr.ifremer.echobase.entities.data.Voyage; -import fr.ifremer.echobase.entities.references.DataMetadata; -import fr.ifremer.echobase.services.service.UserDbPersistenceService; -import fr.ifremer.echobase.services.service.spatial.SpatialService; -import fr.ifremer.echobase.ui.actions.EchoBaseActionSupport; -import org.apache.commons.lang3.StringUtils; - -import javax.inject.Inject; -import java.util.Map; - -/** - * Obtain all dataMetadata used for a given voyage. - * - * @author tchemit <chemit(a)codelutin.com> - * @since 2.2 - */ -public class GetVoyageDataMetadata extends EchoBaseActionSupport { - - private static final long serialVersionUID = 1L; - - /** Selected voyage id. */ - protected String voyageId; - - /** Universe of result. */ - protected Map<String, String> data; - - public void setVoyageId(String voyageId) { - this.voyageId = voyageId; - } - - public Map<String, String> getData() { - return data; - } - - @Override - public String execute() throws Exception { - - if (StringUtils.isEmpty(voyageId)) { - data = Maps.newLinkedHashMap(); - - } else { - - Voyage voyage = userDbPersistenceService.getVoyage(voyageId); - - Preconditions.checkNotNull( - voyage, - "Could not find voyage with id " + voyageId); - - data = userDbPersistenceService.loadSortAndDecorate(DataMetadata.class); - } - - return SUCCESS; - } - - //------------------------------------------------------------------------// - //-- Injected objects // - //------------------------------------------------------------------------// - - @Inject - protected transient UserDbPersistenceService userDbPersistenceService; - - @Inject - protected transient SpatialService spatialService; -} \ No newline at end of file Added: trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/spatial/RefreshSpatialViews.java =================================================================== --- trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/spatial/RefreshSpatialViews.java (rev 0) +++ trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/spatial/RefreshSpatialViews.java 2014-02-28 17:45:12 UTC (rev 964) @@ -0,0 +1,28 @@ +package fr.ifremer.echobase.ui.actions.spatial; + +import fr.ifremer.echobase.services.service.spatial.SpatialService; +import fr.ifremer.echobase.ui.actions.EchoBaseActionSupport; + +import javax.inject.Inject; + +/** + * Created on 2/28/14. + * + * @author Tony Chemit <chemit(a)codelutin.com> + * @since 2.6 + */ +public class RefreshSpatialViews extends EchoBaseActionSupport { + + private static final long serialVersionUID = 1L; + + @Inject + protected transient SpatialService spatialService; + + @Override + public String execute() throws Exception { + + spatialService.updatePostgisViews(); + + return SUCCESS; + } +} Property changes on: trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/spatial/RefreshSpatialViews.java ___________________________________________________________________ Added: svn:keywords + Author Date Id Revision Added: svn:eol-style + native Deleted: trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/spatial/Save.java =================================================================== --- trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/spatial/Save.java 2014-02-28 14:08:22 UTC (rev 963) +++ trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/spatial/Save.java 2014-02-28 17:45:12 UTC (rev 964) @@ -1,36 +0,0 @@ -package fr.ifremer.echobase.ui.actions.spatial; - -/* - * #%L - * EchoBase :: UI - * $Id$ - * $HeadURL$ - * %% - * Copyright (C) 2011 - 2013 Ifremer, Codelutin - * %% - * This program is free software: you can redistribute it and/or modify - * it under the terms of the GNU Affero General Public License as published by - * the Free Software Foundation, either version 3 of the License, or - * (at your option) any later version. - * - * This program is distributed in the hope that it will be useful, - * but WITHOUT ANY WARRANTY; without even the implied warranty of - * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the - * GNU General Public License for more details. - * - * You should have received a copy of the GNU Affero General Public License - * along with this program. If not, see <http://www.gnu.org/licenses/>. - * #L% - */ - -/** - * To save the selected spatial data. - * - * @author tchemit <chemit(a)codelutin.com> - * @since 2.2 - */ -public class Save { - - protected final ShowSpatialModel model = new ShowSpatialModel(); - -} Copied: trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/spatial/Show.java (from rev 956, trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/spatial/ShowSpatialData.java) =================================================================== --- trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/spatial/Show.java (rev 0) +++ trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/spatial/Show.java 2014-02-28 17:45:12 UTC (rev 964) @@ -0,0 +1,84 @@ +package fr.ifremer.echobase.ui.actions.spatial; + +/* + * #%L + * EchoBase :: UI + * $Id$ + * $HeadURL$ + * %% + * Copyright (C) 2011 - 2013 Ifremer, Codelutin + * %% + * This program is free software: you can redistribute it and/or modify + * it under the terms of the GNU Affero General Public License as published by + * the Free Software Foundation, either version 3 of the License, or + * (at your option) any later version. + * + * This program is distributed in the hope that it will be useful, + * but WITHOUT ANY WARRANTY; without even the implied warranty of + * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the + * GNU General Public License for more details. + * + * You should have received a copy of the GNU Affero General Public License + * along with this program. If not, see <http://www.gnu.org/licenses/>. + * #L% + */ + +import fr.ifremer.echobase.entities.data.Voyage; +import fr.ifremer.echobase.services.service.UserDbPersistenceService; +import fr.ifremer.echobase.services.service.spatial.SpatialService; +import fr.ifremer.echobase.ui.actions.EchoBaseActionSupport; + +import javax.inject.Inject; +import java.util.Map; + +/** + * To configure and show spatial datas. + * + * @author tchemit <chemit(a)codelutin.com> + * @since 2.2 + */ +public class Show extends EchoBaseActionSupport { + + private static final long serialVersionUID = 1L; + + @Inject + protected transient UserDbPersistenceService userDbPersistenceService; + + @Inject + protected transient SpatialService spatialService; + + protected final ShowSpatialModel model = new ShowSpatialModel(); + + public ShowSpatialModel getModel() { + return model; + } + + protected boolean canAddSpatial; + + /** Universe of existing voyages. */ + protected Map<String, String> voyages; + + public Map<String, String> getVoyages() { + return voyages; + } + + public boolean isCanAddSpatial() { + return canAddSpatial; + } + + @Override + public String execute() throws Exception { + + voyages = userDbPersistenceService.loadSortAndDecorate(Voyage.class); + + model.setJdbcUrl(getServiceContext().getUserDbUrl()); + model.setWithSpatial(spatialService.isSpatialAware()); + + // can add postgis if working db is postgresql and has still no echobase + // spatial db + canAddSpatial = !model.isWithSpatial() && spatialService.isPostgresql(); + + return SUCCESS; + } + +} Copied: trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/spatial/ShowMap.java (from rev 956, trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/spatial/DisplaySpatialData.java) =================================================================== --- trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/spatial/ShowMap.java (rev 0) +++ trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/spatial/ShowMap.java 2014-02-28 17:45:12 UTC (rev 964) @@ -0,0 +1,59 @@ +package fr.ifremer.echobase.ui.actions.spatial; + +/* + * #%L + * EchoBase :: UI + * $Id$ + * $HeadURL$ + * %% + * Copyright (C) 2011 - 2013 Ifremer, Codelutin + * %% + * This program is free software: you can redistribute it and/or modify + * it under the terms of the GNU Affero General Public License as published by + * the Free Software Foundation, either version 3 of the License, or + * (at your option) any later version. + * + * This program is distributed in the hope that it will be useful, + * but WITHOUT ANY WARRANTY; without even the implied warranty of + * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the + * GNU General Public License for more details. + * + * You should have received a copy of the GNU Affero General Public License + * along with this program. If not, see <http://www.gnu.org/licenses/>. + * #L% + */ + +import fr.ifremer.echobase.ui.actions.EchoBaseActionSupport; +import org.apache.commons.logging.Log; +import org.apache.commons.logging.LogFactory; + +/** + * To display only the spatial data map. + * <p/> + * This action will be reused to raster spatial data maps. + * + * @author tchemit <chemit(a)codelutin.com> + * @since 2.2 + */ +public class ShowMap extends EchoBaseActionSupport { + + private static final long serialVersionUID = 1L; + + /** Logger. */ + private static final Log log = LogFactory.getLog(ShowMap.class); + + protected final ShowSpatialModel model = new ShowSpatialModel(); + + public ShowSpatialModel getModel() { + return model; + } + + @Override + public String execute() throws Exception { + + if (log.isInfoEnabled()) { + log.info("Loading spatial view for voyage: " + model.getVoyageId()); + } + return SUCCESS; + } +} Deleted: trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/spatial/ShowSpatialData.java =================================================================== --- trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/spatial/ShowSpatialData.java 2014-02-28 14:08:22 UTC (rev 963) +++ trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/spatial/ShowSpatialData.java 2014-02-28 17:45:12 UTC (rev 964) @@ -1,101 +0,0 @@ -package fr.ifremer.echobase.ui.actions.spatial; - -/* - * #%L - * EchoBase :: UI - * $Id$ - * $HeadURL$ - * %% - * Copyright (C) 2011 - 2013 Ifremer, Codelutin - * %% - * This program is free software: you can redistribute it and/or modify - * it under the terms of the GNU Affero General Public License as published by - * the Free Software Foundation, either version 3 of the License, or - * (at your option) any later version. - * - * This program is distributed in the hope that it will be useful, - * but WITHOUT ANY WARRANTY; without even the implied warranty of - * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the - * GNU General Public License for more details. - * - * You should have received a copy of the GNU Affero General Public License - * along with this program. If not, see <http://www.gnu.org/licenses/>. - * #L% - */ - -import com.opensymphony.xwork2.Preparable; -import com.opensymphony.xwork2.interceptor.annotations.InputConfig; -import fr.ifremer.echobase.entities.data.Voyage; -import fr.ifremer.echobase.services.service.UserDbPersistenceService; -import fr.ifremer.echobase.services.service.spatial.SpatialService; -import fr.ifremer.echobase.ui.actions.EchoBaseActionSupport; - -import javax.inject.Inject; -import java.util.Map; - -/** - * To configure and show spatial datas. - * - * @author tchemit <chemit(a)codelutin.com> - * @since 2.2 - */ -public class ShowSpatialData extends EchoBaseActionSupport implements Preparable { - - private static final long serialVersionUID = 1L; - - protected final ShowSpatialModel model = new ShowSpatialModel(); - - public ShowSpatialModel getModel() { - return model; - } - - protected boolean canAddSpatial; - - /** Universe of existing voyages. */ - protected Map<String, String> voyages; - - public Map<String, String> getVoyages() { - return voyages; - } - - public boolean isCanAddSpatial() { - return canAddSpatial; - } - - @Override - public void prepare() throws Exception { - voyages = userDbPersistenceService.loadSortAndDecorate(Voyage.class); - - model.setJdbcUrl(getServiceContext().getUserDbUrl()); - model.setWithSpatial(spatialService.isSpatialAware()); - - // can add postgis if working db is postgresql and has still no echobase - // spatial db - canAddSpatial = !model.isWithSpatial() && spatialService.isPostgresql(); - } - - @Override - public String input() throws Exception { - - model.setWithData(false); - return SUCCESS; - } - - @InputConfig(methodName = "input") - @Override - public String execute() throws Exception { - getModel().setWithData(true); - - return SUCCESS; - } - - //------------------------------------------------------------------------// - //-- Injected objects // - //------------------------------------------------------------------------// - - @Inject - protected transient UserDbPersistenceService userDbPersistenceService; - - @Inject - protected transient SpatialService spatialService; -} Modified: trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/user/Create.java =================================================================== --- trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/user/Create.java 2014-02-28 14:08:22 UTC (rev 963) +++ trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/user/Create.java 2014-02-28 17:45:12 UTC (rev 964) @@ -66,7 +66,7 @@ userService.createOrUpdate(userToCreate); // add info message - addFlashMessage(_("echobase.info.user.create", userEmail)); + addFlashMessage(t("echobase.info.user.create", userEmail)); return SUCCESS; } @@ -81,7 +81,7 @@ // empty user login addFieldError("user.email", - _("echobase.error.required.email")); + t("echobase.error.required.email")); } else { // check login not already used @@ -96,7 +96,7 @@ } if (login != null) { addFieldError("user.email", - _("echobase.error.email.already.used")); + t("echobase.error.email.already.used")); } } @@ -105,7 +105,7 @@ // empty user password addFieldError("user.password", - _("echobase.error.required.password")); + t("echobase.error.required.password")); } } Modified: trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/user/Delete.java =================================================================== --- trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/user/Delete.java 2014-02-28 14:08:22 UTC (rev 963) +++ trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/user/Delete.java 2014-02-28 17:45:12 UTC (rev 964) @@ -85,7 +85,7 @@ userService.delete(userToDelete); // add info message - addFlashMessage(_("echobase.info.user.delete", userEmail)); + addFlashMessage(t("echobase.info.user.delete", userEmail)); return SUCCESS; } Modified: trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/user/Update.java =================================================================== --- trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/user/Update.java 2014-02-28 14:08:22 UTC (rev 963) +++ trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/user/Update.java 2014-02-28 17:45:12 UTC (rev 964) @@ -69,7 +69,7 @@ userService.createOrUpdate(userToUpdate); // add info message - addFlashMessage(_("echobase.info.user.update", userEmail)); + addFlashMessage(t("echobase.info.user.update", userEmail)); return SUCCESS; } Modified: trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/workingDb/AbstractWorkingDbAction.java =================================================================== --- trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/workingDb/AbstractWorkingDbAction.java 2014-02-28 14:08:22 UTC (rev 963) +++ trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/workingDb/AbstractWorkingDbAction.java 2014-02-28 17:45:12 UTC (rev 964) @@ -95,7 +95,7 @@ // no conf saved addFlashMessage( - _("echobase.info.no.workingDbConfiguration.saved")); + t("echobase.info.no.workingDbConfiguration.saved")); } String[] ids = parameters.get("conf.topiaId"); Modified: trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/workingDb/AddSpatial.java =================================================================== --- trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/workingDb/AddSpatial.java 2014-02-28 14:08:22 UTC (rev 963) +++ trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/workingDb/AddSpatial.java 2014-02-28 17:45:12 UTC (rev 964) @@ -44,7 +44,7 @@ spatialService.addSpatialSupport(); - addFlashMessage(_("echobase.info.workingDbconfiguration.spatialStructureAdded")); + addFlashMessage(t("echobase.info.workingDbconfiguration.spatialStructureAdded")); return SUCCESS; } Modified: trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/workingDb/Connect.java =================================================================== --- trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/workingDb/Connect.java 2014-02-28 14:08:22 UTC (rev 963) +++ trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/workingDb/Connect.java 2014-02-28 17:45:12 UTC (rev 964) @@ -72,7 +72,7 @@ try { EchoBaseEntityHelper.checkJDBCConnection(jdbcConf); getEchoBaseSession().initUserDb(jdbcConf, true); - addFlashMessage(_("echobase.info.workingDbconfiguration.connected", + addFlashMessage(t("echobase.info.workingDbconfiguration.connected", conf.getUrl())); result = SUCCESS; @@ -80,11 +80,11 @@ jdbcConf = null; addFieldError( "login", - _("echobase.error.workingDbConfiguration.couldNotConnect", + t("echobase.error.workingDbConfiguration.couldNotConnect", e.getMessage())); addFieldError( "password", - _("echobase.error.workingDbConfiguration.couldNotConnect", + t("echobase.error.workingDbConfiguration.couldNotConnect", e.getMessage())); } @@ -105,11 +105,11 @@ jdbcConf = null; addFieldError( "login", - _("echobase.error.workingDbConfiguration.couldNotConnect", + t("echobase.error.workingDbConfiguration.couldNotConnect", e.getMessage())); addFieldError( "password", - _("echobase.error.workingDbConfiguration.couldNotConnect", + t("echobase.error.workingDbConfiguration.couldNotConnect", e.getMessage())); } } Modified: trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/workingDb/Create.java =================================================================== --- trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/workingDb/Create.java 2014-02-28 14:08:22 UTC (rev 963) +++ trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/workingDb/Create.java 2014-02-28 17:45:12 UTC (rev 964) @@ -45,7 +45,7 @@ // new workingDbConfiguration in progress addFlashMessage( - _("echobase.info.new.workingDbConfiguration.inprogress")); + t("echobase.info.new.workingDbConfiguration.inprogress")); return INPUT; } @@ -56,7 +56,7 @@ // new workingDbConfiguration in progress addFlashMessage( - _("echobase.info.new.workingDbConfiguration.inprogress")); + t("echobase.info.new.workingDbConfiguration.inprogress")); return INPUT; } @@ -67,8 +67,8 @@ conf = workingDbConfigurationService.create(getConf()); - addFlashMessage(_("echobase.info.workingDbconfiguration.created", - conf.getUrl())); + addFlashMessage(t("echobase.info.workingDbconfiguration.created", + conf.getUrl())); return SUCCESS; } @@ -79,7 +79,7 @@ if (StringUtils.isBlank(getConf().getUrl())) { addFieldError( "conf.url", - _("echobase.error.workingDbConfiguration.url.required")); + t("echobase.error.workingDbConfiguration.url.required")); } else { if (!isConfExists()) { @@ -90,7 +90,7 @@ boolean urlUsed = workingDbConfigurationService.isUrlAlreadyUsed(getConf().getUrl()); if (urlUsed) { addFieldError("conf.url", - _("echobase.error.workingDbConfiguration.url.already.exists")); + t("echobase.error.workingDbConfiguration.url.already.exists")); } } } @@ -98,7 +98,7 @@ if (StringUtils.isBlank(getConf().getDescription())) { addFieldError( "conf.description", - _("echobase.error.workingDbConfiguration.description.required")); + t("echobase.error.workingDbConfiguration.description.required")); } } } Modified: trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/workingDb/CreatePostgresDb.java =================================================================== --- trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/workingDb/CreatePostgresDb.java 2014-02-28 14:08:22 UTC (rev 963) +++ trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/workingDb/CreatePostgresDb.java 2014-02-28 17:45:12 UTC (rev 964) @@ -83,7 +83,7 @@ // connect to new database getEchoBaseSession().initUserDb(jdbcConf, true); - addFlashMessage(_("echobase.info.workingDb.created", conf.getUrl())); + addFlashMessage(t("echobase.info.workingDb.created", conf.getUrl())); } catch (Exception e) { @@ -106,7 +106,7 @@ if (!DriverType.POSTGRESQL.equals(getConf().getDriverType())) { addFieldError( "conf.driverType", - _("echobase.error.workingDbConfiguration.createOnlyOnPostgresql")); + t("echobase.error.workingDbConfiguration.createOnlyOnPostgresql")); } else { jdbcConf = JdbcConfiguration.newConfig( getConf().getDriverType(), getConf().getUrl(), @@ -122,11 +122,11 @@ jdbcConf = null; addFieldError( "login", - _("echobase.error.workingDbConfiguration.couldNotConnect", + t("echobase.error.workingDbConfiguration.couldNotConnect", e.getMessage())); addFieldError( "password", - _("echobase.error.workingDbConfiguration.couldNotConnect", + t("echobase.error.workingDbConfiguration.couldNotConnect", e.getMessage())); } } Modified: trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/workingDb/Delete.java =================================================================== --- trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/workingDb/Delete.java 2014-02-28 14:08:22 UTC (rev 963) +++ trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/workingDb/Delete.java 2014-02-28 17:45:12 UTC (rev 964) @@ -51,8 +51,8 @@ String result = INPUT; try { workingDbConfigurationService.delete(getConf().getTopiaId()); - addFlashMessage(_("echobase.info.workingDbconfiguration.deleted", - conf.getUrl())); + addFlashMessage(t("echobase.info.workingDbconfiguration.deleted", + conf.getUrl())); conf = null; result = SUCCESS; Modified: trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/workingDb/Disconnect.java =================================================================== --- trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/workingDb/Disconnect.java 2014-02-28 14:08:22 UTC (rev 963) +++ trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/workingDb/Disconnect.java 2014-02-28 17:45:12 UTC (rev 964) @@ -41,7 +41,7 @@ JdbcConfiguration sessionConf = getEchoBaseSession().getWorkingDbConfiguration(); getEchoBaseSession().releaseUserDb(); - addFlashMessage(_("echobase.info.workingDbconfiguration.disconnected", + addFlashMessage(t("echobase.info.workingDbconfiguration.disconnected", sessionConf.getUrl())); return SUCCESS; } Modified: trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/workingDb/ShowList.java =================================================================== --- trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/workingDb/ShowList.java 2014-02-28 14:08:22 UTC (rev 963) +++ trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/workingDb/ShowList.java 2014-02-28 17:45:12 UTC (rev 964) @@ -42,7 +42,7 @@ // no workingDbConfiguration selected addFlashMessage( - _("echobase.info.no.workingDbConfiguration.selected")); + t("echobase.info.no.workingDbConfiguration.selected")); } return SUCCESS; Modified: trunk/echobase-ui/src/main/resources/config/struts-spatial.xml =================================================================== --- trunk/echobase-ui/src/main/resources/config/struts-spatial.xml 2014-02-28 14:08:22 UTC (rev 963) +++ trunk/echobase-ui/src/main/resources/config/struts-spatial.xml 2014-02-28 17:45:12 UTC (rev 964) @@ -30,53 +30,38 @@ <package name="spatial" extends="logguedAndWithDb" namespace="/spatial"> -  - <action name="configure" method="input" - class="fr.ifremer.echobase.ui.actions.spatial.ShowSpatialData"> +  + <action name="show" + class="fr.ifremer.echobase.ui.actions.spatial.Show"> <interceptor-ref name="basicStackLogguedWithdb"/> - <result>/WEB-INF/jsp/spatial/showData.jsp</result> + <result>/WEB-INF/jsp/spatial/show.jsp</result> </action> -  - <action name="reconfigure" method="input" - class="fr.ifremer.echobase.ui.actions.spatial.ShowSpatialData"> - <interceptor-ref name="basicStackLogguedWithdb"/> - <result type="redirectAction"> - <param name="namespace">/spatial</param> - <param name="actionName">configure</param> - </result> +  + <action name="showMap" + class="fr.ifremer.echobase.ui.actions.spatial.ShowMap"> + <interceptor-ref name="prepareParamsStackLogguedWithDb"/> + <result>/WEB-INF/jsp/spatial/showMap.jsp</result> </action>  <action name="addSpatial" class="fr.ifremer.echobase.ui.actions.workingDb.AddSpatial"> <result type="redirectAction"> - <param name="actionName">configure</param> <param name="namespace">/spatial</param> + <param name="actionName">show</param> </result> </action> -  - <action name="showSpatial" - class="fr.ifremer.echobase.ui.actions.spatial.ShowSpatialData"> - <interceptor-ref name="prepareParamsStackLogguedWithDb"/> - <result>/WEB-INF/jsp/spatial/showData.jsp</result> +  + <action name="refreshSpatialViews" + class="fr.ifremer.echobase.ui.actions.spatial.RefreshSpatialViews"> + <result type="redirectAction"> + <param name="namespace">/spatial</param> + <param name="actionName">show</param> + </result> </action> -  - <action name="displaySpatial" - class="fr.ifremer.echobase.ui.actions.spatial.DisplaySpatialData"> - <interceptor-ref name="prepareParamsStackLogguedWithDb"/> - <result>/WEB-INF/jsp/spatial/showDataMap.jsp</result> - </action> - -  - <action name="getVoyageDataMetadata" - class="fr.ifremer.echobase.ui.actions.spatial.GetVoyageDataMetadata"> - <interceptor-ref name="basicStackLogguedWithdb"/> - <result type="json"/> - </action> - </package> </struts> Modified: trunk/echobase-ui/src/main/resources/i18n/echobase-ui_en_GB.properties =================================================================== --- trunk/echobase-ui/src/main/resources/i18n/echobase-ui_en_GB.properties 2014-02-28 14:08:22 UTC (rev 963) +++ trunk/echobase-ui/src/main/resources/i18n/echobase-ui_en_GB.properties 2014-02-28 17:45:12 UTC (rev 964) @@ -28,6 +28,7 @@ echobase.action.logout=Disconnect echobase.action.newQuery=New query echobase.action.newWorkingDbContiguration=New working database configuration +echobase.action.reloadSpatialData=Reload spatial data echobase.action.reloadSqlQuery=Cancel changes echobase.action.reset=Cancel echobase.action.return=Back @@ -37,7 +38,7 @@ echobase.action.show.exportDb.documentation=How to export a database echobase.action.show.import.documentation=How to import data echobase.action.show.importDb.documentation=How to import a database -echobase.action.show.showData.documentation=How to display spatial data +echobase.action.show.showSpatialData.documentation=How to display spatial data echobase.action.show.workingDb.documentation=How to connect to a working database echobase.action.toEnglish=English echobase.action.toFrench=French @@ -281,6 +282,7 @@ echobase.info.query.created=Query created echobase.info.query.removed=Query removed echobase.info.query.saved=Query saved +echobase.info.reload.spatialData=Remember to refresh spatial data in the spatial data view page\! echobase.info.removeData.failed=Import removal failed echobase.info.removeData.succeded=Successful data import removal echobase.info.reportBug=Bug report @@ -375,6 +377,7 @@ echobase.message.warnExportInProgress=Please do not close the window to access the export file echobase.message.warnImportInProgress=Please do not close the window to access the imported file echobase.message.warnRemoveDataInProgress=Please do not close the window to access remove data result +echobase.spatialView.loading=Loading data... echobase.title.confirm.deleteImportLogs=Delete data echobase.title.confirm.deleteQuery=Delete a query echobase.title.confirm.deleteWorkingDbConfiguration=Delete a configuration Modified: trunk/echobase-ui/src/main/resources/i18n/echobase-ui_fr_FR.properties =================================================================== --- trunk/echobase-ui/src/main/resources/i18n/echobase-ui_fr_FR.properties 2014-02-28 14:08:22 UTC (rev 963) +++ trunk/echobase-ui/src/main/resources/i18n/echobase-ui_fr_FR.properties 2014-02-28 17:45:12 UTC (rev 964) @@ -28,6 +28,7 @@ echobase.action.logout=Déconnexion echobase.action.newQuery=Nouvelle requête echobase.action.newWorkingDbContiguration=Nouvelle configuration de base de travail +echobase.action.reloadSpatialData=Recharger les données spatiales echobase.action.reloadSqlQuery=Annuler les modifications echobase.action.reset=Annuler echobase.action.return=Retour @@ -37,7 +38,7 @@ echobase.action.show.exportDb.documentation=Comment exporter une base echobase.action.show.import.documentation=Comment importer des données echobase.action.show.importDb.documentation=Comment importer une base -echobase.action.show.showData.documentation=Comment visualiser des données +echobase.action.show.showSpatialData.documentation=Comment visualiser des données spatiales echobase.action.show.workingDb.documentation=Comment se connecter à une base de travail echobase.action.toEnglish=En anglais echobase.action.toFrench=En français @@ -281,6 +282,7 @@ echobase.info.query.created=Requête créée echobase.info.query.removed=Requête supprimée echobase.info.query.saved=Requête mise à jour +echobase.info.reload.spatialData=Pensez à recharger les données spatiales dans la page de visualisation des données spatiales\! echobase.info.removeData.failed=La suppresion de l'import a échoué echobase.info.removeData.succeded=La suppresion de l'import a réussi echobase.info.reportBug=Rapport de bug @@ -375,6 +377,7 @@ echobase.message.warnExportInProgress=Merci de ne pas fermer la fenêtre pour pouvoir accéder aux résultats de l'export echobase.message.warnImportInProgress=Merci de ne pas fermer la fenêtre pour pouvoir accéder aux résultats de l'import echobase.message.warnRemoveDataInProgress=Merci de ne pas fermer la fenètre pour pouvoir accéder aux résultats de la suppression de l'import +echobase.spatialView.loading=Chargement des données... echobase.title.confirm.deleteImportLogs=Supprimer des données echobase.title.confirm.deleteQuery=Supprimer une requête echobase.title.confirm.deleteWorkingDbConfiguration=Supprimer la configuration Modified: trunk/echobase-ui/src/main/webapp/WEB-INF/decorators.xml =================================================================== --- trunk/echobase-ui/src/main/webapp/WEB-INF/decorators.xml 2014-02-28 14:08:22 UTC (rev 963) +++ trunk/echobase-ui/src/main/webapp/WEB-INF/decorators.xml 2014-02-28 17:45:12 UTC (rev 964) @@ -36,11 +36,9 @@ <pattern>/exportQuery/newLibreOfficeQuery*</pattern> <pattern>/exportQuery/confirmDelete*</pattern> <pattern>/importData/get*</pattern> - <pattern>/spatial/get*</pattern> - <pattern>/spatial/displaySpatial*</pattern> + <pattern>/spatial/showMap*</pattern> <pattern>/workingDb/confirmDelete*</pattern> <pattern>/workingDb/get*</pattern> -  </excludes> <decorator name="layout-default" page="layout-default.jsp"> Modified: trunk/echobase-ui/src/main/webapp/WEB-INF/includes/header.jsp =================================================================== --- trunk/echobase-ui/src/main/webapp/WEB-INF/includes/header.jsp 2014-02-28 14:08:22 UTC (rev 963) +++ trunk/echobase-ui/src/main/webapp/WEB-INF/includes/header.jsp 2014-02-28 17:45:12 UTC (rev 964) @@ -118,7 +118,7 @@ </s:a> </li> <li> - <s:a action="configure" namespace="/spatial" cssClass="spatial"> + <s:a action="show" namespace="/spatial" cssClass="spatial"> <span> <s:text name="echobase.menu.showSpatialData"/> </span> @@ -170,7 +170,7 @@ </s:a> </li> <li> - <s:a action="configure" namespace="/spatial" cssClass="info"> + <s:a action="show" namespace="/spatial" cssClass="info"> <span> <s:text name="echobase.menu.showSpatialData"/> </span> Copied: trunk/echobase-ui/src/main/webapp/WEB-INF/jsp/spatial/show.jsp (from rev 956, trunk/echobase-ui/src/main/webapp/WEB-INF/jsp/spatial/showData.jsp) =================================================================== --- trunk/echobase-ui/src/main/webapp/WEB-INF/jsp/spatial/show.jsp (rev 0) +++ trunk/echobase-ui/src/main/webapp/WEB-INF/jsp/spatial/show.jsp 2014-02-28 17:45:12 UTC (rev 964) @@ -0,0 +1,144 @@ +<%-- + #%L + EchoBase :: UI + $Id$ + $HeadURL$ + %% + Copyright (C) 2011 - 2013 Ifremer, Codelutin + %% + This program is free software: you can redistribute it and/or modify + it under the terms of the GNU Affero General Public License as published by + the Free Software Foundation, either version 3 of the License, or + (at your option) any later version. + + This program is distributed in the hope that it will be useful, + but WITHOUT ANY WARRANTY; without even the implied warranty of + MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the + GNU General Public License for more details. + + You should have received a copy of the GNU Affero General Public License + along with this program. If not, see <http://www.gnu.org/licenses/>. + #L% + --%> +<%@page contentType="text/html" pageEncoding="UTF-8" %> +<%@ taglib prefix="s" uri="/struts-tags" %> +<%@ taglib prefix="sj" uri="/struts-jquery-tags" %> +<title> + <s:text name="echobase.title.show.spatial"/> +</title> + +<script type="text/javascript" + src="<s:url value='/js/gridHelper.js' />"></script> + +<s:if test="model.withSpatial"> + + <script type="text/javascript"> + + jQuery(document).ready(function () { + + var voyageComboBox = $('[name="model.voyageId"]'); + + var loadvoyageData = function(voyageId) { + + var spatialView = $('#spatialViewContainer'); + var spatialViewContent = $('#spatialViewContent'); + if (voyageId!='') { + + // load spatial view + spatialViewContent.html('<s:text name="echobase.spatialView.loading"/>'); + spatialView.show(); + console.info('Load spatial view '+voyageId); + + + jQuery.get( + '<s:url action="showMap" namespace="/spatial"/>', + { + "model.voyageId": voyageComboBox.val() + }, + function (result) { + + // inject result + spatialViewContent.html(result); + }); + } else { + + // hide spatial view + spatialView.hide(); + spatialViewContent.html(""); + } + }; + + voyageComboBox.change(function () { + loadvoyageData(this.value); + }); + + loadvoyageData('<s:property value="model.voyageId"/>'); + + }); + </script> + + <s:form namespace="/spatial" method="POST" enctype="multipart/form-data"> + + <div class="toolbar"> + <ul class="toolbar floatRight"> + <li> + <s:submit action='refreshSpatialViews' key="echobase.action.reloadSpatialData"/> + </li> + </ul> + </div> + + <fieldset> + <legend> + <s:text name="echobase.legend.showData.configure"/> + </legend> + + <div class="cleanBoth help"> + <s:a href="%{getDocumentation('showSpatialData.html', null)}" target="doc"> + <s:text name="echobase.action.show.showSpatialData.documentation"/> + </s:a> + </div> + <br/> + + <s:select key="model.voyageId" requiredLabel="true" + label='%{getText("echobase.common.voyage")}' + list="voyages" headerKey="" headerValue=""/> + + <s:hidden key="model.withSpatial" label=' '/> + + </fieldset> + + + <div id="spatialViewContainer"> + <fieldset> + <legend> + <s:text name="echobase.legend.showData.result"/> + </legend> + + <div id="spatialViewContent"></div> + </fieldset> + </div> + + </s:form> +</s:if> +<s:else> + + <p> + <s:text name="echobase.message.no.spatial.database.support"/> + </p> + + <s:if test="canAddSpatial"> + <br/> + <s:form id="createForm" namespace="/spatial"> + <ul class="toolbar floatLeft"> + + <li> + <s:submit theme="simple" action="addSpatial" + key="echobase.action.workingDbconfiguration.addSpatial"/> + </li> + + </ul> + </s:form> + </s:if> +</s:else> + + Deleted: trunk/echobase-ui/src/main/webapp/WEB-INF/jsp/spatial/showData.jsp =================================================================== --- trunk/echobase-ui/src/main/webapp/WEB-INF/jsp/spatial/showData.jsp 2014-02-28 14:08:22 UTC (rev 963) +++ trunk/echobase-ui/src/main/webapp/WEB-INF/jsp/spatial/showData.jsp 2014-02-28 17:45:12 UTC (rev 964) @@ -1,229 +0,0 @@ -<%-- - #%L - EchoBase :: UI - $Id$ - $HeadURL$ - %% - Copyright (C) 2011 - 2013 Ifremer, Codelutin - %% - This program is free software: you can redistribute it and/or modify - it under the terms of the GNU Affero General Public License as published by - the Free Software Foundation, either version 3 of the License, or - (at your option) any later version. - - This program is distributed in the hope that it will be useful, - but WITHOUT ANY WARRANTY; without even the implied warranty of - MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the - GNU General Public License for more details. - - You should have received a copy of the GNU Affero General Public License - along with this program. If not, see <http://www.gnu.org/licenses/>. - #L% - --%> -<%@page contentType="text/html" pageEncoding="UTF-8" %> -<%@ taglib prefix="s" uri="/struts-tags" %> -<%@ taglib prefix="sj" uri="/struts-jquery-tags" %> -<title> - <s:text name="echobase.title.show.spatial"/> -</title> - -<script type="text/javascript" - src="<s:url value='/js/gridHelper.js' />"></script> - -<s:if test="model.withSpatial"> - - <script type="text/javascript"> - - jQuery(document).ready(function () { - - var getVoyage = $('[name="model.voyageId"]'); - - $.autoSelectVoyageAndDataMetadata( - getVoyage, - $('[name="model.dataMetadataIdForEsduData"]'), - '<s:url action="getVoyageDataMetadata" namespace="/spatial"/>', - '<s:property value="model.voyageId"/>', - '<s:property value="model.dataMetadataIdForEsduData"/>' - ); - - $.autoSelectVoyageAndDataMetadata( - getVoyage, - $('[name="model.dataMetadataIdForEsduResult"]'), - '<s:url action="getVoyageDataMetadata" namespace="/spatial"/>', - '<s:property value="model.voyageId"/>', - '<s:property value="model.dataMetadataIdForEsduResult"/>' - ); - - $.autoSelectVoyageAndDataMetadata( - getVoyage, - $('[name="model.dataMetadataIdForMapData"]'), - '<s:url action="getVoyageDataMetadata" namespace="/spatial"/>', - '<s:property value="model.voyageId"/>', - '<s:property value="model.dataMetadataIdForMapData"/>' - ); - - $.autoSelectVoyageAndDataMetadata( - getVoyage, - $('[name="model.dataMetadataIdForMapResult"]'), - '<s:url action="getVoyageDataMetadata" namespace="/spatial"/>', - '<s:property value="model.voyageId"/>', - '<s:property value="model.dataMetadataIdForMapResult"/>' - ); - - $.showSpatialLayer("model.showCellEsduDataLayer", "cellEsduDataLayer", "model.dataMetadataIdForEsduData", <s:property value="model.showCellEsduDataLayer"/>); - $.showSpatialLayer("model.showCellEsduResultLayer", "cellEsduResultLayer", "model.dataMetadataIdForEsduResult", <s:property value="model.showCellEsduResultLayer"/>); - $.showSpatialLayer("model.showCellMapDataLayer", "cellMapDataLayer", "model.dataMetadataIdForMapData", <s:property value="model.showCellMapDataLayer"/>); - $.showSpatialLayer("model.showCellMapResultLayer", "cellMapResultLayer", "model.dataMetadataIdForMapResult", <s:property value="model.showCellMapResultLayer"/>); - - var showOptions = function(show) { - - var config = $('#withSelectedVoyage'); - - if (show!='') { - config.show(); - } else { - config.hide(); - } - }; - - getVoyage.change(function () { - showOptions(this.value); - }); - - showOptions('<s:property value="model.voyageId"/>'); - - - <s:if test="model.withData"> - - console.info("LOAD DATA!"); - /* - jQuery.get( - '<s:url action="displaySpatial" namespace="/spatial"/>', - { - "model.voyageId": '<s:property value="model.voyageId"/>', - "model.dataMetadataIdForEsduData": '<s:property value="model.dataMetadataIdForEsduData"/>', - "model.dataMetadataIdForEsduResult": '<s:property value="model.dataMetadataIdForEsduResult"/>', - "model.dataMetadataIdForMapData": '<s:property value="model.dataMetadataIdForMapData"/>', - "model.dataMetadataIdForMapResult": '<s:property value="model.dataMetadataIdForMapResult"/>', - "model.showOperationLayer": '<s:property value="model.showOperationLayer"/>', - "model.showCellEsduDataLayer": '<s:property value="model.showCellEsduDataLayer"/>', - "model.showCellEsduResultLayer": '<s:property value="model.showCellEsduResultLayer"/>', - "model.showCellMapDataLayer": '<s:property value="model.showCellMapDataLayer"/>', - "model.showCellMapResultLayer": '<s:property value="model.showCellMapResultLayer"/>' - }, - function (result) { - - // inject result - $('#dataPanel').html(result); - });*/ - </s:if> - }); - </script> - - <s:form namespace="/spatial" method="POST" enctype="multipart/form-data"> - - <fieldset> - <legend> - <s:text name="echobase.legend.showData.configure"/> - </legend> - - <div class="cleanBoth help"> - <s:a href="%{getDocumentation('showData.html', null)}" target="doc"> - <s:text name="echobase.action.show.showData.documentation"/> - </s:a> - </div> - <br/> - - <s:select key="model.voyageId" requiredLabel="true" - label='%{getText("echobase.common.voyage")}' - list="voyages" headerKey="" headerValue=""/> - - <div id="withSelectedVoyage"> - <s:checkbox key="model.showOperationLayer" label='%{getText("echobase.common.showOperationLayer")}'/> - - <s:checkbox key="model.showCellEsduDataLayer" label='%{getText("echobase.common.showCellEsduDataLayer")}'/> - <div class="cellEsduDataLayer"> - <sj:select key="model.dataMetadataIdForEsduData" requiredLabel="true" - label='%{getText("echobase.common.dataMetadata.forEsduDataLayer")}' - headerKey="" headerValue=""/> - </div> - - <s:checkbox key="model.showCellEsduResultLayer" label='%{getText("echobase.common.showCellEsduResultLayer")}'/> - <div class="cellEsduResultLayer"> - <sj:select key="model.dataMetadataIdForEsduResult" requiredLabel="true" - label='%{getText("echobase.common.dataMetadata.forEsduResultLayer")}' - headerKey="" headerValue=""/> - </div> - - <s:checkbox key="model.showCellMapDataLayer" label='%{getText("echobase.common.showCellMapDataLayer")}'/> - <div class="cellMapDataLayer"> - <sj:select key="model.dataMetadataIdForMapData" requiredLabel="true" - label='%{getText("echobase.common.dataMetadata.forMapDataLayer")}' - headerKey="" headerValue=""/> - </div> - <s:checkbox key="model.showCellMapResultLayer" label='%{getText("echobase.common.showCellMapResultLayer")}'/> - <div class="cellMapResultLayer"> - <sj:select key="model.dataMetadataIdForMapResult" requiredLabel="true" - label='%{getText("echobase.common.dataMetadata.forMapResultLayer")}' - headerKey="" headerValue=""/> - </div> - - </div> - - <s:hidden key="model.withData" label=' '/> - <s:hidden key="model.withSpatial" label=' '/> - - </fieldset> - <br/> - - <div class="toolbar"> - <ul class="toolbar floatRight"> - <li> - <s:submit action='reconfigure' key="echobase.action.reset"/> - </li> - <li> - <s:submit action='save' key="echobase.action.save"/> - </li> - <li> - <s:submit action="showSpatial" key='echobase.action.display'/> - </li> - </ul> - </div> - - <s:if test="model.withData"> - - <fieldset> - <legend> - <s:text name="echobase.legend.showData.result"/> - </legend> - - <div id="dataPanel"> - <center>Loading...</center> - </div> - </fieldset> - </s:if> - - </s:form> -</s:if> -<s:else> - - <p> - <s:text name="echobase.message.no.spatial.database.support"/> - </p> - - <s:if test="canAddSpatial"> - <br/> - <s:form id="createForm" namespace="/spatial"> - <ul class="toolbar floatLeft"> - - <li> - <s:submit theme="simple" action="addSpatial" - key="echobase.action.workingDbconfiguration.addSpatial"/> - </li> - - </ul> - </s:form> - </s:if> -</s:else> - - Deleted: trunk/echobase-ui/src/main/webapp/WEB-INF/jsp/spatial/showDataMap.jsp =================================================================== --- trunk/echobase-ui/src/main/webapp/WEB-INF/jsp/spatial/showDataMap.jsp 2014-02-28 14:08:22 UTC (rev 963) +++ trunk/echobase-ui/src/main/webapp/WEB-INF/jsp/spatial/showDataMap.jsp 2014-02-28 17:45:12 UTC (rev 964) @@ -1,80 +0,0 @@ -<%-- - #%L - EchoBase :: UI - $Id$ - $HeadURL$ - %% - Copyright (C) 2011 - 2013 Ifremer, Codelutin - %% - This program is free software: you can redistribute it and/or modify - it under the terms of the GNU Affero General Public License as published by - the Free Software Foundation, either version 3 of the License, or - (at your option) any later version. - - This program is distributed in the hope that it will be useful, - but WITHOUT ANY WARRANTY; without even the implied warranty of - MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the - GNU General Public License for more details. - - You should have received a copy of the GNU Affero General Public License - along with this program. If not, see <http://www.gnu.org/licenses/>. - #L% - --%> -<%@page contentType="text/html" pageEncoding="UTF-8" %> -<%@ taglib prefix="s" uri="/struts-tags" %> -<%@ taglib prefix="sj" uri="/struts-jquery-tags" %> - -<link rel="stylesheet" href="http://cdn.leafletjs.com/leaflet-0.6.4/leaflet.css" /> - - -<sj:head jqueryui="true" jquerytheme="echobase-theme" debug="true" compressed="false" /> - -<%--<script type="text/javascript"--%> - <%--src="http://cdn.leafletjs.com/leaflet-0.6.4/leaflet.js"></script>--%> -<script type="text/javascript" - src="<s:url value='/js/leaflet-src.js' />"></script> - -<%--<script type="text/javascript"--%> - <%--src="<s:url value='/js/gridHelper.js' />"></script>--%> - -<script type="text/javascript"> - - jQuery(document).ready(function () { - - console.info("LL2= "+L); - - jQuery.getJSON( - '<s:url action="getSpatialData" namespace="/spatial"/>', - { - "model.voyageId": '<s:property value="model.voyageId"/>', - "model.dataMetadataId": '<s:property value="model.dataMetadataId"/>', - "model.speciesId": '<s:property value="model.speciesId"/>', - "model.echotypeId": '<s:property value="model.echotypeId"/>' - }, - function (result) { - - console.info("LL3= "+L); - - // create a map in the "map" div, set the view to a given place and zoom - var map = L.map('map').setView([51.505, -0.09], 13); - - // add an OpenStreetMap tile layer - L.tileLayer('http://{s}.tile.osm.org/{z}/{x}/{y}.png', { - attribution: '© <a href="http://osm.org/copyright">OpenStreetMap</a> contributors' - }).addTo(map); - - // get geoJson object - var geoJson = result.data; - - // add it to map - L.geoJson(geoJson).addTo(map); - }); - - }); -</script> -<div id="map"> -Loading... -</div> - Copied: trunk/echobase-ui/src/main/webapp/WEB-INF/jsp/spatial/showMap.jsp (from rev 956, trunk/echobase-ui/src/main/webapp/WEB-INF/jsp/spatial/showDataMap.jsp) =================================================================== --- trunk/echobase-ui/src/main/webapp/WEB-INF/jsp/spatial/showMap.jsp (rev 0) +++ trunk/echobase-ui/src/main/webapp/WEB-INF/jsp/spatial/showMap.jsp 2014-02-28 17:45:12 UTC (rev 964) @@ -0,0 +1,41 @@ +<%-- + #%L + EchoBase :: UI + $Id$ + $HeadURL$ + %% + Copyright (C) 2011 - 2013 Ifremer, Codelutin + %% + This program is free software: you can redistribute it and/or modify + it under the terms of the GNU Affero General Public License as published by + the Free Software Foundation, either version 3 of the License, or + (at your option) any later version. + + This program is distributed in the hope that it will be useful, + but WITHOUT ANY WARRANTY; without even the implied warranty of + MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the + GNU General Public License for more details. + + You should have received a copy of the GNU Affero General Public License + along with this program. If not, see <http://www.gnu.org/licenses/>. + #L% + --%> +<%@page contentType="text/html" pageEncoding="UTF-8" %> +<%@ taglib prefix="s" uri="/struts-tags" %> +<%@ taglib prefix="sj" uri="/struts-jquery-tags" %> + +<sj:head jqueryui="true" jquerytheme="echobase-theme" debug="true" compressed="false" /> + +<script type="text/javascript" + src="<s:url value='/js/leaflet-src.js' />"></script> + +<script type="text/javascript"> + + jQuery(document).ready(function () { + + }); +</script> +<div id="map"> +Loading... +</div> + Modified: trunk/echobase-ui/src/main/webapp/js/gridHelper.js =================================================================== --- trunk/echobase-ui/src/main/webapp/js/gridHelper.js 2014-02-28 14:08:22 UTC (rev 963) +++ trunk/echobase-ui/src/main/webapp/js/gridHelper.js 2014-02-28 17:45:12 UTC (rev 964) @@ -240,90 +240,8 @@ } ); } - }, - - // auto-selection des voyages - dataMetadata - autoSelectVoyageAndDataMetadata: function (voyageSelectBox, datapMetadataSelectBox, getUrl, voyageId, dataMetadataId) { - - voyageSelectBox.change({datapMetadataSelectBox: datapMetadataSelectBox, - getUrl: getUrl}, function (event) { - - $.updateSelectBoxContent(event.data.getUrl, - {voyageId: this.value}, - 'data', - event.data.datapMetadataSelectBox - ); - }); - - if (voyageId) { - - voyageSelectBox.change(voyageId); - - $.updateSelectBoxContent(getUrl, - {voyageId: voyageId}, - 'data', - datapMetadataSelectBox, function () { - - if (dataMetadataId) { - - datapMetadataSelectBox.val(dataMetadataId); - } - } - ); - } - }, - - // auto-selection des voyages - species - autoSelectVoyageAndSpecies: function (voyageSelectBox, speciesSelectBox, getUrl, voyageId, speciesId) { - - voyageSelectBox.change({speciesSelectBox: speciesSelectBox, - getUrl: getUrl}, function (event) { - - $.updateSelectBoxContent(event.data.getUrl, - {voyageId: this.value}, - 'data', - event.data.speciesSelectBox - ); - }); - - if (voyageId) { - - voyageSelectBox.change(voyageId); - - $.updateSelectBoxContent(getUrl, - {voyageId: voyageId}, - 'data', - speciesSelectBox, function () { - - if (speciesId) { - - speciesSelectBox.val(speciesId); - } - } - ); - } - }, - - // affichage ou non des options d'un layer spatial - showSpatialLayer: function (checkboxId, configCssClass, selectBoxId, defaultValue) { - - var f = function(show) { - var config = $('[class~="' + configCssClass + '"]'); - var selectBox = $('[name="' + selectBoxId + '"]') - if (show) { - selectBox.removeAttr('disabled'); - config.show(); - } else { - selectBox.attr('disabled', true); - config.hide(); - } - }; - $('[name="' + checkboxId + '"]').change(function () { - f($(this).is(':checked')); - }); - f(!!defaultValue); } - }); + }); $.extend({ addRowSelectTopic: function (gridId, callback) { @@ -366,15 +284,6 @@ }, autoSelectVoyageAndDataProcessing: function (voyageSelectBox, dataProcessingSelectBox, getUrl, voyageId, dataProcessingId) { return $(document).autoSelectVoyageAndDataProcessing(voyageSelectBox, dataProcessingSelectBox, getUrl, voyageId, dataProcessingId); - }, - autoSelectVoyageAndDataMetadata: function (voyageSelectBox, dataMetadataSelectBox, getUrl, voyageId, dataMetadataId) { - return $(document).autoSelectVoyageAndDataMetadata(voyageSelectBox, dataMetadataSelectBox, getUrl, voyageId, dataMetadataId); - }, - autoSelectVoyageAndSpecies: function (voyageSelectBox, speciesSelectBox, getUrl, voyageId, speciesId) { - return $(document).autoSelectVoyageAndSpecies(voyageSelectBox, speciesSelectBox, getUrl, voyageId, speciesId); - }, - showSpatialLayer: function (checkboxId, configCssClass, selectBoxId, defaultValue) { - return $(document).showSpatialLayer(checkboxId, configCssClass, selectBoxId, defaultValue); } }); })(jQuery); Modified: trunk/pom.xml =================================================================== --- trunk/pom.xml 2014-02-28 14:08:22 UTC (rev 963) +++ trunk/pom.xml 2014-02-28 17:45:12 UTC (rev 964) @@ -141,7 +141,7 @@ <topiaVersion>3.0-alpha-11</topiaVersion> <nuitonUtilsVersion>3.0-rc-2</nuitonUtilsVersion> <nuitonI18nVersion>3.0</nuitonI18nVersion> - <nuitonWebVersion>1.15-alpha-3</nuitonWebVersion> + <nuitonWebVersion>1.15-SNAPSHOT</nuitonWebVersion> <struts2Version>2.3.15.3</struts2Version> <jqueryPluginVersion>3.6.1</jqueryPluginVersion> <h2Version>1.3.174</h2Version>

1 0

r963 - in trunk: echobase-domain/src/main/resources echobase-domain/src/main/resources/migration echobase-services/src/main/resources
by tchemit＠users.forge.codelutin.com 28 Feb '14

28 Feb '14

Author: tchemit Date: 2014-02-28 15:08:22 +0100 (Fri, 28 Feb 2014) New Revision: 963 Url: http://codelutin.com/projects/echobase/repository/revisions/963 Log: refs #4194 (finalize spatial views) Added: trunk/echobase-domain/src/main/resources/postgis-structure.sql trunk/echobase-domain/src/main/resources/postgis-view.sql Removed: trunk/echobase-services/src/main/resources/postgis-structure.sql trunk/echobase-services/src/main/resources/postgis-view.sql Modified: trunk/echobase-domain/src/main/resources/migration/postgis-view-2.6.sql Modified: trunk/echobase-domain/src/main/resources/migration/postgis-view-2.6.sql =================================================================== --- trunk/echobase-domain/src/main/resources/migration/postgis-view-2.6.sql 2014-02-27 20:41:19 UTC (rev 962) +++ trunk/echobase-domain/src/main/resources/migration/postgis-view-2.6.sql 2014-02-28 14:08:22 UTC (rev 963) @@ -21,7 +21,7 @@ -- #L% --- ----------------------------------------------------------------------------------------------------------------------- ----- DROP EXISTING VIEW - INDEX - FUNCTION - TRIGGER ------------------------------------------------------------------ +---- DROP EXISTING VIEW - INDEX - FUNCTION ---------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------- DROP VIEW IF EXISTS echobase_cell_spatial_result CASCADE; @@ -68,7 +68,7 @@ DROP FUNCTION IF EXISTS echobase_refresh_views() CASCADE; ----------------------------------------------------------------------------------------------------------------------- ----- CREATE FUNCTION - TRIGGER ---------------------------------------------------------------------------------------- +---- CREATE FUNCTION -------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------- CREATE OR REPLACE FUNCTION echobase_to_numeric(string VARCHAR) @@ -82,24 +82,8 @@ $$ LANGUAGE plpgsql; CREATE OR REPLACE FUNCTION echobase_refresh_views() - RETURNS TRIGGER AS $$ -DECLARE - operationtype VARCHAR; - result RECORD; + RETURNS VOID AS $$ BEGIN - - IF (TG_OP = 'DELETE') - THEN - result = OLD; - ELSE - result = NEW; - END IF; - - operationtype = result.entitytype; - - IF (operationtype ILIKE '%Import%' OR operationtype ILIKE '%Removed%') - THEN - -- Can update views RAISE NOTICE 'reload cellEsduViewEchotype'; REFRESH MATERIALIZED VIEW cellEsduViewEchotype; REINDEX INDEX cellEsduViewEchotype_idx; @@ -131,17 +115,10 @@ REFRESH MATERIALIZED VIEW cellmapview; REINDEX INDEX cellmapview_idx; REINDEX INDEX cellmapview_uidx; - END IF; - - RETURN result; END $$ LANGUAGE 'plpgsql'; -CREATE TRIGGER echobase_refresh_views_trigger -AFTER INSERT ON entitymodificationlog -FOR EACH ROW EXECUTE PROCEDURE echobase_refresh_views(); - ----------------------------------------------------------------------------------------------------------------------- ---- CELL RESULT VIEW ------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------- @@ -150,13 +127,12 @@ SELECT c.voyagename, c.coordinate, - c.shape, c.cellname, d.name, echobase_to_numeric(r.resultvalue) as resultvalue, e.name as echotypeName, e.meaning as echotypeMeaning, - r.topiaid AS resultid + r.topiaid AS id FROM echobase_cell_spatial c, result r, @@ -168,6 +144,7 @@ AND r.datametadata = d.topiaid AND r.category = cat.topiaid AND cat.echotype IS NOT NULL + AND cat.speciescategory IS NULL AND cat.echotype = e.topiaid AND c.celltypename = 'Elementary Distance Sampling Unit'; @@ -181,18 +158,17 @@ WHERE v.name = 'NASC'; -CREATE UNIQUE INDEX cellEsduViewEchotypeNasc_uidx ON cellEsduViewEchotypeNasc(resultid); +CREATE UNIQUE INDEX cellEsduViewEchotypeNasc_uidx ON cellEsduViewEchotypeNasc(id); CREATE MATERIALIZED VIEW cellEsduViewSpecies AS SELECT c.voyagename, c.coordinate, - c.shape, c.cellname, d.name, echobase_to_numeric(r.resultvalue) as resultvalue, s.baracoudacode, - r.topiaid AS resultid + r.topiaid AS id FROM echobase_cell_spatial c, result r, @@ -231,7 +207,11 @@ SELECT distinct v2.coordinate, v2.voyagename, - v.* + v.nbResults, + v.cellname, + v.baracoudacode, + v.resultvalue, + v.id FROM cellEsduViewSpeciesResultGrouped v JOIN cellEsduViewSpecies v2 USING (name, baracoudacode, cellname) @@ -244,7 +224,11 @@ SELECT distinct v2.coordinate, v2.voyagename, - v.* + v.nbResults, + v.cellname, + v.baracoudacode, + v.resultvalue, + v.id FROM cellEsduViewSpeciesResultGrouped v JOIN cellEsduViewSpecies v2 USING (name, baracoudacode, cellname) @@ -257,7 +241,11 @@ SELECT distinct v2.coordinate, v2.voyagename, - v.* + v.nbResults, + v.cellname, + v.baracoudacode, + v.resultvalue / v.nbResults as resultvalue, + v.id FROM cellEsduViewSpeciesResultGrouped v JOIN cellEsduViewSpecies v2 USING (name, baracoudacode, cellname) @@ -267,38 +255,18 @@ CREATE UNIQUE INDEX cellEsduViewSpeciesMeanLength_uidx ON cellEsduViewSpeciesMeanLength(id); ----------------------------------------------------------------------------------------------------------------------- ----- CELL DATA VIEW --------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------ - -CREATE OR REPLACE VIEW echobase_cell_spatial_data AS - SELECT - s.*, - m.topiaId AS metaDataId, - m.name AS metaDataName, - d.datavalue AS dataValue, - d.topiaid AS dataId - FROM - echobase_cell_spatial s, - data d, - datametadata m - WHERE - s.cellid = d.cell AND - d.datametadata = m.topiaid; - ------------------------------------------------------------------------------------------------------------------------ ---- MAP CELL VIEW ---------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------- CREATE MATERIALIZED VIEW cellmapview AS SELECT c.voyagename, - c.coordinate, c.shape, c.cellname, d.name, s.baracoudacode, echobase_to_numeric(r.resultvalue) as resultvalue, - r.topiaid AS resultid + r.topiaid AS id FROM echobase_cell_spatial c, result r, @@ -315,7 +283,7 @@ AND c.celltypename = 'Map cell'; CREATE INDEX cellmapview_idx ON cellmapview(baracoudacode, name); -CREATE UNIQUE INDEX cellmapview_uidx ON cellmapview(resultid); +CREATE UNIQUE INDEX cellmapview_uidx ON cellmapview(id); CREATE OR REPLACE VIEW meanMapcellBiomassEngrEnc AS SELECT @@ -470,5 +438,5 @@ (SELECT SUM(t.sampleweight) FROM totalsamplemicrpou t WHERE t.operation_id = e.operationname) AS TotalCatchMICRPOU, (SELECT SUM(t.sampleweight) FROM totalsamplescomsco t WHERE t.operation_id = e.operationname) AS TotalCatchSCOMSCO, (SELECT SUM(t.sampleweight) FROM totalsampletractru t WHERE t.operation_id = e.operationname) AS TotalCatchTRACTRU, - e.operationid + e.operationid as id FROM echobase_operation_spatial e; \ No newline at end of file Copied: trunk/echobase-domain/src/main/resources/postgis-structure.sql (from rev 956, trunk/echobase-services/src/main/resources/postgis-structure.sql) =================================================================== --- trunk/echobase-domain/src/main/resources/postgis-structure.sql (rev 0) +++ trunk/echobase-domain/src/main/resources/postgis-structure.sql 2014-02-28 14:08:22 UTC (rev 963) @@ -0,0 +1,1219 @@ +--- +-- #%L +-- EchoBase :: Domain +-- $Id$ +-- $HeadURL$ +-- %% +-- Copyright (C) 2011 - 2014 Ifremer, Codelutin +-- %% +-- This program is free software: you can redistribute it and/or modify +-- it under the terms of the GNU Affero General Public License as published by +-- the Free Software Foundation, either version 3 of the License, or +-- (at your option) any later version. +-- +-- This program is distributed in the hope that it will be useful, +-- but WITHOUT ANY WARRANTY; without even the implied warranty of +-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the +-- GNU General Public License for more details. +-- +-- You should have received a copy of the GNU Affero General Public License +-- along with this program. If not, see <http://www.gnu.org/licenses/>. +-- #L% +--- + +CREATE EXTENSION IF NOT EXISTS postgis; + +DROP VIEW IF EXISTS TotalCatchSpOpWide CASCADE; +DROP VIEW IF EXISTS echobase_cell_spatial_data CASCADE; +DROP VIEW IF EXISTS echobase_cell_spatial_result CASCADE; +DROP VIEW IF EXISTS TotalSampleEngrEnc CASCADE; +DROP VIEW IF EXISTS TotalSampleSardPil CASCADE; +DROP VIEW IF EXISTS TotalSampleTracTru CASCADE; +DROP VIEW IF EXISTS TotalSampleSpraSpr CASCADE; +DROP VIEW IF EXISTS TotalSampleScomSco CASCADE; +DROP VIEW IF EXISTS TotalSampleMicrPou CASCADE; +DROP VIEW IF EXISTS cellmapview CASCADE; + +DROP INDEX IF EXISTS echobase_cell_spatial_coordinate_gix CASCADE; +DROP INDEX IF EXISTS echobase_cell_spatial_coordinate3D_gix CASCADE; +DROP INDEX IF EXISTS echobase_cell_spatial_shape_gix CASCADE; +DROP INDEX IF EXISTS echobase_cell_spatial_voyageid_idx CASCADE; +DROP TABLE IF EXISTS echobase_cell_spatial CASCADE; +DROP TABLE IF EXISTS echobase_cell_spatial_temp CASCADE; + +DROP TRIGGER IF EXISTS echobase_fill_cell_spatial_work_table_trigger ON data CASCADE; +DROP FUNCTION IF EXISTS echobase_fill_spatial_temp_table() CASCADE; +DROP FUNCTION IF EXISTS echobase_fill_cell_spatial_temp_table() CASCADE; +DROP TRIGGER IF EXISTS echobase_delete_cell ON cell CASCADE; +DROP FUNCTION IF EXISTS echobase_delete_cell() CASCADE; +DROP FUNCTION IF EXISTS echobase_compute_all_cell_spatial_data() CASCADE; +DROP FUNCTION IF EXISTS echobase_fill_cell_spatial_table() CASCADE; +DROP FUNCTION IF EXISTS echobase_fill_cell_spatial_row(cell_id VARCHAR) CASCADE; +DROP FUNCTION IF EXISTS echobase_fill_esdu_cell_spatial_table(cell_id VARCHAR) CASCADE; +DROP FUNCTION IF EXISTS echobase_fill_elementary_cell_spatial_table(cell_id VARCHAR) CASCADE; +DROP FUNCTION IF EXISTS echobase_fill_region_cell_spatial_table(cell_id VARCHAR) CASCADE; +DROP FUNCTION IF EXISTS echobase_fill_map_cell_spatial_table(cell_id VARCHAR) CASCADE; +DROP FUNCTION IF EXISTS echobase_create_echobase_cell_spatial_row( +cell_id VARCHAR, +coordinateText VARCHAR, +coordinate3dText VARCHAR, +shapeText VARCHAR) CASCADE; +DROP FUNCTION IF EXISTS echobase_get_cell_type(cell_id VARCHAR) CASCADE; + + +DROP INDEX IF EXISTS echobase_operation_spatial_coordinate_gix; +DROP INDEX IF EXISTS echobase_operation_spatial_voyageid_idx; +DROP TABLE IF EXISTS echobase_operation_spatial CASCADE ; +DROP TABLE IF EXISTS echobase_operation_spatial_temp CASCADE; +DROP TRIGGER IF EXISTS echobase_fill_operation_spatial_work_table_trigger ON operation CASCADE; +DROP FUNCTION IF EXISTS echobase_fill_operation_spatial_temp_table() CASCADE; +DROP TRIGGER IF EXISTS echobase_delete_operation ON operation CASCADE; +DROP FUNCTION IF EXISTS echobase_delete_operation() CASCADE; +DROP FUNCTION IF EXISTS echobase_compute_all_operation_spatial_data() CASCADE; +DROP FUNCTION IF EXISTS echobase_fill_operation_spatial_table() CASCADE; +DROP FUNCTION IF EXISTS echobase_fill_operation_spatial_row(operation_id VARCHAR) CASCADE; +DROP FUNCTION IF EXISTS echobase_create_echobase_operation_spatial_row( +operation_id VARCHAR, +coordinateText VARCHAR) CASCADE; +DROP FUNCTION IF EXISTS echobase_latitudetext_to_dd(latitude VARCHAR) CASCADE; +DROP FUNCTION IF EXISTS echobase_longitudetext_to_dd(longitude VARCHAR) CASCADE; +DROP FUNCTION IF EXISTS echobase_dms2dd(D INTEGER, M INTEGER, S INTEGER, HEMI CHARACTER VARYING(1)) CASCADE; +DROP FUNCTION IF EXISTS echobase_compute_all_spatial_data() CASCADE; + +-------------------------------------------------------------------------------- +-- Table spatial pour les cellules --------------------------------------------- +-------------------------------------------------------------------------------- + +CREATE TABLE echobase_cell_spatial ( + voyageid VARCHAR(256) NOT NULL, + voyagename VARCHAR(256), + transitid VARCHAR(256), + transitname VARCHAR(256), + transectid VARCHAR(256), + transectname VARCHAR(256), + dataAcquisitionid VARCHAR(256), + dataAcquisitionname VARCHAR(256), + dataProcessingid VARCHAR(256), + dataProcessingname VARCHAR(256), + celltypeid VARCHAR(256) NOT NULL, + celltypename VARCHAR(256) NOT NULL, + cellid VARCHAR(256) PRIMARY KEY, + cellname VARCHAR(256) NOT NULL, + lastUpdateDate TIMESTAMP NOT NULL, + FOREIGN KEY (cellid) REFERENCES cell (topiaid), + FOREIGN KEY (celltypeid) REFERENCES celltype (topiaid), + FOREIGN KEY (dataacquisitionid) REFERENCES dataacquisition (topiaid), + FOREIGN KEY (dataprocessingid) REFERENCES dataprocessing (topiaid), + FOREIGN KEY (transectid) REFERENCES transect (topiaid), + FOREIGN KEY (transitid) REFERENCES transit (topiaid), + FOREIGN KEY (voyageid) REFERENCES voyage (topiaid) +); + +SELECT AddGeometryColumn('echobase_cell_spatial', 'coordinate', 4326, 'POINT',2 ); +SELECT AddGeometryColumn('echobase_cell_spatial', 'coordinate3d', 4326, 'POINT',3); +SELECT AddGeometryColumn('echobase_cell_spatial', 'shape', 4326, 'POLYGON',2 ); + +CREATE INDEX echobase_cell_spatial_coordinate_gix ON echobase_cell_spatial USING GIST (coordinate); +CREATE INDEX echobase_cell_spatial_coordinate3D_gix ON echobase_cell_spatial USING GIST (coordinate3d); +CREATE INDEX echobase_cell_spatial_shape_gix ON echobase_cell_spatial USING GIST (shape); +CREATE INDEX echobase_cell_spatial_voyageid_idx ON echobase_cell_spatial USING BTREE(voyageid); + +-- pour stoquer les traitements spatiaux a effectuer + +CREATE TABLE echobase_cell_spatial_temp ( + cellid VARCHAR(256) PRIMARY KEY, + celltype VARCHAR(256) NOT NULL, + FOREIGN KEY (cellid) REFERENCES cell (topiaid) +); + +-- +-- Trigger qui met à jour la table des traitements spatiaux à faire +-- + +CREATE OR REPLACE FUNCTION echobase_fill_cell_spatial_temp_table() + RETURNS TRIGGER AS $$ +DECLARE + cell_id VARCHAR; + data_type_id VARCHAR; + cellType VARCHAR; + dataType VARCHAR; + doInsert BOOLEAN; + result RECORD; +BEGIN + + IF (TG_OP = 'DELETE') + THEN + result = OLD; + ELSE + result = NEW; + END IF; + + cell_id = result.cell; + data_type_id = result.datametadata; + doInsert = FALSE; +-- recuperation du type de la cellule + cellType = echobase_get_cell_type(cell_id); + + IF (SELECT + count(*) + FROM echobase_cell_spatial_temp c + WHERE c.cellid = cell_id) > 0 + THEN +-- la cellule est deja a traitee + RETURN result; + END IF; +-- recuperation du type de la la data + SELECT + dt.name + INTO dataType + FROM datametadata dt + WHERE dt.topiaId = data_type_id; + + CASE cellType + WHEN 'ESDU' + THEN +-- Cell of type Esdu + CASE dataType + WHEN 'LatitudeStart', 'LatitudeBary', 'LatitudeEnd', 'LongitudeStart', 'LongitudeBary', 'LongitudeEnd' + THEN + doInsert = TRUE; + ELSE + doInsert = FALSE; + END CASE; + WHEN 'ELEMENTARY' + THEN +-- Cell of type Elementary + CASE dataType + WHEN 'LatitudeStart', 'LatitudeBary', 'LatitudeEnd', 'LongitudeStart', 'LongitudeBary', 'LongitudeEnd', 'DepthRefSurfaceStart', 'DepthRefSurfaceEnd', 'DepthRefBottomStart', 'DepthRefBottomEnd' + THEN + doInsert = TRUE; + ELSE + doInsert = FALSE; + END CASE; + WHEN 'REGION', 'REGIONSURF', 'REGIONCLAS' + THEN +-- Cell of type Region + CASE dataType + WHEN 'RegionEnvCoordinates' + THEN + doInsert = TRUE; + cellType = 'REGION'; + ELSE + doInsert = FALSE; + END CASE; + WHEN 'MAP' + THEN +-- Cell of type Map + CASE dataType + WHEN 'GridCellLatitude', 'GridCellLongitude', 'GridLatitudeLag', 'GridLongitudeLag' + THEN + doInsert = TRUE; + ELSE + doInsert = FALSE; + END CASE; + END CASE; + + IF doInsert = TRUE + THEN +--- Ajout de la cellule dans la table des traitements à effectuer + RAISE DEBUG 'Add cell % [type %] to echobase_cell_spatial_temp', cell_id, cellType; + INSERT INTO echobase_cell_spatial_temp (cellid, celltype) VALUES (cell_id, cellType); + END IF; + + RETURN result; +END +$$ +LANGUAGE 'plpgsql'; + +CREATE TRIGGER echobase_fill_cell_spatial_work_table_trigger +AFTER INSERT OR UPDATE ON data +FOR EACH ROW WHEN (NEW.cell IS NOT + NULL) EXECUTE PROCEDURE echobase_fill_cell_spatial_temp_table(); + +-- +-- Trigger qui met à jour la table des traitements spatiaux à faire lors d +-- 'une suppression de cellule +-- + +CREATE OR REPLACE FUNCTION echobase_delete_cell() + RETURNS TRIGGER AS $$ +BEGIN + + RAISE DEBUG 'Delete cell % , delete cascade in echobase_cell_spatial_table', OLD.topiaid; + + DELETE FROM echobase_cell_spatial WHERE cellid = OLD.topiaid; + + RETURN OLD; +END +$$ +LANGUAGE 'plpgsql'; + +CREATE TRIGGER echobase_delete_cell +BEFORE DELETE ON cell +FOR EACH ROW EXECUTE PROCEDURE echobase_delete_cell(); + +-- +-- Mettre a jour toutes les données spatiales de cellule +-- + +CREATE OR REPLACE FUNCTION echobase_compute_all_cell_spatial_data() + RETURNS VOID AS $$ +DECLARE cell_id VARCHAR; +BEGIN + DELETE FROM echobase_cell_spatial; + + FOR cell_id IN SELECT topiaid FROM cell LOOP + PERFORM echobase_fill_cell_spatial_row(cell_id); + END LOOP; +END +$$ +LANGUAGE plpgsql; + +-- +-- Mettre a jour la table echobase_cell_spatial depuis echobase_cell_spatial_temp +-- + +CREATE OR REPLACE FUNCTION echobase_fill_cell_spatial_table() + RETURNS VOID AS $$ +DECLARE + cellRow RECORD; +BEGIN + FOR cellRow IN SELECT + * + FROM echobase_cell_spatial_temp LOOP + + PERFORM echobase_fill_cell_spatial_row(cellRow.cellid); + + END LOOP; + + DELETE FROM echobase_cell_spatial_temp; + +END +$$ +LANGUAGE plpgsql; + +CREATE OR REPLACE FUNCTION echobase_fill_cell_spatial_row(cell_id VARCHAR) + RETURNS VOID AS $$ + DECLARE cell_type VARCHAR; +BEGIN + + cell_type = echobase_get_cell_type(cell_id); + + RAISE DEBUG 'Treat spatial cell % [type %] ...', cell_id, cell_type; + CASE cell_type + WHEN 'ESDU' + THEN + EXECUTE echobase_fill_esdu_cell_spatial_table(cell_id); + WHEN 'ELEMENTARY' + THEN + EXECUTE echobase_fill_elementary_cell_spatial_table(cell_id); + WHEN 'REGION' + THEN + EXECUTE echobase_fill_region_cell_spatial_table(cell_id); + WHEN 'MAP' + THEN + EXECUTE echobase_fill_map_cell_spatial_table(cell_id); + ELSE + RAISE LOG 'Can not deal with celltype % (cell %)', cell_type, cell_id; + END CASE; +END +$$ +LANGUAGE plpgsql; + +CREATE OR REPLACE FUNCTION echobase_fill_esdu_cell_spatial_table( + cell_id VARCHAR) + RETURNS VOID AS $$ +DECLARE + latitude VARCHAR; + longitude VARCHAR; + depth RECORD; + spatialText VARCHAR; + latitudeNumber REAL; + longitudeNumber REAL; +BEGIN + RAISE DEBUG 'Treat esdu cell % ', cell_id; +-- test if start / bary / end event + SELECT + d.datavalue + INTO latitude + FROM data d LEFT OUTER JOIN datametadata AS dm + ON d.datametadata = dm.topiaid + WHERE d.cell = cell_id AND dm.name = 'LatitudeStart'; + IF FOUND + THEN +-- start data + RAISE DEBUG 'Treat esdu Start cell % ', cell_id; + SELECT + d.datavalue + INTO longitude + FROM data d LEFT OUTER JOIN datametadata AS dm + ON d.datametadata = dm.topiaid + WHERE d.cell = cell_id AND dm.name = 'LongitudeStart'; + ELSE + SELECT + d.datavalue + INTO latitude + FROM data d LEFT OUTER JOIN datametadata AS dm + ON d.datametadata = dm.topiaid + WHERE d.cell = cell_id AND dm.name = 'LatitudeBary'; + IF FOUND + THEN +-- bary data + RAISE DEBUG 'Treat esdu Bary cell %', cell_id; + SELECT + d.datavalue + INTO longitude + FROM data d LEFT OUTER JOIN datametadata AS dm + ON d.datametadata = dm.topiaid + WHERE d.cell = cell_id AND dm.name = 'LongitudeBary'; + ELSE + SELECT + d.datavalue + INTO latitude + FROM data d LEFT OUTER JOIN datametadata AS dm + ON d.datametadata = dm.topiaid + WHERE d.cell = cell_id AND dm.name = 'LatitudeEnd'; + IF FOUND + THEN +-- end data + RAISE DEBUG 'Treat End cell %', cell_id; + SELECT + d.datavalue + INTO longitude + FROM data d LEFT OUTER JOIN datametadata AS dm + ON d.datametadata = dm.topiaid + WHERE d.cell = cell_id AND dm.name = 'LongitudeEnd'; + ELSE +-- no spatial data + RAISE LOG 'Could not find spatial data for cell %', cell_id; + RETURN; + END IF; + END IF; + END IF; + +-- convert dms latitude to dd latitude + SELECT + echobase_latitudetext_to_dd(latitude) + INTO latitudeNumber; + + IF latitude IS NOT NULL AND latitudeNumber IS NULL + THEN + latitudeNumber := latitude :: REAL; + END IF; + +-- convert dms longitude to dd longitude + SELECT + echobase_longitudetext_to_dd(longitude) + INTO longitudeNumber; + + IF longitude IS NOT NULL AND longitudeNumber IS NULL + THEN + longitudeNumber := longitude :: REAL; + END IF; + + SELECT + 'SRID=4326;POINT(' || longitudeNumber || ' ' || latitudeNumber || ')' + INTO spatialText; + RAISE DEBUG 'spatial data % for cell %', spatialText, cell_id; + + PERFORM echobase_create_echobase_cell_spatial_row(cell_id, + spatialText, + NULL, + NULL); +END +$$ LANGUAGE plpgsql; + +CREATE OR REPLACE FUNCTION echobase_fill_elementary_cell_spatial_table( + cell_id VARCHAR) + RETURNS VOID AS $$ +DECLARE + latitude VARCHAR; + longitude VARCHAR; + depth VARCHAR; + spatialText VARCHAR; + latitudeNumber REAL; + longitudeNumber REAL; +BEGIN + RAISE DEBUG 'Treat elementary cell % ', cell_id; +-- try start elementary + SELECT + d.datavalue + INTO latitude + FROM data d LEFT OUTER JOIN datametadata AS dm + ON d.datametadata = dm.topiaid + WHERE d.cell = cell_id AND dm.name = 'LatitudeStart'; + IF FOUND + THEN +-- this is a start elementary + RAISE DEBUG 'Treat elementary Start cell % ', cell_id; +-- get longitude + SELECT + d.datavalue + INTO longitude + FROM data d LEFT OUTER JOIN datametadata AS dm + ON d.datametadata = dm.topiaid + WHERE d.cell = cell_id AND dm.name = 'LongitudeStart'; +-- get depth (try first surface one) + SELECT + d.datavalue + INTO depth + FROM data d LEFT OUTER JOIN datametadata AS dm + ON d.datametadata = dm.topiaid + WHERE d.cell = cell_id AND dm.name = 'DepthRefSurfaceStart'; + IF NOT FOUND + THEN +-- try then bottom + SELECT + d.datavalue + INTO depth + FROM data d LEFT OUTER JOIN datametadata AS dm + ON d.datametadata = dm.topiaid + WHERE d.cell = cell_id AND dm.name = 'DepthRefBottomStart'; + END IF; + ELSE +-- try bary elementary + SELECT + d.datavalue + INTO latitude + FROM data d LEFT OUTER JOIN datametadata AS dm + ON d.datametadata = dm.topiaid + WHERE d.cell = cell_id AND dm.name = 'LatitudeBary'; + IF FOUND + THEN +-- this is a bary elementary + RAISE DEBUG 'Treat elementary Bary cell %', cell_id; + SELECT + d.datavalue + INTO longitude + FROM data d LEFT OUTER JOIN datametadata AS dm + ON d.datametadata = dm.topiaid + WHERE d.cell = cell_id AND dm.name = 'LongitudeBary'; + SELECT + d.datavalue + INTO depth + FROM data d LEFT OUTER JOIN datametadata AS dm + ON d.datametadata = dm.topiaid + WHERE d.cell = cell_id AND dm.name = 'DepthRefSurfaceBary'; + IF NOT FOUND + THEN +-- use depth bottom + SELECT + d.datavalue + INTO depth + FROM data d LEFT OUTER JOIN datametadata AS dm + ON d.datametadata = dm.topiaid + WHERE d.cell = cell_id AND dm.name = 'DepthRefBottomBary'; + END IF; + ELSE +-- try end elementary + SELECT + d.datavalue + INTO latitude + FROM data d LEFT OUTER JOIN datametadata AS dm + ON d.datametadata = dm.topiaid + WHERE d.cell = cell_id AND dm.name = 'LatitudeEnd'; + IF FOUND + THEN +-- this is a end elementary + RAISE DEBUG 'Treat elementary End cell %', cell_id; + SELECT + d.datavalue + INTO longitude + FROM data d LEFT OUTER JOIN datametadata AS dm + ON d.datametadata = dm.topiaid + WHERE d.cell = cell_id AND dm.name = 'LongitudeEnd'; + SELECT + d.datavalue + INTO depth + FROM data d LEFT OUTER JOIN datametadata AS dm + ON d.datametadata = dm.topiaid + WHERE d.cell = cell_id AND dm.name = 'DepthRefSurfaceEnd'; + IF NOT FOUND + THEN +-- use depth bottom + SELECT + d.datavalue + INTO depth + FROM data d LEFT OUTER JOIN datametadata AS dm + ON d.datametadata = dm.topiaid + WHERE d.cell = cell_id AND dm.name = 'DepthRefBottomEnd'; + END IF; + ELSE +-- no spatial data + RAISE DEBUG 'Could not find spatial data for cell %', cell_id; + RETURN; + END IF; + END IF; + END IF; + +-- convert dms latitude to dd latitude + SELECT + echobase_latitudetext_to_dd(latitude) + INTO latitudeNumber; + + IF latitude IS NOT NULL AND latitudeNumber IS NULL + THEN + latitudeNumber := latitude :: REAL; + END IF; + +-- convert dms longitude to dd longitude + SELECT + echobase_longitudetext_to_dd(longitude) + INTO longitudeNumber; + + IF longitude IS NOT NULL AND longitudeNumber IS NULL + THEN + longitudeNumber := longitude :: REAL; + END IF; + + SELECT + 'SRID=4326;POINT(' || longitudeNumber || ' ' || latitudeNumber || ' ' || + depth || ')' + INTO spatialText; + RAISE DEBUG 'spatial data % for cell %', spatialText, cell_id; + + PERFORM echobase_create_echobase_cell_spatial_row(cell_id, + NULL, + spatialText, + NULL); +END +$$ LANGUAGE plpgsql; + +CREATE OR REPLACE FUNCTION echobase_fill_region_cell_spatial_table(cell_id VARCHAR) + RETURNS VOID AS $$ +DECLARE + latitude REAL; + longitude REAL; + depth REAL; + dataMetadataId VARCHAR; + dataValue VARCHAR; + spatialText VARCHAR := 'POLYGON(('; +BEGIN + RAISE DEBUG 'Treat region cell %', cell_id; + SELECT + topiaid + INTO dataMetadataId + FROM datametadata + WHERE name = 'RegionEnvCoordinates'; + FOR dataValue IN SELECT + d.datavalue + FROM data d + WHERE d.cell = cell_id AND d.datametadata = dataMetadataId + ORDER BY d.topiacreatedate LOOP +-- split dataValue in lat - long - depth + SELECT + split_part(dataValue, ' ', 1) :: REAL + INTO latitude; + SELECT + split_part(dataValue, ' ', 2) :: REAL + INTO longitude; + SELECT + split_part(dataValue, ' ', 3) :: REAL + INTO depth; + SELECT + spatialText || longitude || ' ' || latitude || ',' + INTO spatialText; + END LOOP; + SELECT + left(spatialText, -1) || '))' + INTO spatialText; + + SELECT 'SRID=4326;' || spatialText INTO spatialText; + + RAISE DEBUG 'spatial data % for cell %', spatialText, cell_id; + + PERFORM echobase_create_echobase_cell_spatial_row(cell_id, + NULL, + NULL, + spatialText); +END +$$ LANGUAGE plpgsql; + +CREATE OR REPLACE FUNCTION echobase_fill_map_cell_spatial_table(cell_id VARCHAR) + RETURNS VOID AS $$ +DECLARE + latitude REAL; + longitude REAL; + deltaLatitude REAL; + deltaLongitude REAL; + P0 VARCHAR; + P1 VARCHAR; + P2 VARCHAR; + P3 VARCHAR; + spatialText VARCHAR; +BEGIN + RAISE DEBUG 'Treat map cell % ', cell_id; +-- get GridCellLatitude + SELECT + d.datavalue :: REAL + INTO latitude + FROM data d LEFT OUTER JOIN datametadata AS dm + ON d.datametadata = dm.topiaid + WHERE d.cell = cell_id AND dm.name = 'GridCellLatitude'; +-- get GridCellLatitude + SELECT + d.datavalue :: REAL + INTO longitude + FROM data d LEFT OUTER JOIN datametadata AS dm + ON d.datametadata = dm.topiaid + WHERE d.cell = cell_id AND dm.name = 'GridCellLongitude'; +-- get GridCellLatitude + SELECT + d.datavalue :: REAL + INTO deltaLatitude + FROM data d LEFT OUTER JOIN datametadata AS dm + ON d.datametadata = dm.topiaid + WHERE d.cell = cell_id AND dm.name = 'GridLatitudeLag'; +-- get GridCellLatitude + SELECT + d.datavalue :: REAL + INTO deltaLongitude + FROM data d LEFT OUTER JOIN datametadata AS dm + ON d.datametadata = dm.topiaid + WHERE d.cell = cell_id AND dm.name = 'GridLongitudeLag'; + + IF + latitude IS NOT NULL AND longitude IS NOT NULL AND deltaLatitude IS NOT NULL + AND deltaLongitude IS NOT NULL + THEN + SELECT + longitude || ' ' || latitude + INTO P0; + SELECT + longitude || ' ' || latitude + deltaLatitude + INTO P1; + SELECT + longitude + deltaLongitude || ' ' || latitude + deltaLatitude + INTO P2; + SELECT + longitude + deltaLongitude || ' ' || latitude + INTO P3; + + SELECT + 'SRID=4326;POLYGON((' || p0 || ',' || P1 || ',' || P2 || ',' || P3 || ',' || P0 || + '))' + INTO spatialText; + RAISE DEBUG 'spatial data % for cell %', spatialText, cell_id; + + PERFORM echobase_create_echobase_cell_spatial_row(cell_id, + NULL, + NULL, + spatialText); + END IF; +END +$$ LANGUAGE plpgsql; + +-- procedure pour creer (ou mettre à jour) une ligne dans echobase_cell_spatial + +CREATE OR REPLACE FUNCTION echobase_create_echobase_cell_spatial_row( + cell_id VARCHAR, + coordinateText VARCHAR, + coordinate3dText VARCHAR, + shapeText VARCHAR) + RETURNS VOID AS $$ +DECLARE + cellSpatialRow RECORD; + voyageId VARCHAR; + voyageName VARCHAR; + transitId VARCHAR; + transitName VARCHAR; + transectId VARCHAR; + transectName VARCHAR; + dataAcquisitionId VARCHAR; + dataAcquisitionName VARCHAR; + dataProcessingId VARCHAR; + dataProcessingName VARCHAR; + cellName VARCHAR; + cellTypeId VARCHAR; + cellTypeName VARCHAR; + cellParentId VARCHAR; + cellRow RECORD; + coordinateData GEOMETRY; + coordinate3dData GEOMETRY; + shapeData GEOMETRY; +BEGIN + IF coordinateText IS NULL AND coordinate3dText IS NULL AND + shapeText IS NULL + THEN + RAISE DEBUG 'Could not find spatial data for cell %', cell_id; + RETURN; + END IF; + IF coordinateText IS NOT NULL + THEN + BEGIN + coordinateData = ST_GeomFromEWKT(coordinateText); + EXCEPTION WHEN internal_error + THEN + + RAISE LOG 'Could not create coordinate spatial data %', coordinateText; + RETURN; + END; + ELSEIF coordinate3dText IS NOT NULL + THEN + BEGIN + coordinate3dData = ST_GeomFromEWKT(coordinate3dText); + EXCEPTION WHEN internal_error + THEN + + RAISE LOG 'Could not create coordinate3D spatial data %', coordinate3dText; + RETURN; + END; + ELSEIF shapeText IS NOT NULL + THEN + BEGIN + shapeData = ST_GeomFromEWKT(shapeText); + EXCEPTION WHEN internal_error + THEN + + RAISE LOG 'Could not create shape spatial data %', shapeText; + RETURN; + END; + END IF; + + SELECT + * + INTO cellSpatialRow + FROM echobase_cell_spatial cs + WHERE cs.cellid = cell_id; + IF NOT FOUND + THEN +-- create row + RAISE DEBUG 'Will create spatial cell %', cell_id; + SELECT + cell_id + INTO cellParentId; + LOOP + IF dataProcessingId IS NULL + THEN +-- try to get dataprocessingId from this cell + SELECT + dp.topiaid, + dp.processingdescription + INTO dataProcessingId + FROM dataprocessing dp, cell c + WHERE c.topiaid = cellParentId AND dp.topiaid = c.dataprocessing; + END IF; + SELECT + topiaid, + cell + INTO cellRow + FROM cell + WHERE topiaid = cellParentId; + EXIT WHEN cellRow.cell IS NULL; + SELECT + cellRow.cell + INTO cellParentId; + END LOOP; + RAISE DEBUG 'use cell parentId %', cellParentId; +-- get cell infos + SELECT + c.name, + ct.name, + ct.topiaid + INTO cellName, cellTypeName, cellTypeId + FROM cell c, celltype ct + WHERE c.topiaid = cell_id AND c.celltype = ct.topiaid; + + IF dataProcessingId IS NULL + THEN +-- get voyage infos + SELECT + v.topiaid, + v.name + INTO voyageId, voyageName + FROM voyage v, cell c + WHERE c.topiaid = cellParentId AND v.topiaid = c.voyage; + ELSE + +-- get dataAcquisition infos + SELECT + da.topiaid, + da.acousticinstrument + INTO dataAcquisitionId, dataAcquisitionName + FROM dataacquisition da, dataprocessing dp + WHERE dp.topiaId = dataProcessingId AND da.topiaid = dp.dataacquisition; +-- get transect infos + SELECT + t.topiaid, + t.vessel + INTO transectId, transitName + FROM transect t, dataacquisition da + WHERE da.topiaid = dataAcquisitionId AND t.topiaid = da.transect; +-- get transit infos + SELECT + t.topiaid, + (t.starttime || ' - ' || t.endtime) + INTO transitId, transitName + FROM transit t, transect tt + WHERE tt.topiaid = transectId AND t.topiaid = tt.transit; +-- get voyage infos + SELECT + v.topiaid, + v.name + INTO voyageId, voyageName + FROM voyage v, transit t + WHERE t.topiaid = transitId AND v.topiaid = t.voyage; + END IF; + INSERT INTO echobase_cell_spatial (lastUpdateDate, voyageid, voyagename, transitid, transitname, transectid, transectname, dataacquisitionid, dataacquisitionname, dataprocessingid, dataprocessingname, celltypeid, celltypename, cellid, cellname, coordinate, coordinate3d, shape) + VALUES (now(), voyageId, voyageName, transitId, transitName, transectId, transectName, dataAcquisitionId, dataAcquisitionName, dataProcessingId, dataProcessingName, cellTypeId, cellTypeName, cell_id, cellName, coordinateData, coordinate3dData, shapeData); + ELSE +-- update row + RAISE DEBUG 'Will update spatial cell % ', cell_id; + UPDATE echobase_cell_spatial + SET coordinate = coordinateData, + coordinate3d = coordinate3dData, + shape = shapeData + WHERE cellid = cell_id; + END IF; +END +$$ LANGUAGE plpgsql; + +-- pour obtenir le type d'une cellule sous forme ESDU - ELEMENTARY - REGION ou MAP +CREATE OR REPLACE FUNCTION echobase_get_cell_type(cell_id VARCHAR) + RETURNS VARCHAR AS $$ +DECLARE result VARCHAR; +BEGIN + SELECT + UPPER(ct.id) + INTO result + FROM celltype ct, cell ce + WHERE ce.topiaid = cell_id AND ct.topiaId = ce.celltype; + IF 'REGIONCLAS' = result OR 'REGIONSURF' = result + THEN + result = 'REGION'; + END IF; + RETURN result; +END +$$ +LANGUAGE plpgsql; + +-------------------------------------------------------------------------------- +-- Table spatial pour les operations ------------------------------------------- +-------------------------------------------------------------------------------- + +CREATE TABLE echobase_operation_spatial ( + voyageid VARCHAR(256) NOT NULL, + voyagename VARCHAR(256), + transitid VARCHAR(256), + transitname VARCHAR(256), + transectid VARCHAR(256), + transectname VARCHAR(256), + operationid VARCHAR(256) PRIMARY KEY, + operationname VARCHAR(256) NOT NULL, + lastUpdateDate TIMESTAMP NOT NULL, + FOREIGN KEY (operationid) REFERENCES operation (topiaid), + FOREIGN KEY (transectid) REFERENCES transect (topiaid), + FOREIGN KEY (transitid) REFERENCES transit (topiaid), + FOREIGN KEY (voyageid) REFERENCES voyage (topiaid) +); + +SELECT AddGeometryColumn('echobase_operation_spatial', 'coordinate', 4326, 'POINT',2 ); + +CREATE INDEX echobase_operation_spatial_coordinate_gix ON echobase_operation_spatial USING GIST (coordinate); +CREATE INDEX echobase_operation_spatial_voyageid_idx ON echobase_operation_spatial USING BTREE(voyageid); + +-- pour stoquer les traitements spatiaux a effectuer + +CREATE TABLE echobase_operation_spatial_temp ( + operationid VARCHAR(256) PRIMARY KEY, + FOREIGN KEY (operationid) REFERENCES operation (topiaid) +); + +-- +-- Trigger qui met à jour la table des traitements spatiaux à faire +-- + +CREATE OR REPLACE FUNCTION echobase_fill_operation_spatial_temp_table() + RETURNS TRIGGER AS $$ +DECLARE + operation_id VARCHAR; + result RECORD; +BEGIN + + IF (TG_OP = 'DELETE') + THEN + result = OLD; + ELSE + result = NEW; + END IF; + + operation_id = result.topiaid; + + IF (SELECT count(*) FROM echobase_operation_spatial_temp c + WHERE c.operationid = operation_id) > 0 + THEN +-- operation est deja a traitee + RETURN result; + END IF; +--- Ajout de l'operation dans la table des traitements à effectuer + RAISE DEBUG 'Add operation % to echobase_operation_spatial_temp', operation_id; + INSERT INTO echobase_operation_spatial_temp (operationid) VALUES (operation_id); + RETURN result; +END +$$ +LANGUAGE 'plpgsql'; + +CREATE TRIGGER echobase_fill_operation_spatial_work_table_trigger +AFTER INSERT OR UPDATE ON operation +FOR EACH ROW WHEN (NEW.topiaid IS NOT + NULL) EXECUTE PROCEDURE echobase_fill_operation_spatial_temp_table(); + +-- +-- Trigger qui met à jour la table des traitements spatiaux à faire lors de la +-- suppression d'une operation +-- + +CREATE OR REPLACE FUNCTION echobase_delete_operation() + RETURNS TRIGGER AS $$ +BEGIN + + RAISE DEBUG 'Delete operation % , delete cascade in echobase_operation_spatial_table', OLD.topiaid; + + DELETE FROM echobase_operation_spatial WHERE operationid = OLD.topiaid; + + RETURN OLD; +END +$$ +LANGUAGE 'plpgsql'; + +CREATE TRIGGER echobase_delete_operation +BEFORE DELETE ON operation +FOR EACH ROW EXECUTE PROCEDURE echobase_delete_operation(); + +-- +-- Mettre a jour toutes les données spatiales d'operation +-- + +CREATE OR REPLACE FUNCTION echobase_compute_all_operation_spatial_data() + RETURNS VOID AS $$ +DECLARE operation_id VARCHAR; +BEGIN + DELETE FROM echobase_operation_spatial; + + FOR operation_id IN SELECT topiaid FROM operation LOOP + PERFORM echobase_fill_operation_spatial_row(operation_id); + END LOOP; +END +$$ +LANGUAGE plpgsql; + +-- +-- Mettre a jour la table echobase_cell_spatial depuis echobase_operation_spatial_temp +-- + +CREATE OR REPLACE FUNCTION echobase_fill_operation_spatial_table() + RETURNS VOID AS $$ +DECLARE + operationRow RECORD; +BEGIN + FOR operationRow IN SELECT * FROM echobase_operation_spatial_temp LOOP + + PERFORM echobase_fill_operation_spatial_row(operationRow.operationid); + END LOOP; + + DELETE FROM echobase_operation_spatial_temp; + +END +$$ +LANGUAGE plpgsql; + +CREATE OR REPLACE FUNCTION echobase_fill_operation_spatial_row(operation_id VARCHAR) + RETURNS VOID AS $$ + DECLARE + spatialText VARCHAR; + latitudeNumber REAL; + longitudeNumber REAL; +BEGIN + RAISE NOTICE 'Treat spatial operation % ...', operation_id; + SELECT o.midHaulLatitude, o.midHaulLongitude + INTO latitudeNumber, longitudeNumber FROM operation o + WHERE o.topiaid = operation_id; + + SELECT + 'SRID=4326;POINT(' || longitudeNumber || ' ' || latitudeNumber || ')' + INTO spatialText; + RAISE DEBUG 'spatial data % for operation %', spatialText, operation_id; + + PERFORM echobase_create_echobase_operation_spatial_row(operation_id, + spatialText); +END +$$ +LANGUAGE plpgsql; + +-- procedure pour creer (ou mettre à jour) une ligne dans echobase_cell_spatial + +CREATE OR REPLACE FUNCTION echobase_create_echobase_operation_spatial_row( + operation_id VARCHAR, + coordinateText VARCHAR) + RETURNS VOID AS $$ +DECLARE + operationSpatialRow RECORD; + voyageId VARCHAR; + voyageName VARCHAR; + transitId VARCHAR; + transitName VARCHAR; + transectId VARCHAR; + transectName VARCHAR; + operationId VARCHAR; + operationName VARCHAR; + operationRow RECORD; + coordinateData GEOMETRY; +BEGIN + IF coordinateText IS NULL + THEN + RAISE DEBUG 'Could not find spatial data for operation %', operation_id; + RETURN; + END IF; + BEGIN + coordinateData = ST_GeomFromEWKT(coordinateText); + EXCEPTION WHEN internal_error + THEN + RAISE LOG 'Could not create coordinate operation spatial data %', coordinateText; + RETURN; + END; + + SELECT * INTO operationSpatialRow FROM echobase_operation_spatial os + WHERE os.operationid = operation_id; + IF NOT FOUND + THEN +-- create row + RAISE DEBUG 'Will create spatial operation %', operation_id; +-- get operation infos + SELECT op.id, op.topiaid + INTO operationName, operationId + FROM operation op + WHERE op.topiaid = operation_id; +-- get transect infos + SELECT + t.topiaid, + t.vessel + INTO transectId, transitName + FROM transect t, operation op + WHERE op.topiaid = operation_id AND t.topiaid = op.transect; +-- get transit infos + SELECT + t.topiaid, + (t.starttime || ' - ' || t.endtime) + INTO transitId, transitName + FROM transit t, transect tt + WHERE tt.topiaid = transectId AND t.topiaid = tt.transit; +-- get voyage infos + SELECT + v.topiaid, + v.name + INTO voyageId, voyageName + FROM voyage v, transit t + WHERE t.topiaid = transitId AND v.topiaid = t.voyage; + + INSERT INTO echobase_operation_spatial (lastUpdateDate, voyageid, voyagename, transitid, transitname, transectid, transectname, operationid, operationname, coordinate) + VALUES (now(), voyageId, voyageName, transitId, transitName, transectId, transectName, operationId, operationName, coordinateData); + ELSE +-- update row + RAISE DEBUG 'Will update spatial operation % ', operation_id; + UPDATE echobase_operation_spatial eos + SET eos.coordinate = coordinateData + WHERE eos.operationid = operation_id; + END IF; +END +$$ LANGUAGE plpgsql; + +-------------------------------------------------------------------------------- +-------------------------------------------------------------------------------- +-------------------------------------------------------------------------------- + +-- pour convertir des latitudes en dms (+ hemi) en dd +CREATE OR REPLACE FUNCTION echobase_latitudetext_to_dd(latitude VARCHAR) + RETURNS REAL AS $$ +DECLARE + degre_ INTEGER; + minute_ INTEGER; + second_ INTEGER; + hemi_ VARCHAR(1); +BEGIN + SELECT + right(latitude, 1), + left(latitude, 2) :: INTEGER, + substring(latitude FROM 3 FOR 2) :: INTEGER, + substring(latitude FROM 6 FOR 3) :: INTEGER + INTO hemi_, degre_, minute_, second_; + + RETURN echobase_dms2dd(degre_, minute_, second_, hemi_); + EXCEPTION WHEN invalid_text_representation + THEN + RETURN NULL; +END +$$ LANGUAGE plpgsql; + +-- pour convertir des longitudes en dms (+ hemi) en dd +CREATE OR REPLACE FUNCTION echobase_longitudetext_to_dd(longitude VARCHAR) + RETURNS REAL AS $$ +DECLARE + degre_ INTEGER; + minute_ INTEGER; + second_ INTEGER; + hemi_ VARCHAR(1); +BEGIN + SELECT + right(longitude, 1), + left(longitude, 3) :: INTEGER, + substring(longitude FROM 4 FOR 2) :: INTEGER, + substring(longitude FROM 7 FOR 3) :: INTEGER + INTO hemi_, degre_, minute_, second_; + + RETURN echobase_dms2dd(degre_, minute_, second_, hemi_); + EXCEPTION WHEN invalid_text_representation + THEN + RETURN NULL; +END +$$ LANGUAGE plpgsql; + +CREATE OR REPLACE FUNCTION echobase_dms2dd( + D INTEGER, M INTEGER, S INTEGER, HEMI CHARACTER VARYING(1) +) + RETURNS DOUBLE PRECISION AS $$ +DECLARE + ret DOUBLE PRECISION; + dir INTEGER; +BEGIN + dir := 1; +--init to 1 for default positive return + ret := 0; +--init to zero. + --ONLY S or W will trip this. Any other letter or NULL will result in positive return value + IF UPPER(HEMI) = 'S' OR UPPER(HEMI) = 'W' + THEN + dir := -1; --then southern or western hemisphere + END IF; +--SOME data has negative values in minutes and seconds as well as degrees. Use ABS to standardize all three. + ret := (ABS(CAST(D AS DOUBLE PRECISION)) + (ABS((CAST(M AS DOUBLE PRECISION) + + (ABS((CAST(S AS + DOUBLE PRECISION)) + / 60))) / 60))); + ret := ret * dir; + RETURN ret; + +END; +$$ LANGUAGE plpgsql; + +-------------------------------------------------------------------------------- +-------------------------------------------------------------------------------- +-------------------------------------------------------------------------------- + +-- +-- Mettre a jour toutes les données spatiales +-- + +CREATE OR REPLACE FUNCTION echobase_compute_all_spatial_data() + RETURNS VOID AS $$ +DECLARE cell_id VARCHAR; +BEGIN + EXECUTE echobase_compute_all_cell_spatial_data(); + EXECUTE echobase_compute_all_operation_spatial_data(); +END +$$ +LANGUAGE plpgsql; + + +-- Compute all data +SELECT echobase_compute_all_cell_spatial_data(); +SELECT echobase_compute_all_operation_spatial_data(); \ No newline at end of file Copied: trunk/echobase-domain/src/main/resources/postgis-view.sql (from rev 962, trunk/echobase-services/src/main/resources/postgis-view.sql) =================================================================== --- trunk/echobase-domain/src/main/resources/postgis-view.sql (rev 0) +++ trunk/echobase-domain/src/main/resources/postgis-view.sql 2014-02-28 14:08:22 UTC (rev 963) @@ -0,0 +1,442 @@ +--- +-- #%L +-- EchoBase :: Domain +-- $Id$ +-- $HeadURL$ +-- %% +-- Copyright (C) 2011 - 2014 Ifremer, Codelutin +-- %% +-- This program is free software: you can redistribute it and/or modify +-- it under the terms of the GNU Affero General Public License as published by +-- the Free Software Foundation, either version 3 of the License, or +-- (at your option) any later version. +-- +-- This program is distributed in the hope that it will be useful, +-- but WITHOUT ANY WARRANTY; without even the implied warranty of +-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the +-- GNU General Public License for more details. +-- +-- You should have received a copy of the GNU Affero General Public License +-- along with this program. If not, see <http://www.gnu.org/licenses/>. +-- #L% +--- +----------------------------------------------------------------------------------------------------------------------- +---- DROP EXISTING VIEW - INDEX - FUNCTION ---------------------------------------------------------------------------- +----------------------------------------------------------------------------------------------------------------------- + +DROP VIEW IF EXISTS echobase_cell_spatial_result CASCADE; +DROP VIEW IF EXISTS cellmapview CASCADE; + +DROP VIEW IF EXISTS echobase_cell_spatial_data CASCADE ; +DROP VIEW IF EXISTS meanMapcellBiomassEngrEnc CASCADE; +DROP VIEW IF EXISTS meanMapcellBiomassSardPil CASCADE; +DROP VIEW IF EXISTS meanMapcellBiomassTracTru CASCADE; +DROP VIEW IF EXISTS meanMapcellBiomassSpraSpr CASCADE; +DROP VIEW IF EXISTS meanMapcellBiomassScomSco CASCADE; +DROP VIEW IF EXISTS meanMapcellBiomassMicrPou CASCADE; +DROP VIEW IF EXISTS TotalCatchSpOpWide CASCADE; +DROP VIEW IF EXISTS TotalSampleEngrEnc CASCADE; +DROP VIEW IF EXISTS TotalSampleSardPil CASCADE; +DROP VIEW IF EXISTS TotalSampleTracTru CASCADE; +DROP VIEW IF EXISTS TotalSampleSpraSpr CASCADE; +DROP VIEW IF EXISTS TotalSampleScomSco CASCADE; +DROP VIEW IF EXISTS TotalSampleMicrPou CASCADE; + +DROP INDEX IF EXISTS cellEsduViewSpeciesBiomass_uidx CASCADE; +DROP INDEX IF EXISTS cellEsduViewSpeciesAbundance_uidx CASCADE; +DROP INDEX IF EXISTS cellEsduViewSpeciesMeanLength_uidx CASCADE; +DROP INDEX IF EXISTS cellEsduViewEchotypeNasc_uidx CASCADE; +DROP INDEX IF EXISTS cellEsduViewEchotype_idx CASCADE; +DROP INDEX IF EXISTS cellEsduViewSpecies_idx CASCADE; +DROP INDEX IF EXISTS cellEsduViewSpeciesResultGrouped_idx CASCADE; +DROP INDEX IF EXISTS cellmapview_idx CASCADE; +DROP INDEX IF EXISTS cellmapview_uidx CASCADE; +DROP INDEX IF EXISTS TotalSampleView_idx CASCADE; + +DROP MATERIALIZED VIEW IF EXISTS cellEsduViewEchotypeNasc CASCADE; +DROP MATERIALIZED VIEW IF EXISTS cellEsduViewSpeciesBiomass CASCADE; +DROP MATERIALIZED VIEW IF EXISTS cellEsduViewSpeciesAbundance CASCADE; +DROP MATERIALIZED VIEW IF EXISTS cellEsduViewSpeciesMeanLength CASCADE; +DROP MATERIALIZED VIEW IF EXISTS cellEsduViewEchotype CASCADE; +DROP MATERIALIZED VIEW IF EXISTS cellEsduViewSpeciesResultGrouped CASCADE; +DROP MATERIALIZED VIEW IF EXISTS cellEsduViewSpecies CASCADE; +DROP MATERIALIZED VIEW IF EXISTS cellmapview CASCADE; +DROP MATERIALIZED VIEW IF EXISTS TotalSampleView CASCADE; + +DROP TRIGGER IF EXISTS echobase_refresh_views_trigger ON entitymodificationlog CASCADE; +DROP FUNCTION IF EXISTS echobase_to_numeric(string VARCHAR) CASCADE; +DROP FUNCTION IF EXISTS echobase_refresh_views() CASCADE; + +----------------------------------------------------------------------------------------------------------------------- +---- CREATE FUNCTION -------------------------------------------------------------------------------------------------- +----------------------------------------------------------------------------------------------------------------------- + +CREATE OR REPLACE FUNCTION echobase_to_numeric(string VARCHAR) + RETURNS REAL AS $$ +BEGIN + RETURN string::real; + EXCEPTION WHEN invalid_text_representation + THEN + RETURN NULL; +END +$$ LANGUAGE plpgsql; + +CREATE OR REPLACE FUNCTION echobase_refresh_views() + RETURNS VOID AS $$ +BEGIN + RAISE NOTICE 'reload cellEsduViewEchotype'; + REFRESH MATERIALIZED VIEW cellEsduViewEchotype; + REINDEX INDEX cellEsduViewEchotype_idx; + + RAISE NOTICE 'reload cellEsduViewSpecies'; + REFRESH MATERIALIZED VIEW cellEsduViewSpecies; + REINDEX INDEX cellEsduViewSpecies_idx; + + RAISE NOTICE 'reload cellEsduViewSpeciesResultGrouped'; + REFRESH MATERIALIZED VIEW cellEsduViewSpeciesResultGrouped; + REINDEX INDEX cellEsduViewSpeciesResultGrouped_idx; + + RAISE NOTICE 'reload cellEsduViewEchotypeNasc'; + REFRESH MATERIALIZED VIEW cellEsduViewEchotypeNasc; + + RAISE NOTICE 'reload cellEsduViewSpeciesBiomass'; + REFRESH MATERIALIZED VIEW cellEsduViewSpeciesBiomass; + REINDEX INDEX cellEsduViewSpeciesBiomass_uidx; + + RAISE NOTICE 'reload cellEsduViewSpeciesAbundance'; + REFRESH MATERIALIZED VIEW cellEsduViewSpeciesAbundance; + REINDEX INDEX cellEsduViewSpeciesAbundance_uidx; + + RAISE NOTICE 'reload cellEsduViewSpeciesMeanLength'; + REFRESH MATERIALIZED VIEW cellEsduViewSpeciesMeanLength; + REINDEX INDEX cellEsduViewSpeciesMeanLength_uidx; + + RAISE NOTICE 'reload cellmapview'; + REFRESH MATERIALIZED VIEW cellmapview; + REINDEX INDEX cellmapview_idx; + REINDEX INDEX cellmapview_uidx; +END +$$ +LANGUAGE 'plpgsql'; + +----------------------------------------------------------------------------------------------------------------------- +---- CELL RESULT VIEW ------------------------------------------------------------------------------------------------- +----------------------------------------------------------------------------------------------------------------------- + +CREATE MATERIALIZED VIEW cellEsduViewEchotype AS + SELECT + c.voyagename, + c.coordinate, + c.cellname, + d.name, + echobase_to_numeric(r.resultvalue) as resultvalue, + e.name as echotypeName, + e.meaning as echotypeMeaning, + r.topiaid AS id + FROM + echobase_cell_spatial c, + result r, + datametadata d, + category cat, + echotype e + WHERE + c.cellid = r.cell + AND r.datametadata = d.topiaid + AND r.category = cat.topiaid + AND cat.echotype IS NOT NULL + AND cat.speciescategory IS NULL + AND cat.echotype = e.topiaid + AND c.celltypename = 'Elementary Distance Sampling Unit'; + +CREATE INDEX cellEsduViewEchotype_idx ON cellEsduViewEchotype(name); + +CREATE MATERIALIZED VIEW cellEsduViewEchotypeNasc AS + SELECT + * + FROM + cellEsduViewEchotype v + WHERE + v.name = 'NASC'; + +CREATE UNIQUE INDEX cellEsduViewEchotypeNasc_uidx ON cellEsduViewEchotypeNasc(id); + +CREATE MATERIALIZED VIEW cellEsduViewSpecies AS + SELECT + c.voyagename, + c.coordinate, + c.cellname, + d.name, + echobase_to_numeric(r.resultvalue) as resultvalue, + s.baracoudacode, + r.topiaid AS id + FROM + echobase_cell_spatial c, + result r, + datametadata d, + category cat, + speciescategory scat, + species s + WHERE + c.cellid = r.cell + AND r.datametadata = d.topiaid + AND r.category = cat.topiaid + AND cat.speciescategory IS NOT NULL + AND cat.speciescategory = scat.topiaid + AND scat.species = s.topiaid + AND c.celltypename = 'Elementary Distance Sampling Unit'; + +CREATE INDEX cellEsduViewSpecies_idx ON cellEsduViewSpecies(name); + +CREATE MATERIALIZED VIEW cellEsduViewSpeciesResultGrouped AS + SELECT + count(*) AS nbResults, + name, + cellname, + baracoudacode, + sum(resultvalue) as resultvalue, + cellname || '-' || baracoudacode || '-' || name as id + FROM cellEsduViewSpecies + WHERE + baracoudacode in ('ENGR-ENC', 'SARD-PIL', 'TRAC-TRU', 'SPRA-SPR', 'SCOM-SCO', 'MICR-POU') + AND name in ('Biomass','Abundance','MeanLength') + GROUP BY baracoudacode, cellname, name; + +CREATE INDEX cellEsduViewSpeciesResultGrouped_idx ON cellEsduViewSpecies(baracoudacode, cellname, name); + +CREATE MATERIALIZED VIEW cellEsduViewSpeciesBiomass AS + SELECT + distinct v2.coordinate, + v2.voyagename, + v.nbResults, + v.cellname, + v.baracoudacode, + v.resultvalue, + v.id + FROM + cellEsduViewSpeciesResultGrouped v + JOIN cellEsduViewSpecies v2 USING (name, baracoudacode, cellname) + WHERE + v.name = 'Biomass'; + +CREATE UNIQUE INDEX cellEsduViewSpeciesBiomass_uidx ON cellEsduViewSpeciesBiomass(id); + +CREATE MATERIALIZED VIEW cellEsduViewSpeciesAbundance AS + SELECT + distinct v2.coordinate, + v2.voyagename, + v.nbResults, + v.cellname, + v.baracoudacode, + v.resultvalue, + v.id + FROM + cellEsduViewSpeciesResultGrouped v + JOIN cellEsduViewSpecies v2 USING (name, baracoudacode, cellname) + WHERE + v.name = 'Abundance'; + +CREATE UNIQUE INDEX cellEsduViewSpeciesAbundance_uidx ON cellEsduViewSpeciesAbundance(id); + +CREATE MATERIALIZED VIEW cellEsduViewSpeciesMeanLength AS + SELECT + distinct v2.coordinate, + v2.voyagename, + v.nbResults, + v.cellname, + v.baracoudacode, + v.resultvalue / v.nbResults as resultvalue, + v.id + FROM + cellEsduViewSpeciesResultGrouped v + JOIN cellEsduViewSpecies v2 USING (name, baracoudacode, cellname) + WHERE + v.name = 'MeanLength'; + +CREATE UNIQUE INDEX cellEsduViewSpeciesMeanLength_uidx ON cellEsduViewSpeciesMeanLength(id); + +----------------------------------------------------------------------------------------------------------------------- +---- MAP CELL VIEW ---------------------------------------------------------------------------------------------------- +----------------------------------------------------------------------------------------------------------------------- + +CREATE MATERIALIZED VIEW cellmapview AS + SELECT + c.voyagename, + c.shape, + c.cellname, + d.name, + s.baracoudacode, + echobase_to_numeric(r.resultvalue) as resultvalue, + r.topiaid AS id + FROM + echobase_cell_spatial c, + result r, + datametadata d, + category cat, + speciescategory scat, + species s + WHERE + c.cellid = r.cell + AND r.datametadata = d.topiaid + AND r.category = cat.topiaid + AND cat.speciescategory = scat.topiaid + AND scat.species = s.topiaid + AND c.celltypename = 'Map cell'; + +CREATE INDEX cellmapview_idx ON cellmapview(baracoudacode, name); +CREATE UNIQUE INDEX cellmapview_uidx ON cellmapview(id); + +CREATE OR REPLACE VIEW meanMapcellBiomassEngrEnc AS + SELECT + * + FROM + cellmapview v + WHERE + v.baracoudacode = 'ENGR-ENC' + AND v.name = 'meanMapcellBiomass'; + +CREATE OR REPLACE VIEW meanMapcellBiomassSardPil AS + SELECT + * + FROM + cellmapview v + WHERE + v.baracoudacode = 'SARD-PIL' + AND v.name = 'meanMapcellBiomass'; + +CREATE OR REPLACE VIEW meanMapcellBiomassTracTru AS + SELECT + * + FROM + cellmapview v + WHERE + v.baracoudacode = 'TRAC-TRU' + AND v.name = 'meanMapcellBiomass'; + +CREATE OR REPLACE VIEW meanMapcellBiomassSpraSpr AS + SELECT + * + FROM + cellmapview v + WHERE + v.baracoudacode = 'SPRA-SPR' + AND v.name = 'meanMapcellBiomass'; + +CREATE OR REPLACE VIEW meanMapcellBiomassScomSco AS + SELECT + * + FROM + cellmapview v + WHERE + v.baracoudacode = 'SCOM-SCO' + AND v.name = 'meanMapcellBiomass'; + +CREATE OR REPLACE VIEW meanMapcellBiomassMicrPou AS + SELECT + * + FROM + cellmapview v + WHERE + v.baracoudacode = 'MICR-POU' + AND v.name = 'meanMapcellBiomass'; + +----------------------------------------------------------------------------------------------------------------------- +---- OPERATION VIEW --------------------------------------------------------------------------------------------------- +----------------------------------------------------------------------------------------------------------------------- + +CREATE MATERIALIZED VIEW TotalSampleView AS + SELECT + echobase_operation_spatial.voyageid, + operation.id AS operation_id, + echobase_operation_spatial.coordinate, + sampletype.name AS sampletype_name, + sample.sampleweight, + sizecategory.name AS sizecategory_name, + species.baracoudacode, + sample.topiaid + FROM + echobase_operation_spatial, + operation, + sample, + speciescategory, + sizecategory, + species, + sampletype + WHERE + echobase_operation_spatial.operationid = operation.topiaid AND + operation.topiaid = sample.operation AND + sample.speciescategory = speciescategory.topiaid AND + sample.sampletype = sampletype.topiaid AND + speciescategory.sizecategory = sizecategory.topiaid AND + speciescategory.species = species.topiaid; + +CREATE INDEX TotalSampleView_idx ON TotalSampleView(baracoudacode, sampletype_name); + +CREATE OR REPLACE VIEW TotalSampleEngrEnc AS + SELECT + * + FROM + TotalSampleView v + WHERE + v.sampletype_name = 'Total' AND + v.baracoudacode = 'ENGR-ENC'; + +CREATE OR REPLACE VIEW TotalSampleSardPil AS + SELECT + * + FROM + TotalSampleView v + WHERE + v.sampletype_name = 'Total' AND + v.baracoudacode = 'SARD-PIL'; + +CREATE OR REPLACE VIEW TotalSampleTracTru AS + SELECT + * + FROM + TotalSampleView v + WHERE + v.sampletype_name = 'Total' AND + v.baracoudacode = 'TRAC-TRU'; + +CREATE OR REPLACE VIEW TotalSampleSpraSpr AS + SELECT + * + FROM + TotalSampleView v + WHERE + v.sampletype_name = 'Total' AND + v.baracoudacode = 'SPRA-SPR'; + +CREATE OR REPLACE VIEW TotalSampleScomSco AS + SELECT + * + FROM + TotalSampleView v + WHERE + v.sampletype_name = 'Total' AND + v.baracoudacode = 'SCOM-SCO'; + +CREATE OR REPLACE VIEW TotalSampleMicrPou AS + SELECT + * + FROM + TotalSampleView v + WHERE + v.sampletype_name = 'Total' AND + v.baracoudacode = 'MICR-POU'; + +-- Create a view with species catches as columns + +CREATE OR REPLACE VIEW TotalCatchSpOpWide AS + SELECT + e.voyagename, + e.operationname, + e.coordinate, + (SELECT SUM(t.sampleweight) FROM totalsampleengrenc t WHERE t.operation_id = e.operationname) AS TotalCatchENGRENC, + (SELECT SUM(t.sampleweight) FROM totalsamplesardpil t WHERE t.operation_id = e.operationname) AS TotalCatchSARDPIL, + (SELECT SUM(t.sampleweight) FROM totalsamplespraspr t WHERE t.operation_id = e.operationname) AS TotalCatchSPRASPR, + (SELECT SUM(t.sampleweight) FROM totalsamplemicrpou t WHERE t.operation_id = e.operationname) AS TotalCatchMICRPOU, + (SELECT SUM(t.sampleweight) FROM totalsamplescomsco t WHERE t.operation_id = e.operationname) AS TotalCatchSCOMSCO, + (SELECT SUM(t.sampleweight) FROM totalsampletractru t WHERE t.operation_id = e.operationname) AS TotalCatchTRACTRU, + e.operationid as id + FROM echobase_operation_spatial e; \ No newline at end of file Deleted: trunk/echobase-services/src/main/resources/postgis-structure.sql =================================================================== --- trunk/echobase-services/src/main/resources/postgis-structure.sql 2014-02-27 20:41:19 UTC (rev 962) +++ trunk/echobase-services/src/main/resources/postgis-structure.sql 2014-02-28 14:08:22 UTC (rev 963) @@ -1,1218 +0,0 @@ ---- --- #%L --- EchoBase :: UI --- $Id$ --- $HeadURL$ --- %% --- Copyright (C) 2011 - 2013 Ifremer, Codelutin --- %% --- This program is free software: you can redistribute it and/or modify --- it under the terms of the GNU Affero General Public License as published by --- the Free Software Foundation, either version 3 of the License, or --- (at your option) any later version. --- --- This program is distributed in the hope that it will be useful, --- but WITHOUT ANY WARRANTY; without even the implied warranty of --- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the --- GNU General Public License for more details. --- --- You should have received a copy of the GNU Affero General Public License --- along with this program. If not, see <http://www.gnu.org/licenses/>. --- #L% ---- -CREATE EXTENSION IF NOT EXISTS postgis; - -DROP VIEW IF EXISTS TotalCatchSpOpWide CASCADE; -DROP VIEW IF EXISTS echobase_cell_spatial_data CASCADE; -DROP VIEW IF EXISTS echobase_cell_spatial_result CASCADE; -DROP VIEW IF EXISTS TotalSampleEngrEnc CASCADE; -DROP VIEW IF EXISTS TotalSampleSardPil CASCADE; -DROP VIEW IF EXISTS TotalSampleTracTru CASCADE; -DROP VIEW IF EXISTS TotalSampleSpraSpr CASCADE; -DROP VIEW IF EXISTS TotalSampleScomSco CASCADE; -DROP VIEW IF EXISTS TotalSampleMicrPou CASCADE; -DROP VIEW IF EXISTS cellmapview CASCADE; - -DROP INDEX IF EXISTS echobase_cell_spatial_coordinate_gix CASCADE; -DROP INDEX IF EXISTS echobase_cell_spatial_coordinate3D_gix CASCADE; -DROP INDEX IF EXISTS echobase_cell_spatial_shape_gix CASCADE; -DROP INDEX IF EXISTS echobase_cell_spatial_voyageid_idx CASCADE; -DROP TABLE IF EXISTS echobase_cell_spatial CASCADE; -DROP TABLE IF EXISTS echobase_cell_spatial_temp CASCADE; - -DROP TRIGGER IF EXISTS echobase_fill_cell_spatial_work_table_trigger ON data CASCADE; -DROP FUNCTION IF EXISTS echobase_fill_spatial_temp_table() CASCADE; -DROP FUNCTION IF EXISTS echobase_fill_cell_spatial_temp_table() CASCADE; -DROP TRIGGER IF EXISTS echobase_delete_cell ON cell CASCADE; -DROP FUNCTION IF EXISTS echobase_delete_cell() CASCADE; -DROP FUNCTION IF EXISTS echobase_compute_all_cell_spatial_data() CASCADE; -DROP FUNCTION IF EXISTS echobase_fill_cell_spatial_table() CASCADE; -DROP FUNCTION IF EXISTS echobase_fill_cell_spatial_row(cell_id VARCHAR) CASCADE; -DROP FUNCTION IF EXISTS echobase_fill_esdu_cell_spatial_table(cell_id VARCHAR) CASCADE; -DROP FUNCTION IF EXISTS echobase_fill_elementary_cell_spatial_table(cell_id VARCHAR) CASCADE; -DROP FUNCTION IF EXISTS echobase_fill_region_cell_spatial_table(cell_id VARCHAR) CASCADE; -DROP FUNCTION IF EXISTS echobase_fill_map_cell_spatial_table(cell_id VARCHAR) CASCADE; -DROP FUNCTION IF EXISTS echobase_create_echobase_cell_spatial_row( -cell_id VARCHAR, -coordinateText VARCHAR, -coordinate3dText VARCHAR, -shapeText VARCHAR) CASCADE; -DROP FUNCTION IF EXISTS echobase_get_cell_type(cell_id VARCHAR) CASCADE; - - -DROP INDEX IF EXISTS echobase_operation_spatial_coordinate_gix; -DROP INDEX IF EXISTS echobase_operation_spatial_voyageid_idx; -DROP TABLE IF EXISTS echobase_operation_spatial CASCADE ; -DROP TABLE IF EXISTS echobase_operation_spatial_temp CASCADE; -DROP TRIGGER IF EXISTS echobase_fill_operation_spatial_work_table_trigger ON operation CASCADE; -DROP FUNCTION IF EXISTS echobase_fill_operation_spatial_temp_table() CASCADE; -DROP TRIGGER IF EXISTS echobase_delete_operation ON operation CASCADE; -DROP FUNCTION IF EXISTS echobase_delete_operation() CASCADE; -DROP FUNCTION IF EXISTS echobase_compute_all_operation_spatial_data() CASCADE; -DROP FUNCTION IF EXISTS echobase_fill_operation_spatial_table() CASCADE; -DROP FUNCTION IF EXISTS echobase_fill_operation_spatial_row(operation_id VARCHAR) CASCADE; -DROP FUNCTION IF EXISTS echobase_create_echobase_operation_spatial_row( -operation_id VARCHAR, -coordinateText VARCHAR) CASCADE; -DROP FUNCTION IF EXISTS echobase_latitudetext_to_dd(latitude VARCHAR) CASCADE; -DROP FUNCTION IF EXISTS echobase_longitudetext_to_dd(longitude VARCHAR) CASCADE; -DROP FUNCTION IF EXISTS echobase_dms2dd(D INTEGER, M INTEGER, S INTEGER, HEMI CHARACTER VARYING(1)) CASCADE; -DROP FUNCTION IF EXISTS echobase_compute_all_spatial_data() CASCADE; - --------------------------------------------------------------------------------- --- Table spatial pour les cellules --------------------------------------------- --------------------------------------------------------------------------------- - -CREATE TABLE echobase_cell_spatial ( - voyageid VARCHAR(256) NOT NULL, - voyagename VARCHAR(256), - transitid VARCHAR(256), - transitname VARCHAR(256), - transectid VARCHAR(256), - transectname VARCHAR(256), - dataAcquisitionid VARCHAR(256), - dataAcquisitionname VARCHAR(256), - dataProcessingid VARCHAR(256), - dataProcessingname VARCHAR(256), - celltypeid VARCHAR(256) NOT NULL, - celltypename VARCHAR(256) NOT NULL, - cellid VARCHAR(256) PRIMARY KEY, - cellname VARCHAR(256) NOT NULL, - lastUpdateDate TIMESTAMP NOT NULL, - FOREIGN KEY (cellid) REFERENCES cell (topiaid), - FOREIGN KEY (celltypeid) REFERENCES celltype (topiaid), - FOREIGN KEY (dataacquisitionid) REFERENCES dataacquisition (topiaid), - FOREIGN KEY (dataprocessingid) REFERENCES dataprocessing (topiaid), - FOREIGN KEY (transectid) REFERENCES transect (topiaid), - FOREIGN KEY (transitid) REFERENCES transit (topiaid), - FOREIGN KEY (voyageid) REFERENCES voyage (topiaid) -); - -SELECT AddGeometryColumn('echobase_cell_spatial', 'coordinate', 4326, 'POINT',2 ); -SELECT AddGeometryColumn('echobase_cell_spatial', 'coordinate3d', 4326, 'POINT',3); -SELECT AddGeometryColumn('echobase_cell_spatial', 'shape', 4326, 'POLYGON',2 ); - -CREATE INDEX echobase_cell_spatial_coordinate_gix ON echobase_cell_spatial USING GIST (coordinate); -CREATE INDEX echobase_cell_spatial_coordinate3D_gix ON echobase_cell_spatial USING GIST (coordinate3d); -CREATE INDEX echobase_cell_spatial_shape_gix ON echobase_cell_spatial USING GIST (shape); -CREATE INDEX echobase_cell_spatial_voyageid_idx ON echobase_cell_spatial USING BTREE(voyageid); - --- pour stoquer les traitements spatiaux a effectuer - -CREATE TABLE echobase_cell_spatial_temp ( - cellid VARCHAR(256) PRIMARY KEY, - celltype VARCHAR(256) NOT NULL, - FOREIGN KEY (cellid) REFERENCES cell (topiaid) -); - --- --- Trigger qui met à jour la table des traitements spatiaux à faire --- - -CREATE OR REPLACE FUNCTION echobase_fill_cell_spatial_temp_table() - RETURNS TRIGGER AS $$ -DECLARE - cell_id VARCHAR; - data_type_id VARCHAR; - cellType VARCHAR; - dataType VARCHAR; - doInsert BOOLEAN; - result RECORD; -BEGIN - - IF (TG_OP = 'DELETE') - THEN - result = OLD; - ELSE - result = NEW; - END IF; - - cell_id = result.cell; - data_type_id = result.datametadata; - doInsert = FALSE; --- recuperation du type de la cellule - cellType = echobase_get_cell_type(cell_id); - - IF (SELECT - count(*) - FROM echobase_cell_spatial_temp c - WHERE c.cellid = cell_id) > 0 - THEN --- la cellule est deja a traitee - RETURN result; - END IF; --- recuperation du type de la la data - SELECT - dt.name - INTO dataType - FROM datametadata dt - WHERE dt.topiaId = data_type_id; - - CASE cellType - WHEN 'ESDU' - THEN --- Cell of type Esdu - CASE dataType - WHEN 'LatitudeStart', 'LatitudeBary', 'LatitudeEnd', 'LongitudeStart', 'LongitudeBary', 'LongitudeEnd' - THEN - doInsert = TRUE; - ELSE - doInsert = FALSE; - END CASE; - WHEN 'ELEMENTARY' - THEN --- Cell of type Elementary - CASE dataType - WHEN 'LatitudeStart', 'LatitudeBary', 'LatitudeEnd', 'LongitudeStart', 'LongitudeBary', 'LongitudeEnd', 'DepthRefSurfaceStart', 'DepthRefSurfaceEnd', 'DepthRefBottomStart', 'DepthRefBottomEnd' - THEN - doInsert = TRUE; - ELSE - doInsert = FALSE; - END CASE; - WHEN 'REGION', 'REGIONSURF', 'REGIONCLAS' - THEN --- Cell of type Region - CASE dataType - WHEN 'RegionEnvCoordinates' - THEN - doInsert = TRUE; - cellType = 'REGION'; - ELSE - doInsert = FALSE; - END CASE; - WHEN 'MAP' - THEN --- Cell of type Map - CASE dataType - WHEN 'GridCellLatitude', 'GridCellLongitude', 'GridLatitudeLag', 'GridLongitudeLag' - THEN - doInsert = TRUE; - ELSE - doInsert = FALSE; - END CASE; - END CASE; - - IF doInsert = TRUE - THEN ---- Ajout de la cellule dans la table des traitements à effectuer - RAISE DEBUG 'Add cell % [type %] to echobase_cell_spatial_temp', cell_id, cellType; - INSERT INTO echobase_cell_spatial_temp (cellid, celltype) VALUES (cell_id, cellType); - END IF; - - RETURN result; -END -$$ -LANGUAGE 'plpgsql'; - -CREATE TRIGGER echobase_fill_cell_spatial_work_table_trigger -AFTER INSERT OR UPDATE ON data -FOR EACH ROW WHEN (NEW.cell IS NOT - NULL) EXECUTE PROCEDURE echobase_fill_cell_spatial_temp_table(); - --- --- Trigger qui met à jour la table des traitements spatiaux à faire lors d --- 'une suppression de cellule --- - -CREATE OR REPLACE FUNCTION echobase_delete_cell() - RETURNS TRIGGER AS $$ -BEGIN - - RAISE DEBUG 'Delete cell % , delete cascade in echobase_cell_spatial_table', OLD.topiaid; - - DELETE FROM echobase_cell_spatial WHERE cellid = OLD.topiaid; - - RETURN OLD; -END -$$ -LANGUAGE 'plpgsql'; - -CREATE TRIGGER echobase_delete_cell -BEFORE DELETE ON cell -FOR EACH ROW EXECUTE PROCEDURE echobase_delete_cell(); - --- --- Mettre a jour toutes les données spatiales de cellule --- - -CREATE OR REPLACE FUNCTION echobase_compute_all_cell_spatial_data() - RETURNS VOID AS $$ -DECLARE cell_id VARCHAR; -BEGIN - DELETE FROM echobase_cell_spatial; - - FOR cell_id IN SELECT topiaid FROM cell LOOP - PERFORM echobase_fill_cell_spatial_row(cell_id); - END LOOP; -END -$$ -LANGUAGE plpgsql; - --- --- Mettre a jour la table echobase_cell_spatial depuis echobase_cell_spatial_temp --- - -CREATE OR REPLACE FUNCTION echobase_fill_cell_spatial_table() - RETURNS VOID AS $$ -DECLARE - cellRow RECORD; -BEGIN - FOR cellRow IN SELECT - * - FROM echobase_cell_spatial_temp LOOP - - PERFORM echobase_fill_cell_spatial_row(cellRow.cellid); - - END LOOP; - - DELETE FROM echobase_cell_spatial_temp; - -END -$$ -LANGUAGE plpgsql; - -CREATE OR REPLACE FUNCTION echobase_fill_cell_spatial_row(cell_id VARCHAR) - RETURNS VOID AS $$ - DECLARE cell_type VARCHAR; -BEGIN - - cell_type = echobase_get_cell_type(cell_id); - - RAISE DEBUG 'Treat spatial cell % [type %] ...', cell_id, cell_type; - CASE cell_type - WHEN 'ESDU' - THEN - EXECUTE echobase_fill_esdu_cell_spatial_table(cell_id); - WHEN 'ELEMENTARY' - THEN - EXECUTE echobase_fill_elementary_cell_spatial_table(cell_id); - WHEN 'REGION' - THEN - EXECUTE echobase_fill_region_cell_spatial_table(cell_id); - WHEN 'MAP' - THEN - EXECUTE echobase_fill_map_cell_spatial_table(cell_id); - ELSE - RAISE LOG 'Can not deal with celltype % (cell %)', cell_type, cell_id; - END CASE; -END -$$ -LANGUAGE plpgsql; - -CREATE OR REPLACE FUNCTION echobase_fill_esdu_cell_spatial_table( - cell_id VARCHAR) - RETURNS VOID AS $$ -DECLARE - latitude VARCHAR; - longitude VARCHAR; - depth RECORD; - spatialText VARCHAR; - latitudeNumber REAL; - longitudeNumber REAL; -BEGIN - RAISE DEBUG 'Treat esdu cell % ', cell_id; --- test if start / bary / end event - SELECT - d.datavalue - INTO latitude - FROM data d LEFT OUTER JOIN datametadata AS dm - ON d.datametadata = dm.topiaid - WHERE d.cell = cell_id AND dm.name = 'LatitudeStart'; - IF FOUND - THEN --- start data - RAISE DEBUG 'Treat esdu Start cell % ', cell_id; - SELECT - d.datavalue - INTO longitude - FROM data d LEFT OUTER JOIN datametadata AS dm - ON d.datametadata = dm.topiaid - WHERE d.cell = cell_id AND dm.name = 'LongitudeStart'; - ELSE - SELECT - d.datavalue - INTO latitude - FROM data d LEFT OUTER JOIN datametadata AS dm - ON d.datametadata = dm.topiaid - WHERE d.cell = cell_id AND dm.name = 'LatitudeBary'; - IF FOUND - THEN --- bary data - RAISE DEBUG 'Treat esdu Bary cell %', cell_id; - SELECT - d.datavalue - INTO longitude - FROM data d LEFT OUTER JOIN datametadata AS dm - ON d.datametadata = dm.topiaid - WHERE d.cell = cell_id AND dm.name = 'LongitudeBary'; - ELSE - SELECT - d.datavalue - INTO latitude - FROM data d LEFT OUTER JOIN datametadata AS dm - ON d.datametadata = dm.topiaid - WHERE d.cell = cell_id AND dm.name = 'LatitudeEnd'; - IF FOUND - THEN --- end data - RAISE DEBUG 'Treat End cell %', cell_id; - SELECT - d.datavalue - INTO longitude - FROM data d LEFT OUTER JOIN datametadata AS dm - ON d.datametadata = dm.topiaid - WHERE d.cell = cell_id AND dm.name = 'LongitudeEnd'; - ELSE --- no spatial data - RAISE LOG 'Could not find spatial data for cell %', cell_id; - RETURN; - END IF; - END IF; - END IF; - --- convert dms latitude to dd latitude - SELECT - echobase_latitudetext_to_dd(latitude) - INTO latitudeNumber; - - IF latitude IS NOT NULL AND latitudeNumber IS NULL - THEN - latitudeNumber := latitude :: REAL; - END IF; - --- convert dms longitude to dd longitude - SELECT - echobase_longitudetext_to_dd(longitude) - INTO longitudeNumber; - - IF longitude IS NOT NULL AND longitudeNumber IS NULL - THEN - longitudeNumber := longitude :: REAL; - END IF; - - SELECT - 'SRID=4326;POINT(' || longitudeNumber || ' ' || latitudeNumber || ')' - INTO spatialText; - RAISE DEBUG 'spatial data % for cell %', spatialText, cell_id; - - PERFORM echobase_create_echobase_cell_spatial_row(cell_id, - spatialText, - NULL, - NULL); -END -$$ LANGUAGE plpgsql; - -CREATE OR REPLACE FUNCTION echobase_fill_elementary_cell_spatial_table( - cell_id VARCHAR) - RETURNS VOID AS $$ -DECLARE - latitude VARCHAR; - longitude VARCHAR; - depth VARCHAR; - spatialText VARCHAR; - latitudeNumber REAL; - longitudeNumber REAL; -BEGIN - RAISE DEBUG 'Treat elementary cell % ', cell_id; --- try start elementary - SELECT - d.datavalue - INTO latitude - FROM data d LEFT OUTER JOIN datametadata AS dm - ON d.datametadata = dm.topiaid - WHERE d.cell = cell_id AND dm.name = 'LatitudeStart'; - IF FOUND - THEN --- this is a start elementary - RAISE DEBUG 'Treat elementary Start cell % ', cell_id; --- get longitude - SELECT - d.datavalue - INTO longitude - FROM data d LEFT OUTER JOIN datametadata AS dm - ON d.datametadata = dm.topiaid - WHERE d.cell = cell_id AND dm.name = 'LongitudeStart'; --- get depth (try first surface one) - SELECT - d.datavalue - INTO depth - FROM data d LEFT OUTER JOIN datametadata AS dm - ON d.datametadata = dm.topiaid - WHERE d.cell = cell_id AND dm.name = 'DepthRefSurfaceStart'; - IF NOT FOUND - THEN --- try then bottom - SELECT - d.datavalue - INTO depth - FROM data d LEFT OUTER JOIN datametadata AS dm - ON d.datametadata = dm.topiaid - WHERE d.cell = cell_id AND dm.name = 'DepthRefBottomStart'; - END IF; - ELSE --- try bary elementary - SELECT - d.datavalue - INTO latitude - FROM data d LEFT OUTER JOIN datametadata AS dm - ON d.datametadata = dm.topiaid - WHERE d.cell = cell_id AND dm.name = 'LatitudeBary'; - IF FOUND - THEN --- this is a bary elementary - RAISE DEBUG 'Treat elementary Bary cell %', cell_id; - SELECT - d.datavalue - INTO longitude - FROM data d LEFT OUTER JOIN datametadata AS dm - ON d.datametadata = dm.topiaid - WHERE d.cell = cell_id AND dm.name = 'LongitudeBary'; - SELECT - d.datavalue - INTO depth - FROM data d LEFT OUTER JOIN datametadata AS dm - ON d.datametadata = dm.topiaid - WHERE d.cell = cell_id AND dm.name = 'DepthRefSurfaceBary'; - IF NOT FOUND - THEN --- use depth bottom - SELECT - d.datavalue - INTO depth - FROM data d LEFT OUTER JOIN datametadata AS dm - ON d.datametadata = dm.topiaid - WHERE d.cell = cell_id AND dm.name = 'DepthRefBottomBary'; - END IF; - ELSE --- try end elementary - SELECT - d.datavalue - INTO latitude - FROM data d LEFT OUTER JOIN datametadata AS dm - ON d.datametadata = dm.topiaid - WHERE d.cell = cell_id AND dm.name = 'LatitudeEnd'; - IF FOUND - THEN --- this is a end elementary - RAISE DEBUG 'Treat elementary End cell %', cell_id; - SELECT - d.datavalue - INTO longitude - FROM data d LEFT OUTER JOIN datametadata AS dm - ON d.datametadata = dm.topiaid - WHERE d.cell = cell_id AND dm.name = 'LongitudeEnd'; - SELECT - d.datavalue - INTO depth - FROM data d LEFT OUTER JOIN datametadata AS dm - ON d.datametadata = dm.topiaid - WHERE d.cell = cell_id AND dm.name = 'DepthRefSurfaceEnd'; - IF NOT FOUND - THEN --- use depth bottom - SELECT - d.datavalue - INTO depth - FROM data d LEFT OUTER JOIN datametadata AS dm - ON d.datametadata = dm.topiaid - WHERE d.cell = cell_id AND dm.name = 'DepthRefBottomEnd'; - END IF; - ELSE --- no spatial data - RAISE DEBUG 'Could not find spatial data for cell %', cell_id; - RETURN; - END IF; - END IF; - END IF; - --- convert dms latitude to dd latitude - SELECT - echobase_latitudetext_to_dd(latitude) - INTO latitudeNumber; - - IF latitude IS NOT NULL AND latitudeNumber IS NULL - THEN - latitudeNumber := latitude :: REAL; - END IF; - --- convert dms longitude to dd longitude - SELECT - echobase_longitudetext_to_dd(longitude) - INTO longitudeNumber; - - IF longitude IS NOT NULL AND longitudeNumber IS NULL - THEN - longitudeNumber := longitude :: REAL; - END IF; - - SELECT - 'SRID=4326;POINT(' || longitudeNumber || ' ' || latitudeNumber || ' ' || - depth || ')' - INTO spatialText; - RAISE DEBUG 'spatial data % for cell %', spatialText, cell_id; - - PERFORM echobase_create_echobase_cell_spatial_row(cell_id, - NULL, - spatialText, - NULL); -END -$$ LANGUAGE plpgsql; - -CREATE OR REPLACE FUNCTION echobase_fill_region_cell_spatial_table(cell_id VARCHAR) - RETURNS VOID AS $$ -DECLARE - latitude REAL; - longitude REAL; - depth REAL; - dataMetadataId VARCHAR; - dataValue VARCHAR; - spatialText VARCHAR := 'POLYGON(('; -BEGIN - RAISE DEBUG 'Treat region cell %', cell_id; - SELECT - topiaid - INTO dataMetadataId - FROM datametadata - WHERE name = 'RegionEnvCoordinates'; - FOR dataValue IN SELECT - d.datavalue - FROM data d - WHERE d.cell = cell_id AND d.datametadata = dataMetadataId - ORDER BY d.topiacreatedate LOOP --- split dataValue in lat - long - depth - SELECT - split_part(dataValue, ' ', 1) :: REAL - INTO latitude; - SELECT - split_part(dataValue, ' ', 2) :: REAL - INTO longitude; - SELECT - split_part(dataValue, ' ', 3) :: REAL - INTO depth; - SELECT - spatialText || longitude || ' ' || latitude || ',' - INTO spatialText; - END LOOP; - SELECT - left(spatialText, -1) || '))' - INTO spatialText; - - SELECT 'SRID=4326;' || spatialText INTO spatialText; - - RAISE DEBUG 'spatial data % for cell %', spatialText, cell_id; - - PERFORM echobase_create_echobase_cell_spatial_row(cell_id, - NULL, - NULL, - spatialText); -END -$$ LANGUAGE plpgsql; - -CREATE OR REPLACE FUNCTION echobase_fill_map_cell_spatial_table(cell_id VARCHAR) - RETURNS VOID AS $$ -DECLARE - latitude REAL; - longitude REAL; - deltaLatitude REAL; - deltaLongitude REAL; - P0 VARCHAR; - P1 VARCHAR; - P2 VARCHAR; - P3 VARCHAR; - spatialText VARCHAR; -BEGIN - RAISE DEBUG 'Treat map cell % ', cell_id; --- get GridCellLatitude - SELECT - d.datavalue :: REAL - INTO latitude - FROM data d LEFT OUTER JOIN datametadata AS dm - ON d.datametadata = dm.topiaid - WHERE d.cell = cell_id AND dm.name = 'GridCellLatitude'; --- get GridCellLatitude - SELECT - d.datavalue :: REAL - INTO longitude - FROM data d LEFT OUTER JOIN datametadata AS dm - ON d.datametadata = dm.topiaid - WHERE d.cell = cell_id AND dm.name = 'GridCellLongitude'; --- get GridCellLatitude - SELECT - d.datavalue :: REAL - INTO deltaLatitude - FROM data d LEFT OUTER JOIN datametadata AS dm - ON d.datametadata = dm.topiaid - WHERE d.cell = cell_id AND dm.name = 'GridLatitudeLag'; --- get GridCellLatitude - SELECT - d.datavalue :: REAL - INTO deltaLongitude - FROM data d LEFT OUTER JOIN datametadata AS dm - ON d.datametadata = dm.topiaid - WHERE d.cell = cell_id AND dm.name = 'GridLongitudeLag'; - - IF - latitude IS NOT NULL AND longitude IS NOT NULL AND deltaLatitude IS NOT NULL - AND deltaLongitude IS NOT NULL - THEN - SELECT - longitude || ' ' || latitude - INTO P0; - SELECT - longitude || ' ' || latitude + deltaLatitude - INTO P1; - SELECT - longitude + deltaLongitude || ' ' || latitude + deltaLatitude - INTO P2; - SELECT - longitude + deltaLongitude || ' ' || latitude - INTO P3; - - SELECT - 'SRID=4326;POLYGON((' || p0 || ',' || P1 || ',' || P2 || ',' || P3 || ',' || P0 || - '))' - INTO spatialText; - RAISE DEBUG 'spatial data % for cell %', spatialText, cell_id; - - PERFORM echobase_create_echobase_cell_spatial_row(cell_id, - NULL, - NULL, - spatialText); - END IF; -END -$$ LANGUAGE plpgsql; - --- procedure pour creer (ou mettre à jour) une ligne dans echobase_cell_spatial - -CREATE OR REPLACE FUNCTION echobase_create_echobase_cell_spatial_row( - cell_id VARCHAR, - coordinateText VARCHAR, - coordinate3dText VARCHAR, - shapeText VARCHAR) - RETURNS VOID AS $$ -DECLARE - cellSpatialRow RECORD; - voyageId VARCHAR; - voyageName VARCHAR; - transitId VARCHAR; - transitName VARCHAR; - transectId VARCHAR; - transectName VARCHAR; - dataAcquisitionId VARCHAR; - dataAcquisitionName VARCHAR; - dataProcessingId VARCHAR; - dataProcessingName VARCHAR; - cellName VARCHAR; - cellTypeId VARCHAR; - cellTypeName VARCHAR; - cellParentId VARCHAR; - cellRow RECORD; - coordinateData GEOMETRY; - coordinate3dData GEOMETRY; - shapeData GEOMETRY; -BEGIN - IF coordinateText IS NULL AND coordinate3dText IS NULL AND - shapeText IS NULL - THEN - RAISE DEBUG 'Could not find spatial data for cell %', cell_id; - RETURN; - END IF; - IF coordinateText IS NOT NULL - THEN - BEGIN - coordinateData = ST_GeomFromEWKT(coordinateText); - EXCEPTION WHEN internal_error - THEN - - RAISE LOG 'Could not create coordinate spatial data %', coordinateText; - RETURN; - END; - ELSEIF coordinate3dText IS NOT NULL - THEN - BEGIN - coordinate3dData = ST_GeomFromEWKT(coordinate3dText); - EXCEPTION WHEN internal_error - THEN - - RAISE LOG 'Could not create coordinate3D spatial data %', coordinate3dText; - RETURN; - END; - ELSEIF shapeText IS NOT NULL - THEN - BEGIN - shapeData = ST_GeomFromEWKT(shapeText); - EXCEPTION WHEN internal_error - THEN - - RAISE LOG 'Could not create shape spatial data %', shapeText; - RETURN; - END; - END IF; - - SELECT - * - INTO cellSpatialRow - FROM echobase_cell_spatial cs - WHERE cs.cellid = cell_id; - IF NOT FOUND - THEN --- create row - RAISE DEBUG 'Will create spatial cell %', cell_id; - SELECT - cell_id - INTO cellParentId; - LOOP - IF dataProcessingId IS NULL - THEN --- try to get dataprocessingId from this cell - SELECT - dp.topiaid, - dp.processingdescription - INTO dataProcessingId - FROM dataprocessing dp, cell c - WHERE c.topiaid = cellParentId AND dp.topiaid = c.dataprocessing; - END IF; - SELECT - topiaid, - cell - INTO cellRow - FROM cell - WHERE topiaid = cellParentId; - EXIT WHEN cellRow.cell IS NULL; - SELECT - cellRow.cell - INTO cellParentId; - END LOOP; - RAISE DEBUG 'use cell parentId %', cellParentId; --- get cell infos - SELECT - c.name, - ct.name, - ct.topiaid - INTO cellName, cellTypeName, cellTypeId - FROM cell c, celltype ct - WHERE c.topiaid = cell_id AND c.celltype = ct.topiaid; - - IF dataProcessingId IS NULL - THEN --- get voyage infos - SELECT - v.topiaid, - v.name - INTO voyageId, voyageName - FROM voyage v, cell c - WHERE c.topiaid = cellParentId AND v.topiaid = c.voyage; - ELSE - --- get dataAcquisition infos - SELECT - da.topiaid, - da.acousticinstrument - INTO dataAcquisitionId, dataAcquisitionName - FROM dataacquisition da, dataprocessing dp - WHERE dp.topiaId = dataProcessingId AND da.topiaid = dp.dataacquisition; --- get transect infos - SELECT - t.topiaid, - t.vessel - INTO transectId, transitName - FROM transect t, dataacquisition da - WHERE da.topiaid = dataAcquisitionId AND t.topiaid = da.transect; --- get transit infos - SELECT - t.topiaid, - (t.starttime || ' - ' || t.endtime) - INTO transitId, transitName - FROM transit t, transect tt - WHERE tt.topiaid = transectId AND t.topiaid = tt.transit; --- get voyage infos - SELECT - v.topiaid, - v.name - INTO voyageId, voyageName - FROM voyage v, transit t - WHERE t.topiaid = transitId AND v.topiaid = t.voyage; - END IF; - INSERT INTO echobase_cell_spatial (lastUpdateDate, voyageid, voyagename, transitid, transitname, transectid, transectname, dataacquisitionid, dataacquisitionname, dataprocessingid, dataprocessingname, celltypeid, celltypename, cellid, cellname, coordinate, coordinate3d, shape) - VALUES (now(), voyageId, voyageName, transitId, transitName, transectId, transectName, dataAcquisitionId, dataAcquisitionName, dataProcessingId, dataProcessingName, cellTypeId, cellTypeName, cell_id, cellName, coordinateData, coordinate3dData, shapeData); - ELSE --- update row - RAISE DEBUG 'Will update spatial cell % ', cell_id; - UPDATE echobase_cell_spatial - SET coordinate = coordinateData, - coordinate3d = coordinate3dData, - shape = shapeData - WHERE cellid = cell_id; - END IF; -END -$$ LANGUAGE plpgsql; - --- pour obtenir le type d'une cellule sous forme ESDU - ELEMENTARY - REGION ou MAP -CREATE OR REPLACE FUNCTION echobase_get_cell_type(cell_id VARCHAR) - RETURNS VARCHAR AS $$ -DECLARE result VARCHAR; -BEGIN - SELECT - UPPER(ct.id) - INTO result - FROM celltype ct, cell ce - WHERE ce.topiaid = cell_id AND ct.topiaId = ce.celltype; - IF 'REGIONCLAS' = result OR 'REGIONSURF' = result - THEN - result = 'REGION'; - END IF; - RETURN result; -END -$$ -LANGUAGE plpgsql; - --------------------------------------------------------------------------------- --- Table spatial pour les operations ------------------------------------------- --------------------------------------------------------------------------------- - -CREATE TABLE echobase_operation_spatial ( - voyageid VARCHAR(256) NOT NULL, - voyagename VARCHAR(256), - transitid VARCHAR(256), - transitname VARCHAR(256), - transectid VARCHAR(256), - transectname VARCHAR(256), - operationid VARCHAR(256) PRIMARY KEY, - operationname VARCHAR(256) NOT NULL, - lastUpdateDate TIMESTAMP NOT NULL, - FOREIGN KEY (operationid) REFERENCES operation (topiaid), - FOREIGN KEY (transectid) REFERENCES transect (topiaid), - FOREIGN KEY (transitid) REFERENCES transit (topiaid), - FOREIGN KEY (voyageid) REFERENCES voyage (topiaid) -); - -SELECT AddGeometryColumn('echobase_operation_spatial', 'coordinate', 4326, 'POINT',2 ); - -CREATE INDEX echobase_operation_spatial_coordinate_gix ON echobase_operation_spatial USING GIST (coordinate); -CREATE INDEX echobase_operation_spatial_voyageid_idx ON echobase_operation_spatial USING BTREE(voyageid); - --- pour stoquer les traitements spatiaux a effectuer - -CREATE TABLE echobase_operation_spatial_temp ( - operationid VARCHAR(256) PRIMARY KEY, - FOREIGN KEY (operationid) REFERENCES operation (topiaid) -); - --- --- Trigger qui met à jour la table des traitements spatiaux à faire --- - -CREATE OR REPLACE FUNCTION echobase_fill_operation_spatial_temp_table() - RETURNS TRIGGER AS $$ -DECLARE - operation_id VARCHAR; - result RECORD; -BEGIN - - IF (TG_OP = 'DELETE') - THEN - result = OLD; - ELSE - result = NEW; - END IF; - - operation_id = result.topiaid; - - IF (SELECT count(*) FROM echobase_operation_spatial_temp c - WHERE c.operationid = operation_id) > 0 - THEN --- operation est deja a traitee - RETURN result; - END IF; ---- Ajout de l'operation dans la table des traitements à effectuer - RAISE DEBUG 'Add operation % to echobase_operation_spatial_temp', operation_id; - INSERT INTO echobase_operation_spatial_temp (operationid) VALUES (operation_id); - RETURN result; -END -$$ -LANGUAGE 'plpgsql'; - -CREATE TRIGGER echobase_fill_operation_spatial_work_table_trigger -AFTER INSERT OR UPDATE ON operation -FOR EACH ROW WHEN (NEW.topiaid IS NOT - NULL) EXECUTE PROCEDURE echobase_fill_operation_spatial_temp_table(); - --- --- Trigger qui met à jour la table des traitements spatiaux à faire lors de la --- suppression d'une operation --- - -CREATE OR REPLACE FUNCTION echobase_delete_operation() - RETURNS TRIGGER AS $$ -BEGIN - - RAISE DEBUG 'Delete operation % , delete cascade in echobase_operation_spatial_table', OLD.topiaid; - - DELETE FROM echobase_operation_spatial WHERE operationid = OLD.topiaid; - - RETURN OLD; -END -$$ -LANGUAGE 'plpgsql'; - -CREATE TRIGGER echobase_delete_operation -BEFORE DELETE ON operation -FOR EACH ROW EXECUTE PROCEDURE echobase_delete_operation(); - --- --- Mettre a jour toutes les données spatiales d'operation --- - -CREATE OR REPLACE FUNCTION echobase_compute_all_operation_spatial_data() - RETURNS VOID AS $$ -DECLARE operation_id VARCHAR; -BEGIN - DELETE FROM echobase_operation_spatial; - - FOR operation_id IN SELECT topiaid FROM operation LOOP - PERFORM echobase_fill_operation_spatial_row(operation_id); - END LOOP; -END -$$ -LANGUAGE plpgsql; - --- --- Mettre a jour la table echobase_cell_spatial depuis echobase_operation_spatial_temp --- - -CREATE OR REPLACE FUNCTION echobase_fill_operation_spatial_table() - RETURNS VOID AS $$ -DECLARE - operationRow RECORD; -BEGIN - FOR operationRow IN SELECT * FROM echobase_operation_spatial_temp LOOP - - PERFORM echobase_fill_operation_spatial_row(operationRow.operationid); - END LOOP; - - DELETE FROM echobase_operation_spatial_temp; - -END -$$ -LANGUAGE plpgsql; - -CREATE OR REPLACE FUNCTION echobase_fill_operation_spatial_row(operation_id VARCHAR) - RETURNS VOID AS $$ - DECLARE - spatialText VARCHAR; - latitudeNumber REAL; - longitudeNumber REAL; -BEGIN - RAISE NOTICE 'Treat spatial operation % ...', operation_id; - SELECT o.midHaulLatitude, o.midHaulLongitude - INTO latitudeNumber, longitudeNumber FROM operation o - WHERE o.topiaid = operation_id; - - SELECT - 'SRID=4326;POINT(' || longitudeNumber || ' ' || latitudeNumber || ')' - INTO spatialText; - RAISE DEBUG 'spatial data % for operation %', spatialText, operation_id; - - PERFORM echobase_create_echobase_operation_spatial_row(operation_id, - spatialText); -END -$$ -LANGUAGE plpgsql; - --- procedure pour creer (ou mettre à jour) une ligne dans echobase_cell_spatial - -CREATE OR REPLACE FUNCTION echobase_create_echobase_operation_spatial_row( - operation_id VARCHAR, - coordinateText VARCHAR) - RETURNS VOID AS $$ -DECLARE - operationSpatialRow RECORD; - voyageId VARCHAR; - voyageName VARCHAR; - transitId VARCHAR; - transitName VARCHAR; - transectId VARCHAR; - transectName VARCHAR; - operationId VARCHAR; - operationName VARCHAR; - operationRow RECORD; - coordinateData GEOMETRY; -BEGIN - IF coordinateText IS NULL - THEN - RAISE DEBUG 'Could not find spatial data for operation %', operation_id; - RETURN; - END IF; - BEGIN - coordinateData = ST_GeomFromEWKT(coordinateText); - EXCEPTION WHEN internal_error - THEN - RAISE LOG 'Could not create coordinate operation spatial data %', coordinateText; - RETURN; - END; - - SELECT * INTO operationSpatialRow FROM echobase_operation_spatial os - WHERE os.operationid = operation_id; - IF NOT FOUND - THEN --- create row - RAISE DEBUG 'Will create spatial operation %', operation_id; --- get operation infos - SELECT op.id, op.topiaid - INTO operationName, operationId - FROM operation op - WHERE op.topiaid = operation_id; --- get transect infos - SELECT - t.topiaid, - t.vessel - INTO transectId, transitName - FROM transect t, operation op - WHERE op.topiaid = operation_id AND t.topiaid = op.transect; --- get transit infos - SELECT - t.topiaid, - (t.starttime || ' - ' || t.endtime) - INTO transitId, transitName - FROM transit t, transect tt - WHERE tt.topiaid = transectId AND t.topiaid = tt.transit; --- get voyage infos - SELECT - v.topiaid, - v.name - INTO voyageId, voyageName - FROM voyage v, transit t - WHERE t.topiaid = transitId AND v.topiaid = t.voyage; - - INSERT INTO echobase_operation_spatial (lastUpdateDate, voyageid, voyagename, transitid, transitname, transectid, transectname, operationid, operationname, coordinate) - VALUES (now(), voyageId, voyageName, transitId, transitName, transectId, transectName, operationId, operationName, coordinateData); - ELSE --- update row - RAISE DEBUG 'Will update spatial operation % ', operation_id; - UPDATE echobase_operation_spatial eos - SET eos.coordinate = coordinateData - WHERE eos.operationid = operation_id; - END IF; -END -$$ LANGUAGE plpgsql; - --------------------------------------------------------------------------------- --------------------------------------------------------------------------------- --------------------------------------------------------------------------------- - --- pour convertir des latitudes en dms (+ hemi) en dd -CREATE OR REPLACE FUNCTION echobase_latitudetext_to_dd(latitude VARCHAR) - RETURNS REAL AS $$ -DECLARE - degre_ INTEGER; - minute_ INTEGER; - second_ INTEGER; - hemi_ VARCHAR(1); -BEGIN - SELECT - right(latitude, 1), - left(latitude, 2) :: INTEGER, - substring(latitude FROM 3 FOR 2) :: INTEGER, - substring(latitude FROM 6 FOR 3) :: INTEGER - INTO hemi_, degre_, minute_, second_; - - RETURN echobase_dms2dd(degre_, minute_, second_, hemi_); - EXCEPTION WHEN invalid_text_representation - THEN - RETURN NULL; -END -$$ LANGUAGE plpgsql; - --- pour convertir des longitudes en dms (+ hemi) en dd -CREATE OR REPLACE FUNCTION echobase_longitudetext_to_dd(longitude VARCHAR) - RETURNS REAL AS $$ -DECLARE - degre_ INTEGER; - minute_ INTEGER; - second_ INTEGER; - hemi_ VARCHAR(1); -BEGIN - SELECT - right(longitude, 1), - left(longitude, 3) :: INTEGER, - substring(longitude FROM 4 FOR 2) :: INTEGER, - substring(longitude FROM 7 FOR 3) :: INTEGER - INTO hemi_, degre_, minute_, second_; - - RETURN echobase_dms2dd(degre_, minute_, second_, hemi_); - EXCEPTION WHEN invalid_text_representation - THEN - RETURN NULL; -END -$$ LANGUAGE plpgsql; - -CREATE OR REPLACE FUNCTION echobase_dms2dd( - D INTEGER, M INTEGER, S INTEGER, HEMI CHARACTER VARYING(1) -) - RETURNS DOUBLE PRECISION AS $$ -DECLARE - ret DOUBLE PRECISION; - dir INTEGER; -BEGIN - dir := 1; ---init to 1 for default positive return - ret := 0; ---init to zero. - --ONLY S or W will trip this. Any other letter or NULL will result in positive return value - IF UPPER(HEMI) = 'S' OR UPPER(HEMI) = 'W' - THEN - dir := -1; --then southern or western hemisphere - END IF; ---SOME data has negative values in minutes and seconds as well as degrees. Use ABS to standardize all three. - ret := (ABS(CAST(D AS DOUBLE PRECISION)) + (ABS((CAST(M AS DOUBLE PRECISION) + - (ABS((CAST(S AS - DOUBLE PRECISION)) - / 60))) / 60))); - ret := ret * dir; - RETURN ret; - -END; -$$ LANGUAGE plpgsql; - --------------------------------------------------------------------------------- --------------------------------------------------------------------------------- --------------------------------------------------------------------------------- - --- --- Mettre a jour toutes les données spatiales --- - -CREATE OR REPLACE FUNCTION echobase_compute_all_spatial_data() - RETURNS VOID AS $$ -DECLARE cell_id VARCHAR; -BEGIN - EXECUTE echobase_compute_all_cell_spatial_data(); - EXECUTE echobase_compute_all_operation_spatial_data(); -END -$$ -LANGUAGE plpgsql; - - --- Compute all data -SELECT echobase_compute_all_cell_spatial_data(); -SELECT echobase_compute_all_operation_spatial_data(); \ No newline at end of file Deleted: trunk/echobase-services/src/main/resources/postgis-view.sql =================================================================== --- trunk/echobase-services/src/main/resources/postgis-view.sql 2014-02-27 20:41:19 UTC (rev 962) +++ trunk/echobase-services/src/main/resources/postgis-view.sql 2014-02-28 14:08:22 UTC (rev 963) @@ -1,476 +0,0 @@ ---- --- #%L --- EchoBase :: Services --- $Id$ --- $HeadURL$ --- %% --- Copyright (C) 2011 - 2013 Ifremer, Codelutin --- %% --- This program is free software: you can redistribute it and/or modify --- it under the terms of the GNU Affero General Public License as published by --- the Free Software Foundation, either version 3 of the License, or --- (at your option) any later version. --- --- This program is distributed in the hope that it will be useful, --- but WITHOUT ANY WARRANTY; without even the implied warranty of --- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the --- GNU General Public License for more details. --- --- You should have received a copy of the GNU Affero General Public License --- along with this program. If not, see <http://www.gnu.org/licenses/>. --- #L% ---- - ------------------------------------------------------------------------------------------------------------------------ ----- DROP EXISTING VIEW - INDEX - FUNCTION - TRIGGER ------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------ - -DROP VIEW IF EXISTS echobase_cell_spatial_result CASCADE; -DROP VIEW IF EXISTS cellmapview CASCADE; - -DROP VIEW IF EXISTS echobase_cell_spatial_data CASCADE ; -DROP VIEW IF EXISTS meanMapcellBiomassEngrEnc CASCADE; -DROP VIEW IF EXISTS meanMapcellBiomassSardPil CASCADE; -DROP VIEW IF EXISTS meanMapcellBiomassTracTru CASCADE; -DROP VIEW IF EXISTS meanMapcellBiomassSpraSpr CASCADE; -DROP VIEW IF EXISTS meanMapcellBiomassScomSco CASCADE; -DROP VIEW IF EXISTS meanMapcellBiomassMicrPou CASCADE; -DROP VIEW IF EXISTS TotalCatchSpOpWide CASCADE; -DROP VIEW IF EXISTS TotalSampleEngrEnc CASCADE; -DROP VIEW IF EXISTS TotalSampleSardPil CASCADE; -DROP VIEW IF EXISTS TotalSampleTracTru CASCADE; -DROP VIEW IF EXISTS TotalSampleSpraSpr CASCADE; -DROP VIEW IF EXISTS TotalSampleScomSco CASCADE; -DROP VIEW IF EXISTS TotalSampleMicrPou CASCADE; - -DROP INDEX IF EXISTS cellEsduViewSpeciesBiomass_uidx CASCADE; -DROP INDEX IF EXISTS cellEsduViewSpeciesAbundance_uidx CASCADE; -DROP INDEX IF EXISTS cellEsduViewSpeciesMeanLength_uidx CASCADE; -DROP INDEX IF EXISTS cellEsduViewEchotypeNasc_uidx CASCADE; -DROP INDEX IF EXISTS cellEsduViewEchotype_idx CASCADE; -DROP INDEX IF EXISTS cellEsduViewSpecies_idx CASCADE; -DROP INDEX IF EXISTS cellEsduViewSpeciesResultGrouped_idx CASCADE; -DROP INDEX IF EXISTS cellmapview_idx CASCADE; -DROP INDEX IF EXISTS cellmapview_uidx CASCADE; -DROP INDEX IF EXISTS TotalSampleView_idx CASCADE; - -DROP MATERIALIZED VIEW IF EXISTS cellEsduViewEchotypeNasc CASCADE; -DROP MATERIALIZED VIEW IF EXISTS cellEsduViewSpeciesBiomass CASCADE; -DROP MATERIALIZED VIEW IF EXISTS cellEsduViewSpeciesAbundance CASCADE; -DROP MATERIALIZED VIEW IF EXISTS cellEsduViewSpeciesMeanLength CASCADE; -DROP MATERIALIZED VIEW IF EXISTS cellEsduViewEchotype CASCADE; -DROP MATERIALIZED VIEW IF EXISTS cellEsduViewSpeciesResultGrouped CASCADE; -DROP MATERIALIZED VIEW IF EXISTS cellEsduViewSpecies CASCADE; -DROP MATERIALIZED VIEW IF EXISTS cellmapview CASCADE; -DROP MATERIALIZED VIEW IF EXISTS TotalSampleView CASCADE; - -DROP TRIGGER IF EXISTS echobase_refresh_views_trigger ON entitymodificationlog CASCADE; -DROP FUNCTION IF EXISTS echobase_to_numeric(string VARCHAR) CASCADE; -DROP FUNCTION IF EXISTS echobase_refresh_views() CASCADE; - ------------------------------------------------------------------------------------------------------------------------ ----- CREATE FUNCTION - TRIGGER ---------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------ - -CREATE OR REPLACE FUNCTION echobase_to_numeric(string VARCHAR) - RETURNS REAL AS $$ -BEGIN - RETURN string::real; - EXCEPTION WHEN invalid_text_representation - THEN - RETURN NULL; -END -$$ LANGUAGE plpgsql; - - -CREATE OR REPLACE FUNCTION echobase_refresh_views() - RETURNS TRIGGER AS $$ -DECLARE - operationtype VARCHAR; - result RECORD; -BEGIN - - IF (TG_OP = 'DELETE') - THEN - result = OLD; - ELSE - result = NEW; - END IF; - - operationtype = result.entitytype; - - IF (operationtype ILIKE '%Import%' OR operationtype ILIKE '%Removed%') - THEN - -- Can update views - RAISE NOTICE 'reload cellEsduViewEchotype'; - REFRESH MATERIALIZED VIEW cellEsduViewEchotype; - REINDEX INDEX cellEsduViewEchotype_idx; - - RAISE NOTICE 'reload cellEsduViewSpecies'; - REFRESH MATERIALIZED VIEW cellEsduViewSpecies; - REINDEX INDEX cellEsduViewSpecies_idx; - - RAISE NOTICE 'reload cellEsduViewSpeciesResultGrouped'; - REFRESH MATERIALIZED VIEW cellEsduViewSpeciesResultGrouped; - REINDEX INDEX cellEsduViewSpeciesResultGrouped_idx; - - RAISE NOTICE 'reload cellEsduViewEchotypeNasc'; - REFRESH MATERIALIZED VIEW cellEsduViewEchotypeNasc; - - RAISE NOTICE 'reload cellEsduViewSpeciesBiomass'; - REFRESH MATERIALIZED VIEW cellEsduViewSpeciesBiomass; - REINDEX INDEX cellEsduViewSpeciesBiomass_uidx; - - RAISE NOTICE 'reload cellEsduViewSpeciesAbundance'; - REFRESH MATERIALIZED VIEW cellEsduViewSpeciesAbundance; - REINDEX INDEX cellEsduViewSpeciesAbundance_uidx; - - RAISE NOTICE 'reload cellEsduViewSpeciesMeanLength'; - REFRESH MATERIALIZED VIEW cellEsduViewSpeciesMeanLength; - REINDEX INDEX cellEsduViewSpeciesMeanLength_uidx; - - RAISE NOTICE 'reload cellmapview'; - REFRESH MATERIALIZED VIEW cellmapview; - REINDEX INDEX cellmapview_idx; - REINDEX INDEX cellmapview_uidx; - END IF; - - RETURN result; -END -$$ -LANGUAGE 'plpgsql'; - -CREATE TRIGGER echobase_refresh_views_trigger -AFTER INSERT OR UPDATE ON entitymodificationlog -FOR EACH ROW EXECUTE PROCEDURE echobase_refresh_views(); - ------------------------------------------------------------------------------------------------------------------------ ----- CELL RESULT VIEW ------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------ - -CREATE MATERIALIZED VIEW cellEsduViewEchotype AS - SELECT - c.voyagename, - c.coordinate, - c.shape, - c.cellname, - d.name, - echobase_to_numeric(r.resultvalue) as resultvalue, - e.name as echotypeName, - e.meaning as echotypeMeaning, - r.topiaid AS resultid - FROM - echobase_cell_spatial c, - result r, - datametadata d, - category cat, - echotype e - WHERE - c.cellid = r.cell - AND r.datametadata = d.topiaid - AND r.category = cat.topiaid - AND cat.echotype IS NOT NULL - AND cat.echotype = e.topiaid - AND c.celltypename = 'Elementary Distance Sampling Unit'; - -CREATE INDEX cellEsduViewEchotype_idx ON cellEsduViewEchotype(name); - -CREATE MATERIALIZED VIEW cellEsduViewEchotypeNasc AS - SELECT - * - FROM - cellEsduViewEchotype v - WHERE - v.name = 'NASC'; - -CREATE UNIQUE INDEX cellEsduViewEchotypeNasc_uidx ON cellEsduViewEchotypeNasc(resultid); - -CREATE MATERIALIZED VIEW cellEsduViewSpecies AS - SELECT - c.voyagename, - c.coordinate, - c.shape, - c.cellname, - d.name, - echobase_to_numeric(r.resultvalue) as resultvalue, - s.baracoudacode, - r.topiaid AS resultid - FROM - echobase_cell_spatial c, - result r, - datametadata d, - category cat, - speciescategory scat, - species s - WHERE - c.cellid = r.cell - AND r.datametadata = d.topiaid - AND r.category = cat.topiaid - AND cat.speciescategory IS NOT NULL - AND cat.speciescategory = scat.topiaid - AND scat.species = s.topiaid - AND c.celltypename = 'Elementary Distance Sampling Unit'; - -CREATE INDEX cellEsduViewSpecies_idx ON cellEsduViewSpecies(name); - -CREATE MATERIALIZED VIEW cellEsduViewSpeciesResultGrouped AS - SELECT - count(*) AS nbResults, - name, - cellname, - baracoudacode, - sum(resultvalue) as resultvalue, - cellname || '-' || baracoudacode || '-' || name as id - FROM cellEsduViewSpecies - WHERE - baracoudacode in ('ENGR-ENC', 'SARD-PIL', 'TRAC-TRU', 'SPRA-SPR', 'SCOM-SCO', 'MICR-POU') - AND name in ('Biomass','Abundance','MeanLength') - GROUP BY baracoudacode, cellname, name; - -CREATE INDEX cellEsduViewSpeciesResultGrouped_idx ON cellEsduViewSpecies(baracoudacode, cellname, name); - -CREATE MATERIALIZED VIEW cellEsduViewSpeciesBiomass AS - SELECT - distinct v2.coordinate, - v2.voyagename, - v.* - FROM - cellEsduViewSpeciesResultGrouped v - JOIN cellEsduViewSpecies v2 USING (name, baracoudacode, cellname) - WHERE - v.name = 'Biomass'; - -CREATE UNIQUE INDEX cellEsduViewSpeciesBiomass_uidx ON cellEsduViewSpeciesBiomass(id); - -CREATE MATERIALIZED VIEW cellEsduViewSpeciesAbundance AS - SELECT - distinct v2.coordinate, - v2.voyagename, - v.* - FROM - cellEsduViewSpeciesResultGrouped v - JOIN cellEsduViewSpecies v2 USING (name, baracoudacode, cellname) - WHERE - v.name = 'Abundance'; - -CREATE UNIQUE INDEX cellEsduViewSpeciesAbundance_uidx ON cellEsduViewSpeciesAbundance(id); - -CREATE MATERIALIZED VIEW cellEsduViewSpeciesMeanLength AS - SELECT - distinct v2.coordinate, - v2.voyagename, - v.* - FROM - cellEsduViewSpeciesResultGrouped v - JOIN cellEsduViewSpecies v2 USING (name, baracoudacode, cellname) - WHERE - v.name = 'MeanLength'; - -CREATE UNIQUE INDEX cellEsduViewSpeciesMeanLength_uidx ON cellEsduViewSpeciesMeanLength(id); - ------------------------------------------------------------------------------------------------------------------------ ----- CELL DATA VIEW --------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------ - -CREATE OR REPLACE VIEW echobase_cell_spatial_data AS - SELECT - s.*, - m.topiaId AS metaDataId, - m.name AS metaDataName, - d.datavalue AS dataValue, - d.topiaid AS dataId - FROM - echobase_cell_spatial s, - data d, - datametadata m - WHERE - s.cellid = d.cell AND - d.datametadata = m.topiaid; - ------------------------------------------------------------------------------------------------------------------------ ----- MAP CELL VIEW ---------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------ - -CREATE MATERIALIZED VIEW cellmapview AS - SELECT - c.voyagename, - c.coordinate, - c.shape, - c.cellname, - d.name, - s.baracoudacode, - echobase_to_numeric(r.resultvalue) as resultvalue, - r.topiaid AS resultid - FROM - echobase_cell_spatial c, - result r, - datametadata d, - category cat, - speciescategory scat, - species s - WHERE - c.cellid = r.cell - AND r.datametadata = d.topiaid - AND r.category = cat.topiaid - AND cat.speciescategory = scat.topiaid - AND scat.species = s.topiaid - AND c.celltypename = 'Map cell'; - -CREATE INDEX cellmapview_idx ON cellmapview(baracoudacode, name); -CREATE UNIQUE INDEX cellmapview_uidx ON cellmapview(resultid); - -CREATE OR REPLACE VIEW meanMapcellBiomassEngrEnc AS - SELECT - * - FROM - cellmapview v - WHERE - v.baracoudacode = 'ENGR-ENC' - AND v.name = 'meanMapcellBiomass'; - -CREATE OR REPLACE VIEW meanMapcellBiomassSardPil AS - SELECT - * - FROM - cellmapview v - WHERE - v.baracoudacode = 'SARD-PIL' - AND v.name = 'meanMapcellBiomass'; - -CREATE OR REPLACE VIEW meanMapcellBiomassTracTru AS - SELECT - * - FROM - cellmapview v - WHERE - v.baracoudacode = 'TRAC-TRU' - AND v.name = 'meanMapcellBiomass'; - -CREATE OR REPLACE VIEW meanMapcellBiomassSpraSpr AS - SELECT - * - FROM - cellmapview v - WHERE - v.baracoudacode = 'SPRA-SPR' - AND v.name = 'meanMapcellBiomass'; - -CREATE OR REPLACE VIEW meanMapcellBiomassScomSco AS - SELECT - * - FROM - cellmapview v - WHERE - v.baracoudacode = 'SCOM-SCO' - AND v.name = 'meanMapcellBiomass'; - -CREATE OR REPLACE VIEW meanMapcellBiomassMicrPou AS - SELECT - * - FROM - cellmapview v - WHERE - v.baracoudacode = 'MICR-POU' - AND v.name = 'meanMapcellBiomass'; - ------------------------------------------------------------------------------------------------------------------------ ----- OPERATION VIEW --------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------ - -CREATE MATERIALIZED VIEW TotalSampleView AS - SELECT - echobase_operation_spatial.voyageid, - operation.id AS operation_id, - echobase_operation_spatial.coordinate, - sampletype.name AS sampletype_name, - sample.sampleweight, - sizecategory.name AS sizecategory_name, - species.baracoudacode, - sample.topiaid - FROM - echobase_operation_spatial, - operation, - sample, - speciescategory, - sizecategory, - species, - sampletype - WHERE - echobase_operation_spatial.operationid = operation.topiaid AND - operation.topiaid = sample.operation AND - sample.speciescategory = speciescategory.topiaid AND - sample.sampletype = sampletype.topiaid AND - speciescategory.sizecategory = sizecategory.topiaid AND - speciescategory.species = species.topiaid; - -CREATE INDEX TotalSampleView_idx ON TotalSampleView(baracoudacode, sampletype_name); - -CREATE OR REPLACE VIEW TotalSampleEngrEnc AS - SELECT - * - FROM - TotalSampleView v - WHERE - v.sampletype_name = 'Total' AND - v.baracoudacode = 'ENGR-ENC'; - -CREATE OR REPLACE VIEW TotalSampleSardPil AS - SELECT - * - FROM - TotalSampleView v - WHERE - v.sampletype_name = 'Total' AND - v.baracoudacode = 'SARD-PIL'; - -CREATE OR REPLACE VIEW TotalSampleTracTru AS - SELECT - * - FROM - TotalSampleView v - WHERE - v.sampletype_name = 'Total' AND - v.baracoudacode = 'TRAC-TRU'; - -CREATE OR REPLACE VIEW TotalSampleSpraSpr AS - SELECT - * - FROM - TotalSampleView v - WHERE - v.sampletype_name = 'Total' AND - v.baracoudacode = 'SPRA-SPR'; - -CREATE OR REPLACE VIEW TotalSampleScomSco AS - SELECT - * - FROM - TotalSampleView v - WHERE - v.sampletype_name = 'Total' AND - v.baracoudacode = 'SCOM-SCO'; - -CREATE OR REPLACE VIEW TotalSampleMicrPou AS - SELECT - * - FROM - TotalSampleView v - WHERE - v.sampletype_name = 'Total' AND - v.baracoudacode = 'MICR-POU'; - --- Create a view with species catches as columns - -CREATE OR REPLACE VIEW TotalCatchSpOpWide AS - SELECT - e.voyagename, - e.operationname, - e.coordinate, - (SELECT SUM(t.sampleweight) FROM totalsampleengrenc t WHERE t.operation_id = e.operationname) AS TotalCatchENGRENC, - (SELECT SUM(t.sampleweight) FROM totalsamplesardpil t WHERE t.operation_id = e.operationname) AS TotalCatchSARDPIL, - (SELECT SUM(t.sampleweight) FROM totalsamplespraspr t WHERE t.operation_id = e.operationname) AS TotalCatchSPRASPR, - (SELECT SUM(t.sampleweight) FROM totalsamplemicrpou t WHERE t.operation_id = e.operationname) AS TotalCatchMICRPOU, - (SELECT SUM(t.sampleweight) FROM totalsamplescomsco t WHERE t.operation_id = e.operationname) AS TotalCatchSCOMSCO, - (SELECT SUM(t.sampleweight) FROM totalsampletractru t WHERE t.operation_id = e.operationname) AS TotalCatchTRACTRU, - e.operationid - FROM echobase_operation_spatial e; \ No newline at end of file

1 0

r962 - trunk/echobase-services/src/main/resources
by tchemit＠users.forge.codelutin.com 27 Feb '14

27 Feb '14

Author: tchemit Date: 2014-02-27 21:41:19 +0100 (Thu, 27 Feb 2014) New Revision: 962 Url: http://codelutin.com/projects/echobase/repository/revisions/962 Log: refs #4194 fix trigger Modified: trunk/echobase-services/src/main/resources/postgis-view.sql Modified: trunk/echobase-services/src/main/resources/postgis-view.sql =================================================================== --- trunk/echobase-services/src/main/resources/postgis-view.sql 2014-02-27 17:25:32 UTC (rev 961) +++ trunk/echobase-services/src/main/resources/postgis-view.sql 2014-02-27 20:41:19 UTC (rev 962) @@ -142,7 +142,7 @@ CREATE TRIGGER echobase_refresh_views_trigger AFTER INSERT OR UPDATE ON entitymodificationlog -EXECUTE PROCEDURE echobase_refresh_views(); +FOR EACH ROW EXECUTE PROCEDURE echobase_refresh_views(); ----------------------------------------------------------------------------------------------------------------------- ---- CELL RESULT VIEW -------------------------------------------------------------------------------------------------

1 0

r961 - trunk
by tchemit＠users.forge.codelutin.com 27 Feb '14

27 Feb '14

Author: tchemit Date: 2014-02-27 18:25:32 +0100 (Thu, 27 Feb 2014) New Revision: 961 Url: http://codelutin.com/projects/echobase/repository/revisions/961 Log: fix nuiton-csv version Modified: trunk/pom.xml Modified: trunk/pom.xml =================================================================== --- trunk/pom.xml 2014-02-27 17:25:15 UTC (rev 960) +++ trunk/pom.xml 2014-02-27 17:25:32 UTC (rev 961) @@ -221,7 +221,7 @@ <dependency> <groupId>org.nuiton</groupId> <artifactId>nuiton-csv</artifactId> - <version>3.0-alpha-2</version> + <version>3.0-alpha-3</version> </dependency> <dependency>

1 0

r960 - trunk/echobase-domain/src/main/resources/migration
by tchemit＠users.forge.codelutin.com 27 Feb '14

27 Feb '14

Author: tchemit Date: 2014-02-27 18:25:15 +0100 (Thu, 27 Feb 2014) New Revision: 960 Url: http://codelutin.com/projects/echobase/repository/revisions/960 Log: refs #4194 fix trigger Modified: trunk/echobase-domain/src/main/resources/migration/postgis-view-2.6.sql Modified: trunk/echobase-domain/src/main/resources/migration/postgis-view-2.6.sql =================================================================== --- trunk/echobase-domain/src/main/resources/migration/postgis-view-2.6.sql 2014-02-27 16:43:28 UTC (rev 959) +++ trunk/echobase-domain/src/main/resources/migration/postgis-view-2.6.sql 2014-02-27 17:25:15 UTC (rev 960) @@ -139,8 +139,8 @@ LANGUAGE 'plpgsql'; CREATE TRIGGER echobase_refresh_views_trigger -AFTER INSERT OR UPDATE ON entitymodificationlog -EXECUTE PROCEDURE echobase_refresh_views(); +AFTER INSERT ON entitymodificationlog +FOR EACH ROW EXECUTE PROCEDURE echobase_refresh_views(); ----------------------------------------------------------------------------------------------------------------------- ---- CELL RESULT VIEW -------------------------------------------------------------------------------------------------

1 0

r959 - in trunk/echobase-domain: . src/main src/main/data src/main/java/fr/ifremer/echobase/persistence/migration/workingDb src/main/resources/migration
by tchemit＠users.forge.codelutin.com 27 Feb '14

27 Feb '14

Author: tchemit Date: 2014-02-27 17:43:28 +0100 (Thu, 27 Feb 2014) New Revision: 959 Url: http://codelutin.com/projects/echobase/repository/revisions/959 Log: fixes #4583: Mettre ?\195?\160 jour le r?\195?\169f?\195?\169rentiel Added: trunk/echobase-domain/src/main/data/ trunk/echobase-domain/src/main/data/echobase-referentiel.echobase Modified: trunk/echobase-domain/pom.xml trunk/echobase-domain/src/main/java/fr/ifremer/echobase/persistence/migration/workingDb/MigrationCallBackForVersion2_6.java trunk/echobase-domain/src/main/resources/migration/postgis-view-2.6.sql Modified: trunk/echobase-domain/pom.xml =================================================================== --- trunk/echobase-domain/pom.xml 2014-02-27 16:42:36 UTC (rev 958) +++ trunk/echobase-domain/pom.xml 2014-02-27 16:43:28 UTC (rev 959) @@ -13,6 +13,12 @@ <name>EchoBase :: Domain</name> <description>EchoBase domain module</description> + <properties> + <redmine.releaseFiles> + ${project.build.directory}/echobase-referentiel-${project.version}.echobase + </redmine.releaseFiles> + </properties> + <build> <plugins> @@ -219,4 +225,47 @@ </dependencies> + <profiles> + + <profile> + <id>copy-referential</id> + <activation> + <property> + <name>performRelease</name> + <value>true</value> + </property> + </activation> + + <build> + <defaultGoal>generate-resources</defaultGoal> + <plugins> + <plugin> + <artifactId>maven-antrun-plugin</artifactId> + <executions> + <execution> + <id>copy referential</id> + <phase>generate-resources</phase> + <inherited>false</inherited> + <configuration> + <tasks> + <echo message="copy echobase referential" /> + <copy verbose="true" + failonerror="false" + overwrite="true" + file="${project.basedir}/src/main/data/echobase-referentiel.echobase" + tofile="${project.build.directory}/echobase-referentiel-${project.version}.echobase"/> + </tasks> + </configuration> + <goals> + <goal>run</goal> + </goals> + </execution> + </executions> + </plugin> + </plugins> + </build> + + </profile> + + </profiles> </project> Added: trunk/echobase-domain/src/main/data/echobase-referentiel.echobase =================================================================== (Binary files differ) Property changes on: trunk/echobase-domain/src/main/data/echobase-referentiel.echobase ___________________________________________________________________ Added: svn:mime-type + application/zip Modified: trunk/echobase-domain/src/main/java/fr/ifremer/echobase/persistence/migration/workingDb/MigrationCallBackForVersion2_6.java =================================================================== --- trunk/echobase-domain/src/main/java/fr/ifremer/echobase/persistence/migration/workingDb/MigrationCallBackForVersion2_6.java 2014-02-27 16:42:36 UTC (rev 958) +++ trunk/echobase-domain/src/main/java/fr/ifremer/echobase/persistence/migration/workingDb/MigrationCallBackForVersion2_6.java 2014-02-27 16:43:28 UTC (rev 959) @@ -1,10 +1,38 @@ package fr.ifremer.echobase.persistence.migration.workingDb; +/* + * #%L + * EchoBase :: Domain + * $Id$ + * $HeadURL:$ + * %% + * Copyright (C) 2011 - 2014 Ifremer, Codelutin + * %% + * This program is free software: you can redistribute it and/or modify + * it under the terms of the GNU Affero General Public License as published by + * the Free Software Foundation, either version 3 of the License, or + * (at your option) any later version. + * + * This program is distributed in the hope that it will be useful, + * but WITHOUT ANY WARRANTY; without even the implied warranty of + * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the + * GNU General Public License for more details. + * + * You should have received a copy of the GNU Affero General Public License + * along with this program. If not, see <http://www.gnu.org/licenses/>. + * #L% + */ + import fr.ifremer.echobase.entities.EchoBaseUserTopiaPersistenceContext; import fr.ifremer.echobase.io.EchoBaseIOUtil; import org.nuiton.topia.persistence.TopiaException; +import org.nuiton.topia.persistence.support.TopiaSqlQuery; import org.nuiton.util.Version; +import java.sql.Connection; +import java.sql.PreparedStatement; +import java.sql.ResultSet; +import java.sql.SQLException; import java.util.List; /** @@ -32,12 +60,60 @@ if (spatialAware) { - // repass postgis scripts (http://forge.codelutin.com/issues/4018) + // add new postigs view (see https://forge.codelutin.com/issues/4194) updatePostgis(queries); } + + // update datametadata referential (see https://forge.codelutin.com/issues/4583) + updateDataMedataReferential(tx, queries); } protected void updatePostgis(List<String> queries) { queries.add(EchoBaseIOUtil.loadScript(POSTGIS_VIEW_SQL)); } + + protected void updateDataMedataReferential(EchoBaseUserTopiaPersistenceContext tx, List<String> queries) { + + String dataId; + dataId = tx.getSqlSupport().findSingleResult(new GetdataMetadataSqlQuery("meanMapcellBiomass")); + if (dataId == null) { + // add it + queries.add("INSERT INTO datametadata (topiaId,topiaversion, topiacreatedate, addOffset, comment, fillValue, longName, name, scaleFactor, units, validMax, validMin) VALUES('fr.ifremer.echobase.entities.references.DataMetadata#4443610280597#0.9322615025965290', 0, '2014-02-27 17:01:48.355', 0.0, 'Mean biomass averaged in a grid map cell using a block averaging procedure', 0, 'Mean biomass in map cell', 'meanMapcellBiomass', 1.0, 'tons', 999999999, 0);"); + } + + dataId = tx.getSqlSupport().findSingleResult(new GetdataMetadataSqlQuery("stdevMapcellBiomass")); + if (dataId == null) { + // add it + queries.add("INSERT INTO datametadata (topiaId,topiaversion, topiacreatedate, addOffset, comment, fillValue, longName, name, scaleFactor, units, validMax, validMin) VALUES('fr.ifremer.echobase.entities.references.DataMetadata#4443610280597#0.9322615025965291', 0, '2014-02-27 17:01:48.355', 0.0, 'Biomass standard deviation in a grid map cell produced from a block averaging procedure', 0, 'Biomass standard deviation in map cell', 'stdevMapcellBiomass', 1.0, 'tons', 999999999, 0);"); + } + + dataId = tx.getSqlSupport().findSingleResult(new GetdataMetadataSqlQuery("NsampleMapcell")); + if (dataId == null) { + // add it + queries.add("INSERT INTO datametadata (topiaId,topiaversion, topiacreatedate, addOffset, comment, fillValue, longName, name, scaleFactor, units, validMax, validMin) VALUES('fr.ifremer.echobase.entities.references.DataMetadata#4443610280597#0.9322615025965292', 0, '2014-02-27 17:01:48.355', 0.0, 'No. of data samples in a grid map cell in a block averaging procedure', 0, 'No. of samples in map cell', 'NsampleMapcell', 1.0, 'tons', 999999999, 0);"); + } + } + + private static class GetdataMetadataSqlQuery extends TopiaSqlQuery<String> { + + private final String dataName; + + private GetdataMetadataSqlQuery(String dataName) { + this.dataName = dataName; + } + + @Override + public PreparedStatement prepareQuery(Connection connection) throws SQLException { + String hql = "SELECT topiaid FROM datametadata " + + "WHERE name = ?"; + PreparedStatement result = connection.prepareStatement(hql); + result.setString(1, dataName); + return result; + } + + @Override + public String prepareResult(ResultSet set) throws SQLException { + return set.getString(1); + } + } } \ No newline at end of file Modified: trunk/echobase-domain/src/main/resources/migration/postgis-view-2.6.sql =================================================================== --- trunk/echobase-domain/src/main/resources/migration/postgis-view-2.6.sql 2014-02-27 16:42:36 UTC (rev 958) +++ trunk/echobase-domain/src/main/resources/migration/postgis-view-2.6.sql 2014-02-27 16:43:28 UTC (rev 959) @@ -1,3 +1,25 @@ +--- +-- #%L +-- EchoBase :: Domain +-- $Id:$ +-- $HeadURL:$ +-- %% +-- Copyright (C) 2011 - 2014 Ifremer, Codelutin +-- %% +-- This program is free software: you can redistribute it and/or modify +-- it under the terms of the GNU Affero General Public License as published by +-- the Free Software Foundation, either version 3 of the License, or +-- (at your option) any later version. +-- +-- This program is distributed in the hope that it will be useful, +-- but WITHOUT ANY WARRANTY; without even the implied warranty of +-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the +-- GNU General Public License for more details. +-- +-- You should have received a copy of the GNU Affero General Public License +-- along with this program. If not, see <http://www.gnu.org/licenses/>. +-- #L% +--- ----------------------------------------------------------------------------------------------------------------------- ---- DROP EXISTING VIEW - INDEX - FUNCTION - TRIGGER ------------------------------------------------------------------ -----------------------------------------------------------------------------------------------------------------------

1 0

r958 - in trunk/echobase-services/src/test/java/fr/ifremer/echobase/services: . service/exportquery
by tchemit＠users.forge.codelutin.com 27 Feb '14

27 Feb '14

Author: tchemit Date: 2014-02-27 17:42:36 +0100 (Thu, 27 Feb 2014) New Revision: 958 Url: http://codelutin.com/projects/echobase/repository/revisions/958 Log: fix tests Modified: trunk/echobase-services/src/test/java/fr/ifremer/echobase/services/EchoBaseTestServiceSupport.java trunk/echobase-services/src/test/java/fr/ifremer/echobase/services/FakeEchoBaseServiceContext.java trunk/echobase-services/src/test/java/fr/ifremer/echobase/services/service/exportquery/ExportQueryServiceTest.java Modified: trunk/echobase-services/src/test/java/fr/ifremer/echobase/services/EchoBaseTestServiceSupport.java =================================================================== --- trunk/echobase-services/src/test/java/fr/ifremer/echobase/services/EchoBaseTestServiceSupport.java 2014-02-27 15:52:37 UTC (rev 957) +++ trunk/echobase-services/src/test/java/fr/ifremer/echobase/services/EchoBaseTestServiceSupport.java 2014-02-27 16:42:36 UTC (rev 958) @@ -89,7 +89,9 @@ @Before public void setUp() throws Exception { this.serviceContext = fakeServiceContext; - this.fakeServiceContext.inject(this); + if (fakeServiceContext!=null) { + fakeServiceContext.inject(this); + } } protected EchoBaseUser createFakeUser() { Modified: trunk/echobase-services/src/test/java/fr/ifremer/echobase/services/FakeEchoBaseServiceContext.java =================================================================== --- trunk/echobase-services/src/test/java/fr/ifremer/echobase/services/FakeEchoBaseServiceContext.java 2014-02-27 15:52:37 UTC (rev 957) +++ trunk/echobase-services/src/test/java/fr/ifremer/echobase/services/FakeEchoBaseServiceContext.java 2014-02-27 16:42:36 UTC (rev 958) @@ -159,7 +159,7 @@ @Override protected void finished(Description description) { super.finished(description); - if (!echoBaseTopiaApplicationContext.isClosed()) { + if (echoBaseTopiaApplicationContext!=null && !echoBaseTopiaApplicationContext.isClosed()) { echoBaseTopiaApplicationContext.closeContext(); } } Modified: trunk/echobase-services/src/test/java/fr/ifremer/echobase/services/service/exportquery/ExportQueryServiceTest.java =================================================================== --- trunk/echobase-services/src/test/java/fr/ifremer/echobase/services/service/exportquery/ExportQueryServiceTest.java 2014-02-27 15:52:37 UTC (rev 957) +++ trunk/echobase-services/src/test/java/fr/ifremer/echobase/services/service/exportquery/ExportQueryServiceTest.java 2014-02-27 16:42:36 UTC (rev 958) @@ -46,12 +46,6 @@ @Inject ExportQueryService exportQueryService; -// @Before -// public void setup() { -// exportQueryService = getExportQueryService(); -// } - - @Ignore @Test public void processLibreOfficeSqlQuery() throws Exception {

1 0