Changeset 520

Timestamp:

02/01/10 19:11:39 (5 weeks ago)

Author:

olivier

Message:

improve and optimize location models structure and querying

Location:

trunk/telemeta

Files:

• models/__init__.py (modified) (1 diff)
• models/crem.py (modified) (7 diffs)
• models/cremquery.py (modified) (6 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)
• web/base.py (modified) (3 diffs)

trunk/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)    
-    
-

trunk/telemeta/models/crem.py

@@ -220 +220 @@
         "Return the countries of the items"
         countries = []
-        items = self.items.all()
-        for item in items:
-            if item.location:
-                country = item.location.country()
-                if country and not country in countries:
+        for item in self.items.filter(location__isnull=False):
+            for country in item.location.countries():
+                if not country in countries:
                     countries.append(country)
-
+            
         countries.sort(self.__name_cmp)                
 
@@ -292 +290 @@
     recorded_from_date    = DateField(_('recording date (from)'))
     recorded_to_date      = DateField(_('recording date (until)'))
-    location              = WeakForeignKey('Location', related_name="items", 
-                                           db_column='location_name', verbose_name=_('location'))
+    location              = WeakForeignKey('Location', verbose_name=_('location'))
     location_comment      = CharField(_('location comment'))
     ethnic_group          = WeakForeignKey('EthnicGroup', related_name="items", 
@@ -553 +550 @@
 
 class Location(ModelCore):
-    "Item location"
-    TYPE_CHOICES     = (('country', 'country'), ('continent', 'continent'), ('other', 'other'))
-
-    name             = CharField(_('name'), primary_key=True, max_length=150, required=True)
-    type             = CharField(_('type'), choices=TYPE_CHOICES, max_length=16, required=True)
-    complete_type    = ForeignKey('LocationType', related_name="types", verbose_name=_('complete type'))
-    current_name     = WeakForeignKey('self', related_name="past_names", db_column="current_name", 
-                                      verbose_name=_('current name')) 
+    "Locations"
+    OTHER_TYPE  = 0
+    CONTINENT   = 1
+    COUNTRY     = 2
+    TYPE_CHOICES     = ((COUNTRY, _('country')), (CONTINENT, _('continent')), (OTHER_TYPE, _('other')))
+
+    name             = CharField(_('name'), unique=True, max_length=150, required=True)
+    type             = IntegerField(_('type'), choices=TYPE_CHOICES, required=True, db_index=True)
+    complete_type    = ForeignKey('LocationType', related_name="locations", verbose_name=_('complete type'))
+    current_location = WeakForeignKey('self', related_name="past_names", 
+                                      verbose_name=_('current location')) 
     is_authoritative = BooleanField(_('authoritative'))
 
-    def parent(self):
-        relations = self.parent_relations.all()
-        if relations:
-            return relations[0].parent_location
-
-        return None
-
-    def _by_type(self, typename):
-        location = self
-        while location and location.type != typename:
-            location = location.parent()
-
-        return location
-
-    def country(self):
-        return self._by_type('country')
-
-    def continent(self):
-        return self._by_type('continent')
+    objects = query.LocationManager()
+
+    def items(self):
+        return MediaItem.objects.by_location(self)
+
+    def collections(self):
+        return MediaCollection.objects.by_location(self)
+
+    def ancestors(self):
+        return Location.objects.filter(descendant_relations__location=self)
+
+    def descendants(self):
+        return Location.objects.filter(ancestor_relations__ancestor_location=self)
+
+    def countries(self):
+        if self.type == self.COUNTRY:
+            return Location.objects.filter(pk=self.id)
+        return self.ancestors().filter(type=self.COUNTRY)
 
     class Meta(MetaCore):
@@ -589 +588 @@
         return self.name
 
-    def sequence(self):
+    def flatname(self):
+        if self.type != self.COUNTRY and self.type != self.CONTINENT:
+            raise Exceptions("Flat names are only supported for countries and continents")
+
+        map = Location.objects.flatname_map()
+        for flatname in map:
+            if self.id == map[flatname]:
+                return flatname
+
+        return None                    
+
+    def sequences(self):
         sequence = []
         location = self
@@ -597 +607 @@
         return sequence
 
-    def fullname(self):
+    def fullnames(self):
         
         return u', '.join([unicode(l) for l in self.sequence()])
 
 class LocationType(ModelCore):
-    "Location type of an item location"
-    id   = CharField(_('identifier'), max_length=64, primary_key=True, required=True)
+    "Location types"
+    code = CharField(_('identifier'), max_length=64, unique=True, required=True)
     name = CharField(_('name'), max_length=150, required=True)
 
@@ -610 +620 @@
 
 class LocationAlias(ModelCore):
-    "Location other name"
-    location         = ForeignKey('Location', related_name="aliases", db_column="location_name", 
-                                  max_length=150, verbose_name=_('location'))
+    "Location aliases"
+    location         = ForeignKey('Location', related_name="aliases", verbose_name=_('location'))
     alias            = CharField(_('alias'), max_length=150, required=True)
     is_authoritative = BooleanField(_('authoritative'))
@@ -624 +633 @@
     
 class LocationRelation(ModelCore):
-    "Location family"
-    location             = ForeignKey('Location', related_name="parent_relations", 
-                                      db_column="location_name", max_length=150, verbose_name=_('location'))
-    parent_location      = ForeignKey('Location', related_name="child_relations", db_column="parent_location_name", 
-                                      null=True, max_length=150, verbose_name=_('parent location'))
-    is_authoritative     = BooleanField()
+    "Location relations"
+    location             = ForeignKey('Location', related_name="ancestor_relations", verbose_name=_('location'))
+    ancestor_location      = ForeignKey('Location', related_name="descendant_relations",  verbose_name=_('ancestor location'))
+    is_direct            = BooleanField(db_index=True)
 
     class Meta(MetaCore):
         db_table = 'location_relations'
+        unique_together = ('location', 'ancestor_location')
     
 class ContextKeyword(Enumeration):

trunk/telemeta/models/cremquery.py

@@ -41 +41 @@
 from django import db
 import _mysql_exceptions
+from telemeta.util.unaccent import unaccent_icmp, unaccent
 
 class CoreQuerySet(EnhancedQuerySet):
@@ -104 +105 @@
         )
 
