When describing static lists of values in your business domain, you should consider defining them as static enumerations in the Xomega model, which can help you to better version control it, as well as to use it for generation of various artifacts. This will make these values available and easily accessible from the lookup cache in both UI and business logic layers.
However, sometimes you need to be able to access this data in the database layer as well. For example, if you need to look up descriptions or additional properties of certain enumeration items in SQL queries or stored procedures. Therefore, it is useful to be able to store such data in the database in one or several dictionary tables. When the Xomega model is a primary source of the static data, you will be able to
generate a SQL script to populate the database dictionary tables from the Xomega model's enumerations. Also, when you start modeling from the existing database, that already contains some static data, you will be able to
import such data into the Xomega model as enumerations.
When creating dictionaries, you can either define one generic system dictionary that holds all items of all enumerations, and potentially their additional properties, or you can store each enumeration in its own table, or use a combination of these two approaches, where some enumerations are stored in dedicated tables, while all others are stored in a generic dictionary.
If you model your system from scratch, and you don't have any system dictionary defined yet, then you can easily create a generic dictionary for storing all enumerations and their additional properties by using the
Add > New Item menu option on the model project and selecting the
Dictionary Object Xomega template.
If, however, you already have database tables that store all or some of the static enumerations, or if you have specific database design for such tables, then you need to import or create the corresponding model objects and configure the mapping between the object fields and the structure of Xomega enumerations. Below are the steps that will help you achieve this.
- Create an object definition for storing one or multiple types of enumeration items by either importing it from an existing table, developing manually or updating the default object definition provided by the Dictionary Object item template.
- Add a config element inside the object element if needed.
- Add a sql:enum-items element inside of the config element, where the sql prefix maps to the "http://www.xomega.net/sql" namespace.
- If the object can store multiple enumerations, then specify the object's field that stores the enumeration name by setting the enum-name attribute.
- Set the item-value attribute to the name of the field that stores the value for each item (typically this is some kind of an ID).
- Set the item-text attribute to the name of the field that stores the display text for each item (typically this is some kind of an name).
- Optionally set the item-desc attribute to the name of the field that stores a longer description for each item if applicable.
- If this object should be used to store all enumerations that don't explicitly specify which object stores their data, then set the default attribute to true. Only one object may have this attribute set to true.
- If your object has other fields, for which the values should be stored as additional properties of the enumerations items, then for each such field add a sql:property nested element inside of the sql:enum-items element.
- Set the field-name attribute to the name of the field that should be stored as an additional enumeration property.
- Set the property-name attribute to the name of the enumeration property that stores the value for this field.
- Similarly, if your object has other fields, for which the values should be either fixed values or generated at insert time, such as the current date, then for each such field add a sql:fixed nested element inside of the sql:enum-items element.
- Set the field-name attribute to the name of the field that should have the fixed value.
- Set the fixed-value attribute to the actual value or an SQL expression to be inserted.
If you also want to be able to store arbitrary additional properties for your enumeration(s) in a child table as name/value pairs, then you need to also define such an object or a subobject of the dictionary object, and configure the mappings between object's fields and the structure of enumeration properties. Here are the steps that you need to take.
- Create a (sub)object definition for storing additional properties for enumeration items by either importing it from an existing table, developing manually or updating the default object definition provided by the Dictionary Object item template.
- Add a config element inside the object element if needed.
- Add a sql:enum-item-properties element inside of the config element, where the sql prefix maps to the "http://www.xomega.net/sql" namespace.
- If the object can store multiple enumerations, then specify the object's field that stores the enumeration name by setting the enum-name attribute.
- Set the item-value attribute to the name of the field that stores the value for each item (typically this is some kind of an ID).
- Set the property-name attribute to the name of the field that stores the additional property name for each item.
- Set the property-value attribute to the name of the field that stores the value of the additional property for each item.
- If this object should be used to store properties for all enumerations that don't explicitly specify which object stores their data, then set the default attribute to true. Only one object may have this attribute set to true.
- If your object has other fields, for which the values should be either fixed values or generated at insert time, such as the current date, then for each such field add a sql:fixed nested element inside of the sql:enum-item-properties element.
- Set the field-name attribute to the name of the field that should have the fixed value.
- Set the fixed-value attribute to the actual value or an SQL expression to be inserted.
The following snippets demonstrate this setup for a generic dictionary.
Code:
<fieldset name="dictionary key">
<field name="enumeration" type="enum name"/>
<field name="item code" type="enum item code"/>
</fieldset>
<object name="dictionary">
<fields>
<fieldset ref="dictionary key" key="supplied"/>
<field name="text" type="enum item text"/>
<field name="description" type="enum item desc"/>
<field name="addl field" type="string"/>
<field name="reload date" type="date time"/>
</fields>
<config>
<sql:enum-items default="true" enum-name="enumeration" item-value="item code"
item-text="text" item-desc="description">
<sql:property field-name="addl field" property-name="addl prop"/>
<sql:fixed field-name="reload date" fixed-value="GetDate()"/>
</sql:enum-items>
</config>
<subobjects>
<object name="property">
<fields>
<field name="id" type="enum item property" key="serial"/>
<field name="property name" type="enum name"/>
<field name="property value" type="enum item text"/>
<field name="fixed fld" type="string"/>
</fields>
<config>
<sql:enum-item-properties default="true"
enum-name="enumeration" item-value="item code"
property-name="property name" property-value="property value">
<sql:fixed field-name="fixed fld" fixed-value="'some value'"/>
</sql:enum-item-properties>
</config>
</object>
</subobjects>
</object>