Blake Snippets

readme.md

Rollbar querying

Also scope to item id with item.counter. More docs

SELECT *
FROM item_occurrence
WHERE person.email = 'kim@kalka.com.au'
ORDER BY timestamp DESC
LIMIT 0, 100

File management

merged csvs head -n 1 example.csv > combined.out && tail -n+2 -q *.csv >> combined.out

Elixir debugging

find flakey tests

while [ 0 -eq $? ]; do mix test --maxfailures 1; done

Zoho Reports

retry csvs that errored

find /mnt/readingeggs/zoho/error/ -name *Students-2021083118*csv* -exec mv '{}' /mnt/readingeggs/zoho/ \;

iTunes Sub issues

more info

Clever

Querying events

select subscription_type,inserted_at,data from events where data #>> '{"data", "object", "id"}' = '5b568995993bbf08d2df3b49';

select subscription_type,inserted_at,data ->> 'type' as type, data ->> 'created' as created from events where data -> 'data' -> 'object' ->> 'id' = 'clever_id';

Reset the clever database

(connect to the stating clever database, then...)

begin;
delete from sections where district_id='58da8a43cc70ab00017a1a87';
delete from teachers where district_id='58da8a43cc70ab00017a1a87';
delete from students where district_id='58da8a43cc70ab00017a1a87';
delete from school_admins where district_id='58da8a43cc70ab00017a1a87';
delete from schools where district_id='58da8a43cc70ab00017a1a87';
delete from district_admins where district_id='58da8a43cc70ab00017a1a87';
delete from synchronisations where district_id='58da8a43cc70ab00017a1a87';
delete from events where district_id='58da8a43cc70ab00017a1a87';
delete from districts where id='58da8a43cc70ab00017a1a87';
commit;

Delete all the clever_* matching records in the RE database

NOTES

* the districts id is 16338

* the matching clever_districts id is 20

(connect to the RE stating database, then...)

begin;
delete from clever_students where clever_district_id=20;
delete from clever_teachers where clever_district_id=20;
delete from clever_school_classes where clever_district_id=20;
delete from clever_schools where clever_district_id=20;

delete from student_school_classes where school_class_id in (
  select school_classes.id from school_classes
  join schools on schools.id = school_classes.school_id
  where schools.district_id=16338
);
delete from teacher_school_classes where school_class_id in (
  select school_classes.id from school_classes
  join schools on schools.id = school_classes.school_id
  where schools.district_id=16338
);
delete from school_classes where school_id in (
  select schools.id from schools
  where schools.district_id=16338
);
delete from students where school_id in (
  select schools.id from schools
  where schools.district_id=16338
);
delete from subscription_coordinators where school_id in (
  select schools.id from schools
  where schools.district_id=16338
);
delete from user_consents where user_id in (
  select users.id from users
  where school_id in (
    select schools.id from schools
    where schools.district_id=16338
  )
);
delete from users where school_id in (
  select schools.id from schools
  where schools.district_id=16338
);
update clever_districts set reading_clever_managed = false, maths_clever_managed = false where id = 20;
commit;

dev

begin;
delete from clever_students where clever_district_id=3346;
delete from clever_teachers where clever_district_id=3346;
delete from clever_school_classes where clever_district_id=3346;
delete from clever_schools where clever_district_id=3346;

delete from student_school_classes where school_class_id in (
  select school_classes.id from school_classes
  join schools on schools.id = school_classes.school_id
  where schools.district_id=17060
);
delete from teacher_school_classes where school_class_id in (
  select school_classes.id from school_classes
  join schools on schools.id = school_classes.school_id
  where schools.district_id=17060
);
delete from school_classes where school_id in (
  select schools.id from schools
  where schools.district_id=17060
);
delete from students where school_id in (
  select schools.id from schools
  where schools.district_id=17060
);
delete from subscription_coordinators where school_id in (
  select schools.id from schools
  where schools.district_id=17060
);
delete from user_consents where user_id in (
  select users.id from users
  where school_id in (
    select schools.id from schools
    where schools.district_id=17060
  )
);
delete from marketing_infos where user_id in (
  select users.id from users
  where school_id in (
    select schools.id from schools
    where schools.district_id=17060
  )
);
delete from users where school_id in (
  select schools.id from schools
  where schools.district_id=17060
);
update clever_districts set reading_clever_managed = false, maths_clever_managed = false where id = 3346;
commit;

Clever Sync

Find the last processed event for each district's subscription

most_recent_events =
  from(e in CleverData.Download.Event,
    group_by: [e.district_id, e.subscription_type],
    select: %{
      district_id: e.district_id,
      inserted_at: max(e.inserted_at),
      subscription_type: e.subscription_type
    },
    order_by: [asc: max(e.inserted_at)]
  )

from(e in subquery(most_recent_events),
  where: e.inserted_at < ago(3, "day")
) |> CleverData.Repo.all

Check for incomplete syncs

from(e in CleverData.Download.Synchronisation,
  join: d in assoc(e, :district),
  group_by: [e.district_id, d.name, e.subscription_type],
  select: {e.district_id, d.name, e.subscription_type, max(e.inserted_at)},
  where: e.completed == false,
  order_by: [asc: max(e.inserted_at)]
) |> CleverData.Repo.all

Git

query for stale branches

for b in $(git branch --remote); do
    git show $b --pretty="format:%ct %an $b" | head -n 1;
done | sort -n > stale-branches.txt

Bit of manual work from here, remove any current members from list

Bash

Renaming files and folders

find . -name "spanish_spanish*" | while read FNAME; do mv "$FNAME" "${FNAME//spanish_spanish/spanish}"; done

Browser

when debugging ember apps Lookup the config

JSON.parse(decodeURIComponent(document.querySelectorAll('meta[name="parent-ui-client/config/environment"]')[0].content))

Google Subs

Regex.scan(~r/purchaseToken" => (".*?")/, File.read!(Path.join(__DIR__, "google-purchase-tokens.txt"))) |> Enum.map(&List.last/1) |> Enum.map(&String.replace(&1, "\"", ""))

    from(p in Db.Parent,
      join: gps in assoc(p, :google_play_subscribers),
      where: gps.purchase_token in ^purchase_tokens,
      distinct: true
    ) |> Db.Repo.all() |> Enum.map(&Gravity.Students.FamilySubscriptions.create/1)