CMDBuild Forum

Problem about Views in Administration mode

Old Versions
#1

Hi, guys, I have met a problem about Views from SQL. In the pgAdmin, I can just create a view based on sql, but it doesn't show in the CMDBuild. So, if we want to create a view, should I have to write a function and selected it in the data source? And in the manual, it says the function has to include the input and output parameters, so what is the input and output for a view function?

 

Thank you!

#2

As specified in the Administrator Manual V2.4 (page 58) the function must have the comment “TYPE: function”.
Then remember to clean the server cache (Administration Module - Setup - Management Server Page) and the browser cache.
CMDBuild Team

#3

Previously Tecnoteca wrote:

As specified in the Administrator Manual V2.4 (page 58) the function must have the comment "TYPE: function".
Then remember to clean the server cache (Administration Module - Setup - Management Server Page) and the browser cache.
CMDBuild Team
Thank you for reminding these steps, but I still got this error after I cleaned the server cache.
Call: services/json/dashboard/list
-----------------------------------
Error: org.cmdbuild.servlets.json.management.JsonSerializationException: org.codehaus.jackson.map.JsonMappingException: (was java.lang.UnsupportedOperationException) (through reference chain: org.cmdbuild.servlets.json.management.JsonResponse["response"]->org.cmdbuild.servlets.json.serializers.JsonDashboardListResponse["dataSources"]->java.util.ArrayList[0]->org.cmdbuild.servlets.json.serializers.JsonDataSource["output"])
at org.cmdbuild.servlets.json.management.JsonResponse.toString(JsonResponse.java:61)
at org.cmdbuild.servlets.JSONDispatcher.writeResponseData(JSONDispatcher.java:294)
at org.cmdbuild.servlets.JSONDispatcher.writeResponse(JSONDispatcher.java:211)
at org.cmdbuild.servlets.JSONDispatcher.dispatch(JSONDispatcher.java:106)
at org.cmdbuild.servlets.JSONDispatcher.doPost(JSONDispatcher.java:64)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:648)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:729)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:292)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:240)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)
at org.cmdbuild.filters.AuthFilter.doFilter(AuthFilter.java:188)
at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:346)
at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:262)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:240)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)
at org.cmdbuild.filters.PatchManagerFilter.doFilter(PatchManagerFilter.java:48)
at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:346)
at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:262)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:240)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)
at org.cmdbuild.filters.ConfCheckFilter.doFilter(ConfCheckFilter.java:31)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:240)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)
at org.cmdbuild.filters.TranslationFilter.doFilter(TranslationFilter.java:52)
at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:346)
at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:262)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:240)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)
at org.cmdbuild.filters.LocalizationFilter.doFilter(LocalizationFilter.java:127)
at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:346)
at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:262)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:240)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:212)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:106)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:502)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:141)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:79)
at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:616)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:88)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:528)
at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1099)
at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:672)
at org.apache.tomcat.util.net.AprEndpoint$SocketProcessor.doRun(AprEndpoint.java:2508)
at org.apache.tomcat.util.net.AprEndpoint$SocketProcessor.run(AprEndpoint.java:2497)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
at java.lang.Thread.run(Thread.java:745)
Caused by: org.codehaus.jackson.map.JsonMappingException: (was java.lang.UnsupportedOperationException) (through reference chain: org.cmdbuild.servlets.json.management.JsonResponse["response"]->org.cmdbuild.servlets.json.serializers.JsonDashboardListResponse["dataSources"]->java.util.ArrayList[0]->org.cmdbuild.servlets.json.serializers.JsonDataSource["output"])
at org.codehaus.jackson.map.JsonMappingException.wrapWithPath(JsonMappingException.java:218)
at org.codehaus.jackson.map.JsonMappingException.wrapWithPath(JsonMappingException.java:183)
at org.codehaus.jackson.map.ser.std.SerializerBase.wrapAndThrow(SerializerBase.java:140)
at org.codehaus.jackson.map.ser.std.BeanSerializerBase.serializeFields(BeanSerializerBase.java:158)
at org.codehaus.jackson.map.ser.BeanSerializer.serialize(BeanSerializer.java:112)
at org.codehaus.jackson.map.ser.std.StdContainerSerializers$IndexedListSerializer.serializeContents(StdContainerSerializers.java:122)
at org.codehaus.jackson.map.ser.std.StdContainerSerializers$IndexedListSerializer.serializeContents(StdContainerSerializers.java:71)
at org.codehaus.jackson.map.ser.std.AsArraySerializerBase.serialize(AsArraySerializerBase.java:86)
at org.codehaus.jackson.map.ser.BeanPropertyWriter.serializeAsField(BeanPropertyWriter.java:446)
at org.codehaus.jackson.map.ser.std.BeanSerializerBase.serializeFields(BeanSerializerBase.java:150)
at org.codehaus.jackson.map.ser.BeanSerializer.serialize(BeanSerializer.java:112)
at org.codehaus.jackson.map.ser.BeanPropertyWriter.serializeAsField(BeanPropertyWriter.java:446)
at org.codehaus.jackson.map.ser.std.BeanSerializerBase.serializeFields(BeanSerializerBase.java:150)
at org.codehaus.jackson.map.ser.BeanSerializer.serialize(BeanSerializer.java:112)
at org.codehaus.jackson.map.ser.StdSerializerProvider._serializeValue(StdSerializerProvider.java:610)
at org.codehaus.jackson.map.ser.StdSerializerProvider.serializeValue(StdSerializerProvider.java:256)
at org.codehaus.jackson.map.ObjectMapper._configAndWriteValue(ObjectMapper.java:2575)
at org.codehaus.jackson.map.ObjectMapper.writeValueAsString(ObjectMapper.java:2097)
at org.cmdbuild.servlets.json.management.JsonResponse.toString(JsonResponse.java:59)
... 50 more
Caused by: java.lang.UnsupportedOperationException
at org.cmdbuild.dao.entrytype.attributetype.UndefinedAttributeType.accept(UndefinedAttributeType.java:17)
at org.cmdbuild.servlets.json.serializers.JsonDashboardDTO$JsonDataSourceParameter$TypeConverter.(JsonDashboardDTO.java:118)
at org.cmdbuild.servlets.json.serializers.JsonDashboardDTO$JsonDataSourceParameter.(JsonDashboardDTO.java:102)
at org.cmdbuild.servlets.json.serializers.JsonDashboardDTO$JsonDataSourceParameter.(JsonDashboardDTO.java:95)
at org.cmdbuild.servlets.json.serializers.JsonDashboardDTO$JsonDataSource$1.apply(JsonDashboardDTO.java:72)
at org.cmdbuild.servlets.json.serializers.JsonDashboardDTO$JsonDataSource$1.apply(JsonDashboardDTO.java:69)
at com.google.common.collect.Iterators$8.transform(Iterators.java:817)
at com.google.common.collect.TransformedIterator.next(TransformedIterator.java:48)
at com.google.common.collect.Iterators.addAll(Iterators.java:365)
at com.google.common.collect.Lists.newArrayList(Lists.java:162)
at com.google.common.collect.Lists.newArrayList(Lists.java:146)
at org.cmdbuild.servlets.json.serializers.JsonDashboardDTO$JsonDataSource.getOutput(JsonDashboardDTO.java:91)
at sun.reflect.GeneratedMethodAccessor196.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.codehaus.jackson.map.ser.BeanPropertyWriter.get(BeanPropertyWriter.java:483)
at org.codehaus.jackson.map.ser.BeanPropertyWriter.serializeAsField(BeanPropertyWriter.java:418)
at org.codehaus.jackson.map.ser.std.BeanSerializerBase.serializeFields(BeanSerializerBase.java:150)
... 65 more
 
