/**
 * DStruct - Object-Relation Mapping for D programming language, with interface similar to Hibernate. 
 * 
 * Hibernate documentation can be found here:
 * $(LINK http://hibernate.org/docs)$(BR)
 * 
 * Source file dstruct/core.d.
 *
 * This module contains HQL query parser and HQL to SQL transform implementation.
 * 
 * Copyright: Copyright 2013
 * License:   $(LINK www.boost.org/LICENSE_1_0.txt, Boost License 1.0).
 * Author:   Vadim Lopatin
 */
module dstruct.query;

import std.ascii;
import std.algorithm;
import std.exception;
import std.array;
import std..string;
import std.conv;
import std.stdio;
import std.variant;

import dstruct.ddbc.core;

import dstruct.annotations;
import dstruct.metadata;
import dstruct.type;
import dstruct.core;
import dstruct.dialect;
import dstruct.dialects.mysqldialect;

// For backwards compatibily
// 'enforceEx' will be removed with 2.089
static if (__VERSION__ < 2080)
{
	alias enforceHelper = enforceEx;
}
else
{
	alias enforceHelper = enforce;
}

enum JoinType
{
	InnerJoin,
	LeftJoin,
}

class FromClauseItem
{
	string entityName;
	const EntityInfo entity;
	string entityAlias;
	string sqlAlias;
	int startColumn;
	int selectedColumns;
	// for JOINs
	JoinType joinType = JoinType.InnerJoin;
	bool fetch;
	FromClauseItem base;
	const PropertyInfo baseProperty;
	string pathString;
	int index;
	int selectIndex;

	string getFullPath()
	{
		if (base is null)
			return entityAlias;
		return base.getFullPath() ~ "." ~ baseProperty.propertyName;
	}

	this(const EntityInfo entity, string entityAlias, JoinType joinType, bool fetch,
			FromClauseItem base = null, const PropertyInfo baseProperty = null)
	{
		this.entityName = entity.name;
		this.entity = entity;
		this.joinType = joinType;
		this.fetch = fetch;
		this.base = base;
		this.baseProperty = baseProperty;
		this.selectIndex = -1;
	}

}

class FromClause
{
	FromClauseItem[] items;
	FromClauseItem add(const EntityInfo entity, string entityAlias, JoinType joinType,
			bool fetch, FromClauseItem base = null, const PropertyInfo baseProperty = null)
	{
		FromClauseItem item = new FromClauseItem(entity, entityAlias, joinType,
				fetch, base, baseProperty);
		item.entityAlias = entityAlias is null ? "_a" ~ to!string(items.length + 1) : entityAlias;
		item.sqlAlias = "_t" ~ to!string(items.length + 1);
		item.index = cast(int) items.length;
		item.pathString = item.getFullPath();
		items ~= item;
		return item;
	}

	@property size_t length()
	{
		return items.length;
	}

	string getSQL()
	{
		return "";
	}

	@property FromClauseItem first()
	{
		return items[0];
	}

	FromClauseItem opIndex(int index)
	{
		enforceHelper!DStructException(index >= 0 && index < items.length,
				"FromClause index out of range: " ~ to!string(index));
		return items[index];
	}

	FromClauseItem opIndex(string aliasName)
	{
		return findByAlias(aliasName);
	}

	bool hasAlias(string aliasName)
	{
		foreach (ref m; items)
		{
			if (m.entityAlias == aliasName)
				return true;
		}
		return false;
	}

	FromClauseItem findByAlias(string aliasName)
	{
		foreach (ref m; items)
		{
			if (m.entityAlias == aliasName)
				return m;
		}
		throw new QuerySyntaxException("Cannot find FROM alias by name " ~ aliasName);
	}

	FromClauseItem findByPath(string path)
	{
		foreach (ref m; items)
		{
			if (m.pathString == path)
				return m;
		}
		return null;
	}
}

struct OrderByClauseItem
{
	FromClauseItem from;
	PropertyInfo prop;
	bool asc;
}

struct SelectClauseItem
{
	FromClauseItem from;
	PropertyInfo prop;
}

class QueryParser
{
	string query;
	EntityMetaData metadata;
	Token[] tokens;
	FromClause fromClause;
	//FromClauseItem[] fromClause;
	string[] parameterNames;
	OrderByClauseItem[] orderByClause;
	SelectClauseItem[] selectClause;
	Token whereClause; // AST for WHERE expression

	this(EntityMetaData metadata, string query)
	{
		this.metadata = metadata;
		this.query = query;
		fromClause = new FromClause();
		//writeln("tokenizing query: " ~ query);
		tokens = tokenize(query);
		//writeln("parsing query: " ~ query);
		parse();
		//writeln("parsing done");
	}

	void parse()
	{
		processParameterNames(0, cast(int) tokens.length); // replace pairs {: Ident} with single Parameter token
		int len = cast(int) tokens.length;
		//writeln("Query tokens: " ~ to!string(len));
		int fromPos = findKeyword(KeywordType.FROM);
		int selectPos = findKeyword(KeywordType.SELECT);
		int wherePos = findKeyword(KeywordType.WHERE);
		int orderPos = findKeyword(KeywordType.ORDER);
		enforceHelper!QuerySyntaxException(fromPos >= 0, "No FROM clause in query " ~ query);
		enforceHelper!QuerySyntaxException(selectPos <= 0,
				"SELECT clause should be first - invalid query " ~ query);
		enforceHelper!QuerySyntaxException(wherePos == -1 || wherePos > fromPos,
				"Invalid WHERE position in query " ~ query);
		enforceHelper!QuerySyntaxException(orderPos == -1 || (orderPos < tokens.length - 2
				&& tokens[orderPos + 1].keyword == KeywordType.BY),
				"Invalid ORDER BY in query " ~ query);
		enforceHelper!QuerySyntaxException(orderPos == -1 || orderPos > fromPos,
				"Invalid ORDER BY position in query " ~ query);
		int fromEnd = len;
		if (orderPos >= 0)
			fromEnd = orderPos;
		if (wherePos >= 0)
			fromEnd = wherePos;
		int whereEnd = wherePos < 0 ? -1 : (orderPos >= 0 ? orderPos : len);
		int orderEnd = orderPos < 0 ? -1 : len;
		parseFromClause(fromPos + 1, fromEnd);
		if (selectPos == 0 && selectPos < fromPos - 1)
			parseSelectClause(selectPos + 1, fromPos);
		else
			defaultSelectClause();
		bool selectedEntities = validateSelectClause();
		if (wherePos >= 0 && whereEnd > wherePos)
			parseWhereClause(wherePos + 1, whereEnd);
		if (orderPos >= 0 && orderEnd > orderPos)
			parseOrderClause(orderPos + 2, orderEnd);
		if (selectedEntities)
		{
			processAutoFetchReferences();
			prepareSelectFields();
		}
	}

	private void prepareSelectFields()
	{
		int startColumn = 1;
		for (int i = 0; i < fromClause.length; i++)
		{
			FromClauseItem item = fromClause[i];
			if (!item.fetch)
				continue;
			int count = item.entity.metadata.getFieldCount(item.entity, false);
			if (count > 0)
			{
				item.startColumn = startColumn;
				item.selectedColumns = count;
				startColumn += count;
			}
		}
	}

	private void processAutoFetchReferences()
	{
		FromClauseItem a = selectClause[0].from;
		a.fetch = true;
		processAutoFetchReferences(a);
	}

	private FromClauseItem ensureItemFetched(FromClauseItem a, const PropertyInfo p)
	{
		FromClauseItem res;
		string path = a.pathString ~ "." ~ p.propertyName;
		//writeln("ensureItemFetched " ~ path);
		res = fromClause.findByPath(path);
		if (res is null)
		{
			// autoadd join
			assert(p.referencedEntity !is null);
			res = fromClause.add(p.referencedEntity, null, p.nullable
					? JoinType.LeftJoin : JoinType.InnerJoin, true, a, p);
		}
		else
		{
			// force fetch
			res.fetch = true;
		}
		bool selectFound = false;
		foreach (s; selectClause)
		{
			if (s.from == res)
			{
				selectFound = true;
				break;
			}
		}
		if (!selectFound)
		{
			SelectClauseItem item;
			item.from = res;
			item.prop = null;
			selectClause ~= item;
		}
		return res;
	}

	private bool isBackReferenceProperty(FromClauseItem a, const PropertyInfo p)
	{
		if (a.base is null)
			return false;
		auto baseEntity = a.base.entity;
		assert(baseEntity !is null);
		if (p.referencedEntity != baseEntity)
			return false;

		if (p.referencedProperty !is null && p.referencedProperty == a.baseProperty)
			return true;
		if (a.baseProperty.referencedProperty !is null && p == a.baseProperty.referencedProperty)
			return true;
		return false;
	}

	private void processAutoFetchReferences(FromClauseItem a)
	{
		foreach (p; a.entity.properties)
		{
			if (p.lazyLoad)
				continue;
			if (p.oneToOne && !isBackReferenceProperty(a, p))
			{
				FromClauseItem res = ensureItemFetched(a, p);
				processAutoFetchReferences(res);
			}
		}
	}

	private void updateEntity(const EntityInfo entity, string name)
	{
		foreach (t; tokens)
		{
			if (t.type == TokenType.Ident && t.text == name)
			{
				t.entity = cast(EntityInfo) entity;
				t.type = TokenType.Entity;
			}
		}
	}

	private void updateAlias(const EntityInfo entity, string name)
	{
		foreach (t; tokens)
		{
			if (t.type == TokenType.Ident && t.text == name)
			{
				t.entity = cast(EntityInfo) entity;
				t.type = TokenType.Alias;
			}
		}
	}

