Update dakdb to use debversion

To: debian-dak@lists.debian.org
Subject: Update dakdb to use debversion
From: Roger Leigh <rleigh@codelibre.net>
Date: Sat, 31 Jan 2009 11:45:30 +0000
Message-id: <[🔎] 20090131114529.GA8148@codelibre.net>

Hi,

Attached is a script to run through psql to update dak to use the
new libapt-based debversion type and operators.  This is based upon
the patch I posted a few days back with the following changes:

1) Change the version columns type back to text
2) Drop the debversion domain and associated functions
3) Create the new debversion type, operators, operator class and
   hash and aggregate functions [this is identical bar cosmetic
   changes]
4) Change the version columns type to debversion

I've done a few queries to test, and it looks like Postgres is clever
enough to rebuild the indexes using the new type.  It looks like
sorting works correctly, and queries on package/version do use the
index correctly when I check with EXPLAIN.

If someone (dato/Ganneff?) would like to double check that this is
OK and is working correctly for the queries which were previously
broken, I can then write an update script to go in dak/dakdb based
upon this script.  Testing on a scratch db based on a projectb 
dump, it looks OK, but it would be great if this could be confirmed
independently :-)


Thanks,
Roger

-- 
  .''`.  Roger Leigh
 : :' :  Debian GNU/Linux             http://people.debian.org/~rleigh/
 `. `'   Printing on GNU/Linux?       http://gutenprint.sourceforge.net/
   `-    GPG Public Key: 0x25BFB848   Please GPG sign your mail.

--- WannaBuild Database Schema for PostgreSQL                        -*- sql -*-
--- Debian version type and operators
---
--- Code derived from Dpkg::Version:
--- Copyright © Colin Watson <cjwatson@debian.org>
--- Copyright © Ian Jackson <iwj@debian.org>
--- Copyright © 2007 by Don Armstrong <don@donarmstrong.com>
---
--- PostgreSQL SQL, PL/pgSQL and PL/Perl:
--- Copyright © 2008 Roger Leigh <rleigh@debian.org>
---
--- This program is free software: you can redistribute it and/or modify
--- it under the terms of the GNU General Public License as published by
--- the Free Software Foundation, either version 2 of the License, or
--- (at your option) any later version.
---
--- This program is distributed in the hope that it will be useful, but
--- WITHOUT ANY WARRANTY; without even the implied warranty of
--- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
--- General Public License for more details.
---
--- You should have received a copy of the GNU General Public License
--- along with this program.  If not, see
--- <http://www.gnu.org/licenses/>.

ALTER TABLE source ALTER COLUMN version TYPE text;
ALTER TABLE binaries ALTER COLUMN version TYPE text;

DROP DOMAIN debversion CASCADE;
DROP FUNCTION debversion_compare_single(text,text);

SET search_path = public;

CREATE TYPE debversion;

CREATE OR REPLACE FUNCTION debversionin(cstring)
  RETURNS debversion
  AS 'textin'
  LANGUAGE 'internal'
  IMMUTABLE STRICT;

CREATE OR REPLACE FUNCTION debversionout(debversion)
  RETURNS cstring
  AS 'textout'
  LANGUAGE 'internal'
  IMMUTABLE STRICT;

CREATE OR REPLACE FUNCTION debversionrecv(internal)
  RETURNS debversion
  AS 'textrecv'
  LANGUAGE 'internal'
  STABLE STRICT;

CREATE OR REPLACE FUNCTION debversionsend(debversion)
  RETURNS bytea
  AS 'textsend'
  LANGUAGE 'internal'
  STABLE STRICT;

CREATE TYPE debversion (
    INPUT          = debversionin,
    OUTPUT         = debversionout,
    RECEIVE        = debversionrecv,
    SEND           = debversionsend,
    INTERNALLENGTH = VARIABLE,
    STORAGE        = extended,
    -- make it a non-preferred member of string type category
    CATEGORY       = 'S',
    PREFERRED      = false
);

COMMENT ON TYPE debversion IS 'Debian package version number';

CREATE OR REPLACE FUNCTION debversion(bpchar)
  RETURNS debversion
  AS 'rtrim1'
  LANGUAGE 'internal'
  IMMUTABLE STRICT;

CREATE CAST (debversion AS text)    WITHOUT FUNCTION AS IMPLICIT;
CREATE CAST (debversion AS varchar) WITHOUT FUNCTION AS IMPLICIT;
CREATE CAST (debversion AS bpchar)  WITHOUT FUNCTION AS ASSIGNMENT;
CREATE CAST (text AS debversion)    WITHOUT FUNCTION AS ASSIGNMENT;
CREATE CAST (varchar AS debversion) WITHOUT FUNCTION AS ASSIGNMENT;
CREATE CAST (bpchar AS debversion)  WITH FUNCTION debversion(bpchar);

CREATE OR REPLACE FUNCTION debversion_cmp (version1 debversion,
       	  	  	   		   version2 debversion)
  RETURNS integer AS '$libdir/debversion'
  LANGUAGE 'C'
  IMMUTABLE STRICT;
COMMENT ON FUNCTION debversion_cmp (debversion, debversion)
  IS 'Compare Debian versions';

CREATE OR REPLACE FUNCTION debversion_eq (version1 debversion,
       	  	  	   		  version2 debversion)
  RETURNS boolean AS '$libdir/debversion'
  LANGUAGE 'C'
  IMMUTABLE STRICT;
COMMENT ON FUNCTION debversion_eq (debversion, debversion)
  IS 'debversion equal';

