数据库操作 Quentin Zervaas's DBO

《Web 2.0 开发实战》一书的作者，Quentin Zervaas 在书中提到的一个简易PHP数据访问对象。

DatabaseObject.php 

 

<?php
    /**
     * DatabaseObject
     *
     * Abstract class used to easily manipulate data in a database table
     * via simple load/save/delete methods
     */
    abstract class DatabaseObject
    {
        const TYPE_TIMESTAMP = 1;
        const TYPE_BOOLEAN   = 2;

        protected static $types = array(self::TYPE_TIMESTAMP, self::TYPE_BOOLEAN);

        private $ _id = null;
private $ _properties = array();
protected $ _db = null;
protected $ _table = '';
protected $ _idField = '';
public function __construct(Zend_Db_Adapter_Abstract $db, $table, $idField)
{
$this->_db = $db;
$this->_table = $table;
$this->_idField = $idField;
}
public function load($id, $field = null)
{
if (strlen($field) == 0)
$field = $this->_idField;
if ($field == $this->_idField) {
$id = (int) $id;
if ($id <= 0)
return false;
}
$query = sprintf('select %s from %s where %s = ?',
join(', ', $this->getSelectFields()),
$this->_table,
$field);
$query = $this->_db->quoteInto($query, $id);
return $this->_load($query);
}
protected function getSelectFields($prefix = '')
{
$fields = array($prefix . $this->_idField);
foreach ($this->_properties as $k => $v)
$fields[] = $prefix . $k;
return $fields;
}
protected function _load($query)
{
$result = $this->_db->query($query);
$row = $result->fetch();
if (!$row)
return false;
$this->_init($row);
$this->postLoad();
return true;
}
public function _init($row)
{
foreach ($this->_properties as $k => $v) {
$val = $row[$k];
switch ($v['type']) {
case self::TYPE_TIMESTAMP:
if (!is_null($val))
$val = strtotime($val);
break;
case self::TYPE_BOOLEAN:
$val = (bool) $val;
break;
}
$this->_properties[$k]['value'] = $val;
}
$this->_id = (int) $row[$this->_idField];
}
public function save($useTransactions = true)
{
$update = $this->isSaved();
if ($useTransactions)
$this->_db->beginTransaction();
if ($update)
$commit = $this->preUpdate();
else
$commit = $this->preInsert();
if (!$commit) {
if ($useTransactions)
$this->_db->rollback();
return false;
}
$row = array();
foreach ($this->_properties as $k => $v) {
if ($update && !$v['updated'])
continue;
switch ($v['type']) {
case self::TYPE_TIMESTAMP:
if (!is_null($v['value'])) {
if ($this->_db instanceof Zend_Db_Adapter_Pdo_Pgsql)
$v['value'] = date('Y-m-d H:i:sO', $v['value']);
else
$v['value'] = date('Y-m-d H:i:s', $v['value']);
}
break;
case self::TYPE_BOOLEAN:
$v['value'] = (int) ((bool) $v['value']);
break;
}
$row[$k] = $v['value'];
}
if (count($row) > 0) {
// perform insert/update
if ($update) {
$this->_db->update($this->_table, $row, sprintf('%s = %d', $this->_idField, $this->getId()));
}
else {
$this->_db->insert($this->_table, $row);
$this->_id = $this->_db->lastInsertId($this->_table, $this->_idField);
}
}
// update internal id
if ($commit) {
if ($update)
$commit = $this->postUpdate();
else
$commit = $this->postInsert();
}
if ($useTransactions) {
if ($commit)
$this->_db->commit();
else
$this->_db->rollback();
}
return $commit;
}
public function delete($useTransactions = true)
{
if (!$this->isSaved())
return false;
if ($useTransactions)
$this->_db->beginTransaction();
$commit = $this->preDelete();
if ($commit) {
$this->_db->delete($this->_table, sprintf('%s = %d', $this->_idField, $this->getId()));
}
else {
if ($useTransactions)
$this->_db->rollback();
return false;
}
$commit = $this->postDelete();
$this->_id = null;
if ($useTransactions) {
if ($commit)
$this->_db->commit();
else
$this->_db->rollback();
}
return $commit;
}
public function isSaved()
{
return $this->getId() > 0;
}
public function getId()
{
return (int) $this->_id;
}
public function getDb()
{
return $this->_db;
}
public function __set($name, $value)
{
if (array_key_exists($name, $this->_properties)) {
$this->_properties[$name]['value'] = $value;
$this->_properties[$name]['updated'] = true;
return true;
}
return false;
}
public function __get($name)
{
return array_key_exists($name, $this->_properties) ? $this->_properties[$name]['value'] : null;
}
protected function add($field, $default = null, $type = null)
{
$this->_properties[$field] = array('value'   => $default,
'type'    => in_array($type, self::$types) ? $type : null,
'updated' => false);
}
protected function preInsert()
{
return true;
}
protected function postInsert()
{
return true;
}
protected function preUpdate()
{
return true;
}
protected function postUpdate()
{
return true;
}
protected function preDelete()
{
return true;
}
protected function postDelete()
{
return true;
}
protected function postLoad()
{
return true;
}
public static function BuildMultiple($db, $class, $data)
{
$ret = array();
if (!class_exists($class))
throw new Exception('Undefined class specified: ' . $class);
$testObj = new $class($db);
if (!$testObj instanceof DatabaseObject)
throw new Exception('Class does not extend from DatabaseObject');
foreach ($data as $row) {
$obj = new $class($db);
$obj->_init($row);
$ret[$obj->getId()] = $obj;
}
return $ret;
}
}
DatabaseObject_User
<?php
class DatabaseObject_User extends DatabaseObject
{
static $userTypes = array('member'        => 'Member',
'administrator' => 'Administrator');
public $profile = null;
public $ _newPassword = null;
public function __construct($db)
{
parent::__construct($db, 'users', 'user_id');
$this->add('username');
$this->add('password');
$this->add('user_type', 'member');
$this->add('ts_created', time(), self::TYPE_TIMESTAMP);
$this->add('ts_last_login', null, self::TYPE_TIMESTAMP);
$this->profile = new Profile_User($db);
}
protected function preInsert()
{
$this->_newPassword = Text_Password::create(8);
$this->password = $this->_newPassword;
return true;
}
protected function postLoad()
{
$this->profile->setUserId($this->getId());
$this->profile->load();
}
protected function postInsert()
{
$this->profile->setUserId($this->getId());
$this->profile->save(false);
$this->sendEmail('user-register.tpl');
return true;
}
protected function postUpdate()
{
$this->profile->save(false);
return true;
}
protected function preDelete()
{
$this->profile->delete();
return true;
}
public function sendEmail($tpl)
{
$templater = new Templater();
$templater->user = $this;
// fetch the e-mail body
$body = $templater->render('email/' . $tpl);
// extract the subject from the first line
list($subject, $body) = preg_split('/\r|\n/', $body, 2);
// now set up and send the e-mail
$mail = new Zend_Mail();
// set the to address and the user's full name in the 'to' line
$mail->addTo($this->profile->email,
trim($this->profile->first_name . ' ' .
$this->profile->last_name));
// get the admin 'from' details from the config
$mail->setFrom(Zend_Registry::get('config')->email->from->email,
Zend_Registry::get('config')->email->from->name);
// set the subject and body and send the mail
$mail->setSubject(trim($subject));
$mail->setBodyText(trim($body));
$mail->send();
}
public function createAuthIdentity()
{
$identity = new stdClass;
$identity->user_id = $this->getId();
$identity->username = $this->username;
$identity->user_type = $this->user_type;
$identity->first_name = $this->profile->first_name;
$identity->last_name = $this->profile->last_name;
$identity->email = $this->profile->email;
return $identity;
}
public function loginSuccess()
{
$this->ts_last_login = time();
unset($this->profile->new_password);
unset($this->profile->new_password_ts);
unset($this->profile->new_password_key);
$this->save();
$message = sprintf('Successful login attempt from %s user %s',$ _SERVER['REMOTE_ADDR'],
$this->username);
$logger = Zend_Registry::get('logger');
$logger->notice($message);
}
static public function LoginFailure($username, $code = '')
{
switch ($code) {
case Zend_Auth_Result::FAILURE_IDENTITY_NOT_FOUND:
$reason = 'Unknown username';
break;
case Zend_Auth_Result::FAILURE_IDENTITY_AMBIGUOUS:
$reason = 'Multiple users found with this username';
break;
case Zend_Auth_Result::FAILURE_CREDENTIAL_INVALID:
$reason = 'Invalid password';
break;
default:
$reason = '';
}
$message = sprintf('Failed login attempt from %s user %s',$ _SERVER['REMOTE_ADDR'],
$username);
if (strlen($reason) > 0)
$message .= sprintf(' (%s)', $reason);
$logger = Zend_Registry::get('logger');
$logger->warn($message);
}
public function fetchPassword()
{
if (!$this->isSaved())
return false;
// generate new password properties
$this->_newPassword = Text_Password::create(8);
$this->profile->new_password = md5($this->_newPassword);
$this->profile->new_password_ts = time();
$this->profile->new_password_key = md5(uniqid() .
$this->getId() .
$this->_newPassword);
// save new password to profile and send e-mail
$this->profile->save();
$this->sendEmail('user-fetch-password.tpl');
return true;
}
public function confirmNewPassword($key)
{
// check that valid password reset data is set
if (!isset($this->profile->new_password)
|| !isset($this->profile->new_password_ts)
|| !isset($this->profile->new_password_key)) {
return false;
}
// check if the password is being confirm within a day
if (time() - $this->profile->new_password_ts > 86400)
return false;
// check that the key is correct
if ($this->profile->new_password_key != $key)
return false;
// everything is valid, now update the account to use the new password
// bypass the local setter as new_password is already an md5
parent::__set('password', $this->profile->new_password);
unset($this->profile->new_password);
unset($this->profile->new_password_ts);
unset($this->profile->new_password_key);
// finally, save the updated user record and the updated profile
return $this->save();
}
public function usernameExists($username)
{
$query = sprintf('select count(*) as num from %s where username = ?',
$this->_table);
$result = $this->_db->fetchOne($query, $username);
return $result > 0;
}
static public function IsValidUsername($username)
{
$validator = new Zend_Validate_Alnum();
return $validator->isValid($username);
}
public function __set($name, $value)
{
switch ($name) {
case 'password':
$value = md5($value);
break;
case 'user_type':
if (!array_key_exists($value, self::$userTypes))
$value = 'member';
break;
}
return parent::__set($name, $value);
}
}
?>