openmaint-1.1-2.5.0 error: org.cmdbuild.exception.ORMException: ORM_SQL_PATCH

devanshu.chakraborty · April 13, 2018, 10:04am

Hi,

This post is with reference to the error I am facing after deployment of openmaint-1.1-2.5.0.

I am trying to make a fresh install of openmaint-1.1-2.5.0. But I get error "org.cmdbuild.exception.ORMException: ORM_SQL_PATCH" when I click on "Apply Patch" button just after a fresh install of openmaint.

My Test Environment:

OS: Windows 7 Professional 64bit

Tomcat: Tomcat8.5.28

Java: 1.8.0_162

Database: PostgreSQL 64bit v10.2-1

==========================================

Here is what I did to install it:

1. Downloaded openmaint-1.1-2.5.0.zip.

2. Extracted it to "D:\Programs\Apache\Tomcat8.5.28\webapps"

3. After extraction, two war files got shown: "cmdbuild-openmaint-2.5.0.war" and "cmdbuild-shark-server-openmaint-2.5.0.war".

4. I renamed them to "openmaint" and "shark" respectively.

5. Now I started Tomcat

6. In firefox browser, I opened url: "http://localhost:8080/openmaint".

7. A screen shown with fields:

> Choose default language: Drop Down List

> Show language selection in login box: checkbox

8. I click on "Next". In next screen following fields are shown section-wise:

CMDBuild Database:

> Type: Drop Down List -> I selected value "Demo"

> Name: Text Box: I entered name for the database

> Create a shark schema: Check Box -> I selected it

Database Connection (PostgreSQL JDBC Driver 42.2.1):

> Host: localhost

> Port: 5432

> Super user: postgres

> Password: *******

I tested connection and it was successful

Create restricted database user:

User Type: Drop Down List -> I selected "Super user"

9. I click on "Next". The installation seems to have gone well and it shows a link to access application.

10. When I click link to access application, the opened webpage shows a list of patches to be applied. Following is the list of patches shown:

Category    Name        Description
openMAINT    1.1.0-04    DataModel - Fix triggers and constraints
openMAINT    1.1.0-05    Workorder - Refactoring
openMAINT    1.1.0-06    Maintenance request - Refactoring
openMAINT    1.1.0-07    Processes - Emails revision
openMAINT    1.1.0-08    DataModel - Lookup types revision
openMAINT    1.1.0-09    Maintenance processes - Refactoring
openMAINT    1.1.0-10    Maintenance processes - Time-based preventive activities refactoring
openMAINT    1.1.0-11    Maintenance processes - Time-based preventive activities refactoring (functions)
openMAINT    1.1.0-12    DataModel - Remove unused classes
openMAINT    1.1.0-13    DataModel - Triggers and constraints clean-up
openMAINT    1.1.0-14    DataModel - Comments revision
openMAINT    1.1.0-15    Maintenance processes - Meter-based preventive maintenance
openMAINT    1.1.0-16    DataModel - M/D domains revision
openMAINT    1.1.0-17    Maintenance processes - Scheduling criteria improvements
openMAINT    1.1.0-18    DataModel - Avoid duplicate relations. Remove unused triggers.
openMAINT    1.1.0-19    DataModel - Load French translations
openMAINT    1.1.0-20    DataModel - Drop BuildingBimProject domain if bim is not configured
openMAINT    1.1.0-21    DataModel - New domain Department-Person
openMAINT    1.1.0-22    DataModel - Fix lookup values
openMAINT    1.1.0-23    Reports - Reports improvements
openMAINT    1.1.0-24    DataModel - Fix Priority lookup
openMAINT    1.1.0-25    DataModel - Load Italian translations
openMAINT    1.1.0-26    Portal - Portal utilities
openMAINT    1.1.0-27    Processes - Fix ticket numbers generation
openMAINT    1.1.0-28    Maintenance processes - Time-based preventive activities improvements
openMAINT    1.1.0-29    DataModel - PMScheduling class
openMAINT    1.1.0-30    Maintenance processes - Improvements
openMAINT    1.1.0-31    DataModel - New navigation menu
openMAINT    1.1.0-32    Preventive maintenance - Functions
openMAINT    1.1.0-33    Dashboards and Views- Improvements
openMAINT    1.1.0-34    DataModel - Remove unused metadata
openMAINT    1.1.0-35    Dashboards - Code clean-up
openMAINT    1.1.0-36    QR Code Generator - New process for generating QR codes labels
openMAINT    1.1.0-37    DataModel - Set all grants
openMAINT    1.1.0-38    Reorganize menu folders
openMAINT    1.1.0-39    Add preventive maintenance widgets
openMAINT    1.1.0-40    Fix too short field for Workorder Downtime
openMAINT    1.1.0-41    MTBF/Warehouse reports - Fix descriptions of asset types in input params dropdown
openMAINT    1.1.0-42    DataModel - New service activity class to be used in the maintenance processes
openMAINT    1.1.0-43    Maintenance processes - Fix emails recipients
openMAINT    1.1.0-44    WOGen process - Add message with instructions for the user
openMAINT    1.1.0-45    DataModel - Move spatial_ref_sys table into gis schema
openMAINT    1.1.0-46    Reports - Report to print barcodes from Asset Management and stand-alone
openMAINT    1.1.0-47    DataModel - Fix CQL filters on InventoryItem subclasses
openMAINT    1.1.0-48    QRCodeGen process - Process is startable from AssetManager role only
openMAINT    1.1.0-49    DataModel - Fix wrong dashboard definition
openMAINT    1.1.0-50    DataModel - Final fixes for Italian translations

