1.1 --- a/src/sql/queries.txt	Wed Oct 31 23:02:55 2007 +0100
     1.2 +++ /dev/null	Thu Jan 01 00:00:00 1970 +0000
     1.3 @@ -1,77 +0,0 @@
     1.4 -Commands and queries for isac's UserLogger
     1.5 -==========================================
     1.6 -su
     1.7 -/opt/lampp/lampp start
     1.8 -exit
     1.9 -     ### start interactive db-mode
    1.10 - cd /opt/lampp/bin
    1.11 - ./mysql -uroot                  
    1.12 - use userlogger
    1.13 -     ### redirect output to a file (because a record is longer than a line in the default output):
    1.14 - tee /home/neuper/tmp/output.tex
    1.15 - 
    1.16 -> delete from UserLogger;
    1.17 -
    1.18 -example queries
    1.19 -===============
    1.20 -get all:
    1.21 -~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    1.22 -> select * from UserLogger;
    1.23 -
    1.24 -~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    1.25 -[1] who has used isac ?
    1.26 -
    1.27 -select UserName, Argument as UserUrl, Time as Begin,
    1.28 -unix_timestamp((select Time from UserLogger where step='UI_STOP_SESSION' and session=ul1.session)) - unix_timestamp(ul1.time) as Duration,
    1.29 -(select count(*) from UserLogger where step='LO_OPEN_WORKSHEET' and session=ul1.session) as Examples
    1.30 -from UserLogger ul1
    1.31 -where step='LO_START_SESSION';
    1.32 -
    1.33 -~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    1.34 -[2] which examples has a user calculated (over all sessions) ?
    1.35 -
    1.36 -select session, Argument as ID, FormulaTo as Example, Time as Begin,
    1.37 -unix_timestamp((select Time from UserLogger where step='LO_STOP_EXAMPLE' and session=ul1.session limit 1)) - unix_timestamp(ul1.time) as Duration,
    1.38 -(select Success from UserLogger where step='LO_STOP_EXAMPLE' and session=ul1.session limit 1) as Success,
    1.39 -(select count(*) from UserLogger where step like 'UI_SOLVE%' and session=ul1.session limit 1) as Calc_Steps,
    1.40 -(select count(*) from UserLogger where step not like 'UI_SOLVE%' and session=ul1.session limit 1) as Other_Steps
    1.41 -from UserLogger ul1
    1.42 -where UserName='x' and Step='LO_OPEN_WORKSHEET';
    1.43 -
    1.44 -~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    1.45 -[3] what did a user do during a certain session ?
    1.46 -
    1.47 -select Dialog, Step, Time, Worksheet, Position, FormulaFrom, FormulaTo, Argument, Substitution, Success
    1.48 -from UserLogger
    1.49 -where Session=2
    1.50 -order by Time;
    1.51 -
    1.52 -~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    1.53 -[4] what did a user do during a certain example ?
    1.54 -
    1.55 -select Dialog, Step, Time, Worksheet, Position, FormulaFrom, FormulaTo, Argument, Substitution, Success
    1.56 -from UserLogger
    1.57 -where Worksheet='2_1'
    1.58 -order by Time;
    1.59 -
    1.60 -~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    1.61 -[5] how difficult are the examples ?
    1.62 -
    1.63 -select Argument as ID, FormulaTo as Example, Success, Success-1 as Failure
    1.64 -from UserLogger
    1.65 -where Step='LO_OPEN_WORKSHEET'
    1.66 -order by Success, Failure;
    1.67 -
    1.68 -~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    1.69 -[6] who are the most frequent users of isac ?
    1.70 -
    1.71 -select UserName, Argument as UserUrl,
    1.72 -(select count(*) from UserLogger where step='LO_OPEN_WORKSHEET' and session=ul1.session limit 1) as Examples,
    1.73 -(select Success from UserLogger where step='LO_STOP_EXAMPLE' and session=ul1.session limit 1) as Success,
    1.74 -(select count(*) from UserLogger where step like 'UI_SOLVE%' and session=ul1.session limit 1) as Calc_Steps,
    1.75 -(select count(*) from UserLogger where step not like 'UI_SOLVE%' and session=ul1.session limit 1) as Other_Steps
    1.76 -from UserLogger ul1
    1.77 -where Step='LO_START_SESSION'
    1.78 -order by Examples, UserName;
    1.79 -
    1.80 -~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~