Omnis Mysql defined list
Phil (OmnisList)
phil at pgpotter.co.uk
Fri Dec 16 17:58:25 EST 2016
Hey Doug,
Most of what you have can be put into a query class... in the column
name column, the alias is effectively the 'AS' part of a select,
"(select ...) as aliasname"
There used to be a limit of, I think it was 256 chars, in the 'column
name' column, which limited the case statement for example...
I hit against that one on a few occasions, and asked for TL to extend it.
I think in the latest O$8 it is bigger, I just tried and it seems to be
5390 char long.. so maybe that is no longer an issue these days.
I get the table superclass idea though, where the code is in one place.
Interesting stuff.
and for sure I agree, make the SQL server do most of the work, if it can.
I don't know if the time zone stuff works in a query, never tried it...
suspect it would just work the same though
maybe:
Column name = F_CLIENT.C_DATE_UPDATED at time zone 'Canada/Mountain’
Alias = C_DATE_UPDATED
Thanks for your detailed description.
regards
Phil Potter
Based in Chester in the UK.
On 16/12/2016 17:52, Doug Easterbrook wrote:
> hi Phil:
>
> sometimes what you know or aware of dictate the way you write your code. In my case, I was not aware that you could add calculated columns in a query by sticking something in the alias. If that is the case, it may be more elegant than our solution.
>
>
> We opted to put substitutions into the table class because it seemed to make more sense. We can inherit table classes, which means we can inherit methods, which means that there are some global substitutions that we make for all tables that I don’t need to stick into dozens of query classes.
>
> I think that makes the code more centralized and in one place (i.e. being in a base table class or the table class for each major table).
>
>
> it also lets us describe the query in any way we wish for emphasis, plus use substitution variables as part of the code. eg we can use begin text block and end text block to make a statement that get substituted — and the query can be very complex.
>
> it also makes it code we can share (since its a do $method kind of thing) — so we can easily get the sql to stick into background statements.
>
> and we take apart a bunch of the sql to do optimizations within the table class in the first place, so it just made sense.
>
>
> I pulled out one of our sql statements that gets our client data into the main client list and have put it below. I’m not sure we could have done this in a query class (and I stand corrected if we could - I just didn’t know about it).
>
> but it shows a sql statement using
> - functions
> - sub selects
> - joins
> -coalesces
> -recasting of timestamps to particular timezones so that it works in background threads (our app is timezone aware)
> -use of lookup tables
> -conditionally adding values based on employee permissions to use certain tables.
>
>
> yes, it looks complicated but this is the result of a long long march towards making the application responsive for the end user.
>
> when we did these calculations in omnis code and filled a loop, it might take 10 to 30 SECONDS to fill a 100-200 row table, if thats all the customer searched for. if they asked for 10,000 rows, it might take 15 minutes to deal with the data.
>
>
> using SQL and calculations, we can get 10,000 rows back in milliseconds and there is no exponential decay in performance based on amount of data … I think thats the PURPOSE of going this as per stephens original email — how to make things faster and avoid filling data in omnis lists.
>
>
> perhaps we took the concept a little too far, but I sure like the consistently fast response time we get from the database
>
> and since the substitutions are centrally placed — makes code support easier.
>
>
>
>
> so….. this is a 17+ way table join in one sql statement — its very fast. with substitutions for variables that don’t exist in the database. hope it gives an idea of the power of the concept and why we did what we did.
>
>
>
>
>
>
> SELECT
> getClientListIcon(F_CLIENT,fHousehold,CASE WHEN getSecurityLevelDirect(5,'V','FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF') AND getSecurityAllowDirect(42,'111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111') AND fMarketing.MKT_DON_RESTRICTED THEN fMarketing.MKT_BALANCE ELSE fMarketing.MKT_BALANCE + fMarketing.MKT_DON_BALANCE END,1) AS gPrimaryIcon,
>
> F_CLIENT.C_SEQ,
> (SELECT FC_RESULT1_NAME FROM F_CODETABLE WHERE FC_SEQ = F_CLIENT.C_PATRON_TYPE_FC_SEQ) AS gPatronType,
> F_CLIENT.C_FIRST_NAME,F_CLIENT.C_LAST_NAME,F_CLIENT.C_COMPANY,fAddresses.AD_ADDRESS1,
> COALESCE(fContactInfo.CI_CONTENTS,'') AS gPrimaryPhone,
> COALESCE((SELECT CI_CONTENTS FROM fContactInfo WHERE CI_SEQ = fMarketing.MKT_PRIMARY_FAX_CI_SEQ),'') AS gPrimaryFax,
> COALESCE((SELECT CI_CONTENTS FROM fContactInfo WHERE CI_SEQ = fMarketing.MKT_PRIMARY_EMAIL_CI_SEQ),'') AS gPrimaryEmail,
> COALESCE((SELECT CI_CONTENTS FROM fContactInfo WHERE CI_SEQ = fMarketing.MKT_PRIMARY_WEB_CI_SEQ),'') AS gPrimaryWeb,CASE WHEN getSecurityLevelDirect(5,'V','FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF') AND getSecurityAllowDirect(42,'111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111') AND fMarketing.MKT_DON_RESTRICTED THEN fMarketing.MKT_BALANCE ELSE fMarketing.MKT_BALANCE + fMarketing.MKT_DON_BALANCE
> END AS gBalance,
>
> COALESCE((select t0.C_FIRST_NAME || ' ' || t0.C_LAST_NAME FROM F_CLIENT t0, F_EMPLOYEE t1, fMarketing t2 where t0.C_SEQ = t1.E_C_SEQ and t1.E_SEQ = t2.MKT_E_SEQ and t2.MKT_C_SEQ = F_CLIENT.C_SEQ AND t2.MKT_D_DEPT_SEQ = t1.E_D_DEPT_SEQ AND t2.MKT_D_DEPT_SEQ=fMarketing.MKT_D_DEPT_SEQ),'<No Solicitor>') AS gSolicitor,
>
> fAddresses.AD_CITY,fAddresses.AD_PROVINCE,fMarketing.MKT_DO_NOT_MAILP,fMarketing.MKT_DO_NOT_MAILT,fMarketing.MKT_E_SEQ,F_CLIENT.C_NAME_COMBINED,fAddresses.AD_ADDRESS2,F_CLIENT.C_FORMAL_NAME,F_CLIENT.C_SALUTATION,F_CLIENT.C_DECEASED,F_CLIENT.C_TITLE,F_CLIENT.C_INITIAL,F_CLIENT.C_SUFFIX,fAddresses.AD_SEQ,fHousehold.H_SEQ,fHousehold.H_PRIMARY_C_SEQ,fMarketing.MKT_PRIMARY_PHONE_CI_SEQ,fMarketing.MKT_PRIMARY_EMAIL_CI_SEQ,fMarketing.MKT_PRIMARY_FAX_CI_SEQ,fMarketing.MKT_PRIMARY_WEB_CI_SEQ,fMarketing.MKT_D_DEPT_SEQ,F_CLIENT.C_H_SEQ,fAddresses.AD_COUNTRY,fAddresses.AD_POSTAL_CODE,fMarketing.MKT_FIELD_1,fMarketing.MKT_FIELD_2,fMarketing.MKT_FIELD_3,fMarketing.MKT_FIELD_4,fMarketing.MKT_FIELD_5,
>
> (SELECT FC_RESULT1_NAME FROM F_CODETABLE WHERE FC_SEQ = fMarketing.MKT_FIELD_6_FC_SEQ) AS gC_MKT6result,
> (SELECT FC_RESULT1_NAME FROM F_CODETABLE WHERE FC_SEQ = fMarketing.MKT_FIELD_7_FC_SEQ) AS gC_MKT7result,
> (SELECT FC_RESULT1_NAME FROM F_CODETABLE WHERE FC_SEQ = fMarketing.MKT_FIELD_8_FC_SEQ) AS gC_MKT8result,
> (SELECT FC_RESULT1_NAME FROM F_CODETABLE WHERE FC_SEQ = fMarketing.MKT_AGE_CODE_FC_SEQ) AS gC_AGE_CODEresult,
>
> F_CLIENT.C_DATE_UPDATED at time zone 'Canada/Mountain’,
> fMarketing.MKT_DATE_UPDATED at time zone 'Canada/Mountain’,
>
> fMarketing.MKT_DONOR,fMarketing.MKT_VOLUNTEER,fMarketing.MKT_PRESS,fMarketing.MKT_EMPLOYEE_E_SEQ,F_CLIENT.C_PROFILE_TYPE,F_CLIENT.C_EXTERNAL_SEARCH_FIELD,fAddresses.AD_LATITUDE,fAddresses.AD_LONGITUDE,fAddresses.AD_GEOCODE_SOURCE,fMarketing.MKT_DO_NOT_DELETE,F_CLIENT.C_BIRTHDAY,fAddresses.AD_POSTAL_SHORT,F_CLIENT.C_PATRON_TYPE_FC_SEQ FROM F_CLIENT INNER JOIN fHousehold ON F_CLIENT.C_H_SEQ=fHousehold.H_SEQ INNER JOIN fMarketing ON F_CLIENT.C_SEQ=fMarketing.MKT_C_SEQ LEFT OUTER JOIN fAddresses ON fMarketing.MKT_PRIMARY_AD_SEQ=fAddresses.AD_SEQ LEFT OUTER JOIN F_EMPLOYEE ON fMarketing.MKT_E_SEQ=F_EMPLOYEE.E_SEQ LEFT OUTER JOIN fContactInfo ON fMarketing.MKT_PRIMARY_PHONE_CI_SEQ=fContactInfo.CI_SEQ
>
> where F_CLIENT.C_NAME_COMBINED ILIKE '%SMITH%' AND MKT_D_DEPT_SEQ = 1
>
> ORDER BY F_CLIENT.C_LAST_NAME DESC, F_CLIENT.C_SEQ ASC
>
>
>
>
>
> and an example of omnis code where we do substitutions — you can see the elements of the code below in the sql above where there is parameter/variable substitution into the sql — which is a capability I don't think you’d get in a query class
>
>
>
>
> ; gPrimaryIcon
> ; V100201 only show for current employee dept.
> Calculate Query as con("getClientListIcon(F_CLIENT,fHousehold,CASE WHEN getSecurityLevelDirect(5,'V','",tEmployee.E_ACCESS_LEVEL,"') AND getSecurityAllowDirect(42,'",tEmployee.E_ACCESS_ALLOW,"') AND fMarketing.MKT_DON_RESTRICTED THEN fMarketing.MKT_BALANCE ELSE fMarketing.MKT_BALANCE + fMarketing.MKT_DON_BALANCE END,",tEmployee.E_D_DEPT_SEQ,") AS gPrimaryIcon”)
>
> Do tStringFields.$replace(pSelectNames,'F_APPLICATION.gPrimaryIcon',Query,pSelectNames)
>
> ; gPatronType
> Calculate Query as "(SELECT FC_RESULT1_NAME FROM F_CODETABLE WHERE FC_SEQ = F_CLIENT.C_PATRON_TYPE_FC_SEQ) AS gPatronType”
>
> Do tStringFields.$replace(pSelectNames,'F_APPLICATION.gPatronType',Query,pSelectNames)
>
> ; gC_MKT6result
> Calculate Query as "(SELECT FC_RESULT1_NAME FROM F_CODETABLE WHERE FC_SEQ = fMarketing.MKT_FIELD_6_FC_SEQ) AS gC_MKT6result”
>
> Do tStringFields.$replace(pSelectNames,'F_APPLICATION.gC_MKT6result',Query,pSelectNames)
>
>
>
>
>
> don’t know if that helps explain why we did what we did..
>
>
>
>
> Doug Easterbrook
> Arts Management Systems Ltd.
> mailto:doug at artsman.com
> http://www.artsman.com
> Phone (403) 650-1978
>
>> On Dec 16, 2016, at 7:23 AM, Phil (OmnisList) <phil at pgpotter.co.uk> wrote:
>>
>> Hi Doug,
>>
>> Yes, thanks, it was literally just the double lines I'd missed before now.
>>
>> But I don't understand why adding your code is any better than just putting. ie a sub query, directly into a query class... in the 'Column name' column, and then putting in the required column name in the Alias...
>>
>> Yes, there are limits to how much you can type into a query class, but in most cases it is long enough?
>>
>> Why is what your suggesting any better?
>>
>> Or have I just missed the point of the original question?
>>
>> regards
>> Phil Potter
>> Based in Chester in the UK.
>>
>> On 16/12/2016 13:31, Doug Easterbrook wrote:
>>> hi Phil.
>>>
>>> || is concatenate strings. I just picked that as an example, but you can use anything
>>>
>>>
>>> we even use conditional constructs to fill in some lookup table values and return as strings
>>>
>>> case when A = 1 then B when A=2 then C else Y end
>>>
>>>
>>> or my favourite coalesce( )
>>>
>>>
>>> and we also use a fair number of subselects within a select, so the variable gets replaced with the following — which is extremely helpful if you have more than one join to a lookup table from a main table
>>>
>>> (select x from anothertable where key=xx)
>>>
>>>
>>> in other words, the sky is the limit on the sql you can use with this technique
>>>
>>>
>>>
>>> Doug Easterbrook
>>> Arts Management Systems Ltd.
>>> mailto:doug at artsman.com
>>> http://www.artsman.com
>>> Phone (403) 650-1978
>>>
>>>> On Dec 16, 2016, at 1:53 AM, Phil (OmnisList) <phil at pgpotter.co.uk> wrote:
>>>>
>>>> Hi Doug,
>>>>
>>>> Not seen the double vertical lines before, what do they mean in this select?
>>>>
>>>> Is this a PostGreSQl thing only?
>>>>
>>>> regards
>>>> Phil Potter
>>>> Based in Chester in the UK.
>>>>
>>>> On 15/12/2016 23:43, Doug Easterbrook wrote:
>>>>> hi Stephen:
>>>>>
>>>>> we do something like this all the time — and do sql tricks to make it happen with the table class. The general philosophy is that you NEVER NEVER (ok, hardly ever) want to change lines in a list through calculations because it really affects performance.
>>>>>
>>>>>
>>>>> so, how can you do trick omnis to do this. ..
>>>>>
>>>>> Step 1: add variables to your query class that don’t exist in the database
>>>>>
>>>>> eg, query has firstName and LastName. both are in the database. you add variable called CombinedName — which will be a calculation
>>>>>
>>>>>
>>>>> Step 2:
>>>>>
>>>>> Override the methods in the table class that fetch your data. What you want to do is be able to get ahold of $SELECTNAMES just before it is sent to mysql.
>>>>>
>>>>> from the query class above, your $selectNames will have firstName,LastName,CombinedName Again combinedName is NOT in the database, so this will generate an SQL error — we know this..
>>>>>
>>>>>
>>>>>
>>>>> Step 3:
>>>>>
>>>>> write some code like this to make a calculation on any columns you want. We do it in postgres, so we’ve become adept and using stored procedures or any postgres function, calculations, date conversions or whatever we want to use
>>>>>
>>>>> replacement = ‘ lastName || firstName || length(firstName) as combinedName’
>>>>> and then
>>>>>
>>>>> calculate SQLSTRING as $replace(combinedname with replacement in $selectNames )
>>>>>
>>>>>
>>>>> now your SQL String (which was formerly $selectNames) is
>>>>>
>>>>> firstName,lastName , lastName || firstName || length(firstName) as combinedName
>>>>>
>>>>>
>>>>> and when you send that to the dam, you’ll get three columns back, one of which is calculated.
>>>>>
>>>>>
>>>>>
>>>>>
>>>>> that gives you all sorts of performance … and can also be used in background workers to get data without manipulating the list.
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>> hope that helps with the concept. like I said, we use it all the time.
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>> Doug Easterbrook
>>>>> Arts Management Systems Ltd.
>>>>> mailto:doug at artsman.com
>>>>> http://www.artsman.com
>>>>> Phone (403) 650-1978
>>>>>
>>>>>> On Dec 15, 2016, at 3:39 PM, Stephen Baugh <stephenbaugh at queensberry.com> wrote:
>>>>>>
>>>>>> Hi Everyone
>>>>>>
>>>>>> Is there a way to add columns to a .$definefromsqlclass list definition, so that a calculation is processed as the list is built?
>>>>>>
>>>>>> This might be a pick lookup or math function etc
>>>>>>
>>>>>> I want to add for example, the following calculations to this definition, after of course adding the new columns separately if required.
>>>>>>
>>>>>> Do $cinst.iWindowList.$definefromsqlclass($cinst.iRowTableName) Returns lvStatus
>>>>>>
>>>>>> Calculate Month as ovControl.$doDivide(C_SUPP_BAL_MTH,REG_EXCHANGE_RATE)
>>>>>> Calculate Current as ovControl.$doDivide(C_SUPP_BAL_CUR,REG_EXCHANGE_RATE)
>>>>>> Calculate 30days as ovControl.$doDivide(C_SUPP_BAL_30,REG_EXCHANGE_RATE)
>>>>>> Calculate 60days as ovControl.$doDivide(C_SUPP_BAL_60,REG_EXCHANGE_RATE)
>>>>>> Calculate 90days as ovControl.$doDivide(C_SUPP_BAL_90,REG_EXCHANGE_RATE)
>>>>>>
>>>>>> Both C_SUPP_BAL_MTH and REG_EXCHANGE_RATE are defined in the list definition.
>>>>>>
>>>>>> I hope this makes sense, and as always thanks for the help.
>>>>>>
>>>>>> Cheers
>>>>>> Stephen
>>>>>>
>>>>>>
>>>>>>
>>>>>> _____________________________________________________________
>>>>>> Manage your list subscriptions at http://lists.omnis-dev.com
>>>>> _____________________________________________________________
>>>>> Manage your list subscriptions at http://lists.omnis-dev.com
>>>> _____________________________________________________________
>>>> Manage your list subscriptions at http://lists.omnis-dev.com
>>>
>>> _____________________________________________________________
>>> Manage your list subscriptions at http://lists.omnis-dev.com
>> _____________________________________________________________
>> Manage your list subscriptions at http://lists.omnis-dev.com
>
>
> _____________________________________________________________
> Manage your list subscriptions at http://lists.omnis-dev.com
More information about the omnisdev-en
mailing list