Changeset fc6b123


Ignore:
Timestamp:
01/27/10 22:46:14 (4 years ago)
Author:
olivier <>
Branches:
master, crem, crem2, dev, dev2, diadems, feature/breadcrumbs, feature/ts-0.5, feature/ts-0.5.4, feature/writecache, forma, generic, instru_search, lam, lam2, mapsv3, merge, nlivemulti, production, release/1.4.4, sabiod, security, server, social, storage, telecaster, 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:
7 edited

Legend:

Unmodified
Added
Removed
  • telemeta/models/__init__.py

    r7308fad rfc6b123  
    3737#    PhysicalFormat, PublishingStatus 
    3838 
     39from django.db.models.signals import post_syncdb 
     40 
     41def syncdb_callback(sender, **kwargs): 
     42    from django.db import connection 
     43    import _mysql_exceptions 
     44    cursor = connection.cursor() 
     45    print "Creating MySQL stored procedure" 
     46    try: 
     47        cursor.execute("DROP FUNCTION IF EXISTS telemeta_location_ascendant") 
     48    except _mysql_exceptions.Warning: 
     49        pass 
     50    try: 
     51        cursor.execute(""" 
     52        CREATE FUNCTION telemeta_location_ascendant(loc CHAR(150), asc_type CHAR(16)) 
     53          RETURNS CHAR(150)  
     54          READS SQL DATA  
     55          BEGIN  
     56            DECLARE t, n CHAR(150);  
     57            DECLARE c INT; 
     58            SELECT COUNT(*) INTO c FROM locations WHERE name = loc; 
     59            IF c = 0 THEN 
     60              RETURN NULL; 
     61            END IF; 
     62            SELECT name, type INTO n, t FROM locations WHERE name = loc; 
     63            WHILE t <> asc_type DO 
     64              SELECT COUNT(*) INTO c FROM location_relations WHERE location_name = n; 
     65              IF c = 0 THEN 
     66                RETURN NULL; 
     67              END IF; 
     68              SELECT parent_location_name INTO n FROM location_relations WHERE location_name = n LIMIT 1; 
     69              SELECT type INTO t FROM locations WHERE name = n; 
     70            END WHILE;   
     71            RETURN n; 
     72          END""") 
     73    except _mysql_exceptions.Warning: 
     74        pass 
     75 
     76post_syncdb.connect(syncdb_callback)     
     77     
     78 
  • telemeta/models/cremquery.py

    ra2e58a9 rfc6b123  
    3434#          David LIPSZYC <davidlipszyc@gmail.com> 
    3535 
     36from django import db 
    3637from django.db.models import Manager, Q 
    3738from telemeta.models.core import EnhancedQuerySet, EnhancedManager 
    3839import re 
    3940from django.core.exceptions import ObjectDoesNotExist 
     41from django import db 
     42import _mysql_exceptions 
    4043 
    4144class CoreQuerySet(EnhancedQuerySet): 
     
    103106    def by_country(self, country): 
    104107        "Find collections by country" 
    105         return self.filter(items__location__type="country", items__location=country).distinct() 
     108        db.connection.cursor() # Need this to establish connection 
     109        country = db.connection.connection.literal(country) 
     110        return self.extra(where=["media_items.collection_id = media_collections.id", 
     111                                 "telemeta_location_ascendant(media_items.location_name, 'country') = %s" % country], 
     112                          tables=['media_items']).distinct() 
    106113     
    107114    def by_continent(self, continent): 
     
    166173    by_change_time.__doc__ = MediaCollectionQuerySet.by_change_time.__doc__ 
    167174 
    168     def stat_continents(self, order_by='num'):       
     175    def stat_continents(self, order_by='nitems'):       
    169176        "Return the number of collections by continents and countries as a tree" 
    170177        from django.db import connection 
    171178        cursor = connection.cursor() 
    172         if order_by == 'num': 
     179        if order_by == 'nitems': 
    173180            order_by = 'items_num DESC' 
    174         else: 
    175             order_by = 'etat' 
    176         cursor.execute("SELECT continent, etat, count(*) AS items_num " 
    177             "FROM media_collections INNER JOIN media_items " 
    178             "ON media_collections.id = media_items.collection_id " 
    179             "WHERE (continent IN " 
    180             "  ('EUROPE', 'OCEANIE', 'ASIE', 'AMERIQUE', 'AFRIQUE')) " 
    181             "AND etat <> '' " 
    182             "GROUP BY etat ORDER BY continent, " + order_by) 
     181        elif order_by != 'country': 
     182            raise Exception("stat_continents() can only order by nitems or country") 
     183 
     184        try: 
     185            cursor.execute(""" 
     186                SELECT telemeta_location_ascendant(location_name, 'continent') as continent,  
     187                       telemeta_location_ascendant(location_name, 'country') as country,  
     188                       count(*) AS items_num  
     189                FROM media_collections INNER JOIN media_items  
     190                ON media_collections.id = media_items.collection_id  
     191                GROUP BY country ORDER BY continent, """ + order_by) 
     192        except _mysql_exceptions.Warning: 
     193            pass 
    183194        result_set = cursor.fetchall() 
    184195        stat = {} 
    185196        for continent, country, count in result_set: 
    186             if stat.has_key(continent): 
    187                 stat[continent].append({'name':country, 'count':count}) 
    188             else: 
    189                 stat[continent] = [{'name':country, 'count':count}] 
     197            if continent and country: 
     198                if stat.has_key(continent): 
     199                    stat[continent].append({'name':country, 'count':count}) 
     200                else: 
     201                    stat[continent] = [{'name':country, 'count':count}] 
    190202 
    191203        keys = stat.keys() 
  • telemeta/templates/telemeta_default/geo_continents.html

    r9d3f1f3 rfc6b123  
    77<ul class="continents"> 
    88{% for continent in continents %} 
    9   <li class="name"><b><a href="{% url telemeta-geo-countries continent.name|urlencode %}">{{ continent.name }}</a></b> 
     9  <li class="name"><b><a href="{% url telemeta-geo-countries continent.flatname %}">{{ continent.name }}</a></b> 
    1010    <ul> 
    1111    {% for country in continent.countries|slice:":10" %} 
    1212      <li class="country_name"> 
    13         <a href="{% url telemeta-geo-country-collections continent.name,country.name|urlencode %}"> 
     13        <a href="{% url telemeta-geo-country-collections continent.flatname,country.flatname %}"> 
    1414          {{ country.name|lower|capfirst }}</a></li> 
    1515    {% endfor %} 
    1616    {% if continent.countries.10 %} 
    17     <li><a href="{% url telemeta-geo-countries continent.name|urlencode %}">More..</a></li> 
     17    <li><a href="{% url telemeta-geo-countries continent.flatname %}">More..</a></li> 
    1818    {% endif %} 
    1919    </ul> 
  • telemeta/templates/telemeta_default/geo_countries.html

    r9d3f1f3 rfc6b123  
    11{% extends "telemeta/base.html" %} 
    22{% load telemeta_utils %} 
     3{% load i18n %} 
    34 
    45{% block content %} 
    5 <h3><a href="{% url telemeta-geo-continents %}">WORLD</a> / 
     6<h3><a href="{% url telemeta-geo-continents %}">{% trans "World" %}</a> / 
    67  {{ continent.name }}</h3> 
    78<ul> 
    89{% for country in continent.countries %} 
    9   <li><a href="{% url telemeta-geo-country-collections continent.name,country.name|urlencode %}"> 
     10  <li><a href="{% url telemeta-geo-country-collections continent.flatname,country.flatname %}"> 
    1011    {{ country.name|lower|capfirst }} ({{ country.count }})</a></li> 
    1112{% endfor %} 
  • telemeta/templates/telemeta_default/geo_country_collections.html

    r9d3f1f3 rfc6b123  
    11{% extends "telemeta/base.html" %} 
    22{% load telemeta_utils %} 
     3{% load i18n %} 
    34 
    45{% block content %} 
    5 <h3><a href="{% url telemeta-geo-continents %}">WORLD</a> / 
    6   <a href="{% url telemeta-geo-countries continent|urlencode %}">{{ continent }}</a>  
     6<h3><a href="{% url telemeta-geo-continents %}">{% trans "World" %}</a> / 
     7  <a href="{% url telemeta-geo-countries continent_flatname %}">{{ continent }}</a>  
    78  / {{ country }}</h3> 
    89 
  • telemeta/urls.py

    ra2e58a9 rfc6b123  
    137137    # Geographic browsing 
    138138    url(r'^geo/$', web_view.list_continents, name="telemeta-geo-continents"), 
    139     url(r'^geo/(?P<continent>[A-Za-z]+)/$', web_view.list_countries,  
     139    url(r'^geo/(?P<continent>[a-z_]+)/$', web_view.list_countries,  
    140140        name="telemeta-geo-countries"), 
    141     url('^geo/(?P<continent>[A-Za-z]+)/(?P<country>[-A-Za-z0-9%;.,"& \']+)/$',  
     141    url(r'^geo/(?P<continent>[a-z_]+)/(?P<country>[a-z_]+)/$',  
    142142        web_view.list_country_collections,  
    143143        name="telemeta-geo-country-collections"), 
  • telemeta/web/base.py

    ra2e58a9 rfc6b123  
    5656from telemeta.interop.oaidatasource import TelemetaOAIDataSource 
    5757from django.core.exceptions import ObjectDoesNotExist 
     58from telemeta.util.unaccent import unaccent 
    5859 
    5960class WebView(Component): 
     
    337338        return HttpResponse(template.render(context), mimetype=mimetype) 
    338339 
     340    def make_continents_flatnames(self, continents): 
     341        map = {} 
     342        for c in continents: 
     343            flat = unaccent(c['name']).lower() 
     344            flat = re.sub('[^a-z]', '_', flat) 
     345            while map.has_key(flat): 
     346                flat = '_' + flat 
     347            c['flatname'] = flat 
     348            map[flat] = c['name'] 
     349            for d in c['countries']: 
     350                flat = unaccent(d['name']).lower() 
     351                flat = re.sub('[^a-z]', '_', flat) 
     352                while map.has_key(flat): 
     353                    flat = '_' + flat 
     354                d['flatname'] = flat 
     355                map[flat] = d['name'] 
     356        return map 
     357 
    339358    def list_continents(self, request): 
    340359        continents = MediaCollection.objects.stat_continents() 
     360        self.make_continents_flatnames(continents) 
    341361        return render_to_response('telemeta/geo_continents.html',  
    342362                    {'continents': continents }) 
     
    349369    def list_countries(self, request, continent):                     
    350370        continents = MediaCollection.objects.stat_continents() 
     371        self.make_continents_flatnames(continents) 
    351372        for c in continents: 
    352             if c["name"] == continent: 
     373            if c["flatname"] == continent: 
    353374                break 
    354         if c["name"] != continent: 
     375        if c["flatname"] != continent: 
    355376            raise Http404 
    356377 
     
    358379 
    359380    def list_country_collections(self, request, continent, country): 
    360         objects = MediaCollection.objects.by_country(country) 
     381        continents = MediaCollection.objects.stat_continents() 
     382        map = self.make_continents_flatnames(continents) 
     383        objects = MediaCollection.objects.by_country(map[country]) 
    361384        return list_detail.object_list(request, objects,  
    362385            template_name='telemeta/geo_country_collections.html', paginate_by=20, 
    363             extra_context={'country': country, 'continent': continent}) 
     386            extra_context={'country': map[country], 'continent_flatname': continent, 'continent': map[continent]}) 
    364387 
    365388    def handle_oai_request(self, request): 
Note: See TracChangeset for help on using the changeset viewer.