Skip to content
Merged
Show file tree
Hide file tree
Changes from all commits
Commits
File filter

Filter by extension

Filter by extension

Conversations
Failed to load comments.
Loading
Jump to
Jump to file
Failed to load files.
Loading
Diff view
Diff view
20 changes: 16 additions & 4 deletions README.md
Original file line number Diff line number Diff line change
Expand Up @@ -117,9 +117,13 @@ end
* Everything in the block runs on one connection. It commits when the block ends (also on `break`, `return`, `throw`)
and rolls back when it raises or the thread is killed. A nested `transaction` rolls back only its own part.
* `isolation:` (`pg`, `mysql2`) takes `:read_uncommitted`, `:read_committed`, `:repeatable_read`, `:serializable`;
`mode:` (`sqlite3`) takes `:deferred`, `:immediate`, `:exclusive`. Other values raise before anything runs.
`mode:` (`sqlite3`) takes `:deferred`, `:immediate`, `:exclusive`. Other values raise before anything runs, and so
do `isolation:` / `mode:` on a nested `transaction` (a savepoint cannot change them).
* PostgreSQL: if a statement fails and the block rescues the error, PostgreSQL still rolls the whole transaction
back, so `transaction` raises `PG::InFailedSqlTransaction` instead of pretending it committed.
back, so `transaction` raises `PG::InFailedSqlTransaction` instead of pretending it committed. In a nested
`transaction` only the savepoint is rolled back: the outer block can rescue that error and go on.
* A connection the server closed (restart, failover, killed backend) is reconnected (`pg`) or replaced in the pool
(`mysql2`) the next time it is checked out; the call that hit the dead connection still raises.
* SQLite: threads and fibers sharing one `SQLite3::Database` take turns; a transaction holds the database until it ends.

## Supported features
Expand All @@ -137,7 +141,12 @@ end

`:copyfrom` uses `COPY ... FROM STDIN` on PostgreSQL and batched multi-row `INSERT`s on SQLite. On MySQL it uses
`LOAD DATA LOCAL INFILE`, which needs `local_infile: true` in the `Mysql2::Client` params and `local_infile=1` on the
server; duplicate keys and invalid values raise `Mysql2::Error` instead of being skipped.
server; duplicate keys and invalid values raise `Mysql2::Error` instead of being skipped. Conflict clauses are kept:
`INSERT OR REPLACE` / `OR IGNORE` / `ON CONFLICT ... DO ...` on SQLite, `INSERT IGNORE` (duplicates skipped) and
`REPLACE` on MySQL. Clauses `COPY` / `LOAD DATA` cannot express (`ON CONFLICT` / `RETURNING` on PostgreSQL,
`ON DUPLICATE KEY UPDATE` on MySQL) are generation errors; use `:batchexec` for those.
* MySQL: `LOAD DATA` commits an open transaction that was started with raw SQL (`BEGIN`); call `:copyfrom` inside
`transaction` instead.

### Macros
| Macro | PostgreSQL | MySQL | SQLite | Notes |
Expand All @@ -161,7 +170,7 @@ server; duplicate keys and invalid values raise `Mysql2::Error` instead of being
The generated SQLite code uses `Monitor`: if you run Steep, add `library "monitor"` to your `Steepfile`.