	private void splitCommaDelimitedList(int start, int end, void delegate(int, int) callback)
	{
		//writeln("SPLIT " ~ to!string(start) ~ " .. " ~ to!string(end));
		int len = cast(int) tokens.length;
		int p = start;
		for (int i = start; i < end; i++)
		{
			if (tokens[i].type == TokenType.Comma || i == end - 1)
			{
				enforceHelper!QuerySyntaxException(tokens[i].type != TokenType.Comma
						|| i != end - 1,
						"Invalid comma at end of list" ~ errorContext(tokens[start]));
				int endp = i < end - 1 ? i : end;
				enforceHelper!QuerySyntaxException(endp > p,
						"Invalid comma delimited list" ~ errorContext(tokens[start]));
				callback(p, endp);
				p = i + 1;
			}
		}
	}

	private int parseFieldRef(int start, int end, ref string[] path)
	{
		int pos = start;
		while (pos < end)
		{
			if (tokens[pos].type == TokenType.Ident || tokens[pos].type == TokenType.Alias)
			{
				enforceHelper!QuerySyntaxException(path.length == 0 || tokens[pos].type != TokenType.Alias,
						"Alias is allowed only as first item" ~ errorContext(tokens[pos]));
				path ~= tokens[pos].text;
				pos++;
				if (pos == end || tokens[pos].type != TokenType.Dot)
					return pos;
				if (pos == end - 1 || tokens[pos + 1].type != TokenType.Ident)
					return pos;
				pos++;
			}
			else
			{
				break;
			}
		}
		enforceHelper!QuerySyntaxException(tokens[pos].type != TokenType.Dot,
				"Unexpected dot at end in field list" ~ errorContext(tokens[pos]));
		enforceHelper!QuerySyntaxException(path.length > 0,
				"Empty field list" ~ errorContext(tokens[pos]));
		return pos;
	}

	private void parseFirstFromClause(int start, int end, out int pos)
	{
		enforceHelper!QuerySyntaxException(start < end,
				"Invalid FROM clause " ~ errorContext(tokens[start]));
		// minimal support:
		//    Entity
		//    Entity alias
		//    Entity AS alias
		enforceHelper!QuerySyntaxException(tokens[start].type == TokenType.Ident,
				"Entity name identifier expected in FROM clause" ~ errorContext(tokens[start]));
		string entityName = cast(string) tokens[start].text;
		auto ei = metadata.findEntity(entityName);
		updateEntity(ei, entityName);
		string aliasName = null;
		int p = start + 1;
		if (p < end && tokens[p].type == TokenType.Keyword && tokens[p].keyword == KeywordType.AS)
			p++;
		if (p < end)
		{
			enforceHelper!QuerySyntaxException(tokens[p].type == TokenType.Ident,
					"Alias name identifier expected in FROM clause" ~ errorContext(tokens[p]));
			aliasName = cast(string) tokens[p].text;
			p++;
		}
		if (aliasName != null)
			updateAlias(ei, aliasName);
		fromClause.add(ei, aliasName, JoinType.InnerJoin, false);
		pos = p;
	}

	void appendFromClause(Token context, string[] path, string aliasName,
			JoinType joinType, bool fetch)
	{
		int p = 0;
		enforceHelper!QuerySyntaxException(fromClause.hasAlias(path[p]),
				"Unknown alias " ~ path[p] ~ " in FROM clause" ~ errorContext(context));
		FromClauseItem baseClause = findFromClauseByAlias(path[p]);
		//string pathString = path[p];
		p++;
		while (true)
		{
			auto baseEntity = baseClause.entity;
			enforceHelper!QuerySyntaxException(p < path.length,
					"Property name expected in FROM clause" ~ errorContext(context));
			string propertyName = path[p++];
			auto property = baseEntity[propertyName];
			auto referencedEntity = property.referencedEntity;
			assert(referencedEntity !is null);
			enforceHelper!QuerySyntaxException(!property.simple,
					"Simple property " ~ propertyName ~ " cannot be used in JOIN" ~ errorContext(
						context));
			enforceHelper!QuerySyntaxException(!property.embedded,
					"Embedded property " ~ propertyName ~ " cannot be used in JOIN" ~ errorContext(
						context));
			bool last = (p == path.length);
			FromClauseItem item = fromClause.add(referencedEntity, last
					? aliasName : null, joinType, fetch, baseClause, property);
			if (last && aliasName !is null)
				updateAlias(referencedEntity, item.entityAlias);
			baseClause = item;
			if (last)
				break;
		}
	}

	void parseFromClause(int start, int end)
	{
		int p = start;
		parseFirstFromClause(start, end, p);
		while (p < end)
		{
			Token context = tokens[p];
			JoinType joinType = JoinType.InnerJoin;
			if (tokens[p].keyword == KeywordType.LEFT)
			{
				joinType = JoinType.LeftJoin;
				p++;
			}
			else if (tokens[p].keyword == KeywordType.INNER)
			{
				p++;
			}
			enforceHelper!QuerySyntaxException(p < end && tokens[p].keyword == KeywordType.JOIN,
					"Invalid FROM clause" ~ errorContext(tokens[p]));
			p++;
			enforceHelper!QuerySyntaxException(p < end,
					"Invalid FROM clause - incomplete JOIN" ~ errorContext(tokens[p]));
			bool fetch = false;
			if (tokens[p].keyword == KeywordType.FETCH)
			{
				fetch = true;
				p++;
				enforceHelper!QuerySyntaxException(p < end,
						"Invalid FROM clause - incomplete JOIN" ~ errorContext(tokens[p]));
			}
			string[] path;
			p = parseFieldRef(p, end, path);
			string aliasName;
			bool hasAS = false;
			if (p < end && tokens[p].keyword == KeywordType.AS)
			{
				p++;
				hasAS = true;
			}
			enforceHelper!QuerySyntaxException(p < end && tokens[p].type == TokenType.Ident,
					"Invalid FROM clause - no alias in JOIN" ~ errorContext(tokens[p]));
			aliasName = tokens[p].text;
			p++;
			appendFromClause(context, path, aliasName, joinType, fetch);
		}
		enforceHelper!QuerySyntaxException(p == end, "Invalid FROM clause" ~ errorContext(tokens[p]));
	}

	// in pairs {: Ident} replace type of ident with Parameter 
	void processParameterNames(int start, int end)
	{
		for (int i = start; i < end; i++)
		{
			if (tokens[i].type == TokenType.Parameter)
			{
				parameterNames ~= cast(string) tokens[i].text;
			}
		}
	}

	FromClauseItem findFromClauseByAlias(string aliasName)
	{
		return fromClause.findByAlias(aliasName);
	}

	void addSelectClauseItem(string aliasName, string[] propertyNames)
	{
		//writeln("addSelectClauseItem alias=" ~ aliasName ~ " properties=" ~ to!string(propertyNames));
		FromClauseItem from = aliasName == null ? fromClause.first
			: findFromClauseByAlias(aliasName);
		SelectClauseItem item;
		item.from = from;
		item.prop = null;
		EntityInfo ei = cast(EntityInfo) from.entity;
		if (propertyNames.length > 0)
		{
			item.prop = cast(PropertyInfo) ei.findProperty(propertyNames[0]);
			propertyNames.popFront();
			while (item.prop.embedded)
			{
				//writeln("Embedded property " ~ item.prop.propertyName ~ " of type " ~ item.prop.referencedEntityName);
				ei = cast(EntityInfo) item.prop.referencedEntity;
				enforceHelper!QuerySyntaxException(propertyNames.length > 0,
						"@Embedded field property name should be specified when selecting "
						~ aliasName ~ "." ~ item.prop.propertyName);
				item.prop = cast(PropertyInfo) ei.findProperty(propertyNames[0]);
				propertyNames.popFront();
			}
		}
		enforceHelper!QuerySyntaxException(propertyNames.length == 0,
				"Extra field names in SELECT clause in query " ~ query);
		selectClause ~= item;
		//insertInPlace(selectClause, 0, item);
	}

	void addOrderByClauseItem(string aliasName, string propertyName, bool asc)
	{
		FromClauseItem from = aliasName == null ? fromClause.first
			: findFromClauseByAlias(aliasName);
		OrderByClauseItem item;
		item.from = from;
		item.prop = cast(PropertyInfo) from.entity.findProperty(propertyName);
		item.asc = asc;
		orderByClause ~= item;
		//insertInPlace(orderByClause, 0, item);
	}

	void parseOrderByClauseItem(int start, int end)
	{
		// for each comma delimited item
		// in current version it can only be
		// {property}  or  {alias . property} optionally followed by ASC or DESC
		//writeln("ORDER BY ITEM: " ~ to!string(start) ~ " .. " ~ to!string(end));
		bool asc = true;
		if (tokens[end - 1].type == TokenType.Keyword && tokens[end - 1].keyword == KeywordType.ASC)
		{
			end--;
		}
		else if (tokens[end - 1].type == TokenType.Keyword
				&& tokens[end - 1].keyword == KeywordType.DESC)
		{
			asc = false;
			end--;
		}
		enforceHelper!QuerySyntaxException(start < end,
				"Empty ORDER BY clause item" ~ errorContext(tokens[start]));
		if (start == end - 1)
		{
			// no alias
			enforceHelper!QuerySyntaxException(tokens[start].type == TokenType.Ident,
					"Property name expected in ORDER BY clause" ~ errorContext(tokens[start]));
			addOrderByClauseItem(null, cast(string) tokens[start].text, asc);
		}
		else if (start == end - 3)
		{
			enforceHelper!QuerySyntaxException(tokens[start].type == TokenType.Alias,
					"Entity alias expected in ORDER BY clause" ~ errorContext(tokens[start]));
			enforceHelper!QuerySyntaxException(tokens[start + 1].type == TokenType.Dot,
					"Dot expected after entity alias in ORDER BY clause" ~ errorContext(
						tokens[start]));
			enforceHelper!QuerySyntaxException(tokens[start + 2].type == TokenType.Ident,
					"Property name expected after entity alias in ORDER BY clause" ~ errorContext(
						tokens[start]));
			addOrderByClauseItem(cast(string) tokens[start].text,
					cast(string) tokens[start + 2].text, asc);
		}
		else
		{
			//writeln("range: " ~ to!string(start) ~ " .. " ~ to!string(end));
			enforceHelper!QuerySyntaxException(false,
					"Invalid ORDER BY clause (expected {property [ASC | DESC]} or {alias.property [ASC | DESC]} )"
					~ errorContext(tokens[start]));
		}
	}

