A Simple Web Infrastructure Stack

Published on:

I've been thinking through how to build a rock solid, super fast,
super inexpensive web infrastructure for quite a while.
I recently got all the pieces working together.
It's amazing what you can do on Amazon for dirt cheap.

Here is the feature set.

  • Blazing fast CDN for static pieces
  • Secure, never have to copy keys/passwords around
  • High quality SSL CERT with automated renewal
  • Email alerts if SSL CERT gets too near expiration
  • Automated code deployment

This infrastructure will work for just about any web use case
and just about any programing language. It works for REST APIs,
simple static sites, single page apps, distributed
containerized micro services, etc.

Here are the components. Probably the only
unfamiliar items are certbot-s3front and ssl-cert-check.

  • CloudFront CDN
  • S3 Storage
  • Github Source Control
  • Let's Encrypt SSL
  • certbot-s3front
  • ssl-cert-check
  • EC2 Virtual Machine
  • IAM Roles
  • AWS Command Line Interface
  • Simple Email Service (SES)

CloudFront CDN

CloudFront is simple to set up and dirt cheap for what
you get. Most modest sites will be a few dollars per month.

We use S3 storage to hold the CloudFront resources, and we
automatically push resources from Github using a simple shell
script. The shell script also invalidates the CDN cache if
there are changes.

certbot-s3front makes it super easy to push a free SSL cert from
Let's Encrypt up to your CloudFront CDN. A simple cron job
renews the cert every month, and another cron script uses SES
to send email warnings if the cert is getting too close to
its expiration.

All this code, of course runs on our EC2 virtual machine, which
costs pennies per day.

IAM Roles

IAM roles are they key to simple security. You give permissions
to your EC2 server and never have to worry about copying keys
or passwords around. All code running on our EC2 server has
access to whatever it needs without knowing anything.

AWS Command Line Interface

The AWS CLI can do just about anything you need to do in AWS,
and with IAM roles, your scripts and apps never have to worry
about authentication.

We use the AWS CLI to send SES emails, invalidate the
CloudFront cache, sync folders with S3 storage, etc.

Automated Code Deployment

All you have to do is check your code into the master branch
in Github and your code will be automatically deployed to the
server (for APIs, etc) or to CloudFront for static bits. For
private repos, SSH keys are pulled from S3.

Let's Encrypt SSL

Let's Encrypt makes it super easy to generate and renew an SSL
cert to sit on your web app servers. Just a few lines of shell
script and some cron jobs and you have free, high quality
SSL certs forever.

Add fixed value column to CSV

Published on:


#!/usr/bin/env bash

