generate-ddl - SKILL.md Agent Skill

name: generate-ddl description: > Generate a full-schema DDL script (CREATE TABLE + ALTER TABLE ADD COLUMN) into the project's tmp/ folder by temporarily enabling EclipseLink DDL generation, rebuilding, and redeploying the local app. Reverts all source changes and restores the original deployment afterward. Use when asked to generate/regenerate the DDL for the Database-Schema-DDL-Generation-Guide wiki page, or to produce a schema-sync script for a fresh/behind database. disable-model-invocation: true allowed-tools: Bash, Read, Edit

Generate Full-Schema DDL Script

Produces a self-contained SQL script (new tables + missing columns on existing tables) at <project-root>/tmp/createDDL.jdbc, then leaves the working tree and the deployed app exactly as they were before this skill ran.

This is a temporary, local-only procedure — none of the intermediate edits are ever committed.

How it works

Two files need a temporary edit, and both must point at the same absolute path, computed fresh every run (it differs per developer machine):

PROJECT_ROOT="$(git rev-parse --show-toplevel)"
DDL_DIR="$PROJECT_ROOT/tmp"

src/main/resources/META-INF/persistence.xml — EclipseLink only emits CREATE TABLE + FK ALTER TABLE ADD CONSTRAINT statements in sql-script mode; it does not diff against a live DB.
src/main/java/com/divudi/service/DdlFileEnhancerService.java — a @Startup @Singleton EJB that runs once at app start, reads the generated createDDL.jdbc, and appends an ALTER TABLE ... ADD COLUMN ... for every column of every CREATE TABLE it finds (closing the gap above). Its output directory is a hardcoded compile-time constant (APPLICATION_LOCATION), so it must be edited and rebuilt — there is no runtime config for it today.

Steps

1. Back up the two files before touching them

Use a project tmp subfolder for backups (never the system /tmp):

mkdir -p "$DDL_DIR/.generate-ddl-backup"
cp src/main/resources/META-INF/persistence.xml "$DDL_DIR/.generate-ddl-backup/persistence.xml.bak"
cp src/main/java/com/divudi/service/DdlFileEnhancerService.java "$DDL_DIR/.generate-ddl-backup/DdlFileEnhancerService.java.bak"

This captures whatever local state was already there (e.g. a local JNDI swap in persistence.xml per verify-persistence) so it can be restored byte-for-byte, not just reset to git HEAD.

2. Add DDL-generation properties to persistence.xml

Inside the <properties> block of both hmisPU and hmisAuditPU, add:

<property name="eclipselink.ddl-generation" value="create-or-extend-tables"/>
<property name="eclipselink.ddl-generation.output-mode" value="sql-script"/>
<property name="eclipselink.application-location" value="$DDL_DIR"/>

