HSQLDB Query log - Output SQL statements to log file

It's sometimes useful (especially for debbuging) to be able to see all the SQL statements that the database runs

Lots of RDBMS have this very useful feature (MySQL for example calls it query log)

The HSQLDB is one of those and it can be accomplished in several ways, here I will show you how to do it within the db url and using a SQL statement :

1.- In the DB url

Just add the following parameter to your db url :

hsqldb.sqllog=3

For example :

jdbc:hsqldb:file:c:/dev/db/my_db;shutdown=true;hsqldb.sqllog=3

2.- Through a SQL statement

SET DATABASE EVENT LOG SQL LEVEL 3

Once this is done you will find a log file in the same folder as your database with the naming convention like ${dbname}.sql.log like so :

my_db.sql.log

More info here : hsqldb monitoring documentation

MongoDB $where clause to query array length

There is no direct way in mongodb to return all the documents in where a sub collection has at least X number of entries

  {
    "name" : "Terry Brooks",
    "books" : [ 
              "The Sword of Shannara", 
               "The Elfstones of Shannara",
               "The Wishsong of Shannara"
              ]
  },
 {
    "name" : "Oscar Wilde",
    "books" : [ 
              "The Picture of Dorian Gray"
             
              ]
  }

Let's say that I want all the authors that have written more than 1 book. There is no direct way in mongodb to do this. it needs to be done either by map reduce or perhaps with the new aggregation framework but you cannot combine $gt and $size operators like so :

    db.AUTHORS.find({"books" : {$size : {$gt : 1}}});

It doesn't work, you wont get any error messages but an empty result. MongoDb allows Javascript evaluation through the $where operator although it's significantly slower than native operators it's very flexible and a quick way of executing a query without using map reduce or other means :

    db.AUTHORS.find({$where : "this.books.length > 1"});

But when this query was executed the following error kept coming up :

{
    "error": {
        "$err": "erroroninvocationof$wherefunction: JSError: TypeError: this.bookshasnopropertiesnofile_a: 0",
        "code": 10071
    }
}

The error is not very helpfull (at least to me) and as it turns out the origin of the problem was the fact that not all Author documents in my database had the "books" array. So in order to execute a length query on the "books" array it's necessary to ensure that the array field (books) exists :

    db.AUTHORS.find({"books" : {$exists: true}, $where : "this.books.length > 0"});