August 15, 2009

Getting imported treats in Canada

An off topic blog post this evening that I thought would be interesting to fellow immigrants and ex-patriots in Canada - the topic of "Where the hell do I go to get all my favourite junk food now I'm here?!".

It's tough as all hell to find decent stores to get our imported favourites in Canada, they're poorly advertised and don't show up on Google unless you type exactly the right search. So I thought I'd compile my last few years of "research" by posting the places that I obtain all my imported junk food.

Most of these places will ship within Canada, but some of them don't and therefore you'll need to be in the same city as them:


A Bit Of Home - 600 Burnhamthorpe Rd, Mississauga, ON (Ships within Canada)


Blightys - 88 First St, Orangeville, ON (Ships within Canada)


British Isles Shoppe - 2525 Bayly St, Ajax (Ships within Canada)


Mrs. Bridges British Bakery - 322 Eddystone Ave, Toronto (Delivers in GTA, will ship outside GTA with special arrangement)


The Chocolate Box - 415 Donlands Ave, East York (Delivers in the GTA)


A Good Taste of Britain - Eau Claire Market, Calgary


The British Pantry - 4820 Northland Drive, Calgary, AB


Celtic Treasure Chest - 5639 Dunbar St, Vancouver, BC


Black Pudding - 20243 62 Avenue - Langley BC


Simply Australian - Rainier, WA (Delivers in North America - for a fee)


HomeSick.com.au - Victoria, Australia (Ships Worldwide)


A great place to track down other import food stores in Canada (or anywhere else in North America for that matter) is Chowhound.

Enjoy!

August 11, 2009

Finding permission discrepancies on SQL Server

After almost an hour of bashing my head against some code trying to fix a problem with an application today that I'd tested and deemed working I finally figured out why someone else who was trying to replicate my tests was having so many problems. It turns out that I'd installed two SQL scripts on the database, but had inadvertently forgotten to assign the user group permissions. Having grown tired of trying to figure out which scripts haven't been assigned the execute permission and which have, and figuring out which scripts have been added recently and have been potentially overlooked, I decided that it would be a great idea if I just created a script that assigned execute permissions to all of the stored procedures in my database.

Before you run this code, there are some caveats to be aware of:

  1. It will assign execute permission on ALL of your custom stored procedures to the user group you specify in the @userAcct variable
  2. You should not use this as part of a stored procedure - the SQL is dynamic and not parameterized. If it is included in a stored procedure and you are subject to a SQL injection attack, it is not my fault and I shall not be held responsible - you have been warned
/* Set the user name of the security account you wish to grant permissions to */
Declare @userAcct NVarChar(255)
Set @userAcct = '[MYDOMAIN\Sql Users]'

Declare CRS_STORED_PROCS Cursor For
 Select name
 From SysObjects
 Where xtype = 'P'
 And category = 0

Declare @procName NVarChar(255)
Open CRS_STORED_PROCS
Fetch Next From CRS_STORED_PROCS Into @procName
While (@@Fetch_Status = 0)
 Begin
  Print 'Granting execute permissions on procedure '+ @procName + ' to account ' + @userAcct + '...'

  Declare @stmt NVarChar(255)
  set @stmt = 'Grant Execute On ' + @procName + ' To ' + @userAcct

  execute sp_executesql @stmt

  Print 'Permissions granted.'

  Fetch Next From CRS_STORED_PROCS Into @procName
 End

Close CRS_STORED_PROCS
Deallocate CRS_STORED_PROCS

Some other useful queries follow

Give me a list of all stored procedures that I created on or since a specified date:

Select name 
From  SysObjects 
Where  xtype = 'P' 
And category = 0 
And crdate > '2009-08-10' 
Order By crdate Desc

Give me a list of all stored procedures that my user has permission to execute.

Select c.name As [Procedure],
 Case a.actadd
  When 32 Then 'True'
  When 0 Then 'False'
 End As [Execute],
 Case a.actmod
  When 32 Then 'True'
  When 0 Then 'False'
 End As [With Grant]
