On Mon, Mar 02, 2009 at 02:13:35PM +0100, Lucas Nussbaum wrote:
> On 02/03/09 at 13:15 +0100, Andreas Tille wrote:
> > On Mon, 2 Mar 2009, Lucas Nussbaum wrote:
> >
> >> The plan is to switch to Roger Leigh's work in UDD as well, but someone
> >> needs to do the work, and unfortunately, i'm very busy currently. See
> >> http://git.debian.org/?p=users/rleigh/sbuild.git;a=tree;f=db;hb=81fd39259953853632a7d0e2198cfc745d270fe3
> >> for his code.
> >
> > Did somebody just asked Roger whether he might volunteer?
>
> Roger, if you have time to walk me through the steps needed to get it
> working, please ping me on IRC.
You appeared to be away, so I'll write some brief instructions here.
Firstly, the code is now all present in the sbuild git repository:
git clone git://git.debian.org/git/buildd-tools/sbuild
The code you need is in the db directory. You can either
- build it as a package and install it,
- run ./configure --with-postgresql && cd db && make all install
- run cd db && make -f Makefile-pgxs all install
Note you need postgresql-server-dev-8.3 and libapt-pkg-dev installed.
One built and installed, you need to add the "debversion" type
to your database. Simply source "debversion.sql" (\i) from the
psql shell while connected to your database. This creates all
of the necessary types, operators and index classes.
NOTE: You need privileged database access (e.g. user postgres),
because you are loading untrusted code into the database server
(a shared object containing the C++ type and operator functions,
which wrap the libapt debian version comparison class).
Once this is done, you can see if it works!
# SELECT '4:4.3.2-1lenny+b2'::debversion;
debversion
-------------------
4:4.3.2-1lenny+b2
# SELECT '3.2-3'::debversion < '3.2-1'::debversion;
?column?
----------
f
(1 row)
You can now see that we can create instances of the debversion
type by casting from a text string, and check the version
comparison using this.
# \d testv
Table "public.testv"
Column | Type | Modifiers
---------+------+-----------
version | text | not null
# SELECT * FROM testv ORDER BY version ASC;
version
---------
4.2-4
4.3-2
4.3~rc1
4.4-1
4.4-1
(5 rows)
i.e. ordered with a simple lexical comparison provided by
the text operator class. Now, we can simply change the
column type to debversion:
# ALTER TABLE testv ALTER COLUMN version TYPE debversion;
ALTER TABLE
# \d testv
Table "public.testv"
Column | Type | Modifiers
---------+------------+-----------
version | debversion | not null
# SELECT * FROM testv ORDER BY version ASC;
version
---------
4.2-4
4.3~rc1
4.3-2
4.4-1
4.4-1
(5 rows)
Now we are ordering using "dpkg --compare-versions" ordering,
you can see the ~ is sorting correctly.
So, in summary:
- DUMP YOUR DATABASE FIRST in case something screws up!!
- build and install debversion.so
- install the debversion type in your database using debversion.sql
- verify it's working as shown above
- alter your version columns type from text to debversion using
ALTER TABLE t ALTER COLUMN x TYPE debversion;
- if the column isn't already indexed, you might want to create
an index on it for speeding up SELECTs
While the package is currently in sbuild.git, for use by
wanna-build, it's a completely independent thing, so it
would probably be best if I put this code in a separate
git repo since it's going to be used by quite a few
databases at this point.
Hope this helps! If you have any queries, problems or questions,
just get in touch.
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.
Attachment:
signature.asc
Description: Digital signature