On Tue, Nov 14, 2017 at 11:23 AM, Sajith Perera <[email protected]> wrote:
> Yes, as Lasantha explained queries depend on the database. We had a > similar problem earlier and we provided separate query file with database > name and version[1]. > > AFAIK we cast datasource into HikariDataSource and it provides these > additional meta info. > We can load meta data from the connection[2] > Great! > > > [1] - "Issue when running mysql db script - APIM analytics HA setup" > [2] - https://github.com/wso2/carbon-dashboards/blob/ > 4860db4839c76e11929ac5450e61659495a8388a/components/ > dashboards/org.wso2.carbon.dashboards.core/src/main/java/ > org/wso2/carbon/dashboards/core/internal/database/ > DashboardMetadataDaoFactory.java#L64 > > > On Tue, Nov 14, 2017 at 10:28 AM, Lasantha Samarakoon <[email protected]> > wrote: > >> Why do we really need type and version? Are we going to consume these >> data?, if so how? >> >> We need to have DB type and version since the format of SQL queries vary >> among database types and their versions (ex: LIMIT query is different from >> DB type-to-type and there are some SQL syntax differences between oracle 11 >> and 12). >> >> IMO we don't need them. Let me explain why. We use carbon-datasources to >> load data sources. Data source name is the *only* parameter we need [1] to >> load a particular DataSource via carbon-datasources. Therefore type and >> version are not needed to acquire a DataSource instance. >> >> This is not to load datasources. We use the datasource name to load the >> datasource. But the user can change the underlying database (we ship H2 but >> user changes that to MSSQL). What we do here is load the carbon-datasources >> as it is, then get the undelying DB type and the version and then pick >> relevant query to execute. >> >> Also, we cannot get the database type, DBMS version, and other >> meta-information via the DataSource [2] object. Which means, we cannot >> select queries from type and version. >> >> AFAIK we cast datasource into HikariDataSource and it provides these >> additional meta info. >> >> >> *Lasantha Samarakoon* | Software Engineer >> WSO2, Inc. >> #20, Palm Grove, Colombo 03, Sri Lanka >> <https://maps.google.com/?q=20,+Palm+Grove,+Colombo+03,+Sri+Lanka&entry=gmail&source=g> >> Mobile: +94 (71) 214 1576 <+94%2071%20214%201576> >> Email: [email protected] >> Web: www.wso2.com >> >> lean . enterprise . middleware >> >> On Tue, Nov 14, 2017 at 8:45 AM, SajithAR Ariyarathna <[email protected]> >> wrote: >> >>> >>> >>> On Wed, Oct 25, 2017 at 12:26 AM, Sajith Perera <[email protected]> >>> wrote: >>> >>>> Hi, >>>> >>>> Yes, by default we will not have the query templates in the >>>> "deployment.yaml" file, all the default values are packed with the >>>> component jar itself and if a user required to override the default queries >>>> or adding queries for new database type it can be done through this >>>> component (by adding query configuration under the component namespace in >>>> deployment.yaml file) >>>> >>>> Also, today we had a design review for further discussion, please find >>>> the below meeting notes: >>>> >>>> Attendees: Suho, Mohan, SajithR, Tishan, SajithAR, LasanthaA, Minudika, >>>> Grainier >>>> >>>> Notes: >>>> - Define the common bean class which can use for other components while >>>> reading the yaml configuration in default configuration file and >>>> deployment.yaml files. >>>> - Each component should have default query.yaml file for define the >>>> defaults values. >>>> - The component should pass the default values and values read from the >>>> deployment.yaml to the query manager with the same bean class >>>> - The query manager component merges them by giving priority to the >>>> values in deployment.yaml, >>>> then it will return the merged query with the same bean class. >>>> >>>> >>>> Sample configuration would be deployment.yaml: >>>> >>>> wso2.dashboard: >>>> queries: >>>> - >>>> type: h2 >>>> version: null >>>> >>>> Why do we really need type and version? Are we going to consume these >>> data?, if so how? >>> >>> IMO we don't need them. Let me explain why. We use carbon-datasources to >>> load data sources. Data source name is the *only* parameter we need [1] to >>> load a particular DataSource via carbon-datasources. Therefore type and >>> version are not needed to acquire a DataSource instance. Also, we cannot >>> get the database type, DBMS version, and other meta-information via the >>> DataSource [2] object. Which means, we cannot select queries from type >>> and version. >>> >>> Instead of type and version, my suggestion is to use the data source >>> name as the identifier of a set of queries. >>> >>> e.g. deployment.yaml >>> >>> wso2.dashboard: >>> dataSource: WSO2_DASHBOARD_DB_H2 >>> queries: >>> WSO2_DASHBOARD_DB_H2: >>> countQuery: "SELECT .." >>> createTableQuery: "CREATE TABLE .." >>> deleteQuery: "DELETE FROM {{TABLE_NAME}} ..." >>> >>> >>> WDYT? >>> >>> [1] https://github.com/wso2/carbon-datasources/blob/v1.1.4/c >>> omponents/org.wso2.carbon.datasource.core/src/main/java/org/ >>> wso2/carbon/datasource/core/api/DataSourceService.java#L33 >>> [2] https://docs.oracle.com/javase/8/docs/api/javax/sql/DataSource.html >>> >>>> mappings: >>>> countQuery: "SELECT .." >>>> createTableQuery: "CREATE TABLE .." >>>> deleteQuery: "DELETE FROM {{TABLE_NAME}} ..." >>>> >>>> >>>> Further, it was also decided that to do the POC with the >>>> carbon-dashboard component and conduct another review session. >>>> >>>> Please add anything if I missed. >>>> >>>> Regards, >>>> SajithD >>>> >>>> On Tue, Oct 24, 2017 at 10:46 AM, Miyuru Dayarathna <[email protected]> >>>> wrote: >>>> >>>>> Hi, >>>>> >>>>> I had an offline chat with Sajith on this feature. Because in DAS >>>>> 3.1.0 the query templates were located in >>>>> $DAS_HOME/repository/conf/analytics/rdbms-config.xml. >>>>> My concern was that bringing such configurations to "deployment.yaml" file >>>>> (e.g., $SP_HOME/conf/worker/deployment.yaml) may contribute to the >>>>> length of the "deployment.yaml" file. However, >>>>> (wso2.database.template.queries) is an optional config parameter >>>>> which means by default we will not have the query templates in the >>>>> "deployment.yaml" file. Rather they could be overridden if needed in the >>>>> "deployment.yaml". Hence it does not increase the length of the >>>>> "deployment.yaml" file unnecessarily. >>>>> >>>>> -- >>>>> Thanks, >>>>> Miyuru Dayarathna >>>>> Senior Technical Lead >>>>> Mobile: +94713527783 <+94%2071%20352%207783> >>>>> Blog: http://miyurublog.blogspot.com >>>>> >>>>> On Mon, Oct 23, 2017 at 1:12 AM, Sajith Perera <[email protected]> >>>>> wrote: >>>>> >>>>>> Hi All, >>>>>> >>>>>> Currently, some components in the Product SP uses query templates >>>>>> for executing the database operation with the different databases. Those >>>>>> queries have default values set up by the respective component itself. >>>>>> Also, it has a capability of overriding these functionalities using >>>>>> the deployment.yaml file. >>>>>> But as this kind of redundant works for each component, we are >>>>>> planned to unify the query configuration reader by implementing the >>>>>> common >>>>>> database query configuration reader manager. >>>>>> >>>>>> Let me explain the targeted implementation: >>>>>> >>>>>> 1. We identify that databases can have two metadata properties >>>>>> name and version. So, we have to map the template queries with >>>>>> the database name and version. >>>>>> 2. Each component should map their default query templates into >>>>>> above map. And provide it while initializing the Query Manager. >>>>>> 3. Other than the above map query manager required: >>>>>> 1. *Component Name*(1): a unique name given for each component >>>>>> where it will define the ownership of the template queries in the >>>>>> deployment.yaml. >>>>>> 2. *Database name and version* (2)(3): the required name and >>>>>> the version need to maintained in the current query entry key(this >>>>>> will be >>>>>> the default key in the base query map). >>>>>> 4. The query manager uses the carbon.config.provider service for >>>>>> access deployment.yaml and provide the Query Manager service for >>>>>> other components. >>>>>> 5. While initializing the *Query Manager* following sequence will >>>>>> be executed: >>>>>> 1. First, it will get the available *queries*(4) in the >>>>>> deployment.yaml file using the component name and then it will >>>>>> override the >>>>>> default config map( merge and insert any new configuration). >>>>>> 2. Then it will initialize *Query Config Reader* with the >>>>>> following capability: >>>>>> - *Base Query Map *This is the base query map which has all >>>>>> the available query configuration. This is the resulting map of >>>>>> above-mentioned query config merge operation. >>>>>> - *Current Query Key* This will maintained the current query >>>>>> key for user-selected database name and version in Base Query >>>>>> Map. >>>>>> - Method for altering the current query key. >>>>>> - Method for getting the query template. >>>>>> 3. Finally, it will return the Query Config Reader object. >>>>>> 6. The deployment.yaml entries will be as follows: >>>>>> >>>>>> wso2.database.template.queries: >>>>>> components: >>>>>> - >>>>>> name: test-component* -------------------------(1)* >>>>>> databases: >>>>>> - >>>>>> name: h2*------------------------(2)* >>>>>> version: ~*----------------------(3)* >>>>>> queries:-----------------------(4) >>>>>> countQuery: "SELECT COUNT(*) FROM {{TABLE_NAME}} WHERE >>>>>> siddhiAppName = ?" >>>>>> createTableQuery: "CREATE TABLE {{TABLE_NAME}} (id INT NOT >>>>>> NULL AUTO_INCREMENT, siddhiAppName VARCHAR(100), revision VARCHAR(100), >>>>>> snapshot BLOB, PRIMARY KEY (id))" >>>>>> deleteQuery: "DELETE FROM {{TABLE_NAME}} WHERE id IN >>>>>> (SELECT id FROM {{TABLE_NAME}} WHERE siddhiAppName = ? ORDER BY id ASC >>>>>> LIMIT ?)" >>>>>> insertTableQuery: "INSERT INTO {{TABLE_NAME}} >>>>>> (siddhiAppName, revision, snapshot) VALUES (?, ?, ?)" >>>>>> isTableExistQuery: "SELECT * FROM {{TABLE_NAME}} limit 1" >>>>>> selectLastQuery: "SELECT revision FROM {{TABLE_NAME}} >>>>>> WHERE siddhiAppName = ? ORDER BY id DESC LIMIT 1" >>>>>> selectTableQuery: "SELECT snapshot FROM {{TABLE_NAME}} >>>>>> WHERE revision = ? AND siddhiAppName = ?" >>>>>> - >>>>>> name: oracle >>>>>> version: 11g >>>>>> queries: >>>>>> countQuery: "SELECT COUNT(*) FROM {{TABLE_NAME}} WHERE >>>>>> siddhiAppName = ?" >>>>>> createTableQuery: "CREATE TABLE {{TABLE_NAME}} >>>>>> (siddhiAppName VARCHAR2(100) NOT NULL, revision VARCHAR2(100), snapshot >>>>>> BLOB)" >>>>>> deleteQuery: "DELETE {{TABLE_NAME}} WHERE revision in >>>>>> (SELECT revision from (SELECT * FROM {{TABLE_NAME}} WHERE siddhiAppName >>>>>> = ? ORDER BY revision ASC) WHERE rownum<=?)" >>>>>> insertTableQuery: "INSERT INTO {{TABLE_NAME}} >>>>>> (siddhiAppName, revision, snapshot) VALUES (?, ?, ?)" >>>>>> isTableExistQuery: "SELECT 1 FROM {{TABLE_NAME}} WHERE >>>>>> rownum=1" >>>>>> selectLastQuery: "SELECT revision FROM (SELECT revision >>>>>> FROM {{TABLE_NAME}} WHERE siddhiAppName = ? ORDER BY revision DESC) >>>>>> WHERE rownum=1" >>>>>> selectTableQuery: "SELECT snapshot FROM {{TABLE_NAME}} >>>>>> WHERE revision = ? AND siddhiAppName = ?" >>>>>> >>>>>> Thank You >>>>>> -- >>>>>> <http://wso2.com/signature> >>>>>> Sajith Dimal >>>>>> Software Engineer >>>>>> Email : [email protected] >>>>>> Mobile : +94783101496 >>>>>> WSO2 Inc. | http://wso2.com >>>>>> lean.enterprise.middleware >>>>>> >>>>>> _______________________________________________ >>>>>> Architecture mailing list >>>>>> [email protected] >>>>>> https://mail.wso2.org/cgi-bin/mailman/listinfo/architecture >>>>>> >>>>>> >>>>> >>>>> >>>>> -- >>>>> Thanks, >>>>> Miyuru Dayarathna >>>>> Senior Technical Lead >>>>> Mobile: +94713527783 <+94%2071%20352%207783> >>>>> Blog: http://miyurublog.blogspot.com >>>>> >>>> >>>> >>>> >>>> -- >>>> <http://wso2.com/signature> >>>> Sajith Dimal >>>> Software Engineer >>>> Email : [email protected] >>>> Mobile : +94783101496 >>>> WSO2 Inc. | http://wso2.com >>>> lean.enterprise.middleware >>>> >>>> _______________________________________________ >>>> Architecture mailing list >>>> [email protected] >>>> https://mail.wso2.org/cgi-bin/mailman/listinfo/architecture >>>> >>>> >>> >>> >>> -- >>> Sajith Janaprasad Ariyarathna >>> Senior Software Engineer; WSO2, Inc.; http://wso2.com/ >>> <https://wso2.com/signature> >>> >> >> >> _______________________________________________ >> Architecture mailing list >> [email protected] >> https://mail.wso2.org/cgi-bin/mailman/listinfo/architecture >> >> > > > -- > <http://wso2.com/signature> > Sajith Dimal > Software Engineer > Email : [email protected] > Mobile : +94783101496 > WSO2 Inc. | http://wso2.com > lean.enterprise.middleware > -- Sajith Janaprasad Ariyarathna Senior Software Engineer; WSO2, Inc.; http://wso2.com/ <https://wso2.com/signature>
_______________________________________________ Architecture mailing list [email protected] https://mail.wso2.org/cgi-bin/mailman/listinfo/architecture