	void parseSelectClauseItem(int start, int end)
	{
		// for each comma delimited item
		// in current version it can only be
		// {property}  or  {alias . property}
		//writeln("SELECT ITEM: " ~ to!string(start) ~ " .. " ~ to!string(end));
		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]));
		string aliasName;
		int p = start;
		if (tokens[p].type == TokenType.Alias)
		{
			//writeln("select clause alias: " ~ tokens[p].text ~ " query: " ~ query);
			aliasName = cast(string) tokens[p].text;
			p++;
			enforceHelper!QuerySyntaxException(p == end || tokens[p].type == TokenType.Dot,
					"SELECT clause item is invalid (only  [alias.]field{[.field2]}+ allowed) " ~ errorContext(
						tokens[start]));
			if (p < end - 1 && tokens[p].type == TokenType.Dot)
				p++;
		}
		else
		{
			//writeln("select clause non-alias: " ~ tokens[p].text ~ " query: " ~ query);
		}
		string[] fieldNames;
		while (p < end && tokens[p].type == TokenType.Ident)
		{
			fieldNames ~= tokens[p].text;
			p++;
			if (p > end - 1 || tokens[p].type != TokenType.Dot)
				break;
			// skipping dot
			p++;
		}
		//writeln("parseSelectClauseItem pos=" ~ to!string(p) ~ " end=" ~ to!string(end));
		enforceHelper!QuerySyntaxException(p >= end,
				"SELECT clause item is invalid (only  [alias.]field{[.field2]}+ allowed) " ~ errorContext(
					tokens[start]));
		addSelectClauseItem(aliasName, fieldNames);
	}

	void parseSelectClause(int start, int end)
	{
		enforceHelper!QuerySyntaxException(start < end,
				"Invalid SELECT clause" ~ errorContext(tokens[start]));
		splitCommaDelimitedList(start, end, &parseSelectClauseItem);
	}

	void defaultSelectClause()
	{
		addSelectClauseItem(fromClause.first.entityAlias, null);
	}

	bool validateSelectClause()
	{
		enforceHelper!QuerySyntaxException(selectClause != null
				&& selectClause.length > 0, "Invalid SELECT clause");
		int aliasCount = 0;
		int fieldCount = 0;
		foreach (a; selectClause)
		{
			if (a.prop !is null)
				fieldCount++;
			else
				aliasCount++;
		}
		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");
		return aliasCount > 0;
	}

	void parseWhereClause(int start, int end)
	{
		enforceHelper!QuerySyntaxException(start < end,
				"Invalid WHERE clause" ~ errorContext(tokens[start]));
		whereClause = new Token(tokens[start].pos, TokenType.Expression, tokens, start, end);
		//writeln("before convert fields:\n" ~ whereClause.dump(0));
		convertFields(whereClause.children);
		//writeln("after convertFields before convertIsNullIsNotNull:\n" ~ whereClause.dump(0));
		convertIsNullIsNotNull(whereClause.children);
		//writeln("after convertIsNullIsNotNull\n" ~ whereClause.dump(0));
		convertUnaryPlusMinus(whereClause.children);
		//writeln("after convertUnaryPlusMinus\n" ~ whereClause.dump(0));
		foldBraces(whereClause.children);
		//writeln("after foldBraces\n" ~ whereClause.dump(0));
		foldOperators(whereClause.children);
		//writeln("after foldOperators\n" ~ whereClause.dump(0));
		dropBraces(whereClause.children);
		//writeln("after dropBraces\n" ~ whereClause.dump(0));
	}

	void foldBraces(ref Token[] items)
	{
		while (true)
		{
			if (items.length == 0)
				return;
			int lastOpen = -1;
			int firstClose = -1;
			for (int i = 0; i < items.length; i++)
			{
				if (items[i].type == TokenType.OpenBracket)
				{
					lastOpen = i;
				}
				if (items[i].type == TokenType.CloseBracket)
				{
					firstClose = i;
					break;
				}
			}
			if (lastOpen == -1 && firstClose == -1)
				return;
			//writeln("folding braces " ~ to!string(lastOpen) ~ " .. " ~ to!string(firstClose));
			enforceHelper!QuerySyntaxException(lastOpen >= 0 && lastOpen < firstClose,
					"Unpaired braces in WHERE clause" ~ errorContext(tokens[lastOpen]));
			Token folded = new Token(items[lastOpen].pos, TokenType.Braces,
					items, lastOpen + 1, firstClose);
			//			size_t oldlen = items.length;
			//			int removed = firstClose - lastOpen;
			replaceInPlace(items, lastOpen, firstClose + 1, [folded]);
			//			assert(items.length == oldlen - removed);
			foldBraces(folded.children);
		}
	}

	static void dropBraces(ref Token[] items)
	{
		foreach (t; items)
		{
			if (t.children.length > 0)
				dropBraces(t.children);
		}
		for (int i = 0; i < items.length; i++)
		{
			if (items[i].type != TokenType.Braces)
				continue;
			if (items[i].children.length == 1)
			{
				Token t = items[i].children[0];
				replaceInPlace(items, i, i + 1, [t]);
			}
		}
	}

	void convertIsNullIsNotNull(ref Token[] items)
	{
		for (int i = cast(int) items.length - 2; i >= 0; i--)
		{
			if (items[i].type != TokenType.Operator || items[i + 1].type != TokenType.Keyword)
				continue;
			if (items[i].operator == OperatorType.IS && items[i + 1].keyword == KeywordType.NULL)
			{
				Token folded = new Token(items[i].pos, OperatorType.IS_NULL, "IS NULL");
				replaceInPlace(items, i, i + 2, [folded]);
				i -= 2;
			}
		}
		for (int i = cast(int) items.length - 3; i >= 0; i--)
		{
			if (items[i].type != TokenType.Operator
					|| items[i + 1].type != TokenType.Operator
					|| items[i + 2].type != TokenType.Keyword)
				continue;
			if (items[i].operator == OperatorType.IS
					&& items[i + 1].operator == OperatorType.NOT
					&& items[i + 2].keyword == KeywordType.NULL)
			{
				Token folded = new Token(items[i].pos, OperatorType.IS_NOT_NULL, "IS NOT NULL");
				replaceInPlace(items, i, i + 3, [folded]);
				i -= 3;
			}
		}
	}

	void convertFields(ref Token[] items)
	{
		while (true)
		{
			int p = -1;
			for (int i = 0; i < items.length; i++)
			{
				if (items[i].type != TokenType.Ident && items[i].type != TokenType.Alias)
					continue;
				p = i;
				break;
			}
			if (p == -1)
				return;
			// found identifier at position p
			string[] idents;
			int lastp = p;
			idents ~= items[p].text;
			for (int i = p + 1; i < items.length - 1; i += 2)
			{
				if (items[i].type != TokenType.Dot)
					break;
				enforceHelper!QuerySyntaxException(i < items.length - 1 && items[i + 1].type == TokenType.Ident,
						"Syntax error in WHERE condition - no property name after . " ~ errorContext(
							items[p]));
				lastp = i + 1;
				idents ~= items[i + 1].text;
			}
			string fullName;
			FromClauseItem a;
			if (items[p].type == TokenType.Alias)
			{
				a = findFromClauseByAlias(idents[0]);
				idents.popFront();
			}
			else
			{
				// use first FROM clause if alias is not specified
				a = fromClause.first;
			}
			string aliasName = a.entityAlias;
			EntityInfo ei = cast(EntityInfo) a.entity;
			enforceHelper!QuerySyntaxException(idents.length > 0,
					"Syntax error in WHERE condition - alias w/o property name: " ~ aliasName ~ errorContext(
						items[p]));
			PropertyInfo pi;
			fullName = aliasName;
			while (true)
			{
				string propertyName = idents[0];
				idents.popFront();
				fullName ~= "." ~ propertyName;
				pi = cast(PropertyInfo) ei.findProperty(propertyName);
				while (pi.embedded)
				{ // loop to allow nested @Embedded
					enforceHelper!QuerySyntaxException(idents.length > 0, "Syntax error in WHERE condition - @Embedded property reference should include reference to @Embeddable property " ~ aliasName ~ errorContext(
							items[p]));
					propertyName = idents[0];
					idents.popFront();
					pi = cast(PropertyInfo) pi.referencedEntity.findProperty(propertyName);
					fullName = fullName ~ "." ~ propertyName;
				}
				if (idents.length == 0)
					break;
				if (idents.length > 0)
				{
					// more field names
					string pname = idents[0];
					enforceHelper!QuerySyntaxException(pi.referencedEntity !is null,
							"Unexpected extra field name " ~ pname ~ " - property " ~ propertyName
							~ " doesn't content subproperties " ~ errorContext(items[p]));
					ei = cast(EntityInfo) pi.referencedEntity;
					FromClauseItem newClause = fromClause.findByPath(fullName);
					if (newClause is null)
					{
						// autogenerate FROM clause
						newClause = fromClause.add(ei, null, pi.nullable
								? JoinType.LeftJoin : JoinType.InnerJoin, false, a, pi);
					}
					a = newClause;
				}
			}
			enforceHelper!QuerySyntaxException(idents.length == 0,
					"Unexpected extra field name " ~ idents[0] ~ errorContext(items[p]));
			//writeln("full name = " ~ fullName);
			Token t = new Token(items[p].pos, TokenType.Field, fullName);
			t.entity = cast(EntityInfo) ei;
			t.field = cast(PropertyInfo) pi;
			t.from = a;
			replaceInPlace(items, p, lastp + 1, [t]);
		}
	}

	static void convertUnaryPlusMinus(ref Token[] items)
	{
		foreach (t; items)
		{
			if (t.children.length > 0)
				convertUnaryPlusMinus(t.children);
		}
		for (int i = 0; i < items.length; i++)
		{
			if (items[i].type != TokenType.Operator)
				continue;
			OperatorType op = items[i].operator;
			if (op == OperatorType.ADD || op == OperatorType.SUB)
			{
				// convert + and - to unary form if necessary
				if (i == 0 || !items[i - 1].isExpression())
				{
					items[i].operator = (op == OperatorType.ADD)
						? OperatorType.UNARY_PLUS : OperatorType.UNARY_MINUS;
				}
			}
		}
	}

	string errorContext(Token token)
	{
		return " near `" ~ query[token.pos .. $] ~ "` in query `" ~ query ~ "`";
	}

	void foldCommaSeparatedList(Token braces)
	{
		// fold inside braces
		Token[] items = braces.children;
		int start = 0;
		Token[] list;
		for (int i = 0; i <= items.length; i++)
		{
			if (i == items.length || items[i].type == TokenType.Comma)
			{
				enforceHelper!QuerySyntaxException(i > start,
						"Empty item in comma separated list" ~ errorContext(items[i]));
				enforceHelper!QuerySyntaxException(i != items.length - 1,
						"Empty item in comma separated list" ~ errorContext(items[i]));
				Token item = new Token(items[start].pos, TokenType.Expression,
						braces.children, start, i);
				foldOperators(item.children);
				enforceHelper!QuerySyntaxException(item.children.length == 1,
						"Invalid expression in list item" ~ errorContext(items[i]));
				list ~= item.children[0];
				start = i + 1;
			}
		}
		enforceHelper!QuerySyntaxException(list.length > 0, "Empty list" ~ errorContext(items[0]));
		braces.type = TokenType.CommaDelimitedList;
		braces.children = list;
	}

	void foldOperators(ref Token[] items)
	{
		foreach (t; items)
		{
			if (t.children.length > 0)
				foldOperators(t.children);
		}
		while (true)
		{
			//
			int bestOpPosition = -1;
			int bestOpPrecedency = -1;
			OperatorType t = OperatorType.NONE;
			for (int i = 0; i < items.length; i++)
			{
				if (items[i].type != TokenType.Operator)
					continue;
				int p = operatorPrecedency(items[i].operator);
				if (p > bestOpPrecedency)
				{
					bestOpPrecedency = p;
					bestOpPosition = i;
					t = items[i].operator;
				}
			}
			if (bestOpPrecedency == -1)
				return;
			//writeln("Found op " ~ items[bestOpPosition].toString() ~ " at position " ~ to!string(bestOpPosition) ~ " with priority " ~ to!string(bestOpPrecedency));
			if (t == OperatorType.NOT || t == OperatorType.UNARY_PLUS
					|| t == OperatorType.UNARY_MINUS)
			{
				// fold unary
				enforceHelper!QuerySyntaxException(bestOpPosition < items.length
						&& items[bestOpPosition + 1].isExpression(),
						"Syntax error in WHERE condition " ~ errorContext(items[bestOpPosition]));
				Token folded = new Token(items[bestOpPosition].pos, t,
						items[bestOpPosition].text, items[bestOpPosition + 1]);
				replaceInPlace(items, bestOpPosition, bestOpPosition + 2, [
						folded
						]);
			}
			else if (t == OperatorType.IS_NULL || t == OperatorType.IS_NOT_NULL)
			{
				// fold unary
				enforceHelper!QuerySyntaxException(bestOpPosition > 0 && items[bestOpPosition - 1].isExpression(),
						"Syntax error in WHERE condition " ~ errorContext(items[bestOpPosition]));
				Token folded = new Token(items[bestOpPosition - 1].pos, t,
						items[bestOpPosition].text, items[bestOpPosition - 1]);
				replaceInPlace(items, bestOpPosition - 1, bestOpPosition + 1, [
						folded
						]);
			}
			else if (t == OperatorType.BETWEEN)
			{
				// fold  X BETWEEN A AND B
				enforceHelper!QuerySyntaxException(bestOpPosition > 0,
						"Syntax error in WHERE condition - no left arg for BETWEEN operator");
				enforceHelper!QuerySyntaxException(bestOpPosition < items.length - 1,
						"Syntax error in WHERE condition - no min bound for BETWEEN operator " ~ errorContext(
							items[bestOpPosition]));
				enforceHelper!QuerySyntaxException(bestOpPosition < items.length - 3,
						"Syntax error in WHERE condition - no max bound for BETWEEN operator " ~ errorContext(
							items[bestOpPosition]));
				enforceHelper!QuerySyntaxException(items[bestOpPosition + 2].operator == OperatorType.AND,
						"Syntax error in WHERE condition - no max bound for BETWEEN operator" ~ errorContext(
							items[bestOpPosition]));
				Token folded = new Token(items[bestOpPosition - 1].pos, t,
						items[bestOpPosition].text, items[bestOpPosition - 1]);
				folded.children ~= items[bestOpPosition + 1];
				folded.children ~= items[bestOpPosition + 3];
				replaceInPlace(items, bestOpPosition - 1, bestOpPosition + 4, [
						folded
						]);
			}
			else if (t == OperatorType.IN)
			{
				// fold  X IN (A, B, ...)
				enforceHelper!QuerySyntaxException(bestOpPosition > 0,
						"Syntax error in WHERE condition - no left arg for IN operator");
				enforceHelper!QuerySyntaxException(bestOpPosition < items.length - 1,
						"Syntax error in WHERE condition - no value list for IN operator " ~ errorContext(
							items[bestOpPosition]));
				enforceHelper!QuerySyntaxException(items[bestOpPosition + 1].type == TokenType.Braces,
						"Syntax error in WHERE condition - no value list in braces for IN operator" ~ errorContext(
							items[bestOpPosition]));
				Token folded = new Token(items[bestOpPosition - 1].pos, t,
						items[bestOpPosition].text, items[bestOpPosition - 1]);
				folded.children ~= items[bestOpPosition + 1];
				foldCommaSeparatedList(items[bestOpPosition + 1]);
				replaceInPlace(items, bestOpPosition - 1, bestOpPosition + 2, [
						folded
						]);
				// fold value list
				//writeln("IN operator found: " ~ folded.dump(3));
			}
			else
			{
				// fold binary
				enforceHelper!QuerySyntaxException(bestOpPosition > 0,
						"Syntax error in WHERE condition - no left arg for binary operator " ~ errorContext(
							items[bestOpPosition]));
				enforceHelper!QuerySyntaxException(bestOpPosition < items.length - 1,
						"Syntax error in WHERE condition - no right arg for binary operator " ~ errorContext(
							items[bestOpPosition]));
				//writeln("binary op " ~ items[bestOpPosition - 1].toString() ~ " " ~ items[bestOpPosition].toString() ~ " " ~ items[bestOpPosition + 1].toString());
				enforceHelper!QuerySyntaxException(items[bestOpPosition - 1].isExpression(),
						"Syntax error in WHERE condition - wrong type of left arg for binary operator " ~ errorContext(
							items[bestOpPosition]));
				enforceHelper!QuerySyntaxException(items[bestOpPosition + 1].isExpression(),
						"Syntax error in WHERE condition - wrong type of right arg for binary operator " ~ errorContext(
							items[bestOpPosition]));
				Token folded = new Token(items[bestOpPosition - 1].pos, t,
						items[bestOpPosition].text, items[bestOpPosition - 1],
						items[bestOpPosition + 1]);
				auto oldlen = items.length;
				replaceInPlace(items, bestOpPosition - 1, bestOpPosition + 2, [
						folded
						]);
				assert(items.length == oldlen - 2);
			}
		}
	}

	void parseOrderClause(int start, int end)
	{
		enforceHelper!QuerySyntaxException(start < end,
				"Invalid ORDER BY clause" ~ errorContext(tokens[start]));
		splitCommaDelimitedList(start, end, &parseOrderByClauseItem);
	}

	/// returns position of keyword in tokens array, -1 if not found
	int findKeyword(KeywordType k, int startFrom = 0)
	{
		for (int i = startFrom; i < tokens.length; i++)
		{
			if (tokens[i].type == TokenType.Keyword && tokens[i].keyword == k)
				return i;
		}
		return -1;
	}

	int addSelectSQL(Dialect dialect, ParsedQuery res, string tableName,
			bool first, const EntityInfo ei)
	{
		int colCount = 0;
		for (int j = 0; j < ei.getPropertyCount(); j++)
		{
			PropertyInfo f = cast(PropertyInfo) ei.getProperty(j);
			string fieldName = f.columnName;
			if (f.embedded)
			{
				// put embedded cols here
				colCount += addSelectSQL(dialect, res, tableName, first
						&& colCount == 0, f.referencedEntity);
				continue;
			}
			else if (f.oneToOne)
			{
			}
			else
			{
			}
			if (fieldName is null)
				continue;
			if (!first || colCount > 0)
			{
				res.appendSQL(", ");
			}
			else
				first = false;

			res.appendSQL(tableName ~ "." ~ dialect.quoteIfNeeded(fieldName));
			colCount++;
		}
		return colCount;
	}

	void addSelectSQL(Dialect dialect, ParsedQuery res)
	{
		res.appendSQL("SELECT ");
		bool first = true;
		assert(selectClause.length > 0);
		int colCount = 0;
		foreach (i, s; selectClause)
		{
			s.from.selectIndex = cast(int) i;
		}
		if (selectClause[0].prop is null)
		{
			// object alias is specified: add all properties of object
			//writeln("selected entity count: " ~ to!string(selectClause.length));
			res.setEntity(selectClause[0].from.entity);
			for (int i = 0; i < fromClause.length; i++)
			{
				FromClauseItem from = fromClause[i];
				if (!from.fetch)
					continue;
				string tableName = from.sqlAlias;
				assert(from !is null);
				assert(from.entity !is null);
				colCount += addSelectSQL(dialect, res, tableName, colCount == 0, from.entity);
			}
		}
		else
		{
			// individual fields specified
			res.setEntity(null);
			foreach (a; selectClause)
			{
				string fieldName = a.prop.columnName;
				string tableName = a.from.sqlAlias;
				if (!first)
				{
					res.appendSQL(", ");
				}
				else
					first = false;
				res.appendSQL(tableName ~ "." ~ dialect.quoteIfNeeded(fieldName));
				colCount++;
			}
		}
		res.setColCount(colCount);
		res.setSelect(selectClause);
	}

	void addFromSQL(Dialect dialect, ParsedQuery res)
	{
		res.setFromClause(fromClause);
		res.appendSpace();
		res.appendSQL("FROM ");
		res.appendSQL(dialect.quoteIfNeeded(
				fromClause.first.entity.tableName) ~ " AS " ~ fromClause.first.sqlAlias);
		for (int i = 1; i < fromClause.length; i++)
		{
			FromClauseItem join = fromClause[i];
			FromClauseItem base = join.base;
			assert(join !is null && base !is null);
			res.appendSpace();

			assert(join.baseProperty !is null);
			if (join.baseProperty.manyToMany)
			{
				string joinTableAlias = base.sqlAlias ~ join.sqlAlias;
				res.appendSQL(join.joinType == JoinType.LeftJoin ? "LEFT JOIN " : "INNER JOIN ");

				res.appendSQL(dialect.quoteIfNeeded(
						join.baseProperty.joinTable.tableName) ~ " AS " ~ joinTableAlias);
				res.appendSQL(" ON ");
				res.appendSQL(base.sqlAlias);
				res.appendSQL(".");
				res.appendSQL(dialect.quoteIfNeeded(base.entity.getKeyProperty().columnName));
				res.appendSQL("=");
				res.appendSQL(joinTableAlias);
				res.appendSQL(".");
				res.appendSQL(dialect.quoteIfNeeded(join.baseProperty.joinTable.column1));

				res.appendSpace();

				res.appendSQL(join.joinType == JoinType.LeftJoin ? "LEFT JOIN " : "INNER JOIN ");
				res.appendSQL(dialect.quoteIfNeeded(join.entity.tableName) ~ " AS " ~ join.sqlAlias);
				res.appendSQL(" ON ");
				res.appendSQL(joinTableAlias);
				res.appendSQL(".");
				res.appendSQL(dialect.quoteIfNeeded(join.baseProperty.joinTable.column2));
				res.appendSQL("=");
				res.appendSQL(join.sqlAlias);
				res.appendSQL(".");
				res.appendSQL(dialect.quoteIfNeeded(join.entity.getKeyProperty().columnName));
			}
			else
			{
				res.appendSQL(join.joinType == JoinType.LeftJoin ? "LEFT JOIN " : "INNER JOIN ");
				res.appendSQL(dialect.quoteIfNeeded(join.entity.tableName) ~ " AS " ~ join.sqlAlias);
				res.appendSQL(" ON ");
				//writeln("adding ON");
				if (join.baseProperty.oneToOne)
				{
					assert(join.baseProperty.columnName !is null
							|| join.baseProperty.referencedProperty !is null);
					if (join.baseProperty.columnName !is null)
					{
						//writeln("fk is in base");
						res.appendSQL(base.sqlAlias);
						res.appendSQL(".");
						res.appendSQL(dialect.quoteIfNeeded(join.baseProperty.columnName));
						res.appendSQL("=");
						res.appendSQL(join.sqlAlias);
						res.appendSQL(".");
						res.appendSQL(dialect.quoteIfNeeded(join.entity.getKeyProperty()
								.columnName));
					}
					else
					{
						//writeln("fk is in join");
						res.appendSQL(base.sqlAlias);
						res.appendSQL(".");
						res.appendSQL(dialect.quoteIfNeeded(base.entity.getKeyProperty()
								.columnName));
						res.appendSQL("=");
						res.appendSQL(join.sqlAlias);
						res.appendSQL(".");
						res.appendSQL(dialect.quoteIfNeeded(
								join.baseProperty.referencedProperty.columnName));
					}
				}
				else if (join.baseProperty.manyToOne)
				{
					assert(join.baseProperty.columnName !is null,
							"ManyToOne should have JoinColumn as well");
					//writeln("fk is in base");
					res.appendSQL(base.sqlAlias);
					res.appendSQL(".");
					res.appendSQL(dialect.quoteIfNeeded(join.baseProperty.columnName));
					res.appendSQL("=");
					res.appendSQL(join.sqlAlias);
					res.appendSQL(".");
					res.appendSQL(dialect.quoteIfNeeded(join.entity.getKeyProperty().columnName));
				}
				else if (join.baseProperty.oneToMany)
				{
					res.appendSQL(base.sqlAlias);
					res.appendSQL(".");
					res.appendSQL(dialect.quoteIfNeeded(base.entity.getKeyProperty().columnName));
					res.appendSQL("=");
					res.appendSQL(join.sqlAlias);
					res.appendSQL(".");
					res.appendSQL(dialect.quoteIfNeeded(
							join.baseProperty.referencedProperty.columnName));
				}
				else
				{
					// TODO: support other relations
					throw new QuerySyntaxException("Invalid relation type in join");
				}
			}
		}
	}

	void addWhereCondition(Token t, int basePrecedency, Dialect dialect, ParsedQuery res)
	{
		if (t.type == TokenType.Expression)
		{
			addWhereCondition(t.children[0], basePrecedency, dialect, res);
		}
		else if (t.type == TokenType.Field)
		{
			string tableName = t.from.sqlAlias;
			string fieldName = t.field.columnName;
			res.appendSpace();
			res.appendSQL(tableName ~ "." ~ dialect.quoteIfNeeded(fieldName));
		}
		else if (t.type == TokenType.Number)
		{
			res.appendSpace();
			res.appendSQL(t.text);
		}
		else if (t.type == TokenType.String)
		{
			res.appendSpace();
			res.appendSQL(dialect.quoteSqlString(t.text));
		}
		else if (t.type == TokenType.Parameter)
		{
			res.appendSpace();
			res.appendSQL("?");
			res.addParam(t.text);
		}
		else if (t.type == TokenType.CommaDelimitedList)
		{
			bool first = true;
			for (int i = 0; i < t.children.length; i++)
			{
				if (!first)
					res.appendSQL(", ");
				else
					first = false;
				addWhereCondition(t.children[i], 0, dialect, res);
			}
		}
		else if (t.type == TokenType.OpExpr)
		{
			int currentPrecedency = operatorPrecedency(t.operator);
			bool needBraces = currentPrecedency < basePrecedency;
			if (needBraces)
				res.appendSQL("(");
			switch (t.operator)
			{
			case OperatorType.LIKE:
			case OperatorType.EQ:
			case OperatorType.NE:
			case OperatorType.LT:
			case OperatorType.GT:
			case OperatorType.LE:
			case OperatorType.GE:
			case OperatorType.MUL:
			case OperatorType.ADD:
			case OperatorType.SUB:
			case OperatorType.DIV:
			case OperatorType.AND:
			case OperatorType.OR:
			case OperatorType.IDIV:
			case OperatorType.MOD:
				// binary op
				if (!needBraces)
					res.appendSpace();
				addWhereCondition(t.children[0], currentPrecedency, dialect, res);
				res.appendSpace();
				res.appendSQL(t.text);
				res.appendSpace();
				addWhereCondition(t.children[1], currentPrecedency, dialect, res);
				break;
			case OperatorType.UNARY_PLUS:
			case OperatorType.UNARY_MINUS:
			case OperatorType.NOT:
				// unary op
				if (!needBraces)
					res.appendSpace();
				res.appendSQL(t.text);
				res.appendSpace();
				addWhereCondition(t.children[0], currentPrecedency, dialect, res);
				break;
			case OperatorType.IS_NULL:
			case OperatorType.IS_NOT_NULL:
				addWhereCondition(t.children[0], currentPrecedency, dialect, res);
				res.appendSpace();
				res.appendSQL(t.text);
				res.appendSpace();
				break;
			case OperatorType.BETWEEN:
				if (!needBraces)
					res.appendSpace();
				addWhereCondition(t.children[0], currentPrecedency, dialect, res);
				res.appendSQL(" BETWEEN ");
				addWhereCondition(t.children[1], currentPrecedency, dialect, res);
				res.appendSQL(" AND ");
				addWhereCondition(t.children[2], currentPrecedency, dialect, res);
				break;
			case OperatorType.IN:
				if (!needBraces)
					res.appendSpace();
				addWhereCondition(t.children[0], currentPrecedency, dialect, res);
				res.appendSQL(" IN (");
				addWhereCondition(t.children[1], currentPrecedency, dialect, res);
				res.appendSQL(")");
				break;
			case OperatorType.IS:
			default:
				enforceHelper!QuerySyntaxException(false,
						"Unexpected operator" ~ errorContext(t));
				break;
			}
			if (needBraces)
				res.appendSQL(")");
		}
	}

	void addWhereSQL(Dialect dialect, ParsedQuery res)
	{
		if (whereClause is null)
			return;
		res.appendSpace();
		res.appendSQL("WHERE ");
		addWhereCondition(whereClause, 0, dialect, res);
	}

	void addOrderBySQL(Dialect dialect, ParsedQuery res)
	{
		if (orderByClause.length == 0)
			return;
		res.appendSpace();
		res.appendSQL("ORDER BY ");
		bool first = true;
		// individual fields specified
		foreach (a; orderByClause)
		{
			string fieldName = a.prop.columnName;
			string tableName = a.from.sqlAlias;
			if (!first)
			{
				res.appendSQL(", ");
			}
			else
				first = false;
			res.appendSQL(tableName ~ "." ~ dialect.quoteIfNeeded(fieldName));
			if (!a.asc)
				res.appendSQL(" DESC");
		}
	}

	ParsedQuery makeSQL(Dialect dialect)
	{
		ParsedQuery res = new ParsedQuery(query);
		addSelectSQL(dialect, res);
		addFromSQL(dialect, res);
		addWhereSQL(dialect, res);
		addOrderBySQL(dialect, res);
		return res;
	}

}

