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

RE: [agileDatabases] how to sequence database refactorings?

Expand Messages
  • Scott A. Pascoe
    Pramod, I d be interested in that Stored Procedure. -- Thanks, Scott ... From: Pramod [mailto:psadalage@yahoo.com] Sent: Friday, June 25, 2004 9:21 AM To:
    Message 1 of 15 , Jun 25, 2004
      Pramod,

      I'd be interested in that Stored Procedure.

      --
      Thanks, Scott

      -----Original Message-----
      From: Pramod [mailto:psadalage@...]
      Sent: Friday, June 25, 2004 9:21 AM
      To: agileDatabases@yahoogroups.com
      Subject: Re: [agileDatabases] how to sequence database refactorings?

      here are the details, I stripped out the previous
      msg's for clarity sake.. hopefully the formatting
      makes sense :)

      >* What is the structure of this table?

      The structure we use is

      create table versionlog
      (processname varchar2(50) not null, --Which Process
      updated this versionlog table row
      schema_version_date date not null, -- what was the
      date of the last sql file applied (this could be a
      build number, iteration number, sequential number etc)
      errorcd number(25) null, -- errors if any
      message varchar2(150) null --descriptive message
      )

      >* Does it have multiple records recording all
      previously applied schema versions, or just a single
      record containing the current schema version?

      It has multiple rows, one row for each sql file
      applied.

      >* How is this table updated? Does each individual
      script update the version right before it completes
      and commits?

      Each individual sql file checks if it is the right one
      to be applied before applying itself, if it is the
      right one in the sequence, then it applies all the
      changes and then inserts a new row into the versionlog
      table with the appropriate values, if it is not the
      right one in the sequence then it just aborts with an
      error msg. we have a stored procedure that each sql
      script calls before and after (like first line and
      last line of the sql file), and the stored procedure
      does the checking against the versionlog table and
      also does the insert into the versionlog table once
      the update of the sql file is successful.

      >* What do you mean by "log files"? Are these the
      *.sql script files you refer to, or something
      different?

      they are the sql files

      --Pramod
      PS : if you need a sample of the stored procedure,
      send me an email.


      =====




      __________________________________
      Do you Yahoo!?
      Yahoo! Mail - 50x more storage than other providers!
      http://promotions.yahoo.com/new_mail




      Yahoo! Groups Links






      ---
      Incoming mail is certified Virus Free.
      Checked by AVG anti-virus system (http://www.grisoft.com).
      Version: 6.0.711 / Virus Database: 467 - Release Date: 6/25/2004


      ---
      Outgoing mail is certified Virus Free.
      Checked by AVG anti-virus system (http://www.grisoft.com).
      Version: 6.0.711 / Virus Database: 467 - Release Date: 6/25/2004
    • Steve
      Hi Pramod, I m using a similar approach, but haven t quite yet figured out how to deal with source code branches (for fixes, spikes, etc.). How are you dealing
      Message 2 of 15 , Jul 2, 2004
        Hi Pramod,

        I'm using a similar approach, but haven't quite yet figured out how
        to deal with source code branches (for fixes, spikes, etc.).

        How are you dealing with it? I understand the concept of database
        lineages (from Martin's article)... do you use lineages as units of
        configuration management? How do you keep track of which lineages go
        with which (application) source code files? Is that handled by your
        stored procedure as well?

        Regards

        Steve Tendon

        --- In agileDatabases@yahoogroups.com, Pramod <psadalage@y...> wrote:
        > here are the details, I stripped out the previous
        > msg's for clarity sake.. hopefully the formatting
        > makes sense :)
        >

        <snip>
      • Pramod
        Steve, every code branch has its own set of change files (scripts) because when we branch code our DB folder (which includes all the schema creation scripts,
        Message 3 of 15 , Jul 2, 2004
          Steve,

          every code branch has its own set of change files
          (scripts) because when we branch code our DB folder
          (which includes all the schema creation scripts, data
          population scripts and all the change scripts) is also
          included in the branch, that way the change files go
          along with the code branch.

          we have maintained many branches this way.

          Also the stored procedure does handle different codes
          branches (all you have to do is add code_branch
          checking to the example I sent to some folks, actually
          I stripped it out before sending the example out :( ).
          No we do not use lineage as an unit of configuration
          management since a lineage is made up of
          schema + application setup data + test data.
          There could be n number of lineages within the same
          code branch or even for a given build.

          We always keep track of lineages by their name
          say I10_QA for Iteration 10 QA database or I11_PERF
          for Iteration 11 Performance database or things
          similar to that

          Pramod

          --- Steve <spam.target@...> wrote:
          > Hi Pramod,
          >
          > I'm using a similar approach, but haven't quite yet
          > figured out how
          > to deal with source code branches (for fixes,
          > spikes, etc.).
          >
          > How are you dealing with it? I understand the
          > concept of database
          > lineages (from Martin's article)... do you use
          > lineages as units of
          > configuration management? How do you keep track of
          > which lineages go
          > with which (application) source code files? Is that
          > handled by your
          > stored procedure as well?
          >
          > Regards
          >
          > Steve Tendon
          >
          > --- In agileDatabases@yahoogroups.com, Pramod
          > <psadalage@y...> wrote:
          > > here are the details, I stripped out the previous
          > > msg's for clarity sake.. hopefully the formatting
          > > makes sense :)
          > >
          >
          > <snip>
          >
          >


          =====




          __________________________________
          Do you Yahoo!?
          Take Yahoo! Mail with you! Get it on your mobile phone.
          http://mobile.yahoo.com/maildemo
        • Steve
          According to these scheme, how do you handle merges of different branches - especially with production databases. For instance, if you have a bugfix branch
          Message 4 of 15 , Jul 5, 2004
            According to these scheme, how do you handle merges of different
            branches - especially with production databases. For instance, if you
            have a bugfix branch which you want to merge into the mainline, you
            have to cater for the situation where some customers have a database
            that comes from the branch (they have deployed the bugfix), while
            others have a database that comes from the mainline (they have not
            deployed the bugfix). At the mergepoint you would have to run two
            different sets of scripts to handle the merge.

            Another situation is where some customers are on a customization
            branch, and then that customization goes into the mainline and you
            get a mergepoint. Again you would have two distinct upgrade paths for
            the production databases: one for the customers with the
            customization and one for those without it.

            How do you handle the above scenarios?

            Cheers

            -ST


            --- In agileDatabases@yahoogroups.com, Pramod <psadalage@y...> wrote:
            > Steve,
            >
            > every code branch has its own set of change files
            > (scripts) because when we branch code our DB folder
            > (which includes all the schema creation scripts, data
            > population scripts and all the change scripts) is also
            > included in the branch, that way the change files go
            > along with the code branch.
            >
            <snip>
          • Pramod
            Sorry it took so long to reply, go side tracked with other stuff. Comments below. Thanks Pramod ... We handle this using the revision or release identifier on
            Message 5 of 15 , Jul 14, 2004
              Sorry it took so long to reply, go side tracked with
              other stuff. Comments below.

              Thanks
              Pramod
              --- Steve <spam.target@...> wrote:
              > According to these scheme, how do you handle merges
              > of different
              > branches - especially with production databases. For
              > instance, if you
              > have a bugfix branch which you want to merge into
              > the mainline, you
              > have to cater for the situation where some customers
              > have a database
              > that comes from the branch (they have deployed the
              > bugfix), while
              > others have a database that comes from the mainline
              > (they have not
              > deployed the bugfix). At the mergepoint you would
              > have to run two
              > different sets of scripts to handle the merge.


              We handle this using the revision or release
              identifier on the VersionLog table.
              Every change that is made to the database has to be
              logged and tracked in this
              versionlog table with the release being either the
              BugFix version or the Head Version, there on it does
              not matter what branch of the code base you
              made the change in, since before applying a change you
              can check if the particular change was made to the
              database (production in your case ) or not if
              it was not made then you go ahead and make the change.
              The sticking point is how to merge the base scripts
              (the script that creates all the tables/views/indexes
              etc), I guess it would have to be handled like any
              code merge

              >
              > Another situation is where some customers are on a
              > customization
              > branch, and then that customization goes into the
              > mainline and you
              > get a mergepoint. Again you would have two distinct
              > upgrade paths for
              > the production databases: one for the customers with
              > the
              > customization and one for those without it.

              Again I think this is the same situation as above, as
              long as you are tracking,
              logging and applying database changes for each branch
              then you can easily
              determine if the given change was applied to the
              customized Version or the Main
              branch or not

              >
              > How do you handle the above scenarios?


              =====




              __________________________________
              Do you Yahoo!?
              Yahoo! Mail - You care about security. So do we.
              http://promotions.yahoo.com/new_mail
            Your message has been successfully submitted and would be delivered to recipients shortly.