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

Re: [XP] Re: Unit tests & Database

Expand Messages
  • Lauren Berry
    Thanks all for your input, I agree that automated testing against the real database is necessary - and don t object to using JUnit to write these tests. Dave,
    Message 1 of 16 , Sep 6, 2007
    • 0 Attachment
      Thanks all for your input,

      I agree that automated testing against the real database is necessary - and
      don't object to using JUnit to write these tests.

      Dave, your thoughts are the same as mine. I think I will start by separating
      the true unit tests from the others and then work on either mocking the
      connection to the DB or setting up an HSQL db.

      Thanks,
      Lauren

      On 9/6/07, Dave Nicolette <dnicolet@...> wrote:
      >
      > I notice the OP asked about /unit/ testing. Others have mentioned the
      > need to test with the "real thing." That is not /unit/ testing.
      >
      > For /unit/ test purposes, my preference is to avoid external
      > dependencies to the extent practical. For a conventional webapp with a
      > database backend, it is certainly possible to set up the unit tests
      > with an in-memory database (such as HSQLDB for Java apps) or, almost
      > as good, a local test database that has no dependency on a network
      > connection (easily done for RoR, .NET, and probably other environments).
      >
      > Obviously there comes a time when we want to test against a "real"
      > database. This does /not/ mean it's okay to clutter up the unit test
      > suite with database connections and temporary data that may conflict
      > with other temporary data, and slow down the tests. Remember the unit
      > tests are executed very, very frequently throughout the day.
      >
      > Dave
      >
      > --- In extremeprogramming@yahoogroups.com<extremeprogramming%40yahoogroups.com>,
      > "Lauren Berry"
      > <lauren.berry@...> wrote:
      > >
      > > Hi All,
      > >
      > > I've just joined a new team and have been struggling to get the unit
      > tests
      > > running and passing. The main cause of these problems is (i think!)
      > > database connections. I am of the school of though that says unit tests
      > > shouldn't really be connecting to the database... however I think
      > that might
      > > be a bit of a drastic change all at once.
      > >
      > > The setup is basically:
      > > Each developer has their own schema which they connect to. There has
      > been
      > > some effort to separate out a UnitTestDataSource (unfortunately
      > there isn't
      > > just one of these, and some tests make their own connection to the test
      > > database anyway). The test data source(s) gets a connection by
      > looking up a
      > > JNDI resource from a properties file (again more than one) and then
      > connects
      > > to the database and runs the tests.
      > >
      > > So - obviously my first item to tackle is getting the connection to be
      > > created using the same code each time, through just the one
      > properties file,
      > > and at least have a default location for the properties file.
      > >
      > > So I have issues with test independence, eg. a previous failure
      > causes more
      > > failures, some tests run fine on their own but fail in the suite. And it
      > > seems painful that anyone who wants to run the unit tests can't just
      > get the
      > > source, compile and run the unit tests. I have to configure the
      > properties
      > > file, pass the name of the properties file as a parameter and then
      > remember
      > > to never update or commit this file.
      > >
      > > Some of the options I have thought of to improve the situation are:
      > >
      > > * DBUnit - if i understand correctly still uses a real DB?
      > > * Everyone has their own local DB with a standard test connection setup
      > > rather than a networked one
      > > * Use an in memory DB for unit tests - does this cause our SQL to
      > differ to
      > > the oracle SQL - have different rseults?
      > > * Get serious about separating data access from business logic and
      > restrict
      > > the tests which access the DB to 'acceptance tests' which need to
      > use the DB
      > > - I fear there aren't many true unit tests in there anyway.
      > >
      > > Has anyone got ideas about the positives and negatives of any of
      > these? Or
      > > any better ideas?
      > > If you have a similar setup where each developer has their own
      > schema - how
      > > do you make this easy for new people, and protect people from messing up
      > > their settings?
      > >
      > > Thanks,
      > > Lauren
      > >
      > >
      > > [Non-text portions of this message have been removed]
      > >
      >
      >
      >


      [Non-text portions of this message have been removed]
    • Matt
      ... wrote: * All Stored procedures (in theory because in practice only the more complex ones) are tested by actually executing
      Message 2 of 16 , Sep 6, 2007
      • 0 Attachment
        --- In extremeprogramming@yahoogroups.com, "NOTENBAERT, Peter"
        <peter.notenbaert@...> wrote:
        <snip>
        * All Stored procedures (in theory because in practice only the
        more complex ones) are tested by actually executing them against the
        database. The test data is managed using Data Generation Plans
        (http://msdn2.microsoft.com/en-us/library/aa833211(VS.80).aspx). This
        works well, with a few minor issues, for a simple case as testing one
        stored procedure at a time.
        </snip>

        Peter,

        Where do these tests live? Do they live alongside the DAL tests that
        are most closely associated with the procedure or do they live in
        their own test suite? We have been bouncing around ideas for test
        driving stored procedures and thats one of the questions that came
        up.

        Another one is, in the absence of VS Team Edition and Data Generation
        Plans, should we use the DAL to manage setting up and tearing down
        the data for these tests or should we run scripts against the DB.

        Along those lines, how do you verify the results of the SP? Do you
        run scripts directly against the DB or do you go through the DAL. In
        other words, do you test the integrated results of the SP / DAL or do
        you test the raw data results?

        Thanks

        Matt
      • pnotenb
        ... the ... This ... one ... that ... With us, the stored procedures tests live in their own test suite. I m not sure this is very important though. We
        Message 3 of 16 , Sep 7, 2007
        • 0 Attachment
          --- In extremeprogramming@yahoogroups.com, "Matt"
          <analyticalchaos@...> wrote:
          >
          > --- In extremeprogramming@yahoogroups.com, "NOTENBAERT, Peter"
          > <peter.notenbaert@> wrote:
          > <snip>
          > * All Stored procedures (in theory because in practice only the
          > more complex ones) are tested by actually executing them against
          the
          > database. The test data is managed using Data Generation Plans
          > (http://msdn2.microsoft.com/en-us/library/aa833211(VS.80).aspx).
          This
          > works well, with a few minor issues, for a simple case as testing
          one
          > stored procedure at a time.
          > </snip>
          >
          > Peter,
          >
          > Where do these tests live? Do they live alongside the DAL tests
          that
          > are most closely associated with the procedure or do they live in
          > their own test suite? We have been bouncing around ideas for test
          > driving stored procedures and thats one of the questions that came
          > up.
          >

          With us, the stored procedures tests live in their own test suite.
          I'm not sure this is very important though. We organized our test
          suites along the different layers; one for each.

          > Another one is, in the absence of VS Team Edition and Data
          Generation
          > Plans, should we use the DAL to manage setting up and tearing down
          > the data for these tests or should we run scripts against the DB.
          >

          For these tests, I would not opt for using the DAL to setup test
          data. Especially because it could get in the way of the TDD we do for
          writing sp's. We write the tests before the stored procedure. Only
          when the stored procedure is finished, along with its tests, the data
          access class is tdd'ed. Tearing down test data for these types of
          tests is most easily done by just rolling back a transaction. So yes,
          I think I would choose for using scripts.

          Or I maybe would check-out NDbUnit. Anyone has experience with it?

          > Along those lines, how do you verify the results of the SP? Do you
          > run scripts directly against the DB or do you go through the DAL.
          In
          > other words, do you test the integrated results of the SP / DAL or
          do
          > you test the raw data results?
          >

          We typically execute one or more SQL statements to verify directly
          against the DB. I would not like the idea that the SP layer tests
          would depend on the DAL layer which in its turn is depending on the
          SP layer.

          > Thanks
          >

          I hope this helps.

          May i ask a question too? How would you test your services (web or
          other) if you have them? Do you create a specific service that uses
          the dal to setup test data?
          Do you create compensating services that tear down test data?

          Or is it all to much ovehead, as some of my colleges and managers
          think, to test services like that?

          > Matt
          >
        • heusserm
          ... I m very curious as to which authority your are citing here when you define Unit as just the object in complete isolation. I was talking to Steve Freeman
          Message 4 of 16 , Sep 7, 2007
          • 0 Attachment
            Dave N. Wrote:

            > I notice the OP asked about /unit/ testing. Others have
            > mentioned the need to test with the "real thing." That is
            > not /unit/ testing.
            >
            > For /unit/ test purposes, my preference is to avoid external
            > dependencies to the extent practical.

            I'm very curious as to which authority your are citing here when you
            define "Unit" as just the object in complete isolation.

            I was talking to Steve Freeman about this two weeks ago (Author of the
            original Mock Paper), and he made it clear that he does *not*
            recommend mocking out external, hard boundaries like the database.
            (It's on video here: http://www.youtube.com/watch?v=PHtEkkKXSiY)

            I had an email discussion with Michael Feathers about this about a
            year ago. He's the guy that wrote that if it touches a db, if it
            touches a file system, if it touches the web, it's not a unit test.
            Michael agreed that if the application itself had it's functionality
            in the db, or on the web, or it's role was to interact with files,
            then of course, you might need to hit those systems.

            I brought this up with Michael Schwern at the Open Source Conference
            in 2003, and he said that he did not see value in distinguishing tests
            in that way - that he focused on developer-facing tests vs.
            customer-facing tests.

            Now, as to why I bring this up ...

            I've seen quite a people cull out (or refuse to write) completely
            valuable, useful tests from a test suite because "that's not a unit
            test", and then only get feedback when/if the integration or system
            tests run, which was slim to never.

            If our terminology is getting in the way of doing good work(*), I'm
            afraid it is becoming dogma, and, to be honest, that's what the unit
            test discussion is starting to feel like.

            Regards,

            --heusser
            (*) - An article I wrote on that subject:
            http://www.xndev.com/articles/BOX_OF_A_DIFFERENT_COLOR.pdf
          • Cory Foy
            ... Personally, when TDDing stored procs, I m not sure how much the data generation plans would get you anyway. I can see that for acceptance and load tests
            Message 5 of 16 , Sep 7, 2007
            • 0 Attachment
              pnotenb wrote:
              >> Another one is, in the absence of VS Team Edition and Data
              > Generation
              >> Plans, should we use the DAL to manage setting up and tearing down
              >> the data for these tests or should we run scripts against the DB.
              >>
              > For these tests, I would not opt for using the DAL to setup test
              > data. Especially because it could get in the way of the TDD we do for
              > writing sp's. We write the tests before the stored procedure. Only
              > when the stored procedure is finished, along with its tests, the data
              > access class is tdd'ed. Tearing down test data for these types of
              > tests is most easily done by just rolling back a transaction. So yes,
              > I think I would choose for using scripts.

              Personally, when TDDing stored procs, I'm not sure how much the data
              generation plans would get you anyway. I can see that for acceptance and
              load tests (and see it quite often)

              > Or I maybe would check-out NDbUnit. Anyone has experience with it?

              If you are using the Data Generation Plans, then I assume you are using
              DBPro, no? Are there features in it missing you'd like to see?

              --
              Cory Foy
              http://www.cornetdesign.com
            • John Roth
              ... From: heusserm To: Sent: Friday, September 07, 2007 6:21 AM Subject: [XP] Re: Unit tests &
              Message 6 of 16 , Sep 7, 2007
              • 0 Attachment
                ----- Original Message -----
                From: "heusserm" <matt.heusser@...>
                To: <extremeprogramming@yahoogroups.com>
                Sent: Friday, September 07, 2007 6:21 AM
                Subject: [XP] Re: Unit tests & Database


                Dave N. Wrote:

                > I notice the OP asked about /unit/ testing. Others have
                > mentioned the need to test with the "real thing." That is
                > not /unit/ testing.
                >
                > For /unit/ test purposes, my preference is to avoid external
                > dependencies to the extent practical.

                I'm very curious as to which authority your are citing here when you
                define "Unit" as just the object in complete isolation.

                [response]

                This is the common definition in classical development methodologies.
                Trying to haul that definition into Agile, including XP, methodologies
                causes nothing but confusion.

                When I classify tests, I look at three factors:

                1. Is it developer or customer facing.

                2. was it created as part of the TDD design/development/test
                process, or was it added to the test suite for some other reason.

                3. How fast does it run?

                None of these says anything about how much code is being
                tested, nor does it say anything about what is being tested.

                The first two speak to when and why it was created, the third
                speaks to when I can run it conveniently.

                John Roth
              • Charlie Poole
                Hi John, ... I like this - particularly the second point, which many people miss. I would however add the obvious: each test does, in fact, test a certain
                Message 7 of 16 , Sep 7, 2007
                • 0 Attachment
                  Hi John,

                  > When I classify tests, I look at three factors:
                  >
                  > 1. Is it developer or customer facing.
                  >
                  > 2. was it created as part of the TDD design/development/test
                  > process, or was it added to the test suite for some other reason.
                  >
                  > 3. How fast does it run?
                  >
                  > None of these says anything about how much code is being
                  > tested, nor does it say anything about what is being tested.
                  >
                  > The first two speak to when and why it was created, the third
                  > speaks to when I can run it conveniently.

                  I like this - particularly the second point, which many people miss.

                  I would however add the obvious: each test does, in fact, test
                  a certain scope within the code and it's fairly important that
                  this be under the control of the developer rather than due
                  to random effects.

                  If the developer is intending to test a certain scope, then
                  the discipline of staying within that scope can only improve
                  the structure of the application and the value of the tests.

                  Charlie
                • Dave Nicolette
                  ... How do you define authority? I do this for a living. I m as much an authority as Steve Freeman or Michael Feathers. So are you. ... So, these people
                  Message 8 of 16 , Sep 7, 2007
                  • 0 Attachment
                    --- In extremeprogramming@yahoogroups.com, "heusserm"
                    <matt.heusser@...> wrote:
                    >
                    > Dave N. Wrote:
                    >
                    > > I notice the OP asked about /unit/ testing. Others have
                    > > mentioned the need to test with the "real thing." That is
                    > > not /unit/ testing.
                    > >
                    > > For /unit/ test purposes, my preference is to avoid external
                    > > dependencies to the extent practical.
                    >
                    > I'm very curious as to which authority your are citing here when you
                    > define "Unit" as just the object in complete isolation.

                    How do you define "authority?" I do this for a living. I'm as much an
                    authority as Steve Freeman or Michael Feathers. So are you.


                    >
                    > I was talking to Steve Freeman about this two weeks ago (Author of the
                    > original Mock Paper), and he made it clear that he does *not*
                    > recommend mocking out external, hard boundaries like the database.
                    > (It's on video here: http://www.youtube.com/watch?v=PHtEkkKXSiY)
                    >
                    > I had an email discussion with Michael Feathers about this about a
                    > year ago. He's the guy that wrote that if it touches a db, if it
                    > touches a file system, if it touches the web, it's not a unit test.
                    > Michael agreed that if the application itself had it's functionality
                    > in the db, or on the web, or it's role was to interact with files,
                    > then of course, you might need to hit those systems.
                    >
                    > I brought this up with Michael Schwern at the Open Source Conference
                    > in 2003, and he said that he did not see value in distinguishing tests
                    > in that way - that he focused on developer-facing tests vs.
                    > customer-facing tests.
                    >
                    > Now, as to why I bring this up ...
                    >
                    > I've seen quite a people cull out (or refuse to write) completely
                    > valuable, useful tests from a test suite because "that's not a unit
                    > test", and then only get feedback when/if the integration or system
                    > tests run, which was slim to never.

                    So, these people limit themselves only to unit tests? Sounds foolish.
                    Similarly, I've encountered people who limit themselves only to
                    full-scale system tests. They say the same bugs will be exposed as in
                    unit tests, so the unit tests are superfluous. IMO that's foolish, too.



                    > If our terminology is getting in the way of doing good work(*), I'm
                    > afraid it is becoming dogma, and, to be honest, that's what the unit
                    > test discussion is starting to feel like.

                    All the various points of view you've touched on in this message seem
                    perfectly valid to me (except the pov that excludes necessary tests in
                    a single-minded quest for unit tests). I read your article some time
                    ago, and re-read it just now. I think you make valid points, as well.

                    I'm not getting the sense that the unit test discussion feels dogmatic.

                    Dave
                  • Dave Nicolette
                    John, That is a good way to slice it up, too. Not to focus too much on the word, unit, I think that depending on the purpose of a test, there is an
                    Message 9 of 16 , Sep 7, 2007
                    • 0 Attachment
                      John,

                      That is a good way to slice it up, too. Not to focus too much on the
                      word, "unit," I think that depending on the purpose of a test, there
                      is an appropriate level of isolation.

                      Dave

                      --- In extremeprogramming@yahoogroups.com, John Roth <JohnRoth1@...>
                      wrote:
                      >
                      >
                      > ----- Original Message -----
                      > From: "heusserm" <matt.heusser@...>
                      > To: <extremeprogramming@yahoogroups.com>
                      > Sent: Friday, September 07, 2007 6:21 AM
                      > Subject: [XP] Re: Unit tests & Database
                      >
                      >
                      > Dave N. Wrote:
                      >
                      > > I notice the OP asked about /unit/ testing. Others have
                      > > mentioned the need to test with the "real thing." That is
                      > > not /unit/ testing.
                      > >
                      > > For /unit/ test purposes, my preference is to avoid external
                      > > dependencies to the extent practical.
                      >
                      > I'm very curious as to which authority your are citing here when you
                      > define "Unit" as just the object in complete isolation.
                      >
                      > [response]
                      >
                      > This is the common definition in classical development methodologies.
                      > Trying to haul that definition into Agile, including XP, methodologies
                      > causes nothing but confusion.
                      >
                      > When I classify tests, I look at three factors:
                      >
                      > 1. Is it developer or customer facing.
                      >
                      > 2. was it created as part of the TDD design/development/test
                      > process, or was it added to the test suite for some other reason.
                      >
                      > 3. How fast does it run?
                      >
                      > None of these says anything about how much code is being
                      > tested, nor does it say anything about what is being tested.
                      >
                      > The first two speak to when and why it was created, the third
                      > speaks to when I can run it conveniently.
                      >
                      > John Roth
                      >
                    • NOTENBAERT, Peter
                      Cory, The data generation plans, lets call them DGP s, give us a well defined simple set of random generated data. It is the same every time it is generated
                      Message 10 of 16 , Sep 10, 2007
                      • 0 Attachment
                        Cory,



                        The data generation plans, lets call them DGP's, give us a well defined simple set of random generated data. It is the same every time it is generated with the same seed. It contains completely meaningless content. The product name for example would be something like 'lqksdhfùmqsdf87mqh4sd4fm'.



                        So what we get is a predictable set of data. We've been able to test most of our stored procedures against such a set. And we've been able to simulate most errors we made in our sp's with unit test running against such data.



                        Maybe we didn't see clearly, but we failed to see how DGP's can give us what we need for acceptance tests. What we seem to need for acceptance tests is test data with well defined characteristics. Lest say 'product' is one of the entities. For one test we need a simple product with charatectistics X, for the next test a product with well defined complex charatectistics Y. Isn't DGP to random for that?



                        Maybe we're missing something, when looking at automating acceptance tests and making them too complex and too expensive. We have not been able yet to figure out how to make it easier.



                        If you would like to hear about missing feature's for us of DBPro, are you referring to DGP's or to whole product?



                        Peter Notenbaert



                        ________________________________

                        From: extremeprogramming@yahoogroups.com [mailto:extremeprogramming@yahoogroups.com] On Behalf Of Cory Foy
                        Sent: vrijdag 7 september 2007 19:15
                        To: extremeprogramming@yahoogroups.com
                        Subject: MimeSweeper Spam: Re: MimeSweeper Spam: [XP] Unit tests & Databases



                        pnotenb wrote:
                        >> Another one is, in the absence of VS Team Edition and Data
                        > Generation
                        >> Plans, should we use the DAL to manage setting up and tearing down
                        >> the data for these tests or should we run scripts against the DB.
                        >>
                        > For these tests, I would not opt for using the DAL to setup test
                        > data. Especially because it could get in the way of the TDD we do for
                        > writing sp's. We write the tests before the stored procedure. Only
                        > when the stored procedure is finished, along with its tests, the data
                        > access class is tdd'ed. Tearing down test data for these types of
                        > tests is most easily done by just rolling back a transaction. So yes,
                        > I think I would choose for using scripts.

                        Personally, when TDDing stored procs, I'm not sure how much the data
                        generation plans would get you anyway. I can see that for acceptance and
                        load tests (and see it quite often)

                        > Or I maybe would check-out NDbUnit. Anyone has experience with it?

                        If you are using the Data Generation Plans, then I assume you are using
                        DBPro, no? Are there features in it missing you'd like to see?

                        --
                        Cory Foy
                        http://www.cornetdesign.com <http://www.cornetdesign.com>


                        **********************************************************************
                        All e-mail messages addressed to, received or sent by the Cobelfret Group or Cobelfret Group employees are deemed to be professional in nature. Accordingly, the sender or recipient of these messages agrees that they may be read by other Cobelfret Group employees than the official recipient or sender in order to ensure the continuity of work-related activities and allow supervision thereof.

                        This mail has been checked for viruses by Mailsweeper and Sophos
                        *********************************************************************


                        [Non-text portions of this message have been removed]
                      • Cory Foy
                        ... I guess what I m curious to understand is how having non-deterministic data produces reliable results in your unit tests. Maybe you are doing something
                        Message 11 of 16 , Sep 10, 2007
                        • 0 Attachment
                          NOTENBAERT, Peter wrote:
                          > The data generation plans, lets call them DGP's, give us a well defined
                          > simple set of random generated data. It is the same every time it is
                          > generated with the same seed. It contains completely meaningless
                          > content. The product name for example would be something like
                          > 'lqksdhfùmqsdf87mqh4sd4fm'.
                          >
                          > So what we get is a predictable set of data. We've been able to test
                          > most of our stored procedures against such a set. And we've been able to
                          > simulate most errors we made in our sp's with unit test running against
                          > such data.

                          I guess what I'm curious to understand is how having non-deterministic
                          data produces reliable results in your unit tests. Maybe you are doing
                          something like:

                          string expected = "SELECT TOP 1 product_name FROM products";
                          string actual = "exec sp_get_first_product"
                          Assert.AreEqual(expected, actual)

                          > Maybe we didn't see clearly, but we failed to see how DGP's can give us
                          > what we need for acceptance tests. What we seem to need for acceptance
                          > tests is test data with well defined characteristics. Lest say 'product'
                          > is one of the entities. For one test we need a simple product with
                          > charatectistics X, for the next test a product with well defined complex
                          > charatectistics Y. Isn't DGP to random for that?

                          Actually, you can easily specify that the data either follows specific
                          patterns (using regex) or write your own adapters. I'm just surprised
                          you didn't run into similar problems unit testing.

                          > Maybe we're missing something, when looking at automating acceptance
                          > tests and making them too complex and too expensive. We have not been
                          > able yet to figure out how to make it easier.

                          Many of us here have worked on some very hairy acceptance test criteria.
                          Maybe if you can come up with a scenario you are having problems with we
                          can help point to ways to make it work (even better if you can keep it
                          somewhat tool agnostic)

                          > If you would like to hear about missing feature's for us of DBPro, are
                          > you referring to DGP's or to whole product?

                          It's slightly offtopic for this list. I (and several of us on here) work
                          closely with the product team. I'm part of the MS Rangers for VSTS, so I
                          have some unique insights, and I like abusing my role to take the stuff
                          I used to have problems with, or that is really easy in other languages,
                          and passing that along. I'd be happy to talk to anyone off-list (or
                          on-list if it is on-topic) about problems they are having so I can pass
                          that along.


                          --
                          Cory Foy
                          http://www.cornetdesign.com
                        Your message has been successfully submitted and would be delivered to recipients shortly.