The ORM Structure
This document describes the ORM (Object Relational Mapping) structure which is used to define the database integration.
SilkBuilder IDE Interface
The SilkBuilder IDE provides a graphical interface to configure ORM files. This can be find there: Project, Data Model, and ORM.
This document is provided as an explanation for advance user who want to understand how SilkBuilder communicates with database servers.
The ORM Structure
SilkBuilder provides a series of tags to define how to access and operate the information in the database or databases, and even the file system. Each tag has several properties and SQL commands holders. If the tag's property is boolean the accepted positive values are: "true", "yes", "1" and "on". Any other value will be consider as false.
The ORM Tag <orm>
<orm>The ORM tag defines the root node for the ORM XML structure. It has two properties: remoteService and queryType.
Properties
The RemoteService Property: This property is located in the ORM root tag
<orm>. It defines what service will be use to access a particular database server using the ORMOopsClickClass. The service is define in the "applicationContext.xml". If this property it is not included the service "SilkDBOService" is used as default. The recommendation is to create the service named "SilkDBOService" connected to the database server where silk tables are stored, and create other services, with different names, to connected to other database servers as needed. With a setup on this way the "remoteService" property is only use when accessing the other database servers rather than the default one.The queryType: This property indicates how the SQL queries or commands will interact with the target database. The values are:
sql : This is the default value if the property it is not included. Indicates that the target database uses SQL commands.
folder : Indicates that the target database is the files system. For this case SILK will parse the SQL commands to file system commands. A folder is treated like a table, and the files likes records. For a longer explanation on how to use an ORM with
queryType="folder"check Querying the File System.
The table tag <table>
<table>Defines what target table for the insert, update and delete actions.
Properties
Name: Contains a string with the table name targeted by the ORM file. This information is used to apply insert, update, and delete operation in the defined table. The table name should contain the database scheme if necessary to locate the table in the database (example in SQL Server: redatabase.dbo.table).
pkMode: Indicates how the table's primary key should be treated. Receives a string indicating on of the 4 modalities: Auto, SQL, Value. If the pkMode attribute is not included the "Auto" mode is setup as default.
Auto: Indicates that the primary key had been configured to be an auto incremental field. Each database server has different ways to configure an auto incremental field.
SQL: Indicates that the primary key is going to be generated by an SQL query call. This SQL is defined in the pkSql tag. This SQL should return only one field and one record, the field should have the same name as the primary key the value will served. This SQL runs before the insert operation. For example, the typical SQL expression for a table store in an MS SQLServer with primary key as int would be: Select isNull(max(id),0)+1 as tablePrimaryKeyField from table.
Value: Indicates that the primary key will be provided by the client. The programer should include the primary key value before sending the form.
UUID: Indicates that the primary key will be generated using the class java.util.UUID (Universal Unique ID). The primary key field must be a varchar(36) or similar data type and length.
insertAuthorization: This property store the expected keyword, or keywords separated by commas, to grant authorization to insert records into the table.
updateAuthorization: This property store the expected keyword, or keywords separated by commas, to grant authorization to update records in the table.
deleteAuthorization: This property store the expected keyword, or keywords separated by commas, to grant authorization to delete records from the table.
Example:
The column tag <column>
<column>Defines the fields used by the mapping. These should be the same fields returned by the select command.
Property
name: A string with the name of the column. This column is mandatory.
type: A string indicating the column type. This property is mandatory. The accepted values are:
S : String
I : Integer
N : numeric
D : Date/Time
P : Password. It creates a high-strength password digest with this characteristics:
Algorithm: SHA-256
Salt size: 16 bytes
Iterations: 100000.
pk: A boolean indicating if the columns is the primary key. Currently ORM only supports single primary keys. This property is mandatory.
secure: A boolean indicating if the column information will be encrypted before sending to the client. The client has to return the encrypted values. This is usually used for primary keys and foreign keys. By default the primary key columns is marked as secure and will be encrypted except if it is disable by adding secure="false" . This property is not recommended for other values considering that the client will not be able to un-encrypt the information.
root: A boolean indicating that the column contains the primary key information of the record parent. This is only required to work with hierarchical information.
parent: A boolean indicating if the columns holds the information related to the parent records. This is only required to work with hierarchical information.
level: A boolean indicating that the columns contains the record's hierarchical level. This is only required to work with hierarchical information.
translation: A string or integer indicating the method of translation use for this column. There can be:
"false" or "0" indicates no translation. This is the default value for every column.
"true", "xml" or "1" indicates translation store in the column content. SILK will use a XML structure to manage the languages inside the column content. For this case the table column should be setup as a long string like VARCHAR(3000) or VARCHAR(MAX) in SQL Server.
"column" or "2" indicates translation stored in multiple columns. SILK will use different columns for each language. SILK will combine the property "name" and the language ISO code provided by the application. Example: name_en, name_sp, name_fr. It is the programers responsibility to add these columns to the table.
When using Silk Builder ORM Editor the values use to generate the ORM XML will be 0, 1, 2.
function: A SQL function to modify the value before operating the insert or update command. The value has to contain the ? simbol as placement for the affected value.
functionInsert: A SQL function to modify the value before operating an insert command. The value has to contain the ? symbol as placement for the affected value.
functionUpdate: A SQL function to modify the value before operating an update command. The value has to contain the ? simbol as placement for the affected value.
nullValue: A string indicating the value considered null in an insert or update action. SILK does not send NULL values to the database, instead it returns an empty string or zero. If you want to include the value NULL in the database you will need to use the "nullValue" property to indicate what value should be converted to NULL.
validation: A string containing a Regular Expression to validate the field's received data in the server side. This is an optional property. If one of the validations expression fails the insert or update process will also fail. It is the programmer's resonsability to mimic the same validation process in the client side to avoid confussion of unexpected failing processes.
authorization: This property store the expected keyword to grant authorization to operate in this column.
The sqlSelect tag <sqlSelect>
<sqlSelect>This tag contains the select command used to retrieve the rows and fields to populate the dataProvider in the Flex client. It is possible to use parameters which are defined enclosing them with "[" and "]". These parameters are configured in the client as criteria values. It has the property name used to identify each select command.
Example
The pkSQL tag <pkSQL>
<pkSQL>This tag is necessary if the pkMode attribute is "SQL". This tag contains the select command necessary to create the primary key. It has to return a string field called "pk". Even if the primary key value is an integer in the database structure, is has to be converted to varchar in the select.
Example
The sqlOperation tag <sqlOperation>
<sqlOperation>Contains the sql operation to be executed in a similar way as database triggers. You can also include parameters in the SQL operations in the same way as the <select> tag. These parameters will be processed by the criteria values and also the form field values.
The recommendation is to define database triggers, function, and store procedures to model the business logic.
Properties
name: Operation name, used during an operation direct call.
action: Defines the actions triggering this operation. The values are: select, insert, update, and delete. It can be one or many separated by commas.
when: Defines moment when the operation would be triggered. The values are: before, and after. It must have one of the values.
authorization: This property store the expected keyword to grant authorization to execute the operation.
reportZeroResult: This is an optional property to display a message in the logs if the trigger did not process any row. Value 1 activates it.
The sqlAuthorization tag <sqlAuthorization>
<sqlAuthorization>This tag stores the SQL select command which will validate the property keyword. This SQL should return a value of 1 for true or 0 for false with a column name "result". This SQL can received parameters. If an authorization tag's name is similar to the an authorization keyword, the SQL will be executed.
Properties
Name: This stores the name of authorization related to this sql.
SQL Modifiers
The SQL modifiers are values provided by the application to modify the SQL command. These modifiers are place in the SQL command and are replace by values before being executed. These could be parameters to filter the SQL command output or to modify the SQL output.
$P{} : Parameters
$V{} : Code command changes
$T{} : Translation out
$R{} : Translation in
$A{} : Authorization for SQL code lines
$G{} : Global Variables
$P{}
This modifier received the parameters set in the client. It can be used in any place in the SQL command in which the parameter is necessary to modify the query behavior.
The parameter will be converted into Name Parameters before being executed by the JBDC object. The data passed to the parameter from the client will have a defined data type which should match the column type it is interacting with.
In the example below the parameter is used to filter the query result by the provided age.
In the next example the parameter is used to decide which column will be returned.
$V{}
This modifier receives a value which is directly applied to the SQL code before it is send to the JDBC. It is not converted into a Name Parameter. Using this modiffier portions of SQL can be setup dinamically. Hover the provided value should not include any other moddifier. This modiffier always returns a String value.
In this example the value is used to determine the query order. The value could be "fullName" or "age,fullName".
in this other example the value is used to dinamically decided what colums to return. The values could be "age, phone", or "emailAddress, birthday".
Even though this modifier provides great flexibility it is recommended to limit it used for cases when common SQL practices can not provided the desire effect.
To avoid the danger of SQL Injection common SQL keywords like "select", "update", "insert", ";", "from", "order", "group", and others are remove from the received value.
$T{}
This modifier sets the column to be translated. This wraps the column into the translation function set during Silk setup. This is to be used with columns setup with the translation property to "xml", "true" or "1".
To translate column which had been setup with the translation property to "column" or "2".
Querying the File System
The ORM can be used to "query" the file system. Once setup a folder can be query like a "database".
Last updated