ExperimentStorage Database

[ExperimentStorageDatabase.htm - uqlpayne 22 Aug 2013]

Contents

• Requirements
• PostgreSQL pgAdmin III
• Create Group Role
• Create Login Role
• Create Database
• Update Privileges
• What's Next

Requirements

• PostgreSQL 9.2 is installed and running.

• The UQ-iLab-ServiceBroker-Java GitHub repository has been downloaded as a ZIP file from:

  https://github.com/uqlpayne/UQ-iLab-ServiceBroker-Java

PostgreSQL pgAdmin III

• Run the PostgreSQL pgAdmin III program.

  In the Object browser panel, right-click PostgeSQL 9.2 (localhost:5432) and select Connect from the popup menu to display the Connect to Server window.

  

  Enter the password for the user postgres and click the OK button.

  

Create Group Role

If you have already completed the IlabServiceBroker Database tutorial then you will have already created an IlabServices Group Role for the database and can skip this section.

• In the Object browser panel, right-click Group Roles and select New Group Role... from the popup menu to display the New Group Role... window.

  

  For Role name, enter IlabServicesGroup.
  

  

  Click the OK button to close the New Group Role... window.

Create Login Role

If you have already completed the IlabServiceBroker Database tutorial then you will have already created an IlabServices Login Role for the database and can skip this section.

• In the Object browser panel, right-click Login Roles and select New Login Role... from the popup menu to display the New Login Role... window.

  

  Select the Properties tab. For Role name, enter IlabServices.

  

  Select the Definition tab. For Password and Password (again), enter ilab.

  

  Select the Role membership tab. In the left panel under Not Member, select IlabServicesGroup and click the >> button.

  

  Click the OK button to save the changes and close the New Login Role... window.

Create Database

• In the Object browser panel, right-click Databases and select New Database... from the popup menu to display the New Database... window.

  

  Select the Properties tab. For Name, enter ExperimentStorage.

  

  Click the OK button to save the changes and close the New Database... window.

• In the Object browser panel, under Databases, click ExperimentStorage and then click the Execute arbitrary SQL queries toolbar button to open a Query window.

  

  Check that the ExperimentStorage database is selected in the dropdown list. If not, tables and functions will be created or overwritten in the selected database.

  

  For each of the following SQL scripts in the UQ-iLab-ServiceBroker-Java/Database/PostgreSQL folder, in the order shown, execute the query by selecting Query->Execute from the menu.

  1. ExperimentStorage_Tables.sql
  2. ProcessAgent_Tables.sql
  3. ExperimentStorage_Procedures.sql
  4. ProcessAgent_Procedures.sql
  5. ExperimentStorage_Defaults.sql

  All queries should complete successfully producing various results in the Output pane. Close the Query window.

Update Privileges

• In the Object browser panel, expand the ExperimentStorage node and then expand the Schemas node. Finally, expand the public node so that Functions, Sequences and Tables are visible.

  

  Right-click Functions and select Grant Wizard... from the popup menu to display the Privileges for functions window.

  Select the Selection tab and click the Check all button.

  

  Select the Privileges tab. In the Privileges field, select group IlabServicesGroup from the dropdown list and check EXECUTE.

  Click the Add/Change button.

  

  Click the OK button to save the changes.

  

  Click the Done button to close the Privileges for functions window.

• In the Object browser panel, right-click Sequences and select Grant Wizard... from the popup menu to display the Privileges for sequences window.

  Select the Selection tab and click the Check all button.

  

  Select the Privileges tab. In the Privileges field, select group IlabServicesGroup from the dropdown list and check ALL.

  Click the Add/Change button.

  

  Click the OK button to save the changes.

  

  Click the Done button to close the Privileges for sequences window.

• In the Object browser panel, right-click Tables and select Grant Wizard... from the popup menu to display the Privileges for tables window.

  Select the Selection tab and click the Check all button.

  

  Select the Privileges tab. In the Privileges field, select group IlabServicesGroup from the dropdown list and check ALL.

  Click the Add/Change button.

  

  Click the OK button to save the changes.

  

  Click the Done button to close the Privileges for tables window.

What's Next

• The IlabServiceBroker Deployment tutorial (see IlabServiceBrokerDeployment.pdf).