# # Proposed database schema for Trove source archives, in Postgres95 format. # Written by Eric S. Raymond for the Trove project. # # $Id: schema,v 1.26 1998/07/01 18:44:19 esr Exp $ # # This schema is in first normal form. It uses object references # but these could be trivially eliminated by inlining the `access' record. # # Name, description, ownership and update tracking for all objects. # CREATE TABLE item ( name text not null primary key, # Item name summary text, # One-line description description text, # Fuller description created date, # Date first uploaded updatecount int, # Count of updates modified date, # Date last modified owner text, # Who is privileged to lock/unlock it contributor text, # Who entered it via text, # How it got here # If true, only privileged people may modify the parent record locked bool; ); # An author or maintainer. # # Note: the `name' field of the item block is the user's email address. # In the item block, the `owner' and `locked' fields are not used (the # owner of a person record is the person, and all person records are # considered locked). The other fields are used for update tracking # as usual. # # The `auth' fields may be used in the future to support authentication # modes other than PGP using the public-key-server infrastructure (which # is the default, assumed if `auth-mode' is empty). # CREATE TABLE person ( item item, # Basic info homepage text, # Home site auth_mode text, # Authentication mode auth_secret text, # Authentication secret. ); # A file or resource. # # Each resource is an association of a URL with a package. # A package key has to be part of the record so the filename # field won't be constrained to be unique. # # Note: The `name' of a resource is either (a) the URL to fetch it from, # or (b) the basename of a file carried locally. # # Default MIME type is normally deduced from filename extension. # CREATE TABLE resource ( item item; # Basic info for this resource filename text; # Local file name (if a copy) package text not null, # Owning package version text, # Version stamp role text, # Resource role mimetype text, # MIME type ); # A package. # CREATE TABLE package ( item item; # Basic info for this resource icon text, # 64x64 GIF/PNG/JPEG homepage text, # home site of package latest text, # latest version stable text, # last stable version # These fields are for use by the retrieval crawler crawlto text, # URL to trusted remote metadata remotedate date, # Last-modified date of remote metadata ); # A keyword association # # A `discriminator' textually has the form /a/b/c/d/.../z # where a...z are controlled-vocabulary keywords, and each slash-separated # pair is related parent-to-child in the vocabulary tree. # CREATE TABLE keyword-relation ( package text not null, # Package name discriminator text not null, # Rooted path in keyword tree ); # Note: The relations below are separate to avoid possible namespace collisions # between people IDs, package IDs, and resource IDs. # A relationship between packages # "supercedes"/"superceded by" # "extends"/"has extension" # "requires"/"required by" # "see also" # CREATE TABLE relation ( subject text, # Package primary key object text, # Package primary key verb text; ); # A relationship between a person and a package. # "is subscribed to the notification list of" # "is a contact person for" # "is an author of" # "is a maintainer of" # The `author' and `maintainer' relations are separate in order to support # searches by author name even when an author is no longer a current # maintainer. # CREATE TABLE package-relation ( person text, # Person URN resource text, # Package id verb text, ); # Analogous relationship between person and resource # "is a contact person for" # "is an author of" # "is a maintainer of" # The `author' and `maintainer' relations are separate in order to support # searches by author name even when an author is no longer a current # maintainer. # CREATE TABLE resource-relation ( person text, # Person URN resource text, # Resource id verb text; );