Jetspeed 2
  1. Jetspeed 2
  2. JS2-1024

mssql create-schema.sql is incorrect

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 2.2.0
    • Fix Version/s: 2.2.1
    • Component/s: Persistence and DAO
    • Labels:
      None

      Description

      The mssql create-schema.sql contains old tables less tables then in derbydb create-schema.sql.

      for example: table SECURITY_DOMAIN is missing

      1. create-schema.sql
        50 kB
        Frank Otto
      2. drop-schema.sql
        22 kB
        Frank Otto

        Activity

        Hide
        Frank Otto added a comment -

        the correct sql scripts for mssql

        Show
        Frank Otto added a comment - the correct sql scripts for mssql
        Hide
        Ate Douma added a comment -

        Hi Frank,

        Thanks for the patches, but I'm afraid they are not yet good enough to incorporate in the jetspeed src.
        I see a lot of changes on column data types (e.g. INT -> INTEGER) and adjustments for the changed table definitions, which seems fine to me.

        But, what has gone missing (including possible required adjustments) is all the required mssql trigger code.
        For mssql, as it doesn't properly support (standard) cascade deletes for hierarchical references, there were are several tables which has additional triggers to deal with that functionality (e.g. FOLDER, FOLDER_MENU, FRAGMENT, PAGE_MENU).
        In your environment you might not be yet using database stored psml, but to be able to do so, these additional triggers are required to handle hierarchical references on delete.
        And the same and even more so applies to the drop-schema.sql!
        You might also might want to review the issues JS2-812 and JS2-838 which specifically dealt with these problems and were the basis for the current (but now outdated) hand crafted mssql schema sql.

        Show
        Ate Douma added a comment - Hi Frank, Thanks for the patches, but I'm afraid they are not yet good enough to incorporate in the jetspeed src. I see a lot of changes on column data types (e.g. INT -> INTEGER) and adjustments for the changed table definitions, which seems fine to me. But, what has gone missing (including possible required adjustments) is all the required mssql trigger code. For mssql, as it doesn't properly support (standard) cascade deletes for hierarchical references, there were are several tables which has additional triggers to deal with that functionality (e.g. FOLDER, FOLDER_MENU, FRAGMENT, PAGE_MENU). In your environment you might not be yet using database stored psml, but to be able to do so, these additional triggers are required to handle hierarchical references on delete. And the same and even more so applies to the drop-schema.sql! You might also might want to review the issues JS2-812 and JS2-838 which specifically dealt with these problems and were the basis for the current (but now outdated) hand crafted mssql schema sql.
        Hide
        Frank Otto added a comment -

        I have included the triggers for the tables, which have no cascade delete on mssql.

        I think, there are "only" this tables: FOLDER, FOLDER_MENU, FRAGMENT, PAGE_MENU?

        Show
        Frank Otto added a comment - I have included the triggers for the tables, which have no cascade delete on mssql. I think, there are "only" this tables: FOLDER, FOLDER_MENU, FRAGMENT, PAGE_MENU?
        Hide
        Ate Douma added a comment -

        I've finally found the time (and need ) to pickup this issue.
        The patches were provided by Frank Otto, but I had to adjust them a little for recent database model chances.
        Tested on MsSQL 2005, seems to be working well.
        Thanks Frank for providing the patches!

        Show
        Ate Douma added a comment - I've finally found the time (and need ) to pickup this issue. The patches were provided by Frank Otto, but I had to adjust them a little for recent database model chances. Tested on MsSQL 2005, seems to be working well. Thanks Frank for providing the patches!

          People

          • Assignee:
            Ate Douma
            Reporter:
            Frank Otto
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:

              Development