Using Grouper database structure DDL (database definition language)
To run the Grouper database DDL, e.g. if you have an error about mismatched versions. Note, unique indexes and not constraints are added, so ignore foreign key errors when running in Oracle or database that care (for now).
1. configure DDL properties in grouper.properties:
2. run: ant schemaexport
3. Another option is to manually edit the grouper_ddl table to make the versions match (or set it back), or you can set the grouper.properties config param: ddlutils.failIfNotRightVersion to false
New features / requirements:
- The output script name is unique, it will not clobber existing scripts. No need to configure schemaexport dir in build config
- Schema export can init the registry (true by default). No need for a registry init ant task
- DDL manages foreign keys (no need for existing foreign key script or ant task)
- You dont have to add in the subject tables anymore (grouper.properties config option)
- All grouper tables and views must start with "grouper_"
- All subject tables and views must start with "subject"
- No tables/views not managed from grouper can start with "grouper_" or "subject"
- Can more easily unit test with junit (and it is unit tested)
- Supports managing DDL for extensions of apps (e.g. WS or UI). The extension or app needs to register the ddl app name (its a column in the DB) using the LifecycleHook.ddlInit() hook. If the hook is named "Foo" (not Class-like camel case), then make an enum: edu.internet2.middleware.grouper.ddl.FooDdl, and make it implement the interface: DdlVersionable. In fact, just copy the GrouperDdl or SubjectDll and modify it.
- ant schemaexport has always removed user defined indexes and foreign keys (on tables in question e.g. grouper_*, not other tables), and it still does. If you have an index on a grouper table, either make a ddl hook (see above), or manually add this after each schemaexport
The existing ddl management is to describe the schema in hibernate mapping files and use the hibernate tools schemaupdate to change the DB. If there are db specific workarounds we put those in the upgrade instructions, and if there is something drastic, the user can export the db to xml and import into the new schema. The deficiencies are: hibernate mappings arent rich enough (lacking complex indexes, foreign keys, views, etc), exporting large db's is cumbersome, might be difficult to know what scripts to run if db is partway upgraded or skipping versions, hard to know the diff ddl, hard to know if out of sync, etc. Finally it is not possible to mix ddl and sql (e.g. to remove the ID cols if we wanted a script to do this)
The design for the grouper 1.4 ddl management could change to use ddlutils (jakarta package). The concept would be to keep a db table that would hold the current version of the grouper ddl (and any extensions), and on startup of grouper this table will be queried to see if out of date. If so, then the ddl needed to get up to date will be logged as error. This can also be an ant task. Users can manually tweak version numbers to affect the script if they like (e.g. set all to -1 to get a full ddl check, or move to the current version to remove logging of ddl updates if they dont want them). The java version is logged as info, but it will also be displayed when the versions to not match.
Here is an example of a versioned db space . Note that the schema is maintained in Java, and not XML. This is so we can run diagnostics to see if something is there (if column is there, dont add), and can intersperse DDL and SQL. We can make utility methods to make working with ddlutils easier. Here is an example of adding a column in a version:
To update the db to the current ddl, the user will have to manually run the DDL against the DB. The ddlutils ddl is not 100% foolproof, so the user should inspect and make sure it is not dropping tables or whatever.
ddlutils generally does a good job, but sometimes not. If there are things that we want to improve upon (e.g. when adding a non-null column, something maybe we shouldnt do), if there is a default value, it will make a temp table, copy all data to it, drop and recreate the old table, copy data back). If there wasnt a default value, then the table is just dropped. Anyways, to make specific scripts (on specific db platforms), we can put scripts in a certain dir, named a certain thing, and that script will be found and used instead of ddlutils script. This is all or nothing when updating versions, so specific pieces cannot be replaced, only the whole version. So we can make as many versions as we want, and we probably should segment appropriately.
Here is an example generated script (moving to grouper v0, and grouperLoader v1):
Note, if we are going from scratch, it would also be possible to get the one-time script which isnt a create and a bunch of alters... not sure if this is ideal or not since custom scripts would be out the door, but we could make it an option...