-    def by_country(self, country):
+    def by_location(self, location):
         "Find collections by country"
-        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()
+        return self.filter(Q(items__location=location) | Q(items__location__in=location.descendants())).distinct()
     
     def by_continent(self, continent):
@@ -149 +146 @@
     quick_search.__doc__ = MediaCollectionQuerySet.quick_search.__doc__
 
-    def by_country(self, *args, **kwargs):
-        return self.get_query_set().by_country(*args, **kwargs)
-    by_country.__doc__ = MediaCollectionQuerySet.by_country.__doc__
+    def by_location(self, *args, **kwargs):
+        return self.get_query_set().by_location(*args, **kwargs)
+    by_location.__doc__ = MediaCollectionQuerySet.by_location.__doc__
 
     def by_continent(self, *args, **kwargs):
@@ -173 +170 @@
     by_change_time.__doc__ = MediaCollectionQuerySet.by_change_time.__doc__
 
-    def stat_continents(self, order_by='nitems'):      
+    @staticmethod
+    def __name_cmp(obj1, obj2):
+        return unaccent_icmp(obj1.name, obj2.name)
+
+    def stat_continents(self, only_continent=None):      
         "Return the number of collections by continents and countries as a tree"
-        from django.db import connection
-        cursor = connection.cursor()
-        if order_by == 'nitems':
-            order_by = 'items_num DESC'
-        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()
+        from telemeta.models import MediaItem, Location
+
+        countries = []
+        for lid in MediaItem.objects.filter(location__isnull=False).values_list('location', flat=True).distinct():
+            location = Location.objects.get(pk=lid)
+            if not only_continent or (only_continent in location.ancestors().filter(type=Location.CONTINENT)):
+                for l in location.countries():
+                    if not l in countries:
+                        countries.append(l)
+                
         stat = {}
-        for continent, country, count in result_set:
-            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()
-        keys.sort()
-        ordered = [{'name': k, 'countries': stat[k]} for k in keys]
+
+        for country in countries:
+            count = country.collections().count()
+            for continent in country.ancestors().filter(type=Location.CONTINENT):
+                if not stat.has_key(continent):
+                    stat[continent] = {}
+
+                stat[continent][country] = count
+                
+        keys1 = stat.keys()
+        keys1.sort(self.__name_cmp)
+        ordered = []
+        for c in keys1:
+            keys2 = stat[c].keys()
+            keys2.sort(self.__name_cmp)
+            sub = [{'location': d, 'count': stat[c][d]} for d in keys2]
+            ordered.append({'location': c, 'countries': sub})
+        
         return ordered
-
-    def list_countries(self):
-        "Return a 2D list of all countries with continents"
-
-        from django.db import connection
-        cursor = connection.cursor()
-
-        cursor.execute("SELECT continent, etat FROM media_items "
-            "GROUP BY continent, etat ORDER BY REPLACE(etat, '\"', '')");
-        return cursor.fetchall()
-
-    def list_continents(self):
-        "Return a list of all continents"
-        
-        from django.db import connection
-        cursor = connection.cursor()
-
-        cursor.execute("SELECT DISTINCT(name) FROM locations WHERE type = 'continent' ORDER BY name")
-        result_set = cursor.fetchall()
-        result = []
-        for a, in result_set:
-            if a != '' and a != 'N': # CREM fix
-                result.append(a)
-        
-        return result
 
 
@@ -270 +246 @@
         "Find items by last change time"  
         return self._by_change_time('item', from_time, until_time)
