AbstractDao.class.php 24 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713714715716717718719720721722723724725726727728729730731732733734735736737738739740741742743744745746747748749750751752753754755756757758759760761762763764765766767768769770771772773774775776777778779780781782783784785786787788789790791792793794795796797798799800801802803804805806807808809810811812813814815816817818819820821822823824825826827828829830831832833834835836837
  1. <?php
  2. namespace KIF\Dao;
  3. use KIF\Exception\DaoException;
  4. use KIF\Verify;
  5. use KIF\Exception\ParamsException;
  6. use Exception;
  7. use KIF\Db\MySQLi;
  8. use KIF\Dao\SqlHelper;
  9. use KIF\Core\Config;
  10. use KIF\Cache\Memcached;
  11. use KIF\Math\Math;
  12. /**
  13. * 数据库操作的抽象类
  14. * @author gaoxiaogang@yoka.com
  15. *
  16. */
  17. abstract class AbstractDao {
  18. /**
  19. * 数据表名
  20. * 该抽象类针对表的操作(如:delete、create、findBy)都是基于该属性。由继承的子类填充该值。
  21. * 好处:1、如果继承的子类只针对一个表,把tableName设为静态表名即可。
  22. * 2、如果继承的子类针对一类表操作(如根据某个唯一标志把相同结构的数据hash到多张表中),则针对不同表操作时,动态设置该值
  23. * @var string
  24. */
  25. protected $tableName;
  26. /**
  27. * 表的主键名。默认为id
  28. * @var string
  29. */
  30. protected $primaryKey = 'id';
  31. /**
  32. *
  33. * 是否使用缓存
  34. * @var boolean
  35. */
  36. private $useCache = false;
  37. /**
  38. *
  39. * 缓存失效时间 ,默认1小时
  40. * @var int
  41. */
  42. private $cache_expiration = 3600;
  43. /**
  44. *
  45. * 存放这个数据表的缓存标志。
  46. * 目的:清空这个标志,能清空这张表的所有行记录缓存
  47. * @var string
  48. */
  49. private $tableCacheFlag;
  50. /**
  51. *
  52. * 用于缓存表记录的memcache缓存集群标志
  53. * 优先使用config里的 memcached->dao_cache;如不存在,则使用 memcached->default;如仍不存在,则不启用缓存.
  54. * @var string
  55. */
  56. private $memcacheClusterFlag;
  57. /**
  58. *
  59. * 插入行为
  60. * @var string
  61. */
  62. const PARAM_CREATE_ACTION_INSERT = 'INSERT INTO';
  63. /**
  64. *
  65. * 插入,但重复时忽略
  66. * @var string
  67. */
  68. const PARAM_CREATE_ACTION_INSERT_IGNORE = 'INSERT IGNORE';
  69. /**
  70. *
  71. * 插入,但重复时完全replace
  72. * @var string
  73. */
  74. const PARAM_CREATE_ACTION_REPLACE = 'REPLACE INTO';
  75. /**
  76. *
  77. * 插入,但重复时自动转为update
  78. * @var string
  79. */
  80. const PARAM_CREATE_ACTION_ONDUPLICATE = 'ON DUPLICATE KEY UPDATE';
  81. /**
  82. * 数据库实例,可被动态切换到主库或从库
  83. *
  84. * @var KIF\Db\MySQLi
  85. */
  86. protected $db;
  87. /**
  88. *
  89. * mysql集群标志
  90. * @var string
  91. */
  92. private $cluster_flag;
  93. /**
  94. *
  95. * @param string $cluster_flag mysql集群标志
  96. *
  97. */
  98. public function __construct($cluster_flag = 'default') {
  99. $appConfig = Config::getInstance()->current();
  100. $dbConfig = $appConfig['db'];
  101. if (!$dbConfig || !isset($dbConfig[$cluster_flag]) || !is_string($dbConfig[$cluster_flag])) {
  102. throw new ParamsException("load config error:{$dbConfig}");
  103. }
  104. $this->cluster_flag = $cluster_flag;
  105. if (isset($appConfig['memcached']['dao_cache'])) {
  106. $this->memcacheClusterFlag = 'dao_cache';
  107. } elseif (isset($appConfig['memcached']['default'])) {
  108. $this->memcacheClusterFlag = 'default';
  109. } else {# 没有缓存配置,不启用缓存
  110. $this->useCache(false);
  111. }
  112. $dsn = $dbConfig[$cluster_flag];
  113. $this->db = new MySQLi($dsn);
  114. }
  115. /**
  116. *
  117. * 获取设置的主键名
  118. * @return string
  119. */
  120. public function getPrimaryKey() {
  121. return $this->primaryKey;
  122. }
  123. /**
  124. * 设置表名
  125. * 动态设置表名是为了拆表
  126. * @param $tableName
  127. */
  128. public function setTableName($tableName) {
  129. $this->tableName = $tableName;
  130. }
  131. /**
  132. *
  133. * 控制是否使用缓存。使用缓存,会将数据表的行记录缓存起来,极大的提高效率和高可用;同时也会在记录删除、更新时,清空对应的缓存。
  134. * @param boolean $useCache
  135. */
  136. public function useCache($useCache = true) {
  137. $this->useCache = (bool) $useCache;
  138. }
  139. /**
  140. * 解析条件
  141. * @param array|string $condition
  142. * @throws Exception parameter errors
  143. * @return string
  144. */
  145. public function parseCondition($condition = null) {
  146. if(empty($condition)) return '';
  147. // 如果条件语句是字符串 直接拼接
  148. if(is_string($condition)){
  149. return "
  150. WHERE ". $condition ." ";
  151. }
  152. // 如果不是字符串 强制传递数组 如果不是数组抛异常
  153. if(!is_array($condition)){
  154. throw new Exception('parameter errors');
  155. }
  156. $condition = $this->quote($condition);
  157. $where = array();
  158. foreach($condition as $tmpK => $tmpV) {
  159. # 避免条件字段重复,支持二维数组。比如$conditon = array(
  160. # array(
  161. # 'publish_time' => SqlHelper::addCompareOperator(">=", $s_ts),
  162. # ),
  163. # array(
  164. # 'publish_time' => SqlHelper::addCompareOperator("<", $e_ts),
  165. # ),
  166. # );
  167. # 以前只能这样变相支持:$condition = array(
  168. # 'publish_time' => SqlHelper::addCompareOperator(">=", $s_ts),
  169. # '`publish_time`' => SqlHelper::addCompareOperator("<", $e_ts),
  170. # );
  171. if (Verify::naturalNumber($tmpK)) {
  172. list($key, $val) = each($tmpV);
  173. } else {
  174. $key = $tmpK;
  175. $val = $tmpV;
  176. }
  177. if (strpos($key, '`') === false // 没用使用 ` 字符,比如 `status`
  178. && strpos($key, '(') === false) // 也不含有括号(即不是函数),比如 last_insert_id(status)
  179. {
  180. $key = "`{$key}`";
  181. }
  182. if(is_scalar($val)) {
  183. $where[] = "{$key} " . SqlHelper::explodeCompareOperator($val);
  184. } elseif(is_array($val)) {
  185. $where[] = "{$key} IN (".join(',', $val).")";
  186. } else {
  187. throw new Exception('parameter errors');
  188. }
  189. }
  190. return "
  191. WHERE ".join(' && ', $where)."
  192. ";
  193. }
  194. /**
  195. * 根据条件查找主键id列表集
  196. * 做这个方法,是出于架构上的考虑。只取出主键id集,然后通过主键id集才去取数据(内存或数据库)
  197. * @param array|String $condition 条件
  198. * @param string | int $limit 指定分页
  199. * @param string $order 指定排序
  200. * @return array 如:array(123, 124)。无值时返回空数组
  201. */
  202. public function findIdsBy($condition = null, $limit = null, $order = null) {
  203. $result = $this->findBy($condition, $this->primaryKey, $limit, $this->primaryKey, $order);
  204. if (! $result) {
  205. return array();
  206. }
  207. return array_keys($result);
  208. }
  209. /**
  210. * 查找的底层方法。对查找只提供有限支持,太复杂的查询请手动sql
  211. * final 让该方法禁止被继承
  212. * 1、根据条件查找,多条件只支持 与(&&),不支持或之类的
  213. * 2、支持limit
  214. * 3、group by
  215. * @param array|string $condition
  216. * @param string $returnAssociateKey 如果指定该指,返回的值不再以0,1,2为key,而是以其对应的值为key
  217. * @param string | int $limit
  218. * @param string $selectCols 要获取的列。语法:id, uid ,默认为 *
  219. * @param string $order 指定排序
  220. * @return array
  221. */
  222. final public function findBy($condition = null, $returnAssociateKey = null, $limit = null, $selectCols = '*', $order = null) {
  223. $where = $this->parseCondition($condition);
  224. if (!isset($limit) || !preg_match('#^(?:\d+\s*,\s*)?\d+$#', $limit)) {
  225. $strLimit = ' ';
  226. } else {
  227. $strLimit = " LIMIT $limit ";
  228. }
  229. $strOrder = '';
  230. if(!empty($order)) {
  231. $strOrder = " ORDER BY {$order} ";
  232. }
  233. if(!isset($selectCols)) {
  234. $selectCols = '*';
  235. }
  236. $sql = "SELECT {$selectCols} FROM {$this->tableName}
  237. {$where}
  238. {$strOrder}
  239. {$strLimit}
  240. ;
  241. ";
  242. return $this->db->fetchAll($sql, $returnAssociateKey);
  243. }
  244. public function fetchOne($condition = null, $selectCols = '*', $order = null) {
  245. $result = self::findBy($condition, null, 1, $selectCols, $order);
  246. if (!$result) {
  247. return false;
  248. }
  249. return array_pop($result);
  250. }
  251. public function fetchAll($condition = null, $limit = 10, $order = null, $selectCols = '*') {
  252. $result = self::findBy($condition, null, $limit, $selectCols, $order);
  253. if (!$result) {
  254. return false;
  255. }
  256. return $result;
  257. }
  258. /**
  259. * 获取所有
  260. * @param string $order 排序方式。默认以主键倒序;有效的格式:"create_time desc"
  261. * @param string $limit 该参数用于支持分页,默认为不使用分页。格式 "offset, length"
  262. * @return array
  263. */
  264. public function getsAll($order = null, $limit = null) {
  265. $ids = self::getsAllIds($order, $limit);
  266. return $this->gets($ids);
  267. }
  268. /**
  269. * 获取所有id列表集
  270. * @param string $order 排序方式。默认以主键倒序;有效的格式:"create_time desc"
  271. * @param string $limit 该参数用于支持分页,默认为不使用分页。格式 "offset, length"
  272. * @return array
  273. */
  274. public function getsAllIds($order = null, $limit = null) {
  275. if (is_null($order)) {
  276. $order = "{$this->primaryKey} desc";
  277. }
  278. if (!is_string($order)) {
  279. throw new Exception('$order 必须是字符串或null');
  280. }
  281. $condition = null;
  282. $ids = self::findIdsBy($condition, $limit, $order);
  283. return $ids;
  284. }
  285. /**
  286. * 获取指定$id
  287. * @param int | string $id
  288. * @return false | array
  289. */
  290. public function get($id) {
  291. if (Verify::int($id)) {
  292. if ($id < 1) {
  293. return false;
  294. }
  295. } elseif (is_string($id)) {
  296. if (strlen($id) == 0) {
  297. return false;
  298. }
  299. } else {
  300. return false;
  301. }
  302. $result = self::gets(array($id));// 调用该类自身的gets方法,而不是继承者的gets方法!
  303. if (!$result) {
  304. return false;
  305. }
  306. return array_pop($result);
  307. }
  308. /**
  309. * 批量获取信息
  310. * @param array $ids id组成的数组
  311. * @return array 无结果时返回空数组
  312. */
  313. public function gets(array $ids) {
  314. $return = array();
  315. if (empty($ids)) {
  316. return $return;
  317. }
  318. $ids = array_unique($ids);
  319. # 从缓存取
  320. $cache_return = self::getsFromCache($ids);
  321. $non_cache_ids = array_diff($ids, array_keys($cache_return));
  322. if (!$non_cache_ids) {
  323. return $cache_return;
  324. }
  325. # 从DB取
  326. $db_return = self::getsFromDB($non_cache_ids);
  327. if ($db_return) {
  328. self::setToCache($db_return);
  329. }
  330. foreach ($ids as $id) {
  331. if (isset($cache_return[$id])) {
  332. $return[$id] = $cache_return[$id];
  333. continue;
  334. }
  335. if (isset($db_return[$id])) {
  336. $return[$id] = $db_return[$id];
  337. continue;
  338. }
  339. }
  340. return $return;
  341. }
  342. /**
  343. *
  344. * 从后端数据库取结果
  345. * @param array $ids
  346. * @return array 格式:array(
  347. * (int) id => (mixed) value,
  348. * ... ,
  349. * )
  350. *
  351. */
  352. private function getsFromDB(array $ids) {
  353. $return = array();
  354. $condition = array(
  355. $this->primaryKey => $ids,
  356. );
  357. $result = $this->findBy($condition, $this->primaryKey);
  358. if (!$result) {
  359. return $return;
  360. }
  361. foreach ($ids as $id) {
  362. $id = (string) $id;// php的数组下标是int整时,如果超出了操作系统平台的最大有符号正整数后,会取不到值。转成字符串型,解决这个bug
  363. if (array_key_exists($id, $result)) {
  364. $return[$id] = $result[$id];
  365. }
  366. }
  367. return $return;
  368. }
  369. /**
  370. *
  371. * 获取表缓存的标志。后续这个表里每行的缓存,都会关联上这个表缓存标志。
  372. * 所以只要清空这个标志,即可使整个表记录的缓存失效。
  373. * @Exception DaoException::CACHE_SET_TABLE_FLAG_ERROR
  374. * @Exception DaoException::CACHE_SERVICE_UNAVAILABLE
  375. * @return String
  376. */
  377. public function getTableCacheFlag() {
  378. if ($this->tableCacheFlag) {
  379. return $this->tableCacheFlag;
  380. }
  381. $dbconfig = Config::getInstance()->get('db');
  382. $cacheKey_elements = array(
  383. serialize($dbconfig[$this->cluster_flag]),
  384. $this->tableName,
  385. 'table_cache_flag',
  386. );
  387. $key = Math::md5_16(join($cacheKey_elements, '::'));
  388. $objMemcached = new Memcached($this->memcacheClusterFlag);
  389. $tmpGetResult = $objMemcached->get($key);
  390. if ($tmpGetResult) {
  391. $this->tableCacheFlag = $tmpGetResult;
  392. return $this->tableCacheFlag;
  393. }
  394. if (is_null($tmpGetResult)) {// 缓存不存在,建立
  395. $tmpVal = microtime(true);
  396. if ($objMemcached->set($key, $tmpVal, 0)) {// 永不过期
  397. $this->tableCacheFlag = $tmpVal;
  398. return $this->tableCacheFlag;
  399. }
  400. # TODO 设置缓存失败的处理
  401. throw new DaoException(DaoException::CACHE_SET_TABLE_FLAG_ERROR);
  402. }
  403. if (!$tmpGetResult) {
  404. # TODO memcache服务不可用的处理
  405. throw new DaoException(DaoException::CACHE_SERVICE_UNAVAILABLE);
  406. }
  407. }
  408. /**
  409. *
  410. * 获取指定ids集对应的缓存key集
  411. * @param array $ids array(13343, 9939);
  412. * @Exception DaoException::CACHE_SET_TABLE_FLAG_ERROR
  413. * @Exception DaoException::CACHE_SERVICE_UNAVAILABLE
  414. * @return array 格式:array(
  415. * (int) id => (string) cacheKey,
  416. * ... ,
  417. *
  418. * )
  419. */
  420. public function getsCacheKeys(array $ids) {
  421. $tableCacheFlag = $this->getTableCacheFlag();
  422. $cacheKeys = array();
  423. $dbconfig = Config::getInstance()->get('db');
  424. foreach ($ids as $id) {
  425. $cacheKey_elements = array(
  426. $tableCacheFlag,
  427. serialize($dbconfig[$this->cluster_flag]),
  428. $this->tableName,
  429. $id,
  430. 'row_cache',// 这是针对数据库记录行的缓存
  431. );
  432. $cacheKeys[$id] = Math::md5_16(join($cacheKey_elements, '::'));
  433. }
  434. return $cacheKeys;
  435. }
  436. /**
  437. *
  438. * 从缓存里取数据表对应的行记录
  439. * @param array $ids
  440. * @return array 只返回缓存里有的值,如果缓存里没有任何值,则返回空数组
  441. */
  442. private function getsFromCache(array $ids) {
  443. $return = array();
  444. if (!$this->useCache) {
  445. return $return;
  446. }
  447. try {
  448. $cacheKeys = self::getsCacheKeys($ids);
  449. } catch (DaoException $e) {
  450. return $return;
  451. }
  452. $objMemcached = new Memcached($this->memcacheClusterFlag);
  453. $cacheVals = $objMemcached->gets($cacheKeys);
  454. $cache_return = array_combine(array_keys($cacheKeys), $cacheVals);
  455. foreach ($cacheVals as $tmpK => $tmpV) {
  456. if (is_null($tmpV)) {
  457. continue;
  458. }
  459. $return[array_search($tmpK, $cacheKeys)] = $tmpV;
  460. }
  461. return $return;
  462. }
  463. /**
  464. *
  465. * 从缓存里将$id对应的行记录删除
  466. * @param int $id
  467. * @throws Exception
  468. * @return Boolean
  469. */
  470. private function deleteFromCache($id) {
  471. if (!$this->useCache) {
  472. return false;
  473. }
  474. $objMemcached = new Memcached($this->memcacheClusterFlag);
  475. if (Verify::unsignedInt($id)) {
  476. try {
  477. $cacheKeys = self::getsCacheKeys(array($id));
  478. } catch (DaoException $e) {
  479. return false;
  480. }
  481. return $objMemcached->delete(array_pop($cacheKeys));
  482. }
  483. if (is_array($id) && !empty($id)) {
  484. try {
  485. $cacheKeys = self::getsCacheKeys($id);
  486. } catch (DaoException $e) {
  487. return false;
  488. }
  489. if (count($cacheKeys) == 1) {
  490. return $objMemcached->delete(array_pop($cacheKeys));
  491. }
  492. return $objMemcached->deletes($cacheKeys);
  493. }
  494. return false;
  495. }
  496. /**
  497. *
  498. * 清除缓存的这张表的所有记录
  499. * @return Boolean
  500. */
  501. public function deleteAllCaches() {
  502. if (!$this->useCache) {
  503. return false;
  504. }
  505. try {
  506. $tableCacheFlag = $this->getTableCacheFlag();
  507. } catch (DaoException $e) {
  508. return false;
  509. }
  510. $objMemcached = new Memcached($this->memcacheClusterFlag);
  511. return $objMemcached->delete($tableCacheFlag);
  512. }
  513. /**
  514. *
  515. * 将gets出来的结果集,存到缓存
  516. * @param array $results
  517. * @return boolean
  518. */
  519. private function setToCache(array $results) {
  520. if (!$this->useCache) {
  521. return false;
  522. }
  523. if (!$results) {
  524. return false;
  525. }
  526. try {
  527. $cacheKeys = self::getsCacheKeys(array_keys($results));
  528. } catch (DaoException $e) {
  529. return false;
  530. }
  531. $objMemcached = new Memcached($this->memcacheClusterFlag);
  532. return $objMemcached->sets(array_combine($cacheKeys, $results), $this->cache_expiration);
  533. }
  534. /**
  535. * 创建一条记录
  536. * @param array $tableInfo 待插入的数据
  537. * @param boolean $isAutoIncrement 操作成功时,如果该值为true,返回最后插入的id;否则返回true
  538. * @return boolean | int
  539. */
  540. private function _create(array $tableInfo, $isAutoIncrement = true, $action = self::PARAM_CREATE_ACTION_INSERT) {
  541. if(empty($tableInfo)) return false;
  542. switch($action) {
  543. case self::PARAM_CREATE_ACTION_INSERT :
  544. case self::PARAM_CREATE_ACTION_INSERT_IGNORE :
  545. case self::PARAM_CREATE_ACTION_REPLACE :
  546. break;
  547. default:
  548. throw new Exception('error insert action');
  549. }
  550. $sql = "{$action} {$this->tableName}
  551. SET
  552. ";
  553. $sqlSets = '';
  554. $tableInfo = $this->quote($tableInfo);
  555. foreach($tableInfo as $key => $val) {
  556. if($sqlSets != '') $sqlSets .= ' ,';
  557. $sqlSets .= "
  558. `{$key}` = {$val}
  559. ";
  560. }
  561. $sql .= $sqlSets;
  562. if($this->db->query($sql)) {
  563. if($isAutoIncrement) {
  564. $id = $this->db->insertId();
  565. # 清空这条记录的缓存
  566. self::deleteFromCache($id);
  567. return $id > 0 ? $id : true;
  568. } else {
  569. return true;
  570. }
  571. }
  572. return false;
  573. }
  574. /**
  575. * 创建一条记录,如果重复,则替换
  576. * @param array $tableInfo 待插入的数据
  577. * @param boolean $isAutoIncrement 操作成功时,如果该值为true,返回最后插入的id;否则返回true
  578. * @return boolean | int
  579. */
  580. public function replace(array $tableInfo, $isAutoIncrement = true) {
  581. return $this->_create($tableInfo, $isAutoIncrement, self::PARAM_CREATE_ACTION_REPLACE);
  582. }
  583. /**
  584. * 创建一条记录
  585. * @param array $tableInfo 待插入的数据
  586. * @param boolean $isAutoIncrement 操作成功时,如果该值为true,返回最后插入的id;否则返回true
  587. * @return boolean | int
  588. */
  589. public function create(array $tableInfo, $isAutoIncrement = true) {
  590. return $this->_create($tableInfo, $isAutoIncrement, self::PARAM_CREATE_ACTION_INSERT);
  591. }
  592. /**
  593. * 创建一条记录,如果重复,则忽略
  594. * @param array $tableInfo 待插入的数据
  595. * @param boolean $isAutoIncrement 操作成功时,如果该值为true,返回最后插入的id;否则返回true
  596. * @return boolean | int PS:$isAutoIncrement = true时:1、如果插入了,返回自动id值;2、如果已存在,返回true。
  597. */
  598. public function insertIgnore(array $tableInfo, $isAutoIncrement = true) {
  599. return $this->_create($tableInfo, $isAutoIncrement, self::PARAM_CREATE_ACTION_INSERT_IGNORE);
  600. }
  601. /**
  602. *
  603. * 插入一条记录,如果重复,自动转为更新语句
  604. * @param array $tableInfo
  605. * @param array $onDuplicate 如果重复时,需要更新的信息。如果不指定,则使用$tableInfo的值,即认为要全部更新
  606. * @return int | Boolean
  607. * int:只要存在,无论之前记录是否存在,都会返回记录的id;
  608. * true:执行成功,但获取记录id时失败;
  609. * false:执行失败
  610. */
  611. public function insertDuplicate(array $tableInfo, array $onDuplicate = array()) {
  612. if (!$tableInfo) {
  613. return false;
  614. }
  615. $tmpArrKeys = array();
  616. foreach ($tableInfo as $tmpKey => $tmpV) {
  617. $tmpArrKeys[] = "`{$tmpKey}`";
  618. }
  619. $sql = "INSERT INTO {$this->tableName} (" . join(', ', $tmpArrKeys). ") VALUES ";
  620. $tmpArrValues = array();
  621. $new_tableInfo = $this->quote($tableInfo);
  622. foreach ($new_tableInfo as $tmpKey => $tmpV) {
  623. $tmpArrValues[] = $tmpV;
  624. }
  625. $sql .= " ( " . join(', ', $tmpArrValues) . " ) ";
  626. $sql .= "
  627. ON DUPLICATE KEY UPDATE
  628. ";
  629. $tmpArrDps = array();
  630. if (empty($onDuplicate)) {
  631. $onDuplicate = $tableInfo;
  632. }
  633. $new_onDuplicate = $this->quote($onDuplicate);
  634. foreach ($new_onDuplicate as $tmpKey => $tmpV) {
  635. $tmpArrDps[] = " `{$tmpKey}` = {$tmpV} ";
  636. }
  637. $sql .= join(', ', $tmpArrDps);
  638. if (!$this->db->query($sql)) {
  639. return false;
  640. }
  641. $id = $this->db->insertId();
  642. # 清空这条记录的缓存
  643. self::deleteFromCache($id);
  644. return $id > 0 ? $id : true;
  645. }
  646. /**
  647. * 根据条件更新指定数据
  648. * @param array $tableInfo 待更新的数据(与数据库字段对应的数据)
  649. * @param array $condition 条件(与数据库字段对应的数据)
  650. * @return boolean
  651. */
  652. public function update(array $tableInfo, array $condition) {
  653. if(empty($tableInfo)) return false;
  654. $sql = "UPDATE {$this->tableName}
  655. SET
  656. ";
  657. $sqlSets = '';
  658. foreach($tableInfo as $key => $val) {
  659. if($sqlSets != '') $sqlSets .= ' ,';
  660. $sqlSets .= "
  661. `{$key}` = {$this->quote($val)}
  662. ";
  663. }
  664. $sql .= $sqlSets;
  665. $where = $this->parseCondition($condition);
  666. $sql .= "
  667. {$where}
  668. ;
  669. ";
  670. $tmpQueryResult = $this->db->query($sql);
  671. if ($tmpQueryResult) {
  672. if (isset($condition[$this->primaryKey])) {
  673. # 清空这条记录的缓存,条件里有可能是一批ids
  674. self::deleteFromCache($condition[$this->primaryKey]);
  675. } else {
  676. $this->deleteAllCaches();
  677. }
  678. }
  679. return $tmpQueryResult;
  680. }
  681. /**
  682. * 根据条件删除数据
  683. * @param Array|String $condition 条件
  684. * @return boolean
  685. */
  686. public function delete($condition) {
  687. $where = $this->parseCondition($condition);
  688. $sql = "DELETE FROM {$this->tableName}
  689. {$where}
  690. ;
  691. ";
  692. $tmpQueryResult = $this->db->query($sql);
  693. if ($tmpQueryResult) {
  694. if (isset($condition[$this->primaryKey])) {
  695. # 清空这条记录的缓存,条件里有可能是一批ids
  696. self::deleteFromCache($condition[$this->primaryKey]);
  697. } else {
  698. $this->deleteAllCaches();
  699. }
  700. }
  701. return $tmpQueryResult;
  702. }
  703. /**
  704. * 转义数据
  705. * @param mixed $data
  706. */
  707. public function quote($data) {
  708. return SqlHelper::escape($data, true);
  709. }
  710. /**
  711. * 判断给定的值是否是有效的自增主键值
  712. * @param mixid $pk
  713. * @return boolean
  714. */
  715. static protected function isValidPK($pk) {
  716. return SqlHelper::isValidPK($pk);
  717. }
  718. /**
  719. * 判断给定数组的某个key的值,是否是有效的自增主键值
  720. * @param array $arr
  721. * @param mixid $key
  722. * @return boolean
  723. */
  724. static protected function isValidPKWithArray(array $arr, $key) {
  725. return SqlHelper::isValidPKWithArray($arr, $key);
  726. }
  727. /**
  728. * 获取指定条件的记录数
  729. * @return int
  730. */
  731. public function totals($condtion = null){
  732. $result = $this->findBy($condtion, null, null, "count(*)");
  733. if (!$result) {
  734. return 0;
  735. }
  736. return (int) array_pop(array_pop($result));
  737. }
  738. }