aarsilv’s gists

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 / 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 / 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 / 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 / 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 / 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 / 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 / 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 / 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,

Aaron Silverman aarsilv