This documentation is for WSO2 Stream Processor 4.0.0. View documentation for the latest release.

All docs This doc
                                                                                                                                                                                                                                           
||
Skip to end of metadata
Go to start of metadata

Datasources are configured in WSO2 SP for each runtime ( i.e., worker, editor, manager, dashboard) are defined in the <SP_HOME>/conf/<runtime>/deployment.yaml file. e.g., to configure a datasource in the worker runtime, relevant configurations need to be added to the <SP_Home>/conf/worker/deployment.yaml file. 

To view a sample datasource confuguration for each database type supported, click on the following links:

If the database driver is not an OSGI bundle, then it should be converted to OSGI (using jartobundle.sh) before placing it in the <SP_HOME>/lib directory. For detailed instructions, see Adding Third Party Non OSGi Libraries.

e.g., sh WSO2_SP_HOME/bin/jartobundle.sh ojdbc6.jar WSO2_SP_HOME/lib/

 H2
wso2.datasources:
 dataSources:
   - name: WSO2_TEST_DB
     description: The datasource used for test database
     jndiConfig:
       name: jdbc/WSO2_TEST_DB
     definition:
       type: RDBMS
      configuration:
        jdbcUrl: 'jdbc:h2:${sys:carbon.home}/wso2/${sys:wso2.runtime}/database/testdb;IFEXISTS=TRUE;DB_CLOSE_ON_EXIT=FALSE;LOCK_TIMEOUT=60000;MVCC=TRUE'
        username: root
        password: root
        driverClassName: org.h2.Driver
        maxPoolSize: 50
        idleTimeout: 60000
        connectionTestQuery: SELECT 1
        validationTimeout: 30000
        isAutoCommit: false
 MySQL
wso2.datasources:
 dataSources:
   - name: WSO2_TEST_DB
     description: The datasource used for test database
     jndiConfig:
       name: jdbc/WSO2_TEST_DB
     definition:
       type: RDBMS
       configuration: 
         jdbcUrl: jdbc:mysql://hostname:port/testdb
         username: root
         password: root
         driverClassName: com.mysql.jdbc.Driver
         maxPoolSize: 50
         idleTimeout: 60000
         connectionTestQuery: SELECT 1
         validationTimeout: 30000
         isAutoCommit: false
 POSTGRES
wso2.datasources:
 dataSources:
   - name: WSO2_TEST_DB
     description: The datasource used for test database
     jndiConfig:
       name: jdbc/WSO2_TEST_DB
     definition:
       type: RDBMS
      configuration:
        jdbcUrl: jdbc:postgresql://hostname:port/testdb
        username: root
        password: root
        driverClassName: org.postgresql.Driver
        maxPoolSize: 10
        idleTimeout: 60000
        connectionTestQuery: SELECT 1
        validationTimeout: 30000
        isAutoCommit: false
 Oracle

There are two ways to configure this database type. If you have a System Identifier (SID), use this (older) format:

jdbc:oracle:thin:@[HOST][:PORT]:SID

wso2.datasources:
 dataSources:
   - name: WSO2_TEST_DB
     description: The datasource used for test database
     jndiConfig:
       name: jdbc/WSO2_TEST_DB
     definition:
       type: RDBMS
       configuration:
         jdbcUrl: jdbc:oracle:thin:@hostname:port:SID
         username: testdb
         password: root
         driverClassName: oracle.jdbc.driver.OracleDriver
         maxPoolSize: 50
         idleTimeout: 60000
         connectionTestQuery: SELECT 1
         validationTimeout: 30000
         isAutoCommit: false

If you have an Oracle service name, use this (newer) format:

jdbc:oracle:thin:@//[HOST][:PORT]/SERVICE

wso2.datasources:
 dataSources:
   - name: WSO2_TEST_DB
     description: The datasource used for test database
     jndiConfig:
       name: jdbc/WSO2_TEST_DB
     definition:
       type: RDBMS
       configuration:
         jdbcUrl: jdbc:oracle:thin:@hostname:port/SERVICE
         username: testdb
         password: root
         driverClassName: oracle.jdbc.driver.OracleDriver
         maxPoolSize: 50
         idleTimeout: 60000
         connectionTestQuery: SELECT 1
         validationTimeout: 30000
         isAutoCommit: false

