codeface

There are two more features I want to get into Simple.Data before releasing 1.0beta1: upserts, and eager loading. Upserts are pretty straight-forward, but there are a few different approaches to eager loading and I want to get some input from the community before I pick one.

In case you don’t know, eager loading is a pattern for getting data from the database in an optimal fashion, with the minimum number of requests or least-possible server load. It’s the opposite of lazy loading, which Simple.Data already does.

The implementation syntax will look like this:

    IEnumerable<Customer> customers = _db.Customers.FindAllByRegion("South")
                .With(_db.Customers.Orders)                .Cast<Customer>();

This will create the customer objects and populate an ICollection<Order> Orders property in each one, assuming there is one.

There are two directions in which a class may have relationships with other classes: it may have “parent” (or “lookup”) classes, where there is at most a single instance of that parent class within the instance of the class in question; and it may have “child” classes, where there is a collection of instances of the child class. Eager loading of parent classes is trivial; you just add the necessary join(s) to the master table(s) and marshal the data into the right places in code.

Eager loading of child classes is similarly easy when there is only a single child collection to be loaded; you join to the detail table and then process the results so that a single instance of the main table row is created, and instances of the detail table rows are grouped into collections.

For example, loading user objects with a list of phone numbers:

SELECT User.Id, User.Name, Phone.Number
FROM User
LEFT JOIN Phone ON User.Id = Phone.UserId

Returned rows:

From this we create a User object for Alice with her two phone numbers, and an object for Bob with his. Even if there are a lot of rows in the detail table, this is still the preferred way of handling eager loading for this scenario as it involves the fewest round-trips to the database. Often, you can handle a parent-child-child relationship using this algorithm, too, although beyond that point you’re getting a bit scary.

The complexity arises when you want to load two unrelated child collections, say, load Customers with their Invoices and Orders:

SELECT Customer.*, Invoice.*, Order.*
FROM Customer
LEFT JOIN Invoice ON Customer.Id = Invoice.CustomerId
LEFT JOIN Order ON Customer.Id = Order.CustomerId

This creates what is technically called an “outer Cartesian product”, where a row is returned for every possible combination of the rows from the two outer-joined tables. So if you’re grabbing the details for a single valuable customer who has placed a thousand orders and generated a thousand invoices, you get a million rows back from the database. I have a hunch this might not be optimal.

So this is my Request for Comments: what would you like to see done here?

The options are:

1. Only allow one detail-table With clause to be specified per query. Throw an exception if there is more than one.
2. Allow multiple detail-table With clauses, and use additional SELECT operations for all except the first.
3. Add a parameter to the With method allowing the developer to specify the technique on a per-detail-table basis:

    var customers = _db.Customers.FindAllByRegion("South")
                .With(_db.Customers.Orders, WithUsing.Join)
                .With(_db.Customers.Orders.Items, WithUsing.Join)
                .With(_db.Customers.Invoices, WithUsing.ExtraQuery);

If only one detail table is specified, or one detail with a detail below it (e.g. _db.Customers.Orders.Items), the default behaviour when no WithUsing parameter is supplied will be to use JOIN. Multiple detail tables without a WithUsing will cause the ADO adapter to throw an exception (other adapters, such as the one for MongoDB, can use the With clause as they see fit).

I think I’ve rubber ducked here a bit, as Option 3 is looking like a no-brainer, but I’ve typed it all now, so if you do have any comments or suggestions, please do leave them below. I’m planning on starting code for this on Sunday morning, so there’s plenty of time to change my mind.

Several people who weren’t following the show at the inception of Simple.Data have asked whether it does anything to prevent SQL Injection attacks. Those of you who remember the start of the project will understand why I’m kind of amused by the question.

I originally built Simple.Data as a proof-of-concept when there was a big web-scuffle over what was then called Microsoft.Data (now WebMatrix.Data). David Fowler, a developer I have since come to respect greatly, posted an example on his blog which used concatenated SQL, and everybody lined up to tear him a new one. I’m a big fan of code over cockfights, so I had a go at implementing a simple data access library which made SQL Injection impossible by eliminating SQL strings. Thus Simple.Data. You told it what you wanted, and provided the necessary values, and then it wrapped them safely up in parameters and executed them. I stuck it on GitHub, and posted about it on my blog.

(In fact, the first version did allow you to execute SQL strings, much like Dapper and Massive, because the functionality was extremely limited otherwise. As I’ve iterated on it, I’ve been able to expose nearly all the required functionality using the dynamic approach, so the only way you can execute SQL directly now is by getting an IDbConnection object from the AdoAdapter and using ADO.NET against it.)

Deserves its own post, even though I’m about to write the 0.11 one.

This release took much longer than I would have liked, mainly because I bit off more than I could chew. I had a requirement to provide poly-fills that implement query operations against in-memory datasets, on behalf of adapters for data-stores that don’t support certain operations. Think aggregates, grouping and so on. At some point, it occurred to me that this would make it really easy to create an in-memory adapter which would just have all query functionality implemented by these poly-fills.

Aside: From very early on, Simple.Data has had the XmlMockAdapter which could be used for providing a quick and dirty database for test environments. At the time I wrote that, I wasn’t totally confident in the Simple.Data model, so a way to instantiate that data without using the API seemed important. Since then, of course, the API has become much more stable and I’ve gained confidence, so it seems perfectly OK to use it to set up the test data as well as consume it. Consider XmlMockAdapter deprecated.

Simple.Data.InMemoryAdapter

The InMemoryAdapter lives directly in the Simple.Data namespace in the Simple.Data.dll assembly from the Simple.Data.Core package. It is a first-class component, because testing is a first-class concern.

You can use the adapter like this:

Database.UseMockAdapter(new InMemoryAdapter());
var db = Database.Open();
db.Test.Insert(Id: 1, Name: "Alice");
var record = db.Test.FindById(1);
Assert.IsNotNull(record);
Assert.AreEqual(1, record.Id);
Assert.AreEqual("Alice", record.Name);

That UseMockAdapter method is now directly on the Database class. After calling that, all subsequent calls to any of the Database.Open methods will return a database using that adapter.

Any tables you reference in the database will be “created” if they don’t already exist. Internally, the tables are just lists of dictionaries. Any data you insert will retain its CLR type, so the adapter can handle byte arrays and so on quite happily.

Joins are supported:

var adapter = new InMemoryAdapter();
adapter.ConfigureJoin("Customer", "ID", "Orders", "Order", "CustomerID", "Customer");
Database.UseMockAdapter(adapter);
var db = Database.Open();
db.Customer.Insert(ID: 1, Name: "NASA");
db.Customer.Insert(ID: 2, Name: "ACME");
db.Order.Insert(ID: 1, CustomerID: 1, Date: new DateTime(1997, 1, 12));
db.Order.Insert(ID: 2, CustomerID: 2, Date: new DateTime(2001, 1, 1));

var customers = db.Customer    .FindAll(db.Customer.Orders.Date < new DateTime(1999, 12, 31)).ToList();
Assert.IsNotNull(customers);
Assert.AreEqual(1, customers.Count);

And most query operations work OK.

YMMV: This is probably not perfect, but it was stalling other development so I took the decision to release it. So far in my own projects it has not caused any problems; I’ve had one reported which is fixed in 0.11 (more on that in the next post).

Bug fixes and such

There were a bunch of fixes for various issues in 0.10, most of which were contributed by Richard Hopton, who has also released an ADO provider for Sybase SQL Anywhere.

I know it’s early for New Year resolutions, but I’m going to share this one now partly so I don’t forget in six weeks.

I buy a lot of media. I have an entire 6′x3′ bookcase full of technical books, most of which are out of date by at least one version. There’s a cubic hectare of Blu-ray and DVD boxes lying around the place, and I’m pretty sure there are boxes of CDs (remember those?) in the loft somewhere. Probably half the available storage space in my house is taken up with things which would actually fit on a few terabytes of hard disk.

It’s not like everything in the house isn’t already digital. We’ve got a 160GB iPod Classic, iPhones, iPads, Kindles, three laptops, an Xbox 360, a PS3 (with a 320GB HD), a 50Mbps internet connection, and on Saturday we bought an Apple TV.

It’s ridiculous. It has to stop. It ends, starting in January. From then on, where a digital purchase option is available, I shall avail myself of it. I will invest in a home server with a few TB of storage on mirrored drives; I’m thinking of the Lacie 5big Network 2, which will take up to 10TB. Then I can “back up” my movie collection to that, and install iTunes on it for music streaming. Paper books will go to charities and libraries; if I want to read any of them again, I’ll buy them in ebook format. Magazines through Newsstand and papers through the Times iPad app.

Now, I just need Sony and Microsoft to distribute all PS3 and Xbox games digitally, and I need never darken the door of another shop. Not that I do anyway. All this crap comes from Amazon.