Connect and use a PostgreSQL database

PRESENTATION

The aim of this tutorial is to show you how to connect and use a pgSQL database to your model. It can be used in the case where we have a lot of data. For instance, we used it on projects about urban logistics and transport supervision. Before starting this tutorial, please follow these guides to:

Get PostgreSQL → Connect database to your model - Download and install PostgreSQL
Get Cygwin packages → Cygwin - Packages
Add environment variables → Cygwin - Path variables

Prerequisites

You should have already installed PostgreSQL.

News skills that you will acquire

On Designer:

Use a database.

On Virtual Bench:

Use a new Context elements: Form, Pie chart.

Duration

CREATE THE MODEL ON PGSQL

Import the database on PgSQL

In this part you will restore the tuto_SQL database already created (download it here). It is composed of different tables :

"Person" where is saved person's first name, last name and age.
"Spouse" which do the relationship between two persons from the table "Person"
"Child" which do the relationship between three persons (the child and the two parents)

To do so, you will open PgSQL and follow these steps :

Create a new database

Name the database "tutoSQL" and save it.

Restore the database you download

In filename, search for the tuto_SQL.backup file and click on restore.

Create functions on PgSQL

Click on the query tool ( Select the backup file ) and you will see the query editor (documentation about the query editor). In the query editor, you can tap the requests you want. Here, we will use it in order to create functions useful for our designer model.

Function name_from_id

The function name_from_id take as input an id and returns the person first name and last name. Copy and paste the following code and press F5.

CREATE OR REPLACE FUNCTION public.name_from_id(IN id_input integer DEFAULT '0')
RETURNS TABLE (firstname text, lastname text)
language plpgsql

AS $BODY$

BEGIN
DECLARE "firstname_p" text;
DECLARE "lastname_p" text;

BEGIN

SELECT Person.firstname from "Person" as Person WHERE id=id_input INTO firstname_p;
SELECT Person.lastname from "Person" as Person WHERE id=id_input INTO lastname_p;

RETURN QUERY VALUES (firstname_p, lastname_p);
END;

END;

$BODY$;

Function spouse

The function spouse takes as input :

the first name of a parent
last name of a person

and returns

the first name of the spouse
the last name of the spouse
the age of the spouse.

It returns "-" if the person doesn't exist or doesn't have spouse. Copy and paste the following code and press F5.

CREATE OR REPLACE FUNCTION public.spouse(IN firstname_input text DEFAULT '0', IN lastname_input text DEFAULT '0')
RETURNS TABLE (firstname_spouse text, lastname_spouse text, age_spouse integer)

language plpgsql 

AS $BODY$

BEGIN
DECLARE "count1" integer;
DECLARE "count2" integer;
DECLARE "id_spouse" integer;
DECLARE "firstname_spouse" text;
DECLARE "lastname_spouse" text;
DECLARE "age_spouse" integer;
DECLARE "sens" integer;

BEGIN

SELECT count(sps.id_person2) from "Person" as prs, "Spouse" as sps where prs.lastname=lastname_input and prs.firstname=firstname_input and sps.id_person1=prs.id INTO count1; 
SELECT count(sps.id_person1) from "Person" as prs, "Spouse" as sps where prs.lastname=lastname_input and prs.firstname=firstname_input and sps.id_person2=prs.id INTO count2; 
if(count1 != 0) then
	SELECT sps.id_person2 from "Person" as prs, "Spouse" as sps where prs.lastname=lastname_input and prs.firstname=firstname_input and sps.id_person1=prs.id INTO id_spouse; 
	SELECT firstname from "Person" where id=id_spouse INTO firstname_spouse;
	SELECT lastname from "Person" where id=id_spouse INTO lastname_spouse;
	SELECT age from "Person" where id=id_spouse INTO age_spouse;
elsif (count2 != 0) then
	SELECT sps.id_person1 from "Person" as prs, "Spouse" as sps where prs.lastname=lastname_input and prs.firstname=firstname_input and sps.id_person1=prs.id INTO id_spouse; 
	SELECT firstname from "Person" where id=id_spouse INTO firstname_spouse;
	SELECT lastname from "Person" where id=id_spouse INTO lastname_spouse;
	SELECT age from "Person" where id=id_spouse INTO age_spouse;
