Using sqlite3

Posted on by

An sqlite database is an entire database that is contained in a single cross platform file.  It is used by many applications to store data and state information.  I’ll show you a few things to get you started using sqlite databases.

The Fedora software update tool,  yum, uses sqlite databases to store package information locally  from the yum repository.  I’ll be using my Fedora 12 server for this example.

First I’ll get some fresh data.


yum clean all
yum check-update

The fresh data will be found in /var/cache/yum.   I’m interested in files named *.sqlite.


find /var/cache/yum -name "*.sqlite"

/var/cache/yum/updates/1793ed5dc6773763df84b55d5515f96add374ca115e0e4151a70378f6ed323c8-primary.sqlite

The long filename is a bit unwieldy so I’ll copy it to a file that is easier to handle.


cp /var/cache/yum/updates/1793ed5dc6773763df84b55d5515f96add374ca115e0e4151a70378f6ed323c8-primary.sqlite /tmp/primary.sqlite

Now lets see a list of database tables.


sqlite3 /tmp/primary.sqlite .tables

conflicts  db_info    files      obsoletes  packages   provides   requires

Now lets dump the database to another file.


sqlite3 /tmp/primary.sqlite .dump > /tmp/primary.dump

Lets examine the dumpfile.  I want to see the column names from the packages table.


egrep "CREATE TABLE packages" /tmp/primary.dump

CREATE TABLE packages (  pkgKey INTEGER PRIMARY KEY,  pkgId TEXT,  name TEXT,  arch TEXT,  version TEXT,  epoch TEXT,  release TEXT,  summary TEXT,  description TEXT,  url TEXT,  time_file INTEGER,  time_build INTEGER,  rpm_license TEXT,  rpm_vendor TEXT,  rpm_group TEXT,  rpm_buildhost TEXT,  rpm_sourcerpm TEXT,  rpm_header_start INTEGER,  rpm_header_end INTEGER,  rpm_packager TEXT,  size_package INTEGER,  size_installed INTEGER,  size_archive INTEGER,  location_href TEXT,  location_base TEXT,  checksum_type TEXT);

Lets extract some data.


sqlite3 /tmp/primary.sqlite "select name, time_build, location_href from packages" | less

logstalgia|1277021205|logstalgia-1.0.0-1.fc12.x86_64.rpm
php-pear-Services-Weather|1257462896|php-pear-Services-Weather-1.4.5-1.fc12.noarch.rpm
geany-plugins-geanydoc|1266257186|geany-plugins-geanydoc-0.18-2.fc12.x86_64.rpm
dgc|1259416815|dgc-0.98-3.fc12.i686.rpm
autocorr-sk|1278184863|autocorr-sk-3.1.1-19.34.fc12.noarch.rpm
wireshark-devel|1268753377|wireshark-devel-1.2.6-2.fc12.i686.rpm
sazanami-mincho-fonts|1277357251|sazanami-mincho-fonts-0.20040629-9.1.fc12.noarch.rpm
moodle-cs|1277226792|moodle-cs-1.9.9-1.fc12.noarch.rpm
libmapi|1276209360|libmapi-6.40.0-2.fc12.i686.rpm
moodle-ro|1277226792|moodle-ro-1.9.9-1.fc12.noarch.rpm

As you can see sqlite is using the ‘|’ as a column separator.

Now lets do the same thing but with a perl program.  You’ll need the following two modules DBI and DBD::SQLite.


yum install perl-DBI perl-DBD-SQLite

#!/usr/bin/perl

use DBI;

$file="/tmp/primary.sqlite";

my $db = DBI->connect("dbi:SQLite:dbname=$file", { PrintError => 0,
RaiseError => 0 });

my $sth = $db->prepare("select name, time_build, location_href from packages");
$sth->execute();

my @row;
while ( @row = $sth->fetchrow_array() ) {
my ( $name, $time_build, $location_href ) = @row;
print "$name $time_build $location_href\n";
}
warn "Data fetching terminated early by error: $DBI::errstr\n"
if $DBI::err;

$db->disconnect
or warn "Error disconnecting: $DBI::errstr\n";

exit;

I hope you found this useful and it will inspire you to do more.

Leave a comment