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