wneuper@246
|
1 |
protocol of the ISAC-meeting
|
wneuper@246
|
2 |
############################################################################
|
wneuper@246
|
3 |
date of meeting: 30.11.07
|
wneuper@246
|
4 |
author: WN
|
wneuper@246
|
5 |
date of writing: 30.11.07 12:00
|
wneuper@246
|
6 |
attendees: mail
|
wneuper@246
|
7 |
agenda: check the queries
|
wneuper@246
|
8 |
############################################################################
|
wneuper@246
|
9 |
|
wneuper@246
|
10 |
fields and values are commented only if there is something to be checked.
|
wneuper@246
|
11 |
comments give the right value and relate to line-numbers of output #1, #2, ...
|
wneuper@246
|
12 |
~6 is an estimated value (meaning approximately 6)
|
wneuper@246
|
13 |
! marks an apparent error in the query
|
wneuper@246
|
14 |
? marks wrong values to be checked for the origin, the data logged and/or the query
|
wneuper@246
|
15 |
|
wneuper@246
|
16 |
|
wneuper@246
|
17 |
[1] who has used isac ?
|
wneuper@246
|
18 |
mysql> select UserName, Argument as UserUrl, Time as Begin,
|
wneuper@246
|
19 |
-> unix_timestamp((select Time from UserLogger where step='UI_STOP_SESSION' and session=ul1.session)) - unix_timestamp(ul1.time) as Duration,
|
wneuper@246
|
20 |
-> (select count(*) from UserLogger where step='LO_OPEN_WORKSHEET' and session=ul1.session) as Examples
|
wneuper@246
|
21 |
-> from UserLogger ul1
|
wneuper@246
|
22 |
-> where step='LO_START_SESSION';
|
wneuper@246
|
23 |
+----------+---------------+---------------------+----------+----------+
|
wneuper@246
|
24 |
| UserName | UserUrl | Begin | Duration | Examples |
|
wneuper@246
|
25 |
+----------+---------------+---------------------+----------+----------+
|
wneuper@246
|
26 |
| x | TODO user-url | 2007-10-30 11:35:12 | 138 | 3 |
|
wneuper@246
|
27 |
#2| htl | TODO user-url | 2007-10-30 11:37:39 | 52 | 1 |
|
wneuper@246
|
28 |
#3| x | TODO user-url | 2007-10-30 11:38:53 | 334 | 1 |
|
wneuper@246
|
29 |
| krempler | TODO user-url | 2007-10-30 11:39:37 | 101 | 2 |
|
wneuper@246
|
30 |
| x | TODO user-url | 2007-10-30 11:41:26 | 151 | 2 |
|
wneuper@246
|
31 |
+----------+---------------+---------------------+----------+----------+
|
wneuper@246
|
32 |
5 rows in set (0.07 sec)
|
wneuper@246
|
33 |
|
wneuper@246
|
34 |
comments:
|
wneuper@246
|
35 |
?#2: 3 examples
|
wneuper@246
|
36 |
?#3: 3 examples
|
wneuper@246
|
37 |
|
wneuper@246
|
38 |
|
wneuper@246
|
39 |
[2] which examples has a user calculated (over all sessions) ?
|
wneuper@246
|
40 |
mysql> select session, Argument as ID, FormulaTo as Example, Time as Begin,
|
wneuper@246
|
41 |
-> unix_timestamp((select Time from UserLogger where step='LO_STOP_EXAMPLE' and session=ul1.session limit 1)) - unix_timestamp(ul1.time) as Duration,
|
wneuper@246
|
42 |
-> (select Success from UserLogger where step='LO_STOP_EXAMPLE' and session=ul1.session limit 1) as Success,
|
wneuper@246
|
43 |
-> (select count(*) from UserLogger where step like 'UI_SOLVE%' and session=ul1.session limit 1) as Calc_Steps,
|
wneuper@246
|
44 |
-> (select count(*) from UserLogger where step not like 'UI_SOLVE%' and session=ul1.session limit 1) as Other_Steps
|
wneuper@246
|
45 |
-> from UserLogger ul1
|
wneuper@246
|
46 |
-> where UserName='x' and Step='LO_OPEN_WORKSHEET';
|
wneuper@246
|
47 |
+---------+--------------------------------------+------------------------------------------------------------------+---------------------+----------+---------+------------+-------------+
|
wneuper@246
|
48 |
| session | ID | Example | Begin | Duration | Success | Calc_Steps | Other_Steps |
|
wneuper@246
|
49 |
+---------+--------------------------------------+------------------------------------------------------------------+---------------------+----------+---------+------------+-------------+
|
wneuper@246
|
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 |
|
wneuper@246
|
51 |
#2| 2 | exp_IsacCore_Tests_1b.xml | solve (x + 1 = 2, x) | 2007-10-30 11:35:28 | -4 | 0 | 5 | 11 |
|
wneuper@246
|
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 |
|
wneuper@246
|
53 |
#4| 4 | NULL | solve (1 + -1 * 2 + x = 0, x) | 2007-10-30 11:39:04 | 2 | 0 | 2 | 7 |
|
wneuper@246
|
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 |
|
wneuper@246
|
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 |
|
wneuper@246
|
56 |
+---------+--------------------------------------+------------------------------------------------------------------+---------------------+----------+---------+------------+-------------+
|
wneuper@246
|
57 |
6 rows in set (0.04 sec)
|
wneuper@246
|
58 |
|
wneuper@246
|
59 |
comments:
|
wneuper@246
|
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)
|
wneuper@246
|
61 |
!# Duration
|
wneuper@246
|
62 |
?# Success 1 in #2, #3
|
wneuper@246
|
63 |
?# Calc_Steps 0 in #1, ~6 in #2, 1 in #3
|
wneuper@246
|
64 |
?# Other _Steps ???
|
wneuper@246
|
65 |
|
wneuper@246
|
66 |
[3] what did a user do during a certain session ?
|
wneuper@246
|
67 |
mysql> select Dialog, Step, Time, Worksheet, Position, FormulaFrom, FormulaTo, Argument, Substitution, Success
|
wneuper@246
|
68 |
-> from UserLogger
|
wneuper@246
|
69 |
-> where Session=2
|
wneuper@246
|
70 |
-> order by Time;
|
wneuper@246
|
71 |
+-----------+---------------------------------------+---------------------+-----------+-----------+------------------------------------------------------------------+------------------------------------------------------------------+---------------------------+--------------+---------+
|
wneuper@246
|
72 |
| Dialog | Step | Time | Worksheet | Position | FormulaFrom | FormulaTo | Argument | Substitution | Success |
|
wneuper@246
|
73 |
+-----------+---------------------------------------+---------------------+-----------+-----------+------------------------------------------------------------------+------------------------------------------------------------------+---------------------------+--------------+---------+
|
wneuper@246
|
74 |
#1| NULL | LO_START_SESSION | 2007-10-30 11:35:12 | NULL | NULL | NULL | NULL | TODO user-url | NULL | 0 |
|
wneuper@246
|
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 |
|
wneuper@246
|
76 |
#3| worksheet | LO_STOP_EXAMPLE | 2007-10-30 11:35:24 | 2_0 | NULL | NULL | NULL | NULL | NULL | 0 |
|
wneuper@246
|
77 |
#4| worksheet | UI_CLOSE_WORKSHEET | 2007-10-30 11:35:24 | 2_0 | NULL | NULL | NULL | NULL | NULL | 0 |
|
wneuper@246
|
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 |
|
wneuper@246
|
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 |
|
wneuper@246
|
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 |
|
wneuper@246
|
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 |
|
wneuper@246
|
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 |
|
wneuper@246
|
83 |
#10| worksheet | LO_STOP_EXAMPLE | 2007-10-30 11:37:08 | 2_1 | NULL | NULL | NULL | NULL | NULL | 1 |
|
wneuper@246
|
84 |
#11| worksheet | UI_CLOSE_WORKSHEET | 2007-10-30 11:37:14 | 2_1 | NULL | NULL | NULL | NULL | NULL | 0 |
|
wneuper@246
|
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 |
|
wneuper@246
|
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 |
|
wneuper@246
|
87 |
#14| worksheet | LO_STOP_EXAMPLE | 2007-10-30 11:37:23 | 2_2 | NULL | NULL | NULL | NULL | NULL | 1 |
|
wneuper@246
|
88 |
#15| worksheet | UI_CLOSE_WORKSHEET | 2007-10-30 11:37:28 | 2_2 | NULL | NULL | NULL | NULL | NULL | 0 |
|
wneuper@246
|
89 |
#16| NULL | UI_STOP_SESSION | 2007-10-30 11:37:30 | NULL | NULL | NULL | NULL | NULL | NULL | 0 |
|
wneuper@246
|
90 |
+-----------+---------------------------------------+---------------------+-----------+-----------+------------------------------------------------------------------+------------------------------------------------------------------+---------------------------+--------------+---------+
|
wneuper@246
|
91 |
16 rows in set (0.00 sec)
|
wneuper@246
|
92 |
|
wneuper@246
|
93 |
comments:
|
wneuper@246
|
94 |
?#8 2 wrong inputs before this step are not logged
|
wneuper@246
|
95 |
|
wneuper@246
|
96 |
|
wneuper@246
|
97 |
[4] what did a user do during a certain example ?
|
wneuper@246
|
98 |
mysql> select Dialog, Step, Time, Worksheet, Position, FormulaFrom, FormulaTo, Argument, Substitution, Success
|
wneuper@246
|
99 |
-> from UserLogger
|
wneuper@246
|
100 |
-> where Worksheet='2_1'
|
wneuper@246
|
101 |
-> order by Time;
|
wneuper@246
|
102 |
+-----------+---------------------------------------+---------------------+-----------+----------------------+----------------------+---------------------------+--------------+---------+
|
wneuper@246
|
103 |
| Dialog | Step | Time | Position | FormulaFrom | FormulaTo | Argument | Substitution | Success |
|
wneuper@246
|
104 |
+-----------+---------------------------------------+---------------------+-----------+----------------------+----------------------+---------------------------+--------------+---------+
|
wneuper@246
|
105 |
| exp | LO_OPEN_WORKSHEET | 2007-10-30 11:35:28 | ([],Pbl) | solve (x + 1 = 2, x) | solve (x + 1 = 2, x) | exp_IsacCore_Tests_1b.xml | NULL | 1 |
|
wneuper@246
|
106 |
| worksheet | UI_SOLVE_CALCULATE_1 | 2007-10-30 11:35:37 | ([],Met) | solve (x + 1 = 2, x) | x + 1 = 2 | NULL | NULL | 1 |
|
wneuper@246
|
107 |
| worksheet | UI_SOLVE_CALCULATE_1 | 2007-10-30 11:35:39 | ([1],Frm) | x + 1 = 2 | x + 1 + -1 * 2 = 0 | NULL | NULL | 1 |
|
wneuper@246
|
108 |
#4| worksheet | UI_SOLVE_EDIT_ACTIVE_FORMULA_COMPLETE | 2007-10-30 11:36:54 | ([1],Res) | x + 1 + -1 * 2 = 0 | x - 1 = 0 | x - 1 = 0 | NULL | 1 |
|
wneuper@246
|
109 |
| worksheet | UI_SOLVE_CALCULATE_ALL | 2007-10-30 11:37:08 | ([2],Res) | x - 1 = 0 | [x = 1] | NULL | NULL | 1 |
|
wneuper@246
|
110 |
| worksheet | LO_STOP_EXAMPLE | 2007-10-30 11:37:08 | NULL | NULL | NULL | NULL | NULL | 1 |
|
wneuper@246
|
111 |
| worksheet | UI_CLOSE_WORKSHEET | 2007-10-30 11:37:14 | NULL | NULL | NULL | NULL | NULL | 0 |
|
wneuper@246
|
112 |
+-----------+---------------------------------------+---------------------+-----------+----------------------+----------------------+---------------------------+--------------+---------+
|
wneuper@246
|
113 |
7 rows in set (0.02 sec)
|
wneuper@246
|
114 |
|
wneuper@246
|
115 |
comments:
|
wneuper@246
|
116 |
?#4 2 error-input before this step have not been recorded by UserLogger
|
wneuper@246
|
117 |
|
wneuper@246
|
118 |
|
wneuper@246
|
119 |
[5] how difficult are the examples ?
|
wneuper@246
|
120 |
mysql> select Argument as ID, FormulaTo as Example, Success, Success-1 as Failure
|
wneuper@246
|
121 |
-> from UserLogger
|
wneuper@246
|
122 |
-> where Step='LO_OPEN_WORKSHEET'
|
wneuper@246
|
123 |
-> order by Success, Failure;
|
wneuper@246
|
124 |
+--------------------------------------+------------------------------------------------------------------+---------+---------+
|
wneuper@246
|
125 |
| ID | Example | Success | Failure |
|
wneuper@246
|
126 |
+--------------------------------------+------------------------------------------------------------------+---------+---------+
|
wneuper@246
|
127 |
#1| exp_IsacCore_Tests_1a.xml | solve (1 + -1 * 2 + x = 0, x) | 1 | 0 |
|
wneuper@246
|
128 |
#2| exp_IsacCore_Tests_1b.xml | solve (x + 1 = 2, x) | 1 | 0 |
|
wneuper@246
|
129 |
#3| exp_IsacCore_Tests_1c.xml | solve (x / (x ^ 2 - 6 * x + 9) - 1 / (x ^ 2 - 3 * x) = 1 / x, x) | 1 | 0 |
|
wneuper@246
|
130 |
#4| NULL | solve (1 + -1 * 2 + x = 0, x) | 1 | 0 |
|
wneuper@246
|
131 |
#5| NULL | solve (1 + -1 * 2 + x = 0, x) | 1 | 0 |
|
wneuper@246
|
132 |
#6| exp_IsacCore_CalcDiff_Stein-359a.xml | Differentiate (P = 3 * z ^ 2 - 4 * z, z) | 1 | 0 |
|
wneuper@246
|
133 |
#7| exp_IsacCore_CalcDiff_Stein-359b.xml | Differentiate (s = pi * z ^ 3 + 6 / z, z) | 1 | 0 |
|
wneuper@246
|
134 |
#8| exp_IsacCore_CalcDiff_Stein-359a.xml | Differentiate (P = 3 * z ^ 2 - 4 * z, z) | 1 | 0 |
|
wneuper@246
|
135 |
#9| exp_IsacCore_CalcDiff_Stein-359b.xml | Differentiate (s = pi * z ^ 3 + 6 / z, z) | 1 | 0 |
|
wneuper@246
|
136 |
+--------------------------------------+------------------------------------------------------------------+---------+---------+
|
wneuper@246
|
137 |
9 rows in set (0.04 sec)
|
wneuper@246
|
138 |
|
wneuper@246
|
139 |
comments:
|
wneuper@246
|
140 |
correct would be:
|
wneuper@246
|
141 |
---------+---------+
|
wneuper@246
|
142 |
Success | Failure |
|
wneuper@246
|
143 |
---------+---------+
|
wneuper@246
|
144 |
!#1 0 | 3 |
|
wneuper@246
|
145 |
!#2 3 | 0 |
|
wneuper@246
|
146 |
!#3 3 | 0 |
|
wneuper@246
|
147 |
?#4 wrong entry steming from overlapping session (??, and exp's handled by ComPod)
|
wneuper@246
|
148 |
?#5 wrong entry steming from overlapping session (??, and exp's handled by ComPod)
|
wneuper@246
|
149 |
!#6 2 | 0 |
|
wneuper@246
|
150 |
!#7 2 | 0 |
|
wneuper@246
|
151 |
!#8 each example should be listed once
|
wneuper@246
|
152 |
!#9 each example should be listed once
|
wneuper@246
|
153 |
|
wneuper@246
|
154 |
|
wneuper@246
|
155 |
[6] who are the most frequent users of isac ?
|
wneuper@246
|
156 |
mysql> select UserName,
|
wneuper@246
|
157 |
-> (select count(*) from UserLogger where step='LO_OPEN_WORKSHEET' and username=ul1.username limit 1) as Examples,
|
wneuper@246
|
158 |
-> (select count(*) from UserLogger where step='LO_STOP_EXAMPLE' and username=ul1.username limit 1) as Success,
|
wneuper@246
|
159 |
-> (select count(*) from UserLogger where step like 'UI_SOLVE%' and username=ul1.username limit 1) as Calc_Steps,
|
wneuper@246
|
160 |
-> (select count(*) from UserLogger where step not like 'UI_SOLVE%' and username=ul1.username limit 1) as Other_Steps
|
wneuper@246
|
161 |
-> from UserLogger ul1
|
wneuper@246
|
162 |
-> where Step='LO_START_SESSION'
|
wneuper@246
|
163 |
-> group by UserName
|
wneuper@246
|
164 |
-> order by Examples, UserName;
|
wneuper@246
|
165 |
+----------+----------+---------+------------+-------------+
|
wneuper@246
|
166 |
| UserName | Examples | Success | Calc_Steps | Other_Steps |
|
wneuper@246
|
167 |
+----------+----------+---------+------------+-------------+
|
wneuper@246
|
168 |
| htl | 1 | 3 | 2 | 7 |
|
wneuper@246
|
169 |
| krempler | 2 | 2 | 2 | 8 |
|
wneuper@246
|
170 |
| x | 6 | 8 | 10 | 26 |
|
wneuper@246
|
171 |
+----------+----------+---------+------------+-------------+
|
wneuper@246
|
172 |
3 rows in set (0.00 sec)
|
wneuper@246
|
173 |
|
wneuper@246
|
174 |
comments:
|
wneuper@246
|
175 |
!# The output should be:
|
wneuper@246
|
176 |
+----------+----------+----------+---------+------------+-------------+
|
wneuper@246
|
177 |
| UserName | Sessions | Examples | Success | Calc_Steps | Other_Steps |
|
wneuper@246
|
178 |
+----------+----------+----------+---------+------------+-------------+
|
wneuper@246
|
179 |
| x | 3 | 8 | 5 | 10 | 26 |
|
wneuper@246
|
180 |
| htl | 1 | 3 | 2 | 2 | 7 |
|
wneuper@246
|
181 |
| krempler | 1 | 2 | 2 | 2 | 8 |
|
wneuper@246
|
182 |
+----------+----------+----------+---------+------------+-------------+
|
wneuper@246
|
183 |
?# In 071030-CR-WN.data I see that closing session 4 after session 6
|
wneuper@246
|
184 |
does _not_ LO_STOP_EXAMPLE for Worksheet 4_1 and 4_2
|
wneuper@246
|
185 |
(while is should do that). |