/*****************************************************************************************/
/*****************************************************************************************/
/* Copyright(c) 2013 SAS Institute Inc., Cary, NC, USA. All Rights Reserved.             */
/*                                                                                       */
/* Name: sna_23to61_upgrade_ora.sas                                                      */  
/*                                                                                       */
/* Purpose: Upgrade SAS Social Network Analysis version 2.3 tables                       */
/*          to the SAS Social Network Analysis version 6.1                               */
/*                                                                                       */
/* Output: SNA 6.1 tables                                                                */
/*                                                                                       */
/* Usage: Run this script in a SAS environment with administrative privaleges.           */
/*       a. LIBNAME is set to SNA.                                                       */
/*       b. Provide the database specific connection information to                      */
/*          begin the migration process.                                                 */
/*                                                                                       */
/* Backup/Restore: Save a backup of the 2.3 table data.  Review the changes made to      */
/*                 the database tables and restore the data after the migration          */
/*                 is complete.                                                          */
/*                                                                                       */
/*****************************************************************************************/
/*****************************************************************************************/

/*===================================================================*/
/* Enter Customer Specific Target Source Connection Values - Oracle  */
/*===================================================================*/


%let lib = <Migration Library>;   /* For example, Migrate  */
%let path = <Oracle TNS Entry> ;  /* From tnsnames.ora     */
%let user = <User Name> ;         /* Oracle User/Schema    */
%let pass = <Password> ;          /* Oracle Password       */

PROC SQL NOERRORSTOP;
CONNECT TO ORACLE (USER=&USER PASS=&PASS PATH=&PATH);


/*========================================================*/
/*            create additional table sequences           */
/*========================================================*/

EXECUTE(
CREATE SEQUENCE SEQ_CUSTOM_MEASURE_ITEMS
	INCREMENT BY 1
	START WITH 10000) BY ORACLE;
EXECUTE(
CREATE SEQUENCE SEQ_CUSTOM_MEASURE_ITEMS_SAVED
	INCREMENT BY 1
	START WITH 10000) BY ORACLE;
EXECUTE(
CREATE SEQUENCE SEQ_SNA_COLORMODEL
	INCREMENT BY 1
	START WITH 10000) BY ORACLE;
EXECUTE(
CREATE SEQUENCE SEQ_SNA_COLUMN_DISPLAY
	INCREMENT BY 1
	START WITH 10000) BY ORACLE;
EXECUTE(
CREATE SEQUENCE SEQ_SNA_COLUMN_METADATA
	INCREMENT BY 1
	START WITH 10000) BY ORACLE;
EXECUTE(
CREATE SEQUENCE SEQ_SNA_CONTEXT_PREF
	INCREMENT BY 1
	START WITH 10000) BY ORACLE;
EXECUTE(
CREATE SEQUENCE SEQ_SNA_CURRENCY
	INCREMENT BY 1
	START WITH 10000) BY ORACLE;
EXECUTE(
CREATE SEQUENCE SEQ_SNA_CUSTOM_MEASURES
	INCREMENT BY 1
	START WITH 10000) BY ORACLE;
EXECUTE(
CREATE SEQUENCE SEQ_SNA_EXTERNAL_APP
	INCREMENT BY 1
	START WITH 10000 ) BY ORACLE;
EXECUTE(
CREATE SEQUENCE SEQ_SNA_EXTERNAL_APP_DSPLY_NM
	INCREMENT BY 1
	START WITH 10000) BY ORACLE ;
EXECUTE(
CREATE SEQUENCE SEQ_SNA_EXTERNAL_APP_ROLE
	INCREMENT BY 1
	START WITH 10000 ) BY ORACLE ;
EXECUTE(
CREATE SEQUENCE SEQ_SNA_TABLE_DISPLAY
	INCREMENT BY 1
	START WITH 10000) BY ORACLE;
EXECUTE(
CREATE SEQUENCE SEQ_SNA_TABLE_METADATA
	INCREMENT BY 1
	START WITH 10000) BY ORACLE ;



/*========================================================*/
/* Oracle alter script for SNA_SOCIAL_NETWORK_CLUSTER     */
/*========================================================*/

