# Enhancing Postgres Database Script Management in yCrash

To simplify and strengthen database initialization in yCrash, we are introducing an improved approach for managing PostgreSQL DDL (Data Definition Language) scripts. The main enhancement is the externalization of DDL scripts into a dedicated file named ddl_postgres_ee.txt.

By externalizing database scripts, we achieve the following benefits:

  • Separation of database queries from application code.
  • Simplified version management and release tracking.
  • Easier auditing of schema changes.
  • Improved flexibility for users who wish to manage DDL execution manually.

# 1. Packaging and Default Location

The ddl_postgres_ee.txt file will be packaged with each release. It will be placed in the root directory of the yCrash distribution package. This ensures that the DDL file is consistently available with every release.

# 2. Database Initialization

A new system property -DcreateDBManually is introduced to control how DDL scripts are applied.

a) -DcreateDBManually=false (Default)

The yCrash application will automatically execute the DDL scripts from ddl_postgres_ee.txt. Existing database queries will be checked before applying changes, ensuring that already available objects are not duplicated or altered unnecessarily.

b) -DcreateDBManually=true

The yCrash Application won't run the database scripts automatically. Instead, you (the user or admin) will need to open the ddl_postgres_ee.txt file, check the changes, and run them yourself. This way, you stay in full control of what gets applied to the database, which is useful if you want to review everything carefully for compliance or auditing.

java -Xms2g -Xmx4g ... -DcreateDBManually=true ...
1

# 3. Example DDL Changes

Changes introduced in each release will be annotated with release version name in the DDL file for easy tracking and auditing.

Example:

########## Release 3.0.1 ##########

-- Updated user credentials and permissions

ALTER USER app_user IDENTIFIED BY "NewStrongPassword";

########## Release 3.0.2 ##########

-- Modified USER table schema to add new column 'last_login'

ALTER TABLE USER ADD (last_login TIMESTAMP DEFAULT NULL);

Note

By default -DcreateDBManually this property is false.

# 4. How yCrash Resolves ddl_postgres_ee.text

  • If users configure a custom upload directory (opens new window), the system will first attempt to load ddl_postgres_ee.txt from that location.

  • If the file is not found in the custom directory, the system will fall back to the root directory of the distribution where the yCrash application is started.

# Conclusion

These improvements provide a more robust, user-friendly, and auditable database setup process in yCrash. Whether administrators prefer automated initialization or manual control, the new approach ensures flexibility, transparency, and reliability in managing database schema changes.

← H2 Database →