enum KeywordType
{
	NONE,
	SELECT,
	FROM,
	WHERE,
	ORDER,
	BY,
	ASC,
	DESC,
	JOIN,
	INNER,
	OUTER,
	LEFT,
	RIGHT,
	FETCH,
	AS,
	LIKE,
	IN,
	IS,
	NOT,
	NULL,
	AND,
	OR,
	BETWEEN,
	DIV,
	MOD,
}

KeywordType isKeyword(string str)
{
	return isKeyword(str.dup);
}

KeywordType isKeyword(char[] str)
{
	char[] s = toUpper(str);
	if (s == "SELECT")
		return KeywordType.SELECT;
	if (s == "FROM")
		return KeywordType.FROM;
	if (s == "WHERE")
		return KeywordType.WHERE;
	if (s == "ORDER")
		return KeywordType.ORDER;
	if (s == "BY")
		return KeywordType.BY;
	if (s == "ASC")
		return KeywordType.ASC;
	if (s == "DESC")
		return KeywordType.DESC;
	if (s == "JOIN")
		return KeywordType.JOIN;
	if (s == "INNER")
		return KeywordType.INNER;
	if (s == "OUTER")
		return KeywordType.OUTER;
	if (s == "LEFT")
		return KeywordType.LEFT;
	if (s == "RIGHT")
		return KeywordType.RIGHT;
	if (s == "FETCH")
		return KeywordType.FETCH;
	if (s == "LIKE")
		return KeywordType.LIKE;
	if (s == "IN")
		return KeywordType.IN;
	if (s == "IS")
		return KeywordType.IS;
	if (s == "NOT")
		return KeywordType.NOT;
	if (s == "NULL")
		return KeywordType.NULL;
	if (s == "AS")
		return KeywordType.AS;
	if (s == "AND")
		return KeywordType.AND;
	if (s == "OR")
		return KeywordType.OR;
	if (s == "BETWEEN")
		return KeywordType.BETWEEN;
	if (s == "DIV")
		return KeywordType.DIV;
	if (s == "MOD")
		return KeywordType.MOD;
	return KeywordType.NONE;
}

