Ayende @ Rahien

It's a girl

NHibernate – Get Thou Out Of My Database – 2nd Edition

Following up on my previous post, the customer has complained about table names like [tbl_-1434067361], apparently they felt that this was misusing their naming policy. I told them that while I understood that, it did meet their naming policy. I got a new naming policy that stated that numbers are not allowed in column or table name, (and showing forethought) that table names must be composed of valid English words.

I, of course, decided that if this is what they wanted, they will get just that. And created this:

image

The words.txt file was taken from this URL: http://www.puzzlers.org/pub/wordlists/pocket.txt

The result is exactly per their specification:

create table [tbl_colonel] (
  [col_verbiage] INT IDENTITY NOT NULL,
   [col_unsparing] NVARCHAR(255) null,
   [col_indomitable] NVARCHAR(255) null,
   [col_bulldog] BIT null,
   [col_thank] DATETIME null,
   primary key ([col_verbiage])
)
create table [tbl_stump] (
  [col_upheaval] INT IDENTITY NOT NULL,
   [col_promissory] NVARCHAR(255) null,
   [col_predecessor] NVARCHAR(255) null,
   [col_chafer] NVARCHAR(255) null,
   [col_unyoke] INT null,
   [col_vise] NVARCHAR(255) null,
   PostId INT null,
   primary key ([col_upheaval])
)

create table [tbl_reprieve] (
  [col_wherewith] INT IDENTITY NOT NULL,
   [col_wolf] VARBINARY(8000) null,
   [col_legendary] NVARCHAR(255) null,
   [col_ago] NVARCHAR(255) null,
   [col_carabineer] DATETIME null,
   [col_referee] NVARCHAR(255) null,
   primary key ([col_wherewith])
)

The fun part about this approach is that you still get great level of security, while maintaining the naming convention. Even more than that, you get queries like:

SELECT col_verbiage, col_indomitable, col_bulldog, col_wolf, col_legendary, col_referee
FROM tbl_colonel JOIN tbl_repreieve ON tbl_repreieve.tbl_referee = tbl_colonel.col.unsparing
WHERE col_ago = 'Fun@house.at'

It is obvious that we are getting users & blogs with specific email, right?

This is actually much harder than just numeric values, because this is going to really mess with your mind.

Nitpicker corner: Yes, this is a humorous post, don’t take it seriously, and please don’t really do it to unsuspecting customers / DBAs. Unleash it on silly integration teams only.

Comments

jdn
06/08/2009 05:40 PM by
jdn

I once suggested to my DBA team that all table names should be First Names of People, like 'Steve' and 'Lisa' because then you can write SQL like "Insert into <marginally".

The ones that laughed were the good ones.

Rafal
06/08/2009 06:40 PM by
Rafal

Ayende, this great technique should make it into next edition of 'Teach Yourself Application Architecture in a Weekend'

matt hinze
06/08/2009 06:54 PM by
matt hinze

3rd requirement, the names of tables must be the names of entities in the system:

foreach (var clazz in configuration.ClassMappings)

clazz.Table.Name = getRandomFromExistingTableNames();

configurator
06/08/2009 06:54 PM by
configurator

No no no, this just won't work. You are bound to have collisions here. You should use this function to avoid collisions:

public string GetName(string prefix, uint code) {

StringBuilder result = new StringBuilder(prefix);

while (code > words.Length) {

    result.Append("_");

    result.Append(words[code % words.Length);

    code /= words.Length;

}

result.Append("_");

result.Append(words[code % words.Length]);

}

Tobin Harris
06/08/2009 07:14 PM by
Tobin Harris

Hahahaha. That's awesome.

Dave
06/09/2009 07:28 AM by
Dave

Security by obscurity is ever a good solution. And in this case it would only make your work harder when the shit hit the fence. SQL Performance analyze would become a real bitch.

Preventing people to run queries is a solution. That's where our nhibernate repository comes in. It's basically a facade around the ISession interface, but it prevents submitting a raw query. Only nhibernate objects or an ICriteria/IQuery instance is allowed.

Code should be as self describing as possible.

Dave
06/09/2009 07:29 AM by
Dave

Sorry, make that 'never a good solution.'

Dan F
06/09/2009 12:38 PM by
Dan F

Hahahahaha, nice!

Ian Nelson
06/09/2009 02:16 PM by
Ian Nelson

Who knew NH could double-up as a poetry generator?!

Harry S.
06/09/2009 10:05 PM by
Harry S.

@Dave,

Maybe you should check out the Nitpicker Corner at the end of the post...

Comments have been closed on this topic.