dcm4chee-2.18.0 upgrade / SQL script issues

[leogrande]

I am trying to upgrade my current 2.17.3 / PostgreSQL to 2.18.0.

It looks like there is a typo in update-2.18.0.psql:
===================================================================
-- Update DB from dcm4chee-2.17.x to dcm4chee-2.18.x

CREATE TABLE published_study (
    pk                SERIAL8 NOT NULL CONSTRAINT published_study_pk PRIMARY KEY,
    study_fk          INT8,
    created_time      TIMESTAMP,
    updated_time      TIMESTAMP,
    doc_uid           TEXT,
    docentry_uid      TEXT,
    repository_uid    TEXT,
    status            INTEGER NOT NULL,
FOREIGN KEY (study_fk) REFERENCES study(pk)
);
CREATE INDEX published_study ON published_study(study_fk);
CREATE INDEX published_study_status ON published_study(status);
CREATE INDEX repository_uid ON published_study(repository_uid);
=======================================================
This string looks suspicious to me:

CREATE INDEX published_study ON published_study(study_fk);

What is the correct index for the published_study(study_fk) ?

If you are running this script against the PostgreSQL database it is throwing the error about already used relation (published_study). The name of the index the same as the table's name.


I removed this string from the script and it ran successfully, but ...

Without the new table "published_study" upgrade will not succeed.

Another problem that I have encountered was failed deployment of dcm4chee-web-ear-3.0.4-psql.ear.
Currently I am using dcm4chee-web-ear-3.0.1-psql.ear.

After hours of struggling with this annoyance, I decided to switch from jboss-4.2.3.GA-jdk6 to jboss-4.2.3.GA (JDK5) (jboss-4.2.3.GA.zip).

At least it helped me to upgrade to 2.18.0.
But the only problem that persists now is the new table 'published_study' with the missing index for published_study(study_fk).

Any idea on this issue, guys?

[Alexander Sanz]

Hi Leo.

Just try to change the line

CREATE INDEX published_study ON published_study(study_fk);

by this new one ( or just add _fk)

CREATE INDEX published_study_fk ON published_study(study_fk);

I think it should work!

Best regards,

[leogrande]

Hello Alexander,

If you mean to run this update script without errors, yes, it should work. As long as this index has a unique name, of course, it should work.
But we need to know for sure what index name developer meant. The guessing strategy is not very helpful in this case, right? 

Do you really know that published_study_fk is the right one?