spanner_postgresql_identifier_case_sensitivity project_id:, instance_id:, database_id:

in spanner/spanner_postgresql_identifier_case_sensitivity.rb [18:99]

• 47 lines of code
• 4 McCabe index (conditional complexity)

def spanner_postgresql_identifier_case_sensitivity project_id:, instance_id:, database_id:
  
  
  

  db_admin_client = Google::Cloud::Spanner::Admin::Database.database_admin project: project_id

  db_path = db_admin_client.database_path project: project_id,
                                          instance: instance_id,
                                          database: database_id

  
  
  
  
  create_concerts_query = <<~QUERY
    CREATE TABLE Concerts (
      ConcertId bigint NOT NULL PRIMARY KEY,
      \"Location\" varchar(1024) NOT NULL,
      \"Time\"  timestamptz NOT NULL
    )
  QUERY


  job = db_admin_client.update_database_ddl database: db_path,
                                            statements: [create_concerts_query]

  job.wait_until_done!

  if job.error?
    puts "Error while updating database. Code: 
    raise GRPC::BadStatus.new(job.error.code, job.error.message)
  end

  puts "Created table with case sensitive names in database #{database_id} using PostgreSQL dialect."

  spanner = Google::Cloud::Spanner.new project: project_id
  client  = spanner.client instance_id, database_id

  
  
  
  client.commit do |c|
    c.insert "Concerts", [
      { ConcertId: 1, Location: "Venue 1", Time: Time.utc(2022, "Mar", 11) }
    ]
  end

  results = client.execute "SELECT * FROM Concerts"
  results.rows.each do |row|
    
    
    puts "ConcertId: #{row[:concertid]}"

    
    
    puts "Location: #{row[:Location]}"
    puts "Time: #{row[:Time]}"
  end

  
  
  results = client.execute "SELECT concertid AS \"ConcertId\", \"Location\" AS \"venue\", \"Time\" FROM Concerts"
  results.rows.each do |row|
    
    puts "ConcertId (double quoted alias): #{row[:ConcertId]}"
    puts "Location (double quoted alias): #{row[:venue]}"
    puts "Time (double quoted): #{row[:Time]}"
  end

  
  
  sql_query = "INSERT INTO Concerts (ConcertId, \"Location\", \"Time\") VALUES($1, $2, $3)"
  params = { p1: 2, p2: "Venue 2", p3: Time.utc(2022, "Mar", 11) }
  row_count = nil
  client.transaction do |transaction|
    row_count = transaction.execute_update sql_query, params: params
  end

  puts "Inserted #{row_count} row(s)"
end