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