The Oracle driver need to be converted to OSGi (using jartobundle.sh) before put into WSO2_SP_HOME/lib directory. For detailed instructions, see Adding Third Party Non OSGi Libraries.


 MSSQL
wso2.datasources:
 dataSources:
   - name: WSO2_TEST_DB
     description: The datasource used for test database
     jndiConfig:
       name: jdbc/WSO2_TEST_DB
     definition:
       type: RDBMS
       configuration: 
         jdbcUrl: jdbc:sqlserver://hostname:port;databaseName=testdb
         username: root
         password: root
         driverClassName: com.microsoft.sqlserver.jdbc.SQLServerDriver
         maxPoolSize: 50
         idleTimeout: 60000
         connectionTestQuery: SELECT 1
         validationTimeout: 30000
         isAutoCommit: false

The following sections explain datasource requirment and how to change the default datasource in each components:

RDBMS Data Provider

Database Access
RDBMS provider is publishing records from RDBMS table into generated gadgets and also can configure to purging the records in table. For that required access for read and delete the records for user defined table in the database.
Mandatory/OptionalMandatory, if user select the datasource when generating the widget or uses already generated widgets which bind to RDBMS data provider in dashboard runtime.
Default Datasource NameSAMPLE_DB
Default DatabaseThe default 'H2' database location is '<PRODUCT_HOME>/wso2/dashboard/database/SAMPLE_DB'.
TablesThe default database shipped with sample table 'TRANSACTION_TABLE'
Schemas and Queries

Information about the default queries: Configuring Database Queries#RDBMSDataProviderRDBMSDataProvider

Schema for sample table: 'TRANSACTIONS_TABLE (creditCardNo VARCHAR(50), country VARCHAR(50), transaction VARCHAR(50), amount INT)'

Tested Database TypesH2, MySQL, Postgres, Mssql, Oracle 11g


Carbon Coordination 

Database AccessCarbon coordination supports zookeeper and RDBMS based coordination. In RDBMS coordination, database access is required for updating the heartbeats of the nodes. In addition, Database access is requried to update the coordinator and the other members in the cluster
Mandatory/OptionalMandatory, but Zookeeper coordination can also be used instead of RDBMS
Default Datasource Name

Carbon datasources is used. The default datasource varies depending on the deployment as follows:

Configuration documentation can be found in Configuring Cluster Coordination

TablesLEADER_STATUS_TABLE, MEMBERSHIP_EVENT_TABLE, REMOVED_MEMBERS_TABLE, CLUSTER_NODE_STATUS_TABLE
Schemas and Queries

Information about the default queries and schema: https://github.com/wso2/carbon-coordination/blob/v2.0.12/components/cluster-coordinator/org.wso2.carbon.cluster.coordinator/org.wso2.carbon.cluster.coordinator.rdbms/src/main/java/org/wso2/carbon/cluster/coordinator/rdbms/util/RDBMSConstants.java

Tested Database TypesMySQL


Stream Processor Core - Persistence 

Database AccessPersist the state of Siddhi Applications Periodically into the database, if user select org.wso2.carbon.stream.processor.core.persistence.DBPersistenceStore class in <SP_Home>/conf/<worker/manager>/deployment.yaml  file under  state.persistence see Configuring Database and File System State Persistence.
Mandatory/OptionalOptional, uses File system persistence as default.
Default Datasource NameN/A, user have to specify datasource to be used in persisting the state in < SP_Home >/ conf /<worker/manager>/deployment. yaml  file under  state.persistence > config > datasource
TablesN/A, user have to specify table name to be used in persisting the state in < SP_Home >/ conf /<worker/manager>/deployment. yaml  file under  state.persistence > config > table
Schemas and Queries

Information about the default queries and schema: https://github.com/wso2/carbon-analytics/blob/master/components/org.wso2.carbon.stream.processor.core/src/main/resources/queries.yaml

Tested Database TypesH2, MySQL, Postgres, Mssql, Oracle 11g


