Page Comparison

Setup Foundation Elements for VetView

Note

For running script files we recommend using a Database developer tool like SQL Developer or TOAD. This allows you to preview and update any script prior to executing it, it also allows you to see progress and any error messages.

Example Connection in SQL Developer Example Connection in TOAD

All script file should be executed using the VetView account.

There are 4 scripts that need to be run to prior starting up the VetView project on the Web Server

Tables, Constraints, and Indices

The Tables 0.9.5.sql file includes a single line statement that defines each table. Some tables that include CLOB columns have tablespace definition that may need to be replaced depending on the name of the tablespaces defined when setting up the database.

Code Block

language	sql
title	Example Table Create Statement

CREATE TABLE NOTIFICATIONS ( NOTIFICATION_ID NUMBER(19) NOT NULL, VERSION NUMBER(19) NOT NULL, CREATED_DATE TIMESTAMP(6), LUD TIMESTAMP(6), LUN VARCHAR2(30 CHAR), MESSAGE_TEXT CLOB NOT NULL, MSG_FROM NUMBER(19), MESSAGE_LINK NUMBER(19), MESSAGE_LINK_TYPE VARCHAR2(30 CHAR), MSG_TO NUMBER(19) NOT NULL, MESSAGE_TO_SECTION_LINK NUMBER(19), MESSAGE_TO_SERVICE_LINK NUMBER(19), MESSAGE_TYPE VARCHAR2(30 CHAR) NOT NULL, STATUS VARCHAR2(30 CHAR) NOT NULL ) 
LOB (MESSAGE_TEXT) STORE AS ( TABLESPACE DATA_SMALL ENABLE STORAGE IN ROW CHUNK 8192 RETENTION);

CREATE INDEX NOTIFICATIONS_MSG_FROM_INDEX ON NOTIFICATIONS (MSG_FROM);

CREATE INDEX NOTIFICATIONS_MSG_TO_INDEX ON NOTIFICATIONS (MSG_TO);

ALTER TABLE NOTIFICATIONS ADD (  PRIMARY KEY (NOTIFICATION_ID) ENABLE VALIDATE);

ALTER TABLE NOTIFICATIONS ADD (  CONSTRAINT FK4BD694E880B55DEB FOREIGN KEY (MSG_FROM) REFERENCES EMPLOYEE (EMP_ID) ENABLE VALIDATE,  CONSTRAINT FK4BD694E88DBFBC3C FOREIGN KEY (MSG_TO) REFERENCES EMPLOYEE (EMP_ID) ENABLE VALIDATE);

Sequences

Code Block

language	sql
title	Example Sequence Statement

CREATE SEQUENCE SEQ_ACCESSION_COMMENT_ID START WITH 1 MAXVALUE 9999999999999999999999999999 MINVALUE 1 NOCYCLE CACHE 20 NOORDER;

Localization
1. Localization is a table of labels and messages used to display dynamically on the rendered web pages by language. The localization script loads the default set of text which is in English. The "Localization All 0.9.x.sql" file deletes all existing records in the table and resets it for the indicated version.
Localization
Security Roles and Admin User Account

Code Block

language	sql
title	Example Localization Statement

Insert into LOCALIZATION (ID, VERSION, CODE, DATE_CREATED, LAST_UPDATED, LOC, RELEVANCE, TEXT)
 Values (1464, 0, 'privilege.description.worksheet', TO_TIMESTAMP('8/3/2012 11:10:31.562000 AM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), TO_TIMESTAMP('8/3/2012 11:10:31.562000 AM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), '*', 1, 'Worksheet Screen');

Security Roles and Admin User Account

The security roles are the access rights and privileges that are defined by VetView. These are not the institution defined user roles, those are defined from a setup screen inside of the application. The second part of the script defines an administrator employee record and security user with all defined roles associated to it. By default the username is 'administrator' and the password is 'VETVIEW-ADMIN'. User accounts are not created in or user to connect to the oracle database. User accounts can either use a database encrypted password 'Local Account' or LDAP for authentication. The VetView web application always connects to the database using the VetView oracle user account.

Code Block

language	sql
title	Example Administrator Statement

DECLARE 
    v_sec_user_id number ;
BEGIN
/**************************************************************************************
*   Username: Administrator
*   Password: VETVIEW-ADMIN
***************************************************************************************/

     INSERT INTO EMPLOYEE ( emp_id, version, username, display_name, cell_messages, cell_notifications, dvm_YN, email_messages, email_notifications, default_home_page,
                            language, faculty_yn, intern_yn, resident_yn, staff_yn, student_yn, tech_yn, service_chief_yn, FINALIZE_ENTER_KEY_YN )
         Values (  99999999, 1, 'ADMINISTRATOR', 'Administrator', 'N', 'N', 'N', 'N', 'N', 'Hospital', 'en_US', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N' ) ;
         
     SELECT HIBERNATE_SEQUENCE.nextval INTO v_sec_user_id FROM dual ;

     INSERT INTO SEC_USER ( id, version, account_expired, account_locked, authentication_type, enabled, failedlogincount, "password", password_expired, system_to_system_identifier, username, session_exempt )
     Values ( v_sec_user_id , 1, 0, 0, 'Local Account', 1, 0, '2dec8726b8e5f64cb8b86cf4839f76669ab3945fbea4b9eeef6adb0169287dc7c2063d5c567b2006c8b2ff3bea3fdee1abcde9eaddb45a02dff1214e69c34e2a', 0, 'cbdc5ae8e5bc4b5da0abe67faa9d6ad1', 'ADMINISTRATOR', 0 ) ;

     INSERT INTO sec_user_sec_role s ( S.SEC_USER_ID, S.SEC_ROLE_ID )
     SELECT v_sec_user_id, R.ID FROM sec_role r ;

     INSERT INTO labs_users_employees e ( E.LABS_EMPLOYEE_ID, E.VERSION, E.EMPLOYEE_ID, E.LUD, E.LUN, E.SITE_ID, E.ASSIGNTO_YN, E.COORDINATOR_YN, E.SIGNATURE_YN )
     SELECT SEQ_LABS_EMPLOYEE_ID.nextval, 1, 99999999, sysdate, 'VetView', S.SITE_ID, 'N' ,'N', 'N'
     FROM labs_sites s;

     COMMIT;
        
EXCEPTION
     WHEN NO_DATA_FOUND THEN
       NULL;
     WHEN OTHERS THEN
       RAISE;  
END;
/

At this point the VetView Project can be started and you can login using the 'Administrator' account.

Note