Posts Tagged “nhibernate”

MySQL error 105 - Phantom Table Menace

MySQL is weird The weirdest problem happened to a college today.

When creating the database schema during integration tests run, he got “Cannot create table FOO error 105” from MySQL.

There used to be a table named FOO with a VARCHAR primary key. The schema then changed so that the primary key of FOO became BIGINT. There is also a second table in the system (call it BAR) which has a foreign-key into FOO’s primary key. A classic master/details scenario.

However, the table BAR was obsoleted from the schema.

The integration tests runner is dropping all tables and recreating them before running the test suite. It is inferring the schema from the persisted classes using NHibernate’s mapper and the Schema creation feature of NHibernate. Sleeves up We cranked open the mysql console and started to look around:

• When doing “SHOW TABLES”, the FOO table was not listed.
• CREATE TABLE FOO (Id BIGINT) - fail with error 105.
• CREATE TABLE FOO (Id VARCHAR) – success !!
• huh?
• DROP TABLE FOO – success
• encouraging !
• CREATE TABLE FOO (Id BIGINT) - fail with error 105 – again
• huh ???
• DROP TABLE FOO – fail with “cannot delete … foreign key …”
• but SHOW TABLES still does not list FOO
• huh ?????
• DROP DATABASE dev; CREATE DATABASE dev;
• now everything works. Back to work Luckily this was not a production database, and even more lucky – the said DB change (change that PK from VARVHAR to BIGINT) would need to run on production within a separate DB instance that can be recreated on deploy.

And while we’re at it

Way can’t MySQL store non-indexed columns in an index?

NHibernate (and ActiveRecord’s) show_sql in web application

Looking into When using an OR/M of any kind, it is quite worthwhile to be able to look at the SQL generated by the tool, for various reasons (such as tuning the DB, finding SELECT N+1 issues, and sheer curiosity).

  Solution #1 One way of doing that is to start a profiler on the DB engine, but it has its downsides. For one, you would need a profiler tool, which is not always freely available. You might also not be able to access the DB engine with that kind of tool on various hosted environments.

In NHibernate’s configuration (and it is also exposed to Castle’s ActiveRecord users) you can set a property names “show_sql” to true. This will cause NHibernate to spit every SQL query, along with its parameters, onto the Console. Very useful when running Tests, but when running within a Web Application, you do not have access to the Console window, and can’t really see what is going on.

That also leads to another problem with using a profiler on the DB engine – you won’t be able to figure out which queries belong to which web request.

  Solution #2 One comprehensive solution is to use the excellent tool from Oren Eini – NhProf. I will not cover this tool here; it does lots of great stuff, and can help your development cycle. However not everyone will be willing to pay the price for using it.

not to worry, I hereby offer you two more options, which gives you less options, but are good enough for the problem at hand, and are free.

  Free solution #1 NHibernate is using log4net. it stores a lot of what it’s doing there. So, one can always setup a logger named “NHibernate.SQL” and get a hold of the queries. I do not cover log4net usage here. Google it up, and then set up the NHibernate.SQL logger.

You’d also want to setup a log4net variable for you to group the queries it gets by the web-request that needed them. You can do so by setting a property on log4net’s GlobalContext. e.g., in Application_BeginRequest:

log4net.GlobalContext.Properties["page_url"] = Context.Request.RawUrl + "|" + Guid.NewGuid();

then you’d set the log appender to write the page_url value, and you’ll be able to Group By it.

But this suck. You need to depend on log4net even if you do not want to, and setup that hack-ish global property, then read it from the log4net storage, and lots of complexities. wouldn’t it be great if you could simply got a hold of the Console’s output (or at least the NH parts of it?)

  Free solution #2 – even simpler The Console object allow you to set a custom Writer to its Out stream. Meaning, you can grab the Console’s output into an in memory string during a request, and then at the end of the request, grab all lines that starts with “NHibernate:” and you’re done:

on Application_BeginRequest:

