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