M1ke

Date Specific Database Testing

I posted earlier on Twitter:

Testing a DB which has records by today's date. Is there a more elegant way to test than by removing the "WHERE date=" on dev branch?

To elaborate on this a bit more I'm working on an application which allows a user to specify that a resource is available on a set date. Therefore we have an availability table as follows:

  • available_date
  • resource_id
  • claimed?

Each time a search is done, e.g. a search for 18/04/2012 the site will list all available resources:

SELECT resource_id FROM available WHERE available_date='2012-04-18' and claimed=0

During development I have a few example resources, however each new day I test the search there are no available records for that day. So I'd either have to log in as the owner of each resource and set them as available, log in as an admin and make all available and do something a bit more tricky on the back end.

For example

SELECT resource_id FROM available WHERE ".($dev?"":"available_date='2012-04-18'")." and claimed=0 GROUP BY resource_id

However this adds processing to the live version, not much at all, but it still feels like adding bloat. I've now come up with an alternative, which is a stored procedure in SQL:

CREATE TEMPORARY TABLE available_temp ENGINE=MEMORY SELECT * FROM available GROUP BY resource_id;

UPDATE available_temp SET available_id='',available_date=CURDATE();

INSERT INTO available SELECT * FROM available_temp;

DROP TABLE available_temp;

This will effectively duplicate one record per resource each time it is run. If it runs twice in a day it will produce two sets of available records for that day, which could be confusing if the "claimed" value differs, but that can be avoided by logging it in my build script.

Can you think of a more elegant way to do this?

Find us on StuRents