Home : Database Upgrade 7.7.2
Q14509 - INFO: Database Upgrade 7.7.2
With the release of WIMS Client 7.7.2 a database upgrade was required.  The upgrade is automatically applied when logging into a Facility Database for the first time with the WIMS 7.7.2 client. 

Tech Notes:

SQL Server Scripts:

OPSROOT

CREATE TABLE [dbo].[ANALYTECODES]([CODE] [nvarchar](10) NULL,[NAME] [nvarchar](255) NULL,[SCIENTIFIC_NAME] [nvarchar](255) NULL,[TYPE_CODE] [nvarchar](255) NULL,[CAS_REGISTRY_NUM] [nvarchar](255) NULL,[STATE_CLASS_CODE] [nvarchar](255) NULL) ON [PRIMARY]

AnalyteCodes is then populated with 768 standard AnalyteCodes from SDWIS/CMDP database.

FACILITY DATABASES

CREATE TABLE [dbo].[LC_LABS]([ID] [int] IDENTITY(1,1) NOT NULL,[AUDITUSER] [varchar](50) NULL,[AUDITTIMESTAMP] [datetime] NULL DEFAULT (getdate()),[LABID] [nvarchar](50) NULL,[NAME] [nvarchar](255) NULL,[ADDRESS1] [nvarchar](255) NULL,[ADDRESS2] [nvarchar](255) NULL,[CITY] [nvarchar](255) NULL,[STATE] [nvarchar](255) NULL,[ZIP] [nvarchar](255) NULL,[PHONE] [nvarchar](255) NULL,[EMAIL] [nvarchar](255) NULL,[NOTES] [nvarchar](2000) NULL) ON [PRIMARY]

ALTER TABLE EVENTS ADD COMMENTS NVARCHAR2(2000) DEFAULT NULL
ALTER TABLE LC_SAMPLEDEF ADD LABID NVARCHAR(50) NULL DEFAULT (NULL)
ALTER TABLE LC_SAMPLE ADD LABID NVARCHAR(50) NULL DEFAULT (NULL)
ALTER TABLE LC_SAMPLE ADD SAMPLEVOLUME NVARCHAR(25) NULL DEFAULT (NULL)
ALTER TABLE LC_SAMPLETEST ADD VOLANALYZED NVARCHAR(25) NULL DEFAULT (NULL)
ALTER TABLE LC_PEOPLE ADD LABID NVARCHAR(50) NULL DEFAULT (NULL)
ALTER TABLE LOCATION ADD SAMP_POINT_ID NVARCHAR(50) NULL DEFAULT (NULL)
ALTER TABLE LC_TEST ADD ANALYTECODE NVARCHAR(10) NULL DEFAULT (NULL)
ALTER TABLE VARDESC ADD ANALYTECODE NVARCHAR(10) NULL DEFAULT (NULL)
SP_RENAME 'VARDESC.ComRequiredOnEdit','COMREQUIREDONEDIT','COLUMN'
ALTER TABLE FILES ADD DATESTAMP DATETIME NULL DEFAULT (NULL)

Oracle Scripts

OPSROOT

CREATE TABLE "ANALYTECODES" ("CODE" VARCHAR2(10) NULL, "NAME" NVARCHAR2 (255) NULL, "SCIENTIFIC_NAME" NVARCHAR2 (255) NULL, "TYPE_CODE" NVARCHAR2 (255) NULL,"CAS_REGISTRY_NUM" NVARCHAR2(255) DEFAULT NULL,"STATE_CLASS_CODE" NVARCHAR2(255) DEFAULT NULL)

AnalyteCodes is then populated with 768 standard AnalyteCodes from SDWIS/CMDP database.

FACILITY DATABASES

CREATE TABLE "LC_LABS" ("ID" NUMBER NOT NULL, "AUDITUSER" VARCHAR2(50) NULL, "AUDITTIMESTAMP" DATE DEFAULT NULL, "LABID" NVARCHAR2 (50) NULL, "NAME" NVARCHAR2 (255) NULL,"ADDRESS1" NVARCHAR2 (100) NULL,"ADDRESS2" NVARCHAR2 (100) NULL,"CITY" NVARCHAR2 (100) NULL,"STATE" NVARCHAR2 (25) NULL,"ZIP" NVARCHAR2 (25) NULL,"PHONE" NVARCHAR2 (100) NULL,"EMAIL" NVARCHAR2 (100) NULL,"NOTES" NVARCHAR2 (2000) NULL)
CREATE SEQUENCE "A_LC_LABS" INCREMENT BY 1 START WITH 1 MAXVALUE 1.0E28 MINVALUE 1 NOCYCLE CACHE 20 NOORDER
CREATE TRIGGER "T_LC_LABS" BEFORE INSERT ON "LC_LABS" FOR EACH ROW BEGIN SELECT "A_LC_LABS".NEXTVAL INTO :NEW."ID" FROM DUAL; END T_LC_LABS;

ALTER TABLE EVENTS ADD COMMENTS NVARCHAR(2000) NULL DEFAULT (NULL)
ALTER TABLE LC_SAMPLEDEF ADD LABID NVARCHAR2(50) DEFAULT NULL
ALTER TABLE LC_SAMPLE ADD LABID NVARCHAR2(50) DEFAULT NULL
ALTER TABLE LC_SAMPLE ADD SAMPLEVOLUME NVARCHAR2(25) DEFAULT NULL
ALTER TABLE LC_SAMPLETEST ADD VOLANALYZED NVARCHAR2(25) DEFAULT NULL
ALTER TABLE LC_PEOPLE ADD LABID NVARCHAR2(25) DEFAULT NULL
ALTER TABLE LOCATION ADD SAMP_POINT_ID NVARCHAR2(50) DEFAULT NULL
ALTER TABLE LC_TEST ADD ANALYTECODE NVARCHAR2(10) DEFAULT NULL
ALTER TABLE VARDESC ADD ANALYTECODE NVARCHAR2(10) DEFAULT NULL
ALTER TABLE VARDESC RENAME COLUMN ComRequiredOnEdit to CROE
ALTER TABLE VARDESC RENAME COLUMN CROE TO COMREQUIREDONEDIT



 

 

 

Related Articles
No Related Articles Available.

Article Attachments
No Attachments Available.

Related External Links
No Related Links Available.
Help us improve this article...
What did you think of this article?

poor 
1
2
3
4
5
6
7
8
9
10

 excellent
Tell us why you rated the content this way. (optional)
 
Approved Comments...
No user comments available for this article.
Created on 7/31/2018 2:05 PM.
Last Modified on 7/31/2018 2:07 PM.
Last Modified by Scott Dorner.
Article has been viewed 545 times.
Rated 0 out of 10 based on 0 votes.
Print Article
Email Article