Monday, 30 June 2014

PeanutButter.DatabaseHelpers: get your compiler to start error-checking your SQL statements

PeanutButter was born out of a desire to share code between projects, in particular, to take advantage of utility and fixes introduced during the development process on a range of concurrent projects in the realm of code which was specific to none.

Quite basically, I had some code which was already figured out and worked fairly well for what it was intended to do and I was just plain too lazy to maintain multiple versions of that code. I thought it would be great if I could use, say, the Nuget packaging system to spread the most up-to-date versions of code between projects, if only packaging for Nuget wasn't such a pain. The CLI tools work, but aren't easy to use. The official GUI for Nuget packaging looks like a revenge unleashed on the world by an angry development manager trying to prove he can code. No offense.

Ok. Offense. The Nuget GUI tools are horrid and the CLI packaging mechanism is a PITA. Thank goodness for the Nuget Package Template extension for Visual Studio. With a simple post-build event from each relevant package, I can push all 11 (currently) PeanutButter.* Nuget package updates after having run all my tests and switched to Release mode. Win!

But I digress; this article is actually to introduce PeanutButter.DatabaseHelpers and show you how you can effectively get your compiler to error-check your application SQL.

First, taking a step back. If you've been using a decent ORM like Entity, NHibernate, Habanero or even just Linq-to-SQL, you don't have a need for PB's DatabaseHelpers. You can save yourself the effort of reading the rest of this article. One of the primary reasons for using an ORM (imo) is to abstract the SQL away from the application. ORMs which do Linq well are especially good at this -- and that's one reason why I've been a fan of Entity (warts and all) for quite some time. None of them are perfect but they all can take a lot of the pain of dealing with direct database calls away. In particular, an ORM allows you to:
  • Switch database backends (eg MSSQL/Firebird/MySQL/PostgreSQL and others). Of course backend support depends on the ORM, but most give you some kind of choice here.
  • Not have to type out correct SQL statements in your code. You may be wondering why this is a problem, unless, of course, you've had the experience where SQL in your code worked once and stopped mysteriously. After back-tracking VCS commits, you find that someone accidentally changed a string and there was nothing to pick it up. Or perhaps there was a test and the test was just as wrong as the code it was constraining.
  • Get the compiler to check that you've gotten the names of your database entities correct -- if you've misspelled an entity in your code in one place, chances are your code doesn't compile. Which is good -- the earlier up the code/compile/run/debug/package/deploy/test/etc chain you fail, the less it costs everyone.
  • Not have to worry about SQL injection attacks.
If you're using a super-light ORM like Dapper or just simply converting a giant project with heaps of direct ADO access or perhaps providing support for an app using an unsupported backend (like Access), you may have to get down to the bare SQL. But it would be great if you didn't have to actually take the risk of writing some SQL. Better still if the tool producing the SQL to run on your backend can be tweaked to target a different backend as required.

So here's a possible approach:

  1. Ensure that all of your database entities are defined as string constants in one accessible source file so that you aren't constantly fighting with each other on how to spell "color". Or "colour". However you all choose to spell it. And you aren't left to discover that "received" is spelled "recieved" sometimes in your SQL at go-live time. 
  2. Get a tool like PeanutButter to do the boring work for you. Reliably and in a manner you can test, with fluent builder syntax.
