~launchpad-pqm/launchpad/devel

--

-- This will DESTROY your database and create a fresh one

--

/*

  TODO

        - re-evalutate some of the "text" field types, they might need to be "bytea"

	  unless we can guarantee utf-8

        - re-evaluate the relationship table names, see if there isn't a better name for each of them

	- add sample data for the schemas

	- make sure names are only [a-z][0-9][-.+] and can only start with [a-z]

	- present the database as a set of Interfaces

  CHANGES

  v0.94:

        - rename soyuz.sql to launchpad.sql

	- make Schema.extensible DEFAULT false (thanks spiv)

  v0.93:

        - add a manifest to Sourcepackage and Product, for the mutable HEAD manifest

	- add a manifest to CodeRelease

	- rename includeas to entrytype in ManifestEntry

	- remove "part" from ManifestEntry

	- add hints in Manifest table so sourcerer knows how to name patch branches

	- fix my brain dead constraints for mutual exlcusivity on branch/changeset specs

	- for a ManifestEntry, branch AND changeset can now both be null, to allow for Scott's virtual entries

	- add the Packaging table to indicate the relationship between a Product and a Sourcepackage

  v0.92:

        - make Schema and Label have name, title, description

        - added filenames for UpstreamreleaseFile, SourcepackageFile and BinarypackageBuildFile

        - linked BinarypackageBuild to DistroRelease instead of DistroArchRelease

        - add the Country table for a list of countries

	- add the SpokenIn table to link countries and languages

        - rename TranslationProject to TranslationEffort

        - add iscurrent (boolean) field to the POTFiles table, current POTFiles

	    will be displayed in project summary pages.

        - add ChangesetFile, ChangesetFilename and ChangesetFileHash tables

        - rename Release to CodeRelease (and all dependent tables)

        - refactor Processor and ProcessorFamily:

	  - the distroarchrelease now has a processorfamily field

	  - the binarypackagebuild (deb) now records its processor

	- refactor the allocation of binarypackagebuild's (debs) to distroarchrelease's

	  - create a new table BinarypackageUpload that stores the packagearchivestatus

	  - remove that status from the BinarypackageBuild table

	- refactor sourcepackage upload status

	  - move changes and urgency to sourcepackagerelease

	  - add builddependsindep so sourcepackagerelease

  v0.91:

        - remove Translation_POTFile_Relationship

	- ...and replace with a "project" field in POTFile

	- add a commenttext field to the POTMsgIDSighting table so we can track comments in POT files too

  v0.9:

         6 July 2004

       - first versioned release

*/

/*

  DESTROY ALL TABLES

*/

DROP TABLE SpokenIn CASCADE;

DROP TABLE Country CASCADE;

DROP TABLE BugMessageSighting CASCADE;

DROP TABLE TranslationEffort_POTFile_Relationship CASCADE;

DROP TABLE POComment CASCADE;

DROP TABLE Branch_Relationship CASCADE;

DROP TABLE BugSystem CASCADE;

DROP TABLE BugWatch CASCADE;

DROP TABLE RosettaPOTranslationSighting CASCADE;

DROP TABLE BugAttachment CASCADE;

DROP TABLE BugAttachment_Relationship CASCADE;

DROP TABLE License CASCADE;

DROP TABLE Bug_Relationship CASCADE;

DROP TABLE BugMessage CASCADE;

DROP TABLE POTranslationSighting CASCADE;

DROP TABLE BugRef CASCADE;

DROP TABLE Bug CASCADE;

DROP TABLE Packaging CASCADE;

DROP TABLE SourcepackageReleaseFile CASCADE;

DROP TABLE Sourcepackage_Relationship CASCADE;

DROP TABLE SourcepackageRelease CASCADE;

DROP TABLE CodeReleaseBugStatus CASCADE;

DROP TABLE SourcepackageLabel CASCADE;

DROP TABLE SourcepackageBugStatus CASCADE;

DROP TABLE Bug_Sourcepackage_Relationship CASCADE;

DROP TABLE SourcepackageUpload CASCADE;

DROP TABLE Sourcepackage CASCADE;

