http://ayende.comAyende @ RahienCopyright (C) Ayende Rahien 2004 - 2021 (c) 202660@Bob, Just use "data source=:memory:" in the connection string. Then you need to generate the schema, for that you will need to pass the IDbConnection to the SchemaExport, I added an Execute() method that does that. http://ayende.com/2457/working-around-sql-server-bugs#comment16http://ayende.com/2457/working-around-sql-server-bugs#comment16Sat, 02 Jun 2007 13:31:39 GMTNo, that is translation from HQL (object based) to SQL (table based) http://ayende.com/2457/working-around-sql-server-bugs#comment15http://ayende.com/2457/working-around-sql-server-bugs#comment15Thu, 31 May 2007 21:28:07 GMTyou may have mistyped stuff, but your query: from Address generated query: from Addresses that would be unknown... http://ayende.com/2457/working-around-sql-server-bugs#comment14http://ayende.com/2457/working-around-sql-server-bugs#comment14Thu, 31 May 2007 21:22:29 GMTCan you define what you mean? It is correct in that it is valid SQL. It is not correct in that SQL Server fails to run it. http://ayende.com/2457/working-around-sql-server-bugs#comment13http://ayende.com/2457/working-around-sql-server-bugs#comment13Thu, 31 May 2007 20:25:31 GMTisn't the first generated query incorrect though? http://ayende.com/2457/working-around-sql-server-bugs#comment12http://ayende.com/2457/working-around-sql-server-bugs#comment12Thu, 31 May 2007 19:47:41 GMT@Mr_Database, TSQL is a highly inefficent language to work with for a lot of scenarios. Building queries happens to be one of those. Being able to construct queries on the fly, based on business conditions and according to my domain is extremely powerful an enable me to do a lot of stuff that is really not trivial to do otherwise. http://ayende.com/2457/working-around-sql-server-bugs#comment11http://ayende.com/2457/working-around-sql-server-bugs#comment11Wed, 30 May 2007 13:13:17 GMTI'm currently testing against SQL2005 db rather than an in-memory db. I'd agree with Ayende that this approach is too costly - the whole tear-down/set up of dropping/creating the db is becoming annoying to the point where I don't want to run all my tests, which is a BAD THING. I reckon migrating my tests to SQLite will be very easy, just need to put 10 mins aside to work out how to install the thing and tweak the config for my testing assembly. http://ayende.com/2457/working-around-sql-server-bugs#comment10http://ayende.com/2457/working-around-sql-server-bugs#comment10Wed, 30 May 2007 12:29:26 GMTJust write TSQL if that is what the customer is running. Your stuff will always run and those left behind after you are long gone won't have to learn yet ANOTHER tool just to maintain your code. http://ayende.com/2457/working-around-sql-server-bugs#comment9http://ayende.com/2457/working-around-sql-server-bugs#comment9Wed, 30 May 2007 12:16:18 GMT@Jericho, I have integration tests to catch those type of differences, but I have a lot of tests that does various sorts of queries, and trying to do it against remote server would be really expensive. http://ayende.com/2457/working-around-sql-server-bugs#comment8http://ayende.com/2457/working-around-sql-server-bugs#comment8Wed, 30 May 2007 10:04:13 GMTCostly, Writing those unit tests (~30) involved many changes to the code, involving many changes to the schema, and evolved very quickly. Using in memory I just return the tests, using SQL Server from the start, I would have to re-gen the schema from scratch, which takes time. Certainly doable (that is how the NH tests are working, after all) just much more costly. http://ayende.com/2457/working-around-sql-server-bugs#comment7http://ayende.com/2457/working-around-sql-server-bugs#comment7Wed, 30 May 2007 04:40:26 GMTRoy, This is literally the first time that I had run into this issue, and the solution has turned out to be moving the location of a join, so I wouldn't consider this a major concession to living on several databases, I think that querying is a business concern, and as such, requires testing: http://www.ayende.com/Blog/archive/2007/03/09/Querying-is-a-business-concern.aspx http://ayende.com/2457/working-around-sql-server-bugs#comment6http://ayende.com/2457/working-around-sql-server-bugs#comment6Wed, 30 May 2007 04:06:53 GMTThat's one of the reasons why I don't support the notion of writing "unit tests" against a database, or the idea of mocking or replacing a DB for tests. You end up not testing the real DB, and you just might (like in your case) ed up with a different feature set just so it can live up bth to the "fake" and to the "real" database, a feature that was not needed. http://ayende.com/2457/working-around-sql-server-bugs#comment5http://ayende.com/2457/working-around-sql-server-bugs#comment5Wed, 30 May 2007 02:29:02 GMT> Would you mind posting the generated TSQL from your working solution? That is three pages worth, I would rather not :-) http://ayende.com/2457/working-around-sql-server-bugs#comment4http://ayende.com/2457/working-around-sql-server-bugs#comment4Tue, 29 May 2007 21:20:35 GMTI would probably start by testing without a DB at all, testing just the interaction described above, using mocks. But, if you have NHibernate running than it should be very easy to get it running on an in memory db. http://ayende.com/2457/working-around-sql-server-bugs#comment3http://ayende.com/2457/working-around-sql-server-bugs#comment3Tue, 29 May 2007 21:05:14 GMTDon't you prefer to perform all joins with join clauses using the "on" keyword? Or is this not an option with NHibernate? Would you mind posting the generated TSQL from your working solution? I should think the where clause would cancel out the effect of the "left" in the join, but I'm not sure of the rules for odd constructs like you've posted. http://ayende.com/2457/working-around-sql-server-bugs#comment2http://ayende.com/2457/working-around-sql-server-bugs#comment2Tue, 29 May 2007 21:03:36 GMTIt's good that you bring this up. Unfortunately I can't use an in-memory DB for unit tests at this time because we have old software that is wired to a crappy SQLBase database. We would like to migrate to SQL Server, which is one reason why we need more unit tests. One algorithm I'd like to test goes like this (it's actually something else, but you'll get the idea): A customer has a birthday (DateTime) and an age (int). We want to update the customer's age. So the algorithm goes like this: - Load a given customer. - If he doesn't have a birthday, send a message to the boss (create an entry in the MESSAGES table), telling him that an employee has forgotten to ask the customer for his birthday ;-). Abort the operation. - Calculate the new age. - If the new age differs from the old age, send a message to the developer (again, an entry in the MESSAGES table), telling him his code is crap ;-). - Finally, if his birthday was within last week, send him a gift, based upon his last order (evaluate last order, create new order). As you can see, there are quite a lot of tables involved. At least: CUSTOMERS EMPLOYEES MESSAGES ORDERS PRODUCTS ADDRESSES Futhermore, there are lots of methods involved (if StoreCustomer() fails, the whole operation will fail), which all will have to be tested. How do you do that? I could of course use an empty database and insert all objects I will need (including his last order) and delete everything again once I'm done. But as there are many columns etc., I'd prefer a different approach. Like configuring the database using the GUI of our application and then using that database for all unit tests, restoring it after each test. But how can you tell the unit test which customer to use in an elegant way? I can configure the customer ID in the application settings, but I still don't think that's the best approach. And (the actual reason why we've decided to add MANY more unit tests): We recently discovered a horrible bug in NullSafeGet in one of our custom NHibernate datatypes. NullSafeSet on the other hand worked correctly, so data was destroyed. I then wrote a unit test that loaded, stored and loaded data and compared the values. But even then you can never be sure you've screwed something up (columns in the wrong order in the mapping file etc.). How would you write a unit test for such a case? http://ayende.com/2457/working-around-sql-server-bugs#comment1http://ayende.com/2457/working-around-sql-server-bugs#comment1Tue, 29 May 2007 20:51:52 GMT