Analysis of unit test of database program

Elementary analysis of unit test of database program Source: internet/Author: Xu ningning/Published: 2007-02-14///><!--read: 55 times//--><!

This article is the author's experience in the database functional testing, examples of which are written in the Java language, but I think these ideas for most programming environments are generally applicable. Of course, I'm still working on finding better solutions.

The real problem is this: you have a SQL database, some stored procedures, and a middle tier between the application and the database. How do you insert a test

The implementation of the data access function in the database by testing the code.

Why is there such a problem?

I guess some, maybe not exactly, most of the database development process is like this: Build the database, write the code that accesses the data to the database, compile and run it, and use a query to verify that the data listed is displayed correctly. If you can show it correctly, you're done.

However, the disadvantage of this eye-detection is that you do not often conduct such tests, and this test is incomplete. There is the possibility that when you modify the system, after a few months, you inadvertently destroy the system, resulting in data loss. As a programmer, you may not spend a lot of time checking the data itself, which makes it take a long time for the wrong data to be exposed. I have been involved in a project to establish a website, in which a required data was not found to be entered into the database during the year of registration. Although the company's marketing department once suggested that they needed this information, since no one had ever seen it, it directly led to the problem being uncovered for a long time.

Automated testing reduces the risk of data loss due to its frequent testing and wide range of testing. I found it would make me rest more comfortably. Of course, there are other benefits of automated testing, which are themselves examples of code writing, as well as documentation that allows you to modify the original program written by others, thus reducing the time required for testing.

What is the test we are talking about.

Imagine a very simple user database, including a user's email address and a flag indicating whether the email addresses were bounced back. Your database program should include methods such as inserting, modifying, deleting, and querying.

The Insert method invokes a stored procedure to write data to the database. For the sake of narrative convenience, there are a few details omitted here, the approximate procedure is as follows:

public class Userdatabase

{

...

public void Insert (user user)

{

PreparedStatement PS = Connection.preparecall (&quot;

{Call User_insert (?,?)} &quot;);

Ps.setstring (1, User.getemail ());

Ps.setstring (2, User.isbad ());

In real life, this would is a Boolean.

Ps.executeupdate ();

Ps.close ();

}

...

}

And I think the test code should be:

public class Testuserdatabase extends TestCase

{

...

public void Testinsert ()

{

Insert a test User:

User user = new user (&quot;some@email.address&quot;);

Userdatabase database = new Userdatabase ();

Database.insert (user);

Make sure the data really got there:

User Db_user = Database.find (&quot;some@email.address&quot;);

Asserttrue (&quot; Expected Non-null Result&quot;

Db_user = null);

Assertequals (&quot; Wrong Email&quot;

&quot;some@email.address&quot;, Db_user.getemail ());

Assertequals (&quot; Wrong bad flag&quot;, false, Db_user.isbad ());

}

...

}

Maybe you have more test code. (note some tests, such as tests on the date Class).

Asserttrue and Assertequals methods for conditional testing. If the test fails, they will return a diagnostic message. The point is that these tests are performed automatically based on a test framework and give a sign of the success or failure of the test. These tests are based on the JUnit class of the test framework written in the Java language (after the program is attached). This framework also adapts to other locales such as C, C + +, Perl, Python,. NET (All languages), PL/SQL, Eiffel, Delphi, and VB.

The next question is: we have tests, but how do we ensure that the test data and actual data can be strictly differentiated.

Different methods of identification

Before I start, I have to point out that you'd better have a test database, and you might want to practice what I'm talking about in an informal database.

The first method is to manually enter some pre-known test data into the database, such as entering "testuser01@test.testing" in the email address. If you are testing the database query function, you can know beforehand, for example, there are five, the database record is "@test. Testing" end.

The data inserted by the above method must be maintained by the test itself. For example, the test must be responsible for deleting the established test data and avoiding the actual data, thus ensuring that the entire database is intact.

This approach still has the following problems:

You have to coordinate data with other programmers – assuming they have their own test database.

There are some special data in the database that are not correct, such as some special e-mail addresses and reserved number prefixes.

In some cases, you will not be able to use some special data to distinguish between test data and actual data, which is tricky. For example, a piece of data is made up of some integer fields, and the values used as tests look reasonable.

Your tests are limited to certain special values that you keep for the test, which means that you will be careful to select those special values.

If the data is time-sensitive, it will be more difficult to maintain the database. For example, a product sales proposal is available in the database, and the proposal is valid only for a definite period of time.

I have tried to make a change. For example, adding the "is_test" field to the database as a marker to differentiate the test data avoids the problem of special values. The problem, however, is that your test code will test only those data that is marked as test, while your formal code is dealing with data that is not marked as test. If your tests differ in this respect, you are not actually testing the same code.

You need four of databases

Some think that a good test is sufficient enough to build all the data needed for testing. If you know the state of the database before the test is done, the test can be simplified. A simplified approach is to establish a separate unit test database for testing programs, and the test program clears all data from the test database before it starts.

