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 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/ > components/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
