Friday, November 12, 2010

How to regression test a database application (part 2)

This is part 2 of 2. In part 1 I characterize what is meant by "database application". I also quickly review some different types of regression testing, and then describe a hypothetical retail inventory example system which will serve as the subject of detailed discussion. In part 2, I explore the "final table" type of regression test in detail, and apply it to our retail inventory system. I conclude with an argument for why "final table" testing should always be included on your project.

The challenge

I’m the project manager for the AIR system, which was described in some detail in part 1. The system has been humming along in production for years, and now my developers are working on some significant functional changes to the system which are scheduled to be released shortly. My biggest concern is to not break something that is already working. For that I need some type of regression testing, but I"m unsure what the best regression strategy is. Some, but not all, of my system is already covered by Unit tests, and there are a couple of higher level functional tests that cover a slice. But there is no end-to-end fully integrated systems test.
In an ideal (fantasy) world, I would now instruct my developers to write regression tests, at all levels, covering every aspect of the system. They would write low level unit tests to test every DB function, DB stored procedure, and Java method. They would then write a higher level functional test for each slice. Finally, they would script, or automate, a highest level fully integrated end-to-end systems test.
Figures 1 & 2 (same as in part 1) diagram the system.
Figure 1: System
Figure 2: Forecast slice
Taking the Forecast slice as an example, if I want to competely cover this slice with Unit tests, my developers will need to write several for the getProductList() stored procedure. More than one Unit will be needed in order to exercises all of the possible boundary and error conditions. The test harness will need to somehow populate the underlying tables/views that are read by getProductList(). Just the Unit tests required to provide decent coverage for the getProductList() could take an entire day to write. Next, my Java developers need to provide Units to blanket the forecast() method. Since forecast() calls the getProductList() stored procedure, the forecast() Units will need to mock the stored procedure. Setting up mock stored procedures is itself an exacting and laborious endeavour. Again, providing comprehensive Unit coverage for just forecast() might pin down a Java developer for an entire day. Finally, all of the tables and views touched by this slice will also need their own Unit tests. These Unit tests will ensure that defaults, invariants, and constraints are always observed within the tables and views. If the views implement any business logic, that too must be covered by Unit testing.
All of the above Unit tests are data intensive. So in each case, the test writer must think long and hard about the possible range of data values that the subject function might be exposed to. The test writer will need to think about both expected values for each parameter or data set, and also possible values for each. After all, one of the goals of software testing is to ensure that the test subject continues to operate properly even in the face of unexpected or "flaky" data or paramters.
It’s clear that providing deep Unit test coverage for just the Forecast slice will entail significant effort. 3 developer days (1 for java, 1 for stored procedure, and 1 for tables/views) might be generous, but it’s not an unrealistic estimate, considering all of the different concerns involved. The Forecast slice is just one of many slices that constitute the whole system. In a complex enterprise system, and that includes most Big Enterprise systems, there will be hundreds or thousands of slices.
If my whole system has just 100 slices, it will take 300 developer days to achieve exquisite, comprehensive, rock-solid, Unit test coverage. Current software testing theory dictates that this is exactly the type of Unit testing you should have on a well run project. But as the manager of project AIR, I don’t have 300 developer days to write Unit tests. Purists will object that most of the Unit tests should already have been written before the current iteration, but on project AIR (as on many, if not most, Enterprise systems) they weren’t. That’s the fault of the previous project manager, but he has since moved on, so now it’s my problem. The theory dictates perfect Unit testing, but it’s hard to square that with time and resource constraints:
In theory there is no difference between theory and practice. In practice there is.
Jan L. A. van de Snepscheut
Alternatively, I can abandon the idea of blanket Unit test coverage. Instead, I could go to the other extreme and build just 1, fully integrated, end-to-end, systems test. One way to carry out this system test is to:
Final Table Test
  • capture a complete set of inputs (files, tables, whatever) from the current production (pre-change) system.
  • in an isolated, controlled, test environment, run those inputs through the complete current production (pre-change) system in order to produce outputs. I’ll call the outputs "final tables", because all of the outputs are captured in DB tables and those tables are at the end of a chain (or pipeline) of processing. The outputs from this run are labelled "expected".
  • in an isolated, controlled, test environment, run those same inputs through the complete current in-development (post-change) system in order to produce outputs. Label those outputs "actual".
  • compare the expected final tables with the actual final tables. They should match, except in the areas where there are intended functional changes that are meant to produce changes to the outputs.
