[Short Blog] Testing RDBMS store with WSO2 APIM SI analytic triggers

 This blog post contain steps and instructions on testing RDBMS store with SI analytics. The common problem while testing SI is complexity of calling stream without tooling UI. Here, we used siddhi triggers on the siddhi script it self to trigger RDBMS event process.

1. Download SI product pack from WSO2 Github https://github.com/wso2/streaming-integrator/releases
2. Download mysql-connector-java-8.0.18 MySQL DB driver and copy it into the `<SI_HOME>/lib`
3. Create database named production in MySQL
4. Create table SweetProductionTablewith following SQL:

CREATE TABLE SweetProductionTable (name VARCHAR(20),amount double(10,2));

5. Insert data into the table with following SQL:

INSERT INTO SweetProductionTable VALUES ('Sugar',23.50);

3. Create a file Store-rdbms.siddhi and add the following content into the `<SI_HOME>/wso2/server/deployment/siddhi-files/Store-rdbms.siddhi` file.

@App:name("Store-rdbms")

@App:description('Receive events via simulator and persist the received data in the store.')

define trigger mytrigger at 'start';


define stream insertSweetProductionStream (name string, amount double);
define stream deleteSweetProductionStream (name string);
define stream searchSweetProductionStream (name string);
define stream updateSweetProductionStream (name string, amount double);
define stream updateOrInsertSweetProductionStream (name string, amount double);
define stream containsSweetProductionStream (name string, amount double);

@sink(type='log')
define stream logStream(name string, amount double);

@Store(type="rdbms",
       jdbc.url="jdbc:mysql://localhost:3306/production?useSSL=false&allowPublicKeyRetrieval=true",
       username="root",
       password="" ,
       jdbc.driver.name="com.mysql.jdbc.Driver")
--@PrimaryKey("name")
@index("amount")
define table SweetProductionTable (name string, amount double);

/* Inserting event into the mysql database */
@info(name='query1')
from insertSweetProductionStream
insert into SweetProductionTable;

/* Deleting event from mysql database */
@info(name = 'query2')
from deleteSweetProductionStream
delete SweetProductionTable
on SweetProductionTable.name == name ;

/* Updating event from mysql database */
@info(name = 'query3')
from updateSweetProductionStream
update SweetProductionTable
on SweetProductionTable.name == name ;

/* Updating or inserting event from mysql database */
@info(name = 'query4')
from updateOrInsertSweetProductionStream
update or insert into SweetProductionTable
on SweetProductionTable.name == name;

/* Siddhi In in mysql database */
@info(name = 'query5')
from containsSweetProductionStream
[(SweetProductionTable.name == name and SweetProductionTable.amount == amount) in SweetProductionTable]
insert into logStream;

--Perform a join on raw material name so that the data in the store can be viewed
@info(name='query6')
from searchSweetProductionStream as s join SweetProductionTable as sp
    on s.name == sp.name
select sp.name, sp.amount
insert into logStream;

from mytrigger#window.delay(10 sec) select 'abcd' as name insert into searchSweetProductionStream;
from mytrigger#window.delay(15 sec) select 'abcde' as name, 2132.0 as amount insert into insertSweetProductionStream;
from mytrigger#window.delay(17 sec) select 'abcde' as name insert into searchSweetProductionStream;
from mytrigger#window.delay(10 sec) select 'abcd' as name insert into deleteSweetProductionStream;

4. Start the SI server with sh <SI_HOME>/bin/server.sh command. Replace <SI_HOME> with SI home location.

5. Check the logs getting printed on the SI console.