internet2/grouper/grouper-ddl

Grouper DDL Change Guide

Every DDL change in Grouper touches multiple files in a predictable pattern. The database compare system (ddlutils) uses Java code to know what the schema should look like, so raw SQL in install files alone is not enough. Follow this checklist so nothing gets missed.

Checklist for Any DDL Change

1. Install SQL files (3 files) - the DDL for fresh installs
2. DDL version class (GrouperDdlX_Y_Z.java) - for database compares / auto-DDL
3. Call from GrouperDdl.java V47 - wire the version class into the compare engine
4. Upgrade task (UpgradeTaskVNN.java) - applies the change to existing databases

(prefer reusing the current release's existing task; one task accumulates a release's changes)

1. Register upgrade task in UpgradeTasks.java enum (only when creating a new task for a new release)
2. AI DDL reference (aiGshDdl.txt) - keep the AI reference in sync
3. Upgrade task UI description - every task needs

upgradeTaskDescription_UpgradeTaskVNN = <what it does> (GRP-####) in grouper/conf/grouperText/grouper.textNg.en.us.base.properties (after the previous VNN line; shown on the upgrade tasks UI page). When adding to an existing task, append to its description and GRP list. That file is CRLF - keep CRLF on the new line.

1. Unit test in grouper/src/test/edu/internet2/middleware/grouper/ddl/GrouperDdlUtilsTest.java:

fresh install -> assert the new schema, deep-check compare has 0 errors/warnings, then simulate the old schema, run UpgradeTasks.VNN.upgradeTask().updateVersionFromPrevious(null), assert the fix, and run it again to prove idempotency. See testGrp7076InstallColumnWidths, testGrp7417FileContentsClobText. Also CRLF.

1. Upgrade tasks wiki page - when a new upgrade task is added, the comment in UpgradeTasks.java

says to update https://spaces.at.internet2.edu/spaces/Grouper/pages/318572008/Grouper+upgrade+tasks (tell the user; do not edit it unprompted)

Release version: DDL ALWAYS bumps the minor version

Any DDL change ships in the NEXT MINOR release, never a patch release. If the latest release is 7.6.1, a DDL change is 7.7.0 (not 7.6.2). Use that version for versionIntroduced() in the upgrade task and for the GrouperDdlX_Y_Z class name when a new one is needed. Find the latest release from the git tags (git tag --sort=-creatordate | head, e.g. GROUPER_RELEASE_7.6.1) or the container Dockerfile ARG GROUPER_VERSION. If the next minor release is already in progress (an upgrade task with that minor's versionIntroduced() exists and is unreleased), reuse it.

File Locations

• Install SQL (postgres): grouper/conf/ddl/GrouperDdl_Grouper_install_postgres.sql
• Install SQL (oracle): grouper/conf/ddl/GrouperDdl_Grouper_install_oracle.sql
• Install SQL (mysql): grouper/conf/ddl/GrouperDdl_Grouper_install_mysql.sql
• DDL version classes: grouper/src/grouper/edu/internet2/middleware/grouper/ddl/GrouperDdlX_Y_Z.java
• GrouperDdl enum: grouper/src/grouper/edu/internet2/middleware/grouper/ddl/GrouperDdl.java
• Upgrade tasks: grouper/src/grouper/edu/internet2/middleware/grouper/app/upgradeTasks/UpgradeTaskVNN.java
• Upgrade tasks enum: grouper/src/grouper/edu/internet2/middleware/grouper/app/upgradeTasks/UpgradeTasks.java
• AI DDL reference: grouper/misc/aiGsh/aiGshDdl.txt

Step 1: Install SQL Files

Add the DDL to all three install SQL files. The syntax is standard SQL and identical across databases for most operations (CREATE TABLE, CREATE INDEX, ALTER TABLE). Key differences:

MySQL differences

• VARCHAR index columns need a prefix length: column_name(100) not just column_name. This also

applies when you widen an already-indexed VARCHAR: a single-column index on VARCHAR(1024) (4096 bytes in utf8mb4) exceeds InnoDB's 3072-byte key limit, so the index must become a prefix index (column_name(255)). In the version-class index def, encode the prefix in the column string ("name(255)"); ddlutils keeps it for mysql and strips it for postgres/oracle (which index the full column). See member_name_idx / group_name_idx for the pattern.

• DROP INDEX syntax: DROP INDEX idx_name ON table_name (postgres/oracle omit ON table_name)
• `MODIFY col VARCHAR(n)` rewrites the whole column definition - it drops NOT NULL unless you

re-specify it (MODIFY col VARCHAR(1024) NOT NULL). Oracle's MODIFY (col VARCHAR2(n)) keeps the existing NOT NULL/NULL and does not need it re-specified.

• BIGINT instead of Oracle's NUMBER(38)
• DATETIME instead of postgres's timestamp or Oracle's DATE

Oracle differences

• Uses NUMBER(38) for big integers instead of BIGINT
• Uses DATE for timestamps
• Uses VARCHAR2 in some places

Finding the right location

Search for the table name in the install SQL to find where to add indexes or columns. For new tables, find a logical neighbor and place nearby.

Widening or altering an existing column (views + indexes)

Changing the type/size of a column that already exists (e.g. widening VARCHAR(255) to VARCHAR(1024)) has two gotchas the simple "add a column" path does not.

1. Views block the ALTER on postgres only

A column referenced by a view behaves differently per database when you ALTER its type:

• postgres - HARD blocks it: cannot alter type of a column used by a view or rule. You must drop

the dependent views first, alter, then recreate them.

• oracle - allows ALTER ... MODIFY (col VARCHAR2(n)); dependent views just go INVALID and

auto-recompile on next use. No drop needed.

• mysql - allows it; views are not type-bound to base columns for a widen. No drop needed.

The DDL upgrade task runs while views still exist (the GrouperDdlEngine drop-all-views / recreate-all pass is a separate cycle), so on postgres your upgrade task must drop the blocking views itself. Precedent: UpgradeTaskV21 does DROP VIEW grouper_sql_cache_mship_v before its ALTER, and never recreates it - GrouperDdlEngine.addViewsAndForeignKeysIfNeeded regenerates all views at the end of the run. To find the exact dependent views on a real postgres DB, query pg_depend/pg_rewrite (join pg_attribute on the column) rather than eyeballing the install SQL.

Two strategies, depending on risk tolerance:

• Auto (V21 style): in the upgrade task, drop each dependent view (guarded by assertTableThere),

run the ALTER, let the engine recreate the views.

• Manual (warn-only) for postgres: when the dependent-view set is large/risky, have the upgrade task

do the ALTER automatically on oracle/mysql, but on postgres only check the width and LOG.error a "MANUAL ACTION REQUIRED" message, then return normally so the upgrade is not blocked - and document the drop-view / alter / recreate-view SQL in the release notes for the DBA. IMPORTANT: when going warn-only, doesUpgradeTaskHaveDdlWorkToDo() must return false on postgres, otherwise with auto-DDL turned off the framework throws "There's DDL work to do that has been configured not to be automatic..." and blocks the whole upgrade. See UpgradeTaskV43 (GRP-7076) for this pattern.

2. Idempotent width checks

Use GrouperDdlUtils.getColumnSize(tableName, columnName) (returns the declared length via JDBC metadata, works across all three DBs) to skip columns already at the target width, so the task is safe to re-run.

3. mysql index surgery when widening past the key limit

If the widened column is indexed and the new size exceeds mysql's InnoDB key limit (see MySQL differences above), the upgrade task must, on mysql: DROP INDEX, MODIFY the column (re-specifying NOT NULL/NULL), then recreate the index as a (255) prefix. Oracle/postgres keep the full-column index.

4. Changing an existing column's type/size: edit the ORIGINAL version class in place

ddlutilsFindOrCreateColumn only sets type/size when it CREATES the column; it never changes an existing one. So for a type or size change, do NOT add a new GrouperDdlX_Y_Z method - edit the column definition where it was originally declared (find it with grep on the column name across ddl/GrouperDdl*.java and GrouperDdl.java), and let the upgrade task alter existing databases. Precedent: GRP-7076 edited GrouperDdl2_3/2_4/2_6_6 in place; GRP-7417 edited GrouperDdl2_5_34. Existing installs are already at V47, so the edited model is used for fresh installs and the database compare only; it does not regenerate DDL against existing tables.

5. postgres TEXT in the ddlutils model = LONGVARCHAR with null size

Grouper's postgres "clob" columns are historically VARCHAR(10000000) (modeled as Types.VARCHAR, "10000000"). For an unbounded postgres TEXT column, model it as Types.LONGVARCHAR with a null size:

if (GrouperDdlUtils.isPostgres()) {
  GrouperDdlUtils.ddlutilsFindOrCreateColumn(table, "my_clob", Types.LONGVARCHAR, null, false, false, null);
}

Why: ddlutils maps LONGVARCHAR (and CLOB) to native TEXT on postgres, and PostgreSqlModelReader reads a TEXT column back as LONGVARCHAR with size null. GrouperDdlCompare warns when the sizes differ, so keeping "10000000" against a TEXT column would report a false size mismatch. Note postgres caps varchar at 10485760, so anything that must hold more than ~10MB has to be TEXT. Oracle stays Types.CLOB and mysql mediumtext (via additional script) - check the existing branches of the column definition and only change the postgres one.

6. Checking the current type in an upgrade task (idempotency)

JDBC metadata is unreliable for postgres TEXT (the driver reports it as VARCHAR with a driver-dependent precision). Ask information_schema instead:

String dataType = new GcDbAccess().sql("select data_type from information_schema.columns "
    + "where table_schema = current_schema() and table_name = ? and column_name = ?")
    .addBindVar("grouper_file").addBindVar("file_contents_clob").select(String.class);
boolean alreadyText = StringUtils.equalsIgnoreCase("text", dataType);

Postgres ALTER COLUMN ... TYPE text from varchar is binary-compatible (no data rewrite), but it is still blocked by dependent views (see 1.) - check pg_depend first. See UpgradeTaskV45 (GRP-7417).

Adding a NOT NULL column to a table that already has rows

A NOT NULL column cannot be added in one step to a table with existing rows (unless it has a default, and Grouper only uses constant defaults like 'T'). Precedents: UpgradeTaskV41 (grouper_sync.internal_id), UpgradeTaskV16 (grouper_members.internal_id), UpgradeTaskV45 (GRP-7439 grouper_file created_on_micros / updated_on_micros).

Fresh install / model: declare the column NOT NULL in all three install SQL files and aiGshDdl.txt, and pass isRequired=true to ddlutilsFindOrCreateColumn in the version class.

Upgrade task, in this order, each step idempotent:

1. Add the column NULLABLE if missing (GrouperDdlUtils.assertColumnThere(true, table, col)).

Oracle: ALTER TABLE t ADD col NUMBER(38); postgres/mysql: ALTER TABLE t ADD COLUMN col BIGINT.

1. Backfill nulls. Use coalesce(col, ?) with where col is null so values already set are kept.
2. If still nullable, make it NOT NULL:

• postgres: ALTER TABLE t ALTER COLUMN col SET NOT NULL
• mysql: ALTER TABLE t MODIFY col BIGINT NOT NULL (MODIFY rewrites the whole definition)
• oracle: ALTER TABLE t MODIFY (col NOT NULL)

Re-run the backfill right before each ALTER: an older Grouper node still running during a rolling upgrade can insert a row without the column between the backfill and the ALTER, which makes the ALTER fail.