Skip to end of metadata
Go to start of metadata

During development, any database changes should be made against a developers local database until they are ready to push their changes out to rest of the team. At that point the changes should be pushed to the master database so that other developers can update their databases when they pull down the next day's export.

Please review the standards for these data changes, then follow the procedure outlined below.

Standards for data changes

Timing of database changes

  • Changes to the bootstrap data should only be done in a major or minor release
  • Only demo and test data can be changed in patch releases

Naming standards for tables, columns, indices, constraints

Identifiers for new rows in shared tables (such as in KIM) should be prefixed with the application code of "KR".

  • To determine what identifier value to use, cook up some sql similar to this:
    • select max(foo_id) from krim_foo_t where foo_id like 'KR%';
  • Make sure to run this query against the dataset for Rice's trunk , otherwise you could create a bad situation in which the same identifier is used for differing data across the masters.
  • If there are no "KR" prefixed identifiers in the table yet, start off at 'KR1000'.
  • then embed the value directly in your SQL script like so:
    • insert into krim_foo_t (foo_id, ...) values ('KR1001', ...);
  • For background, see KULRICE-7267.

If permissions are added or modified such that they will turn a feature on by default:

  • this change needs to be agreed to by the KTI and KAI
  • it also needs to be noted as an impacting change for the release

If tables are created, the MySQL scripts should specify the character set and collation:

Sample app tables should not be referenced in upgrade scripts.

  • Implementation databases will not have these tables and therefore these statements will produce errors.
  • In order to record these changes, they should still be saved in the same folder as other updates, but prefixed with "demo-", e.g. demo-mysql-2012-08-10.sql and demo-2012-08-10.sql.
  • These scripts will not be rolled up for the release.
  • If you add sample app tables, the appropriate SQL to drop them must be added to db/sql/src/main/resources/bootstrap-server-dataset-cleanup.sql.

Changes should be made in a manner that doesn't break version compatiblity

upgrade scripts should not drop tables which contain non-transient data.

  • as an example of the distinction, the service registry tables hold runtime data that will be recreated at the next Rice server startup. It would be fine to drop and re-create these tables in upgrade scripts. On the other hand, the kim tables hold non-transient data about people, groups, roles, etc. We would not drop these tables in the upgrade scripts, instead we would follow the process outlined below.
  • This type of destructive cleanup operation should be saved in separate scripts in the same folder prefixed with "cleanup-". For example, if the upgrade scripts you created were 2012-08-10.sql and mysql-2012-08-10.sql, then the associated cleanup scripts should be named cleanup-2012-08-10.sql and cleanup-mysql-2012-08-10.sql.
  • These scripts will be rolled in to final cleanup scripts delivered with the release.

Formatting Comments

  • Some clients choke when there is not a space after a line comment character sequence (--):

Procedure (2.5.x and above)

The procedure is the same as 2.4.x, except that the profiles have changed slightly to run the SQL scripts before committing.  If you are on MySQL, you will need to activate an additional profile, "mysql".

cd db/sql
mvn clean install
mvn initialize -Pdb,local,mysql   <-- This runs against MySQL
mvn initialize -Pdb,local,oracle  <-- This runs against Oracle

