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

Test-driving SQL Server

Expand Messages
  • Kay A. Pentecost
    Hi, Everybody!! Well, here I am, a mere programmer, tasked with writing a slew of stored procedures. I seem to be remembering some of my SQL Server 7
    Message 1 of 11 , Aug 25, 2004
    • 0 Attachment
      Hi, Everybody!!

      Well, here I am, a mere programmer, <grin> tasked with writing a slew
      of stored procedures. I seem to be remembering some of my SQL Server
      7 experience, but after doing TDD for what, 2+ years now, I'm
      frustrated with not being able to automate my tests in stored procs.

      Anybody have any info or pointers to info about test-driving T-SQL?
      I've got the T-SQLUnit, somewhere but haven't had a chance to play
      with it... but I didn't find much in the way of illustrative test
      examples... which I sure could use.

      We're doing VB6, VB.Net and C# but the data access and manipulation
      is all in stored procedures. I've already been bitten a few times by
      changes in the SQL crashing my perfect GUI code <giggle> and I'm
      really sick of testing every step with the GuruChecksOutput anti-
      pattern.

      Right now I have a slew of update queries that I'm testing by making
      sure the number of records joined are the same before and after... it
      feels like a kludge, though.

      Any suggestions?

      Kay
    • Tony Nassar
      Kay, For what it s worth, you might check out http://utplsql.oracledeveloper.nl/. Obviously this applies to PL/SQL, not T-SQL, but you and some others with
      Message 2 of 11 , Aug 25, 2004
      • 0 Attachment
        Kay,
         
        For what it's worth, you might check out http://utplsql.oracledeveloper.nl/. Obviously this applies to PL/SQL, not T-SQL, but you and some others with time on their hands might model a unit-testing framework on Steven Feuerstein's.
         
        Tony
      • Chris Morris
        ... Can you just write C# unit tests that round trip though the stored procs with ADO.NET? T-SQL is very limited in comparison to C# and I d think any time you
        Message 3 of 11 , Aug 25, 2004
        • 0 Attachment
          Kay A. Pentecost wrote:

          >We're doing VB6, VB.Net and C# but the data access and manipulation
          >is all in stored procedures. I've already been bitten a few times by
          >changes in the SQL crashing my perfect GUI code <giggle> and I'm
          >really sick of testing every step with the GuruChecksOutput anti-
          >pattern.
          >
          >Right now I have a slew of update queries that I'm testing by making
          >sure the number of records joined are the same before and after... it
          >feels like a kludge, though.
          >
          >Any suggestions?
          >
          >
          Can you just write C# unit tests that round trip though the stored procs
          with ADO.NET? T-SQL is very limited in comparison to C# and I'd think
          any time you spent trying to wrangle together a T-SQL Unit wouldn't be
          worth whatever downsides to unit testing from the outside with NUnit or
          somesuch.

          --
          Chris
          http://clabs.org/blogki
        • Richard Quinn
          ... Can you just write C# unit tests that round trip though the stored procs with ADO.NET? T-SQL is very limited in comparison to C# and I d think any time you
          Message 4 of 11 , Aug 25, 2004
          • 0 Attachment
            >Chris Morris
            Can you just write C# unit tests that round trip though the stored
            procs
            with ADO.NET? T-SQL is very limited in comparison to C# and I'd
            think
            any time you spent trying to wrangle together a T-SQL Unit wouldn't
            be
            worth whatever downsides to unit testing from the outside with NUnit
            or
            somesuch.

            Hi,

            I am in exactly the same situation as the OP at the moment.
            I have a slew of sprocs since I am (still) using the MS DNA architectural
            pattern. I have a slew of unit tests (in csUnit) which I use to test my data
            objects (c# classes using the .NET data reader).

            It works fine, I use RapTier to generate all of the data objects and the
            test stubs. But I really need a unit testing framework for the database. I
            want to do things like the following:

            a) Make schema changes
            b) Load test data
            c) Delete the test data and return to a known previous schema
            d) Test SQLMail
            e) Test the inbuilt propagation of key changes to related tables
            (UPDATE, DELETE, RESTRICT)
            f) Test restoring the transaction log
            g) Test my maintenance SPs (verifying that statistics have been
            updated and sproc caches cleared)

            These are things that I don't know how to test or don't want to test from
            the .NET application. If anyone knows of a testing framework for MS SQL
            (2000) which can do some of those I would also really like to know!

            - Richard
          • Chris Morris
            ... FWIW, most unit testing frameworks offer little in the way of actually contributing to the test content -- they re more like test secretaries: they ll
            Message 5 of 11 , Aug 26, 2004
            • 0 Attachment
              Richard Quinn wrote:

              >But I really need a unit testing framework for the database. I
              >want to do things like the following:
              >
              > a) Make schema changes
              > b) Load test data
              > c) Delete the test data and return to a known previous schema
              > d) Test SQLMail
              > e) Test the inbuilt propagation of key changes to related tables
              >(UPDATE, DELETE, RESTRICT)
              > f) Test restoring the transaction log
              > g) Test my maintenance SPs (verifying that statistics have been
              >updated and sproc caches cleared)
              >
              >
              FWIW, most unit testing frameworks offer little in the way of actually
              contributing to the test content -- they're more like test secretaries:
              they'll organize, execute and record results, but the test writing is
              still up to you. Obviously, it'd be nice to have a tool that did all
              those things for you, but I don't think that'd be called a unit test
              framework.

              --
              Chris
              http://clabs.org
            • Ismet Erensoy Kahraman
              It was the answer that I would write. ... From: Chris Morris To: agileDatabases@yahoogroups.com Sent: Thursday, August 26, 2004 5:07 PM Subject: Re:
              Message 6 of 11 , Aug 26, 2004
              • 0 Attachment
                It was the answer that I would write.
                 
                ----- Original Message -----
                Sent: Thursday, August 26, 2004 5:07 PM
                Subject: Re: [agileDatabases] Test-driving SQL Server

                Richard Quinn wrote:

                >But I really need a unit testing framework for the database. I
                >want to do things like the following:
                >
                >      a) Make schema changes
                >      b) Load test data
                >      c) Delete the test data and return to a known previous schema
                >      d) Test SQLMail
                >      e) Test the inbuilt propagation of key changes to related tables
                >(UPDATE, DELETE, RESTRICT)
                >      f) Test restoring the transaction log
                >      g) Test my maintenance SPs (verifying that statistics have been
                >updated and sproc caches cleared)

                >
                FWIW, most unit testing frameworks offer little in the way of actually
                contributing to the test content -- they're more like test secretaries:
                they'll organize, execute and record results, but the test writing is
                still up to you. Obviously, it'd be nice to have a tool that did all
                those things for you, but I don't think that'd be called a unit test
                framework.

                --
                Chris
                http://clabs.org


              • Kay A. Pentecost
                Hi, Tony, How re you doing? ... Thanks! ... I m *working* now! yea! So I have no time on my hands at all... but thanks for the pointer! Kay
                Message 7 of 11 , Aug 26, 2004
                • 0 Attachment
                  Hi, Tony,

                  How're you doing?

                  --- In agileDatabases@yahoogroups.com, Tony Nassar <tony.nassar@o...>
                  wrote:
                  > Kay,
                  >
                  > For what it's worth, you might check out
                  >http://utplsql.oracledeveloper.nl/.

                  Thanks!

                  >Obviously this applies to PL/SQL, not T-SQL, but you and some others
                  >with time on their hands might model a unit-testing framework on
                  >Steven Feuerstein's.

                  I'm *working* <grin> now! yea! So I have no time on my hands at
                  all... but thanks for the pointer!

                  Kay
                • Kay A. Pentecost
                  Hi, Chris, ... manipulation ... by ... making ... it ... procs ... think ... be ... NUnit or ... Yeah, I can do that. Even better, I ll write NUnit tests,
                  Message 8 of 11 , Aug 26, 2004
                  • 0 Attachment
                    Hi, Chris,

                    --- In agileDatabases@yahoogroups.com, Chris Morris <chrismo@c...>
                    wrote:
                    > Kay A. Pentecost wrote:
                    >
                    > >We're doing VB6, VB.Net and C# but the data access and
                    manipulation
                    > >is all in stored procedures. I've already been bitten a few times
                    by
                    > >changes in the SQL crashing my perfect GUI code <giggle> and I'm
                    > >really sick of testing every step with the GuruChecksOutput anti-
                    > >pattern.
                    > >
                    > >Right now I have a slew of update queries that I'm testing by
                    making
                    > >sure the number of records joined are the same before and after...
                    it
                    > >feels like a kludge, though.
                    > >
                    > >Any suggestions?
                    > >
                    > >
                    > Can you just write C# unit tests that round trip though the stored
                    procs
                    > with ADO.NET? T-SQL is very limited in comparison to C# and I'd
                    think
                    > any time you spent trying to wrangle together a T-SQL Unit wouldn't
                    be
                    > worth whatever downsides to unit testing from the outside with
                    NUnit or
                    > somesuch.

                    Yeah, I can do that. Even better, I'll write NUnit tests,
                    eventually, that test the procs... I'm pretty familiar with NUnit and
                    I've been doing TDD with vbUnit for a few years now...

                    Right now, however, the Stored Procs are pretty coupled to the
                    codebase, (don't ask me to explain how that is true) and they get
                    changed and then the codebase fails... and I'm wasting a lot of time
                    finding out exactly where the error is....

                    Thanks for the feedback!

                    Kay
                  • Kay A. Pentecost
                    Hi, Richard, ... stored ... I d ... wouldn t ... with NUnit ... architectural ... test my data ... and the ... Oh, I didn t know about RapTier. I ll check it
                    Message 9 of 11 , Aug 26, 2004
                    • 0 Attachment
                      Hi, Richard,

                      --- In agileDatabases@yahoogroups.com, "Richard Quinn" <rquinn@w...>
                      wrote:
                      >
                      > >Chris Morris
                      > Can you just write C# unit tests that round trip though the
                      stored
                      > procs
                      > with ADO.NET? T-SQL is very limited in comparison to C# and
                      I'd
                      > think
                      > any time you spent trying to wrangle together a T-SQL Unit
                      wouldn't
                      > be
                      > worth whatever downsides to unit testing from the outside
                      with NUnit
                      > or
                      > somesuch.
                      >
                      > Hi,
                      >
                      > I am in exactly the same situation as the OP at the moment.
                      > I have a slew of sprocs since I am (still) using the MS DNA
                      architectural
                      > pattern. I have a slew of unit tests (in csUnit) which I use to
                      test my data
                      > objects (c# classes using the .NET data reader).
                      >
                      > It works fine, I use RapTier to generate all of the data objects
                      and the
                      > test stubs.

                      Oh, I didn't know about RapTier. I'll check it out.



                      >But I really need a unit testing framework for the database.

                      There's TSQLUnit... which I haven't started looking at yet (much too
                      much to do at this new job) at
                      http://tsqlunit.sourceforge.net/tsqlunit_cookbook.htm

                      That's the url for the "cookbook."

                      I was hoping for some more documentation or examples --



                      I
                      > want to do things like the following:
                      >
                      > a) Make schema changes
                      > b) Load test data
                      > c) Delete the test data and return to a known previous schema
                      > d) Test SQLMail
                      > e) Test the inbuilt propagation of key changes to related
                      tables
                      > (UPDATE, DELETE, RESTRICT)
                      > f) Test restoring the transaction log
                      > g) Test my maintenance SPs (verifying that statistics have
                      been
                      > updated and sproc caches cleared)

                      Yes, that's the kind of thing I want to do, too.


                      >
                      > These are things that I don't know how to test or don't want to
                      test from
                      > the .NET application. If anyone knows of a testing framework for MS
                      SQL
                      > (2000) which can do some of those I would also really like to know!
                      >

                      Check out TSQLUnit and let me know what you think... maybe we can
                      figure it out together... write me offlist?

                      Kay
                    • Chris Morris
                      ... But can t you use NUnit right away to simply call whatever proc you need directly and check the output you get, bypassing any of the current .NET codebase?
                      Message 10 of 11 , Aug 26, 2004
                      • 0 Attachment
                        Kay A. Pentecost wrote:

                        >Yeah, I can do that. Even better, I'll write NUnit tests,
                        >eventually, that test the procs... I'm pretty familiar with NUnit and
                        >I've been doing TDD with vbUnit for a few years now...
                        >
                        >Right now, however, the Stored Procs are pretty coupled to the
                        >codebase, (don't ask me to explain how that is true) and they get
                        >changed and then the codebase fails... and I'm wasting a lot of time
                        >finding out exactly where the error is....
                        >
                        >
                        But can't you use NUnit right away to simply call whatever proc you need
                        directly and check the output you get, bypassing any of the current .NET
                        codebase? You could even dump the resulting ADO.NET recordset to XML (I
                        think) and do file comparisons on a known good XML dump of the same
                        recordset (or somesuch)?

                        --
                        Chris
                        http://clabs.org
                      • Kay A. Pentecost
                        ... wrote: ... need ... current .NET ... XML (I ... Yes. I can. And I will. Thanks, Kay
                        Message 11 of 11 , Aug 30, 2004
                        • 0 Attachment
                          --- In agileDatabases@yahoogroups.com, Chris Morris <chrismo@c...>
                          wrote:
                          <snip>> >
                          > But can't you use NUnit right away to simply call whatever proc you
                          need
                          > directly and check the output you get, bypassing any of the
                          current .NET
                          > codebase? You could even dump the resulting ADO.NET recordset to
                          XML (I
                          > think) and do file comparisons on a known good XML dump of the same
                          > recordset (or somesuch)?

                          Yes. I can.

                          And I will.

                          Thanks,

                          Kay
                        Your message has been successfully submitted and would be delivered to recipients shortly.