Clean Up Unused Columns in Form Data Table

During development, fields are added and removed from form designs, which can lead to the accumulation of unused columns in the database table.

To address this issue, we need to organize the table structure and remove unused columns. In this exercise, we will focus on the table named sample. In Joget's database, this table is identified as app_fd_sample.

Understand the table structure

To better understand which columns should be retained and which ones can be removed, it's helpful to examine the current structure of the table. To obtain this information, execute the following SQL query.

mysql> describe app_fd_sample;
+------------------------+--------------+------+-----+---------+-------+
| Field                  | Type         | Null | Key | Default | Extra |
+------------------------+--------------+------+-----+---------+-------+
| id                     | varchar(255) | NO   | PRI | NULL    |       |
| dateCreated            | datetime     | YES  |     | NULL    |       |
| dateModified           | datetime     | YES  |     | NULL    |       |
| c_attachment           | longtext     | YES  |     | NULL    |       |
| c_title                | longtext     | YES  |     | NULL    |       |
| createdBy              | varchar(255) | YES  |     | NULL    |       |
| createdByName          | varchar(255) | YES  |     | NULL    |       |
| modifiedBy             | varchar(255) | YES  |     | NULL    |       |
| modifiedByName         | varchar(255) | YES  |     | NULL    |       |
| c_description          | longtext     | YES  |     | NULL    |       |
| c_approver_signature   | longtext     | YES  |     | NULL    |       |
| c_approver_2_signature | longtext     | YES  |     | NULL    |       |
| c_status               | longtext     | YES  |     | NULL    |       |
| c_field1               | longtext     | YES  |     | NULL    |       |
| c_address              | longtext     | YES  |     | NULL    |       |
| c_contact              | longtext     | YES  |     | NULL    |       |
| c_name                 | longtext     | YES  |     | NULL    |       |
| c_approval_status      | longtext     | YES  |     | NULL    |       |
| c_last_approve         | longtext     | YES  |     | NULL    |       |
| c_field2               | longtext     | YES  |     | NULL    |       |
| c_desc                 | longtext     | YES  |     | NULL    |       |
| c_field3               | longtext     | YES  |     | NULL    |       |
| c_city                 | longtext     | YES  |     | NULL    |       |
| c_state                | longtext     | YES  |     | NULL    |       |
| c_approverUsername     | longtext     | YES  |     | NULL    |       |
| c_identification       | longtext     | YES  |     | NULL    |       |
| c_query                | longtext     | YES  |     | NULL    |       |
| c_result               | longtext     | YES  |     | NULL    |       |
| c_country_list         | longtext     | YES  |     | NULL    |       |
+------------------------+--------------+------+-----+---------+-------+
29 rows in set (0.00 sec)

Some columns that should be purged are:

• c_field1
• c_field2
• c_field3

Understand the data

If you are doing this in a production server, then this step is absolutely needed. You must understand the data first before performing any sort of cleanup. Below is a simple query to return 1 sample row.

mysql> select * from app_fd_sample limit 1 \G;
*************************** 1. row ***************************
                    id: 478407c6-45cc-433a-8684-6f052e9fb5aa
           dateCreated: 2020-04-27 09:20:12
          dateModified: 2020-04-27 09:20:12
          c_attachment: NULL
               c_title: NULL
             createdBy: admin
         createdByName: Hugo Lim
            modifiedBy: admin
        modifiedByName: Hugo Lim
         c_description: NULL
  c_approver_signature: NULL
c_approver_2_signature: NULL
              c_status: NULL
              c_field1: NULL
             c_address: NULL
             c_contact: NULL
                c_name: NULL
     c_approval_status: NULL
        c_last_approve: NULL
              c_field2: NULL
                c_desc: NULL
              c_field3: NULL
                c_city: Austin
               c_state: Alabama
    c_approverUsername: NULL
      c_identification: NULL
               c_query: NULL
              c_result: NULL
        c_country_list: NULL
