From fc6b123c50b112c958f37f71432567c6f9ef5fce Mon Sep 17 00:00:00 2001 From: olivier <> Date: Wed, 27 Jan 2010 21:46:14 +0000 Subject: [PATCH] fix geo navigator (needs optimization) --- telemeta/models/__init__.py | 40 +++++++++++++++++ telemeta/models/cremquery.py | 44 ++++++++++++------- .../telemeta_default/geo_continents.html | 6 +-- .../telemeta_default/geo_countries.html | 5 ++- .../geo_country_collections.html | 5 ++- telemeta/urls.py | 4 +- telemeta/web/base.py | 31 +++++++++++-- 7 files changed, 106 insertions(+), 29 deletions(-) diff --git a/telemeta/models/__init__.py b/telemeta/models/__init__.py index 3b4367b0..09106a8b 100644 --- a/telemeta/models/__init__.py +++ b/telemeta/models/__init__.py @@ -36,3 +36,43 @@ from telemeta.models.crem import * #MediaCollection, MediaItem, MediaPart, Revision, \ # 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) + + diff --git a/telemeta/models/cremquery.py b/telemeta/models/cremquery.py index 4526bcdb..b4452ba9 100644 --- a/telemeta/models/cremquery.py +++ b/telemeta/models/cremquery.py @@ -33,10 +33,13 @@ # Authors: Olivier Guilyardi # David LIPSZYC +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): "Base class for all query sets" @@ -102,7 +105,11 @@ class MediaCollectionQuerySet(CoreQuerySet): 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): "Find collections by continent" @@ -165,28 +172,33 @@ class MediaCollectionManager(CoreManager): return self.get_query_set().by_change_time(*args, **kwargs) 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() keys.sort() diff --git a/telemeta/templates/telemeta_default/geo_continents.html b/telemeta/templates/telemeta_default/geo_continents.html index 9e2a7314..6ba9e731 100644 --- a/telemeta/templates/telemeta_default/geo_continents.html +++ b/telemeta/templates/telemeta_default/geo_continents.html @@ -6,15 +6,15 @@ {% if continents %}