Changeset fc6b123

Timestamp:

01/27/10 22:46:14 (3 years ago)

Author:

olivier <>

Branches:

master, crem, crem2, dev, dev2, diadems, forma, generic, instru_search, lam, nlivemulti, production, release/1.4.4, security, social, storage, test, video

Children:

b3ffdab

Parents:

01268f9

git-author:

olivier <> (01/27/10 22:46:14)

git-committer:

olivier <> (01/27/10 22:46:14)

Message:

fix geo navigator (needs optimization)

Location:

telemeta

Files:

• models/__init__.py (modified) (1 diff)
• models/cremquery.py (modified) (3 diffs)
• templates/telemeta_default/geo_continents.html (modified) (1 diff)
• templates/telemeta_default/geo_countries.html (modified) (1 diff)
• templates/telemeta_default/geo_country_collections.html (modified) (1 diff)
• urls.py (modified) (1 diff)
• web/base.py (modified) (4 diffs)

telemeta/models/__init__.py

@@ -37 +37 @@
 #    PhysicalFormat, PublishingStatus
 
+from django.db.models.signals import post_syncdb
+
+def syncdb_callback(sender, **kwargs):
+    from django.db import connection
+    import _mysql_exceptions
+    cursor = connection.cursor()
+    print "Creating MySQL stored procedure"
+    try:
+        cursor.execute("DROP FUNCTION IF EXISTS telemeta_location_ascendant")
+    except _mysql_exceptions.Warning:
+        pass
+    try:
+        cursor.execute("""
+        CREATE FUNCTION telemeta_location_ascendant(loc CHAR(150), asc_type CHAR(16))
+          RETURNS CHAR(150) 
+          READS SQL DATA 
+          BEGIN 
+            DECLARE t, n CHAR(150); 
+            DECLARE c INT;
+            SELECT COUNT(*) INTO c FROM locations WHERE name = loc;
+            IF c = 0 THEN
+              RETURN NULL;
+            END IF;
+            SELECT name, type INTO n, t FROM locations WHERE name = loc;
+            WHILE t <> asc_type DO
+              SELECT COUNT(*) INTO c FROM location_relations WHERE location_name = n;
+              IF c = 0 THEN
+                RETURN NULL;
+              END IF;
+              SELECT parent_location_name INTO n FROM location_relations WHERE location_name = n LIMIT 1;
+              SELECT type INTO t FROM locations WHERE name = n;
+            END WHILE;  
+            RETURN n;
+          END""")
+    except _mysql_exceptions.Warning:
+        pass
+
+post_syncdb.connect(syncdb_callback)    
+    
+

telemeta/models/cremquery.py

@@ -34 +34 @@
 #          David LIPSZYC <davidlipszyc@gmail.com>
 
+from django import db
 from django.db.models import Manager, Q
 from telemeta.models.core import EnhancedQuerySet, EnhancedManager
 import re
 from django.core.exceptions import ObjectDoesNotExist
+from django import db
+import _mysql_exceptions
 
 class CoreQuerySet(EnhancedQuerySet):
@@ -103 +106 @@
     def by_country(self, country):
         "Find collections by country"
-        return self.filter(items__location__type="country", items__location=country).distinct()
+        db.connection.cursor() # Need this to establish connection
+        country = db.connection.connection.literal(country)
+        return self.extra(where=["media_items.collection_id = media_collections.id",
+                                 "telemeta_location_ascendant(media_items.location_name, 'country') = %s" % country],
+                          tables=['media_items']).distinct()
     
     def by_continent(self, continent):
@@ -166 +173 @@
     by_change_time.__doc__ = MediaCollectionQuerySet.by_change_time.__doc__
 
-    def stat_continents(self, order_by='num'):      
+    def stat_continents(self, order_by='nitems'):      
         "Return the number of collections by continents and countries as a tree"
         from django.db import connection
         cursor = connection.cursor()
-        if order_by == 'num':
+        if order_by == 'nitems':
             order_by = 'items_num DESC'
-        else:
-            order_by = 'etat'
-        cursor.execute("SELECT continent, etat, count(*) AS items_num "
-            "FROM media_collections INNER JOIN media_items "
-            "ON media_collections.id = media_items.collection_id "
-            "WHERE (continent IN "
-            "  ('EUROPE', 'OCEANIE', 'ASIE', 'AMERIQUE', 'AFRIQUE')) "
-            "AND etat <> '' "
-            "GROUP BY etat ORDER BY continent, " + order_by)
+        elif order_by != 'country':
+            raise Exception("stat_continents() can only order by nitems or country")
+
+        try:
+            cursor.execute("""
+                SELECT telemeta_location_ascendant(location_name, 'continent') as continent, 
+                       telemeta_location_ascendant(location_name, 'country') as country, 
+                       count(*) AS items_num 
+                FROM media_collections INNER JOIN media_items 
+                ON media_collections.id = media_items.collection_id 
+                GROUP BY country ORDER BY continent, """ + order_by)
+        except _mysql_exceptions.Warning:
+            pass
         result_set = cursor.fetchall()
         stat = {}
         for continent, country, count in result_set:
-            if stat.has_key(continent):
-                stat[continent].append({'name':country, 'count':count})
-            else:
-                stat[continent] = [{'name':country, 'count':count}]
+            if continent and country:
+                if stat.has_key(continent):
+                    stat[continent].append({'name':country, 'count':count})
+                else:
+                    stat[continent] = [{'name':country, 'count':count}]
 
         keys = stat.keys()

