DEV Community

canonical
canonical

Posted on

How to Add Extended Fields to Entities Without Modifying Tables

#jpa #hibernate #prisma #architecture

Video Demo

Based on the NopOrm engine built into the Nop platform, it's very convenient to add extended properties to any entity. These properties support querying and sorting, and are completely consistent with the entity's built-in properties in terms of usage.

The specific method is very simple: just add the use-ext-field tag to the data table in the Excel data model to enable global extended field support. Extended fields will be saved in the nop_sys_ext_field table.

use-ext-field.png

The structure of the nop_sys_ext_field table is as follows:

Column Name Type
entity_name VARCHAR
entity_id VARCHAR
field_name VARCHAR
field_type INTEGER
decimal_value DECIMAL
date_value DATE
timestamp_value TIMESTAMP
string_value VARCHAR

Based on the setting of the field_type, the specific field value is saved in different columns like decimal_value, etc.

ORM Configuration

During compilation, the <orm-gen:ExtFieldsSupport> tag identifies the use-ext-field configuration and automatically generates the associated properties:

<entity name="xxx.MyEntity">
    <relations>
        <to-many name="extFields" refEntityName="io.nop.sys.dao.entity.NopSysExtField" keyProp="fieldName">
            <join>
                <on leftProp="id" rightProp="entityId"/>
                <on leftValue="xxx.MyEntity" rightProp="entityName"/>
            </join>
        </to-many>
    </relations>
</entity>
Enter fullscreen mode Exit fullscreen mode

If keyProp is set in a one-to-many relationship configuration, it indicates that this property is the unique identifier property. The IOrmEntitySet collection provides extension methods like prop_get/prop_set which can directly access collection items based on this property.

For usage of extended fields, refer to TestExtFields.java

In Java programs, we can access extended fields through the following configuration:

IOrmKeyValueTable field = (IOrmKeyValueTable)entity.getExtFields().prop_get("fldA");
entity.getExtFields().prop_set("fldA",value);
Enter fullscreen mode Exit fullscreen mode

In the XScript scripting language and in the EQL query language, extFields.fldA.string is equivalent to entity.getExtFields().prop_get("fldA").getString()

fldA is equivalent to finding the unique record from the one-to-many collection object according to keyProp.

Extended Field Aliases

To simplify access, we can add aliases for extended fields:

<entity>
    <relations>
        <to-many name="extFields" ... />
    </relations>

    <aliases>
        <alias name="extFldA" propPath="extFields.fldA.string" type="String"/>
        <alias name="extFldB" propPath="extFields.fldB.int" type="Integer" />
    </aliases>
</entity>
Enter fullscreen mode Exit fullscreen mode

After adding the alias configuration, extFldA and extFldB become properties on the entity. In Java, you can get the extended property via entity.prop_get(fieldName).
In XScript, you can access them via property methods like entity.extFldA, exactly the same as regular entity properties.

If code is generated based on the ORM model file that defines alias, corresponding get/set methods will be automatically generated. Thus, in Java we can access extended properties via entity.getExtFldA() and entity.setExtFldA(value).

If get/set methods are generated, you can no longer use the entity.prop_get method to get the property value. This is because prop_get is used to get extended properties that do not exist on the entity. If you want a unified way to get both built-in and extended fields, you can use the entity.orm_propValueByName(name) method, or use reflection mechanisms like BeanTool.getProperty(entity, propName).

Moreover, in EQL query syntax, you can directly use extended fields for filtering and sorting. The usage of extended fields is completely consistent with the entity's built-in fields:

select o.extFldA
from MyEntity o
where o.extFldA = '123'
order by o.extFldA
Enter fullscreen mode Exit fullscreen mode

Using the alias mechanism, we can achieve a smooth transition between extended fields and built-in fields: during initial development, extended fields can be used first, and then basic fields can be added to the entity when performance bottlenecks arise,
while keeping the property names in Java code unchanged.

GraphQL Access

Add configuration for properties like extFldA and extFldB in the xmeta file to enable GraphQL access for extended properties.

    <prop name="extFldA" displayName="Extended Field A" queryable="true" sortable="true" insertable="true" updatable="true">
        <schema type="String" domain="email" />
    </prop>
