sql

I have been thinking recently about how the database can influence our design decisions, and perhaps makes them harder than they need to be in some cases. An example of this is the design of a system which stores data about people, specifically for this, their email addresses. A cut down version of the structure is this: table people id serial primary key firstname varchar(50) lastname varchar(50) table emails id serial primary key person_id int => people....

We work we have a number of windows services which each have a lot of stats they could expose. Currently they are only interrogatable by the logfiles and from any notifications we receive. I have been toying with the idea of hosting a website in-process which would give a simple dashboard ui and access to a live view of the log file. The idea first struck me when I was experimenting with FubuMvc, as they have an EmbeddedFubuMvcServer, which is very easy to use:...

In the last post we looked at using custom ID types to help abstract the column type from the domain. This works well until you start trying to load and save entities using an ORM, as the ORM has not way to know how to map a column to a custom type. ORMs provide extension points to allow you to create these mappings. As I tend to favour using Dapper, we will go through setting it up to work with our custom ID types....

The Database is just an Implementation Detail A quote from Martin Fowler given during his Architecture talk stated that the Database in your application should just be an implementation detail. I agree on this wholeheartedly and find that its really not that difficult to achieve if you think about your architecture carefully. Having said that, I still see parts of the database implementation leaking out into the domain, mainly in the form of IDs....

One of the databases I use on a regular bases has a rather interesting column naming scheme; all columns have a prefix, based on the table name. For example, the table containing people would have the prefix PEO_, so you would have this: Select * from People PEO_PersonID, PEO_FirstName, PEO_LastName, PEO_DoB ----------------------------------------------------- 1 John Jones 1984-07-15 I believe the idea was so that when querying, you would not have any column name clashes....

For the last 6 years or so at work, we have had an intermittent bug. In this case, intermittent means around once in 6 months or so. A little background to the problem first: Our data access is done via what was originally Microsoft’s SQLHelper class, passing in a stored procedure (and parameters), and our entities use the reader to load all their properties. Pretty straight forward stuff. The problemis, on the live system, every few months a sproc will stop returning results, for no apparent reason....

Today I learnt a few new (well to me) SQL commands. The Like statement can do some basic regex type things. It supports character specifiers like this: Column Like '%[a-z]Test[a-z]%' This will find the word test as long as there is a letter at either end of the word in a block of text. You can also say Not a letter like so: Column Like '%[^a-z]Test[^a-z]%' This should find any words Test that do not have letters before or after them....