else
    firstname_spouse:='-';
    lastname_spouse:='-';
    age_spouse:=-1;
end if; 

RETURN QUERY select firstname_spouse, lastname_spouse, age_spouse;

END;

END;

$BODY$;

You can test this function by writing in the Query editor SELECT * from spouse('Lea','Vasseur') and press F5.

Function parents

The function parents takes as input

the first name of the child
the last name of the child

and returns two rows (one for each parent):

the first name of the parent
the last name of the parent
the age of the parents

Copy and paste the following code and press F5.

CREATE OR REPLACE FUNCTION public.parents(IN firstname_input text DEFAULT '0', IN lastname_input text DEFAULT '0')
RETURNS TABLE (firstname_p1 text, lastname_p1 text, age_p1 integer)

language plpgsql 

AS $BODY$

BEGIN
DECLARE "id_p1" integer;
DECLARE "firstname_parent1" text;
DECLARE "lastname_parent1" text;
DECLARE "age_parent1" integer;

DECLARE "id_p2" integer;
DECLARE "firstname_parent2" text;
DECLARE "lastname_parent2" text;
DECLARE "age_parent2" integer;


BEGIN

SELECT id_parent1 from "Child" as child, "Person" as prs where prs.firstname=firstname_input AND prs.lastname=lastname_input AND prs.id=child.id_child INTO id_p1;
SELECT firstname from name_from_id(id_p1) INTO firstname_parent1;
SELECT lastname from name_from_id(id_p1) INTO lastname_parent1;
SELECT age from "Person" where id=id_p1 INTO age_parent1;

SELECT id_parent2 from "Child" as child, "Person" as prs where prs.firstname=firstname_input AND prs.lastname=lastname_input AND prs.id=child.id_child INTO id_p2;
SELECT firstname from name_from_id(id_p2) INTO firstname_parent2;
SELECT lastname from name_from_id(id_p2) INTO lastname_parent2;
SELECT age from "Person" where id=id_p2 INTO age_parent2;


RETURN QUERY VALUES (firstname_parent1, lastname_parent1, age_parent1),(firstname_parent2, lastname_parent2, age_parent2);

END;

END;

$BODY$;

You can test this function by writing in the Query editor SELECT * from parents('Marty','Vasseur') and press F5.

Function new born

This function registers a new born. For that, it takes as input :

first name of the first parent
last name of the first parent
first name of the second parent
last name of the second parent
first name of the new born
last name of the new born

It will add the new born in the "Person" table and will do the relationship between the two parents and the new born in the "Child" table.

CREATE OR REPLACE FUNCTION public.newborn(IN firstnamep1_input text DEFAULT '0', IN lastnamep1_input text DEFAULT '0', IN firstnamep2_input text DEFAULT '0', IN lastnamep2_input text DEFAULT '0', IN firstnamec1_input text DEFAULT '0', IN lastnamec1_input text DEFAULT '0')
RETURNS TABLE (a text)

language plpgsql 

AS $BODY$

BEGIN
DECLARE "id_p1" integer;

DECLARE "id_p2" integer;

DECLARE "id_c" integer;

BEGIN

INSERT INTO "Person" (firstname, lastname, age) VALUES ( firstnamec1_input, lastnamec1_input, '0');
SELECT id from "Person" where firstname=firstnamec1_input AND lastname=lastnamec1_input INTO id_c;
SELECT id from "Person" where firstname=firstnamep1_input AND lastname=lastnamep1_input INTO id_p1;
SELECT id from "Person" where firstname=firstnamep2_input AND lastname=lastnamep2_input INTO id_p2;
INSERT INTO "Child" (id_parent1, id_parent2, id_child) VALUES (id_p1, id_p2, id_c);

END;

END;

$BODY$;

Function Death

This function registers a death. For that, it takes as input :

the first name of the person who died
the last name of the person who died

