r23141 MediaWiki - Code Review archive

Repository:MediaWiki
Revision:< r23140‎ | r23141 | r23142 >
Date:21:57, 20 June 2007
Author:kim
Status:old
Tags:
Comment:
changes by Andre Malafaya Baptista:
* items that are marked deleted are no longer counted or shown
* in missing.php : shows all missing defined meanings in set (abandoning the
assumption: "english is always complete").
Modified paths:
  • /trunk/extensions/Wikidata/util/collection.php (modified) (history)
  • /trunk/extensions/Wikidata/util/collections.php (modified) (history)
  • /trunk/extensions/Wikidata/util/missing.php (modified) (history)
  • /trunk/extensions/Wikidata/util/stats.php (modified) (history)

Diff [purge]

Index: trunk/extensions/Wikidata/util/missing.php
——@@ -63,6 +63,8 @@
6464
6565 ";
6666
 67+# Malafaya: Here lies the old query, assuming there's always an English expression for the DM
 68+/*
6769 $result = mysql_query("
6870 SELECT en.id, en.spelling
6971 FROM
——@@ -90,18 +92,69 @@
9193 ON en.id=actual.id
9294 WHERE actual.id IS NULL
9395 ")or die ("error ".mysql_error());
 96+*/
9497
 98+# Malafaya: This is my query (performance must be checked live) for missing expressions based on
 99+# * don't count deleted stuff (old query did)
 100+# * do 2 joins: between members of collection and target language, and then with english for default, but only for elements having target language expression as NULL (non-existing)
 101+# * this gives us the DM id, the spelling in target language (or NULL, if none), the spelling in English (or NULL, if none)
 102+# Warning: some DMs came up in OLPC and Swadesh collections belonging to those collections but having no expressions associated... These are visible in this query