unittest
{
	assert(isKeyword("Null") == KeywordType.NULL);
	assert(isKeyword("from") == KeywordType.FROM);
	assert(isKeyword("SELECT") == KeywordType.SELECT);
	assert(isKeyword("blabla") == KeywordType.NONE);
}

enum OperatorType
{
	NONE,

	// symbolic
	EQ, // ==
	NE, // != <>
	LT, // <
	GT, // >
	LE, // <=
	GE, // >=
	MUL, // *
	ADD, // +
	SUB, // -
	DIV, // /

	// from keywords
	LIKE,
	IN,
	IS,
	NOT,
	AND,
	OR,
	BETWEEN,
	IDIV,
	MOD,

	UNARY_PLUS,
	UNARY_MINUS,

	IS_NULL,
	IS_NOT_NULL,
}

OperatorType isOperator(KeywordType t)
{
	switch (t)
	{
	case KeywordType.LIKE:
		return OperatorType.LIKE;
	case KeywordType.IN:
		return OperatorType.IN;
	case KeywordType.IS:
		return OperatorType.IS;
	case KeywordType.NOT:
		return OperatorType.NOT;
	case KeywordType.AND:
		return OperatorType.AND;
	case KeywordType.OR:
		return OperatorType.OR;
	case KeywordType.BETWEEN:
		return OperatorType.BETWEEN;
	case KeywordType.DIV:
		return OperatorType.IDIV;
	case KeywordType.MOD:
		return OperatorType.MOD;
	default:
		return OperatorType.NONE;
	}
}