DROP TABLE BinarypackageBuildFile CASCADE;

DROP TABLE BinarypackageUpload CASCADE;

DROP TABLE BinarypackageBuild CASCADE;

DROP TABLE Binarypackage CASCADE;

DROP TABLE ProductBugStatus CASCADE;

DROP TABLE Branch CASCADE;

DROP TABLE ArchConfig CASCADE;

DROP TABLE ArchConfigEntry CASCADE;

DROP TABLE BugActivity CASCADE;

DROP TABLE ArchArchiveLocation CASCADE;

DROP TABLE POTranslation CASCADE;

DROP TABLE BugSystemType CASCADE;

DROP TABLE POTSubscription CASCADE;

DROP TABLE ChangesetFileHash CASCADE;

DROP TABLE ChangesetFile CASCADE;

DROP TABLE ChangesetFileName CASCADE;

DROP TABLE Changeset CASCADE;

DROP TABLE ArchArchive CASCADE;

DROP TABLE UpstreamReleaseFile CASCADE;

DROP TABLE UpstreamRelease CASCADE;

DROP TABLE CodeRelease CASCADE;

DROP TABLE CodeRelease_Relationship CASCADE;

DROP TABLE POTInheritance CASCADE;

DROP TABLE POTMsgIDSighting CASCADE;

DROP TABLE Manifest CASCADE;

DROP TABLE ProductLabel CASCADE;

DROP TABLE Product CASCADE;

DROP TABLE POFile CASCADE;

DROP TABLE POTFile CASCADE;

DROP TABLE Project_Relationship;

DROP TABLE POMsgID CASCADE;

DROP TABLE Language CASCADE;

DROP TABLE BugLabel CASCADE;

DROP TABLE TranslationEffort CASCADE;

DROP TABLE Project CASCADE;

DROP TABLE Project_TranslationEffort_Relationship CASCADE;

DROP TABLE Person CASCADE;

DROP TABLE EmailAddress CASCADE;

DROP TABLE TranslationFilter CASCADE;

DROP TABLE BranchLabel CASCADE;

DROP TABLE ManifestEntry CASCADE;

DROP TABLE GPGKey CASCADE;

DROP TABLE ArchUserID CASCADE;

DROP TABLE Membership CASCADE;

DROP TABLE WikiName CASCADE;

DROP TABLE JabberID CASCADE;

DROP TABLE IRCID CASCADE;

DROP TABLE PersonLabel CASCADE;

DROP TABLE TeamParticipation CASCADE;

DROP TABLE Schema CASCADE;

DROP TABLE Label CASCADE;

DROP TABLE Distribution CASCADE;

DROP TABLE DistroRelease CASCADE;

DROP TABLE DistroArchRelease CASCADE;

DROP TABLE ProcessorFamily CASCADE;

DROP TABLE Builder CASCADE;

DROP TABLE Processor CASCADE;

DROP TABLE SoyuzFileHash CASCADE;

DROP TABLE SoyuzFile CASCADE;

DROP TABLE OSFileInPackage CASCADE;

DROP TABLE OSFile;

/*

  Person

  This is a person in the Soyuz system. A Person can also be a

  team if the teamowner is not NULL. Note that we will create a

  Person entry whenever we see an email address we didn't know

  about, or a GPG key we didn't know about... and if we later

  link that to a real Soyuz person we will update all the tables

  that refer to that temporary person.

  A Person is one of these automatically created people if it

  has a NULL password and is not a team.

  It's created first so that a Schema can have an owner, we'll

  then define Schemas and Labels a bit later.

*/

CREATE TABLE Person (

  person                serial PRIMARY KEY,

  presentationname      text,

  givenname             text,

  familyname            text,

  password              text,

  teamowner             integer REFERENCES Person,

  teamdescription       text,

  karma                 integer,

  karmatimestamp        timestamp

);

INSERT INTO Person ( presentationname, givenname, familyname ) VALUES ( 'Mark Shuttleworth', 'Mark', 'Shuttleworth' );     -- 1

INSERT INTO Person ( presentationname, givenname, familyname ) VALUES ( 'Dave Miller', 'David', 'Miller' );                -- 2

