Cloud Spanner API - Class Google::Cloud::Spanner::Transaction (v2.24.0)

Example

require "google/cloud/spanner"

spanner = Google::Cloud::Spanner.new
db = spanner.client "my-instance", "my-database"

db.transaction do |tx|
  # Read the second album budget.
  second_album_result = tx.read "Albums", ["marketing_budget"],
                                keys: [[2, 2]], limit: 1
  second_album_row = second_album_result.rows.first
  second_album_budget = second_album_row.values.first

  transfer_amount = 200000

  if second_album_budget < 300000
    # Raising an exception will automatically roll back the
    # transaction.
    raise "The second album doesn't have enough funds to transfer"
  end

  # Read the first album's budget.
  first_album_result = tx.read "Albums", ["marketing_budget"],
                                keys: [[1, 1]], limit: 1
  first_album_row = first_album_result.rows.first
  first_album_budget = first_album_row.values.first

  # Update the budgets.
  second_album_budget -= transfer_amount
  first_album_budget += transfer_amount
  puts "Setting first album's budget to #{first_album_budget} and " \
       "the second album's budget to #{second_album_budget}."

  # Update the rows.
  rows = [
    {singer_id: 1, album_id: 1, marketing_budget: first_album_budget},
    {singer_id: 2, album_id: 2, marketing_budget: second_album_budget}
  ]
  tx.update "Albums", rows
end

Methods

#batch_update

def batch_update(request_options: nil, call_options: nil, &block) { |batch_update| ... } -> Array<Integer>

require "google/cloud/spanner"

spanner = Google::Cloud::Spanner.new
db = spanner.client "my-instance", "my-database"

db.transaction do |tx|
  begin
    row_counts = tx.batch_update do |b|
      statement_count = b.batch_update(
        "UPDATE users SET name = 'Charlie' WHERE id = 1"
      )
    end
    puts row_counts.inspect
  rescue Google::Cloud::Spanner::BatchUpdateError => err
    puts err.cause.message
    puts err.row_counts.inspect
  end
end

Update using SQL parameters:

require "google/cloud/spanner"

spanner = Google::Cloud::Spanner.new
db = spanner.client "my-instance", "my-database"

db.transaction do |tx|
  begin
    row_counts = tx.batch_update do |b|
      statement_count = b.batch_update(
        "UPDATE users SET name = 'Charlie' WHERE id = 1",
        params: { id: 1, name: "Charlie" }
      )
    end
    puts row_counts.inspect
  rescue Google::Cloud::Spanner::BatchUpdateError => err
    puts err.cause.message
    puts err.row_counts.inspect
  end
end

#commit_timestamp

def commit_timestamp() -> ColumnValue

require "google/cloud/spanner"

spanner = Google::Cloud::Spanner.new

db = spanner.client "my-instance", "my-database"

db.transaction do |tx|
  tx.insert "users", [
    { id: 5, name: "Murphy", updated_at: tx.commit_timestamp }
  ]
end

#delete

def delete(table, keys = [])

require "google/cloud/spanner"

spanner = Google::Cloud::Spanner.new
db = spanner.client "my-instance", "my-database"

db.transaction { |tx| tx.delete "users", [1, 2, 3] }

#execute

def execute(sql, params: nil, types: nil, query_options: nil, request_options: nil, call_options: nil) -> Google::Cloud::Spanner::Results

require "google/cloud/spanner"

spanner = Google::Cloud::Spanner.new
db = spanner.client "my-instance", "my-database"

db.transaction do |tx|
  results = tx.execute_query "SELECT * FROM users"

  results.rows.each do |row|
    puts "User #{row[:id]} is #{row[:name]}"
  end
end

Query using query parameters:

require "google/cloud/spanner"

spanner = Google::Cloud::Spanner.new
db = spanner.client "my-instance", "my-database"

db.transaction do |tx|
  results = tx.execute_query(
    "SELECT * FROM users WHERE active = @active",
    params: { active: true }
  )

  results.rows.each do |row|
    puts "User #{row[:id]} is #{row[:name]}"
  end
end

Query with a SQL STRUCT query parameter as a Hash:

