1: <?php
2: /**
3: * CakePHP(tm) : Rapid Development Framework (https://cakephp.org)
4: * Copyright (c) Cake Software Foundation, Inc. (https://cakefoundation.org)
5: *
6: * Licensed under The MIT License
7: * For full copyright and license information, please see the LICENSE.txt
8: * Redistributions of files must retain the above copyright notice.
9: *
10: * @copyright Copyright (c) Cake Software Foundation, Inc. (https://cakefoundation.org)
11: * @link https://cakephp.org CakePHP(tm) Project
12: * @since 3.0.0
13: * @license https://opensource.org/licenses/mit-license.php MIT License
14: */
15: namespace Cake\Database;
16:
17: use Cake\Database\Expression\Comparison;
18: use Cake\Database\Expression\IdentifierExpression;
19:
20: /**
21: * Sql dialect trait
22: */
23: trait SqlDialectTrait
24: {
25: /**
26: * Quotes a database identifier (a column name, table name, etc..) to
27: * be used safely in queries without the risk of using reserved words
28: *
29: * @param string $identifier The identifier to quote.
30: * @return string
31: */
32: public function quoteIdentifier($identifier)
33: {
34: $identifier = trim($identifier);
35:
36: if ($identifier === '*' || $identifier === '') {
37: return $identifier;
38: }
39:
40: // string
41: if (preg_match('/^[\w-]+$/u', $identifier)) {
42: return $this->_startQuote . $identifier . $this->_endQuote;
43: }
44:
45: // string.string
46: if (preg_match('/^[\w-]+\.[^ \*]*$/u', $identifier)) {
47: $items = explode('.', $identifier);
48:
49: return $this->_startQuote . implode($this->_endQuote . '.' . $this->_startQuote, $items) . $this->_endQuote;
50: }
51:
52: // string.*
53: if (preg_match('/^[\w-]+\.\*$/u', $identifier)) {
54: return $this->_startQuote . str_replace('.*', $this->_endQuote . '.*', $identifier);
55: }
56:
57: // Functions
58: if (preg_match('/^([\w-]+)\((.*)\)$/', $identifier, $matches)) {
59: return $matches[1] . '(' . $this->quoteIdentifier($matches[2]) . ')';
60: }
61:
62: // Alias.field AS thing
63: if (preg_match('/^([\w-]+(\.[\w\s-]+|\(.*\))*)\s+AS\s*([\w-]+)$/ui', $identifier, $matches)) {
64: return $this->quoteIdentifier($matches[1]) . ' AS ' . $this->quoteIdentifier($matches[3]);
65: }
66:
67: // string.string with spaces
68: if (preg_match('/^([\w-]+\.[\w][\w\s\-]*[\w])(.*)/u', $identifier, $matches)) {
69: $items = explode('.', $matches[1]);
70: $field = implode($this->_endQuote . '.' . $this->_startQuote, $items);
71:
72: return $this->_startQuote . $field . $this->_endQuote . $matches[2];
73: }
74:
75: if (preg_match('/^[\w_\s-]*[\w_-]+/u', $identifier)) {
76: return $this->_startQuote . $identifier . $this->_endQuote;
77: }
78:
79: return $identifier;
80: }
81:
82: /**
83: * Returns a callable function that will be used to transform a passed Query object.
84: * This function, in turn, will return an instance of a Query object that has been
85: * transformed to accommodate any specificities of the SQL dialect in use.
86: *
87: * @param string $type the type of query to be transformed
88: * (select, insert, update, delete)
89: * @return callable
90: */
91: public function queryTranslator($type)
92: {
93: return function ($query) use ($type) {
94: if ($this->isAutoQuotingEnabled()) {
95: $query = (new IdentifierQuoter($this))->quote($query);
96: }
97:
98: /** @var \Cake\ORM\Query $query */
99: $query = $this->{'_' . $type . 'QueryTranslator'}($query);
100: $translators = $this->_expressionTranslators();
101: if (!$translators) {
102: return $query;
103: }
104:
105: $query->traverseExpressions(function ($expression) use ($translators, $query) {
106: foreach ($translators as $class => $method) {
107: if ($expression instanceof $class) {
108: $this->{$method}($expression, $query);
109: }
110: }
111: });
112:
113: return $query;
114: };
115: }
116:
117: /**
118: * Returns an associative array of methods that will transform Expression
119: * objects to conform with the specific SQL dialect. Keys are class names
120: * and values a method in this class.
121: *
122: * @return array
123: */
124: protected function _expressionTranslators()
125: {
126: return [];
127: }
128:
129: /**
130: * Apply translation steps to select queries.
131: *
132: * @param \Cake\Database\Query $query The query to translate
133: * @return \Cake\Database\Query The modified query
134: */
135: protected function _selectQueryTranslator($query)
136: {
137: return $this->_transformDistinct($query);
138: }
139:
140: /**
141: * Returns the passed query after rewriting the DISTINCT clause, so that drivers
142: * that do not support the "ON" part can provide the actual way it should be done
143: *
144: * @param \Cake\Database\Query $query The query to be transformed
145: * @return \Cake\Database\Query
146: */
147: protected function _transformDistinct($query)
148: {
149: if (is_array($query->clause('distinct'))) {
150: $query->group($query->clause('distinct'), true);
151: $query->distinct(false);
152: }
153:
154: return $query;
155: }
156:
157: /**
158: * Apply translation steps to delete queries.
159: *
160: * Chops out aliases on delete query conditions as most database dialects do not
161: * support aliases in delete queries. This also removes aliases
162: * in table names as they frequently don't work either.
163: *
164: * We are intentionally not supporting deletes with joins as they have even poorer support.
165: *
166: * @param \Cake\Database\Query $query The query to translate
167: * @return \Cake\Database\Query The modified query
168: */
169: protected function _deleteQueryTranslator($query)
170: {
171: $hadAlias = false;
172: $tables = [];
173: foreach ($query->clause('from') as $alias => $table) {
174: if (is_string($alias)) {
175: $hadAlias = true;
176: }
177: $tables[] = $table;
178: }
179: if ($hadAlias) {
180: $query->from($tables, true);
181: }
182:
183: if (!$hadAlias) {
184: return $query;
185: }
186:
187: return $this->_removeAliasesFromConditions($query);
188: }
189:
190: /**
191: * Apply translation steps to update queries.
192: *
193: * Chops out aliases on update query conditions as not all database dialects do support
194: * aliases in update queries.
195: *
196: * Just like for delete queries, joins are currently not supported for update queries.
197: *
198: * @param \Cake\Database\Query $query The query to translate
199: * @return \Cake\Database\Query The modified query
200: */
201: protected function _updateQueryTranslator($query)
202: {
203: return $this->_removeAliasesFromConditions($query);
204: }
205:
206: /**
207: * Removes aliases from the `WHERE` clause of a query.
208: *
209: * @param \Cake\Database\Query $query The query to process.
210: * @return \Cake\Database\Query The modified query.
211: * @throws \RuntimeException In case the processed query contains any joins, as removing
212: * aliases from the conditions can break references to the joined tables.
213: */
214: protected function _removeAliasesFromConditions($query)
215: {
216: if ($query->clause('join')) {
217: throw new \RuntimeException(
218: 'Aliases are being removed from conditions for UPDATE/DELETE queries, ' .
219: 'this can break references to joined tables.'
220: );
221: }
222:
223: $conditions = $query->clause('where');
224: if ($conditions) {
225: $conditions->traverse(function ($expression) {
226: if ($expression instanceof Comparison) {
227: $field = $expression->getField();
228: if (is_string($field) &&
229: strpos($field, '.') !== false
230: ) {
231: list(, $unaliasedField) = explode('.', $field, 2);
232: $expression->setField($unaliasedField);
233: }
234:
235: return $expression;
236: }
237:
238: if ($expression instanceof IdentifierExpression) {
239: $identifier = $expression->getIdentifier();
240: if (strpos($identifier, '.') !== false) {
241: list(, $unaliasedIdentifier) = explode('.', $identifier, 2);
242: $expression->setIdentifier($unaliasedIdentifier);
243: }
244:
245: return $expression;
246: }
247:
248: return $expression;
249: });
250: }
251:
252: return $query;
253: }
254:
255: /**
256: * Apply translation steps to insert queries.
257: *
258: * @param \Cake\Database\Query $query The query to translate
259: * @return \Cake\Database\Query The modified query
260: */
261: protected function _insertQueryTranslator($query)
262: {
263: return $query;
264: }
265:
266: /**
267: * Returns a SQL snippet for creating a new transaction savepoint
268: *
269: * @param string $name save point name
270: * @return string
271: */
272: public function savePointSQL($name)
273: {
274: return 'SAVEPOINT LEVEL' . $name;
275: }
276:
277: /**
278: * Returns a SQL snippet for releasing a previously created save point
279: *
280: * @param string $name save point name
281: * @return string
282: */
283: public function releaseSavePointSQL($name)
284: {
285: return 'RELEASE SAVEPOINT LEVEL' . $name;
286: }
287:
288: /**
289: * Returns a SQL snippet for rollbacking a previously created save point
290: *
291: * @param string $name save point name
292: * @return string
293: */
294: public function rollbackSavePointSQL($name)
295: {
296: return 'ROLLBACK TO SAVEPOINT LEVEL' . $name;
297: }
298: }
299: