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

Re: [agileDatabases] Re: agile ddl scripts

Expand Messages
  • Chris Morris
    ... each ... I think it s better to use an approach in development that also works in production. To me it s too tempting to keep the dev shortcuts around
    Message 1 of 15 , Jan 7, 2003
      > Another approach may to drop the database and recreate it from scratch
      each
      > time along with any initial data. This would work if there is no need to
      > hold any data inserted by developers.

      I think it's better to use an approach in development that also works in
      production. To me it's too tempting to keep the dev shortcuts around until I
      really need something that works in production and then, whoops, time to
      start over or line-up the refactorings so no production data is lost -- and
      usually I've got no more dev time left when I get to this point. Treat all
      dev data like production data in the automation.

      Then when you really just do a new database for dev purposes, drop it
      manually and run the latest ddl scripts.

      Chris
    • Emlyn O'regan
      We may have some useful code here too, also Delphi. It s a Delphi 5 shop at my end, btw. A Delphi based, windows only solution would suit me just fine. Ruby
      Message 2 of 15 , Jan 7, 2003
        RE: [agileDatabases] agile ddl scripts
        We may have some useful code here too, also Delphi.
         
        It's a Delphi 5 shop at my end, btw. A Delphi based, windows only solution would suit me just fine.
         
        Ruby looks interesting, but if there's a chance of using Delphi, I'd leap at it!
         
        btw, those third party components, do they do anything irreplaceable? I imagine that the BDE can deliver up some pretty solid schema information and manipulation functions, if you know how to get at them, especially using SQL Links drivers (old tech, I know).
         
        Emlyn
         
         -----Original Message-----
        From: Chris Morris [mailto:chrismo@...]
        Sent: Tuesday, 7 January 2003 9:54
        To: agileDatabases@yahoogroups.com
        Subject: Re: [agileDatabases] agile ddl scripts

        If there's not something already, I'd very much be interested in trying to collaborate on an open tool. This sort of thing will be soon looming for me on the job, so the timing may be good for my involvement. No promises of course, I'll be limited by what I can contribute in the line of duty, my free time is dedicated elsewhere.
         
        I've already got a tool (written in Delphi, using a 3rd party lib for ODBC) that I can use to compare the schema's of two databases. The ODBC lib gives very easy access to schema info, so it wasn't that hard to write.
         
        My next step for that tool I never got around to was being able to do the comparison against a static file, maybe XML, since having two live dbs to do a comparison won't work for many future client databases we'll be having this year.
         
        But the existing code is not a good start for an open source dealy -- there's no free Delphi compiler (well, there are some free pascal compilers, but Delphi compatibility is quite limited last I checked), plus it's dependent on the 3rd party lib for which there is no free option.
         
        Something like Ruby and its DBI library might be good (or Ruby -> WIN32OLE -> ADO if Windows only is acceptable).
         
        Of course, high-end tools like DBArtisan and their ilk do this sort of stuff don't they? Surely(?) there's something open source out there already?
         
        Chris
        ----- Original Message -----
        Sent: Monday, January 06, 2003 5:08 PM
        Subject: RE: [agileDatabases] agile ddl scripts

        - If not, is anyone in the same boat who'd like to look at putting together a simple opensource product to do the job?



        To unsubscribe from this group, send an email to:
        agileDatabases-unsubscribe@yahoogroups.com



        Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.


        ***************************************************************************

        Confidentiality: The contents of this email are confidential and are intended only for the named recipient. If the reader of this e-mail is not the intended recipient you are hereby notified that any use, reproduction, disclosure or distribution of the information contained in the e-mail is prohibited. If you have received this e-mail in error, please reply to us immediately and delete the document.

        Viruses: Any loss/damage incurred by using this material is not the sender's responsibility. Our entire liability will be limited to resupplying the material. No warranty is made that this material is free from computer virus or other defect.



      • jhrothjr <yahoogroups@jhrothjr.com>
        ... It depends on your objective. Are you simply trying to verify what level the data base is at so that you can apply the right change set to get it to
        Message 3 of 15 , Jan 7, 2003
          --- In agileDatabases@yahoogroups.com, "Chris Morris" <chrismo@c...> wrote:
          > > I was actually toying with this methodology myself about a month
          > > ago. I would add that each script should have some kind of prologue
          > > (in PL/SQL or similar) to verify that the current version of the db
          > > is n-1 (if n is defined as the version of the db established in the
          > > current script). If the version is not n-1, the script exits. This
          > > provides some safety against executing scripts out of order.
          >
          > As I wrote elsewhere, for my environment, I'd also like to have a 100%
          > thorough verification of the schema, table by table, column by column, etc.
          > I want to be able to guarantee that this really is version n-1, not just
          > trust a single number in a table. Of course, this is more work.

          It depends on your objective. Are you simply trying to verify what level the data base is at so that you can apply the right change set to get it to another level, or do you really have a need to verify the entire enchilada every time you look at it?

          In the first case, you only need to verify enough tables to determine the level. This probably isn't that big a deal; the number of verification scripts is, at worst, the same as the number of change scripts and is probably much lower.

          I'm not sure why you need the second unless you suspect that someone else is fiddling with the DB outside of your control. In that case, you've got worse problems than a simple level verification.

          John Roth
          >
          > Chris
        • Chris Morris
          ... is fiddling with the DB outside of your control. In that case, you ve got worse problems than a simple level verification. Possibly -- but some of my soon
          Message 4 of 15 , Jan 7, 2003
            > I'm not sure why you need the second unless you suspect that someone else
            is fiddling with the DB outside of your control. In that case, you've got
            worse problems than a simple level verification.

            Possibly -- but some of my soon to be clients are franchise owners, and
            their database is theirs to do what they want, within reason of course, but
            I've learned that if it's not that much more to cover my entire backside
            rather than just the seam, it's probably worthwhile :)

            Chris
          • Chris Morris
            RE: [agileDatabases] agile ddl scriptsAh, nice to run into another Borlander! The 3rd party lib is ODBCExpress, a BDE replacement, so no luck there. Any Ruby
            Message 5 of 15 , Jan 7, 2003
              RE: [agileDatabases] agile ddl scripts
              Ah, nice to run into another Borlander!
               
              The 3rd party lib is ODBCExpress, a BDE replacement, so no luck there.
               
              Any Ruby *is* quite interesting, I'd definitely recommend it. Ping me off line and I can send you some interesting teaser code.
              ----- Original Message -----
              Sent: Tuesday, January 07, 2003 4:44 PM
              Subject: RE: [agileDatabases] agile ddl scripts

              We may have some useful code here too, also Delphi.
               
              It's a Delphi 5 shop at my end, btw. A Delphi based, windows only solution would suit me just fine.
               
              Ruby looks interesting, but if there's a chance of using Delphi, I'd leap at it!
               
              btw, those third party components, do they do anything irreplaceable? I imagine that the BDE can deliver up some pretty solid schema information and manipulation functions, if you know how to get at them, especially using SQL Links drivers (old tech, I know).
               
              Emlyn
               
               -----Original Message-----
              From: Chris Morris [mailto:chrismo@...]
              Sent: Tuesday, 7 January 2003 9:54
              To: agileDatabases@yahoogroups.com
              Subject: Re: [agileDatabases] agile ddl scripts

              If there's not something already, I'd very much be interested in trying to collaborate on an open tool. This sort of thing will be soon looming for me on the job, so the timing may be good for my involvement. No promises of course, I'll be limited by what I can contribute in the line of duty, my free time is dedicated elsewhere.
               
              I've already got a tool (written in Delphi, using a 3rd party lib for ODBC) that I can use to compare the schema's of two databases. The ODBC lib gives very easy access to schema info, so it wasn't that hard to write.
               
              My next step for that tool I never got around to was being able to do the comparison against a static file, maybe XML, since having two live dbs to do a comparison won't work for many future client databases we'll be having this year.
               
              But the existing code is not a good start for an open source dealy -- there's no free Delphi compiler (well, there are some free pascal compilers, but Delphi compatibility is quite limited last I checked), plus it's dependent on the 3rd party lib for which there is no free option.
               
              Something like Ruby and its DBI library might be good (or Ruby -> WIN32OLE -> ADO if Windows only is acceptable).
               
              Of course, high-end tools like DBArtisan and their ilk do this sort of stuff don't they? Surely(?) there's something open source out there already?
               
              Chris
              ----- Original Message -----
              Sent: Monday, January 06, 2003 5:08 PM
              Subject: RE: [agileDatabases] agile ddl scripts

              - If not, is anyone in the same boat who'd like to look at putting together a simple opensource product to do the job?



              To unsubscribe from this group, send an email to:
              agileDatabases-unsubscribe@yahoogroups.com



              Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.


              To unsubscribe from this group, send an email to:
              agileDatabases-unsubscribe@yahoogroups.com



              Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.


              ***************************************************************************

              Confidentiality: The contents of this email are confidential and are intended only for the named recipient. If the reader of this e-mail is not the intended recipient you are hereby notified that any use, reproduction, disclosure or distribution of the information contained in the e-mail is prohibited. If you have received this e-mail in error, please reply to us immediately and delete the document.

              Viruses: Any loss/damage incurred by using this material is not the sender's responsibility. Our entire liability will be limited to resupplying the material. No warranty is made that this material is free from computer virus or other defect.



            • dizzley <peter.hitchmough@astrazeneca.co
              ... a schema ... that if the ... just run A really good way to keep a handle on a changing database structure (even reference data too) is to keep the DDL in a
              Message 6 of 15 , Jan 8, 2003
                --- In agileDatabases@yahoogroups.com, "Chris Morris" <chrismo@c...>
                wrote:

                > Basically, my approach so far involves a central table that stores
                a schema
                > version number. Then the rest of the ddl scripts do a check on that.

                > I don't like this approach because it's assumptive -- it assumes
                that if the
                > SchemaVersion is 'y', then all of the following ddl scripts are
                just run

                A really good way to keep a handle on a changing database structure
                (even reference data too) is to keep the DDL in a code control system
                e.g. CVS. This can even handle branched code control. Always aim to
                keep the database and application functions bound together into
                one "release tag".

                Then store the release tag in a global variable or parameter table.
                This allows a measure of knowledge of the database version.

                If collaborators from outside the immediate team use your data then
                they accept they are "at risk" from changes and either make their own
                copies of tables/ views etc. or take the refactoring hit.

                There are also 3rd party tools to check for data structure
                consistency. TOAD from Quest (www.quest.com) is
                fairly cheap and will do a "Compare Schemas". Quest have some heavier
                weight tools also like Schema Manager. In a large project you are
                likely to see Oracle's Designer or something like that which can do
                the job - not just a repository.

                As an observation: even if your environment is not really agile it
                will still show the same bad behaviours during the intense
                integration phase when datbase and app changes chase each other round
                in some absurb dance.

                Cheers,
                Pete (Dizzley)
              • Chris Morris
                ... I do version control my ddl ... it needs to be more resilient (e.g. I just only recently stripped out the drop table commands for production tables --
                Message 7 of 15 , Jan 8, 2003
                  > A really good way to keep a handle on a changing database structure
                  > (even reference data too) is to keep the DDL in a code control system
                  > e.g. CVS. This can even handle branched code control. Always aim to
                  > keep the database and application functions bound together into
                  > one "release tag".

                  I do version control my ddl ... it needs to be more resilient (e.g. I just
                  only recently stripped out the drop table commands for production tables --
                  obviously a no-no for upgrades, of which I've not had to do many, but that's
                  looming :)

                  There's very little to tie together the app build to a schema 'build' --
                  although I've been making strides. The app has internal knowledge of its
                  version and which schema versions it can work with, and will refuse to run
                  if it detects a mismatch.

                  > If collaborators from outside the immediate team use your data then
                  > they accept they are "at risk" from changes and either make their own
                  > copies of tables/ views etc. or take the refactoring hit.

                  Fortunately, I don't really have that level of outside involvement. No one
                  really *should* ever be messing with stuff. I'd just like better feedback to
                  save me from myself and install confidence.

                  > There are also 3rd party tools to check for data structure
                  > consistency. TOAD from Quest (www.quest.com) is
                  > fairly cheap and will do a "Compare Schemas". Quest have some heavier
                  > weight tools also like Schema Manager. In a large project you are
                  > likely to see Oracle's Designer or something like that which can do
                  > the job - not just a repository.

                  I'll have to check out TOAD - thanks.

                  Thx for the comments.

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