1: <?php
2:
3: 4: 5: 6:
7:
8: namespace Nette\Database;
9:
10: use Nette;
11:
12:
13: 14: 15: 16: 17:
18: class SqlPreprocessor extends Nette\Object
19: {
20:
21: private $connection;
22:
23:
24: private $driver;
25:
26:
27: private $params;
28:
29:
30: private $remaining;
31:
32:
33: private $counter;
34:
35:
36: private $arrayMode;
37:
38:
39: public function __construct(Connection $connection)
40: {
41: $this->connection = $connection;
42: $this->driver = $connection->getSupplementalDriver();
43: }
44:
45:
46: 47: 48: 49:
50: public function process($params)
51: {
52: $this->params = $params;
53: $this->counter = 0; $prev = -1;
54: $this->remaining = array();
55: $this->arrayMode = NULL;
56: $res = array();
57:
58: while ($this->counter < count($params)) {
59: $param = $params[$this->counter++];
60:
61: if (($this->counter === 2 && count($params) === 2) || !is_scalar($param)) {
62: $res[] = $this->formatValue($param, 'auto');
63: $this->arrayMode = NULL;
64:
65: } elseif (is_string($param) && $this->counter > $prev + 1) {
66: $prev = $this->counter;
67: $this->arrayMode = NULL;
68: $res[] = Nette\Utils\Strings::replace(
69: $param,
70: '~\'[^\']*+\'|"[^"]*+"|\?[a-z]*|^\s*+(?:INSERT|REPLACE)\b|\b(?:SET|WHERE|HAVING|ORDER BY|GROUP BY|KEY UPDATE)(?=[\s?]*+\z)|/\*.*?\*/|--[^\n]*~si',
71: array($this, 'callback')
72: );
73: } else {
74: throw new Nette\InvalidArgumentException('There are more parameters than placeholders.');
75: }
76: }
77:
78: return array(implode(' ', $res), $this->remaining);
79: }
80:
81:
82:
83: public function callback($m)
84: {
85: $m = $m[0];
86: if ($m[0] === '?') {
87: if ($this->counter >= count($this->params)) {
88: throw new Nette\InvalidArgumentException('There are more placeholders than passed parameters.');
89: }
90: return $this->formatValue($this->params[$this->counter++], substr($m, 1) ?: 'auto');
91:
92: } elseif ($m[0] === "'" || $m[0] === '"' || $m[0] === '/' || $m[0] === '-') {
93: return $m;
94:
95: } else {
96: static $modes = array(
97: 'INSERT' => 'values',
98: 'REPLACE' => 'values',
99: 'KEY UPDATE' => 'set',
100: 'SET' => 'set',
101: 'WHERE' => 'and',
102: 'HAVING' => 'and',
103: 'ORDER BY' => 'order',
104: 'GROUP BY' => 'order',
105: );
106: $this->arrayMode = $modes[ltrim(strtoupper($m))];
107: return $m;
108: }
109: }
110:
111:
112: private function formatValue($value, $mode = NULL)
113: {
114: if (!$mode || $mode === 'auto') {
115: if (is_string($value)) {
116: if (strlen($value) > 20) {
117: $this->remaining[] = $value;
118: return '?';
119:
120: } else {
121: return $this->connection->quote($value);
122: }
123:
124: } elseif (is_int($value)) {
125: return (string) $value;
126:
127: } elseif (is_float($value)) {
128: return rtrim(rtrim(number_format($value, 10, '.', ''), '0'), '.');
129:
130: } elseif (is_bool($value)) {
131: return $this->driver->formatBool($value);
132:
133: } elseif ($value === NULL) {
134: return 'NULL';
135:
136: } elseif ($value instanceof Table\IRow) {
137: return $value->getPrimary();
138:
139: } elseif ($value instanceof SqlLiteral) {
140: $prep = clone $this;
141: list($res, $params) = $prep->process(array_merge(array($value->__toString()), $value->getParameters()));
142: $this->remaining = array_merge($this->remaining, $params);
143: return $res;
144:
145: } elseif ($value instanceof \DateTime || $value instanceof \DateTimeInterface) {
146: return $this->driver->formatDateTime($value);
147:
148: } elseif ($value instanceof \DateInterval) {
149: return $this->driver->formatDateInterval($value);
150:
151: } elseif (is_object($value) && method_exists($value, '__toString')) {
152: return $this->formatValue((string) $value);
153:
154: } elseif (is_resource($value)) {
155: $this->remaining[] = $value;
156: return '?';
157: }
158:
159: } elseif ($mode === 'name') {
160: if (!is_string($value)) {
161: $type = gettype($value);
162: throw new Nette\InvalidArgumentException("Placeholder ?$mode expects string, $type given.");
163: }
164: return $this->delimite($value);
165: }
166:
167: if ($value instanceof \Traversable && !$value instanceof Table\IRow) {
168: $value = iterator_to_array($value);
169: }
170:
171: if (is_array($value)) {
172: $vx = $kx = array();
173: if ($mode === 'auto') {
174: $mode = $this->arrayMode;
175: }
176:
177: if ($mode === 'values') {
178: if (array_key_exists(0, $value)) {
179: foreach ($value[0] as $k => $v) {
180: $kx[] = $this->delimite($k);
181: }
182: foreach ($value as $val) {
183: $vx2 = array();
184: foreach ($val as $v) {
185: $vx2[] = $this->formatValue($v);
186: }
187: $vx[] = implode(', ', $vx2);
188: }
189: $select = $this->driver->isSupported(ISupplementalDriver::SUPPORT_MULTI_INSERT_AS_SELECT);
190: return '(' . implode(', ', $kx) . ($select ? ') SELECT ' : ') VALUES (')
191: . implode($select ? ' UNION ALL SELECT ' : '), (', $vx) . ($select ? '' : ')');
192: }
193:
194: foreach ($value as $k => $v) {
195: $kx[] = $this->delimite($k);
196: $vx[] = $this->formatValue($v);
197: }
198: return '(' . implode(', ', $kx) . ') VALUES (' . implode(', ', $vx) . ')';
199:
200: } elseif (!$mode || $mode === 'set') {
201: foreach ($value as $k => $v) {
202: if (is_int($k)) {
203: $vx[] = is_array($v) ? '(' . $this->formatValue($v) . ')' : $this->formatValue($v);
204: } elseif (substr($k, -1) === '=') {
205: $k2 = $this->delimite(substr($k, 0, -2));
206: $vx[] = $k2 . '=' . $k2 . ' ' . substr($k, -2, 1) . ' ' . $this->formatValue($v);
207: } else {
208: $vx[] = $this->delimite($k) . '=' . $this->formatValue($v);
209: }
210: }
211: return implode(', ', $vx);
212:
213: } elseif ($mode === 'and' || $mode === 'or') {
214: foreach ($value as $k => $v) {
215: if (is_int($k)) {
216: $vx[] = $this->formatValue($v);
217: continue;
218: }
219: list($k, $operator) = explode(' ', $k . ' ');
220: $k = $this->delimite($k);
221: if (is_array($v)) {
222: if ($v) {
223: $vx[] = $k . ' ' . ($operator ? $operator . ' ' : '') . 'IN (' . $this->formatValue(array_values($v)) . ')';
224: } elseif ($operator === 'NOT') {
225: } else {
226: $vx[] = '1=0';
227: }
228: } else {
229: $v = $this->formatValue($v);
230: $vx[] = $k . ' ' . ($operator ?: ($v === 'NULL' ? 'IS' : '=')) . ' ' . $v;
231: }
232: }
233: return $value ? '(' . implode(') ' . strtoupper($mode) . ' (', $vx) . ')' : '1=1';
234:
235: } elseif ($mode === 'order') {
236: foreach ($value as $k => $v) {
237: $vx[] = $this->delimite($k) . ($v > 0 ? '' : ' DESC');
238: }
239: return implode(', ', $vx);
240:
241: } else {
242: throw new Nette\InvalidArgumentException("Unknown placeholder ?$mode.");
243: }
244:
245: } elseif (in_array($mode, array('and', 'or', 'set', 'values', 'order'), TRUE)) {
246: $type = gettype($value);
247: throw new Nette\InvalidArgumentException("Placeholder ?$mode expects array or Traversable object, $type given.");
248:
249: } elseif ($mode && $mode !== 'auto') {
250: throw new Nette\InvalidArgumentException("Unknown placeholder ?$mode.");
251:
252: } else {
253: throw new Nette\InvalidArgumentException('Unexpected type of parameter: ' . (is_object($value) ? get_class($value) : gettype($value)));
254: }
255: }
256:
257:
258: private function delimite($name)
259: {
260: return implode('.', array_map(array($this->driver, 'delimite'), explode('.', $name)));
261: }
262:
263: }
264: