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