Skip to content

Instantly share code, notes, and snippets.

View aarsilv's full-sized avatar

Aaron Silverman aarsilv

9 followers · 2 following
View GitHub Profile
@aarsilv
aarsilv / mysql_trigger_demo.sql
Created February 19, 2019 18:57
Simple demo of using MySQL triggers
USE demo;
/* DEMO FOR TRACKING HISTORY */
DROP TABLE IF EXISTS users;
DROP TABLE IF EXISTS user_history;
DROP PROCEDURE IF EXISTS append_user_history;
CREATE TABLE users (
user_id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
@aarsilv
aarsilv / mysql_8_windows_and_ctes.sql
Last active June 9, 2020 18:38
Example of MySQL 8 Windowing and Common Table Expression Functionality
CREATE DATABASE demo;
USE demo;
#Let's get some data
CREATE TABLE video_games (
video_game_id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
title varchar(200) NOT NULL,
developer varchar(200) NOT NULL,
platform varchar(100) NOT NULL,
release_date date NOT NULL,
@aarsilv
aarsilv / generate_signed_s3_url.js
Created May 24, 2018 17:53
Generating a signed s3 download url in node
// Creates a signed query string for getting private S3 files
// See "Query String Request Authentication Alternative" in https://docs.aws.amazon.com/AmazonS3/latest/dev/RESTAuthentication.html
// Node requirements
const crypto = require('crypto');
const querystring = require('querystring');
// Parameters
const AWS_BUCKET = 'my-bucket';
const AWS_KEY = 'MY:AWS:KEY';
@aarsilv
aarsilv / multiple_visits_in_segmentation.sql
Last active June 1, 2017 23:45
Queries for determining break down of members with multiple visits
/* visitors who were never seen as a lead or member */
SELECT count(distinct v_orig.visitor_cookie_id)
FROM videoblocks.abtest_members v_orig
LEFT JOIN videoblocks.abtest_members v_ul
ON v_orig.visitor_cookie_id = v_ul.visitor_cookie_id
AND v_orig.test_id = v_ul.test_id
AND v_orig.id <> v_ul.id
AND v_ul.user_lead_id IS NOT NULL
LEFT JOIN videoblocks.abtest_members v_m
ON v_orig.visitor_cookie_id = v_m.visitor_cookie_id
@aarsilv
aarsilv / full_text_myisam_vs_innodb_example.sql
Last active December 30, 2015 20:18
Simple example that illustrates the differences in MySQL's full text MATCH in BOOLEAN MODE on a table using MyISAM storage engine vs one using InnoDB
DROP TABLE IF EXISTS word_match_test;
CREATE TABLE word_match_test (
word varchar(63),
FULLTEXT INDEX IX_word_match_test_word (word)
) ENGINE=MyISAM;
INSERT INTO word_match_test (word)
VALUES ('test butterfly mouse kitten test'),
('test butterflymore mousemore test'),
@aarsilv
aarsilv / placeholder-support.js
Created December 9, 2013 03:58
JavaScript file that mimics the functionality of HTML5's placeholder attribute in browsers that don't support it, including Internet Explorer versions older than IE10.
$(function(){
//code from http://stackoverflow.com/questions/8628522/placeholder-not-working-for-internet-explorer
var _debug = false;
var _placeholderSupport = function() {
var t = document.createElement("input");
t.type = "text";
return (typeof t.placeholder !== "undefined");
}();
@aarsilv
aarsilv / node csv stream bug example.js
Created November 11, 2013 22:47
Simple illustration of bug in pausing stream being parsed by node csv
var csv = require('csv');
var fs = require('fs');
var os = require('os');
var path = require('path');
var ENABLE_PAUSING = true;
var testFilePath = path.join(os.tmpDir(),'test.csv');
console.log('Creating test csv temp file');
@aarsilv
aarsilv / JSON.stringify() excludes object properties with undefined values
Created October 8, 2013 18:44
Simple example showing how JSON.stringify() of an object excludes properties of undefined objects.
arr = ['a','b']
//[ 'a', 'b' ]
obj = {a: arr[0], b: arr[1], c: arr[2]}
//{ a: 'a', b: 'b', c: undefined }
JSON.stringify(obj)
//'{"a":"a","b":"b"}'
@aarsilv
aarsilv / MySQL UPDATE set order matters
Last active December 24, 2015 16:29
Simple example illustrating how in a MySQL UPDATE statement the order of column value pairs in the SET clause matters.
/*
* Beware that the orer of set operations in MySQL are evaluated from left to right for updating a single table,
and in no guarenteed order when updating multiple tables at once through a join.
http://dev.mysql.com/doc/refman/5.7/en/update.html
*/
DROP TABLE IF EXISTS test;
CREATE TABLE test (
num1 int,