1 protocol of the ISAC-meeting
2 ############################################################################
3 date of meeting: 30.11.07
5 date of writing: 30.11.07 12:00
7 agenda: check the queries
8 ############################################################################
10 fields and values are commented only if there is something to be checked.
11 comments give the right value and relate to line-numbers of output #1, #2, ...
12 ~6 is an estimated value (meaning approximately 6)
13 ! marks an apparent error in the query
14 ? marks wrong values to be checked for the origin, the data logged and/or the query
17 [1] who has used isac ?
18 mysql> select UserName, Argument as UserUrl, Time as Begin,
19 -> unix_timestamp((select Time from UserLogger where step='UI_STOP_SESSION' and session=ul1.session)) - unix_timestamp(ul1.time) as Duration,
20 -> (select count(*) from UserLogger where step='LO_OPEN_WORKSHEET' and session=ul1.session) as Examples
21 -> from UserLogger ul1
22 -> where step='LO_START_SESSION';
23 +----------+---------------+---------------------+----------+----------+
24 | UserName | UserUrl | Begin | Duration | Examples |
25 +----------+---------------+---------------------+----------+----------+
26 | x | TODO user-url | 2007-10-30 11:35:12 | 138 | 3 |
27 #2| htl | TODO user-url | 2007-10-30 11:37:39 | 52 | 1 |
28 #3| x | TODO user-url | 2007-10-30 11:38:53 | 334 | 1 |
29 | krempler | TODO user-url | 2007-10-30 11:39:37 | 101 | 2 |
30 | x | TODO user-url | 2007-10-30 11:41:26 | 151 | 2 |
31 +----------+---------------+---------------------+----------+----------+
32 5 rows in set (0.07 sec)
39 [2] which examples has a user calculated (over all sessions) ?
40 mysql> select session, Argument as ID, FormulaTo as Example, Time as Begin,
41 -> unix_timestamp((select Time from UserLogger where step='LO_STOP_EXAMPLE' and session=ul1.session limit 1)) - unix_timestamp(ul1.time) as Duration,
42 -> (select Success from UserLogger where step='LO_STOP_EXAMPLE' and session=ul1.session limit 1) as Success,
43 -> (select count(*) from UserLogger where step like 'UI_SOLVE%' and session=ul1.session limit 1) as Calc_Steps,
44 -> (select count(*) from UserLogger where step not like 'UI_SOLVE%' and session=ul1.session limit 1) as Other_Steps
45 -> from UserLogger ul1
46 -> where UserName='x' and Step='LO_OPEN_WORKSHEET';
47 +---------+--------------------------------------+------------------------------------------------------------------+---------------------+----------+---------+------------+-------------+
48 | session | ID | Example | Begin | Duration | Success | Calc_Steps | Other_Steps |
49 +---------+--------------------------------------+------------------------------------------------------------------+---------------------+----------+---------+------------+-------------+
50 #1| 2 | exp_IsacCore_Tests_1a.xml | solve (1 + -1 * 2 + x = 0, x) | 2007-10-30 11:35:22 | 2 | 0 | 5 | 11 |
51 #2| 2 | exp_IsacCore_Tests_1b.xml | solve (x + 1 = 2, x) | 2007-10-30 11:35:28 | -4 | 0 | 5 | 11 |
52 #3| 2 | exp_IsacCore_Tests_1c.xml | solve (x / (x ^ 2 - 6 * x + 9) - 1 / (x ^ 2 - 3 * x) = 1 / x, x) | 2007-10-30 11:37:21 | -117 | 0 | 5 | 11 |
53 #4| 4 | NULL | solve (1 + -1 * 2 + x = 0, x) | 2007-10-30 11:39:04 | 2 | 0 | 2 | 7 |
54 #5| 6 | exp_IsacCore_CalcDiff_Stein-359a.xml | Differentiate (P = 3 * z ^ 2 - 4 * z, z) | 2007-10-30 11:41:49 | 70 | 1 | 3 | 8 |
55 #6| 6 | exp_IsacCore_CalcDiff_Stein-359b.xml | Differentiate (s = pi * z ^ 3 + 6 / z, z) | 2007-10-30 11:43:05 | -6 | 1 | 3 | 8 |
56 +---------+--------------------------------------+------------------------------------------------------------------+---------------------+----------+---------+------------+-------------+
57 6 rows in set (0.04 sec)
60 !# ID in #4 indicates the queries problems handling a user with more than 1 session open (x has 3 examples started by ComPod in session 4 which is closed _after_ session 6 by x)
62 ?# Success 1 in #2, #3
63 ?# Calc_Steps 0 in #1, ~6 in #2, 1 in #3
66 [3] what did a user do during a certain session ?
67 mysql> select Dialog, Step, Time, Worksheet, Position, FormulaFrom, FormulaTo, Argument, Substitution, Success
71 +-----------+---------------------------------------+---------------------+-----------+-----------+------------------------------------------------------------------+------------------------------------------------------------------+---------------------------+--------------+---------+
72 | Dialog | Step | Time | Worksheet | Position | FormulaFrom | FormulaTo | Argument | Substitution | Success |
73 +-----------+---------------------------------------+---------------------+-----------+-----------+------------------------------------------------------------------+------------------------------------------------------------------+---------------------------+--------------+---------+
74 #1| NULL | LO_START_SESSION | 2007-10-30 11:35:12 | NULL | NULL | NULL | NULL | TODO user-url | NULL | 0 |
75 #2| exp | LO_OPEN_WORKSHEET | 2007-10-30 11:35:22 | 2_0 | ([],Pbl) | solve (1 + -1 * 2 + x = 0, x) | solve (1 + -1 * 2 + x = 0, x) | exp_IsacCore_Tests_1a.xml | NULL | 1 |
76 #3| worksheet | LO_STOP_EXAMPLE | 2007-10-30 11:35:24 | 2_0 | NULL | NULL | NULL | NULL | NULL | 0 |
77 #4| worksheet | UI_CLOSE_WORKSHEET | 2007-10-30 11:35:24 | 2_0 | NULL | NULL | NULL | NULL | NULL | 0 |
78 #5| exp | LO_OPEN_WORKSHEET | 2007-10-30 11:35:28 | 2_1 | ([],Pbl) | solve (x + 1 = 2, x) | solve (x + 1 = 2, x) | exp_IsacCore_Tests_1b.xml | NULL | 1 |
79 #6| worksheet | UI_SOLVE_CALCULATE_1 | 2007-10-30 11:35:37 | 2_1 | ([],Met) | solve (x + 1 = 2, x) | x + 1 = 2 | NULL | NULL | 1 |
80 #7| worksheet | UI_SOLVE_CALCULATE_1 | 2007-10-30 11:35:39 | 2_1 | ([1],Frm) | x + 1 = 2 | x + 1 + -1 * 2 = 0 | NULL | NULL | 1 |
81 #8| worksheet | UI_SOLVE_EDIT_ACTIVE_FORMULA_COMPLETE | 2007-10-30 11:36:54 | 2_1 | ([1],Res) | x + 1 + -1 * 2 = 0 | x - 1 = 0 | x - 1 = 0 | NULL | 1 |
82 #9| worksheet | UI_SOLVE_CALCULATE_ALL | 2007-10-30 11:37:08 | 2_1 | ([2],Res) | x - 1 = 0 | [x = 1] | NULL | NULL | 1 |
83 #10| worksheet | LO_STOP_EXAMPLE | 2007-10-30 11:37:08 | 2_1 | NULL | NULL | NULL | NULL | NULL | 1 |
84 #11| worksheet | UI_CLOSE_WORKSHEET | 2007-10-30 11:37:14 | 2_1 | NULL | NULL | NULL | NULL | NULL | 0 |
85 #12| exp | LO_OPEN_WORKSHEET | 2007-10-30 11:37:21 | 2_2 | ([],Pbl) | solve (x / (x ^ 2 - 6 * x + 9) - 1 / (x ^ 2 - 3 * x) = 1 / x,x) | solve (x / (x ^ 2 - 6 * x + 9) - 1 / (x ^ 2 - 3 * x) = 1 / x, x) | exp_IsacCore_Tests_1c.xml | NULL | 1 |
86 #13| worksheet | UI_SOLVE_CALCULATE_ALL | 2007-10-30 11:37:23 | 2_2 | ([],Pbl) | solve (x / (x ^ 2 - 6 * x + 9) - 1 / (x ^ 2 - 3 * x) = 1 / x,x) | [x = 6 / 5] | NULL | NULL | 1 |
87 #14| worksheet | LO_STOP_EXAMPLE | 2007-10-30 11:37:23 | 2_2 | NULL | NULL | NULL | NULL | NULL | 1 |
88 #15| worksheet | UI_CLOSE_WORKSHEET | 2007-10-30 11:37:28 | 2_2 | NULL | NULL | NULL | NULL | NULL | 0 |
89 #16| NULL | UI_STOP_SESSION | 2007-10-30 11:37:30 | NULL | NULL | NULL | NULL | NULL | NULL | 0 |
90 +-----------+---------------------------------------+---------------------+-----------+-----------+------------------------------------------------------------------+------------------------------------------------------------------+---------------------------+--------------+---------+
91 16 rows in set (0.00 sec)
94 ?#8 2 wrong inputs before this step are not logged
97 [4] what did a user do during a certain example ?
98 mysql> select Dialog, Step, Time, Worksheet, Position, FormulaFrom, FormulaTo, Argument, Substitution, Success
100 -> where Worksheet='2_1'
102 +-----------+---------------------------------------+---------------------+-----------+-----------+----------------------+----------------------+---------------------------+--------------+---------+
103 | Dialog | Step | Time | Worksheet | Position | FormulaFrom | FormulaTo | Argument | Substitution | Success |
104 +-----------+---------------------------------------+---------------------+-----------+-----------+----------------------+----------------------+---------------------------+--------------+---------+
105 | exp | LO_OPEN_WORKSHEET | 2007-10-30 11:35:28 | 2_1 | ([],Pbl) | solve (x + 1 = 2, x) | solve (x + 1 = 2, x) | exp_IsacCore_Tests_1b.xml | NULL | 1 |
106 | worksheet | UI_SOLVE_CALCULATE_1 | 2007-10-30 11:35:37 | 2_1 | ([],Met) | solve (x + 1 = 2, x) | x + 1 = 2 | NULL | NULL | 1 |
107 | worksheet | UI_SOLVE_CALCULATE_1 | 2007-10-30 11:35:39 | 2_1 | ([1],Frm) | x + 1 = 2 | x + 1 + -1 * 2 = 0 | NULL | NULL | 1 |
108 #4| worksheet | UI_SOLVE_EDIT_ACTIVE_FORMULA_COMPLETE | 2007-10-30 11:36:54 | 2_1 | ([1],Res) | x + 1 + -1 * 2 = 0 | x - 1 = 0 | x - 1 = 0 | NULL | 1 |
109 | worksheet | UI_SOLVE_CALCULATE_ALL | 2007-10-30 11:37:08 | 2_1 | ([2],Res) | x - 1 = 0 | [x = 1] | NULL | NULL | 1 |
110 | worksheet | LO_STOP_EXAMPLE | 2007-10-30 11:37:08 | 2_1 | NULL | NULL | NULL | NULL | NULL | 1 |
111 | worksheet | UI_CLOSE_WORKSHEET | 2007-10-30 11:37:14 | 2_1 | NULL | NULL | NULL | NULL | NULL | 0 |
112 +-----------+---------------------------------------+---------------------+-----------+-----------+----------------------+----------------------+---------------------------+--------------+---------+
113 7 rows in set (0.02 sec)
116 !# Worksheet is superfluous
117 ?#4 2 error-input before this step have not been recorded by UserLogger
120 [5] how difficult are the examples ?
121 mysql> select Argument as ID, FormulaTo as Example, Success, Success-1 as Failure
123 -> where Step='LO_OPEN_WORKSHEET'
124 -> order by Success, Failure;
125 +--------------------------------------+------------------------------------------------------------------+---------+---------+
126 | ID | Example | Success | Failure |
127 +--------------------------------------+------------------------------------------------------------------+---------+---------+
128 #1| exp_IsacCore_Tests_1a.xml | solve (1 + -1 * 2 + x = 0, x) | 1 | 0 |
129 #2| exp_IsacCore_Tests_1b.xml | solve (x + 1 = 2, x) | 1 | 0 |
130 #3| exp_IsacCore_Tests_1c.xml | solve (x / (x ^ 2 - 6 * x + 9) - 1 / (x ^ 2 - 3 * x) = 1 / x, x) | 1 | 0 |
131 #4| NULL | solve (1 + -1 * 2 + x = 0, x) | 1 | 0 |
132 #5| NULL | solve (1 + -1 * 2 + x = 0, x) | 1 | 0 |
133 #6| exp_IsacCore_CalcDiff_Stein-359a.xml | Differentiate (P = 3 * z ^ 2 - 4 * z, z) | 1 | 0 |
134 #7| exp_IsacCore_CalcDiff_Stein-359b.xml | Differentiate (s = pi * z ^ 3 + 6 / z, z) | 1 | 0 |
135 #8| exp_IsacCore_CalcDiff_Stein-359a.xml | Differentiate (P = 3 * z ^ 2 - 4 * z, z) | 1 | 0 |
136 #9| exp_IsacCore_CalcDiff_Stein-359b.xml | Differentiate (s = pi * z ^ 3 + 6 / z, z) | 1 | 0 |
137 +--------------------------------------+------------------------------------------------------------------+---------+---------+
138 9 rows in set (0.04 sec)
148 ?#4 wrong entry steming from overlapping session (??, and exp's handled by ComPod)
149 ?#5 wrong entry steming from overlapping session (??, and exp's handled by ComPod)
152 !#8 each example should be listed once
153 !#9 each example should be listed once
156 [6] who are the most frequent users of isac ?
157 mysql> select UserName, Argument as UserUrl,
158 -> (select count(*) from UserLogger where step='LO_OPEN_WORKSHEET' and session=ul1.session limit 1) as Examples,
159 -> (select Success from UserLogger where step='LO_STOP_EXAMPLE' and session=ul1.session limit 1) as Success,
160 -> (select count(*) from UserLogger where step like 'UI_SOLVE%' and session=ul1.session limit 1) as Calc_Steps,
161 -> (select count(*) from UserLogger where step not like 'UI_SOLVE%' and session=ul1.session limit 1) as Other_Steps
162 -> from UserLogger ul1
163 -> where Step='LO_START_SESSION'
164 -> order by Examples, UserName;
165 +----------+---------------+----------+---------+------------+-------------+
166 | UserName | UserUrl | Examples | Success | Calc_Steps | Other_Steps |
167 +----------+---------------+----------+---------+------------+-------------+
168 | htl | TODO user-url | 1 | 0 | 2 | 7 |
169 | x | TODO user-url | 1 | 0 | 2 | 7 |
170 | krempler | TODO user-url | 2 | 1 | 2 | 8 |
171 | x | TODO user-url | 2 | 1 | 3 | 8 |
172 | x | TODO user-url | 3 | 0 | 5 | 11 |
173 +----------+---------------+----------+---------+------------+-------------+
174 5 rows in set (0.00 sec)
177 ! each UserName should occur _once_
178 ! add the field Session between UserUrl | Examples for counting the number of sessions the user had.