Facilitate Adding New Fields to the Database Schema When Entities Are Updated

Issue Title: Facilitate Adding New Fields to the Database Schema When Entities Are Updated

Description:

When new attributes are added to the JPA entities in our application, EclipseLink does not automatically create the corresponding fields in the existing database tables. This leads to discrepancies between the application model and the database schema, causing runtime errors when the application tries to access the new fields.

Objective:

Provide a clear procedure to facilitate adding new fields to the database schema when entities are updated.
Explain how to find the SQL file generated by EclipseLink.
Describe how to change the location where EclipseLink outputs the SQL file.
Offer guidance on generating ALTER TABLE statements from the CREATE TABLE statements to update the existing database schema without losing data.

Steps to Resolve the Issue:

Enable EclipseLink DDL Generation:

Configure EclipseLink to generate DDL scripts without applying them directly to the database. This allows us to review and modify the scripts before execution.

Update persistence.xml:

<?xml version="1.0" encoding="UTF-8"?>
<persistence version="2.2"
   xmlns="http://java.sun.com/xml/ns/persistence"
   xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
   xsi:schemaLocation="
       http://xmlns.jcp.org/xml/ns/persistence
       http://xmlns.jcp.org/xml/ns/persistence/persistence_2_2.xsd">
   <persistence-unit name="yourPU" transaction-type="JTA">
       <provider>org.eclipse.persistence.jpa.PersistenceProvider</provider>
       <jta-data-source>jdbc/yourDataSource</jta-data-source>
       <exclude-unlisted-classes>false</exclude-unlisted-classes>
       <properties>
           <!-- Enable DDL generation -->
           <property name="eclipselink.ddl-generation" value="create-or-extend-tables"/>
           <!-- Output DDL scripts to a file -->
           <property name="eclipselink.ddl-generation.output-mode" value="sql-script"/>
           <!-- Specify the output location for the DDL scripts -->
           <property name="eclipselink.application-location" value="ddl"/>
       </properties>
   </persistence-unit>
</persistence>

Notes:

The eclipselink.application-location property specifies the directory where the DDL scripts will be generated.
The path can be relative (e.g., ddl) or absolute (e.g., /path/to/ddl).
Ensure the application has write permissions to the specified directory.

Find the Generated SQL File:

After running the application (or deploying it to your application server), EclipseLink will generate the DDL scripts in the specified location.
- Default File Names:
  - createDDL.jdbc: Contains the SQL CREATE TABLE statements.
  - dropDDL.jdbc: Contains the SQL DROP TABLE statements.
- Locating the Files:
  - If you set eclipselink.application-location to a relative path like ddl, the files will be generated in a directory named ddl relative to the application's working directory.
  - To find the working directory:
```
System.out.println("Current working directory: " + new java.io.File(".").getAbsolutePath());
```
  - Add this line to your application code to output the current working directory.
  - Check your application logs or console output for the printed path.
  - Example:
  - If the working directory is /opt/app, the scripts will be in /opt/app/ddl/createDDL.jdbc.
Change the Location of the SQL File:

If you need to change where the SQL files are generated, adjust the eclipselink.application-location property in persistence.xml.

Examples:
- Absolute Path:
```
<property name="eclipselink.application-location" value="/var/app/ddl"/>
```
  - Ensure the directory exists and the application has write permissions.
- Relative Path:
```
<property name="eclipselink.application-location" value="sql-scripts"/>
```
  - The scripts will be generated in a directory named sql-scripts relative to the working directory.
- Using System Properties:
```
<property name="eclipselink.application-location" value="${DDL_OUTPUT_DIR}"/>
```
  - Set the DDL_OUTPUT_DIR environment variable or system property to specify the location at runtime.
Generate ALTER TABLE Statements:

Since the database already exists with data, running the CREATE TABLE statements directly would result in errors. To update the existing tables without losing data, we need to generate ALTER TABLE statements to add the new columns.

Procedure:
- Extract the CREATE TABLE Statement:
  - Open the createDDL.jdbc file generated by EclipseLink.
  - Find the CREATE TABLE statement for the entity you updated.
