[d3657c]: run / bin / stats-mysql.sh Maximize Restore History

Download this file

stats-mysql.sh    117 lines (101 with data), 3.9 kB

  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
#!/bin/bash
# bbou@ac-toulouse.fr
# 25/11/2013
# 30|31|31_snapshot [password]
dbtag=$1
shift
if [ -z "${dbtag}" ]; then
echo "$0 <dbtag:30|31|31_snapshot>"
exit 1
fi
if [ -z "$MYSQLPASSWORD" ]; then
MYSQLPASSWORD=$1
shift
if [ -z "$MYSQLPASSWORD" ]; then
read -s -p "enter root's password: " MYSQLPASSWORD
echo
export MYSQLPASSWORD
fi
fi
# D A T A B A S E
db="wordnet${dbtag}"
dbtype='mysql'
#echo "database=${db}"
# T A B L E S
wn="words casedwords senses synsets linktypes semlinks lexlinks postypes lexdomains morphmaps morphs samples vframemaps vframes vframesentencemaps vframesentences adjpositions adjpositiontypes"
lsenses="senses20 senses21"
lsensemaps="sensemaps2021 sensemaps2130"
lsynsetmaps=""
lcomputedsynsetmaps=""
case ${dbtag} in
31)
lsenses="${lsenses} senses30"
lsynsetmaps="${lsynsetmaps} synsetmaps3031"
;;
31_snapshot*)
lsenses="${lsenses} senses30 senses31"
lsynsetmaps="${lsynsetmaps} synsetmaps3031"
lcomputedsynsetmaps="${lcomputedsynsetmaps} sensemaps3031"
;;
esac
legacy="${lsenses} ${lsensemaps} ${lsynsetmaps} ${lcomputedsynsetmaps}"
vn="vnclasses vnclassmembers vnclassmembersenses vngroupings vngroupingmaps vnrolemaps vnroles vnroletypes vnselrestrs vnselrestrtypes vnframemaps vnframes vnframenames vnframesubnames vnsemantics vnsyntaxes vnpredicatemaps vnpredicates vnexamplemaps vnexamples"
xwn="xwnparselfts xwnwsds"
bnc="bncs bncimaginfs bncconvtasks bncspwrs"
sumo="sumofiles sumoformulas sumoterms sumotermattrs sumomaps sumoparsemaps"
glf="glfs"
ilfwn="ilfs"
# O U T P U T
outfile=../stats/stats-${dbtag}.html
# D O M A I N S
extensions="legacy vn bnc sumo xwn glf ilfwn"
domains="wn ${extensions}"
echo "<html><head><meta http-equiv='Content-Type' content='text/html; charset=UTF-8'><title>${db}</title></head><body>" > ${outfile}
echo "<h1>${db}</h1>" >> ${outfile}
mysql -u root --password=$MYSQLPASSWORD ${db} --html >> ${outfile} <<EOF
SELECT COUNT(*) AS log_count FROM logs;
EOF
# cardinality
for domain in ${domains}; do
echo "<h2>${domain}</h2>" >> ${outfile}
eval tables=\$${domain}
for t in ${tables}; do
mysql -u root --password=$MYSQLPASSWORD ${db} --html <<EOF
SELECT "${t}" AS ${domain}_table, COUNT(*) AS row_count FROM ${t};
EOF
done >> ${outfile}
done
# POS distribution
echo "<h1>POS</h1>" >> ${outfile}
echo "<h2>words</h2>" >> ${outfile}
mysql -u root --password=$MYSQLPASSWORD ${db} --html >> ${outfile} <<EOF
SELECT pos, COUNT(DISTINCT lemma) AS words_count FROM words JOIN senses USING (wordid) JOIN synsets USING (synsetid) GROUP BY pos
EOF
echo "<h2>senses</h2>" >> ${outfile}
mysql -u root --password=$MYSQLPASSWORD ${db} --html >> ${outfile} <<EOF
SELECT pos, COUNT(*) AS senses_count FROM senses JOIN synsets USING (synsetid) GROUP BY pos;
EOF
echo "<h2>synsets</h2>" >> ${outfile}
mysql -u root --password=$MYSQLPASSWORD ${db} --html >> ${outfile} <<EOF
SELECT pos, COUNT(*) AS synsets_count FROM synsets GROUP BY pos;
EOF
# link distribution
echo "<h1>Links</h1>" >> ${outfile}
echo "<h2>Semantic</h2>" >> ${outfile}
mysql -u root --password=$MYSQLPASSWORD ${db} --html >> ${outfile} <<EOF
SELECT linkid, link, s1.pos, s2.pos, COUNT(*) FROM semlinks AS l
INNER JOIN synsets AS s1 ON l.synset1id = s1.synsetid INNER JOIN synsets AS s2 ON l.synset2id = s2.synsetid
INNER JOIN linktypes USING (linkid)
GROUP BY linkid, s1.pos, s2.pos
ORDER BY linkid, s1.pos, s2.pos;
EOF
echo "<h2>Lexical</h2>" >> ${outfile}
mysql -u root --password=$MYSQLPASSWORD ${db} --html >> ${outfile} <<EOF
SELECT linkid, link, s1.pos, s2.pos, count(*) FROM lexlinks AS l
INNER JOIN words AS w1 ON l.word1id = w1.wordid INNER JOIN words AS w2 ON l.word2id = w2.wordid
INNER JOIN synsets AS s1 ON l.synset1id = s1.synsetid INNER JOIN synsets AS s2 ON l.synset2id = s2.synsetid
INNER JOIN linktypes USING (linkid)
GROUP BY linkid, s1.pos, s2.pos
ORDER BY linkid, s1.pos, s2.pos;
EOF
echo "</body></html>" >> ${outfile}