chrisvaughn

# Criando um script .sh para executar todos os comandos:
#root@servidor:~# vi script.sh 
#root@servidor:~# chmod +x script.sh 
#root@servidor:~# ./script.sh

apt-get update
apt-get -y install autoconf automake build-essential git-core libass-dev libgpac-dev libsdl1.2-dev libtheora-dev libtool libvdpau-dev libvorbis-dev libx11-dev libxext-dev libxfixes-dev pkg-config texi2html zlib1g-dev libmp3lame-dev nasm gcc yasm && true
mkdir ~/ffmpeg_sources
cd ~/ffmpeg_sources
git clone --depth 1 git://github.com/mstorsjo/fdk-aac.git

chrisvaughn

CREATE OR REPLACE FUNCTION current_streak_single_year(arr integer[], as_of date) RETURNS INTEGER AS
$$
DECLARE
    total_streak integer := 0;
    month_streak integer;
    dt date;
BEGIN
    FOR i IN REVERSE month_index(as_of)..1 LOOP
        IF date_part('month', as_of) = i THEN
            dt := as_of;

chrisvaughn

shifted_checkin = 0000100111011111, computed streak should equal 5

0. starting value
  0000100111011111
1. invert (~shifted_checkin)
  1111011000100000
2. compute "shifted_checkin + 1"
  0000100111011111 + 1 = 0000100111100000
3. AND step 1 & 2 (~shifted_checkin & (shifted_checkin + 1))
  1111011000100000 & 0000100111100000 = 0000000000100000 = 32

chrisvaughn

-- takes a 2d array of checkins sorted by year desc.
-- it is assumed that the first elem in the array is the
-- checkins for the same year in the as_of. it will continue to
-- call current_streak for the year data until it finds a break in the streak
CREATE OR REPLACE FUNCTION current_streak_all_years(arr integer[][], as_of date) RETURNS INTEGER AS
$$
DECLARE
    total_streak integer := 0;
    year_streak integer;
    dt date;

chrisvaughn

CREATE OR REPLACE FUNCTION current_streak_for_month(x integer, as_of date) RETURNS INTEGER AS
$$
DECLARE
    shifted_checkin bigint;
    rightmost_zero bigint;
BEGIN
    shifted_checkin := x >> 31 - day_of_month(as_of);
    rightmost_zero := ~shifted_checkin & (shifted_checkin + 1);
    RETURN log(2, rightmost_zero)::INTEGER;

chrisvaughn

-- HELPER FUNCTIONS

-- returns the day of the month from a date
CREATE OR REPLACE FUNCTION day_of_month(dt date) RETURNS INTEGER AS
$$
    SELECT DATE_PART('days', dt)::INTEGER
$$ LANGUAGE SQL;

-- returns the numeric (1-12) value for a month from a date
CREATE OR REPLACE FUNCTION month_index(dt date) RETURNS INTEGER AS

chrisvaughn

CREATE TABLE "public"."streaks_checkins" (
	"user_id" int4 NOT NULL,
	"year" int2 NOT NULL,
	"checkins" int4[] NOT NULL DEFAULT '{0,0,0,0,0,0,0,0,0,0,0,0}'::integer[],
	CONSTRAINT "streaks_checkins_pkey" PRIMARY KEY ("user_id", "year") NOT DEFERRABLE INITIALLY IMMEDIATE
)

chrisvaughn

UPDATE streaks
SET current_streak = (
    CASE
        WHEN %(date)s - last_checkin_dt = 1 THEN current_streak + 1
        ELSE 1
    END
),
longest_streak = GREATEST(longest_streak, (
    CASE
        WHEN %(date)s - last_checkin_dt = 1 THEN current_streak + 1

chrisvaughn

CREATE TABLE "public"."streaks" (
	"user_id" int4 NOT NULL,
	"current_streak" int4 NOT NULL DEFAULT 0,
	"longest_streak" int4 NOT NULL DEFAULT 0,
	"last_checkin_dt" date NOT NULL,
	CONSTRAINT "streaks_pkey" PRIMARY KEY ("user_id") NOT DEFERRABLE INITIALLY IMMEDIATE
)

-- update query
UPDATE streaks

chrisvaughn

Keybase proof

I hereby claim:

• I am chrisvaughn on github.
• I am chrisvaughn (https://keybase.io/chrisvaughn) on keybase.
• I have a public key ASDE0TDzs5l13DLOsN-LqLbuFmQC5ZWkoxxubvHR78DNqgo

To claim this, I am signing this object: