Eliminate PL/pgSQL functions from petascope

[Dimitar Misev]

PL/pgSQL functions are used in petascope to compute some queries. This saves some number of queries, but ties petascope to postgres, so they should be eliminated:

1. identify which PL/pgSQL functions in source:applications/petascope/src/main/db/petascope/update8 (in particular in utilities.sql and macros.sql) are used in the SQL queries executed by petascope. All SQL queries are executed in DbMetadataSource, so look here to identify which (custom) functions they use.
2. translate those functions to Java, so that the function call in the SQL query in DbMetadataSource is instead replaced with the result from the Java method.

[Piero Campalani]

Hi there,
those functions were developed in order to maintain integrity of the data at coverage level, ie. ensure you do not insert data that does not make sense.
Make sure Petascope reproduces those checks: I'm here in case something is not clear about them!

[Dimitar Misev]

I'm getting an exception on coverages imported with rasimport, after commit changeset:b8dfedd6d3464f3194a304398b675e869b3072ca

 DEBUG [20:46:34] DbMetadataSource@2731: SQL query:  SELECT ps_uom.code,ps_quantity.label,ps_quantity.description,ps_quantity.definition_uri,ps_quantity.nil_ids,ps_nil_value.id,ps_nil_value.value AS nil_values , ps_nil_value.reason AS nil_reasons FROM ps_nil_value , ps_quantity INNER JOIN ps_uom ON (ps_uom.id=ps_quantity.uom_id)  WHERE ps_quantity.id=11 GROUP BY ps_uom.code,ps_quantity.label,ps_quantity.description,ps_quantity.definition_uri,ps_quantity.nil_ids,ps_nil_value.id , nil_values , nil_reasons
 ERROR [20:46:34] DbMetadataSource@1424: Failed reading the coverage metadata
InvalidServiceConfiguration: No SWE quantities stored in the database.
	at petascope.core.DbMetadataSource.readSWEQuantity(DbMetadataSource.java:2735)
	at petascope.core.DbMetadataSource.read(DbMetadataSource.java:1060)
	at petascope.wcs2.handlers.GetCapabilitiesHandler.handle(GetCapabilitiesHandler.java:312)
	at petascope.wcs2.handlers.GetCapabilitiesHandler.handle(GetCapabilitiesHandler.java:68)
	at petascope.wcs2.extensions.AbstractProtocolExtension.handle(AbstractProtocolExtension.java:70)
	at petascope.PetascopeInterface.handleWcs2Request(PetascopeInterface.java:646)
	at petascope.PetascopeInterface.handleWcsRequest(PetascopeInterface.java:576)
	at petascope.PetascopeInterface.doGet(PetascopeInterface.java:356)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:621)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:722)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:304)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:240)
	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:203)
	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:164)
	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:108)
	at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:558)
	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118)
	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:379)
	at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:242)
	at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:259)
	at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:237)
	at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:281)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
	at java.lang.Thread.run(Thread.java:745)

[Bidesh Thapaliya]

That is weird. It is not finding the metadata. Will look at the mentioned changeset to see if something there can cause it.

[Alex Dumitru]

Probably needs a left join here, instead of the inner join, as it would require a nill value to always be present, which is not the case.
​http://rasdaman.org/browser/applications/petascope/src/main/java/petascope/core/DbMetadataSource.java#L2709

[Alex Dumitru]

Actually, it's the line above: ​http://rasdaman.org/browser/applications/petascope/src/main/java/petascope/core/DbMetadataSource.java#L2708

 FROM " + TABLE_NIL_VALUE + " , " + TABLE_QUANTITY will need something in the table NIL_VALUE each time the query is executed.

Replacing it with TABLE_NIL_VALUE LEFT JOIN TABLE_QUANTITY, might do the trick, but I don't have enough time to check it today, and I'll be at OGC next days. If any of you could submit a patch, that would be great, right now, nothing works due to this issue.
Raising the level to Critical as Petascope is currently unusable.

[Dimitar Misev]

Ok I'll check it now.

[Dimitar Misev]

Patch submitted