INSERT INTO Person ( presentationname, givenname, familyname ) VALUES ( 'Colin Watson', 'Colin', 'Watson' );               -- 3

INSERT INTO Person ( presentationname, givenname, familyname ) VALUES ( 'Steve Alexander', 'Steve', 'Alexander' );         -- 4

INSERT INTO Person ( presentationname, givenname, familyname ) VALUES ( 'Scott James Remnant', 'Scott James', 'Remnant' ); -- 5

INSERT INTO Person ( presentationname, givenname, familyname ) VALUES ( 'Robert Collins', 'Robert', 'Collins' );           -- 6

/*

  REVELATION. THE SOYUZ METADATA

*/

/*

  Schema

  This is the (finger finger) "metadata" (finger finger)

  which makes us... muahaha... distinctive... muahaha,

  muahahaha, muahahahahahaa....

  And yes, I'm not yet sure if my database model for this

  is on crack. Comments please. MS 24/06/04

*/

CREATE TABLE Schema (

  schema         serial PRIMARY KEY,

  name           text NOT NULL,

  title          text NOT NULL,

  description    text NOT NULL,

  owner          integer NOT NULL REFERENCES Person,

  extensible     boolean NOT NULL DEFAULT false

);

/*

  Label

  The set of labels in all schemas

*/

CREATE TABLE Label (

  label          serial PRIMARY KEY,

  schema         integer NOT NULL REFERENCES Schema,

  name           text NOT NULL,

  title          text NOT NULL,

  description    text NOT NULL

);

/*

  EmailAddress

  A table of email addresses for Soyuz people.

*/

CREATE TABLE EmailAddress (

  emailid     serial PRIMARY KEY,

  email       text NOT NULL UNIQUE,

  person      integer NOT NULL REFERENCES Person,

  label       integer NOT NULL REFERENCES Label

);

/*

  GPGKey

  A table of GPGKeys, mapping them to Soyuz users.

*/

CREATE TABLE GPGKey (

  gpgkey      serial PRIMARY KEY,

  person      integer NOT NULL REFERENCES Person,

  keyid       text NOT NULL UNIQUE,

  fingerprint text NOT NULL UNIQUE,

  pubkey      text NOT NULL,

  revoked     boolean NOT NULL

);

/*

  ArchUserID

  A table of Arch user id's

*/

CREATE TABLE ArchUserID (

  person     integer NOT NULL REFERENCES Person,

  archuserid text NOT NULL UNIQUE

);

/*

  WikiName

  The identity a person uses on one of the Soyuz wiki's.

*/

CREATE TABLE WikiName (

  person     integer NOT NULL REFERENCES Person,

  wiki       text NOT NULL,

  wikiname   text NOT NULL,

  UNIQUE ( wiki, wikiname )

);

/*

  JabberID

  A person's Jabber ID on our network.

*/

CREATE TABLE JabberID (

  person      integer NOT NULL REFERENCES Person,

  jabberid    text NOT NULL UNIQUE

);

/*

  IrcID

  A person's irc nick's.

*/

CREATE TABLE IRCID (

  person       integer NOT NULL REFERENCES Person,

  network      text NOT NULL,

  nickname     text NOT NULL

);

/*

  PersonLabel

  A neat way to attache tags to people...

*/

CREATE TABLE PersonLabel (

  person       integer NOT NULL REFERENCES Person,

  label        integer NOT NULL REFERENCES Label

);

/*

  Membership

  A table of memberships. It's only valid to have a membership

  in a team, not a non-team person.

*/

CREATE TABLE Membership (

  person      integer NOT NULL REFERENCES Person,

  team        integer NOT NULL REFERENCES Person,

  label       integer NOT NULL REFERENCES Label,

  status      integer NOT NULL REFERENCES Label,

  PRIMARY KEY ( person, team )

);

/*

  TeamParticipation

  This is a table which shows all the memberships

  of a person. Effectively it collapses team hierarchies

  and flattens them to a straight team-person relation.

  People are also members of themselves. This allows

  us to query against a person entry elsewhere in Soyuz

  and quickly find the things a person is an owner of.

*/

