How to query database tables using Slick and HSQLDB?

Question

I've been playing around with Slick (verison 2.1.0) and the HSQLDB JDBC driver (2.3.2) and I can create tables and execute CRUD queries but now I have the problem that I can't seem to be able to get the database meta data, specifically the existing tables.

This is a similar problem to How to execute DDL only when tables don't exist? however that is for MySQL and the solution doesn't work for me.

Here is some code:

  private def initDatabase() {
    log.info("Initialising song database.")
    db.withSession {
      implicit session =>
        {
          // This doesn't seem to be working...
          val tables = MTable.getTables.list
          log.debug("Existing database tables: {}", tables)

          createIfNotExists(artists, songs, broadcasts)
        }
    }
  }

  private def createIfNotExists(tables: TableQuery[_ <: Table[_]]*)(implicit session: Session) {
    tables foreach { table => if (MTable.getTables(table.baseTableRow.tableName).list.isEmpty) table.ddl.create }
  }

This always logs:

Existing database tables: List()

And then when the table is created I get the following:

object name already exists: ARTIST in statement [create table "ARTIST"...

Anyone have any idea why this might not be working?

I can see that the driver contains the following method org.hsqldb.jdbc.JDBCDatabaseMetaData.getTables(String, String, String, String[]) (for some reason I can't seem to debug into this in Eclipse even though the source is attached).

Update:

I managed to build HSQLDB and I was able to debug it that way. The actual query that is being executed for the unfiltered list is:

SELECT * FROM INFORMATION_SCHEMA.SYSTEM_TABLES WHERE TRUE AND TABLE_CAT IS NULL AND TABLE_SCHEM IS NULL

The query in the createIfNotExists foreach is:

SELECT * FROM INFORMATION_SCHEMA.SYSTEM_TABLES WHERE TRUE AND TABLE_CAT IS NULL AND TABLE_SCHEM IS NULL AND TABLE_NAME = 'ARTIST'

I'll have to investigate this further tomorrow.

steinybot steinybot · Accepted Answer · 2015-04-09T13:10:55

I finally figured it out. I used Squirrel SQL to actually have a look in my HSQLDB and see what were in those tables. It turns out that the TABLE_CAT and TABLE_SCHEM columns in the where clause of those queries are not null, they have the value of PUBLIC. At least that's the case for me and I haven't done anything special with Slick just your bog standard lift embedding.

So to get it to work I had to use this:

MTable.getTables(Some("PUBLIC"), Some("PUBLIC"), Some(tableName), Some(Seq("TABLE"))).list

How to query database tables using Slick and HSQLDB?

1 Answers