11. When I click on "Apply" button, I get a small error popup on right bottom corner of the page with text "Error in SQL patch". I click on "Show details" link and see following error:

Call: services/json/configure/applypatches
-------------------------------------------
Error: org.cmdbuild.exception.ORMException: ORM_SQL_PATCH
    at org.cmdbuild.exception.ORMException$ORMExceptionType.createException(ORMException.java:63)
    at org.cmdbuild.services.DefaultPatchManager.applyPatch(DefaultPatchManager.java:391)
    at org.cmdbuild.services.DefaultPatchManager.applyPatchList(DefaultPatchManager.java:360)
    at org.cmdbuild.services.startup.DefaultStartupLogic.migrate(DefaultStartupLogic.java:31)
    at org.cmdbuild.servlets.json.Configure.applyPatches(Configure.java:214)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
    at java.lang.reflect.Method.invoke(Unknown Source)
    at org.cmdbuild.servlets.JSONDispatcher.dispatch(JSONDispatcher.java:104)
    at org.cmdbuild.servlets.JSONDispatcher.doPost(JSONDispatcher.java:64)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:661)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:742)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:231)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
    at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
    at org.cmdbuild.filters.AuthFilter.doFilter(AuthFilter.java:183)
    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:193)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
    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:193)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
    at org.cmdbuild.filters.ConfCheckFilter.doFilter(ConfCheckFilter.java:31)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
    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:193)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
    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:193)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:199)
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96)
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:140)
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:81)
    at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:650)
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:87)
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:342)
    at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:803)
    at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:66)
    at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:790)
    at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1459)
    at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
    at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
    at java.lang.Thread.run(Unknown Source)

The log file "cmdbuild.log" shows following entries after I click on "Apply Patch" button:

INFO 2018-04-13 15:28:14 [jsonrpc ] Calling url /configure/applypatches

INFO 2018-04-13 15:28:14 [cmdbuild] applying patch 'DefaultPatchManager.DefaultPatch[version=1.1.0-04,description=DataModel - Fix triggers and constraints,category=openMAINT,file=D:\Programs\Apache\Tomcat8.5.28\webapps\openmaint\WEB-INF\patches-ext\openMAINT\1.1.0-04.sql]'

ERROR 2018-04-13 15:28:14 [cmdbuild] failed applying patch '1.1.0-04'

