Full Solution (Draft)

Step 1: Database Structure and Schema Setup

The first step involves dumping the structure of the database or schema to a file, excluding the actual data. This structure is then version-controlled in Git as text files.

To facilitate this process, the utPLSQL Maven plugin is used. This worked seamlessly for me since the dump of the data structure was written in Java. For each table, a set of test data is prepared, ensuring that relationships between tables are maintained and that the data makes sense.

The operations performed (such as inserts or updates) are tested via API calls against the corresponding tables or functions related to the entity. The final step is to implement these operations on a clean database or schema. This allows us to generate the full set of schema components (procedures, packages, etc.) along with tests from the Git repository. Unit tests are then run to verify that the implementation is correct (TRUE/FALSE checks).

Step 2: Writing Unit Tests

At this stage, unit tests are written. A suitable choice for this would be utPLSQL version 3, which I believe is now appropriate for performing unit tests. In previous versions (e.g., version 2), this wasn’t as convincing for me.

If using the utPLSQL library for unit tests doesn’t seem appropriate, consider developing a custom verification method by writing unified tests in PL/SQL. This flexibility allows you to tailor the testing process according to your project’s needs.

Step 3: Documentation and Scalability

To maintain a large database (e.g., 100+ tables) efficiently, detailed documentation of all packages, schemas, and APIs within the database is essential. Ideally, a team would be responsible for this, but if you’re working solo, maintaining and developing this structure will require careful attention to scalability and process organization.

Step 4: Maintenance Workflow

The ongoing maintenance process involves the following workflow:

• Check out the branch for development purposes.
• Insert the schema structure into a clean database.
• Implement any changes in this clean environment using the prepared test data.
• Run tests to ensure coverage, adhering to Test-Driven Development (TDD) principles.
• Review the code through a JIRA or GitHub process, ensuring a mechanism for reverting changes if necessary.
• Commit the final changes, closing the topic and transferring it to the testing environment (such as a pre-production system).
• Finally, the implementation is rolled out in a scheduled release, utilizing release management processes, especially if the environment is distributed.

Additional Comments

For a PL/SQL developer, you might find this resource useful. It could provide additional insights into leveraging the utPLSQL framework effectively in PLSQL Developer.

This approach helps organize the development environment, focusing on code quality and reducing maintenance time post-implementation. In environments where even minor changes can disrupt business processes, this structure ensures that changes are thoroughly tested and documented before release.