CREATE OR REPLACE FUNCTION debversion_ne (version1 debversion,
       	  	  	   		  version2 debversion)
  RETURNS boolean AS '$libdir/debversion'
  LANGUAGE 'C'
  IMMUTABLE STRICT;
COMMENT ON FUNCTION debversion_ne (debversion, debversion)
  IS 'debversion not equal';

CREATE OR REPLACE FUNCTION debversion_lt (version1 debversion,
       	  	  	   		  version2 debversion)
  RETURNS boolean AS '$libdir/debversion'
  LANGUAGE 'C'
  IMMUTABLE STRICT;
COMMENT ON FUNCTION debversion_lt (debversion, debversion)
  IS 'debversion less-than';

CREATE OR REPLACE FUNCTION debversion_gt (version1 debversion,
       	  	  	   		  version2 debversion)
  RETURNS boolean AS '$libdir/debversion'
  LANGUAGE 'C'
  IMMUTABLE STRICT;
COMMENT ON FUNCTION debversion_gt (debversion, debversion)
  IS 'debversion greater-than';

CREATE OR REPLACE FUNCTION debversion_le (version1 debversion,
       	  	  	   		  version2 debversion)
  RETURNS boolean AS '$libdir/debversion'
  LANGUAGE 'C'
  IMMUTABLE STRICT;
COMMENT ON FUNCTION debversion_le (debversion, debversion)
  IS 'debversion less-than-or-equal';

CREATE OR REPLACE FUNCTION debversion_ge (version1 debversion,
       	  	  	   		  version2 debversion)
  RETURNS boolean AS '$libdir/debversion'
  LANGUAGE 'C'
  IMMUTABLE STRICT;
COMMENT ON FUNCTION debversion_ge (debversion, debversion)
  IS 'debversion greater-than-or-equal';

CREATE OPERATOR = (
  PROCEDURE = debversion_eq,
  LEFTARG = debversion,
  RIGHTARG = debversion,
  COMMUTATOR = =,
  NEGATOR = !=
);
COMMENT ON OPERATOR = (debversion, debversion)
  IS 'debversion equal';

CREATE OPERATOR != (
  PROCEDURE = debversion_ne,
  LEFTARG = debversion,
  RIGHTARG = debversion,
  COMMUTATOR = !=,
  NEGATOR = =
);
COMMENT ON OPERATOR != (debversion, debversion)
  IS 'debversion not equal';

CREATE OPERATOR < (
  PROCEDURE = debversion_lt,
  LEFTARG = debversion,
  RIGHTARG = debversion,
  COMMUTATOR = >,
  NEGATOR = >=
);
COMMENT ON OPERATOR < (debversion, debversion)
  IS 'debversion less-than';

CREATE OPERATOR > (
  PROCEDURE = debversion_gt,
  LEFTARG = debversion,
  RIGHTARG = debversion,
  COMMUTATOR = <,
  NEGATOR = >=
);
COMMENT ON OPERATOR > (debversion, debversion)
  IS 'debversion greater-than';

CREATE OPERATOR <= (
  PROCEDURE = debversion_le,
  LEFTARG = debversion,
  RIGHTARG = debversion,
  COMMUTATOR = >=,
  NEGATOR = >
);
COMMENT ON OPERATOR <= (debversion, debversion)
  IS 'debversion less-than-or-equal';

CREATE OPERATOR >= (
  PROCEDURE = debversion_ge,
  LEFTARG = debversion,
  RIGHTARG = debversion,
  COMMUTATOR = <=,
  NEGATOR = <
);
COMMENT ON OPERATOR >= (debversion, debversion)
  IS 'debversion greater-than-or-equal';

CREATE OPERATOR CLASS debversion_ops
DEFAULT FOR TYPE debversion USING btree AS
  OPERATOR 1 <  (debversion, debversion),
  OPERATOR 2 <= (debversion, debversion),
  OPERATOR 3 =  (debversion, debversion),
  OPERATOR 4 >= (debversion, debversion),
  OPERATOR 5 >  (debversion, debversion),
  FUNCTION 1 debversion_cmp(debversion, debversion);

CREATE OR REPLACE FUNCTION debversion_hash(debversion)
  RETURNS int4
  AS '$libdir/debversion'
  LANGUAGE 'C'
  IMMUTABLE STRICT;

CREATE OPERATOR CLASS debversion_ops
DEFAULT FOR TYPE debversion USING hash AS
  OPERATOR 1 = (debversion, debversion),
  FUNCTION 1 debversion_hash(debversion);

CREATE OR REPLACE FUNCTION debversion_smaller(version1 debversion,
					      version2 debversion)
  RETURNS debversion
  AS '$libdir/debversion'
  LANGUAGE 'C'
  IMMUTABLE STRICT;

CREATE OR REPLACE FUNCTION debversion_larger(version1 debversion,
					     version2 debversion)
  RETURNS debversion
  AS '$libdir/debversion'
  LANGUAGE 'C'
  IMMUTABLE STRICT;

CREATE AGGREGATE min(debversion)  (
  SFUNC = debversion_smaller,
  STYPE = debversion,
  SORTOP = <
);

CREATE AGGREGATE max(debversion)  (
  SFUNC = debversion_larger,
  STYPE = debversion,
  SORTOP = >
);

ALTER TABLE source ALTER COLUMN version TYPE debversion;
ALTER TABLE binaries ALTER COLUMN version TYPE debversion;

Attachment: signature.asc
Description: Digital signature

Reply to:

Prev by Date: [dak/master] more docstrings
Previous by thread: [dak/master] more docstrings
Index(es):
- Date
- Thread