Skip to main content

How to change the key in Data Type?

Let's imagine you have a recipe book (analogous to a database) with specific instructions for each recipe (analogous to rules for a data type). Each recipe has a key ingredient (analogous to a key in a database table) that makes it unique. Now, suppose you realize that a particular recipe's key ingredient needs to be changed, and you've already written down all the specific instructions for that recipe.

If you catch this mistake right after creating the recipe, you can simply erase the instructions, change the key ingredient, and rewrite the instructions. However, if you discover this error later, after many copies of the recipe have been shared (analogous to rules being locked in an old version), things get trickier.

In the digital world, this is like having a database table with predefined rules (recipe instructions) and realizing that you need to change the key (key ingredient). It's not as simple as starting from scratch, especially when the rules are already locked in a certain version.

The challenge here is similar to going back to your original recipe book, crossing out the old key ingredient, and replacing it with a new one without messing up the existing instructions. Even if the original version is locked, you need to find a way to update it without causing chaos.

In the world of databases, this involves a careful process to modify the key without disrupting the existing data and rules. This ensures that the database remains consistent and accurate, just like making sure your recipes still make sense after changing a key ingredient.

So modifying the key of a database table is a common requirement in response to various reasons, and developers may find themselves needing to alter the key defined in a data type. If this change is required shortly after the data type is created, one can simply delete all the generated rules and redefine the data type from scratch. However, the situation becomes more complex when the request to change the key arises later, and all the rules are already locked in the old RSV (Rule Set Version). It's important to note that whether the rule is locked or not becomes inconsequential in the process of changing the key. In this post, I will outline the detailed steps involved in making such a key modification despite the rules being locked in the old RSV.

The concept of a primary key, or simply "key," is defined at two levels in Pega: at the Pega level and the database management system (DBMS) level. It's worth noting that the keys at these levels may differ. For instance, in Pega, the default key for a Work- derived class is "pyID," while at the DBMS level, the primary key is "pzInsKey." On the other hand, for a Blob table, regardless of whether it's derived from Work- or Data-, "pzInsKey" is used as the primary key at the DBMS level.

Since Pega 7.4, CustomerData has been adopted as the default database for Data Type. Consequently, the out-of-the-box table generated by a Data Type is Non-Blob. This means that not only "pzPVStream" but also "pzInsKey" does not exist. Instead, any custom property specified as a key in the Data Type configuration is set as the primary key in the DBMS. In the provided example, the MacAddress property has been configured as the key.


Upon inspecting the physical table in the DBMS, it is evident that the primary key is automatically configured on the "macaddress" property.




At the Pega level, the key "MacAddress" is located on the class form. It's important to note that the key field appears grayed-out, and this is due to the table being already populated. When a table has existing records, the key becomes non-editable.


How to change the key?

Suppose the Rule Set Version (RSV) is locked, and there's a change in requirements: you're tasked with switching the key from "MacAddress" to "SerialNo." Even though all the rules are locked, it is possible to accomplish this without directly modifying the rules. The following steps outline how this can be achieved.

1. As mentioned earlier, the key in Pega cannot be edited in the class form when the physical table has existing records. Therefore, the initial step is to truncate the table. If the table contains crucial records, it is advisable to manually create a backup. In this instance, the pgAdmin tool was utilized to export all records into a CSV file.

2.Access the class form through Dev Studio. The key field should now be editable since the table no longer contains records. It's essential to note that a Class is a unique type of rule instance, and unlike other rules, there is no check-in or check-out concept. Even when the Rule Set Version (RSV) is locked, this rule is always savable, similar to a Data instance. Replace "MacAddress" with "SerialNo" and proceed to save the changes.


3.Despite changing the key in the class form, this action does not automatically trigger a Primary key change at the DBMS level. Upon clicking the "Test Connection" button in the class form, an error message will be displayed, indicating a key mismatch. The error message may state, "There is a key mismatch: the keys defined for class MyCo-MyApp-Data-Computer do not match the primary key columns for the external data table data.pr_myco_myapp_data_computer in the CustomerData database. The class keys are [SerialNo], but the table's primary keys are [data.macaddress]." This highlights that modifying the key remains an irregular operation, and the change must be manually executed at the DBMS level.


4.In the DBMS, execute a Data Definition Language (DDL) command to drop the existing Primary key. Alternatively, you can use the pgAdmin tool for this task.



5.Recreate the Primary key.


6.Assign a name to the Primary key. It can be the same as the original one. In this instance, use the name "pr_myco_myapp_data_computer_pk."


7. Define "serialno" as the column for the Primary Key.


8.Verify that "serialno" is now set as the Primary Key in the DBMS.


9. Reattempt the "Test Connection" process and observe that the previously encountered mismatch error message is no longer present. This indicates that the key, which is now set as "SerialNo," is synchronized at both the Pega level and the DBMS level.

10. The final task remaining is to restore the backup records into the table. Utilize the pgAdmin tool and choose the "Import" option for this step.

11.Ensure that all records have been successfully restored.


Happy Learning :)

Comments

Post a Comment

Popular posts from this blog

Understanding the Lock Mechanism in Pega

Now, let's delve into the Pega lock mechanism. To begin, Pega employs both database-level locks, which are of shorter durations (usually in milliseconds), and Pega-level locks, which extend over more extended periods (determined by user operations, often a few minutes). While you have the option to disable Pega-level locks using the Optimistic locking strategy (discussed later), database-level locks cannot be turned off; they are an integral part of the infrastructure layer. Moving forward, I'll elaborate on Pega-level locks. 1. Fundamentals of Pega-Level Locks When an individual initiates an assignment, the system transitions the state to "Perform" mode and acquires a lock. This lock data is then added to the "PR_SYS_LOCKS" table in the database, where it is systematically managed. Throughout this phase, no other user can access the same assignment. Upon the user's submission or cancellation of the assignment, the state shifts to "Review" mode...
Post a Comment
Read more

Building a CSV File Download Function from a Page List

Consider a scenario where we want to download recently added table data from the screen into a CSV format for analysis. Today, we will explore how to accomplish this in Pega. An existing out-of-the-box activity named "pxConvertResultsToCSV" is available in the @baseclass, and you can utilize it without the need to create it from the ground up. The following example illustrates a sample screen: when the button is clicked, the Page List data on the screen is downloaded as a CSV file to the end user's local machine. 1.To begin, establish a Page List property. In this instance, I defined a Data class, "MyCo-Data-Item," and subsequently generated a Page List named "ItemList" in the Work class, referencing the Item class. 2.Position a table that references the Page List, and configure it to be inline-editable, allowing the addition of records directly from the screen. Additionally, position a button in close proximity to the table. 3.Configure the button by ...
Post a Comment
Read more

How to set up JFrog Artifactory as a Repository in Pega.

Before delving into the topic, let's gain some understanding of what a repository is........... What is a repository? A repository is a centralized storage location where data, files, documents, or other digital assets are stored and managed. It serves as a single source of truth for organizing, versioning, and accessing these assets.  In the context of software development, a repository typically refers to a version control system, where developers store and manage source code, configuration files, documentation, and other project-related files. Version control systems like Git, Subversion (SVN), and Mercurial are commonly used repositories in software development. In addition to version control systems, repositories can also refer to databases, file systems, content management systems (CMS), or any other structured storage system used to manage digital assets. Overall, a repository provides a structured and organized way to store and manage digital assets, facilitating collaborat...
Post a Comment
Read more