From SysPermissions a
 Left Join SysUsers b On a.Grantee = b.uid
 Left Join SysObjects c On a.id = c.id
Where b.name = 'MYDOMAIN\Sql Users'
And c.Type = 'P'

Give me a list of all stored procedures that my user does not have permissions for.

Select a.name as [Procedure Name],
 a.crdate as [Created]
From SysObjects a
Where type = 'P'
And category = 0
And id Not In (
 Select id 
 From SysPermissions a 
  Left Join SysUsers b On a.Grantee = b.uid
 Where b.name = 'MYDOMAIN\Sql Users' 
)

Get a list of tables that have changed structurally since a known specified date:

Declare @LastKnownChangeDate DateTime

Select @LastKnownChangeDate = '2009-08-01'

select name As TableName, 
  create_date As Created,
  modify_date As LastModified
From sys.tables
Where type_desc = 'USER_TABLE'
And  modify_date > @LastKnownChangeDate

The permissions found for users of stored procedures and tables are found in the SysPermissions tables. The columns actadd, actmod, seladd, selmod, updadd, updmod, refadd and refmod refer to the permissions assigned to the user specified in the grantee column, the grantee and grantor columns reference the uid field in the SysUsers table. The numbers found in the permissions columns are a bitmask for the permissions assigned such that:

  • Select = 1
  • Update = 2
  • References = 4
  • Insert = 8
  • Delete = 16
  • Execute = 32

For those of you not familiar with bitmasks a quick example is that it's like a bunch of flags either being on or off. Each flag represents the number specified and the sum of the enabled flags is the value stored. For instance, If a user has select, insert, update and delete permissions, the value would be 1 + 2 + 8 + 16 = 27. If the user only has execute permission on a stored procedure, then the value would be 32.

August 4, 2009

Generic database configuration

In the business world, we spend much of our time trying to write software that is as flexible as possible. This is where the concept of interfaces and loose coupling come in. For most of my career I've written applications where my data layer is some abstract layer that sits between my application and my database on the pretence that if for whatever reason someone decides they want the application to sit on top of a different database, the only thing that would need replacing is the data layer.

Recently though, this concept has been troubling me - I don't know why it only just occurred to me but I was thinking - if I did want to sit my application atop another database, I sure as hell wouldn't want to have to rewrite my data layer - what a pain in the ass that would be!

I guess the plethora of ORMs that are on the market in the last few years culminating with LINQ to SQL was what got me thinking. Sure ORMs are a fantastic tool for what they're worth but what if you have a simple model and don't want the overhead of an ORM?

So I got to wondering - what would it take to specify my connection string and data client type in my configuration file and build everything atop that? We're used to specifying a connection string in our configuration file but I thought I'd take it a step further and define my data client too. Then I would build a generic database helper that would sit on top of this and do it's thing. This way, if I wanted to switch out the database from SQL Server to say Oracle, or MySQL, I could just change my configuration file and carry on as normal - without having to modify my data layer in the slightest.

First up, what does it take to grab our connection string and provider from the configuration file? Not much at all.

Let's consider the following app.config:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <connectionStrings>
    <add name="TestDb" connectionString="Data Source=.\SQLExpress;Initial Catalog=MyTestDatabase;Integrated Security=SSPI;" providerName="System.Data.SqlClient"/>
  </connectionStrings>
</configuration>

To grab our connection settings we add a reference to System.Configuration and use the following code:

var ConStrSettings = ConfigurationManager.ConnectionStrings["MyConnectionString"];

If we need access to the connection string, or the provider name from here, we can access them very simply using:

var ConStr = ConStrSettings.ConnectionString;
var ProvName = ConStrSettings.ProviderName;

So there we have it, we can grab our ConnectionString and Provider right in from the app.config.

Next up, we need to instantiate a connection object that will connect to our database with nothing more than the information provided in the config file.

Thankfully there's a great little tool called the ProviderFactory which has a method to do the heavy lifting for us - CreateConnection().

