#hsql

Often it's useful to try and execute Liferay's out-of-the-box functionality in a non-out-of-the-box way. For example, creating a new site, creating a page, adding a portlet, etc.

Once you know where to look, tracking down the service calls to make is relatively straight forward. However, not many people are aware of where to look and what to look for. So the next couple of posts here are going to cover my tips and tricks for tracking down exactly what Liferay is doing behind the scenes when you click on something.

HSQL Version

The first tip is to turn on your database's logging so you can see the actual SQL being written. If you're using the default HSQL database you're already set. HSQL doesn't store any structured data on disk - it just writes a log of changes and replays that log next time it starts up. Incidentally, this makes it particularly easy to share a portal configuration between a dev team - just check the relevant lportal.* files in to source control and your team can all have the same setup. However, there'll be a post in the near future discussing an alternative to meet this same end with the benefit of also keeping your non-local environments synchronized and consistent.

If you take a look under your Liferay installs data/hsql directory, you'll see something like lportal.log and lportal.script. Together these files comprise everything that was done until quite recently (lportal.script) and the recent (and current) changes (lportal.log). lportal.log rolls over and is appended to lportal.script - so if you're watching at runtime to see what's happening "tail -F lportal.log" is all you need.

HSQL also has the benefit that only changes are logged, meaning you don't have to wade through dozens of select statements to track down the relevant updates. The down side of not seeing selects in these logs is that sometimes magic numbers just appear without any good pointer where they came from. However, the columns they're being inserted into usually provide a good clue as to their source. Combine this with some judicious use of a database browser and you can usually map the values fairly simply, in spite of Liferay's distaste for foreign keys.

Bonus tip: You can connect to a local HSQL database while it's in use via the JDBC URL "jdbc:hsqldb:file:/Users/martin/Applications/liferay-portal-6.1.20-ee-ga2/data/hsql/lportal;readonly=true" (your path will obviously vary) with the username 'SA' (no quotes) and no password. Note the ";readonly=true" parameter - without it the HSQL driver won't be able to get a read/write connection if Liferay is running and will fail to connect. If Liferay isn't running and you open a read/write connection, Liferay won't be able to start up for the same reason.

MySQL Version

Enabling the general query log in MySQL just requires adding this line to your instance's my.cnf (paths here are based on the OSX/Homebrew MySQL install - they'll be different for other operating systems, installations, etc):

$ cat /usr/local/etc/my.cnf [mysqld] general-log=on

MySQL will now start writing all queries to it's general log file (in my case: /usr/local/var/mysql/Martin-MacBook-Pro.log). From there on - the same rules as above apply.

Generic version (Hibernate logging)

What if you're not using MySQL of HSQL and the database you're using can't give you those logs? I don't know if this is really an issue or not (I suspect not) but this is just a catch all option that's also useful for general Hibernate work.

My suggestion is to turn on Hibernate's logging of prepared statement execution and parameter binding so you can see and track things at that level. Because this is slightly higher up the stack (application level logging rather than database specific logging) it should work across the board, regardless of database.

The first step is to configure your Liferay logging environment via portal-log4j-ext.xml. With the skeleton in place, add a new appender for SQL to be logged to and configure the relevant categories:

<?xml version="1.0"?> <!DOCTYPE log4j:configuration SYSTEM "log4j.dtd"> <log4j:configuration xmlns:log4j="http://jakarta.apache.org/log4j/"> <appender name="SQL_FILE" class="org.apache.log4j.rolling.RollingFileAppender"> <rollingPolicy class="org.apache.log4j.rolling.TimeBasedRollingPolicy"> <param name="FileNamePattern" value="@liferay.home@/logs/sql.%d{yyyy-MM-dd}.log" /> </rollingPolicy> <layout class="org.apache.log4j.PatternLayout"> <param name="ConversionPattern" value="%d{ABSOLUTE} %-5p [%c{1}:%L] %m%n" /> </layout> </appender> <logger name="org.hibernate.SQL"> <level value="TRACE" /> <appender-ref ref="SQL_FILE" /> </logger> <logger name="org.hibernate.type"> <level value="TRACE" /> <appender-ref ref="SQL_FILE" /> </logger> </log4j:configuration>

Start up Liferay and tail the log file you've configured and you should be able to see the updates coming through as you make changes. As with MySQL logging, this approach will give you the full set of queries as well as updates so be prepared to break out grep to filter through some of the noise. However, everything that hits the database will be logged so you'll be able to see lookup queries, etc.

Final note