The Internals documentation covers the layout of the database tables as well as interfaces for all script files.
Data Architecture
Note that a * denotes that the field can be null
Exchanges: main table for record of all the exchanges that have
occurred
Clubs: table of all clubs and their admin netid
Members: table of all students who are members of an eating club
Sessions: table of all active user sessions on the website
These data tables will be accessed through server-side scripts, mostly in Perl and Bash shell. The user will have no direct access to any database. Their access will be controlled by client side javascript requests to server scripts that manipulate the tables or retrieve information. At no point will any user be able to specify a custom value for a table, thus eliminating most SQL injection risks. The only exception to this will be the initial member-list upload in which the club tables are populated, which should be easy to sanitize.
The user interface will be a combination of CSS, XHTML, PHP, and JavaScript. The interface will be tested in Firefox 3.0, Safari, and IE 7.0, as these are the most commonly used browsers. CSS and XHTML will be used for formatting and appearance. PHP will be used for forms and data manipulation, while JavaScripts will contribute mostly to ease of use and appearance. Currently we are using a javascript to create a drop-down calender to fill in the date section of the form with.
Interfaces
CGI Page Load/Save calls
All cgi scripts return error string "NO SESSION" if sessionid fails to validate.
public_html/cgi/loadClubBalance.cgi
· takes GET/POST variable sessionid
· returns delimited string of the full club name followed by a list of unbalanced exchanges hosted at club. String line format:
o CLUBNAME
o HOSTID GUESTID DATE MEAL HOSTLNAME HOSTFNAME HOSTMI GUESTLNAME GUESTFNAME GUESTMI GUESTCLUB
public_html/cgi/loadClubOptions.cgi
· takes GET/POST variable sessionid
· returns delimited string of any saved options for club (these could change)
o GMEALS BPRICE LPRICE DPRICE EMAILS
o EMAILS is list of email addresses to send end-of-month balances to.
public_html/cgi/saveClubOptions.cgi
· takes GET variables (these could change):
o netid, gmeals, bprice, lprice, dprice, emails"
o emails is list of email addresses to send end-of-month balances to.
· returns string containing error message if save failed
public_html/cgi/loadClubMembers.cgi
· takes GET/POST variable sessionid
· returns delimited list of members. This should be called when user wants to view current members. String line format:
o NETID LNAME FNAME MI YEAR
public_html/cgi/saveClubMembers.cgi
· takes GET/POST variable sessionid for club and variables for # new members
o netid# lname# fname# mi# year#
· returns string containing error message if save failed
public_html/cgi/saveClubNewExchanges.cgi
· takes GET/POST variables for # new exchanges
o hostid# guestid# date# meal# type#
· returns string containing error message if save failed or list of duplicate exchanges that could not be inserted.
public_html/cgi/loadClubCard.cgi
· takes GET/POST variable sessionid
· returns delimited string of any saved options for member (these could change)
o CLUB $BAL DAYSLEFT
public_html/cgi/loadMemberBalance.cgi
· takes GET/POST variable sessionid
· returns delimited string of user info followed by all exchanges user was in sorted into unbalanced, balanced, and guest meal categories. String line format:
o MEMBERLNAME MEMBERFNAME MEMBERMI MEMBERCLUB
o (unbalanced/balanced/guest)
o HOSTID GUESTID DATE TYPE MEAL FRIENDLNAME FRIENDFNAME FRIENDMI FRIENDCLUB
public_html/cgi/loadMemberOptions.cgi
· takes GET/POST variable sessionid
· returns delimited string of any saved options for member (these could change)
o REMAIL BEMAIL
public_html/cgi/saveMemberOptions.cgi
· takes GET/POST variables (these could change):
o sessionid, remail, bemail
· returns string containing error message if save failed
public_html/cgi/loadMemberCard.cgi
· takes GET/POST variable sessionid
· returns delimited string of any saved options for member (these could change)
o LNAME FNAME MI CLUB $BAL DAYSLEFT
public_html/cgi/findNetid.cgi
· takes GET/POST variable sessionid
· returns string containing netid
CGI AJAX calls
public_html/cgi/findPotentialHosts.cgi
· takes GET/POST variables
o sessionid, queryterms
· returns host club name and delimited string of matching members in host club. String line format:
o CLUB
o LNAME FNAME MI YEAR NETID
public_html/cgi/findPotentialGuests.cgi
· takes GET/POST variables
o sessionid, queryterms
· returns delimited string of matching members not in host club. String line format:
o LNAME FNAME MI YEAR NETID CLUB
Server Side
cgi-includes/member.pl
· Backend file for processing member data requests from database
cgi-includes/club.plh
· Backend file for processing club data requests from database
cgi-includes/mysql.pl
· Backend file for handling database connections and duplicate cleaning
cgi-includes/utils.pl
· Backend file for handling various non-user specific functions
cgi-includes/ajax.pl
· Backend file for handling ajax queries
scripts/clean_sessions.sh
· Shell script for cleaning Sessions MySQL table, deleting entries more than 24 hours old. This should be run nightly by a scheduler.
scripts/bemail.sh
· Shell script for sending monthly member balance emails. This should be run at the end of the month manually or by a scheduler.
· Dependencies: scripts/bemail.pl
scripts/bemail.pl
· Builds content for member balance email.
scripts/remail.sh
· Shell script for sending weekly member reminder emails. This should be run at the end of the week manually or by a scheduler.
· Dependencies: scripts/remail.pl
scripts/ remail.pl
· Builds content for member reminder email.
scripts/cemail.sh
· Shell script for sending monthly club balance emails. This should be run at the end of the month manually or by a scheduler.
· Dependencies: scripts/cemail.pl
scripts/cemail.pl
· Builds content for club balance email.
Client Side
public_html/dbConnect.php
· connects to the database
· returns an error message if applicable
public_html/logout.php
· removes cookies, deletes sessionid from database, and redirects to log out of CAS
public_html/loadBalanceImproved.php
· takes COOKIE sessionid and POST variable
o option
· returns HTML code for a table with current balance modified according to option
public_html/saveClubNewExchanges.php
· takes POST variables:
o hostid# guestid#, date#, type#, meal#;
· returns nothing unless there was an error
public_html/loadClubMembers.php
· takes COOKIE variable:
o sessionid
· returns HTML code for a table with club members
public_html/loadMemberOptions.php (Actually Member and Club Options)
· takes COOKIE variable:
o sessionid
· returns HTML code for a table with member/club options
public_html/saveClubMembers.php
· takes COOKIE sessionid and POST variables:
o netid# lname # fname# mi# year#
· returns nothing unless there was an error, then returns an error message
public_html/saveClubOptions.php
· Takes COOKIE sessionid and POST variables:
o gmeals bprice lprice dprice email
· returns nothing unless there was an error, then returns an error message
public_html/saveMemberOptions.php
· takes COOKIE sessionid and POST variables
o remail bemail
· returns nothing unless there was an error
public_html/timestamp.php
· returns the current time and date
public_html/verifySession.php
· takes COOKIE sessionid
· returns "club" or "member" if user is properly logged in
· returns "invalid" if user is not logged in
public_html/workingDirectory.php
· must be updated if system is moved
· returns the directory where the program is stored for use in absolute addresses
public_html/redirection2.php
· verifies login, redirects to appropriate homepage
public_html/timestamp.php
· returns formatted date and time for session management
public_html/cloisterLogin.php, public_html/towerLogin.php
· bypass CAS login to allow for administrative testing
· will be removed before implementation
promem/sql.php
· returns the database password
Database
all_parser
· script that uses bash and awk to process a club's member list (CLUBNAME_listserv) composed of all of the member netIDs
· searches the Princeton LDAP directory given the netID (uid) and returns the user's full name and class year
· outputs a file consisting of the person's full name, netID, and class year
cloister_parser
· takes the file created by all_parser and creates the loader file for the mysql database
· orders the various columns for last name, first name, middle initial, etc correctly for the INSERT record
CSV_converter
· some clubs provide a .CSV file that may or may not contain member's names
· this script just grabs the netIDs and makes a file of just the netIDs; the LDAP directory will return the correct full name of the user
*a _parser file exists/will exist for all the different clubs
Bulk Uploading:
cloister.sh
· script called by uploader.php to call the parser and generate the SQL loader file