Create the Examplegear Database Schema

1. Download the attached   file to your local machine.

2. Open a Terminal window and do the following:

   1. Navigate to the directory to which you downloaded the the attached file.

   2. Go into the mysql command line program as the root user by first command shown to the right.

   3. At the mysql prompt, execute the second command shown to the right to create the database and populate it with data.

Command to go into the mysql command line program:

mysql -u root mysql

Command to create the database schema:

source CreateExamplegearDatabase_v2.sql

Verify the database creation was successful

1. Execute the first set of commands shown to the right at the mysql prompt to ensure the schema and each of the tables were created successfully.

2. The output should look like the screenshot shown to the right.

3. At this point you should have the following:

   1. A MySQL database named examplegeardb running on port 3306 with a URL of localhost.

   2. A user account dbuser with a password of mypassword.

   3. These tables:

      • Addresses

      • Customers

      • Departments

      • Employees

      • LineItems

      • Products

      • Ref_AddressTypeCode

      • Ref_CountryCode

      • Ref_EmployeeStatusCode

      • Ref_OrderStatusCode

      • Sales

4. You can also verify the row counts of each table by pasting in the second set of commands shown to the right at the mysql prompt.

Commands to verify the database creation:

use examplegeardb;
show tables;

Output of the database creation verification commands:

Commands to verify row counts:

SELECT 'Addresses' AS TableName, COUNT(*) AS RowCount FROM Addresses
UNION
SELECT 'Customers' AS TableName, COUNT(*) AS RowCount FROM Customers
UNION
SELECT 'Departments' AS TableName, COUNT(*) AS RowCount FROM Departments
UNION
SELECT 'Employees' AS TableName, COUNT(*) AS RowCount FROM Employees
UNION
SELECT 'LineItems' AS TableName, COUNT(*) AS RowCount FROM LineItems
UNION
SELECT 'Products' AS TableName, COUNT(*) AS RowCount FROM Products
UNION
SELECT 'Ref_AddressTypeCode' AS TableName, COUNT(*) AS RowCount FROM Ref_AddressTypeCode
UNION
SELECT 'Ref_CountryCode' AS TableName, COUNT(*) AS RowCount FROM Ref_CountryCode
UNION
SELECT 'Ref_EmployeeStatusCode' AS TableName, COUNT(*) AS RowCount FROM Ref_EmployeeStatusCode
UNION
SELECT 'Ref_OrderStatusCode' AS TableName, COUNT(*) AS RowCount FROM Ref_OrderStatusCode
UNION
SELECT 'Sales' AS TableName, COUNT(*) AS RowCount FROM Sales
;

Output of the row count verification commands:

Create the Data Source Profile

To support running SQL queries against the examplegeardb database using the SQL for Confluence (Express Edition) add-on or SQL for Confluence (Pro Edition) add-on, you must build a data source profile.

1. While logged on to Confluence as a Confluence Administrator, type Manage add-ons in the Search box to go to the Manage Add-ons administration screen.

2. Scroll through the list of user-installed add-ons until you see the add-on named Bob Swift Atlassian Add-ons - SQL (Pro Edition) or Bob Swift Atlassian Add-ons - SQL (Express Edition).

3. Click the ">" icon to expand that entry.

4. Click Configure.

5. In the Data Source Profiles section, add a new entry with the information shown to the right.

   1. The Name parameter (examplegeardb) will be the name you reference when using the SQL Query macro.

   2. The Value parameter provides the information necessary to connect to the MySQL database you just created, using the user account that was created during the database creation process.

   3. Your screen should look like the screenshot shown on the right.

6. Click Save to save your changes.

Parameters for the new examplegeardb data source profile:

Configure SQL for Confluence screen: