1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
|
#!/bin/sh
# Copyright (C) 2008 Achim Mueller <ace@gnubg.org>
# This program is free software: you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation, either version 3 of the License, or
# (at your option) any later version.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program. If not, see <https://www.gnu.org/licenses/>.
#
# $Id: $
#
# query_player.sh - a small shell script for getting some player's data
#
# Usage: query_player.sh [search pattern] [output]
#
# search pattern: a player's name or parts of it
# output: [screen|csv] the way the result is presented, either on your screen in
# readable columns or into a csv file for usage in a
# spreadsheet.
#
#
# Put the path to your database here:
DATABASE=$HOME/.gnubg/gnubg.db
OUTPUT="$2"
NAME_ID_SEARCH="select player_id from player where name like '%$1%';"
NAME_SEARCH="select name from player where name like '%$1%';"
NAME_ID_RESULT=`sqlite3 $DATABASE <<EOF
.header off
$NAME_ID_SEARCH
EOF`
# echo $NAME_ID_RESULT
STATS_SEARCH=" select s.session_id as No
, s.player_id0 as Player
, round(m1.snowie_error_rate_per_move*1000,2) as Snowie
, round(m1.error_based_fibs_rating,1) as Fibs
, s.player_id1 as Opp
, p1.name as Name
, round(m2.snowie_error_rate_per_move*1000,2) as Snowie
, round(m2.error_based_fibs_rating,1) as Fibs_Opp
, round(50+m1.luck_adjusted_result*100,2) as LAR
, round(50+(m2.overall_error_total-m1.overall_error_total)*100,2) as MWC
, s.length as Length
, round(m1.actual_result+0.5) as Result
from session as s
join matchstat as m1
on m1.session_id = s.session_id
and m1.player_id = s.player_id0
join matchstat as m2
on m2.session_id = s.session_id
and m2.player_id = s.player_id1
join player as p2
on p2.player_id = s.player_id0
and p1.player_id = s.player_id1
join player as p1
on p1.player_id = s.player_id1
and p2.player_id = s.player_id0
where player_id0 = '$NAME_ID_RESULT'
union
select s.session_id
, s.player_id1 as Player
, round(m1.snowie_error_rate_per_move*1000,2) as Snowie
, round(m1.error_based_fibs_rating,1) as Fibs
, s.player_id0 as Opp
, p2.name as name
, round(m2.snowie_error_rate_per_move*1000,2) as Snowie
, round(m2.error_based_fibs_rating,1) Fibs_Opp
, round(50+m1.luck_adjusted_result*100,2) as LAR
, round(50+(m2.overall_error_total-m1.overall_error_total)*100,2) as MWC
, s.length as Length
, round(m1.actual_result+0.5) as Result
from session as s
join matchstat as m2
on m2.session_id = s.session_id
and m2.player_id = s.player_id0
join matchstat as m1
on m1.session_id = s.session_id
and m1.player_id = s.player_id1
join player as p2
on p2.player_id = s.player_id0
and p1.player_id = s.player_id1
join player as p1
on p1.player_id = s.player_id1
and p2.player_id = s.player_id0
where player_id1 = '$NAME_ID_RESULT';"
NICK_NAME_RESULT=`sqlite3 $DATABASE <<EOF
.headers OFF
$NAME_SEARCH
EOF`
POINTS_WON="select sum(round(actual_result+0.5,0)) from matchstat where player_id = '$NAME_ID_RESULT';"
NUMBER_MATCHES="select count(session_id) from matchstat where player_id = '$NAME_ID_RESULT';"
TOTAL_ERRORS="select round(sum(snowie_error_rate_per_move*snowie_moves)*1000/sum(snowie_moves),2) from matchstat where player_id = $NAME_ID_RESULT;"
TOTAL_RESULT=`sqlite3 $DATABASE <<EOF
.headers OFF
$POINTS_WON
EOF`
STATS_RESULT_SCREEN=`sqlite3 $DATABASE <<EOF
.mode column
.headers ON
.output tmp.file
$STATS_SEARCH
EOF`
STATS_RESULT_CSV=`sqlite3 $DATABASE <<EOF
.mode csv
.headers ON
.output gnubg_db.txt
$STATS_SEARCH
EOF`
NUMBER_MATCHES_RESULT=`sqlite3 $DATABASE <<EOF
.headers OFF
$NUMBER_MATCHES
EOF`
ERROR_AVR_RESULT=`sqlite3 $DATABASE <<EOF
.headers OFF
$TOTAL_ERRORS
EOF`
case "$OUTPUT" in
screen|"") $STATS_RESULT_SCREEN
echo
cat tmp.file
rm tmp.file
echo
echo "Result ${NICK_NAME_RESULT} (${NAME_ID_RESULT}): $TOTAL_RESULT win(s) in $NUMBER_MATCHES_RESULT matches. Snowie error rate: $ERROR_AVR_RESULT"
echo
;;
csv) $STATS_RESULT_CSV
;;
*) echo "Usage: query_player.sh [search pattern] [screen|csv]" ;;
esac
exit 0
|
