Use Eclipse to Map Between CIT Attributes and Database Tables

This task describes how to install and use the JPA plug-in, provided with the J2EE edition of Eclipse, to:

• Enable graphical mapping between CMDB class attributes and database table columns.

• Enable manual editing of the mapping file (orm.xml), while providing correctness. The correctness check includes a syntax check as well as verification that the class attributes and mapped database table columns are stated correctly.

• Enable deployment of the mapping file to the CMDB server and to view the errors, as a further correctness check.

• Define a sample query on the CMDB server and run it directly from Eclipse, to test the mapping file.

Version 1.1 of the plug-in is compatible with UCMDB version 9.01 or later and Eclipse IDE for Java EE Developers, version 1.2.2.20100217-2310 or later.

1. Prerequisites

   Install the latest supported Java Runtime Environment (JRE) on the machine where you will run Eclipse from the following site: http://www.oracle.com/technetwork/java/javase/downloads/index.html.

2. Installation

   1. Download and extract Eclipse IDE for Java EE Developers from http://www.eclipse.org/downloads to a local folder, for example, C:\Program Files\eclipse.

   2. Copy com.hp.plugin.import_cmdb_model_1.0.jar from C:\UCMDB\UCMDBServer\tools\db-adapter-eclipse-plugin\bin to C:\Program Files\Eclipse\plugins.

   3. Launch C:\Program Files\Eclipse\eclipse.exe. If a message is displayed that the Java virtual machine is not found, launch eclipse.exe with the following command line:

      "C:\Program Files\eclipse\eclipse.exe" -vm "<JRE installation folder>\bin"
      

3. Prepare the Work Environment

   In this step, you set up the workspace, database, connections, and driver properties.

   1. Extract the file workspaces_gdb.zip from C:\UCMDB\UCMDBServer\tools\db-adapter-eclipse-plugin\workspace into C:\Documents and Settings\All Users.

      Note You must use the exact folder path. If you unzip the file to the wrong path or leave the file unzipped, the procedure will not work.

   2. In Eclipse, choose File > Switch Workspace > Other:

      If you are working with:

      • SQL Server, select the following folder: C:\Documents and Settings\All Users\workspace_gdb_sqlserver.

      • MySQL, select the following folder: C:\Documents and Settings\All Users\workspace_gdb_mysql.

      • Oracle, select the following folder: C:\Documents and Settings\All Users\workspace_gdb_oracle.

   3. Click OK.

   4. In Eclipse, display the Project Explorer view and select <Active project> > JPA Content > persistence.xml > <active project name> > orm.xml.

   5. In the Data Source Explorer view (the bottom left pane), right-click the database connection and select the Properties menu.

   6. In the Properties for <Connection name> dialog box, select Common and select the Connect every time the workbench is started check box. Select Driver Properties and fill in the connection properties. Click Test Connection and verify that the connection is working. Click OK.

   7. In the Data Source Explorer view, right-click the database connection and click Connect. A tree containing the database schemas and tables is displayed under the database connection icon.

4. Create an Adapter

   Create an adapter using the guidelines in Step 1: Create an Adapter.

5. Configure the CMDB Plug-in

   1. In Eclipse, click UCMDB > Settings to open the CMDB Settings dialog box.

   2. If not already selected, select the newly created JPA project as the Active project.

   3. Enter the CMDB host name, for example, localhost or labm1.itdep1. There is no need to include the port number or http:// prefix in the address.

   4. Fill in the user name and password for accessing the CMDB API, usually admin/admin.

   5. Make sure that the C:\UCMDB folder on the CMDB server is mapped as a network drive.

   6. Select the base folder of the relevant adapter under C:\UCMDB. The base folder is the one that contains the dbAdapter.jar file and the META-INF subfolder. Its path should be C:\UCMDB\UCMDBServer\runtime\fcmdb\CodeBase\<adapter name>. Verify that there is no backslash (\) at the end.

6. Import the UCMDB Class Model

   In this step, you select the CITs to be mapped as JPA entities.

   1. Click UCMDB > Import CMDB Class Model to open the CI Types Selection dialog box.

   2. Select the CI types that you intend to map as JPA entities. Click OK. The CI types are imported as Java classes. Verify that they appear under the src folder of the active project.