Enter fullscreen mode Exit fullscreen mode

Dedicated Extended Field Tables

By default, all extended fields in the system are stored in the nop_sys_ext_field table, which may lead to excessive data volume in a single table and poor performance. To alleviate this issue, we can add the
local-ext tag to the entity table. Then the system will automatically generate a paired extended field table for the current entity. The extended table name is generally original_table_name + '_ext', for example, nop_sys_notice_template_ext.

The structure of the extended table is similar to nop_sys_ext_field, except it lacks the entityName field, as filtering by entity name is not needed.

Pivot Table Transformation (Vertical to Horizontal)

Many low-code platforms use vertical tables to store all data to achieve dynamic modification of database structures, and the vertical-to-horizontal table transformation is a special hardcoded conversion. The Nop platform is different.
Its built-in vertical/horizontal table transformation is a standard mathematical transformation. Not only ordinary vertical tables, but any one-to-many relationship can be transformed into a one-to-one relationship, and the properties of a one-to-one or many-to-one relationship are completely consistent with using the table's native fields at the EQL level.

In the TestExtFields unit test:

 <entity name="io.nop.app.SimsExam">

            <aliases>
                <alias name="extFldA" propPath="ext.fldA.string" type="String"/>
                <alias name="extFldB" propPath="ext.fldB.boolean" type="Boolean" notGenCode="true"/>
            </aliases>

            <relations>
                <to-many name="ext" refEntityName="io.nop.app.SimsExtField" keyProp="fieldName">
                    <join>
                        <on leftProp="id" rightProp="entityId"/>
                        <on leftValue="io.nop.app.SimsExam" rightProp="entityName"/>
                    </join>
                </to-many>

            </relations>
        </entity>
Enter fullscreen mode Exit fullscreen mode
  • Any to-many relationship can be configured with a keyProp attribute to distinguish a unique record within the collection.
  • ext.fldA.string is equivalent to ((IOrmEntitySet)entity.getExt()).prop_get("fldA").getString()
  • Through the alias mechanism, we can give a simple name to the complex path for accessing extended fields. For example, in the configuration above, extFldA is equivalent to ext.fldA.string.
  • If notGenCode is marked, the get/set methods in Java will not be generated for this property during code generation. Values need to be obtained via methods like entity.prop_get("extFldB").

In XScript or XPL template languages, the access syntax for extended properties and ordinary properties is exactly the same; you can directly use methods like entity.extFldB = true.

In the EQL query language, keyProp is also automatically recognized for structural transformation.

select o.children.myKey.value from MyEntity o
// Will be transformed into
select u.value from MyEntity o  left join Children u on o.id = u.parent_id and u.key = 'myKey'
Enter fullscreen mode Exit fullscreen mode

That is, as long as a collection has some unique identifier, mathematically it can always be flattened into an associated property with a unique access path. The ORM engine's EQL query language is responsible for transforming associated properties like o.a.b.c into table join queries.

select o.name from MyEntity o
where o.children.myKey1.intValue = 3 and o.children.myKey2.strValue like 'a%'

-- Will be transformed into

select o.name from MyEntity o left join Children u1
on o.sid = u1.parent_id and u1.key = 'myKey1'
left join Children u2
on o.sid = u2.parent_id and u2.key = 'myKey2'
where u1.intValue = 3
and u2.strValue like 'a%'
Enter fullscreen mode Exit fullscreen mode

For a one-to-many related table, adding a key filter condition naturally turns it into a one-to-one related table:

select o.key1,o.children.myKey1.value,o.children.myKey2.value
from MyEntity o

-- Will be transformed into
select o.key1, u1.value, u2.value
from MyEntity o left join Children u1 on
on o.sid = u1.parent_id and u1.key = 'myKey1'
left join Children u2
on o.sid = u2.parent_id and u2.key = 'myKey2'
Enter fullscreen mode Exit fullscreen mode

According to the rules, extracting the related table from o.children.myKey is, at the mathematical level, a deterministic local transformation rule.

Top comments (0)