Wednesday, July 30, 2008

Don't guess. Test! - A sample database with test suite

Some time ago, with the help of Patrick Crews, I built a sample database for testing.
Now this database is published as a stand-alone project on Launchpad.

What's special about it?
Unlike the previous databases used in MySQL documentation and tutorials, this database has some weight. The total data is over 160 MB, distributed across 6 table, for a total of about 4 million records. It is not huge, but it is large enough to be non-trivial.
The second important feature is that this database comes with a test suite. This will allow you to make sure that you have loaded the right data.

Getting started

Using the sample database is trivial.
Open the test db downloads page and get the current full archive (25 MB) containing the installation script, the dump files, and the test suite. Then, expand it.
$ tar -xjf $HOME/Downloads/employees_db-full-1.0.4.tar.bz2
$ cd employees_db/
$ vim employees.sql
Edit the employee.sql script, and uncomment the storage engine that you want to use for your tests. By default, it uses InnoDB.
   set storage_engine = InnoDB;
-- set storage_engine = MyISAM;
-- set storage_engine = Falcon;
-- set storage_engine = PBXT;
-- set storage_engine = Maria;
Then, load the files.
$ mysql -t < employees.sql
+-----------------------------+
| INFO |
+-----------------------------+
| CREATING DATABASE STRUCTURE |
+-----------------------------+
+------------------------+
| INFO |
+------------------------+
| storage engine: MyISAM |
+------------------------+
+---------------------+
| INFO |
+---------------------+
| LOADING departments |
+---------------------+
+-------------------+
| INFO |
+-------------------+
| LOADING employees |
+-------------------+
+------------------+
| INFO |
+------------------+
| LOADING dept_emp |
+------------------+
+----------------------+
| INFO |
+----------------------+
| LOADING dept_manager |
+----------------------+
+----------------+
| INFO |
+----------------+
| LOADING titles |
+----------------+
+------------------+
| INFO |
+------------------+
| LOADING salaries |
+------------------+
Finally, test that your data is what it is supposed to be. The test suite include two methods, one with MD5 and one with SHA1.
$ time mysql -t < test_employees_sha.sql
+----------------------+
| INFO |
+----------------------+
| TESTING INSTALLATION |
+----------------------+
+--------------+------------------+------------------------------------------+
| table_name | expected_records | expected_crc |
+--------------+------------------+------------------------------------------+
| employees | 300024 | 4d4aa689914d8fd41db7e45c2168e7dcb9697359 |
| departments | 9 | 4b315afa0e35ca6649df897b958345bcb3d2b764 |
| dept_manager | 24 | 9687a7d6f93ca8847388a42a6d8d93982a841c6c |
| dept_emp | 331603 | f16f6ce609d032d6b1b34748421e9195c5083da8 |
| titles | 443308 | d12d5f746b88f07e69b9e36675b6067abb01b60e |
| salaries | 2844047 | b5a1785c27d75e33a4173aaa22ccf41ebd7d4a9f |
+--------------+------------------+------------------------------------------+
+--------------+------------------+------------------------------------------+
| table_name | found_records | found_crc |
+--------------+------------------+------------------------------------------+
| employees | 300024 | 4d4aa689914d8fd41db7e45c2168e7dcb9697359 |
| departments | 9 | 4b315afa0e35ca6649df897b958345bcb3d2b764 |
| dept_manager | 24 | 9687a7d6f93ca8847388a42a6d8d93982a841c6c |
| dept_emp | 331603 | f16f6ce609d032d6b1b34748421e9195c5083da8 |
| titles | 443308 | d12d5f746b88f07e69b9e36675b6067abb01b60e |
| salaries | 2844047 | b5a1785c27d75e33a4173aaa22ccf41ebd7d4a9f |
+--------------+------------------+------------------------------------------+
+--------------+---------------+-----------+
| table_name | records_match | crc_match |
+--------------+---------------+-----------+
| employees | OK | ok |
| departments | OK | ok |
| dept_manager | OK | ok |
| dept_emp | OK | ok |
| titles | OK | ok |
| salaries | OK | ok |
+--------------+---------------+-----------+

real 0m37.067s
user 0m0.007s
sys 0m0.009s

$ time mysql -t < test_employees_md5.sql
+----------------------+
| INFO |
+----------------------+
| TESTING INSTALLATION |
+----------------------+
+--------------+------------------+----------------------------------+
| table_name | expected_records | expected_crc |
+--------------+------------------+----------------------------------+
| employees | 300024 | 4ec56ab5ba37218d187cf6ab09ce1aa1 |
| departments | 9 | d1af5e170d2d1591d776d5638d71fc5f |
| dept_manager | 24 | 8720e2f0853ac9096b689c14664f847e |
| dept_emp | 331603 | c2c4fc7f0506e50959a6c67ad55cac31 |
| titles | 443308 | bfa016c472df68e70a03facafa1bc0a8 |
| salaries | 2844047 | fd220654e95aea1b169624ffe3fca934 |
+--------------+------------------+----------------------------------+
+--------------+------------------+----------------------------------+
| table_name | found_records | found_crc |
+--------------+------------------+----------------------------------+
| employees | 300024 | 4ec56ab5ba37218d187cf6ab09ce1aa1 |
| departments | 9 | d1af5e170d2d1591d776d5638d71fc5f |
| dept_manager | 24 | 8720e2f0853ac9096b689c14664f847e |
| dept_emp | 331603 | c2c4fc7f0506e50959a6c67ad55cac31 |
| titles | 443308 | bfa016c472df68e70a03facafa1bc0a8 |
| salaries | 2844047 | fd220654e95aea1b169624ffe3fca934 |
+--------------+------------------+----------------------------------+
+--------------+---------------+-----------+
| table_name | records_match | crc_match |
+--------------+---------------+-----------+
| employees | OK | ok |
| departments | OK | ok |
| dept_manager | OK | ok |
| dept_emp | OK | ok |
| titles | OK | ok |
| salaries | OK | ok |
+--------------+---------------+-----------+

real 0m33.453s
user 0m0.007s
sys 0m0.009s

Now we're ready to use it.
The test-db project is open. Participate!

6 comments:

Keith said...

Giuseppe,

Thanks for the hard work on this. It will be really useful for a lot of people!

Keith

Nicklas said...

Nice Giuseppe, I know I've wanted a larger test database a few times, glad to see someone created one!

Branko Ajzele, engineer said...

Thank you so much for this. I started learning Doctrine ORM for PHP and this will certainly be of great help with testing all sort of stuff.

Pierre said...

Giuseppe, your database is just what I need to do some performance testing of web services. I am writing about that in my blog.

Thank you
Coffee_fan

Anonymous said...

Giuseppe,

Thanks a ton for the DB!

Harshal said...

The test DB shall be very helpful. Thanks a lot.

Vote on Planet MySQL