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

What database files should I keep in the repository?

Expand Messages
  • Diego Moreira da Rosa
    Hi all, I am new to the group, so excuse me for any silly questions. I have just read Fowler and Pramod´s article Evolutionary Database Design at Fowler´s
    Message 1 of 3 , Apr 5, 2007
    • 0 Attachment
      Hi all,

      I am new to the group, so excuse me for any silly questions. I have just read Fowler and Pramod´s article "Evolutionary Database Design" at Fowler´s web site an liked it a lot. However, there is one thing that is not clear to me: what database files you think are better to keep in the repository?

      I can see three main formats to represent a databse. I will give it names to make it easier to explain:

      - Database SQL: one or many files containing all the SQL DDL commands (defining the tables) and SQL DML commands (defining standing data and testing data). This files can be used to build the database from scratch.

      - Database patches: one or many files containing basic DDL commands that build the database as it was a long time ago and many "patch" files containing the SQL commands used to refactor the database. This can also be used to build the database from scratch.

      - Database binaries: the binary representation of the database, usually accessed through a DBMS. It can be a single file (Firebird), multiple files (Derby) or even have no file representation (Oracle).

      - Database backup: usually a more compact one-file representation of the database.

      I would like to know which of this formats you guys think is better to keep in the repository. In the article, it is said to keep the "master database"...

      "As such the master database is kept under configuration management in much the same way as the source code."

      ...sometimes, it mentions the "database refactorings"...

      "Once done, we keep hold of these script files to produce a complete change log of all the alterations done to the database as a result of database refactorings."

      ...it also talks about the "database schema"...

      "Once he's ready to begin he takes a copy of the database master and can modify both the database schema and code freely."

      ...and other times, only "database" is used...

      "Whenever we have a successful build, the database is checked into the configuration management system together with the code."

      I am working on a project that uses a Firebird database and we keep the binaries (the .gdb file) in the repository. Sometime ago we started supporting Oracle and, since Oracle does not have "a file", we are not keeping any Oracle files in our repository. Now I am thinking it would be much better to keep the Database SQL or the Database refactorings, or even both of them. Which do you think is the best strategy?

      Thanks in advance,
      Diego Moreira da Rosa




      __________________________________________________
      Fale com seus amigos de graça com o novo Yahoo! Messenger
      http://br.messenger.yahoo.com/

      [Non-text portions of this message have been removed]
    • Scott Ambler
      It depends. ;-) A few ideas below. - Scott ... Advantages: It s compact, easy to maintain, and easy to view. Disadvantages: I m assuming that this is a few
      Message 2 of 3 , Apr 5, 2007
      • 0 Attachment
        It depends. ;-)

        A few ideas below.

        - Scott
        --- Diego Moreira da Rosa <diegomrosa@...>
        wrote:

        >
        > - Database SQL: one or many files containing all the
        > SQL DDL commands (defining the tables) and SQL DML
        > commands (defining standing data and testing data).
        > This files can be used to build the database from
        > scratch.

        Advantages:
        It's compact, easy to maintain, and easy to view.

        Disadvantages:
        I'm assuming that this is a few big files which create
        the entire DB, written with the assumption that you're
        starting from scratch.

        >
        > - Database patches: one or many files containing
        > basic DDL commands that build the database as it was
        > a long time ago and many "patch" files containing
        > the SQL commands used to refactor the database. This
        > can also be used to build the database from scratch.

        Advantages:
        Small changes to maintain.
        Can start from an existing schema and evolve it from
        there.

        Disadvantages:
        More complex that #1


        >
        > - Database binaries: the binary representation of
        > the database, usually accessed through a DBMS. It
        > can be a single file (Firebird), multiple files
        > (Derby) or even have no file representation
        > (Oracle).

        Advantages:
        Straightforward, brute force approach

        Disadvantages:
        Binaries difficult to work with using other tools
        Not a lot of flexibility


        >
        > - Database backup: usually a more compact one-file
        > representation of the database.


        Advantages:
        Straightforward, brute force approach
        Could be really fast way to put DB in known state

        Disadvantages:
        Backups difficult to work with using other tools
        Not a lot of flexibility


        >
        > I would like to know which of this formats you guys
        > think is better to keep in the repository. In the
        > article, it is said to keep the "master database"...

        I prefer strategy #2, but it requires a bit of
        sophistication on the way that you deploy the db. See
        www.dbdeploy.com

        - Scott

        Scott W. Ambler
        Practice Leader Agile Development, IBM Methods Group
        http://www-306.ibm.com/software/rational/bios/ambler.html

        __________________________________________________
        Do You Yahoo!?
        Tired of spam? Yahoo! Mail has the best spam protection around
        http://mail.yahoo.com
      • malcolmeleach
        Hi Diego, In the case of Oracle, or any other database engine that does not have a convenient underlying file arrangement, a backup of the database would be
        Message 3 of 3 , Apr 6, 2007
        • 0 Attachment
          Hi Diego,

          In the case of Oracle, or any other database engine that does not
          have a convenient underlying file arrangement, a backup of the
          database would be kept under source control instead. Basically all
          you are after here is a point-in-time binary representation of the
          database. This is either the base files or a backup, whichever is
          more convenient.

          Another handy tip (sorry if this is in the original article) is to
          ensure the underlying files/backup file is compressed before adding
          it to source control.

          Kind regards,

          Malcolm Leach
          www.dbghost.com - The original and best Change Management solution
          for SQL Server databases.

          --- In agileDatabases@yahoogroups.com, Diego Moreira da Rosa
          <diegomrosa@...> wrote:
          >
          > Hi all,
          >
          > I am new to the group, so excuse me for any silly questions. I have
          just read Fowler and Pramod´s article "Evolutionary Database Design"
          at Fowler´s web site an liked it a lot. However, there is one thing
          that is not clear to me: what database files you think are better to
          keep in the repository?
          >
          > I can see three main formats to represent a databse. I will give it
          names to make it easier to explain:
          >
          > - Database SQL: one or many files containing all the SQL DDL
          commands (defining the tables) and SQL DML commands (defining
          standing data and testing data). This files can be used to build the
          database from scratch.
          >
          > - Database patches: one or many files containing basic DDL commands
          that build the database as it was a long time ago and many "patch"
          files containing the SQL commands used to refactor the database. This
          can also be used to build the database from scratch.
          >
          > - Database binaries: the binary representation of the database,
          usually accessed through a DBMS. It can be a single file (Firebird),
          multiple files (Derby) or even have no file representation (Oracle).
          >
          > - Database backup: usually a more compact one-file representation
          of the database.
          >
          > I would like to know which of this formats you guys think is better
          to keep in the repository. In the article, it is said to keep
          the "master database"...
          >
          > "As such the master database is kept under configuration management
          in much the same way as the source code."
          >
          > ...sometimes, it mentions the "database refactorings"...
          >
          > "Once done, we keep hold of these script files to produce a
          complete change log of all the alterations done to the database as a
          result of database refactorings."
          >
          > ...it also talks about the "database schema"...
          >
          > "Once he's ready to begin he takes a copy of the database master
          and can modify both the database schema and code freely."
          >
          > ...and other times, only "database" is used...
          >
          > "Whenever we have a successful build, the database is checked into
          the configuration management system together with the code."
          >
          > I am working on a project that uses a Firebird database and we keep
          the binaries (the .gdb file) in the repository. Sometime ago we
          started supporting Oracle and, since Oracle does not have "a file",
          we are not keeping any Oracle files in our repository. Now I am
          thinking it would be much better to keep the Database SQL or the
          Database refactorings, or even both of them. Which do you think is
          the best strategy?
          >
          > Thanks in advance,
          > Diego Moreira da Rosa
          >
          >
          >
          >
          > __________________________________________________
          > Fale com seus amigos de graça com o novo Yahoo! Messenger
          > http://br.messenger.yahoo.com/
          >
          > [Non-text portions of this message have been removed]
          >
        Your message has been successfully submitted and would be delivered to recipients shortly.