During the NetCat 7.0 I had a chance to review Jeff's tutorial. I could not resist to try installing PDO_OCI. Having everything needed running on my Ubuntu box, I was finally able to do what I promised: Create PDO database class. The key objective was to make it easy to switch between MySQL and Oracle XE, preferably just by changing connection string.
There is more to be done, for example all input variables must be filtered. You are warmly welcomed to join Wish List Sample Application project.
Problems
Before porting WishDB class to PDO due to differences between Oracle XE and MySQL queries needed to be normalized. There are two differences affecting Wish List Sample Application:- Oracle is case insensitive to object names, and Oracle schema object names are stored as uppercase
- There is a difference in handling dates between MySQL and Oracle XE
Further on, in few cases database specific PHP functions where used outside WishDB class. Such usage is hardening code maintenance and porting code to PDO. Therefore those cases had to be resolved as well before porting WishDB class.
Finally, if data could be retrieved in a single query don't query database multiple times. Establishing connection with database is bottleneck in every PHP database driven application.
Handling dates is a bit trickier. Both MySQL and Oracle XE store dates internally as timestamp. However, input and output need to be formatted differently. Logical step was to create functions having same names, input parameters and outputting identically formatted date string.
Before creating SQL functions I had to normalize function format_date_for_sql first. I prefer using DateTime class over native PHP functions.
Finally, if data could be retrieved in a single query don't query database multiple times. Establishing connection with database is bottleneck in every PHP database driven application.
Restructuring the Code
Here is an example:// MySQL version
$wisher = $this->query("SELECT id FROM wishers WHERE name = '"
. $name . "'");
// OCI8 version
$query = "SELECT ID FROM wishers WHERE name = :user_bv";
// PDO version
$query = "
SELECT id ID
FROM wishers
WHERE name = :user_bv
";
Schema object names in MySQL version of the application are written lowercase. Oracle XE will not have a problem with lowercase names but returned array keys will be uppercase. I decided to add uppercase column names alias and use OCI8 version of the application as base for refactoring. In this way there is no need to change MySQL schema nor PHP code other then WishDB class.Handling dates is a bit trickier. Both MySQL and Oracle XE store dates internally as timestamp. However, input and output need to be formatted differently. Logical step was to create functions having same names, input parameters and outputting identically formatted date string.
Before creating SQL functions I had to normalize function format_date_for_sql first. I prefer using DateTime class over native PHP functions.
// MySQL version
function format_date_for_sql($date) {
if ($date == "")
return null;
else {
$dateParts = date_parse($date);
return $dateParts['year'] * 10000 + $dateParts['month'] * 100 + $dateParts['day'];
}
}
// OCI8 version
function format_date_for_sql($date) {
if ($date == "")
return null;
else {
$dateParts = date_parse($date);
return $dateParts['year'] * 10000 + '-' + $dateParts['month'] * 100 + '-' + $dateParts['day'];
}
}
// PDO version
function format_date_for_sql($date)
{
if ($date == "") {
return null;
} else {
$dateTime = new DateTime($date, new DateTimeZone("UTC"));
return $dateTime->format("Y-n-j H:i:s e");
}
}
Here are those two SQL functions:// MySQL version
CREATE DEFINER=`root`@`localhost` FUNCTION `wishlist`.`format_due_date`(
`in_date` VARCHAR(255) CHARSET latin1
) RETURNS varchar(255) CHARSET latin1
SQL SECURITY INVOKER
BEGIN
return CONCAT(in_date, SPACE(1), 'UTC');
END $$
CREATE DEFINER=`root`@`localhost` FUNCTION `wishlist`.`set_due_date`(
`in_date` VARCHAR(255) CHARSET latin1
) RETURNS varchar(255) CHARSET latin1
SQL SECURITY INVOKER
BEGIN
return SUBSTR(in_date, 1, length(in_date) - 4);
END $$
// Oracle XE version
CREATE OR REPLACE
FUNCTION "FORMAT_DUE_DATE" (in_date in TIMESTAMP) return VARCHAR2 is
begin
return TO_CHAR(IN_DATE, 'YYYY-MM-DD HH24:MI:SS TZR');
end; /
CREATE OR REPLACE
FUNCTION "SET_DUE_DATE" (in_date in VARCHAR2) return VARCHAR2 is
begin return TO_TIMESTAMP_TZ(in_date, 'YYYY-MM-DD HH24:MI:SS TZR');
end; /
Finally queries could be normalized.// MySQL version
$this->query("INSERT INTO wishes (wisher_id, description, due_date)" .
" VALUES (" . $wisherID . ", '" . $description . "', "
. $this->format_date_for_sql($duedate) . ")");
// OCI8 version
$query = "INSERT INTO wishes (wisher_id, description, due_date) VALUES (:wisher_id_bv, :desc_bv, to_date(:due_date_bv, 'YYYY-MM-DD'))";
// PDO version
$query = "
INSERT INTO wishes (wisher_id, description, due_date)
VALUES (
:wisher_id_bv,
:desc_bv,
set_due_date(:due_date_bv)
)
";
User's wish list is retrieved in two steps: First, a user #id is found by user name and later on list of wishes is found by a user #id.$wisherID = WishDB::getInstance()->get_wisher_id_by_name($_SESSION['user']);
$stid = WishDB::getInstance()->get_wishes_by_wisher_id($wisherID);
User's wish list could be retrieved in a single query. Oracle XE supports full outer join but, unfortunately, MySQL does not. Therefore, a query has to be a bit more complex. SELECT w.id ID, w.description DESCRIPTION,
format_due_date(w.due_date) DUE_DATE, wr.id WRID
FROM wishes w RIGHT OUTER JOIN wishers wr
ON wr.id = w.wisher_id
WHERE wr.name = :user_bv
Finally, I replaced function get_wishes_by_wisher_id with function get_wishes_by_wisher_name. /**
* Gets user's wishes for the user having given name
*
* @param string $name
* @return ArrayIterator
*/
public function get_wishes_by_wisher_name($name)
{
$query = "";
$stid = null;
$row = array();
$result = null;
$query = "
SELECT w.id ID, w.description DESCRIPTION,
format_due_date(w.due_date) DUE_DATE, wr.id WRID
FROM wishes w RIGHT OUTER JOIN wishers wr
ON wr.id = w.wisher_id
WHERE wr.name = :user_bv
";
$stid = $this->con->prepare($query);
$stid->bindParam(":user_bv", $name, PDO::PARAM_STR);
$stid->execute();
$result = new ArrayIterator();
while ($row = $stid->fetch(PDO::FETCH_ASSOC)) {
$result->append($row);
}
$result->rewind();
return $result;
}
Function get_wishes_by_wisher_name is returning ArrayIterator instead of resultset, letting me to use the same code regardless of database sitting in backend and having more clear code when displaying results.<table class="std">
<tr>
<th>Item</th>
<th>Due Date</th>
<th colspan="2"> </th>
</tr>
<?php
$wishes = WishDB::getInstance()->
get_wishes_by_wisher_name($_SESSION["user"]);
while ($wishes->valid()):
$row = $wishes->current();
$date = new DateTime($row['DUE_DATE'], new DateTimeZone("UTC"));
if (true === is_null($row["ID"])) {
$wishes->next();
continue;
}
?>
<tr>
<td>
<?php
echo htmlentities($row['DESCRIPTION']);
?>
</td>
<td>
<?php
echo (is_null($row['DUE_DATE']) ?
"" : $date->format("Y, M jS"));
$wishID = $row['ID'];
?>
</td>
<td>
<form name="editWish"
action="editWish.php" method="GET">
<input type="hidden"
name="wishID"
value="<?php echo $wishID; ?>" />
<input type="submit" name="editWish" value="Edit" />
</form>
</td>
<td>
<form name="deleteWish"
action="deleteWish.php" method="POST">
<input type="hidden"
name="wishID"
value="<?php echo $wishID; ?>" />
<input type="submit"
name="deleteWish"
value="Delete" />
</form>
</td>
</tr>
<?php
$wishes->next();
endwhile;
?>
</table>
You can grab the code from Kenai repository.There is more to be done, for example all input variables must be filtered. You are warmly welcomed to join Wish List Sample Application project.
No comments:
Post a Comment