Simplified view of custom data

Support for Microsoft SQL Server in the context of Jiwa installations.

Simplified view of custom data

Postby pricerc » Tue Jan 14, 2020 2:18 pm

Because of the way that custom data is stored in JIWA, querying it can be a bit of a pain.

To ease that pain, I wrote myself a little procedure that will generate a view for JIWA custom data that will allow you to write simple SQL queries against it. Or to allow you (e.g. from within a plugin) to easily get all the custom data for a business object with a single query against the database.

It's imperfect, because it doesn't look at the plugin id, but I always make a point of making the setting names unique across plugins (for a given business object), so it works for me.

I've implemented it as a stored procedure. Even though I'd only use it a couple of times when doing a new implementation, saving it as a stored procedure means it's already there if I need to do it again later.

You have to provide values for:
    @settingTableName - the name of the settings table. e.g. IN_CustomSetting
    @valuesTableName - the name of the related values table e.g. IN_CustomSettingValues
    @keyFieldName - the name of the business object key field within the setting table. e.g. InventoryID
    @viewSchema - the schema to use for the new view. e.g. 'dbo'
    @viewName - the name of the new view - e.g. IN_CustomData

the stored procedure :
Code: Select all
CREATE  PROC [AERP_GenerateCustomDataView](@settingTableName sysname, @valuesTableName sysname, @keyFieldName sysname, @viewSchema sysname, @viewName sysname) AS
begin

   declare @sql nvarchar(MAX) = N'SELECT @sql2=N''CREATE VIEW ' + QUOTENAME(@viewSchema) + N'.' + QUOTENAME(@viewName) + N' AS
   SELECT
   ' + QUOTENAME(@keyFieldName) + N''' +
   replace((SELECT ''
   ,'' + quotename([SettingName]) + '' = max(CASE WHEN RTRIM([SettingID]) = '''''' + RTRIM(SettingID) + '''''' THEN Contents ELSE '''''''' END)''
                        FROM ' + QUOTENAME(@settingTableName) + N'
                  ORDER BY DisplayOrder
                        FOR XML PATH('''')
    ), ''
'','''') + N''
   FROM ' + QUOTENAME(@valuesTableName) + N'
   WHERE COALESCE(Contents,'''''''') <> ''''''''
   GROUP BY ' + QUOTENAME(@keyFieldName) + N'
   '''

   --print @sql;
   declare @sql2 nvarchar(MAX);
   execute sp_executesql @sql, N'@sql2 nvarchar(max) OUTPUT', @sql2 OUTPUT;

   print @sql2;
   execute sp_executesql @sql2;

end


This should work on any version of SQL that has nvarchar(MAX) support (2005+ IIRC).

calling it:
Code: Select all
EXECUTE [AERP_GenerateCustomDataView]
   @settingTableName = 'IN_CustomSetting'
  ,@valuesTableName = 'IN_CustomSettingValues'
  ,@keyFieldName = 'InventoryID'
  ,@viewSchema = 'dbo'
  ,@viewName = 'IN_CustomData'


The view it generates will look like a bit like this, based on a technique I learnt from SqlServerCentral.com. (this is for the JiwaDemo database) :
Code: Select all
CREATE OR ALTER VIEW [dbo].[IN_CustomData] AS
   SELECT
   [InventoryID]
   ,[product_id] = max(CASE WHEN RTRIM([SettingID]) = '257c204d-240d-4b33-8b1d-436e6f300c11' THEN Contents ELSE '' END)
   ,[ComboBox] = max(CASE WHEN RTRIM([SettingID]) = '1' THEN Contents ELSE '' END)
   ,[Sample Custom Field 1] = max(CASE WHEN RTRIM([SettingID]) = '8d0a674cbb9d4e7995c5' THEN Contents ELSE '' END)
   ,[Discontinue Date] = max(CASE WHEN RTRIM([SettingID]) = 'accaafadf2744904bbe9' THEN Contents ELSE '' END)
   ,[WebBrowserURL] = max(CASE WHEN RTRIM([SettingID]) = 'b450e24b-0b83-4650-b812-311f26dfeeb2' THEN Contents ELSE '' END)
   ,[Date Field] = max(CASE WHEN RTRIM([SettingID]) = '2' THEN Contents ELSE '' END)
   ,[CheckBox] = max(CASE WHEN RTRIM([SettingID]) = '3' THEN Contents ELSE '' END)
   ,[Sample Button] = max(CASE WHEN RTRIM([SettingID]) = '4' THEN Contents ELSE '' END)
   ,[Sample text] = max(CASE WHEN RTRIM([SettingID]) = '5' THEN Contents ELSE '' END)
   ,[Sample Integer] = max(CASE WHEN RTRIM([SettingID]) = '6' THEN Contents ELSE '' END)
   ,[Sample float] = max(CASE WHEN RTRIM([SettingID]) = '7' THEN Contents ELSE '' END)
   FROM [IN_CustomSettingValues]
   WHERE COALESCE(Contents,'') <> ''
   GROUP BY [InventoryID]


a query would just be like querying a table:
Code: Select all
SELECT [InventoryID]
      ,[product_id]
      ,[ComboBox]
      ,[Sample Custom Field 1]
      ,[Discontinue Date]
      ,[WebBrowserURL]
      ,[Date Field]
      ,[CheckBox]
      ,[Sample Button]
      ,[Sample text]
      ,[Sample Integer]
      ,[Sample float]
  FROM [JiwaDemo].[dbo].[IN_CustomData]


From there, you can manually modify the view to:
  • change field names
  • convert data types - JIWA stores everything as text, but you can convert to whatever data type you want in the view. (the procedure could probably do this, but I seldom have a need for non-text versions of the data when it comes to custom data, so I haven't bothered, and it's trivial to do on the view itself where necessary).


P.S. this kind of view will perform better if your clustered index is on a combination of business object rec id and setting id. e.g. InventoryID, SettingID. This is because it will have all the related records on the same page in SQL (or at least on very few pages).
/Ryan

ERP Consultant,
Advanced ERP Limited, NZ
https://aerp.co.nz
User avatar
pricerc
Senpai
Senpai
 
Posts: 509
Joined: Mon Aug 10, 2009 12:22 pm
Location: Auckland, NZ
Topics Solved: 20

Re: Simplified view of custom data

Postby pricerc » Tue Jan 14, 2020 2:26 pm

If someone was really desperate, it's also technically possible to make these views 'writable'.

But it's what is euphemistically called a 'non-trivial' problem.

Basically, you need to write INSTEAD OF INSERT and INSTEAD OF UPDATE triggers on the view that then update the actual data underneath.

I've done it for a non-JIWA database that has a similar custom-data structure. It was gnarly, but did work.
/Ryan

ERP Consultant,
Advanced ERP Limited, NZ
https://aerp.co.nz
User avatar
pricerc
Senpai
Senpai
 
Posts: 509
Joined: Mon Aug 10, 2009 12:22 pm
Location: Auckland, NZ
Topics Solved: 20


Return to Microsoft SQL Server

Who is online

Users browsing this forum: No registered users and 1 guest

cron