- Convert CREATE TABLE to ALTER TABLE:
  - Use a tool or script to convert the CREATE TABLE statement into individual ALTER TABLE statements.
  - Alternatively, write a custom method to parse the CREATE TABLE statement and generate ALTER TABLE statements for the new columns.
Example Java Method:
```
public String generateAlterStatements(String createTableSql) {
   StringBuilder result = new StringBuilder();
   result.append("SET foreign_key_checks = 0;\n\n");

   // Extract the table name
   String tableName = extractTableName(createTableSql);

   // Extract column definitions
   List<String> columnDefinitions = extractColumnDefinitions(createTableSql);

   // Generate ALTER TABLE statements for columns
   for (String columnDef : columnDefinitions) {
       String alterStatement = generateAlterColumnStatement(tableName, columnDef);
       if (alterStatement != null) {
           result.append(alterStatement).append("\n");
       }
   }

   result.append("\nSET foreign_key_checks = 1;\n");
   return result.toString();
}

// Implement helper methods: extractTableName, extractColumnDefinitions, generateAlterColumnStatement
```
- Notes:
  - This method parses the CREATE TABLE SQL and generates ALTER TABLE ADD COLUMN statements.
  - It disables foreign key checks during the execution to prevent constraint violations.
  - Ensure to re-enable foreign key checks after the alterations.
Execute the ALTER TABLE Statements:
- Option 1: Run Within the Application
  - Implement a method in your application to execute the generated ALTER TABLE statements.
  - Ensure to handle exceptions and provide feedback on the execution status.
  Example Method:
```
public void runSqlToCreateFields(String alterStatements) {
 String[] sqlStatements = alterStatements.split(";\n");
 for (String sql : sqlStatements) {
     sql = sql.trim();
     if (sql.isEmpty()) {
         continue;
     }
     try {
         entityManager.createNativeQuery(sql).executeUpdate();
     } catch (Exception e) {
         // Handle exceptions and log errors
     }
 }
}
```
- Option 2: Run Using an IDE or Database Tool
  - Copy the generated ALTER TABLE statements.
  - Use your database management tool (e.g., MySQL Workbench, pgAdmin) to execute the statements directly on the database.
  - This approach allows you to review and adjust the statements before execution.
Verify the Changes:
- After executing the ALTER TABLE statements, verify that the new columns have been added to the database tables.
- Test the application to ensure it can interact with the new fields without errors.

Additional Information:

EclipseLink Configuration Properties:
- eclipselink.ddl-generation: Controls DDL generation. Options:
- none: No DDL generation.
- create-tables: Creates tables but fails if they exist.
- drop-and-create-tables: Drops existing tables and recreates them.
- create-or-extend-tables: Creates tables or extends them if they exist.
- eclipselink.ddl-generation.output-mode: Controls how DDL scripts are outputted. Options:
- database: Executes DDL against the database.
- sql-script: Writes DDL to scripts.
- both: Executes against the database and writes scripts.
Permissions and Access:
- Ensure the application has the necessary permissions to write to the specified directory and execute DDL statements on the database.
Backup Your Database:
- Always back up your database before making schema changes to prevent data loss in case of errors.
Testing:
- Perform these operations in a development or staging environment before applying them to production.

Conclusion:

By following the steps above, you can facilitate the addition of new fields to the database schema when entities are updated. This process ensures that the database remains in sync with the application model without losing existing data.

Action Items:

Update the persistence.xml configuration to enable DDL script generation.
Locate the generated SQL file and adjust the output location if necessary.
Generate ALTER TABLE statements from the CREATE TABLE statements.
Execute the ALTER TABLE statements to update the database schema.
Verify the changes and test the application.

References:

Please Note:

This issue requires careful handling to prevent data loss or corruption.
Ensure all changes are tested thoroughly before deploying to production.
If you encounter any issues, please provide detailed error messages and logs to assist in troubleshooting.

hmislk / hmis

Facilitate Adding New Fields to the Database Schema When Entities Are Updated #8891