if [ $# -lt 2 ]; then
  me="$(basename "$(test -L "$0" && readlink "$0" || echo "$0")")"
  echo ""
  echo "Usage: $me HEADER VALUE"
  echo ""
  echo "  We read from STDIN and write to STDOUT."
  echo ""
  exit 1

cat /dev/stdin | sed "1 s/$/,$1/" | sed "1 ! s/$/,$2/"


#!/usr/bin/env node

'use strict';

const path = require('path');

if (process.argv.length < 4) {
Usage: ${path.basename(process.argv[1])} HEADER VALUE
  We read from STDIN and write to STDOUT.

  transform(row, cb) {
    row[process.argv[2]] = process.argv[3];
    cb(null, row);

JavaScript Async/Await .eslintrc.js

Published on:

The new Async/Await is nice.


Here's an ESlint configuration file that supports it. Key bits are

  • plugins: ['async-await']
  • ecmaVersion: 2017


module.exports = {
  plugins: ['async-await'],
  env: {
    es6: true,
    node: true
  extends: 'eslint:recommended',
  parserOptions: {
    sourceType: 'module',
    ecmaVersion: 2017,
  rules: {
    'comma-dangle': ['error', {
      arrays: 'never',
      objects: 'never',
      imports: 'never',
      exports: 'never',
      functions: 'ignore'
    'no-console': 0,
    indent: [
    'linebreak-style': [
    quotes: [
    semi: [

Node.js Transform a CSV Stream

Published on:

Here is all you need to transform a CSV stream.

#!/usr/bin/env node

'use strict';
const parser = require('csv-parse')({
  columns: true
const stringer = require('csv-stringify')({
  header: true
const transformer = new require('stream').Transform({
  transform(row, encoding, cb) {
    // row will be JSON with the CSV column names as property
    // names.
    // modify row however you like, or create a new object
    // to pass to the callback.
    cb(null, row);
  objectMode: true

SQL Server expand comma delimited field into rows

Published on:

Turn this

fund_id subfunds
123 AA,BB,CC

into this

fund_id subfunds
123 AA
123 BB
123 CC

and now you are a SQL hero!

The trick is the UNION with recursive select from exploded_subfunds.


with exploded_subfunds(fund_id, subfund, subfunds) as (
  convert(varchar(100),LEFT(subfunds, CHARINDEX(',',subfunds+',')-1)),
  convert(varchar(100),STUFF(subfunds, 1, CHARINDEX(',',subfunds+','), ''))
union all
  convert(varchar(100),LEFT(subfunds, CHARINDEX(',',subfunds+',')-1)),
  convert(varchar(100),STUFF(subfunds, 1, CHARINDEX(',',subfunds+','), ''))
  subfunds > ''

select LEFT('AA,BB,CC', CHARINDEX(',','AA,BB,CC'+',')-1);
-- AA

select STUFF('AA,BB,CC', 1, CHARINDEX(',','AA,BB,CC'+','), '');
-- BB,CC

Node.js JSON to CSV

Published on:
#!/usr/bin/env node

'use strict';

const JSONStream = require('JSONStream');
const csvStringify = require('csv-stringify');

const WRITE_HEADER = process.argv[2] !== 'NO_HEADER';

  .pipe(csvStringify({ header: WRITE_HEADER }))

Simple Node.js Dedup CSV

Published on:

Here is a simple way to de-duplicate a CSV file on a single field in Node.js.

Not gonna help you too much without the pipedream library. But . . .

#!/usr/bin/env node

'use strict';

const dups = {};
const field = process.argv[2];
if (!field) {
  console.error('Field to dedup on required.');
  transform(i, cb) {
    if (dups[i[field]]) {
      // skip it

    } else {
      dups[i[field]] = true;
      cb(null, i);

brew install python

Published on:
out $ brew install python
Updating Homebrew...
==> Auto-updated Homebrew!
Updated 3 taps (caskroom/cask, caskroom/fonts, homebrew/core).
==> New Formulae
allure                              bdsup2sub                           cockroach                           docker-machine-driver-vultr         goad                                moodbar                             wabt
audacious                           chronograf                          dcos-cli                            gcc@6                               mono-libgdiplus                     pilosa
==> Updated Formulae
aalib                       cli53                       flex                        gspell                      libdc1394                   mercurial                   osc                         scw                         tomcat
ammonite-repl               clutter                     flow                        gtk+3                       libetpan                    metabase                    packetbeat                  sdl_ttf                     traildb
amqp-cpp                    cockatrice                  fluent-bit                  gtk-mac-integration         libfabric                   metricbeat                  paket                       serveit                     tth
antigen                     coffeescript                folly                       gtksourceview               libglade                    minicom                     pass                        shellshare                  twarc
antlr                       conan                       fonttools                   gtksourceviewmm             libglademm                  minio                       passenger                   sjk                         upx
apache-flink                consul                      freeimage                   gucharmap                   libgnomecanvas              miniupnpc                   pcre2                       snapraid                    urbit
apache-spark                consul-template             freetds                     gws                         libgnomecanvasmm            minizinc                    pdf2svg                     sngrep                      urh
app-engine-java             convox                      freetype                    halibut                     libhttpseverywhere          mldonkey                    pgformatter                 sonar-scanner               vagrant-completion
arangodb                    corebird                    fwup                        heroku                      libmaxminddb                monax                       pgplot                      source-to-image             vamp-plugin-sdk
archi-steam-farm            cppcheck                    gauge                       homebank                    libmemcached                mpich                       planck                      sourcekitten                vault
aria2                       datetime-fortran            gcc                         hugo                        libosmium                   mypy                        plantuml                    sslscan                     vice
artifactory                 datomic                     gecode                      hydra                       libphonenumber              mysql                       ponyc                       streamlink                  vte3
at-spi2-atk                 davix                       geoip                       hyper                       libproxy                    mysql-sandbox               postgresql                  subversion                  webpack
at-spi2-core                dbt                         gexiv2                      ibex                        libressl                    mysql@5.6                   postgresql@9.4              svtplay-dl                  weechat
aws-sdk-cpp                 deis                        gist                        imagemagick                 libslax                     mysqltuner                  postgresql@9.5              swiftformat                 winetricks
awscli                      diffoscope                  git                         imagemagick@6               libsoup                     nagios                      pre-commit                  swiftgen                    wireguard-tools
azure-cli                   django-completion           git-annex                   immortal                    libusb-compat               nano                        prest                       syncthing                   wolfssl
babl                        dnscrypt-proxy              git-extras                  infer                       libvirt                     ne                          prometheus                  sysbench                    xmake
bandcamp-dl                 docker                      gitbucket                   influxdb                    libvorbis                   neatvi                      protobuf-swift              sysdig                      xrootd
bash-preexec                docker-completion           gjs                         ipfs                        libxc                       nifi                        psqlodbc                    tbb                         yadm
binwalk                     docker-compose              glbinding                   jenkins                     linkerd                     nnn                         pwntools                    tcpreplay                   yarn
bit                         docker-compose-completion   glib                        jetty                       logrotate                   node                        pxz                         tcptraceroute               yaz
bitlbee                     doctl                       glog                        jetty-runner                logstash                    node-build                  pyenv                       tee-clc                     ykpers
bitrise                     duck                        gnome-recipes               json-fortran                logtalk                     node@4                      pygtksourceview             telegraf                    youtube-dl
bluepill                    dungeon                     gnu-cobol                   juise                       lumo                        node@6                      quantlib                    teleport                    zabbix
bmake                       elasticsearch               gnu-typist                  kafkacat                    luvit                       nuxeo                       rancher-cli                 tenyr                       zinc
buildifier                  elixir                      gnupg                       kobalt                      lynis                       nvc                         rancher-compose             terminal-notifier           zsh-autosuggestions
caddy                       ettercap                    gnutls                      kops                        lysp                        ode                         ripgrep                     terraform                   zsh-completions
cake                        fabio                       gobject-introspection       kubernetes-cli              macvim                      open-mpi                    rkflashtool                 terragrunt                  zstd
capnp                       fades                       gofabric8                   kubernetes-helm             make                        open-mpi@1.6                rocksdb                     the_silver_searcher
certbot                     ffmpeg                      goocanvas                   lapack                      makensis                    opencoarrays                rrdtool                     thefuck
certigo                     filebeat                    grails@2.5                  leaps                       mariadb                     opencore-amr                s-search                    tig
cfengine                    firebase-cli                graphite2                   libaacs                     media-info                  openrct2                    s3fs                        tiger-vnc
cheapglk                    fits                        grib-api                    libcds                      mediaconch                  openshift-cli               saltstack                   tippecanoe
citus                       flatcc                      grpc                        libcouchbase                menhir                      openvpn                     scipy                       todoman
==> Deleted Formulae
blucat                                                                                                                          dvtm

==> Installing dependencies for python: readline, sqlite, gdbm, openssl
==> Installing python dependency: readline
==> Downloading https://homebrew.bintray.com/bottles/readline-7.0.3_1.sierra.bottle.tar.gz
######################################################################## 100.0%
==> Pouring readline-7.0.3_1.sierra.bottle.tar.gz
==> Using the sandbox
==> Caveats
This formula is keg-only, which means it was not symlinked into /usr/local,
because macOS provides the BSD libedit library, which shadows libreadline.
In order to prevent conflicts when programs look for libreadline we are
defaulting this GNU Readline installation to keg-only..

For compilers to find this software you may need to set:
    LDFLAGS:  -L/usr/local/opt/readline/lib
    CPPFLAGS: -I/usr/local/opt/readline/include

==> Summary
🍺  /usr/local/Cellar/readline/7.0.3_1: 46 files, 1.5MB
==> Installing python dependency: sqlite
==> Downloading https://homebrew.bintray.com/bottles/sqlite-3.18.0.sierra.bottle.tar.gz
######################################################################## 100.0%
==> Pouring sqlite-3.18.0.sierra.bottle.tar.gz
==> Caveats
This formula is keg-only, which means it was not symlinked into /usr/local,
because macOS provides an older sqlite3.

If you need to have this software first in your PATH run:
  echo 'export PATH="/usr/local/opt/sqlite/bin:$PATH"' >> ~/.bash_profile

For compilers to find this software you may need to set:
    LDFLAGS:  -L/usr/local/opt/sqlite/lib
    CPPFLAGS: -I/usr/local/opt/sqlite/include

==> Summary
🍺  /usr/local/Cellar/sqlite/3.18.0: 11 files, 3.0MB
==> Installing python dependency: gdbm
==> Downloading https://homebrew.bintray.com/bottles/gdbm-1.13.sierra.bottle.tar.gz

curl: (22) The requested URL returned error: 500 Internal Server Error
Error: Failed to download resource "gdbm"
Download failed: https://homebrew.bintray.com/bottles/gdbm-1.13.sierra.bottle.tar.gz
Warning: Bottle installation failed: building from source.
==> Downloading https://ftp.gnu.org/gnu/gdbm/gdbm-1.13.tar.gz
######################################################################## 100.0%
==> ./configure --disable-silent-rules --without-readline --prefix=/usr/local/Cellar/gdbm/1.13
==> make install
🍺  /usr/local/Cellar/gdbm/1.13: 19 files, 552.2KB, built in 19 seconds
==> Installing python dependency: openssl
==> Downloading https://homebrew.bintray.com/bottles/openssl-1.0.2k.sierra.bottle.tar.gz
######################################################################## 100.0%
==> Pouring openssl-1.0.2k.sierra.bottle.tar.gz
==> Caveats
A CA file has been bootstrapped using certificates from the SystemRoots
keychain. To add additional certificates (e.g. the certificates added in
the System keychain), place .pem files in

and run

This formula is keg-only, which means it was not symlinked into /usr/local,
because Apple has deprecated use of OpenSSL in favor of its own TLS and crypto libraries.

If you need to have this software first in your PATH run:
  echo 'export PATH="/usr/local/opt/openssl/bin:$PATH"' >> ~/.bash_profile

For compilers to find this software you may need to set:
    LDFLAGS:  -L/usr/local/opt/openssl/lib
    CPPFLAGS: -I/usr/local/opt/openssl/include

==> Summary
🍺  /usr/local/Cellar/openssl/1.0.2k: 1,704 files, 12.1MB
==> Installing python
==> Downloading https://homebrew.bintray.com/bottles/python-2.7.13.sierra.bottle.1.tar.gz
######################################################################## 100.0%
==> Pouring python-2.7.13.sierra.bottle.1.tar.gz
==> /usr/local/Cellar/python/2.7.13/bin/python -s setup.py --no-user-cfg install --force --verbose --single-version-externally-managed --record=installed.txt --install-scripts=/usr/local/Cellar/python/2.7.13/bin --install-lib=/usr/local/lib/python2.7/site
==> /usr/local/Cellar/python/2.7.13/bin/python -s setup.py --no-user-cfg install --force --verbose --single-version-externally-managed --record=installed.txt --install-scripts=/usr/local/Cellar/python/2.7.13/bin --install-lib=/usr/local/lib/python2.7/site
==> /usr/local/Cellar/python/2.7.13/bin/python -s setup.py --no-user-cfg install --force --verbose --single-version-externally-managed --record=installed.txt --install-scripts=/usr/local/Cellar/python/2.7.13/bin --install-lib=/usr/local/lib/python2.7/site
==> Caveats
Pip and setuptools have been installed. To update them
  pip install --upgrade pip setuptools

You can install Python packages with
  pip install <package>

They will install into the site-package directory

See: http://docs.brew.sh/Homebrew-and-Python.html
==> Summary
🍺  /usr/local/Cellar/python/2.7.13: 3,526 files, 48MB

Mass Deleting Salesforce Data

Published on:

If you need to delete lots (500k or more) of records from Salesforce in a short period of time (less than a week or so), you may need to create a support ticket. Here is how you create it.

I need assistance with: Setup & Security
Product Topics:         Data Management
Subject:                Deleting Data and Objects

In the ticket list all the objects that need to be deleted. Not sure if support will actually delete your objects for you, but they will perform a "Physical Delete" so the records are actually removed from the database. This may be necessary for performance reasons.

Back Story

If an org has deleted a large number of records (generally 500K or more) in a short time frame (under a week), all of the records that are ready to be Physically Deleted, may not be purged for days or longer. This is especially common when an org deleted multiple millions of records in a very short timeframe. Salesforce is not designed to easily handle extremely large deletes in a short period of time. We are improving the way this is handled, and are making improvements, but to help our customers, we need to use a Manual Physical Delete process to help remove the records.

Since these records are still in the system, they can still affect the performance of reports and API queries. For best results, it is generally recommended that when large deletes are being processed, the org either manually empties the Recycle Bin or uses the emptyRecycleBin() call to get the records ready for the Physical Delete process.

In this case, it may be necessary for Tier 3 or R&D to manually kick off the Physical Delete process. This will submit the entire org (will start to remove all of the records set for Physical Delete), or it can be submitted for specific record entities (eg – Accounts, Opportunities, a custom object). The system will then begin to Physically Delete the specified records. The advantage of this is that it can be done as needed, and during the normal business hours.

About your assumption on a Physical Delete, you are pretty much correct. The process ultimately deletes all soft-deleted records so that they no longer be available via an isDeleted=True query.

Actually Delete Data

as part your Premier Plus Success Plan, you have an option to create a case to have our Admin Team process the deletion for you. You simply need to make sure that you have a CSV file of the records being deleted attached on the case you open. You can create an Admin Request case in Help and Training by first selecting Configuration Services.

Turns out it wasn't exactly as described above, but here's the format of the request:

I need assistance with: Configuration Services
Product Topics:         Data Management
Subject:                Data Delete

Salesforce Apex Custom Id Generator

Published on:
public with sharing class Atomic {

  // Warning!!! This code has not been tested thoroughly. It works, but

  // may throw too many lock failures, etc. It may not work for your

  // particular use case.

  // We had a requirement that both accounts and contacts should have an

  // auto-incrementing id, but the customer never wanted the ids to overlap.

  // We could have just started the counters far enough apart to make sure

  // the max of one would never reach the min of the other, but the 

  // customer didn't like that.

  // The key to the whole thing is SOQL "select for update" syntax.

  // See getAtomicSingleton() below.

  // This will cause an error to be thrown if there are any changes to your

  // record between the time you select and the time you update. This isn't 

  // ideal, but it's the only way I can think of to achieve atomicity.

  // We created a custom object, Atomic__c, that will only ever have a single

  // row and a single field, Account_Contact_Id__c.

  // We will use a trigger to set the account and contact ids. 

  // We select this single row, add the number of IDs we are going to

  // need in our trigger to the current Account_Contact_Id__c value. 

  // Then we update the row with it's new value. If it

  // fails we try again up to 7 times.

  public class MyException extends Exception {}

  // Return an array of integer ids which once returned will ever be returned

  // again. So once 1-10 have been returned they will never be returned again.

  // The smallest id returned next time this method returns successfully will 

  // be 11.

  public static Integer[] nextAccountContactIds(Integer count) {
    if (count < 1 || count > 200) {
      throw new MyException('Count out of range: '
        + count + '. Must be between 1 and 200 (inclusive).');
    return nextAccountContactIds(count, 0);

  private static Integer[] nextAccountContactIds(Integer count, Integer retry) {
    Atomic__c a = getAtomicSingleton();
    Integer startingValue = 1;
    if (a.Account_Contact_Id__c == null) {
      a.Account_Contact_Id__c = startingValue;
    Integer[] rv = buildArray(a.Account_Contact_Id__c.intValue(), count);
    a.Account_Contact_Id__c += count;
    try {
      update a;
    } catch (Exception e) {
      if (retry < 7) {
        return nextAccountContactIds(count, ++retry);
      } else {
        throw e;
    return rv;

  private static Integer[] buildArray(Integer startingNumber, Integer count) {
    Integer[] ints = new List<Integer>();
    for (Integer i = startingNumber; i < startingNumber + count; i++) {
    return ints;

  // The very first time this is called it will create the singleton row. 

  // It will NOT be atomic that very first time because we are doing an insert

  // not a select for update. You may want to call nextAccountContactIds()

  // manually once to seed the table before using it in a context that will

  // require atomicity.

  private static Atomic__c getAtomicSingleton() {
    Atomic__c[] aa = Database.query(
      'select ' +
      ChargentService.allFields(Atomic__c.SObjectType) +
      ' from Atomic__c where Name = \'Singleton\' limit 1 for update');
    if (aa.size() == 0) {
      Atomic__c a = new Atomic__c();
      a.Name = 'Singleton';
      insert a;
      return a;
    return aa[0];

SQL select first (or last) row of a group

Published on:

My goal is to select the amendment id of the most recently amended gift.

The most recently amended gift will have the highest sequence number.

giftamendment table

giftid sequence amendmentid
249138 2 6517441
249138 1 6078366
249139 1 6078814
249246 3 13162954
249246 2 12731461
249246 1 12406299

sql over clause


In laymans terms, the over clause makes the row I want have a value of 1 for
the uno field.

  over(partition by giftid order by sequence desc) uno 
from giftamendment

query results

giftid sequence amendmentid uno
249138 2 6517441 1
249138 1 6078366 2
249139 1 6078814 1
249246 3 13162954 1
249246 2 12731461 2
249246 1 12406299 3

Final Solution

Finally I just select the rows from summary where uno is 1.

with summary as (
    over(partition by giftid order by sequence desc) uno 
  from giftamendment
select amendmentid from summary where uno = 1

query results


Backup and Restore SQL Server to Amazon RDS

Published on:

Backup and Restore SQL Server to Amazon RDS Instance

If you are using Amazon RDS, you can't backup and restore in the normal way
with SQL Server Management Studio.

You have to upload your .BAK file to S3 and then run one of the special stored
procedures documented here:

Once you have uploaded your .BAK file to S3 just run the following to restore it
to your RDS instance (the stored proc is already included in RDS).

exec msdb.dbo.rds_restore_database

-- Check the status. Make sure to use the correct task id returned by
-- the above stored proc.
exec msdb.dbo.rds_task_status @db_name='ydoyou', @task_id='2'

Backing up is just the reverse.

exec msdb.dbo.rds_backup_database

Below is a specific implementation of the instructions in the link above.

S3 Bucket


Upload your .BAK files to the some-s3-bucket S3 bucket and all will be well.

IAM Policy

The sql-server-backup IAM policy allows you to backup and restore SQL Server
.BAK files from the S3 bucket some-s3-bucket.


    "Version": "2012-10-17",
    "Statement": [
            "Effect": "Allow",
            "Action": [
            "Resource": [
            "Effect": "Allow",
            "Action": [
            "Resource": [

IAM Role


Create the sql-server-backup IAM role with one policy, sql-server-backup.

RDS Option Group


Create an option group called sqlserver-options. Make sure your RDS instance
uses this option group. You can specify this when you create the db or

The sqlserver-options option group must have an option named
SQLSERVER_BACKUP_RESTORE that points to the sql-server-backup IAM Role.

Thats all folks!

Raiser's Edge Gift Attributes SQL

Published on:

Some handy SQL to run for Raiser's Edge gift attributes.

DECLARE @attr varchar(MAX)
SET @attr = 'Legacy Correction'

-- Overall Count
select count(*) count
from giftattributes ga
join attributetypes at on ga.attributetypesid = at.attributetypesid
where at.description like @attr

-- Determine Picklist Values
  count(*) count,
  te.longdescription picklist
  giftattributes ga
  join attributetypes at on at.attributetypesid = ga.attributetypesid
  left outer join tableentries te on ga.tableentriesid = te.tableentriesid
  at.description like @attr
  -- and at.typeofdata = 6
  -- and te.longdescription is not null
  -- and te.active = -1
group by
order by
  count(*) desc

-- Do Objects Have Multiple Entries for the same Attribute?
  ga.parentid giftid,
  at.description name,
  -- ga.sequence,
  te.longdescription picklist,
  convert(varchar, ga.attributedate, 101) date
  giftattributes ga
  -- ga.constitid is always null
  -- join records r on r.id = ga.constitid
  join attributetypes at on at.attributetypesid = ga.attributetypesid
  left outer join tableentries te on te.tableentriesid = ga.tableentriesid
  at.description like @attr
  and 1 < (
    select count(*)
    from giftattributes z
    join attributetypes zt on zt.attributetypesid = z.attributetypesid
    where z.parentid = ga.parentid
    and zt.description like @attr)
order by
  ga.parentid, ga.sequence

-- Select all
  --ga.parentid giftid,
  at.description name,
  te.longdescription picklist,
  convert(varchar, ga.attributedate, 101) date
  giftattributes ga
  -- ga.constitid is always null
  -- join records r on r.id = ga.constitid
  join attributetypes at on at.attributetypesid = ga.attributetypesid
  left outer join tableentries te on te.tableentriesid = ga.tableentriesid
  at.description like @attr
order by
  ga.parentid, ga.sequence

PostgreSQL CSV file foreign data wrapper

Published on:

Postgres foreign data wrappers allow you to create a table from a CSV file
without actually loading the CSV file into Postgres.

You can then query the data in Postgres but not update it.
Updates you make to the CSV file are immediately seen in Postgres.

Given this file . . .



You can use csvsql to generate your CREATE TABLE statement for you.

csvsql out/account_contact.csv
CREATE TABLE account_contact (

Then just tweak it a bit to create your foreign table.



CREATE FOREIGN TABLE account_contact (
SERVER file_server
    format 'csv',
    header 'true',
    delimiter ',',
    filename '/pipedream/out/account_contact.csv'

Now you can query the table.

select * from account_contact;
         ID         | NPE01__ONE2ONECONTACT__C
 0014B000006TMDTQA4 | asdf
 asdf               | 0034B000005XhcOQAS
 0014B000006TMDVQA4 | 8e8e8e
(3 rows)

Bash cleanup csv header

Published on:

Clean up CSV header row using bash.

#!/usr/bin/env bash

# Takes a CSV input file and replaces all non alpha numeric characters
# in the header (first line) with underscore. Makes it nice to import into
# SQL database.

if [ -a $FILE ]; then
    HEADER=$(head -1 "$FILE" | tr '[:upper:]' '[:lower:]')
    export IFS=","
    for word in $HEADER; do
    sed -i "1s/.*/$NEW_HEADER/" "$FILE"
    echo "file, $FILE, not found"

Node Docker and SIGTERM

Published on:

I am not sure why but outside of docker your node app will exit immediately
on SIGTERM even if you don't have a SIGTERM handler. But when you run your
app in a docker it does not immediately quit on SIGTERM. It takes 10
seconds and then docker has to SIGKILL it.

This simple app without the SIGTERM handler will need to be killed by Docker. But outside of Docker it exits immediately.

Full example project here: https://github.com/johndstein/docker-syslog-loglove

#!/usr/bin/env node

var log = new(require('loglove'))().log(__filename.substring(process.cwd().length));

log.info('we are logging out something here.');

process.on('SIGTERM', function() {

setInterval(function() {
  log.info('we are logging out something here.');
}, 5000);

Node custom readable stream

Published on:

This guy creates a CSV file of fake IDs and ACCOUNTIDs. It's a simple example of a custom readable stream in node.

Run it like this.

./generate-uuids.js 100000 > 100k.csv


#!/usr/bin/env node

'use strict';

var Readable = require('stream').Readable;
var uuid = require('node-uuid');
var util = require('util');

// test class to generate a bunch of unique ids to load into map
// 100k seems no problem. 1 million you run out of memory.
// ./generate-uuids.js 100000 > csv/million.csv

function UUIDsReadStream(max) {
  max = !!max ? parseInt(max, 10) : 10;
  max = isNaN(max) ? 10 : max;
  this.index = 0;
  this.max = max;

util.inherits(UUIDsReadStream, Readable);

UUIDsReadStream.prototype._read = function() {
  if (this.index++ > this.max) {
  } else if (this.index === 1) {
    this.push('ID, ACCOUNTID\n');
  } else {
    this.push(`${uuid.v4()}, ${uuid.v4()}\n`);

var uuidStr = new UUIDsReadStream(process.argv[2])
  .on('end', () => {
    // console.log('end');
  }).on('error', function(err) {
    console.log('err', err);