Course Pages on Scripps Scholars

Historical Documentation (via Google Docs)

Last Edited : August 4, 5:18 PM 2014 , April 7, 2020

Luis Avila

Database for Courses

~~marinara - scrippsscholars.courses~~

Now lives on siodb courses_backup schema

[lavila@siomail ~]$sudo su - bubba

siomail(bubba): /home/bubba% cd data_scripts/Grad/Scrippsscholars/

dl scripts on siomail - siomail(bubba): data_scripts/Grad/Scrippsscholars% courses.sh (calls dl_courses.db2 and inserts downloaded data into ~~marinara~~. siodb)

not automated - need to update the term(s) in dl_courses.db2

usually hear from someone - Instructor or Grad Dept when they need a new qtr's info

or a prior qtr updated.

need to define a procedure for initial upload of new qtr courses and updates to prior quarters.

basically, instructors can change up to the last minute.

marinara

mysql> describe courses;

+------------------+------------------+------+-----+---------+----------------+

+------------------+------------------+------+-----+---------+----------------+

+------------------+------------------+------+-----+---------+----------------+

13 rows in set (0.00 sec)

----------------- ---------------- ---------------- ---------------- ---------------- ----------------

(from Terri Departure Meeting Notes )

Added Sept. 18, 2012:

XVI. RAB Courses ----> Scripps Scholar Courses.

marinara scrippsscholars.courses. data from DW student_db. dl script on siomail

20120925 - /home/bubba/data_scripts/Grad/Scrippsscholars/

needs to be automated - probably qtrly -

see dl_grad_student_status.pl grad_student_status.sh for example of automated

qtrly dl (already automated)

http://scrippsscholars.ucsd.edu/

20120902 Luis: - talk to Edgar about this scripps scholars app. and there is this course data there.

---------------- ---------------- ---------------- ---------------- ---------------- ----------------

# set the term code

# Set $term_code for GPA downloads by calculating the term based on the month the dl_* script is run.

sub getterm {

my $term_code = "";

my $_year = sprintf"%02d", (localtime)[5] - 100 ; # years

my $_yearminus1 = sprintf"%02d", (localtime)[5] - 101 ; # years

my $_month= sprintf "%02d", (localtime)[4] + 1; # months

$_year = sprintf "%02d", $_year;

$_yearminus1 = sprintf "%02d", $_yearminus1 ;

#$_month= "07";

print $_month;

print $_year;

# Conditional to determine TERM_CODE. If the date falls between Jan-March then term_code will be set to the

# Fall quarter of previous year. If date is in April or May then term_code is set to Winter quarter of present

# year. If date is between July-September then term_code is set to Spring of present year. Finally, if date

# falls in Oct. or Nov. then term_code is set to Fall of present year.

if ( $_month eq "01" || $_month eq "02" || $_month eq "03" ) {

$term_code= "FA$_yearminus1";

}

elsif ( $_month eq "04" || $_month eq "05" ) {

$term_code= "WI$_year";

}

elsif ( $_month eq "06" || $_month eq "07" || $_month eq "08" || $_month eq "09" ) {

$term_code= "SP$_year";

}

elsif ( $_month eq 10 || $_month eq 11 ) {

$term_code= "FA$_year";

return ($term_code);

}

my $term_code = getterm();

---------------- ---------------- ---------------- ---------------- ---------------- ----------------

Added Aug. 4, 2014:

siomail(bubba): data_scripts/Grad/Scrippsscholars% ls -artl

total 28

-rw-rw-r-- 1 bubba bubba 7 Oct 2 2012 .db2zzz

-rw-r--r-- 1 bubba bubba 185 Jan 9 2013 dl_courses.tmp

-rw-rw-r-- 1 bubba bubba 4188 Jul 3 2013 dl_courses.db2

-rwxrwxr-x 1 bubba bubba 972 Jul 23 2013 courses.sh

drwxrwxr-x 2 bubba bubba 4096 Jul 23 2013 .

drwxrwxr-x 4 bubba bubba 4096 Jun 20 13:21 ..

siomail(bubba): data_scripts/Grad/Scrippsscholars% more courses.sh

#!/bin/sh

#

#this script needs to be automated similar to the Grad/gradinfo.sh and .pl

export PATH=$PATH:$HOME/bin:/usr/sbin:/usr/bin:$RDB/bin:$SYBASE/OCS/bin:/usr/local/sbin:

#set up variables

export MYSERVER="marinara.ucsd.edu"

export MYUSER="bubba"

export MYPW="cat $HOME/bin/.pw/.bpass"

#get the data from Darwin

#the term(s) for the download are hardcoded in dl_courses.db2 update as needed

#if "refreshing" data for a term already in the database, delete those

# records before running this script or there will be duplicates

/home/bubba/bin/db2prod dl_courses.db2 /tmp/courses.tmp

#

#refresh data on Production Server (marinara)

#courses

#existing term data must be deleted manually before running this script

# uncomment below if want to delete all records from courses

#mysql -h $MYSERVER -u $MYUSER -p`$MYPW` -e "delete from courses" scrippsscholars

mysqlimport -L -h $MYSERVER -u $MYUSER -p`$MYPW` scrippsscholars /tmp/courses.txt

echo "script done. "

siomail(bubba): data_scripts/Grad/Scrippsscholars% more dl_courses.tmp

SQL3104N The Export utility is beginning to export data to file

"/tmp/courses.txt".

SQL3105N The Export utility has finished exporting "7125" rows.

Number of rows exported: 7125

siomail(bubba): data_scripts/Grad/Scrippsscholars% more dl_courses.db2

-- this script updates scrippscholars.courses.

-- export sends the output to a tab delim file in /tmp

EXPORT TO /tmp/courses.txt OF DEL MODIFIED BY COLDEL0X09 nochardel

-- there's 2 select statements using UNION

-- get primary instructors

-- need to manually change the term_code before running

select distinct '0' as "ID",

student_db.s_lec_instructor.lec_instructor_pid as InstructorID,

student_db.s_lec_instructor.lec_instructor_name as "InstructorName",

'Y' as "Primary",

student_db.s_section.crs_course_id as "Course Full" ,

student_db.s_section.crs_course_title as "Course Title",

student_db.s_section.crs_course_code as "Course Code",

student_db.s_section.sub_subject_code as "Subject Code",

student_db.s_section.dpt_department_code as "Course Department Code",

student_db.s_section.sec_instruction_type as "InstrType",

student_db.s_term.trm_term_code as "Term",

student_db.s_term.trm_term_id as "Term Id",

student_db.s_section.sec_subtitle as "Section Subtitle"

from

student_db.s_section

inner join student_db.s_term on student_db.s_section.trm_term_code = student_db.s_term.trm_term_code

inner join student_db.s_lec_instructor on student_db.s_lec_instructor.sec_section_id = student_db.s_section.sec_section

_id

left outer join student_db.s_instructor_section on student_db.s_section.sec_section_id = student_db.s_instructor_sectio

n.sec_section_id

inner join student_db.s_course on student_db.s_course.crs_course_id = student_db.s_section.crs_course_id and student_db

.s_term.trm_term_code = student_db.s_course.trm_term_code and student_db.s_term.trm_term_code = student_db.s_course.trm_term_co

de

where

student_db.s_term.trm_term_id in (select distinct trm_term_id from student_db.s_term where

--trm_term_code ='FA12')

trm_term_code in ('FA12','WI13','SP13','S313'))

