LOBase HSQL avoid having to put table and field names in quote marks

using LO Base with a local HSQL database file.

What I want to do: copy and paste my existing (and in some cases very long) queries, which are of the form:

SELECT table1.field1, table2.field2 FROM table1, table2 WHERE table1.id=table2.id

what I am having to do:

SELECT "table1"."field1", "table2"."field2" FROM "table1", "table2" WHERE "table1"."id"="table2"."id"

I note then when I use LO Base to connect to a remote MySQL database and run the queries, it works fine without the quote marks, but fails when data source is local HSQL database. Is there any way around this? I do not want to have to edit a whole load of complicated queries... any help appreciated

Solution

Quotes are required unless the table and field names are upper case, as explained at http://hsqldb.org/doc/2.0/guide/guide.html:

Standard SQL is not case sensitive, except when names of objects are enclosed in double-quotes... all uppercase for unquoted identifiers.

If you don't want this, move the data into a local MySQL instance, or any other database that has case sensitivity without quotes.

Alternatively, write regular expressions that add quotes to your queries before running them. This can be done in LibreOffice Calc, putting the original queries in one column and then a macro-based regular expression formula in the next column that adds the quotes.

It looks like this could get complex, so I would write a macro in Python instead of Basic since there is a regex library. Here is some example code.

import re

def quote_identifier(matchobj):
    if matchobj.group(0).upper() in ('SELECT','FROM','WHERE'):
        return matchobj.group(0)
    else:
        return '"{}"'.format(matchobj.group(0))

s = "SELECT table1.field1, table2.field2 FROM table1, table2 WHERE table1.id=table2.id"
result = re.sub("(\w+)", quote_identifier, s)
print(result)

This prints the quoted result:

SELECT "table1"."field1", "table2"."field2" FROM "table1", "table2" WHERE "table1"."id"="table2"."id"