Patrick Steele

ActiveRecord: Never forget there's a database

Some lessons learned while using ActiveRecord . I really like ActiveRecord and I would recommend using it on .NET projects when there is the need an OR Mapping tool. The fault lay was us, the developers. We didn't pay enough attention to the fact that there was a database at the end of the calls that the repository was making. We didn't spot that some of the calls which our test code was making were potentially very expensive in real usage scenarios. And so we got bitten by the law of leaky abstractions, which manifest itself through a number of bad usage patterns which caused immediate performance problems. Read the full post for other lessons. Technorati tags: ActiveRecord

Generate ActiveRecord Classes from Existing DB Schema

GeneratorStudio is a project on CodePlex used to automate the creation of Castle ActiveRecord classes from existing database schema. I haven't downloaded it yet to play around with it, but it looks like it would be a nice thing to have if you have a large (or even medium) schema that's already in production and you'd like to start using Castle ActiveRecord for DB access. More info here and here . Technorati tags: ActiveRecord , ORM , CodePlex

Mock Databases and ActiveRecord

Fellow SRT employee Jay Harris has a great post on using SQLite's in-memory database feature to mock his database .  He points out a few of the factors that drove him to investigate such an approach: I did not want a SQL Server installation to be a requirement for me, the other developers, and my Continuous Integration server. I wanted something fast. I didn't want to have to wait for SQL Server to build / tear down my schema. I wanted something isolated, so the other developers, and my CI server, and I wouldn't have contention over the same database, but didn't want to have to deal with independent SQL Server instances for everyone. Check it out . Technorati Tags: ActiveRecord , SQLite , Mock

ActiveRecord and a Custom NHibernate PrimitiveType

