1 /**
2  * HibernateD - Object-Relation Mapping for D programming language, with interface similar to Hibernate. 
3  * 
4  * Hibernate documentation can be found here:
5  * $(LINK http://hibernate.org/docs)$(BR)
6  * 
7  * Source file hibernated/core.d.
8  *
9  * This module contains HQL query parser and HQL to SQL transform implementation.
10  * 
11  * Copyright: Copyright 2013
12  * License:   $(LINK www.boost.org/LICENSE_1_0.txt, Boost License 1.0).
13  * Author:   Vadim Lopatin
14  */
15 module hibernated.query;
16 
17 import std.ascii;
18 import std.algorithm;
19 import std.exception;
20 import std.array;
21 import std.string;
22 import std.conv;
23 //import std.stdio : writeln;
24 import std.variant;
25 
26 import ddbc.core;
27 
28 import hibernated.annotations;
29 import hibernated.metadata;
30 import hibernated.type;
31 import hibernated.core;
32 import hibernated.dialect;
33 import hibernated.dialects.mysqldialect;
34 
35 
36 // For backwards compatibily
37 // 'enforceEx' will be removed with 2.089
38 static if(__VERSION__ < 2080) {
39     alias enforceHelper = enforceEx;
40 } else {
41     alias enforceHelper = enforce;
42 }
43 
44 // For backwards compatibily (since D 2.101, logger is no longer in std.experimental)
45 static if (__traits(compiles, (){ import std.logger; } )) {
46     import std.logger : trace;
47 } else {
48     import std.experimental.logger : trace;
49 }
50 
51 enum JoinType {
52     InnerJoin,
53     LeftJoin,
54 }
55 
56 class FromClauseItem {
57 	string entityName;
58     const EntityInfo entity;
59 	string entityAlias;
60 	string sqlAlias;
61     int startColumn;
62     int selectedColumns;
63     // for JOINs
64     JoinType joinType = JoinType.InnerJoin;
65     bool fetch;
66     FromClauseItem base;
67     const PropertyInfo baseProperty;
68     string pathString;
69     int index;
70     int selectIndex;
71 
72     string getFullPath() {
73         if (base is null)
74             return entityAlias;
75         return base.getFullPath() ~ "." ~ baseProperty.propertyName;
76     }
77 
78     this(const EntityInfo entity, string entityAlias, JoinType joinType, bool fetch, FromClauseItem base = null, const PropertyInfo baseProperty = null) {
79         this.entityName = entity.name;
80         this.entity = entity;
81         this.joinType = joinType;
82         this.fetch = fetch;
83         this.base = base;
84         this.baseProperty = baseProperty;
85         this.selectIndex = -1;
86     }
87 
88 }
89 
90 class FromClause {
91     FromClauseItem[] items;
92     FromClauseItem add(const EntityInfo entity, string entityAlias, JoinType joinType, bool fetch, FromClauseItem base = null, const PropertyInfo baseProperty = null) {
93         FromClauseItem item = new FromClauseItem(entity, entityAlias, joinType, fetch, base, baseProperty);
94         item.entityAlias = entityAlias is null ? "_a" ~ to!string(items.length + 1) : entityAlias;
95         item.sqlAlias = "_t" ~ to!string(items.length + 1);
96         item.index = cast(int)items.length;
97         item.pathString = item.getFullPath();
98         items ~= item;
99         return item;
100     }
101     @property size_t length() { return items.length; }
102     string getSQL() {
103         return "";
104     }
105     @property FromClauseItem first() {
106         return items[0];
107     }
108     FromClauseItem opIndex(int index) {
109         enforceHelper!HibernatedException(index >= 0 && index < items.length, "FromClause index out of range: " ~ to!string(index));
110         return items[index];
111     }
112     FromClauseItem opIndex(string aliasName) {
113         return findByAlias(aliasName);
114     }
115     bool hasAlias(string aliasName) {
116         foreach(ref m; items) {
117             if (m.entityAlias == aliasName)
118                 return true;
119         }
120         return false;
121     }
122     FromClauseItem findByAlias(string aliasName) {
123         foreach(ref m; items) {
124             if (m.entityAlias == aliasName)
125                 return m;
126         }
127         throw new QuerySyntaxException("Cannot find FROM alias by name " ~ aliasName);
128     }
129     FromClauseItem findByPath(string path) {
130         foreach(ref m; items) {
131             if (m.pathString == path)
132                 return m;
133         }
134         return null;
135     }
136 }
137 
138 struct OrderByClauseItem {
139 	FromClauseItem from;
140 	PropertyInfo prop;
141 	bool asc;
142 }
143 
144 struct SelectClauseItem {
145 	FromClauseItem from;
146 	PropertyInfo prop;
147 }
148 
149 class QueryParser {
150 	string query;
151 	EntityMetaData metadata;
152 	Token[] tokens;
153     FromClause fromClause;
154 	//FromClauseItem[] fromClause;
155 	string[] parameterNames;
156 	OrderByClauseItem[] orderByClause;
157 	SelectClauseItem[] selectClause;
158 	Token whereClause; // AST for WHERE expression
159 	
160 	
161 	this(EntityMetaData metadata, string query) {
162 		this.metadata = metadata;
163 		this.query = query;
164         fromClause = new FromClause();
165 		//trace("tokenizing query: " ~ query);
166 		tokens = tokenize(query);
167         //trace("parsing query: " ~ query);
168         parse();
169         //trace("parsing done");
170     }
171 	
172 	void parse() {
173 		processParameterNames(0, cast(int)tokens.length); // replace pairs {: Ident} with single Parameter token
174 		int len = cast(int)tokens.length;
175 		//trace("Query tokens: " ~ to!string(len));
176 		int fromPos = findKeyword(KeywordType.FROM);
177 		int selectPos = findKeyword(KeywordType.SELECT);
178 		int wherePos = findKeyword(KeywordType.WHERE);
179 		int orderPos = findKeyword(KeywordType.ORDER);
180 		enforceHelper!QuerySyntaxException(fromPos >= 0, "No FROM clause in query " ~ query);
181 		enforceHelper!QuerySyntaxException(selectPos <= 0, "SELECT clause should be first - invalid query " ~ query);
182 		enforceHelper!QuerySyntaxException(wherePos == -1 || wherePos > fromPos, "Invalid WHERE position in query " ~ query);
183 		enforceHelper!QuerySyntaxException(orderPos == -1 || (orderPos < tokens.length - 2 && tokens[orderPos + 1].keyword == KeywordType.BY), "Invalid ORDER BY in query " ~ query);
184 		enforceHelper!QuerySyntaxException(orderPos == -1 || orderPos > fromPos, "Invalid ORDER BY position in query " ~ query);
185 		int fromEnd = len;
186 		if (orderPos >= 0)
187 			fromEnd = orderPos;
188 		if (wherePos >= 0)
189 			fromEnd = wherePos;
190 		int whereEnd = wherePos < 0 ? -1 : (orderPos >= 0 ? orderPos : len);
191 		int orderEnd = orderPos < 0 ? -1 : len;
192 		parseFromClause(fromPos + 1, fromEnd);
193 		if (selectPos == 0 && selectPos < fromPos - 1)
194 			parseSelectClause(selectPos + 1, fromPos);
195 		else
196 			defaultSelectClause();
197 		bool selectedEntities = validateSelectClause();
198 		if (wherePos >= 0 && whereEnd > wherePos)
199 			parseWhereClause(wherePos + 1, whereEnd);
200 		if (orderPos >= 0 && orderEnd > orderPos)
201 			parseOrderClause(orderPos + 2, orderEnd);
202         if (selectedEntities) {
203             processAutoFetchReferences();
204             prepareSelectFields();
205         }
206 	}
207 	
208     private void prepareSelectFields() {
209         int startColumn = 1;
210         for (int i=0; i < fromClause.length; i++) {
211             FromClauseItem item = fromClause[i];
212             if (!item.fetch)
213                 continue;
214             int count = item.entity.metadata.getFieldCount(item.entity, false);
215             if (count > 0) {
216                 item.startColumn = startColumn;
217                 item.selectedColumns = count;
218                 startColumn += count;
219             }
220         }
221     }
222 
223     private void processAutoFetchReferences() {
224         FromClauseItem a = selectClause[0].from;
225         a.fetch = true;
226         processAutoFetchReferences(a);
227     }
228 
229     private FromClauseItem ensureItemFetched(FromClauseItem a, const PropertyInfo p) {
230         FromClauseItem res;
231         string path = a.pathString ~ "." ~ p.propertyName;
232         //trace("ensureItemFetched " ~ path);
233         res = fromClause.findByPath(path);
234         if (res is null) {
235             // autoadd join
236             assert(p.referencedEntity !is null);
237             res = fromClause.add(p.referencedEntity, null, p.nullable ? JoinType.LeftJoin : JoinType.InnerJoin, true, a, p);
238         } else {
239             // force fetch
240             res.fetch = true;
241         }
242         bool selectFound = false;
243         foreach(s; selectClause) {
244             if (s.from == res) {
245                 selectFound = true;
246                 break;
247             }
248         }
249         if (!selectFound) {
250             SelectClauseItem item;
251             item.from = res;
252             item.prop = null;
253             selectClause ~= item;
254         }
255         return res;
256     }
257 
258     private bool isBackReferenceProperty(FromClauseItem a, const PropertyInfo p) {
259         if (a.base is null)
260             return false;
261         auto baseEntity = a.base.entity;
262         assert(baseEntity !is null);
263         if (p.referencedEntity != baseEntity)
264             return false;
265 
266         if (p.referencedProperty !is null && p.referencedProperty == a.baseProperty)
267             return true;
268         if (a.baseProperty.referencedProperty !is null && p == a.baseProperty.referencedProperty)
269             return true;
270         return false;
271     }
272 
273     private void processAutoFetchReferences(FromClauseItem a) {
274         foreach (p; a.entity.properties) {
275             if (p.lazyLoad)
276                 continue;
277             if (p.oneToOne && !isBackReferenceProperty(a, p)) {
278                 FromClauseItem res = ensureItemFetched(a, p);
279                 processAutoFetchReferences(res);
280             }
281         }
282     }
283     
284     private void updateEntity(const EntityInfo entity, string name) {
285 		foreach(t; tokens) {
286 			if (t.type == TokenType.Ident && t.text == name) {
287                 t.entity = cast(EntityInfo)entity;
288 				t.type = TokenType.Entity;
289 			}
290 		}
291 	}
292 	
293 	private void updateAlias(const EntityInfo entity, string name) {
294 		foreach(t; tokens) {
295 			if (t.type == TokenType.Ident && t.text == name) {
296                 t.entity = cast(EntityInfo)entity;
297 				t.type = TokenType.Alias;
298 			}
299 		}
300 	}
301 	
302 	private void splitCommaDelimitedList(int start, int end, void delegate(int, int) callback) {
303 		//trace("SPLIT " ~ to!string(start) ~ " .. " ~ to!string(end));
304 		int len = cast(int)tokens.length;
305 		int p = start;
306 		for (int i = start; i < end; i++) {
307 			if (tokens[i].type == TokenType.Comma || i == end - 1) {
308 				enforceHelper!QuerySyntaxException(tokens[i].type != TokenType.Comma || i != end - 1, "Invalid comma at end of list" ~ errorContext(tokens[start]));
309 				int endp = i < end - 1 ? i : end;
310 				enforceHelper!QuerySyntaxException(endp > p, "Invalid comma delimited list" ~ errorContext(tokens[start]));
311 				callback(p, endp);
312 				p = i + 1;
313 			}
314 		}
315 	}
316 
317     private int parseFieldRef(int start, int end, ref string[] path) {
318         int pos = start;
319         while (pos < end) {
320             if (tokens[pos].type == TokenType.Ident || tokens[pos].type == TokenType.Alias) {
321                 enforceHelper!QuerySyntaxException(path.length == 0 || tokens[pos].type != TokenType.Alias, "Alias is allowed only as first item" ~ errorContext(tokens[pos]));
322                 path ~= tokens[pos].text;
323                 pos++;
324                 if (pos == end || tokens[pos].type != TokenType.Dot)
325                     return pos;
326                 if (pos == end - 1 || tokens[pos + 1].type != TokenType.Ident)
327                     return pos;
328                 pos++;
329             } else {
330                 break;
331             }
332         }
333         enforceHelper!QuerySyntaxException(tokens[pos].type != TokenType.Dot, "Unexpected dot at end in field list" ~ errorContext(tokens[pos]));
334         enforceHelper!QuerySyntaxException(path.length > 0, "Empty field list" ~ errorContext(tokens[pos]));
335         return pos;
336     }
337 	
338     private void parseFirstFromClause(int start, int end, out int pos) {
339         enforceHelper!QuerySyntaxException(start < end, "Invalid FROM clause " ~ errorContext(tokens[start]));
340         // minimal support:
341         //    Entity
342         //    Entity alias
343         //    Entity AS alias
344         enforceHelper!QuerySyntaxException(tokens[start].type == TokenType.Ident, "Entity name identifier expected in FROM clause" ~ errorContext(tokens[start]));
345         string entityName = cast(string)tokens[start].text;
346         auto ei = metadata.findEntity(entityName);
347         updateEntity(ei, entityName);
348         string aliasName = null;
349         int p = start + 1;
350         if (p < end && tokens[p].type == TokenType.Keyword && tokens[p].keyword == KeywordType.AS)
351             p++;
352         if (p < end) {
353             enforceHelper!QuerySyntaxException(tokens[p].type == TokenType.Ident, "Alias name identifier expected in FROM clause" ~ errorContext(tokens[p]));
354             aliasName = cast(string)tokens[p].text;
355             p++;
356         }
357         if (aliasName != null)
358             updateAlias(ei, aliasName);
359         fromClause.add(ei, aliasName, JoinType.InnerJoin, false);
360         pos = p;
361     }
362 
363     void appendFromClause(Token context, string[] path, string aliasName, JoinType joinType, bool fetch) {
364         int p = 0;
365         enforceHelper!QuerySyntaxException(fromClause.hasAlias(path[p]), "Unknown alias " ~ path[p] ~ " in FROM clause" ~ errorContext(context));
366         FromClauseItem baseClause = findFromClauseByAlias(path[p]);
367         //string pathString = path[p];
368         p++;
369         while(true) {
370             auto baseEntity = baseClause.entity;
371             enforceHelper!QuerySyntaxException(p < path.length, "Property name expected in FROM clause" ~ errorContext(context));
372             string propertyName = path[p++];
373             auto property = baseEntity[propertyName];
374             auto referencedEntity = property.referencedEntity;
375             assert(referencedEntity !is null);
376             enforceHelper!QuerySyntaxException(!property.simple, "Simple property " ~ propertyName ~ " cannot be used in JOIN" ~ errorContext(context));
377             enforceHelper!QuerySyntaxException(!property.embedded, "Embedded property " ~ propertyName ~ " cannot be used in JOIN" ~ errorContext(context));
378             bool last = (p == path.length);
379             FromClauseItem item = fromClause.add(referencedEntity, last ? aliasName : null, joinType, fetch, baseClause, property);
380             if (last && aliasName !is null)
381                 updateAlias(referencedEntity, item.entityAlias);
382             baseClause = item;
383             if (last)
384                 break;
385         }
386     }
387 
388     void parseFromClause(int start, int end) {
389         int p = start;
390         parseFirstFromClause(start, end, p);
391         while (p < end) {
392             Token context = tokens[p];
393             JoinType joinType = JoinType.InnerJoin;
394             if (tokens[p].keyword == KeywordType.LEFT) {
395                 joinType = JoinType.LeftJoin;
396                 p++;
397             } else if (tokens[p].keyword == KeywordType.INNER) {
398                 p++;
399             }
400             enforceHelper!QuerySyntaxException(p < end && tokens[p].keyword == KeywordType.JOIN, "Invalid FROM clause" ~ errorContext(tokens[p]));
401             p++;
402             enforceHelper!QuerySyntaxException(p < end, "Invalid FROM clause - incomplete JOIN" ~ errorContext(tokens[p]));
403             bool fetch = false;
404             if (tokens[p].keyword == KeywordType.FETCH) {
405                 fetch = true;
406                 p++;
407                 enforceHelper!QuerySyntaxException(p < end, "Invalid FROM clause - incomplete JOIN" ~ errorContext(tokens[p]));
408             }
409             string[] path;
410             p = parseFieldRef(p, end, path);
411             string aliasName;
412             bool hasAS = false;
413             if (p < end && tokens[p].keyword == KeywordType.AS) {
414                 p++;
415                 hasAS = true;
416             }
417             enforceHelper!QuerySyntaxException(p < end && tokens[p].type == TokenType.Ident, "Invalid FROM clause - no alias in JOIN" ~ errorContext(tokens[p]));
418             aliasName = tokens[p].text;
419             p++;
420             appendFromClause(context, path, aliasName, joinType, fetch);
421         }
422 		enforceHelper!QuerySyntaxException(p == end, "Invalid FROM clause" ~ errorContext(tokens[p]));
423 	}
424 	
425 	// in pairs {: Ident} replace type of ident with Parameter 
426 	void processParameterNames(int start, int end) {
427 		for (int i = start; i < end; i++) {
428 			if (tokens[i].type == TokenType.Parameter) {
429 				parameterNames ~= cast(string)tokens[i].text;
430 			}
431 		}
432 	}
433 	
434 	FromClauseItem findFromClauseByAlias(string aliasName) {
435         return fromClause.findByAlias(aliasName);
436 	}
437 	
438 	void addSelectClauseItem(string aliasName, string[] propertyNames) {
439 		//trace("addSelectClauseItem alias=" ~ aliasName ~ " properties=" ~ to!string(propertyNames));
440 		FromClauseItem from = aliasName == null ? fromClause.first : findFromClauseByAlias(aliasName);
441 		SelectClauseItem item;
442 		item.from = from;
443 		item.prop = null;
444 		EntityInfo ei = cast(EntityInfo)from.entity;
445 		if (propertyNames.length > 0) {
446 			item.prop = cast(PropertyInfo)ei.findProperty(propertyNames[0]);
447 			propertyNames.popFront();
448 			while (item.prop.embedded) {
449 				//trace("Embedded property " ~ item.prop.propertyName ~ " of type " ~ item.prop.referencedEntityName);
450                 ei = cast(EntityInfo)item.prop.referencedEntity;
451 			    enforceHelper!QuerySyntaxException(propertyNames.length > 0, "@Embedded field property name should be specified when selecting " ~ aliasName ~ "." ~ item.prop.propertyName);
452                 item.prop = cast(PropertyInfo)ei.findProperty(propertyNames[0]);
453 				propertyNames.popFront();
454 			}
455 		}
456 		enforceHelper!QuerySyntaxException(propertyNames.length == 0, "Extra field names in SELECT clause in query " ~ query);
457 		selectClause ~= item;
458 		//insertInPlace(selectClause, 0, item);
459 	}
460 	
461 	void addOrderByClauseItem(string aliasName, string propertyName, bool asc) {
462 		FromClauseItem from = aliasName == null ? fromClause.first : findFromClauseByAlias(aliasName);
463 		OrderByClauseItem item;
464 		item.from = from;
465         item.prop = cast(PropertyInfo)from.entity.findProperty(propertyName);
466 		item.asc = asc;
467 		orderByClause ~= item;
468 		//insertInPlace(orderByClause, 0, item);
469 	}
470 	
471 	void parseOrderByClauseItem(int start, int end) {
472 		// for each comma delimited item
473 		// in current version it can only be
474 		// {property}  or  {alias . property} optionally followed by ASC or DESC
475 		//trace("ORDER BY ITEM: " ~ to!string(start) ~ " .. " ~ to!string(end));
476 		bool asc = true;
477 		if (tokens[end - 1].type == TokenType.Keyword && tokens[end - 1].keyword == KeywordType.ASC) {
478 			end--;
479 		} else if (tokens[end - 1].type == TokenType.Keyword && tokens[end - 1].keyword == KeywordType.DESC) {
480 			asc = false;
481 			end--;
482 		}
483 		enforceHelper!QuerySyntaxException(start < end, "Empty ORDER BY clause item" ~ errorContext(tokens[start]));
484 		if (start == end - 1) {
485 			// no alias
486 			enforceHelper!QuerySyntaxException(tokens[start].type == TokenType.Ident, "Property name expected in ORDER BY clause" ~ errorContext(tokens[start]));
487 			addOrderByClauseItem(null, cast(string)tokens[start].text, asc);
488 		} else if (start == end - 3) {
489 			enforceHelper!QuerySyntaxException(tokens[start].type == TokenType.Alias, "Entity alias expected in ORDER BY clause" ~ errorContext(tokens[start]));
490 			enforceHelper!QuerySyntaxException(tokens[start + 1].type == TokenType.Dot, "Dot expected after entity alias in ORDER BY clause" ~ errorContext(tokens[start]));
491 			enforceHelper!QuerySyntaxException(tokens[start + 2].type == TokenType.Ident, "Property name expected after entity alias in ORDER BY clause" ~ errorContext(tokens[start]));
492 			addOrderByClauseItem(cast(string)tokens[start].text, cast(string)tokens[start + 2].text, asc);
493 		} else {
494 			//trace("range: " ~ to!string(start) ~ " .. " ~ to!string(end));
495 			enforceHelper!QuerySyntaxException(false, "Invalid ORDER BY clause (expected {property [ASC | DESC]} or {alias.property [ASC | DESC]} )" ~ errorContext(tokens[start]));
496 		}
497 	}
498 	
499 	void parseSelectClauseItem(int start, int end) {
500 		// for each comma delimited item
501 		// in current version it can only be
502 		// {property}  or  {alias . property}
503 		//trace("SELECT ITEM: " ~ to!string(start) ~ " .. " ~ to!string(end));
504 		enforceHelper!QuerySyntaxException(tokens[start].type == TokenType.Ident || tokens[start].type == TokenType.Alias, "Property name or alias expected in SELECT clause in query " ~ query ~ errorContext(tokens[start]));
505 		string aliasName;
506 		int p = start;
507 		if (tokens[p].type == TokenType.Alias) {
508             //trace("select clause alias: " ~ tokens[p].text ~ " query: " ~ query);
509 			aliasName = cast(string)tokens[p].text;
510 			p++;
511 			enforceHelper!QuerySyntaxException(p == end || tokens[p].type == TokenType.Dot, "SELECT clause item is invalid (only  [alias.]field{[.field2]}+ allowed) " ~ errorContext(tokens[start]));
512 			if (p < end - 1 && tokens[p].type == TokenType.Dot)
513 				p++;
514 		} else {
515             //trace("select clause non-alias: " ~ tokens[p].text ~ " query: " ~ query);
516         }
517 		string[] fieldNames;
518 		while (p < end && tokens[p].type == TokenType.Ident) {
519 			fieldNames ~= tokens[p].text;
520 			p++;
521 			if (p > end - 1 || tokens[p].type != TokenType.Dot)
522 				break;
523 			// skipping dot
524 			p++;
525 		}
526 		//trace("parseSelectClauseItem pos=" ~ to!string(p) ~ " end=" ~ to!string(end));
527 		enforceHelper!QuerySyntaxException(p >= end, "SELECT clause item is invalid (only  [alias.]field{[.field2]}+ allowed) " ~ errorContext(tokens[start]));
528 		addSelectClauseItem(aliasName, fieldNames);
529 	}
530 	
531 	void parseSelectClause(int start, int end) {
532 		enforceHelper!QuerySyntaxException(start < end, "Invalid SELECT clause" ~ errorContext(tokens[start]));
533 		splitCommaDelimitedList(start, end, &parseSelectClauseItem);
534 	}
535 	
536 	void defaultSelectClause() {
537 		addSelectClauseItem(fromClause.first.entityAlias, null);
538 	}
539 	
540 	bool validateSelectClause() {
541 		enforceHelper!QuerySyntaxException(selectClause != null && selectClause.length > 0, "Invalid SELECT clause");
542 		int aliasCount = 0;
543 		int fieldCount = 0;
544 		foreach(a; selectClause) {
545 			if (a.prop !is null)
546 				fieldCount++;
547 			else
548 				aliasCount++;
549 		}
550 		enforceHelper!QuerySyntaxException((aliasCount == 1 && fieldCount == 0) || (aliasCount == 0 && fieldCount > 0), "You should either use single entity alias or one or more properties in SELECT clause. Don't mix objects with primitive fields");
551         return aliasCount > 0;
552 	}
553 	
554 	void parseWhereClause(int start, int end) {
555 		enforceHelper!QuerySyntaxException(start < end, "Invalid WHERE clause" ~ errorContext(tokens[start]));
556 		whereClause = new Token(tokens[start].pos, TokenType.Expression, tokens, start, end);
557 		//trace("before convert fields:\n" ~ whereClause.dump(0));
558 		convertFields(whereClause.children);
559 		//trace("after convertFields before convertIsNullIsNotNull:\n" ~ whereClause.dump(0));
560 		convertIsNullIsNotNull(whereClause.children);
561 		//trace("after convertIsNullIsNotNull\n" ~ whereClause.dump(0));
562 		convertUnaryPlusMinus(whereClause.children);
563 		//trace("after convertUnaryPlusMinus\n" ~ whereClause.dump(0));
564 		foldBraces(whereClause.children);
565 		//trace("after foldBraces\n" ~ whereClause.dump(0));
566 		foldOperators(whereClause.children);
567 		//trace("after foldOperators\n" ~ whereClause.dump(0));
568 		dropBraces(whereClause.children);
569 		//trace("after dropBraces\n" ~ whereClause.dump(0));
570 	}
571 	
572 	void foldBraces(ref Token[] items) {
573 		while (true) {
574 			if (items.length == 0)
575 				return;
576 			int lastOpen = -1;
577 			int firstClose = -1;
578 			for (int i=0; i<items.length; i++) {
579 				if (items[i].type == TokenType.OpenBracket) {
580 					lastOpen = i;
581 				} if (items[i].type == TokenType.CloseBracket) {
582 					firstClose = i;
583 					break;
584 				}
585 			}
586 			if (lastOpen == -1 && firstClose == -1)
587 				return;
588 			//trace("folding braces " ~ to!string(lastOpen) ~ " .. " ~ to!string(firstClose));
589 			enforceHelper!QuerySyntaxException(lastOpen >= 0 && lastOpen < firstClose, "Unpaired braces in WHERE clause" ~ errorContext(tokens[lastOpen]));
590 			Token folded = new Token(items[lastOpen].pos, TokenType.Braces, items, lastOpen + 1, firstClose);
591 			//			size_t oldlen = items.length;
592 			//			int removed = firstClose - lastOpen;
593 			replaceInPlace(items, lastOpen, firstClose + 1, [folded]);
594 			//			assert(items.length == oldlen - removed);
595 			foldBraces(folded.children);
596 		}
597 	}
598 	
599 	static void dropBraces(ref Token[] items) {
600 		foreach (t; items) {
601 			if (t.children.length > 0)
602 				dropBraces(t.children);
603 		}
604 		for (int i=0; i<items.length; i++) {
605 			if (items[i].type != TokenType.Braces)
606 				continue;
607 			if (items[i].children.length == 1) {
608 				Token t = items[i].children[0];
609 				replaceInPlace(items, i, i + 1, [t]);
610 			}
611 		}
612 	}
613 	
614 	void convertIsNullIsNotNull(ref Token[] items) {
615 		for (int i = cast(int)items.length - 2; i >= 0; i--) {
616 			if (items[i].type != TokenType.Operator || items[i + 1].type != TokenType.Keyword)
617 				continue;
618 			if (items[i].operator == OperatorType.IS && items[i + 1].keyword == KeywordType.NULL) {
619 				Token folded = new Token(items[i].pos,OperatorType.IS_NULL, "IS NULL");
620 				replaceInPlace(items, i, i + 2, [folded]);
621 				i-=2;
622 			}
623 		}
624 		for (int i = cast(int)items.length - 3; i >= 0; i--) {
625 			if (items[i].type != TokenType.Operator || items[i + 1].type != TokenType.Operator || items[i + 2].type != TokenType.Keyword)
626 				continue;
627 			if (items[i].operator == OperatorType.IS && items[i + 1].operator == OperatorType.NOT && items[i + 2].keyword == KeywordType.NULL) {
628 				Token folded = new Token(items[i].pos, OperatorType.IS_NOT_NULL, "IS NOT NULL");
629 				replaceInPlace(items, i, i + 3, [folded]);
630 				i-=3;
631 			}
632 		}
633 	}
634 	
635 	void convertFields(ref Token[] items) {
636 		while(true) {
637 			int p = -1;
638 			for (int i=0; i<items.length; i++) {
639 				if (items[i].type != TokenType.Ident && items[i].type != TokenType.Alias)
640 					continue;
641 				p = i;
642 				break;
643 			}
644 			if (p == -1)
645 				return;
646 			// found identifier at position p
647 			string[] idents;
648 			int lastp = p;
649 			idents ~= items[p].text;
650 			for (int i=p + 1; i < items.length - 1; i+=2) {
651 				if (items[i].type != TokenType.Dot)
652 					break;
653 				enforceHelper!QuerySyntaxException(i < items.length - 1 && items[i + 1].type == TokenType.Ident, "Syntax error in WHERE condition - no property name after . " ~ errorContext(items[p]));
654 				lastp = i + 1;
655 				idents ~= items[i + 1].text;
656 			}
657 			string fullName;
658 			FromClauseItem a;
659 			if (items[p].type == TokenType.Alias) {
660 				a = findFromClauseByAlias(idents[0]);
661 				idents.popFront();
662 			} else {
663 				// use first FROM clause if alias is not specified
664 				a = fromClause.first;
665 			}
666 			string aliasName = a.entityAlias;
667             EntityInfo ei = cast(EntityInfo)a.entity;
668 			enforceHelper!QuerySyntaxException(idents.length > 0, "Syntax error in WHERE condition - alias w/o property name: " ~ aliasName ~ errorContext(items[p]));
669             PropertyInfo pi;
670             fullName = aliasName;
671             while(true) {
672     			string propertyName = idents[0];
673     			idents.popFront();
674     			fullName ~= "." ~ propertyName;
675                 pi = cast(PropertyInfo)ei.findProperty(propertyName);
676     			while (pi.embedded) { // loop to allow nested @Embedded
677     				enforceHelper!QuerySyntaxException(idents.length > 0, "Syntax error in WHERE condition - @Embedded property reference should include reference to @Embeddable property " ~ aliasName ~ errorContext(items[p]));
678     				propertyName = idents[0];
679     				idents.popFront();
680                     pi = cast(PropertyInfo)pi.referencedEntity.findProperty(propertyName);
681     				fullName = fullName ~ "." ~ propertyName;
682     			}
683                 if (idents.length == 0)
684                     break;
685                 if (idents.length > 0) {
686                     // more field names
687                     string pname = idents[0];
688                     enforceHelper!QuerySyntaxException(pi.referencedEntity !is null, "Unexpected extra field name " ~ pname ~ " - property " ~ propertyName ~ " doesn't content subproperties " ~ errorContext(items[p]));
689                     ei = cast(EntityInfo)pi.referencedEntity;
690                     FromClauseItem newClause = fromClause.findByPath(fullName);
691                     if (newClause is null) {
692                         // autogenerate FROM clause
693                         newClause = fromClause.add(ei, null, pi.nullable ? JoinType.LeftJoin : JoinType.InnerJoin, false, a, pi);
694                     }
695                     a = newClause;
696                 }
697             }
698 			enforceHelper!QuerySyntaxException(idents.length == 0, "Unexpected extra field name " ~ idents[0] ~ errorContext(items[p]));
699 			//trace("full name = " ~ fullName);
700 			Token t = new Token(items[p].pos, TokenType.Field, fullName);
701             t.entity = cast(EntityInfo)ei;
702             t.field = cast(PropertyInfo)pi;
703 			t.from = a;
704 			replaceInPlace(items, p, lastp + 1, [t]);
705 		}
706 	}
707 	
708 	static void convertUnaryPlusMinus(ref Token[] items) {
709 		foreach (t; items) {
710 			if (t.children.length > 0)
711 				convertUnaryPlusMinus(t.children);
712 		}
713 		for (int i=0; i<items.length; i++) {
714 			if (items[i].type != TokenType.Operator)
715 				continue;
716 			OperatorType op = items[i].operator;
717 			if (op == OperatorType.ADD || op == OperatorType.SUB) {
718 				// convert + and - to unary form if necessary
719 				if (i == 0 || !items[i - 1].isExpression()) {
720 					items[i].operator = (op == OperatorType.ADD) ? OperatorType.UNARY_PLUS : OperatorType.UNARY_MINUS;
721 				}
722 			}
723 		}
724 	}
725 	
726 	string errorContext(Token token) {
727 		return " near `" ~ query[token.pos .. $] ~ "` in query `" ~ query ~ "`";
728 	}
729 
730     void foldCommaSeparatedList(Token braces) {
731         // fold inside braces
732         Token[] items = braces.children;
733         int start = 0;
734         Token[] list;
735         for (int i=0; i <= items.length; i++) {
736             if (i == items.length || items[i].type == TokenType.Comma) {
737                 enforceHelper!QuerySyntaxException(i > start, "Empty item in comma separated list" ~ errorContext(items[i]));
738                 enforceHelper!QuerySyntaxException(i != items.length - 1, "Empty item in comma separated list" ~ errorContext(items[i]));
739                 Token item = new Token(items[start].pos, TokenType.Expression, braces.children, start, i);
740                 foldOperators(item.children);
741                 enforceHelper!QuerySyntaxException(item.children.length == 1, "Invalid expression in list item" ~ errorContext(items[i]));
742                 list ~= item.children[0];
743                 start = i + 1;
744             }
745         }
746         enforceHelper!QuerySyntaxException(list.length > 0, "Empty list" ~ errorContext(items[0]));
747         braces.type = TokenType.CommaDelimitedList;
748         braces.children = list;
749     }
750 
751 	void foldOperators(ref Token[] items) {
752 		foreach (t; items) {
753 			if (t.children.length > 0)
754 				foldOperators(t.children);
755 		}
756 		while (true) {
757 			//
758 			int bestOpPosition = -1;
759 			int bestOpPrecedency = -1;
760 			OperatorType t = OperatorType.NONE;
761 			for (int i=0; i<items.length; i++) {
762 				if (items[i].type != TokenType.Operator)
763 					continue;
764 				int p = operatorPrecedency(items[i].operator);
765 				if (p > bestOpPrecedency) {
766 					bestOpPrecedency = p;
767 					bestOpPosition = i;
768 					t = items[i].operator;
769 				}
770 			}
771 			if (bestOpPrecedency == -1)
772 				return;
773 			//trace("Found op " ~ items[bestOpPosition].toString() ~ " at position " ~ to!string(bestOpPosition) ~ " with priority " ~ to!string(bestOpPrecedency));
774 			if (t == OperatorType.NOT || t == OperatorType.UNARY_PLUS || t == OperatorType.UNARY_MINUS) {
775 				// fold unary
776 				enforceHelper!QuerySyntaxException(bestOpPosition < items.length && items[bestOpPosition + 1].isExpression(), "Syntax error in WHERE condition " ~ errorContext(items[bestOpPosition]));
777 				Token folded = new Token(items[bestOpPosition].pos, t, items[bestOpPosition].text, items[bestOpPosition + 1]);
778 				replaceInPlace(items, bestOpPosition, bestOpPosition + 2, [folded]);
779 			} else if (t == OperatorType.IS_NULL || t == OperatorType.IS_NOT_NULL) {
780 				// fold unary
781 				enforceHelper!QuerySyntaxException(bestOpPosition > 0 && items[bestOpPosition - 1].isExpression(), "Syntax error in WHERE condition " ~ errorContext(items[bestOpPosition]));
782 				Token folded = new Token(items[bestOpPosition - 1].pos, t, items[bestOpPosition].text, items[bestOpPosition - 1]);
783 				replaceInPlace(items, bestOpPosition - 1, bestOpPosition + 1, [folded]);
784 			} else if (t == OperatorType.BETWEEN) {
785 				// fold  X BETWEEN A AND B
786 				enforceHelper!QuerySyntaxException(bestOpPosition > 0, "Syntax error in WHERE condition - no left arg for BETWEEN operator");
787 				enforceHelper!QuerySyntaxException(bestOpPosition < items.length - 1, "Syntax error in WHERE condition - no min bound for BETWEEN operator " ~ errorContext(items[bestOpPosition]));
788 				enforceHelper!QuerySyntaxException(bestOpPosition < items.length - 3, "Syntax error in WHERE condition - no max bound for BETWEEN operator " ~ errorContext(items[bestOpPosition]));
789 				enforceHelper!QuerySyntaxException(items[bestOpPosition + 2].operator == OperatorType.AND, "Syntax error in WHERE condition - no max bound for BETWEEN operator" ~ errorContext(items[bestOpPosition]));
790 				Token folded = new Token(items[bestOpPosition - 1].pos, t, items[bestOpPosition].text, items[bestOpPosition - 1]);
791 				folded.children ~= items[bestOpPosition + 1];
792 				folded.children ~= items[bestOpPosition + 3];
793 				replaceInPlace(items, bestOpPosition - 1, bestOpPosition + 4, [folded]);
794             } else if (t == OperatorType.IN) {
795                 // fold  X IN (A, B, ...)
796                 enforceHelper!QuerySyntaxException(bestOpPosition > 0, "Syntax error in WHERE condition - no left arg for IN operator");
797                 enforceHelper!QuerySyntaxException(bestOpPosition < items.length - 1, "Syntax error in WHERE condition - no value list for IN operator " ~ errorContext(items[bestOpPosition]));
798                 enforceHelper!QuerySyntaxException(items[bestOpPosition + 1].type == TokenType.Braces, "Syntax error in WHERE condition - no value list in braces for IN operator" ~ errorContext(items[bestOpPosition]));
799                 Token folded = new Token(items[bestOpPosition - 1].pos, t, items[bestOpPosition].text, items[bestOpPosition - 1]);
800                 folded.children ~= items[bestOpPosition + 1];
801                 foldCommaSeparatedList(items[bestOpPosition + 1]);
802                 replaceInPlace(items, bestOpPosition - 1, bestOpPosition + 2, [folded]);
803                 // fold value list
804                 //trace("IN operator found: " ~ folded.dump(3));
805             } else {
806 				// fold binary
807 				enforceHelper!QuerySyntaxException(bestOpPosition > 0, "Syntax error in WHERE condition - no left arg for binary operator " ~ errorContext(items[bestOpPosition]));
808 				enforceHelper!QuerySyntaxException(bestOpPosition < items.length - 1, "Syntax error in WHERE condition - no right arg for binary operator " ~ errorContext(items[bestOpPosition]));
809 				//trace("binary op " ~ items[bestOpPosition - 1].toString() ~ " " ~ items[bestOpPosition].toString() ~ " " ~ items[bestOpPosition + 1].toString());
810 				enforceHelper!QuerySyntaxException(items[bestOpPosition - 1].isExpression(), "Syntax error in WHERE condition - wrong type of left arg for binary operator " ~ errorContext(items[bestOpPosition]));
811 				enforceHelper!QuerySyntaxException(items[bestOpPosition + 1].isExpression(), "Syntax error in WHERE condition - wrong type of right arg for binary operator " ~ errorContext(items[bestOpPosition]));
812 				Token folded = new Token(items[bestOpPosition - 1].pos, t, items[bestOpPosition].text, items[bestOpPosition - 1], items[bestOpPosition + 1]);
813 				auto oldlen = items.length;
814 				replaceInPlace(items, bestOpPosition - 1, bestOpPosition + 2, [folded]);
815 				assert(items.length == oldlen - 2);
816 			}
817 		}
818 	}
819 	
820 	void parseOrderClause(int start, int end) {
821 		enforceHelper!QuerySyntaxException(start < end, "Invalid ORDER BY clause" ~ errorContext(tokens[start]));
822 		splitCommaDelimitedList(start, end, &parseOrderByClauseItem);
823 	}
824 	
825 	/// returns position of keyword in tokens array, -1 if not found
826 	int findKeyword(KeywordType k, int startFrom = 0) {
827 		for (int i = startFrom; i < tokens.length; i++) {
828 			if (tokens[i].type == TokenType.Keyword && tokens[i].keyword == k)
829 				return i;
830 		}
831 		return -1;
832 	}
833 	
834     int addSelectSQL(Dialect dialect, ParsedQuery res, string tableName, bool first, const EntityInfo ei) {
835         int colCount = 0;
836         for(int j = 0; j < ei.getPropertyCount(); j++) {
837             PropertyInfo f = cast(PropertyInfo)ei.getProperty(j);
838             string fieldName = f.columnName;
839             if (f.embedded) {
840                 // put embedded cols here
841                 colCount += addSelectSQL(dialect, res, tableName, first && colCount == 0, f.referencedEntity);
842                 continue;
843             } else if (f.oneToOne) {
844             } else {
845             }
846             if (fieldName is null)
847                 continue;
848             if (!first || colCount > 0) {
849                 res.appendSQL(", ");
850             } else
851                 first = false;
852             
853             res.appendSQL(tableName ~ "." ~ dialect.quoteIfNeeded(fieldName));
854             colCount++;
855         }
856         return colCount;
857     }
858 
859 	void addSelectSQL(Dialect dialect, ParsedQuery res) {
860 		res.appendSQL("SELECT ");
861 		bool first = true;
862 		assert(selectClause.length > 0);
863 		int colCount = 0;
864         foreach(i, s; selectClause) {
865             s.from.selectIndex = cast(int)i;
866         }
867 		if (selectClause[0].prop is null) {
868 			// object alias is specified: add all properties of object
869             //trace("selected entity count: " ~ to!string(selectClause.length));
870             res.setEntity(selectClause[0].from.entity);
871             for(int i = 0; i < fromClause.length; i++) {
872                 FromClauseItem from = fromClause[i];
873                 if (!from.fetch)
874                     continue;
875                 string tableName = from.sqlAlias;
876     			assert(from !is null);
877     			assert(from.entity !is null);
878                 colCount += addSelectSQL(dialect, res, tableName, colCount == 0, from.entity);
879             }
880 		} else {
881 			// individual fields specified
882 			res.setEntity(null);
883 			foreach(a; selectClause) {
884 				string fieldName = a.prop.columnName;
885 				string tableName = a.from.sqlAlias;
886 				if (!first) {
887 					res.appendSQL(", ");
888 				} else
889 					first = false;
890 				res.appendSQL(tableName ~ "." ~ dialect.quoteIfNeeded(fieldName));
891 				colCount++;
892 			}
893 		}
894 		res.setColCount(colCount);
895         res.setSelect(selectClause);
896 	}
897 	
898 	void addFromSQL(Dialect dialect, ParsedQuery res) {
899         res.setFromClause(fromClause);
900 		res.appendSpace();
901 		res.appendSQL("FROM ");
902 		res.appendSQL(dialect.quoteIfNeeded(fromClause.first.entity.tableName) ~ " AS " ~ fromClause.first.sqlAlias);
903         for (int i = 1; i < fromClause.length; i++) {
904             FromClauseItem join = fromClause[i];
905             FromClauseItem base = join.base;
906             assert(join !is null && base !is null);
907             res.appendSpace();
908 
909             assert(join.baseProperty !is null);
910             if (join.baseProperty.manyToMany) {
911                 string joinTableAlias = base.sqlAlias ~ join.sqlAlias;
912                 res.appendSQL(join.joinType == JoinType.LeftJoin ? "LEFT JOIN " : "INNER JOIN ");
913 
914                 res.appendSQL(dialect.quoteIfNeeded(join.baseProperty.joinTable.tableName) ~ " AS " ~ joinTableAlias);
915                 res.appendSQL(" ON ");
916                 res.appendSQL(base.sqlAlias);
917                 res.appendSQL(".");
918                 res.appendSQL(dialect.quoteIfNeeded(base.entity.getKeyProperty().columnName));
919                 res.appendSQL("=");
920                 res.appendSQL(joinTableAlias);
921                 res.appendSQL(".");
922                 res.appendSQL(dialect.quoteIfNeeded(join.baseProperty.joinTable.column1));
923 
924                 res.appendSpace();
925 
926                 res.appendSQL(join.joinType == JoinType.LeftJoin ? "LEFT JOIN " : "INNER JOIN ");
927                 res.appendSQL(dialect.quoteIfNeeded(join.entity.tableName) ~ " AS " ~ join.sqlAlias);
928                 res.appendSQL(" ON ");
929                 res.appendSQL(joinTableAlias);
930                 res.appendSQL(".");
931                 res.appendSQL(dialect.quoteIfNeeded(join.baseProperty.joinTable.column2));
932                 res.appendSQL("=");
933                 res.appendSQL(join.sqlAlias);
934                 res.appendSQL(".");
935                 res.appendSQL(dialect.quoteIfNeeded(join.entity.getKeyProperty().columnName));
936             } else {
937                 res.appendSQL(join.joinType == JoinType.LeftJoin ? "LEFT JOIN " : "INNER JOIN ");
938                 res.appendSQL(dialect.quoteIfNeeded(join.entity.tableName) ~ " AS " ~ join.sqlAlias);
939                 res.appendSQL(" ON ");
940                 //trace("adding ON");
941                 if (join.baseProperty.oneToOne) {
942                     assert(join.baseProperty.columnName !is null || join.baseProperty.referencedProperty !is null);
943                     if (join.baseProperty.columnName !is null) {
944                         //trace("fk is in base");
945                         res.appendSQL(base.sqlAlias);
946                         res.appendSQL(".");
947                         res.appendSQL(dialect.quoteIfNeeded(join.baseProperty.columnName));
948                         res.appendSQL("=");
949                         res.appendSQL(join.sqlAlias);
950                         res.appendSQL(".");
951                         res.appendSQL(dialect.quoteIfNeeded(join.entity.getKeyProperty().columnName));
952                     } else {
953                         //trace("fk is in join");
954                         res.appendSQL(base.sqlAlias);
955                         res.appendSQL(".");
956                         res.appendSQL(dialect.quoteIfNeeded(base.entity.getKeyProperty().columnName));
957                         res.appendSQL("=");
958                         res.appendSQL(join.sqlAlias);
959                         res.appendSQL(".");
960                         res.appendSQL(dialect.quoteIfNeeded(join.baseProperty.referencedProperty.columnName));
961                     }
962                 } else if (join.baseProperty.manyToOne) {
963                     assert(join.baseProperty.columnName !is null, "ManyToOne should have JoinColumn as well");
964                     //trace("fk is in base");
965                     res.appendSQL(base.sqlAlias);
966                     res.appendSQL(".");
967                     res.appendSQL(dialect.quoteIfNeeded(join.baseProperty.columnName));
968                     res.appendSQL("=");
969                     res.appendSQL(join.sqlAlias);
970                     res.appendSQL(".");
971                     res.appendSQL(dialect.quoteIfNeeded(join.entity.getKeyProperty().columnName));
972                 } else if (join.baseProperty.oneToMany) {
973                     res.appendSQL(base.sqlAlias);
974                     res.appendSQL(".");
975                     res.appendSQL(dialect.quoteIfNeeded(base.entity.getKeyProperty().columnName));
976                     res.appendSQL("=");
977                     res.appendSQL(join.sqlAlias);
978                     res.appendSQL(".");
979                     res.appendSQL(dialect.quoteIfNeeded(join.baseProperty.referencedProperty.columnName));
980                 } else {
981                     // TODO: support other relations
982                     throw new QuerySyntaxException("Invalid relation type in join");
983                 }
984             }
985         }
986 	}
987 	
988 	void addWhereCondition(Token t, int basePrecedency, Dialect dialect, ParsedQuery res) {
989 		if (t.type == TokenType.Expression) {
990 			addWhereCondition(t.children[0], basePrecedency, dialect, res);
991 		} else if (t.type == TokenType.Field) {
992 			string tableName = t.from.sqlAlias;
993 			string fieldName = t.field.columnName;
994 			res.appendSpace();
995 			res.appendSQL(tableName ~ "." ~ dialect.quoteIfNeeded(fieldName));
996 		} else if (t.type == TokenType.Number) {
997 			res.appendSpace();
998 			res.appendSQL(t.text);
999 		} else if (t.type == TokenType.String) {
1000 			res.appendSpace();
1001 			res.appendSQL(dialect.quoteSqlString(t.text));
1002 		} else if (t.type == TokenType.Parameter) {
1003 			res.appendSpace();
1004 			res.appendSQL("?");
1005 			res.addParam(t.text);
1006         } else if (t.type == TokenType.CommaDelimitedList) {
1007             bool first = true;
1008             for (int i=0; i<t.children.length; i++) {
1009                 if (!first)
1010                     res.appendSQL(", ");
1011                 else
1012                     first = false;
1013                 addWhereCondition(t.children[i], 0, dialect, res);
1014             }
1015         } else if (t.type == TokenType.OpExpr) {
1016 			int currentPrecedency = operatorPrecedency(t.operator);
1017 			bool needBraces = currentPrecedency < basePrecedency;
1018 			if (needBraces)
1019 				res.appendSQL("(");
1020 			switch(t.operator) {
1021 				case OperatorType.LIKE:
1022 				case OperatorType.EQ:
1023 				case OperatorType.NE:
1024 				case OperatorType.LT:
1025 				case OperatorType.GT:
1026 				case OperatorType.LE:
1027 				case OperatorType.GE:
1028 				case OperatorType.MUL:
1029 				case OperatorType.ADD:
1030 				case OperatorType.SUB:
1031 				case OperatorType.DIV:
1032 				case OperatorType.AND:
1033 				case OperatorType.OR:
1034 				case OperatorType.IDIV:
1035 				case OperatorType.MOD:
1036 					// binary op
1037 					if (!needBraces)
1038 						res.appendSpace();
1039 					addWhereCondition(t.children[0], currentPrecedency, dialect, res);
1040 					res.appendSpace();
1041 					res.appendSQL(t.text);
1042 					res.appendSpace();
1043 					addWhereCondition(t.children[1], currentPrecedency, dialect, res);
1044 					break;
1045 				case OperatorType.UNARY_PLUS:
1046 				case OperatorType.UNARY_MINUS:
1047 				case OperatorType.NOT:
1048 					// unary op
1049 					if (!needBraces)
1050 						res.appendSpace();
1051 					res.appendSQL(t.text);
1052 					res.appendSpace();
1053 					addWhereCondition(t.children[0], currentPrecedency, dialect, res);
1054 					break;
1055 				case OperatorType.IS_NULL:
1056 				case OperatorType.IS_NOT_NULL:
1057 					addWhereCondition(t.children[0], currentPrecedency, dialect, res);
1058 					res.appendSpace();
1059 					res.appendSQL(t.text);
1060 					res.appendSpace();
1061 					break;
1062 				case OperatorType.BETWEEN:
1063 					if (!needBraces)
1064 						res.appendSpace();
1065 					addWhereCondition(t.children[0], currentPrecedency, dialect, res);
1066 					res.appendSQL(" BETWEEN ");
1067 					addWhereCondition(t.children[1], currentPrecedency, dialect, res);
1068 					res.appendSQL(" AND ");
1069 					addWhereCondition(t.children[2], currentPrecedency, dialect, res);
1070 					break;
1071                 case OperatorType.IN:
1072                     if (!needBraces)
1073                         res.appendSpace();
1074                     addWhereCondition(t.children[0], currentPrecedency, dialect, res);
1075                     res.appendSQL(" IN (");
1076                     addWhereCondition(t.children[1], currentPrecedency, dialect, res);
1077                     res.appendSQL(")");
1078                     break;
1079 				case OperatorType.IS:
1080 				default:
1081 					enforceHelper!QuerySyntaxException(false, "Unexpected operator" ~ errorContext(t));
1082 					break;
1083 			}
1084 			if (needBraces)
1085 				res.appendSQL(")");
1086 		}
1087 	}
1088 	
1089 	void addWhereSQL(Dialect dialect, ParsedQuery res) {
1090 		if (whereClause is null)
1091 			return;
1092 		res.appendSpace();
1093 		res.appendSQL("WHERE ");
1094 		addWhereCondition(whereClause, 0, dialect, res);
1095 	}
1096 	
1097 	void addOrderBySQL(Dialect dialect, ParsedQuery res) {
1098 		if (orderByClause.length == 0)
1099 			return;
1100 		res.appendSpace();
1101 		res.appendSQL("ORDER BY ");
1102 		bool first = true;
1103 		// individual fields specified
1104 		foreach(a; orderByClause) {
1105 			string fieldName = a.prop.columnName;
1106 			string tableName = a.from.sqlAlias;
1107 			if (!first) {
1108 				res.appendSQL(", ");
1109 			} else 
1110 				first = false;
1111 			res.appendSQL(tableName ~ "." ~ dialect.quoteIfNeeded(fieldName));
1112 			if (!a.asc)
1113 				res.appendSQL(" DESC");
1114 		}
1115 	}
1116 	
1117 	ParsedQuery makeSQL(Dialect dialect) {
1118 		ParsedQuery res = new ParsedQuery(query);
1119 		addSelectSQL(dialect, res);
1120 		addFromSQL(dialect, res);
1121 		addWhereSQL(dialect, res);
1122 		addOrderBySQL(dialect, res);
1123 		return res;
1124 	}
1125 	
1126 }
1127 
1128 enum KeywordType {
1129 	NONE,
1130 	SELECT,
1131 	FROM,
1132 	WHERE,
1133 	ORDER,
1134 	BY,
1135 	ASC,
1136 	DESC,
1137 	JOIN,
1138 	INNER,
1139 	OUTER,
1140 	LEFT,
1141 	RIGHT,
1142     FETCH,
1143     AS,
1144 	LIKE,
1145 	IN,
1146 	IS,
1147 	NOT,
1148 	NULL,
1149 	AND,
1150 	OR,
1151 	BETWEEN,
1152 	DIV,
1153 	MOD,
1154 }
1155 
1156 KeywordType isKeyword(string str) {
1157 	return isKeyword(str.dup);
1158 }
1159 
1160 KeywordType isKeyword(char[] str) {
1161 	char[] s = toUpper(str);
1162 	if (s=="SELECT") return KeywordType.SELECT;
1163 	if (s=="FROM") return KeywordType.FROM;
1164 	if (s=="WHERE") return KeywordType.WHERE;
1165 	if (s=="ORDER") return KeywordType.ORDER;
1166 	if (s=="BY") return KeywordType.BY;
1167 	if (s=="ASC") return KeywordType.ASC;
1168 	if (s=="DESC") return KeywordType.DESC;
1169 	if (s=="JOIN") return KeywordType.JOIN;
1170 	if (s=="INNER") return KeywordType.INNER;
1171 	if (s=="OUTER") return KeywordType.OUTER;
1172 	if (s=="LEFT") return KeywordType.LEFT;
1173 	if (s=="RIGHT") return KeywordType.RIGHT;
1174     if (s=="FETCH") return KeywordType.FETCH;
1175     if (s=="LIKE") return KeywordType.LIKE;
1176 	if (s=="IN") return KeywordType.IN;
1177 	if (s=="IS") return KeywordType.IS;
1178 	if (s=="NOT") return KeywordType.NOT;
1179 	if (s=="NULL") return KeywordType.NULL;
1180 	if (s=="AS") return KeywordType.AS;
1181 	if (s=="AND") return KeywordType.AND;
1182 	if (s=="OR") return KeywordType.OR;
1183 	if (s=="BETWEEN") return KeywordType.BETWEEN;
1184 	if (s=="DIV") return KeywordType.DIV;
1185 	if (s=="MOD") return KeywordType.MOD;
1186 	return KeywordType.NONE;
1187 }
1188 
1189 unittest {
1190 	assert(isKeyword("Null") == KeywordType.NULL);
1191 	assert(isKeyword("from") == KeywordType.FROM);
1192 	assert(isKeyword("SELECT") == KeywordType.SELECT);
1193 	assert(isKeyword("blabla") == KeywordType.NONE);
1194 }
1195 
1196 enum OperatorType {
1197 	NONE,
1198 	
1199 	// symbolic
1200 	EQ, // ==
1201 	NE, // != <>
1202 	LT, // <
1203 	GT, // >
1204 	LE, // <=
1205 	GE, // >=
1206 	MUL,// *
1207 	ADD,// +
1208 	SUB,// -
1209 	DIV,// /
1210 	
1211 	// from keywords
1212 	LIKE,
1213 	IN,
1214 	IS,
1215 	NOT,
1216 	AND,
1217 	OR,
1218 	BETWEEN,
1219 	IDIV,
1220 	MOD,
1221 	
1222 	UNARY_PLUS,
1223 	UNARY_MINUS,
1224 	
1225 	IS_NULL,
1226 	IS_NOT_NULL,
1227 }
1228 
1229 OperatorType isOperator(KeywordType t) {
1230 	switch (t) {
1231 		case KeywordType.LIKE: return OperatorType.LIKE;
1232 		case KeywordType.IN: return OperatorType.IN;
1233 		case KeywordType.IS: return OperatorType.IS;
1234 		case KeywordType.NOT: return OperatorType.NOT;
1235 		case KeywordType.AND: return OperatorType.AND;
1236 		case KeywordType.OR: return OperatorType.OR;
1237 		case KeywordType.BETWEEN: return OperatorType.BETWEEN;
1238 		case KeywordType.DIV: return OperatorType.IDIV;
1239 		case KeywordType.MOD: return OperatorType.MOD;
1240 		default: return OperatorType.NONE;
1241 	}
1242 }
1243 
1244 int operatorPrecedency(OperatorType t) {
1245 	switch(t) {
1246 		case OperatorType.EQ: return 5; // ==
1247 		case OperatorType.NE: return 5; // != <>
1248 		case OperatorType.LT: return 5; // <
1249 		case OperatorType.GT: return 5; // >
1250 		case OperatorType.LE: return 5; // <=
1251 		case OperatorType.GE: return 5; // >=
1252 		case OperatorType.MUL: return 10; // *
1253 		case OperatorType.ADD: return 9; // +
1254 		case OperatorType.SUB: return 9; // -
1255 		case OperatorType.DIV: return 10; // /
1256 			// from keywords
1257 		case OperatorType.LIKE: return 11;
1258 		case OperatorType.IN: return 12;
1259 		case OperatorType.IS: return 13;
1260 		case OperatorType.NOT: return 6; // ???
1261 		case OperatorType.AND: return 4;
1262 		case OperatorType.OR:  return 3;
1263 		case OperatorType.BETWEEN: return 7; // ???
1264 		case OperatorType.IDIV: return 10;
1265 		case OperatorType.MOD: return 10;
1266 		case OperatorType.UNARY_PLUS: return 15;
1267 		case OperatorType.UNARY_MINUS: return 15;
1268 		case OperatorType.IS_NULL: return 15;
1269 		case OperatorType.IS_NOT_NULL: return 15;
1270 		default: return -1;
1271 	}
1272 }
1273 
1274 OperatorType isOperator(string s, ref int i) {
1275 	int len = cast(int)s.length;
1276 	char ch = s[i];
1277 	char ch2 = i < len - 1 ? s[i + 1] : 0;
1278 	//char ch3 = i < len - 2 ? s[i + 2] : 0;
1279 	if (ch == '=' && ch2 == '=') { i++; return OperatorType.EQ; } // ==
1280 	if (ch == '!' && ch2 == '=') { i++; return OperatorType.NE; } // !=
1281 	if (ch == '<' && ch2 == '>') { i++; return OperatorType.NE; } // <>
1282 	if (ch == '<' && ch2 == '=') { i++; return OperatorType.LE; } // <=
1283 	if (ch == '>' && ch2 == '=') { i++; return OperatorType.GE; } // >=
1284 	if (ch == '=') return OperatorType.EQ; // =
1285 	if (ch == '<') return OperatorType.LT; // <
1286 	if (ch == '>') return OperatorType.GT; // <
1287 	if (ch == '*') return OperatorType.MUL; // <
1288 	if (ch == '+') return OperatorType.ADD; // <
1289 	if (ch == '-') return OperatorType.SUB; // <
1290 	if (ch == '/') return OperatorType.DIV; // <
1291 	return OperatorType.NONE;
1292 }
1293 
1294 
1295 enum TokenType {
1296 	Keyword,      // WHERE
1297 	Ident,        // ident
1298 	Number,       // 25   13.5e-10
1299 	String,       // 'string'
1300 	Operator,     // == != <= >= < > + - * /
1301 	Dot,          // .
1302 	OpenBracket,  // (
1303 	CloseBracket, // )
1304 	Comma,        // ,
1305 	Entity,       // entity name
1306 	Field,        // field name of some entity
1307 	Alias,        // alias name of some entity
1308 	Parameter,    // ident after :
1309 	// types of compound AST nodes
1310 	Expression,   // any expression
1311 	Braces,       // ( tokens )
1312 	CommaDelimitedList, // tokens, ... , tokens
1313 	OpExpr, // operator expression; current token == operator, children = params
1314 }
1315 
1316 class Token {
1317 	int pos;
1318 	TokenType type;
1319 	KeywordType keyword = KeywordType.NONE;
1320 	OperatorType operator = OperatorType.NONE;
1321 	string text;
1322 	string spaceAfter;
1323 	EntityInfo entity;
1324     PropertyInfo field;
1325 	FromClauseItem from;
1326 	Token[] children;
1327 	this(int pos, TokenType type, string text) {
1328 		this.pos = pos;
1329 		this.type = type;
1330 		this.text = text;
1331 	}
1332 	this(int pos, KeywordType keyword, string text) {
1333 		this.pos = pos;
1334 		this.type = TokenType.Keyword;
1335 		this.keyword = keyword;
1336 		this.text = text;
1337 	}
1338 	this(int pos, OperatorType op, string text) {
1339 		this.pos = pos;
1340 		this.type = TokenType.Operator;
1341 		this.operator = op;
1342 		this.text = text;
1343 	}
1344 	this(int pos, TokenType type, Token[] base, int start, int end) {
1345 		this.pos = pos;
1346 		this.type = type;
1347 		this.children = new Token[end - start];
1348 		for (int i = start; i < end; i++)
1349 			children[i - start] = base[i];
1350 	}
1351 	// unary operator expression
1352 	this(int pos, OperatorType type, string text, Token right) {
1353 		this.pos = pos;
1354 		this.type = TokenType.OpExpr;
1355 		this.operator = type;
1356 		this.text = text;
1357 		this.children = new Token[1];
1358 		this.children[0] = right;
1359 	}
1360 	// binary operator expression
1361 	this(int pos, OperatorType type, string text, Token left, Token right) {
1362 		this.pos = pos;
1363 		this.type = TokenType.OpExpr;
1364 		this.text = text;
1365 		this.operator = type;
1366 		this.children = new Token[2];
1367 		this.children[0] = left;
1368 		this.children[1] = right;
1369 	}
1370 	bool isExpression() {
1371 		return type==TokenType.Expression || type==TokenType.Braces || type==TokenType.OpExpr || type==TokenType.Parameter 
1372 			|| type==TokenType.Field || type==TokenType.String || type==TokenType.Number;
1373 	}
1374 	bool isCompound() {
1375 		return this.type >= TokenType.Expression;
1376 	}
1377 	string dump(int level) {
1378 		string res;
1379 		for (int i=0; i<level; i++)
1380 			res ~= "    ";
1381 		res ~= toString() ~ "\n";
1382 		foreach (c; children)
1383 			res ~= c.dump(level + 1);
1384 		return res;
1385 	}
1386 	override string toString() {
1387 		switch (type) {
1388 			case TokenType.Keyword:      // WHERE
1389 			case TokenType.Ident: return "`" ~ text ~ "`";        // ident
1390 			case TokenType.Number: return "" ~ text;       // 25   13.5e-10
1391 			case TokenType.String: return "'" ~ text ~ "'";       // 'string'
1392 			case TokenType.Operator: return "op:" ~ text;     // == != <= >= < > + - * /
1393 			case TokenType.Dot: return ".";          // .
1394 			case TokenType.OpenBracket: return "(";  // (
1395 			case TokenType.CloseBracket: return ")"; // )
1396 			case TokenType.Comma: return ",";        // ,
1397 			case TokenType.Entity: return "entity: " ~ entity.name;       // entity name
1398 			case TokenType.Field: return from.entityAlias ~ "." ~ field.propertyName;        // field name of some entity
1399 			case TokenType.Alias: return "alias: " ~ text;        // alias name of some entity
1400 			case TokenType.Parameter: return ":" ~ text;    // ident after :
1401 				// types of compound AST nodes
1402 			case TokenType.Expression: return "expr";   // any expression
1403 			case TokenType.Braces: return "()";       // ( tokens )
1404 			case TokenType.CommaDelimitedList: return ",,,"; // tokens, ... , tokens
1405 			case TokenType.OpExpr: return "" ~ text;
1406 			default: return "UNKNOWN";
1407 		}
1408 	}
1409 	
1410 }
1411 
1412 Token[] tokenize(string s) {
1413 	Token[] res;
1414 	int startpos = 0;
1415 	int state = 0;
1416 	int len = cast(int)s.length;
1417 	for (int i=0; i<len; i++) {
1418 		char ch = s[i];
1419 		char ch2 = i < len - 1 ? s[i + 1] : 0;
1420 		char ch3 = i < len - 2 ? s[i + 2] : 0;
1421 		string text;
1422 		bool quotedIdent = ch == '`';
1423 		startpos = i;
1424 		OperatorType op = isOperator(s, i);
1425 		if (op != OperatorType.NONE) {
1426 			// operator
1427 			res ~= new Token(startpos, op, s[startpos .. i + 1]);
1428 		} else if (ch == ':' && (isAlpha(ch2) || ch2=='_')) {
1429 			// parameter name
1430 			i++;
1431 			// && state == 0
1432 			for(int j=i; j<len; j++) {
1433 				if (isAlphaNum(s[j]) || s[j] == '_') {
1434 					text ~= s[j];
1435 					i = j;
1436 				} else {
1437 					break;
1438 				}
1439 			}
1440 			enforceHelper!QuerySyntaxException(text.length > 0, "Invalid parameter name near " ~ cast(string)s[startpos .. $]);
1441 			res ~= new Token(startpos, TokenType.Parameter, text);
1442 		} else if (isAlpha(ch) || ch=='_' || quotedIdent) {
1443 			// identifier or keyword
1444 			if (quotedIdent) {
1445 				i++;
1446 				enforceHelper!QuerySyntaxException(i < len - 1, "Invalid quoted identifier near " ~ cast(string)s[startpos .. $]);
1447 			}
1448 			// && state == 0
1449 			for(int j=i; j<len; j++) {
1450 				if (isAlphaNum(s[j]) || s[j] == '_') {
1451 					text ~= s[j];
1452 					i = j;
1453 				} else {
1454 					break;
1455 				}
1456 			}
1457 			enforceHelper!QuerySyntaxException(text.length > 0, "Invalid quoted identifier near " ~ cast(string)s[startpos .. $]);
1458 			if (quotedIdent) {
1459 				enforceHelper!QuerySyntaxException(i < len - 1 && s[i + 1] == '`', "Invalid quoted identifier near " ~ cast(string)s[startpos .. $]);
1460 				i++;
1461 			}
1462 			KeywordType keywordId = isKeyword(text);
1463 			if (keywordId != KeywordType.NONE && !quotedIdent) {
1464 				OperatorType keywordOp = isOperator(keywordId);
1465 				if (keywordOp != OperatorType.NONE)
1466 					res ~= new Token(startpos, keywordOp, text); // operator keyword
1467 				else
1468 					res ~= new Token(startpos, keywordId, text);
1469 			} else
1470 				res ~= new Token(startpos, TokenType.Ident, text);
1471 		} else if (isWhite(ch)) {
1472 			// whitespace
1473 			for(int j=i; j<len; j++) {
1474 				if (isWhite(s[j])) {
1475 					text ~= s[j];
1476 					i = j;
1477 				} else {
1478 					break;
1479 				}
1480 			}
1481 			// don't add whitespace to lexer results as separate token
1482 			// add as spaceAfter
1483 			if (res.length > 0) {
1484 				res[$ - 1].spaceAfter = text;
1485 			}
1486 		} else if (ch == '\'') {
1487 			// string constant
1488 			i++;
1489 			for(int j=i; j<len; j++) {
1490 				if (s[j] != '\'') {
1491 					text ~= s[j];
1492 					i = j;
1493 				} else {
1494 					break;
1495 				}
1496 			}
1497 			enforceHelper!QuerySyntaxException(i < len - 1 && s[i + 1] == '\'', "Unfinished string near " ~ cast(string)s[startpos .. $]);
1498 			i++;
1499 			res ~= new Token(startpos, TokenType.String, text);
1500 		} else if (isDigit(ch) || (ch == '.' && isDigit(ch2))) {
1501 			// numeric constant
1502 			if (ch == '.') {
1503 				// .25
1504 				text ~= '.';
1505 				i++;
1506 				for(int j = i; j<len; j++) {
1507 					if (isDigit(s[j])) {
1508 						text ~= s[j];
1509 						i = j;
1510 					} else {
1511 						break;
1512 					}
1513 				}
1514 			} else {
1515 				// 123
1516 				for(int j=i; j<len; j++) {
1517 					if (isDigit(s[j])) {
1518 						text ~= s[j];
1519 						i = j;
1520 					} else {
1521 						break;
1522 					}
1523 				}
1524 				// .25
1525 				if (i < len - 1 && s[i + 1] == '.') {
1526 					text ~= '.';
1527 					i++;
1528 					for(int j = i; j<len; j++) {
1529 						if (isDigit(s[j])) {
1530 							text ~= s[j];
1531 							i = j;
1532 						} else {
1533 							break;
1534 						}
1535 					}
1536 				}
1537 			}
1538 			if (i < len - 1 && std.ascii.toLower(s[i + 1]) == 'e') {
1539 				text ~= s[i+1];
1540 				i++;
1541 				if (i < len - 1 && (s[i + 1] == '-' || s[i + 1] == '+')) {
1542 					text ~= s[i+1];
1543 					i++;
1544 				}
1545 				enforceHelper!QuerySyntaxException(i < len - 1 && isDigit(s[i]), "Invalid number near " ~ cast(string)s[startpos .. $]);
1546 				for(int j = i; j<len; j++) {
1547 					if (isDigit(s[j])) {
1548 						text ~= s[j];
1549 						i = j;
1550 					} else {
1551 						break;
1552 					}
1553 				}
1554 			}
1555 			enforceHelper!QuerySyntaxException(i >= len - 1 || !isAlpha(s[i]), "Invalid number near " ~ cast(string)s[startpos .. $]);
1556 			res ~= new Token(startpos, TokenType.Number, text);
1557 		} else if (ch == '.') {
1558 			res ~= new Token(startpos, TokenType.Dot, ".");
1559 		} else if (ch == '(') {
1560 			res ~= new Token(startpos, TokenType.OpenBracket, "(");
1561 		} else if (ch == ')') {
1562 			res ~= new Token(startpos, TokenType.CloseBracket, ")");
1563 		} else if (ch == ',') {
1564 			res ~= new Token(startpos, TokenType.Comma, ",");
1565 		} else {
1566 			enforceHelper!QuerySyntaxException(false, "Invalid character near " ~ cast(string)s[startpos .. $]);
1567 		}
1568 	}
1569 	return res;
1570 }
1571 
1572 unittest {
1573 	Token[] tokens;
1574 	tokens = tokenize("SELECT a From User a where a.flags = 12 AND a.name='john' ORDER BY a.idx ASC");
1575 	assert(tokens.length == 23);
1576 	assert(tokens[0].type == TokenType.Keyword);
1577 	assert(tokens[2].type == TokenType.Keyword);
1578 	assert(tokens[5].type == TokenType.Keyword);
1579 	assert(tokens[5].text == "where");
1580 	assert(tokens[10].type == TokenType.Number);
1581 	assert(tokens[10].text == "12");
1582 	assert(tokens[16].type == TokenType.String);
1583 	assert(tokens[16].text == "john");
1584 	assert(tokens[22].type == TokenType.Keyword);
1585 	assert(tokens[22].text == "ASC");
1586 }
1587 
1588 class ParameterValues {
1589 	Variant[string] values;
1590 	int[][string]params;
1591 	int[string]unboundParams;
1592 	this(int[][string]params) {
1593 		this.params = params;
1594 		foreach(key, value; params) {
1595 			unboundParams[key] = 1;
1596 		}
1597 	}
1598 	void setParameter(string name, Variant value) {
1599         enforceHelper!QueryParameterException((name in params) !is null, "Attempting to set unknown parameter " ~ name);
1600 		unboundParams.remove(name);
1601 		values[name] = value;
1602 	}
1603 	void checkAllParametersSet() {
1604 		if (unboundParams.length == 0)
1605 			return;
1606 		string list;
1607 		foreach(key, value; unboundParams) {
1608 			if (list.length > 0)
1609 				list ~= ", ";
1610 			list ~= key;
1611 		}
1612         enforceHelper!QueryParameterException(false, "Parameters " ~ list ~ " not set");
1613 	}
1614 	void applyParams(DataSetWriter ds) {
1615 		foreach(key, indexes; params) {
1616 			Variant value = values[key];
1617 			foreach(i; indexes)
1618 				ds.setVariant(i, value);
1619 		}
1620 	}
1621 }
1622 
1623 class ParsedQuery {
1624 	private string _hql;
1625 	private string _sql;
1626 	private int[][string]params; // contains 1-based indexes of ? ? ? placeholders in SQL for param by name
1627 	private int paramIndex = 1;
1628     private FromClause _from;
1629     private SelectClauseItem[] _select;
1630     private EntityInfo _entity;
1631 	private int _colCount = 0;
1632 	this(string hql) {
1633 		_hql = hql;
1634 	}
1635 	@property string hql() { return _hql; }
1636 	@property string sql() { return _sql; }
1637 	@property const(EntityInfo)entity() { return _entity; }
1638 	@property int colCount() { return _colCount; }
1639     @property FromClause from() { return _from; }
1640     @property SelectClauseItem[] select() { return _select; }
1641     void setEntity(const EntityInfo entity) {
1642         _entity = cast(EntityInfo)entity;
1643 	}
1644     void setFromClause(FromClause from) {
1645         _from = from;
1646     }
1647     void setSelect(SelectClauseItem[] items) {
1648         _select = items; 
1649     }
1650     void setColCount(int cnt) { _colCount = cnt; }
1651 	void addParam(string paramName) {
1652 		if ((paramName in params) is null) {
1653 			params[paramName] = [paramIndex++];
1654 		} else {
1655 			params[paramName] ~= [paramIndex++];
1656 		}
1657 	}
1658 	int[] getParam(string paramName) {
1659 		if ((paramName in params) is null) {
1660 			throw new HibernatedException("Parameter " ~ paramName ~ " not found in query " ~ _hql);
1661 		} else {
1662 			return params[paramName];
1663 		}
1664 	}
1665 	void appendSQL(string sql) {
1666 		_sql ~= sql;
1667 	}
1668 	void appendSpace() {
1669 		if (_sql.length > 0 && _sql[$ - 1] != ' ')
1670 			_sql ~= ' ';
1671 	}
1672 	ParameterValues createParams() {
1673 		return new ParameterValues(params);
1674 	}
1675 }
1676 
1677 unittest {
1678 	ParsedQuery q = new ParsedQuery("FROM User where id = :param1 or id = :param2");
1679 	q.addParam("param1"); // 1
1680 	q.addParam("param2"); // 2
1681 	q.addParam("param1"); // 3
1682 	q.addParam("param1"); // 4
1683 	q.addParam("param3"); // 5
1684 	q.addParam("param2"); // 6
1685 	assert(q.getParam("param1") == [1,3,4]);
1686 	assert(q.getParam("param2") == [2,6]);
1687 	assert(q.getParam("param3") == [5]);
1688 }
1689 
1690 unittest {
1691 
1692 	//trace("query unittest");
1693     import hibernated.tests;
1694 
1695     EntityMetaData schema = new SchemaInfoImpl!(User, Customer, AccountType, Address, Person, MoreInfo, EvenMoreInfo, Role);
1696 	QueryParser parser = new QueryParser(schema, "SELECT a FROM User AS a WHERE id = :Id AND name != :skipName OR name IS NULL  AND a.flags IS NOT NULL ORDER BY name, a.flags DESC");
1697 	assert(parser.parameterNames.length == 2);
1698 	//trace("param1=" ~ parser.parameterNames[0]);
1699 	//trace("param2=" ~ parser.parameterNames[1]);
1700 	assert(parser.parameterNames[0] == "Id");
1701 	assert(parser.parameterNames[1] == "skipName");
1702 	assert(parser.fromClause.length == 1);
1703 	assert(parser.fromClause.first.entity.name == "User");
1704     assert(parser.fromClause.first.entityAlias == "a");
1705 	assert(parser.selectClause.length == 1);
1706 	assert(parser.selectClause[0].prop is null);
1707 	assert(parser.selectClause[0].from.entity.name == "User");
1708 	assert(parser.orderByClause.length == 2);
1709 	assert(parser.orderByClause[0].prop.propertyName == "name");
1710 	assert(parser.orderByClause[0].from.entity.name == "User");
1711 	assert(parser.orderByClause[0].asc == true);
1712 	assert(parser.orderByClause[1].prop.propertyName == "flags");
1713 	assert(parser.orderByClause[1].from.entity.name == "User");
1714 	assert(parser.orderByClause[1].asc == false);
1715 	
1716 	parser = new QueryParser(schema, "SELECT a FROM User AS a WHERE ((id = :Id) OR (name LIKE 'a%' AND flags = (-5 + 7))) AND name != :skipName AND flags BETWEEN 2*2 AND 42/5 ORDER BY name, a.flags DESC");
1717 	assert(parser.whereClause !is null);
1718 	//trace(parser.whereClause.dump(0));
1719 	Dialect dialect = new MySQLDialect();
1720 	
1721 	assert(dialect.quoteSqlString("abc") == "'abc'");
1722 	assert(dialect.quoteSqlString("a'b'c") == "'a\\'b\\'c'");
1723 	assert(dialect.quoteSqlString("a\nc") == "'a\\nc'");
1724 	
1725 	parser = new QueryParser(schema, "FROM User AS u WHERE id = :Id and u.name like '%test%'");
1726 	ParsedQuery q = parser.makeSQL(dialect);
1727 	//trace(parser.whereClause.dump(0));
1728 	//trace(q.hql ~ "\n=>\n" ~ q.sql);
1729 
1730 	//trace(q.hql);
1731 	//trace(q.sql);
1732     parser = new QueryParser(schema, "SELECT a FROM Person AS a LEFT JOIN a.moreInfo as b LEFT JOIN b.evenMore c WHERE a.id = :Id AND b.flags > 0 AND c.flags > 0");
1733     assert(parser.fromClause.hasAlias("a"));
1734     assert(parser.fromClause.hasAlias("b"));
1735     assert(parser.fromClause.findByAlias("a").entityName == "Person");
1736     assert(parser.fromClause.findByAlias("b").entityName == "MoreInfo");
1737     assert(parser.fromClause.findByAlias("b").joinType == JoinType.LeftJoin);
1738     assert(parser.fromClause.findByAlias("c").entityName == "EvenMoreInfo");
1739     // indirect JOIN
1740     parser = new QueryParser(schema, "SELECT a FROM Person a WHERE a.id = :Id AND a.moreInfo.evenMore.flags > 0");
1741     assert(parser.fromClause.hasAlias("a"));
1742     assert(parser.fromClause.length == 3);
1743     assert(parser.fromClause[0].entity.tableName == "person");
1744     assert(parser.fromClause[1].entity.tableName == "person_info");
1745     assert(parser.fromClause[1].joinType == JoinType.InnerJoin);
1746     assert(parser.fromClause[1].pathString == "a.moreInfo");
1747     assert(parser.fromClause[2].entity.tableName == "person_info2");
1748     assert(parser.fromClause[2].joinType == JoinType.LeftJoin);
1749     assert(parser.fromClause[2].pathString == "a.moreInfo.evenMore");
1750     // indirect JOIN, no alias
1751     parser = new QueryParser(schema, "FROM Person WHERE id = :Id AND moreInfo.evenMore.flags > 0");
1752     assert(parser.fromClause.length == 3);
1753     assert(parser.fromClause[0].entity.tableName == "person");
1754     assert(parser.fromClause[0].fetch == true);
1755     //trace("select fields [" ~ to!string(parser.fromClause[0].startColumn) ~ ", " ~ to!string(parser.fromClause[0].selectedColumns) ~ "]");
1756     //trace("select fields [" ~ to!string(parser.fromClause[1].startColumn) ~ ", " ~ to!string(parser.fromClause[1].selectedColumns) ~ "]");
1757     //trace("select fields [" ~ to!string(parser.fromClause[2].startColumn) ~ ", " ~ to!string(parser.fromClause[2].selectedColumns) ~ "]");
1758     assert(parser.fromClause[0].selectedColumns == 4);
1759     assert(parser.fromClause[1].entity.tableName == "person_info");
1760     assert(parser.fromClause[1].joinType == JoinType.InnerJoin);
1761     assert(parser.fromClause[1].pathString == "_a1.moreInfo");
1762     assert(parser.fromClause[1].fetch == true);
1763     assert(parser.fromClause[1].selectedColumns == 2);
1764     assert(parser.fromClause[2].entity.tableName == "person_info2");
1765     assert(parser.fromClause[2].joinType == JoinType.LeftJoin);
1766     assert(parser.fromClause[2].pathString == "_a1.moreInfo.evenMore");
1767     assert(parser.fromClause[2].fetch == true);
1768     assert(parser.fromClause[2].selectedColumns == 3);
1769 
1770     q = parser.makeSQL(dialect);
1771     //trace(q.hql);
1772     //trace(q.sql);
1773 
1774     parser = new QueryParser(schema, "FROM User WHERE id in (1, 2, (3 - 1 * 25) / 2, 4 + :Id, 5)");
1775     //trace(parser.whereClause.dump(0));
1776     q = parser.makeSQL(dialect);
1777     //trace(q.hql);
1778     //trace(q.sql);
1779 
1780     parser = new QueryParser(schema, "FROM Customer WHERE users.id = 1");
1781     q = parser.makeSQL(dialect);
1782 //    trace(q.hql);
1783 //    trace(q.sql);
1784     assert(q.sql == "SELECT _t1.id, _t1.name, _t1.zip, _t1.city, _t1.street_address, _t1.account_type_fk FROM customers AS _t1 LEFT JOIN users AS _t2 ON _t1.id=_t2.customer_fk WHERE _t2.id = 1");
1785 
1786     parser = new QueryParser(schema, "FROM Customer WHERE id = 1");
1787     q = parser.makeSQL(dialect);
1788 //    trace(q.hql);
1789 //    trace(q.sql);
1790     assert(q.sql == "SELECT _t1.id, _t1.name, _t1.zip, _t1.city, _t1.street_address, _t1.account_type_fk FROM customers AS _t1 WHERE _t1.id = 1");
1791 
1792     parser = new QueryParser(schema, "FROM User WHERE roles.id = 1");
1793     q = parser.makeSQL(dialect);
1794     //trace(q.hql);
1795     //trace(q.sql);
1796     assert(q.sql == "SELECT _t1.id, _t1.name, _t1.flags, _t1.comment, _t1.customer_fk FROM users AS _t1 LEFT JOIN role_users AS _t1_t2 ON _t1.id=_t1_t2.user_fk LEFT JOIN role AS _t2 ON _t1_t2.role_fk=_t2.id WHERE _t2.id = 1");
1797 
1798     parser = new QueryParser(schema, "FROM Role WHERE users.id = 1");
1799     q = parser.makeSQL(dialect);
1800 //    trace(q.hql);
1801 //    trace(q.sql);
1802     assert(q.sql == "SELECT _t1.id, _t1.name FROM role AS _t1 LEFT JOIN role_users AS _t1_t2 ON _t1.id=_t1_t2.role_fk LEFT JOIN users AS _t2 ON _t1_t2.user_fk=_t2.id WHERE _t2.id = 1");
1803     
1804     parser = new QueryParser(schema, "FROM User WHERE customer.id = 1");
1805     q = parser.makeSQL(dialect);
1806 //    trace(q.hql);
1807 //    trace(q.sql);
1808     assert(q.sql == "SELECT _t1.id, _t1.name, _t1.flags, _t1.comment, _t1.customer_fk FROM users AS _t1 LEFT JOIN customers AS _t2 ON _t1.customer_fk=_t2.id WHERE _t2.id = 1");
1809 
1810     parser = new QueryParser(schema, "SELECT a2 FROM User AS a1 JOIN a1.roles AS a2 WHERE a1.id = 1");
1811     q = parser.makeSQL(dialect);
1812     //trace(q.hql);
1813     //trace(q.sql);
1814     assert(q.sql == "SELECT _t2.id, _t2.name FROM users AS _t1 INNER JOIN role_users AS _t1_t2 ON _t1.id=_t1_t2.user_fk INNER JOIN role AS _t2 ON _t1_t2.role_fk=_t2.id WHERE _t1.id = 1");
1815 
1816     parser = new QueryParser(schema, "SELECT a2 FROM Customer AS a1 JOIN a1.users AS a2 WHERE a1.id = 1");
1817     q = parser.makeSQL(dialect);
1818     //trace(q.hql);
1819     //trace(q.sql);
1820     assert(q.sql == "SELECT _t2.id, _t2.name, _t2.flags, _t2.comment, _t2.customer_fk FROM customers AS _t1 INNER JOIN users AS _t2 ON _t1.id=_t2.customer_fk WHERE _t1.id = 1");
1821     
1822 }