1: <?php
2:
3: namespace Alchemy\dialect;
4:
5: class ANSICompiler extends Compiler {
6: protected static $expr_formats = array(
7:
8: 'add' => '%s + %s',
9: 'sub' => '%s - %s',
10: 'mult' => '%s * %s',
11: 'div' => '%s / %s',
12: 'mod' => 'MOD(%s, %s)',
13: 'abs' => 'ABS(%s)',
14: 'ceil' => 'CEIL(%s)',
15: 'exp' => 'EXP(%s)',
16: 'floor' => 'FLOOR(%s)',
17: 'ln' => 'LN(%s)',
18: 'sqrt' => 'SQRT(%s)',
19: 'extract' => 'EXTRACT(%s FROM %s)',
20: 'interval' => 'INTERVAL %s %s',
21: 'now' => 'NOW()',
22: 'lower' => 'LOWER(%s)',
23: 'upper' => 'UPPER(%s)',
24: 'convert' => 'CONVERT(%s USING %s)',
25: 'translate' => 'TRANSLATE(%s USING %s)',
26: 'concat' => '%s || %s',
27: 'coalesce' => 'COALESCE(%1//, /)',
28:
29:
30: 'equal' => '%s = %s',
31: 'lt' => '%s < %s',
32: 'gt' => '%s > %s',
33: 'ne' => '%s != %s',
34: 'le' => '%s <= %s',
35: 'ge' => '%s >= %s',
36: 'between' => '%s BETWEEN %s AND %s',
37: 'isnull' => '%s IS NULL',
38: 'like' => '%s LIKE %s',
39: 'in' => '%s IN (%2//, /)',
40: 'and' => '(%// AND /)',
41: 'or' => '(%// OR /)',
42: 'not' => 'NOT (%s)');
43:
44: protected static $schema_formats = array(
45:
46: 'Bool' => "BOOL",
47: 'Integer' => "INT(%s)",
48: 'TinyInt' => "TINYINT(%s)",
49: 'SmallInt' => "SMALLINT(%s)",
50: 'MediumInt' => "MEDIUMINT(%s)",
51: 'BigInt' => "BIGINT(%s)",
52: 'Float' => "FLOAT(%s)",
53: 'Decimal' => "DECIMAL(%s, %s)",
54:
55:
56: 'Blob' => "BLOB",
57: 'Binary' => "BINARY(%s)",
58: 'String' => "VARCHAR(%s)",
59: 'Char' => "CHAR(%s)",
60: 'Text' => "TEXT(%s)",
61:
62:
63: 'Date' => "DATE",
64: 'Time' => "TIME",
65: 'Datetime' => "DATETIME",
66: 'Timestamp' => "TIMESTAMP",
67:
68:
69: 'Index' => "KEY %s (%3$//, /)",
70: 'UniqueKey' => "UNIQUE KEY %s (%3$//, /)",
71: 'PrimaryKey' => "PRIMARY KEY (%3$//, /)");
72:
73: private $counters = array();
74: private $aliases = array();
75:
76: protected $defaults = array(
77: 'alias_columns' => true,
78: 'alias_tables' => false);
79:
80:
81: public static function get_schema_format($type) {
82: if (array_key_exists($type, static::$schema_formats)) {
83: return static::$schema_formats[$type];
84: }
85:
86: $parent = get_parent_class(get_called_class());
87: if ($parent && method_exists($parent, 'get_schema_format')) {
88: return $parent::get_schema_format($type);
89: }
90: }
91:
92: 93: 94: 95: 96: 97:
98: public function alias($obj) {
99: $tag = $obj->getTag('sql.compile');
100: $key = "{$tag}.{$obj->getID()}";
101:
102: if (!array_key_exists($key, $this->aliases)) {
103: if (!array_key_exists($tag, $this->counters)) {
104: $this->counters[$tag] = 0;
105: }
106:
107: $id = $this->counters[$tag]++;
108: $fn = $this->getFunction($obj, 'sql.compile', 'Alias_');
109: $this->aliases[$key] = call_user_func($fn, $obj, $id);
110: }
111:
112: return $this->aliases[$key];
113: }
114:
115:
116: public function Alias_ColumnRef($obj, $id) {
117: return $obj->name();
118: }
119:
120:
121: public function Alias_Scalar($obj, $id) {
122: return "p{$id}";
123: }
124:
125:
126: public function Alias_TableRef($obj, $id) {
127: return strtolower(substr($obj->name(), 0, 2)) . ($id + 1);
128: }
129:
130:
131: public function Column($obj) {
132: return $obj->getName();
133: }
134:
135:
136: public function ColumnRef($obj) {
137: $column = $obj->name();
138:
139: if ($this->getConfig('alias_tables')) {
140: $column = "{$this->alias($obj->table())}.$column";
141: }
142:
143: return $column;
144: }
145:
146:
147: public function Create($obj) {
148: $table = $obj->getTable();
149:
150: $parts = array_merge(
151: array_values($table->listColumns()),
152: array_values($table->listIndexes()));
153:
154: $parts = implode(', ', $this->map('Create_Element', $parts));
155:
156: return "CREATE TABLE IF NOT EXISTS {$table->getName()} ({$parts})";
157: }
158:
159:
160: public function Create_Column($obj) {
161: $null = $obj->isNullable() ? "NULL" : "NOT NULL";
162:
163: if ($fn = $this->getFunction($obj, 'element.type', 'Create_')) {
164: $type = call_user_func($fn, $obj);
165: } else {
166: $format = static::get_schema_format($obj->getType());
167: $type = $this->format($format,
168: array($obj->getArg(0), $obj->getArg(1)));
169: }
170:
171: return "{$obj->getName()} {$type} {$null}";
172: }
173:
174:
175: public function Create_Index($obj) {
176: $format = static::get_schema_format($obj->getType());
177: $elements = array($obj->getName(), $obj->getTable()->getName(),
178: $this->compile($obj->listColumns()));
179:
180: return $this->format($format, $elements);
181: }
182:
183:
184: public function Create_ForeignKey($obj) {
185: $columns = $this->compile($obj->listColumns());
186: $columns = implode(', ', $columns);
187:
188: $sources = $this->compile($obj->listSources());
189: $sources = implode(', ', $sources);
190:
191: $table = $obj->getSourceTable()->getName();
192:
193: return "FOREIGN KEY ({$columns}) REFERENCES {$table} ({$sources})";
194: }
195:
196:
197: public function Create_Element($obj) {
198: $fn = $this->getFunction($obj, 'sql.create', 'Create_');
199: return call_user_func($fn, $obj);
200: }
201:
202:
203: public function Delete($obj) {
204: $alias = $this->getConfig('alias_tables') ? $this->alias($obj->table()) : '';
205:
206: $parts = array(
207: "DELETE", $alias,
208: "FROM {$this->compile($obj->table())}",
209: $this->Query_Join($obj),
210: $this->Query_Where($obj),
211: $this->Query_Limit($obj));
212:
213: return implode(' ', array_filter($parts));
214: }
215:
216:
217: public function Drop($obj) {
218: return "DROP TABLE IF EXISTS {$obj->getTable()->getName()}";
219: }
220:
221:
222: public function Insert($obj) {
223: $columns = implode(", ", array_keys($obj->columns()));
224:
225: $rows = $this->compile($obj->rows());
226: $data = $rows
227: ? $this->format("VALUES %/(%!!, !)/, /", $rows)
228: : $this->Select($obj);
229:
230: return "INSERT INTO {$obj->table()->name()} ({$columns}) {$data}";
231: }
232:
233:
234: public function Join($obj) {
235: $table = $this->compile($obj->getTable());
236: $on = $obj->getOn() ? " ON {$this->compile($obj->getOn())}" : "";
237: return "{$obj->getDirection()} {$obj->getType()} JOIN {$table}{$on}";
238: }
239:
240:
241: public function Expression($obj) {
242: $format = static::$expr_formats[$obj->getType()];
243: $elements = $this->compile($obj->elements());
244:
245: return $this->format($format, $elements);
246: }
247:
248:
249: public function Query($obj) {
250: $fn = $this->getFunction($obj, 'element.type');
251: return call_user_func($fn, $obj);
252: }
253:
254:
255: public function Scalar($obj) {
256: return ":{$this->alias($obj)}";
257: }
258:
259:
260: public function Select($obj) {
261: $columns = array();
262: foreach($obj->columns() as $name => $column) {
263: $alias = $this->getConfig('alias_columns')
264: ? " as {$name}" : "";
265: $columns[] = $this->compile($column) . $alias;
266: }
267:
268: $columns = implode(", ", $columns);
269:
270: $table = $this->compile($obj->table());
271:
272: $parts = array(
273: "SELECT {$columns}",
274: $table ? "FROM {$table}" : "",
275: $this->Query_Join($obj),
276: $this->Query_Where($obj),
277: $this->Query_Limit($obj));
278:
279: return implode(' ', array_filter($parts));
280: }
281:
282:
283: public function TableRef($obj) {
284: if ($this->getConfig('alias_tables')) {
285: return "{$obj->name()} {$this->alias($obj)}";
286: }
287:
288: return $obj->name();
289: }
290:
291:
292: public function Update($obj) {
293: $table = $this->compile($obj->table());
294:
295: $columns = array();
296: foreach($obj->columns() as $name => $column) {
297: if ($this->getConfig('alias_tables')) {
298: $name = "{$this->alias($obj->table())}.{$name}";
299: }
300: $columns[] = "{$name} = {$this->compile($column)}";
301: }
302:
303: $columns = implode(", ", $columns);
304:
305: $parts = array(
306: "UPDATE {$table} SET {$columns}",
307: $this->Query_Join($obj),
308: $this->Query_Where($obj),
309: $this->Query_Limit($obj));
310:
311: return implode(' ', array_filter($parts));
312: }
313:
314:
315: public function Query_Join($obj) {
316: return implode(' ', $this->compile($obj->joins()));
317: }
318:
319:
320: public function Query_Limit($obj) {
321: $offset = $this->compile($obj->offset());
322: $limit = $this->compile($obj->limit());
323:
324: if (!$limit && !$offset) {
325: return "";
326: } elseif (!$offset) {
327: return "LIMIT {$limit}";
328: }
329:
330: return "LIMIT {$offset}, {$limit}";
331: }
332:
333:
334: public function Query_Where($obj) {
335: $where = $this->compile($obj->where());
336: return $where ? "WHERE {$where}" : "";
337: }
338: }