Sound like a plan? Great (:

First, I like to stick to using a DataConstants file to hold constants like field names, default values, etc. The other advantage here is that you can reference the same DataConstants in your FluentMigrator migrations (you are using FluentMigrator, aren't you?!). For example:

namespace MyProject.Database 
    public static class DataConstants 
        public static class Tables 
            public static class Employee 
               // to use where you would reference the name of your table
                public const string NAME = "Employee";
                public static class Columns 
                  public const string EMPLOYEEID = "EmployeeID";
                  public const string FIRSTNAME = "FirstName";
                  public const string SURNAME = "Surname";
                  public const string DATEOFBIRTH = "DateOfBirth";

Next, let's say we wanted to get a list of all Employees whose first names are "Bob". We could do:

var sql = SelectStatementBuilder.Create()

Ok, so this looks a little longwinded, but with a using trick like so:

using _Employee = DataConstants.Tables.Employee;
using _Columns = DataConstants.Tables.Employee.Columns;

// some time later, we can do:
var sql = SelectStatementBuilder.Create()
            .WithCondition(_Columns.FIRSTNAME, Condition.EqualityOperators.Equals, "Bob");

Now that's fairly readable and always produces the same, valid SQL. And will break compilation if you misstype something. And you can use Intellisense to help figure out required column names. This is a fairly simple example; just a taste. PeanutButter.DatabaseHelpers includes:
  • Statement builders for:
    • Select
    • Update
    • Insert
    • Delete
    • Data copy (insert into <X> select from <Y>)
  • The ability to do Left and Inner joins
  • Order clauses
  • Where clauses
  • Automatic quoting of strings, datetimes and decimals to values which will play nicely at your database (no more SQL injection issues, no more issues with localised decimals containing commas and breaking your SQL)
  • Interfaces you can use for injection and testing, for example with NSubstitute (ISelectStatementBuilder, IInsertStatementBuilder, IUpdateStatementBuilder, IDataCopyStatementBuilder, IDeleteStatementBuilder)
  • Another helper package PeanutButter.DatabaseHelpers.Testability which sets up NSubstitute mock objects for you so you can easily test without having to stub out all of the myriad builder returns on statement builders
  • Executor builders:
    • ScalarExecutorBuilder for insert/update/delete statements
    • DataReaderBuilder for traditional ADO-style code
      • With interfaces for easy testing
  • Support for some computed fields (Min, Max, Coalesce, Count)
  • Constrained by tests, developed TDD.
  • Syntax support for Access, SQLite, MSSQL and Firebird. Additional syntax support can be added upon request, given enough time (:
All in a convenient Nuget package you can use from your .NET application (install-package peanutbutter.databasehelpers), irrespective of target language. PeanutButter.DatabaseHelpers was developed and is maintained in VB.NET simply because of its origins, but that really doesn't matter to you as the consumer (:

Sure, there are features which are missing. This library has been built very much on the premise of extending on requirement. I hope that it can be helpful for someone else though. It has been used in at least 5 decent-sized projects.

I welcome feedback and will implement reasonable requests as and when I have time. Like the rest of PeanutButter, DatabaseHelpers is licensed BSD and you can get the source from GitHub.

Monday, 23 June 2014

Introducing... Peanut Butter!

I've been writing code for about 15 years now. It's not really that long, considering the amount of time that friends of mine have been writing code, but I guess it's not an insignificant portion of my life.

Anyone who writes code eventually figures out that there are some common problems that we solve time and again; and the time we spend solving them is time we could have spent solving more interesting problems.

Such common problems include (but are certainly not restricted to):
  • INI file parsing and writing
  • Random value generation (especially for testing)
  • Win32 polling services
  • Dealing with temporary files which need to be cleaned up as soon as we're done with them
  • Writing SQL statements (yes, sometimes we don't have ORM frameworks to help us (eg when we have to deal with Access) and sometimes we use super-light frameworks like Dapper which need us to do the SQL grunt work ourselves)
  • Systray icons
There are many more, of course.

There are a few ways we could deal with this scenario. We could re-write, from scratch, algorithms to provide solutions to these common problems. We could copy-and-paste known working code into our solutions every time. We could try to find someone else who has provided a library to help (and we certainly should, before embarking on rolling our own, if we value our time).

PeanutButter ( is a suite of small libraries aimed at tackling such common tasks. I built it out of a desire to re-use bits of code that I'd spent reasonable amounts of time on where those bits of code could perhaps save me time later. PeanutButter modules are available from Nuget and enable me (and anyone else who so desires) to spend more time on interesting tasks and less time on the more common ones.

I hope to spend time introducing the different modules of PeanutButter in more depth. I really hope that this code and blog can perhaps save someone a little time and effort. Even if I'm the only person to use PeanutButter, I'm OK with that -- it serves me well. But if it can make dev a little friendlier for someone else, all the better.

PeanutButter is employed in a few commercial products. Licensing is BSD, so you're free to use it, fork it, break it and keep all the pieces. Feedback is welcome and contributions will be considered.

Off the bat, PeanutButter offers (in small, fairly independant Nuget modules):
  • PeanutButter.DatabaseHelpers
    • provides builders for Insert, Update, Select, Delete and data copy (insert into X select from Y) SQL statement builders for Microsoft-style databases (MSSQL, Access), SQLite and Firebird (other dialects can be added on request/requirement)
    • DataReader and Scalar executor builders
    • Connection string builder (Access only, but I plan to expand this)
  • PeanutButter.INI
    • Provides reading/parsing and writing of INI files in a very small library with a very simple interface
  • PeanutButter.MVC
    • provides shims and facades to make testing MVC projects easier, especially when you'd like to constrain script and style bundle logic in your tests
  • PeanutButter.RandomGenerators
    • produces random values for all basic types (string, long, bool, DateTime), enums and selecting a random item from a collection
    • includes GenericBuilder, a builder base class which can build random versions of any POCO class. GenericBuilder is extensible, but doesn't do collections since I haven't determined what the optimal course of action is there. Still, it's proven to be very useful and has freed literally hours and hours of dev time that I (and others) would have spent on generating random input for tests requiring complex objects
  • PeanutButter.ServiceShell
    • provides a simple shell for Win32 polling services. You just have to set up the name, description and interval (default is 10 seconds) and override RunMain. Your Program.cs has one line to run in it and suddenly you have a service which:
      • Polls on a regular interval, guaranteed not to overlap (runs which exceed the poll interval just result in the next run being executed immediately)
      • can install, uninstall, start and stop itself from the command line
    • There's an example project, EmailSpooler, which demonstrates usage. EmailSpooler is a win32 service which polls an email registration table in your database for unsent emails and, using a configured SMTP server, attempts to send those mails, with handling for failed sends and backoff time. Whilst being an effective example for how to use the Service Shell, it was also an application actively used in production... until the client and I parted ways. It's generic enough for anyone to use though, and it's free. Help yourself.
  • PeanutButter.TestUtils 
    • provides a PropertyAssert class to easily test equality of properties on disparate object types, using property names and types. It's great for reducing the amount of time spent writing tests to ensure that DTO property copies take place as expected.
  • PeanutButter.TinyEventAggregator 
    • provides a "prism-like" event aggregator with very little overhead. In addition, it can log subscriptions and publications to the Debug output so you can figure out why your program is misbehaving (especially when you forget to unsubscribe...) and provides convenience methods like SubscribeOnce (to just be notified on the next publication) and, of course, the ability to subscribe for the next [N] events. You can interrogate subscriptions at run time and even pause and resume eventing which is quite useful from within winphone apps, when your app is backgrounded and resumed.
  • PeanutButter.TrayIcon
    • provides a simple way to create and use systray icons, using register methods to create menu items with callbacks
    • provides methods to launch and respond to clicks on bubble notifications
    • provides an animator class to animate your tray icon, given a set of icon frames
  • PeanutButter.Utils
    • provides AutoDeleter, a class implementing IDisposable which will clean up the files you give it when it's disposed (ie, when leaving the using scope)
    • provides AutoDisposer, which works like AutoDeleter, on disposing multiple registered IDisposable objects. Great for reducing your using nests
    • AutoLocker which locks a Semaphore or Mutex (though you should avoid win32 mutexes as they are, imo, broken) and releases when disposed so you can use a using block to ensure that your locks are acquired and released even if an exception is thrown
  • PeanutButter.WindowsServiceManagement
    • provides mechanisms to interrogate, start, stop, pause, resume and just plain "find out more about" windows services.
  • PeanutButter.XmlUtils
    • for the moment, just provides a simple Text() extension method to XElements to get all of their text nodes as one large string.
PeanutButter is developed with TDD principles. There are currently only 343 tests, but that number climbs as features are added, of course. Some things (like Windows service manipulation) are difficult to test, so they're not as well (if at all) covered. Other things are developed "properly": test-first. If nothing else, there's a collection of code which can wag a finger at me when I break something, before I upload it and break something of yours (:

I will be elaborating on each one a little more, with some code samples, in the near future. For the moment, it's enough that I've announced them. Oh yeah, and version 1.0.50 is available through Nuget and on github.

Who am I? What am I? Why should you care? Who actually does care?

The short answers are:
  • Just a dude. Nothing special. Coder, biker, amateur yogi, thinker, father, curious cat. Aren't we all?
  • A carbon-based life form on a rock whizzing through space, dedicated to trying to make the most of the inconsequential slice of time allotted to his existence.
  • You don't have to care. If you don't, that's OK. If you do, that's also OK.
  • I'm not sure. I'd like to think at least someone does, but if not, it doesn't really matter, does it?

What can you expect here? I'm not sure yet. Apart from my desire to share code with anyone who might find it useful, I don't have much of a plan for this place. I might spew some thoughts out here. I might leave this dormant for ages at a time.... Strike that. I'd like to (in the very least) post some kind of announcement here when I spew out some code for public consumption. If for no other reason than that of Scott Hanselman: we only have a limited amount of time to spend on information dissemination whilst we're alive. It only makes sense to make that process as efficient as possible -- spread links to data instead of spreading the same data, over and over, with multiple consumers.

That's about enough for now...

What's new in PeanutButter?

Retrieving the post... Please hold. If the post doesn't load properly, you can check it out here: