Slashdot Mirror
Strategies for Test Databases?
youngcfan asks: "I've been tasked with finding strategies for a test database that can be used effectively by both software developers and the QA team. We're a J2EE shop with most of the interesting pieces of the application interacting heavily with the database -- so we need to test it. We're ramping up on JUnit, but are looking for ways to test the database-driven pieces of code. Since QA needs the same database for functional testing separate from developers' unit tests, DBUnit doesn't seem to suffice. We also have the challenge of working on multiple releases at the same time, which only complicates how and when to add new data to the test database in a way that's useful and valid for everyone. We're looking for strategies for using a test database in a way that meets both the QA's and the developers' needs, works for multiple releases, and isn't a heavy burden to maintain given that the schema and code can change anytime before any of the multiple upcoming releases. Any suggestions?"
You'll find it much easier to create dedicated DBs for each test scope.
Say out loud: I'm an Aspie and I'm somewhat proud, I guess. Uh. Can I write an email in all caps instead? Hm...
Oracle, Sybase and MySQL can all be used as test databases.
Perhaps you really want to know how to test code that uses databases, which is a different question
There are many refactorings that can be done to reduce your dependency on a particular database install...but thats a rather large topic. I'm available for consultancy, post here and I can get in touch...
Some things you might like to consider
Test data sucks: there are too many real-world situations the developers fail to think of.
We're a pretty small shop, but here's what we do: The production server backup is loaded to the test server daily. Every developer maintains a set of scripts which make any needed databae structure modifications after the backup has loaded. All development and QA testing is done against this test database. Where the production data isn't stable enough for unit testing we force-feed a few specific rows (as few as possible). This gives us fresh, real-world data for development and testing, and when an application rolls out, the exact same set of modification scripts are usually run on the production server (i.e. the modification scripts have been indirectly but repeatedly tested themselves).
SMQ 90AE4B2BC4F6BEAF7340F0B40BA2DEF7340F6BC2D0392
http://www.quest.com/benchmark_factory/
It will help create, manage and then run tests against the Databases...
If you have to work on UNIT test (or single developer test) there are a lot of tools but if you are talking about SIZING, TUNING and so on you cannot reach your goals without using complex tools and working with more RDBMS. In the last 4 years I worked and designed testing processes on J2EE and without "high levels" tool we cannot understand when the probs are on the java code, on IO SW&HW subsystems, RDBMS or concurrency on classes or table rows. You have to develop testing code for specific goals. We spend less time using commercial tools (like BMC, Quest and Mercury for J2EE and web users simulation), we develop the 4th generation of our test processes and the last 2 times, using the commercial tools, we take half time on update processes and testing times.
You'd have 3 servers as close in configuration as possible. One houses your production enviroment DB and the other houses your test and one for the QA enviroment. You can get away with QA and TEST in the same server but you REALLY don't want a devoloper to crash the test box or bog it down with a bad query when they're doing QA.
Ask not what you can do for your country. Ask what your country did to you
DBUnit doesn't suffice? What's it missing? It's only function is to place the database into a known state before the test, to make sure the data is correct before you test with it. How can that not do what you want?
It also occurs to me that if you can't even decide what data is 'useful and valid to everyone' then your test data is nothing like the live data you will have. Here's my suggestion: If it seems like it'll be even slightly relevant to anyone, use it. Otherwise you aren't testing everything.
The constantly changing schema is puzzling also. Did you not plan your database beforehand? I'm guessing this is an XP shop then, eh? XP doesn't stand for 'no planning'. I can understand changes to the schema in the early stages of programming, but if you're getting close to 'multiple releases' then the schema should be pretty solid by now, and the little changes needed to make to DBUnit shouldn't be a big bother.
"If you make people think they're thinking, they'll love you; But if you really make them think, they'll hate you." - DM
Ideally, when unit testing your code, you should touch the database as little as possible. One way to do this is by coding to interfaces, especially for your data access objects, and allowing your junits to override the implementations at runtime with mocked DAO's that don't touch the database. You could also try using an Inversion of Control framework (such as Spring, PicoContainer, etc.) to help decouple your DAO implementations from your business logic.
... It is not desirable that our development database be a copy of production. Whenever we find a new bug that is data-state related, we re-create that data-state in our SQL and write a new test for it. For Test and QA, however, we do tend to use copies of production data.
The downside to this is that, if your DAO code is not generated, you'll still need to unit test it. For this we use an in-memory java database such as HSQL or Derby. We build the database from scratch for each test run, using sql scripts to create the tables, views, PK & FK's, populate the data, etc. This ensures that we're always testing against the same data, and because it is in-memory, it is VERY fast. In development, we try to rely less on JWebUnit because it is not fast (relative to JUnit), but when we do use it we prefer to run our server against a local database rather than a shared database. We usually use HSQL for this as well.
Worth mentioning
I'm confused by your statement. A single database server (Oracle, PostgreSQL, whatever) can hold many databases. You should definitely have two separate databases for each release (for developers and testing), and arguably a database for each developer for unit tests. It's a one-line change in your config files to switch from one database to another, hardly an onerous burden.
I guess some toys would only be able to handle a single database, but I can't imagine why anyone would use one when there are so many excellent free database servers.
(This is ignoring tools like Sleepy Cat DB since it's not something you would use in a J2EE context.)
For every complex problem there is an answer that is clear, simple, and wrong. -- H L Mencken
It sounds like you need someone intimately familiar with the database who is not a developer, but can do things like create scripts to build your schema and populate it with useful test data... this person is usually called a DBA.
DBAs are usually viewed by devs as complete assholes, because they scream and holler at devs who make gratuitous changes to schemas and stored procedures. But a good DBA will make your database issues go away.
Conformity is the jailer of freedom and enemy of growth. -JFK
Unit tests should be as minimal as possible. E.g., you might have a single record loaded to test the basic CRUD operations for a class.
Why? You can set up your JUnit failure method so it takes a snapshot of the database at the point of failure and mails it to you (as an XML attachment). This means you can run smoke tests nightly -- try doing that with a "complete" database that's been scribbled on by other tests and developers since the problem occurred.
For every complex problem there is an answer that is clear, simple, and wrong. -- H L Mencken
Consider getting storage that can provide data point-in-time copies (Snapshots). Use Snapshots of your production database for development. Using different Snapshots for different releases. If you don't like the changes, make a new Snapshot and rework the tables. You can also use Snapshots for upgrade testing.
You should use caution here. Moving your production data is never trivial. Snapshots are not free. Developement machines can load the point-in-time copy to the point where it could impact the production system. If your production load can't handle providing Snapshots, you should consider clones (Snapshots/Copies) of your database for development.
If your development application is on a virtual platform, (VMware in particular), you can do a Snapshot of your application (in VMware), test an upgrade and then rollback to the pre-upgrade OS. In this environment you might be able to script nightly baseline testing of builds.
machinator omnis sine licentia
I make a copy of the production database with *real data*. I augument the copy of the production db with the new schema. I then merge the schema back into the production database when I am happy with the testing.
In your case, it sounds like a traditional test environment of seperate machines and multiple instances is not the way to go. I would suggest using a virtualization server like VMWare or MS Virtual server or other related software. what this allows you to do is get one environment set up and established, and then make an image of it. Then you can mount this image into a virtual environment where everyone can bang away at it and no matter how bad they destroy the database, all you have to do is mount the image again and you are back where you started from. you could also mount multiple images that each group (developers and QA testers) could have access to, if you do eventually go that route (and you will).
If you do make any changes that need to be kept, you will need to re-image the environment with those changes. you might want to do that every few months and keep several images available in several different states. However, now the developer team need to establish a policy for storing their code. If they hose the virtual environment, it is easy for YOU to mount a new image, but what about their code changes? They will need to have a policy on code storage and maintenance, but that is a different question altogether.
--- The revolution will be digitized! - http://www.binrev.com/ ---
Ok, with a structured approach you can make testing walk in the park. First, listen to your costumer, what ar his needs? What does he wnt to do? Define input and output, and of course wht information needs to be stored, and what information can be tabulated. It's no use storing for example ge when you have a birthdate registered. Remove ALL information that can be derived. Make a paper drawing of the structure of your database. plan out the relations. Make sure to obey the CODD rules for design of a relational database. Don't go ovrboard with it though. Just remove the repeating groups (the reoccuring fields inside a table, if there are any) and draw up the general layout. Then proceed to define the content type (text field, integer, float, boolean,...) of each field and set up the test database. You allready have a paper blueprint, liturally so you can easily use an erasor and pencil to record any changes you make. If you've done your planning well, there won't be a need for changes. Now that you have a digital empty construct of the database, defined with the content types. you can then make masks on the input so that only the correct type of date, properly formatted (think of dates etc...) is sent to the database. Having done this you have now successfully created a proper test database. As mentioned earlier as a reply to this post, I agree, you should NEVER alow multiple user groups to access the database during testing time. Give every group it's own test database so the problems both in structure and usability can be recorded seperately from each user group. You get a much clearer view of the problems you might have this way. Better information makes for better sollutions. after the first round of testing compare notes of the different user groups problems, fix them, and go to the second round. Repeat as often as is nessecary. A good database is able to keep preforming forever. If the needs don't change a properly designed and tested databse will work till all our bones are in some history museum. And, provided it'sa relational database, changes in needs can usaully be complied with without having to redesign the entire database.
We maintain an SQL script that creates the database or, when run in an existing database, upgrades any stored procedures that are out of date, alters tables, etc. This script (actually the smaller scripts it is assembled from) is checked in to Subversion like any other source code.
Our unit tests work at the C# level, not SQL (they test the objects implemented using the database, rather than the database itself). Most tests start by running the creation script to create a fresh database, do things to it, and then throw it away when the test is done. This way tests are isolated from each other and all the usual unit-testy requirements are met.
The down side is that tests that involve the database are a lot slower than unit tests that don't.
The installer (.msi file) uses the same script to install or upgrade the database on the production server. Testers work from their own copies of the database, completely isolated from whatever freakish mess we developers have perpetrated in our development databases.
For (QA) test databases, it's generally not enough to just have a separate instance, you also need to support the following capabilities:
... the more confident QA is in the data, the more confident (and/or prone-to-automation) their can be.
1- "Clone" whatever is most recent on production
2- Revert to "known good QA state" (ie: big red reset button)
3- Dump current state for later use.
You need to be able to clone so that ad-hoc testing can be run against production data w/o making production impact. This doesn't have to be live, but can be like a once-a-week/once-a-month activity, or rotate out a slave DB every once in a while, or have your DB people test your backups / etc.
You need the ability to revert to a known good state so that specific tests can be run and those can be more easily automated. Like: search "foo", 7 results found (not 6, not 8, not "it was 8 a few seconds ago but now it's 9 because there's a new result that was just added)
The ability to dump out DB state is a very distant third, but can be helpful for post-testing analysis or being able to modify a particular DB snapshot to fit some particular testing needs and then dump that out to the file-system for later use.
QA is hard, thank you for trying to make it easier.
--Robert
For the scope of unit testing, try out HSQLDB. It's an in-memory database that you can connect to over JDBC, so even if you're using Hibernate or some other layered persistence engine you can simply switch your DataSource. If you're writing Java that follows the tenets of dependency injection, this is really straightforward.
Now, this can only really effectively test a few things, and generally, I find that it can only really be useful for exercising small operations, like individual DAO methods. This is actually where I'll shut up, since I've not yet found an effective method for testing inter-method and transaction based operations, but for unit testing HSQLDB has saved my life more times than I'd like to count.
Attention deficit disorder is a complicated issue, spanning several major... HEY LET'S GO RIDE BIKES!
Use an embedded (or at least small) database like McKoi or Apache Derby, have a script that defines the tables and some test data (which you can grab from a real test system). Then simply create the db once, and use the embedded jdbc url with your unit tests. Clear the database out, or destroy it before or after each unit test (you probably want to do it before each test, because there's no guarantee the last test exited cleanly). Ta da.
It's 10 PM. Do you know if you're un-American?
I also second the idea that developers and QA's normally should all have their own database running on separate servers.
Ideally, the developers and QA run against a smaller database that is (ideally) populated from scratch with a small dataset to speed development; and then for release testing use a much larger populated database or (if that's too difficult) a copy of the production database that has been appropriately scrubbed to get rid of confidential data.
The database offerings from the various major vendors allow you to "quiesce" the database which suspends new transactions, completes all pending transactions, and then ensure that all data and log are flushed to disk. Then, with the production system paused, take a hot point-in-time snapshot of the filesystem, effectively giving you a compelte database dump in a few seconds. (This requires a storage system that allows you to make snapshots -- NetApp's do this, for example.) Resume the database to let the production system continue, and then copy the snapshot of database files to another server and reconstruct a clone of the database.
Run the appropriate trimming/cleansing/schema update on the clone database, and then make a snapshot of THAT. You can then revert the database to a knowing starting point as you like. If your development requires schema changes, don't let developers make the schema changes directly -- insteead, insist on schema change DDL's to be scripted, and reapply the script to the snapshot at each refresh.
When doing the final release testing, get the latest snapshot of the production database, run the update scripts, and run the tests. If everything looks good, make another snapshot of the production database, and apply the updates to the production database.
Done right, you can always roll back the test
- Typically, the term Unit Testing refers to the testing of a single, fine-grained unit of code. In other words, to do your true Unit Tests, you should not be accessing any database.
- The question that I think you are asking, is "How do I get databases initialized with the correct schema and correct data for integration testing?" The answer is, as always, "It depends."
The two biggest factors for creating useful test environments are: "How often does your schema change?", and "How much data do you need in your database for meaningful test cases?"Schema Changes: As a J2EE architect, the first time I saw Ruby on Rails' database migrations my first impulse was to wonder, "Why the !@#$ is this not in Hibernate?" I am not aware of any slick framework for J2EE apps to manage DB migrations, so you may have to use your own migration scripts. Hopefully, your schema is not changing much.
Getting Data In There: This totally depends on how much data you need. My "favorite" reply to you was to have one snapshot of your production data per developer. That works great, as long as you don't have much data. My last project had I don't even remember how many terrabytes of data in prod. Do you really think the client was going to spring for that much storage and that many Oracle licenses to get one instance per developer? Yeah right. We had a full snapshot for performance testing, but regular integration testing was done on a representative subset of data.
DBUnit is a great way to initialize a small amount of data. For larger datasets, you cannot get away with things like DBUnit, as it would take hours, if not days, to get the data in there. For our performance testing databases, we had the prod data snapshot stored on a RAID-1. Before testing started, we broke the mirror and did testing against the degraded array. When it came time to reset the data, we shut down Oracle and rebuilt the array to the good snapshot. That wound up being very fast for us. For medium amounts of data, you could probably get away with using SQL*Loader.
They don't grade fathers, but if your daughter's a stripper, you fucked up. --Chris Rock
One approach that has worked for me in the past is the "backup and recover" approach. Basically, it works like this:
1) You maintain a canonical "test" database (or multiple ones). This database has the same functionality as the production database but generally contains much less data. No one touches this database unless they need to permanently modify the test data. After each release, you make a backup of the database and release that backup to everyone who needs a test database. They restore it to their own environment.
2) You always write changes to the database as scripts so that you can run them against your test database and your production database. Your release process has to change to include running any database modification scripts on the canonical test database as well as the production database. This ensures that your new test database matches the production database for that release.
3) You need to modify your test process so that it runs a database restore at the appropriate points. In our case, we always restore before QA functional tests (because they leave the database in an altered state) but we don't restore for unit-tests (because we insist they leave the database in the same state they started.)
The advantages to this approach is that everyone has a copy of an actual database and you get to see all the funkiness of your real environment. The downside is that you have to be very disciplined in keeping the backups for all releases, and for running modification scripts against both the test and production databases appropriately.
-Peter
We define our schema in an XML format. We have a class that builds a DB from that format, subclassed by database type, making skeletal DB install an automated process. This also means it's the same process to install a client site using Oracle as it is to install a test database on a developer machine using Postgres.
When our master build runs test cases, it drops all tables and creates them all fresh using the XML definitions. Each JUnit test case is responsible for ensuring it has the data it needs. In some cases, this is done by setting up a facade on the regular service so that the test can worry about semantics and not data storage. In other cases, the test (or a utility) creates test data. You could presumably also copy part of your live data, though that makes it much more difficult to know what the correct answer is in advance.
If you follow this structure, multiple releases with different schemas is trivial. Just have a parameter for the DB URL in your test suite and let it build the correct database version for you when it checks your schema out of your source repository.
(Incidentally, keeping your database schema in your source repository also allows easy comparison of database structure between code versions, making it easier to figure out what must happen when you upgrade.)
Ceci n'est pas une signature.
I work with a large, legacy codebase - about 2 million lines of code, 600 tables. Some bits are nicely written, some aren't. Concepts such as dependency injection, seperation via interfaces etc are not prevasive, so traditional unit testing approaches of mocks or HSQL are not useful (in fact I find they do not scale for 'meaningful' tests anyway).
So you have this legacy code base - you want to make changes, but how can you validate the result? One approach is to compare database states - one from a known good codebase, one from a modified codebase. DBUnit can be tremendously useful here - this is what I've done (perhaps too complex for explaining on Slashdot):
Create a common Unit Test base class that extends DBUnit's DatabaseTestCase. It will:
a) receive a list of modified table names from the concrete test class
b) if a system property is set, export a pristine copy of these tables prior to running the test - 'reference data'.
c) execute the use case (register a user, perform a transaction, whatever) - this just makes a 'blind' call into the
code proper.
d) if a system property is set, export the modified table data ('known good results')
The idea is you run this test twice:
1) With the original codebase, with result exporting enabled to generate known good results.
2) With the codebase under test - the results generated will be compared against known good results and DBUnit will flag any differences. You can get it to ignore stuff like sequnces,dates that will differ between runs.
The reference data generated in (b) is reloaded prior to running the test second test, so you start from the same point. Each concrete test class just has to:
* figure out what tables change within the test
* provide the test code itself
Everything else is managed by DBUnit - exporting/importing datasets, comparing datasets, etc.
http://sqlunit.sourceforge.net/ is based on JUnit and it specifically designed to test databases and result sets. It is what I use when building automated test streams. Supports many databases on fresh download and can be extended easily if required.
I had one client who had a bunch of customer records compromised when they sent out some data to a development firm for "testing purposes". There are several products out there that will take actual records, scramble them and spit out a "test" database. I'd highly recommend doing that, no matter what other methodology you use.
2 cents,
QueenB
HDGary secures my bank
This is not a bad thing.
First, listen to your costumer... Costumer? LOL! I'm pretty sure most costumers wouldn't have the faintest clue about how to set up a database testing environment. They might know about floppy hats and masks, but not floppy disks and markup. Sorry, dude. That was just the funniest typo I've seen in a long time. :-) I keep picturing some dude dressed like Will Shakespeare hunkering over a server, muttering "Verily, thou are a varlet!", or some such silliness.
Spring provides TestCase subclasses that provide a Spring ApplicationContext and a TransactionManager. Spring automagically starts a transaction in setUp() and rolls it back in tearDown(). They provide hooks to execute setUp() and tearDown() code both inside and outside the transaction. You can force the transaction to commit if you want, but that's not really what you want to do. I've found that this works really well for a number of reasons 1) initialize the database once, 2) unit tests are independent because they do not alter the database state permanently, 3) transaction management is independent of the production code and unit tests and 4) it performs pretty well - most RDMSes will keep uncommitted transactions in memory. There really shouldn't be a need to commit anything because _unit_ tests should not need to cross transaction boundaries.
I used Hibernate with Spring, and I think that helps a lot. I don't know if you have any control over that. Hibernate makes database manipulations much more terse, and it is actually pretty easy to write a little code to create a small bit of database state just for a particular unit test. Writing single use code to just toss in 15 rows using straight JDBC is, by contrast, more err, interesting.
The other thing Spring does for you is that it obviates the need for any hokey J2EE specific harnesses. One of the most sucky things about EJBs is that you have to have a running app server for them to be usable. Spring frees you from this burden. Your ApplicationContext(s) are valid inside and outside the container.
The other thing I would point out is that database schemas should not be such a precious resource. I develop on my laptop, so I don't want to be tethered to my corporate LAN (or any other LAN) in order to develop. Oracle XE is free, easy and quite reasonable resource-wise. MySQL, Postgres and other OSS databases are easy to run locally. Let everyone create their own local database installations. This is perfect for unit/integration/acceptance testing, anything functional in nature. If you deploy with the database on a dedicated server, you will still need to test that scenario, but that only impacts things like performance, high availability, etc. These are certainly important issues, so do test them, but for unit tests, just run a local database.
John McNair
I used this recently for running test cases against Python code, and it worked great! I placed some DB population code in my setUp() method so you can run the test from any dir and it works -- no DB server needed!
It works like MS Access (file-based) but supports most of the SQL92 standard.
http://sqlite.org/