org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [-- DataModel - Fix triggers and constraints

-- AccountingDocument

COMMENT ON TABLE public."AccountingDocument"

COMMENT ON COLUMN public."AccountingDocument"."Id" IS 'MODE: reserved';

COMMENT ON COLUMN public."AccountingDocument"."IdClass" IS 'MODE: reserved';

COMMENT ON COLUMN public."AccountingDocument"."Status" IS 'MODE: reserved';

COMMENT ON COLUMN public."AccountingDocument"."User" IS 'MODE: reserved';

COMMENT ON COLUMN public."AccountingDocument"."BeginDate" IS 'MODE: reserved';

COMMENT ON COLUMN public."AccountingDocument"."Notes" IS 'MODE: read|DESCR: Notes|INDEX: 3';

-- PurchaseDeliveryNote

CREATE OR REPLACE FUNCTION public._opm_trigger_purchasedeliverynote_description()

RETURNS trigger AS

$BODY$

BEGIN

--RAISE NOTICE 'Trigger % on %', TG_NAME, TG_TABLE_NAME;

IF(TG_OP = 'INSERT') THEN

NEW."Code"= concat_ws('-','PDN',lpad(nextval('purchasedeliverynote_seq')::VARCHAR,9,'0'));

END IF;

NEW."Description" = concat_ws(' - ', NEW."Code", NEW."Title");

RETURN NEW;

END;

$BODY$

LANGUAGE plpgsql VOLATILE

COST 100;

DROP SEQUENCE public.purchasedeliverynote_seq;

CREATE SEQUENCE public.purchasedeliverynote_seq

INCREMENT 1

MINVALUE 1

MAXVALUE 999999999

START 1

CACHE 1;

COMMENT ON TABLE public."PurchaseDeliveryNote"

COMMENT ON COLUMN public."PurchaseDeliveryNote"."Id" IS 'MODE: reserved';

COMMENT ON COLUMN public."PurchaseDeliveryNote"."IdClass" IS 'MODE: reserved';

COMMENT ON COLUMN public."PurchaseDeliveryNote"."Status" IS 'MODE: reserved';

COMMENT ON COLUMN public."PurchaseDeliveryNote"."User" IS 'MODE: reserved';

COMMENT ON COLUMN public."PurchaseDeliveryNote"."BeginDate" IS 'MODE: reserved';

-- PurchaseInvoice

CREATE OR REPLACE FUNCTION public._opm_trigger_purchaseinvoice_description()

RETURNS trigger AS

$BODY$

BEGIN

--RAISE NOTICE 'Trigger % on %', TG_NAME, TG_TABLE_NAME;

IF(TG_OP = 'INSERT') THEN

NEW."Code"= concat_ws('-', 'PI',lpad(nextval('purchaseinvoice_seq')::VARCHAR,9,'0'));

END IF;

NEW."Description" = concat_ws(' - ', NEW."Code", NEW."Title");

RETURN NEW;

END;

$BODY$

LANGUAGE plpgsql VOLATILE

COST 100;

DROP SEQUENCE public.purchaseinvoice_seq;

CREATE SEQUENCE public.purchaseinvoice_seq

INCREMENT 1

MINVALUE 1

MAXVALUE 999999999

START 1

CACHE 1;

COMMENT ON TABLE public."PurchaseInvoice"

COMMENT ON COLUMN public."PurchaseInvoice"."Id" IS 'MODE: reserved';

COMMENT ON COLUMN public."PurchaseInvoice"."IdClass" IS 'MODE: reserved';

COMMENT ON COLUMN public."PurchaseInvoice"."Status" IS 'MODE: reserved';

COMMENT ON COLUMN public."PurchaseInvoice"."User" IS 'MODE: reserved';

COMMENT ON COLUMN public."PurchaseInvoice"."BeginDate" IS 'MODE: reserved';

-- PurchaseOrder

SELECT * FROM cm_delete_attribute('"PurchaseOrder"'::regclass, 'ScheduledStart');

SELECT * FROM cm_delete_attribute('"PurchaseOrder"'::regclass, 'ScheduledEnd');

CREATE OR REPLACE FUNCTION public._opm_trigger_purchaseorder_description()

RETURNS trigger AS

$BODY$

BEGIN

--RAISE NOTICE 'Trigger % on %', TG_NAME, TG_TABLE_NAME;

IF(TG_OP = 'INSERT') THEN

NEW."Code"= concat_ws('-', 'PO',lpad(nextval('purchaseorder_seq')::VARCHAR,9,'0'));

END IF;

NEW."Description" = concat_ws(' - ', NEW."Code", NEW."Title");

RETURN NEW;

END;

$BODY$

LANGUAGE plpgsql VOLATILE

COST 100;

DROP SEQUENCE public.purchaseorder_seq;

CREATE SEQUENCE public.purchaseorder_seq

INCREMENT 1

MINVALUE 1

MAXVALUE 999999999

START 1

CACHE 1;

COMMENT ON TABLE public."PurchaseOrder"

COMMENT ON COLUMN public."PurchaseOrder"."Id" IS 'MODE: reserved';

COMMENT ON COLUMN public."PurchaseOrder"."IdClass" IS 'MODE: reserved';

COMMENT ON COLUMN public."PurchaseOrder"."Status" IS 'MODE: reserved';

COMMENT ON COLUMN public."PurchaseOrder"."User" IS 'MODE: reserved';

COMMENT ON COLUMN public."PurchaseOrder"."BeginDate" IS 'MODE: reserved';

-- AccountingDocumentItem

COMMENT ON TABLE public."AccountingDocumentItem"

COMMENT ON COLUMN public."AccountingDocumentItem"."Id" IS 'MODE: reserved';

COMMENT ON COLUMN public."AccountingDocumentItem"."IdClass" IS 'MODE: reserved';

COMMENT ON COLUMN public."AccountingDocumentItem"."Status" IS 'MODE: reserved';

COMMENT ON COLUMN public."AccountingDocumentItem"."User" IS 'MODE: reserved';

COMMENT ON COLUMN public."AccountingDocumentItem"."BeginDate" IS 'MODE: reserved';

-- PurchaseInvoiceItem

ALTER TABLE public."PurchaseInvoiceItem"

ADD CONSTRAINT "_NotNull_Invoice" CHECK ("Status" <> 'A'::bpchar OR "Invoice" IS NOT NULL);

CREATE OR REPLACE FUNCTION public._opm_trigger_purchaseinvoiceitem_description()

RETURNS trigger AS

$BODY$

DECLARE

code character varying;

max_code integer;

parent_code character varying;

BEGIN

--RAISE NOTICE 'Trigger % on %', TG_NAME, TG_TABLE_NAME;

IF(TG_OP = 'INSERT') THEN

SELECT coalesce(MAX("Code"::integer),0) into max_code

FROM "PurchaseInvoiceItem"

WHERE "Invoice" = NEW."Invoice" AND "Status" = 'A';

NEW."Code"= trim(to_char(max_code + 1,'000'));

ELSEIF(TG_OP = 'UPDATE' AND NEW."Invoice" <> OLD."Invoice") THEN

SELECT coalesce(MAX("Code"::integer),0) into max_code

FROM "PurchaseInvoiceItem"

WHERE "Invoice" = NEW."Invoice" AND "Status" = 'A' AND "Id" <> NEW."Id";

NEW."Code"= trim(to_char(max_code + 1,'000'));

END IF;

SELECT "Code" INTO parent_code

FROM "PurchaseInvoice"

WHERE "Id" = NEW."Invoice";

NEW."Description" = concat_ws(' - ', concat_ws('/', parent_code,NEW."Code"), NEW."Title");

RETURN NEW;

END;

$BODY$

LANGUAGE plpgsql VOLATILE

COST 100;

COMMENT ON TABLE public."PurchaseInvoiceItem"

COMMENT ON COLUMN public."PurchaseInvoiceItem"."Id" IS 'MODE: reserved';

COMMENT ON COLUMN public."PurchaseInvoiceItem"."IdClass" IS 'MODE: reserved';

COMMENT ON COLUMN public."PurchaseInvoiceItem"."Status" IS 'MODE: reserved';

COMMENT ON COLUMN public."PurchaseInvoiceItem"."User" IS 'MODE: reserved';

COMMENT ON COLUMN public."PurchaseInvoiceItem"."BeginDate" IS 'MODE: reserved';

-- PurchaseOrderItem

ALTER TABLE public."PurchaseOrderItem" DROP CONSTRAINT "_NotNull_Quantity";

ALTER TABLE public."PurchaseOrderItem" DROP CONSTRAINT "_NotNull_QuantityUoM";

CREATE OR REPLACE FUNCTION public._opm_trigger_purchaseorderitem_description()

RETURNS trigger AS

$BODY$

DECLARE

code character varying;

max_code integer;

parent_code character varying;

BEGIN

--RAISE NOTICE 'Trigger % on %', TG_NAME, TG_TABLE_NAME;

IF(TG_OP = 'INSERT') THEN

SELECT coalesce(MAX("Code"::integer),0) into max_code

FROM "PurchaseOrderItem"

WHERE "Order" = NEW."Order" AND "Status"='A';

NEW."Code"= trim(to_char(max_code + 1,'000'));

ELSEIF(TG_OP = 'UPDATE' AND NEW."Order" <> OLD."Order") THEN

SELECT coalesce(MAX("Code"::integer),0) into max_code

FROM "PurchaseOrderItem"

WHERE "Order" = NEW."Order" AND "Status" = 'A' AND "Id" <> NEW."Id";

NEW."Code"= trim(to_char(max_code + 1,'000'));

END IF;

SELECT "Code" INTO parent_code

FROM "PurchaseOrder"

WHERE "Id" = NEW."Order";

NEW."Description" = concat_ws(' - ', concat_ws('/', parent_code,NEW."Code"), NEW."Title");

RETURN NEW;

END;

$BODY$

LANGUAGE plpgsql VOLATILE

COST 100;

COMMENT ON TABLE public."PurchaseOrderItem"

COMMENT ON COLUMN public."PurchaseOrderItem"."Id" IS 'MODE: reserved';

COMMENT ON COLUMN public."PurchaseOrderItem"."IdClass" IS 'MODE: reserved';

COMMENT ON COLUMN public."PurchaseOrderItem"."Status" IS 'MODE: reserved';

COMMENT ON COLUMN public."PurchaseOrderItem"."User" IS 'MODE: reserved';

COMMENT ON COLUMN public."PurchaseOrderItem"."BeginDate" IS 'MODE: reserved';

-- Budget

COMMENT ON TABLE public."Budget"

COMMENT ON COLUMN public."Budget"."Id" IS 'MODE: reserved';

COMMENT ON COLUMN public."Budget"."IdClass" IS 'MODE: reserved';

COMMENT ON COLUMN public."Budget"."Status" IS 'MODE: reserved';

COMMENT ON COLUMN public."Budget"."User" IS 'MODE: reserved';

COMMENT ON COLUMN public."Budget"."BeginDate" IS 'MODE: reserved';

-- BudgetCentre

ALTER TABLE public."BudgetCentre"

ADD CONSTRAINT "_NotNull_Budget" CHECK ("Status" <> 'A'::bpchar OR "Budget" IS NOT NULL);

DROP SEQUENCE public.budgetcentre_seq;

CREATE SEQUENCE public.budgetcentre_seq

INCREMENT 1

MINVALUE 1

MAXVALUE 9999

START 1

CACHE 1;

CREATE OR REPLACE FUNCTION public._opm_trigger_budgetcentre_description()

RETURNS trigger AS

$BODY$

DECLARE

budget_code character varying;

BEGIN

--RAISE NOTICE 'Trigger % on %', TG_NAME, TG_TABLE_NAME;

IF(TG_OP = 'INSERT') THEN

NEW."Code"= lpad(nextval('budgetcentre_seq')::VARCHAR,4,'0');

END IF;

SELECT concat_ws('/', "Code", NEW."Code") into budget_code

FROM "Budget"

WHERE "Id" = NEW."Budget" AND "Status"='A';

NEW."Description" = concat_ws('-', budget_code, NEW."Name");

RETURN NEW;

END;

$BODY$

LANGUAGE plpgsql VOLATILE

COST 100;

COMMENT ON TABLE public."BudgetCentre"

COMMENT ON COLUMN public."BudgetCentre"."Id" IS 'MODE: reserved';

COMMENT ON COLUMN public."BudgetCentre"."IdClass" IS 'MODE: reserved';

COMMENT ON COLUMN public."BudgetCentre"."Status" IS 'MODE: reserved';

COMMENT ON COLUMN public."BudgetCentre"."User" IS 'MODE: reserved';

COMMENT ON COLUMN public."BudgetCentre"."BeginDate" IS 'MODE: reserved';

-- BudgetItem

ALTER TABLE public."BudgetItem"

ADD CONSTRAINT "_NotNull_BudgetCentre" CHECK ("Status" <> 'A'::bpchar OR "BudgetCentre" IS NOT NULL);

CREATE OR REPLACE FUNCTION public._opm_trigger_budgetitem_description()

RETURNS trigger AS

$BODY$

DECLARE

code character varying;

parent_descr character varying;

max_code integer;

BEGIN

--RAISE NOTICE 'Trigger % on %', TG_NAME, TG_TABLE_NAME;

SELECT "Description" INTO parent_descr

FROM "BudgetCentre"

WHERE "Id" = NEW."BudgetCentre";

IF(TG_OP = 'INSERT') THEN

SELECT coalesce(MAX("Code"::integer),0) into max_code

FROM "BudgetItem"

WHERE "BudgetCentre" = NEW."BudgetCentre" AND "Status"='A';

NEW."Code"= trim(to_char(max_code+1,'000'));

ELSEIF(TG_OP = 'UPDATE') THEN

SELECT coalesce(MAX("Code"::integer),0) into max_code

FROM "BudgetItem"

WHERE "BudgetCentre" = NEW."BudgetCentre" AND "Status" = 'A' AND "Id" <> NEW."Id";

NEW."Code"= trim(to_char(max_code + 1,'000'));

END IF;

NEW."Description" = concat_ws(' - ', concat_ws('/', parent_descr,NEW."Code"), NEW."Name");

RETURN NEW;

END;

$BODY$

LANGUAGE plpgsql VOLATILE

COST 100;

COMMENT ON TABLE public."BudgetItem"

COMMENT ON COLUMN public."BudgetItem"."Id" IS 'MODE: reserved';

COMMENT ON COLUMN public."BudgetItem"."IdClass" IS 'MODE: reserved';

COMMENT ON COLUMN public."BudgetItem"."Status" IS 'MODE: reserved';

COMMENT ON COLUMN public."BudgetItem"."User" IS 'MODE: reserved';

COMMENT ON COLUMN public."BudgetItem"."BeginDate" IS 'MODE: reserved';

-- Certificate

COMMENT ON TABLE public."Certificate"

COMMENT ON COLUMN public."Certificate"."Id" IS 'MODE: reserved';

COMMENT ON COLUMN public."Certificate"."IdClass" IS 'MODE: reserved';

COMMENT ON COLUMN public."Certificate"."Status" IS 'MODE: reserved';

COMMENT ON COLUMN public."Certificate"."User" IS 'MODE: reserved';

COMMENT ON COLUMN public."Certificate"."BeginDate" IS 'MODE: reserved';

COMMENT ON COLUMN public."Certificate"."Notes" IS 'MODE: read|DESCR: Notes|INDEX: 3';

-- DisabilityAccess

COMMENT ON TABLE public."DisabilityAccess"

COMMENT ON COLUMN public."DisabilityAccess"."Id" IS 'MODE: reserved';

COMMENT ON COLUMN public."DisabilityAccess"."IdClass" IS 'MODE: reserved';

COMMENT ON COLUMN public."DisabilityAccess"."Status" IS 'MODE: reserved';

COMMENT ON COLUMN public."DisabilityAccess"."User" IS 'MODE: reserved';

COMMENT ON COLUMN public."DisabilityAccess"."BeginDate" IS 'MODE: reserved';

COMMENT ON COLUMN public."DisabilityAccess"."Notes" IS 'MODE: read|DESCR: Notes|INDEX: 3';

-- EnergyPerformance

COMMENT ON TABLE public."EnergyPerformance"

COMMENT ON COLUMN public."EnergyPerformance"."Id" IS 'MODE: reserved';

COMMENT ON COLUMN public."EnergyPerformance"."IdClass" IS 'MODE: reserved';

COMMENT ON COLUMN public."EnergyPerformance"."Status" IS 'MODE: reserved';

COMMENT ON COLUMN public."EnergyPerformance"."User" IS 'MODE: reserved';

COMMENT ON COLUMN public."EnergyPerformance"."BeginDate" IS 'MODE: reserved';

COMMENT ON COLUMN public."EnergyPerformance"."Notes" IS 'MODE: read|DESCR: Notes|INDEX: 3';

-- ClimaticZone

COMMENT ON TABLE public."ClimaticZone"

COMMENT ON COLUMN public."ClimaticZone"."Id" IS 'MODE: reserved';

COMMENT ON COLUMN public."ClimaticZone"."IdClass" IS 'MODE: reserved';

COMMENT ON COLUMN public."ClimaticZone"."Status" IS 'MODE: reserved';

COMMENT ON COLUMN public."ClimaticZone"."User" IS 'MODE: reserved';

COMMENT ON COLUMN public."ClimaticZone"."BeginDate" IS 'MODE: reserved';

COMMENT ON COLUMN public."ClimaticZone"."Notes" IS 'MODE: read|DESCR: Notes|INDEX: 3';

-- CostIssue

COMMENT ON TABLE public."CostIssue"

COMMENT ON COLUMN public."CostIssue"."Id" IS 'MODE: reserved';

COMMENT ON COLUMN public."CostIssue"."IdClass" IS 'MODE: reserved';

COMMENT ON COLUMN public."CostIssue"."Code" IS 'MODE: read|DESCR: Code|INDEX: 1|BASEDSP: true|CLASSORDER: 0';

COMMENT ON COLUMN public."CostIssue"."Description" IS 'MODE: read|DESCR: Description|INDEX: 2|BASEDSP: true|CLASSORDER: 0';

COMMENT ON COLUMN public."CostIssue"."Status" IS 'MODE: reserved';

COMMENT ON COLUMN public."CostIssue"."User" IS 'MODE: reserved';

COMMENT ON COLUMN public."CostIssue"."BeginDate" IS 'MODE: reserved';

COMMENT ON COLUMN public."CostIssue"."Notes" IS 'MODE: read|DESCR: Notes|INDEX: 3|CLASSORDER: 0';

COMMENT ON COLUMN public."CostIssue"."BudgetItem" IS 'REFERENCEDIRECT: false|REFERENCEDOM: BudgetItemCost|REFERENCETYPE: restrict|FILTER: from BudgetItem where BudgetCentre = {client:BudgetCentre}|STATUS: active|BASEDSP: false|CLASSORDER: 0|DESCR: Budget item|GROUP: |INDEX: 9|MODE: write|FIELDMODE: write';

-- InventoryItem

COMMENT ON TABLE public."InventoryItem"

COMMENT ON COLUMN public."InventoryItem"."Id" IS 'MODE: reserved';

COMMENT ON COLUMN public."InventoryItem"."IdClass" IS 'MODE: reserved';

COMMENT ON COLUMN public."InventoryItem"."Status" IS 'MODE: reserved';

COMMENT ON COLUMN public."InventoryItem"."User" IS 'MODE: reserved';

COMMENT ON COLUMN public."InventoryItem"."BeginDate" IS 'MODE: reserved';

COMMENT ON COLUMN public."InventoryItem"."Notes" IS 'MODE: read|DESCR: Notes|INDEX: 3';

-- Site

COMMENT ON TABLE public."Site"

COMMENT ON COLUMN public."Site"."Id" IS 'MODE: reserved';

COMMENT ON COLUMN public."Site"."IdClass" IS 'MODE: reserved';

COMMENT ON COLUMN public."Site"."Status" IS 'MODE: reserved';

COMMENT ON COLUMN public."Site"."User" IS 'MODE: reserved';

COMMENT ON COLUMN public."Site"."BeginDate" IS 'MODE: reserved';

COMMENT ON COLUMN public."Site"."Notes" IS 'MODE: read|DESCR: Notes|INDEX: 3';

COMMENT ON COLUMN public."Site"."Category" IS 'REFERENCEDIRECT: false|REFERENCEDOM: MaintenanceCategoryItem|REFERENCETYPE: restrict|FILTER: from MaintenanceCategory where Area=0{client:Area.Id}|STATUS: active|BASEDSP: false|CLASSORDER: 0|DESCR: Category|GROUP: Maintenance Data|INDEX: 15|MODE: write|FIELDMODE: write';

COMMENT ON COLUMN public."Site"."Group" IS 'REFERENCEDIRECT: false|REFERENCEDOM: MaintenanceGroupItem|REFERENCETYPE: restrict|FILTER: from MaintenanceGroup where Category=0{client:Category.Id}|STATUS: active|BASEDSP: false|CLASSORDER: 0|DESCR: Group|GROUP: Maintenance Data|INDEX: 16|MODE: write|FIELDMODE: write';

-- Building

CREATE OR REPLACE FUNCTION public._opm_building()

RETURNS trigger AS

$BODY$

DECLARE

complex character varying;

BEGIN

--RAISE NOTICE 'Trigger % on %', TG_NAME, TG_TABLE_NAME;

SELECT INTO complex c."Code"

FROM "Complex" c

WHERE c."Id"=NEW."Complex" AND c."Status"='A';

NEW."Description" = concat_ws(' - ', complex, concat_ws(' ', NEW."Code", NEW."Name"));

NEW."IsInComplex" = NEW."Complex";

RETURN NEW;

END;

$BODY$

LANGUAGE plpgsql VOLATILE

COST 100;

CREATE OR REPLACE FUNCTION public._opm_count_buildings_of_complex()

RETURNS trigger AS

$BODY$

DECLARE

new_value INTEGER;

old_value integer;

BEGIN

--RAISE NOTICE 'Trigger % on %', TG_NAME, TG_TABLE_NAME;

IF (TG_OP = 'UPDATE' AND OLD."Complex" IS NOT NULL) THEN

SELECT "Buildings" INTO old_value

FROM "Complex"

WHERE "Id" = OLD."Complex";

SELECT COUNT(*) INTO new_value

FROM "Map_ComplexBuilding"

WHERE "IdObj1" = OLD."Complex" AND "Status"='A';

old_value = coalesce(old_value,0);

new_value = coalesce(new_value,0);

--RAISE NOTICE 'there were % buildings in complex %', old_value, OLD."Complex";

--RAISE NOTICE 'there are % buildings in complex %', new_value, OLD."Complex";

IF(old_value <> new_value)THEN

UPDATE "Complex"

SET "Buildings" = new_value

WHERE "Id" = OLD."Complex";

END IF;

IF(NEW."Complex" IS NOT NULL) THEN

SELECT "Buildings" INTO old_value

FROM "Complex"

WHERE "Id" = NEW."Complex";

SELECT COUNT(*) INTO new_value

FROM "Map_ComplexBuilding"

WHERE "IdObj1" = NEW."Complex" AND "Status"='A';

old_value = coalesce(old_value,0);

new_value = coalesce(new_value,0);

--RAISE NOTICE 'there were % buildings in complex %', old_value, NEW."Complex";

--RAISE NOTICE 'there are % buildings in complex %', new_value, NEW."Complex";

IF(old_value <> new_value)THEN

UPDATE "Complex"

SET "Buildings" = new_value

WHERE "Id"=NEW."Complex";

END IF;

RETURN null;

END;

$BODY$

LANGUAGE plpgsql VOLATILE

COST 100;

COMMENT ON TABLE public."Building"

COMMENT ON COLUMN public."Building"."Id" IS 'MODE: reserved';

COMMENT ON COLUMN public."Building"."IdClass" IS 'MODE: reserved';

COMMENT ON COLUMN public."Building"."Status" IS 'MODE: reserved';

COMMENT ON COLUMN public."Building"."User" IS 'MODE: reserved';

COMMENT ON COLUMN public."Building"."BeginDate" IS 'MODE: reserved';

COMMENT ON COLUMN public."Building"."Category" IS 'REFERENCEDIRECT: false|REFERENCEDOM: MaintenanceCategoryItem|REFERENCETYPE: restrict|FILTER: from MaintenanceCategory where Area=0{client:Area.Id}|STATUS: active|BASEDSP: false|CLASSORDER: 0|DESCR: Category|GROUP: Maintenance Data|INDEX: 18|MODE: write|FIELDMODE: write';

COMMENT ON COLUMN public."Building"."Group" IS 'REFERENCEDIRECT: false|REFERENCEDOM: MaintenanceGroupItem|REFERENCETYPE: restrict|FILTER: from MaintenanceGroup where Category=0{client:Category.Id}|STATUS: active|BASEDSP: false|CLASSORDER: 0|DESCR: Group|GROUP: Maintenance Data|INDEX: 19|MODE: write|FIELDMODE: write';

-- Complex

COMMENT ON TABLE public."Complex"

COMMENT ON COLUMN public."Complex"."Id" IS 'MODE: reserved';

COMMENT ON COLUMN public."Complex"."IdClass" IS 'MODE: reserved';

COMMENT ON COLUMN public."Complex"."Status" IS 'MODE: reserved';

COMMENT ON COLUMN public."Complex"."User" IS 'MODE: reserved';

COMMENT ON COLUMN public."Complex"."BeginDate" IS 'MODE: reserved';

COMMENT ON COLUMN public."Complex"."Notes" IS 'MODE: read|DESCR: Notes|INDEX: 3';

COMMENT ON COLUMN public."Complex"."Category" IS 'REFERENCEDIRECT: false|REFERENCEDOM: MaintenanceCategoryItem|REFERENCETYPE: restrict|FILTER: from MaintenanceCategory where Area=0{client:Area.Id}|STATUS: active|BASEDSP: false|CLASSORDER: 0|DESCR: Category|GROUP: Maintenance Data|INDEX: 15|MODE: write|FIELDMODE: write';

COMMENT ON COLUMN public."Complex"."Group" IS 'REFERENCEDIRECT: false|REFERENCEDOM: MaintenanceGroupItem|REFERENCETYPE: restrict|FILTER: from MaintenanceGroup where Category=0{client:Category.Id}|STATUS: active|BASEDSP: false|CLASSORDER: 0|DESCR: Group|GROUP: Maintenance Data|INDEX: 16|MODE: write|FIELDMODE: write';

]; nested exception is org.postgresql.util.PSQLException: ERROR: relation "public.AccountingDocument" does not exist

