Eclipse Community Forums: Eclipse Titan » Connecting TTCN-3 and Titan to SQL databases

Help

Home

Home » Eclipse Projects » Eclipse Titan » Connecting TTCN-3 and Titan to SQL databases(Usage of the SQL test port)

Show: Today's Messages :: Show Polls :: Message Navigator

Connecting TTCN-3 and Titan to SQL databases [message #1717265]

Fri, 11 December 2015 08:55

Elemer Lelik

Messages: 1120
Registered: January 2015

Senior Member

The example posted in the attachment is essentially different from all the previous ones,
as it refers to a different type of communication mechanism ( see Core language standard, ch 22.1 The communication mechanisms):
instead of a message based communication we are already familiar with it uses procedure-based communication.
This comes in two flavors: blocking and non-blocking and both are illustrated in the code.

First , lets' examine the basic functions used:

  function f_mysql_Connect() runs on MySQL_CT return integer{
    MySQL_PCO.call( S_SQL_connect:{vl_conn} ){
      [] MySQL_PCO.getreply(S_SQL_connect:{?}) -> value v_result{
        v_connId := v_result.connId;
        log("ConnID: ",v_connId);
      }
      [] MySQL_PCO.catch( S_SQL_connect, SQL_exception:?) -> value v_exception{
        log("Conn exception: ", v_exception);
        return -1;
      } 
    }
    
    return v_connId;
  }

where vl_conn takes it's value from tsp_conn:

  var SQL_Connection vl_conn:=tsp_conn;

which contains teh necessarry connection parameters:

modulepar {

 SQL_Connection tsp_conn:={
    MySQL_engine,
      {
        {"host","localhost"},
        {"user","root"},
        {"password","root"},
        {"database","test"},
        {"port","3306"},
        {"unix_socket","/var/run/mysqld/mysqld.sock"},
        {"char_set","utf8"}
      }
    };

}

This function initiates a client connection to the MySQL database and returns a connection Id.
Its' counterpart is the disconnect function for a given connection Id:

function f_mysql_Disconnect(integer p_connId) runs on  MySQL_CT return boolean{
    MySQL_PCO.call( S_SQL_disconnect:{p_connId}){
      [] MySQL_PCO.getreply(S_SQL_disconnect:{?}) -> value v_result{
        log("Disconnect result: ", v_result);
      }
      [] MySQL_PCO.catch(S_SQL_disconnect, SQL_exception:?) -> value v_exception {
        log("Disconnect exception: ", v_exception);
        return false;
      }
    }
    
    return true;
  }

A blocking query can be initiated with:

function f_mysql_query(in integer p_connId,in charstring pl_query) runs on  MySQL_CT return boolean{
    MySQL_PCO.call( S_SQL_Query:{p_connId, pl_query}){
      [] MySQL_PCO.getreply(S_SQL_Query:{?,?}) -> value v_query_result{
        log("Query result: ", v_query_result);
      }
      [] MySQL_PCO.catch(S_SQL_Query, SQL_exception:?) -> value v_exception {
        log("Query exception: ", v_exception);
        return false;
      }
    }
    
    return true;
  }

where the query appears as a characterstring parameter,

while a non-blocking one with:

        function f_mysql_query_nowait0(in integer p_connId, in charstring pl_query) runs on  MySQL_CT  {
    MySQL_PCO.call( S_SQL_Query:{p_connId, pl_query}, nowait)
    }

In case of a non-blocking query the user will have to take separate care of collecting teh reply, catching the exceptions etc.

//-----------------------------------------------------------------------------
function f_SQL_dialogue_nonblocking(charstring p_query)  runs on MySQL_CT 
//-----------------------------------------------------------------------------
{
var charstring v_char:=p_query;

   v_connId:=f_mysql_Connect();  
  if( v_connId==-1){    stop;   }

 f_mysql_query_nowait0(v_connId,v_char);

alt {
                
[]MySQL_PCO.getreply(S_SQL_Query:{?,?}  value t_SQL_Query_result) -> value v_query_result {
       log("Query result: ", v_query_result);
      }
      
      
[]MySQL_PCO.getreply(S_SQL_Query:{?,?}) -> value v_query_result{
       log("Query result: ", v_query_result);    
      }


   [] MySQL_PCO.catch(S_SQL_Query, SQL_exception:?) -> value v_exception {
        log("Query exception: ", v_exception);
      }
                
    }            

f_mysql_Disconnect(v_connId);

}

The non-blocking query has the advantage that the user can do other things while waiting for the reply, for instance initiating another procedure.

The function f_SQL_dialogue_blocking() exemplifies a simple usage , creating MySQL database tables, initializing and reading values etc.

 sqlFlag:= f_mysql_query(v_connId,"DROP TABLE IF EXISTS Devices ;");

There are a couple of things worth highlighting here:

-The tables are maintained in the memory

CREATE TABLE Devices ( id int NOT NULL, protTimer float NOT NULL, protState int NOT NULL, associatedId int NOT NULL,primary key (id)  ) ENGINE=MEMORY;

which allows a faster execution at the expense of an increased risk of volatility: tables are not stored on hard disk, so in case of an incident, they may disappear, so it might be a good idea to back them up periodically.
Else, ENGINE=INNODB will trigger the classical behaviour.
The difference in speed between the two options can easily be tested and measured, for instance by creating a classical and a memory -based table and running the same write/read query against them say a million times;

-The query

 v_query:= "CALL initStatCounters(10)";  
 sqlFlag:= f_mysql_query(v_connId,v_query);

calls a stored procedure initStatCounters;

this has to be declared before running the queries as below:

DROP PROCEDURE IF EXISTS initStatCounters;

DELIMITER #
	CREATE PROCEDURE initStatCounters(sessionrange int) 
	BEGIN 
		DECLARE v_counter int unsigned DEFAULT 0; 
		TRUNCATE TABLE StatisticalCounters;
		START transaction; 
			while v_counter < sessionrange DO
				INSERT INTO StatisticalCounters (id, nrOfInitiatedSessions, nrOfSuccessfulSessions,nrOfUnsuccessfulSessions,nrOfResultCodeNonZero,nrOfSessionsLostWithTimeout)
				VALUES (v_counter, 0,0,0,0,0);
				SET v_counter=v_counter+1; 
				END while;
		COMMIT; 
	END # 
DELIMITER ;

The code has been tested with mariadb client libraries, which are perfectly MySQL compatible, and also with MySQLs' own client libraries:

# Flags for the C++ preprocessor (and makedepend as well):
CPPFLAGS = -D$(PLATFORM) -I$(TTCN3_DIR)/include  -I/opt/lampp/include/
#

# Flags for the C++ compiler:
CXXFLAGS = -Wall

# Flags for the linker:
LDFLAGS = -L/opt/lampp/lib
#/home/james00/mariadb-10.0.10-linux-x86_64/lib

The Titan SQL test port can be found at
https://github.com/eclipse/titan.TestPorts.SQLasp
and currently has extensions for SQLite and MySQL.

Best regards

Elemer