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
>
_______________________________________________
Architecture mailing list
Architecture@wso2.org
https://mail.wso2.org/cgi-bin/mailman/listinfo/architecture