and this is the function I wrote:
CREATE OR REPLACE FUNCTION cmf_apache_host_view(OUT os "LookUp", OUT system character varying, OUT admin character varying)
  RETURNS SETOF record AS
$BODY$BEGIN
RETURN QUERY SELECT 
"host"."os" as"os",
"Apache"."system",
"Apache"."admin"
FROM "host","Apache"
WHERE
"Apache"."ip" = "host"."ip";
END$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000;
ALTER FUNCTION cmf_apache_host_view()
  OWNER TO postgres;
COMMENT ON FUNCTION cmf_apache_host_view() IS 'TYPE: function';
 
Could you help me to fix this bug?
#4

Previously Tecnoteca wrote:

As specified in the Administrator Manual V2.4 (page 58) the function must have the comment "TYPE: function".
Then remember to clean the server cache (Administration Module - Setup - Management Server Page) and the browser cache.
CMDBuild Team
 
But I still got this error
 
Call: services/json/dashboard/list
-----------------------------------
Error: org.cmdbuild.servlets.json.management.JsonSerializationException: org.codehaus.jackson.map.JsonMappingException: (was java.lang.UnsupportedOperationException) (through reference chain: org.cmdbuild.servlets.json.management.JsonResponse["response"]->org.cmdbuild.servlets.json.serializers.JsonDashboardListResponse["dataSources"]->java.util.ArrayList[1]->org.cmdbuild.servlets.json.serializers.JsonDataSource["output"])
at org.cmdbuild.servlets.json.management.JsonResponse.toString(JsonResponse.java:61)
at org.cmdbuild.servlets.JSONDispatcher.writeResponseData(JSONDispatcher.java:294)
at org.cmdbuild.servlets.JSONDispatcher.writeResponse(JSONDispatcher.java:211)
at org.cmdbuild.servlets.JSONDispatcher.dispatch(JSONDispatcher.java:106)
at org.cmdbuild.servlets.JSONDispatcher.doPost(JSONDispatcher.java:64)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:648)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:729)
......
 
