Tester 'll Never " Satisfied "

Unit Testing Database Logic for SQL Server Database Developers

I haven't tried it, but Karl Gram seems to be cooking up something interesting - dbUnit for unit testing database logic.

dbUnit aids the developer in creating unit tests for all your database logic. You can test the table structure, inserts, updates, deletes, triggers and also procedures. In general the tool allows you as a developer to write all your unit tests in T-SQL and use NUnit to unit test them.

Some of the features of the current alpa version (0.5) are:

• Create a dbUnit project
• Add unit tests to the project
• Specifiy the SQL Server to connect to
• Handle expected exceptions (e.g raiserror and other database errors)
• Generate the NUnit test dll
• Start NUnit from within dbUnit

Oracle Tips

 

Oracle regression testing tips

 

Deploying complex applications has always been a challenge.  Most ERP databases have thousands of tables and millions of lines of related code.

 

Oracle Applications patch management has always been a super complex task because of the recursive many-to-many relationship between application patches.  For example, a patch for a Accounts Receivable screen may have many prerequisite patches.  Immediately after this patch is released, it may become a prerequisite for a collection of subsequent patches.

 

Remember, to an application, a "patch" may take many forms, and it's not always a modified application executable.  The changes could be PL/SQL stored procedure code changes, schema changes or external application changes (i.e. changes to a host language like C++ or Java).

 

Managing QA testing for Oracle-based Applications

 

The concept of "regression testing" came about because people noticed that some fixes became "un-done" as a result of later patches.  In most applications we see a complex network of related tables and a corresponding complex network of interrelated application code.

 

When re-certifying a database after a change to the application layer, most people use a regression testing approach whereby the system behavior is checked using a known solution set of "proper" results.  The system then undergoes a regression test using several approaches:

Workload Simulation testing - An artificial simulation of end-user interaction is applied to the new software.

 

Workload Benchmark testing - Existing workloads are "replayed" to stress the new software.  There are many approaches to Oracle benchmarking.

 

The "impossible" certification challenge of complete regression testing

 

In some software, the complex interaction of the code and the sheer number of variables presents a serious issue of "technological feasibility".  In these cases, there are billions of possible testing permutations and it's technologically unfeasible to thoroughly run the whole universe of possible test-case usage combinations.

 

For example, a medical treatment machine once had a bug which killed someone, an obscure combination of settings caused a lethal dose of radiation.  When challenged, the software company said that they had only done spot-check testing because testing every possible combination would have taken many years, unjustly delaying the release of a life-saving medical treatment.

 

In the real-world the DBA must choose a statistically-reliable sample size for the regression test, one that does the deepest test of system functionality, while falling within reasonable time constraints.  While it may not be possible to test all of the obscure functional features, a workload of the most popular functionality can be sampled from a working system.

 

Regression Testing and Oracle

 

Oracle has many tools to facilitate regression testing in complex environments, tools to help ensure a production-like environment, with production data and a typical workload, long enough to capture all "popular" system functionality"

Data Quality features - In Oracle11g we now see a methods to automate regression tests using the popular Oracle Data Guard replication product.  The new standby snapshot feature allows you to encapsulate a snapshot for regression testing.  You can collect a standby snapshot and move it into your QA database, ensuring that your regression test uses real production data.

 

Workload Quality Features - Oracle 11g also provides an exciting new workload capture and replay feature that promises to allow the DBA to record and playback a set of "popular" transactions.

1. How to test data loading in Data base testing?

You have to do the following things while you are involving in Data Load testing.

1. You have know about source data (table(s), columns, datatypes and Contraints)

2. You have to know about Target data (table(s), columns, datatypes and Contraints)

3. You have to check the compatibility of Source and Target.

4. You have to Open corresponding DTS package in SQL Enterprise Manager and run the DTS package (If you are using SQL Server).

5. Then you should compare the column's data of Source and Target.

6. You have to check the number to rows of Source and Target.

7. Then you have to update the data in Source and see the change is reflecting in Target or not.

8. You have to check about junk character and NULLs.

RELATIONAL DATABASE TESTING