### Names
* Names are snake_cased. Ruby keywords and names a row or method already uses get a trailing `_` (`class_`, `hash_`).
* Names are snake_cased, acronyms kept together (`MIN_ID` → `min_id`, `HTTPServer` → `http_server`). Ruby keywords and names a row or method already uses get a trailing `_` (`class_`, `hash_`).
* A parameter used twice is one keyword when named with `sqlc.arg`; repeated `?` / `$n` of one column become `id:`, `id_2:`.
* A table `authors` next to a table `author` keeps its plural model name (`Authors`). Query names that would replace
a generated method (`Transaction`, `Initialize`, ...) are errors.
Expand All @@ -170,6 +179,9 @@ server; duplicate keys and invalid values raise `Mysql2::Error` instead of being
* Two result columns with the same name (e.g. `a.id, b.id`) are an error: alias one (`b.id AS book_id`) or use `sqlc.embed`.
* `rubyVersion` takes `3.4` but not a patch version of it (`3.4.1`).
* SQLite's `NUMERIC` affinity can return an `Integer` where `Float` is declared.
* `:batchexec` / `:batchone` / `:batchmany` are not atomic on their own: wrap them in `transaction` for all-or-nothing.
* SQLite: a transaction belongs to the thread that opened it; a Fiber / `Enumerator#next` inside it that uses the same
database waits for it (with `async`, run the work in the transaction's own fiber).
* Not applicable to Ruby (they exist in the C# plugin): `withAsyncSuffix` / `withCancellationToken` (use the Fiber
scheduler), `useDapper`, target framework / csproj options (`moduleName` replaces the namespace).

Expand Down
21 changes: 21 additions & 0 deletions examples/authors/mysql/query.sql
Original file line number Diff line number Diff line change
Expand Up @@ -118,3 +118,24 @@ SELECT id, payload FROM regressions ORDER BY id;

-- name: TruncateRegressions :exec
TRUNCATE TABLE regressions;

-- name: CopyCopyTypes :copyfrom
INSERT INTO copy_types (id, code, doc, flags, amount) VALUES (?, ?, ?, ?, ?);

-- name: CopyCopyTypesIgnore :copyfrom
INSERT IGNORE INTO copy_types (id, code) VALUES (?, ?);

-- name: CopyCopyTypesReplace :copyfrom
REPLACE INTO copy_types (id, code) VALUES (?, ?);

-- name: CreateCopyTypesBatch :batchexec
INSERT INTO copy_types (id, code, flags, amount) VALUES (?, ?, ?, ?);

-- name: CountCopyTypesByCodes :batchone
SELECT count(*) AS matches FROM copy_types WHERE code = ? OR code = ?;

-- name: ListCopyTypes :many
SELECT id, code, doc, flags, amount FROM copy_types ORDER BY id;

-- name: TruncateCopyTypes :exec
TRUNCATE TABLE copy_types;
8 changes: 8 additions & 0 deletions examples/authors/mysql/schema.sql
Original file line number Diff line number Diff line change
Expand Up @@ -70,3 +70,11 @@ CREATE TABLE regressions (
note text,
payload varbinary(16)
);

CREATE TABLE copy_types (
id bigint NOT NULL PRIMARY KEY,
code varchar(32) NOT NULL UNIQUE,
doc json,
flags bit(8),
amount int unsigned
);
15 changes: 15 additions & 0 deletions examples/authors/postgresql/query.sql
Original file line number Diff line number Diff line change
Expand Up @@ -163,3 +163,18 @@ SELECT * FROM regressions WHERE (sqlc.narg(order_name)::text IS NULL OR "order"

-- name: TruncateRegressions :exec
TRUNCATE TABLE regressions;

-- name: CreateBatchTypesBatch :batchexec
INSERT INTO batch_types (id, ok, ratio, blobs, docs, counts, amounts, stamps) VALUES ($1, $2, $3, $4, $5, $6, $7, $8);

-- name: CopyBatchTypes :copyfrom
INSERT INTO batch_types (id, ok, ratio, blobs, docs, counts, amounts, stamps) VALUES ($1, $2, $3, $4, $5, $6, $7, $8);

-- name: ListBatchTypes :many
SELECT * FROM batch_types ORDER BY id;

-- name: CountBatchTypesAbove :one
SELECT count(*) FROM batch_types WHERE id > sqlc.arg(MIN_ID);

-- name: TruncateBatchTypes :exec
TRUNCATE TABLE batch_types;
11 changes: 11 additions & 0 deletions examples/authors/postgresql/schema.sql
Original file line number Diff line number Diff line change
Expand Up @@ -91,3 +91,14 @@ CREATE TABLE regressions (
bio_types extended.bio_type[],
uids uuid[]
);

CREATE TABLE batch_types (
id bigint PRIMARY KEY,
ok boolean,
ratio float8,
blobs bytea[],
docs jsonb[],
counts int[],
amounts numeric[],
stamps timestamptz[]
);
15 changes: 15 additions & 0 deletions examples/authors/sqlite/query.sql
Original file line number Diff line number Diff line change
Expand Up @@ -112,3 +112,18 @@ SELECT coalesce(max(price), 0) AS max_price FROM regressions;

-- name: TruncateRegressions :exec
DELETE FROM regressions;

-- name: CopyTagsOrReplace :copyfrom
INSERT OR REPLACE INTO tags (name, uses) VALUES (?, ?);

-- name: CopyTagsOrIgnore :copyfrom
INSERT OR IGNORE INTO tags (name, uses) VALUES (?, ?);

-- name: CopyTagsAddingUses :copyfrom
INSERT INTO tags (name, uses) VALUES (?, ?) ON CONFLICT (name) DO UPDATE SET uses = tags.uses + excluded.uses;

-- name: ListTags :many
SELECT name, uses FROM tags ORDER BY name;

-- name: TruncateTags :exec
DELETE FROM tags;
5 changes: 5 additions & 0 deletions examples/authors/sqlite/schema.sql
Original file line number Diff line number Diff line change
Expand Up @@ -28,3 +28,8 @@ CREATE TABLE regressions (
"order" VARCHAR(255) NOT NULL,
price DECIMAL(10,2)
);

CREATE TABLE tags (
name TEXT NOT NULL PRIMARY KEY,
uses INTEGER NOT NULL DEFAULT 0
);
9 changes: 9 additions & 0 deletions examples/mysql2/models.rb
Original file line number Diff line number Diff line change
Expand Up @@ -69,4 +69,13 @@ class ExtendedBio < Data.define(:author_name, :name, :bio_type, :author_type)

class Regression < Data.define(:id, :order, :note, :payload)
end

class CopyType < Data.define(
:id,
:code,
:doc,
:flags,
:amount
)
end
end
15 changes: 15 additions & 0 deletions examples/mysql2/models.rbs
Original file line number Diff line number Diff line change
Expand Up @@ -123,4 +123,19 @@ module Mysql2Codegen
attr_reader payload: String?
def initialize: ( Integer id, String order, String? note, String? payload) -> void
end

class CopyType
attr_reader id: Integer
attr_reader code: String
attr_reader doc: String?
attr_reader flags: String?
attr_reader amount: Integer?
def initialize: (
Integer id,
String code,
String? doc,
String? flags,
Integer? amount
) -> void
end
end
Loading
Loading