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

Re: agile ddl scripts

Expand Messages
  • jhrothjr <yahoogroups@jhrothjr.com>
    ... If the major issue is determining the current revision level of the data base so the correct set of changes can be run, then I d go with a brute force
    Message 1 of 15 , Jan 6, 2003
    View Source
    • 0 Attachment
      --- In agileDatabases@yahoogroups.com, "Chris Morris" <chrismo@c...> wrote:
      > I have two production apps that I've been tinkering with approaches to
      > scripting schema changes. I've not had need to do many updates, so both are
      > very young and I haven't seen either in action long enough to get a feel for
      > the pros and cons. I wanted to throw out my thoughts so far to get some
      > feedback from the group.
      >
      > 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. So the
      > final script to upgrade from version x to y is akin to:
      >
      > update SysConfig
      > set Value = 'y'
      > where Item = 'SchemaVersion'
      >
      > if SchemaVersion = 'y'
      > alter table TableA yada yada yada ...
      >
      > if SchemaVersion = 'y'
      > alter table TableB yada yada yada ...
      >
      > if SchemaVersion = 'y'
      > create table NewTable
      >
      > Each ddl script is actually in its own file, then I run a combine program to
      > build one master script for easier execution.
      >
      > 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 --
      > there's no checking the actual existing schema to ensure it's okay prior to
      > running the ddl changes. And that same validation check could be used in
      > isolation to simply verify the current schema in any database.
      >
      > So I guess I'm wanting to head to something similar to what Willem Bogaerts
      > wrote about [http://groups.yahoo.com/group/agileDatabases/message/41%5d. That
      > seems like a lot of work ... but of course it wouldn't have been if I'd
      > started that way from the get go.
      >
      > Thoughts?

      If the major issue is determining the current revision level of the data base so the correct set of changes can be run, then I'd go with a brute force approach.

      I'd throw together a script that checks the differences at any given revision level to determine the current level, and then apply the changes from the ddl files to bring it to the level you want.

      for example, from a command line:

      >>> migratedb <database> <level>

      >>> migratedb fubar 10

      meaning: bring data base "fubar" up to level 10.

      If you're proficient at a scripting language it should be a couple of hours to set it up. When I say "brute force" here I mean that I'd probably just run the standard admin tools to dump table structures to a file, and then text compare the file to determine if the table I'm looking at has the expected structure for a given level (or set of levels.)

      Making it look pretty and making it run fast are something for later, when I can justify that it matters.

      John Roth

      >
      > Chris
      > http://clabs.org
    • Stephen van Egmond
      ... Eeek. A little too brute-force for me. It seems like it d break quicky unless you bother to remember all the conditions for all the previous schema
      Message 2 of 15 , Jan 6, 2003
      View Source
      • 0 Attachment
        jhrothjr <yahoogroups@...> (yahoogroups@...) wrote:
        > If you're proficient at a scripting language it should be a couple of hours to
        >set it up. When I say "brute force" here I mean that I'd probably just run the
        >standard admin tools to dump table structures to a file, and then text compare
        >the file to determine if the table I'm looking at has the expected structure for
        >a given level (or set of levels.)

        Eeek. A little too brute-force for me. It seems like it'd break quicky unless
        you bother to remember all the conditions for all the previous schema
        arrangements. And how could you do that, when in a team environment, you didn't
        even necessarily write them all?

        How about a one-row, one-column table in the schema itself, as a bootstrap?

        create table schema_descriptor (
        schema_version int
        );
        insert into schema_descriptor(schema_version) values(1);

        The rule for any upgrade script is that it has to say as its last line:

        update schema_descriptor set schema_version = n;

        -Steve
      • jonas_edgeworth <jonas_edgeworth@yahoo.c
        ... bootstrap? ... 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
        Message 3 of 15 , Jan 7, 2003
        View Source
        • 0 Attachment
          > How about a one-row, one-column table in the schema itself, as a
          bootstrap?
          >
          > create table schema_descriptor (
          > schema_version int
          > );
          > insert into schema_descriptor(schema_version) values(1);
          >
          > The rule for any upgrade script is that it has to say as its last
          line:
          >
          > update schema_descriptor set schema_version = n;

          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.

          At this point, the only issue is writing some external process to
          determine the upgrade level, and execute the appropriate set of ddl
          scripts. Assuming a naming convention for your sql scripts that has
          a direct correlation to the schema version, coding this behavior
          should be a no-brainer.
        • Chris Morris
          ... 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
          Message 4 of 15 , Jan 7, 2003
          View Source
          • 0 Attachment
            > 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.

            Chris
          • Barry Fortune
            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
            Message 5 of 15 , Jan 7, 2003
            View Source
            • 0 Attachment
              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.
              -----Original Message-----
              From: Chris Morris [mailto:chrismo@...]
              Sent: Tuesday, January 07, 2003 12:05 PM
              To: agileDatabases@yahoogroups.com
              Subject: Re: [agileDatabases] Re: agile ddl scripts

              > 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.

              Chris



              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.

              *******************Internet Email Confidentiality Footer*******************


              Privileged/Confidential Information may be contained in this message. If you

              are not the addressee indicated in this message (or responsible for delivery

              of the message to such person), you may not copy or deliver this message to

              anyone.


              In such case, you should destroy this message and kindly notify the sender by

              reply email. Please advise immediately if you or your employer do not consent

              to Internet email for messages of this kind. Please note, any views

              expressed by an individual within this E-mail do not necessarily reflect

              the views of Avistar.


            • 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 6 of 15 , Jan 7, 2003
              View Source
              • 0 Attachment
                > 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 7 of 15 , Jan 7, 2003
                View Source
                • 0 Attachment
                  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 8 of 15 , Jan 7, 2003
                  View Source
                  • 0 Attachment
                    --- 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 9 of 15 , Jan 7, 2003
                    View Source
                    • 0 Attachment
                      > 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 10 of 15 , Jan 7, 2003
                      View Source
                      • 0 Attachment
                        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 11 of 15 , Jan 8, 2003
                        View Source
                        • 0 Attachment
                          --- 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 12 of 15 , Jan 8, 2003
                          View Source
                          • 0 Attachment
                            > 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.