Remove a package with no builds

[Mike McLean]

First let me say that I caution against directly altering the koji 
database except in very narrow circumstances.

The sql commands Eric wrote are incomplete and potentially destructive. 
Please be cautious about running sql commands you might find on the 
internet.

On 07/01/2010 09:40 PM, Eric Zhong wrote:
>   koji ?
>
> delete package:
> ============
>
> psql -U koji koji

The request was to remove a package that had not been used. This set of 
sql commands is overkill and furthermore does not appear to affect the 
table Nathan was actually concerned about (the package table).

> delete from tag_listing where build_id in (select id from build where
> pkg_id in (select id from package where name = 'libXext'));
> delete from rpmsigs where rpm_id in (select id from rpminfo where
> build_id in (select id from build where pkg_id in (select id from
> package where name = 'libXext')));
> delete from buildroot_listing where rpm_id in (select id from rpminfo
> where build_id in (select id from build where pkg_id in (select id
> from package where name = 'libXext')));
> delete from rpminfo where build_id in (select id from build where
> pkg_id in (select id from package where name = 'libXext'));
> delete from build where pkg_id = (select id from package where name =
> 'libXext');
> delete from tag_packages where package_id = (select id from package
> where name = 'libXext');
> delete from package where name = 'libXext';

This is a potentially very destructive set of commands. If a package id 
has references in these other tables, then it probably shouldn't be deleted.

If you have really added a package with an incorrect name and it has not 
been used, then it will only be referenced in two tables: package and 
tag_packages. Frankly, this is pretty harmless, but if you must remove 
the entry, then this will do it.

-- begin;
-- delete from tag_packages where package_id = (select id from package 
where name = 'SOME-TYPO');
-- delete from package where name = 'SOME-TYPO';
-- commit;
...or if something goes wrong:
-- rollback;

(of course you'll need to strip the "--" from the start of each line for 
this to not be a comment.)
You definitely want to keep this in a transaction (since nothing 
references the tag_packages table, the first command will go through 
even if you type the wrong package name).
If the second delete gives you any foreign key constraint violations 
then you have done something with the package besides just add it and 
you should really consider leaving it alone.

> delete build :
> ==========
>
> delete from tag_listing where build_id in (select id from build where
> pkg_id = (select id from package where name = 'serefpolicy') and
> version = '2.4.6' and release = '203.el5.ns5.se.09');

> delete from rpmsigs where rpm_id in (select id from rpminfo where
> build_id in (select id from build where pkg_id = (select id from
> package where name = 'serefpolicy') and version = '2.4.6' and release
> = '203.el5.ns5.se.09'));

> delete from buildroot_listing where rpm_id in (select id from rpminfo
> where build_id in (select id from build where pkg_id = (select id from
> package where name = 'serefpolicy') and version = '2.4.6' and release
> = '203.el5.ns5.se.09'));

> delete from rpminfo where build_id in (select id from build where
> pkg_id = (select id from package where name = 'serefpolicy') and
> version = '2.4.6' and release = '203.el5.ns5.se.09');

> delete from build where pkg_id = (select id from package where name =
> 'serefpolicy') and version = '2.4.6' and release =
> '203.el5.ns5.se.09';

This is an even more destructive set of commands. What these commands 
attempt to do is delete a build without a trace even if it is currently 
referenced.

Koji already has the capability to (sanely) delete a build. It doesn't 
have its own direct cli subcommand, but you can run it like so:

# brew call deleteBuild  fake-1.0-6

There are three things that will keep this from going through:
- build currently tagged
- build was used in a buildroot of another (non-deleted) build
- build was used in some other buildroot recently
If a build has such references, then you probably shouldn't be deleting it.

If there are no such references, then this command will delete the build 
from disk, delete any signature data for it, and mark the build deleted. 
It will still have a small presence in the db as historical data (tag 
history, deleted build entry, etc)


> 2010/7/2 Nathan Blackham<kemotaha at gmail.com>
>
>> I added a package with a typo to a tag.  I now have the bad package
>> name show up in the package list.  There are no builds against it.  Is
>> there an easy way to delete?
>>
>> I would guess it probably requires a sql query?  Anyone have one
>> handy, to pull it out of all the dependencies?