In the code, you can write a Dbsetup method, as follows:

public void Dbsetup ()

{

Put the database in a known state:

(Stored procedures would probably be better here)

Helper.exec (&quot;delete from somesidetable&quot;);

Helper.exec (&quot;delete from user&quot;);

Insert some commonly-used test cases:

...

}

Any database test program will first call the Dbsetup method before doing anything, which will leave the test database in a known state (in most cases, an empty database state). This approach has the following advantages:

All test data is communicated at the code level and other programmers, so there is no need for external test data coordination.

No need to test the intervention of special data.

A simple and easy way to understand.

It may take more time to delete and insert data before each test, but because of the relatively small amount of data used for testing, I think this method is faster, especially when testing a local database.

The downside to this approach is that you need at least two databases. But keep in mind that they can be run on the same server as necessary. Using this method, I used four databases, and the other two were used in emergency moments, as follows:

1. Actually use the database, including the actual data. Do not test in this database to ensure the integrity of the data.

2. Your local development database, which is used for most tests.

3. A local development database that joins a certain amount of data, which may be shared with other programmers to run applications and detect the ability to run on a database that is actually used, rather than copying all the data actually used in the database. You may not need this database in the strictest sense, but this database ensures that your application runs smoothly in a database with a large amount of data.

4. A publication database, or integration database, used to perform a series of tests prior to the official release, ensuring that modifications to all local databases are confirmed. If you develop it by yourself, you can omit the database, but you must ensure that all modifications to the data structure and stored procedures are confirmed in the actual usage database.

In the case of multiple databases, you want to ensure that the structure of the different databases is synchronized: If you change the definition or stored procedures of a table in a test database, you must remember to make the same changes on the server you are actually using. The purpose of the publication database is to remind you to make these changes. In addition, I found that if the code control system can automatically send the comments to the entire development group, it will be of great help to team development. CVS can do this, and I hope you can take advantage of that.

Test in a suitable database

In this case, you must connect to the correct database. It is frightening to me that it is possible to remove all useful data in the actual use of the database for testing.

There are several ways to avoid the occurrence of such tragedies. For example, it is common practice to record database connection settings in an initial file to identify which is the test database. You can also test the local database with the initial file, and connect to the actual database using the other specified methods.

In Java code, the initial file might look like the following;

Myapp.db.url= "Jdbc:mysql://127.0.0.1/mydatabase

This connection string is used to connect to the database. You can add a second connection string to differentiate the test database:

Myapp.db.url= "Jdbc:mysql://127.0.0.1/mydatabase

Myapp.db.testurl= "Jdbc:mysql://127.0.0.1/my_test_database

In the test code, you can check and make sure that the application continues to run after you connect to the test database:

public void Dbsetup ()

{

String test_db = Initproperties.get (&quot;myapp.db.testurl&quot;);

String db = Initproperties.get (&quot;myapp.db.url&quot;);

if (test_db = "=" null)

Abort (&quot; No test database configured&quot;);

if (test_db.equals (db))

{

All are well:the database we ' re connecting to is the

Same as the database identified as &quot;for testing&quot;

}

Else

{

Abort (&quot; Would not run tests against a non-test database&quot;);

}

}

Another trick is that if you have a local test database, the test program can be detected by providing an IP address or hostname. If it is not "localhost/127.0.0.1", there is a risk that the connection will be tested on the actual use of the database.

The experience of testing date

If you want to store date information, you probably want to confirm that the date information you saved is correct. Please note the following points.

First ask yourself, who created the date. If it's your application, it's easier to verify because you can compare it by looking at a specific date in the database. If the date is created by the database itself, possibly as a default field, then you may have some problems. For example, can you make sure that your code represents a time zone that is consistent with the time zone of the database? Never heard of a database that shows time and date in Greenwich Mean time. Can you make sure that the time on the computer where the application is running is consistent with the time on the computer where the database resides. If not, you must set aside some errors when comparing time.

If you encounter these situations, there are some things you can do:

If you know the time zone in advance, convert all dates and times to the date and time of that time zone before testing.

Set a certain error when comparing time, such as a few minutes, hours, or months. Appears to be unconvincing, but at least it captures errors such as the date is empty or January 1, 1970.

Summarize

In this article, I would like to say:

Unit database testing is a worthwhile thing to do;

If you can give a series of test programs a corresponding database, the test itself is not very scary.

There are other ways to solve this problem. I'm still not sure how to mimic the object (mock objects). In my understanding of this approach, the mock object simulates a system middle layer (in this article, the database operating system), making the emulated database always return the data you want.   I appreciate this concept, which encourages you to layer tests, possibly dividing them into SQL-related tests and Java language tests, to test the simulated resultset objects. I'm more concerned with the actions that can cause a change in two or more data tables at a time. In this case, it is difficult to use the Imitation object method to maintain and implement the database. Of course, I also have to find a good way to test the SQL aspects of the database to verify that the data is correctly