damned-lies r1118 - branches/djamnedlies/stats

[stephaner svn gnome org]

Author: stephaner
Date: Sat Nov  1 15:21:00 2008
New Revision: 1118
URL: http://svn.gnome.org/viewvc/damned-lies?rev=1118&view=rev

Log:
2008-11-01  StÃphane Raimbault  <stephane raimbault gmail com>

	* stats/models.py: Fixed invalid SQL query (detected by
	PostgreSQL). Code formatting cleanups.


Modified:
   branches/djamnedlies/stats/models.py

Modified: branches/djamnedlies/stats/models.py
==============================================================================
--- branches/djamnedlies/stats/models.py	(original)
+++ branches/djamnedlies/stats/models.py	Sat Nov  1 15:21:00 2008
@@ -541,43 +541,61 @@
     
     def total_strings(self):
         """ Returns the total number of strings in the release as a tuple (doc_total, ui_total) """
-        # TODO: transform this SQL query in a Django model query, if possible...
-        query = """ SELECT domain.dtype, SUM(stat.untranslated) FROM statistics AS stat
-                      LEFT JOIN domain ON domain.id=stat.domain_id
-                      LEFT JOIN branch AS br ON br.id=stat.branch_id
-                      LEFT JOIN category AS cat ON br.category_id=cat.id
-                      LEFT JOIN "release" AS rel ON rel.id = cat.release_id 
-                      WHERE rel.id=%s AND stat.language_id IS NULL
-                      GROUP BY domain.dtype """
+        # Uses the special statistics record where language_id is NULL to compute the sum.
+        query = """
+            SELECT domain.dtype, 
+                   SUM(stat.untranslated)
+            FROM statistics AS stat
+            LEFT JOIN domain 
+                   ON domain.id = stat.domain_id
+            LEFT JOIN branch AS br
+                   ON br.id = stat.branch_id
+            LEFT JOIN category AS cat
+                   ON br.category_id = cat.id
+            LEFT JOIN "release" AS rel
+                   ON rel.id = cat.release_id 
+            WHERE rel.id = %s
+              AND stat.language_id IS NULL
+            GROUP BY domain.dtype"""
         cursor = connection.cursor()
         if settings.DATABASE_ENGINE == 'mysql':
             cursor.execute("SET sql_mode='ANSI_QUOTES'")
         cursor.execute(query, (self.id,))
-        totaldoc = 0; totalui = 0
+
+        total_doc, total_ui = 0, 0
         for row in cursor.fetchall():
             if row[0] == 'ui':
-                totalui = row[1]
+                total_ui = row[1]
             elif row[0] == 'doc':
-                totaldoc = row[1]
-        return (totaldoc, totalui)
+                total_doc = row[1]
+        return (total_doc, total_ui)
     
     def total_for_lang(self, lang):
-        """ Returns total translated/fuzzy/untranslated strings for a specific language """
+        """ Returns total translated/fuzzy/untranslated strings for a specific
+            language """
         
         total_doc, total_ui = self.total_strings()
-        query = """SELECT domain.dtype, SUM(stat.translated), SUM(stat.fuzzy) FROM statistics AS stat
-            LEFT JOIN domain ON stat.domain_id=domain.id
-            LEFT JOIN branch ON stat.branch_id=branch.id
-            LEFT JOIN category ON branch.category_id=category.id
-            WHERE language_id = %s AND category.release_id = %s
-            GROUP BY domain.dtype;"""
+        query = """
+            SELECT domain.dtype,
+                   SUM(stat.translated), 
+                   SUM(stat.fuzzy) 
+            FROM statistics AS stat
+            LEFT JOIN domain 
+                   ON stat.domain_id = domain.id
+            LEFT JOIN branch 
+                   ON stat.branch_id = branch.id
+            LEFT JOIN category 
+                   ON branch.category_id = category.id
+            WHERE language_id = %s 
+              AND category.release_id = %s
+            GROUP BY domain.dtype"""
         cursor = connection.cursor()
         cursor.execute(query, (lang.id, self.id))
         stats = {'id': self.id, 'name': _(self.name),
-                 'uitrans':0, 'uifuzzy':0, 'uitotal':total_ui,
-                 'doctrans':0, 'docfuzzy':0, 'doctotal':total_doc,
-                 'uitransperc':0, 'uifuzzyperc':0, 'uiuntransperc':0,
-                 'doctransperc':0, 'docfuzzyperc':0, 'docuntransperc':0}
+                 'uitrans': 0, 'uifuzzy': 0, 'uitotal': total_ui,
+                 'doctrans': 0, 'docfuzzy': 0, 'doctotal': total_doc,
+                 'uitransperc': 0, 'uifuzzyperc': 0, 'uiuntransperc': 0,
+                 'doctransperc': 0, 'docfuzzyperc': 0, 'docuntransperc': 0}
         for res in cursor.fetchall():
             if res[0] == 'ui':
                 stats['uitrans'] = res[1]
@@ -599,18 +617,27 @@
     
     def get_global_stats(self):
         """ Get statistics for all languages in a release, grouped by language
-            Returns a sorted list: (language, doc_trans, doc_fuzzy, doc_untrans, ui_trans, ui_fuzzy, ui_untrans)"""
-        
-        # TODO: transform this SQL query in a Django model query, if possible...
-        query = """SELECT lang.name, lang.locale, domain.dtype, SUM(stat.translated) AS trans, SUM(stat.fuzzy)
-          FROM statistics AS stat
-          LEFT JOIN domain ON domain.id=stat.domain_id
-          LEFT JOIN language AS lang ON stat.language_id=lang.id
-          LEFT JOIN branch AS br ON br.id=stat.branch_id
-          LEFT JOIN category ON br.category_id=category.id
-          WHERE category.release_id = %s
-          GROUP BY domain.dtype, stat.language_id
-          ORDER BY domain.dtype, trans desc;"""
+            Returns a sorted list: (language, doc_trans, doc_fuzzy,
+            doc_untrans, ui_trans, ui_fuzzy, ui_untrans) """
+
+        query = """
+            SELECT MIN(lang.name), 
+                   MIN(lang.locale), 
+                   domain.dtype, 
+                   SUM(stat.translated) AS trans, 
+                   SUM(stat.fuzzy)
+            FROM statistics AS stat
+            LEFT JOIN domain 
+                   ON domain.id = stat.domain_id
+            LEFT JOIN language AS lang 
+                   ON stat.language_id = lang.id
+            LEFT JOIN branch AS br
+                   ON br.id = stat.branch_id
+            LEFT JOIN category
+                   ON br.category_id = category.id
+            WHERE category.release_id = %s
+            GROUP BY domain.dtype, stat.language_id
+            ORDER BY domain.dtype, trans DESC"""
         cursor = connection.cursor()
         cursor.execute(query, (self.id,))
         stats = {}