int operatorPrecedency(OperatorType t)
{
	switch (t)
	{
	case OperatorType.EQ:
		return 5; // ==
	case OperatorType.NE:
		return 5; // != <>
	case OperatorType.LT:
		return 5; // <
	case OperatorType.GT:
		return 5; // >
	case OperatorType.LE:
		return 5; // <=
	case OperatorType.GE:
		return 5; // >=
	case OperatorType.MUL:
		return 10; // *
	case OperatorType.ADD:
		return 9; // +
	case OperatorType.SUB:
		return 9; // -
	case OperatorType.DIV:
		return 10; // /
		// from keywords
	case OperatorType.LIKE:
		return 11;
	case OperatorType.IN:
		return 12;
	case OperatorType.IS:
		return 13;
	case OperatorType.NOT:
		return 6; // ???
	case OperatorType.AND:
		return 4;
	case OperatorType.OR:
		return 3;
	case OperatorType.BETWEEN:
		return 7; // ???
	case OperatorType.IDIV:
		return 10;
	case OperatorType.MOD:
		return 10;
	case OperatorType.UNARY_PLUS:
		return 15;
	case OperatorType.UNARY_MINUS:
		return 15;
	case OperatorType.IS_NULL:
		return 15;
	case OperatorType.IS_NOT_NULL:
		return 15;
	default:
		return -1;
	}
}

OperatorType isOperator(string s, ref int i)
{
	int len = cast(int) s.length;
	char ch = s[i];
	char ch2 = i < len - 1 ? s[i + 1] : 0;
	//char ch3 = i < len - 2 ? s[i + 2] : 0;
	if (ch == '=' && ch2 == '=')
	{
		i++;
		return OperatorType.EQ;
	} // ==
	if (ch == '!' && ch2 == '=')
	{
		i++;
		return OperatorType.NE;
	} // !=
	if (ch == '<' && ch2 == '>')
	{
		i++;
		return OperatorType.NE;
	} // <>
	if (ch == '<' && ch2 == '=')
	{
		i++;
		return OperatorType.LE;
	} // <=
	if (ch == '>' && ch2 == '=')
	{
		i++;
		return OperatorType.GE;
	} // >=
	if (ch == '=')
		return OperatorType.EQ; // =
	if (ch == '<')
		return OperatorType.LT; // <
	if (ch == '>')
		return OperatorType.GT; // <
	if (ch == '*')
		return OperatorType.MUL; // <
	if (ch == '+')
		return OperatorType.ADD; // <
	if (ch == '-')
		return OperatorType.SUB; // <
	if (ch == '/')
		return OperatorType.DIV; // <
	return OperatorType.NONE;
}

enum TokenType
{
	Keyword, // WHERE
	Ident, // ident
	Number, // 25   13.5e-10
	String, // 'string'
	Operator, // == != <= >= < > + - * /
	Dot, // .
	OpenBracket, // (
	CloseBracket, // )
	Comma, // ,
	Entity, // entity name
	Field, // field name of some entity
	Alias, // alias name of some entity
	Parameter, // ident after :
	// types of compound AST nodes
	Expression, // any expression
	Braces, // ( tokens )
	CommaDelimitedList, // tokens, ... , tokens
	OpExpr, // operator expression; current token == operator, children = params
}

class Token
{
	int pos;
	TokenType type;
	KeywordType keyword = KeywordType.NONE;
	OperatorType operator = OperatorType.NONE;
	string text;
	string spaceAfter;
	EntityInfo entity;
	PropertyInfo field;
	FromClauseItem from;
	Token[] children;
	this(int pos, TokenType type, string text)
	{
		this.pos = pos;
		this.type = type;
		this.text = text;
	}

	this(int pos, KeywordType keyword, string text)
	{
		this.pos = pos;
		this.type = TokenType.Keyword;
		this.keyword = keyword;
		this.text = text;
	}

	this(int pos, OperatorType op, string text)
	{
		this.pos = pos;
		this.type = TokenType.Operator;
		this.operator = op;
		this.text = text;
	}

