Idempotency with Rails and Turbolinks

Tagged idempotency, turbolinks, d3.js  Languages ruby

To achieve idempotency with Rails and Turbolinks you have many options including these:

  • Option 1: Destroy DOM elements

Destroy existing elements that were created on the first page load. This works well with most libraries, including D3.js.

The is also the easiest to implement if you put everything under one root DOM element.

  • Option 2: Set a flag

Set a flag. Check if the flag is set before running the script. Note that you will have to use delegated event listeners or attach event listeners again. This is hard to do with, for example, D3.js.

document.addEventListener("turbolinks:load", function() {
  $('.linechart').each(function(_ix, el) {
    // Option 1: Destroy existing elements that were created on the first page load
    el = $(el);
    el.empty(); // empty removes all child elements. Normally this would be put in the script itself
    linechart(el)
    // Option 2: Set a flag to avoid calling the script twice 
    //if(el.attr('data-initialized') == null) {
    //  console.log("not initialized")
    //  linechart(el)
    //} else {
    //  console.log("initialized")
    //}
    // // Attach event listeners again
    // linechart.attachDelegatedEventListeners(el);
    //el.attr('data-initialized', true);
  });
});

Rails+WebPack

Tagged rails, webpack  Languages slim

“Pass all your pack names when using javascript_packs_with_chunks_tag helper otherwise you will get duplicated chunks on the page”

<%= stylesheet_pack_tag 'YOUR_PACK_NAME_HERE' %>

<%# DO %>
<%= javascript_packs_with_chunks_tag 'calendar', 'map' %>

<%# DON'T %>
<%= javascript_packs_with_chunks_tag 'calendar' %>
<%= javascript_packs_with_chunks_tag 'map' %>

https://github.com/rails/webpacker/blob/master/docs/webpack.md#add-splitchunks-webpack-v4

How to convert a HTML table to CSV or JSON

Tagged csv, html, table, json  Languages ruby
require 'nokogiri'
require 'net/http'
require 'csv'
require 'json'

url = 'http://site/html.table'
uri = URI(url)
response = Net::HTTP.get(uri)

doc = Nokogiri::HTML(response)
table = doc.xpath('//table//tr').map do |row|
  row.xpath('td').map do |cell|
    cell.text
  end
end
puts JSON.pretty_generate(table)

How to extract numbers from a string in Bash scripts

Tagged bash, extract, number, regexp, rematch  Languages bash

To extract numbers from a string in Bash scripts you can use a bash feature called REMATCH. You don’t need grep, sed, or awk.

Add this to script.sh (remember to run chmod +x script.sh):

#!/usr/bin/env bash
string="COPY 23845\n3409"
if [[ $string =~ ^COPY[[:space:]]([0-9]+) ]]; then
  echo "Match: ${BASH_REMATCH[1]}"
else
  echo "No match"
fi

This will print 23845, but not 3409. Note that this example uses a capture group.

How to use PostgreSQL's "COPY FROM STDIN"

Tagged copy from, postgresql  Languages bash

This is an example of how to use PostgreSQL’s “COPY FROM STDIN”:

psql xxx_development -c "COPY table1(column1, column2) FROM STDIN DELIMITER E'\t' QUOTE E'\x01' ENCODING 'ISO-8859-1' CSV HEADER;" < sub.txt

NOTE: The following example where I put the “COPY FROM STDIN” in a file does not work:

# The COPY FROM STDIN SQL statement is in a file named copy.sql. This does not work:
psql xxx_development | cat copy.sql

Handling invalid CSV quoting with PostgreSQL's "COPY FROM"

Tagged copy from, postgresql, quote  Languages sql

PostgreSQL “COPY FROM” not importing all CSV rows and not reporting any errors?

This is an example of how to use a character that does not exist in the CSV file as a quote character to fix and handle invalid quoting issues:

CREATE TABLE xxx (
  id SERIAL,
  name VARCHAR,
  value VARCHAR,
  PRIMARY KEY (id)
);
COPY xxx(name, value)
FROM 'xxx.txt'
DELIMITER E'\t'
-- Use a character that does not exist in the CSV file as quote character to handle quoting
QUOTE E'\x01'
ENCODING 'utf8'
CSV HEADER;

Best of luck…

Notifications for failing cron jobs

Tagged cron, failure, notification, slack  Languages bash

Here’s a simple way of receiving a notification to a Slack channel when a cron job fails:

#!/usr/bin/env sh

# Replace this with the command
echo "hello world" &> /var/log/app.log