at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:231)

at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73)

at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:415)

at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:439)

at org.cmdbuild.services.DefaultPatchManager$4.doInTransactionWithoutResult(DefaultPatchManager.java:378)

at org.springframework.transaction.support.TransactionCallbackWithoutResult.doInTransaction(TransactionCallbackWithoutResult.java:34)

at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:133)

at org.cmdbuild.services.DefaultPatchManager.applyPatch(DefaultPatchManager.java:372)

at org.cmdbuild.services.DefaultPatchManager.applyPatchList(DefaultPatchManager.java:360)

at org.cmdbuild.services.startup.DefaultStartupLogic.migrate(DefaultStartupLogic.java:31)

at org.cmdbuild.servlets.json.Configure.applyPatches(Configure.java:214)

at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)

at java.lang.reflect.Method.invoke(Unknown Source)

at org.cmdbuild.servlets.JSONDispatcher.dispatch(JSONDispatcher.java:104)

at org.cmdbuild.servlets.JSONDispatcher.doPost(JSONDispatcher.java:64)

at javax.servlet.http.HttpServlet.service(HttpServlet.java:661)

at javax.servlet.http.HttpServlet.service(HttpServlet.java:742)

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:231)

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)

at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)

at org.cmdbuild.filters.AuthFilter.doFilter(AuthFilter.java:183)

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:193)

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)

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:193)

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)

at org.cmdbuild.filters.ConfCheckFilter.doFilter(ConfCheckFilter.java:31)

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)

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:193)

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)

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:193)

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)

at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:199)

at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96)

at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:140)

at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:81)

at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:650)

at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:87)

at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:342)

at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:803)

at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:66)

at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:790)

at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1459)

at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)

at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)

at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)

at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)

at java.lang.Thread.run(Unknown Source)

Caused by: org.postgresql.util.PSQLException: ERROR: relation "public.AccountingDocument" does not exist

at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2422)

at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2167)

at org.postgresql.core.v3.QueryExecutorImpl.flushIfDeadlockRisk(QueryExecutorImpl.java:1297)

at org.postgresql.core.v3.QueryExecutorImpl.sendQuery(QueryExecutorImpl.java:1332)

at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:298)

at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:441)

at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:365)

at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:307)

at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:293)

at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:270)

at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:266)

at org.apache.tomcat.dbcp.dbcp.DelegatingStatement.execute(DelegatingStatement.java:264)

at org.springframework.jdbc.core.JdbcTemplate$1ExecuteStatementCallback.doInStatement(JdbcTemplate.java:431)

at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:404)

... 60 more

ERROR 2018-04-13 15:28:14 [jsonrpc ] A org.cmdbuild.exception.ORMException occurred calling method class org.cmdbuild.servlets.json.Configure.applyPatches: ORM_SQL_PATCH

org.cmdbuild.exception.ORMException: ORM_SQL_PATCH

at org.cmdbuild.exception.ORMException$ORMExceptionType.createException(ORMException.java:63)

at org.cmdbuild.services.DefaultPatchManager.applyPatch(DefaultPatchManager.java:391)