Picture Perfect

candlewhynotData Management

Jan 31, 2013 (4 years and 4 months ago)

495 views

A

BATCH PROCESS TO LOAD PHOTOS I NTO
ORACLE 9.0 CAMPUS SOLUTI ONS


Picture Perfect

03/03/2010

The College of New Jersey

Session # 27561

Presenter


Sushma

Mendu



Applications Developer



Enterprise Applications



The College of New Jersey




Primary areas of work:




Student Administration




Security


03/03/2010

The College of New Jersey

About TCNJ


The College of New Jersey (TCNJ) was founded in 1855 and has grown to become one of
the top comprehensive colleges in the nation.


TCNJ is the
State College of NJ located in in Ewing, close to Trenton


Primarily Undergraduate Liberal Arts Institution.


A wealth of degree programs offered through TCNJ’s seven schools

Arts &
Communication; Business; Culture and Society; Education; Science; Nursing, Health,
and Exercise Science; and Engineering make its retention and graduation rates among
the highest in the country.


Students


6,400 undergraduate, 850 graduate



Faculty
-

325 full
-
time, 350 adjunct



Staff
-

675 full
-
time, 125 part
-
time



03/03/2010

The College of New Jersey

Technology at TCNJ



Migrated from legacy system SIS to PeopleSoft
-
Oracle suite of applications in 2009.



Currently on


Oracle 10g


Peoplesoft

9.0


Peopletools

8.49.15


SunSolaris

9.0


03/03/2010

The College of New Jersey

Background


PeopleSoft CS 8.9 has several pages which can display
photos
-

a highly visible feature in comparison to the legacy
system.



This is a capability that the campus community (advisors,
administrative staff, campus police etc) would benefit from
extensively.



There are also other third party systems (such as SOCS)
that would benefit from access to these photos.




03/03/2010

The College of New Jersey

Source

03/03/2010

The College of New Jersey



The photos are obtained via an add
-
on photo
management system


IDWorks
.


The photos are located on the
IDWorks

server.


Details about the photos are maintained on the
Blackboard database.


Filename


Customer_ID



File Location


Photo_Path




Delivered process


Step 1

03/03/2010

The College of New Jersey

Step 2

03/03/2010

The College of New Jersey

Step 3

03/03/2010

The College of New Jersey

Challenge


PeopleSoft’s current capability is to manually load one photo at a
time


we needed a Mass load process.



TCNJ wanted to leverage PeopleSoft functionality and capability to
the maximum and minimize customizations



TCNJ needed a process that would enable photos to be loaded into
PS and also be accessible to other third party systems.



The process had to handle large loads during Orientation when
incoming students get their pictures taken for their ID cards and
also



Daily updates for existing students and new Employees



03/03/2010

The College of New Jersey

Research


Some of solutions we researched included


Loading the photos via the Employee Photo Upload

Component Interface.


C++ load programs


SQL or PL/SQL scripts to load photos in the back
-
end


Had to be run by the DBAs


Did not provide the capability to check the PeopleSoft system for
existing
Emplids


Did not give the Functional users control over the load process


Did not provide any log of uploaded/updated photos




03/03/2010

The College of New Jersey

ID
Works

Black

board

DBase

PERL script

Gets files, does compare and
loads into Oracle Dbase


Photo
Hub.Scheduled

to run
daily

TCNJ
PhotoHub

TCNJ SERVER

Photos.jpg

SHELL

script

Creates a list of new
filesS

Scheduled to run
once a week.

SQR

Loads
jpgs

to
PS tables

PS
Oracle

DBase

Other
third
party

software

Solution

03/03/2010

The College of New Jersey

PS
pages

TCNJ PROD
SERVER

Photos.jpg

Emplid.txt

Script 1

03/03/2010

The College of New Jersey



This script runs daily to keep the photo hub in sync with the
BlackBoard
/
IDWorks

tables.

This is scheduled via
cron
.





The script uses DBI and DBD Perl packages which allow access to
many database environments in a standard way. DBD implementations
exist for proprietary products such as
Oracle
,
Microsoft SQL Server
,
IBM DB2
, and for
free
-
software

databases such as
SQLite
,
PostgreSQL
,
Firebird
, and
MySQL
.


Script 2


This is comprised of 4 scripts (3 Bourne shell scripts and one
SQL script) to pull down the latest photos as JPGs each
Tuesday morning to PAWS.


There is a main "wrapper" script (aka driver script).

It calls a
script to get all the EMPLIDs where the photos have been
updated in the last 8 days.

That script uses and the SQL
script.


Finally, the wrapper script calls the script that actually loops
through all the EMPLIDs that were gathered to download the
photos to a directory on the PAWS server and create the text
file containing those filenames.




03/03/2010

The College of New Jersey

This is the main script (
getphotos.wrapper
) that runs the sub
-
scripts
below to get the latest photos every Tuesday morning


#!/bin/
sh

cd

/
ora
/u05/
xfer
/photo


/
ora
/u05/
xfer
/photo/getemplids.sh >/dev/null 2>&1

touch /
ora
/u05/
xfer
/photo/EMPLIDs.txt

/
ora
/u05/
xfer
/photo/getphotos.sh </
ora
/u05/
xfer
/photo/EMPLIDs.txt
>/dev/null 2>&1


cd

/
ora
/u05/
xfer
/photo

chown

psoft

*.jpg *.log *.txt




03/03/2010