Some benefits of this scheme are worth considering carefully. This is the ulimately "black box" test. The testers do not need to understand anything at all about the internals of the system. They only need to know which buttons to press in order to run the system, and which are the "final tables" they need to compare. So this is the type of test that an independent testing group (say QA) should be able to effect. Another interesting aspect of this approach is that of test data selection. The good part is that it relies on current production inputs, exactly the real-world data conditions that the application will experience in practice. Also, simply reusing production data alleviates the burden of the test team needing to carefully consider and synthesize test data. This could be a significant labor savings.
But perhaps the most important aspect of this type of testing is that it effectively tests the system at all levels. Put another way, the final table test can provide much of the test coverage that would be provided by the collection of Units, whereas Units cannot provide any coverage at the functional or system levels. As a result, even if you have a blanket of Unit tests, you still really do need to do some form of functional or systems test. That’s because the interfaces, or boundaries, between functions, modules, and slices have to be exercised. As an example, look at the Forecast Slice. Suppose I write individual Unit tests for getProductList(), forecast(), and the tables and views. I test the forecast() method by mocking up the getProductList(). That’s not sufficient to provide confidence that the slice is production ready. I need at least some functional tests that exercise forecast() making a real call against the getProductList() stored procedure. Otherwise, the Unit tests, in mocking the getProductList(), might make assumptions about the parameters, behaviour, or side effects, that are not consistent with the real (non-mock) getProductList(). Likewise, it’s not enough for me to simply write a functional test that just covers each slice. I need a still higher level test that exercises the interfaces between slices. So the bottom line is that, if you absolutely have to, you can live without Unit tests and rely exclusively on a single, integrated, systems test (final table test). But the converse is not true. Even if you have superbe Unit test coverage, you will still need to perform some integrated systems testing.
Of course, the final table test is hardly bulletproof. Probably the biggest problem with this test is that it is a relative test, wherease the Units are absolute. By that I mean the final table test only tells you that the changed system behaves the same as the reference (current production) system that you compare it against. If the current production system is producing incorrect results, and your changed system passes the final table test, then your changed system will also be producing incorrect results in exactly the same fashion. The Unit tests, on the other hand, are measured against absolute expectations, not relative ones. So Unit tests are better grounded. Also, the final table test only tests aspects of the system that will be exercised by the current production data inputs. It’s possible that there are bugs lurking in the newly developed (changed) system, but that the bugs are not triggered by the current production data inputs. But the production data could change at any time in a way that would then trigger the bug, but in production. One more material drawback of the final table test is that it can only be carried out late in the Software Development Cycle, whereas Unit tests are able to uncover problems very early on in the cycle. That’s important because it’s generally, though not always, more expensive to correct bugs late in the cycle than early in the cycle.
Table 1. Units versus final table test
Units Final Table
bug detection
lowest, function
highest, system
test type
white box
black box
results type
impl. expertise
The key to deriving maximum benefit from the final table test is that the final tables comparison be carried out at the actual field value level. That is, every row+column in the expected final tables must be compared with its corresponding row+column in the actual final tables. Remarkably, very few shops that utilize a fully integrated systems test actually carry out these detailed comparisons. Instead, they rely on gross (summary level) comparisons, and visual inspection of the resulting data sets. That is completely inadequate.Many bugs can, and do, elude gross and visual comparisons.
The conclusion is that you must perform some type of fully integrated systems test, and a very powerful and cost effective option for this is the "final table test". Of course, high quality projects will also have low-level Unit tests and mid-level functional tests. However, if you are in a bind, constrained by very limited developer and testing resources, I would recommend that the first and foremost test you should carry out is the final table test.


  1. There are lots of information about latest technology and how to get trained in them, like Big Data Training Chennai have spread around the web, but this is a unique one according to me. The strategy you have updated here will make me to get trained in future technologies. By the way you are running a great blog. Thanks for sharing this.

    1. I feel this is among the such a lot vital info for me. And i am satisfied studying your article. However wanna commentary on few general things, The website style is ideal, the articles is truly nice
      Tangki Panel
      Tangki Fiberglass
      Jual Septic Tank

  2. A table is the basic unit of data storage in an oracle database. The table of a database hold all of the user accesible data. Table data is stored in rows and columns. But what is all about the clusters and how to handle it using oracle database system? Expecting a right answer from you. By the way you are maintaining a great blog. Thanks for sharing this in here.
    Oracle Training in Chennai | Oracle Course in Chennai | Oracle Training Center in Chennai

  3. Cloud storage is a model of data storage where the digital data is stored in logical pools, the physical storage spans multiple servers (and often locations), and the physical environment is typically owned and managed by a hosting company. The way you have explained everything is quite impressive and elegant. Thank you so much for sharing this data in here.

    cloud computing training in chennai
    cloud computing training
    cloud computing training chennai


  4. Thanks for sharing this valuable information to our vision. You have posted a trust worthy blog keep sharing.
    SAP training in chennai|sas training in Chennai|SAP course in chennai

  5. Quite Interesting post!!! Thanks for posting such a useful post. I wish to read your upcoming blog to enhance my skill set, keep blogging.
    Informatica course in Chennai|Informatica training in chennai|Informatica courses in Chennai

  6. Excellant post!!!. The strategy you have posted on this technology helped me to get into the next level and had lot of information in it.
    SAS Training in Chennai | SAS Course in Chennai

  7. Well Said, you have furnished the right information that will be useful to anyone at all time. Thanks for sharing your Ideas.
    Python Training in Chennai | Python Course in Chennai

  8. Nice Post! It is really interesting to read from the beginning & I would like to share your blog to my circles, keep your blog as updated.
    JAVA Training in Chennai|JAVA Course in Chennai|PHP Training in Chennai


  9. The strategy you posted was nice. The people who want to shift their career to the IT sector then it is the right option to go with the ethical hacking course.
    Ethical hacking course in Chennai | Ethical hacking training in chennai

  10. The post about regression testing is very much helpful.Thanks for sharing it
    Selenium Training Center in Chennai

  11. The post about regression testing is very much helpful.Thanks for sharing it
    Selenium Training Center in Chennai

  12. Java Online Training Java Online Training Java Online Training Java Online Training Java Online Training Java Online Training

    Hibernate Online Training Hibernate Online Training Spring Online Training Spring Online Training Spring Batch Training Online Spring Batch Training Online

  13. I appreciate this work amazing post for us I like it. I must say we should have an online discussion on this. We also share some information about our business
    eCommerce Support Services In India

  14. I like the post format as you create user engagement in the complete article. It seems round up of all published posts. Thanks for gauging the informative posts.
    cara menggugurkan kandungan obat penggugur kandungan tanda tanda kehamilan cara menggugurkan kandungan obat aborsi cara menggugurkan kandungan

  15. You have provided an nice article, Thank you very much for this one. And i hope this will be useful for many people.. and i am waiting for your next post keep on updating these kinds of knowledgeable things...
    Online Marketing Services
    Top SEO Company in Bangalore
    seo pricing india

  16. The provided information’s are very useful to me. It’s a wonderful site for learning web application. Thank you for sharing this wonderful blog.
    PHP Training in Chennai | PHP Course in Chennai | PHP Institutes in Chennai | PHP Training Center in Chennai

  17. Great Article… I love to read your articles because your writing style is too good, its is very very helpful for all of us. Do check Six Sigma Training in Bangalore | Six Sigma Training in Dubai & Get trained by an expert who will enrich you with the latest trends.

  18. Beberapa tips cara menggugurkan hamil untuk anda dengan menggunakan obat penggugur kandungan cytotec , terbukti dengan cepat untuk cara mencegah kehamilan . Maka dari itu pilihlah obat aborsi sangat ampuh . Jika anda telah haid obat telat datang bulan juga manjur , kami jual obat aborsi ini secara online .

  19. Good job! Fruitful article. I like this very much. It is very useful for my research. It shows your interest in this topic very well. I hope you will post some more information about the software. Please keep sharing!!
    German Classes in Chennai
    German Language Classes in Chennai
    Hadoop Training in Chennai
    Python Training in Chennai
    German classes in OMR
    German classes in Porur
    German classes in Tnagar


  20. شركة تنظيف في الكويت شركة تنظيف بالكويت
    فني صحي فني صحي في الكويت
    سباك الكويت سباك بالكويت

    شركة تنظيف كنب الكويت شركة تنظيف كنبات الكويت
    ادوات صحيه الكويت ادوات صحيه الكويت
    شركة غسيل سجاد الكويت مصبغة غسيل سجاد الكويت
    فني كهربائي منازل الكويت كهربائي منازل في الكويت

    شركة تنظيف في الكويت شركة الكويت سيرفيس للتنظيف

  21. ดูหนัง Earthquake Bird รอยปริศนาในลางร้าย (2019) แสนสนุก ดูหนังฟรี ดูหนัง HD ต้องที่นี่

  22. Diversify - It may be tempting to focus all of your media promotion on one site such as Facebook, but if you really want to supercharge your results, then you need to establish a presence on multiple social media sites, which will allow you to reach as much of your target audience as possible. keep itu p guys
    Ai & Artificial Intelligence Course in Chennai
    PHP Training in Chennai
    Ethical Hacking Course in Chennai Blue Prism Training in Chennai
    UiPath Training in Chennai


  23. เล่นเกมแล้วได้เงิน ได้ง่ายๆ ฟรีเครดิต สล็อตออนไลน์ เล่นสล็อต slot ไม่มีโกง คลิกเลยที่นี่

  24. สุดยอดเว็บแทงหวยออนไลน์ที่ดีที่สุดของประเทศไทย เว็บหวยออนไลน์อันดับ1 ของเมืองไทย ด้วยระบบที่ทันสมัย อยู่ที่ไหนก็เล่นได้

  25. Slot machine หรือ เครื่องเล่นสล็อต นับว่าเป็นเครื่องเล่น ยอดนิยม เยอะที่สุด ในทุกๆ คาสิโนทั่วทั้งโลก กระทั่งมีบางบุคคลได้กล่าวไว้ว่า เครื่องเล่นสล็อตเป็นตัวทำรายได้ทั้งหมดของคาสิโนมากกว่า 70% ของรายได้ทั้งหมด
    สำหรับผู้ที่เคยไปคาสิโนจริงๆก็คงจะได้สัมผัสเครื่องสล็อตมาบ้าง แต่ถึงอย่างงั้นสำหรับผู้เล่นหน้าใหม่อาจจะยังไม่เข้าใจว่าเจ้าเครื่องสล็อตนั้นเป็นยังไง หรือมีประวัติที่มาที่ไปยังไง เพราะอะไรถึงเป็นที่นิยมในหมู่ผู้เล่น วันนี้ผู้เขียนมีคำตอบมาให้ทุกท่านได้รู้กันอย่างแน่แท้
    ประวัติที่มาที่ไปของเครื่องเกมส์สล็อต หรือ Slot machine
    Slot machine นั้นมีความเป็นมาอย่างมากมาย และในแต่ละประเทศก็จะมีคำที่ใช้เรียกเครื่องสล็อตนั้นแตกต่างกัน อย่างเช่นในออสเตรเลียนั้นจะเรียกว่า “Pokies” นั่นก็เพราะเครื่องสล็อตเครื่องแรกนั้นจะใช้สัญลักษณ์ต่างๆในเกมส์โป้กเกอร์ ส่วนในประเทศอังกฤษนั้นจะเรียกเครื่องเล่นสล็อตว่า “Fruit machine” ก็เพราะว่าเครื่องสล็อตในสหราชอณาจักรมักจะใช้รูปผลไม้เป็นสัญลักษณ์นั่นเอง
    และเพราะว่าเครื่องเล่นสล็อตในแต่ละพื้นที่มีประวัติความเป็นมาแตกต่างกันและยังมีหลากหลายที่มา ทางผู้เขียนเลยอยากจะเล่าถึงเรื่องที่ว่า ใครเป็นผู้ผลิตเครื่องสล็อต และรวมถึงการพัฒนาของเครื่องสล็อตตามยุคสมัย
    Sitmann & Pitt company ผู้คิดค้นเครื่องสล็อต
    จุดเริ่มต้นของเครื่อง สล็อต แมชชีน ยังไม่แน่ชัดว่ามีต้นกำเนิดมาจากไหน แต่โดยหลักฐานเท่าที่มีให้ศึกษาได้บอกไว้ว่าเครื่องสล็อต เครื่องแรกนั้นเกิดขึ้นใน ทศวรรษที่ 19 โดย บริษัท Sitmann and Pitt ในเมือง บรูคลิน,นิวยอร์ก ประเทศสหรัฐอเมริกา เครื่องสล็อต เครื่องแรกนั้นถูกสร้างขึ้นในปี ค.ศ. 1887 โดยเดิมที สล็อต แมชชีน เครื่องแรกนั้นจะยังไม่ใช่เครื่องสล็อต แมชชีน ที่ทุกคนเคยเห็นหรือเคยสัมผัสมาในปัจจุบัน แต่จะมีลักษณะเหมือนกับเครื่องเล่นโป้กเกอร์เสียมากกว่า เพราะใช้กติกาเดียวกับโป้กเกอร์และสัญลักษณ์ยังเป็นสัญลักษณ์ไพ่โป้กเกอร์ โดยเครื่องดังกล่าวนั้น ชาวอเมริกัน จะพบเจอหรือเข้าไปเล่นได้ที่ บาร์,คลับ,ร้านอาหาร เป็นต้น
    โดยลักษณะของเครื่องสล็อต แมชชีน เครื่องแรกนั้นจะมีช่องว่างเรียงกัน 5 ช่อง ในแต่ละช่องจะมีไพ่วางแบบคว่ำหน้าไพ่ไว้อีก 50 ใบภายในเครื่อง ด้านข้างจะมีคันโยกเพื่อให้ผู้เล่นโยกไปด้านหลัง แล้วหยอดเหรียญ เมื่อผู้เล่นดึงคันโยกกลับ วงล้อในช่องทั้ง 5 จะหมุน เพื่อสุ่มไพ่ เมื่อวงล้อหยุดหมุน นั่นคือผลลัพธ์หรือแต้มที่เป็นตัววัดว่าผู้เล่นจะได้รับรางวัลหรือไม่ ในส่วนของรางวัลในสมัยนั้น จะไม่ได้จ่ายแบบอัตโนมัติ แต่จะได้รับเป็นของที่อยู่ในบาร์ หรือ คลับนั้นๆ เช่น เหล้า เบียร์ เป็นต้น

  26. เว็บหวยออนไลน์ อันดับหนึ่งของประเทศไทย


    อยู่ที่ไหนก็เล่นได้ เล่นง่ายแค่ปลายนิ้ว

    อีกทั้งยังสามารถยกเลิกตั๋วด้วยตัวเอง เล่นง่ายจ่ายจริงรับเงินเร็ว ไม่มีโกงแน่นอน

    สุดยอดเว็บแทงหวยออนไลน์ที่ดีที่สุดของประเทศไทยต้อง CLICK1234

    #### หวยออนไลน์ # ต้อง CLICK1234
    #### หวยออนไลน์ # ต้อง CLICK1234
    #### หวยออนไลน์ # ต้อง CLICK1234
    #### หวยออนไลน์ # ต้อง CLICK1234

  27. Mumbai escorts are the clear choice for an exceptional experience without the hassle! Our escorts give you the obvious advantage. Wet N Wild Mumbai escorts are available for a variety of social occasions when you need an absolute stunner who's GORGEOUS, smart, fit, intelligent and sexy! Wet N Wild Mumbai escorts offer a variety of fine female companions for any occasion when you need for as long as you need! Call Wet N Wild Mumbai escorts when you're ready to book an unforgettable experience with the finest Mumbai Escorts, Mumbai has to offer!
    Mumbai escorts
    Escort Services IN Mumbai
    Mumbai Escorts Service
    Mumbai call Girls
    Hire Russian Escort Mumbai
    Mumbai Escort

  28. เกมสล็อตออนไลน์แจกหนักแจกจริง พร้อมระบบฝากถอนอัตโนมัติบริการ 24 ชั่วโมง จาก pgslot
    ทั้งสล็ตอออนไลน์และยิงปลาออนไลน์ แค่สมัครสมาชิกก็รับ โบนัสสล็อตเครดิตฟรี ได้เลย

  29. Actually I read it yesterday I looked at most of your posts but I had some ideas about it . This article is probably where I got the most useful information for my research and today I wanted to read it again because it is so well written.
    Data Science Course in Bangalore

  30. เล่นเกมแล้วได้เงิน สล็อตออนไลน์ pgslot เล่นง่ายๆ เล่นสล็อต ฟรีเครดิต
    ได้เงินจริง เล่นเลยที่นี่ คลิกเลย เกมยิงปลา อย่าพลาดมาลุ้นกันเลย slotxo

  31. thank you for the information provided, we are waiting for the next info at cara menggugurkan kandungan