/phpSurvey/library/Snake/Db/Model.php
PHP | 612 lines | 356 code | 2 blank | 254 comment | 68 complexity | d09b1dbc3c09cb9605d6a8591463e1a8 MD5 | raw file
Possible License(s): LGPL-2.1, BSD-3-Clause
- <?php
- // ??????
- Basic::loadClass('Helper_Cache');
- /**
- * ???????
- * ?????????????-CURD
- *
- * @package Db
- * @author Snake.Zero
- * @copyright Snake.Zero
- */
- class Db_Model
- {
- /**
- * ?????
- *
- * @var string
- */
- var $tablePrefix = '';
- /**
- * ?????
- *
- * @var string
- */
- var $tableName = '';
- /**
- * ?????
- *
- * @var string
- */
- var $tableFullName;
- /**
- * ?????
- *
- * @var handle
- */
- var $handle;
- /**
- * ?????
- *
- * @var array
- */
- var $fieldsName;
- /**
- * ????????
- *
- * @var array
- */
- var $fields;
- /**
- * ??(???DB???????????????????????)
- *
- * @var string
- */
- var $primaryKey;
- /**
- * ??????????
- *
- * @var boolean
- */
- var $autoupdated = true;
- /**
- * ??????????????????
- *
- * ????????????????? create() ????????
- * ???????????????
- *
- * @var array
- */
- var $createdTimeFields = array('created' , 'dateline');
- /**
- * ????
- *
- * @param array $config
- * @return Db_Model
- */
- function Db_Model ($config = null)
- {
- // ??????????????
- if (empty($config)) {
- $config = Basic::get_config_db();
- }
- $this->handle = $this->connect($config);
- // ??????
- if (empty($this->tablePrefix) && ! empty($config['prefix'])) {
- $this->tablePrefix = $config['prefix'];
- }
- // ???????
- $this->tableFullName = $this->tablePrefix . $this->tableName;
- // ????????????????????
- if (! empty($this->fields)) {
- $_fieldsName = array_keys($this->fields);
- foreach ((array)$_fieldsName as $v) {
- $this->fieldsName[$v] = $v;
- }
- }
- if (empty($this->fieldsName) && ! empty($this->tableName)) {
- if (is_file(CACHEPATH . DS . 'array_CTABLE_' . $this->tableFullName . '.php')) {
- $this->fieldsName = include (CACHEPATH . DS . 'array_CTABLE_' . $this->tableFullName . '.php');
- } else {
- $this->fieldsName = $this->_getFields();
- $this->_cacheTable($this->fieldsName);
- }
- }
- }
- /**
- * ???????????
- *
- * @param array $config
- * @return handle
- */
- function connect ($config)
- {
- // ????????MD5??
- $configMD5 = md5(serialize($config));
- // ????????????????????
- if (! empty($GLOBALS['_SNAKE_DB_HANDLES_'][$configMD5])) {
- return $GLOBALS['_SNAKE_DB_HANDLES_'][$configMD5];
- }
- // ??????????????
- $_hd = mysql_connect($config['host'], $config['username'], $config['password']) or die('Unable to connect to the database.');
- mysql_select_db($config['database'], $_hd) or die('Unable to selete this database:' . $config['database']);
- $this->_execute("SET NAMES {$config['charset']}", $_hd);
- // ??????????
- $GLOBALS['_SNAKE_DB_HANDLES_'][$configMD5] = $_hd;
- return $_hd;
- }
- /**
- * ????
- *
- * @param mixed $fields
- * @return string
- */
- function _parseFields ($fields)
- {
- if ($fields == '*' || $fields == '') {
- return '*';
- }
- if (is_array($fields)) {
- return '`' . implode('` , `', $fields) . '`';
- } else {
- return $fields;
- }
- }
- /**
- * parse the order by subsentence
- *
- * @param mixed $order
- * @return string
- */
- function _parseOrderBy ($order)
- {
- if (empty($order)) {
- return ' ORDER BY ' . "`$this->primaryKey`";
- }
- if (strtoupper($order) == 'DESC') {
- return ' ORDER BY ' . "`$this->primaryKey`" . ' DESC';
- }
- if (is_array($order)) {
- $total = count($order);
- $i = 1;
- foreach ((array)$order as $key => $value) {
- if (is_numeric($key)) {
- $orderBy .= "`{$value}` ";
- } else {
- $orderBy .= "`{$key}` {$value}";
- }
- if ($i < $total) {
- $orderBy .= ' , ';
- }
- $i ++;
- }
- } else {
- $orderBy = $order;
- }
- return ' ORDER BY ' . $orderBy;
- }
- /**
- * parse the limit subsentence
- *
- * @param mixed $limit
- * @return string
- */
- function _parseLimit ($limit)
- {
- if ($limit == 0) {
- return '';
- }
- if (is_numeric($limit)) {
- $limit = array(0 , $limit);
- }
- if (is_array($limit)) {
- $limit = "{$limit[0]},{$limit[1]}";
- }
- return ' LIMIT ' . $limit;
- }
- /**
- * ??????????
- *
- * @param string $field
- * @return boolean
- */
- function hasField ($field)
- {
- $fds = $this->fieldsName;
- if (in_array($field, $fds)) {
- return true;
- }
- return false;
- }
- /**
- * ??SQL??
- *
- * @param string $sql
- * @return result
- */
- function _execute ($sql, $HADLE = null, $log = true)
- {
- $HADLE = empty($HADLE) ? $this->handle : $HADLE;
- if ($log) {
- $GLOBALS['SNAKE']['SQL'][] = $sql;
- }
- return mysql_query($sql, $HADLE);
- }
- /**
- * ??SQL??,????????
- *
- * @param string $sql
- * @param string $key
- * @return result
- */
- function _hexecute ($sql, $key = 'test', $HADLE = null)
- {
- $GLOBALS['SNAKE']['SQL'][$key][] = $sql;
- return $this->_execute($sql, $HADLE, false);
- }
- /**
- * ??
- *
- */
- function total ($conditions = '')
- {
- $row = $this->findAll($conditions, "COUNT(*) AS `total`", '', '');
- return $row[0]['total'];
- }
- /**
- * ??
- *
- * @param mixed $conditions
- * @param mixed $fields
- * @param mixed $orderBy
- * @param mixed $limit
- * @param string $foc ??
- * @return array
- */
- function findAll ($conditions = array(), $fields = '*', $orderBy = '', $limit = array(0,30), $foc = null)
- {
- // ????
- $condition = $this->_parseConditions($conditions);
- // ????
- $fields = $this->_parseFields($fields);
- // ????
- $orderBy = $this->_parseOrderBy($orderBy);
- // ????
- $limit = $this->_parseLimit($limit);
- // ??SQL??
- $SQL = "SELECT {$fields} FROM `{$this->tableFullName}`{$condition}{$orderBy}{$limit};";
- $result = $this->_execute($SQL);
- return $this->fetchAll($result, $foc);
- }
- /**
- * ????????
- *
- * @param mixed $conditions
- * @param mixed $limit
- * @param mixed $foc
- * @return array
- */
- function findAllDESC ($conditions = array(), $limit = array(0,30), $foc = null)
- {
- return $this->findAll($conditions, '*', 'DESC', $limit, $foc);
- }
- /**
- * ??$foc??
- *
- */
- function findAllFoc ($foc = null, $conditions = array(), $fields = '*', $orderBy = '', $limit = array(0,30))
- {
- if (empty($foc)) {
- $foc = $this->primaryKey;
- }
- return $this->findAll($conditions, $fields, $orderBy, $limit, $foc);
- }
- function find ($conditions = array(), $fields = '*', $orderBy = '')
- {
- $rows = $this->findAll($conditions, $fields, $orderBy, 1);
- return $rows[0];
- }
- /**
- * ??(??)????
- * ???(INSERT INTO)??
- * $high ????????TRUE?????????????
- *
- * @param array $row
- * @param boolean $high
- * @return int
- */
- function create ($row, $high = false)
- {
- // ????????????
- $row = $this->_columnCollect($row);
- if (in_array('created', $this->fieldsName)) {
- $row['created'] = time();
- }
- if ($this->autoupdated && in_array('updated', $this->fieldsName)) {
- $row['updated'] = time();
- }
- if ($high == false) {
- unset($row[$this->primaryKey]);
- }
- // ?????
- if (empty($row)) {
- return;
- }
- $keys = array_keys($row);
- $keys = '`' . implode('` , `', $keys) . '`';
- // ???
- $values = '\'' . implode('\' , \'', $row) . '\'';
- $SQL = "INSERT INTO `{$this->tableFullName}` ({$keys}) VALUES ({$values});";
- $this->_execute($SQL);
- return mysql_insert_id($this->handle);
- }
- /**
- * ?????????
- *
- * @param mixed $conditons
- * @param mixed $values
- * @return mixed
- */
- function updateByCondition ($conditons, $values)
- {
- // ???????????????????????
- if (empty($conditons) || empty($values)) {
- return;
- }
- // ????
- $conditons = $this->_parseConditions($conditons);
- if (is_array($values)) {
- // ?????????????????????????
- unset($values[$this->primaryKey]);
- if (in_array('updated', $this->fieldsName)) {
- $values['updated'] = time();
- }
-
- // ???????????
- $values = $this->_columnCollect($values);
- // ?????
- $keysValues = $this->kvstring($values);
-
- } else {
- $keysValues = $values;
- }
- if (empty($conditons) || empty($keysValues)) {
- return;
- }
- $SQL = "UPDATE `{$this->tableFullName}` SET{$keysValues}{$conditons}";
- return $this->_execute($SQL);
- }
- /**
- * ?????????
- *
- * @param array $row
- * @return mixed
- */
- function update (& $row)
- {
- return $this->updateByCondition($row[$this->primaryKey], $row);
- }
- /**
- * ????
- *
- * @param int $id
- * @param mixed $keys
- * @param string $plus
- * @param int $step
- * @return unknown
- */
- function updatecounter ($id, $keys, $plus = '+', $step = 1)
- {
- if (is_array($keys)) {
- foreach ((array)$keys as $key) {
- $updaterows[] = "`$key` = `{$key}`{$plus}{$step}";
- $updaterow = ' ' . implode(' , ', $updaterows);
- }
- } else {
- $updaterow = " `{$keys}` = `{$keys}`{$plus}{$step}";
- }
- return $this->updateByCondition($id, $updaterow);
- }
- /**
- * ????
- *
- * @param unknown_type $conditions
- * @param unknown_type $limit
- * @return unknown
- */
- function _delete ($conditions, $limit = 1)
- {
- $limitStr = '';
- $conditions = $this->_parseConditions($conditions);
- if ($limit != 0) {
- $limitStr = 'LIMIT ' . $limit;
- }
- if (! empty($conditions)) {
- $SQL = "DELETE FROM `{$this->tableFullName}` {$conditions} {$limitStr}";
- return $this->_execute($SQL);
- }
- return false;
- }
- function remove ($conditions)
- {
- return $this->_delete(array($this->primaryKey => $conditions), 1);
- }
- /**
- * ??mysql_fetch_all????
- *
- * @param mysql result $result
- * @return array
- */
- function fetchAll ($result, $key = '', $mysqlMethod = 'mysql_fetch_assoc')
- {
- $usedMethod = array('mysql_fetch_assoc' , 'mysql_fetch_row' , 'mysql_fetch_array' , 'mysql_fetch_field' , 'mysql_fetch_object');
- if (! in_array($mysqlMethod, $usedMethod)) {
- $mysqlMethod = 'mysql_fetch_assoc';
- }
- if (! is_resource($result) && is_string($result)) {
- if (trim($result) != '') {
- $result = $this->_execute($result);
- }
- }
- while ($row = $mysqlMethod($result)) {
- if ($key == '') {
- $rows[] = $row;
- } else {
- $rows[$row[$key]] = $row;
- }
- }
- return $rows;
- }
- function fetchRow ($result, $key = '')
- {
- $rows = $this->fetchAll($result, $key);
- return $rows[0];
- }
- /**
- * ???????????2????
- *
- * @param array $array
- * @return array[string x 2]
- */
- function fieldsValues ($array)
- {
- $fields = array_keys($array);
- $return['fields'] = '`' . implode('`, `', $fields) . '`';
- $return['values'] = "'" . implode("', '", $array) . "'";
- return $return;
- }
- /**
- * ?Key?Value?????????
- *
- * @param unknown_type $row
- * @return unknown
- */
- function kvstring ($row)
- {
- foreach ((array)$row as $key => $value) {
- $return[] = " `{$key}` = '{$value}' ";
- }
- return implode(',', $return);
- }
- /**
- * ??????????????
- *
- * @param array $row
- * @return array
- */
- function _columnCollect ($row)
- {
- $keys = array_intersect(array_keys($row), $this->fieldsName);
- foreach ((array)$keys as $key) {
- $return[$key] = addslashes($row[$key]);
- }
- return $return;
- }
- /**
- * ????
- *
- * @param mixed $conditions
- * @return string
- */
- function _parseConditions ($conditions)
- {
- if (empty($conditions)) {
- return;
- }
- if (is_numeric($conditions)) {
- $conditions = $this->_intConditions($conditions);
- }
- if (is_array($conditions)) {
- $conditions = $this->_columnCollect($conditions);
- if (empty($conditions)) {
- return;
- }
- $conditions = $this->_arrayCondition($conditions);
- }
- if (is_string($conditions)) {
- $conditions = $this->_stringCondition($conditions);
- }
- return $conditions;
- }
- /**
- * ?????
- *
- * @param int $conditions
- * @return array
- */
- function _intConditions ($conditions)
- {
- $return[$this->primaryKey] = "{$conditions}";
- return $return;
- }
- /**
- * ?????
- *
- * @param array $conditions
- * @return string
- */
- function _arrayCondition ($conditions)
- {
- if (empty($conditions)) {
- return null;
- }
- foreach ((array)$conditions as $key => $value) {
- if (! is_array($value)) {
- $condition[] = "`{$key}` = '{$value}'";
- } else {
- $value = implode('\' , \'', array_unique($value));
- $condition[] = "`{$key}` IN ('{$value}')";
- }
- }
- $condition = implode(' AND ', $condition);
- return $condition;
- }
- /**
- * ?????
- *
- * @return string
- */
- function _stringCondition ($string)
- {
- return ' WHERE ' . $string;
- }
- function _getFields ()
- {
- $fields = $this->showColumns();
- $collected = $this->collectKeys($fields);
- return $collected;
- }
- /**
- * ???
- *
- * @param unknown_type $array
- * @return unknown
- */
- function collectKeys ($array)
- {
- foreach ((array)$array as $key => $value) {
- $keys[$value['Field']] = $value['Field'];
- }
- return $keys;
- }
- /**
- * ??????
- *
- * @return array
- */
- function showColumns ()
- {
- $sql = "SHOW COLUMNS FROM `{$this->tableFullName}`;";
- $result = $this->_execute($sql);
- $rows = $this->fetchAll($result);
- if (empty($this->primaryKey)) {
- foreach ((array)$rows as $row) {
- if ($row['Key'] == 'PRI') {
- $this->primaryKey = $row['Field'];
- break;
- }
- }
- }
- return $rows;
- }
- /**
- * ???????
- *
- * @param array $array
- */
- function _cacheTable ($array)
- {
- $cache = & new Helper_Cache();
- $cache->cacheArray('CTABLE_' . $this->tableFullName, $array);
- }
- }