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

RE: [agileDatabases] Re: Enterprise Configuration Management for DB Scripts/etc?

Expand Messages
  • 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 1 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 2 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 3 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 4 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 5 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 6 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 7 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 8 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.