
-- 05/20/2019
-- Author: Tony Chen 
-- Following changes are made for Globus integration. 

-- ************************************************************
-- As of May. 20, 2019: 
-- ONLY IN BETA INSTANCE (cipres_v2) WORK IN PROGRESS, NOT A FINALIZED VERSION. 

-- The corresponding Java codes are ONLY in Tony's branch, 
-- they are NOT in 'trunk' nor any other branches. 

-- Contact Tony if you have any questions. 
-- ************************************************************

DROP TABLE IF EXISTS globus_users; 

CREATE TABLE globus_users 
( 
    ID                  BIGINT NOT NULL, 
    USER_ID             BIGINT NOT NULL, 
    GLOBUS_USER_ID      VARCHAR(255) NOT NULL,
    GLOBUS_USERNAME     VARCAHR(255) NULL, 
    USER_NAME           VARCHAR(255) NULL,
    EMAIL               VARCHAR(255) NULL,
    ORGANIZATION        VARCHAR(255) NULL,
    ID_PROVIDER         VARCHAR(255) NULL,
    STATUS              VARCHAR(16) NULL,
    
    CONSTRAINT pk_globus_users_01 PRIMARY KEY (ID), 
    
    CONSTRAINT fk_globus_users_01 FOREIGN KEY (USER_ID) REFERENCES users (USER_ID) 
        ON DELETE NO ACTION     ON UPDATE NO ACTION 
);



CREATE TABLE globus_tokens 
(
    ID                  BIGINT NOT NULL, 
    USER_ID             BIGINT NOT NULL,
    ID_TOKEN            VARCHAR(2000) NULL,
    ACCESS_TOKEN        VARCHAR(255) NOT NULL,
    EXPIRES_TIME        DATETIME NULL, 
    REFRESH_TOKEN       VARCHAR(255) NULL, 
    RSRC_SERVER         VARCHAR(2000) NULL, 
    

);





-- This table will be used by Tomcat Session Manager. 
CREATE TABLE tomcat_sessions 
(
    id              BIGINT AUTO_INCREMENT,
    session_id      VARCHAR(100) NOT NULL,
    valid_session   CHAR(1) NOT NULL,
    max_inactive    INT NOT NULL,
    last_access     BIGINT NOT NULL,
    app_name        VARCHAR(255),
    session_data    MEDIUMBLOB, 
    user_id         BIGINT, 
    
    CONSTRAINT pk_tomcat_sessions_01 PRIMARY KEY (id), 
    CONSTRAINT uq_tomcat_sessions_01 UNIQUE (session_id) 

    CONSTRAINT fk_tomcat_sessions_01 FOREIGN KEY (user_id) REFERENCES users (USER_ID) 
        ON DELETE NO ACTION     ON UPDATE NO ACTION
);

CREATE INDEX ix_tomcat_sessions_01 ON tomcat_sessions (app_name);
