Changeset e1d0274


Ignore:
Timestamp:
Feb 1, 2010 7:11:39 PM (5 years ago)
Author:
olivier <>
Branches:
master, crem, crem2, crem3, dev, dev2, diadems, dj1.6, feature/breadcrumbs, feature/ts-0.5, feature/ts-0.5.4, feature/writecache, forma, generic, instru_search, lam, lam2, mapsv3, merge, nlivemulti, prob, production, release/1.4.4, sabiod, search, security, server, social, storage, telecaster, test, video
Children:
be51b23
Parents:
b3ffdab
git-author:
olivier <> (01/02/2010 19:11:39)
git-committer:
olivier <> (01/02/2010 19:11:39)
Message:

improve and optimize location models structure and querying

Location:
telemeta
Files:
7 edited

Legend:

Unmodified
Added
Removed
  • telemeta/models/__init__.py

    rfc6b123 re1d0274  
    3737#    PhysicalFormat, PublishingStatus 
    3838 
    39 from django.db.models.signals import post_syncdb 
    40  
    41 def 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  
    76 post_syncdb.connect(syncdb_callback)     
    77      
    78  
  • telemeta/models/crem.py

    r01268f9 re1d0274  
    220220        "Return the countries of the items" 
    221221        countries = [] 
    222         items = self.items.all() 
    223         for item in items: 
    224             if item.location: 
    225                 country = item.location.country() 
    226                 if country and not country in countries: 
     222        for item in self.items.filter(location__isnull=False): 
     223            for country in item.location.countries(): 
     224                if not country in countries: 
    227225                    countries.append(country) 
    228  
     226             
    229227        countries.sort(self.__name_cmp)                 
    230228 
     
    292290    recorded_from_date    = DateField(_('recording date (from)')) 
    293291    recorded_to_date      = DateField(_('recording date (until)')) 
    294     location              = WeakForeignKey('Location', related_name="items",  
    295                                            db_column='location_name', verbose_name=_('location')) 
     292    location              = WeakForeignKey('Location', verbose_name=_('location')) 
    296293    location_comment      = CharField(_('location comment')) 
    297294    ethnic_group          = WeakForeignKey('EthnicGroup', related_name="items",  
     
    553550 
    554551class Location(ModelCore): 
    555     "Item location" 
    556     TYPE_CHOICES     = (('country', 'country'), ('continent', 'continent'), ('other', 'other')) 
    557  
    558     name             = CharField(_('name'), primary_key=True, max_length=150, required=True) 
    559     type             = CharField(_('type'), choices=TYPE_CHOICES, max_length=16, required=True) 
    560     complete_type    = ForeignKey('LocationType', related_name="types", verbose_name=_('complete type')) 
    561     current_name     = WeakForeignKey('self', related_name="past_names", db_column="current_name",  
    562                                       verbose_name=_('current name'))  
     552    "Locations" 
     553    OTHER_TYPE  = 0 
     554    CONTINENT   = 1 
     555    COUNTRY     = 2 
     556    TYPE_CHOICES     = ((COUNTRY, _('country')), (CONTINENT, _('continent')), (OTHER_TYPE, _('other'))) 
     557 
     558    name             = CharField(_('name'), unique=True, max_length=150, required=True) 
     559    type             = IntegerField(_('type'), choices=TYPE_CHOICES, required=True, db_index=True) 
     560    complete_type    = ForeignKey('LocationType', related_name="locations", verbose_name=_('complete type')) 
     561    current_location = WeakForeignKey('self', related_name="past_names",  
     562                                      verbose_name=_('current location'))  
    563563    is_authoritative = BooleanField(_('authoritative')) 
    564564 
    565     def parent(self): 
    566         relations = self.parent_relations.all() 
    567         if relations: 
    568             return relations[0].parent_location 
    569  
    570         return None 
    571  
    572     def _by_type(self, typename): 
    573         location = self 
    574         while location and location.type != typename: 
    575             location = location.parent() 
    576  
    577         return location 
    578  
    579     def country(self): 
    580         return self._by_type('country') 
    581  
    582     def continent(self): 
    583         return self._by_type('continent') 
     565    objects = query.LocationManager() 
     566 
     567    def items(self): 
     568        return MediaItem.objects.by_location(self) 
     569 
     570    def collections(self): 
     571        return MediaCollection.objects.by_location(self) 
     572 
     573    def ancestors(self): 
     574        return Location.objects.filter(descendant_relations__location=self) 
     575 
     576    def descendants(self): 
     577        return Location.objects.filter(ancestor_relations__ancestor_location=self) 
     578 
     579    def countries(self): 
     580        if self.type == self.COUNTRY: 
     581            return Location.objects.filter(pk=self.id) 
     582        return self.ancestors().filter(type=self.COUNTRY) 
    584583 
    585584    class Meta(MetaCore): 
     
    589588        return self.name 
    590589 
    591     def sequence(self): 
     590    def flatname(self): 
     591        if self.type != self.COUNTRY and self.type != self.CONTINENT: 
     592            raise Exceptions("Flat names are only supported for countries and continents") 
     593 
     594        map = Location.objects.flatname_map() 
     595        for flatname in map: 
     596            if self.id == map[flatname]: 
     597                return flatname 
     598 
     599        return None                     
     600 
     601    def sequences(self): 
    592602        sequence = [] 
    593603        location = self 
     
    597607        return sequence 
    598608 
    599     def fullname(self): 
     609    def fullnames(self): 
    600610         
    601611        return u', '.join([unicode(l) for l in self.sequence()]) 
    602612 
    603613class LocationType(ModelCore): 
    604     "Location type of an item location" 
    605     id   = CharField(_('identifier'), max_length=64, primary_key=True, required=True) 
     614    "Location types" 
     615    code = CharField(_('identifier'), max_length=64, unique=True, required=True) 
    606616    name = CharField(_('name'), max_length=150, required=True) 
    607617 
     
    610620 
    611621class LocationAlias(ModelCore): 
    612     "Location other name" 
    613     location         = ForeignKey('Location', related_name="aliases", db_column="location_name",  
    614                                   max_length=150, verbose_name=_('location')) 
     622    "Location aliases" 
     623    location         = ForeignKey('Location', related_name="aliases", verbose_name=_('location')) 
    615624    alias            = CharField(_('alias'), max_length=150, required=True) 
    616625    is_authoritative = BooleanField(_('authoritative')) 
     
    624633     
    625634class LocationRelation(ModelCore): 
    626     "Location family" 
    627     location             = ForeignKey('Location', related_name="parent_relations",  
    628                                       db_column="location_name", max_length=150, verbose_name=_('location')) 
    629     parent_location      = ForeignKey('Location', related_name="child_relations", db_column="parent_location_name",  
    630                                       null=True, max_length=150, verbose_name=_('parent location')) 
    631     is_authoritative     = BooleanField() 
     635    "Location relations" 
     636    location             = ForeignKey('Location', related_name="ancestor_relations", verbose_name=_('location')) 
     637    ancestor_location      = ForeignKey('Location', related_name="descendant_relations",  verbose_name=_('ancestor location')) 
     638    is_direct            = BooleanField(db_index=True) 
    632639 
    633640    class Meta(MetaCore): 
    634641        db_table = 'location_relations' 
     642        unique_together = ('location', 'ancestor_location') 
    635643     
    636644class ContextKeyword(Enumeration): 
  • telemeta/models/cremquery.py

    rfc6b123 re1d0274  
    4141from django import db 
    4242import _mysql_exceptions 
     43from telemeta.util.unaccent import unaccent_icmp, unaccent 
    4344 
    4445class CoreQuerySet(EnhancedQuerySet): 
     
    104105        ) 
    105106 
    106     def by_country(self, country): 
     107    def by_location(self, location): 
    107108        "Find collections by country" 
    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() 
     109        return self.filter(Q(items__location=location) | Q(items__location__in=location.descendants())).distinct() 
    113110     
    114111    def by_continent(self, continent): 
     
    149146    quick_search.__doc__ = MediaCollectionQuerySet.quick_search.__doc__ 
    150147 
    151     def by_country(self, *args, **kwargs): 
    152         return self.get_query_set().by_country(*args, **kwargs) 
    153     by_country.__doc__ = MediaCollectionQuerySet.by_country.__doc__ 
     148    def by_location(self, *args, **kwargs): 
     149        return self.get_query_set().by_location(*args, **kwargs) 
     150    by_location.__doc__ = MediaCollectionQuerySet.by_location.__doc__ 
    154151 
    155152    def by_continent(self, *args, **kwargs): 
     
    173170    by_change_time.__doc__ = MediaCollectionQuerySet.by_change_time.__doc__ 
    174171 
    175     def stat_continents(self, order_by='nitems'):       
     172    @staticmethod 
     173    def __name_cmp(obj1, obj2): 
     174        return unaccent_icmp(obj1.name, obj2.name) 
     175 
     176    def stat_continents(self, only_continent=None):       
    176177        "Return the number of collections by continents and countries as a tree" 
    177         from django.db import connection 
    178         cursor = connection.cursor() 
    179         if order_by == 'nitems': 
    180             order_by = 'items_num DESC' 
    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 
    194         result_set = cursor.fetchall() 
     178        from telemeta.models import MediaItem, Location 
     179 
     180        countries = [] 
     181        for lid in MediaItem.objects.filter(location__isnull=False).values_list('location', flat=True).distinct(): 
     182            location = Location.objects.get(pk=lid) 
     183            if not only_continent or (only_continent in location.ancestors().filter(type=Location.CONTINENT)): 
     184                for l in location.countries(): 
     185                    if not l in countries: 
     186                        countries.append(l) 
     187                 
    195188        stat = {} 
    196         for continent, country, count in result_set: 
    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}] 
    202  
    203         keys = stat.keys() 
    204         keys.sort() 
    205         ordered = [{'name': k, 'countries': stat[k]} for k in keys] 
     189 
     190        for country in countries: 
     191            count = country.collections().count() 
     192            for continent in country.ancestors().filter(type=Location.CONTINENT): 
     193                if not stat.has_key(continent): 
     194                    stat[continent] = {} 
     195 
     196                stat[continent][country] = count 
     197                 
     198        keys1 = stat.keys() 
     199        keys1.sort(self.__name_cmp) 
     200        ordered = [] 
     201        for c in keys1: 
     202            keys2 = stat[c].keys() 
     203            keys2.sort(self.__name_cmp) 
     204            sub = [{'location': d, 'count': stat[c][d]} for d in keys2] 
     205            ordered.append({'location': c, 'countries': sub}) 
     206         
    206207        return ordered 
    207  
    208     def list_countries(self): 
    209         "Return a 2D list of all countries with continents" 
    210  
    211         from django.db import connection 
    212         cursor = connection.cursor() 
    213  
    214         cursor.execute("SELECT continent, etat FROM media_items " 
    215             "GROUP BY continent, etat ORDER BY REPLACE(etat, '\"', '')"); 
    216         return cursor.fetchall() 
    217  
    218     def list_continents(self): 
    219         "Return a list of all continents" 
    220          
    221         from django.db import connection 
    222         cursor = connection.cursor() 
    223  
    224         cursor.execute("SELECT DISTINCT(name) FROM locations WHERE type = 'continent' ORDER BY name") 
    225         result_set = cursor.fetchall() 
    226         result = [] 
    227         for a, in result_set: 
    228             if a != '' and a != 'N': # CREM fix 
    229                 result.append(a) 
    230          
    231         return result 
    232208 
    233209 
     
    270246        "Find items by last change time"   
    271247        return self._by_change_time('item', from_time, until_time) 
     248 
     249    def by_location(self, location): 
     250        "Find items by location" 
     251        from telemeta.models import LocationRelation 
     252        descendants = LocationRelation.objects.filter(ancestor_location=location) 
     253        return self.filter(Q(location=location) | Q(location__in=descendants)) 
    272254             
    273255class MediaItemManager(CoreManager): 
     
    302284    by_change_time.__doc__ = MediaItemQuerySet.by_change_time.__doc__     
    303285 
     286    def by_location(self, *args, **kwargs): 
     287        return self.get_query_set().by_location(*args, **kwargs) 
     288    by_location.__doc__ = MediaItemQuerySet.by_location.__doc__     
     289 
     290class LocationQuerySet(CoreQuerySet): 
     291    def by_flatname(self, flatname): 
     292        map = LocationManager.flatname_map() 
     293        return self.filter(pk=map[flatname]) 
     294 
     295class LocationManager(CoreManager): 
     296    __flatname_map = None 
     297 
     298    def get_query_set(self): 
     299        "Return location query set" 
     300        return LocationQuerySet(self.model) 
     301 
     302    @classmethod 
     303    def flatname_map(cls): 
     304        if cls.__flatname_map: 
     305            return cls.__flatname_map 
     306 
     307        from telemeta.models import Location 
     308        map = {} 
     309        locations = Location.objects.filter(Q(type=Location.COUNTRY) | Q(type=Location.CONTINENT)) 
     310        for l in locations: 
     311            flatname = unaccent(l.name).lower() 
     312            flatname = re.sub('[^a-z]', '_', flatname) 
     313            while map.has_key(flatname): 
     314                flatname = '_' + flatname 
     315            map[flatname] = l.id 
     316 
     317        cls.__flatname_map = map 
     318        return map 
     319             
     320    def by_flatname(self, *args, **kwargs): 
     321        return self.get_query_set().by_flatname(*args, **kwargs) 
     322    by_flatname.__doc__ = LocationQuerySet.by_flatname.__doc__     
     323 
     324     
  • telemeta/templates/telemeta_default/geo_continents.html

    rfc6b123 re1d0274  
    77<ul class="continents"> 
    88{% for continent in continents %} 
    9   <li class="name"><b><a href="{% url telemeta-geo-countries continent.flatname %}">{{ continent.name }}</a></b> 
     9  <li class="name"><b><a href="{% url telemeta-geo-countries continent.location.flatname %}">{{ continent.location }}</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.flatname,country.flatname %}"> 
    14           {{ country.name|lower|capfirst }}</a></li> 
     13        <a href="{% url telemeta-geo-country-collections continent.location.flatname,country.location.flatname %}"> 
     14          {{ country.location }}</a></li> 
    1515    {% endfor %} 
    1616    {% if continent.countries.10 %} 
    17     <li><a href="{% url telemeta-geo-countries continent.flatname %}">More..</a></li> 
     17    <li><a href="{% url telemeta-geo-countries continent.location.flatname %}">More..</a></li> 
    1818    {% endif %} 
    1919    </ul> 
  • telemeta/templates/telemeta_default/geo_countries.html

    rfc6b123 re1d0274  
    55{% block content %} 
    66<h3><a href="{% url telemeta-geo-continents %}">{% trans "World" %}</a> / 
    7   {{ continent.name }}</h3> 
     7  {{ continent.location.name }}</h3> 
    88<ul> 
    99{% for country in continent.countries %} 
    10   <li><a href="{% url telemeta-geo-country-collections continent.flatname,country.flatname %}"> 
    11     {{ country.name|lower|capfirst }} ({{ country.count }})</a></li> 
     10  <li><a href="{% url telemeta-geo-country-collections continent.location.flatname,country.location.flatname %}"> 
     11    {{ country.location.name }} ({{ country.count }})</a></li> 
    1212{% endfor %} 
    1313</ul> 
  • telemeta/templates/telemeta_default/geo_country_collections.html

    rfc6b123 re1d0274  
    55{% block content %} 
    66<h3><a href="{% url telemeta-geo-continents %}">{% trans "World" %}</a> / 
    7   <a href="{% url telemeta-geo-countries continent_flatname %}">{{ continent }}</a>  
     7  <a href="{% url telemeta-geo-countries continent.flatname %}">{{ continent }}</a>  
    88  / {{ country }}</h3> 
    99 
  • telemeta/web/base.py

    rfc6b123 re1d0274  
    4646 
    4747import telemeta 
    48 from telemeta.models import MediaItem 
    49 from telemeta.models import MediaCollection 
     48from telemeta.models import MediaItem, Location, MediaCollection 
    5049from telemeta.core import Component, ExtensionPoint 
    5150from telemeta.export import * 
     
    338337        return HttpResponse(template.render(context), mimetype=mimetype) 
    339338 
    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  
    358339    def list_continents(self, request): 
    359340        continents = MediaCollection.objects.stat_continents() 
    360         self.make_continents_flatnames(continents) 
    361341        return render_to_response('telemeta/geo_continents.html',  
    362342                    {'continents': continents }) 
     
    368348 
    369349    def list_countries(self, request, continent):                     
    370         continents = MediaCollection.objects.stat_continents() 
    371         self.make_continents_flatnames(continents) 
    372         for c in continents: 
    373             if c["flatname"] == continent: 
    374                 break 
    375         if c["flatname"] != continent: 
    376             raise Http404 
    377  
    378         return render_to_response('telemeta/geo_countries.html', {'continent': c }) 
     350        continent = Location.objects.by_flatname(continent)[0] 
     351        data = MediaCollection.objects.stat_continents(only_continent=continent) 
     352 
     353        return render_to_response('telemeta/geo_countries.html', {'continent': data[0] }) 
    379354 
    380355    def list_country_collections(self, request, continent, country): 
    381         continents = MediaCollection.objects.stat_continents() 
    382         map = self.make_continents_flatnames(continents) 
    383         objects = MediaCollection.objects.by_country(map[country]) 
     356        continent = Location.objects.by_flatname(continent)[0] 
     357        country = Location.objects.by_flatname(country)[0] 
     358        objects = MediaCollection.objects.by_location(country) 
    384359        return list_detail.object_list(request, objects,  
    385360            template_name='telemeta/geo_country_collections.html', paginate_by=20, 
    386             extra_context={'country': map[country], 'continent_flatname': continent, 'continent': map[continent]}) 
     361            extra_context={'country': country, 'continent': continent}) 
    387362 
    388363    def handle_oai_request(self, request): 
Note: See TracChangeset for help on using the changeset viewer.