CREATE TABLE TeamParticipation (

  team         integer NOT NULL REFERENCES Person,

  person       integer NOT NULL REFERENCES Person,

  PRIMARY KEY ( team, person )

);

/*

  BUTTRESS. THE ARCH REPOSITORY.

  This is the Soyuz subsystem that handles the storing and

  cataloguing of all of our Arch branches.

*/

/*

  ArchArchive

  A table of all known Arch Archives.

*/

CREATE TABLE ArchArchive (

  archive       serial PRIMARY KEY,

  name          text NOT NULL,

  title         text NOT NULL,

  description   text NOT NULL,

  visible       boolean NOT NULL,

  owner         integer REFERENCES Person

);

/*

  ArchArchiveLocation

  A table of known Arch archive locations.

*/

CREATE TABLE ArchArchiveLocation (

  archive       integer NOT NULL REFERENCES ArchArchive,

  archivetype   integer NOT NULL REFERENCES Label, -- the Arch archive type

  url           text NOT NULL,

  gpgsigned     boolean NOT NULL

);

/*

  Branch

  An Arch Branch in the Soyuz system.

*/

CREATE TABLE Branch (

  branch                 serial PRIMARY KEY,

  archive                integer NOT NULL REFERENCES ArchArchive,

  categorybranchversion  text NOT NULL,

  title                  text NOT NULL,

  description            text NOT NULL,

  visible                boolean NOT NULL,

  owner                  integer REFERENCES Person

);

/*

  Changeset

  An Arch changeset.

*/

CREATE TABLE Changeset (

  changeset      serial PRIMARY KEY,

  branch         integer NOT NULL REFERENCES Branch,

  createdate     timestamp NOT NULL,

  logmessage     text NOT NULL,

  author         integer REFERENCES Person,

  gpgkey         integer REFERENCES GPGKey

);

/*

  ChangesetFileName

  A filename in an arch changeset.

*/

CREATE TABLE ChangesetFileName (

  changesetfilename     serial PRIMARY KEY,

  filename              text NOT NULL UNIQUE

);

/*

  ChangesetFile

  A file in an arch changeset.

*/

CREATE TABLE ChangesetFile (

  changesetfile      serial PRIMARY KEY,

  changeset          integer NOT NULL REFERENCES Changeset,

  changesetfilename  integer NOT NULL REFERENCES ChangesetFileName,

  filecontents       bytea NOT NULL,

  filesize           integer NOT NULL,

  UNIQUE ( changeset, changesetfilename )

);

/*

  ChangesetFileHash

  A cryptographic hash of a changeset file.

*/

CREATE TABLE ChangesetFileHash (

  changesetfile     integer NOT NULL REFERENCES ChangesetFile,

  hashalg           integer NOT NULL REFERENCES Label,

  hash              bytea NOT NULL

);

/*

  Branch_Relationship

  A table of relationships between branches. For example:

  "src is a debianization-branch-of dst"

  "src is-a-patch-branch-of dst

*/

CREATE TABLE Branch_Relationship (

  src        integer NOT NULL REFERENCES Branch,

  dst        integer NOT NULL REFERENCES Branch,

  label      integer NOT NULL REFERENCES Label,

  PRIMARY KEY ( src, dst )

);

/*

  BranchLabel

  A table of labels on branches.

*/

CREATE TABLE BranchLabel (

  branch       int NOT NULL REFERENCES Branch,

  label        int NOT NULL REFERENCES Label

);

/*

  Manifest

  A release manifest. This is sort of an Arch config

  on steroids. A Manifest is a set of ManifestEntry's

*/

CREATE TABLE Manifest (

  manifest         serial PRIMARY KEY,

  creationdate     timestamp NOT NULL,

  brancharchive    integer REFERENCES ArchArchive, --

  branchcategory   text,                           -- Where to put new patch-branches

  branchversion    text                            --

);

/*

  ManifestEntry

  An entry in a Manifest. each entry specifies either a branch or

  a specific changeset (revision) on a branch, as well as how that

  piece of code (revision) is brought into the release.

*/

CREATE TABLE ManifestEntry (

  manifest        integer NOT NULL REFERENCES Manifest,

  sequence        integer NOT NULL,

  branch          integer REFERENCES Branch,

  changeset       integer REFERENCES Changeset,

  entrytype       integer NOT NULL REFERENCES Label,

  path            text NOT NULL,

  patchon         integer NOT NULL,

  -- sequence must be a positive integer

  CHECK ( sequence > 0 ),

  -- EITHER branch OR changeset:

  CHECK ( NOT ( branch IS NOT NULL AND changeset IS NOT NULL ) ),

  -- the "patchon" must be another manifestentry from the same

  -- manifest, and a different sequence

  -- XXX no idea how to express this constraint, help!

  -- the primary key is the combination of manifest and sequence

  PRIMARY KEY ( manifest, sequence )

);

/*

  FLOSS. THE OPEN SOURCE WORLD

  This is the Soyuz subsystem that models the open source world

  of projects and products.

*/

/*

 The Project table. This stores information about an open

 source project, which can be translated or packaged, or

 about which bugs can be filed.

*/

CREATE TABLE Project (

    project      serial PRIMARY KEY,

    owner        integer NOT NULL REFERENCES Person,

    name         text NOT NULL UNIQUE,

    title        text NOT NULL,

    description  text,

    homepage     text

    );

/*

 The Project_Relationship table. This stores information about

 the relationships between open source projects. For example,

 the Gnome project aggregates the GnomeMeeting project.

*/

CREATE TABLE Project_Relationship (

  src           integer NOT NULL REFERENCES Project,

  dst           integer NOT NULL REFERENCES Project,

  relationship  text NOT NULL,

  value         text

);

/*

  Product

  A table of project products. A product is something that

  can be built, or a branch of code that is useful elsewhere, or

  a set of docs... some distinct entity. Products can be made

  up of other products, but that is not reflected in this

  database. For example, Firefax includes Gecko, both are

  products.

*/

CREATE TABLE Product (

  product       serial PRIMARY KEY,

  project       integer NOT NULL REFERENCES Project,

  owner         integer NOT NULL REFERENCES Person,

  name          text NOT NULL,

  title         text NOT NULL,

  description   text NOT NULL,

  homepage      text,

  manifest      integer REFERENCES Manifest,

  UNIQUE ( project, name )

);

/*

  ProductLabel

  A label or metadata on a Product.

*/

CREATE TABLE ProductLabel (

  product  integer NOT NULL REFERENCES Product,

  label      integer NOT NULL REFERENCES Label,

  PRIMARY KEY ( product, label )

);

/*

  UpstreamRelease

  A specific tarball release of Upstream.

*/

CREATE TABLE UpstreamRelease (

  upstreamrelease  serial PRIMARY KEY,

  product        integer NOT NULL REFERENCES Product,

  releasedate      timestamp NOT NULL,

  name             text NOT NULL,

  gsvname          text,

  description      text,

  owner            integer REFERENCES Person

);

/*

   BUTTRESS phase 2

*/

/*

  ArchConfig

  A table to model Arch configs.

*/

CREATE TABLE ArchConfig (

  archconfig       serial PRIMARY KEY,

  name             text NOT NULL,

  title            text NOT NULL,

  description      text NOT NULL,

  upstreamrelease  integer REFERENCES UpstreamRelease,

  owner            integer REFERENCES Person

);

/*

  ArchConfigEntry

  A table to represent the entries in an Arch config. Each

  row is a separate entry in the arch config.

*/

CREATE TABLE ArchConfigEntry (

  archconfig    integer NOT NULL REFERENCES ArchConfig,

  path          text NOT NULL,

  branch        integer REFERENCES Branch,

  changeset     integer REFERENCES Changeset,

  -- EITHER branch OR changeset:

  CHECK ( NOT ( branch IS NULL AND changeset IS NULL ) ),

  CHECK ( NOT ( branch IS NOT NULL AND changeset IS NOT NULL ) )

);

/*

  LOGISTIX. THE PACKAGES AND DISTRIBUTION MANAGER.

  Nicknamed after UPS (United Parcel Service) this is the

  Soyuz subsystem that deals with distribution and packages.

*/

