#185 SchemaSpy doesn't use quotes for PostGreSQL databases

[wizard07]

I have a PostGreSQL database with several schemas and relationships between them. I created the schemas with case-sensitive names. SchemaSpy will take the name of schemas in correctly with the -s and -schemas switches, but it doesn't use them correctly. I get errors for every table on all case-sensetive schemas that look like this:

WARNING: Table.initColumnAutoUpdate - SQL: select * from CaseSensetiveSchemaName."TableName" where 0 = 1
WARNING: Table.initColumnAutoUpdate - Failed to determine auto increment status: org.postgresql.util.PSQLException: ERROR: relation "casesensetiveschemaname.TableName" does not exist

I tried to use various combinations of quotes to 'trick' SchemaSpy into use the correct schema name, but to no avail. In the above errors, CaseSensetiveSchemaName is the name of the schema I passed to SchemaSpy with the -schemas option (-schemas "CaseSensetiveSchemaName"). You can see where it used CaseSensetiveSchemaName in the SELECT query, but it gets turned into casesensetiveschemaname by PostGre. This is something that the database itself does, so to preserve capitalization of object names, SchemaSpy needs to wrap everything in quotes for PostGre databases.

[SDietrich]

I can confirm this error. I have the same situation and tried the same thing, various quotes on the command line etc. didn't help.
The interesting fact is, SchemaSpy does it correctly on some queries, e.g. the relationships are figured out correctly. Only the queries for the number of rows and the auto-increment value seem to use the schema name unquoted.

[SDietrich]

I took a look into the source code and tracked down the problem:
In the file Table.java the functions initColumnAutoUpdate() and fetchNumRows() add the schema name unquoted.
Interestingly though, right after the schema name is added to the query there is an if-structure to check whether quotes are needed for the table name.

So the solution is very easy: Move the adding of the schema name inside the if-structure and add quotes to it if they are required, just like for the table names.

I will attach a patch to this ticket within the next couple of days.

[SRS]

I am also encountering this situation.

Database is PostgreSQL, and schema name is PSA.

These were the two main types of errors I am getting:

Error #1
15:22:49.274 WARNING: Table.initColumnAutoUpdate - SQL: select * from PSA."vendor" where 0 = 1
15:22:49.274 WARNING: Table.initColumnAutoUpdate - Failed to determine auto increment status: org.postgresql.util.PSQLException: ERROR: relation "psa.vendor" does not exist

Error #2
15:22:58.561 WARNING: Table.fetchNumRows - Unable to extract the number of rows for table vendor, using '-1'
15:22:58.561 WARNING: Table.fetchNumRows - org.postgresql.util.PSQLException: ERROR: relation "psa.vendor" does not exist

[SRS]

Followed pointers given by @SDietrich and made update to the source code and it appears to have fixed the quoting of the schema names in PostgreSQL.

This is working for my situation as described in prior comment.

I have only tested this against PostgreSQL 9.2, running with driver postgresql-jdbc4.jar, against various schema names (such as public, PSA, PSAtestPSA).

I am attaching the patch for review at this time (I can also attach the compiled jar).