telemeta/templates/telemeta_default/geo_continents.html

@@ -7 +7 @@
 <ul class="continents">
 {% for continent in continents %}
-  <li class="name"><b><a href="{% url telemeta-geo-countries continent.name|urlencode %}">{{ continent.name }}</a></b>
+  <li class="name"><b><a href="{% url telemeta-geo-countries continent.flatname %}">{{ continent.name }}</a></b>
     <ul>
     {% for country in continent.countries|slice:":10" %}
       <li class="country_name">
-        <a href="{% url telemeta-geo-country-collections continent.name,country.name|urlencode %}">
+        <a href="{% url telemeta-geo-country-collections continent.flatname,country.flatname %}">
           {{ country.name|lower|capfirst }}</a></li>
     {% endfor %}
     {% if continent.countries.10 %}
-    <li><a href="{% url telemeta-geo-countries continent.name|urlencode %}">More..</a></li>
+    <li><a href="{% url telemeta-geo-countries continent.flatname %}">More..</a></li>
     {% endif %}
     </ul>

telemeta/templates/telemeta_default/geo_countries.html

@@ -1 +1 @@
 {% extends "telemeta/base.html" %}
 {% load telemeta_utils %}
+{% load i18n %}
 
 {% block content %}
-<h3><a href="{% url telemeta-geo-continents %}">WORLD</a> /
+<h3><a href="{% url telemeta-geo-continents %}">{% trans "World" %}</a> /
   {{ continent.name }}</h3>
 <ul>
 {% for country in continent.countries %}
-  <li><a href="{% url telemeta-geo-country-collections continent.name,country.name|urlencode %}">
+  <li><a href="{% url telemeta-geo-country-collections continent.flatname,country.flatname %}">
     {{ country.name|lower|capfirst }} ({{ country.count }})</a></li>
 {% endfor %}

telemeta/templates/telemeta_default/geo_country_collections.html

@@ -1 +1 @@
 {% extends "telemeta/base.html" %}
 {% load telemeta_utils %}
+{% load i18n %}
 
 {% block content %}
-<h3><a href="{% url telemeta-geo-continents %}">WORLD</a> /
-  <a href="{% url telemeta-geo-countries continent|urlencode %}">{{ continent }}</a> 
+<h3><a href="{% url telemeta-geo-continents %}">{% trans "World" %}</a> /
+  <a href="{% url telemeta-geo-countries continent_flatname %}">{{ continent }}</a> 
   / {{ country }}</h3>
 

telemeta/urls.py

@@ -137 +137 @@
     # Geographic browsing
     url(r'^geo/$', web_view.list_continents, name="telemeta-geo-continents"),
-    url(r'^geo/(?P<continent>[A-Za-z]+)/$', web_view.list_countries, 
+    url(r'^geo/(?P<continent>[a-z_]+)/$', web_view.list_countries, 
         name="telemeta-geo-countries"),
-    url('^geo/(?P<continent>[A-Za-z]+)/(?P<country>[-A-Za-z0-9%;.,"& \']+)/$', 
+    url(r'^geo/(?P<continent>[a-z_]+)/(?P<country>[a-z_]+)/$', 
         web_view.list_country_collections, 
         name="telemeta-geo-country-collections"),

telemeta/web/base.py

@@ -56 +56 @@
 from telemeta.interop.oaidatasource import TelemetaOAIDataSource
 from django.core.exceptions import ObjectDoesNotExist
+from telemeta.util.unaccent import unaccent
 
 class WebView(Component):
@@ -337 +338 @@
         return HttpResponse(template.render(context), mimetype=mimetype)
 
+    def make_continents_flatnames(self, continents):
+        map = {}
+        for c in continents:
+            flat = unaccent(c['name']).lower()
+            flat = re.sub('[^a-z]', '_', flat)
+            while map.has_key(flat):
+                flat = '_' + flat
+            c['flatname'] = flat
+            map[flat] = c['name']
+            for d in c['countries']:
+                flat = unaccent(d['name']).lower()
+                flat = re.sub('[^a-z]', '_', flat)
+                while map.has_key(flat):
+                    flat = '_' + flat
+                d['flatname'] = flat
+                map[flat] = d['name']
+        return map
+
     def list_continents(self, request):
         continents = MediaCollection.objects.stat_continents()
+        self.make_continents_flatnames(continents)
         return render_to_response('telemeta/geo_continents.html', 
                     {'continents': continents })
@@ -349 +369 @@
     def list_countries(self, request, continent):                    
         continents = MediaCollection.objects.stat_continents()
+        self.make_continents_flatnames(continents)
         for c in continents:
-            if c["name"] == continent:
+            if c["flatname"] == continent:
                 break
-        if c["name"] != continent:
+        if c["flatname"] != continent:
             raise Http404
 
@@ -358 +379 @@
 
     def list_country_collections(self, request, continent, country):
-        objects = MediaCollection.objects.by_country(country)
+        continents = MediaCollection.objects.stat_continents()
+        map = self.make_continents_flatnames(continents)
+        objects = MediaCollection.objects.by_country(map[country])
         return list_detail.object_list(request, objects, 
             template_name='telemeta/geo_country_collections.html', paginate_by=20,
-            extra_context={'country': country, 'continent': continent})
+            extra_context={'country': map[country], 'continent_flatname': continent, 'continent': map[continent]})
 
     def handle_oai_request(self, request):