The College of New Jersey

This is the getemplids.sh script referenced in
getphotos.wrapper
, line 4


#!/bin/
sh

ORACLE_HOME=/u01/app/oracle/product/10.2.0

PATH=$ORACLE_HOME/bin
\
:$PATH

export ORACLE_HOME PATH

/bin/
rm

-
f /
ora
/u05/
xfer
/photo/EMPLIDs.txt

sqlplus

-
S
photoid
/xxxxxx@photoserver.tcnj.edu:1521/PHOTOS.photoserver.tcnj.edu
@/
ora
/u05/
xfer
/photo/getemplids.sql




03/03/2010

The College of New Jersey


This is the getemplids.sql script referenced in the last line of
getemplids.sh


SPOOL /
ora
/u05/
xfer
/photo/EMPLIDs.txt

SET TERMOUT OFF

SET TRIMS ON

SET TRIM ON

SET PAGESIZE 0

SET HEADING OFF

SET FEEDBACK OFF

SET TAB OFF

SELECT EMPLID

FROM PHOTOID.PHOTOS

WHERE LAST_UPDATE > SYSDATE
-

8;

SPOOL OFF

EXIT


03/03/2010

The College of New Jersey

This is the script that gets the actual photos out of the PHOTO database,
referenced in line 6 of
getphotos.wrapper

. An Apache web server running
on the
photoserver

provides a URL interface to the PHOTO Dbase


#!/bin/
sh

PATH=/
usr
/bin:/
usr
/
sbin
:/bin:/
sbin;export

PATH

WGET=/
usr
/
sfw
/bin/
wget


/bin/
rm

-
f *.jpg File_names.txt nophoto.log

while read EMPLID ; do


if [ !
-
z "$EMPLID" ]; then




echo "Getting photo for $EMPLID ..."




$WGET
-
q
-
O tmp.jpg '
http://photouser:xxxxxx@photoserver.tcnj.edu/idphoto
-
cgi/showphoto.pl?emplid=
'"$EMPLID"'&days=16'




if [
-
s tmp.jpg ] ; then






mv

tmp.jpg "$EMPLID.jpg"






echo "$EMPLID.jpg" >>File_names.txt




else






echo "$EMPLID" >>nophoto.log




fi


fi

done

rm

-
f tmp.jpg

chmod

644 *.jpg File_names.txt nophoto.log




03/03/2010

The College of New Jersey

03/03/2010

The College of New Jersey

begin
-
sql

CREATE OR REPLACE DIRECTORY BLACKBOARD_PHOTOS AS '/
ora
/u05/
xfer
/photo/';

end
-
sql


begin
-
sql


DECLARE


src_lob

BFILE ;;


dest_lob

BLOB;;


src_lob_exists

BOOLEAN;;


f
utl_file.file_type
;;


file_name

varchar2(200);;


src_emplid

varchar2(200);;


emplid_exists

PS_PERSON.EMPLID%TYPE;;



BEGIN


src_lob

:=
bfilename
( 'BLACKBOARD_PHOTOS', $
Emplid_jpg

);;




INSERT INTO PS_EMPL_PHOTO VALUES($
Emplid
, 1, EMPTY_BLOB())


RETURNING EMPLOYEE_PHOTO INTO
dest_lob
;;




DBMS_LOB.OPEN(
src_lob
, DBMS_LOB.LOB_READONLY);;


DBMS_LOB.LoadFromFile
( DEST_LOB =>
dest_lob
, SRC_LOB =>
src_lob
, AMOUNT =>
DBMS_LOB.GETLENGTH(
src_lob
) );;


DBMS_LOB.CLOSE(
src_lob
);;



EXCEPTION

WHEN NO_DATA_FOUND THEN


utl_file.fclose
(f);;


END;;


end
-
sql


Summary


New photo is taken via
IDworks


The photo is stored on
IDworks

server


Information about the photo


CustomerID
,
Photopath

are available on the Dbase


Blackboard


Shell script 1 reads information on Blackboard, compares to local Dbase and


Inserts/Updates photos to TCNJ Photo Hub in BLOB format


FTPs the photos as BLOB to TCNJ Server


This script is scheduled via
Cron

to run daily


Shell script 2 reads information on the TCNJ
PhotoHub

and


gives a list of
Emplids

created in the past 7days
-

Change Log


Saves the photos as JPG on the prod server


This script is scheduled via
Cron

to run once a week


Run Control / Scheduled job


SQR reads the change log file and


Compares with PS transaction tables to make sure
Emplid

is valid


Checks if photo is existing


Insert or Update


Creates a log of activity


PS logs


emailed via Distribution Lists to Functional Users




03/03/2010

The College of New Jersey

Solution
-

SQR and
RunCntl

page


Advantages:


Built off of existing
PeopleSoft functionality


Will not interfere with
upgrades


Allows users to have control
over the process


Allows control over the
cohort of
Emplids

for whom
we want to load photos


Can be scheduled


Clean and non invasive


Secure


This process can work with
other source Dbases as well.


Disadvantages:


Reliant on a third party
system to provide the jpg
files


Requires Shell scripts to run
as a means of connecting to
the photo source


Sometimes the photos are
imported in different sizes






03/03/2010

The College of New Jersey

Questions?


Contact Information:




Sushma

Mendu




mendu@tcnj.edu






Shawn
Sivvy




ssivvy@tcnj.edu


03/03/2010

The College of New Jersey