629741 - Proctdims needs sortable version column

Status: RESOLVED FIXED

(Socorro :: General, task)

Description

[[:jberkus] Josh Berkus]

Currently the "version" column contained within productdims won't do two things:

1. It will not sort correctly
2. We can't reliably "group by" parts of the vesion number (i.e. everything in version 3.6 production releases, or everying in pre releases in the 4 series).

We need to store "normalized" version number information in the database so that it can be used in queries.  Lars already has code to produce such data, so"

1. Add columns to productdims to hold normalized version information
2. Run Lars' code to backfill this data
3. Improve the code which populates productdims to populate the normalized version info.

Comment 1

[Ryan Snyder [:ryansnyder] [:rsnyder] [:rysny]]

Josh, per our IRC conversation about specs for a web service that could return data for products, versions & builds.
https://wiki.mozilla.org/Socorro/SocorroServer/Web_Services/Products_API

Comment 3

[[:jberkus] Josh Berkus]

FYI, the database code for this feature is done.  It just needs deployment.

Comment 4

[[:jberkus] Josh Berkus]

Attachment: Version parsing function in PL/Perl

Version parsing function in PL/Perl, to be installed in database.

Comment 5

[[:jberkus] Josh Berkus]

Attachment: Versioning change script in SQL

SQL file making a number of changes to clean up versions as well as adding version sorting functionality.

Note that the contrib module CITEXT needs to be installed in the database prior to executing the scripts in this file, and the tk_version.sql file needs to be installed first.

Adds the table productdims_version_sort to track sortable versions.   This table does not need to updated via the UI or visible there.  

Adds the column sort_key to productdims.  This allows any UI which accesses a group of versions to sort on (product, version) and get a proper version sort.

Comment 6

[Robert Kaiser]

Should bug 564088 and bug 604882 be marked dependent on this? They at least all sound related to the same problem...

Comment 7

[[:jberkus] Josh Berkus]

TODO still on this bug:

add code to schema.py

Comment 8

[[:jberkus] Josh Berkus]

We need the following changes in schema.py to support this feature:

Somewhere near the beginning, add:
CREATE LANGUAGE plperl;

===

Modify the table productdims to this new definition:

CREATE TABLE productdims (
    id SERIAL NOT NULL,
    product citext NOT NULL,
    version citext NOT NULL,
    branch text NOT NULL,
    release release_enum,
    sort_key integer
);

ALTER TABLE productdims
    ADD CONSTRAINT productdims_pkey1 PRIMARY KEY (id);

CREATE UNIQUE INDEX productdims_product_version_key ON productdims USING btree (product, version);

CREATE INDEX productdims_sort_key ON productdims USING btree (product, sort_key);

====

Change the definition of table Builds to this new definition:

CREATE TABLE builds (
    product citext,
    version citext,
    platform citext,
    buildid bigint,
    platform_changeset text,
    filename text,
    date timestamp without time zone DEFAULT now(),
    app_changeset_1 text,
    app_changeset_2 text
);
CREATE UNIQUE INDEX builds_key ON builds USING btree (product, version, platform, buildid);

===

Create a new sorted versions table:

create table productdims_version_sort (
	id int not null unique,
	product citext not null,
	version citext not null,
	sec1_num1 int,
	sec1_string1 text,
	sec1_num2 int,
	sec1_string2 text,
	sec2_num1 int,
	sec2_string1 text,
	sec2_num2 int,
	sec2_string2 text,
	sec3_num1 int,
	sec3_string1 text,
	sec3_num2 int,
	sec3_string2 text,
	extra text,
	constraint productdims_version_sort_key primary key (product, version),
	constraint productdims_product_version_fkey foreign key ( product, version ) 
		references productdims(product, version) on delete cascade on update cascade
);

===
Add the version-parsing function:

CREATE OR REPLACE FUNCTION tokenize_version(
    version TEXT,
	OUT s1n1 INT,
	OUT s1s1 TEXT,
	OUT s1n2 INT,
	OUT s1s2 TEXT,
	OUT s2n1 INT,
	OUT s2s1 TEXT,
	OUT s2n2 INT,
	OUT s2s2 TEXT,
	OUT s3n1 INT,
	OUT s3s1 TEXT,
	OUT s3n2 INT,
	OUT s3s2 TEXT,
	OUT ext TEXT
) LANGUAGE plperl AS $$
    my $version = shift;
    my @parts = split /[.]/ => $version;
    my $extra;
    if (@parts > 3) {
        $extra = join '.', @parts[3..$#parts];
        @parts = @parts[0..2];
    }

    my @tokens;
    for my $part (@parts) {
        die "$version is not a valid toolkit version" unless $part =~ qr{\A
            ([-]?\d+)                    # number-a
            (?:
                ([-_a-zA-Z]+(?=-|\d|\z)) # string-b
                (?:
                    (-?\d+)              # number-c
                    (?:
                        ([^-*+\s]+)      # string-d
                    |\z)
                |\z)
            |\z)
        \z}x;
        push @tokens, $1, $2, $3, $4;
    }

    die "$version is not a valid toolkit version" unless @tokens;
    my @cols = qw(s1n1 s1s1 s1n2 s1s2 s2n1 s2s1 s2n2 s2s2 s3n1 s3s1 s3n2 s3s2 ext);
    return { ext => $extra, map { $cols[$_] => $tokens[$_] } 0..11 }
$$;

===

Add the following triggers to keep sorted versions updated automatically:

CREATE OR REPLACE FUNCTION product_version_sort_number (
	sproduct text )
RETURNS BOOLEAN
LANGUAGE plpgsql AS $f$
BEGIN
-- reorders the product-version list for a specific
-- product after an update
-- we just reorder the whole group rather than doing
-- something more fine-tuned because it's actually less
-- work for the database and more foolproof.

UPDATE productdims SET sort_key = new_sort
FROM  ( SELECT product, version, 
		row_number() over ( partition by product
			order by sec1_num1 ASC NULLS FIRST,
					sec1_string1 ASC NULLS LAST,
					sec1_num2 ASC NULLS FIRST,
					sec1_string2 ASC NULLS LAST,
					sec1_num1 ASC NULLS FIRST,
					sec1_string1 ASC NULLS LAST,
					sec1_num2 ASC NULLS FIRST,
					sec1_string2 ASC NULLS LAST,
					sec1_num1 ASC NULLS FIRST,
					sec1_string1 ASC NULLS LAST,
					sec1_num2 ASC NULLS FIRST,
					sec1_string2 ASC NULLS LAST,
					extra ASC NULLS FIRST)
					as new_sort
	 FROM productdims_version_sort
	 WHERE product = sproduct )
AS product_resort
WHERE productdims.product = product_resort.product
	AND productdims.version = product_resort.version
	AND ( sort_key <> new_sort OR sort_key IS NULL );

RETURN TRUE;
END;$f$;

CREATE OR REPLACE FUNCTION version_sort_insert_trigger ()
RETURNS TRIGGER
LANGUAGE plpgsql AS $f$
BEGIN
-- updates productdims_version_sort and adds a sort_key
-- for sorting, renumbering all products-versions if
-- required

-- add new sort record
INSERT INTO productdims_version_sort (
	id,
	product,
	version,
	sec1_num1,	sec1_string1,	sec1_num2,	sec1_string2,
	sec2_num1,	sec2_string1,	sec2_num2,	sec2_string2,
	sec3_num1,	sec3_string1,	sec3_num2,	sec3_string2,
	extra )
SELECT 
	NEW.id,
	NEW.product,
	NEW.version,
	s1n1,	s1s1,	s1n2,	s1s2,
	s2n1,	s2s1,	s2n2,	s2s2,
	s3n1,	s3s1,	s3n2,	s3s2,
	ext 
FROM tokenize_version(NEW.version);

-- update sort key
PERFORM product_version_sort_number(NEW.product);

RETURN NEW;
END; $f$;

CREATE TRIGGER version_sort_insert_trigger AFTER INSERT
ON productdims FOR EACH ROW EXECUTE PROCEDURE version_sort_insert_trigger();

CREATE OR REPLACE FUNCTION version_sort_update_trigger_before ()
RETURNS TRIGGER 
LANGUAGE plpgsql AS $f$
BEGIN
-- updates productdims_version_sort
-- should be called only by a cascading update from productdims

-- update sort record
SELECT 	s1n1,	s1s1,	s1n2,	s1s2,
	s2n1,	s2s1,	s2n2,	s2s2,
	s3n1,	s3s1,	s3n2,	s3s2,
	ext
INTO 
	NEW.sec1_num1,	NEW.sec1_string1,	NEW.sec1_num2,	NEW.sec1_string2,
	NEW.sec2_num1,	NEW.sec2_string1,	NEW.sec2_num2,	NEW.sec2_string2,
	NEW.sec3_num1,	NEW.sec3_string1,	NEW.sec3_num2,	NEW.sec3_string2,
	NEW.extra
FROM tokenize_version(NEW.version);

RETURN NEW;
END; $f$;

CREATE OR REPLACE FUNCTION version_sort_update_trigger_after ()
RETURNS TRIGGER 
LANGUAGE plpgsql AS $f$
BEGIN
-- update sort keys
PERFORM product_version_sort_number(NEW.product);
RETURN NEW;
END; $f$;

CREATE TRIGGER version_sort_update_trigger_before BEFORE UPDATE
ON productdims_version_sort FOR EACH ROW 
EXECUTE PROCEDURE version_sort_update_trigger_before();

CREATE TRIGGER version_sort_update_trigger_after AFTER UPDATE
ON productdims_version_sort FOR EACH ROW 
EXECUTE PROCEDURE version_sort_update_trigger_after();

Comment 9

[Robert Helmer [:rhelmer]]

Attachment: updates for schema.py

Translate comment 8 into schema.py

Tested that this loads without errors in my local dev DB.

Comment 10

[Laura Thomson :laura]

Comment on attachment 514834 [details] [diff] [review]
updates for schema.py

Assuming the underlying plperl is ok, fine by me.  (I know >< this much plperl.)

Comment 11

[Robert Helmer [:rhelmer]]

(In reply to comment #10)
> Comment on attachment 514834 [details] [diff] [review]
> updates for schema.py
> 
> Assuming the underlying plperl is ok, fine by me.  (I know >< this much
> plperl.)

I just made sure it loaded, didn't test the functionality at all (would be great to have functional tests for these though).

Committed revision 2966.

Comment 12

[Matt Brandt [:mbrandt]]

(In reply to comment #11)
> I just made sure it loaded, didn't test the functionality at all (would be
> great to have functional tests for these though).
> 
> Committed revision 2966.

I spoke with stephend and we're both stuck on which routes we can take to test this? Any help would be welcome.

Comment 13

[[:jberkus] Josh Berkus]

Rob,

What's exposed to the user which draws on the new sort column?

Comment 14

[Ryan Snyder [:ryansnyder] [:rsnyder] [:rysny]]

As far as I can remember, nothing in Socorro UI utilizes the new sort column on the site.

Comment 15

[Matt Brandt [:mbrandt]]

Thanks, we were weren't sure if this was exposed in the UI. In that case I'll back off from testing this.