/*

  ProcessorFamily

  A family of CPU's, which are all compatible. In other words, code

  compiled for any one of these processors will run on any of the

  others.

*/

CREATE TABLE ProcessorFamily (

  processorfamily    serial PRIMARY KEY,

  name               text NOT NULL UNIQUE,

  title              text NOT NULL,

  description        text NOT NULL,

  owner              integer NOT NULL REFERENCES Person

);

/*

  Processor

  This is a table of system architectures. A DistroArchRelease needs

  to be one of these.

*/

CREATE TABLE Processor (

  processor          serial PRIMARY KEY,

  family             integer NOT NULL REFERENCES ProcessorFamily,

  name               text NOT NULL UNIQUE,

  title              text NOT NULL,

  description        text NOT NULL,

  owner              integer NOT NULL REFERENCES Person

);

/*

  Builder

  An Ubuntu build daemon.

*/

CREATE TABLE Builder (

  builder            serial PRIMARY KEY,

  processor          integer NOT NULL REFERENCES Processor,

  fqdn               text NOT NULL,

  name               text NOT NULL,

  title              text NOT NULL,

  description        integer NOT NULL REFERENCES Person,

  UNIQUE ( fqdn, name )

);

/*

  Distribution

  An open source distribution. Collection of packages, the reason

  for Soyuz existence.

*/

CREATE TABLE Distribution (

  distribution     serial PRIMARY KEY,

  name             text NOT NULL,

  title            text NOT NULL,

  description      text NOT NULL,

  components       integer NOT NULL REFERENCES Schema,

  sections         integer NOT NULL REFERENCES Schema,

  owner            integer NOT NULL REFERENCES Person

);

/*

  DistroRelease

  These are releases of the various distributions in the system. For

  example: warty, hoary, grumpy, woody, potato, slink, sarge, fc1,

  fc2.

*/

CREATE TABLE DistroRelease (

  distrorelease   serial PRIMARY KEY,

  distribution    integer NOT NULL REFERENCES Distribution,

  name            text NOT NULL, -- "warty"

  title           text NOT NULL, -- "Ubuntu 4.10 (The Warty Warthog Release)"

  description     text NOT NULL,

  version         text NOT NULL, -- "4.10"

  releasestate    integer NOT NULL REFERENCES Label

);

/*

  DistroArchRelease

  This is a distrorelease for a particular architecture, for example,

  warty-i386.

*/

CREATE TABLE DistroArchRelease (

  distroarchrelease serial PRIMARY KEY,

  distrorelease     integer NOT NULL REFERENCES DistroRelease,

  processor         integer NOT NULL REFERENCES Processor,

  architecturetag   text NOT NULL,

  releasestatus     integer NOT NULL REFERENCES Label,

  releasedate       timestamp,

  owner             integer NOT NULL REFERENCES Person

);

/*

  SoyuzFile

  The Soyuz system keeps copies of all the files that are used to make

  up a distribution, such as deb's and tarballs and .dsc files and .spec

  files and CodeRelease files... these are represented by this table.

*/

CREATE TABLE SoyuzFile (

  soyuzfile        serial PRIMARY KEY,

  filename         text NOT NULL,

  filesize         integer NOT NULL

);

/*

  SoyuzFileHash

  A hash (cryptographic digest) on the file. We can support multiple

  different hashes with different algorithms. Initially we'll just 

  use SHA1, but if that gets broken we can trivially switch to another

  algorithm.

  The hash is not required to be UNIQUE but Oscar should flag duplicates

  for inspection by hand. Note that the combination of filesize and hash

  should be unique or there is something very weird going on. Or we just hit

  the crypto lottery and found a collision.

*/

CREATE TABLE SoyuzFileHash (

  soyuzfile       integer NOT NULL REFERENCES SoyuzFile,

  hashalg         integer NOT NULL REFERENCES Label,

  hash            bytea NOT NULL

);

/*

  UpstreamReleaseFile

  A file from an Upstream CodeRelease. Usually this would be a tarball.

*/