Caused by: org.codehaus.jackson.map.JsonMappingException: (was java.lang.UnsupportedOperationException) (through reference chain: org.cmdbuild.servlets.json.management.JsonResponse["response"]->org.cmdbuild.servlets.json.serializers.JsonDashboardListResponse["dataSources"]->java.util.ArrayList[1]->org.cmdbuild.servlets.json.serializers.JsonDataSource["output"])
at org.codehaus.jackson.map.JsonMappingException.wrapWithPath(JsonMappingException.java:218)
at org.codehaus.jackson.map.JsonMappingException.wrapWithPath(JsonMappingException.java:183)
at org.codehaus.jackson.map.ser.std.SerializerBase.wrapAndThrow(SerializerBase.java:140)
at org.codehaus.jackson.map.ser.std.BeanSerializerBase.serializeFields(BeanSerializerBase.java:158)
at org.codehaus.jackson.map.ser.BeanSerializer.serialize(BeanSerializer.java:112)
............
 
Caused by: java.lang.UnsupportedOperationException
at org.cmdbuild.dao.entrytype.attributetype.UndefinedAttributeType.accept(UndefinedAttributeType.java:17)
at org.cmdbuild.servlets.json.serializers.JsonDashboardDTO$JsonDataSourceParameter$TypeConverter.(JsonDashboardDTO.java:118)
at org.cmdbuild.servlets.json.serializers.JsonDashboardDTO$JsonDataSourceParameter.(JsonDashboardDTO.java:102)
at org.cmdbuild.servlets.json.serializers.JsonDashboardDTO$JsonDataSourceParameter.(JsonDashboardDTO.java:95)
at org.cmdbuild.servlets.json.serializers.JsonDashboardDTO$JsonDataSource$1.apply(JsonDashboardDTO.java:72)
at org.cmdbuild.servlets.json.serializers.JsonDashboardDTO$JsonDataSource$1.apply(JsonDashboardDTO.java:69)
............
 
and this is the function I wrote:
CREATE OR REPLACE FUNCTION cmf_apache_host_view(OUT os "LookUp", OUT system character varying, OUT admin character varying)
  RETURNS SETOF record AS
$BODY$BEGIN
RETURN QUERY SELECT 
"host"."os" as"os",
"Apache"."system",
"Apache"."admin"
FROM "host","Apache"
WHERE
"Apache"."ip" = "host"."ip";
END$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000;
ALTER FUNCTION cmf_apache_host_view()
  OWNER TO postgres;
COMMENT ON FUNCTION cmf_apache_host_view() IS 'TYPE: function';
 
Plz help me to fix it.
#5

Previously Tecnoteca wrote:

As specified in the Administrator Manual V2.4 (page 58) the function must have the comment "TYPE: function".
Then remember to clean the server cache (Administration Module - Setup - Management Server Page) and the browser cache.
CMDBuild Team
Sorry to bother you again, But I was stuck in the view function on my cmdb project. Could you help me to explain or fix this error?
Thank you!
 
 
#6

Before using your function CMDBuild’ve tried to run it from Postgres?
The SQL that you posted may not work because Postgres did not know the type LookUp (for him is an integer).
CMDBuild Team

 
 

 

#7

Previously Tecnoteca wrote:

Before using your function CMDBuild've tried to run it from Postgres?
The SQL that you posted may not work because Postgres did not know the type LookUp (for him is an integer).
CMDBuild Team
 
 

Thank you for your help, It works for me. And I also have a another question about view, it seems like in the view we can not use search filter, should I put some 'in' parameter in the function? Thank you! 

 

#8

I confirm that you can perform searches in the views created by filters, but you can not perform searches in SQL views.
CMDBuild Team