databases/sqlports, a Database of Ports

Marc Espie, espie@, recently added an sqlite database of ports. Here is some background information:

There's a new port called sqlports in the tree. It's an sqlite database containing most information related to ports in the ports tree.

It's already useful as it is, by using a tool called sqlitebrowser which is also part of the ports tree.

But I expect that enterprising people will run with this and build all kind of interesting dynamic web pages that could present and organize this information.

Some examples

Assuming you've got this database installed, and you're looking at it in sqlitebrowser.

• How many ports seem to use perl

                                                                                                                  
  SELECT COUNT(PKGPATH) FROM Ports WHERE CONFIGURE_STYLE LIKE '%perl%'                                                 
  

• All ports in the graphics category

                                                                                                                  
  SELECT DISTINCT PKGPATH FROM Categories WHERE VALUE='graphics'                                                       
  

• Ports that need GMAKE, but not LIBTOOL

                                                                                                                  
  SELECT DISTINCT PKGPATH FROM Ports WHERE USE_GMAKE=1 AND USE_LIBTOOL IS NULL                                         
  

How it works

There's a new introspection mechanism in the ports tree, the target dumpvars that dumps most variables value to standard output (think of that as an extended describe but not for human consumption.

The output from dumpvars is fed into infrastructure/package/mksqlitedb, a perl script which turns this information into some main table, and does some extra processing for relevant variables (simple object-oriented structure, containing a variable name/class mapping, and using TemplateMethod to handle all kind of variables.

The sqlports port is just the glue that invokes those mechanisms correctly and packages the result.

Design Considerations

People used to mysql or postgresql may wonder about the use of sqlite. Several reasons for that.

• No heavy-weight setup. sqlite uses plain old text files.
• Wide-spread support. All relevant languages have sqlite bindings these days.
• Efficient storage. No need to know how big any field will grow.

I expect that some people will want to create postgresql/mysql databases anyways, and that they will send me relevant changes to adapt mksqlports