schemacrawler does not like column name (identifier) quotation with sqlite

[hjoukl]

Hi,

really nice tool! Thanks for your work on it.

Here goes:

This sqlite DDL results in output with correct primary key information and markup (bold, italic):

PRAGMA foreign_keys = ON;

CREATE TABLE Deal (
  deal_source TEXT NOT NULL,
  deal_id TEXT NOT NULL,
  fo_deal_id TEXT NOT NULL,
  PRIMARY KEY (deal_source, deal_id)
);

CREATE TABLE DealCost (
  deal_source TEXT NOT NULL,
  deal_id TEXT NOT NULL,
  deal_cost_no INTEGER NOT NULL,
  fee_type TEXT NOT NULL,
  PRIMARY KEY (deal_source, deal_id, deal_cost_no),
  FOREIGN KEY (deal_source, deal_id) REFERENCES Deal (deal_source, deal_id)
);

htmlx output result:

...while this does not:

PRAGMA foreign_keys = ON;

CREATE TABLE "Deal" (
  "deal_source" TEXT NOT NULL,
  "deal_id" TEXT NOT NULL,
  "fo_deal_id" TEXT NOT NULL,
  PRIMARY KEY ("deal_source", "deal_id")
);

CREATE TABLE "DealCost" (
  "deal_source" TEXT NOT NULL,
  "deal_id" TEXT NOT NULL,
  "deal_cost_no" INTEGER NOT NULL,
  "fee_type" TEXT NOT NULL,
  PRIMARY KEY ("deal_source", "deal_id", "deal_cost_no"),
  FOREIGN KEY ("deal_source", "deal_id") REFERENCES "Deal" ("deal_source", "deal_id")
);

htmlx output result:

The only difference is the quoting of identifiers, which is more or less recommended to not clash with sqlite keywords; for me, this also happens as a side effect of using ponyorm to create the DB tables i.e. I can't easily switch this off.

SchemaCrawler command line:

java -classpath /var/tmp/ER_from_db/schemacrawler/schemacrawler-14.16.01-main/_schemacrawler/lib/derby-10.13.1.1.jar:/var/tmp/ER_from_db/schemacrawler/schemacrawler-14.16.01-main/_schemacrawler/lib/h2-1.4.194.jar:/var/tmp/ER_from_db/schemacrawler/schemacrawler-14.16.01-main/_schemacrawler/lib/hsqldb-2.3.4.jar:/var/tmp/ER_from_db/schemacrawler/schemacrawler-14.16.01-main/_schemacrawler/lib/mssql-jdbc-6.1.0.jre8.jar:/var/tmp/ER_from_db/schemacrawler/schemacrawler-14.16.01-main/_schemacrawler/lib/mysql-connector-java-6.0.6.jar:/var/tmp/ER_from_db/schemacrawler/schemacrawler-14.16.01-main/_schemacrawler/lib/postgresql-42.0.0.jar:/var/tmp/ER_from_db/schemacrawler/schemacrawler-14.16.01-main/_schemacrawler/lib/schemacrawler-14.16.01.jar:/var/tmp/ER_from_db/schemacrawler/schemacrawler-14.16.01-main/_schemacrawler/lib/schemacrawler-db2-14.16.01.jar:/var/tmp/ER_from_db/schemacrawler/schemacrawler-14.16.01-main/_schemacrawler/lib/schemacrawler-hsqldb-14.16.01.jar:/var/tmp/ER_from_db/schemacrawler/schemacrawler-14.16.01-main/_schemacrawler/lib/schemacrawler-lint-14.16.01.jar:/var/tmp/ER_from_db/schemacrawler/schemacrawler-14.16.01-main/_schemacrawler/lib/schemacrawler-mysql-14.16.01.jar:/var/tmp/ER_from_db/schemacrawler/schemacrawler-14.16.01-main/_schemacrawler/lib/schemacrawler-offline-14.16.01.jar:/var/tmp/ER_from_db/schemacrawler/schemacrawler-14.16.01-main/_schemacrawler/lib/schemacrawler-oracle-14.16.01.jar:/var/tmp/ER_from_db/schemacrawler/schemacrawler-14.16.01-main/_schemacrawler/lib/schemacrawler-postgresql-14.16.01.jar:/var/tmp/ER_from_db/schemacrawler/schemacrawler-14.16.01-main/_schemacrawler/lib/schemacrawler-sqlite-14.16.01.jar:/var/tmp/ER_from_db/schemacrawler/schemacrawler-14.16.01-main/_schemacrawler/lib/schemacrawler-sqlserver-14.16.01.jar:/var/tmp/ER_from_db/schemacrawler/schemacrawler-14.16.01-main/_schemacrawler/lib/schemacrawler-sybaseiq-14.16.01.jar:/var/tmp/ER_from_db/schemacrawler/schemacrawler-14.16.01-main/_schemacrawler/lib/sqlite-jdbc-3.7.8.jar:/var/tmp/ER_from_db/schemacrawler/schemacrawler-14.16.01-main/_schemacrawler/lib/xmlpull-1.1.3.1.jar:/var/tmp/ER_from_db/schemacrawler/schemacrawler-14.16.01-main/_schemacrawler/lib/xpp3_min-1.1.4c.jar:/var/tmp/ER_from_db/schemacrawler/schemacrawler-14.16.01-main/_schemacrawler/lib/xstream-1.4.9.jar schemacrawler.Main -server=sqlite -database=/var/tmp/sample.db -outputformat=htmlx -outputfile=/var/tmp/ER_from_db/schemacrawler/sample.htmlx -command=schema -infolevel=maximum -user= -password= -portablenames

The same happens with other diagram output formats (png, svg).

Some version information: graphviz 2.34.0, sqlite 3.7.17 (yeah I know this is all pretty old but I don't currently have access to more recent versions - corporate environment...)

[schemacrawler]

That is a very interesting bug in the SQLite JDBC driver. However, the good news is that I can work around it in the SchemaCrawler code. I will fix it and release a new SchemaCrawler version.

[schemacrawler]

Please use SchemaCrawler 14.16.02.