-- right(trm_term_code,2) between '09' and '12')

-- uncomment below if want to limit to certain depts

-- the list is a best guess of science depts

-- and student_db.s_section.dpt_department_code in (

-- 'BIOL',

-- 'BIOM',

-- 'CHEM',

-- 'COGS',

-- 'CSE',

-- 'ECE',

-- 'ENVR',

-- 'ERTH',

-- 'ESYS',

-- 'MAE',

-- 'MATH',

-- 'MATS',

-- 'NENG',

-- 'NEU',

-- 'PHYS',

-- 'SIO',

-- 'SOE')

UNION

-- get co instructors

select distinct '0' as "ID" ,

student_db.s_instructor_section.ins_instructor_pid as "InstructorID",

student_db.s_instructor_section.ins_instructor_name as "Instructor",

'' as "Primary",

student_db.s_section.crs_course_id as "Course Full" ,

student_db.s_section.crs_course_title as "Course Title",

student_db.s_section.crs_course_code as "Course Code",

student_db.s_section.sub_subject_code as "Subject Code",

student_db.s_section.dpt_department_code as "Course Department Code",

student_db.s_section.sec_instruction_type as "InstrType",

student_db.s_term.trm_term_code as "Term", student_db.s_term.trm_term_id as "Term Id",

student_db.s_section.sec_subtitle as "Section Subtitle"

from

student_db.s_section inner join student_db.s_term on student_db.s_section.trm_term_code = student_db.s_term.trm_term_code

left outer join student_db.s_instructor_section on student_db.s_section.sec_section_id = student_db.s_instructor_section.sec_sec

tion_id and student_db.s_instructor_section.ins_primary_flag != 'Y'

inner join student_db.s_course on student_db.s_course.crs_course_id = student_db.s_section.crs_course_id and student_db.s_term.t

rm_term_code = student_db.s_course.trm_term_code and student_db.s_term.trm_term_code = student_db.s_course.trm_term_code

where

student_db.s_term.trm_term_id in (select distinct trm_term_id from student_db.s_term where

-- trm_term_code ='FA12')

trm_term_code in ('FA12','WI13','SP13','S313'))

-- right(trm_term_code,2) between '09' and '12')

and student_db.s_instructor_section.ins_instructor_pid !=''

-- uncomment below if want to limit to certain depts

-- the list is a best guess of science depts

-- and student_db.s_section.dpt_department_code in (

-- 'BIOL',

-- 'BIOM',

-- 'CHEM',

-- 'COGS',

-- 'CSE',

-- 'ECE',

-- 'ENVR',

-- 'ERTH',

-- 'ESYS',

-- 'MAE',

-- 'MATH',

-- 'MATS',

-- 'NENG',

-- 'NEU',

-- 'PHYS',

-- 'SIO',

-- 'SOE')

fetch first 100000 rows only;

*************************

teaching staff - course info

key words: scrippsscholars, rab

Wednesday, October 10, 2012 3:48 PM

Hi Cerise,

The class listing is moving from

http://sio.ucsd.edu/Profile/<email_name>

to

http://scrippsscholars.ucsd.edu/<email_name>/classes

For example:

http://sio.ucsd.edu/Profile/grouse

is now

http://scrippsscholars.ucsd.edu/grouse/classes

The data are downloaded from the campus data warehouse student_db tables and is up-to-date as of SP12.

The course detail is another subject - it's not currently a feature on the new site. It will most likely be added as a

discussion item for the ScrippsScholars team and we'll keep you posted....

Terri

x43424

On Oct 10, 2012, at 2:45 PM, Cerise Maue wrote:

Hi Terri,

Please update the teaching staff database information through Fall 2012.

Also, please update the course database information listed on the web site.

for example,

SIO 284 and SIO 293 course titles and descriptions are outdated and need to be revised.

http://sio.ucsd.edu/Profile/grouse

Thank you!