Here's a more elaborate version of the print-index script.
It's without doubt the longest sql query I've ever written...
This *does* give identical lines to "make INDEX" apart from the
fact that the pkgpath selection mechanism is more thorough, so it may
give you identical paths, and also it does sort things in pkgpath order
instead of respecting the multi-packages order.
... but for any consumer, it can replace the 'index' file handily.
(todo: see how my sqlports-compact file works, maybe...)
#! /bin/sh
file=/usr/local/share/sqlports
cat <<'EOSQL' |sqlite3 $file
-- in order for group_concat to sort, you must do it in two steps
create temp view d1 (d, p, t) as
select
distinct((case pkgspec when '' then '' else pkgspec||":"
end)||dependspath) as fd,
fullpkgpath, type
from depends order by fd;
-- and now the view that's going to be used 3 times in the main request
create temp view d2 as
select group_concat(d, ' ') as dlist, p, t
from d1 group by p, t;
select fullpkgname,fullpkgpath,
(case prefix when '/usr/local' THEN "" else prefix end),
comment,descr,maintainer,categories,
libd.dlist, buildd.dlist, rund.dlist,
(case 1 when fullpkgpath in
-- thankful for for 2ndary tables, because onlyforarch has a weird value
-- when !defined, that cannot be tested for
(select fullpkgpath from onlyforarch)
then only_for_archs
else (case 1 when fullpkgpath in
(select fullpkgpath from notforarch)
then "!"||not_for_archs
else "any"
end)
end),
(case lower(PERMIT_PACKAGE_CDROM) when "yes" then "y" else "n" end),
(case lower(PERMIT_PACKAGE_FTP) when "yes" then "y" else "n" end),
(case lower(PERMIT_DISTFILES_FTP) when "yes" then "y" else "n" end)
from ports
-- yep this has to be selects, can't be where, because left join!
left join (select * from d2 where d2.t='L') as libd on
libd.p=fullpkgpath
left join (select * from d2 where d2.t='B') as buildd on
buildd.p=fullpkgpath
left join (select * from d2 where d2.t='R') as rund on
rund.p=fullpkgpath
where fullpkgpath in (select distinct canonical from paths)
order by fullpkgpath
EOSQL
