Total hours between start and end time - Google Spreadsheet

Tagged time report, format, google, spreadsheet, time  Languages 

I want to extract the total hours worked from a Google spreadsheet.

Given the following data in a Google spreadsheet:

  • End time in column D using time format
  • Start time in column C using time format

For example:

start,end,hours
10:00,12:00,2
9:00,12:00,3

To achieve this in a Google spreadsheet we can use the following formula:

=ARRAYFORMULA((HOUR(D496-C496))+(MINUTE((D496-C496)))/60)

Reference: https://infoinspired.com/google-docs/spreadsheet/payroll-hours-time-calculation-in-google-sheets/

"ERROR: permission denied for schema public" WTF

Tagged postgresql, grant, wtf  Languages sql

If you get this error:

ERROR:  permission denied for schema history

And, you’re running a query such as, for example, this one:

SELECT 1 FROM ONLY "history"."dummytafvel" x WHERE "id" OPERATOR(pg_catalog.=) $1 FOR KEY SHARE OF x;

And, your user has been granted access to the schema:

GRANT ALL ON history TO myself_and_i;

Then, you might have been hit over the head by the “permission denied for schema even as superuser” feature in PostgreSQL:

That’s a foreign key checking query.FK checks are done as the owner of the target table, not as the user who did the original query. So your problem is that the owner of the table lacks permissions to access the other table (or more specifically, the schema it’s in).

The solution is to grant access to the owner of the foreign keys to the schema in question.

References

https://sharingtechknowledge.blogspot.com/2012/03/postgresql-foreign-key-checking.html

RabbitMQ Ruby client example (AMQP)

Tagged bunny, thread-safe, ruby, rabbitmq, amqp  Languages ruby
require 'bunny'

# See http://rubybunny.info/articles/concurrency.html
class AMQP
  # Bunny::Session
  def self.session
    Thread.current[:bunny_session] ||= Bunny.new(uri).start
  end
  class << self
    alias connect session
  end

  # Bunny::Channel
  def self.channel
    Thread.current[:bunny_channel] ||= session.create_channel
  end

  # Bunny::Exchange
  def self.exchange
    channel.topic(EXCHANGE, auto_delete: false, durable: true)
  end

  def self.publish(message, queue:)
    exchange.publish(message, routing_key: queue)
  end

  def self.disconnect
    session&.close
  end

  # amqp://user:pass@localhost:5672
  def self.uri
    ENV.fetch('AMQP_URI')
  end
end
  • All Bunny methods are not thread safe, e.g., channel
  • Call AMQP.connect/disconnect in Puma/Unicorn/Sneakers/Sidekiq’s after/before_fork callbacks.

Escaping strings in PostgreSQL queries

Tagged postgres, escape, sql  Languages sql

PostgreSQL queries containing, for example, single quotes or semicolons need to be escaped.

This won’t work:

UPDATE jobs SET work=':'';

To make it work, escape the strings using C-style escapes (E’’) and replacing single-quotes with ‘’:

UPDATE jobs SET work=E':''';

See section “4.1.2.2. String Constants with C-style Escapes” in the PostgreSQL documentation https://www.postgresql.org/docs/10/sql-syntax-lexical.html for details.

How to use private Github repositories with Bundler

Tagged repository, git, bundler, github, private  Languages bash

Does your Gemfile reference private Github repositories?

Option 1: ENV variable

export BUNDLE_GITHUB__COM=username:password
export BUNDLE_GITHUB__COM=<personal-oauth-token>:x-oauth-basic

Option 2: bundle config

bundle config https://github.com/bundler/bundler.git username:password

Option 3: ~/.git-credentials

echo 'https://user:pass@example.com' >> ~/.git-credentials

References https://github.com/rubygems/bundler/pull/3898 https://git-scm.com/docs/git-credential-store

RabbitMQ Publisher Confirms with Bunny

Tagged publisher, rabbitmq, bunny, confirms  Languages ruby

Source https://github.com/ruby-amqp/bunny/blob/master/examples/guides/extensions/publisher_confirms.rb:

require "bunny"

puts "=> Demonstrating publisher confirms"
puts

conn = Bunny.new
conn.start

ch   = conn.create_channel
x    = ch.fanout("amq.fanout")
q    = ch.queue("", :exclusive => true).bind(x)

ch.confirm_select
1000.times do
  x.publish("")
end
ch.wait_for_confirms # blocks calling thread until all acks are received

sleep 0.2
puts "Received acks for all published messages. #{q.name} now has #{q.message_count} messages."

sleep 0.7
puts "Disconnecting..."
conn.close

How to call JavaScript code from Ruby

Tagged execjs, therubyracer, ruby, javascript  Languages js, ruby, javascript

Gemfile:

source 'https://rubygems.org'
gem "therubyracer"
# commonjs.rb provides 'require' needed to import modules
gem 'commonjs'

add_with_javascript.rb:

require 'v8'
require 'commonjs'
env = CommonJS::Environment.new(V8::Context.new, path: './node_modules')
env.require('add.js').add(2, 2)

node_modules/add.js:

function add(a, b) {
  // Require works, if the package is available in node_modules
  // require('xyz');
  // Console is not available by default
  // console.log(msg);
  return a + b;
}

exports.add = add;
$ bundle exec ruby add_with_javascript.rb

Tested with:

  • commonjs (0.2.7)
  • libv8 (3.16.14.19)
  • therubyracer (0.12.3)

Set the accept-language header in Capybara / Selenium WebDriver tests

Tagged ruby, capybara, selenium  Languages ruby

To change the accept-language header that Chrome sends with each request in Capybara tests, add the ‘selenium-webdriver (3.142.6)’ preference to the driver’s options:

  opts.add_preference('intl.accept_languages', 'sv')

Example:

Capybara.register_driver :chrome do |app|
  caps = Selenium::WebDriver::Remote::Capabilities.chrome(loggingPrefs: { browser: 'ALL' })
  opts = Selenium::WebDriver::Chrome::Options.new
  opts.add_preference('intl.accept_languages', 'sv')
  chrome_args = %w[--no-sandbox --disable-popup-blocking --enable-features=NetworkService,NetworkServiceInProcess --window-size=1920,1080]
  chrome_args.each { |arg| opts.add_argument(arg) }
  Capybara::Selenium::Driver.new(app, browser: :chrome, options: opts, desired_capabilities: caps)
end

Tested with:

  • capybara (3.30.3)
  • selenium-webdriver (3.142.6)