Architecture and Data Blog

Recently we had peculiar problem. Some of the data in the database was not being created in a proper fashion. Once we found that out we fixed the problem in the application. The customer still had the perception that the code is still broken, because the fixed code was now interacting with the data that was broken (since it was created much earlier by code that was broken). Data has a life of its own (more on this later)

The following SELECT statement in code

stmt = DB.prepare("select id,name,state,zip " +
        "from customer " +
        "where " +
        "phone = ? " +
        "and active = ?");
stmt.setString(1, customerPhone);
stmt.setBoolean(2, isActive);
stmt.execute();

where customerPhone and isActive are values you would pass in to the SELECT before its executed. Everything is fine when one day the value passed for customerPhone is NULL. For a database (Oracle is what I know most) a NULL will never be equal to NULL , the SELECT will not return rows where the customer.phone is NULL, leading to wrong results. The SELECT will have to be changed to

While working on a Legacy Application with Legacy Database design as part of fixing a bug, I thought this bug would not have ever happened if a particular column was defined as Non Nullable since this particular column was the identifier to the parent table.

We had a Customer table and the all the names a customer could have like LegalName, LongName, ShortName etc are stored in the CustomerName table. CustomerName cannot exist without Customer hence its logical that the CustomerName.CustomerID column cannot be nullable and should also have a Foreign Key constraint enforcing the relationship. Implementing just the Foreign Key constraint is not enough since the application could potentially be inserting null values in the CustomerName.CustomerID creating orphan records.

Some time ago I wrote about what it means to do database testing.. more I think about this and having had some strange situations recently I want to add more to the list of things we should be testing.

Persistence Layer We should persist the objects to the database using the applications persistence layer and retrieve the objects using the same mechanism and test that we get the same object back. If we have a lot of business logic in out persistence layer we may also want to retrieve the object using Direct SQL and test that the correct values got persisted.

You learn a lot from kids, and this lesson I will never forget.

So I’m at Lyon (in France) airport and there are a couple of kids (4-5 year olds) playing in the airport play area which had slides, call these two kids TA and TB my daughter call her AA joins these kids and they all start playing with the slide.

All is going well when a family with two kids, mom, dad and grandma join in, mom and dad are staying away and grandma takes the kids lets call them FA and FB to play on the slides. Grandma makes the previous kids (AA, AB, AA) stop playing and clears the slide for her grand kids (EA and EB) to play the slide. Grandkids start playing for a while and TA and TB are just waiting around not knowing what to do. AA by now has moved on to a different toy in the airport.

I have taken up the hobby of searching the opensource landscape for tools that help me do Agile database development. I’m going to write about all the Tools that I come across that help me, my preference is opensource software but not limited to it. I will try to provide some sound examples and share my experiences with all that tools that I come across and share the example code I used.

I had been on vacation for sometime. Started back work and getting connected with work and life again.

What does it mean to test your Database? usually when someone mentions database testing, what is that they want to test. The application code that interacts with the database, or the sql code the resides in the database like stored procedures and triggers etc. I see all these aspects to database testing as important.

Testing the applications persistence mechanism We should test that the application persists what its supposed to save and retrieve the data using SQL and see if the database contains the same information that is being saved, this kind of testing makes sense when the application has complex persistence layer. This type of testing can be achieved using unit tests, functional tests etc.