Click Person Feed

We plan to send a nightly feed of person data from the Tufts LDAP server to the Click IRB application. This has already been configured and tested in the test environment, but (as of July 2019) is not yet running in production as the Click production servers are not yet ready.

The person feed has been set up on the Tufts production server, and will just need to be configured and scheduled with cron once the Click production server is ready to go.

Server Info

Test: eaapi-test-01.uit.tufts.edu
Prod: eaapi-prod-01.uit.tufts.edu

Server contact: Bill Sivret

User: apiadm

Path: /home/apiadm/clickFeed

Configuration

Within /home/apiadm/clickFeed, there is a configuration file named tufts.properties. This contains the connection string for the Click database, as well as the connection info for the Tufts LDAP server.

To set the Click connection info, update the DATABASE_URL parameter with the new connection string. The values currently in this file are for the Click Stage Environment; when the click Production environment is ready, this file will need to be updated on eaapi-prod-01. To do this, simply replace the IP/port/databaseName/user/password in the connection string with the new values for production.

Running

The person feed is a jar file which can be run directly with the "java -jar" command, as displayed below. When run, the adapter will connect to both the Tufts LDAP server and the Click IRB database using the given credentials, and feed the appropriate person data from LDAP to Click. It will batch the person records by the first letter of the last name, and each batch will print a row showing its progress.

After completing the person records, the adapter will also create new 'Organization' entries within Click for any department IDs which were not already present in Click. The process will then end.

Scheduling

To schedule the person feed, run the command "crontab -e" and uncomment the click_irb_person_feed line as shown below. By default, the job is scheduled to run nightly at midnight, though this can be changed in the crontab file if necessary. Output from the job will be redirected to the click_irb_person_feed.log file.

Source

The source code for the jar file is in an Eclipse project which can be found in the RAS GitLab repository: https://gitlab.it.tufts.edu/ras/click-irb-person-feed 

Click Database Schema

Person table schema:
[ClickIntegration].[Person_Staging](
       [ID] [int] IDENTITY(1,1) NOT NULL,
       [UserId] [nvarchar](255) NULL,
       [CompanyID] [nvarchar](50) NULL,
       [FirstName] [nvarchar](255) NULL,
       [LastName] [nvarchar](255) NULL,
       [MiddleName] [nvarchar](255) NULL,
       [PhoneNumber] [nvarchar](50) NULL,
       [EmailAddress] [nvarchar](255) NULL,
       [Roles] [nvarchar](4000) NULL,
       [IsActive] [bit] NULL,
       [Address1] [nvarchar](255) NULL,
       [City] [nvarchar](255) NULL,
       [State] [nvarchar](255) NULL,
       [Country] [nvarchar](255) NULL,
       [PostalCode] [nvarchar](255) NULL,
       [EmployeeID] [nvarchar](255) NULL,
CONSTRAINT [PK_Person_Staging] PRIMARY KEY CLUSTERED

Org table schema:
[ClickIntegration].[Org_Staging](
       [ID] [nvarchar](255) NOT NULL,
       [IsFundingSource] [bit] NULL,
       [Name] [nvarchar](255) NULL,
       [Parent] [nvarchar](255) NULL,
CONSTRAINT [PK_Org_Staging] PRIMARY KEY CLUSTERED

Troubleshooting

The Huron database requires connecting over a VPN tunnel, which is set up on the two eaapi servers listed above. Due to this, it's not possible to directly connect to the Click database from your local machine. This makes troubleshooting a bit trickier.

I've set up a command-line JDBC script on the eaapi servers to run simple SQL queries against the Click DB. 

Path: /home/apiadm/jdbcSql/

Usage: ./stage-clickSql.sh [query] 

Example: ./stage-clickSql.sh "select * from Person_Staging where UserId='rscaffid';"

The stage-clickSql.sh script contains the connection information for the Stage database. To create a script for Production once the prod DB is available, simply copy the script and replace the host/db/user info at the top of the file.