Sqlsrv.php 5.0 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159
  1. <?php
  2. // +----------------------------------------------------------------------
  3. // | ThinkPHP [ WE CAN DO IT JUST THINK IT ]
  4. // +----------------------------------------------------------------------
  5. // | Copyright (c) 2006-2012 http://thinkphp.cn All rights reserved.
  6. // +----------------------------------------------------------------------
  7. // | Licensed ( http://www.apache.org/licenses/LICENSE-2.0 )
  8. // +----------------------------------------------------------------------
  9. // | Author: liu21st <liu21st@gmail.com>
  10. // +----------------------------------------------------------------------
  11. namespace think\db\builder;
  12. use think\db\Builder;
  13. use think\db\Expression;
  14. use think\db\Query;
  15. use think\Exception;
  16. /**
  17. * Sqlsrv数据库驱动
  18. */
  19. class Sqlsrv extends Builder
  20. {
  21. protected $selectSql = 'SELECT T1.* FROM (SELECT thinkphp.*, ROW_NUMBER() OVER (%ORDER%) AS ROW_NUMBER FROM (SELECT %DISTINCT% %FIELD% FROM %TABLE%%JOIN%%WHERE%%GROUP%%HAVING%) AS thinkphp) AS T1 %LIMIT%%COMMENT%';
  22. protected $selectInsertSql = 'SELECT %DISTINCT% %FIELD% FROM %TABLE%%JOIN%%WHERE%%GROUP%%HAVING%';
  23. protected $updateSql = 'UPDATE %TABLE% SET %SET% FROM %TABLE% %JOIN% %WHERE% %LIMIT% %LOCK%%COMMENT%';
  24. protected $deleteSql = 'DELETE FROM %TABLE% %USING% FROM %TABLE% %JOIN% %WHERE% %LIMIT% %LOCK%%COMMENT%';
  25. protected $insertSql = 'INSERT INTO %TABLE% (%FIELD%) VALUES (%DATA%) %COMMENT%';
  26. protected $insertAllSql = 'INSERT INTO %TABLE% (%FIELD%) %DATA% %COMMENT%';
  27. /**
  28. * order分析
  29. * @access protected
  30. * @param Query $query 查询对象
  31. * @param mixed $order
  32. * @return string
  33. */
  34. protected function parseOrder(Query $query, $order)
  35. {
  36. if (empty($order)) {
  37. return ' ORDER BY rand()';
  38. }
  39. foreach ($order as $key => $val) {
  40. if ($val instanceof Expression) {
  41. $array[] = $val->getValue();
  42. } elseif ('[rand]' == $val) {
  43. $array[] = $this->parseRand($query);
  44. } else {
  45. if (is_numeric($key)) {
  46. list($key, $sort) = explode(' ', strpos($val, ' ') ? $val : $val . ' ');
  47. } else {
  48. $sort = $val;
  49. }
  50. if (preg_match('/^[\w\.]+$/', $key)) {
  51. $sort = strtoupper($sort);
  52. $sort = in_array($sort, ['ASC', 'DESC'], true) ? ' ' . $sort : '';
  53. $array[] = $this->parseKey($query, $key, true) . $sort;
  54. } else {
  55. throw new Exception('order express error:' . $key);
  56. }
  57. }
  58. }
  59. return empty($array) ? '' : ' ORDER BY ' . implode(',', $array);
  60. }
  61. /**
  62. * 随机排序
  63. * @access protected
  64. * @param Query $query 查询对象
  65. * @return string
  66. */
  67. protected function parseRand(Query $query)
  68. {
  69. return 'rand()';
  70. }
  71. /**
  72. * 字段和表名处理
  73. * @access public
  74. * @param Query $query 查询对象
  75. * @param mixed $key 字段名
  76. * @param bool $strict 严格检测
  77. * @return string
  78. */
  79. public function parseKey(Query $query, $key, $strict = false)
  80. {
  81. if (is_numeric($key)) {
  82. return $key;
  83. } elseif ($key instanceof Expression) {
  84. return $key->getValue();
  85. }
  86. $key = trim($key);
  87. if (strpos($key, '.') && !preg_match('/[,\'\"\(\)\[\s]/', $key)) {
  88. list($table, $key) = explode('.', $key, 2);
  89. $alias = $query->getOptions('alias');
  90. if ('__TABLE__' == $table) {
  91. $table = $query->getOptions('table');
  92. $table = is_array($table) ? array_shift($table) : $table;
  93. }
  94. if (isset($alias[$table])) {
  95. $table = $alias[$table];
  96. }
  97. }
  98. if ($strict && !preg_match('/^[\w\.\*]+$/', $key)) {
  99. throw new Exception('not support data:' . $key);
  100. }
  101. if ('*' != $key && !preg_match('/[,\'\"\*\(\)\[.\s]/', $key)) {
  102. $key = '[' . $key . ']';
  103. }
  104. if (isset($table)) {
  105. $key = '[' . $table . '].' . $key;
  106. }
  107. return $key;
  108. }
  109. /**
  110. * limit
  111. * @access protected
  112. * @param Query $query 查询对象
  113. * @param mixed $limit
  114. * @return string
  115. */
  116. protected function parseLimit(Query $query, $limit)
  117. {
  118. if (empty($limit)) {
  119. return '';
  120. }
  121. $limit = explode(',', $limit);
  122. if (count($limit) > 1) {
  123. $limitStr = '(T1.ROW_NUMBER BETWEEN ' . $limit[0] . ' + 1 AND ' . $limit[0] . ' + ' . $limit[1] . ')';
  124. } else {
  125. $limitStr = '(T1.ROW_NUMBER BETWEEN 1 AND ' . $limit[0] . ")";
  126. }
  127. return 'WHERE ' . $limitStr;
  128. }
  129. public function selectInsert(Query $query, $fields, $table)
  130. {
  131. $this->selectSql = $this->selectInsertSql;
  132. return parent::selectInsert($query, $fields, $table);
  133. }
  134. }