Documentation Cacheable Tables

Overview: Cacheable DataTables

See Data Caching in the architectural overview of openPETRA.

Underlying Technology

The cacheable DataTables are stored in IsolatedStorage. see also [1] to clear all cached Tables.

The files are stored somewhere in the profile. see http://stackoverflow.com/questions/275536/where-is-net-isolated-storage-stored eg. Users\<user>\AppData\Roaming\IsolatedStorage

Generate Cacheable DataTables

Cacheable DataTables are generated with:

nant generateORMCachedTables

Defining cached tables and lists

The file csharp/ICT/Petra/Definitions/CacheableTablesAndLists.yaml contains DB tables and lists which should be cached.

All cached tables are defined in this one file. The first level is the module, the next deeper level is the submodule. After that, there is a different section for either tables that are straight from the database (DatabaseTables, eg. PMethodOfContact), and lists that are calculated with SQL or have added columns (CalculatedLists, eg. CountryListFromExistingLocations).

You can define the following Attributes and Values:

• Comment: if you don't want the default description from petra.xml to be used in the enum comment in Shared, you can define your own comment here by setting the Value of this Attribute. You definitely should add a comment for all calculated lists since there is no default comment for them.
• Enum: if the enum name should not be calculated from the table name (PMethodOfContact would become MethodOfContactList), you can specify an explicit enum name in the Value of that Attribute (eg. CurrencyCodeList for ACurrency)
• DependsOnLedger: Value: true/false. This is used for most finance tables because you only want to cache values of a specific ledger. This attribute can be defined on an element higher up in the yaml hierarchy and is inherited for all lower lines.
• WipeCacheTableAfterRefreshBeforeMerging: Value: true/false. Must be set to true for refreshing Cacheable DataTables whose Primary Key can be changed (such as 'CostCentreList' and 'AccountList') in order to not end up with duplicate entries (one with the old key, one with the new key).
• Only for 'calculated lists': IsStorableToDBTable: Allows a 'calculated list' to be saved to the database. The Value of that Element needs to be the name of the DB Table as used in OpenPetra C# code (eg. PPartnerAttributeCategory).

Files affected by the generation of Cacheable DataTables

• in Petra/Shared, for each module there is a Petra/Shared/lib/M<Module>/Cacheable.cs file autogenerated
• in Petra/Server, for each submodule there is a file autogenerated in Petra/Server/lib/M<Module>/<Submodule>.Cacheable.cs. If there is only one submodule, then the submodule name might be omitted from the filename.

ManualCode adjustments

For lists, you need to define how to calculate and fill the lists. Please find the Get...Table() functions at the bottom of the Server Cacheable file. Don't forget to define a primary key for calculated lists as otherwise it can happen that the client has problems with merging data from the server and therefore the list might grow each time it is referenced.

You also need to add using clauses to the top of the Server Cacheable files, inside a ManualCode section.

Updating/Invalidating a Cacheable DataTable

When data gets changed, then the cached table needs to be marked as invalid.

Procedure if this needs to be invoked on the server-side of OpenPetra:

reference 'Ict.Petra.Shared.dll' (for TCacheableTablesManager Class)

using Ict.Petra.Shared;

TCacheableTablesManager.GCacheableTablesManager.MarkCachedTableNeedsRefreshing(
    TCacheableFinanceTablesEnum.AccountList.ToString());

Procedure if this needs to be invoked on the client-side of OpenPetra:

reference 'Ict.Petra.Client.App.Core.dll' (for TDataCache Class)

using Ict.Petra.Client.App.Core;

TDataCache.ReloadCacheTable(TCacheableCommonTablesEnum.CountryList);  // for non Ledger-specific cacheable Tables
  -- or --
TDataCache.ReloadCacheTable(TCacheableFinanceTablesEnum.MotivationList, 43);  // for Ledger-specific cacheable Tables

Effect
The Cache Manager (server-side and client-side) defers the actual re-loading of a Cacheable Table as long as possible, but this is transparent to the programmer.

• If this is requested server-side a flag in the server-side Cache Manager class is set that the DataTable needs to get refreshed on next access. Also, all Clients but the one that triggered the refresh will get notified to refresh that Cacheable DataTable when it is next accessed.
• If this is requested client-side, this sets a flag in the client-side Cache Manager class that the DataTable needs to get refreshed on next access. When it is next accessed, the client-side Cache Manager makes a server call to get the most up-to-date data.