Connect database to your model

If you have a large amount of data to handle, you should consider using a database and not write on your own every single data in your model

Download and install PostgreSQL

note

PostgreSQL requires Python, available only for the version pro of Designer.

• Download PostgreSQL 13.2

• Install PostgreSQL by double-clicking on postgresql-13.2-1-windows-x64.exe

• Follow these steps :

• PostgreSQL and pgAdmin 4 are now installed

Configure binary path

To configure the binary path for PostgreSQL:

• Open pgAdmin and click on Servers in the left panel.
• Click Configure pgAdmin.

• Inside Paths, select Binary paths.
• Scroll down to PostgreSQL Binary Path.
• Then, depending on your version add the PostgreSQL bin folder.
• Check the field Set as default.

Cygwin

Please see the Cygwin page if you need help with cygwin packages, path variables, compilation with cygwin in Eclipse environment.

Packages

Make sure you have the following Cygwin packages.

• Devel/mingw64-x86_64-postgresql
• Database/postgresql
• Database/postgresql-client
• Database/postgresql-devel
• Database/postgresql-plpython

caution

When installing new package, Cygwin will update all other packages if available. To avoid breaking your build please change the column New to "Keep" for each following package in order to keep the current version. Also check that you have python 3.7.9 version or above:

• Select View → Pending:
  • gcc-core (10.2)
  • gcc-g++ (10.2)
  • Devel/make (4.3)
  • Libs/libboost-devel (1.66)
  • Interpreters/python37 (3.7.9 or above)
  • Interpreters/python37-devel (3.7.9 or above)
  • mingw64-x86_64-gcc-core (10.2)
  • mingw64-x86_64-gcc-g++ (10.2)
  • mingw64-x86_64-boost (1.66)

Path variables

In Environment variables add PostgreSQL.

• In the taskbar window, search "modify environment variables" and click on "Environment Variables".

• You will have the following window. Double click on "Path".

• Add the path C:\Program Files\PostgreSQL\13\bin. You will have :

Compilation properties in Eclipse

DefaultNode properties

In the "Cygwin C++ Compiler" you must have the following path

• "{workspace_loc:/CIL4SysLibs/libs/pgsql/include}"

• the path to Python37 and the file to Python.h

• In "Cygwin C++ Linker" / "Libraries" you must have the following libraries

  • pthread
  • pqxx_mingw_cyg
  • pq_mingw_cyg

• In "Library search path" add ${workspace_loc:/CIL4SysLibs/libs/pgsql/lib}

Example

Here, we will take the example of the Papyrus Project called TutorielSQL in the project Organization to show you how you need to parameter your DefaultNode.

• Open the DefaultNode properties.

• Add the path in the Cygwin C++ Compiler.

note

Make sure that the paths are in the same order than on the following picture.

• Add the libraries in the Cygwin C++ Linker.

note

Make sure that the libraries are in the same order than on the following picture.

• Add the command -static in the Miscellaneous Linker flag.

• Add the command x86_64-w64-mingw32-g++ in the Cygwin C++ Compiler and the Cygwin C++ Linker.

Include the Libraries

Now that you finished the DefaultNode compilation properties you will include the library in the Papyrus project.

• Open the project on the overview
• Double click on the project with the logo cil4sys.
• In properties select C/C++
• In the Header, add #include <pqxx/pqxx>

Create Database

• Create your database in PgAdmin.

• Open your database menu, then open "Schemas", "public", "Tables". Create your first Table.

• Specify columns.

• Specify constraints (a table must have a primary key and may have foreign keys to be linked to other tables).

Export database

• Create a database "Tutorial" in PgAdmin.

• Open the window "Backup" and choose the file you want to export.

Import database

• On pgAdmin, go on the database where you want to restore a backup
• Go to public then restore

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

Connect the database to Designer

You have two methods to connect your database.

• The first method is a connection made in the C++ activity. It will make a new connection to PgSQL at each request. It can be useful if you just need to import data at the beginning of the simulation.
• In the second method, Designer will be connected to PgAdmin only one time. Indeed, a pointer makes the connection to the database and is used in the C++ activities. It requires more manipulations on Designer. However, it is the method you need if you want to communicate with the tables during the simulation.

Method 1 : Connection to the database in the C++ activity

For this connection you just need to change the host, port and database name (dbname) in the pqxx::connection code bellow. You will use this type of code in an internal activity where you want to import data.

try {
pqxx::result r;
try {
pqxx::connection c {"host=localhost port=5432 dbname=Your_database_name connect_timeout=10 
user=postgres password=your_password" }; // change this line for your connection informations
pqxx::work txn { c }; pqxx::result temp { txn.exec("select * from \"Points\"")}; // change this line for the request
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

double longitude= std::stod(line[0].as<std::string>());
double latitude= std::stod(line[1].as<std::string>());
double angle=std::stod(line[2].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;
}

Method 2 : Connection to the database with a pointer

Go to the Model Explorer > ModelPackage, ListHint, Include» Project_name

Create an external type pqxx::connection

• Right click on «ExternLibrary» ExternalTypes > New Child > Class

• Rename the Class pqxx::connection

• Then go to profile and add an applied stereotype by clicking on the +. Then, select the applied stereotypes External and click on ok.

Add the property

• Go to the service package where you want to add a database.

• Right click on «ServiceClass» Service_NameBB > New Child > Property

• Double click on the new property. Rename it "c"

• Add a type by clicking on the tree dots. Then select «External» pqxx::connection and click on ok.

• Go to Profile and add an applied stereotype by clicking on +. Then add Variable and Ptr before clicking on ok.

Add the Opaque Behaviour

• Right click on «ServiceClass» Service_NameBB > OpaqueBehavior > As ownedBehavior

• Rename it into Service_NameBB

• Add the Laguage C++ by clicking on the +
• Add the code

try{
c = new pqxx::connection("host=localhost port=5432 dbname=Database_Name connect_timeout=10 user=postgres password=yourdatabase_password");
} catch (std::exception const &e) {
std::cerr << "Error: " << e.what() << std::endl;
}

Add the Operation

• Right click on «ServiceClass» Service_NameBB > New Child > Operation

• Rename it into Service_NameBB

• Add the method Service_NameBB (the Opaque Behavior we created)

• Go on Profile and add an Applied stereotypes by clicking on +. Then, add the applied stereotypes Create.

Use the pointer in the C++ code

As you will see bellow, we added a pointer *c for the connection to the database. This will be used in internal activities to communicate with the database.

try {
pqxx::result r;
try {
pqxx::work txn { *c }; pqxx::result temp { txn.exec("select * from \"Points\"")}; // change this line for the request
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

double longitude= std::stod(line[0].as<std::string>());
double latitude= std::stod(line[1].as<std::string>());
double angle=std::stod(line[2].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;
}

C++ code in model activity

Read table

• Use try and catch to print connection errors or pgSQL request errors.
• In this example we will use the pgSQL request SELECT * from "Points". This request select every columns and lines from table Points.
• Use \ to avoid escape characters in string. For example instead of writing SELECT * from "Points" write SELECT * from \"Points\"

try {
pqxx::result r;
try {
pqxx::connection c {"host=localhost port=5432 dbname=Your_database_name connect_timeout=10 
user=postgres password=your_password" };
pqxx::work txn { c }; pqxx::result temp { txn.exec("select * from \"Points\"")};
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

double longitude= std::stod(line[0].as<std::string>());
double latitude= std::stod(line[1].as<std::string>());
double angle=std::stod(line[2].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;
}

Insert into table

If you want to insert data in your table, use the same process as above but change your pgSQL request to:

 INSERT into "your_table" (column_1,column_2,column_3) 
 VALUES (your_data_1,your_data_2,your_data_3)

Update table

If you want to update a certain line of your table, use the following pgSQL request:

UPDATE "your_table"
SET column_you_want_to_update=new_value
WHERE column_where_you_want_to_specify_condition=condition_value