We Shall See

#!/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
fi

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) {
  console.error(`
Usage: ${path.basename(process.argv[1])} HEADER VALUE
  We read from STDIN and write to STDOUT.
`);
  process.exit(1);
}

require('csv-stream-transform')({
  transform(row, cb) {
    row[process.argv[2]] = process.argv[3];
    cb(null, row);
  }
});

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: [
      'error',
      2
    ],
    'linebreak-style': [
      'error',
      'unix'
    ],
    quotes: [
      'error',
      'single'
    ],
    semi: [
      'error',
      'always'
    ]
  }
};

#!/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
});
process.stdin
  .pipe(parser)
  .pipe(transformer)
  .pipe(stringer)
  .pipe(process.stdout);

select
  fund_id,
  subfunds
from
  sf_foo;

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

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

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

#!/usr/bin/env node

'use strict';

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

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

process.stdin
  .pipe(JSONStream.parse('*'))
  .pipe(csvStringify({ header: WRITE_HEADER }))
  .pipe(process.stdout);

#!/usr/bin/env node

'use strict';

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

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

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
  /usr/local/etc/openssl/certs

and run
  /usr/local/opt/openssl/bin/c_rehash

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
  /usr/local/lib/python2.7/site-packages

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

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

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

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++) {
      ints.add(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];
  }
}

select 
  giftid, 
  sequence, 
  amendmentid,
  row_number()
  over(partition by giftid order by sequence desc) uno 
from giftamendment

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

exec msdb.dbo.rds_restore_database
  @restore_db_name='ydoyou',
  @s3_arn_to_restore_from='arn:aws:s3:::some-s3-bucket/ydoyou-raisers-edge.bak';

-- 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'

exec msdb.dbo.rds_backup_database
  @source_db_name='ydoyou',
  @s3_arn_to_backup_to='arn:aws:s3:::some-s3-bucket/ydoyou-restored.bak',
  @overwrite_S3_backup_file=1;

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Action": [
                "s3:ListBucket",
                "s3:GetBucketLocation"
            ],
            "Resource": [
                "arn:aws:s3:::some-s3-bucket"
            ]
        },
        {
            "Effect": "Allow",
            "Action": [
                "s3:GetObjectMetaData",
                "s3:GetObject",
                "s3:PutObject",
                "s3:ListMultipartUploadParts",
                "s3:AbortMultipartUpload"
            ],
            "Resource": [
                "arn:aws:s3:::some-s3-bucket/*"
            ]
        }
    ]
}

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
select
  count(*) count,
  te.active,
  te.longdescription picklist
from
  giftattributes ga
  join attributetypes at on at.attributetypesid = ga.attributetypesid
  left outer join tableentries te on ga.tableentriesid = te.tableentriesid
where
  at.description like @attr
  -- and at.typeofdata = 6
  -- and te.longdescription is not null
  -- and te.active = -1
group by
  te.longdescription,
  te.active
order by
  te.active,
  count(*) desc

-- Do Objects Have Multiple Entries for the same Attribute?
select
  ga.parentid giftid,
  at.description name,
  -- ga.sequence,
  ga.text,
  ga.num,
  ga.datetime,
  ga.currency,
  ga.boolean,
  ga.comments,
  te.longdescription picklist,
  convert(varchar, ga.attributedate, 101) date
from
  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
where
  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
select
  --ga.parentid giftid,
  at.description name,
  --ga.sequence,
  ga.text,
  ga.num,
  ga.datetime,
  ga.currency,
  ga.boolean,
  ga.comments,
  te.longdescription picklist,
  convert(varchar, ga.attributedate, 101) date
from
  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
where
  at.description like @attr
order by
  ga.parentid, ga.sequence

"ID","NPE01__ONE2ONECONTACT__C"
"0014B000006TMDTQA4","asdf"
"asdf","0034B000005XhcOQAS"
"0014B000006TMDVQA4","8e8e8e"

csvsql out/account_contact.csv
CREATE TABLE account_contact (
    "ID" VARCHAR(18) NOT NULL,
    "NPE01__ONE2ONECONTACT__C" VARCHAR(18) NOT NULL
);

CREATE EXTENSION file_fdw;

CREATE SERVER file_server FOREIGN DATA WRAPPER file_fdw;

CREATE FOREIGN TABLE account_contact (
    "ID" VARCHAR(18) NOT NULL,
    "NPE01__ONE2ONECONTACT__C" VARCHAR(18) NOT NULL
)
SERVER file_server
OPTIONS (
    format 'csv',
    header 'true',
    delimiter ',',
    filename '/pipedream/out/account_contact.csv'
);

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

#!/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.

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

#!/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() {
  console.log('SIGTERM');
  process.exit(0);
});

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

./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;
  Readable.call(this);
  this.index = 0;
  this.max = max;
}

util.inherits(UUIDsReadStream, Readable);

UUIDsReadStream.prototype._read = function() {
  if (this.index++ > this.max) {
    this.push(null);
  } 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);
    process.exit(6);
  });

uuidStr.pipe(process.stdout);