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

Re: Enterprise Configuration Management for DB Scripts/etc?

Expand Messages
  • Albert Tumanov
    Hi, ... If you have very interrelated database components (if some parts are missing, others probably will not work), you may get better off with a single db
    Message 1 of 13 , Mar 7, 2005
    • 0 Attachment
      Hi,

      > Date: Fri, 25 Feb 2005 11:10:09 -0700
      > From: Chad Woolley <lists@...>
      > Subject: Enterprise Configuration Management for DB Scripts/etc?
      >
      > I am leaning towards having a single common "db" project in version
      > control which will contain ALL db related stuff, but this also presents
      > problem, especially in the area of tagging and continuous integration.

      If you have very interrelated database components
      (if some parts are missing, others probably will not work),
      you may get better off with a single db component.
      On the other side, if you can group database components
      so that each group can be installed on different database,
      then you could also create separate groups in version control
      and benefit from the separation.

      If one database component is used by many applications ("projects"), then
      it's better to treat the database component as a separate application and build
      it separately. But then you must manage the version dependencies somehow (like
      "Project X build 123 requires db component build 456 or later").

      There are actually some related questions that bother me:
      1) should you store CREATE TABLE or ALTER TABLE statements, or both ?
      If you store only CREATE TABLE, then you cannot create build that can be
      installed on existing database.
      2) should you store in version control also deployment-dependent statements,
      like STORAGE, TABLESPACE, ROLES etc ?
      3) should you store reference data in version control ?

      Albert





      __________________________________
      Celebrate Yahoo!'s 10th Birthday!
      Yahoo! Netrospective: 100 Moments of the Web
      http://birthday.yahoo.com/netrospective/
    • Curt Larock
      ... CREATE TABLE and related DDL to create a database should always be stored so developers and QA can easily recreate a given version of the database. For
      Message 2 of 13 , Mar 7, 2005
      • 0 Attachment
        > There are actually some related questions that bother me:
        > 1) should you store CREATE TABLE or ALTER TABLE statements, or both ?
        > If you store only CREATE TABLE, then you cannot create build
        > that can be
        > installed on existing database.
        > 2) should you store in version control also
        > deployment-dependent statements,
        > like STORAGE, TABLESPACE, ROLES etc ?
        > 3) should you store reference data in version control ?
        >
        > Albert
        >
        CREATE TABLE and related DDL to create a database should always be stored
        so developers and QA can easily recreate a given version of the database.
        For ALTER statements what we do is when the development cycle reaches the
        "release candidate" point we include a migration script with ALTER TABLE
        statements in the code provided to QA, and QA tests this against the
        previous
        production release.

        We also include in version control scripts to create an empty database
        instance with tablespaces, roles, etc. This gives us an easy way to create
        databases as needed for development and QA, and is used as a reference by
        production DBAs (the latter usually have their own policies about
        tablespaces
        and the like).

        Reference data that's identical in QA and production and test data are
        versioned separately. This keeps test data from leaking into production.
      • Todd Carrico
        ... From: Curt Larock [mailto:clarock@intelidata.com] ... CREATE TABLE and related DDL to create a database should always be stored so developers and QA can
        Message 3 of 13 , Mar 7, 2005
        • 0 Attachment
          -----Original Message-----
          From: Curt Larock [mailto:clarock@...]
          > There are actually some related questions that bother me:
          > 1) should you store CREATE TABLE or ALTER TABLE statements, or both ?
          > If you store only CREATE TABLE, then you cannot create build
          > that can be
          > installed on existing database.
          > 2) should you store in version control also
          > deployment-dependent statements,
          > like STORAGE, TABLESPACE, ROLES etc ?
          > 3) should you store reference data in version control ?
          >
          > Albert
          >
          CREATE TABLE and related DDL to create a database should always be
          stored
          so developers and QA can easily recreate a given version of the
          database.
          For ALTER statements what we do is when the development cycle reaches
          the
          "release candidate" point we include a migration script with ALTER TABLE
          statements in the code provided to QA, and QA tests this against the
          previous
          production release.

          We also include in version control scripts to create an empty database
          instance with tablespaces, roles, etc. This gives us an easy way to
          create
          databases as needed for development and QA, and is used as a reference
          by
          production DBAs (the latter usually have their own policies about
          tablespaces
          and the like).

          Reference data that's identical in QA and production and test data are
          versioned separately. This keeps test data from leaking into
          production.

          ========================================================================
          ==
          This works for us:
          The create table scripts only create the table if it does not exists.

          After that statement, changes to the table are added to the script as
          alters, but with checks to make sure that the alteration does not exist.

          Index creation blocks test to see if the index exists before creating
          the index again.

          The goal is to be able to execute the script over and over again without
          error, and end up with the correct schema.

          We did not want to drop and create the table each time because of the
          amount of data we run with in Dev and QA. We do however drop and create
          procs, views, functions etc., pretty much everything accept for data is
          dropped and recreated.

          We still have to create migration scripts to migrate data. Those are
          also managed in our source control.

          It is not perfect, but we manage...

          tc
        • Chad Woolley
          Thanks for the responses, sorry it took me a while to revisit this thread. What I ended up doing is moving all db-related stuff into a single project. Given
          Message 4 of 13 , Mar 16, 2005
          • 0 Attachment
            Thanks for the responses, sorry it took me a while to revisit this thread.

            What I ended up doing is moving all db-related stuff into a single
            project. Given the interrelationship between multiple apps and
            databases, I think that's the only manageable choice.

            In my build system (maven), I have set it up so each application "knows"
            (via a property) which databases it uses. This allows a "db:update"
            goal/target to be run from within the app, which will bring each
            database that it uses up to the latest. There are also other targets
            which are specific to our deployment environments: db:updateci
            (continuous integration), db:updatedev (automated dev/smoketest db),
            db:updatestaging (staging db), and db:updateprod (production). Through
            the use of different "master" scripts for each environment which can
            call other scripts (we use Oracle/PLSQL), we can reuse the common script
            logic (like table update/creation), but still have custom script logic
            for different environments (for example, production will have different
            tablespace definitions).

            As I said before, I will manage versioning through CVS modules
            (APPONE_CI appone &db). This means each application, when built by
            Continuous Integration, will actually check out and include all of the
            db project as part of it's build (and tag it). This will mean the CVS
            module for an app will include stuff for unrelated databases, and this
            may not scale as the amount of data in the DB project increases, but
            I'll deal with that when it becomes a problem. I'll also have to ensure
            my tags are unique across all projects so they don't conflict on the db
            project (that currently not the case). If anyone can think of a reason
            why this is a bad idea, please let me know.

            I haven't fully implemented all of this, so I'm sure I'll run into some
            hitches. I'll try to post my progress back here.

            Thanks,
            Chad
          • Todd Carrico
            How are you handling the following: Static data:: i.e. look up tables Transactional data:: i.e. data generated by end users Those two seem to be the trouble
            Message 5 of 13 , Mar 16, 2005
            • 0 Attachment
              How are you handling the following:
              Static data:: i.e. look up tables
              Transactional data:: i.e. data generated by end users

              Those two seem to be the trouble spots that I run into. Procedures,
              views, tables, functions, (packages in your case) all lend themselves
              well to your plan, but data is it's own beast.

              namespace Match.com
              {
              public class ToddCarrico: TechnicalArchitect, LeadDatabaseEngineer
              {
              public string BlogURL =
              "http://sqljunkies.com/WebLog/tcarrico/";
              }
              }

              "A man's worth is no greater than the worth of his ambitions." - Marcus
              Aurelius


              > -----Original Message-----
              > From: Chad Woolley [mailto:lists@...]
              > Sent: Wednesday, March 16, 2005 6:32 AM
              > To: agileDatabases@yahoogroups.com
              > Subject: Re: [agileDatabases] Enterprise Configuration Management for
              DB
              > Scripts/etc?
              >
              >
              > Thanks for the responses, sorry it took me a while to revisit this
              thread.
              >
              > What I ended up doing is moving all db-related stuff into a single
              > project. Given the interrelationship between multiple apps and
              > databases, I think that's the only manageable choice.
              >
              > In my build system (maven), I have set it up so each application
              "knows"
              > (via a property) which databases it uses. This allows a "db:update"
              > goal/target to be run from within the app, which will bring each
              > database that it uses up to the latest. There are also other targets
              > which are specific to our deployment environments: db:updateci
              > (continuous integration), db:updatedev (automated dev/smoketest db),
              > db:updatestaging (staging db), and db:updateprod (production).
              Through
              > the use of different "master" scripts for each environment which can
              > call other scripts (we use Oracle/PLSQL), we can reuse the common
              script
              > logic (like table update/creation), but still have custom script logic
              > for different environments (for example, production will have
              different
              > tablespace definitions).
              >
              > As I said before, I will manage versioning through CVS modules
              > (APPONE_CI appone &db). This means each application, when built by
              > Continuous Integration, will actually check out and include all of the
              > db project as part of it's build (and tag it). This will mean the CVS
              > module for an app will include stuff for unrelated databases, and this
              > may not scale as the amount of data in the DB project increases, but
              > I'll deal with that when it becomes a problem. I'll also have to
              ensure
              > my tags are unique across all projects so they don't conflict on the
              db
              > project (that currently not the case). If anyone can think of a
              reason
              > why this is a bad idea, please let me know.
              >
              > I haven't fully implemented all of this, so I'm sure I'll run into
              some
              > hitches. I'll try to post my progress back here.
              >
              > Thanks,
              > Chad
              >
            • Chad Woolley
              Well, currently my implementation is in it s infancy, so I just have the static data in scripts. As for transactional data, I think you mean data that is
              Message 6 of 13 , Mar 16, 2005
              • 0 Attachment
                Well, currently my implementation is in it's infancy, so I just have the
                static data in scripts.

                As for "transactional" data, I think you mean data that is already in
                the database and was created by users, which you want to preserve when
                you update the schema. To handle this, I am planning on implementing
                the "database refactoring" approaches that Pramod and Scott have written
                about, which allow you to change the database structure while preserving
                the data. Is this what you mean?

                -- Chad

                Todd Carrico wrote:

                >How are you handling the following:
                >Static data:: i.e. look up tables
                >Transactional data:: i.e. data generated by end users
                >
                >Those two seem to be the trouble spots that I run into. Procedures,
                >views, tables, functions, (packages in your case) all lend themselves
                >well to your plan, but data is it's own beast.
                >
                >
                >
              • Todd Carrico
                Yup. Sounds like you are on track. Or at least we are on the same track ;) tc ... DB ... the ... written ... preserving
                Message 7 of 13 , Mar 16, 2005
                • 0 Attachment
                  Yup.

                  Sounds like you are on track. Or at least we are on the same track ;)

                  tc


                  > -----Original Message-----
                  > From: Chad Woolley [mailto:lists@...]
                  > Sent: Wednesday, March 16, 2005 7:10 AM
                  > To: agileDatabases@yahoogroups.com
                  > Subject: Re: [agileDatabases] Enterprise Configuration Management for
                  DB
                  > Scripts/etc?
                  >
                  >
                  > Well, currently my implementation is in it's infancy, so I just have
                  the
                  > static data in scripts.
                  >
                  > As for "transactional" data, I think you mean data that is already in
                  > the database and was created by users, which you want to preserve when
                  > you update the schema. To handle this, I am planning on implementing
                  > the "database refactoring" approaches that Pramod and Scott have
                  written
                  > about, which allow you to change the database structure while
                  preserving
                  > the data. Is this what you mean?
                  >
                  > -- Chad
                  >
                  > Todd Carrico wrote:
                  >
                  > >How are you handling the following:
                  > >Static data:: i.e. look up tables
                  > >Transactional data:: i.e. data generated by end users
                  > >
                  > >Those two seem to be the trouble spots that I run into. Procedures,
                  > >views, tables, functions, (packages in your case) all lend themselves
                  > >well to your plan, but data is it's own beast.
                  > >
                  > >
                  > >
                  >
                  >
                  >
                  >
                  >
                  > Yahoo! Groups Links
                  >
                  >
                  >
                  >
                  >
                  >
                • tpbrown2001
                  Chad, Did you write a new Maven plugin, or use Ant scripts? Care to share them? (e.g. - the db:update goals, etc.) Thanks, Tim ... thread. ... knows ...
                  Message 8 of 13 , Mar 17, 2005
                  • 0 Attachment
                    Chad,

                    Did you write a new Maven plugin, or use Ant scripts? Care to share
                    them? (e.g. - the db:update goals, etc.)

                    Thanks,

                    Tim

                    --- In agileDatabases@yahoogroups.com, Chad Woolley <lists@t...> wrote:
                    > Thanks for the responses, sorry it took me a while to revisit this
                    thread.
                    >
                    > What I ended up doing is moving all db-related stuff into a single
                    > project. Given the interrelationship between multiple apps and
                    > databases, I think that's the only manageable choice.
                    >
                    > In my build system (maven), I have set it up so each application
                    "knows"
                    > (via a property) which databases it uses. This allows a "db:update"
                    > goal/target to be run from within the app, which will bring each
                    > database that it uses up to the latest. There are also other targets
                    > which are specific to our deployment environments: db:updateci
                    > (continuous integration), db:updatedev (automated dev/smoketest db),
                    > db:updatestaging (staging db), and db:updateprod (production). Through
                    > the use of different "master" scripts for each environment which can
                    > call other scripts (we use Oracle/PLSQL), we can reuse the common
                    script
                    > logic (like table update/creation), but still have custom script logic
                    > for different environments (for example, production will have different
                    > tablespace definitions).
                    >
                    > As I said before, I will manage versioning through CVS modules
                    > (APPONE_CI appone &db). This means each application, when built by
                    > Continuous Integration, will actually check out and include all of the
                    > db project as part of it's build (and tag it). This will mean the CVS
                    > module for an app will include stuff for unrelated databases, and this
                    > may not scale as the amount of data in the DB project increases, but
                    > I'll deal with that when it becomes a problem. I'll also have to ensure
                    > my tags are unique across all projects so they don't conflict on the db
                    > project (that currently not the case). If anyone can think of a reason
                    > why this is a bad idea, please let me know.
                    >
                    > I haven't fully implemented all of this, so I'm sure I'll run into some
                    > hitches. I'll try to post my progress back here.
                    >
                    > Thanks,
                    > Chad
                  • Chad Woolley
                    They are just maven (Jelly). I could write them in Ant, but I didn t. In my maven environment, I have a master project (with no code) that everything
                    Message 9 of 13 , Mar 20, 2005
                    • 0 Attachment
                      They are just maven (Jelly). I could write them in Ant, but I didn't.
                      In my maven environment, I have a "master" project (with no code) that
                      everything inherits from. The scripts are just goals in the master
                      script for now, and make assumptions that the projects and db project
                      are all peers under a common workspace. When I have enough goals, I'll
                      make a plugin, but that would need to be fancier (defining project
                      locations and such), so for now they are just plain goals.

                      My next major task is to port the NANT/SQLUnit scripts that someone
                      posted here to Maven/Oracle (my environment), and then might be a good
                      time to think about a formal plugin. Someone with more time or
                      grandiose ambitions than I might even extend it for other databases.
                      For now, I can probably post the plain update goals I have, but it may
                      be later.

                      However, if someone wanted to help, I might whip up a plugin faster with
                      just what I have :). The plugins project on SF would probably host it,
                      unless there is somewhere better...

                      -- Chad

                      tpbrown2001 wrote:

                      >
                      >Chad,
                      >
                      >Did you write a new Maven plugin, or use Ant scripts? Care to share
                      >them? (e.g. - the db:update goals, etc.)
                      >
                      >Thanks,
                      >
                      >Tim
                      >
                      >
                      >
                    Your message has been successfully submitted and would be delivered to recipients shortly.