The (at least initial) purpose of this page is to list some problems in the ports system that could be solved using SQLite database for pkgdb. As the discussion on mailing list and at the in this document shows, SQLite is not the *only* way these problems could be solved, but it may be the most convenient and with minimal development cost.

/!\ Until announced at the freebsd-hackers mailing list, this is unfinished WORK IN PROGRESS.

Some benefits of SQLite

1. transaction safety

The problem:

beastie:/usr/ports/distfiles/xorg# pkg_info | wc -l
pkg_info: the package info for package 'xf86-input-summa-1.1.0' is corrupt
beastie:/usr/ports/distfiles/xorg# pkg_info | wc -l
pkg_info: show_file: can't open '+COMMENT' for reading

The output above was gained by executing pkg_info twice in a row while installing several ports ( 7.2 in particular). As it shows, there's no concept of "transactions" in the "all-or-nothing" sense, and no support for concurrently running package tools even if they are as simple as pkg_info. Problems that can arise from these are:

How SQLite could help:


2. Tool/library availability

Text files are incredibly well supported for simple operations (reading and writing) by many tools, and developers can easily support them in their own programs when needed. But moving away from the most simple operations, anything more complex than sequential reading and appending to the end of the file quickly becomes tedious and error-prone. Non-atomic operations require locking but since the ports database is used by many utilities (some of which are not in the base system), there's no guarantee that all those programs will consistently lock it.

How SQlite could help:


3. Speed

Tired of a simple command like:

pkg_info | grep apache

taking minutes to complete on a system with many installed ports? Be assured that a SQLite database query fetching the information which pkg_info displays in this mode takes on the order of milliseconds to complete. In all probability, in the above command, time taken by "grep" to parse the output will probably match the time taken by sqlite to generate it.

4. SQL squeamishness

Many hard-core C developers are understandably wary of "helper" languages like SQL. Though SQL really is evil to some extent and exists only because there is nothing better and more wide-spread out there, it shouldn't be dismissed so easily. Think of it the same way you would think of Regular expressions inside a C program - it's a separate, helper language to do something in an easier way.

Besides, in all probability, the raw interface with SQL will be abstracted into lower levels of libpkg and regular mechanisms and functions will be used to pass data within the programs. However, the programs will *have* to be aware they are working on a transactional database and avoid doing silly things like locking the database as the first line in main() and releasing it in atexit()...

PortsUsingSQLite (last edited 2010-09-02T10:21:42+0000 by IvanVoras)