	this(int pos, TokenType type, Token[] base, int start, int end)
	{
		this.pos = pos;
		this.type = type;
		this.children = new Token[end - start];
		for (int i = start; i < end; i++)
			children[i - start] = base[i];
	}
	// unary operator expression
	this(int pos, OperatorType type, string text, Token right)
	{
		this.pos = pos;
		this.type = TokenType.OpExpr;
		this.operator = type;
		this.text = text;
		this.children = new Token[1];
		this.children[0] = right;
	}
	// binary operator expression
	this(int pos, OperatorType type, string text, Token left, Token right)
	{
		this.pos = pos;
		this.type = TokenType.OpExpr;
		this.text = text;
		this.operator = type;
		this.children = new Token[2];
		this.children[0] = left;
		this.children[1] = right;
	}

	bool isExpression()
	{
		return type == TokenType.Expression || type == TokenType.Braces || type == TokenType.OpExpr
			|| type == TokenType.Parameter || type == TokenType.Field
			|| type == TokenType.String || type == TokenType.Number;
	}

	bool isCompound()
	{
		return this.type >= TokenType.Expression;
	}

	string dump(int level)
	{
		string res;
		for (int i = 0; i < level; i++)
			res ~= "    ";
		res ~= toString() ~ "\n";
		foreach (c; children)
			res ~= c.dump(level + 1);
		return res;
	}

	override string toString()
	{
		switch (type)
		{
		case TokenType.Keyword: // WHERE
		case TokenType.Ident:
			return "`" ~ text ~ "`"; // ident
		case TokenType.Number:
			return "" ~ text; // 25   13.5e-10
		case TokenType.String:
			return "'" ~ text ~ "'"; // 'string'
		case TokenType.Operator:
			return "op:" ~ text; // == != <= >= < > + - * /
		case TokenType.Dot:
			return "."; // .
		case TokenType.OpenBracket:
			return "("; // (
		case TokenType.CloseBracket:
			return ")"; // )
		case TokenType.Comma:
			return ","; // ,
		case TokenType.Entity:
			return "entity: " ~ entity.name; // entity name
		case TokenType.Field:
			return from.entityAlias ~ "." ~ field.propertyName; // field name of some entity
		case TokenType.Alias:
			return "alias: " ~ text; // alias name of some entity
		case TokenType.Parameter:
			return ":" ~ text; // ident after :
			// types of compound AST nodes
		case TokenType.Expression:
			return "expr"; // any expression
		case TokenType.Braces:
			return "()"; // ( tokens )
		case TokenType.CommaDelimitedList:
			return ",,,"; // tokens, ... , tokens
		case TokenType.OpExpr:
			return "" ~ text;
		default:
			return "UNKNOWN";
		}
	}

}

Token[] tokenize(string s)
{
	Token[] res;
	int startpos = 0;
	int state = 0;
	int len = cast(int) s.length;
	for (int i = 0; i < len; i++)
	{
		char ch = s[i];
		char ch2 = i < len - 1 ? s[i + 1] : 0;
		char ch3 = i < len - 2 ? s[i + 2] : 0;
		string text;
		bool quotedIdent = ch == '`';
		startpos = i;
		OperatorType op = isOperator(s, i);
		if (op != OperatorType.NONE)
		{
			// operator
			res ~= new Token(startpos, op, s[startpos .. i + 1]);
		}
		else if (ch == ':' && (isAlpha(ch2) || ch2 == '_'))
		{
			// parameter name
			i++;
			// && state == 0
			for (int j = i; j < len; j++)
			{
				if (isAlphaNum(s[j]) || s[j] == '_')
				{
					text ~= s[j];
					i = j;
				}
				else
				{
					break;
				}
			}
			enforceHelper!QuerySyntaxException(text.length > 0,
					"Invalid parameter name near " ~ cast(string) s[startpos .. $]);
			res ~= new Token(startpos, TokenType.Parameter, text);
		}
		else if (isAlpha(ch) || ch == '_' || quotedIdent)
		{
			// identifier or keyword
			if (quotedIdent)
			{
				i++;
				enforceHelper!QuerySyntaxException(i < len - 1,
						"Invalid quoted identifier near " ~ cast(string) s[startpos .. $]);
			}
			// && state == 0
			for (int j = i; j < len; j++)
			{
				if (isAlphaNum(s[j]) || s[j] == '_')
				{
					text ~= s[j];
					i = j;
				}
				else
				{
					break;
				}
			}
			enforceHelper!QuerySyntaxException(text.length > 0,
					"Invalid quoted identifier near " ~ cast(string) s[startpos .. $]);
			if (quotedIdent)
			{
				enforceHelper!QuerySyntaxException(i < len - 1 && s[i + 1] == '`',
						"Invalid quoted identifier near " ~ cast(string) s[startpos .. $]);
				i++;
			}
			KeywordType keywordId = isKeyword(text);
			if (keywordId != KeywordType.NONE && !quotedIdent)
			{
				OperatorType keywordOp = isOperator(keywordId);
				if (keywordOp != OperatorType.NONE)
					res ~= new Token(startpos, keywordOp, text); // operator keyword
				else
					res ~= new Token(startpos, keywordId, text);
			}
			else
				res ~= new Token(startpos, TokenType.Ident, text);
		}
		else if (isWhite(ch))
		{
			// whitespace
			for (int j = i; j < len; j++)
			{
				if (isWhite(s[j]))
				{
					text ~= s[j];
					i = j;
				}
				else
				{
					break;
				}
			}
			// don't add whitespace to lexer results as separate token
			// add as spaceAfter
			if (res.length > 0)
			{
				res[$ - 1].spaceAfter = text;
			}
		}
		else if (ch == '\'')
		{
			// string constant
			i++;
			for (int j = i; j < len; j++)
			{
				if (s[j] != '\'')
				{
					text ~= s[j];
					i = j;
				}
				else
				{
					break;
				}
			}
			enforceHelper!QuerySyntaxException(i < len - 1 && s[i + 1] == '\'',
					"Unfinished string near " ~ cast(string) s[startpos .. $]);
			i++;
			res ~= new Token(startpos, TokenType.String, text);
		}
		else if (isDigit(ch) || (ch == '.' && isDigit(ch2)))
		{
			// numeric constant
			if (ch == '.')
			{
				// .25
				text ~= '.';
				i++;
				for (int j = i; j < len; j++)
				{
					if (isDigit(s[j]))
					{
						text ~= s[j];
						i = j;
					}
					else
					{
						break;
					}
				}
			}
			else
			{
				// 123
				for (int j = i; j < len; j++)
				{
					if (isDigit(s[j]))
					{
						text ~= s[j];
						i = j;
					}
					else
					{
						break;
					}
				}
				// .25
				if (i < len - 1 && s[i + 1] == '.')
				{
					text ~= '.';
					i++;
					for (int j = i; j < len; j++)
					{
						if (isDigit(s[j]))
						{
							text ~= s[j];
							i = j;
						}
						else
						{
							break;
						}
					}
				}
			}
			if (i < len - 1 && std.ascii.toLower(s[i + 1]) == 'e')
			{
				text ~= s[i + 1];
				i++;
				if (i < len - 1 && (s[i + 1] == '-' || s[i + 1] == '+'))
				{
					text ~= s[i + 1];
					i++;
				}
				enforceHelper!QuerySyntaxException(i < len - 1 && isDigit(s[i]),
						"Invalid number near " ~ cast(string) s[startpos .. $]);
				for (int j = i; j < len; j++)
				{
					if (isDigit(s[j]))
					{
						text ~= s[j];
						i = j;
					}
					else
					{
						break;
					}
				}
			}
			enforceHelper!QuerySyntaxException(i >= len - 1 || !isAlpha(s[i]),
					"Invalid number near " ~ cast(string) s[startpos .. $]);
			res ~= new Token(startpos, TokenType.Number, text);
		}
		else if (ch == '.')
		{
			res ~= new Token(startpos, TokenType.Dot, ".");
		}
		else if (ch == '(')
		{
			res ~= new Token(startpos, TokenType.OpenBracket, "(");
		}
		else if (ch == ')')
		{
			res ~= new Token(startpos, TokenType.CloseBracket, ")");
		}
		else if (ch == ',')
		{
			res ~= new Token(startpos, TokenType.Comma, ",");
		}
		else
		{
			enforceHelper!QuerySyntaxException(false,
					"Invalid character near " ~ cast(string) s[startpos .. $]);
		}
	}
	return res;
}

unittest
{
	Token[] tokens;
	tokens = tokenize(
			"SELECT a From User a where a.flags = 12 AND a.name='john' ORDER BY a.idx ASC");
	assert(tokens.length == 23);
	assert(tokens[0].type == TokenType.Keyword);
	assert(tokens[2].type == TokenType.Keyword);
	assert(tokens[5].type == TokenType.Keyword);
	assert(tokens[5].text == "where");
	assert(tokens[10].type == TokenType.Number);
	assert(tokens[10].text == "12");
	assert(tokens[16].type == TokenType.String);
	assert(tokens[16].text == "john");
	assert(tokens[22].type == TokenType.Keyword);
	assert(tokens[22].text == "ASC");
}

class ParameterValues
{
	Variant[string] values;
	int[][string] params;
	int[string] unboundParams;
	this(int[][string] params)
	{
		this.params = params;
		foreach (key, value; params)
		{
			unboundParams[key] = 1;
		}
	}

	void setParameter(string name, Variant value)
	{
		enforceHelper!QueryParameterException((name in params) !is null,
				"Attempting to set unknown parameter " ~ name);
		unboundParams.remove(name);
		values[name] = value;
	}