Procedure (2.4.x)

  1. Make changes to your local database during development.  The changes should follow the standards listed above.
  2. Create alter scripts (in Oracle & MySQL DDL) in the appropriate directory for the version you are working on.
    1. All SQL database changes go in a subfolder of db/sql/src/main/resources/org/kuali/rice/rice-sql/upgrades/<x.x.x>

    2. Oracle changes go in the subfolder oracle and MySQL changes go in the subfolder mysql.
    3. Client-side changes should be put in the subfolder client while the rest of the changes should go into server.  There is no need to duplicate scripts between client and server side, the client scripts are applied to the server database automatically (in other words the server schema and dataset is a superset of the client).
    4. Scripts should be further split between bootstrap, demo, and test.
      1. If the change is needed by all Rice users to use the system, put it in bootstrap.
      2. If the change is just needed by Rice developers to test something in the system, put it in demo.
      3. If the change is needed by all the unit tests, put it in test.
    5. Scripts should be named with the year, month and day and the JIRA number as such: yyyy-mm-dd--KULRICE-0000.sql.
    6. Both types of scripts should use the / delimiter (as previously only used in Oracle).
  3. Create XML ingestion scripts in the appropriate directory for the version you are working on.
    1. All XML database changes go in a subfolder of db/xml/src/main/resources/org/kuali/rice/rice-xml/upgrades/<x.x.x>
    2. Scripts should be further split between bootstrap, demo, and test.
      1. If the change is needed by all Rice users to use the system, put it in bootstrap.
      2. If the change is just needed by Rice developers to test something in the system, put it in demo.
      3. If the change is needed by all the unit tests, put it in test.
    3. Scripts should be named with the same value as the XML document <name>
    4. If there is a parent/child relationship, then the child document should go into a folder named after the parent document so that it is ingested after its parent.  This structure can be as deep as needed.

      demo
      |-- ParentDocument.xml
      |-- ParentDocument 
           |-- ChildDocument.xml
      
  4. Prior to committing any SQL changes reset your local database using the same process CI will use once the SQL has been committed
    1. From the root of a local checkout of the Rice project

      cd db/sql
      mvn clean install
      mvn initialize -Pdb,local         <-- This runs against MySQL
      mvn initialize -Pdb,local,oracle  <-- This runs against Oracle
      


    2. The MySQL process connects as “root” (no password) to jdbc:mysql://localhost” 

      (you can override the these by adding -D parameters/values for mysql.dba.username, mysql.dba.password & mysql.dba.url)

    3. The Oracle process connects as “system/manager” to jdbc:oracle:thin:@localhost:1521:XE” 

      (you can override the these by adding -D parameters/values for oracle.dba.username, oracle.dba.password & oracle.dba.url)

    4. Examine your local database to ensure the changes have been picked up correctly
    5. Perform unit testing / smoke testing of the application as appropriate
  5. Prior to committing any XML changes ingest your documents to make sure they work
  6. Commit your changes.  
  7. Start the CI process rice-<x.x.x>-db-install to produce the export
    1. If it goes red, there is probably a problem with your script.  Check the output and either try to repair it yourself or ask your DM for help.
    2. If it finishes green, then your export is ready to be reviewed on the master-db-install branch of the kuali-devops/rice repository.
  8. Double check that the changes you made are correct in the export. Checkout the branch and run impex locally to build the rice database. (Note that the job changes the OBJ_ID value. That's ok).
  9. Create pull request to merge the master-db-install branch changes into the code base.  Delete the master-db-install branch after the merger.

Procedure (2.3.x and below)

  1. Make changes to your local database during development. The changes should follow the standards listed above.
  2. Create alter scripts (in Oracle & MySQL DDL) in the appropriate directory for the version you are working on.
    • non-KRNS table changes go in scripts/upgrades/<x.x.x to x.x.x>/db-updates
    • KRNS table changes need to go in the above folder, but also in scripts/upgrades/<x.x.x to x.x.x>/db-updates-client
  3. If making a change requires XML ingestion, put those xml files in the appropriate scripts/upgrades/<x.x.x to x.x.x>/xml-ingest-updates directory.
  4. Once your work is complete and any required code has been committed, create a KULRICE jira with a component of "Database" and an appropriate fix version detailing the SQL that needs to be applied to the master databases.
  5. Send an email to rice.dev@kuali.org referencing the JIRA and requesting that the changes be applied to the master databases.
  6. One of the Rice "DBAs" will process your request and apply the change to the master database. See Kuali Rice Database Development for more information on how these changes will be applied.
  7. Your changes will automatically be included with the next daily export.
  8. If the changes you made will immediately affect other developers working on the project, please indicate that in your email to the dev list so that they know they will need to manually apply the changes to their database until they receive the next days export.