EXECUTE(ALTER TABLE SNA_SOCIAL_NETWORK_CLUSTER DROP PRIMARY KEY) BY ORACLE;
EXECUTE(ALTER TABLE SNA_SOCIAL_NETWORK_CLUSTER MODIFY CLUSTER_ID VARCHAR2(255)) BY ORACLE;
EXECUTE(ALTER TABLE SNA_SOCIAL_NETWORK_CLUSTER MODIFY ACTIONABLE_ENTITY_ID VARCHAR2(255)) BY ORACLE;
EXECUTE(ALTER TABLE SNA_SOCIAL_NETWORK_CLUSTER MODIFY ACTIONABLE_ENTITY_TYPE VARCHAR2(100)) BY ORACLE;
EXECUTE(ALTER TABLE SNA_SOCIAL_NETWORK_CLUSTER MODIFY LAST_UPDATE_DTTM DATE) BY ORACLE;
EXECUTE(ALTER TABLE SNA_SOCIAL_NETWORK_CLUSTER MODIFY USER_ID VARCHAR2(35)) BY ORACLE;
EXECUTE(ALTER TABLE SNA_SOCIAL_NETWORK_CLUSTER MODIFY CREATE_DTTM DATE) BY ORACLE;
EXECUTE(ALTER TABLE SNA_SOCIAL_NETWORK_CLUSTER ADD CONSTRAINT PRIM_KEY PRIMARY KEY(CLUSTER_ID, ACTIONABLE_ENTITY_ID, ACTIONABLE_ENTITY_TYPE, LAST_UPDATE_DTTM)) BY ORACLE;


/*========================================================*/
/*  Oracle alter script for SNA_SOCIAL_NETWORK_GROUP      */
/*========================================================*/
 
EXECUTE(ALTER TABLE SNA_SOCIAL_NETWORK_GROUP MODIFY LAST_UPDATE_DTTM DATE) BY ORACLE;

/*========================================================*/
/*   Oracle alter script for SNA_SOCIAL_NETWORK_LINK      */
/*========================================================*/

EXECUTE(ALTER TABLE SNA_SOCIAL_NETWORK_LINK DROP PRIMARY KEY) BY ORACLE;
EXECUTE(ALTER TABLE SNA_SOCIAL_NETWORK_LINK MODIFY LINK_VERSION_ID INTEGER) BY ORACLE;
EXECUTE(ALTER TABLE SNA_SOCIAL_NETWORK_LINK MODIFY CLUSTER_ID NOT NULL) BY ORACLE;
EXECUTE(ALTER TABLE SNA_SOCIAL_NETWORK_LINK MODIFY CLUSTER_ID VARCHAR2(255)) BY ORACLE;
EXECUTE(ALTER TABLE SNA_SOCIAL_NETWORK_LINK MODIFY FROM_NODE_UID INTEGER) BY ORACLE;
EXECUTE(ALTER TABLE SNA_SOCIAL_NETWORK_LINK MODIFY TO_NODE_UID INTEGER) BY ORACLE;
EXECUTE(ALTER TABLE SNA_SOCIAL_NETWORK_LINK ADD CLUSTER_VERSION_ID INTEGER) BY ORACLE;
EXECUTE(ALTER TABLE SNA_SOCIAL_NETWORK_LINK MODIFY LINK_COLOR VARCHAR2(255)) BY ORACLE;
EXECUTE(ALTER TABLE SNA_SOCIAL_NETWORK_LINK MODIFY LINK_LABEL VARCHAR2(255)) BY ORACLE;
EXECUTE(ALTER TABLE SNA_SOCIAL_NETWORK_LINK MODIFY CREATE_DTTM DATE) BY ORACLE;
EXECUTE(ALTER TABLE SNA_SOCIAL_NETWORK_LINK MODIFY LAST_UPDATE_DTTM DATE) BY ORACLE;
EXECUTE(ALTER TABLE SNA_SOCIAL_NETWORK_LINK ADD CONSTRAINT PRIM_KEY PRIMARY KEY(LINK_VERSION_ID)) BY ORACLE;


/*========================================================*/
/*   Oracle alter script for SNA_SOCIAL_NETWORK_NODE      */
/*========================================================*/

EXECUTE(ALTER TABLE SNA_SOCIAL_NETWORK_NODE DROP PRIMARY KEY) BY ORACLE;
EXECUTE(ALTER TABLE SNA_SOCIAL_NETWORK_NODE MODIFY NODE_VERSION_ID INTEGER) BY ORACLE;
EXECUTE(ALTER TABLE SNA_SOCIAL_NETWORK_NODE ADD GROUP_VERSION_ID INTEGER) BY ORACLE;
EXECUTE(ALTER TABLE SNA_SOCIAL_NETWORK_NODE ADD NODE_ID VARCHAR2(255)) BY ORACLE;
EXECUTE(ALTER TABLE SNA_SOCIAL_NETWORK_NODE MODIFY CREATE_DTTM DATE) BY ORACLE;
EXECUTE(ALTER TABLE SNA_SOCIAL_NETWORK_NODE MODIFY LAST_UPDATE_DTTM DATE) BY ORACLE;
EXECUTE(ALTER TABLE SNA_SOCIAL_NETWORK_NODE ADD CONSTRAINT PRIM_KEY PRIMARY KEY(NODE_VERSION_ID)) BY ORACLE;


