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

RE: [XP] XP, database design and cost of change

Expand Messages
  • Pit Capitain
    ... Charlie, if you only look at the (empty) database schema, such a tool is possible, indeed. However, if you also consider the migration of existing data,
    Message 1 of 24 , Feb 1, 2002
    • 0 Attachment
      On 31 Jan 2002, at 17:57, Charlie Poole wrote:

      > Chris,
      >
      > > Know of any tools that will then take two versions and create a diff
      > > script -- a script that could be applied to a database of schema
      > > revision 5 to bring it up to schema revision 12?
      >
      > My first thought was that your SCM does it, but now I see what you
      > mean. So, given two sets of scripts that each generate a database,
      > produce a script that will migrate one database to another.
      >
      > Now that you've thought of it, I definitely want one!
      >
      > Creating such a program - if it doesn't exist already - would be
      > non-trivial but it seems fairly doable. Especially so since you might
      > be able to leverage the database software to parse the scripts and
      > create the internal structures that represent the database.

      Charlie, if you only look at the (empty) database schema, such a
      tool is possible, indeed. However, if you also consider the migration
      of existing data, you don't get enough information from the two
      scripts generating the database versions.

      For example, suppose you introduce a column in an ORDER table
      that - for performance reasons - holds the sum of all items
      belonging to an order. From the DDL scripts you can only detect
      the new column, but you have no clue which data to put in there.

      When I once had to maintain a very rapidly evolving database
      schema (a couple of changes every week), I wrote the diff scripts
      by hand at a slightly higher level than pure SQL and used a code
      generator to compile these scripts to SQL.

      In the example above, I'd write something like "add a new non-null
      column of type xxx to table ORDER and populate it using the
      following SQL commands ...". The code generator then generated
      the code to add a (null) column to the table, execute the populating
      SQL commands, and then change the column to have the non-null
      constraint.

      I added new commands to the code generator as I needed them,
      which wasn't too much work. I also introduced a table holding the
      actual schema version and a web frontend to a couple of scripts,
      so that each developer and each tester could automatically update
      her private database schema to newer versions. The generated diff
      scripts were also used to migrate the production database after
      approval of the test team.

      With this infrastructure in place, the amount of work to maintain the
      database schema was drastically reduced.

      Regards,
      Pit
    • Charlie Poole
      ... You re right, I haven t thought about data migration - nice solution. I still wouldn t mind seeing the basic tool for schemas though.
      Message 2 of 24 , Feb 1, 2002
      • 0 Attachment
        Pit wrote:
        > On 31 Jan 2002, at 17:57, Charlie Poole wrote:
        >
        > > Chris,
        > >
        > > > Know of any tools that will then take two versions and create a diff
        > > > script -- a script that could be applied to a database of schema
        > > > revision 5 to bring it up to schema revision 12?
        > >
        > > My first thought was that your SCM does it, but now I see what you
        > > mean. So, given two sets of scripts that each generate a database,
        > > produce a script that will migrate one database to another.
        > >
        > > Now that you've thought of it, I definitely want one!
        > >
        > > Creating such a program - if it doesn't exist already - would be
        > > non-trivial but it seems fairly doable. Especially so since you might
        > > be able to leverage the database software to parse the scripts and
        > > create the internal structures that represent the database.
        >
        > Charlie, if you only look at the (empty) database schema, such a
        > tool is possible, indeed. However, if you also consider the migration
        > of existing data, you don't get enough information from the two
        > scripts generating the database versions.

        <snip>

        You're right, I haven't thought about data migration - nice solution.

        I still wouldn't mind seeing the basic tool for schemas though.
      • Blum, Robert
        ... Unless I m totally off my rocker, it looks like this: http://www.quest.com/schema_manager/ does what you want? Robert
        Message 3 of 24 , Feb 1, 2002
        • 0 Attachment
          > From: Charlie Poole [mailto:cpoole@...]
          > You're right, I haven't thought about data migration - nice solution.
          >
          > I still wouldn't mind seeing the basic tool for schemas though.

          Unless I'm totally off my rocker, it looks like this:
          http://www.quest.com/schema_manager/ does what you want?

          Robert
        • Charlie Poole
          Robert, ... Sounds good - maybe more than I need - but also Oracle-specific. Charlie Poole cpoole@pooleconsulting.com
          Message 4 of 24 , Feb 1, 2002
          • 0 Attachment
            Robert,

            > > From: Charlie Poole [mailto:cpoole@...]
            > > You're right, I haven't thought about data migration - nice solution.
            > >
            > > I still wouldn't mind seeing the basic tool for schemas though.
            >
            > Unless I'm totally off my rocker, it looks like this:
            > http://www.quest.com/schema_manager/ does what you want?
            >

            Sounds good - maybe more than I need - but also Oracle-specific.

            Charlie Poole
            cpoole@...
          • Blum, Robert
            ... You _REALLY_ insist on making it difficult, do ya? :) OK, let us assume we really need something like that - what would be your stories? (I m itching to do
            Message 5 of 24 , Feb 4, 2002
            • 0 Attachment
              > From: Charlie Poole [mailto:cpoole@...]
              > Sounds good - maybe more than I need - but also Oracle-specific.

              You _REALLY_ insist on making it difficult, do ya? :)

              OK, let us assume we really need something like that - what would be your
              stories? (I'm itching to do some XP - if only XpForOne [or is that xp41?])

              Bye,
              Robert
            • Charlie Poole
              I was thinking about doing the same thing but I m too tied up right now. Here are a few thoughts. All of them assume input of two scripts that generate a
              Message 6 of 24 , Feb 4, 2002
              • 0 Attachment
                I was thinking about doing the same thing but I'm too tied up right now.

                Here are a few thoughts. All of them assume input of two scripts that
                generate a database, with one of them being the earlier form and the
                other a newer revised form. Output is to be a script which will
                update the earlier form to the later form.

                1. Recognize that no changes have been made and indicate by a message.
                2. Recognize that a table has been added (and generate the script)
                3. Recognize that a table has been deleted
                4. Recognize that a table definition has been altered

                Of course it gets much more complicated eventually, as multiple changes occur,
                dependencies are taken into account, etc.

                Charlie Poole
                cpoole@...


                > -----Original Message-----
                > From: Blum, Robert [mailto:rblum@...]
                > Sent: Monday, February 04, 2002 11:00 AM
                > To: 'extremeprogramming@yahoogroups.com'
                > Subject: RE: [XP] XP, database design and cost of change
                >
                >
                > > From: Charlie Poole [mailto:cpoole@...]
                > > Sounds good - maybe more than I need - but also Oracle-specific.
                >
                > You _REALLY_ insist on making it difficult, do ya? :)
                >
                > OK, let us assume we really need something like that - what would be your
                > stories? (I'm itching to do some XP - if only XpForOne [or is that xp41?])
                >
                > Bye,
                > Robert
                >
                > To Post a message, send it to: extremeprogramming@...
                >
                > To Unsubscribe, send a blank message to:
                > extremeprogramming-unsubscribe@...
                >
                > ad-free courtesy of objectmentor.com
                >
                > Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
                >
                >
                >
              • Blum, Robert
                ... Can we stipulate that the input scripts do not necessarily generate a database, but only contain a DB description? (Just for now) It s quite easy to
                Message 7 of 24 , Feb 4, 2002
                • 0 Attachment
                  > From: Charlie Poole [mailto:cpoole@...]

                  > I was thinking about doing the same thing but I'm too tied up
                  > right now.
                  >
                  > Here are a few thoughts. All of them assume input of two scripts that
                  > generate a database, with one of them being the earlier form and the
                  > other a newer revised form. Output is to be a script which will
                  > update the earlier form to the later form.

                  Can we stipulate that the input scripts do not necessarily generate a
                  database, but only contain a DB description? (Just for now)

                  It's quite easy to actually dump a DB schema in XML notation, and that would
                  make it much easier for me to create a working prototype - hence more value
                  earlier on :)

                  > 1. Recognize that no changes have been made and indicate by a message.
                  > 2. Recognize that a table has been added (and generate the script)
                  > 3. Recognize that a table has been deleted
                  > 4. Recognize that a table definition has been altered

                  I assume 3 and 4 generate a script, too?

                  Bye,
                  Robert
                • Charlie Poole
                  Good point.
                  Message 8 of 24 , Feb 4, 2002
                  • 0 Attachment
                    Good point.

                    > -----Original Message-----
                    > From: Blum, Robert [mailto:rblum@...]
                    > Sent: Monday, February 04, 2002 12:25 PM
                    > To: 'extremeprogramming@yahoogroups.com'
                    > Subject: RE: [XP] XP, database design and cost of change
                    >
                    >
                    > > From: Charlie Poole [mailto:cpoole@...]
                    >
                    > > I was thinking about doing the same thing but I'm too tied up
                    > > right now.
                    > >
                    > > Here are a few thoughts. All of them assume input of two scripts that
                    > > generate a database, with one of them being the earlier form and the
                    > > other a newer revised form. Output is to be a script which will
                    > > update the earlier form to the later form.
                    >
                    > Can we stipulate that the input scripts do not necessarily generate a
                    > database, but only contain a DB description? (Just for now)
                    >
                    > It's quite easy to actually dump a DB schema in XML notation, and that would
                    > make it much easier for me to create a working prototype - hence more value
                    > earlier on :)
                    >
                    > > 1. Recognize that no changes have been made and indicate by a message.
                    > > 2. Recognize that a table has been added (and generate the script)
                    > > 3. Recognize that a table has been deleted
                    > > 4. Recognize that a table definition has been altered
                    >
                    > I assume 3 and 4 generate a script, too?
                    >
                    > Bye,
                    > Robert
                    >
                    > To Post a message, send it to: extremeprogramming@...
                    >
                    > To Unsubscribe, send a blank message to:
                    > extremeprogramming-unsubscribe@...
                    >
                    > ad-free courtesy of objectmentor.com
                    >
                    > Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
                    >
                    >
                    >
                  • David Corbin
                    And I can throw some perl code at you if you want, which generates such a script (for oracle) if it is wanted (I ve started done this path, but got
                    Message 9 of 24 , Feb 4, 2002
                    • 0 Attachment
                      And I can throw some perl code at you if you want, which generates such
                      a script (for oracle) if it is wanted (I've started done this path, but
                      got distracted...)

                      Charlie Poole wrote:

                      >Good point.
                      >
                      >>-----Original Message-----
                      >>From: Blum, Robert [mailto:rblum@...]
                      >>Sent: Monday, February 04, 2002 12:25 PM
                      >>To: 'extremeprogramming@yahoogroups.com'
                      >>Subject: RE: [XP] XP, database design and cost of change
                      >>
                      >>
                      >>>From: Charlie Poole [mailto:cpoole@...]
                      >>>
                      >>>I was thinking about doing the same thing but I'm too tied up
                      >>>right now.
                      >>>
                      >>>Here are a few thoughts. All of them assume input of two scripts that
                      >>>generate a database, with one of them being the earlier form and the
                      >>>other a newer revised form. Output is to be a script which will
                      >>>update the earlier form to the later form.
                      >>>
                      >>Can we stipulate that the input scripts do not necessarily generate a
                      >>database, but only contain a DB description? (Just for now)
                      >>
                      >>It's quite easy to actually dump a DB schema in XML notation, and that would
                      >>make it much easier for me to create a working prototype - hence more value
                      >>earlier on :)
                      >>
                      >>>1. Recognize that no changes have been made and indicate by a message.
                      >>>2. Recognize that a table has been added (and generate the script)
                      >>>3. Recognize that a table has been deleted
                      >>>4. Recognize that a table definition has been altered
                      >>>
                      >>I assume 3 and 4 generate a script, too?
                      >>
                      >>Bye,
                      >> Robert
                      >>
                      >>To Post a message, send it to: extremeprogramming@...
                      >>
                      >>To Unsubscribe, send a blank message to:
                      >>extremeprogramming-unsubscribe@...
                      >>
                      >>ad-free courtesy of objectmentor.com
                      >>
                      >>Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
                      >>
                      >>
                      >>
                      >
                      >To Post a message, send it to: extremeprogramming@...
                      >
                      >To Unsubscribe, send a blank message to: extremeprogramming-unsubscribe@...
                      >
                      >ad-free courtesy of objectmentor.com
                      >
                      >Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
                      >
                      >
                      >
                    • Blum, Robert
                      I apologize I ve been silent on this front for a while - things have a tendency to get hectic whenever I volunteer my spare time for anything :) No, seriously
                      Message 10 of 24 , Feb 6, 2002
                      • 0 Attachment
                        I apologize I've been silent on this front for a while - things have a
                        tendency to get hectic whenever I volunteer my spare time for anything :)

                        No, seriously - I am setting up the infrastructure (dev machine, dev
                        environment, DB) at the moment. I'll report before the end of the week, with
                        a list of what I consider customer stories, and their estimates. This way,
                        I'll be ready to take on iteration one on a monday.

                        David, thanks for your kind offer of some code to get me started. I feel
                        inclined to decline, though - I would really like to tackle this one from
                        the ground up, test first. Just to see what happens. Plus, I always was
                        looking for a complete (tiny) XP project, documented completely on the web.
                        Since I never found one, I probably have to provide it myself.


                        Bye,
                        Robert
                      • David Corbin
                        ... No problem. There are a dozen reasons not to accept. If I had kept working on it to some degree, it would be a nice handy open source tool by now, but I
                        Message 11 of 24 , Feb 6, 2002
                        • 0 Attachment
                          Blum, Robert wrote:

                          >David, thanks for your kind offer of some code to get me started. I feel
                          >inclined to decline, though - I would really like to tackle this one from
                          >the ground up, test first. Just to see what happens. Plus, I always was
                          >looking for a complete (tiny) XP project, documented completely on the web.
                          >Since I never found one, I probably have to provide it myself.
                          >
                          No problem. There are a dozen reasons not to accept. If I had kept
                          working on it to some degree, it would be a nice handy open source tool
                          by now, but I didn't. I just felt I should offer.

                          >
                          >Bye,
                          > Robert
                          >
                          >To Post a message, send it to: extremeprogramming@...
                          >
                          >To Unsubscribe, send a blank message to: extremeprogramming-unsubscribe@...
                          >
                          >ad-free courtesy of objectmentor.com
                          >
                          >Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
                          >
                          >
                          >
                        Your message has been successfully submitted and would be delivered to recipients shortly.