Purpose

The purpose of this guide is to show you how to connect to Fisheye/Crucible's embedded Hypersonic SQL (HSQL) database using a database administration tool called DbVisualizer so you can investigate the Fisheye/Crucible database schema. 

Solution

1. Download and install the latest copy of DBVisualizer.
2. Create a backup of the HSQL database and connect DbVisualizer to the copy (backup) of the database rather than the actual database.
   1. The reason why we make such recommendation is because DbVisualizer will not be able to connect to the HSQL database while Fisheye/Crucible is running. Either you create a copy of the database and connect to the copy or stop Fisheye/Crucible before attempting to connect DbVisualizer to it.

Please ensure that you read and follow the instructions below carefully.

Creating a new database connection

1. Open the Connection Wizard. 
   
2. Enter an identifiable name for the connection. For example: myFisheyeDatabase
    
3. Click 'Next'.

Selecting a JDBC Driver

1. From the drop down list select HSQLDB embedded.
    
2. Click the Load Driver File(s) button.
3. Browse to FISHEYE_HOME/lib directory where the hsqldb*.jar file is located. For example: /atlassian/apps/fisheye/lib/hsqldb-1.8.0.10.jar.

Entering the connection details

1. Browse to your FISHEYE_HOME directory if you are going to connect to your production database or browse to your expanded <<Backup>> directory that was created when you unzipped the backup file you made (if you just want to run some queries against your data).

   Fisheye/Crucible cannot be running if you attempt to connect to your production database.

2. Open the /var/data/crudb folder.
3. Select the crucible.properties file 
4. Remove the .properties from the file name.
5. Type in sa for the username.
6. Leave the password field blank.
   
7. Click on Ping Server to verify that the details are correct.
8. Click on 'Finish' to complete the setup.

Connecting to the embedded database

1. Select the connection from the list on the left hand side.

   If you're trying to connect to one of the external databases make sure to select a different Database Type & Driver (JDBC).

   After that specify a Database URL for your Database Type. You can look at your FISHEYE_HOME/config.xml file if you are not sure what is the database url and the credentials you should be using.

   

2. You can now click on 'Connect' to connect to the embedded database. 

Viewing tables and references

1. Click on any table on the left and the References tab to see a graph that displays references to that table:
    
2. Alternatively you can also click on Tables (from the left menu) and on the References tab to see all of the tables in the database together with all the references. The following image was taken from the Fisheye/Crucible 4.8.7 database schema and is just an illustration/ example. If you're looking to explore the database schema you should not rely on this image alone and rather follow the steps outlined in this page so you can browse through the database on your own. Please note that the tables might differ between different versions of Fisheye/Crucible.