From dc94eade03022ce3a5286f5e781576321a5f1653 Mon Sep 17 00:00:00 2001 From: Dan McGee Date: Fri, 27 Apr 2012 08:59:00 -0500 Subject: Incomplete-only todolists optimization We can push this down to the database if we know in advance we only need the incomplete lists. This helps our call on the developer dashboard quite a bit; the time of the single query in question drops from >1300ms to around 40ms. Signed-off-by: Dan McGee --- todolists/utils.py | 8 ++++++-- 1 file changed, 6 insertions(+), 2 deletions(-) (limited to 'todolists/utils.py') diff --git a/todolists/utils.py b/todolists/utils.py index 24101e86..94f39f71 100644 --- a/todolists/utils.py +++ b/todolists/utils.py @@ -3,7 +3,7 @@ from django.db.models import Count from main.models import Todolist -def get_annotated_todolists(): +def get_annotated_todolists(incomplete_only=False): qs = Todolist.objects.all() lists = qs.select_related('creator').defer( 'creator__email', 'creator__password', 'creator__is_staff', @@ -13,8 +13,12 @@ def get_annotated_todolists(): incomplete = qs.filter(todolistpkg__complete=False).annotate( Count('todolistpkg')).values_list('id', 'todolistpkg__count') - # tag each list with an incomplete package count lookup = dict(incomplete) + + if incomplete_only: + lists = lists.filter(id__in=lookup.keys()) + + # tag each list with an incomplete package count for todolist in lists: todolist.incomplete_count = lookup.get(todolist.id, 0) -- cgit v1.2.3-2-g168b From 160a08bba5324b25abd9e866b884c91d75e597b0 Mon Sep 17 00:00:00 2001 From: Dan McGee Date: Tue, 20 Nov 2012 15:57:05 -0600 Subject: Improve performance of todolists query Use some standard SQL and split the query into two different parts to save a lot of unnecessary sorting and field retrieval at the database level. The `CASE WHEN complete THEN 1 ELSE 0 END` syntax should be accepted by any database that implements proper SQL; it was tested in PostgreSQL and sqlite3 without issues. Signed-off-by: Dan McGee --- todolists/utils.py | 41 ++++++++++++++++++++++++++--------------- 1 file changed, 26 insertions(+), 15 deletions(-) (limited to 'todolists/utils.py') diff --git a/todolists/utils.py b/todolists/utils.py index 94f39f71..03c47931 100644 --- a/todolists/utils.py +++ b/todolists/utils.py @@ -1,26 +1,37 @@ +from django.db import connections, router from django.db.models import Count -from main.models import Todolist +from main.models import Todolist, TodolistPkg -def get_annotated_todolists(incomplete_only=False): - qs = Todolist.objects.all() - lists = qs.select_related('creator').defer( - 'creator__email', 'creator__password', 'creator__is_staff', - 'creator__is_active', 'creator__is_superuser', - 'creator__last_login', 'creator__date_joined').annotate( - pkg_count=Count('todolistpkg')).order_by('-date_added') - incomplete = qs.filter(todolistpkg__complete=False).annotate( - Count('todolistpkg')).values_list('id', 'todolistpkg__count') +def todo_counts(): + sql = """ +SELECT list_id, count(*), sum(CASE WHEN complete THEN 1 ELSE 0 END) + FROM todolist_pkgs + GROUP BY list_id + """ + database = router.db_for_write(TodolistPkg) + connection = connections[database] + cursor = connection.cursor() + cursor.execute(sql) + results = cursor.fetchall() + return {row[0]: (row[1], row[2]) for row in results} - lookup = dict(incomplete) - if incomplete_only: - lists = lists.filter(id__in=lookup.keys()) +def get_annotated_todolists(incomplete_only=False): + lists = Todolist.objects.all().select_related( + 'creator').order_by('-date_added') + lookup = todo_counts() - # tag each list with an incomplete package count + # tag each list with package counts for todolist in lists: - todolist.incomplete_count = lookup.get(todolist.id, 0) + counts = lookup.get(todolist.id, (0, 0)) + todolist.pkg_count = counts[0] + todolist.complete_count = counts[1] + todolist.incomplete_count = counts[0] - counts[1] + + if incomplete_only: + lists = [l for l in lists if l.incomplete_count > 0] return lists -- cgit v1.2.3-2-g168b From c8ece67cec9c421ac0c711554edd34f022623b45 Mon Sep 17 00:00:00 2001 From: Dan McGee Date: Fri, 28 Dec 2012 00:27:20 -0600 Subject: Convert to using new todolist models everywhere This is a rather widespread set of changes converting usage to the new todo list and todo list package model recently introduced. The data migration is not included in this commit. After this commit, the old model should no longer be referenced anywhere. Signed-off-by: Dan McGee --- todolists/utils.py | 14 +++++++------- 1 file changed, 7 insertions(+), 7 deletions(-) (limited to 'todolists/utils.py') diff --git a/todolists/utils.py b/todolists/utils.py index 03c47931..d084c645 100644 --- a/todolists/utils.py +++ b/todolists/utils.py @@ -1,26 +1,26 @@ from django.db import connections, router from django.db.models import Count -from main.models import Todolist, TodolistPkg +from .models import Todolist, TodolistPackage def todo_counts(): sql = """ -SELECT list_id, count(*), sum(CASE WHEN complete THEN 1 ELSE 0 END) - FROM todolist_pkgs - GROUP BY list_id +SELECT todolist_id, count(*), sum(CASE WHEN status = %s THEN 1 ELSE 0 END) + FROM todolists_todolistpackage + GROUP BY todolist_id """ - database = router.db_for_write(TodolistPkg) + database = router.db_for_write(TodolistPackage) connection = connections[database] cursor = connection.cursor() - cursor.execute(sql) + cursor.execute(sql, [TodolistPackage.COMPLETE]) results = cursor.fetchall() return {row[0]: (row[1], row[2]) for row in results} def get_annotated_todolists(incomplete_only=False): lists = Todolist.objects.all().select_related( - 'creator').order_by('-date_added') + 'creator').order_by('-created') lookup = todo_counts() # tag each list with package counts -- cgit v1.2.3-2-g168b From 39a603bf65c4aec780e4711074e9ed27fb7c301e Mon Sep 17 00:00:00 2001 From: Dan McGee Date: Sat, 29 Dec 2012 12:26:50 -0600 Subject: Defer the 'raw' field when listing todolists A lot like skipping fetching of the news content; we definitely don't need this just to list the todolists on index pages. Signed-off-by: Dan McGee --- todolists/utils.py | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'todolists/utils.py') diff --git a/todolists/utils.py b/todolists/utils.py index d084c645..0daca3b6 100644 --- a/todolists/utils.py +++ b/todolists/utils.py @@ -19,7 +19,7 @@ SELECT todolist_id, count(*), sum(CASE WHEN status = %s THEN 1 ELSE 0 END) def get_annotated_todolists(incomplete_only=False): - lists = Todolist.objects.all().select_related( + lists = Todolist.objects.all().defer('raw').select_related( 'creator').order_by('-created') lookup = todo_counts() -- cgit v1.2.3-2-g168b From 7952fe0ede3a5a68a64f05eccb180194394652f3 Mon Sep 17 00:00:00 2001 From: Dan McGee Date: Mon, 31 Dec 2012 11:31:35 -0600 Subject: Mark todolist packages as removed rather than deleting them This makes it easier to see the progression of a todolist and its contents easier since we are no longer losing the data. Signed-off-by: Dan McGee --- todolists/utils.py | 1 + 1 file changed, 1 insertion(+) (limited to 'todolists/utils.py') diff --git a/todolists/utils.py b/todolists/utils.py index 0daca3b6..e86d9054 100644 --- a/todolists/utils.py +++ b/todolists/utils.py @@ -8,6 +8,7 @@ def todo_counts(): sql = """ SELECT todolist_id, count(*), sum(CASE WHEN status = %s THEN 1 ELSE 0 END) FROM todolists_todolistpackage + WHERE removed IS NULL GROUP BY todolist_id """ database = router.db_for_write(TodolistPackage) -- cgit v1.2.3-2-g168b From e9e1c071654edd7b95e20c8105abbc23f426cecc Mon Sep 17 00:00:00 2001 From: Dan McGee Date: Tue, 22 Jan 2013 16:47:43 -0600 Subject: Show staging version on todolist view page If one exists, it is easy enough to show it here so in-progress todolists can easily be cross-checked with the current state of the repository. Signed-off-by: Dan McGee --- todolists/utils.py | 19 +++++++++++++++++++ 1 file changed, 19 insertions(+) (limited to 'todolists/utils.py') diff --git a/todolists/utils.py b/todolists/utils.py index e86d9054..51a75a3c 100644 --- a/todolists/utils.py +++ b/todolists/utils.py @@ -2,6 +2,7 @@ from django.db import connections, router from django.db.models import Count from .models import Todolist, TodolistPackage +from packages.models import Package def todo_counts(): @@ -36,4 +37,22 @@ def get_annotated_todolists(incomplete_only=False): return lists + +def attach_staging(packages, list_id): + '''Look for any staging version of the packages provided and attach them + to the 'staging' attribute on each package if found.''' + pkgnames = TodolistPackage.objects.filter( + todolist_id=list_id).values('pkgname') + staging_pkgs = Package.objects.normal().filter(repo__staging=True, + pkgname__in=pkgnames) + # now build a lookup dict to attach to the correct package + lookup = {(p.pkgname, p.arch): p for p in staging_pkgs} + + annotated = [] + for package in packages: + in_staging = lookup.get((package.pkgname, package.arch), None) + package.staging = in_staging + + return annotated + # vim: set ts=4 sw=4 et: -- cgit v1.2.3-2-g168b From b7b24740640e24883cd17fd683e1d465fbb343f8 Mon Sep 17 00:00:00 2001 From: Dan McGee Date: Tue, 16 Apr 2013 22:12:01 -0500 Subject: Various minor code cleanups and fixes Most of these were suggested by PyCharm, and include everything from little syntax issues and other bad smells to dead or bad code. Signed-off-by: Dan McGee --- todolists/utils.py | 1 - 1 file changed, 1 deletion(-) (limited to 'todolists/utils.py') diff --git a/todolists/utils.py b/todolists/utils.py index 51a75a3c..7b98c887 100644 --- a/todolists/utils.py +++ b/todolists/utils.py @@ -1,5 +1,4 @@ from django.db import connections, router -from django.db.models import Count from .models import Todolist, TodolistPackage from packages.models import Package -- cgit v1.2.3-2-g168b