Database

Connect

1. Login to jumpbox.
2. Run aurora/connect.sh. Enter password when prompted (from gdrive).
3. After login, a mysql client is presented.

Queries

Discovery

> show databases;   
> use *<database>*; 
> show tables;
> show columns from *<table>*;

Statistics

All conversations created

> select count(*) from h2conversation 
  where creation > UNIX_TIMESTAMP('20160801') * 1000;

Unique conversation creators

> select count(distinct author) from h2conversation 
  where creation > UNIX_TIMESTAMP('20160801') * 1000;

Stanzas created

> select count(id) from h2ink where timestamp_c > (UNIX_TIMESTAMP('20160801') * 1000);
> select count(id) from h2multiwordtext where timestamp_c > (UNIX_TIMESTAMP('20160801') * 1000);
> select count(id) from h2image where timestamp_c > (UNIX_TIMESTAMP('20160801') * 1000);
> select count(id) from h2quizresponse where timestamp_c > (UNIX_TIMESTAMP('20160801') * 1000);

All stanzas created (informal)

> select sum(
    ( select count(id) from h2ink where timestamp_c > (UNIX_TIMESTAMP('20160801') * 1000) ) +
    ( select count(id) from h2multiwordtext where timestamp_c > (UNIX_TIMESTAMP('20160801') * 1000) ) +
    ( select count(id) from h2image where timestamp_c > (UNIX_TIMESTAMP('20160801') * 1000) )
  );

All poll answers created (formal)

> select count(id) from h2quizresponse where timestamp_c > (UNIX_TIMESTAMP('20160801') * 1000);

All stanzas created (formal and informal)

> select sum(
    ( select count(id) from h2ink where timestamp_c > (UNIX_TIMESTAMP('20160801') * 1000) ) +
    ( select count(id) from h2multiwordtext where timestamp_c > (UNIX_TIMESTAMP('20160801') * 1000) ) +
    ( select count(id) from h2image where timestamp_c > (UNIX_TIMESTAMP('20160801') * 1000) ) +
    ( select count(id) from h2quizresponse where timestamp_c > (UNIX_TIMESTAMP('20160801') * 1000) )
  );

All unique stanza creators (excluding quiz)

Incorrect - counts the same author multiple times for different stanza types

> select sum(
    ( select count(distinct author) from h2ink where timestamp_c > (UNIX_TIMESTAMP('20160801') * 1000) ) +
    ( select count(distinct author) from h2multiwordtext where timestamp_c > (UNIX_TIMESTAMP('20160801') * 1000) ) +
    ( select count(distinct author) from h2image where timestamp_c > (UNIX_TIMESTAMP('20160801') * 1000) )
  );

or:

> select count(a) from (
    select distinct author as a from h2ink where timestamp_c > (UNIX_TIMESTAMP('20160801') * 1000) 
      union
    select distinct author as a from h2multiwordtext where timestamp_c > (UNIX_TIMESTAMP('20160801') * 1000) 
      union
    select distinct author as a from h2image where timestamp_c > (UNIX_TIMESTAMP('20160801') * 1000)
  ) as bob;

All unique quiz responders

> select count(distinct author) from h2quizresponse where timestamp_c > (UNIX_TIMESTAMP('20160801') * 1000);

All unique stanza creators (including quiz)

Incorrect - counts the same author multiple times for different stanza types

> select sum(
    ( select count(distinct author) from h2ink where timestamp_c > (UNIX_TIMESTAMP('20160801') * 1000) ) +
    ( select count(distinct author) from h2multiwordtext where timestamp_c > (UNIX_TIMESTAMP('20160801') * 1000) ) +
    ( select count(distinct author) from h2image where timestamp_c > (UNIX_TIMESTAMP('20160801') * 1000) ) +
    ( select count(distinct author) from h2quizresponse where timestamp_c > (UNIX_TIMESTAMP('20160801') * 1000) )
  );

or:

> select count(a) from (
    select distinct author as a from h2ink where timestamp_c > (UNIX_TIMESTAMP('20160801') * 1000) 
      union
    select distinct author as a from h2multiwordtext where timestamp_c > (UNIX_TIMESTAMP('20160801') * 1000) 
      union
    select distinct author as a from h2image where timestamp_c > (UNIX_TIMESTAMP('20160801') * 1000) 
      union
    select distinct author as a from h2quizresponse where timestamp_c > (UNIX_TIMESTAMP('20160801') * 1000) 
  ) as bob;

Unique conversation attendees

> select count(distinct author) from h2attendance 
  where timestamp_c > UNIX_TIMESTAMP('20160801') * 1000;

Troubleshooting

Find images with bad 'source'

> select timestamp_c, from_unixtime(timestamp_c / 1000), author from h2image where source REGEXP ' ' order by timestamp_c desc;
> select timestamp_c, from_unixtime(timestamp_c / 1000), author from h2image where source REGEXP '\n' order by timestamp_c desc;

Find images for a slide

> select * from h2image where source like '%3916001%' order by timestamp_c desc limit 10;

Find student content in a course

> select r.author as 'Student', r.room, c2.title as 'Conversation', t.words from ( select a.author, s.room from ( select distinct a.author from h2conversation c join h2attendance a on c.jid = a.room  where c.jid = 3363000 and a.author not in ('ANALYST1','ANALYST2','TEACHER') and a.author = 'XXXXX' ) as a  join ( ( select author, room from h2multiwordtext ) union ( select author, room from h2ink) union ( select author, room from h2image) )  as s on a.author = s.author ) as r left join h2conversation c2 on r.room REGEXP '[0-9]+$'  and c2.jid = concat(reverse(substring(reverse(r.room),4)),'000')  left join ( select room, words, timestamp_c from h2multiwordtext  order by timestamp_c asc ) as t on c2.jid = concat(reverse(substring(reverse(t.room),4)),'000')  order by r.author, c2.title;