sqlports man page

gistfile1.txt

SQLPORTS(5)		      File Formats Manual		   SQLPORTS(5)

NAME
     sqlports - sqlite database for ports meta information

DESCRIPTION
     sqlports is built as a port by parsing the output of make dump-vars over
     the full ports tree.

     The database contains tables for automated tools, along with nice views
     for human consumption.

     Table names are prefixed with underscore, .e.g, `_Ports' contrary to
     views, e.g., `Paths'.

     The special table `Meta' contains only one entry, detailing the current
     database.

	   Meta (SchemaVersion, Hash, CurrentDate)
		    SchemaVersion follows the usual major.minor rules as
		    shared libraries, Hash is unique to each generated
		    database, CurrentDate is the date the database was
		    generated.

     Other tables are as follows:

	   _Paths (ID, FULLPKGPATH, PKGPATH, CANONICAL)
		    PKGPATH points to a PATHS entry corresponding to the
		    stripped down version of FULLPKGPATH, without flavors or
		    subpackage markers, or is null if FULLPKGPATH is already
		    stripped.  Every other FULLPKGPATH, PKGPATH, DEPENDSPATH
		    entry in the database points to this table.	 The
		    FULLPKGPATH is complete, including flavors and pseudo
		    flavors markers.  For every port with MULTI_PACKAGES
		    settings, one entry is written for each SUBPACKAGE.
		    CANONICAL points to the actual ID to use as an entry in
		    other tables, for FULLPKGPATH which don't have their own
		    entry.

	   _Ports(FULLPKGPATH, ...)
		    holds all the information retrieved through various
		    variables that is not stored in specialized tables, e.g.,:
			  AUTOCONF_VERSION
			  AUTOMAKE_VERSION
			  COMES_WITH
			  COMMENT
			  COMPILER
			  COMPILER_LANGS
			  DISTFILES
			  DISTNAME
			  DIST_SUBDIR
			  EPOCH
			  FIX_EXTRACT_PERMISSIONS
			  FULLPKGNAME
			  GH_*
			  HOMEPAGE
			  IGNORE
			  IS_INTERACTIVE
			  MAINTAINER
			  NO_*
			  PERMIT_*
			  PKGNAME
			  PKGSPEC
			  PKGSTEM
			  PKG_ARCH
			  PORTROACH
			  PORTROACH_COMMENT
			  PREFIX
			  PSEUDO_FLAVOR
			  REVISION
			  SEPARATE_BUILD
			  STATIC_PLIST
			  SUBPACKAGE
			  SUPDISTFILES
			  TEST_IS_INTERACTIVE
			  UPDATE_PLIST_ARGS
			  USE_*
		    Some of this information is stored as indexes in keyword
		    tables, and Yes/No variables have been replaced with 1/0.
		    Variables not present in a given port are left undefined.

		    Specifically, AUTOCONF_VERSION and AUTOMAKE_VERSION
		    reference _AutoVersion, MAINTAINER references _EMail,
		    PERMIT_DISTFILES and SEPARATE_BUILD reference _Keywords2,
		    PKG_ARCH references _Arch, and PREFIX references _Prefix.
		    Note that USE_LIBTOOL is 3-valued: 2 is gnu, 1 is yes,
		    undef is no.  Note that USE_WXNEEDED is 3-valued: 2 is
		    special, 1 is yes, undef is no.

		    MULTI_PACKAGES ports hold several entries with
		    corresponding FULLPKGPATH (after canonicalisation).

	   _Flavors(FULLPKGPATH, VALUE, N)

	   _PseudoFlavors(FULLPKGPATH, VALUE, N)

	   _Categories(FULLPKGPATH, VALUE, N)

	   _Multi(FULLPKGPATH, VALUE, SUBPKGPATH, N)
		    No values are stored for SUBPKGPATH=-.

	   _Modules(FULLPKGPATH, VALUE, N)

	   _Configure(FULLPKGPATH, VALUE, N)
		    Corresponds to CONFIGURE_STYLE

	   _ConfigureArgs(FULLPKGPATH, VALUE, N, QUOTETYPE)

	   _DebugConfigureArgs(FULLPKGPATH, VALUE, N, QUOTETYPE)

	   _MasterSites(FULLPKGPATH, N, VALUE)

	   _Makefiles(FULLPKGPATH, VALUE, N)
		    This tables contains only Makefiles from MAKE_FILE_LIST
		    that are not `default values' (always included makefiles).

	   _FixCRLFFiles(FULLPKGPATH, VALUE, N)

	   _NotForArch(FULLPKGPATH, VALUE, N)

	   _OnlyForArch(FULLPKGPATH, VALUE, N)
		    All of these variable values are actually ordered lists
		    (hence the N).

		    Each keyword table follows the same scheme
		    TABLENAME(KEYREF, VALUE)

	   _Depends(FULLPKGPATH, FULLDEPENDS, PKGSPEC, REST, DEPENDSPATH,
		    TYPE, N)
		    All depends are stored in a single table, the type is
			  0   LIB_DEPENDS
			  1   RUN_DEPENDS
			  2   BUILD_DEPENDS
			  3   TEST_DEPENDS
		    FULLDEPENDS is the full text of the dependency, which
		    parses as DEPENDSPATH, the actual PKGPATH we depend upon,
		    PKGSPEC, the spec we depend upon (if explicit), and REST
		    the remainder of the text (things like :configure and
		    such).

	   _Distfiles(FULLPKGPATH, VALUE, N, Sufx, Type)
		    All distfiles/patchfiles/supdistfiles are stored in a
		    single table, with Type
			  0   distfiles
			  1   patchfiles
			  2   supdistfiles
		    Note that N is :N from Sites, those are not considered
		    ordered lists.

	   _DPBProperties (FULLPKGPATH, VALUE, N)

	   _Wantlib(FULLPKGPATH, VALUE, EXTRA)
		    All the libraries the FULLPKGPATH depends upon, with
		    optional version number specification stored in EXTRA
		    coming from the WANTLIB variable.

	   _Multi(FULLPKGPATH, VALUE, SUBPKGPATH, N)
		    also contains the normalized pkgpath for the corresponding
		    subpackage.

	   _DebugPackages(FULLPKGPATH, VALUE, SUBPKGPATH, N)
		    contrary to _Multi, also contains `-' entries for packages
		    without subpackages which have debug packages.

	   _PkgPaths(FULLPKGPATH, Value, N)
		    all build_packages associated to a fullpkgpath.

	   _Broken(FULLPKGPATH, ARCH, TEXT)

	   _Shared_Libs(FULLPKGPATH, LIBNAME, VERSION)

	   _SubstVars(FULLPKGPATH, VALUE, N)

	   _TARGETS(FULLPKGPATH, VALUE)
     Some information, both in the main table and in secondary tables is
     stored as keyword references to other tables:
	   AUTOCONF_VERSION, AUTOMAKE_VERSION
				      _AUTOVERSION
	   FixCRLFFiles		      _FILENAME2
	   _DPBProperties.value	      _DPBKeys
	   MAINTAINER		      _EMAIL
	   CATEGORIES		      _CATEGORYKEYS
	   CONFIGURE		      _CONFIGURE_KEYS
	   MAKEFILES		      _FILENAME
	   MODULES		      _MODULEKEYS
	   PREFIX		      _PREFIX
	   _WANTLIB.VALUE, _SHARED_LIBS.LIBNAME
				      _LIBRARY
	   PERMIT_*, SEPARATE_BUILD   _KEYWORDS2
	   FLAVORS, PSEUDO_FLAVORS    _KEYWORDS
	   NOT_FOR_ARCHS, ONLY_FOR_ARCHS, PKG_ARCH
				      _ARCH
	   _SubstVars.value	      _substvarskey
	   TARGETS		      _TARGETKEYS
	   _Distfiles.value	      _Fetchfiles
     A few views are provided for convenience while building complex requests.

	   o   Ports: contains all info from _ports and from secondary tables
	       with indices replaced with text values.

	   o   * Depends_ordered, Categories_ordered, CompilerLinks_Ordered,
	       Configureargs_ordered, Configure_ordered,
	       DebugConfigureArgs_Ordered, DPBProperties_ordered,
	       Flavors_ordered, Makefiles_ordered, Modules_ordered,
	       NotForArch_ordered, OnlyforArch_ordered, PkgPaths_ordered,
	       PseudoFlavors_ordered, SubstVars_ordered, Targets_ordered,
	       distfiles_ordered, wantlib_ordered: reconstituted lists of text
	       values as a single string from the corresponding table.

	   o   each "keyword" list also has a corresponding view, e.g.,
	       _BROKEN -> BROKEN (PathId, FULLPKGPATH, Arch, Value) with text
	       fields, and PathId still the index entry for the fullpkgpath,
	       suitable for further joins.

	   o   canonical_depends: fullpkgpath/dependspath/dependstype from
	       _depends but normalized as canonical paths.

     The database also contains a caching table named `PortsQ' that contains
     the actual data from the `Ports' view.

EXAMPLES
     Looking at the details of a view:

	   sqlite> .schema depends
	   CREATE VIEW Depends AS
	       SELECT
		   _Paths.Id AS PathId,
		   _Paths.FullPkgPath AS FullPkgPath,
		   FullDepends,
		   PkgSpec,
		   Rest,
		   T0001.FullPkgpath AS DependsPath,
		   Type,
		   N
	       FROM _Depends
		   JOIN _Paths
		       ON _Paths.Canonical=_Depends.FullPkgPath
		   JOIN _Paths T0001
		       ON T0001.Canonical=DependsPath
	   /* Depends(PathId,FullPkgPath,FullDepends,PkgSpec,Rest,DependsPath,Type,N) */;
     List the fullpkgpaths of leaf ports (not a dependency of anything):

	   sqlite> select distinct fullpkgpath from _paths where pkgpath not in
	       (select pkgpath from _paths
		   join _depends on _depends.dependspath=_paths.id);

BUGS AND LIMITATIONS
     The optimizer in sqlite(1) doesn't care for non-standard constructs such
     as `group_concat' and doesn't optimize left joins away when it should, so
     even if you don't ask for agregated columns in the `Ports' view, it will
     compute them irregardless.	 Hence the `PortsQ' cache.

FILES
     /usr/local/share/sqlports

SEE ALSO
     sqlite3(1)

OpenBSD 7.3		       September 6, 2023		   OpenBSD 7.3