Black box testing
- Data values being persisted
- Data values being retrieved
- Stored Procedures / Functions
Clear box Testing
- Stored Procedures / Functions
- Triggers
- Views
- Constraints
- Existing Data quality
- Referential Integiry / Data consistency
Functionality Testing in Relational Databases
Stored procedures and triggers. Stored procedures and triggers should be tested just like your application code would be.
Relationship Testing in Relational Databases
Referential integrity (RI). RI rules, in particular cascading deletes in which highly coupled "child" rows are deleted when a parent row is deleted, should also be validated. Existence rules, such as a customer row corresponding to an account row, must exist before the row can be inserted into the Account table, and can be easily tested, too.
Data Quality Testing in Relational Databases
Default values. Columns often have default values defined for them. Are the default values actually being assigned. (Someone could have accidentally removed this part of the table definition.)
Data invariants. Columns often have invariants, implemented in the forms of constraints, defined for them. For example, a number column may be restricted to containing the values 1 through 7. These invariants should be tested.
Validate the attribute size. Is the field size defined in the application is matching with that in the db.
Performance Testing of Relational Databases
Access time to read/write/delete a single row.
Access time for common queries returning multiple rows.
Access time for queries involving several tables.
Existence test for an index. Does the expected index exist or not?
Structural Testing in Relational Databases
Table existence. We can check whether all the data from the application is being inserted into the database properly, or not
View definitions. Views often implement interesting business logic. Things to look out for include: Does the filtering/select logic work properly? Do you get back the right number of rows? Are you returning the right columns? Are the columns, and rows, in the right order?
Terminology
• Database testing. The act of validating the contents, schema, and functionality within a database. From the point of view of a relational database this includes the actual data itself, the table structures and relationships between tables, and the stored procedures/functions or database classes respectively.
• Database interface testing. Database testing which validates the database at the black-box level.
• Internal database testing. Database testing which validates the database at the white/clear-box level.
• Database regression testing. The act of running the database test suite on a regular basis, ideally whenever someone does something which could potentially inject a defect into the database such as change how they write data into a database or change some of the code within the database itself.
• Test Driven Database Development (TDDD). Also known as "Behavior Driven Database Development" (BDDD), this is the act of specifying the design of a database by writing a single test then just enough database code/schema to fulfill that test.
QTP Steps
Quick Test testing process consists of 6 main phases:

Create your test plan

Prior to automating there should be a detailed description of the test including the exact steps to follow, data to be input, and all items to be verified by the test. The verification information should include both data validations and existence or state verifications of objects in the application.

Recording a session on your application

As you navigate through your application, Quick Test graphically displays each step you perform in the form of a collapsible icon-based test tree. A step is any user action that causes or makes a change in your site, such as clicking a link or image, or entering data in a form.

Enhancing your test

Inserting checkpoints into your test lets you search for a specific value of a page, object or text string, which helps you identify whether or not your application is functioning correctly. NOTE: Checkpoints can be added to a test as you record it or after the fact via the Active Screen. It is much easier and faster to add the checkpoints during the recording process. Broadening the scope of your test by replacing fixed values with parameters lets you check how your application performs the same operations with multiple sets of data. Adding logic and conditional statements to your test enables you to add sophisticated checks to your test.

Debugging your test

If changes were made to the script, you need to debug it to check that it operates smoothly and without interruption.
Running your test on a new version of your application
You run a test to check the behavior of your application. While running, Quick Test connects to your application and performs each step in your test.

Analyzing the test results

You examine the test results to pinpoint defects in your application.
Reporting defects
As you encounter failures in the application when analyzing test results, you will create defect reports in Defect Reporting Tool.

• Module 1 Database Testing Concepts Primer

o The Database Component : What is a Database Application?
o Testing at the Database layer
o Why should Test professionals know DB Basics?
o The DB component:What is a Data-Based Application?
o Back end vs. Front End Testing
o Examining the data’s round trip through the app
o Common problems in Relational Databases that affect the Database Application
o Testing the Application vs. Testing the DBMS
o Knowledge Requirements for Database Testing
o Test Plan: Organizing your approach
o Data must pass Quality Assurance too!
o Test Set up


• Module 2 Relational Database Basics for Testing

o Why should Test professionals know Relational DB basics?
o What’s a relational database?
o Types of Data Integrity
o Lack of data integrity introduces bugs
o Identifying Design Defects
o Inspecting table structures to reveal design problems
o Exploratory Testing: Reading an ERD
o Table Relationships: 1-1, 1-many, many-to-many
o What to look for when Testing Relational Databases


• Module 3: Data Normalization Bugs

o What bugs are caused by improperly normalized databases
o Understanding Normalization:First, 2nd and 3rd Normal Form
o Understanding Denormalization
o Identifying poor design; developing Test cases


• Module 4: Introduction to SQL

o Why should Test professionals know Structured Query Language?
o SQL essentials
o Basic SQL statements for Testing