/libraries/joomla/database/query/sqlsrv.php

https://gitlab.com/vitaliylukin91/alex-lavka · PHP · 360 lines · 171 code · 50 blank · 139 comment · 27 complexity · f55e40f319205b41c3b1b8683835d711 MD5 · raw file

  1. <?php
  2. /**
  3. * @package Joomla.Platform
  4. * @subpackage Database
  5. *
  6. * @copyright Copyright (C) 2005 - 2015 Open Source Matters, Inc. All rights reserved.
  7. * @license GNU General Public License version 2 or later; see LICENSE
  8. */
  9. defined('JPATH_PLATFORM') or die;
  10. /**
  11. * Query Building Class.
  12. *
  13. * @since 11.1
  14. */
  15. class JDatabaseQuerySqlsrv extends JDatabaseQuery implements JDatabaseQueryLimitable
  16. {
  17. /**
  18. * The character(s) used to quote SQL statement names such as table names or field names,
  19. * etc. The child classes should define this as necessary. If a single character string the
  20. * same character is used for both sides of the quoted name, else the first character will be
  21. * used for the opening quote and the second for the closing quote.
  22. *
  23. * @var string
  24. * @since 11.1
  25. */
  26. protected $name_quotes = '`';
  27. /**
  28. * The null or zero representation of a timestamp for the database driver. This should be
  29. * defined in child classes to hold the appropriate value for the engine.
  30. *
  31. * @var string
  32. * @since 11.1
  33. */
  34. protected $null_date = '1900-01-01 00:00:00';
  35. /**
  36. * @var integer The affected row limit for the current SQL statement.
  37. * @since 3.2 CMS
  38. */
  39. protected $limit = 0;
  40. /**
  41. * @var integer The affected row offset to apply for the current SQL statement.
  42. * @since 3.2 CMS
  43. */
  44. protected $offset = 0;
  45. /**
  46. * Magic function to convert the query to a string.
  47. *
  48. * @return string The completed query.
  49. *
  50. * @since 11.1
  51. */
  52. public function __toString()
  53. {
  54. $query = '';
  55. switch ($this->type)
  56. {
  57. case 'select':
  58. $query .= (string) $this->select;
  59. $query .= (string) $this->from;
  60. if ($this->join)
  61. {
  62. // Special case for joins
  63. foreach ($this->join as $join)
  64. {
  65. $query .= (string) $join;
  66. }
  67. }
  68. if ($this->where)
  69. {
  70. $query .= (string) $this->where;
  71. }
  72. if ($this->group)
  73. {
  74. $query .= (string) $this->group;
  75. }
  76. if ($this->order)
  77. {
  78. $query .= (string) $this->order;
  79. }
  80. if ($this->having)
  81. {
  82. $query .= (string) $this->having;
  83. }
  84. if ($this instanceof JDatabaseQueryLimitable && ($this->limit > 0 || $this->offset > 0))
  85. {
  86. $query = $this->processLimit($query, $this->limit, $this->offset);
  87. }
  88. break;
  89. case 'insert':
  90. $query .= (string) $this->insert;
  91. // Set method
  92. if ($this->set)
  93. {
  94. $query .= (string) $this->set;
  95. }
  96. // Columns-Values method
  97. elseif ($this->values)
  98. {
  99. if ($this->columns)
  100. {
  101. $query .= (string) $this->columns;
  102. }
  103. $elements = $this->insert->getElements();
  104. $tableName = array_shift($elements);
  105. $query .= 'VALUES ';
  106. $query .= (string) $this->values;
  107. if ($this->autoIncrementField)
  108. {
  109. $query = 'SET IDENTITY_INSERT ' . $tableName . ' ON;' . $query . 'SET IDENTITY_INSERT ' . $tableName . ' OFF;';
  110. }
  111. if ($this->where)
  112. {
  113. $query .= (string) $this->where;
  114. }
  115. }
  116. break;
  117. case 'delete':
  118. $query .= (string) $this->delete;
  119. $query .= (string) $this->from;
  120. if ($this->join)
  121. {
  122. // Special case for joins
  123. foreach ($this->join as $join)
  124. {
  125. $query .= (string) $join;
  126. }
  127. }
  128. if ($this->where)
  129. {
  130. $query .= (string) $this->where;
  131. }
  132. if ($this->order)
  133. {
  134. $query .= (string) $this->order;
  135. }
  136. break;
  137. case 'update':
  138. $query .= (string) $this->update;
  139. if ($this->join)
  140. {
  141. // Special case for joins
  142. foreach ($this->join as $join)
  143. {
  144. $query .= (string) $join;
  145. }
  146. }
  147. $query .= (string) $this->set;
  148. if ($this->where)
  149. {
  150. $query .= (string) $this->where;
  151. }
  152. if ($this->order)
  153. {
  154. $query .= (string) $this->order;
  155. }
  156. break;
  157. default:
  158. $query = parent::__toString();
  159. break;
  160. }
  161. return $query;
  162. }
  163. /**
  164. * Casts a value to a char.
  165. *
  166. * Ensure that the value is properly quoted before passing to the method.
  167. *
  168. * @param string $value The value to cast as a char.
  169. *
  170. * @return string Returns the cast value.
  171. *
  172. * @since 11.1
  173. */
  174. public function castAsChar($value)
  175. {
  176. return 'CAST(' . $value . ' as NVARCHAR(10))';
  177. }
  178. /**
  179. * Gets the function to determine the length of a character string.
  180. *
  181. * @param string $field A value.
  182. * @param string $operator Comparison operator between charLength integer value and $condition
  183. * @param string $condition Integer value to compare charLength with.
  184. *
  185. * @return string The required char length call.
  186. *
  187. * @since 11.1
  188. */
  189. public function charLength($field, $operator = null, $condition = null)
  190. {
  191. return 'DATALENGTH(' . $field . ')' . (isset($operator) && isset($condition) ? ' ' . $operator . ' ' . $condition : '');
  192. }
  193. /**
  194. * Concatenates an array of column names or values.
  195. *
  196. * @param array $values An array of values to concatenate.
  197. * @param string $separator As separator to place between each value.
  198. *
  199. * @return string The concatenated values.
  200. *
  201. * @since 11.1
  202. */
  203. public function concatenate($values, $separator = null)
  204. {
  205. if ($separator)
  206. {
  207. return '(' . implode('+' . $this->quote($separator) . '+', $values) . ')';
  208. }
  209. else
  210. {
  211. return '(' . implode('+', $values) . ')';
  212. }
  213. }
  214. /**
  215. * Gets the current date and time.
  216. *
  217. * @return string
  218. *
  219. * @since 11.1
  220. */
  221. public function currentTimestamp()
  222. {
  223. return 'GETDATE()';
  224. }
  225. /**
  226. * Get the length of a string in bytes.
  227. *
  228. * @param string $value The string to measure.
  229. *
  230. * @return integer
  231. *
  232. * @since 11.1
  233. */
  234. public function length($value)
  235. {
  236. return 'LEN(' . $value . ')';
  237. }
  238. /**
  239. * Add to the current date and time.
  240. * Usage:
  241. * $query->select($query->dateAdd());
  242. * Prefixing the interval with a - (negative sign) will cause subtraction to be used.
  243. *
  244. * @param datetime $date The date to add to; type may be time or datetime.
  245. * @param string $interval The string representation of the appropriate number of units
  246. * @param string $datePart The part of the date to perform the addition on
  247. *
  248. * @return string The string with the appropriate sql for addition of dates
  249. *
  250. * @since 13.1
  251. * @note Not all drivers support all units.
  252. * @link http://msdn.microsoft.com/en-us/library/ms186819.aspx for more information
  253. */
  254. public function dateAdd($date, $interval, $datePart)
  255. {
  256. return "DATEADD('" . $datePart . "', '" . $interval . "', '" . $date . "'" . ')';
  257. }
  258. /**
  259. * Method to modify a query already in string format with the needed
  260. * additions to make the query limited to a particular number of
  261. * results, or start at a particular offset.
  262. *
  263. * @param string $query The query in string format
  264. * @param integer $limit The limit for the result set
  265. * @param integer $offset The offset for the result set
  266. *
  267. * @return string
  268. *
  269. * @since 12.1
  270. */
  271. public function processLimit($query, $limit, $offset = 0)
  272. {
  273. if ($limit == 0 && $offset == 0)
  274. {
  275. return $query;
  276. }
  277. $start = $offset + 1;
  278. $end = $offset + $limit;
  279. $orderBy = stristr($query, 'ORDER BY');
  280. if (is_null($orderBy) || empty($orderBy))
  281. {
  282. $orderBy = 'ORDER BY (select 0)';
  283. }
  284. $query = str_ireplace($orderBy, '', $query);
  285. $rowNumberText = ', ROW_NUMBER() OVER (' . $orderBy . ') AS RowNumber FROM ';
  286. $query = preg_replace('/\sFROM\s/i', $rowNumberText, $query, 1);
  287. $query = 'SELECT * FROM (' . $query . ') A WHERE A.RowNumber BETWEEN ' . $start . ' AND ' . $end;
  288. return $query;
  289. }
  290. /**
  291. * Sets the offset and limit for the result set, if the database driver supports it.
  292. *
  293. * Usage:
  294. * $query->setLimit(100, 0); (retrieve 100 rows, starting at first record)
  295. * $query->setLimit(50, 50); (retrieve 50 rows, starting at 50th record)
  296. *
  297. * @param integer $limit The limit for the result set
  298. * @param integer $offset The offset for the result set
  299. *
  300. * @return JDatabaseQuery Returns this object to allow chaining.
  301. *
  302. * @since 12.1
  303. */
  304. public function setLimit($limit = 0, $offset = 0)
  305. {
  306. $this->limit = (int) $limit;
  307. $this->offset = (int) $offset;
  308. return $this;
  309. }
  310. }