[dcm4che-commits] SF.net SVN: dcm4che:[6696] dcm4chee/dcm4chee-arc/trunk/dcm4jboss-ejb/src/ etc/db2/sql

[<gun...@us...>]

Revision: 6696
          http://dcm4che.svn.sourceforge.net/dcm4che/?rev=6696&view=rev
Author:   gunterze
Date:     2008-07-31 12:39:54 +0000 (Thu, 31 Jul 2008)

Log Message:
-----------
[#DCMEE-898] Use SQL type for pk fields suitable to hold 64 bit integer in all supported DBs

Modified Paths:
--------------
    dcm4chee/dcm4chee-arc/trunk/dcm4jboss-ejb/src/etc/db2/sql/update-2.14.db2sql

Added Paths:
-----------
    dcm4chee/dcm4chee-arc/trunk/dcm4jboss-ejb/src/etc/db2/sql/update-2.14.sh

Modified: dcm4chee/dcm4chee-arc/trunk/dcm4jboss-ejb/src/etc/db2/sql/update-2.14.db2sql
===================================================================
--- dcm4chee/dcm4chee-arc/trunk/dcm4jboss-ejb/src/etc/db2/sql/update-2.14.db2sql	2008-07-31 11:35:13 UTC (rev 6695)
+++ dcm4chee/dcm4chee-arc/trunk/dcm4jboss-ejb/src/etc/db2/sql/update-2.14.db2sql	2008-07-31 12:39:54 UTC (rev 6696)
@@ -1,9 +1,408 @@
 -- Update DB from dcm4chee-2.13.x to dcm4chee-2.14.x
+-- Invoked by update-2.14.sh
 
-CONNECT TO pacsdb;
-SET CURRENT SCHEMA pacsdb;
-
 DROP INDEX pat_id;
 CREATE INDEX pat_id ON patient(pat_id, pat_id_issuer);
 
 CREATE INDEX mpps_drcode_fk ON mpps(drcode_fk);
+
+ALTER TABLE ae
+  ALTER pk DROP IDENTITY;
+
+ALTER TABLE ae
+  ALTER pk SET DATA TYPE BIGINT;
+
+ALTER TABLE ae
+  ALTER pk SET GENERATED BY DEFAULT AS IDENTITY;
+
+REORG TABLE ae;
+
+ALTER TABLE code
+  ALTER pk DROP IDENTITY;
+
+ALTER TABLE code
+  ALTER pk SET DATA TYPE BIGINT;
+
+ALTER TABLE code
+  ALTER pk SET GENERATED BY DEFAULT AS IDENTITY;
+
+REORG TABLE code;
+
+ALTER TABLE patient
+  ALTER pk DROP IDENTITY;
+
+ALTER TABLE patient
+  ALTER pk SET DATA TYPE BIGINT
+  ALTER merge_fk SET DATA TYPE BIGINT;
+
+ALTER TABLE patient
+  ALTER pk SET GENERATED BY DEFAULT AS IDENTITY;
+
+REORG TABLE patient;
+
+ALTER TABLE other_pid
+  ALTER pk DROP IDENTITY;
+
+ALTER TABLE other_pid
+  ALTER pk SET DATA TYPE BIGINT;
+
+ALTER TABLE other_pid
+  ALTER pk SET GENERATED BY DEFAULT AS IDENTITY;
+
+REORG TABLE other_pid;
+
+ALTER TABLE rel_pat_other_pid
+  ALTER patient_fk SET DATA TYPE BIGINT
+  ALTER other_pid_fk SET DATA TYPE BIGINT;
+
+REORG TABLE rel_pat_other_pid;
+
+ALTER TABLE study
+  ALTER pk DROP IDENTITY;
+
+ALTER TABLE study
+  ALTER pk SET DATA TYPE BIGINT
+  ALTER patient_fk SET DATA TYPE BIGINT;
+
+ALTER TABLE study
+  ALTER pk SET GENERATED BY DEFAULT AS IDENTITY;
+
+REORG TABLE study;
+
+ALTER TABLE rel_study_pcode
+  ALTER study_fk SET DATA TYPE BIGINT
+  ALTER pcode_fk SET DATA TYPE BIGINT;
+
+REORG TABLE rel_study_pcode;
+
+ALTER TABLE study_permission
+  ALTER pk DROP IDENTITY;
+
+ALTER TABLE study_permission
+  ALTER pk SET DATA TYPE BIGINT;
+
+ALTER TABLE study_permission
+  ALTER pk SET GENERATED BY DEFAULT AS IDENTITY;
+
+REORG TABLE study_permission;
+
+ALTER TABLE mpps
+  ALTER pk DROP IDENTITY;
+
+ALTER TABLE mpps
+  ALTER pk SET DATA TYPE BIGINT
+  ALTER patient_fk SET DATA TYPE BIGINT
+  ALTER drcode_fk SET DATA TYPE BIGINT;
+
+ALTER TABLE mpps
+  ALTER pk SET GENERATED BY DEFAULT AS IDENTITY;
+
+REORG TABLE mpps;
+
+ALTER TABLE series
+  ALTER pk DROP IDENTITY;
+
+ALTER TABLE series
+  ALTER pk SET DATA TYPE BIGINT
+  ALTER study_fk SET DATA TYPE BIGINT
+  ALTER mpps_fk SET DATA TYPE BIGINT;
+
+ALTER TABLE series
+  ALTER pk SET GENERATED BY DEFAULT AS IDENTITY;
+
+REORG TABLE series;
+
+ALTER TABLE series_req
+  ALTER pk DROP IDENTITY;
+
+ALTER TABLE series_req
+  ALTER pk SET DATA TYPE BIGINT
+  ALTER series_fk SET DATA TYPE BIGINT;
+
+ALTER TABLE series_req
+  ALTER pk SET GENERATED BY DEFAULT AS IDENTITY;
+
+REORG TABLE series_req;
+
+ALTER TABLE media
+  ALTER pk DROP IDENTITY;
+
+ALTER TABLE media
+  ALTER pk SET DATA TYPE BIGINT;
+
+REORG TABLE media;
+
+ALTER TABLE instance
+  ALTER pk DROP IDENTITY;
+
+ALTER TABLE instance
+  ALTER pk SET DATA TYPE BIGINT
+  ALTER series_fk SET DATA TYPE BIGINT
+  ALTER srcode_fk SET DATA TYPE BIGINT
+  ALTER media_fk SET DATA TYPE BIGINT;
+
+ALTER TABLE instance
+  ALTER pk SET GENERATED BY DEFAULT AS IDENTITY;
+
+REORG TABLE instance;
+
+ALTER TABLE verify_observer
+  ALTER pk DROP IDENTITY;
+
+ALTER TABLE verify_observer
+  ALTER pk SET DATA TYPE BIGINT
+  ALTER instance_fk SET DATA TYPE BIGINT;
+
+ALTER TABLE verify_observer
+  ALTER pk SET GENERATED BY DEFAULT AS IDENTITY;
+
+REORG TABLE verify_observer;
+
+ALTER TABLE filesystem
+  ALTER pk DROP IDENTITY;
+
+ALTER TABLE filesystem
+  ALTER pk SET DATA TYPE BIGINT
+  ALTER next_fk SET DATA TYPE BIGINT;
+
+ALTER TABLE filesystem
+  ALTER pk SET GENERATED BY DEFAULT AS IDENTITY;
+
+REORG TABLE filesystem;
+
+ALTER TABLE files
+  ALTER pk DROP IDENTITY;
+
+ALTER TABLE files
+  ALTER pk SET DATA TYPE BIGINT
+  ALTER instance_fk SET DATA TYPE BIGINT
+  ALTER filesystem_fk SET DATA TYPE BIGINT;
+
+ALTER TABLE files
+  ALTER pk SET GENERATED BY DEFAULT AS IDENTITY;
+
+REORG TABLE files;
+
+ALTER TABLE study_on_fs
+  ALTER pk DROP IDENTITY;
+
+ALTER TABLE study_on_fs
+  ALTER pk SET DATA TYPE BIGINT
+  ALTER study_fk SET DATA TYPE BIGINT
+  ALTER filesystem_fk SET DATA TYPE BIGINT;
+
+ALTER TABLE study_on_fs
+  ALTER pk SET GENERATED BY DEFAULT AS IDENTITY;
+
+REORG TABLE study_on_fs;
+
+ALTER TABLE mwl_item
+  ALTER pk DROP IDENTITY;
+
+ALTER TABLE mwl_item
+  ALTER pk SET DATA TYPE BIGINT
+  ALTER patient_fk SET DATA TYPE BIGINT;
+
+ALTER TABLE mwl_item
+  ALTER pk SET GENERATED BY DEFAULT AS IDENTITY;
+
+REORG TABLE mwl_item;
+
+ALTER TABLE gpsps
+  ALTER pk DROP IDENTITY;
+
+ALTER TABLE gpsps
+  ALTER pk SET DATA TYPE BIGINT
+  ALTER patient_fk SET DATA TYPE BIGINT
+  ALTER code_fk SET DATA TYPE BIGINT;
+
+ALTER TABLE gpsps
+  ALTER pk SET GENERATED BY DEFAULT AS IDENTITY;
+
+REORG TABLE gpsps;
+
+ALTER TABLE rel_gpsps_appcode
+  ALTER gpsps_fk SET DATA TYPE BIGINT
+  ALTER appcode_fk SET DATA TYPE BIGINT;
+
+REORG TABLE rel_gpsps_appcode;
+
+ALTER TABLE rel_gpsps_devname
+  ALTER gpsps_fk SET DATA TYPE BIGINT
+  ALTER devname_fk SET DATA TYPE BIGINT;
+
+REORG TABLE rel_gpsps_devname;
+
+ALTER TABLE rel_gpsps_devclass
+  ALTER gpsps_fk SET DATA TYPE BIGINT
+  ALTER devclass_fk SET DATA TYPE BIGINT;
+
+REORG TABLE rel_gpsps_devclass;
+
+ALTER TABLE rel_gpsps_devloc
+  ALTER gpsps_fk SET DATA TYPE BIGINT
+  ALTER devloc_fk SET DATA TYPE BIGINT;
+
+REORG TABLE rel_gpsps_devloc;
+
+ALTER TABLE gpsps_perf
+  ALTER pk DROP IDENTITY;
+
+ALTER TABLE gpsps_perf
+  ALTER pk SET DATA TYPE BIGINT
+  ALTER gpsps_fk SET DATA TYPE BIGINT
+  ALTER code_fk SET DATA TYPE BIGINT;
+
+ALTER TABLE gpsps_perf
+  ALTER pk SET GENERATED BY DEFAULT AS IDENTITY;
+
+REORG TABLE gpsps_perf;
+
+ALTER TABLE gpsps_req
+  ALTER pk DROP IDENTITY;
+
+ALTER TABLE gpsps_req
+  ALTER pk SET DATA TYPE BIGINT
+  ALTER gpsps_fk SET DATA TYPE BIGINT;
+
+ALTER TABLE gpsps_req
+  ALTER pk SET GENERATED BY DEFAULT AS IDENTITY;
+
+REORG TABLE gpsps_req;
+
+ALTER TABLE gppps
+  ALTER pk DROP IDENTITY;
+
+ALTER TABLE gppps
+  ALTER pk SET DATA TYPE BIGINT
+  ALTER patient_fk SET DATA TYPE BIGINT;
+
+ALTER TABLE gppps
+  ALTER pk SET GENERATED BY DEFAULT AS IDENTITY;
+
+REORG TABLE gppps;
+
+ALTER TABLE rel_gpsps_gppps
+  ALTER gpsps_fk SET DATA TYPE BIGINT
+  ALTER gppps_fk SET DATA TYPE BIGINT;
+
+REORG TABLE rel_gpsps_gppps;
+
+ALTER TABLE hp
+  ALTER pk DROP IDENTITY;
+
+ALTER TABLE hp
+  ALTER pk SET DATA TYPE BIGINT
+  ALTER user_fk SET DATA TYPE BIGINT;
+
+ALTER TABLE hp
+  ALTER pk SET GENERATED BY DEFAULT AS IDENTITY;
+
+REORG TABLE hp;
+
+ALTER TABLE hpdef
+  ALTER pk DROP IDENTITY;
+
+ALTER TABLE hpdef
+  ALTER pk SET DATA TYPE BIGINT
+  ALTER hp_fk SET DATA TYPE BIGINT;
+
+ALTER TABLE hpdef
+  ALTER pk SET GENERATED BY DEFAULT AS IDENTITY;
+
+REORG TABLE hpdef;
+
+ALTER TABLE rel_hpdef_region
+  ALTER hpdef_fk SET DATA TYPE BIGINT
+  ALTER region_fk SET DATA TYPE BIGINT;
+
+REORG TABLE rel_hpdef_region;
+
+ALTER TABLE rel_hpdef_proc
+  ALTER hpdef_fk SET DATA TYPE BIGINT
+  ALTER proc_fk SET DATA TYPE BIGINT;
+
+REORG TABLE rel_hpdef_proc;
+
+ALTER TABLE rel_hpdef_reason
+  ALTER hpdef_fk SET DATA TYPE BIGINT
+  ALTER reason_fk SET DATA TYPE BIGINT;
+
+REORG TABLE rel_hpdef_reason;
+
+ALTER TABLE priv_patient
+  ALTER pk DROP IDENTITY;
+
+ALTER TABLE priv_patient
+  ALTER pk SET DATA TYPE BIGINT;
+
+ALTER TABLE priv_patient
+  ALTER pk SET GENERATED BY DEFAULT AS IDENTITY;
+
+REORG TABLE priv_patient;
+
+ALTER TABLE priv_study
+  ALTER pk DROP IDENTITY;
+
+ALTER TABLE priv_study
+  ALTER pk SET DATA TYPE BIGINT
+  ALTER patient_fk SET DATA TYPE BIGINT;
+
+ALTER TABLE priv_study
+  ALTER pk SET GENERATED BY DEFAULT AS IDENTITY;
+
+REORG TABLE priv_study;
+
+ALTER TABLE priv_series
+  ALTER pk DROP IDENTITY;
+
+ALTER TABLE priv_series
+  ALTER pk SET DATA TYPE BIGINT
+  ALTER study_fk SET DATA TYPE BIGINT;
+
+ALTER TABLE priv_series
+  ALTER pk SET GENERATED BY DEFAULT AS IDENTITY;
+
+REORG TABLE priv_series;
+
+ALTER TABLE priv_instance
+  ALTER pk DROP IDENTITY;
+
+ALTER TABLE priv_instance
+  ALTER pk SET DATA TYPE BIGINT
+  ALTER series_fk SET DATA TYPE BIGINT;
+
+ALTER TABLE priv_instance
+  ALTER pk SET GENERATED BY DEFAULT AS IDENTITY;
+
+REORG TABLE priv_instance;
+
+ALTER TABLE priv_file
+  ALTER pk DROP IDENTITY;
+
+ALTER TABLE priv_file
+  ALTER pk SET DATA TYPE BIGINT
+  ALTER instance_fk SET DATA TYPE BIGINT
+  ALTER filesystem_fk SET DATA TYPE BIGINT;
+
+ALTER TABLE priv_file
+  ALTER pk SET GENERATED BY DEFAULT AS IDENTITY;
+
+REORG TABLE priv_file;
+
+ALTER TABLE device
+  ALTER pk DROP IDENTITY;
+
+ALTER TABLE device
+  ALTER pk SET DATA TYPE BIGINT;
+
+ALTER TABLE device
+  ALTER pk SET GENERATED BY DEFAULT AS IDENTITY;
+
+REORG TABLE device;
+
+ALTER TABLE rel_dev_proto
+  ALTER device_fk SET DATA TYPE BIGINT
+  ALTER prcode_fk SET DATA TYPE BIGINT;
+
+REORG TABLE rel_dev_proto;

Added: dcm4chee/dcm4chee-arc/trunk/dcm4jboss-ejb/src/etc/db2/sql/update-2.14.sh
===================================================================
--- dcm4chee/dcm4chee-arc/trunk/dcm4jboss-ejb/src/etc/db2/sql/update-2.14.sh	                        (rev 0)
+++ dcm4chee/dcm4chee-arc/trunk/dcm4jboss-ejb/src/etc/db2/sql/update-2.14.sh	2008-07-31 12:39:54 UTC (rev 6696)
@@ -0,0 +1,23 @@
+#!/bin/bash
+# Update DB from dcm4chee-2.13.x to dcm4chee-2.14.x
+
+db2 CONNECT TO pacsdb
+db2 SET SCHEMA pacsdb
+
+db2 -stf update-2.14.db2sql
+
+tables="ae code patient other_pid study study_permission mpps series \
+  series_req media instance verify_observer filesystem files study_on_fs \
+  mwl_item gpsps gpsps_perf gpsps_req gppps hp hpdef priv_patient priv_study \
+  priv_series priv_instance priv_file device"
+
+
+for t in $tables ; 
+do
+  pk_start=`db2 -x "SELECT MAX(pk)+1 FROM $t"`
+  if [ $pk_start != "-" ]; then
+    db2 -v "ALTER TABLE $t ALTER COLUMN pk RESTART WITH $pk_start";
+  fi
+done
+
+db2 TERMINATE


This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.