We first obtained space and permission to host a table on the Princeton University CS MySQL server, publicdb. We named our database promem, which is where our various tables would be created and located.
Creating a table
Assume, for example, that we wanted to create the table Clubs which maps basic information about the various eating clubs, including the club’s netID, prices for the various meals, and the email address for club notifications. In MYSQL, we would execute the following command:
CREATE TABLE CLUBS ( NETID VARCHAR(8) NOT NULL, CLUB VARCHAR(15) NOT NULL, GMEALS INT(3) NOT NULL, BPRICE DOUBLE NOT NULL, LPRICE DOUBLE NOT NULL, DPRICE DOUBLE NOT NULL, EMAIL VARCHAR(8) NOT NULL);
Altering the columns in an already-existing table
Assume, for example, that we wanted to change the property of a column in the table Exchanges, by making NETID a non-null value. In MYSQL, we would execute the following command:
ALTER TABLE EXCHANGES CHANGE NETID VARCHAR(8) VARCHAR(8) NOT NULL;
If we wanted to add a column to the table Sessions that is a timestamp for when a specific TICKETID was added to the database, we could do the following:
ALTER TABLE SESSIONS ADD COLUMN TIMESTAMP DATETIME;
Clearing the contents of a table
In MYSQL, we execute the following command:
TRUNCATE TABLE TABLE_NAME;
The root directory should not be in a public location. This ensures that privacy of the database password and limits access to server side scripts.
Since it is not accessible to the public, the permission settings for the root directory are not crucial, but for consistency should be set to 775. The root directory contains sql.php which contains the database password. The password must be changed in this file as well as mysql.php in the directory cgi-includes. The root directory also contains the directory “cgi-includes” which contains the server side routines for interfacing with the database. The directory “scripts” contains the scripts to handle the reminder e-mails and removing expired sessions from the database. All other files are contained in public_html.
The public_html directory contains all other files that are publicly viewable. These permissions should be set to 775 to ensure that the user can access the site appropriately, but cannot write to the files. The directory “tablesorter” contains the JQuery scripts to operate the sorting feature of the balance tables. The “cgi” directory contains the server side scripts which call the routines from root/cgi-includes to access the database. The “uploads” directory is used to store files that are uploaded to the site for a bulk member addition. The file “workingDirectory.php” returns the root directory for the project. This file must be changed when moving the code. Similarly the “requires” statements at the top of files in cgi-includes and scripts must be manually updated with any changes to the root directory, as consistently reading the root from an external file is hindered by these scripts being called from different locations with different working directories.
It is important to make sure the Sessions database table is routinely purged of expired sessionids. This is done using scripts/clean_sessions.sh.
MealSaver also supports sending weekly and monthly balance emails to users. These jobs are handled by scripts/bemail.sh[pl] for monthly member balance emails, scripts/remail.sh[pl] for weekly member reminder emails, and scripts/cemail.sh[pl] for monthly club balance emails.
To run any one, call the associated shell scripts. In order to set them up for automated mailing on your Unix server, you’ll need to set up a cronjob using Crontab. From the command line enter:
crontab –e
This will open up your user crontab file in vi text editor. Enter the following three lines:
0 0 * * * sh clean_sessions.sh
0 0 * * 1 sh remail.sh
0 0 1 * * sh bemail.sh
0 0 1 * * sh cemail.sh
Save and exit vi and crontab. Your scripts should now run as expected. Check error logs routinely to make sure they executed as expected. (Note: The mailing scripts are not scheduled in our implementation as we do not want to send junk data to all club members in our test database)