Integration with a content manager has a much greater value if it enables content enrichment with metadata. If we have a payroll app, we can integrate with a content manager to store the payroll pdf of each employee just as a plain document, but it is much more interesting from a documental perspective to store it as a ‘payroll’ document type (instead of a plain ‘generic’ document) with custom properties as employee Id, department id, month/year of the payroll, …. This would enable much more powerful capabilities an automations in the ECM platform side (we can launch custom workflows with much more information on the content it is handling).
docXtend has a large range of capabilities and sophisticated mapping rules for decorating the transferred content with meaningful, dynamic metadata, based on the application database content. The setup tool provides a metadata mapping screen where we can define basic and advanced mappings. The mappings are configured for each table/attribute holding content that we will move to the content manager (in the case we select several attributes in the same or different tables, we can define independent mapping rules for each of them). Let’s see in detail.
Common concepts
A mapping rule is a valid SQL expression. This is done by design and opens a powerful way to define dynamic mapping rules for your content. So, any configured rule in the metadata mapping configuration screen must be syntactically a correct SQL expression and semantically acceptable in your application data model. For example, if we want to place on the content in a single subfolder called “myapp content” we have to configure this mapping rule with a SQL constant expression (that is, surrounded with single quotes). So, in our example, we have to configure ‘myapp content’ . However, if the folder name should be the value stored in the attribute “description” in the table “app_content” of our app data model, we have to configure app_content.description (without quotes). If we are using Oracle and want to make sure that all the folders are created uppercase, we would use the expression UPPER(app_content.description) .
The mappings must conform with the document model defined at your ECM platform. This must be designed and configured in advance in your ECM platform (the procedure varies depending on the platform; check your documentation). That is, if we decide to assign to our content a document type of “mycompany:payroll”, this document type must exist in your ECM platform and you have to create mappings for all the mandatory properties defined in such document type. Otherwise, the validation tests will fail and a error message will be generated in the docXtend setup tool.
Basic mappings
We consider basic mappings as those that define the core behavior for the transferred content:
- MIME type: Is the content type for the transferred content (e.g. application/pdf, image/png, …). Enterprise grade platforms as Alfresco are able to detect it automatically so leaving it blank should be a safe option.
- Document type: Is the document type (conforming our document model in the content manager, as described above) to be assigned. If we leave it blank the default, generic document type in your content manager will be used.
- Folder: It is the subfolder to be created for placing the content from the root folder defined in the content manager credentials configuration screen. Notice that this is a dynamic expression so we can create a variable list of folders, not necessarily all the content will be placed in the same subfolder. Following our payroll example, if we have an attribute in our database defining the year and month of the payroll we can define an expression year||’/’||month that would create a hierarchy of subfolders with the year – month hierarchy of our payrolls.
- File name: The name of the document in the content manager. Any content stored in a BLOB attribute in a database has no identifiable ‘name’, so we should provide a mapping for naming this content with a reasonable meaningful value.
It is not mandatory to define any mapping rule. In the case we leave the full mapping configuration screen blank, the behavior is:
- MIME type automatically guessed.
- Document type as the default document type in your content manager.
- No subfolders; all the content will be placed in the root folder configured in the content manager connection screen.
- Random generated file names.
Once our mappings are configured, the setup tool will execute a series of validations through your database and your content manager to ensure that your rules are:
- Syntactically correct from a SQL perspective.
- Conforms the document model in your content manager.
If everything is OK, the tool will simulate the mappings for a few rows in your data model and generate a screen where you can check that the folder structure and document name are the expected, before moving on.
Advanced mappings
In addition to the basic mappings we can configure custom properties that conforms with our document model. If our payroll document type in the content manager has an attribute emp_id we can define a custom mapping for that attribute. This is valid also for subtypes (in Alfresco terminology, aspects). The way to assign a aspect to a content is assigning a custom property of that aspect through a custom mapping. The advanced mappings should conform to the same rules and syntactical/semmantical requirements described for basic mappings.
Join expression
Depending on the complexity of our mapping, we may need to define the JOIN expression that will link our data model tables to generate the metadata mappings query that docXtend uses when moving content. As stated above, we are defining the mapping rules for an specific table/attribute in our data model (this is shown at the top of the screen). If our mapping expressions uses attributes from other tables, we need to define a join expression to precisely guide how to link them together. So if we want to add to our payroll document properties the emp_name attribute value from the table employees and the table payroll and employees are ‘linked’ by the attribute emp_id, we need to add the join expression employees ON employees.emp_id = payroll.emp_id (notice that you must NOT include the beginning JOIN word). This way we can use the SQL expression employees.name in the mapping rules for our table payroll.
For complex data models and mapping rules it is recommendable to test a query and join clause directly through our SQL query tool and validate it generates the expected results before configuring it in the docXtend setup tool.
Updating mappgins
We should carefully plan and configure our mapping rules. We can update them anytime it is needed but those changes will not be automatically applied to the content already moved to the content manager (it will require an update on the content at the database level). Based on that, we recommend:
- Using a non-production environment for testing our mappings.
- Install docXtend, configure your mappings, insert some content with your application, check that it generates the expected structure and metadata.
- If it requires adjustments, rollback the content, update the mappings and move it again, using the command line interface (commands “docxtend rollback” and “docxtend move” respectively)
- Once finished, apply the same settings in your production environment.
For updating mappings, just open the same setup tool and select the option Upgrade/update software and DB configuration (it is the same option for upgrading the software with a new release).
When requested, select the check “enter schema credentials and update metadata mappings” as shown below.
ç
After that, you can select your specific table / attribute and will use the same mappings configuration screen shown above.