This week we finally got to upgrade our RMH homebase code to 2.0. This version includes some extra documentation, organization, functionality, and database tables. The following exercises refine this functionality and focus on SQL implementation.
7.1 Find examples of where the following criteria are broken in the dbDates.
>Every entry in the table has exactly one value of the appropriate type.
68 $shifts=$d->get_shifts();
69 foreach ($shifts as $key => $value) {
70 insert_dbShifts($d->get_shift($key));
$shifts is stored into value when it contains several different values and data types. This problem adds to the complexity of updating the values in the table, since you do not always know what types are being put into the table and in what order.
>No attribute in the table is redundant with the primary key.
58 $query=”INSERT INTO dbDates VALUES
59 (\””.$d->get_id().”\”,\””.
60 get_shifts_text($d).”\”,\””.$d->get_chef_notes().”\”,\””.$d->get_mgr_notes().”\”)”;
These values are all subsets of the primary key $shift. This can make it much more complex to search. If the primary key was only id, the search would be much easier.
7.2 Develop the functions get_shift_month, get_shift_day, get_shift_year, get_shift_start, and get_shift_end.
For this exercise I will only show the get_shift_month method, in order to prevent redundancy in this post:
function get_shift_month($id)
$shiftMonth = explode("-",$id);
return $shiftMonth[0];
This getter extracts the month from an array within the shift. Shifts follow the format MM-DD-YY-start-end. By creating an array separated at each dash, we can extract every value in an array created using explode() and return it to the parent function.
7.3 Design, implement, and test the calendar month view from the back of the book. This feature would allow the user to view all the shifts for an entire month. This feature requires us to add in a new module known as dbMonths. The simplest design I could use is code reuse. By using the code in dbWeeks and replacing the weeks with months. Below is the implementation of months:
include_once(‘Month.php’);
include_once(‘dbinfo.php’);
include_once(‘dbDates.php’);
/**
* Drops the dbMonths table if it exists, and creates a new one
* Table fields:
* [0] id: mm-dd-yy
* [1] dates: array of RMHDate ids
* [2] status: “unpublished”, “published” or “archived”
* [3] name: name of the month
* [4] end: timestamp of the end of the month
*/
function setup_dbMonths() {
connect();
mysql_query(“DROP TABLE IF EXISTS dbMonths”);
$result=mysql_query(“CREATE TABLE dbMonths (id CHAR(8) NOT NULL, dates TEXT,
status TEXT, name TEXT, end INT, PRIMARY KEY (id))”);
if(!$result)
echo mysql_error();
mysql_close();
}
/**
* Inserts a month into the db
* @param $m the month to insert
*/
function insert_dbMonths($m) {
if (! $m instanceof Month) {
die (“Invalid argument for dbMonths->add_week function call”);
}
connect();
$query = “SELECT * FROM dbMonths WHERE id =\””.$m->get_id().”\””;
$result = mysql_query ($query);
if(mysql_num_rows($result)!=0) {
delete_dbMonths($m);
connect();
}
$query=”INSERT INTO dbMonths VALUES
(\””.$m->get_id().”\”,”.get_dates_text($m->get_dates()).”,\””.
$m->get_status().”\”,\””.
$m->get_name().”\”,\””.
$m->get_end().”\”)”;
$result=mysql_query($query);
mysql_close();
if (!$result) {
echo (“unable to insert into dbMonths: “.$m->get_id(). mysql_error());
return false;
}
else foreach($m->get_dates() as $i)
insert_dbDates($i);
return true;
}
/**
* Deletes a week from the db
* @param $m the week to delete
*/
function delete_dbMonths($m) {
if (! $m instanceof Month)
die (“Invalid argument for delete_dbMonths function call”);
connect();
$query=”DELETE FROM dbMonths WHERE id=\””.$m->get_id().”\””;
$result=mysql_query($query);
mysql_close();
if (!$result) {
echo (“unable to delete from dbMonths: “.$m->get_id(). mysql_error());
return false;
}
else foreach ($m->get_dates() as $i)
delete_dbDates($i);
return true;
}
/**
* Updates a week in the db by deleting it and re-inserting it
* @param $m the week to update
*/
function update_dbMonths($m) {
if (! $m instanceof Month)
die (“Invalid argument for dbMonths->replace_week function call”);
if (delete_dbMonths($m))
return insert_dbMonths($m);
else return false;
}
/**
* Selects a week from the database
* @param $id week id
* @return mysql entry corresponding to id
*/
function select_dbMonths($id) {
if(strlen($id)!=8) {
die (“Invalid month id.”);
}
else {
$timestamp = mktime(0,0,0,substr($id,0,2),substr($id,3,2),substr($id,6,2));
$dow = date(“N”,$timestamp);
$id=date(“m-d-y”,mktime(0, 0, 0, substr($id,0,2), substr($id,3,2)-$dow+1, substr($id,6,2)));
}
connect();
$query = “SELECT * FROM dbMonths WHERE id =\””.$id.”\””;
$result = mysql_query ($query);
if (!$result) {
echo ‘Could not run query: ‘ . mysql_error();
$result_row = false;
}
else
$result_row=mysql_fetch_row($result);
mysql_close();
return $result_row;
}
/**
* retrieves a Month from the database
* @param $id = id of the week to retrieve
* @return the desired week, or null
*/
function get_dbMonths($id) {
$result_row=select_dbMonths($id);
if($result_row) {
$dates=$result_row[1];
$dates=explode(“*”,$dates);
$d=array();
foreach($dates as $i){
$d[]=select_dbDates($i);
}
$m=new Month($d, $result_row[2], $result_row[3], $result_row[4], $result_row[5]);
}
return $m;
}
/**
* the full contents of dbMonths, used by addMonth to list all scheduld weeks
* @return mysql result array of weeks
*/
function get_all_dbMonths() {
connect();
$query=”SELECT * FROM dbMonths”;
$result=mysql_query($query);
mysql_close();
return $result;
}
/**
* generates a string of date ids
* @param $dates array of dates for a week
* @return string of date ids, * delimited
*/
function get_dates_text($dates){
$d=”\””.$dates[0]->get_id();
for($i=1;$i<7;++$i) {
$d=$d.”*”.$dates[$i]->get_id();
}
return $d.”\””;
}
?>
After creating the module, I made the new module testdbMonths to help make assertions on the values of the database. The new code produced the correct results from the database and no bugs were overtly present.
These exercises were a great chance to try out SQL and PHP on open source software. I look forward to the final leg of the RHM source code exercises in chapter 8!
shdavidson 