7. Build the ORM File – Map UCMDB Classes to Database Tables

   In this step, you map the Java classes (that you imported in the previous step) to the database tables.

   1. Make sure the DB connection is connected. Right-click the active project (called myProject by default) in Project Explorer. Select the JPA view, select the Override default schema from connection check box, and select the relevant database schema. Click OK.

   2. Map a CIT: In the JPA Structure view, right-click the Entity Mappings branch and select Add Class. The Add Persistent Class dialog box opens. Do not change the Map as field (Entity).

   3. Click Browse and select the UCMDB class to be mapped (all UCMDB classes belong to the generic_db_adapter package).

   4. Click OK in both dialog boxes. The selected class is displayed under the Entity Mappings branch in the JPA Structure view.

      Note If the entity appears without an attribute tree, right-click the active project in the Project Explorer view. Choose Close and then Open.

   5. In the JPA Details view, select the primary database table to which the UCMDB class should be mapped. Leave all other fields unchanged.

8. Map IDs

   According to JPA standards, each persistent class must have at least one ID attribute. For UCMDB classes, you can map up to three attributes as IDs. Potential ID attributes are called id1, id2, and id3.

   To map an ID attribute:

   1. Expand the corresponding class under the Entity Mappings branch in the JPA Structure view, right-click the relevant attribute (for example, id1), and select Add Attribute to XML and Map....

   2. The Add Persistent Attribute dialog box opens. Select Id in the Map as field and click OK.

   3. In the JPA Details view, select the database table column to which the ID field should be mapped.

9. Map Attributes

   In this step, you map attributes to the database columns.

   1. Expand the corresponding class under the Entity Mappings branch in the JPA Structure view, right-click the relevant attribute (for example, host_hostname), and select Add Attribute to XML and Map....

   2. The Add Persistent Attribute dialog box opens. Select Basic in the Map as field and click OK.

   3. In the JPA Details view, select the database table column to which the attribute field should be mapped.

10. Map a Valid Link

    Perform the steps described above in the step Build the ORM File – Map UCMDB Classes to Database Tables for mapping a UCMDB class denoting a valid link. The name of each such class takes the following structure: <end1 entity name>_<link name>_<end 2 entity name>. For example, a Contains link between a host and a location is denoted by a Java class whose name is generic_db_adapter.host_contains_location. For details, see The reconciliation_rules.txt File (for backwards compatibility).

    1. Map the ID attributes of the link class as described in Map IDs. For each ID attribute, expand the Details check box group in the JPA Details view and clear the Insertable and Updateable check boxes.

    2. Map the end1 and end2 attributes of the link class as follows: For each of the end1 and end2 attributes of the link class:

    • Expand the corresponding class under the Entity Mappings branch in the JPA Structure view, right-click the relevant attribute (for example, end1), and select Add Attribute to XML and Map....

    • In the Add Persistent Attribute dialog box, select Many to One or One to One in the Map as field.

    • Select Many to One if the specified end1 or end2 CI can have multiple links of this type. Otherwise, select One to One. For example, for a host_contains_ip link the host end should be mapped as Many to One, since one host can have multiple IPs, and the ip end should be mapped as One to One, since one IP can have only a single host.

    • In the JPA Details view, select Target entity, for example, generic_db_adapter.host.

    • In the Join Columns section of the JPA Details view, check Override Default. Click Edit. In the Edit Join Column dialog box, select the foreign key column of the link database table that points to an entry in the end1/end2 target entity's table. If the referenced column name in the end1/end2 target entity's table is mapped to its ID attribute, leave the Referenced Column Name unchanged. Otherwise, select the name of the column to which the foreign key column points. Clear the Insertable and Updatable check boxes and click OK.

    • If the end1/end2 target entity has more than one ID, click the Add button to add additional join columns and map them in the same way as described in the previous step.

