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

RE: [agileDatabases] Test-driving SQL Server

Expand Messages
  • 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 1 of 11 , Aug 25, 2004
      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 2 of 11 , Aug 25, 2004
        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 3 of 11 , Aug 25, 2004
          >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 4 of 11 , Aug 26, 2004
            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 5 of 11 , Aug 26, 2004
              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 6 of 11 , Aug 26, 2004
                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 7 of 11 , Aug 26, 2004
                  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 8 of 11 , Aug 26, 2004
                    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 9 of 11 , Aug 26, 2004
                      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 10 of 11 , Aug 30, 2004
                        --- 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.