	void checkAllParametersSet()
	{
		if (unboundParams.length == 0)
			return;
		string list;
		foreach (key, value; unboundParams)
		{
			if (list.length > 0)
				list ~= ", ";
			list ~= key;
		}
		enforceHelper!QueryParameterException(false, "Parameters " ~ list ~ " not set");
	}

	void applyParams(DataSetWriter ds)
	{
		foreach (key, indexes; params)
		{
			Variant value = values[key];
			foreach (i; indexes)
				ds.setVariant(i, value);
		}
	}
}

class ParsedQuery
{
	private string _hql;
	private string _sql;
	private int[][string] params; // contains 1-based indexes of ? ? ? placeholders in SQL for param by name
	private int paramIndex = 1;
	private FromClause _from;
	private SelectClauseItem[] _select;
	private EntityInfo _entity;
	private int _colCount = 0;
	this(string hql)
	{
		_hql = hql;
	}

	@property string hql()
	{
		return _hql;
	}

	@property string sql()
	{
		return _sql;
	}

	@property const(EntityInfo) entity()
	{
		return _entity;
	}

	@property int colCount()
	{
		return _colCount;
	}

	@property FromClause from()
	{
		return _from;
	}

	@property SelectClauseItem[] select()
	{
		return _select;
	}

	void setEntity(const EntityInfo entity)
	{
		_entity = cast(EntityInfo) entity;
	}

	void setFromClause(FromClause from)
	{
		_from = from;
	}

	void setSelect(SelectClauseItem[] items)
	{
		_select = items;
	}

	void setColCount(int cnt)
	{
		_colCount = cnt;
	}

	void addParam(string paramName)
	{
		if ((paramName in params) is null)
		{
			params[paramName] = [paramIndex++];
		}
		else
		{
			params[paramName] ~= [paramIndex++];
		}
	}

	int[] getParam(string paramName)
	{
		if ((paramName in params) is null)
		{
			throw new DStructException("Parameter " ~ paramName ~ " not found in query " ~ _hql);
		}
		else
		{
			return params[paramName];
		}
	}

	void appendSQL(string sql)
	{
		_sql ~= sql;
	}

	void appendSpace()
	{
		if (_sql.length > 0 && _sql[$ - 1] != ' ')
			_sql ~= ' ';
	}

	ParameterValues createParams()
	{
		return new ParameterValues(params);
	}
}

unittest
{
	ParsedQuery q = new ParsedQuery("FROM User where id = :param1 or id = :param2");
	q.addParam("param1"); // 1
	q.addParam("param2"); // 2
	q.addParam("param1"); // 3
	q.addParam("param1"); // 4
	q.addParam("param3"); // 5
	q.addParam("param2"); // 6
	assert(q.getParam("param1") == [1, 3, 4]);
	assert(q.getParam("param2") == [2, 6]);
	assert(q.getParam("param3") == [5]);
}

unittest
{
	import dstruct.tests;

	EntityMetaData schema = new SchemaInfoImpl!(User, Customer, AccountType,
			Address, Person, MoreInfo, EvenMoreInfo, Role);
	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");
	assert(parser.parameterNames.length == 2);
	//writeln("param1=" ~ parser.parameterNames[0]);
	//writeln("param2=" ~ parser.parameterNames[1]);
	assert(parser.parameterNames[0] == "Id");
	assert(parser.parameterNames[1] == "skipName");
	assert(parser.fromClause.length == 1);
	assert(parser.fromClause.first.entity.name == "User");
	assert(parser.fromClause.first.entityAlias == "a");
	assert(parser.selectClause.length == 1);
	assert(parser.selectClause[0].prop is null);
	assert(parser.selectClause[0].from.entity.name == "User");
	assert(parser.orderByClause.length == 2);
	assert(parser.orderByClause[0].prop.propertyName == "name");
	assert(parser.orderByClause[0].from.entity.name == "User");
	assert(parser.orderByClause[0].asc == true);
	assert(parser.orderByClause[1].prop.propertyName == "flags");
	assert(parser.orderByClause[1].from.entity.name == "User");
	assert(parser.orderByClause[1].asc == false);

	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");
	assert(parser.whereClause !is null);
	//writeln(parser.whereClause.dump(0));
	Dialect dialect = new MySQLDialect();

	assert(dialect.quoteSqlString("abc") == "'abc'");
	assert(dialect.quoteSqlString("a'b'c") == "'a\\'b\\'c'");
	assert(dialect.quoteSqlString("a\nc") == "'a\\nc'");

	parser = new QueryParser(schema, "FROM User AS u WHERE id = :Id and u.name like '%test%'");
	ParsedQuery q = parser.makeSQL(dialect);
	//writeln(parser.whereClause.dump(0));
	//writeln(q.hql ~ "\n=>\n" ~ q.sql);

	//writeln(q.hql);
	//writeln(q.sql);
	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");
	assert(parser.fromClause.hasAlias("a"));
	assert(parser.fromClause.hasAlias("b"));
	assert(parser.fromClause.findByAlias("a").entityName == "Person");
	assert(parser.fromClause.findByAlias("b").entityName == "MoreInfo");
	assert(parser.fromClause.findByAlias("b").joinType == JoinType.LeftJoin);
	assert(parser.fromClause.findByAlias("c").entityName == "EvenMoreInfo");
	// indirect JOIN
	parser = new QueryParser(schema,
			"SELECT a FROM Person a WHERE a.id = :Id AND a.moreInfo.evenMore.flags > 0");
	assert(parser.fromClause.hasAlias("a"));
	assert(parser.fromClause.length == 3);
	assert(parser.fromClause[0].entity.tableName == "person");
	assert(parser.fromClause[1].entity.tableName == "person_info");
	assert(parser.fromClause[1].joinType == JoinType.InnerJoin);
	assert(parser.fromClause[1].pathString == "a.moreInfo");
	assert(parser.fromClause[2].entity.tableName == "person_info2");
	assert(parser.fromClause[2].joinType == JoinType.LeftJoin);
	assert(parser.fromClause[2].pathString == "a.moreInfo.evenMore");
	// indirect JOIN, no alias
	parser = new QueryParser(schema, "FROM Person WHERE id = :Id AND moreInfo.evenMore.flags > 0");
	assert(parser.fromClause.length == 3);
	assert(parser.fromClause[0].entity.tableName == "person");
	assert(parser.fromClause[0].fetch == true);
	//writeln("select fields [" ~ to!string(parser.fromClause[0].startColumn) ~ ", " ~ to!string(parser.fromClause[0].selectedColumns) ~ "]");
	//writeln("select fields [" ~ to!string(parser.fromClause[1].startColumn) ~ ", " ~ to!string(parser.fromClause[1].selectedColumns) ~ "]");
	//writeln("select fields [" ~ to!string(parser.fromClause[2].startColumn) ~ ", " ~ to!string(parser.fromClause[2].selectedColumns) ~ "]");
	assert(parser.fromClause[0].selectedColumns == 4);
	assert(parser.fromClause[1].entity.tableName == "person_info");
	assert(parser.fromClause[1].joinType == JoinType.InnerJoin);
	assert(parser.fromClause[1].pathString == "_a1.moreInfo");
	assert(parser.fromClause[1].fetch == true);
	assert(parser.fromClause[1].selectedColumns == 2);
	assert(parser.fromClause[2].entity.tableName == "person_info2");
	assert(parser.fromClause[2].joinType == JoinType.LeftJoin);
	assert(parser.fromClause[2].pathString == "_a1.moreInfo.evenMore");
	assert(parser.fromClause[2].fetch == true);
	assert(parser.fromClause[2].selectedColumns == 3);

	q = parser.makeSQL(dialect);
	//writeln(q.hql);
	//writeln(q.sql);

	parser = new QueryParser(schema, "FROM User WHERE id in (1, 2, (3 - 1 * 25) / 2, 4 + :Id, 5)");
	//writeln(parser.whereClause.dump(0));
	q = parser.makeSQL(dialect);
	//writeln(q.hql);
	//writeln(q.sql);

	parser = new QueryParser(schema, "FROM Customer WHERE users.id = 1");
	q = parser.makeSQL(dialect);
	//    writeln(q.hql);
	//    writeln(q.sql);
	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");

	parser = new QueryParser(schema, "FROM Customer WHERE id = 1");
	q = parser.makeSQL(dialect);
	//    writeln(q.hql);
	//    writeln(q.sql);
	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");

	parser = new QueryParser(schema, "FROM User WHERE roles.id = 1");
	q = parser.makeSQL(dialect);
	//writeln(q.hql);
	//writeln(q.sql);
	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");

	parser = new QueryParser(schema, "FROM Role WHERE users.id = 1");
	q = parser.makeSQL(dialect);
	//    writeln(q.hql);
	//    writeln(q.sql);
	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");

	parser = new QueryParser(schema, "FROM User WHERE customer.id = 1");
	q = parser.makeSQL(dialect);
	//    writeln(q.hql);
	//    writeln(q.sql);
	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");

	parser = new QueryParser(schema,
			"SELECT a2 FROM User AS a1 JOIN a1.roles AS a2 WHERE a1.id = 1");
	q = parser.makeSQL(dialect);
	//writeln(q.hql);
	//writeln(q.sql);
	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");

	parser = new QueryParser(schema,
			"SELECT a2 FROM Customer AS a1 JOIN a1.users AS a2 WHERE a1.id = 1");
	q = parser.makeSQL(dialect);
	//writeln(q.hql);
	//writeln(q.sql);
	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");

}