If the person has a spouse, the couple won't exists anymore (in the table "Spouse"). If the person who died is the child of two parents, the parent will loose their child. (in the table "Child"). If the who died is a parent of someone, he won't be able to hold this responsibility anymore (in the table "Child"). Finally, as this person is not alive anymore, we can remove him/her from "Person".

CREATE OR REPLACE FUNCTION public.death(IN firstname_input text DEFAULT '0', IN lastname_input text DEFAULT '0')
RETURNS TABLE (a text)

language plpgsql 

AS $BODY$

BEGIN
DECLARE "id_p" integer;

BEGIN

SELECT id from "Person" WHERE firstname=firstname_input AND lastname=lastname_input INTO id_p;
DELETE from "Spouse" WHERE id_person1=id_p OR id_person2=id_p; 
DELETE from "Child" WHERE id_child=id_p;
UPDATE "Child" SET id_parent1=null WHERE id_parent1=id_p;
UPDATE "Child" SET id_parent2=null WHERE id_parent2=id_p;
DELETE from "Person" WHERE firstname=firstname_input AND lastname=lastname_input;

END;

END;

$BODY$;

Create the model on Designer

Create a model called TutorialSQL
Create a new project called "Organization"
Create a service called "User Interface" and a service called "Person Management"
Create a life phase Standard use in User Interface and in Person Management

Create a DataType : person_data

Create a type called person_data where is stored the person's name and lastname

Create the use cases in the Service User interface

In the use case, create a New actor called "User"
In the use case, create new use cases called "Asks informations", "Declares a birth or a death" and "Receives informations"

Create the use cases in the Service Person Management

In the use case, create two new use cases called "Process the requests" and "Register the event"

Setting the DefaultNode to create the pointer for the database

In order to create the TutorialSQL_defaultNode_Organization, generate the code TutorialSQL by using Cygwin. Then set the compilation properties of TutorialSQL_defaultNode_Organization and include the libraries.

As we will create a model which will communicate with our database during the simulation, follow the documentation Connect database to your model - Method 2 : Connection to the database with a pointer.

Create the stories to request the spouse information

New story ask the spouse information in the use case Ask Information of the Service User interface

Create a story called "ask the spouse information"

For that :

Create the flow between User : User and User InterfaceBB
- Create an interface between User : User and User InterfaceBB
- Trace the flow request_spouse_information between User : User and User InterfaceBB
Create an internal activity called "convert the spouse id"
- Create a variable called id_spouse with the person_data type
- Complete the activity with id_spouse = request_spouse_informations_id;
Create an interface between User InterfaceBB and Person management

Trace the flow between User InterfaceBB and Person management called informations to request the spouse identity with value = id_spouse
Generate the state machine

New story Process the spouse request in the use case Process the requests of the Service Person Management

In the use case Process the requests of the Service Person Management, create a story called "Process the spouse request"

For that :

Trace informations to request the spouse identity between :User interfaceBB and Person ManagementBB
- Drag and Drop the interface I_transmission_of_the_requests
Create an internal activity called "check the spouse information"
- Copy and paste the following code in the internal activity :

try {
	pqxx::result r;
	try {
		pqxx::work txn { *c }; pqxx::result temp { txn.exec("SELECT * from spouse('"+informations_to_request_the_spouse_identity_value.firstname+"','"+informations_to_request_the_spouse_identity_value.lastname+"')" )};
	
	     std::cout<<"After 1st sql"<<std::endl;
		txn.commit();
		std::cout<<"After 1st commit"<<std::endl;
		r = temp;
	}
	catch (pqxx::sql_error const& e) {
		std::cerr << "SQL error: " << e.what() << std::endl;
		std::cerr << "Query was: " << e.query() << std::endl;
	}
	catch (std::exception const& e) {
		std::cerr << "Error: " << e.what() << std::endl;
	}

	if (r.size() > 0) {
		//std::cout<<"size result "<<r.size()<<std::endl;
		for (pqxx::result::const_iterator line = r.begin(); line != r.end();
			++line) {

			//spouse_information.firstname = std::stod(line[0].as<std::string>());
			spouse_information.firstname = line[0].as<std::string>();
			spouse_information.lastname = line[1].as<std::string>();
			//spouse_information.lastname=  std::stod(line[1].as<std::string>());
			// this loop will go through every line of your pgSQL request


			// here you can display or save data from your request
			//int var=std::stod(line[0].as<std::string>());

		}
	}
}
catch (std::exception const& e) {
	std::cerr << "Error: " << e.what() << std::endl;
}

