Hi,
Rather going with two-step query model and frontend calculations, could you
also check whether we can publish these data from APIM to SP and
using aggregation calculate the values required for the graphs?
Thanks,
SajithD
On Thu, Nov 15, 2018 at 5:13 PM Niveathika Rajendran <niveath...@wso2.com>
wrote:
> Hi Fazlan,
>
> As per the offline discussion with Sandalu, we have decided to go with 2
> step query approach for now since, we do not have a feature which allows
> running queries on top of a database from Widget level, as this is a
> security vulnerability. In this case, the entire database would have to be
> exposed rather than a single table.
>
> As of now, the issue was that *applicationId* needed for the query is
> available in a separate table. This table "AM_SUBSCRIPTION" can be queried
> first to get the applicationId, after which the needed table can be queried
> for the data needed in the widgets.
>
>
> Best Regards,
> *Niveathika Rajendran,*
> *Senior Software Engineer.*
> *Mobile : +94 077 903 7536*
>
>
>
>
>
> On Thu, Nov 15, 2018 at 4:55 PM Fazlan Nazeem <fazl...@wso2.com> wrote:
>
>> Analytics team,
>>
>> Will we have a solution for joining multiple tables from a widget config
>> in a future release? If not, is there an alternative solution.
>>
>> On Thu, Nov 15, 2018 at 11:58 AM Sandalu Kalpanee <sand...@wso2.com>
>> wrote:
>>
>>> Hi all,
>>>
>>> A small addition to the above mail.
>>>
>>> For the above graph, I just need to get the count(APPLICATION_ID) and
>>> CREATED_TIME from AM_APPLICATION table which will be filtered using AM_API,
>>> AM_SUBSCRIBER, AM_SUBSCRIPTION tables.
>>>
>>> *AM_API (API_ID int,API_PROVIDER string,API_NAME string,API_VERSION
>>> string,CONTEXT string,CONTEXT_TEMPLATE string,API_TIER string,CREATED_BY
>>> string,CREATED_TIME string,UPDATED_BY string,UPDATED_TIME string)*
>>>
>>> *AM_APPLICATION (APPLICATION_ID int,NAME string,SUBSCRIBER_ID
>>> int,APPLICATION_TIER string,CALLBACK_URL string,DESCRIPTION
>>> string,APPLICATION_STATUS string,GROUP_ID string,CREATED_BY
>>> string,CREATED_TIME string,UPDATED_BY string,UPDATED_TIME string,UUID
>>> string,TOKEN_TYPE string)*
>>>
>>> *AM_SUBSCRIPTION (SUBSCRIPTION_ID int,TIER_ID string,API_ID
>>> int,LAST_ACCESSED string,APPLICATION_ID int,SUB_STATUS
>>> string,SUBS_CREATE_STATE string,CREATED_BY string,CREATED_TIME
>>> string,UPDATED_BY string,UPDATED_TIME string,UUID string)*
>>>
>>> *AM_SUBSCRIBER (SUBSCRIBER_ID int,USER_ID string,TENANT_ID
>>> int,EMAIL_ADDRESS string,DATE_SUBSCRIBED string,CREATED_BY
>>> string,CREATED_TIME string,UPDATED_BY string,UPDATED_TIME string)*
>>>
>>> Thanks.
>>>
>>> On Thu, Nov 15, 2018 at 8:36 AM Sandalu Kalpanee <sand...@wso2.com>
>>> wrote:
>>>
>>>> Hi All,
>>>>
>>>> I came up with an issue while working on my publisher dashboard widgets
>>>> creation.
>>>>
>>>> *Issue:* No option to join tables in siddhi queries.
>>>> *Widget:* Applications Created Over Time + (Other widgets which will
>>>> consume AM_DB data)
>>>> *Data source:* I am using Siddhi store data provider with the shared
>>>> AM_DB. (In the deployment.yaml file I have added a data source
>>>> referring the AM_DB at APIM side.)
>>>>
>>>> ************ sample providerConfig in widgetConf.json ****************
>>>> "type": "SiddhiStoreDataProvider",
>>>> "config": {
>>>> "siddhiApp": "@App:name('App Name') @primaryKey('Primary Key')
>>>> @store(type=\"rdbms\" , datasource=\"AM_DB\") define table TableName
>>>> (Column
>>>> Headings with Types) ;",
>>>> "queryData": {
>>>> "query": "Query"
>>>> },
>>>>
>>>> *
>>>> ****************************************************************************
>>>>
>>>> *Required Widget View: *
>>>>
>>>> According to the below figure the first combo(All APIs or My APIs) will
>>>> provide the user an option to filter the API list in the third combo. API
>>>> list inside the third combo will be populated by AM_API table using a
>>>> separate query in the widgetConf.json. The second combo will list down the
>>>> APP creators which will be populated by AM_subscriber table using another
>>>> query. And according to the selected options in these combos the data will
>>>> be filtered from AM_APPLICATION table.
>>>>
>>>> [image: App-Registrations.png]
>>>>
>>>> *Query used to generate the graph in APIM 2.6 version: *
>>>> https://github.com/wso2/carbon-apimgt/blob/v6.4.50/components/apimgt/org.wso2.carbon.apimgt.usage/org.wso2.carbon.apimgt.usage.client/src/main/java/org/wso2/carbon/apimgt/usage/client/UsageClient.java#L331
>>>>
>>>> Ex: If a user has selected options other than 'All' for both second and
>>>> third combos, the data should be filtered from AM_APPLICATION table joining
>>>> with 3 other tables AM_API, AM_SUBSCRIBER, and AM_SUBSCRIPTION.
>>>>
>>>> But as there is no way to join tables using siddhi queries (And as
>>>> RDBMS batch data provider will not be OK with changing DB types), there
>>>> will be an issue to generate widgets which consumes data from AM_DB (This
>>>> issue doesn't count for the widgets generated by STAT_DB since the data
>>>> will be extracted from a single table).
>>>>
>>>> Highly appreciated if you can suggest any solution for this. Thanks in
>>>> advance.
>>>>
>>>> Best Regards,
>>>> *Sandalu Kalpanee*
>>>> *Software Engineer - Intern*
>>>> *WSO2*
>>>>
>>>
>>>
>>> --
>>> *Sandalu Kalpanee*
>>> *Software Engineer - Intern*
>>> *WSO2*
>>> _______________________________________________
>>> Architecture mailing list
>>> Architecture@wso2.org
>>> https://mail.wso2.org/cgi-bin/mailman/listinfo/architecture
>>>
>>
>>
>> --
>> Thanks & Regards,
>>
>> *Fazlan Nazeem*
>> Senior Software Engineer
>> WSO2 Inc
>> Mobile : +94772338839
>> fazl...@wso2.com
>> _______________________________________________
>> Architecture mailing list
>> Architecture@wso2.org
>> https://mail.wso2.org/cgi-bin/mailman/listinfo/architecture
>>
>
--
<http://wso2.com/signature>
Sajith Dimal
Senior Software Engineer
Email: saji...@wso2.com
Mobile: +94783101496
WSO2 Inc. | http://wso2.com
lean.enterprise.middleware
Disclaimer: This communication may contain privileged or other confidential
information and is intended exclusively for the addressee/s. If you are not
the intended recipient/s, or believe that you may have received this
communication in error, please reply to the sender indicating that fact and
delete the copy you received and in addition, you should not print, copy,
re-transmit, disseminate, or otherwise use the information contained in
this communication. Internet communications cannot be guaranteed to be
timely, secure, error or virus-free. The sender does not accept liability
for any errors or omissions.
_______________________________________________
Architecture mailing list
Architecture@wso2.org
https://mail.wso2.org/cgi-bin/mailman/listinfo/architecture
