1: <?php
2:
3: namespace PHPixie\Database\Type\SQL;
4:
5: abstract class Parser extends \PHPixie\Database\Parser
6: {
7: protected $fragmentParser;
8: protected $conditionsParser;
9: protected $supportedJoins;
10:
11: public function __construct($database, $driver, $config, $fragmentParser, $conditionsParser)
12: {
13: parent::__construct($database, $driver, $config);
14: $this->fragmentParser = $fragmentParser;
15: $this->conditionsParser = $conditionsParser;
16: }
17:
18: public function parse($query)
19: {
20: $expr = $this->database->sqlExpression();
21: $type = $query->type();
22:
23: switch ($type) {
24: case 'select':
25: $this->selectQuery($query, $expr);
26: break;
27: case 'insert':
28: $this->insertQuery($query, $expr);
29: break;
30: case 'update':
31: $this->updateQuery($query, $expr);
32: break;
33: case 'delete':
34: $this->deleteQuery($query, $expr);
35: break;
36: case 'count':
37: $this->countQuery($query, $expr);
38: break;
39: default:
40: throw new \PHPixie\Database\Exception\Parser("Query type $type is not supported");
41: }
42:
43: return $expr;
44: }
45:
46: protected function selectQuery($query, $expr)
47: {
48: $expr->sql = "SELECT ";
49: $this->appendFields($query, $expr);
50:
51: if ($query->getTable() !== null) {
52: $expr->sql.= " FROM ";
53: $this->appendTable($query, $expr);
54: }
55:
56: $this->appendJoins($query, $expr);
57: $this->appendConditions('where', $query->getWhereConditions(), $expr);
58: $this->appendGroupBy($query, $expr);
59: $this->appendConditions('having', $query->getHavingConditions(), $expr);
60: $this->appendOrderBy($query, $expr);
61: $this->appendLimitOffset($query, $expr);
62: $this->appendUnion($query, $expr);
63: }
64:
65: protected function insertQuery($query, $expr)
66: {
67: $expr->sql = "INSERT INTO ";
68:
69: $this->appendTable($query, $expr, true);
70: $this->appendInsertValues($query, $expr);
71: }
72:
73: protected function updateQuery($query, $expr)
74: {
75: $expr->sql = "UPDATE ";
76:
77: $this->appendTable($query, $expr, true);
78: $this->appendJoins($query, $expr);
79: $this->appendUpdateValues($query, $expr);
80: $this->appendConditions('where', $query->getWhereConditions(), $expr);
81: $this->appendOrderBy($query, $expr);
82: $this->appendLimitOffset($query, $expr);
83:
84: return $expr;
85: }
86:
87: protected function deleteQuery($query, $expr)
88: {
89: $expr->sql = "DELETE FROM ";
90:
91: $this->appendTable($query, $expr, true);
92: $this->appendJoins($query, $expr);
93: $this->appendConditions('where', $query->getWhereConditions(), $expr);
94: $this->appendOrderBy($query, $expr);
95: $this->appendLimitOffset($query, $expr);
96:
97: return $expr;
98: }
99:
100: protected function countQuery($query, $expr)
101: {
102: $expr->sql .= "SELECT COUNT(1) AS ";
103: $this->fragmentParser->appendColumn('count', $expr);
104: $expr->sql .= " FROM ";
105: $this->appendTable($query, $expr, true);
106: $this->appendJoins($query, $expr);
107: $this->appendConditions('where', $query->getWhereConditions(), $expr);
108: }
109:
110: protected function appendTable($query, $expr, $required = false)
111: {
112: $table = $query->getTable();
113:
114: if ($required && $table === null) {
115: $type = strtoupper($query->type());
116: throw new \PHPixie\Database\Exception\Parser("Table not specified for $type query");
117: }
118:
119: $this->fragmentParser->appendTable($table['table'], $expr, $table['alias']);
120: }
121:
122: protected function appendInsertValues($query, $expr)
123: {
124: if (($insertData = $query->getBatchData()) !== null) {
125: $columns = $insertData['columns'];
126: $rows = $insertData['rows'];
127:
128: }elseif(($data = $query->getData()) !== null) {
129: $columns = array_keys($data);
130: $rows = array(array_values($data));
131:
132: }else{
133: $columns = array();
134: $rows = array();
135: }
136:
137: if (empty($columns) && empty($rows) ) {
138: return $this->appendEmptyInsertValues($expr);
139: }
140:
141: if (!empty($columns)) {
142: $expr->sql .= "(";
143:
144: foreach ($columns as $key => $column) {
145: if($key > 0)
146: $expr->sql.= ', ';
147: $this->fragmentParser->appendColumn($column, $expr);
148: }
149:
150: $expr->sql .= ")";
151:
152: }
153:
154: $expr->sql .= " ";
155:
156: if (!$this->appendSubquery($expr, $rows)) {
157: $expr->sql .="VALUES ";
158: $columnsCount = count($columns);
159:
160: foreach ($rows as $rowKey => $row) {
161:
162: if (count($row) != $columnsCount)
163: throw new \PHPixie\Database\Exception\Parser("The number of keys does not match the number of values for bulk insert.");
164:
165: if($rowKey > 0)
166: $expr->sql.= ', ';
167:
168: $expr->sql.= '(';
169: foreach ($row as $valueKey => $value) {
170: if($valueKey > 0)
171: $expr->sql.= ', ';
172:
173: $this->fragmentParser->appendValue($value, $expr);
174: }
175: $expr->sql.= ')';
176: }
177: }
178:
179: }
180:
181: protected function appendEmptyInsertValues($expr)
182: {
183: $expr->sql.= "() VALUES ()";
184: }
185:
186: protected function appendUpdateValues($query, $expr)
187: {
188: $expr->sql .= " SET ";
189: $set = $query->getSet();
190: $increment = $query->getIncrement();
191:
192: if(empty($set) && empty($increment))
193: throw new \PHPixie\Database\Exception\Parser("Empty data passed to the UPDATE query");
194:
195: $first = true;
196: foreach ($set as $column => $value) {
197: if (!$first) {
198: $expr->sql.= ', ';
199: } else {
200: $first = false;
201: }
202: $this->fragmentParser->appendColumn($column, $expr);
203: $expr->sql.= " = ";
204: $this->fragmentParser->appendValue($value, $expr);
205: }
206:
207: foreach ($increment as $column => $amount) {
208: if (!$first) {
209: $expr->sql.= ', ';
210: } else {
211: $first = false;
212: }
213: $this->fragmentParser->appendColumn($column, $expr);
214: $expr->sql.= " = ";
215: $this->fragmentParser->appendColumn($column, $expr);
216: if ($amount >= 0) {
217: $expr->sql.=' + ';
218: } else {
219: $expr->sql.=' - ';
220: $amount = 0 - $amount;
221: }
222:
223: $this->fragmentParser->appendValue($amount, $expr);
224: }
225: }
226:
227: protected function appendJoins($query, $expr)
228: {
229: foreach ($query->getJoins() as $join) {
230:
231: if (!isset($this->supportedJoins[$join['type']]))
232: throw new \PHPixie\Database\Exception\Parser("Join type '{$join['type']}' is not supported by this database driver");
233:
234: $expr->sql.= ' '.$this->supportedJoins[$join['type']]." JOIN ";
235: $this->fragmentParser->appendTable($join['table'], $expr, $join['alias']);
236: $this->appendConditions('on', $join['container']->getConditions(), $expr);
237: }
238: }
239:
240: protected function appendConditions($prefix, $conditions, $expr)
241: {
242: if(empty($conditions))
243:
244: return;
245:
246: $expr->sql.= ' '.strtoupper($prefix).' ';
247: $expr->append($this->conditionsParser->parse($conditions));
248: }
249:
250: protected function appendGroupBy($query, $expr)
251: {
252: $groupBy = $query->getGroupBy();
253:
254: if (empty($groupBy))
255: return;
256:
257: $expr->sql.= " GROUP BY ";
258: foreach ($groupBy as $key => $column) {
259: if ($key > 0)
260: $expr->sql.= ', ';
261:
262: $this->fragmentParser->appendColumn($column, $expr);
263: }
264: }
265:
266: protected function appendOrderBy($query, $expr)
267: {
268: $order = $query->getOrderBy();
269:
270: if (empty($order))
271: return;
272:
273: $expr->sql.= " ORDER BY ";
274: foreach ($order as $key => $orderBy) {
275: $field = $orderBy->field();
276: $dir = $orderBy->direction();
277:
278: if ($key > 0)
279: $expr->sql.= ', ';
280:
281: $this->fragmentParser->appendColumn($field, $expr);
282: $expr->sql.= ' '.strtoupper($dir);
283: }
284: }
285:
286: protected function appendLimitOffset($query, $expr)
287: {
288: $limit = $query->getLimit();
289: $offset = $query->getOffset();
290:
291: $this->appendLimitOffsetValues($expr, $limit, $offset);
292: }
293:
294: protected function appendLimitOffsetValues($expr, $limit, $offset)
295: {
296: if ($limit !== null) {
297: $expr->sql.= " LIMIT $limit";
298: }
299:
300: if ($offset !== null) {
301: $expr->sql.=" OFFSET $offset";
302: }
303: }
304:
305: protected function appendUnion($query, $expr)
306: {
307: foreach ($query->getUnions() as $union) {
308: $query = $union['query'];
309: $all = $union['all'];
310: $expr->sql.= " UNION ";
311: if ($all)
312: $expr->sql.= "ALL ";
313:
314: if (!$this->appendSubquery($expr, $query)) {
315: throw new \PHPixie\Database\Exception\Parser("Union parameter must be either a SELECT Query object or SQL expression object");
316: }
317: }
318: }
319:
320: protected function appendSubquery($expr, $query)
321: {
322: if ($query instanceof \PHPixie\Database\Type\SQL\Query && $query->type() === 'select') {
323: $expr->append($query->parse());
324:
325: } elseif ($query instanceof \PHPixie\Database\Type\SQL\Expression) {
326: $expr->append($query);
327:
328: }else{
329: return false;
330: }
331:
332: return true;
333: }
334:
335: protected function appendFields($query, $expr)
336: {
337: $fields = $query->getFields();
338:
339: if (empty($fields)) {
340: $expr->sql.= '*';
341:
342: return;
343: }
344:
345: $first = true;
346: foreach ($query->getFields() as $key => $field) {
347: if (!$first) {
348: $expr->sql.= ', ';
349: }else
350: $first = false;
351:
352: if (!is_numeric($key)) {
353: $this->fragmentParser->appendColumn($field, $expr);
354: $expr->sql.=" AS ".$this->fragmentParser->quote($key);
355: } else {
356: $this->fragmentParser->appendColumn($field, $expr);
357: }
358: }
359: }
360:
361: }
362: