bash/perl - Create local ubuntu packages sqlite database

Projects from the ubuntucy community...

bash/perl - Create local ubuntu packages sqlite database

Postby medigeek » Sat Jul 05, 2008 12:50 am

Create your own local Ubuntu database, using SQLite and a couple of perl/bash scripts.

You must have these installed:
  1. rsync or wget
    Note: The script favours the use of rsync first
  2. sqlite3
  3. bzip2
  4. perl
    Note: I was using 5.8.8 while coding
  5. bash

For Ubuntu we just need the sqlite3 package (the rest are pre-installed):
Code: Select all
sudo apt-get install sqlite3


Important notes:
The sqlite package is not sqlite3 - they're two different versions!

The scripts required are attached in a compressed .tar.gz format:
ubuntu-packages.tar.gz
(15.61 KiB) Downloaded 570 times


Usage:
- Extract
- Execute
Code: Select all
./packages2sqlite

The script will gather the Packages.bz2 from the Ubuntu archive and process them into SQL "INSERT INTO" lines, and finally import them into an SQLite database (default filename: ubuntu-packages.db)

Example SQLite queries:
Code: Select all
sqlite3 ubuntu-packages.db "select count([original-maintainer]) from packages where [original-maintainer] != ''"
sqlite3 ubuntu-packages.db "select count(\`original-maintainer\`) from packages where \`original-maintainer\` != ''"
i=`sqlite3 -separator ", " ubuntu-packages.db "select count(package),count(distinct(package)) from packages"`; echo "All Ubuntu packages: $i unique"
i=(`sqlite3 -separator " " ubuntu-packages.db "select count(package),count(distinct(package)),(select count(distinct(package)) from packages where distro = 'hardy') from packages"`); echo -e "All Ubuntu packages: ${i[0]}\nAll Ubuntu unique packages: ${i[1]}\nAll Ubuntu unique packages in hardy: ${i[2]}"
i=`sqlite3 ubuntu-packages.db "select sum(size) from packages where distro = 'hardy';"`; echo -n "Total size of all the packages of distribution hardy: "; perl -e "print $i / (1024**3);" -e 'print " GB\n;"'
sqlite3 -separator , ubuntu-packages.db "select id,package,distro,architecture,version from packages where package='ubuntu-desktop' order by version DESC;"


Compression:
I've added 3 types of compression, 7-zip, tar/gzip and tar/bzip2.
If you want a compressed copy, open packages2sqlite file in your favourite text editor, scroll down to "#Compress" and uncomment one or more lines under that line.

P.S. I'll be glad to receive any comments you have or if you stumble upon any bugs!
The code is probably messy, I'm not made to be a programmer :mrgreen:

UPDATES:
- Switched the license to GPLv3
- Added the code at http://bazaar.launchpad.net/~medigeek/% ... lite/files
User avatar
medigeek
How much time you spend in here?
 
Posts: 856
Joined: Fri May 23, 2008 3:19 pm
Location: Serbia

Re: bash/perl - Create local ubuntu packages sqlite database

Postby theo » Mon Jul 07, 2008 10:41 am

What is this doing exactly? Creating a mirror of ubuntu repositories?
Some people like to look out of the window. Some others prefer to look at the curtains instead!

www.theo-andreou.org
User avatar
theo
31337 Guru
 
Posts: 3133
Joined: Tue May 06, 2008 10:10 am
Location: Paphos

Re: bash/perl - Create local ubuntu packages sqlite database

Postby medigeek » Tue Jul 08, 2008 2:50 pm

yes, as a database :) I think the sql files can be used for mysql or any other sql language, but I'm not sure, never tried it
User avatar
medigeek
How much time you spend in here?
 
Posts: 856
Joined: Fri May 23, 2008 3:19 pm
Location: Serbia

Re: bash/perl - Create local ubuntu packages sqlite database

Postby theo » Tue Jul 08, 2008 3:21 pm

Looks cool but are there any advantages to apt-proxy or apt-mirror?
Some people like to look out of the window. Some others prefer to look at the curtains instead!

www.theo-andreou.org
User avatar
theo
31337 Guru
 
Posts: 3133
Joined: Tue May 06, 2008 10:10 am
Location: Paphos