First up we will check our connection string to see if it included a provider name - after all, the person writing the config file may not be as thorough as us.

public DbProviderFactory GetProviderFactory(string provider, string connectionString)
{
 var providerName = provider;

 //If the provider argument was empty, attempt to grab it from the connection string itself.
 if (string.IsNullOrEmpty(providerName))
 {
  var csb = new DbConnectionStringBuilder() { ConnectionString = connectionString };
  if (csb.ContainsKey("provider"))
   providerName = csb["provider"].ToString();
 }
   
 //If we still don't have a connection string, throw an exception.
 if(string.IsNullOrEmpty(providerName))
  throw new InvalidReferenceException("Missing provider name"); 

 //If the provider doesn't exist, throw an exception.
 if (!DbProviderFactories.GetFactoryClasses().Rows.Cast().Any(r => r[2].Equals(providerName)))
  throw new MissingTyhpeReferenceException(
   String.Format("Specified provider {0} could not be found.", providerName));

 return DbProviderFactories.GetFactory(providerName);
}

So, now we've got our provider factory for the database of our choosing or we've thrown an exception because the provider was either missing or invalid.

Now it's simple to create our connection object using the GetProviderFactory method:

DbConnection con = GetProviderFactory(ConStrSettings).CreateConnection();

From here, we can very easily create our command objects and anything else we would normally do with our database connection.

DbCommand cmd = con.CreateCommand();

August 2, 2009

Abstract Classes, Inheritance, Generics, Covariance and Type Constraints

I was asked a question on Friday regarding type constraints. I have to claim complete ignorance to the terms as I've never come across it before so it's no suprise that I completely floundered.

In order to discuss the concept, I was presented with an example about inheritance which as a fairly seasoned OO programmer is something I do with very little if any thought. So consider the example I was given of two types of tires which we want to have common types of behaviour: SportsTire and RoadTire. Now as we want to be able to inherit certain behaviours and force the implementation of each of the two types of tire to contain certain behaviours we will consider some abstract methods and properties, so let's define a base class called TireBase:

public abstract class TireBase
{
 public abstract bool Inflated{ get; }
 public abstract void Inflate();
 public abstract void Deflate();
 public abstract void DisplayInflateState();
 public TireBase(string TireDisplayName)
 {
  Console.WriteLine("New {0} reporting for duty!", TireDisplayName);
 }
}

Now we will define our two concrete classes inheriting TireBase, first our RoadTire class:

public class RoadTire : TireBase
{
 private string _DisplayName = "Road tire";
 public string DisplayName
 { 
  get
  {
   return _DisplayName;
  }
 }

 private bool _Inflated;
 public override bool Inflated
 {
  get
  {
   return _Inflated;
  }
 }

 public override void Inflate()
 {
  _Inflated = true;
  DisplayInflateState();
 }

 public override void Deflate()
 {
  _Inflated = false;
  DisplayInflateState();
 }

 public override void DisplayInflateState()
 {
  Console.WriteLine("{0} {1}", _DisplayName, _Inflated ? "inflated" : "deflated");
 }

 public RoadTire(): base("road tire") {}
}

And now our SportsTire class:

public class SportsTire : TireBase
{
 private string _DisplayName = "Sports tire";
 public string DisplayName
 { 
  get
  {
   return _DisplayName;
  }
 }

 private bool _Inflated;
 public override bool Inflated 
 {
  get
  {
   return _Inflated;
  }
 }

 public override void Inflate()
 {
  _Inflated = true;
  DisplayInflateState();
 }

 public override void Deflate()
 {
  _Inflated = false;
  DisplayInflateState();
 }

 public override void DisplayInflateState()
 {
  Console.WriteLine("Sports tire {0}", _Inflated ? "inflated" : "deflated");
 }

 public SportsTire(): base("sports tire") {}         
}

You will notice that the two classes are extremely similar - for obvious reasons, after all this is just an example and really we are only demonstrating the need to be able to work with both classes.

