#!/bin/sh
# list_popular_packages_without_manpages.sh
#						Version 0.12
#
# Script to compares Debian list of "binary-without-manpage" 
# With localy installed packages. Sorts the results according 
# to popularity-contest.
#					Licence GPL 2.0
#					Franklin PIAT (C) 2006
# This script requires :
#	wget
#	elinks
#	sqlite3

if [ 1 ]; then
# Retreive Lintian reports
[ ! -s /tmp/binary-without-manpage.txt ] && elinks -dump-width 200 -dump http://lintian.debian.org/reports/Tbinary-without-manpage.html -no-numbering -no-references > /tmp/binary-without-manpage.txt
sed -e "s/binary-without-manpage//" -e "s/[[:blank:]]\+//g" -e "s/:/\|/g" /tmp/binary-without-manpage.txt | grep -E "^W\|" | sed -e "s/^W|//" >   /tmp/binary-without-manpage.csv

#Retreive Popcon
[ ! -f /tmp/all-popcon-results.txt.gz ] && wget -O /tmp/all-popcon-results.txt.gz http://popcon.debian.org/all-popcon-results.txt.gz
zcat /tmp/all-popcon-results.txt.gz | grep -E "^Package:" | sed -e "s/^Package:[[:blank:]]*//" -e "s/[[:blank:]]\+/|/g" > /tmp/popcon-results.txt

#Retreive local packages
COLUMNS=200 dpkg --get-selections | grep -E "\<install" | cut -f 1 >/tmp/localdebs

#Use sqlite to merge data
[ -f /tmp/deb ] && rm /tmp/deb
sqlite3 /tmp/deb 'create table popcon(pkg,i INTEGER,o INTEGER,r INTEGER,n INTEGER);'
sqlite3 /tmp/deb 'create table noman(pkg,file);'
sqlite3 /tmp/deb 'create table localdebs(pkg);'
echo .import /tmp/popcon-results.txt  popcon |sqlite3 /tmp/deb
echo .import /tmp/binary-without-manpage.csv  noman |sqlite3 /tmp/deb
echo .import /tmp/localdebs localdebs |sqlite3 /tmp/deb
#Missing manpage where package-name = missing manpage-name
sqlite3 /tmp/deb 'select popcon.* from noman, popcon,localdebs where noman.pkg = noman.file and noman.pkg=localdebs.pkg and noman.pkg = popcon.pkg order by popcon.i desc ;' > /tmp/binary-name_IS_pkg-name.txt
#And a complete list
sqlite3 /tmp/deb 'select popcon.*,noman.file from noman, popcon,localdebs where noman.pkg=localdebs.pkg and noman.pkg = popcon.pkg order by popcon.i desc ;'>/tmp/complete_list.txt

cat <<ENDTAG >/dev/null
 sqlite /tmp/deb
create table popcon(pkg,i INTEGER,o INTEGER,r INTEGER,n INTEGER);
create table noman(pkg,file);
create table localdebs(pkg);
.separator ";"
.import /tmp/popcon-results.txt  popcon;
.import /tmp/binary-without-manpage.csv  noman;
.import sel localdebs;
.output /tmp/list1.txt;
select popcon.* from noman, popcon,localdebs where noman.pkg = noman.file and noman.pkg=localdebs.pkg and noman.pkg = popcon.pkg order by popcon.i desc ;
.output /tmp/list2.txt;
select popcon.*,noman.file from noman, popcon,localdebs where noman.pkg=localdebs.pkg and noman.pkg = popcon.pkg order by popcon.i desc ;
ENDTAG
sort -n -t "|" -k 2 -r /tmp/complete_list.txt > /tmp/complete_list-bis.txt
fi