var writer = new StringWriter();
Console.SetOut(writer);
Context.Items[SqlLogFilter.SQL_LOG_WRITER_KEY] = writer;

on Application_EndRequest:

var writer = HttpContext.Current.Items[SQL_LOG_WRITER_KEY] as StringWriter;
if (writer != null)
{
	var lines = writer.ToString().Split(new[] { Environment.NewLine }, StringSplitOptions.RemoveEmptyEntries);
	var relevantLines =
		lines.Where(l => l.StartsWith("NHibernate") || l.Length > 0 && char.IsWhiteSpace(l[0])).ToArray()
		;
	var queries = string.Join(Environment.NewLine, relevantLines)
		.Split(new[] {"NHibernate:"}, StringSplitOptions.RemoveEmptyEntries)
		.Select(q => q.Trim());
	DoSomethingWith(queries);
}

within DoSomethingWith you can do whatever you like with the queries string collection

The more complete solution that I use, is taking advantage of a feature in Monorail’s AspView called ViewFilter (you can do this with ASP.NET’s output filter; look up HttpFilter. it’s not as clean, but workable). I create a filter that wrap the stuff that’s on the Application_EndRequest, turn the queries collection into a bunch of <pre> elements, and stick it within the view-engine’s output by simple string.Replace call, injecting these <pre> elements into a marker location in the markup.

I’d then use jQuery to make these <pre> elements visible when clicking somewhere secret on the screen.

The ViewFilter’s code (for reference):

public class SqlLogFilter : IViewFilter
{
	public static readonly string SQL_LOG_PLACEHOLDER = "SQL_LOG_PLACEHOLDER";
	public static readonly string SQL_LOG_WRITER_KEY = "SQL_LOG_WRITER_KEY";
	public string ApplyOn(string input)
	{
		var log = "";
		var writer = HttpContext.Current.Items[SQL_LOG_WRITER_KEY] as StringWriter;
		if (writer != null)
		{
			var lines = writer.ToString().Split(new[] { Environment.NewLine }, StringSplitOptions.RemoveEmptyEntries);
			var relevantLines =
				lines.Where(l => l.StartsWith("NHibernate") || l.Length > 0 && char.IsWhiteSpace(l[0])).ToArray()
				;
			var queries = string.Join(Environment.NewLine, relevantLines)
				.Split(new[] {"NHibernate:"}, StringSplitOptions.RemoveEmptyEntries)
				.Select(q => q.Trim());
			log = queries
				.Select(q => "<pre>" + q + "</pre>")
				.Aggregate("", (q1, q2) => q1 + q2);
			var count = queries.Count();
			log = "<p>Queries: " + count + "</p>" + log;
		}
		return input.Replace(SQL_LOG_PLACEHOLDER, log);
	}
}

Single and looking

explanation (before the wife kills me): I have some free time in the coming months, so I’m looking for interesting consulting gigs.

So, if you’re in a company / dev team, and looking for some help with Castle (Windsor, MonoRail), NHibernate, or general information system design and architecture advices or training, setting up build and test environments, or any other of the things I rant about in this blog, then I’m your guy.

I also do web-client ninja work, dancing the crazy css/html/javascript tango (jQuery: yes, Ms-Ajax: no)

I currently live in Israel, but I’m fine with going abroad for short terms if you’re an off-shore client.

you can contact me at “ken@kenegozi.com”

Described Enums in NHibernate

First feature in D9.NHibernate: DescribedEnumStringType

That’s a generic IUserType for mapping enum columns using the descriptions of values instead of their names.

It depends on D9.Commons which contains the Described Enum helpers described in an early post

Usage:

given the following enum:

usingSystem.ComponentModel;

namespaceOpenUni.Domain.Modules
{
    publicenumModuleTypes
    {
        [Description("ר")]
        Standard,

        [Description("מ")]
        Advanced,

        [Description("מס")]
        AdvancedSeminar,

        [Description("תש")]
        Masters
    }
}