Create the flows between : User InterfaceBB and Person ManagementBB
- Create an interface between : User InterfaceBB and Person ManagementBB
- Trace the flow spouse information to the user interface between Person ManagementBB and : User InterfaceBB
Generate the state machine

New story Receive spouse information in the use case Receive Information of the Service *User interface

In the use case Receive Information of the Service User interface, create a story called "Receive spouse information"

For that :

Trace the flow spouse information to the user interface between User InterfaceBB and User : User
Create the flows betwen User InterfaceBB and User : User
- Create an interface I_provides_the_information_to_the_user between User : User and User InterfaceBB
- Trace the flow the spouse information between User InterfaceBB and User : User
Generate the state machine

Create the stories to request the parents name

New story Ask for the parents name in the use case Ask information of the Service User Interface

In the use case Ask information of the Service User Interface, create a story called "Ask for the parents name"

Trace the flow between User : User and the User InterfaceBB
Create a new variable called id_child with the data_person type.
Create an internal activity called "convert the child id" and put in edit body id_child = request_parents_informations_id_child;
Trace the flow information to request the parents identity between User InterfaceBB and the Person ManagementBB
Generate the state machine

New story Process parents request in the use case Process the requests of the Service Person Management

In the use case Process the requests of the Service Person Management, create a story called "Process parents request"

For that :

Trace the flow information to request the parents identity
Create two variables parent1 and parent2 with person_data type
Trace internal activity called "extract the parents informations" and copy and paste the following code :

try {
	pqxx::result r;
	try {
		pqxx::work txn { *c }; 
		pqxx::result temp { txn.exec("select * from parents('"+information_to_request_the_parents_identity_value.firstname+"','"+information_to_request_the_parents_identity_value.lastname+"')" )};

		// std::cout<<"After 1st sql"<<std::endl;
		txn.commit();
		//std::cout<<"After 1st commit"<<std::endl;
		r = temp;
	}
	catch (pqxx::sql_error const& e) {
		std::cerr << "SQL error: " << e.what() << std::endl;
		std::cerr << "Query was: " << e.query() << std::endl;
	}
	catch (std::exception const& e) {
		std::cerr << "Error: " << e.what() << std::endl;
	}

	if (r.size() > 0) {
		//std::cout<<"size result "<<r.size()<<std::endl;
		
		for (pqxx::result::const_iterator line = r.begin(); line != r.end();
			++line) {

			// this loop will go through every line of your pgSQL request
			if (line==r.begin() ){
			parent1.firstname = line[0].as<std::string>();
			parent1.lastname = line[1].as<std::string>();
			}
			if (line==r.begin()+1){
			parent2.firstname = line[0].as<std::string>();
			parent2.lastname = line[1].as<std::string>();
			}
		}
	}
}
catch (std::exception const& e) {
	std::cerr << "Error: " << e.what() << std::endl;
}

std::cout <<parent2.firstname << std::endl;

Trace the flow parents information to the user interface between Person ManagementBB and User InterfaceBB

New story Receive parents informations in the use case Receive information of the Service User InterfaceBB

In the use case Receive information of the Service User InterfaceBB, create the story called Receive parents informations

Trace the flow parents information to the user interface between Person ManagementBB and User InterfaceBB
Create the variables id_parent1 and id_parent2 with person_data type.
Create an internal activity called convert the data for the interface and edit in the body :

id_parent1 = parents_information_to_the_user_interface_data1;
id_parent2 = parents_information_to_the_user_interface_data2;

Trace the flow the parents information between User InterfaceBB and User : User
Generate the state machine

Create the stories to know how many people are younger or older than X years old

New story Ask the number of persons who are younger and older than X years old in the use case Ask information of the Service User InterfaceBB

In the use case Ask information of the Service User InterfaceBB, create the story called Ask the number of persons who are younger and older than X years old

Create a flow between User : User and User InterfaceBB called request the number of person who are younger or older than
Create a variable age with the IntegerType type
Create an internal activity called convert the age data

age = request_the_number_of_person_who_are_younger_or_older_than_age;

Trace the flow the age which will be used to do the statistic study

New story Check the number of persons younger and older than an age in the use case Process the requests of the Service Person ManagementBB

In the use case Process the requests of the Service Person ManagementBB, create the story called Check the number of persons younger and older than an age

Trace the flow the age used to do the statistic study between User Interface and Person ManagementBB
Create the variables younger and older with IntergerType type.
Trace an internal activity called check the number of person younger than the age and copy and paste the following code in the edit body :

try {
	pqxx::result r;
	try {
		pqxx::work txn { *c }; pqxx::result temp { txn.exec("select count(*) from \"Person\" where age<"+std::to_string(the_age_used_to_do_the_statistic_study_value) )};

		// std::cout<<"After 1st sql"<<std::endl;
		txn.commit();
		//std::cout<<"After 1st commit"<<std::endl;
		r = temp;
	}
	catch (pqxx::sql_error const& e) {
		std::cerr << "SQL error: " << e.what() << std::endl;
		std::cerr << "Query was: " << e.query() << std::endl;
	}
	catch (std::exception const& e) {
		std::cerr << "Error: " << e.what() << std::endl;
	}

	if (r.size() > 0) {
		//std::cout<<"size result "<<r.size()<<std::endl;
		
		for (pqxx::result::const_iterator line = r.begin(); line != r.end();
			++line) {
			// here you can display or save data from your request

			younger = std::stod(line[0].as<std::string>());
		}
	}
}
catch (std::exception const& e) {
	std::cerr << "Error: " << e.what() << std::endl;
}

Trace an internal activity called check the number of person older than the age and copy and paste the following code in the edit body :

try {
	pqxx::result r;
	try {
		pqxx::work txn { *c }; pqxx::result temp { txn.exec("select count(*) from \"Person\" where age>"+std::to_string(the_age_used_to_do_the_statistic_study_value) )};

		// std::cout<<"After 1st sql"<<std::endl;
		txn.commit();
		//std::cout<<"After 1st commit"<<std::endl;
		r = temp;
	}
	catch (pqxx::sql_error const& e) {
		std::cerr << "SQL error: " << e.what() << std::endl;
		std::cerr << "Query was: " << e.query() << std::endl;
	}
	catch (std::exception const& e) {
		std::cerr << "Error: " << e.what() << std::endl;
	}

	if (r.size() > 0) {
		//std::cout<<"size result "<<r.size()<<std::endl;
		
		for (pqxx::result::const_iterator line = r.begin(); line != r.end();
			++line) {

			// here you can display or save data from your request
			older = std::stod(line[0].as<std::string>());

		}
	}
}
catch (std::exception const& e) {
	std::cerr << "Error: " << e.what() << std::endl;
}

Trace the flow the number of person to the user interface between Person Management and User Interface
Generate the state machine

New story Receive the number of younger and older people in the use case Receive information of the Service User InterfaceBB

In the use case Receive information of the Service User InterfaceBB, create the story called Receive the number of younger and older people

Trace the flow the number of person to the user interface between Person ManagementBB and User InterfaceBB
Create the variable number_younger and the variable number_older with IntergerType type.
Create an internal activity convert the number of person and in the edit body write

number_younger = the_number_of_person_to_the_user_interface_younger;
number_older = the_number_of_person_to_the_user_interface_older;

Trace the flow the number information between User Interface and User : User
Generate the State Machine

Create the stories to declare a new born

New story Declare a new born in the use case Declare a birth or a death of the Service User InterfaceBB

In the use case Declare a birth or a death of the Service User InterfaceBB, create the story called Declare a new born

Create an interface called I_declare_an_event between User : User and User InterfaceBB

We use again the variable id_child with person_data type
Create an internal activity called convert the informations and copy and paste in the body

id_child = declare_a_new_born_child;
id_parent1 = declare_a_new_born_parent1;
id_parent2 = declare_a_new_born_parent2;

Create an interface I_transmission_of_the_information_to_save between User InterfaceBB and Person ManagementBB

I_transmission_of_the_information_to_save

Trace the flow information of the birth between User InterfaceBB and Person ManagementBB
Generate State Machine

New story Register the new born in the use case Register the event of the Service Person ManagementBB

In the use case Register the event of the Service Person ManagementBB, create the story called Register the new born

Trace the flow information of a birth between User InterfaceBB and Person ManagementBB
Create an internal flow called register the new born and copy and paste the following code :

try {
	pqxx::result r;
	try {
		pqxx::work txn {*c }; pqxx::result temp { txn.exec("SELECT newborn('"+information_of_a_birth_parent1.firstname+"','"+information_of_a_birth_parent1.lastname+"','"+information_of_a_birth_parent2.firstname+"','"+information_of_a_birth_parent2.lastname+"','"+information_of_a_birth_baby.firstname+"','"+information_of_a_birth_baby.lastname+"')" )};
		// std::cout<<"After 1st sql"<<std::endl;
		txn.commit();
		//std::cout<<"After 1st commit"<<std::endl;
		r = temp;
	}
	catch (pqxx::sql_error const& e) {
		std::cerr << "SQL error: " << e.what() << std::endl;
		std::cerr << "Query was: " << e.query() << std::endl;
	}
	catch (std::exception const& e) {
		std::cerr << "Error: " << e.what() << std::endl;
	}

	if (r.size() > 0) {
		//std::cout<<"size result "<<r.size()<<std::endl;
		
		for (pqxx::result::const_iterator line = r.begin(); line != r.end();
			++line) {

			// this loop will go through every line of your pgSQL request

			if (line==r.begin() ){
			parent1.firstname = line[0].as<std::string>();
			parent1.lastname = line[1].as<std::string>();
			}
			if (line==r.begin()+1){
			parent2.firstname = line[0].as<std::string>();
			parent2.lastname = line[1].as<std::string>();
			}

			// here you can display or save data from your request

		}
	}
}
catch (std::exception const& e) {
	std::cerr << "Error: " << e.what() << std::endl;
}

Generate state machine

Create the stories to declare a death

New story Declare a death in the use case Declare a birth or a death of the Service User InterfaceBB

In the use case Declare a birth or a death of the Service User InterfaceBB, create the story called Declare a death

Trace the flow declare a death between User : User and User InterfaceBB
Trace the flow information of a death between User InterfaceBB and Person ManagementBB

New story Register the death in the use case Register the event of the Service Person ManagementBB

In the use case Register the event of the Service Person ManagementBB, create the story called Register the death

Trace the flow between the User InterfaceBB and the Person ManagementBB
Create an internal flow called register the death and copy and paste the following code :

try {
	pqxx::result r;
	try {
		pqxx::work txn { *c }; pqxx::result temp { txn.exec("SELECT death('"+information_of_a_death_value.firstname+"','"+information_of_a_death_value.lastname+"')" )};

		// std::cout<<"After 1st sql"<<std::endl;
		txn.commit();
		//std::cout<<"After 1st commit"<<std::endl;
		r = temp;
	}
	catch (pqxx::sql_error const& e) {
		std::cerr << "SQL error: " << e.what() << std::endl;
		std::cerr << "Query was: " << e.query() << std::endl;
	}
	catch (std::exception const& e) {
		std::cerr << "Error: " << e.what() << std::endl;
	}

	if (r.size() > 0) {
		//std::cout<<"size result "<<r.size()<<std::endl;

		}
}
catch (std::exception const& e) {
	std::cerr << "Error: " << e.what() << std::endl;
}

Generate state machine

备注

If you have the community version of Designer (free version), please build the executable via Hub4sys as shown on this page.

Create the Virtual Bench Model

Go on the Sim4Sys Virtual Bench: https://sim4sys.com/
In the Projects tab choose your sandbox project
Create a new executable called "TutoSQL"
- Import type and flows thanks to the XML file you generated by the Designer model in export (Mapping by XML
- Configure the executable by adding the exe generated in the DefaultNode
Add a new scenario called as you wish
Create a websocket with the executable TutoSQL
Add a HMI Scenario contexts

Create the HMI Search the Spouse

We will create an interface like you see bellow. The user will enter a first name and last name. It will returns the spouse's first name and last name. If there is no spouse associate to this name or if the person doesn't exist, it will return "-".

Drag and drop the text icon
- In the text field add Search the spouse
Drag and drop the form icon
- Write in the Label field : First name;Last name
- Write in the Inputs field : Lea;Vasseur
- Create a Simu to model mapping request spouse informations
- Edit the request spouse informations mapping
  - For the first name, select Inputs > attr. of underfined type and write 1
  - For the last name select Inputs > attr. of underfined type and write 2
You will obtain :
Drag and drop the text icon
- In the text field add First name
- Add a Receive flow the spouse information
- In the element fields Text, select value.firstname
Drag and drop the text icon
- In the text field add Last name
- Add a Receive flow the spouse information
- In the element fields Text, select value.lastname

Create the HMI Search the parents

We will create an interface like you see bellow. The user will enter the first name and the last name of a child. It will returns the parents' first name and last name. If there is no parents associate to this name or if the person doesn't exist, it will return nothing.

Drag and drop the text icon
- In the text field add Search the parents
Drag and drop the form icon
- Write in the Label field : First name;Last name
- Write in the Inputs field : Marty;Vasseur
- Create a Simu to model mapping request parents informations
- Edit the request parents informations mapping
  - For the first name, select Inputs > attr. of underfined type and write 1
  - For the last name select Inputs > attr. of underfined type and write 2
You will obtain :
Drag and drop the text icon
- In the text field add First name
- Add a Receive flow the parents information
- In the element fields Text, select parent1.firstname
Drag and drop the text icon
- In the text field add Last name
- Add a Receive flow the parents information
- In the element fields Text, select parent1.lastname
Drag and drop the text icon
- In the text field add First name
- Add a Receive flow the parents information
- In the element fields Text, select parent2.firstname
Drag and drop the text icon
- In the text field add Last name
- Add a Receive flow the parents information
- In the element fields Text, select parent2.lastname

Create the HMI Seek the number of person older or younger than

We will create an interface like you see bellow. The user will enter an age. It will return in a pie chart the number of person younger and older.

Drag and drop the text icon
- In the text field add Seek the number of persons older or younger than
Drag and drop the form icon
- Write in the Label field : Age
- Write in the Inputs field : 25
- Create a Simu to model mapping request the number of person who are younger or older than
- Edit the request the number of person who are younger or older than mapping
  - For the Age, select Inputs > attr. of underfined type and write 1
  You will obtain :
Drag and drop the pie chart icon
- Add Receive flow the number information you will edit as follow
- Add Receive flow the number information you will edit as follow

Create the HMI Declare a new born

We will create an interface like you see bellow. The user will enter the first name and the last name of a child and the first and last name of his/her parents.

Drag and drop the text icon
- In the text field add Declare a new born
Drag and drop the form icon
- Write in the Label field : Parent 1 - First name; Parent 1 - Last name; Parent 2 - First name; Parent 2 - Last name; Child - First name; Child - Last name
- Create a Simu to model mapping declare a new born
- Edit the declare a new born mapping
  - For the child's first name, select Inputs > attr. of underfined type and write 5
  - For the child's last name select Inputs > attr. of underfined type and write 6
  - For the parent1's first name, select Inputs > attr. of underfined type and write 1
  - For the parent1's last name select Inputs > attr. of underfined type and write 2
  - For the parent2's first name, select Inputs > attr. of underfined type and write 3
  - For the parent2's last name select Inputs > attr. of underfined type and write 4

Create the HMI Declare a death

We will create an interface like you see bellow. The user will enter the first name of the death person.

Drag and drop the text icon
- In the text field add Declare a death
Drag and drop the form icon
- Write in the Label field : First name;Last name
  - Write in the Inputs field : first name; last name
- Create a Simu to model mapping declare a death
- Edit the declare a new born mapping
  - For the first name, select Inputs > attr. of underfined type and write 1
  - For the last name select Inputs > attr. of underfined type and write 2

Run the simulation

Now the scenario is ready to play: you can connect the executable by clicking on the WebSocket button and click on play in Run mode.

You can fill the forms as you want and submit it to see the result.

备注

The HMI Declare a new born does not create a new Person for the new born. You need to write someone who already exists as parent.

PRESENTATION​

Prerequisites​

News skills that you will acquire​

Duration​

CREATE THE MODEL ON PGSQL​

Import the database on PgSQL​

Create functions on PgSQL​

Function name_from_id​

Function spouse​

Function parents​

Function new born​

Function Death​

Create the model on Designer​

Create a DataType : person_data​

Create the use cases in the Service User interface​

Create the use cases in the Service Person Management​

Setting the DefaultNode to create the pointer for the database​

Create the stories to request the spouse information​

New story ask the spouse information in the use case Ask Information of the Service User interface​

New story Process the spouse request in the use case Process the requests of the Service Person Management​

New story Receive spouse information in the use case Receive Information of the Service *User interface​

Create the stories to request the parents name​

New story Ask for the parents name in the use case Ask information of the Service User Interface​

New story Process parents request in the use case Process the requests of the Service Person Management​

New story Receive parents informations in the use case Receive information of the Service User InterfaceBB​

Create the stories to know how many people are younger or older than X years old​

New story Ask the number of persons who are younger and older than X years old in the use case Ask information of the Service User InterfaceBB​

New story Check the number of persons younger and older than an age in the use case Process the requests of the Service Person ManagementBB​

New story Receive the number of younger and older people in the use case Receive information of the Service User InterfaceBB​

Create the stories to declare a new born​

New story Declare a new born in the use case Declare a birth or a death of the Service User InterfaceBB​

New story Register the new born in the use case Register the event of the Service Person ManagementBB​

Create the stories to declare a death​

New story Declare a death in the use case Declare a birth or a death of the Service User InterfaceBB​

New story Register the death in the use case Register the event of the Service Person ManagementBB​

Create the Virtual Bench Model​

Create the HMI Search the Spouse​

Create the HMI Search the parents​

Create the HMI Seek the number of person older or younger than​

Create the HMI Declare a new born​

Create the HMI Declare a death​

Run the simulation​

PRESENTATION

Prerequisites

News skills that you will acquire

Duration

CREATE THE MODEL ON PGSQL

Import the database on PgSQL

Create functions on PgSQL

Function name_from_id

Function spouse

Function parents

Function new born

Function Death

Create the model on Designer

Create a DataType : person_data

Create the use cases in the Service User interface

Create the use cases in the Service Person Management

Setting the DefaultNode to create the pointer for the database

Create the stories to request the spouse information

New story ask the spouse information in the use case Ask Information of the Service User interface

New story Process the spouse request in the use case Process the requests of the Service Person Management

New story Receive spouse information in the use case Receive Information of the Service *User interface

Create the stories to request the parents name

New story Ask for the parents name in the use case Ask information of the Service User Interface

New story Process parents request in the use case Process the requests of the Service Person Management

New story Receive parents informations in the use case Receive information of the Service User InterfaceBB

Create the stories to know how many people are younger or older than X years old

New story Ask the number of persons who are younger and older than X years old in the use case Ask information of the Service User InterfaceBB

New story Check the number of persons younger and older than an age in the use case Process the requests of the Service Person ManagementBB

New story Receive the number of younger and older people in the use case Receive information of the Service User InterfaceBB

Create the stories to declare a new born

New story Declare a new born in the use case Declare a birth or a death of the Service User InterfaceBB

New story Register the new born in the use case Register the event of the Service Person ManagementBB

Create the stories to declare a death

New story Declare a death in the use case Declare a birth or a death of the Service User InterfaceBB

New story Register the death in the use case Register the event of the Service Person ManagementBB

Create the Virtual Bench Model

Create the HMI Search the Spouse

Create the HMI Search the parents

Create the HMI Seek the number of person older or younger than

Create the HMI Declare a new born

Create the HMI Declare a death

Run the simulation