Merge lp:~edwin-grubbs/launchpad/bug-663861-product-series-timeout-part1 into lp:launchpad/db-devel

[Edwin Grubbs]

This branch adds an index to the ProductSeries table to allow efficient batching. I deleted the last merge proposal since I had erroneously made changes on top of devel instead of db-devel.

[Robert Collins (lifeless)]

So, a few things here.
Patch #2208-26-0

I thought we had a function for this in the DB already? Could you have a look for it and preferentially use it?

If we don't have one, this seems fine - but I think you should follow this patch up with a test that you get the sort order you expect.

review: Approve

[Edwin Grubbs (edwin-grubbs)]

> So, a few things here.
> Patch #2208-26-0

Done.

> I thought we had a function for this in the DB already? Could you have a look
> for it and preferentially use it?

I can't find another one that will work properly. I added the milestone_sort_key() recently, but that takes a date field in addition to the version number.

> If we don't have one, this seems fine - but I think you should follow this
> patch up with a test that you get the sort order you expect.

I'll definitely add a test for that in the next branch.

=== added file 'database/schema/patch-2208-26-0.sql'
--- database/schema/patch-2208-26-0.sql	1970-01-01 00:00:00 +0000
+++ database/schema/patch-2208-26-0.sql	2010-10-28 16:17:49 +0000
@@ -0,0 +1,9 @@
+-- Copyright 2010 Canonical Ltd.  This software is licensed under the
+-- GNU Affero General Public License version 3 (see the file LICENSE).
+SET client_min_messages=ERROR;
+
+CREATE INDEX productseries_name_sort
+ON ProductSeries
+USING btree (version_sort_key(name));
+
+INSERT INTO LaunchpadDatabaseRevision VALUES (2208, 26, 0);

=== modified file 'database/schema/security.cfg'
--- database/schema/security.cfg	2010-10-26 12:53:04 +0000
+++ database/schema/security.cfg	2010-10-28 16:17:49 +0000
@@ -18,6 +18,7 @@
 public.calculate_bug_heat(integer)         = EXECUTE
 public.debversion_sort_key(text)           = EXECUTE
 public.milestone_sort_key(timestamp without time zone, text) = EXECUTE
+public.version_sort_key(text)              = EXECUTE
 public.null_count(anyarray)                = EXECUTE
 public.valid_name(text)                    = EXECUTE
 public.valid_bug_name(text)                = EXECUTE

=== modified file 'database/schema/trusted.sql'
--- database/schema/trusted.sql	2010-10-05 12:05:50 +0000
+++ database/schema/trusted.sql	2010-10-28 16:17:49 +0000
@@ -1814,3 +1814,22 @@
 
 COMMENT ON FUNCTION milestone_sort_key(timestamp, text) IS
 'Sort by the Milestone dateexpected and name. If the dateexpected is NULL, then it is converted to a date far in the future, so it will be sorted as a milestone in the future.';
+
+
+CREATE OR REPLACE FUNCTION version_sort_key(version text) RETURNS text
+LANGUAGE plpythonu IMMUTABLE RETURNS NULL ON NULL INPUT AS
+$$
+    # If this method is altered, then any functional indexes using it
+    # need to be rebuilt.
+    import re
+
+    [version] = args
+
+    def substitute_filled_numbers(match):
+        return match.group(0).zfill(5)
+
+    return re.sub(u'\d+', substitute_filled_numbers, version)
+$$;
+
+COMMENT ON FUNCTION version_sort_key(text) IS
+'Sort a field as version numbers that do not necessarily conform to debian package versions (For example, when "2-2" should be considered greater than "1:1"). debversion_sort_key() should be used for debian versions.';