Page Comparison

WSO2 DAS supports several RDBMS types for its underlying Data Access Layer (DAL). In order to use the RDBMS DAL component, a pre-configured installation of a RDBMS is required. The RDBMS implementation for the DAS DAL contains both the implementations of Analytics Record Store and Analytics File System. The following sections describe both implementations of the Cassandra DAL component

...

In order to use RDBMS as the record store/file system, ensure that the RDBMS datasource provider is enabled by following the steps below.

...

Property	Description
<implementation>	The implementation class of the Analytics Record Store relevant for RDBMS, which is `org.wso2.carbon.analytics.datasource.rdbms.RDBMSAnalyticsRecordStore`.
<property name="datasource">	The Carbon datasource name of the `RDBMS` type, that is used to find the associated RDBMS data source.

Configurations for the Analytics File System

...

RDBMS query configuration

The above Analytics Record Store depends on a query configuration to execute its implementation. The query configuration contains SQL query templates for each of the RDBMS servers that it interfaces with. Using this configuration, you can modify the existing queries for fine tuning, or create new query configurations for a RDBMS that is not configured out of the box. You can find these configurations in the <DAS_HOME>/repository/conf/analytics/analyticsrdbms-config.xml file xml file as shown in the example below. Click on the relevant tab to view the query templates for each database category.

Localtabgroup

Localtab

title	h2

Code Block

language	xml

<analytics-record-store

    <database name =

"EVENT_STORE"> <implementation>org.wso2.carbon.analytics.datasource.rdbms.RDBMSAnalyticsFileSystem</implementation>

 "h2.*" minVersion = "1.0" maxVersion = "10.0">

   <recordCountSupported>false</recordCountSupported>

<!--

the

data

<paginationSupported>true</paginationSupported>

source

name

mentioned

in

data

sources

configuration

-->

 <paginationMode>MODE1</paginationMode>

<property name="datasource">WSO2_ANALYTICS_EVENT_STORE_DB</property> </properties> </analytics-record-store>

The properties of the above configurations are described below.

Property	Description
<implementation>	The implementation class of the Analytics Record Store relevant for RDBMS, which is `org.wso2.carbon.analytics.datasource.rdbms.RDBMSAnalyticsFileSystem`.
<property name="datasource">	The Carbon datasource name of the `RDBMS` type, that is used to find the associated RDBMS data source.

RDBMS query configuration

Both the above RDBMS Analytics File System and Analytics Record Store, depends on a query configuration to execute its implementation. The query configuration contains SQL query templates for each of the RDBMS servers that it interfaces with. Using this configuration, you can modify the existing queries for fine tuning, or create new query configurations for a RDBMS that is not configured out of the box. You can find these configurations in the <DAS_HOME>/repository/conf/analytics/rdbms-query-config.xml file as shown in the example below. Click on the relevant tab to view the query templates for each database category.

Localtab

title	h2

Code Block

language	xml

    <database name = "h2.*" minVersion = "1.0" maxVersion = "10.0">
        <recordCountSupported>false</recordCountSupported> <recordTableCheckQuery>SELECT 1 FROM {{TABLE_NAME}} LIMIT 1</recordTableCheckQuery>
        <recordCountQuery>SELECT COUNT(*) FROM {{TABLE_NAME}} WHERE timestamp &gt;= ? AND timestamp &lt; ?</recordCountQuery>
        <recordDeletionQuery>DELETE FROM {{TABLE_NAME}} WHERE timestamp &gt;= ? AND timestamp &lt; ?</recordDeletionQuery>
        <recordDeletionWithIdsQuery>DELETE FROM {{TABLE_NAME}} WHERE record_id IN ({{RECORD_IDS}})</recordDeletionWithIdsQuery>
        <recordMergeQuery>MERGE INTO {{TABLE_NAME}} (partition_key, timestamp, data, record_id) KEY (record_id) VALUES (?, ?, ?, ?)</recordMergeQuery>
        <recordRetrievalQuery>SELECT record_id, timestamp, data FROM {{TABLE_NAME}} WHERE partition_key &gt;= ? and partition_key &lt; ? AND timestamp &gt;= ? AND timestamp &lt; ? LIMIT ?,?</recordRetrievalQuery>
        <paginationSupported>true</paginationSupported>
        <paginationMode>MODE1</paginationMode><recordRetrievalWithIdsQuery>SELECT record_id, timestamp, data FROM {{TABLE_NAME}} WHERE record_id IN ({{RECORD_IDS}})</recordRetrievalWithIdsQuery>
        <recordTableCheckQuery>SELECT<recordTableDeleteQueries>
1 FROM {{TABLE_NAME}} LIMIT 1</recordTableCheckQuery>        <query>DROP <recordCountQuery>SELECT COUNT(*) FROMINDEX IF EXISTS {{TABLE_NAME}}_TIMESTAMP</query>
WHERE timestamp &gt;= ? AND timestamp &lt; ?</recordCountQuery>     <query>DROP INDEX IF  <recordDeletionQuery>DELETE FROMEXISTS {{TABLE_NAME}} WHERE timestamp &gt;= ? AND timestamp &lt; ?</recordDeletionQuery>_PARTITION_KEY</query>
            <query>DROP TABLE <recordDeletionWithIdsQuery>DELETEIF FROMEXISTS {{TABLE_NAME}} WHERE record_id IN ({{RECORD_IDS}})</recordDeletionWithIdsQuery></query>
        </recordTableDeleteQueries>
        <recordTableInitQueries>
            <recordMergeQuery>MERGE<query>CREATE INTOTABLE {{TABLE_NAME}} (partition_keyrecord_id VARCHAR(50), timestamp BIGINT, data BINARY, record_id)partition_key INT, PRIMARY KEY (record_id))</query>
VALUES (?, ?, ?, ?)</recordMergeQuery>        <query>CREATE <recordRetrievalQuery>SELECT record_id, timestamp, data FROM INDEX {{TABLE_NAME}}_TIMESTAMP ON {{TABLE_NAME}} WHERE partition_key &gt;= ? and partition_key &lt; ? AND timestamp &gt;= ? AND timestamp &lt; ? LIMIT ?,?</recordRetrievalQuery>(timestamp)</query>
            <query>CREATE INDEX {{TABLE_NAME}}_PARTITION_KEY ON {{TABLE_NAME}} (partition_key)</query>
        </recordTableInitQueries>
<recordRetrievalWithIdsQuery>SELECT record_id, timestamp, data FROM {{TABLE_NAME}} WHERE record_id IN ({{RECORD_IDS}})</recordRetrievalWithIdsQuery>
    </database>

Localtab

title	mysql - large_dataset_optimized category

Code Block

language	xml

<database name = "mysql" category = "large_dataset_optimized">
    <recordTableDeleteQueries>    <recordCountSupported>false</recordCountSupported>
        <query>DROP<paginationSupported>true</paginationSupported>
INDEX IF EXISTS {{TABLE_NAME}}_TIMESTAMP</query>     <paginationMode>MODE1</paginationMode>
       <query>DROP INDEX<recordTableCheckQuery>SELECT IF1 EXISTSFROM {{TABLE_NAME}}_PARTITION_KEY</query> LIMIT 1</recordTableCheckQuery>
        <recordCountQuery>SELECT COUNT(*) <query>DROP TABLE IF EXISTS FROM {{TABLE_NAME}}</query> WHERE timestamp &gt;= ? AND timestamp &lt;  ?</recordTableDeleteQueries>recordCountQuery>
        <recordDeletionQuery>DELETE FROM {{TABLE_NAME}} WHERE timestamp &gt;= <recordTableInitQueries>? AND timestamp &lt; ?</recordDeletionQuery>
        <query>CREATE<recordDeletionWithIdsQuery>DELETE TABLEFROM {{TABLE_NAME}} WHERE (record_id VARCHAR(50), timestamp BIGINT, data BINARY, partition_key INT, PRIMARY KEY(record_id))</query>
            <query>CREATE INDEX {{TABLE_NAME}}_TIMESTAMP ON {{TABLE_NAME}} (timestamp)</query>IN ({{RECORD_IDS}})</recordDeletionWithIdsQuery>
        <recordMergeQuery>INSERT INTO {{TABLE_NAME}} (partition_key, timestamp, data, record_id) VALUES (?, ?, ?, ?) ON DUPLICATE KEY UPDATE partition_key=VALUES(partition_key), timestamp=VALUES(timestamp), data=VALUES(data)</recordMergeQuery>
        <forwardOnlyReadEnabled>true</forwardOnlyReadEnabled>
      <query>CREATE INDEX {{TABLE_NAME}}_PARTITION_KEY ON {{TABLE_NAME}} (partition_key)</query> <fetchSize>-2147483648</fetchSize>        
       </recordTableInitQueries> <recordRetrievalQuery>SELECT record_id, timestamp, data FROM {{TABLE_NAME}} WHERE  <fsDataChunkSize>10240</fsDataChunkSize>
        <fsDeletePathQuery>DELETE FROM AN_FS_PATH WHERE path = ?</fsDeletePathQuery>partition_key &gt;= ? and partition_key &lt; ? AND timestamp &gt;= ? AND timestamp &lt; ? LIMIT ?,?</recordRetrievalQuery>
        <fsDeleteDataQuery>DELETE FROM AN_FS_DATA WHERE path = ?</fsDeleteDataQuery><recordRetrievalWithIdsQuery>SELECT record_id, timestamp, data FROM {{TABLE_NAME}} WHERE record_id IN ({{RECORD_IDS}})</recordRetrievalWithIdsQuery>
        <fsUpdateDataPathQuery>UPDATE AN_FS_DATA SET path = ? WHERE path = ?</fsUpdateDataPathQuery><recordTableDeleteQueries>
            <query>DROP <fsFileLengthRetrievalQuery>SELECT length FROM AN_FS_PATH WHERE path = ?</fsFileLengthRetrievalQuery>INDEX {{TABLE_NAME}}_PARTITION_KEY ON {{TABLE_NAME}}</query>
            <fsInsertPathQuery>INSERT<query>DROP INTOINDEX AN_FS_PATH (path,is_directory,length,parent_path) VALUES (?,?,?,?)</fsInsertPathQuery>{{TABLE_NAME}}_TIMESTAMP ON {{TABLE_NAME}}</query>
         <fsListFilesQuery>SELECT path FROM AN_FS_PATH WHERE parent_path = ?</fsListFilesQuery><query>DROP TABLE IF EXISTS {{TABLE_NAME}}</query>
        <fsPathRetrievalQuery>SELECT</recordTableDeleteQueries>
* FROM AN_FS_PATH WHERE path = ?</fsPathRetrievalQuery>  <recordTableInitQueries>
      <fsReadDataChunkQuery>SELECT data FROM AN_FS_DATA WHERE path =<query>CREATE ? AND sequence = ?</fsReadDataChunkQuery>
        <fsSetFileLengthQuery>UPDATE AN_FS_PATH SET length = ? WHERE path = ?</fsSetFileLengthQuery>
        <fsTableInitQueries>TABLE {{TABLE_NAME}} (record_id VARCHAR(50), timestamp BIGINT, data LONGBLOB, partition_key INT, PRIMARY KEY(record_id)) ENGINE='MyISAM'</query>
            <query>CREATE INDEX {{TABLE_NAME}}_TIMESTAMP ON {{TABLE_NAME}} (timestamp)</query>
            <query>CREATE <query>CREATEINDEX {{TABLE AN_FS_PATH (path VARCHAR(256), is_directory BOOLEAN, length BIGINT, parent_path VARCHAR(256), PRIMARY KEY(path), FOREIGN KEY (parent_path) REFERENCES AN_FS_PATH(path))</query>
            <query>CREATE TABLE AN_FS_DATA (path VARCHAR(256), sequence BIGINT, data BINARY, PRIMARY KEY (path,sequence), FOREIGN KEY (path) REFERENCES AN_FS_PATH(path))</query>
  NAME}}_PARTITION_KEY ON {{TABLE_NAME}} (partition_key)</query>
        </recordTableInitQueries>
    </database>

Localtab

title	mysql - limited_dataset_optimized category

Code Block

language	xml

    <database name = "mysql" category = "limited_dataset_optimized">
        <recordCountSupported>false</recordCountSupported>
        <paginationSupported>true</paginationSupported>
        <paginationMode>MODE1</paginationMode>
<query>CREATE INDEX index_parent_id ON AN_FS_PATH(parent_path)</query>    <recordTableCheckQuery>SELECT 1 FROM {{TABLE_NAME}} LIMIT <1</fsTableInitQueries>recordTableCheckQuery>
        <fsTablesCheckQuery>SELECT path FROM AN_FS_PATH<recordCountQuery>SELECT COUNT(*) FROM {{TABLE_NAME}} WHERE pathtimestamp = '/'</fsTablesCheckQuery>&gt;= ? AND timestamp &lt; ?</recordCountQuery>
        <fsMergeDataChunkQuery>MERGE<recordDeletionQuery>DELETE INTOFROM AN_FS_DATA (path,sequence,data) KEY (path,sequence) VALUES (?,?,?)</fsMergeDataChunkQuery>
    </database>

Localtab

title	mysql - large_dataset_optimized category

Code Block

language	xml

<database name = "mysql" category = "large_dataset_optimized">{{TABLE_NAME}} WHERE timestamp &gt;= ? AND timestamp &lt; ?</recordDeletionQuery>
        <recordDeletionWithIdsQuery>DELETE FROM {{TABLE_NAME}} WHERE record_id IN ({{RECORD_IDS}})</recordDeletionWithIdsQuery>
        <recordCountSupported>false</recordCountSupported><recordMergeQuery>INSERT INTO {{TABLE_NAME}} (partition_key, timestamp, data, record_id) VALUES  <paginationSupported>true</paginationSupported>
        <paginationMode>MODE1</paginationMode>
 (?, ?, ?, ?) ON DUPLICATE KEY UPDATE partition_key=VALUES(partition_key), timestamp=VALUES(timestamp), data=VALUES(data)</recordMergeQuery>
      <recordTableCheckQuery>SELECT 1 FROM {{TABLE_NAME}} LIMIT 1</recordTableCheckQuery> <forwardOnlyReadEnabled>true</forwardOnlyReadEnabled>
        <recordCountQuery>SELECT COUNT(*) FROM {{TABLE_NAME}} WHERE timestamp &gt;= ? AND timestamp &lt; ?</recordCountQuery><fetchSize>-2147483648</fetchSize>        
        <recordRetrievalQuery>SELECT record_id, timestamp, <recordDeletionQuery>DELETEdata FROM {{TABLE_NAME}} WHERE timestamppartition_key &gt;= ? ANDand timestamppartition_key &lt; ?</recordDeletionQuery> AND        <recordDeletionWithIdsQuery>DELETEtimestamp &gt;= ? AND timestamp &lt; ? LIMIT ?,?</recordRetrievalQuery>
        <recordRetrievalWithIdsQuery>SELECT record_id, timestamp, data FROM {{TABLE_NAME}} WHERE record_id IN ({{RECORD_IDS}})</recordDeletionWithIdsQuery>recordRetrievalWithIdsQuery>
        <recordTableDeleteQueries>
            <recordMergeQuery>INSERT<query>DROP INTOINDEX {{TABLE_NAME}} (partition_key, timestamp, data, record_id) VALUES (?, ?, ?, ?) ON DUPLICATE KEY UPDATE partition_key=VALUES(partition_key), timestamp=VALUES(timestamp), data=VALUES(data)</recordMergeQuery>_PARTITION_KEY ON {{TABLE_NAME}}</query>
            <query>DROP INDEX {{TABLE_NAME}}_TIMESTAMP ON {{TABLE_NAME}}</query>
            <query>DROP TABLE IF  <forwardOnlyReadEnabled>true</forwardOnlyReadEnabled>EXISTS {{TABLE_NAME}}</query>
        <fetchSize>-2147483648</fetchSize></recordTableDeleteQueries>
        <recordTableInitQueries>
         <recordRetrievalQuery>SELECT record_id, timestamp, data<query>CREATE FROMTABLE {{TABLE_NAME}} WHERE partition_key &gt;= ? and(record_id VARCHAR(50), timestamp BIGINT, data LONGBLOB, partition_key &lt; ? AND timestamp &gt;= ? AND timestamp &lt; ? LIMIT ?,?</recordRetrievalQuery>INT, PRIMARY KEY(record_id)) ENGINE='InnoDB'</query>
         <recordRetrievalWithIdsQuery>SELECT record_id, timestamp, data<query>CREATE FROMINDEX {{TABLE_NAME}} WHERE record_idTIMESTAMP INON ({{RECORDTABLE_IDSNAME}} (timestamp)</recordRetrievalWithIdsQuery>query>
        <recordTableDeleteQueries>             <query>DROP <query>CREATE INDEX {{TABLE_NAME}}_PARTITION_KEY ON {{TABLE_NAME}} (partition_key)</query>
         </recordTableInitQueries>
  <query>DROP INDEX {{TABLE_NAME}}_TIMESTAMP ON {{TABLE_NAME}}</query></database>

Localtab

title	oracle

Code Block

language	xml

    <database name =  "oracle">
    <query>DROP TABLE IF EXISTS {{TABLE_NAME}}</query> <recordCountSupported>false</recordCountSupported>
        <<paginationSupported>true</recordTableDeleteQueries>paginationSupported>
        <recordTableInitQueries><paginationMode>MODE2</paginationMode>
        <recordTableCheckQuery>SELECT 1 FROM  <query>CREATE TABLE {{TABLE_NAME}} (record_id VARCHAR(50), timestamp BIGINT, data LONGBLOB, partition_key INT, PRIMARY KEY(record_id)) ENGINE='MyISAM'</query>
   WHERE rownum=1</recordTableCheckQuery>
        <recordCountQuery>SELECT COUNT(*) FROM {{TABLE_NAME}} WHERE timestamp &gt;= ? AND timestamp &lt; ?</recordCountQuery>
        <query>CREATE<recordDeletionQuery>DELETE INDEXFROM {{TABLE_NAME}}_TIMESTAMP WHERE ON {{TABLE_NAME}} (timestamp)</query>
   timestamp &gt;= ? AND timestamp &lt; ?</recordDeletionQuery>
        <query>CREATE<recordDeletionWithIdsQuery>DELETE INDEXFROM {{TABLE_NAME}}_PARTITION_KEY ON {{WHERE record_id IN ({{RECORD_IDS}})</recordDeletionWithIdsQuery>
        <recordInsertQuery>INSERT INTO {{TABLE_NAME}} (partition_key)</query>
        </recordTableInitQueries>, timestamp, data, record_id) VALUES (?, ?, ?, ?)</recordInsertQuery>
        <fsDataChunkSize>10240</fsDataChunkSize>
        <fsDeletePathQuery>DELETE FROM AN_FS_PATH WHERE path<recordUpdateQuery>UPDATE {{TABLE_NAME}} SET partition_key = ?, timestamp = ?, data = ? WHERE record_id = ?</fsDeletePathQuery>recordUpdateQuery>
        <fsDeleteDataQuery>DELETE<recordMergeQuery>MERGE FROMINTO AN_FS_DATA WHERE path = ?</fsDeleteDataQuery>
        <fsUpdateDataPathQuery>UPDATE AN_FS_DATA SET path = ? WHERE path = ?</fsUpdateDataPathQuery>
        <fsFileLengthRetrievalQuery>SELECT length FROM AN_FS_PATH WHERE path = ?</fsFileLengthRetrievalQuery>
        <fsInsertPathQuery>INSERT INTO AN_FS_PATH (path,is_directory,length,parent_path) VALUES (?,?,?,?)</fsInsertPathQuery>
        <fsListFilesQuery>SELECT path FROM AN_FS_PATH WHERE parent_path = ?</fsListFilesQuery>
        <fsPathRetrievalQuery>SELECT * FROM AN_FS_PATH WHERE path = ?</fsPathRetrievalQuery>
        <fsReadDataChunkQuery>SELECT data FROM AN_FS_DATA WHERE path = ? AND sequence = ?</fsReadDataChunkQuery>{{TABLE_NAME}} dest USING( SELECT  ? partition_key, ? timestamp, ? data, ? record_id FROM dual) src ON(dest.record_id = src.record_id) WHEN NOT MATCHED THEN INSERT(partition_key, timestamp, data, record_id) VALUES(src.partition_key, src.timestamp, src.data, src.record_id) WHEN MATCHED THEN UPDATE SET dest.partition_key = src.partition_key, dest.timestamp = src.timestamp, dest.data = src.data</recordMergeQuery>
        <recordRetrievalQuery>SELECT record_id, timestamp, data from (SELECT rownum RNUM, record_id, timestamp, data FROM {{TABLE_NAME}} WHERE partition_key &gt;= ? and partition_key &lt; ? AND timestamp &gt;= ? AND timestamp &lt; ? and rownum &lt;= ?) where RNUM &gt; ?</recordRetrievalQuery>
        <fsSetFileLengthQuery>UPDATE<recordRetrievalWithIdsQuery>SELECT AN_FS_PATH SET length = ? WHERE path = ?</fsSetFileLengthQuery>record_id, timestamp, data FROM {{TABLE_NAME}} WHERE record_id IN ({{RECORD_IDS}})</recordRetrievalWithIdsQuery>
        <recordTableDeleteQueries>
<fsTableInitQueries>            <query>DROP <query>CREATEINDEX {{TABLE AN_FS_PATH (path VARCHAR(256), is_directory BOOLEAN, length BIGINT, parent_path VARCHAR(256), PRIMARY KEY(path)) ENGINE='MyISAM'</query>NAME}}_PARTITION_KEY</query>
            <query>DROP        <query>CREATE TABLE AN_FS_DATA (path VARCHAR(256), sequence BIGINT, data LONGBLOB, PRIMARY KEY (path,sequence)) ENGINE='MyISAM'<INDEX {{TABLE_NAME}}_TIMESTAMP</query>
            <query>CREATE<query>DROP INDEXTABLE index_parent_id ON AN_FS_PATH(parent_path){{TABLE_NAME}}</query>
        </fsTableInitQueries>recordTableDeleteQueries>
        <fsTablesCheckQuery>SELECT<recordTableInitQueries>
path FROM AN_FS_PATH WHERE path = '/'</fsTablesCheckQuery>      <query>CREATE TABLE  <fsMergeDataChunkQuery>INSERT INTO AN_FS_DATA (path,sequence,data) VALUES (?, ?, ?) ON DUPLICATE KEY UPDATE data=VALUES(data)</fsMergeDataChunkQuery>{{TABLE_NAME}} (record_id VARCHAR2(50), timestamp NUMBER(19), data BLOB, partition_key NUMBER(10), PRIMARY KEY(record_id))</query>
            <query>CREATE  </database>

Localtab

title	mysql - limited_dataset_optimized category

Code Block

language	xml

    <database name = "mysql" category = "limited_dataset_optimized">INDEX {{TABLE_NAME}}_TIMESTAMP ON {{TABLE_NAME}} (timestamp)</query>
            <query>CREATE INDEX  <recordCountSupported>false</recordCountSupported>{{TABLE_NAME}}_PARTITION_KEY ON {{TABLE_NAME}} (partition_key)</query>
        <paginationSupported>true<</paginationSupported>recordTableInitQueries>
    </database>

Localtab

title	Microsoft SQL Server

Code Block

language	xml

    <paginationMode>MODE1</paginationMode><database name = "Microsoft SQL Server">
   <recordTableCheckQuery>SELECT 1 FROM {{TABLE_NAME}} LIMIT 1<<recordCountSupported>false</recordTableCheckQuery>recordCountSupported>
        <paginationSupported>true</paginationSupported>
        <paginationMode>MODE2</paginationMode>
        <blobLengthRequired>true</blobLengthRequired>
        <recordTableCheckQuery>SELECT TOP 1 1 from {{TABLE_NAME}}</recordTableCheckQuery>
        <recordCountQuery>SELECT COUNT(*) FROM {{TABLE_NAME}} WHERE timestamp &gt;= ? AND timestamp &lt; ?</recordCountQuery>
        <recordDeletionQuery>DELETE FROM {{TABLE_NAME}} WHERE timestamp &gt;= ? AND timestamp &lt; ?</recordDeletionQuery>
        <recordDeletionWithIdsQuery>DELETE FROM {{TABLE_NAME}} WHERE record_id IN ({{RECORD_IDS}})</recordDeletionWithIdsQuery>
        <recordMergeQuery>INSERT INTO <!--recordMergeQuery>MERGE {{TABLE_NAME}} AS dest USING (SELECT ?, ?, ?, ?) AS src (partition_key, timestamp, data, record_id) VALUESON (?, ?, ?, ?) ON DUPLICATE KEY UPDATEdest.record_id = src.record_id) WHEN MATCHED THEN UPDATE SET partition_key =VALUES( src.partition_key), timestamp =VALUES( src.timestamp), data =VALUES(data)</recordMergeQuery>
        <forwardOnlyReadEnabled>true</forwardOnlyReadEnabled> src.data WHEN NOT MATCHED THEN INSERT(partition_key, timestamp, data, record_id) VALUES (src.partition_key, src.timestamp, src.data, src.record_id);</recordMergeQuery-->
        <fetchSize>-2147483648</fetchSize><recordInsertQuery>INSERT INTO {{TABLE_NAME}} (partition_key, timestamp, data, record_id) VALUES (?, ?, ?, ?)</recordInsertQuery>
     <recordRetrievalQuery>SELECT record_id, timestamp, data FROM<recordUpdateQuery>UPDATE {{TABLE_NAME}} WHERESET partition_key &gt;= ?, and partition_key &lt;timestamp = ?, ANDdata timestamp &gt;= ? ANDWHERE timestamp &lt; ? LIMIT ?,?</recordRetrievalQuery>record_id = ?</recordUpdateQuery>
        <recordRetrievalWithIdsQuery>SELECT<recordRetrievalQuery>SELECT record_id, timestamp, data FROM {{TABLE_NAME}} WHERE (SELECT ROW_NUMBER() OVER(ORDER BY record_id) AS rownumber, record_id IN (, timestamp, data FROM {{RECORDTABLE_IDSNAME}})</recordRetrievalWithIdsQuery> WHERE partition_key &gt;= ? and partition_key &lt; ? <recordTableDeleteQueries>AND timestamp &gt;= ? AND timestamp &lt; ?) AS A WHERE A.rownumber &lt;= <query>DROP? INDEX {{TABLE_NAME}}_PARTITION_KEY ON {{TABLE_NAME}}</query>AND A.rownumber &gt; ?</recordRetrievalQuery>
        <!--recordRetrievalQuery>SELECT record_id, timestamp, data <query>DROPFROM INDEX {{TABLE_NAME}}_TIMESTAMP ON {{TABLE_NAME}}</query>
            <query>DROP TABLE IF EXISTS {{TABLE_NAME}}</query>WHERE timestamp &gt;= ? AND timestamp &lt; ? OFFSET ? ROWS FETCH NEXT ? ROWS ONLY</recordRetrievalQuery-->
        </recordTableDeleteQueries>
        <recordTableInitQueries><recordRetrievalWithIdsQuery>SELECT record_id, timestamp, data FROM {{TABLE_NAME}} WHERE record_id IN ({{RECORD_IDS}})</recordRetrievalWithIdsQuery>
        <recordTableDeleteQueries>
   <query>CREATE TABLE {{TABLE_NAME}} (record_id VARCHAR(50), timestamp BIGINT, data LONGBLOB, partition_key INT, PRIMARY KEY(record_id)) ENGINE='InnoDB'<<query>DROP INDEX {{TABLE_NAME}}_PARTITION_KEY</query>
            <query>CREATE<query>DROP INDEX {{TABLE_NAME}}_TIMESTAMP ONTIMESTAMP</query>
            <query>DROP TABLE {{TABLE_NAME}} (timestamp)</query>
        </recordTableDeleteQueries>
   <query>CREATE INDEX     <recordTableInitQueries>
            <query>CREATE TABLE {{TABLE_NAME}}_PARTITION_KEY ON {{TABLE_NAME}} (partition_key (record_id VARCHAR(50), timestamp BIGINT, data VARBINARY(max), partition_key INTEGER, PRIMARY KEY(record_id))</query>
        </recordTableInitQueries>    <query>CREATE INDEX {{TABLE_NAME}}_TIMESTAMP ON  <fsDataChunkSize>10240</fsDataChunkSize>{{TABLE_NAME}} (timestamp)</query>
        <fsDeletePathQuery>DELETE FROM AN_FS_PATH WHERE path = ?</fsDeletePathQuery> <query>CREATE INDEX {{TABLE_NAME}}_PARTITION_KEY ON {{TABLE_NAME}} (partition_key)</query>
        <fsDeleteDataQuery>DELETE</recordTableInitQueries>
FROM AN_FS_DATA WHERE path = ?</fsDeleteDataQuery>database>

Localtab

title	PostgreSQL

Code Block

language	xml

    <database name = "PostgreSQL">
 <fsUpdateDataPathQuery>UPDATE AN_FS_DATA SET path = ? WHERE path<recordCountSupported>false</recordCountSupported>
= ?</fsUpdateDataPathQuery>       <paginationSupported>true</paginationSupported>
 <fsFileLengthRetrievalQuery>SELECT length FROM AN_FS_PATH WHERE path = ?<<paginationMode>MODE1</fsFileLengthRetrievalQuery>paginationMode>
        <fsInsertPathQuery>INSERT<recordTableCheckQuery>SELECT INTO AN_FS_PATH (path,is_directory,length,parent_path) VALUES (?,?,?,?)</fsInsertPathQuery>1 FROM {{TABLE_NAME}} LIMIT 1</recordTableCheckQuery>
        <fsListFilesQuery>SELECT path<recordCountQuery>SELECT COUNT(*) FROM AN_FS_PATH{{TABLE_NAME}} WHERE parent_path timestamp &gt;= ? AND timestamp &lt; ?</fsListFilesQuery>recordCountQuery>
        <fsPathRetrievalQuery>SELECT<recordDeletionQuery>DELETE * FROM AN_FS_PATH{{TABLE_NAME}} WHERE pathtimestamp &gt;= ? AND timestamp &lt; ?</fsPathRetrievalQuery>recordDeletionQuery>
        <fsReadDataChunkQuery>SELECT<recordDeletionWithIdsQuery>DELETE data FROM AN_FS_DATA{{TABLE_NAME}} WHERE pathrecord_id = ? AND sequence = ?</fsReadDataChunkQuery>IN ({{RECORD_IDS}})</recordDeletionWithIdsQuery>
        <fsSetFileLengthQuery>UPDATE AN_FS_PATH SET length = ? WHERE path = ?</fsSetFileLengthQuery><recordInsertQuery>INSERT INTO {{TABLE_NAME}} (partition_key, timestamp, data, record_id) VALUES (?, ?, ?, ?)</recordInsertQuery>
        <fsTableInitQueries>
            <query>CREATE TABLE AN_FS_PATH (path VARCHAR(256), is_directory BOOLEAN, length BIGINT, parent_path VARCHAR(256), PRIMARY KEY(path)) ENGINE='InnoDB'</query>
            <query>CREATE TABLE AN_FS_DATA (path VARCHAR(256), sequence BIGINT, data LONGBLOB, PRIMARY KEY (path,sequence)) ENGINE='InnoDB'</query><recordUpdateQuery>UPDATE {{TABLE_NAME}} SET partition_key = ?, timestamp = ?, data = ? WHERE record_id = ?</recordUpdateQuery>
        <!--recordMergeQuery>INSERT INTO {{TABLE_NAME}} (partition_key, timestamp, data, record_id) VALUES (?, ?, ?, ?) ON CONFLICT DO UPDATE partition_key=VALUES(partition_key), timestamp=VALUES(timestamp), data=VALUES(data)</recordMergeQuery-->
        <forwardOnlyReadEnabled>true</forwardOnlyReadEnabled>
   <query>CREATE  INDEX index_parent_id ON AN_FS_PATH(parent_path)</query> <fetchSize>1000</fetchSize>
        </fsTableInitQueries>
        <fsTablesCheckQuery>SELECT path FROM AN_FS_PATH WHERE path = '/'</fsTablesCheckQuery>
        <fsMergeDataChunkQuery>INSERT INTO AN_FS_DATA (path,sequence,data) VALUES (?, ?, ?) ON DUPLICATE KEY UPDATE data=VALUES(data)</fsMergeDataChunkQuery><recordRetrievalQuery>SELECT record_id, timestamp, data FROM {{TABLE_NAME}} WHERE partition_key &gt;= ? and partition_key &lt; ? AND timestamp &gt;= ? AND timestamp &lt; ? OFFSET ? LIMIT ?</recordRetrievalQuery>
        <recordRetrievalWithIdsQuery>SELECT record_id, timestamp,  </database>

Localtab

title	oracle

Code Block

language	xml

    <database name = "oracle">data FROM {{TABLE_NAME}} WHERE record_id IN ({{RECORD_IDS}})</recordRetrievalWithIdsQuery>
        <recordTableDeleteQueries>
 <recordCountSupported>false</recordCountSupported>         <paginationSupported>true</paginationSupported>  <query>DROP INDEX {{TABLE_NAME}}_PARTITION_KEY ON {{TABLE_NAME}}</query>
  <paginationMode>MODE2</paginationMode>         <recordTableCheckQuery>SELECT 1<query>DROP FROMINDEX {{TABLE_NAME}}_TIMESTAMP WHERE rownum=1</recordTableCheckQuery>ON {{TABLE_NAME}}</query>
            <query>DROP <recordCountQuery>SELECT COUNT(*) FROMTABLE IF EXISTS {{TABLE_NAME}}</query>
WHERE  timestamp &gt;= ? AND timestamp &lt; ?</recordCountQuery>recordTableDeleteQueries>
        <recordDeletionQuery>DELETE FROM {{TABLE_NAME}} WHERE timestamp &gt;= ? AND timestamp &lt; ?</recordDeletionQuery><recordTableInitQueries>
            <recordDeletionWithIdsQuery>DELETE<query>CREATE FROMTABLE {{TABLE_NAME}} WHERE (record_id IN ({{RECORD_IDS}})</recordDeletionWithIdsQuery>
        <recordInsertQuery>INSERT INTO {{TABLE_NAME}} (partition_key, timestamp, data, record_id) VALUES (?, ?, ?, ?)</recordInsertQuery>VARCHAR(50), timestamp BIGINT, data BYTEA, partition_key INTEGER, PRIMARY KEY(record_id))</query>
            <query>CREATE <recordUpdateQuery>UPDATEINDEX {{TABLE_NAME}}_TIMESTAMP SETON partition_key = ?, timestamp = ?, data = ? WHERE record_id = ?</recordUpdateQuery>{{TABLE_NAME}} (timestamp)</query>
            <query>CREATE INDEX    <recordMergeQuery>MERGE INTO {{TABLE_NAME}}_PARTITION_KEY ON {{TABLE_NAME}} dest USING( SELECT(partition_key)</query>
  ? partition_key, ? timestamp, ? data, ? record_id FROM dual) src ON(dest.record_id = src.record_id) WHEN NOT MATCHED THEN INSERT(partition_key, timestamp, data, record_id) VALUES(src.partition_key, src.timestamp, src.data, src.record_id) WHEN MATCHED THEN UPDATE SET dest.partition_key = src.partition_key, dest.timestamp = src.timestamp, dest.data = src.data</recordMergeQuery></recordTableInitQueries>
    </database>

Localtab

title	DB2.*

Code Block

language	xml

    <database name = "DB2.*">
        <recordCountSupported>false</recordCountSupported>
        <paginationSupported>true</paginationSupported>
        <paginationMode>MODE1</paginationMode>
        <recordRetrievalQuery>SELECT record_id, timestamp, data from (SELECT rownum RNUM, record_id, timestamp, data<blobLengthRequired>true</blobLengthRequired>
        <recordTableCheckQuery>SELECT 1 FROM {{TABLE_NAME}} WHEREFETCH partition_key &gt;= ? and partition_key &lt; ? ANDFIRST 1 ROWS ONLY</recordTableCheckQuery>
        <recordCountQuery>SELECT COUNT(*) FROM {{TABLE_NAME}} WHERE timestamp &gt;= ? AND timestamp &lt; ? and rownum &lt</recordCountQuery>
        <recordDeletionQuery>DELETE FROM {{TABLE_NAME}} WHERE timestamp &gt;= ?) whereAND RNUMtimestamp &gtlt; ?</recordRetrievalQuery>recordDeletionQuery>
        <recordRetrievalWithIdsQuery>SELECT record_id, timestamp, data <recordDeletionWithIdsQuery>DELETE FROM {{TABLE_NAME}} WHERE record_id IN ({{RECORD_IDS}})</recordRetrievalWithIdsQuery>recordDeletionWithIdsQuery>
        <recordTableDeleteQueries><recordMergeQuery>MERGE             <query>DROP INDEX INTO {{TABLE_NAME}}_PARTITION_KEY</query> AS dest USING (VALUES(?, ?, ?, ?)) AS src (partition_key, timestamp,  <query>DROP INDEX {{TABLE_NAME}}_TIMESTAMP</query>
            <query>DROP TABLE {{TABLE_NAME}}</query>
        </recordTableDeleteQueries>
        <recordTableInitQueries>data, record_id) ON dest.record_id = src.record_id WHEN MATCHED THEN UPDATE SET dest.partition_key = src.partition_key, dest.timestamp = src.timestamp, dest.data = src.data WHEN NOT MATCHED THEN INSERT (partition_key, timestamp, data, record_id) VALUES (src.partition_key, src.timestamp, src.data, src.record_id)</recordMergeQuery>
               <query>CREATE TABLE<recordInsertQuery>INSERT INTO {{TABLE_NAME}} (record_id VARCHAR2(50)partition_key, timestamp NUMBER(19), data BLOB, partition_key NUMBER(10), PRIMARY KEY(record_id))</query>record_id) VALUES (?, ?, ?, ?)</recordInsertQuery>
            <query>CREATE INDEX <recordUpdateQuery>UPDATE {{TABLE_NAME}} SET partition_TIMESTAMPkey ON {{TABLE_NAME}} (timestamp)</query>
    = ?, timestamp = ?, data = ? WHERE record_id = ?</recordUpdateQuery>
       <query>CREATE INDEX<recordRetrievalQuery>SELECT {{TABLE_NAME}}_PARTITION_KEY ONrecord_id, timestamp, data FROM {{TABLE_NAME}} WHERE (partition_key)</query>
        </recordTableInitQueries>
        <fsDataChunkSize>10240</fsDataChunkSize> &gt;= ? and partition_key &lt; ? AND timestamp &gt;= ? AND timestamp &lt; ? LIMIT ?,?</recordRetrievalQuery>
        <recordRetrievalWithIdsQuery>SELECT record_id, timestamp, <fsDeletePathQuery>DELETEdata FROM AN_FS_PATH{{TABLE_NAME}} WHERE pathrecord_id = ?IN ({{RECORD_IDS}})</fsDeletePathQuery>recordRetrievalWithIdsQuery>
        <fsDeleteDataQuery>DELETE FROM AN_FS_DATA WHERE path = ?</fsDeleteDataQuery><recordTableDeleteQueries>
            <query>DROP <fsUpdateDataPathQuery>UPDATE AN_FS_DATA SET path = ? WHERE path = ?</fsUpdateDataPathQuery>INDEX {{TABLE_NAME}}_PARTITION_KEY</query>
            <query>DROP    <fsFileLengthRetrievalQuery>SELECT length FROM AN_FS_PATH WHERE path = ?</fsFileLengthRetrievalQuery>INDEX {{TABLE_NAME}}_TIMESTAMP</query>
            <fsInsertPathQuery>INSERT<query>DROP INTOTABLE AN_FS_PATH (path,is_directory,length,parent_path) VALUES (?,?,?,?)</fsInsertPathQuery>
  {{TABLE_NAME}}</query>
     <fsListFilesQuery>SELECT path FROM AN_FS_PATH WHERE parent_path = ?</fsListFilesQuery>recordTableDeleteQueries>
        <fsPathRetrievalQuery>SELECT<recordTableInitQueries>
* FROM AN_FS_PATH WHERE path = ?</fsPathRetrievalQuery>      <query>CREATE TABLE  <fsReadDataChunkQuery>SELECT data FROM AN_FS_DATA WHERE path = ? AND sequence = ?</fsReadDataChunkQuery>{{TABLE_NAME}} (record_id VARCHAR(50) NOT NULL, timestamp BIGINT, data BLOB(2G) NOT LOGGED, partition_key INTEGER, PRIMARY KEY(record_id))</query>
        <fsSetFileLengthQuery>UPDATE AN_FS_PATH SET length =<query>CREATE ? WHERE path = ?</fsSetFileLengthQuery>INDEX {{TABLE_NAME}}_TIMESTAMP ON {{TABLE_NAME}} (timestamp)</query>
        <fsTableInitQueries>    <query>CREATE         <query>CREATE TABLE AN_FS_PATH (path VARCHAR2(256), is_directory NUMBER(1), length NUMBER(19), parent_path VARCHAR2(256), PRIMARY KEY(path), FOREIGN KEY (parent_path) REFERENCES AN_FS_PATH(path))</query>
            <query>CREATE TABLE AN_FS_DATA (path VARCHAR2(256), sequence NUMBER(19), data BLOB, PRIMARY KEY (path,sequence), FOREIGN KEY (path) REFERENCES AN_FS_PATH(path))</query>
            <query>CREATE INDEX index_parent_id ON AN_FS_PATH(parent_path)</query>
        </fsTableInitQueries>
        <fsTablesCheckQuery>SELECT path FROM AN_FS_PATH WHERE path = '/'</fsTablesCheckQuery>
        <fsUpdateDataChunkQuery>UPDATE AN_FS_DATA SET data = ? WHERE path = ? AND sequence = ?</fsUpdateDataChunkQuery>
        <fsWriteDataChunkQuery>INSERT INTO AN_FS_DATA (path,sequence,data) VALUES (?,?,?)</fsWriteDataChunkQuery>
        <fsMergeDataChunkQuery>MERGE INTO AN_FS_DATA dest USING( SELECT ? path, ? sequence, ? data FROM dual) src ON(dest.path = src.path AND dest.sequence = src.sequence) WHEN NOT MATCHED THEN INSERT(path, sequence, data) VALUES(src.path, src.sequence, src.data) WHEN MATCHED THEN UPDATE SET dest.data = src.data</fsMergeDataChunkQuery>
    </database>

Localtab

title	Microsoft SQL Server

Code Block

language	xml

    <database name = "Microsoft SQL Server">
        <recordCountSupported>false</recordCountSupported>
        <paginationSupported>true</paginationSupported>
        <paginationMode>MODE2</paginationMode>
        <blobLengthRequired>true</blobLengthRequired>
        <recordTableCheckQuery>SELECT TOP 1 1 from {{TABLE_NAME}}</recordTableCheckQuery>
        <recordCountQuery>SELECT COUNT(*) FROM {{TABLE_NAME}} WHERE timestamp &gt;= ? AND timestamp &lt; ?</recordCountQuery>
        <recordDeletionQuery>DELETE FROM {{TABLE_NAME}} WHERE timestamp &gt;= ? AND timestamp &lt; ?</recordDeletionQuery>
        <recordDeletionWithIdsQuery>DELETE FROM {{TABLE_NAME}} WHERE record_id IN ({{RECORD_IDS}})</recordDeletionWithIdsQuery>
        <!--recordMergeQuery>MERGE {{TABLE_NAME}} AS dest USING (SELECT ?, ?, ?, ?) AS src (partition_key, timestamp, data, record_id) ON (dest.record_id = src.record_id) WHEN MATCHED THEN UPDATE SET partition_key = src.partition_key, timestamp = src.timestamp, data = src.data WHEN NOT MATCHED THEN INSERT(partition_key, timestamp, data, record_id) VALUES (src.partition_key, src.timestamp, src.data, src.record_id);</recordMergeQuery-->
        <recordInsertQuery>INSERT INTO {{TABLE_NAME}} (partition_key, timestamp, data, record_id) VALUES (?, ?, ?, ?)</recordInsertQuery>
        <recordUpdateQuery>UPDATE {{TABLE_NAME}} SET partition_key = ?, timestamp = ?, data = ? WHERE record_id = ?</recordUpdateQuery>
        <recordRetrievalQuery>SELECT record_id, timestamp, data FROM (SELECT ROW_NUMBER() OVER(ORDER BY record_id) AS rownumber, record_id, timestamp, data FROM {{TABLE_NAME}} WHERE partition_key &gt;= ? and partition_key &lt; ? AND timestamp &gt;= ? AND timestamp &lt; ?) AS A WHERE A.rownumber &lt;= ? AND A.rownumber &gt; ?</recordRetrievalQuery>
        <!--recordRetrievalQuery>SELECT record_id, timestamp, data FROM {{TABLE_NAME}} WHERE timestamp &gt;= ? AND timestamp &lt; ? OFFSET ? ROWS FETCH NEXT ? ROWS ONLY</recordRetrievalQuery-->
        <recordRetrievalWithIdsQuery>SELECT record_id, timestamp, data FROM {{TABLE_NAME}} WHERE record_id IN ({{RECORD_IDS}})</recordRetrievalWithIdsQuery>
        <recordTableDeleteQueries>
            <query>DROP INDEX {{TABLE_NAME}}_PARTITION_KEY</query>
            <query>DROP INDEX {{TABLE_NAME}}_TIMESTAMP</query>
            <query>DROP TABLE {{TABLE_NAME}}</query>
        </recordTableDeleteQueries>
        <recordTableInitQueries>
            <query>CREATE TABLE {{TABLE_NAME}} (record_id VARCHAR(50), timestamp BIGINT, data VARBINARY(max), partition_key INTEGER, PRIMARY KEY(record_id))</query>
            <query>CREATE INDEX {{TABLE_NAME}}_TIMESTAMP ON {{TABLE_NAME}} (timestamp)</query>
            <query>CREATE INDEX {{TABLE_NAME}}_PARTITION_KEY ON {{TABLE_NAME}} (partition_key)</query>
        </recordTableInitQueries>
        <fsDataChunkSize>10240</fsDataChunkSize>
        <fsDeletePathQuery>DELETE FROM AN_FS_PATH WHERE path = ?</fsDeletePathQuery>
        <fsDeleteDataQuery>DELETE FROM AN_FS_DATA WHERE path = ?</fsDeleteDataQuery>
        <fsUpdateDataPathQuery>UPDATE AN_FS_DATA SET path = ? WHERE path = ?</fsUpdateDataPathQuery>
        <fsFileLengthRetrievalQuery>SELECT length FROM AN_FS_PATH WHERE path = ?</fsFileLengthRetrievalQuery>
        <fsInsertPathQuery>INSERT INTO AN_FS_PATH (path,is_directory,length,parent_path) VALUES (?,?,?,?)</fsInsertPathQuery>
        <fsListFilesQuery>SELECT path FROM AN_FS_PATH WHERE parent_path = ?</fsListFilesQuery>
        <fsPathRetrievalQuery>SELECT * FROM AN_FS_PATH WHERE path = ?</fsPathRetrievalQuery>
        <fsReadDataChunkQuery>SELECT data FROM AN_FS_DATA WHERE path = ? AND sequence = ?</fsReadDataChunkQuery>
        <fsSetFileLengthQuery>UPDATE AN_FS_PATH SET length = ? WHERE path = ?</fsSetFileLengthQuery>
        <fsTableInitQueries>
            <query>CREATE TABLE AN_FS_PATH (path VARCHAR(256), is_directory BIT, length BIGINT, parent_path VARCHAR(256), PRIMARY KEY(path), FOREIGN KEY (parent_path) REFERENCES AN_FS_PATH(path))</query>
            <query>CREATE TABLE AN_FS_DATA (path VARCHAR(256), sequence BIGINT, data VARBINARY(max), PRIMARY KEY (path,sequence), FOREIGN KEY (path) REFERENCES AN_FS_PATH(path) ON DELETE CASCADE)</query>
            <query>CREATE INDEX index_parent_id ON AN_FS_PATH(parent_path)</query>
        </fsTableInitQueries>
        <fsTablesCheckQuery>SELECT path FROM AN_FS_PATH WHERE path = '/'</fsTablesCheckQuery>
        <fsUpdateDataChunkQuery>UPDATE AN_FS_DATA SET data = ? WHERE path = ? AND sequence = ?</fsUpdateDataChunkQuery>
        <fsWriteDataChunkQuery>INSERT INTO AN_FS_DATA (path,sequence,data) VALUES (?,?,?)</fsWriteDataChunkQuery>
        <!--fsMergeDataChunkQuery>MERGE AN_FS_DATA AS dest USING (SELECT ?, ?, ?) AS src (path, sequence, data) ON (dest.path = src.path AND dest.sequence = src.sequence) WHEN MATCHED THEN UPDATE SET data = src.data WHEN NOT MATCHED THEN INSERT(path, sequence, data) VALUES (src.path, src.sequence, src.data);</fsMergeDataChunkQuery-->
    </database>

Localtab

title	PostgreSQL

Code Block

language	xml

    <database name = "PostgreSQL">
        <recordCountSupported>false</recordCountSupported>
        <paginationSupported>true</paginationSupported>
        <paginationMode>MODE1</paginationMode>
        <recordTableCheckQuery>SELECT 1 FROM {{TABLE_NAME}} LIMIT 1</recordTableCheckQuery>
        <recordCountQuery>SELECT COUNT(*) FROM {{TABLE_NAME}} WHERE timestamp &gt;= ? AND timestamp &lt; ?</recordCountQuery>
        <recordDeletionQuery>DELETE FROM {{TABLE_NAME}} WHERE timestamp &gt;= ? AND timestamp &lt; ?</recordDeletionQuery>
        <recordDeletionWithIdsQuery>DELETE FROM {{TABLE_NAME}} WHERE record_id IN ({{RECORD_IDS}})</recordDeletionWithIdsQuery>
        <recordInsertQuery>INSERT INTO {{TABLE_NAME}} (partition_key, timestamp, data, record_id) VALUES (?, ?, ?, ?)</recordInsertQuery>
        <recordUpdateQuery>UPDATE {{TABLE_NAME}} SET partition_key = ?, timestamp = ?, data = ? WHERE record_id = ?</recordUpdateQuery>
        <!--recordMergeQuery>INSERT INTO {{TABLE_NAME}} (partition_key, timestamp, data, record_id) VALUES (?, ?, ?, ?) ON CONFLICT DO UPDATE partition_key=VALUES(partition_key), timestamp=VALUES(timestamp), data=VALUES(data)</recordMergeQuery-->
        <forwardOnlyReadEnabled>true</forwardOnlyReadEnabled>
        <fetchSize>1000</fetchSize>
        <recordRetrievalQuery>SELECT record_id, timestamp, data FROM {{TABLE_NAME}} WHERE partition_key &gt;= ? and partition_key &lt; ? AND timestamp &gt;= ? AND timestamp &lt; ? OFFSET ? LIMIT ?</recordRetrievalQuery>
        <recordRetrievalWithIdsQuery>SELECT record_id, timestamp, data FROM {{TABLE_NAME}} WHERE record_id IN ({{RECORD_IDS}})</recordRetrievalWithIdsQuery>
        <recordTableDeleteQueries>
            <query>DROP INDEX {{TABLE_NAME}}_PARTITION_KEY ON {{TABLE_NAME}}</query>
            <query>DROP INDEX {{TABLE_NAME}}_TIMESTAMP ON {{TABLE_NAME}}</query>
            <query>DROP TABLE IF EXISTS {{TABLE_NAME}}</query>
        </recordTableDeleteQueries>
        <recordTableInitQueries>
            <query>CREATE TABLE {{TABLE_NAME}} (record_id VARCHAR(50), timestamp BIGINT, data BYTEA, partition_key INTEGER, PRIMARY KEY(record_id))</query>
            <query>CREATE INDEX {{TABLE_NAME}}_TIMESTAMP ON {{TABLE_NAME}} (timestamp)</query>
            <query>CREATE INDEX {{TABLE_NAME}}_PARTITION_KEY ON {{TABLE_NAME}} (partition_key)</query>
        </recordTableInitQueries>
        <fsDataChunkSize>10240</fsDataChunkSize>
        <fsDeletePathQuery>DELETE FROM AN_FS_PATH WHERE path = ?</fsDeletePathQuery>
        <fsDeleteDataQuery>DELETE FROM AN_FS_DATA WHERE path = ?</fsDeleteDataQuery>
        <fsUpdateDataPathQuery>UPDATE AN_FS_DATA SET path = ? WHERE path = ?</fsUpdateDataPathQuery>
        <fsFileLengthRetrievalQuery>SELECT length FROM AN_FS_PATH WHERE path = ?</fsFileLengthRetrievalQuery>
        <fsInsertPathQuery>INSERT INTO AN_FS_PATH (path,is_directory,length,parent_path) VALUES (?,?,?,?)</fsInsertPathQuery>
        <fsListFilesQuery>SELECT path FROM AN_FS_PATH WHERE parent_path = ?</fsListFilesQuery>
        <fsPathRetrievalQuery>SELECT * FROM AN_FS_PATH WHERE path = ?</fsPathRetrievalQuery>
        <fsReadDataChunkQuery>SELECT data FROM AN_FS_DATA WHERE path = ? AND sequence = ?</fsReadDataChunkQuery>
        <fsSetFileLengthQuery>UPDATE AN_FS_PATH SET length = ? WHERE path = ?</fsSetFileLengthQuery>
        <fsTableInitQueries>
            <query>CREATE TABLE AN_FS_PATH (path VARCHAR(256), is_directory BOOLEAN, length BIGINT, parent_path VARCHAR(256), PRIMARY KEY(path))</query>
            <query>CREATE TABLE AN_FS_DATA (path VARCHAR(256), sequence BIGINT, data BYTEA, PRIMARY KEY (path,sequence))</query>
            <query>CREATE INDEX index_parent_id ON AN_FS_PATH(parent_path)</query>
        </fsTableInitQueries>
        <fsTablesCheckQuery>SELECT path FROM AN_FS_PATH WHERE path = '/'</fsTablesCheckQuery>
        <fsUpdateDataChunkQuery>UPDATE AN_FS_DATA SET data = ? WHERE path = ? AND sequence = ?</fsUpdateDataChunkQuery>
        <fsWriteDataChunkQuery>INSERT INTO AN_FS_DATA (path,sequence,data) VALUES (?,?,?)</fsWriteDataChunkQuery>
        <!--fsMergeDataChunkQuery>INSERT INTO AN_FS_DATA (path,sequence,data) VALUES (?, ?, ?) ON CONFLICT DO UPDATE data=VALUES(data)</fsMergeDataChunkQuery-->
    </database>

Localtab

title	DB2.*

Code Block

language	xml

    <database name = "DB2.*">
        <recordCountSupported>false</recordCountSupported>
        <paginationSupported>true</paginationSupported>
        <paginationMode>MODE1</paginationMode>
        <blobLengthRequired>true</blobLengthRequired>
        <recordTableCheckQuery>SELECT 1 FROM {{TABLE_NAME}} FETCH FIRST 1 ROWS ONLY</recordTableCheckQuery>
        <recordCountQuery>SELECT COUNT(*) FROM {{TABLE_NAME}} WHERE timestamp &gt;= ? AND timestamp &lt; ?</recordCountQuery>
        <recordDeletionQuery>DELETE FROM {{TABLE_NAME}} WHERE timestamp &gt;= ? AND timestamp &lt; ?</recordDeletionQuery>
        <recordDeletionWithIdsQuery>DELETE FROM {{TABLE_NAME}} WHERE record_id IN ({{RECORD_IDS}})</recordDeletionWithIdsQuery>
        <recordMergeQuery>MERGE INTO {{TABLE_NAME}} AS dest USING (VALUES(?, ?, ?, ?)) AS src (partition_key, timestamp, data, record_id) ON dest.record_id = src.record_id WHEN MATCHED THEN UPDATE SET dest.partition_key = src.partition_key, dest.timestamp = src.timestamp, dest.data = src.data WHEN NOT MATCHED THEN INSERT (partition_key, timestamp, data, record_id) VALUES (src.partition_key, src.timestamp, src.data, src.record_id)</recordMergeQuery>
        <recordInsertQuery>INSERT INTO {{TABLE_NAME}} (partition_key, timestamp, data, record_id) VALUES (?, ?, ?, ?)</recordInsertQuery>
        <recordUpdateQuery>UPDATE {{TABLE_NAME}} SET partition_key = ?, timestamp = ?, data = ? WHERE record_id = ?</recordUpdateQuery>
        <recordRetrievalQuery>SELECT record_id, timestamp, data FROM {{TABLE_NAME}} WHERE partition_key &gt;= ? and partition_key &lt; ? AND timestamp &gt;= ? AND timestamp &lt; ? LIMIT ?,?</recordRetrievalQuery>
        <recordRetrievalWithIdsQuery>SELECT record_id, timestamp, data FROM {{TABLE_NAME}} WHERE record_id IN ({{RECORD_IDS}})</recordRetrievalWithIdsQuery>
        <recordTableDeleteQueries>
            <query>DROP INDEX {{TABLE_NAME}}_PARTITION_KEY</query>
            <query>DROP INDEX {{TABLE_NAME}}_TIMESTAMP</query>
            <query>DROP TABLE {{TABLE_NAME}}</query>
        </recordTableDeleteQueries>
        <recordTableInitQueries>
            <query>CREATE TABLE {{TABLE_NAME}} (record_id VARCHAR(50) NOT NULL, timestamp BIGINT, data BLOB(2G) NOT LOGGED, partition_key INTEGER, PRIMARY KEY(record_id))</query>
            <query>CREATE INDEX {{TABLE_NAME}}_TIMESTAMP ON {{TABLE_NAME}} (timestamp)</query>
            <query>CREATE INDEX {{TABLE_NAME}}_PARTITION_KEY ON {{TABLE_NAME}} (partition_key)</query>
        </recordTableInitQueries>
        <fsDataChunkSize>10240</fsDataChunkSize>
        <fsDeletePathQuery>DELETE FROM AN_FS_PATH WHERE path = ?</fsDeletePathQuery>
        <fsDeleteDataQuery>DELETE FROM AN_FS_DATA WHERE path = ?</fsDeleteDataQuery>
        <fsUpdateDataPathQuery>UPDATE AN_FS_DATA SET path = ? WHERE path = ?</fsUpdateDataPathQuery>
        <fsFileLengthRetrievalQuery>SELECT length FROM AN_FS_PATH WHERE path = ?</fsFileLengthRetrievalQuery>
        <fsInsertPathQuery>INSERT INTO AN_FS_PATH (path,is_directory,length,parent_path) VALUES (?,?,?,?)</fsInsertPathQuery>
        <fsListFilesQuery>SELECT path FROM AN_FS_PATH WHERE parent_path = ?</fsListFilesQuery>
        <fsPathRetrievalQuery>SELECT * FROM AN_FS_PATH WHERE path = ?</fsPathRetrievalQuery>
        <fsReadDataChunkQuery>SELECT data FROM AN_FS_DATA WHERE path = ? AND sequence = ?</fsReadDataChunkQuery>
        <fsSetFileLengthQuery>UPDATE AN_FS_PATH SET length = ? WHERE path = ?</fsSetFileLengthQuery>
        <fsTableInitQueries>
            <query>CREATE TABLE AN_FS_PATH (path VARCHAR(256) NOT NULL, is_directory SMALLINT, length BIGINT, parent_path VARCHAR(256), PRIMARY KEY(path))</query>
            <query>CREATE TABLE AN_FS_DATA (path VARCHAR(256) NOT NULL, sequence BIGINT NOT NULL, data BLOB(2G) NOT LOGGED, PRIMARY KEY (path,sequence))</query>
            <query>CREATE INDEX index_parent_id ON AN_FS_PATH(parent_path)</query>
        </fsTableInitQueries>
        <fsTablesCheckQuery>SELECT path FROM AN_FS_PATH WHERE path = '/'</fsTablesCheckQuery>
        <fsUpdateDataChunkQuery>UPDATE AN_FS_DATA SET data = ? WHERE path = ? AND sequence = ?</fsUpdateDataChunkQuery>
        <fsWriteDataChunkQuery>INSERT INTO AN_FS_DATA (path,sequence,data) VALUES (?,?,?)</fsWriteDataChunkQuery>
        <fsMergeDataChunkQuery>MERGE INTO AN_FS_DATA AS dest USING (VALUES(?, ?, ?)) AS src (path, sequence, data) ON (dest.path = src.path AND dest.sequence = src.sequence) WHEN MATCHED THEN UPDATE SET dest.data = src.data WHEN NOT MATCHED THEN INSERT(path, sequence, data) VALUES (src.path, src.sequence, src.data)</fsMergeDataChunkQuery>
    </database>

The above configuration properties are described below.

Property	Description
database name	The target RDBMS name to which this query template applies, a regular expression can be put here, to give a pattern on to which a database product name can be mapped. For example, DB2 will give different product name strings when running in Windows and Unix based OS environments, so a regular expression like "DB2.*" will match for all DB2 based database server environments.
minVersion	The minumum version of the database server this configuration will match to, this will be of format "majorVersion.minorVersion".
maxVersion	The maximum version of the database server this configuration will match to, this will be of format "majorVersion.minorVersion".
`recordCountSupported`	This property specifies whether it is possible to take a count of all the records in the record store.
`paginationSupported`	This property specifies whether dividing the output of the record store to manageable chunks is allowed.
`paginationMode`	This property specifies the pagination mode. Possible values are as follows.
`blobLengthRequired`	This property specifies whether a length should be assigned to data blocks saved in the record store/file system or not.
`recordCountQuery`	The query template to take a count of the records in the record store.
`recordDeletionQuery`	The query template to delete a record in the record store.
`recordDeletionWithIdsQuery`	The query template to delete records with specific IDs in the record store.
`recordMergeQuery`	The query template to merge two rows of data of a table in the record store.
`forwardOnlyReadEnabled`	If this property is set to `true`, the cursor can only move forward on the result set when retrieving records from record store.
`fetchSize`	Number of rows that should be fetched from the database if more rows are needed on the generated result set when retrieving records from record store.
`recordInsertQuery`	The query template to insert new rows of data to a table in the record store.
`recordUpdateQuery`	The query template to modify the existing table rows in the record store.
`recordRetrievalQuery`	The query template to retrieve a record from the record store.
`recordRetrievalWithIdsQuery`	The query template to retrieve records with specific IDs from the record store.
`recordTableCheckQuery`	The query template to check tables in the record store.
`recordTableDeleteQueries`	The query template to delete a table in the record store.
`recordTableInitQueries`	The query template to initialize the tables in the record store.
`fsDataChunkSize`	The maximum size for a chunk of data stored in the file system.
`fsDeletePathQuery`	The query template to delete all the files in a specific path in the file system.
`fsDeleteDataQuery`	The query template to delete selected data from the file system.
`fsFileLengthRetrievalQuery`	The query template to retrieve the length of a file stored in the file system.
`fsInsertPathQuery`	The query template to insert a new path to the file system.
`fsListFilesQuery`	The query template to list all the files in the file system.
`fsPathRetrievalQuery`	The query template to retrieve all the paths available in the file system.
`fsReadDataChunkQuery`	The query template to read a data chunk saved in the file system.
`fsSetFileLengthQuery`	The query template to specify a length for the files saved in the file path.
`fsTableInitQueries`	The query template to initiate a table saved in the file system.
`fsTablesCheckQuery`	The query template to check the tables saved in the file system.
`fsMergeDataChunkQuery`	The query template to merge two or more data chunks saved in the file system.
`fsUpdateDataChunkQuery`	The query template to update a data chunk saved in the file system.
`fsWriteDataChunkQuery`	The query template to create a new data chunk in the file system.

Configuring the datasources

You need to define datasources to connect to the above underlying RDBMS implementations as described below.

...

INDEX {{TABLE_NAME}}_PARTITION_KEY ON {{TABLE_NAME}} (partition_key)</query>
        </recordTableInitQueries>
    </database>

The above configuration properties are described below.

Property	Description
database name	The target RDBMS name to which this query template applies, a regular expression can be put here, to give a pattern on to which a database product name can be mapped. For example, DB2 will give different product name strings when running in Windows and Unix based OS environments, so a regular expression like "DB2.*" will match for all DB2 based database server environments.
minVersion	The minumum version of the database server this configuration will match to, this will be of format "majorVersion.minorVersion".
maxVersion	The maximum version of the database server this configuration will match to, this will be of format "majorVersion.minorVersion".
`recordCountSupported`	This property specifies whether it is possible to take a count of all the records in the record store.
`paginationSupported`	This property specifies whether dividing the output of the record store to manageable chunks is allowed.
`paginationMode`	This property specifies the pagination mode. Possible values are as follows.
`blobLengthRequired`	This property specifies whether a length should be assigned to data blocks saved in the record store or not.
`recordCountQuery`	The query template to take a count of the records in the record store.
`recordDeletionQuery`	The query template to delete a record in the record store.
`recordDeletionWithIdsQuery`	The query template to delete records with specific IDs in the record store.
`recordMergeQuery`	The query template to merge two rows of data of a table in the record store.
`forwardOnlyReadEnabled`	If this property is set to `true`, the cursor can only move forward on the result set when retrieving records from record store.
`fetchSize`	Number of rows that should be fetched from the database if more rows are needed on the generated result set when retrieving records from record store.
`recordInsertQuery`	The query template to insert new rows of data to a table in the record store.
`recordUpdateQuery`	The query template to modify the existing table rows in the record store.
`recordRetrievalQuery`	The query template to retrieve a record from the record store.
`recordRetrievalWithIdsQuery`	The query template to retrieve records with specific IDs from the record store.
`recordTableCheckQuery`	The query template to check tables in the record store.
`recordTableDeleteQueries`	The query template to delete a table in the record store.
`recordTableInitQueries`	The query template to initialize the tables in the record store.

Configuring the datasources

Change the configurations of the WSO2_ANALYTICS_EVENT_STORE_DB datasource in the <DAS_HOME>/repository/conf/datasources/analytics-datasources.xml file accordingly. For information on the datasource configurations, see Configuring an RDBMS Datasource.

Configuring the datasource for the Analytics File System datasource

Change the configurations of the WSO2_ANALYTICS_FS_DB datasource in the <DAS_HOME>/repository/conf/datasources/ analytics-datasources.xml file accordingly. For information on the datasource configurations, see Configuring an RDBMS Datasource.

Versions Compared

Old Version 1

New Version Current

Key

Configurations for the Analytics File System

RDBMS query configuration

RDBMS query configuration

Configuring the datasources

Configuring the datasources

Configuring the datasource for the Analytics File System datasource