Child pages
  • DDL in Grouper v2.5+
Skip to end of metadata
Go to start of metadata


DDL is the database table and view structure. 

DDL upgrade

MethodHowDescription
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

Database upgrades in v2.5

Verify the DDL version with this query:

select * from grouper_ddl where object_name = 'Grouper'


VersionDescriptionDDL versionVerify
v2.5.22Add password and sync tables, adjust some views32

This should not have an error

select count(1) from grouper_sync
v2.5.31Add 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;

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

ScriptDescription

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>.sqlGrouper DDL if the DB starts from scratch with no tables
GrouperDdl_Subject_install_<db>.sqlSubject tables (for the sample subject source, maybe not needed but thats ok)
GrouperDdl_Grouper_30_upgradeTo_31_<db>.sqlUpgrade script from v2.3 to v2.4
GrouperDdl_Grouper_31_upgradeTo_32_<db>.sqlUpgrade 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

CommandStrategy
startupstatic SQL
gsh.sh -registry -checkstatic SQL
gsh.sh -registry -check -runscriptstatic SQL
gsh.sh -registry -check -dropdrop and then
static SQL
gsh.sh -registry -check -useDdlUtilsddlUtils
...  -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

DatabaseSupports "replace view"Replace maintains grantsReplace ok with views on views
hsqlnoNANA
mysqlyesyesyes
postgresyesyesyes
oracleyesyesyes

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;
  • No labels