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
11 changes: 9 additions & 2 deletions README.md
Original file line number Diff line number Diff line change
Expand Up @@ -98,7 +98,7 @@ per table, one module of constants per enum), `connection.rb` (shared helpers) a
* **Bang finders:** every `:one` query also gets a `get_author!` that raises `QuerySql::NotFound` instead of returning `nil`.
* **Connections:** `QuerySql.connect(params, **pool_options)` builds a [`connection_pool`](https://github.com/mperham/connection_pool).
To use a pool you already have (or a fake in tests) pass it to `QuerySql.new(pool)`; anything with `#with` works.
For SQLite pass a `SQLite3::Database`: `QuerySql.new(db)`.
For SQLite pass a `SQLite3::Database` (opened with any options, `results_as_hash: true` included): `QuerySql.new(db)`.
* **Logging:** `connect(params, logger: Rails.logger)` / `new(pool, logger:)` logs `QuerySql#get_author (0.4ms)` at
debug level for each query and transaction. Parameter values are never logged.
* **Async / fibers:** it is plain synchronous Ruby and works with the Fiber scheduler ([`async`](https://github.com/socketry/async)):
Expand Down Expand Up @@ -131,7 +131,11 @@ end
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.
(`mysql2`) the next time it is checked out; the call that hit the dead connection still raises. Inside `transaction`
a lost `pg` connection is not reconnected: every later call in the block and `transaction` itself raise
`PG::ConnectionBad`, so nothing after the loss runs outside the transaction (the server rolled it back). A `mysql2`
client that mysql2 closed because a running query was interrupted (an `Async` task stopped, `Thread#raise`) is
replaced the same way, without failing the next call.
* SQLite: threads and fibers sharing one `SQLite3::Database` take turns; a transaction holds the database until it ends.

## Supported features
Expand Down Expand Up @@ -191,6 +195,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`.
* 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: sqlc does not see parameters in `ON CONFLICT ... DO UPDATE SET` (it leaves `sqlc.arg(...)` in the SQL or
reports fewer parameters than there are `?`), so such queries are generation errors. Use `excluded.<column>`
(`DO UPDATE SET n = counters.n + excluded.n`) or move the value into `VALUES`.
* SQLite transactions belong to the thread that opened them; fibers sharing the same database take turns, and a
transaction holds it until it ends.
* Not applicable to Ruby (they exist in the C# plugin): `withAsyncSuffix` / `withCancellationToken` (use the Fiber
Expand Down
3 changes: 2 additions & 1 deletion docs/Coverage.md
Original file line number Diff line number Diff line change
Expand Up @@ -21,7 +21,7 @@ only when the behavior is rejected or decided during generation and generated co
| MySQL unsigned integers, BIT, YEAR, DATETIME/TIME, JSON, enums/SET and client quoting | not MySQL | [`test_client_side_quoting_round_trips_supported_values`](../tests/end2end_mysql2.rb#test_client_side_quoting_round_trips_supported_values), [`test_time_values_match_mysql2_prepared_statement_storage`](../tests/end2end_mysql2.rb#test_time_values_match_mysql2_prepared_statement_storage), [`test_declared_types_match_runtime_values`](../tests/end2end_mysql2.rb#test_declared_types_match_runtime_values) | not MySQL | [`test_mysql_uses_client_side_binding_and_sqlite_closes_prepared_statements`](../test/features_test.rb#test_mysql_uses_client_side_binding_and_sqlite_closes_prepared_statements) |
| SQLite affinity, JSON/UUID, and NUMERIC integer/float results | not SQLite | not SQLite | [`test_declared_types_match_runtime_values`](../tests/end2end_sqlite3.rb#test_declared_types_match_runtime_values), [`test_declared_types_with_parameters`](../tests/end2end_sqlite3.rb#test_declared_types_with_parameters) | [`test_sqlite_declared_types_follow_type_affinity`](../test/features_test.rb#test_sqlite_declared_types_follow_type_affinity), [`test_sqlite_json_and_uuid_declarations_are_strings`](../test/features_test.rb#test_sqlite_json_and_uuid_declarations_are_strings) |
| Names: keywords, method collisions, acronyms, quoted mixed case, repeated `$n`, plural models | [`test_keyword_and_method_names_keep_distinct_row_and_argument_names`](../tests/end2end_pg.rb#test_keyword_and_method_names_keep_distinct_row_and_argument_names), [`test_repeated_column_parameters_get_distinct_names`](../tests/end2end_pg.rb#test_repeated_column_parameters_get_distinct_names) | [`test_keyword_and_method_names_keep_distinct_row_and_argument_names`](../tests/end2end_mysql2.rb#test_keyword_and_method_names_keep_distinct_row_and_argument_names), [`test_repeated_positional_parameters_get_distinct_names`](../tests/end2end_mysql2.rb#test_repeated_positional_parameters_get_distinct_names) | [`test_keyword_and_method_names_keep_distinct_row_and_argument_names`](../tests/end2end_sqlite3.rb#test_keyword_and_method_names_keep_distinct_row_and_argument_names) | [`test_snake_case_keeps_acronyms_together`](../test/features_test.rb#test_snake_case_keeps_acronyms_together), [`test_query_names_cannot_replace_generated_methods`](../test/features_test.rb#test_query_names_cannot_replace_generated_methods), [`test_tables_with_the_same_singular_get_distinct_models`](../test/features_test.rb#test_tables_with_the_same_singular_get_distinct_models) |
| Connections, injected pools, reconnect behavior and SQLite serialization | [`test_queries_can_share_an_injected_pool`](../tests/end2end_pg.rb#test_queries_can_share_an_injected_pool), [`test_a_connection_closed_by_the_server_is_reconnected`](../tests/end2end_pg.rb#test_a_connection_closed_by_the_server_is_reconnected) | [`test_queries_can_share_an_injected_pool`](../tests/end2end_mysql2.rb#test_queries_can_share_an_injected_pool), [`test_a_connection_closed_by_the_server_is_replaced`](../tests/end2end_mysql2.rb#test_a_connection_closed_by_the_server_is_replaced) | [`test_fibers_sharing_a_database_take_turns`](../tests/end2end_sqlite3.rb#test_fibers_sharing_a_database_take_turns), [`test_threads_sharing_a_database_take_turns_in_transactions`](../tests/end2end_sqlite3.rb#test_threads_sharing_a_database_take_turns_in_transactions) | [`test_engine_must_match_the_driver`](../test/features_test.rb#test_engine_must_match_the_driver) |
| Connections, injected pools, reconnect behavior and SQLite serialization | [`test_queries_can_share_an_injected_pool`](../tests/end2end_pg.rb#test_queries_can_share_an_injected_pool), [`test_a_connection_closed_by_the_server_is_reconnected`](../tests/end2end_pg.rb#test_a_connection_closed_by_the_server_is_reconnected), [`test_connection_lost_inside_transaction_is_not_reconnected_until_the_transaction_ends`](../tests/end2end_pg.rb#test_connection_lost_inside_transaction_is_not_reconnected_until_the_transaction_ends) | [`test_queries_can_share_an_injected_pool`](../tests/end2end_mysql2.rb#test_queries_can_share_an_injected_pool), [`test_a_connection_closed_by_the_server_is_replaced`](../tests/end2end_mysql2.rb#test_a_connection_closed_by_the_server_is_replaced), [`test_a_stopped_async_task_does_not_leave_a_closed_connection_in_the_pool`](../tests/end2end_mysql2.rb#test_a_stopped_async_task_does_not_leave_a_closed_connection_in_the_pool) | [`test_fibers_sharing_a_database_take_turns`](../tests/end2end_sqlite3.rb#test_fibers_sharing_a_database_take_turns), [`test_threads_sharing_a_database_take_turns_in_transactions`](../tests/end2end_sqlite3.rb#test_threads_sharing_a_database_take_turns_in_transactions), [`test_a_database_returning_hash_rows_still_builds_rows`](../tests/end2end_sqlite3.rb#test_a_database_returning_hash_rows_still_builds_rows) | [`test_engine_must_match_the_driver`](../test/features_test.rb#test_engine_must_match_the_driver) |
| Transaction result, commit/rollback, nested savepoint, exit semantics and rescued PostgreSQL error | [`test_nested_savepoint_rolls_back_while_outer_transaction_commits`](../tests/end2end_pg.rb#test_nested_savepoint_rolls_back_while_outer_transaction_commits), [`test_transaction_nonlocal_exit_semantics_follow_timeout_version`](../tests/end2end_pg.rb#test_transaction_nonlocal_exit_semantics_follow_timeout_version), [`test_transaction_whose_failed_statement_was_rescued_raises_instead_of_committing`](../tests/end2end_pg.rb#test_transaction_whose_failed_statement_was_rescued_raises_instead_of_committing) | [`test_nested_savepoint_rolls_back_while_outer_transaction_commits`](../tests/end2end_mysql2.rb#test_nested_savepoint_rolls_back_while_outer_transaction_commits), [`test_transaction_nonlocal_exit_semantics_follow_timeout_version`](../tests/end2end_mysql2.rb#test_transaction_nonlocal_exit_semantics_follow_timeout_version) | [`test_nested_transaction_rolls_back_to_a_savepoint`](../tests/end2end_sqlite3.rb#test_nested_transaction_rolls_back_to_a_savepoint), [`test_transaction_nonlocal_exit_semantics_follow_timeout_version`](../tests/end2end_sqlite3.rb#test_transaction_nonlocal_exit_semantics_follow_timeout_version) | [`test_nested_transactions_reject_isolation_and_mode`](../test/features_test.rb#test_nested_transactions_reject_isolation_and_mode) |
| Isolation applied; invalid isolation/mode rejected before begin; SQLite locks | [`test_transaction_isolation_level`](../tests/end2end_pg.rb#test_transaction_isolation_level) | [`test_transaction_isolation_level_is_applied`](../tests/end2end_mysql2.rb#test_transaction_isolation_level_is_applied) | [`test_transaction_mode`](../tests/end2end_sqlite3.rb#test_transaction_mode), [`test_immediate_and_exclusive_transactions_block_a_second_connection`](../tests/end2end_sqlite3.rb#test_immediate_and_exclusive_transactions_block_a_second_connection), [`test_unknown_transaction_mode_is_rejected_before_beginning`](../tests/end2end_sqlite3.rb#test_unknown_transaction_mode_is_rejected_before_beginning) | [`test_nested_transactions_reject_isolation_and_mode`](../test/features_test.rb#test_nested_transactions_reject_isolation_and_mode) |
| Logger debug format, transaction logs, secret-free parameters | [`test_logger_receives_query_names_and_durations`](../tests/end2end_pg.rb#test_logger_receives_query_names_and_durations) | [`test_logger_receives_query_names_and_durations`](../tests/end2end_mysql2.rb#test_logger_receives_query_names_and_durations) | [`test_logger_receives_query_names_and_durations`](../tests/end2end_sqlite3.rb#test_logger_receives_query_names_and_durations) | [`test_logger_support`](../test/features_test.rb#test_logger_support) |
Expand All @@ -33,5 +33,6 @@ only when the behavior is rejected or decided during generation and generated co
| A transaction ended by the database keeps the original error; a query abandoned by Timeout is cancelled | [`test_query_abandoned_by_a_timeout_is_cancelled`](../tests/end2end_pg.rb#test_query_abandoned_by_a_timeout_is_cancelled) | [`test_deadlock_in_a_nested_transaction_raises_the_deadlock_error`](../tests/end2end_mysql2.rb#test_deadlock_in_a_nested_transaction_raises_the_deadlock_error) | [`test_conflict_that_ends_the_transaction_raises_the_constraint_error`](../tests/end2end_sqlite3.rb#test_conflict_that_ends_the_transaction_raises_the_constraint_error) | |
| Top-level Float JSON parameters, Symbol enum values | [`test_top_level_float_json_parameters_round_trip_exactly`](../tests/end2end_pg.rb#test_top_level_float_json_parameters_round_trip_exactly) | [`test_symbol_enum_value_is_bound_as_its_name`](../tests/end2end_mysql2.rb#test_symbol_enum_value_is_bound_as_its_name) | not SQLite (no enums / JSON encoding) | |
| Modules sharing a connection keep separate prepared statements | [`test_generator_options_preserve_runtime_values_and_generate_expected_layouts`](../tests/end2end_options.rb#test_generator_options_preserve_runtime_values_and_generate_expected_layouts) | not MySQL (no server-side prepared statements) | not SQLite (statements are per call) | [`test_statements_are_named_after_the_module_and_query_class`](../test/features_test.rb#test_statements_are_named_after_the_module_and_query_class) |
| SQLite parameters sqlc does not report (`ON CONFLICT ... DO UPDATE SET`) are generation errors | not PostgreSQL | not MySQL | unit: rejected at generation | [`test_sqlite_parameters_sqlc_does_not_report_are_generation_errors`](../test/features_test.rb#test_sqlite_parameters_sqlc_does_not_report_are_generation_errors), [`test_sqlite_placeholders_in_literals_and_comments_are_not_parameters`](../test/features_test.rb#test_sqlite_placeholders_in_literals_and_comments_are_not_parameters) |

CI runs `test/coverage_map_test.rb`, which checks every linked test method exists in its referenced `tests/` or `test/` file.
10 changes: 6 additions & 4 deletions examples/mysql2/query_sql.rb
Original file line number Diff line number Diff line change
Expand Up @@ -503,8 +503,9 @@ def transaction(isolation: nil)
begin
client.query(rollback)
rescue Mysql2::Error
# a deadlock / lock wait timeout already rolled back the transaction: its savepoints are gone
raise unless e.is_a?(Mysql2::Error)
# a deadlock / lock wait timeout already rolled back the transaction: its savepoints are gone;
# an interrupted query closed the connection: the server rolls back
raise unless e.is_a?(Mysql2::Error) || client.closed?
end
raise
ensure
Expand Down Expand Up @@ -1227,8 +1228,9 @@ def with_client(name)
client.extend(ConnectionState) unless client.is_a?(ConnectionState)
begin
yield client
rescue Mysql2::Error
# a connection closed by the server (restart, KILL, wait_timeout) is replaced by the pool
rescue Exception # rubocop:disable Lint/RescueException
# a connection closed by the server (restart, KILL, wait_timeout) or by mysql2 when a running query
# was interrupted (Async task stopped, Thread#raise) is replaced by the pool
@db.discard_current_connection if client.closed? && @db.respond_to?(:discard_current_connection)
raise
end
Expand Down
3 changes: 3 additions & 0 deletions examples/pg/connection.rb
Original file line number Diff line number Diff line change
Expand Up @@ -14,6 +14,9 @@ def prepared_statements
end
@prepared_statements
end

# Set inside #transaction: a lost connection is then not reconnected until the transaction block ends.
attr_accessor :in_transaction
end

ISOLATION_LEVELS = {
Expand Down
2 changes: 2 additions & 0 deletions examples/pg/connection.rbs
Original file line number Diff line number Diff line change
Expand Up @@ -9,6 +9,8 @@ module PgCodegen
@prepared_statements_pid: Integer

def prepared_statements: () -> Set[String]

attr_accessor in_transaction: bool?
end

ISOLATION_LEVELS: Hash[Symbol, String]
Expand Down
19 changes: 16 additions & 3 deletions examples/pg/query_sql.rb
Original file line number Diff line number Diff line change
Expand Up @@ -574,17 +574,25 @@ def transaction(isolation: nil)
else
client.exec("SAVEPOINT #{savepoint}")
end
entered = !client.in_transaction
client.in_transaction = true
rollback = outermost ? 'ROLLBACK' : "ROLLBACK TO SAVEPOINT #{savepoint}"
failed = false
begin
yield self
rescue Exception # rubocop:disable Lint/RescueException
failed = true
client.cancel if client.transaction_status == PG::PQTRANS_ACTIVE
client.exec(rollback)
client.exec(rollback) unless client.status == PG::CONNECTION_BAD # the server already rolled back
raise
ensure
unless failed
client.in_transaction = false if entered
if failed
nil
elsif client.status == PG::CONNECTION_BAD
# the block rescued the error of a lost connection: the server rolled the transaction back
raise PG::ConnectionBad, 'transaction rolled back: the connection was lost' if Thread.current.status != 'aborting'
else
# a statement failed and the block rescued the error: PostgreSQL can only roll back
rescued = client.transaction_status == PG::PQTRANS_INERROR
commit = outermost ? 'COMMIT' : "RELEASE SAVEPOINT #{savepoint}"
Expand Down Expand Up @@ -1646,7 +1654,12 @@ def instrument(name)
def with_client(name)
instrument(name) do
@db.with do |client|
client.reset if client.status == PG::CONNECTION_BAD # closed by the server (restart, failover, killed)
if client.status == PG::CONNECTION_BAD # closed by the server (restart, failover, killed)
# inside #transaction a new session would run the rest of the block outside the transaction
raise PG::ConnectionBad, 'the connection was lost inside a transaction' if client.is_a?(ConnectionState) && client.in_transaction

client.reset
end
configure(client) unless client.is_a?(ConnectionState)
begin
yield client
Expand Down
Loading
Loading