Loading ...
Sorry, an error occurred while loading the content.
 

What to Test in an RDB

Expand Messages
  • Scott Ambler
    My monthly DDJ newsletter entitled What to Consider When Testing Databases just went out. It is posted at
    Message 1 of 5 , Jun 25, 2007
      My monthly DDJ newsletter entitled "What to Consider
      When Testing Databases" just went out. It is posted
      at
      http://www.ddj.com/dept/architect/200000349?cid=Ambysoft

      The article argues that in a relational database you
      should consider tests for:
      - Database methods (stored procedures, triggers, ...)
      - Data-oriented business rules (constraints, sizes,
      ...)
      - Relationships (referential integrity, cardinality,
      ...)
      - Performance
      - Table structure

      Just like we take a test driven approach to designing
      application code there's no reason why we can't do the
      same for database design. I'll be facilitating a
      workshop on this very subject at Agile 2007.

      - Scott

      Scott W. Ambler
      Practice Leader Agile Development, IBM Methods Group
      http://www-306.ibm.com/software/rational/bios/ambler.html


      Be smarter than spam. See how smart SpamGuard is at giving junk email the boot with the All-new Yahoo! Mail at http://mrd.mail.yahoo.com/try_beta?.intl=ca
    • Andrew Gregovich
      Hi Scott You didn t mention one of the most common pitfalls in DB development - failing to ensure proper concurrency control. This kinds of tests are quite
      Message 2 of 5 , Jun 25, 2007
        Hi Scott

        You didn't mention one of the most common pitfalls in DB development - failing to ensure proper concurrency control. This kinds of tests are quite difficult to fully automate, but can be done easily with a certain degree of manual control.

        Andrew

        ----- Original Message ----
        From: Scott Ambler <scottwambler@...>
        To: Agile Articles <agilearticles@yahoogroups.com>; agiledatabases@yahoogroups.com; ambysoft@yahoogroups.com
        Sent: Tuesday, June 26, 2007 9:00:34 AM
        Subject: [agileDatabases] What to Test in an RDB













        My monthly DDJ newsletter entitled "What to Consider

        When Testing Databases" just went out. It is posted

        at

        http://www.ddj. com/dept/ architect/ 200000349? cid=Ambysoft



        The article argues that in a relational database you

        should consider tests for:

        - Database methods (stored procedures, triggers, ...)

        - Data-oriented business rules (constraints, sizes,

        ...)

        - Relationships (referential integrity, cardinality,

        ...)

        - Performance

        - Table structure



        Just like we take a test driven approach to designing

        application code there's no reason why we can't do the

        same for database design. I'll be facilitating a

        workshop on this very subject at Agile 2007.



        - Scott



        Scott W. Ambler

        Practice Leader Agile Development, IBM Methods Group

        http://www-306. ibm.com/software /rational/ bios/ambler. html



        Be smarter than spam. See how smart SpamGuard is at giving junk email the boot with the All-new Yahoo! Mail at http://mrd.mail. yahoo.com/ try_beta? .intl=ca














        <!--

        #ygrp-mlmsg {font-size:13px;font-family:arial, helvetica, clean, sans-serif;}
        #ygrp-mlmsg table {font-size:inherit;font:100%;}
        #ygrp-mlmsg select, input, textarea {font:99% arial, helvetica, clean, sans-serif;}
        #ygrp-mlmsg pre, code {font:115% monospace;}
        #ygrp-mlmsg * {line-height:1.22em;}
        #ygrp-text{
        font-family:Georgia;
        }
        #ygrp-text p{
        margin:0 0 1em 0;}
        #ygrp-tpmsgs{
        font-family:Arial;
        clear:both;}
        #ygrp-vitnav{
        padding-top:10px;font-family:Verdana;font-size:77%;margin:0;}
        #ygrp-vitnav a{
        padding:0 1px;}
        #ygrp-actbar{
        clear:both;margin:25px 0;white-space:nowrap;color:#666;text-align:right;}
        #ygrp-actbar .left{
        float:left;white-space:nowrap;}
        .bld{font-weight:bold;}
        #ygrp-grft{
        font-family:Verdana;font-size:77%;padding:15px 0;}
        #ygrp-ft{
        font-family:verdana;font-size:77%;border-top:1px solid #666;
        padding:5px 0;
        }
        #ygrp-mlmsg #logo{
        padding-bottom:10px;}

        #ygrp-vital{
        background-color:#e0ecee;margin-bottom:20px;padding:2px 0 8px 8px;}
        #ygrp-vital #vithd{
        font-size:77%;font-family:Verdana;font-weight:bold;color:#333;text-transform:uppercase;}
        #ygrp-vital ul{
        padding:0;margin:2px 0;}
        #ygrp-vital ul li{
        list-style-type:none;clear:both;border:1px solid #e0ecee;
        }
        #ygrp-vital ul li .ct{
        font-weight:bold;color:#ff7900;float:right;width:2em;text-align:right;padding-right:.5em;}
        #ygrp-vital ul li .cat{
        font-weight:bold;}
        #ygrp-vital a {
        text-decoration:none;}

        #ygrp-vital a:hover{
        text-decoration:underline;}

        #ygrp-sponsor #hd{
        color:#999;font-size:77%;}
        #ygrp-sponsor #ov{
        padding:6px 13px;background-color:#e0ecee;margin-bottom:20px;}
        #ygrp-sponsor #ov ul{
        padding:0 0 0 8px;margin:0;}
        #ygrp-sponsor #ov li{
        list-style-type:square;padding:6px 0;font-size:77%;}
        #ygrp-sponsor #ov li a{
        text-decoration:none;font-size:130%;}
        #ygrp-sponsor #nc {
        background-color:#eee;margin-bottom:20px;padding:0 8px;}
        #ygrp-sponsor .ad{
        padding:8px 0;}
        #ygrp-sponsor .ad #hd1{
        font-family:Arial;font-weight:bold;color:#628c2a;font-size:100%;line-height:122%;}
        #ygrp-sponsor .ad a{
        text-decoration:none;}
        #ygrp-sponsor .ad a:hover{
        text-decoration:underline;}
        #ygrp-sponsor .ad p{
        margin:0;}
        o {font-size:0;}
        .MsoNormal {
        margin:0 0 0 0;}
        #ygrp-text tt{
        font-size:120%;}
        blockquote{margin:0 0 0 4px;}
        .replbq {margin:4;}
        -->









        ____________________________________________________________________________________
        Moody friends. Drama queens. Your life? Nope! - their life, your story. Play Sims Stories at Yahoo! Games.
        http://sims.yahoo.com/

        [Non-text portions of this message have been removed]
      • Tim Tuxworth
        Great article Scott. I have wo complementary suggestions, that in a sense, should apply to all kinds of testing . Good testing should have a break it
        Message 3 of 5 , Jun 25, 2007
          Great article Scott.

          I have wo complementary suggestions, that in a sense, should apply to all
          kinds of testing

          . Good testing should have a 'break it' attitude.
          While it is useful to prove that the stored procedure,
          trigger, constraint, etc, etc "works", in my experience,
          great testers get an almost insane degree of pleasure
          from finding bugs, from proving that software has bugs,
          that the software is "broken".
          Good quality software can weather the
          storm of a well crafted suite of tests cleverly designed to
          prove that a given component (a database in this case)
          will fail.

          . database testing can/should also test for security.
          As with testing security at any layer of an architecture,
          it is important to think of things that people shouldn't be
          able to do to the database, and prove (essentially by
          creating a test where success is 'failure' to do what is
          forbidden) that it is 'secure'.

          Tim Tuxworth



          _____

          From: agileDatabases@yahoogroups.com [mailto:agileDatabases@yahoogroups.com]
          On Behalf Of Scott Ambler
          Sent: Monday, 25 June 2007 7:01 PM
          To: Agile Articles; agiledatabases@yahoogroups.com; ambysoft@yahoogroups.com
          Subject: [agileDatabases] What to Test in an RDB



          My monthly DDJ newsletter entitled "What to Consider
          When Testing Databases" just went out. It is posted
          at
          http://www.ddj. <http://www.ddj.com/dept/architect/200000349?cid=Ambysoft>
          com/dept/architect/200000349?cid=Ambysoft

          The article argues that in a relational database you
          should consider tests for:
          - Database methods (stored procedures, triggers, ...)
          - Data-oriented business rules (constraints, sizes,
          ...)
          - Relationships (referential integrity, cardinality,
          ...)
          - Performance
          - Table structure

          Just like we take a test driven approach to designing
          application code there's no reason why we can't do the
          same for database design. I'll be facilitating a
          workshop on this very subject at Agile 2007.

          - Scott

          Scott W. Ambler
          Practice Leader Agile Development, IBM Methods Group
          http://www-306. <http://www-306.ibm.com/software/rational/bios/ambler.html>
          ibm.com/software/rational/bios/ambler.html

          Be smarter than spam. See how smart SpamGuard is at giving junk email the
          boot with the All-new Yahoo! Mail at http://mrd.mail.
          <http://mrd.mail.yahoo.com/try_beta?.intl=ca> yahoo.com/try_beta?.intl=ca






          [Non-text portions of this message have been removed]
        • Tim Tuxworth
          Scott, It seems your article stayed above the how to level - fair enough, and perhaps for many members of this group, it might be teaching grandma to suck
          Message 4 of 5 , Jun 25, 2007
            Scott,

            It seems your article stayed above the 'how to' level - fair enough, and
            perhaps for many members of this group, it might be teaching grandma to suck
            eggs - but something that many developers don't necessarily appreciate is
            how the power of SQL can be applied to this kind of testing.

            It is very simple to craft single SQL queries that can test thousands or
            even millions of records for validity (or lack of validity) in one fell
            swoop. e.g. A single query can express "Count the number of Purchase Orders
            in the database with a negative value, or an invalid customer number or with
            zero order lines".

            A suite of powerful 'test' queries like this, that can typically execute in
            a blink of an eye, can reasonably be run repeatedly, for example after each
            suite of unit tests, smoke tests, functional tests, performance tests etc.
            In this way database testing can be embedded as part of the regular testing
            cycle of application developement in the same way as all other testing.

            Also, the secret with this kind of testing, as with all other forms of
            automation in software development, is to start small, build repeatable
            tests, re-run them every build, and incrementally grow the suite of tests as
            development progresses.

            Tim Tuxworth

            _____

            From: agileDatabases@yahoogroups.com [mailto:agileDatabases@yahoogroups.com]
            On Behalf Of Scott Ambler
            Sent: Monday, 25 June 2007 7:01 PM
            To: Agile Articles; agiledatabases@yahoogroups.com; ambysoft@yahoogroups.com
            Subject: [agileDatabases] What to Test in an RDB



            My monthly DDJ newsletter entitled "What to Consider
            When Testing Databases" just went out. It is posted
            at
            http://www.ddj. <http://www.ddj.com/dept/architect/200000349?cid=Ambysoft>
            com/dept/architect/200000349?cid=Ambysoft

            The article argues that in a relational database you
            should consider tests for:
            - Database methods (stored procedures, triggers, ...)
            - Data-oriented business rules (constraints, sizes,
            ...)
            - Relationships (referential integrity, cardinality,
            ...)
            - Performance
            - Table structure

            Just like we take a test driven approach to designing
            application code there's no reason why we can't do the
            same for database design. I'll be facilitating a
            workshop on this very subject at Agile 2007.

            - Scott

            Scott W. Ambler
            Practice Leader Agile Development, IBM Methods Group
            http://www-306. <http://www-306.ibm.com/software/rational/bios/ambler.html>
            ibm.com/software/rational/bios/ambler.html

            Be smarter than spam. See how smart SpamGuard is at giving junk email the
            boot with the All-new Yahoo! Mail at http://mrd.mail.
            <http://mrd.mail.yahoo.com/try_beta?.intl=ca> yahoo.com/try_beta?.intl=ca






            [Non-text portions of this message have been removed]
          • Scott Ambler
            Answering several at once. - Scott ... Good point. This is typically done at the black box level, external to a DB. ... I d written about stuff like that in
            Message 5 of 5 , Jun 26, 2007
              Answering several at once.

              - Scott
              --- Andrew Gregovich <andrew_gregovich@...>
              wrote:

              > Hi Scott
              >
              > You didn't mention one of the most common pitfalls
              > in DB development - failing to ensure proper
              > concurrency control. This kinds of tests are quite
              > difficult to fully automate, but can be done easily
              > with a certain degree of manual control.

              Good point. This is typically done at the black box
              level, external to a DB.


              --- Tim Tuxworth <tuxworth@...> wrote:

              >
              > . Good testing should have a 'break it' attitude.
              > While it is useful to prove that the stored
              > procedure,
              > trigger, constraint, etc, etc "works", in my
              > experience,
              > great testers get an almost insane degree of
              > pleasure
              > from finding bugs, from proving that software
              > has bugs,
              > that the software is "broken".
              > Good quality software can weather the
              > storm of a well crafted suite of tests cleverly
              > designed to
              > prove that a given component (a database in this
              > case)
              > will fail.


              I'd written about stuff like that in the past, so
              didn't cover it again.



              >
              > . database testing can/should also test for
              > security.
              > As with testing security at any layer of an
              > architecture,
              > it is important to think of things that people
              > shouldn't be
              > able to do to the database, and prove
              > (essentially by
              > creating a test where success is 'failure' to do
              > what is
              > forbidden) that it is 'secure'.

              Definitely. I've actually covered this in a more
              detailed article but didn't remember to include it in
              the newsletter.


              --- Tim Tuxworth <tuxworth@...> wrote:

              >
              > It is very simple to craft single SQL queries that
              > can test thousands or
              > even millions of records for validity (or lack of
              > validity) in one fell
              > swoop. e.g. A single query can express "Count the
              > number of Purchase Orders
              > in the database with a negative value, or an invalid
              > customer number or with
              > zero order lines".

              Definitely. This is Table-level data testing which I
              didn't get into. I'd likely use profiling tools for
              something like this, although SQL also gets the job
              done.


              >
              > A suite of powerful 'test' queries like this, that
              > can typically execute in
              > a blink of an eye, can reasonably be run repeatedly,
              > for example after each
              > suite of unit tests, smoke tests, functional tests,
              > performance tests etc.
              > In this way database testing can be embedded as part
              > of the regular testing
              > cycle of application developement in the same way as
              > all other testing.

              This is something I talk about in
              www.agiledata.org/essays/databaseTesting.html. The
              purpose of the latest newsletter, as you point out, is
              to focus on the what and not the how.

              >
              > Also, the secret with this kind of testing, as with
              > all other forms of
              > automation in software development, is to start
              > small, build repeatable
              > tests, re-run them every build, and incrementally
              > grow the suite of tests as
              > development progresses.

              Definitely covered at www.agiledata.org. ;-)

              - Scott

              Scott W. Ambler
              Practice Leader Agile Development, IBM Methods Group
              http://www-306.ibm.com/software/rational/bios/ambler.html


              Ask a question on any topic and get answers from real people. Go to Yahoo! Answers and share what you know at http://ca.answers.yahoo.com
            Your message has been successfully submitted and would be delivered to recipients shortly.