Blog

After years of owning a PC and taking my environment setup for granted, I landed with a Mac OS that has proven to be a bit of a challenge when it comes to spinning up the 11g JSK. Among the drawbacks is the lack of a Content Server Explorer (CS-Explorer) as the JSK stores it as an executable.

The lack of a Mac installer for the explorer within the JSK is not drastic. Technically, miracles can happen - or close enough - when you have an Intel processor and the help of a third-party tool. However, today I have decided to take a completely different route. Come to think of it, the hierarchical structure defined in CS-Explorer is simple, logical, and well thought of. Therefore it is important to answer the single following question:

Which features of the CS-Explorer tool do I use the most?

1. Dig into database contents.
2. Insert/Delete/Update content rows.

You have guessed it right: we need a database interface; and HyperSQL comes to the rescue for its simplicity and the fact that it is a relational database management system written in java. Let’s set it up within Eclipse to create a seamless integration and keep all my projects within the same workspace.

Environment Settings

JSK installation: 11.1.1.8

Eclipse Mars (you can use newer versions).

 

File System

Go to WCS Install and open the apache folder: App_Server/apache-tomcat-7.0.42/conf/server.xml

 

Next, let's grab the following variable values as we will need them soon (make a note of each):

1. Username
2. Password
3. url

            <Context path="/cs" docBase="cs" reloadable="true" crossContext="true">

           <Resource name="csDataSource" auth="Container" type="javax.sql.DataSource"

                     maxActive="50" maxIdle="10"

                     username=<username>

                     password=<password>

                     driverClassName="org.hsqldb.jdbcDriver"

url="jdbc:hsqldb:<path>/JSK/11.1.1.8.0.app/App_Server/apache-tomcat-7.0.42/Sites/default/data/hypersonic/csDB"/>

         </Context>

 

Set Up Eclipse Tool

Open Eclipse.

Go to Help > Eclipse Marketplace and Install your eclipse version of the Eclipse DTP (Data Tools Platform). I am running Eclipse Mars, therefore my option seems to be:

Click Confirm on the next screen and Accept all agreements.

Restart the Eclipse instance.

Go to Window > Open Perspective > Other > Database Development to open the newly installed perspective.

 

 

Database Connection

Right-click on the Database Connections node and select New to create a new connection instance.

Select HSQLDB and click Next to enter the database credentials retrieved from the server.xml file under your apache instance. Check your notes from the File System section above.

 

 

Resolving Driver

If there is no driver available or selected, click on the starred-plus sign on the far-right of the dropdown.

1. Download the hsqldb jar here: https://sourceforge.net/projects/hsqldb/files/hsqldb/hsqldb_2_4/hsqldb-2.4.0.zip/download
2. Extract the jar file from hsqldb-2.4.0/ hsqldb/lib/hsqldb.jar and add it to a Tools folder under your WCS workspace.
3. You will see an error message: Unable to locate JAR/zip. Ignore the message and just point to the newly downloaded jar.
4. Click on the JAR List tab then on the driver file listed.

 

 

5. Click Edit JAR/zip…
6. Point to the lib/hsqldb.jar location where you downloaded the zip file above.
7. Click OK.

Fill out the credentials from the server.xml information gathered at the beginning of this tutorial.

 

 

 

Point the Database location specifically to:

file:JSK/11.1.1.8.0.app/App_Server/apache-tomcat-7.0.42/Sites/default/data/hypersonic/csDB

It is extremely important to link the database location with the following properties set. By default, the connection is processed in-memory (mem) database and faulty connections create a temporary database instance. Any addition to the JSK's hypersonic folder will conflict with your Sites deployment.

Click on the Optional tab. And enter the following two properties:

1. ifexists=true - connects only if database already exists
2. shutdown=true - shuts down the database when the last connection is closed

 

 

Click on Test Connection. You should receive a positive response:

 

 

If the test connection fails, please verify your database entries and try again.

Click OK to close the ping window then click Finish.

 

Data Source Explorer

All your tables are listed under CSExplorer > Catalogs > PUBLIC > Schemas > PUBLIC > Tables

 

 

Query Tool

In order to execute SQL command lines, you can:

Right-click on New HSQLDB and select Open SQL Scrapbook.

Click on the Name dropdown and select the connection.

Then write a query inside the textarea field. For instance: Select * from aviimage

Right-click anywhere on the field and select Execute All.

 

 

 

Once executed, the SQL Results tab will contain results and status about the query:

 

 

Table Flush 

Once you have made changes to the database using our newly implemented strategy, you must use CatalogManager to flush the internal memory cache of the affected tables. As we are using an integration outside of the WCS development tools, we must flush the tables manually to let WCS consume the modified content. You can do so easily in multiple ways:

• Create a new CSElement and a call to the catalogmanager's flushcatalog commands:

<ics:catalogmanager>
   <ics:argument name="ftcmd" value="flushcatalog" />
   <ics:argument name="tablename" value="<table_name>" />
</ics:catalogmanager>

or

<ics:flushcatalog catalog="<table_name>"/>

 

• Hit the CatalogManager servlet directly (run in browser):

http://<host>:<port>/cs/CatalogManager?ftcmd=flushcatalog&tablename=<table_to_flush>&authusername=<authorized_username>&authpassword=<user_password>

You shall only need the authorized username/password pair if the browser session is not already authenticated.

 

About HSQLDB

There is a list of known issues that users of hsqldb deal with every now and then. You can find them here: http://hsqldb.org/web/hsqlFAQ.html

 

And that completes our tutorial on using HSQLDB on a MacOS machine in lieu of the Window's bound CSExplorer!