Exercise 3.1 - SysML to MySQL
Objectives
The new learning objectives of this exercise are to generate a table in a MySQL database from a collection of blocks in MagicDraw and then update that table when values change in the SysML model.
Preparation
This exercise assumes the student has
Cameo System Modeler 18.0 - 19.0 (or MagicDraw with SysML plug-in) installed correctly on his or her machine with a valid license for use, and
Syndeia 3.3 installed as a plugin for Cameo/MagicDraw with a valid license for use
Syndeia MySQL interface, correctly installed with a valid license for user.
A MySQL repository network connection in the Syndeia Repository Manager, where you have write privileges to MySQL
MySQL Workbench, a direct interface to the user's MySQL database
The specific instructions call for a schema named demo_tutorial, but any other schema available to the user would work. See Appendix 1 for more information on creating schemas in MySQL.
Exercise
We will use the collection of blocks in the MySQL_Tutorial Part 1 package in the MagicDraw model supplied, Syndeia Tutorial Testbed.mdzip (get it here - https://intercax.atlassian.net/wiki/spaces/SYN34/pages/2407364009). In Figure 1, we see a component block with 12 specializations. We will use the parent component block to generate the table structure, using the value properties as column headers, and the specialized blocks to generate the table rows, with the default values as the column entries in each row.
Figure 1: Contents of MySQL_Tutorial Part 1 package in Syndeia Tutorial Testbed.mdzip
The same process works if the 12 blocks are instances rather than specializations of the component block. Whether the modeler uses instances or specializations to capture actual parameter values is a methodology question; MagicDraw and Syndeia support both.
For users unfamiliar with MySQL databases, Appendix 1 describes how to access the databases directly using the MySQL Workbench, where the user can create, modify, and view schemas, tables and table rows directly.Launch the Syndeia dashboard from the MySQL_Tutorial Part 1 package in the SysML model. Select a Syndeia Cloud project, if necessary.
The first step is to create the table in MySQL. Go to the Connection Manager tab. Set the Connection Type to Model Transform, as shown in Figure 2. Drag the parent block, component, from SysML to demo_tutorial in MySQL.
Figure 2: Contents of MySQL_Tutorial Part 1 package in Syndeia Tutorial Testbed.mdzip
Confirm table generation, as shown in Figure 3.
Figure 3: Confirming table generation
When complete, the Connection Manager should show a new table, component, under demo_tutorial, as shown in Figure 4.
Figure 4: Connection Manager after generation of new table, component
The Connection Search tab, as shown in Figure 5, shows the connection created between the SysML block and the MySQL table after Get All is clicked.
Figure 5: Connection Summary tab shows link between SysML block and MySQL table
The next step is to create a row in the table in MySQL. Set the Connection Type to Data Map. Drag the child block, Atmospheric Sensor, from SysML to component in MySQL and a new table row is created, as shown in Figure 6.
Figure 6: Generating a table row from the Atmospheric Sensor block
The table viewed in the MySQL database through the MySQL Workbench appear as in Figure 7.
Figure 7: Generating a table row from the Atmospheric Sensor block
Before completing the table, we will demonstrate how to compare and synchronize the parameter values between the SysML model and the MySQL database. In MagicDraw, modify the default value of the cost value property of the Atmospheric Sensor block, from 50 to 100. In the Connection Browser tab, right click on the connection from the Atmospheric Sensor block and select Compare Source & Target, as shown in Figure 8.
Figure 8: Generating a comparison across the Atmospheric Sensor data map connection
The Comparison Result is shown in Figure 9. The changed value of Atmospheric Sensor.Mass can also be observed directly using the MySQL Workbench. To update the parameter value in MySQL, right-click on the same connection and select Sync Source -> Target and confirm.
Figure 9: Comparison Result before updating SysML value to MySQL table
The remaining SysML specialized blocks can be dragged over in one step by multiple-selecting the blocks on the left side of the Connection Manager tab and dragging them onto the component table on the right. When complete, the Connection Manager will appear as shown in Figure 10. The changed value of Atmospheric Sensor.Mass can also be observed in the right column.
Figure 10: Connection Manager after dragging all components to MySQL table
Appendix 1: Creating a Table in MySQL
The purpose of this appendix is to familiarize the reader with the MySQL Workbench, enabling him or her to create, display, edit and delete MySQL database tables independent of Syndeia. This may be useful in carrying out the Exercise 3.1 and 3.2 tutorials.
A1.1 Connecting to a MySQL Database through MySQL Workbench
In order to connect to a MySQL repository directly, install the MySQL Workbench client on your local computer and contact the MySQL server administrator for a user name, password and the server hostname.
Launch MySQL Workbench from your local computer and select Database → Connect to Database (see Figure A 1)
Figure A 1: Connect to Database from MySQL Workbench
Entering the information provided by the administrator into the window in Figure A 2, create a quick connection path on the workbench desktop. The Stored Connection field should contain a nickname assigned by the user for easy identification.
Figure A 2: Provide your credentials to setup a connection to your MySQL server
There should now be an icon on the MySQL Workbench desktop label with the Stored Connection name. Click it to open the SQL Editor window, as shown in Figure A 3.
Figure A 3: SQL Editor, existing schemas circled in red, Create New Schema icon circled in green
A1.2 Creating a MySQL Database Schema
It is possible to create a table in one of the existing schemas shown in the lower left corner of Figure A 3, skipping this section. Alternatively, a new schema can be created by clicking the Create New Schema icon, shown in green in the same figure. This opens the Schema tab (Figure A 4).
Figure A 4: Creating a new schema in MySQL, Step 1
Assign the new schema a name and click Apply. A new window will appear (Figure A 5) that shows the SQL commands that create the new schema. Review and click Apply again. A final window (Figure A 6) prepares to execute the commands. Click Finish to complete.
Figure A 5: Creating a new schema in MySQL, Step 2
Figure A 6: Creating a new schema in MySQL, Step 3A1.3 Creating a MySQL Database Table
To create a new table, begin by selecting which of the available schemas to create the table in. Right-click on the desired schema in the lower left corner of Figure A 7 and select Set as Default Schema. The selections should now appear in bold font.
Figure A 7: Table Editor, available schemas circled in red, Create New Table icon circled in yellow
Click on the Create New Table icon (see yellow highlight in Figure A 7).
Enter the new table's name in the first field. Check that the Schema to the right is correct.
Expand the Window vertically, if necessary, to see several rows of the table headed Column Name…Datatype and enter column header information for the new table, as shown in Figure A 8. Generally, the first column entered will be set as primary key, but this can be changed by checking the PK and NN (Not Null) columns. When finished, click Apply. Respond to windows similar to Figure A 5 and Figure A 6 with Apply and Finish, respectively.
Figure A 8: Table Editor, with column info entered (red highlight)
To display and edit the contents of the table, enter the command shown is Figure A 9 in the Query 1 tab (shown for table Staff in schema test_demo) and click on the Execute icon.
Figure A 9: Query 1 tab, display contents of table, red highlight around Execute icon
As shown in Figure A 10, enter the data into the table in the lower pane. In this example, we show a table with two columns (employee and desk) and four rows. When all data has been entered, click Apply, Apply and Finish as before.
Figure A 10: Table entry in lower pane