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

Iterative SQL Writing

Expand Messages
  • psadalage
    Has anyone done iterative SQL writing, if soneone has a huge spec to write a SQL statement, how have you gone about writing it? any automated tests for the
    Message 1 of 9 , Sep 20, 2003
    • 0 Attachment
      Has anyone done iterative SQL writing, if soneone has a huge spec to
      write a SQL statement, how have you gone about writing it? any
      automated tests for the SQL?

      anyone has thoughts on this?

      Pramod
    • Somik Raha
      ... At a project I was coaching, we had massive multi-page sql queries.. We developed FIT fixtures to unit test them. DB/ETL folks would create tests in HTML
      Message 2 of 9 , Sep 22, 2003
      • 0 Attachment
        On Sat, 2003-09-20 at 08:37, psadalage wrote:
        > Has anyone done iterative SQL writing, if soneone has a huge spec to
        > write a SQL statement, how have you gone about writing it? any
        > automated tests for the SQL?
        >
        > anyone has thoughts on this?

        At a project I was coaching, we had massive multi-page sql queries.. We
        developed FIT fixtures to unit test them. DB/ETL folks would create
        tests in HTML tables, and verify that the result is what they expected.
        The creation process was easy (used Fitnesse).

        We extended this to stored procs.. massive queries would be encapsulated
        within stored procs, and as the resulting dataset tended to be large,
        cut-back on the data would generally be required using enclosing
        stored-procs or queries (on temp tables).

        The fixtures seem to be working well but are still evolving.
        I could find out if the team could release the fixtures for the
        community.

        Regards,
        Somik


        I n d u s t r i a l L o g i c , I n c .
        Somik Raha
        Extreme Programmer & Coach
        http://industriallogic.com
        http://industrialxp.org
        866-540-8336 (toll free)
        510-540-8336 (phone)

        Without vision, flexibility is just an abstraction -- Tom DeMarco, in
        Slack
      • jbrekke@wi.rr.com
        ... This sounds very interesting. I d be interested in seeing what you ve done also. Could you explain more about how you limit your results down to
        Message 3 of 9 , Sep 23, 2003
        • 0 Attachment
          >>>>> On Mon, 22 Sep 2003 22:45:24 -0700, Somik Raha <somik@...> said:

          > On Sat, 2003-09-20 at 08:37, psadalage wrote:
          >> Has anyone done iterative SQL writing, if soneone has a huge spec
          >> to write a SQL statement, how have you gone about writing it? any
          >> automated tests for the SQL?
          >>
          >> anyone has thoughts on this?

          > At a project I was coaching, we had massive multi-page sql
          > queries.. We developed FIT fixtures to unit test them. DB/ETL folks
          > would create tests in HTML tables, and verify that the result is
          > what they expected. The creation process was easy (used Fitnesse).

          > We extended this to stored procs.. massive queries would be
          > encapsulated within stored procs, and as the resulting dataset
          > tended to be large, cut-back on the data would generally be required
          > using enclosing stored-procs or queries (on temp tables).

          > The fixtures seem to be working well but are still evolving. I
          > could find out if the team could release the fixtures for the
          > community.

          This sounds very interesting. I'd be interested in seeing what you've
          done also. Could you explain more about how you limit your results
          down to something that is reasonable for testing? Our
          developers/customers seem to think that they must get all results and
          check all results each time for the test. Not only is the query slow,
          but the result set could be huge.


          Thanks,

          jb

          --
          =====================================================================
          Jeffrey D. Brekke jbrekke@...
          Wisconsin, USA brekke@...
          ekkerbj@...
        • Somik Raha
          This sounds very interesting. I d be interested in seeing what you ve done also. Could you explain more about how you limit your results down to something
          Message 4 of 9 , Sep 23, 2003
          • 0 Attachment
            This sounds very interesting.  I'd be interested in seeing what you've
            done also.  Could you explain more about how you limit your results
            down to something that is reasonable for testing?  Our
            developers/customers seem to think that they must get all results and
            check all results each time for the test.  Not only is the query slow,
            but the result set could be huge.


            We faced that as well. It turned out that the team had a lot of "coverage" tests for their data. But few storytests. Our fixtures were directed at this problem.
             
            A lot of this was contextual. We had only 5 massive queries, that would be called from a java program. The data they produced was large. The queries couldnt be broken up due to performance reasons. But they could be encapsulated. They didn't take more than a few seconds to run (on a powerful server). Its the testing that took the most time. So a cutback on the result set would work for the most part.  One advantage of the encapsulation was the unification of test sql and production sql. If they are not the same, then you're kind of left hoping that the sql that you just tested is the same sql that multiple clients are using.
             
            The ETL context was different. They had to load and transform data using dozens of business rules. Their deliverable was SQL which would go into their loading tool. They wouldn't benefit from tons of stored procs. Here, smaller queries were possible and preferred. Our fixtures allow them to easily parametrize SQL queries, giving the same effect as stored procs that take in parameters. And given the fit tests, we could produce executable documentation for the business logic in sql.
             
            Regards,
            Somik
             
            I n d u s t r i a l   L o g i c ,  I n c .
            Somik Raha
            Extreme Programmer & Coach
            http://industriallogic.com
            http://industrialxp.org
            866-540-8336 (toll free)
            510-540-8336 (phone)
             
            .. the major danger in vertical thinking is not that of being trapped
            by the obvious but of failing to realize that one may be trapped by
            the obvious. It is not a matter of avoiding vertical  thinking  but  of
            using  it  and   at  the  same  time  being  aware  that  it  might  be
            necessary to escape from a particular way of looking at a situation.
             
            --- Edward De Bono in Lateral Thinking, Chapter 16, Analogies
             
          • Bishop, Murray
            ... I grew some hundreds of lines of SQL a few years ago to help loading some tables in an Oracle database. I was using the Oracle sqlplus utility to run the
            Message 5 of 9 , Sep 23, 2003
            • 0 Attachment
              Pramod wrote (Sat, 20 Sep 2003 15:37:25 -0000):

              > Has anyone done iterative SQL writing, if soneone has a huge spec to
              > write a SQL statement, how have you gone about writing it? any
              > automated tests for the SQL?
              >
              > anyone has thoughts on this?

              I grew some hundreds of lines of SQL a few years ago to help loading some
              tables in an Oracle database. I was using the Oracle sqlplus utility to run
              the SQL inside Windows batch files, aided by Cygwin GNU text utilities. The
              batch file that ran this stuff had 2 modes of operation, production run, and
              --self-test.

              In the --self-test case, roughly what happened (in a minute or so) was:

              - concatenate setup.sql, realstuff.sql, showresult.sql to test.sql
              - run test.sql
              - use diff to compare output of showresult.sql to last known good

              setup.sql created test tables with input values, expected final values.
              Realstuff.sql is the code being tested.
              Test.sql shows rows where actual final values differ from expected.

              In the production case, use sed to change the table names in realstuff.sql
              from test names to real production table names, and run it (over hours).

              I was able to proceed in test first mode:
              - write a failing test (add rows to the input and expected final tables)
              - write just enough code to pass
              - refactor (well sometimes ;->)

              I found working this way a lot harder than doing TDD in java with jUnit.
              When tests failed, it was harder to see which test has failed (all I saw
              were some different rows), and which code wanted to change. I guess that's
              because I chose to run the tests in one big lump - that way I only took the
              20 second sqlplus startup hit once.

              Putting together test cases to drive out behavior was sometimes hard, I
              found that I got some help by having fewer columns in the test tables than
              in the real tables (just the ones I cared about). That cut down the size of
              insert statements. When I had to change behavior, it was hard to identify
              which rows of expected values to change sometimes - and other times, a whole
              swag of rows would want changing.

              We (me and my Customer) got real benefit from having done things test first
              though. In the course of building up this stuff, we learned a lot about what
              the target system really needed to see, and how the input data should be
              bent to fit that. That led to many changes in what we wanted the SQL to do.
              I was able to proceed with confidence for all of those changes - thanks to
              the tests telling me when my latest edit had messed up. I'm sure I would
              have made more mistakes and got less done in the time without them.

              I intend doing that again next time I have a similar problem, unless I learn
              a better way (what Somik Raha wrote sounded really interesting - I'd like to
              see how things were done using FitNesse)

              Best,
              Murray
            • Alex Weatherall
              Does anyone else have any recommendations as to what are the best methods around for unit testing database and query refactoring. I was thinking of going down
              Message 6 of 9 , Sep 24, 2003
              • 0 Attachment
                Does anyone else have any recommendations as to what are the best
                methods around for unit testing database and query refactoring. I
                was thinking of going down the line of using NUnit and having a set
                of tests written as a .net app. ie you have a collection of test
                methods for every stored procedure/query you have and you run NUnit
                against those methods. I've had a (very) brief look at Fitnesse as
                well, but haven't got my head round that yet.
                Also does anyone have any suggestions for documenting databases from
                the schema. I'm currently working on producing documentation as a
                set of html pages from an xml document that I'm getting from the
                schema, but it looks like I'll have to maintain a separate database
                for all the 'extra' information I want to record about the database
                (ie, parameter notes/result sets for stored procedures.)

                Any ideas/pointers would be helpful.

                Cheers

                Alex Weatherall
                TeleWare plc
              • Scott W. Ambler
                The data community has beaten the documentation issue to death, IMHO, with their metadata repository tools. Granted, they re often overkill (OK, they always
                Message 7 of 9 , Sep 24, 2003
                • 0 Attachment
                  The data community has beaten the documentation issue to death, IMHO, with
                  their metadata repository tools. Granted, they're often overkill (OK, they
                  always seem to be overkill) but it seems to me that there is a wonderful
                  opportunity for an open source tool here.

                  - Scott

                  At 11:05 AM 9/24/2003 +0000, you wrote:
                  >Does anyone else have any recommendations as to what are the best
                  >methods around for unit testing database and query refactoring. I
                  >was thinking of going down the line of using NUnit and having a set
                  >of tests written as a .net app. ie you have a collection of test
                  >methods for every stored procedure/query you have and you run NUnit
                  >against those methods. I've had a (very) brief look at Fitnesse as
                  >well, but haven't got my head round that yet.
                  >Also does anyone have any suggestions for documenting databases from
                  >the schema. I'm currently working on producing documentation as a
                  >set of html pages from an xml document that I'm getting from the
                  >schema, but it looks like I'll have to maintain a separate database
                  >for all the 'extra' information I want to record about the database
                  >(ie, parameter notes/result sets for stored procedures.)
                  >
                  >Any ideas/pointers would be helpful.
                  >
                  >Cheers
                  >
                  >Alex Weatherall
                  >TeleWare plc
                  >
                  >
                  >
                  >To unsubscribe from this group, send an email to:
                  >agileDatabases-unsubscribe@yahoogroups.com
                  >
                  >
                  >
                  >Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/

                  ====================================================
                  Scott W. Ambler
                  Senior Consultant, Ronin International, Inc.
                  www.ronin-intl.com/company/scottAmbler.html

                  www.agiledata.org
                  www.agilemodeling.com
                  www.ambysoft.com
                  www.enterpriseunifiedprocess.info
                  www.modelingstyle.info
                  www.ronin-intl.com
                • Dave Andreasen
                  Hello Alex, Have you looked at DBUnit (http://www.dbunit.org)? That tool has database loading capabilities. Though it is Java-based, you might be able to
                  Message 8 of 9 , Sep 24, 2003
                  • 0 Attachment
                    Hello Alex,

                    Have you looked at DBUnit (http://www.dbunit.org)? That tool has database
                    loading capabilities. Though it is Java-based, you might be able to port
                    it to .Net without too much trouble.

                    Sorry - I don't have any ideas with respect to the documentation. It might
                    be easier to pull the stored procedure documentation from the source code
                    than to get it from the database.

                    Hope this helps!

                    Dave

                    At 11:05 AM 9/24/2003 +0000, Alex Weatherall wrote:
                    >Does anyone else have any recommendations as to what are the best
                    >methods around for unit testing database and query refactoring. I
                    >was thinking of going down the line of using NUnit and having a set
                    >of tests written as a .net app. ie you have a collection of test
                    >methods for every stored procedure/query you have and you run NUnit
                    >against those methods. I've had a (very) brief look at Fitnesse as
                    >well, but haven't got my head round that yet.
                    >Also does anyone have any suggestions for documenting databases from
                    >the schema. I'm currently working on producing documentation as a
                    >set of html pages from an xml document that I'm getting from the
                    >schema, but it looks like I'll have to maintain a separate database
                    >for all the 'extra' information I want to record about the database
                    >(ie, parameter notes/result sets for stored procedures.)
                    >
                    >Any ideas/pointers would be helpful.
                    >
                    >Cheers
                    >
                    >Alex Weatherall
                    >TeleWare plc
                    >
                    >
                    >
                    >To unsubscribe from this group, send an email to:
                    >agileDatabases-unsubscribe@yahoogroups.com
                    >
                    >
                    >
                    >Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/

                    +---------------------------------------------------------------+
                    | Dave Andreasen |
                    | dandreasen@... |
                    | |
                    | Caribou Lake Software - http://www.cariboulake.com |
                    +---------------------------------------------------------------+
                  • Laurent
                    Hi Alex ... About that second issue, I m working with CAST products and specifically CAST Enlighten .
                    Message 9 of 9 , Sep 24, 2003
                    • 0 Attachment
                      Hi Alex

                      > Also does anyone have any suggestions for
                      > documenting databases from
                      > the schema. I'm currently working on producing
                      > documentation as a
                      > set of html pages from an xml document that I'm
                      > getting from the
                      > schema, but it looks like I'll have to maintain a
                      > separate database
                      > for all the 'extra' information I want to record
                      > about the database
                      > (ie, parameter notes/result sets for stored
                      > procedures.)
                      >
                      > Any ideas/pointers would be helpful.
                      >

                      About that second issue, I'm working with CAST
                      products and specifically "CAST Enlighten".

                      http://www.castsoftware.com/solutions/profile/developer/Documentation.html

                      That creates automatically all kind of documentations
                      under graphical form and/or HTML, from the SQL
                      database structure itself (no need for a separate
                      database), just what you want to do...
                      (they support Oracle, MS SQL Server, DB2 and Sybase
                      ASE)

                      HTH

                      /Laurent





                      ___________________________________________________________
                      Do You Yahoo!? -- Une adresse @... gratuite et en fran├žais !
                      Yahoo! Mail : http://fr.mail.yahoo.com
                    Your message has been successfully submitted and would be delivered to recipients shortly.