Re: bash/perl - Create local ubuntu packages sqlite database

Postby medigeek » Fri Jul 11, 2008 6:40 pm

I'm not trying to convince anyone to use it, it's just a database with information about new packages.
apt-proxy and apt-mirror keep the package (.deb) files you download, it doesn't have to do anything with this :)
Generally, I don't have a specific use for it (yet)! :lol:
You can use it by the way to extract the package info using the sqlite3 command line, the examples are inside the bash file "packages2sqlite":
Code: Select all
sqlite3 ubuntu-packages.db "select count([original-maintainer]) from packages where [original-maintainer] != ''"

sqlite3 ubuntu-packages.db "select count(\`original-maintainer\`) from packages where \`original-maintainer\` != ''"

i=`sqlite3 -separator ", " ubuntu-packages.db "select count(package),count(distinct(package)) from packages"`; echo "All Ubuntu packages: $i unique"

i=(`sqlite3 -separator " " ubuntu-packages.db "select count(package),count(distinct(package)),(select count(distinct(package)) from packages where distro = 'hardy') from packages"`); echo -e "All Ubuntu packages: ${i[0]}\nAll Ubuntu unique packages: ${i[1]}\nAll Ubuntu unique packages in hardy: ${i[2]}"

i=`sqlite3 ubuntu-packages.db "select sum(size) from packages where distro = 'hardy';"`; echo -n "Total size of all the packages of distribution hardy: "; perl -e "print $i / (1024**3);" -e 'print " GB\n;"'

sqlite3 -separator , ubuntu-packages.db "select id,package,distro,architecture,version from packages where package='ubuntu-desktop' order by version DESC;"


For example: The filenames (URL) for the hardy release from the repository hardy-updates:
Code: Select all
sqlite3 ubuntu-packages.db 'select filename from packages where distro = "hardy" and repository = "hardy-updates"' | sed -e 's,^,http://archive.ubuntu.com/ubuntu/,'


I use it for a bot called "perly" in #ubuntu-gr at freenode (and #ubuntu-cy of course).
If you call .package package-name it replies in greek with several info about the package:
17:54:19 < savvas> .package blubuntu
17:54:23 < perly> Δεν βρέθηκε τέτοιο πακέτο σε καμια έκδοση Ubuntu :(
17:54:29 < perly> Μήπως εννοούσες: blubuntu-gdm-theme blubuntu-look blubuntu-session-splashes blubuntu-theme blubuntu-wallpapers
17:54:37 < savvas> .package blubuntu-theme
17:54:41 < perly> Το πακέτο βρέθηκε στις ακόλουθες εκδόσεις: hardy (0.2-0ubuntu3) intrepid (0.2-0ubuntu3) feisty (0.2-0ubuntu2) gutsy
(0.2-0ubuntu2)
17:54:46 < perly> Για να δείτε περισσότερες πληροφορίες, πρέπει να ψάξετε συγκεκριμένο πακέτο σε συγκεκριμένη έκδοση, για παράδειγμα:
.package blubuntu-theme hardy
17:54:48 < savvas> .package blubuntu-theme hardy
17:54:55 < perly> Για το blubuntu-theme στο hardy βρέθηκαν 2 σειρές με πληροφορίες:
17:55:00 < perly> Περιγραφή: Blubuntu look - GTK and Metacity theme The Blubuntu theme. . This package contains the GTK and Metacity
configuration for the Blubuntu look.
17:55:05 < perly> 1 - hardy/universe/binary-amd64 0.2-0ubuntu3 all Σύνδεσμος:
http://archive.ubuntu.com/ubuntu/pool/u ... u3_all.deb
17:55:10 < perly> 2 - hardy/universe/binary-i386 0.2-0ubuntu3 all Σύνδεσμος:
http://archive.ubuntu.com/ubuntu/pool/u ... u3_all.deb


It can also be used to make websites such as http://packages.ubuntu.com/ that run with a local database and output really fast the results.
User avatar
medigeek
How much time you spend in here?
 
Posts: 856
Joined: Fri May 23, 2008 3:19 pm
Location: Serbia


Return to Projects

Who is online

Users browsing this forum: No registered users and 1 guest

cron