mapping a field of type ModuleTypes will look like that:

<propertyname    = "ModuleType"column  = "ModuleType"type    = "D9.NHibernate.UserTypes.DescribedEnumStringType`1[[OpenUni.Domain.Modules.ModuleTypes, OpenUni.Domain]], 
              D9.NHibernate" />

or if you use Castle ActiveRecord attributes for mapping:

[Property(ColumnType = "D9.NHibernate.UserTypes.DescribedEnumStringType`1[[OpenUni.Domain.Modules.ModuleTypes, OpenUni.Domain]], D9.NHibernate")]
public virtual ModuleTypes ModuleType {get; set;}

Code is here:

http://code.google.com/p/d-9/source/browse/#svn/trunk

I’ll build and upload binaries once I get some time for that. meanwhile you should be able to just svn-co the code, then nant from the root. (assuming nant 0.86b2 and .net 3.5 on the machine)

Happy new year, here comes SQL

One of the things occupying me is my work on my last seminar for the university, so I’d finally get my Bachelor’s degree.

The project is building an application for managing, uhm, a university. You reckon they figured out a way to prototype a new system for free … ?

Anyway, the seminar name is “Database Management Systems Workshop”, so one of the things I have to do is to present a full E/R-D even before I start coding. And UI design sheets. So no agile here :(

My aim is to be able to demonstrate some cool stuff though, like the usage of an advanced O/R mapper (NH), static and dynamic object-based querying (hql and Criteria API), some advanced T-SQL stuff (recursive set-based queries using CTE, and some fancy integrity enforcing triggers), smart caching with SqlCacheDependency, and more.

To make things interesting I decided to create a large-ish mount of fake data. Well, at least in terms of a non-production university-demo app. My DB currently holds 600K students and over 500 possible modules.

I’ll blog a bit on the ways I used to populate that data, and other data as well (such as the ModulePrerequisites table which is an adjacency table).

So, beware, some SQL might follow

Get unique results from joined queries in NHibernate

I’ve just added a page to NHibernate’s new community site, about Getting unique results from joined queries.

Comments (and edits) are welcome

"But it's using reflection, so it must be slow"

Well, not that slow apparently.

The lesson:

Don’t be afraid of powerful tools.

You can use reflection right, gain the power, while not losing too much performance.

Quoting from nhusers mailing list:

How much you be scare about the use of reflection in NH if 1.000.000 of access to get & set to a field mean 0.2seconds ?– Fabio Maulo

Fabio and Ayende On Caching

A quick ripoff from NHibernate’s users group:

Fabio Maulo:

The base concepts to understand are (my opinion):- The Cache is not the panacea of performance.- Don’t use the Cache like the base of your app; add the management of Cache at the end of your development process to increase the performance only where you really need do it.- Implementing a method named GetAll is, in most cases, a bad idea; an acceptable mediation is PaginateAll(pageSize).- InMemoryFilter can have less performance than filter trough RDBMS (especially when you intent to do it trough Cache with a large amount of entities).- Take care on what happen to the memory usage of your app when you are using Cache.

Ayende:

The cache is not magic, and should not be treated in such a fashion. I refuse to use the 2nd level cache in my applications until I have a perf problem that can’t be solved by creating smarter queries.Think about the cache as band aid, and good design as avoiding the need for that.

And I say Hallelujahs

Cloning a DetachedCriteria

I always need to look it up, so I might as well put it here for future reference:

What is a DetachedCriteria?

This is an ICriteria object, that is detached from any ISession, therefore is suitable to be passed around as a specification that is being built up dynamically.

Why would I want to clone it?

Well, say I want to run two similar queries (same WHERE part) but with a different projection or aggregation. For example - when fetching a paged collection, you’d want to create the spec criteria, then add Count projection on A to get the ‘total number of records’ ,and add the paging restrictions (sort, then from row 101 take 10 records). Now if you’d try to add the restrictions to the criteria to which you have already applied Count on, and error would occur.

Solution:

There’s CriteriaTransformer class, which holds a few useful static methods, amongst them, Clone(DetachedCritetia):

DetachedCriteria spec = DetachedCriteria.For&lt;Whadever&gt;() ...; &lt;= setup criterionsDetachedCriteria countCriteria = CriteriaTransformer.Clone(spec)    ...; <= setup the count projectionDetachedCriteria pagedCriteria = CriteriaTransformer.Clone(spec)    ...; <= setup the pagingICollection<Whadever&gt; stuff = whadeverRepository.FindAll(pagedCriteria);
int total = whadeverRepository.Count(countCriteria);

Using nhibernate's named queries with ActiveRecord

One of the methods of querying the DB when using NHibernate, is to issue HQL queries. HQL stands for “Hibernate Query Language”. It has a SQL-like syntax and is very intuitive for people with SQL background.

The way this works is that NHibernate ‘compiles’ the HQL query into SQL, and then issues the SQL query (using ADO.NET’s facilities) to the DB.

Sounds pricey?

enter Named Queries.

now these are HQL (or SQL) queries, each has a name (obviously), that are been supplied to NH through the mapping. The queries are being translated and cached as IDbCommand objects as part of the framework initialisation, which mean that you get rid of the HQL->SQL overhead throughout the life of the process.

One other major benefit, is that the mechanism to actually execute these named queries, does not differentiate between HQL and SQL queries (for the simple fact that these queries have already been transferred to SQL at runtime). That gives you the possibility to replace HQL queries into tighter SQL queries (with the same parameters and which returns the same resultset) should your DBA figure out a better one.

But if you’re using ActiveRecord, you usually do not have direct access into the mapping (.hbm) files. So how would you use named queries with AR?

enter HqlNamedQueryAttribute (not such a great name, as I did state that it would also work for SQL queries).

So, for an example, on this blog’s source code, within PostRepository.cs you’d see this code:

...



#region queries[assembly: HqlNamedQuery(Queries.FindPostsInArchive, Queries.FindPostsInArchive)][assembly: HqlNamedQuery(Queries.FindByUrlFriendlyTagName, Queries.FindByUrlFriendlyTagName)]namespace KenEgozi.Com.Domain{ internal partial class Queries { internal const string FindPostsInArchive = @" from Post p where  year(p.Lifecycle.CreationDate) = :year and month(p.Lifecycle.CreationDate) = :month  order by p.Lifecycle.CreationDate desc"; internal const string FindByUrlFriendlyTagName = @" select p  from  Post p  join p.Tags t where t.UrlFriendlyName like :urlFriendlyTagName order by p.Lifecycle.CreationDate desc;"; }}#endregion

...




 public ICollection<Post> FindInArchive(int year, int month) { 

 return session .GetNamedQuery(Queries.FindPostsInArchive) .SetParameter("year", year) .SetParameter("month", month) .List<Post>(); }

...

Queries class is marked as partial, as other queries might be presented on other repositories or services that would need to add more named queries. I considered grouping of queries into groups by the using repository, or by aggregate roots, but the thing is - having all of the queries under the same namespace helps discoverability, and helps with preventing duplications

Logging SQL output from NHibernate, using Log4Net

Following a question from NHibernate’s users list:

<configSections> <section name="log4net" type="log4net.Config.Log4NetConfigurationSectionHandler,log4net" /></configSections><log4net> <appender name="rollingFile" type="log4net.Appender.RollingFileAppender,log4net" > <param name="File" value="log.txt" /> <param name="AppendToFile" value="true" /> <param name="DatePattern" value="yyyy.MM.dd" /> <layout type="log4net.Layout.PatternLayout,log4net"> <conversionPattern value="%d %p %m%n" /> </layout> </appender> <logger name="NHibernate.SQL"> <level value="ALL" />    <appender-ref ref="rollingFile" />  </logger></log4net>

and configuring your application to use Log4Net (if you hadn’t done that anyway):

log4net.Config.XmlConfigurator.Configure();

If you wan’t to know more about log4net and it’s configuration options - look here or use your favorite search engine.

On Linq for SQL and POCO

I’m looking at the option of using Linq To SQL for persistence.

Basic assumptions:

• Entities should be POCOs
• I like Linq as query language
• I hate visible generated code
• Would rather avoid xml configurations

Today I’m using NHibernate (so 1 and 3 are set), and AR Attributes (so 4 is set). As for querying, I resort to hql (nice, yet too stringy), ICriteria (still stringy) and NHQG (cool, super cool, yet coupled with NH, while Linq is a “query everything” language)

I tried Linq for SQL (on a VS C# 2008 Express Beta2). No designer. Hand coded the entity, and have used the attributes for mapping.

First problem encountered: in order to make a column lazy, I need to change the underlying type to Link<MyOriginalType>, and then I can tell the context (using a LoadingOptions) about whether to load the lazy properties.

Couldn’t yet find a way to actually lazy load that property once the instance has already been loaded.

I much better like the way NH is handling things, with a runtime-generated proxy that takes care of lazy loading (among other stuff), so I get it without hassling my entities code.

Didn’t even mention First and Second Level Caches.

I guess I’d have to try and hop into NHibernate.Linq, and try to help Ayende with bringing it forward. That would mean diving into NH code, something I haven’t done for quite some time now …

ActiveRecord.Linq - naive but Working

I’ve spent some times lately with Linq To SQL and have played a bit with the Mapping namespace.

Why I do not like it very much is a matter for a different post. The matter at hand is that I want the power of Linq, and I want the power of NHibernate, and I want the easy road of ActiveRecord.

What do I mean by that? I’d like:

• query language == Linq
• persistence engine == NHibernate
• Mapping == ActiveRecord attributes

the needed prequisites:

• Linq (framework => framework.Version >= 3.5)
• NHibernate.Linq (source => source.getFrom(Rhino-Tools) )
• Linq for ActiveRecord - Keep on Reading

So, Ayende has kick-started it, and with some help from Bobby Diaz, we have a prototype level NHibernate provider for Linq.

To make it work with ActiveRecord, all you need is to add:

using System;using Castle.ActiveRecord;using Castle.ActiveRecord.Framework;using NHibernate;namespace NHibernate.Linq{    public class ActiveRecordContext : NHibernateContext { public ActiveRecordContext() : base(null) { session = GetSession(); }  private ISession GetSession() { ISessionScope scope = SessionScope.Current; if (scope == null) throw new InvalidOperationException("You should be in a SessionScope()"); ISessionFactoryHolder holder = ActiveRecordMediator.GetSessionFactoryHolder(); return holder.CreateSession(typeof(ActiveRecordBase)); } }}

and now you can do stuff like:

using (new SessionScope()){ ActiveRecordContext context = new ActiveRecordContext(); var q =  from c in context.Session.Linq<Category>() select c;  foreach (Category c in q) Console.WriteLine(c.Name); }

Assuming Category has [ActiveRecord] mapping.

A Sign That I'm Starting To Like NHQG Too Much

Today I noticed that piece of code on my working copy:

Repository.Blog.FindOne(Where.Blog.Id == blogId)

Eagerly loading multiple collections in one roundtrip

Today at work I did a session with my team, showing them several methods to query the DB in a NH/AR enviroment.

When we talked about eager fetching, I said that doing it for more than one collection in one query isn’t good (as advised in hibernate’s site), since it might build up a rather large cartesian product.

Why? If we have a type Entity, that looks a bit like that:

class Entity{ ... [HasMany ...] public IList<Item> Items ... [HasMany ...] public IList<AnotherItem> AnotherItems ... ...}

An eager fetch will do:

SELECT *FROM  Entities INNER JOIN Items ON Entities.Id = Items.EntityIdINNER JOIN AnotherItems ON Entities.Id = AnotherItems.EntityId

The result length will be eia, where e is the number of rows in Entities table, i is the number of rows in Items table , and a .. (you fill the blank).

But, luckily for us, NH is keeping a single concrete copy of each entity in a session. Ayende is abusing that in a way, that makes the above possible, without the 3rd (or more) dimention in the cartesian product.

How it works? Using a MultiCriteria,he isgettinghis entities,eagerly fetching one collection at a time. So it would return ei+earows. So for the first query, NH will populate the Entity instances, with the first collection (Items) populated. For the second query, NH will populate another Entity instances with the second collection (AnotherItems) populated. But, since it’s in the same session, actually the first Entity instances (with Items already populated) will get their AnotherItems collection populated.

That’s why at the end of the snippet, you see that he actually deals with list[0]. That’s where the first set of references is placed, and the other items in that list (list[1] … list[n]) are just copies of the references to the same Entity objects.

Quite a similar approach was seen when Ayende has shown us how to eager fetch using SQL (at the end of the post). The query there returns an array of Message items and an array of User items, but the User instances actually are wired to their Message instances, so he is using only the first array.

ActiveWriter - 2nd preview is out

ActiveWriter is a VS2005 plugin, that adds a new item type for your projects. This item is actually a visual designer for ActiveRecord classes. Quite neat, and hopefully will increase the penetration of Castle’s ActiveRecord to the “If there’s no VS wizard, I do not use it” kinda guys.

You can read about it and download it from http://altinoren.com/activewriter/

NHibernate ' Could not find constructor for: ' in ' select new ' projection query

There are two facts here:1. I love NHibernate.2. I hate NHibernate’s exception messages.

And here’s my story.

On a project I’m working on, I need to show a projection of “top 10” from the database. let’s show this on the good old Blog scenario:

So I want to show the posts with the longest comments measured by the comment’s length. Stupid, huh? but it’s a demo only (I cannot expose the actual ERD). Let’s say I want the top 5.

I am using Castle ActiveRecord. There is a Post and a Comment classes. However, I do not wish to load Posts objects, since It will load the Comments, too, and maybe other stuff that the Post class is related to. So I have defined a PostProjection class:

   1:  publicclass PostProjection

   2:  {

   3:  publicstring Title;

   4:  publicint Length;

   5:  public PostProjection(string title, int length)

   6:      {

   7:          Title = title;

   8:          Length = length;

   9:      }

  10:  }

I have also added an [Import] attribute on the Post. The actual querying is done using the next hql:

   1:  publicstatic PostProjection[] GetTopPosts(int postsToGet)

   2:  {

   3:      SimpleQuery<PostProjection> q =

   4:  new SimpleQuery<PostProjection>(typeof(Post), @"

   5:          select new PostProjection(p.Title, sum(c.LineCount)) 

   6:          from 

   7:              Post p inner join 

   8:              p.Comments c 

   9:          order by sum(o.LineCount) desc

  10:          group by p.Title");

  11:      q.SetQueryRange(postsToGet);

  12:  return q.Execute();

  13:  }

It worked great.

Yesterday I’ve upgraded my Castle dll’s to the ones from build 229. It includes NHibernate 1.2.0.2002

Now the “select new” started to fail, and NHibernate started to claim than “Could not find constructor for: PostProjection”.

I’ve been scratching my head, trying various approaches, and even was keen to skip the “new” and use an object[ ] and populate the Projection Array by hand, but then I tried changing the “length” parameter of the constructor from “int” to “long”. Magically it solved the problem.

Now, if only NHibernate would have said :

Could not find constructor for: PostProjection.Looking for: PostProjection(string, long)

I would’ve known what the problem was, and what should I change.

So what have we learned today?

1. NHibernate expects sum() to return “long” rather than “int”2. NHibernate’s error messages suck.

I’ve svn-ed the NHibernate trunk, added some code so this message would be more developer friendly, and I’m going to send the patch to NHibernate’s JIRA.