René's URL Explorer Experiment

{"@context":"https://schema.org","@type":"DiscussionForumPosting","headline":"PRAGMA queries are not recognised","articleBody":"### Version\r\n\r\n1.25.0\r\n\r\n### What happened?\r\n\r\nSQLite requires a `PRAGMA` query to enable foreign key constraints. Unfortunately, sqlc does not seem to recognise `PRAGMA` as a valid query, and actually collects everything until the first query it does recognise. Let me illustrate. In the `queries.sql` below, there are three named `PRAGMA` queries followed by an `INSERT`. Instead of generating four query functions, sqlc actually just creates one:\r\n\r\n```go\r\n\r\nconst pragmaForeignKeysEnable = `-- name: PragmaForeignKeysEnable :exec\r\n\r\nPRAGMA foreign_keys = 1;\r\n\r\nPRAGMA foreign_keys = 0;\r\n\r\nPRAGMA foreign_keys;\r\n\r\n\r\n\r\nINSERT INTO jobs (\r\n  created_at,\r\n  uuid,\r\n  name,\r\n  job_type,\r\n  priority,\r\n  status,\r\n  activity,\r\n  settings,\r\n  metadata,\r\n  storage_shaman_checkout_id\r\n)\r\nVALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )\r\n`\r\n\r\ntype PragmaForeignKeysEnableParams struct {\r\n\tCreatedAt               time.Time\r\n\tUuid                    string\r\n\tName                    string\r\n\tJobType                 string\r\n\tPriority                int64\r\n\tStatus                  string\r\n\tActivity                string\r\n\tSettings                json.RawMessage\r\n\tMetadata                json.RawMessage\r\n\tStorageShamanCheckoutID sql.NullString\r\n}\r\n\r\n// PRAGMA queries\r\n//\r\n// Jobs / Tasks queries\r\nfunc (q *Queries) PragmaForeignKeysEnable(ctx context.Context, arg PragmaForeignKeysEnableParams) error {\r\n\t_, err := q.db.ExecContext(ctx, pragmaForeignKeysEnable,\r\n\t\targ.CreatedAt,\r\n\t\targ.Uuid,\r\n\t\targ.Name,\r\n\t\targ.JobType,\r\n\t\targ.Priority,\r\n\t\targ.Status,\r\n\t\targ.Activity,\r\n\t\targ.Settings,\r\n\t\targ.Metadata,\r\n\t\targ.StorageShamanCheckoutID,\r\n\t)\r\n\treturn err\r\n}\r\n```\r\n\r\nAs you can see, instead of using `-- name: ...` as query boundary, sqlc groups everything together until the `INSERT`.\r\n\r\n\r\n\r\n### Relevant log output\r\n\r\n_No response_\r\n\r\n### Database schema\r\n\r\n```sql\r\nCREATE TABLE jobs (\r\n  id integer NOT NULL,\r\n  created_at datetime NOT NULL,\r\n  updated_at datetime,\r\n  uuid varchar(36) UNIQUE DEFAULT '' NOT NULL,\r\n  name varchar(64) DEFAULT '' NOT NULL,\r\n  job_type varchar(32) DEFAULT '' NOT NULL,\r\n  priority smallint DEFAULT 0 NOT NULL,\r\n  status varchar(32) DEFAULT '' NOT NULL,\r\n  activity varchar(255) DEFAULT '' NOT NULL,\r\n  settings jsonb NOT NULL,\r\n  metadata jsonb NOT NULL,\r\n  delete_requested_at datetime,\r\n  storage_shaman_checkout_id varchar(255) DEFAULT '',\r\n  PRIMARY KEY (id),\r\n)\r\n```\r\n\r\n\r\n### SQL queries\r\n\r\n```sql\r\n-- PRAGMA queries\r\n--\r\n\r\n-- name: PragmaForeignKeysEnable :exec\r\nPRAGMA foreign_keys = 1;\r\n\r\n-- name: PragmaForeignKeysDisable :exec\r\nPRAGMA foreign_keys = 0;\r\n\r\n-- name: PragmaForeignKeysGet :one\r\nPRAGMA foreign_keys;\r\n\r\n\r\n-- Jobs / Tasks queries\r\n--\r\n\r\n-- name: CreateJob :exec\r\nINSERT INTO jobs (\r\n  created_at,\r\n  uuid,\r\n  name,\r\n  job_type,\r\n  priority,\r\n  status,\r\n  activity,\r\n  settings,\r\n  metadata,\r\n  storage_shaman_checkout_id\r\n)\r\nVALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ? );\r\n```\r\n\r\n\r\n### Configuration\r\n\r\n_No response_\r\n\r\n### Playground URL\r\n\r\nhttps://play.sqlc.dev/p/ce20411be4624a0295c2e51b8eb075a4e1069ac3d6951778cd6a54f032831a3c\r\n\r\n### What operating system are you using?\r\n\r\nWindows\r\n\r\n### What database engines are you using?\r\n\r\nSQLite\r\n\r\n### What type of code are you generating?\r\n\r\nGo","author":{"url":"https://github.com/sybrenstuvel","@type":"Person","name":"sybrenstuvel"},"datePublished":"2024-03-02T17:22:56.000Z","interactionStatistic":{"@type":"InteractionCounter","interactionType":"https://schema.org/CommentAction","userInteractionCount":7},"url":"https://github.com/3237/sqlc/issues/3237"}