warn when using quotes or parse the SQL

[mpldr]

What do you want to change?

I have the following setup:

queries.sql

-- name: CheckDatabase :exec
SELECT
	COUNT(ID),
	COUNT(Destination),
	COUNT(Hits),
	COUNT(Owner)
FROM redirections;

schema.sql

CREATE TABLE "redirections" (
	"ID"	TEXT NOT NULL,
	"Destination"	TEXT NOT NULL,
	"Hits"	INTEGER NOT NULL DEFAULT 0,
	"Owner"	TEXT NOT NULL,
	PRIMARY KEY("ID")
);

sqlc.json

{
	"version": "2",
	"sql": [{
		"schema": "./internal/datastore/db/schema.sql",
		"queries": "./internal/datastore/db/queries.sql",
		"engine": "sqlite",
		"gen": {
			"go": {
				"out": "db"
			}
		}
	}]
}

I think this should work, and in fact play.sqlc.dev agrees with me there. BUT

$ go run github.com/sqlc-dev/sqlc/cmd/sqlc@latest generate
# package 
internal/datastore/db/queries.sql:1:1: relation "redirections" does not exist
exit status 1

Thanks to @qbit, I learned about the use of quotations in the CREATE statement leading to all uses of the identifier having to be quoted. Leaving aside how … unideal … this limitation is, I would consider this unexpected behaviour, so I propose one of the following solutions:

• actually parse the name of the identifier (preferred)
• warn the user about this pitfall (if not in the CLI output, then in the docs)

Since I don't think this affects any specific database engine, or language backend, I have not selected any of them. If that's incorrect, I apologise.

What database engines need to be changed?

No response

What programming language backends need to be changed?

No response

[apastuhov]

Current behaviour actually affects usage of SQLC with SQLite. If initial table was created without quotes, but then developer decided to rename table and generate dump with sqlite3 db.sqlite .schema - new schema will have quotes, it will break SQLC codegen, and reason is not obvious.

Steps to reproduce:

sqlite3 db.sqlite3 'CREATE TABLE test (id INT);'
sqlite3 db.sqlite3 .schema
# output: CREATE TABLE test (id INT);

sqlite3 db.sqlite3 'ALTER TABLE test RENAME TO test_upd;'
sqlite3 db.sqlite3 .schema
# output: CREATE TABLE IF NOT EXISTS "test_upd" (id INT);

See more explanation here - https://sqlite.org/forum/info/6c48ec4e4cb99987e65afb50f1b40e8b5b70ddb34f07ec13ee2032bed487892a

I understand that it is specifics of sqlite, but actual name of db has no quotes, here is result of sqlite3 db.sqlite3 'select * from sqlite_master;' -header

type  | name     | tbl_name | rootpage | sql
table | test_upd | test_upd | 2        | CREATE TABLE "test_upd" (id INT)