11. Build the ORM File – Use Secondary Tables

    JPA enables a Java class to be mapped to more than one database table. For example, Host can be mapped to the Device table to enable persistence of most of its attributes and to the NetworkNames table to enable persistence of host_hostName. In this case, Device is the primary table and NetworkNames is the secondary table. Any number of secondary tables can be defined. The only condition is that there must be a one-to-one relationship between the entries of the primary and secondary tables.

12. Define a Secondary Table

    Select the appropriate class in the JPA Structure view. In the JPA Details view, access the Secondary Tables section and click Add. In the Add Secondary Table dialog box, select the appropriate secondary table. Leave the other fields unchanged.

    If the primary and the secondary table do not have the same primary keys, configure the join columns in the Primary Key Join Columns section of the JPA Details view.

13. Map an Attribute to a Secondary Table

    You map a class attribute to a field of a secondary table as follows:

    1. Map the attribute as described above in Map Attributes.

    2. In the Column section of the JPA Details view, select the secondary table name in the Table field, to replace the default value.

14. Use an Existing ORM File as a Base

    To use an existing orm.xml file as a basis for the one you are developing, perform the following steps:

    1. Verify that all CITs mapped in the existing orm.xml file are imported into the active Eclipse project.

    2. Select and copy all or part of the entity mappings from the existing file.

    3. Select the Source tab of the orm.xml file in the Eclipse JPA perspective.

    4. Paste all copied entity mappings under the <entity-mappings> tag of the edited orm.xml file, beneath the <schema> tag. Make sure that the schema tag is configured as described above in the step Build the ORM File – Map UCMDB Classes to Database Tables. All pasted entities now appear in the JPA Structure view. From now on, mappings can be edited both graphically and manually through the xml code of the orm.xml file.

    5. Click Save.

15. Importing an Existing ORM File from an Adapter

    If an adapter already exists, the Eclipse Plug-in can be used to edit its ORM file graphically. Import the orm.xml file into Eclipse, edit it using the plug-in and then deploy it back to the UCMDB machine. To import the ORM file, press the button on the Eclipse toolbar. A confirmation dialog is displayed. Click OK. The ORM file is copied from the UCMDB machine to the active Eclipse project and all relevant classes are imported from the UCMDB class model.

    If the relevant classes do not appear in the JPA Structure view, right-click the active project in the Project Explorer view, choose Close and then Open.

    From now on, the ORM file can be edited graphically using Eclipse, and then deployed back to the UCMDB machine as described below in Deploy the ORM File to the CMDB.

16. Check the Correctness of the orm.xml File – Built-in Correctness Check

    The Eclipse JPA plug-in checks if any errors are present and marks them in the orm.xml file. Both syntax (for example, wrong tag name, unclosed tag, missing ID) and mapping errors (for example, wrong attribute name or database table field name) are checked. If there are errors, their description appears in the Problems view.

17. Create a New Integration Point

    If no integration point exists in the CMDB for this adapter, you can create it in the Integration Studio. For details, see Integration Studio.

    Fill in the integration point name in the dialog box that opens. The orm.xml file is copied to the adapter folder. An integration point is created with all the imported CI types as its supported classes, except for multinode CITs, if they are configured in the reconciliation_rules.txt file. For details, see The reconciliation_rules.txt File (for backwards compatibility).

18. Deploy the ORM File to the CMDB

    Save the orm.xml file and deploy it to the UCMDB server by clicking UCMDB > Deploy ORM. The orm.xml file is copied to the adapter folder and the adapter is reloaded. The operation result is shown in an Operation Result dialog box. If any error occurs during the reload process, the Java exception stack trace is displayed in the dialog box. If no integration point has yet been defined using the adapter, no mapping errors are detected upon deployment.

19. Run a Sample TQL Query

    1. Define a query (not a view) in the Modeling Studio. For details, see Modeling Studio.

    2. Create an integration point using the adapter that you created in the step Create a New Integration Point. For details, see New Integration Point/Edit Integration Point Dialog Box.

    3. During the creation of the adapter, verify that the CI types that should participate in the query are supported by this integration point.

    4. When configuring the CMDB plug-in, use this sample query name in the Settings dialog box. For details, see the step above Configure the CMDB Plug-in.

    5. Click the Run TWL button to run a sample TQL and verify whether it returns the required results using the newly created orm.xml file.