So we've got our base class and our two classes deriving from TireBase. Now, as it was stated to me, if my understanding is correct, if I present the following method signature:

InflateAll(List<TireBase> tires)
{
 tires.ForEach(i => i.Inflate());
}

This is the exactly the same the code below, but uses the more concise lambda expression form:

InflateAll(List<TireBase> tires)
{
 foreach(tire i in tires)
 {
     i.Inflate();
 }
}

If I pass in a list of SportsTire or a list of RoadTire, then when the application reaches the point of execution of this method, I would get a runtime error. I wasn't convinced - but since we were using a whiteboard, I had no way to be 100% sure until I returned to my computer.

For instance:

var tires = new List<SportsTire>();
tires.Add(new SportsTire());
tires.Add(new SportsTire());

InflateAll(tires);

Thinking about the argument slightly differently, I approached it thus, what if we didn't define our list as that of SportsTire or RoadTire, but of TireBase? Such that the following was our definition:

var tires = new List<TireBase>();
tires.Add(new SportsTire());
tires.Add(new SportsTire());

InflateAll(tires);

My hypothesis is that this should work just fine - and indeed it does. However, the other person wasn't convinced and stated that again this would not be possible due to the inability to derive which type we are working with when we are trying to work down the hierarchy from TireBase to RoadTire or SportsTire. I didn't argue, but I still wasn't convinced - hence this blog post to describe my practical findings from sitting in front of my computer with Visual Studio 2008 rather than our theoretical musings on the whiteboard.

I will continue with the example of tires and I will do the following:

var tires = new list<TireBase>();
tires.Add(new SportsTire());
tires.Add(new SportsTire());
tires.Add(new SportsTire());
tires.Add(new RoadTire());
tires.Add(new RoadTire());
tires.Add(new SportsTire());

InflateAll(tires);

According to the person I was speaking with - if I understood the point he was trying to make - this should fail at the point I enter the InflateAll method and attempt to work with one of the derived types - my theory is that as long as I've defined the abstract method Inflate() in my base class, then much the same as defining the method in an interface, things should still be fine.

Upon running my code, indeed everything runs perfectly and here is my output:

New sports tire reporting for duty!
New sports tire reporting for duty!
New sports tire reporting for duty!
New road tire reporting for duty!
New road tire reporting for duty!
New sports tire reporting for duty!
Sports tire inflated
Sports tire inflated
Sports tire inflated
Road tire inflated
Road tire inflated
Sports tire inflated
Press any key to continue . . .

So my theory is correct - and the practical output is exactly the same as if I'd used an interface ITire and had my concrete classes implement that interface instead.

Okay, on to type constraints which is what this post was supposed to be about - so far I've really just been setting the scene but wanted to clear up one inaccurate point before I moved on. So, what if our InflateAll method had the following signature?:

InflateAll<T>(List<T> tires)
{
 T.Inflate(i => i.Inflate());
}

Suddenly it can receive a list of any type of object which as a programmer may make you panic. How do we determine that the objects passed in our list will actually have a method I can use to inflate them? Well as it turns out, there's this neat little idea called Type Constraints that actually allow us to limit what T can be.

InflateAll<T>(List<T> tires) where T : TireBase
{
 T.Inflate(i => i.Inflate());
}

This means that we can now constrain the list to any class that derives from TireBase - in the same way I we can did it before, but using generics. We've now defined a method that can receive a list of any type, providing it falls within the definition of the T type constraint.

Whereas before we had to define the list we passed in as List<TireBase>, we can now pass in an instance of List<TireBase>, List<SportsTire> or List<RoadTire> or indeed a list of any other type of tire deriving from TireBase - in a similar manner as if we had used an interface:

interface ITire
{
 public bool Inflated{ get; }
 public void Inflate();
 public void Deflate();
 public void DisplayInflateState();
}

The one slight difference is that the classes that implement the interface would have to handle their own constructor logic instead of passing it up to the base class as we did in the previous example.