Postgres SQL query for extracting and querying a JSON object containing an array of JSON objects

Tagged json, jsonb, postgres  Languages json, sql

We want to extract data by querying a JSON object (hash, dictionary, map, object) which contains an array of JSON objects:

{
  "responses": [
    {
      "patient": {
        "ssid": "101010-XXXX"
      },
      "patient": {
        "ssid": "010170-XXXX"
      }
   ]
 }
}

In Postgres we can do this with the jsonb_array_elements function:

WITH json_messages AS (
 SELECT jsonb_array_elements(body#>'{responses}')->'patient'->>'ssid', id from messages
)
SELECT * FROM json_messages WHERE ssid = '010150-XXXX';

How to send an HL7 message using Ruby

Tagged hl7, ruby  Languages ruby
require 'socket'
a = TCPSocket.new('127.0.0.1', 2575)
message = File.read("src/test/resources/iso-8859-1.hl7")
puts "Wrote:\n#{message}"
a.write "\x0b#{message}\x1c\r"
puts "Received:\n" + a.recv(1024)
a.close

How to parse OBX segments from an ORU-01 HL7 message using Java and HAPI

Tagged hapi, hl7, java, obx, oru  Languages java, xml

Tested with HAPI 2.3:

import ca.uhn.hl7v2.model.v23.message.ORU_R01;
import ca.uhn.hl7v2.model.v23.group.*;
import ca.uhn.hl7v2.model.v23.segment.OBX;
import ca.uhn.hl7v2.model.v23.segment.OBR;
import ca.uhn.hl7v2.model.Varies;
import org.junit.BeforeClass;
import ca.uhn.hl7v2.HL7Exception;
import ca.uhn.hl7v2.model.Message;
import ca.uhn.hl7v2.parser.PipeParser;
import ca.uhn.hl7v2.util.Terser;

/*
 * An HL7 message has the following components:
 *
 * MSH - Message Header Segment
 * RESPONSE (repeating)
 *   PATIENT
 *     PID - Patient Identification Segment
 *     PV1 – Patient Visit Segment
 *   ORDER OBSERVATION (repeating)
 *     OBR - Observation Request Segment
 *       ORC - Common Order Segment
 *       OBR - Observation Request Segment
 *       OBX – Observation Segment (repeating)
 *
 */

PipeParser pipeParser = new PipeParser();
pipeParser.setValidationContext(new ca.uhn.hl7v2.validation.impl.NoValidation());
Message message = pipeParser.parse(m);
ORU_R01 oru = (ORU_R01) message;
MSH msh = oru.getMSH();
String sendingApp = msh.getSendingApplication().encode();
String sendingFacility = msh.getSendingFacility().encode();
//
// ORU_R01_RESPONSE group structure (a Group object)
//
// 1: ORU_R01_PATIENT (a Group object) optional
// 2: ORU_R01_ORDER_OBSERVATION (a Group object) repeating
//
// See https://hapifhir.github.io/hapi-hl7v2/v23/apidocs/src-html/ca/uhn/hl7v2/model/v23/group/ORU_R01_RESPONSE.html
//
for (ORU_R01_RESPONSE response : oru.getRESPONSEAll()) {
  //
  // ORU_R01_ORDER_OBSERVATION group structure (a Group object)
  //
  // 1: ORC (Common order segment) optional
  // 2: OBR (Observation request segment)
  // 3: NTE (Notes and comments segment) optional repeating
  // 4: ORU_R01_OBSERVATION (a Group object) repeating
  // 5: CTI (Clinical Trial Identification) optional repeating
  //
  // See https://hapifhir.github.io/hapi-hl7v2/v23/apidocs/src-html/ca/uhn/hl7v2/model/v23/group/ORU_R01_ORDER_OBSERVATION.html
  //
  for (ORU_R01_ORDER_OBSERVATION orderObservation : response.getORDER_OBSERVATIONAll()) {
    OBR obr = orderObservation.getOBR();
    String fillerOrderNumber = obr.getObr3_FillerOrderNumber().encode();
    //
    // ORU_R01_OBSERVATION group structure (a Group object)
    //
    // 1: OBX (Observation segment) optional
    // 2: NTE (Notes and comments segment) optional repeating
    //
    // See https://hapifhir.github.io/hapi-hl7v2/v23/apidocs/src-html/ca/uhn/hl7v2/model/v23/group/ORU_R01_OBSERVATION.html
    //
    for (ORU_R01_OBSERVATION observation : orderObservation.getOBSERVATIONAll()) {
      //
      // HL7 OBX message segment (Observation segment)
      //
      // https://hapifhir.github.io/hapi-hl7v2/v23/apidocs/src-html/ca/uhn/hl7v2/model/v23/segment/OBX.html
      //
      OBX obx = observation.getOBX();
      String type = obx.getObx3_ObservationIdentifier().getCe2_Text().getValue();
      String status = obx.getObservResultStatus().getValue();
      for (Varies varies : obx.getObx5_ObservationValue()) {
        String value = varies.encode();
        log.info("value {} type {} status {}", value, type, status);
      }
    }
  }
}
    <dependency>
      <groupId>ca.uhn.hapi</groupId>
      <artifactId>hapi-base</artifactId>
      <version>${hapi.version.stable}</version>
    </dependency>
    <dependency>
      <groupId>ca.uhn.hapi</groupId>
      <artifactId>hapi-structures-v23</artifactId>
      <version>${hapi.version.stable}</version>
    </dependency>

Use the HAPI test panel to inspect and find the names of elements, e.g. PID-3-4: https://hapifhir.github.io/hapi-hl7v2/hapi-testpanel/install.html

How to use LATERAL as a for loop in Postgres

Tagged lateral, loop, postgres  Languages sql

This example shows how to use LATERAL as a for loop in Postgres. The query will perform a lateral subquery for each month. The subquery returns the number of active courses during each month.

-- A list of months and their start and end timestamps
WITH months AS (
  SELECT start, start + (interval '1' month) - (interval '1' second) AS end FROM (
    SELECT generate_series(DATE('2010-01-01'), DATE('2010-12-01'), interval '1 month') AS start
  ) months
)
-- For each month, run a query that returns the number of active courses during that month
SELECT * FROM months AS m
LEFT JOIN LATERAL (
  SELECT count(id)
  FROM courses AS c
  WHERE
    (c.start <= m.end AND c.start >= m.start) OR
    (c.end <= m.end AND c.end IS NULL)
) AS results ON TRUE;

How to generate a series with the start and end dates of months with Postgres

Tagged generate_series, months, sql  Languages sql
WITH m AS (
  SELECT generate_series(DATE('2010-01-01'), DATE('2010-12-01'), interval '1 month') AS start
)
SELECT m.start, m.start + (interval '1' month) - (interval '1' second) AS end FROM m;
┌────────────────────────┬────────────────────────┐
│         start          │          end           │
├────────────────────────┼────────────────────────┤
│ 2010-01-01 00:00:00+02 │ 2010-01-31 23:59:59+02 │
│ 2010-02-01 00:00:00+02 │ 2010-02-28 23:59:59+02 │
│ 2010-03-01 00:00:00+02 │ 2010-03-31 23:59:59+03 │
│ 2010-04-01 00:00:00+03 │ 2010-04-30 23:59:59+03 │
│ 2010-05-01 00:00:00+03 │ 2010-05-31 23:59:59+03 │
│ 2010-06-01 00:00:00+03 │ 2010-06-30 23:59:59+03 │
│ 2010-07-01 00:00:00+03 │ 2010-07-31 23:59:59+03 │
│ 2010-08-01 00:00:00+03 │ 2010-08-31 23:59:59+03 │
│ 2010-09-01 00:00:00+03 │ 2010-09-30 23:59:59+03 │
│ 2010-10-01 00:00:00+03 │ 2010-10-31 23:59:59+02 │
│ 2010-11-01 00:00:00+02 │ 2010-11-30 23:59:59+02 │
│ 2010-12-01 00:00:00+02 │ 2010-12-31 23:59:59+02 │
└────────────────────────┴────────────────────────┘

Same query without the CTE:

SELECT start, start + (interval '1' month) - (interval '1' second) AS end FROM (
  SELECT generate_series(DATE('2010-01-01'), DATE('2010-12-01'), interval '1 month') AS start
) months;

How to crack an MD5 password using hashcat

Tagged crack, hashcat, md5  Languages bash

Brute-force attack

Install hashcat:

brew install hashcat

Write the MD5 hashes that we want hashcat to crack for us to a file:

echo '098f6bcd4621d373cade4e832627b4f6' >> hashes
echo '1a1dc91c907325c69271ddf0c944bc72' >> hashes

Attempt to crack MD5 password hash using brute force (“-a 3” switch):

$ hashcat -a 3 hashes

Show cracked hashes and passwords:

$ hashcat -a 3 hashes --show
098f6bcd4621d373cade4e832627b4f6:test
1a1dc91c907325c69271ddf0c944bc72:pass

Dictionary attack

Download a dictionary:

wget https://raw.githubusercontent.com/berzerk0/Probable-Wordlists/master/Real-Passwords/Top196-probable.txt

Write the MD5 hashes that we want hashcat to crack for us to a file:

echo '5f4dcc3b5aa765d61d8327deb882cf99' >> hashes

Attempt to crack the password using the dictionary:

hashcat -a 0 hashes Top196-probable.txt

Show cracked hashes and passwords:

$ hashcat -a 3 hashes --show
5f4dcc3b5aa765d61d8327deb882cf99:password

How to decode base64 encoded MD5 password hash

To decode a base64 encoded MD5 password hash you can use these commands:

echo -n 'base64-encoded-md5-password' | base64 -D | xxd -g 0 -ps > hashes

Tested with hashcat v4.0.1.

Database diff

Tagged database, diff, postgres  Languages bash, ruby, yaml

This script will connect to two databases, named old and new, and print:

  • the names of the tables that cannot be found in the new database
  • the difference in the amount of rows per table, if there is a difference in the number of rows

db_diff.rb:

#
# Postgres database diff script. Prints the names of missing tables and the
# difference in the amount of rows per table.
#
require 'sequel'
require 'pg'
require 'yaml'
require 'pry'

list_tables_sql = "SELECT tablename from pg_tables where schemaname = 'public';"
OLD_DB = Sequel.connect(YAML.load(File.read('old_database.yml')))
NEW_DB = Sequel.connect(YAML.load(File.read('new_database.yml')))
OLD_TABLES = OLD_DB[list_tables_sql].map{ |x| x.fetch(:tablename) }
NEW_TABLES = NEW_DB[list_tables_sql].map{ |x| x.fetch(:tablename) }

# Compare tables
def diff_tables
  OLD_TABLES - NEW_TABLES
end

# Compare row count
def diff_row_count
  OLD_TABLES.sort.reduce({}) do |hash, table|
    sql = "SELECT count(*) FROM %{table}"
    # Sequel's count method does not work.
    diff = OLD_DB[sql % {table: table}].first[:count].to_i - NEW_DB[sql % {table: table}].first[:count].to_i
    hash[table] = diff if diff != 0
    hash
  end
end

puts JSON.pretty_generate(tables: diff_tables, rows: diff_row_count)

Gemfile:

source 'https://rubygems.org'

gem 'sequel'
gem 'pg'
gem 'pry'

new_database.yml and old_database.yml:

adapter: postgres
host: localhost
port: 5432
encoding: unicode
database: x_production
username: x
password: x

To run the script:

gem install bundler
bundle
bundle exec ruby db_diff.rb

Other migration and diff tools

migra is a schema diff tool for PostgreSQL. Use it to compare database schemas or autogenerate migration scripts.

Given two database connections, it output a file that represent the difference between schemas. It means that if you run the output file into the from database, it’ll have the same schema as the to database.

Compares the PostgreSQL schema between two databases and generates SQL statements that can be run manually against the second database to make their schemas match.

Detecting software version with Ansible

Tagged ansible, version  Languages yaml

With Ansible, detecting the version of, for example, Redis or Racket can be done like this:

- name: Detect Redis version
  # Input: Redis server v=3.2.1 sha=00000000:0 malloc=libc bits=64 build=62a67eec83b28403
  # Output: 3.2.1
  shell: redis-server -v | awk '{print $3}' | sed -e 's/v=//'
  changed_when: False
  register: redis_installed_version

- name: Detect racket versions
  # Input: Welcome to Racket v6.6.
  # Output: 6.6
  shell: "racket -v | rev | cut -d ' ' -f1 | rev | sed 's/.$//' | sed 's/^v//'"
  register: racket_installed_version

Example: Download src only if version does not match:

- get_url:
    url: http://download.redis.io/releases/redis-{{redis_version}}.tar.gz
    dest: /usr/local/src/
    sha256sum: "{{redis_sha256}}"
  register: get_redis_result
  when: redis_installed_version.stdout | version_compare(redis_version, '!=')

How to set the setgid flag with Ansible

Tagged ansible, setgid, setuid  Languages yaml

This is how to set the setgid flag with Ansible. Tested with Ansible version 2.4.

- name: Create directories and set setguid
  file:
    path: "{{item}}"
    state: directory
    owner: www-data
    group: www-data
    # NOTE: 2 = setguid flag. The prefix 0 is required by Ansible
    mode: 02770
    recurse: true
  with_items:
    - /var/www/
  become: true

Reference

Setting the setgid permission on a directory (“chmod g+s”) causes new files and subdirectories created within it to inherit its group ID, rather than the primary group ID of the user who created the file (the owner ID is never affected, only the group ID).

See https://en.wikipedia.org/wiki/Setuid