Kuali Rice Development
  1. Kuali Rice Development
  2. KULRICE-2481

Organize and finish database conversion scripts for 0.9.3 to 1.0

    Details

    • Type: Task Task
    • Status: Closed Closed
    • Priority: Critical Critical
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 1.0
    • Component/s: Database, Documentation
    • Labels:
      None
    • Similar issues:
      KULRICE-3068Create scripts to migrate from old KEW user and workgroup data to KIM for 0.9.3 to 1.0 upgrade
      KULRICE-11530Create test for KimPermissionConverter conversion script
      KULRICE-11115Enhance conversion scripts to connect to Rice database
      KULRICE-9864Add Lookup Conversion to Conversion Script - Part 1
      KULRICE-10124Add Lookup Conversion to Conversion Script - Part 2
      KULRICE-10744Create database scripts for KNS/KRAD conversion
      KULRICE-2174Create coherent database upgrade scripts for 0.9.4 release
      KULRICE-6811Conversion of WorkflowFunctions to EDLFunctions is not present in database upgrade scripts
      KULRICE-11725Finish KIM RoleDao conversion for JPA
      KULRICE-6014JPA Conversion Guide

      Description

      We need to ensure that our database upgrade scripts for 0.9.3 to 1.0 are complete and documented.

      In particular, we need to make sure there is a mysql version of the big database table/column/sequence name refactor.

      We also need to make sure the data in rice094dba is complete and that all old and unused tables have been cleaned out.

      1. KRIM Tables List.txt
        195 kB
        Srikanth Mummadi
      2. refactor.sql
        54 kB
        Srikanth Mummadi
      3. RefactorTablesMysql.groovy
        7 kB
        Srikanth Mummadi

        Issue Links

          Activity

          Hide
          Garey Taylor added a comment -

          I added the final director to the 0.9.3 to 0.9.4 upgrades directory. Inside there I added a README.txt file. This file will contain instructions on how to update from 094 to 1.0.

          Eric, do you want to go with the previous method of running "kcb-upgrade" then "ken-upgrade" or do you want a single upgrade.sql that merges all of those into one file? I'm cool either way, but I did run into problems when trying to run the files separately.

          1. There were a couple errors in the files. Some could be fixed, others were commented out.
          2. The files have to run in a certain order since the table names have changed. Basically, we need to run updates on tables that were made before the refactor and then run updates that happened after. Some of the files have updates for both times, which is bad. kew-upgrade is an example of this. At the beginning of the file it references 093 tables, then a little further into the file it starts referencing 1.0 tables. In order to get around this I broke the file into two. Before and after refactor versions.

          How do you want this handled?

          with the db-updates-during-qa I just combined all the files into one based on chronology.

          Show
          Garey Taylor added a comment - I added the final director to the 0.9.3 to 0.9.4 upgrades directory. Inside there I added a README.txt file. This file will contain instructions on how to update from 094 to 1.0. Eric, do you want to go with the previous method of running "kcb-upgrade" then "ken-upgrade" or do you want a single upgrade.sql that merges all of those into one file? I'm cool either way, but I did run into problems when trying to run the files separately. 1. There were a couple errors in the files. Some could be fixed, others were commented out. 2. The files have to run in a certain order since the table names have changed. Basically, we need to run updates on tables that were made before the refactor and then run updates that happened after. Some of the files have updates for both times, which is bad. kew-upgrade is an example of this. At the beginning of the file it references 093 tables, then a little further into the file it starts referencing 1.0 tables. In order to get around this I broke the file into two. Before and after refactor versions. How do you want this handled? with the db-updates-during-qa I just combined all the files into one based on chronology.
          Hide
          Garey Taylor added a comment -

          On the oracle side we should be just about done with this one. Only thing left out so far has been

          KIM:
          3) One other important piece of KIM is that we need to provide migration from the old workflow-based tables (EN_USR_T, EN_WRKGRP_T, EN_WRKGRP_MBR_T, EN_WRKGRP_TYP_T, etc.) to the new kim tables. There used to be scripts in the project to do this but I'm not sure where they went. We will want to dig them back up and update the.

          Everything is up-to-date and inside the scripts/0.9.3 to 0.9.4/final directlry.
          There's a README file in there that describes the order to run the upgrade scripts.

          Show
          Garey Taylor added a comment - On the oracle side we should be just about done with this one. Only thing left out so far has been KIM: 3) One other important piece of KIM is that we need to provide migration from the old workflow-based tables (EN_USR_T, EN_WRKGRP_T, EN_WRKGRP_MBR_T, EN_WRKGRP_TYP_T, etc.) to the new kim tables. There used to be scripts in the project to do this but I'm not sure where they went. We will want to dig them back up and update the. Everything is up-to-date and inside the scripts/0.9.3 to 0.9.4/final directlry. There's a README file in there that describes the order to run the upgrade scripts.
          Hide
          Garey Taylor added a comment - - edited

          When running

          on MySql I get this error:

          ALTER TABLE KRSB_QRTZ_CRON_TRIGGERS ADD CONSTRAINT KRSB_QRTZ_CRON_TRIGGERS_TR1 FOREIGN KEY (TRIGGER_GROUP) REFERENCES KRSB_QRTZ_TRIGGERS (TRIGGER_GROUP)

          ------------------------
          LATEST FOREIGN KEY ERROR
          ------------------------
          090731 10:52:01 Error in foreign key constraint of table rice093br/#sql-860_13:
          FOREIGN KEY (TRIGGER_GROUP) REFERENCES KRSB_QRTZ_TRIGGERS (TRIGGER_GROUP):
          Cannot resolve table name close to:
          (TRIGGER_GROUP)

          This particular error, and probably a few others are caused by an order of operation error. ie. the sql statements int he refactor sql are in the wrong order. Will try to fix order.

          Show
          Garey Taylor added a comment - - edited When running on MySql I get this error: ALTER TABLE KRSB_QRTZ_CRON_TRIGGERS ADD CONSTRAINT KRSB_QRTZ_CRON_TRIGGERS_TR1 FOREIGN KEY (TRIGGER_GROUP) REFERENCES KRSB_QRTZ_TRIGGERS (TRIGGER_GROUP) ------------------------ LATEST FOREIGN KEY ERROR ------------------------ 090731 10:52:01 Error in foreign key constraint of table rice093br/#sql-860_13: FOREIGN KEY (TRIGGER_GROUP) REFERENCES KRSB_QRTZ_TRIGGERS (TRIGGER_GROUP): Cannot resolve table name close to: (TRIGGER_GROUP) This particular error, and probably a few others are caused by an order of operation error. ie. the sql statements int he refactor sql are in the wrong order. Will try to fix order.
          Hide
          Garey Taylor added a comment -

          Sirkanth,

          The alter statements look pretty good, at least regarding the syntax. In order for me to get a better understanding of what SHOULD* be there I created a MySql table of the 094 db with the impex tool. After doing this I noticed that there were some differences between some of the statements. For example:

          ALTER TABLE KRSB_QRTZ_BLOB_TRIGGERS
          ADD CONSTRAINT KRSB_QRTZ_BLOB_TRIGGERS_TR1
          FOREIGN KEY (TRIGGER_GROUP)
          REFERENCES KRSB_QRTZ_TRIGGERS (TRIGGER_GROUP)

          SHOULD BE:

          ALTER TABLE KRSB_QRTZ_BLOB_TRIGGERS
          ADD CONSTRAINT KRSB_QRTZ_BLOB_TRIGGERS_TR1
          FOREIGN KEY (TRIGGER_NAME, TRIGGER_GROUP)
          REFERENCES KRSB_QRTZ_TRIGGERS (TRIGGER_NAME, TRIGGER_GROUP)
          /

          Also, there are quite a few sequence tables missing from the mysql.ddl file in the 093 code branch. I'm updated the file to add some of these missing sequences. There were also a couple missing tables that I added.

          So, In order to move forward with this, use the impex tool to build a current version of the mysql database. when it does this, a schema.sql and a schema-constraints.sql file will be generated. Use those files as a comparison point.

          Let me know if you have any questions.

          -Garey

          Show
          Garey Taylor added a comment - Sirkanth, The alter statements look pretty good, at least regarding the syntax. In order for me to get a better understanding of what SHOULD* be there I created a MySql table of the 094 db with the impex tool. After doing this I noticed that there were some differences between some of the statements. For example: ALTER TABLE KRSB_QRTZ_BLOB_TRIGGERS ADD CONSTRAINT KRSB_QRTZ_BLOB_TRIGGERS_TR1 FOREIGN KEY (TRIGGER_GROUP) REFERENCES KRSB_QRTZ_TRIGGERS (TRIGGER_GROUP) SHOULD BE: ALTER TABLE KRSB_QRTZ_BLOB_TRIGGERS ADD CONSTRAINT KRSB_QRTZ_BLOB_TRIGGERS_TR1 FOREIGN KEY (TRIGGER_NAME, TRIGGER_GROUP) REFERENCES KRSB_QRTZ_TRIGGERS (TRIGGER_NAME, TRIGGER_GROUP) / Also, there are quite a few sequence tables missing from the mysql.ddl file in the 093 code branch. I'm updated the file to add some of these missing sequences. There were also a couple missing tables that I added. So, In order to move forward with this, use the impex tool to build a current version of the mysql database. when it does this, a schema.sql and a schema-constraints.sql file will be generated. Use those files as a comparison point. Let me know if you have any questions. -Garey
          Hide
          Eric Westfall added a comment -

          Bulk change of all Rice 1.0 issues to closed after public release.

          Show
          Eric Westfall added a comment - Bulk change of all Rice 1.0 issues to closed after public release.

            People

            • Assignee:
              Garey Taylor
              Reporter:
              Eric Westfall
            • Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Time Tracking

                Estimated:
                Original Estimate - 3 weeks
                3w
                Remaining:
                Remaining Estimate - 3 weeks
                3w
                Logged:
                Time Spent - Not Specified
                Not Specified

                  Structure Helper Panel