How to Add a Table or Column to a Store Server Solution
When developing for a multi-server environment it is important to take some extra things into account. This document focuses on adding tables/columns which are used in a store and central server environment.
When you new tables/columns in your module (for a multi-server environment) you need to validate several things:
- should the new database schema elements be used in the store server
- should data in these new database schema elements be replicated from central to store server
- should data in these new tables/columns be replicated from store to central server
- should initial data be present when creating a store server
In general the following statements can be made:
- master data should be present when creating a store server
- master data is maintained centrally and should be replicated from central to the store server at runtime when it is changed centrally
- transactional data should be present when creating a store server
- transactional data should be replicated from store to central and vice versa at runtime
For replication complex transactional data from store <--> central you can also use a messaging approach as described in this howto. Or to let transaction processing go through/use the Import Entry processing logic.
This page discusses the replication approach.
Creating a table/column
The first step, the creation of the table/column is the same as for non-store server environments. So in your module add the table/column to the database schema and then define it in the Openbravo application dictionary.
The next step is to tell the synchronization engine that the table's content should be present when creating a store server and/or needs to be synced from central to store server at runtime.
Store Server Creation - Store Server DataSet
When adding new tables to your solution you need to validate if the data in these tables needs to be present when creating a store server. If so then your new table needs to be present in the store server dataset.
After adding your table to the store server dataset you need to validate if there are no (non-)mandatory references from your table to other tables which also need to be part of the initial store server creation.
Adding your table to the Store Server Creation DataSet
The first step is to add your table to the store server creation dataset:
- if you need to only export data for the specific store then add the SQL/HQL clause: ad_org_id in (@orgsInNaturalTree@)
- you can also add one table multiple times with different HQL/SQL clauses and different excluded columns (see next section)
Then next validate the store server dataset.
Validating the Store Server DataSet
To validate the store server creation dataset with your new table you select the store server dataset in the dataset window and then click the validate button in the top. Note, this button is only visible for the store server dataset.
The result of the validation is shown in the popup window. Note: there maybe several results use the scrollbar to scroll down in the popup window.
The validation checks in so-called strict mode, this means that also non-mandatory references are checked.
The validation does 2 types of checks:
- the first check is to see if all the properties of the entities in the dataset are referencing entities in the dataset itself or outside of it. If outside the dataset and not set as an excluded column then a warning is shown.
- the next check is to see if any non-mandatory properties referencing outside of the dataset can be added to the dataset.
There are 3 approaches to look at the WARN messages:
- the referenced entity should also be added to the dataset
- if you don't expect any data to be in the referenced tables (for non-mandatory references) you can ignore the message
- if you expect data but that data does not need to be in the store server then you need to exclude the column from the dataset by adding them to the excluded columns tab.
Excluding columns from store server creation
If the validation showed that some columns reference non-included tables then you can exclude these non-mandatory columns from initial store server creation. This needs to be a conscience decision.
To exclude a column from initial store server creation use the 'column' subtab.
Synchronizing data from central to store
To synchronize a table from central to a store server (after creating the store server) it needs to be added to the list of Synchronized tables.
The configuration of the tables to be synchronized is done in the Synchronized Table, available using the System Administrator role.
- Table: The table to be synchronized
- Channel: The synchronization channel that will to synchronize this table. Note that if the contents of the table reference rows from other synchronized tables, they will have to share the same channel.
- Synchronize Insertions/Updates/Deletions: This flags allow to specify if the insertions/updates/deletions should trigger synchronization events respectively. For instance, the C_IMPORT_ENTRY table does not synchronize the deletions, as each server will do the deletion themselves when they process the import entries.
- Synchronization Direction: Specifies the direction of the synchronization. The available options are Central Server to Store Server, Store Server to Central Server and Bidirectional.
- Filter Synchronization by Visible Organization. If this flag is checked, then only the records whose organization is visible by a server (this is defined in the Organizations by Server subtab of the Mobile Servers window) will be synchronized to that server.
- Excluded Columns: Include in this subtab the columns that should not be synchronized. Make sure to include a default value for those columns if they are not nullable.
Validate Synced Tables
After adding your table to the synced tables then you can validate the total set by selecting one of the synced tables and then selecting the validate button.
- the output is the same as for when validating the dataset. So warnings and information can be handled in the same way.
- The system only validates the central --> store synchronization direction. The direction from store --> central is not validated. This because often the store --> central direction only partial synchronization is defined and needed, so validation is not applicable there.
Most tables which are in the store server creation dataset should also be defined as a synchronized table and vice versa. This because if the data is needed when creating a store server it should probably also be updated when it changes in the central server. The same applies mostly also vice versa: a synchronized table should often be also present/filled when creating the store. Therefore the synchronized table validation also checks if there are tables which are not defined in the store server creation dataset or vice versa.
The screenshot above gives a good example of table which is not present in the store server creation dataset: an error table. Often log tables do not need to be filled when creating a server, but it makes sense to synchronize them at runtime. Therefore when validating the synchronized tables these will show up here.
Validate Dataset and Synced Tables
In addition to the validate option within the synced tables/dataset windows you can also validate directly from a process: 'Synced Tables and Datasets Validation'. When you open this process from quick launch the following popup opens:
After clicking ok the results are shown. Note that to view all the results you possibly need to scroll down in the popup.
Note the output is the same as for when validating the dataset. So warnings and information can be handled in the same way.