1.1 --- /dev/null	Thu Jan 01 00:00:00 1970 +0000
     1.2 +++ b/src/sql/queries.sql	Wed Oct 31 22:59:35 2007 +0100
     1.3 @@ -0,0 +1,71 @@
     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]
    1.26 +select UserName, Argument as UserUrl, Time as Begin,
    1.27 +unix_timestamp((select Time from UserLogger where step='UI_STOP_SESSION' and session=ul1.session)) - unix_timestamp(ul1.time) as Duration,
    1.28 +(select count(*) from UserLogger where step='LO_OPEN_WORKSHEET' and session=ul1.session) as Examples
    1.29 +from UserLogger ul1
    1.30 +where step='LO_START_SESSION';
    1.31 +
    1.32 +~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    1.33 +[2]
    1.34 +select session, Argument as ID, FormulaTo as Example, Time as Begin,
    1.35 +unix_timestamp((select Time from UserLogger where step='LO_STOP_EXAMPLE' and session=ul1.session limit 1)) - unix_timestamp(ul1.time) as Duration,
    1.36 +(select Success from UserLogger where step='LO_STOP_EXAMPLE' and session=ul1.session limit 1) as Success,
    1.37 +(select count(*) from UserLogger where step like 'UI_SOLVE%' and session=ul1.session limit 1) as Calc_Steps,
    1.38 +(select count(*) from UserLogger where step not like 'UI_SOLVE%' and session=ul1.session limit 1) as Other_Steps
    1.39 +from UserLogger ul1
    1.40 +where UserName='x' and Step='LO_OPEN_WORKSHEET';
    1.41 +
    1.42 +~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    1.43 +[3]
    1.44 +select Dialog, Step, Time, Worksheet, Position, FormulaFrom, FormulaTo, Argument, Substitution, Success
    1.45 +from UserLogger
    1.46 +where Session=0
    1.47 +order by Time;
    1.48 +
    1.49 +~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    1.50 +[4]
    1.51 +select Dialog, Step, Time, Worksheet, Position, FormulaFrom, FormulaTo, Argument, Substitution, Success
    1.52 +from UserLogger
    1.53 +where Worksheet='1_0'
    1.54 +order by Time;
    1.55 +
    1.56 +~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    1.57 +[5]
    1.58 +select Argument as ID, FormulaTo as Example, Success, Success-1 as Failure
    1.59 +from UserLogger
    1.60 +where Step='LO_OPEN_WORKSHEET'
    1.61 +order by Success, Failure;
    1.62 +
    1.63 +~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    1.64 +[6]
    1.65 +select UserName, Argument as UserUrl,
    1.66 +(select count(*) from UserLogger where step='LO_OPEN_WORKSHEET' and session=ul1.session limit 1) as Examples,
    1.67 +(select Success from UserLogger where step='LO_STOP_EXAMPLE' and session=ul1.session limit 1) as Success,
    1.68 +(select count(*) from UserLogger where step like 'UI_SOLVE%' and session=ul1.session limit 1) as Calc_Steps,
    1.69 +(select count(*) from UserLogger where step not like 'UI_SOLVE%' and session=ul1.session limit 1) as Other_Steps
    1.70 +from UserLogger ul1
    1.71 +where Step='LO_START_SESSION'
    1.72 +order by Examples, UserName;
    1.73 +
    1.74 +~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~