1 row in set (0.00 sec)

Find usages of the table

When you need to know where a table is used in your applications, this feature helps you find it. It shows you which forms and applications are connected to that specific table.

To perform this search, simply execute the following query in your database:

mysql> select appId, appVersion, formId, name, tableName from app_form where tableName = 'sample';
+------------------+------------+----------------------+----------------------+-----------+
| appId            | appVersion | formId               | name                 | tableName |
+------------------+------------+----------------------+----------------------+-----------+
| bulkUpdateList   |          1 | sample               | Sample               | sample    |
| bulkUpdateList   |          1 | sample_approval      | Sample Approval      | sample    |
| bulkUpdateList   |          1 | sample_clarification | Sample Clarification | sample    |
| sampleV5         |          1 | gridTest             | Grid Test            | sample    |
| sampleV5         |          1 | sample               | sample               | sample    |
| validatorOneYear |          1 | sample               | Sample               | sample    |
| vcApp            |          1 | sample               | Sample               | sample    |
| vcApp            |          2 | sample               | Sample               | sample    |
| versionSample    |          1 | sample               | Sample               | sample    |
| versionSample    |          1 | sample2              | Sample2              | sample    |
+------------------+------------+----------------------+----------------------+-----------+
10 rows in set (0.00 sec)

Then, you'll see a list of results that will tell you which applications and forms the table is being used in. For example, it will tell you that the sample table is used in 10 different forms.

This process gives you a clear view of how the sample table is being used in your applications, allowing you to better understand its context and usage in your system.

Clean up the table through the forms

With this information above, you can manually search through each of the 10 forms and identify which columns or fields are still needed.Then, delete the unused fields directly from the form builder. To do this, follow the steps as shown in the screenshot.

If you don't have access to the database to execute the mentioned SQL queries in this article, don't worry. Joget has a built-in feature to help you find the table's usage. Go to Advanced Tools > Table to find out where the table is used within Joget.

You can also click Show Table Usage In Other Apps to see where else the table is used in different applications.

Clean up the database table

After you have gone through all the forms in the previous step, you can move on to the database to remove the unused columns. This step must be done manually outside of Joget.

For example, if you want to remove the 3 fields found in the first section's table, issue the following SQLs:

ALTER TABLE `jwdb`.`app_fd_sample` DROP COLUMN `c_field1`;
ALTER TABLE `jwdb`.`app_fd_sample` DROP COLUMN `c_field2`;
ALTER TABLE `jwdb`.`app_fd_sample` DROP COLUMN `c_field3`;

Generate hibernate mapping file

To ensure the database structure aligns correctly with joget's forms, follow these steps to regenerate the Hibernate mapping file:

1. Go to the directory: Access the wflow/app_forms folder on your server.

2. Delete the existing mapping file: Locate and remove the file named app_fd_sample.hbm.xml, corresponding to your table.

   Note:
   Deleting this file is important because it prompts Joget to traverse all associated forms, leading to the generation of a new mapping file. This new file reflects any recent changes by reintroducing missing columns into the database.

   

3. Restart the Joget server (recommended): Although restarting the Joget server is not mandatory, it is advised. Restarting helps clear the cache that stores Hibernate mapping files, ensuring that all updates are effectively applied. This step is particularly important if you've removed database columns still referenced in any forms, as failing to update the cache could cause errors when attempting to access deleted columns.

Potential Error Example

If the mapping file isn't updated, Joget might attempt to access a non-existent column, leading to errors like:

ERROR 06 May 2020 17:34:38 org.hibernate.engine.jdbc.spi.SqlExceptionHelper - (conn=20963) Unknown column 'app_fd_sam0_.c_name' in 'field list'
ERROR 06 May 2020 17:34:38 org.joget.apps.datalist.model.DataList - Error retrieving binder rows
org.hibernate.exception.SQLGrammarException: could not extract ResultSet