/*========================================================*/
/*     Create New SNA Tables                              */
/*========================================================*/

EXECUTE (CREATE TABLE SNA_COLORMODEL (
COLOR_MODEL_SK	NUMBER(20),
COLUMN_METADATA_SK	NUMBER(20) NOT NULL,
START_RANGE_NM	VARCHAR2(60),
END_RANGE_NM	VARCHAR2(60),
STYLE_TYPE	VARCHAR2(255),
FORMAT_TYPE	VARCHAR2(50),
SEXCL_FLG	VARCHAR2(1),
EEXCL_FLG	VARCHAR2(1),
PRIMARY KEY (COLOR_MODEL_SK)
)) BY ORACLE;


EXECUTE (CREATE TABLE SNA_COLUMN_DISPLAY (
COLUMN_DISPLAY_SK	NUMBER(20)NOT NULL,
COLUMN_METADATA_SK	NUMBER(20) NOT NULL,
COLUMN_DISPLAY_NM	VARCHAR2(100),
JUSTIFICATION_SPEC	VARCHAR2(255),
HEADER_JUSTIFICATION_SPEC	VARCHAR2(255),
FORMATSPEC_DESC	VARCHAR2(255),
LOCALE_DESC	VARCHAR2(255),
PRIMARY KEY (COLUMN_DISPLAY_SK)
)) BY ORACLE;


EXECUTE (CREATE TABLE SNA_COLUMN_METADATA (
COLUMN_METADATA_SK	NUMBER(20)NOT NULL,
TABLE_METADATA_SK	NUMBER(20) NOT NULL,
COLUMN_NM	VARCHAR2(60),
DATATYPE_NM	VARCHAR2(60),
INCLUDE_FACET_SEARCH_FLG	VARCHAR2(1),
FACET_SEARCH_TYPE	VARCHAR2(100),
MIN_WIDTH_SPEC	INTEGER,
PRIMARY KEY (COLUMN_METADATA_SK)
)) BY ORACLE;

EXECUTE (CREATE TABLE SNA_CONTEXT_PREFERENCE (
CONTEXT_PREFERENCE_SK	NUMBER(20)NOT NULL,
ANALYTIC_CONTEXT_NM	VARCHAR2(255) NOT NULL,
PROPERTY_NM	VARCHAR2(255) NOT NULL,
PROPERTY_DESC	VARCHAR2(255) NOT NULL,
PRIMARY KEY (CONTEXT_PREFERENCE_SK)
)) BY ORACLE;

EXECUTE (CREATE TABLE SNA_CURRENCY (
CURRENCY_SK	NUMBER(20)NOT NULL,
ALIGN_SYMBOL_CD	VARCHAR2(5),
CURRENCY_SYMBOL_CD	VARCHAR2(4),
DECIMAL_SEPARATOR_CD	VARCHAR2(4),
PRESION_NO	INTEGER,
ROUNDING_CD	VARCHAR2(7),
USE_NEGATIVE_SIGN_FLG	VARCHAR2(5),
USE_THOUSANDS_SEPARATOR_FLG	VARCHAR2(5),
THOUSANDS_SEPARATOR_CD	VARCHAR2(4),
CURRENCY_NM	VARCHAR2(40),
PRIMARY KEY (CURRENCY_SK)
)) BY ORACLE;


EXECUTE (CREATE TABLE SNA_CUST_MEASURE_ITEMS_SAVED (
CUSTOM_MEASURE_ITEMS_SK	NUMBER(20)NOT NULL,
MEASURE_NM	VARCHAR2(255) NOT NULL,
MEASURE_VALUE	VARCHAR2(255),
DATATYPE_NM	VARCHAR2(60),
LINK_UNIQUE_ID	VARCHAR2(255),
NODE_UNIQUE_ID	VARCHAR2(255),
LAST_UPDATE_DTTM	DATE NOT NULL,
PRIMARY KEY (CUSTOM_MEASURE_ITEMS_SK)
)) BY ORACLE;

EXECUTE (CREATE TABLE SNA_CUSTOM_MEASURE_ITEMS (
CUSTOM_MEASURE_ITEMS_SK	NUMBER(20)NOT NULL,
CUSTOM_MEASURES_SK	NUMBER(20) NOT NULL,
MEASURE_NM	VARCHAR2(255) NOT NULL,
MEASURE_VALUE	VARCHAR2(255),
DATATYPE_NM	VARCHAR2(60) NOT NULL,
PRIMARY KEY (CUSTOM_MEASURE_ITEMS_SK)
)) BY ORACLE;