if [ $? -eq 0 ]
then
  curl -X POST -H 'Content-Type: application/json' --data "{\"text\":\"Cron job ran successfully $(tail -n 5 /var/log/app.log)\"}" $SLACK_URL
  exit 0
else
  curl -X POST -H 'Content-Type: application/json' --data "{\"text\":\"Cron job failed $(tail -n 5 /var/log/app.log)\"}" $SLACK_URL
  exit 1
fi

Set the SLACK_URL environment variable to your slack channel’s hook URL.

A message will be sent to the Slack channel whenever the command fails along with the last 5 lines from the log file.

For loops with LATERAL JOIN in PostgreSQL

Tagged for, loop, postgresql, previous, quarter  Languages sql
SELECT
  *
-- initial data that we want to loop through
FROM (
  SELECT
    *
  FROM
    financials
  WHERE
    quarter = '2020-06-30'
-- for each row run this query
) current_quarter LEFT JOIN LATERAL (
  SELECT
    revenue AS previous_revenue
  FROM
    financials
  WHERE
    company_id = current_quarter.company_id AND 
    -- calculate previous_quarter by subtracting one day
    quarter = to_char(date_trunc('quarter', current_quarter.quarter)::date - 1, 'yyyy-mm-dd')::date
) previous_quarter ON true;

Refreshing all materialized views in the correct order with one command

Tagged dynamic, materialized, postgres, sql, view  Languages sql

The following code is from https://wiki.postgresql.org/wiki/Refresh_All_Materialized_Views:

CREATE OR REPLACE VIEW mat_view_dependencies AS
WITH RECURSIVE s(start_schemaname,start_relname,start_relkind,
         schemaname,relname,relkind,reloid,owneroid,ownername,depth)
         AS (
-- List of tables and views that mat views depend on
SELECT n.nspname AS start_schemaname, c.relname AS start_relname,
c.relkind AS start_relkind,
n2.nspname AS schemaname, c2.relname, c2.relkind,
c2.oid AS reloid,
au.oid AS owneroid,
au.rolname AS ownername,
0 AS depth
FROM pg_class c JOIN pg_namespace n
     ON c.relnamespace=n.oid AND c.relkind IN ('r','m','v','t','f')
JOIN pg_depend d ON c.oid=d.refobjid
JOIN pg_rewrite r ON d.objid=r.oid
JOIN pg_class c2 ON r.ev_class=c2.oid -- AND c2.relkind='m'
JOIN pg_namespace n2 ON n2.oid=c2.relnamespace
JOIN pg_authid au ON au.oid=c2.relowner

UNION

-- Recursively find all mat views depending on previous level
SELECT s.start_schemaname, s.start_relname, s.start_relkind,
n.nspname AS schemaname, c2.relname,
c2.relkind, c2.oid,
au.oid AS owneroid, au.rolname AS ownername,
s.depth+1 AS depth
FROM s
JOIN pg_depend d ON s.reloid=d.refobjid
JOIN pg_rewrite r ON d.objid=r.oid
JOIN pg_class c2 ON r.ev_class=c2.oid AND (c2.relkind IN ('m','v'))
JOIN pg_namespace n ON n.oid=c2.relnamespace
JOIN pg_authid au ON au.oid=c2.relowner

WHERE s.reloid <> c2.oid -- exclude the current MV which always depends on itself
)
SELECT * FROM s;

--------------------------------------------------
--- A view that returns the list of mat views in the
--- order they should be refreshed.
--------------------------------------------------
CREATE OR REPLACE VIEW mat_view_refresh_order AS
WITH b AS (
-- Select the highest depth of each mat view name
SELECT DISTINCT ON (schemaname,relname) schemaname, relname, ownername, depth
FROM mat_view_dependencies
WHERE relkind='m'
ORDER BY schemaname, relname, depth DESC
)
-- Reorder appropriately
SELECT schemaname, relname, ownername, depth AS refresh_order
FROM b
ORDER BY depth, schemaname, relname
;

We can now write a custom function to execute the refresh of all materialized views in the correct order:

CREATE OR REPLACE FUNCTION refresh_materialized_views () RETURNS VOID AS
$$
DECLARE
BEGIN
  EXECUTE (SELECT string_agg('REFRESH MATERIALIZED VIEW "' || schemaname || '"."' || relname || '";', '' ORDER BY refresh_order) AS script FROM mat_view_refresh_order);
END;
$$ LANGUAGE plpgsql;

We use a simple select to execute the dynamically generated SQL

SELECT refresh_materialized_views();