Hi Fazlan, In general, we should be trying to fetch the required data using Siddhi provider.
Going forward our recommendation is to use Siddhi provider due to reasons such as it's not coupled with a specidic database and provides protection for vulnerabilities such as SQL injection. Thanks *,Sajith Ravindra* Associate Technical Lead WSO2 Inc.; http://wso2.com lean.enterprise.middleware mobile: +94 77 2273550 blog: http://sajithr.blogspot.com/ <http://lk.linkedin.com/pub/shani-ranasinghe/34/111/ab> 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 >> >
_______________________________________________ Architecture mailing list Architecture@wso2.org https://mail.wso2.org/cgi-bin/mailman/listinfo/architecture
