Data Relationship Schema Snippet
Introduction
The Data Relationship Schema is a table in AlchemyJ that is used for representing the relationship of the tables and would be used for constructing a data structure. It supports both single layer and multiple layers.
A REST API is always returning a JSON object with the definition of Data Relationship Schema. When the output data are multiple rows, the result will become a JSON Array. Then, it needs to create a root JSON node to convert them to a JSON object. For details, please refer to Example.
As for Java API, when the return class is an object, it would also depend on Data Relationship Schema to construct the object. There is no need to use Data Relationship Schema when the return value is a single value. For example, returning a customer's age.
Also, the Data Relationship Schema would be used in some AlchemyJ Extended Functions such as ajXMLFromSchema and ajJSONFromSchema.
To create a Data Relationship Schema, you can Go to the AlchemyJ ribbon, click on Insert Snippet and select Data Relationship Schema(Single Table) for single table or Data Relationship Schema for multiple table relationship. A table would be inserted into the worksheet. The first row is the table header and there could be one or more multiple records in the table. Each record represents a range of data (table).
Column Definition
| Column Name | Description |
|---|---|
| JSON Node Name (optional) | 1. This column is used to define the node name in the returned JSON. 2. If this column is not included or the value is not keyed in, AlchemyJ will generate the node name automatically using table name with the following rule. a. Change the first character of 'Table' column value to lower case. b. Append "List" to the end if Multiple Row is TRUE. For example, if the 'Table' column value is 'Client' and 'Multiple Row' is TRUE, it will generate the node name as 'clientList'. This field is not used in Java API. |
| Table (required) | 1. Table name. It is a mandatory column and the value is mandatory. 2. The value should be unique in the schema relationship. 3. When the Schema table is used to define the function point input or output Object type parameter, the following rules will be checked. a. The Table name can only contain letters, numbers and underscores. It must start with a uppercase letter. b. The Table Name cannot be a Java or AlchemyJ reserved keyword. |
| Table Address (optional) | 1. The column is mandatory. 2. The column value is mandatory when any one of below situation happens: - Key is not empty. - The current table does not have any sub-tables. 3. The value is a cell range address or a name which specify the range of the table. 4. The table address has to include two rows at least, one column header and one data row. If the table address is not valid, it will return #VALUE!. 5. If the Table header and Table data body range is not in a continuous range, use asterisk "*" to join their addresses. For example: =ajAddress(A1:B1) & "*" & ajAddress(A10:B20) 6. ajAddress must be used to define the table address. Do not hardcode the address. |
| Parent Table (optional) | 1. The column value is mandatory when Key is not empty for the current table. 2. Parent table name which is used to define the table dependency relationship 3. Parent table name should exist in 'Table' column value 4. If the parent table name doesn't exist in 'Table' column value, it will return #VALUE! 5. Parent Table name could not be the same as 'Table' in the same row. 6. This field defines the parent table to be linked. |
| Key (Conditional Mandatory) | 1. If the Parent Table is filled in and parent table records are more than 1 row, Key is mandatory. If the parent table only has one record, Key can be omitted because it means all records in sub-table will be joined. 2. If the Table Address of parent table is empty, Key can be omitted. 3. The key is mandatory when any of its parent tables at any level with Multiple Row = True. In this case, if the key value is not provided, it will return #VALUE!. 4. The Key is a column name in the parent table, the column name should be the same as the data name defined in the data dictionary. 5. If the Key is not provided when the parent table is filled in, it will return #VALUE! 6. If the Key value cannot be matched with any column name in the parent table, the column and data of the sub-table would not appear in the returned JSON/XML. |
| Multiple Row (optional) | 1. Boolean value - True / False, True means the table should be treated as a list. This is particularly important when the table contains one row of record. If it is set to TRUE, that one row of data will be shown as an array in JSON. If it is FALSE, that one row of record will be shown as an object. 2. If the value is provided by the user, it will follow the inputted value. 3. If the value is omitted, AlchemyJ will set it to FALSE if the range contains one data row. Otherwise, TRUE. 4. If the inputted value is not a boolean value True / False, it will return #VALUE! |
| Exclude Column (optional) | 1. Define which columns in the table will not be included in the returned JSON/XML. 2. The format is [Field Name] [Parameter Separator]. E.g. Area Code- ||-Tel3. Field name that does not exist will be ignored. |
| Data Dictionary Address (optional) | 1. The address of the Data Dictionary which defines the formatting rules. Please include the header in the range of cells in the first row. 2. It is used to format the value in the JSON/XML only and will not trigger data dictionary validation 3. When the Data Dictionary is provided, all columns should be included in the Data Dictionary. Otherwise, it will be omitted from the returned JSON/XML. 4. It must use ajAddress function to define the data dictionary address. |
| Header Position(optional) | 1. The table column header is in row-level or column level. a. top - the column header is in row-level b. left - the column header is in column level 2. Default is 'top' if no value is provided in the column |
| Required(optional) | TRUE - The table will be marked as required in OpenAPI specification. FALSE - The table will not be marked as required in OpenAPI specification. The default is 'FALSE' if no value is provided. |
| Description(optional) | Description to be shown in the OpenAPI specification for this table. |
| API Model Name(optional) | API Model Name to be shown in the OpenAPI specification for this table. |
If the cell value is Excel error (e.g. #VALUE!, #NA!, #DIV/0) in the data relationship schema table, the corresponding node would be omitted.
Only Table, Table Address, Data Dictionary Address, and Header Position columns are applicable for Data Relationship Schema(Single Table).
Example
Below are some samples to show you how to use the data relationship table to return objects for API.
Assuming there are two tables, Customer and Email.
Example 1 - API Returns a Single Record
In this sample, it assumes that the REST API returns a customer record only. It only needs to define the Table and Table Address. So it is using Data Relationship Schema(Single Table) snippet to create an Excel table and input the data information. For this case, the setting is the same for Java API.
Example 2 - API Returns Multiple Records of a Single Table
For REST API, when it needs to return multiple records, it needs to add a root node to combine the JSON array into a JSON object. Besides the name and address for the customer table, the dummy table is required and should be set as the parent table. Thus it needs to use Data Relationship Schema snippet to create an Excel table and input the data information. The dummy table is not required for Java API.
Example 3 - API Returns Multiple Records of Multiple Tables
Similarly as Sample 2, the dummy table is required when it returns multiple objects. In this sample, it has appended the email table and the parent table is Customer. Since the email table needs to be linked with the Customer table, the key is required for it. For this case, Name is the joint key for these two tables.