Stream Processor - Status Dashboard 

Database AccessKeep carbon metrics data and registerd sp-worker details and athentication details within the cluster. See Monitoring Stream Processor for more information
Mandatory/OptionalMandatory
Default Datasource NameWSO2_STATUS_DASHBOARD_DB, WSO2_METRICS_DB
Tables METRIC_COUNTER, METRIC_GAUGE, METRIC_HISTOGRAM, METRIC_METER, METRIC_TIMER , WORKERS_CONFIGURATIONS, WORKERS_DETAILS
Schemas and Queries

Information about the default queries and schema: https://github.com/wso2/carbon-analytics/blob/v2.0.250/components/org.wso2.carbon.status.dashboard.core/src/main/resources/queries.yaml

Tested Database TypesH2, MySQL, Mssql, Oracle 11g (Postgres is tested with Carbon-Metrics only)


Siddhi RDBMS Store 

Database AccessIt gives the capability of creating the tables at the siddhi app runtime and access the existing tablesif a user defined carbon datasource or JNDI property in a siddhi app. Documentation can be found in https://wso2-extensions.github.io/siddhi-store-rdbms/api/4.0.15/
Mandatory/OptionalOptional
Default Datasource NameNo such default Datasource. User has to create the datasource in the Siddhi app
TablesNo such default tables. User has to define the tables
Schemas and Queries

Information about the default queries and schema: https://github.com/wso2-extensions/siddhi-store-rdbms/blob/v4.0.15/component/src/main/resources/rdbms-table-config.xml

Tested Database TypesH2, MySQL, Mssql, Oracle 11g, DB2, PostgreSQL


Carbon Dashboards 

Database AccessCarbon Dashboard feature uses its datasource to persist the dashboard related information
Mandatory/OptionalOptional
Default Datasource NameWSO2_DASHBOARD_DB
TablesDASHBOARD_RESOURCE
Schemas and Queries

https://github.com/wso2/carbon-dashboards/tree/master/features/org.wso2.carbon.dashboards.api.feature/src/main/resources/sql

Tested Database TypesH2, MySQL


Business Rules 

Database AccessBusiness Rules feature uses database to persist the derived business rules
Mandatory/OptionalMandatory
Default Datasource NameBUSINESS_RULES_DB
TablesBUSINESS_RULES, RULES_TEMPLATES
Schemas and Queries

https://github.com/wso2/carbon-analytics/blob/v2.0.250/components/org.wso2.carbon.business.rules.core/src/main/resources/queries.yaml

Tested Database TypesH2, MySQL, Oracle 11g


IdP Client 

Database AccessIdP client access the DB layer to persist the client id and the client secret of dynamic client registration
Mandatory/OptionalMandatory for external IdP clients
Default Datasource Name DB_AUTH_DB
TablesOAUTH_APPS
Schemas and Queries

https://github.com/wso2/carbon-analytics-common/blob/v6.0.52/components/authentication/org.wso2.carbon.analytics.idp.client/src/main/resources/queries.yaml

Tested Database TypesH2


RDBMS Data Provider 

Database AccessRDBMS data provider read the table data for given query and push into Widgets, and purge the data in the table.
Mandatory/OptionalMandatory, default is in H2
Default Datasource Name SAMPLE_DB is the default in H2
TablesTRANSACTION_TABLE
Schemas and Queries

https://github.com/wso2/carbon-analytics/blob/v2.0.250/components/org.wso2.carbon.data.provider/src/main/resources/queries.yaml

Tested Database Types H2, MySQL, Postgres, Mssql, Oracle 11g


Permission  Provider 

Database AccessPermission provider will access the DB to persist permissions and role - permission mappings.
Mandatory/OptionalMandatory, default is in H2
Default Datasource NamePERMISSIONS_DB
Tables PERMISSIONS, ROLE_PERMISSIONS
Schemas and Queries

https://github.com/wso2/carbon-analytics-common/blob/v6.0.52/components/permission-provider/org.wso2.carbon.analytics.permissions/src/main/resources/queries.yaml

Tested Database Types H2, MySQL, Mssql, Oracle 11g
  • No labels