CREATE TABLE UpstreamReleaseFile (

  upstreamrelease integer NOT NULL REFERENCES UpstreamRelease,

  soyuzfile       integer NOT NULL REFERENCES SoyuzFile,

  filetype        integer NOT NULL REFERENCES Label,

  filename        text NOT NULL

);

/*

  Sourcepackage

  A distribution source package. In RedHat or Debian this is the name

  of the source package, in Gentoo it's the Ebuild name.

*/

CREATE TABLE Sourcepackage (

  sourcepackage    serial PRIMARY KEY,

  maintainer       integer NOT NULL REFERENCES Person,

  name             text NOT NULL,

  title            text NOT NULL,

  description      text NOT NULL,

  manifest         integer REFERENCES Manifest

);

/*

  Sourcepackage_Relationship

  The relationship between two source packages. For example, if a source

  package in Ubuntu is derived from a source package in Debian, we would

  reflect that here.

*/

CREATE TABLE Sourcepackage_Relationship (

  src       integer NOT NULL REFERENCES Sourcepackage,

  dst       integer NOT NULL REFERENCES Sourcepackage,

  label     integer NOT NULL REFERENCES Sourcepackage,

  CHECK ( src <> dst )

);

/*

  SourcepackageLabel

  A tag or label on a source package.

*/

CREATE TABLE SourcepackageLabel (

  sourcepackage     integer NOT NULL REFERENCES Sourcepackage,

  label             integer NOT NULL REFERENCES Label

);

/*

  Packaging

  This is really the relationship between a Product and a

  Sourcepackage. For example, it allows us to say that

  the apache2 source package is a packaging of the

  httpd Product from the Apache Group.

*/

CREATE TABLE Packaging (

  product         integer NOT NULL REFERENCES Product,

  sourcepackage   integer NOT NULL REFERENCES Sourcepackage,

  label           integer NOT NULL REFERENCES Label

);

/*

  SourcepackageRelease

  A SourcepackageRelease is a specific release of a Sourcepackage, which is

  associated with one or more distribution releases. So apache2__2.0.48-3 can

  be in both ubuntu/warty and debian/sarge.

*/

CREATE TABLE SourcepackageRelease (

  sourcepackagerelease   serial PRIMARY KEY,

  sourcepackage          integer NOT NULL REFERENCES Sourcepackage,

  srcpackageformat       integer NOT NULL REFERENCES Label,

  creator                integer NOT NULL REFERENCES Person,

  version                text NOT NULL, -- "2.0.48-3"

  dateuploaded           timestamp NOT NULL,

  urgency                integer NOT NULL REFERENCES Label,

  dscsigningkey          integer REFERENCES GPGKey,

  component              integer REFERENCES Label,

  changelog              text,

  changes                text,

  builddepends           text,

  builddependsindep      text,

  architecturehintlist   text

);

/*

  SourcepackageReleaseFile

  A file associated with a sourcepackagerelease. For example, could be

  a .dsc file, or an orig.tar.gz, or a diff.gz...

*/

CREATE TABLE SourcepackageReleaseFile (

  sourcepackagerelease  integer NOT NULL REFERENCES SourcepackageRelease,

  soyuzfile             integer NOT NULL REFERENCES SoyuzFile,

  filetype              integer NOT NULL REFERENCES Label,

  filename              text NOT NULL

);

/*

  SourcepackageUpload

  This table indicates which sourcepackagereleases are present in a

  given distrorelease. It also indicates their status in that release

  (for example, whether or not that sourcepackagerelease has been

  withdrawn, or is currently published, in that archive).

*/

CREATE TABLE SourcepackageUpload (

  distrorelease          integer NOT NULL REFERENCES DistroRelease,

  sourcepackagerelease   integer NOT NULL REFERENCES SourcepackageRelease,

  packagereleasestatus   integer NOT NULL REFERENCES Label,

  PRIMARY KEY ( distrorelease, sourcepackagerelease )

);

/*

  Binarypackage

  This is a binary package... not an actual built package (that

  is a BinarypackageBuild) but the concept of that binary package.

  It stores the name of the binary package, together with other

  details. Note that different distributions might well have