2

I've had a hard time making unit test for most of my queries because they are tied very closely to current dates and data which is added to the database could modify the result of the unit test.

For the most part I've been able to avoid problems by relying on a second version of the database filled with immutable dummy data. So my unit tests can go on with expected results without much of a hitch.

But there are some queries that I still don't know what to do with. I have a query that fetches two columns, a date and an associated value from a table based on:

WHERE Date > Now() 

So my enemy here is that tomorrow isn't today. I am using the actual table not a dev version of this table for the test as data entered in this table is sensibly static and shouldn't be changed once added. It's like a reference table so to speak.

If I were to make an immutable dev version of the table then my problem would be that eventually the table's dates will in two years time pass the latest date within the table and thus result in zero records found.

And regardless of whether I had a dev version or a real version, the results for this table will always change based on the current date.

Why does an answer come only once I have finished writing my question out. A thought just occurred to me, which I must say, I want to avoid, but it seems to me that in order to make a unit test function, I would have to have my unit test generate the table before running the query. But then, do I write a unit test for the version of the unit test that populates that table??

Is there a more sensical way of achieving this goal?

Conclusion

All the answers are valid. The one I selected as the accepted answer is the one I believe to be closer to a standard tdd way (a purist viewpoint), however it's more complex and annoying. The other answers though are very simple and nice, should not be overlooked. In fact the little database trick that RolandoMySQLDBA mention is genious for mysql tests.

Since I'm working with Access, For now what I am doing is making a default db which will be programmatically appended to the dev tables which are static. When time permits that table will have it's own append table that will alter the dates to work with the now() query.

For now() I guess that will do, time permitted.

1

Why I would not change the application

I do not like the answers which propose to change the application to fulfill testing needs. To me this is the most wrong way to go. The implementation should only be driven by concepts, requirements and design decisions of the application, not of any helper tools used while implementing.

Use dynamic fixtures instead

It is a very basic concept of all tests to set up a testing environment, also called test fixtures. This concept found its way into the unit testing as well, see for example fixtures in Rails, Python or Java. All propose to use fixtures for preparing specifically crafted databases:

Loading a database with a specific, known set of data

https://github.com/junit-team/junit/wiki/Test-fixtures

Fixtures allow you to populate your testing database with predefined data before your tests run.

http://guides.rubyonrails.org/testing.html#the-low-down-on-fixtures

This may involve, for example, creating temporary or proxy databases […]

https://docs.python.org/2/library/unittest.html

Nothing stops you from populating the test database dependent on the current date!

Actually this is pretty common anyway: For example every Rails object one creates gets the current timestamp as created_at and updated_at. Since they are created as part of preparing the fixture, all Rails objects are created with the date of the current test and thrown away afterwards.

I do not see a reason to verify the test fixture. If something is wrong concerning e.g. consistency, your application should throw an exception. To make sure that the tests cover all situations is your responsibility and no automatic verification will answer this question anyway.

  • I agree with you on premise of not altering the actual sql. But having a hard time wrapping my head around the part of populating a test database dependent on current date. Maybe I will have to simply separate myself from what I know the data to actually represent. And so far it feels like I would be building a whole separate program just to be able to put dates in the right place. Not sure all the info has sunked in. Will have to mull over it a bit. – Mallow Oct 29 '14 at 15:44
  • Perhaps what's limiting me here is that I am doing most of my tests in vba since the end user front end is access. So ruby, python, or java is a bit beyond the scope of this project at this particular moment in time. – Mallow Oct 29 '14 at 15:46
  • Actually I do not really understand the problem. You don't have a problem setting up a database with fixed dummy data? Just replace the creation implementation to insert data based on the current date? There must be some startup code of the test framework, which allows to set up the testing environment? – GhostGambler Oct 29 '14 at 16:53
  • Well I assumed I could just have a static DB. There is no startup sequence that just generates the DB for the testing environment. Only thing I have is something that changes my code to link from the actual db to a dummy one, but no "Framework" that sets up the environment. Rather the environment is already in place. If I explained that right? – Mallow Oct 31 '14 at 19:48
  • Hm… that's not good. Although I would guess it's not much more work to implement a framework, which creates databases on demand for the test, than to rewrite the application to use fixed dates (and keep track of that requirement for further implementations). – GhostGambler Oct 31 '14 at 21:02
1

If I have understood it correctly, what you can do is declare a Datetime variable and store current values in it, run your statements and use datetime variable in your where clause.

so

DECLARE @Now DATETIME SET @Now = GETDATE()

Your Insert Statements

Your SELECT WHERE Date > @Now

does that solve your problem ? or have I miss understood your Question ?

  • I believe that I might be misunderstood. Since if I'm doing unit testing I don't think I should change my query to fit the test, because then I would have two queries to maintain, which means the test wouldn't be testing a relevant query. However maybe I'm misinterpreting the answer and you are saying that instead of relying on asking for the date now() in the query I could make it a variable. However no, I still have the original problem because if I test for now() I have no way of knowing what's the expected value should be without using the very query I'm testing. Right?? – Mallow Oct 28 '14 at 17:31
1

First of all, let's contrast two words

  • Deterministic (Adjective for Determinism) : the principle in classical mechanics that the values of dynamic variables of a system and of the forces acting on the system at a given time, completely determine the values of the variables at any later time (Definition of Determinism)
  • Stochastic : of or pertaining to a process involving a randomly determined sequence of observations each of which is considered as a sample of one element from a probability distribution

Since you want to treat NOW() as deterministic rather than stochastic, you should create a table called UnitTestNow and initialize it with whatever date and time you have determined for the test.

CREATE TABLEUnitTestNow
(
    unit_id INT NOT NULL,
    unit_now DATETIME DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (unit_id)
);

Whenever you create a new unit test (for example, unit test id 27), simply run one the following:

INSERT INTO UnitTestNow (unit_id) VALUES (27);
INSERT INTO UnitTestNow (unit_id,unit_now) VALUES (27,NOW());

This will initialize the now value you need to retrieve before starting a unit test.

If you wish to initialize the now as of 3:30 AM, 5 days ago, do this

INSERT INTO UnitTestNow (unit_id,unit_now) VALUES
(27,CURDATE() - INTERVAL 5 DAY + INTERVAL 3 HOUR + INTERVAL 30 MINUTE);

If you wish to hardcode a specific date and time (i.e. 2014-10-28 09:30:00), do this

INSERT INTO UnitTestNow (unit_id,unit_now) VALUES (27,'2014-10-28 09:30:00');

Then, whenever you start a unit test, just retrieve it by unit_id:

SELECT unit_now INTO @now FROM UnitTestNow WHERE unit_id = 27;

This will allow you to have the same now for the data set of a unit test. If you change the data set of a unit and it needs a different now, just update the now in UnitTestNow by the unit_id.

GIVE IT A TRY !!!

  • Definitively learned something here! So I assume that you are also implying that @mouliin's answer applies here where I would change my original query from "WHERE Date > Now() " to "WHERE Date > @now" therefore defining my now outside of the query in question. And your really awesome example would take care of keeping the unit tests easily manageable and extendable – Mallow Oct 28 '14 at 17:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.