Database structure

Database structure

This structure is intended for storing all information relevant to installability for all the releases, and possibly for all the architectures. It is made of various cross-referenced tables, in a way to make possible advanced SQL queries. For this reason, version strings are associated to index numbers to allow comparison of different versions. It is currently tuned for MySQL, essentially for performance reasons, the PostgreSQL Ocaml interface being less mature. It requires MySQL 5 for transaction safety and uses InnoDB. (See also: Mysql, Postgresql)

The following fields from Debian metadata is converted to a SQL database :

• Package
• Version
• Depends
• Pre-depends
• Replaces
• Enhances
• Provides
• Conflicts
• Essential
• Build-essential
• Source

The database is designed to store historical data and uses the following tables:

• units
  • This table attributes a unique number to every unit name
  • Its structure is:
    • ID : an integer
    • NAME : the unit name (limited to 64 characters)
• packages
  • This table describes all known packages and gives a mapping between version strings and integers. Because there is no way (that I know of) to use Debian's order on version strings with SQL, we give, together with the string giving the version number, an integer that encodes that string for the given package. In other words, for each unit, the order of the version numbers should be compatible with the Debian order of their version strings.
  • Its fields are:
    • ID : a unique identifying integer for the package
    • UNIT_ID : key for units.id, the id of the unit of the package
    • VERSION_ID : id for versions.id
    • SOURCE_ID : non-null id for source
    • ESSENTIAL : boolean
    • BUILD-ESSENTIAL : boolean
• sources
  • This table describes source packages. A source package is a couple (unit_name, version). Note that source unit names may be different than package unit names. In other words, not every unit has a package or a source with its name.
    • ID : unique id for the source package
    • UNIT_ID : same as in packages
    • VERSION_ID : same as in packages
• versions
  • This tables maps version names to numbers in order to be able to sort those according to the Debian rules.
  • ID : a unique ID for this entry
  • UNIT_ID : the ID of the unit of which this is a version number
  • VERSION : the version string
  • NUMBER : the version number as an integer -- must be ordered
• requirements
  • This table encodes the package relationships Depends, Provides, Pre-depends, Replaces, Enhances, Provides and Conflicts.
    • PACKAGE_ID
    • TYPE : an integer describing the relationship. The encoding used is:
    • TARGET : this is an integer describing the target of the relationship. Its interpretation depends on the type of the relationship :

Field	Type encoding	Target interpretation
Provides	1	Unit ID
Depends	2	Disjunction ID
Pre-depends	3	Disjunction ID
Conflicts	4	Range ID
Replaces	5	Range ID
Suggests	6	Disjunction ID
Enhances	7	Disjunction ID
Recommends	8	Disjunction ID

• ranges
  • A range describes a versioned dependency constraint such as libc6 (>= 2.3.2.ds1-4)
  • Its fields are:
    • ID : a unique integer giving the range ID
    • UNIT_ID : the ID of the unit this range pertains to (e.g., the ID of the unit 'libc6')
    • OPERATOR : a tiny integer encoding the comparison operator (see table below)
    • VERSION_ID : the ID of the corresponding version string

Operator	Debian notation	Encoding
Any		1
Less than	<<	2
Less than or equal to	<=	3
Equal	&eq;=	4
Greater than or equal to	>=	5
Greater than	>>	6

• disjunctions
  • This table allows to decribe disjunctive dependencies. Each disjunction is given an ID, and all the ranges of that disjunction appear as rows having that ID.
  • Its fields are:
    • ID : an integer giving the ID of the disjunction of which this rows describes a range
    • RANGE_ID : the ID of the range that is a member of the disjunction
• lifetimes
  • This table describes the days and the archives in which a package has been present.
  • Its fields are:
    • PACKAGE_ID
    • ARCHIVE_ID
    • DAY : date
• archives
  • This table describes archives
    • ID
    • FLAVOR : e.g. debian, debian-non_US, etc.
    • DISTRIBUTION : stable, unstable, etc.
    • COMPONENT : main, contrib, non-free
    • ARCHITECTURE : binary-i386, etc.