[Buildd-tools-devel] PostgreSQL code for Debian version checking and sorting

[Roger Leigh]

On Sat, Jan 03, 2009 at 10:00:55PM +0100, Joerg Jaspert wrote:
> > Since it's derived from the text type you use in projectb for versions,
> > it's a drop-in replacement.  It uses code from Dpkg::Version for
> > version checking and comparison in PL/Perl, with PostgreSQL operators
> > in PL/pgSQL.  If we used the untrusted form of PL/PerlU we could
> > "use Dpkg::Version" directly and avoid the need to copy the code.
> 
> You know that we have, for years, a postgres module in c++ that is used
> for version comparision and is based on apts code?
> I dont know what your code does, havent looked, just pointing at it. Its
> in our git, src/

Ah, thanks!  I did look at the dak schema for equivalent functionality,
but didn't see this.  (BTW, I don't see a CREATE FUNCTION or any other
use of the sql-aptvc.so or the versioncmp function except in
dak/cruft_report.py where it is already presumably set up for it, which
is why I missed it.)

My code is partially equivalent, except it's not an untrusted language
(in current form), so it can be created by an unprivileged user.  It
creates a proper data type so instead of using

  version text

in your database schema, you can do

  version debversion

and it includes all the operators (= <> < > <= >=) including query
planner optimiser hints, as well as syntax checking on insert so you
can sort on versions, as well as index it (and therefore query and
join with high speed).  I think the debversion_compare is the bit that's
exactly the same; the operators wrap it to let the database use
versions directly.


Regards,
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.