I was intrigued by Steve Smith 's blog post yesterday about reducing SQL Lookup tables in nHibernate . He gave an example of a WorkOrderStatus class the exposed the actual status as a POCO object that wasn't stored in the database. What really piqued my interest was the following comment: NHibernate can map this status directly if you create a WorkOrderStatusType class that inherits from NHibernate.Type.PrimitiveType and overrides its methods. I never knew nHibernate supported this type of feature (never needed it or thought about it). As I'm an avid ActiveRecord user, I decided to see how I would implement a custom nHibernate PrimitiveType and utilize it via ActiveRecord. Turns out it was pretty easy! The full source is available from my GoogleCode page either through SVN or simply a ZIP download. A quick note before we begin: I didn't find a whole lot of documentation on extending PrimitiveType and implementing your own. I reviewed some nHibernate code and I think I got the general implementation right, but can't be sure it'll work 100% of the time. It was a proof-of-concept project. ActiveRecord Setup I decided I'd use SQLite for this sample since it's perfect for this type of job -- small, compact and no install required. I can poke around the database to check schema and data using the SQLite addon for Firefox . Instead of stealing Steve's WorkOrderStatus, I decided to go with a schema that has a simple Company object, and that Company object has a CompanyType defined. Instead of defining a lookup table just for company types, I'll create a CompanyType class that derives from NHibernate.Type.PrimitiveType and let nHibernate do the loading/saving. First, the CompanyType. For this demo, it's a simple object with a Description (string) and a Value (integer). The Value is what is actually saved to the database (note: this isn't the entire class -- just the basics): public class CompanyType : NHibernate.Type.PrimitiveType { public static readonly CompanyType Software = new CompanyType() { Description = "Software" , Value = 1 }; public static readonly CompanyType Manufacturing = new CompanyType() { Description = "Manufacturing" , Value = 2 }; public static readonly CompanyType Insurance = new CompanyType() { Description = "Insurance" , Value = 3 }; private static readonly CompanyType[] AllTypes = new CompanyType[] { Software, Manufacturing, Insurance }; public string Description { get; set; } public int Value { get; set; } public override string ToString() { return this .Description; } public CompanyType() : base (SqlTypeFactory.Int32) { } } I've defined an AllTypes[] that I'll use to find the matching CompanyType when nHibernate reads the integer from the database. The ctor calls the base class ctor and tells nHibernate what data type this new PrimitiveType is based on (the schema in the database will be an integer). I also overrode ToString() to return the Description property to make debugging easier. The Company record is pretty simple too. When we get to the CompanyType, we tell ActiveRecord (which works through nHibernate) the column type for the column (our custom PrimitiveType): [ActiveRecord] public class Company : ActiveRecordBase<Company> { [PrimaryKey(Generator = PrimaryKeyType.Identity)] public int Id { get; set; } [Property] public string Name { get; set; } [Property] public DateTime InceptionDate { get; set; } [Property(ColumnType = "ARPrimitiveType.Model.CompanyType, ARPrimitiveType" )] public CompanyType CompanyType { get; set; } public override string ToString() { return this .Name; } } Implementing the required methods in CompanyType was pretty easy. I'm not sure when DefaultValue is used, so I just return a CompanyType of Software: public override object DefaultValue { get { return CompanyType.Software; } } ObjectToSQLString seems to want to convert your PrimitiveType (CompanyType) to a string value that can be used by the database. So we'll convert our Value property to a string: public override string ObjectToSQLString( object value , NHibernate.Dialect.Dialect dialect) { CompanyType type = value as CompanyType; return type.Value.ToString(); } The PrimitiveType class indicated the actual type of data stored in the database (a 32-bit integer for our CompanyType): public override Type PrimitiveClass { get { return typeof (Int32); } } The FromStringValue and two Get overloads both need to do the same thing: Take a representation of the database value and convert it to our PrimitiveType (a CompanyType). For this I created a single method that converts the database integer back to a CompanyType instance using a LINQ query on the AllTypes array: public override object FromStringValue( string xml) { return GetCompanyType(xml); } public override object Get(System.Data.IDataReader rs, string name) { return GetCompanyType(rs[name]); } public override object Get(System.Data.IDataReader rs,...

ActiveRecord + HQL and an "IN" clause

Late last year as I was using MonoRail and ActiveRecord for a simple web application. I was helping my local church find volunteers with various skills to teach some basic computer courses to the the church staff. I wanted to keep track of the volunteers along with the skills they had. I used this as another opportunity to learn more about MonoRail and ActiveRecord. The Database The database couldn't be any simpler: I had a Trainer table, a Skill table and a join table to keep track of the 1:M relationship between a Trainer and their skills. ActiveRecord The ActiveRecord classes were equally easy to define (in fact, I created the ActiveRecord objects first and then used schema generation to generate the actual database tables). The Problem Query I used MonoRail to put together a couple of web pages for editing of the data. Then I created a "Report" page that allowed me to pick an arbritrary set of skills and get a list of all Trainers that had that particular skill. In SQL, I'd use an "IN" clause like this: select t.* from Trainer t inner join TrainerSkills ts on ts.TrainerId = t.id and ts.SkillId in (2,6) In the query above, the IN clause of (2,6) contains the primary keys of the two Skill records selected by the user. Pretty simple SQL. I needed to figure out how to get this in HQL (Hibernate Query Language). My first attempt was an almost exact port of the SQL syntax (HQL is very similar to SQL anyway): public static Trainer[] FindBySkillset(Skill[] skills) { SimpleQuery<Trainer> q = new SimpleQuery<Trainer>( "from Trainer t where t.Skills in (?)" , skills); return q.Execute(); } That didn't work. I got some cryptic error about having an "unindexed collection before []". So then I tried a variation of the above query where I used a named parameter and specifically indicated the parameter was a list: public static Trainer[] FindBySkillset(Skill[] skills) { SimpleQuery<Trainer> q = new SimpleQuery<Trainer>( "from Trainer t where t.Skills in (:skills)" ); q.SetParameterList( "skills" , skills); return q.Execute(); } That gave me another odd error. After digging around in the HQL docs as well as finding a forum post somewhere that showed a slightly different IN clause, I found out that I needed to "flip" the way I use the IN clause. In SQL, you'd say "WHERE xxx IN (values...)". In HQL, you give the list of values first and use the "elements" keyword to indicate which collection to match up those values to. The final working query: public static Trainer[] FindBySkillset(Skill[] skills) { SimpleQuery<Trainer> q = new SimpleQuery<Trainer>( "from Trainer t where ? in elements (t.Skills)" , skills); return q.Execute(); } Hopefully this helps out someone else. Technorati tags: MonoRail , ActiveRecord , HQL