+
+    def by_location(self, location):
+        "Find items by location"
+        from telemeta.models import LocationRelation
+        descendants = LocationRelation.objects.filter(ancestor_location=location)
+        return self.filter(Q(location=location) | Q(location__in=descendants))
             
 class MediaItemManager(CoreManager):
@@ -302 +284 @@
     by_change_time.__doc__ = MediaItemQuerySet.by_change_time.__doc__    
 
+    def by_location(self, *args, **kwargs):
+        return self.get_query_set().by_location(*args, **kwargs)
+    by_location.__doc__ = MediaItemQuerySet.by_location.__doc__    
+
+class LocationQuerySet(CoreQuerySet):
+    def by_flatname(self, flatname):
+        map = LocationManager.flatname_map()
+        return self.filter(pk=map[flatname])
+
+class LocationManager(CoreManager):
+    __flatname_map = None
+
+    def get_query_set(self):
+        "Return location query set"
+        return LocationQuerySet(self.model)
+
+    @classmethod
+    def flatname_map(cls):
+        if cls.__flatname_map:
+            return cls.__flatname_map
+
+        from telemeta.models import Location
+        map = {}
+        locations = Location.objects.filter(Q(type=Location.COUNTRY) | Q(type=Location.CONTINENT))
+        for l in locations:
+            flatname = unaccent(l.name).lower()
+            flatname = re.sub('[^a-z]', '_', flatname)
+            while map.has_key(flatname):
+                flatname = '_' + flatname
+            map[flatname] = l.id
+
+        cls.__flatname_map = map
+        return map
+            
+    def by_flatname(self, *args, **kwargs):
+        return self.get_query_set().by_flatname(*args, **kwargs)
+    by_flatname.__doc__ = LocationQuerySet.by_flatname.__doc__    
+
+    

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

trunk/telemeta/templates/telemeta_default/geo_countries.html

@@ -5 +5 @@
 {% block content %}
 <h3><a href="{% url telemeta-geo-continents %}">{% trans "World" %}</a> /
-  {{ continent.name }}</h3>
+  {{ continent.location.name }}</h3>
 <ul>
 {% for country in continent.countries %}
-  <li><a href="{% url telemeta-geo-country-collections continent.flatname,country.flatname %}">
-    {{ country.name|lower|capfirst }} ({{ country.count }})</a></li>
+  <li><a href="{% url telemeta-geo-country-collections continent.location.flatname,country.location.flatname %}">
+    {{ country.location.name }} ({{ country.count }})</a></li>
 {% endfor %}
 </ul>

trunk/telemeta/templates/telemeta_default/geo_country_collections.html

@@ -5 +5 @@
 {% block content %}
 <h3><a href="{% url telemeta-geo-continents %}">{% trans "World" %}</a> /
-  <a href="{% url telemeta-geo-countries continent_flatname %}">{{ continent }}</a> 
+  <a href="{% url telemeta-geo-countries continent.flatname %}">{{ continent }}</a> 
   / {{ country }}</h3>
 

trunk/telemeta/web/base.py

@@ -46 +46 @@
 
 import telemeta
-from telemeta.models import MediaItem
-from telemeta.models import MediaCollection
+from telemeta.models import MediaItem, Location, MediaCollection
 from telemeta.core import Component, ExtensionPoint
 from telemeta.export import *
@@ -338 +337 @@
         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 })
@@ -368 +348 @@
 
     def list_countries(self, request, continent):                    
-        continents = MediaCollection.objects.stat_continents()
-        self.make_continents_flatnames(continents)
-        for c in continents:
-            if c["flatname"] == continent:
-                break
-        if c["flatname"] != continent:
-            raise Http404
-
-        return render_to_response('telemeta/geo_countries.html', {'continent': c })
+        continent = Location.objects.by_flatname(continent)[0]
+        data = MediaCollection.objects.stat_continents(only_continent=continent)
+
+        return render_to_response('telemeta/geo_countries.html', {'continent': data[0] })
 
     def list_country_collections(self, request, continent, country):
-        continents = MediaCollection.objects.stat_continents()
-        map = self.make_continents_flatnames(continents)
-        objects = MediaCollection.objects.by_country(map[country])
+        continent = Location.objects.by_flatname(continent)[0]
+        country = Location.objects.by_flatname(country)[0]
+        objects = MediaCollection.objects.by_location(country)
         return list_detail.object_list(request, objects, 
             template_name='telemeta/geo_country_collections.html', paginate_by=20,
-            extra_context={'country': map[country], 'continent_flatname': continent, 'continent': map[continent]})
+            extra_context={'country': country, 'continent': continent})
 
     def handle_oai_request(self, request):