(Substitute the actual computed $DDL_DIR value — XML doesn't expand shell variables.) Use the Edit tool with the existing closing </properties>/last <property> line as anchor, same as the existing eclipselink.jdbc.result-set-access-optimization property block.

3. Point DdlFileEnhancerService at the same directory

Edit the constant:

private static final String APPLICATION_LOCATION = "$DDL_DIR";

(Again, substitute the real computed path — this is a Java string literal, not a shell expansion.)

4. Rebuild and redeploy

mvn -q package -DskipTests

Find the built WAR and force-deploy it (this also triggers the singleton's @PostConstruct, which is what runs the column-enhancement step):

WAR=$(ls target/*.war | head -1)
/home/buddhika/payara/bin/asadmin redeploy --name rh "$WAR"

Use the explicit app name rh — the same name dev-issue and playwright-e2e redeploy under. Omitting --name lets asadmin derive the app name from the WAR filename (e.g. rh-3.0.0) instead of redeploying the existing rh app, which can leave two separate apps competing for the same hardcoded /rh context root (glassfish-web.xml).

If deploy fails with a JNDI lookup error for a datasource (e.g. jdbc/ruhunuAudit not found): this is a pre-existing local-environment mismatch unrelated to DDL generation — do NOT silently "fix" it as part of this skill. Run /home/buddhika/payara/bin/asadmin list-jdbc-resources to see what's actually registered, report the mismatch to the user, and ask before changing <jta-data-source> (that line may already be a deliberate uncommitted local override).

5. Verify the output

test -f "$DDL_DIR/createDDL.jdbc" && echo FOUND
grep -c "^CREATE TABLE" "$DDL_DIR/createDDL.jdbc"
grep -c "ADD COLUMN" "$DDL_DIR/createDDL.jdbc"

Both counts should be in the hundreds/thousands, not zero. If ADD COLUMN count is 0, the enhancer didn't run — check the deployed app actually restarted (a --force=true redeploy always re-triggers @PostConstruct; if it didn't, the JNDI/deploy step above likely failed).

6. Restore both files exactly

cp "$DDL_DIR/.generate-ddl-backup/persistence.xml.bak" src/main/resources/META-INF/persistence.xml
cp "$DDL_DIR/.generate-ddl-backup/DdlFileEnhancerService.java.bak" src/main/java/com/divudi/service/DdlFileEnhancerService.java
rm -rf "$DDL_DIR/.generate-ddl-backup"

Confirm with git diff that the only remaining diff (if any) is whatever pre-existing local-only change was already there before step 1 — never more.

7. Rebuild and redeploy again to restore the running app

mvn -q package -DskipTests
/home/buddhika/payara/bin/asadmin redeploy --name rh "$(ls target/*.war | head -1)"

If this redeploy fails for the same pre-existing JNDI reason noted in step 4, say so explicitly — don't leave the user thinking the app is back to a known-good state when it isn't. The previous (DDL-generation-enabled) deployment will keep running in that case until the underlying datasource issue is fixed.

8. Publish the DDL to the wiki

Write the updated wiki page. The wiki lives in the sibling ../hmis.wiki repo. If that directory does not exist, print a warning and skip steps 8–9 (the DDL file is still useful locally).

WIKI_DIR="$(git rev-parse --show-toplevel)/../hmis.wiki"
WIKI_FILE="$WIKI_DIR/Database-Schema-DDL-Generation-Guide.md"
UPDATE_TS="$(date '+%Y.%m.%d %H.%M')"
AUTHOR="$(git config user.name | awk '{print $NF}')"   # last name only

if [ ! -d "$WIKI_DIR" ]; then
  echo "WARNING: wiki repo not found at $WIKI_DIR — skipping wiki publish"
else
  # Write header + fresh DDL wrapped in a sql fence
  cat > "$WIKI_FILE" << WIKI_HEADER
This page explains how to generate and apply the full database schema for the
application, including all missing tables and fields. This is especially
useful when setting up a fresh instance of the application or restoring a
database structure.

## Steps to Generate the DDL File

1. Locate the \`persistence.xml\` file in your project you use for development.
2. Replace its contents with the configuration from \`persistence_for_database_generation_script.xml\`.
3. Adjust the values in that file, especially the location where the DDL file should be generated on your computer.
4. Run the application once. This will generate the full database schema as a DDL script in the specified file location.
5. Open the generated DDL file and copy its contents.
6. In the application where you want to update the database, go to **Menu > Administration > Manage Metadata > Add Missing Fields**, paste the copied DDL content into the provided text area, and click the **Update Database** button.
7. The contents of the latest version of the ddl file is given below so that you need not to generate it yourself.

## Last Update - $UPDATE_TS - ($AUTHOR)

## Full DDL File Contents

\`\`\`sql
WIKI_HEADER
  cat "$DDL_DIR/createDDL.jdbc" >> "$WIKI_FILE"
  printf '\n```\n' >> "$WIKI_FILE"
  echo "Wiki page written: $WIKI_FILE"
fi

9. Commit and push the wiki

if [ -d "$WIKI_DIR" ]; then
  cd "$WIKI_DIR"
  # Pull in any concurrent wiki edits; if this file conflicts, keep ours
  git stash --include-untracked
  git pull --rebase origin master || true
  git stash pop || true
  # Re-apply our version of the DDL page (in case of rebase conflict)
  git checkout --theirs Database-Schema-DDL-Generation-Guide.md 2>/dev/null || true
  git add Database-Schema-DDL-Generation-Guide.md
  git rebase --continue 2>/dev/null || true
  # Commit (skip if nothing staged, e.g. rebase already applied it)
  git diff --cached --quiet || git commit -m "docs(wiki): update DDL generation guide with $(date '+%Y-%m-%d') schema"
  git push origin master
  cd -
fi

If the push is rejected again (another concurrent push), re-run the pull --rebase + push cycle once more by hand — two concurrent DDL regenerations are rare enough that a single retry is sufficient.

Notes

Never commit persistence.xml or DdlFileEnhancerService.java with the DDL-generation edits in place — they are local-machine-specific (hardcoded absolute paths) and would break CI/CD and other developers' checkouts. verify-persistence already checks for stray eclipselink.application-location hardcoded paths before push.
The two persistence units writing to the same file/directory rely on EclipseLink appending rather than truncating — this matches the previously-documented working DDL (which includes both regular and audit tables, e.g. AUDITEVENT).