require "google/cloud/spanner"

spanner = Google::Cloud::Spanner.new
db = spanner.client "my-instance", "my-database"

db.transaction do |tx|
  user_hash = { id: 1, name: "Charlie", active: false }

  results = tx.execute_query(
    "SELECT * FROM users WHERE " \
    "ID = @user_struct.id " \
    "AND name = @user_struct.name " \
    "AND active = @user_struct.active",
    params: { user_struct: user_hash }
  )

  results.rows.each do |row|
    puts "User #{row[:id]} is #{row[:name]}"
  end
end

Specify the SQL STRUCT type using Fields object:

require "google/cloud/spanner"

spanner = Google::Cloud::Spanner.new
db = spanner.client "my-instance", "my-database"

db.transaction do |tx|
  user_type = tx.fields id: :INT64, name: :STRING, active: :BOOL
  user_hash = { id: 1, name: nil, active: false }

  results = tx.execute_query(
    "SELECT * FROM users WHERE " \
    "ID = @user_struct.id " \
    "AND name = @user_struct.name " \
    "AND active = @user_struct.active",
    params: { user_struct: user_hash },
    types: { user_struct: user_type }
  )

  results.rows.each do |row|
    puts "User #{row[:id]} is #{row[:name]}"
  end
end

Or, query with a SQL STRUCT as a typed Data object:

require "google/cloud/spanner"

spanner = Google::Cloud::Spanner.new
db = spanner.client "my-instance", "my-database"

db.transaction do |tx|
  user_type = tx.fields id: :INT64, name: :STRING, active: :BOOL
  user_data = user_type.struct id: 1, name: nil, active: false

  results = tx.execute_query(
    "SELECT * FROM users WHERE " \
    "ID = @user_struct.id " \
    "AND name = @user_struct.name " \
    "AND active = @user_struct.active",
    params: { user_struct: user_data }
  )

  results.rows.each do |row|
    puts "User #{row[:id]} is #{row[:name]}"
  end
end

Query using query options:

require "google/cloud/spanner"

spanner = Google::Cloud::Spanner.new
db = spanner.client "my-instance", "my-database"

db.transaction do |tx|
  results = tx.execute_query \
    "SELECT * FROM users", query_options: {
    optimizer_version: "1",
    optimizer_statistics_package: "auto_20191128_14_47_22UTC"
  }

  results.rows.each do |row|
    puts "User #{row[:id]} is #{row[:name]}"
  end
end

Query using custom timeout and retry policy:

require "google/cloud/spanner"

spanner = Google::Cloud::Spanner.new
db = spanner.client "my-instance", "my-database"

timeout = 30.0
retry_policy = {
  initial_delay: 0.25,
  max_delay:     32.0,
  multiplier:    1.3,
  retry_codes:   ["UNAVAILABLE"]
}
call_options = { timeout: timeout, retry_policy: retry_policy }

db.transaction do |tx|
  results = tx.execute_query \
    "SELECT * FROM users", call_options: call_options

  results.rows.each do |row|
    puts "User #{row[:id]} is #{row[:name]}"
  end
end

#execute_query

def execute_query(sql, params: nil, types: nil, query_options: nil, request_options: nil, call_options: nil) -> Google::Cloud::Spanner::Results

require "google/cloud/spanner"

spanner = Google::Cloud::Spanner.new
db = spanner.client "my-instance", "my-database"

db.transaction do |tx|
  results = tx.execute_query "SELECT * FROM users"

  results.rows.each do |row|
    puts "User #{row[:id]} is #{row[:name]}"
  end
end

Query using query parameters:

require "google/cloud/spanner"

spanner = Google::Cloud::Spanner.new
db = spanner.client "my-instance", "my-database"

db.transaction do |tx|
  results = tx.execute_query(
    "SELECT * FROM users WHERE active = @active",
    params: { active: true }
  )

  results.rows.each do |row|
    puts "User #{row[:id]} is #{row[:name]}"
  end
end

Query with a SQL STRUCT query parameter as a Hash:

require "google/cloud/spanner"

spanner = Google::Cloud::Spanner.new
db = spanner.client "my-instance", "my-database"

