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:
19: /**
20: * Sql dialect trait
21: */
22: trait SqlDialectTrait
23: {
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 ($condition) {
226: if (!($condition instanceof Comparison)) {
227: return $condition;
228: }
229:
230: $field = $condition->getField();
231: if ($field instanceof ExpressionInterface || strpos($field, '.') === false) {
232: return $condition;
233: }
234:
235: list(, $field) = explode('.', $field);
236: $condition->setField($field);
237:
238: return $condition;
239: });
240: }
241:
242: return $query;
243: }
244:
245: /**
246: * Apply translation steps to insert queries.
247: *
248: * @param \Cake\Database\Query $query The query to translate
249: * @return \Cake\Database\Query The modified query
250: */
251: protected function _insertQueryTranslator($query)
252: {
253: return $query;
254: }
255:
256: /**
257: * Returns a SQL snippet for creating a new transaction savepoint
258: *
259: * @param string $name save point name
260: * @return string
261: */
262: public function savePointSQL($name)
263: {
264: return 'SAVEPOINT LEVEL' . $name;
265: }
266:
267: /**
268: * Returns a SQL snippet for releasing a previously created save point
269: *
270: * @param string $name save point name
271: * @return string
272: */
273: public function releaseSavePointSQL($name)
274: {
275: return 'RELEASE SAVEPOINT LEVEL' . $name;
276: }
277:
278: /**
279: * Returns a SQL snippet for rollbacking a previously created save point
280: *
281: * @param string $name save point name
282: * @return string
283: */
284: public function rollbackSavePointSQL($name)
285: {
286: return 'ROLLBACK TO SAVEPOINT LEVEL' . $name;
287: }
288: }
289: