Connecting a MySQL Database

In this tutorial you are connecting to an external data repository using an access. An access is a bridge between a model and the data stored in a specific repository. The repository you want to connect is a MySQL database where you will have a new schema with a table that contains the properties of a Person entity. You will use that MySQL table to import the Person data into tribefire and then make sure the manipulations done in tribefire are reflected in MySQL.

The Person entity you create in this tutorial is not the same as the Person entity in the Demo Cartridge.

The following procedure also covers creating a tribefire model from database - see Creating a MySQL Access for more information.

Steps

To connect a MySQL database, you must perform the following steps:

1. Creating a MySQL Connection
2. Creating a MySQL Access
3. Testing a MySQL Access

Prerequisites

MySQL database prepared as per the instructions in Preparing a MySQL Database.

Preparing a MySQL Database

To prepare a MySQL database for a connection to tribefire:

1. Download and install MySQL (version 5.7 or higher).

   For MySQL installation and configuration instructions, see MySQL documentation .

2. Using MySQL Workbench, create a new schema called tribefiremysql.

3. In the tribefiremysql schema, create a new table called person and configure it as follows:

   Column Name	Datatype	PK	NN	UQ	B	UN	ZF	AI	G
   idperson	INT	Yes	Yes	No	No		No	No	Yes	No
   firstName	VARCHAR(45)		No
   lastName	VARCHAR(45)
   dateOfBirth	DATE
   placeOfBirth	VARCHAR(45)
   nationality	VARCHAR(45)

   Make sure that the ID property has three properties checked: PK (Primary Key), NN (Non-Nullable, means that data must be entered in this column when creating a new entry), and AI (Auto-Increment, means a number is automatically added when creating a new entry). You can also use the following SQL snippet:

 CREATE TABLE `tribefiremysql`.`person` (
`idperson` INT NOT NULL AUTO_INCREMENT,
`firstName` VARCHAR(45) NULL,
`lastName` VARCHAR(45) NULL,
`dateOfBirth` DATE NULL,
`placeOfBirth` VARCHAR(45) NULL,
`nationality` VARCHAR(45) BINARY NULL,
PRIMARY KEY (`idperson`));

4. Using the Users and Privileges link of MySQL Workbench, add a new user account to MySQL and configure it as follows:

   Tab of the Users and Privileges View	Property	Value
   Login	Login Name	cortexMySQL
   Login	Password	cortex
   Administrative Roles	DBManager DBDesigner BackupAdmin	Yes Yes Yes

5. Continue with Creating a MySQL Connection

Creating a MySQL Connection

To create a MySQL connection:

1. Download a JDBC connector for MySQL and place the connector .jar file in the <TRIBEFIRE_INSTALLATION_DIRECTORY>/tribefire/host/lib directory.

   For MySQL connector download page, see: https://dev.mysql.com/downloads/connector/.

2. Start the tribefire Server and open Control Center. If your tribefire Server was running when you copied the connector file, you must restart it.

3. In Control Center, on the Workbench panel, click the Connections entry point, and click New .

4. Select the HikariCpConnectionPool entry and configure it as follows:

   Name	Value	Description
   externalId	mySQLConnection.local	External ID of the connection
   name	mySQLConnection	Internal name of the connection

5. In the same modal window, next to the connectionDescriptor label, click Assign. New view is displayed.

6. In the DatabaseConnectionDescriptor view, select the GenericDatabaseConnectionDescriptor, and configure it as follows:

   Name	Value	Description
   driver	com.mysql.jdbc.Driver	Name of the driver
   password	cortex	Password for the database scheme you are connecting to. This is the same password you provided when you created the database.
   url	jdbc:mysql://localhost:3306/tribefiremysql	URL to the database schema.
   user	cortexMySQL	User you use to connect to the database. This is the same user you created during the creation of the database.

7. In the DatabaseConnectionDescriptor view, click Apply. You can see that your database connection descriptor is added to the connection.

8. In the connection view, click Apply. Your new mySQLConnection is displayed in a new tab. Click the Commit button.

9. Right-click your new connection and click Deploy. Your connection is deployed.

10. Right-click your deployed connection and click Test Connection. If you did everything correctly, the Connection successfully tested message is displayed at the top of the screen.

11. Right-click your deployed connection and click Synchronize DB Schema. This loads the rough types into tribefire.

Continue with Creating a MySQL Access.

Creating a MySQL Access

To create a MySQL Access:

1. In Control Center, on the Workbench panel, click Custom Accesses, and then click New.

2. Select HibernateAccess and configure it as follows:

   Name	Value	Description
   externalId	mySQLAccess.local	External ID of the access
   name	mySQLAccess	Internal name of the access

3. In the same modal window, next to the connector label, click Assign. New view is displayed.

4. In the DatabaseConnectionPool view, select the mySQLConnection and click Finish. You can see that your database connection descriptor is added to the connection.

5. In the connection view, click Apply. Your access is opened in a new tab. Click Commit.

6. Right-click your new access and select More -> Create Model from DB. The Create Model From DB Schema modal window is displayed.

7. In the modal window, configure the new model as follows and click Execute:

   Name	Value	Description
   Name	PersonModelMySQL	Name of the model
   Group Id	custom.model	Group ID of the model

   The database schema is retrieved and a model is created.

8. Right-click your access and click Deploy. Your new access is deployed and ready to be queried.

9. Continue with Testing a MySQL Access

Testing a MySQL Access

To test the MySQL access:

1. In Control Center, on the Workbench panel, click Custom Accesses. The Custom Accesses tab is displayed.
2. Right-click your mySQLAccess and select Switch To. Explorer opens.
3. In Explorer, locate the Quick Access... search box at the top of the page. In the search box, type Person and select the Person type from the drop-down list. A new Person tab is displayed.
4. Notice there is no data in the new Person tab. That is because there are no records of the type Person in your MySQL database. Time to change that.
5. On the Person tab, click New. A new modal window is displayed. In the modal window, provide the necessary information.

   Note that the input fields in the modal window correspond to the columns of your MySQL database schema.

6. In the modal window, click Apply. The modal window disappears and Explorer is displayed again. In Explorer, click Commit.
7. In MySQL Workbench, inspect the person table. The instance of the entity type Person which you just created is listed as the first record.
8. Still in MySQL Workbench, add a new row to the person schema, effectively creating a new instance of Person. Make sure to apply the changes.
9. In tribefire Explorer, on the Action Bar, click Refresh. The Person instance you added directly to the database schema is visible and available in tribefire.