6 Replies Latest reply on Feb 13, 2004 3:51 PM by kevinconner

    databse schema update

    triathlon98
    triathlon98 Feb 9, 2004 6:01 AM

      Automatic database schema update is now possible with the new <alter-table>true</alter-table> feature in jbossjdbc-cmp.xml.

      It is assumed that alter-table is used in combination with create-table.
      When checking the table structure, the new tables are being added,
      and fields which did not exist are being added (by comparing with the existing table in the datasource).

      Field which do not exist in the new definition but which existed in the database are being removed. This is somewhat dangerous and assumes that all the fields are represented by the EJB. It would probably be best to make this configurable in some way.

      Tables are not removed as this may cause too big a data loss. However, link tables which are not declared in the schema cause a warning.

      At the field level, for String fields it is checked whether the newly declared length is longer than the current length. If that is the case, the length is increased. There are no other type or precision conversions.

      Basically this should make small changes to the database automatic when deploying, making life easier when upgrading your applications.

      Joachim

      • 6539 Views
      • Tags:
        • 1. Re: database schema update
          triathlon98
          triathlon98 Feb 9, 2004 6:06 AM (in response to triathlon98)

          Heiko Rupp wrote :

          one one side, I really see this as a cool feature during development.
          On the other side, this is very dangerous.

          You e.g. have two columns street and housenumber. Now you delete
          housenumber as you wish to have both fields in one column.
          The data in housenumber is deleted, but the data in street is
          not updated to now include the housenmber. So in the next run
          you might end up looking for lost data.
          Perhaps a fat warning should be printed when columns are removed.

          Answer :

          True, this is a feature which is to be used with caution. However, I do not think it is development only. In may case, I will have many identical systems (not a cluster) at clients, and having to manually update the DB structure can be very time consuming in that case.

          However, as you said, data loss is possible by using alter-table. I tried to prevent it somewhat by not automaticall removing tables or links.

          I could add the warnings you mention, but which warnings do I have to include? Just the warnings which indicate that a field has been removes, or also warn when a string field has been modified?

          Joachim

            • Actions
          • 2. Re: databse schema update
            pilhuhn
            pilhuhn Feb 9, 2004 11:33 AM (in response to triathlon98)

            I'd include both at log level warning, as those events don't happen often, but have a certain severity.

              • Actions
            • 3. Re: databse schema update
              kevinconner
              kevinconner Feb 10, 2004 4:26 PM (in response to triathlon98)

              It is dangerous to do this automatically.

              I modified an earlier version of jboss to prevent the deployment of beans that did not match the db schema in some way. I found this to be a better solution as the majority of the changes made by my team were rarely as straight forward as adding or removing a field.

              The removal of fields usually meant that others had to be updated based on the data and even the simplest of additions usually meant that a default value was to be used.

              Kev

                • Actions
              • 4. Re: databse schema update
                triathlon98
                triathlon98 Feb 13, 2004 3:42 AM (in response to triathlon98)

                 

                "Kevin Conner" wrote:
                It is dangerous to do this automatically.


                Don't worry, it is an option. The update is only done automatically when you turn thison explicitly.

                Joachim

                  • Actions
                • 5. Re: database schema update
                  triathlon98
                  triathlon98 Feb 13, 2004 4:12 AM (in response to triathlon98)

                   

                  "pilhuhn" wrote:
                  I'd include both at log level warning, as those events don't happen often, but have a certain severity.


                  Ok, done.

                  Joachim

                    • Actions
                  • 6. Re: databse schema update
                    kevinconner
                    kevinconner Feb 13, 2004 3:51 PM (in response to triathlon98)

                     

                    "triathlon98" wrote:

                    Don't worry, it is an option. The update is only done automatically when you turn thison explicitly.

                    Joachim


                    I'm aware of this but it is still an option that can be turned on by accident. One of my colleagues recently turned on the 'remove-table' option by accident, actually it was his jalopy formatter that did it, and it resulted in the removal of a few of our tables. Of course we only discovered this when the system was rebooted :-).

                    The point I was trying to make is that it there is often more to the change than just adding or removing a field, it usually involves transforms of some sort. And while adding a field is certainly safer than removing one, it still doesn't handle default values.

                    This is why we changed our version of the code to prevent the deployment, it was telling us that we hadn't performed the correct transform and that the current database schema didn't match the beans.

                    Any chance you can add this to the code base? How about a 'validate-table' option?

                      • Actions