XNAT 1.9.0 SQL Hotfix for CT Scan Column Change

In XNAT-8143, we introduced a quick fix to the CT Scan history table to account for long entries. This has a corresponding change in SQL to change a VARCHAR column to TEXT, but in our final release testing for XNAT 1.9.0, we discovered that this change is not being automatically handled. This will be addressed in XNAT 1.9.1, but for any users affected by this issue, here is a manual fix that can be run on your local system.

1. Save the text below to a file, e.g. xnat-8205-fix.sql, or download this attachment xnat-8205-fix.sql:

   SQL

   -- Remove dependencies so they don't block altering columns
   SELECT dependencies_save_and_drop('xnat_ctscandata');
   
   --Fix xnat_ctscandata_history table.
   
   ALTER TABLE xnat_ctscandata_history ADD COLUMN parameters_options_cp varchar;
   
   UPDATE xnat_ctscandata_history SET parameters_options_cp = parameters_options;
   
   ALTER TABLE xnat_ctscandata_history DROP COLUMN parameters_options;
   
   ALTER TABLE xnat_ctscandata_history ADD COLUMN parameters_options TEXT;
   
   UPDATE xnat_ctscandata_history SET parameters_options = CAST(parameters_options_cp AS TEXT);
   
   ALTER TABLE xnat_ctscandata_history DROP COLUMN parameters_options_cp;
   
   CREATE OR REPLACE FUNCTION after_update_xnat_ctscandata()  RETURNS TRIGGER AS '    begin        RETURN NULL;     end; '   LANGUAGE 'plpgsql' VOLATILE;
   
   --Fix xnat_ctscandata table.
   
   ALTER TABLE xnat_ctscandata ADD COLUMN parameters_options_cp varchar;
   
   UPDATE xnat_ctscandata SET parameters_options_cp = parameters_options;
   
   ALTER TABLE xnat_ctscandata DROP COLUMN parameters_options;
   
   ALTER TABLE xnat_ctscandata ADD COLUMN parameters_options TEXT;
   
   UPDATE xnat_ctscandata SET parameters_options = CAST(parameters_options_cp AS TEXT);
   
   ALTER TABLE xnat_ctscandata DROP COLUMN parameters_options_cp;
   
   -- Restore dependencies
   SELECT dependencies_restore('xnat_ctscandata');

2. Shut down your Tomcat server(s).

3. Run the SQL fix using psql (note that the value for the --username parameter depends on the username used to access the database for your XNAT instance; if the database name is different from the username, you’ll also need to add --dbname=XXXfor that value, where XXXis the actual database name):

   BASH

   # psql --username=xnat --file=xnat-8205-fix.sql
    dependencies_save_and_drop
   ----------------------------
                             1
   (1 row)
   
   ALTER TABLE
   UPDATE 0
   ALTER TABLE
   ALTER TABLE
   UPDATE 0
   ALTER TABLE
   CREATE FUNCTION
   ALTER TABLE
   UPDATE 3
   ALTER TABLE
   ALTER TABLE
   UPDATE 3
   ALTER TABLE
    dependencies_restore
   ----------------------
                       1
   (1 row)

4. Restart Tomcat.