1 #region Copyright © 2003-2008 Richard Beauchamp <rbeauchamp@gmail.com> 2 3 /* 4 * DomainObjects for .NET 5 * Copyright © 2003-2008 Richard Beauchamp 6 * 7 * This library is free software; you can redistribute it and/or 8 * modify it under the terms of the GNU Lesser General Public 9 * License as published by the Free Software Foundation; either 10 * version 2.1 of the License, or (at your option) any later version. 11 * 12 * This library is distributed in the hope that it will be useful, 13 * but WITHOUT ANY WARRANTY; without even the implied warranty of 14 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU 15 * Lesser General Public License for more details. 16 * 17 * You should have received a copy of the GNU Lesser General Public 18 * License along with this library; if not, write to the Free Software 19 * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA 20 */ 21 22 #endregion 23 24 using System; 25 using System.Collections.Generic; 26 using System.Data; 27 using System.Data.SqlClient; 28 using System.Diagnostics; 29 using System.Runtime.InteropServices; 30 using DomainObjects.Diagnostics; 31 using DomainObjects.Facade; 32 using DomainObjects.Facade.Command; 33 using DomainObjects.Facade.Command.Expression; 34 using DomainObjects.Facade.Command.Function; 35 using DomainObjects.Facade.Test; 36 using DomainObjects.Test.Domain; 37 using DomainObjects.Test.TestType.Enum; 38 using DomainObjects.Test.TestType.Service; 39 using NUnit.Framework; 40 41 namespace DomainObjects.Test.TestFixture 42 { 43 /// <summary> 44 /// Tests the <see cref="Query{ClassToFind}"/> class. 45 /// </summary> 46 [TestFixture] 47 public class QueryTests : DomainObjectsTestFixture 48 { 49 /// <summary> 50 /// Tests that an object can be retrieved by its primary key. 51 /// </summary> 52 [Test] 53 public void FindObjectByPrimaryKey() 54 { 55 // Retrieve the instance of Product with primary key of 1000 56 Product productFromDB = Query.FindObjectByPrimaryKey<Product>(1000); 57 58 // Validate that we found it 59 Assert.IsNotNull(productFromDB, "A Product was retrieved from the database"); 60 61 // Validate that is is the correct instance. 62 Assert.AreEqual(1000, (int) productFromDB.PrimaryKey[0], "The correct Product instance was retrieved."); 63 } 64 65 /// <summary> 66 /// Tests a query with an AddEqualTo() constraint. 67 /// </summary> 68 [Test] 69 public void AddEqualTo() 70 { 71 // Create a new query that will return instances of the Person class. 72 Query<Person> personQuery = new Query<Person>(); 73 74 // Constrain the search to a person whose firstname is 'tom'. 75 // The first parameter represents the field to search against. 76 // The input to this parameter is a Field generated by the 77 // DomainObjectGen.exe tool based on the Person ClassDescriptor defined 78 // in the Repository.xml file. The second parameter specifies 79 // the value to match against. 80 personQuery.Where.AddEqualTo(Types.Person.Field._firstname, "tom"); 81 82 // Query the database, i.e., find the object of type Person that matches the given criteria. 83 Person person = personQuery.FindObject(); 84 85 Assert.IsNotNull(person, "A person with the first name of 'tom' was found."); 86 } 87 88 /// <summary> 89 /// Tests querying with an AddEqualTo() constraint 90 /// along with an inner join in the reference path. 91 /// </summary> 92 [Test] 93 public void AddEqualToWithInnerJoin() 94 { 95 // Create a new query that will return instances of the Product class. 96 Query<Product> productQuery = new Query<Product>(); 97 98 // Constrain the search to products where the product category name is 'Liquors'. 99 // Note that, because the '_productCategory' argument represents a class referenced 100 // by Product, DomainObjects will automatically generate SQL that contains 101 // an inner join between the PRODUCT table and the referenced PRODUCT_CATEGORY table. 102 productQuery.Where.AddEqualTo(Types.Product._productCategory.Field._name, "Liquors"); 103 104 // Query the database, in other words, find the set of objects of type Product 105 // that match the 'Where' criteria. 106 List<Product> results = productQuery.FindObjectSet(); 107 108 // DomainObjects converts the productQuery to the following SQL SELECT statement: 109 // 110 // SELECT PRODUCT01.PRODUCT_ID AS PRODUCT01_PRODUCT_ID, 111 // PRODUCT01.NAME AS PRODUCT01_NAME, 112 // PRODUCT01.SUPPLIER_ID AS PRODUCT01_SUPPLIER_ID, 113 // PRODUCT01.PRODUCT_CATEGORY_ID AS PRODUCT01_PRODUCT_CATEGORY_ID, 114 // PRODUCT01.UNIT AS PRODUCT01_UNIT, 115 // PRODUCT01.PRICE_PER_UNIT AS PRODUCT01_PRICE_PER_UNIT, 116 // PRODUCT01.CURRENT_STOCK AS PRODUCT01_CURRENT_STOCK, 117 // PRODUCT01.ORDERED_UNITS AS PRODUCT01_ORDERED_UNITS, 118 // PRODUCT01.MINIMUM_STOCK AS PRODUCT01_MINIMUM_STOCK, 119 // PRODUCT01.IS_SELLOUT_PRODUCT AS PRODUCT01_IS_SELLOUT_PRODUCT, 120 // PRODUCT01.STRING_CONVERTED_TO_STRING AS PRODUCT01_STRING_CONVERTED_TO_STRING 121 // FROM PRODUCT AS PRODUCT01 122 // INNER JOIN PRODUCT_CATEGORY AS PRODUCT_CATEGORY12 123 // ON PRODUCT01.PRODUCT_CATEGORY_ID=PRODUCT_CATEGORY12.PRODUCT_CATEGORY_ID 124 // WHERE ((PRODUCT_CATEGORY12.NAME = @PRODUCT_CATEGORY12_NAME_0)) 125 // 126 // Parameter values: 127 // @PRODUCT_CATEGORY12_NAME_0='Liquors' 128 129 Assert.Greater(results.Count, 0, "Found some results."); 130 } 131 132 /// <summary> 133 /// Validates the following features of DomainObjects: 134 /// 135 /// 1. The Criteria.AddEqualToField() method. 136 /// 2. The Criteria.AddNotEqualToField() method. 137 /// 3. Performing a cross join between tables. 138 /// </summary> 139 [Test] 140 public void FindDuplicateFirstNames() 141 { 142 // In this test we want to find people who have the same first name. To determine this 143 // we need to generate a SQL statement that cross joins the PERSON table on itself and searches 144 // for rows where the first name is the same but the primary key is not the same. 145 146 // Create a new query that returns instances of the Person class. 147 Query<Person> personQuery = new Query<Person>(); 148 149 // This criteria finds rows where the Person._firstname field is equal to the cross joined Person._firstname field. 150 // The 'CrossJoin' in the reference path tells DomainObjects to create a cross join between the type on the left 151 // and the type on the right. Because the types on the left and the right of the cross join are the same (in this case 'Person'), 152 // DomainObjects generates a table alias for the second type. 153 // 154 // Note that the AddEqualToField constraint combined with the CrossJoin enables you to define 'custom joins' in 155 // your query; in other words, with this mechanism you can join any two tables on any two fields. 156 personQuery.Where.AddEqualTo(Types.Person.Field._firstname, Types.Person.CrossJoin.Person.Field._firstname); 157 158 // This criteria finds rows where the Person._primaryKey field is NOT equal to the cross joined Person._primaryKey field 159 personQuery.Where.AddNotEqualTo(Types.Person.Field._primaryKey, Types.Person.CrossJoin.Person.Field._primaryKey); 160 161 // Execute the query 162 List<Person> peopleWithSameFirstNames = personQuery.FindObjectSet(); 163 164 // DomainObjects converts the personQuery to the following SQL SELECT statement: 165 // 166 // SELECT PERSON01.ID AS PERSON01_ID, 167 // PERSON01.FIRSTNAME AS PERSON01_FIRSTNAME, 168 // PERSON01.LASTNAME AS PERSON01_LASTNAME 169 // 170 // FROM PERSON AS PERSON01 171 // CROSS JOIN PERSON AS PERSON12 172 // 173 // WHERE ((PERSON01.FIRSTNAME = PERSON12.FIRSTNAME 174 // AND PERSON01.ID <> PERSON12.ID)) 175 176 177 Assert.AreEqual(2, peopleWithSameFirstNames.Count, "Found two people with the same first name."); 178 } 179 180 [Test] 181 public void BuildOrCriterias() 182 { 183 Criteria criteriaHolder = new Criteria(); 184 185 // build an or criteria 186 Criteria orCriteriaOne = new Criteria(); 187 orCriteriaOne.AddEqualTo(Types.Person.Field._firstname, "tom"); 188 criteriaHolder.AddOrCriteria(orCriteriaOne); 189 190 // build a second or criteria 191 Criteria orCriteriaTwo = new Criteria(); 192 orCriteriaTwo.AddEqualTo(Types.Person.Field._firstname, "bob"); 193 criteriaHolder.AddOrCriteria(orCriteriaTwo); 194 195 criteriaHolder.AddEqualTo(Types.Person.Field._firstname, "albert"); 196 197 int count = QueryFacade.GetCount<Person>(criteriaHolder); 198 199 Assert.AreEqual(4, count, "Found four persons with the given names."); 200 } 201 202 [Test] 203 public void Union() 204 { 205 Query<Person> queryOne = new Query<Person>(); 206 Criteria criteriaOne = new Criteria(); 207 criteriaOne.AddEqualTo(Types.Person.Field._firstname, "bob"); 208 queryOne.Where = criteriaOne; 209 210 Query<Person> queryTwo = new Query<Person>(); 211 Criteria criteriaTwo = new Criteria(); 212 criteriaTwo.AddEqualTo(Types.Person.Field._firstname, "albert"); 213 queryTwo.Where = criteriaTwo; 214 215 queryOne.AddUnion(queryTwo); 216 217 DataSet dataSet = queryOne.FindDataSet(); 218 219 Assert.IsNotNull(dataSet); 220 Assert.AreEqual(2, dataSet.Tables[0].Rows.Count); 221 } 222 223 [Test] 224 public void BuildComplexCriteria() 225 { 226 Criteria criteriaHolder = new Criteria(); 227 228 // build an and criteria 229 Criteria andCriteriaOne = new Criteria(); 230 andCriteriaOne.AddEqualTo(Types.Person.Field._firstname, "tom"); 231 232 // build an or criteria 233 Criteria orCriteria = new Criteria(); 234 Criteria andCriteriaInsideOrOne = new Criteria(); 235 andCriteriaInsideOrOne.AddEqualTo(Types.Person.Field._firstname, "bob"); 236 Criteria andCriteriaInsideOrTwo = new Criteria(); 237 andCriteriaInsideOrTwo.AddEqualTo(Types.Person.Field._primaryKey, 2); 238 orCriteria.AddAndCriteria(andCriteriaInsideOrOne); 239 orCriteria.AddAndCriteria(andCriteriaInsideOrTwo); 240 241 criteriaHolder.AddAndCriteria(andCriteriaOne); 242 criteriaHolder.AddOrCriteria(orCriteria); 243 244 int count = QueryFacade.GetCount<Person>(criteriaHolder); 245 246 Assert.AreEqual(3, count, "Found three persons with the given names."); 247 } 248 249 [Test] 250 public void FindDuplicateFirstNamesAlongWithAnOrCriteria() 251 { 252 try 253 { 254 Criteria criteria = new Criteria(); 255 256 criteria.AddNotEqualTo(Types.Person.Field._primaryKey, Types.Person.CrossJoin.Person.Field._primaryKey); 257 258 criteria.AddEqualTo(Types.Person.Field._firstname, Types.Person.CrossJoin.Person.Field._firstname); 259 260 // constrain the last name 261 Criteria lastNameCriteria = new Criteria(); 262 lastNameCriteria.AddEqualTo(Types.Person.Field._lastname, "hanks"); 263 264 Criteria aliasLastNameCriteria = new Criteria(); 265 aliasLastNameCriteria.AddEqualTo(Types.Person.CrossJoin.Person.Field._lastname, "hanks"); 266 267 lastNameCriteria.AddOrCriteria(aliasLastNameCriteria); 268 269 criteria.AddAndCriteria(lastNameCriteria); 270 271 List<Person> peopleWithSameFirstNames = QueryFacade.FindObjectSet<Person>(criteria); 272 273 Assert.AreEqual(2, peopleWithSameFirstNames.Count, "Found two people with the same first name where at least one of them has a last name of 'hanks'."); 274 } 275 catch (Exception ex) 276 { 277 Fail(ex); 278 } 279 } 280 281 [Test] 282 public void GroupBy() 283 { 284 Criteria criteria = new Criteria(); 285 286 criteria.AddGroupBy(Types.Role.Field._rolename.Upper()); 287 288 DataSet dataSet = QueryFacade.FindDataSet<Role>(criteria, Types.Role.Field._rolename.Upper().As("Role")); 289 290 Assert.IsNotNull(dataSet); 291 Assert.AreEqual(6, dataSet.Tables[0].Rows.Count); 292 293 string columnName = dataSet.Tables[0].Columns[0].ColumnName; 294 295 //You dont know the order 296 //Assert.AreEqual("developer", dataSet.Tables[0].Rows[0].ItemArray.GetValue(0)); 297 //Assert.AreEqual("lead", dataSet.Tables[0].Rows[3].ItemArray.GetValue(0)); 298 Assert.AreEqual(1, dataSet.Tables[0].Select(String.Format("{0} = '{1}'", columnName, "DEVELOPER")).Length); 299 Assert.AreEqual(1, dataSet.Tables[0].Select(String.Format("{0} = '{1}'", columnName, "LEAD")).Length); 300 } 301 302 [Test] 303 public void QueryWithFunction() 304 { 305 Criteria criteria = new Criteria(); 306 307 criteria.AddEqualTo(Types.Person.Field._firstname, "%o%"); 308 309 DataSet dataSet = QueryFacade.FindDataSet<Person>(criteria, Types.Person.Field._firstname.Count()); 310 311 Assert.IsNotNull(dataSet); 312 Assert.AreEqual(6, Convert.ToInt32(dataSet.Tables[0].Rows[0].ItemArray.GetValue(0))); // MySql returns 'long', SQL Server returns 'int' 313 } 314 315 [Test] 316 public void QueryWithFunctionAndGroupBy() 317 { 318 Criteria criteria = new Criteria(); 319 320 criteria.AddGroupBy(Types.Role.Field._rolename); 321 322 DataSet dataSet = QueryFacade.FindDataSet<Role>(criteria, Types.Role.Field._rolename.Count()); 323 324 Assert.IsNotNull(dataSet); 325 326 Assert.AreEqual(6, dataSet.Tables[0].Rows.Count); 327 328 //YOU DONT KNOW THE ORDER 329 // there are 4 developers 330 //Assert.AreEqual(4, Convert.ToInt32(dataSet.Tables[0].Rows[0].ItemArray.GetValue(0))); // MySql returns 'long', SQL Server returns 'int' 331 //Assert.AreEqual("developer", dataSet.Tables[0].Rows[0].ItemArray.GetValue(1)); 332 333 string columnName = dataSet.Tables[0].Columns[1].ColumnName; 334 335 Assert.AreEqual(4, Convert.ToInt32(dataSet.Tables[0].Select(String.Format("{0} = '{1}'", columnName, "developer"))[0][0])); // MySql returns 'long', SQL Server returns 'int' 336 //Assert.AreEqual("developer", dataSet.Tables[0].Rows[0].ItemArray.GetValue(1)); 337 338 // there are 2 leads 339 //Assert.AreEqual(2, Convert.ToInt32(dataSet.Tables[0].Rows[3].ItemArray.GetValue(0))); // MySql returns 'long', SQL Server returns 'int' 340 Assert.AreEqual(2, Convert.ToInt32(dataSet.Tables[0].Select(String.Format("{0} = '{1}'", columnName, "lead"))[0][0])); // MySql returns 'long', SQL Server returns 'int' 341 //Assert.AreEqual("lead", dataSet.Tables[0].Rows[3].ItemArray.GetValue(1)); 342 } 343 344 [Test] 345 public void LikeCriteria() 346 { 347 Criteria criteria = new Criteria(); 348 criteria.AddEqualTo(Types.Person.Field._firstname, "%o%"); 349 350 351 List<Person> results = QueryFacade.FindObjectSet<Person>(criteria); 352 Assert.IsNotNull(results); 353 Assert.Greater(results.Count, 0); 354 } 355 356 [Test] 357 public void NullCriteria() 358 { 359 Criteria criteria = new Criteria(); 360 criteria.AddIsNull(Types.Person.Field._firstname); 361 362 363 List<Person> results = QueryFacade.FindObjectSet<Person>(criteria); 364 Assert.IsNotNull(results); 365 Assert.AreEqual(0, results.Count); 366 } 367 368 [Test] 369 public void BetweenCriteria() 370 { 371 Criteria criteria = new Criteria(); 372 criteria.AddBetween(Types.Person.Field._primaryKey, 1, 5); 373 374 375 List<Person> results = QueryFacade.FindObjectSet<Person>(criteria); 376 Assert.IsNotNull(results); 377 Assert.AreEqual(5, results.Count); 378 } 379 380 [Test] 381 public void CountColumnField_WithDistinct() 382 { 383 int count = QueryFacade.GetCount<Person>(null, Types.Person.Field._firstname, true); 384 Assert.AreEqual(10, count); 385 } 386 387 [Test] 388 public void CountColumnField_WithoutDistinct() 389 { 390 int count = QueryFacade.GetCount<Person>(null, Types.Person.Field._firstname, false); 391 Assert.AreEqual(11, count); 392 } 393 394 /// <summary> 395 /// Validates that DomainObjects correctly retrieves values via a DataSet. 396 /// </summary> 397 [Test] 398 public void FindDataSet() 399 { 400 // Create a new instance of a query to retrieve 401 // values from the Person object. 402 Query<Person> personQuery = new Query<Person>(); 403 404 // Constrain the query to persons with primary keys between 1 and 5 405 personQuery.Where.AddBetween(Types.Person.Field._primaryKey, 1, 5); 406 407 // Order the rows by the person's first name 408 personQuery.Where.AddOrderBy(Types.Person.Field._firstname); 409 410 // Retrieve the person's first name in the data set 411 personQuery.AddSelectField(Types.Person.Field._firstname); 412 413 // Execute the query 414 DataSet dataSet = personQuery.FindDataSet(); 415 416 Assert.IsNotNull(dataSet, "A DataSet instance was returned."); 417 418 Assert.AreEqual(1, dataSet.Tables.Count, "The data set contains one data table."); 419 420 Assert.AreEqual(5, dataSet.Tables[0].Rows.Count, "5 persons were retrieved in the data set."); 421 422 Assert.AreEqual("albert", dataSet.Tables[0].Rows[0].ItemArray.GetValue(0), "The first column in the first row has a value of 'albert'."); 423 } 424 425 [Test] 426 public void FindDataSetWithConstant() 427 { 428 Criteria criteria = new Criteria(); 429 criteria.AddBetween(Types.Person.Field._primaryKey, 1, 5); 430 criteria.AddOrderBy(Types.Person.Field._firstname); 431 432 433 DataSet dataSet = QueryFacade.FindDataSet<Person>(criteria, Types.Person.Field._firstname, new Constant(1)); 434 Assert.IsNotNull(dataSet); 435 Assert.AreEqual(1, dataSet.Tables.Count); 436 Assert.AreEqual(5, dataSet.Tables[0].Rows.Count); 437 Assert.AreEqual("albert", dataSet.Tables[0].Rows[0].ItemArray.GetValue(0)); 438 Assert.AreEqual(1, dataSet.Tables[0].Rows[0].ItemArray.GetValue(1)); 439 } 440 441 [Test] 442 public void FindDataSetWithReferencePathFieldFromJoinedTable() 443 { 444 DataSet dataSet = QueryFacade.FindDataSet<Product>(null, Types.Product.Field._name, Types.Product._productCategory.Field._description); 445 Assert.IsNotNull(dataSet); 446 Assert.IsTrue(dataSet.Tables.Count > 0, "Found some rows."); 447 } 448 449 [Test] 450 public void FindDataSetWithTop() 451 { 452 Criteria criteria = new Criteria(); 453 criteria.AddBetween(Types.Person.Field._primaryKey, 1, 5); 454 criteria.AddOrderBy(Types.Person.Field._firstname); 455 456 457 DataSet dataSet = QueryFacade.FindDataSet<Person>(criteria, 2, Types.Person.Field._firstname); 458 Assert.IsNotNull(dataSet); 459 Assert.AreEqual(1, dataSet.Tables.Count); 460 Assert.AreEqual(2, dataSet.Tables[0].Rows.Count); 461 Assert.AreEqual("albert", dataSet.Tables[0].Rows[0].ItemArray.GetValue(0)); 462 } 463 464 [Test] 465 public void InCriteria() 466 { 467 Criteria criteria = new Criteria(); 468 List<object> ids = new List<object>(); 469 ids.Add(1); 470 ids.Add(3); 471 ids.Add(5); 472 criteria.AddIn(Types.Person.Field._primaryKey, ids); 473 List<Person> results = QueryFacade.FindObjectSet<Person>(criteria); 474 Assert.AreEqual(3, results.Count); 475 } 476 477 [Test] 478 public void InSubQueryCriteria() 479 { 480 Criteria criteria = new Criteria(); 481 Criteria productCriteria = new Criteria(); 482 productCriteria.AddEqualTo(Types.Product.Field._supplierId, 1); 483 484 criteria.AddInSubquery<Product>(Types.Person.Field._primaryKey, Types.Product.Field._productCategoryId, productCriteria); 485 List<Person> results = QueryFacade.FindObjectSet<Person>(criteria); 486 Assert.AreEqual(2, results.Count); 487 } 488 489 [Test] 490 public void InSubQuery() 491 { 492 ScalarQuery<Product> subQuery = new ScalarQuery<Product>(Types.Product.Field._productCategoryId); 493 subQuery.Where.AddEqualTo(Types.Product.Field._supplierId, 1); 494 495 Query<Person> outerQuery = new Query<Person>(); 496 outerQuery.Where.AddInSubquery(Types.Person.Field._primaryKey, subQuery); 497 List<Person> results = outerQuery.FindObjectSet(); 498 Assert.AreEqual(2, results.Count); 499 } 500 501 /// <summary> 502 /// Tests the case where a subquery is added 503 /// as part of a sub-criteria. Ensures that nested 504 /// subqueries get initialized. 505 /// </summary> 506 [Test] 507 public void InNestedSubQuery() 508 { 509 ScalarQuery<Product> subQuery = new ScalarQuery<Product>(Types.Product.Field._productCategoryId); 510 subQuery.Where.AddEqualTo(Types.Product.Field._supplierId, 1); 511 512 Query<Person> outerQuery = new Query<Person>(); 513 514 // Create a nested criteria 515 Criteria subCriteria = new Criteria(); 516 subCriteria.AddInSubquery(Types.Person.Field._primaryKey, subQuery); 517 outerQuery.Where.AddAndCriteria(subCriteria); 518 519 List<Person> results = outerQuery.FindObjectSet(); 520 521 Assert.AreEqual(2, results.Count); 522 } 523 524 [Test] 525 public void InCriteriaUsingArray() 526 { 527 Criteria criteria = new Criteria(); 528 int[] ids = new[] {1, 3, 5}; 529 criteria.AddIn(Types.Person.Field._primaryKey, ids); 530 List<Person> results = QueryFacade.FindObjectSet<Person>(criteria); 531 Assert.AreEqual(3, results.Count); 532 } 533 534 /// <summary> 535 /// This test must produce a SQL statement that looks something like: 536 /// 537 ///SELECT DISTINCT 538 /// A0.BASE_TYPE_ID AS A0_BASE_TYPE_ID, 539 /// A0.ROOT_OBJECT_ID AS A0_ROOT_OBJECT_ID, 540 /// A0.VALUE_IN_SUB_TYPE AS A0_VALUE_IN_SUB_TYPE 541 ///FROM SUB_TYPE A0 542 ///INNER JOIN 543 /// (BASE_TYPE A1 544 /// INNER JOIN 545 /// BASE_TYPE_REFERENCE A2 546 /// ON A1.BASE_TYPE_ID=A2.BASE_TYPE_ID 547 /// ) 548 /// ON A0.BASE_TYPE_ID=A1.BASE_TYPE_ID 549 ///INNER JOIN 550 /// ROOT_OBJECT A3 551 /// ON A0.ROOT_OBJECT_ID=A3.ROOT_OBJECT_ID 552 ///WHERE 553 /// ( 554 /// ( 555 /// ( 556 /// A1.VALUE_IN_BASE_TYPE = @p1 557 /// ) 558 /// AND A2.BASE_TYPE_REFERENCE_ID IS NOT NULL 559 /// ) 560 /// AND A1.BASE_TYPE_ID IS NOT NULL 561 /// ) 562 /// AND A3.ROOT_OBJECT_ID = @p2',N'@p1 varchar(32), 563 /// @p2 int',@p1='PathExpressionUnitTestInBaseType',@p2=84 564 /// 565 /// </summary> 566 [Test] 567 public void PathExpression() 568 { 569 // Delete all root objects 570 TransactionalService transactionalService = TransactionalService.Instance; 571 transactionalService.DeleteAllRootObjects(); 572 573 // Create the instances that we are going to query 574 RootObject rootObject = RootObject.Create(LegalValue.VALUE_THREE); 575 BaseType baseType = new BaseType("PathExpressionUnitTestInBaseType").Manage<BaseType>(); 576 SubType subType = new SubType(baseType, rootObject, "PathExpressionUnitTestInSubType").Manage<SubType>(); 577 BaseTypeReference baseTypeReference = new BaseTypeReference().Manage<BaseTypeReference>(); 578 baseTypeReference.BaseType = baseType; 579 PersistenceFacade.Persist(rootObject, baseType, subType, baseTypeReference); 580 581 Assert.IsFalse(rootObject.IsNew, "The rootObject instance is not new."); 582 583 // Clear the cache to ensure the test is going all the way to the database 584 ClearAppDomainAndContextCache(); 585 586 Criteria criteria = new Criteria(); 587 588 criteria.AddEqualTo(Types.SubType._baseType.Field._valueInBaseType, "PathExpressionUnitTestInBaseType"); 589 criteria.AddNotNull(Types.SubType._baseType._baseTypeReferences.Field._primaryKey); 590 criteria.AddNotNull(Types.SubType._baseType.Field._primaryKey); 591 criteria.AddEqualTo(Types.SubType._rootObject.Field._primaryKey, rootObject.PrimaryKey[0]); 592 593 SubType result = QueryFacade.FindObject<SubType>(criteria); 594 595 Assert.IsNotNull(result, "Found an instance of ISubType."); 596 } 597 598 [Test] 599 public void PathExpressionsMtoNDecomposed() 600 { 601 Criteria criteria = new Criteria(); 602 criteria.AddEqualTo(Types.Person._roles._project.Field._title, "HSQLDB"); 603 List<Person> results = QueryFacade.FindObjectSet<Person>(criteria); 604 Assert.Greater(results.Count, 0); 605 } 606 607 [Test] 608 public void PathExpressionsMtoN() 609 { 610 Criteria criteria = new Criteria(); 611 criteria.AddEqualTo(Types.User._customers.Field._name, "customerOne"); 612 List<User> results = QueryFacade.FindObjectSet<User>(criteria); 613 Assert.Greater(results.Count, 0); 614 } 615 616 [Test] 617 [Category(UnitTestCategory.SQLServerSpecific)] 618 [Category(UnitTestCategory.DoesNotWorkWithOracle)] 619 public void TwoFunctionsOnAField() 620 { 621 Criteria criteria = new Criteria(); 622 623 QueryFacade.FindDataSet<Planet>(criteria, Types.Planet.Field._rotationalPeriod.Sum()); 624 QueryFacade.FindDataSet<Planet>(criteria, Types.Planet.Field._rotationalPeriod.Sum().ReplaceNull(0)); 625 } 626 627 [Test] 628 public void ReplaceNullFunctionWithStringValues() 629 { 630 Criteria criteria = new Criteria(); 631 criteria.AddEqualTo(Types.Person.Field._firstname, "bob"); 632 633 Assert.AreEqual("No Name", QueryFacade.FindDataSet<Person>(criteria, Types.Person.Field._lastname.ReplaceNull("'No Name'")).Tables[0].Rows[0][0], "The lastname is 'No Name'"); 634 } 635 636 /// <summary> 637 /// This test verifies that only SpanishProductCategory instances are 638 /// returned per the assertion in ObjectBuilder 'classToFind.IsAssignableFrom(newlyBuiltInstance.GetType())' 639 /// </summary> 640 [Test] 641 public void QueryForSubClass() 642 { 643 QueryFacade.FindObjectSet<SpanishProductCategory>(new Criteria()); 644 } 645 646 [Test] 647 public void Having() 648 { 649 Query<Person> query = new Query<Person>(); 650 651 Criteria whereCriteria = new Criteria(); 652 whereCriteria.AddGroupBy(Types.Person.Field._firstname); 653 query.Where = whereCriteria; 654 655 Criteria havingCriteria = new Criteria(); 656 havingCriteria.AddGreaterThan(Types.Person.Field._primaryKey.Average(), 5); 657 query.Having = havingCriteria; 658 659 query.AddSelectField(Types.Person.Field._firstname); 660 661 DataSet dataSet = query.FindDataSet(); 662 663 Assert.AreEqual(6, dataSet.Tables[0].Rows.Count); 664 } 665 666 [Test] 667 public void LongStringsAreAllowedInWhereClauses() 668 { 669 // Create a new instance of Criteria 670 Criteria criteria = new Criteria(); 671 672 //LONG VARCHARS ARE ALLOWED 673 criteria.AddLike(Types.Person.Field._firstname, "%01234567890123456789012345678901234567890123456789%"); 674 675 Person person = QueryFacade.FindObject<Person>(criteria); 676 677 Assert.IsNull(person, "There is no person with a name like %01234567890123456789012345678901234567890123456789%"); 678 679 680 //LONG CHARS ARE ALLOWED 681 criteria = new Criteria(); 682 criteria.AddLike(Types.Book.Field._isbn, "%1234567890%"); 683 684 Book book = QueryFacade.FindObject<Book>(criteria); 685 686 Assert.IsNotNull(book, "There is a book with the ISBN like %1234567890%"); 687 } 688 689 [Test] 690 public void VarCharsAreFilteres() 691 { 692 Criteria criteria = new Criteria(); 693 criteria.AddEqualTo(Types.Book.Field._name, "%60 Characters Book Book Book Book Book Book Book Book Book B%"); 694 695 DataSet dataSet = QueryFacade.FindDataSet<Book>(criteria, 1, new Field[] {}); 696 697 Assert.AreEqual(1, dataSet.Tables[0].Rows.Count, "There is a book named '60 Characters Book Book Book Book Book Book Book Book Book B'"); 698 } 699 700 [Test] 701 public void ShortCharsAreFiltered() 702 { 703 // Create a new instance of Criteria 704 Criteria criteria = new Criteria(); 705 criteria.AddEqualTo(Types.Book.Field._isbn, "555"); 706 707 Book book = QueryFacade.FindObject<Book>(criteria); 708 709 Assert.IsNotNull(book, "There is a book with the ISBN equal to '555'"); 710 } 711 712 [Test] 713 public void InlineCriteria() 714 { 715 DataSet dataSet = QueryFacade.FindDataSet<Person>(new Criteria().AddBetween(Types.Person.Field._primaryKey, 1, 5).AddOrderBy(Types.Person.Field._firstname), Types.Person.Field._firstname, new Constant(1)); 716 Assert.IsNotNull(dataSet); 717 Assert.AreEqual(1, dataSet.Tables.Count); 718 Assert.AreEqual(5, dataSet.Tables[0].Rows.Count); 719 Assert.AreEqual("albert", dataSet.Tables[0].Rows[0].ItemArray.GetValue(0)); 720 Assert.AreEqual(1, dataSet.Tables[0].Rows[0].ItemArray.GetValue(1)); 721 } 722 723 724 /// <summary> 725 /// Validates that concrete class constraints are not added 726 /// to the where clause if the class being queried is the base 727 /// most class mapped to a table and is the only base class mapped 728 /// to that table. 729 /// </summary> 730 [Test] 731 public void QueryAgainstBaseMostClass() 732 { 733 /****** Perform a query that should not cause a concrete class contraint to be generated *******/ 734 // instantiate the event handler 735 CommandMonitor commandMonitor = new CommandMonitor(); 736 737 Criteria criteria = new Criteria(); 738 criteria.AddEqualTo(Types.ProductCategory.Field._name, "Liquors"); 739 QueryFacade.FindObjectSet<ProductCategory>(criteria); 740 Assert.IsTrue(!commandMonitor.DatabaseEventArgList[0].CommandText.Contains(" IN "), "The concrete class has not been constrained by the query; it does not contain an 'IN' clause."); 741 DatabaseSession.CurrentDatabaseSession.DatabaseAccessEvent -= commandMonitor.HandleDatabaseAccess; 742 743 /****** Now perform a query that should cause a concrete class contraint to be generated *******/ 744 // instantiate the event handler 745 commandMonitor = new CommandMonitor(); 746 criteria = new Criteria(); 747 criteria.AddEqualTo(Types.AcousticGuitar.Field._primaryKey, 2); 748 QueryFacade.FindObject<AcousticGuitar>(criteria); 749 Assert.IsTrue(commandMonitor.DatabaseEventArgList[0].CommandText.Contains(" IN "), "The concrete class has been constrained by the query; it does contain an 'IN' clause."); 750 DatabaseSession.CurrentDatabaseSession.DatabaseAccessEvent -= commandMonitor.HandleDatabaseAccess; 751 } 752 753 754 [Test] 755 public void QueryWithDuplicatedPropertyName() 756 { 757 Criteria criteria = new Criteria(); 758 criteria.AddOrderBy(Types.Product._productCategory.Field._name, false); 759 760 QueryFacade.FindDataSet<Product>(null, 20, new Field[] {Types.Product.Field._name, Types.Product._productCategory.Field._name}); 761 } 762 763 /// <summary> 764 /// Validates that a query can be constrained on a field 765 /// that only exists in a sub class in a reference path. 766 /// </summary> 767 [Test] 768 public void ConstrainQueryOnFieldInSubClass() 769 { 770 // Create an instance of a 'ProductCategory' that 771 // is related to an instance of 'AcousticGuitar' 772 ProductCategory productCategory = new ProductCategory("Nylon String Guitars", "Acoustic Guitars that have nylon strings"); 773 AcousticGuitar acousticGuitar = new AcousticGuitar(productCategory, "McPherson", 6, "Nylon Classical(N) body shape"); 774 775 // Persist the new category and guitar 776 PersistenceFacade.Persist(productCategory, acousticGuitar); 777 778 // Now find the product category related to the acoustic 779 // guitar with the given body description. The '_bodyDescription' 780 // field only exists in the AcousticGuitar sub class, so we need 781 // to navigate from _guitarsInThisCategory -> SubClass -> AcousticGuitar 782 // to access this field. 783 Query<ProductCategory> query = new Query<ProductCategory>(); 784 query.Where.AddEqualTo(Types.ProductCategory._guitarsInThisCategory.SubClass.AcousticGuitar.Field._bodyDescription, "Nylon Classical(N) body shape"); 785 786 // Execute the query 787 ProductCategory productCategoryFromDatabase = query.FindObject(); 788 789 // Validate that we found the instance via the query 790 Assert.IsNotNull(productCategoryFromDatabase, "Found the product category."); 791 792 // From the above query, DomainObjects generates the following SQL SELECT statement: 793 // 794 // SELECT PRODUCT_CATEGORY01.PRODUCT_CATEGORY_ID AS PRODUCT_CATEGORY01_PRODUCT_CATEGORY_ID, 795 // PRODUCT_CATEGORY01.NAME AS PRODUCT_CATEGORY01_NAME, 796 // PRODUCT_CATEGORY01.DESCRIPTION AS PRODUCT_CATEGORY01_DESCRIPTION, 797 // PRODUCT_CATEGORY01.CONCRETE_CLASS AS PRODUCT_CATEGORY01_CONCRETE_CLASS, 798 // PRODUCT_CATEGORY01.SPANISH_DESCRIPTION AS PRODUCT_CATEGORY01_SPANISH_DESCRIPTION 799 // FROM PRODUCT_CATEGORY AS PRODUCT_CATEGORY01 800 // INNER JOIN GUITARS AS GUITARS12 801 // ON PRODUCT_CATEGORY01.PRODUCT_CATEGORY_ID=GUITARS12.PRODUCT_CATEGORY_ID 802 // WHERE ((GUITARS12.BodyDesc = @GUITARS12_BodyDesc_0) 803 // AND ((GUITARS12.ConcreteClass IN ( @GUITARS12_ConcreteClass_1, @GUITARS12_ConcreteClass_2)))) 804 // 805 // @GUITARS12_BodyDesc_0='Nylon Classical(N) body shape' 806 // @GUITARS12_ConcreteClass_1='DomainObjects.Test.Domain.AcousticGuitar' 807 // @GUITARS12_ConcreteClass_2='DomainObjects.Test.Domain.SpanishAcousticGuitar' 808 } 809 810 /// <summary> 811 /// Validates that multiple aliases are automatically 812 /// generated when the related class subclasses are not 813 /// assignable from each other. 814 /// </summary> 815 [Test] 816 public void ConstrainQueryOnDifferentRelatedSubClasses() 817 { 818 // Find the instance of ProductCategory that has 819 // an AcousticGuitar manufactured by 'Ovation' and 820 // an ElectricGuitar manufactured by 'Fender'. 821 // 822 // The subclasses 'AcousticGuitar' and 'ElectricGuitar' 823 // are mutually exclusive: they will never be in the same 824 // row, so DomainObjects needs to create two aliases for 825 // the GUITAR table to meet the semantics of the query. 826 Query<ProductCategory> productCategoryQuery = new Query<ProductCategory>(); 827 productCategoryQuery.Where.AddEqualTo(Types.ProductCategory._guitarsInThisCategory.Field._productCategoryId, 9); 828 productCategoryQuery.Where.AddEqualTo(Types.ProductCategory._guitarsInThisCategory.SubClass.AcousticGuitar.Field._manufacturer, "Ovation"); 829 productCategoryQuery.Where.AddEqualTo(Types.ProductCategory._guitarsInThisCategory.SubClass.ElectricGuitar.Field._manufacturer, "Fender"); 830 831 ProductCategory productCategory = productCategoryQuery.FindObject(); 832 833 Assert.IsNotNull(productCategory, "Found an instance of ProductCategory."); 834 Assert.AreEqual("Guitars", productCategory.Name, "The 'Guitars' product category was found."); 835 } 836 837 /// <summary> 838 /// Validates that connections are closed and therefore returned to the connection pool. 839 /// </summary> 840 [Category(UnitTestCategory.SQLServerSpecific)] 841 [Category(UnitTestCategory.DoesNotWorkWithOracle)] 842 [Test] 843 public void ConnectionsAreClosedForGetDataSetCount() 844 { 845 SqlConnection.ClearAllPools(); 846 847 Dictionary<ConnectionPoolPerformanceCounterName, PerformanceCounter> performanceCounters = SetUpPerformanceCounters(); 848 849 try 850 { 851 for (int numberOfStatementsExecuted = 1; numberOfStatementsExecuted < 21; numberOfStatementsExecuted++) 852 { 853 GetDataSetCountOutsideOfTransaction(); 854 ValidateConnectionUsage(performanceCounters); 855 } 856 } 857 finally 858 { 859 ClosePerformanceCounters(performanceCounters); 860 } 861 } 862 863 /// <summary> 864 /// Validates that connections are closed and therefore returned to the connection pool. 865 /// </summary> 866 [Category(UnitTestCategory.SQLServerSpecific)] 867 [Category(UnitTestCategory.DoesNotWorkWithOracle)] 868 [Test] 869 public void ConnectionsAreClosedForFindObject() 870 { 871 SqlConnection.ClearAllPools(); 872 873 Dictionary<ConnectionPoolPerformanceCounterName, PerformanceCounter> performanceCounters = SetUpPerformanceCounters(); 874 875 try 876 { 877 for (int numberOfStatementsExecuted = 1; numberOfStatementsExecuted < 21; numberOfStatementsExecuted++) 878 { 879 FindObjectOutsideOfTransaction(); 880 ValidateConnectionUsage(performanceCounters); 881 } 882 } 883 finally 884 { 885 ClosePerformanceCounters(performanceCounters); 886 } 887 } 888 889 /// <summary> 890 /// Validates that connections are closed and therefore returned to the connection pool. 891 /// </summary> 892 [Category(UnitTestCategory.SQLServerSpecific)] 893 [Category(UnitTestCategory.DoesNotWorkWithOracle)] 894 [Test] 895 public void ConnectionsAreClosedForFailedInsert() 896 { 897 SqlConnection.ClearAllPools(); 898 899 Dictionary<ConnectionPoolPerformanceCounterName, PerformanceCounter> performanceCounters = SetUpPerformanceCounters(); 900 901 try 902 { 903 for (int numberOfStatementsExecuted = 1; numberOfStatementsExecuted < 50; numberOfStatementsExecuted++) 904 { 905 CauseFailedInsertStatement(); 906 ValidateConnectionUsage(performanceCounters); 907 } 908 } 909 finally 910 { 911 ClosePerformanceCounters(performanceCounters); 912 } 913 } 914 915 /// <summary> 916 /// Validates that connections are closed and therefore returned to the connection pool. 917 /// </summary> 918 [Category(UnitTestCategory.SQLServerSpecific)] 919 [Category(UnitTestCategory.DoesNotWorkWithOracle)] 920 [Test] 921 public void ConnectionsAreClosedForFindObjectSet() 922 { 923 SqlConnection.ClearAllPools(); 924 925 Dictionary<ConnectionPoolPerformanceCounterName, PerformanceCounter> performanceCounters = SetUpPerformanceCounters(); 926 927 try 928 { 929 for (int numberOfStatementsExecuted = 1; numberOfStatementsExecuted < 21; numberOfStatementsExecuted++) 930 { 931 FindObjectSetOutsideOfTransaction(); 932 ValidateConnectionUsage(performanceCounters); 933 } 934 } 935 finally 936 { 937 ClosePerformanceCounters(performanceCounters); 938 } 939 } 940 941 /// <summary> 942 /// Validates that connections are closed and therefore returned to the connection pool. 943 /// </summary> 944 [Category(UnitTestCategory.SQLServerSpecific)] 945 [Category(UnitTestCategory.DoesNotWorkWithOracle)] 946 [Test] 947 public void ConnectionsAreClosedForFindDataSet() 948 { 949 SqlConnection.ClearAllPools(); 950 951 Dictionary<ConnectionPoolPerformanceCounterName, PerformanceCounter> performanceCounters = SetUpPerformanceCounters(); 952 953 try 954 { 955 for (int numberOfStatementsExecuted = 1; numberOfStatementsExecuted < 21; numberOfStatementsExecuted++) 956 { 957 FindDataSetOutsideOfTransaction(); 958 ValidateConnectionUsage(performanceCounters); 959 } 960 } 961 finally 962 { 963 ClosePerformanceCounters(performanceCounters); 964 } 965 } 966 967 private static void GetDataSetCountOutsideOfTransaction() 968 { 969 Query<Person> personQuery = new Query<Person>(); 970 personQuery.Where.AddEqualTo(Types.Person.Field._firstname, "tom"); 971 personQuery.AddSelectField(Types.Person.Field._lastname); 972 int count = personQuery.GetDataSetCount(); 973 Assert.IsTrue(count > 0, "A person with the first name of 'tom' was found."); 974 } 975 976 private static void FindObjectOutsideOfTransaction() 977 { 978 Query<Person> personQuery = new Query<Person>(); 979 personQuery.Where.AddEqualTo(Types.Person.Field._firstname, "tom"); 980 Person person = personQuery.FindObject(); 981 Assert.IsNotNull(person, "A person with the first name of 'tom' was found."); 982 } 983 984 private static void FindObjectSetOutsideOfTransaction() 985 { 986 Query<Person> personQuery = new Query<Person>(); 987 personQuery.Where.AddEqualTo(Types.Person.Field._firstname, "tom"); 988 List<Person> people = personQuery.FindObjectSet(); 989 Assert.IsTrue(people.Count > 1, "A person with the first name of 'tom' was found."); 990 } 991 992 private static void FindDataSetOutsideOfTransaction() 993 { 994 Query<Person> personQuery = new Query<Person>(); 995 personQuery.Where.AddEqualTo(Types.Person.Field._firstname, "tom"); 996 personQuery.FindDataSet(); 997 } 998 999 private static void CauseFailedInsertStatement() 1000 { 1001 Exception sqlException = null; 1002 try 1003 { 1004 // Create a product group 1005 ProductCategory productCategory = new ProductCategory("unitTestName", "unitTestDescription").Manage<ProductCategory>(); 1006 1007 // Create an product that will fail with the given product group that should succeed 1008 Product product = new Product(productCategory, "TooooooooooooooooooooooooooooooooooooooooooooooooooooooooooLooooooooooooooooooooooooong").Manage<Product>(); 1009 1010 // This statement should fail 1011 PersistenceFacade.Persist(productCategory, product); 1012 } 1013 catch (Exception ex) 1014 { 1015 sqlException = ex; 1016 } 1017 Assert.IsNotNull(sqlException, "The insert statement failed."); 1018 } 1019 1020 [DllImport("kernel32.dll", SetLastError = true)] 1021 private static extern int GetCurrentProcessId(); 1022 1023 private static string GetInstanceName() 1024 { 1025 // Must replace special characters like (, ), #, /, \\ 1026 string instanceName = AppDomain.CurrentDomain.FriendlyName.Replace('(', '[').Replace(')', ']').Replace('#', '_').Replace('/', '_').Replace('\\', '_'); 1027 1028 string processId = GetCurrentProcessId().ToString(); 1029 1030 return instanceName + "[" + processId + "]"; 1031 } 1032 1033 1034 private enum ConnectionPoolPerformanceCounterName 1035 { 1036 /// <summary> 1037 /// The number of emancipated connections the CLR reclaims. 1038 /// Emancipated connections happen when the object that 1039 /// owns the DBConnection goes out of scope. The CLR reclaims 1040 /// these connections when the pool runs out of connections 1041 /// or during normal thread cleanup. 1042 /// </summary> 1043 NumberOfReclaimedConnections, 1044 1045 /// <summary> 1046 /// The number of actual connections per second that are 1047 /// being made to a server (ignores pooled connections, 1048 /// only real database connection opens counted) 1049 /// </summary> 1050 HardConnectsPerSecond, 1051 1052 /// <summary> 1053 /// Current number of connections in all pools associated 1054 /// with the process or appdomain. 1055 /// </summary> 1056 NumberOfPooledConnections, 1057 1058 /// <summary> 1059 /// The number of connections ADO.NET gets from the pool per second. 1060 /// </summary> 1061 // ReSharper disable UnusedMemberInPrivateClass 1062 SoftConnectsPerSecond, 1063 // ReSharper restore UnusedMemberInPrivateClass 1064 1065 /// <summary> 1066 /// The number of connections in a pool that are currently in use. 1067 /// </summary> 1068 NumberOfActiveConnections, 1069 1070 /// <summary> 1071 /// The number of connections in a pool that are currently available for use. 1072 /// </summary> 1073 // ReSharper disable UnusedMemberInPrivateClass 1074 NumberOfFreeConnections 1075 // ReSharper restore UnusedMemberInPrivateClass 1076 } 1077 1078 private static Dictionary<ConnectionPoolPerformanceCounterName, PerformanceCounter> SetUpPerformanceCounters() 1079 { 1080 Dictionary<ConnectionPoolPerformanceCounterName, PerformanceCounter> performanceCounters = new Dictionary<ConnectionPoolPerformanceCounterName, PerformanceCounter>(); 1081 1082 foreach (ConnectionPoolPerformanceCounterName performanceCounterName in Enum.GetValues(typeof (ConnectionPoolPerformanceCounterName))) 1083 { 1084 PerformanceCounter performanceCounter = new PerformanceCounter(); 1085 performanceCounter.CategoryName = ".NET Data Provider for SqlServer"; 1086 performanceCounter.CounterName = performanceCounterName.ToString(); 1087 performanceCounter.InstanceName = GetInstanceName(); 1088 performanceCounters[performanceCounterName] = performanceCounter; 1089 } 1090 1091 return performanceCounters; 1092 } 1093 1094 private static void ClosePerformanceCounters(IDictionary<ConnectionPoolPerformanceCounterName, PerformanceCounter> performanceCounters) 1095 { 1096 foreach (ConnectionPoolPerformanceCounterName performanceCounterName in Enum.GetValues(typeof (ConnectionPoolPerformanceCounterName))) 1097 { 1098 performanceCounters[performanceCounterName].Close(); 1099 } 1100 } 1101 1102 /// <summary> 1103 /// Validates that we are returning connections to the pool and reusing them. 1104 /// </summary> 1105 /// <param name="performanceCounters">The performance counters.</param> 1106 private static void ValidateConnectionUsage(IDictionary<ConnectionPoolPerformanceCounterName, PerformanceCounter> performanceCounters) 1107 { 1108 Assert.AreEqual(0, performanceCounters[ConnectionPoolPerformanceCounterName.NumberOfReclaimedConnections].NextValue(), "Connections are not being garbage collected."); 1109 1110 Assert.AreEqual(0, performanceCounters[ConnectionPoolPerformanceCounterName.HardConnectsPerSecond].NextValue(), "New connections to the database are not being created."); 1111 1112 Assert.AreEqual(1, performanceCounters[ConnectionPoolPerformanceCounterName.NumberOfPooledConnections].NextValue(), "DomainObjects is reusing the connections from the pool."); 1113 1114 Assert.AreEqual(0, performanceCounters[ConnectionPoolPerformanceCounterName.NumberOfActiveConnections].NextValue(), "DomainObjects is reusing the same connection from the pool."); 1115 1116 // Commented out because there seems to be a timing issue when the unit tests are run via the command line. Maybe the connection does not get freed before NextValue() is called... 1117 //Assert.AreEqual(1, performanceCounters[ConnectionPoolPerformanceCounterName.NumberOfFreeConnections].NextValue(), "The one connection in the pool is now ready for reuse."); 1118 } 1119 1120 #region Query Count Tests 1121 1122 /// <summary> 1123 /// Executes <see cref="Query{ClassToFind}.GetObjectSetCount()"/> against 1124 /// a class with a single primary key column. 1125 /// </summary> 1126 [Test] 1127 public void GetObjectSetCount_FromClassWithSinglePrimaryKeyColumn() 1128 { 1129 // Create a query 1130 Query<Product> productQuery = new Query<Product>(); 1131 productQuery.Where.AddEqualTo(Types.Product._productCategory.Field._name, "Dairy Products"); 1132 1133 // Track the commands executed against the database 1134 CommandMonitor commandMonitor = new CommandMonitor(); 1135 1136 int count = productQuery.GetObjectSetCount(); 1137 1138 // Validate that the expected SQL statement was generated. 1139 Assert.IsTrue(commandMonitor.FirstCommandText.Contains("SELECT COUNT(DISTINCT Product02.PRODUCT_ID)"), "A 'SELECT COUNT(DISTINCT _primaryKey)' SQL statement was generated."); 1140 1141 Assert.AreEqual(16, count, "The correct count was returned."); 1142 } 1143 1144 /// <summary> 1145 /// Executes <see cref="Query{ClassToFind}.GetObjectSetCount()"/> against 1146 /// a class with multiple primary key columns 1147 /// </summary> 1148 [Category(UnitTestCategory.SQLServerSpecific)] 1149 [Category(UnitTestCategory.DoesNotWorkWithOracle)] 1150 [Test] 1151 public void GetObjectSetCount_FromClassWithMultiplePrimaryKeyColumns() 1152 { 1153 // Create a query 1154 Query<Role> roleQuery = new Query<Role>(); 1155 roleQuery.Where.AddEqualTo(Types.Role._person.Field._lastname, "thumbs"); 1156 1157 // Track the commands executed against the database 1158 CommandMonitor commandMonitor = new CommandMonitor(); 1159 1160 int count = roleQuery.GetObjectSetCount(); 1161 1162 // Validate that the expected SQL statement was generated. 1163 Assert.IsTrue(commandMonitor.FirstCommandText.Contains("SELECT DISTINCT Role02.PERSON_ID AS Role02_PERSON_ID2,Role02.PROJECT_ID AS Role02_PROJECT_ID4 FROM "), "A distinct primary key colums SQL statement was generated."); 1164 1165 Assert.AreEqual(2, count, "The correct count was returned."); 1166 } 1167 1168 /// <summary> 1169 /// Executes <see cref="Query{ClassToFind}.GetDataSetCount()"/> against a query 1170 /// with a single select field specified. 1171 /// </summary> 1172 [Test] 1173 public void GetDataSetCount_WithSingleSelectFieldSpecified() 1174 { 1175 // Create a query 1176 Query<Product> productQuery = new Query<Product>(); 1177 productQuery.Where.AddEqualTo(Types.Product._productCategory.Field._name, "Dairy Products"); 1178 1179 // Add a single field to select 1180 productQuery.AddSelectField(Types.Product.Field._name); 1181 1182 // Track the commands executed against the database 1183 CommandMonitor commandMonitor = new CommandMonitor(); 1184 1185 int count = productQuery.GetDataSetCount(); 1186 1187 // Validate that the expected SQL statement was generated. 1188 Assert.IsTrue(commandMonitor.FirstCommandText.Contains("SELECT COUNT(Product02.NAME) AS DerivedTable01_3 FROM"), "A 'SELECT COUNT(SelectField)' SQL statement was generated."); 1189 1190 Assert.AreEqual(16, count, "The correct count was returned."); 1191 } 1192 1193 /// <summary> 1194 /// Executes <see cref="Query{ClassToFind}.GetDataSetCount()"/> against a query 1195 /// with a no select fields specified. 1196 /// </summary> 1197 [Test] 1198 public void GetDataSetCount_WithNoSelectFieldsSpecified() 1199 { 1200 // Create a query 1201 Query<Product> productQuery = new Query<Product>(); 1202 productQuery.Where.AddEqualTo(Types.Product._productCategory.Field._name, "Dairy Products"); 1203 1204 // Track the commands executed against the database 1205 CommandMonitor commandMonitor = new CommandMonitor(); 1206 1207 int count = productQuery.GetDataSetCount(); 1208 1209 // Validate that the expected SQL statement was generated. 1210 Assert.IsTrue(commandMonitor.FirstCommandText.Contains("SELECT COUNT(*) AS DerivedTable01_2 FROM PRODUCT"), "A 'SELECT COUNT(*)' SQL statement was generated."); 1211 1212 Assert.AreEqual(16, count, "The correct count was returned."); 1213 } 1214 1215 #endregion 1216 1217 /// <summary> 1218 /// Tests <see cref="Query{ClassToFind}.Exists()"/> 1219 /// </summary> 1220 [Test] 1221 public void Exists() 1222 { 1223 // Create a query 1224 Query<Product> productQuery = new Query<Product>(); 1225 1226 productQuery.Where.AddEqualTo(Types.Product._productCategory.Field._name, "Dairy Products"); 1227 1228 Assert.IsTrue(productQuery.Exists(), "At least one product exists in the category of 'Dairy Products'"); 1229 } 1230 1231 /// <summary> 1232 /// Validates that DomainObjects does not add a IN clause that contains the 1233 /// set of sub type names if the type is constrained via an inner join. 1234 /// The inner join is sufficient to constrain the type. 1235 /// </summary> 1236 [Test] 1237 public void ConcreteTypeConstraintsNotAddedToQueryWhenQueryContainsInnerJoinUniqueToType() 1238 { 1239 Query<SpanishAcousticGuitar> guitarQuery = new Query<SpanishAcousticGuitar>(); 1240 // Constrain on the relationship with SpanishGuitarPart. This is sufficient 1241 // to constrain the SpanishAcousticGuitar type as the relationship is unique 1242 // to that sub type. 1243 guitarQuery.Where.AddEqualTo(Types.SpanishAcousticGuitar._parts.Field._partName, "Strings"); 1244 CommandMonitor commandMonitor = new CommandMonitor(); 1245 guitarQuery.FindObjectSet(); 1246 // Validate that the expected SQL statement was generated. 1247 Assert.IsTrue(!commandMonitor.FirstCommandText.Contains("'DomainObjects.Test.Domain.SpanishAcousticGuitar'"), "The SQL statement does not have a concrete type constrain clause."); 1248 1249 // Now perform a query against the same sub type but 1250 // without the unique join. The concrete type constraints via 1251 // the 'IN' clause should be added to the query. 1252 Query<SpanishAcousticGuitar> guitarQuery2 = new Query<SpanishAcousticGuitar>(); 1253 guitarQuery2.Where.AddEqualTo(Types.SpanishAcousticGuitar.Field._bodyDescription, "Strings"); 1254 CommandMonitor commandMonitor2 = new CommandMonitor(); 1255 guitarQuery2.FindObjectSet(); 1256 // Validate that the expected SQL statement was generated. 1257 Assert.IsTrue(commandMonitor2.FirstCommandText.Contains("'DomainObjects.Test.Domain.SpanishAcousticGuitar'"), "The SQL statement has a concrete type constrain clause."); 1258 } 1259 1260 [Test] 1261 public void INClauseAddedWhenInnerJoinIsNotUniqueToType() 1262 { 1263 // Setup the unit test data so that an instance 1264 // of SpanishGuitarPart and SpecialSpanishGuitarPart 1265 // are both related to the same instance of SpanishAcousticGuitar. 1266 // If I then query for just the SpecialSpanishGuitarPart, 1267 // only one of the two instances should be returned; i.e., 1268 // the inner join to SpanishAcousticGuitar is not sufficient 1269 // to constrain the SpecialSpanishGuitarPart type because the 1270 // join is not unique to that type. 1271 SpanishProductCategory spanishProductCategory = new SpanishProductCategory("Engraved Guitars", "Guitars that have engravings", "Las guitarras que tienen grabados").Manage<SpanishProductCategory>(); 1272 SpanishAcousticGuitar spanishAcousticGuitar = new SpanishAcousticGuitar(spanishProductCategory, "Ricardos Guitars", 10, "Floral Engraved Body").Manage<SpanishAcousticGuitar>(); 1273 1274 SpanishGuitarPart spanishGuitarPart = new SpanishGuitarPart(spanishAcousticGuitar, "headstock").Manage<SpanishGuitarPart>(); 1275 SpecialSpanishGuitarPart specialSpanishGuitarPart = new SpecialSpanishGuitarPart(spanishAcousticGuitar, "tuners").Manage<SpecialSpanishGuitarPart>(); 1276 1277 PersistenceFacade.Persist(spanishProductCategory, spanishAcousticGuitar, spanishGuitarPart, specialSpanishGuitarPart); 1278 1279 // Now perform a query against the same sub type but 1280 // without the unique join. The concrete type constraints via 1281 // the 'IN' clause should be added to the query. 1282 Query<SpecialSpanishGuitarPart> specialSpanishGuitarPartQuery = new Query<SpecialSpanishGuitarPart>(); 1283 specialSpanishGuitarPartQuery.Where.AddEqualTo(Types.SpecialSpanishGuitarPart._spanishGuitar.Field._primaryKey, spanishAcousticGuitar.PrimaryKey[0]); 1284 1285 CommandMonitor commandMonitor2 = new CommandMonitor(); 1286 1287 DataSet guitarParts = specialSpanishGuitarPartQuery.FindDataSet(); 1288 1289 Assert.AreEqual(1, guitarParts.Tables[0].Rows.Count, "Only one row was retrieved."); 1290 1291 // Validate that the expected SQL statement was generated. 1292 Assert.IsTrue(commandMonitor2.FirstCommandText.Contains("'DomainObjects.Test.Domain.SpecialSpanishGuitarPart'"), "The SQL statement has a concrete type constrain clause."); 1293 } 1294 1295 [Test] 1296 public void SameColumnFieldWithDifferentPathHasDifferentToStringValue() 1297 { 1298 Assert.AreNotEqual(Types.StorageHierarchy._storageChild.Field.name.ToString(), Types.StorageHierarchy._storageParent.Field.name.ToString(), "Types.StorageHierarchy._storageChild.Field.name is not equals to Types.StorageHierarchy._storageParent.Field.name"); 1299 } 1300 1301 [Test] 1302 public void ScalarSubqueryInWhereClause() 1303 { 1304 ScalarQuery<Product> maxPricePerUnit = new ScalarQuery<Product>(Types.Product.Field._pricePerUnit.Max()); 1305 1306 Query<Product> productQuery = new Query<Product>(); 1307 productQuery.Where.AddEqualTo(Types.Product.Field._pricePerUnit, maxPricePerUnit); 1308 1309 Product productWithMaxPricePerUnit = productQuery.FindObject(); 1310 1311 Assert.IsNotNull(productWithMaxPricePerUnit, "Should find exactly on Product instance with a maximum price."); 1312 } 1313 1314 [Test] 1315 public void ScalarSubqueryInSelectClause() 1316 { 1317 ScalarQuery<Product> maxPricePerUnit = new ScalarQuery<Product>(Types.Product.Field._pricePerUnit.Max()); 1318 1319 Query<Product> productQuery = new Query<Product>(); 1320 productQuery.Top = 1; 1321 productQuery.AddSelectField(maxPricePerUnit.As("MaxPrice")); 1322 1323 DataSet product = productQuery.FindDataSet(); 1324 1325 Query<Product> productQuery2 = new Query<Product>(); 1326 productQuery2.AddSelectField(Types.Product.Field._pricePerUnit.Max().As("MaxPrice")); 1327 DataSet maxPrice = productQuery2.FindDataSet(); 1328 1329 Assert.AreEqual(maxPrice.Tables[0].Rows[0].Field<Decimal>("MaxPrice"), 1330 product.Tables[0].Rows[0].Field<Decimal>("MaxPrice"), 1331 "The max price found without a subquery should match the max price found with a subquery."); 1332 } 1333 1334 [Test] 1335 public void ScalarSubqueryWithOrderByClause() 1336 { 1337 ScalarQuery<Product> mostUsedName = new ScalarQuery<Product>(Types.Product.Field._name); 1338 mostUsedName.Where.AddGroupBy(Types.Product.Field._name); 1339 mostUsedName.Where.AddOrderBy(new Count(), false); 1340 mostUsedName.Top = 1; 1341 1342 Query<Product> productQuery = new Query<Product>(); 1343 productQuery.AddSelectField(mostUsedName.As("MostUsedName")); 1344 1345 productQuery.FindDataSet(); 1346 } 1347 1348 [Test] 1349 public void QueryWithNullCriteria() 1350 { 1351 // Create a set of classes where the base-type matches 1352 // a constraint but the sub-type is more constrained 1353 SpanishProductCategory guitarCategory = new SpanishProductCategory(GenerateUniqueString(Types.ProductCategory.Field._name), 1354 GenerateUniqueString(Types.ProductCategory.Field._description), GenerateUniqueString(Types.SpanishProductCategory.Field._spanishDescription)); 1355 string manufacturerOne = GenerateUniqueString(Types.Guitar.Field._manufacturer); 1356 Guitar guitar = new Guitar(guitarCategory, manufacturerOne, 3); 1357 1358 string bodyDescription = GenerateUniqueString(Types.SpanishAcousticGuitar.Field._bodyDescription); 1359 SpanishAcousticGuitar acousticGuitar = new SpanishAcousticGuitar(guitarCategory, manufacturerOne, 3, bodyDescription); 1360 1361 PersistenceFacade.Persist(guitarCategory, guitar, acousticGuitar); 1362 1363 ClearAppDomainAndContextCache(); 1364 1365 Query<SpanishAcousticGuitar> spanishAcousticGuitarQuery = new Query<SpanishAcousticGuitar>(); 1366 spanishAcousticGuitarQuery.Where = null; 1367 spanishAcousticGuitarQuery.FindObjectSet(); 1368 // shouldn't throw an exception 1369 } 1370 1371 [Test] 1372 public void QueryWithStringConstantInSelectList() 1373 { 1374 // shouldn't throw an exception 1375 Query<Product> productQuery = new Query<Product>(); 1376 productQuery.Top = 1; 1377 productQuery.AddSelectField(new Constant("false").As("FalseString")); 1378 productQuery.FindDataSet(); 1379 1380 } 1381 1382 [Test] 1383 public void AliasedFieldInSelectListAndFieldInOrderBy() 1384 { 1385 // shouldn't throw an exception 1386 // An alias should not affect the check for equality 1387 Query<Product> productQuery = new Query<Product>(); 1388 productQuery.Top = 1; 1389 productQuery.AddSelectField(Types.Product.Field._currentStock.As("CurrentStock")); 1390 productQuery.Where.AddOrderBy(Types.Product.Field._currentStock); 1391 productQuery.FindDataSet(); 1392 } 1393 1394 [Category(UnitTestCategory.DoesNotWorkWithServerSideSequencing)] 1395 [Category(UnitTestCategory.DoesNotWorkWithOracle)] 1396 [Test] 1397 public void NestedSubqueriesInCase() 1398 { 1399 InsertByQuery<Product, ProductCategory> insertByQuery = new InsertByQuery<Product, ProductCategory>(); 1400 1401 insertByQuery.Top = 1; 1402 1403 insertByQuery.AddInsertField(Types.Product.Field._productCategoryId, 1); 1404 insertByQuery.AddInsertField(Types.Product.Field._name, GetProductNameCase()); 1405 1406 insertByQuery.Where.AddEqualTo(Types.ProductCategory.Field._name, "FooBar"); 1407 1408 insertByQuery.Execute(); 1409 } 1410 1411 private static Case GetProductNameCase() 1412 { 1413 Case productNameCase = new Case(); 1414 1415 productNameCase.AddCase(new Criteria().AddIsNull(GetProductNameQuery()), Types.ProductCategory.Field._name.Max()); 1416 productNameCase.AddElse(GetProductNameQuery()); 1417 1418 return productNameCase; 1419 } 1420 1421 private static ScalarQuery<CompactDisc> GetProductNameQuery() 1422 { 1423 ScalarQuery<CompactDisc> compactDiscQuery = new ScalarQuery<CompactDisc>(Types.CompactDisc.Field._name); 1424 1425 compactDiscQuery.Where.AddEqualTo(Types.CompactDisc.Field._labelname, "Ricardos Records"); 1426 1427 compactDiscQuery.Top = 1; 1428 1429 return compactDiscQuery; 1430 } 1431 1432 [Category(UnitTestCategory.DoesNotWorkWithServerSideSequencing)] 1433 [Category(UnitTestCategory.DoesNotWorkWithOracle)] 1434 [Test] 1435 public void EqualSubqueriesInSelectList() 1436 { 1437 Query<Product> productQuery = new Query<Product>(); 1438 1439 ScalarQuery<CompactDisc> compactDiscNameQuery = GetProductNameQuery(); 1440 1441 // Add compactDiscNameQuery as a select field 1442 productQuery.AddSelectField(compactDiscNameQuery); 1443 1444 ScalarQuery<CompactDisc> compactDiscQuery2 = new ScalarQuery<CompactDisc>(Types.CompactDisc.Field._name); 1445 1446 // Now nest compactDiscNameQuery inside another query 1447 compactDiscQuery2.Where.AddEqualTo(Types.CompactDisc.Field._labelname, compactDiscNameQuery); 1448 1449 productQuery.AddSelectField(compactDiscQuery2); 1450 1451 productQuery.Where.AddEqualTo(Types.Product.Field._name, "FooBar"); 1452 1453 productQuery.FindObjectSet(); 1454 } 1455 } 1456 }