95103
 104+$result = mysql_query("
 105+ SELECT member.id, translation_en.spelling_en
 106+ FROM
 107+ (
 108+ SELECT member_mid as id
 109+ FROM uw_collection_contents WHERE
 110+ collection_id = $collection_esc
 111+ AND remove_transaction_id IS NULL
 112+ ) as member
 113+ LEFT JOIN
 114+ (
 115+ SELECT spelling, defined_meaning_id
 116+ FROM uw_syntrans, uw_expression_ns WHERE
 117+ uw_expression_ns.expression_id = uw_syntrans.expression_id
 118+ AND uw_syntrans.remove_transaction_id IS NULL
 119+ AND language_id = $language_esc
 120+ ) as translation
 121+ ON
 122+ translation.defined_meaning_id = member.id
 123+ LEFT JOIN
 124+ (
 125+ SELECT spelling as spelling_en, defined_meaning_id
 126+ FROM uw_syntrans, uw_expression_ns WHERE
 127+ uw_expression_ns.expression_id = uw_syntrans.expression_id
 128+ AND uw_syntrans.remove_transaction_id IS NULL
 129+ AND language_id = 85
 130+ ) as translation_en
 131+ ON
 132+ translation_en.defined_meaning_id = member.id
 133+ WHERE translation.spelling IS NULL
 134+ ORDER BY spelling_en
 135+")or die ("error ".mysql_error());
96136
 137+
97138 while ($row = mysql_fetch_array($result, MYSQL_NUM)) {
98139 $id=$row[0];
99 - $spelling=$row[1];
100 - print "<a href=\"../../../index.php?title=DefinedMeaning:".$spelling."_($id)\">$spelling</a>;\n";
 140+ $spelling_en=$row[1];
 141+
 142+ # Malafaya: Not translated to target language
 143+ if ($spelling_en == null)
 144+ # Malafaya: Not translated to English either; use a placeholder expression
 145+ print "<a href=\"../../../index.php?title=DefinedMeaning:(untranslated)_($id)\">(untranslated)</a>;\n";
 146+ else
 147+ # Malafaya: English translation exists; use it
 148+ print "<a href=\"../../../index.php?title=DefinedMeaning:".$spelling_en."_($id)\">$spelling_en</a>;\n";
101149 }
102150 print "<br>\n";
103151
 152+
104153 print "<hr>\n
105154 <h3>Already present</h3>\n";
 155+
 156+# Malafaya: Old query with same caveats as the one above
 157+
 158+/*
106159 $result = mysql_query("
107160 SELECT actual.id, actual.spelling
108161 FROM
——@@ -129,8 +182,27 @@
130183 ON en.id=actual.id
131184 WHERE actual.id IS NOT NULL
132185 ")or die ("error ".mysql_error());
 186+*/
133187
 188+# Malafaya: my new query, not counting deleted stuff; just select target language expression for DMs in collection (whether translated to English or not, it's not relevant)
134189
 190+$result = mysql_query("
 191+ SELECT defined_meaning_id, spelling
 192+ FROM uw_syntrans, uw_expression_ns WHERE
 193+ uw_expression_ns.expression_id = uw_syntrans.expression_id
 194+ AND uw_syntrans.remove_transaction_id IS NULL
 195+ AND language_id = $language_esc
 196+ AND uw_syntrans.defined_meaning_id IN
 197+ (
 198+ SELECT member_mid as id
 199+ FROM uw_collection_contents WHERE
 200+ collection_id = $collection_esc
 201+ AND remove_transaction_id IS NULL
 202+ )
 203+ ORDER BY spelling
 204+")or die ("error ".mysql_error());
 205+
 206+
135207 while ($row = mysql_fetch_array($result, MYSQL_NUM)) {
136208 $id=$row[0];
137209 $spelling=$row[1];
——@@ -153,8 +225,9 @@
154226 <h3> see also</h3>
155227 <ul>
156228 <li><a href=\"collection.php?collection=$collection_id\">Return to Number of Expressions per language in this collection</a></li>
157 -<li><a href=\"stats.php\">Overview, expressions per langauge</a></li>
 229+<li><a href=\"stats.php\">Overview, expressions per language</a></li>
158230 <li><a href=\"../../..\">return to Omegawiki proper</li></a>
159231 </p>
160232 "
 233+
161234 ?>
Index: trunk/extensions/Wikidata/util/collection.php
——@@ -77,7 +77,9 @@
7878 WHERE collection_id = $collection_esc
7979 AND uw_syntrans.defined_meaning_id= uw_collection_contents.member_mid
8080 AND uw_expression_ns.expression_id = uw_syntrans.expression_id
 81+AND uw_expression_ns.remove_transaction_id IS NULL
8182 AND uw_syntrans.remove_transaction_id IS NULL
 83+AND uw_collection_contents.remove_transaction_id is NULL
8284 GROUP BY language_id
8385 ORDER BY counts DESC
8486 ")or die ("error ".mysql_error());
——@@ -160,6 +162,6 @@
161163 <h3> see also</h3>
162164 <ul>
163165 <li><a href="collections.php">Other collections</a></li>
164 -<li><a href="stats.php">Overview, expressions per langauge</a></li>
 166+<li><a href="stats.php">Overview, expressions per language</a></li>
165167 <li><a href="../../..">return to Omegawiki proper</li></a>
166168 </p>
Index: trunk/extensions/Wikidata/util/collections.php
——@@ -48,6 +48,8 @@
4949 SELECT uw_collection_ns.collection_id AS id ,count(*) AS total
5050 FROM uw_collection_contents, uw_collection_ns
5151 WHERE uw_collection_ns.collection_id=uw_collection_contents.collection_id
 52+ AND uw_collection_contents.remove_transaction_id is NULL
 53+ AND uw_collection_ns.remove_transaction_id is NULL
5254 GROUP BY uw_collection_ns.collection_id
5355 ) AS counts
5456 ON spellings.id=counts.id
——@@ -71,6 +73,6 @@
7274 <p align="left">
7375 <h3> see also</h3>
7476 <ul>
75 -<li><a href="stats.php">Overview, expressions per langauge</a></li>
 77+<li><a href="stats.php">Overview, expressions per language</a></li>
7678 <li><a href="../../..">return to Omegawiki proper</li></a>
7779 </p>
Index: trunk/extensions/Wikidata/util/stats.php
——@@ -43,11 +43,11 @@
4444 <hr width=950 size=1 noshade><br>
4545 ";
4646
47 -$expressions_r=mysql_query("SELECT COUNT(*) FROM uw_expression_ns");
 47+$expressions_r=mysql_query("SELECT COUNT(*) FROM uw_expression_ns WHERE remove_transaction_id IS NULL");
4848 $expressions_a=mysql_fetch_row($expressions_r);
4949 $expressions=$expressions_a[0];
5050
51 -$defined_meanings_r=mysql_query("SELECT COUNT(*) FROM uw_defined_meaning");
 51+$defined_meanings_r=mysql_query("SELECT COUNT(*) FROM uw_defined_meaning WHERE remove_transaction_id IS NULL");
5252 $defined_meanings_a=mysql_fetch_row($defined_meanings_r);
5353 $defined_meanings=$defined_meanings_a[0];
5454 echo"<br>\n";
——@@ -69,7 +69,7 @@
7070 ////////////////////////////////////////////////////////
7171 $result = mysql_query("
7272 SELECT
73 -language_id, count(*)
 73+language_id, count(*) as tot
7474 FROM uw_expression_ns
7575 WHERE expression_id IN
7676 (
——@@ -77,8 +77,9 @@
7878 FROM uw_syntrans
7979 WHERE remove_transaction_id IS NULL
8080 )
 81+AND remove_transaction_id IS NULL
8182 group by language_id
82 -order by count(*) desc
 83+order by tot desc
8384 ")or die ("error ".mysql_error());
8485
8586 echo '

Status & tagging log