Versions Compared

Old Version 3

changes.mady.by.user Sam Tsoi

Saved on

compared with

New Version 4

changes.mady.by.user Sam Tsoi

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

 

Code Block
languagesql
themerdark
bash-4.1$ psql
postgres=# \c i2b2pm
i2b2pm=# \d+ pm_user_login
                           Table "public.pm_user_login"
    Column     |            Type             | Modifiers | Storage  | Description
---------------+-----------------------------+-----------+----------+-------------
 user_id       | character varying(50)       | not null  | extended |
 attempt_cd    | character varying(50)       | not null  | extended |
 entry_date    | timestamp without time zone | not null  | plain    |
 changeby_char | character varying(50)       |           | extended |
 status_cd     | character varying(50)       |           | extended |
Indexes:
    "pm_user_login_pkey" PRIMARY KEY, btree (entry_date, user_id)
Has OIDs: no
 
i2b2pm=# ALTER TABLE "pm_user_login" DROP CONSTRAINT "pm_user_login_pkey" ;
ALTER TABLE
 
i2b2pm=# \d+ pm_user_login
                           Table "public.pm_user_login"
    Column     |            Type             | Modifiers | Storage  | Description
---------------+-----------------------------+-----------+----------+-------------
 user_id       | character varying(50)       | not null  | extended |
 attempt_cd    | character varying(50)       | not null  | extended |
 entry_date    | timestamp without time zone | not null  | plain    |
 changeby_char | character varying(50)       |           | extended |
 status_cd     | character varying(50)       |           | extended |
Has OIDs: no

 

Allow for Many More Types of Status from the i2b2 CRC

Change the shrine_query_history's QUERY_RESULT's status column's type from an enum to a text field to accommodate many more types from the i2b2 CRC. This will allow some results previously interpreted as errors to be interpreted as QUEUED. See SHRINE-2715.

 

For Mysql:

 

Code Block
languagesql
themerdark
mysql> use shrine_query_history;
mysql> alter table 

mysql> alter table QUERY_RESULT change status status
status 
 varchar(30)
 
 not
 
 null;

 

 

For Oracle:

 

Code Block
languagesql
themerdark
-- To find the constraint name on the status column:

SELECT
 
 COLUMN_NAME,CONSTRAINT_NAME,TABLE_
NAME 
NAME FROM
 
 user_cons_
columns 
columns WHERE
 
 TABLE_NAME =
 
 'QUERY_RESULT';

-- To drop that constraint:

ALTER
 
 TABLE
 
 QUERY_
RESULT 
RESULT DROP
 
 CONSTRAINT
 
 <constraint_name_for_status>;

 

 

For MS SQL Server:

 

Code Block
languagesql
themerdark
USE shrine_query_history;
  

  
-- To find the constraint name on the status column:
 
SELECT * FROM 

 
SELECT * FROM INFORMATION_SCHEMA.TABLE_
CONSTRAINTS 
CONSTRAINTS WHERE
 
 TABLE_NAME=
 
 'QUERY_RESULT';
 

 
-- To drop that constraint:
 
ALTER TABLE QUERY_RESULT DROP CONSTRAINT 

 
ALTER TABLE QUERY_RESULT DROP CONSTRAINT <constraint_name_for_status>;

 

 

Changes to the context.xml file

...