EXECUTE (CREATE TABLE SNA_CUSTOM_MEASURES (
CUSTOM_MEASURES_SK	NUMBER(20)NOT NULL,
ANALYTIC_CONTEXT_NM	VARCHAR2(255),
OBJECT_ID	VARCHAR2(255) NOT NULL,
OBJECT_TYPE	VARCHAR2(255) NOT NULL,
PRIMARY KEY (CUSTOM_MEASURES_SK)
)) BY ORACLE;

EXECUTE (CREATE TABLE SNA_EXTERNAL_APP (
EXTERNAL_APP_SK	NUMBER(20),
ANALYTIC_CONTEXT_NM	VARCHAR2(255) NOT NULL,
URL_NM	VARCHAR2(255) NOT NULL,
PRIMARY KEY (EXTERNAL_APP_SK)
)) BY ORACLE;


EXECUTE (CREATE TABLE SNA_EXTERNAL_APP_DISPLAY_NM (
EXTERNAL_APP_DISPLAY_NAME_SK	NUMBER(20)NOT NULL,
EXTERNAL_APP_SK	NUMBER(20),
DISPLAY_NM	VARCHAR2(255) NOT NULL,
LOCALE_NM	VARCHAR2(255) NOT NULL,
PRIMARY KEY (EXTERNAL_APP_DISPLAY_NAME_SK)
)) BY ORACLE;


EXECUTE (CREATE TABLE SNA_EXTERNAL_APP_ROLE (
EXTERNAL_APP_ROLE_SK	NUMBER(20) NOT NULL,
EXTERNAL_APP_SK	NUMBER(20) NOT NULL,
METADATA_ROLE_NM	VARCHAR2(255) NOT NULL,
PRIMARY KEY (EXTERNAL_APP_ROLE_SK)
)) BY ORACLE;


EXECUTE (CREATE TABLE SNA_TABLE_DISPLAY (
TABLE_DISPLAY_SK	NUMBER(20) NOT NULL,
TABLE_METADATA_SK	NUMBER(20) NOT NULL,
TABLE_DISPLAY_NM	VARCHAR2(100) NOT NULL,
LOCALE_DESC	VARCHAR2(100),
PRIMARY KEY (TABLE_DISPLAY_SK)
)) BY ORACLE;

EXECUTE (CREATE TABLE SNA_TABLE_METADATA (
TABLE_METADATA_SK	NUMBER(20) NOT NULL,
SEGMENT_ID	VARCHAR2(128) NOT NULL,
ANALYTIC_CONTEXT_NM	VARCHAR2(255) NOT NULL,
TABLE_NM	VARCHAR2(60),
TAB_POSITION_NO	NUMBER(4),
PRIMARY KEY (TABLE_METADATA_SK)
)) BY ORACLE;


/* Add Foreign Keys */

EXECUTE (
ALTER TABLE SNA_COLORMODEL
	ADD CONSTRAINT FKSNA_COLORMODEL FOREIGN KEY (COLUMN_METADATA_SK) REFERENCES SNA_COLUMN_METADATA (COLUMN_METADATA_SK)
	)  BY ORACLE;

EXECUTE (
ALTER TABLE SNA_COLUMN_DISPLAY
	ADD CONSTRAINT FKSNA_SNA_COLUMN_DISPLAY FOREIGN KEY (COLUMN_METADATA_SK) REFERENCES SNA_COLUMN_METADATA (COLUMN_METADATA_SK)
	)  BY ORACLE;

EXECUTE (
ALTER TABLE SNA_COLUMN_METADATA
	ADD CONSTRAINT FKSNA_SNA_COLUMN_METADATA FOREIGN KEY (TABLE_METADATA_SK) REFERENCES SNA_TABLE_METADATA (TABLE_METADATA_SK)
	)  BY ORACLE;

EXECUTE (
ALTER TABLE SNA_CUSTOM_MEASURE_ITEMS
	ADD CONSTRAINT FKSNA_SNA_CUSTOM_MEASURE_ITEMS FOREIGN KEY (CUSTOM_MEASURES_SK) REFERENCES SNA_CUSTOM_MEASURES (CUSTOM_MEASURES_SK)
	)  BY ORACLE;
EXECUTE (
ALTER TABLE SNA_TABLE_DISPLAY
	ADD CONSTRAINT FKSNA_SNA_CUSTOM_MEASURE_ITEMS FOREIGN KEY (TABLE_METADATA_SK) REFERENCES SNA_TABLE_METADATA (TABLE_METADATA_SK)
	)  BY ORACLE;



DISCONNECT FROM ORACLE;

quit;