DDL is the database table and view structure.
DDL upgrade
| Method | How | Description |
|---|---|---|
| Auto | grouper.hibernate.properties registry.auto.ddl.upToVersion = 2.5.* | Just start the Grouper UI or WS or GSH or the daemon, and Grouper will auto-upgrade the DDL |
| Manual run script | docker exec -u tomcat -it grouper-ui /bin/bash cd /opt/grouper/grouperWebapp/WEB-INF/bin ./gsh.sh -registry -check -runscript | This will generate a script and run it in your database |
| Manual generate script | docker exec -u tomcat -it grouper-ui /bin/bash cd /opt/grouper/grouperWebapp/WEB-INF/bin ./gsh.sh -registry -check | This will generate a script that you can run against your database |
If a DDL script does not complete successfully, you will need to trace through the script and the database to see where it stopped and what needs to be run. This is not as difficult as it seems. e.g. here is a postgres upgrade script for 2.5.39
Database upgrades in v2.5
Verify the DDL version with this query:
select * from grouper_ddl where object_name = 'Grouper'
| Version | Description | DDL version | Verify |
|---|---|---|---|
| v2.5.22 | Add password and sync tables, adjust some views | 32 | This should not have an error select count(1) from grouper_sync |
| v2.5.31 | Add recent membership tables and views, add PIT views, add grouper member columns for USDU | 33 | This should not have an error select distinct subject_resolution_resolvable from grouper_members; select count(1) from grouper_recent_mships_load_v; |
| v2.5.34 | Add config PIT table and grouper_file table | 34 | This should not have an error select count(1) from grouper_file; |
| v2.5.38 | Add provisioning log and sync start columns | 35 | This should not have an error select count(1) from grouper_sync_membership_v; |
| v2.5.40 | Add provisioning error codes, and provisioning membership view | 36 | This should not have an error select error_code from grouper_sync_membership; |
Description
Grouper 2.5+ can handle DDL more efficiently and automatically. DDL can now also change during Grouper build number (previously we kept DDL largely to minor upgrades).
If Grouper is running the DDL automatically, or you run it from gsh manaually, or you run the script in your DB UI tool or whatever, if it fails part-way through, you need to grab the rest of the DDL scripts (from WEB-INF/ddlScripts) and run the rest manually. Grouper will not be able to start where it left off and you need to fix it.
The DDL that Grouper runs will be minimal for the task at hand and not drop and create all views or foreign keys.
On startup Grouper will see if the database is up to date (i.e. Java is a higher DDL version than the database), and if so a static DDL SQL script will either be run automatically or you can run the scripts manually or through GSH. If you are running manually, just start Grouper up (either GSH/daemon/UI/WS/scim/whatever, and it will log which DDL to use. These are the static scripts. There are scripts:
Note: <db> is either postgres (recommended), mysql, oracle, or hsql
| Script | Description |
|---|---|
GrouperDdl_Grouper_createDdlWorker_<db>.sql | Create the new (for 2.5) grouper_ddl_worker table which synchronizes which JVM does the DDL updates when multiple start up at once |
| GrouperDdl_Grouper_install_<db>.sql | Grouper DDL if the DB starts from scratch with no tables |
| GrouperDdl_Subject_install_<db>.sql | Subject tables (for the sample subject source, maybe not needed but thats ok) |
| GrouperDdl_Grouper_30_upgradeTo_31_<db>.sql | Upgrade script from v2.3 to v2.4 |
| GrouperDdl_Grouper_31_upgradeTo_32_<db>.sql | Upgrade script from v2.4 to v2.5 |
Note: if upgrading from Grouper prior to v2.3, you need to upgrade to 2.3 first, then startup a 2.5 container to do the rest.
Check your DDL
If you are upgrading your DDL, you can run this:
./gsh.sh -registry -check
If your container is the same version as your database, you can run (from /opt/grouper/grouperWebapp/WEB-INF/bin
./gsh.sh -registry -check -deep
Note, the script generated by "-deep" is not the one you should run, but you will get a report that will help you figure out issues. Use this report and the full scripts above to try to fix things if your DDL is not correct.
You should see something like this:
SUCCESS: Database DDL is correct! Note: Database version for Grouper: 32 (2.5.0) Note: Java version for Grouper: 32 (2.5.0) Success: Database version is the same as the Java codebase Grouper version Success: Table 'grouper_attr_assign_action': Table is up to date. 7 columns, 1 indexes, 1 foreign keys. Success: Table 'grouper_attr_assign_action_set': Table is up to date. 10 columns, 3 indexes, 3 foreign keys. Success: Table 'grouper_attribute_assign': Table is up to date. 20 columns, 8 indexes, 8 foreign keys. Success: Table 'grouper_attribute_assign_value': Table is up to date. 10 columns, 4 indexes, 1 foreign keys. Success: Table 'grouper_attribute_def': Table is up to date. 28 columns, 3 indexes, 1 foreign keys. Success: Table 'grouper_attribute_def_name': Table is up to date. 13 columns, 2 indexes, 2 foreign keys. Success: Table 'grouper_attribute_def_name_set': Table is up to date. 10 columns, 3 indexes, 3 foreign keys. Success: Table 'grouper_attribute_def_scope': Table is up to date. 9 columns, 1 indexes, 1 foreign keys. Success: Table 'grouper_audit_entry': Table is up to date. 30 columns, 13 indexes, 1 foreign keys. Success: Table 'grouper_audit_type': Table is up to date. 20 columns, 1 indexes, 0 foreign keys. Success: Table 'grouper_change_log_consumer': Table is up to date. 6 columns, 1 indexes, 0 foreign keys. Success: Table 'grouper_change_log_entry': Table is up to date. 16 columns, 15 indexes, 1 foreign keys. Success: Table 'grouper_change_log_entry_temp': Table is up to date. 16 columns, 13 indexes, 0 foreign keys. Success: Table 'grouper_change_log_type': Table is up to date. 19 columns, 1 indexes, 0 foreign keys. Success: Table 'grouper_composites': Table is up to date. 9 columns, 8 indexes, 4 foreign keys. Success: Table 'grouper_config': Table is up to date. 11 columns, 4 indexes, 0 foreign keys. Success: Table 'grouper_ddl': Table is up to date. 5 columns, 1 indexes, 0 foreign keys. Success: Table 'grouper_ddl_worker': Table is up to date. 5 columns, 1 indexes, 0 foreign keys. Success: Table 'grouper_ext_subj': Table is up to date. 16 columns, 2 indexes, 0 foreign keys. Success: Table 'grouper_ext_subj_attr': Table is up to date. 10 columns, 3 indexes, 1 foreign keys. Success: Table 'grouper_fields': Table is up to date. 7 columns, 3 indexes, 0 foreign keys. Success: Table 'grouper_group_set': Table is up to date. 22 columns, 15 indexes, 10 foreign keys. Success: Table 'grouper_groups': Table is up to date. 21 columns, 17 indexes, 3 foreign keys. Success: Table 'grouper_loader_log': Table is up to date. 27 columns, 2 indexes, 0 foreign keys. Success: Table 'grouper_members': Table is up to date. 19 columns, 13 indexes, 0 foreign keys. Success: Table 'grouper_memberships': Table is up to date. 16 columns, 21 indexes, 7 foreign keys. Success: Table 'grouper_message': Table is up to date. 11 columns, 6 indexes, 1 foreign keys. Success: Table 'grouper_password': Table is up to date. 16 columns, 1 indexes, 0 foreign keys. Success: Table 'grouper_password_recently_used': Table is up to date. 4 columns, 0 indexes, 1 foreign keys. Success: Table 'grouper_pit_attr_assn_actn': Table is up to date. 9 columns, 4 indexes, 1 foreign keys. Success: Table 'grouper_pit_attr_assn_actn_set': Table is up to date. 11 columns, 6 indexes, 3 foreign keys. Success: Table 'grouper_pit_attr_assn_value': Table is up to date. 12 columns, 8 indexes, 1 foreign keys. Success: Table 'grouper_pit_attr_def_name': Table is up to date. 10 columns, 6 indexes, 2 foreign keys. Success: Table 'grouper_pit_attr_def_name_set': Table is up to date. 11 columns, 6 indexes, 3 foreign keys. Success: Table 'grouper_pit_attribute_assign': Table is up to date. 17 columns, 12 indexes, 8 foreign keys. Success: Table 'grouper_pit_attribute_def': Table is up to date. 10 columns, 7 indexes, 1 foreign keys. Success: Table 'grouper_pit_fields': Table is up to date. 9 columns, 5 indexes, 0 foreign keys. Success: Table 'grouper_pit_group_set': Table is up to date. 19 columns, 18 indexes, 9 foreign keys. Success: Table 'grouper_pit_groups': Table is up to date. 9 columns, 6 indexes, 1 foreign keys. Success: Table 'grouper_pit_members': Table is up to date. 11 columns, 6 indexes, 0 foreign keys. Success: Table 'grouper_pit_memberships': Table is up to date. 13 columns, 11 indexes, 5 foreign keys. Success: Table 'grouper_pit_role_set': Table is up to date. 11 columns, 6 indexes, 3 foreign keys. Success: Table 'grouper_pit_stems': Table is up to date. 9 columns, 6 indexes, 1 foreign keys. Success: Table 'grouper_qz_blob_triggers': Table is up to date. 4 columns, 0 indexes, 1 foreign keys. Success: Table 'grouper_qz_calendars': Table is up to date. 3 columns, 0 indexes, 0 foreign keys. Success: Table 'grouper_qz_cron_triggers': Table is up to date. 5 columns, 0 indexes, 1 foreign keys. Success: Table 'grouper_qz_fired_triggers': Table is up to date. 13 columns, 6 indexes, 0 foreign keys. Success: Table 'grouper_qz_job_details': Table is up to date. 10 columns, 2 indexes, 0 foreign keys. Success: Table 'grouper_qz_locks': Table is up to date. 2 columns, 0 indexes, 0 foreign keys. Success: Table 'grouper_qz_paused_trigger_grps': Table is up to date. 2 columns, 0 indexes, 0 foreign keys. Success: Table 'grouper_qz_scheduler_state': Table is up to date. 4 columns, 0 indexes, 0 foreign keys. Success: Table 'grouper_qz_simple_triggers': Table is up to date. 6 columns, 0 indexes, 1 foreign keys. Success: Table 'grouper_qz_simprop_triggers': Table is up to date. 14 columns, 0 indexes, 1 foreign keys. Success: Table 'grouper_qz_triggers': Table is up to date. 16 columns, 12 indexes, 1 foreign keys. Success: Table 'grouper_role_set': Table is up to date. 10 columns, 3 indexes, 3 foreign keys. Success: Table 'grouper_stem_set': Table is up to date. 10 columns, 3 indexes, 3 foreign keys. Success: Table 'grouper_stems': Table is up to date. 16 columns, 13 indexes, 3 foreign keys. Success: Table 'grouper_sync': Table is up to date. 12 columns, 2 indexes, 0 foreign keys. Success: Table 'grouper_sync_group': Table is up to date. 23 columns, 8 indexes, 1 foreign keys. Success: Table 'grouper_sync_job': Table is up to date. 13 columns, 1 indexes, 1 foreign keys. Success: Table 'grouper_sync_log': Table is up to date. 11 columns, 2 indexes, 1 foreign keys. Success: Table 'grouper_sync_member': Table is up to date. 24 columns, 9 indexes, 1 foreign keys. Success: Table 'grouper_sync_membership': Table is up to date. 14 columns, 6 indexes, 3 foreign keys. Success: Table 'grouper_table_index': Table is up to date. 6 columns, 1 indexes, 0 foreign keys. Success: View 'grouper_attr_asn_asn_attrdef_v': View is up to date. 23 columns. Success: View 'grouper_attr_asn_asn_efmship_v': View is up to date. 27 columns. Success: View 'grouper_attr_asn_asn_group_v': View is up to date. 24 columns. Success: View 'grouper_attr_asn_asn_member_v': View is up to date. 24 columns. Success: View 'grouper_attr_asn_asn_mship_v': View is up to date. 28 columns. Success: View 'grouper_attr_asn_asn_stem_v': View is up to date. 24 columns. Success: View 'grouper_attr_asn_attrdef_v': View is up to date. 14 columns. Success: View 'grouper_attr_asn_efmship_v': View is up to date. 20 columns. Success: View 'grouper_attr_asn_group_v': View is up to date. 16 columns. Success: View 'grouper_attr_asn_member_v': View is up to date. 16 columns. Success: View 'grouper_attr_asn_mship_v': View is up to date. 20 columns. Success: View 'grouper_attr_asn_stem_v': View is up to date. 15 columns. Success: View 'grouper_attr_assn_action_set_v': View is up to date. 10 columns. Success: View 'grouper_attr_def_name_set_v': View is up to date. 10 columns. Success: View 'grouper_attr_def_priv_v': View is up to date. 12 columns. Success: View 'grouper_audit_entry_v': View is up to date. 45 columns. Success: View 'grouper_aval_asn_asn_attrdef_v': View is up to date. 28 columns. Success: View 'grouper_aval_asn_asn_efmship_v': View is up to date. 32 columns. Success: View 'grouper_aval_asn_asn_group_v': View is up to date. 29 columns. Success: View 'grouper_aval_asn_asn_member_v': View is up to date. 29 columns. Success: View 'grouper_aval_asn_asn_mship_v': View is up to date. 33 columns. Success: View 'grouper_aval_asn_asn_stem_v': View is up to date. 29 columns. Success: View 'grouper_aval_asn_attrdef_v': View is up to date. 19 columns. Success: View 'grouper_aval_asn_efmship_v': View is up to date. 25 columns. Success: View 'grouper_aval_asn_group_v': View is up to date. 21 columns. Success: View 'grouper_aval_asn_member_v': View is up to date. 21 columns. Success: View 'grouper_aval_asn_mship_v': View is up to date. 25 columns. Success: View 'grouper_aval_asn_stem_v': View is up to date. 20 columns. Success: View 'grouper_change_log_entry_v': View is up to date. 30 columns. Success: View 'grouper_composites_v': View is up to date. 15 columns. Success: View 'grouper_ext_subj_invite_v': View is up to date. 13 columns. Success: View 'grouper_ext_subj_v': View is up to date. 7 columns. Success: View 'grouper_groups_v': View is up to date. 24 columns. Success: View 'grouper_memberships_all_v': View is up to date. 24 columns. Success: View 'grouper_memberships_lw_v': View is up to date. 7 columns. Success: View 'grouper_memberships_v': View is up to date. 23 columns. Success: View 'grouper_mship_attrdef_lw_v': View is up to date. 6 columns. Success: View 'grouper_mship_stem_lw_v': View is up to date. 6 columns. Success: View 'grouper_perms_all_v': View is up to date. 27 columns. Success: View 'grouper_perms_assigned_role_v': View is up to date. 20 columns. Success: View 'grouper_perms_role_subject_v': View is up to date. 27 columns. Success: View 'grouper_perms_role_v': View is up to date. 27 columns. Success: View 'grouper_pit_attr_asn_value_v': View is up to date. 18 columns. Success: View 'grouper_pit_memberships_all_v': View is up to date. 19 columns. Success: View 'grouper_pit_perms_all_v': View is up to date. 47 columns. Success: View 'grouper_pit_perms_role_subj_v': View is up to date. 47 columns. Success: View 'grouper_pit_perms_role_v': View is up to date. 47 columns. Success: View 'grouper_role_set_v': View is up to date. 10 columns. Success: View 'grouper_roles_v': View is up to date. 23 columns. Success: View 'grouper_rpt_composites_v': View is up to date. 2 columns. Success: View 'grouper_rpt_group_field_v': View is up to date. 5 columns. Success: View 'grouper_rpt_groups_v': View is up to date. 8 columns. Success: View 'grouper_rpt_members_v': View is up to date. 4 columns. Success: View 'grouper_rpt_roles_v': View is up to date. 7 columns. Success: View 'grouper_rpt_stems_v': View is up to date. 10 columns. Success: View 'grouper_rules_v': View is up to date. 30 columns. Success: View 'grouper_service_role_v': View is up to date. 15 columns. Success: View 'grouper_stem_set_v': View is up to date. 10 columns. Success: View 'grouper_stems_v': View is up to date. 19 columns.
Configuration
You need to add an entry to your grouper.hibernate.properties to identify that the environment should auto-update DDL to any version in 2.5. This is recommended. This will not allow a 2.6 container to automatically change the DDL, but any container in 2.5 that is newer than the DB will update DDL and they are backwards compatible (if you dont update a WS container, but do update the UI, while still in 2.5.*, both will work). Note the database user that you connect as needs to be able to change DDL in the schema that it connects to. If you don't make this setting for whatever reason, you need to manually make DDL changes when new containers require it. We will identify if that is the case on the release notes page for a container. Note: if you don't want auto-DDL, you can change a setting so that Grouper will not log an error reminding you to change this setting. This is not set by default since we do not want to change DDL without you consciously configuring that you are expecting it.
# what version should we auto install DDL up to. You should put the major and minor version here (e.g. 2.5.*). Or you could go to a build number if you like,
# or nothing to not auto DDL. e.g. 2.5.32 or 2.5.*
# {valueType: "string"}
registry.auto.ddl.upToVersion = 2.5.*
Full settings: grouper.hibernate.properties
#######################################
## Initialization settings
#######################################
# what version should we auto install DDL up to. You should put the major and minor version here (e.g. 2.5.*). Or you could go to a build number if you like,
# or nothing to not auto DDL. e.g. 2.5.32 or 2.5.*
# {valueType: "string"}
registry.auto.ddl.upToVersion =
# if you are consciously not doing auto-ddl, set this to true
# {valueType: "boolean", required: true}
registry.auto.ddl.dontRemindMeAboutUpToVersion = false
# its ok if a WS is a little behind on DDL as the UI. If the major and minor version are the same then its not considered a mismatch
# {valueType: "boolean", required: true}
registry.auto.ddl.okIfSameMajorAndMinorVersion = true
Static scripts vs ddlutils
Note: if you are pre v2.3, you need to upgrade to v2.3 or v2.4 before upgrading to v2.5
| Command | Strategy |
|---|---|
| startup | static SQL |
| gsh.sh -registry -check | static SQL |
| gsh.sh -registry -check -runscript | static SQL |
| gsh.sh -registry -check -drop | drop and then static SQL |
| gsh.sh -registry -check -useDdlUtils | ddlUtils |
| ... -deep ... | ddlUtils |
Grouper DDL locking
There is a new table grouper_ddl_worker which will be auto-created (if you are doing auto-DDL): grouper_ddl_worker that JVMs can lock on to do DDL or wait for another JVM to finish doing DDL. This is so multiple JVMs dont attempt this at once.
Grouper DDL versioning
Each DDL version is now correlated with a Grouper version. e.g.
From:
Grouper ddl object type 'Grouper' has dbVersion: 31 and java version: 32
To:
Grouper ddl object type 'Grouper' has dbVersion: 31 (2.4.0) and java version: 32 (2.5.0)
Grouper view updates
| Database | Supports "replace view" | Replace maintains grants | Replace ok with views on views |
|---|---|---|---|
| hsql | no | NA | NA |
| mysql | yes | yes | yes |
| postgres | yes | yes | yes |
| oracle | yes | yes | yes |
Some DDL/SQL to test with
create or replace view whatever as select * from grouper_groups where name like '%'; create view whatever2 as select * from whatever; grant select on whatever to grouper_v2_5a; select * from whatever2; drop view whatever; select * from whatever2;