reddit-job-subscribers.conf

description "send subscriber stats to traffic"

manual
task
stop on reddit-stop or runlevel [016]

nice 10

env DB_HOST=localhost
env DB_PORT=5432
env DB_USER=reddit
env DB_NAME=reddit

env TRAFFIC_HOST=localhost
env TRAFFIC_PORT=5432
env TRAFFIC_USER=reddit
env TRAFFIC_NAME=reddit

script
TEMPFILE=$(mktemp)

cat <<SQL | psql -A -F'|' -t -h $DB_HOST -p $DB_PORT -U $DB_USER -d $DB_NAME > $TEMPFILE
SELECT
    value,
    CAST(date AS DATE),
    COUNT(*)
FROM
    reddit_rel_srmember r,
    reddit_data_subreddit d
WHERE
    r.name = 'subscriber' AND
    r.date > current_date - interval '1 days' AND
    r.date < current_date - interval '0 days' AND
    d.key = 'name' AND
    r.thing1_id = d.thing_id
GROUP BY
    value,
    CAST(date AS DATE)
;
SQL

cat <<SQL | psql -h $TRAFFIC_HOST -p $TRAFFIC_PORT -U $TRAFFIC_USER -d $TRAFFIC_NAME
\\copy traffic_subscriptions from '$TEMPFILE' with delimiter as '|';
SQL

rm -f $TEMPFILE
end script