db.transaction do |tx|
  user_hash = { id: 1, name: "Charlie", active: false }

  results = tx.execute_query(
    "SELECT * FROM users WHERE " \
    "ID = @user_struct.id " \
    "AND name = @user_struct.name " \
    "AND active = @user_struct.active",
    params: { user_struct: user_hash }
  )

  results.rows.each do |row|
    puts "User #{row[:id]} is #{row[:name]}"
  end
end

Specify the SQL STRUCT type using Fields object:

require "google/cloud/spanner"

spanner = Google::Cloud::Spanner.new
db = spanner.client "my-instance", "my-database"

db.transaction do |tx|
  user_type = tx.fields id: :INT64, name: :STRING, active: :BOOL
  user_hash = { id: 1, name: nil, active: false }

  results = tx.execute_query(
    "SELECT * FROM users WHERE " \
    "ID = @user_struct.id " \
    "AND name = @user_struct.name " \
    "AND active = @user_struct.active",
    params: { user_struct: user_hash },
    types: { user_struct: user_type }
  )

  results.rows.each do |row|
    puts "User #{row[:id]} is #{row[:name]}"
  end
end

Or, query with a SQL STRUCT as a typed Data object:

require "google/cloud/spanner"

spanner = Google::Cloud::Spanner.new
db = spanner.client "my-instance", "my-database"

db.transaction do |tx|
  user_type = tx.fields id: :INT64, name: :STRING, active: :BOOL
  user_data = user_type.struct id: 1, name: nil, active: false

  results = tx.execute_query(
    "SELECT * FROM users WHERE " \
    "ID = @user_struct.id " \
    "AND name = @user_struct.name " \
    "AND active = @user_struct.active",
    params: { user_struct: user_data }
  )

  results.rows.each do |row|
    puts "User #{row[:id]} is #{row[:name]}"
  end
end

Query using query options:

require "google/cloud/spanner"

spanner = Google::Cloud::Spanner.new
db = spanner.client "my-instance", "my-database"

db.transaction do |tx|
  results = tx.execute_query \
    "SELECT * FROM users", query_options: {
    optimizer_version: "1",
    optimizer_statistics_package: "auto_20191128_14_47_22UTC"
  }

  results.rows.each do |row|
    puts "User #{row[:id]} is #{row[:name]}"
  end
end

Query using custom timeout and retry policy:

require "google/cloud/spanner"

spanner = Google::Cloud::Spanner.new
db = spanner.client "my-instance", "my-database"

timeout = 30.0
retry_policy = {
  initial_delay: 0.25,
  max_delay:     32.0,
  multiplier:    1.3,
  retry_codes:   ["UNAVAILABLE"]
}
call_options = { timeout: timeout, retry_policy: retry_policy }

db.transaction do |tx|
  results = tx.execute_query \
    "SELECT * FROM users", call_options: call_options

  results.rows.each do |row|
    puts "User #{row[:id]} is #{row[:name]}"
  end
end

#execute_sql

def execute_sql(sql, params: nil, types: nil, query_options: nil, request_options: nil, call_options: nil) -> Google::Cloud::Spanner::Results

require "google/cloud/spanner"

spanner = Google::Cloud::Spanner.new
db = spanner.client "my-instance", "my-database"

db.transaction do |tx|
  results = tx.execute_query "SELECT * FROM users"

  results.rows.each do |row|
    puts "User #{row[:id]} is #{row[:name]}"
  end
end

Query using query parameters:

require "google/cloud/spanner"

spanner = Google::Cloud::Spanner.new
db = spanner.client "my-instance", "my-database"

db.transaction do |tx|
  results = tx.execute_query(
    "SELECT * FROM users WHERE active = @active",
    params: { active: true }
  )

  results.rows.each do |row|
    puts "User #{row[:id]} is #{row[:name]}"
  end
end

Query with a SQL STRUCT query parameter as a Hash:

require "google/cloud/spanner"

spanner = Google::Cloud::Spanner.new
db = spanner.client "my-instance", "my-database"

db.transaction do |tx|
  user_hash = { id: 1, name: "Charlie", active: false }

  results = tx.execute_query(
    "SELECT * FROM users WHERE " \
    "ID = @user_struct.id " \
    "AND name = @user_struct.name " \
    "AND active = @user_struct.active",
    params: { user_struct: user_hash }
  )

  results.rows.each do |row|
    puts "User #{row[:id]} is #{row[:name]}"
  end
end

Specify the SQL STRUCT type using Fields object:

require "google/cloud/spanner"

spanner = Google::Cloud::Spanner.new
db = spanner.client "my-instance", "my-database"

db.transaction do |tx|
  user_type = tx.fields id: :INT64, name: :STRING, active: :BOOL
  user_hash = { id: 1, name: nil, active: false }

  results = tx.execute_query(
    "SELECT * FROM users WHERE " \
    "ID = @user_struct.id " \
    "AND name = @user_struct.name " \
    "AND active = @user_struct.active",
    params: { user_struct: user_hash },
    types: { user_struct: user_type }
  )

  results.rows.each do |row|
    puts "User #{row[:id]} is #{row[:name]}"
  end
end

Or, query with a SQL STRUCT as a typed Data object:

require "google/cloud/spanner"

spanner = Google::Cloud::Spanner.new
db = spanner.client "my-instance", "my-database"

db.transaction do |tx|
  user_type = tx.fields id: :INT64, name: :STRING, active: :BOOL
  user_data = user_type.struct id: 1, name: nil, active: false

  results = tx.execute_query(
    "SELECT * FROM users WHERE " \
    "ID = @user_struct.id " \
    "AND name = @user_struct.name " \
    "AND active = @user_struct.active",
    params: { user_struct: user_data }
  )

  results.rows.each do |row|
    puts "User #{row[:id]} is #{row[:name]}"
  end
end

Query using query options:

require "google/cloud/spanner"

spanner = Google::Cloud::Spanner.new
db = spanner.client "my-instance", "my-database"

db.transaction do |tx|
  results = tx.execute_query \
    "SELECT * FROM users", query_options: {
    optimizer_version: "1",
    optimizer_statistics_package: "auto_20191128_14_47_22UTC"
  }

  results.rows.each do |row|
    puts "User #{row[:id]} is #{row[:name]}"
  end
end

Query using custom timeout and retry policy:

require "google/cloud/spanner"

spanner = Google::Cloud::Spanner.new
db = spanner.client "my-instance", "my-database"

timeout = 30.0
retry_policy = {
  initial_delay: 0.25,
  max_delay:     32.0,
  multiplier:    1.3,
  retry_codes:   ["UNAVAILABLE"]
}
call_options = { timeout: timeout, retry_policy: retry_policy }

db.transaction do |tx|
  results = tx.execute_query \
    "SELECT * FROM users", call_options: call_options

  results.rows.each do |row|
    puts "User #{row[:id]} is #{row[:name]}"
  end
end

#execute_update

def execute_update(sql, params: nil, types: nil, query_options: nil, request_options: nil, call_options: nil) -> Integer

require "google/cloud/spanner"

spanner = Google::Cloud::Spanner.new
db = spanner.client "my-instance", "my-database"

db.transaction do |tx|
  row_count = tx.execute_update(
    "UPDATE users SET name = 'Charlie' WHERE id = 1"
  )
end

Update using SQL parameters:

require "google/cloud/spanner"

spanner = Google::Cloud::Spanner.new
db = spanner.client "my-instance", "my-database"

db.transaction do |tx|
  row_count = tx.execute_update(
    "UPDATE users SET name = @name WHERE id = @id",
    params: { id: 1, name: "Charlie" }
  )
end

Update using query options

require "google/cloud/spanner"

spanner = Google::Cloud::Spanner.new
db = spanner.client "my-instance", "my-database"

db.transaction do |tx|
  row_count = tx.execute_update(
    "UPDATE users SET name = 'Charlie' WHERE id = 1",
    query_options: {
      optimizer_version: "1",
      optimizer_statistics_package: "auto_20191128_14_47_22UTC"
    }
  )
end

Update using custom timeout and retry policy:

require "google/cloud/spanner"

spanner = Google::Cloud::Spanner.new
db = spanner.client "my-instance", "my-database"

timeout = 30.0
retry_policy = {
  initial_delay: 0.25,
  max_delay:     32.0,
  multiplier:    1.3,
  retry_codes:   ["UNAVAILABLE"]
}
call_options = { timeout: timeout, retry_policy: retry_policy }

db.transaction do |tx|
  row_count = tx.execute_update(
    "UPDATE users SET name = 'Charlie' WHERE id = 1",
    call_options: call_options
  )
end

#fields

def fields(types) -> Fields

Create a STRUCT value with named fields using Fields object:

require "google/cloud/spanner"

spanner = Google::Cloud::Spanner.new
db = spanner.client "my-instance", "my-database"

db.transaction do |tx|
  named_type = tx.fields(
    { id: :INT64, name: :STRING, active: :BOOL }
  )
  named_data = named_type.struct(
    { id: 42, name: nil, active: false }
  )
end

Create a STRUCT value with anonymous field names:

require "google/cloud/spanner"

spanner = Google::Cloud::Spanner.new
db = spanner.client "my-instance", "my-database"

db.transaction do |tx|
  anon_type = tx.fields [:INT64, :STRING, :BOOL]
  anon_data = anon_type.struct [42, nil, false]
end

Create a STRUCT value with duplicate field names:

require "google/cloud/spanner"

spanner = Google::Cloud::Spanner.new
db = spanner.client "my-instance", "my-database"

db.transaction do |tx|
  dup_type = tx.fields [[:x, :INT64], [:x, :STRING], [:x, :BOOL]]
  dup_data = dup_type.struct [42, nil, false]
end

#initialize

def initialize() -> Transaction

#insert

def insert(table, *rows)

require "google/cloud/spanner"

spanner = Google::Cloud::Spanner.new
db = spanner.client "my-instance", "my-database"

db.transaction do |tx|
  tx.insert "users", [{ id: 1, name: "Charlie", active: false },
                      { id: 2, name: "Harvey",  active: true }]
end

#query

def query(sql, params: nil, types: nil, query_options: nil, request_options: nil, call_options: nil) -> Google::Cloud::Spanner::Results

require "google/cloud/spanner"

spanner = Google::Cloud::Spanner.new
db = spanner.client "my-instance", "my-database"

db.transaction do |tx|
  results = tx.execute_query "SELECT * FROM users"

  results.rows.each do |row|
    puts "User #{row[:id]} is #{row[:name]}"
  end
end

Query using query parameters:

require "google/cloud/spanner"

spanner = Google::Cloud::Spanner.new
db = spanner.client "my-instance", "my-database"

db.transaction do |tx|
  results = tx.execute_query(
    "SELECT * FROM users WHERE active = @active",
    params: { active: true }
  )

  results.rows.each do |row|
    puts "User #{row[:id]} is #{row[:name]}"
  end
end

Query with a SQL STRUCT query parameter as a Hash:

require "google/cloud/spanner"

spanner = Google::Cloud::Spanner.new
db = spanner.client "my-instance", "my-database"

db.transaction do |tx|
  user_hash = { id: 1, name: "Charlie", active: false }

  results = tx.execute_query(
    "SELECT * FROM users WHERE " \
    "ID = @user_struct.id " \
    "AND name = @user_struct.name " \
    "AND active = @user_struct.active",
    params: { user_struct: user_hash }
  )

  results.rows.each do |row|
    puts "User #{row[:id]} is #{row[:name]}"
  end
end

Specify the SQL STRUCT type using Fields object:

require "google/cloud/spanner"

spanner = Google::Cloud::Spanner.new
db = spanner.client "my-instance", "my-database"

db.transaction do |tx|
  user_type = tx.fields id: :INT64, name: :STRING, active: :BOOL
  user_hash = { id: 1, name: nil, active: false }

  results = tx.execute_query(
    "SELECT * FROM users WHERE " \
    "ID = @user_struct.id " \
    "AND name = @user_struct.name " \
    "AND active = @user_struct.active",
    params: { user_struct: user_hash },
    types: { user_struct: user_type }
  )

  results.rows.each do |row|
    puts "User #{row[:id]} is #{row[:name]}"
  end
end

Or, query with a SQL STRUCT as a typed Data object:

require "google/cloud/spanner"

spanner = Google::Cloud::Spanner.new
db = spanner.client "my-instance", "my-database"

db.transaction do |tx|
  user_type = tx.fields id: :INT64, name: :STRING, active: :BOOL
  user_data = user_type.struct id: 1, name: nil, active: false

  results = tx.execute_query(
    "SELECT * FROM users WHERE " \
    "ID = @user_struct.id " \
    "AND name = @user_struct.name " \
    "AND active = @user_struct.active",
    params: { user_struct: user_data }
  )

  results.rows.each do |row|
    puts "User #{row[:id]} is #{row[:name]}"
  end
end

Query using query options:

require "google/cloud/spanner"

spanner = Google::Cloud::Spanner.new
db = spanner.client "my-instance", "my-database"

db.transaction do |tx|
  results = tx.execute_query \
    "SELECT * FROM users", query_options: {
    optimizer_version: "1",
    optimizer_statistics_package: "auto_20191128_14_47_22UTC"
  }

  results.rows.each do |row|
    puts "User #{row[:id]} is #{row[:name]}"
  end
end

Query using custom timeout and retry policy:

require "google/cloud/spanner"

spanner = Google::Cloud::Spanner.new
db = spanner.client "my-instance", "my-database"

timeout = 30.0
retry_policy = {
  initial_delay: 0.25,
  max_delay:     32.0,
  multiplier:    1.3,
  retry_codes:   ["UNAVAILABLE"]
}
call_options = { timeout: timeout, retry_policy: retry_policy }

db.transaction do |tx|
  results = tx.execute_query \
    "SELECT * FROM users", call_options: call_options

  results.rows.each do |row|
    puts "User #{row[:id]} is #{row[:name]}"
  end
end

#range

def range(beginning, ending, exclude_begin: false, exclude_end: false) -> Google::Cloud::Spanner::Range

require "google/cloud/spanner"

spanner = Google::Cloud::Spanner.new
db = spanner.client "my-instance", "my-database"

db.transaction do |tx|
  key_range = tx.range 1, 100
  results = tx.read "users", [:id, :name], keys: key_range

  results.rows.each do |row|
    puts "User #{row[:id]} is #{row[:name]}"
  end
end

#read

def read(table, columns, keys: nil, index: nil, limit: nil, request_options: nil, call_options: nil) -> Google::Cloud::Spanner::Results

require "google/cloud/spanner"

spanner = Google::Cloud::Spanner.new
db = spanner.client "my-instance", "my-database"

db.transaction do |tx|
  results = tx.read "users", [:id, :name]

  results.rows.each do |row|
    puts "User #{row[:id]} is #{row[:name]}"
  end
end

#replace

def replace(table, *rows)

require "google/cloud/spanner"

spanner = Google::Cloud::Spanner.new
db = spanner.client "my-instance", "my-database"

db.transaction do |tx|
  tx.replace "users", [{ id: 1, name: "Charlie", active: false },
                       { id: 2, name: "Harvey",  active: true }]
end

#save

def save(table, *rows)

require "google/cloud/spanner"

spanner = Google::Cloud::Spanner.new
db = spanner.client "my-instance", "my-database"

db.transaction do |tx|
  tx.upsert "users", [{ id: 1, name: "Charlie", active: false },
                      { id: 2, name: "Harvey",  active: true }]
end

#transaction_id

def transaction_id() -> String

#update

def update(table, *rows)

require "google/cloud/spanner"

spanner = Google::Cloud::Spanner.new
db = spanner.client "my-instance", "my-database"

db.transaction do |tx|
  tx.update "users", [{ id: 1, name: "Charlie", active: false },
                      { id: 2, name: "Harvey",  active: true }]
end

#upsert

def upsert(table, *rows)

require "google/cloud/spanner"

spanner = Google::Cloud::Spanner.new
db = spanner.client "my-instance", "my-database"

db.transaction do |tx|
  tx.upsert "users", [{ id: 1, name: "Charlie", active: false },
                      { id: 2, name: "Harvey",  active: true }]
end