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