FreeBSD pkgng for SQL Junkies!

[achix]

Hello, just tried accessing repository and local package information today and got excited!

Enter the SQLite3 SQL shell:

[cmd=root@smadev:~#] sqlite3 /var/db/pkg/local.sqlite[/cmd]
SQLite version 3.7.16.1 2013-03-29 13:44:34
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite>

Find how many packages are currently installed:

sqlite> [cmd]select count(*) from packages;[/cmd]
756
sqlite>

Find how many packages are available at the local copy of the remote repository:

[cmd=root@smadev:~#]sqlite3 /var/db/pkg/repo.sqlite[/cmd]
SQLite version 3.7.16.1 2013-03-29 13:44:34
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> [cmd]select count(*) from packages;[/cmd]
23117
sqlite>

Query for the packages schema:

sqlite> [cmd].schema packages[/cmd]
CREATE TABLE packages (id INTEGER PRIMARY KEY,origin TEXT UNIQUE NOT NULL,name TEXT NOT NULL,version TEXT NOT NULL,comment TEXT NOT NULL,desc TEXT NOT NULL,mtree_id INTEGER REFERENCES mtree(id) ON DELETE RESTRICT ON UPDATE CASCADE,message TEXT,arch TEXT NOT NULL,maintainer TEXT NOT NULL, www TEXT,prefix TEXT NOT NULL,flatsize INTEGER NOT NULL,automatic INTEGER NOT NULL,licenselogic INTEGER NOT NULL,infos TEXT, time INTEGER, pkg_format_version INTEGER);
sqlite>

Find all tables:

sqlite> [cmd].tables[/cmd]
categories       licenses         pkg_directories  scripts        
deps             mtree            pkg_groups       shlibs         
directories      options          pkg_licenses     users          
files            packages         pkg_shlibs     
groups           pkg_categories   pkg_users      
sqlite>

Fantastic. The power is just there to search/do whatever you want.

 

[fonz]

Thanks for posting!

When PKGNG really comes around (probably with 10.0-RELEASE) it's nice to know that it uses a relational database and SQLite can be used to root around in it. However, I do think it's probably best to just look and leave actual modifications to PKGNG itself; unless one really knows what one is doing, going around and changing stuff probably isn't such a good idea.

Side note: constructing complex SQL queries isn't my strong suit, but if someone could conjure up a query to detect "orphaned" ports/packages (or more to the point: things that were installed as a dependency but are no longer needed because the thing that depended on it has been removed) I'd be quite interested in that.

 

[wblock@]

You don't need a query, PKGNG already can delete dependencies that are no longer needed because the package requiring them was removed. I can't recall the option name, though, --autoremove?

 

[kpa]

It's pkg autoremove. It only works for packages that were marked as "automatic" during the installation, usually these are build time dependencies. If you for install for example devel/libtool explicitly it won't be marked as automatic and pkg autoremove won't offer to uninstall it.

 

[fonz]

usually these are build time dependencies.

You say usually. Would it also work for run time dependencies?

 

[kpa]

How to find packages that are marked as automatic:

pkg query -e '%a=1' '%n-%v'

This is a superset of packages listed by pkg autoremove because it includes packages that are still depended on.

Edit: More fun with pkg query, this produces the exact list that pkg autoremove produces:

pkg query -e '%a=1 && %#r=0' '%n-%v'.

As you can see, pkg query is a powerful front end to the sqlite database with a syntax that should stay stable even if the format of the sqlite database changes over time. I would recommend writing custom scripts around pkg query instead of using the sqlite database directly. If pkg query can not do something you want it to do, submit a change request at https://github.com/freebsd/pkg.

 

[kpa]

You say usually. Would it also work for run time dependencies?

Yeah, it applies to run time dependencies as well. It's of course most useful with build time dependencies that can be autoremoved right after a build because they are all marked as automatic.

 

[fonz]

Yeah, it applies to run time dependencies as well. It's of course most useful with build time dependencies that can be autoremoved right after a build because they are all marked as automatic.

Actually, I tend to use build jails (to produce binary packages) so I'd like to keep build time dependencies around (inside the build jail anyway) as long as they are needed. But if PKGNG is also capable of spotting when something is no longer needed because it was installed as a (build time or run time) dependency and everything that depended upon it has been removed, that will be quite nice.

P.S. Apologies to @achix, I had no intention to hijack this thread.

 

[achix]

But aren't run time dependencies meant to be needed at run time (for the whole life of a package)? Why would anyone want to remove them? Removal would/should fail anyway.

 

[kpa]

If you remove a leaf package, a package that is not depended on by other packages, its removal makes other packages candidates for removal because those packages are now leaf packages. The pkg autoremove helps in this case if the non-leaf packages that became leaf packages were marked as automatic during their installation. It's quite like what ports-mgmt/pkg_cutleaves does but takes advantage of the automatic flag of PKGNG.

 

[achix]

Aha, thanks, that makes sense. Leaf packages which are also automatic are definitely good candidates for removal. The -e evaluation-condition in your example above pkg query -e '%a=1 && %#r=0' '%n-%v' denotes just that.